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>