[Home] [Help]
PACKAGE BODY: APPS.FII_AR_TRX_ACT_HISTORY_PKG
Source
1 PACKAGE BODY FII_AR_TRX_ACT_HISTORY_PKG AS
2 /* $Header: FIIARDBITAHB.pls 120.7.12000000.1 2007/02/23 02:29:12 applrt ship $ */
3
4 PROCEDURE get_trx_act_history (
5 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
6 trx_act_history_sql OUT NOCOPY VARCHAR2,
7 trx_act_history_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
8 IS
9
10 sqlstmt VARCHAR2(32000);
11
12 --Variable to implement drill on 'amount' column for Receipts
13 l_drill VARCHAR2(300);
14 l_app_rec_drill VARCHAR2(300);
15 l_trx_id_join VARCHAR2(100);
16 l_adjust_flag VARCHAR2(10);
17 l_trx_sql VARCHAR2(32000);
18 l_cust_all VARCHAR2(30);
19
20 BEGIN
21 --Call to reset the parameter variables
22 fii_ar_util_pkg.reset_globals;
23
24 --Call to get all the parameters in the report
25 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
26
27 /* Changes for bug 5086091
28
29 1. Changed the query to use Applied_customer_trx_id and not customer_trx_id in case of Receipts
30 2. Changed l_drill to send customer_trx_id and cash_receipt_id while drilling to Receipts Detail */
31
32 --Amount Drills
33
34 l_cust_all := 'All';
35
36 l_drill := 'pFunctionName=FII_AR_APP_RCT_DTL&TRX_NUM=FII_AR_CUST_TRX_ID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS='||l_cust_all||'&FII_AR_CASH_RECEIPT_ID=''||inline.FII_AR_CASH_RECEIPT_ID||''&pParamIds=Y';
37
38 l_app_rec_drill := 'DECODE(inline.LOOKUP_CODE, ''RECEIPT'', '''||l_drill||''')';
39
40 IF fii_ar_util_pkg.g_tran_class = 'CM' THEN
41 l_trx_id_join := 'customer_trx_id = :CUST_TRX_ID and';
42 ELSE
43 l_trx_id_join := 'applied_customer_trx_id = :CUST_TRX_ID and';
44 END IF;
45
46 -- Assigning l_adjust_flag to N when there is drill from Balance column
47 l_adjust_flag := 'N';
48
49 IF (p_page_parameter_tbl.count > 0) THEN
50 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
51
52 IF p_page_parameter_tbl(i).parameter_name = 'FII_AR_ADJUST_ONLY' THEN
53 l_adjust_flag := p_page_parameter_tbl(i).parameter_value;
54 END IF;
55 END LOOP;
56 END IF;
57
58 -- l_adjust_flag variable holds Y when drilled from Adjustments column
59
60 IF l_adjust_flag <> 'Y' THEN
61 l_trx_sql :=
62 'SELECT ''ENTRY'' LOOKUP_CODE,
63 amount_due_original_trx FII_AR_TRAN_AMT,
64 ar_creation_date FII_AR_TRAN_DATE,
65 user_id USER_ID,
66 :CUST_TRX_ID FII_AR_CUST_TRX_ID,
67 NULL FII_AR_CASH_RECEIPT_ID
68
69 FROM fii_ar_transactions_f
70
71 WHERE customer_trx_id = :CUST_TRX_ID and
72 filter_date <= TRUNC(sysdate) and
73 class = :TRAN_CLASS
74
75 UNION ALL
76
77 SELECT ''RECEIPT'' LOOKUP_CODE,
78 amount_applied_trx FII_AR_TRAN_AMT,
79 ar_creation_date FII_AR_TRAN_DATE,
80 user_id USER_ID,
81 :CUST_TRX_ID FII_AR_CUST_TRX_ID,
82 CASH_RECEIPT_ID FII_AR_CASH_RECEIPT_ID
83
84 FROM fii_ar_receipts_f
85
86 WHERE applied_customer_trx_id = :CUST_TRX_ID and
87 filter_date <= TRUNC(sysdate) and
88 application_type = ''CASH''
89
90 UNION ALL
91
92 SELECT ''CREDIT'' LOOKUP_CODE,
93 amount_applied_trx FII_AR_TRAN_AMT,
94 ar_creation_date FII_AR_TRAN_DATE,
95 user_id USER_ID,
96 :CUST_TRX_ID FII_AR_CUST_TRX_ID,
97 NULL FII_AR_CASH_RECEIPT_ID
98
99 FROM fii_ar_receipts_f
100
101 WHERE '||l_trx_id_join||'
102 filter_date <= TRUNC(sysdate) and
103 application_type = ''CM''
104
105 UNION ALL
106
107 SELECT ''DISC'' LOOKUP_CODE,
108 earned_discount_amount_trx FII_AR_TRAN_AMT,
109 ar_creation_date FII_AR_TRAN_DATE,
110 user_id USER_ID,
111 :CUST_TRX_ID FII_AR_CUST_TRX_ID,
112 NULL FII_AR_CASH_RECEIPT_ID
113
114 FROM fii_ar_receipts_f
115
116 WHERE applied_customer_trx_id = :CUST_TRX_ID and
117 earned_discount_amount_trx is not null and
118 filter_date <= TRUNC(sysdate) and
119 application_type = ''CASH''
120
121 UNION ALL
122
123 SELECT ''ASSIGN'' LOOKUP_CODE,
124 amount_trx FII_AR_TRAN_AMT,
125 ar_creation_date FII_AR_TRAN_DATE,
126 user_id USER_ID,
127 :CUST_TRX_ID FII_AR_CUST_TRX_ID,
128 NULL FII_AR_CASH_RECEIPT_ID
129
130 FROM fii_ar_adjustments_f
131
132 WHERE customer_trx_id = :CUST_TRX_ID and
133 filter_date <= TRUNC(sysdate) and
134 adj_class = ''BR''
135 UNION ALL
136 ';
137
138 END IF;
139
140 -- Report Query
141
142 sqlstmt := '
143 SELECT lookup.meaning FII_AR_ACTION,
144 inline.FII_AR_TRAN_AMT FII_AR_TRAN_AMT,
145 inline.FII_AR_TRAN_DATE FII_AR_TRAN_DATE,
146 users.user_name FII_AR_USER,
147 '||l_app_rec_drill||' FII_AR_TRAN_AMT_DRILL
148 FROM (
149 '||l_trx_sql||'
150 SELECT ''ADJ'' LOOKUP_CODE,
151 amount_trx FII_AR_TRAN_AMT,
152 ar_creation_date FII_AR_TRAN_DATE,
153 user_id USER_ID,
154 :CUST_TRX_ID FII_AR_CUST_TRX_ID,
155 NULL FII_AR_CASH_RECEIPT_ID
156
157 FROM fii_ar_adjustments_f
158
159 WHERE customer_trx_id = :CUST_TRX_ID and
160 filter_date <= TRUNC(sysdate) and
161 NVL(adj_class,''XX'') <> ''BR''
162
163 ) inline,
164 fnd_user users,
165 fnd_lookup_values lookup
166
167 WHERE inline.user_id = users.user_id
168 and lookup.lookup_type = ''FII_AR_TRX_ACTIONS''
169 and lookup.lookup_code = inline.lookup_code
170 and lookup.language = userenv(''LANG'')
171
172 &ORDER_BY_CLAUSE';
173
174 fii_ar_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, trx_act_history_sql, trx_act_history_output);
175
176 END get_trx_act_history;
177
178 END FII_AR_TRX_ACT_HISTORY_PKG;