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