1: PACKAGE BODY CSP_REP_HIERARCHIES_PKG as
2: /* $Header: csptprhb.pls 120.0 2005/05/24 18:26:33 appldev noship $ */
3: -- Start of Comments
4: -- Package name : CSP_REP_HIERARCHIES_PKG
5: -- Purpose :
1: PACKAGE BODY CSP_REP_HIERARCHIES_PKG as
2: /* $Header: csptprhb.pls 120.0 2005/05/24 18:26:33 appldev noship $ */
3: -- Start of Comments
4: -- Package name : CSP_REP_HIERARCHIES_PKG
5: -- Purpose :
6: -- History :
7: -- NOTE :
8: -- End of Comments
7: -- NOTE :
8: -- End of Comments
9:
10:
11: G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_REP_HIERARCHIES_PKG';
12: G_FILE_NAME CONSTANT VARCHAR2(12) := 'csptephb.pls';
13:
14: PROCEDURE Insert_Row(
15: px_HIERARCHY_NODE_ID IN OUT NOCOPY NUMBER,
40: p_ATTRIBUTE14 VARCHAR2,
41: p_ATTRIBUTE15 VARCHAR2)
42:
43: IS
44: CURSOR C2 IS SELECT CSP_REP_HIERARCHIES_S1.nextval FROM sys.dual;
45: BEGIN
46: If (px_HIERARCHY_NODE_ID IS NULL) OR (px_HIERARCHY_NODE_ID = FND_API.G_MISS_NUM) then
47: OPEN C2;
48: FETCH C2 INTO px_HIERARCHY_NODE_ID;
47: OPEN C2;
48: FETCH C2 INTO px_HIERARCHY_NODE_ID;
49: CLOSE C2;
50: End If;
51: INSERT INTO CSP_REP_HIERARCHIES(
52: HIERARCHY_NODE_ID,
53: CREATED_BY,
54: CREATION_DATE,
55: LAST_UPDATED_BY,
135: p_ATTRIBUTE14 VARCHAR2,
136: p_ATTRIBUTE15 VARCHAR2)
137: IS
138: BEGIN
139: Update CSP_REP_HIERARCHIES
140: SET
141: HIERARCHY_NODE_ID = decode( p_HIERARCHY_NODE_ID, FND_API.G_MISS_NUM, HIERARCHY_NODE_ID, p_HIERARCHY_NODE_ID),
142:
143: CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
189: p_HIERARCHY_NODE_ID NUMBER)
190: IS
191:
192: Cursor c1(P_HIERARCHY_NODE_ID Number) is
193: select HIERARCHY_NODE_ID from CSP_REP_HIERARCHIES
194: start with HIERARCHY_NODE_ID = P_HIERARCHY_NODE_ID
195: connect by PARENT_NODE_ID = PRIOR HIERARCHY_NODE_ID;
196:
197: l_HIERARCHY_NODE_ID Number;
207: -- Update Subinventory for all the SubHierarchy nodes
208: Update csp_sec_inventories
209: set hierarchy_node_id = NULL
210: where hierarchy_node_id in (select rh.hierarchy_node_id
211: from csp_rep_hierarchies rh
212: where parent_node_id = p_HIERARCHY_NODE_ID);
213: -- Delete all Subhierarchies
214:
215: Open c1(p_HIERARCHY_NODE_ID);
215: Open c1(p_HIERARCHY_NODE_ID);
216: Loop
217: Fetch c1 into l_HIERARCHY_NODE_ID;
218: Exit when c1%NotFound;
219: DELETE FROM CSP_REP_HIERARCHIES
220: WHERE PARENT_NODE_ID = l_HIERARCHY_NODE_ID;
221: End Loop;
222: Close c1;
223:
220: WHERE PARENT_NODE_ID = l_HIERARCHY_NODE_ID;
221: End Loop;
222: Close c1;
223:
224: DELETE FROM CSP_REP_HIERARCHIES
225: WHERE HIERARCHY_NODE_ID = p_HIERARCHY_NODE_ID;
226: If (SQL%NOTFOUND) then
227: RAISE NO_DATA_FOUND;
228: End If;
260: p_ATTRIBUTE15 VARCHAR2)
261: IS
262: CURSOR C IS
263: SELECT *
264: FROM CSP_REP_HIERARCHIES
265: WHERE HIERARCHY_NODE_ID = p_HIERARCHY_NODE_ID
266: FOR UPDATE of HIERARCHY_NODE_ID NOWAIT;
267: Recinfo C%ROWTYPE;
268: BEGIN
365: APP_EXCEPTION.RAISE_EXCEPTION;
366: End If;
367: END Lock_Row;
368:
369: End CSP_REP_HIERARCHIES_PKG;