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