<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: wangwei  
    $Date: 2015-12-22 下午5:13:29  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
               SELECT
                     TRIM(TO_CHAR((SELECT
                        ROUND(nvl(SUM(NVL(ccc.principal, 0) - NVL(ccc.received_principal, 0)),0)/10000,0)
                    FROM
                        con_contract_cashflow ccc
                    WHERE
                        ccc.cf_item = 1 AND
                        EXISTS
                        (SELECT
                            1
                        FROM
                            con_contract cc
                        WHERE
                            cc.contract_id = ccc.contract_id AND
                            cc.data_class  = 'NORMAL'
                            and cc.contract_status not in('CANCEL')
                        )
                        and exists(
                        select 1 from con_contract_cashflow cf where 
                        cf.contract_id=ccc.contract_id
                        and cf.cf_item=0 
                        and cf.write_off_flag<>'NOT'
                        )
                    )-
                    round(nvl((select sum(nvl(scc.principal,0)) from
                    sdic_other_contract sc,sdic_other_contract_cf scc where sc.wfl_status='APPROVED'
                    AND SC.CONTRACT_ID=scc.contract_id
                    and trunc(scc.due_date)<=sysdate),0)/10000,0),'999,999,999,999,999'))
                    no_received_amount,
                    (SELECT
                        TRIM(TO_CHAR(SUM(NVL(ccc.principal, 0)), '999,999,999,999,999.99'))
                    FROM
                        con_contract_cashflow ccc
                    WHERE
                        ccc.cf_item = 1 AND
                        EXISTS
                        (SELECT
                            1
                        FROM
                            con_contract cc
                        WHERE
                            cc.contract_id = ccc.contract_id AND
                            cc.data_class  = 'NORMAL'
                            and cc.contract_status not in('CANCEL')
                        )
                    ) total_finance_amount,
                    (SELECT
                        TRIM(TO_CHAR(round(nvl(SUM(NVL(ccc.received_amount, 0)),0)/10000,0), '999,999,999,999,999'))
                    FROM
                        con_contract_cashflow ccc
                    WHERE
                        ccc.cf_item = 0 AND
                        EXISTS
                        (SELECT
                            1
                        FROM
                            con_contract cc
                        WHERE
                            cc.contract_id                         = ccc.contract_id AND
                            cc.data_class                          = 'NORMAL' AND
                             cc.contract_status not in('CANCEL') and
                            TO_CHAR(cc.inception_of_lease, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY')
                        )
                    ) yaer_total_finance_amount,
                    (SELECT
                        TRIM(TO_CHAR(round(nvl(SUM(NVL(ccc.received_amount, 0)),0)/10000,0), '999,999,999,999,999'))
                    FROM
                        con_contract_cashflow ccc
                    WHERE
                        ccc.cf_item = 0 AND
                        EXISTS
                        (SELECT
                            1
                        FROM
                            con_contract cc
                        WHERE
                            cc.contract_id                            = ccc.contract_id AND
                            cc.data_class                             = 'NORMAL' AND
                             cc.contract_status not in('CANCEL') and
                            TO_CHAR(cc.inception_of_lease, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM')
                        )
                    ) month_total_finance_amount,
                    (SELECT
                        TRIM(TO_CHAR(nvl(SUM(NVL(tbc.credit_amount, 0)),0)/10000, '999,999,999,999,999'))
                    FROM
                        tre_bank_credit_contract tbc
                    WHERE
                        SYSDATE        > TRUNC(tbc.credit_date_from) AND
                        TRUNC(SYSDATE) < NVL(tbc.credit_date_to, SYSDATE)
                    ) credit_amount,
                    (SELECT
                        TRIM(TO_CHAR(nvl(SUM(NVL(tre_bank_credit_contract_pkg.get_available_amount(tbc.credit_contract_id), 0)),0)/10000, '999,999,999,999,999'))
                    FROM
                        tre_bank_credit_contract tbc
                    WHERE
                        SYSDATE        > TRUNC(tbc.credit_date_from) AND
                        TRUNC(SYSDATE) < NVL(tbc.credit_date_to, SYSDATE)
                    ) available_credit_amount,
                    (SELECT
                        TRIM(TO_CHAR(SUM(NVL(cwo.due_amount, 0)), '999,999,999,999,999.99'))
                    FROM
                        tre_loan_con_write_off cwo
                    WHERE
                        cwo.plan_type               = 'WITHDRAW' AND
                        cwo.cf_item                 = 301 AND
                        NVL(cwo.reversed_flag, 'N') = 'N'
                    ) withdraw_amount,
                    TRIM(TO_CHAR(NVL(
                    (SELECT
                        SUM(NVL(cwo.due_amount, 0))
                    FROM
                        tre_loan_con_write_off cwo
                    WHERE
                        cwo.plan_type               = 'WITHDRAW' AND
                        cwo.cf_item                 = 301 AND
                        NVL(cwo.reversed_flag, 'N') = 'N'
                    ), 0) - NVL(
                    (SELECT
                        SUM(NVL(cwo.due_amount, 0))
                    FROM
                        tre_loan_con_write_off cwo
                    WHERE
                        cwo.plan_type               = 'PAYMENT' AND
                        cwo.cf_item                 = 302 AND
                        NVL(cwo.reversed_flag, 'N') = 'N'
                    ), 0), '999,999,999,999,999.99')) no_recevied_withdraw_amount,
                 to_char(ROUND((SELECT
                        SUM((nvl(ccc.principal,0)-
                        NVL(ccc.received_principal, 0))*con.irr)
                    FROM
                        con_contract con,
                        con_contract_cashflow ccc
                    WHERE
                        ccc.cf_item = 1 
                        and con.contract_id=ccc.contract_id
                        AND
                        
                        EXISTS
                        (SELECT
                            1
                        FROM
                            con_contract cc
                        WHERE
                            cc.contract_id = ccc.contract_id AND
                            cc.data_class  = 'NORMAL'
                            and cc.contract_status  not in('CANCEL')
                        ) 
                    )/(SELECT
                        SUM(NVL(ccc.principal, 0)-nvl(ccc.received_principal,0))
                    FROM
                        con_contract con,
                        con_contract_cashflow ccc
                    WHERE
                        ccc.cf_item = 1 
                        and con.contract_id=ccc.contract_id
                        AND
                        
                        EXISTS
                        (SELECT
                            1
                        FROM
                            con_contract cc
                        WHERE
                            cc.contract_id = ccc.contract_id AND
                            cc.data_class  = 'NORMAL'
                             and cc.contract_status  not in('CANCEL')
                        ) 
                    )*100,2),'99.99')||'%' AVG_BALANCE_RATE,
                    (SELECT trim(to_char(nvl(a.year_profits,0),'999,999,999,999'))
                      FROM SDIC_COMPANY_PERIOD_PROFITS A WHERE A.COMPNAY_ID=${/session/@company_id}
                    and to_number(to_char(add_months(sysdate,-1),'yyyymm'))=a.internal_period_num)   year_profits,
                   (SELECT trim(to_char(nvl(a.month_profits,0),'999,999,999,999'))
                     FROM SDIC_COMPANY_PERIOD_PROFITS A WHERE A.COMPNAY_ID=${/session/@company_id}
                    and to_number(to_char(add_months(sysdate,-1),'yyyymm'))=a.internal_period_num) month_profits,
                    trim(TO_CHAR(ROUND((select sum(nvl(rp.amount,0)*nvl((select ex.exchange_rate from sdic_monthly_exchange_rate ex where
ex.currency_code=tlc.currency
and ex.internal_period_num=(select  max(ex1.internal_period_num) 
from sdic_monthly_exchange_rate ex1
where ex1.currency_code=tlc.currency)
),1))  from tre_loan_con_repayment_plan rp,
tre_loan_contract tlc 
where rp.repayment_type='PRINCIPAL'
and nvl(rp.write_off_flag,'N')<>'Y'
and tlc.loan_contract_id=rp.loan_contract_id
and tlc.data_class='NORMAL'
and exists(select 1 from prj_quotation q where nvl(q.main_contract_flag,0)<>'Y'
and q.quotation_id=rp.quotation_id))/10000),'999,999,999,999,999')) loan_con_balance
                FROM
                    dual
            ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
               begin
bgfl_hls_report_query_pkg.report_rpt3010_init(p_calc_type => 'TIMES_POINT',
                                              p_calc_date => sysdate,
                                              p_session_id =>${/session/@session_id} ,
                                              p_user_id =>${/session/@user_id} ,
                                              p_wacc => ${@wacc},
                                              p_wacc_tax =>${@wacc_tax} );
end; 
            ]]></bm:update-sql>
            <bm:parameters>
                <bm:parameter name="wacc" input="false" output="true" outputPath="@wacc"/>
                <bm:parameter name="wacc_tax" input="false" output="true" outputPath="@wacc_tax"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
</bm:model>
