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