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