[Home] [Help]
PACKAGE BODY: APPS.FII_AR_REC_AGING_PKG
Source
1 PACKAGE BODY FII_AR_REC_AGING_PKG AS
2 /* $Header: FIIARDBIRAB.pls 120.16 2007/05/15 20:52:06 vkazhipu ship $ */
3
4 -----------------------------------------------------------------------------
5 -- This procedure is called by the Pastdue Receivables Aging Summary report
6 -------------------------------------------------------------------------------
7 PROCEDURE get_pastdue_rec_aging
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_url_sql VARCHAR2(6000);
15 l_bucket_graph_sql VARCHAR2(1000);
16 l_bucket_sql VARCHAR2(1000);
17 l_dispute_bkt_graph_sql VARCHAR2(1000);
18 l_dispute_bkt_sql VARCHAR2(1000);
19 l_bucket_ct NUMBER;
20 l_max_bucket_ct NUMBER := 7; -- Maximum number of bucket ranges
21 i NUMBER;
22
23 l_as_of_date DATE;
24 l_cust_suffix VARCHAR2(6);
25 l_curr_suffix VARCHAR2(4);
26 l_collector_id VARCHAR2(30);
27 l_cust_id VARCHAR2(500);
28 l_cust_account_id VARCHAR2(30);
29 l_itd_bitand NUMBER;
30 l_viewby VARCHAR2(100);
31 l_hierarchical_flag VARCHAR2(1);
32 l_cust_clause VARCHAR2(100);
33 l_cust_clause2 VARCHAR2(100);
34
35 l_customer_url VARCHAR2(500) := NULL;
36 l_open_rec_amt_det_url VARCHAR2(500) := NULL;
37 l_pastdue_rec_amt_det_url VARCHAR2(500) := NULL;
38 l_days_past_due_b1_det_url VARCHAR2(500) := NULL;
39 l_days_past_due_b2_det_url VARCHAR2(500) := NULL;
40 l_days_past_due_b3_det_url VARCHAR2(500) := NULL;
41 l_days_past_due_b4_det_url VARCHAR2(500) := NULL;
42 l_days_past_due_b5_det_url VARCHAR2(500) := NULL;
43 l_days_past_due_b6_det_url VARCHAR2(500) := NULL;
44 l_days_past_due_b7_det_url VARCHAR2(500) := NULL;
45
46 l_open_rec_amt_url VARCHAR2(500) := NULL;
47 l_pastdue_rec_amt_url VARCHAR2(500) := NULL;
48 l_days_past_due_b1_url VARCHAR2(500) := NULL;
49 l_days_past_due_b2_url VARCHAR2(500) := NULL;
50 l_days_past_due_b3_url VARCHAR2(500) := NULL;
51 l_days_past_due_b4_url VARCHAR2(500) := NULL;
52 l_days_past_due_b5_url VARCHAR2(500) := NULL;
53 l_days_past_due_b6_url VARCHAR2(500) := NULL;
54 l_days_past_due_b7_url VARCHAR2(500) := NULL;
55
56 l_open_rec_amt_url_1 VARCHAR2(500) := NULL;
57 l_pastdue_rec_amt_url_1 VARCHAR2(500) := NULL;
58 l_days_past_due_b1_url_1 VARCHAR2(500) := NULL;
59 l_days_past_due_b2_url_1 VARCHAR2(500) := NULL;
60 l_days_past_due_b3_url_1 VARCHAR2(500) := NULL;
61 l_days_past_due_b4_url_1 VARCHAR2(500) := NULL;
62 l_days_past_due_b5_url_1 VARCHAR2(500) := NULL;
63 l_days_past_due_b6_url_1 VARCHAR2(500) := NULL;
64 l_days_past_due_b7_url_1 VARCHAR2(500) := NULL;
65
66 l_order_by VARCHAR2(500);
67 l_order_column VARCHAR2(100);
68 l_gt_hint varchar2(500);
69 BEGIN
70 -- Reset all the global variables to NULL or to the default value
71 fii_ar_util_pkg.reset_globals;
72
73 -- Get the parameters and set the global variables
74 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
75
76 -- Retrieve values for global variables
77 l_as_of_date := fii_ar_util_pkg.g_as_of_date;
78 l_cust_suffix := fii_ar_util_pkg.g_cust_suffix;
79 l_curr_suffix := fii_ar_util_pkg.g_curr_suffix;
80 l_collector_id := fii_ar_util_pkg.g_collector_id;
81 l_cust_id := fii_ar_util_pkg.g_party_id;
82 l_cust_account_id := fii_ar_util_pkg.g_cust_account_id;
83 l_itd_bitand := fii_ar_util_pkg.g_bitand_inc_todate;
84 l_viewby := fii_ar_util_pkg.g_view_by;
85 l_hierarchical_flag := fii_ar_util_pkg.g_is_hierarchical_flag;
86
87 -- Populate global temp table based on the parameters chosen
88 fii_ar_util_pkg.populate_summary_gt_tables;
89 l_gt_hint := ' leading(gt) cardinality(gt 1) ';
90 -- Find out the number of bucket ranges customized for this report
91 SELECT sum(decode(bbc.range1_low, null, 0, 1) +
92 decode(bbc.range2_low, null, 0, 1) +
93 decode(bbc.range3_low, null, 0, 1) +
94 decode(bbc.range4_low, null, 0, 1) +
95 decode(bbc.range5_low, null, 0, 1) +
96 decode(bbc.range6_low, null, 0, 1) +
97 decode(bbc.range7_low, null, 0, 1) +
98 decode(bbc.range8_low, null, 0, 1) +
99 decode(bbc.range9_low, null, 0, 1) +
100 decode(bbc.range10_low, null, 0, 1)) bucket_count
101 INTO l_bucket_ct
102 FROM bis_bucket_customizations bbc,
103 bis_bucket bb
104 WHERE bb.short_name = 'FII_DBI_PAST_DUE_REC_BUCKET'
105 AND bbc.bucket_id = bb.bucket_id;
106
107 -- Construct the self node clause
108 -- We only need this when view by customer and it is a hierarchical setup
109 IF (l_viewby = 'CUSTOMER+FII_CUSTOMERS') AND (l_hierarchical_flag = 'Y') THEN
110 l_cust_clause := ' , v.is_self_flag, v.is_leaf_flag ';
111 l_cust_clause2 := ' , is_self_flag, is_leaf_flag ';
112 ELSE
113 l_cust_clause := NULL;
114 l_cust_clause2 := NULL;
115 END IF;
116
117 -----------------------------------------------------------------------------
118 -- When view by Customer for leaf level customers or view by Customer Acct,
119 -- we'll use the following drilldown URLs:
120 --
121 -- 1. Open Receivables amount will drill to Open Receivables Detail Report
122 -- (Transaction Detail)
123 -- 2. Past Due Receivables amount will drill to Past Due Receivables Detail
124 -- Report (Transaction Detail)
125 -- 3. Aging bucket X amount will drill to Receivables X days Past Due Detail
126 -- report (Transaction Detail)
127 -----------------------------------------------------------------------------
128
129 IF l_viewby = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
130 l_gt_hint := ' leading(gt.gt) cardinality(gt.gt 1) ';
131 --This is for sending customer id in the detail drills
132 l_cust_clause := l_cust_clause || ' , v.party_id ';
133 l_cust_clause2 := l_cust_clause2 || ' , party_id ';
134
135 -- Open Receivables Amount Drilldown URL
136 l_open_rec_amt_det_url := 'pFunctionName=FII_AR_OPEN_REC_DTL&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inline_view.party_id||''&FII_AR_CUST_ACCOUNT=VIEWBYID&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
137
138 -- Past Due Receivables Amount Drilldown URL
139 l_pastdue_rec_amt_det_url := 'pFunctionName=FII_AR_PDUE_REC_DTL&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inline_view.party_id||''&FII_AR_CUST_ACCOUNT=VIEWBYID&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
140 ELSE
141 -- Open Receivables Amount Drilldown URL
142 l_open_rec_amt_det_url := 'pFunctionName=FII_AR_OPEN_REC_DTL&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
143
144 -- Past Due Receivables Amount Drilldown URL
145 l_pastdue_rec_amt_det_url := 'pFunctionName=FII_AR_PDUE_REC_DTL&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
146
147 END IF;
148
149 -- Aging Bucket X Amount Drilldown URL
150 IF (l_bucket_ct >= 1) THEN
151 IF l_viewby = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
152 l_days_past_due_b1_det_url := 'pFunctionName=FII_AR_REC_PDUE_BUCKET&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inline_view.party_id||''&FII_AR_CUST_ACCOUNT=VIEWBYID&FII_AR_BUCKET_NUM=1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
153 ELSIF l_viewby = 'CUSTOMER+FII_CUSTOMERS' THEN
154 l_days_past_due_b1_det_url := 'pFunctionName=FII_AR_REC_PDUE_BUCKET&FII_AR_BUCKET_NUM=1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
155 END IF;
156 END IF;
157
158 IF (l_bucket_ct >= 2) THEN
159 IF l_viewby = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
160 l_days_past_due_b2_det_url := 'pFunctionName=FII_AR_REC_PDUE_BUCKET&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inline_view.party_id||''&FII_AR_CUST_ACCOUNT=VIEWBYID&FII_AR_BUCKET_NUM=2&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
161 ELSIF l_viewby = 'CUSTOMER+FII_CUSTOMERS' THEN
162 l_days_past_due_b2_det_url := 'pFunctionName=FII_AR_REC_PDUE_BUCKET&FII_AR_BUCKET_NUM=2&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
163 END IF;
164 END IF;
165
166 IF (l_bucket_ct >= 3) THEN
167 IF l_viewby = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
168 l_days_past_due_b3_det_url := 'pFunctionName=FII_AR_REC_PDUE_BUCKET&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inline_view.party_id||''&FII_AR_CUST_ACCOUNT=VIEWBYID&FII_AR_BUCKET_NUM=3&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
169 ELSIF l_viewby = 'CUSTOMER+FII_CUSTOMERS' THEN
170 l_days_past_due_b3_det_url := 'pFunctionName=FII_AR_REC_PDUE_BUCKET&FII_AR_BUCKET_NUM=3&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
171 END IF;
172 END IF;
173
174 IF (l_bucket_ct >= 4) THEN
175 IF l_viewby = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
176 l_days_past_due_b4_det_url := 'pFunctionName=FII_AR_REC_PDUE_BUCKET&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inline_view.party_id||''&FII_AR_BUCKET_NUM=4&FII_AR_CUST_ACCOUNT=VIEWBYID&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
177 ELSIF l_viewby = 'CUSTOMER+FII_CUSTOMERS' THEN
178 l_days_past_due_b4_det_url := 'pFunctionName=FII_AR_REC_PDUE_BUCKET&FII_AR_BUCKET_NUM=4&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
179 END IF;
180 END IF;
181
182 IF (l_bucket_ct >= 5) THEN
183 IF l_viewby = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
184 l_days_past_due_b5_det_url := 'pFunctionName=FII_AR_REC_PDUE_BUCKET&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inline_view.party_id||''&FII_AR_CUST_ACCOUNT=VIEWBYID&FII_AR_BUCKET_NUM=5&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
185 ELSIF l_viewby = 'CUSTOMER+FII_CUSTOMERS' THEN
186 l_days_past_due_b5_det_url := 'pFunctionName=FII_AR_REC_PDUE_BUCKET&FII_AR_BUCKET_NUM=5&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
187 END IF;
188 END IF;
189
190 IF (l_bucket_ct >= 6) THEN
191 IF l_viewby = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
192 l_days_past_due_b6_det_url := 'pFunctionName=FII_AR_REC_PDUE_BUCKET&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inline_view.party_id||''&FII_AR_CUST_ACCOUNT=VIEWBYID&FII_AR_BUCKET_NUM=6&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
193 ELSIF l_viewby = 'CUSTOMER+FII_CUSTOMERS' THEN
194 l_days_past_due_b6_det_url := 'pFunctionName=FII_AR_REC_PDUE_BUCKET&FII_AR_BUCKET_NUM=6&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
195 END IF;
196 END IF;
197
198 IF (l_bucket_ct >= 7) THEN
199 IF l_viewby = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
200 l_days_past_due_b7_det_url := 'pFunctionName=FII_AR_REC_PDUE_BUCKET&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inline_view.party_id||''&FII_AR_CUST_ACCOUNT=VIEWBYID&FII_AR_BUCKET_NUM=7&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
201 ELSIF l_viewby = 'CUSTOMER+FII_CUSTOMERS' THEN
202 l_days_past_due_b7_det_url := 'pFunctionName=FII_AR_REC_PDUE_BUCKET&FII_AR_BUCKET_NUM=7&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
203 END IF;
204 END IF;
205
206 -----------------------------------------------------------------------------
207 -- When view by OU, Collector, or Customer (for rollup customers),
208 -- we'll use the following drilldown URLs:
209 --
210 -- 1. Open Receivables amount will drill to Open Receivables Summary
211 -- (View by Customer Account)
212 -- 2. Past Due Receivables amount will drill to Past Due Receivables Aging
213 -- Summary (View by Customer Account)
214 -- 3. Aging bucket X amount will drill to Past Due Receivables Aging Summary
215 -- (View by Customer Account)
216 -----------------------------------------------------------------------------
217 --Drill when View by OU or Collector
218 IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
219 -- Open Receivables Amount Drilldown URL
220 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';
221
222 -- Past Due Receivables Amount Drilldown URL
223 l_pastdue_rec_amt_url_1 := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
224
225 -- Aging Bucket X Amount Drilldown URL
226 IF (l_bucket_ct >= 1) THEN
227 l_days_past_due_b1_url_1 := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
228 END IF;
229
230 IF (l_bucket_ct >= 2) THEN
231 l_days_past_due_b2_url_1 := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
232 END IF;
233
234 IF (l_bucket_ct >= 3) THEN
235 l_days_past_due_b3_url_1 := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
236 END IF;
237
238 IF (l_bucket_ct >= 4) THEN
239 l_days_past_due_b4_url_1 := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
240 END IF;
241
242 IF (l_bucket_ct >= 5) THEN
243 l_days_past_due_b5_url_1 := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
244 END IF;
245
246 IF (l_bucket_ct >= 6) THEN
247 l_days_past_due_b6_url_1 := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
248 END IF;
249
250 IF (l_bucket_ct >= 7) THEN
251 l_days_past_due_b7_url_1 := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
252 END IF;
253 ELSE
254 -- Open Receivables Amount Drilldown URL
255 l_open_rec_amt_url_1 := 'pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
256
257 -- Past Due Receivables Amount Drilldown URL
258 l_pastdue_rec_amt_url_1 := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
259
260 -- Aging Bucket X Amount Drilldown URL
261 IF (l_bucket_ct >= 1) THEN
262 l_days_past_due_b1_url_1 := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
263 END IF;
264
265 IF (l_bucket_ct >= 2) THEN
266 l_days_past_due_b2_url_1 := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
267 END IF;
268
269 IF (l_bucket_ct >= 3) THEN
270 l_days_past_due_b3_url_1 := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
271 END IF;
272
273 IF (l_bucket_ct >= 4) THEN
274 l_days_past_due_b4_url_1 := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
275 END IF;
276
277 IF (l_bucket_ct >= 5) THEN
278 l_days_past_due_b5_url_1 := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
279 END IF;
280
281 IF (l_bucket_ct >= 6) THEN
282 l_days_past_due_b6_url_1 := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
283 END IF;
284
285 IF (l_bucket_ct >= 7) THEN
286 l_days_past_due_b7_url_1 := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
287 END IF;
288
289 END IF;
290
291
292 -- Open Receivables Amount Drilldown URL
293 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';
294
295 -- Past Due Receivables Amount Drilldown URL
296 l_pastdue_rec_amt_url := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
297
298 -- Aging Bucket X Amount Drilldown URL
299 IF (l_bucket_ct >= 1) THEN
300 l_days_past_due_b1_url := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
301 END IF;
302
303 IF (l_bucket_ct >= 2) THEN
304 l_days_past_due_b2_url := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
305 END IF;
306
307 IF (l_bucket_ct >= 3) THEN
308 l_days_past_due_b3_url := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
309 END IF;
310
311 IF (l_bucket_ct >= 4) THEN
312 l_days_past_due_b4_url := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
313 END IF;
314
315 IF (l_bucket_ct >= 5) THEN
316 l_days_past_due_b5_url := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
317 END IF;
318
319 IF (l_bucket_ct >= 6) THEN
320 l_days_past_due_b6_url := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
321 END IF;
322
323 IF (l_bucket_ct >= 7) THEN
324 l_days_past_due_b7_url := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
325 END IF;
326
327 -----------------------------------------------------------------------------
328 -- When view by Customer and the customer is not a leaf node,
329 -- we'll drilldown to the next level in the customer hierarchy on the same report
330 -----------------------------------------------------------------------------
331 l_customer_url := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
332
333 ----------------------------------------------------------------
334 -- Construct the drilldown URL sql
335 ----------------------------------------------------------------
336 IF (l_viewby IN ('ORGANIZATION+FII_OPERATING_UNITS',
337 'FII_COLLECTOR+FII_COLLECTOR')) THEN
338 l_url_sql :=
339 ', DECODE((SUM(FII_AR_PASTDUE_REC_AMT) + SUM(FII_AR_OPEN_REC_AMT)), 0, NULL, NULL, NULL, '''||
340 l_open_rec_amt_url_1 || ''') FII_AR_OPEN_REC_DRILL,
341 DECODE(sum(FII_AR_PASTDUE_REC_AMT), 0, NULL, NULL, NULL, '''||
342 l_pastdue_rec_amt_url_1 || ''') FII_AR_PASTDUE_REC_DRILL, ';
343
344 IF (l_bucket_ct >= 1) THEN
345 l_url_sql := l_url_sql ||
346 ' DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B1), 0, NULL, NULL, NULL, '''||
347 l_days_past_due_b1_url_1 || ''') FII_AR_PD_BKT_AMT_DRILL_B1 ';
348 END IF;
349
350 IF (l_bucket_ct >= 2) THEN
351 l_url_sql := l_url_sql ||
352 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B2), 0, NULL, NULL, NULL, '''||
353 l_days_past_due_b2_url_1 || ''') FII_AR_PD_BKT_AMT_DRILL_B2 ';
354 END IF;
355
356 IF (l_bucket_ct >= 3) THEN
357 l_url_sql := l_url_sql ||
358 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B3), 0, NULL, NULL, NULL, '''||
359 l_days_past_due_b3_url_1 || ''') FII_AR_PD_BKT_AMT_DRILL_B3 ';
360 END IF;
361
362 IF (l_bucket_ct >= 4) THEN
363 l_url_sql := l_url_sql ||
364 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B4), 0, NULL, NULL, NULL, '''||
365 l_days_past_due_b4_url_1 || ''') FII_AR_PD_BKT_AMT_DRILL_B4 ';
366 END IF;
367
368 IF (l_bucket_ct >= 5) THEN
369 l_url_sql := l_url_sql ||
370 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B5), 0, NULL, NULL, NULL, '''||
371 l_days_past_due_b5_url_1 || ''') FII_AR_PD_BKT_AMT_DRILL_B5';
372 END IF;
373
374 IF (l_bucket_ct >= 6) THEN
375 l_url_sql := l_url_sql ||
376 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B6), 0, NULL, NULL, NULL, '''||
377 l_days_past_due_b6_url_1 || ''') FII_AR_PD_BKT_AMT_DRILL_B6';
378 END IF;
379
380 IF (l_bucket_ct >= 7) THEN
381 l_url_sql := l_url_sql ||
382 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B7), 0, NULL, NULL, NULL, '''||
383 l_days_past_due_b7_url_1 || ''') FII_AR_PD_BKT_AMT_DRILL_B7';
384 END IF;
385
386 l_url_sql := l_url_sql || ', NULL FII_AR_CUSTOMER_DRILL';
387
388 ELSIF (( l_viewby = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') OR
389 ((l_viewby = 'CUSTOMER+FII_CUSTOMERS') AND (l_hierarchical_flag = 'N'))) THEN
390 l_url_sql :=
391 ', DECODE((SUM(FII_AR_PASTDUE_REC_AMT) + SUM(FII_AR_OPEN_REC_AMT)), 0, NULL, NULL, NULL, '''||
392 l_open_rec_amt_det_url || ''') FII_AR_OPEN_REC_DRILL,
393 DECODE(sum(FII_AR_PASTDUE_REC_AMT), 0, NULL, NULL, NULL, '''||
394 l_pastdue_rec_amt_det_url || ''') FII_AR_PASTDUE_REC_DRILL, ';
395
396 IF (l_bucket_ct >= 1) THEN
397 l_url_sql := l_url_sql ||
398 ' DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B1), 0, NULL, NULL, NULL, '''||
399 l_days_past_due_b1_det_url || ''') FII_AR_PD_BKT_AMT_DRILL_B1 ';
400 END IF;
401
402 IF (l_bucket_ct >= 2) THEN
403 l_url_sql := l_url_sql ||
404 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B2), 0, NULL, NULL, NULL, '''||
405 l_days_past_due_b2_det_url || ''') FII_AR_PD_BKT_AMT_DRILL_B2 ';
406 END IF;
407
408 IF (l_bucket_ct >= 3) THEN
409 l_url_sql := l_url_sql ||
410 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B3), 0, NULL, NULL, NULL, '''||
411 l_days_past_due_b3_det_url || ''') FII_AR_PD_BKT_AMT_DRILL_B3 ';
412 END IF;
413
414 IF (l_bucket_ct >= 4) THEN
415 l_url_sql := l_url_sql ||
416 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B4), 0, NULL, NULL, NULL, '''||
417 l_days_past_due_b4_det_url || ''') FII_AR_PD_BKT_AMT_DRILL_B4 ';
418 END IF;
419
420 IF (l_bucket_ct >= 5) THEN
421 l_url_sql := l_url_sql ||
422 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B5), 0, NULL, NULL, NULL, '''||
423 l_days_past_due_b5_det_url || ''') FII_AR_PD_BKT_AMT_DRILL_B5';
424 END IF;
425
426 IF (l_bucket_ct >= 6) THEN
427 l_url_sql := l_url_sql ||
428 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B6), 0, NULL, NULL, NULL, '''||
429 l_days_past_due_b6_det_url || ''') FII_AR_PD_BKT_AMT_DRILL_B6';
430 END IF;
431
432 IF (l_bucket_ct >= 7) THEN
433 l_url_sql := l_url_sql ||
434 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B7), 0, NULL, NULL, NULL, '''||
435 l_days_past_due_b7_det_url || ''') FII_AR_PD_BKT_AMT_DRILL_B7';
436 END IF;
437
438 l_url_sql := l_url_sql || ', NULL FII_AR_CUSTOMER_DRILL';
439
440 ELSIF ((l_viewby = 'CUSTOMER+FII_CUSTOMERS') AND (l_hierarchical_flag = 'Y')) THEN
441 l_url_sql :=
442 ', DECODE((SUM(FII_AR_PASTDUE_REC_AMT) + SUM(FII_AR_OPEN_REC_AMT)), 0, NULL, NULL, NULL,
443 DECODE(is_self_flag, ''Y'', '''|| l_open_rec_amt_det_url || '''
444 , DECODE(is_leaf_flag, ''Y'', '''|| l_open_rec_amt_det_url || ''',
445 '''|| l_open_rec_amt_url || '''))) FII_AR_OPEN_REC_DRILL,
446 DECODE(sum(FII_AR_PASTDUE_REC_AMT), 0, NULL, NULL, NULL,
447 DECODE(is_self_flag, ''Y'', '''|| l_pastdue_rec_amt_det_url || '''
448 , DECODE(is_leaf_flag, ''Y'', '''|| l_pastdue_rec_amt_det_url || ''',
449 '''|| l_pastdue_rec_amt_url || '''))) FII_AR_PASTDUE_REC_DRILL, ';
450
451 IF (l_bucket_ct >= 1) THEN
452 l_url_sql := l_url_sql ||
453 ' DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B1), 0, NULL, NULL, NULL,
454 DECODE(is_self_flag, ''Y'', '''|| l_days_past_due_b1_det_url || '''
455 , DECODE(is_leaf_flag, ''Y'', '''|| l_days_past_due_b1_det_url || ''',
456 '''|| l_days_past_due_b1_url || '''))) FII_AR_PD_BKT_AMT_DRILL_B1 ';
457 END IF;
458
459 IF (l_bucket_ct >= 2) THEN
460 l_url_sql := l_url_sql ||
461 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B2), 0, NULL, NULL, NULL,
462 DECODE(is_self_flag, ''Y'', '''|| l_days_past_due_b2_det_url || '''
463 , DECODE(is_leaf_flag, ''Y'', '''|| l_days_past_due_b2_det_url || ''',
464 '''|| l_days_past_due_b2_url || '''))) FII_AR_PD_BKT_AMT_DRILL_B2 ';
465 END IF;
466
467 IF (l_bucket_ct >= 3) THEN
468 l_url_sql := l_url_sql ||
469 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B3), 0, NULL, NULL, NULL,
470 DECODE(is_self_flag, ''Y'', '''|| l_days_past_due_b3_det_url || '''
471 , DECODE(is_leaf_flag, ''Y'', '''|| l_days_past_due_b3_det_url || ''',
472 '''|| l_days_past_due_b3_url || '''))) FII_AR_PD_BKT_AMT_DRILL_B3 ';
473 END IF;
474
475 IF (l_bucket_ct >= 4) THEN
476 l_url_sql := l_url_sql ||
477 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B4), 0, NULL, NULL, NULL,
478 DECODE(is_self_flag, ''Y'', '''|| l_days_past_due_b4_det_url || '''
479 , DECODE(is_leaf_flag, ''Y'', '''|| l_days_past_due_b4_det_url || ''',
480 '''|| l_days_past_due_b4_url || '''))) FII_AR_PD_BKT_AMT_DRILL_B4 ';
481 END IF;
482
483 IF (l_bucket_ct >= 5) THEN
484 l_url_sql := l_url_sql ||
485 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B5), 0, NULL, NULL, NULL,
486 DECODE(is_self_flag, ''Y'', '''|| l_days_past_due_b5_det_url || '''
487 , DECODE(is_leaf_flag, ''Y'', '''|| l_days_past_due_b5_det_url || ''',
488 '''|| l_days_past_due_b5_url || '''))) FII_AR_PD_BKT_AMT_DRILL_B5';
489 END IF;
490
491 IF (l_bucket_ct >= 6) THEN
492 l_url_sql := l_url_sql ||
493 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B6), 0, NULL, NULL, NULL,
494 DECODE(is_self_flag, ''Y'', '''|| l_days_past_due_b6_det_url || '''
495 , DECODE(is_leaf_flag, ''Y'', '''|| l_days_past_due_b6_det_url || ''',
496 '''|| l_days_past_due_b6_url || '''))) FII_AR_PD_BKT_AMT_DRILL_B6';
497 END IF;
498
499 IF (l_bucket_ct >= 7) THEN
500 l_url_sql := l_url_sql ||
501 ', DECODE(sum(FII_AR_PASTDUE_BUCKET_AMT_B7), 0, NULL, NULL, NULL,
502 DECODE(is_self_flag, ''Y'', '''|| l_days_past_due_b7_det_url || '''
503 , DECODE(is_leaf_flag, ''Y'', '''|| l_days_past_due_b7_det_url || ''',
504 '''|| l_days_past_due_b7_url || '''))) FII_AR_PD_BKT_AMT_DRILL_B7';
505 END IF;
506
507 l_url_sql := l_url_sql ||
508 ', DECODE(is_self_flag, ''Y'', NULL,
509 DECODE(is_leaf_flag, ''N'', '''|| l_customer_url || ''',
510 NULL)) FII_AR_CUSTOMER_DRILL ';
511
512 END IF;
513
514 ----------------------------------------------------------------
515 -- Find out the sort order column and construct the order clause
516 ----------------------------------------------------------------
517 IF (instr(fii_ar_util_pkg.g_order_by, ',') <> 0) THEN
518 -------------------------------------------------------------
519 -- This means no particular sort column is selected in the
520 -- report. Thus, sort on the default column in descending
521 -- order. NVL is added ot make sure NULL will appear last.
522 -------------------------------------------------------------
523 l_order_by := ' ORDER BY NVL(FII_AR_PASTDUE_REC_AMT, -999999999) DESC';
524
525 ELSIF (instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0) THEN
526 -------------------------------------------------------------
527 -- This means a particular sort column is chosen to be sorted
528 -- in descending order. Add NVL to that column so NULL will
529 -- appear last.
530 -------------------------------------------------------------
531 l_order_column := substr(fii_ar_util_pkg.g_order_by, 1,
532 instr(fii_ar_util_pkg.g_order_by, ' DESC'));
533 l_order_by := ' ORDER BY NVL('|| l_order_column || ', -999999999) DESC';
534 ELSE
535 -------------------------------------------------------------
536 -- This means user has asked for an ascending order sort.
537 -- We should use PMV's order by clause
538 -------------------------------------------------------------
539 l_order_by := ' &ORDER_BY_CLAUSE';
540
541 END IF;
542
543 --------------------------------------
544 -- Construct the bucket sql statements
545 --------------------------------------
546 i := 1;
547
548 IF (l_bucket_ct >= 1) THEN
549 l_bucket_graph_sql :=
550 'sum(past_due_bucket_1_amount) FII_AR_PASTDUE_BKT_AMT_G_B1';
551 l_bucket_sql :=
552 'sum(past_due_bucket_1_amount) FII_AR_PASTDUE_BUCKET_AMT_B1';
553 l_dispute_bkt_graph_sql := ' 0 FII_AR_PASTDUE_BKT_AMT_G_B1';
554 l_dispute_bkt_sql := ' 0 FII_AR_PASTDUE_BUCKET_AMT_B1';
555 END IF;
556
557 FOR i IN 2..l_bucket_ct LOOP
558 IF (i > l_max_bucket_ct) THEN
559 l_bucket_graph_sql := l_bucket_graph_sql ||
560 ', NULL FII_AR_PASTDUE_BKT_AMT_G_B'||i;
561 l_bucket_sql := l_bucket_sql ||
562 ', NULL FII_AR_PASTDUE_BUCKET_AMT_B'||i;
563 l_dispute_bkt_graph_sql := l_dispute_bkt_graph_sql ||
564 ', NULL FII_AR_PASTDUE_BKT_AMT_G_B'||i;
565 l_dispute_bkt_sql := l_dispute_bkt_sql ||
566 ', NULL FII_AR_PASTDUE_BUCKET_AMT_B'||i;
567 ELSE
568 l_bucket_graph_sql := l_bucket_graph_sql || ', sum(past_due_bucket_' || i ||
569 '_amount) FII_AR_PASTDUE_BKT_AMT_G_B'||i;
570 l_bucket_sql := l_bucket_sql || ', sum(past_due_bucket_' || i ||
571 '_amount) FII_AR_PASTDUE_BUCKET_AMT_B'||i;
572 l_dispute_bkt_graph_sql := l_dispute_bkt_graph_sql ||
573 ', 0 FII_AR_PASTDUE_BKT_AMT_G_B'||i;
574 l_dispute_bkt_sql := l_dispute_bkt_sql ||
575 ', 0 FII_AR_PASTDUE_BUCKET_AMT_B'||i;
576 END IF;
577 END LOOP;
578
579 -----------------------------------------
580 -- Construct the conditional where clause
581 -----------------------------------------
582 -- Only add the join on collector_id if we have a specific collector selected
583 -- or view by = Collector
584 IF ((l_viewby = 'FII_COLLECTOR+FII_COLLECTOR') OR
585 (l_collector_id <> '-111')) THEN
586 l_where_clause := l_where_clause ||
587 'AND f.collector_id = v.collector_id ';
588 END IF;
589
590 -- Only add the join on cust_acct_id if we have a specific customer acct
591 -- selected or when view by = Customer Account
592 IF ((l_viewby = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') OR
593 (l_cust_account_id <> '-111')) THEN
594 l_where_clause := l_where_clause ||
595 'AND f.cust_account_id = v.cust_account_id ';
596 END IF;
597
598 -- Only add the join on parent_party_id when view by = Customer
599 IF (l_viewby = 'CUSTOMER+FII_CUSTOMERS') THEN
600 l_where_clause := l_where_clause ||
601 'AND f.parent_party_id = v.parent_party_id ';
602 END IF;
603
604 -- Only add the join on party_id when we have a specific customer
605 -- selected or when view by = Customer
606 IF (l_cust_id <> '-111' OR l_viewby = 'CUSTOMER+FII_CUSTOMERS') THEN
607 l_where_clause := l_where_clause ||
608 'AND f.party_id = v.party_id ';
609 END IF;
610
611 -------------------------------
612 -- Construct the sql statements
613 -------------------------------
614 l_sqlstmt :=
615 'SELECT
616 viewby, viewbyid, ';
617
618 i := 1;
619 IF (l_bucket_ct >= 1) THEN
620 l_sqlstmt := l_sqlstmt ||
621 'sum(FII_AR_PASTDUE_BKT_AMT_G_B1) FII_AR_PASTDUE_BKT_AMT_G_B1';
622 END IF;
623
624 FOR i IN 2..l_bucket_ct LOOP
625 IF (i > l_max_bucket_ct) THEN
626 l_sqlstmt := l_sqlstmt ||
627 ', NULL FII_AR_PASTDUE_BKT_AMT_G_B' || i;
628 ELSE
629 l_sqlstmt := l_sqlstmt ||
630 ', sum(FII_AR_PASTDUE_BKT_AMT_G_B'||i||') FII_AR_PASTDUE_BKT_AMT_G_B'||i;
631 END IF;
632 END LOOP;
633
634 -- <arcdixit> Bug 5005028. Correct column sources for open receivables and weighted ddso
635 l_sqlstmt := l_sqlstmt ||',
636 ROUND((SUM(FII_AR_PASTDUE_REC_AMT) * to_number(to_char(&BIS_CURRENT_ASOF_DATE , ''J''))
637 -
638 SUM(FII_AR_WEIGHTED_DDSO_NUM))/NULLIF(SUM(FII_AR_PASTDUE_REC_AMT),0)) FII_AR_WEIGHTED_DDSO_G,
639 (SUM(FII_AR_PASTDUE_REC_AMT) + SUM(FII_AR_OPEN_REC_AMT)) FII_AR_OPEN_REC_AMT,
640 sum(FII_AR_PASTDUE_REC_AMT) FII_AR_PASTDUE_REC_AMT,
641 sum(FII_AR_PASTDUE_REC_CT) FII_AR_PASTDUE_REC_CT,
642 (SUM(FII_AR_PASTDUE_REC_AMT) * to_number(to_char(&BIS_CURRENT_ASOF_DATE , ''J''))
643 -
644 SUM(FII_AR_WEIGHTED_DDSO_NUM))/NULLIF(SUM(FII_AR_PASTDUE_REC_AMT),0) FII_AR_WEIGHTED_DDSO,
645 sum(FII_AR_DISPUTE_AMT) FII_AR_DISPUTE_AMT,
646 (sum(FII_AR_DISPUTE_AMT) / NULLIF(sum(FII_AR_PASTDUE_REC_AMT), 0) ) * 100
647 FII_AR_DISPUTE_PERCENT_TOTAL,
648 sum(FII_AR_DISPUTE_CT) FII_AR_DISPUTE_CT,';
649
650 i := 1;
651 IF (l_bucket_ct >= 1) THEN
652 l_sqlstmt := l_sqlstmt ||
653 'sum(FII_AR_PASTDUE_BUCKET_AMT_B1) FII_AR_PASTDUE_BUCKET_AMT_B1';
654 END IF;
655
656 FOR i IN 2..l_bucket_ct LOOP
657 IF (i > l_max_bucket_ct) THEN
658 l_sqlstmt := l_sqlstmt ||
659 ', NULL FII_AR_PASTDUE_BUCKET_AMT_B' ||i;
660 ELSE
661 l_sqlstmt := l_sqlstmt ||
662 ', sum(FII_AR_PASTDUE_BUCKET_AMT_B'||i||') FII_AR_PASTDUE_BUCKET_AMT_B'||i;
663 END IF;
664 END LOOP;
665
666 l_sqlstmt := l_sqlstmt || ',
667 (SUM(SUM(FII_AR_PASTDUE_REC_AMT)) over() + SUM(SUM(FII_AR_OPEN_REC_AMT)) over()) FII_AR_GT_OPEN_REC_AMT,
668 sum(sum(FII_AR_PASTDUE_REC_AMT)) over() FII_AR_GT_PASTDUE_REC_AMT,
669 sum(sum(FII_AR_PASTDUE_REC_CT)) over() FII_AR_GT_PASTDUE_REC_CT,
670 (SUM(SUM(FII_AR_PASTDUE_REC_AMT)) over() * to_number(to_char(&BIS_CURRENT_ASOF_DATE , ''J''))
671 -
672 SUM(SUM(FII_AR_WEIGHTED_DDSO_NUM)) over())/NULLIF(SUM(SUM(FII_AR_PASTDUE_REC_AMT)) OVER(),0) FII_AR_GT_WEIGHTED_DDSO,
673 sum(sum(FII_AR_DISPUTE_AMT)) over() FII_AR_GT_DISPUTE_AMT,
674 (sum(sum(FII_AR_DISPUTE_AMT))over() / NULLIF(sum(sum(FII_AR_PASTDUE_REC_AMT))over(), 0) ) * 100
675 FII_AR_GT_DISPUTE_PCT_TOTAL,
676 sum(sum(FII_AR_DISPUTE_CT)) over() FII_AR_GT_DISPUTE_CT, ';
677
678 i := 1;
679 IF (l_bucket_ct >= 1) THEN
680 l_sqlstmt := l_sqlstmt ||
681 'sum(sum(FII_AR_PASTDUE_BUCKET_AMT_B1)) over() FII_AR_GT_PASTDUE_BKT_AMT_B1';
682 END IF;
683
684 FOR i IN 2..l_bucket_ct LOOP
685 IF (i > l_max_bucket_ct) THEN
686 l_sqlstmt := l_sqlstmt ||
687 ', NULL FII_AR_GT_PASTDUE_BUCKET_AMT_B'||i;
688 ELSE
689 l_sqlstmt := l_sqlstmt ||
690 ', sum(sum(FII_AR_PASTDUE_BUCKET_AMT_B'||i||')) over() FII_AR_GT_PASTDUE_BKT_AMT_B'||i;
691 END IF;
692 END LOOP;
693
694 -- Attach the drilldown URL sql to the sql statement
695 l_sqlstmt := l_sqlstmt || l_url_sql;
696
697 FOR i IN 8..l_bucket_ct LOOP
698 l_sqlstmt := l_sqlstmt
699 || ', NULL FII_AR_PD_BKT_AMT_DRILL_B' || i;
700 END LOOP;
701
702 l_sqlstmt := l_sqlstmt || ' FROM (
703 SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
704 v.viewby VIEWBY,
705 v.viewby_code VIEWBYID,' ||
706 l_bucket_graph_sql || ',
707 sum(f.wtd_ddso_due_num) FII_AR_WEIGHTED_DDSO_G,
708 sum(f.current_open_amount) FII_AR_OPEN_REC_AMT,
709 sum(f.past_due_open_amount) FII_AR_PASTDUE_REC_AMT,
710 sum(f.past_due_count) FII_AR_PASTDUE_REC_CT,
711 sum(f.wtd_ddso_due_num) FII_AR_WEIGHTED_DDSO_NUM,
712 NULL FII_AR_DISPUTE_AMT,
713 NULL FII_AR_DISPUTE_CT, '||
714 l_bucket_sql || l_cust_clause || '
715 FROM fii_ar_net_rec'||l_cust_suffix||'_mv'||l_curr_suffix||' f,
716 ( SELECT /*+ no_merge '||l_gt_hint|| ' */ *
717 FROM fii_time_structures cal, '||
718 fii_ar_util_pkg.get_from_statement ||
719 ' gt WHERE cal.report_date = :ASOF_DATE
720 AND bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
721 AND '|| fii_ar_util_pkg.get_where_statement || ') v
722 WHERE f.time_id = v.time_id
723 AND f.period_type_id = v.period_type_id
724 AND f.org_id = v.org_id
725 AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_where_clause ||
726 ' GROUP BY v.viewby, v.viewby_code ' || l_cust_clause;
727
728 -------------------------------------
729 -- Sql for the dispute amount section
730 -------------------------------------
731 l_sqlstmt :=
732 l_sqlstmt || ' union all '||
733 'SELECT
734 v.viewby VIEWBY,
735 v.viewby_code VIEWBYID, '||
736 l_dispute_bkt_graph_sql || ',
737 NULL FII_AR_WEIGHTED_DDSO_G,
738 NULL FII_AR_OPEN_REC_AMT,
739 NULL FII_AR_PASTDUE_REC_AMT,
740 NULL FII_AR_PASTDUE_REC_CT,
741 NULL FII_AR_WEIGHTED_DDSO_NUM,
742 sum(past_due_dispute_amount) FII_AR_DISPUTE_AMT,
743 sum(past_due_dispute_count) FII_AR_DISPUTE_CT, '||
744 l_dispute_bkt_sql || l_cust_clause || '
745 FROM fii_ar_disputes'||l_cust_suffix||'_mv'||l_curr_suffix||' f,
746 ( SELECT /*+ no_merge '||l_gt_hint|| ' */ *
747 FROM fii_time_structures cal, '||
748 fii_ar_util_pkg.get_from_statement ||
749 ' gt WHERE cal.report_date = :ASOF_DATE
750 AND bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
751 AND '|| fii_ar_util_pkg.get_where_statement || ') v
752 WHERE f.time_id = v.time_id
753 AND f.period_type_id = v.period_type_id
754 AND f.org_id = v.org_id ' || l_where_clause ||
755 ' GROUP BY v.viewby, v.viewby_code ' || l_cust_clause || ') inline_view
756 GROUP BY viewby, viewbyid ' || l_cust_clause2
757 ||l_order_by;
758
759 -- Bind variables so that no literal will be used in the pmv report
760 fii_ar_util_pkg.bind_variable
761 (p_sqlstmt => l_sqlstmt,
762 p_page_parameter_tbl => p_page_parameter_tbl,
763 p_sql_output => p_pastdue_rec_aging_sql,
764 p_bind_output_table => p_pastdue_rec_aging_output);
765
766 END get_pastdue_rec_aging;
767
768 -------------------------------------------------------------------------------
769 -- This procedure is called by the Receivables Aging Summary report
770 -------------------------------------------------------------------------------
771 PROCEDURE get_rec_aging
772 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
773 p_pastdue_rec_aging_sql OUT NOCOPY VARCHAR2,
774 p_pastdue_rec_aging_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
775 IS
776 l_sqlstmt VARCHAR2(30000);
777 l_where_clause VARCHAR2(2000);
778 l_return_status VARCHAR2(10);
779 l_curr_label VARCHAR2(240);
780 l_bis_bucket_rec BIS_BUCKET_PUB.bis_bucket_rec_type;
781 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
782 l_bucket_ct NUMBER;
783 l_as_of_date DATE;
784 l_cust_suffix VARCHAR2(6);
785 l_curr_suffix VARCHAR2(4);
786 l_itd_bitand NUMBER;
787 l_collector_id VARCHAR2(30);
788 l_cust_id VARCHAR2(500);
789 l_max_bucket_ct NUMBER := 7; -- Maximum number of bucket ranges
790 l_last_bucket_num NUMBER;
791
792 l_bkt_url VARCHAR2(500) := NULL;
793 l_curr_rec_url VARCHAR2(500) := NULL;
794
795 TYPE DataRec IS RECORD (
796 l_label_0 VARCHAR2(240),
797 l_label_1 VARCHAR2(80),
798 l_label_2 VARCHAR2(80),
799 l_label_3 VARCHAR2(80),
800 l_label_4 VARCHAR2(80),
801 l_label_5 VARCHAR2(80),
802 l_label_6 VARCHAR2(80),
803 l_label_7 VARCHAR2(80),
804 l_curr_rec_amt NUMBER,
805 l_pdue_bkt1_amt NUMBER,
806 l_pdue_bkt2_amt NUMBER,
807 l_pdue_bkt3_amt NUMBER,
808 l_pdue_bkt4_amt NUMBER,
809 l_pdue_bkt5_amt NUMBER,
810 l_pdue_bkt6_amt NUMBER,
811 l_pdue_bkt7_amt NUMBER);
812
813 TYPE DataRecTab is table of DataRec;
814 TYPE num_type IS TABLE OF NUMBER;
815 TYPE val_type IS TABLE OF VARCHAR2(240);
816
817 TYPE bucket_rec IS RECORD (
818 l_ord_seq num_type,
819 l_label val_type,
820 l_amount num_type );
821
822 l_data_rec DataRecTab;
823 l_bucket_rec BUCKET_REC;
824
825 CURSOR rec_aging_cursor IS
826 SELECT 7, l_data_rec(1).l_label_7, l_data_rec(1).l_pdue_bkt7_amt
827 FROM dual
828 UNION
829 SELECT 6, l_data_rec(1).l_label_6, l_data_rec(1).l_pdue_bkt6_amt
830 FROM dual
831 UNION
832 SELECT 5, l_data_rec(1).l_label_5, l_data_rec(1).l_pdue_bkt5_amt
833 FROM dual
834 UNION
835 SELECT 4, l_data_rec(1).l_label_4, l_data_rec(1).l_pdue_bkt4_amt
836 FROM dual
837 UNION
838 SELECT 3, l_data_rec(1).l_label_3, l_data_rec(1).l_pdue_bkt3_amt
839 FROM dual
840 UNION
841 SELECT 2, l_data_rec(1).l_label_2, l_data_rec(1).l_pdue_bkt2_amt
842 FROM dual
843 UNION
844 SELECT 1, l_data_rec(1).l_label_1, l_data_rec(1).l_pdue_bkt1_amt
845 FROM dual
846 UNION
847 SELECT 0, l_data_rec(1).l_label_0, l_data_rec(1).l_curr_rec_amt
848 FROM dual;
849
850 l_fii_user_id NUMBER(15);
851 l_fii_login_id NUMBER(15);
852
853
854 BEGIN
855 -- Reset all the global variables to NULL or to the default value
856 fii_ar_util_pkg.reset_globals;
857
858 -- Get the parameters and set the global variables
859 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
860
861 -- Populate global temp table based on the parameters chosen
862 fii_ar_util_pkg.populate_summary_gt_tables;
863
864 -- Retrieve values for global variables
865 l_as_of_date := fii_ar_util_pkg.g_as_of_date;
866 l_cust_suffix := fii_ar_util_pkg.g_cust_suffix;
867 l_curr_suffix := fii_ar_util_pkg.g_curr_suffix;
868 l_itd_bitand := fii_ar_util_pkg.g_bitand_inc_todate;
869 l_collector_id := fii_ar_util_pkg.g_collector_id;
870 l_cust_id := fii_ar_util_pkg.g_party_id;
871
872 l_fii_user_id := FND_GLOBAL.User_Id;
873 l_fii_login_id := FND_GLOBAL.Login_Id;
874
875 -- Find out the number of bucket ranges customized for this report
876 SELECT sum(decode(bbc.range1_low, null, 0, 1) +
877 decode(bbc.range2_low, null, 0, 1) +
878 decode(bbc.range3_low, null, 0, 1) +
879 decode(bbc.range4_low, null, 0, 1) +
880 decode(bbc.range5_low, null, 0, 1) +
881 decode(bbc.range6_low, null, 0, 1) +
882 decode(bbc.range7_low, null, 0, 1) +
883 decode(bbc.range8_low, null, 0, 1) +
884 decode(bbc.range9_low, null, 0, 1) +
885 decode(bbc.range10_low, null, 0, 1)) bucket_count
886 INTO l_bucket_ct
887 FROM bis_bucket_customizations bbc,
888 bis_bucket bb
889 WHERE bb.short_name = 'FII_DBI_PAST_DUE_REC_BUCKET'
890 AND bbc.bucket_id = bb.bucket_id;
891
892 -------------------------------------------------------------------------
893 -- Construct the drilldown URLs:
894 -- 1. Bucket amounts should drill to Past Due Receivables Aging Summary
895 -- view by Customer
896 -- 2. Current Receivables amount should drill to Current Receivables
897 -- Summary view by Customer
898 -------------------------------------------------------------------------
899 l_bkt_url := 'pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMERS&pParamIds=Y';
900
901 l_curr_rec_url := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMERS&pParamIds=Y';
902
903 -----------------------------------------
904 -- Construct the conditional where clause
905 -----------------------------------------
906 -- Only add the join on collector_id if we have a specific collector selected
907 IF (l_collector_id <> '-111') THEN
908 l_where_clause := l_where_clause ||
909 'AND f.collector_id = v.collector_id ';
910 END IF;
911
912 -- Only add the join on party_id when we have a specific customer
913 -- selected
914 IF (l_cust_id <> '-111') THEN
915 l_where_clause := l_where_clause ||
916 'AND f.party_id = v.party_id ';
917 END IF;
918
919 -- Clean up temp table
920 delete from FII_AR_REC_AGING_SUM_GT;
921
922 -- Retrieve the bucket labels for this report and insert into temp table
923 BIS_BUCKET_PUB.retrieve_bis_bucket (
924 p_short_name => 'FII_DBI_PAST_DUE_REC_BUCKET',
925 x_bis_bucket_rec => l_bis_bucket_rec,
926 x_return_status => l_return_status,
927 x_error_tbl => l_error_tbl
928 );
929
930 -- Retrive the label for current receivables
931 l_curr_label := FND_MESSAGE.get_string('FII', 'FII_AR_CURR_REC');
932
933 --------------------------------------------------------------------
934 -- Find out receivables aging amounts and current receivables amount
935 -- and store the info into pl/sql table l_data_rec
936 --------------------------------------------------------------------
937 l_sqlstmt :=
938 'SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/ ''' ||
939 l_curr_label || ''' ,''' ||
940 l_bis_bucket_rec.range1_name || ''' ,''' ||
941 l_bis_bucket_rec.range2_name || ''' ,''' ||
942 l_bis_bucket_rec.range3_name || ''' ,''' ||
943 l_bis_bucket_rec.range4_name || ''' ,''' ||
944 l_bis_bucket_rec.range5_name || ''' ,''' ||
945 l_bis_bucket_rec.range6_name || ''' ,''' ||
946 l_bis_bucket_rec.range7_name || ''' ,
947 SUM(f.current_bucket_1_amount) + SUM(f.current_bucket_2_amount)
948 + SUM(f.current_bucket_3_amount),
949 SUM(f.past_due_bucket_1_amount),
950 SUM(f.past_due_bucket_2_amount),
951 SUM(f.past_due_bucket_3_amount),
952 SUM(f.past_due_bucket_4_amount),
953 SUM(f.past_due_bucket_5_amount),
954 SUM(f.past_due_bucket_6_amount),
955 SUM(f.past_due_bucket_7_amount)
956 FROM fii_ar_net_rec'||l_cust_suffix||'_mv'||l_curr_suffix||' f,
957 ( SELECT /*+ no_merge leading(gt) cardinality(gt 1)*/ *
958 FROM fii_time_structures cal, '||
959 fii_ar_util_pkg.get_from_statement ||
960 ' gt WHERE cal.report_date = '''||l_as_of_date||
961 ''' AND bitand(cal.record_type_id, '||l_itd_bitand||') = 512
962 AND '|| fii_ar_util_pkg.get_where_statement || ') v
963 WHERE f.time_id = v.time_id
964 AND f.period_type_id = v.period_type_id
965 AND f.org_id = v.org_id
966 AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_where_clause;
967
968 EXECUTE IMMEDIATE l_sqlstmt BULK COLLECT INTO l_data_rec;
969
970 -----------------------------------------------------------------
971 -- Use the rec_aging_cursor to turn our row in l_dat_rec
972 -- into columns and insert the appropriate values into
973 -- FII_AR_REC_AGING_SUM_GT
974 -----------------------------------------------------------------
975 OPEN rec_aging_cursor;
976 FETCH rec_aging_cursor BULK COLLECT INTO l_bucket_rec.l_ord_seq,
977 l_bucket_rec.l_label,
978 l_bucket_rec.l_amount;
979 CLOSE rec_aging_cursor;
980
981 -- We should only insert data for bucket ranges set up and the
982 -- Current Receivables into FII_AR_REC_AGING_SUM_GT. If bucket ranges
983 -- exceed the maximum allowed for this report (7 ranges) we will
984 -- only display up to the 7th bucket plus the Current Receivables data
985 IF (l_bucket_ct > l_max_bucket_ct) THEN
986 l_last_bucket_num := l_max_bucket_ct + 1;
987 ELSE
988 l_last_bucket_num := l_bucket_ct + 1;
989 END IF;
990
991 FORALL i IN l_bucket_rec.l_ord_seq.FIRST .. l_last_bucket_num
992 INSERT INTO FII_AR_REC_AGING_SUM_GT
993 ( ord_seq, label, amount, creation_date, created_by, last_update_date, last_updated_by, last_update_login )
994 VALUES
995 ( l_bucket_rec.l_ord_seq(i),
996 l_bucket_rec.l_label(i),
997 l_bucket_rec.l_amount(i),
998 sysdate,
999 l_fii_user_id,
1000 sysdate,
1001 l_fii_user_id,
1002 l_fii_login_id);
1003
1004 -- Build sql statement
1005 l_sqlstmt := 'SELECT
1006 amount FII_AR_REC_AMT_G,
1007 label FII_AR_REC_LABEL,
1008 amount FII_AR_REC_AMT,
1009 DECODE( amount, 0, NULL,
1010 NULL, NULL,
1011 DECODE(ord_seq,
1012 0, '''||l_curr_rec_url ||''',
1013 '''||l_bkt_url ||''')) FII_AR_REC_AMT_DRILL
1014 FROM FII_AR_REC_AGING_SUM_GT
1015 ORDER BY ord_seq desc';
1016
1017 -- Bind variables so that no literal will be used in the pmv report
1018 fii_ar_util_pkg.bind_variable
1019 (p_sqlstmt => l_sqlstmt,
1020 p_page_parameter_tbl => p_page_parameter_tbl,
1021 p_sql_output => p_pastdue_rec_aging_sql,
1022 p_bind_output_table => p_pastdue_rec_aging_output);
1023
1024 END get_rec_aging;
1025
1026
1027 END FII_AR_REC_AGING_PKG;
1028