[Home] [Help]
PACKAGE BODY: APPS.FII_FIN_CAT_MAINTAIN_PKG
Source
1 PACKAGE BODY FII_FIN_CAT_MAINTAIN_PKG AS
2 /* $Header: FIIFICMB.pls 120.7 2006/09/26 12:42:43 arcdixit ship $ */
3
4 G_MASTER_VALUE_SET_ID NUMBER(15) := NULL;
5 G_TOP_NODE_ID NUMBER(15) := NULL;
6 G_TOP_NODE_VALUE VARCHAR2(240) := NULL;
7 -- G_INCL_LEAF_NODES VARCHAR2(1);
8 G_DBI_ENABLED_FLAG VARCHAR2(1);
9
10 g_phase VARCHAR2(120);
11 g_schema_name VARCHAR2(120) := 'FII';
12 g_retcode VARCHAR2(20) := NULL;
13 g_debug_mode VARCHAR2(1)
14 := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
15 g_index NUMBER(10) :=0;
16 g_dimension_name VARCHAR2(30) := 'GL_FII_FIN_ITEM';
17 -- *****************************************************************
18 -- Check if a value set is table validated
19
20 FUNCTION Is_Table_Validated (X_Vs_Id NUMBER) RETURN BOOLEAN IS
21 l_tab_name VARCHAR2(240) := NULL;
22
23 BEGIN
24
25 --if FIIDIM_Debug then
26 -- FII_MESSAGE.Func_Ent (func_name => 'Is_Table_Validated');
27 --end if;
28
29 -- Execute statement to determine if the value set is table validated
30 BEGIN
31
32 SELECT fvt.application_table_name INTO l_tab_name
33 FROM fnd_flex_validation_tables fvt,
34 fnd_flex_value_sets fvs
35 WHERE fvs.flex_value_set_id = X_vs_id
36 AND fvs.validation_type = 'F'
37 AND fvt.flex_value_set_id = fvs.flex_value_set_id;
38 EXCEPTION
39 WHEN NO_DATA_FOUND THEN
40 return FALSE;
41 END;
42
43 --if FIIDIM_Debug then
44 -- FII_MESSAGE.Func_Succ (func_name => 'Is_Table_Validated');
45 --end if;
46
47 RETURN TRUE;
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 RETURN FALSE;
52
53 END Is_Table_Validated;
54
55 -- *******************************************************************
56 -- Function Get_Value_Set_Name
57
58 Function Get_Value_Set_Name (p_vs_id NUMBER) RETURN VARCHAR2 IS
59 l_vs_name varchar2(120);
60
61 Begin
62
63 -- if FIIDIM_Debug then
64 -- FII_MESSAGE.Func_Ent (func_name => 'Get_Value_Set_Name');
65 -- end if;
66
67 select flex_value_set_name into l_vs_name
68 from fnd_flex_value_sets
69 where flex_value_set_id = p_vs_id;
70
71 -- if FIIDIM_Debug then
72 -- FII_MESSAGE.Func_Succ (func_name => 'Get_Value_Set_Name');
73 -- end if;
74
75 return l_vs_name;
76
77 Exception
78 when others then
79 FII_UTIL.Write_Log(
80 'Unexpected error when calling Get_Value_Set_Name...');
81 FII_UTIL.WRITE_LOG ( 'Error Message: '|| substr(sqlerrm,1,80));
82 FII_UTIL.WRITE_LOG ( 'Value Set ID: ' || p_vs_id);
83 RAISE;
84
85 End Get_Value_Set_Name;
86
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 -- Lock down value set for processing
122
123 PROCEDURE lock_flex_value_set (fvsid NUMBER) is
124 lkname varchar2(128);
125 lkhandle varchar2(128);
126 rs_mode constant integer := 5;
127 timout constant integer := 2; -- 2 secs timeout
128 expiration_secs constant integer := 864000;
129 lkresult integer;
130 begin
131 -- generate the name for the user-defined lock
132 lkname := 'FND_FLEX_AHE_VS_' || to_char(fvsid);
133
134 -- get Oracle-assigned lock handle
135 dbms_lock.allocate_unique( lkname, lkhandle, expiration_secs );
136
137 -- request a lock in the ROW SHARE mode
138 lkresult := dbms_lock.request( lkhandle, rs_mode, timout, TRUE );
139
140 if ( lkresult = 0 ) then
141 -- locking was successful
142 return;
143 elsif ( lkresult = 1 ) then
144 -- Dimension Hierarchy Manager is locking out value set
145 FII_UTIL.Write_Log( 'DHM is locking out value set: ' || fvsid);
146 app_exception.raise_exception;
147 else
148 FII_UTIL.Write_Log( 'Error when locking out value set: ' || fvsid);
149 app_exception.raise_exception;
150 end if;
151
152 END lock_flex_value_set;
153
154 -- *******************************************************************
155 -- Initialize (get the master value set and the top node)
156
157 PROCEDURE Initialize IS
158
159 l_dir VARCHAR2(160);
160 l_check NUMBER;
161 l_bool_ret BOOLEAN;
162
163 BEGIN
164
165 ----------------------------------------------
166 -- Do set up for log file
167 ----------------------------------------------
168
169 l_dir := fnd_profile.value('BIS_DEBUG_LOG_DIRECTORY');
170 ------------------------------------------------------
171 -- Set default directory in case if the profile option
172 -- BIS_DEBUG_LOG_DIRECTORY is not set up
173 ------------------------------------------------------
174 if l_dir is NULL then
175 l_dir := FII_UTIL.get_utl_file_dir ;
176 end if;
177
178 ----------------------------------------------------------------
179 -- FII_UTIL.initialize will get profile options FII_DEBUG_MODE
180 -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
181 -- the log files and output files are written to
182 ----------------------------------------------------------------
183 FII_UTIL.initialize('FII_FIN_CAT_MAINTAIN_PKG.log',
184 'FII_FIN_CAT_MAINTAIN_PKG.out',l_dir, 'FII_FIN_CAT_MAINTAIN_PKG');
185
186 -- --------------------------------------------------------
187 -- Check source ledger setup for DBI
188 -- --------------------------------------------------------
189 l_check := FII_EXCEPTION_CHECK_PKG.check_slg_setup;
190
191 if l_check <> 0 then
192 FII_UTIL.write_log(' No source ledger setup for DBI');
193 RAISE FINDIM_fatal_err;
194 end if;
195
196 -- --------------------------------------------------------
197 -- Detect unmapped local value set
198 -- --------------------------------------------------------
199 l_check :=
200 FII_EXCEPTION_CHECK_PKG.detect_unmapped_local_vs(g_dimension_name);
201
202 if l_check > 0 then
203 l_bool_ret := FND_CONCURRENT.Set_Completion_Status(
204 status => 'WARNING',
205 message => 'Detected unmapped local value set.'
206 );
207 elsif l_check < 0 then
208 RAISE FINDIM_fatal_err;
209 end if;
210
211 -- Obtain FII schema name
212 g_schema_name := FII_UTIL.get_schema_name ('FII');
213
214 -- Obtain user ID, login ID and initialize package variables
215 FII_USER_ID := FND_GLOBAL.USER_ID;
216 FII_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
217
218 -- If any of the above values is not set, error out
219 IF (FII_User_Id is NULL OR FII_Login_Id is NULL) THEN
220 -- Fail to initialize
221 FII_UTIL.Write_Log(' Fail Intialization');
222 RAISE FINDIM_fatal_err;
223 END IF;
224
225 -- Determine if process will be run in debug mode
226 IF (NVL(G_Debug_Mode, 'N') <> 'N') THEN
227 FIIDIM_Debug := TRUE;
228 FII_UTIL.Write_Log('Debug On');
229 ELSE
230 FIIDIM_Debug := FALSE;
231 FII_UTIL.Write_Log('Debug Off');
232 END IF;
233
234 -- Turn trace on if process is run in debug mode
235 IF (FIIDIM_Debug) THEN
236 -- Program running in debug mode, turning trace on
237 EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
238 FII_UTIL.Write_Log('Initialize: Set Trace On');
239 END IF;
240
241 IF (FIIDIM_Debug) THEN
242 FII_UTIL.Write_Log('Initialize: Now start processing '||
243 'Financial Category Dimension');
244 End If;
245
246 --Get the master value set and top node for Financial Category
247 Begin
248 -- Bug 4152798. Removed literal
249 SELECT MASTER_VALUE_SET_ID, DBI_HIER_TOP_NODE, DBI_HIER_TOP_NODE_ID,
250 DBI_ENABLED_FLAG
251 INTO G_MASTER_VALUE_SET_ID, G_TOP_NODE_VALUE, G_TOP_NODE_ID,
252 G_DBI_ENABLED_FLAG
253 FROM FII_FINANCIAL_DIMENSIONS
254 WHERE DIMENSION_SHORT_NAME = g_dimension_name;
255
256 IF G_MASTER_VALUE_SET_ID is NULL THEN
257 FII_MESSAGE.write_log (msg_name => 'FII_MSTR_VSET_NOT_FOUND',
258 token_num => 0);
259 FII_MESSAGE.write_output (msg_name => 'FII_MSTR_VSET_NOT_FOUND',
260 token_num => 0);
261 RAISE FINDIM_fatal_err;
262 ELSIF G_TOP_NODE_ID is NULL OR G_TOP_NODE_VALUE is NULL THEN
263 FII_MESSAGE.write_log (msg_name => 'FII_MSTR_TNODE_NOT_FOUND',
264 token_num => 0);
265 FII_MESSAGE.write_output (msg_name => 'FII_MSTR_TNODE_NOT_FOUND',
266 token_num => 0);
267 RAISE FINDIM_fatal_err;
268 END IF;
269
270 Exception
271 When NO_DATA_FOUND Then
272 FII_MESSAGE.write_log (msg_name => 'FII_MSTR_VSET_NOT_FOUND',
273 token_num => 0);
274 FII_MESSAGE.write_output (msg_name => 'FII_MSTR_VSET_NOT_FOUND',
275 token_num => 0);
276 RAISE FINDIM_fatal_err;
277 When TOO_MANY_ROWS Then
278 FII_UTIL.Write_Log ('More than one master value set found for Financial Category Dimension');
279 RAISE FINDIM_fatal_err;
280 When OTHERS Then
281 FII_UTIL.Write_Log ('Unexpected error when getting master value set for Financial Category Dimension');
282 FII_UTIL.WRITE_LOG ('Error Message: '|| substr(sqlerrm,1,180));
283 RAISE FINDIM_fatal_err;
284 End;
285
286 IF (FIIDIM_Debug) THEN
287 FII_UTIL.Write_Log('Financial Category Master Value Set Id: '
288 || G_MASTER_VALUE_SET_ID);
289 FII_UTIL.Write_Log('Financial Category Master Value Set Name: '
290 || Get_Value_Set_Name(G_MASTER_VALUE_SET_ID));
291 FII_UTIL.Write_Log(' and Financial Category Top Node: '
292 || G_TOP_NODE_VALUE);
293 END IF;
294
295 -- Check if the master value set is a table validated set.
296 If Is_Table_Validated (G_MASTER_VALUE_SET_ID) Then
297 FII_MESSAGE.write_log (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 FII_MESSAGE.write_output (msg_name => 'FII_TBL_VALIDATED_VSET',
302 token_num => 1,
303 t1 => 'VS_NAME',
304 v1 => Get_Value_Set_Name (G_MASTER_VALUE_SET_ID));
305 RAISE FINDIM_fatal_err;
306 End If;
307
308
309 --If the FC dimension is not enabled, raise an exception
310 IF G_DBI_ENABLED_FLAG <> 'Y' then
311 RAISE FINDIM_NOT_ENABLED;
312 END IF;
313
314 Exception
315
316 When FINDIM_NOT_ENABLED then
317 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
318 --Let the main program to handle this
319 raise;
320
321 When FINDIM_fatal_err then
322 FII_UTIL.Write_Log('FII_FIN_CAT_MAINTAIN_PKG.Initialize : '||
323 'User defined error');
324 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
325 -- Rollback
326 FND_CONCURRENT.Af_Rollback;
327 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Initialize');
328 raise;
329
330 When others then
331 FII_UTIL.Write_Log(
332 'Unexpected error when calling Initialize...');
333 FII_UTIL.WRITE_LOG ( 'Error Message: '|| substr(sqlerrm,1,180));
334 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
335 RAISE;
336
337 END Initialize;
338
339
340 -- *******************************************************************
341 -- Populate the table FII_DIM_NORM_HIER_GT
342
343 PROCEDURE Get_NORM_HIERARCHY_TMP IS
344
345 -- Bug 4152798. Removed literal
346 Cursor all_local_value_sets IS
347 select distinct child_flex_value_set_id
348 from FII_DIM_NORM_HIERARCHY
349 where parent_flex_value_set_id = G_MASTER_VALUE_SET_ID
350 and parent_flex_value_set_id <> child_flex_value_set_id
351 and child_flex_value_set_id IN
352 (select map.flex_value_set_id1
353 from fii_dim_mapping_rules map,
354 fii_slg_assignments sts,
355 fii_source_ledger_groups slg
356 where map.dimension_short_name = g_dimension_name
357 and map.chart_of_accounts_id = sts.chart_of_accounts_id
358 and sts.source_ledger_group_id = slg.source_ledger_group_id
359 and slg.usage_code = 'DBI');
360
361 l_vset_id NUMBER(15);
362
363 BEGIN
364
365 IF (FIIDIM_Debug) THEN
366 FII_MESSAGE.Func_Ent ('FII_FIN_CAT_MAINTAIN_PKG.Get_NORM_HIERARCHY_TMP');
367 END IF;
368
369 --First, copy table FII_DIM_NORM_HIERARCHY
370 g_phase := 'Copy FII_DIM_NORM_HIERARCHY for parent-child valuesets relation';
371
372 Insert into FII_DIM_NORM_HIER_GT (
373 PARENT_FLEX_VALUE_SET_ID,
374 PARENT_FLEX_VALUE,
375 RANGE_ATTRIBUTE,
376 CHILD_FLEX_VALUE_SET_ID,
377 CHILD_FLEX_VALUE_LOW,
378 CHILD_FLEX_VALUE_HIGH)
379 Select
380 PARENT_FLEX_VALUE_SET_ID,
381 PARENT_FLEX_VALUE,
382 RANGE_ATTRIBUTE,
383 CHILD_FLEX_VALUE_SET_ID,
384 CHILD_FLEX_VALUE_LOW,
385 CHILD_FLEX_VALUE_HIGH
386 From FII_DIM_NORM_HIERARCHY
387 Where PARENT_FLEX_VALUE_SET_ID = G_MASTER_VALUE_SET_ID
388 And PARENT_FLEX_VALUE_SET_ID <> CHILD_FLEX_VALUE_SET_ID
389 And CHILD_FLEX_VALUE_SET_ID IN
390 (select map.flex_value_set_id1
391 from fii_dim_mapping_rules map,
392 fii_slg_assignments sts,
393 fii_source_ledger_groups slg
394 where map.dimension_short_name = g_dimension_name -- Bug 4152798. Removed literal
395 and map.chart_of_accounts_id = sts.chart_of_accounts_id
396 and sts.source_ledger_group_id = slg.source_ledger_group_id
397 and slg.usage_code = 'DBI');
398
399 IF (FIIDIM_Debug) THEN
400 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_DIM_NORM_HIER_GT');
401 END IF;
402
403 --Insert records for the master value set
407 PARENT_FLEX_VALUE_SET_ID,
404 g_phase := 'insert records for the master value set';
405
406 Insert into FII_DIM_NORM_HIER_GT (
408 PARENT_FLEX_VALUE,
409 RANGE_ATTRIBUTE,
410 CHILD_FLEX_VALUE_SET_ID,
411 CHILD_FLEX_VALUE_LOW,
412 CHILD_FLEX_VALUE_HIGH)
413 Select
414 FLEX_VALUE_SET_ID,
415 PARENT_FLEX_VALUE,
416 RANGE_ATTRIBUTE,
417 FLEX_VALUE_SET_ID,
418 CHILD_FLEX_VALUE_LOW,
419 CHILD_FLEX_VALUE_HIGH
420 From FND_FLEX_VALUE_NORM_HIERARCHY
421 Where flex_value_set_id = G_MASTER_VALUE_SET_ID;
422
423 IF (FIIDIM_Debug) THEN
424 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_DIM_NORM_HIER_GT');
425 END IF;
426
427 --Insert records for all local (child) value sets
428 g_phase := 'Insert records for all local (child) value sets';
429
430 FOR vset_rec IN all_local_value_sets LOOP
431
432 l_vset_id := vset_rec.child_flex_value_set_id;
433
434 -- Check if the (child) value set is a table validated set.
435 If Is_Table_Validated (l_vset_id) Then
436 FII_MESSAGE.write_log (msg_name => 'FII_TBL_VALIDATED_VSET',
437 token_num => 1,
438 t1 => 'VS_NAME',
439 v1 => Get_Value_Set_Name (l_vset_id));
440 FII_MESSAGE.write_output (msg_name => 'FII_TBL_VALIDATED_VSET',
441 token_num => 1,
442 t1 => 'VS_NAME',
443 v1 => Get_Value_Set_Name (l_vset_id));
444 RAISE FINDIM_fatal_err;
445 End If;
446
447 g_phase := 'Insert records for local value set ' || l_vset_id;
448
449 Insert into FII_DIM_NORM_HIER_GT (
450 PARENT_FLEX_VALUE_SET_ID,
451 PARENT_FLEX_VALUE,
452 RANGE_ATTRIBUTE,
453 CHILD_FLEX_VALUE_SET_ID,
454 CHILD_FLEX_VALUE_LOW,
455 CHILD_FLEX_VALUE_HIGH)
456 Select
457 FLEX_VALUE_SET_ID,
458 PARENT_FLEX_VALUE,
459 RANGE_ATTRIBUTE,
460 FLEX_VALUE_SET_ID,
461 CHILD_FLEX_VALUE_LOW,
462 CHILD_FLEX_VALUE_HIGH
463 From FND_FLEX_VALUE_NORM_HIERARCHY
464 Where flex_value_set_id = l_vset_id;
465
466 END LOOP;
467
468 --Call FND_STATS to collect statistics after populating the table
469 g_phase := 'gather_table_stats for FII_DIM_NORM_HIER_GT';
470
471 FND_STATS.gather_table_stats
472 (ownname => g_schema_name,
473 tabname => 'FII_DIM_NORM_HIER_GT');
474
475 Exception
476
477 When FINDIM_fatal_err then
478 FII_UTIL.Write_Log('FII_FIN_CAT_MAINTAIN_PKG.Get_NORM_HIERARCHY_TMP: '||
479 'User defined error');
480 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
481 -- Rollback
482 FND_CONCURRENT.Af_Rollback;
483 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Get_NORM_HIERARCHY_TMP');
484 raise;
485
486 When others then
487 FII_UTIL.Write_Log(
488 'Unexpected error when calling Get_NORM_HIERARCHY_TMP.');
489 FII_UTIL.WRITE_LOG ( 'Error Message: '|| substr(sqlerrm,1,180));
490 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
491 RAISE;
492
493 END Get_NORM_HIERARCHY_TMP;
494
495
496 -- **************************************************************************
497 -- This procedure will check for child value multiple assignments
498 -- to different parents within FII_FIN_ITEM_HIER_GT (the TMP hierarchy table)
499
500 PROCEDURE Detect_Diamond_Shape IS
501
502 --The first cursor is to find all flex_value_id which has multiple parents;
503 --we look at records such as (P1,A,A) and (P2,A,A)
504 Cursor Dup_Assg_Cur IS
505 SELECT count(parent_fin_cat_id) parents,
506 child_fin_cat_id flex_value_id
507 FROM FII_FIN_ITEM_HIER_GT
508 WHERE next_level_fin_cat_id = child_fin_cat_id
509 AND parent_level = next_level - 1
510 GROUP BY child_fin_cat_id
511 HAVING count(parent_fin_cat_id) > 1;
512
513 --The second cursor is to print out the list of duplicate parents;
514 --again, we get records such as (P1,A,A),(P2,A,A) to print out P1, P2 for A
515 Cursor Dup_Assg_Parent_Cur (p_child_value_id NUMBER) IS
516 SELECT parent_fin_cat_id,
517 parent_flex_value_set_id,
518 child_fin_cat_id,
519 child_flex_value_set_id
520 FROM FII_FIN_ITEM_HIER_GT
521 WHERE child_fin_cat_id = p_child_value_id
522 AND next_level_fin_cat_id = child_fin_cat_id
523 AND parent_level = next_level - 1;
524
525 l_count NUMBER(15):=0;
526 l_flex_value VARCHAR2(120);
527 l_vset_name VARCHAR2(240);
528 l_parent_flex_value VARCHAR2(120);
529 l_parent_vset_name VARCHAR2(240);
530
531 BEGIN
532
533 IF (FIIDIM_Debug) THEN
537 -- check all value sets: if there is a diamond in any of them
534 FII_MESSAGE.Func_Ent ('FII_FIN_CAT_MAINTAIN_PKG.Detect_Diamond_Shape');
535 END IF;
536
538 -- (even values involved are not mapped for the dimension),
539 -- report and raise an exception
540 g_phase := 'check all value sets for diamonds';
541
542 FOR dup_asg_rec IN Dup_Assg_Cur LOOP
543
544 l_count := l_count + 1;
545 if l_count = 1 then
546
547 FII_MESSAGE.write_log(msg_name => 'FII_DMND_SHAPE_VS_EXIST',
548 token_num => 0);
549 FII_MESSAGE.write_log(msg_name => 'FII_REFER_TO_OUTPUT',
550 token_num => 0);
551
552 FII_MESSAGE.write_output (msg_name => 'FII_DMND_SHAPE_VS_EXIST',
553 token_num => 0);
554 FII_MESSAGE.write_output (msg_name => 'FII_DMND_SHAPE_VS_TAB',
555 token_num => 0);
556
557 end if;
558
559 FOR dup_asg_par_rec IN Dup_Assg_Parent_Cur (dup_asg_rec.flex_value_id ) LOOP
560
561 l_flex_value := Get_Flex_Value (dup_asg_par_rec.child_fin_cat_id);
562 l_vset_name := Get_Value_Set_Name (dup_asg_par_rec.child_flex_value_set_id);
563 l_parent_flex_value:= Get_Flex_Value (dup_asg_par_rec.parent_fin_cat_id);
564 l_parent_vset_name := Get_Value_Set_Name (dup_asg_par_rec.parent_flex_value_set_id);
565
566 FII_UTIL.Write_Output (
567 l_flex_value || ' '||
568 l_vset_name || ' '||
569 l_parent_flex_value || ' '||
570 l_parent_vset_name);
571
572 END LOOP;
573
574 END LOOP;
575
576 IF (FIIDIM_Debug) THEN
577 FII_MESSAGE.Func_Succ ('FII_FIN_CAT_MAINTAIN_PKG.Detect_Diamond_Shape');
578 END IF;
579
580 IF l_count > 0 THEN
581 RAISE FINDIM_MULT_PAR_err;
582 END IF;
583
584 Exception
585
586 When FINDIM_MULT_PAR_err then
587 FII_UTIL.Write_Log ('FII_FIN_CAT_MAINTAIN_PKG.Detect_Diamond_Shape: '||
588 'diamond shape detected!');
589 RAISE;
590
591 When others then
592 FII_UTIL.Write_Log (
593 'Unexpected error when calling Detect_Diamond_Shape.');
594 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
595 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
596 RAISE;
597
598 END Detect_Diamond_Shape;
599
600 -- *******************************************************************
601
602 PROCEDURE INSERT_IMM_CHILD_NODES
603 (p_vset_id NUMBER, p_root_node VARCHAR2) IS
604
605 --Per suggestion from performance team, add a hint to the select
606 --(it uses a new index FII_DIM_NORM_HIER_GT_N1)
607 CURSOR direct_children_csr (p_parent_vs_id NUMBER, p_parent_node VARCHAR2) IS
608 SELECT /*+ leading(ffvnh) index(ffvnh) */
609 ffv.flex_value_id, ffv.flex_value, ffv.flex_value_set_id, attribute_sort_order sort_order
610 FROM FII_DIM_NORM_HIER_GT ffvnh,
611 fnd_flex_values ffv
612 WHERE ffvnh.child_flex_value_set_id = ffv.flex_value_set_id
613 AND (ffv.flex_value BETWEEN ffvnh.child_flex_value_low
614 AND ffvnh.child_flex_value_high)
615 AND ((ffvnh.range_attribute = 'P' and ffv.summary_flag = 'Y') OR
616 (ffvnh.range_attribute = 'C' and ffv.summary_flag = 'N'))
617 AND ffvnh.parent_flex_value = p_parent_node
618 AND ffvnh.parent_flex_value_set_id = p_parent_vs_id;
619
620 l_flex_value_id number(15);
621 l_flex_value_set_id number(15);
622 l_sort_order NUMBER(15);
623 BEGIN
624
625 -- IF (FIIDIM_Debug) THEN
626 -- FII_MESSAGE.Func_Ent ('FII_COM_MAINTAIN_PKG.INSERT_IMM_CHILD_NODES');
627 -- END IF;
628
629 select flex_value_id, attribute_sort_order into l_flex_value_id, l_sort_order
630 from fnd_flex_values
631 where flex_value_set_id = p_vset_id
632 and flex_value = p_root_node;
633
634 l_flex_value_set_id := p_vset_id;
635
636 /* Inserting parent in a gt table: FII_DIM_HIER_HELP_GT */
637 g_index := g_index + 1;
638 insert into FII_DIM_HIER_HELP_GT
639 ( IDX,
640 FLEX_VALUE_ID,
641 FLEX_VALUE_SET_ID,
642 NEXT_LEVEL_FLEX_VALUE_ID, SORT_ORDER)
643 values
644 ( g_index,
645 l_flex_value_id,
646 l_flex_value_set_id,
647 l_flex_value_id, l_sort_order);
648
649 update FII_DIM_HIER_HELP_GT
650 set NEXT_LEVEL_FLEX_VALUE_ID= l_flex_value_id,
651 SORT_ORDER= l_sort_order
652 where IDX = g_index - 1;
653
654 FOR direct_children_rec IN direct_children_csr(p_vset_id, p_root_node)
655 LOOP
656
657 /* Inserting record with all parents */
658 INSERT INTO fii_fin_item_hier_gt (
659 parent_level,
663 child_level,
660 parent_fin_cat_id,
661 child_fin_cat_id,
662 next_level,
664 next_level_is_leaf,
665 is_leaf_flag,
666 parent_flex_value_Set_id,
667 child_flex_value_set_id,
668 next_level_fin_cat_id,
669 next_level_fin_cat_sort_order)
670 SELECT pp.idx,
671 pp.flex_value_id,
672 direct_children_rec.flex_value_id,
673 pp.idx + 1,
674 g_index + 1,
675 'N',
676 'N',
677 pp.flex_value_set_id,
678 direct_children_rec.flex_value_set_id,
679 decode(pp.idx, g_index,
680 direct_children_rec.flex_value_id,
681 pp.next_level_flex_value_id),
682 decode(pp.idx, g_index,
683 direct_children_rec.sort_order,
684 pp.sort_order)
685 FROM FII_DIM_HIER_HELP_GT pp;
686
687 --Recursive Call.
688 INSERT_IMM_CHILD_NODES (direct_children_rec.flex_value_set_id,
689 direct_children_rec.flex_value);
690
691 END LOOP;
692
693 /* Deleting parent from the gt table */
694 delete from FII_DIM_HIER_HELP_GT where idx = g_index;
695 g_index := g_index - 1;
696
697 FND_CONCURRENT.Af_Commit;
698
699 EXCEPTION
700 WHEN NO_DATA_FOUND Then
701 FII_UTIL.WRITE_LOG ('Insert Immediate child: No Data Found');
702 FII_MESSAGE.Func_Fail
703 (func_name =>
704 'FII_DIMENSION_MAINTAIN_PKG.Fin_Insert_Imm_Child_Nodes');
705 RAISE;
706
707 WHEN OTHERS Then
708 FII_UTIL.WRITE_LOG( substr(SQLERRM,1,180));
709 FII_MESSAGE.Func_Fail
710 (func_name => 'FII_FIN_CAT_MAINTAIN_PKG.INSERT_IMM_CHILD_NODES');
711 RAISE;
712
713 END INSERT_IMM_CHILD_NODES;
714
715
716 -- **************************************************************************
717 -- This procedure will populate the TMP hierarchy table
718
719 PROCEDURE Flatten_Fin_Dim_Hier (p_vset_id NUMBER, p_root_node VARCHAR2) IS
720
721
722 l_flex_value VARCHAR2(150);
723 p_parent_id NUMBER(15);
724
725 BEGIN
726
727 IF (FIIDIM_Debug) THEN
728 FII_MESSAGE.Func_Ent(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
729 'Flatten_Fin_Dim_Hier');
730 END IF;
731
732 g_phase := 'Truncate table FII_FIN_ITEM_HIER_GT';
733 FII_UTIL.truncate_table ('FII_FIN_ITEM_HIER_GT', 'FII', g_retcode);
734
735 -----------------------------------------------------------------
736
737 FINDIM_parent_node := p_root_node;
738 FINDIM_parent_vset_id := p_vset_id;
739
740 g_phase := 'Get p_parent_id from FND_FLEX_VALUES';
741
742 SELECT flex_value_id INTO p_parent_id
743 FROM FND_FLEX_VALUES
744 WHERE flex_value_set_id = p_vset_id
745 AND flex_value = p_root_node;
746
747 FINDIM_parent_flex_id := p_parent_id;
748
749 -- The following Insert statement inserts the top node self row and
750 -- invokes Ins_Imm_Child_nodes routine to insert all top node mappings
751 -- with in the hierarchy.
752 g_phase := 'invoke Ins_Imm_Child_nodes';
753
754 INSERT_IMM_CHILD_NODES (p_vset_id, p_root_node);
755
756 g_phase := 'insert all self nodes';
757
758 insert into fii_fin_item_hier_gt (
759 parent_level,
760 parent_fin_cat_id,
761 next_level,
762 next_level_fin_cat_id,
763 child_level,
764 child_fin_cat_id,
765 child_flex_value_set_id,
766 parent_flex_value_set_id,
767 next_level_is_leaf,
768 is_leaf_flag)
769 select
770 child_level,
771 child_fin_cat_id,
772 child_level,
773 child_fin_cat_id,
774 child_level,
775 child_fin_cat_id,
776 child_flex_value_set_id,
777 child_flex_value_set_id,
778 'N',
779 'N'
780 from (select distinct child_fin_cat_id,child_level,child_flex_value_set_id from fii_fin_item_hier_gt);
781
782 IF (FIIDIM_Debug) THEN
783 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_fin_item_hier_gt');
784 END IF;
785
786 g_phase := 'Insert self node for the top node';
787
788 INSERT INTO fii_fin_item_hier_gt
789 (parent_level,
790 parent_fin_cat_id,
791 next_level,
792 next_level_fin_cat_id,
793 child_level,
794 child_fin_cat_id,
795 child_flex_value_set_id,
799 VALUES
796 parent_flex_value_set_id,
797 next_level_is_leaf,
798 is_leaf_flag)
800 (1,
801 p_parent_id,
802 1,
803 p_parent_id,
804 1,
805 p_parent_id,
806 p_vset_id,
807 FINDIM_parent_vset_id,
808 'N',
809 'N');
810
811 IF (FIIDIM_Debug) THEN
812 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_fin_item_hier_gt');
813 END IF;
814
815
816 --Call FND_STATS to collect statistics after populating the table
817 g_phase := 'gather_table_stats for FII_FIN_ITEM_HIER_GT';
818
819 FND_STATS.gather_table_stats
820 (ownname => g_schema_name,
821 tabname => 'FII_FIN_ITEM_HIER_GT');
822
823 --====================================================================
824 --Before we proceed to populate the final hierarchy table, we should
825 --check if there are any diamond shapes in the TMP hierarchy table.
826 --If so, we will report the problem, and error out the program
827
828 -- The following block checks for child value multiple assignments
829 -- to different parents within the value sets
830 -- We use (just created) TMP table FII_FIN_ITEM_HIER_GT for this purpose
831 g_phase := 'Call Detect_Diamond_Shape';
832
833 Detect_Diamond_Shape;
834
835 --====================================================================
836
837 --So far, there is no problem...
838
839 --Update the column next_level_is_leaf
840 --We look at those records (P,A,A) in which A is a leaf value
841 -- g_phase := 'Update the column next_level_is_leaf';
842
843 --Per suggestion from performance team,
844 --rewrite the update statement
845 --Update fii_fin_item_hier_gt tab1
846 -- Set next_level_is_leaf = 'Y'
847 -- Where tab1.next_level_fin_cat_id = tab1.child_fin_cat_id
848 -- And 1 = (select count(*)
849 -- from fii_fin_item_hier_gt tab2
850 -- where tab2.parent_fin_cat_id = tab1.next_level_fin_cat_id);
851
852
853 -------------------------------------------------------
854 --Currently , there is no need to update this column for
855 --the full hierarchy since it's not used anywhere
856 -------------------------------------------------------
857
858 -- Note that we use self record (A,A,A) for tab3 here!
859 --Update fii_fin_item_hier_gt tab1
860 -- Set next_level_is_leaf = 'Y'
861 -- Where tab1.next_level_fin_cat_id = tab1.child_fin_cat_id
862 -- and tab1.next_level_fin_cat_id IN (
863 -- select /*+ ordered */ tab3.next_level_fin_cat_id
864 -- from fii_fin_item_hier_gt tab3,
865 -- fii_fin_item_hier_gt tab2
866 -- where tab2.parent_fin_cat_id = tab3.parent_fin_cat_id
867 -- and tab3.parent_fin_cat_id = tab3.child_fin_cat_id
868 -- group by tab3.next_level_fin_cat_id
869 -- having count(*) = 1);
870
871
872 --Update the column is_leaf_flag
873 --We look at all records (A,A,A) in which A is a leaf value
874 -- g_phase := 'Update the column is_leaf_flag';
875
876 -------------------------------------------------------
877 --Currently , there is no need to update this column for
878 --the full hierarchy since it's not used anywhere
879 -------------------------------------------------------
880 --Update fii_fin_item_hier_gt
881 -- Set is_leaf_flag = 'Y'
882 --Where parent_fin_cat_id = next_level_fin_cat_id
883 -- and next_level_fin_cat_id = child_fin_cat_id
884 -- and next_level_is_leaf = 'Y';
885
886
887 IF (FIIDIM_Debug) THEN
888 FII_MESSAGE.Func_Succ(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
889 'Flatten_Fin_Dim_Hier');
890 END IF;
891
892 EXCEPTION
893
894 WHEN NO_DATA_FOUND THEN
895 FII_UTIL.Write_Log('Flatten_Fin_Dim_Hier: No Data Found');
896 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
897 'Flatten_Fin_Dim_Hier');
898 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
899 raise;
900
901 WhEN FINDIM_MULT_PAR_err THEN
902 FII_UTIL.WRITE_LOG ('Flatten_Fin_Dim_Hier: Diamond Shape Detected');
903 FII_MESSAGE.Func_Fail (func_name =>
904 'FII_DIMENSION_MAINTAIN_PKG.Flatten_Fin_Dim_Hier');
905 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
906 raise;
907
908 WHEN OTHERS THEN
909 FII_UTIL.Write_Log('Flatten_Fin_Dim_Hier: '|| substr(sqlerrm,1,180));
910 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
911 'Flatten_Fin_Dim_Hier');
912 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
913 raise;
914
915 END Flatten_Fin_Dim_Hier;
916
917 -- **************************************************************************
921
918 -- Update next_level_is_leaf and is_leaf_flag in FII_FIN_ITEM_HIER_GT
919
920 PROCEDURE Update_GT IS
922 Begin
923
924 IF (FIIDIM_Debug) THEN
925 FII_MESSAGE.Func_Ent(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
926 'Update_GT');
927 END IF;
928
929 --Update the columns next_level_is_leaf, is_leaf_flag
930 --for the latest FII_FIN_ITEM_HIER_GT
931 g_phase := 'Update next_level_is_leaf, is_leaf_flag';
932
933 --Update the column next_level_is_leaf
934 --We look at those records (P,A,A) in which A is a leaf value
935
936 --Note that we use self record (A,A,A) for tab3 here!
937 Update fii_fin_item_hier_gt tab1
938 Set next_level_is_leaf = 'Y'
939 Where tab1.next_level_fin_cat_id = tab1.child_fin_cat_id
940 and tab1.next_level_fin_cat_id IN (
941 select /*+ ordered */ tab3.next_level_fin_cat_id
942 from fii_fin_item_hier_gt tab3,
943 fii_fin_item_hier_gt tab2
944 where tab2.parent_fin_cat_id = tab3.parent_fin_cat_id
945 and tab3.parent_fin_cat_id = tab3.child_fin_cat_id
946 group by tab3.next_level_fin_cat_id
947 having count(*) = 1);
948
949 IF (FIIDIM_Debug) THEN
950 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_fin_item_hier_gt');
951 END IF;
952
953 g_phase := 'Update is_leaf_flag';
954
955 --Update the column is_leaf_flag
956 --We look at all records (A,A,A) in which A is a leaf value
957 Update fii_fin_item_hier_gt
958 Set is_leaf_flag = 'Y'
959 Where parent_fin_cat_id = next_level_fin_cat_id
960 and next_level_fin_cat_id = child_fin_cat_id
961 and next_level_is_leaf = 'Y';
962
963 IF (FIIDIM_Debug) THEN
964 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_fin_item_hier_gt');
965 END IF;
966
967 IF (FIIDIM_Debug) THEN
968 FII_MESSAGE.Func_Succ(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
969 'Update_GT');
970 END IF;
971
972 EXCEPTION
973
974 WHEN OTHERS THEN
975 FII_UTIL.Write_Log ('Update_GT -> phase: '|| g_phase);
976 FII_UTIL.Write_Log ('Update_GT: '|| substr(sqlerrm,1,180));
977 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
978 'Update_GT');
979 raise;
980
981 END Update_GT;
982
983
984 --**********************************************************************************************
985 PROCEDURE Get_level_populated IS
986
987 -- For BI - 2006
988 CURSOR pre_dep_cur IS SELECT * FROM
989 ( -- normalized parent-child relationship (one-level)
990 select parent_fin_cat_id pid
991 , child_fin_cat_id cid
992 , child_level clv
993 , child_flex_value_set_id cvs
994 , is_leaf_flag clf
995 from fii_fin_item_hier_gt
996 where parent_level + 1 = child_level
997 --and child_flex_value_set_id = G_MASTER_VALUE_SET_ID
998 union all
999 select null, G_TOP_NODE_ID, 1, G_MASTER_VALUE_SET_ID, 'N'
1000 from dual
1001 )
1002 START WITH pid is NULL
1003 CONNECT BY pid = PRIOR cid
1004 ORDER siblings BY cid;
1005
1006 -- For BI - 2006
1007 TYPE stack_type IS VARRAY( 128 ) OF pre_dep_cur%ROWTYPE;
1008
1009 r_stack stack_type := stack_type(); -- the stack
1010 c_top number; -- index of the top element of the stack (child level)
1011
1012 n_top number; -- next level (parent level is p_top defined in the body)
1013 --p_top1 number;
1014 BEGIN
1015 r_stack.extend( 128 );
1016
1017 ----------------------------------------------------------------------
1018 -- We want to update the newly introduced level columns for BI - 2006
1019 ----------------------------------------------------------------------
1020 FOR pre_dep_rec IN pre_dep_cur LOOP
1021 -- put (pop/push) the new child value on the stack
1022 c_top := pre_dep_rec.clv;
1023 r_stack( c_top ) := pre_dep_rec;
1024 -- loop through the stack for all the parents
1025 FOR p_top IN 1..c_top LOOP
1026 -- figure out the next level
1027 IF p_top = c_top THEN
1028 n_top := p_top;
1029 ELSE
1030 n_top := p_top + 1;
1031 END IF;
1032
1033 FII_UTIL.Write_Log('Updating for parent and child : ' || r_stack( p_top ).cid || ' and ' || r_stack( c_top ).cid );
1034
1035 update fii_fin_item_hier_gt
1036 set LEVEL2_fin_cat_ID = r_stack( least( p_top + 1, c_top) ).cid
1037 , LEVEL3_fin_cat_ID = r_stack( least( p_top + 2, c_top ) ).cid
1038 , LEVEL4_fin_cat_ID = r_stack( least( p_top + 3, c_top ) ).cid
1039 , LEVEL5_fin_cat_ID = r_stack( least( p_top + 4, c_top ) ).cid
1040 where parent_fin_cat_id = r_stack( p_top ).cid
1041 and child_fin_cat_id = r_stack( c_top ).cid;
1042
1043 END LOOP;
1044
1048
1045 END LOOP;
1046
1047 END Get_level_populated;
1049 -- **************************************************************************
1050 -- Delete the LVS records from FII_FIN_ITEM_HIER_GT table
1051
1052 PROCEDURE Delete_LVS_Records IS
1053
1054 Begin
1055
1056 IF (FIIDIM_Debug) THEN
1057 FII_MESSAGE.Func_Ent(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
1058 'Delete_LVS_Records');
1059 END IF;
1060
1061 --Delete from FII_FIN_ITEM_HIER_GT for child value set not equal to
1062 --the master value set
1063 g_phase := 'Delete FII_FIN_ITEM_HIER_GT ';
1064
1065 Delete from FII_FIN_ITEM_HIER_GT
1066 Where child_flex_value_set_id <> G_MASTER_VALUE_SET_ID;
1067
1068
1069 IF (FIIDIM_Debug) THEN
1070 FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows in fii_fin_item_hier_gt');
1071 END IF;
1072
1073 Get_level_populated ;
1074
1075 --Update FII_FIN_ITEM_HIER_GT for pruned hierarchy table for Expense Analysis
1076 g_phase := 'Update FII_FIN_ITEM_HIER_GT for pruned hierarchy table';
1077
1078 Update_Gt;
1079
1080 IF (FIIDIM_Debug) THEN
1081 FII_MESSAGE.Func_Succ(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
1082 'Delete_LVS_Records');
1083 END IF;
1084
1085
1086 EXCEPTION
1087
1088 WHEN OTHERS THEN
1089 FII_UTIL.Write_Log ('Delete_LVS_Records -> phase: '|| g_phase);
1090 FII_UTIL.Write_Log ('Delete_LVS_Records: '|| substr(sqlerrm,1,180));
1091 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
1092 'Delete_LVS_Records');
1093 raise;
1094
1095 END Delete_LVS_Records;
1096
1097
1098 -- **************************************************************************
1099 -- Populate FII_FIN_CAT_MAPPINGS_GT Table for FIN_CAT_MAPPINGS
1100
1101 PROCEDURE Get_FC_Mapping_GT IS
1102
1103 Begin
1104
1105 IF (FIIDIM_Debug) THEN
1106 FII_MESSAGE.Func_Ent(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
1107 'Get_FC_Mapping_GT');
1108 END IF;
1109
1110 --First, populate FII_FIN_CAT_MAPPINGS_GT with the truncated portion
1111 --of the financial category hierarchy.
1112 --Note this already includes all self leaf records
1113 g_phase := 'populate FII_FIN_CAT_MAPPINGS_GT with truncated portion';
1114
1115 INSERT INTO FII_FIN_CAT_MAPPINGS_GT
1116 (parent_fin_cat_id,
1117 child_fin_cat_id)
1118 SELECT fh.parent_fin_cat_id,
1119 fh.child_fin_cat_id
1120 FROM FII_FULL_FIN_ITEM_HIERS fh
1121 WHERE fh.parent_fin_cat_id IN
1122 (SELECT ph.parent_fin_cat_id
1123 FROM FII_FIN_ITEM_HIERARCHIES ph
1124 WHERE ph.is_leaf_flag = 'Y');
1125
1126 IF (FIIDIM_Debug) THEN
1127 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_MAPPINGS_GT');
1128 END IF;
1129
1130 --Then, insert self-mapping records for all nodes in pruned hierarchy
1131 --FII_FIN_ITEM_HIERARCHIES. Note we should exclude all self leaf
1132 --records since they are inserted in the previous step.
1133 g_phase := 'insert self-mapping records for all nodes in pruned hierarchy';
1134
1135 INSERT INTO FII_FIN_CAT_MAPPINGS_GT
1136 (parent_fin_cat_id,
1137 child_fin_cat_id)
1138 SELECT parent_fin_cat_id,
1139 child_fin_cat_id
1140 FROM FII_FIN_ITEM_HIERARCHIES
1141 WHERE child_flex_value_set_id = G_MASTER_VALUE_SET_ID
1142 AND parent_fin_cat_id = child_fin_cat_id
1143 AND is_leaf_flag = 'N';
1144
1145 IF (FIIDIM_Debug) THEN
1146 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_MAPPINGS_GT');
1147 END IF;
1148
1149 --Call FND_STATS to collect statistics after populating the table
1150 g_phase := 'gather_table_stats for FII_FIN_CAT_MAPPINGS_GT';
1151
1152 FND_STATS.gather_table_stats
1153 (ownname => g_schema_name,
1154 tabname => 'FII_FIN_CAT_MAPPINGS_GT');
1155
1156 IF (FIIDIM_Debug) THEN
1157 FII_MESSAGE.Func_Succ(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Get_FC_Mapping_GT');
1158 END IF;
1159
1160 EXCEPTION
1161
1162 WHEN OTHERS THEN
1163 FII_UTIL.Write_Log ('Get_FC_Mapping_GT -> phase: '|| g_phase);
1164 FII_UTIL.Write_Log ('Get_FC_Mapping_GT: '|| substr(sqlerrm,1,180));
1165 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
1166 'Get_FC_Mapping_GT');
1167 raise;
1168
1169 END Get_FC_Mapping_GT;
1170
1171 -- **************************************************************************
1172 -- This procedure will check FC Type assignment using 2 Business Rules:
1173 -- 1. A node can not be assigned both Revenue (R)
1174 -- and another expense (i.e. OE, TE, PE)
1175 -- 2. A node can not be assigned both Revenue (R)
1176 -- and Cost of Good Sold (CGS)
1177 -- A new business rule for DR is checked:
1178 -- 3. Financial categories assigned type DR cannot have any other type
1179 -- assigned.
1180
1181 PROCEDURE Check_rules_denorm IS
1185 select FIN_CATEGORY_ID
1182
1183 -- Bug 4152798. Removed literal and introduced a parameter.
1184 Cursor rev_nodes_cur (p_cat_type VARCHAR2) IS
1186 from FII_FC_TYPE_ASSGNS_GT
1187 where FIN_CAT_TYPE_CODE = p_cat_type;
1188
1189 Cursor Invalid_Asg_Cur (p_rev_cat_id NUMBER) IS
1190 select FIN_CATEGORY_ID, FIN_CAT_TYPE_CODE
1191 from FII_FC_TYPE_ASSGNS_GT
1192 where FIN_CATEGORY_ID = p_rev_cat_id
1193 and FIN_CAT_TYPE_CODE in ('OE', 'TE', 'PE', 'CGS');
1194
1195 -- Bug 4152798. Removed the cursor as this cursor and rev_nodes_cur cursor
1196 -- are same after removal of the literal.
1197 -- 2 cursors to check business rule for DR
1198 -- Cursor def_rev_nodes_cur IS
1199 -- select FIN_CATEGORY_ID
1200 -- from FII_FC_TYPE_ASSGNS_GT
1201 -- where FIN_CAT_TYPE_CODE = 'DR';
1202
1203 Cursor Conflict_Asg_Cur (p_rev_cat_id NUMBER, p_cat_type VARCHAR2) IS
1204 select FIN_CATEGORY_ID, FIN_CAT_TYPE_CODE
1205 from FII_FC_TYPE_ASSGNS_GT
1206 where FIN_CATEGORY_ID = p_rev_cat_id
1207 and FIN_CAT_TYPE_CODE <> p_cat_type;
1208
1209 l_rev_cat_id NUMBER(15);
1210 l_cat_value VARCHAR2(60);
1211 l_count NUMBER(15):=0;
1212 l_count_2 NUMBER(15):=0;
1213
1214 Begin
1215
1216 IF (FIIDIM_Debug) THEN
1217 FII_MESSAGE.Func_Ent (func_name =>
1218 'FII_FIN_CAT_MAINTAIN_PKG.check_rules_denorm');
1219 END IF;
1220
1221 g_phase := 'check rules for Revenue';
1222
1223 For rev_node_rec IN rev_nodes_cur('R') LOOP
1224 l_rev_cat_id := rev_node_rec.FIN_CATEGORY_ID;
1225
1226 For bad_asg_rec IN Invalid_Asg_Cur (l_rev_cat_id) LOOP
1227 l_count := l_count + 1;
1228 if l_count = 1 then
1229
1230 FII_MESSAGE.write_log(msg_name => 'FII_INVALID_FCT_ASGN',
1231 token_num => 0);
1232 FII_MESSAGE.write_log(msg_name => 'FII_REFER_TO_OUTPUT',
1233 token_num => 0);
1234
1235 FII_MESSAGE.write_output (msg_name => 'FII_INVALID_FCT_ASGN',
1236 token_num => 0);
1237 FII_MESSAGE.write_output (msg_name => 'FII_INVALID_FCT_TAB',
1238 token_num => 0);
1239
1240 end if;
1241
1242 --bug 3263273: should print out flex value
1243 l_cat_value := Get_Flex_Value (bad_asg_rec.FIN_CATEGORY_ID);
1244
1245 FII_UTIL.Write_Output (l_cat_value || ' , ' ||
1246 bad_asg_rec.FIN_CAT_TYPE_CODE);
1247 End Loop;
1248
1249 END LOOP;
1250
1251 -- check business rule for DR
1252
1253 g_phase := 'check rules for Deferred Revenue';
1254
1255 For def_rev_node_rec IN rev_nodes_cur('DR') LOOP
1256 l_rev_cat_id := def_rev_node_rec.FIN_CATEGORY_ID;
1257
1258 For bad_asg_rec IN Conflict_Asg_Cur (l_rev_cat_id, 'DR') LOOP
1259 l_count_2 := l_count_2 + 1;
1260 if l_count_2 = 1 then
1261
1262 FII_MESSAGE.write_log(msg_name => 'FII_CONFLICT_DR_ASGN',
1263 token_num => 0);
1264 FII_MESSAGE.write_log(msg_name => 'FII_REFER_TO_OUTPUT',
1265 token_num => 0);
1266
1267 FII_MESSAGE.write_output (msg_name => 'FII_CONFLICT_DR_ASGN',
1268 token_num => 0);
1269 FII_MESSAGE.write_output (msg_name => 'FII_CONFLICT_DR_TAB',
1270 token_num => 0);
1271
1272 end if;
1273
1274 l_cat_value := Get_Flex_Value (bad_asg_rec.FIN_CATEGORY_ID);
1275
1276 FII_UTIL.Write_Output (l_cat_value || ' , ' ||
1277 bad_asg_rec.FIN_CAT_TYPE_CODE);
1278 End Loop;
1279
1280 END LOOP;
1281
1282 IF ( l_count > 0 OR l_count_2 > 0 ) then
1283 RAISE FINDIM_Invalid_FC_ASG_err;
1284 END IF;
1285
1286 Exception
1287
1288 When FINDIM_Invalid_FC_ASG_err then
1289 FII_UTIL.Write_Log('FII_FIN_CAT_MAINTAIN_PKG.check_rules_denorm: '||
1290 'invalid FC Type assignment detected!');
1291 raise;
1292
1293 When others then
1294 FII_UTIL.Write_Log(
1295 'Unexpected error check_rules_denorm');
1296 FII_UTIL.WRITE_LOG ( 'Error Message: '|| substr(sqlerrm,1,180));
1297 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
1298 RAISE;
1299
1300 END Check_rules_denorm;
1301
1302
1303 -- **************************************************************************
1304 -- Populate the table FII_FIN_CAT_TYPE_ASSGNS from FII_FC_TYPE_NORM_ASSIGN
1305 -- by traveraling the dimension hierarchy table
1306
1307 Procedure Populate_FCT_denorm (p_initial_load VARCHAR2) IS
1308
1309 l_sql_rowcount number;
1310
1311 Begin
1312
1313 IF (FIIDIM_Debug) THEN
1314 FII_MESSAGE.Func_Ent(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Populate_FTC_denorm');
1315 END IF;
1316
1317 --clean up the denorm TMP table
1318 g_phase := 'Truncate FII_FC_TYPE_ASSGNS_GT';
1319
1323 --Note that we need to use DISTINCT here since both parent-child
1320 FII_UTIL.truncate_table ('FII_FC_TYPE_ASSGNS_GT', 'FII', g_retcode);
1321
1322 --First, insert records into the denorm TMP table
1324 --can be assigned to same type in FII_FC_TYPE_NORM_ASSIGN
1325 g_phase := 'insert records into the denorm TMP table';
1326
1327 Insert into FII_FC_TYPE_ASSGNS_GT
1328 (fin_cat_type_code,
1329 fin_category_id,
1330 top_node_flag)
1331 select distinct
1332 fcn.fin_cat_type_code,
1333 hier.child_fin_cat_id,
1334 'N'
1335 from FII_FC_TYPE_NORM_ASSIGN fcn,
1336 FII_FULL_FIN_ITEM_HIERS hier
1337 where fcn.fin_category_id = hier.parent_fin_cat_id;
1338
1339 l_sql_rowcount := SQL%ROWCOUNT;
1340
1341 IF (FIIDIM_Debug) THEN
1342 FII_UTIL.Write_Log('Inserted ' || l_sql_rowcount || ' rows into FII_FC_TYPE_ASSGNS_GT');
1343 END IF;
1344
1345 --Check if there is any Financial Category type assignment in the system
1346 g_phase := 'Check if there is any Financial Category type assignment';
1347
1348 if l_sql_rowcount = 0 then
1349 FII_UTIL.truncate_table ('FII_FIN_CAT_TYPE_ASSGNS', 'FII', g_retcode);
1350 raise FINDIM_NO_FC_TYPE_ASGN;
1351 end if;
1352
1353 --Insert a new internal type (EXP) that contains a distinct list
1354 --of accounts from the 4 expense categories (TE, OE, PE, CGS)
1355 --...................................................................
1356 --BACKGROUND FOR THIS CHANGE: we validate that none of the accounts
1357 --tagged as Revenue (R) can be tagged with a different type,
1358 --so that list is unique. We can however have multiple assignments for
1359 --for a single node between the expense categories (TE, CGS, OE, PE).
1360 --So we create this new internal type (EXP) that contains a distinct
1361 --list of accounts from these 4 expense categories.
1362 --The MVs will now join to this table and pick data for types R and EXP.
1363 --We would not need to add a col for sign since we know R is +ve and
1364 --EXP is -ve. This should resolve duplication as well as eliminate data
1365 --for other account type.
1366 --....................................................................
1367
1368 g_phase := 'Insert a new internal type (EXP)';
1369
1370 Insert into FII_FC_TYPE_ASSGNS_GT
1371 (fin_cat_type_code,
1372 fin_category_id,
1373 top_node_flag)
1374 select distinct
1375 'EXP',
1376 fct.fin_category_id,
1377 'N'
1378 from FII_FC_TYPE_ASSGNS_GT fct
1379 where fct.fin_cat_type_code IN ('OE', 'TE', 'PE', 'CGS');
1380
1381 IF (FIIDIM_Debug) THEN
1382 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FC_TYPE_ASSGNS_GT');
1383 END IF;
1384
1385 --Call FND_STATS to collect statistics after populating the table
1386 g_phase := 'gather_table_stats for FII_FC_TYPE_ASSGNS_GT';
1387
1388 FND_STATS.gather_table_stats
1389 (ownname => g_schema_name,
1390 tabname => 'FII_FC_TYPE_ASSGNS_GT');
1391
1392 --Now to update the column TOP_NODE_FLAG
1393 --For a node with certain fin cat type, look at all nodes in the
1394 --hierarchy that have the node as the child. If there is only one
1395 --(i.e. the self node), then this node with the fin cat type is
1396 --the top node.
1397 g_phase := 'update the column TOP_NODE_FLAG';
1398
1399 --Per suggestion from performance team,
1400 --rewrite the update statement
1401 --UPDATE FII_FC_TYPE_ASSGNS_GT tab1
1402 -- SET tab1.TOP_NODE_FLAG = 'Y'
1403 -- WHERE 1 = (select count(*)
1404 -- from FII_FC_TYPE_ASSGNS_GT tab2,
1405 -- FII_FULL_FIN_ITEM_HIERS hier
1406 -- where tab2.fin_cat_type_code = tab1.fin_cat_type_code
1407 -- and hier.child_fin_cat_id = tab1.fin_category_id
1408 -- and hier.parent_fin_cat_id = tab2.fin_category_id );
1409
1410 UPDATE FII_FC_TYPE_ASSGNS_GT tab1
1411 SET tab1.TOP_NODE_FLAG = 'Y'
1412 WHERE (tab1.fin_cat_type_code,tab1.fin_category_id) IN
1413 (select /*+ ordered parallel(hier) */
1414 tab3.fin_cat_type_code,tab3.fin_category_id
1415 from FII_FC_TYPE_ASSGNS_GT tab3,
1416 FII_FULL_FIN_ITEM_HIERS hier,
1417 FII_FC_TYPE_ASSGNS_GT tab2
1418 where tab2.fin_cat_type_code = tab3.fin_cat_type_code
1419 and hier.child_fin_cat_id = tab3.fin_category_id
1420 and hier.parent_fin_cat_id = tab2.fin_category_id
1421 group by tab3.fin_cat_type_code,
1422 tab3.fin_category_id
1423 having count(*) = 1);
1424
1425 IF (FIIDIM_Debug) THEN
1426 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_FC_TYPE_ASSGNS_GT');
1427 END IF;
1428
1429 --Validate the denorm table by 2 business rules
1433
1430 g_phase := 'Validate the denorm table by 2 business rules';
1431
1432 Check_rules_denorm;
1434 -- Write the TMP table to the final denorm table based on the load mode
1435 g_phase := 'Write TMP table to final denorm table based on load mode';
1436
1437 IF p_initial_load = 'Y' THEN --initial load
1438
1439 g_phase := 'truncate FII_FIN_CAT_TYPE_ASSGNS';
1440
1441 FII_UTIL.truncate_table ('FII_FIN_CAT_TYPE_ASSGNS', 'FII', g_retcode);
1442
1443 g_phase := 'INSERT INTO FII_FIN_CAT_TYPE_ASSGNS';
1444
1445 INSERT /*+ APPEND */ INTO FII_FIN_CAT_TYPE_ASSGNS
1446 (fin_cat_type_code,
1447 fin_category_id,
1448 top_node_flag,
1449 creation_date,
1450 created_by,
1451 last_update_date,
1452 last_updated_by,
1453 last_update_login)
1454 SELECT fin_cat_type_code,
1455 fin_category_id,
1456 top_node_flag,
1457 SYSDATE,
1458 FII_USER_ID,
1459 SYSDATE,
1460 FII_USER_ID,
1461 FII_LOGIN_ID
1462 FROM FII_FC_TYPE_ASSGNS_GT;
1463
1464 IF (FIIDIM_Debug) THEN
1465 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_TYPE_ASSGNS');
1466 END IF;
1467
1468 ELSE --incremental update
1469
1470 g_phase := 'DELETE FROM FII_FIN_CAT_TYPE_ASSGNS';
1471
1472 DELETE FROM FII_FIN_CAT_TYPE_ASSGNS
1473 WHERE (fin_cat_type_code, fin_category_id, top_node_flag) IN
1474 (SELECT fin_cat_type_code, fin_category_id, top_node_flag
1475 FROM FII_FIN_CAT_TYPE_ASSGNS
1476 MINUS
1477 SELECT fin_cat_type_code, fin_category_id, top_node_flag
1478 FROM FII_FC_TYPE_ASSGNS_GT);
1479
1480 IF (FIIDIM_Debug) THEN
1481 FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_FIN_CAT_TYPE_ASSGNS');
1482 END IF;
1483
1484 g_phase := 'Insert into FII_FIN_CAT_TYPE_ASSGNS';
1485
1486 Insert into FII_FIN_CAT_TYPE_ASSGNS(
1487 fin_cat_type_code,
1488 fin_category_id,
1489 top_node_flag,
1490 creation_date,
1491 created_by,
1492 last_update_date,
1493 last_updated_by,
1494 last_update_login)
1495 (SELECT
1496 fin_cat_type_code,
1497 fin_category_id,
1498 top_node_flag,
1499 SYSDATE,
1500 FII_USER_ID,
1501 SYSDATE,
1502 FII_USER_ID,
1503 FII_LOGIN_ID
1504 FROM FII_FC_TYPE_ASSGNS_GT
1505 MINUS
1506 SELECT
1507 fin_cat_type_code,
1508 fin_category_id,
1509 top_node_flag,
1510 SYSDATE,
1511 FII_USER_ID,
1512 SYSDATE,
1513 FII_USER_ID,
1514 FII_LOGIN_ID
1515 FROM FII_FIN_CAT_TYPE_ASSGNS);
1516
1517 IF (FIIDIM_Debug) THEN
1518 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_TYPE_ASSGNS');
1519 END IF;
1520
1521 END IF;
1522 -----------------------------------------
1523
1524 --Call FND_STATS to collect statistics after re-populating the tables.
1525 g_phase := 'gather_table_stats for FII_FIN_CAT_TYPE_ASSGNS';
1526
1527 FND_STATS.gather_table_stats
1528 (ownname => g_schema_name,
1529 tabname => 'FII_FIN_CAT_TYPE_ASSGNS');
1530
1531 IF p_initial_load = 'Y' THEN
1532
1533 g_phase := 'gather_table_stats for MLOG$_FII_FIN_CAT_TYPE_ASS';
1534
1535 FND_STATS.gather_table_stats
1536 (ownname => g_schema_name,
1537 tabname => 'MLOG$_FII_FIN_CAT_TYPE_ASS');
1538 END IF;
1539
1540 IF (FIIDIM_Debug) THEN
1541 FII_MESSAGE.Func_Succ(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
1542 'Populate_FCT_denorm');
1543 END IF;
1544
1545 EXCEPTION
1546
1547 WHEN NO_DATA_FOUND THEN
1548 FII_UTIL.Write_Log('Populate_FCT_denorm : No Data Found');
1549 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
1550 'Populate_FCT_denorm');
1551 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
1552 raise;
1553
1554 WHEN FINDIM_NO_FC_TYPE_ASGN THEN
1555 FII_MESSAGE.write_log( msg_name => 'FII_NO_FC_TYPE_ASGN',
1556 token_num => 0);
1557 FII_MESSAGE.write_output(msg_name => 'FII_NO_FC_TYPE_ASGN',
1558 token_num => 0);
1559 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
1560 'Populate_FCT_denorm');
1561 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
1562 raise;
1563
1564 WHEN OTHERS THEN
1565 FII_UTIL.Write_Log('Populate_FCT_denorm: '|| substr(sqlerrm,1,180));
1566 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
1567 'Populate_FCT_denorm');
1568 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
1572
1569 raise;
1570
1571 END Populate_FCT_denorm;
1573 -- **************************************************************************
1574 -- If the FIN ITEM dimension is not enabled, truncate the tables and exit.
1575 --
1576
1577 PROCEDURE Handle_Unenabled_DIM IS
1578
1579 Begin
1580
1581 IF (FIIDIM_Debug) THEN
1582 FII_MESSAGE.Func_Ent(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
1583 'Handle_Unenabled_DIM');
1584 END IF;
1585
1586 FII_UTIL.truncate_table ('FII_FULL_FIN_ITEM_HIERS', 'FII', g_retcode);
1587 FII_UTIL.truncate_table ('FII_FIN_ITEM_HIERARCHIES', 'FII', g_retcode);
1588 FII_UTIL.truncate_table ('FII_FIN_CAT_MAPPINGS', 'FII', g_retcode);
1589 FII_UTIL.truncate_table ('FII_FIN_CAT_TYPE_ASSGNS', 'FII', g_retcode);
1590 FII_UTIL.truncate_table ('FII_FIN_ITEM_LEAF_HIERS', 'FII', g_retcode);
1591 FII_UTIL.truncate_table ('FII_FIN_CAT_LEAF_MAPS', 'FII', g_retcode);
1592
1593 IF (FIIDIM_Debug) THEN
1594 FII_MESSAGE.Func_Succ(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
1595 'Handle_Unenabled_DIM');
1596 END IF;
1597
1598 EXCEPTION
1599
1600 WHEN OTHERS THEN
1601 FII_UTIL.Write_Log ('Handle_Unenabled_DIM: '|| substr(sqlerrm,1,180));
1602 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
1603 'Handle_Unenabled_DIM');
1604 raise;
1605
1606 END Handle_Unenabled_DIM;
1607
1608 -- **************************************************************************
1609 -- This is the main procedure of FC dimension program (initial populate).
1610
1611 PROCEDURE Init_Load (errbuf OUT NOCOPY VARCHAR2,
1612 retcode OUT NOCOPY VARCHAR2) IS
1613
1614 ret_val BOOLEAN := FALSE;
1615
1616 BEGIN
1617
1618 IF (FIIDIM_Debug) THEN
1619 FII_MESSAGE.Func_Ent(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Init_Load');
1620 END IF;
1621
1622 --First do the initialization
1623
1624 g_phase := 'Call Initialize';
1625
1626 Initialize;
1627
1628
1629 --Secondly populate the table FII_DIM_NORM_HIER_GT
1630
1631 g_phase := 'Call Get_NORM_HIERARCHY_TMP';
1632
1633 Get_NORM_HIERARCHY_TMP;
1634
1635
1636 --Call the Flatten financial item dimension hierarchy routine to
1637 --insert all mappings.
1638
1639 g_phase := 'Call Flatten_Fin_Dim_Hier';
1640
1641 Flatten_Fin_Dim_Hier (G_MASTER_VALUE_SET_ID, G_TOP_NODE_VALUE);
1642
1643 --Copy TMP hierarchy table to the final dimension table
1644 g_phase := 'Copy TMP hierarchy table to the final full dimension table';
1645
1646 FII_UTIL.truncate_table ('FII_FULL_FIN_ITEM_HIERS', 'FII', g_retcode);
1647
1648 INSERT /*+ APPEND */ INTO FII_FULL_FIN_ITEM_HIERS (
1649 parent_level,
1650 parent_fin_cat_id,
1651 next_level,
1652 next_level_fin_cat_id,
1653 next_level_is_leaf,
1654 is_leaf_flag,
1655 child_level,
1656 child_fin_cat_id,
1657 parent_flex_value_set_id,
1658 child_flex_value_set_id,
1659 creation_date,
1660 created_by,
1661 last_update_date,
1662 last_updated_by,
1663 last_update_login)
1664 SELECT
1665 parent_level,
1666 parent_fin_cat_id,
1667 next_level,
1668 next_level_fin_cat_id,
1669 next_level_is_leaf,
1670 is_leaf_flag,
1671 child_level,
1672 child_fin_cat_id,
1673 parent_flex_value_set_id,
1674 child_flex_value_set_id,
1675 SYSDATE,
1676 FII_USER_ID,
1677 SYSDATE,
1678 FII_USER_ID,
1679 FII_LOGIN_ID
1680 FROM FII_FIN_ITEM_HIER_GT;
1681
1682 IF (FIIDIM_Debug) THEN
1683 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FULL_FIN_ITEM_HIERS');
1684 END IF;
1685
1686 commit;
1687
1688 --Call FND_STATS to collect statistics after re-populating the tables.
1689 --for the full table since it will be used in the program later
1690
1691 g_phase := 'gather_table_stats for FII_FULL_FIN_ITEM_HIERS';
1692
1693 FND_STATS.gather_table_stats
1694 (ownname => g_schema_name,
1695 tabname => 'FII_FULL_FIN_ITEM_HIERS');
1696
1697 --==============================================================--
1698
1699 --Delete LVS records from FII_FIN_ITEM_HIER_GT for pruned hierarchy table for Expense Analysis
1700 g_phase := 'Delete LVS records from FII_FIN_ITEM_HIER_GT for pruned hierarchy table for Expense Analysis';
1701
1702 Delete_LVS_Records;
1703
1704 --Copy TMP hierarchy table to the final dimension table for Expense Analysis
1705 g_phase := 'Copy TMP hierarchy table to the final dimension table for Expense Analysis';
1706
1707 FII_UTIL.truncate_table ('FII_FIN_ITEM_LEAF_HIERS', 'FII', g_retcode);
1708
1709 INSERT /*+ APPEND */ INTO FII_FIN_ITEM_LEAF_HIERS (
1710 parent_level,
1711 parent_fin_cat_id,
1712 next_level,
1713 next_level_fin_cat_id,
1714 next_level_is_leaf_flag,
1715 is_leaf_flag,
1716 child_level,
1720 next_level_fin_cat_sort_order,
1717 child_fin_cat_id,
1718 parent_flex_value_set_id,
1719 child_flex_value_set_id,
1721 aggregate_next_level_flag,
1722 LEVEL2_fin_cat_ID,
1723 LEVEL3_fin_cat_ID,
1724 LEVEL4_fin_cat_ID,
1725 LEVEL5_fin_cat_ID,
1726 creation_date,
1727 created_by,
1728 last_update_date,
1729 last_updated_by,
1730 last_update_login,
1731 is_to_be_rolled_up_flag)
1732 SELECT
1733 parent_level,
1734 parent_fin_cat_id,
1735 next_level,
1736 next_level_fin_cat_id,
1737 next_level_is_leaf,
1738 is_leaf_flag,
1739 child_level,
1740 child_fin_cat_id,
1741 parent_flex_value_set_id,
1742 child_flex_value_set_id,
1743 next_level_fin_cat_sort_order,
1744 'N',
1745 LEVEL2_fin_cat_ID,
1746 LEVEL3_fin_cat_ID,
1747 LEVEL4_fin_cat_ID,
1748 LEVEL5_fin_cat_ID,
1749 SYSDATE,
1750 FII_USER_ID,
1751 SYSDATE,
1752 FII_USER_ID,
1753 FII_LOGIN_ID,
1754 'N'
1755 FROM FII_FIN_ITEM_HIER_GT;
1756
1757 IF (FIIDIM_Debug) THEN
1758 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');
1759 END IF;
1760
1761 commit;
1762
1763 -- Since leaf nodes are always included we copy FII_FIN_ITEM_HIER_GT to FII_FIN_ITEM_HIERARCHIES
1764 --Copy FII_FIN_ITEM_HIER_GT hierarchy table to the final dimension table for DBI6.0
1765 g_phase := 'Copy FII_FIN_ITEM_HIER_GT hierarchy table to the final full dimension table for DBI 6.0';
1766
1767 FII_UTIL.truncate_table ('FII_FIN_ITEM_HIERARCHIES', 'FII', g_retcode);
1768
1769 INSERT /*+ APPEND */ INTO FII_FIN_ITEM_HIERARCHIES (
1770 parent_level,
1771 parent_fin_cat_id,
1772 next_level,
1773 next_level_fin_cat_id,
1774 next_level_is_leaf,
1775 is_leaf_flag,
1776 child_level,
1777 child_fin_cat_id,
1778 parent_flex_value_set_id,
1779 child_flex_value_set_id,
1780 creation_date,
1781 created_by,
1782 last_update_date,
1783 last_updated_by,
1784 last_update_login)
1785 SELECT
1786 parent_level,
1787 parent_fin_cat_id,
1788 next_level,
1789 next_level_fin_cat_id,
1790 next_level_is_leaf,
1791 is_leaf_flag,
1792 child_level,
1793 child_fin_cat_id,
1794 parent_flex_value_set_id,
1795 child_flex_value_set_id,
1796 SYSDATE,
1797 FII_USER_ID,
1798 SYSDATE,
1799 FII_USER_ID,
1800 FII_LOGIN_ID
1801 FROM FII_FIN_ITEM_HIER_GT;
1802
1803 IF (FIIDIM_Debug) THEN
1804 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_HIERARCHIES');
1805 END IF;
1806
1807 -- We have added an update statement on FII_FIN_ITEM_LEAF_HIERS. Hence, moved gathering statistics
1808 -- for FII_FIN_ITEM_LEAF_HIERS table and its mlog at the end of procedure.
1809
1810 --Call FND_STATS to collect statistics after re-populating the tables.
1811 --Will seed this in RSG
1812 -- FND_STATS.gather_table_stats
1813 -- (ownname => g_schema_name,
1814 -- tabname => 'FII_FIN_ITEM_HIERARCHIES');
1815
1816 --to avoid ORA-12838: cannot read/modify an object after modifying
1817 --it in parallel (due to the hint APPEND)
1818 commit;
1819
1820 --================================================================--
1821 --Populate FII_FIN_CAT_MAPPINGS table
1822 g_phase := 'Populate FII_FIN_CAT_MAPPINGS_GT table';
1823
1824 Get_FC_Mapping_GT;
1825
1826 --Copy FII_FIN_CAT_MAPPINGS_GT to FII_FIN_CAT_LEAF_MAPS
1827 g_phase := 'Copy TMP FC Mapping table to the FC Mapping Table';
1828
1829 FII_UTIL.truncate_table ('FII_FIN_CAT_LEAF_MAPS', 'FII', g_retcode);
1830
1831 INSERT /*+ APPEND */ INTO FII_FIN_CAT_LEAF_MAPS (
1832 parent_fin_cat_id,
1833 child_fin_cat_id,
1834 creation_date,
1835 created_by,
1836 last_update_date,
1837 last_updated_by,
1838 last_update_login)
1839 SELECT
1840 parent_fin_cat_id,
1841 child_fin_cat_id,
1842 SYSDATE,
1843 FII_USER_ID,
1844 SYSDATE,
1845 FII_USER_ID,
1846 FII_LOGIN_ID
1847 FROM FII_FIN_CAT_MAPPINGS_GT;
1848
1849 IF (FIIDIM_Debug) THEN
1850 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_LEAF_MAPS');
1851 END IF;
1852
1853 --Copy FII_FIN_CAT_MAPPINGS_GT to FII_FIN_CAT_MAPPINGS
1854 g_phase := 'Copy TMP FC Mapping table to the FC Mapping Table';
1855
1856 FII_UTIL.truncate_table ('FII_FIN_CAT_MAPPINGS', 'FII', g_retcode);
1857
1858 INSERT /*+ APPEND */ INTO FII_FIN_CAT_MAPPINGS (
1859 parent_fin_cat_id,
1860 child_fin_cat_id,
1861 creation_date,
1862 created_by,
1863 last_update_date,
1864 last_updated_by,
1865 last_update_login)
1866 SELECT
1867 parent_fin_cat_id,
1868 child_fin_cat_id,
1869 SYSDATE,
1873 FII_LOGIN_ID
1870 FII_USER_ID,
1871 SYSDATE,
1872 FII_USER_ID,
1874 FROM FII_FIN_CAT_MAPPINGS_GT;
1875
1876 IF (FIIDIM_Debug) THEN
1877 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_MAPPINGS');
1878 END IF;
1879
1880 --Call FND_STATS to collect statistics after re-populating the table.
1881
1882 g_phase := 'gather_table_stats FII_FIN_CAT_MAPPINGS';
1883
1884 FND_STATS.gather_table_stats
1885 (ownname => g_schema_name,
1886 tabname => 'FII_FIN_CAT_MAPPINGS');
1887
1888 g_phase := 'gather_table_stats MLOG$_FII_FIN_CAT_MAPPINGS';
1889
1890 FND_STATS.gather_table_stats
1891 (ownname => g_schema_name,
1892 tabname => 'MLOG$_FII_FIN_CAT_MAPPINGS');
1893
1894 g_phase := 'gather_table_stats FII_FIN_CAT_LEAF_MAPS';
1895
1896 FND_STATS.gather_table_stats
1897 (ownname => g_schema_name,
1898 tabname => 'FII_FIN_CAT_LEAF_MAPS');
1899
1900 g_phase := 'gather_table_stats MLOG$_FII_FIN_CAT_LEAF_MAP';
1901
1902 FND_STATS.gather_table_stats
1903 (ownname => g_schema_name,
1904 tabname => 'MLOG$_FII_FIN_CAT_LEAF_MAP');
1905
1906 --=====================================================================
1907
1908 --Call to populate the FC Type denorm table
1909 g_phase := 'Call to populate the FC Type denorm table';
1910
1911 Populate_FCT_denorm (p_initial_load => 'Y');
1912
1913 g_phase := 'Update is_to_be_rolled_up_flag flag';
1914
1915 UPDATE FII_FIN_ITEM_LEAF_HIERS
1916 SET is_to_be_rolled_up_flag = 'Y'
1917 WHERE next_level_fin_cat_id in ( SELECT fin_category_id
1918 FROM fii_fin_cat_type_assgns
1919 WHERE top_node_flag = 'Y' and
1920 fin_cat_type_code in ('R','EXP')
1921 )
1922 OR parent_fin_cat_id in ( SELECT fin_category_id
1923 FROM fii_fin_cat_type_assgns
1924 WHERE top_node_flag = 'Y' and
1925 fin_cat_type_code in ('R','EXP')
1926 );
1927
1928 g_phase := 'Update top_node_fin_cat_type flag for OE';
1929
1930 -- Updating the records for Category type OE. We give precedence to OE over TE.
1931 UPDATE fii_fin_item_leaf_hiers
1932 SET top_node_fin_cat_type = 'OE'
1933 WHERE next_level_fin_cat_id in (SELECT fin_category_id FROM fii_fin_cat_type_assgns
1934 WHERE top_node_flag = 'Y' AND fin_cat_type_code = 'OE')
1935 AND next_level_fin_cat_id <> parent_fin_cat_id;
1936
1937 g_phase := 'Update top_node_fin_cat_type flag for CGS';
1938
1939 -- Updating the records for Category type OE. We give precedence to CGS over TE
1940 -- OE and CGS cannot be assigned to the same node so we need not worry about checking
1941 UPDATE fii_fin_item_leaf_hiers
1942 SET top_node_fin_cat_type = 'CGS'
1943 where next_level_fin_cat_id in (SELECT fin_category_id FROM fii_fin_cat_type_assgns
1944 WHERE top_node_flag = 'Y' AND fin_cat_type_code = 'CGS')
1945 and next_level_fin_cat_id <> parent_fin_cat_id;
1946
1947 g_phase := 'Update top_node_fin_cat_type flag for other category types';
1948
1949 -- Updating the records for rest of the Category type.
1950 UPDATE fii_fin_item_leaf_hiers fin
1951 SET top_node_fin_cat_type = (SELECT fin_cat_type_code FROM fii_fin_cat_type_assgns
1952 WHERE fin_category_id = fin.next_level_fin_cat_id and fin_cat_type_code in ( 'R','TE'))
1953 WHERE (fin.next_level_fin_cat_id in (SELECT fin_category_id FROM fii_fin_cat_type_assgns
1954 WHERE top_node_flag = 'Y' AND fin_cat_type_code in ( 'R','TE'))
1955 AND fin.next_level_fin_cat_id not in (SELECT fin_category_id FROM fii_fin_cat_type_assgns
1956 WHERE top_node_flag = 'Y' AND fin_cat_type_code in ('OE', 'CGS')))
1957 AND next_level_fin_cat_id <> parent_fin_cat_id;
1958
1959
1960
1961 -- Call FND_STATS to collect statistics of the table.
1962 g_phase := 'gather_table_stats FII_FIN_ITEM_LEAF_HIERS';
1963
1964 FND_STATS.gather_table_stats
1965 (ownname => g_schema_name,
1966 tabname => 'FII_FIN_ITEM_LEAF_HIERS');
1967
1968 g_phase := 'gather_table_stats MLOG$_FII_FIN_ITEM_LEAF_HI';
1969 FND_STATS.gather_table_stats
1970 (ownname => g_schema_name,
1971 tabname => 'MLOG$_FII_FIN_ITEM_LEAF_HI');
1972
1973 commit; --FND_CONCURRENT.Af_Commit;
1974
1975 IF (FIIDIM_Debug) THEN
1976 FII_MESSAGE.Func_Succ(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Init_Load');
1977 END IF;
1978
1979 -- ret_val := FND_CONCURRENT.Set_Completion_Status
1980 -- (status => 'NORMAL', message => NULL);
1981
1982 -- Exception handling
1983 EXCEPTION
1984
1985 WHEN FINDIM_fatal_err THEN
1986 FII_UTIL.Write_Log ('Init_Load -> phase: '|| g_phase);
1987 FII_UTIL.Write_Log('FII_FIN_CAT_MAINTAIN_PKG.Init_Load: '||
1988 'User defined error');
1989 -- Rollback
1990 rollback; --FND_CONCURRENT.Af_Rollback;
1991 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Init_Load');
1992 retcode := sqlcode;
1993 ret_val := FND_CONCURRENT.Set_Completion_Status
1994 (status => 'ERROR', message => substr(sqlerrm,1,180));
1995
1996 WHEN FINDIM_MULT_PAR_err THEN
1997 FII_UTIL.Write_Log ('Init_Load -> phase: '|| g_phase);
2001 rollback; --FND_CONCURRENT.Af_Rollback;
1998 FII_UTIL.Write_Log('FII_FIN_CAT_MAINTAIN_PKG.Init_Load: '||
1999 'Diamond Shape Detected');
2000 -- Rollback
2002 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Init_Load');
2003 retcode := sqlcode;
2004 ret_val := FND_CONCURRENT.Set_Completion_Status
2005 (status => 'ERROR', message => substr(sqlerrm,1,180));
2006
2007 WHEN FINDIM_Invalid_FC_ASG_err then
2008 FII_UTIL.Write_Log ('Init_Load -> phase: '|| g_phase);
2009 FII_UTIL.Write_Log('FII_FIN_CAT_MAINTAIN_PKG.Init_Load: '||
2010 'Invalid FC Type Assignment Detected');
2011 -- Rollback
2012 rollback; --FND_CONCURRENT.Af_Rollback;
2013 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Init_Load');
2014 retcode := sqlcode;
2015 ret_val := FND_CONCURRENT.Set_Completion_Status
2016 (status => 'ERROR', message => substr(sqlerrm,1,180));
2017
2018 WHEN FINDIM_NOT_ENABLED THEN
2019 FII_UTIL.Write_Log ('Init_Load -> phase: '|| g_phase);
2020 FII_UTIL.Write_Log ('>>> Financial Categories Dimension Not Enabled...');
2021
2022 Handle_Unenabled_DIM;
2023
2024 retcode := sqlcode;
2025 -- ret_val := FND_CONCURRENT.Set_Completion_Status
2026 -- (status => 'NORMAL', message => NULL);
2027
2028 WHEN FINDIM_NO_FC_TYPE_ASGN THEN
2029 FII_UTIL.Write_Log ('Init_Load -> phase: '|| g_phase);
2030 FII_UTIL.Write_Log('No Financial Category Type assignment is done.');
2031 -- Rollback
2032 rollback; --FND_CONCURRENT.Af_Rollback;
2033 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Init_Load');
2034 retcode := sqlcode;
2035 ret_val := FND_CONCURRENT.Set_Completion_Status
2036 (status => 'ERROR',
2037 message => 'No Financial Category Type assignment is done.');
2038
2039 WHEN OTHERS THEN
2040 FII_UTIL.Write_Log ('Init_Load -> phase: '|| g_phase);
2041 FII_UTIL.Write_Log(
2042 'Other error in FII_FIN_CAT_MAINTAIN_PKG.Init_Load: ' || substr(sqlerrm,1,180));
2043 -- Rollback
2044 rollback; --FND_CONCURRENT.Af_Rollback;
2045 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Init_Load');
2046 retcode := sqlcode;
2047 ret_val := FND_CONCURRENT.Set_Completion_Status
2048 (status => 'ERROR', message => NULL);
2049
2050 END Init_Load;
2051
2052
2053 -- *****************************************************************
2054 -- This is the main procedure of FC dimension program (incremental update).
2055
2056 PROCEDURE Incre_Update (errbuf OUT NOCOPY VARCHAR2,
2057 retcode OUT NOCOPY VARCHAR2) IS
2058
2059 ret_val BOOLEAN := FALSE;
2060
2061 BEGIN
2062
2063 IF (FIIDIM_Debug) THEN
2064 FII_MESSAGE.Func_Ent(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Incre_Load');
2065 END IF;
2066
2067 --First do the initialization
2068
2069 g_phase := 'Call Initialize';
2070
2071
2072 Initialize;
2073
2074 --Secondly populate the table FII_DIM_NORM_HIER_GT
2075
2076 g_phase := 'Call Get_NORM_HIERARCHY_TMP';
2077
2078 Get_NORM_HIERARCHY_TMP;
2079
2080 --Call the Flatten financial item dimension hierarchy routine to
2081 --insert all mappings.
2082
2083 g_phase := 'Call Flatten_Fin_Dim_Hier';
2084
2085 Flatten_Fin_Dim_Hier (G_MASTER_VALUE_SET_ID, G_TOP_NODE_VALUE);
2086
2087 --Copy TMP hierarchy table to the final dimension table
2088 g_phase := 'Copy TMP hierarchy table to the final full dimension table';
2089
2090 --FII_FULL_FIN_ITEM_HIERS does not require an incremental refresh.
2091 FII_UTIL.truncate_table ('FII_FULL_FIN_ITEM_HIERS', 'FII', g_retcode);
2092
2093 INSERT /*+ APPEND */ INTO FII_FULL_FIN_ITEM_HIERS (
2094 parent_level,
2095 parent_fin_cat_id,
2096 next_level,
2097 next_level_fin_cat_id,
2098 next_level_is_leaf,
2099 is_leaf_flag,
2100 child_level,
2101 child_fin_cat_id,
2102 parent_flex_value_set_id,
2103 child_flex_value_set_id,
2104 creation_date,
2105 created_by,
2106 last_update_date,
2107 last_updated_by,
2108 last_update_login)
2109 SELECT
2110 parent_level,
2111 parent_fin_cat_id,
2112 next_level,
2113 next_level_fin_cat_id,
2114 next_level_is_leaf,
2115 is_leaf_flag,
2116 child_level,
2117 child_fin_cat_id,
2118 parent_flex_value_set_id,
2119 child_flex_value_set_id,
2120 SYSDATE,
2121 FII_USER_ID,
2122 SYSDATE,
2123 FII_USER_ID,
2124 FII_LOGIN_ID
2125 FROM FII_FIN_ITEM_HIER_GT;
2126
2127 commit;
2128
2129 IF (FIIDIM_Debug) THEN
2130 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FULL_FIN_ITEM_HIERS');
2131 END IF;
2132
2133 --Call FND_STATS to collect statistics after re-populating the tables.
2134 --for the full table since it will be used later in the program
2135
2136 g_phase := 'gather_table_stats for FII_FULL_FIN_ITEM_HIERS';
2137
2138 FND_STATS.gather_table_stats
2142 --==============================================================--
2139 (ownname => g_schema_name,
2140 tabname => 'FII_FULL_FIN_ITEM_HIERS');
2141
2143
2144 --Delete LVS records from FII_FIN_ITEM_HIER_GT for pruned hierarchy table for Expense Analysis
2145 g_phase := 'Delete LVS records from FII_FIN_ITEM_HIER_GT for pruned hierarchy table for Expense Analysis';
2146
2147 Delete_LVS_Records;
2148
2149 --Copy FII_FIN_ITEM_HIER_GT to the final (pruned) dimension table for Expense Analysis
2150
2151 -- Incremental Dimension Maintence
2152 -- All data is now in the temporary table FII_FIN_ITEM_HIER_GT,
2153 -- we need to maintain the permanent table FII_FIN_ITEM__LEAF_HIERS
2154 -- by diffing the 2 tables.
2155 -- The maintenance is done by 2 statements, one INSERT and one DELETE.
2156
2157 g_phase := 'DELETE FROM FII_FIN_ITEM_LEAF_HIERS';
2158
2159 DELETE FROM FII_FIN_ITEM_LEAF_HIERS
2160 WHERE
2161 (parent_level, parent_fin_cat_id, next_level, next_level_fin_cat_id,
2162 next_level_is_leaf_flag, is_leaf_flag, child_level, child_fin_cat_id,
2163 parent_flex_value_set_id, child_flex_value_set_id,
2164 NVL(next_level_fin_cat_sort_order, -92883),
2165 LEVEL2_fin_cat_ID,
2166 LEVEL3_fin_cat_ID,
2167 LEVEL4_fin_cat_ID,
2168 LEVEL5_fin_cat_ID) IN
2169 (SELECT parent_level, parent_fin_cat_id, next_level, next_level_fin_cat_id,
2170 next_level_is_leaf_flag, is_leaf_flag, child_level, child_fin_cat_id,
2171 parent_flex_value_set_id, child_flex_value_set_id,
2172 NVL(next_level_fin_cat_sort_order, -92883),
2173 LEVEL2_fin_cat_ID,
2174 LEVEL3_fin_cat_ID,
2175 LEVEL4_fin_cat_ID,
2176 LEVEL5_fin_cat_ID
2177 FROM FII_FIN_ITEM_LEAF_HIERS
2178 MINUS
2179 SELECT parent_level, parent_fin_cat_id, next_level, next_level_fin_cat_id,
2180 next_level_is_leaf, is_leaf_flag, child_level, child_fin_cat_id,
2181 parent_flex_value_set_id, child_flex_value_set_id,
2182 NVL(next_level_fin_cat_sort_order, -92883),
2183 LEVEL2_fin_cat_ID,
2184 LEVEL3_fin_cat_ID,
2185 LEVEL4_fin_cat_ID,
2186 LEVEL5_fin_cat_ID
2187 FROM FII_FIN_ITEM_HIER_GT);
2188
2189 IF (FIIDIM_Debug) THEN
2190 FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_FIN_ITEM_LEAF_HIERS');
2191 END IF;
2192
2193 g_phase := 'Insert into FII_FIN_ITEM_LEAF_HIERS';
2194
2195 Insert into FII_FIN_ITEM_LEAF_HIERS (
2196 parent_level,
2197 parent_fin_cat_id,
2198 next_level,
2199 next_level_fin_cat_id,
2200 next_level_is_leaf_flag,
2201 is_leaf_flag,
2202 child_level,
2203 child_fin_cat_id,
2204 parent_flex_value_set_id,
2205 child_flex_value_set_id,
2206 next_level_fin_cat_sort_order,
2207 aggregate_next_level_flag,
2208 LEVEL2_fin_cat_ID,
2209 LEVEL3_fin_cat_ID,
2210 LEVEL4_fin_cat_ID,
2211 LEVEL5_fin_cat_ID,
2212 creation_date,
2213 created_by,
2214 last_update_date,
2215 last_updated_by,
2216 last_update_login,
2217 is_to_be_rolled_up_flag)
2218 (SELECT parent_level,
2219 parent_fin_cat_id,
2220 next_level,
2221 next_level_fin_cat_id,
2222 next_level_is_leaf,
2223 is_leaf_flag,
2224 child_level,
2225 child_fin_cat_id,
2226 parent_flex_value_set_id,
2227 child_flex_value_set_id,
2228 next_level_fin_cat_sort_order,
2229 'N',
2230 LEVEL2_fin_cat_ID,
2231 LEVEL3_fin_cat_ID,
2232 LEVEL4_fin_cat_ID,
2233 LEVEL5_fin_cat_ID,
2234 SYSDATE,
2235 FII_USER_ID,
2236 SYSDATE,
2237 FII_USER_ID,
2238 FII_LOGIN_ID,
2239 'N'
2240 FROM FII_FIN_ITEM_HIER_GT
2241 MINUS
2242 SELECT parent_level,
2243 parent_fin_cat_id,
2244 next_level,
2245 next_level_fin_cat_id,
2246 next_level_is_leaf_flag,
2247 is_leaf_flag,
2248 child_level,
2249 child_fin_cat_id,
2250 parent_flex_value_set_id,
2251 child_flex_value_set_id,
2252 next_level_fin_cat_sort_order,
2253 'N',
2254 LEVEL2_fin_cat_ID,
2255 LEVEL3_fin_cat_ID,
2256 LEVEL4_fin_cat_ID,
2257 LEVEL5_fin_cat_ID,
2258 SYSDATE,
2259 FII_USER_ID,
2260 SYSDATE,
2261 FII_USER_ID,
2262 FII_LOGIN_ID,
2263 'N'
2264 FROM FII_FIN_ITEM_LEAF_HIERS);
2265
2266 IF (FIIDIM_Debug) THEN
2267 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');
2268 END IF;
2269
2270 --Copy FII_FIN_ITEM_HIER_GT to the final (pruned) dimension table for DBI6.0
2271
2272 -- Incremental Dimension Maintence
2273 -- All data is now in the temporary table FII_FIN_ITEM_LEAF_HIERS,
2274 -- we need to maintain the permanent table FII_FIN_ITEM_HIERARCHIES
2275 -- by diffing the 2 tables.
2276 -- The maintenance is done by 2 statements, one INSERT and one DELETE.
2277
2278 g_phase := 'DELETE FROM FII_FIN_ITEM_HIERARCHIES';
2279
2283 next_level_is_leaf, is_leaf_flag, child_level, child_fin_cat_id,
2280 DELETE FROM FII_FIN_ITEM_HIERARCHIES
2281 WHERE
2282 (parent_level, parent_fin_cat_id, next_level, next_level_fin_cat_id,
2284 parent_flex_value_set_id, child_flex_value_set_id) IN
2285 (SELECT parent_level, parent_fin_cat_id, next_level, next_level_fin_cat_id,
2286 next_level_is_leaf, is_leaf_flag, child_level, child_fin_cat_id,
2287 parent_flex_value_set_id, child_flex_value_set_id
2288 FROM FII_FIN_ITEM_HIERARCHIES
2289 MINUS
2290 SELECT parent_level, parent_fin_cat_id, next_level, next_level_fin_cat_id,
2291 next_level_is_leaf, is_leaf_flag, child_level, child_fin_cat_id,
2292 parent_flex_value_set_id, child_flex_value_set_id
2293 FROM FII_FIN_ITEM_HIER_GT);
2294
2295 IF (FIIDIM_Debug) THEN
2296 FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_FIN_ITEM_HIERARCHIES');
2297 END IF;
2298
2299 g_phase := 'Insert into FII_FIN_ITEM_HIERARCHIES';
2300
2301 Insert into FII_FIN_ITEM_HIERARCHIES (
2302 parent_level,
2303 parent_fin_cat_id,
2304 next_level,
2305 next_level_fin_cat_id,
2306 next_level_is_leaf,
2307 is_leaf_flag,
2308 child_level,
2309 child_fin_cat_id,
2310 parent_flex_value_set_id,
2311 child_flex_value_set_id,
2312 creation_date,
2313 created_by,
2314 last_update_date,
2315 last_updated_by,
2316 last_update_login)
2317 (SELECT parent_level,
2318 parent_fin_cat_id,
2319 next_level,
2320 next_level_fin_cat_id,
2321 next_level_is_leaf,
2322 is_leaf_flag,
2323 child_level,
2324 child_fin_cat_id,
2325 parent_flex_value_set_id,
2326 child_flex_value_set_id,
2327 SYSDATE,
2328 FII_USER_ID,
2329 SYSDATE,
2330 FII_USER_ID,
2331 FII_LOGIN_ID
2332 FROM FII_FIN_ITEM_HIER_GT
2333 MINUS
2334 SELECT parent_level,
2335 parent_fin_cat_id,
2336 next_level,
2337 next_level_fin_cat_id,
2338 next_level_is_leaf,
2339 is_leaf_flag,
2340 child_level,
2341 child_fin_cat_id,
2342 parent_flex_value_set_id,
2343 child_flex_value_set_id,
2344 SYSDATE,
2345 FII_USER_ID,
2346 SYSDATE,
2347 FII_USER_ID,
2348 FII_LOGIN_ID
2349 FROM FII_FIN_ITEM_HIERARCHIES);
2350
2351 IF (FIIDIM_Debug) THEN
2352 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_HIERARCHIES');
2353 END IF;
2354
2355 -- We have added an update statement on FII_FIN_ITEM_LEAF_HIERS. Hence, moved gathering statistics
2356 -- for FII_FIN_ITEM_LEAF_HIERS table at the end of procedure.
2357
2358 --Call FND_STATS to collect statistics after re-populating the tables.
2359 --Will seed this in RSG
2360 -- FND_STATS.gather_table_stats
2361 -- (ownname => g_schema_name,
2362 -- tabname => 'FII_FIN_ITEM_HIERARCHIES');
2363
2364 --================================================================--
2365 --Populate FII_FIN_CAT_MAPPINGS table
2366 g_phase := 'Populate FII_FIN_CAT_MAPPINGS_GT table';
2367
2368 Get_FC_Mapping_GT;
2369
2370 --Copy FII_FIN_CAT_MAPPINGS_GT to FII_FIN_CAT_LEAF_MAPS
2371 g_phase := 'DELETE FROM FII_FIN_CAT_LEAF_MAPS';
2372
2373 DELETE FROM FII_FIN_CAT_LEAF_MAPS
2374 WHERE
2375 (parent_fin_cat_id, child_fin_cat_id) IN
2376 (SELECT parent_fin_cat_id, child_fin_cat_id
2377 FROM FII_FIN_CAT_LEAF_MAPS
2378 MINUS
2379 SELECT parent_fin_cat_id, child_fin_cat_id
2380 FROM FII_FIN_CAT_MAPPINGS_GT);
2381
2382 IF (FIIDIM_Debug) THEN
2383 FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_FIN_CAT_LEAF_MAPS');
2384 END IF;
2385
2386 g_phase := 'Insert into FII_FIN_CAT_LEAF_MAPS';
2387
2388 Insert into FII_FIN_CAT_LEAF_MAPS (
2389 parent_fin_cat_id,
2390 child_fin_cat_id,
2391 creation_date,
2392 created_by,
2393 last_update_date,
2394 last_updated_by,
2395 last_update_login)
2396 (SELECT parent_fin_cat_id,
2397 child_fin_cat_id,
2398 SYSDATE,
2399 FII_USER_ID,
2400 SYSDATE,
2401 FII_USER_ID,
2402 FII_LOGIN_ID
2403 FROM FII_FIN_CAT_MAPPINGS_GT
2404 MINUS
2405 SELECT parent_fin_cat_id,
2406 child_fin_cat_id,
2407 SYSDATE,
2408 FII_USER_ID,
2409 SYSDATE,
2410 FII_USER_ID,
2411 FII_LOGIN_ID
2412 FROM FII_FIN_CAT_LEAF_MAPS);
2413
2414 IF (FIIDIM_Debug) THEN
2415 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_LEAF_MAPS');
2416 END IF;
2417
2418 --Copy FII_FIN_CAT_MAPPINGS_GT to FII_FIN_CAT_MAPPINGS
2419
2420 g_phase := 'DELETE FROM FII_FIN_CAT_MAPPINGS';
2421
2422 DELETE FROM FII_FIN_CAT_MAPPINGS
2423 WHERE
2424 (parent_fin_cat_id, child_fin_cat_id) IN
2425 (SELECT parent_fin_cat_id, child_fin_cat_id
2426 FROM FII_FIN_CAT_MAPPINGS
2427 MINUS
2428 SELECT parent_fin_cat_id, child_fin_cat_id
2429 FROM FII_FIN_CAT_MAPPINGS_GT);
2430
2431 IF (FIIDIM_Debug) THEN
2432 FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_FIN_CAT_MAPPINGS');
2433 END IF;
2434
2435 g_phase := 'Insert into FII_FIN_CAT_MAPPINGS';
2436
2437 Insert into FII_FIN_CAT_MAPPINGS (
2438 parent_fin_cat_id,
2439 child_fin_cat_id,
2440 creation_date,
2441 created_by,
2442 last_update_date,
2443 last_updated_by,
2444 last_update_login)
2445 (SELECT parent_fin_cat_id,
2446 child_fin_cat_id,
2447 SYSDATE,
2448 FII_USER_ID,
2449 SYSDATE,
2450 FII_USER_ID,
2451 FII_LOGIN_ID
2452 FROM FII_FIN_CAT_MAPPINGS_GT
2453 MINUS
2454 SELECT parent_fin_cat_id,
2455 child_fin_cat_id,
2456 SYSDATE,
2457 FII_USER_ID,
2458 SYSDATE,
2459 FII_USER_ID,
2460 FII_LOGIN_ID
2461 FROM FII_FIN_CAT_MAPPINGS);
2462
2463 IF (FIIDIM_Debug) THEN
2464 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_MAPPINGS');
2465 END IF;
2466
2467 --Call FND_STATS to collect statistics after re-populating the table.
2468
2469 g_phase := 'gather_table_stats for FII_FIN_CAT_MAPPINGS';
2470
2471 FND_STATS.gather_table_stats
2472 (ownname => g_schema_name,
2473 tabname => 'FII_FIN_CAT_MAPPINGS');
2474
2475 -- Bug 4200473. Not to analyze MLOG in incremental run.
2476 -- As per performance teams suggestions.
2477
2478 -- g_phase := 'gather_table_stats for MLOG$_FII_FIN_CAT_MAPPINGS';
2479
2480 -- FND_STATS.gather_table_stats
2481 -- (ownname => g_schema_name,
2482 -- tabname => 'MLOG$_FII_FIN_CAT_MAPPINGS');
2483
2484
2485 --Call FND_STATS to collect statistics after re-populating the table.
2486
2487 g_phase := 'gather_table_stats for FII_FIN_CAT_LEAF_MAPS';
2488
2489 FND_STATS.gather_table_stats
2490 (ownname => g_schema_name,
2491 tabname => 'FII_FIN_CAT_LEAF_MAPS');
2492
2493 -- Bug 4200473. Not to analyze MLOG in incremental run.
2494 -- As per performance teams suggestions.
2495
2496 -- g_phase := 'gather_table_stats for MLOG$_FII_FIN_CAT_LEAF_MAP';
2497
2498 -- FND_STATS.gather_table_stats
2499 -- (ownname => g_schema_name,
2500 -- tabname => 'MLOG$_FII_FIN_CAT_LEAF_MAP');
2501
2502 --=====================================================================
2503
2504 --Call to populate the FC Type denorm table
2505 g_phase := 'Call to populate the FC Type denorm table';
2506
2507 Populate_FCT_denorm (p_initial_load => 'N');
2508
2509 g_phase := 'Update is_to_be_rolled_up_flag flag ';
2510
2511 UPDATE FII_FIN_ITEM_LEAF_HIERS
2512 SET is_to_be_rolled_up_flag = 'Y'
2513 WHERE (next_level_fin_cat_id in ( SELECT fin_category_id
2514 FROM fii_fin_cat_type_assgns
2515 WHERE top_node_flag = 'Y' and
2516 fin_cat_type_code in ('R','EXP')
2517 )
2518 OR parent_fin_cat_id in ( SELECT fin_category_id
2519 FROM fii_fin_cat_type_assgns
2520 WHERE top_node_flag = 'Y' and
2521 fin_cat_type_code in ('R','EXP')
2522 ))
2523 AND is_to_be_rolled_up_flag <> 'Y' ;
2524
2525 IF (FIIDIM_Debug) THEN
2526 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');
2527 END IF;
2528
2529 g_phase := 'Update top_node_fin_cat_type flag for OE';
2530
2531 -- Updating the records for Category type OE. We give precedence to OE over TE.
2532 UPDATE fii_fin_item_leaf_hiers
2533 SET top_node_fin_cat_type = 'OE'
2534 WHERE next_level_fin_cat_id IN (SELECT fin_category_id FROM fii_fin_cat_type_assgns
2535 WHERE top_node_flag = 'Y' AND fin_cat_type_code = 'OE')
2536 AND next_level_fin_cat_id <> parent_fin_cat_id
2537 AND (top_node_fin_cat_type <> 'OE' OR top_node_fin_cat_type is null);
2538
2539 IF (FIIDIM_Debug) THEN
2540 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');
2541 END IF;
2542
2543 g_phase := 'Update top_node_fin_cat_type flag for CGS';
2544
2545 -- Updating the records for Category type OE. We give precedence to CGS over TE
2546 -- OE and CGS cannot be assigned to the same node so we need not worry about checking
2547 UPDATE fii_fin_item_leaf_hiers
2548 SET top_node_fin_cat_type = 'CGS'
2549 where next_level_fin_cat_id IN (SELECT fin_category_id FROM fii_fin_cat_type_assgns
2550 WHERE top_node_flag = 'Y' AND fin_cat_type_code = 'CGS')
2551 AND next_level_fin_cat_id <> parent_fin_cat_id
2552 AND (top_node_fin_cat_type <> 'CGS' OR top_node_fin_cat_type is null);
2553
2554 IF (FIIDIM_Debug) THEN
2555 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');
2556 END IF;
2557
2558 g_phase := 'Update top_node_fin_cat_type flag for other category types';
2559
2560 -- Updating the records for rest of the Category type.
2561 UPDATE fii_fin_item_leaf_hiers fin
2562 SET top_node_fin_cat_type = (SELECT fin_cat_type_code FROM fii_fin_cat_type_assgns
2563 WHERE fin_category_id = fin.next_level_fin_cat_id and fin_cat_type_code in ( 'R','TE'))
2564 WHERE (fin.next_level_fin_cat_id in (SELECT fin_category_id FROM fii_fin_cat_type_assgns
2565 WHERE top_node_flag = 'Y' AND fin_cat_type_code in ( 'R','TE'))
2566 AND fin.next_level_fin_cat_id not in (SELECT fin_category_id FROM fii_fin_cat_type_assgns
2567 WHERE top_node_flag = 'Y' AND fin_cat_type_code in ('OE', 'CGS')))
2568 AND next_level_fin_cat_id <> parent_fin_cat_id
2569 AND (top_node_fin_cat_type <> (SELECT fin_cat_type_code FROM fii_fin_cat_type_assgns
2570 WHERE fin_category_id = fin.next_level_fin_cat_id and fin_cat_type_code in ( 'R','TE'))
2571 OR top_node_fin_cat_type is null);
2572
2573
2574 -- This update statement is added for the nodes for which there is no category assigned now, but they had one before
2575 -- This is a valid case
2576 UPDATE fii_fin_item_leaf_hiers fin
2577 set top_node_fin_cat_type = NULL
2578 where next_level_fin_cat_id in (SELECT next_level_fin_cat_id from fii_fin_item_leaf_hiers
2579 WHERE top_node_fin_cat_type is not null
2580 MINUS
2581 SELECT fin_category_id FROM fii_fin_cat_type_assgns
2582 WHERE top_node_flag = 'Y' AND fin_cat_type_code in ( 'R','TE', 'OE', 'CGS')
2583 )
2584 AND next_level_fin_cat_id <> parent_fin_cat_id;
2585
2586 IF (FIIDIM_Debug) THEN
2587 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');
2588 END IF;
2589
2590 -- Call FND_STATS to collect statistics of the table.
2591 g_phase := 'gather_table_stats FII_FIN_ITEM_LEAF_HIERS';
2592
2593 FND_STATS.gather_table_stats
2594 (ownname => g_schema_name,
2595 tabname => 'FII_FIN_ITEM_LEAF_HIERS');
2596
2597 -- Bug 4200473. Not to analyze MLOG in incremental run.
2598 -- As per performance teams suggestions.
2599
2600 -- g_phase := 'gather_table_stats MLOG$_FII_FIN_ITEM_LEAF_HI';
2601 -- FND_STATS.gather_table_stats
2602 -- (ownname => g_schema_name,
2603 -- tabname => 'MLOG$_FII_FIN_ITEM_LEAF_HI');
2604
2605 commit; --FND_CONCURRENT.Af_Commit;
2606
2607 IF (FIIDIM_Debug) THEN
2608 FII_MESSAGE.Func_Succ(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Incre_Load');
2609 END IF;
2610
2611 -- ret_val := FND_CONCURRENT.Set_Completion_Status
2612 -- (status => 'NORMAL', message => NULL);
2613
2614 -- Exception handling
2615 EXCEPTION
2616 WHEN FINDIM_fatal_err THEN
2617 FII_UTIL.Write_Log ('Incre_Update -> phase: '|| g_phase);
2618 FII_UTIL.Write_Log('FII_FIN_CAT_MAINTAIN_PKG.Incre_Update'||
2619 'User defined error');
2620 -- Rollback
2621 rollback; --FND_CONCURRENT.Af_Rollback;
2622 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Incre_Update');
2623 retcode := sqlcode;
2624 ret_val := FND_CONCURRENT.Set_Completion_Status
2625 (status => 'ERROR', message => substr(sqlerrm,1,180));
2626
2627 WHEN FINDIM_MULT_PAR_err THEN
2628 FII_UTIL.Write_Log ('Incre_Update -> phase: '|| g_phase);
2629 FII_UTIL.Write_Log('FII_FIN_CAT_MAINTAIN_PKG.Incre_Update: '||
2630 'Diamond Shape Detected');
2631 -- Rollback
2632 rollback; --FND_CONCURRENT.Af_Rollback;
2633 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Incre_Update');
2634 retcode := sqlcode;
2635 ret_val := FND_CONCURRENT.Set_Completion_Status
2636 (status => 'ERROR', message => substr(sqlerrm,1,180));
2637
2638 WHEN FINDIM_Invalid_FC_ASG_err then
2639 FII_UTIL.Write_Log ('Incre_Update -> phase: '|| g_phase);
2640 FII_UTIL.Write_Log('FII_FIN_CAT_MAINTAIN_PKG.Incre_Update: '||
2641 'Invalid FC Type Assignment Detected');
2642 -- Rollback
2643 rollback; --FND_CONCURRENT.Af_Rollback;
2644 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Incre_Update');
2645 retcode := sqlcode;
2646 ret_val := FND_CONCURRENT.Set_Completion_Status
2647 (status => 'ERROR', message => substr(sqlerrm,1,180));
2648
2649 WHEN FINDIM_NO_FC_TYPE_ASGN THEN
2650 FII_UTIL.Write_Log ('Incre_Update -> phase: '|| g_phase);
2651 FII_UTIL.Write_Log('No Financial Category Type assignment is done.');
2652 -- Rollback
2653 rollback; --FND_CONCURRENT.Af_Rollback;
2654 FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Incre_Update');
2655 retcode := sqlcode;
2656 ret_val := FND_CONCURRENT.Set_Completion_Status
2657 (status => 'ERROR',
2658 message => 'No Financial Category Type assignment is done.');
2659
2660 WHEN FINDIM_NOT_ENABLED THEN
2661 FII_UTIL.Write_Log ('Incre_Update -> phase: '|| g_phase);
2662 FII_UTIL.Write_Log ('>>> Financial Categories Dimension Not Enabled...');
2663
2664 Handle_Unenabled_DIM;
2665
2666 retcode := sqlcode;
2667 -- ret_val := FND_CONCURRENT.Set_Completion_Status
2668 -- (status => 'NORMAL', message => NULL);
2669
2670 WHEN OTHERS THEN
2671 FII_UTIL.Write_Log ('Incre_Update -> phase: '|| g_phase);
2672 FII_UTIL.Write_Log(
2673 'Other error in FII_FIN_CAT_MAINTAIN_PKG.Incre_Update: ' || substr(sqlerrm,1,180));
2674 -- Rollback
2675 rollback; --FND_CONCURRENT.Af_Rollback;
2676 FII_MESSAGE.Func_Fail(func_name => 'II_FIN_CAT_MAINTAIN_PKG.Incre_Update');
2677 retcode := sqlcode;
2678 ret_val := FND_CONCURRENT.Set_Completion_Status
2679 (status => 'ERROR', message => substr(sqlerrm,1,180));
2680
2681 END Incre_Update;
2682
2683 END FII_FIN_CAT_MAINTAIN_PKG;