[Home] [Help]
PACKAGE BODY: APPS.XLA_PERIOD_CLOSE_EXP_PKG
Source
1 PACKAGE BODY XLA_PERIOD_CLOSE_EXP_PKG AS
2 -- $Header: xlarppcl.pkb 120.38.12010000.1 2008/07/29 10:06:51 appldev ship $
3 /*===========================================================================+
4 | Copyright (c) 2003 Oracle Corporation Belmont, California, USA |
5 | ALL rights reserved. |
6 +============================================================================+
7 | FILENAME |
8 | xlarppcl.pkb |
9 | |
10 | PACKAGE NAME |
11 | xla_period_close_exp_pkg |
12 | |
13 | DESCRIPTION |
14 | This package generates an XML extract for the Period Close Validation |
15 | program unit. A dynamic query is created based on the parameters that are |
16 | input and the data template is used to generate XML. The extract is |
17 | called either when the user submits a concurrent request or when a General |
18 | Ledger Period is closed. |
19 | |
20 | HISTORY |
21 | 26/07/2005 VS Koushik Created |
22 | 15/02/2006 VamsiKrishna Kasina Changed the package to use |
23 | Data Template. |
24 | 7/12/2007 ssawhney 6613827, perf fix changed NOT IN |
25 | to IN in C_EVENTS_WO_AAD |
26 | 7/2/2008 vkasina removed the event_date filter in |
27 | C_EVENTS_WO_AAD |
28 | 08/02/2008 sasingha bug 6805286: |
29 | STAMP_EVENTS_WO_AAD is more needed|
30 | and call to it are removed. |
31 | 24/04/2008 schodava bug 6981926: |
32 | Perf fix, added hint of index in |
33 | procedure get_transaction_id |
34 +===========================================================================*/
35
36 TYPE t_rec IS RECORD
37 (f1 VARCHAR2(80)
38 ,f2 VARCHAR2(80));
39 TYPE t_array IS TABLE OF t_rec INDEX BY BINARY_INTEGER;
40
41 --=============================================================================
42 -- *********** Local Trace Routine **********
43 --=============================================================================
44 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
45 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
46 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
47 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
48 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
49 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
50 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
51 C_DEFAULT_MODULE CONSTANT VARCHAR2(240)
52 := 'xla.plsql.xla_period_close_exp_pkg';
53
54 g_log_level NUMBER;
55 g_log_enabled BOOLEAN;
56 g_use_ledger_security VARCHAR2(1) :=
57 nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'), 'N');
58 g_access_set_id PLS_INTEGER := fnd_profile.value('GL_ACCESS_SET_ID');
59 g_sec_access_set_id PLS_INTEGER :=
60 fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID');
61
62 PROCEDURE param_list_sql
63 (p_application_id IN NUMBER
64 ,p_ledger_id IN NUMBER
65 ,p_object_type_code OUT NOCOPY VARCHAR2
66 ,p_je_source_name OUT NOCOPY VARCHAR2);
67
68 PROCEDURE build_query_sql
69 (p_application_id IN NUMBER
70 ,p_ledger_id IN NUMBER
71 ,p_period_from IN VARCHAR2
72 ,p_period_to IN VARCHAR2
73 ,p_event_class IN VARCHAR2
74 ,p_je_category IN VARCHAR2
75 ,p_object_type_code IN VARCHAR2
76 ,p_je_source_name IN VARCHAR2
77 ,p_mode IN VARCHAR2);
78
79 PROCEDURE trace
80 (p_msg IN VARCHAR2
81 ,p_level IN NUMBER
82 ,p_module IN VARCHAR2) IS
83 BEGIN
84 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
85 fnd_log.message(p_level, NVL(p_module,C_DEFAULT_MODULE));
86 ELSIF p_level >= g_log_level THEN
87 fnd_log.string(p_level, NVL(p_module,C_DEFAULT_MODULE), p_msg);
88 END IF;
89
90 EXCEPTION
91 WHEN xla_exceptions_pkg.application_exception THEN
92 RAISE;
93 WHEN OTHERS THEN
94 xla_exceptions_pkg.raise_message
95 (p_location => 'xla_period_close_exp_pkg.trace');
96 END trace;
97
98 --=============================================================================
99 -- *********** public procedures and functions **********
100 --=============================================================================
101 -- 1. run_report
102 --=============================================================================
103 PROCEDURE RUN_REPORT
104 (p_errbuf OUT NOCOPY VARCHAR2
105 ,p_retcode OUT NOCOPY NUMBER
106 ,p_application_id IN NUMBER
107 ,p_ledger_id IN NUMBER
108 ,p_period_from IN VARCHAR2
109 ,p_period_to IN VARCHAR2
110 ,p_event_class IN VARCHAR2
111 ,p_je_category IN VARCHAR2
112 ,p_mode IN VARCHAR2) IS
113
114 l_log_module VARCHAR2(240);
115
116 l_object_type_code gl_ledgers.object_type_code%TYPE;
117 l_je_source_name gl_je_sources.je_source_name%TYPE;
118
119 BEGIN
120 IF g_log_enabled THEN
121 l_log_module := C_DEFAULT_MODULE||'.run_report';
122 END IF;
123
124 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
125 trace('run_report.Begin',C_LEVEL_PROCEDURE,l_log_module);
126 END IF;
127
128 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
129 trace('p_application_id = '|| to_char(p_application_id),
130 C_LEVEL_STATEMENT, l_log_module);
131 trace('p_ledger_id = '|| to_char(p_ledger_id),
132 C_LEVEL_STATEMENT, l_log_module);
133 trace('p_period_from = '|| p_period_from,
134 C_LEVEL_STATEMENT,l_log_module);
135 trace('p_period_to = '|| p_period_to,
136 C_LEVEL_STATEMENT, l_log_module);
137 trace('p_mode = '|| p_mode,
138 C_LEVEL_STATEMENT, l_log_module);
139 trace('p_event_class = '|| p_event_class,
140 C_LEVEL_STATEMENT, l_log_module);
141 trace('p_je_category = '|| p_je_category,
142 C_LEVEL_STATEMENT, l_log_module);
143 END IF;
144
145 ----------------------------------------------------------------------------
146 -- Following sets the Security Context for the execution. This enables the
147 -- event API to respect the transaction security implementation
148 ----------------------------------------------------------------------------
149
150 IF p_application_id = 101 THEN
151 xla_security_pkg.set_security_context(602);
152 ELSE
153 xla_security_pkg.set_security_context(p_application_id);
154 END IF;
155
156 param_list_sql
157 (p_application_id => p_application_id
158 ,p_ledger_id => p_ledger_id
159 ,p_object_type_code => l_object_type_code
160 ,p_je_source_name => l_je_source_name);
161
162 build_query_sql
163 (p_application_id => p_application_id
164 ,p_ledger_id => p_ledger_id
165 ,p_period_from => p_period_from
166 ,p_period_to => p_period_to
167 ,p_event_class => p_event_class
168 ,p_je_category => p_je_category
169 ,p_object_type_code => l_object_type_code
170 ,p_je_source_name => l_je_source_name
171 ,p_mode => p_mode);
172
173 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
174 trace('run_report.End',C_LEVEL_PROCEDURE,l_Log_module);
175 END IF;
176 EXCEPTION
177 WHEN OTHERS THEN
178
179 xla_exceptions_pkg.raise_message
180 (p_location => 'xla_period_close_exp_pkg.run_report ');
181 END run_report;
182
183
184 --------------------------------------------------------------------
185 -- The following is procedure is actually no more needed and
186 -- can be removed.
187 -- There is no call to this procedure and this change is
188 -- made as part of bug fix 6805286
189 --------------------------------------------------------------------
190 PROCEDURE stamp_events_wo_aad
191 (p_application_id IN NUMBER
192 ,p_ledger_id IN VARCHAR2 -- 4949921
193 ,p_start_date IN DATE
194 ,p_end_date IN DATE) IS
195
196 l_set NUMBER;
197 l_return_status VARCHAR2(10);
198 l_log_module VARCHAR2(240);
199 l_stamp_query VARCHAR2(32000);
200 l_percl_query VARCHAR2(10000);
201 l_filters VARCHAR2(1000);
202 l_application_id xla_events.application_id%TYPE;
203
204 C_EVENTS_WO_AAD CONSTANT VARCHAR2(10000) :=
205 ' UPDATE xla_events xle
206 SET xle.event_status_code = ''P''
207 ,xle.process_status_code = ''P''
208 ,xle.last_update_date = sysdate
209 ,xle.last_updated_by = fnd_global.user_id
210 ,xle.last_update_login = fnd_global.login_id
211 ,xle.program_id = fnd_global.conc_program_id
212 ,xle.request_id = nvl(fnd_global.conc_request_id,0)
213 ,xle.program_application_id = fnd_global.prog_appl_id
214 WHERE event_type_code <> ''MANUAL''
215 AND event_type_code in
216 (SELECT xetb.event_type_code
217 FROM gl_ledgers glg,
218 xla_acctg_methods_b xam,
219 xla_acctg_method_rules xamr,
220 xla_prod_acct_headers xpah,
221 xla_event_types_b xetb
222 WHERE glg.sla_accounting_method_code = xam.accounting_method_code
223 AND glg.sla_accounting_method_type = xam.accounting_method_type_code
224 AND xam.accounting_method_code = xamr.accounting_method_code
225 AND xam.accounting_method_type_code =
226 xamr.accounting_method_type_code
227 AND xamr.application_id = xle.application_id
228 AND xetb.application_id = xpah.application_id
229 AND xetb.entity_code = xpah.entity_code
230 AND xetb.event_class_code = xpah.event_class_code
231 AND (substr(xpah.event_type_code,-4) = ''_ALL''
232 OR xetb.event_type_code = xpah.event_type_code)
233 AND (NVL(xam.enabled_flag,''N'') <> ''Y''
234 -- OR xle.event_date < xamr.start_date_active
235 -- OR xle.event_date > xamr.end_date_active
236 OR NVL(xpah.accounting_required_flag,''N'') <> ''Y'')
237 AND xpah.application_id = xamr.application_id
238 AND xpah.product_rule_type_code = xamr.product_rule_type_code
239 AND xpah.product_rule_code = xamr.product_rule_code
240 AND xpah.amb_context_code = xamr.amb_context_code
241 AND xpah.amb_context_code =
242 NVL(xla_profiles_pkg.get_value(''XLA_AMB_CONTEXT''),''DEFAULT'')
243 AND glg.ledger_id IN ($ledger_ids$))
244 AND xle.event_status_code IN ( ''U'',''I'')
245 AND xle.process_status_code IN (''I'',''U'',''R'',''D'',''E'')
246 AND $filters$';
247 -- For 6613827 changed the NOT IN from P,N to IN U and I.
248 -- For 6784591 added process_status_code filter.
249
250 BEGIN
251
252 IF g_log_enabled THEN
253 l_log_module := C_DEFAULT_MODULE||'.stamp_events_wo_aad';
254 END IF;
255
256 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
257 trace
258 ( p_msg => 'BEGIN of procedure stamp_events_wo_aad'
259 ,p_level => C_LEVEL_PROCEDURE
260 ,p_module => l_log_module);
261 END IF;
262
263 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
264 trace('p_application_id = ' ||
265 to_char(p_application_id), C_LEVEL_STATEMENT, l_log_module);
266 trace('p_ledger_id = '||
267 p_ledger_id, C_LEVEL_STATEMENT, l_log_module);
268 trace('p_start_date = '||
269 to_char(p_start_date,'DD-MON-YYYY'), C_LEVEL_STATEMENT, l_log_module);
270 trace('p_end_date = '||
271 to_char(p_end_date,'DD-MON-YYYY'), C_LEVEL_STATEMENT, l_log_module);
272 END IF;
273
274 l_percl_query := C_EVENTS_WO_AAD;
275
276 l_filters := 'xle.event_date between '''|| p_start_date ||''' and '''
277 || p_end_date||'''';
278
279 IF p_application_id <> 101 then
280 l_filters := l_filters || ' AND xle.application_id = '
281 || p_application_id;
282 END IF;
283
284 l_percl_query := REPLACE(l_percl_query, '$ledger_ids$',p_ledger_id);
285 l_percl_query := REPLACE(l_percl_query, '$filters$', l_filters);
286
287 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
288 trace
289 ( p_msg => 'l_percl_query'||l_percl_query
290 ,p_level => C_LEVEL_PROCEDURE
291 ,p_module => l_log_module);
292 END IF;
293
294 EXECUTE IMMEDIATE l_percl_query;
295
296 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
297 trace
298 ( p_msg => 'Number of events updated'||SQL%ROWCOUNT
299 ,p_level => C_LEVEL_PROCEDURE
300 ,p_module => l_log_module);
301 END IF;
302
303 EXCEPTION
304 WHEN NO_DATA_FOUND THEN
305 NULL;
306 WHEN xla_exceptions_pkg.application_exception THEN
307 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
308 trace( p_msg => 'End of procedure stamp_events_wo_aad'
309 ,p_level => C_LEVEL_PROCEDURE
310 ,p_module => l_log_module);
311 END IF;
312 RAISE;
313 END stamp_events_wo_aad;
314
315
316
317 PROCEDURE param_list_sql
318 (p_application_id IN NUMBER
319 ,p_ledger_id IN NUMBER
320 ,p_object_type_code OUT NOCOPY VARCHAR2
321 ,p_je_source_name OUT NOCOPY VARCHAR2) IS
322
323 l_log_module VARCHAR2(240);
324 BEGIN
325
326 IF g_log_enabled THEN
327 l_log_module := C_DEFAULT_MODULE||'.param_list_sql';
328 END IF;
329 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
330 trace('param_list_sql.Begin',C_LEVEL_PROCEDURE,l_Log_module);
331 END IF;
332
333 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
334 trace('p_application_id = '|| to_char(p_application_id),
335 C_LEVEL_STATEMENT,l_log_module);
336 trace('p_ledger_id = '|| to_char(p_ledger_id),
337 C_LEVEL_STATEMENT,l_log_module);
338 END IF;
339
340 --
341 -- Getting Translated value for all ID and codes
342 --
343
344 p_object_type_code := xla_report_utility_pkg.
345 get_ledger_object_type(p_ledger_id);
346
347 IF p_application_id = 101 THEN
348 p_je_source_name := NULL;
349 ELSE
350 SELECT gjst.je_source_name
351 INTO p_je_source_name
352 FROM xla_subledgers xls, gl_je_sources_tl gjst
353 WHERE xls.application_id = p_application_id
354 AND xls.je_source_name = gjst.je_source_name
355 AND gjst.language = USERENV('LANG');
356 END IF;
357
358 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
359 trace('p_object_type_code = '|| p_object_type_code,
360 C_LEVEL_STATEMENT,l_log_module);
361 trace('p_je_source_name = '|| p_je_source_name,
362 C_LEVEL_STATEMENT,l_log_module);
363 END IF;
364
365
366 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
367 trace('param_list_sql.End',C_LEVEL_PROCEDURE,l_Log_module);
368 END IF;
369
370 EXCEPTION
371 WHEN OTHERS THEN
372 -- trace mesg
373 xla_exceptions_pkg.raise_message
374 (p_location => 'xla_period_close_exp_pkg.param_list_sql');
375 END param_list_sql;
376
377 PROCEDURE get_period_start_end_dates
378 ( p_ledger_id IN NUMBER
379 ,p_period_from IN VARCHAR2
380 ,p_period_to IN VARCHAR2
381 ,p_start_date OUT NOCOPY DATE
382 ,p_end_date OUT NOCOPY DATE ) IS
383
384 l_log_module VARCHAR2(240);
385 gl_appl_id NUMBER := 101;
386
387 BEGIN
388
389 IF g_log_enabled THEN
390 l_log_module := C_DEFAULT_MODULE||'.get_period_start_end_dates';
391 END IF;
392
393 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
394 trace('get_period_start_end_dates.Begin'
395 ,C_LEVEL_PROCEDURE, l_log_module);
396 END IF;
397
398 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
399 trace('p_ledger_id = '|| to_char(p_ledger_id),
400 C_LEVEL_STATEMENT,l_log_module);
401 trace('p_period_from = '|| p_period_from,
402 C_LEVEL_STATEMENT,l_log_module);
403 trace('p_period_to = '|| p_period_to,
404 C_LEVEL_STATEMENT,l_log_module);
405 END IF;
406
407 SELECT start_date, end_date
408 INTO p_start_date, p_end_date
409 FROM gl_period_statuses glp
410 WHERE glp.period_name = p_period_from
411 AND glp.ledger_id = p_ledger_id
412 AND glp.adjustment_period_flag = 'N'
413 AND glp.application_id = gl_appl_id ;
414
415 IF p_period_from <> p_period_to THEN
416 SELECT end_date
417 INTO p_end_date
418 FROM gl_period_statuses glp
419 WHERE glp.period_name = p_period_to
420 AND glp.ledger_id = p_ledger_id
421 AND glp.adjustment_period_flag = 'N'
422 AND glp.application_id = gl_appl_id ;
423 END IF;
424
425 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
426 trace('get_period_start_end_dates.End'
427 ,C_LEVEL_PROCEDURE, l_log_module);
428 END IF;
429
430 EXCEPTION
431 WHEN OTHERS THEN
432 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
433 trace('get_period_start_end_dates.End with Error'
434 ,C_LEVEL_PROCEDURE, l_log_module);
435 END IF;
436 xla_exceptions_pkg.raise_message
437 (p_location =>
438 'xla_period_close_exp_pkg.get_period_start_end_dates');
439 END get_period_start_end_dates ;
440
441
442 PROCEDURE build_xml_sql IS
443 l_log_module VARCHAR2(240);
444 BEGIN
445
446 IF g_log_enabled THEN
447 l_log_module := C_DEFAULT_MODULE||'.build_xml_sql';
448 END IF;
449
450 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
451 trace('build_xml_sql.Begin'
452 ,C_LEVEL_PROCEDURE, l_log_module);
453 END IF;
454
455 C_EVENTS_COLS_QUERY :=
456 ' SELECT ent.ledger_id LEDGER_ID
457 ,gld.short_name LEDGER_SHORT_NAME
458 ,gld.name LEDGER_NAME
459 ,gld.description LEDGER_DESCRIPTION
460 ,gld.currency_code LEDGER_CURRENCY
461 ,gps.period_year PERIOD_YEAR
462 ,gps.period_num PERIOD_NUMBER
463 ,gps.period_name PERIOD_NAME
464 ,xle.application_id APPLICATION_ID
465 ,gjt.je_source_name JOURNAL_SOURCE
466 ,gjt.user_je_source_name USER_JE_SOURCE
467 ,xcl.event_class_code EVENT_CLASS_CODE
468 ,xcl.name EVENT_CLASS_NAME
469 ,gjct.je_category_name JOURNAL_CATEGORY_NAME
470 ,gjct.user_je_category_name USER_JE_CATEGORY_NAME
471 ,to_char(xle.event_date,''YYYY-MM-DD'') EVENT_DATE
472 ,xle.event_id EVENT_ID
473 ,xle.event_number EVENT_NUMBER
474 ,fnu.user_id CREATED_BY
475 ,fnu.user_name USER_NAME
476 ,to_char(xle.last_update_date,''YYYY-MM-DD'') LAST_UPDATE_DATE
477 ,to_char(xle.creation_date,''YYYY-MM-DD'') CREATION_DATE
478 ,ent.transaction_number TRANSACTION_NUMBER
479 ,to_char(xle.transaction_date,''YYYY-MM-DD'') TRANSACTION_DATE
480 ,xle.on_hold_flag ON_HOLD_FLAG
481 ,xlo2.meaning ON_HOLD
482 ,xtt.event_type_code EVENT_TYPE_CODE
483 ,xtt.name EVENT_TYPE_NAME
484 ,NULL BALANCE_TYPE_CODE
485 ,NULL BALANCE_TYPE
486 ,xlo1.meaning PRINT_STATUS ';
487
488 C_EVENTS_FROM_QUERY :=
489 ' FROM XLA_EVENTS XLE
490 ,XLA_TRANSACTION_ENTITIES ENT
491 ,XLA_SUBLEDGERS XLS
492 ,FND_USER FNU
493 ,GL_PERIOD_STATUSES GPS
494 ,GL_LEDGERS GLD
495 ,GL_JE_SOURCES_TL GJT
496 ,GL_JE_CATEGORIES_TL GJCT
497 ,XLA_EVENT_CLASSES_TL XCL
498 ,XLA_EVENT_TYPES_B XET
499 ,XLA_EVENT_TYPES_TL XTT
500 ,XLA_EVENT_CLASS_ATTRS XECA
501 ,XLA_LOOKUPS XLO1
502 ,XLA_LOOKUPS XLO2
503 ,XLA_LEDGER_OPTIONS XLP
504 WHERE xls.application_id = xle.application_id
505 AND xle.event_status_code IN (''I'',''U'')
506 AND xle.process_status_code IN (''U'',''D'',''E'',''R'',''I'')
507 AND xle.entity_id = ent.entity_id
508 AND ent.application_id = xle.application_id
509 AND ent.ledger_id = gps.ledger_id
510 AND gps.application_id = 101
511 AND xle.event_date BETWEEN gps.start_date AND gps.end_date
512 AND gld.ledger_id = ent.ledger_id
513 AND gjt.je_source_name = xls.je_source_name
514 AND gjt.LANGUAGE = USERENV(''LANG'')
515 AND fnu.user_id = xle.created_by
516 AND xet.application_id = xle.application_id
517 AND xet.event_type_code = xle.event_type_code
518 AND xtt.application_id = xet.application_id
519 AND xtt.event_type_code = xet.event_type_code
520 AND xtt.event_class_code = xet.event_class_code
521 AND xtt.entity_code = xet.entity_code
522 AND xtt.LANGUAGE = USERENV(''LANG'')
523 AND xcl.application_id = xet.application_id
524 AND xcl.entity_code = xet.entity_code
525 AND xcl.event_class_code = xet.event_class_code
526 AND xcl.application_id = ent.application_id
527 AND xcl.entity_code = ent.entity_code
528 AND xcl.LANGUAGE = USERENV(''LANG'')
529 AND xeca.application_id = xcl.application_id
530 AND xeca.entity_code = xcl.entity_code
531 AND xeca.event_class_code = xcl.event_class_code
532 AND xeca.je_category_name = gjct.je_category_name
533 AND gjct.language = USERENV(''LANG'')
534 AND xlo1.lookup_type = ''XLA_EVENT_STATUS''
535 AND xlo1.lookup_code = xle.event_status_code
536 AND xlo2.lookup_type = ''XLA_YES_NO''
537 AND xlo2.lookup_code = xle.on_hold_flag
538 AND ent.ledger_id = xlp.ledger_id
539 AND ent.application_id = xlp.application_id
540 AND xlp.capture_event_flag = ''Y''
541 AND NOT EXISTS (SELECT aeh.event_id
542 FROM XLA_AE_HEADERS aeh
543 WHERE aeh.application_id = xle.application_id
544 AND aeh.event_id = xle.event_id
545 )
546 AND ent.ledger_id IN ';
547
548 C_HEADERS_COLS_QUERY :=
549 ' SELECT /*+ leading(aeh) */ aeh.ledger_id LEDGER_ID
550 ,gld.short_name LEDGER_SHORT_NAME
551 ,gld.name LEDGER_NAME
552 ,gld.description LEDGER_DESCRIPTION
553 ,gld.currency_code LEDGER_CURRENCY
554 ,gps.period_year PERIOD_YEAR
555 ,gps.period_num PERIOD_NUMBER
556 ,gps.period_name PERIOD_NAME
557 ,xle.application_id APPLICATION_ID
558 ,gjt.je_source_name JOURNAL_SOURCE
559 ,gjt.user_je_source_name USER_JE_SOURCE
560 ,xcl.event_class_code EVENT_CLASS_CODE
561 ,xcl.name EVENT_CLASS_NAME
562 ,gjct.je_category_name JOURNAL_CATEGORY_NAME
563 ,gjct.user_je_category_name USER_JE_CATEGORY_NAME
564 ,to_char(aeh.accounting_date,''YYYY-MM-DD'') EVENT_DATE
565 ,xle.event_id EVENT_ID
566 ,xle.event_number EVENT_NUMBER
567 ,fnu.user_id CREATED_BY
568 ,fnu.user_name USER_NAME
569 ,to_char(aeh.last_update_date,''YYYY-MM-DD'') LAST_UPDATE_DATE
570 ,to_char(aeh.creation_date,''YYYY-MM-DD'') CREATION_DATE
571 ,ent.transaction_number TRANSACTION_NUMBER
572 ,to_char(xle.transaction_date,''YYYY-MM-DD'') TRANSACTION_DATE
573 ,xle.on_hold_flag ON_HOLD_FLAG
574 ,xlo2.meaning ON_HOLD
575 ,xet.event_type_code EVENT_TYPE_CODE
576 ,xtt.name EVENT_TYPE_NAME
577 ,aeh.balance_type_code BALANCE_TYPE_CODE
578 ,xlo5.meaning BALANCE_TYPE
579 ,xlo4.meaning PRINT_STATUS ';
580
581 C_HEADERS_FROM_QUERY :=
582 ' FROM XLA_AE_HEADERS AEH
583 ,XLA_EVENTS XLE
584 ,XLA_TRANSACTION_ENTITIES ENT
585 ,XLA_SUBLEDGERS XLS
586 ,FND_USER FNU
587 ,GL_PERIOD_STATUSES GPS
588 ,GL_LEDGERS GLD
589 ,GL_JE_SOURCES_TL GJT
590 ,GL_JE_CATEGORIES_TL GJCT
591 ,XLA_EVENT_TYPES_B XET
592 ,XLA_EVENT_TYPES_TL XTT
593 ,XLA_EVENT_CLASSES_TL XCL
594 ,XLA_LOOKUPS XLO2
595 ,XLA_LOOKUPS XLO4
596 ,XLA_LOOKUPS XLO5
597 WHERE xls.application_id = aeh.application_id
598 AND aeh.event_id = xle.event_id
599 AND aeh.application_id = xle.application_id
600 AND xle.entity_id = ent.entity_id
601 AND xle.application_id = ent.application_id
602 AND aeh.period_name = gps.period_name
603 AND aeh.ledger_id = gps.ledger_id
604 AND gps.application_id = 101
605 AND gld.ledger_id = aeh.ledger_id
606 AND fnu.user_id = aeh.created_by
607 AND gjt.je_source_name = xls.je_source_name
608 AND gjt.LANGUAGE = USERENV(''LANG'')
609 AND xet.application_id = xle.application_id
610 AND xet.event_type_code = aeh.event_type_code
611 AND xtt.application_id = xet.application_id
612 AND xtt.event_type_code = xet.event_type_code
613 AND xtt.entity_code = xet.entity_code
614 AND xtt.event_class_code = xet.event_class_code
615 AND xtt.LANGUAGE = USERENV(''LANG'')
616 AND xcl.application_id = xtt.application_id
617 AND xcl.entity_code = xtt.entity_code
618 AND xcl.event_class_code = xtt.event_class_code
619 AND xcl.application_id = ent.application_id
620 AND xcl.entity_code = ent.entity_code
621 AND xcl.LANGUAGE = USERENV(''LANG'')
622 AND gjct.je_category_name = aeh.je_category_name
623 AND gjct.LANGUAGE = USERENV(''LANG'')
624 AND aeh.gl_transfer_status_code IN (''N'',''E'')
625 AND xlo2.lookup_type = ''XLA_YES_NO''
626 AND xlo2.lookup_code = xle.on_hold_flag
627 AND xlo4.lookup_type = ''XLA_ACCOUNTING_ENTRY_STATUS''
628 AND xlo4.lookup_code = aeh.accounting_entry_status_code
629 AND xlo5.lookup_type = ''XLA_BALANCE_TYPE''
630 AND xlo5.lookup_code = aeh.balance_type_code
631 AND aeh.ledger_id IN ';
632
633 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
634 trace('build_xml_sql.End'
635 ,C_LEVEL_PROCEDURE, l_log_module);
636 END IF;
637
638 EXCEPTION
639 WHEN OTHERS THEN
640 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
641 trace('build_xml_sql.End with Error'
642 ,C_LEVEL_PROCEDURE, l_log_module);
643 END IF;
644 xla_exceptions_pkg.raise_message
645 (p_location =>
646 'xla_period_close_exp_pkg.build_xml_sql');
647 END build_xml_sql;
648
649 --changed Function get_transaction_id returning varchar2 to a procedure preeti/6204675
650
651 procedure get_transaction_id
652 (p_ledger_ids IN VARCHAR2
653 ,p_event_filter IN VARCHAR2
654 ,p_header_filter IN VARCHAR2) IS
655
656 C_EVENTS_HEADERS_QUERY VARCHAR2(8000) :=
657 'SELECT /*+ index(ent XLA_TRANSACTION_ENTITIES_U1) */ DISTINCT
658 xle.application_id APPLICATION_ID
659 ,xcl.entity_code ENTITY_CODE
660 ,xcl.event_class_code EVENT_CLASS_CODE
661 ,gjct.reporting_view_name REPORTING_VIEW_NAME
662 FROM xla_events xle
663 ,xla_event_types_b xcl
664 ,xla_event_class_attrs gjct
665 ,xla_transaction_entities ent
666 ,xla_ledger_options xlo
667 WHERE ent.entity_id = xle.entity_id
668 AND ent.application_id = xle.application_id
669 AND ent.ledger_id = xlo.ledger_id
670 AND ent.application_id = xlo.application_id
671 AND xlo.capture_event_flag = ''Y''
672 AND xcl.application_id = xle.application_id
673 AND xcl.event_type_code = xle.event_type_code
674 AND xcl.entity_code NOT IN (''MANUAL'',''THIRD_PARTY_MERGE'')
675 AND gjct.application_id = xcl.application_id
676 AND gjct.entity_code = xcl.entity_code
677 AND gjct.event_class_code = xcl.event_class_code
678 AND xle.event_status_code IN (''I'',''U'')
679 AND xle.process_status_code IN (''U'',''D'',''E'',''R'',''I'')
680 AND ent.ledger_id IN $ledger_ids$
681 $event_filter$
682 UNION ALL
683 SELECT DISTINCT
684 aeh.application_id APPLICATION_ID
685 ,xcl.entity_code ENTITY_CODE
686 ,xcl.event_class_code EVENT_CLASS_CODE
687 ,gjct.reporting_view_name REPORTING_VIEW_NAME
688 FROM xla_ae_headers aeh
689 ,xla_event_types_b xcl
690 ,xla_event_class_attrs gjct
691 ,xla_transaction_entities ent
692 WHERE xcl.application_id = aeh.application_id
693 AND xcl.event_type_code = aeh.event_type_code
694 AND gjct.application_id = xcl.application_id
695 AND gjct.entity_code = xcl.entity_code
696 AND gjct.event_class_code = xcl.event_class_code
697 AND ent.entity_id = aeh.entity_id
698 AND ent.application_id = aeh.application_id
699 AND xcl.entity_code NOT IN (''MANUAL'',''THIRD_PARTY_MERGE'') -- bug 6896350
700 AND aeh.gl_transfer_status_code IN (''N'',''E'') -- bug 6896350
701 AND aeh.ledger_id IN $ledger_ids$
702 $header_filter$';
703
704 cursor c1 is
705 SELECT application_id
706 ,entity_code
707 ,event_class_code
708 ,reporting_view_name
709 FROM xla_event_class_attrs;
710
711 TYPE l_event_class_tab IS TABLE of c1%ROWTYPE;
712 l_event_class_set l_event_class_tab;
713
714 l_col_array t_array;
715 l_null_col_array t_array;
716 l_trx_id_str VARCHAR2(32000):=NULL;
717 l_trx_id_str_temp VARCHAR2(32000):=NULL;
718 l_col_string VARCHAR2(10000) := NULL;
719 l_view_name VARCHAR2(800);
720 l_join_string VARCHAR2(10000) := NULL;
721 l_index INTEGER;
722 l_outerjoin VARCHAR2(300);
723 l_log_module VARCHAR2(240);
724 l_id_num number:=1;
725 BEGIN
726 IF g_log_enabled THEN
727 l_log_module := C_DEFAULT_MODULE||'.get_transaction_id';
728 END IF;
729 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
730 trace
731 (p_msg => 'BEGIN of function GET_TRANSACTION_ID'
732 ,p_level => C_LEVEL_PROCEDURE
733 ,p_module => l_log_module);
734 trace
735 (p_msg => 'p_ledger_ids = '||p_ledger_ids
736 ,p_level => C_LEVEL_PROCEDURE
737 ,p_module => l_log_module);
738 trace
739 (p_msg => 'p_event_filter = '||p_event_filter
740 ,p_level => C_LEVEL_PROCEDURE
741 ,p_module => l_log_module);
742 trace
743 (p_msg => 'p_header_filter = '||p_header_filter
744 ,p_level => C_LEVEL_PROCEDURE
745 ,p_module => l_log_module);
746 END IF;
747 l_trx_id_str := ',CASE WHEN 1<1 THEN NULL';
748
749 C_EVENTS_HEADERS_QUERY := replace(C_EVENTS_HEADERS_QUERY,
750 '$ledger_ids$',p_ledger_ids);
751 C_EVENTS_HEADERS_QUERY := replace(C_EVENTS_HEADERS_QUERY,
752 '$event_filter$',p_event_filter);
753 C_EVENTS_HEADERS_QUERY := replace(C_EVENTS_HEADERS_QUERY,
754 '$header_filter$',p_header_filter);
755
756 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
757 trace
758 (p_msg => 'C_EVENTS_HEADERS_QUERY = '||C_EVENTS_HEADERS_QUERY
759 ,p_level => C_LEVEL_STATEMENT
760 ,p_module => l_log_module);
761 END IF;
762
763
764 EXECUTE IMMEDIATE C_EVENTS_HEADERS_QUERY
765 BULK COLLECT INTO l_event_class_set;
766
767 IF l_event_class_set.count > 0 THEN
768 FOR k in l_event_class_set.FIRST .. l_event_class_set.LAST
769 LOOP
770
771 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
772 trace
773 (p_msg => 'inside loop count = '||k
774 ,p_level => C_LEVEL_STATEMENT
775 ,p_module => l_log_module);
776 END IF;
777
778 l_col_string := NULL;
779 l_view_name := NULL;
780 l_join_string := NULL;
781
782
783 --
784 -- creating a dummy array that contains "NULL" strings
785 --
786
787
788 FOR i IN 1..10 LOOP
789 l_null_col_array(i).f1 := 'NULL';
790 l_null_col_array(i).f2 := 'NULL';
791 END LOOP;
792 --
793 -- initiating the array that contains name of the columns to be
794 -- selected from the TID View.
795 --
796 l_col_array := l_null_col_array;
797
798 --
799 -- creating SELECT,FROM and WHERE clause strings when the reporting
800 -- view is defined for an Event Class.
801 --
802
803 IF l_event_class_set(k).reporting_view_name IS NOT NULL THEN
804 --
805 -- creating string to be added to FROM clause
806 --
807
808 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
809 trace
810 (p_msg => 'Inside when reporting view name is not null'
811 ,p_level => C_LEVEL_STATEMENT
812 ,p_module => l_log_module);
813 END IF;
814
815 l_view_name := l_event_class_set(k).reporting_view_name
816 || ' TIV';
817
818 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
819 trace
820 (p_msg => 'length of l_view_name = '||length(l_view_name)
821 ,p_level => C_LEVEL_STATEMENT
822 ,p_module => l_log_module);
823 END IF;
824
825 l_index := 0;
826
827 FOR cols_csr IN
828 (SELECT xid.transaction_id_col_name_1 trx_col_1
829 ,xid.transaction_id_col_name_2 trx_col_2
830 ,xid.transaction_id_col_name_3 trx_col_3
831 ,xid.transaction_id_col_name_4 trx_col_4
832 ,xid.source_id_col_name_1 src_col_1
833 ,xid.source_id_col_name_2 src_col_2
834 ,xid.source_id_col_name_3 src_col_3
835 ,xid.source_id_col_name_4 src_col_4
836 ,xem.column_name column_name
837 ,xem.column_title PROMPT
838 ,utc.data_type data_type
839 FROM xla_entity_id_mappings xid
840 ,xla_event_mappings_vl xem
841 ,user_tab_columns utc
842 WHERE xid.application_id = l_event_class_set(k).application_id
843 AND xid.entity_code = l_event_class_set(k).entity_code
844 AND xem.application_id = l_event_class_set(k).application_id
845 AND xem.entity_code = l_event_class_set(k).entity_code
846 AND xem.event_class_code = l_event_class_set(k).event_class_code
847 AND utc.table_name = l_event_class_set(k).reporting_view_name
848 AND utc.column_name = xem.column_name
849 ORDER BY xem.user_sequence)
850 LOOP
851
852 l_index := l_index + 1;
853 --
854 -- creating string to be added to WHERE clause
855 --
856 IF l_index = 1 THEN
857 -----------------------------------------------------------
858 -- Bug 3389175
859 -- Following logic is build to make sure all events are
860 -- reported if debug is enabled evenif there is no data for
861 -- the event in the transaction id view.
862 -- if log enabled then
863 -- outer join to TID view
864 -- endif
865 -----------------------------------------------------------
866 IF g_log_level <> C_LEVEL_LOG_DISABLED THEN
867 l_outerjoin := '(+)';
868 ELSE
869 l_outerjoin := NULL;
870 END IF;
871
872 IF cols_csr.trx_col_1 IS NOT NULL THEN
873 l_join_string := l_join_string ||
874 ' TIV.'|| cols_csr.trx_col_1 ||
875 l_outerjoin ||
876 ' = ENT.'|| cols_csr.src_col_1;
877 END IF;
878 IF cols_csr.trx_col_2 IS NOT NULL THEN
879 l_join_string := l_join_string ||
880 ' AND TIV.'|| cols_csr.trx_col_2 ||
881 l_outerjoin ||
882 ' = ENT.'|| cols_csr.src_col_2;
883 END IF;
884 IF cols_csr.trx_col_3 IS NOT NULL THEN
885 l_join_string := l_join_string ||
886 ' AND TIV.'|| cols_csr.trx_col_3 ||
887 l_outerjoin ||
888 ' = ENT.'|| cols_csr.src_col_3;
889 END IF;
890 IF cols_csr.trx_col_4 IS NOT NULL THEN
891 l_join_string := l_join_string ||
892 ' AND TIV.'|| cols_csr.trx_col_4 ||
893 l_outerjoin ||
894 ' = ENT.'|| cols_csr.src_col_4;
895 END IF;
896
897 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
898 trace
899 (p_msg => 'length of l_join_string = '||length(l_join_string)
900 ,p_level => C_LEVEL_STATEMENT
901 ,p_module => l_log_module);
902 END IF;
903
904 END IF;
905 --
906 -- getting the PROMPTs to be displayed
907 --
908 l_col_array(l_index).f1 := ''''||cols_csr.PROMPT||'''';
909
910 ---
911 -- getting the columns to be displayed
912 ---
913 IF cols_csr.data_type = 'VARCHAR2' THEN
914 l_col_array(l_index).f2 := 'TIV.'|| cols_csr.column_name;
915 ELSE
916 l_col_array(l_index).f2 := 'to_char(TIV.'||
917 cols_csr.column_name||')';
918 END IF;
919 END LOOP;
920 END IF;
921 --------------------------------------------------------------------
922 -- building the string to be added to the SELECT clause
923 --------------------------------------------------------------------
924 l_col_string := l_col_string ||
925 l_col_array(1).f1||'||''|''||'||l_col_array(1).f2;
926
927 FOR i IN 2..l_col_array.count LOOP
928 l_col_string := l_col_string ||'||''|''||'||l_col_array(i).f1
929 ||'||''|''||'||l_col_array(i).f2;
930 END LOOP;
931
932
933 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
934 trace
935 (p_msg => 'length of l_col_string = '||length(l_col_string)
936 ,p_level => C_LEVEL_STATEMENT
937 ,p_module => l_log_module);
938 END IF;
939
940 l_trx_id_str_temp := l_trx_id_str||' WHEN xet.event_class_code = '''
941 ||l_event_class_set(k).event_class_code||
942 ''' THEN ( SELECT '||l_col_string
943 ||' FROM '||l_view_name ||' WHERE '|| l_join_string
944 ||' )' ;
945 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
946 trace
947 (p_msg => 'length of l_trx_id_str_temp = '||length(l_trx_id_str_temp)
948 ,p_level => C_LEVEL_STATEMENT
949 ,p_module => l_log_module);
950 END IF;
951
952 IF length(l_trx_id_str_temp)<=25000 then
953 l_trx_id_str := l_trx_id_str_temp;
954
955 ELSE
956 IF l_id_num = 1 then
957 p_trx_identifiers_1 := l_trx_id_str;
958 l_trx_id_str_temp:=NULL;
959 l_trx_id_str:=NULL;
960 END IF;
961 IF l_id_num = 2 then
962 p_trx_identifiers_2 := l_trx_id_str;
963 l_trx_id_str_temp:=NULL;
964 l_trx_id_str:=NULL;
965 END IF;
966 IF l_id_num = 3 then
967 p_trx_identifiers_3 := l_trx_id_str;
968 l_trx_id_str_temp:=NULL;
969 l_trx_id_str:=NULL;
970 END IF;
971 IF l_id_num = 4 then
972 p_trx_identifiers_4 := l_trx_id_str;
973 l_trx_id_str_temp:=NULL;
974 l_trx_id_str:=NULL;
975 END IF;
976 IF l_id_num = 5 then
977 p_trx_identifiers_5 := l_trx_id_str;
978 l_trx_id_str_temp:=NULL;
979 l_trx_id_str:=NULL;
980 END IF;
981 l_trx_id_str_temp := ' WHEN xet.event_class_code = '''
982 ||l_event_class_set(k).event_class_code||
983 ''' THEN ( SELECT '||l_col_string
984 ||' FROM '||l_view_name ||' WHERE '|| l_join_string
985 ||' )' ;
986 l_trx_id_str:=l_trx_id_str_temp;
987 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
988 trace
989 (p_msg => 'inside length of l_trx_id_str_temp = '||length(l_trx_id_str_temp)
990 ,p_level => C_LEVEL_STATEMENT
991 ,p_module => l_log_module);
992 END IF;
993
994 l_id_num := l_id_num + 1;
995
996 END IF;
997
998
999
1000
1001 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1002 trace
1003 (p_msg => 'length of l_trx_id_str = '||length(l_trx_id_str)
1004 ,p_level => C_LEVEL_STATEMENT
1005 ,p_module => l_log_module);
1006 END IF;
1007
1008
1009
1010
1011 END LOOP;
1012 END IF;
1013
1014 l_trx_id_str := l_trx_id_str ||' END '||' USERIDS';
1015
1016 if l_id_num = 1 then
1017 p_trx_identifiers_1 := l_trx_id_str;
1018 elsif l_id_num = 2 then
1019 p_trx_identifiers_2 := l_trx_id_str;
1020 elsif l_id_num = 3 then
1021 p_trx_identifiers_3 := l_trx_id_str;
1022 elsif l_id_num = 4 then
1023 p_trx_identifiers_4 := l_trx_id_str;
1024 elsif l_id_num = 5 then
1025 p_trx_identifiers_5 := l_trx_id_str;
1026 end if;
1027
1028 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1029 trace('get_transaction_id .End'
1030 ,C_LEVEL_PROCEDURE, l_log_module);
1031 END IF;
1032
1033
1034 EXCEPTION
1035 WHEN OTHERS THEN
1036 xla_exceptions_pkg.raise_message
1037 (p_location => 'xla_period_close_exp_pkg.get_transaction_id ');
1038
1039 END get_transaction_id;
1040
1041
1042 FUNCTION get_ledger_ids(p_ledger_id IN NUMBER) RETURN VARCHAR2 IS
1043 CURSOR cur_primary_ledger( p_ledger_id NUMBER) IS
1044 select distinct glr1.target_ledger_id ledger_id
1045 from gl_ledger_relationships glr1
1046 ,gl_ledger_relationships glr2
1047 where glr1.source_ledger_id = glr2.source_ledger_id
1048 and glr1.application_id = glr2.application_id
1049 and glr2.target_ledger_id = p_ledger_id
1050 and glr2.application_id = 101
1051 and (g_use_ledger_security = 'N'
1052 or glr1.target_ledger_id in
1053 (select led.ledger_id
1054 from gl_ledgers led, gl_access_set_assignments aset
1055 where aset.ledger_id = led.ledger_id
1056 and aset.access_set_id in
1057 (g_access_set_id, g_sec_access_set_id)));
1058
1059 CURSOR cur_ledger ( p_ledger_id NUMBER) IS
1060 SELECT distinct glr2.target_ledger_id ledger_id
1061 FROM gl_ledger_set_assignments gla
1062 ,gl_ledger_relationships glr1
1063 ,gl_ledger_relationships glr2
1064 WHERE gla.ledger_id = glr1.target_ledger_id
1065 AND glr1.source_ledger_id = glr2.source_ledger_id
1066 and glr1.application_id = glr2.application_id
1067 AND gla.ledger_set_id = p_ledger_id
1068 AND gla.ledger_id <> gla.ledger_set_id
1069 AND glr1.application_id = 101
1070 AND (g_use_ledger_security = 'N'
1071 or glr2.target_ledger_id in
1072 (SELECT led.ledger_id
1073 FROM gl_ledgers led, gl_access_set_assignments aset
1074 WHERE aset.ledger_id = led.ledger_id
1075 AND aset.access_set_id in
1076 (g_access_set_id, g_sec_access_set_id)));
1077
1078 l_log_module VARCHAR2(240);
1079 l_ledger_ids VARCHAR2(2000);
1080 l_object_type_code VARCHAR2(1);
1081
1082 BEGIN
1083 IF g_log_enabled THEN
1084 l_log_module := C_DEFAULT_MODULE||'.get_ledger_ids';
1085 END IF;
1086
1087 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1088 trace('get_ledger_ids.Begin',C_LEVEL_PROCEDURE,l_log_module);
1089 END IF;
1090
1091 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1092 trace('p_ledger_id = '|| to_char(p_ledger_id),
1093 C_LEVEL_STATEMENT ,l_log_module);
1094 trace('g_access_set_id = '|| to_char(g_access_set_id),
1095 C_LEVEL_STATEMENT ,l_log_module);
1096 trace('g_sec_access_set_id = '|| to_char(g_sec_access_set_id),
1097 C_LEVEL_STATEMENT ,l_log_module);
1098 trace('g_use_ledger_security = '|| g_use_ledger_security,
1099 C_LEVEL_STATEMENT ,l_log_module);
1100 END IF;
1101
1102 l_object_type_code := xla_report_utility_pkg.get_ledger_object_type
1103 (p_ledger_id);
1104
1105 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1106 trace('l_object_type_code = '|| l_object_type_code,
1107 C_LEVEL_STATEMENT ,l_log_module);
1108 END IF;
1109
1110 IF l_object_type_code = 'S' THEN
1111 FOR l_set IN cur_ledger(p_ledger_id)
1112 LOOP
1113 l_ledger_ids := l_ledger_ids || l_set.ledger_id ||',';
1114 END LOOP;
1115
1116 ELSIF l_object_type_code = 'L' THEN
1117
1118 FOR l_set IN cur_primary_ledger(p_ledger_id)
1119 LOOP
1120 l_ledger_ids := l_ledger_ids || l_set.ledger_id || ',';
1121 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1122 trace('l_ledger_ids = '|| l_ledger_ids,
1123 C_LEVEL_STATEMENT ,l_log_module);
1124 trace('l_set.ledger_id = '|| to_char(l_set.ledger_id),
1125 C_LEVEL_STATEMENT ,l_log_module);
1126 END IF;
1127 END LOOP;
1128 END IF;
1129
1130 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1131 trace('l_ledger_ids = '|| l_ledger_ids,
1132 C_LEVEL_STATEMENT ,l_log_module);
1133 END IF;
1134
1135 l_ledger_ids := substr(l_ledger_ids,0,length(l_ledger_ids)-1);
1136
1137 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1138 trace('l_ledger_ids = '|| l_ledger_ids,
1139 C_LEVEL_STATEMENT ,l_log_module);
1140 END IF;
1141
1142 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1143 trace('get_ledger_ids.End',C_LEVEL_PROCEDURE,l_log_module);
1144 END IF;
1145 RETURN l_ledger_ids;
1146 EXCEPTION
1147 WHEN OTHERS THEN
1148 xla_exceptions_pkg.raise_message
1149 (p_location => 'xla_period_close_exp_pkg.get_ledger_ids');
1150 END get_ledger_ids;
1151
1152 PROCEDURE build_query_sql
1153 (p_application_id IN NUMBER
1154 ,p_ledger_id IN NUMBER
1155 ,p_period_from IN VARCHAR2
1156 ,p_period_to IN VARCHAR2
1157 ,p_event_class IN VARCHAR2
1158 ,p_je_category IN VARCHAR2
1159 ,p_object_type_code IN VARCHAR2
1160 ,p_je_source_name IN VARCHAR2
1161 ,p_mode IN VARCHAR2) IS
1162
1163 l_event_filter VARCHAR2(4000) := ' ';
1164
1165 l_header_filter VARCHAR2(4000) := ' ';
1166 l_application_filter_evt VARCHAR2(150);
1167 l_application_filter_aeh VARCHAR2(200);
1168 l_je_source_filter VARCHAR2(200) := ' ';
1169 l_je_category_filter VARCHAR2(200);
1170 l_date_filter_evt VARCHAR2(200);
1171 l_date_filter_aeh VARCHAR2(200);
1172 l_ledger_ids VARCHAR2(2000);
1173 l_event_class_filter VARCHAR2(200);
1174
1175 l_start_date DATE;
1176 l_end_date DATE;
1177
1178 l_log_module VARCHAR2(240);
1179 l_period_ledger_id GL_LEDGERS.ledger_id%TYPE;
1180 l_index NUMBER;
1181
1182
1183
1184
1185 BEGIN
1186
1187 IF g_log_enabled THEN
1188 l_log_module := C_DEFAULT_MODULE||'.build_query_sql';
1189 END IF;
1190
1191 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1192 trace('build_query_sql.Begin',C_LEVEL_PROCEDURE,l_log_module);
1193 END IF;
1194
1195 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1196 trace('p_ledger_id =' || p_ledger_id,C_LEVEL_STATEMENT,
1197 l_log_module);
1198 trace('period_from =' || p_period_from,C_LEVEL_STATEMENT,l_log_module);
1199 trace('period_to = '|| p_period_to,C_LEVEL_STATEMENT,l_Log_module);
1200 trace('p_event_class = '|| p_event_class,
1201 C_LEVEL_STATEMENT ,l_log_module);
1202 trace('p_je_category = '|| p_je_category,
1203 C_LEVEL_STATEMENT, l_log_module);
1204 trace('p_object_type_code = '|| p_object_type_code,
1205 C_LEVEL_STATEMENT, l_log_module);
1206 trace('p_je_source_name = '|| p_je_source_name,
1207 C_LEVEL_STATEMENT, l_log_module);
1208 END IF;
1209
1210
1211 build_xml_sql;
1212
1213 ----------------------------------------------------------------------------
1214 -- build filter condition based on parameters
1215 ----------------------------------------------------------------------------
1216
1217 l_ledger_ids := get_ledger_ids(p_ledger_id);
1218
1219
1220 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1221 trace('l_ledger_ids = '|| l_ledger_ids,
1222 C_LEVEL_STATEMENT, l_log_module);
1223 END IF;
1224
1225
1226 IF p_object_type_code = 'S' THEN
1227 SELECT ledger_id
1228 INTO l_period_ledger_id
1229 FROM gl_ledger_set_assignments
1230 WHERE ledger_set_id = p_ledger_id
1231 AND ledger_id <> p_ledger_id
1232 AND ROWNUM = 1;
1233 ELSE
1234 l_period_ledger_id := p_ledger_id ;
1235 END IF;
1236
1237
1238 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1239 trace('l_period_ledger_id = '|| l_period_ledger_id,
1240 C_LEVEL_STATEMENT, l_log_module);
1241 END IF;
1242
1243 get_period_start_end_dates(l_period_ledger_id
1244 ,p_period_from
1245 ,p_period_to
1246 ,l_start_date
1247 ,l_end_date);
1248
1249 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1250 trace('l_start_date = '|| to_char(l_start_date,'DD-MON-YYYY'),
1251 C_LEVEL_STATEMENT, l_log_module);
1252 trace('l_end_date = '|| to_char(l_end_date,'DD-MON-YYYY'),
1253 C_LEVEL_STATEMENT, l_log_module);
1254 END IF;
1255
1256 l_ledger_ids := nvl(l_ledger_ids,'NULL');
1257
1258 --------------------------------------------------------------------
1259 -- the following is removed (commented) as part of bug fix 6805286
1260 --------------------------------------------------------------------
1261 -- IF p_mode <> 'W' THEN
1262 --
1263 -- stamp_events_wo_aad(p_application_id
1264 -- ,l_ledger_ids
1265 -- ,l_start_date
1266 -- ,l_end_date);
1267 -- END IF;
1268
1269 l_ledger_ids := '(' || l_ledger_ids ||')';
1270
1271 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1272 trace('l_ledger_ids = '|| l_ledger_ids,
1273 C_LEVEL_STATEMENT, l_log_module);
1274 END IF;
1275
1276 IF p_application_id <> 101 THEN
1277 l_application_filter_evt := l_application_filter_evt ||
1278 ' AND xle.application_id = '|| to_char(p_application_id) ;
1279 l_application_filter_aeh := l_application_filter_aeh ||
1280 ' AND aeh.application_id = '|| to_char(p_application_id) ;
1281 END IF;
1282
1283 IF p_je_source_name is NOT NULL THEN
1284 l_je_source_filter := ' AND xls.je_source_name =
1285 ' ||''''|| p_je_source_name||'''' ;
1286 END IF;
1287
1288 IF p_je_category is NOT NULL THEN
1289 l_je_category_filter := ' AND gjct.je_category_name = ' ||''''||
1290 p_je_category||'''';
1291 END IF;
1292
1293 IF p_event_class is NOT NULL THEN
1294 l_event_class_filter := ' AND xcl.event_class_code = ' ||''''||
1295 p_event_class||'''' ;
1296 END IF;
1297
1298 l_date_filter_evt :=
1299 ' AND xle.event_date BETWEEN '''||l_start_date|| ''' '||
1300 'AND '''||l_end_date||''' ';
1301
1302 l_date_filter_aeh :=
1303 ' AND aeh.accounting_date BETWEEN '''||l_start_date|| ''' '||
1304 'AND '''||l_end_date||''' ';
1305
1306 l_event_filter := l_application_filter_evt || l_date_filter_evt ||
1307 l_je_category_filter || l_event_class_filter ;
1308 l_header_filter := l_application_filter_aeh || l_date_filter_aeh ||
1309 l_je_category_filter || l_event_class_filter ;
1310
1311 -- l_application_filter_evt needs to be combined with
1312 -- l_application_filter_aeh to be replaced in l_percl_query
1313 -- but the same cannot be used from l_user_trx_query
1314 -- so joining with l_je_source_filter which will be used for l_percl_query
1315
1316
1317 get_transaction_id(l_ledger_ids,l_event_filter,l_header_filter);
1318
1319
1320 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1321 trace('p_trx_identifiers_1='||substr(p_trx_identifiers_1,1,3000),C_LEVEL_PROCEDURE, l_log_module);
1322 END IF;
1323 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1324 trace('p_trx_identifiers_2='||substr(p_trx_identifiers_2,1,3000),C_LEVEL_PROCEDURE, l_log_module);
1325 END IF;
1326 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1327 trace('p_trx_identifiers_3='||substr(p_trx_identifiers_3,1,3000),C_LEVEL_PROCEDURE, l_log_module);
1328 END IF;
1329 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1330 trace('p_trx_identifiers_4='||substr(p_trx_identifiers_4,1,3000),C_LEVEL_PROCEDURE, l_log_module);
1331 END IF;
1332 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1333 trace('p_trx_identifiers_5='||substr(p_trx_identifiers_5,1,3000),C_LEVEL_PROCEDURE, l_log_module);
1334 END IF;
1335
1336
1337
1338
1339 xla_period_close_exp_pkg.p_ledger_ids := l_ledger_ids;
1340 xla_period_close_exp_pkg.p_event_filter := l_event_filter;
1341 xla_period_close_exp_pkg.p_header_filter := l_header_filter;
1342 xla_period_close_exp_pkg.p_je_source_filter := l_je_source_filter;
1343 xla_period_close_exp_pkg.p_object_type_code := p_object_type_code;
1344 xla_period_close_exp_pkg.p_je_source_name := p_je_source_name;
1345
1346 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1347 trace('build_query_sql.End'
1348 ,C_LEVEL_PROCEDURE, l_log_module);
1349 END IF;
1350
1351 EXCEPTION
1352 WHEN NO_DATA_FOUND THEN
1353 NULL;
1354 WHEN OTHERS THEN
1355 xla_exceptions_pkg.raise_message
1356 (p_location => 'xla_period_close_exp_pkg.build_query_sql');
1357 END build_query_sql ;
1358
1359 FUNCTION check_period_close(p_application_id IN NUMBER
1360 ,p_period_name IN VARCHAR2
1361 ,p_ledger_id IN NUMBER) RETURN NUMBER IS
1362 l_log_module VARCHAR2(240);
1363 l_period_start_date DATE;
1364 l_period_end_date DATE;
1365 l_unprocessed NUMBER DEFAULT 0;
1366 l_ledger_ids VARCHAR2(2000);
1367 BEGIN
1368 IF g_log_enabled THEN
1369 l_log_module := C_DEFAULT_MODULE||'.check_period_close';
1370 END IF;
1371
1372 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1373 trace('check_period_close.Begin',C_LEVEL_PROCEDURE,l_log_module);
1374 END IF;
1375
1376 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1377 trace('p_application_id = '|| to_char(p_application_id),
1378 C_LEVEL_STATEMENT ,l_log_module);
1379 trace('p_period_name = '|| p_period_name,
1380 C_LEVEL_STATEMENT ,l_log_module);
1381 trace('p_ledger_id = '|| to_char(p_ledger_id),
1382 C_LEVEL_STATEMENT ,l_log_module);
1383 END IF;
1384 get_period_start_end_dates(p_ledger_id
1385 ,p_period_name
1386 ,p_period_name
1387 ,l_period_start_date
1388 ,l_period_end_date);
1389
1390 l_ledger_ids := nvl(get_ledger_ids(p_ledger_id),'NULL');
1391
1392 --------------------------------------------------------------------
1393 -- the following is removed (commented) as part of bug fix 6805286
1394 --------------------------------------------------------------------
1395 -- stamp_events_wo_aad(p_application_id
1396 -- ,l_ledger_ids
1397 -- ,l_period_start_date
1398 -- ,l_period_end_date);
1399
1400
1401 IF p_application_id = 101 THEN
1402
1403
1404 BEGIN
1405
1406 SELECT 1
1407 INTO l_unprocessed
1408 FROM dual
1409 WHERE EXISTS(select 1
1410 FROM xla_events xle
1411 ,xla_transaction_entities xte
1412 ,gl_ledger_relationships glr1
1413 ,gl_ledger_relationships glr2
1414 ,xla_ledger_options xlo
1415 WHERE xle.entity_id = xte.entity_id
1416 AND xle.application_id = xte.application_id
1417 AND xle.event_date BETWEEN l_period_start_date and l_period_end_date
1418 AND glr2.target_ledger_id = p_ledger_id
1419 AND glr2.source_ledger_id = glr1.source_ledger_id
1420 AND glr2.application_id = glr1.application_id
1421 AND glr1.target_ledger_id = xlo.ledger_id
1422 AND xle.application_id = xlo.application_id
1423 AND xlo.capture_event_flag = 'Y'
1424 AND (glr1.target_ledger_id = xte.ledger_id OR
1425 glr1.primary_ledger_id = xte.ledger_id )
1426 AND (glr1.relationship_type_code = 'SUBLEDGER' OR
1427 (glr1.target_ledger_category_code = 'PRIMARY'
1428 AND glr1.relationship_type_code = 'NONE'))
1429 AND glr2.application_id = 101
1430 AND xle.event_status_code IN ('I','U')
1431 AND xle.process_status_code IN ('I','U','R','D','E'));
1432
1433
1434 EXCEPTION WHEN no_data_found THEN
1435
1436 --IF l_unprocessed = 0 THEN
1437 SELECT count(*)
1438 INTO l_unprocessed
1439 FROM xla_ae_headers aeh
1440 ,xla_transaction_entities xte
1441 ,gl_ledger_relationships glr1
1442 ,gl_ledger_relationships glr2
1443 WHERE aeh.ledger_id = glr2.target_ledger_id
1444 AND glr2.source_ledger_id = glr1.source_ledger_id
1445 AND glr2.application_id = glr1.application_id
1446 AND glr1.target_ledger_id = p_ledger_id
1447 AND glr1.application_id = 101
1448 AND xte.entity_id = aeh.entity_id
1449 AND xte.application_id = aeh.application_id
1450 AND aeh.gl_transfer_status_code IN ('N','E')
1451 AND aeh.accounting_date BETWEEN
1452 l_period_start_date AND l_period_end_date
1453 AND rownum = 1;
1454 --END IF;
1455
1456
1457 END;
1458
1459
1460 ELSE
1461
1462
1463 BEGIN
1464
1465 SELECT 1
1466 INTO l_unprocessed
1467 FROM dual
1468 WHERE EXISTS(select 1
1469 FROM xla_events xle
1470 ,xla_transaction_entities xte
1471 ,gl_ledger_relationships glr1
1472 ,gl_ledger_relationships glr2
1473 ,xla_ledger_options xlo
1474 WHERE xle.entity_id = xte.entity_id
1475 AND xle.application_id = xte.application_id
1476 AND xle.event_date BETWEEN l_period_start_date and l_period_end_date
1477 AND xle.application_id = p_application_id
1478 AND xle.event_status_code IN ('I','U')
1479 AND xle.process_status_code IN ('I','U','R','D','E')
1480 AND glr2.target_ledger_id = p_ledger_id
1481 AND glr2.source_ledger_id = glr1.source_ledger_id
1482 AND glr2.application_id = glr1.application_id
1483 AND glr1.target_ledger_id = xlo.ledger_id
1484 AND xle.application_id = xlo.application_id
1485 AND xlo.capture_event_flag = 'Y'
1486 AND (glr1.target_ledger_id = xte.ledger_id OR
1487 glr1.primary_ledger_id = xte.ledger_id )
1488 AND (glr1.relationship_type_code = 'SUBLEDGER' OR
1489 (glr1.target_ledger_category_code = 'PRIMARY'
1490 AND glr1.relationship_type_code = 'NONE'))
1491 AND glr2.application_id = 101
1492 AND xte.application_id = p_application_id
1493 );
1494
1495 --6784591 added process_status_code check.
1496
1497
1498 EXCEPTION WHEN no_data_found THEN
1499
1500 --IF l_unprocessed = 0 THEN
1501 SELECT count(*)
1502 INTO l_unprocessed
1503 FROM xla_ae_headers aeh
1504 ,xla_transaction_entities xte
1505 ,gl_ledger_relationships glr1
1506 ,gl_ledger_relationships glr2
1507 WHERE aeh.ledger_id = glr2.target_ledger_id
1508 AND glr2.source_ledger_id = glr1.source_ledger_id
1509 AND glr2.application_id = glr1.application_id
1510 AND glr1.target_ledger_id = p_ledger_id
1511 AND glr1.application_id = 101
1512 AND xte.entity_id = aeh.entity_id
1513 AND xte.application_id = aeh.application_id
1514 AND aeh.gl_transfer_status_code IN ('N','E')
1515 AND aeh.accounting_date BETWEEN
1516 l_period_start_date AND l_period_end_date
1517 AND xte.application_id = p_application_id
1518 AND rownum = 1;
1519 --END IF;
1520
1521
1522 END;
1523
1524 END IF;
1525
1526 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1527 trace('l_unprocessed = '|| to_char(l_unprocessed),
1528 C_LEVEL_STATEMENT ,l_log_module);
1529 END IF;
1530
1531 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1532 trace('check_period_close.End',C_LEVEL_PROCEDURE,l_log_module);
1533 END IF;
1534
1535 RETURN l_unprocessed;
1536 EXCEPTION
1537 WHEN OTHERS THEN
1538 xla_exceptions_pkg.raise_message
1539 (p_location => 'xla_period_close_exp_pkg.check_period_close');
1540
1541 END check_period_close;
1542
1543 FUNCTION beforeReport RETURN BOOLEAN IS
1544 l_errbuf VARCHAR2(2000);
1545 l_log_module VARCHAR2(240);
1546 BEGIN
1547 IF g_log_enabled THEN
1548 l_log_module := C_DEFAULT_MODULE||'.beforeReport';
1549 END IF;
1550
1551 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1552 trace('beforeReport.Begin',C_LEVEL_PROCEDURE,l_log_module);
1553 END IF;
1554
1555 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1556
1557 trace('p_application_id = '|| to_char(p_application_id),
1558 C_LEVEL_STATEMENT, l_log_module);
1559 trace('p_je_source = '|| p_je_source,
1560 C_LEVEL_STATEMENT, l_log_module);
1561 trace('p_dummy_param_1 = '|| to_char(p_dummy_param_1),
1562 C_LEVEL_STATEMENT, l_log_module);
1563 trace('p_dummy_param_2 = '|| to_char(p_dummy_param_2),
1564 C_LEVEL_STATEMENT, l_log_module);
1565 trace('p_ledger_id = '|| to_char(p_ledger_id),
1566 C_LEVEL_STATEMENT, l_log_module);
1567 trace('p_ledger = '|| p_ledger,
1568 C_LEVEL_STATEMENT, l_log_module);
1569 trace('p_period_from = '|| p_period_from,
1570 C_LEVEL_STATEMENT,l_log_module);
1571 trace('p_period_to = '|| p_period_to,
1572 C_LEVEL_STATEMENT, l_log_module);
1573 trace('p_event_class = '|| p_event_class,
1574 C_LEVEL_STATEMENT, l_log_module);
1575 trace('p_event_class_code = '|| p_event_class_code,
1576 C_LEVEL_STATEMENT, l_log_module);
1577 trace('p_je_category = '|| p_je_category,
1578 C_LEVEL_STATEMENT, l_log_module);
1579 trace('p_je_category_name = '|| p_je_category_name,
1580 C_LEVEL_STATEMENT, l_log_module);
1581 trace('p_mode = '|| p_mode,
1582 C_LEVEL_STATEMENT, l_log_module);
1583 END IF;
1584
1585 run_report(p_errbuf => l_errbuf
1586 ,p_retcode => xla_period_close_exp_pkg.C_RETURN_CODE
1587 ,p_application_id => xla_period_close_exp_pkg.p_application_id
1588 ,p_ledger_id => xla_period_close_exp_pkg.p_ledger_id
1589 ,p_period_from => xla_period_close_exp_pkg.p_period_from
1590 ,p_period_to => xla_period_close_exp_pkg.p_period_to
1591 ,p_event_class => xla_period_close_exp_pkg.p_event_class_code
1592 ,p_je_category => xla_period_close_exp_pkg.p_je_category_name
1593 ,p_mode => xla_period_close_exp_pkg.p_mode);
1594
1595 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1596 trace('beforeReport.End'
1597 ,C_LEVEL_PROCEDURE, l_log_module);
1598 END IF;
1599
1600 RETURN TRUE;
1601
1602 EXCEPTION
1603 WHEN OTHERS THEN
1604 xla_exceptions_pkg.raise_message
1605 (p_location => 'xla_period_close_exp_pkg.beforeReport ');
1606
1607 END beforeReport;
1608
1609 BEGIN
1610 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1611 g_log_enabled := fnd_log.test
1612 (log_level => g_log_level
1613 ,MODULE => C_DEFAULT_MODULE);
1614
1615 IF NOT g_log_enabled THEN
1616 g_log_level := C_LEVEL_LOG_DISABLED;
1617 END IF;
1618
1619 END xla_period_close_exp_pkg;