PACKAGE BODY: APPS.GMI_WF_LOT_CM

Source

1 PACKAGE BODY gmi_wf_lot_cm AS
2 /* $Header: gmiltcmb.pls 120.1 2005/10/03 12:09:47 jsrivast noship $ */
3 
4    PROCEDURE init_wf IS
5 
6       CURSOR ic_lots_mst_cursor IS
7          SELECT  lot_id, lot_no , sublot_no ,expire_date ,
8                  retest_date , item_id , created_by
9          FROM    ic_lots_mst
10          WHERE   delete_mark = 0 AND inactive_ind = 0
11          FOR UPDATE; -- table is NOT updated, see NOTES section above
12 
13       l_lot_ctl             ic_item_mst.lot_ctl%TYPE ;
14       l_grade_ctl           ic_item_mst.grade_ctl%TYPE ;
15 
16 
17       l_date_at_start       DATE ;
18       l_notify_date         DATE ;
19 
20 
21       l_retest_interval             NUMBER(3);
22       l_expiry_interval             NUMBER(3);
23       l_retest_interval_from_tab    NUMBER(3);
24       l_expiry_interval_from_tab    NUMBER(3);
25       l_default_retest_interval     NUMBER(3) := 7;
26       l_default_expiry_interval     NUMBER(3) := 7;
27 
28       l_date_now            DATE ;
29 
30    BEGIN
31 
32       SELECT SYSDATE  INTO l_date_at_start FROM  DUAL ;
33 
34 
35 	IF (FND_PROFILE.DEFINED ('WF$RETEST_INTERVAL')) THEN
36       	    l_retest_interval := FND_PROFILE.VALUE ('WF$RETEST_INTERVAL');
37         END IF;
38 
39 	IF l_retest_interval = NULL THEN
40            l_retest_interval := l_default_retest_interval;
41         END IF;
42 
43 	IF (FND_PROFILE.DEFINED ('WF$EXPIRY_INTERVAL')) THEN
44       	    l_expiry_interval := FND_PROFILE.VALUE ('WF$EXPIRY_INTERVAL');
45         END IF;
46 
47 	IF l_expiry_interval = NULL THEN
48            l_expiry_interval := l_default_expiry_interval;
49         END IF;
50 
51       FOR ic_lots_mst_record IN ic_lots_mst_cursor
52       LOOP
53 
54          SELECT  lot_ctl,grade_ctl
55          INTO    l_lot_ctl,l_grade_ctl
56          FROM    ic_item_mst
57          WHERE   item_id = ic_lots_mst_record.item_id
58          AND     delete_mark = 0 ;
59 
60 
61          IF (l_lot_ctl = 1) and (l_grade_ctl = 1) THEN
62 
63 
64                 SELECT nvl(lot_retest_note,0),nvl(lot_expiry_note,0)
65                 INTO   l_retest_interval_from_tab, l_expiry_interval_from_tab
66                 FROM   sy_wf_item_roles
67                 WHERE   item_id = ic_lots_mst_record.item_id;
68 
69                 IF l_retest_interval_from_tab <> 0   then
70                    l_retest_interval:= l_retest_interval_from_tab;
71                 END IF;
72                 IF l_expiry_interval_from_tab <> 0   then
73                    l_expiry_interval:= l_expiry_interval_from_tab;
74                 END IF;
75 
76 
77          	IF ( ic_lots_mst_record.retest_date IS NOT NULL ) THEN
78                      l_notify_date := ( ic_lots_mst_record.retest_date - l_retest_interval ) ;
79        		    IF ( l_date_at_start >= l_notify_date ) THEN
80 		        gmi_wf_lot_retest.init_wf (
81                		ic_lots_mst_record.lot_id ,
82 	                  ic_lots_mst_record.lot_no ,
83                   	ic_lots_mst_record.sublot_no ,
84                   	ic_lots_mst_record.retest_date ,
85                   	ic_lots_mst_record.item_id ,
86                   	ic_lots_mst_record.created_by);
87 
88             	    END IF ;
89 
90 		END IF;
91 
92          	IF ( ic_lots_mst_record.expire_date IS NOT NULL ) THEN
93             	     l_notify_date := ( ic_lots_mst_record.expire_date - l_expiry_interval ) ;
94 
95 	            IF ( l_date_at_start >= l_notify_date ) THEN
96 
97         	        gmi_wf_lot_expiry.init_wf (
98                 	  ic_lots_mst_record.lot_id ,
99                   	  ic_lots_mst_record.lot_no ,
100                   	  ic_lots_mst_record.sublot_no ,
101                   	  ic_lots_mst_record.expire_date ,
102                   	  ic_lots_mst_record.item_id ,
103                   	  ic_lots_mst_record.created_by);
104                      END IF ;
105 
106          	END IF ;
107 
108          END IF ;
109 
110 	 COMMIT ;
111 
112       END LOOP ;
113 
114    END init_wf;
115 
116 END gmi_wf_lot_cm;