[Home] [Help]
PACKAGE BODY: APPS.XLA_MULTIPERIOD_RPRTG_PKG
Source
1 PACKAGE BODY XLA_MULTIPERIOD_RPRTG_PKG AS
2 -- $Header: xlarpmpa.pkb 120.8.12020000.2 2012/07/23 07:02:24 vgopiset ship $
3 /*===========================================================================+
4 | Copyright (c) 2003 Oracle Corporation Belmont, California, USA |
5 | ALL rights reserved. |
6 +============================================================================+
7 | FILENAME |
8 | xlarpmpa.pkb |
9 | |
10 | PACKAGE NAME |
11 | xla_multiperiod_rprtg_pkg |
12 | |
13 | DESCRIPTION |
14 | This package calls XLA_MULTIPERIOD_ACCOUNTING_PKG.complete_ |
15 | journal_entries and generates the XML extract for reporting |
16 | multiperiod recognition entries,accrual reversal entries and |
17 | their errors. |
18 | HISTORY |
19 | 16/08/2005 VS Koushik Created |
20 +===========================================================================*/
21
22 TYPE t_rec IS RECORD
23 (f1 VARCHAR2(80)
24 ,f2 VARCHAR2(80));
25 TYPE t_array IS TABLE OF t_rec INDEX BY BINARY_INTEGER;
26
27
28 --=============================================================================
29 -- *********** Local Trace Routine **********
30 --=============================================================================
31 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
32 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
33 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
34 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
35 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
36 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
37 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
38 C_DEFAULT_MODULE CONSTANT VARCHAR2(240)
39 := 'xla.plsql.xla_multiperiod_rprtg_pkg';
40
41 g_log_level NUMBER;
42 g_log_enabled BOOLEAN;
43
44
45 PROCEDURE trace
46 (p_msg IN VARCHAR2
47 ,p_level IN NUMBER
48 ,p_module IN VARCHAR2) IS
49 BEGIN
50 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
51 fnd_log.message(p_level, NVL(p_module,C_DEFAULT_MODULE));
52 ELSIF p_level >= g_log_level THEN
53 fnd_log.string(p_level, NVL(p_module,C_DEFAULT_MODULE), p_msg);
54 END IF;
55
56 EXCEPTION
57 WHEN xla_exceptions_pkg.application_exception THEN
58 RAISE;
59 WHEN OTHERS THEN
60 xla_exceptions_pkg.raise_message
61 (p_location => 'xla_multiperiod_rprtg_pkg.trace');
62 END trace;
63
64
65
66 PROCEDURE print_logfile(p_msg IN VARCHAR2) IS
67 BEGIN
68
69 fnd_file.put_line(fnd_file.log,p_msg);
70
71 EXCEPTION
72 WHEN xla_exceptions_pkg.application_exception THEN
73 RAISE;
74 WHEN OTHERS THEN
75 xla_exceptions_pkg.raise_message
76 (p_location => 'XLA_MULTIPERIOD_RPRTG_PKG.print_logfile');
77 END print_logfile;
78
79 PROCEDURE build_xml_sql (p_accounting_batch_id IN NUMBER);
80
81 --=============================================================================
82 -- *********** public procedures and functions **********
83 --=============================================================================
84 -- 1. run_report
85 --=============================================================================
86 PROCEDURE RUN_REPORT
87 (p_errbuf OUT NOCOPY VARCHAR2
88 ,p_retcode OUT NOCOPY NUMBER
89 ,p_application_id IN NUMBER
90 ,p_ledger_id IN NUMBER
91 ,p_process_category_code IN VARCHAR2
92 ,p_end_date IN DATE
93 ,p_errors_only_flag IN VARCHAR2
94 ,p_transfer_to_gl_flag IN VARCHAR2
95 ,p_post_in_gl_flag IN VARCHAR2
96 ,p_gl_batch_name IN VARCHAR2
97 ,p_valuation_method_code IN VARCHAR2
98 ,p_security_int_1 IN NUMBER
99 ,p_security_int_2 IN NUMBER
100 ,p_security_int_3 IN NUMBER
101 ,p_security_char_1 IN VARCHAR2
102 ,p_security_char_2 IN VARCHAR2
103 ,p_security_char_3 IN VARCHAR2) IS
104
105 l_log_module VARCHAR2(240);
106 l_accounting_batch_id XLA_AE_HEADERS.ACCOUNTING_BATCH_ID%TYPE;
107
108 BEGIN
109
110 IF g_log_enabled THEN
111 l_log_module := C_DEFAULT_MODULE||'.run_report';
112 END IF;
113
114 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
115 trace('run_report.Begin',C_LEVEL_PROCEDURE,l_log_module);
116 END IF;
117
118 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
119 trace('p_application_id = '|| to_char(p_application_id),
120 C_LEVEL_STATEMENT, l_log_module);
121 trace('p_ledger_id = '|| to_char(p_ledger_id),
122 C_LEVEL_STATEMENT, l_log_module);
123 trace('p_process_category_code = '||to_char(p_process_category_code),
124 C_LEVEL_STATEMENT, l_log_module);
125 trace('p_end_date = '||to_char(p_end_date,'DD-MON-YYYY'),
126 C_LEVEL_STATEMENT,l_log_module);
127 trace('p_errors_only_flag = '|| p_errors_only_flag,
128 C_LEVEL_STATEMENT, l_log_module);
129 trace('p_transfer_to_gl_flag = '|| to_char(p_transfer_to_gl_flag),
130 C_LEVEL_STATEMENT, l_log_module);
131 trace('p_post_in_gl_flag = '|| to_char(p_post_in_gl_flag),
132 C_LEVEL_STATEMENT, l_log_module);
133 trace('p_gl_batch_name = '|| to_char(p_gl_batch_name),
134 C_LEVEL_STATEMENT, l_log_module);
135 trace('p_valuation_method_code = '||to_char(p_valuation_method_code),
136 C_LEVEL_STATEMENT, l_log_module);
137 trace('p_security_int_1 = '|| to_char(p_security_int_1),
138 C_LEVEL_STATEMENT, l_log_module);
139 trace('p_security_int_2 = '|| to_char(p_security_int_2),
140 C_LEVEL_STATEMENT, l_log_module);
141 trace('p_security_int_3 = '|| to_char(p_security_int_3),
142 C_LEVEL_STATEMENT, l_log_module);
143 trace('p_security_char_1 = '|| to_char(p_security_char_1),
144 C_LEVEL_STATEMENT, l_log_module);
145 trace('p_security_char_2 = '|| to_char(p_security_char_2),
146 C_LEVEL_STATEMENT, l_log_module);
147 trace('p_security_char_3 = '|| to_char(p_security_char_3),
148 C_LEVEL_STATEMENT, l_log_module);
149 END IF;
150 XLA_MULTIPERIOD_ACCOUNTING_PKG.complete_journal_entries(
151 p_application_id => p_application_id
152 ,p_ledger_id => p_ledger_id
153 ,p_process_category_code => p_process_category_code
154 ,p_end_date => p_end_date
155 ,p_errors_only_flag => p_errors_only_flag
156 ,p_transfer_to_gl_flag => p_transfer_to_gl_flag
157 ,p_post_in_gl_flag => p_post_in_gl_flag
158 ,p_gl_batch_name => p_gl_batch_name
159 ,p_valuation_method_code => p_valuation_method_code
160 ,p_security_id_int_1 => p_security_int_1
161 ,p_security_id_int_2 => p_security_int_2
162 ,p_security_id_int_3 => p_security_int_3
163 ,p_security_id_char_1 => p_Security_char_1
164 ,p_security_id_char_2 => p_security_char_2
165 ,p_security_id_char_3 => p_security_char_3
166 ,p_accounting_batch_id => l_accounting_batch_id
167 ,p_errbuf => p_errbuf
168 ,p_retcode => p_retcode);
169
170 build_xml_sql(l_accounting_batch_id);
171
172
173 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
174 trace('run_report.End',C_LEVEL_PROCEDURE,l_log_module);
175 END IF;
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 -- trace mesg
180 xla_exceptions_pkg.raise_message
181 (p_location => 'xla_multiperiod_rprtg_pkg.run_report ');
182 END run_report;
183
184 FUNCTION get_transaction_id
185 (p_application_id IN NUMBER
186 ,p_ledger_id IN NUMBER
187 ,p_end_date IN DATE
188 ,p_process_category_code IN VARCHAR2) RETURN VARCHAR2 IS
189
190 CURSOR cur_event_class IS
191 /* Changed from = xla_ae_headers to exists in xla_ae_headers
192 performance bug#8234582*/
193 SELECT DISTINCT xcl.application_id APPLICATION_ID
194 ,xcl.entity_code ENTITY_CODE
195 ,xcl.event_class_code EVENT_CLASS_CODE
196 ,xatr.reporting_view_name REPORTING_VIEW_NAME
197 FROM xla_event_types_b xcl
198 ,xla_event_class_attrs xatr
199 WHERE xatr.entity_code = xcl.entity_code
200 AND xatr.event_class_code = xcl.event_class_code
201 AND xatr.application_id = p_application_id
202 AND xcl.application_id = p_application_id -- added for 8722755
203 AND xatr.event_class_group_code = nvl(p_process_category_code, xatr.event_class_group_code)
204 AND xatr.event_class_code NOT IN ('THIRD_PARTY_MERGE','MANUAL','REVERSAL');
205
206
207 l_col_array t_array;
208 l_null_col_array t_array;
209 l_trx_id_str VARCHAR2(32000);
210 l_col_string VARCHAR2(4000) := NULL;
211 l_view_name VARCHAR2(800);
212 l_join_string VARCHAR2(4000) := NULL;
213 l_sql_string VARCHAR2(4000) := NULL;
214 l_index INTEGER;
215 l_outerjoin VARCHAR2(30);
216 l_log_module VARCHAR2(240);
217
218 BEGIN
219 IF g_log_enabled THEN
220 l_log_module := C_DEFAULT_MODULE||'.get_transaction_id';
221 END IF;
222 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
223 trace
224 (p_msg => 'BEGIN of function GET_TRANSACTION_ID'
225 ,p_level => C_LEVEL_PROCEDURE
226 ,p_module => l_log_module);
227 trace
228 (p_msg => 'p_application_id = '||to_char(p_application_id)
229 ,p_level => C_LEVEL_PROCEDURE
230 ,p_module => l_log_module);
231 trace
232 (p_msg => 'p_ledger_id = '||to_char(p_ledger_id)
233 ,p_level => C_LEVEL_PROCEDURE
234 ,p_module => l_log_module);
235 trace
236 (p_msg => 'p_end_date = '||to_char(p_end_date,'DD-MON-YYYY')
237 ,p_level => C_LEVEL_PROCEDURE
238 ,p_module => l_log_module);
239 trace
240 (p_msg => 'p_process_category_code = '||p_process_category_code
241 ,p_level => C_LEVEL_PROCEDURE
242 ,p_module => l_log_module);
243 END IF;
244
245 l_trx_id_str := ',CASE WHEN 1<1 THEN NULL';
246
247 FOR cur_trx IN cur_event_class LOOP
248 l_col_string := NULL;
249 l_view_name := NULL;
250 l_join_string := NULL;
251
252 IF cur_trx.entity_code <> 'MANUAL' THEN
253 --
254 -- creating a dummy array that contains "NULL" strings
255 --
256 FOR i IN 1..10 LOOP
257 l_null_col_array(i).f1 := 'NULL';
258 l_null_col_array(i).f2 := 'NULL';
259 END LOOP;
260 --
261 -- initiating the array that contains name of the columns to be selected
262 -- from the TID View.
263 --
264 l_col_array := l_null_col_array;
265
266 --
267 -- creating SELECT,FROM and WHERE clause strings when the reporting view is
268 -- defined for an Event Class.
269 --
270
271 IF cur_trx.reporting_view_name IS NOT NULL THEN
272
273 --
274 -- creating string to be added to FROM clause
275 --
276 l_view_name := cur_trx.reporting_view_name || ' TIV';
277 l_index := 0;
278 -- Split the join between Entity Mapping and Event Mappings as Report Ends in Error
279 -- with SQL Syntax erro when User Transaction Identifiers are nor provided in
280 -- Accounting Event Class Options Window (Added for Bug 13596822)
281 FOR cols_csr IN
282 (SELECT xid.transaction_id_col_name_1 trx_col_1
283 ,xid.transaction_id_col_name_2 trx_col_2
284 ,xid.transaction_id_col_name_3 trx_col_3
285 ,xid.transaction_id_col_name_4 trx_col_4
286 ,xid.source_id_col_name_1 src_col_1
287 ,xid.source_id_col_name_2 src_col_2
288 ,xid.source_id_col_name_3 src_col_3
289 ,xid.source_id_col_name_4 src_col_4
290 FROM xla_entity_id_mappings xid
291 --,xla_event_mappings_vl xem
292 WHERE xid.application_id = cur_trx.application_id
293 AND xid.entity_code = cur_trx.entity_code
294 )
295 LOOP
296
297 l_index := l_index + 1;
298 --
299 -- creating string to be added to WHERE clause
300 --
301 IF l_index = 1 THEN
302
303 IF g_log_level <> C_LEVEL_LOG_DISABLED THEN
304 l_outerjoin := '(+)';
305 ELSE
306 l_outerjoin := NULL;
307 END IF;
308
309 IF cols_csr.trx_col_1 IS NOT NULL THEN
310 l_join_string := l_join_string ||
311 ' TIV.'|| cols_csr.trx_col_1 ||l_outerjoin ||
312 ' = ENT.'|| cols_csr.src_col_1;
313 END IF;
314 IF cols_csr.trx_col_2 IS NOT NULL THEN
315 l_join_string := l_join_string ||
316 ' AND TIV.'|| cols_csr.trx_col_2 ||l_outerjoin ||
317 ' = ENT.'|| cols_csr.src_col_2;
318 END IF;
319 IF cols_csr.trx_col_3 IS NOT NULL THEN
320 l_join_string := l_join_string ||
321 ' AND TIV.'|| cols_csr.trx_col_3 ||l_outerjoin ||
322 ' = ENT.'|| cols_csr.src_col_3;
323 END IF;
324 IF cols_csr.trx_col_4 IS NOT NULL THEN
325 l_join_string := l_join_string ||
326 ' AND TIV.'|| cols_csr.trx_col_4 ||l_outerjoin ||
327 ' = ENT.'|| cols_csr.src_col_4;
328 END IF;
329 END IF;
330 END LOOP;
331 l_index := 0;
332 --Check if the object is synonym or not.(Added and commented for Bug 13596822)
333 FOR cols_csr IN
334 (SELECT xem.column_name column_name
335 ,xem.column_title PROMPT
336 ,utc.data_type data_type
337 FROM (SELECT t.table_name , t.column_name ,t.data_type
338 FROM user_tab_columns t , user_objects o
339 WHERE t.table_name = o.object_name
340 AND o.object_name = cur_trx.reporting_view_name
341 AND o.object_type <> 'SYNONYM'
342 UNION ALL
343 SELECT dt.table_name, dt.column_name , dt.data_type
344 FROM dba_tab_columns dt
345 WHERE (dt.table_name , dt.owner)
346 IN ( SELECT s.table_name , s.table_owner
347 FROM user_synonyms s , user_objects o
348 WHERE 1 = 1
349 AND o.object_name = cur_trx.reporting_view_name
350 AND o.object_type = 'SYNONYM'
351 AND s.synonym_name = o.object_name ) ) utc,
352 xla_event_mappings_vl xem
353 WHERE xem.application_id = cur_trx.application_id
354 AND xem.entity_code = cur_trx.entity_code
355 AND xem.event_class_code = cur_trx.event_class_code
356 AND utc.column_name = xem.column_name
357 ORDER BY xem.user_sequence)
358
359 /*
360 (SELECT xem.column_name column_name
361 ,xem.column_title PROMPT
362 ,utc.data_type data_type
363 FROM xla_event_mappings_vl xem
364 ,user_tab_columns utc
365 WHERE xem.application_id = cur_trx.application_id
366 AND xem.entity_code = cur_trx.entity_code
367 AND xem.event_class_code = cur_trx.event_class_code
368 AND utc.table_name = cur_trx.reporting_view_name
369 AND utc.column_name = xem.column_name
370 ORDER BY xem.user_sequence) */
371 LOOP
372
373 l_index := l_index + 1;
374
375 --
376 -- getting the PROMPTs to be displayed
377 --
378 --l_col_array(l_index).f1 := ''''||cols_csr.PROMPT||'''';
379 l_col_array(l_index).f1 := ''''||REPLACE (cols_csr.PROMPT, '''', '''''')||''''; -- bug 7636128
380
381 ---
382 -- getting the columns to be displayed
383 ---
384 IF cols_csr.data_type = 'VARCHAR2' THEN
385 l_col_array(l_index).f2 := 'TIV.'|| cols_csr.column_name;
386 ELSE
387 l_col_array(l_index).f2 := 'to_char(TIV.'|| cols_csr.column_name||')';
388 END IF;
389 END LOOP;
390 END IF;
391 --------------------------------------------------------------------------
392 -- building the string to be added to the SELECT clause
393 --------------------------------------------------------------------------
394 l_col_string := l_col_string ||
395 l_col_array(1).f1||'||''|''||'||l_col_array(1).f2;
396
397 FOR i IN 2..l_col_array.count LOOP
398 l_col_string := l_col_string ||'||''|''||'||l_col_array(i).f1
399 ||'||''|''||'||l_col_array(i).f2;
400 END LOOP;
401 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
402 trace
403 (p_msg => 'event class code = '||cur_trx.event_class_code||' l_col_string = '||l_col_string
404 ,p_level => C_LEVEL_STATEMENT
405 ,p_module => l_log_module);
406 END IF;
407 l_trx_id_str := l_trx_id_str||' WHEN xet.event_class_code = '''
408 ||cur_trx.event_class_code||''' THEN ( SELECT '||l_col_string
409 ||' FROM '||l_view_name ||' WHERE '|| l_join_string ||' )' ;
410
411 END IF;
412 END LOOP;
413
414 l_trx_id_str := l_trx_id_str ||' END ';
415
416 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
417 trace('get_transaction_id.End'
418 ,C_LEVEL_PROCEDURE, l_log_module);
419 END IF;
420
421 RETURN l_trx_id_str;
422
423 EXCEPTION
424 WHEN OTHERS THEN
425 xla_exceptions_pkg.raise_message
426 (p_location => 'xla_multiperiod_rprtg_pkg.get_transaction_id ');
427
428 END get_transaction_id;
429
430 PROCEDURE build_xml_sql (p_accounting_batch_id IN NUMBER) IS
431 l_log_module VARCHAR2(240);
432 BEGIN
433
434 IF g_log_enabled THEN
435 l_log_module := C_DEFAULT_MODULE||'.build_xml_sql';
436 END IF;
437
438 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
439 trace('build_xml_sql.Begin'
440 ,C_LEVEL_PROCEDURE, l_log_module);
441 trace
442 (p_msg => 'p_accounting_batch_id = '||to_char(p_accounting_batch_id)
443 ,p_level => C_LEVEL_PROCEDURE
444 ,p_module => l_log_module);
445 END IF;
446
447 xla_multiperiod_rprtg_pkg.C_SUMMARY_QUERY :=
448 ' SELECT xec.event_class_code EVENT_CLASS_CODE
449 ,xec.name EVENT_CLASS
450 ,xgl.ledger_id LEDGER_ID
451 ,xgl.name LEDGER
452 ,lk1.meaning ACTUAL
453 ,lk2.meaning BUDGET
454 ,lk3.meaning ENCUMBRANCE
455 ,ent.entity_id NUMBER_OF_DOC
456 ,DECODE(xah.balance_type_code,''A''
457 ,xah.accounting_entry_status_code) ACTUAL_B
458 ,DECODE(xah.balance_type_code,''B''
459 ,xah.accounting_entry_status_code) BUDGET_B
460 ,DECODE(xah.balance_type_code,''E''
461 ,xah.accounting_entry_status_code) ENCUMBRANCE_B
462 FROM xla_ae_headers xah
463 ,xla_gl_ledgers_v xgl
464 ,xla_events xae
465 ,xla_event_classes_tl xec
466 ,xla_event_types_b xet
467 ,xla_transaction_entities ent
468 ,xla_lookups lk1
469 ,xla_lookups lk2
470 ,xla_lookups lk3
471 WHERE xgl.ledger_id = xah.ledger_id
472 AND xec.application_id = xet.application_id
473 AND xec.event_class_code = xet.event_class_code
474 AND xec.language = USERENV(''LANG'')
475 AND ent.entity_id = xae.entity_id
476 AND xet.application_id = xae.application_id
477 AND xet.event_type_code = xae.event_type_code
478 AND xae.event_id = xah.event_id
479 AND lk1.lookup_type = ''XLA_BALANCE_TYPE''
480 AND lk1.lookup_code = ''A''
481 AND lk2.lookup_type = ''XLA_BALANCE_TYPE''
482 AND lk2.lookup_code = ''B''
483 AND lk3.lookup_type = ''XLA_BALANCE_TYPE''
484 AND lk3.lookup_code = ''E'' ';
485
486 xla_multiperiod_rprtg_pkg.xah_appl_filter := ' AND xah.application_id = '||
487 to_char(xla_multiperiod_rprtg_pkg.p_application_id);
488 xla_multiperiod_rprtg_pkg.xae_appl_filter := ' AND xae.application_id = '||
489 to_char(xla_multiperiod_rprtg_pkg.p_application_id);
490 xla_multiperiod_rprtg_pkg.ent_appl_filter := ' AND ent.application_id = '||
491 to_char(xla_multiperiod_rprtg_pkg.p_application_id);
492 xla_multiperiod_rprtg_pkg.xal_appl_filter := ' AND xal.application_id = '||
493 to_char(xla_multiperiod_rprtg_pkg.p_application_id);
494
495 xla_multiperiod_rprtg_pkg.acct_batch_filter := ' AND xah.accounting_batch_id = '||
496 to_char(p_accounting_batch_id);
497
498 xla_multiperiod_rprtg_pkg.C_TRANSFER_QUERY :=
499 ' SELECT xgl.name LEDGER
500 ,xgl.ledger_id LEDGER_ID
501 ,lk1.meaning ACTUAL
502 ,lk2.meaning BUDGET
503 ,lk3.meaning ENCUMBRANCE
504 ,sum(decode(xah.balance_type_code,''A'',1,0))
505 ACTUAL_B
506 ,sum(decode(xah.balance_type_code,''B'',1,0))
507 BUDGET_B
508 ,sum(decode(xah.balance_type_code,''E'',1,0))
509 ENCUMBRANCE_B
510 FROM xla_ae_headers xah
511 ,xla_gl_ledgers_v xgl
512 ,xla_lookups lk1
513 ,xla_lookups lk2
514 ,xla_lookups lk3
515 WHERE xgl.ledger_id = xah.ledger_id
516 AND xah.gl_transfer_status_code = ''Y''
517 AND lk1.lookup_type = ''XLA_BALANCE_TYPE''
518 AND lk1.lookup_code = ''A''
519 AND lk2.lookup_type = ''XLA_BALANCE_TYPE''
520 AND lk2.lookup_code = ''B''
521 AND lk3.lookup_type = ''XLA_BALANCE_TYPE''
522 AND lk3.lookup_code = ''E'' ';
523
524 xla_multiperiod_rprtg_pkg.C_GENERAL_ERRORS_QUERY :=
525 ' SELECT ERR.MESSAGE_NUMBER ERROR_NO
526 ,ERR.ENCODED_MSG ERROR_MSG
527 ,ERR.AE_LINE_NUM LINE_NUM
528 FROM XLA_ACCOUNTING_ERRORS ERR
529 ,XLA_AE_HEADERS XAH
530 WHERE err.ae_header_id = xah.ae_header_id
531 AND err.application_id = xah.application_id ';
532
533 xla_multiperiod_rprtg_pkg.C_RECOGNITION_COLS_QUERY :=
534 ' SELECT xah.event_id event_id
535 ,xec.name event_class
536 ,xet.name event_type
537 ,xae.event_number event_number
538 ,to_char(xae.event_date,''YYYY-MM-DD'')
539 event_date
540 ,xah.ae_header_id ae_header_id
541 ,gld.name ledger
542 ,to_char(xah.accounting_date,''YYYY-MM-DD'')
543 gl_date
544 ,gld.currency_code ledger_currency
545 ,xpr.name aad_name
546 ,xah.product_rule_version aad_version
547 ,xah.description description
548 ,lk1.meaning journal_entry_status
549 ,lk3.meaning mpa_type
550 ,seqv2.header_name acounting_sequence_name
551 ,seqv2.version_name acounting_sequence_version
552 ,xah.completion_acct_seq_value accounting_sequence_number
553 ,seqv3.header_name reporting_sequence_name
554 ,seqv3.version_name reporting_sequence_version
555 ,xah.close_acct_seq_value reporting_sequence_number
556 ,seq.name document_sequence_name
557 ,xah.doc_sequence_value document_sequence_value
558 ,xal.ae_line_num ae_line_num
559 ,lk2.meaning accounting_class
560 ,xal.displayed_line_number line_number
561 ,fnd_flex_ext.get_segs(''SQLGL'', ''GL#'',
562 gld.chart_of_accounts_id, xal.code_combination_id) account
563 ,xal.currency_code currency
564 ,xal.entered_dr entered_debit
565 ,xal.entered_cr entered_credit
566 ,xal.accounted_dr accounted_debit
567 ,xal.accounted_cr accounted_credit
568 ,sum(xal.accounted_dr) over (partition by xal.ae_header_id)
569 total_accted_debits
570 ,sum(xal.accounted_cr) over (partition by xal.ae_header_id)
571 total_accted_credits ';
572
573 xla_multiperiod_rprtg_pkg.C_RECOGNITION_FROM_QUERY :=
574 ' FROM xla_ae_headers xah
575 ,xla_events xae
576 ,xla_event_types_tl xet
577 ,xla_event_classes_tl xec
578 ,xla_transaction_entities ent
579 ,xla_gl_ledgers_v gld
580 ,xla_product_rules_tl xpr
581 ,xla_lookups lk1
582 ,xla_lookups lk2
583 ,xla_lookups lk3
584 ,xla_ae_lines xal
585 ,fnd_document_sequences seq
586 ,fun_seq_versions seqv2
587 ,fun_seq_versions seqv3 ';
588
589 xla_multiperiod_rprtg_pkg.C_RECOGNITION_WHR_QUERY :=
590 ' WHERE xec.application_id = xet.application_id
591 AND xec.event_class_code = xet.event_class_code
592 AND xec.language = USERENV(''LANG'')
593 AND ent.application_id = xet.application_id
594 AND ent.entity_code = xet.entity_code
595 AND xet.application_id = xae.application_id
596 AND xet.event_type_code = xae.event_type_code
597 AND xah.entity_id = ent.entity_id
598 AND xet.language = USERENV(''LANG'')
599 AND xpr.amb_context_code = xah.amb_context_code
600 AND xpr.application_id = xah.application_id
601 AND xpr.product_rule_type_code = xah.product_rule_type_code
602 AND xpr.product_rule_code = xah.product_rule_code
603 AND xpr.language = USERENV(''LANG'')
604 AND gld.ledger_id = xah.ledger_id
605 AND xal.application_id = xah.application_id
606 AND xal.ae_header_id = xah.ae_header_id
607 AND xae.application_id = xah.application_id
608 AND xae.event_id = xah.event_id
609 AND seq.doc_sequence_id(+) = xah.doc_sequence_id
610 AND seqv2.seq_version_id(+) = xah.completion_acct_seq_version_id
611 AND seqv3.seq_version_id(+) = xah.close_acct_seq_version_id
612 AND lk1.lookup_type = ''XLA_ACCOUNTING_ENTRY_STATUS''
613 AND lk1.lookup_code = xah.accounting_entry_status_code
614 AND lk2.lookup_type = ''XLA_ACCOUNTING_CLASS''
615 AND lk2.lookup_code = xal.accounting_class_code
616 AND lk3.lookup_type = ''XLA_MPA_TYPE''
617 AND lk3.lookup_code = decode(xal.mpa_accrual_entry_flag,''Y'',''M'',''R'')
618 AND xah.accounting_entry_status_code in (''D'',''F'')
619 AND xah.parent_ae_header_id IS NOT NULL
620 AND xah.parent_ae_line_num IS NOT NULL ';
621
622 xla_multiperiod_rprtg_pkg.C_ACCRUAL_RVRSL_COLS_QUERY :=
623 ' SELECT xah.event_id event_id
624 ,xec.name event_class
625 ,xet.name event_type
626 ,xae.event_number event_number
627 ,to_char(xae.event_date,''YYYY-MM-DD'')
628 event_date
629 ,xah.ae_header_id ae_header_id
630 ,gld.name ledger
631 ,to_char(xah.accounting_date,''YYYY-MM-DD'')
632 gl_date
633 ,gld.currency_code ledger_currency
634 ,xpr.name aad_name
635 ,xah.product_rule_version aad_version
636 ,xah.description description
637 ,lk1.meaning journal_entry_status
638 ,lk3.meaning mpa_type
639 ,seqv2.header_name acounting_sequence_name
640 ,seqv2.version_name acounting_sequence_version
641 ,xah.completion_acct_seq_value accounting_sequence_number
642 ,seqv3.header_name reporting_sequence_name
643 ,seqv3.version_name reporting_sequence_version
644 ,xah.close_acct_seq_value reporting_sequence_number
645 ,seq.name document_sequence_name
646 ,xah.doc_sequence_value document_sequence_value
647 ,xal.ae_line_num ae_line_num
648 ,lk2.meaning accounting_class
649 ,xal.displayed_line_number line_number
650 ,fnd_flex_ext.get_segs(''SQLGL'', ''GL#'',
651 gld.chart_of_accounts_id, xal.code_combination_id) account
652 ,xal.currency_code currency
653 ,xal.entered_dr entered_debit
654 ,xal.entered_cr entered_credit
655 ,xal.accounted_dr accounted_debit
656 ,xal.accounted_cr accounted_credit
657 ,sum(xal.accounted_dr) over (partition by xal.ae_header_id)
658 total_accted_debits
659 ,sum(xal.accounted_cr) over (partition by xal.ae_header_id)
660 total_accted_credits ';
661
662 xla_multiperiod_rprtg_pkg.C_ACCRUAL_RVRSL_FROM_QUERY :=
663 ' FROM xla_ae_headers xah
664 ,xla_events xae
665 ,xla_event_types_tl xet
666 ,xla_event_classes_tl xec
667 ,xla_transaction_entities ent
668 ,xla_gl_ledgers_v gld
669 ,xla_product_rules_tl xpr
670 ,xla_lookups lk1
671 ,xla_lookups lk2
672 ,xla_lookups lk3
673 ,xla_ae_lines xal
674 ,fnd_document_sequences seq
675 ,fun_seq_versions seqv2
676 ,fun_seq_versions seqv3 ';
677
678 xla_multiperiod_rprtg_pkg.C_ACCRUAL_RVRSL_WHR_QUERY :=
679 ' WHERE xec.application_id = xet.application_id
680 AND xec.event_class_code = xet.event_class_code
681 AND xec.language = USERENV(''LANG'')
682 AND ent.application_id = xet.application_id
683 AND ent.entity_code = xet.entity_code
684 AND xet.application_id = xae.application_id
685 AND xet.event_type_code = xae.event_type_code
686 AND xah.entity_id = ent.entity_id
687 AND xet.language = USERENV(''LANG'')
688 AND xpr.amb_context_code = xah.amb_context_code
689 AND xpr.application_id = xah.application_id
690 AND xpr.product_rule_type_code = xah.product_rule_type_code
691 AND xpr.product_rule_code = xah.product_rule_code
692 AND xpr.language = USERENV(''LANG'')
693 AND gld.ledger_id = xah.ledger_id
694 AND xal.application_id = xah.application_id
695 AND xal.ae_header_id = xah.ae_header_id
696 AND xae.application_id = xah.application_id
697 AND xae.event_id = xah.event_id
698 AND seq.doc_sequence_id(+) = xah.doc_sequence_id
699 AND seqv2.seq_version_id(+) = xah.completion_acct_seq_version_id
700 AND seqv3.seq_version_id(+) = xah.close_acct_seq_version_id
701 AND lk1.lookup_type = ''XLA_ACCOUNTING_ENTRY_STATUS''
702 AND lk1.lookup_code = xah.accounting_entry_status_code
703 AND lk2.lookup_type = ''XLA_ACCOUNTING_CLASS''
704 AND lk2.lookup_code = xal.accounting_class_code
705 AND lk3.lookup_type = ''XLA_MPA_TYPE''
706 AND lk3.lookup_code = decode(xah.accrual_reversal_flag,''Y'',''A'',''V'')
707 AND xah.accounting_entry_status_code in (''D'',''F'')
708 AND xah.parent_ae_header_id IS NOT NULL
709 AND xah.parent_ae_line_num IS NULL ';
710
711 xla_multiperiod_rprtg_pkg.C_ERRORS_COLS_QUERY :=
712 ' SELECT xah.event_id event_id
713 ,xec.name event_class
714 ,xet.name event_type
715 ,xae.event_number event_number
716 ,to_char(xae.event_date,''YYYY-MM-DD'')
717 event_date
718 ,xah.ae_header_id ae_header_id
719 ,gld.name ledger
720 ,to_char(xah.accounting_date,''YYYY-MM-DD'')
721 gl_date
722 ,gld.currency_code ledger_currency
723 ,xpr.name aad_name
724 ,xah.product_rule_version aad_version
725 ,xah.description description
726 ,lk1.meaning journal_entry_status
727 ,lk3.meaning mpa_type
728 ,seqv2.header_name acounting_sequence_name
729 ,seqv2.version_name acounting_sequence_version
730 ,xah.completion_acct_seq_value accounting_sequence_number
731 ,seqv3.header_name reporting_sequence_name
732 ,seqv3.version_name reporting_sequence_version
733 ,xah.close_acct_seq_value reporting_sequence_number
734 ,seq.name document_sequence_name
735 ,xah.doc_sequence_value document_sequence_value
736 ,xal.ae_line_num ae_line_num
737 ,lk2.meaning accounting_class
738 ,xal.displayed_line_number line_number
739 ,fnd_flex_ext.get_segs(''SQLGL'', ''GL#'',
740 gld.chart_of_accounts_id, xal.code_combination_id) account
741 ,xal.currency_code currency
742 ,xal.entered_dr entered_debit
743 ,xal.entered_cr entered_credit
744 ,xal.accounted_dr accounted_debit
745 ,xal.accounted_cr accounted_credit
746 ,sum(xal.accounted_dr) over (partition by xal.ae_header_id)
747 total_accted_debits
748 ,sum(xal.accounted_cr) over (partition by xal.ae_header_id)
749 total_accted_credits
750 ,err.message_number error_number
751 ,err.encoded_msg error_message ';
752
753 xla_multiperiod_rprtg_pkg.C_ERRORS_FROM_QUERY :=
754 ' FROM xla_ae_headers xah
755 ,xla_events xae
756 ,xla_event_types_tl xet
757 ,xla_event_classes_tl xec
758 ,xla_transaction_entities ent
759 ,xla_gl_ledgers_v gld
760 ,xla_product_rules_tl xpr
761 ,xla_lookups lk1
762 ,xla_lookups lk2
763 ,xla_lookups lk3
764 ,xla_ae_lines xal
765 ,fnd_document_sequences seq
766 ,fun_seq_versions seqv2
767 ,fun_seq_versions seqv3
768 ,xla_accounting_errors err ';
769
770 xla_multiperiod_rprtg_pkg.C_ERRORS_WHR_QUERY :=
771 ' WHERE xec.application_id = xet.application_id
772 AND xec.event_class_code = xet.event_class_code
773 AND xec.language = USERENV(''LANG'')
774 AND ent.application_id = xet.application_id
775 AND ent.entity_code = xet.entity_code
776 AND xet.application_id = xae.application_id
777 AND xet.event_type_code = xae.event_type_code
778 AND xah.entity_id = ent.entity_id
779 AND xet.language = USERENV(''LANG'')
780 AND xpr.amb_context_code = xah.amb_context_code
781 AND xpr.application_id = xah.application_id
782 AND xpr.product_rule_type_code = xah.product_rule_type_code
783 AND xpr.product_rule_code = xah.product_rule_code
784 AND xpr.language = USERENV(''LANG'')
785 AND gld.ledger_id = xah.ledger_id
786 AND xal.application_id = xah.application_id
787 AND xal.ae_header_id = xah.ae_header_id
788 AND xae.application_id = xah.application_id
789 AND xae.event_id = xah.event_id
790 AND seq.doc_sequence_id(+) = xah.doc_sequence_id
791 AND seqv2.seq_version_id(+) = xah.completion_acct_seq_version_id
792 AND seqv3.seq_version_id(+) = xah.close_acct_seq_version_id
793 AND lk1.lookup_type = ''XLA_ACCOUNTING_ENTRY_STATUS''
794 AND lk1.lookup_code = xah.accounting_entry_status_code
795 AND lk2.lookup_type = ''XLA_ACCOUNTING_CLASS''
796 AND lk2.lookup_code = xal.accounting_class_code
797 AND lk3.lookup_type = ''XLA_MPA_TYPE''
798 AND lk3.lookup_code = (CASE WHEN xah.accrual_reversal_flag = ''Y''
799 THEN ''A''
800 WHEN xah.parent_ae_header_id IS NOT NULL
801 AND xah.parent_ae_line_num IS NULL
802 THEN ''V''
803 WHEN xal.mpa_accrual_entry_Flag = ''Y''
804 THEN ''M''
805 ELSE ''R'' END)
806 AND xal.application_id(+) = err.application_id
807 AND xal.ae_header_id(+) = err.ae_header_id
808 AND xal.ae_line_num(+) = err.ae_line_num
809 AND xah.accounting_entry_status_code NOT IN (''D'',''F'')
810 AND xah.parent_ae_header_id IS NOT NULL ';
811
812 IF p_report = 'D' THEN
813 xla_multiperiod_rprtg_pkg.p_trx_identifiers := get_transaction_id(xla_multiperiod_rprtg_pkg.p_application_id
814 ,xla_multiperiod_rprtg_pkg.p_ledger_id
815 ,xla_multiperiod_rprtg_pkg.p_end_date
816 ,xla_multiperiod_rprtg_pkg.p_process_category_code)
817 ||' USERIDS ';
818 ELSE
819 xla_multiperiod_rprtg_pkg.p_trx_identifiers := ',CASE WHEN 1<1 THEN NULL END USERIDS ';
820 END IF;
821
822 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
823 trace('build_xml_sql.End'
824 ,C_LEVEL_PROCEDURE, l_log_module);
825 END IF;
826
827 EXCEPTION
828 WHEN OTHERS THEN
829 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
830 trace('build_xml_sql.End with Error'
831 ,C_LEVEL_PROCEDURE, l_log_module);
832 END IF;
833 xla_exceptions_pkg.raise_message
834 (p_location =>
835 'xla_multiperiod_rprtg_pkg.build_xml_sql');
836 END build_xml_sql;
837
838
839 FUNCTION beforeReport RETURN BOOLEAN IS
840 l_errbuf VARCHAR2(2000);
841 l_log_module VARCHAR2(240);
842 BEGIN
843 IF g_log_enabled THEN
844 l_log_module := C_DEFAULT_MODULE||'.beforeReport';
845 END IF;
846
847 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
848 trace('beforeReport.Begin',C_LEVEL_PROCEDURE,l_log_module);
849 END IF;
850
851 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
852 trace('p_application_id = '|| to_char(p_application_id),
853 C_LEVEL_STATEMENT, l_log_module);
854 trace('p_ledger_id = '|| to_char(p_ledger_id),
855 C_LEVEL_STATEMENT, l_log_module);
856 trace('p_process_category_code = '|| to_char(p_process_category_code),
857 C_LEVEL_STATEMENT, l_log_module);
858 trace('p_end_date = '|| to_char(p_end_date,'DD-MON-YYYY'),
859 C_LEVEL_STATEMENT, l_log_module);
860 trace('p_errors_only_flag = '|| to_char(p_errors_only),
861 C_LEVEL_STATEMENT, l_log_module);
862 trace('p_transfer_to_gl_flag = '|| to_char(p_transfer_to_gl),
863 C_LEVEL_STATEMENT, l_log_module);
864 trace('p_post_in_gl_flag = '|| to_char(p_post_in_gl),
865 C_LEVEL_STATEMENT, l_log_module);
866 trace('p_gl_batch_name = '|| p_gl_batch_name,
867 C_LEVEL_STATEMENT, l_log_module);
868 trace('p_valuation_method_code = '|| to_char(p_valuation_method_code),
869 C_LEVEL_STATEMENT, l_log_module);
870 trace('p_security_int_1 = '|| to_char(p_security_int_1),
871 C_LEVEL_STATEMENT, l_log_module);
872 trace('p_security_int_2 = '|| to_char(p_security_int_2),
873 C_LEVEL_STATEMENT, l_log_module);
874 trace('p_security_int_3 = '|| to_char(p_security_int_3),
875 C_LEVEL_STATEMENT, l_log_module);
876 trace('p_security_char_1 = '|| to_char(p_security_char_1),
877 C_LEVEL_STATEMENT, l_log_module);
878 trace('p_security_char_2 = '|| to_char(p_security_char_2),
879 C_LEVEL_STATEMENT, l_log_module);
880 trace('p_security_char_3 = '|| to_char(p_security_char_3),
881 C_LEVEL_STATEMENT, l_log_module);
882 END IF;
883
884 run_report(p_errbuf => l_errbuf
885 ,p_retcode => C_RETURN_CODE
886 ,p_application_id => xla_multiperiod_rprtg_pkg.p_application_id
887 ,p_ledger_id => xla_multiperiod_rprtg_pkg.p_ledger_id
888 ,p_process_category_code => xla_multiperiod_rprtg_pkg.p_process_category_code
889 ,p_end_date => xla_multiperiod_rprtg_pkg.p_end_date
890 ,p_errors_only_flag => xla_multiperiod_rprtg_pkg.p_errors_only
891 ,p_transfer_to_gl_flag => xla_multiperiod_rprtg_pkg.p_transfer_to_gl
892 ,p_post_in_gl_flag => xla_multiperiod_rprtg_pkg.p_post_in_gl
893 ,p_gl_batch_name => xla_multiperiod_rprtg_pkg.p_gl_batch_name
894 ,p_valuation_method_code => xla_multiperiod_rprtg_pkg.p_valuation_method_code
895 ,p_security_int_1 => xla_multiperiod_rprtg_pkg.p_security_int_1
896 ,p_security_int_2 => xla_multiperiod_rprtg_pkg.p_security_int_2
897 ,p_security_int_3 => xla_multiperiod_rprtg_pkg.p_security_int_3
898 ,p_security_char_1 => xla_multiperiod_rprtg_pkg.p_security_char_1
899 ,p_security_char_2 => xla_multiperiod_rprtg_pkg.p_security_char_2
900 ,p_security_char_3 => xla_multiperiod_rprtg_pkg.p_security_char_3);
901
902 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
903 trace('C_RETURN_CODE = '||C_RETURN_CODE,C_LEVEL_STATEMENT ,l_log_module);
904 trace('beforeReport.End'
905 ,C_LEVEL_PROCEDURE, l_log_module);
906 END IF;
907
908 RETURN TRUE;
909
910 EXCEPTION
911 WHEN OTHERS THEN
912 xla_exceptions_pkg.raise_message
913 (p_location => 'xla_multiperiod_rprtg_pkg.beforeReport ');
914
915 END beforeReport;
916
917 FUNCTION AfterReport RETURN BOOLEAN IS
918 l_temp BOOLEAN;
919 l_log_module VARCHAR2(240);
920 BEGIN
921
922 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
923 trace('BEGIN of AfterReport' ,C_LEVEL_STATEMENT ,l_log_module);
924 trace('C_RETURN_CODE = '||C_RETURN_CODE,C_LEVEL_STATEMENT ,l_log_module);
925 END IF;
926
927 IF C_RETURN_CODE = 0 THEN
928 NULL;
929 ELSIF C_RETURN_CODE = 1 THEN
930 l_temp := fnd_concurrent.set_completion_status
931 (status => 'WARNING'
932 ,message => NULL);
933 ELSE
934 l_temp := fnd_concurrent.set_completion_status
935 (status => 'ERROR'
936 ,message => NULL);
937 END IF;
938
939
940 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
941 trace('END of AfterReport'
942 ,C_LEVEL_STATEMENT
943 ,l_log_module);
944 END IF;
945 print_logfile(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')|| ' - End of the Report');
946 RETURN (TRUE);
947
948 EXCEPTION
949 WHEN OTHERS THEN
950 xla_exceptions_pkg.raise_message
951 (p_location => 'xla_multiperiod_rprtg_pkg.AfterReport');
952
953 END afterReport;
954
955 BEGIN
956 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
957 g_log_enabled := fnd_log.test
958 (log_level => g_log_level
959 ,MODULE => C_DEFAULT_MODULE);
960
961 IF NOT g_log_enabled THEN
962 g_log_level := C_LEVEL_LOG_DISABLED;
963 END IF;
964 END xla_multiperiod_rprtg_pkg;