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