[Home] [Help]
PACKAGE BODY: APPS.GMD_SS_BATCH_WF_PKG
Source
1 PACKAGE BODY GMD_SS_BATCH_WF_PKG AS
2 /* $Header: GMDQSSBB.pls 120.4 2006/12/05 16:07:30 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 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
16 itemtype=>p_itemtype,
17 itemkey=>P_itemkey,
18 aname=>'EVENT_NAME');
19 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
20 itemtype=>p_itemtype,
21 itemkey=>P_itemkey,
22 aname=>'EVENT_KEY');
23
24 l_current_approver varchar2(240);
25
26 l_application_id number;
27 l_transaction_type varchar2(100):='GMDQM_STABILITY_BATCH';
28 l_user varchar2(32);
29 Approver ame_util.approverRecord;
30
31
32 l_form varchar2(240);
33 l_itemtype varchar2(240);
34 l_itemkey varchar2(240);
35 l_workflow_process varchar2(240);
36 l_log varchar2(4000);
37 I NUMBER;
38 l_orgn_code varchar2(3); --Added as part of Convergence changes
39 l_item_no varchar2(240);
40 l_item_desc varchar2(240);
41 l_revision varchar2(3); --Added as part of Convergence changes
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_sample_qty varchar2(240);
48 l_sample_uom varchar2(240);
49 l_status varchar2(240);
50 l_Sched_date varchar2(240);
51 l_plant varchar2(240);
52 l_owner number;
53 l_owner_used number := 0;
54
55
56 --Cursor C1 is modified for Convergence
57 Cursor C1 is
58 select distinct b.meaning, e.ss_no, f.description, h.organization_code,
59 e.scheduled_start_date, g.concatenated_segments item_no,
60 g.description item_desc1, e.revision, i.organization_code plant_code,
61 a.sample_qty, a.sample_quantity_uom,
62 c.recipe_no, c.recipe_version, e.owner,e.ss_id -- Bug#3374906
63 from gmd_ss_material_sources a,
64 gmd_Qc_status b,
65 gmd_recipes c,
66 gmd_stability_studies_b e ,
67 gmd_stability_studies_tl f ,
68 mtl_system_items_kfv g,
69 mtl_parameters h,
70 mtl_parameters i
71 where a.source_id = l_event_key
72 and a.ss_id = e.ss_id
73 and e.ss_id = f.ss_id
74 and c.recipe_id(+) = a.recipe_id
75 and e.inventory_item_id = g.inventory_item_id
76 and e.organization_id = g.organization_id
77 and b.entity_type = 'STABILITY'
78 and b.status_code = e.status
79 and h.organization_id = e.organization_id
80 and i.organization_id = a.source_organization_id
81 and f.language = userenv('LANG') ;
82
83 cursor get_from_role is
84 select nvl( text, '')
85 from wf_Resources
86 where name = 'WF_ADMIN_ROLE' --RLNAGARA B5654562 Changed from WF_ADMIN to WF_ADMIN_ROLE
87 and language = userenv('LANG'); --Bug# 4594306. Added this condition.
88
89 l_from_role varchar2(240);
90 l_study_id NUMBER := 0; --Bug#3374906
91
92 BEGIN
93
94 IF (l_debug = 'Y') THEN
95 gmd_debug.log_initialize('StabStudyBatch');
96 END IF;
97
98 IF (l_debug = 'Y') THEN
99 gmd_debug.put_line('Event Name ' || l_event_name);
100 gmd_debug.put_line('Event Key ' || l_event_key);
101 END IF;
102
103 open get_from_role ;
104 fetch get_from_role into l_from_role ;
105 close get_from_role ;
106
107
108 IF P_FUNCMODE='RUN' THEN
109 /* Get application_id from FND_APPLICATION */
110 select application_id into l_application_id
111 from fnd_application where application_short_name='GMD';
112
113 /* Check which event has been raised */
114 wf_log_pkg.string(6, 'Dummy','Entered Stability Study Batch Creation '||l_event_key);
115
116
117 OPEN C1;
118 Fetch C1 into l_status, l_study, l_study_desc, l_orgn_code, l_sched_date,
119 l_item_no, l_item_desc , l_revision, l_plant, l_sample_qty, l_sample_uom,
120 l_recipe_no, l_recipe_vers, l_owner,
121 l_study_id; --Bug#3374906
122
123 /* Set Form Attribute to the sampling event */
124 --BUG#3374906 A.Sriram Modified the parameter name from SOURCE_ID to SS_ID
125 --and l_event_key to l_study_id in the following statement.
126 l_form := 'GMDQSSVT_F:SS_ID="'||l_study_id||'"';
127
128
129 /* Start the Workflow for the Given Combination */
130 ame_api.clearAllApprovals(applicationIdIn => l_application_id,
131 transactionIdIn => l_event_key,
132 transactionTypeIn => l_transaction_type);
133
134 wf_log_pkg.string(6, 'Dummy','Approvers Cleared');
135 ame_api.getNextApprover(applicationIdIn => l_application_id,
136 transactionIdIn => l_event_key,
137 transactionTypeIn => l_transaction_type,
138 nextApproverOut => Approver);
139
140 if(Approver.user_id is null and Approver.person_id is null) then
141 /* No Approval Required */
142 /* P_resultout:='COMPLETE:N';
143 return; */
144 select user_name into l_user from fnd_user
145 where user_id = l_owner;
146 l_owner_used := 1;
147 end if;
148
149
150 if (l_user is null) then
151 if(Approver.person_id is null) then
152 select user_name into l_user from fnd_user
153 where user_id=Approver.user_id;
154 else
155 select user_name into l_user from fnd_user
156 where user_id=ame_util.PERSONIDTOUSERID(Approver.person_id);
157 end if;
158 end if ;
159
160 IF (l_debug = 'Y') THEN
161 gmd_debug.put_line('Approver ' || l_user);
162 END IF;
163
164 l_itemtype:='GMDQSBAT';
165 l_itemkey:=l_event_key||'-'||to_char(sysdate,'dd/mm/yy hh:mi:ss');
166
167 l_workflow_process:='GMDQSBAT_SUB_PROCESS';
168
169 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype,
170 itemkey => l_itemkey,
171 process => l_workflow_process );
172
173
174 IF (l_debug = 'Y') THEN
175 gmd_debug.put_line('Going to set workflow attributes ');
176 END IF;
177
178 /* Set the User Attribute */
179 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey => l_itemkey,
180 aname => 'CURRENT_APPROVER',
181 avalue => l_user);
182 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
183 aname => 'APPS_FORM',
184 avalue =>l_form );
185 /* Set All other Attributes */
186 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => p_itemkey,
187 aname => 'EVENT_NAME',
188 avalue =>l_event_name );
189 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
190 aname => 'EVENT_KEY',
191 avalue =>l_event_key );
192 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
193 aname => 'ITEM_NO',
194 avalue =>l_item_no );
195 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
196 aname => 'ITEM_DESC',
197 avalue =>l_item_desc );
198 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
199 aname => 'ITEM_REVISION',
200 avalue =>l_revision );
201 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
202 aname => 'STUDY',
203 avalue =>l_study );
204 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
205 aname => 'STUDY_DESC',
206 avalue =>l_study_desc );
207 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
208 aname => 'ORG',
209 avalue =>l_orgn_code );
210 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
211 aname => 'STATUS',
212 avalue =>l_status );
213 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
214 aname => 'PLANT',
215 avalue =>l_plant );
216 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
217 aname => 'RECIPE',
218 avalue =>l_recipe_no);
219 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
220 aname => 'RECIPE_VERSION',
221 avalue =>l_recipe_vers );
222 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
223 aname => 'SAMPLE_QTY',
224 avalue =>l_sample_qty );
225 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
226 aname => 'SAMPLE_UOM',
227 avalue =>l_sample_uom );
228 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
229 aname => 'STUDY_DATE',
230 avalue =>l_sched_date);
231 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
232 aname => '#FROM_ROLE',
233 avalue =>l_from_role );
234
235 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
236 aname => 'AME_TRANS',
237 avalue =>l_transaction_type);
238
239 wf_log_pkg.string(6, 'Dummy','Setting Parent');
240
241
242 WF_ENGINE.SETITEMPARENT(itemtype =>l_itemtype,itemkey =>l_itemkey,
243 parent_itemtype => p_itemtype,
244 parent_itemkey=> p_itemkey,
245 parent_context=> NULL);
246
247 /* start the Workflow process */
248 wf_log_pkg.string(6, 'Dummy','Starting Process');
249
250 IF (l_debug = 'Y') THEN
251 gmd_debug.put_line('Finished setting workflow attributes ');
252 END IF;
253
254 /* As this a pure FYI notification we will set the approver to approve status */
255 Approver.approval_status := ame_util.approvedStatus;
256
257 if (l_owner_used = 0) then
258 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
259 transactionIdIn => l_event_key,
260 approverIn => Approver,
261 transactionTypeIn => l_transaction_type,
262 forwardeeIn => ame_util.emptyApproverRecord);
263 end if ;
264
265 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
266
267 close C1;
268
269
270 END IF;
271
272
273 p_resultout:='COMPLETE:';
274
275 EXCEPTION
276 WHEN OTHERS THEN
277 WF_CORE.CONTEXT ('GMD_SS_BATCH_WF_PKG','VERIFY_EVENT',p_itemtype,p_itemkey,l_log );
278 raise;
279
280 END VERIFY_EVENT;
281
282
283
284 PROCEDURE CHECK_NEXT_APPROVER(
285 p_itemtype IN VARCHAR2,
286 p_itemkey IN VARCHAR2,
287 p_actid IN NUMBER,
288 p_funcmode IN VARCHAR2,
289 p_resultout OUT NOCOPY VARCHAR2)
290
291 IS
292 l_event_name varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
293 itemtype=>p_itemtype,
294 itemkey=>P_itemkey,
295 aname=>'EVENT_NAME');
296 l_event_key varchar2(240):=WF_ENGINE.GETITEMATTRTEXT(
297 itemtype=>p_itemtype,
298 itemkey=>P_itemkey,
299 aname=>'EVENT_KEY');
300
301 l_current_approver varchar2(240);
302
303 l_application_id number;
304 l_transaction_type varchar2(100):=WF_ENGINE.GETITEMATTRTEXT(
305 itemtype=>p_itemtype,
306 itemkey=>P_itemkey,
307 aname=>'AME_TRANS');
308 l_user varchar2(32);
309 Approver ame_util.approverRecord;
310 l_form varchar2(240);
311 BEGIN
312
313 /* Get Next Approver */
314 /* Get application_id from FND_APPLICATION */
315 select application_id into l_application_id
316 from fnd_application where application_short_name='GMD';
317
318 ame_api.getNextApprover(applicationIdIn => l_application_id,
319 transactionIdIn => l_event_key,
320 transactionTypeIn => l_transaction_type,
321 nextApproverOut => Approver);
322
323
324 if(Approver.user_id is null and Approver.person_id is null) then
325 /* No Approval Required */
326 P_resultout:='COMPLETE:N';
327 else
328 if(Approver.person_id is null) then
329 select user_name into l_user from fnd_user
330 where user_id=Approver.user_id;
331 else
332 select user_name into l_user from fnd_user
333 where user_id=ame_util.PERSONIDTOUSERID(Approver.person_id);
334 end if;
335
336 /* Set the User Attribute */
337
338 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
339 aname => 'CURRENT_APPROVER',
340 avalue => l_user);
341
342 P_resultout:='COMPLETE:Y';
343 Approver.approval_status := ame_util.approvedStatus;
344 ame_api.updateApprovalStatus(applicationIdIn => l_application_id,
345 transactionIdIn => l_event_key,
346 approverIn => Approver,
347 transactionTypeIn => l_transaction_type,
348 forwardeeIn => ame_util.emptyApproverRecord);
349 end if;
350 EXCEPTION
351 WHEN OTHERS THEN
352 WF_CORE.CONTEXT ('GMD_SS_BATCH_WF_PKG','CHECK_NEXT_APPROVER',p_itemtype,p_itemkey,'Initial' );
353 raise;
354
355 END CHECK_NEXT_APPROVER;
356
357
358 END GMD_SS_BATCH_WF_PKG ;