48:
49: PROCEDURE Flatten_Focus_Node
50: (
51: p_api_version IN NUMBER ,
52: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
53: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
54: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
55: x_return_status OUT NOCOPY VARCHAR2 ,
56: x_msg_count OUT NOCOPY NUMBER ,
49: PROCEDURE Flatten_Focus_Node
50: (
51: p_api_version IN NUMBER ,
52: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
53: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
54: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
55: x_return_status OUT NOCOPY VARCHAR2 ,
56: x_msg_count OUT NOCOPY NUMBER ,
57: x_msg_data OUT NOCOPY VARCHAR2 ,
50: (
51: p_api_version IN NUMBER ,
52: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
53: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
54: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
55: x_return_status OUT NOCOPY VARCHAR2 ,
56: x_msg_count OUT NOCOPY NUMBER ,
57: x_msg_data OUT NOCOPY VARCHAR2 ,
58: --
113: BEGIN
114: --
115: SAVEPOINT Flatten_Focus_Node_Pvt ;
116: --
117: IF NOT FND_API.Compatible_API_Call ( l_api_version,
118: p_api_version,
119: l_api_name,
120: G_PKG_NAME )
121: THEN
118: p_api_version,
119: l_api_name,
120: G_PKG_NAME )
121: THEN
122: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
123: END IF;
124: --
125:
126: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
122: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
123: END IF;
124: --
125:
126: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
127: FND_MSG_PUB.initialize ;
128: END IF;
129: --
130: x_return_status := FND_API.G_RET_STS_SUCCESS ;
126: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
127: FND_MSG_PUB.initialize ;
128: END IF;
129: --
130: x_return_status := FND_API.G_RET_STS_SUCCESS ;
131: --
132:
133: l_flattened_rows_flag := 'N';
134:
143: if (l_flattened_rows_flag = 'N') then
144: FND_MESSAGE.SET_NAME('FEM','FEM_DHM_CANNOT_FLATTEN_HIER');
145: FND_MESSAGE.SET_TOKEN('HIER_DEFN_NAME',l_hier_defn_name );
146: FND_MSG_PUB.Add;
147: RAISE FND_API.G_EXC_ERROR ;
148: end if;
149:
150: if (p_focus_value_set_id is not null) then
151:
396: -- Down below are again the standard end and exception sections of the API.
397: --
398:
399: --
400: IF FND_API.To_Boolean ( p_commit ) THEN
401: COMMIT WORK;
402: END iF;
403: --
404: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
405: p_data => x_msg_data );
406: --
407: EXCEPTION
408: --
409: WHEN FND_API.G_EXC_ERROR THEN
410: --
411: ROLLBACK TO Flatten_Focus_Node_Pvt ;
412: x_return_status := FND_API.G_RET_STS_ERROR;
413: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
408: --
409: WHEN FND_API.G_EXC_ERROR THEN
410: --
411: ROLLBACK TO Flatten_Focus_Node_Pvt ;
412: x_return_status := FND_API.G_RET_STS_ERROR;
413: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
414: p_data => x_msg_data );
415: --
416: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
412: x_return_status := FND_API.G_RET_STS_ERROR;
413: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
414: p_data => x_msg_data );
415: --
416: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
417: --
418: ROLLBACK TO Flatten_Focus_Node_Pvt ;
419: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
420: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
415: --
416: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
417: --
418: ROLLBACK TO Flatten_Focus_Node_Pvt ;
419: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
420: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
421: p_data => x_msg_data );
422: --
423: WHEN OTHERS THEN
422: --
423: WHEN OTHERS THEN
424: --
425: ROLLBACK TO Flatten_Focus_Node_Pvt ;
426: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
427: --
428: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
429: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
430: l_api_name);
450:
451: PROCEDURE Flatten_Whole_Hier_Version
452: (
453: p_api_version IN NUMBER ,
454: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
455: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
456: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
457: x_return_status OUT NOCOPY VARCHAR2 ,
458: x_msg_count OUT NOCOPY NUMBER ,
451: PROCEDURE Flatten_Whole_Hier_Version
452: (
453: p_api_version IN NUMBER ,
454: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
455: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
456: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
457: x_return_status OUT NOCOPY VARCHAR2 ,
458: x_msg_count OUT NOCOPY NUMBER ,
459: x_msg_data OUT NOCOPY VARCHAR2 ,
452: (
453: p_api_version IN NUMBER ,
454: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
455: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
456: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
457: x_return_status OUT NOCOPY VARCHAR2 ,
458: x_msg_count OUT NOCOPY NUMBER ,
459: x_msg_data OUT NOCOPY VARCHAR2 ,
460: --
496: l_child_depth_num number;
497: l_child_valueset_id number;
498: l_display_order_num number;
499: l_weighting_pct number;
500: l_flattened varchar2(6) := FND_API.G_FALSE;
501: --
502: l_api_name CONSTANT VARCHAR2(30) := 'Flatten_Whole_Hier_Version' ;
503: l_api_version CONSTANT NUMBER := 1.0;
504:
511:
512: -- Bug # 3562336 : Removed Savepoint as the commit is now being
513: -- done in Flatten_Focus_Node_Tree after every merged row
514:
515: IF NOT FND_API.Compatible_API_Call ( l_api_version,
516: p_api_version,
517: l_api_name,
518: G_PKG_NAME )
519: THEN
516: p_api_version,
517: l_api_name,
518: G_PKG_NAME )
519: THEN
520: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
521: END IF;
522: --
523:
524: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
520: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
521: END IF;
522: --
523:
524: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
525: FND_MSG_PUB.initialize ;
526: END IF;
527: --
528: x_return_status := FND_API.G_RET_STS_SUCCESS ;
524: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
525: FND_MSG_PUB.initialize ;
526: END IF;
527: --
528: x_return_status := FND_API.G_RET_STS_SUCCESS ;
529: --
530:
531: l_flattened_rows_flag := 'N';
532:
552: if (l_flattened_rows_flag = 'N') then
553: FND_MESSAGE.SET_NAME('FEM','FEM_DHM_CANNOT_FLATTEN_HIER');
554: FND_MESSAGE.SET_TOKEN('HIER_DEFN_NAME',l_hier_defn_name );
555: FND_MSG_PUB.Add;
556: RAISE FND_API.G_EXC_ERROR ;
557: end if;
558:
559: For l_dim_csr_rec in
560: (
582:
583: Loop
584: Fetch dhm_flat_cur into l_flat_count;
585: if l_flat_count <> 0 then
586: l_flattened := FND_API.G_TRUE;
587: end if;
588: exit;
589: End Loop;
590:
615: p_api_version => 1.0,
616: x_return_status => l_return_status,
617: x_msg_count => l_msg_count,
618: x_msg_data => l_msg_data,
619: p_commit => FND_API.G_TRUE,
620: --
621: p_hier_obj_defn_id => p_hier_obj_defn_id,
622: p_hier_table_name => l_hier_table_name,
623: p_focus_node => l_top_nodes_tbl(i),
624: p_focus_value_set_id => ltopvsids(i)
625: ) ;
626:
627: -- Bug # 3562336 : Changed the return status check to
628: -- "<> FND_API.G_RET_STS_SUCCESS " from "= FND_API.G_RET_STS_ERROR"
629:
630: if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
631: RAISE FND_API.G_EXC_ERROR ;
632: end if;
626:
627: -- Bug # 3562336 : Changed the return status check to
628: -- "<> FND_API.G_RET_STS_SUCCESS " from "= FND_API.G_RET_STS_ERROR"
629:
630: if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
631: RAISE FND_API.G_EXC_ERROR ;
632: end if;
633:
634: End Loop; -- For Loop Top Nodes
627: -- Bug # 3562336 : Changed the return status check to
628: -- "<> FND_API.G_RET_STS_SUCCESS " from "= FND_API.G_RET_STS_ERROR"
629:
630: if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
631: RAISE FND_API.G_EXC_ERROR ;
632: end if;
633:
634: End Loop; -- For Loop Top Nodes
635: End Loop; -- Outer Loop Top Nodes
659: p_api_version => 1.0,
660: x_return_status => l_return_status,
661: x_msg_count => l_msg_count,
662: x_msg_data => l_msg_data,
663: p_commit => FND_API.G_TRUE,
664: --
665: p_hier_obj_defn_id => p_hier_obj_defn_id,
666: p_hier_table_name => l_hier_table_name,
667: p_focus_node => l_top_nodes_tbl(i),
668: p_focus_value_set_id => NULL
669: ) ;
670:
671: -- Bug # 3562336 : Changed the return status check to
672: -- "<> FND_API.G_RET_STS_SUCCESS " from "= FND_API.G_RET_STS_ERROR"
673:
674: if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
675: RAISE FND_API.G_EXC_ERROR ;
676: end if;
670:
671: -- Bug # 3562336 : Changed the return status check to
672: -- "<> FND_API.G_RET_STS_SUCCESS " from "= FND_API.G_RET_STS_ERROR"
673:
674: if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
675: RAISE FND_API.G_EXC_ERROR ;
676: end if;
677:
678: End Loop; -- For Loop Top Nodes
671: -- Bug # 3562336 : Changed the return status check to
672: -- "<> FND_API.G_RET_STS_SUCCESS " from "= FND_API.G_RET_STS_ERROR"
673:
674: if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
675: RAISE FND_API.G_EXC_ERROR ;
676: end if;
677:
678: End Loop; -- For Loop Top Nodes
679: End Loop; -- Outer Loop Top Nodes
697: -- Down below are again the standard end and exception sections of the API.
698: --
699:
700: --
701: IF FND_API.To_Boolean ( p_commit ) THEN
702: COMMIT WORK;
703: END iF;
704: --
705: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
713:
714: -- Bug # 3562336 : Remove Rollback statement as Savepoint is not
715: -- needed anymore
716:
717: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
718: --
719: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
720: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
721: l_api_name);
741:
742: PROCEDURE Flatten_Focus_Node_Tree
743: (
744: p_api_version IN NUMBER ,
745: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
746: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
747: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
748: x_return_status OUT NOCOPY VARCHAR2 ,
749: x_msg_count OUT NOCOPY NUMBER ,
742: PROCEDURE Flatten_Focus_Node_Tree
743: (
744: p_api_version IN NUMBER ,
745: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
746: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
747: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
748: x_return_status OUT NOCOPY VARCHAR2 ,
749: x_msg_count OUT NOCOPY NUMBER ,
750: x_msg_data OUT NOCOPY VARCHAR2 ,
743: (
744: p_api_version IN NUMBER ,
745: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
746: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
747: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
748: x_return_status OUT NOCOPY VARCHAR2 ,
749: x_msg_count OUT NOCOPY NUMBER ,
750: x_msg_data OUT NOCOPY VARCHAR2 ,
751: --
803: -- Bug # 3562336 : Removed Savepoint as the commit is now being done
804: -- after every merged row instead of committing rows at the end of the
805: -- procedure
806:
807: IF NOT FND_API.Compatible_API_Call ( l_api_version,
808: p_api_version,
809: l_api_name,
810: G_PKG_NAME )
811: THEN
808: p_api_version,
809: l_api_name,
810: G_PKG_NAME )
811: THEN
812: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
813: END IF;
814: --
815:
816: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
812: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
813: END IF;
814: --
815:
816: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
817: FND_MSG_PUB.initialize ;
818: END IF;
819: --
820: x_return_status := FND_API.G_RET_STS_SUCCESS ;
816: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
817: FND_MSG_PUB.initialize ;
818: END IF;
819: --
820: x_return_status := FND_API.G_RET_STS_SUCCESS ;
821: --
822:
823: if (p_focus_value_set_id is not null) then
824:
1005:
1006: -- Bug # 3562336 : Commit after every insert/update to avoid
1007: -- rollback segment capacity issues
1008:
1009: IF FND_API.To_Boolean ( p_commit ) THEN
1010: COMMIT WORK;
1011: END IF;
1012:
1013: End Loop;
1053:
1054: -- Bug # 3562336 : Commit after every insert/update to avoid
1055: -- rollback segment capacity issues
1056:
1057: IF FND_API.To_Boolean ( p_commit ) THEN
1058: COMMIT WORK;
1059: END IF;
1060:
1061: End Loop;
1193:
1194: -- Bug # 3562336 : Commit after every insert/update to avoid
1195: -- rollback segment capacity issues
1196:
1197: IF FND_API.To_Boolean ( p_commit ) THEN
1198: COMMIT WORK;
1199: END iF;
1200:
1201:
1239:
1240: -- Bug # 3562336 : Commit after every insert/update to avoid
1241: -- rollback segment capacity issues
1242:
1243: IF FND_API.To_Boolean ( p_commit ) THEN
1244: COMMIT WORK;
1245: END IF;
1246:
1247:
1257: -- Down below are again the standard end and exception sections of the API.
1258: --
1259:
1260: --
1261: IF FND_API.To_Boolean ( p_commit ) THEN
1262: COMMIT WORK;
1263: END iF;
1264: --
1265: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1272:
1273: -- Bug # 3562336 : Remove Rollback statement as Savepoint is
1274: -- not needed anymore
1275:
1276: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1277: --
1278: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1279: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1280: l_api_name);
1288:
1289: PROCEDURE Unflatten_Focus_Node_Tree
1290: (
1291: p_api_version IN NUMBER ,
1292: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1293: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1294: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
1295: x_return_status OUT NOCOPY VARCHAR2 ,
1296: x_msg_count OUT NOCOPY NUMBER ,
1289: PROCEDURE Unflatten_Focus_Node_Tree
1290: (
1291: p_api_version IN NUMBER ,
1292: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1293: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1294: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
1295: x_return_status OUT NOCOPY VARCHAR2 ,
1296: x_msg_count OUT NOCOPY NUMBER ,
1297: x_msg_data OUT NOCOPY VARCHAR2 ,
1290: (
1291: p_api_version IN NUMBER ,
1292: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1293: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1294: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
1295: x_return_status OUT NOCOPY VARCHAR2 ,
1296: x_msg_count OUT NOCOPY NUMBER ,
1297: x_msg_data OUT NOCOPY VARCHAR2 ,
1298: --
1348: BEGIN
1349: --
1350: SAVEPOINT Unflatten_Focus_Node_Tree_Pvt ;
1351: --
1352: IF NOT FND_API.Compatible_API_Call ( l_api_version,
1353: p_api_version,
1354: l_api_name,
1355: G_PKG_NAME )
1356: THEN
1353: p_api_version,
1354: l_api_name,
1355: G_PKG_NAME )
1356: THEN
1357: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1358: END IF;
1359: --
1360:
1361: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
1357: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1358: END IF;
1359: --
1360:
1361: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
1362: FND_MSG_PUB.initialize ;
1363: END IF;
1364: --
1365: x_return_status := FND_API.G_RET_STS_SUCCESS ;
1361: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
1362: FND_MSG_PUB.initialize ;
1363: END IF;
1364: --
1365: x_return_status := FND_API.G_RET_STS_SUCCESS ;
1366:
1367: if (p_focus_value_set_id is not null) then
1368:
1369:
1732: -- Down below are again the standard end and exception sections of the API.
1733: --
1734:
1735: --
1736: IF FND_API.To_Boolean ( p_commit ) THEN
1737: COMMIT WORK;
1738: END iF;
1739: --
1740: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1741: p_data => x_msg_data );
1742: --
1743: EXCEPTION
1744: --
1745: WHEN FND_API.G_EXC_ERROR THEN
1746: --
1747: ROLLBACK TO Unflatten_Focus_Node_Tree_Pvt ;
1748: x_return_status := FND_API.G_RET_STS_ERROR;
1749: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1744: --
1745: WHEN FND_API.G_EXC_ERROR THEN
1746: --
1747: ROLLBACK TO Unflatten_Focus_Node_Tree_Pvt ;
1748: x_return_status := FND_API.G_RET_STS_ERROR;
1749: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1750: p_data => x_msg_data );
1751: --
1752: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1748: x_return_status := FND_API.G_RET_STS_ERROR;
1749: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1750: p_data => x_msg_data );
1751: --
1752: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1753: --
1754: ROLLBACK TO Unflatten_Focus_Node_Tree_Pvt ;
1755: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1756: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1751: --
1752: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1753: --
1754: ROLLBACK TO Unflatten_Focus_Node_Tree_Pvt ;
1755: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1756: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1757: p_data => x_msg_data );
1758: --
1759: WHEN OTHERS THEN
1758: --
1759: WHEN OTHERS THEN
1760: --
1761: ROLLBACK TO Unflatten_Focus_Node_Tree_Pvt ;
1762: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1763: --
1764: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1765: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1766: l_api_name);
1814: COMMIT WORK;
1815:
1816: EXCEPTION
1817:
1818: WHEN FND_API.G_EXC_ERROR THEN
1819: --
1820: retcode := 2 ;
1821: COMMIT WORK ;
1822: --
1819: --
1820: retcode := 2 ;
1821: COMMIT WORK ;
1822: --
1823: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1824: --
1825: retcode := 2 ;
1826: COMMIT WORK ;
1827: --
1898: p_focus_node => p_focus_node,
1899: p_focus_value_set_id => p_focus_value_set_id
1900: );
1901:
1902: if l_return_status = FND_API.G_RET_STS_ERROR THEN
1903: RAISE FND_API.G_EXC_ERROR ;
1904: end if;
1905:
1906: elsif (p_operation = 'Move') then
1899: p_focus_value_set_id => p_focus_value_set_id
1900: );
1901:
1902: if l_return_status = FND_API.G_RET_STS_ERROR THEN
1903: RAISE FND_API.G_EXC_ERROR ;
1904: end if;
1905:
1906: elsif (p_operation = 'Move') then
1907: Flatten_Focus_Node_Tree
1963: COMMIT WORK;
1964:
1965: EXCEPTION
1966:
1967: WHEN FND_API.G_EXC_ERROR THEN
1968: --
1969: retcode := 2 ;
1970: COMMIT WORK ;
1971: --
1968: --
1969: retcode := 2 ;
1970: COMMIT WORK ;
1971: --
1972: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1973: --
1974: retcode := 2 ;
1975: COMMIT WORK ;
1976: --
1996: -- The API to insert Root Nodes.
1997: --
1998: PROCEDURE Insert_Root_Node (
1999: p_api_version IN NUMBER ,
2000: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2001: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2002: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
2003: p_return_status OUT NOCOPY VARCHAR2 ,
2004: p_msg_count OUT NOCOPY NUMBER ,
1997: --
1998: PROCEDURE Insert_Root_Node (
1999: p_api_version IN NUMBER ,
2000: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2001: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2002: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
2003: p_return_status OUT NOCOPY VARCHAR2 ,
2004: p_msg_count OUT NOCOPY NUMBER ,
2005: p_msg_data OUT NOCOPY VARCHAR2 ,
1998: PROCEDURE Insert_Root_Node (
1999: p_api_version IN NUMBER ,
2000: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2001: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2002: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
2003: p_return_status OUT NOCOPY VARCHAR2 ,
2004: p_msg_count OUT NOCOPY NUMBER ,
2005: p_msg_data OUT NOCOPY VARCHAR2 ,
2006: p_rowid IN OUT NOCOPY VARCHAR2,
2028:
2029: SAVEPOINT Insert_Root_Node_Pvt ;
2030:
2031:
2032: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
2033:
2034: FND_MSG_PUB.initialize ;
2035:
2036: END IF;
2034: FND_MSG_PUB.initialize ;
2035:
2036: END IF;
2037:
2038: p_return_status := FND_API.G_RET_STS_SUCCESS ;
2039: l_creation_date := SYSDATE ;
2040: l_last_update_date := SYSDATE;
2041: l_last_Updated_by := FND_GLOBAL.User_Id;
2042: l_created_by := FND_GLOBAL.User_Id;
2112: RAISE no_data_found;
2113:
2114: END IF;
2115:
2116: IF FND_API.To_Boolean ( p_commit ) THEN
2117:
2118: COMMIT WORK;
2119:
2120: END iF;
2122: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2123: p_data => p_msg_data );
2124:
2125: EXCEPTION
2126: WHEN FND_API.G_EXC_ERROR THEN
2127:
2128: ROLLBACK TO Insert_Root_Node_Pvt ;
2129:
2130: p_return_status := FND_API.G_RET_STS_ERROR;
2126: WHEN FND_API.G_EXC_ERROR THEN
2127:
2128: ROLLBACK TO Insert_Root_Node_Pvt ;
2129:
2130: p_return_status := FND_API.G_RET_STS_ERROR;
2131:
2132: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2133: p_data => p_msg_data );
2134:
2131:
2132: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2133: p_data => p_msg_data );
2134:
2135: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2136:
2137: ROLLBACK TO Insert_Root_Node_Pvt ;
2138: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2139:
2134:
2135: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2136:
2137: ROLLBACK TO Insert_Root_Node_Pvt ;
2138: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2139:
2140: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2141: p_data => p_msg_data );
2142:
2142:
2143: WHEN OTHERS THEN
2144:
2145: ROLLBACK TO Insert_Root_Node_Pvt ;
2146: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2147:
2148: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2149: p_data => p_msg_data );
2150:
2216: -- The API to duplicate a Hierarchy.
2217: --
2218: PROCEDURE Duplicate_Hierarchy (
2219: p_api_version IN NUMBER ,
2220: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2221: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2222: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
2223: p_return_status OUT NOCOPY VARCHAR2 ,
2224: p_msg_count OUT NOCOPY NUMBER ,
2217: --
2218: PROCEDURE Duplicate_Hierarchy (
2219: p_api_version IN NUMBER ,
2220: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2221: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2222: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
2223: p_return_status OUT NOCOPY VARCHAR2 ,
2224: p_msg_count OUT NOCOPY NUMBER ,
2225: p_msg_data OUT NOCOPY VARCHAR2 ,
2218: PROCEDURE Duplicate_Hierarchy (
2219: p_api_version IN NUMBER ,
2220: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2221: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2222: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
2223: p_return_status OUT NOCOPY VARCHAR2 ,
2224: p_msg_count OUT NOCOPY NUMBER ,
2225: p_msg_data OUT NOCOPY VARCHAR2 ,
2226: p_hier_table_name IN VARCHAR2,
2277: begin
2278:
2279: SAVEPOINT Dup_Hier_Pvt ;
2280:
2281: if not FND_API.Compatible_API_Call ( l_api_version,
2282: p_api_version,
2283: l_api_name,
2284: G_PKG_NAME )
2285: then
2282: p_api_version,
2283: l_api_name,
2284: G_PKG_NAME )
2285: then
2286: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2287: end if;
2288:
2289: if FND_API.to_Boolean ( p_init_msg_list ) then
2290: FND_MSG_PUB.initialize ;
2285: then
2286: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2287: end if;
2288:
2289: if FND_API.to_Boolean ( p_init_msg_list ) then
2290: FND_MSG_PUB.initialize ;
2291: end if;
2292:
2293: p_return_status := FND_API.G_RET_STS_SUCCESS ;
2289: if FND_API.to_Boolean ( p_init_msg_list ) then
2290: FND_MSG_PUB.initialize ;
2291: end if;
2292:
2293: p_return_status := FND_API.G_RET_STS_SUCCESS ;
2294:
2295: l_creation_date := SYSDATE ;
2296: l_last_update_date := SYSDATE;
2297: l_last_Updated_by := FND_GLOBAL.User_Id;
2433: p_dest_start_date => p_dest_start_date,
2434: p_dest_end_date => p_dest_end_date);
2435:
2436: if p_return_status <> 'S' then
2437: RAISE FND_API.G_EXC_ERROR;
2438: end if;
2439:
2440: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2441: p_data => p_msg_data );
2440: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2441: p_data => p_msg_data );
2442:
2443: EXCEPTION
2444: WHEN FND_API.G_EXC_ERROR THEN
2445: ROLLBACK TO Dup_Hier_Pvt;
2446: p_return_status := FND_API.G_RET_STS_ERROR;
2447: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2448: p_data => p_msg_data );
2442:
2443: EXCEPTION
2444: WHEN FND_API.G_EXC_ERROR THEN
2445: ROLLBACK TO Dup_Hier_Pvt;
2446: p_return_status := FND_API.G_RET_STS_ERROR;
2447: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2448: p_data => p_msg_data );
2449:
2450: p_msg_data := sqlerrm;
2448: p_data => p_msg_data );
2449:
2450: p_msg_data := sqlerrm;
2451:
2452: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2453: ROLLBACK TO Dup_Hier_Pvt ;
2454: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2455: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2456: p_data => p_msg_data );
2450: p_msg_data := sqlerrm;
2451:
2452: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2453: ROLLBACK TO Dup_Hier_Pvt ;
2454: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2455: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2456: p_data => p_msg_data );
2457:
2458: p_msg_data := sqlerrm;
2457:
2458: p_msg_data := sqlerrm;
2459: WHEN OTHERS THEN
2460: ROLLBACK TO Dup_Hier_Pvt ;
2461: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2462: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2463: p_data => p_msg_data );
2464:
2465: p_msg_data := sqlerrm;
2483: IS
2484:
2485: l_new_relationship_id NUMBER;
2486:
2487: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2488: l_msg_count NUMBER;
2489: l_msg_data VARCHAR2(2000);
2490:
2491: l_src_version_start_date DATE;
2588: p_child_sequence_num => l_dest_version_hier_data_rec.child_sequence_num,
2589: p_hier_table_name => 'FEM_COST_OBJECTS_HIER',
2590: p_remove_all_children_flag => 'N');
2591:
2592: IF l_return_status = FND_API.G_RET_STS_ERROR
2593: THEN
2594: RAISE FND_API.G_EXC_ERROR ;
2595: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2596: THEN
2590: p_remove_all_children_flag => 'N');
2591:
2592: IF l_return_status = FND_API.G_RET_STS_ERROR
2593: THEN
2594: RAISE FND_API.G_EXC_ERROR ;
2595: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2596: THEN
2597: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2598: END IF;
2591:
2592: IF l_return_status = FND_API.G_RET_STS_ERROR
2593: THEN
2594: RAISE FND_API.G_EXC_ERROR ;
2595: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2596: THEN
2597: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2598: END IF;
2599:
2593: THEN
2594: RAISE FND_API.G_EXC_ERROR ;
2595: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2596: THEN
2597: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2598: END IF;
2599:
2600: END LOOP;
2601:
2619: p_bom_reference => l_src_version_hier_data_rec.bom_reference,
2620: p_display_order_num=>l_src_version_hier_data_rec.display_order_num,
2621: p_hier_table_name => 'FEM_COST_OBJECTS_HIER');
2622:
2623: IF l_return_status = FND_API.G_RET_STS_ERROR
2624: THEN
2625: RAISE FND_API.G_EXC_ERROR ;
2626: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2627: THEN
2621: p_hier_table_name => 'FEM_COST_OBJECTS_HIER');
2622:
2623: IF l_return_status = FND_API.G_RET_STS_ERROR
2624: THEN
2625: RAISE FND_API.G_EXC_ERROR ;
2626: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2627: THEN
2628: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2629: END IF;
2622:
2623: IF l_return_status = FND_API.G_RET_STS_ERROR
2624: THEN
2625: RAISE FND_API.G_EXC_ERROR ;
2626: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2627: THEN
2628: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2629: END IF;
2630:
2624: THEN
2625: RAISE FND_API.G_EXC_ERROR ;
2626: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2627: THEN
2628: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2629: END IF;
2630:
2631: END LOOP;
2632:
2629: END IF;
2630:
2631: END LOOP;
2632:
2633: x_return_status := FND_API.G_RET_STS_SUCCESS ;
2634:
2635: EXCEPTION
2636: WHEN FND_API.G_EXC_ERROR THEN
2637:
2632:
2633: x_return_status := FND_API.G_RET_STS_SUCCESS ;
2634:
2635: EXCEPTION
2636: WHEN FND_API.G_EXC_ERROR THEN
2637:
2638: ROLLBACK TO Dup_Cost_Object_Hier_Data_Pvt ;
2639: x_return_status := FND_API.G_RET_STS_ERROR;
2640: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2635: EXCEPTION
2636: WHEN FND_API.G_EXC_ERROR THEN
2637:
2638: ROLLBACK TO Dup_Cost_Object_Hier_Data_Pvt ;
2639: x_return_status := FND_API.G_RET_STS_ERROR;
2640: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2641: p_data => x_msg_data );
2642:
2643: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2639: x_return_status := FND_API.G_RET_STS_ERROR;
2640: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2641: p_data => x_msg_data );
2642:
2643: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2644:
2645: ROLLBACK TO Dup_Cost_Object_Hier_Data_Pvt ;
2646: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2647: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2642:
2643: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2644:
2645: ROLLBACK TO Dup_Cost_Object_Hier_Data_Pvt ;
2646: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2647: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2648: p_data => x_msg_data );
2649:
2650: WHEN OTHERS THEN
2649:
2650: WHEN OTHERS THEN
2651:
2652: ROLLBACK TO Dup_Cost_Object_Hier_Data_Pvt ;
2653: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2654:
2655: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2656: p_data => x_msg_data );
2657:
4267: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4268: p_data => p_msg_data );
4269:
4270: EXCEPTION
4271: WHEN FND_API.G_EXC_ERROR THEN
4272: ROLLBACK TO Copy_Hier_Data_Pvt ;
4273: p_return_status := FND_API.G_RET_STS_ERROR;
4274: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4275: p_data => p_msg_data );
4269:
4270: EXCEPTION
4271: WHEN FND_API.G_EXC_ERROR THEN
4272: ROLLBACK TO Copy_Hier_Data_Pvt ;
4273: p_return_status := FND_API.G_RET_STS_ERROR;
4274: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4275: p_data => p_msg_data );
4276:
4277: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4273: p_return_status := FND_API.G_RET_STS_ERROR;
4274: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4275: p_data => p_msg_data );
4276:
4277: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4278: ROLLBACK TO Copy_Hier_Data_Pvt ;
4279: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4280: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4281: p_data => p_msg_data );
4275: p_data => p_msg_data );
4276:
4277: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4278: ROLLBACK TO Copy_Hier_Data_Pvt ;
4279: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4280: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4281: p_data => p_msg_data );
4282:
4283: WHEN OTHERS THEN
4281: p_data => p_msg_data );
4282:
4283: WHEN OTHERS THEN
4284: ROLLBACK TO Copy_Hier_Data_Pvt ;
4285: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4286: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4287: p_data => p_msg_data );
4288:
4289: END Copy_Hier_Data;
4348: -- The API to duplicate a Hierarchy Version.
4349: --
4350: PROCEDURE Duplicate_Hier_Version(
4351: p_api_version IN NUMBER ,
4352: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
4353: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
4354: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
4355: p_return_status OUT NOCOPY VARCHAR2 ,
4356: p_msg_count OUT NOCOPY NUMBER ,
4349: --
4350: PROCEDURE Duplicate_Hier_Version(
4351: p_api_version IN NUMBER ,
4352: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
4353: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
4354: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
4355: p_return_status OUT NOCOPY VARCHAR2 ,
4356: p_msg_count OUT NOCOPY NUMBER ,
4357: p_msg_data OUT NOCOPY VARCHAR2 ,
4350: PROCEDURE Duplicate_Hier_Version(
4351: p_api_version IN NUMBER ,
4352: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
4353: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
4354: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
4355: p_return_status OUT NOCOPY VARCHAR2 ,
4356: p_msg_count OUT NOCOPY NUMBER ,
4357: p_msg_data OUT NOCOPY VARCHAR2 ,
4358: p_hier_table_name IN VARCHAR2,
4397: begin
4398:
4399: SAVEPOINT Dup_Hier_Version_Pvt;
4400:
4401: if not FND_API.Compatible_API_Call ( l_api_version,
4402: p_api_version,
4403: l_api_name,
4404: G_PKG_NAME )
4405: then
4402: p_api_version,
4403: l_api_name,
4404: G_PKG_NAME )
4405: then
4406: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4407: end if;
4408:
4409: if FND_API.to_Boolean ( p_init_msg_list ) then
4410: FND_MSG_PUB.initialize ;
4405: then
4406: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4407: end if;
4408:
4409: if FND_API.to_Boolean ( p_init_msg_list ) then
4410: FND_MSG_PUB.initialize ;
4411: end if;
4412:
4413: p_return_status := FND_API.G_RET_STS_SUCCESS ;
4409: if FND_API.to_Boolean ( p_init_msg_list ) then
4410: FND_MSG_PUB.initialize ;
4411: end if;
4412:
4413: p_return_status := FND_API.G_RET_STS_SUCCESS ;
4414:
4415: -- Added for Bug:3737462
4416: -- The following call ensures that when duplicating a version there is no overlap in
4417: -- the date ranges of existing versions.
4423: x_return_status => p_return_status,
4424: x_msg_count => p_msg_count,
4425: x_msg_data => p_msg_data);
4426:
4427: if p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4428: RAISE FND_API.G_EXC_ERROR ;
4429: end if;
4430:
4431: open l_object_definition_csr (p_version_id => p_src_hier_version_id);
4424: x_msg_count => p_msg_count,
4425: x_msg_data => p_msg_data);
4426:
4427: if p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4428: RAISE FND_API.G_EXC_ERROR ;
4429: end if;
4430:
4431: open l_object_definition_csr (p_version_id => p_src_hier_version_id);
4432: fetch l_object_definition_csr into l_object_definition_rec;
4509: END IF;
4510:
4511:
4512: if p_return_status <> 'S' then
4513: RAISE FND_API.G_EXC_ERROR;
4514: end if;
4515:
4516:
4517: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4517: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4518: p_data => p_msg_data );
4519:
4520: EXCEPTION
4521: WHEN FND_API.G_EXC_ERROR THEN
4522: ROLLBACK TO Dup_Hier_Version_Pvt ;
4523: p_return_status := FND_API.G_RET_STS_ERROR;
4524: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4525: p_data => p_msg_data );
4519:
4520: EXCEPTION
4521: WHEN FND_API.G_EXC_ERROR THEN
4522: ROLLBACK TO Dup_Hier_Version_Pvt ;
4523: p_return_status := FND_API.G_RET_STS_ERROR;
4524: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4525: p_data => p_msg_data );
4526:
4527: p_msg_data := sqlerrm;
4524: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4525: p_data => p_msg_data );
4526:
4527: p_msg_data := sqlerrm;
4528: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4529: ROLLBACK TO Dup_Hier_Version_Pvt ;
4530: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4531: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4532: p_data => p_msg_data );
4526:
4527: p_msg_data := sqlerrm;
4528: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4529: ROLLBACK TO Dup_Hier_Version_Pvt ;
4530: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4531: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4532: p_data => p_msg_data );
4533:
4534: p_msg_data := sqlerrm;
4533:
4534: p_msg_data := sqlerrm;
4535: WHEN OTHERS THEN
4536: ROLLBACK TO Dup_Hier_Version_Pvt ;
4537: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4538: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4539: p_data => p_msg_data );
4540:
4541: p_msg_data := sqlerrm;
4885: -- The API to delete a Hierarchy.
4886: --
4887: PROCEDURE Delete_Hierarchy(
4888: p_api_version IN NUMBER ,
4889: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
4890: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
4891: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
4892: p_return_status OUT NOCOPY VARCHAR2 ,
4893: p_msg_count OUT NOCOPY NUMBER ,
4886: --
4887: PROCEDURE Delete_Hierarchy(
4888: p_api_version IN NUMBER ,
4889: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
4890: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
4891: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
4892: p_return_status OUT NOCOPY VARCHAR2 ,
4893: p_msg_count OUT NOCOPY NUMBER ,
4894: p_msg_data OUT NOCOPY VARCHAR2 ,
4887: PROCEDURE Delete_Hierarchy(
4888: p_api_version IN NUMBER ,
4889: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
4890: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
4891: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
4892: p_return_status OUT NOCOPY VARCHAR2 ,
4893: p_msg_count OUT NOCOPY NUMBER ,
4894: p_msg_data OUT NOCOPY VARCHAR2 ,
4895: p_hier_table_name IN VARCHAR2,
4911: begin
4912:
4913: SAVEPOINT Delete_Hierarachy_Pvt;
4914:
4915: if not FND_API.Compatible_API_Call ( l_api_version,
4916: p_api_version,
4917: l_api_name,
4918: G_PKG_NAME )
4919: then
4916: p_api_version,
4917: l_api_name,
4918: G_PKG_NAME )
4919: then
4920: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4921: end if;
4922:
4923: if FND_API.to_Boolean ( p_init_msg_list ) then
4924: FND_MSG_PUB.initialize ;
4919: then
4920: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4921: end if;
4922:
4923: if FND_API.to_Boolean ( p_init_msg_list ) then
4924: FND_MSG_PUB.initialize ;
4925: end if;
4926:
4927: p_return_status := FND_API.G_RET_STS_SUCCESS ;
4923: if FND_API.to_Boolean ( p_init_msg_list ) then
4924: FND_MSG_PUB.initialize ;
4925: end if;
4926:
4927: p_return_status := FND_API.G_RET_STS_SUCCESS ;
4928:
4929:
4930: delete_hier_version( p_api_version => p_api_version,
4931: p_return_status => p_return_status,
4935: p_hier_obj_id => p_hier_obj_id,
4936: p_hier_version_id => NULL);
4937:
4938: if p_return_status <> 'S' then
4939: RAISE FND_API.G_EXC_ERROR;
4940: end if;
4941:
4942: DELETE FROM fem_hier_dimension_grps
4943: WHERE hierarchy_obj_id = p_hier_obj_id;
4947:
4948: DELETE FROM fem_hierarchies
4949: WHERE hierarchy_obj_id = p_hier_obj_id;
4950:
4951: IF FND_API.To_Boolean ( p_commit ) THEN
4952:
4953: COMMIT WORK;
4954:
4955: END iF;
4957: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4958: p_data => p_msg_data );
4959:
4960: EXCEPTION
4961: WHEN FND_API.G_EXC_ERROR THEN
4962:
4963: ROLLBACK TO Delete_Hierarachy_Pvt ;
4964:
4965: p_return_status := FND_API.G_RET_STS_ERROR;
4961: WHEN FND_API.G_EXC_ERROR THEN
4962:
4963: ROLLBACK TO Delete_Hierarachy_Pvt ;
4964:
4965: p_return_status := FND_API.G_RET_STS_ERROR;
4966:
4967: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4968: p_data => p_msg_data );
4969:
4966:
4967: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4968: p_data => p_msg_data );
4969:
4970: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4971:
4972: ROLLBACK TO Delete_Hierarachy_Pvt ;
4973: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4974:
4969:
4970: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4971:
4972: ROLLBACK TO Delete_Hierarachy_Pvt ;
4973: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4974:
4975: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4976: p_data => p_msg_data );
4977:
4977:
4978: WHEN OTHERS THEN
4979:
4980: ROLLBACK TO Delete_Hierarachy_Pvt ;
4981: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4982:
4983: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4984: p_data => p_msg_data );
4985:
5070: -- The API to Delete Hierarchy Version.
5071: --
5072: PROCEDURE Delete_Hier_Version(
5073: p_api_version IN NUMBER ,
5074: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
5075: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
5076: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
5077: p_return_status OUT NOCOPY VARCHAR2 ,
5078: p_msg_count OUT NOCOPY NUMBER ,
5071: --
5072: PROCEDURE Delete_Hier_Version(
5073: p_api_version IN NUMBER ,
5074: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
5075: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
5076: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
5077: p_return_status OUT NOCOPY VARCHAR2 ,
5078: p_msg_count OUT NOCOPY NUMBER ,
5079: p_msg_data OUT NOCOPY VARCHAR2 ,
5072: PROCEDURE Delete_Hier_Version(
5073: p_api_version IN NUMBER ,
5074: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
5075: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
5076: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
5077: p_return_status OUT NOCOPY VARCHAR2 ,
5078: p_msg_count OUT NOCOPY NUMBER ,
5079: p_msg_data OUT NOCOPY VARCHAR2 ,
5080: p_hier_table_name IN VARCHAR2,
5123: begin
5124:
5125: SAVEPOINT Delete_Hier_Version_Pvt;
5126:
5127: if not FND_API.Compatible_API_Call ( l_api_version,
5128: p_api_version,
5129: l_api_name,
5130: G_PKG_NAME )
5131: then
5128: p_api_version,
5129: l_api_name,
5130: G_PKG_NAME )
5131: then
5132: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
5133: end if;
5134:
5135: if FND_API.to_Boolean ( p_init_msg_list ) then
5136: FND_MSG_PUB.initialize ;
5131: then
5132: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
5133: end if;
5134:
5135: if FND_API.to_Boolean ( p_init_msg_list ) then
5136: FND_MSG_PUB.initialize ;
5137: end if;
5138:
5139: p_return_status := FND_API.G_RET_STS_SUCCESS ;
5135: if FND_API.to_Boolean ( p_init_msg_list ) then
5136: FND_MSG_PUB.initialize ;
5137: end if;
5138:
5139: p_return_status := FND_API.G_RET_STS_SUCCESS ;
5140:
5141: -- If p_hier_version_id = NULL then we want to delete all the versions
5142: -- for the given p_hier_obj_id.
5143:
5173: x_return_status => p_return_status,
5174: x_msg_count => p_msg_count,
5175: x_msg_data => p_msg_data);
5176:
5177: if p_return_status = FND_API.G_RET_STS_ERROR then
5178:
5179: raise FND_API.G_EXC_ERROR;
5180:
5181: elsif p_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
5175: x_msg_data => p_msg_data);
5176:
5177: if p_return_status = FND_API.G_RET_STS_ERROR then
5178:
5179: raise FND_API.G_EXC_ERROR;
5180:
5181: elsif p_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
5182:
5183: raise FND_API.G_EXC_UNEXPECTED_ERROR;
5177: if p_return_status = FND_API.G_RET_STS_ERROR then
5178:
5179: raise FND_API.G_EXC_ERROR;
5180:
5181: elsif p_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
5182:
5183: raise FND_API.G_EXC_UNEXPECTED_ERROR;
5184:
5185: end if;
5179: raise FND_API.G_EXC_ERROR;
5180:
5181: elsif p_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
5182:
5183: raise FND_API.G_EXC_UNEXPECTED_ERROR;
5184:
5185: end if;
5186:
5187: END LOOP;
5229: x_return_status => p_return_status,
5230: x_msg_count => p_msg_count,
5231: x_msg_data => p_msg_data);
5232:
5233: if p_return_status = FND_API.G_RET_STS_ERROR then
5234:
5235: raise FND_API.G_EXC_ERROR;
5236:
5237:
5231: x_msg_data => p_msg_data);
5232:
5233: if p_return_status = FND_API.G_RET_STS_ERROR then
5234:
5235: raise FND_API.G_EXC_ERROR;
5236:
5237:
5238: elsif p_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
5239:
5234:
5235: raise FND_API.G_EXC_ERROR;
5236:
5237:
5238: elsif p_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
5239:
5240: raise FND_API.G_EXC_UNEXPECTED_ERROR;
5241:
5242: end if;
5236:
5237:
5238: elsif p_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
5239:
5240: raise FND_API.G_EXC_UNEXPECTED_ERROR;
5241:
5242: end if;
5243:
5244: END IF;
5242: end if;
5243:
5244: END IF;
5245:
5246: IF FND_API.To_Boolean ( p_commit ) THEN
5247:
5248: COMMIT WORK;
5249:
5250: END iF;
5252: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
5253: p_data => p_msg_data );
5254:
5255: EXCEPTION
5256: WHEN FND_API.G_EXC_ERROR THEN
5257:
5258: ROLLBACK TO Delete_Hier_Version_Pvt ;
5259:
5260: p_return_status := FND_API.G_RET_STS_ERROR;
5256: WHEN FND_API.G_EXC_ERROR THEN
5257:
5258: ROLLBACK TO Delete_Hier_Version_Pvt ;
5259:
5260: p_return_status := FND_API.G_RET_STS_ERROR;
5261:
5262: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
5263: p_data => p_msg_data );
5264:
5261:
5262: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
5263: p_data => p_msg_data );
5264:
5265: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5266:
5267: ROLLBACK TO Delete_Hier_Version_Pvt ;
5268: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5269:
5264:
5265: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5266:
5267: ROLLBACK TO Delete_Hier_Version_Pvt ;
5268: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5269:
5270: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
5271: p_data => p_msg_data );
5272:
5272:
5273: WHEN OTHERS THEN
5274:
5275: ROLLBACK TO Delete_Hier_Version_Pvt ;
5276: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5277:
5278: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
5279: p_data => p_msg_data );
5280:
5290: --
5291: PROCEDURE Remove_Nodes
5292: (
5293: p_api_version IN NUMBER,
5294: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5295: p_commit IN VARCHAR2 := FND_API.G_FALSE,
5296: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5297: x_return_status OUT NOCOPY VARCHAR2,
5298: x_msg_count OUT NOCOPY NUMBER,
5291: PROCEDURE Remove_Nodes
5292: (
5293: p_api_version IN NUMBER,
5294: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5295: p_commit IN VARCHAR2 := FND_API.G_FALSE,
5296: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5297: x_return_status OUT NOCOPY VARCHAR2,
5298: x_msg_count OUT NOCOPY NUMBER,
5299: x_msg_data OUT NOCOPY VARCHAR2,
5292: (
5293: p_api_version IN NUMBER,
5294: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5295: p_commit IN VARCHAR2 := FND_API.G_FALSE,
5296: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5297: x_return_status OUT NOCOPY VARCHAR2,
5298: x_msg_count OUT NOCOPY NUMBER,
5299: x_msg_data OUT NOCOPY VARCHAR2,
5300: --
5362: l_rowid VARCHAR2(20);
5363: l_select_stmt VARCHAR2(1000);
5364: l_delete_stmt VARCHAR2(1000);
5365: l_update_stmt VARCHAR2(1000);
5366: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5367: l_msg_count NUMBER;
5368: l_msg_data VARCHAR2(2000);
5369:
5370: BEGIN
5370: BEGIN
5371: --
5372: SAVEPOINT Hier_Operation_Pvt ;
5373: --
5374: IF NOT FND_API.Compatible_API_Call ( l_api_version,
5375: p_api_version,
5376: l_api_name,
5377: G_PKG_NAME )
5378: THEN
5375: p_api_version,
5376: l_api_name,
5377: G_PKG_NAME )
5378: THEN
5379: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
5380: END IF;
5381: --
5382:
5383: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
5379: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
5380: END IF;
5381: --
5382:
5383: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
5384: FND_MSG_PUB.initialize ;
5385: END IF;
5386: --
5387: x_return_status := FND_API.G_RET_STS_SUCCESS ;
5383: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
5384: FND_MSG_PUB.initialize ;
5385: END IF;
5386: --
5387: x_return_status := FND_API.G_RET_STS_SUCCESS ;
5388: --
5389:
5390: IF (p_value_set_required_flag IS NULL OR p_value_set_required_flag = 'N')
5391: THEN
5808: -- End bug fix# 3916681.
5809:
5810: END IF;
5811:
5812: IF FND_API.To_Boolean(p_commit)
5813: THEN
5814: COMMIT WORK;
5815: END IF;
5816:
5867:
5868: EXCEPTION
5869:
5870: --
5871: WHEN FND_API.G_EXC_ERROR THEN
5872: --
5873: ROLLBACK TO Hier_Operation_Pvt ;
5874: x_return_status := FND_API.G_RET_STS_ERROR;
5875: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
5870: --
5871: WHEN FND_API.G_EXC_ERROR THEN
5872: --
5873: ROLLBACK TO Hier_Operation_Pvt ;
5874: x_return_status := FND_API.G_RET_STS_ERROR;
5875: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
5876: p_data => x_msg_data );
5877: --
5878: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5874: x_return_status := FND_API.G_RET_STS_ERROR;
5875: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
5876: p_data => x_msg_data );
5877: --
5878: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5879: --
5880: ROLLBACK TO Hier_Operation_Pvt ;
5881: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5882: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
5877: --
5878: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5879: --
5880: ROLLBACK TO Hier_Operation_Pvt ;
5881: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5882: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
5883: p_data => x_msg_data );
5884: --
5885: WHEN NO_DATA_FOUND THEN
5884: --
5885: WHEN NO_DATA_FOUND THEN
5886: --Bug#4878100
5887: ROLLBACK TO Hier_Operation_Pvt ;
5888: x_return_status := FND_API.G_RET_STS_ERROR;
5889: FND_MESSAGE.SET_NAME('FEM', 'FEM_DHM_MEM_DELETED_ERR');
5890: FND_MSG_PUB.ADD;
5891:
5892: --
5892: --
5893: WHEN OTHERS THEN
5894: --
5895: ROLLBACK TO Hier_Operation_Pvt ;
5896: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5897: --
5898: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5899: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
5900: l_api_name);
5915: --
5916: PROCEDURE Move_Nodes
5917: (
5918: p_api_version IN NUMBER,
5919: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5920: p_commit IN VARCHAR2 := FND_API.G_FALSE,
5921: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5922: x_return_status OUT NOCOPY VARCHAR2,
5923: x_msg_count OUT NOCOPY NUMBER,
5916: PROCEDURE Move_Nodes
5917: (
5918: p_api_version IN NUMBER,
5919: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5920: p_commit IN VARCHAR2 := FND_API.G_FALSE,
5921: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5922: x_return_status OUT NOCOPY VARCHAR2,
5923: x_msg_count OUT NOCOPY NUMBER,
5924: x_msg_data OUT NOCOPY VARCHAR2,
5917: (
5918: p_api_version IN NUMBER,
5919: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5920: p_commit IN VARCHAR2 := FND_API.G_FALSE,
5921: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5922: x_return_status OUT NOCOPY VARCHAR2,
5923: x_msg_count OUT NOCOPY NUMBER,
5924: x_msg_data OUT NOCOPY VARCHAR2,
5925: --
5980: l_req_id NUMBER;
5981: l_rowid VARCHAR2(20);
5982: l_select_stmt VARCHAR2(1000);
5983: l_insert_stmt VARCHAR2(1000);
5984: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5985: l_msg_count NUMBER;
5986: l_msg_data VARCHAR2(2000);
5987: BEGIN
5988: --
5987: BEGIN
5988: --
5989: SAVEPOINT Hier_Operation_Pvt ;
5990: --
5991: IF NOT FND_API.Compatible_API_Call ( l_api_version,
5992: p_api_version,
5993: l_api_name,
5994: G_PKG_NAME )
5995: THEN
5992: p_api_version,
5993: l_api_name,
5994: G_PKG_NAME )
5995: THEN
5996: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
5997: END IF;
5998: --
5999:
6000: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
5996: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
5997: END IF;
5998: --
5999:
6000: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
6001: FND_MSG_PUB.initialize ;
6002: END IF;
6003: --
6004: x_return_status := FND_API.G_RET_STS_SUCCESS ;
6000: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
6001: FND_MSG_PUB.initialize ;
6002: END IF;
6003: --
6004: x_return_status := FND_API.G_RET_STS_SUCCESS ;
6005: --
6006:
6007: -- Recorde the whole hierarchy from the source node down. Remove the
6008: -- hierarchy that was recorderd previously. Then add then back as
6057: -- Remove the whole hierarchy from the source node down (including source)
6058:
6059: Remove_Nodes(
6060: p_api_version => 1.0,
6061: p_init_msg_list => FND_API.G_FALSE,
6062: p_commit => FND_API.G_FALSE,
6063: p_validation_level => p_validation_level,
6064: x_return_status => l_return_status,
6065: x_msg_count => l_msg_count,
6058:
6059: Remove_Nodes(
6060: p_api_version => 1.0,
6061: p_init_msg_list => FND_API.G_FALSE,
6062: p_commit => FND_API.G_FALSE,
6063: p_validation_level => p_validation_level,
6064: x_return_status => l_return_status,
6065: x_msg_count => l_msg_count,
6066: x_msg_data => l_msg_data,
6071: p_value_set_required_flag => p_value_set_required_flag,
6072: p_flatten_rows_flag => p_flatten_rows_flag,
6073: p_user_id => p_user_id);
6074:
6075: IF l_return_status = FND_API.G_RET_STS_ERROR
6076: THEN
6077: RAISE FND_API.G_EXC_ERROR ;
6078: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6079: THEN
6073: p_user_id => p_user_id);
6074:
6075: IF l_return_status = FND_API.G_RET_STS_ERROR
6076: THEN
6077: RAISE FND_API.G_EXC_ERROR ;
6078: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6079: THEN
6080: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6081: END IF;
6074:
6075: IF l_return_status = FND_API.G_RET_STS_ERROR
6076: THEN
6077: RAISE FND_API.G_EXC_ERROR ;
6078: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6079: THEN
6080: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6081: END IF;
6082:
6076: THEN
6077: RAISE FND_API.G_EXC_ERROR ;
6078: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6079: THEN
6080: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6081: END IF;
6082:
6083: -- Get the max display number of the children of the destination member.
6084: -- If the destination member is a leaf level node, no record will be
6264: -- Remove the whole hierarchy from the source node down (including source)
6265:
6266: Remove_Nodes(
6267: p_api_version => 1.0,
6268: p_init_msg_list => FND_API.G_FALSE,
6269: p_commit => FND_API.G_FALSE,
6270: p_validation_level => p_validation_level,
6271: x_return_status => l_return_status,
6272: x_msg_count => l_msg_count,
6265:
6266: Remove_Nodes(
6267: p_api_version => 1.0,
6268: p_init_msg_list => FND_API.G_FALSE,
6269: p_commit => FND_API.G_FALSE,
6270: p_validation_level => p_validation_level,
6271: x_return_status => l_return_status,
6272: x_msg_count => l_msg_count,
6273: x_msg_data => l_msg_data,
6278: p_value_set_required_flag => p_value_set_required_flag,
6279: p_flatten_rows_flag => p_flatten_rows_flag,
6280: p_user_id => p_user_id);
6281:
6282: IF l_return_status = FND_API.G_RET_STS_ERROR
6283: THEN
6284: RAISE FND_API.G_EXC_ERROR ;
6285: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6286: THEN
6280: p_user_id => p_user_id);
6281:
6282: IF l_return_status = FND_API.G_RET_STS_ERROR
6283: THEN
6284: RAISE FND_API.G_EXC_ERROR ;
6285: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6286: THEN
6287: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6288: END IF;
6281:
6282: IF l_return_status = FND_API.G_RET_STS_ERROR
6283: THEN
6284: RAISE FND_API.G_EXC_ERROR ;
6285: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6286: THEN
6287: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6288: END IF;
6289:
6283: THEN
6284: RAISE FND_API.G_EXC_ERROR ;
6285: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6286: THEN
6287: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6288: END IF;
6289:
6290: -- Get the max display number of the children of the destination member.
6291: -- If the destination member is a leaf level node, no record will be
6431: END IF;
6432:
6433: END IF;
6434:
6435: IF FND_API.To_Boolean(p_commit)
6436: THEN
6437: COMMIT WORK;
6438: END IF;
6439:
6465:
6466: EXCEPTION
6467:
6468: --
6469: WHEN FND_API.G_EXC_ERROR THEN
6470: --
6471: ROLLBACK TO Hier_Operation_Pvt ;
6472: x_return_status := FND_API.G_RET_STS_ERROR;
6473: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6468: --
6469: WHEN FND_API.G_EXC_ERROR THEN
6470: --
6471: ROLLBACK TO Hier_Operation_Pvt ;
6472: x_return_status := FND_API.G_RET_STS_ERROR;
6473: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6474: p_data => x_msg_data );
6475: --
6476: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6472: x_return_status := FND_API.G_RET_STS_ERROR;
6473: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6474: p_data => x_msg_data );
6475: --
6476: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6477: --
6478: ROLLBACK TO Hier_Operation_Pvt ;
6479: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6480: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6475: --
6476: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6477: --
6478: ROLLBACK TO Hier_Operation_Pvt ;
6479: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6480: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6481: p_data => x_msg_data );
6482: --
6483: WHEN OTHERS THEN
6482: --
6483: WHEN OTHERS THEN
6484: --
6485: ROLLBACK TO Hier_Operation_Pvt ;
6486: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6487: --
6488: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
6489: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
6490: l_api_name);
6505: --
6506: PROCEDURE Add_Nodes
6507: (
6508: p_api_version IN NUMBER,
6509: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
6510: p_commit IN VARCHAR2 := FND_API.G_FALSE,
6511: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
6512: x_return_status OUT NOCOPY VARCHAR2,
6513: x_msg_count OUT NOCOPY NUMBER,
6506: PROCEDURE Add_Nodes
6507: (
6508: p_api_version IN NUMBER,
6509: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
6510: p_commit IN VARCHAR2 := FND_API.G_FALSE,
6511: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
6512: x_return_status OUT NOCOPY VARCHAR2,
6513: x_msg_count OUT NOCOPY NUMBER,
6514: x_msg_data OUT NOCOPY VARCHAR2,
6507: (
6508: p_api_version IN NUMBER,
6509: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
6510: p_commit IN VARCHAR2 := FND_API.G_FALSE,
6511: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
6512: x_return_status OUT NOCOPY VARCHAR2,
6513: x_msg_count OUT NOCOPY NUMBER,
6514: x_msg_data OUT NOCOPY VARCHAR2,
6515: --
6537: l_display_order_num NUMBER;
6538: l_req_id NUMBER;
6539: l_rowid VARCHAR2(20);
6540: l_select_depth_stmt VARCHAR2(1000);
6541: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
6542: l_msg_count NUMBER;
6543: l_msg_data VARCHAR2(2000);
6544: BEGIN
6545: --
6544: BEGIN
6545: --
6546: SAVEPOINT Hier_Operation_Pvt ;
6547: --
6548: IF NOT FND_API.Compatible_API_Call ( l_api_version,
6549: p_api_version,
6550: l_api_name,
6551: G_PKG_NAME )
6552: THEN
6549: p_api_version,
6550: l_api_name,
6551: G_PKG_NAME )
6552: THEN
6553: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6554: END IF;
6555: --
6556:
6557: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
6553: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6554: END IF;
6555: --
6556:
6557: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
6558: FND_MSG_PUB.initialize ;
6559: END IF;
6560: --
6561: x_return_status := FND_API.G_RET_STS_SUCCESS ;
6557: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
6558: FND_MSG_PUB.initialize ;
6559: END IF;
6560: --
6561: x_return_status := FND_API.G_RET_STS_SUCCESS ;
6562: --
6563:
6564: -- Get the max display order number of the children of the destination
6565: -- member. Then add a node as a child of the destination member using the
6694:
6695: FOR i IN 1..p_child_members.COUNT
6696: LOOP
6697: insert_root_node(p_api_version => 1.0,
6698: p_init_msg_list => FND_API.G_FALSE,
6699: p_commit => FND_API.G_FALSE,
6700: p_validation_level => p_validation_level,
6701: p_return_status => l_return_status,
6702: p_msg_count => l_msg_count,
6695: FOR i IN 1..p_child_members.COUNT
6696: LOOP
6697: insert_root_node(p_api_version => 1.0,
6698: p_init_msg_list => FND_API.G_FALSE,
6699: p_commit => FND_API.G_FALSE,
6700: p_validation_level => p_validation_level,
6701: p_return_status => l_return_status,
6702: p_msg_count => l_msg_count,
6703: p_msg_data => l_msg_data,
6716: p_weighting_pct => NULL);
6717:
6718: l_display_order_num := l_display_order_num + 1;
6719:
6720: IF l_return_status = FND_API.G_RET_STS_ERROR
6721: THEN
6722: RAISE FND_API.G_EXC_ERROR ;
6723: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6724: THEN
6718: l_display_order_num := l_display_order_num + 1;
6719:
6720: IF l_return_status = FND_API.G_RET_STS_ERROR
6721: THEN
6722: RAISE FND_API.G_EXC_ERROR ;
6723: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6724: THEN
6725: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6726: END IF;
6719:
6720: IF l_return_status = FND_API.G_RET_STS_ERROR
6721: THEN
6722: RAISE FND_API.G_EXC_ERROR ;
6723: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6724: THEN
6725: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6726: END IF;
6727: END LOOP;
6721: THEN
6722: RAISE FND_API.G_EXC_ERROR ;
6723: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6724: THEN
6725: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6726: END IF;
6727: END LOOP;
6728:
6729: IF FND_API.To_Boolean(p_commit)
6725: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6726: END IF;
6727: END LOOP;
6728:
6729: IF FND_API.To_Boolean(p_commit)
6730: THEN
6731: COMMIT WORK;
6732: END IF;
6733:
6752:
6753: EXCEPTION
6754:
6755: --
6756: WHEN FND_API.G_EXC_ERROR THEN
6757: --
6758: ROLLBACK TO Hier_Operation_Pvt ;
6759: x_return_status := FND_API.G_RET_STS_ERROR;
6760: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6755: --
6756: WHEN FND_API.G_EXC_ERROR THEN
6757: --
6758: ROLLBACK TO Hier_Operation_Pvt ;
6759: x_return_status := FND_API.G_RET_STS_ERROR;
6760: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6761: p_data => x_msg_data );
6762: --
6763: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6759: x_return_status := FND_API.G_RET_STS_ERROR;
6760: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6761: p_data => x_msg_data );
6762: --
6763: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6764: --
6765: ROLLBACK TO Hier_Operation_Pvt ;
6766: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6767: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6762: --
6763: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6764: --
6765: ROLLBACK TO Hier_Operation_Pvt ;
6766: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6767: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6768: p_data => x_msg_data );
6769: --
6770: WHEN OTHERS THEN
6769: --
6770: WHEN OTHERS THEN
6771: --
6772: ROLLBACK TO Hier_Operation_Pvt ;
6773: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6774: --
6775: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
6776: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
6777: l_api_name);
6792: --
6793: PROCEDURE Add_Root_Nodes
6794: (
6795: p_api_version IN NUMBER,
6796: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
6797: p_commit IN VARCHAR2 := FND_API.G_FALSE,
6798: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
6799: x_return_status OUT NOCOPY VARCHAR2,
6800: x_msg_count OUT NOCOPY NUMBER,
6793: PROCEDURE Add_Root_Nodes
6794: (
6795: p_api_version IN NUMBER,
6796: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
6797: p_commit IN VARCHAR2 := FND_API.G_FALSE,
6798: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
6799: x_return_status OUT NOCOPY VARCHAR2,
6800: x_msg_count OUT NOCOPY NUMBER,
6801: x_msg_data OUT NOCOPY VARCHAR2,
6794: (
6795: p_api_version IN NUMBER,
6796: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
6797: p_commit IN VARCHAR2 := FND_API.G_FALSE,
6798: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
6799: x_return_status OUT NOCOPY VARCHAR2,
6800: x_msg_count OUT NOCOPY NUMBER,
6801: x_msg_data OUT NOCOPY VARCHAR2,
6802: --
6817:
6818: l_display_order_num NUMBER := 0;
6819: l_rowid VARCHAR2(20);
6820: l_select_stmt VARCHAR2(1000);
6821: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
6822: l_msg_count NUMBER;
6823: l_msg_data VARCHAR2(2000);
6824: BEGIN
6825: --
6824: BEGIN
6825: --
6826: SAVEPOINT Hier_Operation_Pvt ;
6827: --
6828: IF NOT FND_API.Compatible_API_Call ( l_api_version,
6829: p_api_version,
6830: l_api_name,
6831: G_PKG_NAME )
6832: THEN
6829: p_api_version,
6830: l_api_name,
6831: G_PKG_NAME )
6832: THEN
6833: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6834: END IF;
6835: --
6836:
6837: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
6833: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6834: END IF;
6835: --
6836:
6837: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
6838: FND_MSG_PUB.initialize ;
6839: END IF;
6840: --
6841: x_return_status := FND_API.G_RET_STS_SUCCESS ;
6837: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
6838: FND_MSG_PUB.initialize ;
6839: END IF;
6840: --
6841: x_return_status := FND_API.G_RET_STS_SUCCESS ;
6842: --
6843:
6844: -- Find out the max display order number.
6845:
6887: FOR i IN 1..p_child_members.COUNT
6888: LOOP
6889: insert_root_node(
6890: p_api_version => 1.0,
6891: p_init_msg_list => FND_API.G_FALSE,
6892: p_commit => FND_API.G_FALSE,
6893: p_validation_level => p_validation_level,
6894: p_return_status => l_return_status,
6895: p_msg_count => l_msg_count,
6888: LOOP
6889: insert_root_node(
6890: p_api_version => 1.0,
6891: p_init_msg_list => FND_API.G_FALSE,
6892: p_commit => FND_API.G_FALSE,
6893: p_validation_level => p_validation_level,
6894: p_return_status => l_return_status,
6895: p_msg_count => l_msg_count,
6896: p_msg_data => l_msg_data,
6909: p_weighting_pct => NULL);
6910:
6911: l_display_order_num := l_display_order_num + 1;
6912:
6913: IF l_return_status = FND_API.G_RET_STS_ERROR
6914: THEN
6915: RAISE FND_API.G_EXC_ERROR ;
6916: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6917: THEN
6911: l_display_order_num := l_display_order_num + 1;
6912:
6913: IF l_return_status = FND_API.G_RET_STS_ERROR
6914: THEN
6915: RAISE FND_API.G_EXC_ERROR ;
6916: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6917: THEN
6918: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6919: END IF;
6912:
6913: IF l_return_status = FND_API.G_RET_STS_ERROR
6914: THEN
6915: RAISE FND_API.G_EXC_ERROR ;
6916: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6917: THEN
6918: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6919: END IF;
6920: END LOOP;
6914: THEN
6915: RAISE FND_API.G_EXC_ERROR ;
6916: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6917: THEN
6918: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6919: END IF;
6920: END LOOP;
6921:
6922: IF FND_API.To_Boolean(p_commit)
6918: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6919: END IF;
6920: END LOOP;
6921:
6922: IF FND_API.To_Boolean(p_commit)
6923: THEN
6924: COMMIT WORK;
6925: END IF;
6926:
6926:
6927: EXCEPTION
6928:
6929: --
6930: WHEN FND_API.G_EXC_ERROR THEN
6931: --
6932: ROLLBACK TO Hier_Operation_Pvt ;
6933: x_return_status := FND_API.G_RET_STS_ERROR;
6934: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6929: --
6930: WHEN FND_API.G_EXC_ERROR THEN
6931: --
6932: ROLLBACK TO Hier_Operation_Pvt ;
6933: x_return_status := FND_API.G_RET_STS_ERROR;
6934: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6935: p_data => x_msg_data );
6936: --
6937: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6933: x_return_status := FND_API.G_RET_STS_ERROR;
6934: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6935: p_data => x_msg_data );
6936: --
6937: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6938: --
6939: ROLLBACK TO Hier_Operation_Pvt ;
6940: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6941: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6936: --
6937: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6938: --
6939: ROLLBACK TO Hier_Operation_Pvt ;
6940: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6941: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6942: p_data => x_msg_data );
6943: --
6944: WHEN OTHERS THEN
6943: --
6944: WHEN OTHERS THEN
6945: --
6946: ROLLBACK TO Hier_Operation_Pvt ;
6947: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6948: --
6949: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
6950: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
6951: l_api_name);
6961: -- Update Hierarchy Display Sequence numbbers
6962: --
6963: procedure Hier_Member_Sequence_Update (
6964: p_api_version IN NUMBER ,
6965: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
6966: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
6967: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
6968: p_return_status OUT NOCOPY VARCHAR2 ,
6969: p_msg_count OUT NOCOPY NUMBER ,
6962: --
6963: procedure Hier_Member_Sequence_Update (
6964: p_api_version IN NUMBER ,
6965: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
6966: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
6967: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
6968: p_return_status OUT NOCOPY VARCHAR2 ,
6969: p_msg_count OUT NOCOPY NUMBER ,
6970: p_msg_data OUT NOCOPY VARCHAR2 ,
6963: procedure Hier_Member_Sequence_Update (
6964: p_api_version IN NUMBER ,
6965: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
6966: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
6967: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
6968: p_return_status OUT NOCOPY VARCHAR2 ,
6969: p_msg_count OUT NOCOPY NUMBER ,
6970: p_msg_data OUT NOCOPY VARCHAR2 ,
6971: --
6981: begin
6982: --
6983: SAVEPOINT Update_Row_Pvt ;
6984:
6985: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
6986: FND_MSG_PUB.initialize ;
6987: END IF;
6988: --
6989: p_return_status := FND_API.G_RET_STS_SUCCESS ;
6985: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
6986: FND_MSG_PUB.initialize ;
6987: END IF;
6988: --
6989: p_return_status := FND_API.G_RET_STS_SUCCESS ;
6990:
6991: if (p_parent_value_set_id <= 0 AND p_child_value_set_id <= 0) then
6992: execute immediate 'update ' || p_hierarchy_table_name ||
6993: ' SET DISPLAY_ORDER_NUM = ' || p_new_display_order_num ||
7007: IF (sql%notfound) then
7008: RAISE no_data_found;
7009: END IF;
7010:
7011: IF FND_API.To_Boolean ( p_commit ) THEN
7012: COMMIT WORK;
7013: END iF;
7014: --
7015: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
7016: p_data => p_msg_data );
7017: --
7018: EXCEPTION
7019: --
7020: WHEN FND_API.G_EXC_ERROR THEN
7021: --
7022: ROLLBACK TO Update_Row_Pvt ;
7023: p_return_status := FND_API.G_RET_STS_ERROR;
7024: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
7019: --
7020: WHEN FND_API.G_EXC_ERROR THEN
7021: --
7022: ROLLBACK TO Update_Row_Pvt ;
7023: p_return_status := FND_API.G_RET_STS_ERROR;
7024: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
7025: p_data => p_msg_data );
7026:
7027: --
7024: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
7025: p_data => p_msg_data );
7026:
7027: --
7028: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7029: --
7030: ROLLBACK TO Update_Row_Pvt ;
7031: -- DBMS_OUTPUT.PUT_LINE('error: '|| SQLCODE);
7032: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7028: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7029: --
7030: ROLLBACK TO Update_Row_Pvt ;
7031: -- DBMS_OUTPUT.PUT_LINE('error: '|| SQLCODE);
7032: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7033: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
7034: p_data => p_msg_data );
7035: --
7036:
7037: WHEN OTHERS THEN
7038: --
7039: -- DBMS_OUTPUT.PUT_LINE('error: '|| SQLCODE);
7040: ROLLBACK TO Update_Row_Pvt ;
7041: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7042: --
7043:
7044: --
7045: FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
7545: FND_MSG_PUB.Add;
7546:
7547: END IF;
7548:
7549: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7550:
7551: END Circular_Ref_Check;
7552:
7553: /*===========================================================================+
7559: -- NOTE: This is currently used for Cost Object Hierarchies.
7560: --
7561: PROCEDURE Add_Relation(
7562: p_api_version IN NUMBER,
7563: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
7564: p_commit IN VARCHAR2 := FND_API.G_FALSE,
7565: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
7566: x_return_status OUT NOCOPY VARCHAR2,
7567: x_msg_count OUT NOCOPY NUMBER,
7560: --
7561: PROCEDURE Add_Relation(
7562: p_api_version IN NUMBER,
7563: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
7564: p_commit IN VARCHAR2 := FND_API.G_FALSE,
7565: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
7566: x_return_status OUT NOCOPY VARCHAR2,
7567: x_msg_count OUT NOCOPY NUMBER,
7568: x_msg_data OUT NOCOPY VARCHAR2,
7561: PROCEDURE Add_Relation(
7562: p_api_version IN NUMBER,
7563: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
7564: p_commit IN VARCHAR2 := FND_API.G_FALSE,
7565: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
7566: x_return_status OUT NOCOPY VARCHAR2,
7567: x_msg_count OUT NOCOPY NUMBER,
7568: x_msg_data OUT NOCOPY VARCHAR2,
7569: --
7627: BEGIN
7628:
7629: SAVEPOINT Add_Relation_Pvt;
7630: --
7631: IF NOT FND_API.Compatible_API_Call ( l_api_version,
7632: p_api_version,
7633: l_api_name,
7634: G_PKG_NAME )
7635: THEN
7632: p_api_version,
7633: l_api_name,
7634: G_PKG_NAME )
7635: THEN
7636: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
7637: END IF;
7638: --
7639:
7640: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
7636: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
7637: END IF;
7638: --
7639:
7640: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
7641: FND_MSG_PUB.initialize ;
7642: END IF;
7643: --
7644:
7645: ----- Start: Bug#5895840: Raising an exception and later catching it and throwing an error message
7646: ----- if there is no fem:period or fem:dataset profile set for the user
7647:
7648: IF(l_cal_period_id IS NULL) THEN
7649: RAISE FND_API.G_EXC_ERROR;
7650: END IF;
7651:
7652: IF(l_dataset_code IS NULL) THEN
7653: RAISE FND_API.G_EXC_ERROR;
7649: RAISE FND_API.G_EXC_ERROR;
7650: END IF;
7651:
7652: IF(l_dataset_code IS NULL) THEN
7653: RAISE FND_API.G_EXC_ERROR;
7654: END IF;
7655: ---- End: Bug#5895840
7656:
7657:
7772: x_msg_count => x_msg_count,
7773: x_msg_data => x_msg_data);
7774:
7775:
7776: IF x_return_status = FND_API.G_RET_STS_ERROR
7777: THEN
7778: RAISE FND_API.G_EXC_ERROR ;
7779: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
7780: THEN
7774:
7775:
7776: IF x_return_status = FND_API.G_RET_STS_ERROR
7777: THEN
7778: RAISE FND_API.G_EXC_ERROR ;
7779: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
7780: THEN
7781: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
7782: END IF;
7775:
7776: IF x_return_status = FND_API.G_RET_STS_ERROR
7777: THEN
7778: RAISE FND_API.G_EXC_ERROR ;
7779: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
7780: THEN
7781: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
7782: END IF;
7783:
7777: THEN
7778: RAISE FND_API.G_EXC_ERROR ;
7779: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
7780: THEN
7781: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
7782: END IF;
7783:
7784: END IF;
7785:
7782: END IF;
7783:
7784: END IF;
7785:
7786: IF FND_API.To_Boolean(p_commit) THEN
7787: COMMIT WORK;
7788: END iF;
7789:
7790: x_return_status := FND_API.G_RET_STS_SUCCESS ;
7786: IF FND_API.To_Boolean(p_commit) THEN
7787: COMMIT WORK;
7788: END iF;
7789:
7790: x_return_status := FND_API.G_RET_STS_SUCCESS ;
7791:
7792: EXCEPTION
7793: WHEN FND_API.G_EXC_ERROR THEN
7794:
7789:
7790: x_return_status := FND_API.G_RET_STS_SUCCESS ;
7791:
7792: EXCEPTION
7793: WHEN FND_API.G_EXC_ERROR THEN
7794:
7795: ---- Start : Bug#5895840
7796: IF(l_cal_period_id IS NULL) THEN
7797: FND_MESSAGE.SET_NAME('FEM','FEM_DHM_CREATE_REL_PROFILE_ERR');
7804: END IF;
7805: ---- End: Bug#5895840
7806:
7807: ROLLBACK TO Add_Relation_Pvt ;
7808: x_return_status := FND_API.G_RET_STS_ERROR;
7809: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
7810: p_data => x_msg_data );
7811:
7812: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7808: x_return_status := FND_API.G_RET_STS_ERROR;
7809: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
7810: p_data => x_msg_data );
7811:
7812: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7813:
7814: ROLLBACK TO Add_Relation_Pvt ;
7815: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7816: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
7811:
7812: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7813:
7814: ROLLBACK TO Add_Relation_Pvt ;
7815: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7816: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
7817: p_data => x_msg_data );
7818:
7819: WHEN OTHERS THEN
7818:
7819: WHEN OTHERS THEN
7820:
7821: ROLLBACK TO Add_Relation_Pvt ;
7822: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7823:
7824: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
7825: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
7826: l_api_name);
7840: -- NOTE: This is currently used for Cost Object Hierarchies.
7841: --
7842: PROCEDURE Add_Relations(
7843: p_api_version IN NUMBER,
7844: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
7845: p_commit IN VARCHAR2 := FND_API.G_FALSE,
7846: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
7847: x_return_status OUT NOCOPY VARCHAR2,
7848: x_msg_count OUT NOCOPY NUMBER,
7841: --
7842: PROCEDURE Add_Relations(
7843: p_api_version IN NUMBER,
7844: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
7845: p_commit IN VARCHAR2 := FND_API.G_FALSE,
7846: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
7847: x_return_status OUT NOCOPY VARCHAR2,
7848: x_msg_count OUT NOCOPY NUMBER,
7849: x_msg_data OUT NOCOPY VARCHAR2,
7842: PROCEDURE Add_Relations(
7843: p_api_version IN NUMBER,
7844: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
7845: p_commit IN VARCHAR2 := FND_API.G_FALSE,
7846: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
7847: x_return_status OUT NOCOPY VARCHAR2,
7848: x_msg_count OUT NOCOPY NUMBER,
7849: x_msg_data OUT NOCOPY VARCHAR2,
7850: --
7859: l_api_version CONSTANT NUMBER := 1.0;
7860:
7861: l_relations_tab_type FEM_DHM_HIER_NODE_TAB_TYP;
7862:
7863: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
7864: l_msg_count NUMBER;
7865: l_msg_data VARCHAR2(2000);
7866:
7867: BEGIN
7867: BEGIN
7868:
7869: SAVEPOINT Add_Relations_Pvt;
7870: --
7871: IF NOT FND_API.Compatible_API_Call ( l_api_version,
7872: p_api_version,
7873: l_api_name,
7874: G_PKG_NAME )
7875: THEN
7872: p_api_version,
7873: l_api_name,
7874: G_PKG_NAME )
7875: THEN
7876: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
7877: END IF;
7878: --
7879:
7880: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
7876: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
7877: END IF;
7878: --
7879:
7880: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
7881: FND_MSG_PUB.initialize ;
7882: END IF;
7883: --
7884:
7901: p_bom_reference => l_relations_tab_type(i).bom_reference,
7902: p_display_order_num=>l_relations_tab_type(i).display_order_num,
7903: p_hier_table_name => p_hier_table_name);
7904:
7905: IF l_return_status = FND_API.G_RET_STS_ERROR
7906: THEN
7907: RAISE FND_API.G_EXC_ERROR ;
7908: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
7909: THEN
7903: p_hier_table_name => p_hier_table_name);
7904:
7905: IF l_return_status = FND_API.G_RET_STS_ERROR
7906: THEN
7907: RAISE FND_API.G_EXC_ERROR ;
7908: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
7909: THEN
7910: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
7911: END IF;
7904:
7905: IF l_return_status = FND_API.G_RET_STS_ERROR
7906: THEN
7907: RAISE FND_API.G_EXC_ERROR ;
7908: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
7909: THEN
7910: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
7911: END IF;
7912:
7906: THEN
7907: RAISE FND_API.G_EXC_ERROR ;
7908: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
7909: THEN
7910: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
7911: END IF;
7912:
7913: END LOOP;
7914:
7911: END IF;
7912:
7913: END LOOP;
7914:
7915: IF FND_API.To_Boolean(p_commit) THEN
7916: COMMIT WORK;
7917: END iF;
7918:
7919: x_return_status := FND_API.G_RET_STS_SUCCESS ;
7915: IF FND_API.To_Boolean(p_commit) THEN
7916: COMMIT WORK;
7917: END iF;
7918:
7919: x_return_status := FND_API.G_RET_STS_SUCCESS ;
7920:
7921: EXCEPTION
7922: WHEN FND_API.G_EXC_ERROR THEN
7923:
7918:
7919: x_return_status := FND_API.G_RET_STS_SUCCESS ;
7920:
7921: EXCEPTION
7922: WHEN FND_API.G_EXC_ERROR THEN
7923:
7924: ROLLBACK TO Add_Relations_Pvt ;
7925: x_return_status := FND_API.G_RET_STS_ERROR;
7926: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
7921: EXCEPTION
7922: WHEN FND_API.G_EXC_ERROR THEN
7923:
7924: ROLLBACK TO Add_Relations_Pvt ;
7925: x_return_status := FND_API.G_RET_STS_ERROR;
7926: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
7927: p_data => x_msg_data );
7928:
7929: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7925: x_return_status := FND_API.G_RET_STS_ERROR;
7926: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
7927: p_data => x_msg_data );
7928:
7929: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7930:
7931: ROLLBACK TO Add_Relations_Pvt ;
7932: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7933: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
7928:
7929: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7930:
7931: ROLLBACK TO Add_Relations_Pvt ;
7932: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7933: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
7934: p_data => x_msg_data );
7935:
7936: WHEN OTHERS THEN
7935:
7936: WHEN OTHERS THEN
7937:
7938: ROLLBACK TO Add_Relations_Pvt ;
7939: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7940:
7941: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
7942: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
7943: l_api_name);
7998: -- NOTE: This is currently used for Cost Object Hierarchies.
7999: --
8000: PROCEDURE Remove_Relation(
8001: p_api_version IN NUMBER,
8002: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8003: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8004: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8005: x_return_status OUT NOCOPY VARCHAR2,
8006: x_msg_count OUT NOCOPY NUMBER,
7999: --
8000: PROCEDURE Remove_Relation(
8001: p_api_version IN NUMBER,
8002: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8003: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8004: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8005: x_return_status OUT NOCOPY VARCHAR2,
8006: x_msg_count OUT NOCOPY NUMBER,
8007: x_msg_data OUT NOCOPY VARCHAR2,
8000: PROCEDURE Remove_Relation(
8001: p_api_version IN NUMBER,
8002: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8003: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8004: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8005: x_return_status OUT NOCOPY VARCHAR2,
8006: x_msg_count OUT NOCOPY NUMBER,
8007: x_msg_data OUT NOCOPY VARCHAR2,
8008: --
8066: BEGIN
8067:
8068: SAVEPOINT Remove_Relation_Pvt;
8069: --
8070: IF NOT FND_API.Compatible_API_Call ( l_api_version,
8071: p_api_version,
8072: l_api_name,
8073: G_PKG_NAME )
8074: THEN
8071: p_api_version,
8072: l_api_name,
8073: G_PKG_NAME )
8074: THEN
8075: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8076: END IF;
8077: --
8078:
8079: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
8075: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8076: END IF;
8077: --
8078:
8079: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
8080: FND_MSG_PUB.initialize ;
8081: END IF;
8082:
8083: IF p_hier_table_name = 'FEM_COST_OBJECTS_HIER' THEN
8349: p_curr_ver_start_date => l_curr_version_start_date);
8350: */
8351: END IF;
8352:
8353: IF FND_API.To_Boolean(p_commit) THEN
8354: COMMIT WORK;
8355: END iF;
8356:
8357: x_return_status := FND_API.G_RET_STS_SUCCESS ;
8353: IF FND_API.To_Boolean(p_commit) THEN
8354: COMMIT WORK;
8355: END iF;
8356:
8357: x_return_status := FND_API.G_RET_STS_SUCCESS ;
8358:
8359: EXCEPTION
8360: WHEN FND_API.G_EXC_ERROR THEN
8361:
8356:
8357: x_return_status := FND_API.G_RET_STS_SUCCESS ;
8358:
8359: EXCEPTION
8360: WHEN FND_API.G_EXC_ERROR THEN
8361:
8362: ROLLBACK TO Remove_Relation_Pvt ;
8363: x_return_status := FND_API.G_RET_STS_ERROR;
8364: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8359: EXCEPTION
8360: WHEN FND_API.G_EXC_ERROR THEN
8361:
8362: ROLLBACK TO Remove_Relation_Pvt ;
8363: x_return_status := FND_API.G_RET_STS_ERROR;
8364: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8365: p_data => x_msg_data );
8366:
8367: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8363: x_return_status := FND_API.G_RET_STS_ERROR;
8364: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8365: p_data => x_msg_data );
8366:
8367: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8368:
8369: ROLLBACK TO Remove_Relation_Pvt ;
8370: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8371: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8366:
8367: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8368:
8369: ROLLBACK TO Remove_Relation_Pvt ;
8370: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8371: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8372: p_data => x_msg_data );
8373:
8374: WHEN OTHERS THEN
8373:
8374: WHEN OTHERS THEN
8375:
8376: ROLLBACK TO Remove_Relation_Pvt ;
8377: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8378:
8379: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
8380: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
8381: l_api_name);
8394: -- NOTE: This is currently used for Cost Object Hierarchies.
8395: --
8396: PROCEDURE Update_Relation(
8397: p_api_version IN NUMBER,
8398: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8399: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8400: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8401: x_return_status OUT NOCOPY VARCHAR2,
8402: x_msg_count OUT NOCOPY NUMBER,
8395: --
8396: PROCEDURE Update_Relation(
8397: p_api_version IN NUMBER,
8398: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8399: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8400: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8401: x_return_status OUT NOCOPY VARCHAR2,
8402: x_msg_count OUT NOCOPY NUMBER,
8403: x_msg_data OUT NOCOPY VARCHAR2,
8396: PROCEDURE Update_Relation(
8397: p_api_version IN NUMBER,
8398: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8399: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8400: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8401: x_return_status OUT NOCOPY VARCHAR2,
8402: x_msg_count OUT NOCOPY NUMBER,
8403: x_msg_data OUT NOCOPY VARCHAR2,
8404: --
8457: BEGIN
8458:
8459: SAVEPOINT Update_Relation_Pvt;
8460: --
8461: IF NOT FND_API.Compatible_API_Call ( l_api_version,
8462: p_api_version,
8463: l_api_name,
8464: G_PKG_NAME )
8465: THEN
8462: p_api_version,
8463: l_api_name,
8464: G_PKG_NAME )
8465: THEN
8466: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8467: END IF;
8468: --
8469:
8470: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
8466: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8467: END IF;
8468: --
8469:
8470: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
8471: FND_MSG_PUB.initialize ;
8472: END IF;
8473:
8474: IF p_hier_table_name = 'FEM_COST_OBJECTS_HIER' THEN
8544: -- Bug#5723800 : End
8545:
8546: END IF;
8547:
8548: IF FND_API.To_Boolean(p_commit) THEN
8549: COMMIT WORK;
8550: END iF;
8551:
8552: x_return_status := FND_API.G_RET_STS_SUCCESS ;
8548: IF FND_API.To_Boolean(p_commit) THEN
8549: COMMIT WORK;
8550: END iF;
8551:
8552: x_return_status := FND_API.G_RET_STS_SUCCESS ;
8553:
8554: EXCEPTION
8555: WHEN FND_API.G_EXC_ERROR THEN
8556:
8551:
8552: x_return_status := FND_API.G_RET_STS_SUCCESS ;
8553:
8554: EXCEPTION
8555: WHEN FND_API.G_EXC_ERROR THEN
8556:
8557: ROLLBACK TO Update_Relation_Pvt ;
8558: x_return_status := FND_API.G_RET_STS_ERROR;
8559: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8554: EXCEPTION
8555: WHEN FND_API.G_EXC_ERROR THEN
8556:
8557: ROLLBACK TO Update_Relation_Pvt ;
8558: x_return_status := FND_API.G_RET_STS_ERROR;
8559: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8560: p_data => x_msg_data );
8561:
8562: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8558: x_return_status := FND_API.G_RET_STS_ERROR;
8559: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8560: p_data => x_msg_data );
8561:
8562: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8563:
8564: ROLLBACK TO Update_Relation_Pvt ;
8565: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8566: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8561:
8562: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8563:
8564: ROLLBACK TO Update_Relation_Pvt ;
8565: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8566: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8567: p_data => x_msg_data );
8568:
8569: WHEN OTHERS THEN
8568:
8569: WHEN OTHERS THEN
8570:
8571: ROLLBACK TO Update_Relation_Pvt ;
8572: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8573:
8574: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
8575: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
8576: l_api_name);
8589: -- NOTE: This is currently used for Cost Object Hierarchies.
8590: --
8591: PROCEDURE Update_Relations(
8592: p_api_version IN NUMBER,
8593: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8594: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8595: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8596: x_return_status OUT NOCOPY VARCHAR2,
8597: x_msg_count OUT NOCOPY NUMBER,
8590: --
8591: PROCEDURE Update_Relations(
8592: p_api_version IN NUMBER,
8593: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8594: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8595: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8596: x_return_status OUT NOCOPY VARCHAR2,
8597: x_msg_count OUT NOCOPY NUMBER,
8598: x_msg_data OUT NOCOPY VARCHAR2,
8591: PROCEDURE Update_Relations(
8592: p_api_version IN NUMBER,
8593: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8594: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8595: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8596: x_return_status OUT NOCOPY VARCHAR2,
8597: x_msg_count OUT NOCOPY NUMBER,
8598: x_msg_data OUT NOCOPY VARCHAR2,
8599: --
8608: l_api_version CONSTANT NUMBER := 1.0;
8609:
8610: l_relations_tab_type FEM_DHM_HIER_NODE_TAB_TYP;
8611:
8612: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
8613: l_msg_count NUMBER;
8614: l_msg_data VARCHAR2(2000);
8615:
8616: BEGIN
8616: BEGIN
8617:
8618: SAVEPOINT Update_Relations_Pvt;
8619: --
8620: IF NOT FND_API.Compatible_API_Call ( l_api_version,
8621: p_api_version,
8622: l_api_name,
8623: G_PKG_NAME )
8624: THEN
8621: p_api_version,
8622: l_api_name,
8623: G_PKG_NAME )
8624: THEN
8625: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8626: END IF;
8627: --
8628:
8629: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
8625: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8626: END IF;
8627: --
8628:
8629: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
8630: FND_MSG_PUB.initialize ;
8631: END IF;
8632:
8633: l_relations_tab_type := p_relation_details_tbl;
8652: => l_relations_tab_type(i).display_order_num,
8653: p_bom_reference => l_relations_tab_type(i).bom_reference,
8654: p_hier_table_name => p_hier_table_name);
8655:
8656: IF l_return_status = FND_API.G_RET_STS_ERROR
8657: THEN
8658: RAISE FND_API.G_EXC_ERROR ;
8659: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
8660: THEN
8654: p_hier_table_name => p_hier_table_name);
8655:
8656: IF l_return_status = FND_API.G_RET_STS_ERROR
8657: THEN
8658: RAISE FND_API.G_EXC_ERROR ;
8659: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
8660: THEN
8661: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8662: END IF;
8655:
8656: IF l_return_status = FND_API.G_RET_STS_ERROR
8657: THEN
8658: RAISE FND_API.G_EXC_ERROR ;
8659: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
8660: THEN
8661: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8662: END IF;
8663:
8657: THEN
8658: RAISE FND_API.G_EXC_ERROR ;
8659: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
8660: THEN
8661: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8662: END IF;
8663:
8664: END LOOP;
8665:
8662: END IF;
8663:
8664: END LOOP;
8665:
8666: x_return_status := FND_API.G_RET_STS_SUCCESS ;
8667:
8668: EXCEPTION
8669: WHEN FND_API.G_EXC_ERROR THEN
8670:
8665:
8666: x_return_status := FND_API.G_RET_STS_SUCCESS ;
8667:
8668: EXCEPTION
8669: WHEN FND_API.G_EXC_ERROR THEN
8670:
8671: ROLLBACK TO Update_Relations_Pvt ;
8672: x_return_status := FND_API.G_RET_STS_ERROR;
8673: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8668: EXCEPTION
8669: WHEN FND_API.G_EXC_ERROR THEN
8670:
8671: ROLLBACK TO Update_Relations_Pvt ;
8672: x_return_status := FND_API.G_RET_STS_ERROR;
8673: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8674: p_data => x_msg_data );
8675:
8676: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8672: x_return_status := FND_API.G_RET_STS_ERROR;
8673: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8674: p_data => x_msg_data );
8675:
8676: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8677:
8678: ROLLBACK TO Update_Relations_Pvt ;
8679: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8680: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8675:
8676: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8677:
8678: ROLLBACK TO Update_Relations_Pvt ;
8679: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8680: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8681: p_data => x_msg_data );
8682:
8683: WHEN OTHERS THEN
8682:
8683: WHEN OTHERS THEN
8684:
8685: ROLLBACK TO Update_Relations_Pvt ;
8686: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8687:
8688: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
8689: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
8690: l_api_name);
8700: -- API to move a Relation from one node to another.
8701:
8702: PROCEDURE Move_Relation(
8703: p_api_version IN NUMBER,
8704: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8705: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8706: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8707: x_return_status OUT NOCOPY VARCHAR2,
8708: x_msg_count OUT NOCOPY NUMBER,
8701:
8702: PROCEDURE Move_Relation(
8703: p_api_version IN NUMBER,
8704: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8705: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8706: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8707: x_return_status OUT NOCOPY VARCHAR2,
8708: x_msg_count OUT NOCOPY NUMBER,
8709: x_msg_data OUT NOCOPY VARCHAR2,
8702: PROCEDURE Move_Relation(
8703: p_api_version IN NUMBER,
8704: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8705: p_commit IN VARCHAR2 := FND_API.G_FALSE,
8706: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
8707: x_return_status OUT NOCOPY VARCHAR2,
8708: x_msg_count OUT NOCOPY NUMBER,
8709: x_msg_data OUT NOCOPY VARCHAR2,
8710: --
8719:
8720: l_api_name CONSTANT VARCHAR2(30) := 'Move_Relation';
8721: l_api_version CONSTANT NUMBER := 1.0;
8722:
8723: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
8724: l_msg_count NUMBER;
8725: l_msg_data VARCHAR2(2000);
8726:
8727: l_relationship_id NUMBER;
8761: BEGIN
8762:
8763: SAVEPOINT Move_Relation_Pvt;
8764: --
8765: IF NOT FND_API.Compatible_API_Call ( l_api_version,
8766: p_api_version,
8767: l_api_name,
8768: G_PKG_NAME )
8769: THEN
8766: p_api_version,
8767: l_api_name,
8768: G_PKG_NAME )
8769: THEN
8770: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8771: END IF;
8772: --
8773:
8774: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
8770: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8771: END IF;
8772: --
8773:
8774: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
8775: FND_MSG_PUB.initialize ;
8776: END IF;
8777: --
8778:
8807: p_child_sequence_num => p_child_sequence_num,
8808: p_hier_table_name => p_hier_table_name,
8809: p_remove_all_children_flag => 'N');
8810:
8811: IF l_return_status = FND_API.G_RET_STS_ERROR
8812: THEN
8813: RAISE FND_API.G_EXC_ERROR ;
8814: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
8815: THEN
8809: p_remove_all_children_flag => 'N');
8810:
8811: IF l_return_status = FND_API.G_RET_STS_ERROR
8812: THEN
8813: RAISE FND_API.G_EXC_ERROR ;
8814: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
8815: THEN
8816: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8817: END IF;
8810:
8811: IF l_return_status = FND_API.G_RET_STS_ERROR
8812: THEN
8813: RAISE FND_API.G_EXC_ERROR ;
8814: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
8815: THEN
8816: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8817: END IF;
8818:
8812: THEN
8813: RAISE FND_API.G_EXC_ERROR ;
8814: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
8815: THEN
8816: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8817: END IF;
8818:
8819: Add_Relation(p_api_version => '1.0',
8820: x_return_status => l_return_status,
8830: p_bom_reference => l_bom_reference,
8831: p_display_order_num => l_display_order_num,
8832: p_hier_table_name => p_hier_table_name);
8833:
8834: IF l_return_status = FND_API.G_RET_STS_ERROR
8835: THEN
8836: RAISE FND_API.G_EXC_ERROR ;
8837: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
8838: THEN
8832: p_hier_table_name => p_hier_table_name);
8833:
8834: IF l_return_status = FND_API.G_RET_STS_ERROR
8835: THEN
8836: RAISE FND_API.G_EXC_ERROR ;
8837: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
8838: THEN
8839: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8840: END IF;
8833:
8834: IF l_return_status = FND_API.G_RET_STS_ERROR
8835: THEN
8836: RAISE FND_API.G_EXC_ERROR ;
8837: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
8838: THEN
8839: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8840: END IF;
8841:
8835: THEN
8836: RAISE FND_API.G_EXC_ERROR ;
8837: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
8838: THEN
8839: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8840: END IF;
8841:
8842: END IF;
8843:
8840: END IF;
8841:
8842: END IF;
8843:
8844: IF FND_API.To_Boolean(p_commit) THEN
8845: COMMIT WORK;
8846: END iF;
8847:
8848: x_return_status := FND_API.G_RET_STS_SUCCESS ;
8844: IF FND_API.To_Boolean(p_commit) THEN
8845: COMMIT WORK;
8846: END iF;
8847:
8848: x_return_status := FND_API.G_RET_STS_SUCCESS ;
8849:
8850: EXCEPTION
8851: WHEN FND_API.G_EXC_ERROR THEN
8852:
8847:
8848: x_return_status := FND_API.G_RET_STS_SUCCESS ;
8849:
8850: EXCEPTION
8851: WHEN FND_API.G_EXC_ERROR THEN
8852:
8853: ROLLBACK TO Move_Relation_Pvt ;
8854: x_return_status := FND_API.G_RET_STS_ERROR;
8855: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8850: EXCEPTION
8851: WHEN FND_API.G_EXC_ERROR THEN
8852:
8853: ROLLBACK TO Move_Relation_Pvt ;
8854: x_return_status := FND_API.G_RET_STS_ERROR;
8855: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8856: p_data => x_msg_data );
8857:
8858: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8854: x_return_status := FND_API.G_RET_STS_ERROR;
8855: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8856: p_data => x_msg_data );
8857:
8858: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8859:
8860: ROLLBACK TO Move_Relation_Pvt ;
8861: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8862: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8857:
8858: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8859:
8860: ROLLBACK TO Move_Relation_Pvt ;
8861: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8862: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8863: p_data => x_msg_data );
8864:
8865: WHEN OTHERS THEN
8864:
8865: WHEN OTHERS THEN
8866:
8867: ROLLBACK TO Move_Relation_Pvt ;
8868: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8869:
8870: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
8871: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
8872: l_api_name);
8886:
8887: PROCEDURE Flatten_Whole_Hierarchy
8888: (
8889: p_api_version IN NUMBER ,
8890: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
8891: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
8892: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
8893: x_return_status OUT NOCOPY VARCHAR2 ,
8894: x_msg_count OUT NOCOPY NUMBER ,
8887: PROCEDURE Flatten_Whole_Hierarchy
8888: (
8889: p_api_version IN NUMBER ,
8890: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
8891: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
8892: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
8893: x_return_status OUT NOCOPY VARCHAR2 ,
8894: x_msg_count OUT NOCOPY NUMBER ,
8895: x_msg_data OUT NOCOPY VARCHAR2 ,
8888: (
8889: p_api_version IN NUMBER ,
8890: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
8891: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
8892: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
8893: x_return_status OUT NOCOPY VARCHAR2 ,
8894: x_msg_count OUT NOCOPY NUMBER ,
8895: x_msg_data OUT NOCOPY VARCHAR2 ,
8896: p_hierarchy_id IN NUMBER
8905:
8906: l_api_name CONSTANT VARCHAR2(30) := 'Flatten_Whole_Hierarchy' ;
8907: l_api_version CONSTANT NUMBER := 1.0;
8908:
8909: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
8910: l_msg_count NUMBER;
8911: l_msg_data VARCHAR2(2000);
8912:
8913: BEGIN
8913: BEGIN
8914:
8915: SAVEPOINT Flatten_Whole_Hierarchy_Pvt;
8916:
8917: IF NOT FND_API.Compatible_API_Call ( l_api_version,
8918: p_api_version,
8919: l_api_name,
8920: G_PKG_NAME )
8921: THEN
8918: p_api_version,
8919: l_api_name,
8920: G_PKG_NAME )
8921: THEN
8922: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8923: END IF;
8924: --
8925:
8926: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
8922: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8923: END IF;
8924: --
8925:
8926: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
8927: FND_MSG_PUB.initialize ;
8928: END IF;
8929:
8930: x_return_status := FND_API.G_RET_STS_SUCCESS ;
8926: IF FND_API.to_Boolean ( p_init_msg_list ) THEN
8927: FND_MSG_PUB.initialize ;
8928: END IF;
8929:
8930: x_return_status := FND_API.G_RET_STS_SUCCESS ;
8931:
8932: FOR l_hier_version_rec IN l_hier_version_csr(c_hierarchy_id => p_hierarchy_id)
8933: LOOP
8934:
8938: x_msg_count => l_msg_count,
8939: x_msg_data => l_msg_data,
8940: p_hier_obj_defn_id => l_hier_version_rec.object_definition_id);
8941:
8942: IF l_return_status = FND_API.G_RET_STS_ERROR
8943: THEN
8944: RAISE FND_API.G_EXC_ERROR ;
8945: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
8946: THEN
8940: p_hier_obj_defn_id => l_hier_version_rec.object_definition_id);
8941:
8942: IF l_return_status = FND_API.G_RET_STS_ERROR
8943: THEN
8944: RAISE FND_API.G_EXC_ERROR ;
8945: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
8946: THEN
8947: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8948: END IF;
8941:
8942: IF l_return_status = FND_API.G_RET_STS_ERROR
8943: THEN
8944: RAISE FND_API.G_EXC_ERROR ;
8945: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
8946: THEN
8947: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8948: END IF;
8949:
8943: THEN
8944: RAISE FND_API.G_EXC_ERROR ;
8945: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
8946: THEN
8947: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
8948: END IF;
8949:
8950: END LOOP;
8951:
8955: (SELECT object_definition_id
8956: FROM fem_object_definition_b
8957: WHERE object_id = p_hierarchy_id);
8958:
8959: IF FND_API.To_Boolean(p_commit) THEN
8960: COMMIT WORK;
8961: END iF;
8962:
8963: x_return_status := FND_API.G_RET_STS_SUCCESS ;
8959: IF FND_API.To_Boolean(p_commit) THEN
8960: COMMIT WORK;
8961: END iF;
8962:
8963: x_return_status := FND_API.G_RET_STS_SUCCESS ;
8964:
8965: EXCEPTION
8966: WHEN FND_API.G_EXC_ERROR THEN
8967:
8962:
8963: x_return_status := FND_API.G_RET_STS_SUCCESS ;
8964:
8965: EXCEPTION
8966: WHEN FND_API.G_EXC_ERROR THEN
8967:
8968: ROLLBACK TO Flatten_Whole_Hierarchy_Pvt;
8969: x_return_status := FND_API.G_RET_STS_ERROR;
8970: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8965: EXCEPTION
8966: WHEN FND_API.G_EXC_ERROR THEN
8967:
8968: ROLLBACK TO Flatten_Whole_Hierarchy_Pvt;
8969: x_return_status := FND_API.G_RET_STS_ERROR;
8970: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8971: p_data => x_msg_data );
8972:
8973: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8969: x_return_status := FND_API.G_RET_STS_ERROR;
8970: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8971: p_data => x_msg_data );
8972:
8973: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8974:
8975: ROLLBACK TO Flatten_Whole_Hierarchy_Pvt;
8976: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8977: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8972:
8973: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8974:
8975: ROLLBACK TO Flatten_Whole_Hierarchy_Pvt;
8976: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8977: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
8978: p_data => x_msg_data );
8979:
8980: WHEN OTHERS THEN
8979:
8980: WHEN OTHERS THEN
8981:
8982: ROLLBACK TO Flatten_Whole_Hierarchy_Pvt;
8983: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8984:
8985: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
8986: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
8987: l_api_name);
9037: COMMIT WORK;
9038:
9039: EXCEPTION
9040:
9041: WHEN FND_API.G_EXC_ERROR THEN
9042: --
9043: retcode := 2 ;
9044: COMMIT WORK ;
9045: --
9042: --
9043: retcode := 2 ;
9044: COMMIT WORK ;
9045: --
9046: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
9047: --
9048: retcode := 2 ;
9049: COMMIT WORK ;
9050: --