[Home] [Help]
PACKAGE BODY: APPS.FII_CC_MAINTAIN_PKG
Source
1 PACKAGE BODY FII_CC_MAINTAIN_PKG AS
2 /* $Header: FIICCCMB.pls 120.2 2006/09/26 12:39:03 arcdixit ship $ */
3
4
5 G_MASTER_VALUE_SET_ID NUMBER(15) := NULL;
6 G_TOP_NODE_ID NUMBER(15) := NULL;
7 G_TOP_NODE_VALUE VARCHAR2(240) := NULL;
8 G_UNASSIGNED_CC_ID NUMBER(15);
9 G_FII_INT_VALUE_SET_ID NUMBER(15);
10 G_PHASE VARCHAR2(120);
11 G_INCL_LEAF_NODES VARCHAR2(1);
12 G_DBI_ENABLED_FLAG VARCHAR2(1);
13 g_schema_name VARCHAR2(120) := 'FII';
14 g_retcode VARCHAR2(20) := NULL;
15 g_debug_mode VARCHAR2(1)
16 := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
17
18 g_index NUMBER(10) :=0;
19
20 -- *****************************************************************
21 -- Check if a value set is table validated
22
23 FUNCTION Is_Table_Validated (X_Vs_Id NUMBER) RETURN BOOLEAN IS
24 l_tab_name VARCHAR2(240) := NULL;
25
26 BEGIN
27
28 --FII_MESSAGE.Func_Ent (func_name => 'Is_Table_Validated');
29
30 -- Execute statement to determine if the value set is table validated
31 BEGIN
32
33 SELECT fvt.application_table_name INTO l_tab_name
34 FROM fnd_flex_validation_tables fvt,
35 fnd_flex_value_sets fvs
36 WHERE fvs.flex_value_set_id = X_vs_id
37 AND fvs.validation_type = 'F'
38 AND fvt.flex_value_set_id = fvs.flex_value_set_id;
39 EXCEPTION
40 WHEN NO_DATA_FOUND THEN
41 return FALSE;
42 END;
43
44 --FII_MESSAGE.Func_Succ (func_name => 'Is_Table_Validated');
45
46 RETURN TRUE;
47
48 EXCEPTION
49 WHEN OTHERS THEN
50 RETURN FALSE;
51
52 END Is_Table_Validated;
53
54 -- *******************************************************************
55 -- Function Get_Value_Set_Name
56
57 Function Get_Value_Set_Name (p_vs_id NUMBER) RETURN VARCHAR2 IS
58 l_vs_name varchar2(120);
59
60 Begin
61
62 if FIIDIM_Debug then
63 FII_MESSAGE.Func_Ent (func_name => 'Get_Value_Set_Name');
64 end if;
65
66 select flex_value_set_name into l_vs_name
67 from fnd_flex_value_sets
68 where flex_value_set_id = p_vs_id;
69
70 if FIIDIM_Debug then
71 FII_MESSAGE.Func_Succ (func_name => 'Get_Value_Set_Name');
72 end if;
73
74 return l_vs_name;
75
76 Exception
77 when others then
78 FII_UTIL.Write_Log (
79 'Unexpected error when calling Get_Value_Set_Name...');
80 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,80));
81 FII_UTIL.Write_Log ('Value Set ID: ' || p_vs_id);
82 RAISE;
83
84 End Get_Value_Set_Name;
85
86 -- *******************************************************************
87 -- Function Get_Flex_Value
88
89 Function Get_Flex_Value (p_flex_value_id NUMBER) RETURN VARCHAR2 IS
90 l_flex_value varchar2(120);
91
92 Begin
93
94 if FIIDIM_Debug then
95 FII_MESSAGE.Func_Ent (func_name => 'Get_Flex_Value');
96 end if;
97
98 select flex_value into l_flex_value
99 from fnd_flex_values
100 where flex_value_id = p_flex_value_id;
101
102 if FIIDIM_Debug then
103 FII_MESSAGE.Func_Succ (func_name => 'Get_Flex_Value');
104 end if;
105
106 return l_flex_value;
107
108 Exception
109 when others then
110 FII_UTIL.Write_Log (
111 'Unexpected error when calling Get_Flex_Value...');
112 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,80));
113 FII_UTIL.Write_Log ('Value ID: ' || p_flex_value_id);
114 RAISE;
115
116 End Get_Flex_Value;
117
118 -- *******************************************************************
119 -- Initialize (get the master value set and the top node)
120
121 PROCEDURE Initialize IS
122 l_dir VARCHAR2(160);
123 l_check NUMBER;
124 l_bool_ret BOOLEAN;
125 l_ret_code number;
126
127 BEGIN
128
129 g_phase := 'Do set up for log file';
130 ----------------------------------------------
131 -- Do set up for log file
132 ----------------------------------------------
133
134 l_dir := fnd_profile.value('BIS_DEBUG_LOG_DIRECTORY');
135 ------------------------------------------------------
136 -- Set default directory in case if the profile option
137 -- BIS_DEBUG_LOG_DIRECTORY is not set up
138 ------------------------------------------------------
139 if l_dir is NULL then
140 l_dir := FII_UTIL.get_utl_file_dir;
141 end if;
142
143 ----------------------------------------------------------------
144 -- FII_UTIL.initialize will get profile options FII_DEBUG_MODE
145 -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
146 -- the log files and output files are written to
147 ----------------------------------------------------------------
148 FII_UTIL.initialize('FII_CC_MAINTAIN_PKG.log',
149 'FII_CC_MAINTAIN_PKG.out',l_dir,'FII_CC_MAINTAIN_PKG' );
150
151 -- --------------------------------------------------------
152 -- Check source ledger setup for DBI
153 -- --------------------------------------------------------
154 l_check := FII_EXCEPTION_CHECK_PKG.check_slg_setup;
155
156 if l_check <> 0 then
157 FII_UTIL.write_log('>>> No source ledger setup for DBI');
158 RAISE CCDIM_fatal_err;
159 end if;
160
161 -- --------------------------------------------------------
162 -- Detect unmapped local value set
163 -- --------------------------------------------------------
164 g_phase := 'Detect unmapped local value set';
165
166 l_check := FII_EXCEPTION_CHECK_PKG.detect_unmapped_local_vs('HRI_CL_ORGCC');
167
168 if l_check > 0 then
169 l_bool_ret := FND_CONCURRENT.Set_Completion_Status(
170 status => 'WARNING',
171 message => 'Detected unmapped local value set.'
172 );
173 elsif l_check < 0 then
174 RAISE CCDIM_fatal_err;
175 end if;
176 -- Obtain FII schema name
177 g_schema_name := FII_UTIL.get_schema_name ('FII');
178
179 -- Obtain user ID, login ID and initialize package variables
180 FII_USER_ID := FND_GLOBAL.USER_ID;
181 FII_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
182
183 -- If any of the above values is not set, error out
184 IF (FII_User_Id is NULL OR FII_Login_Id is NULL) THEN
185 FII_UTIL.Write_Log ('>>> Failed Intialization');
186 RAISE CCDIM_fatal_err;
187 END IF;
188
189 -- Determine if process will be run in debug mode
190 IF (NVL(G_Debug_Mode, 'N') <> 'N') THEN
191 FIIDIM_Debug := TRUE;
192 FII_UTIL.Write_Log (' Debug On');
193 ELSE
194 FIIDIM_Debug := FALSE;
195 FII_UTIL.Write_Log (' Debug Off');
196 END IF;
197
198 -- Turn trace on if process is run in debug mode
199 IF (FIIDIM_Debug) THEN
200 -- Program running in debug mode, turning trace on
201 EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
202 FII_UTIL.Write_Log ('Initialize: Set Trace On');
203 END IF;
204
205 IF (FIIDIM_Debug) THEN
206 FII_UTIL.Write_Log ('Initialize: Now start processing '|| 'Cost Center dimension');
207 End If;
208
209 -- --------------------------------------------------------
210 -- Find the unassigned cost center ID
211 -- --------------------------------------------------------
212
213 g_phase := 'Find the shipped FII value set id and the unassigned value id';
214
215 FII_GL_EXTRACTION_UTIL.get_unassigned_id(G_UNASSIGNED_CC_ID, G_FII_INT_VALUE_SET_ID, l_ret_code);
216 IF(l_ret_code = -1) THEN
217 RAISE CCDIM_fatal_err;
218 END IF;
219
220 -- --------------------------------------------------------
221 -- Get the master value set and top node for cost center
222 -- --------------------------------------------------------
223
224 g_phase := 'Get the master value set and top node for cost center';
225 Begin
226
227 SELECT MASTER_VALUE_SET_ID, DBI_HIER_TOP_NODE,
228 DBI_HIER_TOP_NODE_ID,
229 DBI_ENABLED_FLAG
230 INTO G_MASTER_VALUE_SET_ID, G_TOP_NODE_VALUE,
231 G_TOP_NODE_ID, G_DBI_ENABLED_FLAG
232 FROM FII_FINANCIAL_DIMENSIONS
233 WHERE DIMENSION_SHORT_NAME = 'HRI_CL_ORGCC';
234
235 --If the COST CENTER dimension is not enabled, raise an exception.
236 --Note that we will insert 'UNASSIGNED' to the dimension
237 IF NVL(G_DBI_ENABLED_FLAG, 'N') <> 'Y' then
238 RAISE CCDIM_NOT_ENABLED;
239 END IF;
240
241 --If the master value is not set up, raise an exception
242 IF G_MASTER_VALUE_SET_ID is NULL THEN
243 FII_MESSAGE.write_log (msg_name => 'FII_MSTR_VSET_NOT_FOUND',
244 token_num => 0);
245 FII_MESSAGE.write_output (msg_name => 'FII_MSTR_VSET_NOT_FOUND',
246 token_num => 0);
247 RAISE CCDIM_fatal_err;
248 --If the top node is not set up, raise an exception
249 ELSIF G_TOP_NODE_ID is NULL OR G_TOP_NODE_VALUE is NULL THEN
250 FII_MESSAGE.write_log (msg_name => 'FII_MSTR_TNODE_NOT_FOUND',
251 token_num => 0);
252 FII_MESSAGE.write_output (msg_name => 'FII_MSTR_TNODE_NOT_FOUND',
253 token_num => 0);
254 RAISE CCDIM_fatal_err;
255 END IF;
256
257 Exception
258 When NO_DATA_FOUND Then
259 FII_MESSAGE.write_log (msg_name => 'FII_MSTR_VSET_NOT_FOUND',
260 token_num => 0);
261 FII_MESSAGE.write_output (msg_name => 'FII_MSTR_VSET_NOT_FOUND',
262 token_num => 0);
263 RAISE CCDIM_fatal_err;
264 When TOO_MANY_ROWS Then
265 FII_UTIL.Write_Log ('More than one master value set found for COST CENTER Dimension');
266 RAISE CCDIM_fatal_err;
267 When CCDIM_NOT_ENABLED then
268 raise;
269 When CCDIM_fatal_err then
270 raise;
271 When OTHERS Then
272 FII_UTIL.Write_Log ('Unexpected error when getting master value set for COST CENTER Dimension');
273 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
274 RAISE CCDIM_fatal_err;
275 End;
276
277 IF (FIIDIM_Debug) THEN
278 FII_UTIL.Write_Log ('COST CENTER Master Value Set ID: '|| G_MASTER_VALUE_SET_ID);
279 FII_UTIL.Write_Log ('COST CENTER Master Value Set: '||
280 Get_Value_Set_Name (G_MASTER_VALUE_SET_ID));
281 FII_UTIL.Write_Log (' and COST CENTER Top Node: '|| G_TOP_NODE_VALUE);
282 END IF;
283
284
285 -- Check if the master value set is a table validated set.
286 g_phase := 'Check if the master value set is a table validated set';
287
288 If Is_Table_Validated (G_MASTER_VALUE_SET_ID) Then
289 FII_MESSAGE.write_log (msg_name => 'FII_TBL_VALIDATED_VSET',
290 token_num => 1,
291 t1 => 'VS_NAME',
292 v1 => Get_Value_Set_Name (G_MASTER_VALUE_SET_ID));
293 FII_MESSAGE.write_output (msg_name => 'FII_TBL_VALIDATED_VSET',
294 token_num => 1,
295 t1 => 'VS_NAME',
296 v1 => Get_Value_Set_Name (G_MASTER_VALUE_SET_ID));
297 RAISE CCDIM_fatal_err;
298 End If;
299
300 --If the Cost Center dimension is not enabled, raise an exception
301 IF G_DBI_ENABLED_FLAG <> 'Y' then
302 RAISE CCDIM_NOT_ENABLED;
303 END IF;
304
305 Exception
306
307 When CCDIM_NOT_ENABLED then
308 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
309 --Let the main program handle this
310 raise;
311
312 When CCDIM_fatal_err then
313 FII_UTIL.Write_Log ('FII_CC_MAINTAIN_PKG.Initialize : '|| 'User defined error');
314 FND_CONCURRENT.Af_Rollback;
315 FII_MESSAGE.Func_Fail(func_name => 'FII_CC_MAINTAIN_PKG.Initialize');
316 raise;
317
318 When others then
319 FII_UTIL.Write_Log ('Unexpected error when calling Initialize...');
320 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
321 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
322 RAISE;
323
324 END Initialize;
325
326
327 -- *******************************************************************
328 -- Populate the table FII_DIM_NORM_HIER_GT
329
330 PROCEDURE Get_NORM_HIERARCHY_TMP IS
331
332 Cursor all_local_value_sets IS
333 select distinct child_flex_value_set_id
334 from FII_DIM_NORM_HIERARCHY
335 where parent_flex_value_set_id = G_MASTER_VALUE_SET_ID
336 and parent_flex_value_set_id <> child_flex_value_set_id
337 and child_flex_value_set_id IN
338 (select map.flex_value_set_id1
339 from fii_dim_mapping_rules map,
343 and map.chart_of_accounts_id = sts.chart_of_accounts_id
340 fii_slg_assignments sts,
341 fii_source_ledger_groups slg
342 where map.dimension_short_name = 'HRI_CL_ORGCC'
344 and sts.source_ledger_group_id = slg.source_ledger_group_id
345 and slg.usage_code = 'DBI');
346
347 l_vset_id NUMBER(15);
348
349 BEGIN
350
351 IF (FIIDIM_Debug) THEN
352 FII_MESSAGE.Func_Ent ('FII_CC_MAINTAIN_PKG.Get_NORM_HIERARCHY_TMP');
353 END IF;
354 --First, insert records for the master value set
355 g_phase := 'insert records for the master value set';
356
357 Insert into FII_DIM_NORM_HIER_GT (
358 PARENT_FLEX_VALUE_SET_ID,
359 PARENT_FLEX_VALUE,
360 RANGE_ATTRIBUTE,
361 CHILD_FLEX_VALUE_SET_ID,
362 CHILD_FLEX_VALUE_LOW,
363 CHILD_FLEX_VALUE_HIGH)
364 Select
365 FLEX_VALUE_SET_ID,
366 PARENT_FLEX_VALUE,
367 RANGE_ATTRIBUTE,
368 FLEX_VALUE_SET_ID,
369 CHILD_FLEX_VALUE_LOW,
370 CHILD_FLEX_VALUE_HIGH
371 From FND_FLEX_VALUE_NORM_HIERARCHY
372 Where flex_value_set_id = G_MASTER_VALUE_SET_ID;
373
374
375 --Copy table FII_DIM_NORM_HIERARCHY for parent-child valuesets relation
376 g_phase := 'Copy FII_DIM_NORM_HIERARCHY for parent-child valuesets relation';
377 Insert into FII_DIM_NORM_HIER_GT (
378 PARENT_FLEX_VALUE_SET_ID,
379 PARENT_FLEX_VALUE,
380 RANGE_ATTRIBUTE,
381 CHILD_FLEX_VALUE_SET_ID,
382 CHILD_FLEX_VALUE_LOW,
383 CHILD_FLEX_VALUE_HIGH)
384 Select
385 PARENT_FLEX_VALUE_SET_ID,
386 PARENT_FLEX_VALUE,
387 RANGE_ATTRIBUTE,
388 CHILD_FLEX_VALUE_SET_ID,
389 CHILD_FLEX_VALUE_LOW,
390 CHILD_FLEX_VALUE_HIGH
391 From FII_DIM_NORM_HIERARCHY
392 Where PARENT_FLEX_VALUE_SET_ID <> CHILD_FLEX_VALUE_SET_ID
393 AND PARENT_FLEX_VALUE_SET_ID = G_MASTER_VALUE_SET_ID
394 And CHILD_FLEX_VALUE_SET_ID IN
395 (select map.flex_value_set_id1
396 from fii_dim_mapping_rules map,
397 fii_slg_assignments sts,
398 fii_source_ledger_groups slg
399 where map.dimension_short_name = 'HRI_CL_ORGCC'
400 and map.chart_of_accounts_id = sts.chart_of_accounts_id
401 and sts.source_ledger_group_id = slg.source_ledger_group_id
402 and slg.usage_code = 'DBI');
403
404
405 --Insert records for all local (child) value sets
406 g_phase := 'Insert records for all local (child) value sets';
407
408 FOR vset_rec IN all_local_value_sets LOOP
409
410 l_vset_id := vset_rec.child_flex_value_set_id;
411
412 -- Check if the (child) value set is a table validated set.
413 If Is_Table_Validated (l_vset_id) Then
414 FII_MESSAGE.write_log (msg_name => 'FII_TBL_VALIDATED_VSET',
415 token_num => 1,
416 t1 => 'VS_NAME',
417 v1 => Get_Value_Set_Name (l_vset_id));
418 FII_MESSAGE.write_output (msg_name => 'FII_TBL_VALIDATED_VSET',
419 token_num => 1,
420 t1 => 'VS_NAME',
421 v1 => Get_Value_Set_Name (l_vset_id));
422 RAISE CCDIM_fatal_err;
423 End If;
424
425 Insert into FII_DIM_NORM_HIER_GT (
426 PARENT_FLEX_VALUE_SET_ID,
427 PARENT_FLEX_VALUE,
428 RANGE_ATTRIBUTE,
429 CHILD_FLEX_VALUE_SET_ID,
430 CHILD_FLEX_VALUE_LOW,
431 CHILD_FLEX_VALUE_HIGH)
432 Select
433 FLEX_VALUE_SET_ID,
434 PARENT_FLEX_VALUE,
435 RANGE_ATTRIBUTE,
436 FLEX_VALUE_SET_ID,
437 CHILD_FLEX_VALUE_LOW,
438 CHILD_FLEX_VALUE_HIGH
439 From FND_FLEX_VALUE_NORM_HIERARCHY
440 Where flex_value_set_id = l_vset_id;
441
442 END LOOP;
443
444 --Call FND_STATS to collect statistics after populating the table
445 g_phase := 'gather_table_stats for FII_DIM_NORM_HIER_GT';
446
447 FND_STATS.gather_table_stats
448 (ownname => g_schema_name,
449 tabname => 'FII_DIM_NORM_HIER_GT');
450
451 IF (FIIDIM_Debug) THEN
452 FII_MESSAGE.Func_Succ ('FII_CC_MAINTAIN_PKG.Get_NORM_HIERARCHY_TMP');
453 END IF;
454 Exception
455
456 When CCDIM_fatal_err then
457 FII_UTIL.Write_Log ('FII_CC_MAINTAIN_PKG.Get_NORM_HIERARCHY_TMP: '||
458
459
460 'User defined error');
461 FND_CONCURRENT.Af_Rollback;
462 FII_MESSAGE.Func_Fail(func_name => 'FII_CC_MAINTAIN_PKG.Get_NORM_HIERARCHY_TMP');
463 raise;
464
465 When others then
466 FII_UTIL.Write_Log ('Unexpected error when calling Get_NORM_HIERARCHY_TMP.');
467 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
468 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
469 RAISE;
470
471 END Get_NORM_HIERARCHY_TMP;
472
473
474 -- **************************************************************************
478 PROCEDURE Detect_Diamond_Shape IS
475 -- This procedure will check for child value multiple assignments
476 -- to different parents within FII_CC_HIER_GT (the TMP hierarchy table)
477
479
480 --The first cursor is to find all flex_value_id which has multiple parents;
481 --we look at records such as (P1,A,A) and (P2,A,A)
482 Cursor Dup_Assg_Cur IS
483 SELECT count(parent_cc_id) parents,
484 child_cc_id flex_value_id
485 FROM FII_COST_CTR_HIER_GT
486 WHERE next_level_cc_id = child_cc_id
487 AND parent_level = next_level - 1
488 GROUP BY child_cc_id
489 HAVING count(parent_cc_id) > 1;
490
491 --The second cursor is to print out the list of duplicate parents;
492 --again, we get records such as (P1,A,A),(P2,A,A) to print out P1, P2 for A
493 Cursor Dup_Assg_Parent_Cur (p_child_value_id NUMBER) IS
494 SELECT parent_cc_id,
495 parent_flex_value_set_id,
496 child_cc_id,
497 child_flex_value_set_id
498 FROM FII_COST_CTR_HIER_GT
499 WHERE child_cc_id = p_child_value_id
500 AND next_level_cc_id = child_cc_id
501 AND parent_level = next_level - 1;
502
503 l_count NUMBER(15):=0;
504 l_flex_value VARCHAR2(120);
505 l_vset_name VARCHAR2(240);
506 l_parent_flex_value VARCHAR2(120);
507 l_parent_vset_name VARCHAR2(240);
508
509 BEGIN
510
511 IF (FIIDIM_Debug) THEN
512 FII_MESSAGE.Func_Ent ('FII_CC_MAINTAIN_PKG.Detect_Diamond_Shape');
513 END IF;
514 -- check whole TMP hierarhy table: if there is a diamond
515 -- report and raise an exception
516 g_phase := 'check all value sets for diamonds';
517
518 FOR dup_asg_rec IN Dup_Assg_Cur LOOP
519
520 l_count := l_count + 1;
521 if l_count = 1 then
522
523 FII_MESSAGE.write_log(msg_name => 'FII_DMND_SHAPE_VS_EXIST',
524 token_num => 0);
525 FII_MESSAGE.write_log(msg_name => 'FII_REFER_TO_OUTPUT',
526 token_num => 0);
527
528
529 FII_MESSAGE.write_output (msg_name => 'FII_DMND_SHAPE_VS_EXIST',
530 token_num => 0);
531
532 FII_MESSAGE.write_output (msg_name => 'FII_DMND_SHAPE_VS_TAB',
533 token_num => 0);
534
535 end if;
536
537 FOR dup_asg_par_rec IN Dup_Assg_Parent_Cur (dup_asg_rec.flex_value_id ) LOOP
538
539 l_flex_value := Get_Flex_Value (dup_asg_par_rec.child_cc_id);
540 l_vset_name := Get_Value_Set_Name (dup_asg_par_rec.child_flex_value_set_id);
541 l_parent_flex_value:= Get_Flex_Value (dup_asg_par_rec.parent_cc_id);
542 l_parent_vset_name := Get_Value_Set_Name (dup_asg_par_rec.parent_flex_value_set_id);
543
544 FII_UTIL.Write_Output (
545 l_flex_value || ' '||
546 l_vset_name || ' '||
547 l_parent_flex_value || ' '||
548 l_parent_vset_name);
549
550 END LOOP;
551
552 END LOOP;
553
554 IF (FIIDIM_Debug) THEN
555 FII_MESSAGE.Func_Succ ('FII_CC_MAINTAIN_PKG.Detect_Diamond_Shape');
556 END IF;
557 IF l_count > 0 THEN
558 RAISE CCDIM_MULT_PAR_err;
559 END IF;
560
561 Exception
562
563 When CCDIM_MULT_PAR_err then
564 FII_UTIL.Write_Log ('FII_CC_MAINTAIN_PKG.Detect_Diamond_Shape: '||
565 'diamond shape detected!');
566 RAISE;
567
568 When others then
569 FII_UTIL.Write_Log ('Unexpected error when calling Detect_Diamond_Shape.');
570 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
571 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
572 RAISE;
573
574 END Detect_Diamond_Shape;
575
576 -- *******************************************************************
577
578 PROCEDURE INSERT_IMM_CHILD_NODES
579 (p_vset_id NUMBER, p_root_node VARCHAR2) IS
580
581 CURSOR direct_children_csr (p_parent_vs_id NUMBER, p_parent_node VARCHAR2)
582 IS
583
584 SELECT ffv.flex_value_id, ffv.flex_value, ffv.flex_value_set_id, attribute_sort_order sort_order
585 FROM FII_DIM_NORM_HIER_GT ffvnh,
586 fnd_flex_values ffv
587 WHERE ffvnh.child_flex_value_set_id = ffv.flex_value_set_id
588 AND (ffv.flex_value BETWEEN ffvnh.child_flex_value_low
589 AND ffvnh.child_flex_value_high)
590 AND ((ffvnh.range_attribute = 'P' and ffv.summary_flag = 'Y') OR
591 (ffvnh.range_attribute = 'C' and ffv.summary_flag = 'N'))
592 AND ffvnh.parent_flex_value = p_parent_node
593 AND ffvnh.parent_flex_value_set_id = p_parent_vs_id;
594
595 l_flex_value_id NUMBER(15);
596 l_flex_value_set_id NUMBER(15);
597 l_sort_order NUMBER(15);
598
599 BEGIN
600
601 select flex_value_id, attribute_sort_order into l_flex_value_id, l_sort_order
602 from fnd_flex_values
603 where flex_value_set_id = p_vset_id
607
604 and flex_value = p_root_node;
605
606 l_flex_value_set_id := p_vset_id;
608 /* Inserting parent in a gt table: FII_DIM_HIER_HELP_GT */
609
610 g_index := g_index+1;
611
612 insert into FII_DIM_HIER_HELP_GT
613 ( IDX,
614 FLEX_VALUE_ID,
615 FLEX_VALUE_SET_ID,
616 NEXT_LEVEL_FLEX_VALUE_ID, SORT_ORDER)
617 values
618 ( g_index,
619 l_flex_value_id,
620 l_flex_value_set_id,
621 l_flex_value_id, l_sort_order);
622
623 update FII_DIM_HIER_HELP_GT
624 set NEXT_LEVEL_FLEX_VALUE_ID= l_flex_value_id,
625 SORT_ORDER= l_sort_order
626 where IDX = g_index - 1;
627
628 FOR direct_children_rec IN direct_children_csr(p_vset_id, p_root_node)
629 LOOP
630
631 /* Inserting record with all parents */
632 INSERT INTO fii_COST_CTR_hier_gt (
633 parent_level,
634 parent_cc_id,
635 child_cc_id,
636 next_level,
637 child_level,
638 next_level_is_leaf_flag,
639 is_leaf_flag,
640 parent_flex_value_Set_id,
641 child_flex_value_set_id,
642 next_level_cc_id,
643 next_level_cc_sort_order)
644 SELECT pp.idx,
645 pp.flex_value_id,
646 direct_children_rec.flex_value_id,
647 pp.idx + 1,
648 g_index + 1,
649 'N',
650 'N',
651 pp.flex_value_set_id,
652 direct_children_rec.flex_value_set_id,
653 decode(pp.idx, g_index,
654 direct_children_rec.flex_value_id,
655 pp.next_level_flex_value_id),
656 decode(pp.idx, g_index,
657 direct_children_rec.sort_order,
658 pp.sort_order)
659 FROM FII_DIM_HIER_HELP_GT pp;
660
661 --Recursive Call.
662 INSERT_IMM_CHILD_NODES (direct_children_rec.flex_value_set_id,
663 direct_children_rec.flex_value);
664
665 END LOOP;
666
667 /* Deleting parent from the gt table */
668 delete from FII_DIM_HIER_HELP_GT where idx = g_index;
669
670 g_index := g_index-1;
671
672 FND_CONCURRENT.Af_Commit;
673
674 EXCEPTION
675 WHEN NO_DATA_FOUND Then
676 FII_UTIL.Write_Log ('Insert Immediate child: No Data Found');
677 FII_MESSAGE.Func_Fail
678 (func_name => 'FII_CC_MAINTAIN_PKG.Insert_Imm_Child_Nodes');
679 RAISE;
680
681 WHEN OTHERS Then
682 FII_UTIL.Write_Log (substr(SQLERRM,1,180));
683 FII_MESSAGE.Func_Fail
684 (func_name => 'FII_CC_MAINTAIN_PKG.INSERT_IMM_CHILD_NODES');
685 RAISE;
686
687 END INSERT_IMM_CHILD_NODES;
688
689 -- **************************************************************************
690 -- This procedure will populate the TMP hierarchy table
691
692 PROCEDURE Flatten_CC_Dim_Hier (p_vset_id NUMBER, p_root_node VARCHAR2) IS
693 CURSOR MAIN_CSR is
694 SELECT parent_level, parent_cc_id, next_level, next_level_cc_id,
695 child_level, child_cc_id, child_flex_value_set_id,
696 parent_flex_value_set_id
697 FROM fii_COST_CTR_hier_gt
698 ORDER BY parent_level, child_level;
699
700 l_flex_value VARCHAR2(150);
701 p_parent_id NUMBER(15);
702
703 BEGIN
704
705 IF (FIIDIM_Debug) THEN
706 FII_MESSAGE.Func_Ent(func_name => 'FII_CC_MAINTAIN_PKG.'||
707 'Flatten_CC_Dim_Hier');
708 END IF;
709 g_phase := 'Truncate table FII_CC_HIER_GT';
710 FII_UTIL.truncate_table ('FII_COST_CTR_HIER_GT', 'FII', g_retcode);
711
712 -----------------------------------------------------------------
713
714 CCDIM_parent_node := p_root_node;
715 CCDIM_parent_vset_id := p_vset_id;
716
717 g_phase := 'Get p_parent_id from FND_FLEX_VALUES';
718
719 SELECT flex_value_id INTO p_parent_id
720 FROM FND_FLEX_VALUES
721 WHERE flex_value_set_id = p_vset_id
722 AND flex_value = p_root_node;
723
724 CCDIM_parent_flex_id := p_parent_id;
725
726 -- The following Insert statement inserts the top node self row and
727 -- invokes Ins_Imm_Child_nodes routine to insert all top node mappings
728 -- with in the hierarchy.
729 g_phase := 'insert top node self row and invoke Ins_Imm_Child_nodes';
730
731 INSERT_IMM_CHILD_NODES (p_vset_id, p_root_node);
732
736 next_level,
733 insert into fii_COST_CTR_hier_gt (
734 parent_level,
735 parent_cc_id,
737 next_level_cc_id,
738 child_level,
739 child_cc_id,
740 child_flex_value_set_id,
741 parent_flex_value_set_id,
742 next_level_is_leaf_flag,
743 is_leaf_flag)
744 select
745 child_level,
746 child_cc_id,
747 child_level,
748 child_cc_id,
749 child_level,
750 child_cc_id,
751 child_flex_value_set_id,
752 child_flex_value_set_id,
753 'N',
754 'N'
755 from (select distinct child_cc_id,child_level,child_flex_value_set_id from fii_cOST_CTR_hier_gt);
756
757 IF (FIIDIM_Debug) THEN
758 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_cost_ctr_hier_gt');
759 END IF;
760
761 INSERT INTO fii_COST_CTR_hier_gt
762 (parent_level,
763 parent_cc_id,
764 next_level,
765 next_level_cc_id,
766 child_level,
767 child_cc_id,
768 child_flex_value_set_id,
769 parent_flex_value_set_id,
770 next_level_is_leaf_flag,
771 is_leaf_flag)
772 VALUES
773 (1,
774 p_parent_id,
775 1,
776 p_parent_id,
777 1,
778 p_parent_id,
779 p_vset_id,
780 CCDIM_parent_vset_id,
781 'N',
782 'N');
783
784 IF (FIIDIM_Debug) THEN
785 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_cost_ctr_hier_gt');
786 END IF;
787
788 --Insert the UNASSIGNED to the hierarchy table.
789 --Use top node as the parent
790 g_phase := 'Insert the UNASSIGNED to the hierarchy table';
791
792 -- First one is (G_TOP_NODE_ID, UNASSIGNED, UNASSIGNED)
793 INSERT INTO fii_COST_CTR_hier_gt
794 (parent_level,
795 parent_cc_id,
796 next_level,
797 next_level_cc_id,
798 child_level,
799 child_cc_id,
800 child_flex_value_set_id,
801 parent_flex_value_set_id,
802 next_level_is_leaf_flag,
803 is_leaf_flag)
804 VALUES (
805 1,
806 G_TOP_NODE_ID,
807 2,
808 G_UNASSIGNED_CC_ID,
809 2,
810 G_UNASSIGNED_CC_ID,
811 G_FII_INT_VALUE_SET_ID,
812 G_MASTER_VALUE_SET_ID,
813 'N',
814 'N');
815
816 IF (FIIDIM_Debug) THEN
817 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_cost_ctr_hier_gt');
818 END IF;
819
820 -- Another one is (UNASSIGNED, UNASSIGNED, UNASSIGNED)
821 INSERT INTO fii_COST_CTR_hier_gt
822 (parent_level,
823 parent_cc_id,
824 next_level,
825 next_level_cc_id,
826 child_level,
827 child_cc_id,
828 child_flex_value_set_id,
829 parent_flex_value_set_id,
830 next_level_is_leaf_flag,
831 is_leaf_flag)
832 VALUES (
833 2,
834 G_UNASSIGNED_CC_ID,
835 2,
836 G_UNASSIGNED_CC_ID,
837 2,
838 G_UNASSIGNED_CC_ID,
839 G_FII_INT_VALUE_SET_ID,
840 G_FII_INT_VALUE_SET_ID,
841 'N',
842 'N');
843
844 IF (FIIDIM_Debug) THEN
845 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_cost_ctr_hier_gt');
846 END IF;
847
848 -- Insert a dummy super top node (-999) to the hierarchy table
849 -- (the dummy value set id is -998)
850 g_phase := 'Insert a dummy top node (-999) to the hierarchy table';
851
852 INSERT INTO fii_COST_CTR_hier_gt
853 (parent_level,
854 parent_cc_id,
855 next_level,
856 next_level_cc_id,
857 child_level,
858 child_cc_id,
859 child_flex_value_set_id,
860 parent_flex_value_set_id,
861 next_level_is_leaf_flag,
862 is_leaf_flag)
863 SELECT
864 0,
865 -999,
866 1,
867 G_TOP_NODE_ID,
868 child_level,
869 child_cc_id,
870 child_flex_value_set_id,
871 -998,
872 'N',
873 'N'
874 FROM fii_COST_CTR_hier_gt
875 WHERE child_cc_id = parent_cc_id;
876
877
878 IF (FIIDIM_Debug) THEN
879 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_cost_ctr_hier_gt');
880 END IF;
881
882 --Call FND_STATS to collect statistics after populating the table
883 g_phase := 'gather_table_stats for FII_FIN_ITEM_HIER_GT';
884
885 FND_STATS.gather_table_stats
889 --====================================================================
886 (ownname => g_schema_name,
887 tabname => 'FII_FIN_ITEM_HIER_GT');
888
890 --Before we proceed to populate the final hierarchy table, we should
891 --check if there are any diamond shapes in the TMP hierarchy table.
892 --If so, we will report the problem, and error out the program
893
894 -- The following block checks for child value multiple assignments
895 -- to different parents within the value sets
896 -- We use (just created) TMP table FII_CC_HIER_GT for this purpose
897 g_phase := 'Call Detect_Diamond_Shape';
898
899 Detect_Diamond_Shape;
900
901 ----------------------------------------------------------------------
902 -- We are not updating the next_level_is_leaf_flag and is_leaf_flag
903 -- for the full hierarchy since it's not used anywhere
904 ----------------------------------------------------------------------
905
906 IF (FIIDIM_Debug) THEN
907 FII_MESSAGE.Func_Succ(func_name => 'FII_CC_MAINTAIN_PKG.'||
908 'Flatten_CC_Dim_Hier');
909 END IF;
910
911 EXCEPTION
912
913 WHEN NO_DATA_FOUND THEN
914 FII_UTIL.Write_Log ('Flatten_CC_Dim_Hier: No Data Found');
915 FII_MESSAGE.Func_Fail(func_name => 'FII_CC_MAINTAIN_PKG.'||
916 'Flatten_CC_Dim_Hier');
917 raise;
918
919 WHEN CCDIM_MULT_PAR_err THEN
920 FII_UTIL.Write_Log ('Flatten_CC_Dim_Hier: Diamond Shape Detected');
921 FII_MESSAGE.Func_Fail (func_name =>
922 'FII_DIMENSION_MAINTAIN_PKG.Flatten_CC_Dim_Hier');
923 raise;
924
925 WHEN OTHERS THEN
926 FII_UTIL.Write_Log ('Flatten_CC_Dim_Hier: '|| substr(sqlerrm,1,180));
927 FII_MESSAGE.Func_Fail(func_name => 'FII_CC_MAINTAIN_PKG.'||
928 'Flatten_CC_Dim_Hier');
929 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
930 raise;
931
932 END Flatten_CC_Dim_Hier;
933
934
935 --**********************************************************************************************
936 PROCEDURE Get_level_populated IS
937
938 -- For BI - 2006
939 CURSOR pre_dep_cur IS SELECT * FROM
940 ( -- normalized parent-child relationship (one-level)
941 select parent_cc_id pid
942 , child_cc_id cid
943 , child_level clv
944 , child_flex_value_set_id cvs
945 , is_leaf_flag clf
946 from fii_cost_ctr_hier_gt
947 where parent_level + 1 = child_level
948 --and child_flex_value_set_id = G_MASTER_VALUE_SET_ID
949 union all
950 select NULL, -999, 0, -998, 'N'
951 from dual
952 )
953 START WITH pid is null
954 CONNECT BY pid = PRIOR cid
955 ORDER siblings BY cid;
956
957 -- For BI - 2006
958 TYPE stack_type IS VARRAY( 128 ) OF pre_dep_cur%ROWTYPE;
959
960 r_stack stack_type := stack_type(); -- the stack
961 c_top number; -- index of the top element of the stack (child level)
962
963 n_top number; -- next level (parent level is p_top defined in the body)
964 --p_top1 number;
965 BEGIN
966 r_stack.extend( 128 );
967
968 g_phase := 'Populating level columns added for siebel content';
969 ----------------------------------------------------------------------
970 -- We want to update the newly introduced level columns for BI - 2006
971 ----------------------------------------------------------------------
972 FOR pre_dep_rec IN pre_dep_cur LOOP
973 -- put (pop/push) the new child value on the stack
974 c_top := pre_dep_rec.clv;
975 r_stack( c_top+1 ) := pre_dep_rec;
976 -- loop through the stack for all the parents
977 FOR p_top IN 0..c_top LOOP
978 -- figure out the next level
979 IF p_top = c_top THEN
980 n_top := p_top;
981 ELSE
982 n_top := p_top + 1;
983 END IF;
984
985 update fii_cost_ctr_hier_gt
986 set LEVEL2_CC_ID = r_stack( least( p_top + 2, c_top+1 ) ).cid
987 , LEVEL3_CC_ID = r_stack( least( p_top + 3, c_top+1 ) ).cid
988 , LEVEL4_CC_ID = r_stack( least( p_top + 4, c_top+1 ) ).cid
989 , LEVEL5_CC_ID = r_stack( least( p_top + 5, c_top+1 ) ).cid
990 where parent_cc_id = r_stack( p_top+1 ).cid
991 and child_cc_id = r_stack( c_top+1 ).cid;
992
993 END LOOP;
994
995 END LOOP;
996
997 END Get_level_populated;
998
999
1000 -- **************************************************************************
1001 -- Populate the pruned COST CENTER hierarchy FII_CC_HIERARCHIES by deleting from
1002 -- FII_CC_HIER_GT (full version) the LVS records from the temporary table
1003
1004 PROCEDURE Get_Pruned_CC_GT IS
1005
1006 Begin
1007
1008 IF (FIIDIM_Debug) THEN
1009 FII_MESSAGE.Func_Ent(func_name => 'FII_CC_MAINTAIN_PKG.'||
1010 'Get_Pruned_CC_GT');
1011 END IF;
1012 --Delete from FII_CC_HIER_GT for child value set not equal to
1016 Delete from FII_COST_CTR_HIER_GT
1013 --the master value set and not equal to the UNASSIGNED value set.
1014 g_phase := 'Delete FII_CC_HIER_GT #1';
1015
1017 Where child_flex_value_set_id <> G_MASTER_VALUE_SET_ID
1018 And child_flex_value_set_id <> G_FII_INT_VALUE_SET_ID;
1019
1020 IF (FIIDIM_Debug) THEN
1021 FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from fii_COST_CTR_hier_gt');
1022 END IF;
1023
1024 Get_level_populated;
1025
1026 --Finally, update the columns next_level_is_leaf_flag, is_leaf_flag again
1027 --for the latest FII_CC_HIER_GT
1028 g_phase := 'Update next_level_is_leaf_flag, is_leaf_flag';
1029
1030 --Update the column next_level_is_leaf_flag
1031 --We look at those records (P,A,A) in which A is a leaf value
1032 Update fii_cost_ctr_hier_gt tab1
1033 Set next_level_is_leaf_flag = 'Y'
1034 Where tab1.next_level_cc_id = tab1.child_cc_id
1035 and tab1.next_level_cc_id IN (
1036 select /*+ ordered */ tab3.next_level_cc_id
1037 from fii_cost_ctr_hier_gt tab3,
1038 fii_cost_ctr_hier_gt tab2
1039 where tab2.parent_cc_id = tab3.parent_cc_id
1040 and tab3.parent_cc_id = tab3.child_cc_id
1041 group by tab3.next_level_cc_id
1042 having count(*) = 1);
1043
1044 IF (FIIDIM_Debug) THEN
1045 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_COST_CTR_hier_gt');
1046 END IF;
1047
1048
1049 -- Update the column is_leaf_flag
1050 -- We look at all records (A,A,A) in which A is a leaf value
1051 Update fii_COST_CTR_hier_gt
1052 Set is_leaf_flag = 'Y'
1053 Where parent_cc_id = child_cc_id
1054 and next_level_is_leaf_flag = 'Y';
1055
1056 IF (FIIDIM_Debug) THEN
1057 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_COST_CTR_hier_gt');
1058 END IF;
1059
1060 IF (FIIDIM_Debug) THEN
1061 FII_MESSAGE.Func_Succ(func_name => 'FII_CC_MAINTAIN_PKG.'||
1062 'Get_Pruned_CC_GT');
1063 END IF;
1064 EXCEPTION
1065
1066 WHEN OTHERS THEN
1067 FII_UTIL.Write_Log ('Get_Pruned_CC_GT -> phase: '|| g_phase);
1068 FII_UTIL.Write_Log ('Get_Pruned_CC_GT: '|| substr(sqlerrm,1,180));
1069 FII_MESSAGE.Func_Fail(func_name => 'FII_CC_MAINTAIN_PKG.'||
1070 'Get_Pruned_CC_GT');
1071 raise;
1072
1073 END Get_Pruned_CC_GT;
1074
1075 -- **************************************************************************
1076 -- Insert UNASSIGNED to the dimension tables (both full and pruned version)
1077 --
1078
1079 PROCEDURE Handle_Unenabled_DIM IS
1080
1081 l_count number := 0;
1082
1083 Begin
1084
1085 IF (FIIDIM_Debug) THEN
1086 FII_MESSAGE.Func_Ent(func_name => 'FII_CC_MAINTAIN_PKG.'||
1087 'Handle_Unenabled_DIM');
1088 END IF;
1089 -- Bug 4147558.
1090 g_phase := 'Check if the dimension was already disabled';
1091 -- We dont truncate the tables in case the dimension was disabled
1092 -- before also since truncation of the tables does not let
1093 -- incremental refresh of MV happen.
1094 -- If the dimension hierarchy table has 1 record then the dimension
1095 -- was disabled previously.
1096 select count(*) into l_count from fii_full_cost_ctr_hiers;
1097
1098 IF(l_count > 1 OR l_count = 0) THEN
1099 -- Incase the dimension hierarchy table had more than 1 record
1100 -- this means the dimension was enabled previously and it has been
1101 -- disabled now, in which case initial refresh of MV should happen
1102 -- so it is ok to truncate the tables
1103
1104 g_phase := 'Truncate dimension hierarchy tables';
1105 FII_UTIL.truncate_table ('FII_FULL_COST_CTR_HIERS', 'FII', g_retcode);
1106 FII_UTIL.truncate_table ('FII_COST_CTR_HIERARCHIES', 'FII', g_retcode);
1107
1108 INSERT INTO FII_FULL_COST_CTR_HIERS
1109 (parent_level,
1110 parent_cc_id,
1111 next_level,
1112 next_level_cc_id,
1113 child_level,
1114 child_cc_id,
1115 child_flex_value_set_id,
1116 parent_flex_value_set_id,
1117 next_level_is_leaf_flag,
1118 is_leaf_flag,
1119 creation_date,
1120 created_by,
1121 last_update_date,
1122 last_updated_by,
1123 last_update_login)
1124 VALUES (
1125 1,
1126 G_UNASSIGNED_CC_ID,
1127 1,
1128 G_UNASSIGNED_CC_ID,
1129 1,
1130 G_UNASSIGNED_CC_ID,
1131 G_FII_INT_VALUE_SET_ID,
1132 G_FII_INT_VALUE_SET_ID,
1133 'N',
1134 'N',
1135 SYSDATE,
1136 FII_USER_ID,
1137 SYSDATE,
1138 FII_USER_ID,
1139 FII_LOGIN_ID);
1140
1141 INSERT INTO FII_COST_CTR_HIERARCHIES
1142 (parent_level,
1143 parent_cc_id,
1144 next_level,
1145 next_level_cc_id,
1149 parent_flex_value_set_id,
1146 child_level,
1147 child_cc_id,
1148 child_flex_value_set_id,
1150 next_level_is_leaf_flag,
1151 is_leaf_flag,
1152 aggregate_next_level_flag,
1153 LEVEL2_CC_ID,
1154 LEVEL3_CC_ID,
1155 LEVEL4_CC_ID ,
1156 LEVEL5_CC_ID,
1157 creation_date,
1158 created_by,
1159 last_update_date,
1160 last_updated_by,
1161 last_update_login)
1162 VALUES (
1163 1,
1164 G_UNASSIGNED_CC_ID,
1165 1,
1166 G_UNASSIGNED_CC_ID,
1167 1,
1168 G_UNASSIGNED_CC_ID,
1169 G_FII_INT_VALUE_SET_ID,
1170 G_FII_INT_VALUE_SET_ID,
1171 'N',
1172 'N',
1173 'N',
1174 G_UNASSIGNED_CC_ID,
1175 G_UNASSIGNED_CC_ID,
1176 G_UNASSIGNED_CC_ID,
1177 G_UNASSIGNED_CC_ID,
1178 SYSDATE,
1179 FII_USER_ID,
1180 SYSDATE,
1181 FII_USER_ID,
1182 FII_LOGIN_ID);
1183
1184 commit;
1185 END IF;
1186
1187 IF (FIIDIM_Debug) THEN
1188 FII_MESSAGE.Func_Succ(func_name => 'FII_CC_MAINTAIN_PKG.'||
1189 'Handle_Unenabled_DIM');
1190 END IF;
1191
1192 EXCEPTION
1193
1194 WHEN OTHERS THEN
1195 FII_UTIL.Write_Log ('Handle_Unenabled_DIM: '|| substr(sqlerrm,1,180));
1196 FII_MESSAGE.Func_Fail(func_name => 'FII_CC_MAINTAIN_PKG.'||
1197 'Handle_Unenabled_DIM');
1198 raise;
1199
1200 END Handle_Unenabled_DIM;
1201
1202
1203 -- **************************************************************************
1204 -- This is the main procedure of COST CENTER dimension program (initial populate).
1205
1206 PROCEDURE Init_Load (errbuf OUT NOCOPY VARCHAR2,
1207 retcode OUT NOCOPY VARCHAR2) IS
1208
1209 ret_val BOOLEAN := FALSE;
1210
1211 BEGIN
1212
1213 IF (FIIDIM_Debug) THEN
1214 FII_MESSAGE.Func_Ent(func_name => 'FII_CC_MAINTAIN_PKG.Init_Load');
1215 END IF;
1216
1217 --First do the initialization
1218
1219 Initialize;
1220
1221 --Secondly populate the table FII_DIM_NORM_HIER_GT
1222
1223 Get_NORM_HIERARCHY_TMP;
1224
1225 --Call the Flatten COST CENTER dimension hierarchy routine to insert all mappings.
1226
1227 Flatten_CC_Dim_Hier (G_MASTER_VALUE_SET_ID, G_TOP_NODE_VALUE);
1228
1229 --==============================================================--
1230
1231 --Copy TMP hierarchy table to the final dimension table
1232 g_phase := 'Copy TMP hierarchy table to the final full dimension table';
1233
1234 FII_UTIL.truncate_table ('FII_FULL_COST_CTR_HIERS', 'FII', g_retcode);
1235
1236 INSERT /*+ APPEND */ INTO FII_FULL_COST_CTR_HIERS (
1237 parent_level,
1238 parent_cc_id,
1239 next_level,
1240 next_level_cc_id,
1241 next_level_is_leaf_flag,
1242 is_leaf_flag,
1243 child_level,
1244 child_cc_id,
1245 parent_flex_value_set_id,
1246 child_flex_value_set_id,
1247 creation_date,
1248 created_by,
1249 last_update_date,
1250 last_updated_by,
1251 last_update_login)
1252 SELECT
1253 parent_level,
1254 parent_cc_id,
1255 next_level,
1256 next_level_cc_id,
1257 next_level_is_leaf_flag,
1258 is_leaf_flag,
1259 child_level,
1260 child_cc_id,
1261 parent_flex_value_set_id,
1262 child_flex_value_set_id,
1263 SYSDATE,
1264 FII_USER_ID,
1265 SYSDATE,
1266 FII_USER_ID,
1267 FII_LOGIN_ID
1268 FROM FII_COST_CTR_HIER_GT;
1269
1270 IF (FIIDIM_Debug) THEN
1271 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FULL_COST_CTR_HIERS');
1272 END IF;
1273
1274
1275 --Call FND_STATS to collect statistics after re-populating the tables.
1276 --for the full dimension table since it will be used later
1277 FND_STATS.gather_table_stats
1278 (ownname => g_schema_name,
1279 tabname => 'FII_FULL_COST_CTR_HIERS');
1280
1281 --==============================================================--
1282
1283 --Delete/Update FII_CC_HIER_GT for pruned hierarchy table
1284 g_phase := 'Delete/Update FII_CC_HIER_GT for pruned hierarchy table';
1285
1286 Get_Pruned_CC_GT;
1287
1288 --Copy FII_CC_HIER_GT to the final (pruned) dimension table
1289 g_phase := 'Copy TMP hierarchy table to the final pruned dimension table';
1290
1291 FII_UTIL.truncate_table ('FII_COST_CTR_HIERARCHIES', 'FII', g_retcode);
1292
1293 INSERT /*+ APPEND */ INTO FII_COST_CTR_HIERARCHIES (
1294 parent_level,
1295 parent_cc_id,
1296 next_level,
1297 next_level_cc_id,
1298 next_level_is_leaf_flag,
1299 is_leaf_flag,
1300 child_level,
1301 child_cc_id,
1302 parent_flex_value_set_id,
1303 child_flex_value_set_id,
1304 NEXT_LEVEL_CC_SORT_ORDER,
1308 LEVEL4_CC_ID ,
1305 aggregate_next_level_flag,
1306 LEVEL2_CC_ID,
1307 LEVEL3_CC_ID,
1309 LEVEL5_CC_ID,
1310 creation_date,
1311 created_by,
1312 last_update_date,
1313 last_updated_by,
1314 last_update_login)
1315 SELECT
1316 parent_level,
1317 parent_cc_id,
1318 next_level,
1319 next_level_cc_id,
1320 next_level_is_leaf_flag,
1321 is_leaf_flag,
1322 child_level,
1323 child_cc_id,
1324 parent_flex_value_set_id,
1325 child_flex_value_set_id,
1326 NEXT_LEVEL_CC_SORT_ORDER,
1327 'N',
1328 LEVEL2_CC_ID,
1329 LEVEL3_CC_ID,
1330 LEVEL4_CC_ID ,
1331 LEVEL5_CC_ID,
1332 SYSDATE,
1333 FII_USER_ID,
1334 SYSDATE,
1335 FII_USER_ID,
1336 FII_LOGIN_ID
1337 FROM FII_COST_CTR_HIER_GT;
1338
1339
1340 IF (FIIDIM_Debug) THEN
1341 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COST_CTR_HIERARCHIES');
1342 END IF;
1343
1344 -- This will be in RSG data
1345 g_phase := 'gather_table_stats for FII_COST_CTR_HIERARCHIES';
1346 FND_STATS.gather_table_stats
1347 (ownname => g_schema_name,
1348 tabname => 'FII_COST_CTR_HIERARCHIES');
1349
1350 g_phase := 'gather_table_stats for MLOG$_FII_COST_CTR_HIERARC';
1351 FND_STATS.gather_table_stats
1352 (ownname => g_schema_name,
1353 tabname => 'MLOG$_FII_COST_CTR_HIERARC');
1354
1355 --================================================================--
1356
1357 FND_CONCURRENT.Af_Commit;
1358
1359 IF (FIIDIM_Debug) THEN
1360 FII_MESSAGE.Func_Succ(func_name => 'FII_CC_MAINTAIN_PKG.Init_Load');
1361 end if;
1362
1363 -- Exception handling
1364
1365 EXCEPTION
1366
1367 WHEN CCDIM_fatal_err THEN
1368
1369 FII_UTIL.Write_Log ('FII_CC_MAINTAIN_PKG.Init_Load: '||
1370 'User defined error');
1371
1372 FND_CONCURRENT.Af_Rollback;
1373 FII_MESSAGE.Func_Fail(func_name => 'FII_CC_MAINTAIN_PKG.Init_Load');
1374 retcode := sqlcode;
1375 ret_val := FND_CONCURRENT.Set_Completion_Status
1376 (status => 'ERROR', message => substr(sqlerrm,1,180));
1377
1378 WHEN CCDIM_MULT_PAR_err THEN
1379 FII_UTIL.Write_Log ('FII_CC_MAINTAIN_PKG.Init_Load: '||
1380 'Diamond Shape Detected');
1381
1382 FND_CONCURRENT.Af_Rollback;
1383 FII_MESSAGE.Func_Fail(func_name => 'FII_CC_MAINTAIN_PKG.Init_Load');
1384 retcode := sqlcode;
1385 ret_val := FND_CONCURRENT.Set_Completion_Status
1386 (status => 'ERROR', message => substr(sqlerrm,1,180));
1387
1388 WHEN CCDIM_NOT_ENABLED THEN
1389 FII_UTIL.Write_Log ('>>> COST CENTER Dimension Not Enabled...');
1390
1391 Handle_Unenabled_DIM;
1392
1393 retcode := sqlcode;
1394 --ret_val := FND_CONCURRENT.Set_Completion_Status
1395 -- (status => 'NORMAL', message => NULL);
1396
1397 WHEN OTHERS THEN
1398 FII_UTIL.Write_Log ('Init_Load -> phase: '|| g_phase);
1399 FII_UTIL.Write_Log (
1400 'Other error in FII_CC_MAINTAIN_PKG.Init_Load: ' || substr(sqlerrm,1,180));
1401
1402
1403 FND_CONCURRENT.Af_Rollback;
1404 FII_MESSAGE.Func_Fail(func_name => 'FII_CC_MAINTAIN_PKG.Init_Load');
1405 retcode := sqlcode;
1406 ret_val := FND_CONCURRENT.Set_Completion_Status
1407 (status => 'ERROR', message => substr(sqlerrm,1,180));
1408
1409 END Init_Load;
1410
1411
1412 -- *****************************************************************
1413
1414 -- This is the main procedure of COST CENTER dimension program (incremental update).
1415
1416 PROCEDURE Incre_Update (errbuf OUT NOCOPY VARCHAR2,
1417 retcode OUT NOCOPY VARCHAR2) IS
1418
1419 ret_val BOOLEAN := FALSE;
1420
1421 BEGIN
1422
1423 IF (FIIDIM_Debug) THEN
1424 FII_MESSAGE.Func_Ent(func_name => 'FII_CC_MAINTAIN_PKG.Incre_Update');
1425 END IF;
1426
1427 --First do the initialization
1428
1429 Initialize;
1430
1431 --Secondly populate the table FII_DIM_NORM_HIER_GT
1432
1433 Get_NORM_HIERARCHY_TMP;
1434
1435 --Call the Flatten COST CENTER dimension hierarchy routine to insert all mappings.
1436
1437 Flatten_CC_Dim_Hier (G_MASTER_VALUE_SET_ID, G_TOP_NODE_VALUE);
1438
1439
1440 g_phase := 'Copy TMP hierarchy table to the final full dimension table';
1441 FII_UTIL.truncate_table ('FII_FULL_COST_CTR_HIERS', 'FII', g_retcode);
1442
1443 Insert into FII_FULL_COST_CTR_HIERS (
1444 parent_level,
1445 parent_cc_id,
1446 next_level,
1447 next_level_cc_id,
1448 next_level_is_leaf_flag,
1449 is_leaf_flag,
1450 child_level,
1451 child_cc_id,
1452 parent_flex_value_set_id,
1453 child_flex_value_set_id,
1454 creation_date,
1455 created_by,
1456 last_update_date,
1457 last_updated_by,
1458 last_update_login)
1459 SELECT parent_level,
1460 parent_cc_id,
1464 is_leaf_flag,
1461 next_level,
1462 next_level_cc_id,
1463 next_level_is_leaf_flag,
1465 child_level,
1466 child_cc_id,
1467 parent_flex_value_set_id,
1468 child_flex_value_set_id,
1469 SYSDATE,
1470 FII_USER_ID,
1471 SYSDATE,
1472 FII_USER_ID,
1473 FII_LOGIN_ID
1474 FROM FII_COST_CTR_HIER_GT;
1475
1476 IF (FIIDIM_Debug) THEN
1477 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FULL_COST_CTR_HIERS');
1478 END IF;
1479
1480 --Call FND_STATS to collect statistics after re-populating the tables.
1481 --for the full dimension table since it will be used later
1482 FND_STATS.gather_table_stats
1483 (ownname => g_schema_name,
1484 tabname => 'FII_FULL_COST_CTR_HIERS');
1485
1486 --==============================================================--
1487
1488 --Delete/Update FII_CC_HIER_GT for pruned hierarchy table
1489 g_phase := 'Delete/Update FII_CC_HIER_GT for pruned hierarchy table';
1490
1491 Get_Pruned_CC_GT;
1492
1493 --Copy FII_CC_HIER_GT to the final (pruned) dimension table
1494 g_phase := 'Copy TMP hierarchy table to the final pruned dimension table';
1495
1496 -- Incremental Dimension Maintence
1497 -- All data is now in the temporary table FII_COST_CTR_HIER_GT,
1498 -- we need to maintain the permanent table FII_COST_CTR_HIERARCHIES
1499 -- by diffing the 2 tables.
1500 -- The maintenance is done by 2 statements, one INSERT and one DELETE.
1501
1502 DELETE FROM FII_COST_CTR_HIERARCHIES
1503 WHERE
1504 (parent_level, parent_cc_id, next_level,
1505 next_level_cc_id,
1506 next_level_is_leaf_flag, is_leaf_flag, child_level,
1507 child_cc_id,
1508 parent_flex_value_set_id,
1509 child_flex_value_set_id,
1510 NVL(next_level_cc_sort_order, -92883), LEVEL2_CC_ID,
1511 LEVEL3_CC_ID,
1512 LEVEL4_CC_ID ,
1513 LEVEL5_CC_ID) IN
1514 (SELECT parent_level, parent_cc_id,
1515 next_level, next_level_cc_id,
1516 next_level_is_leaf_flag, is_leaf_flag, child_level,
1517 child_cc_id,parent_flex_value_set_id,
1518 child_flex_value_set_id, NVL(next_level_cc_sort_order, -92883), LEVEL2_CC_ID,
1519 LEVEL3_CC_ID,
1520 LEVEL4_CC_ID ,
1521 LEVEL5_CC_ID
1522 FROM FII_COST_CTR_HIERARCHIES
1523 MINUS
1524 SELECT parent_level, parent_cc_id,
1525 next_level, next_level_cc_id,
1526 next_level_is_leaf_flag, is_leaf_flag,
1527 child_level, child_cc_id,
1528 parent_flex_value_set_id,
1529 child_flex_value_set_id, NVL(next_level_cc_sort_order, -92883), LEVEL2_CC_ID,
1530 LEVEL3_CC_ID,
1531 LEVEL4_CC_ID ,
1532 LEVEL5_CC_ID
1533 FROM FII_COST_CTR_HIER_GT);
1534
1535
1536 IF (FIIDIM_Debug) THEN
1537 FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_COST_CTR_HIERARCHIES');
1538 END IF;
1539
1540 Insert into FII_COST_CTR_HIERARCHIES (
1541 parent_level,
1542 parent_cc_id,
1543 next_level,
1544 next_level_cc_id,
1545 next_level_is_leaf_flag,
1546 is_leaf_flag,
1547 child_level,
1548 child_cc_id,
1549 parent_flex_value_set_id,
1550 child_flex_value_set_id,
1551 aggregate_next_level_flag,
1552 next_level_cc_sort_order,
1553 LEVEL2_CC_ID,
1554 LEVEL3_CC_ID,
1555 LEVEL4_CC_ID ,
1556 LEVEL5_CC_ID,
1557 creation_date,
1558 created_by,
1559 last_update_date,
1560 last_updated_by,
1561 last_update_login)
1562 (SELECT parent_level,
1563 parent_cc_id,
1564 next_level,
1565 next_level_cc_id,
1566 next_level_is_leaf_flag,
1567 is_leaf_flag,
1568 child_level,
1569 child_cc_id,
1570 parent_flex_value_set_id,
1571 child_flex_value_set_id,
1572 'N',
1573 next_level_cc_sort_order,
1574 LEVEL2_CC_ID,
1575 LEVEL3_CC_ID,
1576 LEVEL4_CC_ID ,
1577 LEVEL5_CC_ID,
1578 SYSDATE,
1579 FII_USER_ID,
1580 SYSDATE,
1581 FII_USER_ID,
1582 FII_LOGIN_ID
1583 FROM FII_COST_CTR_HIER_GT
1584 MINUS
1585 SELECT parent_level,
1586 parent_cc_id,
1587 next_level,
1588 next_level_cc_id,
1589 next_level_is_leaf_flag,
1590 is_leaf_flag,
1591 child_level,
1592 child_cc_id,
1593 parent_flex_value_set_id,
1594 child_flex_value_set_id,
1595 'N',
1596 next_level_cc_sort_order,
1597 LEVEL2_CC_ID,
1598 LEVEL3_CC_ID,
1599 LEVEL4_CC_ID ,
1600 LEVEL5_CC_ID,
1601 SYSDATE,
1602 FII_USER_ID,
1603 SYSDATE,
1604 FII_USER_ID,
1605 FII_LOGIN_ID
1606 FROM FII_COST_CTR_HIERARCHIES);
1607
1608 IF (FIIDIM_Debug) THEN
1609 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COST_CTR_HIERARCHIES');
1610 END IF;
1611
1612 -- This is in RSG data
1613 g_phase := 'gather_table_stats for FII_COST_CTR_HIERARCHIES';
1614 FND_STATS.gather_table_stats
1615 (ownname => g_schema_name,
1616 tabname => 'FII_COST_CTR_HIERARCHIES');
1617
1618 -- Bug 4200473. Not to analyze MLOG in incremental run.
1619 -- As per performance teams suggestions.
1620 -- g_phase := 'gather_table_stats for MLOG$_FII_COST_CTR_HIERARC';
1621 -- FND_STATS.gather_table_stats
1622 -- (ownname => g_schema_name,
1623 -- tabname => 'MLOG$_FII_COST_CTR_HIERARC');
1624
1625 --=============================================================--
1626
1627 FND_CONCURRENT.Af_Commit;
1628
1629 IF (FIIDIM_Debug) THEN
1630 FII_MESSAGE.Func_Succ(func_name => 'FII_CC_MAINTAIN_PKG.Incre_Update');
1631 END IF;
1632
1633 -- Exception handling
1634
1635 EXCEPTION
1636 WHEN CCDIM_fatal_err THEN
1637 FII_UTIL.Write_Log ('FII_CC_MAINTAIN_PKG.Incre_Update'||
1638 'User defined error');
1639
1640 FND_CONCURRENT.Af_Rollback;
1641 FII_MESSAGE.Func_Fail(func_name => 'FII_CC_MAINTAIN_PKG.Incre_Update');
1642 retcode := sqlcode;
1643 ret_val := FND_CONCURRENT.Set_Completion_Status
1644 (status => 'ERROR', message => substr(sqlerrm,1,180));
1645
1646 WHEN CCDIM_MULT_PAR_err THEN
1647 FII_UTIL.Write_Log ('FII_CC_MAINTAIN_PKG.Incre_Update: '||
1648 'Diamond Shape Detected');
1649
1650 FND_CONCURRENT.Af_Rollback;
1651 FII_MESSAGE.Func_Fail(func_name => 'FII_CC_MAINTAIN_PKG.Incre_Update');
1652 retcode := sqlcode;
1653 ret_val := FND_CONCURRENT.Set_Completion_Status
1654 (status => 'ERROR', message => substr(sqlerrm,1,180));
1655
1656 WHEN CCDIM_NOT_ENABLED THEN
1657 FII_UTIL.Write_Log ('>>> COST CENTER Dimension Not Enabled...');
1658
1659 Handle_Unenabled_DIM;
1660
1661 retcode := sqlcode;
1662 -- ret_val := FND_CONCURRENT.Set_Completion_Status
1663 -- (status => 'NORMAL', message => NULL);
1664
1665 WHEN OTHERS THEN
1666 FII_UTIL.Write_Log ('Incre_Update -> phase: '|| g_phase);
1667 FII_UTIL.Write_Log (
1668 'Other error in FII_CC_MAINTAIN_PKG.Incre_Update: ' || substr(sqlerrm,1,180));
1669
1670
1671 FND_CONCURRENT.Af_Rollback;
1672 FII_MESSAGE.Func_Fail(func_name => 'FII_CC_MAINTAIN_PKG.Incre_Update');
1673 retcode := sqlcode;
1674 ret_val := FND_CONCURRENT.Set_Completion_Status
1675 (status => 'ERROR', message => substr(sqlerrm,1,180));
1676
1677 END Incre_Update;
1678
1679 END FII_CC_MAINTAIN_PKG;