[Home] [Help]
PACKAGE BODY: APPS.GMD_SS_LOT_WF_PKG
Source
1 PACKAGE BODY GMD_SS_LOT_WF_PKG AS
2 /* $Header: GMDQSSLB.pls 120.7 2006/12/05 16:08:36 rlnagara noship $ */
3
4
5 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7 PROCEDURE VERIFY_EVENT(
8 /* procedure to verify event and send out notifications*/
9 p_itemtype IN VARCHAR2,
10 p_itemkey IN VARCHAR2,
11 p_actid IN NUMBER,
12 p_funcmode IN VARCHAR2,
13 p_resultout OUT NOCOPY VARCHAR2)
14
15 IS
16 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
17 itemtype=>p_itemtype,
18 itemkey=>P_itemkey,
19 aname=>'EVENT_NAME');
20 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
21 itemtype=>p_itemtype,
22 itemkey=>P_itemkey,
23 aname=>'EVENT_KEY');
24
25 l_current_approver varchar2(240);
26
27 l_application_id number;
28 l_transaction_type varchar2(100):='GMDQM_STABILITY_LOT';
29 l_user varchar2(32);
30 Approver ame_util.approverRecord;
31
32
33 l_form varchar2(240);
34 l_itemtype varchar2(240);
35 l_itemkey varchar2(240);
36 l_workflow_process varchar2(240);
37 l_log varchar2(4000);
38 I NUMBER;
39 l_inventory_item_id NUMBER; --RLNAGARA B4705797
40 l_item_no varchar2(240);
41 l_item_revision varchar2(3); --Added as part of convergence
42 l_item_desc varchar2(240);
43 l_item_um varchar2(32);
44 l_lot_no varchar2(240);
45 l_ss_id number; -- BUG#4705797
46 l_test_replicate varchar2(240);
47 l_orgn_code varchar2(240);
48 l_sample_id varchar2(240);
49 l_sample_no varchar2(240);
50 l_sample_desc varchar2(240);
51 l_sample_type varchar2(240);
52 l_resource_desc varchar2(240);
53 l_resource_num number ;
54 l_study varchar2(240);
55 l_study_desc varchar2(240);
56 l_batch_no varchar2(240);
57 l_recipe_no varchar2(240);
58 l_recipe_vers varchar2(240);
59 l_storage_whse varchar2(240);
60 l_storage_location varchar2(240);
61 l_storage_spec varchar2(240);
62 l_storage_spec_vers varchar2(240);
63 l_resource varchar2(240);
64 l_formula varchar2(240);
65 l_formula_vers varchar2(240);
66 l_package varchar2(240);
67 l_sample_qty varchar2(240);
68 l_sample_uom varchar2(240);
69 l_status varchar2(240);
70 l_variant_no varchar2(240);
71 l_study_date varchar2(240);
72 l_sample_event_id varchar2(240);
73 l_owner number;
74 l_owner_used number := 0;
75
76
77 /*=====================================
78 BUG#4705797 Added ss_id to cursor.
79 BUG#4705867 Added sample_qty and uom
80 to cursor.
81 BUG#4912224 Replaced mtl_organizations
82 with mtl_parameters.
83 =====================================*/
84 --RLNAGARA B4705797 Added inventory_item_id to the select list of the CURSOR C1
85 CURSOR C1 IS
86 SELECT DISTINCT a.lot_number ,b.meaning, e.ss_no, f.description,e.inventory_item_id,
87 g.concatenated_segments item_no, e.revision item_revision,
88 g.description item_desc1, h.organization_code, e.owner, e.ss_id,
89 a.sample_qty, a.sample_quantity_uom
90 FROM gmd_ss_material_sources a,
91 gmd_Qc_status b,
92 gmd_stability_studies_b e ,
93 gmd_stability_studies_tl f ,
94 mtl_system_items_kfv g,
95 mtl_parameters h
96 WHERE a.source_id = l_event_key
97 AND a.ss_id = e.ss_id
98 AND e.ss_id = f.ss_id
99 AND b.entity_type = 'STABILITY'
100 AND b.status_code = e.status
101 AND g.inventory_item_id = e.inventory_item_id
102 AND g.organization_id = e.organization_id
103 AND h.organization_id = e.organization_id
104 AND f.language = userenv('LANG');
105
106 CURSOR get_from_role IS
107 SELECT nvl( text, '')
108 FROM wf_Resources where name = 'WF_ADMIN_ROLE' --RLNAGARA B5654562 Changed from WF_ADMIN to WF_ADMIN_ROLE
109 AND language = userenv('LANG');
110
111 --JD 10/20/2005 added language to role.
112
113 l_from_role varchar2(240);
114
115 BEGIN
116
117
118 IF (l_debug = 'Y') THEN
119 gmd_debug.log_initialize('StabStudyLot');
120 gmd_debug.put_line('Event Name ' || l_event_name );
121 gmd_debug.put_line('Event Key ' || l_event_key );
122
123 END IF;
124
125 open get_from_role ;
126 fetch get_from_role into l_from_role ;
127 close get_from_role ;
128
129
130 IF P_FUNCMODE='RUN' THEN
131 /* Get application_id from FND_APPLICATION */
132 select application_id into l_application_id
133 from fnd_application where application_short_name='GMD';
134
135 /* Check which event has been raised */
136 wf_log_pkg.string(6, 'Dummy','Entered Stability Study Timepoint Testing '||l_event_key);
137
138
139 OPEN C1;
140 Fetch C1 into l_lot_no, l_status, l_study,l_study_desc, l_inventory_item_id, l_item_no, l_item_revision,
141 l_item_desc ,l_orgn_code, l_owner, l_ss_id, l_sample_qty, l_sample_uom ;
142 /*=====================================
143 BUG#4705797 - added ss_id to cursor
144 and changed parm to be ss_id.
145 BUG#4705867 - added sample_qty and
146 uom to cursor.
147 =====================================*/
148
149 /* Set Form Attribute to the sampling event */
150
151 --RLNAGARA B4705797 Corrected the Form to be opened.
152 --l_form := 'GMDQSSVT_F:SS_ID="'||l_ss_id||'"';
153 l_form := 'GMDQSMPL_EDIT_F:INVENTORY_ITEM_ID="'||l_inventory_item_id||'" LOT_NUMBER="'
154 ||l_lot_no||'" REVISION="'||l_item_revision||'" SAMPLE_TYPE="I" SOURCE="I"';
155
156
157 IF (l_debug = 'Y') THEN
158 gmd_debug.put_line('Checking on approvers ');
159 END IF;
160
161 /* Start the Workflow for the Given Combination */
162 ame_api.clearAllApprovals(applicationIdIn => l_application_id,
163 transactionIdIn => l_event_key,
164 transactionTypeIn => l_transaction_type);
165
166 wf_log_pkg.string(6, 'Dummy','Approvers Cleared');
167 ame_api.getNextApprover(applicationIdIn => l_application_id,
168 transactionIdIn => l_event_key,
169 transactionTypeIn => l_transaction_type,
170 nextApproverOut => Approver);
171
172 if(Approver.user_id is null and Approver.person_id is null) then
173 /* No Approval Required */
174 /* P_resultout:='COMPLETE:N';
175 return; */
176 select user_name into l_user from fnd_user
177 where user_id = l_owner;
178 l_owner_used := 1;
179 end if;
180
181 if (l_user is null) then
182 if(Approver.person_id is null) then
183 select user_name into l_user from fnd_user
184 where user_id=Approver.user_id;
185 else
186 select user_name into l_user from fnd_user
187 where user_id=ame_util.PERSONIDTOUSERID(Approver.person_id);
188 end if;
189 end if ;
190
191 IF (l_debug = 'Y') THEN
192 gmd_debug.put_line('Approver ' || l_user);
193 END IF;
194
195
196 l_itemtype:='GMDQSLOT';
197 l_itemkey:=l_event_key||'-'||to_char(sysdate,'dd/mm/yy hh:mi:ss');
198
199 l_workflow_process:='GMDQSLOT_SUB_PROCESS';
200
201 IF (l_debug = 'Y') THEN
202 gmd_debug.put_line('Going to set workflow attributes ');
203 END IF;
204
205 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype,
206 itemkey => l_itemkey,
207 process => l_workflow_process );
208
209
210 /* Set the User Attribute */
211
212 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
213 aname => 'CURRENT_APPROVER',
214 avalue => l_user);
215 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
216 aname => 'APPS_FORM',
217 avalue =>l_form );
218 /* Set All other Attributes */
219 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => p_itemkey,
220 aname => 'EVENT_NAME',
221 avalue =>l_event_name );
222 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
223 aname => 'EVENT_KEY',
224 avalue =>l_event_key );
225 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
226 aname => 'ORG',
227 avalue =>l_orgn_code );
228 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
229 aname => 'ITEM_NO',
230 avalue =>l_item_no );
231 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
232 aname => 'ITEM_REVISION',
233 avalue =>l_item_revision );
234 -- JD 10/20/2005 Changed item_desc to l_item_revision above.
235
236 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
237 aname => 'ITEM_DESC',
238 avalue =>l_item_desc );
239 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
240 aname => 'LOT_NO',
241 avalue =>l_lot_no );
242 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
243 aname => 'SAMPLE_QTY',
244 avalue =>l_sample_qty );
245 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
246 aname => 'SAMPLE_UOM',
247 avalue =>l_sample_uom );
248 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
249 aname => 'STUDY',
250 avalue =>l_study );
251 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
252 aname => 'STUDY_DESC',
253 avalue =>l_study_desc );
254 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
255 aname => 'STATUS',
256 avalue =>l_status );
257 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
258 aname => '#FROM_ROLE',
259 avalue =>l_from_role );
260
261 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
262 aname => 'MSG_DOCUMENT',
263 avalue =>
264 'plsqlclob:GMD_SS_LOT_WF_PKG.Get_WF_Notif/'||l_event_key );
265
266
267 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
268 aname => 'AME_TRANS',
269 avalue =>l_transaction_type);
270
271 wf_log_pkg.string(6, 'Dummy','Setting Parent');
272
273
274 WF_ENGINE.SETITEMPARENT(itemtype =>l_itemtype,itemkey =>l_itemkey,
275 parent_itemtype => p_itemtype,
276 parent_itemkey=> p_itemkey,
277 parent_context=> NULL);
278
279 /* start the Workflow process */
280 wf_log_pkg.string(6, 'Dummy','Starting Process');
281
282 IF (l_debug = 'Y') THEN
283 gmd_debug.put_line('Completed workflow attributes ');
284 END IF;
285
286
287
288 /* As this a pure FYI notification we will set the approver to approve status */
289 Approver.approval_status := ame_util.approvedStatus;
290
291 if (l_owner_used = 0) then
292 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
293 transactionIdIn => l_event_key,
294 approverIn => Approver,
295 transactionTypeIn => l_transaction_type,
296 forwardeeIn => ame_util.emptyApproverRecord);
297 end if;
298
299
300 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
301
302 IF (l_debug = 'Y') THEN
303 gmd_debug.put_line('Created workflow process ');
304 END IF;
305
306
307 close C1;
308
309
310 END IF;
311
312
313 p_resultout:='COMPLETE:';
314
315 EXCEPTION
316 WHEN OTHERS THEN
317 WF_CORE.CONTEXT ('GMD_SS_LOT_WF_PKG','VERIFY_EVENT',p_itemtype,p_itemkey,l_log );
318 raise;
319
320 END VERIFY_EVENT;
321
322
323
324 PROCEDURE CHECK_NEXT_APPROVER(
325 p_itemtype IN VARCHAR2,
326 p_itemkey IN VARCHAR2,
327 p_actid IN NUMBER,
328 p_funcmode IN VARCHAR2,
329 p_resultout OUT NOCOPY VARCHAR2)
330
331 IS
332 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
333 itemtype=>p_itemtype,
334 itemkey=>P_itemkey,
335 aname=>'EVENT_NAME');
336 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
337 itemtype=>p_itemtype,
338 itemkey=>P_itemkey,
339 aname=>'EVENT_KEY');
340
341 l_current_approver varchar2(240);
342
343 l_application_id number;
344 l_transaction_type varchar2(100):=WF_ENGINE.GETITEMATTRTEXT(
345 itemtype=>p_itemtype,
346 itemkey=>P_itemkey,
347 aname=>'AME_TRANS');
348 l_user varchar2(32);
349 Approver ame_util.approverRecord;
350 l_form varchar2(240);
351 BEGIN
352
353 /* Get Next Approver */
354 /* Get application_id from FND_APPLICATION */
355 select application_id into l_application_id
356 from fnd_application where application_short_name='GMD';
357
358 ame_api.getNextApprover(applicationIdIn => l_application_id,
359 transactionIdIn => l_event_key,
360 transactionTypeIn => l_transaction_type,
361 nextApproverOut => Approver);
362
363
364 if(Approver.user_id is null and Approver.person_id is null) then
365 /* No Approval Required */
366 P_resultout:='COMPLETE:N';
367 else
368 if(Approver.person_id is null) then
369 select user_name into l_user from fnd_user
370 where user_id=Approver.user_id;
371 else
372 select user_name into l_user from fnd_user
373 where user_id=ame_util.PERSONIDTOUSERID(Approver.person_id);
374 end if;
375
376 /* Set the User Attribute */
377
378 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
379 aname => 'CURRENT_APPROVER',
380 avalue => l_user);
381
382 P_resultout:='COMPLETE:Y';
383 Approver.approval_status := ame_util.approvedStatus;
384 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
385 transactionIdIn => l_event_key,
386 approverIn => Approver,
387 transactionTypeIn => l_transaction_type,
388 forwardeeIn => ame_util.emptyApproverRecord);
389 end if;
390 EXCEPTION
391 WHEN OTHERS THEN
392 WF_CORE.CONTEXT ('GMD_SS_LOT_WF_PKG','CHECK_NEXT_APPROVER',p_itemtype,p_itemkey,'Initial' );
393 raise;
394
395 END CHECK_NEXT_APPROVER;
396
397
398 PROCEDURE Get_WF_Notif(document_id in varchar2,
399 display_type in varchar2,
400 document in out nocopy clob,
401 document_type in out nocopy varchar2) IS
402
403
404 l_document_id gmd_ss_material_sources.source_id%TYPE;
405 l_source number := to_number(document_id);
406 l_document VARCHAR2(32000) := '';
407 NL VARCHAR2(1) := fnd_global.newline;
408
409 l_resource_desc varchar2(240);
410 l_resource_num number ;
411 l_study varchar2(240);
412 l_study_desc varchar2(240);
413 l_batch_no varchar2(240);
414 l_recipe_no varchar2(240);
415 l_recipe_vers varchar2(240);
416 l_storage_subinventory varchar2(10);
417 l_storage_locator varchar2(204);
418 l_storage_spec varchar2(240);
419 l_storage_spec_vers varchar2(240);
420 l_resource varchar2(240);
421 l_formula varchar2(240);
422 l_formula_vers varchar2(240);
423 l_package varchar2(240);
424 l_sample_qty varchar2(240);
425 l_sample_uom varchar2(240);
426 l_date varchar2(240);
427
428
429 /* This cursor Will Pick up all material sources for a Given Sample */
430 --Cursor C1 is modified for Convergence
431 Cursor C1 is
432 select distinct b.batch_no,a.recipe_no, c.recipe_version,
433 h.sample_qty, h.sample_quantity_uom , h.storage_subinventory, k.concatenated_segments storage_locator,
434 h.resources, i.spec_name, i.spec_vers, h.scheduled_start_date
435 , j.formula_no, j.formula_vers , d.package_name
436 from gmd_ss_material_sources a,
437 gme_batch_header b,
438 gmd_recipes c,
439 gmd_ss_storage_package d,
440 gmd_stability_studies_b e ,
441 gmd_stability_studies_tl f ,
442 gmd_ss_variants h,
443 gmd_specifications i,
444 fm_form_mst j,
445 mtl_item_locations_kfv k
446 where a.source_id = l_source
447 and a.recipe_id = c.recipe_id(+)
448 and a.batch_id = b.batch_id(+)
449 and a.ss_id = e.ss_id
450 and h.material_source_id = a.source_id
451 and h.storage_spec_id = i.spec_id
452 and h.package_id = d.package_id(+)
453 and d.formula_id = j.formula_id(+)
454 and h.storage_locator_id = k.inventory_location_id(+) ;
455
456
457 BEGIN
458 /* Add a new line */
459 WF_NOTIFICATION.WriteToClob(document,NL );
460
461 OPEN C1;
462 LOOP
463
464 wf_log_pkg.string(6, 'Dummy','Before Fetching the values. Inside the Loop');
465
466 /*Fetching values from the cursor*/
467 Fetch C1 into l_batch_no, l_recipe_no, l_recipe_vers, l_sample_qty,
468 l_sample_uom, l_storage_subinventory, l_storage_locator, l_resource,
469 l_storage_spec, l_storage_spec_vers, l_date,
470 l_formula, l_formula_vers, l_package ;
471 EXIT when c1%notfound;
472
473 /*Use an FND message and populate it */
474 FND_MESSAGE.SET_NAME('GMD','GMD_SS_LOT_BODY');
475 FND_MESSAGE.SET_TOKEN('BATCH', L_BATCH_NO);
476 FND_MESSAGE.SET_TOKEN('RECIPE', l_Recipe_no);
477 FND_MESSAGE.SET_TOKEN('RCP_VERSION', l_recipe_vers);
478 FND_MESSAGE.SET_TOKEN('STORAGE_SPEC', l_storage_spec);
479 FND_MESSAGE.SET_TOKEN('STOR_SPEC_VERSION', l_storage_spec_vers);
480 FND_MESSAGE.SET_TOKEN('RESOURCE',l_resource);
481 FND_MESSAGE.SET_TOKEN('STORAGE_SUBINVENTORY',l_storage_subinventory);
482 FND_MESSAGE.SET_TOKEN('STORAGE_LOCATOR',l_storage_locator);
483 FND_MESSAGE.SET_TOKEN('PACKAGE',l_package);
484 FND_MESSAGE.SET_TOKEN('FORMULA',l_formula);
485 FND_MESSAGE.SET_TOKEN('FORM_VER',l_formula_vers);
486 FND_MESSAGE.SET_TOKEN('SAMPLE_QTY',l_sample_qty);
487 FND_MESSAGE.SET_TOKEN('SAMPLE_UOM',l_sample_uom);
488 FND_MESSAGE.SET_TOKEN('STORAGE_DATE',l_date);
489
490
491 WF_NOTIFICATION.WriteToClob(document, FND_MESSAGE.GET() );
492 WF_NOTIFICATION.WriteToClob(document, NL );
493 WF_NOTIFICATION.WriteToClob(document, NL );
494
495
496 END LOOP;
497 CLOSE C1;
498
499 END;
500
501 END GMD_SS_LOT_WF_PKG ;