[Home] [Help]
PACKAGE BODY: APPS.GMD_TEST_METHODS_PVT
Source
1 PACKAGE BODY GMD_TEST_METHODS_PVT as
2 /* $Header: GMDVMTDB.pls 120.1 2006/06/16 11:28:51 rlnagara noship $ */
3
4 PROCEDURE TRANSLATE_ROW
5 (
6 X_TEST_METHOD_ID IN NUMBER,
7 X_TEST_METHOD_DESC IN VARCHAR2,
8 X_OWNER IN VARCHAR2
9 ) IS
10 BEGIN
11 UPDATE GMD_TEST_METHODS_TL SET
12 TEST_METHOD_ID = X_TEST_METHOD_ID,
13 TEST_METHOD_DESC = X_TEST_METHOD_DESC,
14 LAST_UPDATE_DATE = sysdate,
15 LAST_UPDATED_BY = Decode(X_OWNER, 'SEED', 1, 0),
16 LAST_UPDATE_LOGIN = 0,
17 SOURCE_LANG = userenv('LANG')
18 WHERE TEST_METHOD_ID = X_TEST_METHOD_ID AND
19 userenv('LANG') IN (language, source_lang);
20 END TRANSLATE_ROW;
21
22 PROCEDURE LOAD_ROW
23 (
24 X_TEST_METHOD_ID IN NUMBER,
25 X_TEST_METHOD_CODE IN VARCHAR2,
26 X_TEST_METHOD_DESC IN VARCHAR2,
27 X_TEST_QTY IN NUMBER,
28 X_TEST_QTY_UOM IN VARCHAR2,
29 X_DELETE_MARK IN NUMBER,
30 X_DISPLAY_PRECISION IN NUMBER,
31 X_TEST_DURATION IN NUMBER,
32 X_DAYS IN NUMBER,
33 X_HOURS IN NUMBER,
34 X_MINUTES IN NUMBER,
35 X_SECONDS IN NUMBER,
36 X_TEST_REPLICATE IN NUMBER,
37 X_RESOURCES IN VARCHAR2,
38 X_TEST_KIT_ORGANIZATION_ID IN NUMBER,
39 X_TEST_KIT_INV_ITEM_ID IN NUMBER,
40 X_TEXT_CODE IN NUMBER,
41 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
42 X_ATTRIBUTE1 IN VARCHAR2,
43 X_ATTRIBUTE2 IN VARCHAR2,
44 X_ATTRIBUTE3 IN VARCHAR2,
45 X_ATTRIBUTE4 IN VARCHAR2,
46 X_ATTRIBUTE5 IN VARCHAR2,
47 X_ATTRIBUTE6 IN VARCHAR2,
48 X_ATTRIBUTE7 IN VARCHAR2,
49 X_ATTRIBUTE8 IN VARCHAR2,
50 X_ATTRIBUTE9 IN VARCHAR2,
51 X_ATTRIBUTE10 IN VARCHAR2,
52 X_ATTRIBUTE11 IN VARCHAR2,
53 X_ATTRIBUTE12 IN VARCHAR2,
54 X_ATTRIBUTE13 IN VARCHAR2,
55 X_ATTRIBUTE14 IN VARCHAR2,
56 X_ATTRIBUTE15 IN VARCHAR2,
57 X_ATTRIBUTE16 IN VARCHAR2,
58 X_ATTRIBUTE17 IN VARCHAR2,
59 X_ATTRIBUTE18 IN VARCHAR2,
60 X_ATTRIBUTE19 IN VARCHAR2,
61 X_ATTRIBUTE20 IN VARCHAR2,
62 X_ATTRIBUTE21 IN VARCHAR2,
63 X_ATTRIBUTE22 IN VARCHAR2,
64 X_ATTRIBUTE23 IN VARCHAR2,
65 X_ATTRIBUTE24 IN VARCHAR2,
66 X_ATTRIBUTE25 IN VARCHAR2,
67 X_ATTRIBUTE26 IN VARCHAR2,
68 X_ATTRIBUTE27 IN VARCHAR2,
69 X_ATTRIBUTE28 IN VARCHAR2,
70 X_ATTRIBUTE29 IN VARCHAR2,
71 X_ATTRIBUTE30 IN VARCHAR2,
72 X_OWNER IN VARCHAR2
73 )IS
74 cursor c1 is select * from GMD_TEST_METHODS_B where (TEST_METHOD_ID=X_TEST_METHOD_ID);
75 l_pkpresent c1%rowtype;
76 l_user_id NUMBER := 0;
77 l_sysdate DATE;
78 l_test_method_id NUMBER;
79
80 BEGIN
81 IF (x_owner = 'SEED') THEN
82 l_user_id := 1;
83 END IF;
84 select sysdate into l_sysdate from dual;
85
86 OPEN c1;
87 fetch c1 into l_pkpresent;
88 IF (c1%found) THEN
89
90 UPDATE GMD_TEST_METHODS_B SET
91 TEST_METHOD_CODE = X_TEST_METHOD_CODE,
92 TEST_QTY = X_TEST_QTY,
93 TEST_QTY_UOM = X_TEST_QTY_UOM,
94 DELETE_MARK = X_DELETE_MARK ,
95 DISPLAY_PRECISION = X_DISPLAY_PRECISION,
96 TEST_DURATION = X_TEST_DURATION,
97 DAYS = X_DAYS,
98 HOURS = X_HOURS ,
99 MINUTES = X_MINUTES ,
100 SECONDS = X_SECONDS ,
101 TEST_REPLICATE = X_TEST_REPLICATE,
102 RESOURCES = X_RESOURCES,
103 TEST_KIT_ORGANIZATION_ID = X_TEST_KIT_ORGANIZATION_ID,
104 TEST_KIT_INV_ITEM_ID = X_TEST_KIT_INV_ITEM_ID,
105 TEXT_CODE = X_TEXT_CODE,
106 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
107 ATTRIBUTE1 = X_ATTRIBUTE1,
108 ATTRIBUTE2 = X_ATTRIBUTE2,
109 ATTRIBUTE3 = X_ATTRIBUTE3,
110 ATTRIBUTE4 = X_ATTRIBUTE4,
111 ATTRIBUTE5 = X_ATTRIBUTE5,
112 ATTRIBUTE6 = X_ATTRIBUTE6,
113 ATTRIBUTE7 = X_ATTRIBUTE7,
114 ATTRIBUTE8 = X_ATTRIBUTE8,
115 ATTRIBUTE9 = X_ATTRIBUTE9,
116 ATTRIBUTE10 = X_ATTRIBUTE10,
117 ATTRIBUTE11 = X_ATTRIBUTE11,
118 ATTRIBUTE12 = X_ATTRIBUTE12,
119 ATTRIBUTE13 = X_ATTRIBUTE13,
120 ATTRIBUTE14 = X_ATTRIBUTE14,
121 ATTRIBUTE15 = X_ATTRIBUTE15,
122 ATTRIBUTE16 = X_ATTRIBUTE16,
123 ATTRIBUTE17 = X_ATTRIBUTE17,
124 ATTRIBUTE18 = X_ATTRIBUTE18,
125 ATTRIBUTE19 = X_ATTRIBUTE19,
126 ATTRIBUTE20 = X_ATTRIBUTE20,
127 ATTRIBUTE21 = X_ATTRIBUTE21,
128 ATTRIBUTE22 = X_ATTRIBUTE22,
129 ATTRIBUTE23 = X_ATTRIBUTE23,
130 ATTRIBUTE24 = X_ATTRIBUTE24,
131 ATTRIBUTE25 = X_ATTRIBUTE25,
132 ATTRIBUTE26 = X_ATTRIBUTE26,
133 ATTRIBUTE27 = X_ATTRIBUTE27,
134 ATTRIBUTE28 = X_ATTRIBUTE28,
135 ATTRIBUTE29 = X_ATTRIBUTE29,
136 ATTRIBUTE30 = X_ATTRIBUTE30,
137 LAST_UPDATE_DATE = l_sysdate,
138 LAST_UPDATED_BY = l_user_id,
139 LAST_UPDATE_LOGIN = 0
140 WHERE TEST_METHOD_ID = X_TEST_METHOD_ID;
141
142 UPDATE GMD_TEST_METHODS_TL SET
143 TEST_METHOD_DESC = X_TEST_METHOD_DESC,
144 LAST_UPDATE_DATE = l_sysdate,
145 LAST_UPDATED_BY = l_user_id,
146 LAST_UPDATE_LOGIN = 0,
147 SOURCE_LANG = userenv('LANG')
148 WHERE TEST_METHOD_ID = X_TEST_METHOD_ID and
149 userenv('LANG') in (LANGUAGE,SOURCE_LANG);
150
151 ELSIF (c1%notfound) then
152
153 INSERT INTO GMD_TEST_METHODS_B(
154 TEST_METHOD_ID,
155 TEST_METHOD_CODE,
156 TEST_QTY,
157 TEST_QTY_UOM,
158 DELETE_MARK,
159 DISPLAY_PRECISION,
160 TEST_DURATION,
161 DAYS,
162 HOURS,
163 MINUTES,
164 SECONDS,
165 TEST_REPLICATE,
166 RESOURCES,
167 TEST_KIT_ORGANIZATION_ID,
168 TEST_KIT_INV_ITEM_ID,
169 TEXT_CODE,
170 ATTRIBUTE_CATEGORY,
171 ATTRIBUTE1,
172 ATTRIBUTE2,
173 ATTRIBUTE3,
174 ATTRIBUTE4,
175 ATTRIBUTE5,
176 ATTRIBUTE6,
177 ATTRIBUTE7,
178 ATTRIBUTE8,
179 ATTRIBUTE9,
180 ATTRIBUTE10,
181 ATTRIBUTE11,
182 ATTRIBUTE12,
183 ATTRIBUTE13,
184 ATTRIBUTE14,
185 ATTRIBUTE15,
186 ATTRIBUTE16,
187 ATTRIBUTE17,
188 ATTRIBUTE18,
189 ATTRIBUTE19,
190 ATTRIBUTE20,
191 ATTRIBUTE21,
192 ATTRIBUTE22,
193 ATTRIBUTE23,
194 ATTRIBUTE24,
195 ATTRIBUTE25,
196 ATTRIBUTE26,
197 ATTRIBUTE27,
198 ATTRIBUTE28,
199 ATTRIBUTE29,
200 ATTRIBUTE30,
201 CREATION_DATE,
202 CREATED_BY,
203 LAST_UPDATE_DATE,
204 LAST_UPDATED_BY,
205 LAST_UPDATE_LOGIN)
206 VALUES(
207 X_TEST_METHOD_ID,
208 X_TEST_METHOD_CODE,
209 X_TEST_QTY,
210 X_TEST_QTY_UOM,
211 X_DELETE_MARK,
212 X_DISPLAY_PRECISION,
213 X_TEST_DURATION,
214 X_DAYS,
215 X_HOURS,
216 X_MINUTES,
217 X_SECONDS,
218 X_TEST_REPLICATE,
219 X_RESOURCES,
220 X_TEST_KIT_ORGANIZATION_ID,
221 X_TEST_KIT_INV_ITEM_ID,
222 X_TEXT_CODE,
223 X_ATTRIBUTE_CATEGORY,
224 X_ATTRIBUTE1,
225 X_ATTRIBUTE2,
226 X_ATTRIBUTE3,
227 X_ATTRIBUTE4,
228 X_ATTRIBUTE5,
229 X_ATTRIBUTE6,
230 X_ATTRIBUTE7,
231 X_ATTRIBUTE8,
232 X_ATTRIBUTE9,
233 X_ATTRIBUTE10,
234 X_ATTRIBUTE11,
235 X_ATTRIBUTE12,
236 X_ATTRIBUTE13,
237 X_ATTRIBUTE14,
238 X_ATTRIBUTE15,
239 X_ATTRIBUTE16,
240 X_ATTRIBUTE17,
241 X_ATTRIBUTE18,
242 X_ATTRIBUTE19,
243 X_ATTRIBUTE20,
244 X_ATTRIBUTE21,
245 X_ATTRIBUTE22,
246 X_ATTRIBUTE23,
247 X_ATTRIBUTE24,
248 X_ATTRIBUTE25,
249 X_ATTRIBUTE26,
250 X_ATTRIBUTE27,
251 X_ATTRIBUTE28,
252 X_ATTRIBUTE29,
253 X_ATTRIBUTE30,
254 l_sysdate,
255 l_user_id,
256 l_sysdate,
257 l_user_id,
258 0);
259
260 INSERT into GMD_TEST_METHODS_TL(
261 TEST_METHOD_ID,
262 TEST_METHOD_DESC,
263 CREATION_DATE,
264 CREATED_BY,
265 LAST_UPDATE_DATE,
266 LAST_UPDATED_BY,
267 LAST_UPDATE_LOGIN,
268 LANGUAGE,
269 SOURCE_LANG
270 )SELECT
271 X_TEST_METHOD_ID,
272 X_TEST_METHOD_DESC,
273 l_sysdate,
274 l_user_id,
275 l_sysdate,
276 l_user_id,
277 0,
278 L.LANGUAGE_CODE,
279 userenv('LANG')
280 FROM FND_LANGUAGES L
281 WHERE L.INSTALLED_FLAG in ('I','B') and not exists
282 (select NULL
283 from GMD_TEST_METHODS_TL T
284 where T.TEST_METHOD_ID = X_TEST_METHOD_ID
285 and T.LANGUAGE = L.LANGUAGE_CODE);
286
287 END IF;
288 CLOSE c1;
289 COMMIT;
290 END LOAD_ROW;
291
292
293 procedure INSERT_ROW (
294 X_ROWID in out NOCOPY VARCHAR2,
295 X_TEST_METHOD_ID in NUMBER,
296 X_TEST_METHOD_CODE in VARCHAR2,
297 X_TEST_QTY in NUMBER,
298 X_TEST_QTY_UOM in VARCHAR2,
299 X_DISPLAY_PRECISION in NUMBER,
300 X_TEST_DURATION in NUMBER,
301 X_DAYS in NUMBER,
302 X_HOURS in NUMBER,
303 X_MINUTES in NUMBER,
304 X_SECONDS in NUMBER,
305 X_TEST_REPLICATE in NUMBER,
306 X_DELETE_MARK in NUMBER,
307 X_RESOURCES in VARCHAR2,
308 X_TEST_KIT_ORGANIZATION_ID NUMBER,
309 X_TEST_KIT_INV_ITEM_ID in NUMBER,
310 X_TEXT_CODE in NUMBER,
311 X_ATTRIBUTE_CATEGORY in VARCHAR2,
312 X_ATTRIBUTE1 in VARCHAR2,
313 X_ATTRIBUTE2 in VARCHAR2,
314 X_ATTRIBUTE3 in VARCHAR2,
315 X_ATTRIBUTE4 in VARCHAR2,
316 X_ATTRIBUTE5 in VARCHAR2,
317 X_ATTRIBUTE6 in VARCHAR2,
318 X_ATTRIBUTE7 in VARCHAR2,
319 X_ATTRIBUTE8 in VARCHAR2,
320 X_ATTRIBUTE9 in VARCHAR2,
321 X_ATTRIBUTE10 in VARCHAR2,
322 X_ATTRIBUTE11 in VARCHAR2,
323 X_ATTRIBUTE12 in VARCHAR2,
324 X_ATTRIBUTE13 in VARCHAR2,
325 X_ATTRIBUTE14 in VARCHAR2,
326 X_ATTRIBUTE15 in VARCHAR2,
327 X_ATTRIBUTE16 in VARCHAR2,
328 X_ATTRIBUTE17 in VARCHAR2,
329 X_ATTRIBUTE18 in VARCHAR2,
330 X_ATTRIBUTE19 in VARCHAR2,
331 X_ATTRIBUTE20 in VARCHAR2,
332 X_ATTRIBUTE21 in VARCHAR2,
333 X_ATTRIBUTE22 in VARCHAR2,
334 X_ATTRIBUTE23 in VARCHAR2,
335 X_ATTRIBUTE24 in VARCHAR2,
336 X_ATTRIBUTE25 in VARCHAR2,
337 X_ATTRIBUTE26 in VARCHAR2,
338 X_ATTRIBUTE27 in VARCHAR2,
339 X_ATTRIBUTE28 in VARCHAR2,
340 X_ATTRIBUTE29 in VARCHAR2,
341 X_ATTRIBUTE30 in VARCHAR2,
342 X_TEST_METHOD_DESC in VARCHAR2,
343 X_CREATION_DATE in DATE,
344 X_CREATED_BY in NUMBER,
345 X_LAST_UPDATE_DATE in DATE,
346 X_LAST_UPDATED_BY in NUMBER,
347 X_LAST_UPDATE_LOGIN in NUMBER
348 ) is
349 cursor C is select ROWID from GMD_TEST_METHODS_B
350 where TEST_METHOD_ID = X_TEST_METHOD_ID
351 ;
352 begin
353 insert into GMD_TEST_METHODS_B (
354 TEST_METHOD_ID,
355 TEST_METHOD_CODE,
356 TEST_QTY,
357 TEST_QTY_UOM,
358 DISPLAY_PRECISION,
359 TEST_DURATION,
360 DAYS,
361 HOURS,
362 MINUTES,
363 SECONDS,
364 TEST_REPLICATE,
365 DELETE_MARK,
366 RESOURCES,
367 TEST_KIT_ORGANIZATION_ID,
368 TEST_KIT_INV_ITEM_ID,
369 TEXT_CODE,
370 ATTRIBUTE_CATEGORY,
371 ATTRIBUTE1,
372 ATTRIBUTE2,
373 ATTRIBUTE3,
374 ATTRIBUTE4,
375 ATTRIBUTE5,
376 ATTRIBUTE6,
377 ATTRIBUTE7,
378 ATTRIBUTE8,
379 ATTRIBUTE9,
380 ATTRIBUTE10,
381 ATTRIBUTE11,
382 ATTRIBUTE12,
383 ATTRIBUTE13,
384 ATTRIBUTE14,
385 ATTRIBUTE15,
386 ATTRIBUTE16,
387 ATTRIBUTE17,
388 ATTRIBUTE18,
389 ATTRIBUTE19,
390 ATTRIBUTE20,
391 ATTRIBUTE21,
392 ATTRIBUTE22,
393 ATTRIBUTE23,
394 ATTRIBUTE24,
395 ATTRIBUTE25,
396 ATTRIBUTE26,
397 ATTRIBUTE27,
398 ATTRIBUTE28,
399 ATTRIBUTE29,
400 ATTRIBUTE30,
401 CREATION_DATE,
402 CREATED_BY,
403 LAST_UPDATE_DATE,
404 LAST_UPDATED_BY,
405 LAST_UPDATE_LOGIN
406 ) values (
407 X_TEST_METHOD_ID,
408 X_TEST_METHOD_CODE,
409 X_TEST_QTY,
410 X_TEST_QTY_UOM,
411 X_DISPLAY_PRECISION,
412 X_TEST_DURATION,
413 X_DAYS,
414 X_HOURS,
415 X_MINUTES,
416 X_SECONDS,
417 X_TEST_REPLICATE,
418 X_DELETE_MARK,
419 X_RESOURCES,
420 X_TEST_KIT_ORGANIZATION_ID,
421 X_TEST_KIT_INV_ITEM_ID,
422 X_TEXT_CODE,
423 X_ATTRIBUTE_CATEGORY,
424 X_ATTRIBUTE1,
425 X_ATTRIBUTE2,
426 X_ATTRIBUTE3,
427 X_ATTRIBUTE4,
428 X_ATTRIBUTE5,
429 X_ATTRIBUTE6,
430 X_ATTRIBUTE7,
431 X_ATTRIBUTE8,
432 X_ATTRIBUTE9,
433 X_ATTRIBUTE10,
434 X_ATTRIBUTE11,
435 X_ATTRIBUTE12,
436 X_ATTRIBUTE13,
437 X_ATTRIBUTE14,
438 X_ATTRIBUTE15,
439 X_ATTRIBUTE16,
440 X_ATTRIBUTE17,
441 X_ATTRIBUTE18,
442 X_ATTRIBUTE19,
443 X_ATTRIBUTE20,
444 X_ATTRIBUTE21,
445 X_ATTRIBUTE22,
446 X_ATTRIBUTE23,
447 X_ATTRIBUTE24,
448 X_ATTRIBUTE25,
449 X_ATTRIBUTE26,
450 X_ATTRIBUTE27,
451 X_ATTRIBUTE28,
452 X_ATTRIBUTE29,
453 X_ATTRIBUTE30,
454 X_CREATION_DATE,
455 X_CREATED_BY,
456 X_LAST_UPDATE_DATE,
457 X_LAST_UPDATED_BY,
458 X_LAST_UPDATE_LOGIN
459 );
460
461 insert into GMD_TEST_METHODS_TL (
462 TEST_METHOD_ID,
463 TEST_METHOD_DESC,
464 CREATION_DATE,
465 CREATED_BY,
466 LAST_UPDATED_BY,
467 LAST_UPDATE_DATE,
468 LAST_UPDATE_LOGIN,
469 LANGUAGE,
470 SOURCE_LANG
471 ) select
472 X_TEST_METHOD_ID,
473 X_TEST_METHOD_DESC,
474 X_CREATION_DATE,
475 X_CREATED_BY,
476 X_LAST_UPDATED_BY,
477 X_LAST_UPDATE_DATE,
478 X_LAST_UPDATE_LOGIN,
479 L.LANGUAGE_CODE,
480 userenv('LANG')
481 from FND_LANGUAGES L
482 where L.INSTALLED_FLAG in ('I', 'B')
483 and not exists
484 (select NULL
485 from GMD_TEST_METHODS_TL T
486 where T.TEST_METHOD_ID = X_TEST_METHOD_ID
487 and T.LANGUAGE = L.LANGUAGE_CODE);
488
489 open c;
490 fetch c into X_ROWID;
491 if (c%notfound) then
492 close c;
493 raise no_data_found;
494 end if;
495 close c;
496
497 end INSERT_ROW;
498
499 procedure LOCK_ROW (
500 X_TEST_METHOD_ID in NUMBER,
501 X_TEST_METHOD_CODE in VARCHAR2,
502 X_TEST_QTY in NUMBER,
503 X_TEST_QTY_UOM in VARCHAR2,
504 X_DISPLAY_PRECISION in NUMBER,
505 X_TEST_DURATION in NUMBER,
506 X_DAYS in NUMBER,
507 X_HOURS in NUMBER,
508 X_MINUTES in NUMBER,
509 X_SECONDS in NUMBER,
510 X_TEST_REPLICATE in NUMBER,
511 X_DELETE_MARK in NUMBER,
512 X_RESOURCES in VARCHAR2,
513 X_TEST_KIT_ORGANIZATION_ID in NUMBER,
514 X_TEST_KIT_INV_ITEM_ID in NUMBER,
515 X_TEXT_CODE in NUMBER,
516 X_ATTRIBUTE_CATEGORY in VARCHAR2,
517 X_ATTRIBUTE1 in VARCHAR2,
518 X_ATTRIBUTE2 in VARCHAR2,
519 X_ATTRIBUTE3 in VARCHAR2,
520 X_ATTRIBUTE4 in VARCHAR2,
521 X_ATTRIBUTE5 in VARCHAR2,
522 X_ATTRIBUTE6 in VARCHAR2,
523 X_ATTRIBUTE7 in VARCHAR2,
524 X_ATTRIBUTE8 in VARCHAR2,
525 X_ATTRIBUTE9 in VARCHAR2,
526 X_ATTRIBUTE10 in VARCHAR2,
527 X_ATTRIBUTE11 in VARCHAR2,
528 X_ATTRIBUTE12 in VARCHAR2,
529 X_ATTRIBUTE13 in VARCHAR2,
530 X_ATTRIBUTE14 in VARCHAR2,
531 X_ATTRIBUTE15 in VARCHAR2,
532 X_ATTRIBUTE16 in VARCHAR2,
533 X_ATTRIBUTE17 in VARCHAR2,
534 X_ATTRIBUTE18 in VARCHAR2,
535 X_ATTRIBUTE19 in VARCHAR2,
536 X_ATTRIBUTE20 in VARCHAR2,
537 X_ATTRIBUTE21 in VARCHAR2,
538 X_ATTRIBUTE22 in VARCHAR2,
539 X_ATTRIBUTE23 in VARCHAR2,
540 X_ATTRIBUTE24 in VARCHAR2,
541 X_ATTRIBUTE25 in VARCHAR2,
542 X_ATTRIBUTE26 in VARCHAR2,
543 X_ATTRIBUTE27 in VARCHAR2,
544 X_ATTRIBUTE28 in VARCHAR2,
545 X_ATTRIBUTE29 in VARCHAR2,
546 X_ATTRIBUTE30 in VARCHAR2,
547 X_TEST_METHOD_DESC in VARCHAR2
548 ) is
549 cursor c is select
550 TEST_METHOD_CODE,
551 TEST_QTY,
552 TEST_QTY_UOM,
553 DISPLAY_PRECISION,
554 TEST_DURATION,
555 DAYS,
556 HOURS,
557 MINUTES,
558 SECONDS,
559 TEST_REPLICATE,
560 DELETE_MARK,
561 RESOURCES,
562 TEST_KIT_ORGANIZATION_ID,
563 TEST_KIT_INV_ITEM_ID,
564 TEXT_CODE,
565 ATTRIBUTE_CATEGORY,
566 ATTRIBUTE1,
567 ATTRIBUTE2,
568 ATTRIBUTE3,
569 ATTRIBUTE4,
570 ATTRIBUTE5,
571 ATTRIBUTE6,
572 ATTRIBUTE7,
573 ATTRIBUTE8,
574 ATTRIBUTE9,
575 ATTRIBUTE10,
576 ATTRIBUTE11,
577 ATTRIBUTE12,
578 ATTRIBUTE13,
579 ATTRIBUTE14,
580 ATTRIBUTE15,
581 ATTRIBUTE16,
582 ATTRIBUTE17,
583 ATTRIBUTE18,
584 ATTRIBUTE19,
585 ATTRIBUTE20,
586 ATTRIBUTE21,
587 ATTRIBUTE22,
588 ATTRIBUTE23,
589 ATTRIBUTE24,
590 ATTRIBUTE25,
591 ATTRIBUTE26,
592 ATTRIBUTE27,
593 ATTRIBUTE28,
594 ATTRIBUTE29,
595 ATTRIBUTE30
596 from GMD_TEST_METHODS_B
597 where TEST_METHOD_ID = X_TEST_METHOD_ID
598 for update of TEST_METHOD_ID nowait;
599 recinfo c%rowtype;
600
601 cursor c1 is select
602 TEST_METHOD_DESC,
603 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
604 from GMD_TEST_METHODS_TL
605 where TEST_METHOD_ID = X_TEST_METHOD_ID
606 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
607 for update of TEST_METHOD_ID nowait;
608 begin
609 open c;
610 fetch c into recinfo;
611 if (c%notfound) then
612 close c;
613 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
614 app_exception.raise_exception;
615 end if;
616 close c;
617 if ( (recinfo.TEST_METHOD_CODE = X_TEST_METHOD_CODE)
618 AND ((recinfo.TEST_QTY = X_TEST_QTY)
619 OR ((recinfo.TEST_QTY is null) AND (X_TEST_QTY is null)))
620 AND ((recinfo.TEST_QTY_UOM = X_TEST_QTY_UOM)
621 OR ((recinfo.TEST_QTY_UOM is null) AND (X_TEST_QTY_UOM is null)))
622 AND ((recinfo.DISPLAY_PRECISION = X_DISPLAY_PRECISION)
623 OR ((recinfo.DISPLAY_PRECISION is null) AND (X_DISPLAY_PRECISION is null)))
624 AND ((recinfo.TEST_DURATION = X_TEST_DURATION)
625 OR ((recinfo.TEST_DURATION is null) AND (X_TEST_DURATION is null)))
626 AND ((recinfo.DAYS = X_DAYS)
627 OR ((recinfo.DAYS is null) AND (X_DAYS is null)))
628 AND ((recinfo.HOURS = X_HOURS)
629 OR ((recinfo.HOURS is null) AND (X_HOURS is null)))
630 AND ((recinfo.MINUTES = X_MINUTES)
631 OR ((recinfo.MINUTES is null) AND (X_MINUTES is null)))
632 AND ((recinfo.SECONDS = X_SECONDS)
633 OR ((recinfo.SECONDS is null) AND (X_SECONDS is null)))
634 AND (recinfo.TEST_REPLICATE = X_TEST_REPLICATE)
635 AND (recinfo.DELETE_MARK = X_DELETE_MARK)
636 AND ((recinfo.RESOURCES = X_RESOURCES)
637 OR ((recinfo.RESOURCES is null) AND (X_RESOURCES is null)))
638 AND ((recinfo.TEST_KIT_ORGANIZATION_ID = X_TEST_KIT_ORGANIZATION_ID)
639 OR ((recinfo.TEST_KIT_ORGANIZATION_ID is null) AND (X_TEST_KIT_ORGANIZATION_ID is null)))
640 AND ((recinfo.TEST_KIT_INV_ITEM_ID = X_TEST_KIT_INV_ITEM_ID)
641 OR ((recinfo.TEST_KIT_INV_ITEM_ID is null) AND (X_TEST_KIT_INV_ITEM_ID is null)))
642 AND ((recinfo.TEXT_CODE = X_TEXT_CODE)
643 OR ((recinfo.TEXT_CODE is null) AND (X_TEXT_CODE is null)))
644 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
645 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
646 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
647 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
648 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
649 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
650 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
651 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
652 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
653 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
654 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
655 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
656 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
657 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
658 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
659 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
660 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
661 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
662 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
663 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
664 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
665 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
666 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
667 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
668 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
669 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
670 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
671 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
672 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
673 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
674 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
675 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
676 AND ((recinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
677 OR ((recinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
678 AND ((recinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
679 OR ((recinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
680 AND ((recinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
681 OR ((recinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
682 AND ((recinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
683 OR ((recinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
684 AND ((recinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
685 OR ((recinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
686 AND ((recinfo.ATTRIBUTE21 = X_ATTRIBUTE21)
687 OR ((recinfo.ATTRIBUTE21 is null) AND (X_ATTRIBUTE21 is null)))
688 AND ((recinfo.ATTRIBUTE22 = X_ATTRIBUTE22)
689 OR ((recinfo.ATTRIBUTE22 is null) AND (X_ATTRIBUTE22 is null)))
690 AND ((recinfo.ATTRIBUTE23 = X_ATTRIBUTE23)
691 OR ((recinfo.ATTRIBUTE23 is null) AND (X_ATTRIBUTE23 is null)))
692 AND ((recinfo.ATTRIBUTE24 = X_ATTRIBUTE24)
693 OR ((recinfo.ATTRIBUTE24 is null) AND (X_ATTRIBUTE24 is null)))
694 AND ((recinfo.ATTRIBUTE25 = X_ATTRIBUTE25)
695 OR ((recinfo.ATTRIBUTE25 is null) AND (X_ATTRIBUTE25 is null)))
696 AND ((recinfo.ATTRIBUTE26 = X_ATTRIBUTE26)
697 OR ((recinfo.ATTRIBUTE26 is null) AND (X_ATTRIBUTE26 is null)))
698 AND ((recinfo.ATTRIBUTE27 = X_ATTRIBUTE27)
699 OR ((recinfo.ATTRIBUTE27 is null) AND (X_ATTRIBUTE27 is null)))
700 AND ((recinfo.ATTRIBUTE28 = X_ATTRIBUTE28)
701 OR ((recinfo.ATTRIBUTE28 is null) AND (X_ATTRIBUTE28 is null)))
702 AND ((recinfo.ATTRIBUTE29 = X_ATTRIBUTE29)
703 OR ((recinfo.ATTRIBUTE29 is null) AND (X_ATTRIBUTE29 is null)))
704 AND ((recinfo.ATTRIBUTE30 = X_ATTRIBUTE30)
705 OR ((recinfo.ATTRIBUTE30 is null) AND (X_ATTRIBUTE30 is null)))
706 ) then
707 null;
708 else
709 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
710 app_exception.raise_exception;
711 end if;
712
713 for tlinfo in c1 loop
714 if (tlinfo.BASELANG = 'Y') then
715 if ( (tlinfo.TEST_METHOD_DESC = X_TEST_METHOD_DESC)
716 ) then
717 null;
718 else
719 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
720 app_exception.raise_exception;
721 end if;
722 end if;
723 end loop;
724 return;
725 end LOCK_ROW;
726
727 procedure UPDATE_ROW (
728 X_TEST_METHOD_ID in NUMBER,
729 X_TEST_METHOD_CODE in VARCHAR2,
730 X_TEST_QTY in NUMBER,
731 X_TEST_QTY_UOM in VARCHAR2,
732 X_DISPLAY_PRECISION in NUMBER,
733 X_TEST_DURATION in NUMBER,
734 X_DAYS in NUMBER,
735 X_HOURS in NUMBER,
736 X_MINUTES in NUMBER,
737 X_SECONDS in NUMBER,
738 X_TEST_REPLICATE in NUMBER,
739 X_DELETE_MARK in NUMBER,
740 X_RESOURCES in VARCHAR2,
741 X_TEST_KIT_ORGANIZATION_ID NUMBER,
742 X_TEST_KIT_INV_ITEM_ID in NUMBER,
743 X_TEXT_CODE in NUMBER,
744 X_ATTRIBUTE_CATEGORY in VARCHAR2,
745 X_ATTRIBUTE1 in VARCHAR2,
746 X_ATTRIBUTE2 in VARCHAR2,
747 X_ATTRIBUTE3 in VARCHAR2,
748 X_ATTRIBUTE4 in VARCHAR2,
749 X_ATTRIBUTE5 in VARCHAR2,
750 X_ATTRIBUTE6 in VARCHAR2,
751 X_ATTRIBUTE7 in VARCHAR2,
752 X_ATTRIBUTE8 in VARCHAR2,
753 X_ATTRIBUTE9 in VARCHAR2,
754 X_ATTRIBUTE10 in VARCHAR2,
755 X_ATTRIBUTE11 in VARCHAR2,
756 X_ATTRIBUTE12 in VARCHAR2,
757 X_ATTRIBUTE13 in VARCHAR2,
758 X_ATTRIBUTE14 in VARCHAR2,
759 X_ATTRIBUTE15 in VARCHAR2,
760 X_ATTRIBUTE16 in VARCHAR2,
761 X_ATTRIBUTE17 in VARCHAR2,
762 X_ATTRIBUTE18 in VARCHAR2,
763 X_ATTRIBUTE19 in VARCHAR2,
764 X_ATTRIBUTE20 in VARCHAR2,
765 X_ATTRIBUTE21 in VARCHAR2,
766 X_ATTRIBUTE22 in VARCHAR2,
767 X_ATTRIBUTE23 in VARCHAR2,
768 X_ATTRIBUTE24 in VARCHAR2,
769 X_ATTRIBUTE25 in VARCHAR2,
770 X_ATTRIBUTE26 in VARCHAR2,
771 X_ATTRIBUTE27 in VARCHAR2,
772 X_ATTRIBUTE28 in VARCHAR2,
773 X_ATTRIBUTE29 in VARCHAR2,
774 X_ATTRIBUTE30 in VARCHAR2,
775 X_TEST_METHOD_DESC in VARCHAR2,
776 X_LAST_UPDATE_DATE in DATE,
777 X_LAST_UPDATED_BY in NUMBER,
778 X_LAST_UPDATE_LOGIN in NUMBER
779 ) is
780 begin
781 update GMD_TEST_METHODS_B set
782 TEST_METHOD_CODE = X_TEST_METHOD_CODE,
783 TEST_QTY = X_TEST_QTY,
784 TEST_QTY_UOM = X_TEST_QTY_UOM,
785 DISPLAY_PRECISION = X_DISPLAY_PRECISION,
786 TEST_DURATION = X_TEST_DURATION,
787 DAYS = X_DAYS,
788 HOURS = X_HOURS,
789 MINUTES = X_MINUTES,
790 SECONDS = X_SECONDS,
791 TEST_REPLICATE = X_TEST_REPLICATE,
792 DELETE_MARK = X_DELETE_MARK,
793 RESOURCES = X_RESOURCES,
794 TEST_KIT_ORGANIZATION_ID = X_TEST_KIT_ORGANIZATION_ID,
795 TEST_KIT_INV_ITEM_ID = X_TEST_KIT_INV_ITEM_ID,
796 TEXT_CODE = X_TEXT_CODE,
797 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
798 ATTRIBUTE1 = X_ATTRIBUTE1,
799 ATTRIBUTE2 = X_ATTRIBUTE2,
800 ATTRIBUTE3 = X_ATTRIBUTE3,
801 ATTRIBUTE4 = X_ATTRIBUTE4,
802 ATTRIBUTE5 = X_ATTRIBUTE5,
803 ATTRIBUTE6 = X_ATTRIBUTE6,
804 ATTRIBUTE7 = X_ATTRIBUTE7,
805 ATTRIBUTE8 = X_ATTRIBUTE8,
806 ATTRIBUTE9 = X_ATTRIBUTE9,
807 ATTRIBUTE10 = X_ATTRIBUTE10,
808 ATTRIBUTE11 = X_ATTRIBUTE11,
809 ATTRIBUTE12 = X_ATTRIBUTE12,
810 ATTRIBUTE13 = X_ATTRIBUTE13,
811 ATTRIBUTE14 = X_ATTRIBUTE14,
812 ATTRIBUTE15 = X_ATTRIBUTE15,
813 ATTRIBUTE16 = X_ATTRIBUTE16,
814 ATTRIBUTE17 = X_ATTRIBUTE17,
815 ATTRIBUTE18 = X_ATTRIBUTE18,
816 ATTRIBUTE19 = X_ATTRIBUTE19,
817 ATTRIBUTE20 = X_ATTRIBUTE20,
818 ATTRIBUTE21 = X_ATTRIBUTE21,
819 ATTRIBUTE22 = X_ATTRIBUTE22,
820 ATTRIBUTE23 = X_ATTRIBUTE23,
821 ATTRIBUTE24 = X_ATTRIBUTE24,
822 ATTRIBUTE25 = X_ATTRIBUTE25,
823 ATTRIBUTE26 = X_ATTRIBUTE26,
824 ATTRIBUTE27 = X_ATTRIBUTE27,
825 ATTRIBUTE28 = X_ATTRIBUTE28,
826 ATTRIBUTE29 = X_ATTRIBUTE29,
827 ATTRIBUTE30 = X_ATTRIBUTE30,
828 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
829 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
830 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
831 where TEST_METHOD_ID = X_TEST_METHOD_ID;
832
833 if (sql%notfound) then
834 raise no_data_found;
835 end if;
836
837 update GMD_TEST_METHODS_TL set
838 TEST_METHOD_DESC = X_TEST_METHOD_DESC,
839 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
840 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
841 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
842 SOURCE_LANG = userenv('LANG')
843 where TEST_METHOD_ID = X_TEST_METHOD_ID
844 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
845
846 if (sql%notfound) then
847 raise no_data_found;
848 end if;
849 end UPDATE_ROW;
850
851 procedure DELETE_ROW (
852 X_TEST_METHOD_ID in NUMBER
853 ) is
854 begin
855 delete from GMD_TEST_METHODS_TL
856 where TEST_METHOD_ID = X_TEST_METHOD_ID;
857
858 if (sql%notfound) then
859 raise no_data_found;
860 end if;
861
862 delete from GMD_TEST_METHODS_B
863 where TEST_METHOD_ID = X_TEST_METHOD_ID;
864
865 if (sql%notfound) then
866 raise no_data_found;
867 end if;
868 end DELETE_ROW;
869
870 procedure ADD_LANGUAGE
871 is
872 begin
873 delete from GMD_TEST_METHODS_TL T
874 where not exists
875 (select NULL
876 from GMD_TEST_METHODS_B B
877 where B.TEST_METHOD_ID = T.TEST_METHOD_ID
878 );
879
880 update GMD_TEST_METHODS_TL T set (
881 TEST_METHOD_DESC
882 ) = (select
883 B.TEST_METHOD_DESC
884 from GMD_TEST_METHODS_TL B
885 where B.TEST_METHOD_ID = T.TEST_METHOD_ID
886 and B.LANGUAGE = T.SOURCE_LANG)
887 where (
888 T.TEST_METHOD_ID,
889 T.LANGUAGE
890 ) in (select
891 SUBT.TEST_METHOD_ID,
892 SUBT.LANGUAGE
893 from GMD_TEST_METHODS_TL SUBB, GMD_TEST_METHODS_TL SUBT
894 where SUBB.TEST_METHOD_ID = SUBT.TEST_METHOD_ID
895 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
896 and (SUBB.TEST_METHOD_DESC <> SUBT.TEST_METHOD_DESC
897 ));
898
899 insert into GMD_TEST_METHODS_TL (
900 TEST_METHOD_ID,
901 TEST_METHOD_DESC,
902 CREATION_DATE,
903 CREATED_BY,
904 LAST_UPDATED_BY,
905 LAST_UPDATE_DATE,
906 LAST_UPDATE_LOGIN,
907 LANGUAGE,
908 SOURCE_LANG
909 ) select
910 B.TEST_METHOD_ID,
911 B.TEST_METHOD_DESC,
912 B.CREATION_DATE,
913 B.CREATED_BY,
914 B.LAST_UPDATED_BY,
915 B.LAST_UPDATE_DATE,
916 B.LAST_UPDATE_LOGIN,
917 L.LANGUAGE_CODE,
918 B.SOURCE_LANG
919 from GMD_TEST_METHODS_TL B, FND_LANGUAGES L
920 where L.INSTALLED_FLAG in ('I', 'B')
921 and B.LANGUAGE = userenv('LANG')
922 and not exists
923 (select NULL
924 from GMD_TEST_METHODS_TL T
925 where T.TEST_METHOD_ID = B.TEST_METHOD_ID
926 and T.LANGUAGE = L.LANGUAGE_CODE);
927 end ADD_LANGUAGE;
928
929
930 FUNCTION fetch_row (p_test_methods IN gmd_test_methods%ROWTYPE,
931 x_test_methods OUT NOCOPY gmd_test_methods%ROWTYPE )
932 RETURN BOOLEAN
933 IS
934 BEGIN
935
936 IF (p_test_methods.test_method_id IS NOT NULL) THEN
937 SELECT *
938 INTO x_test_methods
939 FROM gmd_test_methods
940 WHERE test_method_id = p_test_methods.test_method_id
941 ;
942 ELSIF (p_test_methods.test_method_code IS NOT NULL) THEN
943
944 SELECT *
945 INTO x_test_methods
946 FROM gmd_test_methods
947 WHERE test_method_code = p_test_methods.test_method_code;
948 ELSE
949 gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_test_methods');
950 RETURN FALSE;
951 END IF;
952
953 RETURN TRUE;
954
955 EXCEPTION
956 WHEN NO_DATA_FOUND
957 THEN
958 gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_test_methods');
959 RETURN FALSE;
960 WHEN OTHERS
961 THEN
962 fnd_msg_pub.add_exc_msg ('GMD_test_methods_PVT', 'FETCH_ROW');
963 RETURN FALSE;
964
965 END fetch_row;
966 END gmd_test_methods_pvt;