[Home] [Help]
PACKAGE BODY: APPS.FII_UDD2_MAINTAIN_PKG
Source
1 PACKAGE BODY FII_UDD2_MAINTAIN_PKG AS
2 /* $Header: FIIU2CMB.pls 120.1 2005/10/30 05:05:40 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_UD2_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_UDD2_MAINTAIN_PKG.log',
150 'FII_UDD2_MAINTAIN_PKG.out',l_dir,'FII_UDD2_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 UDIM2_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_2');
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 UDIM2_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 UDIM2_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 dimension2');
208 End If;
209
210
211 -- --------------------------------------------------------
212 -- Find the unassigned User Defined Dimension2 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_UD2_ID, G_FII_INT_VALUE_SET_ID, l_ret_code);
218 IF(l_ret_code = -1) THEN
219 RAISE UDIM2_fatal_err;
220 END IF;
221
222 -- --------------------------------------------------------
223 -- Get the master value set and top node for User Defined Dimension2
224 -- --------------------------------------------------------
225
226 g_phase := 'Get the master value set and top node for User Defined Dimension2';
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_2';
236
237 --If the User Defined Dimension2 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 UDIM2_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 UDIM2_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 UDIM2_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 UDIM2_fatal_err;
266 When TOO_MANY_ROWS Then
267 FII_UTIL.Write_Log ('More than one master value set found for USER DEFINED Dimension2');
268 RAISE UDIM2_fatal_err;
269 When UDIM2_NOT_ENABLED then
270 raise;
271 When UDIM2_fatal_err then
272 raise;
273 When OTHERS Then
274 FII_UTIL.Write_Log ('Unexpected error when getting master value set for USER DEFINED Dimension2');
275 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
276 RAISE UDIM2_fatal_err;
277 End;
278
279 IF (FIIDIM_Debug) THEN
280 FII_UTIL.Write_Log ('USER DEFINED DIMENSION2 Master Value Set ID: '|| G_MASTER_VALUE_SET_ID);
281 FII_UTIL.Write_Log ('USER DEFINED DIMENSION2 Master Value Set: '||
282 Get_Value_Set_Name (G_MASTER_VALUE_SET_ID));
283 FII_UTIL.Write_Log (' and USER DEFINED Dimension2 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 UDIM2_fatal_err;
300 End If;
301
302 --If the User Defined dimension2 is not enabled, raise an exception
303 IF G_DBI_ENABLED_FLAG <> 'Y' then
304 RAISE UDIM2_NOT_ENABLED;
305 END IF;
306
307 Exception
308
309 When UDIM2_NOT_ENABLED then
310 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
311 --Let the main program handle this
312 raise;
313
314 When UDIM2_fatal_err then
315 FII_UTIL.Write_Log ('FII_UDD2_MAINTAIN_PKG.Initialize : '|| 'User defined error');
316 FND_CONCURRENT.Af_Rollback;
317 FII_MESSAGE.Func_Fail(func_name => 'FII_UDD2_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_2'
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_UDD2_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_2'
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 UDIM2_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_UDD2_MAINTAIN_PKG.Get_NORM_HIERARCHY_TMP');
456 END IF;
457
458 Exception
459
460 When UDIM2_fatal_err then
461 FII_UTIL.Write_Log ('FII_UDD2_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_UDD2_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_UDD2_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_UDD2_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_UDD2_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_UDD2_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_UDD2_MAINTAIN_PKG.Detect_Diamond_Shape');
560 END IF;
561
562 IF l_count > 0 THEN
563 RAISE UDIM2_MULT_PAR_err;
564 END IF;
565
566 Exception
567
568 When UDIM2_MULT_PAR_err then
569 FII_UTIL.Write_Log ('FII_UDD2_MAINTAIN_PKG.Detect_Diamond_Shape: '||
570 'diamond shape detected!');
571 RAISE;
572
573 When others then
574 FII_UTIL.Write_Log ('Unexpected error when calling Detect_Diamond_Shape.');
575 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
576 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
577 RAISE;
578
579 END Detect_Diamond_Shape;
580
581 -- *******************************************************************
582 -- This procedure recursively builds the hierarchy
583
584 PROCEDURE INSERT_IMM_CHILD_NODES
585 (p_vset_id NUMBER, p_root_node VARCHAR2) IS
586
587 CURSOR direct_children_csr (p_parent_vs_id NUMBER, p_parent_node VARCHAR2)
588 IS
589
590 SELECT ffv.flex_value_id, ffv.flex_value, ffv.flex_value_set_id, attribute_sort_order sort_order
591 FROM FII_DIM_NORM_HIER_GT ffvnh,
592 fnd_flex_values ffv
593 WHERE ffvnh.child_flex_value_set_id = ffv.flex_value_set_id
594 AND (ffv.flex_value BETWEEN ffvnh.child_flex_value_low
595 AND ffvnh.child_flex_value_high)
596 AND ((ffvnh.range_attribute = 'P' and ffv.summary_flag = 'Y') OR
597 (ffvnh.range_attribute = 'C' and ffv.summary_flag = 'N'))
598 AND ffvnh.parent_flex_value = p_parent_node
599 AND ffvnh.parent_flex_value_set_id = p_parent_vs_id;
600
601 l_flex_value_id NUMBER(15);
602 l_flex_value_set_id NUMBER(15);
603 l_sort_order NUMBER(15);
604
605 BEGIN
606
607 select flex_value_id, attribute_sort_order into l_flex_value_id, l_sort_order
608 from fnd_flex_values
609 where flex_value_set_id = p_vset_id
610 and flex_value = p_root_node;
611
612 l_flex_value_set_id := p_vset_id;
613
614 /* Inserting parent in a gt table: FII_DIM_HIER_HELP_GT */
615
616 g_index := g_index+1;
617
618 insert into FII_DIM_HIER_HELP_GT
619 ( IDX,
620 FLEX_VALUE_ID,
621 FLEX_VALUE_SET_ID,
622 NEXT_LEVEL_FLEX_VALUE_ID, SORT_ORDER)
623 values
624 ( g_index,
625 l_flex_value_id,
626 l_flex_value_set_id,
627 l_flex_value_id, l_sort_order);
628
629 update FII_DIM_HIER_HELP_GT
630 set NEXT_LEVEL_FLEX_VALUE_ID= l_flex_value_id,
631 SORT_ORDER= l_sort_order
632 where IDX = g_index - 1;
633
634 FOR direct_children_rec IN direct_children_csr(p_vset_id, p_root_node)
635 LOOP
636
637 /* Inserting record with all parents */
638 INSERT INTO fii_UDD2_hier_gt (
639 parent_level,
640 parent_value_id,
641 child_value_id,
642 next_level,
643 child_level,
644 next_level_is_leaf_flag,
645 is_leaf_flag,
646 parent_flex_value_Set_id,
647 child_flex_value_set_id,
648 next_level_value_id,
649 next_level_value_sort_order)
650 SELECT pp.idx,
651 pp.flex_value_id,
652 direct_children_rec.flex_value_id,
653 pp.idx + 1,
654 g_index + 1,
655 'N',
656 'N',
657 pp.flex_value_set_id,
658 direct_children_rec.flex_value_set_id,
659 decode(pp.idx, g_index,
660 direct_children_rec.flex_value_id,
661 pp.next_level_flex_value_id),
662 decode(pp.idx, g_index,
663 direct_children_rec.sort_order,
664 pp.sort_order)
665 FROM FII_DIM_HIER_HELP_GT pp;
666
667 --Recursive Call.
668 INSERT_IMM_CHILD_NODES (direct_children_rec.flex_value_set_id,
669 direct_children_rec.flex_value);
670
671 END LOOP;
672
673 /* Deleting parent from the gt table */
674 delete from FII_DIM_HIER_HELP_GT where idx = g_index;
675
676 g_index := g_index-1;
677
678 FND_CONCURRENT.Af_Commit; --commit
679
680 EXCEPTION
681 WHEN NO_DATA_FOUND Then
682 FII_UTIL.Write_Log ('Insert Immediate child: No Data Found');
683 FII_MESSAGE.Func_Fail
684 (func_name => 'FII_UDD2_MAINTAIN_PKG.Insert_Imm_Child_Nodes');
685 RAISE;
686
687 WHEN OTHERS Then
688 FII_UTIL.Write_Log (substr(SQLERRM,1,180));
689 FII_MESSAGE.Func_Fail
690 (func_name => 'FII_UDD2_MAINTAIN_PKG.INSERT_IMM_CHILD_NODES');
691 RAISE;
692
693 END INSERT_IMM_CHILD_NODES;
694
695 -- **************************************************************************
696 -- This procedure will populate the TMP hierarchy table
697
698 PROCEDURE Flatten_UD2_Dim_Hier (p_vset_id NUMBER, p_root_node VARCHAR2) IS
699 CURSOR MAIN_CSR is
700 SELECT parent_level, parent_value_id, next_level, next_level_value_id,
701 child_level, child_value_id, child_flex_value_set_id,
702 parent_flex_value_set_id
703 FROM fii_UDD2_hier_gt
704 ORDER BY parent_level, child_level;
705
706 l_flex_value VARCHAR2(150);
707 p_parent_id NUMBER(15);
708
709 BEGIN
710
711 IF (FIIDIM_Debug) THEN
712 FII_MESSAGE.Func_Ent(func_name => 'FII_UDD2_MAINTAIN_PKG.'||
713 'Flatten_UD2_Dim_Hier');
714 END IF;
715
716 g_phase := 'Truncate table FII_UDD2_HIER_GT';
717 FII_UTIL.truncate_table ('FII_UDD2_HIER_GT', 'FII', g_retcode);
718
719 -----------------------------------------------------------------
720
721 UDIM2_parent_node := p_root_node;
722 UDIM2_parent_vset_id := p_vset_id;
723
724 g_phase := 'Get p_parent_id from FND_FLEX_VALUES';
725
726 SELECT flex_value_id INTO p_parent_id
727 FROM FND_FLEX_VALUES
728 WHERE flex_value_set_id = p_vset_id
729 AND flex_value = p_root_node;
730
731 UDIM2_parent_flex_id := p_parent_id;
732
733 -- The following Insert statement inserts the top node self row and
734 -- invokes Ins_Imm_Child_nodes routine to insert all top node mappings
735 -- with in the hierarchy.
736 g_phase := 'insert top node self row and invoke Ins_Imm_Child_nodes';
737
738 INSERT_IMM_CHILD_NODES (p_vset_id, p_root_node);
739
740 g_phase := 'Insert all the self records';
741 insert into fii_UDD2_hier_gt (
742 parent_level,
743 parent_value_id,
744 next_level,
745 next_level_value_id,
746 child_level,
747 child_value_id,
748 child_flex_value_set_id,
749 parent_flex_value_set_id,
750 next_level_is_leaf_flag,
751 is_leaf_flag)
752 select
753 child_level,
754 child_value_id,
755 child_level,
756 child_value_id,
757 child_level,
758 child_value_id,
759 child_flex_value_set_id,
760 child_flex_value_set_id,
761 'N',
762 'N'
763 from (select distinct child_value_id,child_level,child_flex_value_set_id from fii_udd2_hier_gt);
764
765 IF (FIIDIM_Debug) THEN
766 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD2_HIER_GT');
767 END IF;
768
769 g_phase := 'Insert self record for the top node';
770 INSERT INTO fii_UDD2_hier_gt
771 (parent_level,
772 parent_value_id,
773 next_level,
774 next_level_value_id,
775 child_level,
776 child_value_id,
777 child_flex_value_set_id,
778 parent_flex_value_set_id,
779 next_level_is_leaf_flag,
780 is_leaf_flag)
781 VALUES
782 (1,
783 p_parent_id,
784 1,
785 p_parent_id,
786 1,
787 p_parent_id,
788 p_vset_id,
789 UDIM2_parent_vset_id,
790 'N',
791 'N');
792
793 IF (FIIDIM_Debug) THEN
794 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD2_HIER_GT');
795 END IF;
796
797 --Insert the UNASSIGNED to the hierarchy table.
798 --Use G_TOP_NODE_ID as the parent
799 g_phase := 'Insert the UNASSIGNED to the hierarchy table';
800
801 -- First one is (G_TOP_NODE_ID, UNASSIGNED, UNASSIGNED)
802 INSERT INTO fii_UDD2_hier_gt
803 (parent_level,
804 parent_value_id,
805 next_level,
806 next_level_value_id,
807 child_level,
808 child_value_id,
809 child_flex_value_set_id,
810 parent_flex_value_set_id,
811 next_level_is_leaf_flag,
812 is_leaf_flag)
813 VALUES (
814 1,
815 G_TOP_NODE_ID,
816 2,
817 G_UNASSIGNED_UD2_ID,
818 2,
819 G_UNASSIGNED_UD2_ID,
820 G_FII_INT_VALUE_SET_ID,
821 -998,
822 'N',
823 'N');
824
825 IF (FIIDIM_Debug) THEN
826 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD2_HIER_GT');
827 END IF;
828
829 -- Another one is (UNASSIGNED, UNASSIGNED, UNASSIGNED)
830 INSERT INTO fii_UDD2_hier_gt
831 (parent_level,
832 parent_value_id,
833 next_level,
834 next_level_value_id,
835 child_level,
836 child_value_id,
837 child_flex_value_set_id,
838 parent_flex_value_set_id,
839 next_level_is_leaf_flag,
840 is_leaf_flag)
841 VALUES (
842 2,
843 G_UNASSIGNED_UD2_ID,
844 2,
845 G_UNASSIGNED_UD2_ID,
846 2,
847 G_UNASSIGNED_UD2_ID,
848 G_FII_INT_VALUE_SET_ID,
849 G_FII_INT_VALUE_SET_ID,
850 'N',
851 'N');
852
853 IF (FIIDIM_Debug) THEN
854 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD2_HIER_GT');
855 END IF;
856
857 -- Insert a dummy super top node (-999) to the hierarchy table
858 -- (the dummy value set id is -998)
859 g_phase := 'Insert a dummy top node (-999) to the hierarchy table';
860
861 INSERT INTO fii_UDD2_hier_gt
862 (parent_level,
863 parent_value_id,
864 next_level,
865 next_level_value_id,
866 child_level,
867 child_value_id,
868 child_flex_value_set_id,
869 parent_flex_value_set_id,
870 next_level_is_leaf_flag,
871 is_leaf_flag)
872 SELECT
873 0,
874 -999,
875 1,
876 G_TOP_NODE_ID,
877 child_level,
878 child_value_id,
879 child_flex_value_set_id,
880 -998,
881 'N',
882 'N'
883 FROM fii_UDD2_hier_gt
884 WHERE child_value_id = parent_value_id;
885
886 IF (FIIDIM_Debug) THEN
887 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD2_HIER_GT');
888 END IF;
889
890 --Call FND_STATS to collect statistics after populating the table
891 g_phase := 'gather_table_stats for FII_FIN_ITEM_HIER_GT';
892
893 FND_STATS.gather_table_stats
894 (ownname => g_schema_name,
895 tabname => 'FII_FIN_ITEM_HIER_GT');
896
897 --====================================================================
898 --Before we proceed to populate the final hierarchy table, we should
899 --check if there are any diamond shapes in the TMP hierarchy table.
900 --If so, we will report the problem, and error out the program
901
902 -- The following block checks for child value multiple assignments
903 -- to different parents within the value sets
904 -- We use (just created) TMP table FII_UDD2_HIER_GT for this purpose
905 g_phase := 'Call Detect_Diamond_Shape';
906
907 Detect_Diamond_Shape;
908
909 ----------------------------------------------------------------------
910 -- We are not updating the next_level_is_leaf_flag and is_leaf_flag
911 -- for the full hierarchy since it's not used anywhere
912 ----------------------------------------------------------------------
913
914 IF (FIIDIM_Debug) THEN
915 FII_MESSAGE.Func_Succ(func_name => 'FII_UDD2_MAINTAIN_PKG.'||
916 'Flatten_UD2_Dim_Hier');
917 END IF;
918 EXCEPTION
919
920 WHEN NO_DATA_FOUND THEN
921 FII_UTIL.Write_Log ('Flatten_UD2_Dim_Hier: No Data Found');
922 FII_MESSAGE.Func_Fail(func_name => 'FII_UDD2_MAINTAIN_PKG.'||
923 'Flatten_UD2_Dim_Hier');
924 raise;
925
926 WhEN UDIM2_MULT_PAR_err THEN
927 FII_UTIL.Write_Log ('Flatten_UD2_Dim_Hier: Diamond Shape Detected');
928 FII_MESSAGE.Func_Fail (func_name =>
929 'FII_UDD2_MAINTAIN_PKG.Flatten_UD2_Dim_Hier');
930 raise;
931
932 WHEN OTHERS THEN
933 FII_UTIL.Write_Log ('Flatten_UD2_Dim_Hier: '|| substr(sqlerrm,1,180));
934 FII_MESSAGE.Func_Fail(func_name => 'FII_UDD2_MAINTAIN_PKG.'||
935 'Flatten_UD2_Dim_Hier');
936 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
937 raise;
938
939 END Flatten_UD2_Dim_Hier;
940
941
942 -- **************************************************************************
943 -- Populate FII_UDD2_MAPPINGS_GT Table for FII_UDD2_MAPPINGS
944
945 PROCEDURE Get_UD2_Mapping_GT IS
946
947 Begin
948
949 IF (FIIDIM_Debug) THEN
950 FII_MESSAGE.Func_Ent(func_name => 'FII_UDD2_MAINTAIN_PKG.'||
951 'Get_UD2_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_UDD2_MAPPING_GT
961 (PARENT_USER_DIM2_ID,
962 CHILD_USER_DIM2_ID)
963 SELECT fh.parent_value_id,
964 fh.child_value_id
965 FROM FII_FULL_UDD2_HIERS fh
966 WHERE fh.parent_value_id IN
967 (SELECT ph.parent_value_id
968 FROM FII_UDD2_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_UDD2_MAPPINGS_GT');
973 END IF;
974
975
976 --Then, insert self-mapping records for all nodes in pruned hierarchy
977 --FII_UDD2_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_UDD2_MAPPING_GT
982 (PARENT_USER_DIM2_ID,
983 CHILD_USER_DIM2_ID)
984 SELECT parent_value_id,
985 child_value_id
986 FROM FII_UDD2_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_UDD2_MAPPINGS_GT');
993 END IF;
994
995 --Call FND_STATS to collect statistics after populating the table
996 g_phase := 'gather_table_stats for FII_UDD2_MAPPINGS_GT';
997
998 FND_STATS.gather_table_stats
999 (ownname => g_schema_name,
1000 tabname => 'FII_UDD2_MAPPING_GT');
1001
1002 IF (FIIDIM_Debug) THEN
1003 FII_MESSAGE.Func_Succ(func_name => 'FII_UDD2_MAINTAIN_PKG.Get_UD2_Mapping_GT');
1004 END IF;
1005
1006 EXCEPTION
1007
1008 WHEN OTHERS THEN
1009 FII_UTIL.Write_Log ('Get_UD2_Mapping_GT -> phase: '|| g_phase);
1010 FII_UTIL.Write_Log ('Get_UD2_Mapping_GT: '|| substr(sqlerrm,1,180));
1011 FII_MESSAGE.Func_Fail(func_name => 'FII_UDD2_MAINTAIN_PKG.'||
1012 'Get_UD2_Mapping_GT');
1013 raise;
1014
1015 END Get_UD2_Mapping_GT;
1016
1017 -- **************************************************************************
1018 -- Populate the pruned User Defined Dimension2 hierarchy FII_UDD2_HIERARCHIES by deleting from
1019 -- FII_UDD2_HIER_GT (full version) the LVS records
1020
1021 PROCEDURE Get_Pruned_UD2_GT IS
1022
1023 Begin
1024
1025 IF (FIIDIM_Debug) THEN
1026 FII_MESSAGE.Func_Ent(func_name => 'FII_UDD2_MAINTAIN_PKG.'||
1027 'Get_Pruned_UD2_GT');
1028 END IF;
1029
1030 --Delete from FII_UDD2_HIER_GT for child value set not equal to
1031 --the master value set and not equal to the UNASSIGNED value set.
1032 g_phase := 'Delete FII_UDD2_HIER_GT #1';
1033
1034 Delete from FII_UDD2_HIER_GT
1035 Where child_flex_value_set_id <> G_MASTER_VALUE_SET_ID
1036 And child_flex_value_set_id <> G_FII_INT_VALUE_SET_ID;
1037
1038 IF (FIIDIM_Debug) THEN
1039 FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows form FII_UDD2_HIER_GT');
1040 END IF;
1041
1042 --Finally, update the columns next_level_is_leaf_flag, is_leaf_flag again
1043 --for the latest FII_UDD2_HIER_GT
1044 g_phase := 'Update next_level_is_leaf_flag, is_leaf_flag';
1045
1046 --Update the column next_level_is_leaf_flag
1047 --We look at those records (P,A,A) in which A is a leaf value
1048 Update fii_UDD2_hier_gt tab1
1049 Set next_level_is_leaf_flag = 'Y'
1050 Where tab1.next_level_value_id = tab1.child_value_id
1051 and tab1.next_level_value_id IN (
1052 select /*+ ordered */ tab3.next_level_value_id
1053 from fii_UDD2_hier_gt tab3,
1054 fii_UDD2_hier_gt tab2
1055 where tab2.parent_value_id = tab3.parent_value_id
1056 and tab3.parent_value_id = tab3.child_value_id
1057 group by tab3.next_level_value_id
1058 having count(*) = 1);
1059
1060 IF (FIIDIM_Debug) THEN
1061 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_UDD2_HIER_GT');
1062 END IF;
1063
1064 --Update the column is_leaf_flag
1065 --We look at all records (A,A,A) in which A is a leaf value
1066 Update fii_UDD2_hier_gt
1067 Set is_leaf_flag = 'Y'
1068 Where parent_value_id = child_value_id
1069 and next_level_is_leaf_flag = 'Y';
1070
1071 IF (FIIDIM_Debug) THEN
1072 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_UDD2_HIER_GT');
1073 END IF;
1074
1075 IF (FIIDIM_Debug) THEN
1076 FII_MESSAGE.Func_Succ(func_name => 'FII_UDD2_MAINTAIN_PKG.'||
1077 'Get_Pruned_UD2_GT');
1078 END IF;
1079
1080 EXCEPTION
1081
1082 WHEN OTHERS THEN
1083 FII_UTIL.Write_Log ('Get_Pruned_UD2_GT -> phase: '|| g_phase);
1084 FII_UTIL.Write_Log ('Get_Pruned_UD2_GT: '|| substr(sqlerrm,1,180));
1085 FII_MESSAGE.Func_Fail(func_name => 'FII_UDD2_MAINTAIN_PKG.'||
1086 'Get_Pruned_UD2_GT');
1087 raise;
1088
1089 END Get_Pruned_UD2_GT;
1090
1091 -- **************************************************************************
1092 -- Insert UNASSIGNED to the dimension tables (both full and pruned version and
1093 -- Mappings table)
1094 --
1095
1096 PROCEDURE Handle_Unenabled_DIM IS
1097
1098 l_count number := 0;
1099
1100 Begin
1101
1102 IF (FIIDIM_Debug) THEN
1103 FII_MESSAGE.Func_Ent(func_name => 'FII_UDD2_MAINTAIN_PKG.'||
1104 'Handle_Unenabled_DIM');
1105 END IF;
1106
1107 g_phase := 'Check if the dimension was already disabled';
1108 -- We dont truncate the tables in case the dimension was disabled
1109 -- before also since truncation of the tables does not let
1110 -- incremental refresh of MV happen.
1111 -- If the dimension hierarchy table has 1 record then the dimension
1112 -- was disabled previously.
1113 select count(*) into l_count from fii_full_udd2_hiers;
1114
1115 IF(l_count > 1 OR l_count = 0) THEN
1116 -- Incase the dimension hierarchy table had more than 1 record
1117 -- this means the dimension was enabled previously and it has been
1118 -- disabled now, in which case initial refresh of MV should happen
1119 -- so it is ok to truncate the tables
1120
1121 g_phase := 'Truncate dimension hierarchy tables';
1122 FII_UTIL.truncate_table ('FII_FULL_UDD2_HIERS', 'FII', g_retcode);
1123 FII_UTIL.truncate_table ('FII_UDD2_HIERARCHIES', 'FII', g_retcode);
1124 FII_UTIL.truncate_table ('FII_UDD2_MAPPINGS', 'FII', g_retcode);
1125
1126 g_phase := 'Inserting UNASSIGNED record in Full hierarchy';
1127
1128 INSERT INTO FII_FULL_UDD2_HIERS
1129 (parent_level,
1130 parent_value_id,
1131 next_level,
1132 next_level_value_id,
1133 child_level,
1134 child_value_id,
1135 child_flex_value_set_id,
1136 parent_flex_value_set_id,
1137 next_level_is_leaf_flag,
1138 is_leaf_flag,
1139 creation_date,
1140 created_by,
1141 last_update_date,
1142 last_updated_by,
1143 last_update_login)
1144 VALUES (
1145 1,
1146 G_UNASSIGNED_UD2_ID,
1147 1,
1148 G_UNASSIGNED_UD2_ID,
1149 1,
1150 G_UNASSIGNED_UD2_ID,
1151 G_FII_INT_VALUE_SET_ID,
1152 G_FII_INT_VALUE_SET_ID,
1153 'N',
1154 'N',
1155 SYSDATE,
1156 FII_USER_ID,
1157 SYSDATE,
1158 FII_USER_ID,
1159 FII_LOGIN_ID);
1160
1161 g_phase := 'Inserting UNASSIGNED record in Pruned hierarchy';
1162
1163 INSERT INTO FII_UDD2_HIERARCHIES
1164 (parent_level,
1165 parent_value_id,
1166 next_level,
1167 next_level_value_id,
1168 child_level,
1169 child_value_id,
1170 child_flex_value_set_id,
1171 parent_flex_value_set_id,
1172 next_level_is_leaf_flag,
1173 is_leaf_flag,
1174 aggregate_next_level_flag,
1175 creation_date,
1176 created_by,
1177 last_update_date,
1178 last_updated_by,
1179 last_update_login)
1180 VALUES (
1181 1,
1182 G_UNASSIGNED_UD2_ID,
1183 1,
1184 G_UNASSIGNED_UD2_ID,
1185 1,
1186 G_UNASSIGNED_UD2_ID,
1187 G_FII_INT_VALUE_SET_ID,
1188 G_FII_INT_VALUE_SET_ID,
1189 'N',
1190 'N',
1191 'N',
1192 SYSDATE,
1193 FII_USER_ID,
1194 SYSDATE,
1195 FII_USER_ID,
1196 FII_LOGIN_ID);
1197
1198 g_phase := 'Inserting UNASSIGNED record in Mappings table';
1199
1200 INSERT INTO FII_UDD2_MAPPINGS(
1201 PARENT_USER_DIM2_ID ,
1202 CHILD_USER_DIM2_ID ,
1203 LAST_UPDATE_DATE ,
1204 LAST_UPDATED_BY ,
1205 CREATION_DATE ,
1206 CREATED_BY ,
1207 LAST_UPDATE_LOGIN )
1208 VALUES(
1209 G_UNASSIGNED_UD2_ID,
1210 G_UNASSIGNED_UD2_ID,
1211 SYSDATE,
1212 FII_USER_ID,
1213 SYSDATE,
1214 FII_USER_ID,
1215 FII_LOGIN_ID);
1216
1217 commit;
1218 END IF;
1219
1220 IF (FIIDIM_Debug) THEN
1221 FII_MESSAGE.Func_Succ(func_name => 'FII_UDD2_MAINTAIN_PKG.'||
1222 'Handle_Unenabled_DIM');
1223 END IF;
1224
1225 EXCEPTION
1226
1227 WHEN OTHERS THEN
1228 FII_UTIL.Write_Log ('Handle_Unenabled_DIM: '|| substr(sqlerrm,1,180));
1229 FII_MESSAGE.Func_Fail(func_name => 'FII_UDD2_MAINTAIN_PKG.'||
1230 'Handle_Unenabled_DIM');
1231 raise;
1232
1233 END Handle_Unenabled_DIM;
1234
1235
1236 -- **************************************************************************
1237 -- This is the main procedure of User Defined Dimension2 dimension program (initial populate).
1238
1239 PROCEDURE Init_Load (errbuf OUT NOCOPY VARCHAR2,
1240 retcode OUT NOCOPY VARCHAR2) IS
1241
1242 ret_val BOOLEAN := FALSE;
1243
1244 BEGIN
1245
1246 IF (FIIDIM_Debug) THEN
1247 FII_MESSAGE.Func_Ent(func_name => 'FII_UDD2_MAINTAIN_PKG.Init_Load');
1248 END IF;
1249
1250 --First do the initialization
1251
1252 Initialize;
1253
1254 --Secondly populate the table FII_DIM_NORM_HIER_GT
1255
1256 Get_NORM_HIERARCHY_TMP;
1257
1258
1259 --Call the Flatten User Defined Dimension2 dimension hierarchy routine to insert all mappings.
1260
1261 Flatten_UD2_Dim_Hier (G_MASTER_VALUE_SET_ID, G_TOP_NODE_VALUE);
1262
1263
1264 --==============================================================--
1265
1266 --Copy TMP hierarchy table to the final dimension table
1267 g_phase := 'Copy TMP hierarchy table to the final full dimension table';
1268
1269 FII_UTIL.truncate_table ('FII_FULL_UDD2_HIERS', 'FII', g_retcode);
1270
1271 INSERT /*+ APPEND */ INTO FII_FULL_UDD2_HIERS (
1272 parent_level,
1273 parent_value_id,
1274 next_level,
1275 next_level_value_id,
1276 next_level_is_leaf_flag,
1277 is_leaf_flag,
1278 child_level,
1279 child_value_id,
1280 parent_flex_value_set_id,
1281 child_flex_value_set_id,
1282 creation_date,
1283 created_by,
1284 last_update_date,
1285 last_updated_by,
1286 last_update_login)
1287 SELECT
1288 parent_level,
1289 parent_value_id,
1290 next_level,
1291 next_level_value_id,
1292 next_level_is_leaf_flag,
1293 is_leaf_flag,
1294 child_level,
1295 child_value_id,
1296 parent_flex_value_set_id,
1297 child_flex_value_set_id,
1298 SYSDATE,
1299 FII_USER_ID,
1300 SYSDATE,
1301 FII_USER_ID,
1302 FII_LOGIN_ID
1303 FROM FII_UDD2_HIER_GT;
1304
1305 IF (FIIDIM_Debug) THEN
1306 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows in FII_FULL_UDD2_HIERS');
1307 END IF;
1308
1309 --Call FND_STATS to collect statistics after re-populating the tables.
1310 --for the full dimension table since it will be used later
1311 FND_STATS.gather_table_stats
1312 (ownname => g_schema_name,
1313 tabname => 'FII_FULL_UDD2_HIERS');
1314
1315 --==============================================================--
1316
1317 --Delete/Update FII_UDD2_HIER_GT for pruned hierarchy table
1318 g_phase := 'Delete/Update FII_UDD2_HIER_GT for pruned hierarchy table';
1319
1320 Get_Pruned_UD2_GT;
1321
1322 --Copy FII_UDD2_HIER_GT to the final (pruned) dimension table
1323 g_phase := 'Copy TMP hierarchy table to the final pruned dimension table';
1324
1325 FII_UTIL.truncate_table ('FII_UDD2_HIERARCHIES', 'FII', g_retcode);
1326
1327 INSERT /*+ APPEND */ INTO FII_UDD2_HIERARCHIES (
1328 parent_level,
1329 parent_value_id,
1330 next_level,
1331 next_level_value_id,
1332 next_level_is_leaf_flag,
1333 is_leaf_flag,
1334 child_level,
1335 child_value_id,
1336 parent_flex_value_set_id,
1337 child_flex_value_set_id,
1338 NEXT_LEVEL_value_SORT_ORDER,
1339 aggregate_next_level_flag,
1340 creation_date,
1341 created_by,
1342 last_update_date,
1343 last_updated_by,
1344 last_update_login)
1345 SELECT
1346 parent_level,
1347 parent_value_id,
1348 next_level,
1349 next_level_value_id,
1350 next_level_is_leaf_flag,
1351 is_leaf_flag,
1352 child_level,
1353 child_value_id,
1354 parent_flex_value_set_id,
1355 child_flex_value_set_id,
1356 NEXT_LEVEL_value_SORT_ORDER,
1357 'N',
1358 SYSDATE,
1359 FII_USER_ID,
1360 SYSDATE,
1361 FII_USER_ID,
1362 FII_LOGIN_ID
1363 FROM FII_UDD2_HIER_GT;
1364
1365 IF (FIIDIM_Debug) THEN
1366 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD2_HIERARCHIES');
1367 END IF;
1368
1369 -- This will be in RSG data
1370 g_phase := 'gather_table_stats for FII_UDD2_HIERARCHIES';
1371 FND_STATS.gather_table_stats
1372 (ownname => g_schema_name,
1373 tabname => 'FII_UDD2_HIERARCHIES');
1374
1375 g_phase := 'gather_table_stats for MLOG$_FII_UDD2_HIERARCHIES';
1376 FND_STATS.gather_table_stats
1377 (ownname => g_schema_name,
1378 tabname => 'MLOG$_FII_UDD2_HIERARCHIES');
1379
1380 --to avoid ORA-12838: cannot read/modify an object after modifying
1381 --it in parallel (due to the hint APPEND)
1382 commit;
1383
1384 --================================================================--
1385
1386 --Populate FII_UDD2_MAPPINGS table
1387 g_phase := 'Populate FII_UDD2_MAPPINGS_GT table';
1388
1389 Get_UD2_Mapping_GT;
1390
1391 --Copy FII_UDD2_MAPPING_GT to FII_UDD2_MAPPINGS
1392 g_phase := 'Copy TMP UD2 Mapping table to the UD2 Mapping Table';
1393
1394 FII_UTIL.truncate_table ('FII_UDD2_MAPPINGS', 'FII', g_retcode);
1395
1396 INSERT /*+ APPEND */ INTO FII_UDD2_MAPPINGS (
1397 PARENT_USER_DIM2_ID,
1398 CHILD_USER_DIM2_ID,
1399 creation_date,
1400 created_by,
1401 last_update_date,
1402 last_updated_by,
1403 last_update_login)
1404 SELECT
1405 PARENT_USER_DIM2_ID,
1406 CHILD_USER_DIM2_ID,
1407 SYSDATE,
1408 FII_USER_ID,
1409 SYSDATE,
1410 FII_USER_ID,
1411 FII_LOGIN_ID
1412 FROM FII_UDD2_MAPPING_GT;
1413
1414 IF (FIIDIM_Debug) THEN
1415 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD2_MAPPINGS');
1416 END IF;
1417
1418 --Call FND_STATS to collect statistics after re-populating the table.
1419
1420 g_phase := 'gather_table_stats FII_UDD2_MAPPINGS';
1421
1422 FND_STATS.gather_table_stats
1423 (ownname => g_schema_name,
1424 tabname => 'FII_UDD2_MAPPINGS');
1425
1426 g_phase := 'gather_table_stats MLOG$_FII_UDD2_MAPPINGS';
1427
1428 FND_STATS.gather_table_stats
1429 (ownname => g_schema_name,
1430 tabname => 'MLOG$_FII_UDD2_MAPPINGS');
1431
1432 --=====================================================================
1433
1434 FND_CONCURRENT.Af_Commit;
1435
1436 IF (FIIDIM_Debug) THEN
1437 FII_MESSAGE.Func_Succ(func_name => 'FII_UDD2_MAINTAIN_PKG.Init_Load');
1438 END IF;
1439
1440 -- Exception handling
1441
1442 EXCEPTION
1443
1444 WHEN UDIM2_fatal_err THEN
1445
1446 FII_UTIL.Write_Log ('FII_UDD2_MAINTAIN_PKG.Init_Load: '||
1447 'User defined error');
1448
1449 FND_CONCURRENT.Af_Rollback;
1450 FII_MESSAGE.Func_Fail(func_name => 'FII_UDD2_MAINTAIN_PKG.Init_Load');
1451 retcode := sqlcode;
1452 ret_val := FND_CONCURRENT.Set_Completion_Status
1453 (status => 'ERROR', message => substr(sqlerrm,1,180));
1454
1455 WHEN UDIM2_MULT_PAR_err THEN
1456 FII_UTIL.Write_Log ('FII_UDD2_MAINTAIN_PKG.Init_Load: '||
1457 'Diamond Shape Detected');
1458
1459 FND_CONCURRENT.Af_Rollback;
1460 FII_MESSAGE.Func_Fail(func_name => 'FII_UDD2_MAINTAIN_PKG.Init_Load');
1461 retcode := sqlcode;
1462 ret_val := FND_CONCURRENT.Set_Completion_Status
1463 (status => 'ERROR', message => substr(sqlerrm,1,180));
1464
1465 WHEN UDIM2_NOT_ENABLED THEN
1466 FII_UTIL.Write_Log ('>>> UDD2 Dimension Not Enabled...');
1467
1468 Handle_Unenabled_DIM;
1469
1470 retcode := sqlcode;
1471 --ret_val := FND_CONCURRENT.Set_Completion_Status
1472 -- (status => 'NORMAL', message => NULL);
1473
1474 WHEN OTHERS THEN
1475 FII_UTIL.Write_Log ('Init_Load -> phase: '|| g_phase);
1476 FII_UTIL.Write_Log (
1477 'Other error in FII_UDD2_MAINTAIN_PKG.Init_Load: ' || substr(sqlerrm,1,180));
1478
1479
1480 FND_CONCURRENT.Af_Rollback;
1481 FII_MESSAGE.Func_Fail(func_name => 'FII_UDD2_MAINTAIN_PKG.Init_Load');
1482 retcode := sqlcode;
1483 ret_val := FND_CONCURRENT.Set_Completion_Status
1484 (status => 'ERROR', message => substr(sqlerrm,1,180));
1485
1486 END Init_Load;
1487
1488
1489 -- *****************************************************************
1490
1491 -- This is the main procedure of User Defined Dimension2 dimension program (incremental update).
1492
1493 PROCEDURE Incre_Update (errbuf OUT NOCOPY VARCHAR2,
1494 retcode OUT NOCOPY VARCHAR2) IS
1495
1496 ret_val BOOLEAN := FALSE;
1497
1498 BEGIN
1499
1500 IF (FIIDIM_Debug) THEN
1501 FII_MESSAGE.Func_Ent(func_name => 'FII_UDD2_MAINTAIN_PKG.Incre_Load');
1502 END IF;
1503
1504 --First do the initialization
1505
1506 Initialize;
1507
1508 --Secondly populate the table FII_DIM_NORM_HIER_GT
1509
1510 Get_NORM_HIERARCHY_TMP;
1511
1512 --Call the Flatten User Defined Dimension2 dimension hierarchy routine to insert all mappings.
1513
1514 Flatten_UD2_Dim_Hier (G_MASTER_VALUE_SET_ID, G_TOP_NODE_VALUE);
1515
1516 g_phase := 'Copy TMP hierarchy table to the final full dimension table';
1517 FII_UTIL.truncate_table ('FII_FULL_UDD2_HIERS', 'FII', g_retcode);
1518
1519 Insert into FII_FULL_UDD2_HIERS (
1520 parent_level,
1521 parent_value_id,
1522 next_level,
1523 next_level_value_id,
1524 next_level_is_leaf_flag,
1525 is_leaf_flag,
1526 child_level,
1527 child_value_id,
1528 parent_flex_value_set_id,
1529 child_flex_value_set_id,
1530 creation_date,
1531 created_by,
1532 last_update_date,
1533 last_updated_by,
1534 last_update_login)
1535 SELECT parent_level,
1536 parent_value_id,
1537 next_level,
1538 next_level_value_id,
1539 next_level_is_leaf_flag,
1540 is_leaf_flag,
1541 child_level,
1542 child_value_id,
1543 parent_flex_value_set_id,
1544 child_flex_value_set_id,
1545 SYSDATE,
1546 FII_USER_ID,
1547 SYSDATE,
1548 FII_USER_ID,
1549 FII_LOGIN_ID
1550 FROM FII_UDD2_HIER_GT;
1551
1552 IF (FIIDIM_Debug) THEN
1553 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FULL_UDD2_HIERS');
1554 END IF;
1555
1556 --Call FND_STATS to collect statistics after re-populating the tables.
1557 --for the full dimension table since it will be used later
1558 FND_STATS.gather_table_stats
1559 (ownname => g_schema_name,
1560 tabname => 'FII_FULL_UDD2_HIERS');
1561
1562 --==============================================================--
1563
1564 --Delete/Update FII_UDD2_HIER_GT for pruned hierarchy table
1565 g_phase := 'Delete/Update FII_UDD2_HIER_GT for pruned hierarchy table';
1566
1567 Get_Pruned_UD2_GT;
1568
1569 --Copy FII_UDD2_HIER_GT to the final (pruned) dimension table
1570 g_phase := 'Copy TMP hierarchy table to the final pruned dimension table';
1571
1572 -- Incremental Dimension Maintence
1573 -- All data is now in the temporary table FII_UDD2_HIER_GT,
1574 -- we need to maintain the permanent table FII_UDD2_HIERARCHIES
1575 -- by diffing the 2 tables.
1576 -- The maintenance is done by 2 statements, one INSERT and one DELETE.
1577
1578 DELETE FROM FII_UDD2_HIERARCHIES
1579 WHERE
1580 (parent_level, parent_value_id, next_level,
1581 next_level_value_id,
1582 next_level_is_leaf_flag, is_leaf_flag, child_level,
1583 child_value_id,
1584 parent_flex_value_set_id,
1585 child_flex_value_set_id,
1586 NVL(next_level_value_sort_order, -92883)) IN
1587 (SELECT parent_level, parent_value_id,
1588 next_level, next_level_value_id,
1589 next_level_is_leaf_flag, is_leaf_flag, child_level,
1590 child_value_id,parent_flex_value_set_id,
1591 child_flex_value_set_id, NVL(next_level_value_sort_order, -92883)
1592 FROM FII_UDD2_HIERARCHIES
1593 MINUS
1594 SELECT parent_level, parent_value_id,
1595 next_level, next_level_value_id,
1596 next_level_is_leaf_flag, is_leaf_flag,
1597 child_level, child_value_id,
1598 parent_flex_value_set_id,
1599 child_flex_value_set_id, NVL(next_level_value_sort_order, -92883)
1600 FROM FII_UDD2_HIER_GT);
1601
1602
1603 IF (FIIDIM_Debug) THEN
1604 FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_UDD2_HIERARCHIES');
1605 END IF;
1606
1607 Insert into FII_UDD2_HIERARCHIES (
1608 parent_level,
1609 parent_value_id,
1610 next_level,
1611 next_level_value_id,
1612 next_level_is_leaf_flag,
1613 is_leaf_flag,
1614 child_level,
1615 child_value_id,
1616 parent_flex_value_set_id,
1617 child_flex_value_set_id,
1618 aggregate_next_level_flag,
1619 next_level_value_sort_order,
1620 creation_date,
1621 created_by,
1622 last_update_date,
1623 last_updated_by,
1624 last_update_login)
1625 (SELECT parent_level,
1626 parent_value_id,
1627 next_level,
1628 next_level_value_id,
1629 next_level_is_leaf_flag,
1630 is_leaf_flag,
1631 child_level,
1632 child_value_id,
1633 parent_flex_value_set_id,
1634 child_flex_value_set_id,
1635 'N',
1636 next_level_value_sort_order,
1637 SYSDATE,
1638 FII_USER_ID,
1639 SYSDATE,
1640 FII_USER_ID,
1641 FII_LOGIN_ID
1642 FROM FII_UDD2_HIER_GT
1643 MINUS
1644 SELECT parent_level,
1645 parent_value_id,
1646 next_level,
1647 next_level_value_id,
1648 next_level_is_leaf_flag,
1649 is_leaf_flag,
1650 child_level,
1651 child_value_id,
1652 parent_flex_value_set_id,
1653 child_flex_value_set_id,
1654 'N',
1655 next_level_value_sort_order,
1656 SYSDATE,
1657 FII_USER_ID,
1658 SYSDATE,
1659 FII_USER_ID,
1660 FII_LOGIN_ID
1661 FROM FII_UDD2_HIERARCHIES);
1662
1663 IF (FIIDIM_Debug) THEN
1664 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD2_HIERARCHIES');
1665 END IF;
1666
1667 -- This will be in RSG data
1668 g_phase := 'gather_table_stats for FII_UDD2_HIERARCHIES';
1669 FND_STATS.gather_table_stats
1670 (ownname => g_schema_name,
1671 tabname => 'FII_UDD2_HIERARCHIES');
1672
1673 -- Bug 4200473. Not to analyze MLOG in incremental run.
1674 -- As per performance teams suggestions.
1675
1676 --g_phase := 'gather_table_stats for MLOG$_FII_UDD2_HIERARCHIES';
1677 --FND_STATS.gather_table_stats
1678 -- (ownname => g_schema_name,
1679 -- tabname => 'MLOG$_FII_UDD2_HIERARCHIES');
1680 --=============================================================--
1681
1682 --Populate FII_UDD2_MAPPINGS table
1683 g_phase := 'Populate FII_UDD2_MAPPINGS_GT table';
1684
1685 Get_UD2_Mapping_GT;
1686
1687 --Copy FII_UDD2_MAPPING_GT to FII_UDD2_MAPPINGS
1688 g_phase := 'Copy TMP UD2 Mapping table to the UD2 Mapping Table';
1689
1690 g_phase := 'DELETE FROM FII_UDD2_MAPPINGS';
1691
1692 DELETE FROM FII_UDD2_MAPPINGS
1693 WHERE
1694 (PARENT_USER_DIM2_ID, CHILD_USER_DIM2_ID) IN
1695 (SELECT PARENT_USER_DIM2_ID, CHILD_USER_DIM2_ID
1696 FROM FII_UDD2_MAPPINGS
1697 MINUS
1698 SELECT PARENT_USER_DIM2_ID, CHILD_USER_DIM2_ID
1699 FROM FII_UDD2_MAPPING_GT);
1700
1701 IF (FIIDIM_Debug) THEN
1702 FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_UDD2_MAPPINGS');
1703 END IF;
1704
1705 g_phase := 'Insert into FII_UDD2_MAPPINGS';
1706
1707 Insert into FII_UDD2_MAPPINGS (
1708 PARENT_USER_DIM2_ID,
1709 CHILD_USER_DIM2_ID,
1710 creation_date,
1711 created_by,
1712 last_update_date,
1713 last_updated_by,
1714 last_update_login)
1715 (SELECT PARENT_USER_DIM2_ID,
1716 CHILD_USER_DIM2_ID,
1717 SYSDATE,
1718 FII_USER_ID,
1719 SYSDATE,
1720 FII_USER_ID,
1721 FII_LOGIN_ID
1722 FROM FII_UDD2_MAPPING_GT
1723 MINUS
1724 SELECT PARENT_USER_DIM2_ID,
1725 CHILD_USER_DIM2_ID,
1726 SYSDATE,
1727 FII_USER_ID,
1728 SYSDATE,
1729 FII_USER_ID,
1730 FII_LOGIN_ID
1731 FROM FII_UDD2_MAPPINGS);
1732
1733 IF (FIIDIM_Debug) THEN
1734 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD2_MAPPINGS');
1735 END IF;
1736
1737 --Call FND_STATS to collect statistics after re-populating the table.
1738
1739 g_phase := 'gather_table_stats for FII_UDD2_MAPPINGS';
1740
1741 FND_STATS.gather_table_stats
1742 (ownname => g_schema_name,
1743 tabname => 'FII_UDD2_MAPPINGS');
1744
1745 -- Bug 4200473. Not to analyze MLOG in incremental run.
1746 -- As per performance teams suggestions.
1747
1748 -- g_phase := 'gather_table_stats for MLOG$_FII_UDD2_MAPPINGS';
1749
1750 -- FND_STATS.gather_table_stats
1751 -- (ownname => g_schema_name,
1752 -- tabname => 'MLOG$_FII_UDD2_MAPPINGS');
1753
1754 --=====================================================================
1755
1756
1757 FND_CONCURRENT.Af_Commit;
1758
1759 IF (FIIDIM_Debug) THEN
1760 FII_MESSAGE.Func_Succ(func_name => 'FII_UDD2_MAINTAIN_PKG.Incre_Load');
1761 END IF;
1762
1763 -- Exception handling
1764
1765 EXCEPTION
1766 WHEN UDIM2_fatal_err THEN
1767 FII_UTIL.Write_Log ('FII_UDD2_MAINTAIN_PKG.Incre_Update'||
1768 'User defined error');
1769
1770 FND_CONCURRENT.Af_Rollback;
1771 FII_MESSAGE.Func_Fail(func_name => 'FII_UDD2_MAINTAIN_PKG.Incre_Update');
1772 retcode := sqlcode;
1773 ret_val := FND_CONCURRENT.Set_Completion_Status
1774 (status => 'ERROR', message => substr(sqlerrm,1,180));
1775
1776 WHEN UDIM2_MULT_PAR_err THEN
1777 FII_UTIL.Write_Log ('FII_UDD2_MAINTAIN_PKG.Incre_Update: '||
1778 'Diamond Shape Detected');
1779
1780 FND_CONCURRENT.Af_Rollback;
1781 FII_MESSAGE.Func_Fail(func_name => 'FII_UDD2_MAINTAIN_PKG.Incre_Update');
1782 retcode := sqlcode;
1783 ret_val := FND_CONCURRENT.Set_Completion_Status
1784 (status => 'ERROR', message => substr(sqlerrm,1,180));
1785
1786 WHEN UDIM2_NOT_ENABLED THEN
1787 FII_UTIL.Write_Log ('>>> User Defined Dimension2 Not Enabled...');
1788
1789 Handle_Unenabled_DIM;
1790
1791 retcode := sqlcode;
1792 --ret_val := FND_CONCURRENT.Set_Completion_Status
1793 -- (status => 'NORMAL', message => NULL);
1794
1795 WHEN OTHERS THEN
1796 FII_UTIL.Write_Log ('Incre_Update -> phase: '|| g_phase);
1797 FII_UTIL.Write_Log (
1798 'Other error in FII_UDD2_MAINTAIN_PKG.Incre_Update: ' || substr(sqlerrm,1,180));
1799
1800
1801 FND_CONCURRENT.Af_Rollback;
1802 FII_MESSAGE.Func_Fail(func_name => 'FII_UDD2_MAINTAIN_PKG.Incre_Update');
1803 retcode := sqlcode;
1804 ret_val := FND_CONCURRENT.Set_Completion_Status
1805 (status => 'ERROR', message => substr(sqlerrm,1,180));
1806
1807 END Incre_Update;
1808
1809 END FII_UDD2_MAINTAIN_PKG;