1 package body GMD_STORAGE_PLANS_PKG as
2 /* $Header: GMDVSTRB.pls 115.0 2003/04/05 04:58:09 magupta noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_STORAGE_PLAN_ID in NUMBER,
6 X_STORAGE_PLAN_NAME in VARCHAR2,
7 X_BASE_TEST_INTERVAL_PLAN_ID in NUMBER,
8 X_TEXT_CODE in NUMBER,
9 X_ATTRIBUTE_CATEGORY in VARCHAR2,
10 X_ATTRIBUTE1 in VARCHAR2,
11 X_ATTRIBUTE2 in VARCHAR2,
12 X_ATTRIBUTE3 in VARCHAR2,
13 X_ATTRIBUTE4 in VARCHAR2,
14 X_ATTRIBUTE5 in VARCHAR2,
15 X_ATTRIBUTE6 in VARCHAR2,
16 X_ATTRIBUTE7 in VARCHAR2,
17 X_ATTRIBUTE8 in VARCHAR2,
18 X_ATTRIBUTE9 in VARCHAR2,
19 X_ATTRIBUTE10 in VARCHAR2,
20 X_ATTRIBUTE11 in VARCHAR2,
21 X_ATTRIBUTE12 in VARCHAR2,
22 X_ATTRIBUTE13 in VARCHAR2,
23 X_ATTRIBUTE14 in VARCHAR2,
24 X_ATTRIBUTE15 in VARCHAR2,
25 X_ATTRIBUTE16 in VARCHAR2,
26 X_ATTRIBUTE17 in VARCHAR2,
27 X_ATTRIBUTE18 in VARCHAR2,
28 X_ATTRIBUTE19 in VARCHAR2,
29 X_ATTRIBUTE20 in VARCHAR2,
30 X_ATTRIBUTE21 in VARCHAR2,
31 X_ATTRIBUTE22 in VARCHAR2,
32 X_ATTRIBUTE23 in VARCHAR2,
33 X_ATTRIBUTE24 in VARCHAR2,
34 X_ATTRIBUTE25 in VARCHAR2,
35 X_ATTRIBUTE26 in VARCHAR2,
36 X_ATTRIBUTE27 in VARCHAR2,
37 X_ATTRIBUTE28 in VARCHAR2,
38 X_ATTRIBUTE29 in VARCHAR2,
39 X_ATTRIBUTE30 in VARCHAR2,
40 X_DELETE_MARK in NUMBER,
41 X_DESCRIPTION in VARCHAR2,
42 X_CREATION_DATE in DATE,
43 X_CREATED_BY in NUMBER,
44 X_LAST_UPDATE_DATE in DATE,
45 X_LAST_UPDATED_BY in NUMBER,
46 X_LAST_UPDATE_LOGIN in NUMBER
47 ) is
48 cursor C is select ROWID from GMD_STORAGE_PLANS_B
49 where STORAGE_PLAN_ID = X_STORAGE_PLAN_ID
50 ;
51 begin
52 insert into GMD_STORAGE_PLANS_B (
53 STORAGE_PLAN_ID,
54 STORAGE_PLAN_NAME,
55 BASE_TEST_INTERVAL_PLAN_ID,
56 TEXT_CODE,
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 ATTRIBUTE16,
74 ATTRIBUTE17,
75 ATTRIBUTE18,
76 ATTRIBUTE19,
77 ATTRIBUTE20,
78 ATTRIBUTE21,
79 ATTRIBUTE22,
80 ATTRIBUTE23,
81 ATTRIBUTE24,
82 ATTRIBUTE25,
83 ATTRIBUTE26,
84 ATTRIBUTE27,
85 ATTRIBUTE28,
86 ATTRIBUTE29,
87 ATTRIBUTE30,
88 DELETE_MARK,
89 CREATION_DATE,
90 CREATED_BY,
91 LAST_UPDATE_DATE,
92 LAST_UPDATED_BY,
93 LAST_UPDATE_LOGIN
94 ) values (
95 X_STORAGE_PLAN_ID,
96 X_STORAGE_PLAN_NAME,
97 X_BASE_TEST_INTERVAL_PLAN_ID,
98 X_TEXT_CODE,
99 X_ATTRIBUTE_CATEGORY,
100 X_ATTRIBUTE1,
101 X_ATTRIBUTE2,
102 X_ATTRIBUTE3,
103 X_ATTRIBUTE4,
104 X_ATTRIBUTE5,
105 X_ATTRIBUTE6,
106 X_ATTRIBUTE7,
107 X_ATTRIBUTE8,
108 X_ATTRIBUTE9,
109 X_ATTRIBUTE10,
110 X_ATTRIBUTE11,
111 X_ATTRIBUTE12,
112 X_ATTRIBUTE13,
113 X_ATTRIBUTE14,
114 X_ATTRIBUTE15,
115 X_ATTRIBUTE16,
116 X_ATTRIBUTE17,
117 X_ATTRIBUTE18,
118 X_ATTRIBUTE19,
119 X_ATTRIBUTE20,
120 X_ATTRIBUTE21,
121 X_ATTRIBUTE22,
122 X_ATTRIBUTE23,
123 X_ATTRIBUTE24,
124 X_ATTRIBUTE25,
125 X_ATTRIBUTE26,
126 X_ATTRIBUTE27,
127 X_ATTRIBUTE28,
128 X_ATTRIBUTE29,
129 X_ATTRIBUTE30,
130 X_DELETE_MARK,
131 X_CREATION_DATE,
132 X_CREATED_BY,
133 X_LAST_UPDATE_DATE,
134 X_LAST_UPDATED_BY,
135 X_LAST_UPDATE_LOGIN
136 );
137
138 insert into GMD_STORAGE_PLANS_TL (
139 STORAGE_PLAN_ID,
140 DESCRIPTION,
141 CREATION_DATE,
142 CREATED_BY,
143 LAST_UPDATED_BY,
144 LAST_UPDATE_DATE,
145 LAST_UPDATE_LOGIN,
146 LANGUAGE,
147 SOURCE_LANG
148 ) select
149 X_STORAGE_PLAN_ID,
150 X_DESCRIPTION,
151 X_CREATION_DATE,
152 X_CREATED_BY,
153 X_LAST_UPDATED_BY,
154 X_LAST_UPDATE_DATE,
155 X_LAST_UPDATE_LOGIN,
156 L.LANGUAGE_CODE,
157 userenv('LANG')
158 from FND_LANGUAGES L
159 where L.INSTALLED_FLAG in ('I', 'B')
160 and not exists
161 (select NULL
162 from GMD_STORAGE_PLANS_TL T
163 where T.STORAGE_PLAN_ID = X_STORAGE_PLAN_ID
164 and T.LANGUAGE = L.LANGUAGE_CODE);
165
166 open c;
167 fetch c into X_ROWID;
168 if (c%notfound) then
169 close c;
170 raise no_data_found;
171 end if;
172 close c;
173
174 end INSERT_ROW;
175
176 procedure LOCK_ROW (
177 X_STORAGE_PLAN_ID in NUMBER,
178 X_STORAGE_PLAN_NAME in VARCHAR2,
179 X_BASE_TEST_INTERVAL_PLAN_ID in NUMBER,
180 X_TEXT_CODE in NUMBER,
181 X_ATTRIBUTE_CATEGORY in VARCHAR2,
182 X_ATTRIBUTE1 in VARCHAR2,
183 X_ATTRIBUTE2 in VARCHAR2,
184 X_ATTRIBUTE3 in VARCHAR2,
185 X_ATTRIBUTE4 in VARCHAR2,
186 X_ATTRIBUTE5 in VARCHAR2,
187 X_ATTRIBUTE6 in VARCHAR2,
188 X_ATTRIBUTE7 in VARCHAR2,
189 X_ATTRIBUTE8 in VARCHAR2,
190 X_ATTRIBUTE9 in VARCHAR2,
191 X_ATTRIBUTE10 in VARCHAR2,
192 X_ATTRIBUTE11 in VARCHAR2,
193 X_ATTRIBUTE12 in VARCHAR2,
194 X_ATTRIBUTE13 in VARCHAR2,
195 X_ATTRIBUTE14 in VARCHAR2,
196 X_ATTRIBUTE15 in VARCHAR2,
197 X_ATTRIBUTE16 in VARCHAR2,
198 X_ATTRIBUTE17 in VARCHAR2,
199 X_ATTRIBUTE18 in VARCHAR2,
200 X_ATTRIBUTE19 in VARCHAR2,
201 X_ATTRIBUTE20 in VARCHAR2,
202 X_ATTRIBUTE21 in VARCHAR2,
203 X_ATTRIBUTE22 in VARCHAR2,
204 X_ATTRIBUTE23 in VARCHAR2,
205 X_ATTRIBUTE24 in VARCHAR2,
206 X_ATTRIBUTE25 in VARCHAR2,
207 X_ATTRIBUTE26 in VARCHAR2,
208 X_ATTRIBUTE27 in VARCHAR2,
209 X_ATTRIBUTE28 in VARCHAR2,
210 X_ATTRIBUTE29 in VARCHAR2,
211 X_ATTRIBUTE30 in VARCHAR2,
212 X_DELETE_MARK in NUMBER,
213 X_DESCRIPTION in VARCHAR2
214 ) is
215 cursor c is select
216 STORAGE_PLAN_NAME,
217 BASE_TEST_INTERVAL_PLAN_ID,
218 TEXT_CODE,
219 ATTRIBUTE_CATEGORY,
220 ATTRIBUTE1,
221 ATTRIBUTE2,
222 ATTRIBUTE3,
223 ATTRIBUTE4,
224 ATTRIBUTE5,
225 ATTRIBUTE6,
226 ATTRIBUTE7,
227 ATTRIBUTE8,
228 ATTRIBUTE9,
229 ATTRIBUTE10,
230 ATTRIBUTE11,
231 ATTRIBUTE12,
232 ATTRIBUTE13,
233 ATTRIBUTE14,
234 ATTRIBUTE15,
235 ATTRIBUTE16,
236 ATTRIBUTE17,
237 ATTRIBUTE18,
238 ATTRIBUTE19,
239 ATTRIBUTE20,
240 ATTRIBUTE21,
241 ATTRIBUTE22,
242 ATTRIBUTE23,
243 ATTRIBUTE24,
244 ATTRIBUTE25,
245 ATTRIBUTE26,
246 ATTRIBUTE27,
247 ATTRIBUTE28,
248 ATTRIBUTE29,
249 ATTRIBUTE30,
250 DELETE_MARK
251 from GMD_STORAGE_PLANS_B
252 where STORAGE_PLAN_ID = X_STORAGE_PLAN_ID
253 for update of STORAGE_PLAN_ID nowait;
254 recinfo c%rowtype;
255
256 cursor c1 is select
257 DESCRIPTION,
258 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
259 from GMD_STORAGE_PLANS_TL
260 where STORAGE_PLAN_ID = X_STORAGE_PLAN_ID
261 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
262 for update of STORAGE_PLAN_ID nowait;
263 begin
264 open c;
265 fetch c into recinfo;
266 if (c%notfound) then
267 close c;
268 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
269 app_exception.raise_exception;
270 end if;
271 close c;
272 if ( (recinfo.STORAGE_PLAN_NAME = X_STORAGE_PLAN_NAME)
273 AND (recinfo.BASE_TEST_INTERVAL_PLAN_ID = X_BASE_TEST_INTERVAL_PLAN_ID)
274 AND ((recinfo.TEXT_CODE = X_TEXT_CODE)
275 OR ((recinfo.TEXT_CODE is null) AND (X_TEXT_CODE is null)))
276 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
277 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
278 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
279 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
280 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
281 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
282 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
283 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
284 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
285 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
286 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
287 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
288 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
289 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
290 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
291 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
292 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
293 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
294 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
295 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
296 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
297 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
298 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
299 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
300 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
301 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
302 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
303 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
304 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
305 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
306 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
307 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
308 AND ((recinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
309 OR ((recinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
310 AND ((recinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
311 OR ((recinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
312 AND ((recinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
313 OR ((recinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
314 AND ((recinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
315 OR ((recinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
316 AND ((recinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
317 OR ((recinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
318 AND ((recinfo.ATTRIBUTE21 = X_ATTRIBUTE21)
319 OR ((recinfo.ATTRIBUTE21 is null) AND (X_ATTRIBUTE21 is null)))
320 AND ((recinfo.ATTRIBUTE22 = X_ATTRIBUTE22)
321 OR ((recinfo.ATTRIBUTE22 is null) AND (X_ATTRIBUTE22 is null)))
322 AND ((recinfo.ATTRIBUTE23 = X_ATTRIBUTE23)
323 OR ((recinfo.ATTRIBUTE23 is null) AND (X_ATTRIBUTE23 is null)))
324 AND ((recinfo.ATTRIBUTE24 = X_ATTRIBUTE24)
325 OR ((recinfo.ATTRIBUTE24 is null) AND (X_ATTRIBUTE24 is null)))
326 AND ((recinfo.ATTRIBUTE25 = X_ATTRIBUTE25)
327 OR ((recinfo.ATTRIBUTE25 is null) AND (X_ATTRIBUTE25 is null)))
328 AND ((recinfo.ATTRIBUTE26 = X_ATTRIBUTE26)
329 OR ((recinfo.ATTRIBUTE26 is null) AND (X_ATTRIBUTE26 is null)))
333 OR ((recinfo.ATTRIBUTE28 is null) AND (X_ATTRIBUTE28 is null)))
330 AND ((recinfo.ATTRIBUTE27 = X_ATTRIBUTE27)
331 OR ((recinfo.ATTRIBUTE27 is null) AND (X_ATTRIBUTE27 is null)))
332 AND ((recinfo.ATTRIBUTE28 = X_ATTRIBUTE28)
334 AND ((recinfo.ATTRIBUTE29 = X_ATTRIBUTE29)
335 OR ((recinfo.ATTRIBUTE29 is null) AND (X_ATTRIBUTE29 is null)))
336 AND ((recinfo.ATTRIBUTE30 = X_ATTRIBUTE30)
337 OR ((recinfo.ATTRIBUTE30 is null) AND (X_ATTRIBUTE30 is null)))
338 AND (recinfo.DELETE_MARK = X_DELETE_MARK)
339 ) then
340 null;
341 else
342 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
343 app_exception.raise_exception;
344 end if;
345
346 for tlinfo in c1 loop
347 if (tlinfo.BASELANG = 'Y') then
348 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
349 ) then
350 null;
351 else
352 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
353 app_exception.raise_exception;
354 end if;
355 end if;
356 end loop;
357 return;
358 end LOCK_ROW;
359
360 procedure UPDATE_ROW (
361 X_STORAGE_PLAN_ID in NUMBER,
362 X_STORAGE_PLAN_NAME in VARCHAR2,
363 X_BASE_TEST_INTERVAL_PLAN_ID in NUMBER,
364 X_TEXT_CODE in NUMBER,
365 X_ATTRIBUTE_CATEGORY in VARCHAR2,
366 X_ATTRIBUTE1 in VARCHAR2,
367 X_ATTRIBUTE2 in VARCHAR2,
368 X_ATTRIBUTE3 in VARCHAR2,
369 X_ATTRIBUTE4 in VARCHAR2,
370 X_ATTRIBUTE5 in VARCHAR2,
371 X_ATTRIBUTE6 in VARCHAR2,
372 X_ATTRIBUTE7 in VARCHAR2,
373 X_ATTRIBUTE8 in VARCHAR2,
374 X_ATTRIBUTE9 in VARCHAR2,
375 X_ATTRIBUTE10 in VARCHAR2,
376 X_ATTRIBUTE11 in VARCHAR2,
377 X_ATTRIBUTE12 in VARCHAR2,
378 X_ATTRIBUTE13 in VARCHAR2,
379 X_ATTRIBUTE14 in VARCHAR2,
380 X_ATTRIBUTE15 in VARCHAR2,
381 X_ATTRIBUTE16 in VARCHAR2,
382 X_ATTRIBUTE17 in VARCHAR2,
383 X_ATTRIBUTE18 in VARCHAR2,
384 X_ATTRIBUTE19 in VARCHAR2,
385 X_ATTRIBUTE20 in VARCHAR2,
386 X_ATTRIBUTE21 in VARCHAR2,
387 X_ATTRIBUTE22 in VARCHAR2,
388 X_ATTRIBUTE23 in VARCHAR2,
389 X_ATTRIBUTE24 in VARCHAR2,
390 X_ATTRIBUTE25 in VARCHAR2,
391 X_ATTRIBUTE26 in VARCHAR2,
392 X_ATTRIBUTE27 in VARCHAR2,
393 X_ATTRIBUTE28 in VARCHAR2,
394 X_ATTRIBUTE29 in VARCHAR2,
395 X_ATTRIBUTE30 in VARCHAR2,
396 X_DELETE_MARK in NUMBER,
397 X_DESCRIPTION in VARCHAR2,
398 X_LAST_UPDATE_DATE in DATE,
399 X_LAST_UPDATED_BY in NUMBER,
400 X_LAST_UPDATE_LOGIN in NUMBER
401 ) is
402 begin
403 update GMD_STORAGE_PLANS_B set
404 STORAGE_PLAN_NAME = X_STORAGE_PLAN_NAME,
405 BASE_TEST_INTERVAL_PLAN_ID = X_BASE_TEST_INTERVAL_PLAN_ID,
406 TEXT_CODE = X_TEXT_CODE,
407 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
408 ATTRIBUTE1 = X_ATTRIBUTE1,
409 ATTRIBUTE2 = X_ATTRIBUTE2,
410 ATTRIBUTE3 = X_ATTRIBUTE3,
411 ATTRIBUTE4 = X_ATTRIBUTE4,
412 ATTRIBUTE5 = X_ATTRIBUTE5,
413 ATTRIBUTE6 = X_ATTRIBUTE6,
414 ATTRIBUTE7 = X_ATTRIBUTE7,
415 ATTRIBUTE8 = X_ATTRIBUTE8,
416 ATTRIBUTE9 = X_ATTRIBUTE9,
417 ATTRIBUTE10 = X_ATTRIBUTE10,
418 ATTRIBUTE11 = X_ATTRIBUTE11,
419 ATTRIBUTE12 = X_ATTRIBUTE12,
420 ATTRIBUTE13 = X_ATTRIBUTE13,
421 ATTRIBUTE14 = X_ATTRIBUTE14,
422 ATTRIBUTE15 = X_ATTRIBUTE15,
423 ATTRIBUTE16 = X_ATTRIBUTE16,
424 ATTRIBUTE17 = X_ATTRIBUTE17,
425 ATTRIBUTE18 = X_ATTRIBUTE18,
426 ATTRIBUTE19 = X_ATTRIBUTE19,
427 ATTRIBUTE20 = X_ATTRIBUTE20,
428 ATTRIBUTE21 = X_ATTRIBUTE21,
429 ATTRIBUTE22 = X_ATTRIBUTE22,
430 ATTRIBUTE23 = X_ATTRIBUTE23,
431 ATTRIBUTE24 = X_ATTRIBUTE24,
432 ATTRIBUTE25 = X_ATTRIBUTE25,
433 ATTRIBUTE26 = X_ATTRIBUTE26,
434 ATTRIBUTE27 = X_ATTRIBUTE27,
435 ATTRIBUTE28 = X_ATTRIBUTE28,
436 ATTRIBUTE29 = X_ATTRIBUTE29,
437 ATTRIBUTE30 = X_ATTRIBUTE30,
438 DELETE_MARK = X_DELETE_MARK,
439 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
440 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
441 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
442 where STORAGE_PLAN_ID = X_STORAGE_PLAN_ID;
443
444 if (sql%notfound) then
445 raise no_data_found;
446 end if;
447
448 update GMD_STORAGE_PLANS_TL set
449 DESCRIPTION = X_DESCRIPTION,
450 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
451 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
452 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
453 SOURCE_LANG = userenv('LANG')
454 where STORAGE_PLAN_ID = X_STORAGE_PLAN_ID
455 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
456
457 if (sql%notfound) then
458 raise no_data_found;
459 end if;
460 end UPDATE_ROW;
461
462 procedure DELETE_ROW (
463 X_STORAGE_PLAN_ID in NUMBER
464 ) is
465 begin
466 delete from GMD_STORAGE_PLANS_TL
467 where STORAGE_PLAN_ID = X_STORAGE_PLAN_ID;
468
469 if (sql%notfound) then
470 raise no_data_found;
471 end if;
472
473 delete from GMD_STORAGE_PLANS_B
474 where STORAGE_PLAN_ID = X_STORAGE_PLAN_ID;
475
476 if (sql%notfound) then
477 raise no_data_found;
478 end if;
479 end DELETE_ROW;
480
481 procedure ADD_LANGUAGE
482 is
483 begin
484 delete from GMD_STORAGE_PLANS_TL T
485 where not exists
486 (select NULL
487 from GMD_STORAGE_PLANS_B B
488 where B.STORAGE_PLAN_ID = T.STORAGE_PLAN_ID
489 );
490
491 update GMD_STORAGE_PLANS_TL T set (
492 DESCRIPTION
493 ) = (select
494 B.DESCRIPTION
495 from GMD_STORAGE_PLANS_TL B
496 where B.STORAGE_PLAN_ID = T.STORAGE_PLAN_ID
497 and B.LANGUAGE = T.SOURCE_LANG)
498 where (
499 T.STORAGE_PLAN_ID,
500 T.LANGUAGE
501 ) in (select
502 SUBT.STORAGE_PLAN_ID,
503 SUBT.LANGUAGE
504 from GMD_STORAGE_PLANS_TL SUBB, GMD_STORAGE_PLANS_TL SUBT
505 where SUBB.STORAGE_PLAN_ID = SUBT.STORAGE_PLAN_ID
506 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
507 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
508 ));
509
510 insert into GMD_STORAGE_PLANS_TL (
511 STORAGE_PLAN_ID,
512 DESCRIPTION,
513 CREATION_DATE,
514 CREATED_BY,
515 LAST_UPDATED_BY,
516 LAST_UPDATE_DATE,
517 LAST_UPDATE_LOGIN,
518 LANGUAGE,
519 SOURCE_LANG
520 ) select
521 B.STORAGE_PLAN_ID,
522 B.DESCRIPTION,
523 B.CREATION_DATE,
524 B.CREATED_BY,
525 B.LAST_UPDATED_BY,
526 B.LAST_UPDATE_DATE,
527 B.LAST_UPDATE_LOGIN,
528 L.LANGUAGE_CODE,
529 B.SOURCE_LANG
530 from GMD_STORAGE_PLANS_TL B, FND_LANGUAGES L
531 where L.INSTALLED_FLAG in ('I', 'B')
532 and B.LANGUAGE = userenv('LANG')
533 and not exists
534 (select NULL
535 from GMD_STORAGE_PLANS_TL T
536 where T.STORAGE_PLAN_ID = B.STORAGE_PLAN_ID
537 and T.LANGUAGE = L.LANGUAGE_CODE);
538 end ADD_LANGUAGE;
539
540 end GMD_STORAGE_PLANS_PKG;