[Home] [Help]
PACKAGE BODY: APPS.ARP_RECON_REP
Source
1 PACKAGE BODY ARP_RECON_REP as
2 /* $Header: ARGLRECB.pls 120.24.12010000.5 2009/03/05 10:35:34 ankuagar 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_all';
284 arp_recon_rep.var_tname.l_ar_payment_schedules_all := 'ar_payment_schedules_all';
285 arp_recon_rep.var_tname.l_ar_adjustments_all := 'ar_adjustments_all';
286 arp_recon_rep.var_tname.l_ar_cash_receipt_history_all := 'ar_cash_receipt_history_all';
287 arp_recon_rep.var_tname.l_ar_batches_all := 'ar_batches_all';
288 arp_recon_rep.var_tname.l_ar_cash_receipts_all := 'ar_cash_receipts_all';
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_all';
294 arp_recon_rep.var_tname.l_ra_batches_all := 'ra_batches_all';
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_all';
300 arp_recon_rep.var_tname.l_ar_rate_adjustments_all := 'ar_rate_adjustments_all';
301 arp_recon_rep.var_tname.l_ar_receivable_apps_all := 'ar_receivable_applications_all';
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,
357 p_posting_status IN VARCHAR2,
354 p_co_seg_high IN VARCHAR2,
355 p_gl_date_from IN VARCHAR2,
356 p_gl_date_to 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,
441 INTO l_sob_name,
438 sob.set_of_books_id,
439 sob.currency_code functional_currency,
440 sob.chart_of_accounts_id
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_all 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
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
468 /* Initialize the reporting context */
469 init(p_sob_id);
470
471 /* Set the org conditions */
472
473 XLA_MO_REPORTING_API.Initialize(p_reporting_level, p_reporting_entity_id, 'AUTO');
474
475 l_adj_org_where := XLA_MO_REPORTING_API.Get_Predicate('adj',NULL);
476 l_ard_org_where := XLA_MO_REPORTING_API.Get_Predicate('ard',NULL);
477 l_trx_org_where := XLA_MO_REPORTING_API.Get_Predicate('trx',NULL);
478 l_pay_org_where := XLA_MO_REPORTING_API.Get_Predicate('pay',NULL);
479 l_rec_org_where := XLA_MO_REPORTING_API.Get_Predicate('rec',NULL);
480 l_type_org_where := XLA_MO_REPORTING_API.Get_Predicate('type',NULL);
481 l_sysparam_org_where := XLA_MO_REPORTING_API.Get_Predicate('sysparam',NULL);
482
483
484 /* Replace the bind variables with global functions */
485 l_adj_org_where := replace(l_adj_org_where,
486 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
487 l_ard_org_where := replace(l_ard_org_where,
488 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
489 l_trx_org_where := replace(l_trx_org_where,
490 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
491 l_pay_org_where := replace(l_pay_org_where,
492 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
493 l_rec_org_where := replace(l_rec_org_where,
494 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
495 l_type_org_where := replace(l_type_org_where,
496 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
497 l_sysparam_org_where := replace(l_sysparam_org_where,
498 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
499
500
501 l_reporting_entity_name := substrb(XLA_MO_REPORTING_API.get_reporting_entity_name,1,80);
502 l_reporting_level_name := substrb(XLA_MO_REPORTING_API.get_reporting_level_name,1,30);
503
504 /* Multi Org Uptake: Show appropriate message to the user depending upon the security profile */
505 IF p_reporting_level = '1000' THEN
506 l_ld_sp:= mo_utils.check_ledger_in_sp(p_reporting_entity_id);
507 END IF;
508
509 IF l_ld_sp = 'N' THEN
510 FND_MESSAGE.SET_NAME('FND','FND_MO_RPT_PARTIAL_LEDGER');
511 l_message := FND_MESSAGE.get;
512 END IF;
513
514 /* Bug fix 4942083*/
515 IF arp_util.Open_Period_Exists(p_reporting_level,
516 p_reporting_entity_id,
517 arp_recon_rep.var_tname.g_gl_date_from,
518 arp_recon_rep.var_tname.g_gl_date_to) THEN
519 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
520 l_message_acct := FND_MESSAGE.Get;
521 END IF;
522
523 /* Get the org name */
524 IF p_reporting_level = '3000' THEN
525 select substrb(hou.name,1,60)
526 into l_organization
527 from hr_organization_units hou
528 where hou.organization_id = arp_recon_rep.var_tname.g_reporting_entity_id;
529 ELSE
530 select meaning
531 into l_organization
532 from ar_lookups
533 where lookup_code ='ALL' and lookup_type ='ALL';
534 END IF;
535
536 /* Build the WHERE clauses */
537 /*buf fix 5654975 Replaced p_coa_id with l_coa_id*/
538
539 IF p_co_seg_low IS NULL AND p_co_seg_high IS NULL THEN
540 l_co_seg_where := NULL;
541 ELSIF p_co_seg_low IS NULL THEN
542 l_co_seg_where := ' AND ' ||
543 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
544 p_id_flex_code => 'GL#',
545 p_id_flex_num => l_coa_id,
546 p_table_alias => 'GC',
547 p_mode => 'WHERE',
551 ELSIF p_co_seg_high IS NULL THEN
548 p_qualifier => 'GL_BALANCING',
549 p_function => '<=',
550 p_operand1 => p_co_seg_high);
552 l_co_seg_where := ' AND ' ||
553 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
554 p_id_flex_code => 'GL#',
555 p_id_flex_num => l_coa_id,
556 p_table_alias => 'GC',
557 p_mode => 'WHERE',
558 p_qualifier => 'GL_BALANCING',
559 p_function => '>=',
560 p_operand1 => p_co_seg_low);
561 ELSE
562 l_co_seg_where := ' AND ' ||
563 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
564 p_id_flex_code => 'GL#',
565 p_id_flex_num => l_coa_id,
566 p_table_alias => 'GC',
567 p_mode => 'WHERE',
568 p_qualifier => 'GL_BALANCING',
569 p_function => 'BETWEEN',
570 p_operand1 => p_co_seg_low,
571 p_operand2 => p_co_seg_high);
572 END IF;
573
574 IF p_gl_date_from IS NULL and p_gl_date_to IS NULL THEN
575 l_gl_date_where := NULL;
576 ELSIF p_gl_date_from IS NULL THEN
577 l_gl_date_where :=' and adj.gl_date <= arp_recon_rep.get_gl_date_to()';
578 ELSIF p_gl_date_to IS NULL THEN
579 l_gl_date_where :=' and adj.gl_date >= arp_recon_rep.get_gl_date_from() ' ;
580 ELSE
581 l_gl_date_where := ' and adj.gl_date between arp_recon_rep.get_gl_date_from() and arp_recon_rep.get_gl_date_to() ';
582 END IF;
583
584 IF p_gl_account_low IS NOT NULL AND p_gl_account_high IS NOT NULL THEN
585 l_account_where := ' AND ' || AR_CALC_AGING.FLEX_SQL(
586 p_application_id=> 101,
587 p_id_flex_code =>'GL#',
588 p_id_flex_num =>l_coa_id,
589 p_table_alias => 'gc',
590 p_mode => 'WHERE',
591 p_qualifier => 'ALL',
592 p_function=> 'BETWEEN',
593 p_operand1 => p_gl_account_low,
594 p_operand2 => p_gl_account_high);
595 ELSE
596 l_account_where := NULL;
597 END IF;
598
599 IF p_summary_account IS NOT NULL THEN
600 SELECT fcav.application_column_name, flex_value_set_id
601 INTO l_natural_segment_col , l_flex_value_set_id
602 FROM fnd_segment_attribute_values fcav,
603 fnd_id_flex_segments fifs
604 WHERE fcav.application_id = 101
605 AND fcav.id_flex_code = 'GL#'
606 AND fcav.id_flex_num = arp_recon_rep.var_tname.g_chart_of_accounts_id
607 AND fcav.attribute_value = 'Y'
608 AND fcav.segment_attribute_type = 'GL_ACCOUNT'
609 AND fifs.application_id = fcav.application_id
610 AND fifs.id_flex_code = fcav.id_flex_code
611 AND fifs.id_flex_num = fcav.id_flex_num
612 AND fcav.application_column_name = fifs.application_column_name;
613
614 get_detail_accounts(l_flex_value_set_id, p_summary_account, l_code_combinations);
615
616 l_account_seg_where := ' and gc.'||l_natural_segment_col||' in ('||l_code_combinations||' )';
617 ELSE
618 l_account_seg_where := NULL;
619 END IF;
620
621 IF nvl(p_receivable_mode,'N') = 'Y' THEN
622 l_source_type_where := ' and ard.source_type in (''REC'',''UNPAIDREC'') ';
623 select meaning
624 into l_receivable_mode_meaning
625 from fnd_lookups
626 where lookup_type = 'YES_NO'
627 and lookup_code = 'Y';
628 ELSE
629 l_source_type_where := NULL;
630 select meaning
631 into l_receivable_mode_meaning
632 from fnd_lookups
633 where lookup_type = 'YES_NO'
634 and lookup_code = 'N';
635 END IF;
636
637 IF p_posting_status IS NOT NULL THEN
638 select meaning
639 into l_status_meaning
640 from ar_lookups
641 where lookup_type = 'POSTED_STATUS'
642 and lookup_code = arp_recon_rep.var_tname.g_posting_status;
643
644 l_posting_status_where := 'and nvl(adj.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')) =
645 decode(arp_recon_rep.get_posting_status(),
646 ''POSTED'',adj.gl_posted_date,
647 ''UNPOSTED'',TO_DATE(''01/01/0001'',''MM/DD/YYYY''),
648 nvl(adj.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')))';
649 ELSE
650 l_status_meaning := NULL;
651 l_posting_status_where := NULL;
652 END IF;
653
654 l_xml_query := '
655 select trx.invoice_currency_code,
656 type.name,
657 adj.posting_control_id,
658 trx.trx_number,
662 adj.adjustment_number,
659 to_char(pay.due_date,''YYYY-MM-DD'') due_date,
660 to_char(pay.gl_date,''YYYY-MM-DD'') trx_gl_date,
661 to_char(adj.gl_date,''YYYY-MM-DD'') adj_gl_date,
663 decode(adj.adjustment_type,''C'', look.meaning,
664 decode(rec.type, ''FINCHRG'',''Finance'',''Adjustment'')) adj_class,
665 rec.name activity,
666 substrb(party.party_name,1,50) customer_name,
667 cust.account_number customer_number,
668 to_char(trx.trx_date,''YYYY-MM-DD'') trx_date,
669 nvl(ard.amount_dr,0) entered_debit,
670 nvl(ard.amount_cr,0) entered_credit,
671 nvl(ard.acctd_amount_dr,0) acctd_debit,
672 nvl(ard.acctd_amount_cr,0) acctd_credit,
673 gc.code_combination_id account_code_combination_id,
674 l_cat.meaning category,
675 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
676 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
677 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
678 from hz_cust_accounts cust,
679 hz_parties party,
680 ra_cust_trx_types_all type,
681 gl_code_combinations gc,
682 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' pay,
683 ar_receivables_trx_all rec,
684 '||arp_recon_rep.var_tname.l_ra_customer_trx_all||' trx,
685 '||arp_recon_rep.var_tname.l_ar_adjustments_all||' adj,
686 '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
687 ar_lookups look,
688 ar_lookups l_cat
689 where trx.complete_flag = ''Y''
690 and cust.cust_account_id = trx.bill_to_customer_id
691 and cust.party_id = party.party_id
692 and trx.set_of_books_id = arp_recon_rep.get_set_of_books_id()
693 and trx.cust_trx_type_id = type.cust_trx_type_id
694 and trx.customer_trx_id = pay.customer_trx_id
695 and pay.payment_schedule_id = adj.payment_schedule_id
696 and nvl(adj.status, ''A'') = ''A''
697 and type.type in (''INV'',''DEP'',''GUAR'',''CM'',''DM'',''CB'')
698 and nvl(type.org_id,-99) = nvl(trx.org_id,-99)
699 and look.lookup_type = ''INV/CM''
700 and look.lookup_code = type.type
701 and nvl(adj.postable,''Y'') = ''Y''
702 and adj.receivables_trx_id is not null
703 and adj.receivables_trx_id <> -15
704 and adj.receivables_trx_id = rec.receivables_trx_id
705 and nvl(rec.org_id,-99) = nvl(trx.org_id,-99)
706 and ard.source_id = adj.adjustment_id
707 and ard.source_table = ''ADJ''
708 and gc.code_combination_id = ard.code_combination_id
709 and gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
710 and l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
711 and l_cat.lookup_code = (''ADJ_''||ard.source_type)
712 '||l_adj_org_where||'
713 '||l_ard_org_where||'
714 '||l_rec_org_where||'
715 '||l_trx_org_where||'
716 '||l_pay_org_where||'
717 '||l_type_org_where||'
718 '||l_gl_date_where ||'
719 '||l_co_seg_where ||'
720 '||l_account_where ||'
721 '||l_account_seg_where ||'
722 '||l_source_type_where||'
723 '||l_posting_status_where||'
724 order by company, category, account,adj_gl_date, adjustment_number' ;
725
726 DBMS_UTILITY.DB_VERSION(l_version, l_compatibility);
727 l_majorVersion := to_number(substr(l_version, 1, instr(l_version,'.')-1));
728
729 IF (l_majorVersion > 8 and l_majorVersion < 9) THEN
730 BEGIN
731 queryCtx := DBMS_XMLQuery.newContext(l_xml_query);
732 DBMS_XMLQuery.setRaiseNoRowsException(queryCtx,TRUE);
733 l_result := DBMS_XMLQuery.getXML(queryCtx);
734 DBMS_XMLQuery.closeContext(queryCtx);
735 l_rows_processed := 1;
736 EXCEPTION WHEN OTHERS THEN
737 DBMS_XMLQuery.getExceptionContent(queryCtx,l_errNo,l_errMsg);
738 IF l_errNo = 1403 THEN
739 l_rows_processed := 0;
740 END IF;
741 DBMS_XMLQuery.closeContext(queryCtx);
742 END;
743 ELSIF (l_majorVersion >= 9 ) THEN
744 qryCtx := DBMS_XMLGEN.newContext(l_xml_query);
745 l_result := DBMS_XMLGEN.getXML(qryCtx,DBMS_XMLGEN.NONE);
746 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
747 DBMS_XMLGEN.closeContext(qryCtx);
748 END IF;
749
750 IF l_rows_processed <> 0 THEN
754 l_resultOffset := 0;
751 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
752 tempResult := l_result;
753 ELSE
755 END IF;
756
757 l_new_line := '
758 ';
759 select to_char(sysdate,'YYYY-MM-DD')
760 into l_report_date
761 from dual;
762
763 /* Bug 4708930
764 Get the special characters replaced */
765 l_reporting_entity_name := format_string(l_reporting_entity_name);
766 l_reporting_level_name := format_string(l_reporting_level_name);
767 l_organization := format_string(l_organization);
768 l_receivable_mode_meaning := format_string(l_receivable_mode_meaning);
769 l_status_meaning := format_string(l_status_meaning);
770 l_sob_name := format_string(l_sob_name);
771 l_message := format_string(l_message);
772 l_message_acct := format_string(l_message_acct);
773
774 /* Prepare the tag for the report heading */
775 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
776 l_xml_header := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
777 l_xml_header := l_xml_header ||l_new_line||'<ARADJJOURNAL>';
778 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT>'||l_message||'</MSG_TXT>';
779 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT_ACCT>'||l_message_acct||'</MSG_TXT_ACCT>';
780 l_xml_header := l_xml_header ||l_new_line||' <PARAMETERS>';
781 l_xml_header := l_xml_header ||l_new_line||' <REPORT_DATE>'||l_report_date||'</REPORT_DATE>';
782 l_xml_header := l_xml_header ||l_new_line||' <REPORTING_LEVEL>'||l_reporting_level_name||'</REPORTING_LEVEL>';
783 l_xml_header := l_xml_header ||l_new_line||' <REPORTING_ENTITY>'||l_reporting_entity_name||'</REPORTING_ENTITY>';
784 l_xml_header := l_xml_header ||l_new_line||' <SOB_ID>'||p_sob_id||'</SOB_ID>';
785 l_xml_header := l_xml_header ||l_new_line||' <CO_SEG_LOW>'||p_co_seg_low||'</CO_SEG_LOW>';
786 l_xml_header := l_xml_header ||l_new_line||' <CO_SEG_HIGH>'||p_co_seg_high||'</CO_SEG_HIGH>';
787 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>';
788 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>';
789 l_xml_header := l_xml_header ||l_new_line||' <POSTING_STATUS>'||l_status_meaning||'</POSTING_STATUS>';
790 l_xml_header := l_xml_header ||l_new_line||' <GL_ACCOUNT_LOW>'||p_gl_account_low||'</GL_ACCOUNT_LOW>';
791 l_xml_header := l_xml_header ||l_new_line||' <GL_ACCOUNT_HIGH>'||p_gl_account_high||'</GL_ACCOUNT_HIGH>';
792 l_xml_header := l_xml_header ||l_new_line||' <SUMMARY_ACCOUNT>'||p_summary_account||'</SUMMARY_ACCOUNT>';
793 l_xml_header := l_xml_header ||l_new_line||' <REC_MODE_ONLY>'||l_receivable_mode_meaning||'</REC_MODE_ONLY>';
794 l_xml_header := l_xml_header ||l_new_line||' <NUM_ROWS>'||l_rows_processed||'</NUM_ROWS>';
795 l_xml_header := l_xml_header ||l_new_line||' </PARAMETERS>';
796 l_xml_header := l_xml_header ||l_new_line||' <REPORT_HEADING>';
797 l_xml_header := l_xml_header ||l_new_line||' <SET_OF_BOOKS>'||l_sob_name||'</SET_OF_BOOKS>';
798 l_xml_header := l_xml_header ||l_new_line||' <ORGANIZATION>'||l_organization||'</ORGANIZATION>';
799 l_xml_header := l_xml_header ||l_new_line||' <FUNCTIONAL_CURRENCY>'||l_functional_currency||'</FUNCTIONAL_CURRENCY>';
800 l_xml_header := l_xml_header ||l_new_line||' </REPORT_HEADING>';
801
802 l_close_tag := l_new_line||'</ARADJJOURNAL>'||l_new_line;
803 l_xml_header_length := length(l_xml_header);
804 IF l_rows_processed <> 0 THEN
805 dbms_lob.write(tempResult,l_xml_header_length,1,l_xml_header);
806 dbms_lob.copy(tempResult,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
807 l_xml_header_length,l_resultOffset);
808 ELSE
809 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
810 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
811 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
812 END IF;
813
814 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
815
816 ar_cumulative_balance_report.process_clob(tempResult);
817 p_result := tempResult;
818
819 log ('aradj_journal_load_xml (-)');
820
821 END aradj_journal_load_xml;
822
823 /*========================================================================+
824 | PUBLIC PROCEDURE ARUNAPP_JOURNAL_LOAD_XML |
825 | |
826 | DESCRIPTION |
827 | |
828 | This procedure is used to generate the XML data required for reporting|
829 | Unapplied Receipts Journals |
830 | |
831 | PSEUDO CODE/LOGIC |
832 | |
833 | PARAMETERS |
834 | |
835 | |
836 | KNOWN ISSUES |
840 | |
837 | |
838 | NOTES |
839 | |
841 | MODIFICATION HISTORY |
842 | Date Author Description of Changes |
843 | 03-FEB-2004 rkader Created |
844 | |
845 *=======================================================================*/
846 PROCEDURE arunapp_journal_load_xml (
847 p_reporting_level IN VARCHAR2,
848 p_reporting_entity_id IN NUMBER,
849 p_sob_id IN NUMBER,
850 p_coa_id IN NUMBER,
851 p_co_seg_low IN VARCHAR2,
852 p_co_seg_high IN VARCHAR2,
853 p_gl_date_from IN VARCHAR2,
854 p_gl_date_to IN VARCHAR2,
855 p_posting_status IN VARCHAR2,
856 p_gl_account_low IN VARCHAR2,
857 p_gl_account_high IN VARCHAR2,
858 p_summary_account IN NUMBER,
859 p_receivable_mode IN VARCHAR2,
860 p_result OUT NOCOPY CLOB) IS
861
862 l_result CLOB;
863 tempResult CLOB;
864 l_version varchar2(20);
865 l_compatibility varchar2(20);
866 l_suffix varchar2(2);
867 l_majorVersion number;
868 l_resultOffset number;
869 l_xml_header varchar2(3000);
870 l_xml_header_length number;
871 l_errNo NUMBER;
872 l_errMsg VARCHAR2(200);
873 queryCtx DBMS_XMLquery.ctxType;
874 qryCtx DBMS_XMLGEN.ctxHandle;
875 l_xml_query VARCHAR2(32767);
876 l_natural_segment_col VARCHAR2(50);
877 l_flex_value_set_id NUMBER;
878 l_code_combinations VARCHAR2(32767);
879 TYPE ref_cur IS REF CURSOR;
880 l_xml_stmt ref_cur;
881 l_rows_processed NUMBER;
882 l_new_line VARCHAR2(1) ;
883 l_coa_id NUMBER; /*bug fix 5654975*/
884 /* Variables to hold the report heading */
885 l_sob_id NUMBER;
886 l_sob_name VARCHAR2(100);
887 l_functional_currency VARCHAR2(15);
888 l_organization VARCHAR2(60);
889 l_format VARCHAR2(40);
890 l_close_tag VARCHAR2(100);
891 l_reporting_entity_name VARCHAR2(80);
892 l_reporting_level_name VARCHAR2(30);
893 l_status_meaning VARCHAR2(30);
894 l_gl_account_type_meaning VARCHAR2(100);
895 l_receivable_mode_meaning VARCHAR2(10);
896
897 /* Variables to hold the where clause based on the input parameters*/
898 /* Changed length of the variables from 200 to 500 to address bug:5181586*/
899 /* Increased variables length to 32767 for bug 5654975 */
900 l_ra_org_where VARCHAR2(32767);
901 l_ard_org_where VARCHAR2(32767);
902 l_ps_org_where VARCHAR2(32767);
903 l_cr_org_where VARCHAR2(32767);
904 l_crh_org_where VARCHAR2(32767);
905 l_bat_org_where VARCHAR2(32767);
906 l_bs_org_where VARCHAR2(32767);
907 l_sysparam_org_where VARCHAR2(32767);
908 /* Changes to variable length ends*/
909 l_co_seg_where VARCHAR2(32767);
910 l_account_where VARCHAR2(32767);
911 l_account_seg_where VARCHAR2(32767);
912 l_gl_date_where VARCHAR2(1000);
913 l_gl_date_ard_where VARCHAR2(1000);
914 l_source_type_where VARCHAR2(32767);
915 l_gl_date_closed_where VARCHAR2(1000);
916 l_posting_status_where VARCHAR2(1000);
917 l_posting_status_ard_where VARCHAR2(500);
918 l_report_date VARCHAR2(25);
919 l_ld_sp VARCHAR2(1) := 'Y';
920 l_message VARCHAR2(2000);
921 l_message_acct VARCHAR2(1000);
922 l_encoding VARCHAR2(20);
923 BEGIN
924 log('arunapp_journal_load_xml (+)');
925
926
927
928 /* Assign the input parameters to the global variables */
929 arp_recon_rep.var_tname.g_reporting_level := p_reporting_level;
930 arp_recon_rep.var_tname.g_reporting_entity_id := p_reporting_entity_id;
931 /* bug 5654975 p_sob_id is passed incorrectly when the user
932 has access to multiple Ledgers */
933 -- arp_recon_rep.var_tname.g_set_of_books_id := p_sob_id;
934 /* Bug fix 5678284
935 p_coa_id is passed incorrectly when the user has access to multiple Ledgers */
936 --arp_recon_rep.var_tname.g_chart_of_accounts_id := p_coa_id;
937 arp_recon_rep.var_tname.g_gl_date_from := fnd_date.canonical_to_date(p_gl_date_from);
938 arp_recon_rep.var_tname.g_gl_date_to := fnd_date.canonical_to_date(p_gl_date_to);
939 arp_recon_rep.var_tname.g_posting_status := p_posting_status;
940 arp_recon_rep.var_tname.g_max_gl_date := to_date('31-12-4712','DD-MM-YYYY');
941
942
943
944 /* Added Conditional Implication to address bug:5181586*/
945 /*Added set of books id and chart of accounts id for bug 5654975*/
949 sob.currency_code functional_currency,
946 IF p_reporting_level = 1000 THEN
947 SELECT sob.name sob_name,
948 sob.set_of_books_id,
950 sob.chart_of_accounts_id
951 INTO l_sob_name,
952 l_sob_id,
953 l_functional_currency,
954 l_coa_id
955 FROM gl_sets_of_books sob
956 WHERE sob.set_of_books_id = arp_recon_rep.var_tname.g_reporting_entity_id;
957
958 ELSIF p_reporting_level = 3000 THEN
959 SELECT sob.name sob_name,
960 sob.set_of_books_id,
961 sob.currency_code functional_currency,
962 sob.chart_of_accounts_id
963 INTO l_sob_name,
964 l_sob_id,
965 l_functional_currency,
966 l_coa_id
967 FROM gl_sets_of_books sob,
968 ar_system_parameters_all sysparam
969 WHERE sob.set_of_books_id = sysparam.set_of_books_id
970 AND sysparam.org_id = arp_recon_rep.var_tname.g_reporting_entity_id;
971
972 END IF;
973 /* Changes for bug:5181586 ends*/
974
975 arp_recon_rep.var_tname.g_set_of_books_id := l_sob_id;
976 arp_recon_rep.var_tname.g_chart_of_accounts_id := l_coa_id;
977
978 /* Initialize the reporting context */
979 init(p_sob_id);
980
981 /* Set the org conditions */
982
983 XLA_MO_REPORTING_API.Initialize(p_reporting_level, p_reporting_entity_id, 'AUTO');
984
985 l_ra_org_where := XLA_MO_REPORTING_API.Get_Predicate('ra',NULL);
986 l_ard_org_where := XLA_MO_REPORTING_API.Get_Predicate('ard',NULL);
987 l_ps_org_where := XLA_MO_REPORTING_API.Get_Predicate('ps',NULL);
988 l_cr_org_where := XLA_MO_REPORTING_API.Get_Predicate('cr',NULL);
989 l_crh_org_where := XLA_MO_REPORTING_API.Get_Predicate('crh',NULL);
990 l_bat_org_where := XLA_MO_REPORTING_API.Get_Predicate('bat',NULL);
991 l_bs_org_where := XLA_MO_REPORTING_API.Get_Predicate('bs',NULL);
992 l_sysparam_org_where := XLA_MO_REPORTING_API.Get_Predicate('sysparam',NULL);
993
994 /* Replace the bind variables with global functions */
995 l_ra_org_where := replace(l_ra_org_where,
996 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
997 l_ard_org_where := replace(l_ard_org_where,
998 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
999 l_ps_org_where := replace(l_ps_org_where,
1000 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1001 l_cr_org_where := replace(l_cr_org_where,
1002 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1003 l_crh_org_where := replace(l_crh_org_where,
1004 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1005 l_bat_org_where := replace(l_bat_org_where,
1006 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1007 l_bs_org_where := replace(l_bs_org_where,
1008 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1009
1010 l_reporting_entity_name := substrb(XLA_MO_REPORTING_API.get_reporting_entity_name,1,80);
1011 l_reporting_level_name := substrb(XLA_MO_REPORTING_API.get_reporting_level_name,1,30);
1012
1013 /* Multi Org Uptake: Show appropriate message to the user depending upon the security profile */
1014 IF p_reporting_level = '1000' THEN
1015 l_ld_sp:= mo_utils.check_ledger_in_sp(p_reporting_entity_id);
1016 END IF;
1017
1018 IF l_ld_sp = 'N' THEN
1019 FND_MESSAGE.SET_NAME('FND','FND_MO_RPT_PARTIAL_LEDGER');
1020 l_message := FND_MESSAGE.get;
1021 END IF;
1022
1023 /* Bug fix 4942083*/
1024 IF arp_util.Open_Period_Exists(p_reporting_level,
1025 p_reporting_entity_id,
1026 arp_recon_rep.var_tname.g_gl_date_from,
1027 arp_recon_rep.var_tname.g_gl_date_to) THEN
1028 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
1029 l_message_acct := FND_MESSAGE.Get;
1030 END IF;
1031
1032 /* Get the org name */
1033 IF p_reporting_level = '3000' THEN
1034 select substrb(hou.name,1,60)
1035 into l_organization
1036 from hr_organization_units hou
1037 where hou.organization_id = arp_recon_rep.var_tname.g_reporting_entity_id;
1038 ELSE
1039 select meaning
1040 into l_organization
1041 from ar_lookups
1042 where lookup_code ='ALL' and lookup_type ='ALL';
1043 END IF;
1044
1045 /* Build the WHERE clauses */
1046 /*Replaced p_coa_id with l_coa_id for bug 5654975*/
1047
1048 IF p_co_seg_low IS NULL AND p_co_seg_high IS NULL THEN
1049 l_co_seg_where := NULL;
1050 ELSIF p_co_seg_low IS NULL THEN
1051 l_co_seg_where := ' AND ' ||
1052 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
1053 p_id_flex_code => 'GL#',
1054 p_id_flex_num => l_coa_id,
1055 p_table_alias => 'GC',
1059 p_operand1 => p_co_seg_high);
1056 p_mode => 'WHERE',
1057 p_qualifier => 'GL_BALANCING',
1058 p_function => '<=',
1060 ELSIF p_co_seg_high IS NULL THEN
1061 l_co_seg_where := ' AND ' ||
1062 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
1063 p_id_flex_code => 'GL#',
1064 p_id_flex_num => l_coa_id,
1065 p_table_alias => 'GC',
1066 p_mode => 'WHERE',
1067 p_qualifier => 'GL_BALANCING',
1068 p_function => '>=',
1069 p_operand1 => p_co_seg_low);
1070 ELSE
1071 l_co_seg_where := ' AND ' ||
1072 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
1073 p_id_flex_code => 'GL#',
1074 p_id_flex_num => l_coa_id,
1075 p_table_alias => 'GC',
1076 p_mode => 'WHERE',
1077 p_qualifier => 'GL_BALANCING',
1078 p_function => 'BETWEEN',
1079 p_operand1 => p_co_seg_low,
1080 p_operand2 => p_co_seg_high);
1081 END IF;
1082
1083 /* Bug fix 5678284 : Added l_gl_date_ard_where*/
1084 IF p_gl_date_from IS NULL and p_gl_date_to IS NULL THEN
1085 l_gl_date_where := NULL;
1086 l_gl_date_ard_where := NULL;
1087 ELSIF p_gl_date_from IS NULL THEN
1088 l_gl_date_where :=' and ra.gl_date <= arp_recon_rep.get_gl_date_to()';
1089 l_gl_date_ard_where :=' and ard.gl_date <= arp_recon_rep.get_gl_date_to()';
1090 ELSIF p_gl_date_to IS NULL THEN
1091 l_gl_date_where :=' and ra.gl_date >= arp_recon_rep.get_gl_date_from() ' ;
1092 l_gl_date_ard_where :=' and ard.gl_date >= arp_recon_rep.get_gl_date_from() ' ;
1093 ELSE
1094 l_gl_date_where := ' and ra.gl_date between arp_recon_rep.get_gl_date_from() and arp_recon_rep.get_gl_date_to() ';
1095 l_gl_date_ard_where := ' and ard.gl_date between arp_recon_rep.get_gl_date_from() and arp_recon_rep.get_gl_date_to() ';
1096 END IF;
1097
1098 IF p_gl_date_from IS NULL and p_gl_date_to IS NULL THEN
1099 l_gl_date_closed_where := 'and ps.gl_date_closed = arp_recon_rep.get_max_gl_date()';
1100 ELSIF p_gl_date_from IS NOT NULL THEN
1101 l_gl_date_closed_where := 'and ps.gl_date_closed >= arp_recon_rep.get_gl_date_from() ';
1102 ELSIF p_gl_date_to IS NOT NULL THEN
1103 l_gl_date_closed_where := 'and ps.gl_date_closed >= arp_recon_rep.get_gl_date_to()';
1104 END IF;
1105
1106 IF p_gl_account_low IS NOT NULL AND p_gl_account_high IS NOT NULL THEN
1107 l_account_where := ' AND ' || AR_CALC_AGING.FLEX_SQL(
1108 p_application_id=> 101,
1109 p_id_flex_code =>'GL#',
1110 p_id_flex_num =>l_coa_id,
1111 p_table_alias => 'gc',
1112 p_mode => 'WHERE',
1113 p_qualifier => 'ALL',
1114 p_function=> 'BETWEEN',
1115 p_operand1 => p_gl_account_low,
1116 p_operand2 => p_gl_account_high);
1117 ELSE
1118 l_account_where := NULL;
1119 END IF;
1120
1121 IF p_summary_account IS NOT NULL THEN
1122 SELECT fcav.application_column_name, flex_value_set_id
1123 INTO l_natural_segment_col , l_flex_value_set_id
1124 FROM fnd_segment_attribute_values fcav,
1125 fnd_id_flex_segments fifs
1126 WHERE fcav.application_id = 101
1127 AND fcav.id_flex_code = 'GL#'
1128 AND fcav.id_flex_num = arp_recon_rep.var_tname.g_chart_of_accounts_id
1129 AND fcav.attribute_value = 'Y'
1130 AND fcav.segment_attribute_type = 'GL_ACCOUNT'
1131 AND fifs.application_id = fcav.application_id
1132 AND fifs.id_flex_code = fcav.id_flex_code
1133 AND fifs.id_flex_num = fcav.id_flex_num
1134 AND fcav.application_column_name = fifs.application_column_name;
1135
1136 get_detail_accounts(l_flex_value_set_id, p_summary_account, l_code_combinations);
1137
1138 l_account_seg_where := ' and gc.'||l_natural_segment_col||' in ('||l_code_combinations||' )';
1139 ELSE
1140 l_account_seg_where := NULL;
1141 END IF;
1142
1143 /* Is this parameter redundant ?*/
1144
1145 IF nvl(p_receivable_mode,'N') = 'Y' THEN
1146 l_source_type_where := ' and ard.source_type in (''UNAPP'', ''UNID'',''ACC'',''OTHER ACC'') ';
1147 select meaning
1148 into l_receivable_mode_meaning
1149 from fnd_lookups
1150 where lookup_type = 'YES_NO'
1151 and lookup_code = 'Y';
1152 ELSE
1153 l_source_type_where := NULL;
1154 select meaning
1155 into l_receivable_mode_meaning
1156 from fnd_lookups
1157 where lookup_type = 'YES_NO'
1158 and lookup_code = 'N';
1159 END IF;
1160
1161 IF p_posting_status IS NOT NULL THEN
1162 select meaning
1163 into l_status_meaning
1164 from ar_lookups
1168 l_posting_status_where := 'and nvl(ra.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')) =
1165 where lookup_type = 'POSTED_STATUS'
1166 and lookup_code = arp_recon_rep.var_tname.g_posting_status;
1167
1169 decode(arp_recon_rep.get_posting_status(),
1170 ''POSTED'',ra.gl_posted_date,
1171 ''UNPOSTED'',TO_DATE(''01/01/0001'',''MM/DD/YYYY''),
1172 nvl(ra.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')))';
1173 /* Bug fix 5678284 : Added l_posting_status_ard_where*/
1174 l_posting_status_ard_where := 'and nvl(ard.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')) =
1175 decode(arp_recon_rep.get_posting_status(),
1176 ''POSTED'',ard.gl_posted_date,
1177 ''UNPOSTED'',TO_DATE(''01/01/0001'',''MM/DD/YYYY''),
1178 nvl(ard.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')))';
1179 ELSE
1180 l_status_meaning := NULL;
1181 l_posting_status_where := NULL;
1182 l_posting_status_ard_where := NULL;
1183 END IF;
1184
1185
1186 l_xml_query := '
1187 select cr.receipt_number payment_number,
1188 arm.name payment_method,
1189 substrb(party.party_name,1,50) customer_name,
1190 cust.account_number customer_number,
1191 to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
1192 to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
1193 nvl(ard.amount_dr,0) entered_debit,
1194 nvl(ard.amount_cr,0) entered_credit,
1195 nvl(ard.acctd_amount_dr,0) acctd_debit,
1196 nvl(ard.acctd_amount_cr,0) acctd_credit,
1197 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
1198 cr.currency_code receipt_currency,
1199 gc.code_combination_id,
1200 bs.name receipt_source,
1201 bat.name batch_name,
1202 l_cat.meaning category,
1203 /* 7008877 */
1204 ard.line_id,
1205 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
1206 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
1207 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
1208 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
1209 ar_receipt_methods arm,
1210 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
1211 gl_code_combinations gc,
1212 hz_cust_accounts cust,
1213 hz_parties party,
1214 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
1215 ar_batch_sources_all bs,
1216 '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
1217 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
1218 '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
1219 ar_lookups l_cat
1220 where nvl(ra.confirmed_flag,''Y'') = ''Y''
1221 and ra.status in (''UNAPP'',''ACC'',''UNID'',''OTHER ACC'')
1222 and ps.cash_receipt_id = ra.cash_receipt_id
1223 and ps.class = ''PMT''
1224 '||l_gl_date_closed_where||'
1225 and cr.cash_receipt_id = ra.cash_receipt_id
1226 and nvl(cr.confirmed_flag,''Y'') = ''Y''
1227 and cr.receipt_method_id = arm.receipt_method_id
1228 and crh.cash_receipt_id = cr.cash_receipt_id
1229 and crh.first_posted_record_flag = ''Y''
1230 and crh.batch_id = bat.batch_id(+)
1231 and bat.batch_source_id = bs.batch_source_id(+)
1232 and bat.org_id = bs.org_id(+)
1233 and gc.code_combination_id = ard.code_combination_id
1234 and gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
1235 and ard.source_id = ra.receivable_application_id
1236 and ard.source_table = ''RA''
1237 and cr.pay_from_customer = cust.cust_account_id(+)
1238 and cust.party_id = party.party_id(+)
1239 and l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
1240 and l_cat.lookup_code = ''TRADE_''||ard.source_type
1241 '||l_ra_org_where||'
1242 '||l_ard_org_where||'
1243 '||l_ps_org_where||'
1244 '||l_cr_org_where||'
1245 '||l_crh_org_where||'
1246 '||l_bat_org_where||'
1247 '||l_bs_org_where||'
1248 '||l_gl_date_where ||'
1249 '||l_co_seg_where ||'
1250 '||l_account_where ||'
1251 '||l_account_seg_where ||'
1252 '||l_source_type_where||'
1256 arm.name payment_method,
1253 '||l_posting_status_where||'
1254 UNION
1255 select cr.receipt_number payment_number,
1257 substrb(party.party_name,1,50) customer_name,
1258 cust.account_number customer_number,
1259 to_char(ard.gl_date,''YYYY-MM-DD'') app_gl_date,
1260 to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
1261 nvl(ard.amount_dr,0) entered_debit,
1262 nvl(ard.amount_cr,0) entered_credit,
1263 nvl(ard.acctd_amount_dr,0) acctd_debit,
1264 nvl(ard.acctd_amount_cr,0) acctd_credit,
1265 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
1266 cr.currency_code receipt_currency,
1267 gc.code_combination_id,
1268 bs.name receipt_source,
1269 bat.name batch_name,
1270 l_cat.meaning category,
1271 /* 7008877 */
1272 ard.line_id,
1273 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
1274 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
1275 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
1276 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
1277 ar_receipt_methods arm,
1278 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
1279 gl_code_combinations gc,
1280 hz_cust_accounts cust,
1281 hz_parties party,
1282 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
1283 ar_batch_sources_all bs,
1284 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
1285 '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
1286 ar_lookups l_cat
1287 where ard.source_type = ''UNAPP''
1288 and ps.cash_receipt_id = ard.cash_receipt_id
1289 and ps.class = ''PMT''
1290 '||l_gl_date_closed_where||'
1291 and cr.cash_receipt_id = ps.cash_receipt_id
1292 and nvl(cr.confirmed_flag,''Y'') = ''Y''
1293 and cr.receipt_method_id = arm.receipt_method_id
1294 and crh.cash_receipt_id = cr.cash_receipt_id
1295 and crh.first_posted_record_flag = ''Y''
1296 and crh.batch_id = bat.batch_id(+)
1297 and bat.batch_source_id = bs.batch_source_id(+)
1298 and bat.org_id = bs.org_id(+)
1299 and gc.code_combination_id = ard.code_combination_id
1300 and gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
1301 and ard.cash_receipt_id = ps.cash_receipt_id
1302 and ard.source_table in(''CRH'', ''RA'')
1303 and cr.pay_from_customer = cust.cust_account_id(+)
1304 and cust.party_id = party.party_id(+)
1305 and l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
1306 and l_cat.lookup_code = ''TRADE_''||ard.source_type
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_ard_where ||'
1314 '||l_co_seg_where ||'
1315 '||l_account_where ||'
1316 '||l_account_seg_where ||'
1317 '||l_posting_status_ard_where;
1318
1319 DBMS_UTILITY.DB_VERSION(l_version, l_compatibility);
1320 l_majorVersion := to_number(substr(l_version, 1, instr(l_version,'.')-1));
1321
1322 IF (l_majorVersion > 8 and l_majorVersion < 9) THEN
1323 BEGIN
1324 queryCtx := DBMS_XMLQuery.newContext(l_xml_query);
1325 DBMS_XMLQuery.setRaiseNoRowsException(queryCtx,TRUE);
1326 l_result := DBMS_XMLQuery.getXML(queryCtx);
1327 DBMS_XMLQuery.closeContext(queryCtx);
1328 l_rows_processed := 1;
1329 EXCEPTION WHEN OTHERS THEN
1330 DBMS_XMLQuery.getExceptionContent(queryCtx,l_errNo,l_errMsg);
1331 IF l_errNo = 1403 THEN
1332 l_rows_processed := 0;
1333 END IF;
1334 DBMS_XMLQuery.closeContext(queryCtx);
1335 END;
1336
1337 ELSIF (l_majorVersion >= 9 ) THEN
1338 qryCtx := DBMS_XMLGEN.newContext(l_xml_query);
1339 l_result := DBMS_XMLGEN.getXML(qryCtx,DBMS_XMLGEN.NONE);
1340 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
1341 DBMS_XMLGEN.closeContext(qryCtx);
1342 END IF;
1343
1344 IF l_rows_processed <> 0 THEN
1345 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
1346 tempResult := l_result;
1347 ELSE
1348 l_resultOffset := 0;
1349 END IF;
1350
1351 l_new_line := '
1352 ';
1353
1357
1354 select to_char(sysdate,'YYYY-MM-DD')
1355 into l_report_date
1356 from dual;
1358 /* Bug 4708930
1359 Get the special characters replaced */
1360 l_reporting_entity_name := format_string(l_reporting_entity_name);
1361 l_reporting_level_name := format_string(l_reporting_level_name);
1362 l_organization := format_string(l_organization);
1363 l_receivable_mode_meaning := format_string(l_receivable_mode_meaning);
1364 l_status_meaning := format_string(l_status_meaning);
1365 l_sob_name := format_string(l_sob_name);
1366 l_message := format_string(l_message);
1367 l_message_acct := format_string(l_message_acct);
1368
1369 /* Prepare the tag for the report heading */
1370 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
1371 l_xml_header := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
1372 l_xml_header := l_xml_header ||l_new_line||'<ARUNAPPJOURNAL>';
1373 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT>'||l_message||'</MSG_TXT>';
1374 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT_ACCT>'||l_message_acct||'</MSG_TXT_ACCT>';
1375 l_xml_header := l_xml_header ||l_new_line||' <PARAMETERS>';
1376 l_xml_header := l_xml_header ||l_new_line||' <REPORT_DATE>'||l_report_date||'</REPORT_DATE>';
1377 l_xml_header := l_xml_header ||l_new_line||' <REPORTING_LEVEL>'||l_reporting_level_name||'</REPORTING_LEVEL>';
1378 l_xml_header := l_xml_header ||l_new_line||' <REPORTING_ENTITY>'||l_reporting_entity_name||'</REPORTING_ENTITY>';
1379 l_xml_header := l_xml_header ||l_new_line||' <SOB_ID>'||p_sob_id||'</SOB_ID>';
1380 l_xml_header := l_xml_header ||l_new_line||' <CO_SEG_LOW>'||p_co_seg_low||'</CO_SEG_LOW>';
1381 l_xml_header := l_xml_header ||l_new_line||' <CO_SEG_HIGH>'||p_co_seg_high||'</CO_SEG_HIGH>';
1382 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>';
1383 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>';
1384 l_xml_header := l_xml_header ||l_new_line||' <POSTING_STATUS>'||l_status_meaning||'</POSTING_STATUS>';
1385 l_xml_header := l_xml_header ||l_new_line||' <GL_ACCOUNT_LOW>'||p_gl_account_low||'</GL_ACCOUNT_LOW>';
1386 l_xml_header := l_xml_header ||l_new_line||' <GL_ACCOUNT_HIGH>'||p_gl_account_high||'</GL_ACCOUNT_HIGH>';
1387 l_xml_header := l_xml_header ||l_new_line||' <SUMMARY_ACCOUNT>'||p_summary_account||'</SUMMARY_ACCOUNT>';
1388 l_xml_header := l_xml_header ||l_new_line||' <RECEIVABLES_MODE_ONLY>'||l_receivable_mode_meaning||'</RECEIVABLES_MODE_ONLY>';
1389 l_xml_header := l_xml_header ||l_new_line||' <NUM_ROWS>'||l_rows_processed||'</NUM_ROWS>';
1390 l_xml_header := l_xml_header ||l_new_line||' </PARAMETERS>';
1391 l_xml_header := l_xml_header ||l_new_line||' <REPORT_HEADING>';
1392 l_xml_header := l_xml_header ||l_new_line||' <SET_OF_BOOKS>'||l_sob_name||'</SET_OF_BOOKS>';
1393 l_xml_header := l_xml_header ||l_new_line||' <ORGANIZATION>'||l_organization||'</ORGANIZATION>';
1394 l_xml_header := l_xml_header ||l_new_line||' <FUNCTIONAL_CURRENCY>'||l_functional_currency||'</FUNCTIONAL_CURRENCY>';
1395 l_xml_header := l_xml_header ||l_new_line||' </REPORT_HEADING>';
1396
1397 l_close_tag := l_new_line||'</ARUNAPPJOURNAL>'||l_new_line;
1398 l_xml_header_length := length(l_xml_header);
1399 IF l_rows_processed <> 0 THEN
1400 dbms_lob.write(tempResult,l_xml_header_length,1,l_xml_header);
1401 dbms_lob.copy(tempResult,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
1402 l_xml_header_length,l_resultOffset);
1403 ELSE
1404 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
1405 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
1406 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
1407 END IF;
1408
1409 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
1410
1411 ar_cumulative_balance_report.process_clob(tempResult);
1412 p_result := tempResult;
1413
1414 log('arunapp_journal_load_xml (-)');
1415
1416 END arunapp_journal_load_xml;
1417
1418 /*========================================================================+
1419 | PUBLIC PROCEDURE ARAPP_JOURNAL_LOAD_XML |
1420 | |
1421 | DESCRIPTION |
1422 | |
1423 | This procedure is used to generate the XML data required for reporting|
1424 | Applied Receipts Journals |
1425 | |
1426 | PSEUDO CODE/LOGIC |
1427 | |
1428 | PARAMETERS |
1429 | |
1430 | |
1431 | KNOWN ISSUES |
1432 | |
1436 | MODIFICATION HISTORY |
1433 | NOTES |
1434 | |
1435 | |
1437 | Date Author Description of Changes |
1438 | 03-FEB-2004 rkader Created |
1439 | |
1440 *=======================================================================*/
1441
1442 PROCEDURE arapp_journal_load_xml (
1443 p_reporting_level IN VARCHAR2,
1444 p_reporting_entity_id IN NUMBER,
1445 p_sob_id IN NUMBER,
1446 p_coa_id IN NUMBER,
1447 p_co_seg_low IN VARCHAR2,
1448 p_co_seg_high IN VARCHAR2,
1449 p_gl_date_from IN VARCHAR2,
1450 p_gl_date_to IN VARCHAR2,
1451 p_posting_status IN VARCHAR2,
1452 p_gl_account_low IN VARCHAR2,
1453 p_gl_account_high IN VARCHAR2,
1454 p_summary_account IN NUMBER,
1455 p_receivable_mode IN VARCHAR2,
1456 p_result OUT NOCOPY CLOB) IS
1457 l_result CLOB;
1458 tempResult CLOB;
1459 l_version varchar2(20);
1460 l_compatibility varchar2(20);
1461 l_suffix varchar2(2);
1462 l_majorVersion number;
1463 l_resultOffset number;
1464 l_xml_header varchar2(3000);
1465 l_xml_header_length number;
1466 l_errNo NUMBER;
1467 l_errMsg VARCHAR2(200);
1468 queryCtx DBMS_XMLquery.ctxType;
1469 qryCtx DBMS_XMLGEN.ctxHandle;
1470 l_xml_query VARCHAR2(32767);
1471 l_natural_segment_col VARCHAR2(50);
1472 l_flex_value_set_id NUMBER;
1473 l_code_combinations VARCHAR2(32767);
1474 TYPE ref_cur IS REF CURSOR;
1475 l_xml_stmt ref_cur;
1476 l_rows_processed NUMBER;
1477 l_new_line VARCHAR2(1);
1478 l_coa_id NUMBER; /*bufg fix 5654975*/
1479 /* Variables to hold the report heading */
1480 l_sob_id NUMBER;
1481 l_sob_name VARCHAR2(100);
1482 l_functional_currency VARCHAR2(15);
1483 l_organization VARCHAR2(60);
1484 l_format VARCHAR2(40);
1485 l_close_tag VARCHAR2(100);
1486 l_reporting_entity_name VARCHAR2(80);
1487 l_reporting_level_name VARCHAR2(30);
1488 l_status_meaning VARCHAR2(30);
1489 l_receivable_mode_meaning VARCHAR2(10);
1490 /* Variables to hold the where clause based on the input parameters*/
1491 /* Variables length changed from 200 to 500 to address bug:5181586*/
1492 /* Increased variables length to 32767 for bug 5654975 */
1493 l_ra_org_where VARCHAR2(32767);
1494 l_ard_org_where VARCHAR2(32767);
1495 l_ard1_org_where VARCHAR2(32767);
1496 l_ps_org_where VARCHAR2(32767);
1497 l_cr_org_where VARCHAR2(32767);
1498 l_crh_org_where VARCHAR2(32767);
1499 l_bat_org_where VARCHAR2(32767);
1500 l_bs_org_where VARCHAR2(32767);
1501 l_sysparam_org_where VARCHAR2(32767);
1502 /* Changes to variable length ends*/
1503 l_co_seg_where VARCHAR2(32767);
1504 l_account_where VARCHAR2(32767);
1505 l_account_seg_where VARCHAR2(32767);
1506 l_gl_date_where VARCHAR2(1000);
1507 /* Variable length changed from 900 to 1000 to address bug:5181586*/
1508 l_source_type_where VARCHAR2(32767);
1509 /*Change to variable length ends*/
1510 l_posting_status_where VARCHAR2(1000);
1511 l_report_date VARCHAR2(25);
1512 l_ld_sp VARCHAR2(1) := 'Y';
1513 l_message VARCHAR2(2000);
1514 l_encoding VARCHAR2(20);
1515 l_message_acct VARCHAR2(1000);
1516 BEGIN
1517
1518 log('arapp_journal_load_xml (+)');
1519
1520 /* Assign the input parameters to the global variables */
1521 arp_recon_rep.var_tname.g_reporting_level := p_reporting_level;
1522 arp_recon_rep.var_tname.g_reporting_entity_id := p_reporting_entity_id;
1523 /* bug 5654975 p_coa_id,p_sob_id is passed incorrectly when the user
1524 has access to multiple Ledgers */
1525 -- arp_recon_rep.var_tname.g_set_of_books_id := p_sob_id;
1526 -- arp_recon_rep.var_tname.g_chart_of_accounts_id := p_coa_id;
1527 arp_recon_rep.var_tname.g_gl_date_from := fnd_date.canonical_to_date(p_gl_date_from);
1528 arp_recon_rep.var_tname.g_gl_date_to := fnd_date.canonical_to_date(p_gl_date_to);
1529 arp_recon_rep.var_tname.g_posting_status := p_posting_status;
1530
1531 /* Added Conditional Implication to address bug:5181586*/
1532 /*Added set of books id and char of accounts id for bug fix 5654975 */
1533 IF p_reporting_level = 1000 THEN
1534 SELECT sob.name sob_name,
1535 sob.set_of_books_id,
1536 sob.currency_code functional_currency,
1537 sob.chart_of_accounts_id
1538 INTO l_sob_name,
1539 l_sob_id,
1540 l_functional_currency,
1544
1541 l_coa_id
1542 FROM gl_sets_of_books sob
1543 WHERE sob.set_of_books_id = arp_recon_rep.var_tname.g_reporting_entity_id;
1545 ELSIF p_reporting_level = 3000 THEN
1546 SELECT sob.name sob_name,
1547 sob.set_of_books_id,
1548 sob.currency_code functional_currency,
1549 sob.chart_of_accounts_id
1550 INTO l_sob_name,
1551 l_sob_id,
1552 l_functional_currency,
1553 l_coa_id
1554 FROM gl_sets_of_books sob,
1555 ar_system_parameters_all sysparam
1556 WHERE sob.set_of_books_id = sysparam.set_of_books_id
1557 AND sysparam.org_id = arp_recon_rep.var_tname.g_reporting_entity_id;
1558
1559 END IF;
1560 /* Changes for bug:5181586 ends*/
1561
1562 arp_recon_rep.var_tname.g_set_of_books_id := l_sob_id;
1563 arp_recon_rep.var_tname.g_chart_of_accounts_id := l_coa_id;
1564
1565 /* Initialize the reporting context */
1566 init(p_sob_id);
1567
1568 /* Set the org conditions */
1569
1570 XLA_MO_REPORTING_API.Initialize(p_reporting_level, p_reporting_entity_id, 'AUTO');
1571
1572 l_ra_org_where := XLA_MO_REPORTING_API.Get_Predicate('ra',NULL);
1573 l_ard_org_where := XLA_MO_REPORTING_API.Get_Predicate('ard',NULL);
1574 l_ard1_org_where := XLA_MO_REPORTING_API.Get_Predicate('ard1',NULL);
1575 l_ps_org_where := XLA_MO_REPORTING_API.Get_Predicate('ps',NULL);
1576 l_cr_org_where := XLA_MO_REPORTING_API.Get_Predicate('cr',NULL);
1577 l_crh_org_where := XLA_MO_REPORTING_API.Get_Predicate('crh',NULL);
1578 l_bat_org_where := XLA_MO_REPORTING_API.Get_Predicate('bat',NULL);
1579 l_bs_org_where := XLA_MO_REPORTING_API.Get_Predicate('bs',NULL);
1580 l_sysparam_org_where := XLA_MO_REPORTING_API.Get_Predicate('sysparam',NULL);
1581
1582 /* Replace the bind variables with global functions */
1583 l_ra_org_where := replace(l_ra_org_where,
1584 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1585 l_ard_org_where := replace(l_ard_org_where,
1586 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1587 l_ard1_org_where := replace(l_ard1_org_where,
1588 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1589 l_ps_org_where := replace(l_ps_org_where,
1590 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1591 l_cr_org_where := replace(l_cr_org_where,
1592 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1593 l_crh_org_where := replace(l_crh_org_where,
1594 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1595 l_bat_org_where := replace(l_bat_org_where,
1596 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1597 l_bs_org_where := replace(l_bs_org_where,
1598 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1599 l_sysparam_org_where := replace(l_sysparam_org_where,
1600 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
1601
1602
1603 l_reporting_entity_name := substrb(XLA_MO_REPORTING_API.get_reporting_entity_name,1,80);
1604 l_reporting_level_name := substrb(XLA_MO_REPORTING_API.get_reporting_level_name,1,30);
1605
1606 /* Multi Org Uptake: Show appropriate message to the user depending upon the security profile */
1607 IF p_reporting_level = '1000' THEN
1608 l_ld_sp:= mo_utils.check_ledger_in_sp(p_reporting_entity_id);
1609 END IF;
1610
1611 IF l_ld_sp = 'N' THEN
1612 FND_MESSAGE.SET_NAME('FND','FND_MO_RPT_PARTIAL_LEDGER');
1613 l_message := FND_MESSAGE.get;
1614 END IF;
1615
1616 /* Bug fix 4942083*/
1617 IF arp_util.Open_Period_Exists(p_reporting_level,
1618 p_reporting_entity_id,
1619 arp_recon_rep.var_tname.g_gl_date_from,
1620 arp_recon_rep.var_tname.g_gl_date_to) THEN
1621 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
1622 l_message_acct := FND_MESSAGE.Get;
1623 END IF;
1624
1625 /* Get the org name */
1626 IF p_reporting_level = '3000' THEN
1627 select substrb(hou.name,1,60)
1628 into l_organization
1629 from hr_organization_units hou
1630 where hou.organization_id = arp_recon_rep.var_tname.g_reporting_entity_id;
1631 ELSE
1632 select meaning
1633 into l_organization
1634 from ar_lookups
1635 where lookup_code ='ALL' and lookup_type ='ALL';
1636 END IF;
1637
1638
1639 /* Build the WHERE clauses */
1640 /*Replaced p_coa_id with l_coa_id for bug 5654975*/
1641
1642 IF p_co_seg_low IS NULL AND p_co_seg_high IS NULL THEN
1643 l_co_seg_where := NULL;
1644 ELSIF p_co_seg_low IS NULL THEN
1645 l_co_seg_where := ' AND ' ||
1646 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
1650 p_mode => 'WHERE',
1647 p_id_flex_code => 'GL#',
1648 p_id_flex_num => l_coa_id,
1649 p_table_alias => 'GC',
1651 p_qualifier => 'GL_BALANCING',
1652 p_function => '<=',
1653 p_operand1 => p_co_seg_high);
1654 ELSIF p_co_seg_high IS NULL THEN
1655 l_co_seg_where := ' AND ' ||
1656 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
1657 p_id_flex_code => 'GL#',
1658 p_id_flex_num => l_coa_id,
1659 p_table_alias => 'GC',
1660 p_mode => 'WHERE',
1661 p_qualifier => 'GL_BALANCING',
1662 p_function => '>=',
1663 p_operand1 => p_co_seg_low);
1664 ELSE
1665 l_co_seg_where := ' AND ' ||
1666 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
1667 p_id_flex_code => 'GL#',
1668 p_id_flex_num => l_coa_id,
1669 p_table_alias => 'GC',
1670 p_mode => 'WHERE',
1671 p_qualifier => 'GL_BALANCING',
1672 p_function => 'BETWEEN',
1673 p_operand1 => p_co_seg_low,
1674 p_operand2 => p_co_seg_high);
1675 END IF;
1676
1677
1678 IF p_gl_date_from IS NULL and p_gl_date_to IS NULL THEN
1679 l_gl_date_where := NULL;
1680 ELSIF p_gl_date_from IS NULL THEN
1681 l_gl_date_where :=' and ra.gl_date <= arp_recon_rep.get_gl_date_to()';
1682 ELSIF p_gl_date_to IS NULL THEN
1683 l_gl_date_where :=' and ra.gl_date >= arp_recon_rep.get_gl_date_from() ' ;
1684 ELSE
1685 l_gl_date_where := ' and ra.gl_date between arp_recon_rep.get_gl_date_from() and arp_recon_rep.get_gl_date_to() ';
1686 END IF;
1687
1688 IF p_gl_account_low IS NOT NULL AND p_gl_account_high IS NOT NULL THEN
1689 l_account_where := ' AND ' || AR_CALC_AGING.FLEX_SQL(
1690 p_application_id=> 101,
1691 p_id_flex_code =>'GL#',
1692 p_id_flex_num =>l_coa_id,
1693 p_table_alias => 'gc',
1694 p_mode => 'WHERE',
1695 p_qualifier => 'ALL',
1696 p_function=> 'BETWEEN',
1697 p_operand1 => p_gl_account_low,
1698 p_operand2 => p_gl_account_high);
1699 ELSE
1700 l_account_where := NULL;
1701 END IF;
1702
1703 IF p_summary_account IS NOT NULL THEN
1704 SELECT fcav.application_column_name, flex_value_set_id
1705 INTO l_natural_segment_col , l_flex_value_set_id
1706 FROM fnd_segment_attribute_values fcav,
1707 fnd_id_flex_segments fifs
1708 WHERE fcav.application_id = 101
1709 AND fcav.id_flex_code = 'GL#'
1710 AND fcav.id_flex_num = arp_recon_rep.var_tname.g_chart_of_accounts_id
1711 AND fcav.attribute_value = 'Y'
1712 AND fcav.segment_attribute_type = 'GL_ACCOUNT'
1713 AND fifs.application_id = fcav.application_id
1714 AND fifs.id_flex_code = fcav.id_flex_code
1715 AND fifs.id_flex_num = fcav.id_flex_num
1716 AND fcav.application_column_name = fifs.application_column_name;
1717
1718 get_detail_accounts(l_flex_value_set_id, p_summary_account, l_code_combinations);
1719
1720 l_account_seg_where := ' and gc.'||l_natural_segment_col||' in ('||l_code_combinations||' )';
1721 ELSE
1722 l_account_seg_where := NULL;
1723 END IF;
1724
1725
1726 IF nvl(p_receivable_mode,'N') = 'Y' THEN
1727 l_source_type_where := ' and ((ard.source_type = ''REC'')
1728 OR (ps.class =''BR''
1729 and not exists (select line_id
1730 from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard1
1731 where ard1.source_id = ra.receivable_application_id
1732 and ard1.source_type = ''REC''
1733 and ard1.source_table =''RA''
1734 '|| l_ard1_org_where || ')
1735 and ard.source_type in (''REMITTANCE'',''FACTOR'',''UNPAIDREC'')))';
1736 select meaning
1737 into l_receivable_mode_meaning
1738 from fnd_lookups
1739 where lookup_type = 'YES_NO'
1740 and lookup_code = 'Y';
1741 ELSE
1742 l_source_type_where := NULL;
1743 select meaning
1744 into l_receivable_mode_meaning
1745 from fnd_lookups
1746 where lookup_type = 'YES_NO'
1747 and lookup_code = 'N';
1748 END IF;
1749
1750 IF p_posting_status IS NOT NULL THEN
1751
1752 select meaning
1753 into l_status_meaning
1754 from ar_lookups
1755 where lookup_type = 'POSTED_STATUS'
1756 and lookup_code = arp_recon_rep.var_tname.g_posting_status;
1757
1761 ''UNPOSTED'',TO_DATE(''01/01/0001'',''MM/DD/YYYY''),
1758 l_posting_status_where := 'and nvl(ra.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')) =
1759 decode(arp_recon_rep.get_posting_status(),
1760 ''POSTED'',ra.gl_posted_date,
1762 nvl(ra.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')))';
1763 ELSE
1764 l_status_meaning := NULL;
1765 l_posting_status_where := NULL;
1766 END IF;
1767
1768 l_xml_query := '
1769 select cr.receipt_number payment_number,
1770 arm.name payment_method,
1771 substrb(party.party_name,1,50) customer_name,
1772 cust.account_number customer_number,
1773 to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
1774 to_char(crh.gl_date,''YYYY-MM-DD'') payment_gl_date,
1775 nvl(ard.amount_dr,0) entered_debit,
1776 nvl(ard.amount_cr,0) entered_credit,
1777 nvl(ard.acctd_amount_dr,0) acctd_debit,
1778 nvl(ard.acctd_amount_cr,0) acctd_credit,
1779 to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
1780 cr.currency_code receipt_currency,
1781 ps.trx_number trx_number,
1782 gc.code_combination_id,
1783 bs.name receipt_source,
1784 bat.name batch_name,
1785 l_cat.meaning category,
1786 ard.currency_code currency_code,
1787 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
1788 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
1789 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
1790 from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
1791 ar_receipt_methods arm,
1792 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
1793 gl_code_combinations gc,
1794 hz_cust_accounts cust,
1795 hz_parties party,
1796 '||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
1797 ar_batch_sources_all bs,
1798 '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
1799 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
1800 '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
1801 ar_lookups l_cat
1802 where nvl(ra.confirmed_flag,''Y'') = ''Y''
1803 and ra.status = ''APP''
1804 and cr.cash_receipt_id = ra.cash_receipt_id
1805 and nvl(cr.confirmed_flag,''Y'') = ''Y''
1806 and cr.receipt_method_id = arm.receipt_method_id
1807 and crh.cash_receipt_id = cr.cash_receipt_id
1808 and crh.first_posted_record_flag = ''Y''
1809 and crh.batch_id = bat.batch_id(+)
1810 and ps.payment_schedule_id = ra.applied_payment_schedule_id
1811 and bat.batch_source_id = bs.batch_source_id(+)
1812 and bat.org_id = bs.org_id(+)
1813 and gc.code_combination_id = ard.code_combination_id
1814 and gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
1815 and ard.source_id = ra.receivable_application_id
1816 and ard.source_table = ''RA''
1817 and cr.pay_from_customer = cust.cust_account_id(+)
1818 and cust.party_id = party.party_id(+)
1819 and l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
1820 and ((ra.amount_applied_from IS NULL
1821 and l_cat.lookup_code = (''TRADE_''||ard.source_type))
1822 or( ra.amount_applied_from IS NOT NULL
1823 and l_cat.lookup_code = (''CCURR_''||ard.source_type))
1824 or(ps.class =''BR'' and l_cat.lookup_code = (''BR_''||ard.source_type)))
1825 '||l_ra_org_where||'
1826 '||l_ard_org_where||'
1827 '||l_ps_org_where||'
1828 '||l_cr_org_where||'
1829 '||l_crh_org_where||'
1830 '||l_bat_org_where||'
1831 '||l_bs_org_where||'
1832 '||l_gl_date_where ||'
1833 '||l_co_seg_where ||'
1834 '||l_account_where ||'
1835 '||l_account_seg_where ||'
1836 '||l_source_type_where||'
1837 '||l_posting_status_where;
1838
1839 DBMS_UTILITY.DB_VERSION(l_version, l_compatibility);
1840 l_majorVersion := to_number(substr(l_version, 1, instr(l_version,'.')-1));
1841
1842 IF (l_majorVersion > 8 and l_majorVersion < 9) THEN
1843 BEGIN
1844 queryCtx := DBMS_XMLQuery.newContext(l_xml_query);
1845 DBMS_XMLQuery.setRaiseNoRowsException(queryCtx,TRUE);
1849 EXCEPTION WHEN OTHERS THEN
1846 l_result := DBMS_XMLQuery.getXML(queryCtx);
1847 DBMS_XMLQuery.closeContext(queryCtx);
1848 l_rows_processed := 1;
1850 DBMS_XMLQuery.getExceptionContent(queryCtx,l_errNo,l_errMsg);
1851 IF l_errNo = 1403 THEN
1852 l_rows_processed := 0;
1853 END IF;
1854 DBMS_XMLQuery.closeContext(queryCtx);
1855 END;
1856 ELSIF (l_majorVersion >= 9 ) THEN
1857 qryCtx := DBMS_XMLGEN.newContext(l_xml_query);
1858 l_result := DBMS_XMLGEN.getXML(qryCtx,DBMS_XMLGEN.NONE);
1859 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
1860 DBMS_XMLGEN.closeContext(qryCtx);
1861 END IF;
1862
1863 IF l_rows_processed <> 0 THEN
1864 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
1865 tempResult := l_result;
1866 ELSE
1867 l_resultOffset := 0;
1868 END IF;
1869
1870 l_new_line := '
1871 ';
1872
1873 select to_char(sysdate,'YYYY-MM-DD')
1874 into l_report_date
1875 from dual;
1876
1877 /* Bug 4708930
1878 Get the special characters replaced */
1879 l_reporting_entity_name := format_string(l_reporting_entity_name);
1880 l_reporting_level_name := format_string(l_reporting_level_name);
1881 l_organization := format_string(l_organization);
1882 l_receivable_mode_meaning := format_string(l_receivable_mode_meaning);
1883 l_status_meaning := format_string(l_status_meaning);
1884 l_sob_name := format_string(l_sob_name);
1885 l_message := format_string(l_message);
1886 l_message_acct := format_string(l_message_acct);
1887
1888 /* Prepare the tag for the report heading */
1889 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
1890 l_xml_header := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
1891 l_xml_header := l_xml_header ||l_new_line||'<ARAPPJOURNAL>';
1892 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT>'||l_message||'</MSG_TXT>';
1893 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT_ACCT>'||l_message_acct||'</MSG_TXT_ACCT>';
1894 l_xml_header := l_xml_header ||l_new_line||' <PARAMETERS>';
1895 l_xml_header := l_xml_header ||l_new_line||' <REPORT_DATE>'||l_report_date||'</REPORT_DATE>';
1896 l_xml_header := l_xml_header ||l_new_line||' <REPORTING_LEVEL>'||l_reporting_level_name||'</REPORTING_LEVEL>';
1897 l_xml_header := l_xml_header ||l_new_line||' <REPORTING_ENTITY>'||l_reporting_entity_name||'</REPORTING_ENTITY>';
1898 l_xml_header := l_xml_header ||l_new_line||' <SOB_ID>'||p_sob_id||'</SOB_ID>';
1899 l_xml_header := l_xml_header ||l_new_line||' <CO_SEG_LOW>'||p_co_seg_low||'</CO_SEG_LOW>';
1900 l_xml_header := l_xml_header ||l_new_line||' <CO_SEG_HIGH>'||p_co_seg_high||'</CO_SEG_HIGH>';
1901 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>';
1902 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>';
1903 l_xml_header := l_xml_header ||l_new_line||' <POSTING_STATUS>'||l_status_meaning||'</POSTING_STATUS>';
1904 l_xml_header := l_xml_header ||l_new_line||' <GL_ACCOUNT_LOW>'||p_gl_account_low||'</GL_ACCOUNT_LOW>';
1905 l_xml_header := l_xml_header ||l_new_line||' <GL_ACCOUNT_HIGH>'||p_gl_account_high||'</GL_ACCOUNT_HIGH>';
1906 l_xml_header := l_xml_header ||l_new_line||' <SUMMARY_ACCOUNT>'||p_summary_account||'</SUMMARY_ACCOUNT>';
1907 l_xml_header := l_xml_header ||l_new_line||' <REC_MODE_ONLY>'||l_receivable_mode_meaning||'</REC_MODE_ONLY>';
1908 l_xml_header := l_xml_header ||l_new_line||' <NUM_ROWS>'||l_rows_processed||'</NUM_ROWS>';
1909 l_xml_header := l_xml_header ||l_new_line||' </PARAMETERS>';
1910 l_xml_header := l_xml_header ||l_new_line||' <REPORT_HEADING>';
1911 l_xml_header := l_xml_header ||l_new_line||' <SET_OF_BOOKS>'||l_sob_name||'</SET_OF_BOOKS>';
1912 l_xml_header := l_xml_header ||l_new_line||' <ORGANIZATION>'||l_organization||'</ORGANIZATION>';
1913 l_xml_header := l_xml_header ||l_new_line||' <FUNCTIONAL_CURRENCY>'||l_functional_currency||'</FUNCTIONAL_CURRENCY>';
1914 l_xml_header := l_xml_header ||l_new_line||' </REPORT_HEADING>';
1915
1916 l_close_tag := l_new_line||'</ARAPPJOURNAL>'||l_new_line;
1917 l_xml_header_length := length(l_xml_header);
1918 IF l_rows_processed <> 0 THEN
1919 dbms_lob.write(tempResult,l_xml_header_length,1,l_xml_header);
1920 dbms_lob.copy(tempResult,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
1921 l_xml_header_length,l_resultOffset);
1922 ELSE
1923 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
1924 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
1925 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
1926 END IF;
1927
1928 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
1929
1930 ar_cumulative_balance_report.process_clob(tempResult);
1931 p_result := tempResult;
1932
1933 log('arapp_journal_load_xml (-)');
1934
1935 END arapp_journal_load_xml;
1936
1937 /*========================================================================+
1938 | PUBLIC PROCEDURE ARCM_JOURNAL_LOAD_XML |
1939 | |
1940 | DESCRIPTION |
1944 | |
1941 | |
1942 | This procedure is used to generate the XML data required for reporting|
1943 | ON Account Credit Memo Gain or Loss Journals |
1945 | PSEUDO CODE/LOGIC |
1946 | |
1947 | PARAMETERS |
1948 | |
1949 | |
1950 | KNOWN ISSUES |
1951 | |
1952 | NOTES |
1953 | |
1954 | |
1955 | MODIFICATION HISTORY |
1956 | Date Author Description of Changes |
1957 | 03-FEB-2004 rkader Created |
1958 | |
1959 *=======================================================================*/
1960 PROCEDURE arcm_journal_load_xml (
1961 p_reporting_level IN VARCHAR2,
1962 p_reporting_entity_id IN NUMBER,
1963 p_sob_id IN NUMBER,
1964 p_coa_id IN NUMBER,
1965 p_co_seg_low IN VARCHAR2,
1966 p_co_seg_high IN VARCHAR2,
1967 p_gl_date_from IN VARCHAR2,
1968 p_gl_date_to IN VARCHAR2,
1969 p_posting_status IN VARCHAR2,
1970 p_gl_account_low IN VARCHAR2,
1971 p_gl_account_high IN VARCHAR2,
1972 p_summary_account IN NUMBER,
1973 p_receivable_mode IN VARCHAR2,
1974 p_result OUT NOCOPY CLOB) IS
1975 l_result CLOB;
1976 tempResult CLOB;
1977 l_version varchar2(20);
1978 l_compatibility varchar2(20);
1979 l_suffix varchar2(2);
1980 l_majorVersion number;
1981 l_resultOffset number;
1982 l_xml_header varchar2(3000);
1983 l_xml_header_length number;
1984 l_errNo NUMBER;
1985 l_errMsg VARCHAR2(200);
1986 queryCtx DBMS_XMLquery.ctxType;
1987 qryCtx DBMS_XMLGEN.ctxHandle;
1988 l_xml_query VARCHAR2(32767);
1989 l_natural_segment_col VARCHAR2(50);
1990 l_flex_value_set_id NUMBER;
1991 l_code_combinations VARCHAR2(32767);
1992 TYPE ref_cur IS REF CURSOR;
1993 l_xml_stmt ref_cur;
1994 l_rows_processed NUMBER;
1995 l_new_line VARCHAR2(1) ;
1996 l_coa_id NUMBER; /*bug fix 5654975*/
1997 /* Variables to hold the report heading */
1998 l_sob_id NUMBER;
1999 l_sob_name VARCHAR2(100);
2000 l_functional_currency VARCHAR2(15);
2001 l_organization VARCHAR2(60);
2002 l_format VARCHAR2(40);
2003 l_close_tag VARCHAR2(100);
2004 l_reporting_entity_name VARCHAR2(80);
2005 l_reporting_level_name VARCHAR2(30);
2006 l_status_meaning VARCHAR2(30);
2007 l_receivable_mode_meaning VARCHAR2(10);
2008 /* Variables to hold the where clause based on the input parameters*/
2009 /* Variables length changed from 200 to 500 to address bug:5181586*/
2010 /* Increased variables length to 32767 for bug 5654975 */
2011 l_ard_org_where VARCHAR2(32767);
2012 l_ps_org_where VARCHAR2(32767);
2013 l_ps1_org_where VARCHAR2(32767);
2014 l_ra_org_where VARCHAR2(32767);
2015 l_sysparam_org_where VARCHAR2(32767);
2016 /* Changes to variable length ends*/
2017 l_co_seg_where VARCHAR2(32767);
2018 l_account_where VARCHAR2(32767);
2019 l_account_seg_where VARCHAR2(32767);
2020 l_gl_date_where VARCHAR2(1000);
2021 l_source_type_where VARCHAR2(32767);
2022 l_posting_status_where VARCHAR2(1000);
2023 l_report_date VARCHAR2(25);
2024 l_ld_sp VARCHAR2(1) := 'Y';
2025 l_message VARCHAR2(2000);
2026 l_message_acct VARCHAR2(1000);
2027 l_encoding VARCHAR2(20);
2028 BEGIN
2029
2030 log('arcm_journal_load_xml (+)');
2031
2032 /* Assign the input parameters to the global variables */
2033 arp_recon_rep.var_tname.g_reporting_level := p_reporting_level;
2034 arp_recon_rep.var_tname.g_reporting_entity_id := p_reporting_entity_id;
2035 /* bug 5654975 p_coa_id,p_sob_id is passed incorrectly when the user
2036 has access to multiple Ledgers */
2037 -- arp_recon_rep.var_tname.g_set_of_books_id := p_sob_id;
2038 -- arp_recon_rep.var_tname.g_chart_of_accounts_id := p_coa_id;
2039 arp_recon_rep.var_tname.g_gl_date_from := fnd_date.canonical_to_date(p_gl_date_from);
2040 arp_recon_rep.var_tname.g_gl_date_to := fnd_date.canonical_to_date(p_gl_date_to);
2044 /* Added set of books id and chart of accounts id for bug fix 565497*/
2041 arp_recon_rep.var_tname.g_posting_status := p_posting_status;
2042
2043 /* Added Conditional Implication to address bug:5181586*/
2045 IF p_reporting_level = 1000 THEN
2046 SELECT sob.name sob_name,
2047 sob.set_of_books_id,
2048 sob.currency_code functional_currency,
2049 sob.chart_of_accounts_id
2050 INTO l_sob_name,
2051 l_sob_id,
2052 l_functional_currency,
2053 l_coa_id
2054 FROM gl_sets_of_books sob
2055 WHERE sob.set_of_books_id = arp_recon_rep.var_tname.g_reporting_entity_id;
2056
2057 ELSIF p_reporting_level = 3000 THEN
2058 SELECT sob.name sob_name,
2059 sob.set_of_books_id,
2060 sob.currency_code functional_currency,
2061 sob.chart_of_accounts_id
2062 INTO l_sob_name,
2063 l_sob_id,
2064 l_functional_currency,
2065 l_coa_id
2066 FROM gl_sets_of_books sob,
2067 ar_system_parameters_all sysparam
2068 WHERE sob.set_of_books_id = sysparam.set_of_books_id
2069 AND sysparam.org_id = arp_recon_rep.var_tname.g_reporting_entity_id;
2070
2071 END IF;
2072 /* Changes for bug:5181586 ends*/
2073 arp_recon_rep.var_tname.g_set_of_books_id := l_sob_id;
2074 arp_recon_rep.var_tname.g_chart_of_accounts_id := l_coa_id;
2075
2076
2077 /* Initialize the reporting context */
2078 init(p_sob_id);
2079
2080 /* Set the org conditions */
2081
2082 XLA_MO_REPORTING_API.Initialize(p_reporting_level, p_reporting_entity_id, 'AUTO');
2083
2084 l_ra_org_where := XLA_MO_REPORTING_API.Get_Predicate('ra',NULL);
2085 l_ard_org_where := XLA_MO_REPORTING_API.Get_Predicate('ard',NULL);
2086 l_ps_org_where := XLA_MO_REPORTING_API.Get_Predicate('ps',NULL);
2087 l_ps1_org_where := XLA_MO_REPORTING_API.Get_Predicate('ps1',NULL);
2088 l_sysparam_org_where := XLA_MO_REPORTING_API.Get_Predicate('sysparam',NULL);
2089
2090
2091 /* Replace the bind variables with global functions */
2092 l_ra_org_where := replace(l_ra_org_where,
2093 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2094 l_ard_org_where := replace(l_ard_org_where,
2095 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2096 l_ps_org_where := replace(l_ps_org_where,
2097 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2098 l_ps1_org_where := replace(l_ps1_org_where,
2099 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2100 l_sysparam_org_where := replace(l_sysparam_org_where,
2101 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2102 l_reporting_entity_name := substrb(XLA_MO_REPORTING_API.get_reporting_entity_name,1,80);
2103 l_reporting_level_name := substrb(XLA_MO_REPORTING_API.get_reporting_level_name,1,30);
2104
2105 /* Multi Org Uptake: Show appropriate message to the user depending upon the security profile */
2106 IF p_reporting_level = '1000' THEN
2107 l_ld_sp:= mo_utils.check_ledger_in_sp(p_reporting_entity_id);
2108 END IF;
2109
2110 IF l_ld_sp = 'N' THEN
2111 FND_MESSAGE.SET_NAME('FND','FND_MO_RPT_PARTIAL_LEDGER');
2112 l_message := FND_MESSAGE.get;
2113 END IF;
2114
2115 /* Bug fix 4942083*/
2116 IF arp_util.Open_Period_Exists(p_reporting_level,
2117 p_reporting_entity_id,
2118 arp_recon_rep.var_tname.g_gl_date_from,
2119 arp_recon_rep.var_tname.g_gl_date_to) THEN
2120 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
2121 l_message_acct := FND_MESSAGE.Get;
2122 END IF;
2123
2124 IF p_reporting_level = '3000' THEN
2125 select substrb(hou.name,1,60)
2126 into l_organization
2127 from hr_organization_units hou
2128 where hou.organization_id = arp_recon_rep.var_tname.g_reporting_entity_id;
2129 ELSE
2130 select meaning
2131 into l_organization
2132 from ar_lookups
2133 where lookup_code ='ALL' and lookup_type ='ALL';
2134 END IF;
2135
2136 /* Build the WHERE clauses */
2137 /*Replaced p_coa_id with l_coa_id for bug 5654975 */
2138
2139 IF p_co_seg_low IS NULL AND p_co_seg_high IS NULL THEN
2140 l_co_seg_where := NULL;
2141 ELSIF p_co_seg_low IS NULL THEN
2142 l_co_seg_where := ' AND ' ||
2143 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
2144 p_id_flex_code => 'GL#',
2145 p_id_flex_num => l_coa_id,
2146 p_table_alias => 'GC',
2147 p_mode => 'WHERE',
2148 p_qualifier => 'GL_BALANCING',
2149 p_function => '<=',
2150 p_operand1 => p_co_seg_high);
2151 ELSIF p_co_seg_high IS NULL THEN
2152 l_co_seg_where := ' AND ' ||
2153 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
2157 p_mode => 'WHERE',
2154 p_id_flex_code => 'GL#',
2155 p_id_flex_num => l_coa_id,
2156 p_table_alias => 'GC',
2158 p_qualifier => 'GL_BALANCING',
2159 p_function => '>=',
2160 p_operand1 => p_co_seg_low);
2161 ELSE
2162 l_co_seg_where := ' AND ' ||
2163 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
2164 p_id_flex_code => 'GL#',
2165 p_id_flex_num => l_coa_id,
2166 p_table_alias => 'GC',
2167 p_mode => 'WHERE',
2168 p_qualifier => 'GL_BALANCING',
2169 p_function => 'BETWEEN',
2170 p_operand1 => p_co_seg_low,
2171 p_operand2 => p_co_seg_high);
2172 END IF;
2173
2174 IF p_gl_date_from IS NULL and p_gl_date_to IS NULL THEN
2175 l_gl_date_where := NULL;
2176 ELSIF p_gl_date_from IS NULL THEN
2177 l_gl_date_where :=' and ra.gl_date <= arp_recon_rep.get_gl_date_to()';
2178 ELSIF p_gl_date_to IS NULL THEN
2179 l_gl_date_where :=' and ra.gl_date >= arp_recon_rep.get_gl_date_from() ' ;
2180 ELSE
2181 l_gl_date_where := ' and ra.gl_date between arp_recon_rep.get_gl_date_from() and arp_recon_rep.get_gl_date_to() ';
2182 END IF;
2183
2184 IF p_gl_account_low IS NOT NULL AND p_gl_account_high IS NOT NULL THEN
2185 l_account_where := ' AND ' || AR_CALC_AGING.FLEX_SQL(
2186 p_application_id=> 101,
2187 p_id_flex_code =>'GL#',
2188 p_id_flex_num =>l_coa_id,
2189 p_table_alias => 'gc',
2190 p_mode => 'WHERE',
2191 p_qualifier => 'ALL',
2192 p_function=> 'BETWEEN',
2193 p_operand1 => p_gl_account_low,
2194 p_operand2 => p_gl_account_high);
2195 ELSE
2196 l_account_where := NULL;
2197 END IF;
2198
2199 IF p_summary_account IS NOT NULL THEN
2200 SELECT fcav.application_column_name, flex_value_set_id
2201 INTO l_natural_segment_col , l_flex_value_set_id
2202 FROM fnd_segment_attribute_values fcav,
2203 fnd_id_flex_segments fifs
2204 WHERE fcav.application_id = 101
2205 AND fcav.id_flex_code = 'GL#'
2206 AND fcav.id_flex_num = arp_recon_rep.var_tname.g_chart_of_accounts_id
2207 AND fcav.attribute_value = 'Y'
2208 AND fcav.segment_attribute_type = 'GL_ACCOUNT'
2209 AND fifs.application_id = fcav.application_id
2210 AND fifs.id_flex_code = fcav.id_flex_code
2211 AND fifs.id_flex_num = fcav.id_flex_num
2212 AND fcav.application_column_name = fifs.application_column_name;
2213
2214 get_detail_accounts(l_flex_value_set_id, p_summary_account, l_code_combinations);
2215
2216 l_account_seg_where := ' and gc.'||l_natural_segment_col||' in ('||l_code_combinations||' )';
2217 ELSE
2218 l_account_seg_where := NULL;
2219 END IF;
2220
2221 IF nvl(p_receivable_mode,'N') = 'Y' THEN
2222 l_source_type_where := ' and ard.source_type in(''EXCH_GAIN'', ''EXCH_LOSS'')';
2223 select meaning
2224 into l_receivable_mode_meaning
2225 from fnd_lookups
2226 where lookup_type = 'YES_NO'
2227 and lookup_code = 'Y';
2228 ELSE
2229 l_source_type_where := NULL;
2230 select meaning
2231 into l_receivable_mode_meaning
2232 from fnd_lookups
2233 where lookup_type = 'YES_NO'
2234 and lookup_code = 'N';
2235 END IF;
2236
2237 IF p_posting_status IS NOT NULL THEN
2238 select meaning
2239 into l_status_meaning
2240 from ar_lookups
2241 where lookup_type = 'POSTED_STATUS'
2242 and lookup_code = arp_recon_rep.var_tname.g_posting_status;
2243
2244 l_posting_status_where := 'and nvl(ra.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')) =
2245 decode(arp_recon_rep.get_posting_status(),
2246 ''POSTED'',ra.gl_posted_date,
2247 ''UNPOSTED'',TO_DATE(''01/01/0001'',''MM/DD/YYYY''),
2248 nvl(ra.gl_posted_date,TO_DATE(''01/01/0001'',''MM/DD/YYYY'')))';
2249 ELSE
2250 l_status_meaning := NULL;
2251 l_posting_status_where := NULL;
2252 END IF;
2253
2254 l_xml_query := '
2255 select substrb(party.party_name,1,50) customer_name,
2256 cust.account_number customer_number,
2257 ps.trx_number cm_number,
2258 ps1.trx_number trx_number,
2259 nvl(ard.amount_dr,0) entered_debit,
2260 nvl(ard.amount_cr,0) entered_credit,
2261 nvl(ard.acctd_amount_dr,0) acctd_debit,
2262 nvl(ard.acctd_amount_cr,0) acctd_credit,
2266 to_char(ps.gl_date,''YYYY-MM-DD'') cm_gl_date,
2263 to_char(ps.trx_date,''YYYY-MM-DD'') cm_date,
2264 to_char(ps1.trx_date,''YYYY-MM-DD'') trx_date,
2265 to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
2267 to_char(ps1.gl_date,''YYYY-MM-DD'') trx_gl_date,
2268 ps.invoice_currency_code cm_currency_code,
2269 ps1.invoice_currency_code trx_currency_code,
2270 to_char(ps.exchange_date,''YYYY-MM-DD'') cm_exchange_date,
2271 to_char(ps1.exchange_date,''YYYY-MM-DD'') trx_exchange_date,
2272 ps.exchange_rate cm_exchange_rate,
2273 ps1.exchange_rate trx_exchange_rate,
2274 l_cat.meaning category,
2275 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
2276 ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
2277 ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
2278 from '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra ,
2279 '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard ,
2280 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps ,
2281 '||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps1 ,
2282 gl_code_combinations gc,
2283 hz_cust_accounts cust,
2284 hz_parties party,
2285 ar_lookups l_cat
2286 where nvl(ra.confirmed_flag,''Y'') = ''Y''
2287 and ra.application_type = ''CM''
2288 and ra.status = ''APP''
2289 and ard.source_table = ''RA''
2290 and ard.source_id = ra.receivable_application_id
2291 and ra.payment_schedule_id = ps.payment_schedule_id
2292 and ra.applied_payment_schedule_id = ps1.payment_schedule_id
2293 and cust.cust_account_id = ps.customer_id
2294 and cust.party_id = party.party_id
2295 and gc.code_combination_id = ard.code_combination_id
2296 and gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
2297 and l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
2298 and l_cat.lookup_code = (''CMAPP_''||ard.source_type)
2299 '||l_ard_org_where||'
2300 '||l_ra_org_where||'
2301 '||l_ps_org_where||'
2302 '||l_ps1_org_where||'
2303 '||l_gl_date_where ||'
2304 '||l_co_seg_where ||'
2305 '||l_account_where ||'
2306 '||l_account_seg_where ||'
2307 '||l_source_type_where||'
2308 '||l_posting_status_where||'
2309 order by company, category, account,app_gl_date, cm_number' ;
2310
2311 DBMS_UTILITY.DB_VERSION(l_version, l_compatibility);
2312 l_majorVersion := to_number(substr(l_version, 1, instr(l_version,'.')-1));
2313
2314 IF (l_majorVersion > 8 and l_majorVersion < 9) THEN
2315 BEGIN
2316 queryCtx := DBMS_XMLQuery.newContext(l_xml_query);
2317 DBMS_XMLQuery.setRaiseNoRowsException(queryCtx,TRUE);
2318 l_result := DBMS_XMLQuery.getXML(queryCtx);
2319 DBMS_XMLQuery.closeContext(queryCtx);
2320 l_rows_processed := 1;
2321 EXCEPTION WHEN OTHERS THEN
2322 DBMS_XMLQuery.getExceptionContent(queryCtx,l_errNo,l_errMsg);
2323 IF l_errNo = 1403 THEN
2324 l_rows_processed := 0;
2325 END IF;
2326 DBMS_XMLQuery.closeContext(queryCtx);
2327 END;
2328 ELSIF (l_majorVersion >= 9 ) THEN
2329 qryCtx := DBMS_XMLGEN.newContext(l_xml_query);
2330 l_result := DBMS_XMLGEN.getXML(qryCtx,DBMS_XMLGEN.NONE);
2331 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
2332 DBMS_XMLGEN.closeContext(qryCtx);
2333 END IF;
2334 IF l_rows_processed <> 0 THEN
2335 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
2336 tempResult := l_result;
2337 ELSE
2338 l_resultOffset := 0;
2339 END IF;
2340
2341 l_new_line := '
2342 ';
2343
2344 select to_char(sysdate,'YYYY-MM-DD')
2345 into l_report_date
2346 from dual;
2347
2348 /* Bug 4708930
2349 Get the special characters replaced */
2350 l_reporting_entity_name := format_string(l_reporting_entity_name);
2351 l_reporting_level_name := format_string(l_reporting_level_name);
2352 l_organization := format_string(l_organization);
2353 l_receivable_mode_meaning := format_string(l_receivable_mode_meaning);
2354 l_status_meaning := format_string(l_status_meaning);
2355 l_sob_name := format_string(l_sob_name);
2356 l_message := format_string(l_message);
2357 l_message_acct := format_string(l_message_acct);
2358
2359 /* Prepare the tag for the report heading */
2360 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
2361 l_xml_header := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
2362 l_xml_header := l_xml_header ||l_new_line||'<ARCMJOURNAL>';
2366 l_xml_header := l_xml_header ||l_new_line||' <REPORT_DATE>'||l_report_date||'</REPORT_DATE>';
2363 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT>'||l_message||'</MSG_TXT>';
2364 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT_ACCT>'||l_message_acct||'</MSG_TXT_ACCT>';
2365 l_xml_header := l_xml_header ||l_new_line||' <PARAMETERS>';
2367 l_xml_header := l_xml_header ||l_new_line||' <REPORTING_LEVEL>'||l_reporting_level_name||'</REPORTING_LEVEL>';
2368 l_xml_header := l_xml_header ||l_new_line||' <REPORTING_ENTITY>'||l_reporting_entity_name||'</REPORTING_ENTITY>';
2369 l_xml_header := l_xml_header ||l_new_line||' <SOB_ID>'||p_sob_id||'</SOB_ID>';
2370 l_xml_header := l_xml_header ||l_new_line||' <CO_SEG_LOW>'||p_co_seg_low||'</CO_SEG_LOW>';
2371 l_xml_header := l_xml_header ||l_new_line||' <CO_SEG_HIGH>'||p_co_seg_high||'</CO_SEG_HIGH>';
2372 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>';
2373 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>';
2374 l_xml_header := l_xml_header ||l_new_line||' <POSTING_STATUS>'||l_status_meaning||'</POSTING_STATUS>';
2375 l_xml_header := l_xml_header ||l_new_line||' <GL_ACCOUNT_LOW>'||p_gl_account_low||'</GL_ACCOUNT_LOW>';
2376 l_xml_header := l_xml_header ||l_new_line||' <GL_ACCOUNT_HIGH>'||p_gl_account_high||'</GL_ACCOUNT_HIGH>';
2377 l_xml_header := l_xml_header ||l_new_line||' <SUMMARY_ACCOUNT>'||p_summary_account||'</SUMMARY_ACCOUNT>';
2378 l_xml_header := l_xml_header ||l_new_line||' <REC_MODE_ONLY>'||l_receivable_mode_meaning||'</REC_MODE_ONLY>';
2379 l_xml_header := l_xml_header ||l_new_line||' <NUM_ROWS>'||l_rows_processed||'</NUM_ROWS>';
2380 l_xml_header := l_xml_header ||l_new_line||' </PARAMETERS>';
2381 l_xml_header := l_xml_header ||l_new_line||' <REPORT_HEADING>';
2382 l_xml_header := l_xml_header ||l_new_line||' <SET_OF_BOOKS>'||l_sob_name||'</SET_OF_BOOKS>';
2383 l_xml_header := l_xml_header ||l_new_line||' <ORGANIZATION>'||l_organization||'</ORGANIZATION>';
2384 l_xml_header := l_xml_header ||l_new_line||' <FUNCTIONAL_CURRENCY>'||l_functional_currency||'</FUNCTIONAL_CURRENCY>';
2385 l_xml_header := l_xml_header ||l_new_line||' </REPORT_HEADING>';
2386
2387 l_close_tag := l_new_line||'</ARCMJOURNAL>'||l_new_line;
2388 l_xml_header_length := length(l_xml_header);
2389 IF l_rows_processed <> 0 THEN
2390 dbms_lob.write(tempResult,l_xml_header_length,1,l_xml_header);
2391 dbms_lob.copy(tempResult,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
2392 l_xml_header_length,l_resultOffset);
2393 ELSE
2394 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
2395 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
2396 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
2397 END IF;
2398
2399 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
2400
2401 ar_cumulative_balance_report.process_clob(tempResult);
2402 p_result := tempResult;
2403
2404 log('arcm_journal_load_xml (-)');
2405 END arcm_journal_load_xml;
2406
2407
2408 /*========================================================================+
2409 | PUBLIC PROCEDURE ARGLRECON_LOAD_XML |
2410 | |
2411 | DESCRIPTION |
2412 | |
2413 | This procedure is used to generate the XML data required for |
2414 | creating the AR to GL Reconciliation Report |
2415 | |
2416 | PSEUDO CODE/LOGIC |
2417 | |
2418 | PARAMETERS |
2419 | |
2420 | |
2421 | KNOWN ISSUES |
2422 | |
2423 | NOTES |
2424 | |
2425 | |
2426 | MODIFICATION HISTORY |
2427 | Date Author Description of Changes |
2428 | 03-FEB-2004 rkader Created |
2429 | |
2430 *=======================================================================*/
2431
2432 PROCEDURE arglrecon_load_xml(
2433 p_reporting_level IN VARCHAR2,
2434 p_reporting_entity_id IN NUMBER,
2435 p_sob_id IN NUMBER,
2436 p_coa_id IN NUMBER,
2437 p_out_of_balance_only IN VARCHAR2,
2438 p_co_seg_low IN VARCHAR2,
2439 p_co_seg_high IN VARCHAR2,
2440 p_period_name IN VARCHAR2,
2441 p_gl_account_low IN VARCHAR2,
2442 p_gl_account_high IN VARCHAR2,
2443 p_summary_account IN VARCHAR2,
2447 l_gl_date_to date;
2444 p_result OUT NOCOPY CLOB) IS
2445
2446 l_gl_date_from date;
2448 l_result CLOB;
2449 tempResult CLOB;
2450 l_version varchar2(20);
2451 l_compatibility varchar2(20);
2452 l_suffix varchar2(2);
2453 l_majorVersion number;
2454 l_resultOffset number;
2455 l_rows_processed number;
2456 l_xml_header varchar2(1000);
2457 l_xml_header_length number;
2458 queryCtx DBMS_XMLquery.ctxType;
2459 qryCtx DBMS_XMLGEN.ctxHandle;
2460 l_xml_query VARCHAR2(32767);
2461 l_natural_segment_col VARCHAR2(50);
2462 l_flex_value_set_id NUMBER;
2463 l_code_combinations VARCHAR2(32767);
2464 l_new_line VARCHAR2(1);
2465 /* Variables to hold the report heading */
2466 l_sob_id NUMBER;
2467 l_sob_name VARCHAR2(100);
2468 l_functional_currency VARCHAR2(15);
2469 l_organization VARCHAR2(60);
2470 l_format VARCHAR2(40);
2471 l_close_tag VARCHAR2(100);
2472 l_reporting_entity_name VARCHAR2(80);
2473 l_reporting_level_name VARCHAR2(30);
2474 l_errNo NUMBER;
2475 l_errMsg VARCHAR2(200);
2476 /* Variables to hold the where clause based on the input parameters*/
2477 /* Variables length changed from 200 to 500 to address bug:5181586*/
2478 /* Increased variables length to 32767 for bug 5654975 */
2479 l_ra_org_where VARCHAR2(32767);
2480 l_crh_org_where VARCHAR2(32767);
2481 l_cr_org_where VARCHAR2(32767);/* Bug fix 6432847 */
2482 l_gl_dist_org_where VARCHAR2(32767);
2483 l_mcd_org_where VARCHAR2(32767);
2484 l_ard_org_where VARCHAR2(32767);
2485 l_adj_org_where VARCHAR2(32767);
2486 l_ath_org_where VARCHAR2(32767);
2487 l_sysparam_org_where VARCHAR2(32767);
2488 /* Changes to variable length ends*/
2489 l_co_seg_where VARCHAR2(32767);
2490 l_account_where VARCHAR2(32767);
2491 l_account_seg_where VARCHAR2(32767);
2492 l_report_date VARCHAR2(25);
2493 l_encoding VARCHAR2(20);
2494 l_message_acct VARCHAR2(1000);
2495 BEGIN
2496
2497 log('arglrecon_load_xml(+)');
2498
2499 /* Assign the input parameters to the global variables */
2500 /* AR to GL Reconciliation Report can be run only for the Set of Books
2501 So hard coding the reporting_level and context */
2502
2503 arp_recon_rep.var_tname.g_reporting_level := 1000;
2504 arp_recon_rep.var_tname.g_reporting_entity_id := p_reporting_entity_id;
2505 arp_recon_rep.var_tname.g_set_of_books_id := p_reporting_entity_id;
2506 arp_recon_rep.var_tname.g_chart_of_accounts_id := p_coa_id;
2507 arp_recon_rep.var_tname.g_period_name := p_period_name;
2508 arp_recon_rep.var_tname.g_out_of_balance_only := p_out_of_balance_only;
2509
2510 /* Initialize the reporting context */
2511 init(p_sob_id);
2512
2513 /* Set the org conditions */
2514
2515 XLA_MO_REPORTING_API.Initialize(1000,p_reporting_entity_id, 'AUTO');
2516
2517 l_ra_org_where := XLA_MO_REPORTING_API.Get_Predicate('ra',NULL);
2518 l_adj_org_where := XLA_MO_REPORTING_API.Get_Predicate('adj',NULL);
2519 l_ard_org_where := XLA_MO_REPORTING_API.Get_Predicate('ard',NULL);
2520 l_gl_dist_org_where := XLA_MO_REPORTING_API.Get_Predicate('gl_dist',NULL);
2521 l_sysparam_org_where := XLA_MO_REPORTING_API.Get_Predicate('sysparam',NULL);
2522 l_mcd_org_where := XLA_MO_REPORTING_API.Get_Predicate('mcd',NULL);
2523 l_crh_org_where := XLA_MO_REPORTING_API.Get_Predicate('crh',NULL);
2524 l_cr_org_where := XLA_MO_REPORTING_API.Get_Predicate('cr',NULL); /* Bug fix 6432847 */
2525 l_ath_org_where := XLA_MO_REPORTING_API.Get_Predicate('ath',NULL);
2526
2527 /* Replace the bind variables with global functions */
2528 l_ra_org_where := replace(l_ra_org_where,
2529 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2530 l_adj_org_where := replace(l_adj_org_where,
2531 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2532 l_ard_org_where := replace(l_ard_org_where,
2533 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2534 l_gl_dist_org_where := replace(l_gl_dist_org_where,
2535 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2536 l_sysparam_org_where := replace(l_sysparam_org_where,
2537 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2538 l_mcd_org_where := replace(l_mcd_org_where,
2539 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2540 l_crh_org_where := replace(l_crh_org_where,
2541 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2542 /* Bug fix 6432847 */
2543 l_cr_org_where := replace(l_cr_org_where,
2544 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2545 l_ath_org_where := replace(l_ath_org_where,
2549 l_reporting_level_name := substrb(XLA_MO_REPORTING_API.get_reporting_level_name,1,30);
2546 ':p_reporting_entity_id','arp_recon_rep.get_reporting_entity_id()');
2547
2548 l_reporting_entity_name := substrb(XLA_MO_REPORTING_API.get_reporting_entity_name,1,80);
2550
2551 /* Get the org name */
2552 select meaning
2553 into l_organization
2554 from ar_lookups
2555 where lookup_code ='ALL' and lookup_type ='ALL';
2556
2557 /* Build the other WHERE clauses */
2558 IF p_co_seg_low IS NULL AND p_co_seg_high IS NULL THEN
2559 l_co_seg_where := NULL;
2560 ELSIF p_co_seg_low IS NULL THEN
2561 l_co_seg_where := ' AND ' ||
2562 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
2563 p_id_flex_code => 'GL#',
2564 p_id_flex_num => p_coa_id,
2565 p_table_alias => 'GC',
2566 p_mode => 'WHERE',
2567 p_qualifier => 'GL_BALANCING',
2568 p_function => '<=',
2569 p_operand1 => p_co_seg_high);
2570 ELSIF p_co_seg_high IS NULL THEN
2571 l_co_seg_where := ' AND ' ||
2572 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
2573 p_id_flex_code => 'GL#',
2574 p_id_flex_num => p_coa_id,
2575 p_table_alias => 'GC',
2576 p_mode => 'WHERE',
2577 p_qualifier => 'GL_BALANCING',
2578 p_function => '>=',
2579 p_operand1 => p_co_seg_low);
2580 ELSE
2581 l_co_seg_where := ' AND ' ||
2582 AR_CALC_AGING.FLEX_SQL(p_application_id => 101,
2583 p_id_flex_code => 'GL#',
2584 p_id_flex_num => p_coa_id,
2585 p_table_alias => 'GC',
2586 p_mode => 'WHERE',
2587 p_qualifier => 'GL_BALANCING',
2588 p_function => 'BETWEEN',
2589 p_operand1 => p_co_seg_low,
2590 p_operand2 => p_co_seg_high);
2591 END IF;
2592
2593 IF p_gl_account_low IS NOT NULL AND p_gl_account_high IS NOT NULL THEN
2594 l_account_where := ' AND ' || AR_CALC_AGING.FLEX_SQL(
2595 p_application_id=> 101,
2596 p_id_flex_code =>'GL#',
2597 p_id_flex_num =>p_coa_id,
2598 p_table_alias => 'gc',
2599 p_mode => 'WHERE',
2600 p_qualifier => 'ALL',
2601 p_function=> 'BETWEEN',
2602 p_operand1 => p_gl_account_low,
2603 p_operand2 => p_gl_account_high);
2604 ELSE
2605 l_account_where := NULL;
2606 END IF;
2607
2608
2609 IF p_summary_account IS NOT NULL THEN
2610 SELECT fcav.application_column_name, flex_value_set_id
2611 INTO l_natural_segment_col , l_flex_value_set_id
2612 FROM fnd_segment_attribute_values fcav,
2613 fnd_id_flex_segments fifs
2614 WHERE fcav.application_id = 101
2615 AND fcav.id_flex_code = 'GL#'
2616 AND fcav.id_flex_num = arp_recon_rep.var_tname.g_chart_of_accounts_id
2617 AND fcav.attribute_value = 'Y'
2618 AND fcav.segment_attribute_type = 'GL_ACCOUNT'
2619 AND fifs.application_id = fcav.application_id
2620 AND fifs.id_flex_code = fcav.id_flex_code
2621 AND fifs.id_flex_num = fcav.id_flex_num
2622 AND fcav.application_column_name = fifs.application_column_name;
2623
2624 get_detail_accounts(l_flex_value_set_id, p_summary_account, l_code_combinations);
2625
2626 l_account_seg_where := ' AND gc.'||l_natural_segment_col||' in ('||l_code_combinations||' )';
2627 ELSE
2628 l_account_seg_where := NULL;
2629 END IF;
2630
2631 /* Get the report Headings */
2632 /* Added Conditional Implication to address bug:5181586*/
2633 IF p_reporting_level = 1000 THEN
2634 SELECT sob.name sob_name,
2635 sob.currency_code functional_currency
2636 INTO l_sob_name,
2637 l_functional_currency
2638 FROM gl_sets_of_books sob
2639 WHERE sob.set_of_books_id = arp_recon_rep.var_tname.g_reporting_entity_id;
2640
2641 ELSIF p_reporting_level = 3000 THEN
2642 SELECT sob.name sob_name,
2643 sob.currency_code functional_currency
2644 INTO l_sob_name,
2645 l_functional_currency
2646 FROM gl_sets_of_books sob,
2647 ar_system_parameters_all sysparam
2648 WHERE sob.set_of_books_id = sysparam.set_of_books_id
2649 AND sysparam.org_id = arp_recon_rep.var_tname.g_reporting_entity_id;
2650
2651 END IF;
2652 /* Changes for bug:5181586 ends*/
2653
2654 arp_recon_rep.var_tname.g_functional_currency := l_functional_currency;
2655
2656 /* Get the format mask for the function currency */
2657 select fnd_currency.get_format_mask(l_functional_currency,40)
2661 SELECT p.start_date, p.end_date
2658 into l_format
2659 from dual;
2660 /* Get the period start and end dates */
2662 INTO l_gl_date_from , l_gl_date_to
2663 FROM gl_periods p, gl_sets_of_books b
2664 WHERE p.period_set_name = b.period_set_name
2665 AND p.period_type = b.accounted_period_type
2666 AND b.set_of_books_id = arp_recon_rep.var_tname.g_set_of_books_id
2667 AND p.period_name = arp_recon_rep.var_tname.g_period_name;
2668 arp_recon_rep.var_tname.g_gl_date_from := l_gl_date_from;
2669 arp_recon_rep.var_tname.g_gl_date_to := l_gl_date_to;
2670
2671 /* Bug fix 4942083*/
2672 IF arp_util.Open_Period_Exists(p_reporting_level,
2673 p_reporting_entity_id,
2674 arp_recon_rep.var_tname.g_gl_date_from,
2675 arp_recon_rep.var_tname.g_gl_date_to) THEN
2676 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
2677 l_message_acct := FND_MESSAGE.Get;
2678 END IF;
2679
2680 execute immediate '
2681 insert into ar_gl_recon_gt(code_combination_id,
2682 receivables_dr,receivables_cr,
2683 account_type, account_type_code)
2684 (select dat.code_combination_id,
2685 sum(nvl(acctd_amount_dr,0)) receivables_debit,
2686 sum(nvl(acctd_amount_cr,0)) receivables_credit,
2687 lookup.description account_type,
2688 gc.account_type account_type_code
2689 from (
2690
2691 -- Bug 6943555
2692
2693 select decode(sign(sum(nvl(b.acctd_amount_dr,0))- sum(nvl(b.acctd_amount_cr,0))),
2694 +1, (sum(nvl(b.acctd_amount_dr,0))- sum(nvl(b.acctd_amount_cr,0))),
2695 0) acctd_amount_dr,
2696 decode(sign(sum(nvl(b.acctd_amount_dr,0))- sum(nvl(b.acctd_amount_cr,0))),
2697 -1, (sum(nvl(b.acctd_amount_cr,0))- sum(nvl(b.acctd_amount_dr,0))),
2698 0) acctd_amount_cr,
2699 b.code_combination_id
2700
2701 from
2702
2703 (select
2704 DECODE(account_class, ''REC'',decode(sign(acctd_amount), -1 ,0, acctd_amount),
2705 ''REV'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
2706 ''TAX'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
2707 ''ROUND'',decode(sign(acctd_amount), -1,abs(acctd_amount),0),
2708 ''UNEARN'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
2709 ''FREIGHT'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
2710 ''UNBILL'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),0) acctd_amount_dr,
2711 DECODE(account_class, ''REC'',decode(sign(acctd_amount), -1 ,abs(acctd_amount),0),
2712 ''REV'',decode(sign(acctd_amount), -1, 0,acctd_amount),
2713 ''TAX'',decode(sign(acctd_amount), -1, 0,acctd_amount),
2714 ''ROUND'',decode(sign(acctd_amount), -1,0,acctd_amount),
2715 ''UNEARN'',decode(sign(acctd_amount), -1, 0,acctd_amount),
2716 ''FREIGHT'',decode(sign(acctd_amount), -1, 0,acctd_amount),
2717 ''UNBILL'',decode(sign(acctd_amount), -1, 0,acctd_amount),0) acctd_amount_cr,
2718 gl_dist.code_combination_id code_combination_id,
2719 gl_dist.ae_header_id ae_header_id,
2720 gl_dist.ae_line_num ae_line_num
2721 from '||arp_recon_rep.var_tname.l_ra_cust_trx_gl_dist_all||' gl_dist
2722 where gl_dist.gl_date between :gl_date_from and :gl_date_to
2723 /* Bug fix 6631925 */
2724 and gl_dist.account_set_flag = ''N''
2725 and gl_dist.posting_control_id <> -3
2726 '||l_gl_dist_org_where||'
2727 UNION ALL
2728 select ard.acctd_amount_dr acctd_amount_dr ,
2729 ard.acctd_amount_cr acctd_amount_cr ,
2730 ard.code_combination_id code_combination_id,
2731 ard.ae_header_id ae_header_id,
2732 ard.ae_line_num ae_line_num
2733 from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
2734 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
2735 '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr /* Bug fix 6432847 */
2736 where crh.gl_date between :gl_date_from and :gl_date_to
2737 and crh.posting_control_id <> -3
2738 and crh.cash_receipt_history_id = ard.source_id
2739 /* Bug 6432847 : select receipts that are not reversed */
2740 and cr.cash_receipt_id = crh.cash_receipt_id
2741 and cr.reversal_date IS NULL
2742 and ard.source_table = ''CRH''
2743 '||l_ard_org_where||'
2744 '||l_crh_org_where||'
2748 select ard.acctd_amount_dr acctd_amount_dr ,
2745 '||l_cr_org_where||'
2746 /* Bug fix 6432847: select receipts that are reversed*/
2747 UNION ALL
2749 ard.acctd_amount_cr acctd_amount_cr ,
2750 ard.code_combination_id code_combination_id,
2751 ard.ae_header_id ae_header_id,
2752 ard.ae_line_num ae_line_num
2753 from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
2754 '||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
2755 '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr
2756 where cr.reversal_date IS NOT NULL
2757 and crh.cash_receipt_id = cr.cash_receipt_id
2758 and crh.posting_control_id <> -3
2759 and crh.cash_receipt_history_id = ard.source_id
2760 and ard.gl_date between :gl_date_from and :gl_date_to
2761 and ard.source_table = ''CRH''
2762 '||l_ard_org_where||'
2763 '||l_crh_org_where||'
2764 '||l_cr_org_where||'
2765 UNION ALL
2766 /* Bug fix 6432847: with ra.gl_date condition, select
2767 applications which are not unapplied */
2768 select ard.acctd_amount_dr acctd_amount_dr ,
2769 ard.acctd_amount_cr acctd_amount_cr ,
2770 ard.code_combination_id code_combination_id,
2771 ard.ae_header_id ae_header_id,
2772 ard.ae_line_num ae_line_num
2773 from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
2774 '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
2775 '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr /* Bug fix 6432847 */
2776 where ra.gl_date between :gl_date_from and :gl_date_to
2777 and cr.cash_receipt_id = ra.cash_receipt_id
2778 and cr.reversal_date IS NULL
2779 and ra.posting_control_id <> -3
2780 and ra.receivable_application_id = ard.source_id
2781 and ard.source_table = ''RA''
2782 and ra.application_type = ''CASH''
2783 '||l_ard_org_where||'
2784 '||l_ra_org_where||'
2785 '||l_cr_org_where||'
2786 UNION ALL
2787 /* Bug fix 6432847: with ard.gl_date condition, select
2788 applications which are unapplied */
2789 select ard.acctd_amount_dr acctd_amount_dr ,
2790 ard.acctd_amount_cr acctd_amount_cr ,
2791 ard.code_combination_id code_combination_id,
2792 ard.ae_header_id ae_header_id,
2793 ard.ae_line_num ae_line_num
2794 from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
2795 '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
2796 '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr /* Bug fix 6432847 */
2797 where ard.gl_date between :gl_date_from and :gl_date_to
2798 and cr.reversal_date IS NOT NULL
2799 and ra.cash_receipt_id = cr.cash_receipt_id
2800 and ra.posting_control_id <> -3
2801 and ra.receivable_application_id = ard.source_id
2802 and ard.source_table = ''RA''
2803 and ra.application_type = ''CASH''
2804 '||l_ard_org_where||'
2805 '||l_ra_org_where||'
2806 '||l_cr_org_where||'
2807 /* Bug fix 5679071 : UNAPP records should be displayed based on how it was posted */
2808 UNION ALL
2809 select ard.acctd_amount_dr acctd_amount_dr ,
2810 ard.acctd_amount_cr acctd_amount_cr ,
2811 ard.code_combination_id code_combination_id,
2812 ard.ae_header_id ae_header_id,
2813 ard.ae_line_num ae_line_num
2814 from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
2815 '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra
2816 where ard.gl_date between :gl_date_from and :gl_date_to
2817 and ra.posting_control_id <> -3
2818 and ra.receivable_application_id = ard.source_id
2819 and ard.source_table = ''RA''
2820 and ra.application_type <> ''CASH''
2821 '||l_ard_org_where||'
2822 '||l_ra_org_where||'
2823 UNION ALL
2824 select ard.acctd_amount_dr acctd_amount_dr ,
2825 ard.acctd_amount_cr acctd_amount_cr ,
2826 ard.code_combination_id code_combination_id,
2827 ard.ae_header_id ae_header_id,
2828 ard.ae_line_num ae_line_num
2829 from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
2830 '||arp_recon_rep.var_tname.l_ar_misc_cash_dists_all||' mcd
2831 where mcd.gl_date between :gl_date_from and :gl_date_to
2832 and mcd.posting_control_id <> -3
2836 '||l_mcd_org_where||'
2833 and mcd.misc_cash_distribution_id = ard.source_id
2834 and ard.source_table = ''MCD''
2835 '||l_ard_org_where||'
2837 UNION ALL
2838 select ard.acctd_amount_dr acctd_amount_dr ,
2839 ard.acctd_amount_cr acctd_amount_cr ,
2840 ard.code_combination_id code_combination_id,
2841 ard.ae_header_id ae_header_id,
2842 ard.ae_line_num ae_line_num
2843 from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
2844 '||arp_recon_rep.var_tname.l_ar_adjustments_all||' adj
2845 where adj.gl_date between :gl_date_from and :gl_date_to
2846 and adj.posting_control_id <> -3
2847 and adj.adjustment_id = ard.source_id
2848 and ard.source_table = ''ADJ''
2849 '||l_ard_org_where||'
2850 '||l_adj_org_where||'
2851 UNION ALL
2852 select ard.acctd_amount_dr acctd_amount_dr ,
2853 ard.acctd_amount_cr acctd_amount_cr ,
2854 ard.code_combination_id code_combination_id,
2855 ard.ae_header_id ae_header_id,
2856 ard.ae_line_num ae_line_num
2857 from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
2858 ar_transaction_history_all ath
2859 where ath.gl_date between :gl_date_from and :gl_date_to
2860 and ath.posting_control_id <> -3
2861 and ath.transaction_history_id = ard.source_id
2862 and ard.source_table = ''TH''
2863 '||l_ard_org_where||'
2864 '||l_ath_org_where||'
2865 /* 6964153 */
2866 UNION ALL
2867 SELECT xal.accounted_dr acctd_amount_dr,
2868 xal.accounted_cr acctd_amount_cr,
2869 xal.code_combination_id code_combination_id,
2870 xal.ae_header_id ae_header_id,
2871 xal.ae_line_num ae_line_num
2872 from xla_ae_lines xal,
2873 xla_ae_headers xah,
2874 xla_transaction_entities_upg xte
2875 where xal.accounting_class_code = ''BALANCE''
2876 and xah.entity_id = xte.entity_id
2877 and xal.ae_header_id = xah.ae_header_id
2878 and xah.accounting_date
2879 between :gl_date_from and :gl_date_to
2880 and xal.application_id = 222
2881 and xah.application_id = 222
2882 and xte.application_id = 222
2883 and xte.ledger_id = :reporting_entity_id )b
2884
2885 group by b.ae_header_id,b.ae_line_num,b.code_combination_id
2886
2887 ) dat,
2888 gl_code_combinations gc,
2889 gl_lookups lookup
2890 where dat.code_combination_id = gc.code_combination_id
2891 and lookup.lookup_code = gc.account_type
2892 and lookup.lookup_type = ''ACCOUNT TYPE'''||
2893 l_co_seg_where||
2894 l_account_where||
2895 l_account_seg_where||'
2896 group by dat.code_combination_id,lookup.description, gc.code_combination_id,gc.account_type)'
2897 USING
2898 l_gl_date_from, l_gl_date_to,
2899 l_gl_date_from, l_gl_date_to,
2900 l_gl_date_from, l_gl_date_to,
2901 l_gl_date_from, l_gl_date_to,
2902 l_gl_date_from, l_gl_date_to,
2903 l_gl_date_from, l_gl_date_to,
2904 l_gl_date_from, l_gl_date_to,
2905 l_gl_date_from, l_gl_date_to,
2906 l_gl_date_from, l_gl_date_to,
2907 l_gl_date_from, l_gl_date_to, p_reporting_entity_id ;
2908
2909
2910 update ar_gl_recon_gt argt
2911 set (opening_balance_dr,
2912 opening_balance_cr,
2913 period_activity_dr,
2914 period_activity_cr) = (select nvl(glb.begin_balance_dr,0),
2915 nvl(glb.begin_balance_cr,0),
2916 nvl(glb.period_net_dr,0),
2917 nvl(glb.period_net_cr,0)
2918 from gl_balances glb
2919 where glb.period_name = get_period_name()
2920 and glb.code_combination_id = argt.code_combination_id
2921 and glb.actual_flag = 'A'
2922 and glb.ledger_id = get_set_of_books_id()
2923 and glb.currency_code = get_functional_currency());
2924
2925 update ar_gl_recon_gt argt
2926 set (subledger_not_ar_dr ,
2927 subledger_not_ar_cr ,
2928 subledger_manual_dr ,
2929 subledger_manual_cr ,
2930 subledger_rec_dr,
2931 subledger_rec_cr,
2932 gl_unposted_dr,
2933 gl_unposted_cr) =
2934 (select sum(decode(gjh.je_source,'Manual', 0,
2938 sum(decode(gjh.je_source,'Manual', 0,
2935 'Receivables', 0,
2936 decode(gjl.status,
2937 'P',gjl.accounted_dr,0))) subledger_not_ar_dr ,
2939 'Receivables', 0,
2940 decode(gjl.status,
2941 'P',gjl.accounted_cr,0))) subledger_not_ar_cr,
2942 sum(decode(gjh.je_source, 'Manual',
2943 decode(gjl.status,'P',
2944 gjl.accounted_dr,0),0)) subledger_manual_dr ,
2945 sum(decode(gjh.je_source, 'Manual',
2946 decode(gjl.status,'P',
2947 gjl.accounted_cr,0),0)) subledger_manual_cr,
2948 sum(decode(gjh.je_source, 'Receivables',
2949 decode(gjl.status,'P',
2950 gjl.accounted_dr,0),0)) subledger_receivables_dr ,
2951 sum(decode(gjh.je_source, 'Receivables',
2952 decode(gjl.status,'P',
2953 gjl.accounted_cr,0),0)) subledger_receivables_cr,
2954 sum(decode(gjl.status,'P',0,gjl.accounted_dr)) gl_unposted_dr,
2955 sum(decode(gjl.status,'P',0,gjl.accounted_cr)) gl_unposted_cr
2956 from gl_je_lines gjl,
2957 gl_je_headers gjh
2958 where gjl.code_combination_id = argt.code_combination_id
2959 and gjl.period_name = get_period_name()
2960 and gjl.ledger_id = get_set_of_books_id()
2961 and gjl.je_header_id = gjh.je_header_id
2962 and gjh.actual_flag = 'A'
2963 and gjh.currency_code <> 'STAT'
2964 group by gjl.code_combination_id);
2965
2966 update ar_gl_recon_gt argt
2967 set (gl_interface_dr, gl_interface_cr) =
2968 (select sum(nvl(gif.accounted_dr,0)) gl_interface_dr,
2969 sum(nvl(gif.accounted_cr,0)) gl_interface_cr
2970 from gl_interface gif,
2971 gl_je_sources gjs
2972 where gif.code_combination_id = argt.code_combination_id
2973 and gif.accounting_date between get_gl_date_from() and get_gl_date_to()
2974 and gif.user_je_source_name = gjs.user_je_source_name
2975 and gjs.je_source_name = 'Receivables'
2976 and gif.actual_flag = 'A'
2977 group by gif.code_combination_id);
2978
2979 update ar_gl_recon_gt
2980 set account = ar_calc_aging.get_value(101,'GL#',
2981 arp_recon_rep.get_chart_of_accounts_id(),'ALL',code_combination_id),
2982 company = ar_calc_aging.get_value(101,'GL#',
2983 arp_recon_rep.get_chart_of_accounts_id(),'GL_BALANCING',code_combination_id),
2984 account_desc = ar_calc_aging.get_description(101,'GL#',
2985 arp_recon_rep.get_chart_of_accounts_id(),'GL_ACCOUNT',code_combination_id);
2986
2987 l_xml_query := '
2988 select code_combination_id,
2989 account_type,
2990 account,
2991 account_desc,
2992 company,
2993 decode(account_type_code,''A'',1,''L'',2,''R'',3,''E'',4) account_type_code,
2994 nvl(opening_balance_dr,0) begin_gl_bal_debit,
2995 nvl(opening_balance_cr,0) begin_gl_bal_credit,
2996 nvl(opening_balance_dr,0)+nvl(period_activity_dr,0) end_gl_bal_debit,
2997 nvl(opening_balance_cr,0)+nvl(period_activity_cr,0) end_gl_bal_credit,
2998 nvl(subledger_not_ar_dr,0) subledger_not_ar_debit,
2999 nvl(subledger_not_ar_cr,0) subledger_not_ar_credit,
3000 nvl(subledger_manual_dr,0) subledger_manual_debit,
3001 nvl(subledger_manual_cr,0) subledger_manual_credit,
3002 nvl(subledger_rec_dr,0) subledger_receivables_debit,
3003 nvl(subledger_rec_cr,0) subledger_receivables_credit,
3004 nvl(gl_unposted_dr,0) gl_unposted_debit,
3005 nvl(gl_unposted_cr,0) gl_unposted_credit,
3006 nvl(gl_interface_dr,0) gl_interface_debit,
3007 nvl(gl_interface_cr,0) gl_interface_credit,
3008 nvl(receivables_dr,0) receivables_debit,
3009 nvl(receivables_cr,0) receivables_credit
3010 from ar_gl_recon_gt
3011 where ''N'' = arp_recon_rep.get_out_of_balance_only()
3012 or nvl(receivables_dr,0)- nvl(subledger_rec_dr,0) <> 0
3013 or nvl(receivables_cr,0)- nvl(subledger_rec_cr,0) <> 0
3014 order by account_type_code,
3015 company,
3016 ar_calc_aging.get_value(101,''GL#'',
3017 arp_recon_rep.get_chart_of_accounts_id(),
3018 ''GL_ACCOUNT'',code_combination_id)';
3019
3020 DBMS_UTILITY.DB_VERSION(l_version, l_compatibility);
3021 l_majorVersion := to_number(substr(l_version, 1, instr(l_version,'.')-1));
3022
3023 IF (l_majorVersion > 8 and l_majorVersion < 9) THEN
3024 BEGIN
3025 queryCtx := DBMS_XMLQuery.newContext(l_xml_query);
3026 DBMS_XMLQuery.setRaiseNoRowsException(queryCtx,TRUE);
3027 l_result := DBMS_XMLQuery.getXML(queryCtx);
3028 DBMS_XMLQuery.closeContext(queryCtx);
3029 l_rows_processed := 1;
3030 EXCEPTION WHEN OTHERS THEN
3031 DBMS_XMLQuery.getExceptionContent(queryCtx,l_errNo,l_errMsg);
3032 IF l_errNo = 1403 THEN
3033 l_rows_processed := 0;
3034 END IF;
3035 DBMS_XMLQuery.closeContext(queryCtx);
3036 END;
3037 ELSIF (l_majorVersion >= 9 ) THEN
3038 qryCtx := DBMS_XMLGEN.newContext(l_xml_query);
3039 l_result := DBMS_XMLGEN.getXML(qryCtx,DBMS_XMLGEN.NONE);
3040 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
3041 DBMS_XMLGEN.closeContext(qryCtx);
3042 END IF;
3043
3044 IF l_rows_processed <> 0 THEN
3045 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
3046 tempResult := l_result;
3047 ELSE
3048 l_resultOffset := 0;
3049 END IF;
3050
3051 l_new_line := '
3052 ';
3053 select to_char(sysdate,'YYYY-MM-DD')
3054 into l_report_date
3055 from dual;
3056
3057 /* Bug 4708930
3058 Get the special characters replaced */
3059 l_reporting_entity_name := format_string(l_reporting_entity_name);
3060 l_reporting_level_name := format_string(l_reporting_level_name);
3061 l_organization := format_string(l_organization);
3062 l_sob_name := format_string(l_sob_name);
3063 l_message_acct := format_string(l_message_acct);
3064
3065 /* Prepare the tag for the report heading */
3066 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
3067 l_xml_header := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
3068 l_xml_header := l_xml_header ||l_new_line||'<ARGLRECON>';
3069 l_xml_header := l_xml_header ||l_new_line||' <MSG_TXT_ACCT>'||l_message_acct||'</MSG_TXT_ACCT>';
3070 l_xml_header := l_xml_header ||l_new_line||'<PERIOD>'||p_period_name||'</PERIOD>';
3071 l_xml_header := l_xml_header ||l_new_line||'<REPORT_DATE>'||l_report_date||'</REPORT_DATE>';
3072 l_xml_header := l_xml_header ||l_new_line||'<REPORTING_LEVEL>'||l_reporting_level_name||'</REPORTING_LEVEL>';
3073 l_xml_header := l_xml_header ||l_new_line||'<REPORTING_ENTITY>'||l_reporting_entity_name||'</REPORTING_ENTITY>';
3074 l_xml_header := l_xml_header ||l_new_line||'<SOB_ID>'||p_sob_id||'</SOB_ID>';
3075 l_xml_header := l_xml_header ||l_new_line||'<OUT_OF_BAL_ONLY>'||p_out_of_balance_only||'</OUT_OF_BAL_ONLY>';
3076 l_xml_header := l_xml_header ||l_new_line||'<CO_SEG_LOW>'||p_co_seg_low||'</CO_SEG_LOW>';
3077 l_xml_header := l_xml_header ||l_new_line||'<CO_SEG_HIGH>'||p_co_seg_high||'</CO_SEG_HIGH>';
3078 l_xml_header := l_xml_header ||l_new_line||'<GL_DATE_FROM>'||to_char(l_gl_date_from,'YYYY-MM-DD')||'</GL_DATE_FROM>';
3079 l_xml_header := l_xml_header ||l_new_line||'<GL_DATE_TO>'||to_char(l_gl_date_to,'YYYY-MM-DD')||'</GL_DATE_TO>';
3080 l_xml_header := l_xml_header ||l_new_line||'<GL_ACCOUNT_LOW>'||p_gl_account_low||'</GL_ACCOUNT_LOW>';
3081 l_xml_header := l_xml_header ||l_new_line||'<GL_ACCOUNT_HIGH>'||p_gl_account_high||'</GL_ACCOUNT_HIGH>';
3082 l_xml_header := l_xml_header ||l_new_line||'<SUMMARY_ACCOUNT>'||p_summary_account||'</SUMMARY_ACCOUNT>';
3083 l_xml_header := l_xml_header ||l_new_line||'<SET_OF_BOOKS>'||l_sob_name||'</SET_OF_BOOKS>';
3084 l_xml_header := l_xml_header ||l_new_line||'<ORGANIZATION>'||l_organization||'</ORGANIZATION>';
3085 l_xml_header := l_xml_header ||l_new_line||'<FUNCTIONAL_CURRENCY>'||l_functional_currency||'</FUNCTIONAL_CURRENCY>';
3086 l_xml_header := l_xml_header ||l_new_line||'<NUM_ROWS>'||l_rows_processed||'</NUM_ROWS>';
3087 l_xml_header := l_xml_header ||l_new_line||'<CURRENCY_FORMAT>'||l_format||'</CURRENCY_FORMAT>'||l_new_line;
3088 l_close_tag := l_new_line||'</ARGLRECON>'||l_new_line;
3089
3090 l_xml_header_length := length(l_xml_header);
3094 l_xml_header_length,l_resultOffset);
3091 IF l_rows_processed <> 0 THEN
3092 dbms_lob.write(tempResult,l_xml_header_length,1,l_xml_header);
3093 dbms_lob.copy(tempResult,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
3095 ELSE
3096 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
3097 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
3098 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
3099 END IF;
3100
3101 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
3102
3103 ar_cumulative_balance_report.process_clob(tempResult);
3104 p_result := tempResult;
3105
3106 log('arglrecon_load_xml(-)');
3107
3108 END arglrecon_load_xml;
3109
3110 END ARP_RECON_REP;