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