[Home] [Help]
1: PACKAGE BODY GL_FLATTEN_SEG_VAL_HIERARCHIES AS
2: /* $Header: gluflshb.pls 120.11.12010000.2 2008/08/04 21:52:54 bsrikant ship $ */
3:
4:
5: -- ********************************************************************
40:
41: BEGIN
42:
43: GL_MESSAGE.Func_Ent (func_name =>
44: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier');
45:
46: -- The flow of the Flatten_Seg_Val_Hier routine is as follows
47: -- First cleans all records with status 'I'and updates records with
48: -- status_code 'D'to NULL in GL_SEG_VAL_HIERARCHIES.
60: GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
61: token_num => 2,
62: t1 =>'ROUTINE',
63: v1 =>
64: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
65: t2 =>'ACTION',
66: v2 =>'Deleting records with status code I '
67: || 'in the table GL_SEG_VAL_NORM_HIERARCHY');
68: END IF;
89: GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
90: token_num => 2,
91: t1 =>'ROUTINE',
92: v1 =>
93: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
94: t2 => 'ACTION',
95: v2 =>'Updating records with status code D '
96: ||'to status code NULL in the table'
97: ||' GL_SEG_VAL_NORM_HIERARCHY');
117: GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
118: token_num => 2,
119: t1 =>'ROUTINE',
120: v1 =>
121: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
122: t2 =>'ACTION',
123: v2 =>'Deleting records with status code I '
124: || 'in the table GL_SEG_VAL_HIERARCHIES');
125: END IF;
145: GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
146: token_num => 2,
147: t1 =>'ROUTINE',
148: v1 =>
149: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
150: t2 => 'ACTION',
151: v2 =>'Updating records with status code D '
152: ||'to status code NULL in the table'
153: ||' GL_SEG_VAL_HIERARCHIES');
313: GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
314: token_num => 3 ,
315: t1 =>'ROUTINE',
316: v1 =>
317: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
318: t2 =>'VARIABLE',
319: v2 =>'Value_Set_Id',
320: t3 =>'VALUE',
321: v3 => GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID);
323: GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
324: token_num => 3 ,
325: t1 =>'ROUTINE',
326: v1 =>
327: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
328: t2 =>'VARIABLE',
329: v2 =>'Table Name',
330: t3 =>'VALUE',
331: v3 => add_table);
333: GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
334: token_num => 3 ,
335: t1 =>'ROUTINE',
336: v1 =>
337: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
338: t2 =>'VARIABLE',
339: v2 =>'Column Name',
340: t3 =>'VALUE',
341: v3 => GL_FLATTEN_SETUP_DATA.GLSTFL_VS_COL_NAME);
343: GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
344: token_num => 3 ,
345: t1 =>'ROUTINE',
346: v1 =>
347: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
348: t2 =>'VARIABLE',
349: v2 =>'sqlbuf',
350: t3 =>'VALUE',
351: v3 => sqlbuf);
353: GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
354: token_num => 3 ,
355: t1 =>'ROUTINE',
356: v1 =>
357: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
358: t2 =>'VARIABLE',
359: v2 =>'Length of sql_stmt',
360: t3 =>'VALUE',
361: v3 =>l_sql_stmt);
363: GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
364: token_num => 2,
365: t1 =>'ROUTINE',
366: v1 =>
367: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
368: t2 => 'ACTION',
369: v2 =>'Inserting ''T'' records for table validated'
370: ||' value set into the table '
371: ||' GL_SEG_VAL_HIERARCHIES');
408: GL_MESSAGE.Write_Log(msg_name =>'FLAT0001',
409: token_num => 1,
410: t1 =>'ROUTINE_NAME',
411: v1 =>
412: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()');
413: ELSE
414:
415: IF (NOT Fix_Flattened_Table (Is_Flattened_Tab_Changed =>
416: l_Seg_Hier_Flat_Changed )) THEN
421: GL_MESSAGE.Write_Log(msg_name =>'FLAT0001',
422: token_num => 1,
423: t1 =>'ROUTINE_NAME',
424: v1 =>
425: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()');
426:
427: ELSE
428:
429: -- Return True Only if any changes processed in Flattened table.
434:
435: END IF; -- Outer Fix_Norm_Table If control statement ends.
436:
437: GL_MESSAGE.Func_Succ(func_name =>
438: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier');
439:
440: RETURN TRUE;
441:
442: EXCEPTION
446: GL_MESSAGE.Write_Log(msg_name =>'FLAT0002',
447: token_num => 1,
448: t1 =>'ROUTINE_NAME',
449: v1 =>
450: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()');
451:
452: GL_MESSAGE.Func_Fail(func_name =>
453: 'GL_FLATTEN_SEG_VAL_HIERARCHIE.Flatten_Seg_Val_Hier');
454:
468: t2 =>'SQLERRMC',
469: v2 => SQLERRM);
470:
471: GL_MESSAGE.Func_Fail(func_name =>
472: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier');
473:
474: FND_CONCURRENT.Af_Rollback; -- Rollback Point
475:
476: Is_Seg_Hier_Changed := FALSE;
505:
506: BEGIN
507:
508: GL_MESSAGE.FUNC_ENT(func_name =>
509: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Norm_Table');
510:
511: -- The following check allows the program to continue processing
512: -- even there are no hierarchy changes occur in the case of
513: -- inserting a new value into the value set. If the new value is
537: GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
538: token_num => 2,
539: t1 =>'ROUTINE',
540: v1 =>
541: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Norm_Table()',
542: t2 =>'ACTION',
543: v2 =>'Mark all outdated parent-child '
544: ||' segment value mappings'
545: ||' in the table '
598: GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
599: token_num => 3 ,
600: t1 =>'ROUTINE',
601: v1 =>
602: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Norm_Table()',
603: t2 =>'VARIABLE',
604: v2 =>'Value_Set_Id',
605: t3 =>'VALUE',
606: v3 =>GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID);
608: GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
609: token_num => 2,
610: t1 =>'ROUTINE',
611: v1 =>
612: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Norm_Table()',
613: t2 => 'ACTION',
614: v2 =>'Insert any new parent-child'
615: ||' segment value mappings into'
616: ||' the table'
673: t2 => 'TABLE',
674: v2 => 'GL_SEG_VAL_NORM_HIERARCHY');
675:
676: GL_MESSAGE.Func_Succ(func_name =>
677: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Norm_Table');
678:
679: FND_CONCURRENT.Af_Commit; --COMMIT point
680:
681: RETURN TRUE;
689: t1 =>'EMESSAGE',
690: v1 => SQLERRM);
691:
692: GL_MESSAGE.Func_Fail(func_name =>
693: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Norm_Table');
694:
695: FND_CONCURRENT.Af_Rollback; -- Rollback Point
696:
697: RETURN FALSE;
723:
724: BEGIN
725:
726: GL_MESSAGE.Func_Ent(func_name =>
727: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flattened_Table');
728:
729: Is_Flattened_Tab_Changed := FALSE;
730:
731: -- The following SQL statement determine all records in
768: GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
769: token_num =>2,
770: t1 =>'ROUTINE',
771: v1 =>
772: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flattened_Table()',
773: t2 =>'ACTION',
774: v2 =>'Updating all mapping(s) that'
775: ||' contain deleted child via'
776: ||' deleted parent in the'
792: GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
793: token_num =>2,
794: t1 =>'ROUTINE',
795: v1 =>
796: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flattened_Table()',
797: t2 =>'ACTION',
798: v2 =>'Updating all mapping(s) that'
799: ||' contain deleted parent child'
800: ||' and its descendants in the'
853: GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
854: token_num =>2,
855: t1 =>'ROUTINE',
856: v1 =>
857: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flattened_Table()',
858: t2 =>'ACTION',
859: v2 =>'Reconnecting mapping(s) that if '
860: ||' any deleted child mapped to the'
861: ||' parent through someother path');
927: GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
928: token_num =>2,
929: t1 =>'ROUTINE',
930: v1 =>
931: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flattened_Table()',
932: t2 =>'ACTION',
933: v2 =>'Inserting Parent segement value'
934: ||' with itself as child into the table'
935: ||' GL_SEG_VAL_HIERARCHIES');
984: GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
985: token_num =>2,
986: t1 =>'ROUTINE',
987: v1 =>
988: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flattened_Table()',
989: t2 =>'ACTION',
990: v2 =>'Inserting deatail child mapping(s)'
991: ||' into table GL_SEG_VAL_HIERARCHIES');
992: END IF;
1051: GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
1052: token_num =>2,
1053: t1 =>'ROUTINE',
1054: v1 =>
1055: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flattened_Table()',
1056: t2 =>'ACTION',
1057: v2 =>'Inserting Parent child mapping(s)'
1058: ||'into table GL_SEG_VAL_HIERARCHIES');
1059: END IF;
1128: t2 =>'TABLE',
1129: v2 => 'GL_SEG_VAL_HIERARCHIES');
1130:
1131: GL_MESSAGE.Func_Succ(func_name =>
1132: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flattend_Table');
1133:
1134: RETURN TRUE;
1135:
1136: EXCEPTION
1141: t1 =>'EMESSAGE',
1142: v1 => SQLERRM);
1143:
1144: GL_MESSAGE.Func_Fail(func_name =>
1145: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flatten_Table');
1146:
1147: FND_CONCURRENT.Af_Rollback; -- ROLLBACK Ponit
1148:
1149: RETURN FALSE;
1170: l_status VARCHAR2(1);
1171:
1172: BEGIN
1173: GL_MESSAGE.Func_Ent(func_name =>
1174: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Clean_Up');
1175:
1176: IF(GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1177:
1178: GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
1178: GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
1179: token_num => 3,
1180: t1 => 'ROUTINE',
1181: v1 =>
1182: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Clean_Up',
1183: t2 =>'VARIABLE',
1184: v2 => 'Value_Set_Id',
1185: t3 => 'VALUE',
1186: v3 =>
1243: t2 =>'TABLE',
1244: v2 => 'GL_SEG_VAL_HIERARCHIES');
1245:
1246: GL_MESSAGE.Func_Succ(func_name =>
1247: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Clean_Up');
1248:
1249: RETURN TRUE;
1250:
1251: EXCEPTION
1259:
1260: FND_CONCURRENT.Af_Rollback; -- ROLLBACK point
1261:
1262: GL_MESSAGE.Func_Fail(func_name =>
1263: 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Clean_Up');
1264:
1265: RETURN FALSE;
1266:
1267: END Clean_Up;
1268:
1269:
1270: -- ******************************************************************
1271:
1272: END GL_FLATTEN_SEG_VAL_HIERARCHIES;