[Home] [Help]
PACKAGE BODY: APPS.GMD_QMSED
Source
1 PACKAGE BODY GMD_QMSED AS
2 /* $Header: GMDQMSEB.pls 120.5.12010000.2 2009/03/18 21:16:55 plowe ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6
7 PROCEDURE VERIFY_EVENT(
8 /* procedure to verify event if the event is sample disposition or sample event disposition */
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);
29 l_user varchar2(32);
30 Approver ame_util.approverRecord;
31 l_item_no varchar2(240);
32 l_item_desc varchar2(240);
33 l_lot_no varchar2(240);
34 l_sample_no varchar2(240);
35 l_sample_plan varchar2(240);
36 l_sample_disposition varchar2(240);
37 l_sample_source varchar2(240);
38 l_specification varchar2(240);
39 l_validity_rule varchar2(240);
40 l_validity_rule_version varchar2(240);
41 l_sample_event_text varchar2(4000);
42 l_sampling_event_id number;
43 l_sample_desc varchar2(240);
44 l_form varchar2(240);
45 l_log varchar2(200);
46 l_sample_event_count number ;
47 l_sample_id number;
48 l_item_revision varchar2(20);
49 l_orgn_code varchar2(20);
50 l_spec_vers varchar2(20);
51 l_grade_code varchar2(150);
52 -- Bug# 5221298
53 l_spec_vr_id number;
54 l_sampling_plan_id number;
55 l_lpn_id NUMBER; --RLNAGARA LPN ME 7027149
56 l_lpn VARCHAR2(240); --RLNAGARA LPN ME 7027149
57
58 cursor get_from_role is
59 select nvl( text, '')
60 from wf_Resources where name = 'WF_ADMIN_ROLE' --RLNAGARA B5654562 Changed from WF_ADMIN to WF_ADMIN_ROLE
61 and language = userenv('LANG') ;
62
63 l_from_role varchar2(2000);
64
65 BEGIN
66
67 IF (l_debug = 'Y') THEN
68 gmd_debug.log_initialize('SampleDisposition');
69 gmd_debug.put_line('Event ' || l_event_name);
70 gmd_debug.put_line('Event key ' || l_event_key);
71 END IF;
72
73
74 open get_from_role ;
75 fetch get_from_role into l_from_role ;
76 close get_from_role ;
77
78 /* Check which event has been raised */
79
80 if l_event_name = 'oracle.apps.gmd.qm.sample.disposition' then
81 l_transaction_type:='GMDQMSD';
82 l_form := 'GMDQSMPL_EDIT_F:SAMPLE_ID="'||l_event_key||'"';
83
84 -- B 3051565 PK changed the cursor to read Sample disposition from gmd_event_spec_disp
85
86 /*SELECT A.SAMPLE_NO,A.SAMPLE_DESC,A.SAMPLING_EVENT_ID,A.REVISION,B.CONCATENATED_SEGMENTS,B.DESCRIPTION,
87 A.LOT_NUMBER,GES.DISPOSITION,A.SOURCE,D.SPEC_NAME||' / '||to_char(D.SPEC_VERS),
88 D.SPEC_VERS,D.GRADE_CODE, mp.organization_code
89 INTO L_SAMPLE_NO,L_SAMPLE_DESC,l_sampling_Event_id,L_ITEM_REVISION,L_ITEM_NO,L_ITEM_DESC,
90 L_LOT_NO,L_SAMPLE_DISPOSITION,L_SAMPLE_SOURCE,L_SPECIFICATION,
91 L_SPEC_VERS, L_GRADE_CODE, L_ORGN_CODE
92 FROM gmd_samples a,mtl_system_items_kfv b,
93 gmd_sampling_events c,gmd_all_spec_vrs_vl d ,
94 gmd_sampling_events gse,
95 gmd_event_spec_disp ges,
96 gmd_sample_spec_disp gss,
97 mtl_parameters mp
98 WHERE
99 A.sample_id=l_event_key AND
100 a.inventory_item_id=b.inventory_item_id AND
101 a.sampling_event_id=c.sampling_event_id AND
102 c.original_spec_vr_id=d.spec_vr_id AND
103 a.sampling_event_id = gse.sampling_event_id AND
104 gse.sampling_event_id = ges.sampling_event_id AND
105 ges.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y' AND
106 ges.event_Spec_disp_id = gss.event_spec_disp_id AND
107 gss.sample_id = a.sample_id AND
108 ges.delete_mark = 0 AND
109 mp.organization_id = a.organization_id AND
110 b.organization_id = a.organization_id; --RLNAGARA added this condition as item is always bind to Organization in R12*/
111
112 --Rewritten the above query as part of performance bug# 4916904
113 /*SELECT A.SAMPLE_NO,A.SAMPLE_DESC,A.SAMPLING_EVENT_ID,A.REVISION,B.CONCATENATED_SEGMENTS,B.DESCRIPTION,
114 A.LOT_NUMBER,GES.DISPOSITION,A.SOURCE,E.SPEC_NAME||' / '||to_char(E.SPEC_VERS),
115 E.SPEC_VERS,E.GRADE_CODE, mp.organization_code
116 INTO L_SAMPLE_NO,L_SAMPLE_DESC,l_sampling_Event_id,L_ITEM_REVISION,L_ITEM_NO,L_ITEM_DESC,
117 L_LOT_NO,L_SAMPLE_DISPOSITION,L_SAMPLE_SOURCE,L_SPECIFICATION,
118 L_SPEC_VERS, L_GRADE_CODE, L_ORGN_CODE
119 FROM gmd_samples a,
120 mtl_system_items_kfv b,
121 gmd_sampling_events c,
122 gmd_com_spec_vrs_vl d ,
123 gmd_specifications e,
124 gmd_sampling_events gse,
125 gmd_event_spec_disp ges,
126 gmd_sample_spec_disp gss,
127 mtl_parameters mp
128 WHERE
129 A.sample_id=l_event_key AND
130 a.inventory_item_id=b.inventory_item_id AND
131 a.sampling_event_id=c.sampling_event_id AND
132 c.original_spec_vr_id=d.spec_vr_id AND
133 d.spec_id = e.spec_id AND
134 a.sampling_event_id = gse.sampling_event_id AND
135 gse.sampling_event_id = ges.sampling_event_id AND
136 ges.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y' AND
137 ges.event_Spec_disp_id = gss.event_spec_disp_id AND
138 gss.sample_id = a.sample_id AND
139 ges.delete_mark = 0 AND
140 mp.organization_id = a.organization_id AND
141 b.organization_id = a.organization_id;*/
142
143 -- Rewritten the above query as part of performance Bug# 5221298
144 SELECT
145 a.sample_no,a.sample_desc,a.sampling_event_id,a.revision,b.concatenated_segments,b.description,
146 a.lot_number,a.lpn_id,ges.disposition,a.source,e.spec_name||' / '||to_char(e.spec_vers),
147 e.spec_vers,e.grade_code, mp.organization_code
148 INTO L_SAMPLE_NO,L_SAMPLE_DESC,l_sampling_Event_id,L_ITEM_REVISION,L_ITEM_NO,L_ITEM_DESC,
149 L_LOT_NO,l_lpn_id,L_SAMPLE_DISPOSITION,L_SAMPLE_SOURCE,L_SPECIFICATION,
150 L_SPEC_VERS, L_GRADE_CODE, L_ORGN_CODE
151 FROM
152 gmd_samples a,
153 mtl_system_items_kfv b,
154 gmd_specifications_b e,
155 gmd_sampling_events gse,
156 (SELECT ges.sampling_event_id , ges.disposition ,ges.spec_vr_id,ges.spec_id
157 FROM gmd_event_spec_disp ges, gmd_sample_spec_disp gss
158 WHERE spec_used_for_lot_attrib_ind = 'Y'
159 AND ges.event_spec_disp_id = gss.event_spec_disp_id
160 AND ges.delete_mark = 0
161 AND gss.sample_id = l_event_key ) ges,
162 mtl_parameters mp
163 WHERE a.sample_id = l_event_key AND
164 a.inventory_item_id = b.inventory_item_id AND
165 ges.spec_id = e.spec_id AND
166 a.sampling_event_id = gse.sampling_event_id AND
167 gse.sampling_event_id = ges.sampling_event_id AND
168 mp.organization_id = a.organization_id AND
169 b.organization_id = a.organization_id;
170
171
172 SELECT meaning INTO l_sample_DISPOSITION FROM
173 gem_lookups WHERE LOOKUP_TYPE='GMD_QC_SAMPLE_DISP'
174 AND lookup_code=l_sample_disposition;
175
176 ELSIF l_event_name = 'oracle.apps.gmd.qm.samplingevent.disposition' THEN
177
178 /* Check to see the number of active samples in this sampling event */
179 SELECT SAMPLE_ACTIVE_CNT INTO l_sample_event_count
180 FROM gmd_Sampling_events
181 WHERE sampling_event_id = l_event_key ;
182
183 /* If more than one active sample then go to the
184 Composite Results form; otherwise go to Samples Form */
185 IF l_sample_event_count > 1 THEN
186 l_form := 'GMDQCMPS_F:SAMPLING_EVENT_ID="'||l_event_key||'"';
187 ELSE
188 SELECT s.sample_id INTO l_sample_id
189 FROM gmd_samples s,
190 gmd_event_spec_disp esd,
191 gmd_sample_spec_disp ssd
192 WHERE esd.sampling_event_id = l_event_key
193 AND esd.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
194 AND esd.sampling_event_id = s.sampling_event_id
195 AND esd. EVENT_SPEC_DISP_ID = ssd.EVENT_SPEC_DISP_ID
196 AND ssd.disposition NOT IN ('0RT', '7CN') ;
197
198 l_form := 'GMDQSMPL_EDIT_F:SAMPLE_ID="'||l_sample_id||'"';
199 END IF;
200
201
202 l_transaction_type:='GMDQMSED';
203 /* l_form := 'GMDQSMGP_F:SAMPLING_EVENT_ID="'||l_event_key||'"'; */
204 /*SELECT c.CONCATENATED_SEGMENTS,c.DESCRIPTION,A.LOT_NUMBER,
205 A.SOURCE,SPEC_NAME||' / '||to_char(SPEC_VERS),
206 d.SAMPLING_PLAN_NAME||' / '||d.SAMPLING_PLAN_DESC ,b.revision,b.organization_code
207 INTO L_ITEM_NO,L_ITEM_DESC,L_LOT_NO,
208 L_SAMPLE_SOURCE,L_SPECIFICATION,L_SAMPLE_PLAN,l_item_revision,l_orgn_code
209 FROM GMD_SAMPLING_EVENTS A
210 ,GMD_ALL_SPEC_VRS_VL B
211 ,MTL_SYSTEM_ITEMS_KFV C
212 ,GMD_SAMPLING_PLANS D
213 WHERE a.original_spec_vr_id=b.SPEC_VR_ID AND
214 b.inventory_item_id=c.inventory_item_id AND
215 a.sampling_plan_id=d.sampling_plan_id(+) AND
216 a.sampling_event_id=l_event_key;*/
217
218 --Rewritten the above query as part of performance bug# 4916904
219 /*SELECT c.CONCATENATED_SEGMENTS, c.DESCRIPTION, A.LOT_NUMBER,
220 A.SOURCE, e.SPEC_NAME||' / '||to_char(e.SPEC_VERS),
221 d.SAMPLING_PLAN_NAME||' / '||d.SAMPLING_PLAN_DESC ,e.revision, f.organization_code
222 INTO L_ITEM_NO,L_ITEM_DESC,L_LOT_NO,
223 L_SAMPLE_SOURCE,L_SPECIFICATION,L_SAMPLE_PLAN,l_item_revision,l_orgn_code
224 FROM GMD_SAMPLING_EVENTS A
225 ,GMD_COM_SPEC_VRS_VL B
226 ,MTL_SYSTEM_ITEMS_B_KFV C
227 ,GMD_SAMPLING_PLANS D
228 ,GMD_SPECIFICATIONS_B E
229 ,MTL_PARAMETERS F
230 WHERE a.original_spec_vr_id=b.SPEC_VR_ID AND
231 b.spec_id = e.spec_id AND
232 e.inventory_item_id=c.inventory_item_id AND
233 b.organization_id = f.organization_id(+) AND
234 a.sampling_plan_id=d.sampling_plan_id(+) AND
235 a.sampling_event_id= l_event_key;*/
236
237 -- Rewritten the above query as part of performance bug# 5221298
238 -- Split the above SQL into following 3 SQL statements
239 select original_spec_vr_id, sampling_plan_id
240 into l_spec_vr_id, l_sampling_plan_id
241 from gmd_sampling_events
242 where sampling_event_id = l_event_key;
243
244 IF l_sampling_plan_id IS NOT NULL THEN
245 select a.sampling_plan_name || ' / ' || b.sampling_plan_desc
246 into l_sample_plan
247 from gmd_sampling_plans_b a, gmd_sampling_plans_tl b
248 where a.sampling_plan_id = b.sampling_plan_id
249 and a.sampling_plan_id = l_sampling_plan_id
250 and b.language = userenv('LANG');
251 END IF;
252
253 select c.concatenated_segments, c.description, a.lot_number,a.lpn_id,
254 a.source, e.spec_name||' / '||to_char(e.spec_vers),
255 e.revision, f.organization_code
256 INTO l_item_no,l_item_desc,l_lot_no,l_lpn_id,
257 l_sample_source,l_specification,l_item_revision,l_orgn_code
258 from gmd_sampling_events a
259 ,gmd_com_spec_vrs_vl b
260 ,mtl_system_items_b_kfv c
261 ,gmd_specifications_b e
262 ,mtl_parameters f
263 where a.original_spec_vr_id = b.spec_vr_id and
264 b.spec_id = e.spec_id and
265 e.inventory_item_id = c.inventory_item_id and
266 e.owner_organization_id = c.organization_id and --RLNAGARA B5714223 Added this condition
267 b.organization_id = f.organization_id(+) and
268 a.sampling_event_id = l_event_key and
269 b.spec_vr_id = l_spec_vr_id;
270
271 ELSIF l_event_name = 'oracle.apps.gmd.qm.compositeresults' THEN
272 l_log:='Event is composite Results';
273 l_transaction_type:='GMDQMSCR';
274 /* l_form := 'GMDQSMGP_F:SAMPLING_EVENT_ID="'||l_event_key||'"'; */
275 l_form := 'GMDQCMPS_F:SAMPLING_EVENT_ID="'||l_event_key||'"';
276 /*SELECT c.CONCATENATED_SEGMENTS,c.DESCRIPTION,A.LOT_NUMBER,A.SOURCE,SPEC_NAME||' / '||to_char(SPEC_VERS),
277 d.SAMPLING_PLAN_NAME||' / '||d.SAMPLING_PLAN_DESC ,b.revision,b.organization_code
278 INTO L_ITEM_NO,L_ITEM_DESC,L_LOT_NO,L_SAMPLE_SOURCE,L_SPECIFICATION,L_SAMPLE_PLAN ,l_item_revision,l_orgn_code
279 FROM GMD_SAMPLING_EVENTS A
280 ,GMD_ALL_SPEC_VRS_VL B
281 ,MTL_SYSTEM_ITEMS_KFV C
282 ,GMD_SAMPLING_PLANS D
283 WHERE a.original_spec_vr_id=b.SPEC_VR_ID AND
284 b.inventory_item_id=c.inventory_item_id AND
285 a.sampling_plan_id=d.sampling_plan_id(+) AND
286 a.sampling_event_id=l_event_key;*/
287
288 --Rewritten the above query as part of performance bug# 4916904
289 /* SELECT c.CONCATENATED_SEGMENTS,c.DESCRIPTION,A.LOT_NUMBER,A.SOURCE,E.SPEC_NAME||' / '||to_char(E.SPEC_VERS),
290 d.SAMPLING_PLAN_NAME||' / '||d.SAMPLING_PLAN_DESC ,e.revision,f.organization_code
291 INTO L_ITEM_NO,L_ITEM_DESC,L_LOT_NO,L_SAMPLE_SOURCE,L_SPECIFICATION,L_SAMPLE_PLAN ,l_item_revision,l_orgn_code
292 FROM GMD_SAMPLING_EVENTS A
293 ,GMD_COM_SPEC_VRS_VL B
294 ,MTL_SYSTEM_ITEMS_B_KFV C
295 ,GMD_SAMPLING_PLANS D
296 ,GMD_SPECIFICATIONS_B E
297 ,MTL_PARAMETERS F
298 WHERE a.original_spec_vr_id=b.SPEC_VR_ID AND
299 b.spec_id = e.spec_id AND
300 e.inventory_item_id=c.inventory_item_id AND
301 b.organization_id = f.organization_id(+) AND
302 a.sampling_plan_id=d.sampling_plan_id(+) AND
303 a.sampling_event_id= l_event_key; */
304
305 -- Rewritten the above query as part of performance bug# 5221298
306 -- Split the above SQL into following 3 SQL statements
307 select original_spec_vr_id, sampling_plan_id
308 into l_spec_vr_id, l_sampling_plan_id
309 from gmd_sampling_events
310 where sampling_event_id = l_event_key;
311
312 IF l_sampling_plan_id IS NOT NULL THEN
313 select a.sampling_plan_name || ' / ' || b.sampling_plan_desc
314 into l_sample_plan
315 from gmd_sampling_plans_b a, gmd_sampling_plans_tl b
316 where a.sampling_plan_id = b.sampling_plan_id
317 and a.sampling_plan_id = l_sampling_plan_id
318 and b.language = userenv('LANG');
319 END IF;
320
321 select c.concatenated_segments, c.description, a.lot_number,a.lpn_id,a.source, e.spec_name||' / '||to_char(e.spec_vers),
322 e.revision, f.organization_code
323 INTO l_item_no,l_item_desc,l_lot_no,l_lpn_id,l_sample_source,l_specification,
324 l_item_revision,l_orgn_code
325 from gmd_sampling_events a
326 ,gmd_com_spec_vrs_vl b
327 ,mtl_system_items_b_kfv c
328 ,gmd_specifications_b e
329 ,mtl_parameters f
330 where a.original_spec_vr_id = b.spec_vr_id and
331 b.spec_id = e.spec_id and
332 e.inventory_item_id = c.inventory_item_id and
333 e.owner_organization_id = c.organization_id and --RLNAGARA B5714223 Added this condition
334 b.organization_id = f.organization_id(+) and
335 a.sampling_event_id = l_event_key and
336 b.spec_vr_id = l_spec_vr_id;
337
338 END IF;
339 l_log:='Resolving Lookups';
340 /* Resolve Lookups */
341 SELECT meaning INTO l_sample_source FROM
342 gem_lookups WHERE LOOKUP_TYPE='GMD_QC_SOURCE'
343 AND lookup_code=l_sample_source;
344
345 l_log:='Resolved Lookups';
346
347 --RLNAGARA LPN ME 7027149 start
348 IF l_lpn_id IS NOT NULL THEN
349 select license_plate_number INTO l_lpn
350 from wms_license_plate_numbers
351 where lpn_id = l_lpn_id;
352 ELSE
353 l_lpn := NULL;
354 END IF;
355 --RLNAGARA LPN ME 7027149 start
356
357 /* Get First Approver */
358 /* Get application_id from FND_APPLICATION */
359 select application_id into l_application_id
360 from fnd_application where application_short_name='GMD';
361
362 IF (l_debug = 'Y') THEN
363 gmd_debug.put_line('Ckecking approvers ');
364 END IF;
365
366 ame_api.clearAllApprovals(applicationIdIn => l_application_id,
367 transactionIdIn => l_event_key,
368 transactionTypeIn => l_transaction_type);
369
370 l_log:='Approvers Cleared';
371 ame_api.getNextApprover(applicationIdIn => l_application_id,
372 transactionIdIn => l_event_key,
373 transactionTypeIn => l_transaction_type,
374 nextApproverOut => Approver);
375 l_log:='Approver : '||Approver.user_id;
376
377 if(Approver.user_id is null and Approver.person_id is null) then
378 /* No Approval Required */
379 P_resultout:='COMPLETE:NO_WORKFLOW';
380 IF (l_debug = 'Y') THEN
381 gmd_debug.put_line('No approvers ');
382 END IF;
383 return;
384 end if;
385
386 if(Approver.person_id is null) then
387 select user_name into l_user from fnd_user
388 where user_id=Approver.user_id;
389 else
390 /* select user_name into l_user from fnd_user a,per_all_people b
391 where b.person_id=Approver.person_id and
392 a.employee_id is not null and
393 a.employee_id = b.person_id; */
394
395 -- Rewritten the above query as part of performance bug# 5221298
396 /*select user_name into l_user from fnd_user a
397 where a.employee_id = Approver.person_id
398 and a.employee_id is not null
399 and exists (select 1 from per_all_people where person_id = Approver.person_id);*/
400
401 -- RLNAGARA B5714223 Replaced the above query with the below query.
402 select user_name into l_user from fnd_user
403 where user_id=ame_util.personidtouserid (approver.person_id);
404
405 end if;
406
407 /* Set the User Attribute */
408
409 IF (l_debug = 'Y') THEN
410 gmd_debug.put_line('Setting up workflow attributes ');
411 END IF;
412
413 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
414 aname => 'CURRENT_APPROVER',
415 avalue => l_user);
416 /* Set All other Attributes */
417
418 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
419 aname => 'APPS_FORM',
420 avalue =>l_form );
421 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
422 aname => 'ORGN_CODE',
423 avalue =>l_orgn_code);
424 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
425 aname => 'ITEM_NO',
426 avalue =>l_item_no );
427 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
428 aname => 'ITEM_REVISION',
429 avalue =>l_item_revision );
430 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
431 aname => 'ITEM_DESC',
432 avalue =>l_item_desc );
433 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
434 aname => 'LOT_NO',
435 avalue =>l_lot_no );
436 --RLNAGARA LPN ME 7027149
437 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
438 aname => 'LPN',
439 avalue =>l_lpn );
440 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
441 aname => 'SAMPLE_NO',
442 avalue =>l_sample_no );
443 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
444 aname => 'SAMPLE_DESC',
445 avalue =>l_sample_desc );
446 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
447 aname => 'SAMPLE_PLAN',
448 avalue =>l_sample_plan );
449 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
450 aname => 'SAMPLE_DISPOSITION',
451 avalue =>l_sample_disposition );
452 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
453 aname => 'SAMPLE_SOURCE',
454 avalue =>l_sample_SOURCE );
455 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
456 aname => 'SPECIFICATION',
457 avalue =>l_SPECIFICATION );
458 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
459 aname => 'SPECIFICATION_VERSION',
460 avalue =>l_spec_vers );
461 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
462 aname => 'GRADE_CODE',
463 avalue =>l_grade_code );
464 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
465 aname => '#FROM_ROLE',
466 avalue =>l_from_role );
467
468 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
469 aname => 'AME_TRANS',
470 avalue =>l_transaction_type );
471
472 P_resultout:='COMPLETE:'||l_transaction_type;
473 /* As this a pure FYI notification we will set the approer to approve status */
474 Approver.approval_status := ame_util.approvedStatus;
475 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
476 transactionIdIn => l_event_key,
477 approverIn => Approver,
478 transactionTypeIn => l_transaction_type,
479 forwardeeIn => ame_util.emptyApproverRecord);
480
481
482 EXCEPTION
483 WHEN OTHERS THEN
484 WF_CORE.CONTEXT ('GMD_QMSED','VERIFY_EVENT',p_itemtype,p_itemkey,l_log );
485 raise;
486
487 END VERIFY_EVENT;
488
489 PROCEDURE CHECK_NEXT_APPROVER(
490 /* procedure to verify event if the event is sample disposition or sample event disposition */
491 p_itemtype IN VARCHAR2,
492 p_itemkey IN VARCHAR2,
493 p_actid IN NUMBER,
494 p_funcmode IN VARCHAR2,
495 p_resultout OUT NOCOPY VARCHAR2)
496
497 IS
498 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
499 itemtype=>p_itemtype,
500 itemkey=>P_itemkey,
501 aname=>'EVENT_NAME');
502 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
503 itemtype=>p_itemtype,
504 itemkey=>P_itemkey,
505 aname=>'EVENT_KEY');
506
507 l_current_approver varchar2(240);
508
509 l_application_id number;
510 l_transaction_type varchar2(100):=WF_ENGINE.GETITEMATTRTEXT(
511 itemtype=>p_itemtype,
512 itemkey=>P_itemkey,
513 aname=>'AME_TRANS');
514 l_user varchar2(32);
515 Approver ame_util.approverRecord;
516 l_item_no varchar2(240);
517 l_item_desc varchar2(240);
518 l_lot_no varchar2(240);
519 l_sublot_no varchar2(240);
520 l_sample_no varchar2(240);
521 l_sample_plan varchar2(240);
522 l_sample_disposition varchar2(240);
523 l_sample_source varchar2(240);
524 l_specification varchar2(240);
525 l_validity_rule varchar2(240);
526 l_validity_rule_version varchar2(240);
527 l_sample_event_text varchar2(4000);
528 l_sampling_event_id number;
529 l_form varchar2(240);
530 BEGIN
531
532
533
534
535 /* Get Next Approver */
536 /* Get application_id from FND_APPLICATION */
537 select application_id into l_application_id
538 from fnd_application where application_short_name='GMD';
539
540 ame_api.getNextApprover(applicationIdIn => l_application_id,
541 transactionIdIn => l_event_key,
542 transactionTypeIn => l_transaction_type,
543 nextApproverOut => Approver);
544
545 if(Approver.user_id is null and Approver.person_id is null) then
546 /* No Approval Required */
547 P_resultout:='COMPLETE:N';
548 else
549 if(Approver.person_id is null) then
550 select user_name into l_user from fnd_user
551 where user_id=Approver.user_id;
552 else
553 /* select user_name into l_user from fnd_user a,per_all_people b
554 where
555 b.person_id=Approver.person_id and
556 a.employee_id is not null and
557 a.employee_id = b.person_id; */
558
559 -- Rewritten the above query as part of performance bug# 5221298
560 /*select user_name into l_user from fnd_user a
561 where a.employee_id = Approver.person_id
562 and a.employee_id is not null
563 and exists (select 1 from per_all_people where person_id = Approver.person_id);*/
564
565 --Rewritten the above query as part of fix Bug No.7656325
566
567 select user_name into l_user from fnd_user
568 where user_id=ame_util.PERSONIDTOUSERID(Approver.person_id);
569
570 end if;
571
572 /* Set the User Attribute */
573
574 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
575 aname => 'CURRENT_APPROVER',
576 avalue => l_user);
577 P_resultout:='COMPLETE:Y';
578 Approver.approval_status := ame_util.approvedStatus;
579 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
580 transactionIdIn => l_event_key,
581 approverIn => Approver,
582 transactionTypeIn => l_transaction_type,
583 forwardeeIn => ame_util.emptyApproverRecord);
584 end if;
585 EXCEPTION
586 WHEN OTHERS THEN
587 WF_CORE.CONTEXT ('GMD_QMSED','CHECK_NEXT_APPROVER',p_itemtype,p_itemkey,'Initial' );
588 raise;
589
590 END CHECK_NEXT_APPROVER;
591
592
593
594 END GMD_QMSED;