[Home] [Help]
PACKAGE BODY: APPS.FII_AR_REC_DETAIL_PKG
Source
1 PACKAGE BODY FII_AR_REC_DETAIL_PKG AS
2 /* $Header: FIIARDBIRDB.pls 120.27.12000000.2 2007/04/09 20:24:16 vkazhipu ship $ */
3
4 --------------------------------------------------
5 -- This procedure is called by the Receipts Detail
6 --------------------------------------------------
7 PROCEDURE get_rec_detail
8 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
9 p_pastdue_rec_aging_sql OUT NOCOPY VARCHAR2,
10 p_pastdue_rec_aging_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
11 IS
12 l_sqlstmt VARCHAR2(25000);
13 l_where_clause VARCHAR2(2000);
14 l_from_table VARCHAR2(1000);
15 l_currency VARCHAR2(10);
16
17 l_as_of_date DATE;
18 l_curr_suffix VARCHAR2(4);
19 l_itd_bitand NUMBER;
20 l_industry_id VARCHAR2(30);
21 l_collector_id VARCHAR2(30);
22 l_cust_id VARCHAR2(500);
23
24 l_rct_num_url VARCHAR2(500) := NULL;
25 l_rct_amt_url VARCHAR2(500) := NULL;
26 l_rct_app_amt_url VARCHAR2(1000) := NULL;
27
28 l_order_clause VARCHAR2(500);
29 l_order_column VARCHAR2(100);
30 l_order_null VARCHAR2(100);
31 l_order_by VARCHAR2(500);
32 l_source_report VARCHAR2(30);
33
34 l_bucket_num NUMBER;
35 l_bucket_low NUMBER;
36 l_bucket_high NUMBER;
37 l_bis_bucket_rec BIS_BUCKET_PUB.bis_bucket_rec_type;
38 l_return_status VARCHAR2(10);
39 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
40 l_unid_message VARCHAR2(30) := FND_MESSAGE.get_string('FII', 'FII_AR_UNID_CUSTOMER');
41
42 -- Bug 5118034
43 l_unapp_select_sql VARCHAR2(2000);
44 l_unapp_end_select_sql VARCHAR2(2000);
45
46 BEGIN
47 -- Reset all the global variables to NULL or to the default value
48 fii_ar_util_pkg.reset_globals;
49
50 -- Get the parameters and set the global variables
51 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
52
53 fii_ar_util_pkg.g_view_by := 'ORGANIZATION+FII_OPERATING_UNITS';
54
55 -- Retrieve values for global variables
56 l_as_of_date := fii_ar_util_pkg.g_as_of_date;
57 l_curr_suffix := fii_ar_util_pkg.g_curr_suffix;
58 l_collector_id := fii_ar_util_pkg.g_collector_id;
59 l_cust_id := fii_ar_util_pkg.g_party_id;
60 l_industry_id := fii_ar_util_pkg.g_industry_id;
61 l_itd_bitand := fii_ar_util_pkg.g_bitand_inc_todate;
62
63 -- Populate global temp table based on the parameters chosen
64 fii_ar_util_pkg.populate_summary_gt_tables;
65
66 -- Set the currency suffix for use in the amount columns
67 IF (l_curr_suffix = '_p_v') THEN
68 l_currency := '_prim';
69 ELSIF (l_curr_suffix = '_s_v') THEN
70 l_currency := '_sec';
71 ELSIF (l_curr_suffix = '_f_v') THEN
72 l_currency := '_func';
73 END IF;
74
75 ---------------------------------------------------------------------------
76 -- Find out additional parameters pass into Receipt Details via URL:
77 -- 1. Source report calling Receipt Details
78 -- 2. Bucket number from the source report
79 -- 3. Transaction number from the source report
80 -- 4. Customer account from the source report
81 ---------------------------------------------------------------------------
82 IF (p_page_parameter_tbl.count > 0) THEN
83 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
84
85 IF (p_page_parameter_tbl(i).parameter_name = 'BIS_FXN_NAME') THEN
86 l_source_report := p_page_parameter_tbl(i).parameter_value;
87
88 ELSIF (p_page_parameter_tbl(i).parameter_name = 'Bucket_Num') THEN
89 l_bucket_num := p_page_parameter_tbl(i).parameter_id;
90
91 END IF;
92
93 END LOOP;
94 END IF;
95
96 -----------------------------------------
97 -- Construct the conditional where clause
98 -----------------------------------------
99 -- Only add the join on collector_id if we have a specific collector selected
100 IF (l_collector_id <> '-111') THEN
101 l_from_table := l_from_table || 'fii_collectors col, ';
102 l_where_clause := l_where_clause ||
103 ' AND f.bill_to_customer_id = col.cust_account_id
104 AND f.bill_to_site_use_id = col.site_use_id ';
105 END IF;
106
107 -- Only add the join on cust_acct_id if we have a specific customer acct
108 -- selected
109
110 IF fii_ar_util_pkg.g_cust_account <> '-111' THEN
111 --l_from_table := l_from_table || ' fii_cust_accounts acct, ';
112 l_where_clause := l_where_clause ||
113 ' AND f.bill_to_customer_id= :CUST_ACCOUNT
114 AND acct.parent_party_id = :PARTY_ID
115 AND v.party_id = :PARTY_ID ';
116 -- Only add the join on party_id when we have a specific customer selected
117 -- and customer account is not selected
118 ELSIF l_cust_id <> '-111' THEN
119 --l_from_table := l_from_table || 'fii_cust_accounts acct, ';
120 l_where_clause := l_where_clause ||
121 ' AND acct.account_owner_party_id = :PARTY_ID
122 AND acct.account_owner_party_id = acct.parent_party_id
123 AND v.party_id = :PARTY_ID';
124 ELSE
125 l_where_clause := l_where_clause ||
126 ' AND acct.account_owner_party_id = acct.parent_party_id';
127 END IF;
128
129 -- Only add the join on class_category and class_code if we have a
130 -- specific industry selected
131 IF (l_industry_id <> '-111') THEN
132 l_from_table := l_from_table ||
133 'fii_cust_accounts acct2, fii_party_mkt_class ind, ';
134
135 l_where_clause := l_where_clause ||
136 ' AND f.bill_to_customer_id = acct2.cust_account_id
137 AND ind.party_id = acct2.Account_Owner_Party_ID
138 AND acct2.parent_party_id = acct2.account_owner_party_id
139 AND ind.class_category = v.class_category
140 AND ind.class_code = v.class_code ';
141 END IF;
142
143 ---------------------------------------------------------------------------
144 -- 1. If source form function = 'FII_AR_UNAPP_RCT_DTL', then select receipts
145 -- not fully applied as of the As of Date
146 ---------------------------------------------------------------------------
147 IF (l_source_report = 'FII_AR_UNAPP_RCT_DTL') THEN
148 l_where_clause := l_where_clause ||
149 ' AND f.filter_date <= :ASOF_DATE
150 AND f.rct_actual_date_closed > :ASOF_DATE';
151
152 -- Bug 5118034
153 --vkazhipu changed SELECT statement by adding SUM and corresponding GROUP BY for Bug
154 --5131795
155 l_unapp_select_sql :=
156 'SELECT
157 FII_AR_RCT_ACCT_NUM FII_AR_RCT_ACCT_NUM,
158 FII_AR_RCT_NUM FII_AR_RCT_NUM,
159 FII_AR_RCT_DATE FII_AR_RCT_DATE,
160 FII_AR_RCT_GL_DATE FII_AR_RCT_GL_DATE,
161 FII_AR_RCT_PAY_METHOD FII_AR_RCT_PAY_METHOD,
162 FII_AR_RCT_STATUS FII_AR_RCT_STATUS,
163 FII_AR_RCT_AMT_RCURR FII_AR_RCT_AMT_RCURR,
164 SUM(FII_AR_RCT_AMT) FII_AR_RCT_AMT,
165 SUM(FII_AR_RCT_APP_AMT) FII_AR_RCT_APP_AMT,
166 SUM(FII_AR_RCT_UNAPP_AMT) FII_AR_RCT_UNAPP_AMT,
167 SUM(FII_AR_RCT_EARNED_DCT) FII_AR_RCT_EARNED_DCT,
168 SUM(FII_AR_RCT_UNEARNED_DCT) FII_AR_RCT_UNEARNED_DCT,
169 SUM(SUM(FII_AR_RCT_AMT)) over() FII_AR_GT_RCT_AMT,
170 SUM(SUM(FII_AR_RCT_APP_AMT)) over() FII_AR_GT_RCT_APP_AMT,
171 SUM(SUM(FII_AR_RCT_UNAPP_AMT)) over() FII_AR_GT_RCT_UNAPP_AMT,
172 SUM(SUM(FII_AR_RCT_EARNED_DCT)) over() FII_AR_GT_RCT_EARNED_DCT,
173 SUM(SUM(FII_AR_RCT_UNEARNED_DCT)) over() FII_AR_GT_RCT_UNEARNED_DCT,
174 FII_AR_RCT_NUM_DRILL FII_AR_RCT_NUM_DRILL,
175 FII_AR_RCT_AMT_DRILL FII_AR_RCT_AMT_DRILL,
176 FII_AR_RCT_APP_AMT_DRILL FII_AR_RCT_APP_AMT_DRILL
177 FROM (';
178
179 l_unapp_end_select_sql := ' AND FII_AR_RCT_UNAPP_AMT <>0';
180 ---------------------------------------------------------------------------
181 -- 2. If source form function = 'FII_AR_UNAPP_X_RCT_DTL', then select
182 -- receipts that are not fully applied on the as of date, and the
183 -- receipt created [bucket x] days before the As of Date.
184 ---------------------------------------------------------------------------
185 ELSIF (l_source_report = 'FII_AR_UNAPP_X_RCT_DTL') THEN
186 -- Find out bucket information
187 BIS_BUCKET_PUB.retrieve_bis_bucket (
188 p_short_name => 'FII_DBI_UNAPP_RECEIPT_BUCKET',
189 x_bis_bucket_rec => l_bis_bucket_rec,
190 x_return_status => l_return_status,
191 x_error_tbl => l_error_tbl
192 );
193
194 -- Find out the bucket ranges for the bucket calling this report
195 -- Note that maximum bucket ranges for Unapplied Receipts Summary
196 -- is 3 buckets
197 IF (l_bucket_num = 1) THEN
198 l_bucket_low := l_bis_bucket_rec.range1_low;
199 l_bucket_high := (l_bis_bucket_rec.range1_high - 1);
200 ELSIF (l_bucket_num = 2) THEN
201 l_bucket_low := l_bis_bucket_rec.range2_low;
202 l_bucket_high := (l_bis_bucket_rec.range2_high -1);
203 ELSIF (l_bucket_num = 3) THEN
204 l_bucket_low := l_bis_bucket_rec.range3_low;
205 l_bucket_high := (l_bis_bucket_rec.range3_high-1);
206 END IF;
207
208 IF l_bucket_high is NULL THEN
209 l_where_clause := l_where_clause ||
210 ' AND f.rct_actual_date_closed > :ASOF_DATE
211 AND f.filter_date <= :ASOF_DATE
212 AND f.receipt_date <= (:ASOF_DATE - '
213 || l_bucket_low || ')';
214 ELSE
215 l_where_clause := l_where_clause ||
216 ' AND f.rct_actual_date_closed > :ASOF_DATE
217 AND f.filter_date <= :ASOF_DATE
218 AND f.receipt_date BETWEEN ( :ASOF_DATE - ' || l_bucket_high ||
219 ') AND (:ASOF_DATE - '
220 || l_bucket_low || ')';
221 END IF;
222
223 -- Bug 5118034
224 --vkazhipu changed SELECT statement by adding SUM and corresponding GROUP BY for Bug
225 --5131795
226 l_unapp_select_sql :=
227 'SELECT
228 FII_AR_RCT_ACCT_NUM FII_AR_RCT_ACCT_NUM,
229 FII_AR_RCT_NUM FII_AR_RCT_NUM,
230 FII_AR_RCT_DATE FII_AR_RCT_DATE,
231 FII_AR_RCT_GL_DATE FII_AR_RCT_GL_DATE,
232 FII_AR_RCT_PAY_METHOD FII_AR_RCT_PAY_METHOD,
233 FII_AR_RCT_STATUS FII_AR_RCT_STATUS,
234 FII_AR_RCT_AMT_RCURR FII_AR_RCT_AMT_RCURR,
235 SUM(FII_AR_RCT_AMT) FII_AR_RCT_AMT,
236 SUM(FII_AR_RCT_APP_AMT) FII_AR_RCT_APP_AMT,
237 SUM(FII_AR_RCT_UNAPP_AMT) FII_AR_RCT_UNAPP_AMT,
238 SUM(FII_AR_RCT_EARNED_DCT) FII_AR_RCT_EARNED_DCT,
239 SUM(FII_AR_RCT_UNEARNED_DCT) FII_AR_RCT_UNEARNED_DCT,
240 SUM(SUM(FII_AR_RCT_AMT)) over() FII_AR_GT_RCT_AMT,
241 SUM(SUM(FII_AR_RCT_APP_AMT)) over() FII_AR_GT_RCT_APP_AMT,
242 SUM(SUM(FII_AR_RCT_UNAPP_AMT)) over() FII_AR_GT_RCT_UNAPP_AMT,
243 SUM(SUM(FII_AR_RCT_EARNED_DCT)) over() FII_AR_GT_RCT_EARNED_DCT,
244 SUM(SUM(FII_AR_RCT_UNEARNED_DCT)) over() FII_AR_GT_RCT_UNEARNED_DCT,
245 FII_AR_RCT_NUM_DRILL FII_AR_RCT_NUM_DRILL,
246 FII_AR_RCT_AMT_DRILL FII_AR_RCT_AMT_DRILL,
247 FII_AR_RCT_APP_AMT_DRILL FII_AR_RCT_APP_AMT_DRILL
248 FROM (';
249
250 l_unapp_end_select_sql := 'AND FII_AR_RCT_UNAPP_AMT <>0';
251 ---------------------------------------------------------------------------
252 -- 3. If source form function = 'FII_AR_APP_RCT_DTL', then select receipts
253 -- partially or fully applied to the transaction chosen in the sourcel
254 -- report
255 ---------------------------------------------------------------------------
256 ELSIF (l_source_report = 'FII_AR_APP_RCT_DTL') THEN
257 -- Bug 5147703. Join on transaction id is not required
258 l_where_clause := l_where_clause ||
259 ' AND f.cash_receipt_id = :CASH_RECEIPT_ID';
260 END IF;
261
262 -------------------------------
263 -- Construct the drilldown URLs
264 -------------------------------
265 -- Receipt Number Drilldown URL
266 l_rct_num_url := 'pFunctionName=FII_AR_RCT_ACT_HISTORY&BIS_PMV_DRILL_CODE_FII_AR_RCT_CURRENCY=''||f.currency_code||
267 ''&BIS_PMV_DRILL_CODE_FII_CUSTOMER_ACCOUNT=''||f.bill_to_customer_id||
268 ''&BIS_PMV_DRILL_CODE_FII_AR_CASH_RECEIPT_ID=''||f.cash_receipt_id||
269 ''&BIS_PMV_DRILL_CODE_FII_AR_RCT_NUM=FII_AR_RCT_NUM&BIS_PMV_DRILL_CODE_AS_OF_DATE=sysdate'||
270 '&pParamIds=Y';
271
272 -- Receipt Amount Drilldown URL
273 l_rct_amt_url := 'pFunctionName=FII_AR_RCT_BALANCES_DTL&BIS_PMV_DRILL_CODE_FII_CUSTOMER_ACCOUNT=''||f.bill_to_customer_id||
274 ''&BIS_PMV_DRILL_CODE_FII_AR_CASH_RECEIPT_ID=''||f.cash_receipt_id||
275 ''&BIS_PMV_DRILL_CODE_FII_AR_RCT_NUM=FII_AR_RCT_NUM&BIS_PMV_DRILL_CODE_FII_AR_RCT_CURRENCY=''||f.currency_code||
276 ''&BIS_PMV_DRILL_CODE_AS_OF_DATE=sysdate&pParamIds=Y';
277
278 -- Applied Amount Drilldown URL
279 l_rct_app_amt_url := 'pFunctionName=FII_AR_PAID_REC_DTL&BIS_PMV_DRILL_CODE_FII_AR_CASH_RECEIPT_ID=''||f.cash_receipt_id||
280 ''&BIS_PMV_DRILL_CODE_FII_AR_CUST_ACCOUNT=''||f.bill_to_customer_id||
281 ''&pParamIds=Y';
282
283 ----------------------------------------------------------------
284 -- Find out the sort order column and construct the order clause
285 ----------------------------------------------------------------
286 l_order_clause := fii_ar_util_pkg.g_order_by;
287
288 -- Set the default order by clause for displaying NULL last when
289 -- we sort in descending order
290 IF (instr(l_order_clause, 'DATE') <> 0) THEN
291 -- The sort column is a date column
292 l_order_null := 'to_date(''0001/12/31'',''YYYY/MM/DD'')';
293
294 ELSIF (instr(l_order_clause, 'NLSSORT') <> 0) THEN
295 -- The sort column is a VARCHAR2 column
296 l_order_null := 'NLSSORT(''000000000'', ''NLS_SORT=BINARY'')';
297
298 ELSE
299 -- The sort column is a numeric column
300 l_order_null := '-999999999';
301 END IF;
302
303 -- Set the order by clause for the PMV sql
304 IF (instr(l_order_clause, 'FII_AR_RCT_AMT_RCURR') <> 0) THEN
305 -------------------------------------------------------------
306 -- Special treatment for the Receipt Amount (Receipt Currency)
307 -- column. We should sort this as if it is a numeric column
308 -- based on the receipt amount value.
309 -------------------------------------------------------------
310 IF l_source_report <> 'FII_AR_APP_RCT_DTL' THEN
311 --In case of Unapplied Receipt Detail source the sql structure is different
312 l_order_column := substr(fii_ar_util_pkg.g_order_by, 1,
313 instr(l_order_clause, ' DESC'));
314 IF (l_order_column is NULL) THEN
315 --Ascending order
316 l_order_by := ' &ORDER_BY_CLAUSE';
317 ELSE
318 --Descending Order
319 l_order_by := ' ORDER BY NVL('|| l_order_column || ', '
320 || l_order_null || ' ) DESC';
321 END IF;
322 ELSE
323 --In case of Applied Receipts Detail the query structure is different
324 IF (instr(l_order_clause, ' DESC') <> 0) THEN
325 l_order_by := ' ORDER BY NVL(sum(f.amount_applied_rct), -999999999) DESC,
326 NVL(f.currency_code, NLSSORT(''000000000'',
327 ''NLS_SORT=BINARY'')) DESC';
328 ELSE
329 l_order_by := ' ORDER BY NVL(sum(f.amount_applied_rct), -999999999) ASC,
330 NVL(f.currency_code, NLSSORT(''000000000'',
331 ''NLS_SORT=BINARY'')) ASC';
332 END IF;
333 END IF;
334
335 ELSIF ((instr(l_order_clause, ',') <> 0) AND
336 (instr(l_order_clause, 'NLSSORT') = 0)) THEN
337 -------------------------------------------------------------
338 -- No particular sort column is selected in the report. We'll
339 -- sort on the default column in descending order. NVL is
340 -- added ot make sure NULL will appear last.
341 -------------------------------------------------------------
342 l_order_by := ' ORDER BY NVL(FII_AR_RCT_AMT, -999999999) DESC';
343
344 ELSIF (instr(l_order_clause, ' DESC') <> 0) THEN
345 -------------------------------------------------------------
346 -- User has asked for a descending order sort. We'll also
347 -- make sure NULL will appear last with the default order clause.
348 -------------------------------------------------------------
349 l_order_column := substr(fii_ar_util_pkg.g_order_by, 1,
350 instr(l_order_clause, ' DESC'));
351 l_order_by := ' ORDER BY NVL('|| l_order_column || ', '
352 || l_order_null || ' ) DESC';
353
354 ELSE
355 -------------------------------------------------------------
356 -- User has asked for an ascending order sort. We should use
357 -- PMV's order by clause
358 -------------------------------------------------------------
359 l_order_by := ' &ORDER_BY_CLAUSE';
360
361 END IF;
362 -------------------------------
363 -- Construct the sql statements
364 -------------------------------
365
366 l_sqlstmt :='SELECT FII_AR_RCT_ACCT_NUM FII_AR_RCT_ACCT_NUM,
367 FII_AR_RCT_NUM,FII_AR_RCT_DATE,
368 FII_AR_RCT_GL_DATE,
369 m.name FII_AR_RCT_PAY_METHOD ,
370 DECODE(l.lookup_code,''NSF'',l.meaning, ''REV'',l.meaning ,''STOP'', l.meaning, ''-'') FII_AR_RCT_STATUS,
371 FII_AR_RCT_AMT_RCURR,
372 SUM(FII_AR_RCT_AMT) FII_AR_RCT_AMT,
373 SUM(FII_AR_RCT_APP_AMT) FII_AR_RCT_APP_AMT,
374 SUM(FII_AR_RCT_UNAPP_AMT) FII_AR_RCT_UNAPP_AMT,
375 SUM(FII_AR_RCT_EARNED_DCT) FII_AR_RCT_EARNED_DCT,
376 SUM(FII_AR_RCT_UNEARNED_DCT) FII_AR_RCT_UNEARNED_DCT,
377 SUM(SUM(FII_AR_RCT_AMT)) over() FII_AR_GT_RCT_AMT,
378 SUM(SUM(FII_AR_RCT_APP_AMT)) over() FII_AR_GT_RCT_APP_AMT,
379 SUM(SUM(FII_AR_RCT_UNAPP_AMT)) over() FII_AR_GT_RCT_UNAPP_AMT,
380 SUM(SUM(FII_AR_RCT_EARNED_DCT)) over() FII_AR_GT_RCT_EARNED_DCT,
381 SUM(SUM(FII_AR_RCT_UNEARNED_DCT)) over() FII_AR_GT_RCT_UNEARNED_DCT,
382 FII_AR_RCT_NUM_DRILL,
383 FII_AR_RCT_AMT_DRILL,
384 FII_AR_RCT_APP_AMT_DRILL
385 FROM(
386 SELECT /*+ no_merge leading(v) cardinality(v 1) */ NVL(acct.account_number,'''||l_unid_message||''') FII_AR_RCT_ACCT_NUM, --acct.account_number account_number,
387 f.receipt_number FII_AR_RCT_NUM,
388 f.receipt_date FII_AR_RCT_DATE,
389 f.gl_date FII_AR_RCT_GL_DATE,
390 f.header_status header_status,
391 to_char(SUM(f.amount_applied_rct), ''999,999,999'')
392 || '' '' || f.currency_code FII_AR_RCT_AMT_RCURR,
393 sum(f.amount_applied_rct' || l_currency || ') FII_AR_RCT_AMT,
394 sum(decode(f.application_status,
395 ''APP'', f.amount_applied_rct' || l_currency || ',
396 0)) FII_AR_RCT_APP_AMT,
397 sum(decode(f.application_status,
398 ''UNAPP'', f.amount_applied_rct'|| l_currency || ',
399 ''UNID'', f.amount_applied_rct'|| l_currency || ',
400 0)) FII_AR_RCT_UNAPP_AMT,
401 sum(f.earned_discount_amount' || l_currency || ') FII_AR_RCT_EARNED_DCT,
402 sum(f.unearned_discount_amount'||l_currency||') FII_AR_RCT_UNEARNED_DCT,
403 sum(sum(f.amount_applied_rct' || l_currency || ')) over() FII_AR_GT_RCT_AMT,
404 sum(sum(decode(f.application_status,
405 ''APP'', f.amount_applied_rct' || l_currency || ',
406 0))) over() FII_AR_GT_RCT_APP_AMT,
407 sum(sum(decode(f.application_status,
408 ''UNAPP'', f.amount_applied_rct'|| l_currency || ',
409 ''UNID'', f.amount_applied_rct'|| l_currency || ',
410 0))) over() FII_AR_GT_RCT_UNAPP_AMT,
411 sum(sum(f.earned_discount_amount' || l_currency || ')) over() FII_AR_GT_RCT_EARNED_DCT,
412 sum(sum(f.unearned_discount_amount'||l_currency||')) over() FII_AR_GT_RCT_UNEARNED_DCT,
413 decode(f.receipt_number, NULL, NULL, ''' ||
414
415 l_rct_num_url || ''') FII_AR_RCT_NUM_DRILL,
416
417 decode(sum(f.amount_applied_rct' || l_currency || '), 0, NULL, NULL, NULL, ''' ||
418 l_rct_amt_url || ''') FII_AR_RCT_AMT_DRILL,
419
420 decode(sum(f.amount_applied_trx' || l_currency || '), 0, NULL, NULL, NULL,
421
422 ''' || l_rct_app_amt_url || ''') FII_AR_RCT_APP_AMT_DRILL,
423 f.bill_to_customer_id, f.receipt_method_id
424 FROM fii_ar_receipts_f f, fii_cust_accounts acct,
425 '||l_from_table || '
426 FII_AR_SUMMARY_GT v
427 WHERE f.org_id = v.org_id
428 AND f.bill_to_customer_id = acct.cust_account_id '
429 || l_where_clause ||'
430 GROUP BY NVL(acct.account_number,'''||l_unid_message||''') , f.receipt_number, f.receipt_date, f.gl_date, f.cash_receipt_id,
431 f.currency_code, f.bill_to_customer_id, f.header_status, f.receipt_method_id) fact , ar_lookups l,
432 ar_receipt_methods m
433 WHERE fact.receipt_method_id = m.receipt_method_id
434 AND l.lookup_type = ''CHECK_STATUS''
435 AND l.lookup_code = fact.header_status ' || l_unapp_end_select_sql ||'
436 GROUP BY FII_AR_RCT_ACCT_NUM,
437 FII_AR_RCT_NUM,
438 FII_AR_RCT_DATE,
439 FII_AR_RCT_GL_DATE,
440 m.name,
441 l.lookup_code, l.meaning ,
442 FII_AR_RCT_AMT_RCURR,
443 FII_AR_RCT_NUM_DRILL,
444 FII_AR_RCT_AMT_DRILL,
445 FII_AR_RCT_APP_AMT_DRILL ' || l_order_by;
446
447 -- Bind variables so that no literal will be used in the pmv report
448 fii_ar_util_pkg.bind_variable
449 (p_sqlstmt => l_sqlstmt,
450 p_page_parameter_tbl => p_page_parameter_tbl,
451 p_sql_output => p_pastdue_rec_aging_sql,
452 p_bind_output_table => p_pastdue_rec_aging_output);
453
454 END get_rec_detail;
455
456 ----------------------------------------------------------
457 -- This procedure is called by the Receipt Balances Detail
458 ----------------------------------------------------------
459 PROCEDURE get_rec_bal_detail
460 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
461 p_pastdue_rec_aging_sql OUT NOCOPY VARCHAR2,
462 p_pastdue_rec_aging_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
463 IS
464 l_sqlstmt VARCHAR2(25000);
465 l_where_clause VARCHAR2(2000);
466 l_from_table VARCHAR2(1000);
467
468 l_as_of_date DATE;
469 l_itd_bitand NUMBER;
470 l_collector_id VARCHAR2(30);
471 l_cust_id VARCHAR2(500);
472 l_rct_num VARCHAR2(30);
473 l_rct_curr VARCHAR2(30);
474
475 l_rct_amt_url VARCHAR2(500) := NULL;
476
477 BEGIN
478 -- Reset all the global variables to NULL or to the default value
479 fii_ar_util_pkg.reset_globals;
480
481 -- Get the parameters and set the global variables
482 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
483
484 fii_ar_util_pkg.g_view_by := 'ORGANIZATION+FII_OPERATING_UNITS';
485
486 -- Retrieve values for global variables
487 l_as_of_date := fii_ar_util_pkg.g_as_of_date;
488 l_collector_id := fii_ar_util_pkg.g_collector_id;
489 l_cust_id := fii_ar_util_pkg.g_party_id;
490 l_itd_bitand := fii_ar_util_pkg.g_bitand_inc_todate;
491
492 -- Populate global temp table based on the parameters chosen
493 fii_ar_util_pkg.populate_summary_gt_tables;
494
495 IF (p_page_parameter_tbl.count > 0) THEN
496 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
497
498 IF (p_page_parameter_tbl(i).parameter_name = 'FII_AR_RCT_NUM') THEN
499 l_rct_num := p_page_parameter_tbl(i).parameter_value;
500
501 ELSIF (p_page_parameter_tbl(i).parameter_name = 'FII_AR_RCT_CURRENCY') THEN
502 l_rct_curr := p_page_parameter_tbl(i).parameter_value;
503 END IF;
504 END LOOP;
505 END IF;
506
507 IF l_cust_id <> -111 THEN
508 l_where_clause := 'AND gt.party_id = :PARTY_ID';
509 END IF;
510 ----------------------------------------------------------------------------
511 -- Construct the drilldown URLs
512 -- <arcdixit> Bug 5060164.
513 -- Modified the drill to pass the following to Paid Receivables Detail report
514 -- 1. cash_receipt_id
515 -- 2. applied_customer_trx_id (To be Removed as per discussion with Renu)
516 -- 3. cust_account_id
517 -----------------------------------------------------------------------------
518 -- Receipt Amount Drilldown URL
519 l_rct_amt_url := 'pFunctionName=FII_AR_PAID_REC_DTL&BIS_PMV_DRILL_CODE_FII_AR_CASH_RECEIPT_ID=''||v2.cash_receipt_id||
520 ''&BIS_PMV_DRILL_CODE_FII_AR_CUST_ACCOUNT=''||v2.cust_account_id||
521 ''&pParamIds=Y';
522
523 -------------------------------------------------------------------------------------------------------------------
524 -- Construct the sql statements
525 -- <arcdixit> Bug 5060164.
526 -- 1. applied_payment_schedule_id > 0 is for the applied receipts, added applied_payment_schedule_id is NULL
527 -- for cases when the receipt is unapplied.
528 -- 2. Use application_status instead of header_status in the sql
529 -- 3. Remove the join with fii_time_structures tables based on as_of_date
530 -- 4. Added cash_receipt_id, applied_customer_trx_id and cust_account_id for drill to Paid Receivables Detail report
531 -- 5. Decode should be on lookup_code and not meaning. Changed the same
532 ---------------------------------------------------------------------------------------------------------------------
533 l_sqlstmt :=
534 'SELECT
535 lv.meaning FII_AR_RCT_BALANCE,
536 nvl(sum(v2.amount), 0) FII_AR_RCT_AMT,
537 decode(lv.lookup_code, ''APP'',
538 decode(nvl(sum(v2.amount), 0),
539 0, NULL,
540 ''' || l_rct_amt_url || '''),
541 NULL) FII_AR_RCT_AMT_DRILL
542 FROM (
543 SELECT /*+ leading(gt) cardinality(gt 1) */ f.application_status status,
544 sum(f.amount_applied_rct) amount,
545 f.cash_receipt_id,
546 :CUST_ACCOUNT_ID cust_account_id
547 FROM fii_ar_receipts_f f, '||l_from_table || ' fii_ar_summary_gt gt
548 WHERE f.org_id = gt.org_id
549 AND f.cash_receipt_id = :CASH_RECEIPT_ID
550 AND f.currency_code = '''|| l_rct_curr || '''
551 AND (f.applied_payment_schedule_id > 0 OR f.applied_payment_schedule_id is NULL)
552 ' || l_where_clause || '
553 GROUP BY f.application_status,
554 f.cash_receipt_id,
555 :CUST_ACCOUNT_ID
556 UNION ALL
557 SELECT /*+ leading(gt) cardinality(gt 1) */ decode(applied_payment_schedule_id,
558 -2, ''OTHER'', -3, ''OTHER'', -5, ''OTHER'',
559 -6, ''OTHER'', -8, ''OTHER'', -9, ''OTHER'',
560 -1, ''ONACC'',
561 -4, ''CASH'',
562 -7, ''PREPAY'') status,
563 sum(f.amount_applied_rct) amount,
564 f.cash_receipt_id,
565 :CUST_ACCOUNT_ID cust_account_id
566 FROM fii_ar_receipts_f f, '||l_from_table || ' fii_ar_summary_gt gt
567 WHERE f.org_id = gt.org_id
568 AND f.cash_receipt_id = :CASH_RECEIPT_ID
569 AND f.currency_code = '''|| l_rct_curr || '''
570 AND f.applied_payment_schedule_id < 0
571 ' || l_where_clause || '
572 GROUP BY decode(applied_payment_schedule_id,
573 -2, ''OTHER'', -3, ''OTHER'', -5, ''OTHER'',
574 -6, ''OTHER'', -8, ''OTHER'', -9, ''OTHER'',
575 -1, ''ONACC'',
576 -4, ''CASH'',
577 -7, ''PREPAY''),
578 f.cash_receipt_id,
579 :CUST_ACCOUNT_ID
580 ) v2,
581 fnd_lookup_values lv
582 WHERE lv.lookup_type = ''FII_AR_RCT_BAL_DETAIL_TYPE''
583 AND lv.view_application_id = 450
584 AND lv.language = userenv(''LANG'')
585 AND v2.status (+)= lv.lookup_code
586 GROUP BY lv.meaning, lv.lookup_code, cash_receipt_id,
587 cust_account_id
588 ORDER BY decode(lv.lookup_code,
589 ''UNID'', 1, ''APP'', 2, ''ONACC'', 3,
590 ''UNAPP'', 4, ''CASH'', 5, ''PREPAY'', 6,
591 ''OTHER'', 7 )';
592
593 -- Bind variables so that no literal will be used in the pmv report
594 fii_ar_util_pkg.bind_variable
595 (p_sqlstmt => l_sqlstmt,
596 p_page_parameter_tbl => p_page_parameter_tbl,
597 p_sql_output => p_pastdue_rec_aging_sql,
598 p_bind_output_table => p_pastdue_rec_aging_output);
599
600 END get_rec_bal_detail;
601
602 END FII_AR_REC_DETAIL_PKG;
603