<?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
                    1 order_seq,
                    '第'
                    || ccc.times
                    || '期本金租息' column1,
                    '第'
                    || ccc.times
                    || '期本金' column2,
                    '第'
                    || ccc.times
                    || '期租息' column3,
                    '第'
                    || ccc.times
                    || '期不含税租息' column4,
                    '已确认不含税租息' column5,
                    '本次确认不含税租息' column6,
                    '第'
                    || ccc.times
                    || '期增值税' column7,
                    '已确认增值税' column8,
                    '本次确认增值税' column9
                FROM
                    con_contract_cashflow ccc
                WHERE
                    ccc.contract_id = ${@contract_id} AND
                    ccc.times       = ${@times} AND
                    ccc.cf_item     = 1
                UNION
                SELECT
                    2 order_seq,
                    TO_CHAR(ccc.due_amount, 'FM999,999,999,999.00') column1,
                    TO_CHAR(ccc.principal, 'FM999,999,999,999.00') column2,
                    TO_CHAR(ccc.interest, 'FM999,999,999,999.00') column3,
                    TO_CHAR(ccc.net_interest, 'FM999,999,999,999.00') column4,
                    TO_CHAR(
                    (SELECT
                        SUM(cufi.unearned_finance_income)
                    FROM
                        con_unearned_finance_income cufi
                    WHERE
                        cufi.contract_id   = ccc.contract_id AND
                        cufi.times         = ccc.times AND
                        cufi.calc_end_date < ccc.due_date - 1
                    ) - sdic_report_pkg.get_sum_vat_finance_income(p_contract_id => ccc.contract_id, p_finance_income =>
                    (SELECT
                        SUM(cufi.unearned_finance_income)
                    FROM
                        con_unearned_finance_income cufi
                    WHERE
                        cufi.contract_id   = ccc.contract_id AND
                        cufi.times         = ccc.times AND
                        cufi.calc_end_date < ccc.due_date - 1
                    ), p_internal_period_num => NULL), 'FM999,999,999,999.00') column5,
                    TO_CHAR(
                    (SELECT
                        SUM(cufi.unearned_finance_income)
                    FROM
                        con_unearned_finance_income cufi
                    WHERE
                        cufi.contract_id   = ccc.contract_id AND
                        cufi.times         = ccc.times AND
                        cufi.calc_end_date = ccc.due_date - 1
                    ) - sdic_report_pkg.get_sum_vat_finance_income(p_contract_id => ccc.contract_id, p_finance_income =>
                    (SELECT
                        SUM(cufi.unearned_finance_income)
                    FROM
                        con_unearned_finance_income cufi
                    WHERE
                        cufi.contract_id   = ccc.contract_id AND
                        cufi.times         = ccc.times AND
                        cufi.calc_end_date = ccc.due_date - 1
                    ), p_internal_period_num => NULL), 'FM999,999,999,999.00') column6,
                    TO_CHAR(ccc.vat_interest, 'FM999,999,999,999.00') column7,
                    TO_CHAR(sdic_report_pkg.get_sum_vat_finance_income(p_contract_id => ccc.contract_id, p_finance_income =>
                    (SELECT
                        SUM(cufi.unearned_finance_income)
                    FROM
                        con_unearned_finance_income cufi
                    WHERE
                        cufi.contract_id   = ccc.contract_id AND
                        cufi.times         = ccc.times AND
                        cufi.calc_end_date < ccc.due_date - 1
                    ), p_internal_period_num => NULL), 'FM999,999,999,999.00') column8,
                    TO_CHAR(sdic_report_pkg.get_sum_vat_finance_income(p_contract_id => ccc.contract_id, p_finance_income =>
                    (SELECT
                        SUM(cufi.unearned_finance_income)
                    FROM
                        con_unearned_finance_income cufi
                    WHERE
                        cufi.contract_id   = ccc.contract_id AND
                        cufi.times         = ccc.times AND
                        cufi.calc_end_date = ccc.due_date - 1
                    ), p_internal_period_num => NULL), 'FM999,999,999,999.00') column9
                FROM
                    con_contract_cashflow ccc
                WHERE
                    ccc.contract_id = ${@contract_id} AND
                    ccc.times       = ${@times} AND
                    ccc.cf_item     = 1
                UNION
                SELECT
                    3 order_seq,
                    '第'
                    || ccc.times
                    || '期手续费' column1,
                    '不含税手续费' column2,
                    '增值税' column3,
                    NULL column4,
                    NULL column5,
                    NULL column6,
                    NULL column7,
                    NULL column8,
                    NULL column9
                FROM
                    con_contract_cashflow ccc
                WHERE
                    ccc.contract_id = ${@contract_id} AND
                    ccc.times       = ${@times} AND
                    ccc.cf_item     = 3
                UNION
                SELECT
                    4 order_seq,
                    TO_CHAR(ccc.due_amount, 'FM999,999,999,999.00') column1,
                    TO_CHAR(ccc.net_due_amount, 'FM999,999,999,999.00') column2,
                    TO_CHAR(ccc.vat_due_amount, 'FM999,999,999,999.00') column3,
                    NULL column4,
                    NULL column5,
                    NULL column6,
                    NULL column7,
                    NULL column8,
                    NULL column9
                FROM
                    con_contract_cashflow ccc
                WHERE
                    ccc.contract_id = ${@contract_id} AND
                    ccc.times       = ${@times} AND
                    ccc.cf_item     = 3
                ORDER BY
                    order_seq
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
