1 PACKAGE BODY pn_location_parks_pkg AS
2 -- $Header: PNTRGOFB.pls 115.11 2002/11/12 23:10:48 stripath ship $
3
4 PROCEDURE Insert_Row (
5 x_rowid IN OUT NOCOPY VARCHAR2,
6 x_location_park_id NUMBER,
7 x_location_park_type VARCHAR2,
8 x_parent_location_park_id NUMBER,
9 x_name VARCHAR2,
10 x_description VARCHAR2,
11 x_creation_date DATE,
12 x_created_by NUMBER,
13 x_last_update_date DATE,
14 x_last_updated_by NUMBER,
15 x_last_update_login NUMBER,
16 x_attribute_category VARCHAR2,
17 x_attribute1 VARCHAR2,
18 x_attribute2 VARCHAR2,
19 x_attribute3 VARCHAR2,
20 x_attribute4 VARCHAR2,
21 x_attribute5 VARCHAR2,
22 x_attribute6 VARCHAR2,
23 x_attribute7 VARCHAR2,
24 x_attribute8 VARCHAR2,
25 x_attribute9 VARCHAR2,
26 x_attribute10 VARCHAR2,
27 x_attribute11 VARCHAR2,
28 x_attribute12 VARCHAR2,
29 x_attribute13 VARCHAR2,
30 x_attribute14 VARCHAR2,
31 x_attribute15 VARCHAR2
32 )
33 IS
34
35 CURSOR c IS
36 SELECT rowid
37 FROM pn_location_parks
38 WHERE location_park_id = x_location_park_id
39 AND language = userenv('LANG');
40
41 BEGIN
42
43 INSERT INTO pn_location_parks
44 (
45 location_park_id,
46 name,
47 location_park_type,
48 parent_location_park_id,
49 description,
50 last_update_date,
51 last_updated_by,
52 creation_date,
53 created_by,
54 last_update_login,
55 language,
56 source_lang,
57 attribute_category,
58 attribute1,
59 attribute2,
60 attribute3,
61 attribute4,
62 attribute5,
63 attribute6,
64 attribute7,
65 attribute8,
66 attribute9,
67 attribute10,
68 attribute11,
69 attribute12,
70 attribute13,
71 attribute14,
72 attribute15
73 )
74 SELECT x_location_park_id,
75 x_name,
76 x_location_park_type,
77 x_parent_location_park_id,
78 x_description,
79 x_last_update_date,
80 x_last_updated_by,
81 x_creation_date,
82 x_created_by,
83 x_last_update_login,
84 l.language_code,
85 userenv('LANG'),
86 x_attribute_category,
87 x_attribute1,
88 x_attribute2,
89 x_attribute3,
90 x_attribute4,
91 x_attribute5,
92 x_attribute6,
93 x_attribute7,
94 x_attribute8,
95 x_attribute9,
96 x_attribute10,
97 x_attribute11,
98 x_attribute12,
99 x_attribute13,
100 x_attribute14,
101 x_attribute15
102 FROM fnd_languages l
103 WHERE l.installed_flag IN ('I', 'B')
104 AND NOT EXISTS (SELECT NULL
105 FROM pn_location_parks t
106 WHERE t.location_park_id = x_location_park_id
107 AND t.language = l.language_code);
108
109 OPEN c;
110 FETCH c
111 INTO x_rowid;
112 IF c%NOTFOUND THEN
113 CLOSE c;
114 RAISE NO_DATA_FOUND;
115 END IF;
116 CLOSE c;
117
118 END Insert_Row;
119
120
121 PROCEDURE Lock_Row (
122 x_location_park_id IN NUMBER,
123 x_location_park_type IN VARCHAR2,
124 x_parent_location_park_id IN NUMBER,
125 x_name IN VARCHAR2,
126 x_description IN VARCHAR2,
127 x_attribute_category IN VARCHAR2,
128 x_attribute1 IN VARCHAR2,
129 x_attribute2 IN VARCHAR2,
130 x_attribute3 IN VARCHAR2,
131 x_attribute4 IN VARCHAR2,
132 x_attribute5 IN VARCHAR2,
133 x_attribute6 IN VARCHAR2,
134 x_attribute7 IN VARCHAR2,
135 x_attribute8 IN VARCHAR2,
136 x_attribute9 IN VARCHAR2,
137 x_attribute10 IN VARCHAR2,
138 x_attribute11 IN VARCHAR2,
139 x_attribute12 IN VARCHAR2,
140 x_attribute13 IN VARCHAR2,
141 x_attribute14 IN VARCHAR2,
142 x_attribute15 IN VARCHAR2
143 )
144 IS
145
146 CURSOR c1 IS
147 SELECT *
148 FROM pn_location_parks
149 WHERE location_park_id = x_location_park_id
150 AND language = userenv('LANG')
151 FOR UPDATE OF location_park_id NOWAIT;
152
153 tlinfo c1%ROWTYPE;
154
155 BEGIN
156
157 OPEN c1;
158 FETCH c1
159 INTO tlinfo;
160 IF (c1%NOTFOUND) THEN
161 CLOSE c1;
162 RETURN;
163 END IF;
164 CLOSE c1;
165
166 IF NOT (tlinfo.name = x_name)
167 THEN
168 pn_var_rent_pkg.lock_row_exception('NAME',tlinfo.name);
169 END IF;
170
171 IF NOT ((tlinfo.description = x_description)
172 OR ((tlinfo.description IS NULL) AND (x_description IS NULL)))
173 THEN
174 pn_var_rent_pkg.lock_row_exception('DESCRIPTION',tlinfo.description);
175 END IF;
176
177 IF NOT (tlinfo.location_park_type = x_location_park_type)
178 THEN
179 pn_var_rent_pkg.lock_row_exception('LOCATION_PARK_TYPE',tlinfo.location_park_type);
180 END IF;
181
182 IF NOT ((tlinfo.parent_location_park_id = x_parent_location_park_id)
183 OR ((tlinfo.parent_location_park_id IS NULL) AND (x_parent_location_park_id IS NULL)))
184 THEN
185 pn_var_rent_pkg.lock_row_exception('PARENT_LOCATION_PARK_ID',tlinfo.parent_location_park_id);
186 END IF;
187
188 IF NOT ((tlinfo.attribute_category = x_attribute_category)
189 OR ((tlinfo.attribute_category IS NULL) AND (x_attribute_category IS NULL)))
190 THEN
191 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE_CATEGORY',tlinfo.attribute_category);
192 END IF;
193
194 IF NOT ((tlinfo.attribute1 = x_attribute1)
195 OR ((tlinfo.attribute1 IS NULL) AND (x_attribute1 IS NULL)))
196 THEN
197 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE1',tlinfo.attribute1);
198 END IF;
199
200 IF NOT ((tlinfo.attribute2 = x_attribute2)
201 OR ((tlinfo.attribute2 IS NULL) AND (x_attribute2 IS NULL)))
202 THEN
203 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE2',tlinfo.attribute2);
204 END IF;
205
206 IF NOT ((tlinfo.attribute3 = x_attribute3)
207 OR ((tlinfo.attribute3 IS NULL) AND (x_attribute3 IS NULL)))
208 THEN
209 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE3',tlinfo.attribute3);
210 END IF;
211
212 IF NOT ((tlinfo.attribute4 = x_attribute4)
213 OR ((tlinfo.attribute4 IS NULL) AND (x_attribute4 IS NULL)))
214 THEN
215 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE4',tlinfo.attribute4);
216 END IF;
217
218 IF NOT ((tlinfo.attribute5 = x_attribute5)
219 OR ((tlinfo.attribute5 IS NULL) AND (x_attribute5 IS NULL)))
220 THEN
221 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE5',tlinfo.attribute5);
222 END IF;
223
224 IF NOT ((tlinfo.attribute6 = x_attribute6)
225 OR ((tlinfo.attribute6 IS NULL) AND (x_attribute6 IS NULL)))
226 THEN
227 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE6',tlinfo.attribute6);
231 OR ((tlinfo.attribute7 IS NULL) AND (x_attribute7 IS NULL)))
228 END IF;
229
230 IF NOT ((tlinfo.attribute7 = x_attribute7)
232 THEN
233 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE7',tlinfo.attribute7);
234 END IF;
235
236 IF NOT ((tlinfo.attribute8 = x_attribute8)
237 OR ((tlinfo.attribute8 IS NULL) AND (x_attribute8 IS NULL)))
238 THEN
239 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE8',tlinfo.attribute8);
240 END IF;
241
242 IF NOT ((tlinfo.attribute9 = x_attribute9)
243 OR ((tlinfo.attribute9 IS NULL) AND (x_attribute9 IS NULL)))
244 THEN
245 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE9',tlinfo.attribute9);
246 END IF;
247
248 IF NOT ((tlinfo.attribute10 = x_attribute10)
249 OR ((tlinfo.attribute10 IS NULL) AND (x_attribute10 IS NULL)))
250 THEN
251 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE10',tlinfo.attribute10);
252 END IF;
253
254 IF NOT ((tlinfo.attribute11 = x_attribute11)
255 OR ((tlinfo.attribute11 IS NULL) AND (x_attribute11 IS NULL)))
256 THEN
257 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE11',tlinfo.attribute11);
258 END IF;
259
260 IF NOT ((tlinfo.attribute12 = x_attribute12)
261 OR ((tlinfo.attribute12 IS NULL) AND (x_attribute12 IS NULL)))
262 THEN
263 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE12',tlinfo.attribute12);
264 END IF;
265
266 IF NOT ((tlinfo.attribute13 = x_attribute13)
267 OR ((tlinfo.attribute13 IS NULL) AND (x_attribute13 IS NULL)))
268 THEN
269 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE13',tlinfo.attribute13);
270 END IF;
271
272 IF NOT ((tlinfo.attribute14 = x_attribute14)
273 OR ((tlinfo.attribute14 IS NULL) AND (x_attribute14 IS NULL)))
274 THEN
275 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE14',tlinfo.attribute14);
276 END IF;
277
278 IF NOT ((tlinfo.attribute15 = x_attribute15)
279 OR ((tlinfo.attribute15 IS NULL) AND (x_attribute15 IS NULL)))
280 THEN
281 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE15',tlinfo.attribute15);
282 END IF;
283
284 RETURN;
285
286 END Lock_Row;
287
288
289 PROCEDURE Update_Row (
290 x_location_park_id IN NUMBER,
291 x_location_park_type IN VARCHAR2,
292 x_parent_location_park_id IN NUMBER,
293 x_name IN VARCHAR2,
294 x_description IN VARCHAR2,
295 x_last_update_date IN DATE,
296 x_last_updated_by IN NUMBER,
297 x_last_update_login IN NUMBER,
298 x_attribute_category IN VARCHAR2,
299 x_attribute1 IN VARCHAR2,
300 x_attribute2 IN VARCHAR2,
301 x_attribute3 IN VARCHAR2,
302 x_attribute4 IN VARCHAR2,
303 x_attribute5 IN VARCHAR2,
304 x_attribute6 IN VARCHAR2,
305 x_attribute7 IN VARCHAR2,
306 x_attribute8 IN VARCHAR2,
307 x_attribute9 IN VARCHAR2,
308 x_attribute10 IN VARCHAR2,
309 x_attribute11 IN VARCHAR2,
310 x_attribute12 IN VARCHAR2,
311 x_attribute13 IN VARCHAR2,
312 x_attribute14 IN VARCHAR2,
313 x_attribute15 IN VARCHAR2
314 )
315 IS
316 BEGIN
317 UPDATE pn_location_parks
318 SET location_park_type = x_location_park_type,
319 parent_location_park_id = x_parent_location_park_id,
320 name = x_name,
321 description = x_description,
322 last_update_date = x_last_update_date,
323 last_updated_by = x_last_updated_by,
324 last_update_login = x_last_update_login,
325 source_lang = USERENV('lang'),
326 attribute_category = x_attribute_category,
327 attribute1 = x_attribute1,
328 attribute2 = x_attribute2,
329 attribute3 = x_attribute3,
330 attribute4 = x_attribute4,
331 attribute5 = x_attribute5,
332 attribute6 = x_attribute6,
333 attribute7 = x_attribute7,
334 attribute8 = x_attribute8,
335 attribute9 = x_attribute9,
336 attribute10 = x_attribute10,
337 attribute11 = x_attribute11,
338 attribute12 = x_attribute12,
339 attribute13 = x_attribute13,
340 attribute14 = x_attribute14,
341 attribute15 = x_attribute15
342 WHERE location_park_id = x_location_park_id
343 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
344
345 IF SQL%NOTFOUND THEN
346 RAISE NO_DATA_FOUND;
347 END IF;
348
349 END Update_Row;
350
351
352 PROCEDURE Delete_Row (
353 x_location_park_id IN NUMBER
354 )
355 IS
356 BEGIN
357 DELETE FROM pn_location_parks
361 RAISE NO_DATA_FOUND;
358 WHERE location_park_id = x_location_park_id;
359
360 IF (SQL%NOTFOUND) THEN
362 END IF;
363
364 END Delete_Row;
365
366
367 PROCEDURE Add_Language
368 IS
369 BEGIN
370 UPDATE pn_location_parks t
371 SET (
372 name,
373 description
374 ) =
375 (
376 SELECT b.name,
377 b.description
378 FROM pn_location_parks b
379 WHERE b.location_park_id = t.location_park_id
380 AND b.language = t.source_lang
381 )
382 WHERE (t.location_park_id,
383 t.language
384 ) IN
385 (SELECT subt.location_park_id,
386 subt.language
387 FROM pn_location_parks subb, pn_location_parks subt
388 WHERE subb.location_park_id = subt.location_park_id
389 AND subb.language = subt.source_lang
390 AND (subb.name <> subt.name
391 OR subb.description <> subt.description
392 OR (subb.description IS NULL AND subt.description IS NOT NULL)
393 OR (subb.description IS NOT NULL AND subt.description IS NULL)
394 )
395 );
396
397
398 INSERT INTO pn_location_parks
399 (
400 location_park_id,
401 name,
402 location_park_type,
403 parent_location_park_id,
404 description,
405 last_update_date,
406 last_updated_by,
407 creation_date,
408 created_by,
409 last_update_login,
410 language,
411 source_lang,
412 attribute_category,
413 attribute1,
414 attribute2,
415 attribute3,
416 attribute4,
417 attribute5,
418 attribute6,
419 attribute7,
420 attribute8,
421 attribute9,
422 attribute10,
423 attribute11,
424 attribute12,
425 attribute13,
426 attribute14,
427 attribute15
428 )
429 SELECT
430 b.location_park_id,
431 b.name,
432 b.location_park_type,
433 b.parent_location_park_id,
434 b.description,
435 b.last_update_date,
436 b.last_updated_by,
437 b.creation_date,
438 b.created_by,
439 b.last_update_login,
440 l.language_code,
441 b.source_lang ,
442 b.attribute_category,
443 b.attribute1,
444 b.attribute2,
445 b.attribute3,
446 b.attribute4,
447 b.attribute5,
448 b.attribute6,
449 b.attribute7,
450 b.attribute8,
451 b.attribute9,
452 b.attribute10,
453 b.attribute11,
454 b.attribute12,
455 b.attribute13,
456 b.attribute14,
457 b.attribute15
458 FROM pn_location_parks b, fnd_languages l
459 WHERE l.installed_flag IN ('I', 'B')
460 AND b.language = userenv('LANG')
461 AND NOT EXISTS (SELECT null
462 FROM pn_location_parks t
463 WHERE t.location_park_id = b.location_park_id
464 AND t.language = l.language_code);
465
466 END Add_Language;
467
468 END pn_location_parks_pkg;
[Home] [Help]