[Home] [Help]
PACKAGE BODY: APPS.GMD_SS_TIMEPOINT_WF_PKG
Source
1 PACKAGE BODY GMD_SS_TIMEPOINT_WF_PKG AS
2 /* $Header: GMDQSTTB.pls 120.4 2006/12/05 16:07:45 rlnagara noship $ */
3
4
5
6 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
7
8
9 PROCEDURE VERIFY_EVENT(
10 /* procedure to verify event and send out notifications*/
11 p_itemtype IN VARCHAR2,
12 p_itemkey IN VARCHAR2,
13 p_actid IN NUMBER,
14 p_funcmode IN VARCHAR2,
15 p_resultout OUT NOCOPY VARCHAR2)
16
17 IS
18
19 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
20 itemtype=>p_itemtype,
21 itemkey=>P_itemkey,
22 aname=>'EVENT_NAME');
23 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
24 itemtype=>p_itemtype,
25 itemkey=>P_itemkey,
26 aname=>'EVENT_KEY');
27
28 l_current_approver varchar2(240);
29
30 l_application_id number;
31 l_transaction_type varchar2(100):='GMDQM_STABILITY_TEST';
32 l_user varchar2(32);
33 Approver ame_util.approverRecord;
34
35 l_form varchar2(240);
36 l_itemtype varchar2(240);
37 l_itemkey varchar2(240);
38 l_workflow_process varchar2(240);
39 l_log varchar2(4000);
40 I NUMBER;
41 l_item_no varchar2(240);
42 l_item_desc varchar2(240);
43 l_item_revision varchar2(3);
44 l_item_um varchar2(32);
45 l_study varchar2(240);
46 l_study_desc varchar2(240);
47 l_recipe_no varchar2(240);
48 l_recipe_vers varchar2(240);
49 l_status varchar2(240);
50 l_Sched_date varchar2(240);
51 l_plant varchar2(240);
52 l_sample_id varchar2(240);
53 l_sample_no varchar2(240);
54 l_sample_desc varchar2(240);
55 l_sample_type varchar2(240);
56 l_resource_desc varchar2(240);
57 l_resource_num number ;
58 l_batch_no varchar2(240);
59 l_storage_subinventory varchar2(10);
60 l_storage_locator 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_warehouse varchar2(240);
70 l_location varchar2(240);
71 l_variant_no varchar2(240);
72 l_study_date DATE; --RLNAGARA Bug3583790 Changed from VARCHAR2 to DATE
73 l_sample_event_id varchar2(240);
74 l_sample_temp varchar2(240);
75 l_owner number;
76 l_owner_used number := 0;
77 l_time_name varchar2(240);
78 l_organization varchar2(3);
79
80 --Cursor changed for Inventory convergence
81 --RLNAGARA Bug 3583790 select sample_qty instead of samples_per_time_point
82 --also h.storage_spec_id should be compared and not c.spec_id. Hence changed.
83
84 cursor C1 is
85 select distinct k.organization_code, b.meaning, e.ss_no, h.variant_no,f.description,
86 h.sample_qty, h.storage_subinventory, j.concatenated_segments storage_locator,
87 h.resources, i.spec_name, i.spec_vers, c.scheduled_date,
88 d.concatenated_segments item_no, d.description item_desc1, e.revision,
89 c.sampling_event_id, e.owner , c.name
90 from gmd_ss_material_sources a,
91 gmd_Qc_status b,
92 gmd_ss_time_points c,
93 mtl_system_items_b_kfv d,
94 gmd_stability_studies_b e ,
95 gmd_stability_studies_tl f ,
96 gmd_ss_variants h,
97 gmd_specifications i,
98 mtl_item_locations_kfv j,
99 mtl_parameters k
100 where c.time_point_id = l_event_key
101 and c.variant_id = h.variant_id
102 and h.material_source_id = a.source_id
103 and a.ss_id = e.ss_id
104 and d.inventory_item_id = e.inventory_item_id
105 and d.organization_id = e.organization_id
106 and h.material_source_id = a.source_id
107 and h.storage_spec_id = i.spec_id
108 and e.ss_id = f.ss_id
109 and b.entity_type = 'STABILITY'
110 and b.status_code = e.status
111 and h.storage_locator_id = j.inventory_location_id(+)
112 and h.storage_organization_id = j.organization_id(+)
113 and k.organization_id = e.organization_id
114 and f.language = userenv('LANG');
115
116 -- JD to C1 added organization match on mtl_item_locations_kfv and
117 -- outer join.
118 -- added language to gmd_stability_studies_tl
119 -- 10/20/2005
120
121
122 Cursor C2 (se_id NUMBER) is
123 select sample_no from gmd_samples
124 where sampling_event_id = se_id ;
125
126 cursor get_from_role is
127 select nvl( text, '')
128 from wf_Resources where name = 'WF_ADMIN_ROLE' --RLNAGARA B5654562 Changed from WF_ADMIN to WF_ADMIN_ROLE
129 AND language = userenv('LANG'); -- Added 10/20/2005
130
131 l_from_role varchar2(240);
132
133 BEGIN
134
135 IF (l_debug = 'Y') THEN
136 gmd_debug.log_initialize('Timepoint');
137 END IF;
138
139 IF (l_debug = 'Y') THEN
140 gmd_debug.put_line('Event Name ' || l_event_name);
141 gmd_debug.put_line('Event Key ' || l_event_key);
142 END IF;
143
144 open get_from_role ;
145 fetch get_from_role into l_from_role ;
146 close get_from_role ;
147
148
149 IF P_FUNCMODE='RUN' THEN
150
151 /* Get application_id from FND_APPLICATION */
152 select application_id into l_application_id
153 from fnd_application where application_short_name='GMD';
154
155 /* Check which event has been raised */
156 wf_log_pkg.string(6, 'Dummy','Entered Stability Study Batch Creation '||l_event_key);
157
158
159
160 OPEN C1;
161 Fetch C1 into l_organization, l_status, l_study,l_variant_no, l_study_desc,
162 l_sample_qty, l_storage_subinventory, l_storage_locator,
163 l_resource, l_storage_spec, l_storage_spec_vers,
164 l_study_date, l_item_no, l_item_desc , l_item_revision, l_sample_event_id,
165 l_owner, l_time_name;
166
167 OPEN C2 (l_sample_event_id );
168 Loop
169 fetch C2 into l_sample_temp;
170 exit when c2%notfound;
171 l_sample_no := l_sample_no || ' ' || l_sample_temp;
172 end loop;
173 CLOSE C2;
174
175
176 /* Set Form Attribute to the sampling event */
177 -- l_form := 'GMDQSMPL_EDIT_F:SAMPLING_EVENT_ID="'||l_sample_event_id||'"';
178 l_form := 'GMDQSAMPLES_F:SAMPLING_EVENT_ID="'||l_sample_event_id||'"';
179
180 /* Start the Workflow for the Given Combination */
181 IF (l_debug = 'Y') THEN
182 gmd_debug.put_line('Getting AME approver');
183 END IF;
184
185 ame_api.clearAllApprovals(applicationIdIn => l_application_id,
186 transactionIdIn => l_event_key,
187 transactionTypeIn => l_transaction_type);
188
189 wf_log_pkg.string(6, 'Dummy','Approvers Cleared');
190 ame_api.getNextApprover(applicationIdIn => l_application_id,
191 transactionIdIn => l_event_key,
192 transactionTypeIn => l_transaction_type,
193 nextApproverOut => Approver);
194
195 if(Approver.user_id is null and Approver.person_id is null) then
196 /* No Approval Required so we will default to the owner*/
197 /* P_resultout:='COMPLETE:N'; */
198 select user_name into l_user from fnd_user
199 where user_id = l_owner;
200 l_owner_used := 1;
201 end if ;
202
203 if (l_user is null) then
204 if(Approver.person_id is null) then
205 select user_name into l_user from fnd_user
206 where user_id=Approver.user_id;
207 else
208 select user_name into l_user from fnd_user
209 where user_id=ame_util.PERSONIDTOUSERID(Approver.person_id);
210 end if;
211 end if;
212
213
214 IF (l_debug = 'Y') THEN
215 gmd_debug.put_line('AME approver ' || l_user);
216 END IF;
217
218 l_itemtype:='GMDQSTST';
219 l_itemkey:=l_event_key||'-'||to_char(sysdate,'dd/mm/yy hh:mi:ss');
220
221 l_workflow_process:='GMDQSTST_SUB_PROCESS';
222
223 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype,
224 itemkey => l_itemkey,
225 process => l_workflow_process );
226
227 /* Set the User Attribute */
228
229 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
230 aname => 'CURRENT_APPROVER',
231 avalue => l_user);
232 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
233 aname => 'APPS_FORM',
234 avalue =>l_form );
235 /* Set All other Attributes */
236 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => p_itemkey,
237 aname => 'EVENT_NAME',
238 avalue =>l_event_name );
239 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
240 aname => 'EVENT_KEY',
241 avalue =>l_event_key );
242 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
243 aname => 'ORG',
244 avalue =>l_organization ); --INVCONV
245 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
246 aname => 'ITEM_NO',
247 avalue =>l_item_no );
248 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
249 aname => 'ITEM_DESC',
250 avalue =>l_item_desc );
251 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
252 aname => 'ITEM_REVISION',
253 avalue =>l_item_revision ); --INVCONV
254 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
255 aname => 'STUDY',
256 avalue =>l_study );
257 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
258 aname => 'STUDY_DESC',
259 avalue =>l_study_desc );
260 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
261 aname => 'STATUS',
262 avalue =>l_status );
263 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
264 aname => 'SAMPLE_QTY',
265 avalue =>l_sample_qty );
266 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
267 aname => 'SAMPLE_NO',
268 avalue =>l_sample_no );
269 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
270 aname => 'STUDY_DATE',
271 avalue =>l_study_date);
272 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
273 aname => 'RESOURCE',
274 avalue =>l_resource);
275 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
276 aname => 'SUBINVENTORY',
277 avalue =>l_storage_subinventory); --INVCONV
278 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
279 aname => 'LOCATOR',
280 avalue =>l_storage_locator); --INVCONV
281 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
282 aname => 'VARIANT_NO',
283 avalue =>l_variant_no);
284 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
285 aname => 'STORAGE_SPEC',
286 avalue =>l_storage_spec);
287 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
288 aname => 'STGE_SPEC_VER',
289 avalue =>l_storage_spec_vers);
290 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
291 aname => 'TIME_NAME',
292 avalue =>l_time_name);
293 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
294 aname => '#FROM_ROLE',
295 avalue =>l_from_role );
296
297 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
298 aname => 'AME_TRANS',
299 avalue =>l_transaction_type);
300
301 wf_log_pkg.string(6, 'Dummy','Setting Parent');
302
303
304 WF_ENGINE.SETITEMPARENT(itemtype =>l_itemtype,itemkey =>l_itemkey,
305 parent_itemtype => p_itemtype,
306 parent_itemkey=> p_itemkey,
307 parent_context=> NULL);
308
309 /* start the Workflow process */
310 wf_log_pkg.string(6, 'Dummy','Starting Process');
311
312 IF (l_debug = 'Y') THEN
313 gmd_debug.put_line('Finished Workflow atributes');
314 END IF;
315
316
317 /* As this a pure FYI notification we will set the approver to approve status */
318 Approver.approval_status := ame_util.approvedStatus;
319 if (l_owner_used = 0) then
320 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
321 transactionIdIn => l_event_key,
322 approverIn => Approver,
323 transactionTypeIn => l_transaction_type,
324 forwardeeIn => ame_util.emptyApproverRecord);
325 end if;
326
327 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
328
329 close C1;
330
331 END IF;
332
333 p_resultout:='COMPLETE:';
334
335 EXCEPTION
336 WHEN OTHERS THEN
337 WF_CORE.CONTEXT ('GMD_SS_TIMEPOINT_WF_PKG','VERIFY_EVENT',p_itemtype,p_itemkey,l_log );
338 raise;
339
340 END VERIFY_EVENT;
341
342
343
344 PROCEDURE CHECK_NEXT_APPROVER(
345 p_itemtype IN VARCHAR2,
346 p_itemkey IN VARCHAR2,
347 p_actid IN NUMBER,
348 p_funcmode IN VARCHAR2,
349 p_resultout OUT NOCOPY VARCHAR2)
350
351 IS
352 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
353 itemtype=>p_itemtype,
354 itemkey=>P_itemkey,
355 aname=>'EVENT_NAME');
356 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
357 itemtype=>p_itemtype,
358 itemkey=>P_itemkey,
359 aname=>'EVENT_KEY');
360
361 l_current_approver varchar2(240);
362
363 l_application_id number;
364 l_transaction_type varchar2(100):=WF_ENGINE.GETITEMATTRTEXT(
365 itemtype=>p_itemtype,
366 itemkey=>P_itemkey,
367 aname=>'AME_TRANS');
368 l_user varchar2(32);
369 Approver ame_util.approverRecord;
370 l_form varchar2(240);
371 BEGIN
372
373 /* Get Next Approver */
374 /* Get application_id from FND_APPLICATION */
375 select application_id into l_application_id
376 from fnd_application where application_short_name='GMD';
377
378 /*Update the wf_sent column to show that a notification has been sent */
379 update gmd_ss_time_points
380 set wf_sent = 'Y'
381 where time_point_id = l_event_key;
382
383 ame_api.getNextApprover(applicationIdIn => l_application_id,
384 transactionIdIn => l_event_key,
385 transactionTypeIn => l_transaction_type,
386 nextApproverOut => Approver);
387
388
389 if(Approver.user_id is null and Approver.person_id is null) then
390 /* No Approval Required */
391 P_resultout:='COMPLETE:N';
392 else
393 if(Approver.person_id is null) then
394 select user_name into l_user from fnd_user
395 where user_id=Approver.user_id;
396 else
397 select user_name into l_user from fnd_user
398 where user_id=ame_util.PERSONIDTOUSERID(Approver.person_id);
399 end if;
400
401 /* Set the User Attribute */
402
403 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
404 aname => 'CURRENT_APPROVER',
405 avalue => l_user);
406
407 P_resultout:='COMPLETE:Y';
408 Approver.approval_status := ame_util.approvedStatus;
409 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
410 transactionIdIn => l_event_key,
411 approverIn => Approver,
412 transactionTypeIn => l_transaction_type,
413 forwardeeIn => ame_util.emptyApproverRecord);
414 end if;
415 EXCEPTION
416 WHEN OTHERS THEN
417 WF_CORE.CONTEXT ('GMD_SS_TIMEPOINT_WF_PKG','CHECK_NEXT_APPROVER',p_itemtype,p_itemkey,'Initial' );
418 raise;
419
420 END CHECK_NEXT_APPROVER;
421
422
423 /* Procedure to cancel a workflow for a timepoint and reset the
424 wf_sent column */
425 PROCEDURE CANCEL_TIMEPOINT_WF(
426 p_timepoint IN NUMBER,
427 p_result OUT NOCOPY VARCHAR2)
428 IS
429
430 l_itemtype VARCHAR2(10);
431 l_itemkey NUMBER ;
432
433 BEGIN
434
435 l_itemtype :='GMDQSTST';
436 l_itemkey := p_timepoint ;
437
438 /* Cancel workflow process */
439 wf_engine.abortprocess (ITEMTYPE => l_itemtype,
440 ITEMKEY => l_itemkey);
441
442
443 /* update the wf_sent back to N */
444 update gmd_ss_time_points
445 set wf_sent = 'N'
446 where time_point_id = p_timepoint;
447
448 /* Return Success */
449 p_result := 'S';
450
451 EXCEPTION
452 WHEN OTHERS THEN
453 WF_CORE.CONTEXT ('GMD_SS_TIMEPOINT_WF_PKG','CANCEL_TIMEPOINT_WF',l_itemtype,l_itemkey,'Problem Canceling timepoint' );
454 p_result := 'E';
455
456 raise;
457
458 END CANCEL_TIMEPOINT_WF ;
459
460
461 END GMD_SS_TIMEPOINT_WF_PKG ;
462