[Home] [Help]
PACKAGE BODY: APPS.GMD_QMREJ
Source
1 PACKAGE BODY GMD_QMREJ AS
2 /* $Header: GMDQMRJB.pls 120.2.12000000.3 2007/02/07 12:06:02 rlnagara ship $ */
3
4
5 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7
8 PROCEDURE VERIFY_EVENT(
9 /* procedure to verify event and send out notifications*/
10 p_itemtype IN VARCHAR2,
11 p_itemkey IN VARCHAR2,
12 p_actid IN NUMBER,
13 p_funcmode IN VARCHAR2,
14 p_resultout OUT NOCOPY VARCHAR2)
15
16 IS
17 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
18 itemtype=>p_itemtype,
19 itemkey=>P_itemkey,
20 aname=>'EVENT_NAME');
21 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
22 itemtype=>p_itemtype,
23 itemkey=>P_itemkey,
24 aname=>'EVENT_KEY');
25
26 l_current_approver varchar2(240);
27
28 l_application_id number;
29 l_transaction_type varchar2(100):='GMDQMRJ';
30 l_user varchar2(32);
31 Approver ame_util.approverRecord;
32
33
34 l_form varchar2(240);
35 l_itemtype varchar2(240);
36 l_itemkey varchar2(240);
37 l_workflow_process varchar2(240);
38 l_log varchar2(4000);
39 I NUMBER;
40
41
42 l_orgn_code varchar2(240);
43 l_sample_type varchar2(10);
44 l_sample_type_desc varchar2(240);
45 l_sample_source varchar2(240);
46 l_source varchar2(10);
47
48 l_ss_id number ;
49 l_study_no varchar2(240);
50 l_study_desc varchar2(240);
51
52 l_item_no varchar2(240);
53 l_item_desc varchar2(240);
54 l_revision varchar2(10);
55
56 l_resource varchar2(240);
57 l_resource_desc varchar2(240);
58 l_instance_num number ;
59
60 l_subinventory varchar2(240);
61 l_locator varchar2(240);
62
63 /*Cursor to get the common fields in the workflow notification*/
64 CURSOR common_fields IS
65 select mp.organization_code, gse.sample_type, glk.meaning
66 from gmd_sampling_events gse, mtl_parameters mp, gem_lookups glk
67 where gse.sampling_event_id = l_event_key
68 and gse.organization_id = mp.organization_id
69 and glk.lookup_type = 'GMD_QC_SPEC_TYPE'
70 and gse.sample_type = glk.lookup_code;
71
72 CURSOR common_fields1 IS
73 select glk.meaning
74 from gmd_sampling_events gse, gem_lookups glk
75 where gse.sampling_event_id = l_event_key
76 and glk.lookup_type = 'GMD_QC_SOURCE'
77 and gse.source = glk.lookup_code;
78
79 CURSOR common_fields2 IS
80 select glk.meaning
81 from gmd_sampling_events gse, gem_lookups glk
82 where gse.sampling_event_id = l_event_key
83 and glk.lookup_type = 'GMD_QC_MONITOR_RULE_TYPE'
84 and gse.source = glk.lookup_code;
85
86
87 /* This cursor Will Pick up all details for a Resource Sample */
88 Cursor C1 is
89 SELECT gse.resources, cr.resource_desc, ri.instance_number
90 from cr_rsrc_mst cr,
91 gmp_resource_instances ri,
92 cr_rsrc_dtl cd,
93 gmd_sampling_events gse
94 WHERE gse.sampling_event_id = l_event_key
95 and cr.resources = gse.resources
96 and cd.resources = cr.resources
97 and cd.organization_id = gse.organization_id
98 and ri.resource_id = cd.resource_id
99 and ri.instance_id = gse.instance_id;
100
101 /* This cursor Will Pick up all sample details for a Stability Study Sample */
102 Cursor C2 (ss_id_in number) is
103 select a.ss_no, a.description, b.concatenated_segments, b.description, a.revision
104 from gmd_stability_studies a,
105 mtl_system_items_b_kfv b
106 where a.ss_id = ss_id_in
107 and b.inventory_item_id = a.inventory_item_id
108 and b.organization_id = a.organization_id;
109
110 Cursor C3 is
111 select source , c.ss_id
112 from gmd_sampling_events a,
113 gmd_ss_variants b,
114 gmd_stability_studies_b c,
115 gmd_ss_time_points d
116 where d.sampling_event_id = l_event_key
117 and d.variant_id = b.variant_id
118 and b.ss_id = c.ss_id
119 and a.sampling_event_id = d.sampling_event_id ;
120
121 /* This cursor Will Pick up all details for a Physical Location Sample */
122 cursor C4 is
123 select gse.subinventory, mil.concatenated_segments
124 from gmd_sampling_events gse, mtl_item_locations_kfv mil
125 WHERE sampling_event_id = l_event_key
126 and mil.inventory_location_id = gse.locator_id
127 and mil.organization_id = gse.organization_id;
128
129 Cursor C5 is
130 select source
131 from gmd_Sampling_Events
132 where sampling_event_id = l_event_key ;
133
134 cursor get_from_role is
135 select nvl( text, '')
136 from wf_Resources
137 where name = 'WF_ADMIN_ROLE' ;
138
139 l_from_role varchar2(240);
140
141
142 BEGIN
143
144
145 IF (l_debug = 'Y') THEN
146 gmd_debug.log_initialize('Samplerej');
147 END IF;
148
149 open get_from_role ;
150 fetch get_from_role into l_from_role ;
151 close get_from_role ;
152
153
154 IF P_FUNCMODE='RUN' THEN
155 /* Get application_id from FND_APPLICATION */
156 select application_id into l_application_id
157 from fnd_application where application_short_name='GMD';
158
159 /* Check which event has been raised */
160 wf_log_pkg.string(6, 'Dummy','Entered Sample Rejection Transaction with event_key '||l_event_key);
161
162 /* Get the common fields values*/
163 OPEN common_fields;
164 FETCH common_fields INTO l_orgn_code, l_sample_type, l_sample_type_desc;
165 CLOSE common_fields;
166
167 IF l_sample_type = 'I' THEN
168 OPEN common_fields1;
169 FETCH common_fields1 INTO l_sample_source;
170 CLOSE common_fields1;
171 ELSIF l_sample_type = 'M' THEN
172 OPEN common_fields2;
173 FETCH common_fields2 INTO l_sample_source;
174 CLOSE common_fields2;
175 END IF;
176
177 IF (l_debug = 'Y') THEN
178 gmd_debug.put_line('Organization Code ' || l_orgn_code);
179 gmd_debug.put_line('Sample Type ' || l_sample_type);
180 gmd_debug.put_line('Sample Source' || l_sample_source);
181 END IF;
182
183 /* check to see if Stability or Resource/Location Sample */
184 open C3;
185 fetch C3 into l_source, l_ss_id ;
186 close C3 ;
187
188 IF (l_debug = 'Y') THEN
189 gmd_debug.put_line('Sample source type ' || l_source);
190 gmd_debug.put_line('Stability Study ID ' || l_ss_id);
191 END IF;
192
193 if (l_source = 'T') then
194 /* This is a stabiliy study sample */
195 open C2 (l_ss_id);
196 fetch C2 into l_study_no,l_study_desc,l_item_no,l_item_desc,l_revision;
197 close C2;
198 else
199 /*This is either Location or Resource Sample */
200 open C5;
201 fetch C5 into l_source ;
202 close C5 ;
203 if (l_source = 'L') then
204 /* This is a location sample */
205 open C4;
206 fetch C4 into l_subinventory, l_locator;
207 close C4;
208 else
209 /* This is a resource sample */
210 OPEN C1;
211 Fetch C1 into l_resource, l_resource_desc, l_instance_num;
212 CLOSE C1;
213 end if;
214 end if;
215
216 IF (l_debug = 'Y') THEN
217 gmd_debug.put_line('Checking approvers ');
218 END IF;
219
220 /* Set Form Attribute to the sampling event */
221 l_form := 'GMDQSAMPLES_F:SAMPLING_EVENT_ID="'||l_event_key||'"';
222
223 /* Start the Workflow for the Given Combination */
224 ame_api.clearAllApprovals(applicationIdIn => l_application_id,
225 transactionIdIn => l_event_key,
226 transactionTypeIn => l_transaction_type);
227
228 wf_log_pkg.string(6, 'Dummy','Approvers Cleared');
229 ame_api.getNextApprover(applicationIdIn => l_application_id,
230 transactionIdIn => l_event_key,
231 transactionTypeIn => l_transaction_type,
232 nextApproverOut => Approver);
233
234 if(Approver.user_id is null and Approver.person_id is null) then
235 /* No Approval Required */
236 null ;
237 else /* No approvers */
238
239 if(Approver.person_id is null) then
240 select user_name into l_user from fnd_user where user_id=Approver.user_id;
241 else
242 select user_name into l_user from fnd_user where user_id=ame_util.PERSONIDTOUSERID(Approver.person_id);
243 end if;
244
245 IF (l_debug = 'Y') THEN
246 gmd_debug.put_line('Found approvers ');
247 END IF;
248
249 l_itemtype:='GMDQMREJ';
250 l_itemkey:=l_event_key||'-'||to_char(sysdate,'dd/mm/yy hh:mi:ss');
251 l_workflow_process:='GMDQMREJ_SUB_PROCESS';
252
253 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype,
254 itemkey => l_itemkey,
255 process => l_workflow_process );
256
257 /* Set the User Attribute */
258 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
259 aname => '#FROM_ROLE',
260 avalue => l_user );
261
262 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
263 aname => 'CURRENT_APPROVER',
264 avalue => l_user);
265
266 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
267 aname => 'APPS_FORM',
268 avalue =>l_form );
269
270 /* Set All other Attributes */
274
271 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => p_itemkey,
272 aname => 'EVENT_NAME',
273 avalue =>l_event_name );
275 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
276 aname => 'EVENT_KEY',
277 avalue =>l_event_key );
278
279 /* Set Attributes for Common Fields */
280 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
281 aname => 'ORG',
282 avalue => l_orgn_code);
283
284 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
285 aname => 'SMPLGRP_TYPE',
286 avalue => l_sample_type_desc);
287
288 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
289 aname => 'SMPLGRP_SOURCE',
290 avalue => l_sample_source);
291
292 /* Set Attributes for Stability Study Sample Group */
293 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
294 aname => 'STUDY',
295 avalue => l_study_no);
296
297 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
298 aname => 'STUDY_DESC',
299 avalue => l_study_desc);
300
301 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
302 aname => 'ITEM_NO',
303 avalue => l_item_no);
304
305 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
306 aname => 'ITEM_DESC',
307 avalue =>l_item_desc);
308
309 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
310 aname => 'REVISION',
311 avalue =>l_revision);
312
313 /* Set Attributes for Resource Sample Group */
314 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
315 aname => 'RESOURCE',
316 avalue => l_resource);
317
318 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
319 aname => 'RESOURCE_DESC',
320 avalue => l_resource_desc);
321
322 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
323 aname => 'RESOURCE_INST',
324 avalue => l_instance_num);
325
326 /* Set Attributes for Resource Sample Group */
327 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
328 aname => 'SUBINVENTORY',
329 avalue => l_subinventory);
330
331 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
332 aname => 'LOCATOR',
333 avalue => l_locator);
334
335 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
336 aname => 'AME_TRANS',
337 avalue =>l_transaction_type);
338
339 wf_log_pkg.string(6, 'Dummy','Setting Parent');
340
341 WF_ENGINE.SETITEMPARENT(itemtype =>l_itemtype,itemkey =>l_itemkey,
342 parent_itemtype => p_itemtype,
343 parent_itemkey=> p_itemkey,
344 parent_context=> NULL);
345
346 /* start the Workflow process */
347 wf_log_pkg.string(6, 'Dummy','Starting Process');
348
349 /* As this a pure FYI notification we will set the approver to approve status */
350 Approver.approval_status := ame_util.approvedStatus;
351 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
352 transactionIdIn => l_event_key,
353 approverIn => Approver,
354 transactionTypeIn => l_transaction_type,
355 forwardeeIn => ame_util.emptyApproverRecord);
356
357
358 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
359
360 IF (l_debug = 'Y') THEN
361 gmd_debug.put_line('created workflow process ');
362 END IF;
363
364
365 end if; /* No approver condition */
366
367 END IF; -- P_FUNCMODE='RUN'
368
369
370 p_resultout:='COMPLETE:';
371
372 EXCEPTION
373 WHEN OTHERS THEN
374 WF_CORE.CONTEXT ('GMD_QMREJ','VERIFY_EVENT',p_itemtype,p_itemkey,l_log );
375 raise;
376
377 END VERIFY_EVENT;
378
379
380
381 PROCEDURE CHECK_NEXT_APPROVER(
382 p_itemtype IN VARCHAR2,
383 p_itemkey IN VARCHAR2,
384 p_actid IN NUMBER,
385 p_funcmode IN VARCHAR2,
386 p_resultout OUT NOCOPY VARCHAR2)
387
388 IS
389 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
390 itemtype=>p_itemtype,
391 itemkey=>P_itemkey,
392 aname=>'EVENT_NAME');
393 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
394 itemtype=>p_itemtype,
395 itemkey=>P_itemkey,
396 aname=>'EVENT_KEY');
397
398 l_current_approver varchar2(240);
399
400 l_application_id number;
401 l_transaction_type varchar2(100):=WF_ENGINE.GETITEMATTRTEXT(
402 itemtype=>p_itemtype,
403 itemkey=>P_itemkey,
404 aname=>'AME_TRANS');
405 l_user varchar2(32);
406 Approver ame_util.approverRecord;
407 l_form varchar2(240);
408 BEGIN
409
410 /* Get Next Approver */
411 /* Get application_id from FND_APPLICATION */
412 select application_id into l_application_id
413 from fnd_application where application_short_name='GMD';
414
415 ame_api.getNextApprover(applicationIdIn => l_application_id,
416 transactionIdIn => l_event_key,
417 transactionTypeIn => l_transaction_type,
418 nextApproverOut => Approver);
419
420
421 if(Approver.user_id is null and Approver.person_id is null) then
422 /* No Approval Required */
423 P_resultout:='COMPLETE:N';
424 else
425 if(Approver.person_id is null) then
426 select user_name into l_user from fnd_user
427 where user_id=Approver.user_id;
428 else
429 select user_name into l_user from fnd_user
430 where user_id=ame_util.PERSONIDTOUSERID(Approver.person_id);
431 end if;
432
433 /* Set the User Attribute */
434
435 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
436 aname => 'CURRENT_APPROVER',
437 avalue => l_user);
438
439 P_resultout:='COMPLETE:Y';
440 Approver.approval_status := ame_util.approvedStatus;
441 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
442 transactionIdIn => l_event_key,
443 approverIn => Approver,
444 transactionTypeIn => l_transaction_type,
445 forwardeeIn => ame_util.emptyApproverRecord);
446 end if;
447 EXCEPTION
448 WHEN OTHERS THEN
449 WF_CORE.CONTEXT ('GMD_QMREJ','CHECK_NEXT_APPROVER',p_itemtype,p_itemkey,'Initial' );
450 raise;
451
452 END CHECK_NEXT_APPROVER;
453
454
455 END GMD_QMREJ;