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