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