[Home] [Help]
PACKAGE BODY: APPS.GMD_STABILITY_STUDIES_PVT
Source
1 PACKAGE BODY GMD_STABILITY_STUDIES_PVT as
2 /* $Header: GMDVSSTB.pls 120.2 2005/09/02 01:47:09 svankada noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_SS_ID in NUMBER,
6 X_ATTRIBUTE29 in VARCHAR2,
7 X_ATTRIBUTE30 in VARCHAR2,
8 X_DELETE_MARK in NUMBER,
9 X_STORAGE_SPEC_ID in NUMBER,
10 X_ATTRIBUTE_CATEGORY in VARCHAR2,
11 X_ATTRIBUTE25 in VARCHAR2,
12 X_RECOMMENDED_SHELF_LIFE_UNIT in VARCHAR2,
13 X_TEXT_CODE in NUMBER,
14 X_SCHEDULED_START_DATE in DATE,
15 X_SCHEDULED_END_DATE in DATE,
16 X_REVISED_START_DATE in DATE,
17 X_REVISED_END_DATE in DATE,
18 X_ACTUAL_START_DATE in DATE,
19 X_ACTUAL_END_DATE in DATE,
20 X_RECOMMENDED_SHELF_LIFE in NUMBER,
21 X_ATTRIBUTE26 in VARCHAR2,
22 X_ATTRIBUTE27 in VARCHAR2,
23 X_ATTRIBUTE28 in VARCHAR2,
24 X_ATTRIBUTE1 in VARCHAR2,
25 X_ATTRIBUTE2 in VARCHAR2,
26 X_ATTRIBUTE3 in VARCHAR2,
27 X_ATTRIBUTE4 in VARCHAR2,
28 X_ATTRIBUTE5 in VARCHAR2,
29 X_ATTRIBUTE6 in VARCHAR2,
30 X_ATTRIBUTE7 in VARCHAR2,
31 X_ATTRIBUTE8 in VARCHAR2,
32 X_ATTRIBUTE9 in VARCHAR2,
33 X_ATTRIBUTE10 in VARCHAR2,
34 X_ATTRIBUTE11 in VARCHAR2,
35 X_ATTRIBUTE12 in VARCHAR2,
36 X_ATTRIBUTE13 in VARCHAR2,
37 X_ATTRIBUTE14 in VARCHAR2,
38 X_ATTRIBUTE15 in VARCHAR2,
39 X_ATTRIBUTE16 in VARCHAR2,
40 X_ATTRIBUTE17 in VARCHAR2,
41 X_ATTRIBUTE18 in VARCHAR2,
42 X_ATTRIBUTE19 in VARCHAR2,
43 X_ATTRIBUTE20 in VARCHAR2,
44 X_ATTRIBUTE21 in VARCHAR2,
45 X_ATTRIBUTE22 in VARCHAR2,
46 X_ATTRIBUTE23 in VARCHAR2,
47 X_ATTRIBUTE24 in VARCHAR2,
48 X_NOTIFICATION_LEAD_TIME in NUMBER,
49 X_NOTIFICATION_LEAD_TIME_UNIT in VARCHAR2,
50 X_TESTING_GRACE_PERIOD in NUMBER,
51 X_TESTING_GRACE_PERIOD_UNIT in VARCHAR2,
52 --X_ORGN_CODE in VARCHAR2, -- INVCONV
53 X_SS_NO in VARCHAR2,
54 X_STATUS in NUMBER,
55 X_STORAGE_PLAN_ID in NUMBER,
56 --X_ITEM_ID in NUMBER, -- INVCONV
57 X_BASE_SPEC_ID in NUMBER,
58 --X_QC_LAB_ORGN_CODE in VARCHAR2, -- INVCONV
59 X_OWNER in NUMBER,
60 X_MATERIAL_SOURCES_CNT in NUMBER,
61 X_STORAGE_CONDITIONS_CNT in NUMBER,
62 X_PACKAGES_CNT in NUMBER,
63 X_DESCRIPTION in VARCHAR2,
64 X_ORGANIZATION_ID in NUMBER, -- INVCONV
65 X_INVENTORY_ITEM_ID in NUMBER, -- INVCONV
66 X_REVISION in VARCHAR2, -- INVCONV
67 X_LAB_ORGANIZATION_ID in NUMBER, -- INVCONV
68 X_CREATION_DATE in DATE,
69 X_CREATED_BY in NUMBER,
70 X_LAST_UPDATE_DATE in DATE,
71 X_LAST_UPDATED_BY in NUMBER,
72 X_LAST_UPDATE_LOGIN in NUMBER
73 ) is
74 cursor C is select ROWID from GMD_STABILITY_STUDIES_B
75 where SS_ID = X_SS_ID
76 ;
77 begin
78 insert into GMD_STABILITY_STUDIES_B (
79 ATTRIBUTE29,
80 ATTRIBUTE30,
81 DELETE_MARK,
82 STORAGE_SPEC_ID,
83 ATTRIBUTE_CATEGORY,
84 ATTRIBUTE25,
85 SS_ID,
86 RECOMMENDED_SHELF_LIFE_UNIT,
87 TEXT_CODE,
88 SCHEDULED_START_DATE,
89 SCHEDULED_END_DATE,
90 REVISED_START_DATE,
91 REVISED_END_DATE,
92 ACTUAL_START_DATE,
93 ACTUAL_END_DATE,
94 RECOMMENDED_SHELF_LIFE,
95 ATTRIBUTE26,
96 ATTRIBUTE27,
97 ATTRIBUTE28,
98 ATTRIBUTE1,
99 ATTRIBUTE2,
100 ATTRIBUTE3,
101 ATTRIBUTE4,
102 ATTRIBUTE5,
103 ATTRIBUTE6,
104 ATTRIBUTE7,
105 ATTRIBUTE8,
106 ATTRIBUTE9,
107 ATTRIBUTE10,
108 ATTRIBUTE11,
109 ATTRIBUTE12,
110 ATTRIBUTE13,
111 ATTRIBUTE14,
112 ATTRIBUTE15,
113 ATTRIBUTE16,
114 ATTRIBUTE17,
115 ATTRIBUTE18,
116 ATTRIBUTE19,
117 ATTRIBUTE20,
118 ATTRIBUTE21,
119 ATTRIBUTE22,
120 ATTRIBUTE23,
121 ATTRIBUTE24,
122 NOTIFICATION_LEAD_TIME,
123 NOTIFICATION_LEAD_TIME_UNIT,
124 TESTING_GRACE_PERIOD,
125 TESTING_GRACE_PERIOD_UNIT,
126 --ORGN_CODE, -- INVCONV
127 SS_NO,
128 STATUS,
129 STORAGE_PLAN_ID,
130 --ITEM_ID, -- INVCONV
131 BASE_SPEC_ID,
132 --QC_LAB_ORGN_CODE, -- INVCONV
133 OWNER,
134 MATERIAL_SOURCES_CNT,
135 STORAGE_CONDITIONS_CNT,
136 PACKAGES_CNT,
137 ORGANIZATION_ID, -- INVCONV
138 INVENTORY_ITEM_ID , -- INVCONV
139 REVISION , -- INVCONV
140 LAB_ORGANIZATION_ID , -- INVCONV
141 CREATION_DATE,
142 CREATED_BY,
143 LAST_UPDATE_DATE,
144 LAST_UPDATED_BY,
145 LAST_UPDATE_LOGIN
146 ) values (
147 X_ATTRIBUTE29,
148 X_ATTRIBUTE30,
149 X_DELETE_MARK,
150 X_STORAGE_SPEC_ID,
151 X_ATTRIBUTE_CATEGORY,
152 X_ATTRIBUTE25,
153 X_SS_ID,
154 X_RECOMMENDED_SHELF_LIFE_UNIT,
155 X_TEXT_CODE,
156 X_SCHEDULED_START_DATE,
157 X_SCHEDULED_END_DATE,
158 X_REVISED_START_DATE,
159 X_REVISED_END_DATE,
160 X_ACTUAL_START_DATE,
161 X_ACTUAL_END_DATE,
162 X_RECOMMENDED_SHELF_LIFE,
163 X_ATTRIBUTE26,
164 X_ATTRIBUTE27,
165 X_ATTRIBUTE28,
166 X_ATTRIBUTE1,
167 X_ATTRIBUTE2,
168 X_ATTRIBUTE3,
169 X_ATTRIBUTE4,
170 X_ATTRIBUTE5,
171 X_ATTRIBUTE6,
172 X_ATTRIBUTE7,
173 X_ATTRIBUTE8,
174 X_ATTRIBUTE9,
175 X_ATTRIBUTE10,
176 X_ATTRIBUTE11,
177 X_ATTRIBUTE12,
178 X_ATTRIBUTE13,
179 X_ATTRIBUTE14,
180 X_ATTRIBUTE15,
181 X_ATTRIBUTE16,
182 X_ATTRIBUTE17,
183 X_ATTRIBUTE18,
184 X_ATTRIBUTE19,
185 X_ATTRIBUTE20,
186 X_ATTRIBUTE21,
187 X_ATTRIBUTE22,
188 X_ATTRIBUTE23,
189 X_ATTRIBUTE24,
190 X_NOTIFICATION_LEAD_TIME,
191 X_NOTIFICATION_LEAD_TIME_UNIT,
192 X_TESTING_GRACE_PERIOD,
193 X_TESTING_GRACE_PERIOD_UNIT,
194 --X_ORGN_CODE, -- INVCONV
195 X_SS_NO,
196 X_STATUS,
197 X_STORAGE_PLAN_ID,
198 --X_ITEM_ID, -- INVCONV
199 X_BASE_SPEC_ID,
200 --X_QC_LAB_ORGN_CODE, -- INVCONV
201 X_OWNER,
202 X_MATERIAL_SOURCES_CNT,
203 X_STORAGE_CONDITIONS_CNT,
204 X_PACKAGES_CNT,
205 X_ORGANIZATION_ID , -- INVCONV
206 X_INVENTORY_ITEM_ID , -- INVCONV
207 X_REVISION , -- INVCONV
208 X_LAB_ORGANIZATION_ID , -- INVCONV
209 X_CREATION_DATE,
210 X_CREATED_BY,
211 X_LAST_UPDATE_DATE,
212 X_LAST_UPDATED_BY,
213 X_LAST_UPDATE_LOGIN
214 );
215
216 insert into GMD_STABILITY_STUDIES_TL (
217 SS_ID,
218 DESCRIPTION,
219 CREATION_DATE,
220 CREATED_BY,
221 LAST_UPDATED_BY,
222 LAST_UPDATE_DATE,
223 LAST_UPDATE_LOGIN,
224 LANGUAGE,
225 SOURCE_LANG
226 ) select
227 X_SS_ID,
228 X_DESCRIPTION,
229 X_CREATION_DATE,
230 X_CREATED_BY,
231 X_LAST_UPDATED_BY,
232 X_LAST_UPDATE_DATE,
233 X_LAST_UPDATE_LOGIN,
234 L.LANGUAGE_CODE,
235 userenv('LANG')
236 from FND_LANGUAGES L
237 where L.INSTALLED_FLAG in ('I', 'B')
238 and not exists
239 (select NULL
240 from GMD_STABILITY_STUDIES_TL T
241 where T.SS_ID = X_SS_ID
242 and T.LANGUAGE = L.LANGUAGE_CODE);
243
244 open c;
245 fetch c into X_ROWID;
246 if (c%notfound) then
247 close c;
248 raise no_data_found;
249 end if;
250 close c;
251
252 end INSERT_ROW;
253
254 procedure LOCK_ROW (
255 X_SS_ID in NUMBER,
256 X_ATTRIBUTE29 in VARCHAR2,
257 X_ATTRIBUTE30 in VARCHAR2,
258 X_DELETE_MARK in NUMBER,
259 X_STORAGE_SPEC_ID in NUMBER,
260 X_ATTRIBUTE_CATEGORY in VARCHAR2,
261 X_ATTRIBUTE25 in VARCHAR2,
262 X_RECOMMENDED_SHELF_LIFE_UNIT in VARCHAR2,
263 X_TEXT_CODE in NUMBER,
264 X_SCHEDULED_START_DATE in DATE,
265 X_SCHEDULED_END_DATE in DATE,
266 X_REVISED_START_DATE in DATE,
267 X_REVISED_END_DATE in DATE,
268 X_ACTUAL_START_DATE in DATE,
269 X_ACTUAL_END_DATE in DATE,
270 X_RECOMMENDED_SHELF_LIFE in NUMBER,
271 X_ATTRIBUTE26 in VARCHAR2,
272 X_ATTRIBUTE27 in VARCHAR2,
273 X_ATTRIBUTE28 in VARCHAR2,
274 X_ATTRIBUTE1 in VARCHAR2,
275 X_ATTRIBUTE2 in VARCHAR2,
276 X_ATTRIBUTE3 in VARCHAR2,
277 X_ATTRIBUTE4 in VARCHAR2,
278 X_ATTRIBUTE5 in VARCHAR2,
279 X_ATTRIBUTE6 in VARCHAR2,
280 X_ATTRIBUTE7 in VARCHAR2,
281 X_ATTRIBUTE8 in VARCHAR2,
282 X_ATTRIBUTE9 in VARCHAR2,
283 X_ATTRIBUTE10 in VARCHAR2,
284 X_ATTRIBUTE11 in VARCHAR2,
285 X_ATTRIBUTE12 in VARCHAR2,
286 X_ATTRIBUTE13 in VARCHAR2,
287 X_ATTRIBUTE14 in VARCHAR2,
288 X_ATTRIBUTE15 in VARCHAR2,
289 X_ATTRIBUTE16 in VARCHAR2,
290 X_ATTRIBUTE17 in VARCHAR2,
291 X_ATTRIBUTE18 in VARCHAR2,
292 X_ATTRIBUTE19 in VARCHAR2,
293 X_ATTRIBUTE20 in VARCHAR2,
294 X_ATTRIBUTE21 in VARCHAR2,
295 X_ATTRIBUTE22 in VARCHAR2,
296 X_ATTRIBUTE23 in VARCHAR2,
297 X_ATTRIBUTE24 in VARCHAR2,
298 X_NOTIFICATION_LEAD_TIME in NUMBER,
299 X_NOTIFICATION_LEAD_TIME_UNIT in VARCHAR2,
300 X_TESTING_GRACE_PERIOD in NUMBER,
301 X_TESTING_GRACE_PERIOD_UNIT in VARCHAR2,
302 --X_ORGN_CODE in VARCHAR2, -- INVCONV
303 X_SS_NO in VARCHAR2,
304 X_STATUS in NUMBER,
305 X_STORAGE_PLAN_ID in NUMBER,
306 --X_ITEM_ID in NUMBER, -- INVCONV
307 X_BASE_SPEC_ID in NUMBER,
308 --X_QC_LAB_ORGN_CODE in VARCHAR2, -- INVCONV
309 X_OWNER in NUMBER,
310 X_MATERIAL_SOURCES_CNT in NUMBER,
311 X_PACKAGES_CNT in NUMBER,
312 X_DESCRIPTION in VARCHAR2,
313 X_ORGANIZATION_ID in NUMBER, -- INVCONV
314 X_INVENTORY_ITEM_ID in NUMBER, -- INVCONV
315 X_REVISION in VARCHAR2, -- INVCONV
316 X_LAB_ORGANIZATION_ID in NUMBER -- INVCONV
317 ) is
318 cursor c is select
319 ATTRIBUTE29,
320 ATTRIBUTE30,
321 DELETE_MARK,
322 STORAGE_SPEC_ID,
323 ATTRIBUTE_CATEGORY,
324 ATTRIBUTE25,
325 RECOMMENDED_SHELF_LIFE_UNIT,
326 TEXT_CODE,
327 SCHEDULED_START_DATE,
328 SCHEDULED_END_DATE,
329 REVISED_START_DATE,
330 REVISED_END_DATE,
331 ACTUAL_START_DATE,
332 ACTUAL_END_DATE,
333 RECOMMENDED_SHELF_LIFE,
334 ATTRIBUTE26,
335 ATTRIBUTE27,
336 ATTRIBUTE28,
337 ATTRIBUTE1,
338 ATTRIBUTE2,
339 ATTRIBUTE3,
340 ATTRIBUTE4,
341 ATTRIBUTE5,
342 ATTRIBUTE6,
343 ATTRIBUTE7,
344 ATTRIBUTE8,
345 ATTRIBUTE9,
346 ATTRIBUTE10,
347 ATTRIBUTE11,
348 ATTRIBUTE12,
349 ATTRIBUTE13,
350 ATTRIBUTE14,
351 ATTRIBUTE15,
352 ATTRIBUTE16,
353 ATTRIBUTE17,
354 ATTRIBUTE18,
355 ATTRIBUTE19,
356 ATTRIBUTE20,
357 ATTRIBUTE21,
358 ATTRIBUTE22,
359 ATTRIBUTE23,
360 ATTRIBUTE24,
361 NOTIFICATION_LEAD_TIME,
362 NOTIFICATION_LEAD_TIME_UNIT,
363 TESTING_GRACE_PERIOD,
364 TESTING_GRACE_PERIOD_UNIT,
365 --ORGN_CODE, -- INVCONV
366 SS_NO,
367 STATUS,
368 STORAGE_PLAN_ID,
369 --ITEM_ID, -- INVCONV
370 BASE_SPEC_ID,
371 --QC_LAB_ORGN_CODE, -- INVCONV
372 OWNER,
373 MATERIAL_SOURCES_CNT,
374 PACKAGES_CNT,
375 ORGANIZATION_ID , -- INVCONV
376 INVENTORY_ITEM_ID , -- INVCONV
377 REVISION , -- INVCONV
378 LAB_ORGANIZATION_ID -- INVCONV
379 from GMD_STABILITY_STUDIES_B
380 where SS_ID = X_SS_ID
381 for update of SS_ID nowait;
382 recinfo c%rowtype;
383
384 cursor c1 is select
385 DESCRIPTION,
386 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
387 from GMD_STABILITY_STUDIES_TL
388 where SS_ID = X_SS_ID
389 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
390 for update of SS_ID nowait;
391 begin
392 open c;
393 fetch c into recinfo;
394 if (c%notfound) then
395 close c;
396 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
397 app_exception.raise_exception;
398 end if;
399 close c;
400 if ( ((recinfo.ATTRIBUTE29 = X_ATTRIBUTE29)
401 OR ((recinfo.ATTRIBUTE29 is null) AND (X_ATTRIBUTE29 is null)))
402 AND ((recinfo.ATTRIBUTE30 = X_ATTRIBUTE30)
403 OR ((recinfo.ATTRIBUTE30 is null) AND (X_ATTRIBUTE30 is null)))
404 AND (recinfo.DELETE_MARK = X_DELETE_MARK)
405 AND ((recinfo.STORAGE_SPEC_ID = X_STORAGE_SPEC_ID)
406 OR ((recinfo.STORAGE_SPEC_ID is null) AND (X_STORAGE_SPEC_ID is null)))
407 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
408 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
409 AND ((recinfo.ATTRIBUTE25 = X_ATTRIBUTE25)
410 OR ((recinfo.ATTRIBUTE25 is null) AND (X_ATTRIBUTE25 is null)))
411 AND ((recinfo.RECOMMENDED_SHELF_LIFE_UNIT = X_RECOMMENDED_SHELF_LIFE_UNIT)
412 OR ((recinfo.RECOMMENDED_SHELF_LIFE_UNIT is null) AND (X_RECOMMENDED_SHELF_LIFE_UNIT is null)))
413 AND ((recinfo.TEXT_CODE = X_TEXT_CODE)
414 OR ((recinfo.TEXT_CODE is null) AND (X_TEXT_CODE is null)))
415 AND ((recinfo.SCHEDULED_START_DATE = X_SCHEDULED_START_DATE)
416 OR ((recinfo.SCHEDULED_START_DATE is null) AND (X_SCHEDULED_START_DATE is null)))
417 AND ((recinfo.SCHEDULED_END_DATE = X_SCHEDULED_END_DATE)
418 OR ((recinfo.SCHEDULED_END_DATE is null) AND (X_SCHEDULED_END_DATE is null)))
419 AND ((recinfo.REVISED_START_DATE = X_REVISED_START_DATE)
420 OR ((recinfo.REVISED_START_DATE is null) AND (X_REVISED_START_DATE is null)))
421 AND ((recinfo.REVISED_END_DATE = X_REVISED_END_DATE)
422 OR ((recinfo.REVISED_END_DATE is null) AND (X_REVISED_END_DATE is null)))
423 AND ((recinfo.ACTUAL_START_DATE = X_ACTUAL_START_DATE)
424 OR ((recinfo.ACTUAL_START_DATE is null) AND (X_ACTUAL_START_DATE is null)))
425 AND ((recinfo.ACTUAL_END_DATE = X_ACTUAL_END_DATE)
426 OR ((recinfo.ACTUAL_END_DATE is null) AND (X_ACTUAL_END_DATE is null)))
427 AND ((recinfo.RECOMMENDED_SHELF_LIFE = X_RECOMMENDED_SHELF_LIFE)
428 OR ((recinfo.RECOMMENDED_SHELF_LIFE is null) AND (X_RECOMMENDED_SHELF_LIFE is null)))
429 AND ((recinfo.ATTRIBUTE26 = X_ATTRIBUTE26)
430 OR ((recinfo.ATTRIBUTE26 is null) AND (X_ATTRIBUTE26 is null)))
431 AND ((recinfo.ATTRIBUTE27 = X_ATTRIBUTE27)
432 OR ((recinfo.ATTRIBUTE27 is null) AND (X_ATTRIBUTE27 is null)))
433 AND ((recinfo.ATTRIBUTE28 = X_ATTRIBUTE28)
434 OR ((recinfo.ATTRIBUTE28 is null) AND (X_ATTRIBUTE28 is null)))
435 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
436 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
437 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
438 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
439 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
440 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
441 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
442 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
443 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
444 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
445 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
446 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
447 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
448 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
449 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
450 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
451 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
452 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
453 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
454 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
455 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
456 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
457 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
458 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
459 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
460 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
461 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
462 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
463 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
464 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
465 AND ((recinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
466 OR ((recinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
467 AND ((recinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
468 OR ((recinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
469 AND ((recinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
470 OR ((recinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
471 AND ((recinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
472 OR ((recinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
473 AND ((recinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
474 OR ((recinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
475 AND ((recinfo.ATTRIBUTE21 = X_ATTRIBUTE21)
476 OR ((recinfo.ATTRIBUTE21 is null) AND (X_ATTRIBUTE21 is null)))
477 AND ((recinfo.ATTRIBUTE22 = X_ATTRIBUTE22)
478 OR ((recinfo.ATTRIBUTE22 is null) AND (X_ATTRIBUTE22 is null)))
479 AND ((recinfo.ATTRIBUTE23 = X_ATTRIBUTE23)
480 OR ((recinfo.ATTRIBUTE23 is null) AND (X_ATTRIBUTE23 is null)))
481 AND ((recinfo.ATTRIBUTE24 = X_ATTRIBUTE24)
482 OR ((recinfo.ATTRIBUTE24 is null) AND (X_ATTRIBUTE24 is null)))
483 AND ((recinfo.NOTIFICATION_LEAD_TIME = X_NOTIFICATION_LEAD_TIME)
484 OR ((recinfo.NOTIFICATION_LEAD_TIME is null) AND (X_NOTIFICATION_LEAD_TIME is null)))
485 AND ((recinfo.NOTIFICATION_LEAD_TIME_UNIT = X_NOTIFICATION_LEAD_TIME_UNIT)
486 OR ((recinfo.NOTIFICATION_LEAD_TIME_UNIT is null) AND (X_NOTIFICATION_LEAD_TIME_UNIT is null)))
487 AND ((recinfo.TESTING_GRACE_PERIOD = X_TESTING_GRACE_PERIOD)
488 OR ((recinfo.TESTING_GRACE_PERIOD is null) AND (X_TESTING_GRACE_PERIOD is null)))
489 AND ((recinfo.TESTING_GRACE_PERIOD_UNIT = X_TESTING_GRACE_PERIOD_UNIT)
490 OR ((recinfo.TESTING_GRACE_PERIOD_UNIT is null) AND (X_TESTING_GRACE_PERIOD_UNIT is null)))
491 --AND (recinfo.ORGN_CODE = X_ORGN_CODE) -- INVCONV
492 AND (recinfo.SS_NO = X_SS_NO)
493 AND (recinfo.STATUS = X_STATUS)
494 AND (recinfo.STORAGE_PLAN_ID = X_STORAGE_PLAN_ID)
495 --AND (recinfo.ITEM_ID = X_ITEM_ID) -- INVCONV
496 AND (recinfo.BASE_SPEC_ID = X_BASE_SPEC_ID)
497 --AND (recinfo.QC_LAB_ORGN_CODE = X_QC_LAB_ORGN_CODE) -- INVCONV
498 AND (recinfo.OWNER = X_OWNER)
499 AND (recinfo.MATERIAL_SOURCES_CNT = X_MATERIAL_SOURCES_CNT)
500 AND (recinfo.PACKAGES_CNT = X_PACKAGES_CNT)
501 AND (recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID) -- INVCONV
502 AND (recinfo.INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID) -- INVCONV
503 AND ((recinfo.REVISION = X_REVISION) OR (recinfo.REVISION IS NULL AND X_REVISION IS NULL))-- INVCONV
504 AND (recinfo.LAB_ORGANIZATION_ID = X_LAB_ORGANIZATION_ID) -- INVCONV
505
506 ) then
507 null;
508 else
509 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
510 app_exception.raise_exception;
511 end if;
512
513 for tlinfo in c1 loop
514 if (tlinfo.BASELANG = 'Y') then
515 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
516 ) then
517 null;
518 else
519 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
520 app_exception.raise_exception;
521 end if;
522 end if;
523 end loop;
524 return;
525 end LOCK_ROW;
526
527 procedure UPDATE_ROW (
528 X_SS_ID in NUMBER,
529 X_ATTRIBUTE29 in VARCHAR2,
530 X_ATTRIBUTE30 in VARCHAR2,
531 X_DELETE_MARK in NUMBER,
532 X_STORAGE_SPEC_ID in NUMBER,
533 X_ATTRIBUTE_CATEGORY in VARCHAR2,
534 X_ATTRIBUTE25 in VARCHAR2,
535 X_RECOMMENDED_SHELF_LIFE_UNIT in VARCHAR2,
536 X_TEXT_CODE in NUMBER,
537 X_SCHEDULED_START_DATE in DATE,
538 X_SCHEDULED_END_DATE in DATE,
539 X_REVISED_START_DATE in DATE,
540 X_REVISED_END_DATE in DATE,
541 X_ACTUAL_START_DATE in DATE,
542 X_ACTUAL_END_DATE in DATE,
543 X_RECOMMENDED_SHELF_LIFE in NUMBER,
544 X_ATTRIBUTE26 in VARCHAR2,
545 X_ATTRIBUTE27 in VARCHAR2,
546 X_ATTRIBUTE28 in VARCHAR2,
547 X_ATTRIBUTE1 in VARCHAR2,
548 X_ATTRIBUTE2 in VARCHAR2,
549 X_ATTRIBUTE3 in VARCHAR2,
550 X_ATTRIBUTE4 in VARCHAR2,
551 X_ATTRIBUTE5 in VARCHAR2,
552 X_ATTRIBUTE6 in VARCHAR2,
553 X_ATTRIBUTE7 in VARCHAR2,
554 X_ATTRIBUTE8 in VARCHAR2,
555 X_ATTRIBUTE9 in VARCHAR2,
556 X_ATTRIBUTE10 in VARCHAR2,
557 X_ATTRIBUTE11 in VARCHAR2,
558 X_ATTRIBUTE12 in VARCHAR2,
559 X_ATTRIBUTE13 in VARCHAR2,
560 X_ATTRIBUTE14 in VARCHAR2,
561 X_ATTRIBUTE15 in VARCHAR2,
562 X_ATTRIBUTE16 in VARCHAR2,
563 X_ATTRIBUTE17 in VARCHAR2,
564 X_ATTRIBUTE18 in VARCHAR2,
565 X_ATTRIBUTE19 in VARCHAR2,
566 X_ATTRIBUTE20 in VARCHAR2,
567 X_ATTRIBUTE21 in VARCHAR2,
568 X_ATTRIBUTE22 in VARCHAR2,
569 X_ATTRIBUTE23 in VARCHAR2,
570 X_ATTRIBUTE24 in VARCHAR2,
571 X_NOTIFICATION_LEAD_TIME in NUMBER,
572 X_NOTIFICATION_LEAD_TIME_UNIT in VARCHAR2,
573 X_TESTING_GRACE_PERIOD in NUMBER,
574 X_TESTING_GRACE_PERIOD_UNIT in VARCHAR2,
575 --X_ORGN_CODE in VARCHAR2, -- INVCONV
576 X_SS_NO in VARCHAR2,
577 X_STATUS in NUMBER,
578 X_STORAGE_PLAN_ID in NUMBER,
579 --X_ITEM_ID in NUMBER, -- INVCONV
580 X_BASE_SPEC_ID in NUMBER,
581 --X_QC_LAB_ORGN_CODE in VARCHAR2, -- INVCONV
582 X_OWNER in NUMBER,
583 X_MATERIAL_SOURCES_CNT in NUMBER,
584 X_STORAGE_CONDITIONS_CNT in NUMBER,
585 X_PACKAGES_CNT in NUMBER,
586 X_DESCRIPTION in VARCHAR2,
587 X_ORGANIZATION_ID in NUMBER, -- INVCONV
588 X_INVENTORY_ITEM_ID in NUMBER, -- INVCONV
589 X_REVISION in VARCHAR2, -- INVCONV
590 X_LAB_ORGANIZATION_ID in NUMBER, -- INVCONV
591 X_LAST_UPDATE_DATE in DATE,
592 X_LAST_UPDATED_BY in NUMBER,
593 X_LAST_UPDATE_LOGIN in NUMBER
594 ) is
595 begin
596 update GMD_STABILITY_STUDIES_B set
597 ATTRIBUTE29 = X_ATTRIBUTE29,
598 ATTRIBUTE30 = X_ATTRIBUTE30,
599 DELETE_MARK = X_DELETE_MARK,
600 STORAGE_SPEC_ID = X_STORAGE_SPEC_ID,
601 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
602 ATTRIBUTE25 = X_ATTRIBUTE25,
603 RECOMMENDED_SHELF_LIFE_UNIT = X_RECOMMENDED_SHELF_LIFE_UNIT,
604 TEXT_CODE = X_TEXT_CODE,
605 SCHEDULED_START_DATE = X_SCHEDULED_START_DATE,
606 SCHEDULED_END_DATE = X_SCHEDULED_END_DATE,
607 REVISED_START_DATE = X_REVISED_START_DATE,
608 REVISED_END_DATE = X_REVISED_END_DATE,
609 ACTUAL_START_DATE = X_ACTUAL_START_DATE,
610 ACTUAL_END_DATE = X_ACTUAL_END_DATE,
611 RECOMMENDED_SHELF_LIFE = X_RECOMMENDED_SHELF_LIFE,
612 ATTRIBUTE26 = X_ATTRIBUTE26,
613 ATTRIBUTE27 = X_ATTRIBUTE27,
614 ATTRIBUTE28 = X_ATTRIBUTE28,
615 ATTRIBUTE1 = X_ATTRIBUTE1,
616 ATTRIBUTE2 = X_ATTRIBUTE2,
617 ATTRIBUTE3 = X_ATTRIBUTE3,
618 ATTRIBUTE4 = X_ATTRIBUTE4,
619 ATTRIBUTE5 = X_ATTRIBUTE5,
620 ATTRIBUTE6 = X_ATTRIBUTE6,
621 ATTRIBUTE7 = X_ATTRIBUTE7,
622 ATTRIBUTE8 = X_ATTRIBUTE8,
623 ATTRIBUTE9 = X_ATTRIBUTE9,
624 ATTRIBUTE10 = X_ATTRIBUTE10,
625 ATTRIBUTE11 = X_ATTRIBUTE11,
626 ATTRIBUTE12 = X_ATTRIBUTE12,
627 ATTRIBUTE13 = X_ATTRIBUTE13,
628 ATTRIBUTE14 = X_ATTRIBUTE14,
629 ATTRIBUTE15 = X_ATTRIBUTE15,
630 ATTRIBUTE16 = X_ATTRIBUTE16,
631 ATTRIBUTE17 = X_ATTRIBUTE17,
632 ATTRIBUTE18 = X_ATTRIBUTE18,
633 ATTRIBUTE19 = X_ATTRIBUTE19,
634 ATTRIBUTE20 = X_ATTRIBUTE20,
635 ATTRIBUTE21 = X_ATTRIBUTE21,
636 ATTRIBUTE22 = X_ATTRIBUTE22,
637 ATTRIBUTE23 = X_ATTRIBUTE23,
638 ATTRIBUTE24 = X_ATTRIBUTE24,
639 NOTIFICATION_LEAD_TIME = X_NOTIFICATION_LEAD_TIME,
640 NOTIFICATION_LEAD_TIME_UNIT = X_NOTIFICATION_LEAD_TIME_UNIT,
641 TESTING_GRACE_PERIOD = X_TESTING_GRACE_PERIOD,
642 TESTING_GRACE_PERIOD_UNIT = X_TESTING_GRACE_PERIOD_UNIT,
643 --ORGN_CODE = X_ORGN_CODE, -- INVCONV
644 SS_NO = X_SS_NO,
645 STATUS = X_STATUS,
646 STORAGE_PLAN_ID = X_STORAGE_PLAN_ID,
647 --ITEM_ID = X_ITEM_ID, -- INVCONV
648 BASE_SPEC_ID = X_BASE_SPEC_ID,
649 --QC_LAB_ORGN_CODE = X_QC_LAB_ORGN_CODE, -- INVCONV
650 OWNER = X_OWNER,
651 MATERIAL_SOURCES_CNT = X_MATERIAL_SOURCES_CNT,
652 STORAGE_CONDITIONS_CNT = X_STORAGE_CONDITIONS_CNT,
653 PACKAGES_CNT = X_PACKAGES_CNT,
654 ORGANIZATION_ID = X_ORGANIZATION_ID, -- INVCONV
655 INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID, -- INVCONV
656 REVISION = X_REVISION, -- INVCONV
657 LAB_ORGANIZATION_ID = X_LAB_ORGANIZATION_ID, -- INVCONV
658 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
659 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
660 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
661 where SS_ID = X_SS_ID;
662
663 if (sql%notfound) then
664 raise no_data_found;
665 end if;
666
667 update GMD_STABILITY_STUDIES_TL set
668 DESCRIPTION = X_DESCRIPTION,
669 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
670 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
671 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
672 SOURCE_LANG = userenv('LANG')
673 where SS_ID = X_SS_ID
674 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
675
676 if (sql%notfound) then
677 raise no_data_found;
678 end if;
679 end UPDATE_ROW;
680
681 procedure DELETE_ROW (
682 X_SS_ID in NUMBER
683 ) is
684 begin
685 delete from GMD_STABILITY_STUDIES_TL
686 where SS_ID = X_SS_ID;
687
688 if (sql%notfound) then
689 raise no_data_found;
690 end if;
691
692 delete from GMD_STABILITY_STUDIES_B
693 where SS_ID = X_SS_ID;
694
695 if (sql%notfound) then
696 raise no_data_found;
697 end if;
698 end DELETE_ROW;
699
700 procedure ADD_LANGUAGE
701 is
702 begin
703 delete from GMD_STABILITY_STUDIES_TL T
704 where not exists
705 (select NULL
706 from GMD_STABILITY_STUDIES_B B
707 where B.SS_ID = T.SS_ID
708 );
709
710 update GMD_STABILITY_STUDIES_TL T set (
711 DESCRIPTION
712 ) = (select
713 B.DESCRIPTION
714 from GMD_STABILITY_STUDIES_TL B
715 where B.SS_ID = T.SS_ID
716 and B.LANGUAGE = T.SOURCE_LANG)
717 where (
718 T.SS_ID,
719 T.LANGUAGE
720 ) in (select
721 SUBT.SS_ID,
722 SUBT.LANGUAGE
723 from GMD_STABILITY_STUDIES_TL SUBB, GMD_STABILITY_STUDIES_TL SUBT
724 where SUBB.SS_ID = SUBT.SS_ID
725 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
726 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
727 ));
728
729 insert into GMD_STABILITY_STUDIES_TL (
730 SS_ID,
731 DESCRIPTION,
732 CREATION_DATE,
733 CREATED_BY,
734 LAST_UPDATED_BY,
735 LAST_UPDATE_DATE,
736 LAST_UPDATE_LOGIN,
737 LANGUAGE,
738 SOURCE_LANG
739 ) select
740 B.SS_ID,
741 B.DESCRIPTION,
742 B.CREATION_DATE,
743 B.CREATED_BY,
744 B.LAST_UPDATED_BY,
745 B.LAST_UPDATE_DATE,
746 B.LAST_UPDATE_LOGIN,
747 L.LANGUAGE_CODE,
748 B.SOURCE_LANG
749 from GMD_STABILITY_STUDIES_TL B, FND_LANGUAGES L
750 where L.INSTALLED_FLAG in ('I', 'B')
751 and B.LANGUAGE = userenv('LANG')
752 and not exists
753 (select NULL
754 from GMD_STABILITY_STUDIES_TL T
755 where T.SS_ID = B.SS_ID
756 and T.LANGUAGE = L.LANGUAGE_CODE);
757 end ADD_LANGUAGE;
758
759 end GMD_STABILITY_STUDIES_PVT;