1 PACKAGE BODY PNT_LOCATION_FEATURES_PKG AS
2 -- $Header: PNTFEATB.pls 120.2 2005/12/01 08:37:45 appldev ship $
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : INSERT_ROW
6 -- INVOKED FROM : insert_row procedure
7 -- PURPOSE : inserts the row
8 -- HISTORY :
9 -- 13-JUL-05 hrodda o Bug 4284035 - Replaced PN_LOCATION_FEATURES with _ALL
10 -- table.
11 -------------------------------------------------------------------------------
12 PROCEDURE insert_row (
13 x_rowid IN OUT NOCOPY VARCHAR2
14 ,x_org_id IN NUMBER
15 ,x_LOCATION_FEATURE_ID IN OUT NOCOPY NUMBER
16 ,x_LAST_UPDATE_DATE DATE
17 ,x_LAST_UPDATED_BY NUMBER
18 ,x_CREATION_DATE DATE
19 ,x_CREATED_BY NUMBER
20 ,x_LAST_UPDATE_LOGIN NUMBER
21 ,x_LOCATION_ID NUMBER
22 ,x_LOCATION_FEATURE_LOOKUP_CODE VARCHAR2
23 ,x_DESCRIPTION VARCHAR2
24 ,x_QUANTITY NUMBER
25 ,x_FEATURE_SIZE NUMBER
26 ,x_UOM_CODE VARCHAR2
27 ,x_CONDITION_LOOKUP_CODE VARCHAR2
28 ,x_ATTRIBUTE_CATEGORY VARCHAR2
29 ,x_ATTRIBUTE1 VARCHAR2
30 ,x_ATTRIBUTE2 VARCHAR2
31 ,x_ATTRIBUTE3 VARCHAR2
32 ,x_ATTRIBUTE4 VARCHAR2
33 ,x_ATTRIBUTE5 VARCHAR2
34 ,x_ATTRIBUTE6 VARCHAR2
35 ,x_ATTRIBUTE7 VARCHAR2
36 ,x_ATTRIBUTE8 VARCHAR2
37 ,x_ATTRIBUTE9 VARCHAR2
38 ,x_ATTRIBUTE10 VARCHAR2
39 ,x_ATTRIBUTE11 VARCHAR2
40 ,x_ATTRIBUTE12 VARCHAR2
41 ,x_ATTRIBUTE13 VARCHAR2
42 ,x_ATTRIBUTE14 VARCHAR2
43 ,x_ATTRIBUTE15 VARCHAR2
44 )
45 IS
46 CURSOR C IS
47 SELECT rowid
48 FROM PN_LOCATION_FEATURES_ALL
49 WHERE LOCATION_FEATURE_ID = x_location_feature_id;
50
51 CURSOR org_cur IS
52 SELECT org_id FROM pn_locations_all WHERE location_id = x_LOCATION_ID AND ROWNUM = 1;
53
54 l_org_ID NUMBER;
55
56 BEGIN
57
58 PNP_DEBUG_PKG.debug ('PNT_LOCATION_FEATURES_PKG.insert_row (+)');
59
60 -----------------------------------------------------------------
61 -- Allocate the sequence to the primary key loction_id
62 -----------------------------------------------------------------
63 SELECT pn_location_features_s.nextval
64 INTO x_location_feature_id
65 FROM dual;
66
67 IF x_org_id IS NULL THEN
68 FOR rec IN org_cur LOOP
69 l_org_id := rec.org_id;
70 END LOOP;
71 ELSE
72 l_org_id := x_org_id;
73 END IF;
74
75 INSERT INTO PN_LOCATION_FEATURES_ALL
76 (
77 LOCATION_FEATURE_ID
78 ,org_id
79 ,LAST_UPDATE_DATE
80 ,LAST_UPDATED_BY
81 ,CREATION_DATE
82 ,CREATED_BY
83 ,LAST_UPDATE_LOGIN
84 ,LOCATION_ID
85 ,LOCATION_FEATURE_LOOKUP_CODE
86 ,DESCRIPTION
87 ,QUANTITY
88 ,FEATURE_SIZE
89 ,UOM_CODE
90 ,CONDITION_LOOKUP_CODE
91 ,ATTRIBUTE_CATEGORY
92 ,ATTRIBUTE1
93 ,ATTRIBUTE2
94 ,ATTRIBUTE3
95 ,ATTRIBUTE4
96 ,ATTRIBUTE5
97 ,ATTRIBUTE6
98 ,ATTRIBUTE7
99 ,ATTRIBUTE8
100 ,ATTRIBUTE9
101 ,ATTRIBUTE10
102 ,ATTRIBUTE11
103 ,ATTRIBUTE12
104 ,ATTRIBUTE13
105 ,ATTRIBUTE14
106 ,ATTRIBUTE15
107 )
108 VALUES
109 (
110 x_LOCATION_FEATURE_ID
111 ,l_org_id
112 ,x_LAST_UPDATE_DATE
113 ,x_LAST_UPDATED_BY
114 ,x_CREATION_DATE
115 ,x_CREATED_BY
116 ,x_LAST_UPDATE_LOGIN
117 ,x_LOCATION_ID
118 ,x_LOCATION_FEATURE_LOOKUP_CODE
119 ,x_DESCRIPTION
120 ,x_QUANTITY
121 ,x_FEATURE_SIZE
122 ,x_UOM_CODE
123 ,x_CONDITION_LOOKUP_CODE
124 ,x_ATTRIBUTE_CATEGORY
125 ,x_ATTRIBUTE1
126 ,x_ATTRIBUTE2
127 ,x_ATTRIBUTE3
128 ,x_ATTRIBUTE4
129 ,x_ATTRIBUTE5
130 ,x_ATTRIBUTE6
131 ,x_ATTRIBUTE7
132 ,x_ATTRIBUTE8
133 ,x_ATTRIBUTE9
134 ,x_ATTRIBUTE10
135 ,x_ATTRIBUTE11
136 ,x_ATTRIBUTE12
137 ,x_ATTRIBUTE13
138 ,x_ATTRIBUTE14
139 ,x_ATTRIBUTE15
140 );
141 OPEN C;
142 FETCH C INTO x_rowid;
143 IF (C%NOTFOUND) THEN
144 CLOSE C;
145 Raise NO_DATA_FOUND;
146 END IF;
147 CLOSE C;
148
149 PNP_DEBUG_PKG.debug ('PNT_LOCATION_FEATURES_PKG.insert_row (-)');
150
151 END insert_row;
152
153 -------------------------------------------------------------------------------
154 -- PROCDURE : UPDATE_ROW
155 -- INVOKED FROM : UPDATE_ROW procedure
156 -- PURPOSE : updates the row
157 -- HISTORY :
158 -- 13-JUL-05 hrodda o Bug 4284035 - Replaced PN_LOCATION_FEATURES with _ALL
159 -- table and changed the where clause
160 -------------------------------------------------------------------------------
161 PROCEDURE UPDATE_ROW (
162 x_rowid VARCHAR2
163 ,x_LOCATION_FEATURE_ID NUMBER
164 ,x_LAST_UPDATE_DATE DATE
165 ,x_LAST_UPDATED_BY NUMBER
166 ,x_CREATION_DATE DATE
167 ,x_CREATED_BY NUMBER
168 ,x_LAST_UPDATE_LOGIN NUMBER
169 ,x_LOCATION_ID NUMBER
170 ,x_LOCATION_FEATURE_LOOKUP_CODE VARCHAR2
171 ,x_DESCRIPTION VARCHAR2
172 ,x_QUANTITY NUMBER
173 ,x_FEATURE_SIZE NUMBER
174 ,x_UOM_CODE VARCHAR2
175 ,x_CONDITION_LOOKUP_CODE VARCHAR2
176 ,x_ATTRIBUTE_CATEGORY VARCHAR2
177 ,x_ATTRIBUTE1 VARCHAR2
178 ,x_ATTRIBUTE2 VARCHAR2
179 ,x_ATTRIBUTE3 VARCHAR2
180 ,x_ATTRIBUTE4 VARCHAR2
181 ,x_ATTRIBUTE5 VARCHAR2
182 ,x_ATTRIBUTE6 VARCHAR2
183 ,x_ATTRIBUTE7 VARCHAR2
184 ,x_ATTRIBUTE8 VARCHAR2
185 ,x_ATTRIBUTE9 VARCHAR2
186 ,x_ATTRIBUTE10 VARCHAR2
187 ,x_ATTRIBUTE11 VARCHAR2
188 ,x_ATTRIBUTE12 VARCHAR2
189 ,x_ATTRIBUTE13 VARCHAR2
190 ,x_ATTRIBUTE14 VARCHAR2
191 ,x_ATTRIBUTE15 VARCHAR2
192 ) IS
193 BEGIN
194
195 PNP_DEBUG_PKG.debug ('PNT_LOCATION_FEATURES_PKG.update_row (+)');
196 UPDATE PN_LOCATION_FEATURES_ALL
197 SET
198 LOCATION_FEATURE_ID = x_LOCATION_FEATURE_ID
199 ,LAST_UPDATE_DATE = x_LAST_UPDATE_DATE
200 ,LAST_UPDATED_BY = x_LAST_UPDATED_BY
201 ,CREATION_DATE = x_CREATION_DATE
202 ,CREATED_BY = x_CREATED_BY
203 ,LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN
204 ,LOCATION_ID = x_LOCATION_ID
205 ,LOCATION_FEATURE_LOOKUP_CODE= x_LOCATION_FEATURE_LOOKUP_CODE
206 ,DESCRIPTION = x_DESCRIPTION
207 ,QUANTITY = x_QUANTITY
208 ,FEATURE_SIZE = x_FEATURE_SIZE
209 ,UOM_CODE = x_UOM_CODE
210 ,CONDITION_LOOKUP_CODE = x_CONDITION_LOOKUP_CODE
211 ,ATTRIBUTE_CATEGORY = x_ATTRIBUTE_CATEGORY
212 ,ATTRIBUTE1 = x_ATTRIBUTE1
213 ,ATTRIBUTE2 = x_ATTRIBUTE2
214 ,ATTRIBUTE3 = x_ATTRIBUTE3
215 ,ATTRIBUTE4 = x_ATTRIBUTE4
216 ,ATTRIBUTE5 = x_ATTRIBUTE5
217 ,ATTRIBUTE6 = x_ATTRIBUTE6
218 ,ATTRIBUTE7 = x_ATTRIBUTE7
219 ,ATTRIBUTE8 = x_ATTRIBUTE8
220 ,ATTRIBUTE9 = x_ATTRIBUTE9
221 ,ATTRIBUTE10 = x_ATTRIBUTE10
222 ,ATTRIBUTE11 = x_ATTRIBUTE11
223 ,ATTRIBUTE12 = x_ATTRIBUTE12
224 ,ATTRIBUTE13 = x_ATTRIBUTE13
225 ,ATTRIBUTE14 = x_ATTRIBUTE14
226 ,ATTRIBUTE15 = x_ATTRIBUTE15
227 WHERE LOCATION_FEATURE_ID = x_LOCATION_FEATURE_ID;
228
229 IF (SQL%NOTFOUND) THEN
230 RAISE NO_DATA_FOUND;
231 END IF;
232
233 PNP_DEBUG_PKG.debug ('PNT_LOCATION_FEATURES_PKG.update_row (-)');
234 END UPDATE_ROW;
235
236 -------------------------------------------------------------------------------
237 -- PROCDURE : lock_row
238 -- INVOKED FROM : lock_row procedure
239 -- PURPOSE : locks the row
240 -- HISTORY :
241 -- 13-JUL-05 hrodda o Bug 4284035 - Replaced PN_LOCATION_FEATURES with _ALL
242 -- table and changed the where clause
243 -------------------------------------------------------------------------------
244 PROCEDURE lock_row ( x_rowid VARCHAR2
245 ,x_LOCATION_FEATURE_ID NUMBER
246 ,x_LOCATION_ID NUMBER
247 ,x_LOCATION_FEATURE_LOOKUP_CODE VARCHAR2
248 ,x_DESCRIPTION VARCHAR2
249 ,x_QUANTITY NUMBER
250 ,x_FEATURE_SIZE NUMBER
251 ,x_UOM_CODE VARCHAR2
252 ,x_CONDITION_LOOKUP_CODE VARCHAR2
253 ,x_ATTRIBUTE_CATEGORY VARCHAR2
254 ,x_ATTRIBUTE1 VARCHAR2
255 ,x_ATTRIBUTE2 VARCHAR2
256 ,x_ATTRIBUTE3 VARCHAR2
257 ,x_ATTRIBUTE4 VARCHAR2
258 ,x_ATTRIBUTE5 VARCHAR2
259 ,x_ATTRIBUTE6 VARCHAR2
260 ,x_ATTRIBUTE7 VARCHAR2
261 ,x_ATTRIBUTE8 VARCHAR2
262 ,x_ATTRIBUTE9 VARCHAR2
263 ,x_ATTRIBUTE10 VARCHAR2
264 ,x_ATTRIBUTE11 VARCHAR2
265 ,x_ATTRIBUTE12 VARCHAR2
266 ,x_ATTRIBUTE13 VARCHAR2
267 ,x_ATTRIBUTE14 VARCHAR2
268 ,x_ATTRIBUTE15 VARCHAR2
269 )
270 IS
271 CURSOR C IS
272 SELECT *
273 FROM PN_LOCATION_FEATURES_ALL
274 WHERE LOCATION_FEATURE_ID = x_LOCATION_FEATURE_ID
275 FOR UPDATE OF LOCATION_FEATURE_ID NOWAIT;
276
277 Recinfo C%ROWTYPE;
278
279 BEGIN
280
281 PNP_DEBUG_PKG.debug ('PNT_LOCATION_FEATURES_PKG.lock_row (+)');
282 OPEN C;
283 FETCH C INTO Recinfo;
284 IF (C%NOTFOUND) THEN
285 CLOSE C;
286 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
287 APP_EXCEPTION.Raise_Exception;
288 END IF;
289 CLOSE C;
290
291 IF NOT ((Recinfo.LOCATION_FEATURE_ID = X_LOCATION_FEATURE_ID)
292 OR ((Recinfo.LOCATION_FEATURE_ID is null) AND (X_LOCATION_FEATURE_ID is null))) THEN
293 pn_var_rent_pkg.lock_row_exception('LOCATION_FEATURE_ID',Recinfo.location_feature_id);
294 END IF;
295
296 IF NOT ((Recinfo.LOCATION_ID = X_LOCATION_ID)
297 OR ((Recinfo.LOCATION_ID is null) AND (X_LOCATION_ID is null))) THEN
298 pn_var_rent_pkg.lock_row_exception('LOCATION_ID',Recinfo.location_id);
299 END IF;
300
301 IF NOT ((Recinfo.LOCATION_FEATURE_LOOKUP_CODE = X_LOCATION_FEATURE_LOOKUP_CODE)
302 OR ((Recinfo.LOCATION_FEATURE_LOOKUP_CODE is null) AND (X_LOCATION_FEATURE_LOOKUP_CODE is null))) THEN
303 pn_var_rent_pkg.lock_row_exception('LOCATION_FEATURE_LOOKUP_CODE',Recinfo.LOCATION_FEATURE_LOOKUP_CODE);
304 END IF;
305
306 IF NOT ((Recinfo.DESCRIPTION = X_DESCRIPTION)
307 OR ((Recinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null))) THEN
308 pn_var_rent_pkg.lock_row_exception('DESCRIPTION',Recinfo.DESCRIPTION);
309 END IF;
310
311 IF NOT ((Recinfo.QUANTITY = X_QUANTITY)
312 OR ((Recinfo.QUANTITY is null) AND (X_QUANTITY is null))) THEN
313 pn_var_rent_pkg.lock_row_exception('QUANTITY',Recinfo.QUANTITY);
314 END IF;
315
316 IF NOT ((Recinfo.FEATURE_SIZE = X_FEATURE_SIZE)
317 OR ((Recinfo.FEATURE_SIZE is null) AND (X_FEATURE_SIZE is null))) THEN
318 pn_var_rent_pkg.lock_row_exception('FEATURE_SIZE',Recinfo.FEATURE_SIZE);
319 END IF;
320
321 IF NOT ((Recinfo.UOM_CODE = X_UOM_CODE)
322 OR ((Recinfo.UOM_CODE is null) AND (X_UOM_CODE is null))) THEN
323 pn_var_rent_pkg.lock_row_exception('UOM_CODE',Recinfo.UOM_CODE);
324 END IF;
325
326 IF NOT ((Recinfo.CONDITION_LOOKUP_CODE = X_CONDITION_LOOKUP_CODE)
327 OR ((Recinfo.CONDITION_LOOKUP_CODE is null) AND (X_CONDITION_LOOKUP_CODE is null))) THEN
328 pn_var_rent_pkg.lock_row_exception('X_CONDITION_LOOKUP_CODE',Recinfo.CONDITION_LOOKUP_CODE);
329 END IF;
330
331 IF NOT ((Recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
332 OR ((Recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))THEN
333 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE_CATEGORY', Recinfo.attribute_category);
334 END IF;
335
336 IF NOT ((Recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
337 OR ((Recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null))) THEN
338 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE1', Recinfo.attribute1);
339 END IF;
340
341 IF NOT ((Recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
342 OR ((Recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null))) THEN
343 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE2', Recinfo.attribute2);
344 END IF;
345
346 IF NOT ((Recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
347 OR ((Recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null))) THEN
348 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE3', Recinfo.attribute3);
349 END IF;
350
351 IF NOT ((Recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
352 OR ((Recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null))) THEN
353 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE4', Recinfo.attribute4);
354 END IF;
355
356 IF NOT ((Recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
357 OR ((Recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null))) THEN
358 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE5', Recinfo.attribute5);
359 END IF;
360
361 IF NOT ((Recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
362 OR ((Recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null))) THEN
363 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE6', Recinfo.attribute6);
364 END IF;
365
366 IF NOT ((Recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
367 OR ((Recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null))) THEN
368 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE7', Recinfo.attribute7);
369 END IF;
370
371 IF NOT ((Recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
372 OR ((Recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null))) THEN
373 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE8', Recinfo.attribute8);
374 END IF;
375
376 IF NOT ((Recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
377 OR ((Recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null))) THEN
378 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE9', Recinfo.attribute9);
379 END IF;
380
381 IF NOT ((Recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
382 OR ((Recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null))) THEN
383 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE10', Recinfo.attribute10);
384 END IF;
385
386 IF NOT ((Recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
387 OR ((Recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null))) THEN
388 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE11', Recinfo.attribute11);
389 END IF;
390
391 IF NOT ((Recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
392 OR ((Recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null))) THEN
393 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE12', Recinfo.attribute12);
394 END IF;
395
396 IF NOT ((Recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
397 OR ((Recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null))) THEN
398 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE13', Recinfo.attribute13);
399 END IF;
400
401 IF NOT ((Recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
402 OR ((Recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null))) THEN
403 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE14', Recinfo.attribute14);
404 END IF;
405
406 IF NOT ((Recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
407 OR ((Recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null))) THEN
408 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE15', Recinfo.attribute15);
409 END IF;
410
411 PNP_DEBUG_PKG.debug ('PNT_LOCATION_FEATURES_PKG.lock_row (-)');
412 END LOCK_ROW;
413
414 -------------------------------------------------------------------------------
415 -- PROCDURE : delete_row
416 -- INVOKED FROM : delete_row procedure
417 -- PURPOSE : deletes the row
418 -- HISTORY :
419 -- 13-JUL-05 hrodda o Bug 4284035 - Replaced PN_LOCATION_FEATURES with _ALL
420 -- table.
421 -------------------------------------------------------------------------------
422 PROCEDURE delete_row (
423 x_location_feature_id NUMBER
424 ) IS
425 BEGIN
426
427 PNP_DEBUG_PKG.debug ('PNT_LOCATION_FEATURES_PKG.delete_row (+)');
428 DELETE FROM PN_LOCATION_FEATURES_ALL
429 WHERE location_feature_id = x_location_feature_id;
430
431 IF (sql%notfound) THEN
432 RAISE NO_DATA_FOUND;
433 END IF;
434
435 PNP_DEBUG_PKG.debug ('PNT_LOCATION_FEATURES_PKG.delete_row (-)');
436 END delete_row;
437
438 END PNT_LOCATION_FEATURES_PKG ;
[Home] [Help]