[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.25.12010000.3 2008/11/24 10:14:09 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 +===========================================================================*/
28
29 --=============================================================================
30 -- **************** declarations ********************
31 --=============================================================================
32
33 TYPE t_array_char IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
34
35 -------------------------------------------------------------------------------
36 -- constant for getting flexfield segment value description
37 -------------------------------------------------------------------------------
38 C_SEG_DESC_JOIN CONSTANT VARCHAR2(1000) :=
39 ' AND $alias$_b.flex_value_set_id = $flex_value_set_id$ AND '||
40 ' $alias$_b.flex_value = $segment_column$ AND '||
41 ' $alias$.flex_value_id = $alias$_b.flex_value_id AND '||
42 ' $alias$.language = USERENV(''LANG'')';
43
44 -------------------------------------------------------------------------------
45 -- constant for getting leagal entity information
46 -------------------------------------------------------------------------------
47 C_NULL_LEGAL_ENT_COL CONSTANT VARCHAR2(4000) :=
48 ' ,NULL LEGAL_ENTITY_ID
49 ,NULL LEGAL_ENTITY_NAME
50 ,NULL LE_ADDRESS_LINE_1
51 ,NULL LE_ADDRESS_LINE_2
52 ,NULL LE_ADDRESS_LINE_3
53 ,NULL LE_CITY
54 ,NULL LE_REGION_1
55 ,NULL LE_REGION_2
56 ,NULL LE_REGION_3
57 ,NULL LE_POSTAL_CODE
58 ,NULL LE_COUNTRY
59 ,NULL LE_REGISTRATION_NUMBER
60 ,NULL LE_REGISTRATION_EFFECTIVE_FROM
61 ,NULL LE_BR_DAILY_INSCRIPTION_NUMBER
62 ,NULL LE_BR_DAILY_INSCRIPTION_DATE
63 ,NULL LE_BR_DAILY_ENTITY
64 ,NULL LE_BR_DAILY_LOCATION
65 ,NULL LE_BR_DIRECTOR_NUMBER
66 ,NULL LE_BR_ACCOUNTANT_NUMBER
67 ,NULL LE_BR_ACCOUNTANT_NAME ';
68
69 C_LEGAL_ENT_COL CONSTANT VARCHAR2(4000) :=
70 ' ,fiv.legal_entity_id LEGAL_ENTITY_ID
71 ,fiv.NAME LEGAL_ENTITY_NAME
72 ,fiv.ADDRESS_LINE_1 LE_ADDRESS_LINE_1
73 ,fiv.ADDRESS_LINE_2 LE_ADDRESS_LINE_2
74 ,fiv.ADDRESS_LINE_3 LE_ADDRESS_LINE_3
75 ,fiv.TOWN_OR_CITY LE_CITY
76 ,fiv.REGION_1 LE_REGION_1
77 ,fiv.REGION_2 LE_REGION_2
78 ,fiv.REGION_3 LE_REGION_3
79 ,fiv.postal_code LE_POSTAL_CODE
80 ,fiv.country LE_COUNTRY
81 ,fiv.registration_number LE_REGISTRATION_NUMBER
82 ,fiv.effective_from LE_REGISTRATION_EFFECTIVE_FROM
83 ,xrv.registration_number LE_BR_DAILY_INSCRIPTION_NUMBER
84 ,to_char(xrv.effective_from
85 ,''YYYY-MM-DD'') LE_BR_DAILY_INSCRIPTION_DATE
86 ,xrv.legalauth_name LE_BR_DAILY_ENTITY
87 ,xlv.city LE_BR_DAILY_LOCATION
88 ,lc1.contact_number LE_BR_DIRECTOR_NUMBER
89 ,lc2.contact_number LE_BR_ACCOUNTANT_NUMBER
90 ,lc2.contact_name LE_BR_ACCOUNTANT_NAME ';
91
92 C_LEGAL_ENT_FROM CONSTANT VARCHAR2(1000) :=
93 ' ,xle_firstparty_information_v fiv
94 ,xle_registrations_v xrv
95 ,xle_legalauth_v xlv
96 ,xle_legal_contacts_v lc1
97 ,xle_legal_contacts_v lc2
98 ,gl_ledger_le_bsv_specific_v gle';
99
100 C_LEGAL_ENT_JOIN CONSTANT VARCHAR2(2000) :=
101 ' AND gle.ledger_id(+) = TABLE1.ledger_id
102 AND gle.segment_value(+) = TABLE1.$leg_seg_val$
103 AND fiv.legal_entity_id(+) = gle.legal_entity_id
104 AND xrv.legal_entity_id(+) = fiv.legal_entity_id
105 AND xrv.legislative_category(+) = ''FEDERAL_TAX''
106 AND xlv.legalauth_id(+) = xrv.legalauth_id
107 AND lc1.entity_id(+) = fiv.legal_entity_id
108 AND lc1.ROLE(+) = ''DIRECTOR''
109 AND lc1.entity_type(+) = ''LEGAL_ENTITY''
110 AND lc2.entity_id(+) = fiv.legal_entity_id
111 AND lc2.ROLE(+) = ''ACCOUNTANT''
112 AND lc2.entity_type(+) = ''LEGAL_ENTITY'' ';
113
114 C_ESTBLISHMENT_COL CONSTANT VARCHAR2(4000) :=
115 ' ,xev.establishment_id LEGAL_ENTITY_ID
116 ,xev.establishment_name LEGAL_ENTITY_NAME
117 ,xev.address_line_1 LE_ADDRESS_LINE_1
118 ,xev.address_line_2 LE_ADDRESS_LINE_2
119 ,xev.address_line_3 LE_ADDRESS_LINE_3
120 ,xev.town_or_city LE_CITY
121 ,xev.region_1 LE_REGION_1
122 ,xev.region_2 LE_REGION_2
123 ,xev.region_3 LE_REGION_3
124 ,xev.postal_code LE_POSTAL_CODE
125 ,xev.country LE_COUNTRY
126 ,xev.registration_number LE_REGISTRATION_NUMBER
127 ,xev.effective_from LE_REGISTRATION_EFFECTIVE_FROM
128 ,xrv.registration_number LE_BR_DAILY_INSCRIPTION_NUMBER
129 ,to_char(xrv.effective_from
130 ,''YYYY-MM-DD'') LE_BR_DAILY_INSCRIPTION_DATE
131 ,xrv.legalauth_name LE_BR_DAILY_ENTITY
132 ,xlv.city LE_BR_DAILY_LOCATION
133 ,lc1.contact_number LE_BR_DIRECTOR_NUMBER
134 ,lc2.contact_number LE_BR_ACCOUNTANT_NUMBER
135 ,lc2.contact_name LE_BR_ACCOUNTANT_NAME ';
136
137 C_ESTABLISHMENT_FROM CONSTANT VARCHAR2(2000) :=
138 ' ,gl_ledger_le_bsv_specific_v glv
139 ,xle_bsv_associations xba
140 ,xle_establishment_v xev
141 ,xle_registrations_v xrv
142 ,xle_legalauth_v xlv
143 ,xle_legal_contacts_v lc1
144 ,xle_legal_contacts_v lc2';
145
146 C_ESTABLISHMENT_JOIN CONSTANT VARCHAR2(2000) :=
147 ' AND glv.ledger_id(+) = TABLE1.ledger_id
148 AND glv.segment_value(+) = TABLE1.$leg_seg_val$
149 AND xba.legal_parent_id(+) = glv.legal_entity_id
150 AND xba.entity_name(+) = glv.segment_value
151 AND xba.context(+) = ''EST_BSV_MAPPING''
152 AND xev.establishment_id(+) = xba.legal_construct_id
153 AND xrv.establishment_id(+) = xev.establishment_id
154 AND xrv.legislative_category(+) = ''FEDERAL_TAX''
155 AND xlv.legalauth_id(+) = xrv.legalauth_id
156 AND lc1.entity_id(+) = xev.establishment_id
157 AND lc1.entity_type(+) = ''ESTABLISHMENT''
158 AND lc1.ROLE(+) = ''DIRECTOR''
159 AND lc2.entity_id(+) = xev.establishment_id
160 AND lc2.ROLE(+) = ''ACCOUNTANT''
161 AND lc2.entity_type(+) = ''ESTABLISHMENT'' ';
162
163 --------------------------------------------------------------------------------
164 -- constant for COMMERCIAL_NUMBER details
165 --------------------------------------------------------------------------------
166 C_COMMERCIAL_QUERY VARCHAR2(8000) :=
167 'SELECT nvl(xler.registration_number,0) LEGAL_COMMERCIAL_NUMBER
168 FROM XLE_REGISTRATIONS_V xler
169 WHERE legislative_category = ''COMMERCIAL_LAW''
170 AND legal_entity_id = :P_LEGAL_ENTITY_ID';
171
172 C_COMMERCIAL_NULL_QUERY VARCHAR2(8000) :=
173 'select NULL LEGAL_COMMERCIAL_NUMBER from dual where 1>2';
174
175 --------------------------------------------------------------------------------
176 -- constant for VAT_REGISTRATION details
177 --------------------------------------------------------------------------------
178 C_VAT_REGISTRATION_QUERY VARCHAR2(8000) :=
179 'SELECT zptp.REP_REGISTRATION_NUMBER LEGAL_VAT_REGISTRATION_NUMBER
180 FROM ZX_PARTY_TAX_PROFILE zptp ,XLE_ETB_PROFILES xetbp
181 WHERE zptp.PARTY_TYPE_CODE = ''LEGAL_ESTABLISHMENT''
182 AND xetbp.party_id=zptp.party_id
183 AND xetbp.MAIN_ESTABLISHMENT_FLAG = ''Y''
184 AND xetbp.LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID' ;
185
186 C_VAT_REGISTRATION_NULL_QUERY VARCHAR2(8000) :=
187 'select NULL LEGAL_VAT_REGISTRATION_NUMBER from dual where 1>2';
188
189
190 C_QUALIFIED_SEGMENT CONSTANT VARCHAR2(1000) :=
191 ' ,$alias_balancing_segment$ BALANCING_SEGMENT
192 ,$alias_account_segment$ NATURAL_ACCOUNT_SEGMENT
193 ,$alias_costcenter_segment$ COST_CENTER_SEGMENT
194 ,$alias_management_segment$ MANAGEMENT_SEGMENT
195 ,$alias_intercompany_segment$ INTERCOMPANY_SEGMENT
196 $seg_desc_column$ ';
197
198 C_HINT CONSTANT VARCHAR2(240) :=
199 ' /*+ leading(gcck $fnd_flex_hint$, gl1, glb) use_nl(glb) */ ';
200
201
202 --=============================================================================
203 -- ************** forward declaration *******************
204 --=============================================================================
205 --------------------------------------------------------------------------------
206 -- procedure to create the main SQL
207 --------------------------------------------------------------------------------
208 --=============================================================================
209 -- *********** Local Trace Routine **********
210 --=============================================================================
211 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
212 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
213 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
214 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
215 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
216 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
217
218 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
219 C_DEFAULT_MODULE CONSTANT VARCHAR2(240):= 'xla.plsql.xla_acct_analysis_rpt_pkg';
220
221 g_log_level NUMBER;
222 g_log_enabled BOOLEAN;
223
224 PROCEDURE trace
225 (p_msg IN VARCHAR2
226 ,p_level IN NUMBER
227 ,p_module IN VARCHAR2) IS
228 BEGIN
229 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
230 fnd_log.message(p_level, NVL(p_module,C_DEFAULT_MODULE));
231 ELSIF p_level >= g_log_level THEN
232 fnd_log.string(p_level, NVL(p_module,C_DEFAULT_MODULE), p_msg);
233 END IF;
234
235 EXCEPTION
236 WHEN xla_exceptions_pkg.application_exception THEN
237 RAISE;
238 WHEN OTHERS THEN
239 xla_exceptions_pkg.raise_message
240 (p_location => 'xla_acct_analysis_rpt_pkg.trace');
241 END trace;
242
243 /*======================================================================+
244 | |
245 | Private Function |
246 | |
247 | get_flex_range_where |
248 | |
249 | |
250 | Return where clauses for flexfield ranges |
251 | |
252 +======================================================================*/
253
254 FUNCTION get_flex_range_where
255 (p_coa_id IN NUMBER
256 ,p_accounting_flexfield_from IN VARCHAR2
257 ,p_accounting_flexfield_to IN VARCHAR2) RETURN VARCHAR
258
259 IS
260
261 l_log_module VARCHAR2(240);
262 l_where VARCHAR2(32000);
263 l_bind_variables fnd_flex_xml_publisher_apis.bind_variables;
264 l_numof_bind_variables NUMBER;
265 l_segment_name VARCHAR2(30);
266 l_segment_value VARCHAR2(1000);
267 l_data_type VARCHAR2(30);
268
269 BEGIN
270
271 IF g_log_enabled THEN
272 l_log_module := C_DEFAULT_MODULE||'.get_flex_range_where';
273 END IF;
274 --
275 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
276
277 trace
278 (p_msg => 'BEGIN of get_flex_range_where'
279 ,p_level => C_LEVEL_PROCEDURE
280 ,p_module => l_log_module);
281
282 END IF;
283
284 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
285
286 trace
287 (p_msg => 'p_coa_id = '||to_char(p_coa_id)
288 ,p_level => C_LEVEL_STATEMENT
289 ,p_module=> l_log_module );
290
291 trace
292 (p_msg => 'p_accounting_flexfield_from = '||to_char(p_accounting_flexfield_from )
293 ,p_level => C_LEVEL_STATEMENT
294 ,p_module=> l_log_module );
295
296 trace
297 (p_msg => 'p_accounting_flexfield_to = '||to_char(p_accounting_flexfield_to)
298 ,p_level => C_LEVEL_STATEMENT
299 ,p_module=> l_log_module );
300
301 END IF;
302
303 --
304 -- e.g. l_where stores the following:
305 -- gcck.SEGMENT1 BETWEEN :FLEX_PARM1 AND :FLEX_PARM2
306 -- AND gcck.SEGMENT2 BETWEEN :FLEX_PARM3 AND :FLEX_PARM4 ...
307 --
308 fnd_flex_xml_publisher_apis.kff_where
309 (p_lexical_name => 'FLEX_PARM'
310 ,p_application_short_name => 'SQLGL'
311 ,p_id_flex_code => 'GL#'
312 ,p_id_flex_num => p_coa_id
313 ,p_code_combination_table_alias => 'gcck'
314 ,p_segments => 'ALL'
315 ,p_operator => 'BETWEEN'
316 ,p_operand1 => p_accounting_flexfield_from
320 ,x_bind_variables => l_bind_variables);
317 ,p_operand2 => p_accounting_flexfield_to
318 ,x_where_expression => l_where
319 ,x_numof_bind_variables => l_numof_bind_variables
321
322 FOR i IN l_bind_variables.FIRST .. l_bind_variables.LAST LOOP
323
324 l_segment_name := l_bind_variables(i).name;
325 l_data_type := l_bind_variables(i).data_type;
326
327 IF (l_data_type='VARCHAR2') THEN
328
329 l_segment_value := '''' || l_bind_variables(i).varchar2_value || '''';
330
331 ELSIF (l_data_type='NUMBER') THEN
332
333 l_segment_value := l_bind_variables(i).canonical_value;
334
335 ELSIF (l_data_type='DATE') THEN
336
337 l_segment_value := '''' || TO_CHAR(l_bind_variables(i).date_value
338 ,'yyyy-mm-dd HH24:MI:SS') || '''';
339
340 END IF;
341
342 --
343 -- Use REGEXP_REPLACE instead of REPLACE not to replace
344 -- string 'SEGMENT1' in 'SEGMENT10'.
345 -- REGEXP_REPLACE replaces the first occurent of a segment name
346 -- e.g.
347 -- BETWEEN :FLEX_PARM9 AND :FLEX_PARM10
348 -- =>
349 -- BETWEEN '000' AND '100'
350 --
351 l_where := REGEXP_REPLACE
352 (l_where
353 ,':' || l_segment_name
354 ,l_segment_value
355 ,1 -- Position
356 ,1 -- The first occurence
357 ,'c' -- Case sensitive
358 );
359
360 END LOOP ;
361
362 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
363
364 trace
365 (p_msg => 'END of get_flex_range_where'
366 ,p_level => C_LEVEL_PROCEDURE
367 ,p_module => l_log_module);
368
369 END IF;
370
371 RETURN l_where;
372
373 EXCEPTION
374 WHEN xla_exceptions_pkg.application_exception THEN
375 RAISE;
376 WHEN OTHERS THEN
377 xla_exceptions_pkg.raise_message
378 (p_location => 'xla_tb_report_pvt.get_flex_range_where');
379
380 END get_flex_range_where;
381 --=============================================================================
382 -- *********** public procedures and functions **********
383 --=============================================================================
384 --=============================================================================
385
386 --
387 --
388 --
389 --
390 --
391 --
392 --
393 -- Following are public routines
394 --
395 -- 1. beforeReport
396 --
397 --
398 --
399 --
400 --
401 --
402 --
403 --
404
405 --=============================================================================
406 --=============================================================================
407 --
408 --
409 --
410 --=============================================================================
411 FUNCTION beforeReport RETURN BOOLEAN IS
412
413 l_ledger_id NUMBER;
414 l_start_period_num NUMBER;
415 l_end_period_num NUMBER;
416 l_start_date DATE;
417 l_end_date DATE;
418 l_lang VARCHAR2(80);
419 l_count NUMBER;
420 l_coa_id NUMBER;
421 l_object_type VARCHAR2(30);
422 l_balancing_segment P_BALANCING_SEGMENT_FROM%TYPE;
423 l_account_segment P_ACCOUNT_SEGMENT_FROM%TYPE;
424 l_costcenter_segment VARCHAR2(80);
425 l_management_segment VARCHAR2(80);
426 l_intercompany_segment VARCHAR2(80);
427 l_alias_balancing_segment P_BALANCING_SEGMENT_FROM%TYPE;
428 l_alias_account_segment P_ACCOUNT_SEGMENT_FROM%TYPE;
429 l_alias_costcenter_segment l_costcenter_segment%TYPE;
430 l_alias_management_segment l_management_segment%TYPE;
431 l_alias_intercompany_segment l_intercompany_segment%TYPE;
432 l_seg_desc_column VARCHAR2(2000);
433 l_seg_desc_from p_seg_desc_from%TYPE;
434 l_seg_desc_join p_seg_desc_join%TYPE;
435 l_other_param_filter VARCHAR2(2000);
436 l_log_module VARCHAR2(240);
437 l_balance_query VARCHAR2(32000);
438 l_flex_range_where VARCHAR2(32000);
439 l_sla_other_filter p_sla_other_filter%TYPE:= ' ';
440 l_gl_other_filter p_gl_other_filter%TYPE := ' ';
441 l_ledger_set_from VARCHAR2(1000) := ' ';
442 l_ledger_set_where VARCHAR2(1000) := ' ';
443 i NUMBER;
444 l_conc_seg_delimiter VARCHAR2(80);
445 l_concat_segment VARCHAR2(4000);
446 l_array t_array_char;
447
448 l_ledgers VARCHAR2(1000);
449 l_fnd_flex_hint VARCHAR2(240);
450 l_hint VARCHAR2(240);
451 l_statistical VARCHAR2(50);
452
453 CURSOR c(p_coa_id number) IS
454 SELECT 'gcck.'||application_column_name seg
455 FROM fnd_id_flex_segments
456 WHERE application_id =101
457 AND id_flex_code ='GL#'
461
458 AND id_flex_num = p_coa_id
459 ORDER BY segment_num ;
460
462 BEGIN
463
464 --
465 -- default values
466 --
467 P_INCLUDE_ZERO_AMOUNT_LINES := NVL(P_INCLUDE_ZERO_AMOUNT_LINES,'N');
468 P_INCLUDE_USER_TRX_ID_FLAG := NVL(P_INCLUDE_USER_TRX_ID_FLAG,'N');
469 P_INCLUDE_TAX_DETAILS_FLAG := NVL(P_INCLUDE_TAX_DETAILS_FLAG,'N');
470 P_INCLUDE_LE_INFO_FLAG := NVL(P_INCLUDE_LE_INFO_FLAG,'NONE');
471 P_INCLUDE_STAT_AMOUNT_LINES := NVL(P_INCLUDE_STAT_AMOUNT_LINES,'N');
472
473 P_INCLUDE_ACCT_WITH_NO_ACT := NVL(P_INCLUDE_ACCT_WITH_NO_ACT,'N'); --bug#7386068
474
475 --
476 -- following will set the right transaction security
477 -- The transaction security in this case is "no security"
478 -- becuase the report is submitted from a GL responsibility
479 --
480 xla_security_pkg.set_security_context(602);
481
482 --
483 -- Transaction identifiers
484 -- As account analysis report goes accross application and SLA
485 -- does not support user trx ids in such a case, the following
486 -- code is not needed.
487 --
488 --uncommented for bug7514332
489 IF p_include_user_trx_id_flag = 'Y' THEN
490 p_trx_identifiers :=
491 xla_report_utility_pkg.get_transaction_id
492 (p_resp_application_id
493 ,p_ledger_id);
494 END IF;
495 --uncommented for bug7514332
496 --
497 -- Identifying ledger as Ledger or Ledger Set and get value for language
498 --
499 SELECT object_type_code, USERENV('LANG')
500 INTO l_object_type, l_lang
501 FROM gl_ledgers
502 WHERE ledger_id = p_ledger_id;
503
504
505 --
506 -- build join condition based on if ledger passed is a ledger set or a ledger
507 --
508 IF l_object_type = 'S' THEN
509 l_ledgers := '(SELECT ledger_id '||
510 'FROM gl_ledger_set_assignments '||
511 'WHERE ledger_set_id = :P_LEDGER_ID)';
512
513 SELECT ledger_id
514 INTO l_ledger_id
515 FROM gl_ledger_set_assignments
516 WHERE ledger_set_id = p_ledger_id
517 AND ROWNUM = 1;
518
519 ELSE
520 l_ledgers := '(:P_LEDGER_ID)';
521
522 l_ledger_id := p_ledger_id;
523
524 END IF;
525
526 --
527 -- get effective period number for the from and to period
528 --
529 SELECT effective_period_num
530 ,START_DATE
531 INTO l_start_period_num
532 ,l_start_date
533 FROM gl_period_statuses
534 WHERE application_id = 101
535 AND ledger_id = l_ledger_id
536 AND period_name = p_period_from;
537
538 SELECT effective_period_num
539 ,end_date
540 INTO l_end_period_num
541 ,l_end_date
542 FROM gl_period_statuses
543 WHERE application_id = 101
544 AND ledger_id = l_ledger_id
545 AND period_name = p_period_to;
546
547
548
549 p_commercial_query := C_COMMERCIAL_QUERY;
550 p_vat_registration_query := C_VAT_REGISTRATION_QUERY;
551
552 --
553 -- Qualified segments
554 --
555 p_qualifier_segment := C_QUALIFIED_SEGMENT;
556
557
558 --
559 -- get COA for the ledger/ledger set
560 --
561
562 SELECT chart_of_accounts_id
563 INTO l_coa_id
564 FROM gl_ledgers
565 WHERE ledger_id = p_ledger_id;
566
567 -- Get concatenated segment
568
569 l_concat_segment := xla_report_utility_pkg.get_conc_segments(p_coa_id,'gcck');
570
571 ----------------------------------------------------------------------------
572 -- get qualifier segments for the COA
573 ----------------------------------------------------------------------------
574 xla_report_utility_pkg.get_acct_qualifier_segs
575 (p_coa_id => l_coa_id
576 ,p_balance_segment => l_balancing_segment
577 ,p_account_segment => l_account_segment
578 ,p_cost_center_segment => l_costcenter_segment
579 ,p_management_segment => l_management_segment
580 ,p_intercompany_segment => l_intercompany_segment);
581
582 --
583 -- attach table alias to the column names
584 --
585 IF l_balancing_segment = 'NULL' THEN
586 l_alias_balancing_segment := 'NULL';
587 ELSE
588 l_alias_balancing_segment := 'gcck.'||l_balancing_segment;
589 END IF;
590
591 IF l_account_segment = 'NULL' THEN
592 l_alias_account_segment := 'NULL';
593 ELSE
594 l_alias_account_segment := 'gcck.'||l_account_segment;
595 END IF;
596
597 IF l_costcenter_segment = 'NULL' THEN
598 l_alias_costcenter_segment := 'NULL';
599 ELSE
600 l_alias_costcenter_segment := 'gcck.'||l_costcenter_segment;
601 END IF;
602
603 IF l_management_segment = 'NULL' THEN
604 l_alias_management_segment := 'NULL';
605 ELSE
606 l_alias_management_segment := 'gcck.'||l_management_segment;
607 END IF;
608
609 IF l_intercompany_segment = 'NULL' THEN
610 l_alias_intercompany_segment := 'NULL';
611 ELSE
612 l_alias_intercompany_segment := 'gcck.'||l_intercompany_segment;
613 END IF;
614
615 --
619 ,'$alias_balancing_segment$'
616 -- replace placeholders for the qualified segemnts
617 --
618 p_qualifier_segment:= REPLACE(p_qualifier_segment
620 ,l_alias_balancing_segment);
621
622 p_qualifier_segment := REPLACE(p_qualifier_segment
623 ,'$alias_account_segment$'
624 ,l_alias_account_segment);
625
626 p_qualifier_segment := REPLACE(p_qualifier_segment
627 ,'$alias_costcenter_segment$'
628 ,l_alias_costcenter_segment);
629
630 p_qualifier_segment := REPLACE(p_qualifier_segment
631 ,'$alias_management_segment$'
632 ,l_alias_management_segment);
633
634 p_qualifier_segment := REPLACE(p_qualifier_segment
635 ,'$alias_intercompany_segment$'
636 ,l_alias_intercompany_segment);
637
638 ----------------------------------------------------------------------------
639 -- building code to get segment description
640 ----------------------------------------------------------------------------
641 IF l_balancing_segment <> 'NULL' THEN
642 l_seg_desc_column:= l_seg_desc_column||',fvbs.description BALANCING_SEGMENT_DESC ';
643 l_seg_desc_from := l_seg_desc_from||',fnd_flex_values_tl fvbs '||
644 ',fnd_flex_values fvbs_b ';
645 l_seg_desc_join := l_seg_desc_join||C_SEG_DESC_JOIN;
646 l_seg_desc_join := REPLACE(l_seg_desc_join,'$alias$','fvbs');
647 l_seg_desc_join :=
648 REPLACE
649 (l_seg_desc_join
650 ,'$flex_value_set_id$'
651 ,xla_flex_pkg.get_segment_valueset
652 (p_application_id => 101
653 ,p_id_flex_code => 'GL#'
654 ,p_id_flex_num => l_coa_id
655 ,p_segment_code => l_balancing_segment
656 )
657 );
658 l_seg_desc_join := REPLACE(l_seg_desc_join
659 ,'$segment_column$'
660 ,l_alias_balancing_segment);
661
662 l_fnd_flex_hint := l_fnd_flex_hint ||',fvbs_b,fvbs' ;
663 ELSE
664 l_seg_desc_column := l_seg_desc_column ||', NULL BALANCING_SEGMENT_DESC';
665
666 END IF;
667
668 IF l_account_segment <> 'NULL' THEN
669 l_seg_desc_column := l_seg_desc_column||',fvna.description NATURAL_ACCOUNT_DESC ';
670 l_seg_desc_from := l_seg_desc_from||',fnd_flex_values_tl fvna '||
671 ',fnd_flex_values fvna_b ';
672 l_seg_desc_join := l_seg_desc_join||C_SEG_DESC_JOIN;
673 l_seg_desc_join := REPLACE(l_seg_desc_join,'$alias$','fvna');
674 l_seg_desc_join :=
675 REPLACE
676 (l_seg_desc_join
677 ,'$flex_value_set_id$'
678 ,xla_flex_pkg.get_segment_valueset
679 (p_application_id => 101
680 ,p_id_flex_code => 'GL#'
681 ,p_id_flex_num => l_coa_id
682 ,p_segment_code => l_account_segment
683 )
684 );
685 l_seg_desc_join := REPLACE(l_seg_desc_join
686 ,'$segment_column$'
687 ,l_alias_account_segment);
688
689 l_fnd_flex_hint := l_fnd_flex_hint ||',fvna_b,fvna' ;
690 ELSE
691 l_seg_desc_column := l_seg_desc_column ||', NULL NATURAL_ACCOUNT_DESC';
692 END IF;
693
694 IF l_costcenter_segment <> 'NULL' THEN
695 l_seg_desc_column := l_seg_desc_column||',fvcc.description COST_CENTER_DESC ';
696 l_seg_desc_from := l_seg_desc_from||',fnd_flex_values_tl fvcc '||
697 ',fnd_flex_values fvcc_b ';
698 l_seg_desc_join := l_seg_desc_join||C_SEG_DESC_JOIN;
699 l_seg_desc_join := REPLACE(l_seg_desc_join,'$alias$','fvcc');
700 l_seg_desc_join :=
701 REPLACE
702 (l_seg_desc_join
703 ,'$flex_value_set_id$'
704 ,xla_flex_pkg.get_segment_valueset
705 (p_application_id => 101
706 ,p_id_flex_code => 'GL#'
707 ,p_id_flex_num => l_coa_id
708 ,p_segment_code => l_costcenter_segment
709 )
710 );
711 l_seg_desc_join := REPLACE(l_seg_desc_join
712 ,'$segment_column$'
713 ,l_alias_costcenter_segment);
714
715 l_fnd_flex_hint := l_fnd_flex_hint ||',fvcc_b,fvcc' ;
716 ELSE
717 l_seg_desc_column := l_seg_desc_column ||', NULL COST_CENTER_DESC';
718
719 END IF;
720
721 IF l_management_segment <> 'NULL' THEN
722 l_seg_desc_column := l_seg_desc_column||',fvmg.description MANAGEMENT_SEGMENT_DESC ';
723 l_seg_desc_from := l_seg_desc_from||',fnd_flex_values_tl fvmg '||
724 ',fnd_flex_values fvmg_b ';
725 l_seg_desc_join := l_seg_desc_join||C_SEG_DESC_JOIN;
726 l_seg_desc_join := REPLACE(l_seg_desc_join,'$alias$','fvmg');
727 l_seg_desc_join :=
728 REPLACE
729 (l_seg_desc_join
733 ,p_id_flex_code => 'GL#'
730 ,'$flex_value_set_id$'
731 ,xla_flex_pkg.get_segment_valueset
732 (p_application_id => 101
734 ,p_id_flex_num => l_coa_id
735 ,p_segment_code => l_management_segment
736 )
737 );
738 l_seg_desc_join := REPLACE(l_seg_desc_join
739 ,'$segment_column$'
740 ,l_alias_management_segment);
741
742 l_fnd_flex_hint := l_fnd_flex_hint ||',fvmg_b,fvmg' ;
743 ELSE
744 l_seg_desc_column := l_seg_desc_column ||', NULL MANAGEMENT_SEGMENT_DESC';
745
746 END IF;
747
748 IF l_intercompany_segment <> 'NULL' THEN
749 l_seg_desc_column := l_seg_desc_column||',fvic.description INTERCOMPANY_SEGMENT_DESC ';
750 l_seg_desc_from := l_seg_desc_from||',fnd_flex_values_tl fvic '||
751 ',fnd_flex_values fvic_b ';
752 l_seg_desc_join := l_seg_desc_join||C_SEG_DESC_JOIN;
753 l_seg_desc_join := REPLACE(l_seg_desc_join,'$alias$','fvic');
754 l_seg_desc_join :=
755 REPLACE
756 (l_seg_desc_join
757 ,'$flex_value_set_id$'
758 ,xla_flex_pkg.get_segment_valueset
759 (p_application_id => 101
760 ,p_id_flex_code => 'GL#'
761 ,p_id_flex_num => l_coa_id
762 ,p_segment_code => l_intercompany_segment
763 )
764 );
765 l_seg_desc_join := REPLACE(l_seg_desc_join
766 ,'$segment_column$'
767 ,l_alias_intercompany_segment);
768
769 l_fnd_flex_hint := l_fnd_flex_hint ||',fvic_b,fvic' ;
770 ELSE
771 l_seg_desc_column := l_seg_desc_column ||', NULL INTERCOMPANY_SEGMENT_DESC';
772 END IF;
773
774 l_hint := REPLACE(C_HINT,'$fnd_flex_hint$',l_fnd_flex_hint);
775
776 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
777 trace
778 (p_msg => 'seg_desc_column ='||l_seg_desc_column
779 ,p_level => C_LEVEL_STATEMENT
780 ,p_module=> l_log_module);
781 trace
782 (p_msg => 'seg_desc_from ='||l_seg_desc_from
783 ,p_level => C_LEVEL_STATEMENT
784 ,p_module=> l_log_module);
785 trace
786 (p_msg => 'seg_desc_join ='||l_seg_desc_join
787 ,p_level => C_LEVEL_STATEMENT
788 ,p_module=> l_log_module);
789 trace
790 (p_msg => 'l_hint ='||l_hint
791 ,p_level => C_LEVEL_STATEMENT
792 ,p_module=> l_log_module);
793 END IF;
794 --
795 -- replace placeholders for the qualified segemnts
796 --
797 p_qualifier_segment := REPLACE(p_qualifier_segment
798 ,'$seg_desc_column$'
799 ,l_seg_desc_column);
800
801 p_seg_desc_from := l_seg_desc_from;
802
803 p_seg_desc_join := l_seg_desc_join;
804
805
806
807 --
808 -- Legal Entity Information
809 --
810
811 --
812 -- Replace placeholders for Legal entity information
813 --
814 IF p_include_le_info_flag = 'LEGAL_ENTITY' THEN
815 p_legal_ent_col := C_LEGAL_ENT_COL;
816 p_legal_ent_from := C_LEGAL_ENT_FROM;
817 p_legal_ent_join := C_LEGAL_ENT_JOIN;
818
819 p_legal_ent_join := REPLACE(p_legal_ent_join,'$leg_seg_val$',l_balancing_segment);
820
821 IF p_legal_entity_id IS NOT NULL THEN
822 p_legal_ent_join := p_legal_ent_join ||
823 ' AND gle.legal_entity_id = '||p_legal_entity_id;
824 END IF;
825 ELSIF p_include_le_info_flag = 'ESTABLISHMENT' THEN
826 p_legal_ent_col := C_ESTBLISHMENT_COL;
827 p_legal_ent_from := C_ESTABLISHMENT_FROM;
828 p_legal_ent_join := C_ESTABLISHMENT_JOIN;
829
830 p_legal_ent_join := REPLACE(p_legal_ent_join,'$leg_seg_val$',l_balancing_segment);
831
832 IF p_legal_entity_id IS NOT NULL THEN
833 p_legal_ent_join := p_legal_ent_join ||
834 ' AND glv.legal_entity_id = '||p_legal_entity_id;
835 END IF;
836 ELSE -- p_include_le_info_flag = 'NONE' THEN
837 p_legal_ent_col := C_NULL_LEGAL_ENT_COL;
838 p_legal_ent_from := ' ';
839 p_legal_ent_join := ' ';
840
841 IF p_legal_entity_id IS NOT NULL THEN
842 p_legal_ent_from := ' ,gl_ledger_le_bsv_specific_v gle ';
843 p_legal_ent_join := ' AND gle.ledger_id(+) = TABLE1.LEDGER_ID '||
844 ' AND gle.segment_value(+) = TABLE1.$leg_seg_val$ '||
845 ' AND gle.legal_entity_id(+) = '||p_legal_entity_id;
846
847 p_legal_ent_join := REPLACE(p_legal_ent_join,'$leg_seg_val$',l_balancing_segment);
848 END IF;
849 END IF;
850
851
852 --
853 -- Third party information
854 --
855
856 -- 5072266 modify po_vendors.party_id to use ap_suppliers.vendor_id
857 -- po_vendors pov -> ap_suppliers ap
858 -- pov.segment1 -> ap.segment1
862
859 -- pov.vendor_name -> ap.vendor_name
860 -- pov.party_id -> ap_vendor_id
861 -- pov.party_id -> ap.vendor_id
863 /* Below the inner query is having join to xla_ae_lines ael2
864 because it seems that CASE statment doesn't allow to have
865 outer join from parent query column.So as a workaround we
866 have joined to xla_ae_lines ale2 and then through ale2 we
867 have outer joined to sites table for handling cases where
868 party_site_id can be NULL for a valid party_id
869 */
870
871 p_party_columns :=
872 ',CASE
873 WHEN ael.party_type_code = ''S'' THEN
874 (SELECT aps.segment1
875 ||''|''||aps.vendor_name
876 ||''|''||hzp.jgzz_fiscal_code
877 ||''|''||hzp.tax_reference
878 ||''|''||hps.party_site_number
879 ||''|''||hps.party_site_name
880 ||''|''||NULL
881 FROM ap_suppliers aps
882 ,ap_supplier_sites_all apss
883 ,hz_parties hzp
884 ,hz_party_sites hps
885 ,xla_ae_lines ael2
886 WHERE aps.vendor_id = ael2.party_id
887 AND hzp.party_id = aps.party_id
888 AND apss.vendor_site_id(+) = ael2.party_site_id
889 AND hps.party_site_id(+) = apss.party_site_id
890 AND ael2.application_id = ael.application_id
891 AND ael2.ae_header_id = ael.ae_header_id
892 AND ael2.ae_line_num = ael.ae_line_num )
893 WHEN ( ael.party_type_code = ''C'' AND ael.party_id is not null ) THEN
894 (SELECT hca.account_number
895 ||''|''||hzp.party_name
896 ||''|''||hzp.jgzz_fiscal_code
897 ||''|''||hzp.tax_reference
898 ||''|''||hps.party_site_number
899 ||''|''||hps.party_site_name
900 ||''|''||hzcu.tax_reference
901 FROM hz_cust_accounts hca
902 ,hz_cust_acct_sites_all hcas
903 ,hz_cust_site_uses_all hzcu
904 ,hz_parties hzp
905 ,hz_party_sites hps
906 ,xla_ae_lines ael2
907 WHERE hca.cust_account_id = ael2.party_id
908 AND hzp.party_id = hca.party_id
909 AND hzcu.site_use_id(+) = ael2.party_site_id
910 AND hcas.cust_acct_site_id(+) = hzcu.cust_acct_site_id
911 AND hps.party_site_id(+) = hcas.party_site_id
912 AND ael2.application_id = ael.application_id
913 AND ael2.ae_header_id = ael.ae_header_id
914 AND ael2.ae_line_num = ael.ae_line_num )
915 ELSE
916 NULL
917 END ';
918 --===========================================================================
919 -- Build Filter condition based on parameters
920 --===========================================================================
921 --
922 -- Filter based on Balancing Segment Value
923 --
924 IF p_balancing_segment_from IS NOT NULL AND
925 p_balancing_segment_to IS NOT NULL
926 THEN
927 l_other_param_filter :=
928 l_other_param_filter ||' AND '||l_alias_balancing_segment||' BETWEEN '''
929 ||p_balancing_segment_from ||''' AND '''||p_balancing_segment_to||'''';
930 END IF;
931 --
932 -- Filter based on Natural Account Segment Value
933 --
934 IF p_account_segment_from IS NOT NULL AND
935 p_account_segment_to IS NOT NULL
936 THEN
937 l_other_param_filter :=
938 l_other_param_filter ||' AND '||l_alias_account_segment||' BETWEEN '''
939 ||p_account_segment_from ||''' AND '''||p_account_segment_to||'''';
940 END IF;
941
942 --
943 -- <conditions based on side>
944 --
945 IF UPPER(p_balance_side) = 'CREDIT' THEN
946 IF p_balance_amount_from IS NOT NULL THEN
947 l_other_param_filter :=
948 l_other_param_filter ||
949 ' AND ((NVL(glb.begin_balance_cr,0)+ NVL(glb.period_net_cr,0))
950 - (NVL(glb.begin_balance_dr,0)+ NVL(glb.period_net_dr,0)) ) > '
951 ||p_balance_amount_from ;
952 ELSE
953 l_other_param_filter :=
954 l_other_param_filter ||
955 ' AND ((NVL(glb.begin_balance_cr,0)+ NVL(glb.period_net_cr,0))
956 - (NVL(glb.begin_balance_dr,0)+ NVL(glb.period_net_dr,0))) > 0';
957 END IF;
958
959 IF p_balance_amount_to IS NOT NULL THEN
960 l_other_param_filter :=
961 l_other_param_filter ||
962 ' AND ((NVL(glb.begin_balance_cr,0)+ NVL(glb.period_net_cr,0))
963 - (NVL(glb.begin_balance_dr,0)+ NVL(glb.period_net_dr,0)) ) < '
964 ||p_balance_amount_to;
965 END IF;
966 ELSIF UPPER(p_balance_side) = 'DEBIT' THEN
967 IF p_balance_amount_from IS NOT NULL THEN
968 l_other_param_filter :=
972 ||p_balance_amount_from ;
969 l_other_param_filter ||
970 ' AND ((NVL(glb.begin_balance_cr,0)+ NVL(glb.period_net_cr,0))
971 - (NVL(glb.begin_balance_dr,0)+ NVL(glb.period_net_dr,0)) ) < -'
973 ELSE
974 l_other_param_filter :=
975 l_other_param_filter ||
976 ' AND ((NVL(glb.begin_balance_cr,0)+ NVL(glb.period_net_cr,0))
977 - (NVL(glb.begin_balance_dr,0)+ NVL(glb.period_net_dr,0))) < 0';
978 END IF;
979
980 IF p_balance_amount_to IS NOT NULL THEN
981 l_other_param_filter :=
982 l_other_param_filter ||
983 ' AND ((NVL(glb.begin_balance_cr,0)+ NVL(glb.period_net_cr,0))
984 - (NVL(glb.begin_balance_dr,0)+ NVL(glb.period_net_dr,0)) ) > -'
985 ||p_balance_amount_to;
986 END IF;
987 END IF;
988
989 --
990 -- <conditions based on Balance Type >
991 --
992 IF p_balance_type_code IS NOT NULL THEN
993 l_other_param_filter :=
994 l_other_param_filter ||
995 ' AND glb.actual_flag = '''||p_balance_type_code||'''';
996
997 END IF;
998
999 --
1000 -- <conditions based on Encumbrance Type>
1001 --
1002 IF p_encumbrance_type_id IS NOT NULL THEN
1003 l_other_param_filter :=
1004 l_other_param_filter ||
1005 ' AND glb.encumbrance_type_id = '||p_encumbrance_type_id;
1006 END IF;
1007
1008 --
1009 -- <conditions based on Budget Version>
1010 --
1011 IF p_budget_version_id IS NOT NULL THEN -- 4458381
1012 l_other_param_filter :=
1013 l_other_param_filter ||
1014 ' AND glb.budget_version_id = '||p_budget_version_id;
1015 END IF;
1016
1017 --
1018 -- <conditions for Include zero amount lines>
1019 --
1020 IF p_include_stat_amount_lines = 'Y' THEN
1021 l_statistical := ' IN (''STAT'', gl1.currency_code) ';
1022 ELSE
1023 l_statistical := ' = gl1.currency_code ';
1024 END IF;
1025
1026 IF p_include_zero_amount_lines = 'N' THEN
1027 l_other_param_filter :=
1028 l_other_param_filter ||
1029 ' AND (((NVL(glb.begin_balance_cr,0)-NVL(glb.begin_balance_dr,0)) <>0)
1030 OR (NVL(glb.period_net_cr,0) <>0 )
1031 OR (NVL(glb.period_net_dr,0) <> 0))';
1032
1033 l_sla_other_filter :=
1034 l_sla_other_filter ||
1035 ' AND (NVL(ael.accounted_dr,0) - NVL(ael.accounted_cr,0) <> 0)';
1036
1037 l_gl_other_filter :=
1038 l_gl_other_filter ||
1039 ' AND (NVL(gjl.accounted_dr,0) - NVL(gjl.accounted_cr,0) <> 0)';
1040 END IF;
1041
1042 --bug#7386068
1043 -- The below query should be executed in the XML if Include Accounts
1044 -- With No Activity parameter is set to Yes for thic conc program.
1045 -- This query selects those accounts having a beginning balance and no activity for
1046 -- the specified date range of the report.
1047
1048 IF P_INCLUDE_ACCT_WITH_NO_ACT = 'Y' THEN
1049
1050 p_begin_balance_union_all :=
1051 ' UNION ALL
1052 SELECT NULL GL_DATE
1053 ,NULL CREATED_BY
1054 ,NULL CREATION_DATE
1055 ,NULL LAST_UPDATE_DATE
1056 ,NULL GL_TRANSFER_DATE
1057 ,NULL REFERENCE_DATE
1058 ,NULL COMPLETED_DATE
1059 ,NULL TRANSACTION_NUMBER
1060 ,NULL TRANSACTION_DATE
1061 ,NULL ACCOUNTING_SEQUENCE_NAME
1062 ,NULL ACCOUNTING_SEQUENCE_VERSION
1063 ,NULL ACCOUNTING_SEQUENCE_NUMBER
1064 ,NULL REPORTING_SEQUENCE_NAME
1065 ,NULL REPORTING_SEQUENCE_VERSION
1066 ,NULL REPORTING_SEQUENCE_NUMBER
1067 ,NULL DOCUMENT_CATEGORY
1068 ,NULL DOCUMENT_SEQUENCE_NAME
1069 ,NULL DOCUMENT_SEQUENCE_NUMBER
1070 ,NULL APPLICATION_ID
1071 ,NULL APPLICATION_NAME
1072 ,NULL HEADER_ID
1073 ,NULL HEADER_DESCRIPTION
1074 ,NULL FUND_STATUS
1075 ,NULL JE_CATEGORY_NAME
1076 ,NULL JE_SOURCE_NAME
1077 ,NULL EVENT_ID
1078 ,NULL EVENT_DATE
1079 ,NULL EVENT_NUMBER
1080 ,NULL EVENT_CLASS_CODE
1081 ,NULL EVENT_CLASS_NAME
1082 ,NULL EVENT_TYPE_CODE
1083 ,NULL EVENT_TYPE_NAME
1084 ,NULL GL_BATCH_NAME
1085 ,NULL POSTED_DATE
1089 ,NULL LINE_NUMBER
1086 ,NULL GL_JE_NAME
1087 ,NULL EXTERNAL_REFERENCE
1088 ,NULL GL_LINE_NUMBER
1090 ,NULL ACCOUNTING_CLASS_CODE
1091 ,NULL ACCOUNTING_CLASS_NAME
1092 ,NULL LINE_DESCRIPTION
1093 ,NULL ENTERED_CURRENCY
1094 ,NULL CONVERSION_RATE
1095 ,NULL CONVERSION_RATE_DATE
1096 ,NULL CONVERSION_RATE_TYPE_CODE
1097 ,NULL CONVERSION_RATE_TYPE
1098 ,NULL ENTERED_DR
1099 ,NULL ENTERED_CR
1100 ,NULL UNROUNDED_ACCOUNTED_DR
1101 ,NULL UNROUNDED_ACCOUNTED_CR
1102 ,NULL ACCOUNTED_DR
1103 ,NULL ACCOUNTED_CR
1104 ,NULL STATISTICAL_AMOUNT
1105 ,NULL RECONCILIATION_REFERENCE
1106 ,NULL ATTRIBUTE_CATEGORY
1107 ,NULL ATTRIBUTE1
1108 ,NULL ATTRIBUTE2
1109 ,NULL ATTRIBUTE3
1110 ,NULL ATTRIBUTE4
1111 ,NULL ATTRIBUTE5
1112 ,NULL ATTRIBUTE6
1113 ,NULL ATTRIBUTE7
1114 ,NULL ATTRIBUTE8
1115 ,NULL ATTRIBUTE9
1116 ,NULL ATTRIBUTE10
1117 ,NULL PARTY_TYPE_CODE
1118 ,NULL PARTY_TYPE
1119 ,NULL PARTY_INFO
1120 ,NULL USERIDS
1121 ,glbgt.ledger_id LEDGER_ID
1122 ,glbgt.ledger_short_name LEDGER_SHORT_NAME
1123 ,glbgt.ledger_description LEDGER_DESCRIPTION
1124 ,glbgt.ledger_name LEDGER_NAME
1125 ,glbgt.ledger_currency LEDGER_CURRENCY
1126 ,glbgt.period_year PERIOD_YEAR
1127 ,glbgt.period_number PERIOD_NUMBER
1128 ,glbgt.period_name PERIOD_NAME
1129 ,to_char(glbgt.period_start_date
1130 ,''YYYY-MM-DD'') PERIOD_START_DATE
1131 ,to_char(glbgt.period_end_date
1132 ,''YYYY-MM-DD'') PERIOD_END_DATE
1133 ,glbgt.balance_type_code BALANCE_TYPE_CODE
1134 ,glbgt.balance_type BALANCE_TYPE
1135 ,glbgt.budget_name BUDGET_NAME
1136 ,glbgt.encumbrance_type ENCUMBRANCE_TYPE
1137 ,glbgt.begin_balance_dr BEGIN_BALANCE_DR
1138 ,glbgt.begin_balance_cr BEGIN_BALANCE_CR
1139 ,glbgt.period_net_dr PERIOD_NET_DR
1140 ,glbgt.period_net_cr PERIOD_NET_CR
1141 ,glbgt.code_combination_id CODE_COMBINATION_ID
1142 ,glbgt.accounting_code_combination ACCOUNTING_CODE_COMBINATION
1143 ,glbgt.code_combination_description CODE_COMBINATION_DESCRIPTION
1144 ,glbgt.control_account_flag CONTROL_ACCOUNT_FLAG
1145 ,glbgt.control_account CONTROL_ACCOUNT
1146 ,glbgt.balancing_segment BALANCING_SEGMENT
1147 ,glbgt.natural_account_segment NATURAL_ACCOUNT_SEGMENT
1148 ,glbgt.cost_center_segment COST_CENTER_SEGMENT
1149 ,glbgt.management_segment MANAGEMENT_SEGMENT
1150 ,glbgt.intercompany_segment INTERCOMPANY_SEGMENT
1151 ,glbgt.balancing_segment_desc BALANCING_SEGMENT_DESC
1152 ,glbgt.natural_account_desc NATURAL_ACCOUNT_DESC
1153 ,glbgt.cost_center_desc COST_CENTER_DESC
1154 ,glbgt.management_segment_desc MANAGEMENT_SEGMENT_DESC
1155 ,glbgt.intercompany_segment_desc INTERCOMPANY_SEGMENT_DESC
1156 ,glbgt.segment1 SEGMENT1
1157 ,glbgt.segment2 SEGMENT2
1158 ,glbgt.segment3 SEGMENT3
1159 ,glbgt.segment4 SEGMENT4
1160 ,glbgt.segment5 SEGMENT5
1161 ,glbgt.segment6 SEGMENT6
1162 ,glbgt.segment7 SEGMENT7
1163 ,glbgt.segment8 SEGMENT8
1164 ,glbgt.segment9 SEGMENT9
1165 ,glbgt.segment10 SEGMENT10
1166 ,glbgt.segment11 SEGMENT11
1167 ,glbgt.segment12 SEGMENT12
1171 ,glbgt.segment16 SEGMENT16
1168 ,glbgt.segment13 SEGMENT13
1169 ,glbgt.segment14 SEGMENT14
1170 ,glbgt.segment15 SEGMENT15
1172 ,glbgt.segment17 SEGMENT17
1173 ,glbgt.segment18 SEGMENT18
1174 ,glbgt.segment19 SEGMENT19
1175 ,glbgt.segment20 SEGMENT20
1176 ,glbgt.segment21 SEGMENT21
1177 ,glbgt.segment22 SEGMENT22
1178 ,glbgt.segment23 SEGMENT23
1179 ,glbgt.segment24 SEGMENT24
1180 ,glbgt.segment25 SEGMENT25
1181 ,glbgt.segment26 SEGMENT26
1182 ,glbgt.segment27 SEGMENT27
1183 ,glbgt.segment28 SEGMENT28
1184 ,glbgt.segment29 SEGMENT29
1185 ,glbgt.segment30 SEGMENT30
1186 ,glbgt.begin_running_total_cr BEGIN_RUNNING_TOTAL_CR
1187 ,glbgt.begin_running_total_dr BEGIN_RUNNING_TOTAL_DR
1188 ,glbgt.end_running_total_cr END_RUNNING_TOTAL_CR
1189 ,glbgt.end_running_total_dr END_RUNNING_TOTAL_DR
1190 ,glbgt.legal_entity_id LEGAL_ENTITY_ID
1191 ,glbgt.legal_entity_name LEGAL_ENTITY_NAME
1192 ,glbgt.le_address_line_1 LE_ADDRESS_LINE_1
1193 ,glbgt.le_address_line_2 LE_ADDRESS_LINE_2
1194 ,glbgt.le_address_line_3 LE_ADDRESS_LINE_3
1195 ,glbgt.le_city LE_CITY
1196 ,glbgt.le_region_1 LE_REGION_1
1197 ,glbgt.le_region_2 LE_REGION_2
1198 ,glbgt.le_region_3 LE_REGION_3
1199 ,glbgt.le_postal_code LE_POSTAL_CODE
1200 ,glbgt.le_country LE_COUNTRY
1201 ,glbgt.le_registration_number LE_REGISTRATION_NUMBER
1202 ,glbgt.le_registration_effective_from LE_REGISTRATION_EFFECTIVE_FROM
1203 ,glbgt.le_br_daily_inscription_number LE_BR_DAILY_INSCRIPTION_NUMBER
1204 ,to_char(glbgt.le_br_daily_inscription_date
1205 ,''YYYY-MM-DD'') LE_BR_DAILY_INSCRIPTION_DATE
1206 ,glbgt.le_br_daily_entity LE_BR_DAILY_ENTITY
1207 ,glbgt.le_br_daily_location LE_BR_DAILY_LOCATION
1208 ,glbgt.le_br_director_number LE_BR_DIRECTOR_NUMBER
1209 ,glbgt.le_br_accountant_number LE_BR_ACCOUNTANT_NUMBER
1210 ,glbgt.le_br_accountant_name LE_BR_ACCOUNTANT_NAME
1211 FROM xla_report_balances_gt glbgt
1212 WHERE nvl(period_net_dr,0) = 0
1213 AND nvl(period_net_cr,0) = 0
1214 AND (nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0) ) <> 0';
1215
1216 END IF;
1217 --End bug#7386068
1218
1219
1220 --
1221 --<condition for the accounting flex field>
1222 --
1223
1224 IF p_account_flexfield_from IS NOT NULL THEN
1225 l_flex_range_where :=
1226 get_flex_range_where
1227 (p_coa_id => l_coa_id
1228 ,p_accounting_flexfield_from => p_account_flexfield_from
1229 ,p_accounting_flexfield_to => p_account_flexfield_to );
1230 l_other_param_filter := l_other_param_filter||' AND '||l_flex_range_where;
1231 END IF;
1232
1233 -- Bug 5914782
1234 p_ledger_filters:= ' gjh.ledger_id IN '||l_ledgers||
1235 ' AND glbgt.ledger_id IN '||l_ledgers||
1236 ' AND aeh.ledger_id IN '||l_ledgers;
1237 IF p_balance_type_code is NOT NULL THEN
1238 p_ledger_filters:= p_ledger_filters||' AND gjh.actual_flag = '''
1239 ||p_balance_type_code||'''';
1240 END IF;
1241
1242
1243 p_sla_other_filter:= l_sla_other_filter;
1244 p_gl_other_filter:= l_gl_other_filter;
1245
1246
1247 l_balance_query :=
1248 '
1249 INSERT INTO xla_report_balances_gt
1250 (ledger_id
1251 ,ledger_short_name
1252 ,ledger_description
1253 ,ledger_name
1254 ,ledger_currency
1255 ,period_year
1256 ,period_number
1257 ,period_name
1258 ,period_start_date
1259 ,period_end_date
1260 ,balance_type_code
1261 ,balance_type
1262 ,budget_version_id
1263 ,budget_name
1264 ,encumbrance_type_id
1265 ,encumbrance_type
1266 ,begin_balance_dr
1267 ,begin_balance_cr
1268 ,period_net_dr
1269 ,period_net_cr
1270 ,code_combination_id
1271 ,accounting_code_combination
1272 ,code_combination_description
1273 ,control_account_flag
1274 ,control_account
1275 ,balancing_segment
1276 ,natural_account_segment
1277 ,cost_center_segment
1278 ,management_segment
1279 ,intercompany_segment
1280 ,balancing_segment_desc
1281 ,natural_account_desc
1282 ,cost_center_desc
1283 ,management_segment_desc
1284 ,intercompany_segment_desc
1285 ,segment1
1286 ,segment2
1287 ,segment3
1288 ,segment4
1289 ,segment5
1290 ,segment6
1291 ,segment7
1292 ,segment8
1293 ,segment9
1294 ,segment10
1295 ,segment11
1296 ,segment12
1297 ,segment13
1298 ,segment14
1299 ,segment15
1300 ,segment16
1301 ,segment17
1302 ,segment18
1303 ,segment19
1304 ,segment20
1305 ,segment21
1306 ,segment22
1307 ,segment23
1308 ,segment24
1309 ,segment25
1310 ,segment26
1311 ,segment27
1312 ,segment28
1313 ,segment29
1314 ,segment30
1315 ,legal_entity_id
1316 ,legal_entity_name
1317 ,le_address_line_1
1318 ,le_address_line_2
1319 ,le_address_line_3
1320 ,le_city
1321 ,le_region_1
1322 ,le_region_2
1323 ,le_region_3
1324 ,le_postal_code
1325 ,le_country
1326 ,le_registration_number
1327 ,le_registration_effective_from
1328 ,le_br_daily_inscription_number
1329 ,le_br_daily_inscription_date
1330 ,le_br_daily_entity
1331 ,le_br_daily_location
1332 ,le_br_director_number
1333 ,le_br_accountant_number
1334 ,le_br_accountant_name)
1335 (
1336 SELECT TABLE1.LEDGER_ID
1337 ,TABLE1.LEDGER_SHORT_NAME
1338 ,TABLE1.LEDGER_DESCRIPTION
1339 ,TABLE1.LEDGER_NAME
1340 ,TABLE1.LEDGER_CURRENCY
1341 ,TABLE1.PERIOD_YEAR
1342 ,TABLE1.PERIOD_NUMBER
1343 ,TABLE1.PERIOD_NAME
1344 ,TABLE1.PERIOD_START_DATE
1345 ,TABLE1.PERIOD_END_DATE
1346 ,TABLE1.BALANCE_TYPE_CODE
1347 ,TABLE1.BALANCE_TYPE
1348 ,TABLE1.BUDGET_VERSION_ID
1349 ,TABLE1.BUDGET_NAME
1350 ,TABLE1.ENCUMBRANCE_TYPE_ID
1351 ,TABLE1.ENCUMBRANCE_TYPE
1352 ,TABLE1.BEGIN_BALANCE_DR
1353 ,TABLE1.BEGIN_BALANCE_CR
1354 ,TABLE1.PERIOD_NET_DR
1355 ,TABLE1.PERIOD_NET_CR
1356 ,TABLE1.CODE_COMBINATION_ID
1360 ,TABLE1.CONTROL_ACCOUNT
1357 ,TABLE1.ACCOUNTING_CODE_COMBINATION
1358 ,TABLE1.CODE_COMBINATION_DESCRIPTION
1359 ,TABLE1.CONTROL_ACCOUNT_FLAG
1361 ,TABLE1.BALANCING_SEGMENT
1362 ,TABLE1.NATURAL_ACCOUNT_SEGMENT
1363 ,TABLE1.COST_CENTER_SEGMENT
1364 ,TABLE1.MANAGEMENT_SEGMENT
1365 ,TABLE1.INTERCOMPANY_SEGMENT
1366 ,TABLE1.BALANCING_SEGMENT_DESC
1367 ,TABLE1.NATURAL_ACCOUNT_DESC
1368 ,TABLE1.COST_CENTER_DESC
1369 ,TABLE1.MANAGEMENT_SEGMENT_DESC
1370 ,TABLE1.INTERCOMPANY_SEGMENT_DESC
1371 ,TABLE1.SEGMENT1
1372 ,TABLE1.SEGMENT2
1373 ,TABLE1.SEGMENT3
1374 ,TABLE1.SEGMENT4
1375 ,TABLE1.SEGMENT5
1376 ,TABLE1.SEGMENT6
1377 ,TABLE1.SEGMENT7
1378 ,TABLE1.SEGMENT8
1379 ,TABLE1.SEGMENT9
1380 ,TABLE1.SEGMENT10
1381 ,TABLE1.SEGMENT11
1382 ,TABLE1.SEGMENT12
1383 ,TABLE1.SEGMENT13
1384 ,TABLE1.SEGMENT14
1385 ,TABLE1.SEGMENT15
1386 ,TABLE1.SEGMENT16
1387 ,TABLE1.SEGMENT17
1388 ,TABLE1.SEGMENT18
1389 ,TABLE1.SEGMENT19
1390 ,TABLE1.SEGMENT20
1391 ,TABLE1.SEGMENT21
1392 ,TABLE1.SEGMENT22
1393 ,TABLE1.SEGMENT23
1394 ,TABLE1.SEGMENT24
1395 ,TABLE1.SEGMENT25
1396 ,TABLE1.SEGMENT26
1397 ,TABLE1.SEGMENT27
1398 ,TABLE1.SEGMENT28
1399 ,TABLE1.SEGMENT29
1400 ,TABLE1.SEGMENT30
1401 $legal_entity_columns$
1402 FROM
1403 (SELECT $hint$
1404 gl1.ledger_id LEDGER_ID
1405 ,gl1.short_name LEDGER_SHORT_NAME
1406 ,gl1.description LEDGER_DESCRIPTION
1407 ,gl1.NAME LEDGER_NAME
1408 ,glb.currency_code LEDGER_CURRENCY
1409 ,glb.period_year PERIOD_YEAR
1410 ,glb.period_num PERIOD_NUMBER
1411 ,glb.period_name PERIOD_NAME
1412 ,gl1.START_DATE PERIOD_START_DATE
1413 ,gl1.end_date PERIOD_END_DATE
1414 ,glb.actual_flag BALANCE_TYPE_CODE
1415 ,xlk.meaning BALANCE_TYPE
1416 ,glb.budget_version_id BUDGET_VERSION_ID
1417 ,glv.budget_name BUDGET_NAME
1418 ,glb.encumbrance_type_id ENCUMBRANCE_TYPE_ID
1419 ,get.encumbrance_type ENCUMBRANCE_TYPE
1420 ,NVL(glb.begin_balance_dr,0) BEGIN_BALANCE_DR
1421 ,NVL(glb.begin_balance_cr,0) BEGIN_BALANCE_CR
1422 ,NVL(glb.period_net_dr,0) PERIOD_NET_DR
1423 ,NVL(glb.period_net_cr,0) PERIOD_NET_CR
1424 ,glb.code_combination_id CODE_COMBINATION_ID
1425 ,$concat_segments$ ACCOUNTING_CODE_COMBINATION
1426 ,xla_report_utility_pkg.get_ccid_desc
1427 (gl1.chart_of_accounts_id
1428 ,glb.code_combination_id) CODE_COMBINATION_DESCRIPTION
1429 ,gcck.reference3 CONTROL_ACCOUNT_FLAG
1430 ,NULL CONTROL_ACCOUNT
1431 $seg_desc_column$
1432 ,gcck.segment1 SEGMENT1
1433 ,gcck.segment2 SEGMENT2
1434 ,gcck.segment3 SEGMENT3
1435 ,gcck.segment4 SEGMENT4
1436 ,gcck.segment5 SEGMENT5
1437 ,gcck.segment6 SEGMENT6
1438 ,gcck.segment7 SEGMENT7
1439 ,gcck.segment8 SEGMENT8
1440 ,gcck.segment9 SEGMENT9
1441 ,gcck.segment10 SEGMENT10
1442 ,gcck.segment11 SEGMENT11
1443 ,gcck.segment12 SEGMENT12
1444 ,gcck.segment13 SEGMENT13
1445 ,gcck.segment14 SEGMENT14
1446 ,gcck.segment15 SEGMENT15
1447 ,gcck.segment16 SEGMENT16
1448 ,gcck.segment17 SEGMENT17
1449 ,gcck.segment18 SEGMENT18
1450 ,gcck.segment19 SEGMENT19
1451 ,gcck.segment20 SEGMENT20
1452 ,gcck.segment21 SEGMENT21
1453 ,gcck.segment22 SEGMENT22
1454 ,gcck.segment23 SEGMENT23
1455 ,gcck.segment24 SEGMENT24
1456 ,gcck.segment25 SEGMENT25
1457 ,gcck.segment26 SEGMENT26
1458 ,gcck.segment27 SEGMENT27
1459 ,gcck.segment28 SEGMENT28
1460 ,gcck.segment29 SEGMENT29
1461 ,gcck.segment30 SEGMENT30
1462 FROM (SELECT /*+ no_merge */
1463 gll.ledger_id
1464 ,gll.short_name
1465 ,gll.description
1466 ,gll.name
1467 ,gll.currency_code
1468 ,gll.chart_of_accounts_id
1469 ,gls.period_name
1470 ,gls.start_date
1471 ,gls.end_date
1472 FROM gl_ledgers gll
1473 ,gl_period_statuses gls
1474 WHERE gls.ledger_id = gll.ledger_id
1475 AND gls.application_id = 101
1476 AND gls.effective_period_num BETWEEN :P_START_PERIOD_NUM AND :P_END_PERIOD_NUM
1477 AND gll.ledger_id IN $ledger_id$
1478 ) gl1
1479 ,gl_balances glb
1480 ,gl_code_combinations gcck
1481 ,xla_lookups xlk
1485 WHERE glb.ledger_id = gl1.ledger_id
1482 ,gl_budget_versions glv
1483 ,gl_encumbrance_types get
1484 $seg_desc_from$
1486 AND glb.currency_code $statistical$
1487 AND glb.period_name = gl1.period_name
1488 AND glb.template_id IS null
1489 AND gcck.code_combination_id = glb.code_combination_id
1490 AND xlk.lookup_type = ''XLA_BALANCE_TYPE''
1491 AND xlk.lookup_code = glb.actual_flag
1492 AND glv.budget_version_id(+) = glb.budget_version_id
1493 AND get.encumbrance_type_id(+) = glb.encumbrance_type_id
1494 $seg_desc_join$
1495 $other_param_filter$) TABLE1
1496 $legal_entity_from$
1497 WHERE 1 = 1
1498 $legal_entity_join$
1499 )' ;
1500
1501
1502
1503 l_balance_query :=
1504 REPLACE(l_balance_query,'$legal_entity_columns$',p_legal_ent_col);
1505 l_balance_query :=
1506 REPLACE(l_balance_query,'$seg_desc_column$',p_qualifier_segment);
1507 l_balance_query :=
1508 REPLACE(l_balance_query,'$legal_entity_from$',p_legal_ent_from);
1509 l_balance_query :=
1510 REPLACE(l_balance_query,'$seg_desc_from$',p_seg_desc_from);
1511 l_balance_query :=
1512 REPLACE(l_balance_query,'$other_param_filter$',l_other_param_filter);
1513 l_balance_query :=
1514 REPLACE(l_balance_query,'$legal_entity_join$',p_legal_ent_join);
1515 l_balance_query :=
1516 REPLACE(l_balance_query,'$seg_desc_join$',p_seg_desc_join);
1517 l_balance_query :=
1518 REPLACE(l_balance_query,'$concat_segments$',l_concat_segment);
1519 l_balance_query :=
1520 REPLACE(l_balance_query,'$ledger_id$',l_ledgers);
1521 l_balance_query :=
1522 REPLACE(l_balance_query,'$hint$',l_hint);
1523
1524 l_balance_query :=
1525 REPLACE(l_balance_query,'$statistical$',l_statistical);
1526
1527 EXECUTE IMMEDIATE l_balance_query
1528 USING l_start_period_num
1529 ,l_end_period_num
1530 ,p_ledger_id;
1531
1532 IF ((p_gl_date_from > l_start_date) OR (p_gl_date_to < l_end_date)) THEN
1533 UPDATE xla_report_balances_gt xrb
1534 SET (begin_running_total_cr
1535 ,begin_running_Total_dr
1536 ,end_running_total_cr
1537 ,end_running_total_dr) =
1538
1539 (SELECT SUM(CASE
1540 WHEN gjl.effective_date < p_gl_date_from THEN
1541 accounted_cr
1542 ELSE
1543 0
1544 END ) BEGIN_RUNNING_TOTAL_CR
1545
1546 , SUM(CASE
1547 WHEN gjl.effective_date < p_gl_date_from THEN
1548 accounted_dr
1549 ELSE
1550 0
1551 END ) BEGIN_RUNNING_TOTAL_DR
1552
1553 ,SUM(CASE
1554 WHEN gjl.effective_date > p_gl_date_to THEN
1555 accounted_cr
1556 ELSE
1557 0
1558 END ) END_RUNNING_TOTAL_CR
1559 ,SUM(CASE
1560 WHEN gjl.effective_date > p_gl_date_to THEN
1561 accounted_dr
1562 ELSE
1563 0
1564 END ) END_RUNNING_TOTAL_DR
1565
1566 FROM gl_je_headers gjh
1567 ,gl_je_lines gjl
1568 WHERE gjh.je_header_id = gjl.je_header_id
1569 AND gjh.status = 'P'
1570 AND gjh.ledger_id = xrb.ledger_id
1571 AND gjh.period_name = xrb.period_name
1572 AND gjh.actual_flag = xrb.balance_type_code
1576 END IF;
1573 AND gjl.code_combination_id = xrb.code_combination_id
1574 )
1575 WHERE xrb.period_name IN (p_period_from,p_period_to);
1577
1578 RETURN TRUE;
1579
1580 EXCEPTION
1581 WHEN OTHERS THEN
1582 xla_exceptions_pkg.raise_message
1583 (p_location => 'xla_acct_analysis_rpt_pkg.beforeReport ');
1584 END beforeReport;
1585
1586
1587
1588 --=============================================================================
1589 -- *********** Initialization routine **********
1590 --=============================================================================
1591
1592 --=============================================================================
1593 --
1594 --
1595 --
1596 --
1597 --
1598 --
1599 --
1600 --
1601 --
1602 --
1603 -- Following code is executed when the package body is referenced for the first
1604 -- time
1605 --
1606 --
1607 --
1608 --
1609 --
1610 --
1611 --
1612 --
1613 --
1614 --
1615 --
1616 --
1617 --=============================================================================
1618
1619 BEGIN
1620 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1621 g_log_enabled := fnd_log.test
1622 (log_level => g_log_level
1623 ,MODULE => C_DEFAULT_MODULE);
1624
1625 IF NOT g_log_enabled THEN
1626 g_log_level := C_LEVEL_LOG_DISABLED;
1627 END IF;
1628
1629
1630 END XLA_ACCT_ANALYSIS_RPT_PKG;