[Home] [Help]
PACKAGE BODY: APPS.FII_AR_CURR_REC_SUM_PKG
Source
1 PACKAGE BODY FII_AR_CURR_REC_SUM_PKG AS
2 /* $Header: FIIARDBICRB.pls 120.11 2007/05/15 20:48:15 vkazhipu ship $ */
3
4 PROCEDURE GET_CURR_REC_SUM
5 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
6 curr_rec_sql out NOCOPY VARCHAR2,
7 curr_rec_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
8
9 l_as_of_date DATE;
10 l_cust_suffix VARCHAR2(30);
11 l_curr_suffix VARCHAR2(30);
12 l_collector_id VARCHAR2(30);
13 l_cust_id VARCHAR2(500);
14 l_cust_account_id VARCHAR2(30);
15 l_itd_bitand NUMBER;
16 l_viewby VARCHAR2(100);
17 l_self_msg VARCHAR2(240);
18 l_hierarchical_flag VARCHAR2(1);
19 l_cust_clause VARCHAR2(100);
20 l_order_by_clause VARCHAR2(500);
21 l_order_column VARCHAR2(50);
22 l_where_clause VARCHAR2(500);
23
24 l_max_bucket_ct NUMBER := 3;
25 l_bucket_ct NUMBER;
26
27 l_customer_url VARCHAR2(500) := NULL;
28 l_open_rec_amt_det_url VARCHAR2(500) := NULL;
29 l_curr_rec_amt_det_url VARCHAR2(500) := NULL;
30 l_curr_due_days_b1_det_url VARCHAR2(500) := NULL;
31 l_curr_due_days_b2_det_url VARCHAR2(500) := NULL;
32 l_curr_due_days_b3_det_url VARCHAR2(500) := NULL;
33 l_open_rec_amt_url VARCHAR2(500) := NULL;
34 l_curr_rec_amt_url VARCHAR2(500) := NULL;
35 l_curr_due_days_b1_url VARCHAR2(500) := NULL;
36 l_curr_due_days_b2_url VARCHAR2(500) := NULL;
37 l_curr_due_days_b3_url VARCHAR2(500) := NULL;
38 l_or_ca_amt_det_url VARCHAR2(500) := NULL;
39 l_cr_ca_amt_det_url VARCHAR2(500) := NULL;
40 l_cdd_ca_b1_det_url VARCHAR2(500) := NULL;
41 l_cdd_ca_b2_det_url VARCHAR2(500) := NULL;
42 l_cdd_ca_b3_det_url VARCHAR2(500) := NULL;
43 l_open_rec_amt_url_1 VARCHAR2(500) := NULL;
44 l_curr_rec_amt_url_1 VARCHAR2(500) := NULL;
45 l_curr_due_days_b1_url_1 VARCHAR2(500) := NULL;
46 l_curr_due_days_b2_url_1 VARCHAR2(500) := NULL;
47 l_curr_due_days_b3_url_1 VARCHAR2(500) := NULL;
48
49 l_url_sql VARCHAR2(10000);
50 l_bucket_sql VARCHAR2(1000);
51 l_sqlstmt VARCHAR2(32767);
52 i NUMBER;
53 l_gt_hint varchar2(500);
54
55
56 BEGIN
57
58
59 -- Reset all the global variables to NULL or to the default value
60 fii_ar_util_pkg.reset_globals;
61
62 -- Get the parameters and set the global variables
63 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
64 l_gt_hint := ' leading(gt) cardinality(gt 1) ';
65 -- Retrieve values for global variables
66 l_as_of_date := fii_ar_util_pkg.g_as_of_date;
67 l_cust_suffix := fii_ar_util_pkg.g_cust_suffix;
68 l_curr_suffix := fii_ar_util_pkg.g_curr_suffix;
69 l_collector_id := fii_ar_util_pkg.g_collector_id;
70 l_cust_id := fii_ar_util_pkg.g_party_id;
71 l_cust_account_id := fii_ar_util_pkg.g_cust_account_id;
72 l_itd_bitand := fii_ar_util_pkg.g_bitand_inc_todate;
73 l_viewby := fii_ar_util_pkg.g_view_by;
74 l_self_msg := fii_ar_util_pkg.g_self_msg;
75 l_hierarchical_flag := fii_ar_util_pkg.g_is_hierarchical_flag;
76
77 -- Populate global temp table based on the parameters chosen
78 fii_ar_util_pkg.populate_summary_gt_tables;
79
80
81 -- Find out the number of bucket ranges customized for this report
82 SELECT sum(decode(bbc.range1_low, null, 0, 1) +
83 decode(bbc.range2_low, null, 0, 1) +
84 decode(bbc.range3_low, null, 0, 1) +
85 decode(bbc.range4_low, null, 0, 1) +
86 decode(bbc.range5_low, null, 0, 1) +
87 decode(bbc.range6_low, null, 0, 1) +
88 decode(bbc.range7_low, null, 0, 1) +
89 decode(bbc.range8_low, null, 0, 1) +
90 decode(bbc.range9_low, null, 0, 1) +
91 decode(bbc.range10_low, null, 0, 1)) bucket_count
92 INTO l_bucket_ct
93 FROM bis_bucket_customizations bbc,
94 bis_bucket bb
95 WHERE bb.short_name = 'FII_DBI_CURRENT_REC_BUCKET'
96 AND bbc.bucket_id = bb.bucket_id;
97
98 -- Construct the self node clause
99 -- We only need this when view by customer and it is a hierarchical setup
100 IF (l_viewby = 'CUSTOMER+FII_CUSTOMERS') AND (l_hierarchical_flag = 'Y') THEN
101 l_cust_clause := ' , v.is_self_flag, v.is_leaf_flag ';
102 ELSIF (l_viewby = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') THEN
103 l_gt_hint := ' leading(gt.gt) cardinality(gt.gt 1) ';
104 l_cust_clause := ' , v.party_id ';
105 ELSE
106 l_cust_clause := NULL;
107 END IF;
108
109 -----------------------------------------------------------------------------
110 -- When view by Customer for leaf level customers, we'll use the following
111 -- drilldown URLs (and pass null to Customer Account):
112 --
113 -- 1. Open Receivables amount will drill to Open Receivables Detail Report
114 -- (Transaction Detail)
115 -- 2. Current Receivables amount will drill to Current Receivables Detail
116 -- Report (Transaction Detail)
117 -- 3. Aging Bucket X amount will drill to Receivables Due in X days Detail
118 -- report (Transaction Detail)
119 -----------------------------------------------------------------------------
120 -- Open Receivables Amount Drilldown URL
121 l_open_rec_amt_det_url := 'pFunctionName=FII_AR_OPEN_REC_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
122 -- Past Due Receivables Amount Drilldown URL
123 l_curr_rec_amt_det_url := 'pFunctionName=FII_AR_CURR_REC_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
124
125 -- Aging Bucket X Amount Drilldown URL
126 IF (l_bucket_ct >= 1) THEN
127 l_curr_due_days_b1_det_url := 'pFunctionName=FII_AR_REC_DUE_BUCKET&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_AR_BUCKET_NUM=1';
128 END IF;
129
130 IF (l_bucket_ct >= 2) THEN
131 l_curr_due_days_b2_det_url := 'pFunctionName=FII_AR_REC_DUE_BUCKET&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_AR_BUCKET_NUM=2';
132 END IF;
133
134 IF (l_bucket_ct >= 3) THEN
135 l_curr_due_days_b3_det_url := 'pFunctionName=FII_AR_REC_DUE_BUCKET&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_AR_BUCKET_NUM=3';
136 END IF;
137
138 -----------------------------------------------------------------------------
139 -- When view by Customer Acct, we'll use the following drilldown URLs
140 -- (and pass customer account):
141 --
142 -- 1. Open Receivables amount will drill to Open Receivables Detail Report
143 -- (Transaction Detail)
144 -- 2. Current Receivables amount will drill to Current Receivables Detail
145 -- Report (Transaction Detail)
146 -- 3. Aging Bucket X amount will drill to Receivables Due in X days Detail
147 -- report (Transaction Detail)
148 -----------------------------------------------------------------------------
149 -- Open Receivables Amount Drilldown URL
150 l_or_ca_amt_det_url := 'pFunctionName=FII_AR_OPEN_REC_DTL&FII_AR_CUST_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inner_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
151 -- Past Due Receivables Amount Drilldown URL
152 l_cr_ca_amt_det_url := 'pFunctionName=FII_AR_CURR_REC_DTL&FII_AR_CUST_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inner_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
153
154 -- Aging Bucket X Amount Drilldown URL
155 IF (l_bucket_ct >= 1) THEN
156 l_cdd_ca_b1_det_url := 'pFunctionName=FII_AR_REC_DUE_BUCKET&FII_AR_CUST_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inner_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y&FII_AR_BUCKET_NUM=1';
157 END IF;
158
159 IF (l_bucket_ct >= 2) THEN
160 l_cdd_ca_b2_det_url := 'pFunctionName=FII_AR_REC_DUE_BUCKET&FII_AR_CUST_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inner_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y&FII_AR_BUCKET_NUM=2';
161 END IF;
162
163 IF (l_bucket_ct >= 3) THEN
164 l_cdd_ca_b3_det_url := 'pFunctionName=FII_AR_REC_DUE_BUCKET&FII_AR_CUST_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inner_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y&FII_AR_BUCKET_NUM=3';
165 END IF;
166
167
168 -----------------------------------------------------------------------------
169 -- When view by OU, Collector, or Customer (for rollup customers),
170 -- we'll use the following drilldown URLs:
171 --
172 -- 1. Open Receivables amount will drill to Open Receivables Summary
173 -- (View by Customer Account)
174 -- 2. Current Receivables amount will drill to Current Receivables
175 -- Summary (View by Customer Account)
176 -- 3. Aging bucket X amount will drill to Current Receivables Summary
177 -- (View by Customer Account)
178 -----------------------------------------------------------------------------
179
180 IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
181 -- Open Receivables Amount Drilldown URL
182 l_open_rec_amt_url_1 := 'pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
183
184 -- Current Receivables Amount Drilldown URL
185 l_curr_rec_amt_url_1 := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
186
187 -- Aging Bucket X Amount Drilldown URL
188 IF (l_bucket_ct >= 1) THEN
189 l_curr_due_days_b1_url_1 := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
190 END IF;
191
192 IF (l_bucket_ct >= 2) THEN
193 l_curr_due_days_b2_url_1 := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
194 END IF;
195
196 IF (l_bucket_ct >= 3) THEN
197 l_curr_due_days_b3_url_1 := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
198 END IF;
199 ELSE
200 -- Open Receivables Amount Drilldown URL
201 l_open_rec_amt_url_1 := 'pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
202
203 -- Current Receivables Amount Drilldown URL
204 l_curr_rec_amt_url_1 := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
205
206 -- Aging Bucket X Amount Drilldown URL
207 IF (l_bucket_ct >= 1) THEN
208 l_curr_due_days_b1_url_1 := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
209 END IF;
210
211 IF (l_bucket_ct >= 2) THEN
212 l_curr_due_days_b2_url_1 := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
213 END IF;
214
215 IF (l_bucket_ct >= 3) THEN
216 l_curr_due_days_b3_url_1 := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
217 END IF;
218 END IF;
219
220
221
222 -- Open Receivables Amount Drilldown URL
223 l_open_rec_amt_url := 'pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
224
225 -- Current Receivables Amount Drilldown URL
226 l_curr_rec_amt_url := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
227
228 -- Aging Bucket X Amount Drilldown URL
229 IF (l_bucket_ct >= 1) THEN
230 l_curr_due_days_b1_url := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
231 END IF;
232
233 IF (l_bucket_ct >= 2) THEN
234 l_curr_due_days_b2_url := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
235 END IF;
236
237 IF (l_bucket_ct >= 3) THEN
238 l_curr_due_days_b3_url := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
239 END IF;
240
241 -----------------------------------------------------------------------------
242 -- When view by Customer and the customer is not a leaf node,
243 -- we'll drilldown to the next level in the customer hierarchy on the same report
244 -----------------------------------------------------------------------------
245 l_customer_url := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
246
247 ----------------------------------------------------------------
248 -- Construct the drilldown URL sql
249 ----------------------------------------------------------------
250 IF (l_viewby IN ('ORGANIZATION+FII_OPERATING_UNITS',
251 'FII_COLLECTOR+FII_COLLECTOR')) THEN
252 l_url_sql :=
253 ', DECODE(FII_AR_OPEN_REC_AMT, 0, NULL, NULL, NULL, '''||
254 l_open_rec_amt_url_1 || ''') FII_AR_OPEN_REC_DRILL,
255 DECODE(FII_AR_CURR_REC_AMT, 0, NULL, NULL, NULL, '''||
256 l_curr_rec_amt_url_1 || ''') FII_AR_CURR_REC_AMT_DRILL ';
257
258 IF (l_bucket_ct >= 1) THEN
259 l_url_sql := l_url_sql ||
260 ', DECODE(FII_AR_CURR_REC_BUCKET_AMT_B1, 0, NULL, NULL, NULL, '''||
261 l_curr_due_days_b1_url_1 || ''') FII_AR_CR_BKT_AMT_DRILL_B1 ';
262 END IF;
263
264 IF (l_bucket_ct >= 2) THEN
265 l_url_sql := l_url_sql ||
266 ', DECODE(FII_AR_CURR_REC_BUCKET_AMT_B2, 0, NULL, NULL, NULL, '''||
267 l_curr_due_days_b2_url_1 || ''') FII_AR_CR_BKT_AMT_DRILL_B2 ';
268 END IF;
269
270 IF (l_bucket_ct >= 3) THEN
271 l_url_sql := l_url_sql ||
272 ', DECODE(FII_AR_CURR_REC_BUCKET_AMT_B3, 0, NULL, NULL, NULL, '''||
273 l_curr_due_days_b3_url_1 || ''') FII_AR_CR_BKT_AMT_DRILL_B3';
274 END IF;
275
276 l_url_sql := l_url_sql || ', NULL FII_AR_CUSTOMER_DRILL';
277
278 ELSIF ((l_viewby = 'CUSTOMER+FII_CUSTOMERS') AND (l_hierarchical_flag = 'N')) THEN
279 l_url_sql :=
280 ', DECODE(FII_AR_OPEN_REC_AMT, 0, NULL, NULL, NULL, '''||
281 l_open_rec_amt_det_url || ''') FII_AR_OPEN_REC_DRILL,
282 DECODE(FII_AR_CURR_REC_AMT, 0, NULL, NULL, NULL, '''||
283 l_curr_rec_amt_det_url || ''') FII_AR_CURR_REC_AMT_DRILL ';
284
285 IF (l_bucket_ct >= 1) THEN
286 l_url_sql := l_url_sql ||
287 ', DECODE(FII_AR_CURR_REC_BUCKET_AMT_B1, 0, NULL, NULL, NULL, '''||
288 l_curr_due_days_b1_det_url || ''') FII_AR_CR_BKT_AMT_DRILL_B1 ';
289 END IF;
290
291 IF (l_bucket_ct >= 2) THEN
292 l_url_sql := l_url_sql ||
293 ', DECODE(FII_AR_CURR_REC_BUCKET_AMT_B2, 0, NULL, NULL, NULL, '''||
294 l_curr_due_days_b2_det_url || ''') FII_AR_CR_BKT_AMT_DRILL_B2 ';
295 END IF;
296
297 IF (l_bucket_ct >= 3) THEN
298 l_url_sql := l_url_sql ||
299 ', DECODE(FII_AR_CURR_REC_BUCKET_AMT_B3, 0, NULL, NULL, NULL, '''||
300 l_curr_due_days_b3_det_url || ''') FII_AR_CR_BKT_AMT_DRILL_B3 ';
301 END IF;
302
303 l_url_sql := l_url_sql || ', NULL FII_AR_CUSTOMER_DRILL';
304
305 ELSIF (l_viewby = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') THEN
306 l_url_sql :=
307 ', DECODE(FII_AR_OPEN_REC_AMT, 0, NULL, NULL, NULL, '''||
308 l_or_ca_amt_det_url || ''') FII_AR_OPEN_REC_DRILL,
309 DECODE(FII_AR_CURR_REC_AMT, 0, NULL, NULL, NULL, '''||
310 l_cr_ca_amt_det_url || ''') FII_AR_CURR_REC_AMT_DRILL ';
311
312 IF (l_bucket_ct >= 1) THEN
313 l_url_sql := l_url_sql ||
314 ', DECODE(FII_AR_CURR_REC_BUCKET_AMT_B1, 0, NULL, NULL, NULL, '''||
315 l_cdd_ca_b1_det_url || ''') FII_AR_CR_BKT_AMT_DRILL_B1 ';
316 END IF;
317
318 IF (l_bucket_ct >= 2) THEN
319 l_url_sql := l_url_sql ||
320 ', DECODE(FII_AR_CURR_REC_BUCKET_AMT_B2, 0, NULL, NULL, NULL, '''||
321 l_cdd_ca_b2_det_url || ''') FII_AR_CR_BKT_AMT_DRILL_B2 ';
322 END IF;
323
324 IF (l_bucket_ct >= 3) THEN
325 l_url_sql := l_url_sql ||
326 ', DECODE(FII_AR_CURR_REC_BUCKET_AMT_B3, 0, NULL, NULL, NULL, '''||
327 l_cdd_ca_b3_det_url || ''') FII_AR_CR_BKT_AMT_DRILL_B3 ';
328 END IF;
329
330 l_url_sql := l_url_sql || ', NULL FII_AR_CUSTOMER_DRILL';
331
332 ELSIF ((l_viewby = 'CUSTOMER+FII_CUSTOMERS') AND (l_hierarchical_flag = 'Y')) THEN
333 l_url_sql :=
334 ', DECODE(FII_AR_OPEN_REC_AMT, 0, NULL, NULL, NULL,
335 DECODE(is_self_flag, ''Y'', '''|| l_open_rec_amt_det_url || '''
336 , DECODE(is_leaf_flag, ''Y'', '''|| l_open_rec_amt_det_url || ''',
337 '''|| l_open_rec_amt_url || '''))) FII_AR_OPEN_REC_DRILL,
338 DECODE(FII_AR_CURR_REC_AMT, 0, NULL, NULL, NULL,
339 DECODE(is_self_flag, ''Y'', '''|| l_curr_rec_amt_det_url || '''
340 , DECODE(is_leaf_flag, ''Y'', '''|| l_curr_rec_amt_det_url || ''',
341 '''|| l_curr_rec_amt_url || '''))) FII_AR_CURR_REC_AMT_DRILL ';
342
343 IF (l_bucket_ct >= 1) THEN
344 l_url_sql := l_url_sql ||
345 ', DECODE(FII_AR_CURR_REC_BUCKET_AMT_B1, 0, NULL, NULL, NULL,
346 DECODE(is_self_flag, ''Y'', '''|| l_curr_due_days_b1_det_url || '''
347 , DECODE(is_leaf_flag, ''Y'', '''|| l_curr_due_days_b1_det_url || ''',
348 '''|| l_curr_due_days_b1_url || '''))) FII_AR_CR_BKT_AMT_DRILL_B1 ';
349 END IF;
350
351 IF (l_bucket_ct >= 2) THEN
352 l_url_sql := l_url_sql ||
353 ', DECODE(FII_AR_CURR_REC_BUCKET_AMT_B2, 0, NULL, NULL, NULL,
354 DECODE(is_self_flag, ''Y'', '''|| l_curr_due_days_b2_det_url || '''
355 , DECODE(is_leaf_flag, ''Y'', '''|| l_curr_due_days_b2_det_url || ''',
356 '''|| l_curr_due_days_b2_url || '''))) FII_AR_CR_BKT_AMT_DRILL_B2 ';
357 END IF;
358
359 IF (l_bucket_ct >= 3) THEN
360 l_url_sql := l_url_sql ||
361 ', DECODE(FII_AR_CURR_REC_BUCKET_AMT_B3, 0, NULL, NULL, NULL,
362 DECODE(is_self_flag, ''Y'', '''|| l_curr_due_days_b3_det_url || '''
363 , DECODE(is_leaf_flag, ''Y'', '''|| l_curr_due_days_b3_det_url || ''',
364 '''|| l_curr_due_days_b3_url || '''))) FII_AR_CR_BKT_AMT_DRILL_B3';
365 END IF;
366
367 l_url_sql := l_url_sql ||
368 ', DECODE(is_self_flag, ''Y'', NULL,
369 DECODE(is_leaf_flag, ''N'', '''|| l_customer_url || ''',
370 NULL)) FII_AR_CUSTOMER_DRILL ';
371
372
373 END IF;
374
375 -----------------------------------------
376 -- Construct the order by clause
377 -----------------------------------------
378 IF(instr(fii_ar_util_pkg.g_order_by, ',') <> 0) THEN
379 /*This means no particular sort column is selected in the report. So sort on
380 the default column in descending order. NVL is added to make sure the null
381 values appear last. */
382 l_order_by_clause := 'ORDER BY NVL(FII_AR_CURR_REC_AMT, -999999999) DESC';
383
384 ELSIF(instr(fii_ar_util_pkg.g_order_by, 'DESC') <> 0)THEN
385 /*This means a particular sort column is clicked to have descending order in which
386 case we would want all the null values to appear last in the report so add an
387 NVL to that column.*/
388 l_order_column := substr(fii_ar_util_pkg.g_order_by, 1,
389 instr(fii_ar_util_pkg.g_order_by, ' DESC'));
390 l_order_by_clause := 'ORDER BY NVL('||l_order_column ||', -999999999) DESC';
391 ELSE
392 /*This is the case when user has asked for an ascending order sort. Use PMV's
393 order by clause*/
394 l_order_by_clause := ' &ORDER_BY_CLAUSE';
395 END IF;
396
397 --------------------------------------
398 -- Construct the bucket sql statements
399 --------------------------------------
400 i := 1;
401
402 IF (l_bucket_ct >= 1) THEN
403 l_bucket_sql :=
404 ', sum(current_bucket_1_amount) FII_AR_CURR_REC_BUCKET_AMT_B1';
405 END IF;
406
407 FOR i IN 2..l_bucket_ct LOOP
408 IF (i > l_max_bucket_ct) THEN
409 l_bucket_sql := l_bucket_sql ||
410 ', NULL FII_AR_CURR_REC_BUCKET_AMT_B'||i;
411 ELSE
412 l_bucket_sql := l_bucket_sql || ', sum(current_bucket_' || i ||
413 '_amount) FII_AR_CURR_REC_BUCKET_AMT_B'||i;
414 END IF;
415 END LOOP;
416
417 -----------------------------------------
418 -- Construct the conditional where clause
419 -----------------------------------------
420 -- Only add the join on collector_id if we have a specific collector selected
421 -- or view by = Collector
422 IF ((l_viewby = 'FII_COLLECTOR+FII_COLLECTOR') OR
423 (l_collector_id <> '-111')) THEN
424 l_where_clause := l_where_clause ||
425 'AND f.collector_id = v.collector_id ';
426 END IF;
427
428 -- Only add the join on cust_acct_id if we have a specific customer acct
429 -- selected or when view by = Customer Account
430 IF ((l_viewby = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') OR
431 (l_cust_account_id <> -111)) THEN
432 l_where_clause := l_where_clause ||
433 'AND f.cust_account_id = v.cust_account_id ';
434 END IF;
435
436 -- Only add the join on parent_party_id when view by = Customer
437 IF (l_viewby = 'CUSTOMER+FII_CUSTOMERS') THEN
438 l_where_clause := l_where_clause ||
439 'AND f.parent_party_id = v.parent_party_id ';
440 END IF;
441
442 -- Only add the join on party_id when we have a specific customer
443 -- selected or when view by = Customer
444 IF (l_cust_id <> '-111' OR l_viewby = 'CUSTOMER+FII_CUSTOMERS') THEN
445 l_where_clause := l_where_clause ||
446 'AND f.party_id = v.party_id ';
447 END IF;
448
449 -------------------------------
450 -- Construct the sql statements
451 -------------------------------
452 l_sqlstmt :=
453 'SELECT VIEWBY,
454 VIEWBYID ';
455
456 IF (l_bucket_ct >= 1) THEN
457 l_sqlstmt := l_sqlstmt ||
458 ', FII_AR_CURR_REC_BUCKET_AMT_B1 FII_AR_CURR_REC_BKT_AMT_G_B1';
459 END IF;
460
461 FOR i IN 2..l_bucket_ct LOOP
462 IF (i > l_max_bucket_ct) THEN
463 l_sqlstmt := l_sqlstmt ||
464 ', NULL FII_AR_CURR_REC_BUCKET_AMT_B' || i || ' FII_AR_CURR_REC_BKT_AMT_G_B' || i;
465 ELSE
466 l_sqlstmt := l_sqlstmt ||
467 ', FII_AR_CURR_REC_BUCKET_AMT_B'|| i || ' FII_AR_CURR_REC_BKT_AMT_G_B' || i;
468 END IF;
469 END LOOP;
470
471 l_sqlstmt := l_sqlstmt ||
472 ', FII_AR_OPEN_REC_AMT,
473 FII_AR_CURR_REC_AMT,
474 FII_AR_CURR_REC_CT,
475 FII_AR_WEIGHTED_TO ';
476
477 IF (l_bucket_ct >= 1) THEN
478 l_sqlstmt := l_sqlstmt ||
479 ', FII_AR_CURR_REC_BUCKET_AMT_B1';
480 END IF;
481
482 FOR i IN 2..l_bucket_ct LOOP
483 IF (i > l_max_bucket_ct) THEN
484 l_sqlstmt := l_sqlstmt ||
485 ', NULL FII_AR_CURR_REC_BUCKET_AMT_B' ||i;
486 ELSE
487 l_sqlstmt := l_sqlstmt ||
488 ', FII_AR_CURR_REC_BUCKET_AMT_B'||i;
489 END IF;
490 END LOOP;
491
492 l_sqlstmt := l_sqlstmt || ',
493 sum(FII_AR_OPEN_REC_AMT) over() FII_AR_GT_OPEN_REC_AMT,
494 sum(FII_AR_CURR_REC_AMT) over() FII_AR_GT_CURR_REC_AMT,
495 sum(FII_AR_CURR_REC_CT) over() FII_AR_GT_CURR_REC_CT,
496 sum(WTD_TERMS_OUT_CURRENT_NUM) over() /
497 NULLIF(sum(FII_AR_CURR_REC_AMT) over (), 0) FII_AR_GT_WEIGHTED_TO ';
498
499 IF (l_bucket_ct >= 1) THEN
500 l_sqlstmt := l_sqlstmt ||
501 ', sum(FII_AR_CURR_REC_BUCKET_AMT_B1) over() FII_AR_GT_CURR_REC_BKT_AMT_B1';
502 END IF;
503
504 FOR i IN 2..l_bucket_ct LOOP
505 IF (i > l_max_bucket_ct) THEN
506 l_sqlstmt := l_sqlstmt ||
507 ', NULL FII_AR_GT_CURR_REC_BKT_AMT_B'||i;
508 ELSE
509 l_sqlstmt := l_sqlstmt ||
510 ', sum(FII_AR_CURR_REC_BUCKET_AMT_B'||i||') over() FII_AR_GT_CURR_REC_BKT_AMT_B'||i;
511 END IF;
512 END LOOP;
513
514 -- Attach the drilldown URL sql to the sql statement
515 l_sqlstmt := l_sqlstmt || l_url_sql;
516
517 FOR i IN 4..l_bucket_ct LOOP
518 l_sqlstmt := l_sqlstmt
519 || ', NULL FII_AR_CR_BKT_AMT_DRILL_B' || i;
520 END LOOP;
521
522 l_sqlstmt := l_sqlstmt || ' FROM (
523 SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
524 v.viewby VIEWBY,
525 v.viewby_code VIEWBYID,
526 sum(f.total_open_amount) FII_AR_OPEN_REC_AMT,
527 sum(f.current_open_amount) FII_AR_CURR_REC_AMT,
528 sum(f.current_open_count) FII_AR_CURR_REC_CT,
529 sum(f.wtd_terms_out_current_num) WTD_TERMS_OUT_CURRENT_NUM,
530 sum(f.wtd_terms_out_current_num) / NULLIF(sum(f.current_open_amount),0) FII_AR_WEIGHTED_TO ' ||
531 l_bucket_sql || l_cust_clause || '
532 FROM fii_ar_net_rec'||l_cust_suffix||'_mv'||l_curr_suffix||' f,
533 (SELECT /*+ no_merge '||l_gt_hint|| ' */ *
534 FROM fii_time_structures cal, '||
535 fii_ar_util_pkg.get_from_statement ||
536 ' gt WHERE cal.report_date = :ASOF_DATE
537 AND bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
538 AND '|| fii_ar_util_pkg.get_where_statement || ') v
539 WHERE f.time_id = v.time_id
540 AND f.period_type_id = v.period_type_id
541 AND f.org_id = v.org_id
542 AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_where_clause ||
543 ' GROUP BY v.viewby, v.viewby_code ' || l_cust_clause || ' ) inner_view ' || l_order_by_clause;
544
545
546 FII_AR_UTIL_PKG.Bind_Variable(
547 p_sqlstmt => l_sqlstmt,
548 p_page_parameter_tbl => p_page_parameter_tbl,
549 p_sql_output => curr_rec_sql,
550 p_bind_output_table => curr_rec_output);
551
552
553 END GET_CURR_REC_SUM;
554
555 END FII_AR_CURR_REC_SUM_PKG;