<?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
                        cc.contract_id,
                        cc.project_id,
                        cc.contract_number,
                        cc.contract_name,
                        (SELECT
                            pq.lease_item_amount
                        FROM
                            prj_quotation pq
                        WHERE
                            pq.document_category = 'CONTRACT' AND
                            pq.document_id       = cc.contract_id
                        ) lease_item_amount,
                        '基准('
                        || ROUND(cc.base_rate * 100, 2)
                        || '%)'
                        ||
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'TRE502_INT_RATE_FIXING_WAY' AND
                            v.code_value = cc.int_rate_fixing_way
                        )
                        || cc.int_rate_fixing_range * 100
                        || '%' int_rate_n,
                        (SELECT
                            NVL(SUM(NVL(ccc.received_principal, 0)), 0)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id                 = cc.contract_id AND
                            ccc.cf_item                     = 1 AND
                            ccc.cf_direction                = 'INFLOW' AND
                            NVL(ccc.write_off_flag, 'NOT') <> 'NOT' AND
                            (
                                to_date(${@date_to},'yyyy-mm-dd') IS NULL OR
                                ccc.due_date                      <= to_date(${@date_to},'yyyy-mm-dd')
                            )
                        ) received_principal,
                        (SELECT
                            NVL(SUM(NVL(ccc.principal, 0)), 0)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id  = cc.contract_id AND
                            ccc.cf_item      = 1 AND
                            ccc.cf_direction = 'INFLOW'
                        ) -
                        (SELECT
                            NVL(SUM(NVL(ccc.received_principal, 0)), 0)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id                 = cc.contract_id AND
                            ccc.cf_item                     = 1 AND
                            ccc.cf_direction                = 'INFLOW' AND
                            NVL(ccc.write_off_flag, 'NOT') <> 'NOT' AND
                            (
                                to_date(${@date_to},'yyyy-mm-dd') IS NULL OR
                                ccc.due_date                      <= to_date(${@date_to},'yyyy-mm-dd')
                            )
                        ) remain_principal,
                        (SELECT
                            NVL(SUM(NVL(cwo.write_off_due_amount, 0)), 0)
                        FROM
                            csh_write_off cwo
                        WHERE
                            cwo.write_off_type          = 'RECEIPT_CREDIT' AND
                            NVL(cwo.reversed_flag, 'N') = 'N' AND
                            cwo.contract_id             = cc.contract_id AND
                            cwo.write_off_date BETWEEN to_date(${@calc_date}
                            || '-01-01', 'YYYY-MM-DD') AND
                            to_date(${@calc_date}
                            || '-03-31', 'YYYY-MM-DD')
                        ) first_received_amount,
                        (SELECT
                            NVL(SUM(NVL(cwo.write_off_due_amount, 0)), 0)
                        FROM
                            csh_write_off cwo
                        WHERE
                            cwo.write_off_type          = 'RECEIPT_CREDIT' AND
                            NVL(cwo.reversed_flag, 'N') = 'N' AND
                            cwo.contract_id             = cc.contract_id AND
                            cwo.write_off_date BETWEEN to_date(${@calc_date}
                            || '-04-01', 'YYYY-MM-DD') AND
                            to_date(${@calc_date}
                            || '-06-30', 'YYYY-MM-DD')
                        ) second_received_amount,
                        (SELECT
                            NVL(SUM(NVL(cwo.write_off_due_amount, 0)), 0)
                        FROM
                            csh_write_off cwo
                        WHERE
                            cwo.write_off_type          = 'RECEIPT_CREDIT' AND
                            NVL(cwo.reversed_flag, 'N') = 'N' AND
                            cwo.contract_id             = cc.contract_id AND
                            cwo.write_off_date BETWEEN to_date(${@calc_date}
                            || '-07-01', 'YYYY-MM-DD') AND
                            to_date(${@calc_date}
                            || '-09-30', 'YYYY-MM-DD')
                        ) third_received_amount,
                        (SELECT
                            NVL(SUM(NVL(cwo.write_off_due_amount, 0)), 0)
                        FROM
                            csh_write_off cwo
                        WHERE
                            cwo.write_off_type          = 'RECEIPT_CREDIT' AND
                            NVL(cwo.reversed_flag, 'N') = 'N' AND
                            cwo.contract_id             = cc.contract_id AND
                            cwo.write_off_date BETWEEN to_date(${@calc_date}
                            || '-10-01', 'YYYY-MM-DD') AND
                            to_date(${@calc_date}
                            || '-12-31', 'YYYY-MM-DD')
                        ) forth_received_amount,
                        (SELECT
                            NVL(SUM(NVL(cwo.write_off_due_amount, 0)), 0)
                        FROM
                            csh_write_off cwo
                        WHERE
                            cwo.write_off_type          = 'RECEIPT_CREDIT' AND
                            NVL(cwo.reversed_flag, 'N') = 'N' AND
                            cwo.contract_id             = cc.contract_id AND
                            cwo.write_off_date BETWEEN to_date(${@calc_date}
                            || '-01-01', 'YYYY-MM-DD') AND
                            to_date(${@calc_date}
                            || '-12-31', 'YYYY-MM-DD')
                        ) year_received_amount
                    FROM
                        con_contract cc
                    WHERE
                        cc.data_class   = 'NORMAL' AND
                        cc.contract_id IN
                        (SELECT
                            cwo.contract_id
                        FROM
                            csh_write_off cwo
                        WHERE
                            cwo.write_off_type          = 'PAYMENT_DEBT' AND
                            NVL(cwo.reversed_flag, 'N') = 'N' AND
                            cwo.times                   = 0 AND
                            cwo.cf_type                 = 0 AND
                            cwo.cf_item                 = 0
                        )
                    ) t1 #WHERE_CLAUSE#
                ORDER BY
                    t1.contract_number
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
        <bm:query-field name="contract_name" queryExpression="t1.contract_name like &apos;%&apos;||${@contract_name}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
