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