<?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
                        1 loan_contract_id,
                        1 quotation_id,
                        'calc_date' column_name,
                        '日期' column_prompt,
                        'center' column_align,
                        100 column_width,
                        'Leaf.formatDate' column_renderer,
                        'true' column_lock,
                        1 order_seq
                    FROM
                        dual
                    UNION
                    SELECT
                        wp.loan_contract_id,
                        wp.quotation_id,
                        'document_number_'
                        || wp.quotation_id AS column_name,
                        '提款编号' column_prompt,
                        'left' column_align,
                        130 column_width,
                        NULL column_renderer,
                        'false' column_lock,
                        2 order_seq
                    FROM
                        tre_loan_con_withdrawal_plan wp
                    WHERE
                        NVL(wp.write_off_flag, 'N') = 'Y'
                        and exists
                      (select 1
                               from tre_loan_contract tlct
                              where tlct.data_class = 'NORMAL'
                                and tlct.loan_contract_id =
                                    wp.loan_contract_id) AND
                        wp.widthdrawal_date        <= to_date(${/parameter/@calc_end_date}, 'YYYY-MM-DD')
                UNION
                SELECT
                    wp.loan_contract_id,
                    wp.quotation_id,
                    'loan_contract_name_'
                    || wp.quotation_id AS column_name,
                    '合同名称' column_prompt,
                    'left' column_align,
                    220 column_width,
                    NULL column_renderer,
                    'false' column_lock,
                    3 order_seq
                FROM
                    tre_loan_con_withdrawal_plan wp
                WHERE
                    NVL(wp.write_off_flag, 'N') = 'Y'
                    and exists
                      (select 1
                               from tre_loan_contract tlct
                              where tlct.data_class = 'NORMAL'
                                and tlct.loan_contract_id =
                                    wp.loan_contract_id) AND
                    wp.widthdrawal_date        <= to_date(${/parameter/@calc_end_date}, 'YYYY-MM-DD')
                UNION
                SELECT
                    wp.loan_contract_id,
                    wp.quotation_id,
                    'remain_principal_'
                    || wp.quotation_id AS column_name,
                    '本金余额' column_prompt,
                    'right' column_align,
                    120 column_width,
                    'Leaf.formatMoney' column_renderer,
                    'false' column_lock,
                    4 order_seq
                FROM
                    tre_loan_con_withdrawal_plan wp
                WHERE
                    NVL(wp.write_off_flag, 'N') = 'Y'
                    and exists
                      (select 1
                               from tre_loan_contract tlct
                              where tlct.data_class = 'NORMAL'
                                and tlct.loan_contract_id =
                                    wp.loan_contract_id) AND
                    wp.widthdrawal_date        <= to_date(${/parameter/@calc_end_date}, 'YYYY-MM-DD')
                UNION
                SELECT
                    wp.loan_contract_id,
                    wp.quotation_id,
                    'sum_principal_'
                    || wp.quotation_id AS column_name,
                    '本金借入' column_prompt,
                    'right' column_align,
                    120 column_width,
                    'Leaf.formatMoney' column_renderer,
                    'false' column_lock,
                    5 order_seq
                FROM
                    tre_loan_con_withdrawal_plan wp
                WHERE
                    NVL(wp.write_off_flag, 'N') = 'Y'
                    and exists
                      (select 1
                               from tre_loan_contract tlct
                              where tlct.data_class = 'NORMAL'
                                and tlct.loan_contract_id =
                                    wp.loan_contract_id) AND
                    wp.widthdrawal_date        <= to_date(${/parameter/@calc_end_date}, 'YYYY-MM-DD')
                UNION
                SELECT
                    wp.loan_contract_id,
                    wp.quotation_id,
                    'received_principal_'
                    || wp.quotation_id AS column_name,
                    '归还本金' column_prompt,
                    'right' column_align,
                    120 column_width,
                    'Leaf.formatMoney' column_renderer,
                    'false' column_lock,
                    6 order_seq
                FROM
                    tre_loan_con_withdrawal_plan wp
                WHERE
                    NVL(wp.write_off_flag, 'N') = 'Y'
                    and exists
                      (select 1
                               from tre_loan_contract tlct
                              where tlct.data_class = 'NORMAL'
                                and tlct.loan_contract_id =
                                    wp.loan_contract_id) AND
                    wp.widthdrawal_date        <= to_date(${/parameter/@calc_end_date}, 'YYYY-MM-DD')
                UNION
                SELECT
                    wp.loan_contract_id,
                    wp.quotation_id,
                    'received_interest_'
                    || wp.quotation_id AS column_name,
                    '归还利息' column_prompt,
                    'right' column_align,
                    120 column_width,
                    'Leaf.formatMoney' column_renderer,
                    'false' column_lock,
                    7 order_seq
                FROM
                    tre_loan_con_withdrawal_plan wp
                WHERE
                    NVL(wp.write_off_flag, 'N') = 'Y'
                    and exists
                      (select 1
                               from tre_loan_contract tlct
                              where tlct.data_class = 'NORMAL'
                                and tlct.loan_contract_id =
                                    wp.loan_contract_id) AND
                    wp.widthdrawal_date        <= to_date(${/parameter/@calc_end_date}, 'YYYY-MM-DD')
                UNION
                SELECT
                    wp.loan_contract_id,
                    wp.quotation_id,
                    'other_fee_'
                    || wp.quotation_id AS column_name,
                    '其他费用流出' column_prompt,
                    'right' column_align,
                    120 column_width,
                    'Leaf.formatMoney' column_renderer,
                    'false' column_lock,
                    8 order_seq
                FROM
                    tre_loan_con_withdrawal_plan wp
                WHERE
                    NVL(wp.write_off_flag, 'N') = 'Y'
                    and exists
                      (select 1
                               from tre_loan_contract tlct
                              where tlct.data_class = 'NORMAL'
                                and tlct.loan_contract_id =
                                    wp.loan_contract_id) AND
                    wp.widthdrawal_date        <= to_date(${/parameter/@calc_end_date}, 'YYYY-MM-DD')
                UNION
                SELECT
                    wp.loan_contract_id,
                    wp.quotation_id,
                    'int_rate_'
                    || wp.quotation_id AS column_name,
                    '执行利率' column_prompt,
                    'right' column_align,
                    100 column_width,
                    null column_renderer,
                    'false' column_lock,
                    9 order_seq
                FROM
                    tre_loan_con_withdrawal_plan wp
                WHERE
                    NVL(wp.write_off_flag, 'N') = 'Y' 
                    and exists
                      (select 1
                               from tre_loan_contract tlct
                              where tlct.data_class = 'NORMAL'
                                and tlct.loan_contract_id =
                                    wp.loan_contract_id) AND
                    wp.widthdrawal_date        <= to_date(${/parameter/@calc_end_date}, 'YYYY-MM-DD') 
                    ) t1 
                    order by t1.loan_contract_id,t1.quotation_id,order_seq
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
