[Home] [Help]
26: l_tab_name VARCHAR2(240) := NULL;
27:
28: BEGIN
29:
30: -- FII_MESSAGE.Func_Ent (func_name => 'Is_Table_Validated');
31:
32: -- Execute statement to determine if the value set is table validated
33: BEGIN
34:
42: WHEN NO_DATA_FOUND THEN
43: return FALSE;
44: END;
45:
46: -- FII_MESSAGE.Func_Succ (func_name => 'Is_Table_Validated');
47:
48: RETURN TRUE;
49:
50: EXCEPTION
61:
62: Begin
63:
64: if FIIDIM_Debug then
65: FII_MESSAGE.Func_Ent (func_name => 'Get_Value_Set_Name');
66: end if;
67:
68: select flex_value_set_name into l_vs_name
69: from fnd_flex_value_sets
69: from fnd_flex_value_sets
70: where flex_value_set_id = p_vs_id;
71:
72: if FIIDIM_Debug then
73: FII_MESSAGE.Func_Succ (func_name => 'Get_Value_Set_Name');
74: end if;
75:
76: return l_vs_name;
77:
93:
94: Begin
95:
96: if FIIDIM_Debug then
97: FII_MESSAGE.Func_Ent (func_name => 'Get_Flex_Value');
98: end if;
99:
100: select flex_value into l_flex_value
101: from fnd_flex_values
101: from fnd_flex_values
102: where flex_value_id = p_flex_value_id;
103:
104: if FIIDIM_Debug then
105: FII_MESSAGE.Func_Succ (func_name => 'Get_Flex_Value');
106: end if;
107:
108: return l_flex_value;
109:
243: END IF;
244:
245: --If the master value is not set up, raise an exception
246: IF G_MASTER_VALUE_SET_ID is NULL THEN
247: FII_MESSAGE.write_log (msg_name => 'FII_MSTR_VSET_NOT_FOUND',
248: token_num => 0);
249: FII_MESSAGE.write_output (msg_name => 'FII_MSTR_VSET_NOT_FOUND',
250: token_num => 0);
251: RAISE CODIM_fatal_err;
245: --If the master value is not set up, raise an exception
246: IF G_MASTER_VALUE_SET_ID is NULL THEN
247: FII_MESSAGE.write_log (msg_name => 'FII_MSTR_VSET_NOT_FOUND',
248: token_num => 0);
249: FII_MESSAGE.write_output (msg_name => 'FII_MSTR_VSET_NOT_FOUND',
250: token_num => 0);
251: RAISE CODIM_fatal_err;
252: --If the top node is not set up, raise an exception
253: ELSIF G_TOP_NODE_ID is NULL OR G_TOP_NODE_VALUE is NULL THEN
250: token_num => 0);
251: RAISE CODIM_fatal_err;
252: --If the top node is not set up, raise an exception
253: ELSIF G_TOP_NODE_ID is NULL OR G_TOP_NODE_VALUE is NULL THEN
254: FII_MESSAGE.write_log (msg_name => 'FII_MSTR_TNODE_NOT_FOUND',
255: token_num => 0);
256: FII_MESSAGE.write_output (msg_name => 'FII_MSTR_TNODE_NOT_FOUND',
257: token_num => 0);
258: RAISE CODIM_fatal_err;
252: --If the top node is not set up, raise an exception
253: ELSIF G_TOP_NODE_ID is NULL OR G_TOP_NODE_VALUE is NULL THEN
254: FII_MESSAGE.write_log (msg_name => 'FII_MSTR_TNODE_NOT_FOUND',
255: token_num => 0);
256: FII_MESSAGE.write_output (msg_name => 'FII_MSTR_TNODE_NOT_FOUND',
257: token_num => 0);
258: RAISE CODIM_fatal_err;
259: END IF;
260:
259: END IF;
260:
261: Exception
262: When NO_DATA_FOUND Then
263: FII_MESSAGE.write_log (msg_name => 'FII_MSTR_VSET_NOT_FOUND',
264: token_num => 0);
265: FII_MESSAGE.write_output (msg_name => 'FII_MSTR_VSET_NOT_FOUND',
266: token_num => 0);
267: RAISE CODIM_fatal_err;
261: Exception
262: When NO_DATA_FOUND Then
263: FII_MESSAGE.write_log (msg_name => 'FII_MSTR_VSET_NOT_FOUND',
264: token_num => 0);
265: FII_MESSAGE.write_output (msg_name => 'FII_MSTR_VSET_NOT_FOUND',
266: token_num => 0);
267: RAISE CODIM_fatal_err;
268: When TOO_MANY_ROWS Then
269: FII_UTIL.Write_Log ('More than one master value set found for COMPANY Dimension');
289: -- Check if the master value set is a table validated set.
290: g_phase := 'Check if the master value set is a table validated set';
291:
292: If Is_Table_Validated (G_MASTER_VALUE_SET_ID) Then
293: FII_MESSAGE.write_log (msg_name => 'FII_TBL_VALIDATED_VSET',
294: token_num => 1,
295: t1 => 'VS_NAME',
296: v1 => Get_Value_Set_Name (G_MASTER_VALUE_SET_ID));
297: FII_MESSAGE.write_output (msg_name => 'FII_TBL_VALIDATED_VSET',
293: FII_MESSAGE.write_log (msg_name => 'FII_TBL_VALIDATED_VSET',
294: token_num => 1,
295: t1 => 'VS_NAME',
296: v1 => Get_Value_Set_Name (G_MASTER_VALUE_SET_ID));
297: FII_MESSAGE.write_output (msg_name => 'FII_TBL_VALIDATED_VSET',
298: token_num => 1,
299: t1 => 'VS_NAME',
300: v1 => Get_Value_Set_Name (G_MASTER_VALUE_SET_ID));
301: RAISE CODIM_fatal_err;
315:
316: When CODIM_fatal_err then
317: FII_UTIL.Write_Log ('FII_COM_MAINTAIN_PKG.Initialize : '|| 'User defined error');
318: FND_CONCURRENT.Af_Rollback;
319: FII_MESSAGE.Func_Fail(func_name => 'FII_COM_MAINTAIN_PKG.Initialize');
320: raise;
321:
322: When others then
323: FII_UTIL.Write_Log ('Unexpected error when calling Initialize...');
352:
353: BEGIN
354:
355: IF (FIIDIM_Debug) THEN
356: FII_MESSAGE.Func_Ent ('FII_COM_MAINTAIN_PKG.Get_NORM_HIERARCHY_TMP');
357: END IF;
358:
359: --First, insert records for the master value set
360: g_phase := 'insert records for the master value set';
415: l_vset_id := vset_rec.child_flex_value_set_id;
416:
417: -- Check if the (child) value set is a table validated set.
418: If Is_Table_Validated (l_vset_id) Then
419: FII_MESSAGE.write_log (msg_name => 'FII_TBL_VALIDATED_VSET',
420: token_num => 1,
421: t1 => 'VS_NAME',
422: v1 => Get_Value_Set_Name (l_vset_id));
423: FII_MESSAGE.write_output (msg_name => 'FII_TBL_VALIDATED_VSET',
419: FII_MESSAGE.write_log (msg_name => 'FII_TBL_VALIDATED_VSET',
420: token_num => 1,
421: t1 => 'VS_NAME',
422: v1 => Get_Value_Set_Name (l_vset_id));
423: FII_MESSAGE.write_output (msg_name => 'FII_TBL_VALIDATED_VSET',
424: token_num => 1,
425: t1 => 'VS_NAME',
426: v1 => Get_Value_Set_Name (l_vset_id));
427: RAISE CODIM_fatal_err;
453: (ownname => g_schema_name,
454: tabname => 'FII_DIM_NORM_HIER_GT');
455:
456: IF (FIIDIM_Debug) THEN
457: FII_MESSAGE.Func_Succ ('FII_COM_MAINTAIN_PKG.Get_NORM_HIERARCHY_TMP');
458: END IF;
459:
460: Exception
461:
464:
465:
466: 'User defined error');
467: FND_CONCURRENT.Af_Rollback;
468: FII_MESSAGE.Func_Fail(func_name => 'FII_COM_MAINTAIN_PKG.Get_NORM_HIERARCHY_TMP');
469: raise;
470:
471: When others then
472: FII_UTIL.Write_Log ('Unexpected error when calling Get_NORM_HIERARCHY_TMP.');
514:
515: BEGIN
516:
517: IF (FIIDIM_Debug) THEN
518: FII_MESSAGE.Func_Ent ('FII_COM_MAINTAIN_PKG.Detect_Diamond_Shape');
519: END IF;
520:
521: -- check whole TMP hierarhy table: if there is a diamond
522: -- report and raise an exception
526:
527: l_count := l_count + 1;
528: if l_count = 1 then
529:
530: FII_MESSAGE.write_log(msg_name => 'FII_DMND_SHAPE_VS_EXIST',
531: token_num => 0);
532: FII_MESSAGE.write_log(msg_name => 'FII_REFER_TO_OUTPUT',
533: token_num => 0);
534:
528: if l_count = 1 then
529:
530: FII_MESSAGE.write_log(msg_name => 'FII_DMND_SHAPE_VS_EXIST',
531: token_num => 0);
532: FII_MESSAGE.write_log(msg_name => 'FII_REFER_TO_OUTPUT',
533: token_num => 0);
534:
535: FII_MESSAGE.write_output (msg_name => 'FII_DMND_SHAPE_VS_EXIST',
536: token_num => 0);
531: token_num => 0);
532: FII_MESSAGE.write_log(msg_name => 'FII_REFER_TO_OUTPUT',
533: token_num => 0);
534:
535: FII_MESSAGE.write_output (msg_name => 'FII_DMND_SHAPE_VS_EXIST',
536: token_num => 0);
537:
538: FII_MESSAGE.write_output (msg_name => 'FII_DMND_SHAPE_VS_TAB',
539: token_num => 0);
534:
535: FII_MESSAGE.write_output (msg_name => 'FII_DMND_SHAPE_VS_EXIST',
536: token_num => 0);
537:
538: FII_MESSAGE.write_output (msg_name => 'FII_DMND_SHAPE_VS_TAB',
539: token_num => 0);
540:
541: end if;
542:
557:
558: END LOOP;
559:
560: IF (FIIDIM_Debug) THEN
561: FII_MESSAGE.Func_Succ ('FII_COM_MAINTAIN_PKG.Detect_Diamond_Shape');
562: END IF;
563:
564: IF l_count > 0 THEN
565: RAISE CODIM_MULT_PAR_err;
682:
683: EXCEPTION
684: WHEN NO_DATA_FOUND Then
685: FII_UTIL.Write_Log ('Insert Immediate child: No Data Found');
686: FII_MESSAGE.Func_Fail
687: (func_name => 'FII_COM_MAINTAIN_PKG.Insert_Imm_Child_Nodes');
688: RAISE;
689:
690: WHEN OTHERS Then
688: RAISE;
689:
690: WHEN OTHERS Then
691: FII_UTIL.Write_Log (substr(SQLERRM,1,180));
692: FII_MESSAGE.Func_Fail
693: (func_name => 'FII_COM_MAINTAIN_PKG.INSERT_IMM_CHILD_NODES');
694: RAISE;
695:
696: END INSERT_IMM_CHILD_NODES;
711:
712: BEGIN
713:
714: IF (FIIDIM_Debug) THEN
715: FII_MESSAGE.Func_Ent(func_name => 'FII_COM_MAINTAIN_PKG.'||
716: 'Flatten_CO_Dim_Hier');
717: END IF;
718: g_phase := 'Truncate table FII_COMPANY_HIER_GT';
719: FII_UTIL.truncate_table ('FII_COMPANY_HIER_GT', 'FII', g_retcode);
913: -- for the full hierarchy since it's not used anywhere
914: ----------------------------------------------------------------------
915:
916: IF (FIIDIM_Debug) THEN
917: FII_MESSAGE.Func_Succ(func_name => 'FII_CO_MAINTAIN_PKG.'||
918: 'Flatten_CO_Dim_Hier');
919: END IF;
920:
921: EXCEPTION
921: EXCEPTION
922:
923: WHEN NO_DATA_FOUND THEN
924: FII_UTIL.Write_Log ('Flatten_COM_Dim_Hier: No Data Found');
925: FII_MESSAGE.Func_Fail(func_name => 'FII_COM_MAINTAIN_PKG.'||
926: 'Flatten_CO_Dim_Hier');
927: raise;
928:
929: WhEN CODIM_MULT_PAR_err THEN
927: raise;
928:
929: WhEN CODIM_MULT_PAR_err THEN
930: FII_UTIL.Write_Log ('Flatten_CO_Dim_Hier: Diamond Shape Detected');
931: FII_MESSAGE.Func_Fail (func_name =>
932: 'FII_DIMENSION_MAINTAIN_PKG.Flatten_CO_Dim_Hier');
933: raise;
934:
935: WHEN OTHERS THEN
933: raise;
934:
935: WHEN OTHERS THEN
936: FII_UTIL.Write_Log ('Flatten_CO_Dim_Hier: '|| substr(sqlerrm,1,180));
937: FII_MESSAGE.Func_Fail(func_name => 'FII_COM_MAINTAIN_PKG.'||
938: 'Flatten_CO_Dim_Hier');
939: FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
940: raise;
941:
1013:
1014: Begin
1015:
1016: IF (FIIDIM_Debug) THEN
1017: FII_MESSAGE.Func_Ent(func_name => 'FII_COM_MAINTAIN_PKG.'||
1018: 'Get_Pruned_CO_GT');
1019: END IF;
1020: --Delete from FII_company_HIER_GT for child value set not equal to
1021: --the master value set and not equal to the UNASSIGNED value set.
1065: FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COMPANY_HIER_GT');
1066: END IF;
1067:
1068: IF (FIIDIM_Debug) THEN
1069: FII_MESSAGE.Func_Succ(func_name => 'FII_COM_MAINTAIN_PKG.'||
1070: 'Get_Pruned_CO_GT');
1071: END IF;
1072: EXCEPTION
1073:
1073:
1074: WHEN OTHERS THEN
1075: FII_UTIL.Write_Log ('Get_Pruned_CO_GT -> phase: '|| g_phase);
1076: FII_UTIL.Write_Log ('Get_Pruned_CO_GT: '|| substr(sqlerrm,1,180));
1077: FII_MESSAGE.Func_Fail(func_name => 'FII_COM_MAINTAIN_PKG.'||
1078: 'Get_Pruned_CO_GT');
1079: raise;
1080:
1081: END Get_Pruned_CO_GT;
1090:
1091: Begin
1092:
1093: IF (FIIDIM_Debug) THEN
1094: FII_MESSAGE.Func_Ent(func_name => 'FII_COM_MAINTAIN_PKG.'||
1095: 'Handle_Unenabled_DIM');
1096: END IF;
1097: -- Bug 4147558.
1098: g_phase := 'Check if the dimension was already disabled';
1193: commit;
1194: END IF;
1195:
1196: IF (FIIDIM_Debug) THEN
1197: FII_MESSAGE.Func_Succ(func_name => 'FII_COM_MAINTAIN_PKG.'||
1198: 'Handle_Unenabled_DIM');
1199: END IF;
1200: EXCEPTION
1201:
1200: EXCEPTION
1201:
1202: WHEN OTHERS THEN
1203: FII_UTIL.Write_Log ('Handle_Unenabled_DIM: '|| substr(sqlerrm,1,180));
1204: FII_MESSAGE.Func_Fail(func_name => 'FII_COM_MAINTAIN_PKG.'||
1205: 'Handle_Unenabled_DIM');
1206: raise;
1207:
1208: END Handle_Unenabled_DIM;
1218:
1219: BEGIN
1220:
1221: IF (FIIDIM_Debug) THEN
1222: FII_MESSAGE.Func_Ent(func_name => 'FII_COM_MAINTAIN_PKG.Init_Load');
1223: END IF;
1224:
1225: g_load_mode := 'INIT';
1226:
1366:
1367: FND_CONCURRENT.Af_Commit;
1368:
1369: IF (FIIDIM_Debug) THEN
1370: FII_MESSAGE.Func_Succ(func_name => 'FII_COM_MAINTAIN_PKG.Init_Load');
1371: END IF;
1372:
1373: -- Exception handling
1374:
1379: FII_UTIL.Write_Log ('FII_COM_MAINTAIN_PKG.Init_Load: '||
1380: 'User defined error');
1381:
1382: FND_CONCURRENT.Af_Rollback;
1383: FII_MESSAGE.Func_Fail(func_name => 'FII_COM_MAINTAIN_PKG.Init_Load');
1384: retcode := sqlcode;
1385: ret_val := FND_CONCURRENT.Set_Completion_Status
1386: (status => 'ERROR', message => substr(sqlerrm,1,180));
1387:
1389: FII_UTIL.Write_Log ('FII_COM_MAINTAIN_PKG.Init_Load: '||
1390: 'Diamond Shape Detected');
1391:
1392: FND_CONCURRENT.Af_Rollback;
1393: FII_MESSAGE.Func_Fail(func_name => 'FII_COM_MAINTAIN_PKG.Init_Load');
1394: retcode := sqlcode;
1395: ret_val := FND_CONCURRENT.Set_Completion_Status
1396: (status => 'ERROR', message => substr(sqlerrm,1,180));
1397:
1410: 'Other error in FII_COM_MAINTAIN_PKG.Init_Load: ' || substr(sqlerrm,1,180));
1411:
1412:
1413: FND_CONCURRENT.Af_Rollback;
1414: FII_MESSAGE.Func_Fail(func_name => 'FII_COM_MAINTAIN_PKG.Init_Load');
1415: retcode := sqlcode;
1416: ret_val := FND_CONCURRENT.Set_Completion_Status
1417: (status => 'ERROR', message => substr(sqlerrm,1,180));
1418:
1430:
1431: BEGIN
1432:
1433: IF (FIIDIM_Debug) THEN
1434: FII_MESSAGE.Func_Ent(func_name => 'FII_COM_MAINTAIN_PKG.Incre_Update');
1435: END IF;
1436:
1437: g_load_mode := 'INCR';
1438:
1639:
1640: FND_CONCURRENT.Af_Commit;
1641:
1642: IF (FIIDIM_Debug) THEN
1643: FII_MESSAGE.Func_Succ(func_name => 'FII_COM_MAINTAIN_PKG.Incre_Update');
1644: END IF;
1645:
1646: -- Exception handling
1647:
1650: FII_UTIL.Write_Log ('FII_COM_MAINTAIN_PKG.Incre_Update'||
1651: 'User defined error');
1652:
1653: FND_CONCURRENT.Af_Rollback;
1654: FII_MESSAGE.Func_Fail(func_name => 'FII_COM_MAINTAIN_PKG.Incre_Update');
1655: retcode := sqlcode;
1656: ret_val := FND_CONCURRENT.Set_Completion_Status
1657: (status => 'ERROR', message => substr(sqlerrm,1,180));
1658:
1660: FII_UTIL.Write_Log ('FII_COM_MAINTAIN_PKG.Incre_Update: '||
1661: 'Diamond Shape Detected');
1662:
1663: FND_CONCURRENT.Af_Rollback;
1664: FII_MESSAGE.Func_Fail(func_name => 'FII_COM_MAINTAIN_PKG.Incre_Update');
1665: retcode := sqlcode;
1666: ret_val := FND_CONCURRENT.Set_Completion_Status
1667: (status => 'ERROR', message => substr(sqlerrm,1,180));
1668:
1681: 'Other error in FII_COM_MAINTAIN_PKG.Incre_Update: ' || substr(sqlerrm,1,180));
1682:
1683:
1684: FND_CONCURRENT.Af_Rollback;
1685: FII_MESSAGE.Func_Fail(func_name => 'FII_COM_MAINTAIN_PKG.Incre_Update');
1686: retcode := sqlcode;
1687: ret_val := FND_CONCURRENT.Set_Completion_Status
1688: (status => 'ERROR', message => substr(sqlerrm,1,180));
1689: