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
<?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" defaultOrderBy="t1.write_off_date desc">
<bm:operations>
<bm:operation name="query">
<bm:query-sql><![CDATA[
select *
from (select c.contract_id,
c.contract_number,
(select h.bp_name
from hls_bp_master h
where h.bp_id = c.bp_id_tenant) bp_name,
(select h.bp_name
from hls_bp_master h
where h.bp_id = c.bp_id_agent_level1) bp_agent_name,
(select ct.transaction_date
from csh_transaction ct
where ct.transaction_id = t.csh_transaction_id) transaction_date,
t.cf_item,
(SELECT hl.description
FROM hls_cashflow_item hl
WHERE hl.cf_item = t.cf_item) cf_item_n,
t.times,
t.write_off_classification,
(SELECT v.code_value_name
FROM sys_code_values_v v
WHERE v.code = 'DS_CSH_WRITE_OFF_CLASSIFICATION'
AND v.code_value = t.write_off_classification) write_off_classification_n,
t.write_off_due_amount,
nvl(t.write_off_principal, 0) write_off_principal,
nvl(t.write_off_interest, 0) write_off_interest,
(select cc.due_date
from con_contract_cashflow cc
where cc.cashflow_id = t.cashflow_id) due_date,
t.journal_date,
t.write_off_date,
NVL((select transaction_num
from csh_transaction
where transaction_id =
(select ct.source_csh_trx_id
from csh_transaction ct
where ct.transaction_id = t.csh_transaction_id)),
(select ct.transaction_num
from csh_transaction ct
where ct.transaction_id = t.csh_transaction_id)) transaction_num,
(select sum(cwr.csh_write_off_amount)
from csh_write_off cwr
where cwr.csh_transaction_id = t.csh_transaction_id
and cwr.contract_id = t.contract_id
and cwr.write_off_type = 'RECEIPT_CREDIT'
and cwr.reversed_flag = 'N'
group by cwr.csh_transaction_id) csh_write_off_amount_sum
from csh_write_off t, con_contract c
where t.contract_id = c.contract_id
and c.data_class = 'NORMAL'
and t.write_off_type = 'RECEIPT_CREDIT'
and t.reversed_flag = 'N') t1
#WHERE_CLAUSE# #ORDER_BY_CLAUSE#
]]></bm:query-sql>
</bm:operation>
</bm:operations>
<bm:fields>
<bm:field name="contract_id"/>
<bm:field name="contract_number"/>
<bm:field name="bp_name"/>
<bm:field name="bp_agent_name"/>
<bm:field name="transaction_date" databaseType="DATE" datatype="java.util.Date"/>
<bm:field name="cf_item"/>
<bm:field name="cf_item_n"/>
<bm:field name="times"/>
<bm:field name="write_off_classification"/>
<bm:field name="write_off_classification_n"/>
<bm:field name="write_off_due_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="write_off_principal" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="write_off_interest" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="due_date" databaseType="DATE" datatype="java.util.Date"/>
<bm:field name="journal_date" databaseType="DATE" datatype="java.util.Date"/>
<bm:field name="write_off_date" databaseType="DATE" datatype="java.util.Date"/>
<bm:field name="transaction_num"/>
<bm:field name="csh_write_off_amount_sum" databaseType="NUMBER" datatype="java.lang.Double"/>
</bm:fields>
<bm:query-fields>
<bm:query-field field="contract_number" queryOperator="like"/>
<bm:query-field field="bp_name" queryOperator="like"/>
<bm:query-field field="bp_agent_name" queryOperator="like"/>
<bm:query-field field="transaction_num" queryOperator="like"/>
<bm:query-field name="transaction_date_from" queryExpression="t1.transaction_date >= to_date(${@transaction_date_from},'yyyy-mm-dd')"/>
<bm:query-field name="transaction_date_to" queryExpression="t1.transaction_date <= to_date(${@transaction_date_to},'yyyy-mm-dd')"/>
<bm:query-field name="due_date_from" queryExpression="t1.due_date >= to_date(${@due_date_from},'yyyy-mm-dd')"/>
<bm:query-field name="due_date_to" queryExpression="t1.due_date <= to_date(${@due_date_to},'yyyy-mm-dd')"/>
<bm:query-field name="journal_date_from" queryExpression="t1.journal_date >= to_date(${@journal_date_from},'yyyy-mm-dd')"/>
<bm:query-field name="journal_date_to" queryExpression="t1.journal_date <= to_date(${@journal_date_to},'yyyy-mm-dd')"/>
<bm:query-field name="csh_write_off_amount_sum_from" queryExpression="t1.csh_write_off_amount_sum >= ${@csh_write_off_amount_sum_from}"/>
<bm:query-field name="csh_write_off_amount_sum_to" queryExpression="t1.csh_write_off_amount_sum <= ${@csh_write_off_amount_sum_to}"/>
</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>