[Home] [Help]
PACKAGE BODY: APPS.ARP_RECON_REP
Source
1 PACKAGE BODY ARP_RECON_REP as
2 /* $Header: ARGLRECB.pls 120.49.12020000.9 2013/04/02 02:34:14 kkikkise ship $ */
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5 LOG_LEVEL varchar2(1) := NVL(FND_PROFILE.value('AFLOG_LEVEL'), '6');
6
7 PROCEDURE log(msg_txt IN VARCHAR2) IS
8 BEGIN
9 IF (PG_DEBUG='Y' AND LOG_LEVEL='1') THEN
10 FND_FILE.put_line(fnd_file.log,msg_txt);
11 END IF;
12 END log;
13
14 /*========================================================================+
15 Function which returns the global variable g_reporting_level
16 ========================================================================*/
17
18 FUNCTION get_reporting_level return VARCHAR2 is
19 BEGIN
20 return arp_recon_rep.var_tname.g_reporting_level;
21 END get_reporting_level;
22
23 /*========================================================================+
24 Function which returns the global variable g_reporting_entity_id
25 ========================================================================*/
26
27 FUNCTION get_reporting_entity_id return NUMBER is
28 BEGIN
29 return arp_recon_rep.var_tname.g_reporting_entity_id;
30 END get_reporting_entity_id;
31
32 /*========================================================================+
33 Function which returns the global variable g_set_of_books_id
34 ========================================================================*/
35
36 FUNCTION get_set_of_books_id return NUMBER is
37 BEGIN
38 return arp_recon_rep.var_tname.g_set_of_books_id;
39 END get_set_of_books_id;
40
41 /*========================================================================+
42 Function which returns the global variable g_chart_of_accounts_id
43 ========================================================================*/
44
45 FUNCTION get_chart_of_accounts_id return NUMBER is
46 BEGIN
47 return arp_recon_rep.var_tname.g_chart_of_accounts_id;
48 END get_chart_of_accounts_id;
49
50 /*========================================================================+
51 Function which returns the global variable g_gl_date_from
52 ========================================================================*/
53
54 FUNCTION get_gl_date_from return DATE is
55 BEGIN
56 return arp_recon_rep.var_tname.g_gl_date_from;
57 END get_gl_date_from;
58
59 /*========================================================================+
60 Function which returns the global variable g_gl_date_to
61 ========================================================================*/
62
63 FUNCTION get_gl_date_to return DATE is
64 BEGIN
65 return arp_recon_rep.var_tname.g_gl_date_to;
66 END get_gl_date_to;
67
68 /*========================================================================+
69 Function which returns the global variable g_posting_status
70 ========================================================================*/
71
72 FUNCTION get_posting_status return VARCHAR2 is
73 BEGIN
74 return arp_recon_rep.var_tname.g_posting_status;
75 END get_posting_status;
76
77 /*========================================================================+
78 Function which returns the maximum gl_date possible
79 ========================================================================*/
80
81 FUNCTION get_max_gl_date return DATE IS
82 BEGIN
83 return arp_recon_rep.var_tname.g_max_gl_date;
84 END get_max_gl_date;
85
86 /*========================================================================+
87 Function which returns the period name
88 ========================================================================*/
89
90 FUNCTION get_period_name return VARCHAR2 IS
91 BEGIN
92 return arp_recon_rep.var_tname.g_period_name;
93 END get_period_name;
94
95 /*========================================================================+
96 Function which returns the functional currency
97 ========================================================================*/
98
99 FUNCTION get_functional_currency return VARCHAR2 IS
100 BEGIN
101 return arp_recon_rep.var_tname.g_functional_currency;
102 END get_functional_currency;
103
104 /*========================================================================+
105 Function which returns the value of g_out_of_balance_only
106 ========================================================================*/
107
108 FUNCTION get_out_of_balance_only return VARCHAR2 IS
109 BEGIN
110 return arp_recon_rep.var_tname.g_out_of_balance_only;
111 END get_out_of_balance_only;
112
113 /*========================================================================+
114 | PUBLIC PROCEDURE GET_DETAIL_ACCOUNTS |
115 | |
116 | DESCRIPTION |
117 | |
118 | This procedure is used to generate the list of account segments to |
119 | be queried for a given summary account |
120 | |
121 | PSEUDO CODE/LOGIC |
122 | |
123 | PARAMETERS |
124 | |
125 | |
126 | KNOWN ISSUES |
127 | |
128 | NOTES |
129 | |
130 | |
131 | MODIFICATION HISTORY |
132 | Date Author Description of Changes |
133 | 16-NOV-2004 rkader Created |
134 | |
135 *=======================================================================*/
136
137 /* Build the where condition for a single parent
138 Package private procedure */
139 PROCEDURE build_where_clause(p_code_combinations OUT NOCOPY VARCHAR2) is
140
141 l_count binary_integer;
142 i binary_integer;
143
144 BEGIN
145 log('build_where_clause (+)');
146
147 p_code_combinations := ' ';
148
149 l_count := detail.count;
150
151 FOR i in 1..l_count LOOP
152 p_code_combinations := p_code_combinations || ''''||detail(i)||'''';
153 IF i <> l_count THEN
154 p_code_combinations := p_code_combinations ||' , ';
155 END IF;
156 END LOOP;
157
158 log('build_where_clause (-)');
159 END build_where_clause;
160
161 PROCEDURE get_detail_accounts(p_value_set_id IN NUMBER,
162 p_parent_value IN VARCHAR2,
163 p_code_combinations OUT NOCOPY VARCHAR2)
164 IS
165 parent FLEX_TABLE;
166 i BINARY_INTEGER := 1;
167 j BINARY_INTEGER := 0;
168 listed BOOLEAN := FALSE;
169 l_count NUMBER ;
170
171 Cursor FlexCursor (c_value varchar2) is
172 Select flex_value,
173 summary_flag
174 from fnd_flex_value_children_v
175 where flex_value_set_id = p_value_set_id
176 and parent_flex_value = c_value;
177
178 BEGIN
179
180 log('get_detail_accounts (+)');
181 arp_standard.debug('get_detail_accounts (+)');
182
183 /* Algorithm:
184 Read the details for the incoming parent value. If the detail of the incoming parent is also
185 a parent, store it. Remove the current parent from the list once the child accounts are
186 retrieved. Recurse until no parents remain. */
187
188 parent(i) := p_parent_value;
189
190 WHILE i > 0 LOOP
191 FOR r in FlexCursor(parent(1)) LOOP
192 IF r.summary_flag = 'Y' THEN
193 /* store the parent value for later use */
194 i := i + 1;
195 parent(i) := r.flex_value;
196 ELSE
197 /* This is a detail account. Store it */
198
199 /* avoid duplicating any flex_values */
200 listed := FALSE;
201 FOR x in 1..j LOOP
202 IF detail(x) = r.Flex_value THEN
203 listed := TRUE;
204 exit;
205 END IF;
206 END LOOP;
207 IF NOT listed THEN
208 j := j + 1;
209 detail(j) := r.flex_value;
210 END IF;
211 END IF;
212 END LOOP;
213
214 /* shift all parents up one level
215 and decrement the parent index */
216
217 FOR x in 1..(i-1) LOOP
218 parent(x) := parent(x+1);
219 END LOOP;
220 parent.DELETE(i);
221 i := i - 1;
222 END LOOP;
223
224 l_count := detail.count;
225
226 arp_standard.debug('No of rows : '|| l_count);
227
228 IF l_count = 0 THEN
229 detail(1) := p_parent_value;
230 END IF;
231
232 /* Build the string of the code combinations */
233
234 build_where_clause(p_code_combinations);
235
236 arp_standard.debug('p_code_combinations :' ||p_code_combinations);
237
238 arp_standard.debug('get_detail_accounts (-)');
239 log('get_detail_accounts (-)');
240 EXCEPTION
241 WHEN OTHERS THEN
242 arp_standard.debug(sqlcode);
243 arp_standard.debug(sqlerrm);
244 arp_standard.debug('get_detail_accounts (EXCEPTION)');
245 END get_detail_accounts;
246
247
248 /*========================================================================+
249 | PUBLIC PROCEDURE INIT |
250 | |
251 | DESCRIPTION |
252 | |
253 | This procedure is used to initialize the reporting context. This |
254 | procedure sets the table names to be used in the queries.
255 | |
256 | PSEUDO CODE/LOGIC |
257 | |
258 | PARAMETERS |
259 | |
260 | |
261 | KNOWN ISSUES |
262 | |
263 | NOTES |
264 | |
265 | |
266 | MODIFICATION HISTORY |
267 | Date Author Description of Changes |
268 | 16-NOV-2004 rkader Created |
269 | |
270 *=======================================================================*/
271
272 PROCEDURE INIT(p_set_of_books_id IN NUMBER) IS
273 BEGIN
274 log('INIT (+)');
275 /*
276 * When the report is run for reporting book client info will be set for the particular
277 * set of books ID. We will use that information to determine which sql statements to
278 * execute. The checking of profile is done to make sure that if the user submits the
279 * report from reporting responsibility it would still work and in this case even though
280 * report is run for reporting book we still need to point to regular AR views
281 */
282 /* Set the table names based on the sob type */
283 arp_recon_rep.var_tname.l_ar_system_parameters_all := 'ar_system_parameters';
284 arp_recon_rep.var_tname.l_ar_payment_schedules_all := 'ar_payment_schedules';
285 arp_recon_rep.var_tname.l_ar_adjustments_all := 'ar_adjustments';
286 arp_recon_rep.var_tname.l_ar_cash_receipt_history_all := 'ar_cash_receipt_history';
287 arp_recon_rep.var_tname.l_ar_batches_all := 'ar_batches';
288 arp_recon_rep.var_tname.l_ar_cash_receipts_all := 'ar_cash_receipts';
289
290 -- BUG#4429368 Replace ar_distributions_all by ar_xla_ard_lines_v
291 arp_recon_rep.var_tname.l_ar_distributions_all := 'ar_xla_ard_lines_v';
292
293 arp_recon_rep.var_tname.l_ra_customer_trx_all := 'ra_customer_trx';
294 arp_recon_rep.var_tname.l_ra_batches_all := 'ra_batches';
295
296 -- BUG#4429368 Replace ra_cust_trx_line_gl_dist_all by ar_xla_ctlgd_lines_v
297 arp_recon_rep.var_tname.l_ra_cust_trx_gl_dist_all := 'ar_xla_ctlgd_lines_v';
298
299 arp_recon_rep.var_tname.l_ar_misc_cash_dists_all := 'ar_misc_cash_distributions';
300 arp_recon_rep.var_tname.l_ar_rate_adjustments_all := 'ar_rate_adjustments';
301 arp_recon_rep.var_tname.l_ar_receivable_apps_all := 'ar_receivable_applications';
302
303 log('INIT (-)');
304
305 END INIT;
306
307 /*========================================================================+
308 Bug fix: 4708930
309 Function which replaces the special characters in the strings to form
310 a valid XML string
311 +========================================================================*/
312 FUNCTION format_string(p_string varchar2) return varchar2 IS
313
314 l_string varchar2(2000);
315 BEGIN
316
317 l_string := replace(p_string,'&','&'||'amp;');
318 l_string := replace(l_string,'<','&'||'lt;');
319 l_string := replace(l_string,'>','&'||'gt;');
320
321 RETURN l_string;
322
323 END format_string;
324
325 /*========================================================================+
326 | PUBLIC PROCEDURE ARADJ_JOURNAL_LOAD_XML |
327 | |
328 | DESCRIPTION |
329 | |
330 | This procedure is used to generate the XML data required for reporting|
331 | Adjustments Journals |
332 | |
333 | PSEUDO CODE/LOGIC |
334 | |
335 | PARAMETERS |
336 | |
337 | |
338 | KNOWN ISSUES |
339 | |
340 | NOTES |
341 | |
342 | |
343 | MODIFICATION HISTORY |
344 | Date Author Description of Changes |
345 | 03-FEB-2004 rkader Created |
346 | |
347 *=======================================================================*/
348 PROCEDURE aradj_journal_load_xml (
349 p_reporting_level IN VARCHAR2,
350 p_reporting_entity_id IN NUMBER,
351 p_sob_id IN NUMBER,
352 p_coa_id IN NUMBER,
353 p_co_seg_low IN VARCHAR2,
354 p_co_seg_high IN VARCHAR2,
355 p_gl_date_from IN VARCHAR2,
356 p_gl_date_to IN VARCHAR2,
357 p_posting_status IN VARCHAR2,
358 p_gl_account_low IN VARCHAR2,
359 p_gl_account_high IN VARCHAR2,
360 p_summary_account IN NUMBER,
361 p_receivable_mode IN VARCHAR2,
362 p_result OUT NOCOPY CLOB) IS
363
364 l_result CLOB;
365 tempResult CLOB;
366 l_version varchar2(20);
367 l_compatibility varchar2(20);
368 l_suffix varchar2(2);
369 l_majorVersion number;
370 l_resultOffset number;
371 l_xml_header varchar2(3000);
372 l_xml_header_length number;
373 l_errNo NUMBER;
374 l_errMsg VARCHAR2(200);
375 queryCtx DBMS_XMLquery.ctxType;
376 qryCtx DBMS_XMLGEN.ctxHandle;
377 l_xml_query VARCHAR2(32767);
378 l_natural_segment_col VARCHAR2(50);
379 l_flex_value_set_id NUMBER;
380 l_code_combinations VARCHAR2(32767);
381 TYPE ref_cur IS REF CURSOR;
382 l_xml_stmt ref_cur;
383 l_rows_processed NUMBER;
384 l_new_line VARCHAR2(1);
385 l_coa_id NUMBER; /*bug fix 5654975 */
386 /* Variables to hold the report heading */
387 l_sob_id NUMBER;
388 l_sob_name VARCHAR2(100);
389 l_functional_currency VARCHAR2(15);
390 l_organization VARCHAR2(60);
391 l_format VARCHAR2(40);
392 l_close_tag VARCHAR2(100);
393 l_reporting_entity_name VARCHAR2(80);
394 l_reporting_level_name VARCHAR2(30);
395 l_status_meaning VARCHAR2(30);
396 l_receivable_mode_meaning VARCHAR2(10);
397 /* Variables to hold the where clause based on the input parameters*/
398 /* Increased variables length to 32767 for bug 5654975 */
399 /* Variables data length changed from 200 to 500 to address bug:5184277*/
400 l_adj_org_where VARCHAR2(32767);
401 l_ard_org_where VARCHAR2(32767);
402 l_trx_org_where VARCHAR2(32767);
403 l_pay_org_where VARCHAR2(32767);
404 l_rec_org_where VARCHAR2(32767);
405 l_type_org_where VARCHAR2(32767);
406 l_sysparam_org_where VARCHAR2(32767);
407 /*Changes to address bug:5184277 ends*/
408
409 l_co_seg_where VARCHAR2(32767);
410 l_account_where VARCHAR2(32767);
411 l_account_seg_where VARCHAR2(32767);
412 l_gl_date_where VARCHAR2(1000);
413 l_source_type_where VARCHAR2(32767);
414 l_posting_status_where VARCHAR2(1000);
415 l_report_date VARCHAR2(25);
416 l_ld_sp VARCHAR2(1) := 'Y';
417 l_message VARCHAR2(2000);
418 l_encoding VARCHAR2(20);
419 l_message_acct VARCHAR2(1000);
420 BEGIN
421
422 log ('aradj_journal_load_xml (+)');
423
424 /* Assign the input parameters to the global variables */
425 arp_recon_rep.var_tname.g_reporting_level := p_reporting_level;
426 arp_recon_rep.var_tname.g_reporting_entity_id := p_reporting_entity_id;
427 /* bug 5654975 p_coa_id,p_sob_id is passed incorrectly when the user
428 has access to multiple Ledgers */
429 -- arp_recon_rep.var_tname.g_set_of_books_id := p_sob_id;
430 -- arp_recon_rep.var_tname.g_chart_of_accounts_id := p_coa_id;
431 arp_recon_rep.var_tname.g_gl_date_from := fnd_date.canonical_to_date(p_gl_date_from);
432 arp_recon_rep.var_tname.g_gl_date_to := fnd_date.canonical_to_date(p_gl_date_to);
433 arp_recon_rep.var_tname.g_posting_status := p_posting_status;
434
435 /* Added Conditional Implication to address bug:5181586*/
436 IF p_reporting_level = 1000 THEN
437 SELECT sob.name sob_name,
438 sob.set_of_books_id,
439 sob.currency_code functional_currency,
440 sob.chart_of_accounts_id
441 INTO l_sob_name,
442 l_sob_id,
443 l_functional_currency,
444 l_coa_id
445 FROM gl_sets_of_books sob
446 WHERE sob.set_of_books_id = arp_recon_rep.var_tname.g_reporting_entity_id;
447
448 ELSIF p_reporting_level = 3000 THEN
449 SELECT sob.name sob_name,
450 sob.set_of_books_id,
451 sob.currency_code functional_currency,
452 sob.chart_of_accounts_id
453 INTO l_sob_name,
454 l_sob_id,
455 l_functional_currency,
456 l_coa_id
457 FROM gl_sets_of_books sob,
458 ar_system_parameters sysparam
459 WHERE sob.set_of_books_id = sysparam.set_of_books_id
460 AND sysparam.org_id = arp_recon_rep.var_tname.g_reporting_entity_id;
461
462 END IF;
463 /* Changes for bug:5181586 ends*/
464
468 /* Initialize the reporting context */
465 arp_recon_rep.var_tname.g_chart_of_accounts_id := l_coa_id;
466 arp_recon_rep.var_tname.g_set_of_books_id := l_sob_id;
467
469 init(p_sob_id);
470
471 /* Set the org conditions */
472 XLA_MO_REPORTING_API.Initialize(p_reporting_level,
473 p_reporting_entity_id,
474 'AUTO',
475 'N' /* use NVL */);
476
477 l_adj_org_where := XLA_MO_REPORTING_API.Get_Predicate('adj',NULL);
478 l_ard_org_where := XLA_MO_REPORTING_API.Get_Predicate('ard',NULL);
479 l_trx_org_where := XLA_MO_REPORTING_API.Get_Predicate('trx',NULL);
480 l_pay_org_where := XLA_MO_REPORTING_API.Get_Predicate('pay',NULL);
481 l_rec_org_where := XLA_MO_REPORTING_API.Get_Predicate('rec',NULL);
482 l_type_org_where := XLA_MO_REPORTING_API.Get_Predicate('type',NULL);
483 l_sysparam_org_where := XLA_MO_REPORTING_API.Get_Predicate('sysparam',NULL);
484
485 /* Replace the bind variables with global functions */
486 l_adj_org_where := replace(l_adj_org_where,
487 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
488 l_ard_org_where := replace(l_ard_org_where,
489 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
490 l_trx_org_where := replace(l_trx_org_where,
491 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
492 l_pay_org_where := replace(l_pay_org_where,
493 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
494 l_rec_org_where := replace(l_rec_org_where,
495 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
496 l_type_org_where := replace(l_type_org_where,
497 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
498 l_sysparam_org_where := replace(l_sysparam_org_where,
499 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
500
501
502 l_reporting_entity_name := substrb(XLA_MO_REPORTING_API.get_reporting_entity_name,1,80);
503 l_reporting_level_name := substrb(XLA_MO_REPORTING_API.get_reporting_level_name,1,30);
504
505 /* Multi Org Uptake: Show appropriate message to the user depending upon the security profile */
506 IF p_reporting_level = '1000' THEN
507 l_ld_sp:= mo_utils.check_ledger_in_sp(p_reporting_entity_id);
508 END IF;
509
510 IF l_ld_sp = 'N' THEN
511 FND_MESSAGE.SET_NAME('FND','FND_MO_RPT_PARTIAL_LEDGER');
512 l_message := FND_MESSAGE.get;
513 END IF;
514
515 /* Bug fix 4942083*/
516 IF arp_util.Open_Period_Exists(p_reporting_level,
517 p_reporting_entity_id,
518 arp_recon_rep.var_tname.g_gl_date_from,
519 arp_recon_rep.var_tname.g_gl_date_to) THEN
520 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
521 l_message_acct := FND_MESSAGE.Get;
522 END IF;
523
524 /* Get the org name */
525 IF p_reporting_level = '3000' THEN
526 select substrb(hou.name,1,60)
527 into l_organization
528 from hr_organization_units hou
529 where hou.organization_id = arp_recon_rep.var_tname.g_reporting_entity_id;
530 ELSE
531 select meaning
532 into l_organization
533 from ar_lookups
534 where lookup_code ='ALL' and lookup_type ='ALL';
535 END IF;
536
537 /* Build the WHERE clauses */
538 /*buf fix 5654975 Replaced p_coa_id with l_coa_id*/
539
540 IF p_co_seg_low IS NULL AND p_co_seg_high IS NULL THEN
541 l_co_seg_where := NULL;
542 ELSIF p_co_seg_low IS NULL THEN
543 l_co_seg_where := ' AND ' ||
544 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
545 p_id_flex_code => 'GL#',
546 p_id_flex_num => l_coa_id,
547 p_table_alias => 'GC',
548 p_mode => 'WHERE',
549 p_qualifier => 'GL_BALANCING',
550 p_function => '<=',
551 p_operand1 => p_co_seg_high);
552 ELSIF p_co_seg_high IS NULL THEN
553 l_co_seg_where := ' AND ' ||
554 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
555 p_id_flex_code => 'GL#',
556 p_id_flex_num => l_coa_id,
557 p_table_alias => 'GC',
558 p_mode => 'WHERE',
559 p_qualifier => 'GL_BALANCING',
560 p_function => '>=',
561 p_operand1 => p_co_seg_low);
562 ELSE
563 l_co_seg_where := ' AND ' ||
564 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
565 p_id_flex_code => 'GL#',
566 p_id_flex_num => l_coa_id,
567 p_table_alias => 'GC',
568 p_mode => 'WHERE',
569 p_qualifier => 'GL_BALANCING',
570 p_function => 'BETWEEN',
571 p_operand1 => p_co_seg_low,
572 p_operand2 => p_co_seg_high);
573 END IF;
574
575 IF p_gl_date_from IS NULL and p_gl_date_to IS NULL THEN
576 l_gl_date_where := NULL;
577 ELSIF p_gl_date_from IS NULL THEN
578 l_gl_date_where :=' and adj.gl_date <= arp_recon_rep.get_gl_date_to()';
579 ELSIF p_gl_date_to IS NULL THEN
580 l_gl_date_where :=' and adj.gl_date >= arp_recon_rep.get_gl_date_from() ' ;
581 ELSE
582 l_gl_date_where := ' and adj.gl_date between arp_recon_rep.get_gl_date_from() and arp_recon_rep.get_gl_date_to() ';
583 END IF;
584
585 IF p_gl_account_low IS NOT NULL AND p_gl_account_high IS NOT NULL THEN
586 l_account_where := ' AND ' || AR_CALC_AGING.FLEX_SQL(
587 p_application_id=> 101,
588 p_id_flex_code =>'GL#',
589 p_id_flex_num =>l_coa_id,
590 p_table_alias => 'gc',
591 p_mode => 'WHERE',
592 p_qualifier => 'ALL',
593 p_function=> 'BETWEEN',
594 p_operand1 => p_gl_account_low,
595 p_operand2 => p_gl_account_high);
596 ELSE
597 l_account_where := NULL;
598 END IF;
599
600 IF p_summary_account IS NOT NULL THEN
601 SELECT fcav.application_column_name, flex_value_set_id
602 INTO l_natural_segment_col , l_flex_value_set_id
603 FROM fnd_segment_attribute_values fcav,
604 fnd_id_flex_segments fifs
605 WHERE fcav.application_id = 101
606 AND fcav.id_flex_code = 'GL#'
607 AND fcav.id_flex_num = arp_recon_rep.var_tname.g_chart_of_accounts_id
608 AND fcav.attribute_value = 'Y'
609 AND fcav.segment_attribute_type = 'GL_ACCOUNT'
610 AND fifs.application_id = fcav.application_id
611 AND fifs.id_flex_code = fcav.id_flex_code
612 AND fifs.id_flex_num = fcav.id_flex_num
613 AND fcav.application_column_name = fifs.application_column_name;
614
615 get_detail_accounts(l_flex_value_set_id, p_summary_account, l_code_combinations);
616
617 l_account_seg_where := ' and gc.'||l_natural_segment_col||' in ('||l_code_combinations||' )';
618 ELSE
619 l_account_seg_where := NULL;
620 END IF;
621
622 IF nvl(p_receivable_mode,'N') = 'Y' THEN
623 l_source_type_where := ' and ard.source_type in (''REC'',''UNPAIDREC'') ';
624 select meaning
625 into l_receivable_mode_meaning
626 from fnd_lookups
627 where lookup_type = 'YES_NO'
628 and lookup_code = 'Y';
629 ELSE
630 l_source_type_where := NULL;
631 select meaning
632 into l_receivable_mode_meaning
633 from fnd_lookups
634 where lookup_type = 'YES_NO'
635 and lookup_code = 'N';
636 END IF;
637
638 IF p_posting_status IS NOT NULL THEN
639 select meaning
640 into l_status_meaning
641 from ar_lookups
642 where lookup_type = 'POSTED_STATUS'
643 and lookup_code = arp_recon_rep.var_tname.g_posting_status;
644
645 l_posting_status_where := 'and nvl(adj.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')) =
646 decode(arp_recon_rep.get_posting_status(),
647 ''POSTED'',adj.gl_posted_date,
648 ''UNPOSTED'',TO_DATE(''01/01/0001'',''MM/DD/YYYY''),
649 nvl(adj.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')))';
650 ELSE
651 l_status_meaning := NULL;
652 l_posting_status_where := NULL;
653 END IF;
654
655 l_xml_query := '
656 select trx.invoice_currency_code,
657 type.name,
658 adj.posting_control_id,
659 trx.trx_number,
660 to_char(pay.due_date,''YYYY-MM-DD'') due_date,
661 to_char(pay.gl_date,''YYYY-MM-DD'') trx_gl_date,
662 to_char(adj.gl_date,''YYYY-MM-DD'') adj_gl_date,
663 adj.adjustment_number,
664 decode(adj.adjustment_type,''C'', look.meaning,
665 decode(rec.type, ''FINCHRG'',''Finance'',''Adjustment'')) adj_class,
666 rec.name activity,
667 substrb(party.party_name,1,50) customer_name,
668 cust.account_number customer_number,
669 to_char(trx.trx_date,''YYYY-MM-DD'') trx_date,
670 nvl(ard.amount_dr,0) entered_debit,
671 nvl(ard.amount_cr,0) entered_credit,
672 nvl(ard.acctd_amount_dr,0) acctd_debit,
673 nvl(ard.acctd_amount_cr,0) acctd_credit,
674 gc.code_combination_id account_code_combination_id,
675 l_cat.meaning category,
676 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
677 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
678 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
679 from hz_cust_accounts cust,
680 hz_parties party,
681 ra_cust_trx_types type,
682 gl_code_combinations gc,
683 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' pay,
684 ar_receivables_trx rec,
685 '||arp_recon_rep.var_tname.l_ra_customer_trx_all||' trx,
686 '||arp_recon_rep.var_tname.l_ar_adjustments_all||' adj,
687 '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
688 ar_lookups look,
689 ar_lookups l_cat
690 where trx.complete_flag = ''Y''
691 and cust.cust_account_id = trx.bill_to_customer_id
692 and cust.party_id = party.party_id
693 and trx.set_of_books_id = arp_recon_rep.get_set_of_books_id()
694 and trx.cust_trx_type_id = type.cust_trx_type_id
695 and trx.customer_trx_id = pay.customer_trx_id
696 and pay.payment_schedule_id = adj.payment_schedule_id
697 and nvl(adj.status, ''A'') = ''A''
698 and type.type in (''INV'',''DEP'',''GUAR'',''CM'',''DM'',''CB'')
699 and nvl(type.org_id,-99) = nvl(trx.org_id,-99)
700 and look.lookup_type = ''INV/CM''
701 and look.lookup_code = type.type
702 and nvl(adj.postable,''Y'') = ''Y''
703 and adj.receivables_trx_id is not null
704 and adj.receivables_trx_id <> -15
705 and adj.receivables_trx_id = rec.receivables_trx_id
706 and nvl(rec.org_id,-99) = nvl(trx.org_id,-99)
707 and ard.source_id = adj.adjustment_id
708 and ard.source_table = ''ADJ''
709 and gc.code_combination_id = ard.code_combination_id
710 and gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
711 and l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
712 and l_cat.lookup_code = (''ADJ_''||ard.source_type)
713 '||l_adj_org_where||'
714 '||l_ard_org_where||'
715 '||l_rec_org_where||'
716 '||l_trx_org_where||'
717 '||l_pay_org_where||'
718 '||l_type_org_where||'
719 '||l_gl_date_where ||'
720 '||l_co_seg_where ||'
721 '||l_account_where ||'
722 '||l_account_seg_where ||'
723 '||l_source_type_where||'
724 '||l_posting_status_where||'
725 order by company, category, account,adj_gl_date, adjustment_number' ;
726
727 DBMS_UTILITY.DB_VERSION(l_version, l_compatibility);
728 l_majorVersion := to_number(substr(l_version, 1, instr(l_version,'.')-1));
729
730 IF (l_majorVersion > 8 and l_majorVersion < 9) THEN
731 BEGIN
732 queryCtx := DBMS_XMLQuery.newContext(l_xml_query);
733 DBMS_XMLQuery.setRaiseNoRowsException(queryCtx,TRUE);
734 l_result := DBMS_XMLQuery.getXML(queryCtx);
735 DBMS_XMLQuery.closeContext(queryCtx);
736 l_rows_processed := 1;
737 EXCEPTION WHEN OTHERS THEN
738 DBMS_XMLQuery.getExceptionContent(queryCtx,l_errNo,l_errMsg);
739 IF l_errNo = 1403 THEN
740 l_rows_processed := 0;
741 END IF;
742 DBMS_XMLQuery.closeContext(queryCtx);
743 END;
744 ELSIF (l_majorVersion >= 9 ) THEN
745 qryCtx := DBMS_XMLGEN.newContext(l_xml_query);
746 l_result := DBMS_XMLGEN.getXML(qryCtx,DBMS_XMLGEN.NONE);
747 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
748 DBMS_XMLGEN.closeContext(qryCtx);
749 END IF;
750
751 IF l_rows_processed <> 0 THEN
752 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
753 tempResult := l_result;
754 ELSE
755 l_resultOffset := 0;
756 END IF;
757
758 l_new_line := '
759 ';
760 select to_char(sysdate,'YYYY-MM-DD')
761 into l_report_date
762 from dual;
763
764 /* Bug 4708930
765 Get the special characters replaced */
766 l_reporting_entity_name := format_string(l_reporting_entity_name);
767 l_reporting_level_name := format_string(l_reporting_level_name);
768 l_organization := format_string(l_organization);
769 l_receivable_mode_meaning := format_string(l_receivable_mode_meaning);
770 l_status_meaning := format_string(l_status_meaning);
771 l_sob_name := format_string(l_sob_name);
772 l_message := format_string(l_message);
773 l_message_acct := format_string(l_message_acct);
774
775 /* Prepare the tag for the report heading */
776 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
777 l_xml_header := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
778 l_xml_header := l_xml_header ||l_new_line||'<ARADJJOURNAL>';
779 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT>'||l_message||'</MSG_TXT>';
780 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT_ACCT>'||l_message_acct||'</MSG_TXT_ACCT>';
781 l_xml_header := l_xml_header ||l_new_line||' <PARAMETERS>';
782 l_xml_header := l_xml_header ||l_new_line||' <REPORT_DATE>'||l_report_date||'</REPORT_DATE>';
783 l_xml_header := l_xml_header ||l_new_line||' <REPORTING_LEVEL>'||l_reporting_level_name||'</REPORTING_LEVEL>';
784 l_xml_header := l_xml_header ||l_new_line||' <REPORTING_ENTITY>'||l_reporting_entity_name||'</REPORTING_ENTITY>';
785 l_xml_header := l_xml_header ||l_new_line||' <SOB_ID>'||p_sob_id||'</SOB_ID>';
786 l_xml_header := l_xml_header ||l_new_line||' <CO_SEG_LOW>'||p_co_seg_low||'</CO_SEG_LOW>';
787 l_xml_header := l_xml_header ||l_new_line||' <CO_SEG_HIGH>'||p_co_seg_high||'</CO_SEG_HIGH>';
788 l_xml_header := l_xml_header ||l_new_line||' <GL_DATE_FROM>'||to_char(fnd_date.canonical_to_date(p_gl_date_from),'YYYY-MM-DD')||'</GL_DATE_FROM>';
789 l_xml_header := l_xml_header ||l_new_line||' <GL_DATE_TO>'||to_char(fnd_date.canonical_to_date(p_gl_date_to),'YYYY-MM-DD')||'</GL_DATE_TO>';
790 l_xml_header := l_xml_header ||l_new_line||' <POSTING_STATUS>'||l_status_meaning||'</POSTING_STATUS>';
791 l_xml_header := l_xml_header ||l_new_line||' <GL_ACCOUNT_LOW>'||p_gl_account_low||'</GL_ACCOUNT_LOW>';
792 l_xml_header := l_xml_header ||l_new_line||' <GL_ACCOUNT_HIGH>'||p_gl_account_high||'</GL_ACCOUNT_HIGH>';
793 l_xml_header := l_xml_header ||l_new_line||' <SUMMARY_ACCOUNT>'||p_summary_account||'</SUMMARY_ACCOUNT>';
794 l_xml_header := l_xml_header ||l_new_line||' <REC_MODE_ONLY>'||l_receivable_mode_meaning||'</REC_MODE_ONLY>';
795 l_xml_header := l_xml_header ||l_new_line||' <NUM_ROWS>'||l_rows_processed||'</NUM_ROWS>';
796 l_xml_header := l_xml_header ||l_new_line||' </PARAMETERS>';
797 l_xml_header := l_xml_header ||l_new_line||' <REPORT_HEADING>';
798 l_xml_header := l_xml_header ||l_new_line||' <SET_OF_BOOKS>'||l_sob_name||'</SET_OF_BOOKS>';
799 l_xml_header := l_xml_header ||l_new_line||' <ORGANIZATION>'||l_organization||'</ORGANIZATION>';
800 l_xml_header := l_xml_header ||l_new_line||' <FUNCTIONAL_CURRENCY>'||l_functional_currency||'</FUNCTIONAL_CURRENCY>';
801 l_xml_header := l_xml_header ||l_new_line||' </REPORT_HEADING>';
802
803 l_close_tag := l_new_line||'</ARADJJOURNAL>'||l_new_line;
804 l_xml_header_length := length(l_xml_header);
805 IF l_rows_processed <> 0 THEN
806 dbms_lob.write(tempResult,l_xml_header_length,1,l_xml_header);
807 dbms_lob.copy(tempResult,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
808 l_xml_header_length,l_resultOffset);
809 ELSE
810 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
811 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
812 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
813 END IF;
814
815 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
816
817 ar_cumulative_balance_report.process_clob(tempResult);
818 p_result := tempResult;
819
820 log ('aradj_journal_load_xml (-)');
821
822 END aradj_journal_load_xml;
823
824 /*========================================================================+
825 | PUBLIC PROCEDURE ARUNAPP_JOURNAL_LOAD_XML |
826 | |
827 | DESCRIPTION |
828 | |
829 | This procedure is used to generate the XML data required for reporting|
830 | Unapplied Receipts Journals |
831 | |
832 | PSEUDO CODE/LOGIC |
833 | |
834 | PARAMETERS |
835 | |
836 | |
837 | KNOWN ISSUES |
838 | |
839 | NOTES |
840 | |
841 | |
842 | MODIFICATION HISTORY |
843 | Date Author Description of Changes |
844 | 03-FEB-2004 rkader Created |
845 | |
846 *=======================================================================*/
847 PROCEDURE arunapp_journal_load_xml (
848 p_reporting_level IN VARCHAR2,
849 p_reporting_entity_id IN NUMBER,
850 p_sob_id IN NUMBER,
851 p_coa_id IN NUMBER,
852 p_co_seg_low IN VARCHAR2,
853 p_co_seg_high IN VARCHAR2,
854 p_gl_date_from IN VARCHAR2,
855 p_gl_date_to IN VARCHAR2,
856 p_posting_status IN VARCHAR2,
857 p_gl_account_low IN VARCHAR2,
858 p_gl_account_high IN VARCHAR2,
859 p_summary_account IN NUMBER,
860 p_receivable_mode IN VARCHAR2,
861 p_result OUT NOCOPY CLOB) IS
862
863 l_result CLOB;
864 tempResult CLOB;
865 l_version varchar2(20);
866 l_compatibility varchar2(20);
867 l_suffix varchar2(2);
868 l_majorVersion number;
869 l_resultOffset number;
870 l_xml_header varchar2(3000);
871 l_xml_header_length number;
872 l_errNo NUMBER;
873 l_errMsg VARCHAR2(200);
874 queryCtx DBMS_XMLquery.ctxType;
875 qryCtx DBMS_XMLGEN.ctxHandle;
876 l_xml_query CLOB;
877 l_natural_segment_col VARCHAR2(50);
878 l_flex_value_set_id NUMBER;
879 l_code_combinations VARCHAR2(32767);
880 TYPE ref_cur IS REF CURSOR;
881 l_xml_stmt ref_cur;
882 l_rows_processed NUMBER;
883 l_new_line VARCHAR2(1) ;
884 l_coa_id NUMBER; /*bug fix 5654975*/
885 /* Variables to hold the report heading */
886 l_sob_id NUMBER;
887 l_sob_name VARCHAR2(100);
888 l_functional_currency VARCHAR2(15);
889 l_organization VARCHAR2(60);
890 l_format VARCHAR2(40);
891 l_close_tag VARCHAR2(100);
892 l_reporting_entity_name VARCHAR2(80);
893 l_reporting_level_name VARCHAR2(30);
894 l_status_meaning VARCHAR2(30);
895 l_gl_account_type_meaning VARCHAR2(100);
896 l_receivable_mode_meaning VARCHAR2(10);
897
898 /* Variables to hold the where clause based on the input parameters*/
899 /* Changed length of the variables from 200 to 500 to address bug:5181586*/
900 /* Increased variables length to 32767 for bug 5654975 */
901 l_ra_org_where VARCHAR2(32767);
902 l_ard_org_where VARCHAR2(32767);
903 l_ps_org_where VARCHAR2(32767);
904 l_cr_org_where VARCHAR2(32767);
905 l_crh_org_where VARCHAR2(32767);
906 l_bat_org_where VARCHAR2(32767);
907 l_bs_org_where VARCHAR2(32767);
908 l_sysparam_org_where VARCHAR2(32767);
909 /* Changes to variable length ends*/
910 l_co_seg_where VARCHAR2(32767);
911 l_account_where VARCHAR2(32767);
912 l_account_seg_where VARCHAR2(32767);
913 l_gl_date_where VARCHAR2(1000);
914 l_gl_date_ard_where VARCHAR2(1000);
915 l_source_type_where VARCHAR2(32767);
916 l_gl_date_closed_where VARCHAR2(1000);
917 l_posting_status_where VARCHAR2(1000);
918 l_posting_status_ard_where VARCHAR2(500);
919 l_report_date VARCHAR2(25);
920 l_ld_sp VARCHAR2(1) := 'Y';
921 l_message VARCHAR2(2000);
922 l_message_acct VARCHAR2(1000);
923 l_encoding VARCHAR2(20);
924
925 /* Changes for bug 10177944 */
926 l_nls_numeric_char VARCHAR2(5);
927 l_nls_altered BOOLEAN;
928 l_alter_str VARCHAR2(100);
929 l_xml_build DBMS_SQL.VARCHAR2A;
930 l_xml_build_sql VARCHAR2(32767);
931 l_cur_id INTEGER;
932 TYPE ref_cur2 IS REF CURSOR;
933 l_xml_ref ref_cur2;
934 L_GL_DATE_CRH_WHERE VARCHAR2(32767);
935 L_SOURCE_TYPE_ARD_WHERE VARCHAR2(32767);
936 L_POSTING_STATUS_NUL_WHERE VARCHAR2(32767);
937 L_POSTING_STATUS_CRH_WHERE VARCHAR2(32767);
938 l_dyn_block VARCHAR2(1000);
939 BEGIN
940 log('arunapp_journal_load_xml (+)');
941
942
943
944 /* Assign the input parameters to the global variables */
945 arp_recon_rep.var_tname.g_reporting_level := p_reporting_level;
946 arp_recon_rep.var_tname.g_reporting_entity_id := p_reporting_entity_id;
947 /* bug 5654975 p_sob_id is passed incorrectly when the user
948 has access to multiple Ledgers */
949 -- arp_recon_rep.var_tname.g_set_of_books_id := p_sob_id;
950 /* Bug fix 5678284
951 p_coa_id is passed incorrectly when the user has access to multiple Ledgers */
952 --arp_recon_rep.var_tname.g_chart_of_accounts_id := p_coa_id;
953 arp_recon_rep.var_tname.g_gl_date_from := fnd_date.canonical_to_date(p_gl_date_from);
954 arp_recon_rep.var_tname.g_gl_date_to := fnd_date.canonical_to_date(p_gl_date_to);
955 arp_recon_rep.var_tname.g_posting_status := p_posting_status;
956 arp_recon_rep.var_tname.g_max_gl_date := to_date('31-12-4712','DD-MM-YYYY');
957
958
959
960 /* Added Conditional Implication to address bug:5181586*/
961 /*Added set of books id and chart of accounts id for bug 5654975*/
962 IF p_reporting_level = 1000 THEN
963 SELECT sob.name sob_name,
964 sob.set_of_books_id,
965 sob.currency_code functional_currency,
966 sob.chart_of_accounts_id
967 INTO l_sob_name,
968 l_sob_id,
969 l_functional_currency,
970 l_coa_id
971 FROM gl_sets_of_books sob
972 WHERE sob.set_of_books_id = arp_recon_rep.var_tname.g_reporting_entity_id;
973
974 ELSIF p_reporting_level = 3000 THEN
975 SELECT sob.name sob_name,
976 sob.set_of_books_id,
977 sob.currency_code functional_currency,
978 sob.chart_of_accounts_id
979 INTO l_sob_name,
980 l_sob_id,
981 l_functional_currency,
982 l_coa_id
983 FROM gl_sets_of_books sob,
984 ar_system_parameters sysparam
985 WHERE sob.set_of_books_id = sysparam.set_of_books_id
986 AND sysparam.org_id = arp_recon_rep.var_tname.g_reporting_entity_id;
987
988 END IF;
989 /* Changes for bug:5181586 ends*/
990
991 arp_recon_rep.var_tname.g_set_of_books_id := l_sob_id;
992 arp_recon_rep.var_tname.g_chart_of_accounts_id := l_coa_id;
993
994 /* Initialize the reporting context */
995 init(p_sob_id);
996
997 /* Set the org conditions */
998
999 XLA_MO_REPORTING_API.Initialize(p_reporting_level, p_reporting_entity_id, 'AUTO');
1000
1001 l_ra_org_where := XLA_MO_REPORTING_API.Get_Predicate('ra',NULL);
1002 l_ard_org_where := XLA_MO_REPORTING_API.Get_Predicate('ard',NULL);
1003 l_ps_org_where := XLA_MO_REPORTING_API.Get_Predicate('ps',NULL);
1004 l_cr_org_where := XLA_MO_REPORTING_API.Get_Predicate('cr',NULL);
1005 l_crh_org_where := XLA_MO_REPORTING_API.Get_Predicate('crh',NULL);
1006 l_bat_org_where := XLA_MO_REPORTING_API.Get_Predicate('bat',NULL);
1007 l_bs_org_where := XLA_MO_REPORTING_API.Get_Predicate('bs',NULL);
1008 l_sysparam_org_where := XLA_MO_REPORTING_API.Get_Predicate('sysparam',NULL);
1009
1010 /* Replace the bind variables with global functions */
1011 l_ra_org_where := replace(l_ra_org_where,
1012 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1013 l_ard_org_where := replace(l_ard_org_where,
1014 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1015 l_ps_org_where := replace(l_ps_org_where,
1016 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1017 l_cr_org_where := replace(l_cr_org_where,
1018 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1019 l_crh_org_where := replace(l_crh_org_where,
1020 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1021 l_bat_org_where := replace(l_bat_org_where,
1022 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1023 l_bs_org_where := replace(l_bs_org_where,
1024 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1025
1026 l_reporting_entity_name := substrb(XLA_MO_REPORTING_API.get_reporting_entity_name,1,80);
1027 l_reporting_level_name := substrb(XLA_MO_REPORTING_API.get_reporting_level_name,1,30);
1028
1029 /* Multi Org Uptake: Show appropriate message to the user depending upon the security profile */
1030 IF p_reporting_level = '1000' THEN
1031 l_ld_sp:= mo_utils.check_ledger_in_sp(p_reporting_entity_id);
1032 END IF;
1033
1034 IF l_ld_sp = 'N' THEN
1035 FND_MESSAGE.SET_NAME('FND','FND_MO_RPT_PARTIAL_LEDGER');
1036 l_message := FND_MESSAGE.get;
1037 END IF;
1038
1039 /* Bug fix 4942083*/
1040 IF arp_util.Open_Period_Exists(p_reporting_level,
1041 p_reporting_entity_id,
1042 arp_recon_rep.var_tname.g_gl_date_from,
1043 arp_recon_rep.var_tname.g_gl_date_to) THEN
1044 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
1045 l_message_acct := FND_MESSAGE.Get;
1046 END IF;
1047
1048 /* Get the org name */
1049 IF p_reporting_level = '3000' THEN
1050 select substrb(hou.name,1,60)
1051 into l_organization
1052 from hr_organization_units hou
1053 where hou.organization_id = arp_recon_rep.var_tname.g_reporting_entity_id;
1054 ELSE
1055 select meaning
1056 into l_organization
1057 from ar_lookups
1058 where lookup_code ='ALL' and lookup_type ='ALL';
1059 END IF;
1060
1061 /* Build the WHERE clauses */
1062 /*Replaced p_coa_id with l_coa_id for bug 5654975*/
1063
1064 IF p_co_seg_low IS NULL AND p_co_seg_high IS NULL THEN
1065 l_co_seg_where := NULL;
1066 ELSIF p_co_seg_low IS NULL THEN
1067 l_co_seg_where := ' AND ' ||
1068 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
1069 p_id_flex_code => 'GL#',
1070 p_id_flex_num => l_coa_id,
1071 p_table_alias => 'GC',
1072 p_mode => 'WHERE',
1073 p_qualifier => 'GL_BALANCING',
1074 p_function => '<=',
1075 p_operand1 => p_co_seg_high);
1076 ELSIF p_co_seg_high IS NULL THEN
1077 l_co_seg_where := ' AND ' ||
1078 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
1079 p_id_flex_code => 'GL#',
1080 p_id_flex_num => l_coa_id,
1081 p_table_alias => 'GC',
1082 p_mode => 'WHERE',
1083 p_qualifier => 'GL_BALANCING',
1084 p_function => '>=',
1085 p_operand1 => p_co_seg_low);
1086 ELSE
1087 l_co_seg_where := ' AND ' ||
1088 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
1089 p_id_flex_code => 'GL#',
1090 p_id_flex_num => l_coa_id,
1091 p_table_alias => 'GC',
1092 p_mode => 'WHERE',
1093 p_qualifier => 'GL_BALANCING',
1094 p_function => 'BETWEEN',
1095 p_operand1 => p_co_seg_low,
1096 p_operand2 => p_co_seg_high);
1097 END IF;
1098
1099 /* Bug fix 5678284 : Added l_gl_date_ard_where*/
1100 IF p_gl_date_from IS NULL and p_gl_date_to IS NULL THEN
1101 l_gl_date_where := NULL;
1102 l_gl_date_ard_where := NULL;
1103 ELSIF p_gl_date_from IS NULL THEN
1104 l_gl_date_where :=' and ra.gl_date <= arp_recon_rep.get_gl_date_to()';
1105 l_gl_date_crh_where := ' and crh.gl_date <= arp_recon_rep.get_gl_date_to()';
1106 --l_gl_date_ard_where :=' and hd.accounting_date <= arp_recon_rep.get_gl_date_to()';
1107 ELSIF p_gl_date_to IS NULL THEN
1108 l_gl_date_where :=' and ra.gl_date >= arp_recon_rep.get_gl_date_from() ' ;
1109 l_gl_date_crh_where := ' and crh.gl_date >= arp_recon_rep.get_gl_date_from() ' ;
1110 --l_gl_date_ard_where :=' and hd.accounting_date >= arp_recon_rep.get_gl_date_from() ' ;
1111 ELSE
1112 l_gl_date_where := ' and ra.gl_date between arp_recon_rep.get_gl_date_from() and arp_recon_rep.get_gl_date_to() ';
1113 l_gl_date_crh_where := ' and crh.gl_date between arp_recon_rep.get_gl_date_from() and arp_recon_rep.get_gl_date_to() ';
1114 --l_gl_date_ard_where := ' and hd.accounting_date between arp_recon_rep.get_gl_date_from() and arp_recon_rep.get_gl_date_to() ';
1115 END IF;
1116
1117 IF p_gl_date_from IS NULL and p_gl_date_to IS NULL THEN
1118 l_gl_date_closed_where := 'and ps.gl_date_closed = arp_recon_rep.get_max_gl_date()';
1119 ELSIF p_gl_date_from IS NOT NULL THEN
1120 l_gl_date_closed_where := 'and ps.gl_date_closed >= arp_recon_rep.get_gl_date_from() ';
1121 ELSIF p_gl_date_to IS NOT NULL THEN
1122 l_gl_date_closed_where := 'and ps.gl_date_closed >= arp_recon_rep.get_gl_date_to()';
1123 END IF;
1124
1125 IF p_gl_account_low IS NOT NULL AND p_gl_account_high IS NOT NULL THEN
1126 l_account_where := ' AND ' || AR_CALC_AGING.FLEX_SQL(
1127 p_application_id=> 101,
1128 p_id_flex_code =>'GL#',
1129 p_id_flex_num =>l_coa_id,
1130 p_table_alias => 'gc',
1131 p_mode => 'WHERE',
1132 p_qualifier => 'ALL',
1133 p_function=> 'BETWEEN',
1134 p_operand1 => p_gl_account_low,
1135 p_operand2 => p_gl_account_high);
1136 ELSE
1137 l_account_where := NULL;
1138 END IF;
1139
1140 IF p_summary_account IS NOT NULL THEN
1141 SELECT fcav.application_column_name, flex_value_set_id
1142 INTO l_natural_segment_col , l_flex_value_set_id
1143 FROM fnd_segment_attribute_values fcav,
1144 fnd_id_flex_segments fifs
1145 WHERE fcav.application_id = 101
1146 AND fcav.id_flex_code = 'GL#'
1147 AND fcav.id_flex_num = arp_recon_rep.var_tname.g_chart_of_accounts_id
1148 AND fcav.attribute_value = 'Y'
1149 AND fcav.segment_attribute_type = 'GL_ACCOUNT'
1150 AND fifs.application_id = fcav.application_id
1151 AND fifs.id_flex_code = fcav.id_flex_code
1152 AND fifs.id_flex_num = fcav.id_flex_num
1153 AND fcav.application_column_name = fifs.application_column_name;
1154
1155 get_detail_accounts(l_flex_value_set_id, p_summary_account, l_code_combinations);
1156
1157 l_account_seg_where := ' and gc.'||l_natural_segment_col||' in ('||l_code_combinations||' )';
1158 ELSE
1159 l_account_seg_where := NULL;
1160 END IF;
1161
1162 /* Is this parameter redundant ?*/
1163
1164 IF nvl(p_receivable_mode,'N') = 'Y' THEN
1165
1166 l_source_type_where := ' and decode (ae.accounting_class_code, ''UNAPP''
1167 , ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
1168 , decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
1169 , ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type) in (''UNAPP'', ''UNID'',''ACC'',''OTHER ACC'') ';
1170
1171 l_source_type_ard_where := ' and ard.source_type in (''UNAPP'', ''UNID'',''ACC'',''OTHER ACC'') ';
1172
1173 select meaning
1174 into l_receivable_mode_meaning
1175 from fnd_lookups
1176 where lookup_type = 'YES_NO'
1177 and lookup_code = 'Y';
1178 ELSE
1179 l_source_type_where := NULL;
1180 select meaning
1181 into l_receivable_mode_meaning
1182 from fnd_lookups
1183 where lookup_type = 'YES_NO'
1184 and lookup_code = 'N';
1185 END IF;
1186
1187 IF p_posting_status IS NOT NULL THEN
1188 select meaning
1189 into l_status_meaning
1190 from ar_lookups
1191 where lookup_type = 'POSTED_STATUS'
1192 and lookup_code = arp_recon_rep.var_tname.g_posting_status;
1193
1194 l_posting_status_where := 'and nvl(ra.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')) =
1195 decode(arp_recon_rep.get_posting_status(),
1196 ''POSTED'',ra.gl_posted_date,
1197 ''UNPOSTED'',TO_DATE(''01/01/0001'',''MM/DD/YYYY''),
1198 nvl(ra.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')))';
1199
1200 l_posting_status_nul_where := 'and nvl(NULL,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')) =
1201 decode(arp_recon_rep.get_posting_status(),
1202 ''POSTED'',ra.gl_posted_date,
1206 l_posting_status_ard_where := 'and nvl(ard.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')) =
1203 ''UNPOSTED'',TO_DATE(''01/01/0001'',''MM/DD/YYYY''),
1204 nvl(ra.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')))';
1205 /* Bug fix 5678284 : Added l_posting_status_ard_where*/
1207 decode(arp_recon_rep.get_posting_status(),
1208 ''POSTED'',ard.gl_posted_date,
1209 ''UNPOSTED'',TO_DATE(''01/01/0001'',''MM/DD/YYYY''),
1210 nvl(ard.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')))';
1211
1212 l_posting_status_crh_where := 'and nvl(crh.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')) =
1213 decode(arp_recon_rep.get_posting_status(),
1214 ''POSTED'',crh.gl_posted_date,
1215 ''UNPOSTED'',TO_DATE(''01/01/0001'',''MM/DD/YYYY''),
1216 nvl(crh.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')))';
1217 ELSE
1218 l_status_meaning := NULL;
1219 l_posting_status_where := NULL;
1220 l_posting_status_ard_where := NULL;
1221 END IF;
1222
1223
1224 l_xml_build_sql := '(select /*+ leading(ra) index(ra AR_RECEIVABLE_APPLICATIONS_N6) index(ps AR_PAYMENT_SCHEDULES_U2)*/
1225 cr.receipt_number payment_number,
1226 arm.name payment_method,
1227 substrb(party.party_name,1,50) customer_name,
1228 cust.account_number customer_number,
1229 to_char(hd.accounting_date,''YYYY-MM-DD'') app_gl_date,
1230 to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
1231 nvl(lk.unrounded_entered_dr,0) entered_debit,
1232 nvl(lk.unrounded_entered_cr,0) entered_credit,
1233 nvl(lk.unrounded_accounted_dr,0) acctd_debit,
1234 nvl(lk.unrounded_accounted_cr,0) acctd_credit,
1235 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
1236 cr.currency_code receipt_currency,
1237 gc.code_combination_id,
1238 bs.name receipt_source,
1239 bat.name batch_name,
1240 l_cat.meaning category,
1241 /* 7008877 */
1242 ard.line_id,
1243 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
1244 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
1245 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
1246 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
1247 ar_receipt_methods arm,
1248 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
1249 gl_code_combinations gc,
1250 hz_cust_accounts cust,
1251 hz_parties party,
1252 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
1253 ar_batch_sources bs,
1254 '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
1255 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
1256 ar_distributions_all ard,
1257 xla_distribution_links lk,
1258 xla_ae_lines ae,
1259 xla_ae_headers hd,
1260 ar_lookups l_cat
1261 where ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
1262 , ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
1263 , ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
1264 , ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
1265 , ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
1266 , ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
1267 , ''GAIN'', ''LOSS'', ''UNID''
1268 , ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
1269 , ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
1270 , ''FAC_BR'', ''PMT_NET'', ''PREPAY''
1271 , ''REFUND'', ''REM_BR'', ''UNAPP'')
1272 AND nvl(ra.confirmed_flag,''Y'') = ''Y''
1273 AND ra.status in (''UNAPP'',''ACC'',''UNID'',''OTHER ACC'')
1274 AND ps.cash_receipt_id = ra.cash_receipt_id
1275 AND ps.class = ''PMT''
1276 '||l_gl_date_closed_where||'
1277 AND cr.cash_receipt_id = ra.cash_receipt_id
1278 AND lk.ae_header_id = ae.ae_header_id
1279 AND lk.ae_line_num = ae.ae_line_num
1280 AND lk.ae_header_id = hd.ae_header_id
1281 AND ra.set_of_books_id = hd.ledger_id
1282 AND cr.receipt_method_id = arm.receipt_method_id
1283 AND crh.cash_receipt_id = cr.cash_receipt_id
1284 AND crh.batch_id = bat.batch_id(+)
1285 AND bat.batch_source_id = bs.batch_source_id(+)
1286 AND bat.org_id = bs.org_id(+)
1287 AND gc.code_combination_id = ae.code_combination_id
1288 AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
1289 AND ard.source_id = ra.receivable_application_id
1290 AND cr.pay_from_customer = cust.cust_account_id(+)
1291 AND cust.party_id = party.party_id(+)
1292 AND ard.line_id = lk.source_distribution_id_num_1
1293 AND lk.application_id = 222
1294 AND ard.source_table = ''RA''
1295 AND crh.first_posted_record_flag = ''Y''
1296 AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
1297 AND l_cat.lookup_code = ''TRADE_''||decode (ae.accounting_class_code, ''UNAPP''
1298 , ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
1299 , decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
1300 , ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type)
1301 AND lk.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
1302 AND ae.application_id = 222
1303 AND nvl(cr.confirmed_flag,''Y'') = ''Y''
1304 AND hd.event_type_code <> ''MANUAL''
1305 AND ra.event_id IS NOT NULL
1306 '||l_ra_org_where||'
1307 '||l_ard_org_where||'
1308 '||l_ps_org_where||'
1309 '||l_cr_org_where||'
1310 '||l_crh_org_where||'
1311 '||l_bat_org_where||'
1312 '||l_bs_org_where||'
1313 '||l_gl_date_where ||'
1314 '||l_co_seg_where ||'
1315 '||l_account_where ||'
1316 '||l_account_seg_where ||'
1317 '||l_source_type_where||'
1318 '||l_posting_status_where||'
1319 UNION ALL
1320 select /*+ leading(ra) index(ra AR_RECEIVABLE_APPLICATIONS_N6) index(ps AR_PAYMENT_SCHEDULES_U2)*/
1321 cr.receipt_number payment_number,
1322 arm.name payment_method,
1323 substrb(party.party_name,1,50) customer_name,
1324 cust.account_number customer_number,
1325 to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
1326 to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
1327 nvl(ard.amount_dr,0) entered_debit,
1328 nvl(ard.amount_cr,0) entered_credit,
1329 nvl(ard.acctd_amount_dr,0) acctd_debit,
1330 nvl(ard.acctd_amount_cr,0) acctd_credit,
1331 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
1332 cr.currency_code receipt_currency,
1333 gc.code_combination_id,
1334 bs.name receipt_source,
1335 bat.name batch_name,
1336 l_cat.meaning category,
1337 /* 7008877 */
1338 ard.line_id,
1339 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
1340 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
1341 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
1342 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
1343 ar_receipt_methods arm,
1344 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
1345 gl_code_combinations gc,
1346 hz_cust_accounts cust,
1347 hz_parties party,
1348 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
1349 ar_batch_sources bs,
1350 '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
1351 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
1352 ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
1353 ar_lookups l_cat
1354 where
1355 ra.status IN (''ACC'', ''OTHER ACC'')
1356 AND ps.cash_receipt_id = ra.cash_receipt_id
1357 AND ps.class = ''PMT''
1358 '||l_gl_date_closed_where||'
1359 AND cr.cash_receipt_id = ra.cash_receipt_id
1360 AND cr.pay_from_customer = cust.cust_account_id(+)
1361 AND cust.party_id = party.party_id(+)
1362 AND cr.receipt_method_id = arm.receipt_method_id
1363 AND crh.cash_receipt_id = cr.cash_receipt_id
1364 AND crh.batch_id = bat.batch_id(+)
1365 AND bat.batch_source_id = bs.batch_source_id(+)
1366 AND bat.org_id = bs.org_id(+)
1367 AND gc.code_combination_id = ard.code_combination_id
1368 AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
1369 AND ard.source_id = ra.receivable_application_id
1370 AND ard.source_table = ''RA''
1371 AND crh.first_posted_record_flag = ''Y''
1372 AND nvl(cr.confirmed_flag,''Y'') = ''Y''
1373 AND nvl(ra.confirmed_flag,''Y'') = ''Y''
1374 AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
1375 AND l_cat.lookup_code = ''TRADE_''||ard.source_type
1376 AND ra.posting_control_id <> - 3
1377 AND ra.event_id IS NULL
1378 '||l_ra_org_where||'
1379 '||l_ard_org_where||'
1380 '||l_ps_org_where||'
1381 '||l_cr_org_where||'
1382 '||l_crh_org_where||'
1383 '||l_bat_org_where||'
1384 '||l_bs_org_where||'
1385 '||l_gl_date_where ||'
1386 '||l_co_seg_where ||'
1387 '||l_account_where ||'
1388 '||l_account_seg_where ||'
1389 '||l_source_type_ard_where||'
1390 '||l_posting_status_nul_where||'
1391 UNION ALL
1392 select /*+ leading(ra) index(ra AR_RECEIVABLE_APPLICATIONS_N6) index(ps AR_PAYMENT_SCHEDULES_U2)*/
1393 cr.receipt_number payment_number,
1394 arm.name payment_method,
1395 substrb(party.party_name,1,50) customer_name,
1396 cust.account_number customer_number,
1397 to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
1398 to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
1399 nvl(ard.amount_dr,0) entered_debit,
1400 nvl(ard.amount_cr,0) entered_credit,
1401 nvl(ard.acctd_amount_dr,0) acctd_debit,
1402 nvl(ard.acctd_amount_cr,0) acctd_credit,
1403 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
1404 cr.currency_code receipt_currency,
1405 gc.code_combination_id,
1406 bs.name receipt_source,
1407 bat.name batch_name,
1408 l_cat.meaning category,
1409 /* 7008877 */
1410 ard.line_id,
1411 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
1412 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
1413 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
1414 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
1415 ar_receipt_methods arm,
1416 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
1417 gl_code_combinations gc,
1418 hz_cust_accounts cust,
1419 hz_parties party,
1420 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
1421 ar_batch_sources bs,
1422 '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
1423 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
1424 ar_distributions_all ard,
1425 ar_lookups l_cat
1426 where ra.status IN (''UNAPP'', ''UNID'')
1427 AND EXISTS
1428 (SELECT NULL
1429 FROM xla_distribution_links lk
1430 WHERE lk.source_distribution_id_num_1 = ard.line_id
1431 AND lk.application_id = 222
1432 AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'')
1433 AND ps.class = ''PMT''
1434 '||l_gl_date_closed_where||'
1435 AND crh.batch_id = bat.batch_id(+)
1436 AND bat.batch_source_id = bs.batch_source_id(+)
1437 AND bat.org_id = bs.org_id(+)
1438 AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
1439 AND cr.pay_from_customer = cust.cust_account_id(+)
1440 AND cust.party_id = party.party_id(+)
1441 AND ard.source_id = ra.receivable_application_id
1442 AND ps.cash_receipt_id = ra.cash_receipt_id
1443 AND gc.code_combination_id = ard.code_combination_id
1444 AND cr.receipt_method_id = arm.receipt_method_id
1445 AND crh.cash_receipt_id = cr.cash_receipt_id
1446 AND cr.cash_receipt_id = ra.cash_receipt_id
1447 AND crh.first_posted_record_flag = ''Y''
1448 AND ard.source_table = ''RA''
1449 AND nvl(cr.confirmed_flag,''Y'') = ''Y''
1450 AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
1451 AND l_cat.lookup_code = ''TRADE_''||ard.source_type
1452 AND ra.posting_control_id <> - 3
1453 AND nvl(ra.confirmed_flag,''Y'') = ''Y''
1454 AND ra.event_id IS NULL
1455 '||l_ra_org_where||'
1456 '||l_ard_org_where||'
1457 '||l_ps_org_where||'
1458 '||l_cr_org_where||'
1459 '||l_crh_org_where||'
1460 '||l_bat_org_where||'
1461 '||l_bs_org_where||'
1462 '||l_gl_date_where ||'
1463 '||l_co_seg_where ||'
1464 '||l_account_where ||'
1465 '||l_account_seg_where ||'
1466 '||l_source_type_ard_where||'
1467 '||l_posting_status_nul_where||')';
1468
1469 l_xml_build(NVL(l_xml_build.LAST, 0)+ 1) := l_xml_build_sql;
1470 fnd_file.put_line(FND_FILE.LOG, l_xml_build_sql);
1471 l_xml_query := to_clob(l_xml_build_sql);
1472
1473 l_xml_build_sql := 'UNION
1474 (select /*+ leading(crh) index(crh AR_CASH_RECEIPT_HISTORY_N2) index(ps AR_PAYMENT_SCHEDULES_U2)*/
1475 cr.receipt_number payment_number,
1476 arm.name payment_method,
1477 substrb(party.party_name,1,50) customer_name,
1478 cust.account_number customer_number,
1479 to_char(hd.accounting_date,''YYYY-MM-DD'') app_gl_date,
1480 to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
1481 nvl(lk.unrounded_entered_dr,0) entered_debit,
1482 nvl(lk.unrounded_entered_cr,0) entered_credit,
1483 nvl(lk.unrounded_accounted_dr,0) acctd_debit,
1484 nvl(lk.unrounded_accounted_cr,0) acctd_credit,
1485 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
1486 cr.currency_code receipt_currency,
1487 gc.code_combination_id,
1488 bs.name receipt_source,
1489 bat.name batch_name,
1490 l_cat.meaning category,
1491 /* 7008877 */
1492 ard.line_id,
1493 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
1494 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
1495 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
1496 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
1497 ar_receipt_methods arm,
1498 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
1499 gl_code_combinations gc,
1500 hz_cust_accounts cust,
1501 hz_parties party,
1502 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
1503 ar_batch_sources bs,
1504 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
1505 ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
1506 xla_distribution_links lk,
1507 xla_ae_lines ae,
1508 xla_ae_headers hd,
1509 ar_lookups l_cat
1510 where ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
1511 , ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
1512 , ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
1513 , ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
1514 , ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
1515 , ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
1516 , ''GAIN'', ''LOSS'', ''UNID''
1517 , ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
1518 , ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
1519 , ''FAC_BR'', ''PMT_NET'', ''PREPAY''
1520 , ''REFUND'', ''REM_BR'', ''UNAPP'')
1521 --AND ps.cash_receipt_id = ard.cash_receipt_id
1522 AND ps.class = ''PMT''
1523 '||l_gl_date_closed_where||'
1524 AND cr.cash_receipt_id = ps.cash_receipt_id
1525 AND cr.receipt_method_id = arm.receipt_method_id
1526 AND crh.cash_receipt_id = cr.cash_receipt_id
1527 AND lk.ae_header_id = ae.ae_header_id
1528 AND lk.ae_line_num = ae.ae_line_num
1529 AND lk.ae_header_id = hd.ae_header_id
1530 AND crh.batch_id = bat.batch_id(+)
1531 AND bat.batch_source_id = bs.batch_source_id(+)
1532 AND bat.org_id = bs.org_id(+)
1533 AND gc.code_combination_id = ae.code_combination_id
1534 AND crh.cash_receipt_id = ps.cash_receipt_id
1535 AND ard.source_id = crh.cash_receipt_history_id
1536 AND cr.pay_from_customer = cust.cust_account_id(+)
1537 AND cust.party_id = party.party_id(+)
1538 AND ard.line_id = lk.source_distribution_id_num_1
1539 AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
1540 AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
1541 AND l_cat.lookup_code = ''TRADE_''||decode (ae.accounting_class_code, ''UNAPP'', ''UNAPP'', ard.source_type)
1542 AND lk.application_id = 222
1543 AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
1544 AND ae.application_id = 222
1545 AND decode (ae.accounting_class_code, ''UNAPP'', ''UNAPP'', ard.source_type) = ''UNAPP''
1546 AND hd.event_type_code <> ''MANUAL''
1550 AND crh.event_id IS NOT NULL
1547 AND ard.source_table = ''CRH''
1548 AND crh.cash_receipt_id = cr.cash_receipt_id
1549 AND cr.set_of_books_id = hd.ledger_id
1551 AND nvl(cr.confirmed_flag,''Y'') = ''Y''
1552 AND crh.first_posted_record_flag = ''Y''
1553 '||l_ard_org_where||'
1554 '||l_ps_org_where||'
1555 '||l_cr_org_where||'
1556 '||l_crh_org_where||'
1557 '||l_bat_org_where||'
1558 '||l_bs_org_where||'
1559 '||l_gl_date_crh_where ||'
1560 '||l_co_seg_where ||'
1561 '||l_account_where ||'
1562 '||l_account_seg_where ||'
1563 '||l_posting_status_crh_where||'
1564 UNION ALL
1565 select /*+ leading(crh) index(crh AR_CASH_RECEIPT_HISTORY_N2) index(ps AR_PAYMENT_SCHEDULES_U2)*/
1566 cr.receipt_number payment_number,
1567 arm.name payment_method,
1568 substrb(party.party_name,1,50) customer_name,
1569 cust.account_number customer_number,
1570 to_char(crh.gl_date,''YYYY-MM-DD'') app_gl_date,
1571 to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
1572 nvl(ard.amount_dr,0) entered_debit,
1573 nvl(ard.amount_cr,0) entered_credit,
1574 nvl(ard.acctd_amount_dr,0) acctd_debit,
1575 nvl(ard.acctd_amount_cr,0) acctd_credit,
1576 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
1577 cr.currency_code receipt_currency,
1578 gc.code_combination_id,
1579 bs.name receipt_source,
1580 bat.name batch_name,
1581 l_cat.meaning category,
1582 /* 7008877 */
1583 ard.line_id,
1584 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
1585 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
1586 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
1587 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
1588 ar_receipt_methods arm,
1589 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
1590 gl_code_combinations gc,
1591 hz_cust_accounts cust,
1592 hz_parties party,
1593 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
1594 ar_batch_sources bs,
1595 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
1596 ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
1597 ar_lookups l_cat
1598 where ps.class = ''PMT'' --ps.cash_receipt_id = ard.cash_receipt_id
1599 '||l_gl_date_closed_where||'
1600 AND cr.cash_receipt_id = ps.cash_receipt_id
1601 AND cr.receipt_method_id = arm.receipt_method_id
1602 AND crh.cash_receipt_id = cr.cash_receipt_id
1603 AND crh.batch_id = bat.batch_id(+)
1604 AND bat.batch_source_id = bs.batch_source_id(+)
1605 AND bat.org_id = bs.org_id(+)
1606 AND gc.code_combination_id = ard.code_combination_id
1607 --AND ard.cash_receipt_id = ps.cash_receipt_id
1608 AND ard.source_id = crh.cash_receipt_history_id
1609 AND cr.pay_from_customer = cust.cust_account_id(+)
1610 AND cust.party_id = party.party_id(+)
1611 AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
1612 AND ard.source_table = ''CRH''
1613 AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
1614 AND l_cat.lookup_code = ''TRADE_''||ard.source_type
1615 AND crh.event_id IS NULL
1616 AND crh.posting_control_id <> - 3
1617 AND ard.source_type = ''UNAPP''
1618 AND crh.first_posted_record_flag = ''Y''
1619 AND nvl(cr.confirmed_flag,''Y'') = ''Y''
1620 '||l_ard_org_where||'
1621 '||l_ps_org_where||'
1622 '||l_cr_org_where||'
1623 '||l_crh_org_where||'
1624 '||l_bat_org_where||'
1625 '||l_bs_org_where||'
1626 '||l_gl_date_crh_where ||'
1627 '||l_co_seg_where ||'
1628 '||l_account_where ||'
1629 '||l_account_seg_where ||'
1630 '||l_posting_status_crh_where||')';
1631
1632 l_xml_build(NVL(l_xml_build.LAST, 0)+ 1) := l_xml_build_sql;
1633 fnd_file.put_line(FND_FILE.LOG, l_xml_build_sql);
1634 l_xml_query := l_xml_query||to_clob(l_xml_build_sql);
1635
1636 l_xml_build_sql := 'UNION
1637 (select /*+ leading(ra) index(ra AR_RECEIVABLE_APPLICATIONS_N6) index(ps AR_PAYMENT_SCHEDULES_U2)*/
1638 cr.receipt_number payment_number,
1639 arm.name payment_method,
1640 substrb(party.party_name,1,50) customer_name,
1641 cust.account_number customer_number,
1642 to_char(hd.accounting_date,''YYYY-MM-DD'') app_gl_date,
1643 to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
1644 nvl(lk.unrounded_entered_dr,0) entered_debit,
1645 nvl(lk.unrounded_entered_cr,0) entered_credit,
1646 nvl(lk.unrounded_accounted_dr,0) acctd_debit,
1647 nvl(lk.unrounded_accounted_cr,0) acctd_credit,
1648 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
1649 cr.currency_code receipt_currency,
1650 gc.code_combination_id,
1651 bs.name receipt_source,
1652 bat.name batch_name,
1653 l_cat.meaning category,
1654 /* 7008877 */
1655 ard.line_id,
1656 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
1657 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
1658 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
1659 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
1660 ar_receipt_methods arm,
1661 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
1662 gl_code_combinations gc,
1663 hz_cust_accounts cust,
1664 hz_parties party,
1665 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
1666 ar_batch_sources bs,
1667 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
1668 ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
1669 ar_receivable_applications ra,
1670 xla_distribution_links lk,
1671 xla_ae_lines ae,
1672 xla_ae_headers hd,
1673 ar_lookups l_cat
1674 where ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
1675 , ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
1676 , ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
1677 , ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
1678 , ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
1679 , ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
1680 , ''GAIN'', ''LOSS'',''UNID''
1681 , ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
1682 , ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
1683 , ''FAC_BR'', ''PMT_NET'', ''PREPAY''
1684 , ''REFUND'', ''REM_BR'', ''UNAPP'')
1685 --AND ps.cash_receipt_id = ard.cash_receipt_id
1686 AND ps.class = ''PMT''
1687 '||l_gl_date_closed_where||'
1688 AND cr.cash_receipt_id = ps.cash_receipt_id
1689 AND cr.receipt_method_id = arm.receipt_method_id
1690 AND crh.cash_receipt_id = cr.cash_receipt_id
1691 AND crh.batch_id = bat.batch_id(+)
1692 AND bat.batch_source_id = bs.batch_source_id(+)
1693 AND bat.org_id = bs.org_id(+)
1694 AND gc.code_combination_id = ae.code_combination_id
1695 AND ra.cash_receipt_id = ps.cash_receipt_id
1696 AND ard.source_id = ra.receivable_application_id
1697 AND cr.pay_from_customer = cust.cust_account_id(+)
1698 AND cust.party_id = party.party_id(+)
1699 AND ard.line_id = lk.source_distribution_id_num_1
1700 AND lk.ae_header_id = ae.ae_header_id
1701 AND lk.ae_line_num = ae.ae_line_num
1702 AND lk.ae_header_id = hd.ae_header_id
1703 AND ra.set_of_books_id = hd.ledger_id
1704 AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
1705 AND hd.event_type_code <> ''MANUAL''
1706 AND crh.first_posted_record_flag = ''Y''
1707 AND nvl(cr.confirmed_flag,''Y'') = ''Y''
1708 AND lk.application_id = 222
1709 AND lk.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
1710 AND ae.application_id = 222
1711 AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
1712 AND l_cat.lookup_code = ''TRADE_''||decode (ae.accounting_class_code, ''UNAPP''
1713 , ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
1714 , decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
1715 , ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type)
1716 AND ard.source_table = ''RA''
1717 AND ra.event_id IS NOT NULL
1718 AND decode (ae.accounting_class_code, ''UNAPP''
1719 , ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
1720 , decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
1721 , ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type) = ''UNAPP''
1722 '||l_ard_org_where||'
1723 '||l_ps_org_where||'
1724 '||l_cr_org_where||'
1725 '||l_crh_org_where||'
1726 '||l_bat_org_where||'
1727 '||l_bs_org_where||'
1728 '||l_gl_date_where ||'
1729 '||l_co_seg_where ||'
1730 '||l_account_where ||'
1731 '||l_account_seg_where ||'
1732 '||l_posting_status_where||'
1733 UNION ALL
1734 select /*+ leading(ra) index(ra AR_RECEIVABLE_APPLICATIONS_N6) index(ps AR_PAYMENT_SCHEDULES_U2)*/
1735 cr.receipt_number payment_number,
1736 arm.name payment_method,
1737 substrb(party.party_name,1,50) customer_name,
1738 cust.account_number customer_number,
1739 to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
1740 to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
1741 nvl(ard.amount_dr,0) entered_debit,
1742 nvl(ard.amount_cr,0) entered_credit,
1743 nvl(ard.acctd_amount_dr,0) acctd_debit,
1744 nvl(ard.acctd_amount_cr,0) acctd_credit,
1745 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
1746 cr.currency_code receipt_currency,
1747 gc.code_combination_id,
1748 bs.name receipt_source,
1749 bat.name batch_name,
1750 l_cat.meaning category,
1751 /* 7008877 */
1752 ard.line_id,
1753 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
1754 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
1755 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
1756 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
1757 ar_receipt_methods arm,
1758 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
1759 gl_code_combinations gc,
1760 hz_cust_accounts cust,
1761 hz_parties party,
1762 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
1763 ar_batch_sources bs,
1764 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
1765 ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
1766 ar_receivable_applications ra,
1767 ar_lookups l_cat
1768 where ra.status IN (''APP'', ''ACC'', ''ACTIVITY'', ''OTHER ACC'')
1769 --AND ps.cash_receipt_id = ard.cash_receipt_id
1770 AND ps.class = ''PMT''
1771 '||l_gl_date_closed_where||'
1772 AND cr.cash_receipt_id = ps.cash_receipt_id
1773 AND cr.receipt_method_id = arm.receipt_method_id
1774 AND crh.cash_receipt_id = cr.cash_receipt_id
1775 AND crh.batch_id = bat.batch_id(+)
1776 AND bat.batch_source_id = bs.batch_source_id(+)
1777 AND bat.org_id = bs.org_id(+)
1778 AND gc.code_combination_id = ard.code_combination_id
1779 --AND ard.cash_receipt_id = ps.cash_receipt_id
1780 AND ard.source_id = ra.receivable_application_id
1781 AND cr.pay_from_customer = cust.cust_account_id(+)
1782 AND cust.party_id = party.party_id(+)
1783 AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
1784 AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
1785 AND l_cat.lookup_code = ''TRADE_''||ard.source_type
1786 AND ard.source_type = ''UNAPP''
1787 AND nvl(cr.confirmed_flag,''Y'') = ''Y''
1788 AND crh.first_posted_record_flag = ''Y''
1789 AND ard.source_table = ''RA''
1790 AND ra.event_id IS NULL
1791 AND ra.posting_control_id <> - 3
1792 '||l_ard_org_where||'
1793 '||l_ps_org_where||'
1794 '||l_cr_org_where||'
1795 '||l_crh_org_where||'
1796 '||l_bat_org_where||'
1797 '||l_bs_org_where||'
1798 '||l_gl_date_where ||'
1799 '||l_co_seg_where ||'
1800 '||l_account_where ||'
1801 '||l_account_seg_where ||'
1802 '||l_posting_status_nul_where||'
1803 UNION ALL
1804 select /*+ leading(ra) index(ra AR_RECEIVABLE_APPLICATIONS_N6) index(ps AR_PAYMENT_SCHEDULES_U2)*/
1805 cr.receipt_number payment_number,
1806 arm.name payment_method,
1807 substrb(party.party_name,1,50) customer_name,
1808 cust.account_number customer_number,
1809 to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
1810 to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
1811 nvl(ard.amount_dr,0) entered_debit,
1812 nvl(ard.amount_cr,0) entered_credit,
1813 nvl(ard.acctd_amount_dr,0) acctd_debit,
1814 nvl(ard.acctd_amount_cr,0) acctd_credit,
1815 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
1816 cr.currency_code receipt_currency,
1817 gc.code_combination_id,
1818 bs.name receipt_source,
1819 bat.name batch_name,
1820 l_cat.meaning category,
1821 /* 7008877 */
1822 ard.line_id,
1823 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
1824 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
1825 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
1826 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
1827 ar_receipt_methods arm,
1828 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
1829 gl_code_combinations gc,
1830 hz_cust_accounts cust,
1831 hz_parties party,
1832 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
1833 ar_batch_sources bs,
1834 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
1835 ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
1836 ar_receivable_applications ra,
1837 ar_lookups l_cat
1838 where ra.status IN (''UNAPP'', ''UNID'')
1839 --AND ps.cash_receipt_id = ard.cash_receipt_id
1840 AND ps.class = ''PMT''
1841 '||l_gl_date_closed_where||'
1842 AND cr.cash_receipt_id = ps.cash_receipt_id
1843 AND cr.receipt_method_id = arm.receipt_method_id
1844 AND crh.cash_receipt_id = cr.cash_receipt_id
1845 AND crh.batch_id = bat.batch_id(+)
1846 AND bat.batch_source_id = bs.batch_source_id(+)
1847 AND bat.org_id = bs.org_id(+)
1848 AND gc.code_combination_id = ard.code_combination_id
1849 AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
1850 --AND ard.cash_receipt_id = ps.cash_receipt_id
1851 AND ard.source_id = ra.receivable_application_id
1852 AND cr.pay_from_customer = cust.cust_account_id(+)
1853 AND cust.party_id = party.party_id(+)
1854 AND EXISTS
1855 (SELECT NULL
1856 FROM xla_distribution_links lk
1857 WHERE lk.source_distribution_id_num_1 = ard.line_id
1858 AND lk.application_id = 222
1859 AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'')
1860 AND nvl(cr.confirmed_flag,''Y'') = ''Y''
1861 AND crh.first_posted_record_flag = ''Y''
1862 AND ard.source_table = ''RA''
1863 AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
1864 AND l_cat.lookup_code = ''TRADE_''||ard.source_type
1865 AND ard.source_type = ''UNAPP''
1866 AND ra.event_id IS NULL
1867 AND ra.posting_control_id <> - 3
1868 '||l_ard_org_where||'
1869 '||l_ps_org_where||'
1870 '||l_cr_org_where||'
1871 '||l_crh_org_where||'
1872 '||l_bat_org_where||'
1873 '||l_bs_org_where||'
1874 '||l_gl_date_where ||'
1875 '||l_co_seg_where ||'
1876 '||l_account_where ||'
1877 '||l_account_seg_where ||'
1878 '||l_posting_status_nul_where||')';
1879
1880 l_xml_build(NVL(l_xml_build.LAST, 0)+ 1) := l_xml_build_sql;
1881 fnd_file.put_line(FND_FILE.LOG, l_xml_build_sql);
1882 l_xml_query := l_xml_query||to_clob(l_xml_build_sql);
1883
1884 DBMS_UTILITY.DB_VERSION(l_version, l_compatibility);
1885 l_majorVersion := to_number(substr(l_version, 1, instr(l_version,'.')-1));
1886
1887 IF (l_majorVersion > 8 and l_majorVersion < 11) THEN
1888 BEGIN
1889 queryCtx := DBMS_XMLQuery.newContext(l_xml_query);
1890 DBMS_XMLQuery.setRaiseNoRowsException(queryCtx,TRUE);
1891 l_result := DBMS_XMLQuery.getXML(queryCtx);
1895 DBMS_XMLQuery.getExceptionContent(queryCtx,l_errNo,l_errMsg);
1892 DBMS_XMLQuery.closeContext(queryCtx);
1893 l_rows_processed := 1;
1894 EXCEPTION WHEN OTHERS THEN
1896 IF l_errNo = 1403 THEN
1897 l_rows_processed := 0;
1898 END IF;
1899 DBMS_XMLQuery.closeContext(queryCtx);
1900 END;
1901 /*
1902 Bug# 10177944
1903 Starting with 10g DBMS_XMLQUERY package(written java) is deprecated and
1904 it is recommended to use DBMS_XMLGEN package.DBMS_XMLGEN package is similar
1905 to the DBMS_XMLQuery package, except that it is written in C and
1906 compiled into the database kernel.The issue with the DBMS_XMLGEN package is that
1907 it formats the number based on the preferences set.
1908 If the NLS NUMERIC CHARACTERS is set to ",." then the package generates the XML
1909 in such away that all the numbers are formatted using the NLS_NUMERIC_CHARACTERS.
1910 Say, if the number is 12840.5(Raw format) and if NLS_NUMERIC_CHARACTERS is set to ",.",
1911 then the XML generated will have the number as 12.840,50
1912
1913 Issue with Formatting:
1914 This procedure (generate_xml) is to generate the XML for the aging report.
1915 Once the XML got generated, OPP will pickup the XML and transform it into a Report using a template.
1916 While transforming the report, it there exists any field of type number on the template, again
1917 it tries to convert into Number. If the number is not in a raw format then an exception is raised.
1918 As OPP always expects the numbers in raw format,we need to alter the session such that number
1919 gets generated in raw format
1920 */
1921 ELSIF (l_majorVersion >= 11 ) THEN
1922
1923 /*Flag to detrmine if the session has been altered */
1924 l_nls_altered := FALSE;
1925
1926 /*Get the NLS_NUMERIC_CHAR Settings from Session*/
1927 SELECT value INTO l_nls_numeric_char
1928 FROM v$NLS_PARAMETERS
1929 WHERE parameter = 'NLS_NUMERIC_CHARACTERS';
1930
1931 /*Set the NLS_NUMERIC_CHAR Setting*/
1932 IF l_nls_numeric_char <> '.,' THEN
1933 l_alter_str:= 'ALTER SESSION SET nls_numeric_characters=''.,''';
1934 EXECUTE IMMEDIATE l_alter_str;
1935 l_nls_altered := TRUE;
1936 l_alter_str:= NULL;
1937 END IF;
1938 --OPEN l_xml_ref FOR l_xml_query;
1939
1940 /* DBMS_SQL.TO_REFCURSOR is a feature of 11G database so there will be a compilation error for the customers in 10G to overcome it we have used execute immediate to serve the purpose */
1941
1942 l_dyn_block := 'DECLARE TYPE l_xml_ref_cur IS REF CURSOR; l_cursor l_xml_ref_cur; qryCtx DBMS_XMLGEN.ctxHandle; v_cur INTEGER; v_ret NUMBER;
1943 BEGIN v_cur:= DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_cur,:p1,DBMS_SQL.NATIVE); v_ret := DBMS_SQL.EXECUTE(v_cur); l_cursor := DBMS_SQL.TO_REFCURSOR(v_cur);
1944 qryCtx:= DBMS_XMLGEN.newContext(l_cursor); :p2:= DBMS_XMLGEN.getXML(qryCtx,DBMS_XMLGEN.NONE); :p3:= DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
1945 DBMS_XMLGEN.closeContext(qryCtx); CLOSE l_cursor; END;';
1946 execute immediate l_dyn_block using in l_xml_query,out l_result,out l_rows_processed;
1947
1948 END IF;
1949
1950 /* Changes for bug 10177944 */
1951 /*If NLS_NUMERIC_CHAR has been modified, restore the same*/
1952 IF l_nls_altered THEN
1953 l_alter_str := 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='''||l_nls_numeric_Char||'''';
1954 EXECUTE IMMEDIATE l_alter_str;
1955 END IF;
1956
1957 IF l_rows_processed <> 0 THEN
1958 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
1959 tempResult := l_result;
1960 ELSE
1961 l_resultOffset := 0;
1962 END IF;
1963
1964 l_new_line := '
1965 ';
1966
1967 select to_char(sysdate,'YYYY-MM-DD')
1968 into l_report_date
1969 from dual;
1970
1971 /* Bug 4708930
1972 Get the special characters replaced */
1973 l_reporting_entity_name := format_string(l_reporting_entity_name);
1974 l_reporting_level_name := format_string(l_reporting_level_name);
1975 l_organization := format_string(l_organization);
1976 l_receivable_mode_meaning := format_string(l_receivable_mode_meaning);
1977 l_status_meaning := format_string(l_status_meaning);
1978 l_sob_name := format_string(l_sob_name);
1979 l_message := format_string(l_message);
1980 l_message_acct := format_string(l_message_acct);
1981
1982 /* Prepare the tag for the report heading */
1983 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
1984 l_xml_header := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
1985 l_xml_header := l_xml_header ||l_new_line||'<ARUNAPPJOURNAL>';
1986 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT>'||l_message||'</MSG_TXT>';
1987 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT_ACCT>'||l_message_acct||'</MSG_TXT_ACCT>';
1988 l_xml_header := l_xml_header ||l_new_line||' <PARAMETERS>';
1989 l_xml_header := l_xml_header ||l_new_line||' <REPORT_DATE>'||l_report_date||'</REPORT_DATE>';
1990 l_xml_header := l_xml_header ||l_new_line||' <REPORTING_LEVEL>'||l_reporting_level_name||'</REPORTING_LEVEL>';
1991 l_xml_header := l_xml_header ||l_new_line||' <REPORTING_ENTITY>'||l_reporting_entity_name||'</REPORTING_ENTITY>';
1992 l_xml_header := l_xml_header ||l_new_line||' <SOB_ID>'||p_sob_id||'</SOB_ID>';
1993 l_xml_header := l_xml_header ||l_new_line||' <CO_SEG_LOW>'||p_co_seg_low||'</CO_SEG_LOW>';
1994 l_xml_header := l_xml_header ||l_new_line||' <CO_SEG_HIGH>'||p_co_seg_high||'</CO_SEG_HIGH>';
1995 l_xml_header := l_xml_header ||l_new_line||' <GL_DATE_FROM>'||to_char(fnd_date.canonical_to_date(p_gl_date_from),'YYYY-MM-DD')||'</GL_DATE_FROM>';
1996 l_xml_header := l_xml_header ||l_new_line||' <GL_DATE_TO>'||to_char(fnd_date.canonical_to_date(p_gl_date_to),'YYYY-MM-DD')||'</GL_DATE_TO>';
1997 l_xml_header := l_xml_header ||l_new_line||' <POSTING_STATUS>'||l_status_meaning||'</POSTING_STATUS>';
1998 l_xml_header := l_xml_header ||l_new_line||' <GL_ACCOUNT_LOW>'||p_gl_account_low||'</GL_ACCOUNT_LOW>';
1999 l_xml_header := l_xml_header ||l_new_line||' <GL_ACCOUNT_HIGH>'||p_gl_account_high||'</GL_ACCOUNT_HIGH>';
2000 l_xml_header := l_xml_header ||l_new_line||' <SUMMARY_ACCOUNT>'||p_summary_account||'</SUMMARY_ACCOUNT>';
2001 l_xml_header := l_xml_header ||l_new_line||' <RECEIVABLES_MODE_ONLY>'||l_receivable_mode_meaning||'</RECEIVABLES_MODE_ONLY>';
2002 l_xml_header := l_xml_header ||l_new_line||' <NUM_ROWS>'||l_rows_processed||'</NUM_ROWS>';
2003 l_xml_header := l_xml_header ||l_new_line||' </PARAMETERS>';
2004 l_xml_header := l_xml_header ||l_new_line||' <REPORT_HEADING>';
2005 l_xml_header := l_xml_header ||l_new_line||' <SET_OF_BOOKS>'||l_sob_name||'</SET_OF_BOOKS>';
2006 l_xml_header := l_xml_header ||l_new_line||' <ORGANIZATION>'||l_organization||'</ORGANIZATION>';
2007 l_xml_header := l_xml_header ||l_new_line||' <FUNCTIONAL_CURRENCY>'||l_functional_currency||'</FUNCTIONAL_CURRENCY>';
2008 l_xml_header := l_xml_header ||l_new_line||' </REPORT_HEADING>';
2009
2010 l_close_tag := l_new_line||'</ARUNAPPJOURNAL>'||l_new_line;
2011 l_xml_header_length := length(l_xml_header);
2012 IF l_rows_processed <> 0 THEN
2013 dbms_lob.write(tempResult,l_xml_header_length,1,l_xml_header);
2014 dbms_lob.copy(tempResult,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
2015 l_xml_header_length,l_resultOffset);
2016 ELSE
2017 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
2018 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
2019 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
2020 END IF;
2021
2022 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
2023
2024 ar_cumulative_balance_report.process_clob(tempResult);
2025 p_result := tempResult;
2026
2027 log('arunapp_journal_load_xml (-)');
2028
2029 /* Start changes for bug 10177944 */
2030 EXCEPTION
2031 WHEN OTHERS THEN
2032 /*If NLS_NUMERIC_CHAR has been modified, restore the same*/
2033 IF l_nls_altered THEN
2034 l_alter_str := 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='''||l_nls_numeric_Char||'''';
2035 EXECUTE IMMEDIATE l_alter_str;
2036 END IF;
2037
2038 RAISE;
2039 /* End changes for bug 10177944 */
2040
2041 END arunapp_journal_load_xml;
2042
2043 /*========================================================================+
2044 | PUBLIC PROCEDURE ARAPP_JOURNAL_LOAD_XML |
2045 | |
2046 | DESCRIPTION |
2047 | |
2048 | This procedure is used to generate the XML data required for reporting|
2049 | Applied Receipts Journals |
2050 | |
2051 | PSEUDO CODE/LOGIC |
2052 | |
2053 | PARAMETERS |
2054 | |
2055 | |
2056 | KNOWN ISSUES |
2057 | |
2058 | NOTES |
2059 | |
2060 | |
2061 | MODIFICATION HISTORY |
2062 | Date Author Description of Changes |
2063 | 03-FEB-2004 rkader Created |
2064 | |
2065 *=======================================================================*/
2066
2067 PROCEDURE arapp_journal_load_xml (
2068 p_reporting_level IN VARCHAR2,
2069 p_reporting_entity_id IN NUMBER,
2070 p_sob_id IN NUMBER,
2071 p_coa_id IN NUMBER,
2072 p_co_seg_low IN VARCHAR2,
2073 p_co_seg_high IN VARCHAR2,
2074 p_gl_date_from IN VARCHAR2,
2075 p_gl_date_to IN VARCHAR2,
2076 p_posting_status IN VARCHAR2,
2077 p_gl_account_low IN VARCHAR2,
2078 p_gl_account_high IN VARCHAR2,
2079 p_summary_account IN NUMBER,
2080 p_receivable_mode IN VARCHAR2,
2081 p_result OUT NOCOPY CLOB) IS
2082 l_result CLOB;
2083 tempResult CLOB;
2084 l_version varchar2(20);
2085 l_compatibility varchar2(20);
2086 l_suffix varchar2(2);
2087 l_majorVersion number;
2088 l_resultOffset number;
2089 l_xml_header varchar2(3000);
2090 l_xml_header_length number;
2091 l_errNo NUMBER;
2092 l_errMsg VARCHAR2(200);
2093 queryCtx DBMS_XMLquery.ctxType;
2094 qryCtx DBMS_XMLGEN.ctxHandle;
2095 l_xml_query CLOB;
2096 l_natural_segment_col VARCHAR2(50);
2097 l_flex_value_set_id NUMBER;
2098 l_code_combinations VARCHAR2(32767);
2099 TYPE ref_cur IS REF CURSOR;
2100 l_xml_stmt ref_cur;
2101 l_rows_processed NUMBER;
2102 l_new_line VARCHAR2(1);
2103 l_coa_id NUMBER; /*bufg fix 5654975*/
2104 /* Variables to hold the report heading */
2105 l_sob_id NUMBER;
2106 l_sob_name VARCHAR2(100);
2107 l_functional_currency VARCHAR2(15);
2108 l_organization VARCHAR2(60);
2109 l_format VARCHAR2(40);
2110 l_close_tag VARCHAR2(100);
2111 l_reporting_entity_name VARCHAR2(80);
2112 l_reporting_level_name VARCHAR2(30);
2113 l_status_meaning VARCHAR2(30);
2114 l_receivable_mode_meaning VARCHAR2(10);
2115 /* Variables to hold the where clause based on the input parameters*/
2116 /* Variables length changed from 200 to 500 to address bug:5181586*/
2117 /* Increased variables length to 32767 for bug 5654975 */
2118 l_ra_org_where VARCHAR2(32767);
2119 l_ard_org_where VARCHAR2(32767);
2120 l_ard1_org_where VARCHAR2(32767);
2121 l_ps_org_where VARCHAR2(32767);
2122 l_cr_org_where VARCHAR2(32767);
2123 l_crh_org_where VARCHAR2(32767);
2124 l_bat_org_where VARCHAR2(32767);
2125 l_bs_org_where VARCHAR2(32767);
2126 l_sysparam_org_where VARCHAR2(32767);
2127 /* Changes to variable length ends*/
2128 l_co_seg_where VARCHAR2(32767);
2129 l_account_where VARCHAR2(32767);
2130 l_account_seg_where VARCHAR2(32767);
2131 l_gl_date_where VARCHAR2(1000);
2132 l_gl_date_ard_where VARCHAR2(1000);
2133 /* Variable length changed from 900 to 1000 to address bug:5181586*/
2134 l_source_type_where VARCHAR2(32767);
2135 /*Change to variable length ends*/
2136 l_posting_status_where VARCHAR2(1000);
2137 l_posting_status_ard_where VARCHAR2(1000);
2138 l_report_date VARCHAR2(25);
2139 l_ld_sp VARCHAR2(1) := 'Y';
2140 l_message VARCHAR2(2000);
2141 l_encoding VARCHAR2(20);
2142 l_message_acct VARCHAR2(1000);
2143
2144 /* Changes for bug 10177944 */
2145 l_nls_numeric_char VARCHAR2(5);
2146 l_nls_altered BOOLEAN;
2147 l_alter_str VARCHAR2(100);
2148 l_xml_build DBMS_SQL.VARCHAR2A;
2149 l_xml_build_sql VARCHAR2(32767);
2150 l_cur_id INTEGER;
2151 TYPE l_xml_ref_cur IS REF CURSOR;
2152 l_xml_ref l_xml_ref_cur;
2153 l_posting_status_nul_where VARCHAR2(1000);
2154 l_source_type_ard_where VARCHAR2(32767);
2155 l_dyn_block VARCHAR2(1000);
2156 BEGIN
2157
2158 log('arapp_journal_load_xml (+)');
2159
2160 /* Assign the input parameters to the global variables */
2161 arp_recon_rep.var_tname.g_reporting_level := p_reporting_level;
2162 arp_recon_rep.var_tname.g_reporting_entity_id := p_reporting_entity_id;
2163 /* bug 5654975 p_coa_id,p_sob_id is passed incorrectly when the user
2164 has access to multiple Ledgers */
2165 -- arp_recon_rep.var_tname.g_set_of_books_id := p_sob_id;
2166 -- arp_recon_rep.var_tname.g_chart_of_accounts_id := p_coa_id;
2167 arp_recon_rep.var_tname.g_gl_date_from := fnd_date.canonical_to_date(p_gl_date_from);
2168 arp_recon_rep.var_tname.g_gl_date_to := fnd_date.canonical_to_date(p_gl_date_to);
2169 arp_recon_rep.var_tname.g_posting_status := p_posting_status;
2170
2171 /* Added Conditional Implication to address bug:5181586*/
2172 /*Added set of books id and char of accounts id for bug fix 5654975 */
2173 IF p_reporting_level = 1000 THEN
2174 SELECT sob.name sob_name,
2175 sob.set_of_books_id,
2176 sob.currency_code functional_currency,
2177 sob.chart_of_accounts_id
2178 INTO l_sob_name,
2179 l_sob_id,
2180 l_functional_currency,
2181 l_coa_id
2182 FROM gl_sets_of_books sob
2183 WHERE sob.set_of_books_id = arp_recon_rep.var_tname.g_reporting_entity_id;
2184
2185 ELSIF p_reporting_level = 3000 THEN
2186 SELECT sob.name sob_name,
2187 sob.set_of_books_id,
2188 sob.currency_code functional_currency,
2189 sob.chart_of_accounts_id
2190 INTO l_sob_name,
2191 l_sob_id,
2192 l_functional_currency,
2193 l_coa_id
2194 FROM gl_sets_of_books sob,
2195 ar_system_parameters sysparam
2196 WHERE sob.set_of_books_id = sysparam.set_of_books_id
2197 AND sysparam.org_id = arp_recon_rep.var_tname.g_reporting_entity_id;
2198
2199 END IF;
2200 /* Changes for bug:5181586 ends*/
2201
2202 arp_recon_rep.var_tname.g_set_of_books_id := l_sob_id;
2203 arp_recon_rep.var_tname.g_chart_of_accounts_id := l_coa_id;
2204
2205 /* Initialize the reporting context */
2206 init(p_sob_id);
2207
2208 /* Set the org conditions */
2209
2210 XLA_MO_REPORTING_API.Initialize(p_reporting_level, p_reporting_entity_id, 'AUTO');
2211
2212 l_ra_org_where := XLA_MO_REPORTING_API.Get_Predicate('ra',NULL);
2213 l_ard_org_where := XLA_MO_REPORTING_API.Get_Predicate('ard',NULL);
2214 l_ard1_org_where := XLA_MO_REPORTING_API.Get_Predicate('ard1',NULL);
2215 l_ps_org_where := XLA_MO_REPORTING_API.Get_Predicate('ps',NULL);
2216 l_cr_org_where := XLA_MO_REPORTING_API.Get_Predicate('cr',NULL);
2217 l_crh_org_where := XLA_MO_REPORTING_API.Get_Predicate('crh',NULL);
2218 l_bat_org_where := XLA_MO_REPORTING_API.Get_Predicate('bat',NULL);
2219 l_bs_org_where := XLA_MO_REPORTING_API.Get_Predicate('bs',NULL);
2220 l_sysparam_org_where := XLA_MO_REPORTING_API.Get_Predicate('sysparam',NULL);
2221
2222 /* Replace the bind variables with global functions */
2223 l_ra_org_where := replace(l_ra_org_where,
2224 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2225 l_ard_org_where := replace(l_ard_org_where,
2226 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2227 l_ard1_org_where := replace(l_ard1_org_where,
2228 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2229 l_ps_org_where := replace(l_ps_org_where,
2230 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2231 l_cr_org_where := replace(l_cr_org_where,
2232 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2233 l_crh_org_where := replace(l_crh_org_where,
2234 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2235 l_bat_org_where := replace(l_bat_org_where,
2236 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2237 l_bs_org_where := replace(l_bs_org_where,
2238 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2239 l_sysparam_org_where := replace(l_sysparam_org_where,
2240 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2241
2242
2243 l_reporting_entity_name := substrb(XLA_MO_REPORTING_API.get_reporting_entity_name,1,80);
2244 l_reporting_level_name := substrb(XLA_MO_REPORTING_API.get_reporting_level_name,1,30);
2245
2246 /* Multi Org Uptake: Show appropriate message to the user depending upon the security profile */
2247 IF p_reporting_level = '1000' THEN
2248 l_ld_sp:= mo_utils.check_ledger_in_sp(p_reporting_entity_id);
2249 END IF;
2250
2251 IF l_ld_sp = 'N' THEN
2252 FND_MESSAGE.SET_NAME('FND','FND_MO_RPT_PARTIAL_LEDGER');
2253 l_message := FND_MESSAGE.get;
2254 END IF;
2255
2256 /* Bug fix 4942083*/
2257 IF arp_util.Open_Period_Exists(p_reporting_level,
2258 p_reporting_entity_id,
2259 arp_recon_rep.var_tname.g_gl_date_from,
2260 arp_recon_rep.var_tname.g_gl_date_to) THEN
2261 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
2262 l_message_acct := FND_MESSAGE.Get;
2263 END IF;
2264
2265 /* Get the org name */
2266 IF p_reporting_level = '3000' THEN
2267 select substrb(hou.name,1,60)
2268 into l_organization
2269 from hr_organization_units hou
2270 where hou.organization_id = arp_recon_rep.var_tname.g_reporting_entity_id;
2271 ELSE
2272 select meaning
2273 into l_organization
2274 from ar_lookups
2275 where lookup_code ='ALL' and lookup_type ='ALL';
2276 END IF;
2277
2278
2279 /* Build the WHERE clauses */
2280 /*Replaced p_coa_id with l_coa_id for bug 5654975*/
2281
2282 IF p_co_seg_low IS NULL AND p_co_seg_high IS NULL THEN
2283 l_co_seg_where := NULL;
2284 ELSIF p_co_seg_low IS NULL THEN
2285 l_co_seg_where := ' AND ' ||
2286 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
2287 p_id_flex_code => 'GL#',
2288 p_id_flex_num => l_coa_id,
2289 p_table_alias => 'GC',
2290 p_mode => 'WHERE',
2291 p_qualifier => 'GL_BALANCING',
2292 p_function => '<=',
2293 p_operand1 => p_co_seg_high);
2294 ELSIF p_co_seg_high IS NULL THEN
2295 l_co_seg_where := ' AND ' ||
2296 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
2297 p_id_flex_code => 'GL#',
2298 p_id_flex_num => l_coa_id,
2299 p_table_alias => 'GC',
2300 p_mode => 'WHERE',
2301 p_qualifier => 'GL_BALANCING',
2302 p_function => '>=',
2303 p_operand1 => p_co_seg_low);
2304 ELSE
2305 l_co_seg_where := ' AND ' ||
2306 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
2307 p_id_flex_code => 'GL#',
2308 p_id_flex_num => l_coa_id,
2309 p_table_alias => 'GC',
2310 p_mode => 'WHERE',
2311 p_qualifier => 'GL_BALANCING',
2312 p_function => 'BETWEEN',
2313 p_operand1 => p_co_seg_low,
2314 p_operand2 => p_co_seg_high);
2315 END IF;
2316
2317
2318 IF p_gl_date_from IS NULL and p_gl_date_to IS NULL THEN
2319 l_gl_date_where := NULL;
2320 l_gl_date_ard_where := NULL;
2321 ELSIF p_gl_date_from IS NULL THEN
2322 l_gl_date_where :=' and ra.gl_date <= arp_recon_rep.get_gl_date_to()';
2323 l_gl_date_ard_where :=' and hd.accounting_date <= arp_recon_rep.get_gl_date_to()';
2324 ELSIF p_gl_date_to IS NULL THEN
2325 l_gl_date_where :=' and ra.gl_date >= arp_recon_rep.get_gl_date_from() ' ;
2326 l_gl_date_ard_where :=' and hd.accounting_date >= arp_recon_rep.get_gl_date_from() ' ;
2327 ELSE
2328 l_gl_date_where := ' and ra.gl_date between arp_recon_rep.get_gl_date_from() and arp_recon_rep.get_gl_date_to() ';
2329 l_gl_date_ard_where := ' and hd.accounting_date between arp_recon_rep.get_gl_date_from() and arp_recon_rep.get_gl_date_to() ';
2330 END IF;
2331
2332 IF p_gl_account_low IS NOT NULL AND p_gl_account_high IS NOT NULL THEN
2333 l_account_where := ' AND ' || AR_CALC_AGING.FLEX_SQL(
2334 p_application_id=> 101,
2335 p_id_flex_code =>'GL#',
2336 p_id_flex_num =>l_coa_id,
2337 p_table_alias => 'gc',
2338 p_mode => 'WHERE',
2339 p_qualifier => 'ALL',
2340 p_function=> 'BETWEEN',
2341 p_operand1 => p_gl_account_low,
2342 p_operand2 => p_gl_account_high);
2343 ELSE
2344 l_account_where := NULL;
2345 END IF;
2346
2347 IF p_summary_account IS NOT NULL THEN
2348 SELECT fcav.application_column_name, flex_value_set_id
2349 INTO l_natural_segment_col , l_flex_value_set_id
2350 FROM fnd_segment_attribute_values fcav,
2351 fnd_id_flex_segments fifs
2352 WHERE fcav.application_id = 101
2353 AND fcav.id_flex_code = 'GL#'
2354 AND fcav.id_flex_num = arp_recon_rep.var_tname.g_chart_of_accounts_id
2355 AND fcav.attribute_value = 'Y'
2356 AND fcav.segment_attribute_type = 'GL_ACCOUNT'
2357 AND fifs.application_id = fcav.application_id
2358 AND fifs.id_flex_code = fcav.id_flex_code
2359 AND fifs.id_flex_num = fcav.id_flex_num
2360 AND fcav.application_column_name = fifs.application_column_name;
2361
2362 get_detail_accounts(l_flex_value_set_id, p_summary_account, l_code_combinations);
2363
2364 l_account_seg_where := ' and gc.'||l_natural_segment_col||' in ('||l_code_combinations||' )';
2365 ELSE
2366 l_account_seg_where := NULL;
2367 END IF;
2368
2369
2370 IF nvl(p_receivable_mode,'N') = 'Y' THEN
2371 l_source_type_where := ' and ((decode (ae.accounting_class_code, ''UNAPP''
2372 , ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
2373 , decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
2374 , ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type) = ''REC'')
2375 OR (ps.class =''BR''
2376 and not exists (select line_id
2377 from ar_distributions_all ard1, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard1
2378 xla_distribution_links lk1,
2379 xla_ae_lines ae1,
2380 xla_ae_headers hd1
2381 where
2382 ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
2383 , ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
2384 , ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
2385 , ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
2386 , ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
2387 , ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
2388 , ''GAIN'', ''LOSS'', ''UNID''
2389 , ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
2390 , ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
2391 , ''FAC_BR'', ''PMT_NET'', ''PREPAY''
2392 , ''REFUND'', ''REM_BR'', ''UNAPP'')
2393 and ard1.source_id = ra.receivable_application_id
2394 and decode (ae1.accounting_class_code, ''UNAPP''
2395 , ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
2396 , decode (sign (nvl (lk1.unrounded_accounted_cr, 0) - nvl (lk1.unrounded_accounted_dr, 0)), - 1
2397 , ''EXCH_LOSS'', ''EXCH_GAIN''), ard1.source_type) = ''REC''
2398 and ard1.source_table =''RA''
2399 AND ard1.line_id = lk1.source_distribution_id_num_1
2400 AND lk1.application_id = 222
2401 AND lk1.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
2402 AND ae1.application_id = 222
2403 AND lk1.ae_header_id = ae1.ae_header_id
2404 AND lk1.ae_line_num = ae1.ae_line_num
2405 AND lk1.ae_header_id = hd1.ae_header_id
2406 AND ra.set_of_books_id = hd1.ledger_id
2407 AND hd1.event_type_code <> ''MANUAL''
2408 '|| l_ard1_org_where || ')
2409 and decode (ae.accounting_class_code, ''UNAPP''
2410 , ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
2411 , decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
2412 , ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type) in (''REMITTANCE'',''FACTOR'',''UNPAIDREC'')))';
2413
2414 l_source_type_ard_where := ' and ((ard.source_type = ''REC'')
2415 OR (ps.class =''BR''
2416 and not exists (select line_id
2417 from ar_distributions_all ard1 ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard1
2418 where ard1.source_id = ra.receivable_application_id
2419 and ard1.source_type = ''REC''
2420 and ard1.source_table =''RA''
2421 '|| l_ard1_org_where || ')
2422 and ard.source_type in (''REMITTANCE'',''FACTOR'',''UNPAIDREC'')))';
2423
2424 select meaning
2425 into l_receivable_mode_meaning
2426 from fnd_lookups
2427 where lookup_type = 'YES_NO'
2428 and lookup_code = 'Y';
2429 ELSE
2430 l_source_type_where := NULL;
2431 select meaning
2432 into l_receivable_mode_meaning
2433 from fnd_lookups
2434 where lookup_type = 'YES_NO'
2435 and lookup_code = 'N';
2436 END IF;
2437
2438 IF p_posting_status IS NOT NULL THEN
2439
2440 select meaning
2441 into l_status_meaning
2442 from ar_lookups
2443 where lookup_type = 'POSTED_STATUS'
2444 and lookup_code = arp_recon_rep.var_tname.g_posting_status;
2445
2446 l_posting_status_where := 'and nvl(ra.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')) =
2447 decode(arp_recon_rep.get_posting_status(),
2448 ''POSTED'',ra.gl_posted_date,
2449 ''UNPOSTED'',TO_DATE(''01/01/0001'',''MM/DD/YYYY''),
2450 nvl(ra.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')))';
2451
2452 l_posting_status_nul_where := 'and nvl(NULL,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')) =
2453 decode(arp_recon_rep.get_posting_status(),
2454 ''POSTED'',ra.gl_posted_date,
2455 ''UNPOSTED'',TO_DATE(''01/01/0001'',''MM/DD/YYYY''),
2456 nvl(ra.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')))';
2457
2458 l_posting_status_ard_where := 'and nvl(ard.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')) =
2459 decode(arp_recon_rep.get_posting_status(),
2460 ''POSTED'',ard.gl_posted_date,
2461 ''UNPOSTED'',TO_DATE(''01/01/0001'',''MM/DD/YYYY''),
2462 nvl(ard.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')))';
2463 ELSE
2464 l_status_meaning := NULL;
2465 l_posting_status_where := NULL;
2466 END IF;
2467
2468 l_xml_build_sql := 'select cr.receipt_number payment_number,
2469 arm.name payment_method,
2470 substrb(party.party_name,1,50) customer_name,
2471 cust.account_number customer_number,
2472 to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
2473 to_char(crh.gl_date,''YYYY-MM-DD'') payment_gl_date,
2474 nvl(lk.unrounded_entered_dr,0) entered_debit,
2475 nvl(lk.unrounded_entered_cr,0) entered_credit,
2476 nvl(lk.unrounded_accounted_dr,0) acctd_debit,
2477 nvl(lk.unrounded_accounted_cr,0) acctd_credit,
2478 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
2479 cr.currency_code receipt_currency,
2480 ps.trx_number trx_number,
2481 gc.code_combination_id,
2482 bs.name receipt_source,
2483 bat.name batch_name,
2484 l_cat.meaning category,
2485 ard.currency_code currency_code,
2486 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
2487 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
2488 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
2489 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
2490 ar_receipt_methods arm,
2491 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
2492 gl_code_combinations gc,
2493 hz_cust_accounts cust,
2497 '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
2494 hz_parties party,
2495 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
2496 ar_batch_sources bs,
2498 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
2499 ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
2500 xla_distribution_links lk,
2501 xla_ae_lines ae,
2502 xla_ae_headers hd,
2503 ar_lookups l_cat
2504 where ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
2505 , ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
2506 , ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
2507 , ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
2508 , ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
2509 , ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
2510 , ''GAIN'', ''LOSS'', ''UNID''
2511 , ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
2512 , ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
2513 , ''FAC_BR'', ''PMT_NET'', ''PREPAY''
2514 , ''REFUND'', ''REM_BR'', ''UNAPP'')
2515 AND crh.batch_id = bat.batch_id(+)
2516 AND bat.batch_source_id = bs.batch_source_id(+)
2517 AND bat.org_id = bs.org_id(+)
2518 AND cr.pay_from_customer = cust.cust_account_id(+)
2519 AND cust.party_id = party.party_id(+)
2520 AND cr.cash_receipt_id = ra.cash_receipt_id
2521 AND cr.receipt_method_id = arm.receipt_method_id
2522 AND ard.line_id = lk.source_distribution_id_num_1
2523 AND crh.cash_receipt_id = cr.cash_receipt_id
2524 AND lk.ae_header_id = ae.ae_header_id
2525 AND lk.ae_line_num = ae.ae_line_num
2526 AND lk.ae_header_id = hd.ae_header_id
2527 AND ra.set_of_books_id = hd.ledger_id
2528 AND ps.payment_schedule_id = ra.applied_payment_schedule_id
2529 AND gc.code_combination_id = ard.code_combination_id
2530 AND ard.source_id = ra.receivable_application_id
2531 AND ((ra.amount_applied_from IS NULL
2532 and l_cat.lookup_code = (''TRADE_''||decode (ae.accounting_class_code, ''UNAPP''
2533 , ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
2534 , decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
2535 , ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type)))
2536 or( ra.amount_applied_from IS NOT NULL
2537 and l_cat.lookup_code = (''CCURR_''||decode (ae.accounting_class_code, ''UNAPP''
2538 , ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
2539 , decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
2540 , ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type)))
2541 or(ps.class =''BR'' and l_cat.lookup_code = (''BR_''||decode (ae.accounting_class_code, ''UNAPP''
2542 , ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
2543 , decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
2544 , ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type))))
2545 AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
2546 AND lk.application_id = 222
2547 AND lk.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
2548 AND ae.application_id = 222
2549 AND nvl(ra.confirmed_flag,''Y'') = ''Y''
2550 AND ra.status = ''APP''
2551 AND crh.first_posted_record_flag = ''Y''
2552 AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
2553 AND ard.source_table = ''RA''
2554 AND hd.event_type_code <> ''MANUAL''
2555 AND ra.event_id IS NOT NULL
2556 AND cr.reversal_date IS NULL
2557 AND nvl(cr.confirmed_flag,''Y'') = ''Y''
2558 '||l_ra_org_where||'
2559 '||l_ard_org_where||'
2560 '||l_ps_org_where||'
2561 '||l_cr_org_where||'
2562 '||l_crh_org_where||'
2563 '||l_bat_org_where||'
2564 '||l_bs_org_where||'
2565 '||l_gl_date_where||'
2566 '||l_co_seg_where ||'
2567 '||l_account_where ||'
2568 '||l_account_seg_where ||'
2569 '||l_source_type_where||'
2570 '||l_posting_status_where||'
2571 UNION ALL
2572 select cr.receipt_number payment_number,
2573 arm.name payment_method,
2574 substrb(party.party_name,1,50) customer_name,
2575 cust.account_number customer_number,
2576 to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
2577 to_char(crh.gl_date,''YYYY-MM-DD'') payment_gl_date,
2578 nvl(ard.amount_dr,0) entered_debit,
2579 nvl(ard.amount_cr,0) entered_credit,
2580 nvl(ard.acctd_amount_dr,0) acctd_debit,
2581 nvl(ard.acctd_amount_cr,0) acctd_credit,
2582 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
2583 cr.currency_code receipt_currency,
2584 ps.trx_number trx_number,
2585 gc.code_combination_id,
2586 bs.name receipt_source,
2587 bat.name batch_name,
2588 l_cat.meaning category,
2589 ard.currency_code currency_code,
2590 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
2591 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
2592 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
2593 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
2594 ar_receipt_methods arm,
2595 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
2596 gl_code_combinations gc,
2597 hz_cust_accounts cust,
2598 hz_parties party,
2599 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
2600 ar_batch_sources bs,
2601 '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
2602 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
2603 ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
2604 ar_lookups l_cat
2605 where ra.status IN (''APP'',''ACC'', ''ACTIVITY'', ''OTHER ACC'')
2606 AND cr.cash_receipt_id = ra.cash_receipt_id
2607 AND cr.receipt_method_id = arm.receipt_method_id
2608 AND crh.cash_receipt_id = cr.cash_receipt_id
2609 AND crh.batch_id = bat.batch_id(+)
2610 AND ps.payment_schedule_id = ra.applied_payment_schedule_id
2611 AND bat.batch_source_id = bs.batch_source_id(+)
2612 AND bat.org_id = bs.org_id(+)
2613 AND gc.code_combination_id = ard.code_combination_id
2614 AND ard.source_id = ra.receivable_application_id
2615 AND cr.pay_from_customer = cust.cust_account_id(+)
2616 AND cust.party_id = party.party_id(+)
2617 AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
2618 AND ((ra.amount_applied_from IS NULL
2619 and l_cat.lookup_code = (''TRADE_''||ard.source_type))
2620 or( ra.amount_applied_from IS NOT NULL
2621 and l_cat.lookup_code = (''CCURR_''||ard.source_type))
2622 or(ps.class =''BR'' and l_cat.lookup_code = (''BR_''||ard.source_type)))
2623 AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
2624 AND ra.posting_control_id <> - 3
2625 AND nvl(ra.confirmed_flag,''Y'') = ''Y''
2626 AND nvl(cr.confirmed_flag,''Y'') = ''Y''
2627 AND crh.first_posted_record_flag = ''Y''
2628 AND ra.event_id IS NULL
2629 AND ard.source_table = ''RA''
2630 AND ra.status = ''APP''
2631 AND cr.reversal_date IS NULL
2632 '||l_ra_org_where||'
2633 '||l_ard_org_where||'
2634 '||l_ps_org_where||'
2635 '||l_cr_org_where||'
2636 '||l_crh_org_where||'
2637 '||l_bat_org_where||'
2638 '||l_bs_org_where||'
2639 '||l_gl_date_where ||'
2640 '||l_co_seg_where ||'
2641 '||l_account_where ||'
2642 '||l_account_seg_where ||'
2643 '||l_source_type_ard_where||'
2644 '||l_posting_status_nul_where||'
2645 UNION ALL
2646 select cr.receipt_number payment_number,
2647 arm.name payment_method,
2648 substrb(party.party_name,1,50) customer_name,
2649 cust.account_number customer_number,
2650 to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
2651 to_char(crh.gl_date,''YYYY-MM-DD'') payment_gl_date,
2652 nvl(ard.amount_dr,0) entered_debit,
2653 nvl(ard.amount_cr,0) entered_credit,
2654 nvl(ard.acctd_amount_dr,0) acctd_debit,
2655 nvl(ard.acctd_amount_cr,0) acctd_credit,
2656 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
2657 cr.currency_code receipt_currency,
2658 ps.trx_number trx_number,
2659 gc.code_combination_id,
2660 bs.name receipt_source,
2661 bat.name batch_name,
2662 l_cat.meaning category,
2663 ard.currency_code currency_code,
2664 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
2665 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
2666 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
2667 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
2668 ar_receipt_methods arm,
2669 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
2670 gl_code_combinations gc,
2671 hz_cust_accounts cust,
2672 hz_parties party,
2673 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
2674 ar_batch_sources bs,
2675 '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
2676 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
2677 ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
2678 ar_lookups l_cat
2679 where ra.status IN (''UNAPP'', ''UNID'')
2680 AND cr.cash_receipt_id = ra.cash_receipt_id
2681 AND cr.receipt_method_id = arm.receipt_method_id
2682 AND crh.cash_receipt_id = cr.cash_receipt_id
2683 AND crh.batch_id = bat.batch_id(+)
2684 AND ps.payment_schedule_id = ra.applied_payment_schedule_id
2685 AND bat.batch_source_id = bs.batch_source_id(+)
2686 AND bat.org_id = bs.org_id(+)
2687 AND gc.code_combination_id = ard.code_combination_id
2688 AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
2689 AND ard.source_id = ra.receivable_application_id
2690 AND cr.pay_from_customer = cust.cust_account_id(+)
2691 AND cust.party_id = party.party_id(+)
2692 AND ((ra.amount_applied_from IS NULL
2693 and l_cat.lookup_code = (''TRADE_''||ard.source_type))
2694 or( ra.amount_applied_from IS NOT NULL
2695 and l_cat.lookup_code = (''CCURR_''||ard.source_type))
2696 or(ps.class =''BR'' and l_cat.lookup_code = (''BR_''||ard.source_type)))
2697 AND EXISTS
2698 (SELECT NULL
2699 FROM xla_distribution_links lk
2700 WHERE lk.source_distribution_id_num_1 = ard.line_id
2701 AND lk.application_id = 222
2702 AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'')
2703 AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
2704 AND nvl(ra.confirmed_flag,''Y'') = ''Y''
2705 AND cr.reversal_date IS NULL
2706 AND nvl(cr.confirmed_flag,''Y'') = ''Y''
2707 AND crh.first_posted_record_flag = ''Y''
2708 AND ra.status = ''APP''
2709 AND ra.event_id IS NULL
2710 AND ra.posting_control_id <> - 3
2711 AND ard.source_table = ''RA''
2712 '||l_ra_org_where||'
2713 '||l_ard_org_where||'
2714 '||l_ps_org_where||'
2715 '||l_cr_org_where||'
2716 '||l_crh_org_where||'
2717 '||l_bat_org_where||'
2718 '||l_bs_org_where||'
2719 '||l_gl_date_where ||'
2720 '||l_co_seg_where ||'
2721 '||l_account_where ||'
2722 '||l_account_seg_where ||'
2723 '||l_source_type_ard_where||'
2724 '||l_posting_status_nul_where;
2725
2726 l_xml_build(NVL(l_xml_build.LAST, 0)+ 1) := l_xml_build_sql;
2727 fnd_file.put_line(FND_FILE.LOG, l_xml_build_sql);
2728 l_xml_query := to_clob(l_xml_build_sql);
2729
2730 l_xml_build_sql :='UNION ALL
2731 select cr.receipt_number payment_number,
2732 arm.name payment_method,
2733 substrb(party.party_name,1,50) customer_name,
2734 cust.account_number customer_number,
2735 to_char(hd.accounting_date,''YYYY-MM-DD'') app_gl_date,
2736 to_char(crh.gl_date,''YYYY-MM-DD'') payment_gl_date,
2737 nvl(lk.unrounded_entered_dr,0) entered_debit,
2738 nvl(lk.unrounded_entered_cr,0) entered_credit,
2739 nvl(lk.unrounded_accounted_dr,0) acctd_debit,
2740 nvl(lk.unrounded_accounted_cr,0) acctd_credit,
2741 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
2742 cr.currency_code receipt_currency,
2743 ps.trx_number trx_number,
2744 gc.code_combination_id,
2745 bs.name receipt_source,
2746 bat.name batch_name,
2747 l_cat.meaning category,
2748 ard.currency_code currency_code,
2749 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
2750 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
2751 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
2752 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
2753 ar_receipt_methods arm,
2754 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
2755 gl_code_combinations gc,
2756 hz_cust_accounts cust,
2757 hz_parties party,
2758 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
2759 ar_batch_sources bs,
2760 '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
2761 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
2762 ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
2763 xla_distribution_links lk,
2764 xla_ae_lines ae,
2765 xla_ae_headers hd,
2766 ar_lookups l_cat
2767 where ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
2768 , ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
2769 , ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
2770 , ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
2771 , ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
2772 , ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
2773 , ''GAIN'', ''LOSS'', ''UNID''
2774 , ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
2775 , ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
2776 , ''FAC_BR'', ''PMT_NET'', ''PREPAY''
2777 , ''REFUND'', ''REM_BR'', ''UNAPP'')
2778 AND cr.cash_receipt_id = ra.cash_receipt_id
2779 AND cr.receipt_method_id = arm.receipt_method_id
2780 AND crh.cash_receipt_id = cr.cash_receipt_id
2781 AND crh.batch_id = bat.batch_id(+)
2782 AND ps.payment_schedule_id = ra.applied_payment_schedule_id
2783 AND bat.batch_source_id = bs.batch_source_id(+)
2784 AND bat.org_id = bs.org_id(+)
2785 AND gc.code_combination_id = ard.code_combination_id
2786 AND ard.source_id = ra.receivable_application_id
2787 AND cr.pay_from_customer = cust.cust_account_id(+)
2788 AND cust.party_id = party.party_id(+)
2789 AND ((ra.amount_applied_from IS NULL
2790 and l_cat.lookup_code = (''TRADE_''||decode (ae.accounting_class_code, ''UNAPP''
2791 , ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
2792 , decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
2793 , ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type)))
2794 or( ra.amount_applied_from IS NOT NULL
2795 and l_cat.lookup_code = (''CCURR_''||decode (ae.accounting_class_code,''UNAPP''
2796 , ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
2797 , decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
2798 , ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type)))
2799 or(ps.class =''BR'' and l_cat.lookup_code = (''BR_''||decode (ae.accounting_class_code, ''UNAPP''
2800 , ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
2801 , decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
2802 , ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type))))
2803 AND ard.line_id = lk.source_distribution_id_num_1
2804 AND lk.ae_header_id = ae.ae_header_id
2805 AND lk.ae_line_num = ae.ae_line_num
2806 AND lk.ae_header_id = hd.ae_header_id
2807 AND ra.set_of_books_id = hd.ledger_id
2808 AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
2809 AND hd.event_type_code <> ''MANUAL''
2810 AND lk.application_id = 222
2811 AND ra.event_id IS NOT NULL
2812 AND lk.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
2813 AND ae.application_id = 222
2814 AND nvl(ra.confirmed_flag,''Y'') = ''Y''
2815 AND nvl(cr.confirmed_flag,''Y'') = ''Y''
2816 AND crh.first_posted_record_flag = ''Y''
2817 AND cr.reversal_date IS NOT NULL
2818 AND ra.status = ''APP''
2819 AND ard.source_table = ''RA''
2820 AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
2821 '||l_ra_org_where||'
2822 '||l_ard_org_where||'
2823 '||l_ps_org_where||'
2824 '||l_cr_org_where||'
2825 '||l_crh_org_where||'
2826 '||l_bat_org_where||'
2827 '||l_bs_org_where||'
2828 '||l_gl_date_ard_where ||'
2829 '||l_co_seg_where ||'
2830 '||l_account_where ||'
2831 '||l_account_seg_where ||'
2832 '||l_source_type_where||'
2833 '||l_posting_status_where||'
2834 UNION ALL
2835 select cr.receipt_number payment_number,
2836 arm.name payment_method,
2837 substrb(party.party_name,1,50) customer_name,
2838 cust.account_number customer_number,
2839 to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
2840 to_char(crh.gl_date,''YYYY-MM-DD'') payment_gl_date,
2841 nvl(ard.amount_dr,0) entered_debit,
2842 nvl(ard.amount_cr,0) entered_credit,
2843 nvl(ard.acctd_amount_dr,0) acctd_debit,
2844 nvl(ard.acctd_amount_cr,0) acctd_credit,
2845 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
2846 cr.currency_code receipt_currency,
2847 ps.trx_number trx_number,
2848 gc.code_combination_id,
2849 bs.name receipt_source,
2850 bat.name batch_name,
2851 l_cat.meaning category,
2852 ard.currency_code currency_code,
2853 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
2854 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
2855 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
2856 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
2857 ar_receipt_methods arm,
2858 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
2859 gl_code_combinations gc,
2860 hz_cust_accounts cust,
2861 hz_parties party,
2862 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
2863 ar_batch_sources bs,
2864 '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
2865 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
2866 ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
2867 ar_lookups l_cat
2868 where ra.status IN (''APP'', ''ACC'', ''ACTIVITY'', ''OTHER ACC'')
2869 AND cr.cash_receipt_id = ra.cash_receipt_id
2870 AND cr.receipt_method_id = arm.receipt_method_id
2871 AND crh.cash_receipt_id = cr.cash_receipt_id
2872 AND crh.batch_id = bat.batch_id(+)
2873 AND ps.payment_schedule_id = ra.applied_payment_schedule_id
2874 AND bat.batch_source_id = bs.batch_source_id(+)
2875 AND bat.org_id = bs.org_id(+)
2876 AND gc.code_combination_id = ard.code_combination_id
2877 AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
2878 AND ard.source_id = ra.receivable_application_id
2879 AND cr.pay_from_customer = cust.cust_account_id(+)
2880 AND cust.party_id = party.party_id(+)
2881 AND ((ra.amount_applied_from IS NULL
2882 and l_cat.lookup_code = (''TRADE_''||ard.source_type))
2883 or( ra.amount_applied_from IS NOT NULL
2884 and l_cat.lookup_code = (''CCURR_''||ard.source_type))
2885 or(ps.class =''BR'' and l_cat.lookup_code = (''BR_''||ard.source_type)))
2886 AND ra.posting_control_id <> - 3
2887 AND nvl(ra.confirmed_flag,''Y'') = ''Y''
2888 AND crh.first_posted_record_flag = ''Y''
2889 AND nvl(cr.confirmed_flag,''Y'') = ''Y''
2890 AND ard.source_table = ''RA''
2891 AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
2892 AND ra.status = ''APP''
2893 AND cr.reversal_date IS NOT NULL
2894 AND ra.event_id IS NULL
2895 '||l_ra_org_where||'
2896 '||l_ard_org_where||'
2897 '||l_ps_org_where||'
2898 '||l_cr_org_where||'
2899 '||l_crh_org_where||'
2900 '||l_bat_org_where||'
2901 '||l_bs_org_where||'
2902 '||l_gl_date_where ||'
2903 '||l_co_seg_where ||'
2904 '||l_account_where ||'
2905 '||l_account_seg_where ||'
2906 '||l_source_type_ard_where||'
2907 '||l_posting_status_nul_where||'
2908 UNION ALL
2909 select cr.receipt_number payment_number,
2910 arm.name payment_method,
2911 substrb(party.party_name,1,50) customer_name,
2912 cust.account_number customer_number,
2913 to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
2914 to_char(crh.gl_date,''YYYY-MM-DD'') payment_gl_date,
2915 nvl(ard.amount_dr,0) entered_debit,
2916 nvl(ard.amount_cr,0) entered_credit,
2917 nvl(ard.acctd_amount_dr,0) acctd_debit,
2918 nvl(ard.acctd_amount_cr,0) acctd_credit,
2919 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
2920 cr.currency_code receipt_currency,
2921 ps.trx_number trx_number,
2922 gc.code_combination_id,
2923 bs.name receipt_source,
2924 bat.name batch_name,
2925 l_cat.meaning category,
2926 ard.currency_code currency_code,
2927 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
2928 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
2929 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
2930 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
2931 ar_receipt_methods arm,
2932 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
2933 gl_code_combinations gc,
2934 hz_cust_accounts cust,
2935 hz_parties party,
2936 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
2937 ar_batch_sources bs,
2938 '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
2939 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
2940 ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
2941 ar_lookups l_cat
2942 where ra.status IN (''UNAPP'', ''UNID'')
2943 AND cr.cash_receipt_id = ra.cash_receipt_id
2944 AND cr.receipt_method_id = arm.receipt_method_id
2945 AND crh.cash_receipt_id = cr.cash_receipt_id
2946 AND crh.batch_id = bat.batch_id(+)
2947 AND ps.payment_schedule_id = ra.applied_payment_schedule_id
2948 AND bat.batch_source_id = bs.batch_source_id(+)
2949 AND bat.org_id = bs.org_id(+)
2950 AND gc.code_combination_id = ard.code_combination_id
2951 AND ard.source_id = ra.receivable_application_id
2952 AND cr.pay_from_customer = cust.cust_account_id(+)
2953 AND cust.party_id = party.party_id(+)
2954 AND ((ra.amount_applied_from IS NULL
2955 and l_cat.lookup_code = (''TRADE_''||ard.source_type))
2956 or( ra.amount_applied_from IS NOT NULL
2957 and l_cat.lookup_code = (''CCURR_''||ard.source_type))
2958 or(ps.class =''BR'' and l_cat.lookup_code = (''BR_''||ard.source_type)))
2959 AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
2960 AND ra.posting_control_id <> - 3
2961 AND ra.status = ''APP''
2962 AND cr.reversal_date IS NOT NULL
2963 AND nvl(ra.confirmed_flag,''Y'') = ''Y''
2964 AND nvl(cr.confirmed_flag,''Y'') = ''Y''
2965 AND crh.first_posted_record_flag = ''Y''
2966 AND ard.source_table = ''RA''
2967 AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
2968 AND ra.event_id IS NULL
2969 AND EXISTS
2970 (SELECT NULL
2971 FROM xla_distribution_links lk
2972 WHERE lk.source_distribution_id_num_1 = ard.line_id
2973 AND lk.application_id = 222
2974 AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'')
2975 '||l_ra_org_where||'
2976 '||l_ard_org_where||'
2977 '||l_ps_org_where||'
2978 '||l_cr_org_where||'
2979 '||l_crh_org_where||'
2980 '||l_bat_org_where||'
2981 '||l_bs_org_where||'
2982 '||l_gl_date_where ||'
2983 '||l_co_seg_where ||'
2984 '||l_account_where ||'
2985 '||l_account_seg_where ||'
2986 '||l_source_type_ard_where||'
2987 '||l_posting_status_nul_where;
2988
2989 l_xml_build(NVL(l_xml_build.LAST, 0)+ 1) := l_xml_build_sql;
2990 l_xml_query := l_xml_query||to_clob(l_xml_build_sql);
2991 fnd_file.put_line(FND_FILE.LOG, l_xml_build_sql);
2992
2993 DBMS_UTILITY.DB_VERSION(l_version, l_compatibility);
2994 l_majorVersion := to_number(substr(l_version, 1, instr(l_version,'.')-1));
2995
2996 IF (l_majorVersion > 8 and l_majorVersion < 11) THEN
2997 BEGIN
2998 queryCtx := DBMS_XMLQuery.newContext(l_xml_query);
2999 DBMS_XMLQuery.setRaiseNoRowsException(queryCtx,TRUE);
3000 l_result := DBMS_XMLQuery.getXML(queryCtx);
3001 DBMS_XMLQuery.closeContext(queryCtx);
3002 l_rows_processed := 1;
3003 EXCEPTION WHEN OTHERS THEN
3004 DBMS_XMLQuery.getExceptionContent(queryCtx,l_errNo,l_errMsg);
3005 IF l_errNo = 1403 THEN
3006 l_rows_processed := 0;
3007 END IF;
3008 DBMS_XMLQuery.closeContext(queryCtx);
3009 END;
3010
3011 /*
3012 Bug# 10177944
3013 Starting with 10g DBMS_XMLQUERY package(written java) is deprecated and
3014 it is recommended to use DBMS_XMLGEN package.DBMS_XMLGEN package is similar
3015 to the DBMS_XMLQuery package, except that it is written in C and
3016 compiled into the database kernel.The issue with the DBMS_XMLGEN package is that
3017 it formats the number based on the preferences set.
3018 If the NLS NUMERIC CHARACTERS is set to ",." then the package generates the XML
3019 in such away that all the numbers are formatted using the NLS_NUMERIC_CHARACTERS.
3020 Say, if the number is 12840.5(Raw format) and if NLS_NUMERIC_CHARACTERS is set to ",.",
3021 then the XML generated will have the number as 12.840,50
3022
3023 Issue with Formatting:
3024 This procedure (generate_xml) is to generate the XML for the aging report.
3025 Once the XML got generated, OPP will pickup the XML and transform it into a Report using a template.
3026 While transforming the report, it there exists any field of type number on the template, again
3027 it tries to convert into Number. If the number is not in a raw format then an exception is raised.
3028 As OPP always expects the numbers in raw format,we need to alter the session such that number
3029 gets generated in raw format
3030 */
3031 ELSIF (l_majorVersion >= 11 ) THEN
3032
3033 /*Flag to detrmine if the session has been altered */
3034 l_nls_altered := FALSE;
3035
3036 /*Get the NLS_NUMERIC_CHAR Settings from Session*/
3037 SELECT value INTO l_nls_numeric_char
3038 FROM v$NLS_PARAMETERS
3039 WHERE parameter = 'NLS_NUMERIC_CHARACTERS';
3040
3041 /*Set the NLS_NUMERIC_CHAR Setting*/
3042 IF l_nls_numeric_char <> '.,' THEN
3043 l_alter_str:= 'ALTER SESSION SET nls_numeric_characters=''.,''';
3044 EXECUTE IMMEDIATE l_alter_str;
3045 l_nls_altered := TRUE;
3046 l_alter_str:= NULL;
3047 END IF;
3048 --OPEN l_xml_ref FOR l_xml_query;
3049 l_dyn_block := 'DECLARE TYPE l_xml_ref_cur IS REF CURSOR; l_cursor l_xml_ref_cur; qryCtx DBMS_XMLGEN.ctxHandle; v_cur INTEGER; v_ret NUMBER;
3050 BEGIN v_cur:= DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_cur,:p1,DBMS_SQL.NATIVE); v_ret := DBMS_SQL.EXECUTE(v_cur); l_cursor := DBMS_SQL.TO_REFCURSOR(v_cur);
3051 qryCtx:= DBMS_XMLGEN.newContext(l_cursor); :p2:= DBMS_XMLGEN.getXML(qryCtx,DBMS_XMLGEN.NONE); :p3:= DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
3052 DBMS_XMLGEN.closeContext(qryCtx); CLOSE l_cursor; END;';
3053 execute immediate l_dyn_block using in l_xml_query,out l_result,out l_rows_processed;
3054
3055 END IF;
3056
3057 /* Changes for bug 10177944 */
3058 /*If NLS_NUMERIC_CHAR has been modified, restore the same*/
3059 IF l_nls_altered THEN
3060 l_alter_str := 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='''||l_nls_numeric_Char||'''';
3061 EXECUTE IMMEDIATE l_alter_str;
3062 END IF;
3063
3064 IF l_rows_processed <> 0 THEN
3065 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
3066 tempResult := l_result;
3067 ELSE
3068 l_resultOffset := 0;
3069 END IF;
3070
3071 l_new_line := '
3072 ';
3073
3074 select to_char(sysdate,'YYYY-MM-DD')
3075 into l_report_date
3076 from dual;
3077
3078 /* Bug 4708930
3079 Get the special characters replaced */
3080 l_reporting_entity_name := format_string(l_reporting_entity_name);
3081 l_reporting_level_name := format_string(l_reporting_level_name);
3082 l_organization := format_string(l_organization);
3083 l_receivable_mode_meaning := format_string(l_receivable_mode_meaning);
3084 l_status_meaning := format_string(l_status_meaning);
3085 l_sob_name := format_string(l_sob_name);
3086 l_message := format_string(l_message);
3087 l_message_acct := format_string(l_message_acct);
3088
3089 /* Prepare the tag for the report heading */
3090 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
3091 l_xml_header := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
3092 l_xml_header := l_xml_header ||l_new_line||'<ARAPPJOURNAL>';
3093 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT>'||l_message||'</MSG_TXT>';
3094 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT_ACCT>'||l_message_acct||'</MSG_TXT_ACCT>';
3095 l_xml_header := l_xml_header ||l_new_line||' <PARAMETERS>';
3096 l_xml_header := l_xml_header ||l_new_line||' <REPORT_DATE>'||l_report_date||'</REPORT_DATE>';
3097 l_xml_header := l_xml_header ||l_new_line||' <REPORTING_LEVEL>'||l_reporting_level_name||'</REPORTING_LEVEL>';
3098 l_xml_header := l_xml_header ||l_new_line||' <REPORTING_ENTITY>'||l_reporting_entity_name||'</REPORTING_ENTITY>';
3099 l_xml_header := l_xml_header ||l_new_line||' <SOB_ID>'||p_sob_id||'</SOB_ID>';
3100 l_xml_header := l_xml_header ||l_new_line||' <CO_SEG_LOW>'||p_co_seg_low||'</CO_SEG_LOW>';
3101 l_xml_header := l_xml_header ||l_new_line||' <CO_SEG_HIGH>'||p_co_seg_high||'</CO_SEG_HIGH>';
3102 l_xml_header := l_xml_header ||l_new_line||' <GL_DATE_FROM>'||to_char(fnd_date.canonical_to_date(p_gl_date_from),'YYYY-MM-DD')||'</GL_DATE_FROM>';
3103 l_xml_header := l_xml_header ||l_new_line||' <GL_DATE_TO>'||to_char(fnd_date.canonical_to_date(p_gl_date_to),'YYYY-MM-DD')||'</GL_DATE_TO>';
3104 l_xml_header := l_xml_header ||l_new_line||' <POSTING_STATUS>'||l_status_meaning||'</POSTING_STATUS>';
3105 l_xml_header := l_xml_header ||l_new_line||' <GL_ACCOUNT_LOW>'||p_gl_account_low||'</GL_ACCOUNT_LOW>';
3106 l_xml_header := l_xml_header ||l_new_line||' <GL_ACCOUNT_HIGH>'||p_gl_account_high||'</GL_ACCOUNT_HIGH>';
3107 l_xml_header := l_xml_header ||l_new_line||' <SUMMARY_ACCOUNT>'||p_summary_account||'</SUMMARY_ACCOUNT>';
3108 l_xml_header := l_xml_header ||l_new_line||' <REC_MODE_ONLY>'||l_receivable_mode_meaning||'</REC_MODE_ONLY>';
3109 l_xml_header := l_xml_header ||l_new_line||' <NUM_ROWS>'||l_rows_processed||'</NUM_ROWS>';
3110 l_xml_header := l_xml_header ||l_new_line||' </PARAMETERS>';
3111 l_xml_header := l_xml_header ||l_new_line||' <REPORT_HEADING>';
3112 l_xml_header := l_xml_header ||l_new_line||' <SET_OF_BOOKS>'||l_sob_name||'</SET_OF_BOOKS>';
3113 l_xml_header := l_xml_header ||l_new_line||' <ORGANIZATION>'||l_organization||'</ORGANIZATION>';
3114 l_xml_header := l_xml_header ||l_new_line||' <FUNCTIONAL_CURRENCY>'||l_functional_currency||'</FUNCTIONAL_CURRENCY>';
3115 l_xml_header := l_xml_header ||l_new_line||' </REPORT_HEADING>';
3116
3117 l_close_tag := l_new_line||'</ARAPPJOURNAL>'||l_new_line;
3118 l_xml_header_length := length(l_xml_header);
3119 IF l_rows_processed <> 0 THEN
3120 dbms_lob.write(tempResult,l_xml_header_length,1,l_xml_header);
3121 dbms_lob.copy(tempResult,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
3122 l_xml_header_length,l_resultOffset);
3123 ELSE
3124 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
3125 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
3126 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
3127 END IF;
3128
3129 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
3130
3131 ar_cumulative_balance_report.process_clob(tempResult);
3132 p_result := tempResult;
3133
3134 log('arapp_journal_load_xml (-)');
3135
3136 /* Start changes for bug 10177944 */
3137 EXCEPTION
3138 WHEN OTHERS THEN
3139 /*If NLS_NUMERIC_CHAR has been modified, restore the same*/
3140 IF l_nls_altered THEN
3141 l_alter_str := 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='''||l_nls_numeric_Char||'''';
3142 EXECUTE IMMEDIATE l_alter_str;
3143 END IF;
3144
3145 RAISE;
3146 /* End changes for bug 10177944 */
3147
3148 END arapp_journal_load_xml;
3149
3150 /*========================================================================+
3151 | PUBLIC PROCEDURE ARCM_JOURNAL_LOAD_XML |
3152 | |
3153 | DESCRIPTION |
3154 | |
3155 | This procedure is used to generate the XML data required for reporting|
3156 | ON Account Credit Memo Gain or Loss Journals |
3157 | |
3158 | PSEUDO CODE/LOGIC |
3159 | |
3160 | PARAMETERS |
3161 | |
3162 | |
3163 | KNOWN ISSUES |
3164 | |
3165 | NOTES |
3166 | |
3167 | |
3168 | MODIFICATION HISTORY |
3172 *=======================================================================*/
3169 | Date Author Description of Changes |
3170 | 03-FEB-2004 rkader Created |
3171 | |
3173 PROCEDURE arcm_journal_load_xml (
3174 p_reporting_level IN VARCHAR2,
3175 p_reporting_entity_id IN NUMBER,
3176 p_sob_id IN NUMBER,
3177 p_coa_id IN NUMBER,
3178 p_co_seg_low IN VARCHAR2,
3179 p_co_seg_high IN VARCHAR2,
3180 p_gl_date_from IN VARCHAR2,
3181 p_gl_date_to IN VARCHAR2,
3182 p_posting_status IN VARCHAR2,
3183 p_gl_account_low IN VARCHAR2,
3184 p_gl_account_high IN VARCHAR2,
3185 p_summary_account IN NUMBER,
3186 p_receivable_mode IN VARCHAR2,
3187 p_result OUT NOCOPY CLOB) IS
3188 l_result CLOB;
3189 tempResult CLOB;
3190 l_version varchar2(20);
3191 l_compatibility varchar2(20);
3192 l_suffix varchar2(2);
3193 l_majorVersion number;
3194 l_resultOffset number;
3195 l_xml_header varchar2(3000);
3196 l_xml_header_length number;
3197 l_errNo NUMBER;
3198 l_errMsg VARCHAR2(200);
3199 queryCtx DBMS_XMLquery.ctxType;
3200 qryCtx DBMS_XMLGEN.ctxHandle;
3201 l_xml_query VARCHAR2(32767);
3202 l_natural_segment_col VARCHAR2(50);
3203 l_flex_value_set_id NUMBER;
3204 l_code_combinations VARCHAR2(32767);
3205 TYPE ref_cur IS REF CURSOR;
3206 l_xml_stmt ref_cur;
3207 l_rows_processed NUMBER;
3208 l_new_line VARCHAR2(1) ;
3209 l_coa_id NUMBER; /*bug fix 5654975*/
3210 /* Variables to hold the report heading */
3211 l_sob_id NUMBER;
3212 l_sob_name VARCHAR2(100);
3213 l_functional_currency VARCHAR2(15);
3214 l_organization VARCHAR2(60);
3215 l_format VARCHAR2(40);
3216 l_close_tag VARCHAR2(100);
3217 l_reporting_entity_name VARCHAR2(80);
3218 l_reporting_level_name VARCHAR2(30);
3219 l_status_meaning VARCHAR2(30);
3220 l_receivable_mode_meaning VARCHAR2(10);
3221 /* Variables to hold the where clause based on the input parameters*/
3222 /* Variables length changed from 200 to 500 to address bug:5181586*/
3223 /* Increased variables length to 32767 for bug 5654975 */
3224 l_ard_org_where VARCHAR2(32767);
3225 l_ps_org_where VARCHAR2(32767);
3226 l_ps1_org_where VARCHAR2(32767);
3227 l_ra_org_where VARCHAR2(32767);
3228 l_sysparam_org_where VARCHAR2(32767);
3229 /* Changes to variable length ends*/
3230 l_co_seg_where VARCHAR2(32767);
3231 l_account_where VARCHAR2(32767);
3232 l_account_seg_where VARCHAR2(32767);
3233 l_gl_date_where VARCHAR2(1000);
3234 l_source_type_where VARCHAR2(32767);
3235 l_posting_status_where VARCHAR2(1000);
3236 l_report_date VARCHAR2(25);
3237 l_ld_sp VARCHAR2(1) := 'Y';
3238 l_message VARCHAR2(2000);
3239 l_message_acct VARCHAR2(1000);
3240 l_encoding VARCHAR2(20);
3241 BEGIN
3242
3243 log('arcm_journal_load_xml (+)');
3244
3245 /* Assign the input parameters to the global variables */
3246 arp_recon_rep.var_tname.g_reporting_level := p_reporting_level;
3247 arp_recon_rep.var_tname.g_reporting_entity_id := p_reporting_entity_id;
3248 /* bug 5654975 p_coa_id,p_sob_id is passed incorrectly when the user
3249 has access to multiple Ledgers */
3250 -- arp_recon_rep.var_tname.g_set_of_books_id := p_sob_id;
3251 -- arp_recon_rep.var_tname.g_chart_of_accounts_id := p_coa_id;
3252 arp_recon_rep.var_tname.g_gl_date_from := fnd_date.canonical_to_date(p_gl_date_from);
3253 arp_recon_rep.var_tname.g_gl_date_to := fnd_date.canonical_to_date(p_gl_date_to);
3254 arp_recon_rep.var_tname.g_posting_status := p_posting_status;
3255
3256 /* Added Conditional Implication to address bug:5181586*/
3257 /* Added set of books id and chart of accounts id for bug fix 565497*/
3258 IF p_reporting_level = 1000 THEN
3259 SELECT sob.name sob_name,
3260 sob.set_of_books_id,
3261 sob.currency_code functional_currency,
3262 sob.chart_of_accounts_id
3263 INTO l_sob_name,
3264 l_sob_id,
3265 l_functional_currency,
3266 l_coa_id
3267 FROM gl_sets_of_books sob
3268 WHERE sob.set_of_books_id = arp_recon_rep.var_tname.g_reporting_entity_id;
3269
3270 ELSIF p_reporting_level = 3000 THEN
3271 SELECT sob.name sob_name,
3272 sob.set_of_books_id,
3273 sob.currency_code functional_currency,
3274 sob.chart_of_accounts_id
3275 INTO l_sob_name,
3276 l_sob_id,
3277 l_functional_currency,
3278 l_coa_id
3279 FROM gl_sets_of_books sob,
3280 ar_system_parameters sysparam
3281 WHERE sob.set_of_books_id = sysparam.set_of_books_id
3282 AND sysparam.org_id = arp_recon_rep.var_tname.g_reporting_entity_id;
3283
3284 END IF;
3285 /* Changes for bug:5181586 ends*/
3286 arp_recon_rep.var_tname.g_set_of_books_id := l_sob_id;
3287 arp_recon_rep.var_tname.g_chart_of_accounts_id := l_coa_id;
3288
3289
3290 /* Initialize the reporting context */
3291 init(p_sob_id);
3292
3293 /* Set the org conditions */
3294
3295 XLA_MO_REPORTING_API.Initialize(p_reporting_level, p_reporting_entity_id, 'AUTO');
3296
3297 l_ra_org_where := XLA_MO_REPORTING_API.Get_Predicate('ra',NULL);
3298 l_ard_org_where := XLA_MO_REPORTING_API.Get_Predicate('ard',NULL);
3299 l_ps_org_where := XLA_MO_REPORTING_API.Get_Predicate('ps',NULL);
3300 l_ps1_org_where := XLA_MO_REPORTING_API.Get_Predicate('ps1',NULL);
3301 l_sysparam_org_where := XLA_MO_REPORTING_API.Get_Predicate('sysparam',NULL);
3302
3303
3304 /* Replace the bind variables with global functions */
3305 l_ra_org_where := replace(l_ra_org_where,
3306 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
3307 l_ard_org_where := replace(l_ard_org_where,
3308 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
3309 l_ps_org_where := replace(l_ps_org_where,
3310 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
3311 l_ps1_org_where := replace(l_ps1_org_where,
3312 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
3313 l_sysparam_org_where := replace(l_sysparam_org_where,
3314 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
3315 l_reporting_entity_name := substrb(XLA_MO_REPORTING_API.get_reporting_entity_name,1,80);
3316 l_reporting_level_name := substrb(XLA_MO_REPORTING_API.get_reporting_level_name,1,30);
3317
3318 /* Multi Org Uptake: Show appropriate message to the user depending upon the security profile */
3319 IF p_reporting_level = '1000' THEN
3320 l_ld_sp:= mo_utils.check_ledger_in_sp(p_reporting_entity_id);
3321 END IF;
3322
3323 IF l_ld_sp = 'N' THEN
3324 FND_MESSAGE.SET_NAME('FND','FND_MO_RPT_PARTIAL_LEDGER');
3325 l_message := FND_MESSAGE.get;
3326 END IF;
3327
3328 /* Bug fix 4942083*/
3329 IF arp_util.Open_Period_Exists(p_reporting_level,
3330 p_reporting_entity_id,
3331 arp_recon_rep.var_tname.g_gl_date_from,
3332 arp_recon_rep.var_tname.g_gl_date_to) THEN
3333 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
3334 l_message_acct := FND_MESSAGE.Get;
3335 END IF;
3336
3337 IF p_reporting_level = '3000' THEN
3338 select substrb(hou.name,1,60)
3339 into l_organization
3340 from hr_organization_units hou
3341 where hou.organization_id = arp_recon_rep.var_tname.g_reporting_entity_id;
3342 ELSE
3343 select meaning
3344 into l_organization
3345 from ar_lookups
3346 where lookup_code ='ALL' and lookup_type ='ALL';
3347 END IF;
3348
3349 /* Build the WHERE clauses */
3350 /*Replaced p_coa_id with l_coa_id for bug 5654975 */
3351
3352 IF p_co_seg_low IS NULL AND p_co_seg_high IS NULL THEN
3353 l_co_seg_where := NULL;
3354 ELSIF p_co_seg_low IS NULL THEN
3355 l_co_seg_where := ' AND ' ||
3356 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
3357 p_id_flex_code => 'GL#',
3358 p_id_flex_num => l_coa_id,
3359 p_table_alias => 'GC',
3360 p_mode => 'WHERE',
3361 p_qualifier => 'GL_BALANCING',
3362 p_function => '<=',
3363 p_operand1 => p_co_seg_high);
3364 ELSIF p_co_seg_high IS NULL THEN
3365 l_co_seg_where := ' AND ' ||
3366 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
3367 p_id_flex_code => 'GL#',
3368 p_id_flex_num => l_coa_id,
3369 p_table_alias => 'GC',
3370 p_mode => 'WHERE',
3371 p_qualifier => 'GL_BALANCING',
3372 p_function => '>=',
3373 p_operand1 => p_co_seg_low);
3374 ELSE
3375 l_co_seg_where := ' AND ' ||
3376 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
3377 p_id_flex_code => 'GL#',
3378 p_id_flex_num => l_coa_id,
3379 p_table_alias => 'GC',
3380 p_mode => 'WHERE',
3381 p_qualifier => 'GL_BALANCING',
3382 p_function => 'BETWEEN',
3383 p_operand1 => p_co_seg_low,
3384 p_operand2 => p_co_seg_high);
3385 END IF;
3386
3387 IF p_gl_date_from IS NULL and p_gl_date_to IS NULL THEN
3388 l_gl_date_where := NULL;
3389 ELSIF p_gl_date_from IS NULL THEN
3390 l_gl_date_where :=' and ra.gl_date <= arp_recon_rep.get_gl_date_to()';
3391 ELSIF p_gl_date_to IS NULL THEN
3392 l_gl_date_where :=' and ra.gl_date >= arp_recon_rep.get_gl_date_from() ' ;
3393 ELSE
3394 l_gl_date_where := ' and ra.gl_date between arp_recon_rep.get_gl_date_from() and arp_recon_rep.get_gl_date_to() ';
3395 END IF;
3396
3397 IF p_gl_account_low IS NOT NULL AND p_gl_account_high IS NOT NULL THEN
3398 l_account_where := ' AND ' || AR_CALC_AGING.FLEX_SQL(
3399 p_application_id=> 101,
3400 p_id_flex_code =>'GL#',
3401 p_id_flex_num =>l_coa_id,
3402 p_table_alias => 'gc',
3403 p_mode => 'WHERE',
3404 p_qualifier => 'ALL',
3405 p_function=> 'BETWEEN',
3406 p_operand1 => p_gl_account_low,
3407 p_operand2 => p_gl_account_high);
3408 ELSE
3409 l_account_where := NULL;
3410 END IF;
3411
3412 IF p_summary_account IS NOT NULL THEN
3413 SELECT fcav.application_column_name, flex_value_set_id
3414 INTO l_natural_segment_col , l_flex_value_set_id
3415 FROM fnd_segment_attribute_values fcav,
3416 fnd_id_flex_segments fifs
3417 WHERE fcav.application_id = 101
3418 AND fcav.id_flex_code = 'GL#'
3419 AND fcav.id_flex_num = arp_recon_rep.var_tname.g_chart_of_accounts_id
3420 AND fcav.attribute_value = 'Y'
3421 AND fcav.segment_attribute_type = 'GL_ACCOUNT'
3422 AND fifs.application_id = fcav.application_id
3423 AND fifs.id_flex_code = fcav.id_flex_code
3424 AND fifs.id_flex_num = fcav.id_flex_num
3425 AND fcav.application_column_name = fifs.application_column_name;
3426
3427 get_detail_accounts(l_flex_value_set_id, p_summary_account, l_code_combinations);
3428
3429 l_account_seg_where := ' and gc.'||l_natural_segment_col||' in ('||l_code_combinations||' )';
3430 ELSE
3431 l_account_seg_where := NULL;
3432 END IF;
3433
3434 IF nvl(p_receivable_mode,'N') = 'Y' THEN
3438 from fnd_lookups
3435 l_source_type_where := ' and ard.source_type in(''EXCH_GAIN'', ''EXCH_LOSS'')';
3436 select meaning
3437 into l_receivable_mode_meaning
3439 where lookup_type = 'YES_NO'
3440 and lookup_code = 'Y';
3441 ELSE
3442 l_source_type_where := NULL;
3443 select meaning
3444 into l_receivable_mode_meaning
3445 from fnd_lookups
3446 where lookup_type = 'YES_NO'
3447 and lookup_code = 'N';
3448 END IF;
3449
3450 IF p_posting_status IS NOT NULL THEN
3451 select meaning
3452 into l_status_meaning
3453 from ar_lookups
3454 where lookup_type = 'POSTED_STATUS'
3455 and lookup_code = arp_recon_rep.var_tname.g_posting_status;
3456
3457 l_posting_status_where := 'and nvl(ra.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')) =
3458 decode(arp_recon_rep.get_posting_status(),
3459 ''POSTED'',ra.gl_posted_date,
3460 ''UNPOSTED'',TO_DATE(''01/01/0001'',''MM/DD/YYYY''),
3461 nvl(ra.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')))';
3462 ELSE
3463 l_status_meaning := NULL;
3464 l_posting_status_where := NULL;
3465 END IF;
3466
3467 l_xml_query := '
3468 select substrb(party.party_name,1,50) customer_name,
3469 cust.account_number customer_number,
3470 ps.trx_number cm_number,
3471 ps1.trx_number trx_number,
3472 nvl(ard.amount_dr,0) entered_debit,
3473 nvl(ard.amount_cr,0) entered_credit,
3474 nvl(ard.acctd_amount_dr,0) acctd_debit,
3475 nvl(ard.acctd_amount_cr,0) acctd_credit,
3476 to_char(ps.trx_date,''YYYY-MM-DD'') cm_date,
3477 to_char(ps1.trx_date,''YYYY-MM-DD'') trx_date,
3478 to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
3479 to_char(ps.gl_date,''YYYY-MM-DD'') cm_gl_date,
3480 to_char(ps1.gl_date,''YYYY-MM-DD'') trx_gl_date,
3481 ps.invoice_currency_code cm_currency_code,
3482 ps1.invoice_currency_code trx_currency_code,
3483 to_char(ps.exchange_date,''YYYY-MM-DD'') cm_exchange_date,
3484 to_char(ps1.exchange_date,''YYYY-MM-DD'') trx_exchange_date,
3485 ps.exchange_rate cm_exchange_rate,
3486 ps1.exchange_rate trx_exchange_rate,
3487 l_cat.meaning category,
3488 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
3489 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
3490 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
3491 from '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra ,
3492 '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard ,
3493 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps ,
3494 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps1 ,
3495 gl_code_combinations gc,
3496 hz_cust_accounts cust,
3497 hz_parties party,
3498 ar_lookups l_cat
3499 where nvl(ra.confirmed_flag,''Y'') = ''Y''
3500 and ra.application_type = ''CM''
3501 and ra.status = ''APP''
3502 and ard.source_table = ''RA''
3503 and ard.source_id = ra.receivable_application_id
3504 and ra.payment_schedule_id = ps.payment_schedule_id
3505 and ra.applied_payment_schedule_id = ps1.payment_schedule_id
3506 and cust.cust_account_id = ps.customer_id
3507 and cust.party_id = party.party_id
3508 and gc.code_combination_id = ard.code_combination_id
3509 and gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
3510 and l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
3511 and l_cat.lookup_code = (''CMAPP_''||ard.source_type)
3512 '||l_ard_org_where||'
3513 '||l_ra_org_where||'
3514 '||l_ps_org_where||'
3515 '||l_ps1_org_where||'
3516 '||l_gl_date_where ||'
3517 '||l_co_seg_where ||'
3518 '||l_account_where ||'
3519 '||l_account_seg_where ||'
3520 '||l_source_type_where||'
3521 '||l_posting_status_where||'
3522 order by company, category, account,app_gl_date, cm_number' ;
3523
3524 DBMS_UTILITY.DB_VERSION(l_version, l_compatibility);
3525 l_majorVersion := to_number(substr(l_version, 1, instr(l_version,'.')-1));
3526
3527 IF (l_majorVersion > 8 and l_majorVersion < 9) THEN
3528 BEGIN
3529 queryCtx := DBMS_XMLQuery.newContext(l_xml_query);
3530 DBMS_XMLQuery.setRaiseNoRowsException(queryCtx,TRUE);
3531 l_result := DBMS_XMLQuery.getXML(queryCtx);
3532 DBMS_XMLQuery.closeContext(queryCtx);
3533 l_rows_processed := 1;
3534 EXCEPTION WHEN OTHERS THEN
3535 DBMS_XMLQuery.getExceptionContent(queryCtx,l_errNo,l_errMsg);
3536 IF l_errNo = 1403 THEN
3537 l_rows_processed := 0;
3538 END IF;
3539 DBMS_XMLQuery.closeContext(queryCtx);
3540 END;
3541 ELSIF (l_majorVersion >= 9 ) THEN
3542 qryCtx := DBMS_XMLGEN.newContext(l_xml_query);
3543 l_result := DBMS_XMLGEN.getXML(qryCtx,DBMS_XMLGEN.NONE);
3544 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
3545 DBMS_XMLGEN.closeContext(qryCtx);
3546 END IF;
3547 IF l_rows_processed <> 0 THEN
3548 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
3549 tempResult := l_result;
3550 ELSE
3551 l_resultOffset := 0;
3552 END IF;
3553
3554 l_new_line := '
3555 ';
3556
3557 select to_char(sysdate,'YYYY-MM-DD')
3558 into l_report_date
3559 from dual;
3560
3561 /* Bug 4708930
3562 Get the special characters replaced */
3563 l_reporting_entity_name := format_string(l_reporting_entity_name);
3564 l_reporting_level_name := format_string(l_reporting_level_name);
3565 l_organization := format_string(l_organization);
3566 l_receivable_mode_meaning := format_string(l_receivable_mode_meaning);
3567 l_status_meaning := format_string(l_status_meaning);
3568 l_sob_name := format_string(l_sob_name);
3569 l_message := format_string(l_message);
3570 l_message_acct := format_string(l_message_acct);
3571
3572 /* Prepare the tag for the report heading */
3573 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
3574 l_xml_header := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
3575 l_xml_header := l_xml_header ||l_new_line||'<ARCMJOURNAL>';
3576 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT>'||l_message||'</MSG_TXT>';
3577 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT_ACCT>'||l_message_acct||'</MSG_TXT_ACCT>';
3578 l_xml_header := l_xml_header ||l_new_line||' <PARAMETERS>';
3579 l_xml_header := l_xml_header ||l_new_line||' <REPORT_DATE>'||l_report_date||'</REPORT_DATE>';
3580 l_xml_header := l_xml_header ||l_new_line||' <REPORTING_LEVEL>'||l_reporting_level_name||'</REPORTING_LEVEL>';
3584 l_xml_header := l_xml_header ||l_new_line||' <CO_SEG_HIGH>'||p_co_seg_high||'</CO_SEG_HIGH>';
3581 l_xml_header := l_xml_header ||l_new_line||' <REPORTING_ENTITY>'||l_reporting_entity_name||'</REPORTING_ENTITY>';
3582 l_xml_header := l_xml_header ||l_new_line||' <SOB_ID>'||p_sob_id||'</SOB_ID>';
3583 l_xml_header := l_xml_header ||l_new_line||' <CO_SEG_LOW>'||p_co_seg_low||'</CO_SEG_LOW>';
3585 l_xml_header := l_xml_header ||l_new_line||' <GL_DATE_FROM>'||to_char(fnd_date.canonical_to_date(p_gl_date_from),'YYYY-MM-DD')||'</GL_DATE_FROM>';
3586 l_xml_header := l_xml_header ||l_new_line||' <GL_DATE_TO>'||to_char(fnd_date.canonical_to_date(p_gl_date_to),'YYYY-MM-DD')||'</GL_DATE_TO>';
3587 l_xml_header := l_xml_header ||l_new_line||' <POSTING_STATUS>'||l_status_meaning||'</POSTING_STATUS>';
3588 l_xml_header := l_xml_header ||l_new_line||' <GL_ACCOUNT_LOW>'||p_gl_account_low||'</GL_ACCOUNT_LOW>';
3589 l_xml_header := l_xml_header ||l_new_line||' <GL_ACCOUNT_HIGH>'||p_gl_account_high||'</GL_ACCOUNT_HIGH>';
3590 l_xml_header := l_xml_header ||l_new_line||' <SUMMARY_ACCOUNT>'||p_summary_account||'</SUMMARY_ACCOUNT>';
3591 l_xml_header := l_xml_header ||l_new_line||' <REC_MODE_ONLY>'||l_receivable_mode_meaning||'</REC_MODE_ONLY>';
3592 l_xml_header := l_xml_header ||l_new_line||' <NUM_ROWS>'||l_rows_processed||'</NUM_ROWS>';
3593 l_xml_header := l_xml_header ||l_new_line||' </PARAMETERS>';
3594 l_xml_header := l_xml_header ||l_new_line||' <REPORT_HEADING>';
3595 l_xml_header := l_xml_header ||l_new_line||' <SET_OF_BOOKS>'||l_sob_name||'</SET_OF_BOOKS>';
3596 l_xml_header := l_xml_header ||l_new_line||' <ORGANIZATION>'||l_organization||'</ORGANIZATION>';
3597 l_xml_header := l_xml_header ||l_new_line||' <FUNCTIONAL_CURRENCY>'||l_functional_currency||'</FUNCTIONAL_CURRENCY>';
3598 l_xml_header := l_xml_header ||l_new_line||' </REPORT_HEADING>';
3599
3600 l_close_tag := l_new_line||'</ARCMJOURNAL>'||l_new_line;
3601 l_xml_header_length := length(l_xml_header);
3602 IF l_rows_processed <> 0 THEN
3603 dbms_lob.write(tempResult,l_xml_header_length,1,l_xml_header);
3604 dbms_lob.copy(tempResult,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
3605 l_xml_header_length,l_resultOffset);
3606 ELSE
3607 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
3608 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
3609 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
3610 END IF;
3611
3612 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
3613
3614 ar_cumulative_balance_report.process_clob(tempResult);
3615 p_result := tempResult;
3616
3617 log('arcm_journal_load_xml (-)');
3618 END arcm_journal_load_xml;
3619
3620
3621 /*========================================================================+
3622 | PUBLIC PROCEDURE ARGLRECON_LOAD_XML |
3623 | |
3624 | DESCRIPTION |
3628 | |
3625 | |
3626 | This procedure is used to generate the XML data required for |
3627 | creating the AR to GL Reconciliation Report |
3629 | PSEUDO CODE/LOGIC |
3630 | |
3631 | PARAMETERS |
3632 | |
3633 | |
3634 | KNOWN ISSUES |
3635 | |
3636 | NOTES |
3637 | |
3638 | |
3639 | MODIFICATION HISTORY |
3640 | Date Author Description of Changes |
3641 | 03-FEB-2004 rkader Created |
3642 | |
3643 *=======================================================================*/
3644
3645 PROCEDURE arglrecon_load_xml(
3646 p_reporting_level IN VARCHAR2,
3647 p_reporting_entity_id IN NUMBER,
3648 p_sob_id IN NUMBER,
3649 p_coa_id IN NUMBER,
3650 p_out_of_balance_only IN VARCHAR2,
3651 p_co_seg_low IN VARCHAR2,
3652 p_co_seg_high IN VARCHAR2,
3653 p_period_name IN VARCHAR2,
3654 p_gl_account_low IN VARCHAR2,
3655 p_gl_account_high IN VARCHAR2,
3656 p_summary_account IN VARCHAR2,
3657 p_result OUT NOCOPY CLOB) IS
3658
3659 l_gl_date_from date;
3660 l_gl_date_to date;
3661 l_result CLOB;
3662 tempResult CLOB;
3663 l_version varchar2(20);
3664 l_compatibility varchar2(20);
3665 l_suffix varchar2(2);
3666 l_majorVersion number;
3667 l_resultOffset number;
3668 l_rows_processed number;
3669 l_xml_header varchar2(3000);
3670 l_xml_header_length number;
3671 queryCtx DBMS_XMLquery.ctxType;
3672 qryCtx DBMS_XMLGEN.ctxHandle;
3673 l_xml_query VARCHAR2(32767);
3674 l_natural_segment_col VARCHAR2(50);
3675 l_flex_value_set_id NUMBER;
3676 l_code_combinations VARCHAR2(32767);
3677 l_new_line VARCHAR2(1);
3678 /* Variables to hold the report heading */
3679 l_sob_id NUMBER;
3680 l_sob_name VARCHAR2(100);
3681 l_functional_currency VARCHAR2(15);
3682 l_organization VARCHAR2(60);
3683 l_format VARCHAR2(40);
3684 l_close_tag VARCHAR2(100);
3685 l_reporting_entity_name VARCHAR2(80);
3686 l_reporting_level_name VARCHAR2(30);
3687 l_errNo NUMBER;
3688 l_errMsg VARCHAR2(200);
3689 /* Variables to hold the where clause based on the input parameters*/
3690 /* Variables length changed from 200 to 500 to address bug:5181586*/
3691 /* Increased variables length to 32767 for bug 5654975 */
3692 l_ra_org_where VARCHAR2(32767);
3693 l_crh_org_where VARCHAR2(32767);
3694 l_cr_org_where VARCHAR2(32767);/* Bug fix 6432847 */
3695 l_gl_dist_org_where VARCHAR2(32767);
3696 l_mcd_org_where VARCHAR2(32767);
3697 l_ard_org_where VARCHAR2(32767);
3698 l_adj_org_where VARCHAR2(32767);
3699 l_ath_org_where VARCHAR2(32767);
3700 l_sysparam_org_where VARCHAR2(32767);
3701 /* Changes to variable length ends*/
3702 l_co_seg_where VARCHAR2(32767);
3703 l_account_where VARCHAR2(32767);
3704 l_account_seg_where VARCHAR2(32767);
3705 l_report_date VARCHAR2(25);
3706 l_encoding VARCHAR2(20);
3707 l_message_acct VARCHAR2(1000);
3708
3709 l_sel_seg VARCHAR2(2000);
3710 l_sel_co_seg VARCHAR2(1000);--Changes from 100 to 1000 for bug 10177944
3711
3712 /* Changes for bug 10177944 */
3713 l_nls_numeric_char VARCHAR2(5);
3714 l_nls_altered BOOLEAN;
3715 l_alter_str VARCHAR2(100);
3716
3717 l_temp_stmt VARCHAR2(32767);
3718 l_insert_stmt DBMS_SQL.VARCHAR2A;
3719 l_cursor_id NUMBER;
3720 ret INTEGER;
3721 l_increment NUMBER := 0;
3722 BEGIN
3723
3724 log('arglrecon_load_xml(+)');
3725
3726 /* Assign the input parameters to the global variables */
3727 /* AR to GL Reconciliation Report can be run only for the Set of Books
3728 So hard coding the reporting_level and context */
3729
3730 arp_recon_rep.var_tname.g_reporting_level := 1000;
3731 arp_recon_rep.var_tname.g_reporting_entity_id := p_reporting_entity_id;
3732 arp_recon_rep.var_tname.g_set_of_books_id := p_reporting_entity_id;
3733 arp_recon_rep.var_tname.g_chart_of_accounts_id := p_coa_id;
3734 arp_recon_rep.var_tname.g_period_name := p_period_name;
3735 arp_recon_rep.var_tname.g_out_of_balance_only := p_out_of_balance_only;
3736
3737 /* Initialize the reporting context */
3738 init(p_sob_id);
3739
3743
3740 /* Set the org conditions */
3741
3742 XLA_MO_REPORTING_API.Initialize(1000,p_reporting_entity_id, 'AUTO');
3744 l_ra_org_where := XLA_MO_REPORTING_API.Get_Predicate('ra',NULL);
3745 l_adj_org_where := XLA_MO_REPORTING_API.Get_Predicate('adj',NULL);
3746 l_ard_org_where := XLA_MO_REPORTING_API.Get_Predicate('ard',NULL);
3747 l_gl_dist_org_where := XLA_MO_REPORTING_API.Get_Predicate('ctlgd',NULL);
3748 l_sysparam_org_where := XLA_MO_REPORTING_API.Get_Predicate('sysparam',NULL);
3749 l_mcd_org_where := XLA_MO_REPORTING_API.Get_Predicate('mcd',NULL);
3750 l_crh_org_where := XLA_MO_REPORTING_API.Get_Predicate('crh',NULL);
3751 l_cr_org_where := XLA_MO_REPORTING_API.Get_Predicate('cr',NULL); /* Bug fix 6432847 */
3752 l_ath_org_where := XLA_MO_REPORTING_API.Get_Predicate('ard',NULL);
3753
3754 /* Replace the bind variables with global functions */
3755 l_ra_org_where := replace(l_ra_org_where,
3756 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
3757 l_adj_org_where := replace(l_adj_org_where,
3758 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
3759 l_ard_org_where := replace(l_ard_org_where,
3760 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
3761 l_gl_dist_org_where := replace(l_gl_dist_org_where,
3762 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
3763 l_sysparam_org_where := replace(l_sysparam_org_where,
3764 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
3765 l_mcd_org_where := replace(l_mcd_org_where,
3766 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
3767 l_crh_org_where := replace(l_crh_org_where,
3768 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
3769 /* Bug fix 6432847 */
3770 l_cr_org_where := replace(l_cr_org_where,
3771 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
3772 l_ath_org_where := replace(l_ath_org_where,
3773 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
3774
3775 l_reporting_entity_name := substrb(XLA_MO_REPORTING_API.get_reporting_entity_name,1,80);
3776 l_reporting_level_name := substrb(XLA_MO_REPORTING_API.get_reporting_level_name,1,30);
3777
3778 /* Get the org name */
3779 select meaning
3780 into l_organization
3781 from ar_lookups
3782 where lookup_code ='ALL' and lookup_type ='ALL';
3783
3784 /* Build the other WHERE clauses */
3785 IF p_co_seg_low IS NULL AND p_co_seg_high IS NULL THEN
3786 l_co_seg_where := NULL;
3787 ELSIF p_co_seg_low IS NULL THEN
3788 l_co_seg_where := ' AND ' ||
3789 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
3790 p_id_flex_code => 'GL#',
3791 p_id_flex_num => p_coa_id,
3792 p_table_alias => 'GC',
3793 p_mode => 'WHERE',
3794 p_qualifier => 'GL_BALANCING',
3795 p_function => '<=',
3796 p_operand1 => p_co_seg_high);
3797 ELSIF p_co_seg_high IS NULL THEN
3798 l_co_seg_where := ' AND ' ||
3799 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
3800 p_id_flex_code => 'GL#',
3801 p_id_flex_num => p_coa_id,
3802 p_table_alias => 'GC',
3803 p_mode => 'WHERE',
3804 p_qualifier => 'GL_BALANCING',
3805 p_function => '>=',
3806 p_operand1 => p_co_seg_low);
3807 ELSE
3808 l_co_seg_where := ' AND ' ||
3809 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
3810 p_id_flex_code => 'GL#',
3811 p_id_flex_num => p_coa_id,
3812 p_table_alias => 'GC',
3813 p_mode => 'WHERE',
3814 p_qualifier => 'GL_BALANCING',
3815 p_function => 'BETWEEN',
3816 p_operand1 => p_co_seg_low,
3817 p_operand2 => p_co_seg_high);
3818 END IF;
3819
3820 IF p_gl_account_low IS NOT NULL AND p_gl_account_high IS NOT NULL THEN
3821 l_account_where := ' AND ' || AR_CALC_AGING.FLEX_SQL(
3822 p_application_id=> 101,
3823 p_id_flex_code =>'GL#',
3824 p_id_flex_num =>p_coa_id,
3825 p_table_alias => 'gc',
3826 p_mode => 'WHERE',
3827 p_qualifier => 'ALL',
3828 p_function=> 'BETWEEN',
3829 p_operand1 => p_gl_account_low,
3830 p_operand2 => p_gl_account_high);
3831 ELSE
3832 l_account_where := NULL;
3833 END IF;
3834
3835
3836 IF p_summary_account IS NOT NULL THEN
3837 SELECT fcav.application_column_name, flex_value_set_id
3838 INTO l_natural_segment_col , l_flex_value_set_id
3839 FROM fnd_segment_attribute_values fcav,
3840 fnd_id_flex_segments fifs
3841 WHERE fcav.application_id = 101
3842 AND fcav.id_flex_code = 'GL#'
3846 AND fifs.application_id = fcav.application_id
3843 AND fcav.id_flex_num = arp_recon_rep.var_tname.g_chart_of_accounts_id
3844 AND fcav.attribute_value = 'Y'
3845 AND fcav.segment_attribute_type = 'GL_ACCOUNT'
3847 AND fifs.id_flex_code = fcav.id_flex_code
3848 AND fifs.id_flex_num = fcav.id_flex_num
3849 AND fcav.application_column_name = fifs.application_column_name;
3850
3851 get_detail_accounts(l_flex_value_set_id, p_summary_account, l_code_combinations);
3852
3853 l_account_seg_where := ' AND gc.'||l_natural_segment_col||' in ('||l_code_combinations||' )';
3854 ELSE
3855 l_account_seg_where := NULL;
3856 END IF;
3857
3858 /* Get the report Headings */
3859 /* Added Conditional Implication to address bug:5181586*/
3860 IF p_reporting_level = 1000 THEN
3861 SELECT sob.name sob_name,
3862 sob.currency_code functional_currency
3863 INTO l_sob_name,
3864 l_functional_currency
3865 FROM gl_sets_of_books sob
3866 WHERE sob.set_of_books_id = arp_recon_rep.var_tname.g_reporting_entity_id;
3867
3868 ELSIF p_reporting_level = 3000 THEN
3869 SELECT sob.name sob_name,
3870 sob.currency_code functional_currency
3871 INTO l_sob_name,
3872 l_functional_currency
3873 FROM gl_sets_of_books sob,
3874 ar_system_parameters sysparam
3875 WHERE sob.set_of_books_id = sysparam.set_of_books_id
3876 AND sysparam.org_id = arp_recon_rep.var_tname.g_reporting_entity_id;
3877
3878 END IF;
3879 /* Changes for bug:5181586 ends*/
3880
3881 arp_recon_rep.var_tname.g_functional_currency := l_functional_currency;
3882
3883 /* Get the format mask for the function currency */
3884 select fnd_currency.get_format_mask(l_functional_currency,40)
3885 into l_format
3886 from dual;
3887 /* Get the period start and end dates */
3888 SELECT p.start_date, p.end_date
3889 INTO l_gl_date_from , l_gl_date_to
3890 FROM gl_periods p, gl_sets_of_books b
3891 WHERE p.period_set_name = b.period_set_name
3892 AND p.period_type = b.accounted_period_type
3893 AND b.set_of_books_id = arp_recon_rep.var_tname.g_set_of_books_id
3894 AND p.period_name = arp_recon_rep.var_tname.g_period_name;
3895 arp_recon_rep.var_tname.g_gl_date_from := l_gl_date_from;
3896 arp_recon_rep.var_tname.g_gl_date_to := l_gl_date_to;
3897
3898 /* Bug fix 4942083*/
3899 IF arp_util.Open_Period_Exists(p_reporting_level,
3900 p_reporting_entity_id,
3901 arp_recon_rep.var_tname.g_gl_date_from,
3902 arp_recon_rep.var_tname.g_gl_date_to) THEN
3903 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
3904 l_message_acct := FND_MESSAGE.Get;
3905 END IF;
3906
3907 --9671956
3908
3909 l_sel_seg := ar_calc_aging.flex_sql(
3910 101,
3911 'GL#',
3912 p_coa_id,
3913 'gc',
3914 'SELECT',
3915 'ALL');
3916
3917 l_sel_co_seg := ar_calc_aging.flex_sql(
3918 101,
3919 'GL#',
3920 p_coa_id,
3921 'gc',
3922 'SELECT',
3923 'GL_BALANCING');
3924
3925 l_temp_stmt := '
3926 insert into ar_gl_recon_gt(code_combination_id,
3927 receivables_dr,receivables_cr,
3928 account_type, account_type_code,account,company)
3929 (select dat.code_combination_id,
3930 sum(nvl(acctd_amount_dr,0)) receivables_debit,
3931 sum(nvl(acctd_amount_cr,0)) receivables_credit,
3932 lookup.description account_type,
3933 gc.account_type account_type_code, ' || l_sel_seg || ' account, ' || l_sel_co_seg || ' company ' ||
3934 ' from (
3935
3936 -- Bug 6943555
3937
3938 select decode(sign(sum(nvl(b.acctd_amount_dr,0))- sum(nvl(b.acctd_amount_cr,0))),
3939 +1, (sum(nvl(b.acctd_amount_dr,0))- sum(nvl(b.acctd_amount_cr,0))),
3940 0) acctd_amount_dr,
3941 decode(sign(sum(nvl(b.acctd_amount_dr,0))- sum(nvl(b.acctd_amount_cr,0))),
3942 -1, (sum(nvl(b.acctd_amount_cr,0))- sum(nvl(b.acctd_amount_dr,0))),
3943 0) acctd_amount_cr,
3944 b.code_combination_id
3945
3946 from
3947
3948 (select
3949 DECODE(decode (ae.accounting_class_code, ''RECEIVABLE'', ''REC'', ''FV_REC_DR'', ''REC'', ''FV_REC_CR'', ''REC'', ctlgd.account_class), ''REC'',decode(sign(acctd_amount), -1 ,0, acctd_amount),
3950 ''REV'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
3951 ''TAX'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
3952 ''ROUND'',decode(sign(acctd_amount), -1,abs(acctd_amount),0),
3956 DECODE(decode (ae.accounting_class_code, ''RECEIVABLE'', ''REC'', ''FV_REC_DR'' , ''REC'', ''FV_REC_CR''
3953 ''UNEARN'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
3954 ''FREIGHT'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
3955 ''UNBILL'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),0) acctd_amount_dr,
3957 , ''REC'', ctlgd.account_class), ''REC'',decode(sign(acctd_amount), -1 ,abs(acctd_amount),0),
3958 ''REV'',decode(sign(acctd_amount), -1, 0,acctd_amount),
3959 ''TAX'',decode(sign(acctd_amount), -1, 0,acctd_amount),
3960 ''ROUND'',decode(sign(acctd_amount), -1,0,acctd_amount),
3961 ''UNEARN'',decode(sign(acctd_amount), -1, 0,acctd_amount),
3962 ''FREIGHT'',decode(sign(acctd_amount), -1, 0,acctd_amount),
3963 ''UNBILL'',decode(sign(acctd_amount), -1, 0,acctd_amount),0) acctd_amount_cr,
3964 ae.code_combination_id code_combination_id,
3965 ae.ae_header_id ae_header_id,
3966 ae.ae_line_num ae_line_num
3967 from ra_cust_trx_line_gl_dist ctlgd
3968 , xla_distribution_links lk
3969 , xla_ae_headers hd
3970 , xla_ae_lines ae';
3971
3972 l_increment := l_increment+1;
3973 l_insert_stmt(l_increment):= l_temp_stmt;
3974
3975 l_temp_stmt :=' where ctlgd.cust_trx_line_gl_dist_id = lk.source_distribution_id_num_1
3976 AND ae.application_id = 222
3977 AND lk.source_distribution_type = ''RA_CUST_TRX_LINE_GL_DIST_ALL''
3978 AND lk.application_id = 222
3979 AND hd.application_id = 222
3980 AND lk.ae_header_id = ae.ae_header_id
3981 AND lk.ae_line_num = ae.ae_line_num
3982 AND ae.ae_header_id = hd.ae_header_id
3983 AND hd.ledger_id = ctlgd.set_of_books_id
3984 AND hd.event_type_code <> ''MANUAL''
3985 AND ctlgd.event_id IS NOT NULL
3986 AND ae.accounting_class_code IN (''RECEIVABLE'', ''REVENUE'', ''REFUND''
3987 , ''UNBILL'', ''UNEARNED_REVENUE'', ''TAX''
3988 , ''CHARGES'', ''FREIGHT'', ''ROUNDING''
3989 , ''FV_REC_DR'', ''FV_REC_CR'', ''ACCRUAL'')
3990 AND hd.application_id = 222
3991 AND ctlgd.event_id = lk.event_id
3992 AND ctlgd.gl_date between :gl_date_from and :gl_date_to
3993 /* Bug fix 6631925 */
3994 and ctlgd.account_set_flag = ''N''
3995 and ctlgd.posting_control_id <> -3
3996 and ctlgd.set_of_books_id = :reporting_entity_id
3997 '||l_gl_dist_org_where||'
3998 UNION ALL
3999 select
4000 DECODE(ctlgd.account_class, ''REC'',decode(sign(acctd_amount), -1 ,0, acctd_amount),
4001 ''REV'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
4002 ''TAX'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
4003 ''ROUND'',decode(sign(acctd_amount), -1,abs(acctd_amount),0),
4004 ''UNEARN'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
4005 ''FREIGHT'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
4006 ''UNBILL'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),0) acctd_amount_dr,
4007 DECODE(ctlgd.account_class, ''REC'',decode(sign(acctd_amount), -1 ,abs(acctd_amount),0),
4008 ''REV'',decode(sign(acctd_amount), -1, 0,acctd_amount),
4009 ''TAX'',decode(sign(acctd_amount), -1, 0,acctd_amount),
4010 ''ROUND'',decode(sign(acctd_amount), -1,0,acctd_amount),
4011 ''UNEARN'',decode(sign(acctd_amount), -1, 0,acctd_amount),
4012 ''FREIGHT'',decode(sign(acctd_amount), -1, 0,acctd_amount),
4013 ''UNBILL'',decode(sign(acctd_amount), -1, 0,acctd_amount),0) acctd_amount_cr,
4014 ctlgd.code_combination_id code_combination_id,
4015 NULL ae_header_id,
4016 NULL ae_line_num
4017 from ra_cust_trx_line_gl_dist ctlgd
4018 where ctlgd.gl_date between :gl_date_from and :gl_date_to
4019 /* Bug fix 6631925 */
4020 and ctlgd.account_set_flag = ''N''
4021 AND ctlgd.event_id IS NULL
4022 and ctlgd.posting_control_id <> -3
4023 and ctlgd.set_of_books_id = :reporting_entity_id
4024 '||l_gl_dist_org_where||'
4025 UNION ALL';
4026
4027 l_increment := l_increment+1;
4028 l_insert_stmt(l_increment):= l_temp_stmt;
4029
4030 ------------------------------------------------------------------------------------------ AR_XLA_ARD_CRH_LINES_V
4031 l_temp_stmt := '
4032 select lk.unrounded_accounted_dr acctd_amount_dr ,
4033 lk.unrounded_accounted_cr acctd_amount_cr ,
4034 ae.code_combination_id code_combination_id,
4035 ae.ae_header_id ae_header_id,
4036 ae.ae_line_num ae_line_num
4037 from ar_distributions_all ard
4038 , xla_distribution_links lk
4039 , xla_ae_lines ae,
4040 ar_cash_receipt_history crh,
4041 ar_cash_receipts cr,
4042 xla_ae_headers hd
4043 where cr.reversal_date IS NULL
4044 AND ard.line_id = lk.source_distribution_id_num_1
4045 AND lk.application_id = 222
4046 AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
4050 AND lk.ae_header_id = hd.ae_header_id
4047 AND ae.application_id = 222
4048 AND lk.ae_header_id = ae.ae_header_id
4049 AND lk.ae_line_num = ae.ae_line_num
4051 AND hd.event_type_code <> ''MANUAL''
4052 AND crh.cash_receipt_id = cr.cash_receipt_id
4053 AND cr.set_of_books_id = hd.ledger_id
4054 AND crh.event_id IS NOT NULL
4055 AND ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
4056 , ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
4057 , ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
4058 , ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
4059 , ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
4060 , ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
4061 , ''GAIN'', ''LOSS'', ''UNID''
4062 , ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
4063 , ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
4064 , ''FAC_BR'', ''PMT_NET'', ''PREPAY''
4065 , ''REFUND'', ''REM_BR'', ''UNAPP'')
4066 and crh.posting_control_id <> -3
4067 and crh.cash_receipt_history_id = ard.source_id
4068 and hd.accounting_date between :gl_date_from and :gl_date_to
4069 and ard.source_table = ''CRH''
4070 '||l_ard_org_where||'
4071 '||l_crh_org_where||'
4072 '||l_cr_org_where||'';
4073
4074 l_increment := l_increment+1;
4075 l_insert_stmt(l_increment):= l_temp_stmt;
4076
4077 l_temp_stmt:= 'UNION ALL
4078 select ard.acctd_amount_dr acctd_amount_dr ,
4079 ard.acctd_amount_cr acctd_amount_cr ,
4080 ard.code_combination_id code_combination_id,
4081 NULL ae_header_id,
4082 NULL ae_line_num
4083 from ar_distributions_all ard,
4084 ar_cash_receipt_history crh,
4085 ar_cash_receipts cr
4086 where cr.reversal_date IS NULL
4087 and crh.cash_receipt_id = cr.cash_receipt_id
4088 and crh.posting_control_id <> -3
4089 AND crh.event_id IS NULL
4090 and crh.cash_receipt_history_id = ard.source_id
4091 and crh.gl_date between :gl_date_from and :gl_date_to
4092 and ard.source_table = ''CRH''
4093 '||l_ard_org_where||'
4094 '||l_crh_org_where||'
4095 '||l_cr_org_where||'
4096 -----------------------------------------------------------------------------------------
4097 /* Bug fix 6432847: select receipts that are reversed*/
4098 UNION ALL';
4099
4100 l_increment := l_increment+1;
4101 l_insert_stmt(l_increment):= l_temp_stmt;
4102 ------------------------------------------------------------------------------------------ AR_XLA_ARD_CRH_LINES_V
4103 l_temp_stmt := '
4104 select lk.unrounded_accounted_dr acctd_amount_dr ,
4105 lk.unrounded_accounted_cr acctd_amount_cr ,
4106 ae.code_combination_id code_combination_id,
4107 ae.ae_header_id ae_header_id,
4108 ae.ae_line_num ae_line_num
4109 from ar_distributions_all ard
4110 , xla_distribution_links lk
4111 , xla_ae_lines ae,
4112 ar_cash_receipt_history crh,
4113 ar_cash_receipts cr,
4114 xla_ae_headers hd
4115 where cr.reversal_date IS NOT NULL
4116 AND ard.line_id = lk.source_distribution_id_num_1
4117 AND lk.application_id = 222
4118 AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
4119 AND ae.application_id = 222
4120 AND lk.ae_header_id = ae.ae_header_id
4121 AND lk.ae_line_num = ae.ae_line_num
4122 AND lk.ae_header_id = hd.ae_header_id
4123 AND hd.event_type_code <> ''MANUAL''
4124 AND crh.cash_receipt_id = cr.cash_receipt_id
4125 AND cr.set_of_books_id = hd.ledger_id
4126 AND crh.event_id IS NOT NULL
4127 AND ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
4128 , ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
4129 , ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
4130 , ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
4131 , ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
4132 , ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
4133 , ''GAIN'', ''LOSS'', ''UNID''
4134 , ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
4135 , ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
4136 , ''FAC_BR'', ''PMT_NET'', ''PREPAY''
4137 , ''REFUND'', ''REM_BR'', ''UNAPP'')
4138 and crh.posting_control_id <> -3
4139 and crh.cash_receipt_history_id = ard.source_id
4140 and hd.accounting_date between :gl_date_from and :gl_date_to
4141 and ard.source_table = ''CRH''
4142 '||l_ard_org_where||'
4143 '||l_crh_org_where||'
4144 '||l_cr_org_where||'';
4145
4146 l_increment := l_increment+1;
4147 l_insert_stmt(l_increment):= l_temp_stmt;
4148
4149 l_temp_stmt:= 'UNION ALL
4150 select ard.acctd_amount_dr acctd_amount_dr ,
4151 ard.acctd_amount_cr acctd_amount_cr ,
4152 ard.code_combination_id code_combination_id,
4153 NULL ae_header_id,
4154 NULL ae_line_num
4155 from ar_distributions_all ard,
4159 and crh.cash_receipt_id = cr.cash_receipt_id
4156 ar_cash_receipt_history crh,
4157 ar_cash_receipts cr
4158 where cr.reversal_date IS NOT NULL
4160 and crh.posting_control_id <> -3
4161 AND crh.event_id IS NULL
4162 and crh.cash_receipt_history_id = ard.source_id
4163 and crh.gl_date between :gl_date_from and :gl_date_to
4164 and ard.source_table = ''CRH''
4165 '||l_ard_org_where||'
4166 '||l_crh_org_where||'
4167 '||l_cr_org_where||'
4168 -----------------------------------------------------------------------------------------
4169 UNION ALL';
4170 /* Bug fix 6432847: with ra.gl_date condition, select
4171 applications which are not unapplied */
4172
4173 l_increment := l_increment+1;
4174 l_insert_stmt(l_increment):= l_temp_stmt;
4175 ------------------------------------------------------------------------------------------------- AR_XLA_ARD_RA_LINES_V
4176 l_temp_stmt := '
4177 select lk.unrounded_accounted_dr acctd_amount_dr ,
4178 lk.unrounded_accounted_cr acctd_amount_cr ,
4179 ae.code_combination_id code_combination_id,
4180 ae.ae_header_id ae_header_id,
4181 ae.ae_line_num ae_line_num
4182 from ar_distributions_all ard
4183 , xla_distribution_links lk
4184 , xla_ae_lines ae
4185 , ar_receivable_applications ra
4186 , xla_ae_headers hd
4187 , ar_cash_receipts cr /* Bug fix 6432847 */
4188 where ard.source_id = ra.receivable_application_id
4189 AND ard.line_id = lk.source_distribution_id_num_1
4190 AND lk.application_id = 222
4191 AND lk.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
4192 AND ae.application_id = 222
4193 AND lk.ae_header_id = ae.ae_header_id
4194 AND lk.ae_line_num = ae.ae_line_num
4195 AND lk.ae_header_id = hd.ae_header_id
4196 AND ra.set_of_books_id = hd.ledger_id
4197 AND hd.event_type_code <> ''MANUAL''
4198 AND ra.event_id IS NOT NULL
4199 AND ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
4200 , ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
4201 , ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
4202 , ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
4203 , ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
4204 , ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
4205 , ''GAIN'', ''LOSS'', ''UNID''
4206 , ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
4207 , ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
4208 , ''FAC_BR'', ''PMT_NET'', ''PREPAY''
4209 , ''REFUND'', ''REM_BR'', ''UNAPP'')
4210 and cr.reversal_date IS NULL
4211 and ra.cash_receipt_id = cr.cash_receipt_id
4212 AND hd.accounting_date between :gl_date_from and :gl_date_to
4213 and ra.posting_control_id <> -3
4214 and ard.source_table = ''RA''
4215 and ra.application_type = ''CASH''
4216 '||l_ard_org_where||'
4217 '||l_ra_org_where||'
4218 '||l_cr_org_where||'
4219 UNION ALL
4220 select ard.acctd_amount_dr acctd_amount_dr ,
4221 ard.acctd_amount_cr acctd_amount_cr ,
4222 ard.code_combination_id code_combination_id,
4223 to_number (NULL) ae_header_id,
4224 to_number (NULL) ae_line_num';
4225
4226 l_increment := l_increment+1;
4227 l_insert_stmt(l_increment):= l_temp_stmt;
4228
4229 l_temp_stmt:= '
4230 from ar_distributions_all ard,
4231 ar_receivable_applications ra,
4232 ar_cash_receipts cr /* Bug fix 6432847 */
4233 where ra.status IN (''APP'', ''ACC'', ''ACTIVITY''
4234 , ''OTHER ACC'')
4235 AND ra.event_id IS NULL
4236 and cr.reversal_date IS NULL
4237 and ra.cash_receipt_id = cr.cash_receipt_id
4238 AND ra.gl_date between :gl_date_from and :gl_date_to
4239 and ra.posting_control_id <> -3
4240 and ra.receivable_application_id = ard.source_id
4241 and ard.source_table = ''RA''
4242 and ra.application_type = ''CASH''
4243 '||l_ard_org_where||'
4244 '||l_ra_org_where||'
4245 '||l_cr_org_where||'
4246 UNION ALL
4247 select ard.acctd_amount_dr acctd_amount_dr ,
4248 ard.acctd_amount_cr acctd_amount_cr ,
4249 ard.code_combination_id code_combination_id,
4250 to_number (NULL) ae_header_id,
4251 to_number (NULL) ae_line_num
4252 from ar_distributions_all ard,
4253 ar_receivable_applications ra,
4254 ar_cash_receipts cr /* Bug fix 6432847 */
4255 where cr.reversal_date IS NULL
4256 and ra.cash_receipt_id = cr.cash_receipt_id
4257 AND ra.status IN (''UNAPP'', ''UNID'')
4258 AND ra.event_id IS NULL
4259 AND EXISTS
4260 (
4261 SELECT NULL
4262 FROM xla_distribution_links lk
4263 WHERE lk.source_distribution_id_num_1 = ard.line_id
4264 AND lk.application_id = 222
4268 and ra.receivable_application_id = ard.source_id
4265 AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'')
4266 AND ra.gl_date between :gl_date_from and :gl_date_to
4267 and ra.posting_control_id <> -3
4269 and ard.source_table = ''RA''
4270 and ra.application_type = ''CASH''
4271 '||l_ard_org_where||'
4272 '||l_ra_org_where||'
4273 '||l_cr_org_where||'
4274 --------------------------------------------------------------------------------------------------
4275 UNION ALL';
4276 /* Bug fix 6432847: with ard.gl_date condition, select
4277 applications which are unapplied */
4278
4279 l_increment := l_increment+1;
4280 l_insert_stmt(l_increment):= l_temp_stmt;
4281 ------------------------------------------------------------------------------------------------- AR_XLA_ARD_RA_LINES_V
4282 l_temp_stmt := '
4283 select lk.unrounded_accounted_dr acctd_amount_dr ,
4284 lk.unrounded_accounted_cr acctd_amount_cr ,
4285 ae.code_combination_id code_combination_id,
4286 ae.ae_header_id ae_header_id,
4287 ae.ae_line_num ae_line_num
4288 from ar_distributions_all ard
4289 , xla_distribution_links lk
4290 , xla_ae_lines ae
4291 , ar_receivable_applications ra
4292 , xla_ae_headers hd
4293 , ar_cash_receipts cr /* Bug fix 6432847 */
4294 where ard.source_id = ra.receivable_application_id
4295 AND ard.line_id = lk.source_distribution_id_num_1
4296 AND lk.application_id = 222
4297 AND lk.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
4298 AND ae.application_id = 222
4299 AND lk.ae_header_id = ae.ae_header_id
4300 AND lk.ae_line_num = ae.ae_line_num
4301 AND lk.ae_header_id = hd.ae_header_id
4302 AND ra.set_of_books_id = hd.ledger_id
4303 AND hd.event_type_code <> ''MANUAL''
4304 AND ra.event_id IS NOT NULL
4305 AND ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
4306 , ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
4307 , ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
4308 , ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
4309 , ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
4310 , ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
4311 , ''GAIN'', ''LOSS'', ''UNID''
4312 , ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
4313 , ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
4314 , ''FAC_BR'', ''PMT_NET'', ''PREPAY''
4315 , ''REFUND'', ''REM_BR'', ''UNAPP'')
4316 and cr.reversal_date IS NOT NULL
4317 and ra.cash_receipt_id = cr.cash_receipt_id
4318 AND hd.accounting_date between :gl_date_from and :gl_date_to
4319 and ra.posting_control_id <> -3
4320 and ard.source_table = ''RA''
4321 and ra.application_type = ''CASH''
4322 '||l_ard_org_where||'
4323 '||l_ra_org_where||'
4324 '||l_cr_org_where||'
4325 UNION ALL
4326 select ard.acctd_amount_dr acctd_amount_dr ,
4327 ard.acctd_amount_cr acctd_amount_cr ,
4328 ard.code_combination_id code_combination_id,
4329 to_number (NULL) ae_header_id,
4330 to_number (NULL) ae_line_num';
4331
4332 l_increment := l_increment+1;
4336 from ar_distributions_all ard,
4333 l_insert_stmt(l_increment):= l_temp_stmt;
4334
4335 l_temp_stmt:= '
4337 ar_receivable_applications ra,
4338 ar_cash_receipts cr /* Bug fix 6432847 */
4339 where ra.status IN (''APP'', ''ACC'', ''ACTIVITY'', ''OTHER ACC'')
4340 AND ra.event_id IS NULL
4341 and cr.reversal_date IS NOT NULL
4342 and ra.cash_receipt_id = cr.cash_receipt_id
4343 AND ra.gl_date between :gl_date_from and :gl_date_to
4344 and ra.posting_control_id <> -3
4345 and ra.receivable_application_id = ard.source_id
4346 and ard.source_table = ''RA''
4347 and ra.application_type = ''CASH''
4348 '||l_ard_org_where||'
4349 '||l_ra_org_where||'
4350 '||l_cr_org_where||'
4351 UNION ALL
4352 select ard.acctd_amount_dr acctd_amount_dr ,
4353 ard.acctd_amount_cr acctd_amount_cr ,
4354 ard.code_combination_id code_combination_id,
4355 to_number (NULL) ae_header_id,
4356 to_number (NULL) ae_line_num
4357 from ar_distributions_all ard,
4358 ar_receivable_applications ra,
4359 ar_cash_receipts cr /* Bug fix 6432847 */
4360 where cr.reversal_date IS NOT NULL
4361 and ra.cash_receipt_id = cr.cash_receipt_id
4362 AND ra.status IN (''UNAPP'', ''UNID'')
4363 AND ra.event_id IS NULL
4364 AND EXISTS
4365 (
4366 SELECT NULL
4367 FROM xla_distribution_links lk
4368 WHERE lk.source_distribution_id_num_1 = ard.line_id
4369 AND lk.application_id = 222
4370 AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'')
4371 AND ra.gl_date between :gl_date_from and :gl_date_to
4372 and ra.posting_control_id <> -3
4373 and ra.receivable_application_id = ard.source_id
4374 and ard.source_table = ''RA''
4375 and ra.application_type = ''CASH''
4376 '||l_ard_org_where||'
4377 '||l_ra_org_where||'
4378 '||l_cr_org_where||'
4379 --------------------------------------------------------------------------------------------------
4380 /* Bug fix 5679071 : UNAPP records should be displayed based on how it was posted */
4381 UNION ALL';
4382
4383 l_increment := l_increment+1;
4384 l_insert_stmt(l_increment):= l_temp_stmt;
4385 ----------------------------------------------------------------------------------------------- AR_XLA_ARD_RA_LINES_V
4386 l_temp_stmt := '
4387 select lk.unrounded_accounted_dr acctd_amount_dr ,
4388 lk.unrounded_accounted_cr acctd_amount_cr ,
4389 ae.code_combination_id code_combination_id,
4390 ae.ae_header_id ae_header_id,
4391 ae.ae_line_num ae_line_num
4392 from ar_distributions_all ard
4393 , xla_distribution_links lk
4394 , xla_ae_lines ae
4395 , ar_receivable_applications ra
4396 , xla_ae_headers hd
4397 where ard.source_id = ra.receivable_application_id
4398 AND ard.line_id = lk.source_distribution_id_num_1
4399 AND lk.application_id = 222
4400 AND lk.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
4401 AND ae.application_id = 222
4402 AND lk.ae_header_id = ae.ae_header_id
4403 AND lk.ae_line_num = ae.ae_line_num
4404 AND lk.ae_header_id = hd.ae_header_id
4405 AND ra.set_of_books_id = hd.ledger_id
4406 AND hd.event_type_code <> ''MANUAL''
4407 AND ra.event_id IS NOT NULL
4408 AND ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
4409 , ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
4410 , ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
4411 , ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
4412 , ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
4413 , ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
4414 , ''GAIN'', ''LOSS'', ''UNID''
4415 , ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
4416 , ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
4417 , ''FAC_BR'', ''PMT_NET'', ''PREPAY''
4418 , ''REFUND'', ''REM_BR'', ''UNAPP'')
4419 AND hd.accounting_date between :gl_date_from and :gl_date_to
4420 and ra.posting_control_id <> -3
4421 and ard.source_table = ''RA''
4422 and ra.application_type <> ''CASH''
4423 '||l_ard_org_where||'
4424 '||l_ra_org_where||'
4425 UNION ALL
4426 select ard.acctd_amount_dr acctd_amount_dr ,
4427 ard.acctd_amount_cr acctd_amount_cr ,
4428 ard.code_combination_id code_combination_id,
4429 to_number (NULL) ae_header_id,
4430 to_number (NULL) ae_line_num';
4431
4432 l_increment := l_increment+1;
4433 l_insert_stmt(l_increment):= l_temp_stmt;
4434
4435 l_temp_stmt:= '
4436 from ar_distributions_all ard,
4437 ar_receivable_applications ra
4438 where ra.status IN (''APP'', ''ACC'', ''ACTIVITY''
4439 , ''OTHER ACC'')
4440 AND ra.event_id IS NULL
4444 and ard.source_table = ''RA''
4441 AND ra.gl_date between :gl_date_from and :gl_date_to
4442 and ra.posting_control_id <> -3
4443 and ra.receivable_application_id = ard.source_id
4445 and ra.application_type <> ''CASH''
4446 '||l_ard_org_where||'
4447 '||l_ra_org_where||'
4448 UNION ALL
4449 select ard.acctd_amount_dr acctd_amount_dr ,
4450 ard.acctd_amount_cr acctd_amount_cr ,
4451 ard.code_combination_id code_combination_id,
4452 to_number (NULL) ae_header_id,
4453 to_number (NULL) ae_line_num
4454 from ar_distributions_all ard,
4455 ar_receivable_applications ra
4456 where ra.status IN (''UNAPP'', ''UNID'')
4457 AND ra.event_id IS NULL
4458 AND EXISTS
4459 (
4460 SELECT NULL
4461 FROM xla_distribution_links lk
4462 WHERE lk.source_distribution_id_num_1 = ard.line_id
4463 AND lk.application_id = 222
4464 AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'')
4465 AND ra.gl_date between :gl_date_from and :gl_date_to
4466 and ra.posting_control_id <> -3
4467 and ra.receivable_application_id = ard.source_id
4468 and ard.source_table = ''RA''
4469 and ra.application_type <> ''CASH''
4470 '||l_ard_org_where||'
4471 '||l_ra_org_where||'
4472 ---------------------------------------------------------------------------------------------
4473 UNION ALL';
4474
4475 l_increment := l_increment+1;
4476 l_insert_stmt(l_increment):= l_temp_stmt;
4477 ---------------------------------------------------------------------------------- AR_XLA_ARD_MCD_LINES_V
4478 l_temp_stmt := '
4479 select lk.unrounded_accounted_dr acctd_amount_dr ,
4480 lk.unrounded_accounted_cr acctd_amount_cr ,
4481 ae.code_combination_id code_combination_id,
4482 ae.ae_header_id ae_header_id,
4483 ae.ae_line_num ae_line_num
4484 FROM ar_distributions_all ard
4485 , xla_distribution_links lk
4486 , xla_ae_lines ae
4487 , ar_misc_cash_distributions mcd
4488 , xla_ae_headers hd
4489 where ard.source_id = mcd.misc_cash_distribution_id
4490 AND ard.line_id = lk.source_distribution_id_num_1
4491 AND lk.application_id = 222
4492 AND lk.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
4493 AND ae.application_id = 222
4494 AND lk.ae_header_id = ae.ae_header_id
4495 AND lk.ae_line_num = ae.ae_line_num
4496 AND lk.ae_header_id = hd.ae_header_id
4497 AND mcd.set_of_books_id = hd.ledger_id
4498 AND hd.event_type_code <> ''MANUAL''
4499 AND mcd.event_id IS NOT NULL
4500 AND ae.accounting_class_code IN (''FV_MISCCASH_CR'', ''FV_MISCCASH_DR'', ''MISC_CASH''
4501 , ''CASH'', ''CONFIRMATION'', ''REMITTANCE''
4502 , ''FACTOR'', ''SHORT_TERM_DEBT'', ''BANK_CHG''
4503 , ''TAX'')
4504 AND hd.accounting_date between :gl_date_from and :gl_date_to
4505 and mcd.posting_control_id <> -3
4506 and ard.source_table = ''MCD''
4507 '||l_mcd_org_where||'
4508 UNION ALL
4509 select ard.acctd_amount_dr acctd_amount_dr ,
4510 ard.acctd_amount_cr acctd_amount_cr ,
4511 ard.code_combination_id code_combination_id,
4512 NULL ae_header_id,
4513 NULL ae_line_num
4514 from ar_distributions_all ard
4515 , ar_misc_cash_distributions mcd
4516 where ard.source_id = mcd.misc_cash_distribution_id
4517 AND mcd.event_id IS NULL
4518 and mcd.gl_date between :gl_date_from and :gl_date_to
4519 and mcd.posting_control_id <> -3
4520 and ard.source_table = ''MCD''
4521 '||l_mcd_org_where||'
4522 ----------------------------------------------------------------------------------------------
4523 UNION ALL';
4524
4525 l_increment := l_increment+1;
4526 l_insert_stmt(l_increment):= l_temp_stmt;
4527 --------------------------------------------------------------------------------- AR_XLA_ARD_ADJ_LINES_V
4528 l_temp_stmt := '
4529 select lk.unrounded_accounted_dr acctd_amount_dr ,
4530 lk.unrounded_accounted_cr acctd_amount_cr ,
4531 ae.code_combination_id code_combination_id,
4532 ae.ae_header_id ae_header_id,
4533 ae.ae_line_num ae_line_num
4534 FROM ar_distributions_all ard
4535 , xla_distribution_links lk
4536 , xla_ae_lines ae
4537 , ar_adjustments adj
4538 , xla_ae_headers hd
4539 where ard.source_id = adj.adjustment_id
4540 AND ard.line_id = lk.source_distribution_id_num_1
4541 AND lk.application_id = 222
4542 AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
4543 AND ae.application_id = 222
4544 AND lk.ae_header_id = ae.ae_header_id
4545 AND lk.ae_line_num = ae.ae_line_num
4546 AND lk.ae_header_id = hd.ae_header_id
4547 AND adj.set_of_books_id = hd.ledger_id
4548 AND hd.event_type_code <> ''MANUAL''
4549 AND adj.event_id IS NOT NULL
4553 and adj.posting_control_id <> -3
4550 AND ae.accounting_class_code IN (''RECEIVABLE'', ''ADJ'', ''CHARGES''
4551 , ''TAX'', ''DEFERRED_TAX'', ''FINCHRG_NON_REC_TAX''
4552 , ''ADJ_NON_REC_TAX'', ''ENDORSEMENT'')
4554 and ard.source_table = ''ADJ''
4555 and hd.accounting_date between :gl_date_from and :gl_date_to
4556 '||l_adj_org_where||'
4557 UNION ALL
4558 select ard.acctd_amount_dr acctd_amount_dr ,
4559 ard.acctd_amount_cr acctd_amount_cr ,
4560 ard.code_combination_id code_combination_id,
4561 NULL ae_header_id,
4562 NULL ae_line_num
4563 from ar_distributions_all ard
4564 , ar_adjustments adj
4565 where ard.source_id = adj.adjustment_id
4566 AND adj.gl_date between :gl_date_from and :gl_date_to
4567 AND adj.event_id IS NULL
4568 and adj.posting_control_id <> -3
4569 and ard.source_table = ''ADJ''
4570 '||l_adj_org_where||'
4571 -------------------------------------------------------------------------------------------
4572 UNION ALL';
4573
4574 l_increment := l_increment+1;
4575 l_insert_stmt(l_increment):= l_temp_stmt;
4576 -----------------------------------------------------------------------------------ASHLESH ar_xla_ard_trh_lines_v
4577
4578 l_temp_stmt := '
4579 select lk.unrounded_accounted_dr acctd_amount_dr ,
4580 lk.unrounded_accounted_cr acctd_amount_cr ,
4581 ae.code_combination_id code_combination_id,
4582 ae.ae_header_id ae_header_id,
4583 ae.ae_line_num ae_line_num
4584 FROM ar_distributions_all ard
4585 , xla_distribution_links lk
4586 , xla_ae_lines ae
4587 , ar_transaction_history th
4588 , ra_customer_trx trx
4589 , xla_ae_headers hd
4590 where hd.accounting_date between :gl_date_from and :gl_date_to
4591 and th.posting_control_id <> -3
4592 and ard.source_table = ''TH''
4593 AND ard.source_id = th.transaction_history_id
4594 AND ard.line_id = lk.source_distribution_id_num_1
4595 AND lk.application_id = 222
4596 AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
4597 AND ae.application_id = 222
4598 AND lk.ae_header_id = ae.ae_header_id
4599 AND lk.ae_line_num = ae.ae_line_num
4600 AND lk.ae_header_id = hd.ae_header_id
4601 AND hd.event_type_code <> ''MANUAL''
4602 AND th.customer_trx_id = trx.customer_trx_id
4603 AND trx.set_of_books_id = hd.ledger_id
4604 AND th.event_id IS NOT NULL
4605 AND ae.accounting_class_code IN (''RECEIVABLE'', ''DEFERRED_TAX'', ''TAX'', ''UNPAID_BR'', ''REM_BR'', ''FAC_BR'')
4606 '||l_ath_org_where||'
4607 UNION ALL
4608 select ard.acctd_amount_dr acctd_amount_dr ,
4609 ard.acctd_amount_cr acctd_amount_cr ,
4610 ard.code_combination_id code_combination_id,
4611 NULL ae_header_id,
4612 NULL ae_line_num
4613 FROM ar_distributions_all ard
4614 , ar_transaction_history th
4615 where ard.source_id = th.transaction_history_id
4616 AND th.event_id IS NULL
4617 AND th.gl_date between :gl_date_from and :gl_date_to
4618 and th.posting_control_id <> -3
4619 and ard.source_table = ''TH''
4620 '||l_ath_org_where||'';
4621
4622 l_increment := l_increment+1;
4623 l_insert_stmt(l_increment):= l_temp_stmt;
4624 --------------------------------------------------------------------------------------
4625 l_temp_stmt := '
4626 UNION ALL
4627 SELECT xal.accounted_dr acctd_amount_dr,
4628 xal.accounted_cr acctd_amount_cr,
4629 xal.code_combination_id code_combination_id,
4630 xal.ae_header_id ae_header_id,
4631 xal.ae_line_num ae_line_num
4632 from xla_ae_lines xal,
4633 xla_ae_headers xah,
4634 xla_transaction_entities_upg xte
4635 where xal.accounting_class_code = ''BALANCE''
4636 and xah.ledger_id = xte.ledger_id
4637 and xah.entity_id = xte.entity_id
4638 and xal.ae_header_id = xah.ae_header_id
4639 and xah.accounting_date between :gl_date_from and :gl_date_to
4640 and xal.application_id = 222
4641 and xah.application_id = 222
4642 and xte.application_id = 222
4643 and xte.ledger_id = :reporting_entity_id ) b
4644 group by b.ae_header_id,b.ae_line_num,b.code_combination_id ) dat,
4645 gl_code_combinations gc,
4646 gl_lookups lookup
4647 where dat.code_combination_id = gc.code_combination_id
4648 and lookup.lookup_code = gc.account_type
4649 and lookup.lookup_type = ''ACCOUNT TYPE'''||
4650 l_co_seg_where||
4651 l_account_where||
4652 l_account_seg_where||'
4653 group by dat.code_combination_id,lookup.description, gc.code_combination_id,gc.account_type, ' || l_sel_seg || ' , ' || l_sel_co_seg || ' )';
4654
4655 l_increment := l_increment+1;
4656 l_insert_stmt(l_increment):= l_temp_stmt;
4657
4658 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
4662 DBMS_SQL.BIND_VARIABLE(l_cursor_id, 'gl_date_from', l_gl_date_from);
4659
4660 DBMS_SQL.PARSE(l_cursor_id, l_insert_stmt,1,l_insert_stmt.last,FALSE, DBMS_SQL.NATIVE);
4661
4663 DBMS_SQL.BIND_VARIABLE(l_cursor_id, 'gl_date_to', l_gl_date_to);
4664 DBMS_SQL.BIND_VARIABLE(l_cursor_id, 'reporting_entity_id', p_reporting_entity_id);
4665
4666 ret := DBMS_SQL.EXECUTE(l_cursor_id);
4667 /* USING
4668 l_gl_date_from, l_gl_date_to, p_reporting_entity_id,
4669 l_gl_date_from, l_gl_date_to,
4670 l_gl_date_from, l_gl_date_to,
4671 l_gl_date_from, l_gl_date_to,
4672 l_gl_date_from, l_gl_date_to,
4673 l_gl_date_from, l_gl_date_to,
4674 l_gl_date_from, l_gl_date_to,
4675 l_gl_date_from, l_gl_date_to,
4676 l_gl_date_from, l_gl_date_to,
4677 l_gl_date_from, l_gl_date_to, p_reporting_entity_id ;*/
4678
4679
4680 update ar_gl_recon_gt argt
4681 set (opening_balance_dr,
4682 opening_balance_cr,
4683 period_activity_dr,
4684 period_activity_cr) = (select nvl(glb.begin_balance_dr,0),
4685 nvl(glb.begin_balance_cr,0),
4686 nvl(glb.period_net_dr,0),
4687 nvl(glb.period_net_cr,0)
4688 from gl_balances glb
4689 where glb.period_name = get_period_name()
4690 and glb.code_combination_id = argt.code_combination_id
4691 and glb.actual_flag = 'A'
4692 and glb.ledger_id = get_set_of_books_id()
4693 and glb.currency_code = get_functional_currency());
4694
4695 update ar_gl_recon_gt argt
4696 set (subledger_not_ar_dr ,
4697 subledger_not_ar_cr ,
4698 subledger_manual_dr ,
4699 subledger_manual_cr ,
4700 subledger_rec_dr,
4701 subledger_rec_cr,
4702 gl_unposted_dr,
4703 gl_unposted_cr) =
4704 (select sum(decode(gjh.je_source,'Manual', 0,
4705 'Receivables', 0,
4706 decode(gjl.status,
4707 'P',gjl.accounted_dr,0))) subledger_not_ar_dr ,
4708 sum(decode(gjh.je_source,'Manual', 0,
4709 'Receivables', 0,
4710 decode(gjl.status,
4711 'P',gjl.accounted_cr,0))) subledger_not_ar_cr,
4712 sum(decode(gjh.je_source, 'Manual',
4713 decode(gjl.status,'P',
4714 gjl.accounted_dr,0),0)) subledger_manual_dr ,
4715 sum(decode(gjh.je_source, 'Manual',
4716 decode(gjl.status,'P',
4717 gjl.accounted_cr,0),0)) subledger_manual_cr,
4718 sum(decode(gjh.je_source, 'Receivables',
4719 decode(gjl.status,'P',
4720 gjl.accounted_dr,0),0)) subledger_receivables_dr ,
4721 sum(decode(gjh.je_source, 'Receivables',
4722 decode(gjl.status,'P',
4723 gjl.accounted_cr,0),0)) subledger_receivables_cr,
4724 sum(decode(gjl.status,'P',0,gjl.accounted_dr)) gl_unposted_dr,
4725 sum(decode(gjl.status,'P',0,gjl.accounted_cr)) gl_unposted_cr
4726 from gl_je_lines gjl,
4727 gl_je_headers gjh
4728 where gjl.code_combination_id = argt.code_combination_id
4729 and gjl.period_name = get_period_name()
4730 and gjl.ledger_id = get_set_of_books_id()
4731 and gjl.je_header_id = gjh.je_header_id
4732 and gjh.actual_flag = 'A'
4733 and gjh.currency_code <> 'STAT'
4734 group by gjl.code_combination_id);
4735
4736 update ar_gl_recon_gt argt
4737 set (gl_interface_dr, gl_interface_cr) =
4738 (select sum(nvl(gif.accounted_dr,0)) gl_interface_dr,
4739 sum(nvl(gif.accounted_cr,0)) gl_interface_cr
4740 from gl_interface gif,
4741 gl_je_sources gjs
4742 where gif.code_combination_id = argt.code_combination_id
4743 and gif.accounting_date between get_gl_date_from() and get_gl_date_to()
4744 and gif.user_je_source_name = gjs.user_je_source_name
4745 and gjs.je_source_name = 'Receivables'
4746 and gif.actual_flag = 'A'
4747 group by gif.code_combination_id);
4748
4749 /* Commented for bug 10177944, the length of the column 'account_desc' in the table
4750 'ar_gl_recon_gt' is 240, but the return value's length can up to 2000, so cut the
4751 description of it's more than 240./*
4752 /*update ar_gl_recon_gt
4753 set account_desc = ar_calc_aging.get_description (101,'GL#',
4754 arp_recon_rep.get_chart_of_accounts_id(),'GL_ACCOUNT',code_combination_id);*/
4755
4756
4757 -- Fix for bug 16357135 .. pass "code_combination_id" in place of
4761 arp_recon_rep.get_chart_of_accounts_id(),'GL_ACCOUNT',code_combination_id),1,235);
4758 -- "account" for the get_decription function
4759 update ar_gl_recon_gt
4760 set account_desc = substr(get_description(101,'GL#',
4762
4763 l_xml_query := '
4764 select code_combination_id,
4765 account_type,
4766 account,
4767 account_desc,
4768 company,
4769 decode(account_type_code,''A'',1,''L'',2,''R'',3,''E'',4) account_type_code,
4770 nvl(opening_balance_dr,0) begin_gl_bal_debit,
4771 nvl(opening_balance_cr,0) begin_gl_bal_credit,
4772 nvl(opening_balance_dr,0)+nvl(period_activity_dr,0) end_gl_bal_debit,
4773 nvl(opening_balance_cr,0)+nvl(period_activity_cr,0) end_gl_bal_credit,
4774 nvl(subledger_not_ar_dr,0) subledger_not_ar_debit,
4775 nvl(subledger_not_ar_cr,0) subledger_not_ar_credit,
4776 nvl(subledger_manual_dr,0) subledger_manual_debit,
4777 nvl(subledger_manual_cr,0) subledger_manual_credit,
4778 nvl(subledger_rec_dr,0) subledger_receivables_debit,
4779 nvl(subledger_rec_cr,0) subledger_receivables_credit,
4780 nvl(gl_unposted_dr,0) gl_unposted_debit,
4781 nvl(gl_unposted_cr,0) gl_unposted_credit,
4782 nvl(gl_interface_dr,0) gl_interface_debit,
4783 nvl(gl_interface_cr,0) gl_interface_credit,
4784 nvl(receivables_dr,0) receivables_debit,
4785 nvl(receivables_cr,0) receivables_credit
4786 from ar_gl_recon_gt
4787 where ''N'' = arp_recon_rep.get_out_of_balance_only()
4788 or nvl(receivables_dr,0)- nvl(subledger_rec_dr,0) <> 0
4789 or nvl(receivables_cr,0)- nvl(subledger_rec_cr,0) <> 0
4790 order by account_type_code,
4791 company,
4792 account';
4793
4794 DBMS_UTILITY.DB_VERSION(l_version, l_compatibility);
4795 l_majorVersion := to_number(substr(l_version, 1, instr(l_version,'.')-1));
4796
4797 IF (l_majorVersion > 8 and l_majorVersion < 9) THEN
4798 BEGIN
4799 queryCtx := DBMS_XMLQuery.newContext(l_xml_query);
4800 DBMS_XMLQuery.setRaiseNoRowsException(queryCtx,TRUE);
4801 l_result := DBMS_XMLQuery.getXML(queryCtx);
4802 DBMS_XMLQuery.closeContext(queryCtx);
4803 l_rows_processed := 1;
4804 EXCEPTION WHEN OTHERS THEN
4805 DBMS_XMLQuery.getExceptionContent(queryCtx,l_errNo,l_errMsg);
4806 IF l_errNo = 1403 THEN
4807 l_rows_processed := 0;
4808 END IF;
4809 DBMS_XMLQuery.closeContext(queryCtx);
4810 END;
4811
4812 /*
4813 Bug# 10177944
4814 Starting with 10g DBMS_XMLQUERY package(written java) is deprecated and
4815 it is recommended to use DBMS_XMLGEN package.DBMS_XMLGEN package is similar
4816 to the DBMS_XMLQuery package, except that it is written in C and
4817 compiled into the database kernel.The issue with the DBMS_XMLGEN package is that
4818 it formats the number based on the preferences set.
4819 If the NLS NUMERIC CHARACTERS is set to ",." then the package generates the XML
4820 in such away that all the numbers are formatted using the NLS_NUMERIC_CHARACTERS.
4821 Say, if the number is 12840.5(Raw format) and if NLS_NUMERIC_CHARACTERS is set to ",.",
4822 then the XML generated will have the number as 12.840,50
4823
4824 Issue with Formatting:
4825 This procedure (generate_xml) is to generate the XML for the aging report.
4826 Once the XML got generated, OPP will pickup the XML and transform it into a Report using a template.
4827 While transforming the report, it there exists any field of type number on the template, again
4828 it tries to convert into Number. If the number is not in a raw format then an exception is raised.
4829 As OPP always expects the numbers in raw format,we need to alter the session such that number
4830 gets generated in raw format
4831 */
4832
4833 ELSIF (l_majorVersion >= 9 ) THEN
4834
4835 /*Flag to detrmine if the session has been altered */
4836 l_nls_altered := FALSE;
4837
4838 /*Get the NLS_NUMERIC_CHAR Settings from Session*/
4839 SELECT value INTO l_nls_numeric_char
4840 FROM v$NLS_PARAMETERS
4841 WHERE parameter = 'NLS_NUMERIC_CHARACTERS';
4842
4843 /*Set the NLS_NUMERIC_CHAR Setting*/
4844 IF l_nls_numeric_char <> '.,' THEN
4845 l_alter_str:= 'ALTER SESSION SET nls_numeric_characters=''.,''';
4846 EXECUTE IMMEDIATE l_alter_str;
4847 l_nls_altered := TRUE;
4848 l_alter_str:= NULL;
4849 END IF;
4850
4851 qryCtx := DBMS_XMLGEN.newContext(l_xml_query);
4852 l_result := DBMS_XMLGEN.getXML(qryCtx,DBMS_XMLGEN.NONE);
4853 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
4854 DBMS_XMLGEN.closeContext(qryCtx);
4855 END IF;
4856
4857 /* Changes for bug 10177944 */
4858 /*If NLS_NUMERIC_CHAR has been modified, restore the same*/
4859 IF l_nls_altered THEN
4860 l_alter_str := 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='''||l_nls_numeric_Char||'''';
4861 EXECUTE IMMEDIATE l_alter_str;
4862 END IF;
4863
4864 IF l_rows_processed <> 0 THEN
4868 l_resultOffset := 0;
4865 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
4866 tempResult := l_result;
4867 ELSE
4869 END IF;
4870
4871 l_new_line := '
4872 ';
4873 select to_char(sysdate,'YYYY-MM-DD')
4874 into l_report_date
4875 from dual;
4876
4877 /* Bug 4708930
4878 Get the special characters replaced */
4879 l_reporting_entity_name := format_string(l_reporting_entity_name);
4880 l_reporting_level_name := format_string(l_reporting_level_name);
4881 l_organization := format_string(l_organization);
4882 l_sob_name := format_string(l_sob_name);
4883 l_message_acct := format_string(l_message_acct);
4884
4885 /* Prepare the tag for the report heading */
4886 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
4887 l_xml_header := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
4888 l_xml_header := l_xml_header ||l_new_line||'<ARGLRECON>';
4889 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT_ACCT>'||l_message_acct||'</MSG_TXT_ACCT>';
4890 l_xml_header := l_xml_header ||l_new_line||'<PERIOD>'||p_period_name||'</PERIOD>';
4891 l_xml_header := l_xml_header ||l_new_line||'<REPORT_DATE>'||l_report_date||'</REPORT_DATE>';
4892 l_xml_header := l_xml_header ||l_new_line||'<REPORTING_LEVEL>'||l_reporting_level_name||'</REPORTING_LEVEL>';
4893 l_xml_header := l_xml_header ||l_new_line||'<REPORTING_ENTITY>'||l_reporting_entity_name||'</REPORTING_ENTITY>';
4894 l_xml_header := l_xml_header ||l_new_line||'<SOB_ID>'||p_sob_id||'</SOB_ID>';
4895 l_xml_header := l_xml_header ||l_new_line||'<OUT_OF_BAL_ONLY>'||p_out_of_balance_only||'</OUT_OF_BAL_ONLY>';
4896 l_xml_header := l_xml_header ||l_new_line||'<CO_SEG_LOW>'||p_co_seg_low||'</CO_SEG_LOW>';
4897 l_xml_header := l_xml_header ||l_new_line||'<CO_SEG_HIGH>'||p_co_seg_high||'</CO_SEG_HIGH>';
4898 l_xml_header := l_xml_header ||l_new_line||'<GL_DATE_FROM>'||to_char(l_gl_date_from,'YYYY-MM-DD')||'</GL_DATE_FROM>';
4899 l_xml_header := l_xml_header ||l_new_line||'<GL_DATE_TO>'||to_char(l_gl_date_to,'YYYY-MM-DD')||'</GL_DATE_TO>';
4900 l_xml_header := l_xml_header ||l_new_line||'<GL_ACCOUNT_LOW>'||p_gl_account_low||'</GL_ACCOUNT_LOW>';
4901 l_xml_header := l_xml_header ||l_new_line||'<GL_ACCOUNT_HIGH>'||p_gl_account_high||'</GL_ACCOUNT_HIGH>';
4902 l_xml_header := l_xml_header ||l_new_line||'<SUMMARY_ACCOUNT>'||p_summary_account||'</SUMMARY_ACCOUNT>';
4903 l_xml_header := l_xml_header ||l_new_line||'<SET_OF_BOOKS>'||l_sob_name||'</SET_OF_BOOKS>';
4904 l_xml_header := l_xml_header ||l_new_line||'<ORGANIZATION>'||l_organization||'</ORGANIZATION>';
4905 l_xml_header := l_xml_header ||l_new_line||'<FUNCTIONAL_CURRENCY>'||l_functional_currency||'</FUNCTIONAL_CURRENCY>';
4906 l_xml_header := l_xml_header ||l_new_line||'<NUM_ROWS>'||l_rows_processed||'</NUM_ROWS>';
4907 l_xml_header := l_xml_header ||l_new_line||'<CURRENCY_FORMAT>'||l_format||'</CURRENCY_FORMAT>'||l_new_line;
4908 l_close_tag := l_new_line||'</ARGLRECON>'||l_new_line;
4909
4910 l_xml_header_length := length(l_xml_header);
4911 IF l_rows_processed <> 0 THEN
4912 dbms_lob.write(tempResult,l_xml_header_length,1,l_xml_header);
4913 dbms_lob.copy(tempResult,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
4914 l_xml_header_length,l_resultOffset);
4915 ELSE
4916 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
4917 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
4918 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
4919 END IF;
4920
4921 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
4922
4923 ar_cumulative_balance_report.process_clob(tempResult);
4924 p_result := tempResult;
4925
4926 log('arglrecon_load_xml(-)');
4927
4928 /* Start changes for bug 10177944 */
4929 EXCEPTION
4930 WHEN OTHERS THEN
4931 /*If NLS_NUMERIC_CHAR has been modified, restore the same*/
4932 IF l_nls_altered THEN
4933 l_alter_str := 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='''||l_nls_numeric_Char||'''';
4934 EXECUTE IMMEDIATE l_alter_str;
4935 END IF;
4936
4937 RAISE;
4938 /* End changes for bug 10177944 */
4939
4940 END arglrecon_load_xml;
4941
4942 FUNCTION get_description(
4943 p_application_id in number,
4944 p_id_flex_code in varchar2,
4945 p_id_flex_num in number default NULL,
4946 p_qualifier in varchar2,
4947 p_data in varchar2) return varchar2 IS
4948
4949 l_description varchar2(2000);
4950 l_account varchar2(30);
4951
4952 BEGIN
4953
4954 l_description := fa_rx_flex_pkg.get_description(
4955 p_application_id => p_application_id,
4956 p_id_flex_code => p_id_flex_code,
4957 p_id_flex_num => p_id_flex_num,
4958 p_qualifier => p_qualifier,
4959 p_data => p_data);
4960
4961 return l_description;
4962
4963 END get_description;
4964
4965 END ARP_RECON_REP;