<?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.hn_industry_classification,
                    (select pp.prj_scale from prj_project pp
                    where pp.project_id=cc.project_id) prj_scale,
                    cc.contract_number,
                    cc.contract_name,
                    (SELECT
                        MAX(cwo.write_off_date)
                    FROM
                        csh_write_off cwo
                    WHERE
                        cwo.write_off_type = 'PAYMENT_DEBT' AND
                        cwo.cf_item        = 0 AND
                        cwo.cf_type        = 0 AND
                        cwo.times          = 0 AND
                        cwo.contract_id    = cc.contract_id
                    ) write_off_date,
                    cc.lease_term,
                    pq.lease_item_amount,
                    NVL(
                    (SELECT
                        SUM(NVL(cwo.write_off_principal, 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.cf_item                           = 1 AND
                        cwo.cf_type                           = 1 AND
                        TO_CHAR(cwo.write_off_date, 'YYYYMM') = ${@calc_year}||${@calc_month}
                    ), 0) month_principal,
                    NVL(
                    (SELECT
                        SUM(NVL(cwo.write_off_principal, 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.cf_item                            = 1 AND
                        cwo.cf_type                            = 1 AND
                        TO_CHAR(cwo.write_off_date, 'YYYYMM') <= ${@calc_year}||${@calc_month}
                    ), 0) accumulate_principal,
                    pq.lease_item_amount - NVL(
                    (SELECT
                        SUM(NVL(cwo.write_off_principal, 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.cf_item                            = 1 AND
                        cwo.cf_type                            = 1 AND
                        TO_CHAR(cwo.write_off_date, 'YYYYMM') <= ${@calc_year}||${@calc_month}
                    ), 0) remain_principal,
                    NVL(
                    (SELECT
                        SUM(NVL(cwo.write_off_interest, 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.cf_item                           = 1 AND
                        cwo.cf_type                           = 1 AND
                        TO_CHAR(cwo.write_off_date, 'YYYYMM') = ${@calc_year}||${@calc_month}
                    ), 0) month_interest,
                    NVL(
                    (SELECT
                        SUM(NVL(cwo.write_off_interest, 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.cf_item                            = 1 AND
                        cwo.cf_type                            = 1 AND
                        TO_CHAR(cwo.write_off_date, 'YYYYMM') <= ${@calc_year}||${@calc_month}
                    ), 0) accumulate_interest,
                    nvl((select sum(nvl(ccc.interest, 0))
                     from con_contract_cashflow ccc
                    where ccc.contract_id = cc.contract_id
                      and ccc.cf_item = 1
                      and ccc.cf_type = 1),
                   0) received_interest,
                    NVL(
                    (SELECT
                        SUM(NVL(cwo.write_off_due_amount, 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.cf_item                           = 3 AND
                        cwo.cf_type                           = 3 AND
                        TO_CHAR(cwo.write_off_date, 'YYYYMM') = ${@calc_year}||${@calc_month}
                    ), 0) month_lease_charge,
                    NVL(
                    (SELECT
                        SUM(NVL(cwo.write_off_due_amount, 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.cf_item                            = 3 AND
                        cwo.cf_type                            = 3 AND
                        TO_CHAR(cwo.write_off_date, 'YYYYMM') <= ${@calc_year}||${@calc_month}
                    ), 0) accumulate_lease_charge,
                    nvl((select sum(nvl(ccc.due_amount, 0))
                     from con_contract_cashflow ccc
                    where ccc.contract_id = cc.contract_id
                      and ccc.cf_item = 3
                      and ccc.cf_type = 3),
                   0) received_lease_charge,
                    NVL(
                    (SELECT
                        SUM(NVL(cwo.write_off_interest, 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.cf_item                           = 1 AND
                        cwo.cf_type                           = 1 AND
                        TO_CHAR(cwo.write_off_date, 'YYYYMM') = ${@calc_year}||${@calc_month}
                    ), 0) + NVL(
                    (SELECT
                        SUM(NVL(cwo.write_off_due_amount, 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.cf_item                           = 3 AND
                        cwo.cf_type                           = 3 AND
                        TO_CHAR(cwo.write_off_date, 'YYYYMM') = ${@calc_year}||${@calc_month}
                    ), 0) month_sum_amount,
                    NVL(
                    (SELECT
                        SUM(NVL(cwo.write_off_interest, 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.cf_item                            = 1 AND
                        cwo.cf_type                            = 1 AND
                        TO_CHAR(cwo.write_off_date, 'YYYYMM') <= ${@calc_year}||${@calc_month}
                    ), 0) + NVL(
                    (SELECT
                        SUM(NVL(cwo.write_off_due_amount, 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.cf_item                            = 3 AND
                        cwo.cf_type                            = 3 AND
                        TO_CHAR(cwo.write_off_date, 'YYYYMM') <= ${@calc_year}||${@calc_month}
                    ), 0) accumulate_sum_amount,
                    nvl((select sum(nvl(ccc.interest, 0))
                     from con_contract_cashflow ccc
                    where ccc.contract_id = cc.contract_id
                      and ccc.cf_item = 1
                      and ccc.cf_type = 1),
                   0) + nvl((select sum(nvl(ccc.due_amount, 0))
                              from con_contract_cashflow ccc
                             where ccc.contract_id = cc.contract_id
                               and ccc.cf_item = 3
                               and ccc.cf_type = 3),
                            0) received_sum_amount
                FROM
                    con_contract cc,
                    prj_quotation pq
                WHERE
                    cc.data_class                 = 'NORMAL' AND
                    cc.hn_industry_classification = 'WITHIN_GROUP' AND
                    cc.contract_id                = pq.document_id AND
                    pq.document_category          = 'CONTRACT'
                    ) t1 #WHERE_CLAUSE#
            ]]></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-field name="write_off_date_from" queryExpression="t1.write_off_date &gt;=to_date(${@write_off_date_from},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="write_off_date_to" queryExpression="t1.write_off_date &lt;=to_date(${@write_off_date_to},&apos;YYYY-MM-DD&apos;)"/>
    </bm:query-fields>
</bm:model>
