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