[Home] [Help]
PACKAGE BODY: APPS.AR_AGING_BUCKETS_PKG
Source
1 PACKAGE BODY AR_AGING_BUCKETS_PKG AS
2 /* $Header: ARAGBKTB.pls 120.9.12020000.2 2012/07/26 03:59:48 jatian ship $ */
3
4
5 PG_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
6 PG_PARALLEL VARCHAR2(1) := NVL(FND_PROFILE.value('AR_USE_PARALLEL_HINT'), 'N');
7 MAX_ARRAY_SIZE BINARY_INTEGER := 1000 ;
8
9
10 --package constants
11 AR_AGING_CTGRY_INVOICE VARCHAR2(20) := 'INVOICE';
12 AR_AGING_CTGRY_RECEIPT VARCHAR2(20) := 'RECEIPT';
13 AR_AGING_CTGRY_RISK VARCHAR2(20) := 'RISK';
14 AR_AGING_CTGRY_BR VARCHAR2(20) := 'BILLS_RECEIVABLE';
15
16 pg_org_where_ps VARCHAR2(2000) := NULL;
17 pg_org_where_gld VARCHAR2(2000) := NULL;
18 pg_org_where_ct VARCHAR2(2000) := NULL;
19 pg_org_where_sales VARCHAR2(2000) := NULL;
20 pg_org_where_ct2 VARCHAR2(2000) := NULL;
21 pg_org_where_adj VARCHAR2(2000) := NULL;
22 pg_org_where_app VARCHAR2(2000) := NULL;
23 pg_org_where_crh VARCHAR2(2000) := NULL;
24 pg_org_where_ra VARCHAR2(2000) := NULL;
25 pg_org_where_cr VARCHAR2(2000) := NULL;
26 pg_org_where_sys_param VARCHAR2(2000) := NULL;
27 pg_bal_seg_where VARCHAR2(2000) := NULL;
28
29
30 pg_rep_type VARCHAR2(30);
31 pg_reporting_level VARCHAR2(30);
32 pg_reporting_entity_id NUMBER;
33 pg_coaid NUMBER;
34 pg_in_bal_segment_low VARCHAR2(30);
35 pg_in_bal_segment_high VARCHAR2(30);
36 pg_in_as_of_date_low DATE;
37 pg_in_summary_option_low VARCHAR2(80);
38 pg_in_format_option_low VARCHAR2(80);
39 pg_in_bucket_type_low VARCHAR2(30);
40 pg_credit_option VARCHAR2(80);
41 pg_risk_option VARCHAR2(80);
42 pg_in_currency VARCHAR2(20);
43 pg_in_customer_name_low VARCHAR2(240);
44 pg_in_customer_name_high VARCHAR2(240);
45 pg_in_customer_num_low VARCHAR2(200);
46 pg_in_customer_num_high VARCHAR2(200);
47 pg_in_amt_due_low VARCHAR2(200);
48 pg_in_amt_due_high VARCHAR2(200);
49 pg_in_invoice_type_low VARCHAR2(500);
50 pg_in_invoice_type_high VARCHAR2(500);
51 pg_in_collector_low VARCHAR2(30);
52 pg_in_collector_high VARCHAR2(30);
53 pg_retain_staging_flag VARCHAR(1);
54 pg_cons_profile_value VARCHAR2(1);
55 pg_accounting_method VARCHAR2(30);
56
57
58 pg_accounting_flexfield VARCHAR2(2000);
59
60 pg_acct_flex_bal_seg VARCHAR2(2000);
61
62 pg_report_name VARCHAR2(2000);
63 pg_segment_label VARCHAR2(2000);
64 pg_bal_label VARCHAR2(2000);
65 pg_label_1 VARCHAR2(2000);
66 pg_sort_on VARCHAR2(2000);
67 pg_grand_total VARCHAR2(2000);
68 pg_label VARCHAR2(2000);
69 pg_param_org_id NUMBER;
70 pg_company_name VARCHAR2(2000);
71 pg_functional_currency VARCHAR2(2000);
72 pg_func_curr_precision NUMBER;
73 pg_convert_flag VARCHAR2(2000);
74 pg_set_of_books_id NUMBER;
75 pg_in_sortoption VARCHAR2(2000);
76 pg_request_id NUMBER;
77 pg_parent_request_id NUMBER := -1;
78 pg_worker_id NUMBER := 1;
79 pg_worker_count NUMBER := 1;
80 pg_short_unid_phrase VARCHAR2(2000);
81 pg_payment_meaning VARCHAR2(2000);
82 pg_risk_meaning VARCHAR2(2000);
83
84 pg_reporting_entity_name VARCHAR2(2000);
85 pg_reporting_level_name VARCHAR2(2000);
86
87 pg_temp_site_use_id NUMBER;
88 pg_temp_contact_phone VARCHAR2(360);
89 pg_temp_contacts VARCHAR2(360);
90 pg_temp_contact_name HZ_PARTIES.PARTY_NAME%TYPE;
91
92 p_not_implemented_exp EXCEPTION;
93
94 TYPE aging_mfar_tab IS TABLE OF ar_aging_mfar_extract%ROWTYPE INDEX BY BINARY_INTEGER;
95
96 TYPE req_status_type IS RECORD (
97 request_id NUMBER(15),
98 dev_phase VARCHAR2(255),
99 dev_status VARCHAR2(255),
100 message VARCHAR2(2000),
101 phase VARCHAR2(255),
102 status VARCHAR2(255));
103 l_org_id NUMBER;
104
105 TYPE req_status_tab_type IS TABLE OF req_status_type INDEX BY BINARY_INTEGER;
106
107 pg_req_status_tab req_status_tab_type;
108
109 /*========================================================================+
110 Function which replaces the special characters in the strings to form
111 a valid XML string
112 +========================================================================*/
113 FUNCTION format_string(p_string varchar2) return varchar2 IS
114
115 l_string varchar2(2000);
116 BEGIN
117
118 l_string := replace(p_string,'&','&'||'amp;');
119 l_string := replace(l_string,'<','&'||'lt;');
120 l_string := replace(l_string,'>','&'||'gt;');
121
122 RETURN l_string;
123
124 END format_string;
125
126 /*==========================================================================
127 | PRIVATE FUNCTION get_reporting_entity_id |
128 | |
129 | DESCRIPTION |
130 | |
131 | |
132 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
133 | |
134 | PARAMETERS |
135 | |
136 | KNOWN ISSUES |
137 | |
138 | NOTES |
139 | |
140 | MODIFICATION HISTORY |
141 | Date Author Description of Changes |
142 | 10-JUL-2009 Naveen Prodduturi Created |
143 *==========================================================================*/
144 FUNCTION get_reporting_entity_id RETURN NUMBER is
145 BEGIN
146 return pg_reporting_entity_id;
147 END get_reporting_entity_id;
148
149
150
151
152 PROCEDURE print_clob( p_clob IN CLOB) IS
153 l_offset NUMBER DEFAULT 1;
154 BEGIN
155 BEGIN
156 LOOP
157 EXIT WHEN l_offset > dbms_lob.getlength(p_clob);
158 arp_standard.debug( dbms_lob.substr( p_clob, 255, l_offset ) );
159 l_offset := l_offset + 255;
160 END LOOP;
161
162 EXCEPTION
163 WHEN OTHERS THEN
164 IF PG_DEBUG in ('Y', 'C') THEN
165 arp_standard.debug( ' Exception '||SQLERRM);
166 arp_standard.debug( ' Exception print_clob()');
167 END IF;
168 END;
169 END print_clob;
170
171
172
173 /*==========================================================================
174 | PRIVATE FUNCTION get_report_heading |
175 | |
176 | DESCRIPTION |
177 | Returns bucket related headers to be used in report display |
178 | |
179 | |
180 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
181 | |
182 | PARAMETERS |
183 | NONE |
184 | |
185 | KNOWN ISSUES |
186 | |
187 | NOTES |
188 | |
189 | MODIFICATION HISTORY |
190 | Date Author Description of Changes |
191 | 10-JUL-2009 Naveen Prodduturi Created |
192 *==========================================================================*/
193 FUNCTION get_report_heading RETURN VARCHAR2 IS
194 CURSOR buc_info_cur IS
195 select report_heading1,
196 report_heading2
197 from ar_aging_bucket_lines lines,
198 ar_aging_buckets buckets
199 where lines.aging_bucket_id = buckets.aging_bucket_id
200 and UPPER(buckets.bucket_name) = UPPER(pg_in_bucket_type_low)
201 and NVL(buckets.status,'A') = 'A'
202 order by lines.bucket_sequence_num;
203
204 l_report_heading VARCHAR2(32767) := '';
205 i NUMBER(1) := 0;
206 l_new_line VARCHAR2(1) := '
207 ';
208
209 BEGIN
210 IF PG_DEBUG in ('Y', 'C') THEN
211 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.get_report_heading()+');
212 END IF;
213
214 FOR buc_rec IN buc_info_cur LOOP
218 l_report_heading := format_string(l_report_heading) ||l_new_line||
215 IF buc_rec.report_heading2 IS NULL THEN
216 l_report_heading := l_report_heading ||l_new_line||
217 '<HEAD_TOP_'||i||'></HEAD_TOP_'||i||'>';
219 '<HEAD_BOT_'||i||'>'||format_string(buc_rec.report_heading1)||'</HEAD_BOT_'||i||'>';
220 ELSE
221 l_report_heading := l_report_heading ||l_new_line||
222 '<HEAD_TOP_'||i||'>'||format_string(buc_rec.report_heading1)||'</HEAD_TOP_'||i||'>';
223 l_report_heading := l_report_heading ||l_new_line||
224 '<HEAD_BOT_'||i||'>'||format_string(buc_rec.report_heading2)||'</HEAD_BOT_'||i||'>';
225 END IF;
226
227 IF PG_DEBUG in ('Y', 'C') THEN
228 arp_standard.debug( 'report_heading1: '||buc_rec.report_heading1);
229 arp_standard.debug( 'report_heading2: '||buc_rec.report_heading2);
230 END IF;
231
232 i := i + 1;
233
234 END LOOP;
235
236 IF PG_DEBUG in ('Y', 'C') THEN
237 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.get_report_heading()-');
238 END IF;
239
240 RETURN l_report_heading;
241
242 EXCEPTION
243 WHEN OTHERS THEN
244 IF PG_DEBUG in ('Y', 'C') THEN
245 arp_standard.debug( ' Exception '||SQLERRM);
246 arp_standard.debug( ' Exception AR_AGING_BUCKETS_PKG.get_report_heading()');
247 END IF;
248 RAISE;
249 END get_report_heading;
250
251
252
253
254 /*==========================================================================
255 | PRIVATE FUNCTION get_parent_request_id |
256 | |
257 | DESCRIPTION |
258 | |
259 | |
260 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
261 | |
262 | PARAMETERS |
263 | p_request_id |
264 | |
265 | KNOWN ISSUES |
266 | |
267 | NOTES |
268 | |
269 | MODIFICATION HISTORY |
270 | Date Author Description of Changes |
271 | 10-JUL-2009 Naveen Prodduturi Created |
272 *==========================================================================*/
273 FUNCTION get_parent_request_id(p_request_id NUMBER) RETURN NUMBER IS
274 l_request_id NUMBER;
275
276 BEGIN
277 IF PG_DEBUG in ('Y', 'C') THEN
278 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.get_parent_request_id()+');
279 arp_standard.debug( 'p_request_id :'||p_request_id);
280 END IF;
281
282 SELECT parent_request_id
283 INTO l_request_id
284 FROM fnd_concurrent_requests child
285 WHERE child.request_id = p_request_id;
286
287 IF PG_DEBUG in ('Y', 'C') THEN
288 arp_standard.debug( 'Return value :'||l_request_id);
289 END IF;
290
291 RETURN l_request_id;
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 IF PG_DEBUG in ('Y', 'C') THEN
296 arp_standard.debug( ' Exception '||SQLERRM);
297 arp_standard.debug( ' Exception AR_AGING_BUCKETS_PKG.get_parent_request_id()');
298 END IF;
299 RAISE;
300 END get_parent_request_id;
301
302
303
304
305 /*==========================================================================
306 | PRIVATE FUNCTION get_contact_information |
307 | |
308 | DESCRIPTION |
309 | Returns contact information associated to given site,return values |
310 | also depends on what sort of information is requested |
311 | |
312 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
313 | |
314 | PARAMETERS |
315 | p_site_use_id |
316 | p_info_type -possible values are NAME and PHONE |
317 | KNOWN ISSUES |
318 | |
319 | NOTES |
320 | |
321 | MODIFICATION HISTORY |
322 | Date Author Description of Changes |
323 | 10-JUL-2009 Naveen Prodduturi Created |
324 *==========================================================================*/
325 FUNCTION get_contact_information( p_site_use_id NUMBER,
326 p_info_type VARCHAR2) RETURN VARCHAR2 IS
327
328 BEGIN
329 IF PG_DEBUG in ('Y', 'C') THEN
330 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.get_contact_information()+');
331 arp_standard.debug( 'p_site_use_id :'||p_site_use_id);
332 arp_standard.debug( 'p_info_type :'||p_info_type);
336 IF p_site_use_id IS NULL THEN
333 arp_standard.debug( 'pg_temp_site_use_id :'||pg_temp_site_use_id);
334 END IF;
335
337 RETURN NULL;
338 END IF;
339
340 IF NVL(pg_temp_site_use_id,-99999) <> p_site_use_id THEN
341
342 pg_temp_site_use_id := p_site_use_id;
343 pg_temp_contact_name := NULL;
344 pg_temp_contact_phone := NULL;
345 pg_temp_contacts := NULL;
346
347 BEGIN
348 select RTRIM(RPAD(substrb(party.person_first_name,1,40), 1)) ||
349 decode( substrb(party.person_first_name,1,40),
350 NULL, NULL,
351 decode( substrb(party.person_last_name,1,50),
352 NULL, NULL,
353 '. ' )) ||
354 RTRIM(RPAD( substrb(party.person_last_name,1,50), 17)),
355 cont_point.phone_area_code ||
356 ' ' ||
357 RTRIM(RPAD( decode(cont_point.contact_point_type,
358 'TLX', cont_point.telex_number,
359 cont_point.phone_number) , 15)),
360 decode(substrb(party.person_first_name,1,40),
361 NULL, decode( substrb(party.person_last_name,1,50),
362 NULL, decode( cont_point.phone_area_code,
363 NULL, NULL,
364 'Y' ),
365 decode( decode(cont_point.contact_point_type,
366 'TLX', cont_point.telex_number,
367 cont_point.phone_number),
368 NULL, NULL,
369 'Y' ),
370 'Y' ),
371 'Y')
372 into pg_temp_contact_name,
373 pg_temp_contact_phone,
374 pg_temp_contacts
375 from hz_cust_account_roles acct_role,
376 hz_parties party,
377 hz_relationships rel,
378 hz_contact_points cont_point,
379 hz_cust_account_roles car,
380 hz_cust_site_uses site_uses
381 where site_uses.site_use_id = p_site_use_id
382 and site_uses.cust_acct_site_id = acct_role.cust_acct_site_id(+)
383 and acct_role.party_id = rel.party_id(+)
384 and rel.subject_table_name(+) = 'HZ_PARTIES'
385 and rel.object_table_name(+) = 'HZ_PARTIES'
386 and rel.directional_flag(+) = 'F'
387 and acct_role.role_type = 'CONTACT'
388 and rel.subject_id = party.party_id(+)
389 and acct_role.cust_account_role_id = car.cust_account_role_id(+)
390 and car.party_id = cont_point.owner_table_id(+)
391 and cont_point.owner_table_name(+) = 'HZ_PARTIES'
392 and NVL(cont_point.contact_point_type(+),'N') not in ('EDI','EMAIL','WEB')
393 and nvl( nvl(cont_point.phone_line_type(+),
394 cont_point.contact_point_type(+)), 'GEN') = 'GEN'
395 and nvl(acct_role.status,'A') = 'A'
396 and nvl(cont_point.status(+),'A') = 'A'
397 and rownum = 1;
398
399 EXCEPTION
400 WHEN NO_DATA_FOUND THEN
401 IF PG_DEBUG in ('Y', 'C') THEN
402 arp_standard.debug( 'Exception message '||SQLERRM);
403 END IF;
404 END;
405 END IF;
406
407 IF PG_DEBUG in ('Y', 'C') THEN
408 arp_standard.debug( 'pg_temp_contact_name '||pg_temp_contact_name);
409 arp_standard.debug( 'pg_temp_contact_phone '||pg_temp_contact_phone);
410 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.get_contact_information()-');
411 END IF;
412
413 IF p_info_type = 'NAME' THEN
414 RETURN pg_temp_contact_name;
415 ELSE
416 RETURN pg_temp_contact_phone;
417 END IF;
418
419 EXCEPTION
420 WHEN OTHERS THEN
421 IF PG_DEBUG in ('Y', 'C') THEN
422 arp_standard.debug( 'Exception message '||SQLERRM);
423 arp_standard.debug( 'Exception AR_AGING_BUCKETS_PKG.get_contact_information()');
424 END IF;
425 RETURN NULL;
426 END get_contact_information;
427
428
429
430
431 /*==========================================================================
432 | PRIVATE FUNCTION get_report_query |
433 | |
434 | DESCRIPTION |
435 | Report |
436 | |
437 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
438 | |
439 | PARAMETERS |
440 | NONE |
441 | |
442 | KNOWN ISSUES |
443 | |
444 | NOTES |
445 | |
446 | MODIFICATION HISTORY |
447 | Date Author Description of Changes |
448 | 10-JUL-2009 Naveen Prodduturi Created |
449 *==========================================================================*/
450 FUNCTION get_report_query RETURN VARCHAR2 IS
451
452 l_report_query VARCHAR2(32000);
453 l_filter_criteria VARCHAR2(32000);
454
455 BEGIN
456 l_filter_criteria := ' and ext.parent_request_id = '||pg_request_id;
457
458 IF pg_in_amt_due_low IS NOT NULL THEN
459 l_filter_criteria := l_filter_criteria||'
460 and ext.amt_due_remaining >= '||pg_in_amt_due_low;
461 END IF;
462
463 IF pg_in_amt_due_high IS NOT NULL THEN
464 l_filter_criteria := l_filter_criteria||'
465 and ext.amt_due_remaining <= '||pg_in_amt_due_high;
466 END IF;
467
471 END IF;
468 IF pg_accounting_method = 'MFAR' THEN
469 IF pg_in_bal_segment_low IS NOT NULL THEN
470 l_filter_criteria := l_filter_criteria||' and NVL(mfar.bal_segment_value,ext.bal_segment_value) >= '''||pg_in_bal_segment_low||'''';
472
473 IF pg_in_bal_segment_high IS NOT NULL THEN
474 l_filter_criteria := l_filter_criteria||' and NVL(mfar.bal_segment_value,ext.bal_segment_value ) <= '''||pg_in_bal_segment_high||'''';
475 END IF;
476 ELSE
477 IF pg_in_bal_segment_low IS NOT NULL THEN
478 l_filter_criteria := l_filter_criteria||' and ext.bal_segment_value >= '''||pg_in_bal_segment_low||'''';
479 END IF;
480
481 IF pg_in_bal_segment_high IS NOT NULL THEN
482 l_filter_criteria := l_filter_criteria||' and ext.bal_segment_value <= '''||pg_in_bal_segment_high||'''';
483 END IF;
484 END IF;
485
486 IF pg_accounting_method = 'MFAR' AND
487 pg_rep_type = 'ARXAGF' THEN
488
489 IF pg_in_summary_option_low ='C' THEN
490 l_report_query := '
491 select customer_id,
492 customer_number,
493 short_customer_name customer_name,
494 sort_field1,
495 inv_tid_inv,
496 contact_site_id,
497 customer_state,
498 customer_city,
499 cust_acct_site_id,
500 sum(customer_amount ) customer_total,
501 sum( risk_amount ) risk_total,
502 sum( pmt_amount ) pmt_total,
503 sum( cm_amount ) cm_total,
504 sum( claim_amount ) claim_total,
505 sum( inv_amount ) inv_total,
506 data_converted_flag,
507 SUM( bucket_0 ) b_0,
508 SUM( bucket_1 ) b_1,
509 SUM( bucket_2 ) b_2,
510 SUM( bucket_3 ) b_3,
511 SUM( bucket_4 ) b_4,
512 SUM( bucket_5 ) b_5,
513 SUM( bucket_6 ) b_6,
514 bal_segment_value,
515 contact_name,
516 contact_phone
517 from
518 ( select
519 ext.customer_id,
520 ext.customer_number,
521 ext.short_customer_name,
522 NVL(mfar.sort_field1,ext.sort_field1) sort_field1,
523 ext.sort_field2,
524 ext.inv_tid_inv,
525 ext.contact_site_id,
526 ext.customer_state,
527 ext.customer_city,
528 ext.cust_acct_site_id,
529 mfar.rec_aging_amount,
530 ext.amt_due_remaining,
531 nvl(mfar.rec_aging_amount,ext.amt_due_remaining) customer_amount,
532 decode(class,'''|| pg_risk_meaning ||''',nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0) risk_amount,
533 decode(class,''PMT'',nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0) pmt_amount,
534 decode(class,''CM'',nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0) cm_amount,
535 decode(class,''CLAIM'',nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0) claim_amount,
536 decode(class,'''|| pg_risk_meaning ||''',0,
537 ''PMT'',0,
538 ''CM'',0,
539 ''CLAIM'',0,nvl(mfar.rec_aging_amount,ext.amt_due_remaining)) inv_amount,
540 ext.data_converted_flag,
541 ext.exchange_rate,
542 DECODE(bucket_0,0,0,
543 DECODE(class,'''|| pg_risk_meaning ||''',
544 DECODE( '''|| pg_risk_option||''',''DETAIL'',
545 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
546 ''PMT'', DECODE( '''|| pg_credit_option||''',''DETAIL'',
547 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
548 ''CM'', DECODE( '''|| pg_credit_option||''',''DETAIL'',
549 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
550 ''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
551 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
552 nvl(mfar.rec_aging_amount,ext.amt_due_remaining) )) bucket_0,
553 DECODE(bucket_1,0,0,
554 DECODE(class,'''|| pg_risk_meaning ||''',
555 DECODE('''|| pg_risk_option||''',''DETAIL'',
556 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
557 ''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
558 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
559 ''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
560 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
561 ''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
562 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
563 nvl(mfar.rec_aging_amount,ext.amt_due_remaining) )) bucket_1,
564 DECODE(bucket_2,0,0,
565 DECODE(class,'''|| pg_risk_meaning ||''',
566 DECODE('''|| pg_risk_option||''',''DETAIL'',
567 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
568 ''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
569 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
570 ''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
571 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
572 ''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
573 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
574 nvl(mfar.rec_aging_amount,ext.amt_due_remaining) )) bucket_2,
575 DECODE(bucket_3,0,0,
576 DECODE(class,'''|| pg_risk_meaning ||''',
577 DECODE('''|| pg_risk_option||''',''DETAIL'',
578 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
579 ''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
580 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
581 ''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
582 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
583 ''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
584 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
585 nvl(mfar.rec_aging_amount,ext.amt_due_remaining) )) bucket_3,
586 DECODE(bucket_4,0,0,
590 ''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
587 DECODE(class,'''|| pg_risk_meaning ||''',
588 DECODE('''|| pg_risk_option||''',''DETAIL'',
589 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
591 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
592 ''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
593 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
594 ''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
595 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
596 nvl(mfar.rec_aging_amount,ext.amt_due_remaining) )) bucket_4,
597 DECODE(bucket_5,0,0,
598 DECODE(class,'''|| pg_risk_meaning ||''',
599 DECODE('''|| pg_risk_option||''',''DETAIL'',
600 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
601 ''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
602 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
603 ''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
604 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
605 ''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
606 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
607 nvl(mfar.rec_aging_amount,ext.amt_due_remaining) )) bucket_5,
608 DECODE(bucket_6,0,0,
609 DECODE(class,'''|| pg_risk_meaning ||''',
610 DECODE('''|| pg_risk_option||''',''DETAIL'',
611 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
612 ''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
613 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
614 ''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
615 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
616 ''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
617 nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
618 nvl(mfar.rec_aging_amount,ext.amt_due_remaining) )) bucket_6,
619 DECODE(mfar.bal_segment_value,null,ext.bal_segment_value,
620 mfar.bal_segment_value) bal_segment_value,
621 ext.contact_name,
622 ext.contact_phone
623 from ar_aging_extract ext,
624 ( select mfar.*,
625 '||pg_accounting_flexfield||' sort_field1,
626 '||pg_acct_flex_bal_seg||' bal_segment_value
627 from ar_aging_mfar_extract mfar,
628 gl_code_combinations c
629 where c.code_combination_id = mfar.code_combination_id
630 ) mfar
631 where ext.parent_request_id = mfar.parent_request_id(+)
632 and ext.payment_schedule_id = mfar.payment_schedule_id(+)
633 '||l_filter_criteria||'
634 )
635 group by customer_id,
636 customer_number,
637 short_customer_name,
638 sort_field1,
639 inv_tid_inv,
640 contact_site_id,
641 customer_state,
642 customer_city,
643 cust_acct_site_id,
644 data_converted_flag,
645 contact_name,
646 contact_phone,
647 bal_segment_value,
648 rec_aging_amount
649 having sum(nvl(rec_aging_amount,amt_due_remaining)) <> 0';
650 ELSE
651 l_report_query := '
652 SELECT ext.customer_id,
653 ext.customer_number,
654 ext.short_customer_name customer_name,
655 NVL(mfar.sort_field1,ext.sort_field1) sort_field1,
656 ext.sort_field2,
657 ext.inv_tid_inv,
658 ext.contact_site_id,
659 ext.customer_state,
660 ext.customer_city,
661 ext.cust_acct_site_id,
662 ext.payment_schedule_id,
663 ext.class,
664 TO_CHAR(ext.due_date,''YYYY-MM-DD'') due_date,
665 nvl(mfar.rec_aging_amount,ext.amt_due_remaining) amt_due_remaining,
666 ext.trx_number,
667 ext.days_past_due,
668 TO_CHAR(ext.gl_date,''YYYY-MM-DD'') gl_date,
669 ext.data_converted_flag,
670 ext.exchange_rate,
671 DECODE(bucket_0,0,0,nvl(mfar.rec_aging_amount,ext.amt_due_remaining)) b_0,
672 DECODE(bucket_1,0,0,nvl(mfar.rec_aging_amount,ext.amt_due_remaining)) b_1,
673 DECODE(bucket_2,0,0,nvl(mfar.rec_aging_amount,ext.amt_due_remaining)) b_2,
674 DECODE(bucket_3,0,0,nvl(mfar.rec_aging_amount,ext.amt_due_remaining)) b_3,
675 DECODE(bucket_4,0,0,nvl(mfar.rec_aging_amount,ext.amt_due_remaining)) b_4,
676 DECODE(bucket_5,0,0,nvl(mfar.rec_aging_amount,ext.amt_due_remaining)) b_5,
677 DECODE(bucket_6,0,0,nvl(mfar.rec_aging_amount,ext.amt_due_remaining)) b_6,
678 DECODE(mfar.bal_segment_value,null,ext.bal_segment_value,
679 mfar.bal_segment_value) bal_segment_value,
680 ext.invoice_type,
681 ext.cons_billing_number,
682 ext.contact_name,
683 ext.contact_phone
684 from ar_aging_extract ext,
685 ( select mfar.*,
686 '||pg_accounting_flexfield||' sort_field1,
687 '||pg_acct_flex_bal_seg||' bal_segment_value
688 from ar_aging_mfar_extract mfar,
689 gl_code_combinations c
690 where c.code_combination_id = mfar.code_combination_id ) mfar
691 where ext.parent_request_id = mfar.parent_request_id(+)
692 and ext.payment_schedule_id = mfar.payment_schedule_id(+)
693 and nvl(mfar.rec_aging_amount,ext.amt_due_remaining) <> 0
694 '||l_filter_criteria;
695 END IF;
696 ELSE
697 IF pg_in_summary_option_low ='C' THEN
698 l_report_query := '
699 select customer_id,
700 customer_number,
701 short_customer_name customer_name,
702 sort_field1,
703 inv_tid_inv,
704 contact_site_id,
705 customer_state,
706 customer_city,
707 cust_acct_site_id,
708 sum(customer_amount ) customer_total,
709 sum( risk_amount ) risk_total,
713 sum( inv_amount ) inv_total,
710 sum( pmt_amount ) pmt_total,
711 sum( cm_amount ) cm_total,
712 sum( claim_amount ) claim_total,
714 data_converted_flag,
715 SUM( bucket_0 ) b_0,
716 SUM( bucket_1 ) b_1,
717 SUM( bucket_2 ) b_2,
718 SUM( bucket_3 ) b_3,
719 SUM( bucket_4 ) b_4,
720 SUM( bucket_5 ) b_5,
721 SUM( bucket_6 ) b_6,
722 bal_segment_value,
723 contact_name,
724 contact_phone
725 from
726 ( select
727 ext.customer_id,
728 ext.customer_number,
729 ext.short_customer_name,
730 ext.sort_field1 sort_field1,
731 ext.sort_field2,
732 ext.inv_tid_inv,
733 ext.contact_site_id,
734 ext.customer_state,
735 ext.customer_city,
736 ext.cust_acct_site_id,
737 ext.amt_due_remaining,
738 ext.amt_due_remaining customer_amount,
739 decode(class,'''|| pg_risk_meaning ||''',ext.amt_due_remaining,0) risk_amount,
740 decode(class,''PMT'',ext.amt_due_remaining,0) pmt_amount,
741 decode(class,''CM'',ext.amt_due_remaining,0) cm_amount,
742 decode(class,''CLAIM'',ext.amt_due_remaining,0) claim_amount,
743 decode(class,'''|| pg_risk_meaning ||''',0,
744 ''PMT'',0,
745 ''CM'',0,
746 ''CLAIM'',0,ext.amt_due_remaining) inv_amount,
747 ext.data_converted_flag,
748 ext.exchange_rate,
749 DECODE(bucket_0,0,0,
750 DECODE(class,'''|| pg_risk_meaning ||''',
751 DECODE( '''|| pg_risk_option||''',''DETAIL'',
752 ext.amt_due_remaining,0),
753 ''PMT'', DECODE( '''|| pg_credit_option||''',''DETAIL'',
754 ext.amt_due_remaining,0),
755 ''CM'', DECODE( '''|| pg_credit_option||''',''DETAIL'',
756 ext.amt_due_remaining,0),
757 ''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
758 ext.amt_due_remaining,0),
759 ext.amt_due_remaining )) bucket_0,
760 DECODE(bucket_1,0,0,
761 DECODE(class,'''|| pg_risk_meaning ||''',
762 DECODE('''|| pg_risk_option||''',''DETAIL'',
763 ext.amt_due_remaining,0),
764 ''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
765 ext.amt_due_remaining,0),
766 ''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
767 ext.amt_due_remaining,0),
768 ''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
769 ext.amt_due_remaining,0),
770 ext.amt_due_remaining )) bucket_1,
771 DECODE(bucket_2,0,0,
772 DECODE(class,'''|| pg_risk_meaning ||''',
773 DECODE('''|| pg_risk_option||''',''DETAIL'',
774 ext.amt_due_remaining,0),
775 ''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
776 ext.amt_due_remaining,0),
777 ''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
778 ext.amt_due_remaining,0),
779 ''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
780 ext.amt_due_remaining,0),
781 ext.amt_due_remaining )) bucket_2,
782 DECODE(bucket_3,0,0,
783 DECODE(class,'''|| pg_risk_meaning ||''',
784 DECODE('''|| pg_risk_option||''',''DETAIL'',
785 ext.amt_due_remaining,0),
786 ''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
787 ext.amt_due_remaining,0),
788 ''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
789 ext.amt_due_remaining,0),
790 ''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
791 ext.amt_due_remaining,0),
792 ext.amt_due_remaining )) bucket_3,
793 DECODE(bucket_4,0,0,
794 DECODE(class,'''|| pg_risk_meaning ||''',
795 DECODE('''|| pg_risk_option||''',''DETAIL'',
796 ext.amt_due_remaining,0),
797 ''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
798 ext.amt_due_remaining,0),
799 ''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
800 ext.amt_due_remaining,0),
801 ''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
802 ext.amt_due_remaining,0),
803 ext.amt_due_remaining )) bucket_4,
804 DECODE(bucket_5,0,0,
805 DECODE(class,'''|| pg_risk_meaning ||''',
806 DECODE('''|| pg_risk_option||''',''DETAIL'',
807 ext.amt_due_remaining,0),
808 ''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
809 ext.amt_due_remaining,0),
810 ''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
811 ext.amt_due_remaining,0),
812 ''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
813 ext.amt_due_remaining,0),
814 ext.amt_due_remaining )) bucket_5,
815 DECODE(bucket_6,0,0,
816 DECODE(class,'''|| pg_risk_meaning ||''',
817 DECODE('''|| pg_risk_option||''',''DETAIL'',
818 ext.amt_due_remaining,0),
819 ''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
820 ext.amt_due_remaining,0),
821 ''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
822 ext.amt_due_remaining,0),
823 ''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
824 ext.amt_due_remaining,0),
825 ext.amt_due_remaining )) bucket_6,
826 bal_segment_value,
827 ext.contact_name,
828 ext.contact_phone
829 from ar_aging_extract ext
830 where 1 = 1
831 '||l_filter_criteria||'
832 )
833 group by customer_id,
834 customer_number,
835 short_customer_name,
836 sort_field1,
837 inv_tid_inv,
838 contact_site_id,
839 customer_state,
840 customer_city,
841 cust_acct_site_id,
842 data_converted_flag,
843 contact_name,
844 contact_phone,
845 bal_segment_value
846 having sum(amt_due_remaining) <> 0';
847 ELSE
848 l_report_query := '
849 SELECT customer_id,
850 customer_number,
851 short_customer_name customer_name,
852 sort_field1,
853 sort_field2,
854 inv_tid_inv,
855 contact_site_id,
856 customer_state,
857 customer_city,
858 cust_acct_site_id,
859 payment_schedule_id,
860 class,
861 TO_CHAR(due_date,''YYYY-MM-DD'') due_date,
862 amt_due_remaining,
863 trx_number,
864 days_past_due,
865 TO_CHAR(gl_date,''YYYY-MM-DD'') gl_date,
866 gl_date,
867 data_converted_flag,
868 exchange_rate,
869 DECODE(bucket_0,0,0,amt_due_remaining) b_0,
870 DECODE(bucket_1,0,0,amt_due_remaining) b_1,
871 DECODE(bucket_2,0,0,amt_due_remaining) b_2,
872 DECODE(bucket_3,0,0,amt_due_remaining) b_3,
873 DECODE(bucket_4,0,0,amt_due_remaining) b_4,
874 DECODE(bucket_5,0,0,amt_due_remaining) b_5,
875 DECODE(bucket_6,0,0,amt_due_remaining) b_6,
876 bal_segment_value,
877 invoice_type,
878 cons_billing_number,
879 contact_name,
880 contact_phone
881 from ar_aging_extract ext
882 where 1=1 '||l_filter_criteria;
883 END IF;
884 END IF;
885
886 RETURN l_report_query;
887 END get_report_query;
888
889
890
891
892 /*==========================================================================
893 | PRIVATE FUNCTION get_report_header_xml |
894 | |
895 | DESCRIPTION |
896 | |
897 | |
898 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
899 | |
900 | PARAMETERS |
901 | NONE |
902 | |
903 | KNOWN ISSUES |
904 | |
905 | NOTES |
906 | |
907 | MODIFICATION HISTORY |
908 | Date Author Description of Changes |
909 | 10-JUL-2009 Naveen Prodduturi Created |
910 *==========================================================================*/
911 FUNCTION get_report_header_xml RETURN VARCHAR2 IS
912 l_message VARCHAR2(2000);
913 l_xml_header VARCHAR2(32000);
914
915 BEGIN
916 IF PG_DEBUG in ('Y', 'C') THEN
917 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.get_report_header_xml()+');
918 END IF;
919
920 IF to_number(pg_reporting_level) = 1000 AND
921 mo_utils.check_ledger_in_sp(TO_NUMBER(pg_reporting_entity_id)) = 'N' THEN
922 FND_MESSAGE.SET_NAME('FND','FND_MO_RPT_PARTIAL_LEDGER');
923 l_message := FND_MESSAGE.get;
924 END IF;
928 <MSG_TXT>'||l_message||'</MSG_TXT>
925
926 l_xml_header := '<?xml version="1.0" encoding="'||fnd_profile.value('ICX_CLIENT_IANA_ENCODING')||'"?>
927 <ARAGEREP>
929 <COMPANY_NAME>'||pg_company_name||'</COMPANY_NAME>
930 <REPORTING_LEVEL>'||format_string(pg_reporting_level_name) ||'</REPORTING_LEVEL>
931 <REPORTING_ENTITY>'||format_string(pg_reporting_entity_name) ||'</REPORTING_ENTITY>
932 <BAL_SEG_LOW>'||pg_in_bal_segment_low||'</BAL_SEG_LOW>
933 <BAL_SEG_HIGH>'||pg_in_bal_segment_high||'</BAL_SEG_HIGH>
934 <AS_OF_GL_DATE>'||TO_CHAR(pg_in_as_of_date_low,'YYYY-MM-DD')||'</AS_OF_GL_DATE>
935 <SUMMARY_TYPE>'||pg_in_summary_option_low||'</SUMMARY_TYPE>
936 <SUMMARY_TYPE_MEANING>'||ARPT_SQL_FUNC_UTIL.get_lookup_meaning('REPORT_TYPE',pg_in_summary_option_low)||'</SUMMARY_TYPE_MEANING>
937 <REPORT_FORMAT>'||pg_in_format_option_low||'</REPORT_FORMAT>
938 <REPORT_FORMAT_MEANING>'||ARPT_SQL_FUNC_UTIL.get_lookup_meaning('REPORT_FORMAT',pg_in_format_option_low)||'</REPORT_FORMAT_MEANING>
939 <BUCKET_NAME>'||pg_in_bucket_type_low||'</BUCKET_NAME>
940 <CREDIT_OPTION>'||pg_credit_option||'</CREDIT_OPTION>
941 <CREDIT_OPTION_MEANING>'||ARPT_SQL_FUNC_UTIL.get_lookup_meaning('OPEN_CREDITS',pg_credit_option)||'</CREDIT_OPTION_MEANING>
942 <RISK_OPTION>'||pg_risk_option||'</RISK_OPTION>
943 <RISK_OPTION_MEANING>'||ARPT_SQL_FUNC_UTIL.get_lookup_meaning('SHOW_RISK',pg_risk_option)||'</RISK_OPTION_MEANING>
944 <CURRENCY>'||pg_in_currency||'</CURRENCY>
945 <CUST_NAME_LOW><![CDATA['||pg_in_customer_name_low||']]> </CUST_NAME_LOW>
946 <CUST_NAME_HIGH><![CDATA['||pg_in_customer_name_high||']]></CUST_NAME_HIGH>
947 <CUST_NUM_LOW>'||pg_in_customer_num_low||'</CUST_NUM_LOW>
948 <CUST_NUM_HIGH>'||pg_in_customer_num_high||'</CUST_NUM_HIGH>
949 <AMT_DUE_LOW>'||pg_in_amt_due_low||'</AMT_DUE_LOW>
950 <AMT_DUE_HIGH>'||pg_in_amt_due_high||'</AMT_DUE_HIGH>
951 <INV_TYPE_LOW>'||pg_in_invoice_type_low||'</INV_TYPE_LOW>
952 <INV_TYPE_HIGH>'||pg_in_invoice_type_high||'</INV_TYPE_HIGH>
953 <CONS_PROFILE_VALUE>'||pg_cons_profile_value||'</CONS_PROFILE_VALUE>
954 <FUNC_CURRENCY>'||pg_functional_currency||'</FUNC_CURRENCY>
955 <RISK_MEANING>'||pg_risk_meaning||'</RISK_MEANING>'||AR_AGING_BUCKETS_PKG.get_report_heading();
956
957 IF PG_DEBUG in ('Y', 'C') THEN
958 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.get_report_header_xml()-');
959 END IF;
960
961 RETURN l_xml_header;
962
963 EXCEPTION
964 WHEN OTHERS THEN
965 arp_standard.debug( 'Exception message '||SQLERRM);
966 arp_standard.debug( 'Exception AR_AGING_BUCKETS_PKG.get_report_header_xml()');
967 RETURN NULL;
968 END get_report_header_xml;
969
970
971
972
973 /*==========================================================================
974 | PRIVATE PROCEDURE populate_setup_information |
975 | |
976 | DESCRIPTION |
977 | Populates setup related info to local variables |
978 | |
979 | |
980 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
981 | |
982 | PARAMETERS |
983 | NONE |
984 | |
985 | KNOWN ISSUES |
986 | |
987 | NOTES |
988 | |
989 | MODIFICATION HISTORY |
990 | Date Author Description of Changes |
991 | 10-JUL-2009 Naveen Prodduturi Created |
992 *==========================================================================*/
993 PROCEDURE populate_setup_information IS
994 l_sys_query VARCHAR2(20000);
995
996 BEGIN
997 IF PG_DEBUG in ('Y', 'C') THEN
998 arp_standard.debug( ' AR_AGING_BUCKETS_PKG.populate_setup_information()+');
999 END IF;
1000
1001 l_sys_query := '
1002 SELECT param.org_id,
1003 sob.name,
1004 sob.chart_of_accounts_id,
1005 sob.currency_code,
1006 cur.precision,
1007 decode(:p_in_currency,NULL,''Y'',NULL),
1008 param.set_of_books_id
1009 FROM gl_sets_of_books sob,
1010 ar_system_parameters param,
1011 fnd_currencies cur
1012 WHERE sob.set_of_books_id = param.set_of_books_id
1013 AND sob.currency_code = cur.currency_code
1014 AND rownum = 1
1015 '||pg_org_where_sys_param;
1016
1017 IF PG_DEBUG in ('Y', 'C') THEN
1018 arp_standard.debug( ' l_sys_query :'||l_sys_query);
1019 END IF;
1020
1021 EXECUTE IMMEDIATE l_sys_query
1022 INTO pg_param_org_id,
1023 pg_company_name,
1024 pg_coaid,
1025 pg_functional_currency,
1026 pg_func_curr_precision,
1027 pg_convert_flag,
1028 pg_set_of_books_id
1029 USING pg_in_currency;
1030
1031 IF PG_DEBUG in ('Y', 'C') THEN
1032 arp_standard.debug( ' Rows returned '||SQL%ROWCOUNT);
1033 arp_standard.debug( ' AR_AGING_BUCKETS_PKG.populate_setup_information()+');
1034 END IF;
1035
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 IF PG_DEBUG in ('Y', 'C') THEN
1039 arp_standard.debug( ' Exception '||SQLERRM);
1043 END populate_setup_information;
1040 arp_standard.debug( ' Exception AR_AGING_BUCKETS_PKG.populate_setup_information()');
1041 END IF;
1042 RAISE;
1044
1045
1046
1047
1048
1049 /*==========================================================================
1050 | PRIVATE PROCEDURE initialize_package_globals |
1051 | |
1052 | DESCRIPTION |
1053 | Populates reporting entity criteria strings,balancing segment info |
1054 | and other message info required |
1055 | |
1056 | |
1057 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
1058 | |
1059 | PARAMETERS |
1060 | NONE |
1061 | |
1062 | KNOWN ISSUES |
1063 | |
1064 | NOTES |
1065 | |
1066 | MODIFICATION HISTORY |
1067 | Date Author Description of Changes |
1068 | 10-JUL-2009 Naveen Prodduturi Created |
1069 *==========================================================================*/
1070 PROCEDURE initialize_package_globals IS
1071 BEGIN
1072
1073 IF PG_DEBUG in ('Y', 'C') THEN
1074 arp_standard.debug( ' AR_AGING_BUCKETS_PKG.initialize_package_globals()+');
1075 END IF;
1076
1077 XLA_MO_REPORTING_API.Initialize(pg_reporting_level, pg_reporting_entity_id, 'AUTO');
1078
1079 pg_reporting_entity_name := substrb(XLA_MO_REPORTING_API.get_reporting_entity_name,1,80);
1080 pg_reporting_level_name := substrb(XLA_MO_REPORTING_API.get_reporting_level_name,1,30);
1081
1082 IF PG_DEBUG in ('Y', 'C') THEN
1083 arp_standard.debug( ' pg_reporting_entity_name '||pg_reporting_entity_name );
1084 arp_standard.debug( ' pg_reporting_level_name '||pg_reporting_level_name );
1085 END IF;
1086
1087 pg_org_where_ps := XLA_MO_REPORTING_API.Get_Predicate('ps', 'push_subq');
1088 pg_org_where_gld := XLA_MO_REPORTING_API.Get_Predicate('gld', 'push_subq');
1089 pg_org_where_ct := XLA_MO_REPORTING_API.Get_Predicate('ct', 'push_subq');
1090 pg_org_where_sales := XLA_MO_REPORTING_API.Get_Predicate('sales', 'push_subq');
1091 pg_org_where_ct2 := XLA_MO_REPORTING_API.Get_Predicate('ct2', 'push_subq');
1092 pg_org_where_adj := XLA_MO_REPORTING_API.Get_Predicate('adj', 'push_subq');
1093 pg_org_where_app := XLA_MO_REPORTING_API.Get_Predicate('app', 'push_subq');
1094 pg_org_where_crh := XLA_MO_REPORTING_API.Get_Predicate('crh', 'push_subq');
1095 pg_org_where_ra := XLA_MO_REPORTING_API.Get_Predicate('app', 'push_subq');
1096 pg_org_where_cr := XLA_MO_REPORTING_API.Get_Predicate('cr', 'push_subq');
1097 pg_org_where_sys_param := XLA_MO_REPORTING_API.Get_Predicate('PARAM',null);
1098
1099
1100 /* Replace the variables to bind with the function calls so that we don't have to bind those */
1101 pg_org_where_ps := replace(pg_org_where_ps,
1102 ':p_reporting_entity_id','AR_AGING_BUCKETS_PKG.get_reporting_entity_id()');
1103 pg_org_where_gld := replace(pg_org_where_gld,
1104 ':p_reporting_entity_id','AR_AGING_BUCKETS_PKG.get_reporting_entity_id()');
1105 pg_org_where_ct := replace(pg_org_where_ct,
1106 ':p_reporting_entity_id','AR_AGING_BUCKETS_PKG.get_reporting_entity_id()');
1107 pg_org_where_sales := replace(pg_org_where_sales,
1108 ':p_reporting_entity_id','AR_AGING_BUCKETS_PKG.get_reporting_entity_id()');
1109 pg_org_where_ct2 := replace(pg_org_where_ct2,
1110 ':p_reporting_entity_id','AR_AGING_BUCKETS_PKG.get_reporting_entity_id()');
1111 pg_org_where_adj := replace(pg_org_where_adj,
1112 ':p_reporting_entity_id','AR_AGING_BUCKETS_PKG.get_reporting_entity_id()');
1113 pg_org_where_app := replace(pg_org_where_app,
1114 ':p_reporting_entity_id','AR_AGING_BUCKETS_PKG.get_reporting_entity_id()');
1115 pg_org_where_crh := replace(pg_org_where_crh,
1116 ':p_reporting_entity_id','AR_AGING_BUCKETS_PKG.get_reporting_entity_id()');
1117 pg_org_where_ra := replace(pg_org_where_ra,
1118 ':p_reporting_entity_id','AR_AGING_BUCKETS_PKG.get_reporting_entity_id()');
1119 pg_org_where_cr := replace(pg_org_where_cr,
1120 ':p_reporting_entity_id','AR_AGING_BUCKETS_PKG.get_reporting_entity_id()');
1121 pg_org_where_sys_param := replace(pg_org_where_sys_param,
1122 ':p_reporting_entity_id','AR_AGING_BUCKETS_PKG.get_reporting_entity_id()');
1123
1124 IF PG_DEBUG in ('Y', 'C') THEN
1125 arp_standard.debug( ' Populating balancing segment strings.. ');
1126 END IF;
1127
1128 IF pg_in_bal_segment_low IS NULL AND pg_in_bal_segment_high IS NULL THEN
1129 pg_bal_seg_where := NULL;
1130 ELSIF pg_in_bal_segment_low IS NULL THEN
1131 pg_bal_seg_where := ' AND ' ||
1132 ar_calc_aging.FLEX_SQL(p_application_id => 101,
1133 p_id_flex_code => 'GL#',
1134 p_id_flex_num =>pg_coaid,
1135 p_table_alias => 'GC',
1136 p_mode => 'WHERE',
1140 ELSIF pg_in_bal_segment_high IS NULL THEN
1137 p_qualifier => 'GL_BALANCING',
1138 p_function => '<=',
1139 p_operand1 => pg_in_bal_segment_high);
1141 pg_bal_seg_where := ' AND ' ||
1142 ar_calc_aging.FLEX_SQL(p_application_id => 101,
1143 p_id_flex_code => 'GL#',
1144 p_id_flex_num => pg_coaid,
1145 p_table_alias => 'GC',
1149 p_operand1 => pg_in_bal_segment_low);
1146 p_mode => 'WHERE',
1147 p_qualifier => 'GL_BALANCING',
1148 p_function => '>=',
1150 ELSE
1151 pg_bal_seg_where := ' AND ' ||
1152 ar_calc_aging.FLEX_SQL(p_application_id => 101,
1153 p_id_flex_code => 'GL#',
1154 p_id_flex_num =>pg_coaid,
1155 p_table_alias => 'GC',
1156 p_mode => 'WHERE',
1157 p_qualifier => 'GL_BALANCING',
1158 p_function => 'BETWEEN',
1159 p_operand1 => pg_in_bal_segment_low,
1160 p_operand2 => pg_in_bal_segment_high);
1161 END IF;
1162
1163 pg_accounting_flexfield :=
1164 ar_calc_aging.FLEX_SQL(p_application_id => 101,
1165 p_id_flex_code => 'GL#',
1166 p_id_flex_num =>pg_coaid,
1167 p_table_alias => 'c',
1168 p_mode => 'SELECT',
1169 p_qualifier => 'ALL');
1170
1171 pg_acct_flex_bal_seg :=
1172 ar_calc_aging.FLEX_SQL(p_application_id => 101,
1173 p_id_flex_code => 'GL#',
1174 p_id_flex_num =>pg_coaid,
1175 p_table_alias => 'c',
1176 p_mode => 'SELECT',
1177 p_qualifier => 'GL_BALANCING');
1178
1179 pg_cons_profile_value := AR_SETUP.value('AR_SHOW_BILLING_NUMBER',null);
1180
1181 IF PG_DEBUG in ('Y', 'C') THEN
1182 arp_standard.debug( ' pg_accounting_flexfield '||pg_accounting_flexfield);
1183 arp_standard.debug( ' pg_bal_seg_where '||pg_bal_seg_where);
1184 arp_standard.debug( ' pg_acct_flex_bal_seg '||pg_acct_flex_bal_seg);
1185 arp_standard.debug( ' pg_cons_profile_value '||pg_cons_profile_value);
1186 END IF;
1187
1188 pg_report_name := ARP_STANDARD.fnd_message(pg_rep_type||'_REPORT_NAME');
1189 pg_segment_label := ARP_STANDARD.fnd_message(pg_rep_type ||'_SEG_LABEL');
1190 pg_bal_label := ARP_STANDARD.fnd_message(pg_rep_type ||'_BAL_LABEL');
1191 pg_label_1 := ARP_STANDARD.fnd_message(pg_rep_type||'_LABEL_1');
1192
1193
1194 IF pg_rep_type = 'ARXAGS' THEN
1195 IF UPPER(RTRIM(RPAD(pg_in_summary_option_low,1))) = 'I' THEN
1196 pg_bal_label := ARP_STANDARD.fnd_message(pg_rep_type ||'_BAL_LABEL_INV');
1197 END IF;
1198
1199 IF UPPER(SUBSTR(pg_in_sortoption,1,1)) = 'C' THEN
1200 pg_sort_on := ARP_STANDARD.fnd_message(pg_rep_type ||'_SORT_ONC');
1201 pg_grand_total := ARP_STANDARD.fnd_message(pg_rep_type ||'_GRAND_TOTAL_C');
1202 ELSE
1203 pg_sort_on := ARP_STANDARD.fnd_message(pg_rep_type || '_SORT_ONT');
1204 pg_grand_total := ARP_STANDARD.fnd_message(pg_rep_type ||'_GRAND_TOTAL_T');
1205 END IF;
1206 ELSE
1207 pg_sort_on := ARP_STANDARD.fnd_message(pg_rep_type||'_SORT_ON');
1208 pg_grand_total := ARP_STANDARD.fnd_message(pg_rep_type ||'_GRAND_TOTAL');
1209 END IF;
1210
1211 IF pg_rep_type IN ('ARXAGL','ARXAGR') THEN
1212 pg_label := ARP_STANDARD.fnd_message(pg_rep_type||'_LABEL');
1213 END IF;
1214
1215 IF PG_DEBUG in ('Y', 'C') THEN
1216 arp_standard.debug( ' pg_report_name '||pg_report_name);
1217 arp_standard.debug( ' pg_segment_label '||pg_segment_label);
1218 arp_standard.debug( ' pg_bal_label '||pg_bal_label);
1219 arp_standard.debug( ' pg_label_1 '||pg_label_1);
1220 arp_standard.debug( ' pg_sort_on '||pg_sort_on);
1221 arp_standard.debug( ' pg_grand_total '||pg_grand_total);
1222 arp_standard.debug( ' pg_label '||pg_label);
1223 END IF;
1224
1225 populate_setup_information;
1226
1227 pg_short_unid_phrase := RTRIM(RPAD(ARPT_SQL_FUNC_UTIL.get_lookup_meaning
1228 ('MISC_PHRASES','UNIDENTIFIED_PAYMENT'),20));
1229
1230 pg_payment_meaning := INITCAP(RTRIM(RPAD(ARPT_SQL_FUNC_UTIL.get_lookup_meaning
1231 ('INV/CM/ADJ','PMT'),20)));
1232
1233 pg_risk_meaning := rtrim(rpad(ARPT_SQL_FUNC_UTIL.get_lookup_meaning
1234 ('MISC_PHRASES','RISK'),20));
1235
1236 IF PG_DEBUG in ('Y', 'C') THEN
1237 arp_standard.debug( ' pg_short_unid_phrase :'||pg_short_unid_phrase);
1238 arp_standard.debug( ' pg_payment_meaning :'||pg_payment_meaning);
1239 arp_standard.debug( ' pg_risk_meaning :'||pg_risk_meaning);
1240 arp_standard.debug( ' AR_AGING_BUCKETS_PKG.initialize_package_globals()-');
1241 END IF;
1242
1243 EXCEPTION
1244 WHEN OTHERS THEN
1245 arp_standard.debug( ' Exception '||SQLERRM);
1249
1246 arp_standard.debug( ' Exception AR_AGING_BUCKETS_PKG.initialize_package_globals()');
1247 RAISE;
1248 END initialize_package_globals;
1250
1251
1252
1253 /*==========================================================================
1254 | PRIVATE PROCEDURE cleanup_staging_tables |
1255 | |
1256 | DESCRIPTION |
1257 | |
1258 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
1259 | |
1260 | PARAMETERS |
1261 | NONE |
1262 | |
1263 | KNOWN ISSUES |
1264 | |
1265 | NOTES |
1266 | |
1267 | MODIFICATION HISTORY |
1268 | Date Author Description of Changes |
1269 | 10-JUL-2009 Naveen Prodduturi Created |
1270 *==========================================================================*/
1271 PROCEDURE cleanup_staging_tables IS
1272 BEGIN
1273 IF PG_DEBUG in ('Y', 'C') THEN
1274 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.cleanup_staging_tables()+');
1275 arp_standard.debug( 'pg_retain_staging_flag :'||pg_retain_staging_flag );
1276 END IF;
1277
1278 DELETE
1279 FROM ar_aging_payment_schedules
1280 WHERE parent_request_id = pg_request_id;
1281
1282 DELETE
1283 FROM ar_aging_extract
1284 WHERE parent_request_id = pg_request_id;
1285
1286 DELETE
1287 FROM ar_aging_mfar_extract
1288 WHERE parent_request_id = pg_request_id;
1289
1290 IF PG_DEBUG in ('Y', 'C') THEN
1291 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.cleanup_staging_tables()-');
1292 END IF;
1293 END cleanup_staging_tables;
1294
1295
1296
1297
1301 | DESCRIPTION |
1298 /*==========================================================================
1299 | PRIVATE PROCEDURE alloc_aging_payment_schedules |
1300 | |
1302 | Populates all the eligible payment schedules based on the input |
1303 | criteria provided. |
1304 | |
1308 | c) query also allocates the payment schedule to child workers |
1305 | Procedure does the following |
1306 | a) build query based on input parameter values |
1307 | b) populate interim table with selected payment schedule records |
1309 | |
1310 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
1311 | |
1312 | PARAMETERS |
1313 | NONE |
1314 | |
1315 | KNOWN ISSUES |
1316 | |
1317 | NOTES |
1318 | filters p_in_amt_due_low and p_in_amt_due_high are not handled in this |
1319 | routine to avoid any possible read consistency issues as mentioned in |
1320 | bug 3487101 |
1321 | |
1322 | MODIFICATION HISTORY |
1323 | Date Author Description of Changes |
1324 | 10-JUL-2009 Naveen Prodduturi Created |
1325 *==========================================================================*/
1326 PROCEDURE alloc_aging_payment_schedules IS
1327 l_insert_stmt VARCHAR2(2000);
1328 l_select_caluse VARCHAR2(2000);
1329 l_from_clause VARCHAR2(32000);
1330 l_where_clause VARCHAR2(2000);
1331 l_final_stmt VARCHAR2(32000);
1332 l_crh_sub_query VARCHAR2(2000);
1333 l_inv_sub_query VARCHAR2(2000);
1334 l_ra_sub_query VARCHAR2(2000);
1335 l_stmt_cursor INTEGER;
1336 l_rows_processed NUMBER;
1337 BEGIN
1338 IF PG_DEBUG in ('Y', 'C') THEN
1339 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.alloc_aging_payment_schedules()+');
1340 END IF;
1341
1342 IF PG_DEBUG in ('Y', 'C') THEN
1343 arp_standard.debug( 'Deleting existing data for parent_request_id:'||pg_request_id);
1344 END IF;
1345
1346 --clean up the interim tables if there exists any data refering to current request
1347 cleanup_staging_tables;
1348
1349 /** customer info related table joins and where criteria gets appended to the query
1350 strings based on the filter criteria provided */
1351 IF pg_in_customer_num_low IS NOT NULL OR pg_in_customer_num_high IS NOT NULL OR
1352 pg_in_customer_name_low IS NOT NULL OR pg_in_customer_name_high IS NOT NULL THEN
1353
1354 l_from_clause := l_from_clause || ',
1355 hz_cust_accounts cust_acct ';
1356 l_where_clause := l_where_clause || '
1357 and ps.customer_id = cust_acct.cust_account_id ';
1358
1359 IF pg_in_customer_num_low IS NOT NULL THEN
1360 l_where_clause := l_where_clause ||'
1361 and cust_acct.account_number >= :pg_in_customer_num_low';
1362 END IF;
1363
1364 IF pg_in_customer_num_high IS NOT NULL THEN
1365 l_where_clause := l_where_clause ||'
1366 and cust_acct.account_number <= :pg_in_customer_num_high';
1367 END IF;
1368
1369 IF pg_in_customer_name_low IS NOT NULL OR
1370 pg_in_customer_name_high IS NOT NULL THEN
1371
1372 l_from_clause := l_from_clause || ',
1373 hz_parties party ';
1374 l_where_clause := l_where_clause || '
1375 and cust_acct.party_id = party.party_id ';
1376
1377 IF pg_in_customer_name_low IS NOT NULL THEN
1378 l_where_clause := l_where_clause || '
1379 and party.party_name >= :pg_in_customer_name_low';
1380 END IF;
1381
1382 IF pg_in_customer_name_high IS NOT NULL THEN
1383 l_where_clause := l_where_clause || '
1384 and party.party_name <= :pg_in_customer_name_high';
1385 END IF;
1386 END IF;
1387 END IF;
1388
1389 l_insert_stmt :=
1390 'INSERT /*HINT*/ INTO ar_aging_payment_schedules a
1391 ( payment_schedule_id,
1392 source_type,
1393 parent_request_id,
1394 worker_id
1395 ) ';
1396
1397 l_select_caluse :=
1398 ' SELECT payment_schedule_id,
1399 source_type,'
1400 ||pg_request_id||','||
1401 ' DECODE('||pg_worker_count||',1,'||pg_worker_id||', MOD(ROWNUM, '||pg_worker_count||' ) + 1) ';
1402
1403
1404 l_inv_sub_query := '
1405 select ps.payment_schedule_id,
1406 ''INV'' source_type
1407 from ar_payment_schedules ps '||l_from_clause||'
1408 WHERE ps.gl_date_closed > :as_of_date
1409 AND ps.gl_date <= :as_of_date
1410 AND DECODE(UPPER(:pg_in_currency),NULL, ps.invoice_currency_code,
1411 UPPER(:pg_in_currency)) = ps.invoice_currency_code
1412 AND ps.class <> ''PMT'''||pg_org_where_ps||l_where_clause;
1413
1414 IF pg_in_invoice_type_low IS NOT NULL THEN
1415 l_inv_sub_query := l_inv_sub_query || '
1416 and arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id)
1417 >= :pg_in_invoice_type_low ';
1418 END IF;
1419
1420 IF pg_in_invoice_type_high IS NOT NULL THEN
1421 l_inv_sub_query := l_inv_sub_query || '
1422 and arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id)
1423 <= :pg_in_invoice_type_high ';
1424 END IF;
1425
1426 l_crh_sub_query := '
1430 from ar_cash_receipt_history crh,
1427 select /*+ leading(crh) index(ps AR_PAYMENT_SCHEDULES_U2)*/
1428 distinct ps.payment_schedule_id,
1429 ''CRH'' source_type
1431 ar_payment_schedules ps '||l_from_clause||'
1432 where crh.gl_date <= :as_of_date
1433 and ( crh.current_record_flag = ''Y'' OR
1434 crh.reversal_gl_date > :as_of_date )
1435 and crh.status NOT IN
1436 ( DECODE(crh.factor_flag, ''Y'',''RISK_ELIMINATED'',
1437 ''N'',''CLEARED''), ''REVERSED'')
1438 and ps.cash_receipt_id = crh.cash_receipt_id
1439 and ps.class = ''PMT''
1440 AND DECODE(UPPER(:pg_in_currency),NULL, ps.invoice_currency_code,
1441 UPPER(:pg_in_currency)) = ps.invoice_currency_code
1442 and not exists
1443 ( SELECT ''x''
1444 FROM ar_receivable_applications ra
1445 WHERE ra.cash_receipt_id = crh.cash_receipt_id
1446 AND ra.status = ''ACTIVITY''
1447 AND applied_payment_schedule_id = -2
1448 )'||pg_org_where_crh||l_where_clause;
1449
1450 l_ra_sub_query := '
1451 select /*+ leading(ps) index(ps AR_PAYMENT_SCHEDULES_N9) index(app AR_RECEIVABLE_APPLICATIONS_N1)*/
1452 distinct ps.payment_schedule_id,
1453 ''RA'' source_type
1454 from ar_receivable_applications app,
1455 ar_payment_schedules ps '||l_from_clause||'
1456 where app.gl_date <= :as_of_date
1457 AND app.status IN ( ''ACC'' ,
1458 ''UNAPP'',
1459 ''UNID'' ,
1460 ''OTHER ACC'')
1461 AND NVL(app.confirmed_flag, ''Y'') = ''Y''
1462 AND app.reversal_gl_date IS NULL
1463 AND ps.cash_receipt_id = app.cash_receipt_id
1464 AND ps.class = ''PMT''
1465 AND DECODE(UPPER(:pg_in_currency),NULL, ps.invoice_currency_code,
1466 UPPER(:pg_in_currency)) = ps.invoice_currency_code
1467 AND ps.gl_date_closed > :as_of_date
1468 AND NVL( ps.receipt_confirmed_flag, ''Y'' ) = ''Y'''||pg_org_where_ra||l_where_clause;
1469
1470 l_from_clause := '
1471 FROM ('||l_inv_sub_query||'
1472 UNION ALL
1473 '||l_crh_sub_query||'
1474 UNION ALL
1475 '||l_ra_sub_query||') ps';
1476
1477
1478 IF PG_PARALLEL IN ('Y', 'C') THEN
1479 l_insert_stmt := REPLACE( l_insert_stmt,'/*HINT*/','/*+ parallel(a) append */');
1480 END IF;
1481
1482
1483 IF PG_PARALLEL IN ('Y', 'C') THEN
1484 COMMIT;
1485 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
1486 END IF;
1487
1488 l_final_stmt := l_insert_stmt ||
1489 l_select_caluse ||
1490 l_from_clause ;
1491
1492 arp_standard.debug( l_final_stmt );
1493
1494 l_stmt_cursor := dbms_sql.open_cursor;
1495 dbms_sql.parse (l_stmt_cursor,l_final_stmt,dbms_sql.v7);
1496
1497 dbms_sql.bind_variable (l_stmt_cursor,':as_of_date',pg_in_as_of_date_low);
1498 dbms_sql.bind_variable (l_stmt_cursor,':pg_in_currency',pg_in_currency);
1499
1500 IF pg_in_customer_num_low IS NOT NULL THEN
1501 dbms_sql.bind_variable (l_stmt_cursor,':pg_in_customer_num_low',pg_in_customer_num_low);
1502 END IF;
1503 IF pg_in_customer_num_high IS NOT NULL THEN
1504 dbms_sql.bind_variable (l_stmt_cursor,':pg_in_customer_num_high',pg_in_customer_num_high);
1508 END IF;
1505 END IF;
1506 IF pg_in_customer_name_low IS NOT NULL THEN
1507 dbms_sql.bind_variable (l_stmt_cursor,':pg_in_customer_name_low',pg_in_customer_name_low);
1509 IF pg_in_customer_name_high IS NOT NULL THEN
1510 dbms_sql.bind_variable (l_stmt_cursor,':pg_in_customer_name_high',pg_in_customer_name_high);
1511 END IF;
1512
1513 IF pg_in_invoice_type_low IS NOT NULL THEN
1514 dbms_sql.bind_variable (l_stmt_cursor,':pg_in_invoice_type_low',pg_in_invoice_type_low );
1515 END IF;
1516 IF pg_in_invoice_type_high IS NOT NULL THEN
1517 dbms_sql.bind_variable (l_stmt_cursor,':pg_in_invoice_type_high',pg_in_invoice_type_high);
1518 END IF;
1519
1520 l_rows_processed := dbms_sql.execute( l_stmt_cursor );
1521
1522 IF PG_PARALLEL IN ('Y', 'C') THEN
1523 COMMIT;
1524 END IF;
1525
1526 IF PG_DEBUG in ('Y', 'C') THEN
1527 arp_standard.debug( 'rows inserted into staging table:'||l_rows_processed);
1528 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.alloc_aging_payment_schedules()-');
1529 END IF;
1530
1531 EXCEPTION
1532 WHEN OTHERS THEN
1533 arp_standard.debug( 'Exception : '||SQLERRM );
1534 arp_standard.debug( 'In AR_AGING_BUCKETS_PKG.alloc_aging_payment_schedules()-');
1535 RAISE;
1536 END alloc_aging_payment_schedules;
1537
1538
1539
1540
1541 /*==========================================================================
1542 | PRIVATE PROCEDURE get_report_specific_info |
1543 | |
1544 | DESCRIPTION |
1545 | Populates all the eligible payment schedules based on the input |
1546 | criteria provided. |
1547 | |
1548 | Procedure does the following |
1549 | a) build query based on input parameter values |
1550 | b) populate interim table with selected payment schedule records |
1551 | c) query also allocates the payment schedule to child workers |
1552 | |
1553 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
1554 | |
1555 | PARAMETERS |
1556 | NONE |
1557 | |
1558 | KNOWN ISSUES |
1559 | |
1560 | NOTES |
1561 | filters p_in_amt_due_low and p_in_amt_due_high are not handled in this |
1562 | routine to avoid any possible read consistency issues as mentioned in |
1563 | bug 3487101 |
1564 | |
1565 | MODIFICATION HISTORY |
1566 | Date Author Description of Changes |
1567 | 10-JUL-2009 Naveen Prodduturi Created |
1568 *==========================================================================*/
1569 PROCEDURE get_report_specific_info( p_qry_category IN VARCHAR2,
1570 p_rep_specific_cols OUT NOCOPY VARCHAR2,
1571 p_rep_from_info OUT NOCOPY VARCHAR2,
1572 p_rep_where_cls OUT NOCOPY VARCHAR2,
1573 p_rep_spec_sub_query OUT NOCOPY VARCHAR2,
1574 p_rep_spec_grp_cols OUT NOCOPY VARCHAR2) IS
1575 BEGIN
1576 /*Only verified the flow related to aging by account report,need verify and replace the
1577 lexical parameters and test the complete flow when we decide to migrate all these
1578 reports(reports like ARXAGR ,ARXAGL and ARXAGS)*/
1579 IF PG_DEBUG in ('Y', 'C') THEN
1583
1580 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.get_report_specific_info()+');
1581 arp_standard.debug( 'p_qry_category '||p_qry_category);
1582 END IF;
1584 --report specific subquery
1585 IF pg_rep_type = 'ARXAGR' THEN
1586 RAISE p_not_implemented_exp;--will introduce the below code at later stages
1587 -- p_rep_specific_cols := ',extns.resource_name sort_field1
1588 -- ,nvl(sales.salesrep_id, -3) inv_tid_inv';
1589 --
1590 -- p_rep_from_info := ',ra_salesreps sales ,jtf_rs_resource_extns_vl extns ';
1591 --
1592 -- p_rep_where_cls := ' and ps.customer_trx_id = gld.customer_trx_id
1593 -- and nvl(ps.primary_salesrep_id,-3) = sales.salesrep_id
1594 -- and nvl(sales.org_id, ct.org_id) = ct.org_id ';
1595 --
1596 -- IF p_qry_category = AR_AGING_CTGRY_RECEIPT THEN
1597 -- p_rep_spec_grp_cols := ',extns.resource_name sort_field1
1598 -- ,nvl(sales.salesrep_id, -3) ';
1599 -- END IF;
1600 --
1601 -- IF p_qry_category = AR_AGING_CTGRY_INVOICE THEN
1602 -- p_rep_spec_sub_query := '
1603 -- UNION ALL
1604 -- SELECT /*HINT*/
1605 -- ps.customer_id,
1606 -- ps.customer_site_use_id ,
1607 -- ps.customer_trx_id,
1608 -- ps.payment_schedule_id,
1609 -- ps.class class_inv,
1610 -- ct.primary_salesrep_id primary_salesrep_id,
1611 -- ps.due_date due_date_inv,
1612 -- decode( :c_convert_flag, ''Y'',
1613 -- ps.acctd_amount_due_remaining,
1614 -- ps.amount_due_remaining) amt_due_remaining_inv,
1615 -- ps.trx_number,
1616 -- ps.amount_adjusted ,
1617 -- ps.amount_applied ,
1618 -- ps.amount_credited ,
1619 -- ps.amount_adjusted_pending,
1620 -- ps.gl_date ,
1621 -- ps.cust_trx_type_id,
1622 -- ps.org_id,
1623 -- ps.invoice_currency_code,
1624 -- nvl(ps.exchange_rate, 1) exchange_rate,
1625 -- ps.cons_inv_id
1626 -- FROM ar_aging_payment_schedules aging,
1627 -- ar_payment_schedules ps,
1628 -- ra_customer_trx ct,
1629 -- ar_adjustments adj
1630 -- WHERE aging.parent_request_id = :parent_request_id
1631 -- AND aging.worker_id = :worker_id
1632 -- AND aging.payment_schedule_id = ps.payment_schedule_id
1633 -- AND ps.gl_date <= :as_of_date
1634 -- AND ps.gl_date_closed > :as_of_date
1635 -- AND ps.class = ''CB''
1636 -- AND ps.customer_trx_id = adj.chargeback_customer_trx_id
1637 -- AND adj.customer_trx_id = ct.customer_trx_id';
1638 -- END IF;
1639
1640 ELSIF pg_rep_type = 'ARXAGS' THEN
1641 RAISE p_not_implemented_exp;--will introduce the below code at later stages
1642
1643 -- IF p_qry_category IN (AR_AGING_CTGRY_INVOICE,AR_AGING_CTGRY_BR ) THEN
1644 -- p_rep_specific_cols := ' ,decode(upper(:p_in_sortoption),
1645 -- ''CUSTOMER'',NULL,
1646 -- arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id)) sort_field1
1647 -- ,decode(upper(:p_in_sortoption),''CUSTOMER'',-999,ps.cust_trx_type_id) inv_tid_inv';
1648 --
1652 -- initcap(:lp_payment_meaning)) sort_field1
1649 -- ELSIF p_qry_category = AR_AGING_CTGRY_RECEIPT THEN
1650 -- p_rep_specific_cols := ',decode(upper(:p_in_sortoption),
1651 -- ''CUSTOMER'',NULL,
1653 -- ,-999 inv_tid_inv ';
1654 -- p_rep_spec_grp_cols:= ',decode(upper(:p_in_sortoption),
1655 -- ''CUSTOMER'',NULL,
1656 -- initcap(:lp_payment_meaning)) sort_field1
1657 -- ,-999 ';
1658 --
1659 -- ELSIF p_qry_category = AR_AGING_CTGRY_RISK THEN
1660 -- p_rep_specific_cols := ',decode(upper(:p_in_sortoption),
1661 -- ''CUSTOMER'',NULL,
1662 -- initcap(:pg_risk_meaning)) sort_field1
1663 -- ,-999 inv_tid_inv ';
1664 -- p_rep_spec_grp_cols:= ',decode(upper(:p_in_sortoption),
1665 -- ''CUSTOMER'',NULL,
1666 -- initcap(:pg_risk_meaning)) sort_field1
1667 -- ,-999 ';
1668 -- END IF;
1669
1670 ELSIF pg_rep_type = 'ARXAGL' THEN
1671 RAISE p_not_implemented_exp;--will introduce the below code at later stages
1672
1673 -- p_rep_specific_cols := ',col.name sort_field1
1674 -- ,col.collector_id inv_tid_inv';
1675 --
1676 -- p_rep_from_info := ',hz_customer_profiles site_cp
1677 -- ,hz_customer_profiles cust_cp
1678 -- ,ar_collectors col';
1679 --
1680 -- p_rep_where_cls := ' and cust_cp.cust_account_id = cust_acct.cust_account_id
1681 -- and cust_cp.site_use_id is null
1682 -- and site_cp.site_use_id(+) = ps.customer_site_use_id
1683 -- and col.collector_id = NVL(site_cp.collector_id, cust_cp.collector_id)
1684 -- and ps.customer_trx_id = gld.customer_trx_id ';
1685 --
1686 --
1687 -- IF pg_in_collector_low IS NOT NULL THEN
1688 -- p_rep_where_cls := p_rep_where_cls || ' and col.name >= :p_in_collector_low';
1689 -- END IF;
1690 --
1691 -- IF pg_in_collector_high IS NOT NULL THEN
1692 -- p_rep_where_cls := p_rep_where_cls || ' and col.name <= :p_in_collector_high' ;
1693 -- END IF;
1694 --
1695 -- IF p_qry_category IN (AR_AGING_CTGRY_RECEIPT,AR_AGING_CTGRY_RISK ) THEN
1696 -- p_rep_spec_grp_cols := ',col.name sort_field1
1697 -- ,col.collector_id ';
1698 -- END IF;
1699
1700 ELSIF(pg_rep_type = 'ARXAGF') THEN
1701
1702 IF p_qry_category = AR_AGING_CTGRY_INVOICE THEN
1703 p_rep_specific_cols := ',decode(types.post_to_gl, ''Y'', '||pg_accounting_flexfield||'
1704 ,NULL) sort_field1
1705 ,c.code_combination_id inv_tid_inv';
1706
1707 IF pg_accounting_method = 'MFAR' THEN
1708 p_rep_where_cls := p_rep_where_cls || '
1709 and types.cust_trx_type_id = ps.cust_trx_type_id
1710 and types.org_id = ps.org_id
1711 and decode(types.post_to_gl, ''N'', gld.code_combination_id,
1712 decode(gld.posting_control_id,-3,-999999,gld.code_combination_id))
1713 = c.code_combination_id ';
1714
1715 p_rep_from_info := p_rep_from_info ||' ,ra_cust_trx_types types ';
1716
1717 ELSE
1718 p_rep_from_info := p_rep_from_info ||'
1719 ,xla_distribution_links lk
1720 ,xla_ae_lines ae
1721 ,ra_cust_trx_types types ';
1722
1723 p_rep_where_cls := p_rep_where_cls || '
1724 and types.cust_trx_type_id = ps.cust_trx_type_id
1725 and types.org_id = ps.org_id
1726 and gld.cust_trx_line_gl_dist_id = lk.source_distribution_id_num_1(+)
1727 and lk.source_distribution_type(+) = ''RA_CUST_TRX_LINE_GL_DIST_ALL''
1728 and lk.application_id(+) = 222
1729 and ae.application_id(+) = 222
1733 ''CM_EXCH_GAIN_LOSS'', ''N'',
1730 and lk.ae_header_id = ae.ae_header_id(+)
1731 and lk.ae_line_num = ae.ae_line_num(+)
1732 and decode(lk.accounting_line_code, '''', ''Y'',
1734 ''AUTO_GEN_GAIN_LOSS'', ''N'',
1735 ''Y'') = ''Y''
1736 and decode(ae.ledger_id,'''',decode(types.post_to_gl,
1737 ''N'', gld.code_combination_id,
1738 decode(gld.posting_control_id,
1739 -3,-999999,
1740 gld.code_combination_id)),
1741 gld.set_of_books_id,ae.code_combination_id,
1742 -999999)= c.code_combination_id ';
1743 END IF;
1744 ELSE
1745 p_rep_specific_cols := ','||pg_accounting_flexfield||' sort_field1
1746 ,c.code_combination_id inv_tid_inv';
1747
1748 p_rep_spec_grp_cols := ','||pg_accounting_flexfield||'
1749 ,c.code_combination_id';
1750 END IF;
1751
1752 END IF;
1753
1754 --will introduce the below code at later stages
1758 -- END IF;
1755 -- IF pg_rep_type <> 'ARXAGF' THEN
1756 -- p_rep_where_cls := p_rep_where_cls || '
1757 -- and gld.code_combination_id = c.code_combination_id ';
1759
1760 /**set the column list along with tables and required joins to fetch consolidated billing
1761 info based on the profile */
1762 IF ( pg_cons_profile_value = 'N' ) then
1763 p_rep_specific_cols := p_rep_specific_cols || ',
1764 to_char(NULL) cons_billing_number ';
1765 ELSE
1766 p_rep_specific_cols := p_rep_specific_cols || ',
1767 ci.cons_billing_number cons_billing_number ';
1768 p_rep_from_info := p_rep_from_info || '
1769 ,ar_cons_inv ci ';
1770 p_rep_where_cls := p_rep_where_cls || '
1771 and ps.cons_inv_id = ci.cons_inv_id(+) ';
1772 p_rep_spec_grp_cols := p_rep_spec_grp_cols || '
1773 ,ci.cons_billing_number ';
1774 END IF;
1775
1776
1777 IF PG_DEBUG in ('Y', 'C') THEN
1778 arp_standard.debug( 'p_rep_specific_cols '||p_rep_specific_cols );
1779 arp_standard.debug( 'p_rep_from_info '||p_rep_from_info );
1780 arp_standard.debug( 'p_rep_where_cls '||p_rep_where_cls );
1781 arp_standard.debug( 'p_rep_spec_sub_query '||p_rep_spec_sub_query );
1782 arp_standard.debug( 'p_rep_spec_grp_cols '||p_rep_spec_grp_cols );
1783 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.get_report_specific_info()-');
1784 END IF;
1785
1786 EXCEPTION
1787 WHEN OTHERS THEN
1788 arp_standard.debug( 'Exception '||SQLERRM);
1789 arp_standard.debug( 'Exception :AR_AGING_BUCKETS_PKG.get_report_specific_info()-');
1790 RAISE;
1791 END get_report_specific_info;
1792
1793
1794
1795 /*==========================================================================
1796 | PRIVATE PROCEDURE bind_bucket_parameters |
1797 | |
1798 | DESCRIPTION |
1799 | The procedure is a utility to bind bucket info required across various |
1800 | select statements that make use of bucket_function. |
1801 | |
1802 | Procedure does the following |
1803 | a) binds all the info related to the current bucket |
1804 | |
1805 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
1806 | |
1807 | PARAMETERS |
1808 | NONE |
1809 | |
1810 | KNOWN ISSUES |
1811 | |
1812 | NOTES |
1813 | The cursor ensures that we always bind all the 7 bucket info. |
1814 | |
1815 | MODIFICATION HISTORY |
1816 | Date Author Description of Changes |
1817 | 10-JUL-2009 Naveen Prodduturi Created |
1818 *==========================================================================*/
1819 PROCEDURE bind_bucket_parameters( p_cursor INTEGER) IS
1820
1821 CURSOR buc_info_cur IS
1822 select *
1823 from
1824 ( select lines.bucket_sequence_num buc_number,
1825 days_start,
1826 days_to,
1827 report_heading1,
1828 report_heading2,
1829 type,
1830 DECODE(type,'DISPUTE_ONLY',type,
1831 'PENDADJ_ONLY',type,
1832 'DISPUTE_PENDADJ',type,null) bucket_category
1833 from ar_aging_bucket_lines lines,
1834 ar_aging_buckets buckets
1835 where lines.aging_bucket_id = buckets.aging_bucket_id
1836 and upper(buckets.bucket_name) = upper(pg_in_bucket_type_low)
1837 and nvl(buckets.status,'A') = 'A'
1838 ) buckets,
1839 ( select rownum-1 sequence_number
1840 from dual
1841 connect by
1842 rownum < 8 ) dummy
1843 where dummy.sequence_number = buckets.buc_number(+);
1844
1845
1846 BEGIN
1847 IF PG_DEBUG in ('Y', 'C') THEN
1848 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.bind_bucket_parameters()+');
1849 arp_standard.debug( 'p_cursor :'||p_cursor);
1850 END IF;
1851
1852 FOR buc_rec IN buc_info_cur LOOP
1853 IF PG_DEBUG in ('Y', 'C') THEN
1854 arp_standard.debug( ':bucket_days_from_'||buc_rec.sequence_number||' value '||buc_rec.days_start);
1855 arp_standard.debug( ':bucket_days_to_'||buc_rec.sequence_number||' value '||buc_rec.days_to);
1856 arp_standard.debug( ':bucket_line_type_'||buc_rec.sequence_number||' value '||buc_rec.type);
1857 arp_standard.debug( ':bucket_category_'||buc_rec.sequence_number||' value '||buc_rec.bucket_category);
1858 END IF;
1859
1860 dbms_sql.bind_variable(p_cursor, ':bucket_days_from_'||buc_rec.sequence_number,
1861 buc_rec.days_start);
1862 dbms_sql.bind_variable(p_cursor, ':bucket_days_to_'||buc_rec.sequence_number,
1863 buc_rec.days_to);
1864 dbms_sql.bind_variable(p_cursor, ':bucket_line_type_'||buc_rec.sequence_number,
1865 buc_rec.type);
1866 dbms_sql.bind_variable(p_cursor, ':bucket_category_'||buc_rec.sequence_number,
1867 buc_rec.bucket_category);
1868 END LOOP;
1869
1870
1874
1871 IF PG_DEBUG in ('Y', 'C') THEN
1872 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.bind_bucket_parameters()-');
1873 END IF;
1875 EXCEPTION
1876 WHEN OTHERS THEN
1877 arp_standard.debug( 'Exception '||SQLERRM);
1878 arp_standard.debug( 'EXception AR_AGING_BUCKETS_PKG.bind_bucket_parameters()');
1879 RAISE;
1880 END bind_bucket_parameters;
1881
1882
1883
1884
1885 /*==========================================================================
1886 | PRIVATE PROCEDURE extract_aging_information |
1887 | |
1888 | DESCRIPTION |
1889 | Extracts the data using given query and populates the staging table |
1890 | |
1891 | |
1892 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
1893 | |
1894 | PARAMETERS |
1895 | NONE |
1896 | |
1897 | KNOWN ISSUES |
1898 | |
1899 | NOTES |
1900 | |
1901 | MODIFICATION HISTORY |
1902 | Date Author Description of Changes |
1903 | 10-JUL-2009 Naveen Prodduturi Created |
1904 *==========================================================================*/
1905 PROCEDURE extract_aging_information( p_qry_category IN VARCHAR2,
1906 p_in_report_query IN VARCHAR2) IS
1907 l_cursor INTEGER;
1908 l_rows_processed INTEGER;
1909 l_insert_stmt VARCHAR2(32000);
1910
1911 BEGIN
1912 IF PG_DEBUG in ('Y', 'C') THEN
1913 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.extract_aging_information()+');
1914 arp_standard.debug( 'p_qry_category :'||p_qry_category);
1915 END IF;
1916
1917 l_insert_stmt := '
1918 insert into ar_aging_extract
1919 ( customer_id,
1920 customer_number,
1921 short_customer_name,
1922 sort_field1,
1923 sort_field2,
1924 inv_tid_inv,
1925 contact_site_id,
1926 customer_state,
1927 customer_city,
1928 cust_acct_site_id,
1929 payment_schedule_id,
1930 class,
1931 due_date,
1932 amt_due_remaining,
1933 trx_number,
1934 days_past_due,
1935 amount_adjusted,
1936 amount_applied,
1937 amount_credited,
1938 gl_date,
1939 data_converted_flag,
1940 exchange_rate,
1941 contact_name,
1942 contact_phone,
1943 bucket_0,
1944 bucket_1,
1945 bucket_2,
1946 bucket_3,
1947 bucket_4,
1948 bucket_5,
1949 bucket_6,
1950 bal_segment_value,
1951 invoice_type,
1952 cons_billing_number,
1953 category,
1954 parent_request_id,
1955 worker_id)
1956 select customer_id,
1957 customer_number,
1958 short_customer_name,
1959 sort_field1,
1960 sort_field2,
1961 inv_tid_inv,
1962 contact_site_id,
1963 customer_state,
1964 customer_city,
1965 cust_acct_site_id,
1966 payment_schedule_id,
1967 class,
1968 due_date,
1969 amt_due_remaining,
1970 trx_number,
1971 days_past_due,
1972 amount_adjusted,
1973 amount_applied,
1974 amount_credited,
1975 gl_date,
1976 data_converted_flag,
1977 exchange_rate,
1978 AR_AGING_BUCKETS_PKG.get_contact_information( contact_site_id,
1979 ''NAME'') contact_name,
1980 AR_AGING_BUCKETS_PKG.get_contact_information( contact_site_id,
1981 ''PHONE'') contact_phone,
1982 bucket_0,
1983 bucket_1,
1984 bucket_2,
1985 bucket_3,
1986 bucket_4,
1987 bucket_5,
1988 bucket_6,
1989 bal_segment_value,
1990 invoice_type,
1991 cons_billing_number
1992 ,'''||p_qry_category||'''
1993 ,'||pg_parent_request_id||'
1994 ,'||pg_worker_id||'
1995 from (';
1996
1997 l_cursor := dbms_sql.open_cursor;
1998
1999 IF PG_DEBUG in ('Y', 'C') THEN
2000 arp_standard.debug( 'as_of_date '||pg_in_as_of_date_low);
2004 arp_standard.debug( 'pg_functional_currency '||pg_functional_currency);
2001 arp_standard.debug( 'pg_rep_type '||pg_rep_type);
2002 arp_standard.debug( 'pg_in_format_option_low '||pg_in_format_option_low);
2003 arp_standard.debug( 'pg_convert_flag '||pg_convert_flag);
2005 arp_standard.debug( 'worker_id '||pg_worker_id);
2006 arp_standard.debug( 'pg_parent_request_id '||pg_parent_request_id);
2007 END IF;
2008
2009
2010 dbms_sql.parse(l_cursor,l_insert_stmt||p_in_report_query||')',DBMS_SQL.NATIVE);
2011
2012 bind_bucket_parameters( l_cursor );
2013
2014 IF p_qry_category = AR_AGING_CTGRY_INVOICE THEN
2015 dbms_sql.bind_variable(l_cursor, ':pg_rep_type', pg_rep_type);
2016 END IF;
2017
2018 dbms_sql.bind_variable(l_cursor, ':as_of_date', pg_in_as_of_date_low);
2019
2020 --temporary,need to set the actual currency code
2021 dbms_sql.bind_variable(l_cursor, ':functional_currency', pg_functional_currency);
2022 dbms_sql.bind_variable(l_cursor, ':format_detailed', pg_in_format_option_low);
2023 dbms_sql.bind_variable(l_cursor, ':c_convert_flag', pg_convert_flag);
2024 dbms_sql.bind_variable(l_cursor, ':worker_id', pg_worker_id);
2025 dbms_sql.bind_variable(l_cursor, ':parent_request_id', pg_parent_request_id);
2026
2027 l_rows_processed := dbms_sql.execute(l_cursor);
2028
2029 IF PG_DEBUG in ('Y', 'C') THEN
2030 arp_standard.debug( 'Rows Processed '||l_rows_processed);
2031 END IF;
2032
2033 IF PG_DEBUG in ('Y', 'C') THEN
2034 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.extract_aging_information()-');
2035 END IF;
2036
2037 EXCEPTION
2038 WHEN OTHERS THEN
2039 arp_standard.debug( 'Exception '||SQLERRM);
2040 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.extract_aging_information()-');
2041 RAISE;
2042 END extract_aging_information;
2043
2044
2045
2046 /*==========================================================================
2047 | PRIVATE PROCEDURE build_select_stmt |
2048 | |
2052 | |
2049 | DESCRIPTION |
2050 | construct and return various queries to extract the aging info |
2051 | |
2053 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
2054 | |
2055 | PARAMETERS |
2056 | NONE |
2057 | |
2058 | KNOWN ISSUES |
2059 | |
2060 | NOTES |
2061 | |
2062 | MODIFICATION HISTORY |
2063 | Date Author Description of Changes |
2064 | 10-JUL-2009 Naveen Prodduturi Created |
2065 *==========================================================================*/
2066 PROCEDURE build_select_stmt( p_out_invoice_query OUT NOCOPY VARCHAR2,
2067 p_out_receipt_query OUT NOCOPY VARCHAR2,
2068 p_out_riskinfo_query OUT NOCOPY VARCHAR2,
2069 p_out_br_query OUT NOCOPY VARCHAR2) IS
2070
2071 l_inv_app_act_query VARCHAR2(32000);
2072 l_inv_act_sub_query VARCHAR2(32000);
2073 l_pmt_info_query VARCHAR2(32000);
2074 l_rep_specific_cols VARCHAR2(2000);
2075 l_rep_spec_sub_query VARCHAR2(2000);
2076 l_rep_spec_from_list VARCHAR2(2000);
2077 l_rep_spec_where_cls VARCHAR2(2000);
2078 l_rep_spec_grp_cols VARCHAR2(2000);
2079 l_accting_source VARCHAR2(30);
2080
2081 BEGIN
2082 IF PG_DEBUG in ('Y', 'C') THEN
2083 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.build_select_stmt()+');
2084 END IF;
2085
2086 get_report_specific_info( p_qry_category => AR_AGING_CTGRY_INVOICE,
2087 p_rep_specific_cols => l_rep_specific_cols,
2088 p_rep_from_info => l_rep_spec_from_list,
2089 p_rep_where_cls => l_rep_spec_where_cls,
2090 p_rep_spec_sub_query => l_rep_spec_sub_query,
2091 p_rep_spec_grp_cols => l_rep_spec_grp_cols);
2092
2093 l_inv_act_sub_query := '(
2094 SELECT a.customer_id,
2095 a.customer_site_use_id ,
2096 a.customer_trx_id,
2097 a.payment_schedule_id,
2098 a.class ,
2099 sum(a.primary_salesrep_id) primary_salesrep_id,
2103 a.amount_adjusted,
2100 a.due_date ,
2101 sum(a.amount_due_remaining) amt_due_remaining_inv,
2102 a.trx_number,
2104 a.amount_applied ,
2105 a.amount_credited ,
2106 a.amount_adjusted_pending,
2107 a.gl_date ,
2108 a.cust_trx_type_id,
2109 a.org_id,
2110 a.invoice_currency_code,
2111 a.exchange_rate,
2112 sum(a.cons_inv_id) cons_inv_id
2113 FROM
2114 ( SELECT /*HINT*/
2115 ps.customer_id,
2116 ps.customer_site_use_id ,
2117 ps.customer_trx_id,
2118 ps.payment_schedule_id,
2119 ps.class ,
2120 0 primary_salesrep_id,
2121 ps.due_date ,
2122 nvl(sum ( decode( :c_convert_flag, ''Y'',
2123 nvl(adj.acctd_amount, 0),
2124 adj.amount )
2125 ),0) * (-1) amount_due_remaining,
2126 ps.trx_number,
2127 ps.amount_adjusted ,
2128 ps.amount_applied ,
2129 ps.amount_credited ,
2130 ps.amount_adjusted_pending,
2131 ps.gl_date ,
2132 ps.cust_trx_type_id,
2133 ps.org_id,
2134 ps.invoice_currency_code,
2135 nvl(ps.exchange_rate,1) exchange_rate,
2136 0 cons_inv_id
2137 FROM ar_aging_payment_schedules aging,
2138 ar_payment_schedules ps,
2139 ar_adjustments adj
2140 WHERE aging.parent_request_id = :parent_request_id
2141 AND aging.worker_id = :worker_id
2142 AND aging.source_type = ''INV''
2143 AND aging.payment_schedule_id = ps.payment_schedule_id
2144 AND ps.gl_date <= :as_of_date
2145 AND ps.customer_id > 0
2146 AND ps.gl_date_closed > :as_of_date
2147 AND adj.payment_schedule_id = ps.payment_schedule_id
2148 AND adj.status = ''A''
2149 AND adj.gl_date > :as_of_date
2150 GROUP BY
2151 ps.customer_id,
2152 ps.customer_site_use_id ,
2153 ps.customer_trx_id,
2154 ps.class ,
2155 ps.due_date,
2156 ps.trx_number,
2157 ps.amount_adjusted ,
2158 ps.amount_applied ,
2159 ps.amount_credited ,
2160 ps.amount_adjusted_pending,
2161 ps.gl_date ,
2162 ps.cust_trx_type_id,
2163 ps.org_id,
2164 ps.invoice_currency_code,
2165 nvl(ps.exchange_rate,1),
2166 ps.payment_schedule_id
2167
2168 UNION ALL
2169
2170 SELECT /*HINT*/
2171 ps.customer_id,
2172 ps.customer_site_use_id ,
2173 ps.customer_trx_id,
2174 ps.payment_schedule_id,
2175 ps.class ,
2176 0 primary_salesrep_id,
2177 ps.due_date ,
2178 nvl(sum ( decode
2179 ( :c_convert_flag, ''Y'',
2180 (decode(ps.class, ''CM'',
2181 decode ( app.application_type, ''CM'',
2182 app.acctd_amount_applied_from,
2183 app.acctd_amount_applied_to
2184 ),
2185 app.acctd_amount_applied_to)+
2186 nvl(app.acctd_earned_discount_taken,0) +
2187 nvl(app.acctd_unearned_discount_taken,0))
2188 ,
2189 ( app.amount_applied +
2190 nvl(app.earned_discount_taken,0) +
2191 nvl(app.unearned_discount_taken,0) )
2192 ) *
2193 decode
2194 ( ps.class, ''CM'',
2195 decode(app.application_type, ''CM'', -1, 1), 1 )
2196 ), 0) amount_due_remaining_inv,
2197 ps.trx_number ,
2198 ps.amount_adjusted,
2199 ps.amount_applied ,
2200 ps.amount_credited ,
2201 ps.amount_adjusted_pending,
2202 ps.gl_date gl_date_inv,
2203 ps.cust_trx_type_id,
2204 ps.org_id,
2205 ps.invoice_currency_code,
2206 nvl(ps.exchange_rate, 1) exchange_rate,
2207 0 cons_inv_id
2208 FROM ar_aging_payment_schedules aging,
2209 ar_payment_schedules ps,
2210 ar_receivable_applications app
2211 WHERE aging.parent_request_id = :parent_request_id
2212 AND aging.worker_id = :worker_id
2213 AND aging.source_type = ''INV''
2214 AND aging.payment_schedule_id = ps.payment_schedule_id
2215 AND ps.gl_date <= :as_of_date
2216 AND ps.customer_id > 0
2220 app.payment_schedule_id = ps.payment_schedule_id)
2217 AND ps.gl_date_closed > :as_of_date
2218 AND (app.applied_payment_schedule_id = ps.payment_schedule_id
2219 OR
2221 AND app.status IN (''APP'', ''ACTIVITY'')
2222 AND nvl( app.confirmed_flag, ''Y'' ) = ''Y''
2223 AND app.gl_date > :as_of_date
2224 GROUP BY
2225 ps.customer_id,
2226 ps.customer_site_use_id ,
2227 ps.customer_trx_id,
2228 ps.class ,
2229 ps.due_date,
2230 ps.trx_number,
2231 ps.amount_adjusted ,
2232 ps.amount_applied ,
2236 ps.cust_trx_type_id,
2233 ps.amount_credited ,
2234 ps.amount_adjusted_pending,
2235 ps.gl_date ,
2237 ps.org_id,
2238 ps.invoice_currency_code,
2239 nvl(ps.exchange_rate, 1),
2240 ps.payment_schedule_id
2241
2242 UNION ALL
2243
2244 SELECT /*HINT*/
2245 ps.customer_id,
2246 ps.customer_site_use_id ,
2247 ps.customer_trx_id,
2248 ps.payment_schedule_id,
2249 ps.class class_inv,
2250 nvl(ct.primary_salesrep_id, -3) primary_salesrep_id,
2251 ps.due_date due_date_inv,
2252 decode( :c_convert_flag, ''Y'',
2253 ps.acctd_amount_due_remaining,
2254 ps.amount_due_remaining) amt_due_remaining_inv,
2255 ps.trx_number,
2256 ps.amount_adjusted ,
2257 ps.amount_applied ,
2258 ps.amount_credited ,
2259 ps.amount_adjusted_pending,
2260 ps.gl_date ,
2261 ps.cust_trx_type_id,
2262 ps.org_id,
2263 ps.invoice_currency_code,
2264 nvl(ps.exchange_rate, 1) exchange_rate,
2265 ps.cons_inv_id
2266 FROM ar_aging_payment_schedules aging,
2267 ar_payment_schedules ps,
2268 ra_customer_trx ct
2269 WHERE aging.parent_request_id = :parent_request_id
2270 AND aging.worker_id = :worker_id
2271 AND aging.source_type = ''INV''
2272 AND aging.payment_schedule_id = ps.payment_schedule_id
2273 AND ps.gl_date <= :as_of_date
2274 AND ps.gl_date_closed > :as_of_date
2275 AND ps.customer_trx_id = ct.customer_trx_id
2276 AND DECODE(:pg_rep_type,''ARXAGR'',ps.class,''NULL'') <> ''CB''
2277 '||nvl(l_rep_spec_sub_query,CHR(0)) || '
2278 ) a
2279 GROUP BY a.customer_id,
2280 a.customer_site_use_id ,
2281 a.customer_trx_id,
2282 a.payment_schedule_id,
2283 a.class ,
2284 a.due_date ,
2285 a.trx_number,
2286 a.amount_adjusted,
2287 a.amount_applied ,
2288 a.amount_credited ,
2289 a.amount_adjusted_pending,
2290 a.gl_date ,
2291 a.cust_trx_type_id,
2292 a.org_id,
2293 a.invoice_currency_code,
2294 a.exchange_rate) ps, ';
2295
2296 l_inv_act_sub_query := REPLACE( l_inv_act_sub_query ,'/*HINT*/','/*+ LEADING(aging) */');
2297
2298 l_inv_app_act_query := '
2299 select /*+ LEADING(ps) */
2300 nvl(cust_acct.cust_account_id,-999) customer_id,
2301 cust_acct.account_number customer_number,
2302 substrb(party.party_name,1,50) short_customer_name,
2303 arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id) sort_field2,
2304 site.site_use_id contact_site_id,
2305 loc.state customer_state,
2306 loc.city customer_city,
2307 decode(:format_detailed,NULL,-1,acct_site.cust_acct_site_id) cust_acct_site_id,
2308 ps.payment_schedule_id payment_schedule_id,
2309 ps.class class,
2310 ps.due_date due_date,
2311 amt_due_remaining_inv amt_due_remaining,
2312 ps.trx_number trx_number,
2313 ceil(:as_of_date - ps.due_date) days_past_due,
2314 ps.amount_adjusted amount_adjusted,
2315 ps.amount_applied amount_applied,
2316 ps.amount_credited amount_credited,
2317 ps.gl_date gl_date,
2318 decode(ps.invoice_currency_code, :functional_currency, NULL,
2319 decode(ps.exchange_rate, NULL, ''*'', NULL)) data_converted_flag,
2320 nvl(ps.exchange_rate, 1) exchange_rate,
2321 arpt_sql_func_util.bucket_function(:bucket_line_type_0,
2322 dh.amount_in_dispute,ps.amount_adjusted_pending,
2323 :bucket_days_from_0,:bucket_days_to_0,
2324 ps.due_date,:bucket_category_0,:as_of_date) bucket_0,
2325 arpt_sql_func_util.bucket_function(:bucket_line_type_1,
2326 dh.amount_in_dispute,ps.amount_adjusted_pending,
2327 :bucket_days_from_1,:bucket_days_to_1,
2328 ps.due_date,:bucket_category_1,:as_of_date) bucket_1,
2329 arpt_sql_func_util.bucket_function(:bucket_line_type_2,
2330 dh.amount_in_dispute,ps.amount_adjusted_pending,
2331 :bucket_days_from_2,:bucket_days_to_2,
2332 ps.due_date,:bucket_category_2,:as_of_date) bucket_2,
2333 arpt_sql_func_util.bucket_function(:bucket_line_type_3,
2334 dh.amount_in_dispute,ps.amount_adjusted_pending,
2335 :bucket_days_from_3,:bucket_days_to_3,
2336 ps.due_date,:bucket_category_3,:as_of_date) bucket_3,
2337 arpt_sql_func_util.bucket_function(:bucket_line_type_4,
2338 dh.amount_in_dispute,ps.amount_adjusted_pending,
2339 :bucket_days_from_4,:bucket_days_to_4,
2340 ps.due_date,:bucket_category_4,:as_of_date) bucket_4,
2341 arpt_sql_func_util.bucket_function(:bucket_line_type_5,
2342 dh.amount_in_dispute,ps.amount_adjusted_pending,
2343 :bucket_days_from_5,:bucket_days_to_5,
2344 ps.due_date,:bucket_category_5,:as_of_date) bucket_5,
2345 arpt_sql_func_util.bucket_function(:bucket_line_type_6,
2346 dh.amount_in_dispute,ps.amount_adjusted_pending,
2347 :bucket_days_from_6,:bucket_days_to_6,
2348 ps.due_date,:bucket_category_6,:as_of_date) bucket_6, '||pg_acct_flex_bal_seg||'
2349 bal_segment_value,
2350 arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id)
2351 invoice_type '|| l_rep_specific_cols || '
2352 from '||l_inv_act_sub_query|| '
2353 hz_cust_accounts cust_acct,
2354 hz_parties party,
2355 hz_cust_site_uses site,
2356 hz_cust_acct_sites acct_site,
2357 hz_party_sites party_site,
2358 hz_locations loc,
2359 ra_cust_trx_line_gl_dist gld,
2360 ar_dispute_history dh,
2361 gl_code_combinations c '||l_rep_spec_from_list ||'
2362 where ps.customer_site_use_id = site.site_use_id
2363 and ps.customer_id = cust_acct.cust_account_id
2364 and ps.customer_trx_id = gld.customer_trx_id
2368 and gld.account_class = ''REC''
2365 and site.cust_acct_site_id = acct_site.cust_acct_site_id
2366 and acct_site.party_site_id = party_site.party_site_id
2367 and loc.location_id = party_site.location_id
2369 and gld.latest_rec_flag = ''Y''
2370 and ps.payment_schedule_id = dh. payment_schedule_id(+)
2371 and :as_of_date >= nvl(dh.start_date(+), :as_of_date)
2372 and :as_of_date < nvl(dh.end_date(+), :as_of_date + 1)
2373 and cust_acct.party_id = party.party_id '||l_rep_spec_where_cls;
2374
2375 p_out_invoice_query := l_inv_app_act_query;
2376
2377 get_report_specific_info( p_qry_category => AR_AGING_CTGRY_RECEIPT,
2378 p_rep_specific_cols => l_rep_specific_cols,
2379 p_rep_from_info => l_rep_spec_from_list,
2380 p_rep_where_cls => l_rep_spec_where_cls,
2381 p_rep_spec_sub_query => l_rep_spec_sub_query,
2382 p_rep_spec_grp_cols => l_rep_spec_grp_cols);
2383
2384 l_pmt_info_query := '
2385 select /*+ LEADING(aging) */
2386 substrb(nvl(party.party_name, '''||pg_short_unid_phrase||'''),1,50) short_customer_name,
2387 cust_acct.account_number customer_number,
2388 site.site_use_id contact_site_id,
2389 loc.state customer_state,
2390 loc.city customer_city,
2391 decode(:format_detailed,NULL,-1,acct_site.cust_acct_site_id) cust_acct_site_id,
2392 nvl(cust_acct.cust_account_id, -999) customer_id,
2393 ps.payment_schedule_id payment_schedule_id,
2394 DECODE(app.applied_payment_schedule_id,-4,''CLAIM'',ps.class) class,
2395 ps.due_date due_date,
2396 decode ( :c_convert_flag, ''Y'', nvl(-sum(app.acctd_amount_applied_from),0) ,
2397 nvl(-sum(app.amount_applied),0)) amt_due_remaining,
2398 ps.trx_number trx_number,
2399 ceil(:as_of_date - ps.due_date) days_past_due,
2400 ps.amount_adjusted amount_adjusted,
2401 ps.amount_applied amount_applied,
2402 ps.amount_credited amount_credited,
2403 ps.gl_date gl_date,
2404 decode(ps.invoice_currency_code, :functional_currency, NULL,
2405 decode(ps.exchange_rate, NULL, ''*'', NULL) ) data_converted_flag,
2406 nvl(ps.exchange_rate, 1) exchange_rate,
2407 arpt_sql_func_util.bucket_function(:bucket_line_type_0,
2408 ps.amount_in_dispute,ps.amount_adjusted_pending,
2409 :bucket_days_from_0,:bucket_days_to_0,
2410 ps.due_date,:bucket_category_0,:as_of_date) bucket_0,
2411 arpt_sql_func_util.bucket_function(:bucket_line_type_1,
2412 ps.amount_in_dispute,ps.amount_adjusted_pending,
2413 :bucket_days_from_1,:bucket_days_to_1,
2414 ps.due_date,:bucket_category_1,:as_of_date) bucket_1,
2415 arpt_sql_func_util.bucket_function(:bucket_line_type_2,
2416 ps.amount_in_dispute,ps.amount_adjusted_pending,
2417 :bucket_days_from_2,:bucket_days_to_2,
2418 ps.due_date,:bucket_category_2,:as_of_date) bucket_2,
2419 arpt_sql_func_util.bucket_function(:bucket_line_type_3,
2420 ps.amount_in_dispute,ps.amount_adjusted_pending,
2421 :bucket_days_from_3,:bucket_days_to_3,
2422 ps.due_date,:bucket_category_3,:as_of_date) bucket_3,
2423 arpt_sql_func_util.bucket_function(:bucket_line_type_4,
2424 ps.amount_in_dispute,ps.amount_adjusted_pending,
2425 :bucket_days_from_4,:bucket_days_to_4,
2426 ps.due_date,:bucket_category_4,:as_of_date) bucket_4,
2430 ps.due_date,:bucket_category_5,:as_of_date) bucket_5,
2427 arpt_sql_func_util.bucket_function(:bucket_line_type_5,
2428 ps.amount_in_dispute,ps.amount_adjusted_pending,
2429 :bucket_days_from_5,:bucket_days_to_5,
2431 arpt_sql_func_util.bucket_function(:bucket_line_type_6,
2432 ps.amount_in_dispute,ps.amount_adjusted_pending,
2433 :bucket_days_from_6,:bucket_days_to_6,
2434 ps.due_date,:bucket_category_6,:as_of_date) bucket_6,
2435 '||pg_acct_flex_bal_seg||'
2436 bal_segment_value,
2437 '''|| pg_payment_meaning ||''' sort_field2,
2438 '''|| pg_payment_meaning ||''' invoice_type '|| l_rep_specific_cols ||'
2439 from hz_cust_accounts cust_acct,
2440 hz_parties party,
2441 ar_aging_payment_schedules aging,
2442 ar_payment_schedules ps,
2443 hz_cust_site_uses site,
2444 hz_cust_acct_sites acct_site,
2445 hz_party_sites party_site,
2446 hz_locations loc,
2447 ar_receivable_applications app,
2448 gl_code_combinations c '||l_rep_spec_from_list ||'
2449 where aging.parent_request_id = :parent_request_id
2450 AND aging.worker_id = :worker_id
2451 AND aging.source_type = ''RA''
2452 AND aging.payment_schedule_id = ps.payment_schedule_id
2453 AND app.gl_date <= :as_of_date
2454 and ps.trx_number is not null
2455 and ps.customer_id = cust_acct.cust_account_id(+)
2456 and cust_acct.party_id = party.party_id (+)
2457 and ps.cash_receipt_id = app.cash_receipt_id
2458 and app.code_combination_id = c.code_combination_id
2459 and app.status in ( ''ACC'', ''UNAPP'', ''UNID'',''OTHER ACC'')
2460 and nvl(app.confirmed_flag, ''Y'') = ''Y''
2461 and ps.customer_site_use_id = site.site_use_id(+)
2462 and site.cust_acct_site_id = acct_site.cust_acct_site_id(+)
2463 and acct_site.party_site_id = party_site.party_site_id(+)
2464 and loc.location_id(+) = party_site.location_id
2465 and ps.gl_date_closed > :as_of_date
2466 and ((app.reversal_gl_date is not null AND
2467 ps.gl_date <= :as_of_date) OR
2468 app.reversal_gl_date is null )
2469 and nvl( ps.receipt_confirmed_flag, ''Y'' ) = ''Y''
2470 '||l_rep_spec_where_cls||'
2471 GROUP BY party.party_name,
2472 cust_acct.account_number,
2473 site.site_use_id,
2474 loc.state,
2475 loc.city,
2476 acct_site.cust_acct_site_id,
2477 cust_acct.cust_account_id,
2478 ps.payment_schedule_id,
2479 ps.due_date,
2480 ps.trx_number,
2481 ps.amount_adjusted,
2482 ps.amount_applied,
2483 ps.amount_credited,
2484 ps.gl_date,
2485 ps.amount_in_dispute,
2486 ps.amount_adjusted_pending,
2487 ps.invoice_currency_code,
2488 ps.exchange_rate,
2489 DECODE(app.applied_payment_schedule_id,-4,''CLAIM'',ps.class),
2490 '||pg_acct_flex_bal_seg||',
2491 decode( app.status, ''UNID'', ''UNID'',''OTHER ACC'',''OTHER ACC'',''UNAPP''),
2492 '''|| pg_payment_meaning||''''|| l_rep_spec_grp_cols;
2493
2494 p_out_receipt_query := l_pmt_info_query;
2495
2496 get_report_specific_info( p_qry_category => AR_AGING_CTGRY_RISK,
2497 p_rep_specific_cols => l_rep_specific_cols,
2498 p_rep_from_info => l_rep_spec_from_list,
2499 p_rep_where_cls => l_rep_spec_where_cls,
2500 p_rep_spec_sub_query => l_rep_spec_sub_query,
2501 p_rep_spec_grp_cols => l_rep_spec_grp_cols);
2502
2503 p_out_riskinfo_query := '
2504 select /*+ LEADING(aging) */
2505 substrb(nvl(party.party_name, '''||pg_short_unid_phrase||'''),1,50) short_customer_name,
2506 cust_acct.account_number customer_number,
2507 site.site_use_id contact_site_id,
2511 nvl(cust_acct.cust_account_id, -999) customer_id,
2508 loc.state customer_state,
2509 loc.city customer_city,
2510 decode(:format_detailed,NULL,-1,acct_site.cust_acct_site_id) cust_acct_site_id,
2512 ps.payment_schedule_id payment_schedule_id,
2513 '''|| pg_risk_meaning ||''' class,
2514 ps.due_date due_date ,
2515 decode( :c_convert_flag, ''Y'', crh.acctd_amount, crh.amount) amt_due_remaining,
2516 ps.trx_number trx_number,
2517 ceil(:as_of_date - ps.due_date) days_past_due,
2518 ps.amount_adjusted amount_adjusted,
2519 ps.amount_applied amount_applied,
2520 ps.amount_credited amount_credited,
2521 crh.gl_date gl_date,
2522 decode(ps.invoice_currency_code, :functional_currency, NULL,
2523 decode(crh.exchange_rate, NULL, ''*'', NULL)) data_converted_flag,
2527 ps.due_date,:bucket_category_0,:as_of_date) bucket_0,
2524 nvl(crh.exchange_rate, 1) exchange_rate,
2525 arpt_sql_func_util.bucket_function(:bucket_line_type_0,
2526 0,0,:bucket_days_from_0,:bucket_days_to_0,
2528 arpt_sql_func_util.bucket_function(:bucket_line_type_1,
2529 0,0,:bucket_days_from_1,:bucket_days_to_1,
2530 ps.due_date,:bucket_category_1,:as_of_date) bucket_1,
2531 arpt_sql_func_util.bucket_function(:bucket_line_type_2,
2532 0,0,:bucket_days_from_2,:bucket_days_to_2,
2533 ps.due_date,:bucket_category_2,:as_of_date) bucket_2,
2534 arpt_sql_func_util.bucket_function(:bucket_line_type_3,
2535 0,0,:bucket_days_from_3,:bucket_days_to_3,
2536 ps.due_date,:bucket_category_3,:as_of_date) bucket_3,
2537 arpt_sql_func_util.bucket_function(:bucket_line_type_4,
2538 0,0,:bucket_days_from_4,:bucket_days_to_4,
2539 ps.due_date,:bucket_category_4,:as_of_date) bucket_4,
2540 arpt_sql_func_util.bucket_function(:bucket_line_type_5,
2541 0,0,:bucket_days_from_5,:bucket_days_to_5,
2542 ps.due_date,:bucket_category_5,:as_of_date) bucket_5,
2543 arpt_sql_func_util.bucket_function(:bucket_line_type_6,
2544 0,0,:bucket_days_from_6,:bucket_days_to_6,
2545 ps.due_date,:bucket_category_6,:as_of_date) bucket_6,
2546 '||pg_acct_flex_bal_seg||'
2547 bal_segment_value,
2548 '''|| pg_risk_meaning ||''' sort_field2,
2549 '''|| pg_risk_meaning ||''' invoice_type '|| l_rep_specific_cols ||'
2550 from hz_cust_accounts cust_acct,
2551 hz_parties party,
2552 ar_aging_payment_schedules aging,
2553 ar_payment_schedules ps,
2554 hz_cust_site_uses site,
2555 hz_cust_acct_sites acct_site,
2556 hz_party_sites party_site,
2557 hz_locations loc,
2558 ar_cash_receipts cr,
2559 ar_cash_receipt_history crh,
2560 gl_code_combinations c '||l_rep_spec_from_list ||'
2561 where aging.parent_request_id = :parent_request_id
2562 and aging.worker_id = :worker_id
2563 and aging.source_type = ''CRH''
2564 and aging.payment_schedule_id = ps.payment_schedule_id
2565 and crh.gl_date <= :as_of_date
2566 and ps.trx_number is not null
2567 and ps.customer_id = cust_acct.cust_account_id(+)
2568 and cust_acct.party_id = party.party_id(+)
2569 and ps.cash_receipt_id = cr.cash_receipt_id
2570 and cr.cash_receipt_id = crh.cash_receipt_id
2571 and crh.account_code_combination_id = c.code_combination_id
2572 and ps.customer_site_use_id = site.site_use_id(+)
2573 and site.cust_acct_site_id = acct_site.cust_acct_site_id(+)
2574 and acct_site.party_site_id = party_site.party_site_id(+)
2575 and loc.location_id(+) = party_site.location_id
2576 and ( crh.current_record_flag = ''Y''
2577 or crh.reversal_gl_date > :as_of_date )
2578 and crh.status not in ( decode(crh.factor_flag,
2579 ''Y'',''RISK_ELIMINATED'',
2580 ''N'',''CLEARED''),
2581 ''REVERSED'')
2582 and not exists (select ''x''
2583 from ar_receivable_applications ra
2584 where ra.cash_receipt_id = cr.cash_receipt_id
2585 and ra.status = ''ACTIVITY''
2586 and applied_payment_schedule_id = -2)
2587 '||l_rep_spec_where_cls;
2588
2589
2590 IF pg_rep_type = 'ARXAGF' then
2591 l_accting_source := 'ar_xla_ard_lines_v ';
2592 ELSE
2593 l_accting_source := 'ar_distributions ';
2594 END IF;
2595
2596 get_report_specific_info( p_qry_category => AR_AGING_CTGRY_BR,
2597 p_rep_specific_cols => l_rep_specific_cols,
2598 p_rep_from_info => l_rep_spec_from_list,
2599 p_rep_where_cls => l_rep_spec_where_cls,
2600 p_rep_spec_sub_query => l_rep_spec_sub_query,
2601 p_rep_spec_grp_cols => l_rep_spec_grp_cols);
2602
2603 p_out_br_query := '
2604 select /*+ LEADING(aging) */
2605 substrb(party.party_name,1,50) short_customer_name,
2606 cust_acct.account_number customer_number,
2607 arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id) sort_field2,
2608 site.site_use_id contact_site_id,
2609 loc.state customer_state,
2610 loc.city customer_city,
2611 decode(:format_detailed,NULL,-1,acct_site.cust_acct_site_id) cust_acct_site_id,
2612 nvl(cust_acct.cust_account_id,-999) customer_id,
2613 ps.payment_schedule_id payment_schedule_id,
2614 ps.class class,
2615 ps.due_date due_date,
2616 decode( :c_convert_flag, ''Y'',
2617 ps.acctd_amount_due_remaining,
2618 ps.amount_due_remaining) amt_due_remaining,
2619 ps.trx_number trx_number,
2620 ceil(:as_of_date - ps.due_date) days_past_due,
2621 ps.amount_adjusted amount_adjusted,
2622 ps.amount_applied amount_applied,
2623 ps.amount_credited amount_credited,
2624 ps.gl_date gl_date,
2625 decode(ps.invoice_currency_code, :functional_currency, NULL,
2626 decode(ps.exchange_rate, NULL, ''*'', NULL)) data_converted_flag,
2627 nvl(ps.exchange_rate, 1) exchange_rate,
2628 arpt_sql_func_util.bucket_function(:bucket_line_type_0,
2629 ps.amount_in_dispute,ps.amount_adjusted_pending,
2630 :bucket_days_from_0,:bucket_days_to_0,
2631 ps.due_date,:bucket_category_0,:as_of_date) bucket_0,
2632 arpt_sql_func_util.bucket_function(:bucket_line_type_1,
2633 ps.amount_in_dispute,ps.amount_adjusted_pending,
2634 :bucket_days_from_1,:bucket_days_to_1,
2635 ps.due_date,:bucket_category_1,:as_of_date) bucket_1,
2636 arpt_sql_func_util.bucket_function(:bucket_line_type_2,
2642 :bucket_days_from_3,:bucket_days_to_3,
2637 ps.amount_in_dispute,ps.amount_adjusted_pending,
2638 :bucket_days_from_2,:bucket_days_to_2,
2639 ps.due_date,:bucket_category_2,:as_of_date) bucket_2,
2640 arpt_sql_func_util.bucket_function(:bucket_line_type_3,
2641 ps.amount_in_dispute,ps.amount_adjusted_pending,
2643 ps.due_date,:bucket_category_3,:as_of_date) bucket_3,
2644 arpt_sql_func_util.bucket_function(:bucket_line_type_4,
2645 ps.amount_in_dispute,ps.amount_adjusted_pending,
2646 :bucket_days_from_4,:bucket_days_to_4,
2647 ps.due_date,:bucket_category_4,:as_of_date) bucket_4,
2648 arpt_sql_func_util.bucket_function(:bucket_line_type_5,
2649 ps.amount_in_dispute,ps.amount_adjusted_pending,
2650 :bucket_days_from_5,:bucket_days_to_5,
2651 ps.due_date,:bucket_category_5,:as_of_date) bucket_5,
2652 arpt_sql_func_util.bucket_function(:bucket_line_type_6,
2653 ps.amount_in_dispute,ps.amount_adjusted_pending,
2654 :bucket_days_from_6,:bucket_days_to_6,
2655 ps.due_date,:bucket_category_6,:as_of_date) bucket_6,
2656 '||pg_acct_flex_bal_seg||'
2657 bal_segment_value,
2658 arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id) invoice_type
2659 '|| l_rep_specific_cols ||'
2660 from hz_cust_accounts cust_acct,
2661 hz_parties party,
2662 ar_aging_payment_schedules aging,
2663 ar_payment_schedules ps,
2664 hz_cust_site_uses site,
2665 hz_cust_acct_sites acct_site,
2666 hz_party_sites party_site,
2667 hz_locations loc,
2668 ar_transaction_history th,
2669 gl_code_combinations c,
2670 '||l_accting_source ||' dist '||l_rep_spec_from_list ||'
2671 where aging.parent_request_id = :parent_request_id
2672 and aging.worker_id = :worker_id
2673 and aging.source_type = ''INV''
2674 and aging.payment_schedule_id = ps.payment_schedule_id
2675 and ps.gl_date <= :as_of_date
2676 and ps.customer_site_use_id = site.site_use_id
2677 and site.cust_acct_site_id = acct_site.cust_acct_site_id
2678 and acct_site.party_site_id = party_site.party_site_id
2679 and loc.location_id = party_site.location_id
2680 and ps.gl_date_closed > :as_of_date
2681 and ps.class = ''BR''
2682 and th.transaction_history_id = dist.source_id
2683 and ps.customer_id=cust_acct.cust_account_id
2684 and ps.customer_trx_id = th.customer_trx_id
2685 and dist.source_table = ''TH''
2686 and dist.amount_dr is not null
2687 and dist.source_table_secondary is NULL
2688 and dist.code_combination_id = c.code_combination_id
2689 and cust_acct.party_id = party.party_id
2690 and th.transaction_history_id =
2691 (select max(transaction_history_id)
2692 from ar_transaction_history th2,
2693 '||l_accting_source ||' dist2
2694 where th2.transaction_history_id = dist2.source_id
2695 and dist2.source_table = ''TH''
2696 and th2.gl_date <= :as_of_date
2697 and dist2.amount_dr is not null
2698 and th2.customer_trx_id = ps.customer_trx_id)';
2699
2700 IF PG_DEBUG in ('Y', 'C') THEN
2701 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.build_select_stmt()-');
2702 END IF;
2703
2704 EXCEPTION
2705 WHEN OTHERS THEN
2706 IF PG_DEBUG in ('Y', 'C') THEN
2707 arp_standard.debug( 'Exception AR_AGING_BUCKETS_PKG.build_select_stmt()');
2708 arp_standard.debug( 'Exception message '||SQLERRM);
2709 END IF;
2710 RAISE;
2711 END build_select_stmt;
2712
2713
2714
2715
2716 /*==========================================================================
2717 | PRIVATE PROCEDURE prorate_aging_balances_mfar |
2718 | |
2719 | DESCRIPTION |
2720 | query and prorate the information to each receivable account of |
2721 | given documents |
2722 | |
2723 | Procedure does the following |
2724 | |
2725 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
2726 | |
2727 | PARAMETERS |
2728 | NONE |
2729 | |
2733 | |
2730 | KNOWN ISSUES |
2731 | |
2732 | NOTES |
2734 | MODIFICATION HISTORY |
2735 | Date Author Description of Changes |
2736 | 10-JUL-2009 Naveen Prodduturi Created |
2737 *==========================================================================*/
2738 PROCEDURE prorate_aging_balances_mfar(p_category VARCHAR2) IS
2739
2740 CURSOR aging_ps_cur IS
2741 select /*+leading(age_ps) */
2742 pg_parent_request_id,
2743 payment_schedule_id,
2744 code_combination_id,
2745 currency_code,
2746 rec_amount,
2747 0 rec_aging_amount,
2748 SUM(rec_amount) OVER (PARTITION BY payment_schedule_id) receivable_total,
2749 amt_due_remaining,
2750 p_category category
2751 from
2752 ( select /*+ leading(age_ps)*/
2753 ae.code_combination_id,
2754 sum(nvl(xdl.unrounded_entered_dr,0)-nvl(xdl.unrounded_entered_cr,0)) rec_amount,
2755 age_ps.payment_schedule_id,
2756 age_ps.amt_due_remaining,
2757 ae.currency_code
2758 from xla_ae_headers hdr,
2759 xla_ae_lines ae,
2760 xla_distribution_links xdl,
2761 ( select /*+ leading(ext) index(ra AR_RECEIVABLE_APPLICATIONS_N3)*/
2762 ra.event_id,
2763 ext.payment_schedule_id,
2764 ext.amt_due_remaining,
2765 'RA_APPLIED_FROM' source_identifier
2766 from ar_aging_extract ext,
2767 ar_receivable_applications ra
2768 where ext.parent_request_id = pg_parent_request_id
2769 and ext.worker_id = pg_worker_id
2770 and ext.payment_schedule_id = ra.payment_schedule_id
2771 and ra.gl_date <= pg_in_as_of_date_low
2772 and ra.status in ('APP','ACTIVITY')
2773 group by ra.event_id,
2774 ext.payment_schedule_id,
2775 ext.amt_due_remaining
2776
2777 UNION ALL
2778
2782 ext.amt_due_remaining,
2779 select /*+ leading(ext) index(ra AR_RECEIVABLE_APPLICATIONS_N8)*/
2780 ra.event_id,
2781 ext.payment_schedule_id,
2783 'RA_APPLIED_TO' source_identifier
2784 from ar_aging_extract ext,
2785 ar_receivable_applications ra
2786 where ext.parent_request_id = pg_parent_request_id
2787 and ext.worker_id = pg_worker_id
2788 and ext.payment_schedule_id = ra.applied_payment_schedule_id
2789 and ra.gl_date <= pg_in_as_of_date_low
2790 and ra.status in ('APP','ACTIVITY')
2791 group by ra.event_id,
2792 ext.payment_schedule_id,
2793 ext.amt_due_remaining
2794
2795 UNION ALL
2796
2797 select /*+ leading(ext) index(adj AR_ADJUSTMENTS_N3)*/
2798 adj.event_id,
2799 ext.payment_schedule_id,
2800 ext.amt_due_remaining,
2801 'ADJ' source_identifier
2802 from ar_aging_extract ext,
2803 ar_adjustments adj
2804 where ext.parent_request_id = pg_parent_request_id
2805 and ext.worker_id = pg_worker_id
2806 and ext.payment_schedule_id = adj.payment_schedule_id
2807 and adj.gl_date <= pg_in_as_of_date_low
2808 and nvl(postable,'Y') = 'Y'
2809 group by adj.event_id,
2810 ext.payment_schedule_id,
2811 ext.amt_due_remaining
2812
2813 UNION ALL
2814
2815 select /*+ leading(ext) index(ctlgd RA_CUST_TRX_LINE_GL_DIST_N6)*/
2816 ctlgd.event_id,
2817 ext.payment_schedule_id,
2818 ext.amt_due_remaining,
2819 'CTLGD' source_identifier
2820 from ar_aging_extract ext,
2821 ar_payment_schedules ps,
2822 ra_cust_trx_line_gl_dist ctlgd
2823 where ext.parent_request_id = pg_parent_request_id
2824 and ext.worker_id = pg_worker_id
2825 and ext.payment_schedule_id = ps.payment_schedule_id
2826 and ps.customer_trx_id = ctlgd.customer_trx_id
2827 and ctlgd.gl_date <= pg_in_as_of_date_low
2828 group by ctlgd.event_id,
2829 ext.payment_schedule_id,
2830 ext.amt_due_remaining
2831
2832 ) age_ps
2833 where hdr.application_id = 222
2834 and ae.application_id = 222
2835 and xdl.application_id = 222
2836 and hdr.ledger_id = pg_set_of_books_id
2837 and ae.ae_header_id = hdr.ae_header_id
2838 and ae.accounting_class_code = 'RECEIVABLE'
2839 and hdr.accounting_entry_status_code = 'F'
2840 and hdr.event_id = age_ps.event_id
2841 and xdl.ae_header_id = hdr.ae_header_id
2842 and xdl.event_id = hdr.event_id
2846 group by ae.code_combination_id,
2843 and xdl.ae_line_num = ae.ae_line_num
2844 and ( age_ps.source_identifier <> 'RA_APPLIED_TO' OR
2845 xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL') --to restrict CM accounting records
2847 age_ps.payment_schedule_id,
2848 age_ps.amt_due_remaining,
2849 ae.currency_code
2850 order by payment_schedule_id
2851 );
2852
2853 l_aging_mfar_tab AR_AGING_BUCKETS_PKG.AGING_MFAR_TAB;
2854 l_run_alloc_tot NUMBER;
2855 l_alloc_amt NUMBER;
2856 l_run_rec_tot NUMBER;
2857 l_pror_identifier NUMBER(15);
2858
2859 BEGIN
2860 IF PG_DEBUG in ('Y', 'C') THEN
2861 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.prorate_aging_balances_mfar()+');
2862 arp_standard.debug( 'p_category '||p_category);
2863 END IF;
2864
2865 OPEN aging_ps_cur;
2866
2867 LOOP
2868 FETCH aging_ps_cur BULK COLLECT INTO l_aging_mfar_tab LIMIT MAX_ARRAY_SIZE;
2869
2870 IF PG_DEBUG in ('Y', 'C') THEN
2871 arp_standard.debug( 'current fetch count '|| l_aging_mfar_tab.count);
2872 END IF;
2873
2874 EXIT WHEN l_aging_mfar_tab.count = 0;
2875
2876 --loop over the array and prorate amounts
2877 FOR i IN l_aging_mfar_tab.FIRST..l_aging_mfar_tab.LAST LOOP
2878
2879 IF NVL(l_pror_identifier,-9999) <> l_aging_mfar_tab(i).payment_schedule_id THEN
2880 l_run_alloc_tot := 0;
2881 l_run_rec_tot := 0;
2882 l_pror_identifier := l_aging_mfar_tab(i).payment_schedule_id;
2883 END IF;
2884
2885 IF l_aging_mfar_tab(i).rec_amount <> 0 AND
2886 l_aging_mfar_tab(i).amt_due_remaining <>0 THEN
2887 l_run_rec_tot := l_run_rec_tot + l_aging_mfar_tab(i).rec_amount;
2888 l_alloc_amt := ar_unposted_item_util.CurrRound(
2889 (l_run_rec_tot / l_aging_mfar_tab(i).receivable_total) *
2890 l_aging_mfar_tab(i).amt_due_remaining - l_run_alloc_tot,
2891 l_aging_mfar_tab(i).currency_code );
2892
2893 l_aging_mfar_tab(i).rec_aging_amount := l_alloc_amt;
2894 l_run_alloc_tot := l_run_alloc_tot + l_alloc_amt;
2895 END IF;
2896
2897 IF PG_DEBUG in ('Y', 'C') THEN
2898 arp_standard.debug('l_alloc_amt '|| l_alloc_amt);
2899 arp_standard.debug('l_run_alloc_tot '|| l_run_alloc_tot);
2900 arp_standard.debug('l_run_rec_tot '|| l_run_rec_tot);
2901 arp_standard.debug('l_pror_identifier '|| l_pror_identifier);
2902 arp_standard.debug('payment_schedule_id '|| l_aging_mfar_tab(i).payment_schedule_id);
2903 END IF;
2904
2905 END LOOP;
2906
2907 FORALL i IN l_aging_mfar_tab.FIRST..l_aging_mfar_tab.LAST
2908 INSERT INTO ar_aging_mfar_extract
2909 VALUES l_aging_mfar_tab(i);
2910
2911 EXIT WHEN aging_ps_cur%NOTFOUND;
2912 END LOOP;
2913
2914 IF PG_DEBUG in ('Y', 'C') THEN
2915 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.prorate_aging_balances_mfar()-');
2916 END IF;
2917
2918 EXCEPTION
2919 WHEN OTHERS THEN
2920 IF PG_DEBUG in ('Y', 'C') THEN
2921 arp_standard.debug( 'Exception message '||SQLERRM);
2922 arp_standard.debug( 'Exception AR_AGING_BUCKETS_PKG.prorate_aging_balances_mfar()');
2923 END IF;
2924 RAISE;
2925 END prorate_aging_balances_mfar;
2926
2927
2928
2929
2930 /*==========================================================================
2931 | PRIVATE PROCEDURE generate_xml |
2932 | |
2933 | DESCRIPTION |
2934 | generates XML out of making use of input query |
2935 | |
2936 | |
2937 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
2938 | |
2939 | PARAMETERS |
2943 | KNOWN ISSUES |
2940 | p_extract_query |
2941 | NONE |
2942 | |
2944 | |
2945 | NOTES |
2946 | |
2947 | MODIFICATION HISTORY |
2948 | Date Author Description of Changes |
2949 | 10-JUL-2009 Naveen Prodduturi Created |
2950 *==========================================================================*/
2951 PROCEDURE generate_xml(p_extract_query VARCHAR2) IS
2952 l_encoding VARCHAR2(20);
2953 l_result CLOB;
2954 tempResult CLOB;
2955 l_version VARCHAR2(20);
2956 l_compatibility VARCHAR2(20);
2957 l_majorVersion NUMBER;
2958 l_resultOffset NUMBER;
2959 l_rows_processed NUMBER;
2960 l_xml_header VARCHAR2(32000);
2961 l_xml_header_length NUMBER;
2962 queryCtx DBMS_XMLquery.ctxType;
2963 qryCtx DBMS_XMLGEN.ctxHandle;
2964 l_errNo NUMBER;
2965 l_errMsg VARCHAR2(200);
2966 l_nls_numeric_char VARCHAR2(5);
2967 l_nls_altered BOOLEAN;
2968 l_alter_str VARCHAR2(100);
2969
2970 BEGIN
2971 IF PG_DEBUG in ('Y', 'C') THEN
2972 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.generate_xml()+');
2973 print_clob(p_extract_query);
2974 END IF;
2975
2976 DBMS_UTILITY.DB_VERSION(l_version, l_compatibility);
2977 l_majorVersion := to_number(substr(l_version, 1, instr(l_version,'.')-1));
2978
2979 IF PG_DEBUG in ('Y', 'C') THEN
2980 arp_standard.debug( 'l_version :'||l_version);
2981 arp_standard.debug( 'l_compatibility :'||l_compatibility);
2982 arp_standard.debug( 'l_majorVersion :'||l_majorVersion);
2983 END IF;
2984
2985 IF (l_majorVersion > 8 and l_majorVersion < 9) THEN
2986
2987 BEGIN
2988 queryCtx := DBMS_XMLQuery.newContext( p_extract_query );
2989 DBMS_XMLQuery.setRaiseNoRowsException(queryCtx,TRUE);
2990
2991 IF PG_DEBUG in ('Y', 'C') THEN
2992 arp_standard.debug( 'Query context set.call to getXML...');
2993 END IF;
2994
2995 l_result := DBMS_XMLQuery.getXML(queryCtx);
2996 DBMS_XMLQuery.closeContext(queryCtx);
2997
2998 l_rows_processed := 1;
2999
3000 EXCEPTION WHEN OTHERS THEN
3001 DBMS_XMLQuery.getExceptionContent(queryCtx,l_errNo,l_errMsg);
3002 IF PG_DEBUG in ('Y', 'C') THEN
3003 arp_standard.debug( 'l_errNo '||l_errNo);
3004 END IF;
3005
3006 IF l_errNo = 1403 THEN
3007 l_rows_processed := 0;
3008 END IF;
3009
3010 DBMS_XMLQuery.closeContext(queryCtx);
3011 END;
3012 /*
3013 RVIRIYAL: Bug#9672615
3014 Starting with 10g DBMS_XMLQUERY package(written java) is deprecated and
3015 it is recommended to use DBMS_XMLGEN package.DBMS_XMLGEN package is similar
3016 to the DBMS_XMLQuery package, except that it is written in C and
3017 compiled into the database kernel.The issue with the DBMS_XMLGEN package is that
3018 it formats the number based on the preferences set.
3019 If the NLS NUMERIC CHARACTERS is set to ",." then the package generates the XML
3020 in such away that all the numbers are formatted using the NLS_NUMERIC_CHARACTERS.
3021 Say, if the number is 12840.5(Raw format) and if NLS_NUMERIC_CHARACTERS is set to ",.",
3022 then the XML generated will have the number as 12.840,50
3023
3024 Issue with Formatting:
3025 This procedure (generate_xml) is to generate the XML for the aging report.
3026 Once the XML got generated, OPP will pickup the XML and transform it into a Report using a template.
3027 While transforming the report, it there exists any field of type number on the template, again
3028 it tries to convert into Number. If the number is not in a raw format then an exception is raised.
3029 As OPP always expects the numbers in raw format,we need to alter the session such that number
3030 gets generated in raw format
3031 */
3032 ELSIF (l_majorVersion >= 9 ) THEN
3033 /*Flag to detrmine if the session has been altered */
3034 l_nls_altered := FALSE;
3035
3036 /*Get the NLS_NUMERIC_CHAR Settings from Session*/
3037 SELECT value INTO l_nls_numeric_char
3038 FROM v$NLS_PARAMETERS
3039 WHERE parameter = 'NLS_NUMERIC_CHARACTERS';
3040
3041 /*Set the NLS_NUMERIC_CHAR Setting*/
3042 IF l_nls_numeric_char <> '.,' THEN
3046 l_alter_str:= NULL;
3043 l_alter_str:= 'ALTER SESSION SET nls_numeric_characters=''.,''';
3044 EXECUTE IMMEDIATE l_alter_str;
3045 l_nls_altered := TRUE;
3047 END IF;
3048
3049 qryCtx := DBMS_XMLGEN.newContext(p_extract_query);
3050 IF PG_DEBUG in ('Y', 'C') THEN
3051 arp_standard.debug( 'Query context set.call to getXML...');
3052 END IF;
3053
3054 l_result := DBMS_XMLGEN.getXML(qryCtx,DBMS_XMLGEN.NONE);
3055 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
3056 DBMS_XMLGEN.closeContext(qryCtx);
3057 END IF;
3058
3059 /*If NLS_NUMERIC_CHAR has been modified, restore the same*/
3060 IF l_nls_altered THEN
3061 l_alter_str := 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='''||l_nls_numeric_Char||'''';
3062 EXECUTE IMMEDIATE l_alter_str;
3063 END IF;
3064
3065 IF PG_DEBUG in ('Y', 'C') THEN
3066 arp_standard.debug( 'l_rows_processed '||l_rows_processed);
3067 END IF;
3068
3069 IF l_rows_processed <> 0 THEN
3070 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
3071 tempResult := l_result;
3072 ELSE
3073 l_resultOffset := 0;
3074 END IF;
3075
3076 l_xml_header := get_report_header_xml;
3077 l_xml_header_length := length(l_xml_header);
3078
3079 IF l_rows_processed <> 0 THEN
3080 dbms_lob.write(tempResult,l_xml_header_length,1,l_xml_header);
3081 dbms_lob.copy(tempResult,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
3082 l_xml_header_length,l_resultOffset);
3083 dbms_lob.writeAppend(tempResult, length('</ARAGEREP>'), '</ARAGEREP>');
3084 ELSE
3085 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
3086 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
3087 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
3088 dbms_lob.writeAppend(tempResult, length('</ARAGEREP>'), '</ARAGEREP>');
3089 END IF;
3090
3091
3092 ar_cumulative_balance_report.process_clob(tempResult);
3093
3094 IF PG_DEBUG in ('Y', 'C') THEN
3095 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.generate_xml()-');
3096 END IF;
3097
3098 EXCEPTION
3099 WHEN OTHERS THEN
3100 /*If NLS_NUMERIC_CHAR has been modified, restore the same*/
3101 IF l_nls_altered THEN
3102 l_alter_str := 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='''||l_nls_numeric_Char||'''';
3103 EXECUTE IMMEDIATE l_alter_str;
3104 END IF;
3105
3106 IF PG_DEBUG in ('Y', 'C') THEN
3107 arp_standard.debug( 'Exception message '||SQLERRM);
3108 arp_standard.debug( 'Exception AR_AGING_BUCKETS_PKG.generate_xml()');
3109 END IF;
3110 RAISE;
3111 END generate_xml;
3112
3113
3114
3115
3116
3117 /*==========================================================================
3118 | PRIVATE PROCEDURE aging_rep_extract |
3119 | |
3120 | DESCRIPTION |
3121 | Acts as child process,makes required procedure call to process the |
3122 | alllocated payment schedules and generate aging information |
3123 | |
3124 | CALLED FROM PROCEDURES/FUNCTIONS |
3125 | Used as part of the cocurrent program defintion |
3126 | |
3127 | |
3128 | KNOWN ISSUES |
3129 | |
3130 | |
3131 | MODIFICATION HISTORY |
3132 | Date Author Description of Changes |
3133 | 10-JUL-2009 Naveen Prodduturi Created |
3134 *==========================================================================*/
3135 PROCEDURE aging_rep_extract(
3136 p_errbuf OUT NOCOPY VARCHAR2,
3137 p_retcode OUT NOCOPY NUMBER,
3138 p_rep_type IN VARCHAR2,
3139 p_reporting_level IN VARCHAR2,
3140 p_reporting_entity_id IN VARCHAR2,
3141 p_coaid IN VARCHAR2,
3142 p_in_bal_segment_low IN VARCHAR2,
3143 p_in_bal_segment_high IN VARCHAR2,
3144 p_in_as_of_date_low IN VARCHAR2,
3145 p_in_summary_option_low IN VARCHAR2,
3146 p_in_format_option_low IN VARCHAR2,
3147 p_in_bucket_type_low IN VARCHAR2,
3148 p_credit_option IN VARCHAR2,
3149 p_risk_option IN VARCHAR2,
3150 p_in_currency IN VARCHAR2,
3151 p_in_customer_name_low IN VARCHAR2,
3152 p_in_customer_name_high IN VARCHAR2,
3153 p_in_customer_num_low IN VARCHAR2,
3154 p_in_customer_num_high IN VARCHAR2,
3155 p_in_amt_due_low IN VARCHAR2,
3156 p_in_amt_due_high IN VARCHAR2,
3157 p_in_invoice_type_low IN VARCHAR2,
3158 p_in_invoice_type_high IN VARCHAR2,
3159 p_accounting_method IN VARCHAR2,
3160 p_in_worker_id IN VARCHAR2 DEFAULT -1,
3161 p_in_worker_count IN VARCHAR2 DEFAULT 1,
3162 p_retain_staging_flag IN VARCHAR2 DEFAULT NULL) IS
3163 BEGIN
3164
3165 IF PG_DEBUG in ('Y', 'C') THEN
3166 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.aging_rep_extract()+');
3167 arp_standard.debug( 'invoking procedure aging_seven_buckets..');
3171 p_reporting_level => p_reporting_level,
3168 END IF;
3169
3170 aging_seven_buckets( p_rep_type => p_rep_type,
3172 p_reporting_entity_id => p_reporting_entity_id,
3173 p_coaid => p_coaid,
3174 p_in_bal_segment_low => p_in_bal_segment_low,
3175 p_in_bal_segment_high => p_in_bal_segment_high,
3176 p_in_as_of_date_low => p_in_as_of_date_low,
3177 p_in_summary_option_low => p_in_summary_option_low,
3178 p_in_format_option_low => p_in_format_option_low,
3179 p_in_bucket_type_low => p_in_bucket_type_low,
3180 p_credit_option => p_credit_option,
3181 p_risk_option => p_risk_option,
3182 p_in_currency => p_in_currency,
3183 p_in_customer_name_low => p_in_customer_name_low,
3184 p_in_customer_name_high => p_in_customer_name_high,
3185 p_in_customer_num_low => p_in_customer_num_low,
3186 p_in_customer_num_high => p_in_customer_num_high,
3187 p_in_amt_due_low => p_in_amt_due_low,
3188 p_in_amt_due_high => p_in_amt_due_high,
3189 p_in_invoice_type_low => p_in_invoice_type_low,
3190 p_in_invoice_type_high => p_in_invoice_type_high,
3191 p_accounting_method => p_accounting_method,
3192 p_in_worker_id => p_in_worker_id,
3193 p_in_worker_count => p_in_worker_count,
3194 p_retain_staging_flag => p_retain_staging_flag,
3195 p_master_req_flag => 'N');
3196
3197 IF PG_DEBUG in ('Y', 'C') THEN
3198 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.aging_rep_extract()-');
3199 END IF;
3200 END aging_rep_extract;
3201
3202
3203
3204
3205 /*==========================================================================
3206 | PRIVATE PROCEDURE aging_seven_buckets |
3207 | |
3208 | DESCRIPTION |
3209 | |
3210 | |
3211 | CALLED FROM PROCEDURES/FUNCTIONS |
3212 | Used as part of the cocurrent program defintion |
3213 | |
3214 | PARAMETERS |
3215 | NONE |
3216 | |
3217 | KNOWN ISSUES |
3218 | |
3219 | NOTES |
3220 | |
3221 | MODIFICATION HISTORY |
3222 | Date Author Description of Changes |
3223 | 10-JUL-2009 Naveen Prodduturi Created |
3224 *==========================================================================*/
3225 PROCEDURE aging_seven_buckets(
3226 p_rep_type IN VARCHAR2,
3227 p_reporting_level IN VARCHAR2,
3228 p_reporting_entity_id IN VARCHAR2,
3229 p_coaid IN VARCHAR2,
3230 p_in_bal_segment_low IN VARCHAR2,
3231 p_in_bal_segment_high IN VARCHAR2,
3232 p_in_as_of_date_low IN VARCHAR2,
3233 p_in_summary_option_low IN VARCHAR2,
3234 p_in_format_option_low IN VARCHAR2,
3235 p_in_bucket_type_low IN VARCHAR2,
3236 p_credit_option IN VARCHAR2,
3237 p_risk_option IN VARCHAR2,
3238 p_in_currency IN VARCHAR2,
3239 p_in_customer_name_low IN VARCHAR2,
3240 p_in_customer_name_high IN VARCHAR2,
3241 p_in_customer_num_low IN VARCHAR2,
3242 p_in_customer_num_high IN VARCHAR2,
3243 p_in_amt_due_low IN VARCHAR2,
3244 p_in_amt_due_high IN VARCHAR2,
3245 p_in_invoice_type_low IN VARCHAR2,
3246 p_in_invoice_type_high IN VARCHAR2,
3247 p_accounting_method IN VARCHAR2,
3248 p_in_worker_id IN VARCHAR2 DEFAULT -1,
3249 p_in_worker_count IN VARCHAR2 DEFAULT 1,
3250 p_retain_staging_flag IN VARCHAR2 DEFAULT NULL,
3251 p_master_req_flag IN VARCHAR2 DEFAULT 'Y' ) IS
3252
3253 l_out_invoice_query VARCHAR2(32000);
3254 l_out_unapp_query VARCHAR2(32000);
3255 l_out_riskinfo_query VARCHAR2(32000);
3256 l_out_br_query VARCHAR2(32000);
3257 l_worker_number NUMBER;
3258 l_complete BOOLEAN := FALSE;
3259
3260 PROCEDURE submit_subrequest ( p_worker_number IN NUMBER ) IS
3261 l_request_id NUMBER;
3262 BEGIN
3263 arp_standard.debug( 'submit_subrequest()+');
3264
3265 l_request_id := FND_REQUEST.submit_request( 'AR', 'ARXAGEXT',
3266 '',
3267 SYSDATE,
3268 FALSE,
3269 p_rep_type,
3270 p_reporting_level,
3271 p_reporting_entity_id,
3272 p_coaid,
3273 p_in_bal_segment_low,
3274 p_in_bal_segment_high,
3275 p_in_as_of_date_low,
3276 p_in_summary_option_low,
3277 p_in_format_option_low,
3278 p_in_bucket_type_low,
3279 p_credit_option,
3280 p_risk_option,
3281 p_in_currency,
3282 p_in_customer_name_low,
3283 p_in_customer_name_high,
3284 p_in_customer_num_low,
3285 p_in_customer_num_high,
3289 p_in_invoice_type_high,
3286 p_in_amt_due_low,
3287 p_in_amt_due_high,
3288 p_in_invoice_type_low,
3290 p_accounting_method,
3291 to_char(p_worker_number),
3292 to_char(pg_worker_count));
3293
3294
3295 IF (l_request_id = 0) THEN
3296 arp_standard.debug( 'can not start for worker_id: ' ||p_worker_number );
3297 arp_standard.debug( 'Error Message ' ||fnd_Message.get );
3298 return;
3299 ELSE
3300 commit;
3301 arp_standard.debug( 'child request id: ' ||l_request_id ||
3302 ' started for worker_id: ' ||p_worker_number );
3303 END IF;
3304
3305 pg_req_status_tab(p_worker_number).request_id := l_request_id;
3306 arp_standard.debug( 'submit_subrequest()-');
3307
3308 END submit_subrequest;
3309
3310 BEGIN
3311 IF PG_DEBUG in ('Y', 'C') THEN
3312 arp_standard.debug( 'AR_AGING_BUCKETS_PKG.aging_seven_buckets()+');
3313 arp_standard.debug( 'p_rep_type '|| p_rep_type);
3314 arp_standard.debug( 'p_reporting_level '|| p_reporting_level);
3315 arp_standard.debug( 'p_reporting_entity_id '|| p_reporting_entity_id);
3316 arp_standard.debug( 'p_coaid '|| p_coaid);
3317 arp_standard.debug( 'p_in_bal_segment_low '|| p_in_bal_segment_low);
3318 arp_standard.debug( 'p_in_bal_segment_high '|| p_in_bal_segment_high);
3319 arp_standard.debug( 'p_in_as_of_date_low '|| p_in_as_of_date_low);
3320 arp_standard.debug( 'p_in_summary_option_low '|| p_in_summary_option_low);
3321 arp_standard.debug( 'p_in_format_option_low '|| p_in_format_option_low);
3322 arp_standard.debug( 'p_in_bucket_type_low '|| p_in_bucket_type_low);
3323 arp_standard.debug( 'p_credit_option '|| p_credit_option);
3324 arp_standard.debug( 'p_risk_option '|| p_risk_option);
3325 arp_standard.debug( 'p_in_currency '|| p_in_currency);
3326 arp_standard.debug( 'p_in_customer_name_low '|| p_in_customer_name_low);
3327 arp_standard.debug( 'p_in_customer_name_high '|| p_in_customer_name_high);
3328 arp_standard.debug( 'p_in_customer_num_low '|| p_in_customer_num_low);
3329 arp_standard.debug( 'p_in_customer_num_high '|| p_in_customer_num_high);
3330 arp_standard.debug( 'p_in_amt_due_low '|| p_in_amt_due_low);
3331 arp_standard.debug( 'p_in_amt_due_high '|| p_in_amt_due_high);
3332 arp_standard.debug( 'p_in_invoice_type_low '|| p_in_invoice_type_low);
3333 arp_standard.debug( 'p_in_invoice_type_high '|| p_in_invoice_type_high);
3334 arp_standard.debug( 'p_worker_id '|| p_in_worker_id);
3335 arp_standard.debug( 'p_worker_count '|| p_in_worker_count);
3336 arp_standard.debug( 'p_retain_staging_flag '|| p_retain_staging_flag);
3337 arp_standard.debug( 'p_accounting_method '|| p_accounting_method);
3338 END IF;
3339
3340 --set the parameter value to package global variables
3341 pg_rep_type := p_rep_type;
3342 pg_reporting_level := TO_NUMBER(NVL(p_reporting_level,0));
3343 pg_reporting_entity_id := TO_NUMBER(NVL(p_reporting_entity_id,0));
3344 pg_coaid := TO_NUMBER(NVL(p_coaid,0));
3345 pg_in_bal_segment_low := p_in_bal_segment_low;
3346 pg_in_bal_segment_high := p_in_bal_segment_high;
3347 pg_in_as_of_date_low := fnd_date.canonical_to_date(p_in_as_of_date_low);
3348 pg_in_summary_option_low := p_in_summary_option_low;
3349 pg_in_format_option_low := p_in_format_option_low;
3350 pg_in_bucket_type_low := p_in_bucket_type_low;
3351 pg_credit_option := p_credit_option;
3352 pg_risk_option := p_risk_option;
3353 pg_in_currency := p_in_currency;
3354 pg_in_customer_name_low := p_in_customer_name_low;
3355 pg_in_customer_name_high := p_in_customer_name_high;
3356 pg_in_customer_num_low := p_in_customer_num_low;
3357 pg_in_customer_num_high := p_in_customer_num_high;
3358 pg_in_invoice_type_low := p_in_invoice_type_low;
3359 pg_in_invoice_type_high := p_in_invoice_type_high;
3360 pg_worker_id := TO_NUMBER(NVL(p_in_worker_id,'-1'));
3361 pg_worker_count := TO_NUMBER(NVL(p_in_worker_count,'1'));
3362 pg_retain_staging_flag := NVL(p_retain_staging_flag,'N');
3363 pg_accounting_method := p_accounting_method;
3364 pg_in_amt_due_low := p_in_amt_due_low;
3365 pg_in_amt_due_high := p_in_amt_due_high;
3366
3367 IF PG_DEBUG in ('Y', 'C') THEN
3368 arp_standard.debug( 'pg_reporting_level '|| pg_reporting_level);
3369 arp_standard.debug( 'pg_reporting_entity_id '|| pg_reporting_entity_id);
3370 arp_standard.debug( 'pg_coaid '|| pg_coaid);
3371 arp_standard.debug( 'pg_in_as_of_date_low '|| pg_in_as_of_date_low);
3372 arp_standard.debug( 'pg_worker_id '|| pg_worker_id);
3373 arp_standard.debug( 'pg_worker_count '|| pg_worker_count);
3374 arp_standard.debug( 'pg_retain_staging_flag '|| pg_retain_staging_flag);
3375 END IF;
3376
3377 pg_request_id := arp_standard.profile.request_id;
3378
3379 initialize_package_globals;
3380
3381 IF nvl(p_master_req_flag,'Y') = 'Y' THEN
3382 pg_parent_request_id := pg_request_id;
3383
3384 --distribute the workload across the workers
3385 alloc_aging_payment_schedules;
3386 ELSE
3387 pg_parent_request_id := get_parent_request_id( pg_request_id );
3388 END IF;
3389
3390 /* In case the request is to be processed by more than one worker then invoke
3391 required number of child processes, otherwise continue with processing the
3392 report */
3393 IF nvl(p_master_req_flag,'Y') = 'Y' AND pg_worker_count > 1 THEN
3394 --Invoke the child programs
3395 FOR l_worker_number IN 1..pg_worker_count LOOP
3396 IF PG_DEBUG in ('Y', 'C') THEN
3400 END LOOP;
3397 arp_standard.debug( 'Submitting worker '|| l_worker_number );
3398 END IF;
3399 submit_subrequest ( l_worker_number );
3401
3402 -- Wait for the completion of the submitted requests
3403 FOR i in 1..pg_worker_count LOOP
3404 IF PG_DEBUG in ('Y', 'C') THEN
3405 arp_standard.debug( 'Waiting for the completion of worker '||pg_req_status_tab(i).request_id);
3406 END IF;
3407
3408 l_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(
3409 request_id => pg_req_status_tab(i).request_id,
3410 interval => 30,
3411 max_wait =>144000,
3412 phase =>pg_req_status_tab(i).phase,
3413 status =>pg_req_status_tab(i).status,
3414 dev_phase =>pg_req_status_tab(i).dev_phase,
3415 dev_status =>pg_req_status_tab(i).dev_status,
3416 message =>pg_req_status_tab(i).message);
3417
3418 IF pg_req_status_tab(i).dev_phase <> 'COMPLETE' THEN
3419 arp_util.debug('Worker # '|| i||' has a phase '||pg_req_status_tab(i).dev_phase);
3420
3421 ELSIF pg_req_status_tab(i).dev_phase = 'COMPLETE'
3422 AND pg_req_status_tab(i).dev_status <> 'NORMAL' THEN
3423 arp_util.debug('Worker # '|| i||' completed with status '||pg_req_status_tab(i).dev_status);
3424 ELSE
3425 arp_util.debug('Worker # '|| i||' completed successfully');
3426 END IF;
3427 END LOOP;
3428 ELSE
3429 IF PG_DEBUG in ('Y', 'C') THEN
3430 arp_standard.debug( 'Child processing..');
3431 END IF;
3432
3433 build_select_stmt( p_out_invoice_query => l_out_invoice_query,
3434 p_out_receipt_query => l_out_unapp_query,
3435 p_out_riskinfo_query => l_out_riskinfo_query,
3436 p_out_br_query => l_out_br_query);
3437
3438 arp_standard.debug( l_out_unapp_query );
3439
3440 extract_aging_information( AR_AGING_CTGRY_INVOICE,
3441 l_out_invoice_query );
3442
3443 IF pg_accounting_method = 'MFAR' THEN
3444 prorate_aging_balances_mfar(AR_AGING_CTGRY_INVOICE);
3445 END IF;
3446
3447 extract_aging_information( AR_AGING_CTGRY_RECEIPT,
3448 l_out_unapp_query );
3449
3450 IF pg_risk_option <> 'NONE' THEN
3451 extract_aging_information( AR_AGING_CTGRY_RISK,
3452 l_out_riskinfo_query );
3453 END IF;
3454
3455 extract_aging_information( AR_AGING_CTGRY_BR,
3456 l_out_br_query );
3457 END IF;
3458
3459 --invoke the report
3460 IF nvl(p_master_req_flag,'Y') = 'Y' THEN
3461 generate_xml( get_report_query );
3462
3463 IF pg_retain_staging_flag <> 'Y' THEN
3464 cleanup_staging_tables;
3465 END IF;
3466 END IF;
3467
3468 END aging_seven_buckets;
3469
3470
3471 END AR_AGING_BUCKETS_PKG;