[Home] [Help]
PACKAGE BODY: APPS.GMD_SS_LATETEST_WF_PKG
Source
1 PACKAGE BODY GMD_SS_LATETEST_WF_PKG AS
2 /* $Header: GMDQSLTB.pls 120.6 2006/12/05 16:08:07 rlnagara noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 PROCEDURE VERIFY_EVENT(
7 /* procedure to verify event and send out notifications*/
8 p_itemtype IN VARCHAR2,
9 p_itemkey IN VARCHAR2,
10 p_actid IN NUMBER,
11 p_funcmode IN VARCHAR2,
12 p_resultout OUT NOCOPY VARCHAR2)
13
14 IS
15
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_LATETEST';
29 l_user varchar2(32);
30 Approver ame_util.approverRecord;
31
32 l_organization varchar2(3); --INVCONV
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_item_no varchar2(240);
40 l_item_desc varchar2(240);
41 l_item_revision varchar2(3); --INVCONV
42 l_item_um varchar2(32);
43 l_study varchar2(240);
44 l_study_desc varchar2(240);
45 l_recipe_no varchar2(240);
46 l_recipe_vers varchar2(240);
47 l_status varchar2(240);
48 l_Sched_date 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_batch_no varchar2(240);
55 l_storage_subinventory varchar2(10); --INVCONV
56 l_storage_locator varchar2(240); --INVCONV
57 l_storage_spec varchar2(240);
58 l_storage_spec_vers varchar2(240);
59 l_resource varchar2(240);
60 l_formula varchar2(240);
61 l_formula_vers varchar2(240);
62 l_package varchar2(240);
63 l_sample_qty varchar2(240);
64 l_sample_uom varchar2(240);
65 l_variant_no varchar2(240);
66 l_study_date varchar2(240);
67 l_sample_event_id varchar2(240);
68 l_sample_temp varchar2(240);
69 l_owner number;
70 l_owner_used number := 0;
71 l_time_name varchar2(240);
72
73 --Modified for INVCONV
74 cursor C1 is
75 select distinct k.organization_code, b.meaning, e.ss_no, h.variant_no,f.description,
76 h.samples_per_time_point , h.storage_subinventory, j.concatenated_segments storage_locator,
77 h.resources, i.spec_name, i.spec_vers, c.scheduled_date,
78 d.concatenated_segments item_no, d.description item_desc1, e. revision, c.sampling_event_id,
79 e.owner , c.name
80 from gmd_ss_material_sources a,
81 gmd_Qc_status b,
82 gmd_ss_time_points c,
83 mtl_system_items_b_kfv d,
84 gmd_stability_studies_b e ,
85 gmd_stability_studies_tl f ,
86 gmd_ss_variants h,
87 gmd_specifications i,
88 mtl_item_locations_kfv j,
89 mtl_parameters k
90 where c.time_point_id = l_event_key
91 and c.variant_id = h.variant_id
92 and h.material_source_id = a.source_id
93 and a.ss_id = e.ss_id
94 and d.inventory_item_id = e.inventory_item_id
95 and d.organization_id = e.organization_id
96 and h.material_source_id = a.source_id
97 and c.spec_id = i.spec_id
98 and e.ss_id = f.ss_id
99 and b.entity_type = 'STABILITY'
100 and b.status_code = e.status
101 and h.storage_locator_id = j.inventory_location_id(+)
102 and h.storage_organization_id = j.organization_id(+)
103 and e.organization_id = k.organization_id ;
104
105 Cursor C2 (se_id NUMBER) is
106 select sample_no from gmd_samples
107 where sampling_event_id = se_id ;
108
109 cursor get_from_role is
110 select nvl( text, '')
111 from wf_Resources where name = 'WF_ADMIN_ROLE' --RLNAGARA B5654562 Changed from WF_ADMIN to WF_ADMIN_ROLE
112 and language = userenv('LANG');
113
114 l_from_role varchar2(240);
115
116 BEGIN
117
118 IF (l_debug = 'Y') THEN
119 gmd_debug.log_initialize('TimepointLate');
120 END IF;
121
122 IF (l_debug = 'Y') THEN
123 gmd_debug.put_line('Event Name ' || l_event_name);
124 gmd_debug.put_line('Event Key ' || l_event_key);
125 END IF;
126
127
128 open get_from_role ;
129 fetch get_from_role into l_from_role ;
130 close get_from_role ;
131
132
133 IF P_FUNCMODE='RUN' THEN
134
135 /* Get application_id from FND_APPLICATION */
136 select application_id into l_application_id
137 from fnd_application where application_short_name='GMD';
138
139 /* Check which event has been raised */
140 wf_log_pkg.string(6, 'Dummy','Entered Stability Study Timepoint testing late '||l_event_key);
141
142
143 OPEN C1;
144
145
146 Fetch C1 into l_organization, l_status, l_study,l_variant_no, l_study_desc,
147 l_sample_qty, l_storage_subinventory, l_storage_locator,
148 l_resource, l_storage_spec, l_storage_spec_vers,
149 l_study_date, l_item_no, l_item_desc ,l_item_revision, l_sample_event_id,
150 l_owner , l_time_name;
151
152 OPEN C2 (l_sample_event_id );
153 Loop
154 fetch C2 into l_sample_temp;
155 exit when c2%notfound;
156 l_sample_no := l_sample_no || ' ' || l_sample_temp;
157 end loop;
158 CLOSE C2;
159
160
161 /* Set Form Attribute to the sampling event */
162 -- l_form := 'GMDQSMPL_EDIT_F:SAMPLING_EVENT_ID="'||l_sample_event_id||'"';
163 l_form := 'GMDQSAMPLES_F:SAMPLING_EVENT_ID="'||l_sample_event_id||'"';
164
165 /* Start the Workflow for the Given Combination */
166 IF (l_debug = 'Y') THEN
167 gmd_debug.put_line('Getting AME approver');
168 END IF;
169 ame_api.clearAllApprovals(applicationIdIn => l_application_id,
170 transactionIdIn => l_event_key,
171 transactionTypeIn => l_transaction_type);
172
173 wf_log_pkg.string(6, 'Dummy','Approvers Cleared');
174 ame_api.getNextApprover(applicationIdIn => l_application_id,
175 transactionIdIn => l_event_key,
176 transactionTypeIn => l_transaction_type,
177 nextApproverOut => Approver);
178
179 if(Approver.user_id is null and Approver.person_id is null) then
180 /* No Approval Required so we will default to the owner*/
181 /* P_resultout:='COMPLETE:N'; */
182 select user_name into l_user from fnd_user
183 where user_id = l_owner;
184 l_owner_used := 1;
185 end if ;
186
187 if (l_user is null) then
188 if(Approver.person_id is null) then
189 select user_name into l_user from fnd_user
190 where user_id=Approver.user_id;
191 else
192 select user_name into l_user from fnd_user
193 where user_id=ame_util.PERSONIDTOUSERID(Approver.person_id);
194 end if;
195 end if;
196
197 IF (l_debug = 'Y') THEN
198 gmd_debug.put_line('AME approver ' || l_user);
199 END IF;
200
201 l_itemtype:='GMDQSTSL';
202 l_itemkey:=l_event_key||'-'||to_char(sysdate,'dd/mm/yy hh:mi:ss');
203
204 l_workflow_process:='GMDQSTSL_SUB_PROCESS';
205
206 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype,
207 itemkey => l_itemkey,
208 process => l_workflow_process );
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
226 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
227 aname => 'ORG',
228 avalue =>l_organization ); --INVCONV
229 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
230 aname => 'ITEM_NO',
231 avalue =>l_item_no );
232 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
233 aname => 'ITEM_DESC',
234 avalue =>l_item_desc );
235 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
236 aname => 'ITEM_REVISION',
237 avalue =>l_item_revision ); --INVCONV
238 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
239 aname => 'STUDY',
240 avalue =>l_study );
241 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
242 aname => 'STUDY_DESC',
243 avalue =>l_study_desc );
244 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
245 aname => 'STATUS',
246 avalue =>l_status );
247 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
248 aname => 'SAMPLE_QTY',
249 avalue =>l_sample_qty );
250 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
251 aname => 'SAMPLE_NO',
252 avalue =>l_sample_no );
253 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
254 aname => 'STUDY_DATE',
255 avalue =>l_study_date);
256 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
257 aname => 'RESOURCE',
258 avalue =>l_resource);
259 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
260 aname => 'SUBINVENTORY',
261 avalue =>l_storage_subinventory); --INVCONV
262 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
263 aname => 'LOCATOR',
264 avalue =>l_storage_locator); --INVCONV
265 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
266 aname => 'VARIANT_NO',
267 avalue =>l_variant_no);
268 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
269 aname => 'STORAGE_SPEC',
270 avalue =>l_storage_spec);
271 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
272 aname => 'STGE_SPEC_VER',
273 avalue =>l_storage_spec_vers);
274 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
275 aname => 'TIME_NAME',
276 avalue =>l_time_name);
277 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
278 aname => '#FROM_ROLE',
279 avalue =>l_from_role );
280
281
282 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
283 aname => 'AME_TRANS',
284 avalue =>l_transaction_type);
285
286 wf_log_pkg.string(6, 'Dummy','Setting Parent');
287
288
289 WF_ENGINE.SETITEMPARENT(itemtype =>l_itemtype,itemkey =>l_itemkey,
290 parent_itemtype => p_itemtype,
291 parent_itemkey=> p_itemkey,
292 parent_context=> NULL);
293
294 /* start the Workflow process */
295 wf_log_pkg.string(6, 'Dummy','Starting Process');
296
297
298 IF (l_debug = 'Y') THEN
299 gmd_debug.put_line('Finished Workflow atributes');
300 END IF;
301
302 /* As this a pure FYI notification we will set the approver to approve status */
303 Approver.approval_status := ame_util.approvedStatus;
304 if (l_owner_used = 0) then
305 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
306 transactionIdIn => l_event_key,
307 approverIn => Approver,
308 transactionTypeIn => l_transaction_type,
309 forwardeeIn => ame_util.emptyApproverRecord);
310 end if;
311
312 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
313
314 close C1;
315
316 END IF;
317
318 p_resultout:='COMPLETE:';
319
320 EXCEPTION
321 WHEN OTHERS THEN
322 WF_CORE.CONTEXT ('GMD_SS_LATETEST_WF_PKG','VERIFY_EVENT',p_itemtype,p_itemkey,l_log );
323 raise;
324
325 END VERIFY_EVENT;
326
327
328
329 PROCEDURE CHECK_NEXT_APPROVER(
330 p_itemtype IN VARCHAR2,
331 p_itemkey IN VARCHAR2,
332 p_actid IN NUMBER,
333 p_funcmode IN VARCHAR2,
334 p_resultout OUT NOCOPY VARCHAR2)
335
336 IS
337 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
338 itemtype=>p_itemtype,
339 itemkey=>P_itemkey,
340 aname=>'EVENT_NAME');
341 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
342 itemtype=>p_itemtype,
343 itemkey=>P_itemkey,
344 aname=>'EVENT_KEY');
345
346 l_current_approver varchar2(240);
347
348 l_application_id number;
349 l_transaction_type varchar2(100):=WF_ENGINE.GETITEMATTRTEXT(
350 itemtype=>p_itemtype,
351 itemkey=>P_itemkey,
352 aname=>'AME_TRANS');
353 l_user varchar2(32);
354 Approver ame_util.approverRecord;
355 l_form varchar2(240);
356 BEGIN
357
358 /* Get Next Approver */
359 /* Get application_id from FND_APPLICATION */
360 select application_id into l_application_id
361 from fnd_application where application_short_name='GMD';
362
363 ame_api.getNextApprover(applicationIdIn => l_application_id,
364 transactionIdIn => l_event_key,
365 transactionTypeIn => l_transaction_type,
366 nextApproverOut => Approver);
367
368
369 if(Approver.user_id is null and Approver.person_id is null) then
370 /* No Approval Required */
371 P_resultout:='COMPLETE:N';
372 else
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
378 where user_id=ame_util.PERSONIDTOUSERID(Approver.person_id);
379 end if;
380
381 /* Set the User Attribute */
382
383 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
384 aname => 'CURRENT_APPROVER',
385 avalue => l_user);
386
387 P_resultout:='COMPLETE:Y';
388 Approver.approval_status := ame_util.approvedStatus;
389 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
390 transactionIdIn => l_event_key,
391 approverIn => Approver,
392 transactionTypeIn => l_transaction_type,
393 forwardeeIn => ame_util.emptyApproverRecord);
394 end if;
395 EXCEPTION
396 WHEN OTHERS THEN
397 WF_CORE.CONTEXT ('GMD_SS_LATETEST_WF_PKG','CHECK_NEXT_APPROVER',p_itemtype,p_itemkey,'Initial' );
398 raise;
399
400 END CHECK_NEXT_APPROVER;
401
402
403 END GMD_SS_LATETEST_WF_PKG ;