[Home] [Help]
PACKAGE BODY: APPS.JA_JP_TP_BAL_CTRL_RPT_PKG
Source
1 PACKAGE BODY JA_JP_TP_BAL_CTRL_RPT_PKG AS
2 -- $Header: jajptpbcb.pls 120.3 2010/12/21 10:43:29 spasupun noship $
3 -------------------------------------------------------------------------------
4 -- constant for getting leagal entity information
5 -------------------------------------------------------------------------------
6 C_NULL_LEGAL_ENT_COL CONSTANT VARCHAR2(4000) :=
7 ' ,NULL LEGAL_ENTITY_ID
8 ,NULL LEGAL_ENTITY_NAME
9 ,NULL LE_ADDRESS_LINE_1
10 ,NULL LE_ADDRESS_LINE_2
11 ,NULL LE_ADDRESS_LINE_3
12 ,NULL LE_CITY
13 ,NULL LE_REGION_1
14 ,NULL LE_REGION_2
15 ,NULL LE_REGION_3
16 ,NULL LE_POSTAL_CODE
17 ,NULL LE_COUNTRY
18 ,NULL LE_REGISTRATION_NUMBER
19 ,NULL LE_REGISTRATION_EFFECTIVE_FROM
20 ,NULL LE_ACTIVITY_CODE
21 ,NULL LE_SUB_ACTIVITY_CODE
22 ,NULL LE_CONTACT_NAME
23 ,NULL LE_CONTACT_PHONE_NUMBER';
24
25 C_LEGAL_ENT_COL CONSTANT VARCHAR2(4000) :=
26 ' ,fiv.legal_entity_id LEGAL_ENTITY_ID
27 ,fiv.NAME LEGAL_ENTITY_NAME
28 ,fiv.ADDRESS_LINE_1 LE_ADDRESS_LINE_1
29 ,fiv.ADDRESS_LINE_2 LE_ADDRESS_LINE_2
30 ,fiv.ADDRESS_LINE_3 LE_ADDRESS_LINE_3
31 ,fiv.TOWN_OR_CITY LE_CITY
32 ,fiv.REGION_1 LE_REGION_1
33 ,fiv.REGION_2 LE_REGION_2
34 ,fiv.REGION_3 LE_REGION_3
35 ,fiv.postal_code LE_POSTAL_CODE
36 ,fiv.country LE_COUNTRY
37 ,fiv.registration_number LE_REGISTRATION_NUMBER
38 ,fiv.effective_from LE_REGISTRATION_EFFECTIVE_FROM
39 ,fiv.activity_code LE_ACTIVITY_CODE
40 ,fiv.sub_activity_code LE_SUB_ACTIVITY_CODE
41 ,NULL LE_CONTACT_NAME
42 ,NULL LE_CONTACT_PHONE_NUMBER';
43
44 C_LEGAL_ENT_FROM CONSTANT VARCHAR2(1000) :=
45 ' ,xle_firstparty_information_v fiv
46 ,gl_ledger_le_bsv_specific_v gle';
47
48 C_LEGAL_ENT_JOIN CONSTANT VARCHAR2(2000) :=
49 ' AND gle.ledger_id(+) = TABLE1.ledger_id
50 AND gle.segment_value(+) = TABLE1.$leg_seg_val$
51 AND fiv.legal_entity_id(+) = gle.legal_entity_id';
52
53 --------------------------------------------------------------------------------
54 -- constant for COMMERCIAL_NUMBER details
55 --------------------------------------------------------------------------------
56 C_COMMERCIAL_QUERY VARCHAR2(8000) :=
57 'SELECT nvl(xler.registration_number,0) LEGAL_COMMERCIAL_NUMBER
58 FROM XLE_REGISTRATIONS_V xler
59 WHERE legislative_category = ''COMMERCIAL_LAW''
60 AND legal_entity_id = :P_LEGAL_ENTITY_ID';
61
62 C_COMMERCIAL_NULL_QUERY VARCHAR2(8000) :=
63 'select NULL LEGAL_COMMERCIAL_NUMBER from dual where 1>2';
64
65 --------------------------------------------------------------------------------
66 -- constant for VAT_REGISTRATION details
67 --------------------------------------------------------------------------------
68 C_VAT_REGISTRATION_QUERY VARCHAR2(8000) :=
69 'SELECT zptp.REP_REGISTRATION_NUMBER LEGAL_VAT_REGISTRATION_NUMBER
70 FROM ZX_PARTY_TAX_PROFILE zptp ,XLE_ETB_PROFILES xetbp
71 WHERE zptp.PARTY_TYPE_CODE = ''LEGAL_ESTABLISHMENT''
72 AND xetbp.party_id=zptp.party_id
73 AND xetbp.MAIN_ESTABLISHMENT_FLAG = ''Y''
74 AND xetbp.LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID' ;
75
76 C_VAT_REGISTRATION_NULL_QUERY VARCHAR2(8000) :=
77 'select NULL LEGAL_VAT_REGISTRATION_NUMBER from dual where 1>2';
78
79
80 C_QUALIFIED_SEGMENT CONSTANT VARCHAR2(1000) :=
81 ' ,$alias_balancing_segment$ BALANCING_SEGMENT
82 ,$alias_account_segment$ NATURAL_ACCOUNT_SEGMENT
83 ,$alias_costcenter_segment$ COST_CENTER_SEGMENT
84 ,$alias_management_segment$ MANAGEMENT_SEGMENT
85 ,$alias_intercompany_segment$ INTERCOMPANY_SEGMENT
86 $seg_desc_column$ ';
87
88 C_NULL_PARTY_COLS CONSTANT VARCHAR2(1000) :=
89 ' ,NULL PARTY_ID
90 ,NULL PARTY_NUMBER
91 ,NULL PARTY_NAME
92 ,NULL PARTY_SITE_ID
93 ,NULL PARTY_SITE_NUMBER
94 ,NULL PARTY_SITE_TAX_REGS_NUMBER';
95
96 -------------------------------------------------------------------------------
97 -- constant for User Transaction Identifiers name and values
98 -------------------------------------------------------------------------------
99
100
101
102 --=============================================================================
103 -- ************** forward declarations ******************
104 --=============================================================================
105 --------------------------------------------------------------------------------
106 -- procedure to create the main SQL
107 --------------------------------------------------------------------------------
108 --=============================================================================
109 -- *********** Local Trace Routine **********
110 --=============================================================================
111 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
112 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
113 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
114 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
115 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
116 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
117
118 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
119 C_DEFAULT_MODULE CONSTANT VARCHAR2(240):= 'ja.plsql.ja_tp_balance_rpt_pkg';
120
121 g_log_level NUMBER;
122 g_log_enabled BOOLEAN;
123 g_je_source_application_id VARCHAR2(30);
124
125 PROCEDURE trace
126 (p_msg IN VARCHAR2
127 ,p_level IN NUMBER
128 ,p_module IN VARCHAR2) IS
129 BEGIN
130 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
131 fnd_log.message(p_level, NVL(p_module,C_DEFAULT_MODULE));
132 ELSIF p_level >= g_log_level THEN
133 fnd_log.string(p_level, NVL(p_module,C_DEFAULT_MODULE), p_msg);
134 END IF;
135
136 EXCEPTION
137 WHEN OTHERS THEN
138 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
139 fnd_log.message(p_level, NVL(p_module,C_DEFAULT_MODULE));
140 END IF;
141 END trace;
142
143 /*======================================================================+
144 | |
145 | Private Function |
146 | |
147 | get_flex_range_where |
148 | |
149 | |
150 | Return where clauses for flexfield ranges |
151 | |
152 +======================================================================*/
153
154 FUNCTION get_flex_range_where
155 (p_coa_id IN NUMBER
156 ,p_accounting_flexfield_from IN VARCHAR2
157 ,p_accounting_flexfield_to IN VARCHAR2) RETURN VARCHAR
158
159 IS
160
161 l_log_module VARCHAR2(240);
162
163 l_where VARCHAR2(32000);
164 l_bind_variables fnd_flex_xml_publisher_apis.bind_variables;
165 l_numof_bind_variables NUMBER;
166 l_segment_name VARCHAR2(30);
167 l_segment_value VARCHAR2(1000);
168 l_data_type VARCHAR2(30);
169
170 BEGIN
171
172 IF g_log_enabled THEN
173 l_log_module := C_DEFAULT_MODULE||'.get_flex_range_where';
174 END IF;
175 --
176 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
177 trace(p_msg=>'BEGIN of get_flex_range_where',p_level=>C_LEVEL_PROCEDURE,p_module=> l_log_module);
178 END IF;
179
180 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
181 trace(p_msg=>'p_coa_id = '||to_char(p_coa_id),p_level => C_LEVEL_STATEMENT,p_module=> l_log_module );
182 trace(p_msg=>'p_accounting_flexfield_from = '||to_char(p_accounting_flexfield_from ),p_level => C_LEVEL_STATEMENT,p_module=> l_log_module );
183 trace(p_msg=>'p_accounting_flexfield_to = '||to_char(p_accounting_flexfield_to),p_level => C_LEVEL_STATEMENT,p_module=> l_log_module );
184 END IF;
185 --
186 -- e.g. l_where stores the following:
187 -- gcck.SEGMENT1 BETWEEN :FLEX_PARM1 AND :FLEX_PARM2
188 -- AND gcck.SEGMENT2 BETWEEN :FLEX_PARM3 AND :FLEX_PARM4 ...
189 --
190 fnd_flex_xml_publisher_apis.kff_where
191 (p_lexical_name => 'FLEX_PARM'
192 ,p_application_short_name => 'SQLGL'
193 ,p_id_flex_code => 'GL#'
194 ,p_id_flex_num => p_coa_id
195 ,p_code_combination_table_alias => 'gcck'
196 ,p_segments => 'ALL'
197 ,p_operator => 'BETWEEN'
198 ,p_operand1 => p_accounting_flexfield_from
199 ,p_operand2 => p_accounting_flexfield_to
200 ,x_where_expression => l_where
201 ,x_numof_bind_variables => l_numof_bind_variables
202 ,x_bind_variables => l_bind_variables);
203
204 FOR i IN l_bind_variables.FIRST .. l_bind_variables.LAST LOOP
205
206 l_segment_name := l_bind_variables(i).name;
207 l_data_type := l_bind_variables(i).data_type;
208
209 IF (l_data_type='VARCHAR2') THEN
210
211 l_segment_value := '''' || l_bind_variables(i).varchar2_value || '''';
212
213 ELSIF (l_data_type='NUMBER') THEN
214
215 l_segment_value := l_bind_variables(i).canonical_value;
216
217 ELSIF (l_data_type='DATE') THEN
218
219 l_segment_value := '''' || TO_CHAR(l_bind_variables(i).date_value
220 ,'yyyy-mm-dd HH24:MI:SS') || '''';
221
222 END IF;
223
224 --
225 -- Use REGEXP_REPLACE instead of REPLACE not to replace
226 -- string 'SEGMENT1' in 'SEGMENT10'.
227 -- REGEXP_REPLACE replaces the first occurent of a segment name
228 -- e.g.
229 -- BETWEEN :FLEX_PARM9 AND :FLEX_PARM10
230 -- =>
231 -- BETWEEN '000' AND '100'
232 --
233 l_where := REGEXP_REPLACE
234 (l_where
235 ,':' || l_segment_name
236 ,l_segment_value
237 ,1 -- Position
238 ,1 -- The first occurence
239 , 'c' -- Case sensitive
240 );
241
242 END LOOP ;
243
244 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
245 trace(p_msg=>'END of get_flex_range_where',p_level=>C_LEVEL_PROCEDURE,p_module=> l_log_module);
246 END IF;
247
248 RETURN l_where;
249
250 EXCEPTION
251 WHEN OTHERS THEN
252 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in Procedure get_flex_range_where');
253 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
254 trace(p_msg=>'Error in Procedure get_flex_range_where',p_level=>C_LEVEL_PROCEDURE,p_module=> l_log_module);
255 END IF;
256 RAISE;
257 END get_flex_range_where;
258 --=============================================================================
259 -- *********** public procedures and functions **********
260 --=============================================================================
261 --=============================================================================
262 --
263 --
264 --
265 --
266 --
267 --
268 --
269 --
270 --
271 --
272 -- Following are public routines
273 --
274 -- 1. beforeReport
275 --
276 --
277 --
278 --
279 --
280 --
281 --
282 --
283 --
284 --
285 --
286 --=============================================================================
287 --=============================================================================
288 --
289 --
290 --
291 --=============================================================================
292 FUNCTION beforeReport RETURN BOOLEAN IS
293
294 l_source_application_id NUMBER;
295 l_start_period_num NUMBER;
296 l_end_period_num NUMBER;
297 l_start_date DATE;
298 l_end_date DATE;
299 l_select_str VARCHAR2(4000);
300 l_from_str VARCHAR2(240);
301 l_where_str VARCHAR2(4000);
302 l_lang VARCHAR2(80);
303 l_count NUMBER;
304 l_ledger_id NUMBER;
305 l_coa_id NUMBER;
306 l_object_type VARCHAR2(30);
307 l_balancing_segment VARCHAR2(80);
308 l_account_segment VARCHAR2(80);
309 l_costcenter_segment VARCHAR2(80);
310 l_management_segment VARCHAR2(80);
311 l_intercompany_segment VARCHAR2(80);
312 l_alias_balancing_segment VARCHAR2(80);
313 l_alias_account_segment VARCHAR2(80);
314 l_alias_costcenter_segment VARCHAR2(80);
315 l_alias_management_segment VARCHAR2(80);
316 l_alias_intercompany_segment VARCHAR2(80);
317 l_seg_desc_column VARCHAR2(2000);
318 l_seg_desc_from VARCHAR2(1000);
319 l_seg_desc_join VARCHAR2(1000);
320 l_fnd_flex_hint VARCHAR2(500);
321 l_other_filter VARCHAR2(2000);
322 l_log_module VARCHAR2(240);
323 l_insert_query VARCHAR2(4000);
324 l_balance_query VARCHAR2(32000);
325 l_flex_range_where VARCHAR2(32000);
326 l_ledger_set_from VARCHAR2(1000) := ' ';
327 l_ledger_set_where VARCHAR2(1000) := ' ';
328 type t_array_app_id is table of NUMBER index by binary_integer;
329
330 l_responsibility_ids t_array_app_id;
331
332 i number;
333 l_temp number;
334
335
336
337 CURSOR c_alc_ledger_check(l_ledger_id gl_ledgers.ledger_id%TYPE) IS
338 SELECT primary_ledger_id
339 FROM gl_ledger_relationships
340 WHERE target_ledger_id = l_ledger_id
341 AND relationship_type_code = 'SUBLEDGER'
342 AND target_ledger_category_code = 'ALC'
343 AND application_id =101;
344
345 l_primary_ledger_id gl_ledgers.ledger_id%TYPE;
346
347
348 BEGIN
349
350 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Before Report Trigger Start');
351 IF g_log_enabled THEN
352 l_log_module := C_DEFAULT_MODULE||'.beforeReport';
353 END IF;
354 --
355 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
356 trace(p_msg=>'BEGIN of beforeReport',p_level=>C_LEVEL_PROCEDURE,p_module=>l_log_module);
357 END IF;
358
359 --
360 -- default values
361 --
362
363 IF p_je_source_name = '#ALL#' THEN
364 p_je_source_name := 'ALL';
365 END IF;
366
367 BEGIN
368 SELECT application_id
369 INTO g_je_source_application_id
370 FROM xla_subledgers
371 WHERE je_source_name = p_je_source_name;
372 EXCEPTION
373 WHEN NO_DATA_FOUND THEN
374 g_je_source_application_id := NULL;
375 END;
376
377 -- following will set the right transaction security.
378
379 xla_security_pkg.set_security_context(g_je_source_application_id);
380
381 -- capture translated posting status
382
383 SELECT ''''||meaning||'''' INTO G_POSTING_FLAG
384 FROM FND_LOOKUPS
385 WHERE LOOKUP_TYPE='JGZZ_SRS_SLR_POST_STAT'
386 AND lookup_code = 'Y';
387
388 --
389 -- Identifying ledger as Ledger or Ledger Set and get value for language
390 --
391
392 SELECT object_type_code, USERENV('LANG')
393 INTO l_object_type, l_lang
394 FROM gl_ledgers
395 WHERE ledger_id = p_ledger_id;
396
397
398 IF(P_JE_SOURCE_NAME='ALL') THEN
399 select application_id
400 bulk COLLECT into l_responsibility_ids
401 from xla_subledgers
402 WHERE control_account_type_code = 'Y' or control_account_type_code = P_PARTY_TYPE;
403 END IF;
404
405 --
406 -- build join condition based on if ledger passed is a ledger set or a ledger
407 --
408 IF l_object_type = 'S' THEN
409 l_ledger_set_from := l_ledger_set_from ||' ,gl_ledger_set_assignments glst ';
410 l_ledger_set_where := l_ledger_set_where||' AND glst.ledger_set_id = :P_LEDGER_ID '||
411 ' AND gll.ledger_id = glst.ledger_id ';
412
413 SELECT ledger_id
414 INTO l_ledger_id
415 FROM gl_ledger_set_assignments
416 WHERE ledger_set_id = p_ledger_id
417 AND ROWNUM = 1;
418 ELSE
419 l_ledger_set_where := l_ledger_set_where||' AND gll.ledger_id = :P_LEDGER_ID ';
420 l_ledger_id := p_ledger_id;
421 END IF;
422
423 SELECT effective_period_num
424 ,START_DATE
425 INTO l_start_period_num
426 ,l_start_date
427 FROM gl_period_statuses
428 WHERE application_id = 101
429 AND ledger_id = l_ledger_id
430 AND period_name = p_period_from;
431
432 SELECT effective_period_num
433 ,end_date
434 INTO l_end_period_num
435 ,l_end_date
436 FROM gl_period_statuses
437 WHERE application_id = 101
438 AND ledger_id = l_ledger_id
439 AND period_name = p_period_to;
440
441 --
442 -- Third party information based on application_id
443 --
444
445
446 IF p_party_type = 'SUPPLIER' THEN
447 p_party_col := ',aps.vendor_id PARTY_ID '
448 ||',aps.segment1 PARTY_NUMBER'
449 ||',aps.vendor_name PARTY_NAME'
450 ||',NVL(apss.vendor_site_id,-999) PARTY_SITE_ID'
451 ||',hps.party_site_number PARTY_SITE_NUMBER'
452 ||',NULL PARTY_SITE_TAX_REGS_NUMBER';
453
454 p_party_tab := ' ,ap_suppliers aps
455 ,ap_supplier_sites_all apss ';
456
457 p_party_join := 'AND aps.vendor_id = xcb.party_id '
458 ||'AND hzp.party_id = aps.party_id '
459 ||'AND apss.vendor_site_id(+) = xcb.party_site_id '
460 ||'AND hps.party_site_id(+) = apss.party_site_id';
461
462 ELSIF p_party_type = 'CUSTOMER' THEN
463 p_party_col := ',hca.cust_account_id PARTY_ID'
464 ||',hca.account_number PARTY_NUMBER'
465 ||',hzp.party_name PARTY_NAME '
466 ||',NVL(hzcu.site_use_id, -999) PARTY_SITE_ID'
467 ||',hps.party_site_number PARTY_SITE_NUMBER'
468 ||',hzcu.tax_reference PARTY_SITE_TAX_REGS_NUMBER';
469
470 p_party_tab := ',hz_cust_accounts hca '
471 ||',hz_cust_acct_sites_all hcas'
472 ||',hz_cust_site_uses_all hzcu';
473
474 p_party_join :=' AND hzp.party_id = hca.party_id '
475 ||' AND hca.cust_account_id = xcb.party_id '
476 ||' AND hzcu.site_use_id(+) = xcb.party_site_id'
477 ||' AND hcas.cust_acct_site_id(+) = hzcu.cust_acct_site_id'
478 ||' AND hps.party_site_id(+) = hcas.party_site_id ';
479 ELSE
480 p_party_col := C_NULL_PARTY_COLS;
481 END IF;
482
483 p_commercial_query := C_COMMERCIAL_QUERY;
484 p_vat_registration_query := C_VAT_REGISTRATION_QUERY;
485
486 --
487 -- Qualified segments
488 --
489 p_qualifier_segment := C_QUALIFIED_SEGMENT;
490
491 --
492 -- get COA for the ledger/ledger set
493 --
494
495 SELECT chart_of_accounts_id
496 INTO l_coa_id
497 FROM gl_ledgers
498 WHERE ledger_id = p_ledger_id;
499
500 ----------------------------------------------------------------------------
501 -- get qualifier segments for the COA
502 ----------------------------------------------------------------------------
503 xla_report_utility_pkg.get_acct_qualifier_segs
504 (p_coa_id => l_coa_id
505 ,p_balance_segment => l_balancing_segment
506 ,p_account_segment => l_account_segment
507 ,p_cost_center_segment => l_costcenter_segment
508 ,p_management_segment => l_management_segment
509 ,p_intercompany_segment => l_intercompany_segment);
510
511 --
512 -- attach table alias to the column names
513 --
514
515 IF l_balancing_segment = 'NULL' THEN
516 l_alias_balancing_segment := 'NULL';
517 ELSE
518 l_alias_balancing_segment := 'gcck.'||l_balancing_segment;
519 P_ALIAS_BALANCING_SEGMENT := 'gcck.'||l_balancing_segment;
520 END IF;
521
522 IF l_account_segment = 'NULL' THEN
523 l_alias_account_segment := 'NULL';
524 ELSE
525 l_alias_account_segment := 'gcck.'||l_account_segment;
526 P_ALIAS_ACCOUNT_SEGMENT := 'gcck.'||l_account_segment;
527 END IF;
528
529 IF l_costcenter_segment = 'NULL' THEN
530 l_alias_costcenter_segment := 'NULL';
531 ELSE
532 l_alias_costcenter_segment := 'gcck.'||l_costcenter_segment;
533 END IF;
534
535 IF l_management_segment = 'NULL' THEN
536 l_alias_management_segment := 'NULL';
537 ELSE
538 l_alias_management_segment := 'gcck.'||l_management_segment;
539 END IF;
540
541 IF l_intercompany_segment = 'NULL' THEN
542 l_alias_intercompany_segment := 'NULL';
543 ELSE
544 l_alias_intercompany_segment := 'gcck.'||l_intercompany_segment;
545 END IF;
546
547 --
548 -- replace placeholders for the qualified segemnts
549 --
550
551 p_qualifier_segment:= REPLACE(p_qualifier_segment
552 ,'$alias_balancing_segment$'
553 ,l_alias_balancing_segment);
554
555 p_qualifier_segment := REPLACE(p_qualifier_segment
556 ,'$alias_account_segment$'
557 ,l_alias_account_segment);
558
559 p_qualifier_segment := REPLACE(p_qualifier_segment
560 ,'$alias_costcenter_segment$'
561 ,l_alias_costcenter_segment);
562
563 p_qualifier_segment := REPLACE(p_qualifier_segment
564 ,'$alias_management_segment$'
565 ,l_alias_management_segment);
566
567 p_qualifier_segment := REPLACE(p_qualifier_segment
568 ,'$alias_intercompany_segment$'
569 ,l_alias_intercompany_segment);
570
571 xla_report_utility_pkg.get_segment_info
572 (p_coa_id => l_coa_id
573 ,p_balancing_segment => l_balancing_segment
574 ,p_account_segment => l_account_segment
575 ,p_costcenter_segment => l_costcenter_segment
576 ,p_management_segment => l_management_segment
577 ,p_intercompany_segment => l_intercompany_segment
578 ,p_alias_balancing_segment => l_alias_balancing_segment
579 ,p_alias_account_segment => l_alias_account_segment
580 ,p_alias_costcenter_segment => l_alias_costcenter_segment
581 ,p_alias_management_segment => l_alias_management_segment
582 ,p_alias_intercompany_segment=> l_alias_intercompany_segment
583 ,p_seg_desc_column => l_seg_desc_column
584 ,p_seg_desc_from => l_seg_desc_from
585 ,p_seg_desc_join => l_seg_desc_join
586 ,p_hint => l_fnd_flex_hint
587 );
588
589 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
590 TRACE(p_msg => 'seg_desc_column ='||l_seg_desc_column ,p_level => C_LEVEL_STATEMENT ,p_module=> l_log_module);
591 TRACE(p_msg => 'seg_desc_from ='||l_seg_desc_from ,p_level => C_LEVEL_STATEMENT ,p_module=> l_log_module);
592 trace(p_msg => 'seg_desc_join ='||l_seg_desc_join ,p_level => C_LEVEL_STATEMENT ,p_module=> l_log_module);
593 END IF;
594
595 --
596 -- replace placeholders for the qualified segemnts
597 --
598 p_qualifier_segment := REPLACE(p_qualifier_segment
599 ,'$seg_desc_column$'
600 ,l_seg_desc_column);
601
602 p_seg_desc_from := l_seg_desc_from;
603
604 p_seg_desc_join := l_seg_desc_join;
605
606 --
607 -- Legal Entity Information
608 --
609
610 --
611 -- Replace placeholders for Legal entity informationC_LEGAL_ENT_COL
612 --
613 p_legal_ent_col := C_LEGAL_ENT_COL;
614 p_legal_ent_from := C_LEGAL_ENT_FROM;
615 p_legal_ent_join := C_LEGAL_ENT_JOIN;
616
617 p_legal_ent_join := REPLACE(p_legal_ent_join,'$leg_seg_val$',l_balancing_segment);
618
619
620 -- Check whether the ledger is an ALC ledger if yes change
621 -- the join condition of the ledger with the primary ledger.
622 -- As for ALC ledger there is no record in gl_ledger_le_bsv_specific_v table.
623 -- gl_ledger_le_bsv_specific_v has a record for primary and secondary ledger for a
624 -- given legal entity
625
626 OPEN c_alc_ledger_check(l_ledger_id);
627 FETCH c_alc_ledger_check INTO l_primary_ledger_id;
628 CLOSE c_alc_ledger_check;
629
630
631 IF l_primary_ledger_id IS NOT NULL THEN --indicates its a ALC ledger
632
633 -- change the join condition. Join with primary ledger obtained from the cursor.
634
635 p_legal_ent_join := REPLACE(p_legal_ent_join, 'TABLE1.ledger_id', l_primary_ledger_id);
636 p_legal_ent_join := p_legal_ent_join ||
637 ' AND gle.legal_entity_id = '||p_legal_entity_id;
638
639 ELSE
640 p_legal_ent_join := p_legal_ent_join ||
641 ' AND gle.legal_entity_id = '||p_legal_entity_id;
642
643 END IF;
644
645 -- END IF;
646
647 -- <condition for party type>
648 --
649 IF p_party_type IS NOT NULL THEN
650 IF p_party_type = 'CUSTOMER' THEN
651 l_other_filter := l_other_filter||' AND xcb.party_type_code = ''C''';
652 ELSIF p_party_type = 'SUPPLIER' THEN
653 l_other_filter := l_other_filter||' AND xcb.party_type_code = ''S''';
654 END IF;
655 END IF;
656
657 --
658 -- <condition for party number range>
659 --
660
661 IF p_third_party_number_from IS NOT NULL OR p_third_party_number_to IS NOT NULL THEN
662 IF p_party_type = 'CUSTOMER' THEN
663
664 IF p_third_party_number_from IS NOT NULL AND p_third_party_number_to IS NOT NULL THEN
665 l_other_filter :=
666 l_other_filter||' AND hca.account_number BETWEEN '||p_third_party_number_from||
667 ' AND '||p_third_party_number_to||' ';
668 END IF;
669
670 IF p_third_party_number_from IS NOT NULL AND p_third_party_number_to IS NULL THEN
671 l_other_filter :=
672 l_other_filter||' AND hca.account_number >= '||p_third_party_number_from;
673 END IF;
674
675 IF p_third_party_number_from IS NULL AND p_third_party_number_to IS NOT NULL THEN
676 l_other_filter :=
677 l_other_filter||' AND hca.account_number <= '||p_third_party_number_to;
678 END IF;
679
680 ELSIF p_party_type = 'SUPPLIER' THEN
681
682 IF p_third_party_number_from IS NOT NULL AND p_third_party_number_to IS NOT NULL THEN
683 l_other_filter :=
684 l_other_filter||' AND aps.segment1 BETWEEN '||p_third_party_number_from||
685 ' AND '||p_third_party_number_to||' ';
686 END IF;
687
688 IF p_third_party_number_from IS NOT NULL AND p_third_party_number_to IS NULL THEN
689 l_other_filter :=
690 l_other_filter||' AND aps.segment1 >= '||p_third_party_number_from;
691 END IF;
692
693 IF p_third_party_number_from IS NULL AND p_third_party_number_to IS NOT NULL THEN
694 l_other_filter :=
695 l_other_filter||' AND aps.segment1 <= '||p_third_party_number_to;
696 END IF;
697
698 END IF;
699 END IF;
700 --
701 -- <condition for party name range>
702 --
703
704 IF p_third_party_name_from IS NOT NULL OR p_third_party_name_to IS NOT NULL THEN
705 IF p_party_type = 'CUSTOMER' THEN
706 l_other_filter :=
707 l_other_filter||' AND hzp.party_name BETWEEN NVL('''||p_third_party_name_from||
708 ''',hzp.party_name) AND NVL('''||p_third_party_name_to||''',hzp.party_name)';
709 ELSIF p_party_type = 'SUPPLIER' THEN
710 l_other_filter :=
711 l_other_filter||' AND aps.vendor_name BETWEEN NVL('''||p_third_party_name_from||
712 ''',aps.vendor_name) AND NVL('''||p_third_party_name_to||''',aps.vendor_name)';
713 END IF;
714 END IF;
715
716 --
717 -- <condition for party site name range>
718 --
719
720 IF p_third_party_site_name_from IS NOT NULL OR p_third_party_site_name_to IS NOT NULL THEN
721 IF p_party_type = 'CUSTOMER' THEN
722 l_other_filter :=
723 l_other_filter||' AND hps.party_site_name BETWEEN NVL('''||p_third_party_site_name_from||
724 ''',hps.party_site_name) AND NVL('''||p_third_party_site_name_to||''',hps.party_site_name)';
725 ELSIF p_party_type = 'SUPPLIER' THEN
726 l_other_filter :=
727 l_other_filter||' AND hps.party_site_name BETWEEN NVL('''||p_third_party_site_name_from||
728 ''',hps.party_site_name) AND NVL('''||p_third_party_site_name_to||''',hps.party_site_name)';
729 END IF;
730 END IF;
731
732 --
733 -- <condition based on Include Draft activity >
734 --
735 IF p_include_draft_activity = 'Y' THEN
736 p_other_filter :=p_other_filter||' AND aeh.accounting_entry_status_code IN (''D'',''F'')';
737 ELSE
738 p_other_filter :=p_other_filter||' AND aeh.accounting_entry_status_code = ''F'' ';
739 END IF;
740
741 p_lang := l_lang;
742 p_start_period_num := l_start_period_num;
743 p_end_period_num := l_end_period_num;
744 p_start_date := l_start_date;
745 p_end_date := l_end_date;
746
747 l_balance_query :=
748 '
749 INSERT INTO xla_report_balances_gt
750 (ledger_id
751 ,ledger_short_name
752 ,ledger_description
753 ,ledger_name
754 ,ledger_currency
755 ,legal_entity_id
756 ,legal_entity_name
757 ,le_address_line_1
758 ,le_address_line_2
759 ,le_address_line_3
760 ,le_city
761 ,le_region_1
762 ,le_region_2
763 ,le_region_3
764 ,le_postal_code
765 ,le_country
766 ,le_registration_number
767 ,le_registration_effective_from
768 ,le_activity_code
769 ,le_sub_activity_code
770 ,le_contact_name
771 ,le_contact_phone_number
772 ,party_type_code
773 ,party_id
774 ,party_number
775 ,party_name
776 ,party_site_id
777 ,party_site_number
778 ,party_site_tax_regs_number
779 ,party_type_taxpayer_id
780 ,party_tax_registration_number
781 ,party_address_1
782 ,party_address_2
783 ,party_address_3
784 ,party_address_4
785 ,party_city
786 ,party_zip_code
787 ,party_state
788 ,party_province
789 ,party_country
790 ,party_county
791 ,party_site_name
792 ,party_site_address_line_1
793 ,party_site_address_line_2
794 ,party_site_address_line_3
795 ,party_site_address_line_4
796 ,party_site_city
797 ,party_site_zip_code
798 ,party_site_state
799 ,party_site_province
800 ,party_site_country
801 ,party_site_county
802 ,application_id
803 ,application_name
804 ,je_source_name
805 ,period_year
806 ,period_number
807 ,period_name
808 ,period_start_date
809 ,period_end_date
810 ,begin_balance_dr
811 ,begin_balance_cr
812 ,period_net_dr
813 ,period_net_cr
814 ,begin_draft_balance_dr
815 ,begin_draft_balance_cr
816 ,period_draft_net_dr
817 ,period_draft_net_cr
818 ,code_combination_id
819 ,accounting_code_combination
820 ,code_combination_description
821 ,balancing_segment
822 ,natural_account_segment
823 ,cost_center_segment
824 ,management_segment
825 ,intercompany_segment
826 ,balancing_segment_desc
827 ,natural_account_desc
828 ,cost_center_desc
829 ,management_segment_desc
830 ,intercompany_segment_desc
831 ,segment1
832 ,segment2
833 ,segment3
834 ,segment4
835 ,segment5
836 ,segment6
837 ,segment7
838 ,segment8
839 ,segment9
840 ,segment10
841 ,segment11
842 ,segment12
843 ,segment13
844 ,segment14
845 ,segment15
846 ,segment16
847 ,segment17
848 ,segment18
849 ,segment19
850 ,segment20
851 ,segment21
852 ,segment22
853 ,segment23
854 ,segment24
855 ,segment25
856 ,segment26
857 ,segment27
858 ,segment28
859 ,segment29
860 ,segment30)
861 (
862 SELECT TABLE1.LEDGER_ID LEDGER_ID
863 ,TABLE1.LEDGER_SHORT_NAME LEDGER_SHORT_NAME
864 ,TABLE1.LEDGER_DESCRIPTION LEDGER_DESCRIPTION
865 ,TABLE1.LEDGER_NAME LEDGER_NAME
866 ,TABLE1.LEDGER_CURRENCY LEDGER_CURRENCY
867 $legal_entity_columns$
868 ,TABLE1.PARTY_TYPE_CODE PARTY_TYPE_CODE
869 ,TABLE1.PARTY_ID PARTY_ID
870 ,TABLE1.PARTY_NUMBER PARTY_NUMBER
871 ,TABLE1.PARTY_NAME PARTY_NAME
872 ,TABLE1.PARTY_SITE_ID PARTY_SITE_ID
873 ,TABLE1.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
874 ,TABLE1.PARTY_SITE_TAX_REGS_NUMBER PARTY_SITE_TAX_REGS_NUMBER
875 ,TABLE1.PARTY_TYPE_TAXPAYER_ID PARTY_TYPE_TAXPAYER_ID
876 ,TABLE1.PARTY_TAX_REGISTRATION_NUMBER PARTY_TAX_REGISTRATION_NUMBER
877 ,TABLE1.PARTY_ADDRESS_1 PARTY_ADDRESS_1
878 ,TABLE1.PARTY_ADDRESS_2 PARTY_ADDRESS_2
879 ,TABLE1.PARTY_ADDRESS_3 PARTY_ADDRESS_3
880 ,TABLE1.PARTY_ADDRESS_4 PARTY_ADDRESS_4
881 ,TABLE1.PARTY_CITY PARTY_CITY
882 ,TABLE1.PARTY_ZIP_CODE PARTY_ZIP_CODE
883 ,TABLE1.PARTY_STATE PARTY_STATE
884 ,TABLE1.PARTY_PROVINCE PARTY_PROVINCE
885 ,TABLE1.PARTY_COUNTRY PARTY_COUNTRY
886 ,TABLE1.PARTY_COUNTY PARTY_COUNTY
887 ,TABLE1.PARTY_SITE_NAME PARTY_SITE_NAME
888 ,TABLE1.PARTY_SITE_ADDRESS_LINE_1 PARTY_SITE_ADDRESS_LINE_1
889 ,TABLE1.PARTY_SITE_ADDRESS_LINE_2 PARTY_SITE_ADDRESS_LINE_2
890 ,TABLE1.PARTY_SITE_ADDRESS_LINE_3 PARTY_SITE_ADDRESS_LINE_3
891 ,TABLE1.PARTY_SITE_ADDRESS_LINE_4 PARTY_SITE_ADDRESS_LINE_4
892 ,TABLE1.PARTY_SITE_CITY PARTY_SITE_CITY
893 ,TABLE1.PARTY_SITE_ZIP_CODE PARTY_SITE_ZIP_CODE
894 ,TABLE1.PARTY_SITE_STATE PARTY_SITE_STATE
895 ,TABLE1.PARTY_SITE_PROVINCE PARTY_SITE_PROVINCE
896 ,TABLE1.PARTY_SITE_COUNTRY PARTY_SITE_COUNTRY
897 ,TABLE1.PARTY_SITE_COUNTY PARTY_SITE_COUNTY
898 ,TABLE1.APPLICATION_ID APPLICATION_ID
899 ,TABLE1.APPLICATION_NAME APPLICATION_NAME
900 ,TABLE1.JE_SOURCE_NAME JE_SOURCE_NAME
901 ,TABLE1.PERIOD_YEAR PERIOD_YEAR
902 ,TABLE1.PERIOD_NUMBER PERIOD_NUMBER
903 ,TABLE1.PERIOD_NAME PERIOD_NAME
904 ,TABLE1.PERIOD_START_DATE PERIOD_START_DATE
905 ,TABLE1.PERIOD_END_DATE PERIOD_END_DATE
906 ,TABLE1.BEGIN_BALANCE_DR BEGIN_BALANCE_DR
907 ,TABLE1.BEGIN_BALANCE_CR BEGIN_BALANCE_CR
908 ,TABLE1.PERIOD_NET_DR PERIOD_NET_DR
909 ,TABLE1.PERIOD_NET_CR PERIOD_NET_CR
910 ,TABLE1.BEGIN_DRAFT_BALANCE_DR BEGIN_DRAFT_BALANCE_DR
911 ,TABLE1.BEGIN_DRAFT_BALANCE_CR BEGIN_DRAFT_BALANCE_CR
912 ,TABLE1.PERIOD_DRAFT_NET_DR PERIOD_DRAFT_NET_DR
913 ,TABLE1.PERIOD_DRAFT_NET_CR PERIOD_DRAFT_NET_CR
914 ,TABLE1.CODE_COMBINATION_ID CODE_COMBINATION_ID
915 ,TABLE1.ACCOUNTING_CODE_COMBINATION ACCOUNTING_CODE_COMBINATION
916 ,TABLE1.CODE_COMBINATION_DESCRIPTION CODE_COMBINATION_DESCRIPTION
917 ,TABLE1.BALANCING_SEGMENT BALANCING_SEGMENT
918 ,TABLE1.NATURAL_ACCOUNT_SEGMENT NATURAL_ACCOUNT_SEGMENT
919 ,TABLE1.COST_CENTER_SEGMENT COST_CENTER_SEGMENT
920 ,TABLE1.MANAGEMENT_SEGMENT MANAGEMENT_SEGMENT
921 ,TABLE1.INTERCOMPANY_SEGMENT INTERCOMPANY_SEGMENT
922 ,TABLE1.BALANCING_SEGMENT_DESC BALANCING_SEGMENT_DESC
923 ,TABLE1.NATURAL_ACCOUNT_DESC NATURAL_ACCOUNT_DESC
924 ,TABLE1.COST_CENTER_DESC COST_CENTER_DESC
925 ,TABLE1.MANAGEMENT_SEGMENT_DESC MANAGEMENT_SEGMENT_DESC
926 ,TABLE1.INTERCOMPANY_SEGMENT_DESC INTERCOMPANY_SEGMENT_DESC
927 ,TABLE1.SEGMENT1 SEGMENT1
928 ,TABLE1.SEGMENT2 SEGMENT2
929 ,TABLE1.SEGMENT3 SEGMENT3
930 ,TABLE1.SEGMENT4 SEGMENT4
931 ,TABLE1.SEGMENT5 SEGMENT5
932 ,TABLE1.SEGMENT6 SEGMENT6
933 ,TABLE1.SEGMENT7 SEGMENT7
934 ,TABLE1.SEGMENT8 SEGMENT8
935 ,TABLE1.SEGMENT9 SEGMENT9
936 ,TABLE1.SEGMENT10 SEGMENT10
937 ,TABLE1.SEGMENT11 SEGMENT11
938 ,TABLE1.SEGMENT12 SEGMENT12
939 ,TABLE1.SEGMENT13 SEGMENT13
940 ,TABLE1.SEGMENT14 SEGMENT14
941 ,TABLE1.SEGMENT15 SEGMENT15
942 ,TABLE1.SEGMENT16 SEGMENT16
943 ,TABLE1.SEGMENT17 SEGMENT17
944 ,TABLE1.SEGMENT18 SEGMENT18
945 ,TABLE1.SEGMENT19 SEGMENT19
946 ,TABLE1.SEGMENT20 SEGMENT20
947 ,TABLE1.SEGMENT21 SEGMENT21
948 ,TABLE1.SEGMENT22 SEGMENT22
949 ,TABLE1.SEGMENT23 SEGMENT23
950 ,TABLE1.SEGMENT24 SEGMENT24
951 ,TABLE1.SEGMENT25 SEGMENT25
952 ,TABLE1.SEGMENT26 SEGMENT26
953 ,TABLE1.SEGMENT27 SEGMENT27
954 ,TABLE1.SEGMENT28 SEGMENT28
955 ,TABLE1.SEGMENT29 SEGMENT29
956 ,TABLE1.SEGMENT30 SEGMENT30
957 FROM
958 (SELECT gll.ledger_id LEDGER_ID
959 ,gll.short_name LEDGER_SHORT_NAME
960 ,gll.description LEDGER_DESCRIPTION
961 ,gll.NAME LEDGER_NAME
962 ,gll.currency_code LEDGER_CURRENCY
963 ,xcb.party_type_code PARTY_TYPE_CODE
964 $party_col$
965 ,hzp.jgzz_fiscal_code PARTY_TYPE_TAXPAYER_ID
966 ,hzp.tax_reference PARTY_TAX_REGISTRATION_NUMBER
967 ,hzp.address1 PARTY_ADDRESS_1
968 ,hzp.address2 PARTY_ADDRESS_2
969 ,hzp.address3 PARTY_ADDRESS_3
970 ,hzp.address4 PARTY_ADDRESS_4
971 ,hzp.city PARTY_CITY
972 ,hzp.postal_code PARTY_ZIP_CODE
973 ,hzp.state PARTY_STATE
974 ,hzp.province PARTY_PROVINCE
975 ,hzp.country PARTY_COUNTRY
976 ,hzp.county PARTY_COUNTY
977 ,hps.party_site_name PARTY_SITE_NAME
978 ,hzl.address1 PARTY_SITE_ADDRESS_LINE_1
979 ,hzl.address2 PARTY_SITE_ADDRESS_LINE_2
980 ,hzl.address3 PARTY_SITE_ADDRESS_LINE_3
981 ,hzl.address4 PARTY_SITE_ADDRESS_LINE_4
982 ,hzl.city PARTY_SITE_CITY
983 ,hzl.postal_code PARTY_SITE_ZIP_CODE
984 ,hzl.state PARTY_SITE_STATE
985 ,hzl.province PARTY_SITE_PROVINCE
986 ,hzl.country PARTY_SITE_COUNTRY
987 ,hzl.county PARTY_SITE_COUNTY
988 ,xcb.application_id APPLICATION_ID
989 ,fap.application_name APPLICATION_NAME
990 ,gjst.user_je_source_name JE_SOURCE_NAME
991 ,gls.period_year PERIOD_YEAR
992 ,gls.period_num PERIOD_NUMBER
993 ,xcb.period_name PERIOD_NAME
994 ,trunc(gls.START_DATE) PERIOD_START_DATE
995 ,trunc(gls.end_date) PERIOD_END_DATE
996 ,NVL(xcb.beginning_balance_dr,0) BEGIN_BALANCE_DR
997 ,NVL(xcb.beginning_balance_cr,0) BEGIN_BALANCE_CR
998 ,NVL(xcb.period_balance_dr,0) PERIOD_NET_DR
999 ,NVL(xcb.period_balance_cr,0) PERIOD_NET_CR
1000 ,NVL(xcb.draft_beginning_balance_dr,0) BEGIN_DRAFT_BALANCE_DR
1001 ,NVL(xcb.draft_beginning_balance_cr,0) BEGIN_DRAFT_BALANCE_CR
1002 ,NVL(xcb.period_draft_balance_dr,0) PERIOD_DRAFT_NET_DR
1003 ,NVL(xcb.period_draft_balance_cr,0) PERIOD_DRAFT_NET_CR
1004 ,xcb.code_combination_id CODE_COMBINATION_ID
1005 ,gcck.concatenated_segments ACCOUNTING_CODE_COMBINATION
1006 ,xla_report_utility_pkg.get_ccid_desc
1007 (gll.chart_of_accounts_id
1008 ,xcb.code_combination_id) CODE_COMBINATION_DESCRIPTION
1009 $seg_desc_column$
1010 ,gcck.segment1 SEGMENT1
1011 ,gcck.segment2 SEGMENT2
1012 ,gcck.segment3 SEGMENT3
1013 ,gcck.segment4 SEGMENT4
1014 ,gcck.segment5 SEGMENT5
1015 ,gcck.segment6 SEGMENT6
1016 ,gcck.segment7 SEGMENT7
1017 ,gcck.segment8 SEGMENT8
1018 ,gcck.segment9 SEGMENT9
1019 ,gcck.segment10 SEGMENT10
1020 ,gcck.segment11 SEGMENT11
1021 ,gcck.segment12 SEGMENT12
1022 ,gcck.segment13 SEGMENT13
1023 ,gcck.segment14 SEGMENT14
1024 ,gcck.segment15 SEGMENT15
1025 ,gcck.segment16 SEGMENT16
1026 ,gcck.segment17 SEGMENT17
1027 ,gcck.segment18 SEGMENT18
1028 ,gcck.segment19 SEGMENT19
1029 ,gcck.segment20 SEGMENT20
1030 ,gcck.segment21 SEGMENT21
1031 ,gcck.segment22 SEGMENT22
1032 ,gcck.segment23 SEGMENT23
1033 ,gcck.segment24 SEGMENT24
1034 ,gcck.segment25 SEGMENT25
1035 ,gcck.segment26 SEGMENT26
1036 ,gcck.segment27 SEGMENT27
1037 ,gcck.segment28 SEGMENT28
1038 ,gcck.segment29 SEGMENT29
1039 ,gcck.segment30 SEGMENT30
1040 FROM gl_ledgers gll
1041 ,xla_control_balances xcb
1042 ,gl_period_statuses gls
1043 ,gl_code_combinations_kfv gcck
1044 ,hz_parties hzp
1045 ,hz_party_sites hps
1046 ,hz_locations hzl
1047 ,fnd_application_tl fap
1048 ,xla_subledgers xls
1049 ,gl_je_sources_tl gjst
1050 $party_tab$
1051 $seg_desc_from$
1052 $l_ledger_set_from$
1053 WHERE gls.ledger_id = gll.ledger_id
1054 AND gls.application_id = 101
1055 AND gls.effective_period_num =
1056 (SELECT min(inps.effective_period_num)
1057 FROM xla_control_balances incb
1058 ,gl_period_statuses inps
1059 WHERE incb.ledger_id=xcb.ledger_id
1060 AND incb.application_id= xcb.application_id
1061 AND incb.code_combination_id =xcb.code_combination_id
1062 AND incb.party_id = xcb.party_id
1063 AND incb.party_site_id = xcb.party_site_id
1064 AND inps.ledger_id = gll.ledger_id
1065 AND inps.application_id = 101
1066 AND incb.period_name = inps.period_name
1067 AND inps.effective_period_num between :p_start_period_num and :p_end_period_num
1068 )
1069 AND xcb.ledger_id = gll.ledger_id
1070 AND xcb.application_id = :G_JE_SOURCE_APPLICATION_ID
1071 AND xcb.period_name = gls.period_name
1072 AND gcck.code_combination_id = xcb.code_combination_id
1073 AND hzl.location_id(+) = hps.location_id
1074 AND fap.application_id = xcb.application_id
1075 AND fap.LANGUAGE = :P_LANG
1076 AND xls.application_id = xcb.application_id
1077 AND gjst.je_source_name = xls.je_source_name
1078 AND gjst.LANGUAGE = :P_LANG
1079 $other_filter$
1080 $p_party_join$
1081 $seg_desc_join$
1082 $l_ledger_set_where$
1083 $account_range$) TABLE1
1084 $legal_entity_from$
1085 WHERE 1 = 1
1086 $legal_entity_join$
1087 )' ;
1088
1089 l_balance_query := REPLACE(l_balance_query
1090 ,'$legal_entity_columns$'
1091 ,p_legal_ent_col);
1092 l_balance_query := REPLACE(l_balance_query
1093 ,'$party_col$'
1094 ,p_party_col);
1095 l_balance_query := REPLACE(l_balance_query
1096 ,'$seg_desc_column$'
1097 ,p_qualifier_segment);
1098 l_balance_query := REPLACE(l_balance_query
1099 ,'$party_tab$'
1100 ,p_party_tab);
1101 l_balance_query := REPLACE(l_balance_query
1102 ,'$legal_entity_from$'
1103 ,p_legal_ent_from);
1104 l_balance_query := REPLACE(l_balance_query
1105 ,'$seg_desc_from$'
1106 ,p_seg_desc_from);
1107 l_balance_query := REPLACE(l_balance_query
1108 ,'$other_filter$'
1109 ,l_other_filter);
1110 l_balance_query := REPLACE(l_balance_query
1111 ,'$p_party_join$'
1112 ,p_party_join);
1113 l_balance_query := REPLACE(l_balance_query
1114 ,'$legal_entity_join$'
1115 ,p_legal_ent_join);
1116 l_balance_query := REPLACE(l_balance_query
1117 ,'$seg_desc_join$'
1118 ,p_seg_desc_join);
1119 l_balance_query := REPLACE(l_balance_query
1120 ,'$l_ledger_set_from$'
1121 ,l_ledger_set_from);
1122 l_balance_query := REPLACE(l_balance_query
1123 ,'$l_ledger_set_where$'
1124 ,l_ledger_set_where);
1125
1126 IF p_accounting_flexfield_from IS NOT NULL THEN
1127 l_flex_range_where :=
1128 get_flex_range_where
1129 (p_coa_id => l_coa_id
1130 ,p_accounting_flexfield_from => p_accounting_flexfield_from
1131 ,p_accounting_flexfield_to => p_accounting_flexfield_to );
1132
1133 l_balance_query := REPLACE (l_balance_query
1134 ,'$account_range$'
1135 ,' AND '||l_flex_range_where);
1136 ELSE
1137 l_balance_query := REPLACE(l_balance_query, '$account_range$', '');
1138 END IF;
1139
1140
1141 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_balance_query :'||l_balance_query);
1142
1143 IF(P_JE_SOURCE_NAME='ALL') THEN
1144 FORALL i IN 1..l_responsibility_ids.count
1145 execute immediate l_balance_query
1146 using p_start_period_num
1147 ,p_end_period_num
1148 ,l_responsibility_ids(i)
1149 ,p_lang
1150 ,p_lang
1151 ,p_ledger_id;
1152 ELSE
1153 execute immediate l_balance_query
1154 using p_start_period_num
1155 ,p_end_period_num
1156 ,g_je_source_application_id
1157 ,p_lang
1158 ,p_lang
1159 ,p_ledger_id;
1160 END IF;
1161
1162 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1163 trace
1164 (p_msg => 'END of beforeReport'
1165 ,p_level => C_LEVEL_PROCEDURE
1166 ,p_module => l_log_module);
1167 END IF;
1168
1169
1170 IF P_DIST_ACCT_FLEXFIELD_FROM IS NOT NULL AND P_DIST_ACCT_FLEXFIELD_TO IS NOT NULL THEN
1171 p_dist_account_ff_range_filter :=
1172 get_flex_range_where
1173 (p_coa_id => l_coa_id
1174 ,p_accounting_flexfield_from => P_DIST_ACCT_FLEXFIELD_FROM
1175 ,p_accounting_flexfield_to => P_DIST_ACCT_FLEXFIELD_TO );
1176
1177 p_dist_account_ff_range_filter := ' AND '||p_dist_account_ff_range_filter;
1178
1179 END IF;
1180
1181 IF p_dist_account_ff_range_filter IS NULL THEN
1182 p_dist_account_ff_range_filter := 'AND 1=1';
1183 END IF;
1184
1185
1186 RETURN TRUE;
1187
1188 EXCEPTION
1189 WHEN OTHERS THEN
1190 xla_exceptions_pkg.raise_message
1191 (p_location => 'JAJP_TP_BALANCE_CONTRA_RPT_PKG.beforeReport ');
1192 END beforeReport;
1193
1194
1195 --=============================================================================
1196 -- *********** Initialization routine **********
1197 --=============================================================================
1198
1199 --=============================================================================
1200 --
1201 --
1202 --
1203 --
1204 --
1205 --
1206 --
1207 --
1208 --
1209 --
1210 -- Following code is executed when the package body is referenced for the first
1211 -- time
1212 --
1213 --
1214 --
1215 --
1216 --
1217 --
1218 --
1219 --
1220 --
1221 --
1222 --
1223 --
1224 --=============================================================================
1225
1226 BEGIN
1227 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1228 g_log_enabled := fnd_log.test
1229 (log_level => g_log_level
1230 ,MODULE => C_DEFAULT_MODULE);
1231
1232 IF NOT g_log_enabled THEN
1233 g_log_level := C_LEVEL_LOG_DISABLED;
1234 END IF;
1235
1236
1237 END JA_JP_TP_BAL_CTRL_RPT_PKG;