[Home] [Help]
PACKAGE BODY: APPS.XLA_TP_BALANCE_RPT_PKG
Source
1 PACKAGE BODY XLA_TP_BALANCE_RPT_PKG AS
2 -- $Header: xlarptpb.pkb 120.22 2008/01/04 12:10:23 samejain noship $
3 /*===========================================================================+
4 | Copyright (c) 2003 Oracle Corporation BelmFont, California, USA |
5 | ALL rights reserved. |
6 +============================================================================+
7 | FILENAME |
8 | xlarptpb.pkb |
9 | |
10 | PACKAGE NAME |
11 | xla_tp_balance_rpt_pkg |
12 | |
13 | DESCRIPTION |
14 | PACKAGE BODY. This provides XML extract for Third Party Balance Report |
15 | |
16 | HISTORY |
17 | 07/20/2005 V. Kumar Created |
18 | 12/27/2005 V.Swapna Modified the package to use Data template |
19 | 04/23/2006 A. Wan 5072266 - replace po_vendors with |
20 | ap_suppliers |
21 | 08/24/2006 E. Sayyed 5398825- p_je_source ==> p_je_source_name |
22 | 09/01/2006 V. Swapna Bug 5477846 - Raise an error if party type |
23 | is null. |
24 +===========================================================================*/
25
26 --=============================================================================
27 -- **************** declarations ********************
28 --=============================================================================
29
30 C_SEG_DESC_JOIN CONSTANT VARCHAR2(1000) :=
31 ' AND $alias$.flex_value_set_id = $flex_value_set_id$ AND '||
32 ' $alias$.flex_value = $segment_column$ ';
33
34 -------------------------------------------------------------------------------
35 -- constant for getting leagal entity information
36 -------------------------------------------------------------------------------
37 C_NULL_LEGAL_ENT_COL CONSTANT VARCHAR2(4000) :=
38 ' ,NULL LEGAL_ENTITY_ID
39 ,NULL LEGAL_ENTITY_NAME
40 ,NULL LE_ADDRESS_LINE_1
41 ,NULL LE_ADDRESS_LINE_2
42 ,NULL LE_ADDRESS_LINE_3
43 ,NULL LE_CITY
44 ,NULL LE_REGION_1
45 ,NULL LE_REGION_2
46 ,NULL LE_REGION_3
47 ,NULL LE_POSTAL_CODE
48 ,NULL LE_COUNTRY
49 ,NULL LE_REGISTRATION_NUMBER
50 ,NULL LE_REGISTRATION_EFFECTIVE_FROM
51 ,NULL LE_ACTIVITY_CODE
52 ,NULL LE_SUB_ACTIVITY_CODE
53 ,NULL LE_CONTACT_NAME
54 ,NULL LE_CONTACT_PHONE_NUMBER';
55
56 C_LEGAL_ENT_COL CONSTANT VARCHAR2(4000) :=
57 ' ,fiv.legal_entity_id LEGAL_ENTITY_ID
58 ,fiv.NAME LEGAL_ENTITY_NAME
59 ,fiv.ADDRESS_LINE_1 LE_ADDRESS_LINE_1
60 ,fiv.ADDRESS_LINE_2 LE_ADDRESS_LINE_2
61 ,fiv.ADDRESS_LINE_3 LE_ADDRESS_LINE_3
62 ,fiv.TOWN_OR_CITY LE_CITY
63 ,fiv.REGION_1 LE_REGION_1
64 ,fiv.REGION_2 LE_REGION_2
65 ,fiv.REGION_3 LE_REGION_3
66 ,fiv.postal_code LE_POSTAL_CODE
67 ,fiv.country LE_COUNTRY
68 ,fiv.registration_number LE_REGISTRATION_NUMBER
69 ,fiv.effective_from LE_REGISTRATION_EFFECTIVE_FROM
70 ,fiv.activity_code LE_ACTIVITY_CODE
71 ,fiv.sub_activity_code LE_SUB_ACTIVITY_CODE
72 ,NULL LE_CONTACT_NAME
73 ,NULL LE_CONTACT_PHONE_NUMBER';
74
75 C_LEGAL_ENT_FROM CONSTANT VARCHAR2(1000) :=
76 ' ,xle_firstparty_information_v fiv
77 ,gl_ledger_le_bsv_specific_v gle';
78
79 C_LEGAL_ENT_JOIN CONSTANT VARCHAR2(2000) :=
80 ' AND gle.ledger_id(+) = TABLE1.ledger_id
81 AND gle.segment_value(+) = TABLE1.$leg_seg_val$
82 AND fiv.legal_entity_id(+) = gle.legal_entity_id';
83
84 C_ESTBLISHMENT_COL CONSTANT VARCHAR2(4000) :=
85 ' ,xev.establishment_id LEGAL_ENTITY_ID
86 ,xev.establishment_name LEGAL_ENTITY_NAME
87 ,xev.address_line_1 LE_ADDRESS_LINE_1
88 ,xev.address_line_2 LE_ADDRESS_LINE_2
89 ,xev.address_line_3 LE_ADDRESS_LINE_3
90 ,xev.town_or_city LE_CITY
91 ,xev.region_1 LE_REGION_1
92 ,xev.region_2 LE_REGION_2
93 ,xev.region_3 LE_REGION_3
94 ,xev.postal_code LE_POSTAL_CODE
95 ,xev.country LE_COUNTRY
96 ,xev.registration_number LE_REGISTRATION_NUMBER
97 ,xev.effective_from LE_REGISTRATION_EFFECTIVE_FROM
98 ,xev.activity_code LE_ACTIVITY_CODE
99 ,xev.sub_activity_code LE_SUB_ACTIVITY_CODE
100 ,NULL LE_CONTACT_NAME
101 ,NULL LE_CONTACT_PHONE_NUMBER';
102
103 C_ESTABLISHMENT_FROM CONSTANT VARCHAR2(2000) :=
104 ' ,gl_ledger_le_bsv_specific_v glv
105 ,xle_bsv_associations xba
106 ,xle_establishment_v xev ';
107
108 C_ESTABLISHMENT_JOIN CONSTANT VARCHAR2(2000) :=
109 ' AND glv.ledger_id(+) = TABLE1.ledger_id
110 AND glv.segment_value(+) = TABLE1.$leg_seg_val$
111 AND xba.legal_parent_id(+) = glv.legal_entity_id
112 AND xba.entity_name(+) = glv.segment_value
113 AND xba.context(+) = ''EST_BSV_MAPPING''
114 AND xev.establishment_id(+) = xba.legal_construct_id';
115
116 --------------------------------------------------------------------------------
117 -- constant for COMMERCIAL_NUMBER details
118 --------------------------------------------------------------------------------
119 C_COMMERCIAL_QUERY VARCHAR2(8000) :=
120 'SELECT nvl(xler.registration_number,0) LEGAL_COMMERCIAL_NUMBER
121 FROM XLE_REGISTRATIONS_V xler
122 WHERE legislative_category = ''COMMERCIAL_LAW''
123 AND legal_entity_id = :P_LEGAL_ENTITY_ID';
124
125 C_COMMERCIAL_NULL_QUERY VARCHAR2(8000) :=
126 'select NULL LEGAL_COMMERCIAL_NUMBER from dual where 1>2';
127
128 --------------------------------------------------------------------------------
129 -- constant for VAT_REGISTRATION details
130 --------------------------------------------------------------------------------
131 C_VAT_REGISTRATION_QUERY VARCHAR2(8000) :=
132 'SELECT zptp.REP_REGISTRATION_NUMBER LEGAL_VAT_REGISTRATION_NUMBER
133 FROM ZX_PARTY_TAX_PROFILE zptp ,XLE_ETB_PROFILES xetbp
134 WHERE zptp.PARTY_TYPE_CODE = ''LEGAL_ESTABLISHMENT''
135 AND xetbp.party_id=zptp.party_id
136 AND xetbp.MAIN_ESTABLISHMENT_FLAG = ''Y''
137 AND xetbp.LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID' ;
138
139 C_VAT_REGISTRATION_NULL_QUERY VARCHAR2(8000) :=
140 'select NULL LEGAL_VAT_REGISTRATION_NUMBER from dual where 1>2';
141
142
143 C_QUALIFIED_SEGMENT CONSTANT VARCHAR2(1000) :=
144 ' ,$alias_balancing_segment$ BALANCING_SEGMENT
145 ,$alias_account_segment$ NATURAL_ACCOUNT_SEGMENT
146 ,$alias_costcenter_segment$ COST_CENTER_SEGMENT
147 ,$alias_management_segment$ MANAGEMENT_SEGMENT
148 ,$alias_intercompany_segment$ INTERCOMPANY_SEGMENT
149 $seg_desc_column$ ';
150
151 C_NULL_PARTY_COLS CONSTANT VARCHAR2(1000) :=
152 ' ,NULL PARTY_ID
153 ,NULL PARTY_NUMBER
154 ,NULL PARTY_NAME
155 ,NULL PARTY_SITE_ID
156 ,NULL PARTY_SITE_NUMBER
157 ,NULL PARTY_SITE_TAX_REGS_NUMBER';
158
159 -------------------------------------------------------------------------------
160 -- constant for User Transaction Identifiers name and values
161 -------------------------------------------------------------------------------
162
163
164
165 --=============================================================================
166 -- ************** forward declarations ******************
167 --=============================================================================
168 --------------------------------------------------------------------------------
169 -- procedure to create the main SQL
170 --------------------------------------------------------------------------------
171 --=============================================================================
172 -- *********** Local Trace Routine **********
173 --=============================================================================
174 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
175 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
176 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
177 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
178 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
179 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
180
181 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
182 C_DEFAULT_MODULE CONSTANT VARCHAR2(240):= 'xla.plsql.xla_tp_balance_rpt_pkg';
183
184 g_log_level NUMBER;
185 g_log_enabled BOOLEAN;
186 g_je_source_application_id VARCHAR2(30);
187
188 PROCEDURE trace
189 (p_msg IN VARCHAR2
190 ,p_level IN NUMBER
191 ,p_module IN VARCHAR2) IS
192 BEGIN
193 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
194 fnd_log.message(p_level, NVL(p_module,C_DEFAULT_MODULE));
195 ELSIF p_level >= g_log_level THEN
196 fnd_log.string(p_level, NVL(p_module,C_DEFAULT_MODULE), p_msg);
197 END IF;
198
199 EXCEPTION
200 WHEN xla_exceptions_pkg.application_exception THEN
201 RAISE;
202 WHEN OTHERS THEN
203 xla_exceptions_pkg.raise_message
204 (p_location => 'xla_tp_balance_rpt_pkg.trace');
205 END trace;
206
207 /*======================================================================+
208 | |
209 | Private Function |
210 | |
211 | get_flex_range_where |
212 | |
213 | |
214 | Return where clauses for flexfield ranges |
215 | |
216 +======================================================================*/
217
218 FUNCTION get_flex_range_where
219 (p_coa_id IN NUMBER
220 ,p_accounting_flexfield_from IN VARCHAR2
221 ,p_accounting_flexfield_to IN VARCHAR2) RETURN VARCHAR
222
223 IS
224
225 l_log_module VARCHAR2(240);
226
227 l_where VARCHAR2(32000);
228 l_bind_variables fnd_flex_xml_publisher_apis.bind_variables;
229 l_numof_bind_variables NUMBER;
230 l_segment_name VARCHAR2(30);
231 l_segment_value VARCHAR2(1000);
232 l_data_type VARCHAR2(30);
233
234 BEGIN
235
236 IF g_log_enabled THEN
237 l_log_module := C_DEFAULT_MODULE||'.get_flex_range_where';
238 END IF;
239 --
240 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
241
242 trace
243 (p_msg => 'BEGIN of get_flex_range_where'
244 ,p_level => C_LEVEL_PROCEDURE
245 ,p_module => l_log_module);
246
247 END IF;
248
249 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
250
251 trace
252 (p_msg => 'p_coa_id = '||to_char(p_coa_id)
253 ,p_level => C_LEVEL_STATEMENT
254 ,p_module=> l_log_module );
255
256 trace
257 (p_msg => 'p_accounting_flexfield_from = '||to_char(p_accounting_flexfield_from )
258 ,p_level => C_LEVEL_STATEMENT
259 ,p_module=> l_log_module );
260
261 trace
262 (p_msg => 'p_accounting_flexfield_to = '||to_char(p_accounting_flexfield_to)
263 ,p_level => C_LEVEL_STATEMENT
264 ,p_module=> l_log_module );
265
266 END IF;
267
268 --
269 -- e.g. l_where stores the following:
270 -- gcck.SEGMENT1 BETWEEN :FLEX_PARM1 AND :FLEX_PARM2
271 -- AND gcck.SEGMENT2 BETWEEN :FLEX_PARM3 AND :FLEX_PARM4 ...
272 --
273 fnd_flex_xml_publisher_apis.kff_where
274 (p_lexical_name => 'FLEX_PARM'
275 ,p_application_short_name => 'SQLGL'
276 ,p_id_flex_code => 'GL#'
277 ,p_id_flex_num => p_coa_id
278 ,p_code_combination_table_alias => 'gcck'
279 ,p_segments => 'ALL'
280 ,p_operator => 'BETWEEN'
281 ,p_operand1 => p_accounting_flexfield_from
282 ,p_operand2 => p_accounting_flexfield_to
283 ,x_where_expression => l_where
284 ,x_numof_bind_variables => l_numof_bind_variables
285 ,x_bind_variables => l_bind_variables);
286
287 FOR i IN l_bind_variables.FIRST .. l_bind_variables.LAST LOOP
288
289 l_segment_name := l_bind_variables(i).name;
290 l_data_type := l_bind_variables(i).data_type;
291
292 IF (l_data_type='VARCHAR2') THEN
293
294 l_segment_value := '''' || l_bind_variables(i).varchar2_value || '''';
295
296 ELSIF (l_data_type='NUMBER') THEN
297
298 l_segment_value := l_bind_variables(i).canonical_value;
299
300 ELSIF (l_data_type='DATE') THEN
301
302 l_segment_value := '''' || TO_CHAR(l_bind_variables(i).date_value
303 ,'yyyy-mm-dd HH24:MI:SS') || '''';
304
305 END IF;
306
307 --
308 -- Use REGEXP_REPLACE instead of REPLACE not to replace
309 -- string 'SEGMENT1' in 'SEGMENT10'.
310 -- REGEXP_REPLACE replaces the first occurent of a segment name
311 -- e.g.
312 -- BETWEEN :FLEX_PARM9 AND :FLEX_PARM10
313 -- =>
314 -- BETWEEN '000' AND '100'
315 --
316 l_where := REGEXP_REPLACE
317 (l_where
318 ,':' || l_segment_name
319 ,l_segment_value
320 ,1 -- Position
321 ,1 -- The first occurence
322 , 'c' -- Case sensitive
323 );
324
325 END LOOP ;
326
327 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
328
329 trace
330 (p_msg => 'END of get_flex_range_where'
331 ,p_level => C_LEVEL_PROCEDURE
332 ,p_module => l_log_module);
333
334 END IF;
335
336 RETURN l_where;
337
338 EXCEPTION
339 WHEN xla_exceptions_pkg.application_exception THEN
340 RAISE;
341 WHEN OTHERS THEN
342 xla_exceptions_pkg.raise_message
343 (p_location => 'xla_tb_report_pvt.get_flex_range_where');
344
345 END get_flex_range_where;
346 --=============================================================================
347 -- *********** public procedures and functions **********
348 --=============================================================================
349 --=============================================================================
350 --
351 --
352 --
353 --
354 --
355 --
356 --
357 --
358 --
359 --
360 -- Following are public routines
361 --
362 -- 1. beforeReport
363 --
364 --
365 --
366 --
367 --
368 --
369 --
370 --
371 --
372 --
373 --
374 --=============================================================================
375 --=============================================================================
376 --
377 --
378 --
379 --=============================================================================
380 FUNCTION beforeReport RETURN BOOLEAN IS
381
382 l_source_application_id NUMBER;
383 l_start_period_num NUMBER;
384 l_end_period_num NUMBER;
385 l_start_date DATE;
386 l_end_date DATE;
387 l_select_str VARCHAR2(4000);
388 l_from_str VARCHAR2(240);
389 l_where_str VARCHAR2(4000);
390 l_lang VARCHAR2(80);
391 l_count NUMBER;
392 l_ledger_id NUMBER;
393 l_coa_id NUMBER;
394 l_object_type VARCHAR2(30);
395 l_balancing_segment VARCHAR2(80);
396 l_account_segment VARCHAR2(80);
397 l_costcenter_segment VARCHAR2(80);
398 l_management_segment VARCHAR2(80);
399 l_intercompany_segment VARCHAR2(80);
400 l_alias_balancing_segment VARCHAR2(80);
401 l_alias_account_segment VARCHAR2(80);
402 l_alias_costcenter_segment VARCHAR2(80);
403 l_alias_management_segment VARCHAR2(80);
404 l_alias_intercompany_segment VARCHAR2(80);
405 l_seg_desc_column VARCHAR2(2000);
406 l_seg_desc_from VARCHAR2(1000);
407 l_seg_desc_join VARCHAR2(1000);
408 l_other_filter VARCHAR2(2000);
409 l_log_module VARCHAR2(240);
410 l_insert_query VARCHAR2(4000);
411 l_balance_query VARCHAR2(32000);
412 l_flex_range_where VARCHAR2(32000);
413 l_ledger_set_from VARCHAR2(1000) := ' ';
414 l_ledger_set_where VARCHAR2(1000) := ' ';
415 type t_array_app_id is table of NUMBER index by binary_integer;
416
417 l_responsibility_ids t_array_app_id;
418
419 i number;
420 l_temp number;
421 BEGIN
422
423 IF g_log_enabled THEN
424 l_log_module := C_DEFAULT_MODULE||'.beforeReport';
425 END IF;
426 --
427 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
428 trace
429 (p_msg => 'BEGIN of beforeReport'
430 ,p_level => C_LEVEL_PROCEDURE
431 ,p_module => l_log_module);
432 trace
433 (p_msg => 'party type is:'||p_party_type
434 ,p_level => C_LEVEL_PROCEDURE
435 ,p_module => l_log_module);
436 END IF;
437
438 --
439 -- default values
440 --
441 P_INCLUDE_DRAFT_ACTIVITY_FLAG := NVL(P_INCLUDE_DRAFT_ACTIVITY_FLAG,'N');
442 P_INCLUDE_ZERO_AMT_LINES_FLAG := NVL(P_INCLUDE_ZERO_AMT_LINES_FLAG,'N');
443 P_INCLUDE_USER_TRX_ID_FLAG := NVL(P_INCLUDE_USER_TRX_ID_FLAG,'N');
444 P_INCLUDE_TAX_DETAILS_FLAG := NVL(P_INCLUDE_TAX_DETAILS_FLAG,'N');
445 P_INCLUDE_LE_INFO_FLAG := NVL(P_INCLUDE_LE_INFO_FLAG,'NONE');
446
447 IF p_je_source_name = '#ALL#' THEN
448 p_je_source_name := 'ALL';
449 END IF;
450
451 IF p_party_type is NULL THEN -- Bug 5477846
452 xla_exceptions_pkg.raise_message
453 (p_appli_s_name => 'XLA'
454 ,p_msg_name => 'XLA_COMMON_ERROR'
455 ,p_token_1 => 'ERROR'
456 ,p_value_1 => 'Party type parameter is mandatory, but missing.'||
457 'Please populate the party type.'
458 ,p_token_2 => 'LOCATION'
459 ,p_value_2 => 'xla_tp_balance_rpt_pkg.beforeReport');
460 END IF;
461
462 BEGIN
463 SELECT application_id
464 INTO g_je_source_application_id
465 FROM xla_subledgers
466 WHERE je_source_name = p_je_source_name;
467 EXCEPTION
468 WHEN NO_DATA_FOUND THEN
469 g_je_source_application_id := NULL;
470 END;
471
472 --
473 -- following will set the right transaction security.
474 --
475 xla_security_pkg.set_security_context(g_je_source_application_id);
476
477
478
479 --
480 -- User Transaction Identifiers
481 --
482 IF p_include_user_trx_id_flag = 'Y' AND
483 g_je_source_application_id IS NOT NULL
484 THEN
485 p_trx_identifiers :=
486 xla_report_utility_pkg.get_transaction_id
487 (g_je_source_application_id
488 ,p_ledger_id);
489 END IF;
490
491 --
492 -- Identifying ledger as Ledger or Ledger Set and get value for language
493 --
494 SELECT object_type_code, USERENV('LANG')
495 INTO l_object_type, l_lang
496 FROM gl_ledgers
497 WHERE ledger_id = p_ledger_id;
498
499 IF(P_JE_SOURCE_NAME='ALL') THEN
500 select application_id
501 bulk COLLECT into l_responsibility_ids
502 from xla_subledgers
503 WHERE control_account_type_code = 'Y' or control_account_type_code = P_PARTY_TYPE;
504 END IF;
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_ledger_set_from := l_ledger_set_from ||' ,gl_ledger_set_assignments glst ';
510 l_ledger_set_where := l_ledger_set_where||' AND glst.ledger_set_id = :P_LEDGER_ID '||
511 ' AND gll.ledger_id = glst.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 ELSE
519 l_ledger_set_where := l_ledger_set_where||' AND gll.ledger_id = :P_LEDGER_ID ';
520 l_ledger_id := p_ledger_id;
521
522 END IF;
523
524 SELECT effective_period_num
525 ,START_DATE
526 INTO l_start_period_num
527 ,l_start_date
528 FROM gl_period_statuses
529 WHERE application_id = 101
530 AND ledger_id = l_ledger_id
531 AND period_name = p_period_from;
532
533 SELECT effective_period_num
534 ,end_date
535 INTO l_end_period_num
536 ,l_end_date
537 FROM gl_period_statuses
538 WHERE application_id = 101
539 AND ledger_id = l_ledger_id
540 AND period_name = p_period_to;
541
542 --
543 -- Third party information based on application_id
544 --
545 -- 5072266 Modify po_vendors to use ap_suppliers
546 -- po_vendors pov -> ap_supplier ap
547 -- pov.segment1 -> aps.segment1
548 -- pov.vendor_name -> vendor_name
549 -- pov.party_id -> aps.vendor_id
550 IF p_party_type = 'SUPPLIER' THEN
551 p_party_col := ',aps.vendor_id PARTY_ID '
552 ||',aps.segment1 PARTY_NUMBER'
553 ||',aps.vendor_name PARTY_NAME'
554 ||',NVL(apss.vendor_site_id,-999) PARTY_SITE_ID'
555 ||',hps.party_site_number PARTY_SITE_NUMBER'
556 ||',NULL PARTY_SITE_TAX_REGS_NUMBER';
557
558 p_party_tab := ' ,ap_suppliers aps
559 ,ap_supplier_sites_all apss ';
560
561 p_party_join := 'AND aps.vendor_id = xcb.party_id '
562 ||'AND hzp.party_id = aps.party_id '
563 ||'AND apss.vendor_site_id(+) = xcb.party_site_id '
564 ||'AND hps.party_site_id(+) = apss.party_site_id';
565
566
567 ELSIF p_party_type = 'CUSTOMER' THEN
568 p_party_col := ',hca.cust_account_id PARTY_ID'
569 ||',hca.account_number PARTY_NUMBER'
570 ||',hzp.party_name PARTY_NAME '
571 ||',NVL(hzcu.site_use_id, -999) PARTY_SITE_ID'
572 ||',hps.party_site_number PARTY_SITE_NUMBER'
573 ||',hzcu.tax_reference PARTY_SITE_TAX_REGS_NUMBER';
574
575 p_party_tab := ',hz_cust_accounts hca '
576 ||',hz_cust_acct_sites_all hcas'
577 ||',hz_cust_site_uses_all hzcu';
578
579 p_party_join :=' AND hzp.party_id = hca.party_id '
580 ||' AND hca.cust_account_id = xcb.party_id '
581 ||' AND hzcu.site_use_id(+) = xcb.party_site_id'
582 ||' AND hcas.cust_acct_site_id(+) = hzcu.cust_acct_site_id'
583 ||' AND hps.party_site_id(+) = hcas.party_site_id ';
584 ELSE
585 p_party_col := C_NULL_PARTY_COLS;
586 END IF;
587
588 p_commercial_query := C_COMMERCIAL_QUERY;
589 p_vat_registration_query := C_VAT_REGISTRATION_QUERY;
590
591 --
592 -- Qualified segments
593 --
594 p_qualifier_segment := C_QUALIFIED_SEGMENT;
595
596 --
597 -- get COA for the ledger/ledger set
598 --
599
600 SELECT chart_of_accounts_id
601 INTO l_coa_id
602 FROM gl_ledgers
603 WHERE ledger_id = p_ledger_id;
604
605 ----------------------------------------------------------------------------
606 -- get qualifier segments for the COA
607 ----------------------------------------------------------------------------
608 xla_report_utility_pkg.get_acct_qualifier_segs
609 (p_coa_id => l_coa_id
610 ,p_balance_segment => l_balancing_segment
611 ,p_account_segment => l_account_segment
612 ,p_cost_center_segment => l_costcenter_segment
613 ,p_management_segment => l_management_segment
614 ,p_intercompany_segment => l_intercompany_segment);
615
616 --
617 -- attach table alias to the column names
618 --
619 IF l_balancing_segment = 'NULL' THEN
620 l_alias_balancing_segment := 'NULL';
621 ELSE
622 l_alias_balancing_segment := 'gcck.'||l_balancing_segment;
623 END IF;
624
625 IF l_account_segment = 'NULL' THEN
626 l_alias_account_segment := 'NULL';
627 ELSE
628 l_alias_account_segment := 'gcck.'||l_account_segment;
629 END IF;
630
631 IF l_costcenter_segment = 'NULL' THEN
632 l_alias_costcenter_segment := 'NULL';
633 ELSE
634 l_alias_costcenter_segment := 'gcck.'||l_costcenter_segment;
635 END IF;
636
637 IF l_management_segment = 'NULL' THEN
638 l_alias_management_segment := 'NULL';
639 ELSE
640 l_alias_management_segment := 'gcck.'||l_management_segment;
641 END IF;
642
643 IF l_intercompany_segment = 'NULL' THEN
644 l_alias_intercompany_segment := 'NULL';
645 ELSE
646 l_alias_intercompany_segment := 'gcck.'||l_intercompany_segment;
647 END IF;
648
649 --
650 -- replace placeholders for the qualified segemnts
651 --
652 p_qualifier_segment:= REPLACE(p_qualifier_segment
653 ,'$alias_balancing_segment$'
654 ,l_alias_balancing_segment);
655
656 p_qualifier_segment := REPLACE(p_qualifier_segment
657 ,'$alias_account_segment$'
658 ,l_alias_account_segment);
659
660 p_qualifier_segment := REPLACE(p_qualifier_segment
661 ,'$alias_costcenter_segment$'
662 ,l_alias_costcenter_segment);
663
664 p_qualifier_segment := REPLACE(p_qualifier_segment
665 ,'$alias_management_segment$'
666 ,l_alias_management_segment);
667
668 p_qualifier_segment := REPLACE(p_qualifier_segment
669 ,'$alias_intercompany_segment$'
670 ,l_alias_intercompany_segment);
671
672 ----------------------------------------------------------------------------
673 -- building code to get segment description
674 ----------------------------------------------------------------------------
675 IF l_balancing_segment <> 'NULL' THEN
676 l_seg_desc_column := l_seg_desc_column||',fvbs.description BALANCING_SEGMENT_DESC ';
677 l_seg_desc_from := l_seg_desc_from||',fnd_flex_values_vl fvbs ';
678 l_seg_desc_join := l_seg_desc_join||C_SEG_DESC_JOIN;
679 l_seg_desc_join := replace(l_seg_desc_join,'$alias$','fvbs');
680 l_seg_desc_join :=
681 replace
682 (l_seg_desc_join
683 ,'$flex_value_set_id$'
684 ,xla_flex_pkg.get_segment_valueset
685 (p_application_id => 101
686 ,p_id_flex_code => 'GL#'
687 ,p_id_flex_num => l_coa_id
688 ,p_segment_code => l_balancing_segment
689 )
690 );
691 l_seg_desc_join := replace(l_seg_desc_join
692 ,'$segment_column$'
693 ,l_alias_balancing_segment);
694 ELSE
695 l_seg_desc_column := l_seg_desc_column ||', NULL BALANCING_SEGMENT_DESC';
696 END IF;
697
698 IF l_account_segment <> 'NULL' THEN
699 l_seg_desc_column := l_seg_desc_column||',fvna.description NATURAL_ACCOUNT_DESC ';
700 l_seg_desc_from := l_seg_desc_from||',fnd_flex_values_vl fvna ';
701 l_seg_desc_join := l_seg_desc_join||C_SEG_DESC_JOIN;
702 l_seg_desc_join := replace(l_seg_desc_join,'$alias$','fvna');
703 l_seg_desc_join :=
704 replace
705 (l_seg_desc_join
706 ,'$flex_value_set_id$'
707 ,xla_flex_pkg.get_segment_valueset
708 (p_application_id => 101
709 ,p_id_flex_code => 'GL#'
710 ,p_id_flex_num => l_coa_id
711 ,p_segment_code => l_account_segment
712 )
713 );
714 l_seg_desc_join := replace(l_seg_desc_join
715 ,'$segment_column$'
716 ,l_alias_account_segment);
717 ELSE
718 l_seg_desc_column := l_seg_desc_column ||', NULL NATURAL_ACCOUNT_DESC';
719 END IF;
720
721 IF l_costcenter_segment <> 'NULL' THEN
722 l_seg_desc_column := l_seg_desc_column||',fvcc.description COST_CENTER_DESC ';
723 l_seg_desc_from := l_seg_desc_from||',fnd_flex_values_vl fvcc ';
724 l_seg_desc_join := l_seg_desc_join||C_SEG_DESC_JOIN;
725 l_seg_desc_join := replace(l_seg_desc_join,'$alias$','fvcc');
726 l_seg_desc_join :=
727 replace
728 (l_seg_desc_join
729 ,'$flex_value_set_id$'
730 ,xla_flex_pkg.get_segment_valueset
731 (p_application_id => 101
732 ,p_id_flex_code => 'GL#'
733 ,p_id_flex_num => l_coa_id
734 ,p_segment_code => l_costcenter_segment
735 )
736 );
737 l_seg_desc_join := replace(l_seg_desc_join
738 ,'$segment_column$'
739 ,l_alias_costcenter_segment);
740 ELSE
741 l_seg_desc_column := l_seg_desc_column ||', NULL COST_CENTER_DESC';
742 END IF;
743
744 IF l_management_segment <> 'NULL' THEN
745 l_seg_desc_column := l_seg_desc_column||',fvmg.description MANAGEMENT_SEGMENT_DESC ';
746 l_seg_desc_from := l_seg_desc_from||',fnd_flex_values_vl fvmg ';
747 l_seg_desc_join := l_seg_desc_join||C_SEG_DESC_JOIN;
748 l_seg_desc_join := replace(l_seg_desc_join,'$alias$','fvmg');
749 l_seg_desc_join :=
750 replace
751 (l_seg_desc_join
752 ,'$flex_value_set_id$'
753 ,xla_flex_pkg.get_segment_valueset
754 (p_application_id => 101
755 ,p_id_flex_code => 'GL#'
756 ,p_id_flex_num => l_coa_id
757 ,p_segment_code => l_management_segment
758 )
759 );
760 l_seg_desc_join := replace(l_seg_desc_join
761 ,'$segment_column$'
762 ,l_alias_management_segment);
763 ELSE
764 l_seg_desc_column := l_seg_desc_column ||', NULL MANAGEMENT_SEGMENT_DESC';
765 END IF;
766
767 IF l_intercompany_segment <> 'NULL' THEN
768 l_seg_desc_column := l_seg_desc_column||',fvic.description INTERCOMPANY_SEGMENT_DESC ';
769 l_seg_desc_from := l_seg_desc_from||',fnd_flex_values_vl fvic ';
770 l_seg_desc_join := l_seg_desc_join||C_SEG_DESC_JOIN;
771 l_seg_desc_join := replace(l_seg_desc_join,'$alias$','fvic');
772 l_seg_desc_join :=
773 replace
774 (l_seg_desc_join
775 ,'$flex_value_set_id$'
776 ,xla_flex_pkg.get_segment_valueset
777 (p_application_id => 101
778 ,p_id_flex_code => 'GL#'
779 ,p_id_flex_num => l_coa_id
780 ,p_segment_code => l_intercompany_segment
781 )
782 );
783 l_seg_desc_join := replace(l_seg_desc_join
784 ,'$segment_column$'
785 ,l_alias_intercompany_segment);
786 ELSE
787 l_seg_desc_column := l_seg_desc_column ||', NULL INTERCOMPANY_SEGMENT_DESC';
788 END IF;
789
790 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
791 trace
792 (p_msg => 'seg_desc_column ='||l_seg_desc_column
793 ,p_level => C_LEVEL_STATEMENT
794 ,p_module=> l_log_module);
795 trace
796 (p_msg => 'seg_desc_from ='||l_seg_desc_from
797 ,p_level => C_LEVEL_STATEMENT
798 ,p_module=> l_log_module);
799 trace
800 (p_msg => 'seg_desc_join ='||l_seg_desc_join
801 ,p_level => C_LEVEL_STATEMENT
802 ,p_module=> l_log_module);
803 END IF;
804 --
805 -- replace placeholders for the qualified segemnts
806 --
807 p_qualifier_segment := REPLACE(p_qualifier_segment
808 ,'$seg_desc_column$'
809 ,l_seg_desc_column);
810
811 p_seg_desc_from := l_seg_desc_from;
812
813 p_seg_desc_join := l_seg_desc_join;
814
815 --
816 -- Legal Entity Information
817 --
818
819 --
820 -- Replace placeholders for Legal entity information
821 --
822 IF p_include_le_info_flag = 'LEGAL_ENTITY' THEN
823 p_legal_ent_col := C_LEGAL_ENT_COL;
824 p_legal_ent_from := C_LEGAL_ENT_FROM;
825 p_legal_ent_join := C_LEGAL_ENT_JOIN;
826
827 p_legal_ent_join := REPLACE(p_legal_ent_join,'$leg_seg_val$',l_balancing_segment);
828
829 IF p_legal_entity_id IS NOT NULL THEN
830 p_legal_ent_join := p_legal_ent_join ||
831 ' AND gle.legal_entity_id = '||p_legal_entity_id;
832 END IF;
833 ELSIF p_include_le_info_flag = 'ESTABLISHMENT' THEN
834 p_legal_ent_col := C_ESTBLISHMENT_COL;
835 p_legal_ent_from := C_ESTABLISHMENT_FROM;
836 p_legal_ent_join := C_ESTABLISHMENT_JOIN;
837
838 p_legal_ent_join := REPLACE(p_legal_ent_join,'$leg_seg_val$',l_balancing_segment);
839
840 IF p_legal_entity_id IS NOT NULL THEN
841 p_legal_ent_join := p_legal_ent_join ||
842 ' AND glv.legal_entity_id = '||p_legal_entity_id;
843 END IF;
844 ELSE -- p_include_le_info_flag = 'NONE' THEN
845 p_legal_ent_col := C_NULL_LEGAL_ENT_COL;
846 p_legal_ent_from := ' ';
847 p_legal_ent_join := ' ';
848
849 IF p_legal_entity_id IS NOT NULL THEN
850 p_legal_ent_from := ' ,gl_ledger_le_bsv_specific_v gle ';
851 p_legal_ent_join := ' AND gle.ledger_id(+) = TABLE1.LEDGER_ID '||
852 ' AND gle.segment_value(+) = TABLE1.$leg_seg_val$ '||
853 ' AND gle.legal_entity_id(+) = '||p_legal_entity_id;
854
855 p_legal_ent_join := REPLACE(p_legal_ent_join,'$leg_seg_val$',l_balancing_segment);
856 END IF;
857 END IF;
858
859
860 --===========================================================================
861 -- Build Filter condition based on parameters
862 --===========================================================================
863 --
864 -- Filter based on Balancing Segment Value
865 --
866 IF p_balancing_segment_from IS NOT NULL AND
867 p_balancing_segment_to IS NOT NULL THEN
868 l_other_filter :=
869 l_other_filter ||' AND '||l_alias_balancing_segment||' BETWEEN '''
870 ||p_balancing_segment_from ||''' AND '''||p_balancing_segment_to||'''';
871 END IF;
872 --
873 -- Filter based on Natural Account Segment Value
874 --
875 IF p_account_segment_from IS NOT NULL AND
876 p_account_segment_to IS NOT NULL THEN
877 l_other_filter :=
878 l_other_filter ||' AND '||l_alias_account_segment||' BETWEEN '''
879 ||p_account_segment_from ||''' AND '''||p_account_segment_to||'''';
880 END IF;
881
882
883 --
884 -- <conditions based on side>
885 --
886 IF UPPER(p_balance_side_code) = 'DEBIT' THEN
887 IF p_include_draft_activity ='Y' THEN
888 IF p_balance_amount_from IS NOT NULL THEN
889 l_other_filter :=
890 l_other_filter ||
891 ' AND((( NVL(xcb.beginning_balance_cr,0)
892 + NVL(xcb.period_balance_cr,0)
893 + NVL(xcb.draft_beginning_balance_cr,0)
894 + NVL(xcb.period_draft_balance_cr,0))
895 - ( NVL(xcb.beginning_balance_dr,0)
896 + NVL(xcb.period_balance_dr,0)
897 + NVL(xcb.draft_beginning_balance_dr,0)
898 + NVL(xcb.period_draft_balance_dr,0))) < -'
899 ||p_balance_amount_from ||' )';
900 ELSE
901 l_other_filter :=
902 l_other_filter ||
903 ' AND((( NVL(xcb.beginning_balance_cr,0)
904 + NVL(xcb.period_balance_cr,0)
905 + NVL(xcb.draft_beginning_balance_cr,0)
906 + NVL(xcb.period_draft_balance_cr,0))
907 - ( NVL(xcb.beginning_balance_dr,0)
908 + NVL(xcb.period_balance_dr,0)
909 + NVL(xcb.draft_beginning_balance_dr,0)
910 + NVL(xcb.period_draft_balance_dr,0))) < 0 )';
911 END IF;
912 IF p_balance_amount_to IS NOT NULL THEN
913 l_other_filter :=
914 l_other_filter ||
915 ' AND((( NVL(xcb.beginning_balance_cr,0)
916 + NVL(xcb.period_balance_cr,0)
917 + NVL(xcb.draft_beginning_balance_cr,0)
918 + NVL(xcb.period_draft_balance_cr,0))
919 - ( NVL(xcb.beginning_balance_dr,0)
920 + NVL(xcb.period_balance_dr,0)
921 + NVL(xcb.draft_beginning_balance_dr,0)
922 + NVL(xcb.period_draft_balance_dr,0))) > -'
923 ||p_balance_amount_to ||' )';
924 END IF;
925 ELSE
926 IF p_balance_amount_from IS NOT NULL THEN
927 l_other_filter :=
928 l_other_filter ||
929 ' AND ((( NVL(xcb.beginning_balance_cr,0)
930 + NVL(xcb.period_balance_cr,0))
931 - ( NVL(xcb.beginning_balance_dr,0)
932 + NVL(xcb.period_balance_dr,0))) < -'
933 ||p_balance_amount_from||' )';
934 ELSE
935 l_other_filter :=
936 l_other_filter ||
937 ' AND((( NVL(xcb.beginning_balance_cr,0)
938 + NVL(xcb.period_balance_cr,0))
939 - ( NVL(xcb.beginning_balance_dr,0)
940 + NVL(xcb.period_balance_dr,0))) < 0 )';
941 END IF;
942 IF p_balance_amount_to IS NOT NULL THEN
943 l_other_filter :=
944 l_other_filter ||
945 ' AND((( NVL(xcb.beginning_balance_cr,0)
946 + NVL(xcb.period_balance_cr,0))
947 - ( NVL(xcb.beginning_balance_dr,0)
948 + NVL(xcb.period_balance_dr,0))) > -'
949 ||p_balance_amount_to ||' ) ';
950 END IF;
951 END IF;
952 ELSIF UPPER(p_balance_side_code) = 'CREDIT' THEN
953 IF p_include_draft_activity ='Y' THEN
954 IF p_balance_amount_from IS NOT NULL THEN
955 l_other_filter :=
956 l_other_filter ||
957 ' AND ((( NVL(xcb.beginning_balance_cr,0)
958 + NVL(xcb.period_balance_cr,0)
959 + NVL(xcb.draft_beginning_balance_cr,0)
960 + NVL(xcb.period_draft_balance_cr,0))
961 - ( NVL(xcb.beginning_balance_dr,0)
962 + NVL(xcb.period_balance_dr,0)
963 + NVL(xcb.draft_beginning_balance_dr,0)
964 + NVL(xcb.period_draft_balance_dr,0)))> '
965 ||p_balance_amount_from ||' ) ';
966 ELSE
967 l_other_filter :=
968 l_other_filter ||
969 ' AND((( NVL(xcb.beginning_balance_cr,0)
970 + NVL(xcb.period_balance_cr,0)
971 + NVL(xcb.draft_beginning_balance_cr,0)
972 + NVL(xcb.period_draft_balance_cr,0))
973 - ( NVL(xcb.beginning_balance_dr,0)
974 + NVL(xcb.period_balance_dr,0)
975 + NVL(xcb.draft_beginning_balance_dr,0)
976 + NVL(xcb.period_draft_balance_dr,0))) > 0 )';
977 END IF;
978 IF p_balance_amount_to IS NOT NULL THEN
979 l_other_filter :=
980 l_other_filter ||
981 ' AND((( NVL(xcb.beginning_balance_cr,0)
982 + NVL(xcb.period_balance_cr,0)
983 + NVL(xcb.draft_beginning_balance_cr,0)
984 + NVL(xcb.period_draft_balance_cr,0))
985 - (NVL(xcb.beginning_balance_dr,0)
986 + NVL(xcb.period_balance_dr,0)
987 + NVL(xcb.draft_beginning_balance_dr,0)
988 + NVL(xcb.period_draft_balance_dr,0))) < '
989 ||p_balance_amount_to ||' )';
990 END IF;
991 ELSE
992 IF p_balance_amount_from IS NOT NULL THEN
993 l_other_filter :=
994 l_other_filter ||
995 ' AND ((( NVL(xcb.beginning_balance_cr,0)
996 + NVL(xcb.period_balance_cr,0))
997 - ( NVL(xcb.beginning_balance_dr,0)
998 + NVL(xcb.period_balance_dr,0))) > '
999 ||p_balance_amount_from||' ) ';
1000 ELSE
1001 l_other_filter :=
1002 l_other_filter ||
1003 ' AND((( NVL(xcb.beginning_balance_cr,0)
1004 + NVL(xcb.period_balance_cr,0))
1005 - ( NVL(xcb.beginning_balance_dr,0))
1006 + NVL(xcb.period_balance_dr,0))) > 0 )';
1007 END IF;
1008 IF p_balance_amount_to IS NOT NULL THEN
1009 l_other_filter :=
1010 l_other_filter ||
1011 ' AND ((( NVL(xcb.beginning_balance_cr,0)
1012 + NVL(xcb.period_balance_cr,0))
1013 - ( NVL(xcb.beginning_balance_dr,0)
1014 + NVL(xcb.period_balance_dr,0))) < '
1015 ||p_balance_amount_to|| ' )';
1016 END IF;
1017 END IF;
1018 END IF;
1019
1020 --
1021 -- <condition for party type
1022 --
1023 IF p_party_type IS NOT NULL THEN
1024 IF p_party_type = 'CUSTOMER' THEN
1025 l_other_filter := l_other_filter||' AND xcb.party_type_code = ''C''';
1026 ELSIF p_party_type = 'SUPPLIER' THEN
1027 l_other_filter := l_other_filter||' AND xcb.party_type_code = ''S''';
1028 END IF;
1029 END IF;
1030
1031 --
1032 -- <condition for party id
1033 --
1034 IF p_party_id IS NOT NULL THEN
1035 l_other_filter := l_other_filter||' AND xcb.party_id = '
1036 ||p_party_id;
1037 END IF;
1038
1039 --
1040 -- <condition for party site id
1041 --
1042 IF p_party_site_id IS NOT NULL THEN
1043 l_other_filter := l_other_filter||' AND xcb.party_site_id = '
1044 ||p_party_site_id;
1045 END IF;
1046
1047 --
1048 -- <condition for party number range
1049 -- parameter names changed for bug 5635953
1050 --
1051 IF p_party_number_from IS NOT NULL AND
1052 p_party_number_to IS NOT NULL THEN
1053 IF p_party_type = 'CUSTOMER' THEN
1054 l_other_filter :=
1055 l_other_filter||' AND hca.cust_account_id BETWEEN '''||p_party_number_from||
1056 ''' AND '''||p_party_number_to||'''';
1057 ELSIF p_party_type = 'SUPPLIER' THEN
1058 l_other_filter :=
1059 l_other_filter||' AND aps.segment1 BETWEEN '''||p_party_number_from||
1060 ''' AND '''||p_party_number_to||'''';
1061 END IF;
1062 END IF;
1063
1064 --
1065 -- <condition based on Include Draft activity >
1066 --
1067 IF p_include_draft_activity_flag = 'Y' THEN
1068 p_other_filter :=p_other_filter||' AND aeh.accounting_entry_status_code IN (''D'',''F'')';
1069 ELSE
1070 p_other_filter :=p_other_filter||' AND aeh.accounting_entry_status_code = ''F'' ';
1071 END IF;
1072
1073 --
1074 -- <condition for Including zero amount lines>
1075 --
1076 IF p_include_zero_amt_lines_flag = 'N' THEN
1077 p_other_filter := p_other_filter||' AND (NVL(ael.accounted_dr,0) > 0
1078 OR NVL(ael.accounted_cr,0) > 0) ';
1079 END IF;
1080
1081 p_lang := l_lang;
1082 p_start_period_num := l_start_period_num;
1083 p_end_period_num := l_end_period_num;
1084 p_start_date := l_start_date;
1085 p_end_date := l_end_date;
1086
1087 l_balance_query :=
1088 '
1089 INSERT INTO xla_report_balances_gt
1090 (ledger_id
1091 ,ledger_short_name
1092 ,ledger_description
1093 ,ledger_name
1094 ,ledger_currency
1095 ,legal_entity_id
1096 ,legal_entity_name
1097 ,le_address_line_1
1098 ,le_address_line_2
1099 ,le_address_line_3
1100 ,le_city
1101 ,le_region_1
1102 ,le_region_2
1103 ,le_region_3
1104 ,le_postal_code
1105 ,le_country
1106 ,le_registration_number
1107 ,le_registration_effective_from
1108 ,le_activity_code
1109 ,le_sub_activity_code
1110 ,le_contact_name
1111 ,le_contact_phone_number
1112 ,party_type_code
1113 ,party_id
1114 ,party_number
1115 ,party_name
1116 ,party_site_id
1117 ,party_site_number
1118 ,party_site_tax_regs_number
1119 ,party_type_taxpayer_id
1120 ,party_tax_registration_number
1121 ,party_address_1
1122 ,party_address_2
1123 ,party_address_3
1124 ,party_address_4
1125 ,party_city
1126 ,party_zip_code
1127 ,party_state
1128 ,party_province
1129 ,party_country
1130 ,party_county
1131 ,party_site_name
1132 ,party_site_address_line_1
1133 ,party_site_address_line_2
1134 ,party_site_address_line_3
1135 ,party_site_address_line_4
1136 ,party_site_city
1137 ,party_site_zip_code
1138 ,party_site_state
1139 ,party_site_province
1140 ,party_site_country
1141 ,party_site_county
1142 ,application_id
1143 ,application_name
1144 ,je_source_name
1145 ,period_year
1146 ,period_number
1147 ,period_name
1148 ,period_start_date
1149 ,period_end_date
1150 ,begin_balance_dr
1151 ,begin_balance_cr
1152 ,period_net_dr
1153 ,period_net_cr
1154 ,begin_draft_balance_dr
1155 ,begin_draft_balance_cr
1156 ,period_draft_net_dr
1157 ,period_draft_net_cr
1158 ,code_combination_id
1159 ,accounting_code_combination
1160 ,code_combination_description
1161 ,balancing_segment
1162 ,natural_account_segment
1163 ,cost_center_segment
1164 ,management_segment
1165 ,intercompany_segment
1166 ,balancing_segment_desc
1167 ,natural_account_desc
1168 ,cost_center_desc
1169 ,management_segment_desc
1170 ,intercompany_segment_desc
1171 ,segment1
1172 ,segment2
1173 ,segment3
1174 ,segment4
1175 ,segment5
1176 ,segment6
1177 ,segment7
1178 ,segment8
1179 ,segment9
1180 ,segment10
1181 ,segment11
1182 ,segment12
1183 ,segment13
1184 ,segment14
1185 ,segment15
1186 ,segment16
1187 ,segment17
1188 ,segment18
1189 ,segment19
1190 ,segment20
1191 ,segment21
1192 ,segment22
1193 ,segment23
1194 ,segment24
1195 ,segment25
1196 ,segment26
1197 ,segment27
1198 ,segment28
1199 ,segment29
1200 ,segment30)
1201 (
1202 SELECT TABLE1.LEDGER_ID LEDGER_ID
1203 ,TABLE1.LEDGER_SHORT_NAME LEDGER_SHORT_NAME
1204 ,TABLE1.LEDGER_DESCRIPTION LEDGER_DESCRIPTION
1205 ,TABLE1.LEDGER_NAME LEDGER_NAME
1206 ,TABLE1.LEDGER_CURRENCY LEDGER_CURRENCY
1207 $legal_entity_columns$
1208 ,TABLE1.PARTY_TYPE_CODE PARTY_TYPE_CODE
1209 ,TABLE1.PARTY_ID PARTY_ID
1210 ,TABLE1.PARTY_NUMBER PARTY_NUMBER
1211 ,TABLE1.PARTY_NAME PARTY_NAME
1212 ,TABLE1.PARTY_SITE_ID PARTY_SITE_ID
1213 ,TABLE1.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
1214 ,TABLE1.PARTY_SITE_TAX_REGS_NUMBER PARTY_SITE_TAX_REGS_NUMBER
1215 ,TABLE1.PARTY_TYPE_TAXPAYER_ID PARTY_TYPE_TAXPAYER_ID
1216 ,TABLE1.PARTY_TAX_REGISTRATION_NUMBER PARTY_TAX_REGISTRATION_NUMBER
1217 ,TABLE1.PARTY_ADDRESS_1 PARTY_ADDRESS_1
1218 ,TABLE1.PARTY_ADDRESS_2 PARTY_ADDRESS_2
1219 ,TABLE1.PARTY_ADDRESS_3 PARTY_ADDRESS_3
1220 ,TABLE1.PARTY_ADDRESS_4 PARTY_ADDRESS_4
1221 ,TABLE1.PARTY_CITY PARTY_CITY
1222 ,TABLE1.PARTY_ZIP_CODE PARTY_ZIP_CODE
1223 ,TABLE1.PARTY_STATE PARTY_STATE
1224 ,TABLE1.PARTY_PROVINCE PARTY_PROVINCE
1225 ,TABLE1.PARTY_COUNTRY PARTY_COUNTRY
1226 ,TABLE1.PARTY_COUNTY PARTY_COUNTY
1227 ,TABLE1.PARTY_SITE_NAME PARTY_SITE_NAME
1228 ,TABLE1.PARTY_SITE_ADDRESS_LINE_1 PARTY_SITE_ADDRESS_LINE_1
1229 ,TABLE1.PARTY_SITE_ADDRESS_LINE_2 PARTY_SITE_ADDRESS_LINE_2
1230 ,TABLE1.PARTY_SITE_ADDRESS_LINE_3 PARTY_SITE_ADDRESS_LINE_3
1231 ,TABLE1.PARTY_SITE_ADDRESS_LINE_4 PARTY_SITE_ADDRESS_LINE_4
1232 ,TABLE1.PARTY_SITE_CITY PARTY_SITE_CITY
1233 ,TABLE1.PARTY_SITE_ZIP_CODE PARTY_SITE_ZIP_CODE
1234 ,TABLE1.PARTY_SITE_STATE PARTY_SITE_STATE
1235 ,TABLE1.PARTY_SITE_PROVINCE PARTY_SITE_PROVINCE
1236 ,TABLE1.PARTY_SITE_COUNTRY PARTY_SITE_COUNTRY
1237 ,TABLE1.PARTY_SITE_COUNTY PARTY_SITE_COUNTY
1238 ,TABLE1.APPLICATION_ID APPLICATION_ID
1239 ,TABLE1.APPLICATION_NAME APPLICATION_NAME
1240 ,TABLE1.JE_SOURCE_NAME JE_SOURCE_NAME
1241 ,TABLE1.PERIOD_YEAR PERIOD_YEAR
1242 ,TABLE1.PERIOD_NUMBER PERIOD_NUMBER
1243 ,TABLE1.PERIOD_NAME PERIOD_NAME
1244 ,TABLE1.PERIOD_START_DATE PERIOD_START_DATE
1245 ,TABLE1.PERIOD_END_DATE PERIOD_END_DATE
1246 ,TABLE1.BEGIN_BALANCE_DR BEGIN_BALANCE_DR
1247 ,TABLE1.BEGIN_BALANCE_CR BEGIN_BALANCE_CR
1248 ,TABLE1.PERIOD_NET_DR PERIOD_NET_DR
1249 ,TABLE1.PERIOD_NET_CR PERIOD_NET_CR
1250 ,TABLE1.BEGIN_DRAFT_BALANCE_DR BEGIN_DRAFT_BALANCE_DR
1251 ,TABLE1.BEGIN_DRAFT_BALANCE_CR BEGIN_DRAFT_BALANCE_CR
1252 ,TABLE1.PERIOD_DRAFT_NET_DR PERIOD_DRAFT_NET_DR
1253 ,TABLE1.PERIOD_DRAFT_NET_CR PERIOD_DRAFT_NET_CR
1254 ,TABLE1.CODE_COMBINATION_ID CODE_COMBINATION_ID
1255 ,TABLE1.ACCOUNTING_CODE_COMBINATION ACCOUNTING_CODE_COMBINATION
1256 ,TABLE1.CODE_COMBINATION_DESCRIPTION CODE_COMBINATION_DESCRIPTION
1257 ,TABLE1.BALANCING_SEGMENT BALANCING_SEGMENT
1258 ,TABLE1.NATURAL_ACCOUNT_SEGMENT NATURAL_ACCOUNT_SEGMENT
1259 ,TABLE1.COST_CENTER_SEGMENT COST_CENTER_SEGMENT
1260 ,TABLE1.MANAGEMENT_SEGMENT MANAGEMENT_SEGMENT
1261 ,TABLE1.INTERCOMPANY_SEGMENT INTERCOMPANY_SEGMENT
1262 ,TABLE1.BALANCING_SEGMENT_DESC BALANCING_SEGMENT_DESC
1263 ,TABLE1.NATURAL_ACCOUNT_DESC NATURAL_ACCOUNT_DESC
1264 ,TABLE1.COST_CENTER_DESC COST_CENTER_DESC
1265 ,TABLE1.MANAGEMENT_SEGMENT_DESC MANAGEMENT_SEGMENT_DESC
1266 ,TABLE1.INTERCOMPANY_SEGMENT_DESC INTERCOMPANY_SEGMENT_DESC
1267 ,TABLE1.SEGMENT1 SEGMENT1
1268 ,TABLE1.SEGMENT2 SEGMENT2
1269 ,TABLE1.SEGMENT3 SEGMENT3
1270 ,TABLE1.SEGMENT4 SEGMENT4
1271 ,TABLE1.SEGMENT5 SEGMENT5
1272 ,TABLE1.SEGMENT6 SEGMENT6
1273 ,TABLE1.SEGMENT7 SEGMENT7
1274 ,TABLE1.SEGMENT8 SEGMENT8
1275 ,TABLE1.SEGMENT9 SEGMENT9
1276 ,TABLE1.SEGMENT10 SEGMENT10
1277 ,TABLE1.SEGMENT11 SEGMENT11
1278 ,TABLE1.SEGMENT12 SEGMENT12
1279 ,TABLE1.SEGMENT13 SEGMENT13
1280 ,TABLE1.SEGMENT14 SEGMENT14
1281 ,TABLE1.SEGMENT15 SEGMENT15
1282 ,TABLE1.SEGMENT16 SEGMENT16
1283 ,TABLE1.SEGMENT17 SEGMENT17
1284 ,TABLE1.SEGMENT18 SEGMENT18
1285 ,TABLE1.SEGMENT19 SEGMENT19
1286 ,TABLE1.SEGMENT20 SEGMENT20
1287 ,TABLE1.SEGMENT21 SEGMENT21
1288 ,TABLE1.SEGMENT22 SEGMENT22
1289 ,TABLE1.SEGMENT23 SEGMENT23
1290 ,TABLE1.SEGMENT24 SEGMENT24
1291 ,TABLE1.SEGMENT25 SEGMENT25
1292 ,TABLE1.SEGMENT26 SEGMENT26
1293 ,TABLE1.SEGMENT27 SEGMENT27
1294 ,TABLE1.SEGMENT28 SEGMENT28
1295 ,TABLE1.SEGMENT29 SEGMENT29
1296 ,TABLE1.SEGMENT30 SEGMENT30
1297 FROM
1298 (SELECT gll.ledger_id LEDGER_ID
1299 ,gll.short_name LEDGER_SHORT_NAME
1300 ,gll.description LEDGER_DESCRIPTION
1301 ,gll.NAME LEDGER_NAME
1302 ,gll.currency_code LEDGER_CURRENCY
1303 ,xcb.party_type_code PARTY_TYPE_CODE
1304 $party_col$
1305 ,hzp.jgzz_fiscal_code PARTY_TYPE_TAXPAYER_ID
1306 ,hzp.tax_reference PARTY_TAX_REGISTRATION_NUMBER
1307 ,hzp.address1 PARTY_ADDRESS_1
1308 ,hzp.address2 PARTY_ADDRESS_2
1309 ,hzp.address3 PARTY_ADDRESS_3
1310 ,hzp.address4 PARTY_ADDRESS_4
1311 ,hzp.city PARTY_CITY
1312 ,hzp.postal_code PARTY_ZIP_CODE
1313 ,hzp.state PARTY_STATE
1314 ,hzp.province PARTY_PROVINCE
1315 ,hzp.country PARTY_COUNTRY
1316 ,hzp.county PARTY_COUNTY
1317 ,hps.party_site_name PARTY_SITE_NAME
1318 ,hzl.address1 PARTY_SITE_ADDRESS_LINE_1
1319 ,hzl.address2 PARTY_SITE_ADDRESS_LINE_2
1320 ,hzl.address3 PARTY_SITE_ADDRESS_LINE_3
1321 ,hzl.address4 PARTY_SITE_ADDRESS_LINE_4
1322 ,hzl.city PARTY_SITE_CITY
1323 ,hzl.postal_code PARTY_SITE_ZIP_CODE
1324 ,hzl.state PARTY_SITE_STATE
1325 ,hzl.province PARTY_SITE_PROVINCE
1326 ,hzl.country PARTY_SITE_COUNTRY
1327 ,hzl.county PARTY_SITE_COUNTY
1328 ,xcb.application_id APPLICATION_ID
1329 ,fap.application_name APPLICATION_NAME
1330 ,gjst.user_je_source_name JE_SOURCE_NAME
1331 ,gls.period_year PERIOD_YEAR
1332 ,gls.period_num PERIOD_NUMBER
1333 ,xcb.period_name PERIOD_NAME
1334 ,trunc(gls.START_DATE) PERIOD_START_DATE
1335 ,trunc(gls.end_date) PERIOD_END_DATE
1336 ,NVL(xcb.beginning_balance_dr,0) BEGIN_BALANCE_DR
1337 ,NVL(xcb.beginning_balance_cr,0) BEGIN_BALANCE_CR
1338 ,NVL(xcb.period_balance_dr,0) PERIOD_NET_DR
1339 ,NVL(xcb.period_balance_cr,0) PERIOD_NET_CR
1340 ,NVL(xcb.draft_beginning_balance_dr,0) BEGIN_DRAFT_BALANCE_DR
1341 ,NVL(xcb.draft_beginning_balance_cr,0) BEGIN_DRAFT_BALANCE_CR
1342 ,NVL(xcb.period_draft_balance_dr,0) PERIOD_DRAFT_NET_DR
1343 ,NVL(xcb.period_draft_balance_cr,0) PERIOD_DRAFT_NET_CR
1344 ,xcb.code_combination_id CODE_COMBINATION_ID
1345 ,gcck.concatenated_segments ACCOUNTING_CODE_COMBINATION
1346 ,xla_report_utility_pkg.get_ccid_desc
1347 (gll.chart_of_accounts_id
1348 ,xcb.code_combination_id) CODE_COMBINATION_DESCRIPTION
1349 $seg_desc_column$
1350 ,gcck.segment1 SEGMENT1
1351 ,gcck.segment2 SEGMENT2
1352 ,gcck.segment3 SEGMENT3
1353 ,gcck.segment4 SEGMENT4
1354 ,gcck.segment5 SEGMENT5
1355 ,gcck.segment6 SEGMENT6
1356 ,gcck.segment7 SEGMENT7
1357 ,gcck.segment8 SEGMENT8
1358 ,gcck.segment9 SEGMENT9
1359 ,gcck.segment10 SEGMENT10
1360 ,gcck.segment11 SEGMENT11
1361 ,gcck.segment12 SEGMENT12
1362 ,gcck.segment13 SEGMENT13
1363 ,gcck.segment14 SEGMENT14
1364 ,gcck.segment15 SEGMENT15
1365 ,gcck.segment16 SEGMENT16
1366 ,gcck.segment17 SEGMENT17
1367 ,gcck.segment18 SEGMENT18
1368 ,gcck.segment19 SEGMENT19
1369 ,gcck.segment20 SEGMENT20
1370 ,gcck.segment21 SEGMENT21
1371 ,gcck.segment22 SEGMENT22
1372 ,gcck.segment23 SEGMENT23
1373 ,gcck.segment24 SEGMENT24
1374 ,gcck.segment25 SEGMENT25
1375 ,gcck.segment26 SEGMENT26
1376 ,gcck.segment27 SEGMENT27
1377 ,gcck.segment28 SEGMENT28
1378 ,gcck.segment29 SEGMENT29
1379 ,gcck.segment30 SEGMENT30
1380 FROM gl_ledgers gll
1381 ,xla_control_balances xcb
1382 ,gl_period_statuses gls
1383 ,gl_code_combinations_kfv gcck
1384 ,hz_parties hzp
1385 ,hz_party_sites hps
1386 ,hz_locations hzl
1387 ,fnd_application_tl fap
1388 ,xla_subledgers xls
1389 ,gl_je_sources_tl gjst
1390 $party_tab$
1391 $seg_desc_from$
1392 $l_ledger_set_from$
1393 WHERE gls.ledger_id = gll.ledger_id
1394 AND gls.application_id = 101
1395 AND gls.effective_period_num BETWEEN :P_START_PERIOD_NUM AND :P_END_PERIOD_NUM
1396 AND xcb.ledger_id = gll.ledger_id
1397 AND xcb.application_id = :G_JE_SOURCE_APPLICATION_ID
1398 AND xcb.period_name = gls.period_name
1399 AND gcck.code_combination_id = xcb.code_combination_id
1400 AND hzl.location_id(+) = hps.location_id
1401 AND fap.application_id = xcb.application_id
1402 AND fap.LANGUAGE = :P_LANG
1403 AND xls.application_id = xcb.application_id
1404 AND gjst.je_source_name = xls.je_source_name
1405 AND gjst.LANGUAGE = :P_LANG
1406 $other_filter$
1407 $p_party_join$
1408 $seg_desc_join$
1409 $l_ledger_set_where$
1410 $account_range$) TABLE1
1411 $legal_entity_from$
1412 WHERE 1 = 1
1413 $legal_entity_join$
1414 )' ;
1415
1416 l_balance_query := REPLACE(l_balance_query
1417 ,'$legal_entity_columns$'
1418 ,p_legal_ent_col);
1419 l_balance_query := REPLACE(l_balance_query
1420 ,'$party_col$'
1421 ,p_party_col);
1422 l_balance_query := REPLACE(l_balance_query
1423 ,'$seg_desc_column$'
1424 ,p_qualifier_segment);
1425 l_balance_query := REPLACE(l_balance_query
1426 ,'$party_tab$'
1427 ,p_party_tab);
1428 l_balance_query := REPLACE(l_balance_query
1429 ,'$legal_entity_from$'
1430 ,p_legal_ent_from);
1431 l_balance_query := REPLACE(l_balance_query
1432 ,'$seg_desc_from$'
1433 ,p_seg_desc_from);
1434 l_balance_query := REPLACE(l_balance_query
1435 ,'$other_filter$'
1436 ,l_other_filter);
1437 l_balance_query := REPLACE(l_balance_query
1438 ,'$p_party_join$'
1439 ,p_party_join);
1440 l_balance_query := REPLACE(l_balance_query
1441 ,'$legal_entity_join$'
1442 ,p_legal_ent_join);
1443 l_balance_query := REPLACE(l_balance_query
1444 ,'$seg_desc_join$'
1445 ,p_seg_desc_join);
1446 l_balance_query := REPLACE(l_balance_query
1447 ,'$l_ledger_set_from$'
1448 ,l_ledger_set_from);
1449 l_balance_query := REPLACE(l_balance_query
1450 ,'$l_ledger_set_where$'
1451 ,l_ledger_set_where);
1452
1453 IF p_accounting_flexfield_from IS NOT NULL THEN
1454 l_flex_range_where :=
1455 get_flex_range_where
1456 (p_coa_id => l_coa_id
1457 ,p_accounting_flexfield_from => p_accounting_flexfield_from
1458 ,p_accounting_flexfield_to => p_accounting_flexfield_to );
1459
1460 l_balance_query := REPLACE (l_balance_query
1461 ,'$account_range$'
1462 ,' AND '||l_flex_range_where);
1463 ELSE
1464 l_balance_query := REPLACE(l_balance_query, '$account_range$', '');
1465 END IF;
1466
1467 IF(P_JE_SOURCE_NAME='ALL') THEN
1468 FORALL i IN 1..l_responsibility_ids.count
1469 execute immediate l_balance_query
1470 using p_start_period_num
1471 ,p_end_period_num
1472 ,l_responsibility_ids(i)
1473 ,p_lang
1474 ,p_lang
1475 ,p_ledger_id;
1476 ELSE
1477 execute immediate l_balance_query
1478 using p_start_period_num
1479 ,p_end_period_num
1480 ,g_je_source_application_id
1481 ,p_lang
1482 ,p_lang
1483 ,p_ledger_id;
1484 END IF;
1485
1486 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1487 trace
1488 (p_msg => 'END of beforeReport'
1489 ,p_level => C_LEVEL_PROCEDURE
1490 ,p_module => l_log_module);
1491 END IF;
1492
1493 RETURN TRUE;
1494
1495 EXCEPTION
1496 WHEN OTHERS THEN
1497 xla_exceptions_pkg.raise_message
1498 (p_location => 'xla_tp_balance_rpt_pkg.beforeReport ');
1499 END beforeReport;
1500
1501
1502 --=============================================================================
1503 -- *********** Initialization routine **********
1504 --=============================================================================
1505
1506 --=============================================================================
1507 --
1508 --
1509 --
1510 --
1511 --
1512 --
1513 --
1514 --
1515 --
1516 --
1517 -- Following code is executed when the package body is referenced for the first
1518 -- time
1519 --
1520 --
1521 --
1522 --
1523 --
1524 --
1525 --
1526 --
1527 --
1528 --
1529 --
1530 --
1531 --=============================================================================
1532
1533 BEGIN
1534 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1535 g_log_enabled := fnd_log.test
1536 (log_level => g_log_level
1537 ,MODULE => C_DEFAULT_MODULE);
1538
1539 IF NOT g_log_enabled THEN
1540 g_log_level := C_LEVEL_LOG_DISABLED;
1541 END IF;
1542
1543
1544 END XLA_TP_BALANCE_RPT_PKG;
1545
1546
1547