[Home] [Help]
PACKAGE BODY: APPS.WMS_DB_OBJ_PARENTS_PKG
Source
1 PACKAGE BODY WMS_DB_OBJ_PARENTS_PKG AS
2 /*$Header: WMSDBOPB.pls 120.1 2005/06/20 06:36:44 appldev ship $*/
3 --
4 PROCEDURE INSERT_ROW (
5 x_rowid IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6 ,x_db_object_id IN NUMBER
7 ,x_type_code IN NUMBER
8 ,x_parent_db_object_id IN NUMBER
9 ,x_last_updated_by IN NUMBER
10 ,x_last_update_date IN DATE
11 ,x_created_by IN NUMBER
12 ,x_creation_date IN DATE
13 ,x_last_update_login IN NUMBER
14 ,x_attribute_category IN VARCHAR2
15 ,x_attribute1 IN VARCHAR2
16 ,x_attribute2 IN VARCHAR2
17 ,x_attribute3 IN VARCHAR2
18 ,x_attribute4 IN VARCHAR2
19 ,x_attribute5 IN VARCHAR2
20 ,x_attribute6 IN VARCHAR2
21 ,x_attribute7 IN VARCHAR2
22 ,x_attribute8 IN VARCHAR2
23 ,x_attribute9 IN VARCHAR2
24 ,x_attribute10 IN VARCHAR2
25 ,x_attribute11 IN VARCHAR2
26 ,x_attribute12 IN VARCHAR2
27 ,x_attribute13 IN VARCHAR2
28 ,x_attribute14 IN VARCHAR2
29 ,x_attribute15 IN VARCHAR2
30 )IS
31 CURSOR C IS SELECT ROWID FROM WMS_DB_OBJECTS_PARENTS
32 WHERE db_object_id = x_db_object_id
33 AND type_code = x_type_code;
34 BEGIN
35
36 INSERT INTO WMS_DB_OBJECTS_PARENTS (
37 db_object_id
38 ,type_code
39 ,parent_db_object_id
40 ,last_updated_by
41 ,last_update_date
42 ,created_by
43 ,creation_date
44 ,last_update_login
45 ,attribute_category
46 ,attribute1
47 ,attribute2
48 ,attribute3
49 ,attribute4
50 ,attribute5
51 ,attribute6
52 ,attribute7
53 ,attribute8
54 ,attribute9
55 ,attribute10
56 ,attribute11
57 ,attribute12
58 ,attribute13
59 ,attribute14
60 ,attribute15
61 ) values (
62 x_db_object_id
63 ,x_type_code
64 ,x_parent_db_object_id
65 ,x_last_updated_by
66 ,x_last_update_date
67 ,x_created_by
68 ,x_creation_date
69 ,x_last_update_login
70 ,x_attribute_category
71 ,x_attribute1
72 ,x_attribute2
73 ,x_attribute3
74 ,x_attribute4
75 ,x_attribute5
76 ,x_attribute6
77 ,x_attribute7
78 ,x_attribute8
79 ,x_attribute9
80 ,x_attribute10
81 ,x_attribute11
82 ,x_attribute12
83 ,x_attribute13
84 ,x_attribute14
85 ,x_attribute15
86 );
87
88 OPEN C;
89 FETCH C INTO x_rowid;
90 IF (C%NOTFOUND) THEN
91 CLOSE C;
92 RAISE NO_DATA_FOUND;
93 END IF;
94 CLOSE C;
95 END INSERT_ROW;
96 --
97 PROCEDURE LOCK_ROW (
98 x_rowid IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
99 ,x_db_object_id IN NUMBER
100 ,x_type_code IN NUMBER
101 ,x_parent_db_object_id IN NUMBER
102 ,x_attribute_category IN VARCHAR2
103 ,x_attribute1 IN VARCHAR2
104 ,x_attribute2 IN VARCHAR2
105 ,x_attribute3 IN VARCHAR2
106 ,x_attribute4 IN VARCHAR2
107 ,x_attribute5 IN VARCHAR2
108 ,x_attribute6 IN VARCHAR2
109 ,x_attribute7 IN VARCHAR2
110 ,x_attribute8 IN VARCHAR2
111 ,x_attribute9 IN VARCHAR2
112 ,x_attribute10 IN VARCHAR2
113 ,x_attribute11 IN VARCHAR2
114 ,x_attribute12 IN VARCHAR2
115 ,x_attribute13 IN VARCHAR2
116 ,x_attribute14 IN VARCHAR2
117 ,x_attribute15 IN VARCHAR2
118 )IS
119 CURSOR C IS SELECT
120 db_object_id
121 ,type_code
122 ,parent_db_object_id
123 ,attribute_category
124 ,attribute1
125 ,attribute2
126 ,attribute3
127 ,attribute4
128 ,attribute5
129 ,attribute6
130 ,attribute7
131 ,attribute8
132 ,attribute9
133 ,attribute10
134 ,attribute11
135 ,attribute12
136 ,attribute13
137 ,attribute14
138 ,attribute15
139 FROM WMS_DB_OBJECTS_PARENTS
140 WHERE rowid = x_rowid
141 FOR UPDATE OF db_object_id, type_code NOWAIT;
142
143 recinfo c%ROWTYPE;
144 BEGIN
145 OPEN c;
146 FETCH c INTO recinfo;
147 IF (c%notfound) THEN
148 CLOSE c;
149 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
150 app_exception.raise_exception;
151 END IF;
152 CLOSE c;
153 IF ( (recinfo.db_object_id = x_db_object_id)
154 AND (recinfo.type_code = x_type_code)
155 AND (recinfo.parent_db_object_id = x_parent_db_object_id)
156 AND ((recinfo.attribute_category = x_attribute_category)
157 OR ((recinfo.attribute_category IS NULL)
158 AND (x_attribute_category IS NULL)))
159 AND ((recinfo.attribute1 = x_attribute1)
160 OR ((recinfo.attribute1 IS NULL)
161 AND (x_attribute1 IS NULL)))
162 AND ((recinfo.attribute2 = x_attribute2)
163 OR ((recinfo.attribute2 IS NULL)
164 AND (x_attribute2 IS NULL)))
165 AND ((recinfo.attribute3 = x_attribute3)
166 OR ((recinfo.attribute3 IS NULL)
167 AND (x_attribute3 IS NULL)))
168 AND ((recinfo.attribute4 = x_attribute4)
169 OR ((recinfo.attribute4 IS NULL)
170 AND (x_attribute4 IS NULL)))
171 AND ((recinfo.attribute5 = x_attribute5)
172 OR ((recinfo.attribute5 IS NULL)
173 AND (x_attribute5 IS NULL)))
174 AND ((recinfo.attribute6 = x_attribute6)
175 OR ((recinfo.attribute6 IS NULL)
176 AND (x_attribute6 IS NULL)))
177 AND ((recinfo.attribute7 = x_attribute7)
178 OR ((recinfo.attribute7 IS NULL)
179 AND (x_attribute7 IS NULL)))
180 AND ((recinfo.attribute8 = x_attribute8)
181 OR ((recinfo.attribute8 IS NULL)
182 AND (x_attribute8 IS NULL)))
183 AND ((recinfo.attribute9 = x_attribute9)
184 OR ((recinfo.attribute9 IS NULL)
185 AND (x_attribute9 IS NULL)))
186 AND ((recinfo.attribute10 = x_attribute10)
187 OR ((recinfo.attribute10 IS NULL)
188 AND (x_attribute10 IS NULL)))
189 AND ((recinfo.attribute11 = x_attribute11)
190 OR ((recinfo.attribute11 IS NULL)
191 AND (x_attribute11 IS NULL)))
192 AND ((recinfo.attribute12 = x_attribute12)
193 OR ((recinfo.attribute12 IS NULL)
194 AND (x_attribute12 IS NULL)))
195 AND ((recinfo.attribute13 = x_attribute13)
196 OR ((recinfo.attribute13 IS NULL)
197 AND (x_attribute13 IS NULL)))
198 AND ((recinfo.attribute14 = x_attribute14)
199 OR ((recinfo.attribute14 IS NULL)
200 AND (x_attribute14 IS NULL)))
201 AND ((recinfo.attribute15 = x_attribute15)
202 OR ((recinfo.attribute15 IS NULL)
203 AND (x_attribute15 IS NULL)))
204 ) THEN
205 NULL;
206 ELSE
207 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
208 app_exception.raise_exception;
209 END IF;
210 END LOCK_ROW;
211 --
212 PROCEDURE UPDATE_ROW (
213 x_db_object_id IN NUMBER
214 ,x_type_code IN NUMBER
215 ,x_parent_db_object_id IN NUMBER
216 ,x_last_updated_by IN NUMBER
217 ,x_last_update_date IN DATE
218 ,x_last_update_login IN NUMBER
219 ,x_attribute_category IN VARCHAR2
220 ,x_attribute1 IN VARCHAR2
221 ,x_attribute2 IN VARCHAR2
222 ,x_attribute3 IN VARCHAR2
223 ,x_attribute4 IN VARCHAR2
224 ,x_attribute5 IN VARCHAR2
225 ,x_attribute6 IN VARCHAR2
226 ,x_attribute7 IN VARCHAR2
227 ,x_attribute8 IN VARCHAR2
228 ,x_attribute9 IN VARCHAR2
229 ,x_attribute10 IN VARCHAR2
230 ,x_attribute11 IN VARCHAR2
231 ,x_attribute12 IN VARCHAR2
232 ,x_attribute13 IN VARCHAR2
233 ,x_attribute14 IN VARCHAR2
234 ,x_attribute15 IN VARCHAR2
235 )IS
236
237 BEGIN
238 UPDATE WMS_DB_OBJECTS_PARENTS
239 SET last_updated_by = x_last_updated_by
240 ,last_update_date = x_last_update_date
241 ,last_update_login = x_last_update_login
242 ,parent_db_object_id = x_parent_db_object_id
243 ,attribute_category = x_attribute_category
244 ,attribute1 = x_attribute1
245 ,attribute2 = x_attribute2
246 ,attribute3 = x_attribute3
247 ,attribute4 = x_attribute4
248 ,attribute5 = x_attribute5
249 ,attribute6 = x_attribute6
250 ,attribute7 = x_attribute7
251 ,attribute8 = x_attribute8
252 ,attribute9 = x_attribute9
253 ,attribute10 = x_attribute10
254 ,attribute11 = x_attribute11
255 ,attribute12 = x_attribute12
256 ,attribute13 = x_attribute13
257 ,attribute14 = x_attribute14
258 ,attribute15 = x_attribute15
259 WHERE db_object_id = x_db_object_id
260 AND type_code = x_type_code;
261
262 IF (SQL%NOTFOUND) THEN
263 RAISE NO_DATA_FOUND;
264 END IF;
265 END UPDATE_ROW;--
266 PROCEDURE DELETE_ROW (
267 x_rowid IN VARCHAR2
268 )IS
269 BEGIN
270
271 DELETE FROM WMS_DB_OBJECTS_PARENTS
272 WHERE rowid = x_rowid;
273
274 IF (SQL%NOTFOUND) THEN
275 RAISE NO_DATA_FOUND;
276 END IF;
277 END DELETE_ROW;
278
279 PROCEDURE load_row
280 (
281 x_db_object_id IN NUMBER
282 ,x_type_code IN NUMBER
283 ,x_owner IN VARCHAR2
284 ,x_parent_db_object_id IN NUMBER
285 ,x_attribute_category IN VARCHAR2
286 ,x_attribute1 IN VARCHAR2
287 ,x_attribute2 IN VARCHAR2
288 ,x_attribute3 IN VARCHAR2
289 ,x_attribute4 IN VARCHAR2
290 ,x_attribute5 IN VARCHAR2
291 ,x_attribute6 IN VARCHAR2
292 ,x_attribute7 IN VARCHAR2
293 ,x_attribute8 IN VARCHAR2
294 ,x_attribute9 IN VARCHAR2
295 ,x_attribute10 IN VARCHAR2
296 ,x_attribute11 IN VARCHAR2
297 ,x_attribute12 IN VARCHAR2
298 ,x_attribute13 IN VARCHAR2
299 ,x_attribute14 IN VARCHAR2
300 ,x_attribute15 IN VARCHAR2
301 ) IS
302 BEGIN
303 DECLARE
304 l_db_object_id NUMBER;
305 l_type_code NUMBER;
306 l_parent_db_object_id NUMBER;
307 l_user_id NUMBER := 0;
308 l_row_id VARCHAR2(64);
309 l_sysdate DATE;
310 BEGIN
311 IF (x_owner = 'SEED') THEN
312 l_user_id := 1;
313 END IF;
314 --
315 SELECT Sysdate INTO l_sysdate FROM dual;
316 l_db_object_id := fnd_number.canonical_to_number(x_db_object_id);
317 l_type_code :=
318 fnd_number.canonical_to_number(x_type_code);
319 l_parent_db_object_id :=
320 fnd_number.canonical_to_number(x_parent_db_object_id);
321 wms_db_obj_parents_pkg.update_row
322 (
323 x_db_object_id => l_db_object_id
324 ,x_type_code => l_type_code
325 ,x_last_updated_by => l_user_id
326 ,x_last_update_date => l_sysdate
327 ,x_last_update_login => 0
328 ,x_parent_db_object_id => l_parent_db_object_id
329 ,x_attribute_category => x_attribute_category
330 ,x_attribute1 => x_attribute1
331 ,x_attribute2 => x_attribute2
332 ,x_attribute3 => x_attribute3
333 ,x_attribute4 => x_attribute4
334 ,x_attribute5 => x_attribute5
335 ,x_attribute6 => x_attribute6
336 ,x_attribute7 => x_attribute7
337 ,x_attribute8 => x_attribute8
338 ,x_attribute9 => x_attribute9
339 ,x_attribute10 => x_attribute10
340 ,x_attribute11 => x_attribute11
341 ,x_attribute12 => x_attribute12
342 ,x_attribute13 => x_attribute13
343 ,x_attribute14 => x_attribute14
344 ,x_attribute15 => x_attribute15
345 );
346 EXCEPTION
347 WHEN no_data_found THEN
348 wms_db_obj_parents_pkg.insert_row
349 (
350 x_rowid => l_row_id
351 ,x_db_object_id => l_db_object_id
352 ,x_type_code => l_type_code
353 ,x_last_updated_by => l_user_id
354 ,x_last_update_date => l_sysdate
355 ,x_created_by => l_user_id
356 ,x_creation_date => l_sysdate
357 ,x_last_update_login => 0
358 ,x_parent_db_object_id => l_parent_db_object_id
359 ,x_attribute_category => x_attribute_category
360 ,x_attribute1 => x_attribute1
361 ,x_attribute2 => x_attribute2
362 ,x_attribute3 => x_attribute3
363 ,x_attribute4 => x_attribute4
364 ,x_attribute5 => x_attribute5
365 ,x_attribute6 => x_attribute6
366 ,x_attribute7 => x_attribute7
367 ,x_attribute8 => x_attribute8
368 ,x_attribute9 => x_attribute9
369 ,x_attribute10 => x_attribute10
370 ,x_attribute11 => x_attribute11
371 ,x_attribute12 => x_attribute12
372 ,x_attribute13 => x_attribute13
373 ,x_attribute14 => x_attribute14
374 ,x_attribute15 => x_attribute15
375 );
376 END;
377 END load_row;
378 END WMS_DB_OBJ_PARENTS_PKG;