[Home] [Help]
PACKAGE BODY: APPS.GMD_SS_APPROVAL_WF_PKG
Source
1 PACKAGE BODY GMD_SS_APPROVAL_WF_PKG AS
2 /* $Header: GMDQMSAB.pls 120.3.12000000.3 2007/02/07 12:09:49 rlnagara ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 APPLICATION_ERROR EXCEPTION;
7 -- Following function accepts FND userId and returns
8 -- User name
9 FUNCTION GET_FND_USER_NAME( userId Integer) RETURN VARCHAR2 IS
10 CURSOR GET_USER_NAME IS
11 SELECT USER_NAME
12 FROM FND_USER
13 WHERE USER_ID = userId;
14 l_userName FND_USER.USER_NAME%TYPE;
15 BEGIN
16 OPEN GET_USER_NAME;
17 FETCH GET_USER_NAME INTO l_userName;
18 CLOSE GET_USER_NAME;
19 RETURN l_userName;
20 END GET_FND_USER_NAME;
21
22 /********************************************************************************
23 *** This procedure is associated with GMDQMSAP_ISAPROVAL_REQUIRED workflow. **
24 *** This code will execute when Stability Status Business Event is raised. **
25 *** This verifies whether approval required for this transaction or not **
26 *** If approval is required then udated spec status to pending as defined **
27 *** GMD_QC_STATUS_NEXT and populates workflow attributes **
28 ********************************************************************************/
29
30 PROCEDURE IS_APPROVAL_REQ (
31 p_itemtype IN VARCHAR2,
32 p_itemkey IN VARCHAR2,
33 p_actid IN NUMBER,
34 p_funcmode IN VARCHAR2,
35 p_resultout OUT NOCOPY VARCHAR2) IS
36
37
38 applicationId number :=552;
39 transactionType varchar2(50) := 'GMDQM_STABILITY_CSTS';
40 nextApprover ame_util.approverRecord;
41
42 l_userID integer;
43 l_userName FND_USER.USER_NAME%TYPE;
44 l_Requester FND_USER.USER_NAME%TYPE;
45 l_Owner FND_USER.USER_NAME%TYPE;
46 l_storage_name varchar2(200);
47
48 lSSId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SS_ID');
49 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
50 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
51 l_wf_timeout NUMBER := TO_NUMBER(FND_PROFILE.VALUE ('GMD_WF_TIMEOUT'));
52 l_notify_time_unit varchar2(100);
53 l_grace_time_unit varchar2(100);
54
55 lStartStatus_DESC VARCHAR2(240);
56 lTargetStatus_DESC VARCHAR2(240);
57 api_ret_status VARCHAR2(1);
58 api_se_ret_status VARCHAR2(1);
59 api_err_mesg VARCHAR2(240);
60 l_message VARCHAR2(500);
61 l_owner_name varchar2(100);
62
63 cursor get_storage_name (storage_id_in number) is
64 select storage_plan_name
65 from gmd_storage_plans_b
66 where storage_plan_id = storage_id_in;
67
68 --this cursor definition changed as part of convergence
69 cursor get_disp_Attr IS
70 select a.ss_id, a.ss_no, b.meaning, a.description, c.concatenated_segments item_no, c.description item_desc1, a.revision,
71 e.organization_code ORGANIZATION_CODE, a.owner, a.NOTIFICATION_LEAD_TIME , a.NOTIFICATION_LEAD_TIME_UNIT,
72 a.TESTING_GRACE_PERIOD , a.TESTING_GRACE_PERIOD_UNIT , a.MATERIAL_SOURCES_CNT,
73 a.STORAGE_CONDITIONS_CNT , a.PACKAGES_CNT , a.BASE_SPEC_ID , d.SPEC_NAME, d.SPEC_VERS,
74 f.organization_code LAB_ORGANIZATION_CODE , a.storage_plan_id ,
75 a.SCHEDULED_START_DATE , a.SCHEDULED_END_DATE , a.REVISED_START_DATE ,
76 a.REVISED_END_DATE , a.ACTUAL_START_DATE , a.ACTUAL_END_DATE ,
77 a.RECOMMENDED_SHELF_LIFE , a.RECOMMENDED_SHELF_LIFE_UNIT , a.DELETE_MARK , a.LAST_UPDATED_BY
78 from gmd_stability_studies a,
79 gmd_Qc_status b,
80 mtl_system_items_kfv c,
81 gmd_specifications d,
82 mtl_parameters e,
83 mtl_parameters f
84 where a.inventory_item_id = c.inventory_item_id
85 and a.organization_id = c.organization_id
86 and b.entity_type = 'STABILITY'
87 and b.status_code = a.status
88 and a.BASE_SPEC_ID = d.spec_id
89 and a.ss_id = lSSId
90 and a.organization_id = e.organization_id
91 and a.lab_organization_id = f.organization_id;
92
93
94 disp_attr_rec get_disp_Attr%ROWTYPE;
95
96 cursor get_owner_name (owner_id_in number) is
97 select user_name from fnd_user
98 where user_id = owner_id_in ;
99
100
101 CURSOR get_ss_time_unit (p_time varchar2) IS
102 SELECT meaning
103 FROM gem_lookups
104 WHERE lookup_type = 'GMD_QC_FREQUENCY_PERIOD'
105 and lookup_code = p_time ;
106
107 cursor get_from_role is
108 select nvl( text, '')
109 from wf_Resources where name = 'WF_ADMIN_ROLE'
110 and language = userenv('LANG') ;
111
112 l_from_role varchar2(2000);
113
114
115 begin
116
117
118 IF (l_debug = 'Y') THEN
119 gmd_debug.log_initialize('StabStudyStatus');
120 gmd_debug.put_line('SS ID ' || lSSId );
121 END IF;
122
123 open get_from_role ;
124 fetch get_from_role into l_from_role ;
125 close get_from_role ;
126
127
128 IF p_funcmode = 'RUN' THEN
129 --
130 -- clear All Approvals from AME
131 -- following API removes previous instance of approval group from AME tables
132 --
133
134 IF (l_debug = 'Y') THEN
135 gmd_debug.put_line('Getting approvers ');
136 END IF;
137
138
139 ame_api.clearAllApprovals(applicationIdIn => applicationId,
140 transactionIdIn => lSSId ,
141 transactionTypeIn => transactionType);
142
143 --
144 -- Get the next approver who need to approve the trasaction
145 --
146 ame_api.getNextApprover(applicationIdIn => applicationId,
147 transactionIdIn => lSSId,
148 transactionTypeIn => transactionType,
149 nextApproverOut => nextApprover);
150
151
152 -- Get attributes Required for display
153 open get_disp_Attr;
154 FETCH get_disp_Attr INTO disp_attr_rec;
155 IF get_disp_Attr%NOTFOUND THEN
156 WF_CORE.CONTEXT ('GMD_SS_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,'NO VLAID SS ROW');
157 raise APPLICATION_ERROR;
158 END IF;
159 close get_disp_Attr;
160
161
162 IF nextApprover.user_id IS NULL and nextApprover.person_id IS NULL
163 THEN
164 --
165 -- Means either no AME rule is matching for this transaction ID or Approver list is empty.
166 --
167 l_userID := disp_attr_rec.owner;
168
169
170 elsif nextApprover.person_id IS NOT NULL THEN
171 --
172 -- if we got HR Person then we have to find corresponding FND USER
173 -- assumption here is all HR user configured in AME will have
174 -- corresponding FND USER
175 --
176 l_userID := ame_util.personIdToUserId(nextApprover.person_id);
177 ELSE
178 l_userID := nextApprover.user_id;
179 END IF;
180
181 IF (l_debug = 'Y') THEN
182 gmd_debug.put_line('Approver ID ' || l_userID);
183 END IF;
184
185
186 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'USER_ID',l_userID);
187 l_userName := GET_FND_USER_NAME(l_userId);
188
189 --
190 -- Update status to pending
191 --
192
193 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_STABILITY_STUDIES_B'
194 , p_id => lSSId
195 , p_source_status => lStartStatus
196 , p_target_status => lTargetStatus
197 , p_mode => 'P'
198 , p_entity_type => 'STABILITY' --RLNAGARA B5727585
199 , x_return_status => api_ret_status
200 , x_message => api_err_mesg );
201
202
203 IF api_ret_status = 'S' THEN
204
205 -- Get attributes Required for display
206 open get_disp_Attr;
207 FETCH get_disp_Attr INTO disp_attr_rec;
208 IF get_disp_Attr%NOTFOUND THEN
209 WF_CORE.CONTEXT ('GMD_SS_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,'NO VLAID SS ROW');
210 raise APPLICATION_ERROR;
211 END IF;
212
213 open get_storage_name(disp_attr_rec.storage_plan_id);
214 fetch get_storage_name into l_storage_name;
215 close get_storage_name ;
216
217 open get_ss_time_unit (disp_attr_rec.NOTIFICATION_LEAD_TIME_UNIT);
218 fetch get_ss_time_unit into l_notify_time_unit;
219 close get_ss_time_unit ;
220
221 open get_ss_time_unit (disp_attr_rec.TESTING_GRACE_PERIOD_UNIT);
222 fetch get_ss_time_unit into l_grace_time_unit;
223 close get_ss_time_unit ;
224
225 IF (l_debug = 'Y') THEN
226 gmd_debug.put_line('Setting workflow attributes');
227 END IF;
228
229 l_requester := GET_FND_USER_NAME(disp_attr_rec.LAST_UPDATED_BY);
230 l_owner := GET_FND_USER_NAME(disp_attr_rec.OWNER);
231 lStartStatus_DESC := GMD_SS_APPROVAL_WF_PKG.GET_STATUS_MEANING(lStartStatus,'STABILITY');
232 lTargetStatus_DESC:= GMD_SS_APPROVAL_WF_PKG.GET_STATUS_MEANING(lTargetStatus,'STABILITY');
233
234 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'OWNER_ORGN_CODE',disp_attr_rec.ORGANIZATION_CODE);
235 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SPEC_NAME',disp_attr_rec.SPEC_NAME);
236 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'BASE_SPEC',disp_attr_rec.SPEC_NAME);
237 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SPEC_VERS',disp_attr_rec.SPEC_VERS);
238 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'LAB_ORGN',disp_attr_rec.LAB_ORGANIZATION_CODE);
239 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'REQUESTER',l_requester);
240 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'ITEM_NO',disp_attr_rec.ITEM_NO);
241 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'ITEM_DESC',disp_attr_rec.ITEM_DESC1);
242 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'REVISION',disp_attr_rec.REVISION);
243 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'START_STATUS_DESC',lStartStatus_DESC);
244 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS_DESC',lTargetStatus_DESC);
245 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'APPROVER',l_userName);
246 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'STORAGE_PLAN',l_storage_name);
247 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SS_NAME',disp_attr_rec.SS_NO);
248 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SS_DESC',disp_attr_rec.description);
249 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'NOTIFY_TIME',
250 disp_attr_rec.NOTIFICATION_LEAD_TIME || ' ' || l_notify_time_unit);
251 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'GRACE_TIME',
252 disp_attr_rec.TESTING_GRACE_PERIOD || ' ' || l_grace_time_unit);
253 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SCHED_START_DATE',disp_attr_rec.SCHEDULED_START_DATE);
254 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SCHED_END_DATE',disp_attr_rec.SCHEDULED_END_DATE);
255 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'REVISED_START_DATE',disp_attr_rec.REVISED_START_DATE);
256 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'REVISED_END_DATE',disp_attr_rec.REVISED_END_DATE);
257 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'ACTUAL_START_DATE',disp_attr_rec.ACTUAL_START_DATE);
258 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'ACTUAL_END_DATE',disp_attr_rec.ACTUAL_END_DATE);
259 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'OWNER',l_owner);
260
261
262 l_wf_timeout := (l_wf_timeout * 24 * 60) / 4 ; -- Converting days into minutes
263
264 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
265 aname => '#FROM_ROLE',
266 avalue => l_userName );
267
268 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
269 aname => 'GMDQMSAP_TIMEOUT',
270 avalue => l_wf_timeout);
271 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
272 aname => 'GMDQMSAP_MESG_CNT',
273 avalue => 1);
274
275
276 p_resultout := 'COMPLETE:Y';
277 ELSE
278 WF_CORE.CONTEXT ('GMD_SS_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
279 raise APPLICATION_ERROR;
280 END IF;
281
282 END IF;
283
284 EXCEPTION WHEN NO_DATA_FOUND THEN
285 WF_CORE.CONTEXT ('GMD_SS_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,'Invalid SS ID');
286 raise;
287 END IS_APPROVAL_REQ;
288
289 /**************************************************************************************
290 *** This procedure is associated with GMDQSMAP_APP_COMMENT activity of the workflow **
291 *** When user enters comments in response to a notification this procedure appends **
292 *** comments to internal variable so that full history can be shown in notification **
293 *** body. **
294 **************************************************************************************/
295
296
297 PROCEDURE APPEND_COMMENTS (
298 p_itemtype IN VARCHAR2,
299 p_itemkey IN VARCHAR2,
300 p_actid IN NUMBER,
301 p_funcmode IN VARCHAR2,
302 p_resultout OUT NOCOPY VARCHAR2) IS
303
304 l_comment VARCHAR2(4000):= wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDQMSAP_COMMENT');
305 l_mesg_comment VARCHAR2(4000):= wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDQMSAP_DISP_COMMENT');
306 l_performer VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDQMSAP_CURR_PERFORMER');
307 BEGIN
308 IF (p_funcmode = 'RUN' AND l_comment IS NOT NULL) THEN
309 BEGIN
310 l_mesg_comment := l_mesg_comment||wf_core.newline||l_performer||' : '||FND_DATE.DATE_TO_CHARDT(SYSDATE)||
311 wf_core.newline||l_comment;
312 l_comment := null;
313 EXCEPTION WHEN OTHERS THEN
314 NULL;
315 END;
316
317 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
318 itemkey => p_itemkey,
319 aname => 'GMDQMSAP_DISP_COMMENT',
320 avalue => l_mesg_comment);
321 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
322 itemkey => p_itemkey,
323 aname => 'GMDQMSAP_COMMENT',
324 avalue => l_comment);
325 END IF;
326 END APPEND_COMMENTS;
327
328 /***************************************************************************************
329 *** This procedure is associated with VERIFY_ANY_MORE_APPR activity of the workflow **
330 *** once current approver approves status change request this procedure call AME API **
331 *** to verify any more approvers need to approve this request. if it needs some more **
332 *** approvals then it sets approver info to workflow attrbute. now workflow moves to **
333 *** next approval processing. this will continue either all approves approves the **
334 *** request or any one of the rejects. if all approvals are complete then it sets **
335 *** status to target status **
336 ***************************************************************************************/
337
338
339 PROCEDURE ANY_MORE_APPROVERS (
340 p_itemtype IN VARCHAR2,
341 p_itemkey IN VARCHAR2,
342 p_actid IN NUMBER,
343 p_funcmode IN VARCHAR2,
344 p_resultout OUT NOCOPY VARCHAR2) IS
345 applicationId number :=552;
346 transactionType varchar2(50) := 'GMDQM_STABILITY_CSTS';
347 nextApprover ame_util.approverRecord;
348 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
349 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
350 lSSId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SS_ID');
351 l_userID integer;
352 l_userName FND_USER.USER_NAME%TYPE;
353 api_ret_status VARCHAR2(1);
354 api_se_ret_status VARCHAR2(1);
355 api_err_mesg VARCHAR2(240);
356
357 BEGIN
358
359 IF p_funcmode = 'RUN' THEN
360
361 --
362 -- Get the next approver who need to approve the trasaction
363 --
364 ame_api.getNextApprover(applicationIdIn => applicationId,
365 transactionIdIn => lSSId,
366 transactionTypeIn => transactionType,
367 nextApproverOut => nextApprover);
368
369 IF nextApprover.user_id IS NULL and nextApprover.person_id IS NULL
370 THEN
371 --
372 -- All Approvers are approved.
373 -- change status of the object to target status
374 --
375
376 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_STABILITY_STUDIES_B'
377 , p_id => lSSId
378 , p_source_status => lStartStatus
379 , p_target_status => lTargetStatus
380 , p_mode => 'A'
381 , p_entity_type => 'STABILITY' --RLNAGARA B5727585
382 , x_return_status => api_ret_status
383 , x_message => api_err_mesg );
384
385
386 IF api_ret_status <> 'S' THEN
387 WF_CORE.CONTEXT ('GMD_SS_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
388 raise APPLICATION_ERROR;
389 END IF;
390
391 IF (l_debug = 'Y') THEN
392 gmd_debug.put_line('Target Status ' || lTargetStatus);
393 END IF;
394
395 if (lTargetStatus = 400) then
396 -- We got approved, so kick off API to create sampling events
397 GMD_SS_WFLOW_GRP.events_for_status_change(lSSId , api_se_ret_status) ;
398 elsif (lTargetStatus = 700) then
399 -- We need to launch; Enable the Mother workflow for testing
400 gmd_api_pub.raise ('oracle.apps.gmd.qm.ss.test',
401 lSSId);
402 end if;
403
404 IF (l_debug = 'Y') THEN
405 gmd_debug.put_line('Called needed APIs');
406 END IF;
407
408 p_resultout := 'COMPLETE:N';
409 ELSE
410 IF nextApprover.person_id IS NOT NULL THEN
411 --
412 -- if we got HR Person then we have to find corresponding FND USER
413 -- assumption here is all HR user configured in AME will have
414 -- corresponding FND USER
415 --
416 l_userID := ame_util.personIdToUserId(nextApprover.person_id);
417 ELSE
418 l_userID := nextApprover.user_id;
419 END IF;
420 l_userName := GET_FND_USER_NAME(l_userId);
421 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'USER_ID',l_userID);
422 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'APPROVER',l_userName);
423 p_resultout := 'COMPLETE:Y';
424 END IF;
425 END IF;
426
427
428 END ANY_MORE_APPROVERS;
429
430 /*************************************************************************************
431 *** Following procedure is to verify any reminder is required when workflow timeout**
432 *** occurs **
433 *************************************************************************************/
434
435 PROCEDURE REMINDAR_CHECK (
436 p_itemtype IN VARCHAR2,
437 p_itemkey IN VARCHAR2,
438 p_actid IN NUMBER,
439 p_funcmode IN VARCHAR2,
440 p_resultout OUT NOCOPY VARCHAR2) IS
441 l_mesg_cnt number:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDQMSAP_MESG_CNT');
442 l_approver VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'APPROVER');
443 BEGIN
444 IF (p_funcmode = 'TIMEOUT') THEN
445 l_mesg_cnt := l_mesg_cnt + 1;
446 IF l_mesg_cnt <= 4 THEN
447 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
448 aname => 'GMDQMSAP_MESG_CNT',
449 avalue => l_mesg_cnt);
450 ELSE
451 p_resultout := 'COMPLETE:DEFAULT';
452 END IF;
453 ELSIF (p_funcmode = 'RESPOND') THEN
454 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
455 itemkey => p_itemkey,
456 aname => 'GMDQMSAP_CURR_PERFORMER',
457 avalue => l_approver);
458 END IF;
459 END;
460
461
462 /****************************************************************************************
463 *** This procedure is associated with GMDQMSAP_NOTI_NOT_RESP activity of the workflow **
464 *** When approver fails to respond to notification defined in GMD: Workflow timeout **
465 *** profile this procedure sets spec status to start status and ends the workflow **
466 *** approval process. **
467 ****************************************************************************************/
468
469 PROCEDURE NO_RESPONSE (
470 p_itemtype IN VARCHAR2,
471 p_itemkey IN VARCHAR2,
472 p_actid IN NUMBER,
473 p_funcmode IN VARCHAR2,
474 p_resultout OUT NOCOPY VARCHAR2) IS
475 lSpecId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SS_ID');
476 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
477 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
478 api_ret_status VARCHAR2(1);
479 api_err_mesg VARCHAR2(240);
480 BEGIN
481 IF p_funcmode = 'RUN' THEN
482
483
484 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_STABILITY_STUDIES_B'
485 , p_id => lSpecId
486 , p_source_status => lStartStatus
487 , p_target_status => lTargetStatus
488 , p_mode => 'S'
489 , p_entity_type => 'STABILITY' --RLNAGARA B5727585
490 , x_return_status => api_ret_status
491 , x_message => api_err_mesg );
492
493 IF api_ret_status <> 'S' THEN
494 WF_CORE.CONTEXT ('GMD_SS_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
495 raise APPLICATION_ERROR;
496 END IF;
497 END IF;
498 END NO_RESPONSE;
499
500 /****************************************************************************************
501 *** This procedure is associated with GMDQSPAP_NOTI_REWORK activity of the workflow **
502 *** When approver rejects status change request procedure sets spec status to **
503 *** rework status and ends the workflow approval process. **
504 ****************************************************************************************/
505
506
507 PROCEDURE REQ_REJECTED (
508 p_itemtype IN VARCHAR2,
509 p_itemkey IN VARCHAR2,
510 p_actid IN NUMBER,
511 p_funcmode IN VARCHAR2,
512 p_resultout OUT NOCOPY VARCHAR2) IS
513 applicationId number :=552;
514 transactionType varchar2(50) := 'GMDQM_STABILITY_CSTS';
515 nextApprover ame_util.approverRecord;
516 lSSId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SS_ID');
517 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
518 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
519 l_userID VARCHAR2(100) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'USER_ID');
520 new_user_id VARCHAR2(100);
521 api_ret_status VARCHAR2(1);
522 api_err_mesg VARCHAR2(240);
523 BEGIN
524 IF p_funcmode = 'RUN' THEN
525
526 --
527 -- Update Approver action
528 --
529 ame_api.getNextApprover(applicationIdIn => applicationId,
530 transactionIdIn => lSSId,
531 transactionTypeIn => transactionType,
532 nextApproverOut => nextApprover);
533 IF nextApprover.person_id IS NOT NULL THEN
534 --
535 -- if we got HR Person then we have to find corresponding FND USER
536 -- assumption here is all HR user configured in AME will have
537 -- corresponding FND USER
538 --
539 new_user_id := ame_util.personIdToUserId(nextApprover.person_id);
540 ELSE
541 new_user_id := nextApprover.user_id;
542 END IF;
543 IF new_user_id = l_userID THEN
544 nextApprover.approval_status := ame_util.rejectStatus;
545 ame_api.updateApprovalStatus(applicationIdIn => applicationId,
546 transactionIdIn => lSSId,
547 transactionTypeIn => transactionType,
548 ApproverIn => nextApprover);
549 END IF;
550
551
552 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_STABILITY_STUDIES_B'
553 , p_id => lSSId
554 , p_source_status => lStartStatus
555 , p_target_status => lTargetStatus
556 , p_mode => 'R'
557 , p_entity_type => 'STABILITY' --RLNAGARA B5727585
558 , x_return_status => api_ret_status
559 , x_message => api_err_mesg );
560
561 IF api_ret_status <> 'S' THEN
562 WF_CORE.CONTEXT ('GMD_SS_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
563 raise APPLICATION_ERROR;
564 END IF;
565 END IF;
566
567 END REQ_REJECTED;
568
569 /****************************************************************************************
570 *** This procedure is associated with GMDQSPAP_NOTI_APPROVED activity of the workflow **
571 *** When approver approves status change request procedure sets AME Approver status **
572 *** to approved status and continues with approval process to verify any more **
573 *** approvals required **
574 ****************************************************************************************/
575
576
577 PROCEDURE REQ_APPROVED (
578 p_itemtype IN VARCHAR2,
579 p_itemkey IN VARCHAR2,
580 p_actid IN NUMBER,
581 p_funcmode IN VARCHAR2,
582 p_resultout OUT NOCOPY VARCHAR2) IS
583 applicationId number :=552;
584 transactionType varchar2(50) := 'GMDQM_STABILITY_CSTS';
585 nextApprover ame_util.approverRecord;
586 lSpecId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SS_ID');
587 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
588 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
589 l_userID VARCHAR2(100) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'USER_ID');
590 new_user_id VARCHAR2(100);
591 api_ret_status VARCHAR2(1);
592 api_err_mesg VARCHAR2(240);
593 BEGIN
594 IF p_funcmode = 'RUN' THEN
595 --
596 --
597 -- Update Approver action
598 --
599 ame_api.getNextApprover(applicationIdIn => applicationId,
600 transactionIdIn => lSpecId,
601 transactionTypeIn => transactionType,
602 nextApproverOut => nextApprover);
603 IF nextApprover.person_id IS NOT NULL THEN
604 --
605 -- if we got HR Person then we have to find corresponding FND USER
606 -- assumption here is all HR user configured in AME will have
607 -- corresponding FND USER
608 --
609 new_user_id := ame_util.personIdToUserId(nextApprover.person_id);
610 ELSE
611 new_user_id := nextApprover.user_id;
612 END IF;
613 IF new_user_id = l_userID THEN
614 nextApprover.approval_status := ame_util.approvedStatus;
615 ame_api.updateApprovalStatus(applicationIdIn => applicationId,
616 transactionIdIn => lSpecId,
617 transactionTypeIn => transactionType,
618 ApproverIn => nextApprover);
619 END IF;
620
621 END IF;
622
623 END REQ_APPROVED;
624
625 /**************************************************************************************
626 *** Following procedure accepts Status Code and entity type and resolves to Meaning **
627 **************************************************************************************/
628
629 FUNCTION GET_STATUS_MEANING(P_STATUS_CODE NUMBER,
630 P_ENTITY_TYPE VARCHAR2) RETURN VARCHAR2 IS
631 CURSOR GET_STAT_MEANING IS
632 SELECT MEANING
633 FROM GMD_QC_STATUS
634 WHERE STATUS_CODE = P_STATUS_CODE
635 AND ENTITY_TYPE = P_ENTITY_TYPE;
636 l_status_meaning GMD_QC_STATUS.MEANING%TYPE;
637 BEGIN
638 OPEN GET_STAT_MEANING;
639 FETCH GET_STAT_MEANING INTO l_status_meaning;
640 CLOSE GET_STAT_MEANING;
641 RETURN l_status_meaning;
642 END;
643
644 FUNCTION GET_STATUS_NEXT_MEANING(P_STATUS_CODE NUMBER,
645 P_ENTITY_TYPE VARCHAR2) RETURN VARCHAR2 IS
646
647 target_status number;
648
649 CURSOR GET_STAT_MEANING IS
650 SELECT MEANING
651 FROM GMD_QC_STATUS
652 WHERE STATUS_CODE = target_status
653 AND ENTITY_TYPE = P_ENTITY_TYPE;
654 l_status_meaning GMD_QC_STATUS.MEANING%TYPE;
655
656 BEGIN
657
658 if (p_status_code = 200) then
659 /* requesting Approval */
660 target_status := 400;
661 elsif (p_status_code = 500) then
662 /* requesting Launch */
663 target_status := 700;
664 elsif (p_status_code = 900) then
665 /* requesting Cancel */
666 target_status := 1000;
667 end if;
668
669 OPEN GET_STAT_MEANING;
670 FETCH GET_STAT_MEANING INTO l_status_meaning;
671 CLOSE GET_STAT_MEANING;
672 RETURN l_status_meaning;
673 END;
674
675
676 /**************************************************************************************
677 *** Following procedure is to raise Status Change approval business event **
678 **************************************************************************************/
679
680 PROCEDURE RAISE_SS_APPR_EVENT(p_SS_ID NUMBER,
681 p_START_STATUS NUMBER,
682 p_TARGET_STATUS NUMBER) IS
683 l_parameter_list wf_parameter_list_t :=wf_parameter_list_t( );
684 l_event_name VARCHAR2(80) := 'oracle.apps.gmd.qm.ss.csts';
685
686 BEGIN
687 wf_log_pkg.wf_debug_flag:=TRUE;
688 wf_event.AddParameterToList('SS_ID', p_SS_ID,l_parameter_list);
689 wf_event.AddParameterToList('START_STATUS',p_START_STATUS ,l_parameter_list);
690 wf_event.AddParameterToList('TARGET_STATUS',p_TARGET_STATUS ,l_parameter_list);
691 wf_event.raise(p_event_name => L_event_name,
692 p_event_key => p_SS_ID,
693 p_parameters => l_parameter_list);
694 l_parameter_list.DELETE;
695 END;
696
697 END GMD_SS_APPROVAL_WF_PKG;