1 PACKAGE BODY GMD_QCR03USR_XMLP_PKG AS
2 /* $Header: QCR03USRB.pls 120.0 2007/12/24 13:08:32 krreddy noship $ */
3 FUNCTION SAMPLECFFORMULA RETURN VARCHAR2 IS
4 BEGIN
5 /*SRW.REFERENCE(SAMPLECP)*/NULL;
6 IF FROM_SAMPLE IS NOT NULL AND TO_SAMPLE IS NOT NULL THEN
7 SAMPLECP := ' and gs.sample_no >= ''' || FROM_SAMPLE || ''' and gs.sample_no <= ''' || TO_SAMPLE || '''';
8 ELSIF FROM_SAMPLE IS NULL AND TO_SAMPLE IS NULL THEN
9 --SAMPLECP := NULL;
10 SAMPLECP := ' ';
11 ELSIF FROM_SAMPLE IS NOT NULL AND TO_SAMPLE IS NULL THEN
12 SAMPLECP := ' and gs.sample_no >= ''' || FROM_SAMPLE || '''';
13 ELSIF FROM_SAMPLE IS NULL AND TO_SAMPLE IS NOT NULL THEN
14 SAMPLECP := ' and gs.sample_no <= ''' || TO_SAMPLE || '''';
15 END IF;
16 RETURN NULL;
17 END SAMPLECFFORMULA;
18 FUNCTION BATCHCFFORMULA RETURN VARCHAR2 IS
19 BEGIN
20 /*SRW.REFERENCE(BATCHCP)*/NULL;
21 IF FROM_BATCH IS NOT NULL AND TO_BATCH IS NOT NULL THEN
22 BATCHCP := ' and pbh.batch_no >= ''' || FROM_BATCH || ''' and pbh.batch_no <= ''' || TO_BATCH || '''';
23 ELSIF FROM_BATCH IS NULL AND TO_BATCH IS NULL THEN
24 --BATCHCP := NULL;
25 BATCHCP := ' ';
26 ELSIF FROM_BATCH IS NOT NULL AND TO_BATCH IS NULL THEN
27 BATCHCP := ' and pbh.batch_no >= ''' || FROM_BATCH || '''';
28 ELSIF FROM_BATCH IS NULL AND TO_BATCH IS NOT NULL THEN
29 BATCHCP := ' and pbh.batch_no <= ''' || TO_BATCH || '''';
30 END IF;
31 RETURN NULL;
32 END BATCHCFFORMULA;
33 FUNCTION FORMULACFFORMULA RETURN VARCHAR2 IS
34 BEGIN
35 /*SRW.REFERENCE(FORMULACP)*/NULL;
36 IF FROM_FORMULA IS NOT NULL AND TO_FORMULA IS NOT NULL THEN
37 FORMULACP := ' and ffm.formula_no >= ''' || FROM_FORMULA || ''' and ffm.formula_no <= ''' || TO_FORMULA || '''';
38 ELSIF FROM_FORMULA IS NULL AND TO_FORMULA IS NULL THEN
39 --FORMULACP := NULL;
40 FORMULACP := ' ';
41 ELSIF FROM_FORMULA IS NOT NULL AND TO_FORMULA IS NULL THEN
42 FORMULACP := ' and ffm.formula_no >= ''' || FROM_FORMULA || '''';
43 ELSIF FROM_FORMULA IS NULL AND TO_FORMULA IS NOT NULL THEN
44 FORMULACP := ' and ffm.formula_no <= ''' || TO_FORMULA || '''';
45 END IF;
46 RETURN NULL;
47 END FORMULACFFORMULA;
48 FUNCTION FORMULA_VERSCFFORMULA RETURN VARCHAR2 IS
49 BEGIN
50 /*SRW.REFERENCE(FORMULA_VERSCP)*/NULL;
51 IF FROM_FORMULA_VERSION IS NOT NULL AND TO_FORMULA_VERSION IS NOT NULL THEN
52 FORMULA_VERSCP := ' and ffm.formula_vers >= ''' || FROM_FORMULA_VERSION || ''' and ffm.formula_vers <= ''' || TO_FORMULA_VERSION || '''';
53 ELSIF FROM_FORMULA_VERSION IS NULL AND TO_FORMULA_VERSION IS NULL THEN
54 --FORMULA_VERSCP := NULL;
55 FORMULA_VERSCP := ' ';
56 ELSIF FROM_FORMULA_VERSION IS NOT NULL AND TO_FORMULA_VERSION IS NULL THEN
57 FORMULA_VERSCP := ' and ffm.formula_vers >= ''' || FROM_FORMULA_VERSION || '''';
58 ELSIF FROM_FORMULA_VERSION IS NULL AND TO_FORMULA_VERSION IS NOT NULL THEN
59 FORMULA_VERSCP := ' and ffm.formula_vers <= ''' || TO_FORMULA_VERSION || '''';
60 END IF;
61 RETURN NULL;
62 END FORMULA_VERSCFFORMULA;
63 FUNCTION ROUTINGCFFORMULA RETURN VARCHAR2 IS
64 BEGIN
65 /*SRW.REFERENCE(ROUTINGCP)*/NULL;
66 IF FROM_ROUTING IS NOT NULL AND TO_ROUTING IS NOT NULL THEN
67 ROUTINGCP := ' and frh.routing_no >= ''' || FROM_ROUTING || ''' and frh.routing_no <= ''' || TO_ROUTING || '''';
68 ELSIF FROM_ROUTING IS NULL AND TO_ROUTING IS NULL THEN
69 --ROUTINGCP := NULL;
70 ROUTINGCP := ' ';
71 ELSIF FROM_ROUTING IS NOT NULL AND TO_ROUTING IS NULL THEN
72 ROUTINGCP := ' and frh.routing_no >= ''' || FROM_ROUTING || '''';
73 ELSIF FROM_ROUTING IS NULL AND TO_ROUTING IS NOT NULL THEN
74 ROUTINGCP := ' and frh.routing_no <= ''' || TO_ROUTING || '''';
75 END IF;
76 RETURN NULL;
77 END ROUTINGCFFORMULA;
78 FUNCTION ROUTING_VERSCFFORMULA RETURN VARCHAR2 IS
79 BEGIN
80 /*SRW.REFERENCE(ROUTING_VERSCP)*/NULL;
81 IF FROM_ROUTING_VERSION IS NOT NULL AND TO_ROUTING_VERSION IS NOT NULL THEN
82 ROUTING_VERSCP := ' and frh.routing_vers >= ''' || FROM_ROUTING_VERSION || ''' and frh.routing_vers <= ''' || TO_ROUTING_VERSION || '''';
83 ELSIF FROM_ROUTING_VERSION IS NULL AND TO_ROUTING_VERSION IS NULL THEN
84 --ROUTING_VERSCP := NULL;
85 ROUTING_VERSCP := ' ';
86 ELSIF FROM_ROUTING_VERSION IS NOT NULL AND TO_ROUTING_VERSION IS NULL THEN
87 ROUTING_VERSCP := ' and frh.routing_vers >= ''' || FROM_ROUTING_VERSION || '''';
88 ELSIF FROM_ROUTING_VERSION IS NULL AND TO_ROUTING_VERSION IS NOT NULL THEN
89 ROUTING_VERSCP := ' and frh.routing_vers <= ''' || TO_ROUTING_VERSION || '''';
90 END IF;
91 RETURN NULL;
92 END ROUTING_VERSCFFORMULA;
93 FUNCTION ITEMCFFORMULA RETURN VARCHAR2 IS
94 BEGIN
95 /*SRW.REFERENCE(ITEMCP)*/NULL;
96 IF FROM_ITEM IS NOT NULL AND TO_ITEM IS NOT NULL THEN
97 ITEMCP := ' and msi.concatenated_segments >= ''' || FROM_ITEMCP || ''' and msi.concatenated_segments <= ''' || TO_ITEMCP || '''';
98 ELSIF FROM_ITEM IS NULL AND TO_ITEM IS NULL THEN
99 --ITEMCP := NULL;
100 ITEMCP := ' ';
101 ELSIF FROM_ITEM IS NOT NULL AND TO_ITEM IS NULL THEN
102 ITEMCP := ' and msi.concatenated_segments >= ''' || FROM_ITEMCP || '''';
103 ELSIF FROM_ITEM IS NULL AND TO_ITEM IS NOT NULL THEN
104 ITEMCP := ' and msi.concatenated_segments <= ''' || TO_ITEMCP || '''';
105 END IF;
106 /*SRW.MESSAGE(1000
107 ,':ItemCP ' || ITEMCP)*/NULL;
108 RETURN NULL;
109 END ITEMCFFORMULA;
110 FUNCTION DATE1CFFORMULA RETURN VARCHAR2 IS
111 BEGIN
112 /*SRW.REFERENCE(DATE1CP)*/NULL;
113 IF FROM_RESULT_DATE IS NOT NULL AND TO_RESULT_DATE IS NOT NULL THEN
114 DATE1CP := ' and trunc(gr1.result_date) >= FND_DATE.CHARDATE_TO_DATE(''' || FROM_RSLT_DTCP || ''') and trunc(gr1.result_date) <= FND_DATE.CHARDATE_TO_DATE(''' || TO_RSLT_DTCP || '''' || ')';
115 ELSIF FROM_RESULT_DATE IS NULL AND TO_RESULT_DATE IS NULL THEN
116 --DATE1CP := NULL;
117 DATE1CP := ' ';
118 ELSIF FROM_RESULT_DATE IS NOT NULL AND TO_RESULT_DATE IS NULL THEN
119 DATE1CP := ' and trunc(gr1.result_date) >= FND_DATE.CHARDATE_TO_DATE(''' || FROM_RSLT_DTCP || '''' || ')';
120 ELSIF FROM_RESULT_DATE IS NULL AND TO_RESULT_DATE IS NOT NULL THEN
121 DATE1CP := ' and trunc(gr1.result_date) <= FND_DATE.CHARDATE_TO_DATE(''' || TO_RSLT_DTCP || '''' || ')';
122 END IF;
123 RETURN NULL;
124 END DATE1CFFORMULA;
125 FUNCTION OPRNCFFORMULA RETURN VARCHAR2 IS
126 BEGIN
127 /*SRW.REFERENCE(OPRNNOCP)*/NULL;
128 IF (FROM_OPERATION IS NOT NULL OR TO_OPERATION IS NOT NULL) THEN
129 OPRNNOCP := ('fom.oprn_no,fom.oprn_vers');
130 ELSE
131 OPRNNOCP := NULL;
132 --OPRNNOCP := ' ';
133 END IF;
134 RETURN NULL;
135 END OPRNCFFORMULA;
136 FUNCTION OPRNCFFORMULA0032 RETURN VARCHAR2 IS
137 BEGIN
138 /*SRW.REFERENCE(OPRNCP)*/NULL;
139 IF FROM_OPERATION IS NOT NULL AND TO_OPERATION IS NOT NULL THEN
140 OPRNCP := ' and fom.oprn_no >= ''' || FROM_OPERATION || ''' and fom.oprn_no <= ''' || TO_OPERATION || ''' and fom.oprn_id = gs.oprn_id ';
141 ELSIF FROM_OPERATION IS NULL AND TO_OPERATION IS NULL THEN
142 --OPRNCP := NULL;
143 OPRNCP := ' ';
144 ELSIF FROM_OPERATION IS NOT NULL AND TO_OPERATION IS NULL THEN
145 OPRNCP := ' and fom.oprn_no >= ''' || FROM_OPERATION || ''' and fom.oprn_id = gs.oprn_id ';
146 ELSIF FROM_OPERATION IS NULL AND TO_OPERATION IS NOT NULL THEN
147 OPRNCP := ' and fom.oprn_no <= ''' || TO_OPERATION || ''' and fom.oprn_id = gs.oprn_id ';
148 END IF;
149 RETURN NULL;
150 END OPRNCFFORMULA0032;
151 FUNCTION OPRNTBLCFFORMULA RETURN VARCHAR2 IS
152 BEGIN
153 /*SRW.REFERENCE(OPRNTBLCP)*/NULL;
154 IF (FROM_OPERATION IS NOT NULL OR TO_OPERATION IS NOT NULL) THEN
155 OPRNTBLCP := (',gmd_operations_b fom ');
156 ELSE
157 --OPRNTBLCP := NULL;
158 OPRNTBLCP := ' ';
159 END IF;
160 RETURN NULL;
161 END OPRNTBLCFFORMULA;
162 FUNCTION ROUTSTEPCFFORMULA RETURN VARCHAR2 IS
163 BEGIN
164 /*SRW.REFERENCE(ROUTSTEPCP)*/NULL;
165 IF (FROM_ROUTING_STEP_NO IS NOT NULL OR TO_ROUTING_STEP_NO IS NOT NULL) THEN
166 ROUTSTEPCP := ('frd.routingstep_no ');
167 ELSE
168 ROUTSTEPCP := NULL;
169 -- ROUTSTEPCP := ' ';
170 END IF;
171 RETURN NULL;
172 END ROUTSTEPCFFORMULA;
173 FUNCTION ROUTSTEPTBLCFFORMULA RETURN VARCHAR2 IS
174 BEGIN
175 /*SRW.REFERENCE(ROUTSTEPTBLCP)*/NULL;
176 IF (FROM_ROUTING_STEP_NO IS NOT NULL OR TO_ROUTING_STEP_NO IS NOT NULL) THEN
177 ROUTSTEPTBLCP := (',fm_rout_dtl frd');
178 ELSE
179 --ROUTSTEPTBLCP := NULL;
180 ROUTSTEPTBLCP := ' ';
181 END IF;
182 RETURN NULL;
183 END ROUTSTEPTBLCFFORMULA;
184 FUNCTION ROUT_STEPNOCFFORMULA RETURN VARCHAR2 IS
185 BEGIN
186 /*SRW.REFERENCE(ROUT_STEPNOCP)*/NULL;
187 IF FROM_ROUTING_STEP_NO IS NOT NULL AND TO_ROUTING_STEP_NO IS NOT NULL THEN
188 ROUT_STEPNOCP := ' and frd.routingstep_no >= ' || TO_CHAR(FROM_ROUTING_STEP_NO) || ' and frd.routingstep_no <= ' || TO_CHAR(TO_ROUTING_STEP_NO) || ' and frd.routing_id = gs.routing_id ' || ' and frd.routingstep_id = gs.step_id';
189 ELSIF FROM_ROUTING_STEP_NO IS NULL AND TO_ROUTING_STEP_NO IS NULL THEN
190 --ROUT_STEPNOCP := NULL;
191 ROUT_STEPNOCP := ' ';
192 ELSIF FROM_ROUTING_STEP_NO IS NOT NULL AND TO_ROUTING_STEP_NO IS NULL THEN
193 ROUT_STEPNOCP := ' and frd.routingstep_no >= ' || TO_CHAR(FROM_ROUTING_STEP_NO) || ' and frd.routing_id = gs.routing_id ' || ' and frd.routingstep_id = gs.step_id';
194 ELSIF FROM_ROUTING_STEP_NO IS NULL AND TO_ROUTING_STEP_NO IS NOT NULL THEN
195 ROUT_STEPNOCP := ' and frd.routingstep_no <= ' || TO_CHAR(TO_ROUTING_STEP_NO) || ' and frd.routing_id = gs.routing_id ' || ' and frd.routingstep_id = gs.step_id';
196 END IF;
197 RETURN NULL;
198 END ROUT_STEPNOCFFORMULA;
199 FUNCTION ORDERCFFORMULA RETURN VARCHAR2 IS
200 BEGIN
201 /*SRW.REFERENCE(ORDERCP)*/NULL;
202 IF OPRNNOCP IS NOT NULL AND ROUTSTEPCP IS NOT NULL THEN
203 ORDERCP := 'fom.oprn_no,fom.oprn_vers,frd.routingstep_no,';
204 ELSIF OPRNNOCP IS NOT NULL AND ROUTSTEPCP IS NULL THEN
205 ORDERCP := 'fom.oprn_no,fom.oprn_vers,';
206 ELSIF OPRNNOCP IS NULL AND ROUTSTEPCP IS NOT NULL THEN
207 ORDERCP := 'frd.routingstep_no,';
208 ELSIF OPRNNOCP IS NULL AND ROUTSTEPCP IS NULL THEN
209 --ORDERCP := NULL;
210 ORDERCP := ' ';
211 END IF;
212 RETURN NULL;
213 END ORDERCFFORMULA;
214 FUNCTION FINAL1CFFORMULA RETURN VARCHAR2 IS
215 BEGIN
216 /*SRW.REFERENCE(FINAL1CP)*/NULL;
217 IF INCLUDE = '0' THEN
218 --FINAL1CP := NULL;
219 FINAL1CP := ' ';
220 ELSIF INCLUDE = '1' THEN
221 FINAL1CP := ' and decode(gr1.ad_hoc_print_on_coa_ind, ''Y'', decode( gsr.evaluation_ind,''0A'',''Y'',''1V'',''Y'',''2R'',''Y'',''N''),''N'') =''Y''';
222 END IF;
223 RETURN NULL;
224 END FINAL1CFFORMULA;
225 FUNCTION ASSAY_DESCCFFORMULA(ASSAY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
226 ASSAYDESC VARCHAR2(42);
227 BEGIN
228 SELECT
229 '[' || TEST_DESC || ']'
230 INTO ASSAYDESC
231 FROM
232 GMD_QC_TESTS
233 WHERE TEST_CODE = ASSAY_CODE;
234 RETURN (ASSAYDESC);
235 EXCEPTION
236 WHEN OTHERS THEN
237 RETURN NULL;
238 END ASSAY_DESCCFFORMULA;
239 FUNCTION TEXTCFFORMULA(TEXT_SPEC IN VARCHAR2) RETURN VARCHAR2 IS
240 BEGIN
241 IF TEXT_SPEC IS NULL THEN
242 RETURN (NULL);
243 ELSE
244 RETURN (TEXT_SPEC);
245 END IF;
246 END TEXTCFFORMULA;
247 FUNCTION TARGETCFFORMULA(TEXT_SPEC IN VARCHAR2
248 ,TARGET_SPEC IN NUMBER
249 ,TEST_TYPE IN VARCHAR2
250 ,PRECISION IN NUMBER) RETURN CHAR IS
251 L_PRECISION VARCHAR2(25);
252 L_NUMBER NUMBER;
253 L_TARGET_SPEC VARCHAR2(25);
254 BEGIN
255 IF TEXT_SPEC IS NOT NULL THEN
256 RETURN (NULL);
257 ELSE
258 IF TARGET_SPEC IS NOT NULL THEN
259 IF TEST_TYPE in ('N','L','E') THEN
260 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
261 ,PRECISION) - 1);
262 L_NUMBER := TARGET_SPEC;
263 L_TARGET_SPEC := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
264 ,L_PRECISION));
265 RETURN (L_TARGET_SPEC);
266 ELSE
267 L_NUMBER := TEXT_SPEC;
268 RETURN TO_CHAR(L_NUMBER);
269 END IF;
270 ELSE
271 RETURN NULL;
272 END IF;
273 END IF;
274 END TARGETCFFORMULA;
275 FUNCTION QCUNIT_CODECFFORMULA(QC_SPEC_ID IN NUMBER
276 ,ASSAY_CODE IN VARCHAR2
277 ,QCASSY_TYP_ID IN NUMBER) RETURN VARCHAR2 IS
278 QCUNIT VARCHAR2(25);
279 BEGIN
280 IF QC_SPEC_ID IS NULL THEN
281 BEGIN
282 SELECT
283 TEST_UNIT
284 INTO QCUNIT
285 FROM
286 GMD_QC_TESTS_B
287 WHERE TEST_CODE = ASSAY_CODE;
288 EXCEPTION
289 WHEN NO_DATA_FOUND THEN
290 RETURN (NULL);
291 END;
292 ELSE
293 BEGIN
294 SELECT
295 TEST_UNIT
296 INTO QCUNIT
297 FROM
298 GMD_QC_TESTS
299 WHERE TEST_ID = QCASSY_TYP_ID;
300 END;
301 RETURN (QCUNIT);
302 END IF;
303 RETURN NULL;
304 EXCEPTION
305 WHEN NO_DATA_FOUND THEN
306 RETURN (NULL);
307 END QCUNIT_CODECFFORMULA;
308 FUNCTION MIN_SPECCFFORMULA(MIN_CHAR IN VARCHAR2
309 ,MIN_SPEC IN NUMBER
310 ,TEST_TYPE IN VARCHAR2
311 ,PRECISION IN NUMBER) RETURN CHAR IS
312 L_PRECISION VARCHAR2(25);
313 L_NUMBER NUMBER;
314 L_MIN_SPEC VARCHAR2(25);
315 ACCEPTABLE_RANGE NUMBER;
316 BEGIN
317 ACCEPTABLE_RANGE := NULL;
318 IF MIN_CHAR IS NOT NULL THEN
319 RETURN (NULL);
320 ELSE
321 IF MIN_SPEC IS NOT NULL THEN
322 IF TEST_TYPE in ('N','L','E') THEN
323 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
324 ,PRECISION) - 1);
325 L_NUMBER := MIN_SPEC;
326 L_MIN_SPEC := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
327 ,L_PRECISION));
328 RETURN (L_MIN_SPEC);
329 ELSE
330 L_NUMBER := MIN_CHAR;
331 RETURN TO_CHAR(L_NUMBER);
332 END IF;
333 ELSE
334 RETURN NULL;
335 END IF;
336 END IF;
337 END MIN_SPECCFFORMULA;
338 FUNCTION MAX_SPECCFFORMULA(MAX_CHAR IN VARCHAR2
339 ,MAX_SPEC IN NUMBER
340 ,TEST_TYPE IN VARCHAR2
341 ,PRECISION IN NUMBER) RETURN CHAR IS
342 L_PRECISION VARCHAR2(25);
343 L_NUMBER NUMBER;
344 L_MAX_SPEC VARCHAR2(25);
345 ACCEPTABLE_RANGE NUMBER;
346 BEGIN
347 ACCEPTABLE_RANGE := NULL;
348 IF MAX_CHAR IS NOT NULL THEN
349 RETURN (NULL);
350 ELSE
351 IF MAX_SPEC IS NOT NULL THEN
352 IF TEST_TYPE in ('N','L','E') THEN
353 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
354 ,PRECISION) - 1);
355 L_NUMBER := MAX_SPEC;
356 L_MAX_SPEC := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
357 ,L_PRECISION));
358 RETURN (L_MAX_SPEC);
359 ELSE
360 L_NUMBER := MAX_CHAR;
361 RETURN TO_CHAR(L_NUMBER);
362 END IF;
363 ELSE
364 RETURN NULL;
365 END IF;
366 END IF;
367 END MAX_SPECCFFORMULA;
368 --FUNCTION FROM_DATECFFORMULA(SAMPLE_NO IN VARCHAR2) RETURN DATE IS
369 FUNCTION FROM_DATECFFORMULA(SAMPLE_NO_V IN VARCHAR2) RETURN DATE IS
370 FROMDATE DATE;
371 BEGIN
372 SELECT
373 DISTINCT
374 GISV.START_DATE
375 INTO FROMDATE
376 FROM
377 GMD_SAMPLES GS,
378 GMD_SAMPLING_EVENTS GSE,
379 GMD_WIP_SPEC_VRS GISV,
380 GMD_EVENT_SPEC_DISP GES
381 WHERE SAMPLE_NO = SAMPLE_NO_V
382 AND GS.SAMPLING_EVENT_ID = GSE.SAMPLING_EVENT_ID
383 AND GSE.SAMPLING_EVENT_ID = GES.SAMPLING_EVENT_ID
384 AND GES.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
385 AND GES.SPEC_VR_ID = GISV.SPEC_VR_ID;
386 RETURN (FROMDATE);
387 RETURN NULL;
388 EXCEPTION
389 WHEN OTHERS THEN
390 RETURN (NULL);
391 END FROM_DATECFFORMULA;
392 --FUNCTION TO_DATECFFORMULA(SAMPLE_NO IN VARCHAR2) RETURN DATE IS
393 FUNCTION TO_DATECFFORMULA(SAMPLE_NO_V IN VARCHAR2) RETURN DATE IS
394 TODATE DATE;
395 BEGIN
396 SELECT
397 GISV.END_DATE
398 INTO TODATE
399 FROM
400 GMD_SAMPLES GS,
401 GMD_SAMPLING_EVENTS GSE,
402 GMD_WIP_SPEC_VRS GISV,
403 GMD_EVENT_SPEC_DISP GES
404 WHERE SAMPLE_NO = SAMPLE_NO_V
405 AND GS.SAMPLING_EVENT_ID = GSE.SAMPLING_EVENT_ID
406 AND GSE.SAMPLING_EVENT_ID = GES.SAMPLING_EVENT_ID
407 AND GES.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
408 AND GES.SPEC_VR_ID = GISV.SPEC_VR_ID;
409 RETURN (TODATE);
410 EXCEPTION
411 WHEN OTHERS THEN
412 RETURN (NULL);
413 END TO_DATECFFORMULA;
414 FUNCTION TEXTARRAYCFFORMULA(TEXTCODECF IN NUMBER) RETURN VARCHAR2 IS
415 TEXT1 VARCHAR2(116);
416 TEXT2 VARCHAR2(2000);
417 CURSOR GET_TEXT1 IS
418 SELECT
419 TEXT
420 FROM
421 QC_TEXT_TBL
422 WHERE TEXT_CODE = TEXTCODECF
423 AND LINE_NO <> - 1
424 AND LANG_CODE = 'US';
425 BEGIN
426 OPEN GET_TEXT1;
427 LOOP
428 FETCH GET_TEXT1
429 INTO TEXT1;
430 EXIT WHEN GET_TEXT1%NOTFOUND;
431 TEXT2 := TEXT2 || ' ' || TEXT1;
432 END LOOP;
433 CLOSE GET_TEXT1;
434 IF SQL%NOTFOUND THEN
435 RETURN (NULL);
436 END IF;
437 RETURN (TEXT2);
438 RETURN NULL;
439 EXCEPTION
440 WHEN NO_DATA_FOUND THEN
441 RETURN (NULL);
442 END TEXTARRAYCFFORMULA;
443 FUNCTION TEXT_CODECFFORMULA(QC_SPEC_ID IN NUMBER) RETURN NUMBER IS
444 TEXTCODE NUMBER;
445 BEGIN
446 SELECT
447 TEXT_CODE
448 INTO TEXTCODE
449 FROM
450 GMD_SPECIFICATIONS_B
451 WHERE SPEC_ID = QC_SPEC_ID;
452 RETURN (TEXTCODE);
453 RETURN NULL;
454 EXCEPTION
455 WHEN NO_DATA_FOUND THEN
456 RETURN (NULL);
457 END TEXT_CODECFFORMULA;
458 --FUNCTION TEXTARRAY2CFFORMULA(TEXT_CODE IN NUMBER) RETURN VARCHAR2 IS
459 FUNCTION TEXTARRAY2CFFORMULA(TEXT_CODE_V IN NUMBER) RETURN VARCHAR2 IS
460 TEXT2 VARCHAR2(116);
461 TEXT3 VARCHAR2(2000);
462 CURSOR GET_TEXT IS
463 SELECT
464 TEXT
465 FROM
466 QC_TEXT_TBL
467 WHERE TEXT_CODE = TEXT_CODE_V
468 AND LINE_NO <> - 1
469 AND LANG_CODE = 'US';
470 BEGIN
471 OPEN GET_TEXT;
472 LOOP
473 FETCH GET_TEXT
474 INTO TEXT2;
475 EXIT WHEN GET_TEXT%NOTFOUND;
476 TEXT3 := TEXT3 || TEXT2;
477 END LOOP;
478 CLOSE GET_TEXT;
479 IF SQL%NOTFOUND THEN
480 RETURN (NULL);
481 END IF;
482 RETURN (TEXT3);
483 RETURN NULL;
484 EXCEPTION
485 WHEN NO_DATA_FOUND THEN
486 RETURN (NULL);
487 END TEXTARRAY2CFFORMULA;
488 /*FUNCTION STEPCFFORMULA(ROUTING_ID IN NUMBER
489 ,ROUTINGSTEP_ID IN NUMBER) RETURN NUMBER IS*/
490 FUNCTION STEPCFFORMULA(ROUTING_ID_V IN NUMBER
491 ,ROUTINGSTEP_ID_V IN NUMBER) RETURN NUMBER IS
492 ROUTSTEP NUMBER;
493 BEGIN
494 SELECT
495 ROUTINGSTEP_NO
496 INTO ROUTSTEP
497 FROM
498 FM_ROUT_DTL
499 WHERE ROUTING_ID = ROUTING_ID_V
500 AND ROUTINGSTEP_ID = ROUTINGSTEP_ID_V;
501 RETURN (ROUTSTEP);
502 RETURN NULL;
503 EXCEPTION
504 WHEN NO_DATA_FOUND THEN
505 RETURN (NULL);
506 END STEPCFFORMULA;
507 --FUNCTION OPERATIONCFFORMULA(OPRN_ID IN NUMBER) RETURN VARCHAR2 IS
508 FUNCTION OPERATIONCFFORMULA(OPRN_ID_V IN NUMBER) RETURN VARCHAR2 IS
509 OPERATION VARCHAR2(32);
510 BEGIN
511 SELECT
512 OPRN_NO
513 INTO OPERATION
514 FROM
515 GMD_OPERATIONS_B
516 WHERE OPRN_ID = OPRN_ID_V;
517 RETURN (OPERATION);
518 RETURN NULL;
519 EXCEPTION
520 WHEN NO_DATA_FOUND THEN
521 RETURN (NULL);
522 END OPERATIONCFFORMULA;
523 FUNCTION FROM_ITEMCFFORMULA RETURN VARCHAR2 IS
524 L_FROM_ITEM VARCHAR2(240);
525 BEGIN
526 IF FROM_ITEM IS NULL THEN
527 FROM_ITEMCP := SY_ALL;
528 ELSE
529 SELECT
530 DISTINCT
531 CONCATENATED_SEGMENTS
532 INTO L_FROM_ITEM
533 FROM
534 MTL_SYSTEM_ITEMS_KFV
535 WHERE INVENTORY_ITEM_ID = FROM_ITEM;
536 FROM_ITEMCP := L_FROM_ITEM;
537 END IF;
538 /*SRW.MESSAGE(1000
539 ,':FROM_ITEMCP ' || FROM_ITEMCP)*/NULL;
540 RETURN (FROM_ITEMCP);
541 END FROM_ITEMCFFORMULA;
542 FUNCTION TO_ITEMCFFORMULA RETURN VARCHAR2 IS
543 L_TO_ITEM VARCHAR2(240);
544 BEGIN
545 IF TO_ITEM IS NULL THEN
546 TO_ITEMCP := SY_ALL;
547 ELSE
548 SELECT
549 DISTINCT
550 CONCATENATED_SEGMENTS
551 INTO L_TO_ITEM
552 FROM
553 MTL_SYSTEM_ITEMS_KFV
554 WHERE INVENTORY_ITEM_ID = TO_ITEM;
555 TO_ITEMCP := L_TO_ITEM;
556 END IF;
557 /*SRW.MESSAGE(1000
558 ,':TO_ITEMCP ' || TO_ITEMCP)*/NULL;
559 RETURN (TO_ITEMCP);
560 END TO_ITEMCFFORMULA;
561 FUNCTION FROM_SAMPLECFFORMULA RETURN VARCHAR2 IS
562 BEGIN
563 IF FROM_SAMPLE IS NULL THEN
564 FROM_SAMPLECP := SY_ALL;
565 ELSE
566 FROM_SAMPLECP := FROM_SAMPLE;
567 END IF;
568 RETURN (FROM_SAMPLECP);
569 END FROM_SAMPLECFFORMULA;
570 FUNCTION TO_SAMPLECFFORMULA RETURN VARCHAR2 IS
571 BEGIN
572 IF TO_SAMPLE IS NULL THEN
573 TO_SAMPLECP := SY_ALL;
574 ELSE
575 TO_SAMPLECP := TO_SAMPLE;
576 END IF;
577 RETURN (TO_SAMPLECP);
578 END TO_SAMPLECFFORMULA;
579 FUNCTION FROM_RSLT_DTCFFORMULA RETURN VARCHAR2 IS
580 BEGIN
581 IF FROM_RESULT_DATE IS NULL THEN
582 FROM_RSLT_DTCP := SY_ALL;
583 ELSE
584 FROM_RSLT_DTCP := TO_CHAR(FROM_RESULT_DATE
585 ,'DD-MON-YYYY');
586 END IF;
587 RETURN (FROM_RSLT_DTCP);
588 END FROM_RSLT_DTCFFORMULA;
589 FUNCTION TO_RSLT_DTCFFORMULA RETURN VARCHAR2 IS
590 BEGIN
591 IF TO_RESULT_DATE IS NULL THEN
592 TO_RSLT_DTCP := SY_ALL;
593 ELSE
594 TO_RSLT_DTCP := TO_CHAR(TO_RESULT_DATE
595 ,'DD-MON-YYYY');
596 END IF;
597 RETURN (TO_RSLT_DTCP);
598 END TO_RSLT_DTCFFORMULA;
599 FUNCTION FROM_FORMULACFFORMULA RETURN VARCHAR2 IS
600 BEGIN
601 IF FROM_FORMULA IS NULL THEN
602 FROM_FORMULACP := SY_ALL;
603 ELSE
604 FROM_FORMULACP := FROM_FORMULA;
605 END IF;
606 RETURN (FROM_FORMULACP);
607 END FROM_FORMULACFFORMULA;
608 FUNCTION TO_FORMULACFFORMULA RETURN VARCHAR2 IS
609 BEGIN
610 IF TO_FORMULA IS NULL THEN
611 TO_FORMULACP := SY_ALL;
612 ELSE
613 TO_FORMULACP := TO_FORMULA;
614 END IF;
615 RETURN (TO_FORMULACP);
616 END TO_FORMULACFFORMULA;
617 FUNCTION FROM_BATCHCFFORMULA RETURN VARCHAR2 IS
618 BEGIN
619 IF FROM_BATCH IS NULL THEN
620 FROM_BATCHCP := SY_ALL;
621 ELSE
622 FROM_BATCHCP := FROM_BATCH;
623 END IF;
624 RETURN (FROM_BATCHCP);
625 END FROM_BATCHCFFORMULA;
626 FUNCTION TO_BATCHCFFORMULA RETURN VARCHAR2 IS
627 BEGIN
628 IF TO_BATCH IS NULL THEN
629 TO_BATCHCP := SY_ALL;
630 ELSE
631 TO_BATCHCP := TO_BATCH;
632 END IF;
633 RETURN (TO_BATCHCP);
634 END TO_BATCHCFFORMULA;
635 FUNCTION FROM_OPERATIONCFFORMULA RETURN VARCHAR2 IS
636 BEGIN
637 IF FROM_OPERATION IS NULL THEN
638 FROM_OPERATIONCP := SY_ALL;
639 ELSE
640 FROM_OPERATIONCP := FROM_OPERATION;
641 END IF;
642 RETURN (FROM_OPERATIONCP);
643 END FROM_OPERATIONCFFORMULA;
644 FUNCTION TO_OPERATIONCFFORMULA RETURN VARCHAR2 IS
645 BEGIN
646 IF TO_OPERATION IS NULL THEN
647 TO_OPERATIONCP := SY_ALL;
648 ELSE
649 TO_OPERATIONCP := TO_OPERATION;
650 END IF;
651 RETURN (TO_OPERATIONCP);
652 END TO_OPERATIONCFFORMULA;
653 FUNCTION FROM_ROUTINGCFFORMULA RETURN VARCHAR2 IS
654 BEGIN
655 IF FROM_ROUTING IS NULL THEN
656 FROM_ROUTINGCP := SY_ALL;
657 ELSE
658 FROM_ROUTINGCP := FROM_ROUTING;
659 END IF;
660 RETURN (FROM_ROUTINGCP);
661 END FROM_ROUTINGCFFORMULA;
662 FUNCTION TO_ROUTINGCFFORMULA RETURN VARCHAR2 IS
663 BEGIN
664 IF TO_ROUTING IS NULL THEN
665 TO_ROUTINGCP := SY_ALL;
666 ELSE
667 TO_ROUTINGCP := TO_ROUTING;
668 END IF;
669 RETURN (TO_ROUTINGCP);
670 END TO_ROUTINGCFFORMULA;
671 FUNCTION FROM_ROUTING_STEPNOCFFORMULA RETURN VARCHAR2 IS
672 BEGIN
673 IF FROM_ROUTING_STEP_NO IS NULL THEN
674 FROM_ROUTING_STEP_NOCP := SY_ALL;
675 ELSE
676 FROM_ROUTING_STEP_NOCP := FROM_ROUTING_STEP_NO;
677 END IF;
678 RETURN (FROM_ROUTING_STEP_NOCP);
679 END FROM_ROUTING_STEPNOCFFORMULA;
680 FUNCTION TO_ROUTING_STEP_NOCFFORMULA RETURN VARCHAR2 IS
681 BEGIN
682 IF TO_ROUTING_STEP_NO IS NULL THEN
683 TO_ROUTING_STEP_NOCP := SY_ALL;
684 ELSE
685 TO_ROUTING_STEP_NOCP := TO_ROUTING_STEP_NO;
686 END IF;
687 RETURN (TO_ROUTING_STEP_NOCP);
688 END TO_ROUTING_STEP_NOCFFORMULA;
689 FUNCTION FROM_ROUTING_VERSIONCFFORMULA RETURN VARCHAR2 IS
690 BEGIN
691 IF FROM_ROUTING_VERSION IS NULL THEN
692 FROM_ROUTING_VERSIONCP := SY_ALL;
693 ELSE
694 FROM_ROUTING_VERSIONCP := FROM_ROUTING_VERSION;
695 END IF;
696 RETURN (FROM_ROUTING_VERSIONCP);
697 END FROM_ROUTING_VERSIONCFFORMULA;
698 FUNCTION TO_ROUTING_VERSIONCFFORMULA RETURN VARCHAR2 IS
699 BEGIN
700 IF TO_ROUTING_VERSION IS NULL THEN
701 TO_ROUTING_VERSIONCP := SY_ALL;
702 ELSE
706 END TO_ROUTING_VERSIONCFFORMULA;
703 TO_ROUTING_VERSIONCP := TO_ROUTING_VERSION;
704 END IF;
705 RETURN (TO_ROUTING_VERSIONCP);
707 FUNCTION FROM_FORMULA_VERSIONCFFORMULA RETURN VARCHAR2 IS
708 BEGIN
709 IF FROM_FORMULA_VERSION IS NULL THEN
710 FROM_FORMULA_VERSIONCP := SY_ALL;
711 ELSE
712 FROM_FORMULA_VERSIONCP := FROM_FORMULA_VERSION;
713 END IF;
714 RETURN (FROM_FORMULA_VERSIONCP);
715 END FROM_FORMULA_VERSIONCFFORMULA;
716 FUNCTION TO_FORMULA_VERSIONCFFORMULA RETURN VARCHAR2 IS
717 BEGIN
718 IF TO_FORMULA_VERSION IS NULL THEN
719 TO_FORMULA_VERSIONCP := SY_ALL;
720 ELSE
721 TO_FORMULA_VERSIONCP := TO_FORMULA_VERSION;
722 END IF;
723 RETURN (TO_FORMULA_VERSIONCP);
724 END TO_FORMULA_VERSIONCFFORMULA;
725 FUNCTION TEXTCODERSLTCFFORMULA(QC_RESULT_ID IN NUMBER
726 ,ASSAY_CODE IN VARCHAR2) RETURN NUMBER IS
727 TEXTCODE NUMBER;
728 BEGIN
729 SELECT
730 GR1.TEXT_CODE
731 INTO TEXTCODE
732 FROM
733 GMD_RESULTS GR1,
734 GMD_QC_TESTS_B GT
735 WHERE GR1.RESULT_ID = QC_RESULT_ID
736 AND GR1.TEST_ID = GT.TEST_ID
737 AND GT.TEST_CODE = ASSAY_CODE;
738 RETURN (TEXTCODE);
739 RETURN NULL;
740 EXCEPTION
741 WHEN NO_DATA_FOUND THEN
742 RETURN (NULL);
743 END TEXTCODERSLTCFFORMULA;
744 FUNCTION INCLUDE_TEXTCFFORMULA RETURN VARCHAR2 IS
745 INCLUDE1 VARCHAR2(80);
746 BEGIN
747 IF INCLUDE = '0' THEN
748 SELECT
749 MEANING
750 INTO INCLUDE1
751 FROM
752 GEM_LOOKUPS
753 WHERE LOOKUP_CODE = '0'
754 AND LOOKUP_TYPE = 'QC_INCLUDE';
755 ELSIF INCLUDE = '1' THEN
756 SELECT
757 MEANING
758 INTO INCLUDE1
759 FROM
760 GEM_LOOKUPS
761 WHERE LOOKUP_CODE = '1'
762 AND LOOKUP_TYPE = 'QC_INCLUDE';
763 END IF;
764 RETURN (INCLUDE1);
765 END INCLUDE_TEXTCFFORMULA;
766 FUNCTION PRINT_TEXTCFFORMULA RETURN VARCHAR2 IS
767 PRINT1 VARCHAR2(80);
768 BEGIN
769 IF PRINT_CONDITION = '0' THEN
770 SELECT
771 MEANING
772 INTO PRINT1
773 FROM
774 GEM_LOOKUPS
775 WHERE LOOKUP_CODE = '0'
776 AND LOOKUP_TYPE = 'QC_PRINT';
777 ELSIF PRINT_CONDITION = '1' THEN
778 SELECT
779 MEANING
780 INTO PRINT1
781 FROM
782 GEM_LOOKUPS
783 WHERE LOOKUP_CODE = '1'
784 AND LOOKUP_TYPE = 'QC_PRINT';
785 ELSIF PRINT_CONDITION = '2' THEN
786 SELECT
787 MEANING
788 INTO PRINT1
789 FROM
790 GEM_LOOKUPS
791 WHERE LOOKUP_CODE = '2'
792 AND LOOKUP_TYPE = 'QC_PRINT';
793 ELSIF PRINT_CONDITION = '3' THEN
794 SELECT
795 MEANING
796 INTO PRINT1
797 FROM
798 GEM_LOOKUPS
799 WHERE LOOKUP_CODE = '3'
800 AND LOOKUP_TYPE = 'QC_PRINT';
801 END IF;
802 RETURN (PRINT1);
803 END PRINT_TEXTCFFORMULA;
804 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
805 BEGIN
806 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
807 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
808 RETURN (TRUE);
809 END BEFOREREPORT;
810 FUNCTION ACCEPT_CFFORMULA(ACCEPT_ANYWAY IN VARCHAR2 ) RETURN VARCHAR2 IS
811 TEMP1 VARCHAR2(80);
812 BEGIN
813 SELECT
814 MEANING
815 INTO TEMP1
816 FROM
817 FND_LOOKUP_VALUES
818 WHERE LOOKUP_TYPE = 'GMD_QC_YES_NO'
819 AND LOOKUP_CODE = ACCEPT_ANYWAY
820 AND LANGUAGE = USERENV('LANG');
821 ACCEPT_CP := TEMP1;
822 RETURN ACCEPT_CP;
823 END ACCEPT_CFFORMULA;
824 FUNCTION FINAL_CFFORMULA(FINAL_MARK IN VARCHAR2) RETURN VARCHAR2 IS
825 TEMP2 VARCHAR2(80);
826 BEGIN
827 SELECT
828 MEANING
829 INTO TEMP2
830 FROM
831 FND_LOOKUP_VALUES
832 WHERE LOOKUP_TYPE = 'GMD_QC_YES_NO'
833 AND LOOKUP_CODE = FINAL_MARK
834 AND LANGUAGE = USERENV('LANG');
835 FINAL_CP := TEMP2;
836 RETURN FINAL_CP;
837 END FINAL_CFFORMULA;
838 PROCEDURE GMD_QCR03USR_XMLP_PKG_HEADER IS
839 BEGIN
840 NULL;
841 END GMD_QCR03USR_XMLP_PKG_HEADER;
842 FUNCTION FROM_LOTNOCFFORMULA RETURN VARCHAR2 IS
843 BEGIN
844 IF FROM_LOTNO IS NULL THEN
845 FROM_LOTNOCP := SY_ALL;
846 ELSE
847 FROM_LOTNOCP := FROM_LOTNO;
848 END IF;
849 /*SRW.MESSAGE(1000
850 ,':FROM_LOTNOCP ' || FROM_LOTNOCP)*/NULL;
851 RETURN (FROM_LOTNOCP);
852 END FROM_LOTNOCFFORMULA;
853 FUNCTION LOTCFFORMULA RETURN VARCHAR2 IS
854 BEGIN
855 /*SRW.REFERENCE(LOTNOCP)*/NULL;
856 IF FROM_LOTNO IS NOT NULL AND TO_LOTNO IS NOT NULL THEN
857 LOTNOCP := ' and gs.lot_number >= ''' || FROM_LOTNO || ''' and gs.lot_number <= ''' || TO_LOTNO || '''';
858 ELSIF FROM_LOTNO IS NULL AND TO_LOTNO IS NULL THEN
859 --LOTNOCP := NULL;
860 LOTNOCP := ' ';
861 ELSIF FROM_LOTNO IS NOT NULL AND TO_LOTNO IS NULL THEN
862 LOTNOCP := ' and gs.lot_number >= ''' || FROM_LOTNO || '''';
863 ELSIF FROM_LOTNO IS NULL AND TO_LOTNO IS NOT NULL THEN
864 LOTNOCP := ' and gs.lot_number <= ''' || TO_LOTNO || '''';
865 END IF;
866 /*SRW.MESSAGE(1000
867 ,':LotnoCP ' || LOTNOCP)*/NULL;
868 RETURN NULL;
869 END LOTCFFORMULA;
870 FUNCTION TO_LOTNOCFFORMULA RETURN VARCHAR2 IS
871 BEGIN
872 IF TO_LOTNO IS NULL THEN
876 END IF;
873 TO_LOTNOCP := SY_ALL;
874 ELSE
875 TO_LOTNOCP := TO_LOTNO;
877 /*SRW.MESSAGE(1000
878 ,':TO_LOTNOCP ' || TO_LOTNOCP)*/NULL;
879 RETURN (TO_LOTNOCP);
880 END TO_LOTNOCFFORMULA;
881 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
882 BEGIN
883 RETURN (TRUE);
884 END BEFOREPFORM;
885 FUNCTION AFTERPFORM RETURN BOOLEAN IS
886 BEGIN
887 RETURN (TRUE);
888 END AFTERPFORM;
889 --FUNCTION OPRN_VERSCFFORMULA0017(OPRN_ID IN NUMBER) RETURN NUMBER IS
890 FUNCTION OPRN_VERSCFFORMULA0017(OPRN_ID_V IN NUMBER) RETURN NUMBER IS
891 OPRN_VERS NUMBER;
892 BEGIN
893 SELECT
894 OPRN_VERS
895 INTO OPRN_VERS
896 FROM
897 GMD_OPERATIONS_B
898 WHERE OPRN_ID = OPRN_ID_V;
899 RETURN (OPRN_VERS);
900 RETURN NULL;
901 EXCEPTION
902 WHEN NO_DATA_FOUND THEN
903 RETURN (NULL);
904 END OPRN_VERSCFFORMULA0017;
905 FUNCTION ORDERCPFORMULA RETURN CHAR IS
906 BEGIN
907 NULL;
908 END ORDERCPFORMULA;
909 FUNCTION ROUTSTEPCPFORMULA RETURN CHAR IS
910 BEGIN
911 NULL;
912 END ROUTSTEPCPFORMULA;
913 FUNCTION ROUT_STEPNOCPFORMULA RETURN CHAR IS
914 BEGIN
915 NULL;
916 END ROUT_STEPNOCPFORMULA;
917 FUNCTION ASSAY_DISPLAYCFFORMULA(ASSAY_CODE IN VARCHAR2
918 ,QCASSY_TYP_ID IN NUMBER
919 ,TARGETCF IN VARCHAR2) RETURN CHAR IS
920 ASSAYVALUE VARCHAR2(240);
921 ASSAYTYPE VARCHAR2(1);
922 BEGIN
923 BEGIN
924 SELECT
925 DISTINCT
926 TEST_TYPE
927 INTO ASSAYTYPE
928 FROM
929 GMD_QC_TESTS_B
930 WHERE TEST_CODE = ASSAY_CODE;
931 EXCEPTION
932 WHEN NO_DATA_FOUND THEN
933 NULL;
934 END;
935 IF ASSAYTYPE = 'L' THEN
936 BEGIN
937 SELECT
938 DISPLAY_LABEL_NUMERIC_RANGE
939 INTO ASSAYVALUE
940 FROM
941 GMD_QC_TEST_VALUES
942 WHERE TEST_ID = QCASSY_TYP_ID
943 AND ( MIN_NUM <= TARGETCF
944 OR MIN_NUM IS NULL )
945 AND ( MAX_NUM >= TARGETCF
946 OR MAX_NUM IS NULL );
947 EXCEPTION
948 WHEN NO_DATA_FOUND THEN
949 NULL;
950 END;
951 END IF;
952 ASSAY_TYPECP := ASSAYTYPE;
953 RETURN (ASSAYVALUE);
954 END ASSAY_DISPLAYCFFORMULA;
955 FUNCTION ASSAYMIN_DISPCFFORMULA(QCASSY_TYP_ID IN NUMBER
956 ,MIN_SPECCF IN VARCHAR2) RETURN CHAR IS
957 ASSAYVALUE VARCHAR2(240);
958 ASSAYTYPE VARCHAR2(1);
959 BEGIN
960 IF ASSAY_TYPECP = 'L' THEN
961 BEGIN
962 SELECT
963 DISPLAY_LABEL_NUMERIC_RANGE
964 INTO ASSAYVALUE
965 FROM
966 GMD_QC_TEST_VALUES
967 WHERE TEST_ID = QCASSY_TYP_ID
968 AND ( MIN_NUM <= MIN_SPECCF
969 OR MIN_NUM IS NULL )
970 AND ( MAX_NUM >= MIN_SPECCF
971 OR MAX_NUM IS NULL );
972 EXCEPTION
973 WHEN NO_DATA_FOUND THEN
974 ASSAYVALUE := ' ';
975 END;
976 END IF;
977 RETURN (ASSAYVALUE);
978 END ASSAYMIN_DISPCFFORMULA;
979 FUNCTION ASSAYMAX_DISPCFFORMULA(QCASSY_TYP_ID IN NUMBER
980 ,MAX_SPECCF IN VARCHAR2) RETURN CHAR IS
981 ASSAYVALUE VARCHAR2(240);
982 BEGIN
983 IF ASSAY_TYPECP = 'L' THEN
984 BEGIN
985 SELECT
986 DISPLAY_LABEL_NUMERIC_RANGE
987 INTO ASSAYVALUE
988 FROM
989 GMD_QC_TEST_VALUES
990 WHERE TEST_ID = QCASSY_TYP_ID
991 AND ( MIN_NUM <= MAX_SPECCF
992 OR MIN_NUM IS NULL )
993 AND ( MAX_NUM >= MAX_SPECCF
994 OR MAX_NUM IS NULL );
995 EXCEPTION
996 WHEN NO_DATA_FOUND THEN
997 ASSAYVALUE := ' ';
998 END;
999 END IF;
1000 RETURN (ASSAYVALUE);
1001 END ASSAYMAX_DISPCFFORMULA;
1002 FUNCTION RESULT_DISPCFFORMULA(QCASSY_TYP_ID IN NUMBER
1003 ,NUM_RESULT IN NUMBER) RETURN CHAR IS
1004 ASSAYVALUE VARCHAR2(240);
1005 BEGIN
1006 IF ASSAY_TYPECP = 'L' THEN
1007 BEGIN
1008 SELECT
1009 DISPLAY_LABEL_NUMERIC_RANGE
1010 INTO ASSAYVALUE
1011 FROM
1012 GMD_QC_TEST_VALUES
1013 WHERE TEST_ID = QCASSY_TYP_ID
1014 AND ( MIN_NUM <= NUM_RESULT
1015 OR MIN_NUM IS NULL )
1016 AND ( MAX_NUM >= NUM_RESULT
1017 OR MAX_NUM IS NULL );
1018 EXCEPTION
1019 WHEN NO_DATA_FOUND THEN
1020 ASSAYVALUE := ' ';
1021 END;
1022 END IF;
1023 RETURN (ASSAYVALUE);
1024 END RESULT_DISPCFFORMULA;
1025 FUNCTION MAX_CHARCFFORMULA(MAX_CHAR IN VARCHAR2) RETURN CHAR IS
1026 BEGIN
1027 IF MAX_CHAR IS NULL THEN
1028 RETURN (NULL);
1029 ELSE
1030 RETURN (MAX_CHAR);
1031 END IF;
1032 END MAX_CHARCFFORMULA;
1033 FUNCTION MIN_CHARCFFORMULA(MIN_CHAR IN VARCHAR2) RETURN CHAR IS
1034 BEGIN
1035 IF MIN_CHAR IS NULL THEN
1036 RETURN (NULL);
1037 ELSE
1038 RETURN (MIN_CHAR);
1039 END IF;
1040 END MIN_CHARCFFORMULA;
1041 FUNCTION RESULT_NUMCFFORMULA(TEXT_RESULT IN VARCHAR2
1042 ,NUM_RESULT IN NUMBER
1043 ,TEST_TYPE IN VARCHAR2
1044 ,PRECISION IN NUMBER) RETURN CHAR IS
1045 L_PRECISION VARCHAR2(25);
1046 L_NUMBER NUMBER;
1047 L_RESULT_NUM VARCHAR2(25);
1048 BEGIN
1049 IF TEXT_RESULT IS NOT NULL THEN
1050 RETURN (NULL);
1051 ELSE
1052 IF NUM_RESULT IS NOT NULL THEN
1053 IF TEST_TYPE in ('N','L','E') THEN
1054 L_PRECISION := '999999999999999D' || TO_CHAR(POWER(10
1055 ,PRECISION) - 1);
1056 L_NUMBER := NUM_RESULT;
1057 L_RESULT_NUM := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(L_NUMBER))
1058 ,L_PRECISION));
1059 RETURN (L_RESULT_NUM);
1060 ELSE
1061 L_NUMBER := TEXT_RESULT;
1062 RETURN TO_CHAR(L_NUMBER);
1063 END IF;
1064 ELSE
1065 RETURN NULL;
1066 END IF;
1067 END IF;
1068 END RESULT_NUMCFFORMULA;
1069 FUNCTION ORGCFFORMULA RETURN CHAR IS
1070 ORG_CODE VARCHAR2(3);
1071 BEGIN
1072 SELECT
1073 DISTINCT
1074 ORGANIZATION_CODE
1075 INTO ORG_CODE
1076 FROM
1077 MTL_PARAMETERS
1078 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
1079 ORGCP := ORG_CODE;
1080 RETURN (ORGCP);
1081 END ORGCFFORMULA;
1082 FUNCTION AFTERREPORT RETURN BOOLEAN IS
1083 BEGIN
1084 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
1085 RETURN (TRUE);
1086 END AFTERREPORT;
1087 FUNCTION ASSAY_TYPECP_P RETURN VARCHAR2 IS
1088 BEGIN
1089 RETURN ASSAY_TYPECP;
1090 END ASSAY_TYPECP_P;
1091 FUNCTION SAMPLECP_P RETURN VARCHAR2 IS
1092 BEGIN
1093 RETURN SAMPLECP;
1094 END SAMPLECP_P;
1095 FUNCTION BATCHCP_P RETURN VARCHAR2 IS
1096 BEGIN
1097 RETURN BATCHCP;
1098 END BATCHCP_P;
1099 FUNCTION FORMULACP_P RETURN VARCHAR2 IS
1100 BEGIN
1101 RETURN FORMULACP;
1102 END FORMULACP_P;
1103 FUNCTION FORMULA_VERSCP_P RETURN VARCHAR2 IS
1104 BEGIN
1105 RETURN FORMULA_VERSCP;
1106 END FORMULA_VERSCP_P;
1107 FUNCTION ROUTINGCP_P RETURN VARCHAR2 IS
1108 BEGIN
1109 RETURN ROUTINGCP;
1110 END ROUTINGCP_P;
1111 FUNCTION ROUTING_VERSCP_P RETURN VARCHAR2 IS
1112 BEGIN
1113 RETURN ROUTING_VERSCP;
1114 END ROUTING_VERSCP_P;
1115 FUNCTION ITEMCP_P RETURN VARCHAR2 IS
1116 BEGIN
1117 RETURN ITEMCP;
1118 END ITEMCP_P;
1119 FUNCTION DATE1CP_P RETURN VARCHAR2 IS
1120 BEGIN
1121 RETURN DATE1CP;
1122 END DATE1CP_P;
1123 FUNCTION OPRNNOCP_P RETURN VARCHAR2 IS
1124 BEGIN
1125 RETURN OPRNNOCP;
1126 END OPRNNOCP_P;
1127 FUNCTION OPRNTBLCP_P RETURN VARCHAR2 IS
1128 BEGIN
1129 RETURN OPRNTBLCP;
1130 END OPRNTBLCP_P;
1131 FUNCTION OPRNCP_P RETURN VARCHAR2 IS
1132 BEGIN
1133 RETURN OPRNCP;
1134 END OPRNCP_P;
1135 FUNCTION ROUTSTEPCP_P RETURN VARCHAR2 IS
1136 BEGIN
1137 RETURN ROUTSTEPCP;
1138 END ROUTSTEPCP_P;
1139 FUNCTION ROUTSTEPTBLCP_P RETURN VARCHAR2 IS
1140 BEGIN
1141 RETURN ROUTSTEPTBLCP;
1142 END ROUTSTEPTBLCP_P;
1143 FUNCTION ROUT_STEPNOCP_P RETURN VARCHAR2 IS
1144 BEGIN
1145 RETURN ROUT_STEPNOCP;
1146 END ROUT_STEPNOCP_P;
1147 FUNCTION ORDERCP_P RETURN VARCHAR2 IS
1148 BEGIN
1149 RETURN ORDERCP;
1150 END ORDERCP_P;
1151 FUNCTION FINAL1CP_P RETURN VARCHAR2 IS
1152 BEGIN
1153 RETURN FINAL1CP;
1154 END FINAL1CP_P;
1155 FUNCTION FROM_ITEMCP_P RETURN VARCHAR2 IS
1156 BEGIN
1157 RETURN FROM_ITEMCP;
1158 END FROM_ITEMCP_P;
1159 FUNCTION TO_ITEMCP_P RETURN VARCHAR2 IS
1160 BEGIN
1161 RETURN TO_ITEMCP;
1162 END TO_ITEMCP_P;
1163 FUNCTION FROM_SAMPLECP_P RETURN VARCHAR2 IS
1164 BEGIN
1165 RETURN FROM_SAMPLECP;
1166 END FROM_SAMPLECP_P;
1167 FUNCTION TO_SAMPLECP_P RETURN VARCHAR2 IS
1168 BEGIN
1169 RETURN TO_SAMPLECP;
1170 END TO_SAMPLECP_P;
1171 FUNCTION FROM_RSLT_DTCP_P RETURN VARCHAR2 IS
1172 BEGIN
1173 RETURN FROM_RSLT_DTCP;
1174 END FROM_RSLT_DTCP_P;
1175 FUNCTION TO_RSLT_DTCP_P RETURN VARCHAR2 IS
1176 BEGIN
1177 RETURN TO_RSLT_DTCP;
1178 END TO_RSLT_DTCP_P;
1179 FUNCTION FROM_FORMULACP_P RETURN VARCHAR2 IS
1180 BEGIN
1181 RETURN FROM_FORMULACP;
1182 END FROM_FORMULACP_P;
1183 FUNCTION TO_FORMULACP_P RETURN VARCHAR2 IS
1184 BEGIN
1185 RETURN TO_FORMULACP;
1186 END TO_FORMULACP_P;
1187 FUNCTION FROM_BATCHCP_P RETURN VARCHAR2 IS
1188 BEGIN
1189 RETURN FROM_BATCHCP;
1190 END FROM_BATCHCP_P;
1191 FUNCTION TO_BATCHCP_P RETURN VARCHAR2 IS
1192 BEGIN
1193 RETURN TO_BATCHCP;
1194 END TO_BATCHCP_P;
1195 FUNCTION FROM_OPERATIONCP_P RETURN VARCHAR2 IS
1196 BEGIN
1197 RETURN FROM_OPERATIONCP;
1198 END FROM_OPERATIONCP_P;
1199 FUNCTION TO_OPERATIONCP_P RETURN VARCHAR2 IS
1200 BEGIN
1201 RETURN TO_OPERATIONCP;
1202 END TO_OPERATIONCP_P;
1203 FUNCTION FROM_ROUTINGCP_P RETURN VARCHAR2 IS
1204 BEGIN
1205 RETURN FROM_ROUTINGCP;
1206 END FROM_ROUTINGCP_P;
1207 FUNCTION TO_ROUTINGCP_P RETURN VARCHAR2 IS
1208 BEGIN
1209 RETURN TO_ROUTINGCP;
1210 END TO_ROUTINGCP_P;
1211 FUNCTION FROM_ROUTING_STEP_NOCP_P RETURN VARCHAR2 IS
1212 BEGIN
1213 RETURN FROM_ROUTING_STEP_NOCP;
1214 END FROM_ROUTING_STEP_NOCP_P;
1215 FUNCTION TO_ROUTING_STEP_NOCP_P RETURN VARCHAR2 IS
1216 BEGIN
1217 RETURN TO_ROUTING_STEP_NOCP;
1218 END TO_ROUTING_STEP_NOCP_P;
1219 FUNCTION FROM_ROUTING_VERSIONCP_P RETURN VARCHAR2 IS
1220 BEGIN
1221 RETURN FROM_ROUTING_VERSIONCP;
1222 END FROM_ROUTING_VERSIONCP_P;
1223 FUNCTION TO_ROUTING_VERSIONCP_P RETURN VARCHAR2 IS
1224 BEGIN
1225 RETURN TO_ROUTING_VERSIONCP;
1226 END TO_ROUTING_VERSIONCP_P;
1227 FUNCTION FROM_FORMULA_VERSIONCP_P RETURN VARCHAR2 IS
1228 BEGIN
1229 RETURN FROM_FORMULA_VERSIONCP;
1230 END FROM_FORMULA_VERSIONCP_P;
1231 FUNCTION TO_FORMULA_VERSIONCP_P RETURN VARCHAR2 IS
1232 BEGIN
1233 RETURN TO_FORMULA_VERSIONCP;
1234 END TO_FORMULA_VERSIONCP_P;
1235 FUNCTION LOTNOCP_P RETURN VARCHAR2 IS
1236 BEGIN
1237 RETURN LOTNOCP;
1238 END LOTNOCP_P;
1239 FUNCTION FROM_LOTNOCP_P RETURN VARCHAR2 IS
1240 BEGIN
1241 RETURN FROM_LOTNOCP;
1242 END FROM_LOTNOCP_P;
1243 FUNCTION TO_LOTNOCP_P RETURN VARCHAR2 IS
1244 BEGIN
1245 RETURN TO_LOTNOCP;
1246 END TO_LOTNOCP_P;
1247 FUNCTION ORGCP_P RETURN VARCHAR2 IS
1248 BEGIN
1249 RETURN ORGCP;
1250 END ORGCP_P;
1251 END GMD_QCR03USR_XMLP_PKG;
1252
1253
[Home] [Help]