[Home] [Help]
PACKAGE BODY: APPS.GCS_INTERCO_PROCESSING_PKG
Source
1 PACKAGE BODY GCS_INTERCO_PROCESSING_PKG as
2 /* $Header: gcsicpeb.pls 120.6 2007/04/18 14:11:00 spala ship $ */
3
4 -- Definition of Global Data Types and Variables
5
6 g_period_start_date DATE;
7 g_period_end_date DATE;
8 g_match_rule_code VARCHAR2(30);
9 g_currency_code VARCHAR2(30);
10 g_dimension_attr_info gcs_utility_pkg.t_hash_dimension_attr_info
11 := gcs_utility_pkg.g_dimension_attr_info;
12 g_gcs_dimension_info gcs_utility_pkg.t_hash_gcs_dimension_info
13 := gcs_utility_pkg.g_gcs_dimension_info;
14 g_fnd_user_id NUMBER := fnd_global.user_id;
15 g_fnd_login_id NUMBER := fnd_global.login_id;
16 -- Action types for writing module information to the log file.
17
18 -- A newline character. Included for convenience when writing long
19 -- strings.
20 g_nl CONSTANT VARCHAR2 (1) := fnd_global.newline;
21 g_pkg_name VARCHAR2(80) := 'gcs.plsql.GCS_INTERCO_PROCESSING_PKG';
22 g_no_rows NUMBER :=0;
23 l_no_rows NUMBER :=0;
24 g_elim_entity_id NUMBER :=0;
25 g_consolidation_run_name VARCHAR2(80);
26 g_elim_code VARCHAR2(5);
27 g_elim_entity_name VARCHAR2(150);
28 g_cons_entity_id NUMBER; /* For intra company */
29 g_sus_exceed_no_rate BOOLEAN := FALSE;
30 -- Use the following global variable to stop the process
31 -- If there are no rows inserted into GCS_INTERCOHDR_GT table.
32 -- Set a proper value in the INSR_INTERCO_HDRS() routine.
33
34 g_stop_processing BOOLEAN := FALSE;
35
36 g_entity_id NUMBER;
37 g_xlation_required VARCHAR2(10);
38
39
40
41 --
42 -- Procedure
43 -- interco_process_main
44 -- Purpose
45 -- This is the main routine in the intercompany elimination entry
46 -- processing engine.
47 -- Important steps in this routine.
48 -- 1) Get the period information.
49 -- 2) Get the consolidation entity information like currency,
50 -- matching rule.
51 -- 3) Get all the subsidiaries for the given consolidation entity.
52 -- 4) Based on the elimination mode
53 -- populate GCS_INTERCO_HDR_GT
54 -- with corresponding information.
55 -- 5) Copy all the Intercompany transactions into the
56 -- GCS_ENTRY_LINES by calling Insr_Interco_Lines routine.
57 -- 6) After successful suspense plug-in insert the header
58 -- entries into the GCS_ENTRY_HEADERS table by calling
59 -- the Insert Elimination Header procedure.
60 -- 7) All the above processing has to be completed in one
61 -- commit cycle. So here we may COMMIT.
62
63 -- Arguments
64 -- Notes
65 -- p_hierarchy_id Hierarchy id
66 -- p_cal_period_id calendar period id
67 -- p_entity_id Consolidation entity id.
68 -- p_balance_type balance type like 'ACTUAL' or 'ADB'
69 -- p_elim_mode Elimination mode Valid values are 'IE' for Intercompany
70 -- or 'IA' for Intracompany
71 -- p_currency_code Currency code like 'USD', 'EUR', etc..,
72 -- P_run_name Consolidation run name.
73 -- x_errbuf Returns error message to concurrent manager if there is an error.
74 -- x_ret_code Returns error code to concurrent manager if there is an error.
75
76 -- Syntax for calling from an external package.
77
78 -- GCS_INTERCO_PROCESSING_PKG.Interco_process_Main
79 -- (10041,
80 -- 24534640000000000000031002200140,
81 -- 1030682,
82 -- 'ACTUAL',
83 -- 'IE',
84 -- 'EUR',
85 -- 'Srini Run');
86
87 PROCEDURE INTERCO_PROCESS_MAIN(p_hierarchy_id IN NUMBER,
88 p_cal_period_id IN NUMBER,
89 p_entity_id IN NUMBER,
90 p_balance_type VARCHAR2,
91 p_elim_mode IN VARCHAR2,
92 p_currency_code IN VARCHAR2,
93 p_run_name IN VARCHAR2,
94 p_translation_required IN VARCHAR2,
95 x_errbuf OUT NOCOPY VARCHAR2,
96 x_retcode OUT NOCOPY VARCHAR2) IS
97
98 l_period_start_date DATE;
99 l_period_end_date DATE;
100 l_api_name VARCHAR2(50) := 'INTERCO_PROCESS_MAIN';
101 l_success BOOLEAN := FALSE;
102
103 no_period_dates EXCEPTION;
104 no_currency_or_match_code EXCEPTION;
105 no_elim_entity EXCEPTION;
106 no_data_set_code EXCEPTION;
107 INTERCO_SUS_LINE_ERR EXCEPTION;
108 INTERCO_LINE_ERR EXCEPTION;
109 INTERCO_HDR_GT_ERR EXCEPTION;
110 INTERCO_ELIM_HDR_ERR EXCEPTION;
111 Hierarchy_date_Check_Failed EXCEPTION;
112
113 l_hierarchy_id NUMBER;
114 l_cal_period_id NUMBER;
115 l_entity_id NUMBER;
116 l_match_rule_code VARCHAR2(30);
117 l_lob_dim_col_name VARCHAR2(30);
118 l_lob_rpt_enabled_flag VARCHAR2(1);
119 l_lob_hier_obj_id NUMBER;
120 --Bugfix 5149868: Modified balance type to 30 characters
121 l_balance_type VARCHAR2(30);
122 l_elim_mode VARCHAR2(4);
123 l_Currency_code VARCHAR2(30);
124 l_fem_ledger_id NUMBER;
125
126 l_start_period_id NUMBER;
127 l_end_period_id NUMBER;
128 l_suspense_exceeded BOOLEAN := FALSE;
129
130 l_subs_gt NUMBER := 0;
131 l_hdr_gt NUMBER := 0;
132 l_data_set_code NUMBER :=0;
133 l_err_code VARCHAR2(200);
134 l_err_msg VARCHAR2(2000);
135 l_hierarchy_valid_id NUMBER; -- hierarchy object id after validation
136
137 l_sql_stmt VARCHAR2(8000);
138 l_sql_stmt1 VARCHAR2(4000);
139 l_text VARCHAR2(1000);
140 l_dims_list DBMS_SQL.varchar2_table;
141
142
143
144
145 BEGIN
146
147 --dbms_output.Put_line('Log Level: '||FND_LOG.LEVEL_PROCEDURE);
148 --dbms_output.Put_line('G Level: '||FND_LOG.G_CURRENT_RUNTIME_LEVEL);
149
150 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
151 fnd_log.STRING (fnd_log.level_procedure,
152 g_pkg_name || '.' || l_api_name,
153 gcs_utility_pkg.g_module_enter
154 || ' '
155 || l_api_name
156 || '() '
157 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
158 );
159 END IF;
160
161
162
163 -- Assign consolidation run name to a global varaible.
164
165 g_consolidation_run_name := p_run_name;
166 g_entity_id := p_entity_id;
167 g_xlation_required := p_translation_required;
168
169 -- Reassign 'FALSE' to this flag otherwise the cons engine is
170 -- caching this flag and generating weird results.
171
172 g_stop_processing := FALSE;
173
174
175 --Get the period start date and end date information
176 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
177 fnd_log.STRING (fnd_log.level_procedure,
178 g_pkg_name || '.' || l_api_name,
179 'Get the period start date and end date info..,'
180 );
181 END IF;
182
183 BEGIN
184
185
186
187 SELECT DATE_ASSIGN_VALUE
188 INTO g_period_start_date
189 FROM fem_cal_periods_attr fcpa
190 WHERE fcpa.cal_period_id = p_cal_period_id
191 AND fcpa.attribute_id =
192 g_dimension_attr_info ('CAL_PERIOD_ID-CAL_PERIOD_START_DATE').attribute_id
193 AND fcpa.version_id = g_dimension_attr_info ('CAL_PERIOD_ID-CAL_PERIOD_START_DATE').version_id;
194
195 SELECT DATE_ASSIGN_VALUE
196 INTO g_period_end_date
197 FROM fem_cal_periods_attr fcpa
198 WHERE fcpa.cal_period_id = p_cal_period_id
199 AND fcpa.attribute_id =
200 g_dimension_attr_info ('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').attribute_id
201 AND fcpa.version_id = g_dimension_attr_info ('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').version_id;
202
203 EXCEPTION
204 WHEN NO_DATA_FOUND THEN
205 --dbms_output.put_line (' first excpetion');
206 RAISE no_period_dates;
207
208 END;
209
210
211 -- Get the consolidation entity currency and matching rule
212 -- for matching intercompany eliminations such as by organization,
213 -- by company or by cost center.
214
215 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
216 fnd_log.STRING (fnd_log.level_procedure,
217 g_pkg_name || '.' || l_api_name,
218 'Get currency and matching rule information'
219 );
220 END IF;
221 BEGIN
222
223 SELECT ghb.ie_by_org_code,
224 ghb.lob_dim_column_name,
225 ghb.lob_reporting_enabled_flag,
226 ghb.lob_hierarchy_obj_id,
227 ghb.fem_ledger_id
228 INTO g_match_rule_code,
229 l_lob_dim_col_name,
230 l_lob_rpt_enabled_flag,
231 l_lob_hier_obj_id,
232 l_fem_ledger_id
233 FROM GCS_HIERARCHIES_B ghb
234 WHERE ghb.hierarchy_id = p_hierarchy_id;
235
236 SELECT gcea.currency_code
237 INTO g_currency_code
238 FROM GCS_ENTITY_CONS_ATTRS gcea
239 WHERE gcea.hierarchy_id = p_hierarchy_id
240 AND gcea.entity_id = p_entity_id;
241
242 EXCEPTION
243
244 WHEN NO_DATA_FOUND Then
245 Raise NO_CURRENCY_OR_MATCH_CODE;
246 END;
247
248 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
249 fnd_log.STRING (fnd_log.level_procedure,
250 g_pkg_name || '.' || l_api_name,
251 'Get the elimination entity id for the '
252 ||' given consolidation entity'
253 );
254 END IF;
255
256
257
258
259 IF (p_elim_mode = 'IE') THEN
260 BEGIN
261 SELECT DIM_ATTRIBUTE_NUMERIC_MEMBER
262 INTO g_elim_entity_id
263 FROM FEM_ENTITIES_ATTR
264 WHERE attribute_id =
265 g_dimension_attr_info ('ENTITY_ID-ELIMINATION_ENTITY').attribute_id
266 AND entity_id = p_entity_id
267 AND version_id = g_dimension_attr_info ('ENTITY_ID-ELIMINATION_ENTITY').version_id;
268
269 SELECT entity_name
270 INTO g_elim_entity_name
271 FROM FEM_ENTITIES_TL
272 WHERE LANGUAGE = userenv('LANG')
273 AND entity_id = g_elim_entity_id;
274
275
276 EXCEPTION
277 WHEN NO_DATA_FOUND THEN
278 Raise NO_ELIM_ENTITY;
279 END;
280
281 ELSIF (p_elim_mode = 'IA') THEN
282
283 g_elim_entity_id := p_entity_id;
284
285 SELECT parent_entity_id
286 INTO g_cons_entity_id
287 FROM GCS_CONS_RELATIONSHIPS
288 WHERE hierarchy_id = p_hierarchy_id
289 AND child_entity_id = p_entity_id
290 AND dominant_parent_flag = 'Y'
291 AND actual_ownership_flag ='Y'
292 AND (g_period_end_date
293 BETWEEN NVL(start_date,TO_DATE('01/01/1950', 'MM/DD/YYYY'))
294 AND NVL(END_DATE, TO_DATE('12/31/9999', 'MM/DD/YYYY')));
295
296
297 SELECT entity_name
298 INTO g_elim_entity_name
299 FROM FEM_ENTITIES_TL
300 WHERE LANGUAGE = userenv('LANG')
301 AND entity_id = g_elim_entity_id;
302 END IF;
303
304
305 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
306 fnd_log.STRING (fnd_log.level_procedure,
307 g_pkg_name || '.' || l_api_name,
308 'Insert parent child entity relationships into'
309 ||'GCS_INTERCO_SUBS_GT'
310 );
311 END IF;
312
313 BEGIN
314
315 SELECT dataset_code
316 INTO l_data_set_code
317 FROM GCS_DATASET_CODES
318 WHERE hierarchy_id = p_hierarchy_id
319 AND balance_type_code = p_balance_type;
320
321 EXCEPTION
322
323 WHEN OTHERS THEN
324 RAISE no_data_set_code;
325
326 END;
327
328 -- Assign passed arguments to the local varibles.
329
330 l_hierarchy_id := p_hierarchy_id;
331 l_cal_period_id := p_cal_period_id;
332 l_entity_id := p_entity_id;
333 l_match_rule_code := g_match_rule_code;
334 l_balance_type := p_balance_type;
335 l_elim_mode := p_elim_mode;
336 g_elim_code := p_elim_mode;
337 l_Currency_code := p_currency_code;
338
339 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
340
341 SELECT count(*) into g_no_rows
342 from GCS_INTERCO_HDR_GT;
343 FND_LOG.String (fnd_log.level_procedure,
344 g_pkg_name || '.' || l_api_name,
345 'Number of rows in GCS_INTERCO_HDR_GT: '||g_no_rows);
346
347 g_no_rows :=0;
348
349 SELECT count(*) into g_no_rows
350 from GCS_INTERCO_SUBS_GT;
351 FND_LOG.String (fnd_log.level_procedure,
352 g_pkg_name || '.' || l_api_name,
353 'Number of rows in GCS_INTERCO_SUBS_GT: '||g_no_rows);
354
355 g_no_rows :=0;
356
357 END IF;
358
359
360
361
362
363 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
364 fnd_log.STRING (fnd_log.level_procedure,
365 g_pkg_name || '.' || l_api_name,
366 ' Arguments l_hierarchy_id :'||p_hierarchy_id
367 ||' l_cal_period_id: '||p_cal_period_id
368 ||' l_entity_id: '||p_entity_id
369 ||' l_match_rule_code: '||g_match_rule_code
370 ||' l_balance_type: '||p_balance_type
371 ||' l_elim_mode: '||p_elim_mode
372 ||' l_currency_code: '||p_currency_code
373 ||' Period End Date: '||g_period_end_date
374 ||' Period Start Date: '||g_period_start_date
375 ||' Data set Code: '||l_data_set_code
376 ||' Translation Reuired: '||p_translation_required
377 ||' LOB Dim Column Name: '|| l_lob_dim_col_name
378 ||' LOB Reporting Enabled: '||l_lob_rpt_enabled_flag
379 ||' LOB Hierarchy Object Id: '||l_lob_hier_obj_id);
380
381 END IF;
382
383 g_no_rows := 0;
384
385
386
387
388 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
389 fnd_log.STRING (fnd_log.level_procedure,
390 g_pkg_name || '.' || l_api_name,
391 'Call insert_interco_hdrs() routine'
392 );
393 END IF;
394
395
396 IF ((INSR_INTERCO_HDRS ( p_hierarchy_id => l_hierarchy_id,
397 p_cal_period_id => l_cal_period_id,
398 p_entity_id => l_entity_id,
399 p_balance_type => l_balance_type,
400 p_elim_mode => l_elim_mode,
401 p_xlation_required => p_translation_required,
402 p_currency_code => l_currency_code)) = FALSE)
403 THEN
404
405
406 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
407
408 fnd_log.STRING (fnd_log.level_procedure,
409 g_pkg_name || '.' || l_api_name,
410 'Error in inserting rows into temporary table '
411 ||' SQL error message: '||SUBSTR(SQLERRM, 1, 255));
412 RAISE INTERCO_HDR_GT_ERR;
413
414 End If;
415
416 End If;
417
418
419 If (g_stop_processing) THEN
420
421 g_stop_processing := FALSE;
422 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
423 fnd_log.STRING (fnd_log.level_procedure,
424 g_pkg_name || '.' || l_api_name,
425 ' No rows to process');
426 fnd_log.STRING (fnd_log.level_procedure,
427 g_pkg_name || '.' || l_api_name,
428 gcs_utility_pkg.g_module_success
429 || ' '
430 || l_api_name
431 || '() '
432 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
433 );
434 END IF;
435 RETURN;
436 END IF;
437
438 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
439 fnd_log.STRING (fnd_log.level_procedure,
440 g_pkg_name || '.' || l_api_name,
441 ' Call GCS_INTERCO_DYNAMIC_PKG.INSR_INTERCO_LINES()'
442 || ' routine to insert intercompany eliminations'
443 );
444 END IF;
445
446 -- calling routine to insert intercompany elimination lines.
447 -- This routine inserts all eligible intercompany/intracompany
448 -- eliminations from GCS_INTERCO_ELM_TRX (dataprep temp table)
449 -- into GCS_ENTRY_LINES.
450
451 --dbms_output.put_line ('Just before calling insert lines');
452
453
454 IF ((GCS_INTERCO_DYNAMIC_PKG.INSR_INTERCO_LINES (
455 p_hierarchy_id => l_hierarchy_id,
456 p_cal_period_id => l_cal_period_id,
457 p_entity_id => l_entity_id,
458 p_match_rule_code => l_match_rule_code,
459 p_balance_type => l_balance_type,
460 p_elim_mode => l_elim_mode,
461 p_currency_code => l_Currency_code,
462 p_dataset_code => l_data_set_code,
463 p_lob_dim_col_name => l_lob_dim_col_name,
464 p_cons_run_name => g_consolidation_run_name,
465 p_period_end_date => g_period_end_date,
466 p_fem_ledger_id => l_fem_ledger_id))
467 = FALSE) THEN
468
469 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
470
471 fnd_log.STRING (fnd_log.level_procedure,
472 g_pkg_name || '.' || l_api_name,
473 'Error in inserting intercompany lines into '
474 ||' GCS_ENTRY_LINES '
475 ||' SQL error message: '||SUBSTR(SQLERRM, 1, 255));
476
477 RAISE INTERCO_LINE_ERR;
478 END IF;
479
480
481 End If;
482
483 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
484 fnd_log.STRING (fnd_log.level_procedure,
485 g_pkg_name || '.' || l_api_name,
486 ' Call GCS_INTERCO_DYNAMIC_PKG.INSR_INTERCO_LINES()'
487 || ' routine to insert suspense elimination'
488 || ' lines.');
489 END IF;
490
491
492 -- This routine inserts suspense lines for unbalanced matched rows and
493 -- unmatched elimination entries to balance.
494 -- EXAMPLE
495 -- ORG_ID Line Interco_id Cr Dr
496 -------------------------------------------------------------
497 -- 01.1001 2020 02.2004 100.00
498 -- 02.2004 2020 01.1001 80.00
499
500 -- In the above transactions there are matched transactions
501 -- but the balances are off by 20. So a suspense line
502 -- will be generated with balance 20.
503
504 -- The second SQL statement generates suspense line for the unmatched
505 -- intercompany transactions.
506
507 -- EXAMPLE
508 -- ORG_ID Line Interco_id Cr Dr
509 -------------------------------------------------------------
510 -- 01.6677 3434 02.9978 100.00
511
512 -- If you look at the above transaction there is no matching
513 -- intercompany transaction, so a suspense line has to be created
514 -- to balance the above transaction.
515
516 IF ((GCS_INTERCO_DYNAMIC_PKG.INSR_SUSPENSE_LINES (
517 p_hierarchy_id => l_hierarchy_id,
518 p_cal_period_id => l_cal_period_id,
519 p_entity_id => l_entity_id,
520 p_match_rule_code => l_match_rule_code,
521 p_balance_type => l_balance_type,
522 p_elim_mode => l_elim_mode,
523 p_currency_code => l_Currency_code,
524 p_data_set_code => l_data_set_code,
525 p_err_code => l_err_code,
526 p_err_msg => l_err_msg)) = FALSE)
527 THEN
528
529
530 RAISE INTERCO_SUS_LINE_ERR;
531
532
533 End If;
534
535
536 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
537 fnd_log.STRING (fnd_log.level_procedure,
538 g_pkg_name || '.' || l_api_name,
539 ' Call GCS_INTERCO_DYNAMIC_PKG.INSR_INTERCO_LINES()'
540 || ' routine to insert suspense elimination'
541 || ' lines.');
542 END IF;
543
544 -- Insert elimination headers into GCS_ENTRY_HEADERS.
545
546 IF( (INSR_ELIMINATION_HDRS(p_hierarchy_id => l_hierarchy_id,
547 p_cal_period_id => l_cal_period_id,
548 p_entity_id => l_entity_id,
549 p_balance_type => l_balance_type,
550 p_currency_code => l_Currency_code)) = FALSE)
551 THEN
552 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
553
554 fnd_log.STRING (fnd_log.level_procedure,
555 g_pkg_name || '.' || l_api_name,
556 'Error in inserting elimination headers '
557 ||' GCS_ENTRY_LINES '
558 ||' SQL error message: '||SUBSTR(SQLERRM, 1, 255));
559 RAISE INTERCO_ELIM_HDR_ERR;
560 End If;
561
562 End If;
563
564
565 -- This part of the code added as part of the JPMC LE/LOB support
566 -- enhancement.
567 -- IF Lob_Reporting is enabled then the lob_dim_col has to be
568 -- populated with common elimination line of business (cost center value)
569 -- for the suspense lines created by the intercompany processing.
570
571 If ((l_lob_rpt_enabled_flag = 'Y') AND
572 (l_lob_dim_col_name IS NOT NULL)) THEN
573
574
575 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
576 fnd_log.STRING (fnd_log.level_procedure,
577 g_pkg_name || '.' || l_api_name,
578 ' Entered into LE_LOB support block');
579
580 END IF;
581
582 BEGIN
583
584 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
585 fnd_log.STRING (fnd_log.level_procedure,
586 g_pkg_name || '.' || l_api_name,
587 ' Checking Hierarchy Date effectivity');
588
589 END IF;
590
591 SELECT object_definition_id INTO l_hierarchy_valid_id
592 FROM FEM_OBJECT_DEFINITION_B fod
593 WHERE fod.object_id = l_lob_hier_obj_id
594 AND (g_period_end_date
595 BETWEEN NVL(fod.effective_start_date,
596 TO_DATE('01/01/1950','MM/DD/YYYY'))
597 AND NVL(fod.effective_end_date,
598 TO_DATE('12/31/9999','MM/DD/YYYY')));
599
600
601 EXCEPTION
602 WHEN NO_DATA_FOUND THEN
603 l_hierarchy_valid_id :=0;
604 null;
605 --Raise Hierarchy_date_Check_Failed;
606
607 WHEN OTHERS THEN
608 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
609 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
610 g_pkg_name || '.' || l_api_name
611 ||' Hierarchy_Check ',
612 SUBSTR(SQLERRM, 1, 255));
613
614 END IF;
615
616 END;
617
618 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
619 fnd_log.STRING (fnd_log.level_procedure,
620 g_pkg_name || '.' || l_api_name,
621 ' Finding required dimensions');
622
623 END IF;
624
625 SELECT member_col
626 BULK COLLECT INTO l_dims_list
627 FROM fem_xdim_dimensions
628 WHERE gcs_utility_pkg.Get_Dimension_Required(member_col) = 'Y'
629 AND member_col <> 'ENTITY_ID';
630
631 FOR i in l_dims_list.first .. l_dims_list.last loop
632
633 l_text := l_text||g_nl|| ' AND gel.'||l_dims_list(i)
634 ||' = gel1.'
635 ||l_dims_list(i);
636 END LOOP;
637
638 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
639 fnd_log.STRING (fnd_log.level_procedure,
640 g_pkg_name || '.' || l_api_name,
641 'Required dimensions statement is : '
642 ||l_text
643 ||' Lob_Dim_Col_Name is: '
644 || l_lob_dim_col_name);
645
646 END IF;
647
648 l_sql_stmt := 'UPDATE GCS_ENTRY_LINES gel1
649 SET '||l_lob_dim_col_name||' = (SELECT
650 DECODE(
651 fcoa2.dim_attribute_numeric_member,
652 fcoa3.dim_attribute_numeric_member,
653 fcoa2.dim_attribute_numeric_member,
654 NVL(fcca.dim_attribute_numeric_member,
655 gel1.'||l_lob_dim_col_name||'))
656 FROM GCS_ENTRY_LINES gel,
657 fem_cctr_orgs_attr fcoa2,
658 fem_cctr_orgs_attr fcoa3,
659 fem_user_dim1_attr fcca
660 WHERE gel.entry_id = gel1.entry_id
661 AND gel.company_cost_center_org_id =
662 gel1.company_cost_center_org_id
663 AND gel.intercompany_id =
664 gel1.intercompany_id
665 AND gel.line_item_id = gel1.line_item_id
666 AND gel.company_cost_center_org_id =
667 fcoa2.company_cost_center_org_id
668 '
669 ||l_text||
670 '
671 AND fcoa2.attribute_id = :attribute_id
672 AND fcoa2.version_id = :version_id
673 AND gel.intercompany_id = fcoa3.company_cost_center_org_id
674 AND fcoa3.attribute_id = :attribute_id
675 AND fcoa3.version_id = :version_id
676 AND fcca.user_dim1_id = ';
677 l_sql_stmt1 := '(
678 SELECT fcch1.parent_id
679 FROM fem_user_dim1_hier fcch1,
680 fem_user_dim1_hier fcch2
681 WHERE fcch1.child_id =
682 fcoa2.dim_attribute_numeric_member
683 AND fcch1.hierarchy_obj_def_id =
684 :hierarchy_id
685 AND fcch1.parent_id <> fcch1.child_id
686 AND fcch2.child_id =
687 fcoa3.dim_attribute_numeric_member
688 AND fcch2.hierarchy_obj_def_id =
689 :hierarchy_id
690 AND fcch2.parent_id <> fcch2.child_id
691 AND fcch1.parent_id = fcch2.parent_id
692 AND fcch1.parent_depth_num =
693 (SELECT MAX(fcch3.parent_depth_num)
694 FROM fem_user_Dim1_hier fcch3,
695 fem_user_dim1_hier fcch4
696 WHERE fcch3.child_id =
697 fcoa2.dim_attribute_numeric_member
698 AND fcch3.hierarchy_obj_def_id =
699 :hierarchy_id
700 AND fcch3.parent_id <> fcch3.child_id
701 AND fcch4.child_id =
702 fcoa3.dim_attribute_numeric_member
703 AND fcch4.hierarchy_obj_def_id =
704 :hierarchy_id
705 AND fcch4.parent_id <> fcch4.child_id
706 AND fcch3.parent_id = fcch4.parent_id))
707 AND fcca.attribute_id = :attribute_id
708 AND fcca.version_id = :version_id)
709 WHERE ENTRY_ID IN ( SELECT ENTRY_ID FROM GCS_INTERCO_HDR_GT)
710 AND description = ''SUSPENSE_LINE''';
711
712 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
713
714 fnd_log.STRING (fnd_log.level_procedure,
715 g_pkg_name || '.' || l_api_name,
716 'Updating '|| l_lob_dim_col_name
717 ||' in GCS_ENTRY_LINES with elimination LOB'
718 );
719
720 END IF;
721
722 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
723
724 fnd_log.STRING (fnd_log.level_procedure,
725 g_pkg_name || '.' || l_api_name,
726 l_sql_stmt
727 );
728 fnd_log.STRING (fnd_log.level_procedure,
729 g_pkg_name || '.' || l_api_name,
730 l_sql_stmt1
731 );
732 END IF;
733
734 l_sql_stmt := l_sql_stmt||l_sql_stmt1;
735
736 EXECUTE IMMEDIATE l_sql_stmt
737 USING
738 g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COST_CENTER').attribute_id,
739 g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COST_CENTER').version_id,
740 g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COST_CENTER').attribute_id,
741 g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COST_CENTER').version_id,
742 l_hierarchy_valid_id, l_hierarchy_valid_id,
743 l_hierarchy_valid_id, l_hierarchy_valid_id,
744 g_dimension_attr_info('USER_DIM1_ID-ELIMINATION_LOB').attribute_id,
745 g_dimension_attr_info('USER_DIM1_ID-ELIMINATION_LOB').version_id;
746
747
748 g_no_rows := NVL(SQL%ROWCOUNT,0);
749
750 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
751 FND_MESSAGE.Set_Name('SQLGL','SHRD0118');
752 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
753 FND_MESSAGE.Set_Token('TABLE','GCS_ENTRY_LINES');
754
755
756 FND_LOG.String (fnd_log.level_procedure,
757 g_pkg_name || '.' || l_api_name,
758 'SHRD0117: '||FND_MESSAGE.get);
759 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0118: '||FND_MESSAGE.get);
760 END IF;
761
762
763
764
765 END IF;
766
767
768 -- Consolidation engine does not want to be commited these changes.
769 -- Consolidation engine has its own logic to commit all these changes.
770
771 --COMMIT;
772
773 IF (g_sus_exceed_no_rate) THEN
774 x_errbuf := 'WARNING';
775 END IF;
776
777
778 --Success:
779 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
780
781 fnd_log.STRING (fnd_log.level_procedure,
782 g_pkg_name || '.' || l_api_name,
783 gcs_utility_pkg.g_module_success
784 || ' '
785 || l_api_name
786 || '() '
787 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
788 );
789 END IF;
790
791 EXCEPTION
792
793 WHEN no_period_dates THEN
794 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
795
796 fnd_log.STRING (fnd_log.level_unexpected,
797 g_pkg_name || '.' || l_api_name,
798 ' No valid period information available '
799 );
800
801 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
802 g_pkg_name || '.' || l_api_name,
803 SUBSTR(SQLERRM, 1, 255));
804
805 fnd_log.STRING (fnd_log.level_unexpected,
806 g_pkg_name || '.' || l_api_name,
807 gcs_utility_pkg.g_module_failure
808 || ' '
809 || l_api_name
810 || '() '
811 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
812 );
813 END IF;
814 x_errbuf := 'Error In the IntercoProcess_Routine';
815 x_retcode:='2';
816
817 RAISE;
818
819
820
821 WHEN no_currency_or_match_code THEN
822 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
823
824 fnd_log.STRING (fnd_log.level_unexpected,
825 g_pkg_name || '.' || l_api_name,
826 ' No currency or matching rule'
827 );
828
829 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
830 g_pkg_name || '.' || l_api_name,
831 SUBSTR(SQLERRM, 1, 255));
832
833 fnd_log.STRING (fnd_log.level_unexpected,
834 g_pkg_name || '.' || l_api_name,
835 gcs_utility_pkg.g_module_failure
836 || ' '
837 || l_api_name
838 || '() '
839 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
840 );
841 END IF;
842 RAISE;
843
844 WHEN no_elim_entity THEN
845 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
846
847 fnd_log.STRING (fnd_log.level_unexpected,
848 g_pkg_name || '.' || l_api_name,
849 ' No elimination entity associated '
850 );
851
852 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
853 g_pkg_name || '.' || l_api_name,
854 SUBSTR(SQLERRM, 1, 255));
855
856 fnd_log.STRING (fnd_log.level_unexpected,
857 g_pkg_name || '.' || l_api_name,
858 gcs_utility_pkg.g_module_failure
859 || ' '
860 || l_api_name
861 || '() '
862 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
863 );
864 END IF;
865 RAISE;
866
867 WHEN no_data_set_code THEN
868 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
869
870 fnd_log.STRING (fnd_log.level_unexpected,
871 g_pkg_name || '.' || l_api_name,
872 ' No valid dataset code available '
873 );
874
875 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
876 g_pkg_name || '.' || l_api_name,
877 SUBSTR(SQLERRM, 1, 255));
878
879 fnd_log.STRING (fnd_log.level_unexpected,
880 g_pkg_name || '.' || l_api_name,
881 gcs_utility_pkg.g_module_failure
882 || ' '
883 || l_api_name
884 || '() '
885 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
886 );
887 END IF;
888 x_errbuf := 'Error In the IntercoProcess_Routine';
889 x_retcode:='2';
890
891 RAISE;
892
893 WHEN INTERCO_SUS_LINE_ERR THEN
894 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
895
896 fnd_log.STRING (fnd_log.level_unexpected,
897 g_pkg_name || '.' || l_api_name,
898 ' Error In Insert_Suspense_lines() '
899 || SUBSTR(l_err_msg, 1, 255));
900
901 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
902 g_pkg_name || '.' || l_api_name,
903 SUBSTR(l_err_msg, 1, 255));
904
905 fnd_log.STRING (fnd_log.level_unexpected,
906 g_pkg_name || '.' || l_api_name,
907 gcs_utility_pkg.g_module_failure
908 || ' '
909 || l_api_name
910 || '() '
911 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
912 );
913 END IF;
914
915 ROLLBACK TO gcs_cons_eng_insr_warning;
916
917 x_errbuf := 'Error in Insert_Suspense_lines()';
918 x_retcode:='2';
919
920 RAISE;
921
922
923 WHEN INTERCO_LINE_ERR THEN
924
925 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
926
927 fnd_log.STRING (fnd_log.level_unexpected,
928 g_pkg_name || '.' || l_api_name,
929 ' Error In Insert_Interco_lines() '
930 || SUBSTR(SQLERRM, 1, 255));
931
932 fnd_log.STRING (fnd_log.level_unexpected,
933 g_pkg_name || '.' || l_api_name,
934 gcs_utility_pkg.g_module_failure
935 || ' '
936 || l_api_name
937 || '() '
938 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
939 );
940 END IF;
941
942 ROLLBACK TO gcs_cons_eng_insr_warning;
943
944 x_errbuf := 'Error in Insert_Interco_lines()';
945 x_retcode:='2';
946
947 RAISE;
948
949
950 WHEN INTERCO_HDR_GT_ERR THEN
951
952 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
953
954 fnd_log.STRING (fnd_log.level_unexpected,
955 g_pkg_name || '.' || l_api_name,
956 ' Error In Insert_Interco_Hdrs() '
957 || SUBSTR(SQLERRM, 1, 255));
958
959 fnd_log.STRING (fnd_log.level_unexpected,
960 g_pkg_name || '.' || l_api_name,
961 gcs_utility_pkg.g_module_failure
962 || ' '
963 || l_api_name
964 || '() '
965 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
966 );
967 END IF;
968
969 ROLLBACK TO gcs_cons_eng_insr_Hdr_warning;
970
971 x_errbuf := 'Error in Insert_Interco_Hdrs()';
972 x_retcode:='2';
973
974 RAISE;
975
976
977 WHEN INTERCO_ELIM_HDR_ERR THEN
978
979 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
980
981 fnd_log.STRING (fnd_log.level_unexpected,
982 g_pkg_name || '.' || l_api_name,
983 ' Error In Insert_Elimination_Hdrs() '
984 || SUBSTR(SQLERRM, 1, 255));
985
986 fnd_log.STRING (fnd_log.level_unexpected,
987 g_pkg_name || '.' || l_api_name,
988 gcs_utility_pkg.g_module_failure
989 || ' '
990 || l_api_name
991 || '() '
992 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
993 );
994 END IF;
995
996 ROLLBACK TO gcs_cons_eng_insr_warning;
997
998 x_errbuf := 'Error in Insert_Elimination_Hdrs()';
999 x_retcode:='2';
1000
1001 RAISE;
1002
1003 WHEN Hierarchy_Date_Check_Failed THEN
1004
1005 x_errbuf := SQLERRM;
1006
1007 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED)
1008 THEN
1009 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1010 g_pkg_name || '.' || l_api_name
1011 || ' Hierarchy_Check_Failed',
1012 'Either hierarchy does not exist or the '
1013 ||' hierarchy date affectivity has not been '
1014 || ' passed ');
1015
1016
1017 END IF;
1018
1019 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1020 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1021 g_pkg_name || '.' || l_api_name,
1022 SUBSTR(SQLERRM, 1, 255));
1023
1024
1025 END IF;
1026
1027
1028 WHEN NO_DATA_FOUND THEN
1029
1030 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1031
1032 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1033 g_pkg_name || '.' || l_api_name,
1034 SUBSTR(SQLERRM, 1, 255));
1035
1036 fnd_log.STRING (fnd_log.level_unexpected,
1037 g_pkg_name || '.' || l_api_name,
1038 gcs_utility_pkg.g_module_failure
1039 || ' '
1040 || l_api_name
1041 || '() '
1042 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1043 );
1044 END IF;
1045
1046 RAISE;
1047
1048 WHEN OTHERS THEN
1049
1050
1051 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1052
1053
1054 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1055 g_pkg_name || '.' || l_api_name,
1056 SUBSTR(SQLERRM, 1, 255));
1057
1058 fnd_log.STRING (fnd_log.level_unexpected,
1059 g_pkg_name || '.' || l_api_name,
1060 gcs_utility_pkg.g_module_failure
1061 || ' '
1062 || l_api_name
1063 || '() '
1064 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1065 );
1066 END IF;
1067
1068 RAISE;
1069
1070 END interco_process_main;
1071
1072
1073
1074
1075 --
1076 -- Function
1077 -- insr_interco_hdrs
1078 -- Purpose
1079 -- This routine is responsible for inserting distinct pairs of entities
1080 -- for each intercompany rule into the global temporary table
1081 -- GCS_INTERCO_HDR_GT.
1082
1083
1084 FUNCTION INSR_INTERCO_HDRS (p_hierarchy_id IN NUMBER,
1085 p_cal_period_id IN NUMBER,
1086 p_entity_id IN NUMBER,
1087 p_balance_type VARCHAR2,
1088 p_elim_mode IN VARCHAR2,
1089 p_xlation_required IN VARCHAR2,
1090 p_currency_code IN VARCHAR2) RETURN BOOLEAN IS
1091
1092 -- l_dummy NUMBER; /* Not in use */
1093 l_api_name VARCHAR2(30) := 'INSR_INTERCO_HDRS';
1094
1095 BEGIN
1096
1097 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1098 fnd_log.STRING (fnd_log.level_procedure,
1099 g_pkg_name || '.' || l_api_name,
1100 gcs_utility_pkg.g_module_enter
1101 || ' '
1102 || l_api_name
1103 || '() '
1104 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1105 );
1106 END IF;
1107
1108 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1109 fnd_log.STRING (fnd_log.level_procedure,
1110 g_pkg_name || '.' || l_api_name,
1111 ' Arguments passed to Insr_Interco_Hdr() '
1112 ||' Hierarchy_Id :'||p_hierarchy_id
1113 ||' Cal_Period_Id: '||p_cal_period_id
1114 ||' Entity_Id: '||p_entity_id
1115 ||' Balance_Type: '||p_balance_type
1116 ||' Elim_Mode: '||p_elim_mode
1117 ||' Currency_Code: '||p_currency_code);
1118
1119 END IF;
1120
1121
1122 IF (p_elim_mode = 'IE') THEN
1123 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1124 fnd_log.STRING (fnd_log.level_procedure,
1125 g_pkg_name || '.' || l_api_name,
1126 'Insert distinct pairs of entities for each rule '
1127 || 'into GCS_INTERCO_HDR_GT in full '
1128 || 'consolidation run mode and elim-mode=''IE'''
1129 || '- Intercompany rule on Receivables side'
1130 );
1131 END IF;
1132
1133
1134 -- Fixed bug# 4217286.
1135 -- Added DECODE(fc.currency_code,'STAT',10000,
1136 -- gib.sus_financial_elem_id) "SUS_FINANCIAL_ELEM_ID"
1137 -- to the following statement.
1138
1139 INSERT INTO GCS_INTERCO_HDR_GT
1140 (entry_id, source_entity_id, target_entity_id, rule_id,
1141 threshold_currency,threshold_amount,sus_financial_elem_id,
1142 sus_product_id,sus_natural_account_id,
1143 sus_channel_id,sus_line_item_id,sus_project_id,sus_customer_id,
1144 sus_task_id, sus_user_dim1_id, sus_user_dim2_id,sus_user_dim3_id,
1145 sus_user_dim4_id, sus_user_dim5_id, sus_user_dim6_id,
1146 sus_user_dim7_id, sus_user_dim8_id, sus_user_dim9_id,
1147 sus_user_dim10_id, creation_date, created_by,
1148 last_update_date, last_updated_by, last_update_login,
1149 currency_code)
1150 SELECT GCS_ENTRY_HEADERS_S.NEXTVAL, git.src_id,
1151 git.tar_id, git.rule_id,
1152 git.threshold_currency,
1153 git.threshold_amount, git.sus_financial_elem_id,
1154 git.sus_product_id,git.sus_natural_account_id,
1155 git.sus_channel_id,git.sus_line_item_id,
1156 git.sus_project_id,git.sus_customer_id,
1157 git.sus_task_id,git.sus_user_dim1_id,
1158 git.sus_user_dim2_id,git.sus_user_dim3_id,
1159 git.sus_user_dim4_id, git.sus_user_dim5_id,
1160 git.sus_user_dim6_id, git.sus_user_dim7_id,
1161 git.sus_user_dim8_id, git.sus_user_dim9_id,
1162 git.sus_user_dim10_id,SYSDATE,g_fnd_user_id,sysdate,
1163 g_fnd_user_id,g_fnd_login_id, git.currency_code
1164 FROM (SELECT giet.src_entity_id
1165 src_id,
1166 giet.target_entity_id
1167 tar_id,
1168 gib.rule_id, gib.threshold_currency,
1169 gib.threshold_amount,
1170 DECODE(fc.currency_code,'STAT',10000,
1171 gib.sus_financial_elem_id) "SUS_FINANCIAL_ELEM_ID",
1172 gib.sus_product_id, gib.sus_natural_account_id,
1173 gib.sus_channel_id, gib.sus_line_item_id,
1174 gib.sus_project_id, gib.sus_customer_id,
1175 gib.sus_task_id, gib.sus_user_dim1_id,
1176 gib.sus_user_dim2_id, gib.sus_user_dim3_id,
1177 gib.sus_user_dim4_id, gib.sus_user_dim5_id,
1178 gib.sus_user_dim6_id, gib.sus_user_dim7_id,
1179 gib.sus_user_dim8_id, gib.sus_user_dim9_id,
1180 gib.sus_user_dim10_id, fc.currency_code
1181 FROM GCS_INTERCO_ELM_TRX giet,
1182 GCS_FLATTENED_RELNS gfr,
1183 GCS_FLATTENED_RELNS gfr1,
1184 GCS_INTERCO_MEMBERS gim,
1185 GCS_INTERCO_RULES_B gib ,
1186 FND_CURRENCIES fc
1187 WHERE giet.hierarchy_id = p_hierarchy_id
1188 AND giet.cal_period_id = p_cal_period_id
1189 AND fc.currency_code IN (p_currency_code,'STAT')
1190 AND gfr.run_name = g_consolidation_run_name
1191 AND gfr.parent_entity_id = p_entity_id
1192 AND giet.src_entity_id = gfr.child_entity_ID
1193 AND NVL(gfr.consolidation_type_code,'X') <> 'NONE'
1194 AND gfr1.run_name = g_consolidation_run_name
1195 AND gfr1.parent_entity_id = p_entity_id
1196 AND giet.target_entity_id = gfr1.child_entity_id
1197 AND NVL(gfr1.consolidation_type_code,'X') <> 'NONE'
1198 AND giet.src_entity_id <> giet.target_entity_id
1199 AND giet.line_item_id = gim.line_item_id
1200 AND gim.line_item_group = 1
1201 AND gim.rule_id = gib.rule_id
1202 AND gib.enabled_flag = 'Y'
1203 AND NOT EXISTS
1204 (SELECT 'X' FROM gcs_interco_elm_trx giet1,
1205 gcs_interco_members gim1
1206 WHERE giet1.hierarchy_id = p_hierarchy_id
1207 AND giet1.cal_period_id = p_cal_period_id
1208 AND giet1.src_entity_id =
1209 giet.target_entity_id
1210 AND giet1.target_entity_id =
1211 giet.src_entity_id
1212 AND gim1.rule_id = gim.rule_id
1213 AND giet1.company_cost_center_org_id >
1214 giet1.intercompany_id
1215 AND gim1.line_item_group >
1216 gim.line_item_group
1217 AND gim1.line_item_id = gim.line_item_id)
1218
1219 GROUP BY
1220 giet.src_entity_id,
1221 giet.target_entity_id,
1222 gib.rule_id,gib.threshold_currency,
1223 gib.threshold_amount,
1224 gib.sus_financial_elem_id,
1225 gib.sus_product_id,gib.sus_natural_account_id,
1226 gib.sus_channel_id,gib.sus_line_item_id,
1227 gib.sus_project_id,gib.sus_customer_id,
1228 gib.sus_task_id,gib.sus_user_dim1_id,
1229 gib.sus_user_dim2_id,gib.sus_user_dim3_id,
1230 gib.sus_user_dim4_id, gib.sus_user_dim5_id,
1231 gib.sus_user_dim6_id, gib.sus_user_dim7_id,
1232 gib.sus_user_dim8_id, gib.sus_user_dim9_id,
1233 gib.sus_user_dim10_id, fc.currency_code) git
1234 WHERE NOT EXISTS
1235 (SELECT 'X'
1236 FROM GCS_CONS_ENG_RUN_DTLS gcer
1237 WHERE gcer.child_entity_id = git.src_id
1238 AND gcer.contra_child_entity_id = git.tar_id
1239 AND gcer.run_name = g_consolidation_run_name);
1240
1241
1242 g_no_rows := NVL(SQL%ROWCOUNT,0);
1243
1244
1245 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1246 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
1247 FND_MESSAGE.Set_Token('NUM',TO_CHAR(NVL(SQL%ROWCOUNT,0)));
1248 FND_MESSAGE.Set_Token('TABLE','GCS_INTERCO_HDR_GT');
1249
1250
1251
1252 FND_LOG.String (fnd_log.level_procedure,
1253 g_pkg_name || '.' || l_api_name,
1254 'SHRD0117: '||FND_MESSAGE.get);
1255 END IF;
1256
1257 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1258 fnd_log.STRING (fnd_log.level_procedure,
1259 g_pkg_name || '.' || l_api_name,
1260 'Insert distinct pairs of entities for each rule '
1261 || 'into GCS_INTERCO_HDR_GT in full '
1262 || 'consolidation run mode and elim-mode=''IE'''
1263 || '- Intercompany rule Payables side'
1264 );
1265 END IF;
1266
1267 -- Fixed bug# 4217286.
1268 -- Added DECODE(fc.currency_code,'STAT',10000,
1269 -- gib.sus_financial_elem_id) "SUS_FINANCIAL_ELEM_ID"
1270 -- to the following statement.
1271
1272 INSERT INTO GCS_INTERCO_HDR_GT
1273 (entry_id, source_entity_id, target_entity_id, rule_id,
1274 threshold_currency,threshold_amount,sus_financial_elem_id,
1275 sus_product_id,sus_natural_account_id,
1276 sus_channel_id,sus_line_item_id,sus_project_id,sus_customer_id,
1277 sus_task_id, sus_user_dim1_id, sus_user_dim2_id,sus_user_dim3_id,
1278 sus_user_dim4_id, sus_user_dim5_id, sus_user_dim6_id,
1279 sus_user_dim7_id, sus_user_dim8_id, sus_user_dim9_id,
1280 sus_user_dim10_id, creation_date, created_by,
1281 last_update_date, last_updated_by, last_update_login,
1282 currency_code)
1283 SELECT GCS_ENTRY_HEADERS_S.NEXTVAL, git.src_id,
1284 git.tar_id, git.rule_id,
1285 git.threshold_currency,
1286 git.threshold_amount, git.sus_financial_elem_id,
1287 git.sus_product_id,git.sus_natural_account_id,
1288 git.sus_channel_id,git.sus_line_item_id,
1289 git.sus_project_id,git.sus_customer_id,
1290 git.sus_task_id,git.sus_user_dim1_id,
1291 git.sus_user_dim2_id,git.sus_user_dim3_id,
1292 git.sus_user_dim4_id, git.sus_user_dim5_id,
1293 git.sus_user_dim6_id, git.sus_user_dim7_id,
1294 git.sus_user_dim8_id, git.sus_user_dim9_id,
1295 git.sus_user_dim10_id,SYSDATE,g_fnd_user_id,sysdate,
1296 g_fnd_user_id,g_fnd_login_id, git.currency_code
1297 FROM (SELECT giet.target_entity_id
1298 src_id,
1299 giet.src_entity_id
1300 tar_id,
1301 gib.rule_id, gib.threshold_currency,
1302 gib.threshold_amount,
1303 DECODE(fc.currency_code,'STAT',10000,
1304 gib.sus_financial_elem_id) "SUS_FINANCIAL_ELEM_ID",
1305 gib.sus_product_id, gib.sus_natural_account_id,
1306 gib.sus_channel_id, gib.sus_line_item_id,
1307 gib.sus_project_id, gib.sus_customer_id,
1308 gib.sus_task_id, gib.sus_user_dim1_id,
1309 gib.sus_user_dim2_id, gib.sus_user_dim3_id,
1310 gib.sus_user_dim4_id, gib.sus_user_dim5_id,
1311 gib.sus_user_dim6_id, gib.sus_user_dim7_id,
1312 gib.sus_user_dim8_id, gib.sus_user_dim9_id,
1313 gib.sus_user_dim10_id, fc.currency_code
1314 FROM GCS_INTERCO_ELM_TRX giet,
1315 GCS_FLATTENED_RELNS gfr,
1316 GCS_FLATTENED_RELNS gfr1,
1317 GCS_INTERCO_MEMBERS gim,
1318 GCS_INTERCO_RULES_B gib ,
1319 FND_CURRENCIES fc
1320 WHERE giet.hierarchy_id = p_hierarchy_id
1321 AND giet.cal_period_id = p_cal_period_id
1322 AND fc.currency_code IN (p_currency_code,'STAT')
1323 AND gfr.run_name = g_consolidation_run_name
1324 AND gfr.parent_entity_id = p_entity_id
1325 AND giet.src_entity_id = gfr.child_entity_ID
1326 AND NVL(gfr.consolidation_type_code,'X') <> 'NONE'
1327 AND gfr1.run_name = g_consolidation_run_name
1328 AND gfr1.parent_entity_id = p_entity_id
1329 AND giet.target_entity_id = gfr1.child_entity_id
1330 AND NVL(gfr1.consolidation_type_code,'X') <> 'NONE'
1331 AND giet.src_entity_id <> giet.target_entity_id
1332 AND giet.line_item_id = gim.line_item_id
1333 AND gim.line_item_group = 2
1334 AND gim.rule_id = gib.rule_id
1335 AND gib.enabled_flag = 'Y'
1336 AND NOT EXISTS
1337 (SELECT 'Y'
1338 FROM GCS_INTERCO_HDR_GT gihg1,
1339 GCS_INTERCO_MEMBERS gim1
1340 WHERE gihg1.target_entity_id =
1341 DECODE(gim1.line_item_id,
1342 gim.line_item_id,
1343 giet.target_entity_id,
1344 giet.src_entity_id)
1345 AND gihg1.source_entity_id =
1346 DECODE(gim1.line_item_id,
1347 gim.line_item_id,
1348 giet.src_entity_id,
1349 giet.target_entity_id)
1350 AND gihg1.rule_id = gim.rule_id
1351 AND gihg1.rule_id = gim.rule_id
1352 AND gim1.rule_id = gihg1.rule_id
1353 AND gim1.line_item_group = 1)
1354 GROUP BY
1355 giet.src_entity_id,
1356 giet.target_entity_id,
1357 gib.rule_id,gib.threshold_currency,
1358 gib.threshold_amount,
1359 gib.sus_financial_elem_id,
1360 gib.sus_product_id,gib.sus_natural_account_id,
1361 gib.sus_channel_id,gib.sus_line_item_id,
1362 gib.sus_project_id,gib.sus_customer_id,
1363 gib.sus_task_id,gib.sus_user_dim1_id,
1364 gib.sus_user_dim2_id,gib.sus_user_dim3_id,
1365 gib.sus_user_dim4_id, gib.sus_user_dim5_id,
1366 gib.sus_user_dim6_id, gib.sus_user_dim7_id,
1367 gib.sus_user_dim8_id, gib.sus_user_dim9_id,
1368 gib.sus_user_dim10_id, fc.currency_code) git
1369 WHERE NOT EXISTS
1370 (SELECT 'X'
1371 FROM GCS_CONS_ENG_RUN_DTLS gcer
1372 WHERE gcer.child_entity_id = git.src_id
1373 AND gcer.contra_child_entity_id = git.tar_id
1374 AND gcer.run_name = g_consolidation_run_name);
1375
1376 IF (NVL(SQL%ROWCOUNT,0) <> 0) THEN
1377 g_no_rows := NVL(SQL%ROWCOUNT,0);
1378 END IF;
1379
1380 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1381 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
1382 FND_MESSAGE.Set_Token('NUM',TO_CHAR(NVL(SQL%ROWCOUNT,0)));
1383 FND_MESSAGE.Set_Token('TABLE','GCS_INTERCO_HDR_GT');
1384
1385
1386
1387 FND_LOG.String (fnd_log.level_procedure,
1388 g_pkg_name || '.' || l_api_name,
1389 'SHRD0117: '||FND_MESSAGE.get);
1390 END IF;
1391 -- Start Bugfix 5974635
1392
1393 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1394 fnd_log.STRING (fnd_log.level_procedure,
1395 g_pkg_name || '.' || l_api_name,
1396 'Delete pair(s) of entities that are already'
1397 || ' eliminated, for e.g. at a mid level parent'
1398 );
1399 END IF;
1400
1401 DELETE FROM GCS_INTERCO_HDR_GT gihg
1402 WHERE EXISTS
1403 (SELECT 'X' FROM GCS_CONS_ENG_RUN_DTLS gcer
1404 WHERE gcer.run_name = g_consolidation_run_name
1405 AND gcer.category_code = 'INTERCOMPANY'
1406 AND gcer.child_entity_id = gihg.target_entity_id
1407 AND gcer.contra_child_entity_id =
1408 gihg.source_entity_id
1409 AND gcer.rule_id = gihg.rule_id
1410 AND gcer.consolidation_entity_id <>
1411 g_entity_id);
1412
1413 l_no_rows := NVL(SQL%ROWCOUNT,0);
1414
1415 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1416 FND_MESSAGE.Set_Name('SQLGL','SHRD0119');
1417 FND_MESSAGE.Set_Token('NUM',TO_CHAR(l_no_rows));
1418 FND_MESSAGE.Set_Token('TABLE','GCS_INTERCO_HDR_GT');
1419
1420
1421 FND_LOG.String (fnd_log.level_procedure,
1422 g_pkg_name || '.' || l_api_name,
1423 'SHRD0117: '||FND_MESSAGE.get);
1424
1425 END IF;
1426
1427 -- End Bugfix 5974635
1428
1429 ELSIF (p_elim_mode = 'IA') THEN
1430
1431 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1432 fnd_log.STRING (fnd_log.level_procedure,
1433 g_pkg_name || '.' || l_api_name,
1434 'Insert distinct pairs of entities for each rule '
1435 || 'into GCS_INTERCO_HDR_GT in full '
1436 || 'consolidation run mode and elim-mode=''IA'''
1437 );
1438 END IF;
1439
1440 INSERT INTO GCS_INTERCO_HDR_GT
1441 (entry_id, source_entity_id, target_entity_id, rule_id,
1442 threshold_currency,threshold_amount,sus_financial_elem_id,
1443 sus_product_id,sus_natural_account_id,
1444 sus_channel_id,sus_line_item_id,sus_project_id,sus_customer_id,
1445 sus_task_id, sus_user_dim1_id, sus_user_dim2_id,sus_user_dim3_id,
1446 sus_user_dim4_id, sus_user_dim5_id, sus_user_dim6_id,
1447 sus_user_dim7_id, sus_user_dim8_id, sus_user_dim9_id,
1448 sus_user_dim10_id, creation_date, created_by,
1449 last_update_date, last_updated_by, last_update_login,
1450 currency_code)
1451 SELECT GCS_ENTRY_HEADERS_S.NEXTVAL, git.src_entity_id,
1452 git.target_entity_id, git.rule_id,
1453 git.threshold_currency,
1454 git.threshold_amount, git.sus_financial_elem_id,
1455 git.sus_product_id,git.sus_natural_account_id,
1456 git.sus_channel_id,git.sus_line_item_id,
1457 git.sus_project_id,git.sus_customer_id,
1458 git.sus_task_id,git.sus_user_dim1_id,
1459 git.sus_user_dim2_id,git.sus_user_dim3_id,
1460 git.sus_user_dim4_id, git.sus_user_dim5_id,
1461 git.sus_user_dim6_id, git.sus_user_dim7_id,
1462 git.sus_user_dim8_id, git.sus_user_dim9_id,
1463 git.sus_user_dim10_id,SYSDATE,g_fnd_user_id,SYSDATE,
1464 g_fnd_user_id,g_fnd_login_id, git.currency_code
1465 FROM (SELECT giet.src_entity_id,giet.target_entity_id,
1466 gib.rule_id, gib.threshold_currency,
1467 gib.threshold_amount, gib.sus_financial_elem_id,
1468 gib.sus_product_id, gib.sus_natural_account_id,
1469 gib.sus_channel_id, gib.sus_line_item_id,
1470 gib.sus_project_id, gib.sus_customer_id,
1471 gib.sus_task_id, gib.sus_user_dim1_id,
1472 gib.sus_user_dim2_id, gib.sus_user_dim3_id,
1473 gib.sus_user_dim4_id, gib.sus_user_dim5_id,
1474 gib.sus_user_dim6_id, gib.sus_user_dim7_id,
1475 gib.sus_user_dim8_id, gib.sus_user_dim9_id,
1476 gib.sus_user_dim10_id, fc.currency_code
1477 FROM GCS_INTERCO_ELM_TRX giet,
1478 GCS_INTERCO_MEMBERS gim,
1479 GCS_INTERCO_RULES_B gib,
1480 FND_CURRENCIES fc
1481 WHERE giet.hierarchy_id = p_hierarchy_id
1482 AND giet.cal_period_id = p_cal_period_id
1483 AND fc.currency_code IN (P_currency_code, 'STAT')
1484 AND giet.src_entity_id = p_entity_id
1485 AND giet.target_entity_id = giet.src_entity_id
1486 AND giet.line_item_id = gim.line_item_id
1487 AND gim.rule_id = gib.rule_id
1488 AND gib.enabled_flag = 'Y'
1489 GROUP BY giet.src_entity_id,giet.target_entity_id,
1490 gib.rule_id,gib.threshold_currency,
1491 gib.threshold_amount,
1492 gib.sus_financial_elem_id,
1493 gib.sus_product_id,gib.sus_natural_account_id,
1494 gib.sus_channel_id,gib.sus_line_item_id,
1495 gib.sus_project_id,gib.sus_customer_id,
1496 gib.sus_task_id,gib.sus_user_dim1_id,
1497 gib.sus_user_dim2_id,gib.sus_user_dim3_id,
1498 gib.sus_user_dim4_id, gib.sus_user_dim5_id,
1499 gib.sus_user_dim6_id, gib.sus_user_dim7_id,
1500 gib.sus_user_dim8_id, gib.sus_user_dim9_id,
1501 gib.sus_user_dim10_id , fc.currency_code) git;
1502
1503 g_no_rows := NVL(SQL%ROWCOUNT,0);
1504
1505 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1506 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
1507 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
1508 FND_MESSAGE.Set_Token('TABLE','GCS_INTERCO_HDR_GT');
1509
1510
1511 FND_LOG.String (fnd_log.level_procedure,
1512 g_pkg_name || '.' || l_api_name,
1513 'SHRD0117: '||FND_MESSAGE.get);
1514 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0117: '||FND_MESSAGE.get);
1515 END IF;
1516
1517 END IF; -- End of Intercompany or intra company modes.
1518
1519 IF (g_no_rows <= 0) THEN
1520
1521 g_stop_processing := TRUE;
1522
1523
1524 RETURN TRUE;
1525 END IF;
1526
1527
1528 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1529 fnd_log.STRING (fnd_log.level_procedure,
1530 g_pkg_name || '.' || l_api_name,
1531 'Insert avialable information '
1532 || 'into GCS_CONS_ENG_RUN_DTLS '
1533 || ' if there is an error the information will be'
1534 || ' saved upto this point.'
1535 );
1536 END IF;
1537
1538 INSERT INTO gcs_cons_eng_run_dtls
1539 (run_detail_id, run_name, Consolidation_entity_id
1540 , child_entity_id, contra_child_entity_id, entry_id, rule_id,
1541 request_error_code, bp_request_error_code, category_code,
1542 creation_date, created_by, last_update_date,
1543 last_updated_by, last_update_login)
1544 SELECT gcs_cons_eng_run_dtls_s.nextval,
1545 g_consolidation_run_name,
1546 DECODE(g_elim_code, 'IE',p_entity_id,'IA', g_cons_entity_id),
1547 gehg.source_entity_id, gehg.target_entity_id,
1548 gehg.entry_id, gehg.rule_id,
1549 'WARNING', 'WARNING',
1550 DECODE(g_elim_code, 'IE', 'INTERCOMPANY', 'IA', 'INTRACOMPANY'),
1551 SYSDATE, g_fnd_user_id,
1552 SYSDATE, g_fnd_user_id,
1553 g_fnd_login_id
1554 FROM GCS_INTERCO_HDR_GT gehg
1555 WHERE gehg.currency_code <> 'STAT';
1556
1557
1558 g_no_rows := NVL(SQL%ROWCOUNT,0);
1559
1560 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1561 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
1562 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
1563 FND_MESSAGE.Set_Token('TABLE','GCS_CONS_ENG_RUN_DTLS');
1564
1565 FND_LOG.String (fnd_log.level_procedure,
1566 g_pkg_name || '.' || l_api_name,
1567 'SHRD0117: '||FND_MESSAGE.get);
1568 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0117: '||FND_MESSAGE.get);
1569 END IF;
1570
1571 /* ------------------------------------------------------------------------+
1572
1573 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1574 fnd_log.STRING (fnd_log.level_procedure,
1575 g_pkg_name || '.' || l_api_name,
1576 'STAT rows- with serate rule'
1577 ||' Insert avialable information '
1578 || 'into GCS_CONS_ENG_RUN_DTLS '
1579 || ' if there is an error the information will be'
1580 || ' saved upto this point.'
1581 );
1582 END IF;
1583
1584 INSERT INTO gcs_cons_eng_run_dtls
1585 (run_detail_id, run_name, Consolidation_entity_id
1586 , child_entity_id, contra_child_entity_id, entry_id, rule_id,
1587 request_error_code, bp_request_error_code, category_code,
1588 xlate_request_error_code, bp_xlate_request_error_code,
1589 creation_date, created_by, last_update_date,
1590 last_updated_by, last_update_login)
1591 SELECT gcs_cons_eng_run_dtls_s.nextval,
1592 g_consolidation_run_name,
1593 DECODE(g_elim_code, 'IE',p_entity_id,'IA', g_cons_entity_id),
1594 gehg.source_entity_id, gehg.target_entity_id,
1595 gehg.entry_id, gehg.rule_id,
1596 'WARNING', 'WARNING',
1597 DECODE(g_elim_code, 'IE', 'INTERCOMPANY', 'IA', 'INTRACOMPANY'),
1598 DECODE(g_elim_code, 'IA',
1599 DECODE( p_xlation_required,'Y',
1600 'NOT_STARTED','N','NOT_APPLICABLE'),NULL),
1601 DECODE(g_elim_code, 'IA',
1602 DECODE( p_xlation_required,'Y',
1603 'NOT_STARTED','N','NOT_APPLICABLE'),NULL),
1604 SYSDATE, g_fnd_user_id,
1605 SYSDATE, g_fnd_user_id,
1606 g_fnd_login_id
1607 FROM GCS_INTERCO_HDR_GT gehg
1608 WHERE gehg.currency_code = 'STAT'
1609 AND NOT EXISTS ( SELECT 1 FROM gcs_cons_eng_run_dtls gcr1
1610 WHERE gehg.source_entity_id = gcr1.child_entity_id
1611 AND gehg.target_entity_id =
1612 gcr1.contra_child_entity_id
1613 AND gehg.rule_id = gcr1.rule_id);
1614
1615
1616 g_no_rows := NVL(SQL%ROWCOUNT,0);
1617
1618 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1619 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
1620 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
1621 FND_MESSAGE.Set_Token('TABLE','GCS_CONS_ENG_RUN_DTLS');
1622
1623 FND_LOG.String (fnd_log.level_procedure,
1624 g_pkg_name || '.' || l_api_name,
1625 'SHRD0117: '||FND_MESSAGE.get);
1626 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0117: '||FND_MESSAGE.get);
1627 END IF;
1628
1629 */
1630 -- In case of an error, we will roll back to this point in time.
1631 SAVEPOINT gcs_cons_eng_insr_warning;
1632
1633
1634 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1635
1636 fnd_log.STRING (fnd_log.level_procedure,
1637 g_pkg_name || '.' || l_api_name,
1638 gcs_utility_pkg.g_module_success
1639 || ' '
1640 || l_api_name
1641 || '() '
1642 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1643 );
1644 END IF;
1645
1646
1647 RETURN TRUE;
1648
1649 EXCEPTION
1650
1651 WHEN NO_DATA_FOUND THEN
1652
1653
1654 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1655
1656 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1657 g_pkg_name || '.' || l_api_name,
1658 'Error in inserting intercompany headers'
1659 ||' into the GCS_INTERCO_HDR_GT' );
1660
1661 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1662 g_pkg_name || '.' || l_api_name,
1663 SUBSTR(SQLERRM, 1, 255));
1664
1665 fnd_log.STRING (fnd_log.level_unexpected,
1666 g_pkg_name || '.' || l_api_name,
1667 gcs_utility_pkg.g_module_failure
1668 || ' '
1669 || l_api_name
1670 || '() '
1671 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1672 );
1673 END IF;
1674
1675 Return FALSE;
1676
1677 WHEN OTHERS THEN
1678
1679
1680 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1681
1682 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1683 g_pkg_name || '.' || l_api_name,
1684 SUBSTR(SQLERRM, 1, 255));
1685
1686 fnd_log.STRING (fnd_log.level_unexpected,
1687 g_pkg_name || '.' || l_api_name,
1688 gcs_utility_pkg.g_module_failure
1689 || ' '
1690 || l_api_name
1691 || '() '
1692 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1693 );
1694 END IF;
1695
1696 INSERT INTO gcs_cons_eng_run_dtls
1697 (run_detail_id, run_name, Consolidation_entity_id
1698 , child_entity_id, contra_child_entity_id, entry_id, rule_id,
1699 request_error_code, bp_request_error_code, category_code,
1700 creation_date, created_by, last_update_date,
1701 last_updated_by, last_update_login)
1702 SELECT gcs_cons_eng_run_dtls_s.nextval,
1703 g_consolidation_run_name,
1704 DECODE(g_elim_code, 'IE',p_entity_id,'IA', g_cons_entity_id),
1705 NULL, NULL,
1706 NULL, NULL,
1707 'WARNING','WARNING',
1708 DECODE(g_elim_code, 'IE', 'INTERCOMPANY', 'IA', 'INTRACOMPANY'),
1709 SYSDATE, g_fnd_user_id,
1710 SYSDATE, g_fnd_user_id,
1711 g_fnd_login_id
1712 FROM DUAL;
1713
1714
1715 g_no_rows := NVL(SQL%ROWCOUNT,0);
1716
1717 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1718 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
1719 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
1720 FND_MESSAGE.Set_Token('TABLE','GCS_CONS_ENG_RUN_DTLS');
1721
1722 FND_LOG.String (fnd_log.level_procedure,
1723 g_pkg_name || '.' || l_api_name,
1724 'SHRD0117: '||FND_MESSAGE.get);
1725 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0117: '||FND_MESSAGE.get);
1726 END IF;
1727
1728
1729
1730 SAVEPOINT gcs_cons_eng_insr_Hdr_warning;
1731
1732 Return FALSE;
1733
1734
1735
1736 END INSR_INTERCO_HDRS;
1737
1738 --
1739 -- Function
1740 -- insr_elimination_hdrs
1741 -- Purpose
1742 -- Inserts elimination entry headers into GCS_ENTRY_HEADERS.
1743 --
1744
1745 -- Process steps are as follows:
1746 -- If the threshold currency of a intercompany rule is diffrent from
1747 -- consolidation entity currency, then get the conversion rate for the
1748 -- target currency.
1749
1750 -- Then insert elimination entries headers into GCS_ENTRY_HEADERS.
1751
1752 -- Then raise a warning if suspense exceeded for a pair of entities.
1753
1754
1755 FUNCTION INSR_ELIMINATION_HDRS(p_hierarchy_id IN NUMBER,
1756 p_cal_period_id IN NUMBER,
1757 p_entity_id IN NUMBER,
1758 p_balance_type VARCHAR2,
1759 p_currency_code IN VARCHAR2)
1760 RETURN BOOLEAN IS
1761
1762 l_api_name VARCHAR2(50) := 'INSR_ELIMINATION_HDRS';
1763 l_period_end_date DATE;
1764 l_warning NUMBER(4) := 0;
1765 x_corp_rate NUMBER := 0;
1766 x_errbuf VARCHAR2(255);
1767 X_errcode NUMBER;
1768 l_errbuf VARCHAR2(100);
1769 l_errcode NUMBER := 0;
1770
1771
1772
1773 -- Used to get the minimum accountable unit for the currency given.
1774
1775 CURSOR threshold_conv_rate IS
1776 SELECT gihg.entry_id, gihg.threshold_currency,
1777 nvl(minimum_accountable_unit, power(10, -precision)) mau,
1778 NVL(precision,2) pres
1779 FROM GCS_INTERCO_HDR_GT gihg, GCS_ENTRY_LINES gel,
1780 fnd_currencies fnc
1781 WHERE gihg.entry_id = gel.entry_id
1782 AND gihg.currency_code <> 'STAT'
1783 AND gihg.threshold_currency <> p_currency_code
1784 AND gihg.currency_code = fnc.currency_code
1785 GROUP BY gihg.entry_id, threshold_currency,
1786 nvl(minimum_accountable_unit, power(10, -precision)),
1787 NVL(precision,2);
1788
1789
1790 -- Used to get the minimum accountable unit for the currency given.
1791 -- The following cursor is not necessary, since we can merge
1792 -- the following join in the above sql statement.
1793
1794 /* CURSOR ccy_mau_c(c_ccy VARCHAR2) IS
1795 SELECT nvl(minimum_accountable_unit, power(10, -precision)) mau,
1796 NVL(precision,2) pres
1797 FROM fnd_currencies
1798 WHERE currency_code = c_ccy;
1799 */
1800 BEGIN
1801
1802 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1803 fnd_log.STRING (fnd_log.level_procedure,
1804 g_pkg_name || '.' || l_api_name,
1805 gcs_utility_pkg.g_module_enter
1806 || ' '
1807 || l_api_name
1808 || '() '
1809 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1810 );
1811 END IF;
1812
1813 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1814 fnd_log.STRING (fnd_log.level_procedure,
1815 g_pkg_name || '.' || l_api_name,
1816 ' Arguments passed to Insr_Interco_Hdr() '
1817 ||' Hierarchy_Id :'||p_hierarchy_id
1818 ||' Cal_Period_Id: '||p_cal_period_id
1819 ||' Entity_Id: '||p_entity_id
1820 ||' Balance_Type: '||p_balance_type
1821 ||' Currency_Code: '||p_currency_code);
1822
1823 END IF;
1824
1825 g_no_rows := 0;
1826
1827 l_period_end_date := g_period_end_date;
1828
1829 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1830 fnd_log.STRING (fnd_log.level_procedure,
1831 g_pkg_name || '.' || l_api_name,
1832 ' Intercompany- Getting conversion rate');
1833 END IF;
1834
1835
1836 for entries in threshold_conv_rate loop
1837 -- for conv_rate in ccy_mau_c(entries.threshold_currency) loop
1838
1839 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1840 fnd_log.STRING (fnd_log.level_procedure,
1841 g_pkg_name || '.' || l_api_name,
1842 'Intercompany- Inside Loop'
1843 || ' in GCS_INTERCO_HDR_GT'
1844 );
1845 END IF;
1846
1847 BEGIN
1848
1849 /* x_corp_rate := GL_CURRENCY_API.get_rate(entries.threshold_currency,
1850 p_currency_code,
1851 l_period_end_date,
1852 'Corporate'); */
1853
1854 -- Call the gcs_utility_pkg.get_conversion_rate to get the
1855 -- conversion rate.
1856
1857 GCS_UTILITY_PKG.Get_Conversion_Rate
1858 (p_source_currency => entries.threshold_currency,
1859 p_target_currency => p_currency_code,
1860 p_cal_period_id => p_cal_period_id,
1861 p_conversion_rate => x_corp_rate,
1862 P_errbuf => l_errbuf,
1863 p_errcode => l_errcode);
1864
1865
1866 If ( x_corp_rate <> 1) THEN
1867
1868 -- Suspense exceeded flag population.
1869 -- If the absolute net suspense exceeds threshold amount, then
1870 -- insert 'Y' otherwise 'N'. If there is no conversion rate, then
1871 -- insert 'X' into suspense exceeded flag of gcs_entry_headers
1872 -- For stat currency this flag is always 'N'
1873
1874 -- Now Insert elimination entry headers into GCS_ENTRY_HEADERS.
1875
1876 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1877 fnd_log.STRING (fnd_log.level_procedure,
1878 g_pkg_name || '.' || l_api_name,
1879 'Intercompany- Inserting consolidation currency '
1880 ||' entity entry '
1881 ||' headers into GCS_ENTRY_HEADERS '
1882 ||'- where legitimate conversion rate is available.'
1883 );
1884 END IF;
1885
1886 g_no_rows := 0;
1887
1888 INSERT INTO gcs_entry_headers
1889 (entry_id, entry_name, hierarchy_id, disabled_flag,
1890 entity_id, currency_code, balance_type_code,
1891 start_cal_period_id, end_cal_period_id,
1892 description, entry_type_code,
1893 processed_run_name, category_code,
1894 process_code, suspense_exceeded_flag,
1895 creation_date, created_by, last_update_date,
1896 last_updated_by, last_update_login, PERIOD_INIT_ENTRY_FLAG)
1897 SELECT gehg.ENTRY_ID,
1898 gehg.ENTRY_ID,
1899 MAX(p_hierarchy_id), 'N',
1900 MAX(g_elim_entity_id), gehg.currency_code,
1901 p_balance_type, MAX(p_cal_period_id),
1902 MAX(p_cal_period_id),
1903 Decode(g_elim_code,'IE','Intercompany ', 'Intracompany ')
1904 || MAX(girt.rule_name)
1905 ||' executed for '||g_elim_entity_name,
1906 'AUTOMATIC', g_consolidation_run_name,
1907 DECODE(g_elim_code,'IE','INTERCOMPANY','IA','INTRACOMPANY'),
1908 'SINGLE_RUN_FOR_PERIOD',
1909 DECODE (GREATEST(ABS(SUM(NVL(giet.ytd_debit_balance_e,0))-
1910 SUM(NVL(giet.ytd_credit_balance_e,0))),
1911 ROUND(((MAX(gehg.threshold_amount)*
1912 NVL(x_corp_rate,1))/entries.mau),
1913 NVL(entries.pres,2))* entries.mau),
1914 ROUND(((MAX(gehg.threshold_amount)*
1915 NVL(x_corp_rate,1))/entries.mau),
1916 NVL(entries.pres,2))* entries.mau, 'N', 'Y'),
1917 MAX(SYSDATE), MAX(g_fnd_user_id),
1918 MAX(SYSDATE), MAX(g_fnd_user_id),
1919 MAX(g_fnd_login_id), 'N'
1920 FROM GCS_INTERCO_HDR_GT gehg,
1921 GCS_INTERCO_RULES_TL girt,
1922 GCS_ENTRY_LINES giet
1923 WHERE gehg.entry_id = entries.entry_id
1924 AND gehg.rule_id = girt.rule_id
1925 AND girt.language = USERENV('LANG')
1926 AND gehg.entry_id = giet.entry_id(+)
1927 AND giet.line_item_id (+) = gehg.sus_line_item_id
1928 AND giet.description(+) = 'SUSPENSE_LINE'
1929 GROUP BY gehg.ENTRY_ID, gehg.currency_code;
1930
1931 g_no_rows := SQL%ROWCOUNT;
1932
1933 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1934 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
1935 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
1936 FND_MESSAGE.Set_Token('TABLE','GCS_ENTRY_HEADERS');
1937
1938
1939 FND_LOG.String (fnd_log.level_procedure,
1940 g_pkg_name || '.' || l_api_name,
1941 'SHRD0117: '||FND_MESSAGE.get);
1942 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0117: '||FND_MESSAGE.get);
1943 END IF;
1944
1945 ELSIF (x_corp_rate = 1) THEN
1946 -- If the conversion rate is not available then there is no need to
1947 -- convert the threshold amount.
1948
1949 -- Insert elimination entry headers into GCS_ENTRY_HEADERS.
1950
1951 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1952 fnd_log.STRING (fnd_log.level_procedure,
1953 g_pkg_name || '.' || l_api_name,
1954 'Intercompany- Inserting consolidation currency '
1955 ||' entity entry '
1956 ||' headers into GCS_ENTRY_HEADERS '
1957 ||'- where conversion rate is 1, that means valid '
1958 ||' conversion rate is not available.'
1959 );
1960 END IF;
1961
1962 g_no_rows := 0;
1963
1964 INSERT INTO gcs_entry_headers
1965 (entry_id, entry_name, hierarchy_id, disabled_flag,
1966 entity_id, currency_code, balance_type_code,
1967 start_cal_period_id, end_cal_period_id,
1968 description, entry_type_code,
1969 processed_run_name, category_code,
1970 process_code, suspense_exceeded_flag,
1971 creation_date, created_by, last_update_date,
1972 last_updated_by, last_update_login, PERIOD_INIT_ENTRY_FLAG)
1973 SELECT gehg.ENTRY_ID,
1974 gehg.ENTRY_ID,
1975 p_hierarchy_id, 'N',
1976 g_elim_entity_id, gehg.currency_code,
1977 p_balance_type, p_cal_period_id,
1978 p_cal_period_id,
1979 Decode(g_elim_code,'IE','Intercompany ', 'Intracompany ')
1980 || girt.rule_name
1981 ||' executed for '||g_elim_entity_name,
1982 'AUTOMATIC', g_consolidation_run_name,
1983 DECODE(g_elim_code,'IE','INTERCOMPANY','IA','INTRACOMPANY'),
1984 'SINGLE_RUN_FOR_PERIOD',
1985 'X',
1986 SYSDATE, g_fnd_user_id,
1987 SYSDATE, g_fnd_user_id,
1988 g_fnd_login_id, 'N'
1989 FROM GCS_INTERCO_HDR_GT gehg,
1990 GCS_INTERCO_RULES_TL girt
1991 WHERE gehg.entry_id = entries.entry_id
1992 AND gehg.rule_id = girt.rule_id
1993 AND girt.language = USERENV('LANG');
1994
1995
1996 g_no_rows := SQL%ROWCOUNT;
1997
1998 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1999 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
2000 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
2001 FND_MESSAGE.Set_Token('TABLE','GCS_ENTRY_HEADERS');
2002
2003
2004 FND_LOG.String (fnd_log.level_procedure,
2005 g_pkg_name || '.' || l_api_name,
2006 'SHRD0117: '||FND_MESSAGE.get);
2007 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0117: '||FND_MESSAGE.get);
2008 END IF;
2009
2010
2011 END IF;
2012
2013 EXCEPTION
2014 WHEN OTHERS THEN
2015 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2016 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
2017 g_pkg_name || '.' || l_api_name,
2018 SUBSTR(SQLERRM, 1, 255));
2019 END IF;
2020
2021
2022 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2023 fnd_log.STRING (fnd_log.level_procedure,
2024 g_pkg_name || '.' || l_api_name,
2025 'Intercompany- Corporate rate is not available'
2026 || ' from from_currency:'
2027 || entries.threshold_currency
2028 ||' to to_currency : '|| p_currency_code
2029 ||' and end date is: '||l_period_end_date
2030 );
2031 END IF;
2032
2033 -- If the conversion rate is not available then there is no need to
2034 -- convert the threshold amount.
2035
2036 g_no_rows := 0;
2037
2038 INSERT INTO gcs_entry_headers
2039 (entry_id, entry_name, hierarchy_id, disabled_flag,
2040 entity_id, currency_code, balance_type_code,
2041 start_cal_period_id, end_cal_period_id,
2042 description, entry_type_code,
2043 processed_run_name, category_code,
2044 process_code, suspense_exceeded_flag,
2045 creation_date, created_by, last_update_date,
2046 last_updated_by, last_update_login, PERIOD_INIT_ENTRY_FLAG)
2047
2048 SELECT gehg.ENTRY_ID, gehg.ENTRY_ID,
2049 p_hierarchy_id, 'N',
2050 g_elim_entity_id, gehg.currency_code,
2051 p_balance_type, p_cal_period_id,
2052 p_cal_period_id,
2053 Decode(g_elim_code,'IE','Intercompany ', 'Intracompany ')
2054 ||girt.rule_name
2055 ||' executed for '||g_elim_entity_name,
2056 'AUTOMATIC', g_consolidation_run_name,
2057 DECODE(g_elim_code,'IE','INTERCOMPANY','IA','INTRACOMPANY'),
2058 'SINGLE_RUN_FOR_PERIOD',
2059 'X',
2060 SYSDATE, g_fnd_user_id,
2061 SYSDATE, g_fnd_user_id,
2062 g_fnd_login_id, 'N'
2063 FROM GCS_INTERCO_HDR_GT gehg,
2064 GCS_INTERCO_RULES_TL girt
2065 WHERE gehg.entry_id = entries.entry_id
2066 AND gehg.rule_id = girt.rule_id
2067 AND girt.language = USERENV('LANG');
2068
2069
2070
2071 g_no_rows := SQL%ROWCOUNT;
2072
2073 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2074 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
2075 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
2076 FND_MESSAGE.Set_Token('TABLE','GCS_ENTRY_HEADERS');
2077
2078
2079 FND_LOG.String (fnd_log.level_procedure,
2080 g_pkg_name || '.' || l_api_name,
2081 'SHRD0117: '||FND_MESSAGE.get);
2082 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0117: '||FND_MESSAGE.get);
2083 END IF;
2084
2085
2086 END;
2087
2088 End Loop;
2089
2090 --END Loop;
2091
2092
2093
2094 -- Insert elimination entry headers into GCS_ENTRY_HEADERS for same
2095 -- currency.
2096
2097 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2098 fnd_log.STRING (fnd_log.level_procedure,
2099 g_pkg_name || '.' || l_api_name,
2100 'Intercompany- Inserting same currency '
2101 || ' entry headers into GCS_ENTRY_HEADERS'
2102 );
2103 END IF;
2104
2105 -- Suspense exceeded flag population.
2106 -- Now insert the same currency entries.
2107
2108 -- Fixed bug#3691665
2109
2110 INSERT INTO gcs_entry_headers
2111 (entry_id, entry_name, hierarchy_id, disabled_flag,
2112 entity_id, currency_code, balance_type_code,
2113 start_cal_period_id, end_cal_period_id,
2114 description, entry_type_code,
2115 processed_run_name, category_code,
2116 process_code, suspense_exceeded_flag,
2117 creation_date, created_by, last_update_date,
2118 last_updated_by, last_update_login, PERIOD_INIT_ENTRY_FLAG)
2119 SELECT gehg.ENTRY_ID,
2120 gehg.ENTRY_ID,
2121 p_hierarchy_id, 'N',
2122 g_elim_entity_id, gehg.currency_code,
2123 p_balance_type, p_cal_period_id,
2124 p_cal_period_id,
2125 Decode(g_elim_code,'IE','Intercompany ', 'Intracompany ')
2126 ||MAX(girt.rule_name)
2127 ||' executed for '||g_elim_entity_name,
2128 'AUTOMATIC', g_consolidation_run_name,
2129 DECODE(g_elim_code,'IE','INTERCOMPANY','IA','INTRACOMPANY'),
2130 'SINGLE_RUN_FOR_PERIOD',
2131 DECODE (GREATEST(ABS(SUM(NVL(giet.ytd_debit_balance_e,0))-
2132 SUM(NVL(giet.ytd_credit_balance_e,0))),
2133 MAX(gehg.threshold_amount)),
2134 MAX(gehg.threshold_amount), 'N', 'Y'),
2135 MAX(SYSDATE), MAX(g_fnd_user_id),
2136 MAX(SYSDATE), MAX(g_fnd_user_id),
2137 MAX(g_fnd_login_id), 'N'
2138 FROM GCS_INTERCO_HDR_GT gehg,
2139 GCS_INTERCO_RULES_TL girt,
2140 GCS_ENTRY_LINES giet
2141 WHERE (gehg.currency_code = P_currency_code
2142 AND gehg.threshold_currency = P_currency_code)
2143 AND gehg.rule_id = girt.rule_id
2144 AND girt.language = USERENV('LANG')
2145 AND gehg.entry_id = giet.entry_id(+)
2146 AND giet.line_item_id(+) = gehg.sus_line_item_id
2147 AND giet.description(+) = 'SUSPENSE_LINE'
2148 GROUP BY gehg.ENTRY_ID, gehg.currency_code;
2149
2150 g_no_rows := SQL%ROWCOUNT;
2151
2152 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2153 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
2154 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
2155 FND_MESSAGE.Set_Token('TABLE','GCS_ENTRY_HEADERS');
2156
2157
2158 FND_LOG.String (fnd_log.level_procedure,
2159 g_pkg_name || '.' || l_api_name,
2160 'SHRD0117: '||FND_MESSAGE.get);
2161 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0117: '||FND_MESSAGE.get);
2162 END IF;
2163
2164
2165 -- Insert elimination entry headers into GCS_ENTRY_HEADERS.
2166
2167 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2168 fnd_log.STRING (fnd_log.level_procedure,
2169 g_pkg_name || '.' || l_api_name,
2170 'Intercompany- Inserting stat currency entry '
2171 || ' headers into GCS_ENTRY_HEADERS'
2172 );
2173 END IF;
2174
2175 -- Suspense exceeded flag population.
2176 -- Now insert the stat currency entries.
2177 -- For stat currency this flag is always 'N'
2178
2179 INSERT INTO gcs_entry_headers
2180 (entry_id, entry_name, hierarchy_id, disabled_flag,
2181 entity_id, currency_code, balance_type_code,
2182 start_cal_period_id, end_cal_period_id,
2183 description, entry_type_code,
2184 processed_run_name, category_code,
2185 process_code, suspense_exceeded_flag,
2186 creation_date, created_by, last_update_date,
2187 last_updated_by, last_update_login, PERIOD_INIT_ENTRY_FLAG)
2188 SELECT gehg.ENTRY_ID,
2189 gehg.ENTRY_ID,
2190 p_hierarchy_id, 'N',
2191 g_elim_entity_id, gehg.currency_code,
2192 p_balance_type, p_cal_period_id,
2193 p_cal_period_id,
2194 Decode(g_elim_code,'IE','Intercompany ', 'Intracompany ')
2195 ||girt.rule_name
2196 ||' executed for '||g_elim_entity_name,
2197 'AUTOMATIC', g_consolidation_run_name,
2198 DECODE(g_elim_code,'IE','INTERCOMPANY','IA','INTRACOMPANY'),
2199 'SINGLE_RUN_FOR_PERIOD',
2200 'N',
2201 SYSDATE, g_fnd_user_id,
2202 SYSDATE, g_fnd_user_id,
2203 g_fnd_login_id, 'N'
2204 FROM GCS_INTERCO_HDR_GT gehg,
2205 GCS_ENTRY_LINES gel,
2206 GCS_INTERCO_RULES_TL girt
2207 WHERE gehg.entry_id = gel.entry_id
2208 AND gehg.currency_code = 'STAT'
2209 AND gehg.rule_id = girt.rule_id
2210 AND girt.language = USERENV('LANG')
2211 GROUP BY gehg.entry_id, girt.rule_name, gehg.currency_code;
2212
2213 g_no_rows := SQL%ROWCOUNT;
2214
2215 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2216 FND_MESSAGE.Set_Name('SQLGL','SHRD0117');
2217 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
2218 FND_MESSAGE.Set_Token('TABLE','GCS_ENTRY_HEADERS');
2219
2220
2221 FND_LOG.String (fnd_log.level_procedure,
2222 g_pkg_name || '.' || l_api_name,
2223 'SHRD0117: '||FND_MESSAGE.get);
2224 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0117: '||FND_MESSAGE.get);
2225 END IF;
2226
2227 -- Fix Bug #3682104
2228
2229 g_no_rows := 0;
2230
2231 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2232 fnd_log.STRING (fnd_log.level_procedure,
2233 g_pkg_name || '.' || l_api_name,
2234 'Intercompany- Deleting entry headers '
2235 || ' from gcs_entry_headers'
2236 || ' where there are no entry lines '
2237
2238 );
2239 END IF;
2240
2241 DELETE FROM gcs_entry_headers
2242 WHERE entry_id IN
2243 (SELECT gihg.entry_id from gcs_interco_hdr_gt gihg
2244 WHERE NOT EXISTS
2245 (SELECT entry_id from gcs_entry_lines geh
2246 WHERE geh.entry_id = gihg.entry_id));
2247
2248
2249 g_no_rows := NVL(SQL%ROWCOUNT,0);
2250
2251 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2252 FND_MESSAGE.Set_Name('SQLGL','SHRD0119');
2253 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
2254 FND_MESSAGE.Set_Token('TABLE','GCS_ENTRY_HEADERS');
2255
2256
2257 FND_LOG.String (fnd_log.level_procedure,
2258 g_pkg_name || '.' || l_api_name,
2259 'SHRD0117: '||FND_MESSAGE.get);
2260 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0119: '||FND_MESSAGE.get);
2261 END IF;
2262
2263
2264
2265 -- Process Run details in gcs_cons_eng_run_dtls for the entered headers.
2266
2267 g_no_rows := 0;
2268
2269 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2270 fnd_log.STRING (fnd_log.level_procedure,
2271 g_pkg_name || '.' || l_api_name,
2272 'Intercompany- Deleting run details'
2273 || ' from gcs_cons_eng_run_dtls'
2274 || ' which are not ended up into entry headers table'
2275 );
2276 END IF;
2277
2278 DELETE FROM gcs_cons_eng_run_dtls
2279 WHERE entry_id IN
2280 (SELECT gihg.entry_id from gcs_interco_hdr_gt gihg
2281 WHERE gihg.currency_code <> 'STAT'
2282 AND NOT EXISTS
2283 (SELECT entry_id from gcs_entry_headers geh
2284 WHERE geh.entry_id = gihg.entry_id));
2285
2286
2287 g_no_rows := NVL(SQL%ROWCOUNT,0);
2288
2289 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2290 FND_MESSAGE.Set_Name('SQLGL','SHRD0119');
2291 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
2292 FND_MESSAGE.Set_Token('TABLE','GCS_CONS_ENG_RUN_DTLS');
2293
2294
2295 FND_LOG.String (fnd_log.level_procedure,
2296 g_pkg_name || '.' || l_api_name,
2297 'SHRD0117: '||FND_MESSAGE.get);
2298 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0119: '||FND_MESSAGE.get);
2299 END IF;
2300
2301
2302 g_no_rows := 0;
2303
2304 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2305 fnd_log.STRING (fnd_log.level_procedure,
2306 g_pkg_name || '.' || l_api_name,
2307 'Intercompany- Merge run details'
2308 || ' for STAT rows in gcs_cons_eng_run_dtls'
2309 );
2310 END IF;
2311
2312
2313 MERGE INTO gcs_cons_eng_run_dtls gcer
2314 USING (SELECT
2315 DECODE(g_elim_code, 'IE',g_entity_id,'IA', g_cons_entity_id)
2316 cons_entity_id,
2317 gehg.rule_id rule_id,
2318 DECODE(g_elim_code, 'IE', 'INTERCOMPANY', 'IA', 'INTRACOMPANY')
2319 category_code,
2320 DECODE(geh.suspense_exceeded_flag, 'X','WARNING','Y','WARNING',
2321 ' N','NO_ERROR', 'COMPLETED') req_err_code,
2322 DECODE(geh.suspense_exceeded_flag, 'X','WARNING','Y','WARNING',
2323 ' N','NO_ERROR', 'COMPLETED') bp_req_err_code,
2324 gehg.source_entity_id src_entity_id,
2325 gehg.target_entity_id target_entity_id,
2326 gehg.entry_id entry_id
2327 FROM GCS_INTERCO_HDR_GT gehg,
2328 GCS_ENTRY_HEADERS geh
2329 WHERE gehg.entry_id = geh.entry_id
2330 AND gehg.currency_code = 'STAT') stat_result
2331 ON (stat_result.src_entity_id = gcer.child_entity_id
2332 AND stat_result.target_entity_id =
2333 gcer.contra_child_entity_id
2334 AND stat_result.rule_id = gcer.rule_id
2335 AND gcer.run_name = g_consolidation_run_name)
2336 WHEN MATCHED THEN UPDATE SET
2337 gcer.stat_entry_id = stat_result.entry_id,
2338 gcer.request_error_code =
2339 NVL(stat_result.req_err_code,gcer.request_error_code),
2340 gcer.bp_request_error_code =
2341 NVL(stat_result.bp_req_err_code,gcer.bp_request_error_code),
2342 last_update_date = SYSDATE,
2343 last_updated_by = g_fnd_user_id
2344 WHEN NOT MATCHED THEN INSERT (gcer.run_detail_id, gcer.run_name,
2345 gcer.Consolidation_entity_id,
2346 gcer.child_entity_id, gcer.contra_child_entity_id ,
2347 gcer.stat_entry_id, gcer.rule_id, gcer.request_error_code,
2348 gcer.bp_request_error_code, gcer.category_code,
2349 gcer.creation_date, gcer.created_by, gcer.last_update_date,
2350 gcer.last_updated_by, gcer.last_update_login)
2351 VALUES(gcs_cons_eng_run_dtls_s.nextval,
2352 g_consolidation_run_name,
2353 stat_result.cons_entity_id, stat_result.src_entity_id,
2354 stat_result.target_entity_id, stat_result.entry_id,
2355 stat_result.rule_id, stat_result.req_err_code,
2356 stat_result.bp_req_err_code,
2357 stat_result.category_code,
2358 SYSDATE, g_fnd_user_id,
2359 SYSDATE, g_fnd_user_id,
2360 g_fnd_login_id);
2361
2362 g_no_rows := NVL(SQL%ROWCOUNT,0);
2363
2364 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2365 FND_MESSAGE.Set_Name('SQLGL','SHRD0118');
2366 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
2367 FND_MESSAGE.Set_Token('TABLE','GCS_CONS_ENG_RUN_DTLS');
2368
2369
2370 FND_LOG.String (fnd_log.level_procedure,
2371 g_pkg_name || '.' || l_api_name,
2372 'SHRD0117: '||FND_MESSAGE.get);
2373 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0118: '||FND_MESSAGE.get);
2374 END IF;
2375
2376 -----##########-----
2377
2378 g_no_rows := 0;
2379
2380 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2381 fnd_log.STRING (fnd_log.level_procedure,
2382 g_pkg_name || '.' || l_api_name,
2383 'Intercompany- Updating run details'
2384 || ' for non STAT rows in gcs_cons_eng_run_dtls'
2385 );
2386 END IF;
2387
2388
2389
2390 UPDATE gcs_cons_eng_run_dtls gcer
2391 SET (request_error_code,
2392 bp_request_error_code, last_update_date,
2393 last_updated_by) =
2394 (SELECT
2395 --DECODE(gehg.currency_code, 'STAT', gehg.entry_id, NULL),
2396 DECODE(gcer.stat_entry_id, NULL,
2397 DECODE(geh.suspense_exceeded_flag,
2398 'X','WARNING','Y','WARNING',
2399 ' N','NO_ERROR', 'COMPLETED'),
2400 gcer.request_error_code),
2401 DECODE(gcer.stat_entry_id, NULL,
2402 DECODE(geh.suspense_exceeded_flag, 'X','WARNING','Y','WARNING',
2403 ' N','NO_ERROR', 'COMPLETED'),
2404 gcer.bp_request_error_code),
2405 SYSDATE,
2406 g_fnd_user_id
2407 FROM GCS_INTERCO_HDR_GT gehg,
2408 GCS_ENTRY_HEADERS geh
2409 WHERE gehg.entry_id = geh.entry_id
2410 AND gehg.entry_id = gcer.entry_id)
2411 WHERE gcer.entry_id IN (SELECT entry_id from gcs_interco_hdr_gt
2412 WHERE currency_code <> 'STAT');
2413
2414
2415 g_no_rows := NVL(SQL%ROWCOUNT,0);
2416
2417 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2418 FND_MESSAGE.Set_Name('SQLGL','SHRD0118');
2419 FND_MESSAGE.Set_Token('NUM',TO_CHAR(g_no_rows));
2420 FND_MESSAGE.Set_Token('TABLE','GCS_CONS_ENG_RUN_DTLS');
2421
2422
2423 FND_LOG.String (fnd_log.level_procedure,
2424 g_pkg_name || '.' || l_api_name,
2425 'SHRD0117: '||FND_MESSAGE.get);
2426 --FND_FILE.Put_Line(FND_FILE.Log,'SHRD0118: '||FND_MESSAGE.get);
2427 END IF;
2428
2429
2430 -- Consolidation engine requires x_errbuf being returnrd as 'WARNING'
2431 -- if suspense exceeded or could not found a conversion rate for a set
2432 -- of currencies.
2433
2434 BEGIN
2435
2436 SELECT 1 INTO l_warning
2437 FROM DUAL
2438 WHERE EXISTS (SELECT 1 FROM GCS_ENTRY_HEADERS
2439 WHERE suspense_exceeded_flag = 'Y'
2440 AND entry_id IN (SELECT entry_id from gcs_interco_hdr_gt));
2441
2442 IF (l_warning =1) THEN
2443 g_sus_exceed_no_rate := TRUE;
2444 END IF;
2445
2446 EXCEPTION
2447 WHEN NO_DATA_FOUND THEN
2448 NULL;
2449 WHEN OTHERS THEN
2450 NULL;
2451
2452 END;
2453
2454 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2455
2456 fnd_log.STRING (fnd_log.level_procedure,
2457 g_pkg_name || '.' || l_api_name,
2458 gcs_utility_pkg.g_module_success
2459 || ' '
2460 || l_api_name
2461 || '() '
2462 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
2463 );
2464 END IF;
2465
2466
2467 RETURN TRUE;
2468 EXCEPTION
2469 WHEN OTHERS THEN
2470
2471 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2472 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
2473 g_pkg_name || '.' || l_api_name,
2474 SUBSTR(SQLERRM, 1, 255));
2475 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
2476 g_pkg_name || '.' || l_api_name,
2477 GCS_UTILITY_PKG.g_module_failure || l_api_name ||
2478 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
2479 END IF;
2480
2481 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure
2482 -- ||l_api_name || to_char(sysdate
2483 -- , ' DD-MON-YYYY HH:MI:SS'));
2484 RETURN FALSE;
2485
2486 END INSR_ELIMINATION_HDRS;
2487
2488
2489 END GCS_INTERCO_PROCESSING_PKG;