[Home] [Help]
PACKAGE BODY: APPS.GMD_SPECIFICATIONS_PVT
Source
1 PACKAGE BODY GMD_SPECIFICATIONS_PVT AS
2 /* $Header: GMDVSPCB.pls 120.0 2005/05/25 19:41:51 appldev noship $ */
3 PROCEDURE INSERT_ROW (
4 X_ROWID IN OUT NOCOPY VARCHAR2,
5 X_SPEC_ID IN OUT NOCOPY NUMBER,
6 X_SPEC_NAME IN VARCHAR2,
7 X_SPEC_VERS IN NUMBER,
8 X_INVENTORY_ITEM_ID IN NUMBER,
9 X_REVISION IN VARCHAR2,
10 X_GRADE_CODE IN VARCHAR2,
11 X_SPEC_STATUS IN NUMBER,
12 X_OVERLAY_IND IN VARCHAR2,
13 X_SPEC_TYPE IN VARCHAR2,
14 X_BASE_SPEC_ID IN NUMBER,
15 X_OWNER_ORGANIZATION_ID IN NUMBER,
16 X_OWNER_ID IN NUMBER,
17 X_SAMPLE_INV_TRANS_IND IN VARCHAR2,
18 X_DELETE_MARK IN NUMBER,
19 X_TEXT_CODE IN NUMBER,
20 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
21 X_ATTRIBUTE1 IN VARCHAR2,
22 X_ATTRIBUTE2 IN VARCHAR2,
23 X_ATTRIBUTE3 IN VARCHAR2,
24 X_ATTRIBUTE4 IN VARCHAR2,
25 X_ATTRIBUTE5 IN VARCHAR2,
26 X_ATTRIBUTE6 IN VARCHAR2,
27 X_ATTRIBUTE7 IN VARCHAR2,
28 X_ATTRIBUTE8 IN VARCHAR2,
29 X_ATTRIBUTE9 IN VARCHAR2,
30 X_ATTRIBUTE10 IN VARCHAR2,
31 X_ATTRIBUTE11 IN VARCHAR2,
32 X_ATTRIBUTE12 IN VARCHAR2,
33 X_ATTRIBUTE13 IN VARCHAR2,
34 X_ATTRIBUTE14 IN VARCHAR2,
35 X_ATTRIBUTE15 IN VARCHAR2,
36 X_ATTRIBUTE16 IN VARCHAR2,
37 X_ATTRIBUTE17 IN VARCHAR2,
38 X_ATTRIBUTE18 IN VARCHAR2,
39 X_ATTRIBUTE19 IN VARCHAR2,
40 X_ATTRIBUTE20 IN VARCHAR2,
41 X_ATTRIBUTE21 IN VARCHAR2,
42 X_ATTRIBUTE22 IN VARCHAR2,
43 X_ATTRIBUTE23 IN VARCHAR2,
44 X_ATTRIBUTE24 IN VARCHAR2,
45 X_ATTRIBUTE25 IN VARCHAR2,
46 X_ATTRIBUTE26 IN VARCHAR2,
47 X_ATTRIBUTE27 IN VARCHAR2,
48 X_ATTRIBUTE28 IN VARCHAR2,
49 X_ATTRIBUTE29 IN VARCHAR2,
50 X_ATTRIBUTE30 IN VARCHAR2,
51 X_SPEC_DESC IN VARCHAR2,
52 X_CREATION_DATE IN DATE,
53 X_CREATED_BY IN NUMBER,
54 X_LAST_UPDATE_DATE IN DATE,
55 X_LAST_UPDATED_BY IN NUMBER,
56 X_LAST_UPDATE_LOGIN IN NUMBER
57 ) IS
58 CURSOR C IS SELECT ROWID FROM GMD_SPECIFICATIONS_B
59 WHERE SPEC_ID = X_SPEC_ID
60 ;
61 BEGIN
62
63 IF X_SPEC_ID IS NULL THEN
64 SELECT GMD_QC_SPEC_ID_S.NEXTVAL INTO X_SPEC_ID FROM DUAL;
65 END IF;
66
67 INSERT INTO GMD_SPECIFICATIONS_B (
68 SPEC_ID,
69 SPEC_NAME,
70 SPEC_VERS,
71 INVENTORY_ITEM_ID,
72 REVISION,
73 GRADE_CODE,
74 SPEC_STATUS,
75 OVERLAY_IND,
76 SPEC_TYPE,
77 BASE_SPEC_ID,
78 OWNER_ORGANIZATION_ID,
79 OWNER_ID,
80 SAMPLE_INV_TRANS_IND,
81 DELETE_MARK,
82 TEXT_CODE,
83 ATTRIBUTE_CATEGORY,
84 ATTRIBUTE1,
85 ATTRIBUTE2,
86 ATTRIBUTE3,
87 ATTRIBUTE4,
88 ATTRIBUTE5,
89 ATTRIBUTE6,
90 ATTRIBUTE7,
91 ATTRIBUTE8,
92 ATTRIBUTE9,
93 ATTRIBUTE10,
94 ATTRIBUTE11,
95 ATTRIBUTE12,
96 ATTRIBUTE13,
97 ATTRIBUTE14,
98 ATTRIBUTE15,
99 ATTRIBUTE16,
100 ATTRIBUTE17,
101 ATTRIBUTE18,
102 ATTRIBUTE19,
103 ATTRIBUTE20,
104 ATTRIBUTE21,
105 ATTRIBUTE22,
106 ATTRIBUTE23,
107 ATTRIBUTE24,
108 ATTRIBUTE25,
109 ATTRIBUTE26,
110 ATTRIBUTE27,
111 ATTRIBUTE28,
112 ATTRIBUTE29,
113 ATTRIBUTE30,
114 CREATION_DATE,
115 CREATED_BY,
116 LAST_UPDATE_DATE,
117 LAST_UPDATED_BY,
118 LAST_UPDATE_LOGIN
119 ) VALUES (
120 X_SPEC_ID,
121 X_SPEC_NAME,
122 X_SPEC_VERS,
123 X_INVENTORY_ITEM_ID,
124 X_REVISION,
125 X_GRADE_CODE,
126 X_SPEC_STATUS,
127 X_OVERLAY_IND,
128 X_SPEC_TYPE,
129 X_BASE_SPEC_ID,
130 X_OWNER_ORGANIZATION_ID,
131 X_OWNER_ID,
132 X_SAMPLE_INV_TRANS_IND,
133 X_DELETE_MARK,
134 X_TEXT_CODE,
135 X_ATTRIBUTE_CATEGORY,
136 X_ATTRIBUTE1,
137 X_ATTRIBUTE2,
138 X_ATTRIBUTE3,
139 X_ATTRIBUTE4,
140 X_ATTRIBUTE5,
141 X_ATTRIBUTE6,
142 X_ATTRIBUTE7,
143 X_ATTRIBUTE8,
144 X_ATTRIBUTE9,
145 X_ATTRIBUTE10,
146 X_ATTRIBUTE11,
147 X_ATTRIBUTE12,
148 X_ATTRIBUTE13,
149 X_ATTRIBUTE14,
150 X_ATTRIBUTE15,
151 X_ATTRIBUTE16,
152 X_ATTRIBUTE17,
153 X_ATTRIBUTE18,
154 X_ATTRIBUTE19,
155 X_ATTRIBUTE20,
156 X_ATTRIBUTE21,
157 X_ATTRIBUTE22,
158 X_ATTRIBUTE23,
159 X_ATTRIBUTE24,
160 X_ATTRIBUTE25,
161 X_ATTRIBUTE26,
162 X_ATTRIBUTE27,
163 X_ATTRIBUTE28,
164 X_ATTRIBUTE29,
165 X_ATTRIBUTE30,
166 NVL(X_CREATION_DATE,SYSDATE),
167 NVL(X_CREATED_BY,FND_GLOBAL.USER_ID),
168 NVL(X_LAST_UPDATE_DATE,SYSDATE),
169 NVL(X_LAST_UPDATED_BY,FND_GLOBAL.USER_ID),
170 NVL(X_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID)
171 );
172
173 INSERT INTO GMD_SPECIFICATIONS_TL (
174 SPEC_ID,
175 SPEC_DESC,
176 CREATION_DATE,
177 CREATED_BY,
178 LAST_UPDATE_DATE,
179 LAST_UPDATED_BY,
180 LAST_UPDATE_LOGIN,
181 LANGUAGE,
182 SOURCE_LANG
183 ) SELECT
184 X_SPEC_ID,
185 X_SPEC_DESC,
186 NVL(X_CREATION_DATE,SYSDATE),
187 NVL(X_CREATED_BY,FND_GLOBAL.USER_ID),
188 NVL(X_LAST_UPDATE_DATE,SYSDATE),
189 NVL(X_LAST_UPDATED_BY,FND_GLOBAL.USER_ID),
190 NVL(X_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID),
191 L.LANGUAGE_CODE,
192 USERENV('LANG')
193 FROM FND_LANGUAGES L
194 WHERE L.INSTALLED_FLAG IN ('I', 'B')
195 AND NOT EXISTS
196 (SELECT NULL
197 FROM GMD_SPECIFICATIONS_TL T
198 WHERE T.SPEC_ID = X_SPEC_ID
199 AND T.LANGUAGE = L.LANGUAGE_CODE);
200
201 OPEN c;
202 FETCH c INTO X_ROWID;
203 IF (c%NOTFOUND) THEN
204 CLOSE c;
205 RAISE NO_DATA_FOUND;
206 END IF;
207 CLOSE c;
208
209 END INSERT_ROW;
210
211 PROCEDURE LOCK_ROW (
212 X_SPEC_ID IN NUMBER,
213 X_SPEC_NAME IN VARCHAR2,
214 X_SPEC_VERS IN NUMBER,
215 X_INVENTORY_ITEM_ID IN NUMBER,
216 X_REVISION VARCHAR2,
217 X_GRADE_CODE IN VARCHAR2,
218 X_SPEC_STATUS IN NUMBER,
219 X_OVERLAY_IND IN VARCHAR2,
220 X_SPEC_TYPE IN VARCHAR2,
221 X_BASE_SPEC_ID IN NUMBER,
222 X_OWNER_ORGANIZATION_ID IN VARCHAR2,
223 X_OWNER_ID IN NUMBER,
224 X_SAMPLE_INV_TRANS_IND IN VARCHAR2,
225 X_DELETE_MARK IN NUMBER,
226 X_TEXT_CODE IN NUMBER,
227 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
228 X_ATTRIBUTE1 IN VARCHAR2,
229 X_ATTRIBUTE2 IN VARCHAR2,
230 X_ATTRIBUTE3 IN VARCHAR2,
231 X_ATTRIBUTE4 IN VARCHAR2,
232 X_ATTRIBUTE5 IN VARCHAR2,
233 X_ATTRIBUTE6 IN VARCHAR2,
234 X_ATTRIBUTE7 IN VARCHAR2,
235 X_ATTRIBUTE8 IN VARCHAR2,
236 X_ATTRIBUTE9 IN VARCHAR2,
237 X_ATTRIBUTE10 IN VARCHAR2,
238 X_ATTRIBUTE11 IN VARCHAR2,
239 X_ATTRIBUTE12 IN VARCHAR2,
240 X_ATTRIBUTE13 IN VARCHAR2,
241 X_ATTRIBUTE14 IN VARCHAR2,
242 X_ATTRIBUTE15 IN VARCHAR2,
243 X_ATTRIBUTE16 IN VARCHAR2,
244 X_ATTRIBUTE17 IN VARCHAR2,
245 X_ATTRIBUTE18 IN VARCHAR2,
246 X_ATTRIBUTE19 IN VARCHAR2,
247 X_ATTRIBUTE20 IN VARCHAR2,
248 X_ATTRIBUTE21 IN VARCHAR2,
249 X_ATTRIBUTE22 IN VARCHAR2,
250 X_ATTRIBUTE23 IN VARCHAR2,
251 X_ATTRIBUTE24 IN VARCHAR2,
252 X_ATTRIBUTE25 IN VARCHAR2,
253 X_ATTRIBUTE26 IN VARCHAR2,
254 X_ATTRIBUTE27 IN VARCHAR2,
255 X_ATTRIBUTE28 IN VARCHAR2,
256 X_ATTRIBUTE29 IN VARCHAR2,
257 X_ATTRIBUTE30 IN VARCHAR2,
258 X_SPEC_DESC IN VARCHAR2
259 ) IS
260 CURSOR c IS SELECT
261 SPEC_NAME,
262 SPEC_VERS,
263 INVENTORY_ITEM_ID,
264 REVISION,
265 GRADE_CODE,
266 SPEC_STATUS,
267 OVERLAY_IND,
268 SPEC_TYPE,
269 BASE_SPEC_ID,
270 OWNER_ORGANIZATION_ID,
271 OWNER_ID,
272 SAMPLE_INV_TRANS_IND,
273 DELETE_MARK,
274 TEXT_CODE,
275 ATTRIBUTE_CATEGORY,
276 ATTRIBUTE1,
277 ATTRIBUTE2,
278 ATTRIBUTE3,
279 ATTRIBUTE4,
280 ATTRIBUTE5,
281 ATTRIBUTE6,
282 ATTRIBUTE7,
283 ATTRIBUTE8,
284 ATTRIBUTE9,
285 ATTRIBUTE10,
286 ATTRIBUTE11,
287 ATTRIBUTE12,
288 ATTRIBUTE13,
289 ATTRIBUTE14,
290 ATTRIBUTE15,
291 ATTRIBUTE16,
292 ATTRIBUTE17,
293 ATTRIBUTE18,
294 ATTRIBUTE19,
295 ATTRIBUTE20,
296 ATTRIBUTE21,
297 ATTRIBUTE22,
298 ATTRIBUTE23,
299 ATTRIBUTE24,
300 ATTRIBUTE25,
301 ATTRIBUTE26,
302 ATTRIBUTE27,
303 ATTRIBUTE28,
304 ATTRIBUTE29,
305 ATTRIBUTE30
306 FROM GMD_SPECIFICATIONS_B
307 WHERE SPEC_ID = X_SPEC_ID
308 FOR UPDATE OF SPEC_ID NOWAIT;
309 recinfo c%ROWTYPE;
310
311 CURSOR c1 IS SELECT
312 SPEC_DESC,
313 DECODE(LANGUAGE, USERENV('LANG'), 'Y', 'N') BASELANG
314 FROM GMD_SPECIFICATIONS_TL
315 WHERE SPEC_ID = X_SPEC_ID
316 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
317 FOR UPDATE OF SPEC_ID NOWAIT;
318 BEGIN
319 OPEN c;
320 FETCH c INTO recinfo;
321 IF (c%NOTFOUND) THEN
322 CLOSE c;
323 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
324 app_exception.raise_exception;
325 END IF;
326 CLOSE c;
327 IF ( (recinfo.SPEC_NAME = X_SPEC_NAME)
328 AND (recinfo.SPEC_VERS = X_SPEC_VERS)
329 AND ((recinfo.INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID)
330 OR ((recinfo.INVENTORY_ITEM_ID IS NULL) AND (X_INVENTORY_ITEM_ID IS NULL)))
331 AND ((recinfo.REVISION = X_REVISION)
332 OR ((recinfo.REVISION IS NULL) AND (X_REVISION IS NULL)))
333 AND ((recinfo.GRADE_CODE = X_GRADE_CODE)
334 OR ((recinfo.GRADE_CODE IS NULL) AND (X_GRADE_CODE IS NULL)))
335 AND (recinfo.SPEC_STATUS = X_SPEC_STATUS)
336 AND ((recinfo.OVERLAY_IND = X_OVERLAY_IND)
337 OR ((recinfo.OVERLAY_IND IS NULL) AND (X_OVERLAY_IND IS NULL)))
338 AND (recinfo.SPEC_TYPE = X_SPEC_TYPE)
339 AND ((recinfo.BASE_SPEC_ID = X_BASE_SPEC_ID)
340 OR ((recinfo.BASE_SPEC_ID IS NULL) AND (X_BASE_SPEC_ID IS NULL)))
341 AND (recinfo.OWNER_ORGANIZATION_ID = X_OWNER_ORGANIZATION_ID)
342 AND (recinfo.OWNER_ID = X_OWNER_ID)
343 AND ((recinfo.SAMPLE_INV_TRANS_IND = X_SAMPLE_INV_TRANS_IND)
344 OR ((recinfo.SAMPLE_INV_TRANS_IND IS NULL) AND (X_SAMPLE_INV_TRANS_IND IS NULL)))
345 AND (recinfo.DELETE_MARK = X_DELETE_MARK)
346 AND ((recinfo.TEXT_CODE = X_TEXT_CODE)
347 OR ((recinfo.TEXT_CODE IS NULL) AND (X_TEXT_CODE IS NULL)))
348 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
349 OR ((recinfo.ATTRIBUTE_CATEGORY IS NULL) AND (X_ATTRIBUTE_CATEGORY IS NULL)))
350 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
351 OR ((recinfo.ATTRIBUTE1 IS NULL) AND (X_ATTRIBUTE1 IS NULL)))
352 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
353 OR ((recinfo.ATTRIBUTE2 IS NULL) AND (X_ATTRIBUTE2 IS NULL)))
354 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
355 OR ((recinfo.ATTRIBUTE3 IS NULL) AND (X_ATTRIBUTE3 IS NULL)))
356 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
357 OR ((recinfo.ATTRIBUTE4 IS NULL) AND (X_ATTRIBUTE4 IS NULL)))
358 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
359 OR ((recinfo.ATTRIBUTE5 IS NULL) AND (X_ATTRIBUTE5 IS NULL)))
360 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
361 OR ((recinfo.ATTRIBUTE6 IS NULL) AND (X_ATTRIBUTE6 IS NULL)))
362 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
363 OR ((recinfo.ATTRIBUTE7 IS NULL) AND (X_ATTRIBUTE7 IS NULL)))
364 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
365 OR ((recinfo.ATTRIBUTE8 IS NULL) AND (X_ATTRIBUTE8 IS NULL)))
366 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
367 OR ((recinfo.ATTRIBUTE9 IS NULL) AND (X_ATTRIBUTE9 IS NULL)))
368 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
369 OR ((recinfo.ATTRIBUTE10 IS NULL) AND (X_ATTRIBUTE10 IS NULL)))
370 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
371 OR ((recinfo.ATTRIBUTE11 IS NULL) AND (X_ATTRIBUTE11 IS NULL)))
372 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
373 OR ((recinfo.ATTRIBUTE12 IS NULL) AND (X_ATTRIBUTE12 IS NULL)))
374 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
375 OR ((recinfo.ATTRIBUTE13 IS NULL) AND (X_ATTRIBUTE13 IS NULL)))
376 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
377 OR ((recinfo.ATTRIBUTE14 IS NULL) AND (X_ATTRIBUTE14 IS NULL)))
378 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
379 OR ((recinfo.ATTRIBUTE15 IS NULL) AND (X_ATTRIBUTE15 IS NULL)))
380 AND ((recinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
381 OR ((recinfo.ATTRIBUTE16 IS NULL) AND (X_ATTRIBUTE16 IS NULL)))
382 AND ((recinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
383 OR ((recinfo.ATTRIBUTE17 IS NULL) AND (X_ATTRIBUTE17 IS NULL)))
384 AND ((recinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
385 OR ((recinfo.ATTRIBUTE18 IS NULL) AND (X_ATTRIBUTE18 IS NULL)))
386 AND ((recinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
387 OR ((recinfo.ATTRIBUTE19 IS NULL) AND (X_ATTRIBUTE19 IS NULL)))
388 AND ((recinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
389 OR ((recinfo.ATTRIBUTE20 IS NULL) AND (X_ATTRIBUTE20 IS NULL)))
390 AND ((recinfo.ATTRIBUTE21 = X_ATTRIBUTE21)
391 OR ((recinfo.ATTRIBUTE21 IS NULL) AND (X_ATTRIBUTE21 IS NULL)))
392 AND ((recinfo.ATTRIBUTE22 = X_ATTRIBUTE22)
393 OR ((recinfo.ATTRIBUTE22 IS NULL) AND (X_ATTRIBUTE22 IS NULL)))
394 AND ((recinfo.ATTRIBUTE23 = X_ATTRIBUTE23)
395 OR ((recinfo.ATTRIBUTE23 IS NULL) AND (X_ATTRIBUTE23 IS NULL)))
396 AND ((recinfo.ATTRIBUTE24 = X_ATTRIBUTE24)
397 OR ((recinfo.ATTRIBUTE24 IS NULL) AND (X_ATTRIBUTE24 IS NULL)))
398 AND ((recinfo.ATTRIBUTE25 = X_ATTRIBUTE25)
399 OR ((recinfo.ATTRIBUTE25 IS NULL) AND (X_ATTRIBUTE25 IS NULL)))
400 AND ((recinfo.ATTRIBUTE26 = X_ATTRIBUTE26)
401 OR ((recinfo.ATTRIBUTE26 IS NULL) AND (X_ATTRIBUTE26 IS NULL)))
402 AND ((recinfo.ATTRIBUTE27 = X_ATTRIBUTE27)
403 OR ((recinfo.ATTRIBUTE27 IS NULL) AND (X_ATTRIBUTE27 IS NULL)))
404 AND ((recinfo.ATTRIBUTE28 = X_ATTRIBUTE28)
405 OR ((recinfo.ATTRIBUTE28 IS NULL) AND (X_ATTRIBUTE28 IS NULL)))
406 AND ((recinfo.ATTRIBUTE29 = X_ATTRIBUTE29)
407 OR ((recinfo.ATTRIBUTE29 IS NULL) AND (X_ATTRIBUTE29 IS NULL)))
408 AND ((recinfo.ATTRIBUTE30 = X_ATTRIBUTE30)
409 OR ((recinfo.ATTRIBUTE30 IS NULL) AND (X_ATTRIBUTE30 IS NULL)))
410 ) THEN
411 NULL;
412 ELSE
413 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
414 app_exception.raise_exception;
415 END IF;
416
417 FOR tlinfo IN c1 LOOP
418 IF (tlinfo.BASELANG = 'Y') THEN
419 IF ( (tlinfo.SPEC_DESC = X_SPEC_DESC)
420 ) THEN
421 NULL;
422 ELSE
423 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
424 app_exception.raise_exception;
425 END IF;
426 END IF;
427 END LOOP;
428 RETURN;
429 END LOCK_ROW;
430
431 PROCEDURE UPDATE_ROW (
432 X_SPEC_ID IN NUMBER,
433 X_SPEC_NAME IN VARCHAR2,
434 X_SPEC_VERS IN NUMBER,
435 X_INVENTORY_ITEM_ID IN NUMBER,
436 X_REVISION VARCHAR2,
437 X_GRADE_CODE IN VARCHAR2,
438 X_SPEC_STATUS IN NUMBER,
439 X_OVERLAY_IND IN VARCHAR2,
440 X_SPEC_TYPE IN VARCHAR2,
441 X_BASE_SPEC_ID IN NUMBER,
442 X_OWNER_ORGANIZATION_ID IN VARCHAR2,
443 X_OWNER_ID IN NUMBER,
444 X_SAMPLE_INV_TRANS_IND IN VARCHAR2,
445 X_DELETE_MARK IN NUMBER,
446 X_TEXT_CODE IN NUMBER,
447 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
448 X_ATTRIBUTE1 IN VARCHAR2,
449 X_ATTRIBUTE2 IN VARCHAR2,
450 X_ATTRIBUTE3 IN VARCHAR2,
451 X_ATTRIBUTE4 IN VARCHAR2,
452 X_ATTRIBUTE5 IN VARCHAR2,
453 X_ATTRIBUTE6 IN VARCHAR2,
454 X_ATTRIBUTE7 IN VARCHAR2,
455 X_ATTRIBUTE8 IN VARCHAR2,
456 X_ATTRIBUTE9 IN VARCHAR2,
457 X_ATTRIBUTE10 IN VARCHAR2,
458 X_ATTRIBUTE11 IN VARCHAR2,
459 X_ATTRIBUTE12 IN VARCHAR2,
460 X_ATTRIBUTE13 IN VARCHAR2,
461 X_ATTRIBUTE14 IN VARCHAR2,
462 X_ATTRIBUTE15 IN VARCHAR2,
463 X_ATTRIBUTE16 IN VARCHAR2,
464 X_ATTRIBUTE17 IN VARCHAR2,
465 X_ATTRIBUTE18 IN VARCHAR2,
466 X_ATTRIBUTE19 IN VARCHAR2,
467 X_ATTRIBUTE20 IN VARCHAR2,
468 X_ATTRIBUTE21 IN VARCHAR2,
469 X_ATTRIBUTE22 IN VARCHAR2,
470 X_ATTRIBUTE23 IN VARCHAR2,
471 X_ATTRIBUTE24 IN VARCHAR2,
472 X_ATTRIBUTE25 IN VARCHAR2,
473 X_ATTRIBUTE26 IN VARCHAR2,
474 X_ATTRIBUTE27 IN VARCHAR2,
475 X_ATTRIBUTE28 IN VARCHAR2,
476 X_ATTRIBUTE29 IN VARCHAR2,
477 X_ATTRIBUTE30 IN VARCHAR2,
478 X_SPEC_DESC IN VARCHAR2,
479 X_LAST_UPDATE_DATE IN DATE,
480 X_LAST_UPDATED_BY IN NUMBER,
481 X_LAST_UPDATE_LOGIN IN NUMBER
482 ) IS
483 BEGIN
484 UPDATE GMD_SPECIFICATIONS_B SET
485 SPEC_NAME = X_SPEC_NAME,
486 SPEC_VERS = X_SPEC_VERS,
487 INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
488 REVISION = X_REVISION,
489 GRADE_CODE = X_GRADE_CODE,
490 SPEC_STATUS = X_SPEC_STATUS,
491 OVERLAY_IND = X_OVERLAY_IND,
492 SPEC_TYPE = X_SPEC_TYPE,
493 BASE_SPEC_ID = X_BASE_SPEC_ID,
494 OWNER_ORGANIZATION_ID = X_OWNER_ORGANIZATION_ID,
495 OWNER_ID = X_OWNER_ID,
496 SAMPLE_INV_TRANS_IND = X_SAMPLE_INV_TRANS_IND,
497 DELETE_MARK = X_DELETE_MARK,
498 TEXT_CODE = X_TEXT_CODE,
499 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
500 ATTRIBUTE1 = X_ATTRIBUTE1,
501 ATTRIBUTE2 = X_ATTRIBUTE2,
502 ATTRIBUTE3 = X_ATTRIBUTE3,
503 ATTRIBUTE4 = X_ATTRIBUTE4,
504 ATTRIBUTE5 = X_ATTRIBUTE5,
505 ATTRIBUTE6 = X_ATTRIBUTE6,
506 ATTRIBUTE7 = X_ATTRIBUTE7,
507 ATTRIBUTE8 = X_ATTRIBUTE8,
508 ATTRIBUTE9 = X_ATTRIBUTE9,
509 ATTRIBUTE10 = X_ATTRIBUTE10,
510 ATTRIBUTE11 = X_ATTRIBUTE11,
511 ATTRIBUTE12 = X_ATTRIBUTE12,
512 ATTRIBUTE13 = X_ATTRIBUTE13,
513 ATTRIBUTE14 = X_ATTRIBUTE14,
514 ATTRIBUTE15 = X_ATTRIBUTE15,
515 ATTRIBUTE16 = X_ATTRIBUTE16,
516 ATTRIBUTE17 = X_ATTRIBUTE17,
517 ATTRIBUTE18 = X_ATTRIBUTE18,
518 ATTRIBUTE19 = X_ATTRIBUTE19,
519 ATTRIBUTE20 = X_ATTRIBUTE20,
520 ATTRIBUTE21 = X_ATTRIBUTE21,
521 ATTRIBUTE22 = X_ATTRIBUTE22,
522 ATTRIBUTE23 = X_ATTRIBUTE23,
523 ATTRIBUTE24 = X_ATTRIBUTE24,
524 ATTRIBUTE25 = X_ATTRIBUTE25,
525 ATTRIBUTE26 = X_ATTRIBUTE26,
526 ATTRIBUTE27 = X_ATTRIBUTE27,
527 ATTRIBUTE28 = X_ATTRIBUTE28,
528 ATTRIBUTE29 = X_ATTRIBUTE29,
529 ATTRIBUTE30 = X_ATTRIBUTE30,
530 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,SYSDATE),
531 LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY,FND_GLOBAL.USER_ID),
532 LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID)
533 WHERE SPEC_ID = X_SPEC_ID;
534
535 IF (SQL%NOTFOUND) THEN
536 RAISE NO_DATA_FOUND;
537 END IF;
538
539 UPDATE GMD_SPECIFICATIONS_TL SET
540 SPEC_DESC = X_SPEC_DESC,
541 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,SYSDATE),
542 LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY,FND_GLOBAL.USER_ID),
543 LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID),
544 SOURCE_LANG = USERENV('LANG')
545 WHERE SPEC_ID = X_SPEC_ID
546 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
547
548 IF (SQL%NOTFOUND) THEN
549 RAISE NO_DATA_FOUND;
550 END IF;
551 END UPDATE_ROW;
552
553 PROCEDURE ADD_LANGUAGE
554 IS
555 BEGIN
556 DELETE FROM GMD_SPECIFICATIONS_TL T
557 WHERE NOT EXISTS
558 (SELECT NULL
559 FROM GMD_SPECIFICATIONS_B B
560 WHERE B.SPEC_ID = T.SPEC_ID
561 );
562
563 UPDATE GMD_SPECIFICATIONS_TL T SET (
564 SPEC_DESC
565 ) = (SELECT
566 B.SPEC_DESC
567 FROM GMD_SPECIFICATIONS_TL B
568 WHERE B.SPEC_ID = T.SPEC_ID
569 AND B.LANGUAGE = T.SOURCE_LANG)
570 WHERE (
571 T.SPEC_ID,
572 T.LANGUAGE
573 ) IN (SELECT
574 SUBT.SPEC_ID,
575 SUBT.LANGUAGE
576 FROM GMD_SPECIFICATIONS_TL SUBB, GMD_SPECIFICATIONS_TL SUBT
577 WHERE SUBB.SPEC_ID = SUBT.SPEC_ID
578 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
579 AND (SUBB.SPEC_DESC <> SUBT.SPEC_DESC
580 ));
581
582 INSERT INTO GMD_SPECIFICATIONS_TL (
583 SPEC_ID,
584 SPEC_DESC,
585 CREATION_DATE,
586 CREATED_BY,
587 LAST_UPDATED_BY,
588 LAST_UPDATE_DATE,
589 LAST_UPDATE_LOGIN,
590 LANGUAGE,
591 SOURCE_LANG
592 ) SELECT
593 B.SPEC_ID,
594 B.SPEC_DESC,
595 B.CREATION_DATE,
596 B.CREATED_BY,
597 B.LAST_UPDATED_BY,
598 B.LAST_UPDATE_DATE,
599 B.LAST_UPDATE_LOGIN,
600 L.LANGUAGE_CODE,
601 B.SOURCE_LANG
602 FROM GMD_SPECIFICATIONS_TL B, FND_LANGUAGES L
603 WHERE L.INSTALLED_FLAG IN ('I', 'B')
604 AND B.LANGUAGE = USERENV('LANG')
605 AND NOT EXISTS
606 (SELECT NULL
607 FROM GMD_SPECIFICATIONS_TL T
608 WHERE T.SPEC_ID = B.SPEC_ID
609 AND T.LANGUAGE = L.LANGUAGE_CODE);
610 END ADD_LANGUAGE;
611
612 FUNCTION fetch_row (
613 p_specifications IN gmd_specifications%ROWTYPE
614 , x_specifications OUT NOCOPY gmd_specifications%ROWTYPE
615 )
616 RETURN BOOLEAN
617 IS
618 BEGIN
619
620 IF (p_specifications.spec_id IS NOT NULL) THEN
621 SELECT *
622 INTO x_specifications
623 FROM gmd_specifications
624 WHERE spec_id = p_specifications.spec_id
625 ;
626 ELSIF ((p_specifications.spec_name IS NOT NULL) AND
627 (p_specifications.spec_vers IS NOT NULL)) THEN
628 SELECT *
629 INTO x_specifications
630 FROM gmd_specifications
631 WHERE spec_name = p_specifications.spec_name
632 AND spec_vers = p_specifications.spec_vers
633 ;
634 ELSE
635 gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_SPECIFICATIONS');
636 RETURN FALSE;
637 END IF;
638
639 RETURN TRUE;
640
641 EXCEPTION
642 WHEN NO_DATA_FOUND
643 THEN
644 gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_SPECIFICATIONS');
645 RETURN FALSE;
646 WHEN OTHERS
647 THEN
648 fnd_msg_pub.add_exc_msg ('GMD_SPECIFICATIONS_PVT', 'FETCH_ROW');
649 RETURN FALSE;
650
651 END fetch_row;
652
653 FUNCTION lock_row (
654 p_spec_id IN NUMBER ,
655 p_spec_name IN VARCHAR2 ,
656 p_spec_vers IN NUMBER
657 )
658 RETURN BOOLEAN
659 IS
660 dummy NUMBER;
661 BEGIN
662
663 IF (p_spec_id IS NOT NULL) THEN
664 SELECT spec_id
665 INTO dummy
666 FROM gmd_specifications_b
667 WHERE spec_id = p_spec_id
668 FOR UPDATE OF spec_id NOWAIT ;
669 ELSIF (p_spec_name IS NOT NULL and p_spec_vers IS NOT NULL) THEN
670 SELECT spec_id
671 INTO dummy
672 FROM gmd_specifications_b
673 WHERE spec_name = p_spec_name and spec_vers = p_spec_vers
674 FOR UPDATE OF spec_id NOWAIT ;
675 ELSE
676 gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_SPECIFICATIONS');
677 RETURN FALSE;
678 END IF;
679
680 RETURN TRUE;
681
682 EXCEPTION
683 WHEN NO_DATA_FOUND
684 THEN
685 gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_SPECIFICATIONS');
686 RETURN FALSE;
687 WHEN OTHERS
688 THEN
689 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPECIFICATIONS_PVT.LOCK_ROW','ERROR',SUBSTR(SQLERRM,1,100),'POSITION','010');
690 RETURN FALSE;
691 END lock_row;
692
693 FUNCTION mark_for_delete (
694 p_spec_id IN NUMBER ,
695 p_spec_name IN VARCHAR2 ,
696 p_spec_vers IN NUMBER ,
697 p_last_update_date IN DATE ,
698 p_last_updated_by IN NUMBER ,
699 p_last_update_login IN NUMBER
700 )
701 RETURN BOOLEAN
702 IS
703
704 BEGIN
705
706 IF (p_spec_id IS NOT NULL) THEN
707 UPDATE gmd_specifications_b
708 SET delete_mark = 1,
709 last_update_date = NVL(p_last_update_date,SYSDATE),
710 last_updated_by = NVL(p_last_updated_by,FND_GLOBAL.USER_ID),
711 last_update_login = NVL(p_last_update_login,FND_GLOBAL.LOGIN_ID)
712 WHERE spec_id = p_spec_id ;
713 IF SQL%NOTFOUND THEN
714 RAISE NO_DATA_FOUND ;
715 END IF;
716 ELSIF (p_spec_name IS NOT NULL and p_spec_vers IS NOT NULL) THEN
717 UPDATE gmd_specifications_b
718 SET delete_mark = 1,
719 last_update_date = NVL(p_last_update_date,SYSDATE),
720 last_updated_by = NVL(p_last_updated_by,FND_GLOBAL.USER_ID),
721 last_update_login = NVL(p_last_update_login,FND_GLOBAL.LOGIN_ID)
722 WHERE spec_name = p_spec_name and spec_vers = p_spec_vers ;
723 IF SQL%NOTFOUND THEN
724 RAISE NO_DATA_FOUND ;
725 END IF;
726 ELSE
727 gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_SPECIFICATIONS');
728 RETURN FALSE;
729 END IF;
730
731 RETURN TRUE;
732
733 EXCEPTION
734 WHEN NO_DATA_FOUND
735 THEN
736 gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_SPECIFICATIONS');
737 RETURN FALSE;
738 WHEN OTHERS
739 THEN
740 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPECIFICATIONS_PVT.MARK_FOR_DELETE','ERROR',SUBSTR(SQLERRM,1,100),'POSITION','010');
741 RETURN FALSE;
742 END mark_for_delete;
743
744 FUNCTION INSERT_ROW(p_spec IN OUT NOCOPY GMD_SPECIFICATIONS%ROWTYPE)
745 RETURN BOOLEAN IS
746 l_rowid ROWID ;
747 BEGIN
748
749 GMD_Specifications_PVT.INSERT_ROW(
750 X_ROWID => l_rowid,
751 X_SPEC_ID => p_spec.spec_id,
752 X_SPEC_NAME => p_spec.spec_name,
753 X_SPEC_VERS => p_spec.spec_vers,
754 X_INVENTORY_ITEM_ID => p_spec.inventory_item_id,
755 X_REVISION => p_spec.revision,
756 X_GRADE_CODE => p_spec.grade_code,
757 X_SPEC_STATUS => p_spec.spec_status,
758 X_OVERLAY_IND => p_spec.overlay_ind,
759 X_SPEC_TYPE => p_spec.spec_type,
760 X_BASE_SPEC_ID => p_spec.base_spec_id,
761 X_OWNER_ORGANIZATION_ID => p_spec.owner_organization_id,
762 X_OWNER_ID => p_spec.owner_id,
763 X_SAMPLE_INV_TRANS_IND => p_spec.sample_inv_trans_ind,
764 X_DELETE_MARK => p_spec.delete_mark,
765 X_TEXT_CODE => p_spec.text_code,
766 X_ATTRIBUTE_CATEGORY => p_spec.attribute_category,
767 X_ATTRIBUTE1 => p_spec.attribute1,
768 X_ATTRIBUTE2 => p_spec.attribute2,
769 X_ATTRIBUTE3 => p_spec.attribute3,
770 X_ATTRIBUTE4 => p_spec.attribute4,
771 X_ATTRIBUTE5 => p_spec.attribute5,
772 X_ATTRIBUTE6 => p_spec.attribute6,
773 X_ATTRIBUTE7 => p_spec.attribute7,
774 X_ATTRIBUTE8 => p_spec.attribute8,
775 X_ATTRIBUTE9 => p_spec.attribute9,
776 X_ATTRIBUTE10 => p_spec.attribute10,
777 X_ATTRIBUTE11 => p_spec.attribute11,
778 X_ATTRIBUTE12 => p_spec.attribute12,
779 X_ATTRIBUTE13 => p_spec.attribute13,
780 X_ATTRIBUTE14 => p_spec.attribute14,
781 X_ATTRIBUTE15 => p_spec.attribute15,
782 X_ATTRIBUTE16 => p_spec.attribute16,
783 X_ATTRIBUTE17 => p_spec.attribute17,
784 X_ATTRIBUTE18 => p_spec.attribute18,
785 X_ATTRIBUTE19 => p_spec.attribute19,
786 X_ATTRIBUTE20 => p_spec.attribute20,
787 X_ATTRIBUTE21 => p_spec.attribute21,
788 X_ATTRIBUTE22 => p_spec.attribute22,
789 X_ATTRIBUTE23 => p_spec.attribute23,
790 X_ATTRIBUTE24 => p_spec.attribute24,
791 X_ATTRIBUTE25 => p_spec.attribute25,
792 X_ATTRIBUTE26 => p_spec.attribute26,
793 X_ATTRIBUTE27 => p_spec.attribute27,
794 X_ATTRIBUTE28 => p_spec.attribute28,
795 X_ATTRIBUTE29 => p_spec.attribute29,
796 X_ATTRIBUTE30 => p_spec.attribute30,
797 X_SPEC_DESC => p_spec.spec_desc,
798 X_CREATION_DATE => p_spec.creation_date,
799 X_CREATED_BY => p_spec.created_by,
800 X_LAST_UPDATE_DATE => p_spec.last_update_date,
801 X_LAST_UPDATED_BY => p_spec.last_updated_by,
802 X_LAST_UPDATE_LOGIN => p_spec.last_update_login);
803
804 return TRUE;
805
806 EXCEPTION WHEN OTHERS THEN
807 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPECIFICATIONS_PVT.INSERT_ROW','ERROR',
808 SUBSTR(SQLERRM,1,100),'POSITION','010');
809 RETURN FALSE;
810
811 END INSERT_ROW;
812 END Gmd_Specifications_Pvt;