[Home] [Help]
PACKAGE BODY: APPS.GMDQSPEC_APPROVAL_WF_PKG
Source
1 PACKAGE BODY GMDQSPEC_APPROVAL_WF_PKG AS
2 /* $Header: GMDQSAPB.pls 120.5 2006/04/24 05:00:58 rlnagara noship $ */
3
4
5 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7 APPLICATION_ERROR EXCEPTION;
8 -- Following function accepts FND userId and returns
9 -- User name
10 FUNCTION GET_FND_USER_NAME( userId Integer) RETURN VARCHAR2 IS
11 CURSOR GET_USER_NAME IS
12 SELECT USER_NAME
13 FROM FND_USER
14 WHERE USER_ID = userId;
15 l_userName FND_USER.USER_NAME%TYPE;
16 BEGIN
17 OPEN GET_USER_NAME;
18 FETCH GET_USER_NAME INTO l_userName;
19 CLOSE GET_USER_NAME;
20 RETURN l_userName;
21 END GET_FND_USER_NAME;
22
23 /********************************************************************************
24 *** This procedure is associated with GMDQSPAP_ISAPROVAL_REQUIRED workflow. **
25 *** This code will execute when Spec Approval Business Event is raised. **
26 *** This verfifies whether approval required for this transaction or not **
27 *** If approval is required then udated spec status to pending as defined **
28 *** GMD_QC_STATUS_NEXT and populates workflow attributes **
29 ********************************************************************************/
30
31 PROCEDURE IS_APPROVAL_REQ (
32 p_itemtype IN VARCHAR2,
33 p_itemkey IN VARCHAR2,
34 p_actid IN NUMBER,
35 p_funcmode IN VARCHAR2,
36 p_resultout OUT NOCOPY VARCHAR2) IS
37 applicationId number :=552;
38 transactionType varchar2(50) := 'oracle.apps.gmd.qm.spec.sts';
39 nextApprover ame_util.approverRecord;
40 l_userID integer;
41 l_userName FND_USER.USER_NAME%TYPE;
42 l_Requester FND_USER.USER_NAME%TYPE;
43 l_Owner FND_USER.USER_NAME%TYPE;
44 lSpecId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_ID');
45 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
46 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
47 l_wf_timeout NUMBER := TO_NUMBER(FND_PROFILE.VALUE ('GMD_WF_TIMEOUT'));
48 lStartStatus_DESC VARCHAR2(240);
49 lTargetStatus_DESC VARCHAR2(240);
50 api_ret_status VARCHAR2(1);
51 api_err_mesg VARCHAR2(240);
52 l_message VARCHAR2(500);
53
54 /* cursor get_disp_Attr IS
55 SELECT SPEC.SPEC_NAME
56 ,SPEC.SPEC_VERS
57 ,SPEC.SPEC_DESC
58 ,SPEC.GRADE
59 ,SPEC.SPEC_STATUS
60 ,SPEC.OWNER_ORGN_CODE
61 ,SPEC.OWNER_ID
62 ,SPEC.LAST_UPDATED_BY
63 ,ITEM.ITEM_NO
64 ,ITEM.ITEM_DESC1
65 ,ORGN.ORGN_NAME
66 ,QCSTAT.MEANING
67 ,SPEC.SPEC_TYPE
68 ,SPEC.OVERLAY_IND
69 ,SPEC.BASE_SPEC_ID
70 FROM GMD_SPECIFICATIONS SPEC
71 ,IC_ITEM_MST ITEM
72 ,SY_ORGN_MST ORGN
73 ,GMD_QC_STATUS QCSTAT
74 WHERE SPEC.ITEM_ID = ITEM.ITEM_ID (+)
75 AND ORGN.ORGN_CODE = SPEC.OWNER_ORGN_CODE
76 AND SPEC.SPEC_STATUS = QCSTAT.STATUS_CODE
77 AND SPEC.SPEC_ID = lSpecId;*/
78
79 -- INVCONV, NSRIVAST
80 -- Chagned the table names and attributes
81 cursor get_disp_Attr IS
82
83 -- bug 4924550 sql id 14690143 start
84 /* SELECT SPEC.SPEC_NAME
85 ,SPEC.SPEC_VERS
86 ,SPEC.SPEC_DESC
87 ,SPEC.GRADE_CODE
88 ,SPEC.SPEC_STATUS
89 ,SPEC.OWNER_ORGANIZATION_ID
90 ,SPEC.OWNER_ID
91 ,SPEC.LAST_UPDATED_BY
92 ,ITEM.CONCATENATED_SEGMENTS
93 ,SPEC.REVISION
94 ,ITEM.DESCRIPTION
95 --,HAOU.NAME
96 ,MO.ORGANIZATION_NAME
97 ,MO.ORGANIZATION_CODE
98 ,QCSTAT.MEANING
99 ,SPEC.SPEC_TYPE
100 ,SPEC.OVERLAY_IND
101 ,SPEC.BASE_SPEC_ID
102 FROM GMD_SPECIFICATIONS SPEC
103 ,mtl_system_items_kfv ITEM
104 ,mtl_parameters ORGN
105 ,GMD_QC_STATUS QCSTAT
106 ,MTL_ORGANIZATIONS MO
107 WHERE SPEC.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID (+)
108 AND ORGN.ORGANIZATION_ID = SPEC.OWNER_ORGANIZATION_ID
109 AND SPEC.OWNER_ORGANIZATION_ID = nvl(ITEM.ORGANIZATION_ID ,SPEC.OWNER_ORGANIZATION_ID)
110 AND SPEC.SPEC_STATUS = QCSTAT.STATUS_CODE
111 AND SPEC.SPEC_ID = lSpecId
112 AND ORGN.ORGANIZATION_ID = MO.ORGANIZATION_ID; */
113 -- INVCONV, NSRIVAST
114
115 SELECT SPEC.SPEC_NAME
116 ,SPEC.SPEC_VERS
117 ,SPEC.SPEC_DESC
118 ,SPEC.GRADE_CODE
119 ,SPEC.SPEC_STATUS
120 ,SPEC.OWNER_ORGANIZATION_ID
121 ,SPEC.OWNER_ID
122 ,SPEC.LAST_UPDATED_BY
123 ,ITEM.CONCATENATED_SEGMENTS
124 ,SPEC.REVISION
125 ,ITEM.DESCRIPTION
126 --,HAOU.NAME
127 ,HR.NAME ORGANIZATION_NAME -- sql id
128 ,ORGN.ORGANIZATION_CODE -- sql id
129 ,QCSTAT.MEANING
130 ,SPEC.SPEC_TYPE
131 ,SPEC.OVERLAY_IND
132 ,SPEC.BASE_SPEC_ID
133 FROM
134 GMD_SPECIFICATIONS SPEC
135 ,mtl_system_items_kfv ITEM
136 ,mtl_parameters ORGN
137 ,GMD_QC_STATUS QCSTAT
138 --,MTL_ORGANIZATIONS MO
139 ,HR_ALL_ORGANIZATION_UNITS_TL HR -- sql id
140 WHERE SPEC.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID (+)
141 AND ORGN.ORGANIZATION_ID = SPEC.OWNER_ORGANIZATION_ID
142 and ORGN.ORGANIZATION_ID = HR.ORGANIZATION_ID -- sql id
143 and hr.language = userenv('LANG') -- sql id
144 AND SPEC.OWNER_ORGANIZATION_ID = nvl(ITEM.ORGANIZATION_ID ,SPEC.OWNER_ORGANIZATION_ID)
145 AND SPEC.SPEC_STATUS = QCSTAT.STATUS_CODE
146 AND SPEC.SPEC_ID = lSpecId ;
147 -- AND ORGN.ORGANIZATION_ID = MO.ORGANIZATION_ID; -- sql id
148 -- bug 4924550 sql id 14690143 end
149
150 disp_attr_rec get_disp_Attr%ROWTYPE;
151
152 cursor get_from_role is
153 select nvl( text, '')
154 from wf_Resources where name = 'WF_ADMIN_ROLE'
155 and language = userenv('LANG') ;
156
157 l_from_role varchar2(2000);
158 l_spectype_Desc VARCHAR2(50) := NULL;
159
160 --RLNAGARA Bug 4706861 Rework
161 CURSOR get_spec_det(p_spec_id NUMBER) IS
162 SELECT SPEC_NAME,SPEC_VERS
163 FROM GMD_SPECIFICATIONS_B
164 WHERE SPEC_ID = p_spec_id;
165
166 l_base_spec_name VARCHAR2(80);
167 l_base_spec_vers NUMBER;
168
169
170 begin
171
172
173 IF (l_debug = 'Y') THEN
174 gmd_debug.log_initialize('Specapproval');
175 gmd_debug.put_line('Spec ID ' || lSpecId);
176 gmd_debug.put_line('Start ' || lStartStatus);
177 gmd_debug.put_line('End ' || lTargetStatus);
178 END IF;
179
180 open get_from_role ;
181 fetch get_from_role into l_from_role ;
182 close get_from_role ;
183
184
185 IF p_funcmode = 'RUN' THEN
186 --
187 -- clear All Approvals from AME
188 -- following API removes previous instance of approval group from AME tables
189 --
190 ame_api.clearAllApprovals(applicationIdIn => applicationId,
191 transactionIdIn => lSpecId,
192 transactionTypeIn => transactionType);
193 --
194 -- Get the next approver who need to approve the trasaction
195 --
196 ame_api.getNextApprover(applicationIdIn => applicationId,
197 transactionIdIn => lSpecId,
198 transactionTypeIn => transactionType,
199 nextApproverOut => nextApprover);
200
201 IF nextApprover.user_id IS NULL and nextApprover.person_id IS NULL
202 THEN
203 --
204 -- Means either no AME rule is matching for this transaction ID or Approver list is empty.
205 -- change status of the object to target status
206 --
207 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_SPECIFICATIONS_B'
208 , p_id => lSpecId
209 , p_source_status => lStartStatus
210 , p_target_status => lTargetStatus
211 , p_mode => 'A'
212 , x_return_status => api_ret_status
213 , x_message => api_err_mesg );
214
215 IF api_ret_status <> 'S' THEN
216 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
217 raise APPLICATION_ERROR;
218 END IF;
219
220 IF (l_debug = 'Y') THEN
221 gmd_debug.put_line('No approvers ');
222 END IF;
223
224 p_resultout := 'COMPLETE:N';
225
226 ELSE
227 --
228 -- We got the first approver from AME
229 --
230 IF nextApprover.person_id IS NOT NULL THEN
231 --
232 -- if we got HR Person then we have to find corresponding FND USER
233 -- assumption here is all HR user configured in AME will have
234 -- corresponding FND USER
235 --
236 l_userID := ame_util.personIdToUserId(nextApprover.person_id);
237 ELSE
238 l_userID := nextApprover.user_id;
239 END IF;
240 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'USER_ID',l_userID);
241 l_userName := GET_FND_USER_NAME(l_userId);
242 --
243 -- Update status to pending
244 --
245
246 IF (l_debug = 'Y') THEN
247 gmd_debug.put_line('Requesting approvers');
248 gmd_debug.put_line('Final Status ' || lTargetStatus);
249 END IF;
250
251 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_SPECIFICATIONS_B'
252 , p_id => lSpecId
253 , p_source_status => lStartStatus
254 , p_target_status => lTargetStatus
255 , p_mode => 'P'
256 , x_return_status => api_ret_status
257 , x_message => api_err_mesg );
258
259 IF api_ret_status = 'S' THEN
260 -- Get attributes Required for display
261 open get_disp_Attr;
262 FETCH get_disp_Attr INTO disp_attr_rec;
263 IF get_disp_Attr%NOTFOUND THEN
264 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,FND_MESSAGE.GET_STRING('GMD','GMD_QC_INVALID_SPEC_ID'));
265 raise APPLICATION_ERROR;
266 END IF;
267
268 IF (l_debug = 'Y') THEN
269 gmd_debug.put_line('Setting up workflow attributes');
270 END IF;
271
272 l_requester := GET_FND_USER_NAME(disp_attr_rec.LAST_UPDATED_BY);
273 l_owner := GET_FND_USER_NAME(disp_attr_rec.OWNER_ID);
274 lStartStatus_DESC := GMDQSPEC_APPROVAL_WF_PKG.GET_STATUS_MEANING(lStartStatus,'S');
275 lTargetStatus_DESC:= GMDQSPEC_APPROVAL_WF_PKG.GET_STATUS_MEANING(lTargetStatus,'S');
276
277 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SPEC_NAME',disp_attr_rec.SPEC_NAME);
278 wf_engine.setitemattrnumber(p_itemtype, p_itemkey,'SPEC_VERS',disp_attr_rec.SPEC_VERS);
279 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SPEC_DESC',disp_attr_rec.SPEC_DESC);
280 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SPEC_STATUS',disp_attr_rec.MEANING);
281 -- wf_engine.setitemattrtext(p_itemtype, p_itemkey,'OWNER_ORGN_CODE',disp_attr_rec.OWNER_ORGN_CODE);
282 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'OWNER_ORGN_CODE',disp_attr_rec.ORGANIZATION_CODE); -- INVCONV, NSRIVAST
283 -- wf_engine.setitemattrtext(p_itemtype, p_itemkey,'OWNER_ORGN_NAME',disp_attr_rec.ORGN_NAME);
284 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'OWNER_ORGN_NAME',disp_attr_rec.ORGANIZATION_NAME); -- INVCONV, NSRIVAST
285 -- wf_engine.setitemattrtext(p_itemtype, p_itemkey,'GRADE',disp_attr_rec.GRADE);
286 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'GRADE',disp_attr_rec.GRADE_CODE); -- INVCONV, NSRIVAST
287 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'OWNER_NAME',l_owner);
288 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'REQUESTER',l_requester);
289 -- wf_engine.setitemattrtext(p_itemtype, p_itemkey,'ITEM_NO',disp_attr_rec.ITEM_NO);
290 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'ITEM_NO',disp_attr_rec.CONCATENATED_SEGMENTS); -- INVCONV, NSRIVAST
291 -- wf_engine.setitemattrtext(p_itemtype, p_itemkey,'ITEM_DESC',disp_attr_rec.ITEM_DESC1);
292 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'ITEM_DESC',disp_attr_rec.DESCRIPTION); -- INVCONV, NSRIVAST
293 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'START_STATUS_DESC',lStartStatus_DESC);
294 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS_DESC',lTargetStatus_DESC);
295 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'APPROVER',l_userName);
296
297 --RLNAGARA Bug # 4706861
298
299 IF disp_attr_rec.SPEC_TYPE IS NOT NULL THEN
300 SELECT DESCRIPTION INTO l_spectype_desc
301 FROM GEM_LOOKUPS
302 WHERE LOOKUP_TYPE = 'GMD_QC_SPEC_TYPE'
303 AND LOOKUP_CODE = disp_attr_rec.SPEC_TYPE ;
304 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SPEC_TYPE', l_spectype_desc);
305 END IF;
306
307 -- wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SPEC_TYPE',disp_attr_rec.SPEC_TYPE);
308
309 --RLNAGARA Bug # 4706861
310
311
312 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'OVERLAY', disp_attr_rec.OVERLAY_IND);
313
314 --RLNAGARA Bug 4706861 Rework
315 IF disp_attr_rec.BASE_SPEC_ID IS NOT NULL THEN
319 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'BASE_SPEC', l_base_spec_name);
316 OPEN get_spec_det(disp_attr_rec.BASE_SPEC_ID);
317 FETCH get_spec_det INTO l_base_spec_name, l_base_spec_vers;
318 CLOSE get_spec_det;
320 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'BASE_SPEC_VERS', l_base_spec_vers);
321 END IF;
322 -- wf_engine.setitemattrtext(p_itemtype, p_itemkey,'BASE_SPEC', disp_attr_rec.BASE_SPEC_ID);
323
324
325
326 wf_engine.setitemattrtext(p_itemtype, p_itemkey, 'ITEM_REVISION', disp_attr_rec.REVISION); -- INVCONV, NSRIVAST
327
328 /* Depending on whether the Spec is for an item or monitor, fill out the
329 tokenized message and set it in the workflow */
330 if (disp_attr_rec.SPEC_TYPE = 'M') THEN
331 /* This is a monitoring Spec */
332 FND_MESSAGE.SET_NAME('GMD','GMD_SPEC_APPROVAL_MON');
333 --FND_MESSAGE.SET_TOKEN('SPEC_TYPE', disp_attr_rec.SPEC_TYPE);
334 FND_MESSAGE.SET_TOKEN('SPEC_TYPE', 'Monitoring');
335 FND_MESSAGE.SET_TOKEN('OVERLAY', disp_attr_rec.OVERLAY_IND);
336 FND_MESSAGE.SET_TOKEN('BASE_SPEC', disp_attr_rec.BASE_SPEC_ID);
337 ELSE
338 /* This is an Item Spec */
339 FND_MESSAGE.SET_NAME('GMD','GMD_SPEC_APPROVAL_ITEM');
340 --FND_MESSAGE.SET_TOKEN('ITEM_NO', disp_attr_rec.ITEM_NO);
341 --FND_MESSAGE.SET_TOKEN('ITEM_DESC', disp_attr_rec.ITEM_DESC1);
342 --FND_MESSAGE.SET_TOKEN('GRADE', disp_attr_rec.GRADE);
343 FND_MESSAGE.SET_TOKEN('ITEM_NO', disp_attr_rec.CONCATENATED_SEGMENTS);
344 FND_MESSAGE.SET_TOKEN('ITEM_DESC', disp_attr_rec.DESCRIPTION);
345 FND_MESSAGE.SET_TOKEN('GRADE', disp_attr_rec.GRADE_CODE);
346 --FND_MESSAGE.SET_TOKEN('SPEC_TYPE', disp_attr_rec.SPEC_TYPE);
347 FND_MESSAGE.SET_TOKEN('SPEC_TYPE', 'Item');
348 END IF;
349
350 /* These are the common attributes in both messages */
351 FND_MESSAGE.SET_TOKEN('SPEC_NAME', disp_attr_rec.SPEC_NAME);
352 FND_MESSAGE.SET_TOKEN('SPEC_VERS', disp_attr_rec.SPEC_VERS);
353 FND_MESSAGE.SET_TOKEN('SPEC_DESC', disp_attr_rec.SPEC_DESC);
354 FND_MESSAGE.SET_TOKEN('SPEC_STATUS', disp_attr_rec.MEANING);
355 --FND_MESSAGE.SET_TOKEN('OWNER_ORGN_CODE', disp_attr_rec.OWNER_ORGN_CODE);
356 --FND_MESSAGE.SET_TOKEN('OWNER_ORGN_NAME', disp_attr_rec.ORGN_NAME);
357 FND_MESSAGE.SET_TOKEN('OWNER_ORGN_CODE', disp_attr_rec.ORGANIZATION_CODE); --INVCONV, NSRIVAST
358 FND_MESSAGE.SET_TOKEN('OWNER_ORGN_NAME', disp_attr_rec.ORGANIZATION_NAME); --INVCONV, NSRIVAST
359 FND_MESSAGE.SET_TOKEN('OWNER_NAME', l_owner);
360 FND_MESSAGE.SET_TOKEN('REQUESTER', l_requester);
361 FND_MESSAGE.SET_TOKEN('START_STATUS_DESC', lStartStatus_DESC);
362 FND_MESSAGE.SET_TOKEN('TARGET_STATUS_DESC', lTargetStatus_DESC);
363 FND_MESSAGE.SET_TOKEN('APPROVER', l_userName);
364
365
366 /* Set the message attribute, MSG, in the workflow */
367 -- FND_MESSAGE.SET_TOKEN('GMDQSPAP_MSG', FND_MESSAGE.GET() );
368
369
370 wf_engine.setitemattrtext(p_itemtype, p_itemkey, 'GMDQSPAP_MSG', FND_MESSAGE.GET());
371
372 l_wf_timeout := (l_wf_timeout * 24 * 60) / 4 ; -- Converting days into minutes
373
374 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
375 aname => '#FROM_ROLE',
376 avalue => l_userName );
377 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
378 aname => 'GMDQSPAP_TIMEOUT',
379 avalue => l_wf_timeout);
380 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
381 aname => 'GMDQSPAP_MESG_CNT',
382 avalue => 1);
383 p_resultout := 'COMPLETE:Y';
384 ELSE
385 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',
386 p_itemtype,p_itemkey,api_err_mesg );
387 raise APPLICATION_ERROR;
388 END IF;
389 END IF;
390 END IF;
391 EXCEPTION WHEN NO_DATA_FOUND THEN
392 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,'Invalid Spec ID');
393 raise;
394 END IS_APPROVAL_REQ;
395
396 /**************************************************************************************
397 *** This procedure is associated with GMDQSPAP_APP_COMMENT activity of the workflow **
398 *** When user enters comments in response to a notification this procedure appends **
399 *** comments to internal variable so that full history can be shown in notification **
400 *** body. **
401 **************************************************************************************/
402
403
404 PROCEDURE APPEND_COMMENTS (
405 p_itemtype IN VARCHAR2,
406 p_itemkey IN VARCHAR2,
407 p_actid IN NUMBER,
408 p_funcmode IN VARCHAR2,
409 p_resultout OUT NOCOPY VARCHAR2) IS
410
414 BEGIN
411 l_comment VARCHAR2(4000):= wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDQSPAP_COMMENT');
412 l_mesg_comment VARCHAR2(4000):= wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDQSPAP_DISP_COMMENT');
413 l_performer VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDQSPAP_CURR_PERFORMER');
415 IF (p_funcmode = 'RUN' AND l_comment IS NOT NULL) THEN
416 BEGIN
417 l_mesg_comment := l_mesg_comment||wf_core.newline||l_performer||' : '||FND_DATE.DATE_TO_CHARDT(SYSDATE)||
418 wf_core.newline||l_comment;
419 l_comment := null;
420 EXCEPTION WHEN OTHERS THEN
421 NULL;
422 END;
423
424 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
425 itemkey => p_itemkey,
426 aname => 'GMDQSPAP_DISP_COMMENT',
427 avalue => l_mesg_comment);
428 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
429 itemkey => p_itemkey,
430 aname => 'GMDQSPAP_COMMENT',
431 avalue => l_comment);
432 END IF;
433 END APPEND_COMMENTS;
434
435 /***************************************************************************************
436 *** This procedure is associated with VERIFY_ANY_MORE_APPR activity of the workflow **
437 *** once current approver approves status change request this procedure call AME API **
438 *** to verify any more approvers need to approve this request. if it needs some more **
439 *** approvals then it sets approver info to workflow attrbute. now workflow moves to **
440 *** next approval processing. this will continue either all approves approves the **
441 *** request or any one of the rejects. if all approvals are complete then it sets **
442 *** spec status to target status **
443 ***************************************************************************************/
444
445
446 PROCEDURE ANY_MORE_APPROVERS (
447 p_itemtype IN VARCHAR2,
448 p_itemkey IN VARCHAR2,
449 p_actid IN NUMBER,
450 p_funcmode IN VARCHAR2,
451 p_resultout OUT NOCOPY VARCHAR2) IS
452 applicationId number :=552;
453 transactionType varchar2(50) := 'oracle.apps.gmd.qm.spec.sts';
454 nextApprover ame_util.approverRecord;
455 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
456 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
457 lSpecId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_ID');
458 l_userID integer;
459 l_userName FND_USER.USER_NAME%TYPE;
460 api_ret_status VARCHAR2(1);
461 api_err_mesg VARCHAR2(240);
462 BEGIN
463 IF p_funcmode = 'RUN' THEN
464 --
465 -- Get the next approver who need to approve the trasaction
466 --
467 ame_api.getNextApprover(applicationIdIn => applicationId,
468 transactionIdIn => lSpecId,
469 transactionTypeIn => transactionType,
470 nextApproverOut => nextApprover);
471
472 IF nextApprover.user_id IS NULL and nextApprover.person_id IS NULL
473 THEN
474 --
475 -- All Approvers are approved.
476 -- change status of the object to target status
477 --
478
479 IF (l_debug = 'Y') THEN
480 gmd_debug.put_line('Finished approvers; changing status');
481 gmd_debug.put_line('Final Status ' || lTargetStatus);
482 END IF;
483
484 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_SPECIFICATIONS_B'
485 , p_id => lSpecId
486 , p_source_status => lStartStatus
487 , p_target_status => lTargetStatus
488 , p_mode => 'A'
489 , x_return_status => api_ret_status
490 , x_message => api_err_mesg );
491 IF api_ret_status <> 'S' THEN
492 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
493 raise APPLICATION_ERROR;
494 END IF;
495 p_resultout := 'COMPLETE:N';
496 ELSE
497 IF nextApprover.person_id IS NOT NULL THEN
498 --
499 -- if we got HR Person then we have to find corresponding FND USER
500 -- assumption here is all HR user configured in AME will have
501 -- corresponding FND USER
502 --
503 l_userID := ame_util.personIdToUserId(nextApprover.person_id);
504 ELSE
505 l_userID := nextApprover.user_id;
506 END IF;
507 l_userName := GET_FND_USER_NAME(l_userId);
508 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'USER_ID',l_userID);
509 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'APPROVER',l_userName);
510 p_resultout := 'COMPLETE:Y';
511 END IF;
512 END IF;
513 END ANY_MORE_APPROVERS;
514
515 /*************************************************************************************
516 *** Following procedure is to verify any reminder is required when workflow timeout**
517 *** occurs **
518 *************************************************************************************/
519
520 PROCEDURE REMINDAR_CHECK (
521 p_itemtype IN VARCHAR2,
522 p_itemkey IN VARCHAR2,
523 p_actid IN NUMBER,
524 p_funcmode IN VARCHAR2,
525 p_resultout OUT NOCOPY VARCHAR2) IS
526 l_mesg_cnt number:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDQSPAP_MESG_CNT');
527 l_approver VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'APPROVER');
528 BEGIN
529 IF (p_funcmode = 'TIMEOUT') THEN
530 l_mesg_cnt := l_mesg_cnt + 1;
531 IF l_mesg_cnt <= 4 THEN
532 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
533 aname => 'GMDQSPAP_MESG_CNT',
534 avalue => l_mesg_cnt);
535 ELSE
536 p_resultout := 'COMPLETE:DEFAULT';
537 END IF;
538 ELSIF (p_funcmode = 'RESPOND') THEN
539 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
540 itemkey => p_itemkey,
541 aname => 'GMDQSPAP_CURR_PERFORMER',
542 avalue => l_approver);
543 END IF;
544 END;
545
546
547 /****************************************************************************************
548 *** This procedure is associated with GMDQSPAP_NOTI_NOT_RESP activity of the workflow **
549 *** When approver fails to respond to notification defined in GMD: Workflow timeout **
550 *** profile this procedure sets spec status to start status and ends the workflow **
551 *** approval process. **
552 ****************************************************************************************/
553
554 PROCEDURE NO_RESPONSE (
555 p_itemtype IN VARCHAR2,
556 p_itemkey IN VARCHAR2,
557 p_actid IN NUMBER,
558 p_funcmode IN VARCHAR2,
559 p_resultout OUT NOCOPY VARCHAR2) IS
560 lSpecId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_ID');
561 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
562 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
563 api_ret_status VARCHAR2(1);
564 api_err_mesg VARCHAR2(240);
565 BEGIN
566 IF p_funcmode = 'RUN' THEN
567 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_SPECIFICATIONS_B'
568 , p_id => lSpecId
569 , p_source_status => lStartStatus
570 , p_target_status => lTargetStatus
571 , p_mode => 'S'
572 , x_return_status => api_ret_status
573 , x_message => api_err_mesg );
574 IF api_ret_status <> 'S' THEN
575 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
576 raise APPLICATION_ERROR;
577 END IF;
578 END IF;
579 END NO_RESPONSE;
580
581 /****************************************************************************************
582 *** This procedure is associated with GMDQSPAP_NOTI_REWORK activity of the workflow **
583 *** When approver rejects status change request procedure sets spec status to **
584 *** rework status and ends the workflow approval process. **
585 ****************************************************************************************/
586
587
588 PROCEDURE REQ_REJECTED (
589 p_itemtype IN VARCHAR2,
590 p_itemkey IN VARCHAR2,
591 p_actid IN NUMBER,
592 p_funcmode IN VARCHAR2,
593 p_resultout OUT NOCOPY VARCHAR2) IS
594 applicationId number :=552;
595 transactionType varchar2(50) := 'oracle.apps.gmd.qm.spec.sts';
596 nextApprover ame_util.approverRecord;
597 lSpecId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_ID');
598 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
599 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
600 l_userID VARCHAR2(100) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'USER_ID');
601 new_user_id VARCHAR2(100);
602 api_ret_status VARCHAR2(1);
603 api_err_mesg VARCHAR2(240);
604 BEGIN
605 IF p_funcmode = 'RUN' THEN
606
607 --
608 -- Update Approver action
609 --
610 ame_api.getNextApprover(applicationIdIn => applicationId,
611 transactionIdIn => lSpecId,
612 transactionTypeIn => transactionType,
613 nextApproverOut => nextApprover);
614 IF nextApprover.person_id IS NOT NULL THEN
615 --
616 -- if we got HR Person then we have to find corresponding FND USER
617 -- assumption here is all HR user configured in AME will have
618 -- corresponding FND USER
619 --
620 new_user_id := ame_util.personIdToUserId(nextApprover.person_id);
621 ELSE
625 nextApprover.approval_status := ame_util.rejectStatus;
622 new_user_id := nextApprover.user_id;
623 END IF;
624 IF new_user_id = l_userID THEN
626 ame_api.updateApprovalStatus(applicationIdIn => applicationId,
627 transactionIdIn => lSpecId,
628 transactionTypeIn => transactionType,
632 , p_id => lSpecId
629 ApproverIn => nextApprover);
630 END IF;
631 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_SPECIFICATIONS_B'
633 , p_source_status => lStartStatus
634 , p_target_status => lTargetStatus
635 , p_mode => 'R'
636 , x_return_status => api_ret_status
637 , x_message => api_err_mesg );
638 IF api_ret_status <> 'S' THEN
639 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
640 raise APPLICATION_ERROR;
641 END IF;
642 END IF;
643
644 END REQ_REJECTED;
645
646 /****************************************************************************************
647 *** This procedure is associated with GMDQSPAP_NOTI_APPROVED activity of the workflow **
648 *** When approver approves status change request procedure sets AME Approver status **
649 *** to approved status and continues with approval process to verify any more **
650 *** approvals required **
651 ****************************************************************************************/
652
653
654 PROCEDURE REQ_APPROVED (
655 p_itemtype IN VARCHAR2,
656 p_itemkey IN VARCHAR2,
657 p_actid IN NUMBER,
658 p_funcmode IN VARCHAR2,
659 p_resultout OUT NOCOPY VARCHAR2) IS
660 applicationId number :=552;
661 transactionType varchar2(50) := 'oracle.apps.gmd.qm.spec.sts';
662 nextApprover ame_util.approverRecord;
663 lSpecId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_ID');
664 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
665 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
666 l_userID VARCHAR2(100) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'USER_ID');
667 new_user_id VARCHAR2(100);
668 api_ret_status VARCHAR2(1);
669 api_err_mesg VARCHAR2(240);
670 BEGIN
671 IF p_funcmode = 'RUN' THEN
672 --
673 --
674 -- Update Approver action
675 --
676 ame_api.getNextApprover(applicationIdIn => applicationId,
677 transactionIdIn => lSpecId,
678 transactionTypeIn => transactionType,
679 nextApproverOut => nextApprover);
680 IF nextApprover.person_id IS NOT NULL THEN
681 --
682 -- if we got HR Person then we have to find corresponding FND USER
683 -- assumption here is all HR user configured in AME will have
684 -- corresponding FND USER
685 --
686 new_user_id := ame_util.personIdToUserId(nextApprover.person_id);
687 ELSE
688 new_user_id := nextApprover.user_id;
689 END IF;
690 IF new_user_id = l_userID THEN
691 nextApprover.approval_status := ame_util.approvedStatus;
692 ame_api.updateApprovalStatus(applicationIdIn => applicationId,
693 transactionIdIn => lSpecId,
694 transactionTypeIn => transactionType,
695 ApproverIn => nextApprover);
696 END IF;
697
698 END IF;
699
700 END REQ_APPROVED;
701
702 /**************************************************************************************
703 *** Following procedure accepts Status Code and entity type and resolves to Meaning **
704 **************************************************************************************/
705
706 FUNCTION GET_STATUS_MEANING(P_STATUS_CODE NUMBER,
707 P_ENTITY_TYPE VARCHAR2) RETURN VARCHAR2 IS
708 CURSOR GET_STAT_MEANING IS
709 SELECT MEANING
710 FROM GMD_QC_STATUS
711 WHERE STATUS_CODE = P_STATUS_CODE
712 AND ENTITY_TYPE = P_ENTITY_TYPE;
713 l_status_meaning GMD_QC_STATUS.MEANING%TYPE;
714 BEGIN
715 OPEN GET_STAT_MEANING;
716 FETCH GET_STAT_MEANING INTO l_status_meaning;
717 CLOSE GET_STAT_MEANING;
718 RETURN l_status_meaning;
719 END;
720
721 /**************************************************************************************
722 *** Following procedure is to raise Spec approval business event **
723 **************************************************************************************/
724
725 PROCEDURE RAISE_SPEC_APPR_EVENT(p_SPEC_ID NUMBER,
726 p_START_STATUS NUMBER,
727 p_TARGET_STATUS NUMBER) IS
728 l_parameter_list wf_parameter_list_t :=wf_parameter_list_t( );
729 l_event_name VARCHAR2(80) := 'oracle.apps.gmd.qm.spec.sts';
730 BEGIN
731 wf_log_pkg.wf_debug_flag:=TRUE;
732 wf_event.AddParameterToList('SPEC_ID', p_SPEC_ID,l_parameter_list);
733 wf_event.AddParameterToList('START_STATUS',p_START_STATUS ,l_parameter_list);
734 wf_event.AddParameterToList('TARGET_STATUS',p_TARGET_STATUS ,l_parameter_list);
735 wf_event.raise(p_event_name => L_event_name,
736 p_event_key => p_SPEC_ID,
737 p_parameters => l_parameter_list);
738 l_parameter_list.DELETE;
739 END;
740 END GMDQSPEC_APPROVAL_WF_PKG;