<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: wujun  
    $Date: 2016年11月7日10:13:40
    $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
                    *
                FROM
                    (SELECT
                        pq.document_number,
                        tlc.loan_con_bp,
                        (SELECT
                            hbm.bp_name
                        FROM
                            hls_bp_master hbm
                        WHERE
                            hbm.bp_id = tlc.loan_con_bp
                        ) loan_bp_name,
                        tlcwp.amount,--本金金额
                        (NVL(
                        (SELECT
                            SUM(tlcrp.amount)
                        FROM
                            tre_loan_con_repayment_plan tlcrp
                        WHERE
                            tlcrp.repayment_date > to_date(${@last_repayment_date},'yyyy-mm-dd') AND
                            tlcrp.quotation_id   =tlcwp.quotation_id AND
                            tlcrp.repayment_type ='PRINCIPAL'
                        ),0)) surplus_amount,        --本金余额
                        tlc.inside_or_outside,        --境内境外
                        tlc.withhold_income_tax,      --所得税率
                        tlc.withhold_value_added_tax, --增值税率
                        tlc.withhold_sale_tax_add,    --增值税附加
                        tlcwp.quotation_id,
                        DECODE(tlc.inside_or_outside, 'LOAN_DOMESTIC', TO_CHAR(NVL(
                        (SELECT
                            slcrr.int_rate
                        FROM
                            sdic_loan_con_rate_record slcrr
                        WHERE
                            slcrr.quotation_id = tlcwp.quotation_id and rownum = 1 AND
                            to_date(${@last_repayment_date},'yyyy-mm-dd') BETWEEN slcrr.start_date AND slcrr.end_date
                        ),
                        (SELECT
                            slcrr.int_rate
                        FROM
                            sdic_loan_con_rate_record slcrr
                        WHERE
                            slcrr.quotation_id = tlcwp.quotation_id AND
                            slcrr.start_date   =
                            (SELECT
                                MAX(slcrr.start_date)
                            FROM
                                sdic_loan_con_rate_record slcrr
                            WHERE
                                slcrr.quotation_id=tlcwp.quotation_id
                            ) and rownum=1
                        )) * 100, 'FM90.0000')
                        || '%', 'LOAN_ABROAD', TO_CHAR( NVL(
                        (SELECT
                            slcrr.int_rate
                        FROM
                            sdic_loan_con_rate_record slcrr
                        WHERE
                            slcrr.quotation_id = tlcwp.quotation_id AND
                            to_date(${@last_repayment_date},'yyyy-mm-dd') BETWEEN slcrr.start_date AND slcrr.end_date
                        ),
                        (SELECT
                            slcrr.int_rate
                        FROM
                            sdic_loan_con_rate_record slcrr
                        WHERE
                            slcrr.quotation_id = tlcwp.quotation_id AND
                            slcrr.start_date   =
                            (SELECT
                                MAX(slcrr.start_date)
                            FROM
                                sdic_loan_con_rate_record slcrr
                            WHERE
                                slcrr.quotation_id=tlcwp.quotation_id
                            GROUP BY
                                slcrr.quotation_id
                            )
                       )) / (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_value_added_tax,0)) * 100, 'FM90.0000')
                        || '%') composite_rate, --综合利率
                        tlcwp.widthdrawal_date,    --提款日期
                        (SELECT
                            MAX(tlcrp.repayment_date)
                        FROM
                            tre_loan_con_repayment_plan tlcrp
                        WHERE
                            tlcwp.quotation_id = tlcrp.quotation_id
                        ) last_repayment_date, --最后一期还款日期(结束日期)
                        ROUND((
                        (SELECT
                            MAX(tlcrp.repayment_date)
                        FROM
                            tre_loan_con_repayment_plan tlcrp
                        WHERE
                            tlcwp.quotation_id = tlcrp.quotation_id
                        ) - tlcwp.widthdrawal_date) / 365, 2) contract_period, --合同期限
                        ROUND((
                        (SELECT
                            MAX(tlcrp.repayment_date)
                        FROM
                            tre_loan_con_repayment_plan tlcrp
                        WHERE
                            tlcwp.quotation_id = tlcrp.quotation_id
                        )-to_date(${@last_repayment_date},'yyyy-mm-dd'))/365,2) contract_surplus_period --合同剩余期限
                    FROM
                        prj_quotation pq,
                        tre_loan_contract tlc,
                        tre_loan_con_withdrawal_plan tlcwp
                    WHERE
                        tlcwp.loan_contract_id         = tlc.loan_contract_id AND
                        NVL(tlcwp.write_off_flag, 'N') = 'Y' AND
                        tlc.data_class               = 'NORMAL' AND
                        pq.quotation_id             = tlcwp.quotation_id
                    ) t1 #WHERE_CLAUSE#
                ORDER BY
                    t1.contract_period,
                    t1.document_number
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="last_repayment_date" queryExpression="t1.last_repayment_date &gt; TO_DATE(${@last_repayment_date},&apos;YYYY-MM-DD&apos;) and t1.widthdrawal_date &lt;= TO_DATE(${@last_repayment_date},&apos;YYYY-MM-DD&apos;)"/>
    </bm:query-fields>
</bm:model>
