[Home] [Help]
PACKAGE BODY: APPS.CSP_REP_HIERARCHIES_PKG
Source
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
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,
16 p_CREATED_BY NUMBER,
17 p_CREATION_DATE DATE,
18 p_LAST_UPDATED_BY NUMBER,
19 p_LAST_UPDATE_DATE DATE,
20 p_LAST_UPDATE_LOGIN NUMBER,
21 p_NODE_TYPE VARCHAR2,
22 p_NODE_NAME VARCHAR2,
23 p_TOP_NODE_ID NUMBER,
24 p_PARENT_NODE_ID NUMBER,
25 p_ORGANIZATION_ID NUMBER,
26 p_ATTRIBUTE_CATEGORY VARCHAR2,
27 p_ATTRIBUTE1 VARCHAR2,
28 p_ATTRIBUTE2 VARCHAR2,
29 p_ATTRIBUTE3 VARCHAR2,
30 p_ATTRIBUTE4 VARCHAR2,
31 p_ATTRIBUTE5 VARCHAR2,
32 p_ATTRIBUTE6 VARCHAR2,
33 p_ATTRIBUTE7 VARCHAR2,
34 p_ATTRIBUTE8 VARCHAR2,
35 p_ATTRIBUTE9 VARCHAR2,
36 p_ATTRIBUTE10 VARCHAR2,
37 p_ATTRIBUTE11 VARCHAR2,
38 p_ATTRIBUTE12 VARCHAR2,
39 p_ATTRIBUTE13 VARCHAR2,
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;
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,
56 LAST_UPDATE_DATE,
57 LAST_UPDATE_LOGIN,
58 NODE_TYPE,
59 NODE_NAME,
60 TOP_NODE_ID,
61 PARENT_NODE_ID,
62 ORGANIZATION_ID,
63 ATTRIBUTE_CATEGORY,
64 ATTRIBUTE1,
65 ATTRIBUTE2,
66 ATTRIBUTE3,
67 ATTRIBUTE4,
68 ATTRIBUTE5,
69 ATTRIBUTE6,
70 ATTRIBUTE7,
71 ATTRIBUTE8,
72 ATTRIBUTE9,
73 ATTRIBUTE10,
74 ATTRIBUTE11,
75 ATTRIBUTE12,
76 ATTRIBUTE13,
77 ATTRIBUTE14,
78 ATTRIBUTE15
79 ) VALUES (
80 px_HIERARCHY_NODE_ID,
81 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
82 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, to_date(null), p_CREATION_DATE),
83 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
84 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, to_date(null), p_LAST_UPDATE_DATE),
85 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
86 decode( p_NODE_TYPE, FND_API.G_MISS_CHAR, NULL, p_NODE_TYPE),
87 decode( p_NODE_NAME, FND_API.G_MISS_CHAR, NULL, p_NODE_NAME),
88 decode( p_TOP_NODE_ID, FND_API.G_MISS_NUM, NULL, p_TOP_NODE_ID),
89 decode( p_PARENT_NODE_ID, FND_API.G_MISS_NUM, NULL, p_PARENT_NODE_ID),
90 decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, NULL, p_ORGANIZATION_ID),
91 decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY),
92 decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
93 decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
94 decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
95 decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
96 decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
97 decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
98 decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
99 decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
100 decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
101 decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
102 decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
103 decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
104 decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
105 decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
106 decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15));
107 End Insert_Row;
108
109 PROCEDURE Update_Row(
110 p_HIERARCHY_NODE_ID NUMBER,
111 p_CREATED_BY NUMBER,
112 p_CREATION_DATE DATE,
113 p_LAST_UPDATED_BY NUMBER,
114 p_LAST_UPDATE_DATE DATE,
115 p_LAST_UPDATE_LOGIN NUMBER,
116 p_NODE_TYPE VARCHAR2,
117 p_NODE_NAME VARCHAR2,
118 p_TOP_NODE_ID NUMBER,
119 p_PARENT_NODE_ID NUMBER,
120 p_ORGANIZATION_ID NUMBER,
121 p_ATTRIBUTE_CATEGORY VARCHAR2,
122 p_ATTRIBUTE1 VARCHAR2,
123 p_ATTRIBUTE2 VARCHAR2,
124 p_ATTRIBUTE3 VARCHAR2,
125 p_ATTRIBUTE4 VARCHAR2,
126 p_ATTRIBUTE5 VARCHAR2,
127 p_ATTRIBUTE6 VARCHAR2,
128 p_ATTRIBUTE7 VARCHAR2,
129 p_ATTRIBUTE8 VARCHAR2,
130 p_ATTRIBUTE9 VARCHAR2,
131 p_ATTRIBUTE10 VARCHAR2,
132 p_ATTRIBUTE11 VARCHAR2,
133 p_ATTRIBUTE12 VARCHAR2,
134 p_ATTRIBUTE13 VARCHAR2,
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),
144
145 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
146
147 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
148
149 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
150
151 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
152
153 NODE_TYPE = decode( p_NODE_TYPE, FND_API.G_MISS_CHAR, NODE_TYPE, p_NODE_TYPE),
154
155 NODE_NAME = decode( p_NODE_NAME, FND_API.G_MISS_CHAR, NODE_NAME, p_NODE_NAME),
156
157 TOP_NODE_ID = decode( p_TOP_NODE_ID, FND_API.G_MISS_NUM, TOP_NODE_ID, p_TOP_NODE_ID),
158
159 PARENT_NODE_ID = decode( p_PARENT_NODE_ID, FND_API.G_MISS_NUM, PARENT_NODE_ID, p_PARENT_NODE_ID),
160
161 ORGANIZATION_ID = decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, ORGANIZATION_ID, p_ORGANIZATION_ID),
162
163 ATTRIBUTE_CATEGORY = decode( p_ATTRIBUTE_CATEGORY,FND_API.G_MISS_CHAR, ATTRIBUTE_CATEGORY, p_ATTRIBUTE_CATEGORY),
164
165 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, p_ATTRIBUTE1),
166 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, p_ATTRIBUTE2),
167 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, p_ATTRIBUTE3),
168 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, p_ATTRIBUTE4),
169 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, p_ATTRIBUTE5),
170 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, p_ATTRIBUTE6),
171 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, p_ATTRIBUTE7),
172 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, p_ATTRIBUTE8),
173 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, p_ATTRIBUTE9),
174 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, p_ATTRIBUTE10),
175 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11,
176 p_ATTRIBUTE11),
177 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, p_ATTRIBUTE12),
178 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, p_ATTRIBUTE13),
179 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, p_ATTRIBUTE14),
180 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, p_ATTRIBUTE15)
181 where HIERARCHY_NODE_ID = p_HIERARCHY_NODE_ID;
182
183 If (SQL%NOTFOUND) then
184 RAISE NO_DATA_FOUND;
185 End If;
186 END Update_Row;
187
188 PROCEDURE Delete_Row(
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;
198
199
200 BEGIN
201
202 -- Update Subinventory for the Hierarchy node
203 Update csp_sec_inventories
204 set hierarchy_node_id = NULL
205 where hierarchy_node_id = p_HIERARCHY_NODE_ID;
206
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);
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
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;
229
230
231 END Delete_Row;
232
233 PROCEDURE Lock_Row(
234 p_HIERARCHY_NODE_ID NUMBER,
235 p_CREATED_BY NUMBER,
236 p_CREATION_DATE DATE,
237 p_LAST_UPDATED_BY NUMBER,
238 p_LAST_UPDATE_DATE DATE,
239 p_LAST_UPDATE_LOGIN NUMBER,
240 p_NODE_TYPE VARCHAR2,
241 p_NODE_NAME VARCHAR2,
242 p_TOP_NODE_ID NUMBER,
243 p_PARENT_NODE_ID NUMBER,
244 p_ORGANIZATION_ID NUMBER,
245 p_ATTRIBUTE_CATEGORY VARCHAR2,
246 p_ATTRIBUTE1 VARCHAR2,
247 p_ATTRIBUTE2 VARCHAR2,
248 p_ATTRIBUTE3 VARCHAR2,
249 p_ATTRIBUTE4 VARCHAR2,
250 p_ATTRIBUTE5 VARCHAR2,
251 p_ATTRIBUTE6 VARCHAR2,
252 p_ATTRIBUTE7 VARCHAR2,
253 p_ATTRIBUTE8 VARCHAR2,
254 p_ATTRIBUTE9 VARCHAR2,
255 p_ATTRIBUTE10 VARCHAR2,
256 p_ATTRIBUTE11 VARCHAR2,
257 p_ATTRIBUTE12 VARCHAR2,
258 p_ATTRIBUTE13 VARCHAR2,
259 p_ATTRIBUTE14 VARCHAR2,
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
269 OPEN C;
270 FETCH C INTO Recinfo;
271 If (C%NOTFOUND) then
272 CLOSE C;
273 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
274 APP_EXCEPTION.RAISE_EXCEPTION;
275 End If;
276 CLOSE C;
277 if (
278 ( Recinfo.HIERARCHY_NODE_ID = p_HIERARCHY_NODE_ID)
279 AND ( ( Recinfo.HIERARCHY_NODE_ID = p_HIERARCHY_NODE_ID)
280 OR ( ( Recinfo.HIERARCHY_NODE_ID IS NULL )
281 AND ( p_HIERARCHY_NODE_ID IS NULL )))
282 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
283 OR ( ( Recinfo.CREATED_BY IS NULL )
284 AND ( p_CREATED_BY IS NULL )))
285 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
286 OR ( ( Recinfo.CREATION_DATE IS NULL )
287 AND ( p_CREATION_DATE IS NULL )))
288 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
289 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
290 AND ( p_LAST_UPDATED_BY IS NULL )))
291 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
292 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
293 AND ( p_LAST_UPDATE_DATE IS NULL )))
294 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
295 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
296 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
297 AND ( ( Recinfo.NODE_TYPE = p_NODE_TYPE)
298 OR ( ( Recinfo.NODE_TYPE IS NULL )
299 AND ( p_NODE_TYPE IS NULL )))
300 AND ( ( Recinfo.NODE_NAME = p_NODE_NAME)
301 OR ( ( Recinfo.NODE_NAME IS NULL )
302 AND ( p_NODE_NAME IS NULL )))
303 AND ( ( Recinfo.TOP_NODE_ID = p_TOP_NODE_ID)
304 OR ( ( Recinfo.TOP_NODE_ID IS NULL )
305 AND ( p_TOP_NODE_ID IS NULL )))
306 AND ( ( Recinfo.PARENT_NODE_ID = p_PARENT_NODE_ID)
307 OR ( ( Recinfo.PARENT_NODE_ID IS NULL )
308 AND ( p_PARENT_NODE_ID IS NULL )))
309 AND ( ( Recinfo.ORGANIZATION_ID = p_ORGANIZATION_ID)
310 OR ( ( Recinfo.ORGANIZATION_ID IS NULL )
311 AND ( p_ORGANIZATION_ID IS NULL )))
312 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY)
313 OR ( ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
314 AND ( p_ATTRIBUTE_CATEGORY IS NULL )))
315 AND ( ( Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1)
316 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
317 AND ( p_ATTRIBUTE1 IS NULL )))
318 AND ( ( Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2)
319 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
320 AND ( p_ATTRIBUTE2 IS NULL )))
321 AND ( ( Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3)
322 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
323 AND ( p_ATTRIBUTE3 IS NULL )))
324 AND ( ( Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4)
325 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
326 AND ( p_ATTRIBUTE4 IS NULL )))
327 AND ( ( Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5)
328 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
329 AND ( p_ATTRIBUTE5 IS NULL )))
330 AND ( ( Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6)
331 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
332 AND ( p_ATTRIBUTE6 IS NULL )))
333 AND ( ( Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7)
334 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
335 AND ( p_ATTRIBUTE7 IS NULL )))
336 AND ( ( Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8)
337 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
338 AND ( p_ATTRIBUTE8 IS NULL )))
339 AND ( ( Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
340 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
341 AND ( p_ATTRIBUTE9 IS NULL )))
342 AND ( ( Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10)
343 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
344 AND ( p_ATTRIBUTE10 IS NULL )))
345 AND ( ( Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11)
346 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
347 AND ( p_ATTRIBUTE11 IS NULL )))
348 AND ( ( Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12)
349 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
350 AND ( p_ATTRIBUTE12 IS NULL )))
351 AND ( ( Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13)
355 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
352 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
353 AND ( p_ATTRIBUTE13 IS NULL )))
354 AND ( ( Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14)
356 AND ( p_ATTRIBUTE14 IS NULL )))
357
358 AND ( ( Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15)
359 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
360 AND ( p_ATTRIBUTE15 IS NULL )))
361 ) then
362 return;
363 else
364 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
365 APP_EXCEPTION.RAISE_EXCEPTION;
366 End If;
367 END Lock_Row;
368
369 End CSP_REP_HIERARCHIES_PKG;