[Home] [Help]
PACKAGE BODY: APPS.XLA_ACCT_ANALYSIS_RPT_PKG
Source
1 PACKAGE BODY XLA_ACCT_ANALYSIS_RPT_PKG AS
2 -- $Header: xlarpaan.pkb 120.36 2011/08/16 13:07:13 nksurana ship $
3 /*===========================================================================+
4 | Copyright (c) 2003 Oracle Corporation BelmFont, California, USA |
5 | ALL rights reserved. |
6 +============================================================================+
7 | FILENAME |
8 | xlarpaan.pkb |
9 | |
10 | PACKAGE NAME |
11 | xla_acct_analysis_rpt_pkg |
12 | |
13 | DESCRIPTION |
14 | PACKAGE BODY. This provides XML extract for Account Analysis Report |
15 | |
16 | HISTORY |
17 | 07/20/2005 V. Kumar Created |
18 | 12/19/2005 V. Swapna Modifed the package to use data template |
19 | 12/27/2005 V. Swapna Modfied code to use the right GT table. |
20 | Added code to display TP information. |
21 | 04/23/2006 A. Wan 5072266 - replace po_vendors with |
22 | ap_suppliers |
23 | 08/23/2006 V. Swapna 5474255 - Modify filter condition for |
24 | zero amount lines. |
25 | 16-Sep-2008 rajose bug#7386068 To display accounts having |
26 | beginning balance and no activity |
27 | 29-May-2009 rajose bug#8554433 Insert into _gt query taking |
28 | long time to execute. |
29 | 05-Jan-2010 nksurana Added new parameter p_tax_query to handle |
30 | the tax query in the package so that it is |
31 | executed only when tax flag is Y |
32 | 09-Aug-2010 nksurana Removed the clause for zero net period |
33 | activity from the condition on include |
34 | zero amounts. |
35 | 23-Dec-2010 nksurana Added new variables to move the logic from |
36 | xml to pkb to make the xml reuasable and |
37 | improve performance. |
38 | 16-Aug-2011 nksurana Added additional filter in the insert into |
39 | xla_report_balances_gt when the flag |
40 | P_INCLUDE_ACCT_WITH_NO_ACT is NULL or N. |
41 +===========================================================================*/
42
43 --=============================================================================
44 -- **************** declarations ********************
45 --=============================================================================
46
47 TYPE t_array_char IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
48
49
50 -------------------------------------------------------------------------------
51 -- constant for getting leagal entity information
52 -------------------------------------------------------------------------------
53 C_NULL_LEGAL_ENT_COL CONSTANT VARCHAR2(4000) :=
54 ' ,NULL LEGAL_ENTITY_ID
55 ,NULL LEGAL_ENTITY_NAME
56 ,NULL LE_ADDRESS_LINE_1
57 ,NULL LE_ADDRESS_LINE_2
58 ,NULL LE_ADDRESS_LINE_3
59 ,NULL LE_CITY
60 ,NULL LE_REGION_1
61 ,NULL LE_REGION_2
62 ,NULL LE_REGION_3
63 ,NULL LE_POSTAL_CODE
64 ,NULL LE_COUNTRY
65 ,NULL LE_REGISTRATION_NUMBER
66 ,NULL LE_REGISTRATION_EFFECTIVE_FROM
67 ,NULL LE_BR_DAILY_INSCRIPTION_NUMBER
68 ,NULL LE_BR_DAILY_INSCRIPTION_DATE
69 ,NULL LE_BR_DAILY_ENTITY
70 ,NULL LE_BR_DAILY_LOCATION
71 ,NULL LE_BR_DIRECTOR_NUMBER
72 ,NULL LE_BR_ACCOUNTANT_NUMBER
73 ,NULL LE_BR_ACCOUNTANT_NAME ';
74
75 C_LEGAL_ENT_COL CONSTANT VARCHAR2(4000) :=
76 ' ,fiv.legal_entity_id LEGAL_ENTITY_ID
77 ,fiv.NAME LEGAL_ENTITY_NAME
78 ,fiv.ADDRESS_LINE_1 LE_ADDRESS_LINE_1
79 ,fiv.ADDRESS_LINE_2 LE_ADDRESS_LINE_2
80 ,fiv.ADDRESS_LINE_3 LE_ADDRESS_LINE_3
81 ,fiv.TOWN_OR_CITY LE_CITY
82 ,fiv.REGION_1 LE_REGION_1
83 ,fiv.REGION_2 LE_REGION_2
84 ,fiv.REGION_3 LE_REGION_3
85 ,fiv.postal_code LE_POSTAL_CODE
86 ,fiv.country LE_COUNTRY
87 ,fiv.registration_number LE_REGISTRATION_NUMBER
88 ,fiv.effective_from LE_REGISTRATION_EFFECTIVE_FROM
89 ,xrv.registration_number LE_BR_DAILY_INSCRIPTION_NUMBER
90 ,to_char(xrv.effective_from
91 ,''YYYY-MM-DD'') LE_BR_DAILY_INSCRIPTION_DATE
92 ,xrv.legalauth_name LE_BR_DAILY_ENTITY
93 ,xlv.city LE_BR_DAILY_LOCATION
94 ,lc1.contact_number LE_BR_DIRECTOR_NUMBER
95 ,lc2.contact_number LE_BR_ACCOUNTANT_NUMBER
96 ,lc2.contact_name LE_BR_ACCOUNTANT_NAME ';
97
98 C_LEGAL_ENT_FROM CONSTANT VARCHAR2(1000) :=
99 ' ,xle_firstparty_information_v fiv
100 ,xle_registrations_v xrv
101 ,xle_legalauth_v xlv
102 ,xle_legal_contacts_v lc1
103 ,xle_legal_contacts_v lc2
104 ,gl_ledger_le_bsv_specific_v gle';
105
106 C_LEGAL_ENT_JOIN CONSTANT VARCHAR2(2000) :=
107 ' AND gle.ledger_id(+) = TABLE1.ledger_id
108 AND gle.segment_value(+) = TABLE1.$leg_seg_val$
109 AND fiv.legal_entity_id(+) = gle.legal_entity_id
110 AND xrv.legal_entity_id(+) = fiv.legal_entity_id
111 AND xrv.legislative_category(+) = ''FEDERAL_TAX''
112 AND xlv.legalauth_id(+) = xrv.legalauth_id
113 AND lc1.entity_id(+) = fiv.legal_entity_id
114 AND lc1.ROLE(+) = ''DIRECTOR''
115 AND lc1.entity_type(+) = ''LEGAL_ENTITY''
116 AND lc2.entity_id(+) = fiv.legal_entity_id
117 AND lc2.ROLE(+) = ''ACCOUNTANT''
118 AND lc2.entity_type(+) = ''LEGAL_ENTITY'' ';
119
120 C_ESTBLISHMENT_COL CONSTANT VARCHAR2(4000) :=
121 ' ,xev.establishment_id LEGAL_ENTITY_ID
122 ,xev.establishment_name LEGAL_ENTITY_NAME
123 ,xev.address_line_1 LE_ADDRESS_LINE_1
124 ,xev.address_line_2 LE_ADDRESS_LINE_2
125 ,xev.address_line_3 LE_ADDRESS_LINE_3
126 ,xev.town_or_city LE_CITY
127 ,xev.region_1 LE_REGION_1
128 ,xev.region_2 LE_REGION_2
129 ,xev.region_3 LE_REGION_3
130 ,xev.postal_code LE_POSTAL_CODE
131 ,xev.country LE_COUNTRY
132 ,xev.registration_number LE_REGISTRATION_NUMBER
133 ,xev.effective_from LE_REGISTRATION_EFFECTIVE_FROM
134 ,xrv.registration_number LE_BR_DAILY_INSCRIPTION_NUMBER
135 ,to_char(xrv.effective_from
136 ,''YYYY-MM-DD'') LE_BR_DAILY_INSCRIPTION_DATE
137 ,xrv.legalauth_name LE_BR_DAILY_ENTITY
138 ,xlv.city LE_BR_DAILY_LOCATION
139 ,lc1.contact_number LE_BR_DIRECTOR_NUMBER
140 ,lc2.contact_number LE_BR_ACCOUNTANT_NUMBER
141 ,lc2.contact_name LE_BR_ACCOUNTANT_NAME ';
142
143 C_ESTABLISHMENT_FROM CONSTANT VARCHAR2(2000) :=
144 ' ,gl_ledger_le_bsv_specific_v glv
145 ,xle_bsv_associations xba
146 ,xle_establishment_v xev
147 ,xle_registrations_v xrv
148 ,xle_legalauth_v xlv
149 ,xle_legal_contacts_v lc1
150 ,xle_legal_contacts_v lc2';
151
152 C_ESTABLISHMENT_JOIN CONSTANT VARCHAR2(2000) :=
153 ' AND glv.ledger_id(+) = TABLE1.ledger_id
154 AND glv.segment_value(+) = TABLE1.$leg_seg_val$
155 AND xba.legal_parent_id(+) = glv.legal_entity_id
156 AND xba.entity_name(+) = glv.segment_value
157 AND xba.context(+) = ''EST_BSV_MAPPING''
158 AND xev.establishment_id(+) = xba.legal_construct_id
159 AND xrv.establishment_id(+) = xev.establishment_id
160 AND xrv.legislative_category(+) = ''FEDERAL_TAX''
161 AND xlv.legalauth_id(+) = xrv.legalauth_id
162 AND lc1.entity_id(+) = xev.establishment_id
163 AND lc1.entity_type(+) = ''ESTABLISHMENT''
164 AND lc1.ROLE(+) = ''DIRECTOR''
165 AND lc2.entity_id(+) = xev.establishment_id
166 AND lc2.ROLE(+) = ''ACCOUNTANT''
167 AND lc2.entity_type(+) = ''ESTABLISHMENT'' ';
168 --------------------------------------------------------------------------------
169 -- constant for COMMERCIAL_NUMBER details
170 --------------------------------------------------------------------------------
171 C_COMMERCIAL_QUERY VARCHAR2(8000) :=
172 'SELECT nvl(xler.registration_number,0) LEGAL_COMMERCIAL_NUMBER
173 FROM XLE_REGISTRATIONS_V xler
174 WHERE legislative_category = ''COMMERCIAL_LAW''
175 AND legal_entity_id = :P_LEGAL_ENTITY_ID';
176
177 C_COMMERCIAL_NULL_QUERY VARCHAR2(8000) :=
178 'select NULL LEGAL_COMMERCIAL_NUMBER from dual where 1>2';
179
180 --------------------------------------------------------------------------------
181 -- constant for VAT_REGISTRATION details
182 --------------------------------------------------------------------------------
183 C_VAT_REGISTRATION_QUERY VARCHAR2(8000) :=
184 'SELECT zptp.REP_REGISTRATION_NUMBER LEGAL_VAT_REGISTRATION_NUMBER
185 FROM ZX_PARTY_TAX_PROFILE zptp ,XLE_ETB_PROFILES xetbp
186 WHERE zptp.PARTY_TYPE_CODE = ''LEGAL_ESTABLISHMENT''
187 AND xetbp.party_id=zptp.party_id
188 AND xetbp.MAIN_ESTABLISHMENT_FLAG = ''Y''
189 AND xetbp.LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID' ;
190
191 C_VAT_REGISTRATION_NULL_QUERY VARCHAR2(8000) :=
192 'select NULL LEGAL_VAT_REGISTRATION_NUMBER from dual where 1>2';
193
194 --Added for bug 9011171,8762703
195 --------------------------------------------------------------------------------
196 -- constants for TAX details query
197 --------------------------------------------------------------------------------
198 C_TAX_QUERY VARCHAR2(8000) :=
199 'SELECT /*+ index(xdl, XLA_DISTRIBUTION_LINKS_N3) */
200 zxr.tax_regime_name TAX_REGIME
201 ,zxl.tax TAX
202 ,ztt.tax_full_name TAX_NAME
203 ,zst.tax_status_name TAX_STATUS_NAME
204 ,zrt.tax_rate_name TAX_RATE_NAME
205 ,zxl.tax_rate TAX_RATE
206 ,flk1.meaning TAX_RATE_TYPE_NAME
207 ,to_char(zxl.tax_determine_date
208 ,''YYYY-MM-DD'') TAX_DETERMINE_DATE
209 ,to_char(zxl.tax_point_date
210 ,''YYYY-MM-DD'') TAX_POINT_DATE
211 ,zxl.tax_type_code TAX_TYPE_CODE
212 ,flk2.meaning TAX_TYPE_NAME
213 ,zxl.tax_code TAX_CODE
214 ,zxl.tax_registration_number TAX_REGISTRATION_NUMBER
215 ,zxl.trx_currency_code TRX_CURRENCY_CODE
216 ,zxl.tax_currency_code TAX_CURRENCY_CODE
217 ,zxl.tax_amt TAX_AMOUNT
218 ,zxl.tax_amt_tax_curr TAX_AMOUNT_TAX_CURRENCY
219 ,zxl.tax_amt_funcl_curr TAX_AMOUNT_FUNCTIONAL_CURR
220 ,zxl.taxable_amt TAXABLE_AMOUNT
221 ,zxl.taxable_amt_tax_curr TAXABLE_AMOUNT_TAX_CURRENCY
222 ,zxl.taxable_amt_funcl_curr TAXABLE_AMT_FUNC_CURRENCY
223 ,zxl.unrounded_taxable_amt UNROUNDED_TAXABLE_AMOUNT
224 ,zxl.unrounded_tax_amt UNROUNDED_TAX_AMOUNT
225 ,zxl.rec_tax_amt RECOVERABLE_TAX_AMOUNT
226 ,zxl.rec_tax_amt_tax_curr RECOVERABLE_TAX_AMT_TAX_CURR
227 ,zxl.rec_tax_amt_funcl_curr RECOVERABLE_TAX_AMT_FUNC_CURR
228 ,zxl.nrec_tax_amt NON_RECOVERABLE_TAX_AMOUNT
229 ,zxl.nrec_tax_amt_tax_curr NON_REC_TAX_AMT_TAX_CURR
230 ,zxl.nrec_tax_amt_funcl_curr NON_REC_TAX_AMT_FUNC_CURR
231 FROM xla_distribution_links xdl
232 ,zx_lines zxl
233 ,zx_regimes_tl zxr
234 ,zx_taxes_tl ztt
235 ,zx_status_tl zst
236 ,zx_rates_tl zrt
237 ,fnd_lookups flk1
238 ,fnd_lookups flk2
239 WHERE xdl.tax_line_ref_id = zxl.tax_line_id
240 AND zxr.tax_regime_id(+) = zxl.tax_regime_id
241 AND zxr.language(+) = USERENV(''LANG'')
242 AND ztt.tax_id(+) = zxl.tax_id
243 AND ztt.language(+) = USERENV(''LANG'')
244 AND zst.tax_status_id(+) = zxl.tax_status_id
245 AND zst.language(+) = USERENV(''LANG'')
246 AND zrt.tax_rate_id(+) = zxl.tax_rate_id
247 AND zrt.language(+) = USERENV(''LANG'')
248 AND flk1.lookup_type = ''ZX_RATE_TYPE''
249 AND flk1.lookup_code = zxl.tax_rate_type
250 AND flk2.lookup_type(+) = ''ZX_TAX_TYPE_CATEGORY''
251 AND flk2.lookup_code(+) = zxl.tax_type_code
252 AND xdl.application_id = :APPLICATION_ID
253 AND xdl.ae_header_id = :HEADER_ID
254 AND xdl.ae_line_num = :ORIG_LINE_NUMBER ';
255
256 C_TAX_NULL_QUERY VARCHAR2(8000) :=
257 'SELECT NULL FROM DUAL WHERE 1=2 ';
258
259 C_QUALIFIED_SEGMENT CONSTANT VARCHAR2(1000) :=
260 ' ,$alias_balancing_segment$ BALANCING_SEGMENT
261 ,$alias_account_segment$ NATURAL_ACCOUNT_SEGMENT
262 ,$alias_costcenter_segment$ COST_CENTER_SEGMENT
263 ,$alias_management_segment$ MANAGEMENT_SEGMENT
264 ,$alias_intercompany_segment$ INTERCOMPANY_SEGMENT
265 $seg_desc_column$ ';
266
267 C_HINT CONSTANT VARCHAR2(240) :=' /*+ leading(gcck, gl1, glb) */ ';
268 -- modified for bug#8554433
269 --' /*+ leading(gcck $fnd_flex_hint$, gl1, glb) use_nl(glb) */ ';
270 /*bug#8554433 causing the optimizer to hit gl_balances with _n1 index and
271 using only the code_combination_id as the filter. With this hint _N1 index in
272 gl_balances is hit with code_combination_id and period_name filter which is
273 highly selective.
274 */
275
276
277 --=============================================================================
278 -- ************** forward declaration *******************
279 --=============================================================================
280 --------------------------------------------------------------------------------
281 -- procedure to create the main SQL
282 --------------------------------------------------------------------------------
283 --=============================================================================
284 -- *********** Local Trace Routine **********
285 --=============================================================================
286 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
287 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
288 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
289 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
290 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
291 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
292
293 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
294 C_DEFAULT_MODULE CONSTANT VARCHAR2(240):= 'xla.plsql.xla_acct_analysis_rpt_pkg';
295
296 g_log_level NUMBER;
297 g_log_enabled BOOLEAN;
298
299 PROCEDURE trace
300 (p_msg IN VARCHAR2
301 ,p_level IN NUMBER
302 ,p_module IN VARCHAR2) IS
303 BEGIN
304 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
305 fnd_log.message(p_level, NVL(p_module,C_DEFAULT_MODULE));
306 ELSIF p_level >= g_log_level THEN
307 fnd_log.string(p_level, NVL(p_module,C_DEFAULT_MODULE), p_msg);
308 END IF;
309
310 EXCEPTION
311 WHEN xla_exceptions_pkg.application_exception THEN
312 RAISE;
313 WHEN OTHERS THEN
314 xla_exceptions_pkg.raise_message
315 (p_location => 'xla_acct_analysis_rpt_pkg.trace');
316 END trace;
317
318 /*======================================================================+
319 | |
320 | Private Function |
321 | |
322 | get_flex_range_where |
323 | |
324 | |
325 | Return where clauses for flexfield ranges |
326 | |
327 +======================================================================*/
328
329 FUNCTION get_flex_range_where
330 (p_coa_id IN NUMBER
331 ,p_accounting_flexfield_from IN VARCHAR2
332 ,p_accounting_flexfield_to IN VARCHAR2) RETURN VARCHAR
333
334 IS
335
336 l_log_module VARCHAR2(240);
337 l_where VARCHAR2(32000);
338 l_bind_variables fnd_flex_xml_publisher_apis.bind_variables;
339 l_numof_bind_variables NUMBER;
340 l_segment_name VARCHAR2(30);
341 l_segment_value VARCHAR2(1000);
342 l_data_type VARCHAR2(30);
343
344 BEGIN
345
346 IF g_log_enabled THEN
347 l_log_module := C_DEFAULT_MODULE||'.get_flex_range_where';
348 END IF;
349 --
350 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
351
352 trace
353 (p_msg => 'BEGIN of get_flex_range_where'
354 ,p_level => C_LEVEL_PROCEDURE
355 ,p_module => l_log_module);
356
357 END IF;
358
359 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
360
361 trace
362 (p_msg => 'p_coa_id = '||to_char(p_coa_id)
363 ,p_level => C_LEVEL_STATEMENT
364 ,p_module=> l_log_module );
365
366 trace
367 (p_msg => 'p_accounting_flexfield_from = '||to_char(p_accounting_flexfield_from )
368 ,p_level => C_LEVEL_STATEMENT
369 ,p_module=> l_log_module );
370
371 trace
372 (p_msg => 'p_accounting_flexfield_to = '||to_char(p_accounting_flexfield_to)
373 ,p_level => C_LEVEL_STATEMENT
374 ,p_module=> l_log_module );
375
376 END IF;
377
378 --
379 -- e.g. l_where stores the following:
380 -- gcck.SEGMENT1 BETWEEN :FLEX_PARM1 AND :FLEX_PARM2
381 -- AND gcck.SEGMENT2 BETWEEN :FLEX_PARM3 AND :FLEX_PARM4 ...
382 --
383 fnd_flex_xml_publisher_apis.kff_where
384 (p_lexical_name => 'FLEX_PARM'
385 ,p_application_short_name => 'SQLGL'
386 ,p_id_flex_code => 'GL#'
387 ,p_id_flex_num => p_coa_id
388 ,p_code_combination_table_alias => 'gcck'
389 ,p_segments => 'ALL'
390 ,p_operator => 'BETWEEN'
391 ,p_operand1 => p_accounting_flexfield_from
392 ,p_operand2 => p_accounting_flexfield_to
393 ,x_where_expression => l_where
394 ,x_numof_bind_variables => l_numof_bind_variables
395 ,x_bind_variables => l_bind_variables);
396
397 FOR i IN l_bind_variables.FIRST .. l_bind_variables.LAST LOOP
398
399 l_segment_name := l_bind_variables(i).name;
400 l_data_type := l_bind_variables(i).data_type;
401
402 IF (l_data_type='VARCHAR2') THEN
403
404 l_segment_value := '''' || l_bind_variables(i).varchar2_value || '''';
405
406 ELSIF (l_data_type='NUMBER') THEN
407
408 l_segment_value := l_bind_variables(i).canonical_value;
409
410 ELSIF (l_data_type='DATE') THEN
411
412 l_segment_value := '''' || TO_CHAR(l_bind_variables(i).date_value
413 ,'yyyy-mm-dd HH24:MI:SS') || '''';
414
415 END IF;
416
417 --
418 -- Use REGEXP_REPLACE instead of REPLACE not to replace
419 -- string 'SEGMENT1' in 'SEGMENT10'.
420 -- REGEXP_REPLACE replaces the first occurent of a segment name
421 -- e.g.
422 -- BETWEEN :FLEX_PARM9 AND :FLEX_PARM10
423 -- =>
424 -- BETWEEN '000' AND '100'
425 --
426 l_where := REGEXP_REPLACE
427 (l_where
428 ,':' || l_segment_name
429 ,l_segment_value
430 ,1 -- Position
431 ,1 -- The first occurence
432 ,'c' -- Case sensitive
433 );
434
435 END LOOP ;
436
437 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
438
439 trace
440 (p_msg => 'END of get_flex_range_where'
441 ,p_level => C_LEVEL_PROCEDURE
442 ,p_module => l_log_module);
443
444 END IF;
445
446 RETURN l_where;
447
448 EXCEPTION
449 WHEN xla_exceptions_pkg.application_exception THEN
450 RAISE;
451 WHEN OTHERS THEN
452 xla_exceptions_pkg.raise_message
453 (p_location => 'xla_tb_report_pvt.get_flex_range_where');
454
455 END get_flex_range_where;
456 --=============================================================================
457 -- *********** public procedures and functions **********
458 --=============================================================================
459 --=============================================================================
460
461 --
462 --
463 --
464 --
465 --
466 --
467 --
468 -- Following are public routines
469 --
470 -- 1. beforeReport
471 --
472 --
473 --
474 --
475 --
476 --
477 --
478 --
479
480 --=============================================================================
481 --=============================================================================
482 --
483 --
484 --
485 --=============================================================================
486 FUNCTION beforeReport RETURN BOOLEAN IS
487
488 l_ledger_id NUMBER;
489 l_start_period_num NUMBER;
490 l_end_period_num NUMBER;
491 l_start_date DATE;
492 l_end_date DATE;
493 l_lang VARCHAR2(80);
494 l_count NUMBER;
495 l_coa_id NUMBER;
496 l_object_type VARCHAR2(30);
497 l_balancing_segment P_BALANCING_SEGMENT_FROM%TYPE;
498 l_account_segment P_ACCOUNT_SEGMENT_FROM%TYPE;
499 l_costcenter_segment VARCHAR2(80);
500 l_management_segment VARCHAR2(80);
501 l_intercompany_segment VARCHAR2(80);
502 l_alias_balancing_segment P_BALANCING_SEGMENT_FROM%TYPE;
503 l_alias_account_segment P_ACCOUNT_SEGMENT_FROM%TYPE;
504 l_alias_costcenter_segment l_costcenter_segment%TYPE;
505 l_alias_management_segment l_management_segment%TYPE;
506 l_alias_intercompany_segment l_intercompany_segment%TYPE;
507 l_seg_desc_column VARCHAR2(2000);
508 l_seg_desc_from p_seg_desc_from%TYPE;
509 l_seg_desc_join p_seg_desc_join%TYPE;
510 l_other_param_filter VARCHAR2(2000);
511 l_log_module VARCHAR2(240);
512 l_balance_query VARCHAR2(32000);
513 l_flex_range_where VARCHAR2(32000);
514 l_sla_other_filter p_sla_other_filter%TYPE:= ' ';
515 l_gl_other_filter p_gl_other_filter%TYPE := ' ';
516 l_ledger_set_from VARCHAR2(1000) := ' ';
517 l_ledger_set_where VARCHAR2(1000) := ' ';
518 i NUMBER;
519 l_conc_seg_delimiter VARCHAR2(80);
520 l_concat_segment VARCHAR2(4000);
521 l_array t_array_char;
522
523 l_ledgers VARCHAR2(1000);
524 l_fnd_flex_hint VARCHAR2(240);
525 l_hint VARCHAR2(240);
526 l_statistical VARCHAR2(50);
527
528 CURSOR c(p_coa_id number) IS
529 SELECT 'gcck.'||application_column_name seg
530 FROM fnd_id_flex_segments
531 WHERE application_id =101
532 AND id_flex_code ='GL#'
533 AND id_flex_num = p_coa_id
534 ORDER BY segment_num ;
535
536 l_je_source_name VARCHAR2(300); --bug9002134
537
538
539 BEGIN
540
541 --
542 -- default values
543 --
544 P_INCLUDE_ZERO_AMOUNT_LINES := NVL(P_INCLUDE_ZERO_AMOUNT_LINES,'N');
545 P_INCLUDE_USER_TRX_ID_FLAG := NVL(P_INCLUDE_USER_TRX_ID_FLAG,'N');
546 P_INCLUDE_TAX_DETAILS_FLAG := NVL(P_INCLUDE_TAX_DETAILS_FLAG,'N');
547 P_INCLUDE_LE_INFO_FLAG := NVL(P_INCLUDE_LE_INFO_FLAG,'NONE');
548 P_INCLUDE_STAT_AMOUNT_LINES := NVL(P_INCLUDE_STAT_AMOUNT_LINES,'N');
549
550 P_INCLUDE_ACCT_WITH_NO_ACT := NVL(P_INCLUDE_ACCT_WITH_NO_ACT,'N'); --bug#7386068
551
552 --
553 -- following will set the right transaction security
554 -- The transaction security in this case is "no security"
555 -- becuase the report is submitted from a GL responsibility
556 --
557 -- xla_security_pkg.set_security_context(602);
558
559 /*For bug#9002134 Account Analysis report can be run for a given je source.
560 This je source parameter by default would not be displayed and would be null.
561 If je source is null or All ie application id is null or 101 no security else security
562 for that je source.
563 For a given je source other than gl appropriate filters are added in the report xla and gl queries
564 and the period total field is calculated via query and is not taken from gl_balances table.
565 Hence the following piece of code
566 */
567
568 IF nvl(p_application_id,101) = 101 THEN
569 xla_security_pkg.set_security_context(602);
570
571 p_je_source_period := ' ,NULL JE_SOURCE_PERIOD_DR
572 ,NULL JE_SOURCE_PERIOD_CR';
573
574 ELSE
575 xla_security_pkg.set_security_context(p_application_id);
576
577 BEGIN
578 SELECT gjst.je_source_name
579 INTO l_je_source_name
580 FROM xla_subledgers xls, gl_je_sources_tl gjst
581 WHERE xls.application_id = p_application_id
582 AND xls.je_source_name = gjst.je_source_name
583 AND gjst.language = USERENV('LANG');
584
585
586 -- Bug 9668652
587 IF p_application_id IS NOT NULL THEN
588 p_sla_application_id_filter := ' AND ael.application_id = '|| p_application_id;
589 END IF;
590
591
592
593 IF p_application_id IS NOT NULL AND l_je_source_name IS NOT NULL THEN
594
595 p_sla_application_id_filter := p_sla_application_id_filter|| ' AND aeh.application_id = '|| p_application_id; -- Bug 9668652
596 p_gl_application_id_filter := ' AND gjh.je_source = '''
597 || l_je_source_name ||'''';
598
599 p_je_source_period := ' ,sum(TABLE1.ACCOUNTED_DR) OVER (partition by LEDGER_NAME, LEDGER_CURRENCY, BALANCE_TYPE_CODE,BUDGET_NAME, ENCUMBRANCE_TYPE, je_source_name, PERIOD_NAME, ACCOUNTING_CODE_COMBINATION ) JE_SOURCE_PERIOD_DR
600 ,sum(TABLE1.ACCOUNTED_CR) OVER (partition by LEDGER_NAME, LEDGER_CURRENCY, BALANCE_TYPE_CODE,BUDGET_NAME,ENCUMBRANCE_TYPE, je_source_name, PERIOD_NAME, ACCOUNTING_CODE_COMBINATION) JE_SOURCE_PERIOD_CR
601 ';
602
603
604 END IF;
605
606 EXCEPTION
607 WHEN no_data_found THEN
608 p_je_source_period := ' ,NULL JE_SOURCE_PERIOD_DR
609 ,NULL JE_SOURCE_PERIOD_CR';
610
611 END;
612
613 END IF;
614
615 --end bug#9002134
616
617
618 --
619 -- Transaction identifiers
620 -- As account analysis report goes accross application and SLA
621 -- does not support user trx ids in such a case, the following
622 -- code is not needed.
623 --
624 --uncommented for bug7514332
625 IF p_include_user_trx_id_flag = 'Y' THEN
626 xla_report_utility_pkg.get_transaction_id
627 (p_resp_application_id => p_resp_application_id
628 ,p_ledger_id => p_ledger_id
629 ,p_trx_identifiers_1 => p_trx_identifiers_1
630 ,p_trx_identifiers_2 => p_trx_identifiers_2
631 ,p_trx_identifiers_3 => p_trx_identifiers_3
632 ,p_trx_identifiers_4 => p_trx_identifiers_4
633 ,p_trx_identifiers_5 => p_trx_identifiers_5); --Added for bug 7580995
634 ELSE
635 p_trx_identifiers_1 := ',NULL USERIDS '; --Added for bug 7580995
636 END IF;
637 --uncommented for bug7514332
638 --
639 -- Identifying ledger as Ledger or Ledger Set and get value for language
640 --
641 SELECT object_type_code, USERENV('LANG')
642 INTO l_object_type, l_lang
643 FROM gl_ledgers
644 WHERE ledger_id = p_ledger_id;
645
646
647 --
648 -- build join condition based on if ledger passed is a ledger set or a ledger
649 --
650 IF l_object_type = 'S' THEN
651 l_ledgers := '(SELECT ledger_id '||
652 'FROM gl_ledger_set_assignments '||
653 'WHERE ledger_set_id = :P_LEDGER_ID)';
654
655 SELECT ledger_id
656 INTO l_ledger_id
657 FROM gl_ledger_set_assignments
658 WHERE ledger_set_id = p_ledger_id
659 AND ROWNUM = 1;
660
661 ELSE
662 l_ledgers := '(:P_LEDGER_ID)';
663
664 l_ledger_id := p_ledger_id;
665
666 END IF;
667
668 --
669 -- get effective period number for the from and to period
670 --
671 SELECT effective_period_num
672 ,START_DATE
673 INTO l_start_period_num
674 ,l_start_date
675 FROM gl_period_statuses
676 WHERE application_id = 101
677 AND ledger_id = l_ledger_id
678 AND period_name = p_period_from;
679
680 SELECT effective_period_num
681 ,end_date
682 INTO l_end_period_num
683 ,l_end_date
684 FROM gl_period_statuses
685 WHERE application_id = 101
686 AND ledger_id = l_ledger_id
687 AND period_name = p_period_to;
688
689 p_commercial_query := C_COMMERCIAL_QUERY;
690 p_vat_registration_query := C_VAT_REGISTRATION_QUERY;
691
692 --Added for bug 9011171,8762703
693 IF p_include_tax_details_flag = 'Y' THEN
694 p_tax_query := C_TAX_QUERY;
695 ELSE
696 p_tax_query := C_TAX_NULL_QUERY;
697 END IF;
698
699
700 --
701 -- Qualified segments
702 --
703 p_qualifier_segment := C_QUALIFIED_SEGMENT;
704
705
706 --
707 -- get COA for the ledger/ledger set
708 --
709
710 SELECT chart_of_accounts_id
711 INTO l_coa_id
712 FROM gl_ledgers
713 WHERE ledger_id = p_ledger_id;
714
715 -- Get concatenated segment
716
717 l_concat_segment := xla_report_utility_pkg.get_conc_segments(p_coa_id,'gcck');
718
719
720 ----------------------------------------------------------------------------
721 -- get qualifier segments for the COA
722 ----------------------------------------------------------------------------
723 xla_report_utility_pkg.get_acct_qualifier_segs
724 (p_coa_id => l_coa_id
725 ,p_balance_segment => l_balancing_segment
726 ,p_account_segment => l_account_segment
727 ,p_cost_center_segment => l_costcenter_segment
728 ,p_management_segment => l_management_segment
729 ,p_intercompany_segment => l_intercompany_segment);
730
731 --
732 -- attach table alias to the column names
733 --
734 IF l_balancing_segment = 'NULL' THEN
735 l_alias_balancing_segment := 'NULL';
736 ELSE
737 l_alias_balancing_segment := 'gcck.'||l_balancing_segment;
738 END IF;
739
740 IF l_account_segment = 'NULL' THEN
741 l_alias_account_segment := 'NULL';
742 ELSE
743 l_alias_account_segment := 'gcck.'||l_account_segment;
744 END IF;
745
746 IF l_costcenter_segment = 'NULL' THEN
747 l_alias_costcenter_segment := 'NULL';
748 ELSE
749 l_alias_costcenter_segment := 'gcck.'||l_costcenter_segment;
750 END IF;
751
752 IF l_management_segment = 'NULL' THEN
753 l_alias_management_segment := 'NULL';
754 ELSE
755 l_alias_management_segment := 'gcck.'||l_management_segment;
756 END IF;
757
758 IF l_intercompany_segment = 'NULL' THEN
759 l_alias_intercompany_segment := 'NULL';
760 ELSE
761 l_alias_intercompany_segment := 'gcck.'||l_intercompany_segment;
762 END IF;
763
764 --
765 -- replace placeholders for the qualified segemnts
766 --
767 p_qualifier_segment:= REPLACE(p_qualifier_segment
768 ,'$alias_balancing_segment$'
769 ,l_alias_balancing_segment);
770
771 p_qualifier_segment := REPLACE(p_qualifier_segment
772 ,'$alias_account_segment$'
773 ,l_alias_account_segment);
774
775 p_qualifier_segment := REPLACE(p_qualifier_segment
776 ,'$alias_costcenter_segment$'
777 ,l_alias_costcenter_segment);
778
779 p_qualifier_segment := REPLACE(p_qualifier_segment
780 ,'$alias_management_segment$'
781 ,l_alias_management_segment);
782
783 p_qualifier_segment := REPLACE(p_qualifier_segment
784 ,'$alias_intercompany_segment$'
785 ,l_alias_intercompany_segment);
786
787 -- bug 8295104
788
789 xla_report_utility_pkg.get_segment_info
790 (p_coa_id => l_coa_id
791 ,p_balancing_segment => l_balancing_segment
792 ,p_account_segment => l_account_segment
793 ,p_costcenter_segment => l_costcenter_segment
794 ,p_management_segment => l_management_segment
795 ,p_intercompany_segment => l_intercompany_segment
796 ,p_alias_balancing_segment => l_alias_balancing_segment
797 ,p_alias_account_segment => l_alias_account_segment
798 ,p_alias_costcenter_segment => l_alias_costcenter_segment
799 ,p_alias_management_segment => l_alias_management_segment
800 ,p_alias_intercompany_segment=> l_alias_intercompany_segment
801 ,p_seg_desc_column => l_seg_desc_column
802 ,p_seg_desc_from => l_seg_desc_from
803 ,p_seg_desc_join => l_seg_desc_join
804 ,p_hint => l_fnd_flex_hint
805 );
806
807
808 --l_hint := REPLACE(C_HINT,'$fnd_flex_hint$',l_fnd_flex_hint);
809 l_hint := C_HINT;
810 --modfied for bug#8554433
811
812 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
813 trace
814 (p_msg => 'seg_desc_column ='||l_seg_desc_column
815 ,p_level => C_LEVEL_STATEMENT
816 ,p_module=> l_log_module);
817 trace
818 (p_msg => 'seg_desc_from ='||l_seg_desc_from
819 ,p_level => C_LEVEL_STATEMENT
820 ,p_module=> l_log_module);
821 trace
822 (p_msg => 'seg_desc_join ='||l_seg_desc_join
823 ,p_level => C_LEVEL_STATEMENT
824 ,p_module=> l_log_module);
825 trace
826 (p_msg => 'l_hint ='||l_hint
827 ,p_level => C_LEVEL_STATEMENT
828 ,p_module=> l_log_module);
829 END IF;
830 --
831 -- replace placeholders for the qualified segemnts
832 --
833 p_qualifier_segment := REPLACE(p_qualifier_segment
834 ,'$seg_desc_column$'
835 ,l_seg_desc_column);
836
837 p_seg_desc_from := l_seg_desc_from;
838
839 p_seg_desc_join := l_seg_desc_join;
840
841
842
843 --
844 -- Legal Entity Information
845 --
846
847 --
848 -- Replace placeholders for Legal entity information
849 --
850 IF p_include_le_info_flag = 'LEGAL_ENTITY' THEN
851 p_legal_ent_col := C_LEGAL_ENT_COL;
852 p_legal_ent_from := C_LEGAL_ENT_FROM;
853 p_legal_ent_join := C_LEGAL_ENT_JOIN;
854
855 p_legal_ent_join := REPLACE(p_legal_ent_join,'$leg_seg_val$',l_balancing_segment);
856
857 IF p_legal_entity_id IS NOT NULL THEN
858 p_legal_ent_join := p_legal_ent_join ||
859 ' AND gle.legal_entity_id = '||p_legal_entity_id;
860 END IF;
861 ELSIF p_include_le_info_flag = 'ESTABLISHMENT' THEN
862 p_legal_ent_col := C_ESTBLISHMENT_COL;
863 p_legal_ent_from := C_ESTABLISHMENT_FROM;
864 p_legal_ent_join := C_ESTABLISHMENT_JOIN;
865
866 p_legal_ent_join := REPLACE(p_legal_ent_join,'$leg_seg_val$',l_balancing_segment);
867
868 IF p_legal_entity_id IS NOT NULL THEN
869 p_legal_ent_join := p_legal_ent_join ||
870 ' AND glv.legal_entity_id = '||p_legal_entity_id;
871 END IF;
872 ELSE -- p_include_le_info_flag = 'NONE' THEN
873 p_legal_ent_col := C_NULL_LEGAL_ENT_COL;
874 p_legal_ent_from := ' ';
875 p_legal_ent_join := ' ';
876
877 IF p_legal_entity_id IS NOT NULL THEN
878 p_legal_ent_from := ' ,gl_ledger_le_bsv_specific_v gle ';
879 p_legal_ent_join := ' AND gle.ledger_id(+) = TABLE1.LEDGER_ID '||
880 ' AND gle.segment_value(+) = TABLE1.$leg_seg_val$ '||
881 ' AND gle.legal_entity_id(+) = '||p_legal_entity_id;
882
883 p_legal_ent_join := REPLACE(p_legal_ent_join,'$leg_seg_val$',l_balancing_segment);
884 END IF;
885 END IF;
886
887
888 --
889 -- Third party information
890 --
891
892 -- 5072266 modify po_vendors.party_id to use ap_suppliers.vendor_id
893 -- po_vendors pov -> ap_suppliers ap
894 -- pov.segment1 -> ap.segment1
895 -- pov.vendor_name -> ap.vendor_name
896 -- pov.party_id -> ap_vendor_id
897 -- pov.party_id -> ap.vendor_id
898
899 /* Below the inner query is having join to xla_ae_lines ael2
900 because it seems that CASE statment doesn't allow to have
901 outer join from parent query column.So as a workaround we
902 have joined to xla_ae_lines ale2 and then through ale2 we
903 have outer joined to sites table for handling cases where
904 party_site_id can be NULL for a valid party_id
905 */
906
907 p_party_columns :=
908 ',CASE
909 WHEN ael.party_type_code = ''S'' THEN
910 (SELECT aps.segment1
911 ||''|''||aps.vendor_name
912 ||''|''||hzp.jgzz_fiscal_code
913 ||''|''||hzp.tax_reference
914 ||''|''||hps.party_site_number
915 ||''|''||hps.party_site_name
916 ||''|''||NULL
917 FROM ap_suppliers aps
918 ,ap_supplier_sites_all apss
919 ,hz_parties hzp
920 ,hz_party_sites hps
921 ,xla_ae_lines ael2
922 WHERE aps.vendor_id = ael2.party_id
923 AND hzp.party_id = aps.party_id
924 AND apss.vendor_site_id(+) = ael2.party_site_id
925 AND hps.party_site_id(+) = apss.party_site_id
926 AND ael2.application_id = ael.application_id
927 AND ael2.ae_header_id = ael.ae_header_id
928 AND ael2.ae_line_num = ael.ae_line_num )
929 WHEN ( ael.party_type_code = ''C'' AND ael.party_id is not null ) THEN
930 (SELECT hca.account_number
931 ||''|''||hzp.party_name
932 ||''|''||hzp.jgzz_fiscal_code
933 ||''|''||hzp.tax_reference
934 ||''|''||hps.party_site_number
935 ||''|''||hps.party_site_name
936 ||''|''||hzcu.tax_reference
937 FROM hz_cust_accounts hca
938 ,hz_cust_acct_sites_all hcas
939 ,hz_cust_site_uses_all hzcu
940 ,hz_parties hzp
941 ,hz_party_sites hps
942 ,xla_ae_lines ael2
943 WHERE hca.cust_account_id = ael2.party_id
944 AND hzp.party_id = hca.party_id
945 AND hzcu.site_use_id(+) = ael2.party_site_id
946 AND hcas.cust_acct_site_id(+) = hzcu.cust_acct_site_id
947 AND hps.party_site_id(+) = hcas.party_site_id
948 AND ael2.application_id = ael.application_id
949 AND ael2.ae_header_id = ael.ae_header_id
950 AND ael2.ae_line_num = ael.ae_line_num )
951 ELSE
952 NULL
953 END PARTY_INFO'; --bug 10425976
954 --===========================================================================
955 -- Build Filter condition based on parameters
956 --===========================================================================
957 --
958 -- Filter based on Balancing Segment Value
959 --
960 IF p_balancing_segment_from IS NOT NULL AND
961 p_balancing_segment_to IS NOT NULL
962 THEN
963 l_other_param_filter :=
964 l_other_param_filter ||' AND '||l_alias_balancing_segment||' BETWEEN '''
965 ||p_balancing_segment_from ||''' AND '''||p_balancing_segment_to||'''';
966 END IF;
967 --
968 -- Filter based on Natural Account Segment Value
969 --
970 IF p_account_segment_from IS NOT NULL AND
971 p_account_segment_to IS NOT NULL
972 THEN
973 l_other_param_filter :=
974 l_other_param_filter ||' AND '||l_alias_account_segment||' BETWEEN '''
975 ||p_account_segment_from ||''' AND '''||p_account_segment_to||'''';
976 END IF;
977
978 --
979 -- <conditions based on side>
980 --
981 IF UPPER(p_balance_side) = 'CREDIT' THEN
982 IF p_balance_amount_from IS NOT NULL THEN
983 l_other_param_filter :=
984 l_other_param_filter ||
985 ' AND ((NVL(glb.begin_balance_cr,0)+ NVL(glb.period_net_cr,0))
986 - (NVL(glb.begin_balance_dr,0)+ NVL(glb.period_net_dr,0)) ) > '
987 ||p_balance_amount_from ;
988 ELSE
989 l_other_param_filter :=
990 l_other_param_filter ||
991 ' AND ((NVL(glb.begin_balance_cr,0)+ NVL(glb.period_net_cr,0))
992 - (NVL(glb.begin_balance_dr,0)+ NVL(glb.period_net_dr,0))) > 0';
993 END IF;
994
995 IF p_balance_amount_to IS NOT NULL THEN
996 l_other_param_filter :=
997 l_other_param_filter ||
998 ' AND ((NVL(glb.begin_balance_cr,0)+ NVL(glb.period_net_cr,0))
999 - (NVL(glb.begin_balance_dr,0)+ NVL(glb.period_net_dr,0)) ) < '
1000 ||p_balance_amount_to;
1001 END IF;
1002 ELSIF UPPER(p_balance_side) = 'DEBIT' THEN
1003 IF p_balance_amount_from IS NOT NULL THEN
1004 l_other_param_filter :=
1005 l_other_param_filter ||
1006 ' AND ((NVL(glb.begin_balance_cr,0)+ NVL(glb.period_net_cr,0))
1007 - (NVL(glb.begin_balance_dr,0)+ NVL(glb.period_net_dr,0)) ) < -'
1008 ||p_balance_amount_from ;
1009 ELSE
1010 l_other_param_filter :=
1011 l_other_param_filter ||
1012 ' AND ((NVL(glb.begin_balance_cr,0)+ NVL(glb.period_net_cr,0))
1013 - (NVL(glb.begin_balance_dr,0)+ NVL(glb.period_net_dr,0))) < 0';
1014 END IF;
1015
1016 IF p_balance_amount_to IS NOT NULL THEN
1017 l_other_param_filter :=
1018 l_other_param_filter ||
1019 ' AND ((NVL(glb.begin_balance_cr,0)+ NVL(glb.period_net_cr,0))
1020 - (NVL(glb.begin_balance_dr,0)+ NVL(glb.period_net_dr,0)) ) > -'
1021 ||p_balance_amount_to;
1022 END IF;
1023 END IF;
1024
1025 --
1026 -- <conditions based on Balance Type >
1027 --
1028 IF p_balance_type_code IS NOT NULL THEN
1029 l_other_param_filter :=
1030 l_other_param_filter ||
1031 ' AND glb.actual_flag = '''||p_balance_type_code||'''';
1032
1033 END IF;
1034
1035 --
1036 -- <conditions based on Encumbrance Type>
1037 --
1038 IF p_encumbrance_type_id IS NOT NULL THEN
1039 l_other_param_filter :=
1040 l_other_param_filter ||
1041 ' AND glb.encumbrance_type_id = '||p_encumbrance_type_id;
1042 END IF;
1043
1044 --
1045 -- <conditions based on Budget Version>
1046 --
1047 IF p_budget_version_id IS NOT NULL THEN -- 4458381
1048 l_other_param_filter :=
1049 l_other_param_filter ||
1050 ' AND glb.budget_version_id = '||p_budget_version_id;
1051 END IF;
1052
1053 --
1054 -- <conditions for Include zero amount lines>
1055 --
1056 IF p_include_stat_amount_lines = 'Y' THEN
1057 l_statistical := ' IN (''STAT'', gl1.currency_code) ';
1058 ELSE
1059 l_statistical := ' = gl1.currency_code ';
1060 END IF;
1061
1062 IF p_include_zero_amount_lines = 'N' THEN
1063 /* l_other_param_filter :=
1064 l_other_param_filter ||
1065 ' AND (((NVL(glb.begin_balance_cr,0)-NVL(glb.begin_balance_dr,0)) <>0)
1066 OR (NVL(glb.period_net_cr,0) <>0 )
1067 OR (NVL(glb.period_net_dr,0) <> 0))'; */ --bug 9921498
1068
1069 l_sla_other_filter :=
1070 l_sla_other_filter ||
1071 ' AND (NVL(ael.accounted_dr,0) - NVL(ael.accounted_cr,0) <> 0)';
1072
1073 l_gl_other_filter :=
1074 l_gl_other_filter ||
1075 ' AND (NVL(gjl.accounted_dr,0) - NVL(gjl.accounted_cr,0) <> 0)';
1076 END IF;
1077
1078 -- bug 10425976
1079 p_main_col_start:=
1080 'TABLE1.GL_DATE GL_DATE
1081 ,TABLE1.CREATED_BY CREATED_BY
1082 ,TABLE1.CREATION_DATE CREATION_DATE
1083 ,TABLE1.LAST_UPDATE_DATE LAST_UPDATE_DATE
1084 ,TABLE1.GL_TRANSFER_DATE GL_TRANSFER_DATE
1085 ,TABLE1.REFERENCE_DATE REFERENCE_DATE
1086 ,TABLE1.COMPLETED_DATE COMPLETED_DATE
1087 ,TABLE1.TRANSACTION_NUMBER TRANSACTION_NUMBER
1088 ,TABLE1.TRANSACTION_DATE TRANSACTION_DATE
1089 ,TABLE1.ACCOUNTING_SEQUENCE_NAME ACCOUNTING_SEQUENCE_NAME
1090 ,TABLE1.ACCOUNTING_SEQUENCE_VERSION ACCOUNTING_SEQUENCE_VERSION
1091 ,TABLE1.ACCOUNTING_SEQUENCE_NUMBER ACCOUNTING_SEQUENCE_NUMBER
1092 ,TABLE1.REPORTING_SEQUENCE_NAME REPORTING_SEQUENCE_NAME
1093 ,TABLE1.REPORTING_SEQUENCE_VERSION REPORTING_SEQUENCE_VERSION
1094 ,TABLE1.REPORTING_SEQUENCE_NUMBER REPORTING_SEQUENCE_NUMBER
1095 ,TABLE1.DOCUMENT_CATEGORY DOCUMENT_CATEGORY
1096 ,TABLE1.DOCUMENT_SEQUENCE_NAME DOCUMENT_SEQUENCE_NAME
1097 ,TABLE1.DOCUMENT_SEQUENCE_NUMBER DOCUMENT_SEQUENCE_NUMBER
1098 ,TABLE1.GL_DOCUMENT_SEQUENCE_NAME GL_DOCUMENT_SEQUENCE_NAME
1099 ,TABLE1.GL_DOCUMENT_SEQUENCE_NUMBER GL_DOCUMENT_SEQUENCE_NUMBER
1100 ,TABLE1.APPLICATION_ID APPLICATION_ID
1101 ,TABLE1.APPLICATION_NAME APPLICATION_NAME
1102 ,TABLE1.HEADER_ID HEADER_ID
1103 ,TABLE1.HEADER_DESCRIPTION HEADER_DESCRIPTION
1104 ,TABLE1.FUND_STATUS FUND_STATUS
1105 ,TABLE1.JE_CATEGORY_NAME JE_CATEGORY_NAME
1106 ,TABLE1.JE_SOURCE_NAME JE_SOURCE_NAME
1107 ,TABLE1.EVENT_ID EVENT_ID
1108 ,TABLE1.EVENT_DATE EVENT_DATE
1109 ,TABLE1.EVENT_NUMBER EVENT_NUMBER
1110 ,TABLE1.EVENT_CLASS_CODE EVENT_CLASS_CODE
1111 ,TABLE1.EVENT_CLASS_NAME EVENT_CLASS_NAME
1112 ,TABLE1.EVENT_TYPE_CODE EVENT_TYPE_CODE
1113 ,TABLE1.EVENT_TYPE_NAME EVENT_TYPE_NAME
1114 ,TABLE1.GL_BATCH_NAME GL_BATCH_NAME
1115 ,TABLE1.POSTED_DATE POSTED_DATE
1116 ,TABLE1.GL_JE_NAME GL_JE_NAME
1117 ,TABLE1.GL_LINE_NUMBER GL_LINE_NUMBER
1118 ,TABLE1.LINE_NUMBER LINE_NUMBER
1119 ,TABLE1.ORIG_LINE_NUMBER ORIG_LINE_NUMBER
1120 ,TABLE1.ACCOUNTING_CLASS_CODE ACCOUNTING_CLASS_CODE
1121 ,TABLE1.ACCOUNTING_CLASS_NAME ACCOUNTING_CLASS_NAME
1122 ,TABLE1.LINE_DESCRIPTION LINE_DESCRIPTION
1123 ,TABLE1.ENTERED_CURRENCY ENTERED_CURRENCY
1124 ,TABLE1.CONVERSION_RATE CONVERSION_RATE
1125 ,TABLE1.CONVERSION_RATE_DATE CONVERSION_RATE_DATE
1126 ,TABLE1.CONVERSION_RATE_TYPE_CODE CONVERSION_RATE_TYPE_CODE
1127 ,TABLE1.CONVERSION_RATE_TYPE CONVERSION_RATE_TYPE
1128 ,TABLE1.ENTERED_DR ENTERED_DR
1129 ,TABLE1.ENTERED_CR ENTERED_CR
1130 ,TABLE1.UNROUNDED_ACCOUNTED_DR UNROUNDED_ACCOUNTED_DR
1131 ,TABLE1.UNROUNDED_ACCOUNTED_CR UNROUNDED_ACCOUNTED_CR
1132 ,TABLE1.ACCOUNTED_DR ACCOUNTED_DR
1133 ,TABLE1.ACCOUNTED_CR ACCOUNTED_CR
1134 ,TABLE1.STATISTICAL_AMOUNT STATISTICAL_AMOUNT
1135 ,TABLE1.RECONCILIATION_REFERENCE RECONCILIATION_REFERENCE
1136 ,TABLE1.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
1137 ,TABLE1.ATTRIBUTE1 ATTRIBUTE1
1138 ,TABLE1.ATTRIBUTE2 ATTRIBUTE2
1139 ,TABLE1.ATTRIBUTE3 ATTRIBUTE3
1140 ,TABLE1.ATTRIBUTE4 ATTRIBUTE4
1141 ,TABLE1.ATTRIBUTE5 ATTRIBUTE5
1142 ,TABLE1.ATTRIBUTE6 ATTRIBUTE6
1143 ,TABLE1.ATTRIBUTE7 ATTRIBUTE7
1144 ,TABLE1.ATTRIBUTE8 ATTRIBUTE8
1145 ,TABLE1.ATTRIBUTE9 ATTRIBUTE9
1146 ,TABLE1.ATTRIBUTE10 ATTRIBUTE10
1147 ,TABLE1.PARTY_TYPE_CODE PARTY_TYPE_CODE
1148 ,TABLE1.PARTY_TYPE PARTY_TYPE
1149 ,substr(PARTY_INFO,1,instr(PARTY_INFO,''|'',1,1)-1 ) PARTY_NUMBER
1150 ,substr(PARTY_INFO,instr(PARTY_INFO,''|'',1,1)+1,(instr(PARTY_INFO,''|'',1,2)-1-instr(PARTY_INFO,''|'',1,1))) PARTY_NAME
1151 ,substr(PARTY_INFO,instr(PARTY_INFO,''|'',1,2)+1,(instr(PARTY_INFO,''|'',1,3)-1-instr(PARTY_INFO,''|'',1,2))) PARTY_TYPE_TAXPAYER_ID
1152 ,substr(PARTY_INFO,instr(PARTY_INFO,''|'',1,3)+1,(instr(PARTY_INFO,''|'',1,4)-1-instr(PARTY_INFO,''|'',1,3))) PARTY_TAX_REGISTRATION_NUMBER
1153 ,substr(PARTY_INFO,instr(PARTY_INFO,''|'',1,4)+1,(instr(PARTY_INFO,''|'',1,5)-1-instr(PARTY_INFO,''|'',1,4))) PARTY_SITE_NUMBER
1154 ,substr(PARTY_INFO,instr(PARTY_INFO,''|'',1,5)+1,(instr(PARTY_INFO,''|'',1,6)-1-instr(PARTY_INFO,''|'',1,5))) PARTY_SITE_NAME
1155 ,substr(PARTY_INFO,instr(PARTY_INFO,''|'',1,6)+1,(length(PARTY_INFO)- instr(PARTY_INFO,''|'',1,6))) PARTY_SITE_TAX_RGSTN_NUMBER
1156 ,substr(USERIDS,1,instr(USERIDS,''|'',1,1)-1) USER_TRX_IDENTIFIER_NAME_1
1157 ,substr(USERIDS,instr(USERIDS,''|'',1,1)+1,(instr(USERIDS,''|'',1,2)-1-instr(USERIDS,''|'',1,1))) USER_TRX_IDENTIFIER_VALUE_1
1158 ,substr(USERIDS,instr(USERIDS,''|'',1,2)+1,(instr(USERIDS,''|'',1,3)-1-instr(USERIDS,''|'',1,2))) USER_TRX_IDENTIFIER_NAME_2
1159 ,substr(USERIDS,instr(USERIDS,''|'',1,3)+1,(instr(USERIDS,''|'',1,4)-1-instr(USERIDS,''|'',1,3))) USER_TRX_IDENTIFIER_VALUE_2
1160 ,substr(USERIDS,instr(USERIDS,''|'',1,4)+1,(instr(USERIDS,''|'',1,5)-1-instr(USERIDS,''|'',1,4))) USER_TRX_IDENTIFIER_NAME_3
1161 ,substr(USERIDS,instr(USERIDS,''|'',1,5)+1,(instr(USERIDS,''|'',1,6)-1-instr(USERIDS,''|'',1,5))) USER_TRX_IDENTIFIER_VALUE_3
1162 ,substr(USERIDS,instr(USERIDS,''|'',1,6)+1,(instr(USERIDS,''|'',1,7)-1-instr(USERIDS,''|'',1,6))) USER_TRX_IDENTIFIER_NAME_4
1163 ,substr(USERIDS,instr(USERIDS,''|'',1,7)+1,(instr(USERIDS,''|'',1,8)-1-instr(USERIDS,''|'',1,7))) USER_TRX_IDENTIFIER_VALUE_4
1164 ,substr(USERIDS,instr(USERIDS,''|'',1,8)+1,(instr(USERIDS,''|'',1,9)-1-instr(USERIDS,''|'',1,8))) USER_TRX_IDENTIFIER_NAME_5
1165 ,substr(USERIDS,instr(USERIDS,''|'',1,9)+1,(instr(USERIDS,''|'',1,10)-1-instr(USERIDS,''|'',1,9))) USER_TRX_IDENTIFIER_VALUE_5
1166 ,substr(USERIDS,instr(USERIDS,''|'',1,10)+1,(instr(USERIDS,''|'',1,11)-1-instr(USERIDS,''|'',1,10))) USER_TRX_IDENTIFIER_NAME_6
1167 ,substr(USERIDS,instr(USERIDS,''|'',1,11)+1,(instr(USERIDS,''|'',1,12)-1-instr(USERIDS,''|'',1,11))) USER_TRX_IDENTIFIER_VALUE_6
1168 ,substr(USERIDS,instr(USERIDS,''|'',1,12)+1,(instr(USERIDS,''|'',1,13)-1-instr(USERIDS,''|'',1,12))) USER_TRX_IDENTIFIER_NAME_7
1169 ,substr(USERIDS,instr(USERIDS,''|'',1,13)+1,(instr(USERIDS,''|'',1,14)-1-instr(USERIDS,''|'',1,13))) USER_TRX_IDENTIFIER_VALUE_7
1170 ,substr(USERIDS,instr(USERIDS,''|'',1,14)+1,(instr(USERIDS,''|'',1,15)-1-instr(USERIDS,''|'',1,14))) USER_TRX_IDENTIFIER_NAME_8
1171 ,substr(USERIDS,instr(USERIDS,''|'',1,15)+1,(instr(USERIDS,''|'',1,16)-1-instr(USERIDS,''|'',1,15))) USER_TRX_IDENTIFIER_VALUE_8
1172 ,substr(USERIDS,instr(USERIDS,''|'',1,16)+1,(instr(USERIDS,''|'',1,17)-1-instr(USERIDS,''|'',1,16))) USER_TRX_IDENTIFIER_NAME_9
1173 ,substr(USERIDS,instr(USERIDS,''|'',1,17)+1,(instr(USERIDS,''|'',1,18)-1-instr(USERIDS,''|'',1,17))) USER_TRX_IDENTIFIER_VALUE_9
1174 ,substr(USERIDS,instr(USERIDS,''|'',1,18)+1,(instr(USERIDS,''|'',1,19)-1-instr(USERIDS,''|'',1,18))) USER_TRX_IDENTIFIER_NAME_10
1175 ,substr(USERIDS,instr(USERIDS,''|'',1,19)+1,(length(USERIDS)-instr(USERIDS,''|'',1,19))) USER_TRX_IDENTIFIER_VALUE_10';
1176
1177 p_main_lgr_sgmt_col:=
1178 ',TABLE1.LEDGER_ID LEDGER_ID
1179 ,TABLE1.LEDGER_SHORT_NAME LEDGER_SHORT_NAME
1180 ,TABLE1.LEDGER_DESCRIPTION LEDGER_DESCRIPTION
1181 ,TABLE1.LEDGER_NAME LEDGER_NAME
1182 ,TABLE1.LEDGER_CURRENCY LEDGER_CURRENCY
1183 ,TABLE1.PERIOD_YEAR PERIOD_YEAR
1184 ,TABLE1.PERIOD_NUMBER PERIOD_NUMBER
1185 ,TABLE1.PERIOD_NAME PERIOD_NAME
1186 ,TABLE1.PERIOD_START_DATE PERIOD_START_DATE
1187 ,TABLE1.PERIOD_END_DATE PERIOD_END_DATE
1188 ,TABLE1.BALANCE_TYPE_CODE BALANCE_TYPE_CODE
1189 ,TABLE1.BALANCE_TYPE BALANCE_TYPE
1190 ,TABLE1.BUDGET_NAME BUDGET_NAME
1191 ,TABLE1.ENCUMBRANCE_TYPE ENCUMBRANCE_TYPE
1192 ,TABLE1.BEGIN_BALANCE_DR BEGIN_BALANCE_DR
1193 ,TABLE1.BEGIN_BALANCE_CR BEGIN_BALANCE_CR
1194 ,TABLE1.PERIOD_NET_DR PERIOD_NET_DR
1195 ,TABLE1.PERIOD_NET_CR PERIOD_NET_CR
1196 ,TABLE1.CODE_COMBINATION_ID CODE_COMBINATION_ID
1197 ,TABLE1.ACCOUNTING_CODE_COMBINATION ACCOUNTING_CODE_COMBINATION
1198 ,TABLE1.CODE_COMBINATION_DESCRIPTION CODE_COMBINATION_DESCRIPTION
1199 ,TABLE1.CONTROL_ACCOUNT_FLAG CONTROL_ACCOUNT_FLAG
1200 ,TABLE1.CONTROL_ACCOUNT CONTROL_ACCOUNT
1201 ,TABLE1.BALANCING_SEGMENT BALANCING_SEGMENT
1202 ,TABLE1.NATURAL_ACCOUNT_SEGMENT NATURAL_ACCOUNT_SEGMENT
1203 ,TABLE1.COST_CENTER_SEGMENT COST_CENTER_SEGMENT
1204 ,TABLE1.MANAGEMENT_SEGMENT MANAGEMENT_SEGMENT
1205 ,TABLE1.INTERCOMPANY_SEGMENT INTERCOMPANY_SEGMENT
1206 ,TABLE1.BALANCING_SEGMENT_DESC BALANCING_SEGMENT_DESC
1207 ,TABLE1.NATURAL_ACCOUNT_DESC NATURAL_ACCOUNT_DESC
1208 ,TABLE1.COST_CENTER_DESC COST_CENTER_DESC
1209 ,TABLE1.MANAGEMENT_SEGMENT_DESC MANAGEMENT_SEGMENT_DESC
1210 ,TABLE1.INTERCOMPANY_SEGMENT_DESC INTERCOMPANY_SEGMENT_DESC
1211 ,TABLE1.SEGMENT1 SEGMENT1
1212 ,TABLE1.SEGMENT2 SEGMENT2
1213 ,TABLE1.SEGMENT3 SEGMENT3
1214 ,TABLE1.SEGMENT4 SEGMENT4
1215 ,TABLE1.SEGMENT5 SEGMENT5
1216 ,TABLE1.SEGMENT6 SEGMENT6
1217 ,TABLE1.SEGMENT7 SEGMENT7
1218 ,TABLE1.SEGMENT8 SEGMENT8
1219 ,TABLE1.SEGMENT9 SEGMENT9
1220 ,TABLE1.SEGMENT10 SEGMENT10
1221 ,TABLE1.SEGMENT11 SEGMENT11
1222 ,TABLE1.SEGMENT12 SEGMENT12
1223 ,TABLE1.SEGMENT13 SEGMENT13
1224 ,TABLE1.SEGMENT14 SEGMENT14
1225 ,TABLE1.SEGMENT15 SEGMENT15
1226 ,TABLE1.SEGMENT16 SEGMENT16
1227 ,TABLE1.SEGMENT17 SEGMENT17
1228 ,TABLE1.SEGMENT18 SEGMENT18
1229 ,TABLE1.SEGMENT19 SEGMENT19
1230 ,TABLE1.SEGMENT20 SEGMENT20
1231 ,TABLE1.SEGMENT21 SEGMENT21
1232 ,TABLE1.SEGMENT22 SEGMENT22
1233 ,TABLE1.SEGMENT23 SEGMENT23
1234 ,TABLE1.SEGMENT24 SEGMENT24
1235 ,TABLE1.SEGMENT25 SEGMENT25
1236 ,TABLE1.SEGMENT26 SEGMENT26
1237 ,TABLE1.SEGMENT27 SEGMENT27
1238 ,TABLE1.SEGMENT28 SEGMENT28
1239 ,TABLE1.SEGMENT29 SEGMENT29
1240 ,TABLE1.SEGMENT30 SEGMENT30
1241 ,TABLE1.BEGIN_RUNNING_TOTAL_CR BEGIN_RUNNING_TOTAL_CR
1242 ,TABLE1.BEGIN_RUNNING_TOTAL_DR BEGIN_RUNNING_TOTAL_DR
1243 ,TABLE1.END_RUNNING_TOTAL_CR END_RUNNING_TOTAL_CR
1244 ,TABLE1.END_RUNNING_TOTAL_DR END_RUNNING_TOTAL_DR';
1245
1246 p_main_le_col:=
1247 ',TABLE1.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
1248 ,TABLE1.LEGAL_ENTITY_NAME LEGAL_ENTITY_NAME
1249 ,TABLE1.LE_ADDRESS_LINE_1 LE_ADDRESS_LINE_1
1250 ,TABLE1.LE_ADDRESS_LINE_2 LE_ADDRESS_LINE_2
1251 ,TABLE1.LE_ADDRESS_LINE_3 LE_ADDRESS_LINE_3
1252 ,TABLE1.LE_CITY LE_CITY
1253 ,TABLE1.LE_REGION_1 LE_REGION_1
1254 ,TABLE1.LE_REGION_2 LE_REGION_2
1255 ,TABLE1.LE_REGION_3 LE_REGION_3
1256 ,TABLE1.LE_POSTAL_CODE LE_POSTAL_CODE
1257 ,TABLE1.LE_COUNTRY LE_COUNTRY
1258 ,TABLE1.LE_REGISTRATION_NUMBER LE_REGISTRATION_NUMBER
1259 ,TABLE1.LE_REGISTRATION_EFFECTIVE_FROM LE_REGISTRATION_EFFECTIVE_FROM
1260 ,TABLE1.LE_BR_DAILY_INSCRIPTION_NUMBER LE_BR_DAILY_INSCRIPTION_NUMBER
1261 ,TABLE1.LE_BR_DAILY_INSCRIPTION_DATE LE_BR_DAILY_INSCRIPTION_DATE
1262 ,TABLE1.LE_BR_DAILY_ENTITY LE_BR_DAILY_ENTITY
1263 ,TABLE1.LE_BR_DAILY_LOCATION LE_BR_DAILY_LOCATION
1264 ,TABLE1.LE_BR_DIRECTOR_NUMBER LE_BR_DIRECTOR_NUMBER
1265 ,TABLE1.LE_BR_ACCOUNTANT_NUMBER LE_BR_ACCOUNTANT_NUMBER
1266 ,TABLE1.LE_BR_ACCOUNTANT_NAME LE_BR_ACCOUNTANT_NAME';
1267
1268 p_sla_col_start:=
1269 'SELECT /*+ leading (glbgt gjl gjh gir ael aeh) */
1270 to_char(aeh.accounting_date
1271 ,''YYYY-MM-DD'') GL_DATE
1272 ,fdu.user_name CREATED_BY
1273 ,to_char(aeh.creation_date
1274 ,''YYYY-MM-DD"T"hh:mi:ss'') CREATION_DATE
1275 ,to_char(aeh.last_update_date
1276 ,''YYYY-MM-DD'') LAST_UPDATE_DATE
1277 ,to_char(aeh.gl_transfer_date
1278 ,''YYYY-MM-DD"T"hh:mi:ss'') GL_TRANSFER_DATE
1279 ,to_char(aeh.reference_date
1280 ,''YYYY-MM-DD'') REFERENCE_DATE
1281 ,to_char(aeh.completed_date
1282 ,''YYYY-MM-DD"T"hh:mi:ss'') COMPLETED_DATE
1283 ,ent.transaction_number TRANSACTION_NUMBER
1284 ,to_char(xle.transaction_date
1285 ,''YYYY-MM-DD"T"hh:mi:ss'') TRANSACTION_DATE
1286 ,fsv1.header_name ACCOUNTING_SEQUENCE_NAME
1287 ,fsv1.version_name ACCOUNTING_SEQUENCE_VERSION
1288 ,aeh.completion_acct_seq_value ACCOUNTING_SEQUENCE_NUMBER
1289 ,fsv2.header_name REPORTING_SEQUENCE_NAME
1290 ,fsv2.version_name REPORTING_SEQUENCE_VERSION
1291 ,aeh.close_acct_seq_value REPORTING_SEQUENCE_NUMBER
1292 ,NULL DOCUMENT_CATEGORY
1293 ,fns.name DOCUMENT_SEQUENCE_NAME
1294 ,aeh.doc_sequence_value DOCUMENT_SEQUENCE_NUMBER
1295 ,fns1.name GL_DOCUMENT_SEQUENCE_NAME
1296 ,gjh.doc_sequence_value GL_DOCUMENT_SEQUENCE_NUMBER
1297 ,aeh.application_id APPLICATION_ID
1298 ,fap.application_name APPLICATION_NAME
1299 ,aeh.ae_header_id HEADER_ID
1300 ,aeh.description HEADER_DESCRIPTION
1301 ,xlk1.meaning FUND_STATUS
1302 ,gjct.user_je_category_name JE_CATEGORY_NAME
1303 ,gjst.user_je_source_name JE_SOURCE_NAME
1304 ,xle.event_id EVENT_ID
1305 ,to_char(xle.event_date
1306 ,''YYYY-MM-DD'') EVENT_DATE
1307 ,xle.event_number EVENT_NUMBER
1308 ,xet.event_class_code EVENT_CLASS_CODE
1309 ,xect.NAME EVENT_CLASS_NAME
1310 ,aeh.event_type_code EVENT_TYPE_CODE
1311 ,xet.NAME EVENT_TYPE_NAME
1312 ,gjb.NAME GL_BATCH_NAME
1313 ,to_char(gjb.posted_date
1314 ,''YYYY-MM-DD'') POSTED_DATE
1315 ,gjh.NAME GL_JE_NAME
1316 ,gjh.external_reference EXTERNAL_REFERENCE
1317 ,gjl.je_line_num GL_LINE_NUMBER
1318 ,ael.displayed_line_number LINE_NUMBER
1319 ,ael.ae_line_num ORIG_LINE_NUMBER
1320 ,ael.accounting_class_code ACCOUNTING_CLASS_CODE
1321 ,xlk2.meaning ACCOUNTING_CLASS_NAME
1322 ,ael.description LINE_DESCRIPTION
1323 ,ael.currency_code ENTERED_CURRENCY
1324 ,ael.currency_conversion_rate CONVERSION_RATE
1325 ,to_char(ael.currency_conversion_date
1326 ,''YYYY-MM-DD'') CONVERSION_RATE_DATE
1327 ,ael.currency_conversion_type CONVERSION_RATE_TYPE_CODE
1328 ,gdct.user_conversion_type CONVERSION_RATE_TYPE
1329 ,ael.entered_dr ENTERED_DR
1330 ,ael.entered_cr ENTERED_CR
1331 ,ael.unrounded_accounted_dr UNROUNDED_ACCOUNTED_DR
1332 ,ael.unrounded_accounted_cr UNROUNDED_ACCOUNTED_CR
1333 ,ael.accounted_dr ACCOUNTED_DR
1334 ,ael.accounted_cr ACCOUNTED_CR
1335 ,ael.statistical_amount STATISTICAL_AMOUNT
1336 ,ael.jgzz_recon_ref RECONCILIATION_REFERENCE
1337 ,ael.attribute_category ATTRIBUTE_CATEGORY
1338 ,ael.attribute1 ATTRIBUTE1
1339 ,ael.attribute2 ATTRIBUTE2
1340 ,ael.attribute3 ATTRIBUTE3
1341 ,ael.attribute4 ATTRIBUTE4
1342 ,ael.attribute5 ATTRIBUTE5
1343 ,ael.attribute6 ATTRIBUTE6
1344 ,ael.attribute7 ATTRIBUTE7
1345 ,ael.attribute8 ATTRIBUTE8
1346 ,ael.attribute9 ATTRIBUTE9
1347 ,ael.attribute10 ATTRIBUTE10
1348 ,ael.party_type_code PARTY_TYPE_CODE
1349 ,NULL PARTY_TYPE';
1350
1351 p_gt_lgr_sgmt_col:=
1352 ',glbgt.ledger_id LEDGER_ID
1353 ,glbgt.ledger_short_name LEDGER_SHORT_NAME
1354 ,glbgt.ledger_description LEDGER_DESCRIPTION
1355 ,glbgt.ledger_name LEDGER_NAME
1356 ,glbgt.ledger_currency LEDGER_CURRENCY
1357 ,glbgt.period_year PERIOD_YEAR
1358 ,glbgt.period_number PERIOD_NUMBER
1359 ,glbgt.period_name PERIOD_NAME
1360 ,to_char(glbgt.period_start_date
1361 ,''YYYY-MM-DD'') PERIOD_START_DATE
1362 ,to_char(glbgt.period_end_date
1363 ,''YYYY-MM-DD'') PERIOD_END_DATE
1364 ,glbgt.balance_type_code BALANCE_TYPE_CODE
1365 ,glbgt.balance_type BALANCE_TYPE
1366 ,glbgt.budget_name BUDGET_NAME
1367 ,glbgt.encumbrance_type ENCUMBRANCE_TYPE
1368 ,glbgt.begin_balance_dr BEGIN_BALANCE_DR
1369 ,glbgt.begin_balance_cr BEGIN_BALANCE_CR
1370 ,glbgt.period_net_dr PERIOD_NET_DR
1371 ,glbgt.period_net_cr PERIOD_NET_CR
1372 ,glbgt.code_combination_id CODE_COMBINATION_ID
1373 ,glbgt.accounting_code_combination ACCOUNTING_CODE_COMBINATION
1374 ,glbgt.code_combination_description CODE_COMBINATION_DESCRIPTION
1375 ,glbgt.control_account_flag CONTROL_ACCOUNT_FLAG
1376 ,glbgt.control_account CONTROL_ACCOUNT
1377 ,glbgt.balancing_segment BALANCING_SEGMENT
1378 ,glbgt.natural_account_segment NATURAL_ACCOUNT_SEGMENT
1379 ,glbgt.cost_center_segment COST_CENTER_SEGMENT
1380 ,glbgt.management_segment MANAGEMENT_SEGMENT
1381 ,glbgt.intercompany_segment INTERCOMPANY_SEGMENT
1382 ,glbgt.balancing_segment_desc BALANCING_SEGMENT_DESC
1383 ,glbgt.natural_account_desc NATURAL_ACCOUNT_DESC
1384 ,glbgt.cost_center_desc COST_CENTER_DESC
1385 ,glbgt.management_segment_desc MANAGEMENT_SEGMENT_DESC
1386 ,glbgt.intercompany_segment_desc INTERCOMPANY_SEGMENT_DESC
1387 ,glbgt.segment1 SEGMENT1
1388 ,glbgt.segment2 SEGMENT2
1389 ,glbgt.segment3 SEGMENT3
1390 ,glbgt.segment4 SEGMENT4
1391 ,glbgt.segment5 SEGMENT5
1392 ,glbgt.segment6 SEGMENT6
1393 ,glbgt.segment7 SEGMENT7
1394 ,glbgt.segment8 SEGMENT8
1395 ,glbgt.segment9 SEGMENT9
1396 ,glbgt.segment10 SEGMENT10
1397 ,glbgt.segment11 SEGMENT11
1398 ,glbgt.segment12 SEGMENT12
1399 ,glbgt.segment13 SEGMENT13
1400 ,glbgt.segment14 SEGMENT14
1401 ,glbgt.segment15 SEGMENT15
1402 ,glbgt.segment16 SEGMENT16
1403 ,glbgt.segment17 SEGMENT17
1404 ,glbgt.segment18 SEGMENT18
1405 ,glbgt.segment19 SEGMENT19
1406 ,glbgt.segment20 SEGMENT20
1407 ,glbgt.segment21 SEGMENT21
1408 ,glbgt.segment22 SEGMENT22
1409 ,glbgt.segment23 SEGMENT23
1410 ,glbgt.segment24 SEGMENT24
1411 ,glbgt.segment25 SEGMENT25
1412 ,glbgt.segment26 SEGMENT26
1413 ,glbgt.segment27 SEGMENT27
1414 ,glbgt.segment28 SEGMENT28
1415 ,glbgt.segment29 SEGMENT29
1416 ,glbgt.segment30 SEGMENT30
1417 ,glbgt.begin_running_total_cr BEGIN_RUNNING_TOTAL_CR
1418 ,glbgt.begin_running_total_dr BEGIN_RUNNING_TOTAL_DR
1419 ,glbgt.end_running_total_cr END_RUNNING_TOTAL_CR
1420 ,glbgt.end_running_total_dr END_RUNNING_TOTAL_DR';
1421
1422 p_gt_le_col:=
1423 ',glbgt.legal_entity_id LEGAL_ENTITY_ID
1424 ,glbgt.legal_entity_name LEGAL_ENTITY_NAME
1425 ,glbgt.le_address_line_1 LE_ADDRESS_LINE_1
1426 ,glbgt.le_address_line_2 LE_ADDRESS_LINE_2
1427 ,glbgt.le_address_line_3 LE_ADDRESS_LINE_3
1428 ,glbgt.le_city LE_CITY
1429 ,glbgt.le_region_1 LE_REGION_1
1430 ,glbgt.le_region_2 LE_REGION_2
1431 ,glbgt.le_region_3 LE_REGION_3
1432 ,glbgt.le_postal_code LE_POSTAL_CODE
1433 ,glbgt.le_country LE_COUNTRY
1434 ,glbgt.le_registration_number LE_REGISTRATION_NUMBER
1435 ,glbgt.le_registration_effective_from LE_REGISTRATION_EFFECTIVE_FROM
1436 ,glbgt.le_br_daily_inscription_number LE_BR_DAILY_INSCRIPTION_NUMBER
1437 ,to_char(glbgt.le_br_daily_inscription_date
1438 ,''YYYY-MM-DD'') LE_BR_DAILY_INSCRIPTION_DATE
1439 ,glbgt.le_br_daily_entity LE_BR_DAILY_ENTITY
1440 ,glbgt.le_br_daily_location LE_BR_DAILY_LOCATION
1441 ,glbgt.le_br_director_number LE_BR_DIRECTOR_NUMBER
1442 ,glbgt.le_br_accountant_number LE_BR_ACCOUNTANT_NUMBER
1443 ,glbgt.le_br_accountant_name LE_BR_ACCOUNTANT_NAME';
1444
1445 p_sla_from:=
1446 'FROM
1447 xla_ae_headers aeh
1448 ,xla_ae_lines ael
1449 ,xla_lookups xlk1
1450 ,xla_lookups xlk2
1451 ,xla_events xle
1452 ,xla_event_classes_tl xect
1453 ,xla_event_types_tl xet
1454 ,fnd_user fdu
1455 ,xla_transaction_entities ent
1456 ,fnd_application_tl fap
1457 ,fun_seq_versions fsv1
1458 ,fun_seq_versions fsv2
1459 ,fnd_document_sequences fns
1460 ,fnd_document_sequences fns1
1461 ,gl_je_categories_tl gjct
1462 ,gl_je_sources_tl gjst
1463 ,gl_daily_conversion_types gdct
1464 ,gl_import_references gir
1465 ,gl_je_lines gjl
1466 ,gl_je_headers gjh
1467 ,gl_je_batches gjb
1468 ,xla_report_balances_gt glbgt';
1469
1470 p_sla_main_filter:=
1471 'AND gjl.ledger_id = glbgt.ledger_id
1472 AND gjl.code_combination_id = glbgt.code_combination_id
1473 AND gjl.period_name = glbgt.period_name
1474 --AND gjl.effective_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
1475 --AND gjl.effective_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
1476 AND gjl.je_header_id = gjh.je_header_id
1477 AND gjl.period_name = gjh.period_name
1478 AND gjl.je_header_id = gir.je_header_id
1479 AND gjl.je_line_num = gir.je_line_num
1480 AND gjh.je_header_id = gir.je_header_id
1481 AND gjh.status = ''P''
1482 AND fns1.application_id(+) = 101
1483 AND fns1.doc_sequence_id(+) = gjh.doc_sequence_id
1484 AND NVL(gjh.je_from_sla_flag,''N'') IN(''Y'',''U'')
1485 AND gjb.je_batch_id = gir.je_batch_id
1486 AND gjb.status = ''P''
1487 AND gir.gl_sl_link_id = ael.gl_sl_link_id
1488 AND gir.gl_sl_link_table = ael.gl_sl_link_table
1489 --AND gjh.currency_code = glbgt.ledger_currency --added bug 6722505
1490 AND gjct.je_category_name = aeh.je_category_name
1491 AND gjct.LANGUAGE = USERENV(''LANG'')
1492 AND gjst.je_source_name = gjh.je_source
1493 AND gjst.LANGUAGE = USERENV(''LANG'')
1494 AND aeh.accounting_entry_status_code = ''F''
1495 AND aeh.gl_transfer_status_code = ''Y''
1496 AND aeh.balance_type_code = glbgt.balance_type_code
1497 AND NVL(aeh.budget_version_id,-19999) = NVL(glbgt.budget_version_id,-19999)
1498 AND ael.application_id = aeh.application_id
1499 AND ael.ae_header_id = aeh.ae_header_id
1500 AND NVL(ael.encumbrance_type_id,-19999)= NVL(glbgt.encumbrance_type_id,-19999) -- 4458381
1501 AND xlk1.lookup_type(+) = ''XLA_FUNDS_STATUS''
1502 AND xlk1.lookup_code(+) = aeh.funds_status_code
1503 AND xlk2.lookup_type = ''XLA_ACCOUNTING_CLASS''
1504 AND xlk2.lookup_code = ael.accounting_class_code
1505 AND xle.application_id = aeh.application_id
1506 AND xle.event_id = aeh.event_id
1507 AND xet.application_id = aeh.application_id
1508 AND xet.event_type_code = aeh.event_type_code
1509 AND xet.LANGUAGE = USERENV(''LANG'')
1510 AND xect.application_id = xet.application_id
1511 AND xect.entity_code = xet.entity_code
1512 AND xect.event_class_code = xet.event_class_code
1513 AND xect.LANGUAGE = USERENV(''LANG'')
1514 AND ent.application_id = aeh.application_id
1515 AND ent.entity_id = aeh.entity_id
1516 --AND ent.ledger_id = aeh.ledger_id removed for Bug 7557990
1517 AND fdu.user_id = ent.created_by
1518 AND fap.application_id = aeh.application_id
1519 AND fap.LANGUAGE = USERENV(''LANG'')
1520 AND fsv1.seq_version_id(+) = aeh.completion_acct_seq_version_id
1521 AND fsv2.seq_version_id(+) = aeh.close_acct_seq_version_id
1522 AND fns.application_id(+) = aeh.application_id
1523 AND fns.doc_sequence_id(+) = aeh.doc_sequence_id
1524 AND gdct.conversion_type(+) = ael.currency_conversion_type
1525 AND aeh.accounting_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
1526 AND aeh.accounting_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO';
1527
1528 p_gl_col_start:=
1529 'SELECT /*+ leading (glbgt gjl gjh gjb) */
1530 to_char(gjh.default_effective_date
1531 ,''YYYY-MM-DD'') GL_DATE
1532 ,fdu.user_name CREATED_BY
1533 ,to_char(gjh.creation_date
1534 ,''YYYY-MM-DD"T"hh:mi:ss'') CREATION_DATE
1535 ,to_char(gjh.last_update_date
1536 ,''YYYY-MM-DD'') LAST_UPDATE_DATE
1537 ,NULL GL_TRANSFER_DATE
1538 ,to_char(gjh.reference_date
1539 ,''YYYY-MM-DD'') REFERENCE_DATE
1540 ,NULL COMPLETED_DATE
1541 ,NULL TRANSACTION_NUMBER
1542 ,NULL TRANSACTION_DATE
1543 ,fsv1.header_name ACCOUNTING_SEQUENCE_NAME
1544 ,fsv1.version_name ACCOUNTING_SEQUENCE_VERSION
1545 ,gjh.posting_acct_seq_value ACCOUNTING_SEQUENCE_NUMBER
1546 ,fsv2.header_name REPORTING_SEQUENCE_NAME
1547 ,fsv2.version_name REPORTING_SEQUENCE_VERSION
1548 ,gjh.close_acct_seq_value REPORTING_SEQUENCE_NUMBER
1549 ,NULL DOCUMENT_CATEGORY
1550 ,NULL DOCUMENT_SEQUENCE_NAME
1551 ,NULL DOCUMENT_SEQUENCE_NUMBER
1552 ,fns.name GL_DOCUMENT_SEQUENCE_NAME
1553 ,gjh.doc_sequence_value GL_DOCUMENT_SEQUENCE_NUMBER
1554 ,NULL APPLICATION_ID
1555 ,NULL APPLICATION_NAME
1556 ,gjh.je_header_id HEADER_ID
1557 ,gjh.description HEADER_DESCRIPTION
1558 ,NULL FUND_STATUS
1559 ,gjct.user_je_category_name JE_CATEGORY_NAME
1560 ,gjst.user_je_source_name JE_SOURCE_NAME
1561 ,NULL EVENT_ID
1562 ,NULL EVENT_DATE
1563 ,NULL EVENT_NUMBER
1564 ,NULL EVENT_CLASS_CODE
1565 ,NULL EVENT_CLASS_NAME
1566 ,NULL EVENT_TYPE_CODE
1567 ,NULL EVENT_TYPE_NAME
1568 ,gjb.NAME GL_BATCH_NAME
1569 ,to_char(gjb.posted_date
1570 ,''YYYY-MM-DD'') POSTED_DATE
1571 ,gjh.NAME GL_JE_NAME
1572 ,gjh.external_reference EXTERNAL_REFERENCE
1573 ,gjl.je_line_num GL_LINE_NUMBER
1574 ,gjl.je_line_num LINE_NUMBER
1575 ,gjl.je_line_num ORIG_LINE_NUMBER
1576 ,NULL ACCOUNTING_CLASS_CODE
1577 ,NULL ACCOUNTING_CLASS_NAME
1578 ,gjl.description LINE_DESCRIPTION
1579 ,gjh.currency_code ENTERED_CURRENCY
1580 ,gjh.currency_conversion_rate CONVERSION_RATE
1581 ,to_char(gjh.currency_conversion_date
1582 ,''YYYY-MM-DD'') CONVERSION_RATE_DATE
1583 ,gjh.currency_conversion_type CONVERSION_RATE_TYPE_CODE
1584 ,gdct.user_conversion_type CONVERSION_RATE_TYPE
1585 ,gjl.entered_dr ENTERED_DR
1586 ,gjl.entered_cr ENTERED_CR
1587 ,NULL UNROUNDED_ACCOUNTED_DR
1588 ,NULL UNROUNDED_ACCOUNTED_CR
1589 ,gjl.accounted_dr ACCOUNTED_DR
1590 ,gjl.accounted_cr ACCOUNTED_CR
1591 ,gjl.stat_amount STATISTICAL_AMOUNT
1592 ,gjl.jgzz_recon_ref_11i RECONCILIATION_REFERENCE
1593 ,gjl.context ATTRIBUTE_CATEGORY
1594 ,gjl.attribute1 ATTRIBUTE1
1595 ,gjl.attribute2 ATTRIBUTE2
1596 ,gjl.attribute3 ATTRIBUTE3
1597 ,gjl.attribute4 ATTRIBUTE4
1598 ,gjl.attribute5 ATTRIBUTE5
1599 ,gjl.attribute6 ATTRIBUTE6
1600 ,gjl.attribute7 ATTRIBUTE7
1601 ,gjl.attribute8 ATTRIBUTE8
1602 ,gjl.attribute9 ATTRIBUTE9
1603 ,gjl.attribute10 ATTRIBUTE10
1604 ,NULL PARTY_TYPE_CODE
1605 ,NULL PARTY_TYPE
1606 ,NULL PARTY_INFO
1607 ,NULL USERIDS';
1608
1609 p_gl_from:=
1610 'FROM
1611 fnd_user fdu
1612 ,fun_seq_versions fsv1
1613 ,fun_seq_versions fsv2
1614 ,fnd_document_sequences fns
1615 ,gl_je_categories_tl gjct
1616 ,gl_je_sources_tl gjst
1617 ,gl_daily_conversion_types gdct
1618 ,gl_je_lines gjl
1619 ,gl_je_headers gjh
1620 ,gl_je_batches gjb
1621 ,xla_report_balances_gt glbgt';
1622
1623 p_gl_main_filter:=
1624 'WHERE gjl.ledger_id = glbgt.ledger_id
1625 AND gjl.code_combination_id = glbgt.code_combination_id
1626 AND gjl.effective_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
1627 AND gjl.effective_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
1628 AND gjl.period_name = glbgt.period_name
1629 AND gjh.je_header_id = gjl.je_header_id
1630 AND gjh.actual_flag = glbgt.balance_type_code
1631 AND decode(gjh.currency_code,''STAT'',gjh.currency_code,glbgt.ledger_currency) = glbgt.ledger_currency --added bug 6686541
1632 AND NVL(gjh.je_from_sla_flag,''N'') = ''N''
1633 AND NVL(gjh.budget_version_id,-19999)= NVL(glbgt.budget_version_id,-19999)
1634 AND NVL(gjh.encumbrance_type_id,-19999)= NVL(glbgt.encumbrance_type_id,-19999)
1635 AND gjb.je_batch_id = gjh.je_batch_id
1636 AND gjb.status = ''P''
1637 AND fns.application_id(+) = 101
1638 AND fns.doc_sequence_id(+) = gjh.doc_sequence_id
1639 AND fdu.user_id = gjb.created_by
1640 AND fsv1.seq_version_id(+) = gjh.posting_acct_seq_version_id
1641 AND fsv2.seq_version_id(+) = gjh.close_acct_seq_version_id
1642 AND gjct.je_category_name = gjh.je_category
1643 AND gjct.LANGUAGE = USERENV(''LANG'')
1644 AND gjst.je_source_name = gjh.je_source
1645 AND gjst.language = USERENV(''LANG'')
1646 AND gdct.conversion_type(+) = gjh.currency_conversion_type';
1647
1648 p_upg_gl_from:= p_gl_from || '
1649 ,fnd_new_messages fnm';
1650
1651 p_upg_gl_main_filter:=
1652 'WHERE gjl.ledger_id = glbgt.ledger_id
1653 AND gjl.code_combination_id = glbgt.code_combination_id
1654 AND gjl.effective_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
1655 AND gjl.effective_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
1656 AND gjl.period_name = glbgt.period_name
1657 AND gjh.je_header_id = gjl.je_header_id
1658 AND gjh.actual_flag = glbgt.balance_type_code
1659 AND decode(gjh.currency_code,''STAT'',gjh.currency_code,glbgt.ledger_currency) = glbgt.ledger_currency --added bug 6686541
1660 AND NVL(gjh.je_from_sla_flag,''N'') = ''U''
1661 AND fnm.application_id = 101
1662 AND fnm.language_code = USERENV(''LANG'')
1663 AND fnm.message_name in (''PPOS0220'', ''PPOS0221'', ''PPOS0222'', ''PPOS0243'', ''PPOS0222_G'',''PPOSO275'')
1664 AND gjl.description= fnm.message_text
1665 AND NVL(gjh.budget_version_id,-19999) = NVL(glbgt.budget_version_id,-19999)
1666 AND NVL(gjh.encumbrance_type_id,-19999) = NVL(glbgt.encumbrance_type_id,-19999)
1667 AND gjb.je_batch_id = gjh.je_batch_id
1668 AND gjb.status = ''P''
1669 AND fns.application_id(+) = 101
1670 AND fns.doc_sequence_id(+) = gjh.doc_sequence_id
1671 AND fdu.user_id = gjb.created_by
1672 AND fsv1.seq_version_id(+) = gjh.posting_acct_seq_version_id
1673 AND fsv2.seq_version_id(+) = gjh.close_acct_seq_version_id
1674 AND gjct.je_category_name = gjh.je_category
1675 AND gjct.LANGUAGE = USERENV(''LANG'')
1676 AND gjst.je_source_name = gjh.je_source
1677 AND gjst.language = USERENV(''LANG'')
1678 AND gdct.conversion_type(+) = gjh.currency_conversion_type
1679 AND not exists (select ''x'' from gl_import_references gir
1680 where gir.je_header_id=gjl.je_header_id
1681 and gir.je_line_num=gjl.je_line_num)';
1682
1683 p_order_by_clause:=
1684 'ORDER BY
1685 TABLE1.LEDGER_NAME
1686 ,TABLE1.LEDGER_CURRENCY
1687 ,TABLE1.ACCOUNTING_CODE_COMBINATION
1688 ,TABLE1.PERIOD_YEAR
1689 ,TABLE1.PERIOD_NUMBER
1690 ,TABLE1.GL_DATE
1691 ,TABLE1.BALANCE_TYPE_CODE
1692 ,TABLE1.BUDGET_NAME
1693 ,TABLE1.ENCUMBRANCE_TYPE
1694 ,TABLE1.JE_SOURCE_NAME
1695 ,TABLE1.HEADER_ID';
1696
1697 --end of bug 10425976
1698
1699
1700 --bug#7386068
1701 -- The below query should be executed in the XML if Include Accounts
1702 -- With No Activity parameter is set to Yes for thic conc program.
1703 -- This query selects those accounts having a beginning balance and no activity for
1704 -- the specified date range of the report.
1705
1706 IF P_INCLUDE_ACCT_WITH_NO_ACT = 'Y' THEN
1707
1708 p_begin_balance_union_all :=
1709 ' UNION ALL
1710 SELECT NULL GL_DATE
1711 ,NULL CREATED_BY
1712 ,NULL CREATION_DATE
1713 ,NULL LAST_UPDATE_DATE
1714 ,NULL GL_TRANSFER_DATE
1715 ,NULL REFERENCE_DATE
1716 ,NULL COMPLETED_DATE
1717 ,NULL TRANSACTION_NUMBER
1718 ,NULL TRANSACTION_DATE
1719 ,NULL ACCOUNTING_SEQUENCE_NAME
1720 ,NULL ACCOUNTING_SEQUENCE_VERSION
1721 ,NULL ACCOUNTING_SEQUENCE_NUMBER
1722 ,NULL REPORTING_SEQUENCE_NAME
1723 ,NULL REPORTING_SEQUENCE_VERSION
1724 ,NULL REPORTING_SEQUENCE_NUMBER
1725 ,NULL DOCUMENT_CATEGORY
1726 ,NULL DOCUMENT_SEQUENCE_NAME
1727 ,NULL DOCUMENT_SEQUENCE_NUMBER
1728 ,NULL GL_DOCUMENT_SEQUENCE_NAME -- added bug 9925564 .
1729 ,NULL GL_DOCUMENT_SEQUENCE_NUMBER
1730 ,NULL APPLICATION_ID
1731 ,NULL APPLICATION_NAME
1732 ,NULL HEADER_ID
1733 ,NULL HEADER_DESCRIPTION
1734 ,NULL FUND_STATUS
1735 ,NULL JE_CATEGORY_NAME
1736 ,NULL JE_SOURCE_NAME
1737 ,NULL EVENT_ID
1738 ,NULL EVENT_DATE
1739 ,NULL EVENT_NUMBER
1740 ,NULL EVENT_CLASS_CODE
1741 ,NULL EVENT_CLASS_NAME
1742 ,NULL EVENT_TYPE_CODE
1743 ,NULL EVENT_TYPE_NAME
1744 ,NULL GL_BATCH_NAME
1745 ,NULL POSTED_DATE
1746 ,NULL GL_JE_NAME
1747 ,NULL EXTERNAL_REFERENCE
1748 ,NULL GL_LINE_NUMBER
1749 ,NULL LINE_NUMBER
1750 ,NULL ORIG_LINE_NUMBER
1751 ,NULL ACCOUNTING_CLASS_CODE
1752 ,NULL ACCOUNTING_CLASS_NAME
1753 ,NULL LINE_DESCRIPTION
1754 ,NULL ENTERED_CURRENCY
1755 ,NULL CONVERSION_RATE
1756 ,NULL CONVERSION_RATE_DATE
1757 ,NULL CONVERSION_RATE_TYPE_CODE
1758 ,NULL CONVERSION_RATE_TYPE
1759 ,NULL ENTERED_DR
1760 ,NULL ENTERED_CR
1761 ,NULL UNROUNDED_ACCOUNTED_DR
1762 ,NULL UNROUNDED_ACCOUNTED_CR
1763 ,NULL ACCOUNTED_DR
1764 ,NULL ACCOUNTED_CR
1765 ,NULL STATISTICAL_AMOUNT
1766 ,NULL RECONCILIATION_REFERENCE
1767 ,NULL ATTRIBUTE_CATEGORY
1768 ,NULL ATTRIBUTE1
1769 ,NULL ATTRIBUTE2
1770 ,NULL ATTRIBUTE3
1771 ,NULL ATTRIBUTE4
1772 ,NULL ATTRIBUTE5
1773 ,NULL ATTRIBUTE6
1774 ,NULL ATTRIBUTE7
1775 ,NULL ATTRIBUTE8
1776 ,NULL ATTRIBUTE9
1777 ,NULL ATTRIBUTE10
1778 ,NULL PARTY_TYPE_CODE
1779 ,NULL PARTY_TYPE
1780 ,NULL PARTY_INFO
1781 ,NULL USERIDS
1782 ,glbgt.ledger_id LEDGER_ID
1783 ,glbgt.ledger_short_name LEDGER_SHORT_NAME
1784 ,glbgt.ledger_description LEDGER_DESCRIPTION
1785 ,glbgt.ledger_name LEDGER_NAME
1786 ,glbgt.ledger_currency LEDGER_CURRENCY
1787 ,glbgt.period_year PERIOD_YEAR
1788 ,glbgt.period_number PERIOD_NUMBER
1789 ,glbgt.period_name PERIOD_NAME
1790 ,to_char(glbgt.period_start_date
1791 ,''YYYY-MM-DD'') PERIOD_START_DATE
1792 ,to_char(glbgt.period_end_date
1793 ,''YYYY-MM-DD'') PERIOD_END_DATE
1794 ,glbgt.balance_type_code BALANCE_TYPE_CODE
1795 ,glbgt.balance_type BALANCE_TYPE
1796 ,glbgt.budget_name BUDGET_NAME
1797 ,glbgt.encumbrance_type ENCUMBRANCE_TYPE
1798 ,glbgt.begin_balance_dr BEGIN_BALANCE_DR
1799 ,glbgt.begin_balance_cr BEGIN_BALANCE_CR
1800 ,glbgt.period_net_dr PERIOD_NET_DR
1801 ,glbgt.period_net_cr PERIOD_NET_CR
1802 ,glbgt.code_combination_id CODE_COMBINATION_ID
1803 ,glbgt.accounting_code_combination ACCOUNTING_CODE_COMBINATION
1804 ,glbgt.code_combination_description CODE_COMBINATION_DESCRIPTION
1805 ,glbgt.control_account_flag CONTROL_ACCOUNT_FLAG
1806 ,glbgt.control_account CONTROL_ACCOUNT
1807 ,glbgt.balancing_segment BALANCING_SEGMENT
1808 ,glbgt.natural_account_segment NATURAL_ACCOUNT_SEGMENT
1809 ,glbgt.cost_center_segment COST_CENTER_SEGMENT
1810 ,glbgt.management_segment MANAGEMENT_SEGMENT
1811 ,glbgt.intercompany_segment INTERCOMPANY_SEGMENT
1812 ,glbgt.balancing_segment_desc BALANCING_SEGMENT_DESC
1813 ,glbgt.natural_account_desc NATURAL_ACCOUNT_DESC
1814 ,glbgt.cost_center_desc COST_CENTER_DESC
1815 ,glbgt.management_segment_desc MANAGEMENT_SEGMENT_DESC
1816 ,glbgt.intercompany_segment_desc INTERCOMPANY_SEGMENT_DESC
1817 ,glbgt.segment1 SEGMENT1
1818 ,glbgt.segment2 SEGMENT2
1819 ,glbgt.segment3 SEGMENT3
1820 ,glbgt.segment4 SEGMENT4
1821 ,glbgt.segment5 SEGMENT5
1822 ,glbgt.segment6 SEGMENT6
1823 ,glbgt.segment7 SEGMENT7
1824 ,glbgt.segment8 SEGMENT8
1825 ,glbgt.segment9 SEGMENT9
1826 ,glbgt.segment10 SEGMENT10
1827 ,glbgt.segment11 SEGMENT11
1828 ,glbgt.segment12 SEGMENT12
1829 ,glbgt.segment13 SEGMENT13
1830 ,glbgt.segment14 SEGMENT14
1831 ,glbgt.segment15 SEGMENT15
1832 ,glbgt.segment16 SEGMENT16
1833 ,glbgt.segment17 SEGMENT17
1834 ,glbgt.segment18 SEGMENT18
1835 ,glbgt.segment19 SEGMENT19
1836 ,glbgt.segment20 SEGMENT20
1837 ,glbgt.segment21 SEGMENT21
1838 ,glbgt.segment22 SEGMENT22
1839 ,glbgt.segment23 SEGMENT23
1840 ,glbgt.segment24 SEGMENT24
1841 ,glbgt.segment25 SEGMENT25
1842 ,glbgt.segment26 SEGMENT26
1843 ,glbgt.segment27 SEGMENT27
1844 ,glbgt.segment28 SEGMENT28
1845 ,glbgt.segment29 SEGMENT29
1846 ,glbgt.segment30 SEGMENT30
1847 ,glbgt.begin_running_total_cr BEGIN_RUNNING_TOTAL_CR
1848 ,glbgt.begin_running_total_dr BEGIN_RUNNING_TOTAL_DR
1849 ,glbgt.end_running_total_cr END_RUNNING_TOTAL_CR
1850 ,glbgt.end_running_total_dr END_RUNNING_TOTAL_DR
1851 ,glbgt.legal_entity_id LEGAL_ENTITY_ID
1852 ,glbgt.legal_entity_name LEGAL_ENTITY_NAME
1853 ,glbgt.le_address_line_1 LE_ADDRESS_LINE_1
1854 ,glbgt.le_address_line_2 LE_ADDRESS_LINE_2
1855 ,glbgt.le_address_line_3 LE_ADDRESS_LINE_3
1856 ,glbgt.le_city LE_CITY
1857 ,glbgt.le_region_1 LE_REGION_1
1858 ,glbgt.le_region_2 LE_REGION_2
1859 ,glbgt.le_region_3 LE_REGION_3
1860 ,glbgt.le_postal_code LE_POSTAL_CODE
1861 ,glbgt.le_country LE_COUNTRY
1862 ,glbgt.le_registration_number LE_REGISTRATION_NUMBER
1863 ,glbgt.le_registration_effective_from LE_REGISTRATION_EFFECTIVE_FROM
1864 ,glbgt.le_br_daily_inscription_number LE_BR_DAILY_INSCRIPTION_NUMBER
1865 ,to_char(glbgt.le_br_daily_inscription_date
1866 ,''YYYY-MM-DD'') LE_BR_DAILY_INSCRIPTION_DATE
1867 ,glbgt.le_br_daily_entity LE_BR_DAILY_ENTITY
1868 ,glbgt.le_br_daily_location LE_BR_DAILY_LOCATION
1869 ,glbgt.le_br_director_number LE_BR_DIRECTOR_NUMBER
1870 ,glbgt.le_br_accountant_number LE_BR_ACCOUNTANT_NUMBER
1871 ,glbgt.le_br_accountant_name LE_BR_ACCOUNTANT_NAME
1872 FROM xla_report_balances_gt glbgt
1873 WHERE nvl(period_net_dr,0) = 0
1874 AND nvl(period_net_cr,0) = 0
1875 AND (nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0) ) <> 0';
1876
1877 ELSE --bug 12329939
1878 l_other_param_filter := l_other_param_filter ||
1879 ' AND (((NVL(glb.begin_balance_cr,0)-NVL(glb.begin_balance_dr,0)) <> 0)'||
1880 ' OR (NVL(glb.period_net_cr,0) <> 0) OR (NVL(glb.period_net_dr,0) <> 0))';
1881
1882 END IF;
1883 --End bug#7386068
1884
1885
1886
1887
1888 --
1889 --<condition for the accounting flex field>
1890 --
1891
1892 IF p_account_flexfield_from IS NOT NULL THEN
1893 l_flex_range_where :=
1894 get_flex_range_where
1895 (p_coa_id => l_coa_id
1896 ,p_accounting_flexfield_from => p_account_flexfield_from
1897 ,p_accounting_flexfield_to => p_account_flexfield_to );
1898 l_other_param_filter := l_other_param_filter||' AND '||l_flex_range_where;
1899 END IF;
1900
1901 -- Bug 5914782
1902 p_ledger_filters:= ' gjh.ledger_id IN '||l_ledgers||
1903 ' AND glbgt.ledger_id IN '||l_ledgers||
1904 ' AND aeh.ledger_id IN '||l_ledgers;
1905 IF p_balance_type_code is NOT NULL THEN
1906 p_ledger_filters:= p_ledger_filters||' AND gjh.actual_flag = '''
1907 ||p_balance_type_code||'''';
1908 END IF;
1909
1910
1911 p_sla_other_filter:= l_sla_other_filter;
1912 p_gl_other_filter:= l_gl_other_filter;
1913
1914
1915 l_balance_query :=
1916 '
1917 INSERT INTO xla_report_balances_gt
1918 (ledger_id
1919 ,ledger_short_name
1920 ,ledger_description
1921 ,ledger_name
1922 ,ledger_currency
1923 ,period_year
1924 ,period_number
1925 ,period_name
1926 ,period_start_date
1927 ,period_end_date
1928 ,balance_type_code
1929 ,balance_type
1930 ,budget_version_id
1931 ,budget_name
1932 ,encumbrance_type_id
1933 ,encumbrance_type
1934 ,begin_balance_dr
1935 ,begin_balance_cr
1936 ,period_net_dr
1937 ,period_net_cr
1938 ,code_combination_id
1939 ,accounting_code_combination
1940 ,code_combination_description
1941 ,control_account_flag
1942 ,control_account
1943 ,balancing_segment
1944 ,natural_account_segment
1945 ,cost_center_segment
1946 ,management_segment
1947 ,intercompany_segment
1948 ,balancing_segment_desc
1949 ,natural_account_desc
1950 ,cost_center_desc
1951 ,management_segment_desc
1952 ,intercompany_segment_desc
1953 ,segment1
1954 ,segment2
1955 ,segment3
1956 ,segment4
1957 ,segment5
1958 ,segment6
1959 ,segment7
1960 ,segment8
1961 ,segment9
1962 ,segment10
1963 ,segment11
1964 ,segment12
1965 ,segment13
1966 ,segment14
1967 ,segment15
1968 ,segment16
1969 ,segment17
1970 ,segment18
1971 ,segment19
1972 ,segment20
1973 ,segment21
1974 ,segment22
1975 ,segment23
1976 ,segment24
1977 ,segment25
1978 ,segment26
1979 ,segment27
1980 ,segment28
1981 ,segment29
1982 ,segment30
1983 ,legal_entity_id
1984 ,legal_entity_name
1985 ,le_address_line_1
1986 ,le_address_line_2
1987 ,le_address_line_3
1988 ,le_city
1989 ,le_region_1
1990 ,le_region_2
1991 ,le_region_3
1992 ,le_postal_code
1993 ,le_country
1994 ,le_registration_number
1995 ,le_registration_effective_from
1996 ,le_br_daily_inscription_number
1997 ,le_br_daily_inscription_date
1998 ,le_br_daily_entity
1999 ,le_br_daily_location
2000 ,le_br_director_number
2001 ,le_br_accountant_number
2002 ,le_br_accountant_name)
2003 (
2004 SELECT TABLE1.LEDGER_ID
2005 ,TABLE1.LEDGER_SHORT_NAME
2006 ,TABLE1.LEDGER_DESCRIPTION
2007 ,TABLE1.LEDGER_NAME
2008 ,TABLE1.LEDGER_CURRENCY
2009 ,TABLE1.PERIOD_YEAR
2010 ,TABLE1.PERIOD_NUMBER
2011 ,TABLE1.PERIOD_NAME
2012 ,TABLE1.PERIOD_START_DATE
2013 ,TABLE1.PERIOD_END_DATE
2014 ,TABLE1.BALANCE_TYPE_CODE
2015 ,TABLE1.BALANCE_TYPE
2016 ,TABLE1.BUDGET_VERSION_ID
2017 ,TABLE1.BUDGET_NAME
2018 ,TABLE1.ENCUMBRANCE_TYPE_ID
2019 ,TABLE1.ENCUMBRANCE_TYPE
2020 ,TABLE1.BEGIN_BALANCE_DR
2021 ,TABLE1.BEGIN_BALANCE_CR
2022 ,TABLE1.PERIOD_NET_DR
2023 ,TABLE1.PERIOD_NET_CR
2024 ,TABLE1.CODE_COMBINATION_ID
2025 ,TABLE1.ACCOUNTING_CODE_COMBINATION
2026 ,TABLE1.CODE_COMBINATION_DESCRIPTION
2027 ,TABLE1.CONTROL_ACCOUNT_FLAG
2028 ,TABLE1.CONTROL_ACCOUNT
2029 ,TABLE1.BALANCING_SEGMENT
2030 ,TABLE1.NATURAL_ACCOUNT_SEGMENT
2031 ,TABLE1.COST_CENTER_SEGMENT
2032 ,TABLE1.MANAGEMENT_SEGMENT
2033 ,TABLE1.INTERCOMPANY_SEGMENT
2034 ,TABLE1.BALANCING_SEGMENT_DESC
2035 ,TABLE1.NATURAL_ACCOUNT_DESC
2036 ,TABLE1.COST_CENTER_DESC
2037 ,TABLE1.MANAGEMENT_SEGMENT_DESC
2038 ,TABLE1.INTERCOMPANY_SEGMENT_DESC
2039 ,TABLE1.SEGMENT1
2040 ,TABLE1.SEGMENT2
2041 ,TABLE1.SEGMENT3
2042 ,TABLE1.SEGMENT4
2043 ,TABLE1.SEGMENT5
2044 ,TABLE1.SEGMENT6
2045 ,TABLE1.SEGMENT7
2046 ,TABLE1.SEGMENT8
2047 ,TABLE1.SEGMENT9
2048 ,TABLE1.SEGMENT10
2049 ,TABLE1.SEGMENT11
2050 ,TABLE1.SEGMENT12
2051 ,TABLE1.SEGMENT13
2052 ,TABLE1.SEGMENT14
2053 ,TABLE1.SEGMENT15
2054 ,TABLE1.SEGMENT16
2055 ,TABLE1.SEGMENT17
2056 ,TABLE1.SEGMENT18
2057 ,TABLE1.SEGMENT19
2058 ,TABLE1.SEGMENT20
2059 ,TABLE1.SEGMENT21
2060 ,TABLE1.SEGMENT22
2061 ,TABLE1.SEGMENT23
2062 ,TABLE1.SEGMENT24
2063 ,TABLE1.SEGMENT25
2064 ,TABLE1.SEGMENT26
2065 ,TABLE1.SEGMENT27
2066 ,TABLE1.SEGMENT28
2067 ,TABLE1.SEGMENT29
2068 ,TABLE1.SEGMENT30
2069 $legal_entity_columns$
2070 FROM
2071 (SELECT $hint$
2072 gl1.ledger_id LEDGER_ID
2073 ,gl1.short_name LEDGER_SHORT_NAME
2074 ,gl1.description LEDGER_DESCRIPTION
2075 ,gl1.NAME LEDGER_NAME
2076 ,glb.currency_code LEDGER_CURRENCY
2077 ,glb.period_year PERIOD_YEAR
2078 ,glb.period_num PERIOD_NUMBER
2079 ,glb.period_name PERIOD_NAME
2080 ,gl1.START_DATE PERIOD_START_DATE
2081 ,gl1.end_date PERIOD_END_DATE
2082 ,glb.actual_flag BALANCE_TYPE_CODE
2083 ,xlk.meaning BALANCE_TYPE
2084 ,glb.budget_version_id BUDGET_VERSION_ID
2085 ,glv.budget_name BUDGET_NAME
2086 ,glb.encumbrance_type_id ENCUMBRANCE_TYPE_ID
2087 ,get.encumbrance_type ENCUMBRANCE_TYPE
2088 ,NVL(glb.begin_balance_dr,0) BEGIN_BALANCE_DR
2089 ,NVL(glb.begin_balance_cr,0) BEGIN_BALANCE_CR
2090 ,NVL(glb.period_net_dr,0) PERIOD_NET_DR
2091 ,NVL(glb.period_net_cr,0) PERIOD_NET_CR
2092 ,glb.code_combination_id CODE_COMBINATION_ID
2093 ,$concat_segments$ ACCOUNTING_CODE_COMBINATION
2094 ,xla_report_utility_pkg.get_ccid_desc
2095 (gl1.chart_of_accounts_id
2096 ,glb.code_combination_id) CODE_COMBINATION_DESCRIPTION
2097 ,gcck.reference3 CONTROL_ACCOUNT_FLAG
2098 ,NULL CONTROL_ACCOUNT
2099 $seg_desc_column$
2100 ,gcck.segment1 SEGMENT1
2101 ,gcck.segment2 SEGMENT2
2102 ,gcck.segment3 SEGMENT3
2103 ,gcck.segment4 SEGMENT4
2104 ,gcck.segment5 SEGMENT5
2105 ,gcck.segment6 SEGMENT6
2106 ,gcck.segment7 SEGMENT7
2107 ,gcck.segment8 SEGMENT8
2108 ,gcck.segment9 SEGMENT9
2109 ,gcck.segment10 SEGMENT10
2110 ,gcck.segment11 SEGMENT11
2111 ,gcck.segment12 SEGMENT12
2112 ,gcck.segment13 SEGMENT13
2113 ,gcck.segment14 SEGMENT14
2114 ,gcck.segment15 SEGMENT15
2115 ,gcck.segment16 SEGMENT16
2116 ,gcck.segment17 SEGMENT17
2117 ,gcck.segment18 SEGMENT18
2118 ,gcck.segment19 SEGMENT19
2119 ,gcck.segment20 SEGMENT20
2120 ,gcck.segment21 SEGMENT21
2121 ,gcck.segment22 SEGMENT22
2122 ,gcck.segment23 SEGMENT23
2123 ,gcck.segment24 SEGMENT24
2124 ,gcck.segment25 SEGMENT25
2125 ,gcck.segment26 SEGMENT26
2126 ,gcck.segment27 SEGMENT27
2127 ,gcck.segment28 SEGMENT28
2128 ,gcck.segment29 SEGMENT29
2129 ,gcck.segment30 SEGMENT30
2130 FROM (SELECT /*+ no_merge */
2131 gll.ledger_id
2132 ,gll.short_name
2133 ,gll.description
2134 ,gll.name
2135 ,gll.currency_code
2136 ,gll.chart_of_accounts_id
2137 ,gls.period_name
2138 ,gls.start_date
2139 ,gls.end_date
2140 FROM gl_ledgers gll
2141 ,gl_period_statuses gls
2142 WHERE gls.ledger_id = gll.ledger_id
2143 AND gls.application_id = 101
2144 AND gls.effective_period_num BETWEEN :P_START_PERIOD_NUM AND :P_END_PERIOD_NUM
2145 AND gll.ledger_id IN $ledger_id$
2146 ) gl1
2147 ,gl_balances glb
2148 ,gl_code_combinations gcck
2149 ,xla_lookups xlk
2150 ,gl_budget_versions glv
2151 ,gl_encumbrance_types get
2152 $seg_desc_from$
2153 WHERE glb.ledger_id = gl1.ledger_id
2154 AND glb.currency_code $statistical$
2155 AND glb.period_name = gl1.period_name
2156 AND glb.template_id IS null
2157 AND gcck.code_combination_id = glb.code_combination_id
2158 AND gcck.chart_of_accounts_id = gl1.chart_of_accounts_id --12329939
2159 AND xlk.lookup_type = ''XLA_BALANCE_TYPE''
2160 AND xlk.lookup_code = glb.actual_flag
2161 AND glv.budget_version_id(+) = glb.budget_version_id
2162 AND get.encumbrance_type_id(+) = glb.encumbrance_type_id
2163 $seg_desc_join$
2164 $other_param_filter$) TABLE1
2165 $legal_entity_from$
2166 WHERE 1 = 1
2167 $legal_entity_join$
2168 )' ;
2169
2170
2171
2172 l_balance_query :=
2173 REPLACE(l_balance_query,'$legal_entity_columns$',p_legal_ent_col);
2174 l_balance_query :=
2175 REPLACE(l_balance_query,'$seg_desc_column$',p_qualifier_segment);
2176 l_balance_query :=
2177 REPLACE(l_balance_query,'$legal_entity_from$',p_legal_ent_from);
2178 l_balance_query :=
2179 REPLACE(l_balance_query,'$seg_desc_from$',p_seg_desc_from);
2180 l_balance_query :=
2181 REPLACE(l_balance_query,'$other_param_filter$',l_other_param_filter);
2182 l_balance_query :=
2183 REPLACE(l_balance_query,'$legal_entity_join$',p_legal_ent_join);
2184 l_balance_query :=
2185 REPLACE(l_balance_query,'$seg_desc_join$',p_seg_desc_join);
2186 l_balance_query :=
2187 REPLACE(l_balance_query,'$concat_segments$',l_concat_segment);
2188 l_balance_query :=
2189 REPLACE(l_balance_query,'$ledger_id$',l_ledgers);
2190 l_balance_query :=
2191 REPLACE(l_balance_query,'$hint$',l_hint);
2192
2193 l_balance_query :=
2194 REPLACE(l_balance_query,'$statistical$',l_statistical);
2195
2196 EXECUTE IMMEDIATE l_balance_query
2197 USING l_start_period_num
2198 ,l_end_period_num
2199 ,p_ledger_id;
2200
2201 IF ((p_gl_date_from > l_start_date) OR (p_gl_date_to < l_end_date)) THEN
2202 UPDATE xla_report_balances_gt xrb
2203 SET (begin_running_total_cr
2204 ,begin_running_Total_dr
2205 ,end_running_total_cr
2206 ,end_running_total_dr) =
2207
2208 (SELECT SUM(CASE
2209 WHEN gjl.effective_date < p_gl_date_from THEN
2210 accounted_cr
2211 ELSE
2212 0
2213 END ) BEGIN_RUNNING_TOTAL_CR
2214
2215 , SUM(CASE
2216 WHEN gjl.effective_date < p_gl_date_from THEN
2217 accounted_dr
2218 ELSE
2219 0
2220 END ) BEGIN_RUNNING_TOTAL_DR
2221
2222 ,SUM(CASE
2223 WHEN gjl.effective_date > p_gl_date_to THEN
2224 accounted_cr
2225 ELSE
2226 0
2227 END ) END_RUNNING_TOTAL_CR
2228 ,SUM(CASE
2229 WHEN gjl.effective_date > p_gl_date_to THEN
2230 accounted_dr
2231 ELSE
2232 0
2233 END ) END_RUNNING_TOTAL_DR
2234
2235 FROM gl_je_headers gjh
2236 ,gl_je_lines gjl
2237 WHERE gjh.je_header_id = gjl.je_header_id
2238 AND gjh.status = 'P'
2239 AND gjl.status = 'P' -- Bug 9668652
2240 AND gjh.ledger_id = xrb.ledger_id
2241 AND gjl.period_name = xrb.period_name -- Bug 9668652
2242 AND gjh.actual_flag = xrb.balance_type_code
2243 AND gjl.code_combination_id = xrb.code_combination_id
2244 )
2245 WHERE xrb.period_name IN (p_period_from,p_period_to);
2246 END IF;
2247
2248 RETURN TRUE;
2249
2250 EXCEPTION
2251 WHEN OTHERS THEN
2252 xla_exceptions_pkg.raise_message
2253 (p_location => 'xla_acct_analysis_rpt_pkg.beforeReport ');
2254 END beforeReport;
2255
2256
2257
2258 --=============================================================================
2259 -- *********** Initialization routine **********
2260 --=============================================================================
2261
2262 --=============================================================================
2263 --
2264 --
2265 --
2266 --
2267 --
2268 --
2269 --
2270 --
2271 --
2272 --
2273 -- Following code is executed when the package body is referenced for the first
2274 -- time
2275 --
2276 --
2277 --
2278 --
2279 --
2280 --
2281 --
2282 --
2283 --
2284 --
2285 --
2286 --
2287 --=============================================================================
2288
2289 BEGIN
2290 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2291 g_log_enabled := fnd_log.test
2292 (log_level => g_log_level
2293 ,MODULE => C_DEFAULT_MODULE);
2294
2295 IF NOT g_log_enabled THEN
2296 g_log_level := C_LEVEL_LOG_DISABLED;
2297 END IF;
2298
2299
2300 END XLA_ACCT_ANALYSIS_RPT_PKG;