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