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