[Home] [Help]
PACKAGE BODY: APPS.AR_CUMULATIVE_BALANCE_REPORT
Source
1 PACKAGE BODY ar_cumulative_balance_report AS
2 /* $Header: ARXCUABB.pls 120.14.12020000.2 2012/07/24 07:05:01 kkikkise ship $ */
3
4
5 -- comments
6 -- company segment is GL_BALANCING
7 -- natural account segment is GL_ACCOUNT
8
9 TYPE flex_table IS TABLE OF FND_FLEX_VALUES.flex_value%TYPE
10 INDEX BY BINARY_INTEGER;
11
12 TYPE gl_accounts_type
13 IS TABLE OF ar_ccid_by_gl_accounts.gl_account%TYPE
14 INDEX BY BINARY_INTEGER;
15
16 TYPE components_type
17 IS TABLE OF ar_gl_acct_balances.component%TYPE
18 INDEX BY BINARY_INTEGER;
19
20 TYPE natural_accounts_type
21 IS TABLE OF gl_code_combinations.segment1%TYPE
22 INDEX BY BINARY_INTEGER;
23
24 TYPE trx_type_type
25 IS TABLE OF ar_receipt_methods.name%TYPE
26 INDEX BY BINARY_INTEGER;
27
28 TYPE trx_number_type
29 IS TABLE OF ar_cash_receipts_all.receipt_number%TYPE
30 INDEX BY BINARY_INTEGER;
31
32 TYPE trx_date_type
33 IS TABLE OF ra_customer_trx_all.trx_date%TYPE
34 INDEX BY BINARY_INTEGER;
35
36 TYPE currency_code_type
37 IS TABLE OF ra_customer_trx_all.invoice_currency_code%TYPE
38 INDEX BY BINARY_INTEGER;
39
40 TYPE gl_date_type
41 IS TABLE OF --{Replace ra_cust_trx_line_gl_dist_all.gl_date%TYPE by
42 ar_xla_ctlgd_lines_v.gl_date%TYPE
43 --}
44 INDEX BY BINARY_INTEGER;
45
46 TYPE acctd_amount_dr_type
47 IS TABLE OF ar_gl_acct_balances.acctd_amount_dr%TYPE
48 INDEX BY BINARY_INTEGER;
49
50 TYPE acctd_amount_cr_type
51 IS TABLE OF ar_gl_acct_balances.acctd_amount_cr%TYPE
52 INDEX BY BINARY_INTEGER;
53
54 TYPE amount_dr_type
55 IS TABLE OF ar_gl_acct_balances.amount_dr%TYPE
56 INDEX BY BINARY_INTEGER;
57
58 TYPE amount_cr_type
59 IS TABLE OF ar_gl_acct_balances.amount_cr%TYPE
60 INDEX BY BINARY_INTEGER;
61
62 TYPE code_combination_id_type
63 IS TABLE OF ar_gl_acct_balances.code_combination_id%TYPE
64 INDEX BY BINARY_INTEGER;
65
66 TYPE customer_trx_id_type
67 IS TABLE OF ar_gl_acct_balances.customer_trx_id%TYPE
68 INDEX BY BINARY_INTEGER;
69
70 TYPE cash_receipt_id_type
71 IS TABLE OF ar_gl_acct_balances.cash_receipt_id%TYPE
72 INDEX BY BINARY_INTEGER;
73
74 TYPE adjustment_id_type
75 IS TABLE OF ar_gl_acct_balances.adjustment_id%TYPE
76 INDEX BY BINARY_INTEGER;
77
78 TYPE org_id_type
79 IS TABLE OF ar_gl_acct_balances.org_id%TYPE
80 INDEX BY BINARY_INTEGER;
81
82 TYPE last_update_date_type
83 IS TABLE OF ar_gl_acct_balances.last_update_date%TYPE
84 INDEX BY BINARY_INTEGER;
85
86 TYPE last_updated_by_type
87 IS TABLE OF ar_gl_acct_balances.last_updated_by%TYPE
88 INDEX BY BINARY_INTEGER;
89
90 TYPE creation_date_type
91 IS TABLE OF ar_gl_acct_balances.creation_date%TYPE
92 INDEX BY BINARY_INTEGER;
93
94 TYPE created_by_type
95 IS TABLE OF ar_gl_acct_balances.created_by%TYPE
96 INDEX BY BINARY_INTEGER;
97
98 TYPE last_update_login_type
99 IS TABLE OF ar_gl_acct_balances.last_update_login%TYPE
100 INDEX BY BINARY_INTEGER;
101
102 TYPE ref_cur IS REF CURSOR;
103
104 g_ar_system_parameters VARCHAR2(40) DEFAULT NULL;
105 g_ar_system_parameters_all VARCHAR2(40) DEFAULT NULL;
106 g_ar_adjustments VARCHAR2(40) DEFAULT NULL;
107 g_ar_adjustments_all VARCHAR2(40) DEFAULT NULL;
108 g_ar_cash_receipt_history VARCHAR2(40) DEFAULT NULL;
109 g_ar_cash_receipt_history_all VARCHAR2(40) DEFAULT NULL;
110 g_ar_cash_receipts VARCHAR2(40) DEFAULT NULL;
111 g_ar_cash_receipts_all VARCHAR2(40) DEFAULT NULL;
112 g_ar_distributions VARCHAR2(40) DEFAULT NULL;
113 g_ar_distributions_all VARCHAR2(40) DEFAULT NULL;
114 g_ra_customer_trx VARCHAR2(40) DEFAULT NULL;
115 g_ra_customer_trx_all VARCHAR2(40) DEFAULT NULL;
116 g_ra_cust_trx_gl_dist VARCHAR2(40) DEFAULT NULL;
117 g_ra_cust_trx_gl_dist_all VARCHAR2(40) DEFAULT NULL;
118 g_ar_misc_cash_dists VARCHAR2(40) DEFAULT NULL;
119 g_ar_misc_cash_dists_all VARCHAR2(40) DEFAULT NULL;
120 g_ar_receivable_apps VARCHAR2(40) DEFAULT NULL;
121 g_ar_receivable_apps_all VARCHAR2(40) DEFAULT NULL;
122 g_ar_receipt_methods VARCHAR2(40) DEFAULT NULL;
123 g_ra_cust_trx_types VARCHAR2(40) DEFAULT NULL;
124 g_ra_cust_trx_types_all VARCHAR2(40) DEFAULT NULL;
125 g_ar_transaction_history VARCHAR2(40) DEFAULT NULL;
126 g_ar_transaction_history_all VARCHAR2(40) DEFAULT NULL;
127
128 -- Variables to hold the where clause based on the input parameters
129 /* Variable length increased from 200 to 500 for bug:5181586*/
130 g_dist_org_where VARCHAR2(500);
131 g_crh_org_where VARCHAR2(500);
132 g_cr_org_where VARCHAR2(500);
133 g_rm_org_where VARCHAR2(500);
134 g_mcd_org_where VARCHAR2(500);
135 g_br_org_where VARCHAR2(500);
136 g_adj_org_where VARCHAR2(500);
137 g_ard_org_where VARCHAR2(500);
138 g_trx_org_where VARCHAR2(500);
139 g_rec_org_where VARCHAR2(500);
140 g_type_org_where VARCHAR2(500);
141 g_sys_org_where VARCHAR2(500);
142 g_balances_where VARCHAR2(500);
143 /* Change for bug:5181586 ends*/
144 detail flex_table;
145 pg_debug VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
146
147
148 PROCEDURE debug (p_string VARCHAR2, p_mode VARCHAR2 DEFAULT 'ALWAYS') IS
149
150 BEGIN
151
152 IF (p_mode = 'ALWAYS') THEN
153
154 fnd_file.put_line (
155 which => fnd_file.log,
156 buff => p_string);
157
158 ELSIF pg_debug IN ('Y', 'C') THEN
159
160 fnd_file.put_line (
161 which => fnd_file.log,
162 buff => p_string);
163
164 END IF;
165
166 END debug;
167
168
169 PROCEDURE process_clob (p_xml_clob CLOB) IS
170
171 l_clob_size NUMBER;
172 l_offset NUMBER;
173 l_chunk_size INTEGER;
174 l_chunk VARCHAR2(32767);
175
176 l_chunk1 VARCHAR2(32767);
177 l_chunk2 VARCHAR2(32767);
178 l_offset_temp number;
179
180 BEGIN
181
182 debug('ar_cumulative_balance_report.process_clob(+)');
183
184 -- get length of internal lob and open the dest. file.
185 l_clob_size := dbms_lob.getlength(p_xml_clob);
186
187 IF (l_clob_size = 0) THEN
188 debug('CLOB is empty');
189 RETURN;
190 END IF;
191
192 l_offset := 1;
193 l_chunk_size := 3000;
194
195 debug('Unloading... ' || l_clob_size);
196
197 WHILE (l_clob_size > 0) LOOP
198
199 -- debug('Off Set: ' || l_offset);
200
201 l_chunk := dbms_lob.substr (p_xml_clob, l_chunk_size, l_offset);
202
203 --debug('Off Set: ' || l_offset);
204 --debug(l_chunk);
205
206 -- Bug 6696706 - As per input from fnd bug 6868010 this is modified
207
208 l_offset_temp := DBMS_LOB.INSTR(l_chunk,'</',1,1);
209 /* added to fix bug 13987673 - begin */
210 if l_offset_temp = 0 then
211 l_offset_temp := 1;
212 end if;
213 /* added to fix bug 13987673 - end */
214 l_offset_temp := DBMS_LOB.INSTR(l_chunk,'>',l_offset_temp,1);
215 l_chunk1 := dbms_lob.substr (l_chunk, l_offset_temp, 1);
216 l_chunk2 := dbms_lob.substr (l_chunk, l_chunk_size-l_offset_temp, l_offset_temp+1);
217
218 fnd_file.put(
219 which => fnd_file.output,
220 buff => l_chunk1);
221 fnd_file.new_line(fnd_file.output);
222 fnd_file.put(
223 which => fnd_file.output,
224 buff => l_chunk2);
225
226 /*fnd_file.put(
227 which => fnd_file.output,
228 buff => l_chunk);*/
229
230 -- Bug 6696706 ends
231
232 l_clob_size := l_clob_size - l_chunk_size;
233 l_offset := l_offset + l_chunk_size;
234
235 END LOOP;
236
237 fnd_file.new_line(fnd_file.output,1);
238
239 debug('ar_cumulative_balance_report.process_clob(-)');
240
241 EXCEPTION
242 WHEN OTHERS THEN
243 debug('EXCEPTION: OTHERS process_clob');
244 debug(sqlcode);
245 debug(sqlerrm);
246 RAISE;
247
248 END process_clob;
249
250
251 FUNCTION get_gl_account_segment
252 RETURN VARCHAR2 IS
253
254 l_gl_account_segment varchar2(50);
255
256 CURSOR segment IS
257 SELECT application_column_name
258 FROM fnd_segment_attribute_values
259 WHERE attribute_value = 'Y'
260 AND segment_attribute_type = 'GL_ACCOUNT'
261 AND id_flex_num in
262 (SELECT chart_of_accounts_id
263 FROM gl_sets_of_books sob,
264 ar_system_parameters sys
265 WHERE sob.set_of_books_id = sys.set_of_books_id);
266
267 BEGIN
268
269 debug('ar_cumulative_balance_report.get_gl_account_segment(+)');
270
271 OPEN segment;
272 FETCH segment INTO l_gl_account_segment;
273 CLOSE segment;
274
275 debug('ar_cumulative_balance_report.get_gl_account_segment(-)');
276 RETURN l_gl_account_segment;
277
278 END get_gl_account_segment;
279
280
281 PROCEDURE perform_updates IS
282
283 l_update_stmt VARCHAR2(32767);
284
285 BEGIN
286
287 debug('ar_cumulative_balance_report.perform_updates(+)');
288
289 -- update null trx number, type, date, currency for invoices and CMS.
290
291 l_update_stmt :=
292 'UPDATE ar_base_gl_acct_balances bal
293 SET (trx_number, trx_type, trx_date, currency) =
294 (
295 SELECT receipt_number, rm.name, receipt_date, currency_code
296 FROM ' || g_ar_cash_receipts_all || ', '
297 || g_ar_receipt_methods || '
298 WHERE cr.receipt_method_id = rm.receipt_method_id
299 AND cr.cash_receipt_id = bal.cash_receipt_id
300 AND rownum = 1
301 )
302 WHERE bal.cash_receipt_id IS NOT NULL
303 AND bal.trx_number IS NULL';
304
305 l_update_stmt := l_update_stmt || g_balances_where;
306 debug(l_update_stmt, 'N');
307
308 EXECUTE IMMEDIATE l_update_stmt;
309
310 debug('update statement 2(a): ' || SQL%ROWCOUNT);
311
312 l_update_stmt :=
313 'UPDATE ar_gl_acct_balances bal
314 SET (trx_number, trx_type, trx_date, currency) =
315 (
316 SELECT receipt_number, rm.name, receipt_date, currency_code
317 FROM ' || g_ar_cash_receipts_all || ', '
318 || g_ar_receipt_methods || '
319 WHERE cr.receipt_method_id = rm.receipt_method_id
320 AND cr.cash_receipt_id = bal.cash_receipt_id
321 AND rownum = 1
322 )
323 WHERE bal.cash_receipt_id IS NOT NULL
324 AND bal.trx_number IS NULL';
325
326 debug(l_update_stmt, 'N');
327
328 EXECUTE IMMEDIATE l_update_stmt;
329
330 debug('update statement 2(b): ' || SQL%ROWCOUNT);
331
332
333 -- update null trx number, type, date, currency for invoices and CMS.
334
335 l_update_stmt :=
336 'UPDATE ar_base_gl_acct_balances bal
337 SET (trx_number, trx_type, trx_date, currency) =
338 (
339 SELECT trx_number, ctt.name, trx_date, invoice_currency_code
340 FROM ' || g_ra_customer_trx_all || ', '
341 || g_ra_cust_trx_types_all || '
342 WHERE trx.cust_trx_type_id = ctt.cust_trx_type_id
343 AND trx.customer_trx_id = bal.customer_trx_id
344 AND rownum = 1
345 )
346 WHERE bal.customer_trx_id IS NOT NULL
347 AND bal.trx_number IS NULL';
348
349 l_update_stmt := l_update_stmt || g_balances_where;
350 debug(l_update_stmt, 'N');
351
352 EXECUTE IMMEDIATE l_update_stmt;
353
354 debug('update statement 1(a): ' || SQL%ROWCOUNT);
355
356
357 l_update_stmt :=
358 'UPDATE ar_gl_acct_balances bal
359 SET (trx_number, trx_type, trx_date, currency) =
360 (
361 SELECT trx_number, ctt.name, trx_date, invoice_currency_code
362 FROM ' || g_ra_customer_trx_all || ', '
363 || g_ra_cust_trx_types_all || '
364 WHERE trx.cust_trx_type_id = ctt.cust_trx_type_id
365 AND trx.customer_trx_id = bal.customer_trx_id
366 AND rownum = 1
367 )
368 WHERE bal.customer_trx_id IS NOT NULL
369 AND bal.trx_number IS NULL';
370
371 debug(l_update_stmt, 'N');
372
373 EXECUTE IMMEDIATE l_update_stmt;
374
375 debug('update statement 1(b): ' || SQL%ROWCOUNT);
376
377 COMMIT;
378
379 debug('ar_cumulative_balance_report.perform_updates(-)');
380
381 EXCEPTION
382 WHEN NO_DATA_FOUND THEN
383 debug('EXCEPTION: NO_DATA_FOUND perform_updates');
384 debug(sqlcode);
385 debug(sqlerrm);
386 RAISE;
387
388 WHEN OTHERS THEN
389 debug('EXCEPTION: OTHERS perform_updates');
390 debug(sqlcode);
391 debug(sqlerrm);
392 RAISE;
393
394 END perform_updates;
395
396
397 PROCEDURE insert_dist_data (
398 p_start_date DATE,
399 p_end_date DATE,
400 p_period_status VARCHAR2) IS
401
402 components_tab components_type;
403 gl_account_tab gl_accounts_type;
404 natural_account_tab natural_accounts_type;
405 trx_type_tab trx_type_type;
406 trx_number_tab trx_number_type;
407 trx_date_tab trx_date_type;
408 currency_code_tab currency_code_type;
409 gl_date_tab gl_date_type;
410 last_update_date_tab last_update_date_type;
411 last_updated_by_tab last_updated_by_type;
412 creation_date_tab creation_date_type;
413 created_by_tab created_by_type;
414 last_update_login_tab last_update_login_type;
415 acctd_amount_dr_tab acctd_amount_dr_type;
416 acctd_amount_cr_tab acctd_amount_cr_type;
417 amount_dr_tab amount_dr_type;
418 amount_cr_tab amount_cr_type;
419 code_combination_id_tab code_combination_id_type;
420 customer_trx_id_tab customer_trx_id_type;
421 cash_receipt_id_tab cash_receipt_id_type;
422 adjustment_id_tab adjustment_id_type;
423 org_id_tab org_id_type;
424 l_last_fetch boolean;
425 l_sql_stmt VARCHAR2(32767);
426 l_ref_cursor ref_cur;
427 l_user_id fnd_user.user_id%TYPE;
428 l_precision fnd_currencies.precision%TYPE;
429
430 CURSOR precision IS
431 SELECT cur.precision
432 FROM gl_sets_of_books sob,
433 fnd_currencies cur
434 WHERE sob.currency_code = cur.currency_code
435 AND sob.set_of_books_id = arp_standard.sysparm.set_of_books_id;
436
437 BEGIN
438
439 debug('ar_cumulative_balance_report.insert_dist_data(+)');
440 debug('start date: ' || to_char(p_start_date));
441 debug('end date: ' || to_char(p_end_date));
442 debug('status: ' || p_period_status);
443
444 l_user_id := fnd_global.user_id;
445
446 debug('l_user_id: ' || l_user_id);
447
448 -- In order to round the amount columns we must figure how many places
449 -- we must round. That is being determined here by looking at the
450 -- currency precision of the set of books.
451
452 OPEN precision;
453 FETCH precision INTO l_precision;
454 CLOSE precision;
455
456 debug('rounding precision: ' || l_precision);
457
458 l_sql_stmt :=
459 'SELECT
460 MAX(component),
461 MAX(gl_account) gl_account,
462 natural_account,
463 trx_type,
464 trx_number,
465 trx_date,
466 entered_currency,
467 MAX(activity_gl_date) activity_gl_date,
468 round(sum(acctd_amt_dr), ' || l_precision || ') acctd_amt_dr,
469 round(sum(acctd_amt_cr), ' || l_precision || ') acctd_amt_cr,
470 round(sum(amount_dr), ' || l_precision || ') amount_dr,
471 round(sum(amount_cr), ' || l_precision || ') amount_cr,
472 code_combination_id,
473 customer_trx_id,
474 cash_receipt_id,
475 adjustment_id,
476 max(org_id) org_id,
477 sysdate creation_date,
478 ' || l_user_id || ' created_by,
479 sysdate last_update_date,
480 ' || l_user_id || ' last_updated_by,
481 ' || l_user_id || ' last_update_login
482 FROM
483 (
484 -- pick up distributions from the ra_cust_trx_line_gl_dist_all
485 SELECT
486 ''DIST'' component,
487 MAX(glc.gl_account) gl_account,
488 glc.natural_account,
489 ctt.name trx_type,
490 trx_number,
491 trx_date,
492 invoice_currency_code entered_currency,
493 MAX(dist.gl_date) activity_gl_date,
494 sum(DECODE(account_class,
495 ''REC'',decode(sign(acctd_amount),-1,0,acctd_amount),
496 ''REV'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
497 ''TAX'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
498 ''ROUND'',decode(sign(acctd_amount), -1,abs(acctd_amount),0),
499 ''UNEARN'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
500 ''UNBILL'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
501 ''SUSPENSE'',decode(sign(acctd_amount),-1, abs(acctd_amount),0),0))
502 acctd_amt_dr,
503 sum(DECODE(account_class,
504 ''REC'',decode(sign(acctd_amount),-1,abs(acctd_amount),0),
505 ''REV'',decode(sign(acctd_amount), -1, 0,acctd_amount),
506 ''TAX'',decode(sign(acctd_amount), -1, 0,acctd_amount),
507 ''ROUND'',decode(sign(acctd_amount), -1,0,acctd_amount),
508 ''UNEARN'',decode(sign(acctd_amount), -1, 0,acctd_amount),
509 ''UNBILL'',decode(sign(acctd_amount), -1, 0,acctd_amount),
510 ''SUSPENSE'',decode(sign(acctd_amount), -1, 0,acctd_amount),0))
511 acctd_amt_cr ,
512 sum(DECODE(account_class,
513 ''REC'',decode(sign(amount), -1 ,0, amount),
514 ''REV'',decode(sign(amount), -1, abs(amount),0),
515 ''TAX'',decode(sign(amount), -1, abs(amount),0),
516 ''ROUND'',decode(sign(amount), -1,abs(amount),0),
517 ''UNEARN'',decode(sign(amount), -1, abs(amount),0),
518 ''UNBILL'',decode(sign(amount), -1, abs(amount),0),
519 ''SUSPENSE'',decode(sign(acctd_amount),-1, abs(acctd_amount),0),0))
520 amount_dr,
521 sum(DECODE(account_class,
522 ''REC'',decode(sign(amount), -1 ,abs(amount),0),
523 ''REV'',decode(sign(amount), -1, 0,amount),
524 ''TAX'',decode(sign(amount), -1, 0,amount),
525 ''ROUND'',decode(sign(amount), -1,0,amount),
526 ''UNEARN'',decode(sign(amount), -1, 0,amount),
527 ''UNBILL'',decode(sign(amount), -1, 0,amount),
528 ''SUSPENSE'',decode(sign(amount), -1, 0,amount),0))
529 amount_cr,
530 dist.code_combination_id,
531 dist.customer_trx_id customer_trx_id,
532 null cash_receipt_id,
533 null adjustment_id,
534 max(dist.org_id) org_id
535 FROM ' ||
536 g_ra_cust_trx_gl_dist_all || ', ' ||
537 g_ra_customer_trx_all || ', ' ||
538 g_ra_cust_trx_types_all ||
539 ', ar_ccid_by_gl_accounts glc
540 WHERE dist.gl_date BETWEEN :p_start_date AND :p_end_date
541 AND dist.account_set_flag = ''N''
542 AND trx.complete_flag = ''Y''
543 AND dist.customer_trx_id = trx.customer_trx_id
544 AND trx.cust_trx_type_id = ctt.cust_trx_type_id
545 AND dist.code_combination_id = glc.code_combination_id '
546 || g_dist_org_where
547 || g_trx_org_where
548 || g_type_org_where || '
549 -- AND dist.posting_control_id > 0
550 GROUP BY
551 glc.natural_account,
552 ctt.name,
553 trx_number,
554 trx_date,
555 invoice_currency_code,
556 dist.code_combination_id,
557 dist.customer_trx_id,
558 null,
559 null
560 -- pick up distributions from the tables ar_distributions_all for
561 -- ar_cash_receipt_history
562 UNION ALL
563 SELECT
564 ''CRH'' component,
565 MAX(glc.gl_account) gl_account,
566 glc.natural_account,
567 rm.name trx_type,
568 cr.receipt_number trx_number,
569 cr.receipt_date trx_date,
570 cr.currency_code entered_currency,
571 MAX(crh.gl_date) activity_gl_date,
572 sum(acctd_amount_dr) acctd_amt_dr,
573 sum(acctd_amount_cr) acctd_amt_cr,
574 sum(amount_dr) amt_dr, sum(amount_cr) amt_cr,
575 ard.code_combination_id,
576 null customer_trx_id,
577 crh.cash_receipt_id cash_receipt_id,
578 null adjustment_id,
579 max(ard.org_id) org_id
580 FROM ' ||
581 g_ar_distributions_all || ', ' ||
582 g_ar_cash_receipt_history_all || ', ' ||
583 g_ar_cash_receipts_all || ', ' ||
584 g_ar_receipt_methods || ' , ' ||
585 'ar_ccid_by_gl_accounts glc
586 WHERE crh.gl_date between :p_start_date and :p_end_date
587 AND crh.cash_receipt_history_id = ard.source_id
588 AND ard.source_table = ''CRH''
589 AND ard.code_combination_id = glc.code_combination_id
590 AND crh.cash_receipt_id = cr.cash_receipt_id
591 AND cr.receipt_method_id = rm.receipt_method_id '
592 || g_ard_org_where
593 || g_crh_org_where
594 || g_cr_org_where || '
595 -- AND crh.posting_control_id > 0
596 GROUP BY
597 glc.natural_account,
598 rm.name,
599 cr.receipt_number,
600 cr.receipt_date,
601 cr.currency_code,
602 --null,
603 ard.code_combination_id,
604 null,
605 crh.cash_receipt_id,
606 null
607 -- pick up distributions from the table ar_distributions_all for
608 -- receivable_applications_all
609 UNION ALL
610 SELECT
611 ''RA'' component,
612 MAX(glc.gl_account) gl_account,
613 glc.natural_account,
614 null trx_type,
615 null trx_number,
616 null trx_date,
617 null entered_currency,
618 max(ra.gl_date) activity_gl_date,
619 sum(acctd_amount_dr) acctd_amt_dr,
620 sum(acctd_amount_cr) acctd_amt_cr,
621 sum(amount_dr) amt_dr,
622 sum(amount_cr) amt_cr,
623 ard.code_combination_id,
624 decode(ra.application_type,''CASH'',
625 decode(ra.status, ''APP'', ra.applied_customer_trx_id, null),
626 ''CM'', decode(sign(ra.amount_applied),-1,
627 decode(ard.amount_dr,null,ra.customer_trx_id,
628 ra.applied_customer_trx_id),
629 decode(ard.amount_dr,null,ra.applied_customer_trx_id,
630 ra.customer_trx_id))) customer_trx_id,
631 decode(ra.status, ''APP'', to_number(null), ra.cash_receipt_id)
632 cash_receipt_id,
633 null adjustment_id,
634 max(ard.org_id) org_id
635 FROM ' ||
636 g_ar_distributions_all || ', ' ||
637 g_ar_receivable_apps_all || ', ' ||
638 ' ar_ccid_by_gl_accounts glc
639 WHERE ra.gl_date BETWEEN :p_start_date and :p_end_date
640 AND ra.receivable_application_id = ard.source_id
641 AND ard.source_table = ''RA''
642 AND ard.code_combination_id = glc.code_combination_id '
643 || g_ard_org_where
644 || g_rec_org_where || '
645 -- AND ra.posting_control_id > 0
646 GROUP BY
647 glc.natural_account,
648 null,
649 null,
650 null,
651 null,
652 --null,
653 ard.code_combination_id,
654 decode(ra.application_type,''CASH'',
655 decode(ra.status,''APP'',ra.applied_customer_trx_id,null),
656 ''CM'',decode(sign(ra.amount_applied),-1,
657 decode(ard.amount_dr,null,ra.customer_trx_id,
658 ra.applied_customer_trx_id),
659 decode(ard.amount_dr,null,ra.applied_customer_trx_id,
660 ra.customer_trx_id))),
661 decode(ra.status,''APP'',to_number(null),ra.cash_receipt_id),
662 null
663 -- pick up distributions from the table ar_distributions_all for
664 -- ar_misc_cash_distributions
665 UNION ALL
666 SELECT
667 ''MCH'' component,
668 MAX(glc.gl_account) gl_account,
669 glc.natural_account,
670 rm.name trx_type,
671 cr.receipt_number trx_number,
672 cr.receipt_date trx_date,
673 cr.currency_code entered_currency,
674 MAX(mcd.gl_date),
675 sum(acctd_amount_dr) acctd_amt_dr,
676 sum(acctd_amount_cr) acctd_amt_cr,
677 sum(amount_dr) amt_dr, sum(amount_cr) amt_cr,
678 ard.code_combination_id,
679 null customer_trx_id,
680 mcd.cash_receipt_id,
681 null adjustment_id,
682 max(ard.org_id) org_id
683 FROM ' ||
684 g_ar_distributions_all || ', ' ||
685 g_ar_misc_cash_dists_all || ', ' ||
686 g_ar_cash_receipts_all || ', ' ||
687 g_ar_receipt_methods || ', ' ||
688 ' ar_ccid_by_gl_accounts glc
689 WHERE mcd.gl_date between :p_start_date and :p_end_date
690 AND mcd.misc_cash_distribution_id = ard.source_id
691 AND ard.source_table = ''MCD''
692 AND ard.code_combination_id = glc.code_combination_id
693 AND mcd.cash_receipt_id = cr.cash_receipt_id
694 AND cr.receipt_method_id = rm.receipt_method_id '
695 || g_ard_org_where
696 || g_mcd_org_where
697 || g_cr_org_where || '
698 -- AND mcd.posting_control_id > 0
699 GROUP BY
700 glc.natural_account,
701 rm.name,
702 cr.receipt_number,
703 cr.receipt_date,
704 cr.currency_code,
705 --null,
706 ard.code_combination_id,
707 null,
708 mcd.cash_receipt_id,
709 null
710 -- pick up distributions from the table ar_distributions_all for
711 -- ar_adjustments
712 UNION ALL
713 SELECT
714 ''ADJ'' component,
715 MAX(glc.gl_account) gl_account,
716 glc.natural_account,
717 ctt.name trx_type,
718 trx_number,
719 trx_date,
720 invoice_currency_code entered_currency,
721 MAX(adj.gl_date) activity_gl_date,
722 sum(acctd_amount_dr) acctd_amt_dr,
723 sum(acctd_amount_cr) acctd_amt_cr,
724 sum(amount_dr) amt_dr, sum(amount_cr) amt_cr,
725 ard.code_combination_id,
726 decode(adj.amount,-1,
727 decode(ard.amount_dr,null, adj.customer_trx_id, null),
728 decode(ard.amount_cr,null, adj.customer_trx_id, null))
729 customer_trx_id,
730 null cash_receipt_id,
731 decode(adj.amount,-1,
732 decode(ard.amount_cr,null, adj.adjustment_id, null),
733 decode(ard.amount_dr,null, adj.adjustment_id, null))
734 adjustment_id,
735 max(ard.org_id) org_id
736 FROM ' ||
737 g_ar_distributions_all || ', ' ||
738 g_ar_adjustments_all || ', ' ||
739 g_ra_customer_trx_all || ', ' ||
740 g_ra_cust_trx_types_all || ', ' ||
741 ' ar_ccid_by_gl_accounts glc
742 WHERE adj.gl_date between :p_start_date and :p_end_date
743 AND adj.adjustment_id = ard.source_id
744 AND ard.source_table = ''ADJ''
745 AND ard.code_combination_id = glc.code_combination_id
746 AND adj.customer_trx_id = trx.customer_trx_id
747 AND trx.cust_trx_type_id = ctt.cust_trx_type_id '
748 || g_ard_org_where
749 || g_adj_org_where
750 || g_trx_org_where
751 || g_type_org_where || '
752 -- AND adj.posting_control_id > 0
753 GROUP BY
754 glc.natural_account,
755 ctt.name,
756 trx_number,
757 trx_date,
758 invoice_currency_code,
759 --null,
760 ard.code_combination_id,
761 decode(adj.amount,-1,
762 decode(ard.amount_dr,null, adj.customer_trx_id, null),
763 decode(ard.amount_cr,null, adj.customer_trx_id, null)),
764 null,
765 decode(adj.amount,-1,
766 decode(ard.amount_cr,null, adj.adjustment_id, null),
767 decode(ard.amount_dr,null, adj.adjustment_id, null))
768 -- pick up distributions from the table ar_distributions_all for
769 -- ar_transaction_history (BR)
770 UNION ALL
771 SELECT
772 ''BR'' component,
773 MAX(glc.gl_account) gl_account,
774 glc.natural_account,
775 null trx_type,
776 null trx_number,
777 null trx_date,
778 null entered_currency,
779 max(br.gl_date) activity_gl_date,
780 sum(acctd_amount_dr) acctd_amt_dr,
781 sum(acctd_amount_cr) acctd_amt_cr,
782 sum(amount_dr) amt_dr, sum(amount_cr) amt_cr,
783 ard.code_combination_id,
784 br.customer_trx_id customer_trx_id,
785 null cash_receipt_id,
786 null adjustment_id,
787 max(ard.org_id) org_id
788 FROM ' ||
789 g_ar_distributions_all || ', ' ||
790 g_ar_transaction_history_all || ', ' ||
791 ' ar_ccid_by_gl_accounts glc
792 WHERE br.gl_date between :p_start_date and :p_end_date
793 AND br.transaction_history_id = ard.source_id
794 AND ard.source_table = ''TH''
795 AND ard.code_combination_id = glc.code_combination_id '
796 || g_ard_org_where
797 || g_br_org_where || '
798 -- AND br.posting_control_id > 0
799 GROUP BY
800 glc.natural_account,
801 null,
802 null,
803 null,
804 null,
805 --null,
806 ard.code_combination_id,
807 customer_trx_id,
808 null,
809 null
810 )
811 GROUP BY
812 natural_account,
813 trx_type,
814 trx_number,
815 trx_date,
816 entered_currency,
817 -- activity_gl_date,
818 null,
819 null,
820 null,
821 null,
822 null,
823 code_combination_id,
824 customer_trx_id,
825 cash_receipt_id,
826 adjustment_id,
827 sysdate,
828 ' || l_user_id || ' ,
829 sysdate,
830 ' || l_user_id || ' ,
831 ' || l_user_id || ' ';
832
833 debug ('Dynamic SQL constructed');
834 -- debug (l_sql_stmt);
835
836 OPEN l_ref_cursor FOR l_sql_stmt USING
837 p_start_date, p_end_date,
838 p_start_date, p_end_date,
839 p_start_date, p_end_date,
840 p_start_date, p_end_date,
841 p_start_date, p_end_date,
842 p_start_date, p_end_date;
843
844 LOOP
845 FETCH l_ref_cursor BULK COLLECT INTO
846 components_tab,
847 gl_account_tab,
848 natural_account_tab,
849 trx_type_tab,
850 trx_number_tab,
851 trx_date_tab,
852 currency_code_tab,
853 gl_date_tab,
854 acctd_amount_dr_tab,
855 acctd_amount_cr_tab,
856 amount_dr_tab,
857 amount_cr_tab,
858 code_combination_id_tab,
859 customer_trx_id_tab,
860 cash_receipt_id_tab,
861 adjustment_id_tab,
862 org_id_tab,
863 last_update_date_tab,
864 last_updated_by_tab,
865 creation_date_tab,
866 created_by_tab,
867 last_update_login_tab
868 LIMIT 1000;
869
870 IF l_ref_cursor%NOTFOUND THEN
871 l_last_fetch := TRUE;
872 END IF;
873
874 IF code_combination_id_tab.COUNT = 0 and l_last_fetch THEN
875 EXIT;
876 END IF;
877
878 IF p_period_status = 'CLOSED' THEN
879
880 FORALL i IN 1..code_combination_id_tab.count
881 INSERT INTO ar_base_gl_acct_balances
882 (
883 component,
884 gl_account,
885 natural_account,
886 trx_type,
887 trx_number,
888 trx_date,
889 currency,
890 activity_gl_date,
891 code_combination_id ,
892 customer_trx_id,
893 cash_receipt_id,
894 adjustment_id,
895 last_update_date,
896 last_updated_by,
897 creation_date,
898 created_by,
899 last_update_login,
900 acctd_amount_dr,
901 acctd_amount_cr,
902 amount_dr,
903 amount_cr,
904 org_id
905 )
906 VALUES
907 (
908 components_tab(i),
909 gl_account_tab(i),
910 natural_account_tab(i),
911 trx_type_tab(i),
912 trx_number_tab(i),
913 trx_date_tab(i),
914 currency_code_tab(i),
915 gl_date_tab(i),
916 code_combination_id_tab(i),
917 customer_trx_id_tab(i),
918 cash_receipt_id_tab(i),
919 adjustment_id_tab(i),
920 last_update_date_tab(i),
921 last_updated_by_tab(i),
922 creation_date_tab(i),
923 created_by_tab(i),
924 last_update_login_tab(i),
925 acctd_amount_dr_tab(i),
926 acctd_amount_cr_tab(i),
927 amount_dr_tab(i),
928 amount_cr_tab(i),
929 org_id_tab(i)
930 );
931
932 ELSE
933
934 FORALL i IN 1..code_combination_id_tab.count
935 INSERT INTO ar_gl_acct_balances
936 (
937 component,
938 gl_account,
939 natural_account,
940 trx_type,
941 trx_number,
942 trx_date,
943 currency,
944 activity_gl_date,
945 code_combination_id ,
946 customer_trx_id ,
947 cash_receipt_id ,
948 adjustment_id ,
949 last_update_date,
950 last_updated_by ,
951 creation_date ,
952 created_by ,
953 last_update_login,
954 acctd_amount_dr,
955 acctd_amount_cr,
956 amount_dr,
957 amount_cr,
958 org_id
959 )
960 VALUES
961 (
962 components_tab(i),
963 gl_account_tab(i),
964 natural_account_tab(i),
965 trx_type_tab(i),
966 trx_number_tab(i),
967 trx_date_tab(i),
968 currency_code_tab(i),
969 gl_date_tab(i),
970 code_combination_id_tab(i),
971 customer_trx_id_tab(i),
972 cash_receipt_id_tab(i),
973 adjustment_id_tab(i),
974 last_update_date_tab(i),
975 last_updated_by_tab(i),
976 creation_date_tab(i),
977 created_by_tab(i),
978 last_update_login_tab(i),
979 acctd_amount_dr_tab(i),
980 acctd_amount_cr_tab(i),
981 amount_dr_tab(i),
982 amount_cr_tab(i),
983 org_id_tab(i)
984 );
985
986 END IF;
987
988 IF l_last_fetch THEN
989 EXIT;
990 END IF;
991
992 COMMIT;
993
994 END LOOP;
995
996 CLOSE l_ref_cursor;
997
998 debug('ar_cumulative_balance_report.insert_dist_data(-)');
999
1000 EXCEPTION
1001 WHEN NO_DATA_FOUND THEN
1002 debug('EXCEPTION: NO_DATA_FOUND insert_dist_data');
1003 debug(sqlcode);
1004 debug(sqlerrm);
1005 RAISE;
1006
1007 WHEN OTHERS THEN
1008 debug('EXCEPTION: OTHERS insert_dist_data');
1009 debug(sqlcode);
1010 debug(sqlerrm);
1011 RAISE;
1012
1013 END insert_dist_data;
1014
1015
1016 FUNCTION flex_sql(
1017 p_application_id in number,
1018 p_id_flex_code in varchar2,
1019 p_id_flex_num in number default null,
1020 p_table_alias in varchar2,
1021 p_mode in varchar2,
1022 p_qualifier in varchar2,
1023 p_function in varchar2 default null,
1024 p_operand1 in varchar2 default null,
1025 p_operand2 in varchar2 default null) return varchar2 IS
1026
1027 l_ret_param varchar2(2000);
1028
1029 BEGIN
1030
1031 debug('ar_cumulative_balance_report.flex_sql(+)');
1032
1033 -- This is a wrapper function for the fa_rx_flex_pkg. When patch 4128137 is
1034 -- released, we need to replace this call with the corresponding
1035 -- FND API calls
1036
1037 l_ret_param := fa_rx_flex_pkg.flex_sql (
1038 p_application_id => p_application_id,
1039 p_id_flex_code => p_id_flex_code,
1040 p_id_flex_num => p_id_flex_num,
1041 p_table_alias => p_table_alias,
1042 p_mode => p_mode,
1043 p_qualifier => p_qualifier,
1044 p_function => p_function,
1045 p_operand1 => p_operand1,
1046 p_operand2 => p_operand2);
1047
1048 debug('ar_cumulative_balance_report.flex_sql(-)');
1049
1050 RETURN l_ret_param;
1051
1052 END flex_sql;
1053
1054
1055 FUNCTION get_seg_condition (
1056 p_qualifier VARCHAR2,
1057 p_seg_low VARCHAR2,
1058 p_seg_high VARCHAR2,
1059 p_coa_id NUMBER)
1060 RETURN VARCHAR2 IS
1061
1062 l_seg_where VARCHAR2(4000);
1063
1064 BEGIN
1065
1066 debug('ar_cumulative_balance_report.get_seg_codition(+)');
1067
1068 IF p_seg_low IS NULL AND p_seg_high IS NULL THEN
1069
1070 l_seg_where := NULL;
1071
1072 ELSIF p_seg_low IS NULL THEN
1073
1074 l_seg_where := ' AND ' ||
1075 flex_sql(
1076 p_application_id => 101,
1077 p_id_flex_code => 'GL#',
1078 p_id_flex_num => p_coa_id,
1079 p_table_alias => 'gcc',
1080 p_mode => 'WHERE',
1081 p_qualifier => p_qualifier,
1082 p_function => '<=',
1083 p_operand1 => p_seg_high);
1084
1085 ELSIF p_seg_high IS NULL THEN
1086
1087 l_seg_where := ' AND ' ||
1088 flex_sql(
1089 p_application_id => 101,
1090 p_id_flex_code => 'GL#',
1091 p_id_flex_num => p_coa_id,
1092 p_table_alias => 'gcc',
1093 p_mode => 'WHERE',
1094 p_qualifier => p_qualifier,
1095 p_function => '>=',
1096 p_operand1 => p_seg_low);
1097
1098 ELSE
1099
1100 l_seg_where := ' AND ' ||
1101 flex_sql(p_application_id => 101,
1102 p_id_flex_code => 'GL#',
1103 p_id_flex_num => p_coa_id,
1104 p_table_alias => 'gcc',
1105 p_mode => 'WHERE',
1106 p_qualifier => p_qualifier,
1107 p_function => 'BETWEEN',
1108 p_operand1 => p_seg_low,
1109 p_operand2 => p_seg_high);
1110
1111 END IF;
1112
1113 debug('ar_cumulative_balance_report.get_seg_codition(-)');
1114
1115 RETURN l_seg_where;
1116
1117 END get_seg_condition;
1118
1119
1120 PROCEDURE populate_ccids (
1121 p_chart_of_accounts_id IN NUMBER,
1122 p_coa_id IN NUMBER,
1123 p_co_seg_low IN VARCHAR2,
1124 p_co_seg_high IN VARCHAR2,
1125 p_gl_account_low IN VARCHAR2,
1126 p_gl_account_high IN VARCHAR2) IS
1127
1128
1129 l_user_id fnd_user.user_id%TYPE;
1130 l_segment_name fnd_segment_attribute_values.application_column_name%TYPE;
1131 l_sql_stmt VARCHAR2(4000);
1132 l_delete_stmt VARCHAR2(200);
1133 l_account_where VARCHAR2(200);
1134 l_co_seg_where VARCHAR2(200);
1135 l_min_start_date DATE;
1136 l_max_end_date DATE;
1137 l_segment_str VARCHAR2(2000);
1138 BEGIN
1139
1140 debug('ar_cumulative_balance_report.populate_ccids(+)');
1141 debug('p_coa_id : ' || p_coa_id);
1142 debug('p_co_seg_low : ' || p_co_seg_low );
1143 debug('p_co_seg_high : ' || p_co_seg_high);
1144 debug('p_gl_account_low : ' || p_gl_account_low);
1145 debug('p_gl_account_high : ' || p_gl_account_high);
1146
1147 -- Step 1
1148 -- Populate the interim table ar_interim_ccid_by_gl_account with the
1149 -- code combinations for the given gl_account.
1150
1151 l_user_id := fnd_global.user_id;
1152 l_segment_name := get_gl_account_segment();
1153
1154 debug('Natural segment: ' || l_segment_name);
1155
1156 DELETE FROM ar_ccid_by_gl_accounts;
1157
1158 debug('number of rows deleted: ' || SQL%ROWCOUNT);
1159
1160 l_segment_str:=
1161 FLEX_SQL(101,
1162 'GL#',
1163 p_chart_of_accounts_id,
1164 'GCC',
1165 'SELECT',
1166 'ALL');
1167 l_sql_stmt :=
1168 'INSERT INTO ar_ccid_by_gl_accounts
1169 (
1170 code_combination_id,
1171 natural_account,
1172 gl_account,
1173 last_update_date,
1174 last_updated_by,
1175 creation_date,
1176 created_by,
1177 last_update_login
1178 )
1179 (
1180 SELECT
1181 code_combination_id, ' ||
1182 l_segment_name || ' , ' || l_segment_str || ' gl_account ' || ',
1183 sysdate,
1184 ' || l_user_id || ' ,
1185 sysdate,
1186 ' || l_user_id || ' ,
1187 ' || l_user_id || '
1188 FROM gl_code_combinations gcc
1189 WHERE gcc.account_type IN (''A'', ''L'')
1190 AND chart_of_accounts_id = ' || p_chart_of_accounts_id || '
1191 AND ' || l_segment_name || ' IS NOT NULL ) ';
1192
1193 -- debug(l_sql_stmt);
1194
1195 EXECUTE IMMEDIATE l_sql_stmt ;
1196
1197 debug('number of rows inserted: ' || SQL%ROWCOUNT);
1198
1199 fnd_stats.gather_table_stats('AR', 'AR_CCID_BY_GL_ACCOUNTS');
1200
1201 debug('ar_cumulative_balance_report.populate_ccids(-)');
1202
1203 EXCEPTION
1204 WHEN NO_DATA_FOUND THEN
1205 debug('EXCEPTION: NO_DATA_FOUND populate_ccids');
1206 debug(sqlcode);
1207 debug(sqlerrm);
1208 RAISE;
1209
1210 WHEN OTHERS THEN
1211 debug('EXCEPTION: OTHERS populate_ccids');
1212 debug(sqlcode);
1213 debug(sqlerrm);
1214 RAISE;
1215
1216 END populate_ccids;
1217
1218
1219 FUNCTION refresh_verdict (p_gl_as_of_date DATE)
1220 RETURN VARCHAR2 IS
1221
1222 l_num_rows NUMBER;
1223 l_max_trx_date DATE;
1224 l_max_activity_date DATE;
1225 l_sql_stmt VARCHAR2(4000);
1226
1227 BEGIN
1228
1229 debug('ar_cumulative_balance_report.refresh_verdict(+)');
1230
1231 l_sql_stmt := 'SELECT count(*), max(trx_date), max(activity_gl_date)
1232 FROM ar_base_gl_acct_balances bal WHERE 1=1 ' ;
1233
1234 l_sql_stmt := l_sql_stmt || g_balances_where;
1235
1236 debug('sql statement');
1237 debug(l_sql_stmt);
1238
1239 EXECUTE IMMEDIATE l_sql_stmt
1240 INTO l_num_rows, l_max_trx_date, l_max_activity_date ;
1241
1242 debug('Number of Rows in Base Table For This Org: ' || l_num_rows);
1243 debug('Max Trx Date: ' || l_max_trx_date);
1244 debug('Max Activity Date : ' || l_max_activity_date);
1245
1246 IF ( (l_num_rows = 0) OR
1247 (p_gl_as_of_date < l_max_trx_date) OR
1248 (p_gl_as_of_date < l_max_activity_date)) THEN
1249
1250 debug('ar_cumulative_balance_report.refresh_verdict(-)');
1251 RETURN 'Y';
1252
1253 END IF;
1254
1255 debug('ar_cumulative_balance_report.refresh_verdict(-)');
1256 RETURN 'N';
1257
1258 END refresh_verdict;
1259
1260
1261 PROCEDURE populate_data (
1262 p_reporting_level IN VARCHAR2,
1263 p_reporting_entity_id IN NUMBER,
1264 p_reporting_format IN VARCHAR2,
1265 p_chart_of_accounts_id IN NUMBER,
1266 p_sob_id IN NUMBER,
1267 p_coa_id IN NUMBER,
1268 p_co_seg_low IN VARCHAR2,
1269 p_co_seg_high IN VARCHAR2,
1270 p_gl_as_of_date IN VARCHAR2,
1271 p_gl_account_low IN VARCHAR2,
1272 p_gl_account_high IN VARCHAR2,
1273 p_refresh_tables IN VARCHAR2 DEFAULT 'N') IS
1274
1275 l_user_id fnd_user.user_id%TYPE;
1276 l_segment_name fnd_segment_attribute_values.application_column_name%TYPE;
1277 l_sql_stmt VARCHAR2(4000);
1278 /* Variable length increased from 200 to 500 for bug:5181586*/
1279 l_delete_stmt VARCHAR2(500);
1280 /* Change for bug:5181586 ends*/
1281 l_account_where VARCHAR2(200);
1282 l_co_seg_where VARCHAR2(200);
1283 l_min_start_date DATE;
1284 l_max_end_date DATE;
1285 l_refresh_tables VARCHAR2(1) DEFAULT 'N';
1286
1287 CURSOR c IS
1288 SELECT MIN(start_date), MAX(end_date)
1289 FROM ar_closed_gl_periods
1290 WHERE closing_status = 'C';
1291
1292 BEGIN
1293
1294 debug('ar_cumulative_balance_report.populate_data(+)');
1295 debug('p_reporting_level : ' || p_reporting_level);
1296 debug('p_reporting_entity_id : ' || p_reporting_entity_id);
1297 debug('p_reporting_format : ' || p_reporting_format);
1298 debug('p_sob_id : ' || p_sob_id);
1299 debug('p_coa_id : ' || p_coa_id);
1300 debug('p_co_seg_low : ' || p_co_seg_low );
1301 debug('p_co_seg_high : ' || p_co_seg_high);
1302 debug('p_gl_as_of_date : ' || p_gl_as_of_date);
1303 debug('p_gl_account_low : ' || p_gl_account_low);
1304 debug('p_gl_account_high : ' || p_gl_account_high);
1305 -- debug('p_refresh_tables : ' || p_refresh_tables);
1306
1307 -- Step 1
1308 -- Populate the interim table ar_interim_ccid_by_gl_account with the
1309 -- code combinations for the given gl_account.
1310
1311 l_user_id := fnd_global.user_id;
1312 l_segment_name := get_gl_account_segment();
1313
1314 debug('user ID: ' || l_user_id);
1315 debug('Natural segment: ' || l_segment_name);
1316
1317 populate_ccids(
1318 p_chart_of_accounts_id => p_chart_of_accounts_id,
1319 p_coa_id => p_coa_id,
1320 p_co_seg_low => p_co_seg_low,
1321 p_co_seg_high => p_co_seg_high,
1322 p_gl_account_low => p_gl_account_low,
1323 p_gl_account_high => p_gl_account_high);
1324
1325 -- after a detailed discussion, we decided that as of now no option
1326 -- will be given to refresh the tables or not. we will advise
1327 -- our customer to run this report with the last audit date when
1328 -- they run it for the first time. ANy subsequence run would not
1329 -- refresh tables unless they give an earlier date.
1330 --
1331 -- as result, i will intercept the code here and find out if we should
1332 -- refresh or not.
1333
1334 l_refresh_tables := refresh_verdict(p_gl_as_of_date);
1335 debug('l_refresh_tables : ' || l_refresh_tables);
1336
1337 IF l_refresh_tables = 'Y' THEN
1338
1339 debug( 'Refresh option selected');
1340
1341 -- For the given p_gl_as_of_date determine how many gl_periods are there
1342 -- since inception and store them in interim table ar_closed_gl_periods
1343 /* EXISTS clause added to handle more rows returned by the sub-query of SOB for bug:5181586*/
1344
1345 INSERT INTO ar_closed_gl_periods
1346 (
1347 period_name,
1348 start_date,
1349 end_date,
1350 last_update_date,
1351 last_updated_by,
1352 creation_date,
1353 created_by,
1354 last_update_login,
1355 period_year,
1356 closing_status
1357 )
1358 (
1359 SELECT
1360 period_name,
1361 start_date,
1362 end_date,
1363 sysdate,
1364 l_user_id,
1365 sysdate,
1366 l_user_id,
1367 l_user_id,
1368 period_year,
1369 closing_status
1370 FROM gl_period_statuses
1371 WHERE adjustment_period_flag = 'N'
1372 AND application_id = 222
1373 AND end_date <= p_gl_as_of_date
1374 AND EXISTS
1375 (
1376 SELECT set_of_books_id
1377 FROM ar_system_parameters
1378 )
1379 AND NOT EXISTS
1380 (
1381 SELECT 'x'
1382 FROM ar_closed_gl_periods
1383 )
1384 );
1385
1386 /* Change for Bug:5181586 ends*/
1387 debug('Done - INSERT INTO ar_closed_gl_periods');
1388
1389 -- For each period in ar_closed_gl_periods that is CLOSED,
1390 -- we know that additional entries cannot occur hence we do a
1391 -- one time upgrade to get the transaction wise balances for
1392 -- all closed periods.
1393
1394 OPEN c;
1395 FETCH c INTO l_min_start_date, l_max_end_date;
1396 CLOSE c;
1397
1398 debug('start date: ' || l_min_start_date);
1399 debug('end date: ' || l_max_end_date);
1400
1401 l_delete_stmt := 'DELETE FROM ar_base_gl_acct_balances bal WHERE 1=1 ';
1402 l_delete_stmt := l_delete_stmt || g_balances_where;
1403
1404 debug('Delete Statement: ' || l_delete_stmt);
1405 EXECUTE IMMEDIATE l_delete_stmt;
1406
1407 debug('base (all): number of rows deleted: ' || SQL%ROWCOUNT);
1408
1409 debug('calling insert_dist_data passing status as CLOSED');
1410
1411 insert_dist_data(
1412 p_start_date => l_min_start_date ,
1413 p_end_date => l_max_end_date,
1414 p_period_status => 'CLOSED');
1415
1416 debug('base: deleting all lines whose cr and dr cancel each other');
1417
1418 l_delete_stmt := 'DELETE FROM ar_base_gl_acct_balances bal
1419 WHERE nvl(acctd_amount_dr,0) = nvl(acctd_amount_cr,0) ';
1420 l_delete_stmt := l_delete_stmt || g_balances_where;
1421
1422 debug('Delete Statement: ' || l_delete_stmt);
1423 EXECUTE IMMEDIATE l_delete_stmt;
1424
1425 debug('Number of rows deleted: ' || SQL%ROWCOUNT);
1426
1427 ELSE
1428
1429 OPEN c;
1430 FETCH c INTO l_min_start_date, l_max_end_date;
1431 CLOSE c;
1432
1433 debug('start date: ' || l_min_start_date);
1434 debug('end date: ' || l_max_end_date);
1435
1436 END IF; -- l_refresh_tables = 'Y'
1437 COMMIT;
1438
1439 -- now let us construct the data after the cut off date.
1440
1441 l_delete_stmt := 'DELETE FROM ar_gl_acct_balances bal WHERE 1=1 ';
1442 l_delete_stmt := l_delete_stmt || g_balances_where;
1443 --debug('Delete Statement: ' || l_delete_stmt);
1444 EXECUTE IMMEDIATE l_delete_stmt;
1445 debug('number of rows deleted: ' || SQL%ROWCOUNT);
1446
1447 debug( 'p_start_date: ' || to_char(l_max_end_date+1));
1448 debug( 'p_end_date: ' || to_char(p_gl_as_of_date));
1449
1450 insert_dist_data(
1451 p_start_date => l_max_end_date+1,
1452 p_end_date => p_gl_as_of_date,
1453 p_period_status => 'OPEN');
1454
1455 debug('deleting all lines whose cr and dr cancel each other');
1456 l_delete_stmt := 'DELETE FROM ar_gl_acct_balances bal
1457 WHERE nvl(acctd_amount_dr,0) = nvl(acctd_amount_cr,0) ';
1458 l_delete_stmt := l_delete_stmt || g_balances_where;
1459 --debug('Delete Statement: ' || l_delete_stmt);
1460 EXECUTE IMMEDIATE l_delete_stmt;
1461 debug('Number of rows deleted: ' || SQL%ROWCOUNT);
1462
1463 COMMIT;
1464
1465 debug('ar_cumulative_balance_report.populate_data(-)');
1466
1467 EXCEPTION
1468 WHEN NO_DATA_FOUND THEN
1469 debug('EXCEPTION: NO_DATA_FOUND populate_data');
1470 debug(sqlcode);
1471 debug(sqlerrm);
1472 RAISE;
1473
1474 WHEN OTHERS THEN
1475 debug('EXCEPTION: OTHERS populate_data');
1476 debug(sqlcode);
1477 debug(sqlerrm);
1478 RAISE;
1479
1480 END populate_data;
1481
1482
1483 PROCEDURE init (p_set_of_books_id IN NUMBER) IS
1484
1485 l_sysparam_sob_id NUMBER;
1486 l_mrc_sob_type_code VARCHAR2(1);
1487
1488 CURSOR sob_type IS
1489 SELECT mrc_sob_type_code
1490 FROM gl_sets_of_books
1491 WHERE set_of_books_id = p_set_of_books_id;
1492
1493 CURSOR system_options IS
1494 select set_of_books_id
1495 from ar_system_parameters;
1496
1497 BEGIN
1498
1499 debug('ar_cumulative_balance_report.init(+)');
1500
1501 IF p_set_of_books_id <> -1999 THEN
1502 OPEN sob_type;
1503 FETCH sob_type INTO l_mrc_sob_type_code;
1504 CLOSE sob_type;
1505 ELSE
1506 l_mrc_sob_type_code := 'P';
1507 END IF;
1508
1509 OPEN system_options;
1510 FETCH system_options INTO l_sysparam_sob_id;
1511 CLOSE system_options;
1512
1513 IF (upper(l_mrc_sob_type_code) = 'R') THEN
1514 fnd_client_info.set_currency_context(p_set_of_books_id);
1515 END IF;
1516
1517 IF l_sysparam_sob_id = p_set_of_books_id THEN
1518 l_mrc_sob_type_code := 'P';
1519 END IF;
1520
1521 IF upper(l_mrc_sob_type_code) = 'P' THEN
1522
1523 g_ar_system_parameters := 'ar_system_parameters sys';
1524 g_ar_system_parameters_all := 'ar_system_parameters_all sys';
1525 g_ar_adjustments := 'ar_adjustments adj';
1526 g_ar_adjustments_all := 'ar_adjustments_all adj';
1527 g_ar_cash_receipt_history := 'ar_cash_receipt_history crh';
1528 g_ar_cash_receipt_history_all := 'ar_cash_receipt_history_all crh';
1529 g_ar_cash_receipts := 'ar_cash_receipts cr';
1530 g_ar_cash_receipts_all := 'ar_cash_receipts_all cr';
1531 --{Replaced
1532 -- g_ar_distributions := 'ar_distributions ard';
1533 g_ar_distributions := 'ar_xla_ard_lines_v ard';
1534 -- g_ar_distributions_all := 'ar_distributions_all ard';
1535 g_ar_distributions_all := 'ar_xla_ard_lines_v ard';
1536 --}
1537 g_ra_customer_trx := 'ra_customer_trx trx';
1538 g_ra_customer_trx_all := 'ra_customer_trx_all trx';
1539 --{Replaced
1540 -- g_ra_cust_trx_gl_dist := 'ra_cust_trx_line_gl_dist dist';
1541 g_ra_cust_trx_gl_dist := 'ar_xla_ctlgd_lines_v dist';
1542 -- g_ra_cust_trx_gl_dist_all := 'ra_cust_trx_line_gl_dist_all dist';
1543 g_ra_cust_trx_gl_dist_all := 'ar_xla_ctlgd_lines_v dist';
1544 --}
1545 g_ar_misc_cash_dists := 'ar_misc_cash_distributions mcd';
1546 g_ar_misc_cash_dists_all := 'ar_misc_cash_distributions_all mcd';
1547 g_ar_receivable_apps := 'ar_receivable_applications ra';
1548 g_ar_receivable_apps_all := 'ar_receivable_applications_all ra';
1549 g_ar_receipt_methods := 'ar_receipt_methods rm';
1550 g_ra_cust_trx_types := 'ra_cust_trx_types ctt';
1551 g_ra_cust_trx_types_all := 'ra_cust_trx_types_all ctt';
1552 g_ar_transaction_history := 'ar_transaction_history br';
1553 g_ar_transaction_history_all := 'ar_transaction_history_all br';
1554
1555 ELSE
1556
1557 g_ar_system_parameters := 'ar_system_parameters_mrc_v sys';
1558 g_ar_system_parameters_all := 'ar_system_parameters_all_mrc_v sys';
1559 g_ar_adjustments := 'ar_adjustments_mrc_v adj';
1560 g_ar_adjustments_all := 'ar_adjustments_all_mrc_v adj';
1561 g_ar_cash_receipt_history := 'ar_cash_receipt_hist_mrc_v crh';
1562 g_ar_cash_receipt_history_all := 'ar_cash_receipt_hist_all_mrc_v crh';
1563 g_ar_cash_receipts := 'ar_cash_receipts cr';
1564 g_ar_cash_receipts_all := 'ar_cash_receipts_all_mrc_v cr';
1565 g_ar_distributions := 'ar_distributions_mrc_v ard';
1566 g_ar_distributions_all := 'ar_distributions_all_mrc_v ard';
1567 g_ra_customer_trx := 'ra_customer_trx_mrc_v trx';
1568 g_ra_customer_trx_all := 'ra_customer_trx_all_mrc_v trx';
1569 g_ra_cust_trx_gl_dist := 'ra_trx_line_gl_dist_all_mrc_v dist';
1570 g_ra_cust_trx_gl_dist_all := 'ra_trx_line_gl_dist_mrc_v dist';
1571 g_ar_misc_cash_dists := 'ar_misc_cash_dists_mrc_v mcd';
1572 g_ar_misc_cash_dists_all := 'ar_misc_cash_dists_all_mrc_v mcd';
1573 g_ar_receivable_apps := 'ar_receivable_apps_mrc_v ra';
1574 g_ar_receivable_apps_all := 'ar_receivable_apps_all_mrc_v ra';
1575 g_ar_receipt_methods := 'ar_receipt_methods rm';
1576 g_ra_cust_trx_types := 'ra_cust_trx_types ctt';
1577 g_ra_cust_trx_types_all := 'ra_cust_trx_types_all ctt';
1578 g_ar_transaction_history := 'ar_transaction_history br';
1579 g_ar_transaction_history_all := 'ar_transaction_history_all br';
1580
1581 END IF;
1582
1583 debug('ar_cumulative_balance_report.init(-)');
1584
1585 END init;
1586
1587
1588 PROCEDURE generate_xml (
1589 p_reporting_level IN VARCHAR2,
1590 p_reporting_entity_id IN NUMBER,
1591 p_reporting_format IN VARCHAR2,
1592 p_sob_id IN NUMBER,
1593 p_coa_id IN NUMBER,
1594 p_co_seg_low IN VARCHAR2,
1595 p_co_seg_high IN VARCHAR2,
1596 p_gl_as_of_date IN VARCHAR2,
1597 p_gl_account_low IN VARCHAR2,
1598 p_gl_account_high IN VARCHAR2,
1599 p_refresh IN VARCHAR2 DEFAULT 'N',
1600 p_result OUT NOCOPY CLOB) IS
1601
1602 l_xml_stmt ref_cur;
1603 l_result CLOB;
1604 tempResult CLOB;
1605 l_gl_as_of_date DATE;
1606 l_version varchar2(20);
1607 l_compatibility varchar2(20);
1608 l_suffix varchar2(2);
1609 l_majorVersion number;
1610 l_resultOffset number;
1611 l_xml_header varchar2(3000);
1612 l_xml_header_length number;
1613 l_errNo NUMBER;
1614 l_errMsg VARCHAR2(200);
1615 queryCtx DBMS_XMLquery.ctxType;
1616 qryCtx DBMS_XMLGEN.ctxHandle;
1617
1618 l_xml_query VARCHAR2(32767);
1619 l_base_query VARCHAR2(32767);
1620 l_recent_query VARCHAR2(32767);
1621 l_gl_account_where VARCHAR2(4000);
1622 l_company_where VARCHAR2(4000);
1623 l_natural_segment_col VARCHAR2(50);
1624 l_flex_value_set_id NUMBER;
1625 l_code_combinations VARCHAR2(1000);
1626 l_rows_processed NUMBER;
1627 l_new_line VARCHAR2(1) := '';
1628
1629 -- Variables to hold the report heading
1630 l_sob_id NUMBER;
1631 l_chart_of_accounts_id NUMBER;
1632 l_sob_name VARCHAR2(100);
1633 l_functional_currency VARCHAR2(15);
1634 l_organization VARCHAR2(60);
1635 l_format VARCHAR2(40);
1636 l_close_tag VARCHAR2(100);
1637 l_reporting_entity_name VARCHAR2(80);
1638 l_reporting_level_name VARCHAR2(30);
1639 l_status_meaning VARCHAR2(30);
1640 l_gl_account_type_meaning VARCHAR2(100);
1641 l_dummy_where VARCHAR2(200);
1642 l_group_by VARCHAR2(4000);
1643 l_reporting_format VARCHAR2(30);
1644 l_ld_sp VARCHAR2(1) := 'Y';
1645 l_message VARCHAR2(2000);
1646 l_encoding VARCHAR2(20);
1647 l_message_acct VARCHAR2(1000);
1648
1649 CURSOR format (p_format VARCHAR2) IS
1650 SELECT meaning
1651 FROM ar_lookups
1652 WHERE lookup_type = 'AR_ARXCUABR_REPORTING_FORMAT'
1653 AND lookup_code = p_format;
1654
1655
1656 CURSOR all_value IS
1657 SELECT meaning
1658 FROM ar_lookups
1659 WHERE lookup_code ='ALL'
1660 AND lookup_type ='ALL';
1661
1662 BEGIN
1663
1664 debug('ar_cumulative_balance_report.generate_xml()+');
1665 debug('p_reporting_level : ' || p_reporting_level);
1666 debug('p_reporting_entity_id : ' || p_reporting_entity_id);
1667 debug('p_reporting_format : ' || p_reporting_format);
1668 debug('p_sob_id : ' || p_sob_id);
1669 debug('p_coa_id : ' || p_coa_id);
1670 debug('p_co_seg_low : ' || p_co_seg_low );
1671 debug('p_co_seg_high : ' || p_co_seg_high);
1672 debug('p_gl_account_low : ' || p_gl_account_low);
1673 debug('p_gl_account_high : ' || p_gl_account_high);
1674 debug('p_refresh_tables : ' || p_refresh);
1675
1676 debug('p_gl_as_of_date : ' || p_gl_as_of_date);
1677 l_gl_as_of_date := TRUNC(TO_DATE(p_gl_as_of_date, 'YYYY-MM-DD HH24:MI:SS'));
1678 debug( 'l_gl_as_of_date : ' || l_gl_as_of_date);
1679 /* Start Bug 6502401: Get SOB and Chart of Accounts ID */
1680 IF p_reporting_level = 1000 THEN
1681 SELECT sob.name sob_name,
1682 sob.set_of_books_id,
1683 sob.currency_code functional_currency,
1684 sob.chart_of_accounts_id
1685 INTO l_sob_name,
1686 l_sob_id,
1687 l_functional_currency,
1688 l_chart_of_accounts_id
1689 FROM gl_sets_of_books sob
1690 WHERE sob.set_of_books_id = p_reporting_entity_id;
1691
1692 ELSIF p_reporting_level = 3000 THEN
1693 SELECT sob.name sob_name,
1694 sob.set_of_books_id,
1695 sob.currency_code functional_currency,
1696 sob.chart_of_accounts_id,
1697 substr(hou.name,1,60) organization
1698 INTO l_sob_name,
1699 l_sob_id,
1700 l_functional_currency,
1701 l_chart_of_accounts_id,
1702 l_organization
1703 FROM gl_sets_of_books sob,
1704 ar_system_parameters_all sysparam,
1705 hr_organization_units hou
1706 WHERE sob.set_of_books_id = sysparam.set_of_books_id
1707 AND hou.organization_id = sysparam.org_id
1708 AND sysparam.org_id = p_reporting_entity_id;
1709 END IF;
1710 /* End Bug 6502401 */
1711 -- initialize the reporting context
1712 init(p_sob_id);
1713
1714 -- set the org conditions
1715 xla_mo_reporting_api.initialize(
1716 p_reporting_level => p_reporting_level,
1717 p_reporting_entity_id => p_reporting_entity_id,
1718 p_pred_type => 'AUTO');
1719
1720 OPEN format(p_reporting_format);
1721 FETCH format INTO l_reporting_format;
1722 CLOSE format;
1723
1724 debug( 'getting where clause');
1725
1726 g_dist_org_where := xla_mo_reporting_api.get_predicate('dist',NULL);
1727 g_trx_org_where := xla_mo_reporting_api.get_predicate('trx',NULL);
1728 g_type_org_where := xla_mo_reporting_api.get_predicate('ctt',NULL);
1729 g_ard_org_where := xla_mo_reporting_api.get_predicate('ard',NULL);
1730 g_crh_org_where := xla_mo_reporting_api.get_predicate('crh',NULL);
1731 g_cr_org_where := xla_mo_reporting_api.get_predicate('cr',NULL);
1732 g_rm_org_where := xla_mo_reporting_api.get_predicate('rm',NULL);
1733 g_rec_org_where := xla_mo_reporting_api.get_predicate('ra',NULL);
1734 g_mcd_org_where := xla_mo_reporting_api.get_predicate('mcd',NULL);
1735 g_adj_org_where := xla_mo_reporting_api.get_predicate('adj',NULL);
1736 g_br_org_where := xla_mo_reporting_api.get_predicate('br',NULL);
1737 g_sys_org_where := xla_mo_reporting_api.get_Predicate('sys',NULL);
1738 g_balances_where := xla_mo_reporting_api.get_Predicate('bal',NULL);
1739
1740 debug( 'before: g_sys_org_where : ' || g_sys_org_where);
1741
1742 -- replacing with actual reporting entity id
1743
1744 g_dist_org_where := replace(g_dist_org_where, ':p_reporting_entity_id',
1745 p_reporting_entity_id);
1746 g_trx_org_where := replace(g_trx_org_where, ':p_reporting_entity_id',
1747 p_reporting_entity_id);
1748 g_type_org_where := replace(g_type_org_where, ':p_reporting_entity_id',
1749 p_reporting_entity_id);
1750 g_ard_org_where := replace(g_ard_org_where, ':p_reporting_entity_id',
1751 p_reporting_entity_id);
1752 g_crh_org_where := replace(g_crh_org_where, ':p_reporting_entity_id',
1753 p_reporting_entity_id);
1754 g_cr_org_where := replace(g_cr_org_where, ':p_reporting_entity_id',
1755 p_reporting_entity_id);
1756 g_rm_org_where := replace(g_rm_org_where, ':p_reporting_entity_id',
1757 p_reporting_entity_id);
1758 g_rec_org_where := replace(g_rec_org_where, ':p_reporting_entity_id',
1759 p_reporting_entity_id);
1760 g_mcd_org_where := replace(g_mcd_org_where, ':p_reporting_entity_id',
1761 p_reporting_entity_id);
1762 g_adj_org_where := replace(g_adj_org_where, ':p_reporting_entity_id',
1763 p_reporting_entity_id);
1764 g_br_org_where := replace(g_br_org_where, ':p_reporting_entity_id',
1765 p_reporting_entity_id);
1766 g_sys_org_where := replace(g_sys_org_where, ':p_reporting_entity_id',
1767 p_reporting_entity_id);
1768 g_balances_where := replace(g_balances_where, ':p_reporting_entity_id',
1769 p_reporting_entity_id);
1770
1771 debug('g_dist_org_where: ' || g_dist_org_where);
1772 debug('g_trx_org_where: ' || g_trx_org_where);
1773 debug('g_type_org_where: ' || g_type_org_where);
1774 debug('g_ard_org_where: ' || g_ard_org_where);
1775 debug('g_crh_org_where: ' || g_crh_org_where);
1776 debug('g_cr_org_where: ' || g_cr_org_where);
1777 debug('g_rm_org_where: ' || g_rm_org_where);
1778 debug('g_rec_org_where: ' || g_rec_org_where);
1779 debug('g_mcd_org_where: ' || g_mcd_org_where);
1780 debug('g_adj_org_where: ' || g_adj_org_where);
1781 debug('g_br_org_where: ' || g_br_org_where);
1782 debug('g_sys_org_where: ' || g_sys_org_where);
1783 debug('g_balances_where: ' || g_balances_where);
1784
1785 l_reporting_entity_name :=
1786 substrb(xla_mo_reporting_api.get_reporting_entity_name,1,80);
1787 l_reporting_level_name :=
1788 substrb(xla_mo_reporting_api.get_reporting_level_name,1,30);
1789
1790 debug( 'l_reporting_entity_name : ' || l_reporting_entity_name);
1791 debug( 'l_reporting_level_name : ' || l_reporting_level_name);
1792
1793
1794 /* Multi Org Uptake: Show appropriate message to the user depending upon the security profile */
1795 IF p_reporting_level = '1000' THEN
1796 l_ld_sp:= mo_utils.check_ledger_in_sp(p_reporting_entity_id);
1797 END IF;
1798
1799 IF l_ld_sp = 'N' THEN
1800 FND_MESSAGE.SET_NAME('FND','FND_MO_RPT_PARTIAL_LEDGER');
1801 l_message := FND_MESSAGE.get;
1802 END IF;
1803
1804 /* Bug fix 4942083*/
1805 IF arp_util.Open_Period_Exists(p_reporting_level,
1806 p_reporting_entity_id,
1807 l_gl_as_of_date) THEN
1808 FND_MESSAGE.SET_NAME('AR','AR_REPORT_ACC_NOT_GEN');--Changed as per Bug 5578884 the parameter to AR from FND as the message is in AR product
1809 l_message_acct := FND_MESSAGE.Get;
1810 END IF;
1811
1812
1813 -- Populate 'ALL' if it is run for SOB
1814 IF p_reporting_level <> '3000' THEN
1815 OPEN all_value;
1816 FETCH all_value INTO l_organization;
1817 CLOSE all_value;
1818 END IF;
1819
1820 debug( 'SOB Name: ' || l_sob_name);
1821 debug( 'Func Currency: ' || l_functional_currency);
1822 debug( 'Org: ' || l_organization);
1823 debug( 'Chart of Accounts: ' || l_chart_of_accounts_id);
1824
1825 populate_data(
1826 p_reporting_level => p_reporting_level,
1827 p_reporting_entity_id => p_reporting_entity_id,
1828 p_reporting_format => p_reporting_format,
1829 p_chart_of_accounts_id => l_chart_of_accounts_id,
1830 p_sob_id => l_sob_id,
1831 p_coa_id => l_chart_of_accounts_id,
1832 p_co_seg_low => p_co_seg_low,
1833 p_co_seg_high => p_co_seg_high,
1834 p_gl_as_of_date => l_gl_as_of_date,
1835 p_gl_account_low => p_gl_account_low,
1836 p_gl_account_high => p_gl_account_high,
1837 p_refresh_tables => p_refresh);
1838
1839 debug( 'returned from populate_data');
1840
1841 perform_updates;
1842
1843 l_base_query :=
1844 'SELECT
1845 decode (''' || p_reporting_format || ''', ''GL_ACCOUNT'',
1846 gl_account, natural_account) gl_account,
1847 trx_type,
1848 trx_number,
1849 to_char(trx_date,''YYYY-MM-DD'') trx_date,
1850 to_char(max(activity_gl_date),''YYYY-MM-DD'') activity_gl_date,
1851 currency,
1852 decode(sign(sum(nvl(acctd_amount_dr,0))-
1853 sum(nvl(acctd_amount_cr,0))), -1, 0,
1854 sum(nvl(acctd_amount_dr,0))-sum(nvl(acctd_amount_cr,0)))
1855 acctd_amount_dr,
1856 decode(sign(sum(nvl(acctd_amount_cr,0))-
1857 sum(nvl(acctd_amount_dr,0))), -1, 0,
1858 sum(nvl(acctd_amount_cr,0))-sum(nvl(acctd_amount_dr,0)))
1859 acctd_amount_cr,
1860 decode(sign(sum(nvl(amount_dr,0))-sum(nvl(amount_cr,0))), -1, 0,
1861 sum(nvl(amount_dr,0))-sum(nvl(amount_cr,0)))
1862 amount_dr,
1863 decode(sign(sum(nvl(amount_cr,0))-sum(nvl(amount_dr,0))), -1, 0,
1864 sum(nvl(amount_cr,0))-sum(nvl(amount_dr,0)))
1865 amount_cr
1866 FROM ar_base_gl_acct_balances bal,
1867 gl_code_combinations gcc
1868 WHERE bal.code_combination_id = gcc.code_combination_id ';
1869
1870 l_recent_query :=
1871 'SELECT
1872 decode (''' || p_reporting_format || ''', ''GL_ACCOUNT'',
1873 gl_account, natural_account) gl_account,
1874 trx_type,
1875 trx_number,
1876 to_char(trx_date,''YYYY-MM-DD'') trx_date,
1877 to_char(max(activity_gl_date),''YYYY-MM-DD'') activity_gl_date,
1878 currency,
1879 decode(sign(sum(nvl(acctd_amount_dr,0))-
1880 sum(nvl(acctd_amount_cr,0))), -1, 0,
1881 sum(nvl(acctd_amount_dr,0))-sum(nvl(acctd_amount_cr,0)))
1882 acctd_amount_dr,
1883 decode(sign(sum(nvl(acctd_amount_cr,0))-
1884 sum(nvl(acctd_amount_dr,0))), -1, 0,
1885 sum(nvl(acctd_amount_cr,0))-sum(nvl(acctd_amount_dr,0)))
1886 acctd_amount_cr,
1887 decode(sign(sum(nvl(amount_dr,0))-sum(nvl(amount_cr,0))), -1, 0,
1888 sum(nvl(amount_dr,0))-sum(nvl(amount_cr,0)))
1889 amount_dr,
1890 decode(sign(sum(nvl(amount_cr,0))-sum(nvl(amount_dr,0))), -1, 0,
1891 sum(nvl(amount_cr,0))-sum(nvl(amount_dr,0)))
1892 amount_cr
1893 FROM ar_gl_acct_balances bal,
1894 gl_code_combinations gcc
1895 WHERE bal.code_combination_id = gcc.code_combination_id ';
1896
1897 -- l_dummy_where := ' WHERE 1=1 ';
1898 l_group_by := ' GROUP BY gl_account, natural_account, trx_type,
1899 trx_number, trx_date, currency
1900 HAVING sum(nvl(acctd_amount_dr,0)) -
1901 sum(nvl(acctd_amount_cr,0)) > 0
1902 OR
1903 sum(nvl(acctd_amount_cr,0)) -
1904 sum(nvl(acctd_amount_dr,0)) > 0 ';
1905
1906 --l_group_by := ' GROUP BY gl_account, natural_account, trx_type,
1907 -- trx_number, trx_date, currency ';
1908
1909 l_base_query := l_base_query || g_balances_where;
1910 l_recent_query := l_recent_query || g_balances_where;
1911
1912 -- build the other WHERE clauses
1913
1914 l_gl_account_where := get_seg_condition (
1915 p_qualifier => 'ALL',
1916 p_seg_low => p_gl_account_low,
1917 p_seg_high => p_gl_account_high,
1918 p_coa_id => p_coa_id);
1919
1920 -- debug(l_gl_account_where);
1921
1922 l_company_where := get_seg_condition (
1923 p_qualifier => 'GL_BALANCING',
1924 p_seg_low => p_co_seg_low,
1925 p_seg_high => p_co_seg_high,
1926 p_coa_id => p_coa_id);
1927
1928 -- debug(l_company_where);
1929
1930 l_base_query := l_base_query || l_gl_account_where || l_company_where;
1931 l_recent_query := l_recent_query || l_gl_account_where || l_company_where;
1932
1933 l_xml_query := l_base_query || l_group_by || ' UNION ALL ' ||
1934 l_recent_query || l_group_by;
1935
1936 -- l_xml_query := l_recent_query || l_group_by;
1937
1938 debug('xmlquery:');
1939 debug(l_xml_query);
1940
1941 -- get database version
1942 dbms_utility.db_version(
1943 version => l_version,
1944 compatibility => l_compatibility);
1945
1946 l_majorVersion := to_number(substr(l_version, 1, instr(l_version,'.')-1));
1947
1948 debug('DB version : ' || l_majorVersion);
1949
1950 IF (l_majorVersion > 8 and l_majorVersion < 9) THEN
1951
1952 BEGIN
1953 queryCtx := DBMS_XMLQuery.newContext(l_xml_query);
1954 dbms_xmlquery.setRaiseNoRowsException(queryCtx,TRUE);
1955 debug('calling getxml');
1956 l_result := DBMS_XMLQuery.getXML(queryCtx);
1957 debug('returned from getxml');
1958 dbms_xmlquery.closeContext(queryCtx);
1959 l_rows_processed := 1;
1960
1961 EXCEPTION WHEN OTHERS THEN
1962 dbms_xmlquery.getexceptioncontent(queryCtx,l_errNo,l_errMsg);
1963 IF l_errNo = 1403 THEN
1964 l_rows_processed := 0;
1965 END IF;
1966 dbms_xmlquery.closecontext(queryCtx);
1967 END;
1968
1969 ELSIF (l_majorVersion >= 9 ) THEN
1970
1971 qryctx := dbms_xmlgen.newcontext(l_xml_query);
1972 debug('calling getxml');
1973 l_result := dbms_xmlgen.getxml(qryctx,dbms_xmlgen.none);
1974 debug('returned from getxml');
1975 l_rows_processed := dbms_xmlgen.getnumrowsprocessed(qryctx);
1976 debug('rows prcessed: ' || l_rows_processed);
1977 dbms_xmlgen.closecontext(qryCtx);
1978
1979 END IF;
1980
1981 debug('XML generation done: ' || l_rows_processed);
1982
1983 IF l_rows_processed <> 0 THEN
1984 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
1985 tempResult := l_result;
1986 ELSE
1987 l_resultOffset := 0;
1988 END IF;
1989
1990 -- Prepare the tag for the report heading
1991 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
1992 l_xml_header := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
1993 l_xml_header := l_xml_header || l_new_line ||'<ARXCUABR>';
1994 l_xml_header := l_xml_header || l_new_line ||' <MSG_TXT>'||l_message||'</MSG_TXT>';
1995 l_xml_header := l_xml_header || l_new_line ||' <MSG_TXT_ACCT>'||l_message_acct||'</MSG_TXT_ACCT>';
1996 l_xml_header := l_xml_header || l_new_line ||' <PARAMETERS>';
1997 l_xml_header := l_xml_header || l_new_line ||' <REPORTING_LEVEL>'
1998 || l_reporting_level_name || '</REPORTING_LEVEL>';
1999 l_xml_header := l_xml_header || l_new_line ||' <REPORTING_ENTITY>'
2000 || l_reporting_entity_name || '</REPORTING_ENTITY>';
2001 l_xml_header := l_xml_header || l_new_line ||' <REPORTING_FORMAT>'
2002 || l_reporting_format || '</REPORTING_FORMAT>';
2003 l_xml_header := l_xml_header || l_new_line || ' <SOB_ID>'
2004 || p_sob_id ||'</SOB_ID>';
2005 l_xml_header := l_xml_header || l_new_line || ' <FUNCTIONAL_CURRENCY>'
2006 || l_functional_currency ||'</FUNCTIONAL_CURRENCY>';
2007 l_xml_header := l_xml_header || l_new_line || ' <CO_SEG_LOW>'
2008 || p_co_seg_low || '</CO_SEG_LOW>';
2009 l_xml_header := l_xml_header || l_new_line || ' <CO_SEG_HIGH>'
2010 || p_co_seg_high ||'</CO_SEG_HIGH>';
2011 l_xml_header := l_xml_header || l_new_line || ' <GL_AS_OF_DATE>'
2012 ||to_char(fnd_date.canonical_to_date(p_gl_as_of_date),'YYYY-MM-DD')
2013 || '</GL_AS_OF_DATE>';
2014 l_xml_header := l_xml_header || l_new_line || ' <GL_ACCOUNT_LOW>'
2015 || p_gl_account_low || '</GL_ACCOUNT_LOW>';
2016 l_xml_header := l_xml_header || l_new_line || ' <GL_ACCOUNT_HIGH>'
2017 || p_gl_account_high ||'</GL_ACCOUNT_HIGH>';
2018 l_xml_header := l_xml_header || l_new_line || ' <REFRESH_TABLE>'
2019 || p_refresh ||'</REFRESH_TABLE>';
2020 l_xml_header := l_xml_header || l_new_line || ' <NUM_ROWS>'
2021 || l_rows_processed || '</NUM_ROWS>';
2022 l_xml_header := l_xml_header || l_new_line || ' </PARAMETERS>';
2023 l_xml_header := l_xml_header || l_new_line || ' <REPORT_HEADING>';
2024 l_xml_header := l_xml_header || l_new_line || ' <SET_OF_BOOKS>'
2025 || l_sob_name ||'</SET_OF_BOOKS>';
2026 l_xml_header := l_xml_header || l_new_line || ' <ORGANIZATION>'
2027 || l_organization||'</ORGANIZATION>';
2028 l_xml_header := l_xml_header || l_new_line || ' </REPORT_HEADING>';
2029
2030 l_close_tag := l_new_line||'</ARXCUABR>' || l_new_line;
2031 l_xml_header_length := length(l_xml_header);
2032
2033 debug('Header created');
2034
2035 IF l_rows_processed <> 0 THEN
2036 dbms_lob.write(tempResult,l_xml_header_length,1,l_xml_header);
2037 dbms_lob.copy(tempResult, l_result,
2038 dbms_lob.getlength(l_result)-l_resultOffset,
2039 l_xml_header_length,l_resultOffset);
2040 ELSE
2041 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
2042 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
2043 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
2044 END IF;
2045
2046 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
2047 process_clob(tempResult);
2048 p_result := tempResult;
2049
2050 debug('ar_cumulative_balance_report.generate_xml()-');
2051
2052 EXCEPTION
2053 WHEN NO_DATA_FOUND THEN
2054 debug('EXCEPTION: NO_DATA_FOUND generate_xml');
2055 debug(sqlcode);
2056 debug(sqlerrm);
2057 RAISE;
2058
2059 WHEN OTHERS THEN
2060 debug('EXCEPTION: OTHERS generate_xml');
2061 debug(sqlcode);
2062 debug(sqlerrm);
2063 RAISE;
2064
2065 END generate_xml;
2066
2067
2068 -- Package constructor
2069 BEGIN
2070
2071 NULL;
2072
2073 EXCEPTION
2074 WHEN NO_DATA_FOUND THEN
2075 debug('EXCEPTION: ar_cumulative_balance_report.initialize');
2076 RAISE;
2077
2078 WHEN OTHERS THEN
2079 debug('EXCEPTION: ar_cumulative_balance_report.initialize');
2080 RAISE;
2081
2082 END ar_cumulative_balance_report;