<?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 * FROM
                   (SELECT tlcrp.quotation_id,
					       tlcrp.times,
					       to_char(tlcrp.repayment_date, 'YYYY-MM-DD') repayment_date,
					       tlcrp.interest_period_days,
					       tlcrp.interest,
					       (SELECT tlcr.principal
					          FROM tre_loan_con_repayment_plan tlcr
					         WHERE tlcr.quotation_id = tlcrp.quotation_id
					           AND tlcr.times = tlcrp.times
					           AND tlcr.repayment_type = 'PRINCIPAL') principal,
					       tlcrp.ln_user_col_n19,
					       TO_CHAR((SELECT tlcr.repayment_date
					                 FROM tre_loan_con_repayment_plan tlcr
					                WHERE tlcr.quotation_id = tlcrp.quotation_id
					                  AND tlcr.times = tlcrp.times
					                  AND tlcr.repayment_type = 'CONSULTATION'),
					               'YYYY-MM-DD') consulting_advisers_pay_day, --咨询费收取日期
					       NVL((SELECT tlcr.amount
					             FROM tre_loan_con_repayment_plan tlcr
					            WHERE tlcrp.quotation_id = tlcr.quotation_id
					              AND tlcr.times = tlcrp.times
					              AND tlcr.repayment_type = 'CONSULTATION'),
					           0) other_fee2,
					       TO_CHAR((SELECT tlcr.repayment_date
					                 FROM tre_loan_con_repayment_plan tlcr
					                WHERE tlcrp.quotation_id = tlcr.quotation_id
					                  AND tlcr.times = tlcrp.times
					                  AND tlcr.repayment_type = 'CHARGE'),
					               'YYYY-MM-DD') fee_pay_day, --手续费收取日期
					       NVL((SELECT tlcr.amount
					             FROM tre_loan_con_repayment_plan tlcr
					            WHERE tlcrp.quotation_id = tlcr.quotation_id
					              AND tlcr.times = tlcrp.times
					              AND tlcr.repayment_type = 'CHARGE'),
					           0) lease_charge,
					       TO_CHAR((SELECT tlcr.repayment_date
					                 FROM tre_loan_con_repayment_plan tlcr
					                WHERE tlcrp.quotation_id = tlcr.quotation_id
					                  AND tlcr.times = tlcrp.times
					                  AND tlcr.repayment_type = 'ARRANGEMENT'),
					               'YYYY-MM-DD') arrangement_the_fee_pay_day, --安排费收取日期
					       NVL((SELECT tlcr.amount
					             FROM tre_loan_con_repayment_plan tlcr
					            WHERE tlcrp.quotation_id = tlcr.quotation_id
					              AND tlcr.times = tlcrp.times
					              AND tlcr.repayment_type = 'ARRANGEMENT'),
					           0) arrangement,
					       TO_CHAR((SELECT tlcr.repayment_date
					                 FROM tre_loan_con_repayment_plan tlcr
					                WHERE tlcrp.quotation_id = tlcr.quotation_id
					                  AND tlcr.times = tlcrp.times
					                  AND tlcr.repayment_type = 'BTB_INTEREST'),
					               'YYYY-MM-DD') guarantee_fee_pay_day, --保函费收取日期
					       NVL((SELECT tlcr.amount
					             FROM tre_loan_con_repayment_plan tlcr
					            WHERE tlcrp.quotation_id = tlcr.quotation_id
					              AND tlcr.times = tlcrp.times
					              AND tlcr.repayment_type = 'BTB_INTEREST'),
					           0) btb_interest, --保函费
					       (SELECT SUM(ee.amount)
					          FROM tre_loan_con_repayment_plan ee
					         WHERE ee.quotation_id = tlcrp.quotation_id
					           AND ee.times = tlcrp.times) all_amounts
					  FROM tre_loan_con_repayment_plan tlcrp
					 WHERE tlcrp.quotation_id = ${@quotation_id}
					   AND tlcrp.repayment_type = 'INTEREST'
					UNION
					SELECT tlcrp.quotation_id,
					       tlcrp.times,
					       TO_CHAR(tlcrp.repayment_date, 'YYYY-MM-DD') repayment_date,
					       tlcrp.interest_period_days,
					       (SELECT tp.interest
					          FROM tre_loan_con_repayment_plan tp
					         WHERE tp.quotation_id = tlcrp.quotation_id
					           AND tp.times = tlcrp.times
					           AND tp.repayment_type = 'INTEREST') interest,
					       tlcrp.principal,
					       tlcrp.ln_user_col_n19,
					       TO_CHAR((SELECT tlcr.repayment_date
					                 FROM tre_loan_con_repayment_plan tlcr
					                WHERE tlcrp.quotation_id = tlcr.quotation_id
					                  AND tlcr.times = tlcrp.times
					                  AND tlcr.repayment_type = 'CONSULTATION'),
					               'YYYY-MM-DD') consulting_advisers_pay_day, --咨询费收取日期
					       NVL((SELECT tlcr.amount
					             FROM tre_loan_con_repayment_plan tlcr
					            WHERE tlcrp.quotation_id = tlcr.quotation_id
					              AND tlcr.times = tlcrp.times
					              AND tlcr.repayment_type = 'CONSULTATION'),
					           0) other_fee2,
					       TO_CHAR((SELECT tlcr.repayment_date
					                 FROM tre_loan_con_repayment_plan tlcr
					                WHERE tlcrp.quotation_id = tlcr.quotation_id
					                  AND tlcr.times = tlcrp.times
					                  AND tlcr.repayment_type = 'CHARGE'),
					               'YYYY-MM-DD') fee_pay_day, --手续费收取日期
					       NVL((SELECT tlcr.amount
					             FROM tre_loan_con_repayment_plan tlcr
					            WHERE tlcrp.quotation_id = tlcr.quotation_id
					              AND tlcr.times = tlcrp.times
					              AND tlcr.repayment_type = 'CHARGE'),
					           0) lease_charge,
					       TO_CHAR((SELECT tlcr.repayment_date
					                 FROM tre_loan_con_repayment_plan tlcr
					                WHERE tlcrp.quotation_id = tlcr.quotation_id
					                  AND tlcr.times = tlcrp.times
					                  AND tlcr.repayment_type = 'ARRANGEMENT'),
					               'YYYY-MM-DD') arrangement_the_fee_pay_day, --安排费收取日期
					       NVL((SELECT tlcr.amount
					             FROM tre_loan_con_repayment_plan tlcr
					            WHERE tlcrp.quotation_id = tlcr.quotation_id
					              AND tlcr.times = tlcrp.times
					              AND tlcr.repayment_type = 'ARRANGEMENT'),
					           0) arrangement,
					       TO_CHAR((SELECT tlcr.repayment_date
					                 FROM tre_loan_con_repayment_plan tlcr
					                WHERE tlcrp.quotation_id = tlcr.quotation_id
					                  AND tlcr.times = tlcrp.times
					                  AND tlcr.repayment_type = 'BTB_INTEREST'),
					               'YYYY-MM-DD') guarantee_fee_pay_day, --保函费收取日期
					       NVL((SELECT tlcr.amount
					             FROM tre_loan_con_repayment_plan tlcr
					            WHERE tlcrp.quotation_id = tlcr.quotation_id
					              AND tlcr.times = tlcrp.times
					              AND tlcr.repayment_type = 'BTB_INTEREST'),
					           0) btb_interest, --保函费
					       (SELECT SUM(ee.amount)
					          FROM tre_loan_con_repayment_plan ee
					         WHERE ee.quotation_id = tlcrp.quotation_id
					           AND ee.times = tlcrp.times) all_amounts
					  FROM tre_loan_con_repayment_plan tlcrp
					 WHERE tlcrp.quotation_id = ${@quotation_id}
					   AND tlcrp.repayment_type = 'PRINCIPAL'
					   AND NOT EXISTS (SELECT 1
					          FROM tre_loan_con_repayment_plan tt
					         WHERE tt.quotation_id = tlcrp.quotation_id
					           AND tt.times = tlcrp.times
					           AND tt.repayment_type IN ('INTEREST'))
					union --拼接0期
					SELECT tlcwp.quotation_id,
					       tlcwp.times,
					       TO_CHAR((tlcwp.widthdrawal_date), 'YYYY-MM-DD') repayment_date, --投放日期
					       null interest_period_days,
					       null interest,
					       null principal,
					       (tlcwp.amount) ln_user_col_n19, --投放金额
					       TO_CHAR((SELECT tlcr.repayment_date
					                 FROM tre_loan_con_repayment_plan tlcr
					                WHERE tlcr.quotation_id = tlcwp.quotation_id
					                  AND tlcr.times = tlcwp.times
					                  AND tlcr.repayment_type = 'CONSULTATION'),
					               'YYYY-MM-DD') consulting_advisers_pay_day, --咨询费收取日期
					       NVL((SELECT tlcr.amount
					             FROM tre_loan_con_repayment_plan tlcr
					            WHERE tlcr.quotation_id = tlcwp.quotation_id
					              AND tlcr.times = tlcwp.times
					              AND tlcr.repayment_type = 'CONSULTATION'),
					           0) other_fee2,
					       TO_CHAR((SELECT tlcr.repayment_date
					                 FROM tre_loan_con_repayment_plan tlcr
					                WHERE tlcr.quotation_id = tlcwp.quotation_id
					                  AND tlcr.times = tlcwp.times
					                  AND tlcr.repayment_type = 'CHARGE'),
					               'YYYY-MM-DD') fee_pay_day, --手续费收取日期
					       NVL((SELECT tlcr.amount
					             FROM tre_loan_con_repayment_plan tlcr
					            WHERE tlcwp.quotation_id = tlcr.quotation_id
					              AND tlcr.times = tlcwp.times
					              AND tlcr.repayment_type = 'CHARGE'),
					           0) lease_charge,
					       TO_CHAR((SELECT tlcr.repayment_date
					                 FROM tre_loan_con_repayment_plan tlcr
					                WHERE tlcwp.quotation_id = tlcr.quotation_id
					                  AND tlcr.times = tlcwp.times
					                  AND tlcr.repayment_type = 'ARRANGEMENT'),
					               'YYYY-MM-DD') arrangement_the_fee_pay_day, --安排费收取日期
					       NVL((SELECT tlcr.amount
					             FROM tre_loan_con_repayment_plan tlcr
					            WHERE tlcwp.quotation_id = tlcr.quotation_id
					              AND tlcr.times = tlcwp.times
					              AND tlcr.repayment_type = 'ARRANGEMENT'),
					           0) arrangement,
					       TO_CHAR((SELECT tlcr.repayment_date
					                 FROM tre_loan_con_repayment_plan tlcr
					                WHERE tlcwp.quotation_id = tlcr.quotation_id
					                  AND tlcr.times = tlcwp.times
					                  AND tlcr.repayment_type = 'BTB_INTEREST'),
					               'YYYY-MM-DD') guarantee_fee_pay_day, --保函费收取日期
					       NVL((SELECT tlcr.amount
					             FROM tre_loan_con_repayment_plan tlcr
					            WHERE tlcwp.quotation_id = tlcr.quotation_id
					              AND tlcr.times = tlcwp.times
					              AND tlcr.repayment_type = 'BTB_INTEREST'),
					           0) btb_interest, --保函费
					       null all_amounts
					  from tre_loan_con_withdrawal_plan tlcwp
					 where tlcwp.quotation_id = ${@quotation_id}
					   and nvl(tlcwp.write_off_flag, 'N') = 'Y'
                    ) t1 #WHERE_CLAUSE#
                ORDER BY
                    t1.times
            ]]></bm:query-sql>
            <!-- <bm:parameters>
                <bm:parameter name="quotation_id"/>
            </bm:parameters> -->
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="quotation_id" queryExpression="t1.quotation_id=${@quotation_id}"/>
    </bm:query-fields>
</bm:model>
