[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.7 2011/03/18 19:42:38 plowe 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)|| replaced by below line for Bug 11874618 - 12.2 TECHNOLOGY PROJECT ONLINE PATCHING
311 l_mesg_comment := l_mesg_comment||wf_core.newline||l_performer||' : '||FND_DATE.DATE_TO_CHARDT(DATEVAL => SYSDATE, CALENDAR_AWARE => 2)||
312 wf_core.newline||l_comment;
313 l_comment := null;
314 EXCEPTION WHEN OTHERS THEN
315 NULL;
316 END;
317
318 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
319 itemkey => p_itemkey,
320 aname => 'GMDQMSAP_DISP_COMMENT',
321 avalue => l_mesg_comment);
322 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
323 itemkey => p_itemkey,
324 aname => 'GMDQMSAP_COMMENT',
325 avalue => l_comment);
326 END IF;
327 END APPEND_COMMENTS;
328
329 /***************************************************************************************
330 *** This procedure is associated with VERIFY_ANY_MORE_APPR activity of the workflow **
331 *** once current approver approves status change request this procedure call AME API **
332 *** to verify any more approvers need to approve this request. if it needs some more **
333 *** approvals then it sets approver info to workflow attrbute. now workflow moves to **
334 *** next approval processing. this will continue either all approves approves the **
335 *** request or any one of the rejects. if all approvals are complete then it sets **
336 *** status to target status **
337 ***************************************************************************************/
338
339
340 PROCEDURE ANY_MORE_APPROVERS (
341 p_itemtype IN VARCHAR2,
342 p_itemkey IN VARCHAR2,
343 p_actid IN NUMBER,
344 p_funcmode IN VARCHAR2,
345 p_resultout OUT NOCOPY VARCHAR2) IS
346 applicationId number :=552;
347 transactionType varchar2(50) := 'GMDQM_STABILITY_CSTS';
348 nextApprover ame_util.approverRecord;
349 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
350 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
351 lSSId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SS_ID');
352 l_userID integer;
353 l_userName FND_USER.USER_NAME%TYPE;
354 api_ret_status VARCHAR2(1);
355 api_se_ret_status VARCHAR2(1);
356 api_err_mesg VARCHAR2(240);
357
358 BEGIN
359
360 IF p_funcmode = 'RUN' THEN
361
362 --
363 -- Get the next approver who need to approve the trasaction
364 --
365 ame_api.getNextApprover(applicationIdIn => applicationId,
366 transactionIdIn => lSSId,
367 transactionTypeIn => transactionType,
368 nextApproverOut => nextApprover);
369
370 IF nextApprover.user_id IS NULL and nextApprover.person_id IS NULL
371 THEN
372 --
373 -- All Approvers are approved.
374 -- change status of the object to target status
375 --
376
377 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_STABILITY_STUDIES_B'
378 , p_id => lSSId
379 , p_source_status => lStartStatus
380 , p_target_status => lTargetStatus
381 , p_mode => 'A'
382 , p_entity_type => 'STABILITY' --RLNAGARA B5727585
383 , x_return_status => api_ret_status
384 , x_message => api_err_mesg );
385
386
387 IF api_ret_status <> 'S' THEN
388 WF_CORE.CONTEXT ('GMD_SS_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
389 raise APPLICATION_ERROR;
390 END IF;
391
392 IF (l_debug = 'Y') THEN
393 gmd_debug.put_line('Target Status ' || lTargetStatus);
394 END IF;
395
396 if (lTargetStatus = 400) then
397 -- We got approved, so kick off API to create sampling events
398 GMD_SS_WFLOW_GRP.events_for_status_change(lSSId , api_se_ret_status) ;
399 elsif (lTargetStatus = 700) then
400 -- We need to launch; Enable the Mother workflow for testing
401 gmd_api_pub.raise ('oracle.apps.gmd.qm.ss.test',
402 lSSId);
403 end if;
404
405 IF (l_debug = 'Y') THEN
406 gmd_debug.put_line('Called needed APIs');
407 END IF;
408
409 p_resultout := 'COMPLETE:N';
410 ELSE
411 IF nextApprover.person_id IS NOT NULL THEN
412 --
413 -- if we got HR Person then we have to find corresponding FND USER
414 -- assumption here is all HR user configured in AME will have
415 -- corresponding FND USER
416 --
417 l_userID := ame_util.personIdToUserId(nextApprover.person_id);
418 ELSE
419 l_userID := nextApprover.user_id;
420 END IF;
421 l_userName := GET_FND_USER_NAME(l_userId);
422 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'USER_ID',l_userID);
423 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'APPROVER',l_userName);
424 p_resultout := 'COMPLETE:Y';
425 END IF;
426 END IF;
427
428
429 END ANY_MORE_APPROVERS;
430
431 /*************************************************************************************
432 *** Following procedure is to verify any reminder is required when workflow timeout**
433 *** occurs **
434 *************************************************************************************/
435
436 PROCEDURE REMINDAR_CHECK (
437 p_itemtype IN VARCHAR2,
438 p_itemkey IN VARCHAR2,
439 p_actid IN NUMBER,
440 p_funcmode IN VARCHAR2,
441 p_resultout OUT NOCOPY VARCHAR2) IS
442 l_mesg_cnt number:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDQMSAP_MESG_CNT');
443 l_approver VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'APPROVER');
444 BEGIN
445 IF (p_funcmode = 'TIMEOUT') THEN
446 l_mesg_cnt := l_mesg_cnt + 1;
447 IF l_mesg_cnt <= 4 THEN
448 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
449 aname => 'GMDQMSAP_MESG_CNT',
450 avalue => l_mesg_cnt);
451 ELSE
452 p_resultout := 'COMPLETE:DEFAULT';
453 END IF;
454 ELSIF (p_funcmode = 'RESPOND') THEN
455 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
456 itemkey => p_itemkey,
457 aname => 'GMDQMSAP_CURR_PERFORMER',
458 avalue => l_approver);
459 END IF;
460 END;
461
462
463 /****************************************************************************************
464 *** This procedure is associated with GMDQMSAP_NOTI_NOT_RESP activity of the workflow **
465 *** When approver fails to respond to notification defined in GMD: Workflow timeout **
466 *** profile this procedure sets spec status to start status and ends the workflow **
467 *** approval process. **
468 ****************************************************************************************/
469
470 PROCEDURE NO_RESPONSE (
471 p_itemtype IN VARCHAR2,
472 p_itemkey IN VARCHAR2,
473 p_actid IN NUMBER,
474 p_funcmode IN VARCHAR2,
475 p_resultout OUT NOCOPY VARCHAR2) IS
476 lSpecId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SS_ID');
477 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
478 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
479 api_ret_status VARCHAR2(1);
480 api_err_mesg VARCHAR2(240);
481 BEGIN
482 IF p_funcmode = 'RUN' THEN
483
484
485 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_STABILITY_STUDIES_B'
486 , p_id => lSpecId
487 , p_source_status => lStartStatus
488 , p_target_status => lTargetStatus
489 , p_mode => 'S'
490 , p_entity_type => 'STABILITY' --RLNAGARA B5727585
491 , x_return_status => api_ret_status
492 , x_message => api_err_mesg );
493
494 IF api_ret_status <> 'S' THEN
495 WF_CORE.CONTEXT ('GMD_SS_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
496 raise APPLICATION_ERROR;
497 END IF;
498 END IF;
499 END NO_RESPONSE;
500
501 /****************************************************************************************
502 *** This procedure is associated with GMDQSPAP_NOTI_REWORK activity of the workflow **
503 *** When approver rejects status change request procedure sets spec status to **
504 *** rework status and ends the workflow approval process. **
505 ****************************************************************************************/
506
507
508 PROCEDURE REQ_REJECTED (
509 p_itemtype IN VARCHAR2,
510 p_itemkey IN VARCHAR2,
511 p_actid IN NUMBER,
512 p_funcmode IN VARCHAR2,
513 p_resultout OUT NOCOPY VARCHAR2) IS
514 applicationId number :=552;
515 transactionType varchar2(50) := 'GMDQM_STABILITY_CSTS';
516 nextApprover ame_util.approverRecord;
517 lSSId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SS_ID');
518 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
519 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
520 l_userID VARCHAR2(100) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'USER_ID');
521 new_user_id VARCHAR2(100);
522 api_ret_status VARCHAR2(1);
523 api_err_mesg VARCHAR2(240);
524 BEGIN
525 IF p_funcmode = 'RUN' THEN
526
527 --
528 -- Update Approver action
529 --
530 ame_api.getNextApprover(applicationIdIn => applicationId,
531 transactionIdIn => lSSId,
532 transactionTypeIn => transactionType,
533 nextApproverOut => nextApprover);
534 IF nextApprover.person_id IS NOT NULL THEN
535 --
536 -- if we got HR Person then we have to find corresponding FND USER
537 -- assumption here is all HR user configured in AME will have
538 -- corresponding FND USER
539 --
540 new_user_id := ame_util.personIdToUserId(nextApprover.person_id);
541 ELSE
542 new_user_id := nextApprover.user_id;
543 END IF;
544 IF new_user_id = l_userID THEN
545 nextApprover.approval_status := ame_util.rejectStatus;
546 ame_api.updateApprovalStatus(applicationIdIn => applicationId,
547 transactionIdIn => lSSId,
548 transactionTypeIn => transactionType,
549 ApproverIn => nextApprover);
550 END IF;
551
552
553 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_STABILITY_STUDIES_B'
554 , p_id => lSSId
555 , p_source_status => lStartStatus
556 , p_target_status => lTargetStatus
557 , p_mode => 'R'
558 , p_entity_type => 'STABILITY' --RLNAGARA B5727585
559 , x_return_status => api_ret_status
560 , x_message => api_err_mesg );
561
562 IF api_ret_status <> 'S' THEN
563 WF_CORE.CONTEXT ('GMD_SS_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
564 raise APPLICATION_ERROR;
565 END IF;
566 END IF;
567
568 END REQ_REJECTED;
569
570 /****************************************************************************************
571 *** This procedure is associated with GMDQSPAP_NOTI_APPROVED activity of the workflow **
572 *** When approver approves status change request procedure sets AME Approver status **
573 *** to approved status and continues with approval process to verify any more **
574 *** approvals required **
575 ****************************************************************************************/
576
577
578 PROCEDURE REQ_APPROVED (
579 p_itemtype IN VARCHAR2,
580 p_itemkey IN VARCHAR2,
581 p_actid IN NUMBER,
582 p_funcmode IN VARCHAR2,
583 p_resultout OUT NOCOPY VARCHAR2) IS
584 applicationId number :=552;
585 transactionType varchar2(50) := 'GMDQM_STABILITY_CSTS';
586 nextApprover ame_util.approverRecord;
587 lSpecId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SS_ID');
588 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
589 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
590 l_userID VARCHAR2(100) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'USER_ID');
591 new_user_id VARCHAR2(100);
592 api_ret_status VARCHAR2(1);
593 api_err_mesg VARCHAR2(240);
594 BEGIN
595 IF p_funcmode = 'RUN' THEN
596 --
597 --
598 -- Update Approver action
599 --
600 ame_api.getNextApprover(applicationIdIn => applicationId,
601 transactionIdIn => lSpecId,
602 transactionTypeIn => transactionType,
603 nextApproverOut => nextApprover);
604 IF nextApprover.person_id IS NOT NULL THEN
605 --
606 -- if we got HR Person then we have to find corresponding FND USER
607 -- assumption here is all HR user configured in AME will have
608 -- corresponding FND USER
609 --
610 new_user_id := ame_util.personIdToUserId(nextApprover.person_id);
611 ELSE
612 new_user_id := nextApprover.user_id;
613 END IF;
614 IF new_user_id = l_userID THEN
615 nextApprover.approval_status := ame_util.approvedStatus;
616 ame_api.updateApprovalStatus(applicationIdIn => applicationId,
617 transactionIdIn => lSpecId,
618 transactionTypeIn => transactionType,
619 ApproverIn => nextApprover);
620 END IF;
621
622 END IF;
623
624 END REQ_APPROVED;
625
626 /**************************************************************************************
627 *** Following procedure accepts Status Code and entity type and resolves to Meaning **
628 **************************************************************************************/
629
630 FUNCTION GET_STATUS_MEANING(P_STATUS_CODE NUMBER,
631 P_ENTITY_TYPE VARCHAR2) RETURN VARCHAR2 IS
632 CURSOR GET_STAT_MEANING IS
633 SELECT MEANING
634 FROM GMD_QC_STATUS
635 WHERE STATUS_CODE = P_STATUS_CODE
636 AND ENTITY_TYPE = P_ENTITY_TYPE;
637 l_status_meaning GMD_QC_STATUS.MEANING%TYPE;
638 BEGIN
639 OPEN GET_STAT_MEANING;
640 FETCH GET_STAT_MEANING INTO l_status_meaning;
641 CLOSE GET_STAT_MEANING;
642 RETURN l_status_meaning;
643 END;
644
645 FUNCTION GET_STATUS_NEXT_MEANING(P_STATUS_CODE NUMBER,
646 P_ENTITY_TYPE VARCHAR2) RETURN VARCHAR2 IS
647
648 target_status number;
649
650 CURSOR GET_STAT_MEANING IS
651 SELECT MEANING
652 FROM GMD_QC_STATUS
653 WHERE STATUS_CODE = target_status
654 AND ENTITY_TYPE = P_ENTITY_TYPE;
655 l_status_meaning GMD_QC_STATUS.MEANING%TYPE;
656
657 BEGIN
658
659 if (p_status_code = 200) then
660 /* requesting Approval */
661 target_status := 400;
662 elsif (p_status_code = 500) then
663 /* requesting Launch */
664 target_status := 700;
665 elsif (p_status_code = 900) then
666 /* requesting Cancel */
667 target_status := 1000;
668 end if;
669
670 OPEN GET_STAT_MEANING;
671 FETCH GET_STAT_MEANING INTO l_status_meaning;
672 CLOSE GET_STAT_MEANING;
673 RETURN l_status_meaning;
674 END;
675
676
677 /**************************************************************************************
678 *** Following procedure is to raise Status Change approval business event **
679 **************************************************************************************/
680
681 PROCEDURE RAISE_SS_APPR_EVENT(p_SS_ID NUMBER,
682 p_START_STATUS NUMBER,
683 p_TARGET_STATUS NUMBER) IS
684 l_parameter_list wf_parameter_list_t :=wf_parameter_list_t( );
685 l_event_name VARCHAR2(80) := 'oracle.apps.gmd.qm.ss.csts';
686
687 BEGIN
688 wf_log_pkg.wf_debug_flag:=TRUE;
689 wf_event.AddParameterToList('SS_ID', p_SS_ID,l_parameter_list);
690 wf_event.AddParameterToList('START_STATUS',p_START_STATUS ,l_parameter_list);
691 wf_event.AddParameterToList('TARGET_STATUS',p_TARGET_STATUS ,l_parameter_list);
692 wf_event.raise(p_event_name => L_event_name,
693 p_event_key => p_SS_ID,
694 p_parameters => l_parameter_list);
695 l_parameter_list.DELETE;
696 END;
697
698 END GMD_SS_APPROVAL_WF_PKG;