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
<?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 t.*,
(t.transaction_amount - t.deduction_amount - t.refund_amount) balance_amount
FROM (SELECT (SELECT m.bp_name
FROM hls_bp_master m
WHERE m.bp_id = t1.bp_id) AS agent_name,
(SELECT m.bp_code
FROM hls_bp_master m
WHERE m.bp_id = t1.bp_id) AS agent_code,
t1.transaction_amount,
t1.transaction_id,
t1.transaction_date,
t1.transaction_type,
t1.bank_slip_num,
nvl((SELECT SUM(o.write_off_due_amount)
FROM csh_write_off o
WHERE o.csh_transaction_id = t1.transaction_id
AND o.reversed_flag = 'N'
AND o.write_off_type = 'RECEIPT_CREDIT'),
0) deduction_amount,
nvl((SELECT SUM(o.csh_write_off_amount)
FROM csh_write_off o
WHERE o.csh_transaction_id = t1.transaction_id
AND o.reversed_flag = 'N'
AND o.write_off_type = 'CSH_RETURN'),
0) refund_amount,
t1.company_id,
t1.internal_period_num,
t1.returned_amount,
t1.write_off_amount,
--csh_deposit_pkg.get_balance_amount_5(t1.bp_id) AS balance_amount_5,
t1.balance_amount_5,
(SELECT a.bank_account_num
FROM hls_bp_master_bank_account a
WHERE a.bank_account_id = t1.bp_bank_account_id) AS bp_bank_account_num,
(SELECT a.bank_account_code
FROM hls_bp_master_bank_account a
WHERE a.bank_account_id = t1.bp_bank_account_id) AS bp_bank_account_code,
(SELECT a.bank_account_name
FROM hls_bp_master_bank_account a
WHERE a.bank_account_id = t1.bp_bank_account_id) AS bp_bank_account_name,
(SELECT a.bank_account_code
FROM csh_bank_account a
WHERE a.bank_account_id = t1.bank_account_id
AND a.company_id = t1.company_id) AS bank_account_code,
(SELECT a.bank_account_name
FROM csh_bank_account a
WHERE a.bank_account_id = t1.bank_account_id
AND a.company_id = t1.company_id) AS bank_account_name,
(SELECT a.bank_account_num
FROM csh_bank_account a
WHERE a.bank_account_id = t1.bank_account_id
AND a.company_id = t1.company_id) AS bank_account_num,
gld_exchange_rate_pkg.get_currency_exchange(t1.transaction_amount,
t1.exchange_rate,
t1.exchange_rate_quotation) AS transaction_functional_amount,
t1.exchange_rate_type,
t1.exchange_rate_quotation,
t1.exchange_rate,
t1.period_name,
t1.transaction_num,
(t1.transaction_amount - nvl(t1.returned_amount, 0) -
nvl(t1.write_off_amount, 0)) remain_total_amount,
(SELECT gc.currency_name
FROM gld_currency_vl gc
WHERE gc.currency_code = t1.currency_code) AS currency_name,
t1.bp_id,
(SELECT d.bp_code
FROM hls_bp_master d
WHERE d.bp_id = t1.bp_id) AS bp_code,
(SELECT d.bp_name
FROM hls_bp_master d
WHERE d.bp_id = t1.bp_id) AS bp_name,
(SELECT b.description
FROM hls_bp_category b
WHERE b.bp_category = t1.bp_category) AS bp_category_display,
(SELECT tv.type_name
FROM gld_exchangerate_types_vl tv
WHERE tv.type_code = t1.exchange_rate_type) AS exchange_rate_type_display
FROM csh_transaction t1
WHERE t1.company_id = 1
AND transaction_category = 'BUSINESS'
AND t1.deposit_flag = 'Y'
-- AND t1.write_off_flag != 'FULL'
AND t1.returned_flag != 'FULL'
AND t1.posted_flag = 'Y'
AND t1.reversed_flag = 'N') t
#WHERE_CLAUSE#
#ORDER_BY_CLAUSE#
]]></bm:query-sql>
</bm:operation>
</bm:operations>
<bm:query-fields>
<bm:query-field name="transaction_id" queryExpression="t.transaction_id= ${@transaction_id}"/>
<bm:query-field name="agent_name" queryExpression="t.agent_name like '%'||${@agent_name}||'%'"/>
<bm:query-field name="agent_code" queryExpression="t.agent_code like '%'||${@agent_code}||'%'"/>
<bm:query-field name="balance_amount_from" queryExpression="(t.transaction_amount - t.deduction_amount - t.refund_amount) >= ${@balance_amount_from}"/>
<bm:query-field name="balance_amount_to" queryExpression="(t.transaction_amount - t.deduction_amount - t.refund_amount) <= ${@balance_amount_to}"/>
</bm:query-fields>
</bm:model>