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