rpt6090_abs_contract.lwm 6.48 KB
Newer Older
Spencer Chang's avatar
Spencer Chang committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139
<?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>