[Home] [Help]
PACKAGE BODY: APPS.GCS_INTERCO_DYN_BUILD_PKG
Source
1 PACKAGE BODY GCS_INTERCO_DYN_BUILD_PKG AS
2 /* $Header: gcsicdbb.pls 120.10 2007/09/21 21:23:01 spala noship $ */
3
4 --
5 -- Package
6 -- gcs_interco_dyn_build_pkg
7 -- Purpose
8 -- Dynamically created package procedures for the Intercompnay Engine
9 -- History
10 -- 12-APR-04 Srini Pala Created
11 --
12
13 -- Private Global Variables
14 --
15 -- The API name
16 g_api VARCHAR2(100) := 'gcs.plsql.GCS_INTERCO_DYN_BUILD_PKG';
17 g_line_size NUMBER := 250;
18
19
20 --
21 -- Procedure
22 -- Create_Package
23 -- Purpose
24 -- Create the dynamic portion of the translation program
25 -- Example
26 -- GCS_INTERCO_DYN_BUILD_PKG.Create_Package
27 -- Notes
28 --
29
30 PROCEDURE Interco_Create_Package(
31 x_errbuf OUT NOCOPY VARCHAR2,
32 x_retcode OUT NOCOPY VARCHAR2) IS
33
34
35 -- control each line to < 80 chars and put in <= 50 lines each time
36
37 body_block VARCHAR2(4000);
38 body_len NUMBER;
39 curr_pos NUMBER;
40 line_num NUMBER := 1;
41 comp_err VARCHAR2(10);
42
43 l_api_name VARCHAR2(50) := 'Interco_Create_Package';
44
45 BEGIN
46 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
47 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
48 g_api || '.' || l_api_name,
49 GCS_UTILITY_PKG.g_module_enter || l_api_name ||
50 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
51 END IF;
52 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_enter ||
53 -- l_api_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
54
55 body_block :=
56 'CREATE OR REPLACE PACKAGE BODY GCS_INTERCO_DYNAMIC_PKG AS
57 /* $Header $ */
58
59 --
60 -- PRIVATE GLOBAL VARIABLES
61 --
62 g_pkg_name VARCHAR2(100) := ''gcs.plsql.GCS_INTERCO_DYNAMIC_PKG'';
63 g_fnd_user_id NUMBER := fnd_global.user_id;
64 g_fnd_login_id NUMBER := fnd_global.login_id;
65 g_no_rows NUMBER :=0;
66 g_intercompany_org_code VARCHAR2(30) := ''DIFFERENT_ORG'' ;
67 g_specific_intercompany_id NUMBER:= 0;
68 g_cons_run_name VARCHAR2(80);
69 gbl_period_end_date DATE;
70 --
71 -- PUBLIC FUNCTIONS
72 --
73 FUNCTION INSR_INTERCO_LINES (p_hierarchy_id IN NUMBER,
74 p_cal_period_id IN NUMBER,
75 p_entity_id IN NUMBER,
76 p_match_rule_code VARCHAR2,
77 p_balance_type VARCHAR2,
78 p_elim_mode IN VARCHAR2,
79 P_Currency_code IN VARCHAR2,
80 p_dataset_code IN NUMBER,
81 p_lob_dim_col_name IN VARCHAR2,
82 p_cons_run_name IN VARCHAR2,
83 p_period_end_date IN DATE,
84 p_fem_ledger_id IN NUMBER)
85 RETURN BOOLEAN IS
86
87 l_api_name VARCHAR2(30) := ''INSR_INTERCO_LINES'';
88
89 -- Insert all eligible elimination lines from GCS_INTERCO_ELM_TRX
90 -- GCS_ENTRY-LINES.
91 BEGIN
92
93 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
94 fnd_log.STRING (fnd_log.level_procedure,
95 g_pkg_name || ''.'' || l_api_name,
96 gcs_utility_pkg.g_module_enter
97 || '' ''
98 || l_api_name
99 || ''() ''
100 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
101 );
102 END IF;
103
104 ';
105 curr_pos := 1;
106 body_len := LENGTH(body_block);
107 WHILE curr_pos <= body_len LOOP
108 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
109 line_num);
110 curr_pos := curr_pos + g_line_size;
111 line_num := line_num + 1;
112 END LOOP;
113
114
115 body_block := '
116
117 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
118 fnd_log.STRING (fnd_log.level_procedure,
119 g_pkg_name || ''.'' || l_api_name,
120 '' Arguments passed to Insr_Interco_Lines() ''
121 ||'' Hierarchy_Id: ''||p_hierarchy_id
122 ||'' Cal_Period_Id: ''||p_cal_period_id
123 ||'' Entity_Id: ''||p_entity_id
124 ||'' Match Rule Code: ''||p_match_rule_code
125 ||'' Balance_Type: ''||p_balance_type
126 ||'' Elim_Mode: ''||p_elim_mode
127 ||'' Currency_Code: ''||p_currency_code
128 ||'' Dataset Code:''||p_dataset_code
129 ||'' LOB dim column name: ''||p_lob_dim_col_name
130 ||'' Consolidation Run name:''||p_cons_run_name
131 ||''Period end date: ''||p_period_end_date
132 ||''Fem Ledger Id: ''||p_fem_ledger_id);
133
134 END IF;
135 ';
136
137 curr_pos := 1;
138 body_len := LENGTH(body_block);
139 WHILE curr_pos <= body_len LOOP
140 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
141 line_num);
142 curr_pos := curr_pos + g_line_size;
143 line_num := line_num + 1;
144 END LOOP;
145
146 body_block := '
147 g_cons_run_name := p_cons_run_name;
148 gbl_period_end_date := p_period_end_date;
149 IF (P_ELIM_MODE = ''IE'') THEN
150 IF (p_match_rule_code = ''COMPANY'') THEN /* In Intercompany option */
151 g_no_rows := 0;
152 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
153 fnd_log.STRING (fnd_log.level_procedure,
154 g_pkg_name || ''.'' || l_api_name,
155 ''Intercompany- Inserting entry lines''
156 || '' into GCS_ENTRY_LINES_GT''
157 || '' after matching by company-Receivables side''
158 );
159 END IF;
160
161
162 INSERT INTO GCS_ENTRY_LINES_GT
163 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
164 , LINE_ITEM_Id, INTERCOMPANY_ID, FINANCIAL_ELEM_ID
165 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
166 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
167 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
168 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
169 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
170 , YTD_CREDIT_BALANCE_E , YTD_DEBIT_BALANCE_E
171 , DESCRIPTION, YTD_BALANCE_E, RECEIVABLES_ORG_ID,
172 PAYABLES_ORG_ID )
173 SELECT /*+ ORDERED FULL(GIHG) INDEX(GIET GCS_INTERCO_ELM_TRX_U1)
174 INDEX(GIM GCS_INTERCO_MEMBERS_U1) USE_NL(GIET GIM)
175 INDEX(GCR GCS_CONS_RELATIONSHIPS_N1)
176 INDEX (FB FEM_BALANCES_P)
177 USE_NL(GCR FB)*/
178 gihg.entry_id, giet.company_cost_center_org_id
179 , giet.line_item_id
180 , giet.intercompany_id,
181 ';
182
183 curr_pos := 1;
184 body_len := LENGTH(body_block);
185 WHILE curr_pos <= body_len LOOP
186 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
187 line_num);
188 curr_pos := curr_pos + g_line_size;
189 line_num := line_num + 1;
190 END LOOP;
191 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
192 ' giet.', GCS_UTILITY_PKG.g_nl, ' NULL,', line_num);
193
194
195
196 body_block := '
197 SUM(NVL(fb.ytd_debit_balance_e,0))
198 , SUM(NVL(fb.ytd_credit_balance_e,0))
199 , Max(gihg.rule_id)
200 , (SUM(NVL(fb.ytd_credit_balance_e,0))
201 - SUM(NVL(fb.ytd_debit_balance_e,0))),
202 DECODE(MAX(gim.line_item_group), 1,
203 giet.company_cost_center_org_id,
204 giet.Intercompany_id),
205 DECODE(MAX(gim.line_item_group), 2,
206 giet.company_cost_center_org_id,
207 giet.Intercompany_id)
208 FROM GCS_INTERCO_HDR_GT gihg,
209 GCS_INTERCO_ELM_TRX giet,
210 GCS_INTERCO_MEMBERS gim,
211 GCS_CONS_RELATIONSHIPS gcr,
212 FEM_BALANCES fb
213 WHERE giet.cal_period_id = p_cal_period_id
214 AND giet.hierarchy_id = p_hierarchy_id
215 AND gihg.currency_code IN (p_currency_code,''STAT'')
216 AND giet.line_item_id = gim.line_item_id
217 AND (giet.src_entity_id = gihg.source_entity_id
218 AND giet.target_entity_id = gihg.target_entity_id)
219 AND gim.rule_id = gihg.rule_id
220 AND gim.line_item_group = 1
221 AND gcr.hierarchy_id = p_hierarchy_id
222 AND gcr.parent_entity_id = p_entity_id
223 AND gcr.actual_ownership_flag =''Y''
224 AND gcr.dominant_parent_flag = ''Y''
225 AND (gbl_period_end_date
226 BETWEEN NVL(start_date,TO_DATE(''01/01/1950'', ''MM/DD/YYYY''))
227 AND NVL(END_DATE, TO_DATE(''12/31/9999'', ''MM/DD/YYYY'')))
228 AND gcr.child_entity_id = fb.entity_id
229 AND giet.company_cost_center_org_id = fb.company_cost_center_org_id
230 AND giet.intercompany_id = fb.intercompany_id
231 AND giet.line_item_id = fb.line_item_id
232 AND fb.currency_code = gihg.currency_code
233 AND fb.cal_period_id = giet.cal_period_id
234 AND fb.dataset_code = p_dataset_code
235 AND fb.ledger_id = P_fem_ledger_id
236 AND fb.source_system_code = 70
237 ';
238
239 --******--
240 curr_pos := 1;
241 body_len := LENGTH(body_block);
242 WHILE curr_pos <= body_len LOOP
243 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
244 line_num);
245 curr_pos := curr_pos + g_line_size;
246 line_num := line_num + 1;
247 END LOOP;
248
249
250 --******--
251 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Join_List(
252 ' AND fb.', ' = giet. ', GCS_UTILITY_PKG.g_nl, line_num);
253 --******--
254
255 body_block :=
256 '
257 GROUP BY gihg.entry_id, giet.company_cost_center_org_id,
258 giet.intercompany_id,
259
260 ';
261 --******--
262 curr_pos := 1;
263 body_len := LENGTH(body_block);
264 WHILE curr_pos <= body_len LOOP
265 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
266 line_num);
267 curr_pos := curr_pos + g_line_size;
268 line_num := line_num + 1;
269 END LOOP;
270
271
272 --******--
273 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
274 ' giet.', GCS_UTILITY_PKG.g_nl, '', line_num);
275
276
277 body_block :=
278 'giet.line_item_id;';
279
280 --******--
281 curr_pos := 1;
282 body_len := LENGTH(body_block);
283 WHILE curr_pos <= body_len LOOP
284 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
285 line_num);
286 curr_pos := curr_pos + g_line_size;
287 line_num := line_num + 1;
288 END LOOP;
289
290 --******--
291 body_block := '
292 g_no_rows := NVL(SQL%ROWCOUNT,0);
293
294 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
295 FND_MESSAGE.Set_Name(''SQLGL'',''SHRD0117'');
296 FND_MESSAGE.Set_Token(''NUM'',TO_CHAR(g_no_rows));
297 FND_MESSAGE.Set_Token(''TABLE'',''GCS_ENTRY_LINES_GT'');
298
299 FND_LOG.String (fnd_log.level_procedure,
300 g_pkg_name || ''.'' || l_api_name,
301 ''SHRD0117: ''||FND_MESSAGE.get);
302 END IF;
303
304 --****************************----
305 g_no_rows := 0;
306 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
307 fnd_log.STRING (fnd_log.level_procedure,
308 g_pkg_name || ''.'' || l_api_name,
309 ''Intercompany- Inserting entry lines''
310 || '' into GCS_ENTRY_LINES_GT''
311 || '' after matching by company - Payabales side''
312 );
313 END IF;
314
315 INSERT INTO GCS_ENTRY_LINES_GT
316 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
317 , LINE_ITEM_Id, INTERCOMPANY_ID, FINANCIAL_ELEM_ID
318 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
319 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
320 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
321 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
322 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
323 , YTD_CREDIT_BALANCE_E , YTD_DEBIT_BALANCE_E
324 , DESCRIPTION, YTD_BALANCE_E, RECEIVABLES_ORG_ID,
325 PAYABLES_ORG_ID )
326 SELECT /*+ ORDERED FULL(GIHG) INDEX(GIET GCS_INTERCO_ELM_TRX_U1)
327 INDEX(GIM GCS_INTERCO_MEMBERS_U1) USE_NL(GIET GIM)
328 INDEX(GCR GCS_CONS_RELATIONSHIPS_N1)
329 INDEX (FB FEM_BALANCES_P)
330 USE_NL(GCR FB)*/
331 gihg.entry_id, giet.company_cost_center_org_id
332 , giet.line_item_id
333 , giet.intercompany_id,
334 ';
335
336 curr_pos := 1;
337 body_len := LENGTH(body_block);
338 WHILE curr_pos <= body_len LOOP
339 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
340 line_num);
341 curr_pos := curr_pos + g_line_size;
342 line_num := line_num + 1;
343 END LOOP;
344 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
345 ' giet.', GCS_UTILITY_PKG.g_nl, ' NULL,', line_num);
346
347
348
349 body_block := '
350 SUM(NVL(fb.ytd_debit_balance_e,0))
351 , SUM(NVL(fb.ytd_credit_balance_e,0))
352 , MAX(gihg.rule_id)
353 , (SUM(NVL(fb.ytd_credit_balance_e,0))
354 - SUM(NVL(fb.ytd_debit_balance_e,0))),
355 DECODE(MAX(gim.line_item_group), 1,
356 giet.company_cost_center_org_id,
357 giet.Intercompany_id),
358 DECODE(MAX(gim.line_item_group), 2,
359 giet.company_cost_center_org_id,
360 giet.Intercompany_id)
361 FROM GCS_INTERCO_HDR_GT gihg,
362 GCS_INTERCO_ELM_TRX giet,
363 GCS_INTERCO_MEMBERS gim,
364 GCS_CONS_RELATIONSHIPS gcr,
365 FEM_BALANCES fb
366 WHERE giet.cal_period_id = p_cal_period_id
367 AND giet.hierarchy_id = p_hierarchy_id
368 AND gihg.currency_code IN (p_currency_code,''STAT'')
369 AND giet.line_item_id = gim.line_item_id
370 AND (giet.src_entity_id = gihg.target_entity_id
371 AND giet.target_entity_id = gihg.source_entity_id )
372 AND gim.rule_id = gihg.rule_id
373 AND gim.line_item_group = 2
374 AND gcr.hierarchy_id = p_hierarchy_id
375 AND gcr.parent_entity_id = p_entity_id
376 AND gcr.actual_ownership_flag =''Y''
377 AND gcr.dominant_parent_flag = ''Y''
378 AND (gbl_period_end_date
379 BETWEEN NVL(start_date,TO_DATE(''01/01/1950'', ''MM/DD/YYYY''))
380 AND NVL(END_DATE, TO_DATE(''12/31/9999'', ''MM/DD/YYYY'')))
381 AND gcr.child_entity_id = fb.entity_id
382 AND giet.company_cost_center_org_id = fb.company_cost_center_org_id
383 AND giet.intercompany_id = fb.intercompany_id
384 AND giet.line_item_id = fb.line_item_id
385 AND fb.currency_code = gihg.currency_code
389 AND fb.source_system_code = 70
386 AND fb.cal_period_id = giet.cal_period_id
387 AND fb.dataset_code = p_dataset_code
388 AND fb.ledger_id = P_fem_ledger_id
390 ';
391
392 --******--
393 curr_pos := 1;
394 body_len := LENGTH(body_block);
395 WHILE curr_pos <= body_len LOOP
396 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
397 line_num);
398 curr_pos := curr_pos + g_line_size;
399 line_num := line_num + 1;
400 END LOOP;
401
402
403 --******--
404 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Join_List(
405 ' AND fb.', ' = giet. ', GCS_UTILITY_PKG.g_nl, line_num);
406
407 --******--
408
409
410 body_block :=
411 '
412 GROUP BY gihg.entry_id, giet.company_cost_center_org_id,
413 giet.intercompany_id,
414
415 ';
416 --******--
417 curr_pos := 1;
418 body_len := LENGTH(body_block);
419 WHILE curr_pos <= body_len LOOP
420 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
421 line_num);
422 curr_pos := curr_pos + g_line_size;
423 line_num := line_num + 1;
424 END LOOP;
425
426
427 --******--
428 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
429 ' giet.', GCS_UTILITY_PKG.g_nl, '', line_num);
430
431
432 body_block :=
433 'giet.line_item_id;';
434
435 --******--
436 curr_pos := 1;
437 body_len := LENGTH(body_block);
438 WHILE curr_pos <= body_len LOOP
439 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
440 line_num);
441 curr_pos := curr_pos + g_line_size;
442 line_num := line_num + 1;
443 END LOOP;
444
445 --******--
446 body_block := '
447 g_no_rows := NVL(SQL%ROWCOUNT,0);
448
449 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
450 FND_MESSAGE.Set_Name(''SQLGL'',''SHRD0117'');
451 FND_MESSAGE.Set_Token(''NUM'',TO_CHAR(g_no_rows));
452 FND_MESSAGE.Set_Token(''TABLE'',''GCS_ENTRY_LINES_GT'');
453
454 FND_LOG.String (fnd_log.level_procedure,
455 g_pkg_name || ''.'' || l_api_name,
456 ''SHRD0117: ''||FND_MESSAGE.get);
457 END IF;
458
459
460 ';
461 --******--
462 curr_pos := 1;
463 body_len := LENGTH(body_block);
464 WHILE curr_pos <= body_len LOOP
465 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
466 line_num);
467 curr_pos := curr_pos + g_line_size;
468 line_num := line_num + 1;
469 END LOOP;
470 --******--
471
472
473 body_block := '
474
475 ELSIF (p_match_rule_code = ''ORGANIZATION'') THEN
476 --In Intercompany option
477 g_no_rows := 0;
478 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
479 fnd_log.STRING (fnd_log.level_procedure,
480 g_pkg_name || ''.'' || l_api_name,
481 ''Intercompany- Inserting entry lines''
482 || '' into GCS_ENTRY_LINES_GT''
483 || '' after matching by Org-Receivables side''
484 );
485 END IF;
486 ';
487 --******--
488 curr_pos := 1;
489 body_len := LENGTH(body_block);
490 WHILE curr_pos <= body_len LOOP
491 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
492 line_num);
493 curr_pos := curr_pos + g_line_size;
494 line_num := line_num + 1;
495 END LOOP;
496 body_block := '
497 INSERT INTO GCS_ENTRY_LINES_GT
498 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
499 , LINE_ITEM_Id, INTERCOMPANY_ID, FINANCIAL_ELEM_ID
500 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
501 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
502 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
503 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
504 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
505 , YTD_CREDIT_BALANCE_E , YTD_DEBIT_BALANCE_E
506 , DESCRIPTION ,YTD_BALANCE_E, RECEIVABLES_ORG_ID,
507 PAYABLES_ORG_ID)
508 SELECT /*+ ORDERED FULL(GIHG) INDEX(GIET GCS_INTERCO_ELM_TRX_U1)
509 INDEX(GIM GCS_INTERCO_MEMBERS_U1) USE_NL(GIET GIM)
510 INDEX(GCR GCS_CONS_RELATIONSHIPS_N1)
511 INDEX (FB FEM_BALANCES_P)
512 USE_NL(GCR FB)*/
513 gihg.entry_id, giet.company_cost_center_org_id
514 , giet.line_item_id
515 , giet.intercompany_id,
516 ';
517
518 curr_pos := 1;
519 body_len := LENGTH(body_block);
520 WHILE curr_pos <= body_len LOOP
521 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
522 line_num);
523 curr_pos := curr_pos + g_line_size;
524 line_num := line_num + 1;
525 END LOOP;
526
530 'DECODE(p_lob_dim_col_name, ',
527 line_num := GCS_DYNAMIC_UTIL_PKG.Build_interco_Comma_List(
528 ' giet.',
529 'DECODE(giet.elim_lob_id, NULL,giet.',
531 ' giet.elim_lob_id, giet.',
532 ')),',GCS_UTILITY_PKG.g_nl, ' NULL,', line_num);
533
534 body_block := '
535 SUM(NVL(fb.ytd_debit_balance_e,0))
536 , SUM(NVL(fb.ytd_credit_balance_e,0))
537 , MAX(gihg.rule_id)
538 , (SUM(NVL(fb.ytd_credit_balance_e,0))
539 - SUM(NVL(fb.ytd_debit_balance_e,0))),
540 DECODE(MAX(gim.line_item_group), 1,
541 giet.company_cost_center_org_id,
542 giet.Intercompany_id),
543 DECODE(MAX(gim.line_item_group), 2,
544 giet.company_cost_center_org_id,
545 giet.Intercompany_id)
546 FROM GCS_INTERCO_HDR_GT gihg,
547 GCS_INTERCO_ELM_TRX giet,
548 GCS_INTERCO_MEMBERS gim,
549 GCS_CONS_RELATIONSHIPS gcr,
550 FEM_BALANCES fb
551 WHERE giet.cal_period_id = p_cal_period_id
552 AND giet.hierarchy_id = p_hierarchy_id
553 AND gihg.currency_code IN (p_currency_code,''STAT'')
554 AND giet.line_item_id = gim.line_item_id
555 AND (giet.src_entity_id =
556 gihg.source_entity_id
557 AND giet.target_entity_id =
558 gihg.target_entity_id)
559 AND gim.rule_id = gihg.rule_id
560 AND gim.line_item_group = 1
561 AND gcr.hierarchy_id = p_hierarchy_id
562 AND gcr.parent_entity_id = p_entity_id
563 AND gcr.actual_ownership_flag =''Y''
564 AND gcr.dominant_parent_flag = ''Y''
565 AND (gbl_period_end_date
566 BETWEEN NVL(start_date,TO_DATE(''01/01/1950'', ''MM/DD/YYYY''))
567 AND NVL(END_DATE, TO_DATE(''12/31/9999'', ''MM/DD/YYYY'')))
568 AND gcr.child_entity_id = fb.entity_id
569 AND giet.company_cost_center_org_id = fb.company_cost_center_org_id
570 AND giet.intercompany_id = fb.intercompany_id
571 AND giet.line_item_id = fb.line_item_id
572 AND fb.currency_code = gihg.currency_code
573 AND fb.cal_period_id = giet.cal_period_id
574 AND fb.dataset_code = p_dataset_code
575 AND fb.ledger_id = P_fem_ledger_id
576 AND fb.source_system_code = 70
577 ';
578 --******--
579 curr_pos := 1;
580 body_len := LENGTH(body_block);
581 WHILE curr_pos <= body_len LOOP
582 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
583 line_num);
584 curr_pos := curr_pos + g_line_size;
585 line_num := line_num + 1;
586 END LOOP;
587 --******--
588
589 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Join_List(
590 ' AND fb.', ' = giet. ', GCS_UTILITY_PKG.g_nl, line_num);
591
592 body_block :=
593 '
594 GROUP BY gihg.entry_id, giet.company_cost_center_org_id,
595 giet.intercompany_id,
596 ';
597 --******--
598 curr_pos := 1;
599 body_len := LENGTH(body_block);
600 WHILE curr_pos <= body_len LOOP
601 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
602 line_num);
603 curr_pos := curr_pos + g_line_size;
604 line_num := line_num + 1;
605 END LOOP;
606
607 --******--
608
609 line_num := GCS_DYNAMIC_UTIL_PKG.Build_interco_Comma_List(
610 ' giet.',
611 'DECODE(giet.elim_lob_id, NULL,giet.',
612 'DECODE(p_lob_dim_col_name, ',
613 ' giet.elim_lob_id, giet.',
614 ')),',GCS_UTILITY_PKG.g_nl, '', line_num);
615
616
617 body_block :=
618 '
619 giet.line_item_id;
620 ';
621
622 --******--
623 curr_pos := 1;
624 body_len := LENGTH(body_block);
625 WHILE curr_pos <= body_len LOOP
626 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
627 line_num);
628 curr_pos := curr_pos + g_line_size;
629 line_num := line_num + 1;
630 END LOOP;
631
632 --******--
633 body_block := '
634 g_no_rows := NVL(SQL%ROWCOUNT,0);
635
636 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
637 FND_MESSAGE.Set_Name(''SQLGL'',''SHRD0117'');
638 FND_MESSAGE.Set_Token(''NUM'',TO_CHAR(g_no_rows));
639 FND_MESSAGE.Set_Token(''TABLE'',''GCS_ENTRY_LINES_GT'');
640
641 FND_LOG.String (fnd_log.level_procedure,
642 g_pkg_name || ''.'' || l_api_name,
643 ''SHRD0117: ''||FND_MESSAGE.get);
644 END IF;
645
646 ';
647
648 --******--
649 curr_pos := 1;
650 body_len := LENGTH(body_block);
651 WHILE curr_pos <= body_len LOOP
652 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
653 line_num);
654 curr_pos := curr_pos + g_line_size;
655 line_num := line_num + 1;
656 END LOOP;
660 g_no_rows := 0;
657 --*****--------
658
659 body_block :='
661 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
662 fnd_log.STRING (fnd_log.level_procedure,
663 g_pkg_name || ''.'' || l_api_name,
664 ''Intercompany- Inserting entry lines''
665 || '' into GCS_ENTRY_LINES''
666 || '' after matching by Org-Payables side''
667 );
668 END IF;
669 ';
670 --******--
671 curr_pos := 1;
672 body_len := LENGTH(body_block);
673 WHILE curr_pos <= body_len LOOP
674 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
675 line_num);
676 curr_pos := curr_pos + g_line_size;
677 line_num := line_num + 1;
678 END LOOP;
679 body_block := '
680 INSERT INTO GCS_ENTRY_LINES_GT
681 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
682 , LINE_ITEM_Id, INTERCOMPANY_ID, FINANCIAL_ELEM_ID
683 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
684 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
685 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
686 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
687 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
688 , YTD_CREDIT_BALANCE_E , YTD_DEBIT_BALANCE_E
689 , DESCRIPTION ,YTD_BALANCE_E, RECEIVABLES_ORG_ID,
690 PAYABLES_ORG_ID)
691 SELECT /*+ ORDERED FULL(GIHG) INDEX(GIET GCS_INTERCO_ELM_TRX_U1)
692 INDEX(GIM GCS_INTERCO_MEMBERS_U1) USE_NL(GIET GIM)
693 INDEX(GCR GCS_CONS_RELATIONSHIPS_N1)
694 INDEX (FB FEM_BALANCES_P)
695 USE_NL(GCR FB)*/
696 gihg.entry_id, giet.company_cost_center_org_id
697 , giet.line_item_id
698 , giet.intercompany_id,
699 ';
700
701 curr_pos := 1;
702 body_len := LENGTH(body_block);
703 WHILE curr_pos <= body_len LOOP
704 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
705 line_num);
706 curr_pos := curr_pos + g_line_size;
707 line_num := line_num + 1;
708 END LOOP;
709
710 line_num := GCS_DYNAMIC_UTIL_PKG.Build_interco_Comma_List(
711 ' giet.',
712 'DECODE(giet.elim_lob_id, NULL,giet.',
713 'DECODE(p_lob_dim_col_name, ',
714 ' giet.elim_lob_id, giet.',
715 ')),',GCS_UTILITY_PKG.g_nl, ' NULL,', line_num);
716
717 body_block := '
718 SUM(NVL(fb.ytd_debit_balance_e,0))
719 , SUM(NVL(fb.ytd_credit_balance_e,0))
720 , MAX(gihg.rule_id)
721 , (SUM(NVL(fb.ytd_credit_balance_e,0))
722 - SUM(NVL(fb.ytd_debit_balance_e,0))),
723 DECODE(MAX(gim.line_item_group), 1,
724 giet.company_cost_center_org_id,
725 giet.Intercompany_id),
726 DECODE(MAX(gim.line_item_group), 2,
727 giet.company_cost_center_org_id,
728 giet.Intercompany_id)
729 FROM GCS_INTERCO_HDR_GT gihg,
730 GCS_INTERCO_ELM_TRX giet,
731 GCS_INTERCO_MEMBERS gim,
732 GCS_CONS_RELATIONSHIPS gcr,
733 FEM_BALANCES fb
734 WHERE giet.cal_period_id = p_cal_period_id
735 AND giet.hierarchy_id = p_hierarchy_id
736 AND gihg.currency_code IN (p_currency_code,''STAT'')
737 AND giet.line_item_id = gim.line_item_id
738 AND (giet.src_entity_id =
739 gihg.target_entity_id
740 AND giet.target_entity_id =
741 gihg.source_entity_id)
742 AND gim.rule_id = gihg.rule_id
743 AND gim.line_item_group = 2
744 AND gcr.hierarchy_id = p_hierarchy_id
745 AND gcr.parent_entity_id = p_entity_id
746 AND gcr.actual_ownership_flag =''Y''
747 AND gcr.dominant_parent_flag = ''Y''
748 AND (gbl_period_end_date
749 BETWEEN NVL(start_date,TO_DATE(''01/01/1950'', ''MM/DD/YYYY''))
750 AND NVL(END_DATE, TO_DATE(''12/31/9999'', ''MM/DD/YYYY'')))
751 AND gcr.child_entity_id = fb.entity_id
752 AND giet.company_cost_center_org_id = fb.company_cost_center_org_id
753 AND giet.intercompany_id = fb.intercompany_id
754 AND giet.line_item_id = fb.line_item_id
755 AND fb.currency_code = gihg.currency_code
756 AND fb.cal_period_id = giet.cal_period_id
757 AND fb.dataset_code = p_dataset_code
758 AND fb.ledger_id = P_fem_ledger_id
759 AND fb.source_system_code = 70
760 ';
761 --******--
762 curr_pos := 1;
763 body_len := LENGTH(body_block);
764 WHILE curr_pos <= body_len LOOP
765 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
766 line_num);
767 curr_pos := curr_pos + g_line_size;
768 line_num := line_num + 1;
769 END LOOP;
770 --******--
771
772 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Join_List(
773 ' AND fb.', ' = giet. ', GCS_UTILITY_PKG.g_nl, line_num);
774
775 body_block :=
776 '
777 GROUP BY gihg.entry_id, giet.company_cost_center_org_id,
781 curr_pos := 1;
778 giet.intercompany_id,
779 ';
780 --******--
782 body_len := LENGTH(body_block);
783 WHILE curr_pos <= body_len LOOP
784 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
785 line_num);
786 curr_pos := curr_pos + g_line_size;
787 line_num := line_num + 1;
788 END LOOP;
789
790 --******--
791
792 line_num := GCS_DYNAMIC_UTIL_PKG.Build_interco_Comma_List(
793 ' giet.',
794 'DECODE(giet.elim_lob_id, NULL,giet.',
795 'DECODE(p_lob_dim_col_name, ',
796 ' giet.elim_lob_id, giet.',
797 ')),',GCS_UTILITY_PKG.g_nl, '', line_num);
798
799
800 body_block :=
801 '
802 giet.line_item_id;
803 ';
804
805 --******--
806 curr_pos := 1;
807 body_len := LENGTH(body_block);
808 WHILE curr_pos <= body_len LOOP
809 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
810 line_num);
811 curr_pos := curr_pos + g_line_size;
812 line_num := line_num + 1;
813 END LOOP;
814
815 --******--
816 body_block := '
817 g_no_rows := NVL(SQL%ROWCOUNT,0);
818
819 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
820 FND_MESSAGE.Set_Name(''SQLGL'',''SHRD0117'');
821 FND_MESSAGE.Set_Token(''NUM'',TO_CHAR(g_no_rows));
822 FND_MESSAGE.Set_Token(''TABLE'',''GCS_ENTRY_LINES_GT'');
823
824 FND_LOG.String (fnd_log.level_procedure,
825 g_pkg_name || ''.'' || l_api_name,
826 ''SHRD0117: ''||FND_MESSAGE.get);
827 END IF;
828 END IF; -- End if for match by
829 ';
830
831 --******--
832 curr_pos := 1;
833 body_len := LENGTH(body_block);
834 WHILE curr_pos <= body_len LOOP
835 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
836 line_num);
837 curr_pos := curr_pos + g_line_size;
838 line_num := line_num + 1;
839 END LOOP;
840 --****--------
841 -- Intracompany Statements follows -----------------------------
842
843 body_block := '
844 ELSIF (P_ELIM_MODE = ''IA'') THEN
845 IF (p_match_rule_code = ''COMPANY'') THEN --In Intracompany option
846 g_no_rows := 0;
847 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
848 fnd_log.STRING (fnd_log.level_procedure,
849 g_pkg_name || ''.'' || l_api_name,
850 ''Intracompany- Inserting entry lines''
851 || '' into GCS_ENTRY_LINES_GT''
852 || '' after matching by company''
853 );
854 END IF;
855
856 INSERT INTO GCS_ENTRY_LINES_GT
857 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
858 , LINE_ITEM_Id, INTERCOMPANY_ID, FINANCIAL_ELEM_ID
859 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
860 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
861 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
862 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
863 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
864 , YTD_CREDIT_BALANCE_E , YTD_DEBIT_BALANCE_E
865 , DESCRIPTION, YTD_BALANCE_E, RECEIVABLES_ORG_ID,
866 PAYABLES_ORG_ID)
867 SELECT /*+ ORDERED FULL(GIHG)
868 INDEX(GIET GCS_INTERCO_ELM_TRX_U1)
869 INDEX(GIM GCS_INTERCO_MEMBERS_U1)
870 INDEX (FB FEM_BALANCES_P)
871 USE_NL(GIET FB)*/
872 gihg.entry_id, giet.company_cost_center_org_id
873 , giet.line_item_id
874 , giet.intercompany_id,
875 ';
876
877 curr_pos := 1;
878 body_len := LENGTH(body_block);
879 WHILE curr_pos <= body_len LOOP
880 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
881 line_num);
882 curr_pos := curr_pos + g_line_size;
883 line_num := line_num + 1;
884 END LOOP;
885 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
886 ' giet.', GCS_UTILITY_PKG.g_nl, ' NULL,', line_num);
887
888
889
890 body_block := '
891 SUM(fb.ytd_debit_balance_e)
892 , SUM(fb.ytd_credit_balance_e)
893 , MAX(gihg.rule_id)
894 , (SUM(NVL(fb.ytd_credit_balance_e,0))
895 - SUM(NVL(fb.ytd_debit_balance_e,0))),
896 DECODE(MAX(gim.line_item_group), 1,
897 giet.company_cost_center_org_id,
898 giet.Intercompany_id),
899 DECODE(MAX(gim.line_item_group), 2,
900 giet.company_cost_center_org_id,
901 giet.Intercompany_id)
902 FROM GCS_INTERCO_HDR_GT gihg,
903 GCS_INTERCO_ELM_TRX giet,
904 GCS_INTERCO_MEMBERS gim,
905 FEM_BALANCES fb
906 WHERE giet.cal_period_id = p_cal_period_id
907 AND giet.hierarchy_id = p_hierarchy_id
908 AND gihg.currency_code IN (p_currency_code,''STAT'')
909 AND giet.line_item_id = gim.line_item_id
910 AND giet.src_entity_id = giet.target_entity_id
914 AND fb.entity_id = p_entity_id
911 AND giet.src_entity_id = gihg.source_entity_id
912 AND giet.target_entity_id = gihg.target_entity_id
913 AND gim.rule_id = gihg.rule_id
915 AND giet.company_cost_center_org_id = fb.company_cost_center_org_id
916 AND giet.intercompany_id = fb.intercompany_id
917 AND giet.line_item_id = fb.line_item_id
918 AND fb.currency_code = gihg.currency_code
919 AND fb.cal_period_id = giet.cal_period_id
920 AND fb.dataset_code = p_dataset_code
921 AND fb.ledger_id = P_fem_ledger_id
922 AND fb.source_system_code = 70
923 ';
924
925 --******--
926 curr_pos := 1;
927 body_len := LENGTH(body_block);
928 WHILE curr_pos <= body_len LOOP
929 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
930 line_num);
931 curr_pos := curr_pos + g_line_size;
932 line_num := line_num + 1;
933 END LOOP;
934
935
936 --******--
937 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Join_List(
938 ' AND fb.', ' = giet. ', GCS_UTILITY_PKG.g_nl, line_num);
939
940
941 --******--
942 body_block := '
943 AND NOT EXISTS (SELECT 1
944 FROM GCS_INTERCO_ELM_TRX giet3,
945 GCS_INTERCO_MEMBERS gim2
946 WHERE giet3.hierarchy_id = p_hierarchy_id
947 AND giet3.cal_period_id = p_cal_period_id
948 AND giet3.src_entity_id = giet3.target_entity_id
949 AND giet3.src_entity_id = giet.src_entity_id
950 AND giet3.line_item_id = giet.line_item_id
951 AND giet3.src_company_id = giet.src_company_id
952 AND giet3.target_company_id = giet.target_company_id
953 ';
954 --******--
955 curr_pos := 1;
956 body_len := LENGTH(body_block);
957 WHILE curr_pos <= body_len LOOP
958 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
959 line_num);
960 curr_pos := curr_pos + g_line_size;
961 line_num := line_num + 1;
962 END LOOP;
963 --******--
964
965 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Join_List(
966 ' AND giet3.', ' = giet. ',
967 GCS_UTILITY_PKG.g_nl, line_num);
968
969 body_block :=
970 '
971 AND gim2.line_item_id = giet3.line_item_id
972 AND gim2.rule_id = gihg.rule_id
973 AND gim2.line_item_group > gim.line_item_group)
974 ';
975
976 --******--
977 curr_pos := 1;
978 body_len := LENGTH(body_block);
979 WHILE curr_pos <= body_len LOOP
980 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
981 line_num);
982 curr_pos := curr_pos + g_line_size;
983 line_num := line_num + 1;
984 END LOOP;
985 --******--
986
987
988 body_block :=
989 '
990 GROUP BY gihg.entry_id, giet.company_cost_center_org_id ,
991 giet.intercompany_id,
992
993 ';
994 --******--
995 curr_pos := 1;
996 body_len := LENGTH(body_block);
997 WHILE curr_pos <= body_len LOOP
998 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
999 line_num);
1000 curr_pos := curr_pos + g_line_size;
1001 line_num := line_num + 1;
1002 END LOOP;
1003
1004
1005 --******--
1006 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
1007 ' giet.', GCS_UTILITY_PKG.g_nl, '', line_num);
1008
1009
1010 body_block :=
1011 'giet.line_item_id;';
1012
1013 --******--
1014 curr_pos := 1;
1015 body_len := LENGTH(body_block);
1016 WHILE curr_pos <= body_len LOOP
1017 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1018 line_num);
1019 curr_pos := curr_pos + g_line_size;
1020 line_num := line_num + 1;
1021 END LOOP;
1022
1023 --******--
1024 body_block := '
1025 g_no_rows := NVL(SQL%ROWCOUNT,0);
1026
1027 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1028 FND_MESSAGE.Set_Name(''SQLGL'',''SHRD0117'');
1029 FND_MESSAGE.Set_Token(''NUM'',TO_CHAR(g_no_rows));
1030 FND_MESSAGE.Set_Token(''TABLE'',''GCS_ENTRY_LINES_GT'');
1031
1032 FND_LOG.String (fnd_log.level_procedure,
1033 g_pkg_name || ''.'' || l_api_name,
1034 ''SHRD0117: ''||FND_MESSAGE.get);
1035 END IF;
1036
1037 ';
1038 --******--
1039 curr_pos := 1;
1040 body_len := LENGTH(body_block);
1041 WHILE curr_pos <= body_len LOOP
1042 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1043 line_num);
1044 curr_pos := curr_pos + g_line_size;
1045 line_num := line_num + 1;
1046 END LOOP;
1047
1048 body_block := '
1049
1050 ELSIF (p_match_rule_code = ''ORGANIZATION'') THEN
1051 -- In Intracompany option
1052 g_no_rows := 0;
1056 ''Intracompany- Inserting entry lines''
1053 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1054 fnd_log.STRING (fnd_log.level_procedure,
1055 g_pkg_name || ''.'' || l_api_name,
1057 || ''into GCS_ENTRY_LINES_GT''
1058 || ''after matching by Org''
1059 );
1060 END IF;
1061 ';
1062 --******--
1063 curr_pos := 1;
1064 body_len := LENGTH(body_block);
1065 WHILE curr_pos <= body_len LOOP
1066 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1067 line_num);
1068 curr_pos := curr_pos + g_line_size;
1069 line_num := line_num + 1;
1070 END LOOP;
1071 body_block := '
1072 INSERT INTO GCS_ENTRY_LINES_GT
1073 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
1074 , LINE_ITEM_Id, INTERCOMPANY_ID, FINANCIAL_ELEM_ID
1075 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
1076 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
1077 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
1078 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
1079 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
1080 , YTD_CREDIT_BALANCE_E , YTD_DEBIT_BALANCE_E
1081 , DESCRIPTION , YTD_BALANCE_E,RECEIVABLES_ORG_ID,
1082 PAYABLES_ORG_ID )
1083 SELECT /*+ ORDERED FULL(GIHG)
1084 INDEX(GIET GCS_INTERCO_ELM_TRX_U1)
1085 INDEX(GIM GCS_INTERCO_MEMBERS_U1)
1086 INDEX (FB FEM_BALANCES_P)
1087 USE_NL(GIET FB)*/
1088 gihg.entry_id, giet.company_cost_center_org_id
1089 , giet.line_item_id
1090 , giet.intercompany_id,
1091 ';
1092
1093 curr_pos := 1;
1094 body_len := LENGTH(body_block);
1095 WHILE curr_pos <= body_len LOOP
1096 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1097 line_num);
1098 curr_pos := curr_pos + g_line_size;
1099 line_num := line_num + 1;
1100 END LOOP;
1101
1102 line_num := GCS_DYNAMIC_UTIL_PKG.Build_interco_Comma_List(
1103 ' giet.',
1104 'DECODE(giet.elim_lob_id, NULL,giet.',
1105 'DECODE(p_lob_dim_col_name, ',
1106 ' giet.elim_lob_id, giet.',
1107 ')),',GCS_UTILITY_PKG.g_nl, ' NULL,', line_num);
1108
1109
1110 body_block := '
1111 SUM(NVL(fb.ytd_debit_balance_e,0))
1112 , SUM(NVL(fb.ytd_credit_balance_e,0))
1113 , MAX(gihg.rule_id)
1114 , (SUM(NVL(fb.ytd_credit_balance_e,0))
1115 - SUM(NVL(fb.ytd_debit_balance_e,0))),
1116 DECODE(MAX(gim.line_item_group), 1,
1117 giet.company_cost_center_org_id,
1118 giet.Intercompany_id),
1119 DECODE(MAX(gim.line_item_group), 2,
1120 giet.company_cost_center_org_id,
1121 giet.Intercompany_id)
1122 FROM GCS_INTERCO_HDR_GT gihg,
1123 GCS_INTERCO_ELM_TRX giet,
1124 GCS_INTERCO_MEMBERS gim,
1125 FEM_BALANCES fb
1126 WHERE giet.cal_period_id = p_cal_period_id
1127 AND giet.hierarchy_id = p_hierarchy_id
1128 AND gihg.currency_code IN (p_currency_code,''STAT'')
1129 AND giet.line_item_id = gim.line_item_id
1130 AND giet.src_entity_id = giet.target_entity_id
1131 AND giet.src_entity_id = gihg.source_entity_id
1132 AND giet.target_entity_id = gihg.target_entity_id
1133 AND gim.rule_id = gihg.rule_id
1134 AND fb.entity_id = p_entity_id
1135 AND giet.company_cost_center_org_id = fb.company_cost_center_org_id
1136 AND giet.intercompany_id = fb.intercompany_id
1137 AND giet.line_item_id = fb.line_item_id
1138 AND fb.currency_code = gihg.currency_code
1139 AND fb.cal_period_id = giet.cal_period_id
1140 AND fb.dataset_code = p_dataset_code
1141 AND fb.ledger_id = P_fem_ledger_id
1142 AND fb.source_system_code = 70
1143 ';
1144
1145 --******--
1146 curr_pos := 1;
1147 body_len := LENGTH(body_block);
1148 WHILE curr_pos <= body_len LOOP
1149 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1150 line_num);
1151 curr_pos := curr_pos + g_line_size;
1152 line_num := line_num + 1;
1153 END LOOP;
1154 --******--
1155
1156 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Join_List(
1157 ' AND fb.', ' = giet. ', GCS_UTILITY_PKG.g_nl, line_num);
1158 body_block := '
1159 AND NOT EXISTS (SELECT 1
1160 FROM GCS_INTERCO_ELM_TRX giet3,
1161 GCS_INTERCO_MEMBERS gim2
1162 WHERE giet3.hierarchy_id = p_hierarchy_id
1163 AND giet3.cal_period_id = p_cal_period_id
1164 AND giet3.src_entity_id = giet3.target_entity_id
1165 AND giet3.src_entity_id = giet.src_entity_id
1166 AND giet3.line_item_id = giet.line_item_id
1167 AND giet3.company_cost_center_org_id =
1168 giet.company_cost_center_org_id
1169 AND giet3.intercompany_id = giet.intercompany_id
1170 ';
1171
1172 --******--
1173 curr_pos := 1;
1174 body_len := LENGTH(body_block);
1178 curr_pos := curr_pos + g_line_size;
1175 WHILE curr_pos <= body_len LOOP
1176 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1177 line_num);
1179 line_num := line_num + 1;
1180 END LOOP;
1181 --******--
1182
1183
1184 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Join_List(
1185 ' AND giet3.', ' = giet. ',
1186 GCS_UTILITY_PKG.g_nl, line_num);
1187
1188 body_block :=
1189 ' AND gim2.line_item_id = giet3.line_item_id
1190 AND gim2.rule_id = gihg.rule_id
1191 AND gim2.line_item_group > gim.line_item_group)
1192 ';
1193
1194 --******--
1195 curr_pos := 1;
1196 body_len := LENGTH(body_block);
1197 WHILE curr_pos <= body_len LOOP
1198 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1199 line_num);
1200 curr_pos := curr_pos + g_line_size;
1201 line_num := line_num + 1;
1202 END LOOP;
1203 --******--
1204
1205
1206
1207 body_block :=
1208 '
1209 GROUP BY gihg.entry_id, giet.company_cost_center_org_id,
1210 giet.intercompany_id,
1211
1212 ';
1213 --******--
1214 curr_pos := 1;
1215 body_len := LENGTH(body_block);
1216 WHILE curr_pos <= body_len LOOP
1217 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1218 line_num);
1219 curr_pos := curr_pos + g_line_size;
1220 line_num := line_num + 1;
1221 END LOOP;
1222
1223 --******--
1224
1225 line_num := GCS_DYNAMIC_UTIL_PKG.Build_interco_Comma_List(
1226 ' giet.',
1227 'DECODE(giet.elim_lob_id, NULL,giet.',
1228 'DECODE(p_lob_dim_col_name, ',
1229 ' giet.elim_lob_id, giet.',
1230 ')),',GCS_UTILITY_PKG.g_nl, '', line_num);
1231
1232
1233 body_block :=
1234 '
1235 giet.line_item_id;
1236 ';
1237
1238 --******--
1239 curr_pos := 1;
1240 body_len := LENGTH(body_block);
1241 WHILE curr_pos <= body_len LOOP
1242 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1243 line_num);
1244 curr_pos := curr_pos + g_line_size;
1245 line_num := line_num + 1;
1246 END LOOP;
1247
1248 --******--
1249 body_block := '
1250 g_no_rows := NVL(SQL%ROWCOUNT,0);
1251
1252 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1253 FND_MESSAGE.Set_Name(''SQLGL'',''SHRD0117'');
1254 FND_MESSAGE.Set_Token(''NUM'',TO_CHAR(g_no_rows));
1255 FND_MESSAGE.Set_Token(''TABLE'',''GCS_ENTRY_LINES_GT'');
1256
1257 FND_LOG.String (fnd_log.level_procedure,
1258 g_pkg_name || ''.'' || l_api_name,
1259 ''SHRD0117: ''||FND_MESSAGE.get);
1260 END IF;
1261 End If; -- End of matching rule code in intracompany
1262 ';
1263
1264 --******--
1265 curr_pos := 1;
1266 body_len := LENGTH(body_block);
1267 WHILE curr_pos <= body_len LOOP
1268 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1269 line_num);
1270 curr_pos := curr_pos + g_line_size;
1271 line_num := line_num + 1;
1272 END LOOP;
1273
1274
1275 -- LAST statement -----------------
1276
1277
1278
1279 body_block :=
1280 '
1281 END If; -- End of elimination mode.
1282 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1283 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1284 g_pkg_name || ''.'' || l_api_name,
1285 GCS_UTILITY_PKG.g_module_success || l_api_name ||
1286 to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
1287 END IF;
1288 RETURN TRUE;
1289
1290 EXCEPTION
1291
1292 WHEN OTHERS THEN
1293 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1294 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1295 g_pkg_name || ''.'' || l_api_name,
1296 SUBSTR(SQLERRM, 1, 255));
1297 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1298 g_pkg_name || ''.'' || l_api_name,
1299 GCS_UTILITY_PKG.g_module_failure || l_api_name ||
1300 to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
1301 END IF;
1302 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure
1303 -- ||l_api_name || to_char(sysdate
1304 -- , '' DD-MON-YYYY HH:MI:SS''));
1305 RETURN FALSE;
1306 END INSR_INTERCO_LINES;
1307 ';
1308
1309 --******--
1310 curr_pos := 1;
1311 body_len := LENGTH(body_block);
1312 WHILE curr_pos <= body_len LOOP
1313 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1314 line_num);
1315 curr_pos := curr_pos + g_line_size;
1316 line_num := line_num + 1;
1317 END LOOP;
1318
1319 body_block := '
1320 --
1321 -- Function
1322 -- insr_sus_lines
1323
1324 -- Purpose
1325
1326 -- This routine is responsible for inserting the suspense plug in lines
1327 -- into the GCS_ENTRY_LINES table.
1328 ';
1329
1330 --******--
1331 curr_pos := 1;
1332 body_len := LENGTH(body_block);
1333 WHILE curr_pos <= body_len LOOP
1334 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1335 line_num);
1336 curr_pos := curr_pos + g_line_size;
1337 line_num := line_num + 1;
1338 END LOOP;
1339
1340
1341
1342 body_block :='
1343 FUNCTION INSR_SUSPENSE_LINES (p_hierarchy_id IN NUMBER,
1344 p_cal_period_id IN NUMBER,
1345 p_entity_id IN NUMBER,
1346 p_match_rule_code VARCHAR2,
1347 p_balance_type VARCHAR2,
1348 p_elim_mode IN VARCHAR2,
1349 P_Currency_code IN VARCHAR2,
1350 p_data_set_code IN NUMBER ,
1351 p_err_code OUT NOCOPY VARCHAR2,
1352 p_err_msg OUT NOCOPY VARCHAR2)
1353 RETURN BOOLEAN IS
1354
1355 l_api_name VARCHAR2(30) := ''INSR_SUSPENSE_LINES'';
1356
1357 -- Insert Suspense lines for unbalanced matched rows
1358 -- into GCS_ENTRY-LINES.
1359 BEGIN
1360
1361 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1362 fnd_log.STRING (fnd_log.level_procedure,
1363 g_pkg_name || ''.'' || l_api_name,
1364 gcs_utility_pkg.g_module_enter
1365 || '' ''
1366 || l_api_name
1367 || ''() ''
1368 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
1369 );
1370 END IF;
1371
1372 ';
1373 curr_pos := 1;
1374 body_len := LENGTH(body_block);
1375 WHILE curr_pos <= body_len LOOP
1376 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1377 line_num);
1378 curr_pos := curr_pos + g_line_size;
1379 line_num := line_num + 1;
1380 END LOOP;
1381
1382 body_block := '
1383
1384 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1385 fnd_log.STRING (fnd_log.level_procedure,
1386 g_pkg_name || ''.'' || l_api_name,
1387 '' Arguments passed to Insr_Suspense_Lines() ''
1388 ||'' Hierarchy_Id: ''||p_hierarchy_id
1389 ||'' Cal_Period_Id: ''||p_cal_period_id
1390 ||'' Entity_Id: ''||p_entity_id
1391 ||'' Match Rule Code: ''||p_match_rule_code
1392 ||'' Balance_Type: ''||p_balance_type
1393 ||'' Elim_Mode: ''||p_elim_mode
1394 ||'' Currency_Code: ''||p_currency_code
1395 ||'' Dataset Code:''||p_data_set_code);
1396
1397 END IF;
1398 ';
1399
1400 curr_pos := 1;
1401 body_len := LENGTH(body_block);
1402 WHILE curr_pos <= body_len LOOP
1403 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1404 line_num);
1405 curr_pos := curr_pos + g_line_size;
1406 line_num := line_num + 1;
1407 END LOOP;
1408
1409
1410 body_block := '
1411 IF ((P_ELIM_MODE = ''IE'') OR
1412 (P_ELIM_MODE = ''IA'')) THEN
1413
1414 IF (p_match_rule_code = ''COMPANY'') THEN
1415 g_no_rows := 0;
1416 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1417 fnd_log.STRING (fnd_log.level_procedure,
1418 g_pkg_name || ''.'' || l_api_name,
1419 ''Intercompany- Inserting necessary suspense lines''
1420 || '' into GCS_ENTRY_LINES_GT''
1421 || '' after matching by company''
1422 );
1423 END IF;
1424 ';
1425
1426 curr_pos := 1;
1427 body_len := LENGTH(body_block);
1428 WHILE curr_pos <= body_len LOOP
1432 line_num := line_num + 1;
1429 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1430 line_num);
1431 curr_pos := curr_pos + g_line_size;
1433 END LOOP;
1434
1435 body_block := '
1436 INSERT INTO GCS_ENTRY_LINES_GT
1437 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
1438 , FINANCIAL_ELEM_ID, LINE_ITEM_Id, INTERCOMPANY_ID
1439 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
1440 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
1441 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
1442 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
1443 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
1444 , YTD_DEBIT_BALANCE_E , YTD_CREDIT_BALANCE_E
1445 , DESCRIPTION, YTD_BALANCE_E)
1446 SELECT gihg1.entry_id
1447 , MAX(Receivables_org_id)
1448 , MAX(gihg1.sus_financial_elem_id), MAX(gihg1.sus_line_item_id)
1449 , MAX(payables_org_id),
1450 ';
1451
1452 --******--
1453 curr_pos := 1;
1454 body_len := LENGTH(body_block);
1455 WHILE curr_pos <= body_len LOOP
1456 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1457 line_num);
1458 curr_pos := curr_pos + g_line_size;
1459 line_num := line_num + 1;
1460 END LOOP;
1461
1462
1463 body_block := '
1464 MAX(gihg1.SUS_PRODUCT_ID), MAX(gihg1.SUS_NATURAL_ACCOUNT_ID),
1465 MAX(gihg1.SUS_CHANNEL_ID), MAX(gihg1.SUS_PROJECT_ID),
1466 MAX(gihg1.SUS_CUSTOMER_ID), MAX(gihg1.SUS_TASK_ID),
1467 MAX(gihg1.SUS_USER_DIM1_ID), MAX(gihg1.SUS_USER_DIM2_ID),
1468 MAX(gihg1.SUS_USER_DIM3_ID), MAX(gihg1.SUS_USER_DIM4_ID),
1469 MAX(gihg1.SUS_USER_DIM5_ID), MAX(gihg1.SUS_USER_DIM6_ID),
1470 MAX(gihg1.SUS_USER_DIM7_ID), MAX(gihg1.SUS_USER_DIM8_ID),
1471 MAX(gihg1.SUS_USER_DIM9_ID), MAX(gihg1.SUS_USER_DIM10_ID),
1472 ';
1473
1474 --******--
1475 curr_pos := 1;
1476 body_len := LENGTH(body_block);
1477 WHILE curr_pos <= body_len LOOP
1478 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1479 line_num);
1480 curr_pos := curr_pos + g_line_size;
1481 line_num := line_num + 1;
1482 END LOOP;
1483
1484
1485
1486 body_block := '
1487 DECODE(GREATEST(SUM(NVL(ytd_credit_balance_e,0)),
1488 SUM(NVL(ytd_debit_balance_e,0))),
1489 SUM(NVL(ytd_debit_balance_e,0)), 0,
1490 ABS(SUM(NVL(ytd_debit_balance_e,0))-
1491 SUM(NVL(ytd_credit_balance_e,0)))),
1492 DECODE(GREATEST(SUM(NVL(ytd_credit_balance_e,0)),
1493 SUM(NVL(ytd_debit_balance_e,0))),
1494 SUM(NVL(ytd_credit_balance_e,0)), 0,
1495 ABS(SUM(NVL(ytd_debit_balance_e,0))-
1496 SUM(NVL(ytd_credit_balance_e,0))))
1497 , ''SUSPENSE_LINE''
1498 , (DECODE(GREATEST(SUM(NVL(ytd_credit_balance_e,0)),
1499 SUM(NVL(ytd_debit_balance_e,0))),
1500 SUM(NVL(ytd_debit_balance_e,0)), 0,
1501 ABS(SUM(NVL(ytd_debit_balance_e,0))-
1502 SUM(NVL(ytd_credit_balance_e,0))))-
1503 DECODE(GREATEST(SUM(NVL(ytd_credit_balance_e,0)),
1504 SUM(NVL(ytd_debit_balance_e,0))),
1505 SUM(NVL(ytd_credit_balance_e,0)), 0,
1506 ABS(SUM(NVL(ytd_debit_balance_e,0))-
1507 SUM(NVL(ytd_credit_balance_e,0)))))
1508 ';
1509 --******--
1510 curr_pos := 1;
1511 body_len := LENGTH(body_block);
1512 WHILE curr_pos <= body_len LOOP
1513 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1514 line_num);
1515 curr_pos := curr_pos + g_line_size;
1516 line_num := line_num + 1;
1517 END LOOP;
1518
1519 body_block := '
1520 FROM GCS_ENTRY_LINES_GT gel,
1521 GCS_INTERCO_HDR_GT gihg1,
1522 fem_cctr_orgs_attr fcoa2 ,
1523 fem_cctr_orgs_attr fcoa3
1524 WHERE gihg1.entry_id = gel.entry_id
1525 AND gel.receivables_org_id =
1526 fcoa2.company_cost_center_org_id
1527 AND fcoa2.attribute_id =
1528 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COMPANY'').attribute_id
1529 AND fcoa2.version_id =
1530 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COMPANY'').version_id
1531 AND gel.payables_org_id = fcoa3.company_cost_center_org_id
1532 AND fcoa3.attribute_id =
1533 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COMPANY'').attribute_id
1534 AND fcoa3.version_id =
1535 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COMPANY'').version_id
1536 GROUP BY gihg1.entry_id, fcoa2.dim_attribute_numeric_member,
1537 fcoa3.dim_attribute_numeric_member;
1538 ';
1539
1540
1544 WHILE curr_pos <= body_len LOOP
1541 --******--
1542 curr_pos := 1;
1543 body_len := LENGTH(body_block);
1545 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1546 line_num);
1547 curr_pos := curr_pos + g_line_size;
1548 line_num := line_num + 1;
1549 END LOOP;
1550
1551
1552 body_block := '
1553 g_no_rows := NVL(SQL%ROWCOUNT,0);
1554
1555 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1556 FND_MESSAGE.Set_Name(''SQLGL'',''SHRD0117'');
1557 FND_MESSAGE.Set_Token(''NUM'',TO_CHAR(g_no_rows));
1558 FND_MESSAGE.Set_Token(''TABLE'',''GCS_ENTRY_LINES_GT'');
1559
1560 FND_LOG.String (fnd_log.level_procedure,
1561 g_pkg_name || ''.'' || l_api_name,
1562 ''SHRD0117: ''||FND_MESSAGE.get);
1563 END IF;
1564 ';
1565 --******--
1566 curr_pos := 1;
1567 body_len := LENGTH(body_block);
1568 WHILE curr_pos <= body_len LOOP
1569 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1570 line_num);
1571 curr_pos := curr_pos + g_line_size;
1572 line_num := line_num + 1;
1573 END LOOP;
1574
1575
1576 body_block := '
1577 ELSIF (p_match_rule_code = ''ORGANIZATION'') THEN
1578 -- In Intercompany option
1579 g_no_rows := 0;
1580 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1581 fnd_log.STRING (fnd_log.level_procedure,
1582 g_pkg_name || ''.'' || l_api_name,
1583 ''Intercompany- Inserting necessary suspense lines''
1584 || '' into GCS_ENTRY_LINES_GT''
1585 || '' after matching by org ''
1586 );
1587 END IF;
1588 ';
1589
1590 curr_pos := 1;
1591 body_len := LENGTH(body_block);
1592 WHILE curr_pos <= body_len LOOP
1593 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1594 line_num);
1595 curr_pos := curr_pos + g_line_size;
1596 line_num := line_num + 1;
1597 END LOOP;
1598
1599 body_block := '
1600 INSERT INTO GCS_ENTRY_LINES_GT
1601 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
1602 , FINANCIAL_ELEM_ID, LINE_ITEM_Id, INTERCOMPANY_ID
1603 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
1604 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
1605 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
1606 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
1607 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
1608 , YTD_DEBIT_BALANCE_E , YTD_CREDIT_BALANCE_E
1609 , DESCRIPTION , YTD_BALANCE_E)
1610 SELECT gihg1.entry_id, MAX(Receivables_org_id),
1611 MAX(gihg1.sus_financial_elem_id),
1612 MAX(gihg1.sus_line_item_id), MAX(payables_org_id),
1613 ';
1614
1615 --******--
1616 curr_pos := 1;
1617 body_len := LENGTH(body_block);
1618 WHILE curr_pos <= body_len LOOP
1619 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1620 line_num);
1621 curr_pos := curr_pos + g_line_size;
1622 line_num := line_num + 1;
1623 END LOOP;
1624
1625 body_block := '
1626 MAX(gihg1.SUS_PRODUCT_ID), MAX(gihg1.SUS_NATURAL_ACCOUNT_ID),
1627 MAX(gihg1.SUS_CHANNEL_ID), MAX(gihg1.SUS_PROJECT_ID),
1628 MAX(gihg1.SUS_CUSTOMER_ID), MAX(gihg1.SUS_TASK_ID),
1629 MAX(gihg1.SUS_USER_DIM1_ID), MAX(gihg1.SUS_USER_DIM2_ID),
1630 MAX(gihg1.SUS_USER_DIM3_ID), MAX(gihg1.SUS_USER_DIM4_ID),
1631 MAX(gihg1.SUS_USER_DIM5_ID), MAX(gihg1.SUS_USER_DIM6_ID),
1632 MAX(gihg1.SUS_USER_DIM7_ID), MAX(gihg1.SUS_USER_DIM8_ID),
1633 MAX(gihg1.SUS_USER_DIM9_ID), MAX(gihg1.SUS_USER_DIM10_ID),
1634 ';
1635
1636 --******--
1637 curr_pos := 1;
1638 body_len := LENGTH(body_block);
1639 WHILE curr_pos <= body_len LOOP
1640 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1641 line_num);
1642 curr_pos := curr_pos + g_line_size;
1643 line_num := line_num + 1;
1644 END LOOP;
1645
1646 body_block := ' DECODE(GREATEST(SUM(NVL(ytd_credit_balance_e,0)),
1647 SUM(NVL(ytd_debit_balance_e,0))),
1648 SUM(NVL(ytd_debit_balance_e,0)), 0,
1649 ABS(SUM(NVL(ytd_debit_balance_e,0))-
1650 SUM(NVL(ytd_credit_balance_e,0)))),
1651 DECODE(GREATEST(SUM(NVL(ytd_credit_balance_e,0)),
1652 SUM(NVL(ytd_debit_balance_e,0))),
1653 SUM(NVL(ytd_credit_balance_e,0)), 0,
1654 ABS(SUM(NVL(ytd_debit_balance_e,0))-
1655 SUM(NVL(ytd_credit_balance_e,0))))
1656 , ''SUSPENSE_LINE'',
1657 (DECODE(GREATEST(SUM(NVL(ytd_credit_balance_e,0)),
1658 SUM(NVL(ytd_debit_balance_e,0))),
1659 SUM(NVL(ytd_debit_balance_e,0)), 0,
1660 ABS(SUM(NVL(ytd_debit_balance_e,0))-
1661 SUM(NVL(ytd_credit_balance_e,0))))-
1665 ABS(SUM(NVL(ytd_debit_balance_e,0))-
1662 DECODE(GREATEST(SUM(NVL(ytd_credit_balance_e,0)),
1663 SUM(NVL(ytd_debit_balance_e,0))),
1664 SUM(NVL(ytd_credit_balance_e,0)), 0,
1666 SUM(NVL(ytd_credit_balance_e,0)))))
1667 ';
1668
1669 --******--
1670 curr_pos := 1;
1671 body_len := LENGTH(body_block);
1672 WHILE curr_pos <= body_len LOOP
1673 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1674 line_num);
1675 curr_pos := curr_pos + g_line_size;
1676 line_num := line_num + 1;
1677 END LOOP;
1678 body_block := ' FROM GCS_ENTRY_LINES_GT gel,
1679 GCS_INTERCO_HDR_GT gihg1
1680 WHERE gihg1.entry_id = gel.entry_id
1681 GROUP BY gihg1.entry_id, receivables_org_id, payables_org_id;
1682 ';
1683
1684 --******--
1685 curr_pos := 1;
1686 body_len := LENGTH(body_block);
1687 WHILE curr_pos <= body_len LOOP
1688 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1689 line_num);
1690 curr_pos := curr_pos + g_line_size;
1691 line_num := line_num + 1;
1692 END LOOP;
1693
1694 body_block := '
1695 g_no_rows := NVL(SQL%ROWCOUNT,0);
1696
1697 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1698 FND_MESSAGE.Set_Name(''SQLGL'',''SHRD0117'');
1699 FND_MESSAGE.Set_Token(''NUM'',TO_CHAR(g_no_rows));
1700 FND_MESSAGE.Set_Token(''TABLE'',''GCS_ENTRY_LINES_GT'');
1701
1702 FND_LOG.String (fnd_log.level_procedure,
1703 g_pkg_name || ''.'' || l_api_name,
1704 ''SHRD0117: ''||FND_MESSAGE.get);
1705
1706 END IF;
1707 END IF; -- Ends matching rule code in intercompany.
1708 END IF; --Added to end the mode IF
1709 ';
1710 --******--
1711 curr_pos := 1;
1712 body_len := LENGTH(body_block);
1713 WHILE curr_pos <= body_len LOOP
1714 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1715 line_num);
1716 curr_pos := curr_pos + g_line_size;
1717 line_num := line_num + 1;
1718 END LOOP;
1719
1720 body_block :='
1721 g_no_rows := 0;
1722 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1723 fnd_log.STRING (fnd_log.level_procedure,
1724 g_pkg_name || ''.'' || l_api_name,
1725 ''Intercompany- Inserting ''
1726 || '' into GCS_ENTRY_LINES''
1727 || '' after processing''
1728 );
1729 END IF;
1730 ';
1731
1732 curr_pos := 1;
1733 body_len := LENGTH(body_block);
1734 WHILE curr_pos <= body_len LOOP
1735 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1736 line_num);
1737 curr_pos := curr_pos + g_line_size;
1738 line_num := line_num + 1;
1739 END LOOP;
1740
1741 body_block := '
1742 INSERT INTO GCS_ENTRY_LINES
1743 ( ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
1744 , FINANCIAL_ELEM_ID, LINE_ITEM_Id, INTERCOMPANY_ID
1745 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
1746 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
1747 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
1748 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
1749 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
1750 , YTD_DEBIT_BALANCE_E , YTD_CREDIT_BALANCE_E
1751 , CREATION_DATE , CREATED_BY , LAST_UPDATE_DATE
1752 , LAST_UPDATED_BY, LAST_UPDATE_LOGIN
1753 , DESCRIPTION, YTD_BALANCE_E)
1754 ';
1755 curr_pos := 1;
1756 body_len := LENGTH(body_block);
1757 WHILE curr_pos <= body_len LOOP
1758 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1759 line_num);
1760 curr_pos := curr_pos + g_line_size;
1761 line_num := line_num + 1;
1762 END LOOP;
1763 body_block := '
1764 SELECT ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
1765 , FINANCIAL_ELEM_ID, LINE_ITEM_Id, INTERCOMPANY_ID
1766 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
1767 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
1768 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
1769 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
1770 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
1771 , YTD_DEBIT_BALANCE_E , YTD_CREDIT_BALANCE_E
1772 , SYSDATE, g_fnd_user_id
1773 , SYSDATE, g_fnd_user_id, g_fnd_login_id
1774 , DESCRIPTION, YTD_BALANCE_E
1775 FROM GCS_ENTRY_LINES_GT
1776 WHERE DESCRIPTION <> ''SUSPENSE_LINE''
1777 ';
1778
1779 curr_pos := 1;
1780 body_len := LENGTH(body_block);
1781 WHILE curr_pos <= body_len LOOP
1782 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1783 line_num);
1784 curr_pos := curr_pos + g_line_size;
1785 line_num := line_num + 1;
1786 END LOOP;
1787
1788 body_block := '
1789 UNION ALL
1790 SELECT ENTRY_ID, COMPANY_COST_CENTER_ORG_ID
1794 , USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID
1791 , FINANCIAL_ELEM_ID, LINE_ITEM_Id, INTERCOMPANY_ID
1792 , PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID
1793 , PROJECT_ID, CUSTOMER_ID, TASK_ID, USER_DIM1_ID
1795 , USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID
1796 , USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID
1797 , YTD_DEBIT_BALANCE_E , YTD_CREDIT_BALANCE_E
1798 , SYSDATE, g_fnd_user_id
1799 , SYSDATE, g_fnd_user_id, g_fnd_login_id
1800 , DESCRIPTION, YTD_BALANCE_E
1801 FROM GCS_ENTRY_LINES_GT
1802 WHERE (DESCRIPTION = ''SUSPENSE_LINE'' AND YTD_BALANCE_E <> 0);
1803 ';
1804
1805 curr_pos := 1;
1806 body_len := LENGTH(body_block);
1807 WHILE curr_pos <= body_len LOOP
1808 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1809 line_num);
1810 curr_pos := curr_pos + g_line_size;
1811 line_num := line_num + 1;
1812 END LOOP;
1813
1814 body_block := '
1815 g_no_rows := NVL(SQL%ROWCOUNT,0);
1816
1817 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1818 FND_MESSAGE.Set_Name(''SQLGL'',''SHRD0117'');
1819 FND_MESSAGE.Set_Token(''NUM'',TO_CHAR(g_no_rows));
1820 FND_MESSAGE.Set_Token(''TABLE'',''GCS_ENTRY_LINES'');
1821
1822 FND_LOG.String (fnd_log.level_procedure,
1823 g_pkg_name || ''.'' || l_api_name,
1824 ''SHRD0117: ''||FND_MESSAGE.get);
1825
1826 END IF;
1827 ';
1828
1829 curr_pos := 1;
1830 body_len := LENGTH(body_block);
1831 WHILE curr_pos <= body_len LOOP
1832 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1833 line_num);
1834 curr_pos := curr_pos + g_line_size;
1835 line_num := line_num + 1;
1836 END LOOP;
1837
1838
1839 --*********** very last piece of this function ***********---
1840
1841 body_block := '
1842
1843 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1844 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1845 g_pkg_name || ''.'' || l_api_name,
1846 GCS_UTILITY_PKG.g_module_success || l_api_name ||
1847 to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
1848 END IF;
1849 Return TRUE;
1850 ';
1851
1852 --******--
1853 curr_pos := 1;
1854 body_len := LENGTH(body_block);
1855 WHILE curr_pos <= body_len LOOP
1856 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1857 line_num);
1858 curr_pos := curr_pos + g_line_size;
1859 line_num := line_num + 1;
1860 END LOOP;
1861
1862 -- LAST statement -----------------
1863
1864 body_block :=
1865 '
1866
1867
1868 EXCEPTION
1869
1870 WHEN OTHERS THEN
1871 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1872 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1873 g_pkg_name || ''.'' || l_api_name,
1874 SUBSTR(SQLERRM, 1, 255));
1875 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1876 g_pkg_name || ''.'' || l_api_name,
1877 GCS_UTILITY_PKG.g_module_failure || l_api_name ||
1878 to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
1879 END IF;
1880
1881 p_err_code := SQLCODE;
1882 p_err_msg := SQLERRM;
1883
1884
1885 RETURN FALSE;
1886 END INSR_SUSPENSE_LINES;
1887 ';
1888
1889 --******--
1890 curr_pos := 1;
1891 body_len := LENGTH(body_block);
1892 WHILE curr_pos <= body_len LOOP
1893 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1894 line_num);
1895 curr_pos := curr_pos + g_line_size;
1896 line_num := line_num + 1;
1897 END LOOP;
1898
1899 body_block := '
1900 -- Procedure
1901 -- Insert_Interco_Trx
1902 -- Purpose
1903 -- Inserts eligible elimination transactions
1904 -- into GCS_INTERCO_ELM_TRX after dataprep operation.
1905 -- This procedure will be called from the Datapre package.
1906 -- Arguments
1907 -- P_entry_id Entry_id (created by dataprep) for the
1908 -- monetary currency
1909 -- p_stat_entry_id Entry id (created by dataprep) for the stat currency
1910 -- p_Hierarchy_id Hierarchy_id for the above entries.
1911 -- This hierarchy id will
1912 ';
1913
1914 --******--
1915 curr_pos := 1;
1916 body_len := LENGTH(body_block);
1917 WHILE curr_pos <= body_len LOOP
1918 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1919 line_num);
1920 curr_pos := curr_pos + g_line_size;
1921 line_num := line_num + 1;
1922 END LOOP;
1923
1924 body_block := '
1925 -- be used to determine the matching rule like
1926 -- match by organization, match by company,
1927 -- or match by cost center.
1928 -- x_errbuf Returns error message to concurrent manager,
1929 -- if there are any errors.
1933 -- Synatx for Calling from external package.
1930 -- x_retcode Returns error code to concurrent manager,
1931 -- if there are any errors.
1932
1934
1935 -- GCS_INTERCO_DYNAMIC_PKG.INSERT_INTERCO_TRX(1112,
1936 -- 1114,
1937 -- 10041, err, err_code)
1938 --
1939
1940 --
1941 ';
1942
1943
1944 --******--
1945 curr_pos := 1;
1946 body_len := LENGTH(body_block);
1947 WHILE curr_pos <= body_len LOOP
1948 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1949 line_num);
1950 curr_pos := curr_pos + g_line_size;
1951 line_num := line_num + 1;
1952 END LOOP;
1953
1954 --@@
1955 body_block := '
1956
1957
1958 PROCEDURE INSERT_INTERCO_TRX(p_entry_id In NUMBER,
1959 p_stat_entry_id IN NUMBER,
1960 p_hierarchy_id IN NUMBER,
1961 p_period_end_date IN DATE,
1962 x_errbuf OUT NOCOPY VARCHAR2,
1963 x_retcode OUT NOCOPY VARCHAR2) IS
1964
1965 PRAGMA AUTONOMOUS_TRANSACTION;
1966
1967 l_api_name VARCHAR2(50) := ''INSERT_INTERCO_TRX'';
1968 x_match_rule_code VARCHAR2(30);
1969 l_no_rows NUMBER:= 0;
1970 x_intercompany_org_code VARCHAR2(30);
1971 x_specific_intercompany_id NUMBER;
1972 x_lob_reporting_enabled VARCHAR2(30);
1973 x_lob_hierarchy_obj_id NUMBER;
1974 x_lob_dim_column_name VARCHAR2(30);
1975 l_valid_hierarchy_id NUMBER;
1976
1977
1978 NO_MATCH_RULE_CODE Exception;
1979 Hierarchy_check_failed Exception;
1980
1981
1982
1983
1984
1985 BEGIN
1986 ';
1987
1988 --******--
1989 curr_pos := 1;
1990 body_len := LENGTH(body_block);
1991 WHILE curr_pos <= body_len LOOP
1992 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1993 line_num);
1994 curr_pos := curr_pos + g_line_size;
1995 line_num := line_num + 1;
1996 END LOOP;
1997
1998
1999 body_block := '
2000 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2001
2002 fnd_log.STRING (fnd_log.level_procedure,
2003 g_pkg_name || ''.'' || l_api_name,
2004 gcs_utility_pkg.g_module_enter
2005 || '' ''
2006 || l_api_name
2007 || ''() ''
2008 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
2009 );
2010 END IF;
2011
2012 -- Get the matching rule for the given hierarchy_id
2013 -- for matching intercompany eliminations such as by organization,
2014 -- by company or by cost center.
2015
2016 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2017 fnd_log.STRING (fnd_log.level_procedure,
2018 g_pkg_name || ''.'' || l_api_name,
2019 ''Get the matching rule information''
2020 );
2021 END IF;
2022 ';
2023
2024 --******--
2025 curr_pos := 1;
2026 body_len := LENGTH(body_block);
2027 WHILE curr_pos <= body_len LOOP
2028 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2029 line_num);
2030 curr_pos := curr_pos + g_line_size;
2031 line_num := line_num + 1;
2032 END LOOP;
2033
2034 body_block := '
2035 BEGIN
2036
2037 SELECT ghb.ie_by_org_code,
2038 DECODE(gcb.specific_intercompany_id, NULL,
2039 ''N'', ''SPECIFIC_VALUE''),
2040 gcb.specific_intercompany_id,
2041 ghb.lob_reporting_enabled_flag,
2042 ghb.lob_hierarchy_obj_id,
2043 ghb.lob_dim_column_name
2044 INTO x_match_rule_code,
2045 x_intercompany_org_code,
2046 x_specific_intercompany_id,
2047 x_lob_reporting_enabled,
2048 x_lob_hierarchy_obj_id,
2049 x_lob_dim_column_name
2050 FROM GCS_HIERARCHIES_B ghb, gcs_categories_b gcb
2051 WHERE ghb.hierarchy_id = p_hierarchy_id
2052 AND gcb.category_code = ''INTRACOMPANY''
2053 AND rownum = 1;
2054
2055
2056 EXCEPTION
2057
2058 WHEN NO_DATA_FOUND Then
2059 Raise NO_MATCH_RULE_CODE;
2060 END;
2061
2062 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2063 fnd_log.STRING (fnd_log.level_procedure,
2064 g_pkg_name || ''.'' || l_api_name,
2065 '' Arguments Hierarchy_id :''||p_hierarchy_id
2066 ||'' Stat Entry Id: ''|| p_stat_entry_id
2067 ||'' Entry id: ''||p_entry_id
2068 ||'' Matching Rule: ''||x_match_rule_code
2072 ||x_specific_intercompany_id
2069 ||'' Period End Date: ''||p_period_end_date
2070 ||'' Intercompany code: ''||x_intercompany_org_code
2071 ||'' Spec. interco value: ''
2073 ||'' LOB Reporting Enabled: ''
2074 || x_lob_reporting_enabled
2075 ||'' Cost Center Hierarchy Obj Id: ''
2076 ||x_lob_hierarchy_obj_id
2077 );
2078
2079 END IF;
2080
2081 ';
2082
2083 --******--
2084 curr_pos := 1;
2085 body_len := LENGTH(body_block);
2086 WHILE curr_pos <= body_len LOOP
2087 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2088 line_num);
2089 curr_pos := curr_pos + g_line_size;
2090 line_num := line_num + 1;
2091 END LOOP;
2092
2093 body_block := '
2094
2095 IF (x_match_rule_code = ''ORGANIZATION'') THEN
2096
2097 IF ((x_lob_reporting_enabled = ''Y'')
2098 AND (x_lob_hierarchy_obj_id IS NOT NULL)) THEN
2099
2100 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2101 fnd_log.STRING (fnd_log.level_procedure,
2102 g_pkg_name || ''.'' || l_api_name,
2103 ''Entered into LOB support block''
2104 );
2105 END IF;
2106
2107 BEGIN
2108
2109 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2110 fnd_log.STRING (fnd_log.level_procedure,
2111 g_pkg_name || ''.'' || l_api_name,
2112 ''Checking Hierarchy date effectivity''
2113 );
2114 END IF;
2115
2116 SELECT object_definition_id INTO l_valid_hierarchy_id
2117 FROM FEM_OBJECT_DEFINITION_B fod
2118 WHERE fod.object_id = x_lob_hierarchy_obj_id
2119 AND (p_period_end_date
2120 BETWEEN NVL(fod.effective_start_date,
2121 TO_DATE(''01/01/1950'',''MM/DD/YYYY''))
2122 AND NVL(fod.effective_end_date,
2123 TO_DATE(''12/31/9999'',''MM/DD/YYYY'')));
2124
2125
2126 EXCEPTION
2127 WHEN NO_DATA_FOUND THEN
2128 l_valid_hierarchy_id :=0;
2129
2130 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2131 fnd_log.STRING (fnd_log.level_procedure,
2132 g_pkg_name || ''.'' || l_api_name,
2133 ''Hierarchy date effectivity failed''
2134 || '' either due to wrong hierarchy or ''
2135 ||'' period end date is not falling ''
2136 ||'' start date and end date''
2137 );
2138 END IF;
2139 null;
2140 --Raise Hierarchy_Check_Failed;
2141
2142 WHEN OTHERS THEN
2143 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2144 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
2145 g_pkg_name || ''.'' || l_api_name
2146 ||'' Hierarchy_Check '',
2147 SUBSTR(SQLERRM, 1, 255));
2148
2149 END IF;
2150
2151 END;
2152
2153 ';
2154
2155 --******--
2156 curr_pos := 1;
2157 body_len := LENGTH(body_block);
2158 WHILE curr_pos <= body_len LOOP
2159 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2160 line_num);
2161 curr_pos := curr_pos + g_line_size;
2162 line_num := line_num + 1;
2163 END LOOP;
2164
2165 body_block := '
2166 IF (x_intercompany_org_code = ''SPECIFIC_VALUE'') THEN
2167
2168 l_no_rows := 0;
2169
2170 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2171 fnd_log.STRING (fnd_log.level_procedure,
2172 g_pkg_name || ''.'' || l_api_name,
2173 ''Inserting intercompany transactions for matching by''
2174 || '' organization into GCS_INTERCO_ELM_TRX''
2175 || '' - LOB REPORTING ENABLED '');
2176 END IF;
2177 Insert INTO gcs_interco_elm_trx
2178 (hierarchy_id, cal_period_id, company_cost_center_org_id,
2179 src_entity_id, src_company_id, src_cost_center_id,
2180 intercompany_id, target_company_id,
2181 target_cost_center_id, target_entity_id,
2182 currency_code, line_item_id, financial_elem_id,
2183 product_id, natural_account_id, channel_id,
2184 project_id, customer_id, task_id,
2185 user_dim1_id, user_dim2_id, user_dim3_id,
2186 user_dim4_id, user_dim5_id, user_dim6_id,
2187 user_dim7_id, user_dim8_id, user_dim9_id,
2188 user_dim10_id,creation_date,
2189 created_by, last_update_date, last_updated_by,
2190 last_update_login, elim_lob_id)
2191 ';
2192
2193 --******--
2194 curr_pos := 1;
2195 body_len := LENGTH(body_block);
2199 curr_pos := curr_pos + g_line_size;
2196 WHILE curr_pos <= body_len LOOP
2197 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2198 line_num);
2200 line_num := line_num + 1;
2201 END LOOP;
2202
2203 body_block := '
2204 SELECT DISTINCT geh.hierarchy_id, geh.start_cal_period_id,
2205 gel.company_cost_center_org_id,
2206 geo1.entity_id, NULL, NULL, gel.intercompany_id,
2207 NULL,NULL, geo.entity_id, geh.currency_code,
2208 gel.line_item_id,
2209 ';
2210
2211 --******--
2212 curr_pos := 1;
2213 body_len := LENGTH(body_block);
2214 WHILE curr_pos <= body_len LOOP
2215 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2216 line_num);
2217 curr_pos := curr_pos + g_line_size;
2218 line_num := line_num + 1;
2219 END LOOP;
2220
2221
2222 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
2223 'gel.', GCS_UTILITY_PKG.g_nl, ' NULL, ', line_num);
2224
2225 body_block := '
2226 SYSDATE, g_fnd_user_id,
2227 SYSDATE, g_fnd_user_id,
2228 g_fnd_login_id,
2229 DECODE(fcoa2.dim_attribute_numeric_member,
2230 fcoa3.dim_attribute_numeric_member,
2231 fcoa2.dim_attribute_numeric_member,
2232 fcca.dim_attribute_numeric_member)
2233 FROM GCS_ENTRY_HEADERS geh,
2234 GCS_ENTRY_LINES gel,
2235 GCS_ENTITY_CCTR_ORGS geo,
2236 GCS_ENTITY_CCTR_ORGS geo1,
2237 GCS_CONS_RELATIONSHIPS gcr,
2238 GCS_CONS_RELATIONSHIPS gcr1,
2239 fem_cctr_orgs_attr fcoa2,
2240 fem_cctr_orgs_attr fcoa3,
2241 fem_user_dim1_attr fcca
2242 WHERE geh.entry_id IN (p_entry_id, p_stat_entry_id)
2243 AND geh.entry_id = gel.entry_id
2244 AND gel.intercompany_id <> x_specific_intercompany_id
2245 AND gel.intercompany_id =
2246 geo.company_cost_center_org_id
2247 AND gel.company_cost_center_org_id =
2248 geo1.company_cost_center_org_id
2249 AND geh.hierarchy_id = gcr.hierarchy_id
2250 AND (p_period_end_date
2251 BETWEEN NVL(gcr.start_date, p_period_end_date)
2252 AND NVL(gcr.end_date, p_period_end_date))
2253 AND gcr.child_entity_id = geo.entity_id
2254 AND gcr.actual_ownership_flag =''Y''
2255 AND gcr.dominant_parent_flag = ''Y''
2256 AND geh.hierarchy_id = gcr1.hierarchy_id
2257 AND (p_period_end_date
2258 BETWEEN NVL(gcr1.start_date, p_period_end_date)
2259 AND NVL(gcr1.end_date, p_period_end_date))
2260 AND gcr1.child_entity_id = geo1.entity_id
2261 AND gcr1.actual_ownership_flag =''Y''
2262 AND gcr1.dominant_parent_flag = ''Y''
2263 ';
2264
2265 --******--
2266 curr_pos := 1;
2267 body_len := LENGTH(body_block);
2268 WHILE curr_pos <= body_len LOOP
2269 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2270 line_num);
2271 curr_pos := curr_pos + g_line_size;
2272 line_num := line_num + 1;
2273 END LOOP;
2274
2275 body_block :='
2276 AND gel.company_cost_center_org_id =
2277 fcoa2.company_cost_center_org_id
2278 AND fcoa2.attribute_id =
2279 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COST_CENTER'').attribute_id
2280 AND fcoa2.version_id =
2281 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COST_CENTER'').version_id
2282 AND gel.intercompany_id = fcoa3.company_cost_center_org_id
2283 AND fcoa3.attribute_id =
2284 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COST_CENTER'').attribute_id
2285 AND fcoa3.version_id =
2286 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COST_CENTER'').version_id
2287 AND fcca.attribute_id = gcs_utility_pkg.g_dimension_attr_info(''USER_DIM1_ID-ELIMINATION_LOB'').attribute_id
2288 AND fcca.version_id = gcs_utility_pkg.g_dimension_attr_info(''USER_DIM1_ID-ELIMINATION_LOB'').version_id
2289 ';
2290 --******--
2291 curr_pos := 1;
2292 body_len := LENGTH(body_block);
2293 WHILE curr_pos <= body_len LOOP
2294 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2295 line_num);
2296 curr_pos := curr_pos + g_line_size;
2297 line_num := line_num + 1;
2298 END LOOP;
2299 body_block := '
2300 AND fcca.user_dim1_id = (
2301 SELECT fcch1.parent_id
2302 FROM fem_user_dim1_hier fcch1,
2303 fem_user_dim1_hier fcch2
2304 WHERE fcch1.child_id =
2305 fcoa2.dim_attribute_numeric_member
2306 AND fcch1.hierarchy_obj_def_id =
2307 l_valid_hierarchy_id
2308 AND fcch1.parent_id <> fcch1.child_id
2312 AND fcch2.hierarchy_obj_def_id =
2309 -- *** To eliminte self rows
2310 AND fcch2.child_id =
2311 fcoa3.dim_attribute_numeric_member
2313 l_valid_hierarchy_id
2314 AND fcch2.parent_id <> fcch2.child_id
2315 -- *** To eliminte self rows
2316 AND fcch1.parent_id = fcch2.parent_id
2317 AND fcch1.parent_depth_num =
2318 (SELECT MAX(fcch3.parent_depth_num)
2319 FROM fem_user_dim1_hier fcch3,
2320 fem_user_dim1_hier fcch4
2321 WHERE fcch3.child_id =
2322 fcoa2.dim_attribute_numeric_member
2323 AND fcch3.hierarchy_obj_def_id =
2324 l_valid_hierarchy_id
2325 AND fcch3.parent_id <> fcch3.child_id
2326 -- *** To eliminte self rows
2327 AND fcch4.child_id =
2328 fcoa3.dim_attribute_numeric_member
2329 AND fcch4.hierarchy_obj_def_id =
2330 l_valid_hierarchy_id
2331 AND fcch4.parent_id <> fcch4.child_id
2332 -- *** To eliminte self rows
2333 AND fcch3.parent_id = fcch4.parent_id
2334 ))
2335 ';
2336 --******--
2337 curr_pos := 1;
2338 body_len := LENGTH(body_block);
2339 WHILE curr_pos <= body_len LOOP
2340 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2341 line_num);
2342 curr_pos := curr_pos + g_line_size;
2343 line_num := line_num + 1;
2344 END LOOP;
2345 body_block := '
2346 AND NOT EXISTS ( SELECT 1 FROM GCS_INTERCO_ELM_TRX giet1
2347 WHERE giet1.hierarchy_id = geh.hierarchy_id
2348 AND giet1.cal_period_id = geh.start_cal_period_id
2349 AND giet1.company_cost_center_org_id =
2350 gel.company_cost_center_org_id
2351 AND giet1.src_entity_id = geo1.entity_id
2352 AND giet1.target_entity_id = geo.entity_id
2353 AND giet1.intercompany_id = gel.intercompany_id
2354 ';
2355
2356 --******--
2357 curr_pos := 1;
2358 body_len := LENGTH(body_block);
2359 WHILE curr_pos <= body_len LOOP
2360 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2361 line_num);
2362 curr_pos := curr_pos + g_line_size;
2363 line_num := line_num + 1;
2364 END LOOP;
2365
2366
2367 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Join_List(
2368 ' AND giet1.', ' = gel. ',
2369 GCS_UTILITY_PKG.g_nl, line_num);
2370
2371
2372
2373 body_block :=
2374 ' AND giet1.line_item_id = gel.line_item_id) ;
2375
2376 l_no_rows := NVL(SQL%ROWCOUNT,0);
2377
2378 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2379 FND_MESSAGE.Set_Name(''SQLGL'',''SHRD0117'');
2380 FND_MESSAGE.Set_Token(''NUM'',TO_CHAR(l_no_rows));
2381 FND_MESSAGE.Set_Token(''TABLE'',''GCS_INTERCO_ELM_TRX'');
2382
2383 FND_LOG.String (fnd_log.level_procedure,
2384 g_pkg_name || ''.'' || l_api_name,
2385 ''SHRD0117: ''||FND_MESSAGE.get);
2386 --FND_FILE.Put_Line(FND_FILE.Log,''SHRD0117: ''||FND_MESSAGE.get);
2387 END IF;
2388 ';
2389
2390 --******--
2391 curr_pos := 1;
2392 body_len := LENGTH(body_block);
2393 WHILE curr_pos <= body_len LOOP
2394 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2395 line_num);
2396 curr_pos := curr_pos + g_line_size;
2397 line_num := line_num + 1;
2398 END LOOP;
2399
2400 body_block := '
2401 ELSE
2402
2403 l_no_rows := 0;
2404
2405 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2406 fnd_log.STRING (fnd_log.level_procedure,
2407 g_pkg_name || ''.'' || l_api_name,
2408 ''Inserting intercompany transactions for matching by''
2409 || '' organization into GCS_INTERCO_ELM_TRX''
2410 || '' - LOB REPORTING ENABLED '');
2411 END IF;
2412 Insert INTO gcs_interco_elm_trx
2413 (hierarchy_id, cal_period_id, company_cost_center_org_id,
2414 src_entity_id, src_company_id, src_cost_center_id,
2415 intercompany_id, target_company_id,
2416 target_cost_center_id, target_entity_id,
2417 currency_code, line_item_id, financial_elem_id,
2418 product_id, natural_account_id, channel_id,
2419 project_id, customer_id, task_id,
2423 user_dim10_id,creation_date,
2420 user_dim1_id, user_dim2_id, user_dim3_id,
2421 user_dim4_id, user_dim5_id, user_dim6_id,
2422 user_dim7_id, user_dim8_id, user_dim9_id,
2424 created_by, last_update_date, last_updated_by,
2425 last_update_login, elim_lob_id)
2426 ';
2427
2428 --******--
2429 curr_pos := 1;
2430 body_len := LENGTH(body_block);
2431 WHILE curr_pos <= body_len LOOP
2432 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2433 line_num);
2434 curr_pos := curr_pos + g_line_size;
2435 line_num := line_num + 1;
2436 END LOOP;
2437
2438 body_block := '
2439 SELECT DISTINCT geh.hierarchy_id, geh.start_cal_period_id,
2440 gel.company_cost_center_org_id,
2441 geo1.entity_id, NULL, NULL, gel.intercompany_id,
2442 NULL,NULL, geo.entity_id, geh.currency_code,
2443 gel.line_item_id,
2444 ';
2445
2446 --******--
2447 curr_pos := 1;
2448 body_len := LENGTH(body_block);
2449 WHILE curr_pos <= body_len LOOP
2450 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2451 line_num);
2452 curr_pos := curr_pos + g_line_size;
2453 line_num := line_num + 1;
2454 END LOOP;
2455
2456
2457 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
2458 'gel.', GCS_UTILITY_PKG.g_nl, ' NULL, ', line_num);
2459
2460 body_block := '
2461 SYSDATE, g_fnd_user_id,
2462 SYSDATE, g_fnd_user_id,
2463 g_fnd_login_id,
2464 DECODE(fcoa2.dim_attribute_numeric_member,
2465 fcoa3.dim_attribute_numeric_member,
2466 fcoa2.dim_attribute_numeric_member,
2467 fcca.dim_attribute_numeric_member)
2468 FROM GCS_ENTRY_HEADERS geh,
2469 GCS_ENTRY_LINES gel,
2470 GCS_ENTITY_CCTR_ORGS geo,
2471 GCS_ENTITY_CCTR_ORGS geo1,
2472 GCS_CONS_RELATIONSHIPS gcr,
2473 GCS_CONS_RELATIONSHIPS gcr1,
2474 fem_cctr_orgs_attr fcoa2,
2475 fem_cctr_orgs_attr fcoa3,
2476 fem_user_dim1_attr fcca
2477 WHERE geh.entry_id IN (p_entry_id, p_stat_entry_id)
2478 AND geh.entry_id = gel.entry_id
2479 AND gel.intercompany_id <> gel.company_cost_center_org_id
2480 AND gel.intercompany_id =
2481 geo.company_cost_center_org_id
2482 AND gel.company_cost_center_org_id =
2483 geo1.company_cost_center_org_id
2484 AND geh.hierarchy_id = gcr.hierarchy_id
2485 AND (p_period_end_date
2486 BETWEEN NVL(gcr.start_date, p_period_end_date)
2487 AND NVL(gcr.end_date, p_period_end_date))
2488 AND gcr.child_entity_id = geo.entity_id
2489 AND gcr.actual_ownership_flag =''Y''
2490 AND gcr.dominant_parent_flag = ''Y''
2491 AND geh.hierarchy_id = gcr1.hierarchy_id
2492 AND (p_period_end_date
2493 BETWEEN NVL(gcr1.start_date, p_period_end_date)
2494 AND NVL(gcr1.end_date, p_period_end_date))
2495 AND gcr1.child_entity_id = geo1.entity_id
2496 AND gcr1.actual_ownership_flag =''Y''
2497 AND gcr1.dominant_parent_flag = ''Y''
2498 ';
2499
2500 --******--
2501 curr_pos := 1;
2502 body_len := LENGTH(body_block);
2503 WHILE curr_pos <= body_len LOOP
2504 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2505 line_num);
2506 curr_pos := curr_pos + g_line_size;
2507 line_num := line_num + 1;
2508 END LOOP;
2509
2510 body_block :='
2511 AND gel.company_cost_center_org_id =
2512 fcoa2.company_cost_center_org_id
2513 AND fcoa2.attribute_id =
2514 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COST_CENTER'').attribute_id
2515 AND fcoa2.version_id =
2516 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COST_CENTER'').version_id
2517 AND gel.intercompany_id = fcoa3.company_cost_center_org_id
2518 AND fcoa3.attribute_id =
2519 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COST_CENTER'').attribute_id
2520 AND fcoa3.version_id =
2521 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COST_CENTER'').version_id
2522 AND fcca.attribute_id = gcs_utility_pkg.g_dimension_attr_info(''USER_DIM1_ID-ELIMINATION_LOB'').attribute_id
2523 AND fcca.version_id = gcs_utility_pkg.g_dimension_attr_info(''USER_DIM1_ID-ELIMINATION_LOB'').version_id
2524 ';
2525 --******--
2526 curr_pos := 1;
2527 body_len := LENGTH(body_block);
2528 WHILE curr_pos <= body_len LOOP
2529 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2530 line_num);
2531 curr_pos := curr_pos + g_line_size;
2532 line_num := line_num + 1;
2533 END LOOP;
2534 body_block := '
2535 AND fcca.user_dim1_id = (
2536 SELECT fcch1.parent_id
2540 fcoa2.dim_attribute_numeric_member
2537 FROM fem_user_dim1_hier fcch1,
2538 fem_user_dim1_hier fcch2
2539 WHERE fcch1.child_id =
2541 AND fcch1.hierarchy_obj_def_id =
2542 l_valid_hierarchy_id
2543 AND fcch1.parent_id <> fcch1.child_id
2544 -- *** To eliminte self rows
2545 AND fcch2.child_id =
2546 fcoa3.dim_attribute_numeric_member
2547 AND fcch2.hierarchy_obj_def_id =
2548 l_valid_hierarchy_id
2549 AND fcch2.parent_id <> fcch2.child_id
2550 -- *** To eliminte self rows
2551 AND fcch1.parent_id = fcch2.parent_id
2552 AND fcch1.parent_depth_num =
2553 (SELECT MAX(fcch3.parent_depth_num)
2554 FROM fem_user_dim1_hier fcch3,
2555 fem_user_dim1_hier fcch4
2556 WHERE fcch3.child_id =
2557 fcoa2.dim_attribute_numeric_member
2558 AND fcch3.hierarchy_obj_def_id =
2559 l_valid_hierarchy_id
2560 AND fcch3.parent_id <> fcch3.child_id
2561 -- *** To eliminte self rows
2562 AND fcch4.child_id =
2563 fcoa3.dim_attribute_numeric_member
2564 AND fcch4.hierarchy_obj_def_id =
2565 l_valid_hierarchy_id
2566 AND fcch4.parent_id <> fcch4.child_id
2567 -- *** To eliminte self rows
2568 AND fcch3.parent_id = fcch4.parent_id
2569 ))
2570 ';
2571 --******--
2572 curr_pos := 1;
2573 body_len := LENGTH(body_block);
2574 WHILE curr_pos <= body_len LOOP
2575 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2576 line_num);
2577 curr_pos := curr_pos + g_line_size;
2578 line_num := line_num + 1;
2579 END LOOP;
2580 body_block := '
2581 AND NOT EXISTS ( SELECT 1 FROM GCS_INTERCO_ELM_TRX giet1
2582 WHERE giet1.hierarchy_id = geh.hierarchy_id
2583 AND giet1.cal_period_id = geh.start_cal_period_id
2584 AND giet1.company_cost_center_org_id =
2585 gel.company_cost_center_org_id
2586 AND giet1.src_entity_id = geo1.entity_id
2587 AND giet1.target_entity_id = geo.entity_id
2588 AND giet1.intercompany_id = gel.intercompany_id
2589 ';
2590
2591 --******--
2592 curr_pos := 1;
2593 body_len := LENGTH(body_block);
2594 WHILE curr_pos <= body_len LOOP
2595 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2596 line_num);
2597 curr_pos := curr_pos + g_line_size;
2598 line_num := line_num + 1;
2599 END LOOP;
2600
2601
2602 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Join_List(
2603 ' AND giet1.', ' = gel. ',
2604 GCS_UTILITY_PKG.g_nl, line_num);
2605
2606
2607
2608 body_block :=
2609 ' AND giet1.line_item_id = gel.line_item_id) ;
2610
2611 l_no_rows := NVL(SQL%ROWCOUNT,0);
2612
2613 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2614 FND_MESSAGE.Set_Name(''SQLGL'',''SHRD0117'');
2615 FND_MESSAGE.Set_Token(''NUM'',TO_CHAR(l_no_rows));
2616 FND_MESSAGE.Set_Token(''TABLE'',''GCS_INTERCO_ELM_TRX'');
2617
2618 FND_LOG.String (fnd_log.level_procedure,
2619 g_pkg_name || ''.'' || l_api_name,
2620 ''SHRD0117: ''||FND_MESSAGE.get);
2621 --FND_FILE.Put_Line(FND_FILE.Log,''SHRD0117: ''||FND_MESSAGE.get);
2622 END IF;
2623 END IF; -- end of x_intercompany_org_code if
2624 ';
2625
2626 --******--
2627 curr_pos := 1;
2628 body_len := LENGTH(body_block);
2629 WHILE curr_pos <= body_len LOOP
2630 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2631 line_num);
2632 curr_pos := curr_pos + g_line_size;
2633 line_num := line_num + 1;
2634 END LOOP;
2635
2636 body_block := '
2637 ELSE
2638 -- This is for LOB_REPORTING_ENABLED flag is N
2639 -- Regular matching by organization.
2640 IF (x_intercompany_org_code = ''SPECIFIC_VALUE'') THEN
2641 l_no_rows := 0;
2642
2643 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2644 fnd_log.STRING (fnd_log.level_procedure,
2645 g_pkg_name || ''.'' || l_api_name,
2646 ''Inserting intercompany transactions for matching by''
2650 ';
2647 || '' organization into GCS_INTERCO_ELM_TRX''
2648 || '' - LOB REPORTING Disabled'');
2649 END IF;
2651
2652 --******--
2653 curr_pos := 1;
2654 body_len := LENGTH(body_block);
2655 WHILE curr_pos <= body_len LOOP
2656 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2657 line_num);
2658 curr_pos := curr_pos + g_line_size;
2659 line_num := line_num + 1;
2660 END LOOP;
2661
2662 body_block := '
2663 Insert INTO gcs_interco_elm_trx
2664 (hierarchy_id, cal_period_id, company_cost_center_org_id,
2665 src_entity_id, src_company_id, src_cost_center_id,
2666 intercompany_id, target_company_id,
2667 target_cost_center_id, target_entity_id,
2668 currency_code, line_item_id, financial_elem_id,
2669 product_id, natural_account_id, channel_id,
2670 project_id, customer_id, task_id,
2671 user_dim1_id, user_dim2_id, user_dim3_id,
2672 user_dim4_id, user_dim5_id, user_dim6_id,
2673 user_dim7_id, user_dim8_id, user_dim9_id,
2674 user_dim10_id,creation_date,
2675 created_by, last_update_date, last_updated_by,
2676 last_update_login)
2677 ';
2678
2679 --******--
2680 curr_pos := 1;
2681 body_len := LENGTH(body_block);
2682 WHILE curr_pos <= body_len LOOP
2683 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2684 line_num);
2685 curr_pos := curr_pos + g_line_size;
2686 line_num := line_num + 1;
2687 END LOOP;
2688
2689 body_block := '
2690 SELECT DISTINCT geh.hierarchy_id, geh.start_cal_period_id,
2691 gel.company_cost_center_org_id,
2692 geo1.entity_id, NULL, NULL, gel.intercompany_id,
2693 NULL,NULL, geo.entity_id, geh.currency_code,
2694 gel.line_item_id,
2695 ';
2696
2697 --******--
2698 curr_pos := 1;
2699 body_len := LENGTH(body_block);
2700 WHILE curr_pos <= body_len LOOP
2701 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2702 line_num);
2703 curr_pos := curr_pos + g_line_size;
2704 line_num := line_num + 1;
2705 END LOOP;
2706
2707
2708 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
2709 ' ', GCS_UTILITY_PKG.g_nl, ' NULL, ', line_num);
2710
2711
2712 body_block := '
2713 SYSDATE, g_fnd_user_id,
2714 SYSDATE, g_fnd_user_id,
2715 g_fnd_login_id
2716 FROM GCS_ENTRY_HEADERS geh,
2717 GCS_ENTRY_LINES gel,
2718 GCS_ENTITY_CCTR_ORGS geo,
2719 GCS_ENTITY_CCTR_ORGS geo1,
2720 GCS_CONS_RELATIONSHIPS gcr,
2721 GCS_CONS_RELATIONSHIPS gcr1
2722 WHERE geh.entry_id IN (p_entry_id, p_stat_entry_id)
2723 AND geh.entry_id = gel.entry_id
2724 AND gel.intercompany_id <> x_specific_intercompany_id
2725 AND gel.intercompany_id =
2726 geo.company_cost_center_org_id
2727 AND gel.company_cost_center_org_id =
2728 geo1.company_cost_center_org_id
2729 AND geh.hierarchy_id = gcr.hierarchy_id
2730 AND (p_period_end_date
2731 BETWEEN NVL(gcr.start_date, p_period_end_date)
2732 AND NVL(gcr.end_date, p_period_end_date))
2733 AND gcr.child_entity_id = geo.entity_id
2734 AND gcr.actual_ownership_flag =''Y''
2735 AND gcr.dominant_parent_flag = ''Y''
2736 AND geh.hierarchy_id = gcr1.hierarchy_id
2737 AND (p_period_end_date
2738 BETWEEN NVL(gcr1.start_date, p_period_end_date)
2739 AND NVL(gcr.end_date, p_period_end_date))
2740 AND gcr1.child_entity_id = geo1.entity_id
2741 AND gcr1.actual_ownership_flag =''Y''
2742 AND gcr1.dominant_parent_flag = ''Y''
2743 ';
2744
2745 --******--
2746 curr_pos := 1;
2747 body_len := LENGTH(body_block);
2748 WHILE curr_pos <= body_len LOOP
2749 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2750 line_num);
2751 curr_pos := curr_pos + g_line_size;
2752 line_num := line_num + 1;
2753 END LOOP;
2754
2755
2756 body_block := '
2757 AND NOT EXISTS ( SELECT 1 FROM GCS_INTERCO_ELM_TRX giet1
2758 WHERE giet1.hierarchy_id = geh.hierarchy_id
2759 AND giet1.cal_period_id = geh.start_cal_period_id
2760 AND giet1.company_cost_center_org_id =
2761 gel.company_cost_center_org_id
2762 AND giet1.src_entity_id = geo1.entity_id
2763 AND giet1.target_entity_id = geo.entity_id
2764 AND giet1.intercompany_id = gel.intercompany_id
2765 ';
2766
2767 --******--
2768 curr_pos := 1;
2769 body_len := LENGTH(body_block);
2770 WHILE curr_pos <= body_len LOOP
2774 line_num := line_num + 1;
2771 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2772 line_num);
2773 curr_pos := curr_pos + g_line_size;
2775 END LOOP;
2776
2777
2778 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Join_List(
2779 ' AND giet1.', ' = gel. ', GCS_UTILITY_PKG.g_nl, line_num);
2780
2781 body_block := '
2782 AND giet1.line_item_id = gel.line_item_id);
2783
2784 l_no_rows := NVL(SQL%ROWCOUNT,0);
2785
2786 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2787 FND_MESSAGE.Set_Name(''SQLGL'',''SHRD0117'');
2788 FND_MESSAGE.Set_Token(''NUM'',TO_CHAR(l_no_rows));
2789 FND_MESSAGE.Set_Token(''TABLE'',''GCS_INTERCO_ELM_TRX'');
2790
2791 FND_LOG.String (fnd_log.level_procedure,
2792 g_pkg_name || ''.'' || l_api_name,
2793 ''SHRD0117: ''||FND_MESSAGE.get);
2794 --FND_FILE.Put_Line(FND_FILE.Log,''SHRD0117: ''||FND_MESSAGE.get);
2795 END IF;
2796
2797 ELSE
2798 l_no_rows := 0;
2799
2800 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2801 fnd_log.STRING (fnd_log.level_procedure,
2802 g_pkg_name || ''.'' || l_api_name,
2803 ''Inserting intercompany transactions for matching by''
2804 || '' organization into GCS_INTERCO_ELM_TRX''
2805 || '' - LOB REPORTING Disabled'');
2806 END IF;
2807 ';
2808
2809 --******--
2810 curr_pos := 1;
2811 body_len := LENGTH(body_block);
2812 WHILE curr_pos <= body_len LOOP
2813 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2814 line_num);
2815 curr_pos := curr_pos + g_line_size;
2816 line_num := line_num + 1;
2817 END LOOP;
2818
2819 body_block := '
2820 Insert INTO gcs_interco_elm_trx
2821 (hierarchy_id, cal_period_id, company_cost_center_org_id,
2822 src_entity_id, src_company_id, src_cost_center_id,
2823 intercompany_id, target_company_id,
2824 target_cost_center_id, target_entity_id,
2825 currency_code, line_item_id, financial_elem_id,
2826 product_id, natural_account_id, channel_id,
2827 project_id, customer_id, task_id,
2828 user_dim1_id, user_dim2_id, user_dim3_id,
2829 user_dim4_id, user_dim5_id, user_dim6_id,
2830 user_dim7_id, user_dim8_id, user_dim9_id,
2831 user_dim10_id,creation_date,
2832 created_by, last_update_date, last_updated_by,
2833 last_update_login)
2834 ';
2835
2836 --******--
2837 curr_pos := 1;
2838 body_len := LENGTH(body_block);
2839 WHILE curr_pos <= body_len LOOP
2840 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2841 line_num);
2842 curr_pos := curr_pos + g_line_size;
2843 line_num := line_num + 1;
2844 END LOOP;
2845
2846 body_block := '
2847 SELECT DISTINCT geh.hierarchy_id, geh.start_cal_period_id,
2848 gel.company_cost_center_org_id,
2849 geo1.entity_id, NULL, NULL, gel.intercompany_id,
2850 NULL,NULL, geo.entity_id, geh.currency_code,
2851 gel.line_item_id,
2852 ';
2853
2854 --******--
2855 curr_pos := 1;
2856 body_len := LENGTH(body_block);
2857 WHILE curr_pos <= body_len LOOP
2858 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2859 line_num);
2860 curr_pos := curr_pos + g_line_size;
2861 line_num := line_num + 1;
2862 END LOOP;
2863
2864
2865 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
2866 ' ', GCS_UTILITY_PKG.g_nl, ' NULL, ', line_num);
2867
2868
2869 body_block := '
2870 SYSDATE, g_fnd_user_id,
2871 SYSDATE, g_fnd_user_id,
2872 g_fnd_login_id
2873 FROM GCS_ENTRY_HEADERS geh,
2874 GCS_ENTRY_LINES gel,
2875 GCS_ENTITY_CCTR_ORGS geo,
2876 GCS_ENTITY_CCTR_ORGS geo1,
2877 GCS_CONS_RELATIONSHIPS gcr,
2878 GCS_CONS_RELATIONSHIPS gcr1
2879 WHERE geh.entry_id IN (p_entry_id, p_stat_entry_id)
2880 AND geh.entry_id = gel.entry_id
2881 AND gel.intercompany_id <> gel.company_cost_center_org_id
2882 AND gel.intercompany_id =
2883 geo.company_cost_center_org_id
2884 AND gel.company_cost_center_org_id =
2885 geo1.company_cost_center_org_id
2886 AND geh.hierarchy_id = gcr.hierarchy_id
2887 AND (p_period_end_date
2888 BETWEEN NVL(gcr.start_date, p_period_end_date)
2889 AND NVL(gcr.end_date, p_period_end_date))
2890 AND gcr.child_entity_id = geo.entity_id
2891 AND gcr.actual_ownership_flag =''Y''
2892 AND gcr.dominant_parent_flag = ''Y''
2893 AND geh.hierarchy_id = gcr1.hierarchy_id
2894 AND (p_period_end_date
2895 BETWEEN NVL(gcr1.start_date, p_period_end_date)
2896 AND NVL(gcr1.end_date, p_period_end_date))
2900 ';
2897 AND gcr1.child_entity_id = geo1.entity_id
2898 AND gcr1.actual_ownership_flag =''Y''
2899 AND gcr1.dominant_parent_flag = ''Y''
2901
2902 --******--
2903 curr_pos := 1;
2904 body_len := LENGTH(body_block);
2905 WHILE curr_pos <= body_len LOOP
2906 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2907 line_num);
2908 curr_pos := curr_pos + g_line_size;
2909 line_num := line_num + 1;
2910 END LOOP;
2911
2912
2913 body_block := '
2914 AND NOT EXISTS ( SELECT 1 FROM GCS_INTERCO_ELM_TRX giet1
2915 WHERE giet1.hierarchy_id = geh.hierarchy_id
2916 AND giet1.cal_period_id = geh.start_cal_period_id
2917 AND giet1.company_cost_center_org_id =
2918 gel.company_cost_center_org_id
2919 AND giet1.src_entity_id = geo1.entity_id
2920 AND giet1.target_entity_id = geo.entity_id
2921 AND giet1.intercompany_id = gel.intercompany_id
2922 ';
2923
2924 --******--
2925 curr_pos := 1;
2926 body_len := LENGTH(body_block);
2927 WHILE curr_pos <= body_len LOOP
2928 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2929 line_num);
2930 curr_pos := curr_pos + g_line_size;
2931 line_num := line_num + 1;
2932 END LOOP;
2933
2934
2935 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Join_List(
2936 ' AND giet1.', ' = gel. ', GCS_UTILITY_PKG.g_nl, line_num);
2937
2938 body_block := '
2939 AND giet1.line_item_id = gel.line_item_id);
2940
2941 l_no_rows := NVL(SQL%ROWCOUNT,0);
2942
2943 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2944 FND_MESSAGE.Set_Name(''SQLGL'',''SHRD0117'');
2945 FND_MESSAGE.Set_Token(''NUM'',TO_CHAR(l_no_rows));
2946 FND_MESSAGE.Set_Token(''TABLE'',''GCS_INTERCO_ELM_TRX'');
2947
2948 FND_LOG.String (fnd_log.level_procedure,
2949 g_pkg_name || ''.'' || l_api_name,
2950 ''SHRD0117: ''||FND_MESSAGE.get);
2951 --FND_FILE.Put_Line(FND_FILE.Log,''SHRD0117: ''||FND_MESSAGE.get);
2952 END IF;
2953 END IF; -- end of x_intercompany_org_code if
2954 END IF; -- End of LOB_REPORTING_ENABLED IF clause.
2955 ';
2956
2957 --******--
2958 curr_pos := 1;
2959 body_len := LENGTH(body_block);
2960 WHILE curr_pos <= body_len LOOP
2961 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2962 line_num);
2963 curr_pos := curr_pos + g_line_size;
2964 line_num := line_num + 1;
2965 END LOOP;
2966
2967
2968 body_block := '
2969 ELSIF (x_match_rule_code = ''COMPANY'') THEN
2970
2971 if (x_intercompany_org_code = ''SPECIFIC_VALUE'') THEN
2972
2973 l_no_rows := 0;
2974 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2975 FND_LOG.String (fnd_log.level_procedure,
2976 g_pkg_name || ''.'' || l_api_name,
2977 ''Specific_Value - ''
2978 ||'' Inserting intercompany transactions for matching by''
2979 ||'' company intercompany into GCS_INTERCO_ELM_TRX'');
2980 END IF;
2981 ';
2982
2983 --******--
2984 curr_pos := 1;
2985 body_len := LENGTH(body_block);
2986 WHILE curr_pos <= body_len LOOP
2987 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
2988 line_num);
2989 curr_pos := curr_pos + g_line_size;
2990 line_num := line_num + 1;
2991 END LOOP;
2992
2993
2994 body_block := '
2995 Insert /* PARALLEL ( GCS_INTERCO_ELM_TRX) */
2996 INTO gcs_interco_elm_trx
2997 (hierarchy_id, cal_period_id, company_cost_center_org_id,
2998 src_entity_id, src_company_id, src_cost_center_id,
2999 intercompany_id, target_company_id,
3000 target_cost_center_id, target_entity_id,
3001 currency_code, line_item_id, financial_elem_id,
3002 product_id, natural_account_id, channel_id,
3003 project_id, customer_id, task_id,
3004 user_dim1_id, user_dim2_id, user_dim3_id,
3005 user_dim4_id, user_dim5_id, user_dim6_id,
3006 user_dim7_id, user_dim8_id, user_dim9_id,
3007 user_dim10_id,creation_date,
3008 created_by, last_update_date, last_updated_by,
3009 last_update_login)
3010 ';
3011
3012 --******--
3013 curr_pos := 1;
3014 body_len := LENGTH(body_block);
3015 WHILE curr_pos <= body_len LOOP
3016 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3017 line_num);
3018 curr_pos := curr_pos + g_line_size;
3019 line_num := line_num + 1;
3020 END LOOP;
3021
3022 body_block := '
3023 SELECT DISTINCT geh.hierarchy_id, geh.start_cal_period_id,
3024 gel.company_cost_center_org_id,
3025 geo1.entity_id,fcoa2.dim_attribute_numeric_member, NULL,
3026 gel.intercompany_id,
3027 fcoa3.dim_attribute_numeric_member, NULL, geo.entity_id,
3028 geh.currency_code, gel.line_item_id,
3029
3030 ';
3031
3032 --******--
3033 curr_pos := 1;
3034 body_len := LENGTH(body_block);
3035 WHILE curr_pos <= body_len LOOP
3036 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3037 line_num);
3038 curr_pos := curr_pos + g_line_size;
3039 line_num := line_num + 1;
3040 END LOOP;
3041
3042
3043 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
3044 ' ', GCS_UTILITY_PKG.g_nl, ' NULL, ', line_num);
3045
3046 body_block := '
3047 SYSDATE, g_fnd_user_id,
3048 SYSDATE, g_fnd_user_id,
3049 g_fnd_login_id
3050 FROM GCS_ENTRY_HEADERS geh,
3051 GCS_ENTRY_LINES gel,
3052 GCS_ENTITY_CCTR_ORGS geo,
3053 GCS_ENTITY_CCTR_ORGS geo1,
3054 GCS_CONS_RELATIONSHIPS gcr,
3055 GCS_CONS_RELATIONSHIPS gcr1,
3056 fem_cctr_orgs_attr fcoa2,
3057 fem_cctr_orgs_attr fcoa3
3058 WHERE geh.entry_id IN (p_entry_id, p_stat_entry_id)
3059 AND geh.entry_id = gel.entry_id
3060 AND gel.intercompany_id <> x_specific_intercompany_id
3061 AND gel.intercompany_id =
3062 geo.company_cost_center_org_id
3063 AND gel.company_cost_center_org_id =
3064 geo1.company_cost_center_org_id
3065 AND geh.hierarchy_id = gcr.hierarchy_id
3066 AND (p_period_end_date
3067 BETWEEN NVL(gcr.start_date, p_period_end_date )
3068 AND NVL(gcr.end_date, p_period_end_date ))
3069 AND gcr.child_entity_id = geo.entity_id
3070 AND gcr.actual_ownership_flag =''Y''
3071 AND gcr.dominant_parent_flag = ''Y''
3072 AND gel.company_cost_center_org_id =
3073 fcoa2.company_cost_center_org_id
3074 AND geh.hierarchy_id = gcr1.hierarchy_id
3075 AND (p_period_end_date
3076 BETWEEN NVL(gcr1.start_date, p_period_end_date )
3077 AND NVL(gcr1.end_date, p_period_end_date ))
3078 AND gcr1.child_entity_id = geo1.entity_id
3079 AND gcr1.actual_ownership_flag =''Y''
3080 AND gcr1.dominant_parent_flag = ''Y''
3081 ';
3082
3083
3084 --******--
3085 curr_pos := 1;
3086 body_len := LENGTH(body_block);
3087 WHILE curr_pos <= body_len LOOP
3088 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3089 line_num);
3090 curr_pos := curr_pos + g_line_size;
3091 line_num := line_num + 1;
3092 END LOOP;
3093
3094
3095 body_block := '
3096 AND fcoa2.attribute_id =
3097 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COMPANY'').attribute_id
3098 AND fcoa2.version_id =
3099 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COMPANY'').version_id
3100 AND gel.intercompany_id = fcoa3.company_cost_center_org_id
3101 AND fcoa3.attribute_id =
3102 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COMPANY'').attribute_id
3103 AND fcoa3.version_id =
3104 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COMPANY'').version_id
3105
3106 ';
3107 --******--
3108 curr_pos := 1;
3109 body_len := LENGTH(body_block);
3110 WHILE curr_pos <= body_len LOOP
3111 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3112 line_num);
3113 curr_pos := curr_pos + g_line_size;
3114 line_num := line_num + 1;
3115 END LOOP;
3116
3117 body_block := '
3118 AND NOT EXISTS ( SELECT 1 FROM GCS_INTERCO_ELM_TRX giet1
3119 WHERE giet1.hierarchy_id = geh.hierarchy_id
3120 AND giet1.cal_period_id = geh.start_cal_period_id
3121 AND giet1.company_cost_center_org_id =
3122 gel.company_cost_center_org_id
3123 AND giet1.src_company_id =
3124 fcoa2.dim_attribute_numeric_member
3125 AND giet1.src_entity_id = geo1.entity_id
3126 AND giet1.target_entity_id = geo.entity_id
3127 AND giet1.target_company_id =
3128 fcoa3.dim_attribute_numeric_member
3129 AND giet1.intercompany_id = gel.intercompany_id
3130 ';
3131
3132 --******--
3133 curr_pos := 1;
3134 body_len := LENGTH(body_block);
3135 WHILE curr_pos <= body_len LOOP
3136 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3137 line_num);
3138 curr_pos := curr_pos + g_line_size;
3139 line_num := line_num + 1;
3140 END LOOP;
3141
3142
3143
3144 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Join_List(
3145 ' AND giet1.', ' = gel. ', GCS_UTILITY_PKG.g_nl, line_num);
3146
3147 body_block := '
3148 AND giet1.line_item_id = gel.line_item_id);
3149
3150 l_no_rows := NVL(SQL%ROWCOUNT,0);
3151
3152 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3153 FND_MESSAGE.Set_Name(''SQLGL'',''SHRD0117'');
3154 FND_MESSAGE.Set_Token(''NUM'',TO_CHAR(l_no_rows));
3155 FND_MESSAGE.Set_Token(''TABLE'',''GCS_INTERCO_ELM_TRX'');
3156
3157 FND_LOG.String (fnd_log.level_procedure,
3158 g_pkg_name || ''.'' || l_api_name,
3159 ''SHRD0117: ''||FND_MESSAGE.get);
3160 --FND_FILE.Put_Line(FND_FILE.Log,''SHRD0117: ''||FND_MESSAGE.get);
3161
3162 END IF;
3163
3164 ';
3165
3166 --******--
3167 curr_pos := 1;
3168 body_len := LENGTH(body_block);
3169 WHILE curr_pos <= body_len LOOP
3170 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3171 line_num);
3172 curr_pos := curr_pos + g_line_size;
3173 line_num := line_num + 1;
3174 END LOOP;
3175
3176 --******
3177
3178 body_block := '
3179
3180 ELSE
3181
3182 l_no_rows := 0;
3183 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3184 FND_LOG.String (fnd_log.level_procedure,
3185 g_pkg_name || ''.'' || l_api_name,
3189 END IF;
3186 '' Org and Interco are different - ''
3187 ||''Inserting intercompany transactions for matching by''
3188 ||'' company intercompany into GCS_INTERCO_ELM_TRX'');
3190 ';
3191
3192 --******--
3193 curr_pos := 1;
3194 body_len := LENGTH(body_block);
3195 WHILE curr_pos <= body_len LOOP
3196 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3197 line_num);
3198 curr_pos := curr_pos + g_line_size;
3199 line_num := line_num + 1;
3200 END LOOP;
3201
3202
3203 body_block := '
3204 Insert /* PARALLEL ( GCS_INTERCO_ELM_TRX) */
3205 INTO gcs_interco_elm_trx
3206 (hierarchy_id, cal_period_id, company_cost_center_org_id,
3207 src_entity_id, src_company_id, src_cost_center_id,
3208 intercompany_id, target_company_id,
3209 target_cost_center_id, target_entity_id,
3210 currency_code, line_item_id, financial_elem_id,
3211 product_id, natural_account_id, channel_id,
3212 project_id, customer_id, task_id,
3213 user_dim1_id, user_dim2_id, user_dim3_id,
3214 user_dim4_id, user_dim5_id, user_dim6_id,
3215 user_dim7_id, user_dim8_id, user_dim9_id,
3216 user_dim10_id,creation_date,
3217 created_by, last_update_date, last_updated_by,
3218 last_update_login)
3219 ';
3220
3221 --******--
3222 curr_pos := 1;
3223 body_len := LENGTH(body_block);
3224 WHILE curr_pos <= body_len LOOP
3225 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3226 line_num);
3227 curr_pos := curr_pos + g_line_size;
3228 line_num := line_num + 1;
3229 END LOOP;
3230
3231 body_block := '
3232 SELECT DISTINCT geh.hierarchy_id, geh.start_cal_period_id,
3233 gel.company_cost_center_org_id,
3234 geo1.entity_id,fcoa2.dim_attribute_numeric_member, NULL,
3235 gel.intercompany_id,
3236 fcoa3.dim_attribute_numeric_member, NULL, geo.entity_id,
3237 geh.currency_code, gel.line_item_id,
3238
3239 ';
3240
3241 --******--
3242 curr_pos := 1;
3243 body_len := LENGTH(body_block);
3244 WHILE curr_pos <= body_len LOOP
3245 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3246 line_num);
3247 curr_pos := curr_pos + g_line_size;
3248 line_num := line_num + 1;
3249 END LOOP;
3250
3251
3252 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
3253 ' ', GCS_UTILITY_PKG.g_nl, ' NULL, ', line_num);
3254
3255 body_block := '
3256 SYSDATE, g_fnd_user_id,
3257 SYSDATE, g_fnd_user_id,
3258 g_fnd_login_id
3259 FROM GCS_ENTRY_HEADERS geh,
3260 GCS_ENTRY_LINES gel,
3261 GCS_ENTITY_CCTR_ORGS geo,
3262 GCS_ENTITY_CCTR_ORGS geo1,
3263 GCS_CONS_RELATIONSHIPS gcr,
3264 GCS_CONS_RELATIONSHIPS gcr1,
3265 fem_cctr_orgs_attr fcoa2,
3266 fem_cctr_orgs_attr fcoa3
3267 WHERE geh.entry_id IN (p_entry_id, p_stat_entry_id)
3268 AND geh.entry_id = gel.entry_id
3269 AND gel.intercompany_id =
3270 geo.company_cost_center_org_id
3271 AND gel.company_cost_center_org_id =
3272 geo1.company_cost_center_org_id
3273 AND geh.hierarchy_id = gcr.hierarchy_id
3274 AND (p_period_end_date
3275 BETWEEN NVL(gcr.start_date, p_period_end_date )
3276 AND NVL(gcr.end_date, p_period_end_date ))
3277 AND gcr.child_entity_id = geo.entity_id
3278 AND gcr.actual_ownership_flag =''Y''
3279 AND gcr.dominant_parent_flag = ''Y''
3280 AND geh.hierarchy_id = gcr1.hierarchy_id
3281 AND (p_period_end_date
3282 BETWEEN NVL(gcr1.start_date, p_period_end_date )
3283 AND NVL(gcr1.end_date, p_period_end_date ))
3284 AND gcr1.child_entity_id = geo1.entity_id
3285 AND gcr1.actual_ownership_flag =''Y''
3286 AND gcr1.dominant_parent_flag = ''Y''
3287 AND gel.company_cost_center_org_id =
3288 fcoa2.company_cost_center_org_id
3289 ';
3290
3291
3292 --******--
3293 curr_pos := 1;
3294 body_len := LENGTH(body_block);
3295 WHILE curr_pos <= body_len LOOP
3296 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3297 line_num);
3298 curr_pos := curr_pos + g_line_size;
3299 line_num := line_num + 1;
3300 END LOOP;
3301
3302
3303 body_block := '
3304 AND fcoa2.attribute_id =
3305 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COMPANY'').attribute_id
3306 AND fcoa2.version_id =
3307 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COMPANY'').version_id
3308 AND gel.intercompany_id = fcoa3.company_cost_center_org_id
3309 AND fcoa3.attribute_id =
3310 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COMPANY'').attribute_id
3311 AND fcoa3.version_id =
3312 gcs_utility_pkg.g_dimension_attr_info(''COMPANY_COST_CENTER_ORG_ID-COMPANY'').version_id
3313 AND fcoa3.dim_attribute_numeric_member <>
3314 fcoa2.dim_attribute_numeric_member
3315
3316 ';
3317 --******--
3318 curr_pos := 1;
3319 body_len := LENGTH(body_block);
3323 curr_pos := curr_pos + g_line_size;
3320 WHILE curr_pos <= body_len LOOP
3321 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3322 line_num);
3324 line_num := line_num + 1;
3325 END LOOP;
3326
3327 body_block := '
3328 AND NOT EXISTS ( SELECT 1 FROM GCS_INTERCO_ELM_TRX giet1
3329 WHERE giet1.hierarchy_id = geh.hierarchy_id
3330 AND giet1.cal_period_id = geh.start_cal_period_id
3331 AND giet1.company_cost_center_org_id =
3332 gel.company_cost_center_org_id
3333 AND giet1.src_company_id =
3334 fcoa2.dim_attribute_numeric_member
3335 AND giet1.src_entity_id = geo1.entity_id
3336 AND giet1.target_entity_id = geo.entity_id
3337 AND giet1.target_company_id =
3338 fcoa3.dim_attribute_numeric_member
3339 AND giet1.intercompany_id = gel.intercompany_id
3340 ';
3341
3342 --******--
3343 curr_pos := 1;
3344 body_len := LENGTH(body_block);
3345 WHILE curr_pos <= body_len LOOP
3346 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3347 line_num);
3348 curr_pos := curr_pos + g_line_size;
3349 line_num := line_num + 1;
3350 END LOOP;
3351
3352
3353
3354 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Join_List(
3355 ' AND giet1.', ' = gel. ', GCS_UTILITY_PKG.g_nl, line_num);
3356
3357 body_block := '
3358 AND giet1.line_item_id = gel.line_item_id);
3359
3360 l_no_rows := NVL(SQL%ROWCOUNT,0);
3361
3362 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3363 FND_MESSAGE.Set_Name(''SQLGL'',''SHRD0117'');
3364 FND_MESSAGE.Set_Token(''NUM'',TO_CHAR(l_no_rows));
3365 FND_MESSAGE.Set_Token(''TABLE'',''GCS_INTERCO_ELM_TRX'');
3366
3367 FND_LOG.String (fnd_log.level_procedure,
3368 g_pkg_name || ''.'' || l_api_name,
3369 ''SHRD0117: ''||FND_MESSAGE.get);
3370 --FND_FILE.Put_Line(FND_FILE.Log,''SHRD0117: ''||FND_MESSAGE.get);
3371
3372 END IF;
3373 END IF; -- Ends Company if specific value..
3374 END IF; -- Ends If matching by
3375
3376 ';
3377
3378 --******--
3379 curr_pos := 1;
3380 body_len := LENGTH(body_block);
3381 WHILE curr_pos <= body_len LOOP
3382 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3383 line_num);
3384 curr_pos := curr_pos + g_line_size;
3385 line_num := line_num + 1;
3386 END LOOP;
3387
3388
3389 body_block :='
3390 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
3391 THEN
3392
3393 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3394 g_pkg_name || ''.'' || l_api_name,
3395 GCS_UTILITY_PKG.g_module_success || l_api_name ||
3396 to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
3397 END IF;
3398
3399 COMMIT;
3400
3401 EXCEPTION
3402
3403
3404 WHEN NO_MATCH_RULE_CODE THEN
3405
3406 x_errbuf := SQLERRM;
3407
3408 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3409 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3410 g_pkg_name || ''.'' || l_api_name
3411 ||'' NO_MATCH_RULE_CODE'',
3412 SUBSTR(SQLERRM, 1, 255));
3413
3414
3415 END IF;
3416
3417 x_retcode := 2;
3418 ';
3419
3420 --******--
3421 curr_pos := 1;
3422 body_len := LENGTH(body_block);
3423 WHILE curr_pos <= body_len LOOP
3424 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3425 line_num);
3426 curr_pos := curr_pos + g_line_size;
3427 line_num := line_num + 1;
3428 END LOOP;
3429
3430 body_block :='
3431
3432 WHEN Hierarchy_Check_Failed THEN
3433
3434 x_errbuf := SQLERRM;
3435
3436 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
3437 THEN
3438 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3439 g_pkg_name || ''.'' || l_api_name
3440 || '' Hierarchy_Check_Failed'',
3441 ''Either hierarchy does not exist or the ''
3442 ||'' hierarchy date affectivity has not been ''
3443 || '' passed '');
3444
3445
3446 END IF;
3447
3448 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3449 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3450 g_pkg_name || ''.'' || l_api_name,
3451 SUBSTR(SQLERRM, 1, 255));
3452
3453
3454 END IF;
3455
3456 x_retcode := 2;
3457 ';
3458
3459 --******--
3460 curr_pos := 1;
3461 body_len := LENGTH(body_block);
3462 WHILE curr_pos <= body_len LOOP
3463 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3464 line_num);
3465 curr_pos := curr_pos + g_line_size;
3466 line_num := line_num + 1;
3467 END LOOP;
3468
3469 body_block := '
3470
3471
3472
3473 WHEN OTHERS THEN
3474
3475 x_errbuf := SQLERRM;
3476
3477 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3478 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3479 g_pkg_name || ''.'' || l_api_name,
3480 SUBSTR(SQLERRM, 1, 255));
3481 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3482 g_pkg_name || ''.'' || l_api_name,
3483 GCS_UTILITY_PKG.g_module_failure || l_api_name ||
3484 to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
3485 END IF;
3486
3487 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure
3488 -- ||l_api_name || to_char(sysdate
3489 -- , '' DD-MON-YYYY HH:MI:SS''));
3490 x_retcode := 2;
3491
3492 RAISE;
3493 ';
3494
3495 --******--
3496 curr_pos := 1;
3497 body_len := LENGTH(body_block);
3498 WHILE curr_pos <= body_len LOOP
3499 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3500 line_num);
3501 curr_pos := curr_pos + g_line_size;
3502 line_num := line_num + 1;
3503 END LOOP;
3504
3505
3506 body_block := '
3507
3508 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3509
3510 fnd_log.STRING (fnd_log.level_procedure,
3511 g_pkg_name || ''.'' || l_api_name,
3512 gcs_utility_pkg.g_module_success
3513 || '' ''
3514 || l_api_name
3515 || ''() ''
3516 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
3517 );
3518 END IF;
3519
3520 END INSERT_INTERCO_TRX;
3521 ';
3522 --******--
3523 curr_pos := 1;
3524 body_len := LENGTH(body_block);
3525 WHILE curr_pos <= body_len LOOP
3526 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3527 line_num);
3528 curr_pos := curr_pos + g_line_size;
3529 line_num := line_num + 1;
3530 END LOOP;
3531
3532
3533 body_block := '
3534 END GCS_INTERCO_DYNAMIC_PKG;
3535 ';
3536
3537 --******--
3538 curr_pos := 1;
3539 body_len := LENGTH(body_block);
3540 WHILE curr_pos <= body_len LOOP
3541 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
3542 line_num);
3543 curr_pos := curr_pos + g_line_size;
3544 line_num := line_num + 1;
3545 END LOOP;
3546
3547 ad_ddl.create_plsql_object(GCS_DYNAMIC_UTIL_PKG.g_applsys_username,
3548 'GCS', 'GCS_INTERCO_DYNAMIC_PKG',
3549 1, line_num - 1, 'FALSE', comp_err);
3550
3551 EXCEPTION
3552
3553 WHEN OTHERS THEN
3554 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3555 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3556 g_api || '.' || l_api_name,
3557
3558 SUBSTR(SQLERRM, 1, 255));
3559 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3560 g_api || '.' || l_api_name,
3561 GCS_UTILITY_PKG.g_module_failure || l_api_name ||
3562 to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
3563 END IF;
3564 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure
3565 -- ||l_api_name || to_char(sysdate
3566 -- , ' DD-MON-YYYY HH:MI:SS'));
3567
3568
3569 x_errbuf := SQLERRM;
3570 x_retcode := 2;
3571 RAISE;
3572
3573 END Interco_Create_Package;
3574 END GCS_INTERCO_DYN_BUILD_PKG;