<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Administrator  
    $Date: 2016-2-26 上午10:27:58  
    $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
                    t.*,
                    (SELECT
                        g.period_name
                    FROM
                        gld_periods g
                    WHERE
                        g.INTERNAL_PERIOD_NUM = t.INTERNAL_PERIOD_NUM
                    ) period_name
                FROM
                    (SELECT
                        'LEASE' CONTRACT_TYPE,
                        '租赁' CONTRACT_TYPE_desc,
                        T1.PROJECT_ID DOCUMENT_ID,
                        T1.INTERNAL_PERIOD_NUM,
                        T1.FINANCE_INCOME,
                        sdic_report_pkg.get_sum_vat_finance_income(p_contract_id => t1.contract_id, p_finance_income => t1.FINANCE_INCOME, p_internal_period_num => T1.INTERNAL_PERIOD_NUM) vat_finance_income,
                        T1.FINANCE_INCOME - sdic_report_pkg.get_sum_vat_finance_income(p_contract_id => t1.contract_id, p_finance_income => t1.FINANCE_INCOME, p_internal_period_num => T1.INTERNAL_PERIOD_NUM) net_finance_income,
                        prj.virtual_con_number document_number,
                        prj.virtual_con_number original_con_number,
                        prj.virtual_con_name document_name,
                        (SELECT bp.bp_name FROM hls_bp_master bp WHERE bp.bp_id = prj.bp_id_tenant
                        ) bp_name,
                        (SELECT
                            'Y'
                        FROM
                            dual
                        WHERE
                            EXISTS
                            (SELECT
                                1
                            FROM
                                con_contract cc,
                                CON_UNEARNED_FINANCE_INCOME CI
                            WHERE
                                cc.project_id          = t1.project_id AND
                                cc.data_class          = 'NORMAL' AND
                                CC.CONTRACT_ID         = CI.CONTRACT_ID AND
                                CI.INTERNAL_PERIOD_NUM = T1.INTERNAL_PERIOD_NUM AND
                                ci.calc_end_date       = last_day(to_date(ci.internal_period_num
                                || '01', 'YYYYMMDD')) AND
                                CI.FINANCE_INCOME_RECOGNIZED = 'Y'
                            )
                        ) FINANCE_INCOME_RECOGNIZED,
                        TO_CHAR(to_number(HD.LEASE_TERM), 'FM990.0')
                        || '年' LEASE_TERM,
                        to_number(hd.lease_item_amount) lease_item_amount,
                        --(SELECT
                        --    '基准'
                        --   || v.code_value_name
                        --    || INT_RATE_FIXING_RANGE * 100
                        --    || '%'
                        --FROM
                        --    sys_code_values_v v
                        --WHERE
                        --    v.code       = 'TRE502_INT_RATE_FIXING_WAY' AND
                        --    v.code_value = hd.INT_RATE_FIXING_WAY
                        --)
                        to_number(hd.int_rate)*100
                        ||'%' rate_desc,
                        NVL((SELECT sum(cufi.weight_factor)
             FROM con_unearned_finance_income cufi
            WHERE cufi.contract_id = t1.contract_id
              AND cufi.cf_item = 1
              AND cufi.calc_end_date =
                  last_day(to_date(t1.internal_period_num || '01',
                                   'YYYY-MM-DD'))),
           0) provision_days,
           (SELECT MIN(ccc.outstanding_prin_tax_incld)
          FROM con_contract_cashflow ccc
         WHERE ccc.contract_id = t1.contract_id
           AND ccc.cf_item IN (0, 1)
           AND TO_CHAR(ccc.due_date, 'YYYYMM') <
               t1.internal_period_num) -
       NVL((SELECT ccc.principal
             FROM con_contract_cashflow ccc
            WHERE ccc.contract_id = t1.contract_id
              AND ccc.cf_item = 1
              AND TO_CHAR(ccc.due_date, 'YYYYMM') =
                  t1.internal_period_num),
           0) month_end_principal
                    FROM
                        (SELECT
                            P.PROJECT_ID,
                            cc.contract_id,
                            CI.INTERNAL_PERIOD_NUM,
                            ci.unearned_finance_income FINANCE_INCOME
                            --SUM(NVL(CI.UNEARNED_FINANCE_INCOME, 0)) FINANCE_INCOME
                        FROM
                            prj_project p,
                            CON_contract cc,
                            CON_UNEARNED_FINANCE_INCOME CI
                        WHERE
                            p.data_class     = 'VIRTUAL_CON' AND
                            cc.project_id    = p.project_id AND
                            cc.data_class    = 'NORMAL' AND
                            CC.CONTRACT_ID   = CI.CONTRACT_ID AND
                            ci.calc_end_date = last_day(to_date(ci.internal_period_num
                            || '01', 'YYYYMMDD'))
                            --GROUP BY P.PROJECT_ID, CI.INTERNAL_PERIOD_NUM
                        ) T1,
                        prj_project prj,
                        prj_quotation q,
                        hls_fin_calculator_hd hd
                    WHERE
                        t1.PROJECT_ID                  = prj.PROJECT_ID AND
                        t1.internal_period_num        >= 201601 AND
                        q.document_id                  = prj.project_id AND
                        Q.CALC_SESSION_ID              = HD.CALC_SESSION_ID AND
                        NVL(Q.MAIN_CONTRACT_FLAG, 'N') = 'Y'
                    UNION
                    SELECT
                        'LOAN' CONTRACT_TYPE,
                        '融资' CONTRACT_TYPE_desc,
                        T2.quotation_id DOCUMENT_ID,
                        T2.INTERNAL_PERIOD_NUM,
                        T2.FINANCE_INCOME,
                        DECODE(tlc.inside_or_outside, 'LOAN_DOMESTIC', 0, 'LOAN_ABROAD', T2.FINANCE_INCOME - t2.FINANCE_INCOME / ROUND((1 + (NVL(tlc.withhold_income_tax, 0) + NVL(tlc.withhold_value_added_tax, 0) + NVL(tlc.withhold_sale_tax_add, 0)) / (1 - NVL(tlc.withhold_income_tax, 0) - (NVL(tlc.withhold_value_added_tax, 0) * NVL(tlc.withhold_sale_tax_add, 0)))), 2)) vat_finance_income,
                        DECODE(tlc.inside_or_outside, 'LOAN_DOMESTIC', T2.FINANCE_INCOME, 'LOAN_ABROAD', t2.FINANCE_INCOME / ROUND((1 + (NVL(tlc.withhold_income_tax, 0) + NVL(tlc.withhold_value_added_tax, 0) + NVL(tlc.withhold_sale_tax_add, 0)) / (1 - NVL(tlc.withhold_income_tax, 0) - (NVL(tlc.withhold_value_added_tax, 0) * NVL(tlc.withhold_sale_tax_add, 0)))), 2)) net_finance_income,
                        tlc.loan_contract_number
                        || '('
                        || q.document_number
                        || ')' document_number,
                        TLC.ORIGINAL_CON_NUM original_con_number,
                        tlc.loan_contract_name document_name,
                        (SELECT bp.bp_name FROM hls_bp_master bp WHERE bp.bp_id = tlc.loan_con_bp
                        ) bp_name,
                        (SELECT
                            'Y'
                        FROM
                            dual
                        WHERE
                            EXISTS
                            (SELECT
                                1
                            FROM
                                sdic_loan_unearned_income lci
                            WHERE
                                lci.quotation_id        = t2.quotation_id AND
                                lci.internal_period_num = t2.internal_period_num AND
                                lci.calc_end_date       = last_day(to_date(t2.internal_period_num
                                || '01', 'YYYYMMDD')) AND
                                lci.finance_income_recognized = 'Y'
                            )
                        ) finance_income_recognized,
                        TO_CHAR((ROUND(months_between(tlc.loan_date_to, tlc.loan_date_from), 0) / 12), 'FM990.0')
                        || '年' LEASE_TERM,
                        (SELECT
                            to_number(hd.lease_item_amount)
                        FROM
                            hls_fin_calculator_hd hd
                        WHERE
                            hd.calc_session_id = q.calc_session_id
                        ) lease_item_amount,
                        --(SELECT
                        --    '基准'
                        --    || v.code_value_name
                        --    || INT_RATE_FIXING_RANGE * 100
                        --    || '%'
                        --FROM
                        --    sys_code_values_v v,
                        --    hls_fin_calculator_hd hd
                        --WHERE
                        --    v.code             = 'TRE502_INT_RATE_FIXING_WAY' AND
                        --    v.code_value       = hd.INT_RATE_FIXING_WAY AND
                        --    hd.calc_session_id = q.calc_session_id
                        --)
                        (
                        SELECT
                            to_number(hd.int_rate)*100
                        FROM
                            hls_fin_calculator_hd hd
                        WHERE
                            hd.calc_session_id = q.calc_session_id
                        )
                        ||'%' rate_desc,
                        NVL((SELECT slui.weight_factor
             FROM sdic_loan_unearned_income slui
            WHERE slui.loan_contract_id = tlc.loan_contract_id
              AND slui.quotation_id = q.quotation_id
              and slui.quotation_id not in (1874, 1855, 1895,1857,2265,2023,1904,3404)
              AND slui.cf_item = 301
              AND slui.calc_end_date =
                  last_day(to_date(t2.internal_period_num || '01',
                                   'YYYY-MM-DD'))),
           0) provision_days,
           NVL((SELECT MIN(rp.ln_user_col_n19)
             FROM tre_loan_con_repayment_plan rp
            WHERE rp.loan_contract_id = tlc.loan_contract_id
              AND rp.quotation_id = q.quotation_id
              AND rp.repayment_type IN ('PRINCIPAL', 'INTEREST')
              AND TO_CHAR(rp.repayment_date, 'YYYYMM') <
                  t2.internal_period_num),
           (CASE
             WHEN q.lease_start_date <
                  last_day(to_date(t2.internal_period_num || '01', 'YYYY-MM-DD')) AND
                  TO_CHAR(q.lease_start_date, 'YYYYMM') <>
                  t2.internal_period_num THEN
              q.lease_item_amount
             ELSE
              0
           END)) - NVL((SELECT SUM(rp.principal)
                         FROM tre_loan_con_repayment_plan rp
                        WHERE rp.loan_contract_id = tlc.loan_contract_id
                          AND rp.quotation_id = q.quotation_id
                          AND rp.repayment_type = 'PRINCIPAL'
                          AND TO_CHAR(rp.repayment_date, 'YYYYMM') =
                              t2.internal_period_num),
                       0) month_end_principal
                    FROM
                        (SELECT
                            lci.quotation_id,
                            lci.internal_period_num,
                            --SUM(NVL(lci.unearned_income, 0))
                            lci.unearned_income FINANCE_INCOME
                        FROM
                            sdic_loan_unearned_income lci
                        WHERE
                            lci.calc_end_date = last_day(to_date(lci.internal_period_num
                            || '01', 'YYYYMMDD'))
                            --GROUP BY lci.quotation_id, lci.internal_period_num
                        ) t2,
                        prj_quotation q,
                        tre_loan_contract tlc
                    WHERE
                        t2.quotation_id                = q.quotation_id AND
                        t2.internal_period_num        >= 201601 AND
                        NVL(q.main_contract_flag, 'N') = 'N' AND
                        q.document_category            = 'LOAN_CONTRACT' AND
                        Q.DOCUMENT_ID                  = tlc.loan_contract_id
                        and q.quotation_id not in (1874, 1855, 1895,1857,2265,2023,1904,3404)
                    ) t #WHERE_CLAUSE#
                ORDER BY
                    t.CONTRACT_TYPE,
                    t.document_number ,
                    t.INTERNAL_PERIOD_NUM
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_type" queryExpression="t.contract_type=${@contract_type}"/>
        <bm:query-field name="document_number" queryExpression="t.document_number like ${@document_number}"/>
        <bm:query-field name="document_name" queryExpression="t.document_name like ${@document_name}"/>
        <bm:query-field name="bp_name" queryExpression="t.bp_name like ${@bp_name}"/>
        <bm:query-field name="internal_period_num" queryExpression="t.internal_period_num = ${@internal_period_num}"/>
        <bm:query-field name="plan_year" queryExpression="substr(t.internal_period_num,1,4) = ${@plan_year}"/>
        <bm:query-field name="plan_month" queryExpression="substr(t.internal_period_num,5,2)= ${@plan_month}"/>
    </bm:query-fields>
</bm:model>
