DBA Data[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;