<?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
                    hac.abs_contract_id,
                    hac.contract_id,
                    cc.contract_number,
                    pp.project_name,
                    hbm.bp_name,
                    cc.hn_industry_classification,
                    (SELECT
                        v.code_value_name
                    FROM
                        sys_code_values_v v
                    WHERE
                        v.code       = 'HN_INDUSTRY_CLASSIFICATION' AND
                        v.code_value = cc.hn_industry_classification
                    ) hn_industry_classification_n,
                    cc.binary_classification,
                    (SELECT
                        v.code_value_name
                    FROM
                        sys_code_values_v v
                    WHERE
                        v.code      IN ('WITHIN_GROUP', 'MARKETIZATION') AND
                        v.code_value = cc.binary_classification
                    ) binary_classification_n,
                    (SELECT
                        SUM(ccc.due_amount)
                    FROM
                        con_contract_cashflow ccc
                    WHERE
                        ccc.contract_id  = cc.contract_id AND
                        ccc.cf_item      in (1,3,15) AND
                        ccc.cf_direction = 'INFLOW' AND
                        ccc.due_date BETWEEN NVL(
                        (SELECT
                            t.repayment_date
                        FROM
                            hn_abs_repayment_plan t
                        WHERE
                            t.abs_id            = apr.abs_id AND
                            t.abs_project_times = arp.abs_project_times - 1
                        ), apr.start_date) AND
                        apr.calc_date
                    ) con_due_amount,
                    hn_abs_project_pkg.get_receive_amount_1(p_contract_id => hac.contract_id, p_start_date => NVL(
                    (SELECT
                        t.repayment_date
                    FROM
                        hn_abs_repayment_plan t
                    WHERE
                        t.abs_id            = apr.abs_id AND
                        t.abs_project_times = arp.abs_project_times - 1
                    ), apr.start_date), p_calc_date => apr.calc_date) con_receive_amount,
                    (SELECT
                        SUM(ccc.due_amount)
                    FROM
                        con_contract_cashflow ccc
                    WHERE
                        ccc.contract_id  = cc.contract_id AND
                        ccc.cf_item      in (1,3) AND
                        ccc.cf_direction = 'INFLOW' AND
                        ccc.due_date BETWEEN NVL(
                        (SELECT
                            t.repayment_date
                        FROM
                            hn_abs_repayment_plan t
                        WHERE
                            t.abs_id            = apr.abs_id AND
                            t.abs_project_times = arp.abs_project_times - 1
                        ), apr.start_date) AND
                        apr.calc_date
                    ) - hn_abs_project_pkg.get_receive_amount(p_contract_id => hac.contract_id, p_start_date => NVL(
                    (SELECT
                        t.repayment_date
                    FROM
                        hn_abs_repayment_plan t
                    WHERE
                        t.abs_id            = apr.abs_id AND
                        t.abs_project_times = arp.abs_project_times - 1
                    ), apr.start_date), p_calc_date => apr.calc_date) over_short_amount,
                    hn_abs_project_pkg.get_remain_amount(p_contract_id => hac.contract_id, p_start_date => NVL(
                    (SELECT
                        t.repayment_date
                    FROM
                        hn_abs_repayment_plan t
                    WHERE
                        t.abs_id            = apr.abs_id AND
                        t.abs_project_times = arp.abs_project_times - 1
                    ), apr.start_date), p_calc_date => apr.calc_date) remain_amount,
                    hn_abs_project_pkg.get_partial_receive_amount(p_contract_id => hac.contract_id, p_start_date => NVL(
                    (SELECT
                        t.repayment_date
                    FROM
                        hn_abs_repayment_plan t
                    WHERE
                        t.abs_id            = apr.abs_id AND
                        t.abs_project_times = arp.abs_project_times - 1
                    ), apr.start_date), p_calc_date => apr.calc_date) partial_receive_amount,
                    hn_abs_project_pkg.get_ninety_remain_amount(p_contract_id => hac.contract_id, p_start_date => NVL(
                    (SELECT
                        t.repayment_date
                    FROM
                        hn_abs_repayment_plan t
                    WHERE
                        t.abs_id            = apr.abs_id AND
                        t.abs_project_times = arp.abs_project_times - 1
                    ), apr.start_date), p_calc_date => apr.calc_date) ninety_remain_amount,
                    (SELECT ee.name FROM exp_employees ee WHERE ee.employee_id = cc.employee_id
                    ) employee_name,
                    (SELECT
                        hlo.description
                    FROM
                        hls_lease_organization hlo
                    WHERE
                        hlo.lease_organization = cc.lease_organization
                    ) lease_organization_n
                FROM
                    hn_abs_contract hac,
                    hn_abs_project_register apr,
                    hn_abs_repayment_plan arp,
                    con_contract cc,
                    prj_project pp,
                    hls_bp_master hbm
                WHERE
                    hac.contract_id       = cc.contract_id AND
                    cc.project_id         = pp.project_id AND
                    cc.bp_id_tenant       = hbm.bp_id AND
                    hac.abs_id            = apr.abs_id AND
                    hac.abs_id            = arp.abs_id AND
                    arp.repayment_plan_id = ${/parameter/@repayment_plan_id}
                    order by cc.contract_number
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
