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