1 PACKAGE BODY GMI_GMILTGEN_XMLP_PKG AS
2 /* $Header: GMILTGENB.pls 120.0 2007/12/24 13:14:29 nchinnam noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 COMP_NO NUMBER := 1;
5 L_TRACE_ID NUMBER;
6 CURSOR GET_ITEM_DATA IS
7 SELECT
8 IM.ITEM_ID,
9 IL.LOT_ID
10 FROM
11 IC_ITEM_MST IM,
12 IC_LOTS_MST IL
13 WHERE IM.ITEM_NO = P_ITEM_NO
14 AND IM.ITEM_ID = IL.ITEM_ID
15 AND IL.LOT_NO = P_LOT_NO
16 AND ( P_SUBLOT_NO IS NULL
17 OR ( P_SUBLOT_NO IS NOT NULL
18 AND P_SUBLOT_NO = SUBLOT_NO ) )
19 ORDER BY
20 IL.LOT_ID;
21 BEGIN
22 --GGM_TRACE.SET_TRACE_ON('GMI_GMILTGEN_XMLP_PKG');
23 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
24 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
25 IF FND_PROFILE.VALUE('GMI_LOTGENE_ENABLE_FMSEC') <> 'Y' THEN
26 GMD_P_FS_CONTEXT.SET_ADDITIONAL_ATTR;
27 END IF;
28 FND_PROFILE.GET('LOT_GEN_DELIMITER'
29 ,P_DELIMIT);
30 IF P_DELIMIT IS NULL THEN
31 P_DELIMIT := '~';
32 END IF;
33 OPEN GET_ITEM_DATA;
34 FETCH GET_ITEM_DATA
35 INTO P_ITEM_ID,P_LOT_ID;
36 CLOSE GET_ITEM_DATA;
37 IF P_VIEW_BY = 1 THEN
38 P_REP_TITLE := FND_MESSAGE.GET_STRING('GMI'
39 ,'GMI_LOT_SOURCE');
40 P_LOT_COMP_TITLE := FND_MESSAGE.GET_STRING('GMI'
41 ,'GMI_LOT_INGD') ;
42 ELSE
43 P_REP_TITLE := FND_MESSAGE.GET_STRING('GMI'
44 ,'GMI_LOT_WHERE_USED');
45 P_LOT_COMP_TITLE := FND_MESSAGE.GET_STRING('GMI'
46 ,'GMI_LOT_BYPRDS') ;
47 END IF;
48 SELECT
49 GMI_LOT_TRACE_ID_S.NEXTVAL
50 INTO P_TRACE_ID
51 FROM
52 DUAL;
53 GMI_LOT_TRACE_PKG.EXP_LOT(P_ITEM_ID
54 ,P_LOT_ID
55 ,1
56 ,COMP_NO
57 ,P_VIEW_BY
58 ,P_TRACE_ID);
59 COMMIT;
60 RETURN (TRUE);
61 END AFTERPFORM;
62
63 FUNCTION AFTERREPORT RETURN BOOLEAN IS
64 BEGIN
65 DELETE FROM GMI_LOT_TRACE
66 WHERE LOT_TRACE_ID = P_TRACE_ID;
67 COMMIT;
68 --GGM_TRACE.SET_TRACE_OFF;
69 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
70 RETURN (TRUE);
71 END AFTERREPORT;
72
73 FUNCTION ITEM_NOFORMULA(ITEM_ID IN NUMBER
74 ,LOT_ID IN NUMBER) RETURN CHAR IS
75 L_ITEM_NO IC_ITEM_MST.ITEM_NO%TYPE;
76 CURSOR CUR_DOCDTL IS
77 SELECT
78 TRANS_DATE,
79 DOC_TYPE,
80 ORGN_CODE,
81 DOC_ID,
82 LINE_ID,
83 SUM(TRANS_QTY) TR_QTY,
84 TRANS_UM
85 FROM
86 IC_TRAN_VW1
87 WHERE ITEM_ID = ITEM_NOFORMULA.ITEM_ID
88 AND LOT_ID = ITEM_NOFORMULA.LOT_ID
89 AND COMPLETED_IND = 1
90 AND DOC_TYPE not in ( 'GRDI' , 'GRDR' , 'TRNI' , 'TRNR' , 'STSI' , 'STSR' )
91 GROUP BY
92 TRANS_DATE,
93 DOC_TYPE,
94 ORGN_CODE,
95 DOC_ID,
96 LINE_ID,
97 TRANS_UM
98 ORDER BY
99 TRANS_DATE ASC;
100 REC_DOCDTL CUR_DOCDTL%ROWTYPE;
101 CURSOR INVDTL IS
102 SELECT
103 IJM.ORGN_CODE,
104 IJM.JOURNAL_NO
105 FROM
106 IC_JRNL_MST IJM,
107 IC_ADJS_JNL IAJ
108 WHERE IJM.JOURNAL_ID = IAJ.JOURNAL_ID
109 AND IAJ.DOC_ID = REC_DOCDTL.DOC_ID;
110 CURSOR PRODDTL IS
111 SELECT
112 PLANT_CODE,
113 BATCH_NO
114 FROM
115 PM_BTCH_HDR
116 WHERE BATCH_ID = REC_DOCDTL.DOC_ID;
117 CURSOR CUR_PURCHDTL IS
118 SELECT
119 PRH.ORGN_CODE RECV_ORGN,
120 PVM.VENDOR_NO VEND_NO,
121 PVM.VENDOR_NAME VEND_NAME,
122 PRH.RECV_NO RECV_NO,
123 POH.PO_NO PO_NO,
124 POH.ORGN_CODE PO_ORGN
125 FROM
126 PO_RECV_HDR PRH,
127 PO_RECV_DTL PRD,
128 PO_VEND_MST PVM,
129 PO_ORDR_HDR POH
130 WHERE PRH.RECV_ID = REC_DOCDTL.DOC_ID
131 AND PRH.RECV_ID = PRD.RECV_ID
132 AND PRD.LINE_ID = REC_DOCDTL.LINE_ID
133 AND PRD.PO_ID = poh.po_id (+)
134 AND PVM.VENDOR_ID = PRD.SHIPVEND_ID;
135 CURSOR RCV_TRANSACTIONS_INFO IS
136 SELECT
137 SHIPMENT_HEADER_ID,
138 PO_HEADER_ID,
139 VENDOR_ID,
140 VENDOR_SITE_ID
141 FROM
142 RCV_TRANSACTIONS
143 WHERE SHIPMENT_HEADER_ID = REC_DOCDTL.DOC_ID
144 AND TRANSACTION_ID = REC_DOCDTL.LINE_ID;
145 REC_RCV_TRANSACTIONS RCV_TRANSACTIONS_INFO%ROWTYPE;
146 CURSOR VENDOR_INFO IS
147 SELECT
148 VEND.SEGMENT1,
149 VEND.VENDOR_NAME,
150 SITE.VENDOR_SITE_CODE
151 FROM
152 PO_VENDORS VEND,
153 PO_VENDOR_SITES_ALL SITE
154 WHERE VEND.VENDOR_ID = REC_RCV_TRANSACTIONS.VENDOR_ID
155 AND VEND.VENDOR_ID = SITE.VENDOR_ID
156 AND SITE.VENDOR_SITE_ID = REC_RCV_TRANSACTIONS.VENDOR_SITE_ID;
157 REC_VENDOR_INFO VENDOR_INFO%ROWTYPE;
158 CURSOR RECV_INFO IS
159 SELECT
160 RECEIPT_NUM
161 FROM
162 RCV_SHIPMENT_HEADERS
163 WHERE SHIPMENT_HEADER_ID = REC_RCV_TRANSACTIONS.SHIPMENT_HEADER_ID;
164 CURSOR PO_INFO IS
165 SELECT
166 SEGMENT1,
167 ATTRIBUTE15
168 FROM
169 PO_HEADERS_ALL
170 WHERE PO_HEADER_ID = REC_RCV_TRANSACTIONS.PO_HEADER_ID;
171 CURSOR CUR_VEND_NAME IS
172 SELECT
173 VENDOR_NO,
174 VENDOR_NAME
175 FROM
176 PO_VEND_MST
177 WHERE VENDOR_ID = (
178 SELECT
179 SHIPVEND_ID
180 FROM
181 IC_LOTS_MST
182 WHERE ITEM_ID = ITEM_NOFORMULA.ITEM_ID
183 AND LOT_ID = ITEM_NOFORMULA.LOT_ID );
184 BEGIN
185 BEGIN
186 SELECT
187 IM.ITEM_NO,
188 IM.ITEM_DESC1,
189 IM.SHELF_LIFE,
190 IM.RETEST_INTERVAL,
191 IL.LOT_NO,
192 IL.SUBLOT_NO,
193 IM.INV_CLASS,
194 IM.INV_TYPE,
195 IL.EXPIRE_DATE,
196 IL.RETEST_DATE,
197 IL.QC_GRADE,
198 IL.VENDOR_LOT_NO
199 INTO L_ITEM_NO,ITEM_DESC,SHELF_LIFE,RETEST_INTERVAL,LOT_NO,SUBLOT_NO,INV_CLASS,INV_TYPE,EXPIRE_DATE,RETEST_DATE,GRADE,VEND_LOT_NO
200 FROM
201 IC_ITEM_MST IM,
202 IC_LOTS_MST IL
203 WHERE IM.ITEM_ID = ITEM_NOFORMULA.ITEM_ID
204 AND IL.LOT_ID = ITEM_NOFORMULA.LOT_ID
205 AND IM.ITEM_ID = IL.ITEM_ID;
206 OPEN CUR_DOCDTL;
207 FETCH CUR_DOCDTL
208 INTO REC_DOCDTL;
209 IF CUR_DOCDTL%FOUND THEN
210 CLOSE CUR_DOCDTL;
211 CREATION_DATE := REC_DOCDTL.TRANS_DATE;
212 LOT_CREATION_TYPE := REC_DOCDTL.DOC_TYPE;
213 BEGINING_QTY := REC_DOCDTL.TR_QTY;
214 TRANS_UM := REC_DOCDTL.TRANS_UM;
215 IF REC_DOCDTL.DOC_TYPE in ('ADJI','ADJR','CREI','CRER') THEN
216 OPEN INVDTL;
217 FETCH INVDTL
218 INTO JRNL_ORGN_CODE,JOURNAL_NO;
219 CLOSE INVDTL;
220 OPEN CUR_VEND_NAME;
221 FETCH CUR_VEND_NAME
222 INTO VEND_NO,VEND_NAME;
223 CLOSE CUR_VEND_NAME;
224 ELSIF REC_DOCDTL.DOC_TYPE in ('PROD') THEN
225 OPEN PRODDTL;
226 FETCH PRODDTL
227 INTO PLANT_CODE,BATCH_NO;
228 CLOSE PRODDTL;
229 ELSIF REC_DOCDTL.DOC_TYPE = 'RECV' THEN
230 OPEN CUR_PURCHDTL;
231 FETCH CUR_PURCHDTL
232 INTO RECV_ORGN_CODE,VEND_NO,VEND_NAME,RECV_NO,PO_NO,PO_ORGN_CODE;
233 CLOSE CUR_PURCHDTL;
234 ELSIF REC_DOCDTL.DOC_TYPE = 'PORC' THEN
235 RECV_ORGN_CODE := REC_DOCDTL.ORGN_CODE;
236 OPEN RCV_TRANSACTIONS_INFO;
237 FETCH RCV_TRANSACTIONS_INFO
238 INTO REC_RCV_TRANSACTIONS;
239 CLOSE RCV_TRANSACTIONS_INFO;
240 OPEN VENDOR_INFO;
241 FETCH VENDOR_INFO
242 INTO REC_VENDOR_INFO;
243 CLOSE VENDOR_INFO;
244 VEND_NAME := REC_VENDOR_INFO.VENDOR_NAME;
245 VEND_NO := REC_VENDOR_INFO.SEGMENT1 || FND_PROFILE.VALUE('GL$VEND_DELIMITER') || REC_VENDOR_INFO.VENDOR_SITE_CODE;
246 OPEN RECV_INFO;
247 FETCH RECV_INFO
248 INTO RECV_NO;
249 CLOSE RECV_INFO;
250 OPEN PO_INFO;
251 FETCH PO_INFO
252 INTO PO_NO,PO_ORGN_CODE;
253 CLOSE PO_INFO;
254 END IF;
255 END IF;
256 RETURN (L_ITEM_NO);
257 EXCEPTION
258 WHEN NO_DATA_FOUND THEN
259 NULL;
260 WHEN OTHERS THEN
261 NULL;
262 END;
263 END ITEM_NOFORMULA;
264
265 FUNCTION CF_1FORMULA(TRANS_DATE IN DATE) RETURN DATE IS
266 BEGIN
267 RETURN (TRANS_DATE);
268 END CF_1FORMULA;
269
270 FUNCTION I_ITEM_NOFORMULA(ITEM_ID2 IN NUMBER
271 ,LOT_ID3 IN NUMBER
272 ,CIR_FLG1 IN VARCHAR2
273 ,LEVEL_NO1 IN NUMBER) RETURN CHAR IS
274 L_ITEM_NO IC_ITEM_MST.ITEM_NO%TYPE;
275 I NUMBER := 1;
276 L_LOT_NO IC_LOTS_MST.LOT_NO%TYPE;
277 L_SUBLOT_NO IC_LOTS_MST.SUBLOT_NO%TYPE;
278 BEGIN
279 SELECT
280 IM.ITEM_NO,
281 IM.ITEM_DESC1,
282 IL.LOT_NO,
283 IL.SUBLOT_NO
284 INTO L_ITEM_NO,I_ITEM_DESC,I_LOT_NO,I_SUBLOT_NO
285 FROM
286 IC_ITEM_MST IM,
287 IC_LOTS_MST IL
288 WHERE IM.ITEM_ID = ITEM_ID2
289 AND IL.LOT_ID = LOT_ID3
290 AND IM.ITEM_ID = IL.ITEM_ID;
291 IF I_SUBLOT_NO IS NOT NULL THEN
292 IND_KEY := CIR_FLG1 || L_ITEM_NO || P_DELIMIT || I_LOT_NO || P_DELIMIT || I_SUBLOT_NO;
293 ELSIF I_LOT_NO IS NOT NULL THEN
294 IND_KEY := CIR_FLG1 || L_ITEM_NO || P_DELIMIT || I_LOT_NO;
295 ELSE
296 IND_KEY := CIR_FLG1 || L_ITEM_NO;
297 END IF;
298 WHILE I < LEVEL_NO1 LOOP
299
300 IND_KEY := ' ' || IND_KEY;
301 I := I + 1;
302 END LOOP;
303 RETURN (L_ITEM_NO);
304 END I_ITEM_NOFORMULA;
305
306 FUNCTION DOC_NOFORMULA(DOC_ID IN NUMBER
307 ,LINE_ID IN NUMBER
308 ,ORGN_CODE IN VARCHAR2
309 ,DOC_TYPE IN VARCHAR2) RETURN CHAR IS
310 CURSOR INVDTL IS
311 SELECT
312 IJM.JOURNAL_NO
313 FROM
314 IC_ADJS_JNL IAJ,
315 IC_JRNL_MST IJM
316 WHERE IAJ.JOURNAL_ID = IJM.JOURNAL_ID
317 AND IAJ.DOC_ID = DOC_NOFORMULA.DOC_ID;
318 CURSOR PRODDTL IS
319 SELECT
320 BATCH_NO
321 FROM
322 PM_BTCH_HDR
323 WHERE BATCH_ID = DOC_ID;
324 CURSOR CUR_PURCHDTL IS
325 SELECT
326 PRH.RECV_NO RECV_NO
327 FROM
328 PO_RECV_HDR PRH
329 WHERE PRH.RECV_ID = DOC_ID;
330 CURSOR PHYINVDTL IS
331 SELECT
332 CYCLE_NO
333 FROM
334 IC_CYCL_HDR
335 WHERE CYCLE_ID = DOC_ID;
336 CURSOR OPBOLDTL IS
337 SELECT
338 BOL_NO
339 FROM
340 OP_BILL_LAD
341 WHERE BOL_ID = DOC_ID;
342 CURSOR OPSHIPDTL IS
343 SELECT
344 ORDER_NO
345 FROM
346 OP_ORDR_HDR
347 WHERE ORDER_ID = DOC_ID;
348 CURSOR PODTL IS
349 SELECT
350 PO_NO
351 FROM
352 PO_ORDR_HDR
353 WHERE PO_ID = DOC_ID;
354 CURSOR PORTRNDTL IS
355 SELECT
356 RETURN_NO
357 FROM
358 PO_RTRN_HDR
359 WHERE RETURN_ID = DOC_ID;
360 CURSOR OMORDRDTL IS
361 SELECT
362 HDR.ORDER_NUMBER,
363 ORG.NAME
364 FROM
365 OE_ORDER_HEADERS_ALL HDR,
366 OE_ORDER_LINES_ALL DTL,
367 HR_OPERATING_UNITS ORG
368 WHERE ORG.ORGANIZATION_ID = HDR.ORG_ID
369 AND DTL.LINE_ID = DOC_NOFORMULA.LINE_ID
370 AND DTL.HEADER_ID = HDR.HEADER_ID;
371 CURSOR RECV_INFO IS
372 SELECT
373 RECEIPT_NUM
374 FROM
375 RCV_SHIPMENT_HEADERS
376 WHERE SHIPMENT_HEADER_ID = DOC_ID;
377 L_DOC_NO VARCHAR2(32);
378 BEGIN
379 DISP_ORGN_CODE := ORGN_CODE;
380 IF DOC_TYPE in ('ADJI','ADJR','CREI','CRER','GRDI','GRDR','STSI','STSR','TRNI','TRNR') THEN
381 OPEN INVDTL;
382 FETCH INVDTL
383 INTO L_DOC_NO;
384 CLOSE INVDTL;
385 ELSIF DOC_TYPE in ('PROD') THEN
386 OPEN PRODDTL;
387 FETCH PRODDTL
388 INTO L_DOC_NO;
389 CLOSE PRODDTL;
390 ELSIF DOC_TYPE in ('RECV','POSR') THEN
391 OPEN CUR_PURCHDTL;
392 FETCH CUR_PURCHDTL
393 INTO L_DOC_NO;
394 CLOSE CUR_PURCHDTL;
395 ELSIF DOC_TYPE in ('PICY','PIPH') THEN
396 OPEN PHYINVDTL;
397 FETCH PHYINVDTL
398 INTO L_DOC_NO;
399 CLOSE PHYINVDTL;
400 ELSIF DOC_TYPE in ('OPSP') THEN
401 OPEN OPBOLDTL;
402 FETCH OPBOLDTL
403 INTO L_DOC_NO;
404 CLOSE OPBOLDTL;
405 ELSIF DOC_TYPE in ('OPSO') THEN
406 OPEN OPSHIPDTL;
407 FETCH OPSHIPDTL
408 INTO L_DOC_NO;
409 CLOSE OPSHIPDTL;
410 ELSIF DOC_TYPE in ('OMSO') THEN
411 OPEN OMORDRDTL;
412 FETCH OMORDRDTL
413 INTO L_DOC_NO,DISP_ORGN_CODE;
414 CLOSE OMORDRDTL;
415 ELSIF DOC_TYPE in ('PORD') THEN
416 OPEN PODTL;
417 FETCH PODTL
418 INTO L_DOC_NO;
419 CLOSE PODTL;
420 ELSIF DOC_TYPE in ('RTRN') THEN
421 OPEN PORTRNDTL;
422 FETCH PORTRNDTL
423 INTO L_DOC_NO;
424 CLOSE PORTRNDTL;
425 ELSIF DOC_TYPE = 'PORC' THEN
426 OPEN RECV_INFO;
427 FETCH RECV_INFO
428 INTO L_DOC_NO;
429 CLOSE RECV_INFO;
430 END IF;
431 RETURN (L_DOC_NO);
432 END DOC_NOFORMULA;
433
434 FUNCTION QC_SPECFORMULA(QCASSY_TYP_ID IN NUMBER
435 ,QC_SPEC_ID IN NUMBER
436 ,TEXT_RESULT IN VARCHAR2
437 ,NUM_RESULT IN NUMBER
438 ,ACCEPT_ANYWAY IN VARCHAR2) RETURN CHAR IS
439 L_SPEC VARCHAR2(20);
440 L_ASSAY_TYPE VARCHAR2(40);
441 CURSOR CUR_ASSAY_DESC IS
442 SELECT
443 TEST_TYPE
444 FROM
445 GMD_QC_TESTS_B
446 WHERE TEST_ID = QCASSY_TYP_ID;
447 CURSOR CUR_SPEC_DATA IS
448 SELECT
449 DECODE(L_ASSAY_TYPE
450 ,'U'
451 ,A.TARGET_VALUE_CHAR
452 ,'V'
453 ,A.TARGET_VALUE_CHAR
454 ,'N'
455 ,TO_CHAR(A.TARGET_VALUE_NUM)),
456 A.MIN_VALUE_NUM,
457 A.MAX_VALUE_NUM
458 FROM
459 GMD_SPEC_TESTS_B A,
460 GMD_QC_TESTS_B B
461 WHERE A.SPEC_ID = QC_SPEC_ID
462 AND A.TEST_ID = B.TEST_ID;
463 BEGIN
464 IF (QC_SPEC_ID IS NOT NULL) THEN
465 OPEN CUR_SPEC_DATA;
466 FETCH CUR_SPEC_DATA
467 INTO L_SPEC,MIN_SPEC,MAX_SPEC;
468 CLOSE CUR_SPEC_DATA;
469 END IF;
470 IF (TEXT_RESULT IS NOT NULL) THEN
471 RESULT := TEXT_RESULT;
472 ELSIF (NUM_RESULT IS NOT NULL) THEN
473 RESULT := TO_CHAR(NUM_RESULT);
474 END IF;
475 IF ACCEPT_ANYWAY = '0' THEN
476 ACCEPT_FLG := 'No';
477 ELSIF ACCEPT_ANYWAY = '1' THEN
478 ACCEPT_FLG := 'Yes';
479 END IF;
480 RETURN (L_SPEC);
481 END QC_SPECFORMULA;
482
483 FUNCTION UOMFORMULA(ITEM_ID1 IN NUMBER) RETURN CHAR IS
484 L_UOM IC_ITEM_MST.ITEM_UM%TYPE;
485 BEGIN
486 SELECT
487 ITEM_UM,
488 ITEM_UM2
489 INTO L_UOM,UOM2
490 FROM
491 IC_ITEM_MST
492 WHERE ITEM_ID = ITEM_ID1;
493 RETURN (L_UOM);
494 END UOMFORMULA;
495
496 FUNCTION IND_KEY2FORMULA(SUBLOT_NO2 IN VARCHAR2
497 ,ITEM_NO2 IN VARCHAR2
498 ,LOT_NO2 IN VARCHAR2) RETURN CHAR IS
499 L_IND_KEY VARCHAR2(80);
500 I NUMBER := 1;
501 BEGIN
502 IF SUBLOT_NO2 IS NOT NULL THEN
503 L_IND_KEY := ITEM_NO2 || P_DELIMIT || LOT_NO2 || P_DELIMIT || SUBLOT_NO2;
504 ELSIF LOT_NO2 IS NOT NULL THEN
505 L_IND_KEY := ITEM_NO2 || P_DELIMIT || LOT_NO2;
506 ELSE
507 L_IND_KEY := ITEM_NO2;
508 END IF;
509 RETURN (L_IND_KEY);
510 END IND_KEY2FORMULA;
511
512 PROCEDURE HEADER IS
513 BEGIN
514 NULL;
515 END HEADER;
516
517 FUNCTION ITEM_DESC_P RETURN VARCHAR2 IS
518 BEGIN
519 RETURN ITEM_DESC;
520 END ITEM_DESC_P;
521
522 FUNCTION LOT_NO_P RETURN VARCHAR2 IS
523 BEGIN
524 RETURN LOT_NO;
525 END LOT_NO_P;
526
527 FUNCTION SUBLOT_NO_P RETURN VARCHAR2 IS
528 BEGIN
529 RETURN SUBLOT_NO;
530 END SUBLOT_NO_P;
531
532 FUNCTION INV_TYPE_P RETURN VARCHAR2 IS
533 BEGIN
534 RETURN INV_TYPE;
535 END INV_TYPE_P;
536
537 FUNCTION SHELF_LIFE_P RETURN NUMBER IS
538 BEGIN
539 RETURN SHELF_LIFE;
540 END SHELF_LIFE_P;
541
542 FUNCTION INV_CLASS_P RETURN VARCHAR2 IS
543 BEGIN
544 RETURN INV_CLASS;
545 END INV_CLASS_P;
546
547 FUNCTION GRADE_P RETURN VARCHAR2 IS
548 BEGIN
549 RETURN GRADE;
550 END GRADE_P;
551
552 FUNCTION RETEST_INTERVAL_P RETURN NUMBER IS
553 BEGIN
554 RETURN RETEST_INTERVAL;
555 END RETEST_INTERVAL_P;
556
557 FUNCTION LOT_CREATION_TYPE_P RETURN VARCHAR2 IS
558 BEGIN
559 RETURN LOT_CREATION_TYPE;
560 END LOT_CREATION_TYPE_P;
561
562 FUNCTION LOT_CREATION_TYPE_DESC_P RETURN VARCHAR2 IS
563 BEGIN
564 RETURN LOT_CREATION_TYPE_DESC;
565 END LOT_CREATION_TYPE_DESC_P;
566
567 FUNCTION BEGINING_QTY_P RETURN NUMBER IS
568 BEGIN
569 RETURN BEGINING_QTY;
570 END BEGINING_QTY_P;
571
572 FUNCTION CREATION_DATE_P RETURN DATE IS
573 BEGIN
574 RETURN CREATION_DATE;
575 END CREATION_DATE_P;
576
577 FUNCTION EXPIRE_DATE_P RETURN DATE IS
578 BEGIN
579 RETURN EXPIRE_DATE;
580 END EXPIRE_DATE_P;
581
582 FUNCTION RETEST_DATE_P RETURN DATE IS
583 BEGIN
584 RETURN RETEST_DATE;
585 END RETEST_DATE_P;
586
587 FUNCTION PLANT_CODE_P RETURN VARCHAR2 IS
588 BEGIN
589 RETURN PLANT_CODE;
590 END PLANT_CODE_P;
591
592 FUNCTION BATCH_NO_P RETURN VARCHAR2 IS
593 BEGIN
594 RETURN BATCH_NO;
595 END BATCH_NO_P;
596
597 FUNCTION VEND_NO_P RETURN VARCHAR2 IS
598 BEGIN
599 RETURN VEND_NO;
600 END VEND_NO_P;
601
602 FUNCTION VEND_NAME_P RETURN VARCHAR2 IS
603 BEGIN
604 RETURN VEND_NAME;
605 END VEND_NAME_P;
606
607 FUNCTION TRANS_UM_P RETURN VARCHAR2 IS
608 BEGIN
609 RETURN TRANS_UM;
610 END TRANS_UM_P;
611
612 FUNCTION VEND_LOT_NO_P RETURN VARCHAR2 IS
613 BEGIN
614 RETURN VEND_LOT_NO;
615 END VEND_LOT_NO_P;
616
617 FUNCTION PO_ORGN_CODE_P RETURN VARCHAR2 IS
618 BEGIN
619 RETURN PO_ORGN_CODE;
620 END PO_ORGN_CODE_P;
621
622 FUNCTION PO_NO_P RETURN VARCHAR2 IS
623 BEGIN
624 RETURN PO_NO;
625 END PO_NO_P;
626
627 FUNCTION RECV_ORGN_CODE_P RETURN VARCHAR2 IS
628 BEGIN
629 RETURN RECV_ORGN_CODE;
630 END RECV_ORGN_CODE_P;
631
632 FUNCTION RECV_NO_P RETURN VARCHAR2 IS
633 BEGIN
634 RETURN RECV_NO;
635 END RECV_NO_P;
636
637 FUNCTION JRNL_ORGN_CODE_P RETURN VARCHAR2 IS
638 BEGIN
639 RETURN JRNL_ORGN_CODE;
640 END JRNL_ORGN_CODE_P;
641
642 FUNCTION JOURNAL_NO_P RETURN VARCHAR2 IS
643 BEGIN
644 RETURN JOURNAL_NO;
645 END JOURNAL_NO_P;
646
647 FUNCTION MIN_SPEC_P RETURN NUMBER IS
648 BEGIN
649 RETURN MIN_SPEC;
650 END MIN_SPEC_P;
651
652 FUNCTION MAX_SPEC_P RETURN NUMBER IS
653 BEGIN
654 RETURN MAX_SPEC;
655 END MAX_SPEC_P;
656
657 FUNCTION RESULT_P RETURN VARCHAR2 IS
658 BEGIN
659 RETURN RESULT;
660 END RESULT_P;
661
662 FUNCTION ACCEPT_FLG_P RETURN VARCHAR2 IS
663 BEGIN
664 RETURN ACCEPT_FLG;
665 END ACCEPT_FLG_P;
666
667 FUNCTION DISP_ORGN_CODE_P RETURN VARCHAR2 IS
668 BEGIN
669 RETURN DISP_ORGN_CODE;
670 END DISP_ORGN_CODE_P;
671
672 FUNCTION I_ITEM_DESC_P RETURN VARCHAR2 IS
673 BEGIN
674 RETURN I_ITEM_DESC;
675 END I_ITEM_DESC_P;
676
677 FUNCTION I_LOT_NO_P RETURN VARCHAR2 IS
678 BEGIN
679 RETURN I_LOT_NO;
680 END I_LOT_NO_P;
681
682 FUNCTION I_SUBLOT_NO_P RETURN VARCHAR2 IS
683 BEGIN
684 RETURN I_SUBLOT_NO;
685 END I_SUBLOT_NO_P;
686
687 FUNCTION IND_KEY_P RETURN VARCHAR2 IS
688 BEGIN
689 RETURN IND_KEY;
690 END IND_KEY_P;
691
692 FUNCTION UOM2_P RETURN VARCHAR2 IS
693 BEGIN
694 RETURN UOM2;
695 END UOM2_P;
696
697 function M_6FormatTrigger(item_id number, lot_id number) return varchar2 is
698 n number;
699 begin
700 select count(*) into n
701 from gmd_results a, gmd_spec_results b, gmd_samples c
702 where nvl(b.evaluation_ind,'N') in ('0A','1V')
703 and a.result_id = b.result_id
704 and a.sample_id = c.sample_id
705 and a.sample_id not in (select sample_id from gmd_samples where
706 (supplier_id is not null or cust_id is not null) or
707 (batch_id is not null or formula_id is not null or
708 routing_id is not null or oprn_id is not null))
709 and a.delete_mark = 0
710 and c.item_id = M_6FormatTrigger.item_id
711 and c.lot_id = M_6FormatTrigger.lot_id;
712 IF n <>0 THEN
713 return 'FALSE';
714 ELSE
715 return 'TRUE';
716 END IF;
717 end;
718
719 function M_5FormatTrigger(item_id number, lot_id number) return varchar2 is
720 n number;
721 begin
722 IF p_view_by = 1 then
723 SELECT count(*) into n
724 from GMI_LOTS_COMP_v
725 where product_item_id = M_5FormatTrigger.item_id
726 and product_lot_id = M_5FormatTrigger.lot_id;
727 ELSE
728 SELECT count(*) into n
729 from GMI_LOTS_DEST_v
730 where ingred_item_id = M_5FormatTrigger.item_id
731 and ingred_lot_id = M_5FormatTrigger.lot_id;
732 END IF;
733 IF n <>0 then
734 return 'FALSE';
735 ELSE
736 return 'TRUE';
737 END IF;
738 end;
739 END GMI_GMILTGEN_XMLP_PKG;
740
[Home] [Help]