[Home] [Help]
PACKAGE BODY: APPS.GMI_WF_LOT_EXPIRY
Source
1 PACKAGE BODY gmi_wf_lot_expiry AS
2 /* $Header: gmiltexb.pls 115.4 2003/10/16 15:20:41 hsaleeb ship $ */
3
4 PROCEDURE init_wf (
5 /* called via trigger on ic_lots_mst */
6 p_lot_id IN ic_lots_mst.lot_id%TYPE ,
7 p_lot_no IN ic_lots_mst.lot_no%TYPE ,
8 p_sublot_no IN ic_lots_mst.sublot_no%TYPE ,
9 p_expire_date IN ic_lots_mst.expire_date%TYPE ,
10 p_item_id IN ic_lots_mst.item_id%TYPE ,
11 p_created_by IN ic_lots_mst.created_by%TYPE
12 )
13
14 IS
15
16 l_itemtype WF_ITEMS.ITEM_TYPE%TYPE := 'GMWLOTEX';
17
18 /* since two WF processes exist with the GMWLOTEX WF Item,
19 prefix ITEMKEY with 'EX' to differentiate the Expiry Process */
20 l_itemkey WF_ITEMS.ITEM_KEY%TYPE := 'EX' || TO_CHAR(p_lot_id) ;
21
22 l_expiry_interval NUMBER(3) ;
23 l_expiry_interval_from_tab NUMBER(3);
24 l_default_expiry_interval NUMBER(3) :=7;
25 /* make sure that process runs with background engine
26 to prevent SAVEPOINT/ROLLBACK error (see Workflow FAQ)
27 the 'magic value' to use for this is -1 */
28 l_run_wf_in_background CONSTANT WF_ENGINE.THRESHOLD%TYPE := -1;
29
30 l_expire_notify_date DATE ;
31 l_wf_start_date DATE ;
32
33 l_item_no ic_item_mst.item_no%TYPE ;
34 l_item_desc1 ic_item_mst.item_desc1%TYPE ;
35 l_dualum_ind ic_item_mst.dualum_ind%TYPE ;
36 l_item_um ic_item_mst.item_um%TYPE ;
37 l_item_um2 ic_item_mst.item_um2%TYPE ;
38 c_whse_item_id ic_item_mst.whse_item_id%TYPE;
39 c_item_id ic_item_mst.item_id%TYPE;
40
41 l_errname VARCHAR2(30);
42 l_errmsg VARCHAR2(2000);
43
44 l_errstack VARCHAR2(32000);
45 l_sqlcode NUMBER ;
46 l_performer_name WF_ROLES.NAME%TYPE ;
47 l_performer_display_name WF_ROLES.DISPLAY_NAME%TYPE ;
48
49 l_role_name sy_wf_item_roles.expiry_role_name%TYPE ;
50 l_role_display_name sy_wf_item_roles.expiry_role_display_name%TYPE ;
51
52 l_WorkflowProcess VARCHAR2(30) := 'LOT_EXPIRY_PROC';
53
54 wf_item_already_exists EXCEPTION ;
55 --BEGIN BUG#2134597 PR
56 l_status VARCHAR2 (8);
57 l_result VARCHAR2 (30);
58 --END BUG#2134597
59 CURSOR c_whse_item_role(c_whse_item_id NUMBER) is
60 ( SELECT expiry_role_name,expiry_role_display_name,NVL(lot_expiry_note,0)
61 FROM sy_wf_item_roles
62 WHERE whse_item_id = c_whse_item_id);
63 CURSOR c_item_role(c_item_id NUMBER) is
64 ( SELECT expiry_role_name,expiry_role_display_name,NVL(lot_expiry_note,0)
65 FROM sy_wf_item_roles
66 WHERE item_id = c_item_id);
67
68 CURSOR c_whse_item(c_item_id NUMBER) is
69 ( SELECT whse_item_id
70 FROM ic_item_mst
71 WHERE item_id = c_item_id);
72
73 BEGIN
74 /* set the workflow start date to date now */
75 SELECT sysdate INTO l_wf_start_date FROM dual;
76
77 /* get values to be stored into the workflow item */
78 SELECT USER_NAME, DESCRIPTION
79 INTO l_performer_name,l_performer_display_name
80 FROM FND_USER
81 WHERE USER_ID = p_created_by ;
82
83
84 SELECT item_no , item_desc1 , item_um ,item_um2 , dualum_ind
85 INTO l_item_no ,l_item_desc1 , l_item_um , l_item_um2 , l_dualum_ind
86 FROM ic_item_mst
87 WHERE item_id = p_item_id ;
88
89 /*BEGIN BUG#2134597 Praveen Reddy*/
90 /*Check if the workflow data exists and remove the same for the itemtype and itemkey
91 combination */
92 BEGIN
93 IF (wf_item.item_exist (l_itemtype, l_itemkey)) THEN
94 /* Check the status of the root activity */
95 wf_item_activity_status.root_status (l_itemtype, l_itemkey, l_status, l_result);
96 /* If it is not completed then abort the process */
97 IF (l_status <> 'COMPLETE')THEN
98 wf_engine.abortprocess (itemtype=> l_itemtype, itemkey=> l_itemkey, process=> l_workflowprocess);
99 END IF;
100 /* Purge the workflow data for workflow key */
101 wf_purge.total (itemtype=> l_itemtype, itemkey=> l_itemkey, docommit=> FALSE);
102 END IF;
103 EXCEPTION
104 WHEN OTHERS THEN
105 WF_CORE.CONTEXT ('gm_wf_lot_expiry', 'init_wf', l_itemtype, l_itemkey, p_lot_no, p_sublot_no) ;
106 WF_CORE.GET_ERROR (l_errname, l_errmsg, l_errstack);
107 END;
108 /*END BUG#2134597*/
109
110 BEGIN
111 /* create the process */
112 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype, itemkey => l_itemkey, process => l_WorkflowProcess) ;
113 EXCEPTION
114 WHEN DUP_VAL_ON_INDEX THEN
115 RAISE wf_item_already_exists ;
116 END ;
117
118 /* make sure that process runs with background engine */
119 WF_ENGINE.THRESHOLD := l_run_wf_in_background ;
120
121 /* set the item attributes */
122 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,
123 itemkey => l_itemkey,
124 aname => 'LOT_ID',
125 avalue => p_lot_id);
126
127 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
128 itemkey => l_itemkey,
129 aname => 'LOT_NO',
130 avalue => p_lot_no);
131
132 WF_ENGINE.SETITEMATTRTEXT (itemtype => l_itemtype,
133 itemkey => l_itemkey,
134 aname => 'SUBLOT_NO',
135 avalue => p_sublot_no);
136
137 WF_ENGINE.SETITEMATTRDATE (itemtype => l_itemtype,
138 itemkey => l_itemkey,
139 aname => 'EXPIRE_DATE',
140 avalue => p_expire_date);
141
142 /* find notification target of this WF for this item */
143 c_item_id:=p_item_id;
144 OPEN c_item_role(c_item_id);
145 FETCH c_item_role INTO l_role_name,l_role_display_name,l_expiry_interval_from_tab;
146 IF c_item_role%NOTFOUND THEN
147 LOOP
148 OPEN c_whse_item(c_item_id);
149 FETCH c_whse_item INTO c_whse_item_id;
150 IF c_whse_item%FOUND THEN
151 OPEN c_whse_item_role(c_whse_item_id);
152 FETCH c_whse_item_role INTO l_role_name,l_role_display_name,l_expiry_interval_from_tab;
153 IF c_whse_item_role%NOTFOUND THEN
154 IF c_whse_item_id <> c_item_id THEN
155 c_item_id:=c_whse_item_id;
156 close c_whse_item_role;
157 ELSE
158 l_role_name:=NULL;
159 l_expiry_interval_from_tab:=0;
160 close c_whse_item;
161 close c_whse_item_role;
162 EXIT;
163 END IF;
164 ELSE
165 close c_whse_item;
166 close c_whse_item_role;
167 EXIT;
168 END IF;
169 END IF;
170 close c_whse_item;
171 END LOOP;
172 END IF;
173 close c_item_role;
174 IF l_role_name is NULL THEN
175 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
176 itemkey => l_itemkey,
177 aname => 'PERNAME',
178 avalue => l_performer_name);
179 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
180 itemkey => l_itemkey,
181 aname => 'PERDISP',
182 avalue => l_performer_display_name);
183
184 /*Added FROM_ROLE attribute for BLAF standard */
185 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
186 itemkey => l_itemkey,
187 aname => '#FROM_ROLE',
188 avalue => l_performer_name) ;
189 ELSE
190 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
191 itemkey => l_itemkey,
192 aname => 'PERNAME',
193 avalue => l_role_name) ;
194
195 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
196 itemkey => l_itemkey,
197 aname => 'PERDISP',
198 avalue => l_role_display_name) ;
199
200 /*Added FROM_ROLE attribute for BLAF standard */
201 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
202 itemkey => l_itemkey,
203 aname => '#FROM_ROLE',
204 avalue => l_role_name) ;
205 END IF;
206
207 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
208 itemkey => l_itemkey,
209 aname => 'ITEM_NO',
210 avalue => l_item_no);
211
212 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
213 itemkey => l_itemkey,
214 aname => 'ITEM_DESC1',
215 avalue => l_item_desc1);
216
217 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
218 itemkey => l_itemkey,
219 aname => 'ITEM_UM',
220 avalue => l_item_um);
221
222 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
223 itemkey => l_itemkey,
224 aname => 'ITEM_UM2',
225 avalue => l_item_um2);
226
227 WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype,
228 itemkey => l_itemkey,
229 aname => 'DUALUM_IND',
230 avalue => l_dualum_ind);
231
232 WF_ENGINE.SETITEMATTRDATE(itemtype => l_itemtype,
233 itemkey => l_itemkey,
234 aname => 'WF_START_DATE',
235 avalue => l_wf_start_date) ;
236
237 /* Setting the number of days */
238
239 IF l_expiry_interval_from_tab <> 0 then
240 l_expiry_interval:= l_expiry_interval_from_tab;
241 ELSE
242 /* Getting the WF expiry Notification Interval */
243 IF (FND_PROFILE.DEFINED ('WF$EXPIRY_INTERVAL')) THEN
244 l_expiry_interval := FND_PROFILE.VALUE ('WF$EXPIRY_INTERVAL');
245 END IF;
246 IF l_expiry_interval IS NULL THEN
247 l_expiry_interval := l_default_expiry_interval;
248 END IF;
249 END IF;
250
251
252 /* set the notification date and time */
253 l_expire_notify_date := p_expire_date - l_expiry_interval ;
254
255 IF (l_expire_notify_date < l_wf_start_date) THEN
256 l_expire_notify_date := l_wf_start_date ;
257 END IF ;
258
259 WF_ENGINE.SETITEMATTRDATE (itemtype => l_itemtype,
260 itemkey => l_itemkey,
261 aname => 'EXPIRE_NOTIFY_DATE',
262 avalue => l_expire_notify_date);
263
264 WF_ENGINE.SETITEMATTRDATE (itemtype => l_itemtype,
265 itemkey => l_itemkey,
266 aname => 'EXPIRE_NOTIFY_TIME',
267 avalue => l_expire_notify_date ) ;
268
269 /* start the Workflow process */
270
271 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,
272 itemkey => l_itemkey);
273
274 EXCEPTION
275
276 WHEN wf_item_already_exists THEN
277 Null;
278 WHEN OTHERS THEN
279
280 WF_CORE.CONTEXT ('gm_wf_lot_expiry',
281 'init_wf',
282 l_itemtype,
283 l_itemkey,
284 p_lot_no,
285 p_sublot_no) ;
286 WF_CORE.GET_ERROR (l_errname, l_errmsg, l_errstack);
287
288 END init_wf;
289
290 PROCEDURE verify_expiry (
291 /* procedure to confirm lot expiration called via Workflow
292 input/output parameters conform to WF standard (see WF FAQ) */
293 p_itemtype IN VARCHAR2,
294 p_itemkey IN VARCHAR2,
295 p_actid IN NUMBER,
296 p_funcmode IN VARCHAR2,
297 p_resultout OUT NOCOPY VARCHAR2
298 )
299 IS
300 l_count_lots_mst NUMBER := 0;
301 l_sum_loct_onhand ic_loct_inv.loct_onhand%TYPE := 0 ;
302 l_sum_loct_onhand2 ic_loct_inv.loct_onhand2%TYPE := 0 ;
303
304 l_lot_id ic_lots_mst.lot_id%TYPE := TO_NUMBER(LTRIM(p_itemkey, 'EX')) ;
305 l_lot_no ic_lots_mst.lot_no%TYPE ;
306 l_sublot_no ic_lots_mst.sublot_no%TYPE ;
307
308 l_sqlcode NUMBER;
309 l_sqlerrm VARCHAR2(512);
310 l_errname VARCHAR2(30);
311 l_errstack VARCHAR2(32000);
312
313 l_continue_execution BOOLEAN := TRUE ;
314
315 l_date_now DATE ;
316
317 BEGIN
318 IF (p_funcmode = 'RUN') THEN
319
320 SELECT COUNT(*)
321 INTO l_count_lots_mst
322 FROM ic_lots_mst
323 WHERE lot_id = l_lot_id
324 AND delete_mark = 0 ;
325
326 IF (l_count_lots_mst <> 1) THEN
327 p_resultout := 'COMPLETE:LOTDEL' ;
328 l_continue_execution := FALSE ;
329 END IF ;
330
331 IF l_continue_execution THEN
332
333 /* find out quantity we have on hand */
334 SELECT nvl(SUM(loct_onhand),0) , nvl(SUM(loct_onhand2),0)
335 INTO l_sum_loct_onhand , l_sum_loct_onhand2
336 FROM ic_loct_inv
337 WHERE lot_id = l_lot_id
338 AND delete_mark = 0 ;
339
340 IF (
341 /* no rows found in ic_loct_inv
342 quantities total zero */
343 ( l_sum_loct_onhand + l_sum_loct_onhand2 ) = 0
344 ) THEN
345 p_resultout := 'COMPLETE:ZERO' ;
346 ELSE
347 p_resultout := 'COMPLETE:INVEXIST';
348 END IF ;
349
350 /* save inventory quantities in WF item attributes */
351 WF_ENGINE.SETITEMATTRNUMBER (itemtype => p_itemtype,
352 itemkey => p_itemkey,
353 aname => 'QUANTITY',
354 avalue => l_sum_loct_onhand) ;
355 WF_ENGINE.SETITEMATTRNUMBER (itemtype => p_itemtype,
356 itemkey => p_itemkey,
357 aname => 'QUANTITY2',
358 avalue => l_sum_loct_onhand2) ;
359
360 END IF ;
361
362 ELSIF (p_funcmode = 'CANCEL') THEN
363 p_resultout := 'COMPLETE' ;
364
365 ELSIF (p_funcmode = 'TIMEOUT') THEN
366 p_resultout := 'COMPLETE' ;
367
368 ELSE
369 WF_ENGINE.SETITEMATTRTEXT (itemtype => p_itemtype,
370 itemkey => p_itemkey,
371 aname => 'ERRMSG',
372 avalue => 'Bad p_funcmode passed to ' ||
373 'the workflow lot expiry ' ||
374 'verify_expiry process for lot ' || l_lot_no ||
375 'sublot ' || l_sublot_no ||
376 'p_funcmode value = '|| p_funcmode || ' .') ;
377 p_resultout := 'COMPLETE:VERERR' ;
378 END IF ;
379
380 EXCEPTION
381
382 WHEN OTHERS THEN
383
384 l_sqlcode := SQLCODE;
385 l_sqlerrm := SQLERRM(-l_sqlcode);
386 l_lot_no := WF_ENGINE.GETITEMATTRTEXT (p_itemtype,
387 p_itemkey,
388 'LOT_NO');
389
390 l_sublot_no := WF_ENGINE.GETITEMATTRTEXT (p_itemtype,
391 p_itemkey,
392 'SUBLOT_NO');
393
394 WF_ENGINE.SETITEMATTRTEXT (itemtype => p_itemtype,
395 itemkey => p_itemkey,
396 aname => 'ERRMSG',
397 avalue => 'A database error occurred in ' ||
398 'the workflow lot expiry ' ||
399 'process for lot ' || l_lot_no ||
400 'sublot ' || l_sublot_no ||
401 '. Message text: ' || l_sqlerrm);
402
403 p_resultout := 'ERROR:VERERR' ;
404
405 END verify_expiry;
406
407 END gmi_wf_lot_expiry;