[Home] [Help]
PACKAGE BODY: APPS.FII_AR_REC_ACT_DETAIL_PKG
Source
1 PACKAGE BODY FII_AR_REC_ACT_DETAIL_PKG AS
2 /* $Header: FIIARDBIRADB.pls 120.25.12000000.2 2007/04/09 20:23:04 vkazhipu ship $ */
3
4 -----------------------------------------------------------------
5 -- This procedure is called by the Receipts Activity Detail
6 -----------------------------------------------------------------
7 PROCEDURE get_rec_act_detail
8 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
9 p_rec_act_detail_sql OUT NOCOPY VARCHAR2,
10 p_rec_act_detail_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
11 IS
12 l_sqlstmt VARCHAR2(25000);
13 l_where_clause VARCHAR2(2000);
14 l_where_clause1 VARCHAR2(2000);
15 l_dim_where_clause VARCHAR2(2000);
16 l_dim_where_clause1 VARCHAR2(2000) := '1=1';
17 l_from_table VARCHAR2(1000);
18 l_from_table1 VARCHAR2(1000);
19 l_dim_from_table VARCHAR2(1000);
20 l_dim_from_table1 VARCHAR2(1000);
21 l_currency VARCHAR2(10);
22 l_curr_suffix VARCHAR2(6);
23 l_industry_id VARCHAR2(30);
24 l_collector_id VARCHAR2(30);
25 l_cust_id VARCHAR2(500);
26 l_rct_num_url VARCHAR2(500) := NULL;
27 l_rct_amt_url VARCHAR2(500) := NULL;
28 l_rct_app_amt_url VARCHAR2(500) := NULL;
29
30 l_order_clause VARCHAR2(500);
31 l_order_column VARCHAR2(100);
32 l_order_null VARCHAR2(100);
33 l_order_by VARCHAR2(500);
34 l_source_report VARCHAR2(30);
35 l_gt_table_name VARCHAR2(300) := 'FII_AR_SUMMARY_GT v';
36 l_gt_table_name1 VARCHAR2(300) := 'FII_AR_SUMMARY_GT v1';
37 l_cust_acct_id VARCHAR2(30);
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 l_index_hint VARCHAR2(240) := '';
42
43 BEGIN
44 -- Reset all the global variables to NULL or to the default value
45 fii_ar_util_pkg.reset_globals;
46
47 -- Get the parameters and set the global variables
48 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
49
50 -- Retrieve values for global variables
51 l_curr_suffix := fii_ar_util_pkg.g_curr_suffix;
52 l_collector_id := fii_ar_util_pkg.g_collector_id;
53 l_cust_id := fii_ar_util_pkg.g_party_id;
54 l_industry_id := fii_ar_util_pkg.g_industry_id;
55 l_cust_acct_id := fii_ar_util_pkg.g_cust_account_id;
56
57 -- Populate global temp table based on the parameters chosen
58 fii_ar_util_pkg.populate_summary_gt_tables;
59
60
61 -- Set the currency suffix for use in the amount columns
62 IF (l_curr_suffix = '_p_v') THEN
63 l_currency := '_prim';
64 ELSIF (l_curr_suffix = '_s_v') THEN
65 l_currency := '_sec';
66 ELSIF (l_curr_suffix = '_f_v') THEN
67 l_currency := '_func';
68 END IF;
69
70 -----------------------------------------------------------------------------
71 -- Find out additional parameters pass into Receipt Activity Detail via URL:
72 -- 1. Source report calling Receipt Details
73 ------------------------------------------------------------------------------
74
75 IF (p_page_parameter_tbl.count > 0) THEN
76 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
77
78 IF (p_page_parameter_tbl(i).parameter_name = 'BIS_FXN_NAME') THEN
79 l_source_report := p_page_parameter_tbl(i).parameter_value;
80 END IF;
81
82 END LOOP;
83 END IF;
84
85 -----------------------------------------
86 -- Construct the conditional where clause
87 -----------------------------------------
88
89 -- Only add the join on collector_id if we have a specific collector selected
90 --Dimension queries are stored in variable l_dim_where_clause
91 --THIS QUERY COMES IN THE OUTER PART AFTER FACT TABLE IS FILTERED
92 --TWO WHERECLAUSES SINCE APP REC ACTIVITY DETAIL REPORT USES
93 --FACT TABLE 3 TIMES
94
95 IF (l_collector_id <> '-111') THEN
96
97 l_dim_from_table := l_dim_from_table || ',fii_ar_dimensions_mv col ';
98 l_dim_from_table1 := l_dim_from_table1 || ',fii_ar_dimensions_mv col1 ';
99
100 l_dim_where_clause := l_dim_where_clause ||
101 ' AND fact.cust_account_id = col.cust_account_id
102 AND fact.collector_bill_to_site_use_id = col.site_use_id
103 AND fact.collector_id = col.collector_id';
104
105 l_dim_where_clause1 := l_dim_where_clause1 ||
106 ' AND fact1.cust_account_id = col1.cust_account_id
107 AND fact1.collector_bill_to_site_use_id = col1.site_use_id
108 AND fact1.collector_id = col1.collector_id';
109
110 END IF;
111
112
113 IF (l_cust_acct_id <> '-111') THEN
114
115 l_from_table := l_from_table || ' fii_cust_accounts acct, ';
116 l_from_table1 := l_from_table1 || ' fii_cust_accounts acct1, ';
117
118 l_where_clause := l_where_clause ||
119 ' AND f.collector_bill_to_customer_id = :CUST_ACCOUNT_ID
120 AND f.collector_bill_to_customer_id= acct.cust_account_id
121 AND v.party_id = :PARTY_ID
122 AND v.party_id = acct.parent_party_id ';
123
124 l_where_clause1 := l_where_clause1 ||
125 ' AND f1.collector_bill_to_customer_id = :CUST_ACCOUNT_ID
126 AND f1.collector_bill_to_customer_id = acct1.cust_account_id
127 AND v1.party_id = :PARTY_ID
128 AND v1.party_id = acct1.parent_party_id ';
129
130
131 -- Only add the join on party_id when we have a specific customer selected
132 -- and if customer account id is not present
133 --if customer account id is present just bind customer account id and don't user customer id
134 --since one customer account anyway belongs to one customer
135
136
137
138 ELSIF (l_cust_id <> '-111') THEN
139
140 l_from_table := l_from_table || 'fii_cust_accounts acct, ';
141 l_from_table1 := l_from_table1 || 'fii_cust_accounts acct1, ';
142
143 l_index_hint := ' INDEX (acct fii_cust_accounts_n1)';
144
145 l_where_clause := l_where_clause1 ||
146 ' AND f.collector_bill_to_Customer_id = acct.cust_account_id
147 AND acct.account_owner_party_id in ( :PARTY_ID )
148 AND acct.account_owner_party_id = acct.parent_party_id
149 AND v.party_id = :PARTY_ID ';
150
151 l_where_clause1 := l_where_clause1 ||
152 ' AND f1.collector_bill_to_Customer_id = acct1.cust_account_id
153 AND acct1.account_owner_party_id in ( :PARTY_ID )
154 AND acct1.account_owner_party_id = acct1.parent_party_id
155 AND v1.party_id = :PARTY_ID ';
156
157
158
159 END IF;
160
161 -- Only add the join on class_category and class_code if we have a
162 -- specific industry selected
163
164 IF (l_industry_id <> '-111') THEN
165
166
167
168 l_dim_from_table := l_dim_from_table ||
169 ' ,fii_party_mkt_class ind ';
170
171 l_dim_from_table1 := l_dim_from_table1 ||
172 ' ,fii_party_mkt_class ind1 ';
173
174 l_dim_where_clause := l_dim_where_clause ||
175 ' AND ind.party_id = fact.Account_Owner_Party_ID
176 AND ind.class_category = fact.class_category
177 AND ind.class_code = fact.class_code ';
178
179 l_dim_where_clause1 := l_dim_where_clause1 ||
180 ' AND ind1.party_id = fact1.Account_Owner_Party_ID
181 AND ind1.class_category = fact1.class_category
182 AND ind1.class_code = fact1.class_code ';
183
184
185 END IF;
186
187 -------------------------------
188 -- Construct the drilldown URLs
189 -------------------------------
190
191 -- Receipt Number Drilldown URL
192 IF (l_source_report = 'FII_AR_RCT_ACT_DTL') THEN
193 l_rct_num_url := 'pFunctionName=FII_AR_RCT_ACT_HISTORY&FII_AR_RCT_NUM=FII_AR_RCT_NUM'||
194 '&BIS_PMV_DRILL_CODE_FII_AR_RCT_CURRENCY=FII_AR_RCT_CURRENCY'||
195 '&BIS_PMV_DRILL_CODE_BIS_FII_CUSTOMER_ACCOUNT=''||cust_account_id||'''||
196 '&FII_AR_CASH_RECEIPT_ID=FII_AR_CASH_RECEIPT_ID&BIS_PMV_DRILL_CODE_AS_OF_DATE=sysdate'||
197 '&pParamIds=Y';
198
199 -- Receipt Amount Drilldown URL
200
201
202 l_rct_amt_url := 'pFunctionName=FII_AR_RCT_BALANCES_DTL'||
203 '&BIS_PMV_DRILL_CODE_BIS_FII_CUSTOMER_ACCOUNT=''||cust_account_id||'''||
204 '&FII_AR_RCT_NUM=FII_AR_RCT_NUM'||
205 '&FII_AR_CASH_RECEIPT_ID=FII_AR_CASH_RECEIPT_ID'||
206 '&BIS_PMV_DRILL_CODE_FII_AR_RCT_CURRENCY=FII_AR_RCT_CURRENCY&BIS_PMV_DRILL_CODE_AS_OF_DATE=sysdate'||
207 '&pParamIds=Y';
208
209 ELSE
210
211 -- Receipt Number Drilldown URL
212
213 l_rct_num_url := 'pFunctionName=FII_AR_RCT_ACT_HISTORY&FII_AR_RCT_NUM=FII_AR_RCT_NUM'||
214 '&BIS_PMV_DRILL_CODE_FII_AR_RCT_CURRENCY=FII_AR_RCT_CURRENCY'||
215 '&BIS_PMV_DRILL_CODE_BIS_FII_CUSTOMER_ACCOUNT=''||cust_account_id||'''||
216 '&FII_AR_CASH_RECEIPT_ID=FII_AR_CASH_RECEIPT_ID&BIS_PMV_DRILL_CODE_AS_OF_DATE=sysdate'||
217 '&pParamIds=Y';
218
219 -- Receipt Amount Drilldown URL
220
221
222 l_rct_amt_url := 'pFunctionName=FII_AR_RCT_BALANCES_DTL'||
223 '&BIS_PMV_DRILL_CODE_BIS_FII_CUSTOMER_ACCOUNT=''||cust_account_id||'''||
224 '&FII_AR_RCT_NUM=FII_AR_RCT_NUM'||
225 '&FII_AR_CASH_RECEIPT_ID=FII_AR_CASH_RECEIPT_ID'||
226 '&BIS_PMV_DRILL_CODE_FII_AR_RCT_CURRENCY=FII_AR_RCT_CURRENCY'||
227 '&BIS_PMV_DRILL_CODE_AS_OF_DATE=sysdate&pParamIds=Y';
228
229 END IF;
230
231
232
233
234 ----------------------------------------------------------------
235 -- Find out the sort order column and construct the order clause
236 ----------------------------------------------------------------
237
238 l_order_clause := fii_ar_util_pkg.g_order_by;
239
240 -- Set the default order by clause for displaying NULL last when
241 -- we sort in descending order
242
243 IF (instr(l_order_clause, 'DATE') <> 0) THEN
244
245 -- The sort column is a date column
246
247 l_order_null := 'to_date(''0001/12/31'',''YYYY/MM/DD'')';
248
249 ELSIF (instr(l_order_clause, 'NLSSORT') <> 0) THEN
250
251 -- The sort column is a VARCHAR2 column
252
253 l_order_null := 'NLSSORT(''000000000'', ''NLS_SORT=BINARY'')';
254
255 ELSE
256 -- The sort column is a numeric column
257
258 l_order_null := '-999999999';
259
260 END IF;
261
262 -- Set the order by clause for the PMV sql
263
264 IF (instr(l_order_clause, 'FII_AR_RCT_AMT_RCURR') <> 0) THEN
265
266 -------------------------------------------------------------
267 -- Special treatment for the Receipt Amount (Receipt Currency)
268 -- column. We should sort this as if it is a numeric column
269 -- based on the receipt amount value.
270 -------------------------------------------------------------
271
272 IF (instr(l_order_clause, ' DESC') <> 0) THEN
273
274 l_order_by := ' ORDER BY NVL(sum(receipt_amount), -999999999) DESC,
275 NVL(FII_AR_RCT_CURRENCY, NLSSORT(''000000000'',
276 ''NLS_SORT=BINARY'')) DESC';
277 ELSE
278 l_order_by := ' ORDER BY NVL(sum(receipt_amount), -999999999) ASC,
279 NVL(FII_AR_RCT_CURRENCY, NLSSORT(''000000000'',
280 ''NLS_SORT=BINARY'')) ASC';
281 END IF;
282
283
284 ELSIF ((instr(l_order_clause, ',') <> 0) AND
285
286 (instr(l_order_clause, 'NLSSORT') = 0)) THEN
287
288 -------------------------------------------------------------
289 -- No particular sort column is selected in the report. We'll
290 -- sort on the default column in descending order. NVL is
291 -- added ot make sure NULL will appear last.
292 -------------------------------------------------------------
293
294 l_order_by := ' ORDER BY NVL(FII_AR_RCT_AMT, -999999999) DESC';
295
296 ELSIF (instr(l_order_clause, ' DESC') <> 0) THEN
297
298 -------------------------------------------------------------
299 -- User has asked for a descending order sort. We'll also
300 -- make sure NULL will appear last with the default order clause.
301 -------------------------------------------------------------
302
303 l_order_column := substr(fii_ar_util_pkg.g_order_by, 1,
304 instr(l_order_clause, ' DESC'));
305 l_order_by := ' ORDER BY NVL('|| l_order_column || ', '
306 || l_order_null || ' ) DESC';
307
308 ELSE
309
310 -------------------------------------------------------------
311 -- User has asked for an ascending order sort. We should use
312 -- PMV's order by clause
313 -------------------------------------------------------------
314
315 l_order_by := ' &ORDER_BY_CLAUSE';
316
317 END IF;
318
319
320 -------------------------------
321 -- Construct the sql statements
322 -------------------------------
323 IF (l_source_report = 'FII_AR_RCT_ACT_DTL') THEN
324
325 l_sqlstmt :=
326 'SELECT FII_AR_RCT_ACCT_NUM,
327 FII_AR_RCT_NUM,
328 FII_AR_RCT_DATE,
329 FII_AR_RCT_GL_DATE,
330 FII_AR_RCT_PAY_METHOD,
331 FII_AR_RCT_STATUS,
332 to_char(sum(receipt_amount),''999,999,999,999'')|| '' '' || FII_AR_RCT_CURRENCY FII_AR_RCT_AMT_RCURR,
333 sum(FII_AR_RCT_AMT) FII_AR_RCT_AMT ,
334 sum(FII_AR_RCT_APP_AMT) FII_AR_RCT_APP_AMT,
335 sum(FII_AR_RCT_EARNED_DCT) FII_AR_RCT_EARNED_DCT,
336 sum(FII_AR_RCT_UNEARNED_DCT)FII_AR_RCT_UNEARNED_DCT ,
337 sum(sum(FII_AR_RCT_AMT)) over() FII_AR_GT_RCT_AMT,
338 sum(sum(FII_AR_RCT_APP_AMT)) over() FII_AR_GT_RCT_APP_AMT,
339 sum(sum(FII_AR_RCT_EARNED_DCT)) over() FII_AR_GT_RCT_EARNED_DCT,
340 sum(sum(FII_AR_RCT_UNEARNED_DCT)) over() FII_AR_GT_RCT_UNEARNED_DCT,
341 decode(FII_AR_RCT_NUM, NULL, NULL, ''' || l_rct_num_url || ''') FII_AR_RCT_NUM_DRILL,
342 decode(sum(FII_AR_RCT_AMT), 0, NULL, NULL, NULL,'''|| l_rct_amt_url ||''') FII_AR_RCT_AMT_DRILL,
343 FII_AR_RCT_CURRENCY,
344 FII_AR_CASH_RECEIPT_ID
345 FROM (
346 SELECT
347 NVL(fact.account_number,'''||l_unid_message||''') FII_AR_RCT_ACCT_NUM,
348 FII_AR_RCT_NUM FII_AR_RCT_NUM,
349 FII_AR_RCT_DATE FII_AR_RCT_DATE,
350 FII_AR_RCT_GL_DATE FII_AR_RCT_GL_DATE,
351 m.name FII_AR_RCT_PAY_METHOD,
352 hist.status FII_AR_RCT_STATUS,
353 receipt_amount receipt_amount,
354 FII_AR_RCT_AMT FII_AR_RCT_AMT,
355 FII_AR_RCT_APP_AMT FII_AR_RCT_APP_AMT,
356 FII_AR_RCT_EARNED_DCT FII_AR_RCT_EARNED_DCT,
357 FII_AR_RCT_UNEARNED_DCT FII_AR_RCT_UNEARNED_DCT,
358 FII_AR_RCT_CURRENCY FII_AR_RCT_CURRENCY,
359 FII_AR_CASH_RECEIPT_ID FII_AR_CASH_RECEIPT_ID,
360 fact.cust_account_id cust_account_id
361 FROM(
362 SELECT /*+ no_merge leading(v) cardinality(v 1) */ f.receipt_number FII_AR_RCT_NUM,
363 acct.account_number account_number,
364 f.receipt_date FII_AR_RCT_DATE,
365 f.gl_date FII_AR_RCT_GL_DATE,
366 sum(f.amount_applied_rct) receipt_amount,
367 sum(f.amount_applied_rct' || l_currency || ') FII_AR_RCT_AMT,
368 CASE WHEN f.application_status = ''APP''
369 AND f.filter_date <= :ASOF_DATE
370 AND f.filter_date >= :CURR_PERIOD_START
371 THEN sum(f.amount_applied_rct' || l_currency || ')
372 ELSE 0
373 END FII_AR_RCT_APP_AMT,
374 sum(f.earned_discount_amount' || l_currency || ') FII_AR_RCT_EARNED_DCT,
375 sum(f.unearned_discount_amount' || l_currency || ') FII_AR_RCT_UNEARNED_DCT,
376 f.currency_code FII_AR_RCT_CURRENCY,
377 f.cash_receipt_id FII_AR_CASH_RECEIPT_ID,
378 f.collector_bill_to_customer_id cust_account_id ,
379 f.receipt_method_id receipt_method_id,
380 f.collector_bill_to_site_use_id collector_bill_to_site_use_id,
381 v.collector_id collector_id,
382 acct.account_owner_party_id Account_Owner_Party_ID,
383 v.class_Category class_category,
384 v.class_code class_code
385 FROM fii_ar_receipts_f f,
386 '||l_from_table || '
387 '||l_gt_table_name||'
388 WHERE f.org_id = v.org_id
389 AND ((f.header_filter_date <= :ASOF_DATE
390 AND f.header_filter_date >= :CURR_PERIOD_START)
391 AND
392 (f.filter_date <= :ASOF_DATE
393 AND f.filter_date >= :CURR_PERIOD_START))
394 '|| l_where_clause ||'
395 GROUP BY f.receipt_number,acct.account_number,f.receipt_date, f.gl_date,
396 f.currency_code, f.cash_receipt_id,
397 f.collector_bill_to_customer_id,
398 f.receipt_method_id,f.filter_date,f.application_status,
399 f.collector_bill_to_site_use_id ,
400 v.collector_id ,
401 acct.account_owner_party_id,
402 v.class_Category ,
403 v.class_code)fact,
404 ar_receipt_methods m,
405 -- hz_cust_accounts ca,
406 ar_cash_receipt_history_all hist '||l_dim_from_table||'
407 WHERE
408 fact.receipt_method_id = m.receipt_method_id
409 AND hist.cash_receipt_id = fact.FII_AR_CASH_RECEIPT_ID
410 AND hist.cash_receipt_history_id = (select /*+ no_merge */ max(cash_receipt_history_id) from
411 ar_cash_receipt_history_all hist1 where
412 hist1.cash_receipt_id = fact.FII_AR_CASH_RECEIPT_ID)
413 -- AND fact.cust_account_id = ca.cust_account_id(+)
414 '||l_dim_where_clause||')
415 GROUP BY
416 FII_AR_RCT_ACCT_NUM,
417 FII_AR_RCT_NUM,
418 FII_AR_RCT_DATE,
419 FII_AR_RCT_GL_DATE,
420 FII_AR_RCT_PAY_METHOD,
421 FII_AR_RCT_STATUS,
422 CUST_ACCOUNT_ID,
423 FII_AR_RCT_CURRENCY,
424 FII_AR_CASH_RECEIPT_ID
425 '|| l_order_by ;
426
427 ELSE
428
429 l_sqlstmt := 'SELECT
430 FII_AR_RCT_ACCT_NUM,
431 FII_AR_RCT_NUM,
432 FII_AR_RCT_DATE,
433 FII_AR_RCT_GL_DATE,
434 FII_AR_RCT_PAY_METHOD,
435 FII_AR_RCT_STATUS,
436 to_char(sum(receipt_amount),''999,999,999,999'')|| '' '' || FII_AR_RCT_CURRENCY FII_AR_RCT_AMT_RCURR,
437 sum(FII_AR_RCT_AMT) FII_AR_RCT_AMT,
438 sum(FII_AR_RCT_APP_AMT)FII_AR_RCT_APP_AMT ,
439 sum(FII_AR_RCT_EARNED_DCT) FII_AR_RCT_EARNED_DCT,
440 sum(FII_AR_RCT_UNEARNED_DCT) FII_AR_RCT_UNEARNED_DCT,
441 sum(sum(FII_AR_GT_RCT_AMT)) over() FII_AR_GT_RCT_AMT,
442 sum(sum(FII_AR_GT_RCT_APP_AMT)) over() FII_AR_GT_RCT_APP_AMT,
443 sum(sum(FII_AR_GT_RCT_EARNED_DCT)) over() FII_AR_GT_RCT_EARNED_DCT,
444 sum(sum(FII_AR_GT_RCT_UNEARNED_DCT)) over() FII_AR_GT_RCT_UNEARNED_DCT,
445 decode(FII_AR_RCT_NUM, NULL, NULL, ''' ||
446 l_rct_num_url || ''') FII_AR_RCT_NUM_DRILL,
447 decode(sum(FII_AR_RCT_AMT), 0, NULL, NULL, NULL,
448 ''' || l_rct_amt_url ||
449 ''') FII_AR_RCT_AMT_DRILL,
450 FII_AR_RCT_CURRENCY,
451 FII_AR_CASH_RECEIPT_ID FROM(
452 SELECT
453 NVL(fact.account_number,'''||l_unid_message||''') FII_AR_RCT_ACCT_NUM,
454 FII_AR_RCT_NUM FII_AR_RCT_NUM,
455 FII_AR_RCT_DATE FII_AR_RCT_DATE,
456 FII_AR_RCT_GL_DATE FII_AR_RCT_GL_DATE,
457 m.name FII_AR_RCT_PAY_METHOD,
458 hist.status FII_AR_RCT_STATUS,
459 0 receipt_amount,
460 0 FII_AR_RCT_AMT,
461 FII_AR_RCT_APP_AMT FII_AR_RCT_APP_AMT,
462 0 FII_AR_RCT_EARNED_DCT,
463 0 FII_AR_RCT_UNEARNED_DCT,
464 0 FII_AR_GT_RCT_AMT,
465 FII_AR_GT_RCT_APP_AMT FII_AR_GT_RCT_APP_AMT,
466 0 FII_AR_GT_RCT_EARNED_DCT,
467 0 FII_AR_GT_RCT_UNEARNED_DCT,
468 FII_AR_RCT_CURRENCY FII_AR_RCT_CURRENCY,
469 FII_AR_CASH_RECEIPT_ID FII_AR_CASH_RECEIPT_ID,
470 fact.cust_account_id cust_account_id
471 FROM (
472 SELECT /*+ no_merge leading(v) cardinality(v 1)*/
473 acct.account_number account_number,
474 f.receipt_number FII_AR_RCT_NUM,
475 f.receipt_date FII_AR_RCT_DATE,
476 f.gl_date FII_AR_RCT_GL_DATE,
477 sum(f.amount_applied_rct' || l_currency || ') FII_AR_RCT_APP_AMT,
478 sum(f.amount_applied_rct' || l_currency || ') FII_AR_GT_RCT_APP_AMT,
479 f.currency_code FII_AR_RCT_CURRENCY,
480 f.cash_receipt_id FII_AR_CASH_RECEIPT_ID,
481 f.collector_bill_to_customer_id cust_account_id,
482 f.receipt_method_id receipt_method_id,
483 f.collector_bill_to_site_use_id collector_bill_to_site_use_id,
484 v.collector_id collector_id,
485 acct.account_owner_party_id Account_Owner_Party_ID,
486 v.class_Category class_category,
487 v.class_code class_code
488 FROM fii_ar_receipts_f f,
489 '||l_from_table || '
490 '||l_gt_table_name||'
491 WHERE f.org_id = v.org_id
492 AND f.filter_date <= :ASOF_DATE
493 AND f.filter_date >= :CURR_PERIOD_START
494 AND f.application_status = ''APP''
495 AND (f.applied_payment_schedule_id > 0 OR f.applied_payment_schedule_id IS NULL) '
496 || l_where_clause ||
497 ' GROUP BY acct.account_number ,f.receipt_number, f.receipt_date, f.gl_date,
498 f.currency_code, f.cash_receipt_id,
499 f.collector_bill_to_customer_id,
500 f.receipt_method_id,
501 f.collector_bill_to_site_use_id ,
502 v.collector_id ,
503 acct.account_owner_party_id,
504 v.class_Category ,
505 v.class_code) fact,
506 ar_receipt_methods m,
507 ar_cash_receipt_history_all hist
508 '||l_dim_from_table||'
509 WHERE
510 fact.receipt_method_id = m.receipt_method_id
511 AND hist.cash_receipt_id = fact.FII_AR_CASH_RECEIPT_ID
512 AND hist.cash_receipt_history_id = (select /*+ no_merge */ max(cash_receipt_history_id) from
513 ar_cash_receipt_history_all hist1 where
514 hist1.cash_receipt_id = fact.FII_AR_CASH_RECEIPT_ID)
515 '||l_dim_where_clause||'
516 UNION ALL
517 SELECT NVL(fact.account_number,'''||l_unid_message||''') FII_AR_RCT_ACCT_NUM,
518 FII_AR_RCT_NUM FII_AR_RCT_NUM,
519 FII_AR_RCT_DATE FII_AR_RCT_DATE,
520 FII_AR_RCT_GL_DATE FII_AR_RCT_GL_DATE,
521 m.name FII_AR_RCT_PAY_METHOD,
522 hist.status FII_AR_RCT_STATUS,
523 receipt_amount receipt_amount,
524 FII_AR_RCT_AMT FII_AR_RCT_AMT,
525 0 FII_AR_RCT_APP_AMT,
526 FII_AR_RCT_EARNED_DCT FII_AR_RCT_EARNED_DCT,
527 FII_AR_RCT_UNEARNED_DCT FII_AR_RCT_UNEARNED_DCT,
528 FII_AR_GT_RCT_AMT FII_AR_GT_RCT_AMT,
529 0 FII_AR_GT_RCT_APP_AMT,
530 FII_AR_GT_RCT_EARNED_DCT FII_AR_GT_RCT_EARNED_DCT,
531 FII_AR_GT_RCT_UNEARNED_DCT FII_AR_GT_RCT_UNEARNED_DCT,
532 FII_AR_RCT_CURRENCY FII_AR_RCT_CURRENCY,
533 FII_AR_CASH_RECEIPT_ID FII_AR_CASH_RECEIPT_ID ,
534 fact.cust_account_id cust_account_id FROM (
535 SELECT /*+ no_merge '||l_index_hint||' leading(v) cardinality(v 1)*/
536 acct.account_number account_number,
537 f.receipt_number FII_AR_RCT_NUM,
538 f.receipt_date FII_AR_RCT_DATE,
539 f.gl_date FII_AR_RCT_GL_DATE,
540 sum(f.amount_applied_rct) receipt_amount,
541 sum(f.amount_applied_rct' || l_currency || ') FII_AR_RCT_AMT,
542 sum(f.earned_discount_amount' || l_currency || ') FII_AR_RCT_EARNED_DCT,
543 sum(f.unearned_discount_amount'||l_currency||') FII_AR_RCT_UNEARNED_DCT,
544 sum(f.amount_applied_rct' || l_currency || ') FII_AR_GT_RCT_AMT,
545 sum(f.earned_discount_amount' || l_currency || ') FII_AR_GT_RCT_EARNED_DCT,
546 sum(f.unearned_discount_amount'||l_currency||') FII_AR_GT_RCT_UNEARNED_DCT,
547 f.currency_code FII_AR_RCT_CURRENCY,
548 f.cash_receipt_id FII_AR_CASH_RECEIPT_ID,
549 f.collector_bill_to_customer_id cust_account_id,
550 f.receipt_method_id receipt_method_id,
551 f.collector_bill_to_site_use_id collector_bill_to_site_use_id,
552 v.collector_id collector_id,
553 acct.account_owner_party_id Account_Owner_Party_ID,
554 v.class_Category class_category,
555 v.class_code class_code
556 FROM fii_ar_receipts_f f,
557 '||l_from_table || '
558 '||l_gt_table_name||'
559 WHERE f.org_id = v.org_id
560 AND f.filter_date <= :ASOF_DATE
561 and f.cash_receipt_id in
562 (select /*+ no_merge */ distinct cash_receipt_id from
563 ( select /*+ no_merge leading(v1) cardinality(v1 1)*/ v1.collector_id collector_id,
564 v1.class_category class_category,
565 v1.class_code class_code,
566 acct1.account_owner_party_id account_owner_party_id
567 ,cash_receipt_id
568 ,f1.collector_bill_to_customer_id cust_account_id
569 ,f1.collector_bill_to_site_use_id collector_bill_to_site_use_id
570 from FII_AR_RECEIPTS_F f1
571 , '||l_from_table1 || '
572 '||l_gt_table_name1||'
573 where f1.org_id = v1.org_id
574 AND f1.filter_date <= :ASOF_DATE
575 AND f1.filter_date >= :CURR_PERIOD_START
576 AND f1.application_status = ''APP''
577 and (f1.applied_payment_schedule_id > 0 OR f1.applied_payment_schedule_id IS NULL)
578 '|| l_where_clause1 ||'
579 ) fact1 '||l_dim_from_table1||'
580 WHERE
581 '||l_dim_where_clause1||'
582 )
583 AND (f.applied_payment_schedule_id > 0 OR f.applied_payment_schedule_id IS NULL)'
584 || l_where_clause ||
585 ' GROUP BY acct.account_number,f.receipt_number, f.receipt_date, f.gl_date,
586 f.currency_code, f.cash_receipt_id,
587 f.collector_bill_to_customer_id,f.receipt_method_id,
588 f.collector_bill_to_site_use_id ,
589 v.collector_id ,
590 acct.account_owner_party_id,
591 v.class_Category ,
592 v.class_code) fact,
593 ar_receipt_methods m,
594 ar_cash_receipt_history_all hist
595 '||l_dim_from_table||'
596 WHERE
597 fact.receipt_method_id = m.receipt_method_id
598 AND hist.cash_receipt_id = fact.FII_AR_CASH_RECEIPT_ID
599 AND hist.cash_receipt_history_id = (select /*+ no_merge */ max(cash_receipt_history_id) from
600 ar_cash_receipt_history_all hist1 where
601 hist1.cash_receipt_id = fact.FII_AR_CASH_RECEIPT_ID)
602 '||l_dim_where_clause||' )
603 GROUP BY
604 FII_AR_RCT_ACCT_NUM,
605 FII_AR_RCT_NUM,
606 FII_AR_RCT_DATE,
607 FII_AR_RCT_GL_DATE,
608 FII_AR_RCT_PAY_METHOD,
609 FII_AR_RCT_STATUS,
610 FII_AR_RCT_CURRENCY,
611 FII_AR_CASH_RECEIPT_ID,
612 CUST_ACCOUNT_ID '
613 || l_order_by;
614
615 END IF;
616
617 -- Bind variables so that no literal will be used in the pmv report
618
619
620 fii_ar_util_pkg.bind_variable
621 (p_sqlstmt => l_sqlstmt,
622 p_page_parameter_tbl => p_page_parameter_tbl,
623 p_sql_output => p_rec_act_detail_sql,
624 p_bind_output_table => p_rec_act_detail_output);
625
626 END get_rec_act_detail;
627
628
629
630 END FII_AR_REC_ACT_DETAIL_PKG;
631