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