financing_situation_analysis.lwm 6.46 KB
Newer Older
Spencer Chang's avatar
Spencer Chang committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135
<?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>