<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    rownum AS row_num,
                    tt.*
                FROM
                    (SELECT
                        t.loan_contract_number,
                        t.loan_contract_name,
                        t.business_type_desc,
                        t.loan_contract_id,
                        t.loan_con_bp_n,
                        t.original_con_num,
                        (SELECT
                            NVL(SUM(p.amount), 0)
                        FROM
                            TRE_LOAN_CON_REPAYMENT_PLAN p
                        WHERE
                            p.loan_contract_id = t.loan_contract_id AND
                            p.repayment_date  >= add_months(to_date(${@analysis_date}, 'yyyy-mm-dd'), 0) AND
                            p.repayment_date   < add_months(to_date(${@analysis_date}, 'yyyy-mm-dd'), 12)
                        ) amount_one, --1年内
                        (SELECT
                            NVL(SUM(p.amount), 0)
                        FROM
                            TRE_LOAN_CON_REPAYMENT_PLAN p
                        WHERE
                            p.loan_contract_id = t.loan_contract_id AND
                            p.repayment_date  >= add_months(to_date(${@analysis_date}, 'yyyy-mm-dd'), 12) AND
                            p.repayment_date   < add_months(to_date(${@analysis_date}, 'yyyy-mm-dd'), 24)
                        ) amount_one_two, --1-2年内
                        (SELECT
                            NVL(SUM(p.amount), 0)
                        FROM
                            TRE_LOAN_CON_REPAYMENT_PLAN p
                        WHERE
                            p.loan_contract_id = t.loan_contract_id AND
                            p.repayment_date  >= add_months(to_date(${@analysis_date}, 'yyyy-mm-dd'), 24) AND
                            p.repayment_date   < add_months(to_date(${@analysis_date}, 'yyyy-mm-dd'), 36)
                        ) amount_two_three, --2-3年
                        (SELECT
                            NVL(SUM(p.amount), 0)
                        FROM
                            TRE_LOAN_CON_REPAYMENT_PLAN p
                        WHERE
                            p.loan_contract_id = t.loan_contract_id AND
                            p.repayment_date  >= add_months(to_date(${@analysis_date}, 'yyyy-mm-dd'), 36)
                        ) amount_upper_three, --3年以上
                        (SELECT
                            NVL(SUM(p.amount), 0)
                        FROM
                            TRE_LOAN_CON_REPAYMENT_PLAN p
                        WHERE
                            p.loan_contract_id = t.loan_contract_id AND
                            p.repayment_date  >= add_months(to_date(${@analysis_date}, 'yyyy-mm-dd'), 0)
                        ) amount_sum --合计
                    FROM
                        tre_loan_contract_v t
                    WHERE
                        t.loan_contract_status = 'REVIEWED'
                    ) tt #WHERE_CLAUSE#
            ]]></bm:query-sql>
            <bm:parameters>
                <bm:parameter name="analysis_date"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="loan_contract_number" queryExpression="tt.loan_contract_number like ${@loan_contract_number}"/>
        <bm:query-field name="loan_con_bp_n" queryExpression="tt.loan_con_bp_n like &apos;%&apos;||${@loan_con_bp_n}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
