1 PACKAGE BODY GMD_QCR01USR_XMLP_PKG AS
2 /* $Header: QCR01USRB.pls 120.0 2007/12/24 13:05:02 krreddy noship $ */
3 FUNCTION DATE1CFFORMULA RETURN VARCHAR2 IS
4 BEGIN
5 /*SRW.REFERENCE(DATE1CP)*/NULL;
6 IF FROM_RESULT_DATE IS NOT NULL AND TO_RESULT_DATE IS NOT NULL THEN
7 DATE1CP := ' and trunc(gr1.result_date) >= FND_DATE.CANONICAL_TO_DATE(''' || TO_CHAR(FROM_RESULT_DATE
8 ,'yyyy/mm/dd hh24:mi:ss') || ''') and trunc(gr1.result_date) <= FND_DATE.CANONICAL_TO_DATE(''' || TO_CHAR(TO_RESULT_DATE
9 ,'yyyy/mm/dd hh24:mi:ss') || '''' || ')';
10 ELSIF FROM_RESULT_DATE IS NULL AND TO_RESULT_DATE IS NULL THEN
11 DATE1CP := ' ';
12 ELSIF FROM_RESULT_DATE IS NOT NULL AND TO_RESULT_DATE IS NULL THEN
13 DATE1CP := ' and trunc(gr1.result_date) >= FND_DATE.CANONICAL_TO_DATE(''' || TO_CHAR(FROM_RESULT_DATE
14 ,'yyyy/mm/dd hh24:mi:ss') || '''' || ')';
15 ELSIF FROM_RESULT_DATE IS NULL AND TO_RESULT_DATE IS NOT NULL THEN
16 DATE1CP := ' and trunc(gr1.result_date) <= FND_DATE.CANONICAL_TO_DATE(''' || TO_CHAR(TO_RESULT_DATE
17 ,'yyyy/mm/dd hh24:mi:ss') || '''' || ')';
18 END IF;
19 RETURN NULL;
20 END DATE1CFFORMULA;
21 FUNCTION FINALCFFORMULA RETURN VARCHAR2 IS
22 BEGIN
23 IF INCLUDE = '0' THEN
24 FINAL1CP := ' ';
25 ELSIF INCLUDE = '1' THEN
26 FINAL1CP := ' and decode(gr1.ad_hoc_print_on_coa_ind, ''Y'', decode( gsr.evaluation_ind,''0A'',''Y'',''1V'',''Y'',''2R'',''Y'',''N''),''N'') =''Y''';
27 END IF;
28 RETURN NULL;
29 END FINALCFFORMULA;
30 FUNCTION QCUNIT_CODECFFORMULA(QC_SPEC_ID IN NUMBER
31 ,ASSAY_CODE IN VARCHAR2
32 ,QCASSY_TYP_ID IN NUMBER) RETURN VARCHAR2 IS
33 QCUNIT VARCHAR2(4);
34 BEGIN
35 IF QC_SPEC_ID IS NULL THEN
36 BEGIN
37 SELECT
38 TEST_UNIT
39 INTO QCUNIT
40 FROM
41 GMD_QC_TESTS_B
42 WHERE TEST_CODE = ASSAY_CODE;
43 EXCEPTION
44 WHEN NO_DATA_FOUND THEN
45 RETURN (NULL);
46 END;
47 ELSE
48 BEGIN
49 SELECT
50 TEST_UOM
51 INTO QCUNIT
52 FROM
53 GMD_SPEC_TESTS_B
54 WHERE SPEC_ID = QC_SPEC_ID
55 AND TEST_ID = QCASSY_TYP_ID;
56 END;
57 RETURN (QCUNIT);
58 END IF;
59 RETURN NULL;
60 EXCEPTION
61 WHEN NO_DATA_FOUND THEN
62 RETURN (NULL);
63 END QCUNIT_CODECFFORMULA;
64 FUNCTION ASSAY_DESCCFFORMULA(ASSAY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
65 ASSAYDESC VARCHAR2(42);
66 BEGIN
67 SELECT
68 '[' || TEST_DESC || ']'
69 INTO ASSAYDESC
70 FROM
71 GMD_QC_TESTS
72 WHERE TEST_CODE = ASSAY_CODE;
73 RETURN (ASSAYDESC);
74 EXCEPTION
75 WHEN OTHERS THEN
76 RETURN NULL;
77 END ASSAY_DESCCFFORMULA;
78 FUNCTION MIN_SPECCFFORMULA(QC_SPEC_ID IN NUMBER
79 ,ASSAY_CODE IN VARCHAR2
80 ,MIN_CHAR IN VARCHAR2
81 ,MIN_SPEC IN NUMBER) RETURN NUMBER IS
82 ACCEPTABLE_RANGE NUMBER(16);
83 BEGIN
84 ACCEPTABLE_RANGE := 0;
85 IF QC_SPEC_ID IS NULL THEN
86 BEGIN
87 SELECT
88 MIN_VALUE_NUM
89 INTO ACCEPTABLE_RANGE
90 FROM
91 GMD_QC_TESTS_B
92 WHERE TEST_CODE = ASSAY_CODE;
93 EXCEPTION
94 WHEN NO_DATA_FOUND THEN
95 RETURN (ACCEPTABLE_RANGE);
96 END;
97 ELSE
98 IF MIN_CHAR IS NULL THEN
99 RETURN (MIN_SPEC);
100 ELSE
101 RETURN (NULL);
102 END IF;
103 END IF;
104 RETURN (ACCEPTABLE_RANGE);
105 END MIN_SPECCFFORMULA;
106 FUNCTION MAX_SPECFORMULA(MAX_CHAR IN VARCHAR2
107 ,QC_SPEC_ID IN NUMBER
108 ,ASSAY_CODE IN VARCHAR2
109 ,MAX_SPEC IN NUMBER) RETURN NUMBER IS
110 ACCEPTABLE_RANGE NUMBER;
111 BEGIN
112 IF MAX_CHAR IS NOT NULL THEN
113 RETURN (NULL);
114 END IF;
115 ACCEPTABLE_RANGE := 0;
116 IF QC_SPEC_ID IS NULL THEN
117 BEGIN
118 SELECT
119 MAX_VALUE_NUM
120 INTO ACCEPTABLE_RANGE
121 FROM
122 GMD_QC_TESTS_B
123 WHERE TEST_CODE = ASSAY_CODE;
124 EXCEPTION
125 WHEN NO_DATA_FOUND THEN
126 RETURN (ACCEPTABLE_RANGE);
127 END;
128 ELSE
129 RETURN (MAX_SPEC);
130 END IF;
131 RETURN (ACCEPTABLE_RANGE);
132 END MAX_SPECFORMULA;
133 FUNCTION FROM_DATECFFORMULA(SAMPLE_NO_1 IN VARCHAR2) RETURN DATE IS
134 FROMDATE DATE;
135 BEGIN
136 SELECT
137 DISTINCT
138 GISV.START_DATE
139 INTO FROMDATE
140 FROM
141 GMD_SAMPLES GS,
142 GMD_SAMPLING_EVENTS GSE,
143 GMD_CUSTOMER_SPEC_VRS GISV,
144 GMD_EVENT_SPEC_DISP GES
145 WHERE SAMPLE_NO = SAMPLE_NO_1
146 AND GS.SAMPLING_EVENT_ID = GSE.SAMPLING_EVENT_ID
147 AND GSE.SAMPLING_EVENT_ID = GES.SAMPLING_EVENT_ID
148 AND GES.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
149 AND GES.SPEC_VR_ID = GISV.SPEC_VR_ID;
150 RETURN (FROMDATE);
151 EXCEPTION
152 WHEN OTHERS THEN
153 RETURN (NULL);
154 END FROM_DATECFFORMULA;
155 FUNCTION TODATEFORMULA(SAMPLE_NO_1 IN VARCHAR2) RETURN DATE IS
156 TODATE DATE;
157 BEGIN
158 SELECT
159 DISTINCT
160 GISV.END_DATE
161 INTO TODATE
162 FROM
163 GMD_SAMPLES GS,
164 GMD_SAMPLING_EVENTS GSE,
165 GMD_CUSTOMER_SPEC_VRS GISV,
166 GMD_EVENT_SPEC_DISP GES
167 WHERE SAMPLE_NO = SAMPLE_NO_1
168 AND GS.SAMPLING_EVENT_ID = GSE.SAMPLING_EVENT_ID
169 AND GSE.SAMPLING_EVENT_ID = GES.SAMPLING_EVENT_ID
170 AND GES.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
171 AND GES.SPEC_VR_ID = GISV.SPEC_VR_ID;
172 RETURN (TODATE);
173 EXCEPTION
174 WHEN OTHERS THEN
175 RETURN (NULL);
176 END TODATEFORMULA;
177 FUNCTION ASSAY_DESC1CFFORMULA(ASSAY_CODE2 IN VARCHAR2) RETURN VARCHAR2 IS
178 ASSAYDESC VARCHAR2(42);
179 BEGIN
180 SELECT
181 '[' || TEST_DESC || ']'
182 INTO ASSAYDESC
183 FROM
184 GMD_QC_TESTS
185 WHERE TEST_CODE = ASSAY_CODE2;
186 RETURN (ASSAYDESC);
187 EXCEPTION
188 WHEN OTHERS THEN
189 RETURN NULL;
190 END ASSAY_DESC1CFFORMULA;
191 FUNCTION QCUNIT_CODE1CFFORMULA(QC_SPEC_ID2 IN NUMBER
192 ,ASSAY_CODE2 IN VARCHAR2
193 ,QCASSY_TYP_ID1 IN NUMBER) RETURN VARCHAR2 IS
194 QCUNIT VARCHAR2(4);
195 BEGIN
196 IF QC_SPEC_ID2 IS NULL THEN
197 BEGIN
198 SELECT
199 TEST_UNIT
200 INTO QCUNIT
201 FROM
202 GMD_QC_TESTS_B
203 WHERE TEST_CODE = ASSAY_CODE2;
204 EXCEPTION
205 WHEN NO_DATA_FOUND THEN
206 RETURN (NULL);
207 END;
208 ELSE
209 BEGIN
210 SELECT
211 TEST_UOM
212 INTO QCUNIT
213 FROM
214 GMD_SPEC_TESTS_B
215 WHERE SPEC_ID = QC_SPEC_ID2
216 AND TEST_ID = QCASSY_TYP_ID1;
217 END;
218 RETURN (QCUNIT);
219 END IF;
220 RETURN NULL;
221 EXCEPTION
222 WHEN NO_DATA_FOUND THEN
223 RETURN (NULL);
224 END QCUNIT_CODE1CFFORMULA;
225 FUNCTION MIN_SPEC1CFFORMULA(QC_SPEC_ID2 IN NUMBER
226 ,ASSAY_CODE2 IN VARCHAR2
227 ,MIN_CHAR1 IN VARCHAR2
228 ,MIN_SPEC1 IN NUMBER) RETURN NUMBER IS
229 ACCEPTABLE_RANGE NUMBER(16);
230 BEGIN
231 ACCEPTABLE_RANGE := 0;
232 IF QC_SPEC_ID2 IS NULL THEN
233 BEGIN
234 SELECT
235 MIN_VALUE_NUM
236 INTO ACCEPTABLE_RANGE
237 FROM
238 GMD_QC_TESTS_B
239 WHERE TEST_CODE = ASSAY_CODE2;
240 EXCEPTION
241 WHEN NO_DATA_FOUND THEN
242 RETURN (ACCEPTABLE_RANGE);
243 END;
244 ELSE
245 IF MIN_CHAR1 IS NULL THEN
246 RETURN (MIN_SPEC1);
247 ELSE
248 RETURN (NULL);
249 END IF;
250 END IF;
251 RETURN (ACCEPTABLE_RANGE);
252 END MIN_SPEC1CFFORMULA;
253 FUNCTION MAX_SPEC1CFFORMULA(MAX_CHAR1 IN VARCHAR2
254 ,QC_SPEC_ID2 IN NUMBER
255 ,ASSAY_CODE2 IN VARCHAR2
256 ,MAX_SPEC1 IN NUMBER) RETURN NUMBER IS
257 ACCEPTABLE_RANGE NUMBER;
258 BEGIN
259 IF MAX_CHAR1 IS NOT NULL THEN
260 RETURN (NULL);
261 END IF;
262 ACCEPTABLE_RANGE := 0;
263 IF QC_SPEC_ID2 IS NULL THEN
264 BEGIN
265 SELECT
266 MAX_VALUE_NUM
267 INTO ACCEPTABLE_RANGE
268 FROM
269 GMD_QC_TESTS_B
270 WHERE TEST_CODE = ASSAY_CODE2;
271 EXCEPTION
272 WHEN NO_DATA_FOUND THEN
273 RETURN (ACCEPTABLE_RANGE);
274 END;
275 ELSE
276 RETURN (MAX_SPEC1);
277 END IF;
278 RETURN (ACCEPTABLE_RANGE);
279 END MAX_SPEC1CFFORMULA;
280 FUNCTION FROM_DATE1CFFORMULA(SAMPLE_NO2 IN VARCHAR2) RETURN DATE IS
281 FROMDATE1 DATE;
282 BEGIN
283 SELECT
284 DISTINCT
285 GISV.START_DATE
286 INTO FROMDATE1
287 FROM
288 GMD_SAMPLES GS,
289 GMD_SAMPLING_EVENTS GSE,
290 GMD_SUPPLIER_SPEC_VRS GISV,
291 GMD_EVENT_SPEC_DISP GES
292 WHERE SAMPLE_NO = SAMPLE_NO2
293 AND GS.SAMPLING_EVENT_ID = GSE.SAMPLING_EVENT_ID
294 AND GSE.SAMPLING_EVENT_ID = GES.SAMPLING_EVENT_ID
295 AND GES.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
296 AND GES.SPEC_VR_ID = GISV.SPEC_VR_ID;
297 RETURN (FROMDATE1);
298 RETURN NULL;
299 EXCEPTION
300 WHEN OTHERS THEN
301 RETURN (NULL);
302 END FROM_DATE1CFFORMULA;
303 FUNCTION TO_DATE1CFFORMULA(SAMPLE_NO2 IN VARCHAR2) RETURN DATE IS
304 TODATE1 DATE;
305 BEGIN
306 SELECT
307 DISTINCT
308 GISV.END_DATE
309 INTO TODATE1
310 FROM
311 GMD_SAMPLES GS,
312 GMD_SAMPLING_EVENTS GSE,
313 GMD_SUPPLIER_SPEC_VRS GISV,
314 GMD_EVENT_SPEC_DISP GES
315 WHERE SAMPLE_NO = SAMPLE_NO2
316 AND GS.SAMPLING_EVENT_ID = GSE.SAMPLING_EVENT_ID
317 AND GSE.SAMPLING_EVENT_ID = GES.SAMPLING_EVENT_ID
318 AND GES.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
319 AND GES.SPEC_VR_ID = GISV.SPEC_VR_ID;
320 RETURN (TODATE1);
321 EXCEPTION
322 WHEN OTHERS THEN
323 RETURN (NULL);
324 END TO_DATE1CFFORMULA;
325 FUNCTION TEXTCFFORMULA(TEXT_SPEC IN VARCHAR2) RETURN VARCHAR2 IS
326 BEGIN
327 IF TEXT_SPEC IS NOT NULL THEN
328 RETURN (TEXT_SPEC);
329 ELSE
330 RETURN (NULL);
331 END IF;
332 END TEXTCFFORMULA;
333 FUNCTION TARGETCFFORMULA(TEXT_SPEC IN VARCHAR2
334 ,TARGET_SPEC IN NUMBER
335 ,TEST_TYPE IN VARCHAR2
336 ,PRECISION IN NUMBER) RETURN CHAR IS
337 L_PRECISION VARCHAR2(25);
338 L_NUMBER NUMBER;
339 L_TARGET_SPEC VARCHAR2(25);
340 BEGIN
341 IF TEXT_SPEC IS NOT NULL THEN
342 RETURN (NULL);
343 ELSE
344 IF TARGET_SPEC IS NULL THEN
345 RETURN (TARGET_SPEC);
346 ELSIF TEST_TYPE in ('N','L','E') THEN
347 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
348 ,PRECISION) - 1);
349 L_NUMBER := TARGET_SPEC;
350 L_TARGET_SPEC := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
351 ,L_PRECISION));
352 RETURN (L_TARGET_SPEC);
353 END IF;
354 END IF;
355 END TARGETCFFORMULA;
356 FUNCTION TEXT1CFFORMULA(TEXT_SPEC1 IN VARCHAR2) RETURN VARCHAR2 IS
357 BEGIN
358 IF TEXT_SPEC1 IS NOT NULL THEN
359 RETURN (TEXT_SPEC1);
360 ELSE
361 RETURN (NULL);
362 END IF;
363 END TEXT1CFFORMULA;
364 FUNCTION TARGET1CFFORMULA(TEXT_SPEC1 IN VARCHAR2
365 ,TARGET_SPEC1 IN NUMBER
366 ,TEST_TYPE1 IN VARCHAR2
367 ,PRECISION1 IN NUMBER) RETURN CHAR IS
368 L_PRECISION VARCHAR2(25);
369 L_NUMBER NUMBER;
370 L_TARGET_SPEC VARCHAR2(25);
371 BEGIN
372 IF TEXT_SPEC1 IS NOT NULL THEN
373 RETURN (NULL);
374 ELSE
375 IF TARGET_SPEC1 IS NULL THEN
376 RETURN (TARGET_SPEC1);
377 ELSIF TEST_TYPE1 in ('N','L','E') THEN
378 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
379 ,PRECISION1) - 1);
380 L_NUMBER := TARGET_SPEC1;
381 L_TARGET_SPEC := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
382 ,L_PRECISION));
383 RETURN (L_TARGET_SPEC);
384 END IF;
385 END IF;
386 END TARGET1CFFORMULA;
387 FUNCTION TEXTTARGETFORMULA(TEXTCF IN VARCHAR2
388 ,TARGETCF IN VARCHAR2) RETURN VARCHAR2 IS
389 TEXTARGET VARCHAR2(16);
390 BEGIN
391 IF TEXTCF IS NOT NULL THEN
392 TEXTARGET := TEXTCF;
393 ELSIF TARGETCF > 0 THEN
394 TEXTARGET := TARGETCF;
395 END IF;
396 RETURN (TEXTARGET);
397 END TEXTTARGETFORMULA;
398 FUNCTION TEXTTARGET1CFFORMULA(TEXT1CF IN VARCHAR2
399 ,TARGET1CF IN VARCHAR2) RETURN VARCHAR2 IS
400 TEXTARGET1 VARCHAR2(16);
401 BEGIN
402 IF TEXT1CF IS NOT NULL THEN
403 TEXTARGET1 := TEXT1CF;
404 ELSIF TARGET1CF > 0 THEN
405 TEXTARGET1 := TARGET1CF;
406 END IF;
407 RETURN (TEXTARGET1);
408 END TEXTTARGET1CFFORMULA;
409 FUNCTION TEXTARRAYCFFORMULA(TEXTCODECF IN NUMBER) RETURN VARCHAR2 IS
410 TEXT1 VARCHAR2(116);
411 TEXT2 VARCHAR2(2000);
412 CURSOR GET_TEXT1 IS
413 SELECT
414 TEXT
415 FROM
416 QC_TEXT_TBL
417 WHERE TEXT_CODE = TEXTCODECF
418 AND LINE_NO <> - 1
419 AND LANG_CODE = 'US';
420 BEGIN
421 OPEN GET_TEXT1;
422 LOOP
423 FETCH GET_TEXT1
424 INTO TEXT1;
425 EXIT WHEN GET_TEXT1%NOTFOUND;
426 TEXT2 := TEXT2 || TEXT1;
427 END LOOP;
428 CLOSE GET_TEXT1;
429 IF SQL%NOTFOUND THEN
430 RETURN (NULL);
431 END IF;
432 RETURN (TEXT2);
433 RETURN NULL;
434 EXCEPTION
435 WHEN NO_DATA_FOUND THEN
436 RETURN (NULL);
437 END TEXTARRAYCFFORMULA;
438 FUNCTION TEXTARRAY1CFFORMULA(TEXTCODE1CF IN NUMBER) RETURN VARCHAR2 IS
439 TEXT2 VARCHAR2(116);
440 TEXT3 VARCHAR2(2000);
441 CURSOR GET_TEXT IS
442 SELECT
443 TEXT
444 FROM
445 QC_TEXT_TBL
449 BEGIN
446 WHERE TEXT_CODE = TEXTCODE1CF
447 AND LINE_NO <> - 1
448 AND LANG_CODE = 'US';
450 OPEN GET_TEXT;
451 LOOP
452 FETCH GET_TEXT
453 INTO TEXT2;
454 EXIT WHEN GET_TEXT%NOTFOUND;
455 TEXT3 := TEXT3 || TEXT2;
456 END LOOP;
457 CLOSE GET_TEXT;
458 IF SQL%NOTFOUND THEN
459 RETURN (NULL);
460 END IF;
461 RETURN (TEXT3);
462 RETURN NULL;
463 EXCEPTION
464 WHEN NO_DATA_FOUND THEN
465 RETURN (NULL);
466 END TEXTARRAY1CFFORMULA;
467 FUNCTION RESULT1CFFORMULA(TEXT_RESULT IN VARCHAR2
468 ,NUM_RESULT IN NUMBER) RETURN VARCHAR2 IS
469 TEXTRSLT VARCHAR2(16);
470 BEGIN
471 IF PRINT_CONDITION = '1' THEN
472 IF TEXT_RESULT IS NOT NULL THEN
473 TEXTRSLT := TEXT_RESULT;
474 ELSIF NUM_RESULT > 0 THEN
475 TEXTRSLT := NUM_RESULT;
476 END IF;
477 END IF;
478 RETURN (TEXTRSLT);
479 END RESULT1CFFORMULA;
480 FUNCTION RESULT2CFFORMULA(TEXT_RESULT2 IN VARCHAR2
481 ,NUM_RESULT2 IN NUMBER) RETURN VARCHAR2 IS
482 TEXTRSLT1 VARCHAR2(16);
483 BEGIN
484 IF PRINT_CONDITION = '1' THEN
485 IF TEXT_RESULT2 IS NOT NULL THEN
486 TEXTRSLT1 := TEXT_RESULT2;
487 ELSIF NUM_RESULT2 > 0 THEN
488 TEXTRSLT1 := NUM_RESULT2;
489 END IF;
490 END IF;
491 RETURN (TEXTRSLT1);
492 END RESULT2CFFORMULA;
493 FUNCTION TEXTARRAY2CFFORMULA(TEXT_CODE_1 IN NUMBER) RETURN VARCHAR2 IS
494 TEXT2 VARCHAR2(116);
495 TEXT3 VARCHAR2(2000);
496 CURSOR GET_TEXT IS
497 SELECT
498 TEXT
499 FROM
500 QC_TEXT_TBL
501 WHERE TEXT_CODE = TEXT_CODE_1
502 AND LINE_NO <> - 1
503 AND LANG_CODE = 'US';
504 BEGIN
505 OPEN GET_TEXT;
506 LOOP
507 FETCH GET_TEXT
508 INTO TEXT2;
509 EXIT WHEN GET_TEXT%NOTFOUND;
510 TEXT3 := TEXT3 || TEXT2;
511 END LOOP;
512 CLOSE GET_TEXT;
513 IF SQL%NOTFOUND THEN
514 RETURN (NULL);
515 END IF;
516 RETURN (TEXT3);
517 RETURN NULL;
518 EXCEPTION
519 WHEN NO_DATA_FOUND THEN
520 RETURN (NULL);
521 END TEXTARRAY2CFFORMULA;
522 FUNCTION TEXTARRAY3CFFORMULA(TEXT_CODE2 IN NUMBER) RETURN VARCHAR2 IS
523 TEXT2 VARCHAR2(116);
524 TEXT3 VARCHAR2(2000);
525 CURSOR GET_TEXT IS
526 SELECT
527 TEXT
528 FROM
529 QC_TEXT_TBL
530 WHERE TEXT_CODE = TEXT_CODE2
531 AND LINE_NO <> - 1
532 AND LANG_CODE = 'US';
533 BEGIN
534 OPEN GET_TEXT;
535 LOOP
536 FETCH GET_TEXT
537 INTO TEXT2;
538 EXIT WHEN GET_TEXT%NOTFOUND;
539 TEXT3 := TEXT3 || TEXT2;
540 END LOOP;
541 CLOSE GET_TEXT;
542 IF SQL%NOTFOUND THEN
543 RETURN (NULL);
544 END IF;
545 RETURN (TEXT3);
546 RETURN NULL;
547 EXCEPTION
548 WHEN NO_DATA_FOUND THEN
549 RETURN (NULL);
550 END TEXTARRAY3CFFORMULA;
551 FUNCTION ITEMCFFORMULA RETURN VARCHAR2 IS
552 BEGIN
553 /*SRW.REFERENCE(ITEMCP)*/NULL;
554 IF FROM_ITEM IS NOT NULL AND TO_ITEM IS NOT NULL THEN
555 ITEMCP := 'and msi.concatenated_segments >= ''' || FROM_ITEMCP || ''' and msi.concatenated_segments <= ''' || TO_ITEMCP || '''';
556 ELSIF FROM_ITEM IS NULL AND TO_ITEM IS NULL THEN
557 ITEMCP:=' ';
558 ELSIF FROM_ITEM IS NOT NULL AND TO_ITEM IS NULL THEN
559 ITEMCP:= 'and msi.concatenated_segments >= ''' || FROM_ITEMCP || '''';
560 ELSIF FROM_ITEM IS NULL AND TO_ITEM IS NOT NULL THEN
561 ITEMCP:= 'and msi.concatenated_segments <= ''' || TO_ITEMCP || '''';
562 END IF;
563 RETURN NULL;
564 END ITEMCFFORMULA;
565 FUNCTION SAMPLECFFORMULA RETURN VARCHAR2 IS
566 BEGIN
567 /*SRW.REFERENCE(SAMPLECP)*/NULL;
568 IF FROM_SAMPLE IS NOT NULL AND TO_SAMPLE IS NOT NULL THEN
569 SAMPLECP := ' and gs.sample_no >= ''' || FROM_SAMPLE || ''' and gs.sample_no <= ''' || TO_SAMPLE || '''';
570 ELSIF FROM_SAMPLE IS NULL AND TO_SAMPLE IS NULL THEN
571 SAMPLECP := ' ';
572 ELSIF FROM_SAMPLE IS NOT NULL AND TO_SAMPLE IS NULL THEN
573 SAMPLECP := ' and gs.sample_no >= ''' || FROM_SAMPLE || '''';
574 ELSIF FROM_SAMPLE IS NULL AND TO_SAMPLE IS NOT NULL THEN
575 SAMPLECP := ' and gs.sample_no <= ''' || TO_SAMPLE || '''';
576 END IF;
577 RETURN NULL;
578 END SAMPLECFFORMULA;
579 FUNCTION CUST_VENDCFFORMULA RETURN VARCHAR2 IS
580 CUSTVENDTITLE VARCHAR2(80);
581 BEGIN
582 /*SRW.REFERENCE(CUST_VENDCP)*/NULL;
583 IF REPORT_TYPE = 0 THEN
584 IF FROM_CUST_VEND IS NOT NULL AND TO_CUST_VEND IS NOT NULL THEN
585 CUST_VENDCP_1 := ' and cust.account_number >= ''' || FROM_CUST_VEND || ''' and cust.account_number <= ''' || TO_CUST_VEND || '''';
586 ELSIF FROM_CUST_VEND IS NULL AND TO_CUST_VEND IS NULL THEN
587 CUST_VENDCP_1 := ' ';
588 ELSIF FROM_CUST_VEND IS NOT NULL AND TO_CUST_VEND IS NULL THEN
589 CUST_VENDCP_1 := ' and cust.account_number >= ''' || FROM_CUST_VEND || '''';
590 ELSIF FROM_CUST_VEND IS NULL AND TO_CUST_VEND IS NOT NULL THEN
591 CUST_VENDCP_1 := ' and cust.account_number <= ''' || TO_CUST_VEND || '''';
592 END IF;
593 ELSIF REPORT_TYPE = 1 THEN
594 IF FROM_CUST_VEND IS NOT NULL AND TO_CUST_VEND IS NOT NULL THEN
595 CUST_VENDCP := ' and pvm.segment1 >= ''' || FROM_CUST_VEND || ''' and pvm.segment1 <= ''' || TO_CUST_VEND || '''';
596 ELSIF FROM_CUST_VEND IS NULL AND TO_CUST_VEND IS NULL THEN
597 CUST_VENDCP := ' ';
598 ELSIF FROM_CUST_VEND IS NOT NULL AND TO_CUST_VEND IS NULL THEN
599 CUST_VENDCP := ' and pvm.segment1 >= ''' || FROM_CUST_VEND || '''';
600 ELSIF FROM_CUST_VEND IS NULL AND TO_CUST_VEND IS NOT NULL THEN
601 CUST_VENDCP := ' and pvm.segment1 <= ''' || TO_CUST_VEND || '''';
602 END IF;
603 END IF;
604 SELECT
605 MEANING
606 INTO CUSTVENDTITLE
607 FROM
608 GEM_LOOKUPS
609 WHERE LOOKUP_TYPE = 'GMD_REPORT_TYPE'
610 AND LOOKUP_CODE = REPORT_TYPE;
611 RETURN (CUSTVENDTITLE);
612 END CUST_VENDCFFORMULA;
613 FUNCTION TEXTCODECFFORMULA(QC_SPEC_ID IN NUMBER) RETURN NUMBER IS
614 TEXTCODE NUMBER;
615 BEGIN
616 SELECT
617 TEXT_CODE
618 INTO TEXTCODE
619 FROM
620 GMD_SPECIFICATIONS_B
621 WHERE SPEC_ID = QC_SPEC_ID;
622 RETURN (TEXTCODE);
623 RETURN NULL;
624 EXCEPTION
625 WHEN NO_DATA_FOUND THEN
626 RETURN (NULL);
630 BEGIN
627 END TEXTCODECFFORMULA;
628 FUNCTION TEXTCODE1CFFORMULA(QC_SPEC_ID2 IN NUMBER) RETURN NUMBER IS
629 TEXTCODE1 NUMBER;
631 SELECT
632 TEXT_CODE
633 INTO TEXTCODE1
634 FROM
635 GMD_SPECIFICATIONS_B
636 WHERE SPEC_ID = QC_SPEC_ID2;
637 RETURN (TEXTCODE1);
638 RETURN NULL;
639 EXCEPTION
640 WHEN NO_DATA_FOUND THEN
641 RETURN (NULL);
642 END TEXTCODE1CFFORMULA;
643 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
644 BEGIN
645 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
646 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
647 P_ROWS := 0;
648 RETURN (TRUE);
649 END BEFOREREPORT;
650 FUNCTION FROM_ITEMCFFORMULA RETURN VARCHAR2 IS
651 L_FROM_ITEM VARCHAR2(240);
652 BEGIN
653 IF FROM_ITEM IS NULL THEN
654 FROM_ITEMCP := SY_ALL;
655 ELSE
656 SELECT
657 DISTINCT
658 CONCATENATED_SEGMENTS
659 INTO L_FROM_ITEM
660 FROM
661 MTL_SYSTEM_ITEMS_KFV
662 WHERE INVENTORY_ITEM_ID = FROM_ITEM;
663 FROM_ITEMCP := L_FROM_ITEM;
664 END IF;
665 RETURN (FROM_ITEMCP);
666 END FROM_ITEMCFFORMULA;
667 FUNCTION TO_ITEMCFFORMULA RETURN VARCHAR2 IS
668 L_TO_ITEM VARCHAR2(240);
669 BEGIN
670 IF TO_ITEM IS NULL THEN
671 TO_ITEMCP := SY_ALL;
672 ELSE
673 SELECT
674 DISTINCT
675 CONCATENATED_SEGMENTS
676 INTO L_TO_ITEM
677 FROM
678 MTL_SYSTEM_ITEMS_KFV
679 WHERE INVENTORY_ITEM_ID = TO_ITEM;
680 TO_ITEMCP := L_TO_ITEM;
681 END IF;
682 RETURN (TO_ITEMCP);
683 END TO_ITEMCFFORMULA;
684 FUNCTION FROM_SAMPLECFFORMULA RETURN VARCHAR2 IS
685 BEGIN
686 IF FROM_SAMPLE IS NULL THEN
687 FROM_SAMPLECP := SY_ALL;
688 ELSE
689 FROM_SAMPLECP := FROM_SAMPLE;
690 END IF;
691 RETURN (FROM_SAMPLECP);
692 END FROM_SAMPLECFFORMULA;
693 FUNCTION TO_SAMPLECFFORMULA RETURN VARCHAR2 IS
694 BEGIN
695 IF TO_SAMPLE IS NULL THEN
696 TO_SAMPLECP := SY_ALL;
697 ELSE
698 TO_SAMPLECP := TO_SAMPLE;
699 END IF;
700 RETURN (TO_SAMPLECP);
701 END TO_SAMPLECFFORMULA;
702 FUNCTION FROM_CUST_VENDCFFORMULA RETURN VARCHAR2 IS
703 BEGIN
704 IF FROM_CUST_VEND IS NULL THEN
705 FROM_CUST_VENDCP := SY_ALL;
706 ELSE
707 FROM_CUST_VENDCP := FROM_CUST_VEND;
708 END IF;
709 RETURN (FROM_CUST_VENDCP);
710 END FROM_CUST_VENDCFFORMULA;
711 FUNCTION TO_CUST_VENDCFFORMULA RETURN VARCHAR2 IS
712 BEGIN
713 IF TO_CUST_VEND IS NULL THEN
714 TO_CUST_VENDCP := SY_ALL;
715 ELSE
716 TO_CUST_VENDCP := TO_CUST_VEND;
717 END IF;
718 RETURN (TO_CUST_VENDCP);
719 END TO_CUST_VENDCFFORMULA;
720 FUNCTION FROM_RSLT_DTCFFORMULA RETURN VARCHAR2 IS
721 BEGIN
722 IF FROM_RESULT_DATE IS NULL THEN
723 FROM_RSLT_DTCP := SY_ALL;
724 ELSE
725 FROM_RSLT_DTCP := TO_CHAR(FROM_RESULT_DATE
726 ,'DD-MON-YYYY');
727 END IF;
728 RETURN (FROM_RSLT_DTCP);
729 END FROM_RSLT_DTCFFORMULA;
730 FUNCTION TO_RSLT_DTCFFORMULA RETURN VARCHAR2 IS
731 BEGIN
732 IF TO_RESULT_DATE IS NULL THEN
733 TO_RSLT_DTCP := SY_ALL;
734 ELSE
735 TO_RSLT_DTCP := TO_CHAR(TO_RESULT_DATE
736 ,'DD-MON-YYYY');
737 END IF;
738 RETURN (TO_RSLT_DTCP);
739 END TO_RSLT_DTCFFORMULA;
740 FUNCTION INCLUDECFFORMULA RETURN VARCHAR2 IS
741 INCLUDE1 VARCHAR2(80);
742 BEGIN
743 IF INCLUDE = '0' THEN
744 SELECT
745 MEANING
746 INTO INCLUDE1
747 FROM
748 GEM_LOOKUPS
749 WHERE LOOKUP_CODE = '0'
750 AND LOOKUP_TYPE = 'QC_INCLUDE';
751 ELSIF INCLUDE = '1' THEN
752 SELECT
753 MEANING
754 INTO INCLUDE1
755 FROM
756 GEM_LOOKUPS
757 WHERE LOOKUP_CODE = '1'
758 AND LOOKUP_TYPE = 'QC_INCLUDE';
759 END IF;
760 RETURN (INCLUDE1);
761 END INCLUDECFFORMULA;
762 FUNCTION PRINTCFFORMULA RETURN VARCHAR2 IS
763 PRINT1 VARCHAR2(80);
764 BEGIN
765 IF PRINT_CONDITION = '0' THEN
766 SELECT
767 MEANING
768 INTO PRINT1
769 FROM
770 GEM_LOOKUPS
771 WHERE LOOKUP_CODE = '0'
772 AND LOOKUP_TYPE = 'QC_PRINT';
773 ELSIF PRINT_CONDITION = '1' THEN
774 SELECT
775 MEANING
776 INTO PRINT1
777 FROM
778 GEM_LOOKUPS
779 WHERE LOOKUP_CODE = '1'
780 AND LOOKUP_TYPE = 'QC_PRINT';
781 ELSIF PRINT_CONDITION = '2' THEN
782 SELECT
783 MEANING
784 INTO PRINT1
785 FROM
786 GEM_LOOKUPS
787 WHERE LOOKUP_CODE = '2'
788 AND LOOKUP_TYPE = 'QC_PRINT';
789 ELSIF PRINT_CONDITION = '3' THEN
790 SELECT
791 MEANING
792 INTO PRINT1
793 FROM
794 GEM_LOOKUPS
795 WHERE LOOKUP_CODE = '3'
796 AND LOOKUP_TYPE = 'QC_PRINT';
797 END IF;
798 RETURN (PRINT1);
799 END PRINTCFFORMULA;
800 FUNCTION ACCEPT_FINALCFFORMULA(ACCEPT_ANYWAY IN VARCHAR2
801 ,FINAL_MARK IN VARCHAR2) RETURN VARCHAR2 IS
802 TEMP1 VARCHAR2(80);
803 TEMP2 VARCHAR2(80);
804 BEGIN
805 SELECT
806 MEANING
807 INTO TEMP1
808 FROM
812 AND LANGUAGE = USERENV('LANG');
809 FND_LOOKUP_VALUES
810 WHERE LOOKUP_TYPE = 'GMD_QC_YES_NO'
811 AND LOOKUP_CODE = ACCEPT_ANYWAY
813 ACCEPT_CP := TEMP1;
814 SELECT
815 MEANING
816 INTO TEMP2
817 FROM
818 FND_LOOKUP_VALUES
819 WHERE LOOKUP_TYPE = 'GMD_QC_YES_NO'
820 AND LOOKUP_CODE = FINAL_MARK
821 AND LANGUAGE = USERENV('LANG');
822 FINAL_CP := TEMP2;
823 RETURN NULL;
824 RETURN NULL;
825 END ACCEPT_FINALCFFORMULA;
826 FUNCTION ACCEPT_FINALCFFORMULA0037(ACCEPT_ANYWAY1 IN VARCHAR2
827 ,FINAL_MARK1 IN VARCHAR2) RETURN VARCHAR2 IS
828 TEMP1 VARCHAR2(80);
829 TEMP2 VARCHAR2(80);
830 BEGIN
831 SELECT
832 MEANING
833 INTO TEMP1
834 FROM
835 FND_LOOKUP_VALUES
836 WHERE LOOKUP_TYPE = 'GMD_QC_YES_NO'
837 AND LOOKUP_CODE = ACCEPT_ANYWAY1
838 AND LANGUAGE = USERENV('LANG');
839 ACCEPT1_CP := TEMP1;
840 SELECT
841 MEANING
842 INTO TEMP2
843 FROM
844 FND_LOOKUP_VALUES
845 WHERE LOOKUP_TYPE = 'GMD_QC_YES_NO'
846 AND LOOKUP_CODE = FINAL_MARK1
847 AND LANGUAGE = USERENV('LANG');
848 FINAL1_CP := TEMP2;
849 RETURN NULL;
850 END ACCEPT_FINALCFFORMULA0037;
851 PROCEDURE GMD_QCR01USR_XMLP_PKG_HEADER IS
852 BEGIN
853 NULL;
854 END GMD_QCR01USR_XMLP_PKG_HEADER;
855 FUNCTION LOTNOCFFORMULA RETURN CHAR IS
856 BEGIN
857 /*SRW.REFERENCE(LOTNOCP)*/NULL;
858 IF FROM_LOTNO IS NOT NULL AND TO_LOTNO IS NOT NULL THEN
859 LOTNOCP := ' and gs.lot_number >= ''' || FROM_LOTNO || ''' and gs.lot_number <= ''' || TO_LOTNO || '''';
860 ELSIF FROM_LOTNO IS NULL AND TO_LOTNO IS NULL THEN
861 LOTNOCP := ' ';
862 ELSIF FROM_LOTNO IS NOT NULL AND TO_LOTNO IS NULL THEN
863 LOTNOCP := ' and gs.lot_number >= ''' || FROM_LOTNO || '''';
864 ELSIF FROM_LOTNO IS NULL AND TO_LOTNO IS NOT NULL THEN
865 LOTNOCP := ' and gs.lot_number <= ''' || TO_LOTNO || '''';
866 END IF;
867 RETURN NULL;
868 END LOTNOCFFORMULA;
869 FUNCTION FROM_LOTNOCFFORMULA RETURN VARCHAR2 IS
870 BEGIN
871 IF FROM_LOTNO IS NULL THEN
872 FROM_LOTNOCP := SY_ALL;
873 ELSE
874 FROM_LOTNOCP := FROM_LOTNO;
875 END IF;
876 RETURN (FROM_LOTNOCP);
877 END FROM_LOTNOCFFORMULA;
878 FUNCTION TO_LOTNOCFFORMULA RETURN VARCHAR2 IS
879 BEGIN
880 IF TO_LOTNO IS NULL THEN
881 TO_LOTNOCP := SY_ALL;
882 ELSE
883 TO_LOTNOCP := TO_LOTNO;
884 END IF;
885 RETURN (TO_LOTNOCP);
886 END TO_LOTNOCFFORMULA;
887 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
888 BEGIN
889 RETURN (TRUE);
890 END BEFOREPFORM;
891 FUNCTION TARGET_DISPCFFORMULA(ASSAY_CODE IN VARCHAR2
892 ,QCASSY_TYP_ID IN NUMBER
893 ,TARGETCF IN VARCHAR2) RETURN CHAR IS
894 ASSAYVALUE VARCHAR2(240);
895 ASSAYTYPE VARCHAR2(2);
896 BEGIN
897 IF ASSAY_CODE IS NULL THEN
898 RETURN (NULL);
899 END IF;
900 SELECT
901 DISTINCT
902 TEST_TYPE
903 INTO ASSAYTYPE
904 FROM
905 GMD_QC_TESTS_B
906 WHERE TEST_CODE = ASSAY_CODE;
907 IF ASSAYTYPE = 'L' THEN
908 BEGIN
909 SELECT
910 DISPLAY_LABEL_NUMERIC_RANGE
911 INTO ASSAYVALUE
912 FROM
913 GMD_QC_TEST_VALUES
914 WHERE TEST_ID = QCASSY_TYP_ID
915 AND ( MIN_NUM <= TARGETCF
916 OR MIN_NUM IS NULL )
917 AND ( MAX_NUM >= TARGETCF
918 OR MAX_NUM IS NULL );
919 EXCEPTION
920 WHEN NO_DATA_FOUND THEN
921 NULL;
922 END;
923 END IF;
924 ASSAY_TYPECP := ASSAYTYPE;
925 RETURN (ASSAYVALUE);
926 END TARGET_DISPCFFORMULA;
927 FUNCTION MIN_DISPCFFORMULA(QCASSY_TYP_ID IN NUMBER
928 ,MIN_SPECCF IN NUMBER) RETURN CHAR IS
929 ASSAYVALUE VARCHAR2(240);
930 ASSAYTYPE VARCHAR2(1);
931 BEGIN
932 IF ASSAY_TYPECP = 'L' THEN
933 BEGIN
934 SELECT
935 DISPLAY_LABEL_NUMERIC_RANGE
936 INTO ASSAYVALUE
937 FROM
938 GMD_QC_TEST_VALUES
939 WHERE TEST_ID = QCASSY_TYP_ID
940 AND ( MIN_NUM <= MIN_SPECCF
941 OR MIN_NUM IS NULL )
942 AND ( MAX_NUM >= MIN_SPECCF
943 OR MAX_NUM IS NULL );
944 EXCEPTION
945 WHEN NO_DATA_FOUND THEN
946 ASSAYVALUE := ' ';
947 END;
948 END IF;
949 RETURN (ASSAYVALUE);
950 END MIN_DISPCFFORMULA;
951 FUNCTION MAX_DISPCFFORMULA(QCASSY_TYP_ID IN NUMBER
952 ,MAX_SPECCF IN NUMBER) RETURN CHAR IS
953 ASSAYVALUE VARCHAR2(240);
954 BEGIN
955 IF ASSAY_TYPECP = 'L' THEN
956 BEGIN
957 SELECT
958 DISPLAY_LABEL_NUMERIC_RANGE
959 INTO ASSAYVALUE
960 FROM
961 GMD_QC_TEST_VALUES
962 WHERE TEST_ID = QCASSY_TYP_ID
963 AND ( MIN_NUM <= MAX_SPECCF
964 OR MIN_NUM IS NULL )
965 AND ( MAX_NUM >= MAX_SPECCF
966 OR MAX_NUM IS NULL );
967 EXCEPTION
968 WHEN NO_DATA_FOUND THEN
969 ASSAYVALUE := ' ';
970 END;
971 END IF;
972 RETURN (ASSAYVALUE);
973 END MAX_DISPCFFORMULA;
974 FUNCTION MAX_DISP1CFFORMULA(QCASSY_TYP_ID1 IN NUMBER
975 ,MAX_SPEC1CF IN NUMBER) RETURN CHAR IS
976 ASSAYVALUE VARCHAR2(240);
980 SELECT
977 BEGIN
978 IF ASSAY_TYPE1CP = 'L' THEN
979 BEGIN
981 DISPLAY_LABEL_NUMERIC_RANGE
982 INTO ASSAYVALUE
983 FROM
984 GMD_QC_TEST_VALUES
985 WHERE TEST_ID = QCASSY_TYP_ID1
986 AND ( MIN_NUM <= MAX_SPEC1CF
987 OR MIN_NUM IS NULL )
988 AND ( MAX_NUM >= MAX_SPEC1CF
989 OR MAX_NUM IS NULL );
990 EXCEPTION
991 WHEN NO_DATA_FOUND THEN
992 ASSAYVALUE := ' ';
993 END;
994 END IF;
995 RETURN (ASSAYVALUE);
996 END MAX_DISP1CFFORMULA;
997 FUNCTION MIN_DISP1CFFORMULA(QCASSY_TYP_ID1 IN NUMBER
998 ,MIN_SPEC1CF IN NUMBER) RETURN CHAR IS
999 ASSAYVALUE VARCHAR2(240);
1000 ASSAYTYPE VARCHAR2(1);
1001 BEGIN
1002 IF ASSAY_TYPE1CP = 'L' THEN
1003 BEGIN
1004 SELECT
1005 DISPLAY_LABEL_NUMERIC_RANGE
1006 INTO ASSAYVALUE
1007 FROM
1008 GMD_QC_TEST_VALUES
1009 WHERE TEST_ID = QCASSY_TYP_ID1
1010 AND ( MIN_NUM <= MIN_SPEC1CF
1011 OR MIN_NUM IS NULL )
1012 AND ( MAX_NUM >= MIN_SPEC1CF
1013 OR MAX_NUM IS NULL );
1014 EXCEPTION
1015 WHEN NO_DATA_FOUND THEN
1016 ASSAYVALUE := ' ';
1017 END;
1018 END IF;
1019 RETURN (ASSAYVALUE);
1020 END MIN_DISP1CFFORMULA;
1021 FUNCTION TARGET_DISP1CFFORMULA(ASSAY_CODE2 IN VARCHAR2
1022 ,QCASSY_TYP_ID1 IN NUMBER
1023 ,TARGET1CF IN VARCHAR2) RETURN CHAR IS
1024 ASSAYVALUE VARCHAR2(240);
1025 ASSAYTYPE VARCHAR2(2);
1026 BEGIN
1027 IF ASSAY_CODE2 IS NULL THEN
1028 RETURN (NULL);
1029 END IF;
1030 SELECT
1031 DISTINCT
1032 TEST_TYPE
1033 INTO ASSAYTYPE
1034 FROM
1035 GMD_QC_TESTS_B
1036 WHERE TEST_CODE = ASSAY_CODE2;
1037 IF ASSAYTYPE = 'L' THEN
1038 BEGIN
1039 SELECT
1040 DISPLAY_LABEL_NUMERIC_RANGE
1041 INTO ASSAYVALUE
1042 FROM
1043 GMD_QC_TEST_VALUES
1044 WHERE TEST_ID = QCASSY_TYP_ID1
1045 AND ( MIN_NUM <= TARGET1CF
1046 OR MIN_NUM IS NULL )
1047 AND ( MAX_NUM >= TARGET1CF
1048 OR MAX_NUM IS NULL );
1049 EXCEPTION
1050 WHEN NO_DATA_FOUND THEN
1051 NULL;
1052 END;
1053 END IF;
1054 ASSAY_TYPE1CP := ASSAYTYPE;
1055 RETURN (ASSAYVALUE);
1056 END TARGET_DISP1CFFORMULA;
1057 FUNCTION RESULT_DISP1CFFORMULA(QCASSY_TYP_ID1 IN NUMBER
1058 ,NUM_RESULT2 IN NUMBER) RETURN CHAR IS
1059 ASSAYVALUE VARCHAR2(240);
1060 BEGIN
1061 IF ASSAY_TYPE1CP = 'L' THEN
1062 BEGIN
1063 SELECT
1064 DISPLAY_LABEL_NUMERIC_RANGE
1065 INTO ASSAYVALUE
1066 FROM
1067 GMD_QC_TEST_VALUES
1068 WHERE TEST_ID = QCASSY_TYP_ID1
1069 AND ( MIN_NUM <= NUM_RESULT2
1070 OR MIN_NUM IS NULL )
1071 AND ( MAX_NUM >= NUM_RESULT2
1072 OR MAX_NUM IS NULL );
1073 EXCEPTION
1074 WHEN NO_DATA_FOUND THEN
1075 ASSAYVALUE := ' ';
1076 END;
1077 END IF;
1078 RETURN (ASSAYVALUE);
1079 END RESULT_DISP1CFFORMULA;
1080 FUNCTION RESULT_DISPCFFORMULA(QCASSY_TYP_ID IN NUMBER
1081 ,NUM_RESULT IN NUMBER) RETURN CHAR IS
1082 ASSAYVALUE VARCHAR2(240);
1083 BEGIN
1084 IF ASSAY_TYPECP = 'L' THEN
1085 BEGIN
1086 SELECT
1087 DISPLAY_LABEL_NUMERIC_RANGE
1088 INTO ASSAYVALUE
1089 FROM
1090 GMD_QC_TEST_VALUES
1091 WHERE TEST_ID = QCASSY_TYP_ID
1092 AND ( MIN_NUM <= NUM_RESULT
1093 OR MIN_NUM IS NULL )
1094 AND ( MAX_NUM >= NUM_RESULT
1095 OR MAX_NUM IS NULL );
1096 EXCEPTION
1097 WHEN NO_DATA_FOUND THEN
1098 ASSAYVALUE := ' ';
1099 END;
1100 END IF;
1101 RETURN (ASSAYVALUE);
1102 END RESULT_DISPCFFORMULA;
1103 FUNCTION MIN_CHARCFFORMULA(MIN_CHAR IN VARCHAR2) RETURN CHAR IS
1104 BEGIN
1105 IF MIN_CHAR IS NULL THEN
1106 RETURN (NULL);
1107 ELSE
1108 RETURN (MIN_CHAR);
1109 END IF;
1110 END MIN_CHARCFFORMULA;
1111 FUNCTION MIN_CHAR1CFFORMULA(MIN_CHAR1 IN VARCHAR2) RETURN CHAR IS
1112 BEGIN
1113 IF MIN_CHAR1 IS NULL THEN
1114 RETURN (NULL);
1115 ELSE
1116 RETURN (MIN_CHAR1);
1117 END IF;
1118 END MIN_CHAR1CFFORMULA;
1119 FUNCTION MAX_CHARCFFORMULA(MAX_CHAR IN VARCHAR2) RETURN CHAR IS
1120 BEGIN
1121 IF MAX_CHAR IS NULL THEN
1122 RETURN (NULL);
1123 ELSE
1124 RETURN (MAX_CHAR);
1125 END IF;
1126 END MAX_CHARCFFORMULA;
1127 FUNCTION MAX_CHAR1CFFORMULA(MAX_CHAR1 IN VARCHAR2) RETURN CHAR IS
1128 BEGIN
1129 IF MAX_CHAR1 IS NULL THEN
1130 RETURN (NULL);
1131 ELSE
1132 RETURN (MAX_CHAR1);
1133 END IF;
1134 END MAX_CHAR1CFFORMULA;
1135 FUNCTION NUM_RESULT2CFFORMULA(TEXT_RESULT2 IN VARCHAR2
1136 ,NUM_RESULT2 IN NUMBER
1137 ,TEST_TYPE1 IN VARCHAR2
1138 ,PRECISION1 IN NUMBER) RETURN CHAR IS
1139 L_PRECISION VARCHAR2(25);
1140 L_NUMBER NUMBER;
1141 L_NUM_RESULT VARCHAR2(25);
1142 BEGIN
1143 IF TEXT_RESULT2 IS NOT NULL THEN
1144 RETURN (NULL);
1145 ELSE
1146 IF NUM_RESULT2 IS NULL THEN
1147 RETURN (NUM_RESULT2);
1151 L_NUMBER := NUM_RESULT2;
1148 ELSIF TEST_TYPE1 in ('N','L','E') THEN
1149 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
1150 ,PRECISION1) - 1);
1152 L_NUM_RESULT := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
1153 ,L_PRECISION));
1154 RETURN (L_NUM_RESULT);
1155 END IF;
1156 END IF;
1157 END NUM_RESULT2CFFORMULA;
1158 FUNCTION TEXT_RESULT2CFFORMULA(TEXT_RESULT2 IN VARCHAR2) RETURN CHAR IS
1159 BEGIN
1160 IF TEXT_RESULT2 IS NOT NULL THEN
1161 RETURN (TEXT_RESULT2);
1162 ELSE
1163 RETURN (NULL);
1164 END IF;
1165 END TEXT_RESULT2CFFORMULA;
1166 FUNCTION TEXT_RESULTCFFORMULA(TEXT_RESULT IN VARCHAR2) RETURN CHAR IS
1167 BEGIN
1168 IF TEXT_RESULT IS NOT NULL THEN
1169 RETURN (TEXT_RESULT);
1170 ELSE
1171 RETURN (NULL);
1172 END IF;
1173 END TEXT_RESULTCFFORMULA;
1174 FUNCTION NUM_RESULTCFFORMULA(TEXT_RESULT IN VARCHAR2
1175 ,NUM_RESULT IN NUMBER
1176 ,TEST_TYPE IN VARCHAR2
1177 ,PRECISION IN NUMBER) RETURN CHAR IS
1178 L_PRECISION VARCHAR2(25);
1179 L_NUMBER NUMBER;
1180 L_NUM_RESULT VARCHAR2(25);
1181 BEGIN
1182 IF TEXT_RESULT IS NOT NULL THEN
1183 RETURN (NULL);
1184 ELSE
1185 IF NUM_RESULT IS NULL THEN
1186 RETURN (NUM_RESULT);
1187 ELSIF TEST_TYPE in ('N','L','E') THEN
1188 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
1189 ,PRECISION) - 1);
1190 L_NUMBER := NUM_RESULT;
1191 L_NUM_RESULT := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
1192 ,L_PRECISION));
1193 RETURN (L_NUM_RESULT);
1194 END IF;
1195 END IF;
1196 END NUM_RESULTCFFORMULA;
1197 FUNCTION AFTERPFORM RETURN BOOLEAN IS
1198 BEGIN
1199 RETURN (TRUE);
1200 END AFTERPFORM;
1201 FUNCTION BETWEENPAGE RETURN BOOLEAN IS
1202 BEGIN
1203 RETURN (TRUE);
1204 END BETWEENPAGE;
1205 FUNCTION AFTERREPORT RETURN BOOLEAN IS
1206 BEGIN
1207 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
1208 RETURN (TRUE);
1209 END AFTERREPORT;
1210 FUNCTION SHIP_TO_SITECFFORMULA(SHIP_TO_SITE_ID IN NUMBER) RETURN CHAR IS
1211 LOCATION VARCHAR2(40);
1212 BEGIN
1213 IF SHIP_TO_SITE_ID IS NOT NULL THEN
1214 BEGIN
1215 SELECT
1216 S.LOCATION
1217 INTO LOCATION
1218 FROM
1219 HZ_CUST_SITE_USES_ALL S
1220 WHERE S.SITE_USE_ID = SHIP_TO_SITE_ID
1221 AND S.SITE_USE_CODE = 'SHIP_TO';
1222 RETURN (LOCATION);
1223 EXCEPTION
1224 WHEN NO_DATA_FOUND THEN
1225 RETURN (NULL);
1226 END;
1227 ELSE
1228 RETURN (NULL);
1229 END IF;
1230 END SHIP_TO_SITECFFORMULA;
1231 FUNCTION ORDER_HEADERCFFORMULA(ORDER_HEADER_ID IN NUMBER) RETURN NUMBER IS
1232 ORDER_NUMBER NUMBER;
1233 ORDER_TYPE_ID NUMBER;
1234 ORDER_TYPE_NAME VARCHAR2(30);
1235 BEGIN
1236 IF ORDER_HEADER_ID IS NOT NULL THEN
1237 BEGIN
1238 SELECT
1239 H.ORDER_NUMBER,
1240 T.NAME
1241 INTO ORDER_NUMBER,ORDER_TYPE_NAME
1242 FROM
1243 OE_ORDER_HEADERS_ALL H,
1244 OE_TRANSACTION_TYPES_TL T
1245 WHERE H.HEADER_ID = ORDER_HEADER_ID
1246 AND H.ORDER_TYPE_ID = T.TRANSACTION_TYPE_ID
1247 AND T.LANGUAGE = 'US';
1248 ORDER_TYPECP := ORDER_TYPE_NAME;
1249 RETURN (ORDER_NUMBER);
1250 EXCEPTION
1251 WHEN NO_DATA_FOUND THEN
1252 ORDER_TYPECP := NULL;
1253 RETURN (NULL);
1254 END;
1255 ELSE
1256 ORDER_TYPECP := NULL;
1257 RETURN (NULL);
1258 END IF;
1259 END ORDER_HEADERCFFORMULA;
1260 FUNCTION OPERATING_UNITCFFORMULA(ORDER_ORG_ID IN NUMBER) RETURN CHAR IS
1261 OP_UNIT VARCHAR2(60);
1262 BEGIN
1263 IF ORDER_ORG_ID IS NOT NULL THEN
1264 BEGIN
1265 SELECT
1266 NAME
1267 INTO OP_UNIT
1268 FROM
1269 HR_OPERATING_UNITS
1270 WHERE ORGANIZATION_ID = ORDER_ORG_ID;
1271 RETURN (OP_UNIT);
1272 EXCEPTION
1273 WHEN NO_DATA_FOUND THEN
1274 RETURN (NULL);
1275 END;
1276 ELSE
1277 RETURN (NULL);
1278 END IF;
1279 END OPERATING_UNITCFFORMULA;
1280 FUNCTION CF_MINFORMULA(TEST_TYPE IN VARCHAR2
1281 ,PRECISION IN NUMBER
1282 ,MIN_SPEC IN NUMBER
1283 ,MIN_CHAR IN VARCHAR2) RETURN CHAR IS
1284 L_PRECISION VARCHAR2(25);
1285 L_NUMBER NUMBER;
1286 L_MIN_SPEC VARCHAR2(25);
1287 BEGIN
1288 IF TEST_TYPE in ('N','L','E') THEN
1289 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
1290 ,PRECISION) - 1);
1291 L_NUMBER := MIN_SPEC;
1292 L_MIN_SPEC := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
1293 ,L_PRECISION));
1294 RETURN (L_MIN_SPEC);
1295 ELSE
1296 RETURN (MIN_CHAR);
1297 END IF;
1298 END CF_MINFORMULA;
1299 FUNCTION CF_MAXFORMULA(TEST_TYPE IN VARCHAR2
1300 ,PRECISION IN NUMBER
1301 ,MAX_SPEC IN NUMBER
1302 ,MAX_CHAR IN VARCHAR2) RETURN CHAR IS
1303 L_PRECISION VARCHAR2(25);
1304 L_NUMBER NUMBER;
1305 L_MAX_SPEC VARCHAR2(25);
1306 BEGIN
1307 IF TEST_TYPE in ('N','L','E') THEN
1308 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
1309 ,PRECISION) - 1);
1310 L_NUMBER := MAX_SPEC;
1311 L_MAX_SPEC := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
1312 ,L_PRECISION));
1313 RETURN (L_MAX_SPEC);
1314 ELSE
1315 RETURN (MAX_CHAR);
1316 END IF;
1317 END CF_MAXFORMULA;
1318 FUNCTION CF_MIN1FORMULA(TEST_TYPE1 IN VARCHAR2
1319 ,PRECISION1 IN NUMBER
1320 ,MIN_SPEC1 IN NUMBER
1321 ,MIN_CHAR1 IN VARCHAR2) RETURN CHAR IS
1322 L_PRECISION VARCHAR2(25);
1323 L_NUMBER NUMBER;
1324 L_MIN_SPEC VARCHAR2(25);
1325 BEGIN
1326 IF TEST_TYPE1 in ('N','L','E') THEN
1327 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
1328 ,PRECISION1) - 1);
1329 L_NUMBER := MIN_SPEC1;
1330 L_MIN_SPEC := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
1331 ,L_PRECISION));
1332 RETURN (L_MIN_SPEC);
1333 ELSE
1334 RETURN (MIN_CHAR1);
1335 END IF;
1336 END CF_MIN1FORMULA;
1337 FUNCTION CF_MAX1FORMULA(TEST_TYPE1 IN VARCHAR2
1338 ,PRECISION1 IN NUMBER
1339 ,MAX_SPEC1 IN NUMBER
1340 ,MAX_CHAR1 IN VARCHAR2) RETURN CHAR IS
1341 L_PRECISION VARCHAR2(25);
1342 L_NUMBER NUMBER;
1343 L_MAX_SPEC VARCHAR2(25);
1344 BEGIN
1345 IF TEST_TYPE1 in ('N','L','E') THEN
1346 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
1347 ,PRECISION1) - 1);
1348 L_NUMBER := MAX_SPEC1;
1349 L_MAX_SPEC := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
1350 ,L_PRECISION));
1351 RETURN (L_MAX_SPEC);
1352 ELSE
1353 RETURN (MAX_CHAR1);
1354 END IF;
1355 END CF_MAX1FORMULA;
1356 FUNCTION ORG_CFFORMULA RETURN CHAR IS
1357 ORG_CODE VARCHAR2(3);
1358 BEGIN
1359 SELECT
1360 DISTINCT
1361 ORGANIZATION_CODE
1362 INTO ORG_CODE
1363 FROM
1364 MTL_PARAMETERS
1365 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
1366 ORG_CP := ORG_CODE;
1367 RETURN (ORG_CP);
1368 END ORG_CFFORMULA;
1369 FUNCTION ORDER_TYPECP_P RETURN VARCHAR2 IS
1370 BEGIN
1371 RETURN ORDER_TYPECP;
1372 END ORDER_TYPECP_P;
1373 FUNCTION ASSAY_TYPECP_P RETURN VARCHAR2 IS
1374 BEGIN
1375 RETURN ASSAY_TYPECP;
1376 END ASSAY_TYPECP_P;
1377 FUNCTION ACCEPT_CP_P RETURN VARCHAR2 IS
1378 BEGIN
1379 RETURN ACCEPT_CP;
1380 END ACCEPT_CP_P;
1381 FUNCTION FINAL_CP_P RETURN VARCHAR2 IS
1382 BEGIN
1383 RETURN FINAL_CP;
1384 END FINAL_CP_P;
1385 FUNCTION ASSAY_TYPE1CP_P RETURN VARCHAR2 IS
1386 BEGIN
1387 RETURN ASSAY_TYPE1CP;
1388 END ASSAY_TYPE1CP_P;
1389 FUNCTION ACCEPT1_CP_P RETURN VARCHAR2 IS
1390 BEGIN
1391 RETURN ACCEPT1_CP;
1392 END ACCEPT1_CP_P;
1393 FUNCTION FINAL1_CP_P RETURN VARCHAR2 IS
1394 BEGIN
1395 RETURN FINAL1_CP;
1396 END FINAL1_CP_P;
1397 FUNCTION DATE1CP_P RETURN VARCHAR2 IS
1398 BEGIN
1399 RETURN DATE1CP;
1400 END DATE1CP_P;
1401 FUNCTION FINAL1CP_P RETURN VARCHAR2 IS
1402 BEGIN
1403 RETURN FINAL1CP;
1404 END FINAL1CP_P;
1405 FUNCTION CUST_VENDCP_P RETURN VARCHAR2 IS
1406 BEGIN
1407 RETURN CUST_VENDCP;
1408 END CUST_VENDCP_P;
1409 FUNCTION CUST_VENDCP_1_P RETURN VARCHAR2 IS
1410 BEGIN
1411 RETURN CUST_VENDCP_1;
1412 END CUST_VENDCP_1_P;
1413 FUNCTION ITEMCP_P RETURN VARCHAR2 IS
1414 BEGIN
1415 RETURN ITEMCP;
1416 END ITEMCP_P;
1417 FUNCTION SAMPLECP_P RETURN VARCHAR2 IS
1418 BEGIN
1419 RETURN SAMPLECP;
1420 END SAMPLECP_P;
1421 FUNCTION FROM_ITEMCP_P RETURN VARCHAR2 IS
1422 BEGIN
1423 RETURN FROM_ITEMCP;
1424 END FROM_ITEMCP_P;
1425 FUNCTION TO_ITEMCP_P RETURN VARCHAR2 IS
1426 BEGIN
1427 RETURN TO_ITEMCP;
1428 END TO_ITEMCP_P;
1429 FUNCTION FROM_SAMPLECP_P RETURN VARCHAR2 IS
1430 BEGIN
1431 RETURN FROM_SAMPLECP;
1432 END FROM_SAMPLECP_P;
1433 FUNCTION TO_SAMPLECP_P RETURN VARCHAR2 IS
1434 BEGIN
1435 RETURN TO_SAMPLECP;
1436 END TO_SAMPLECP_P;
1437 FUNCTION FROM_CUST_VENDCP_P RETURN VARCHAR2 IS
1438 BEGIN
1439 RETURN FROM_CUST_VENDCP;
1440 END FROM_CUST_VENDCP_P;
1441 FUNCTION TO_CUST_VENDCP_P RETURN VARCHAR2 IS
1442 BEGIN
1443 RETURN TO_CUST_VENDCP;
1444 END TO_CUST_VENDCP_P;
1445 FUNCTION FROM_RSLT_DTCP_P RETURN VARCHAR2 IS
1446 BEGIN
1447 RETURN FROM_RSLT_DTCP;
1448 END FROM_RSLT_DTCP_P;
1449 FUNCTION TO_RSLT_DTCP_P RETURN VARCHAR2 IS
1450 BEGIN
1451 RETURN TO_RSLT_DTCP;
1452 END TO_RSLT_DTCP_P;
1453 FUNCTION LOTNOCP_P RETURN VARCHAR2 IS
1454 BEGIN
1455 RETURN LOTNOCP;
1456 END LOTNOCP_P;
1457 FUNCTION FROM_LOTNOCP_P RETURN VARCHAR2 IS
1458 BEGIN
1459 RETURN FROM_LOTNOCP;
1460 END FROM_LOTNOCP_P;
1461 FUNCTION TO_LOTNOCP_P RETURN VARCHAR2 IS
1462 BEGIN
1463 RETURN TO_LOTNOCP;
1464 END TO_LOTNOCP_P;
1465 FUNCTION ORG_CP_P RETURN VARCHAR2 IS
1466 BEGIN
1467 RETURN ORG_CP;
1468 END ORG_CP_P;
1469 END GMD_QCR01USR_XMLP_PKG;
1470
1471
[Home] [Help]