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
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:s="leaf.plugin.script" xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
<bm:fields>
<bm:field name="contract_id"/>
<bm:field name="history_contract_id"/>
<bm:field name="contract_number"/>
<bm:field name="change_req_number"/>
<bm:field name="req_date" databaseType="DATE" datatype="java.util.Date"/>
<bm:field name="deal_way"/>
<bm:field name="business_type_n"/>
<bm:field name="before_bp_name"/>
<bm:field name="after_bp_name"/>
<bm:field name="lease_channel"/>
<bm:field name="lease_channel_n"/>
<bm:field name="bp_agent_name"/>
<bm:field name="extra_nam"/>
<bm:field name="journal_num"/>
<bm:field name="journal_date" databaseType="DATE" datatype="java.util.Date"/>
<bm:field name="service_charge_journal_num"/>
<bm:field name="service_charge_journal_date" databaseType="DATE" datatype="java.util.Date"/>
</bm:fields>
<bm:operations>
<bm:operation name="query">
<bm:query-sql><![CDATA[
select *
from (SELECT cc.contract_id,
cc.history_contract_id,
cr.change_req_number,
cr.req_date,
'承租人变更' deal_way,
cc.contract_number,
(SELECT ma.bp_name
FROM hls_bp_master ma
WHERE ma.bp_id =
(select c.bp_id_tenant
from con_contract c
where c.contract_id = cc.history_contract_id)) before_bp_name,
(SELECT ma.bp_name
FROM hls_bp_master ma
WHERE ma.bp_id = cc.bp_id_tenant) after_bp_name,
(SELECT c.lease_channel
FROM con_contract c
WHERE c.contract_id = cc.contract_id) lease_channel,
(Select ch.description
from hls_lease_channel ch
Where ch.lease_channel =
(SELECT c.lease_channel
FROM con_contract c
WHERE c.contract_id = cc.contract_id)) lease_channel_n,
(SELECT c.business_type
FROM con_contract c
WHERE c.contract_id = cc.contract_id) business_type,
(select bt.description
from hls_business_type bt
where bt.business_type =
(SELECT cc.business_type
FROM con_contract c
WHERE c.contract_id = cc.contract_id)) AS business_type_n,
(SELECT ma.bp_name
FROM hls_bp_master ma
WHERE ma.bp_id = cc.bp_id_agent_level1) bp_agent_name,
(SELECT ma.extra_nam
FROM hls_bp_master ma
WHERE ma.bp_id = cc.bp_id_agent_level1) extra_nam,
(select hj.journal_num
from hls_journal_header hj
where hj.source_table = 'CON_CONTRACT_CHANGE_REQ'
and hj.source_id = cr.change_req_id) journal_num,
(select hj.journal_date
from hls_journal_header hj
where hj.source_table = 'CON_CONTRACT_CHANGE_REQ'
and hj.source_id = cr.change_req_id) journal_date,
(select hj.journal_num
from hls_journal_header hj
where hj.source_table = 'CSH_WRITE_OFF'
and hj.source_id =
(select max(cwo.write_off_id)
from csh_write_off cwo
where cwo.contract_id = cr.contract_id
and cwo.cf_item = 304
and cwo.reversed_flag = 'N'
and cwo.create_je_flag = 'Y'
and cwo.times =
(select max(times)
from csh_write_off cwo
where cwo.contract_id = cr.contract_id
and cwo.cf_item = 304
and cwo.reversed_flag = 'N'
and cwo.create_je_flag = 'Y'))) service_charge_journal_num,
(select hj.journal_date
from hls_journal_header hj
where hj.source_table = 'CSH_WRITE_OFF'
and hj.source_id =
(select max(cwo.write_off_id)
from csh_write_off cwo
where cwo.contract_id = cr.contract_id
and cwo.cf_item = 304
and cwo.reversed_flag = 'N'
and cwo.create_je_flag = 'Y'
and cwo.times =
(select max(times)
from csh_write_off cwo
where cwo.contract_id = cr.contract_id
and cwo.cf_item = 304
and cwo.reversed_flag = 'N'
and cwo.create_je_flag = 'Y'))) service_charge_journal_date
FROM con_contract cc,
con_contract_change_req cr,
con_contract_case_record ccd
WHERE ccd.document_id = cr.change_req_id
and cc.contract_id = cr.change_req_id
and cc.data_class = 'CHANGE_REQ'
and cr.document_type = 'LEASSE_CHAG'
and ccd.document_table = 'CON_CONTRACT_CHANGE_REQ'
and ccd.status in ('APPROVING', 'APPROVED')
UNION ALL
SELECT cc.contract_id,
cc.history_contract_id,
cr.change_req_number,
cr.req_date,
'承租人变更' deal_way,
cc.contract_number,
(SELECT ma.bp_name
FROM hls_bp_master ma
WHERE ma.bp_id =
(select c.bp_id_tenant
from con_contract c
where c.contract_id = cc.history_contract_id)) before_bp_name,
(SELECT ma.bp_name
FROM hls_bp_master ma
WHERE ma.bp_id = cc.bp_id_tenant) after_bp_name,
(SELECT c.lease_channel
FROM con_contract c
WHERE c.contract_id = cc.contract_id) lease_channel,
(Select ch.description
from hls_lease_channel ch
Where ch.lease_channel =
(SELECT c.lease_channel
FROM con_contract c
WHERE c.contract_id = cc.contract_id)) lease_channel_n,
(SELECT c.business_type
FROM con_contract c
WHERE c.contract_id = cc.contract_id) business_type,
(select bt.description
from hls_business_type bt
where bt.business_type =
(SELECT cc.business_type
FROM con_contract c
WHERE c.contract_id = cc.contract_id)) AS business_type_n,
(SELECT ma.bp_name
FROM hls_bp_master ma
WHERE ma.bp_id = cc.bp_id_agent_level1) bp_agent_name,
(SELECT ma.extra_nam
FROM hls_bp_master ma
WHERE ma.bp_id = cc.bp_id_agent_level1) extra_nam,
null journal_num,
null journal_num,
null service_charge_journal_num,
null service_charge_journal_date
FROM con_contract cc, con_contract_change_req cr
WHERE cc.contract_id = cr.contract_id
and cr.history_flag = 'Y'
and cr.document_type = 'LEASSE_CHAG') t1
#WHERE_CLAUSE#
#ORDER_BY_CLAUSE#
]]></bm:query-sql>
</bm:operation>
</bm:operations>
<bm:query-fields>
<bm:query-field field="change_req_number" queryOperator="like"/>
<bm:query-field field="contract_number" queryOperator="like"/>
<bm:query-field field="extra_nam" queryOperator="like"/>
<bm:query-field field="bp_agent_name" queryOperator="like"/>
<bm:query-field name="req_date_from" queryExpression="t1.req_date >= to_date(${@req_date_from},'yyyy-mm-dd')"/>
<bm:query-field name="req_date_to" queryExpression="t1.req_date <= to_date(${@req_date_to},'yyyy-mm-dd')"/>
</bm:query-fields>
<s:bm-script><![CDATA[
var cx = Packages.leaf.javascript.Context.getCurrentContext();
Packages.leaf.plugin.script.engine.ScriptImportor.defineExternScript(cx, this, $ctx.getData(), "aut_authority_bm_validate.js");
]]></s:bm-script>
</bm:model>