[Home] [Help]
PACKAGE BODY: APPS.GMDQSPEC_APPROVAL_WF_PKG
Source
1 PACKAGE BODY GMDQSPEC_APPROVAL_WF_PKG AS
2 /* $Header: GMDQSAPB.pls 120.6 2011/03/18 19:38:24 plowe ship $ */
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
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;
319 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'BASE_SPEC', l_base_spec_name);
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
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');
414 BEGIN
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)|| replaced by below line for Bug 11874618 - 12.2 TECHNOLOGY PROJECT ONLINE PATCHING
418 l_mesg_comment := l_mesg_comment||wf_core.newline||l_performer||' : '||FND_DATE.DATE_TO_CHARDT(DATEVAL => SYSDATE, CALENDAR_AWARE => 2)||
419 wf_core.newline||l_comment;
420 l_comment := null;
421 EXCEPTION WHEN OTHERS THEN
422 NULL;
423 END;
424
425 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
426 itemkey => p_itemkey,
427 aname => 'GMDQSPAP_DISP_COMMENT',
428 avalue => l_mesg_comment);
429 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
430 itemkey => p_itemkey,
431 aname => 'GMDQSPAP_COMMENT',
432 avalue => l_comment);
433 END IF;
434 END APPEND_COMMENTS;
435
436 /***************************************************************************************
437 *** This procedure is associated with VERIFY_ANY_MORE_APPR activity of the workflow **
438 *** once current approver approves status change request this procedure call AME API **
439 *** to verify any more approvers need to approve this request. if it needs some more **
440 *** approvals then it sets approver info to workflow attrbute. now workflow moves to **
441 *** next approval processing. this will continue either all approves approves the **
442 *** request or any one of the rejects. if all approvals are complete then it sets **
443 *** spec status to target status **
444 ***************************************************************************************/
445
446
447 PROCEDURE ANY_MORE_APPROVERS (
448 p_itemtype IN VARCHAR2,
449 p_itemkey IN VARCHAR2,
450 p_actid IN NUMBER,
451 p_funcmode IN VARCHAR2,
452 p_resultout OUT NOCOPY VARCHAR2) IS
453 applicationId number :=552;
454 transactionType varchar2(50) := 'oracle.apps.gmd.qm.spec.sts';
455 nextApprover ame_util.approverRecord;
456 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
457 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
458 lSpecId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_ID');
459 l_userID integer;
460 l_userName FND_USER.USER_NAME%TYPE;
461 api_ret_status VARCHAR2(1);
462 api_err_mesg VARCHAR2(240);
463 BEGIN
464 IF p_funcmode = 'RUN' THEN
465 --
466 -- Get the next approver who need to approve the trasaction
467 --
468 ame_api.getNextApprover(applicationIdIn => applicationId,
469 transactionIdIn => lSpecId,
470 transactionTypeIn => transactionType,
471 nextApproverOut => nextApprover);
472
473 IF nextApprover.user_id IS NULL and nextApprover.person_id IS NULL
474 THEN
475 --
476 -- All Approvers are approved.
477 -- change status of the object to target status
478 --
479
480 IF (l_debug = 'Y') THEN
481 gmd_debug.put_line('Finished approvers; changing status');
482 gmd_debug.put_line('Final Status ' || lTargetStatus);
483 END IF;
484
485 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_SPECIFICATIONS_B'
486 , p_id => lSpecId
487 , p_source_status => lStartStatus
488 , p_target_status => lTargetStatus
489 , p_mode => 'A'
490 , x_return_status => api_ret_status
491 , x_message => api_err_mesg );
492 IF api_ret_status <> 'S' THEN
493 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
494 raise APPLICATION_ERROR;
495 END IF;
496 p_resultout := 'COMPLETE:N';
497 ELSE
498 IF nextApprover.person_id IS NOT NULL THEN
499 --
500 -- if we got HR Person then we have to find corresponding FND USER
501 -- assumption here is all HR user configured in AME will have
502 -- corresponding FND USER
503 --
504 l_userID := ame_util.personIdToUserId(nextApprover.person_id);
505 ELSE
506 l_userID := nextApprover.user_id;
507 END IF;
508 l_userName := GET_FND_USER_NAME(l_userId);
509 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'USER_ID',l_userID);
510 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'APPROVER',l_userName);
511 p_resultout := 'COMPLETE:Y';
512 END IF;
513 END IF;
514 END ANY_MORE_APPROVERS;
515
516 /*************************************************************************************
517 *** Following procedure is to verify any reminder is required when workflow timeout**
518 *** occurs **
519 *************************************************************************************/
520
521 PROCEDURE REMINDAR_CHECK (
522 p_itemtype IN VARCHAR2,
523 p_itemkey IN VARCHAR2,
524 p_actid IN NUMBER,
525 p_funcmode IN VARCHAR2,
526 p_resultout OUT NOCOPY VARCHAR2) IS
527 l_mesg_cnt number:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDQSPAP_MESG_CNT');
528 l_approver VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'APPROVER');
529 BEGIN
530 IF (p_funcmode = 'TIMEOUT') THEN
531 l_mesg_cnt := l_mesg_cnt + 1;
532 IF l_mesg_cnt <= 4 THEN
533 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
534 aname => 'GMDQSPAP_MESG_CNT',
535 avalue => l_mesg_cnt);
536 ELSE
537 p_resultout := 'COMPLETE:DEFAULT';
538 END IF;
539 ELSIF (p_funcmode = 'RESPOND') THEN
540 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
541 itemkey => p_itemkey,
542 aname => 'GMDQSPAP_CURR_PERFORMER',
543 avalue => l_approver);
544 END IF;
545 END;
546
547
548 /****************************************************************************************
549 *** This procedure is associated with GMDQSPAP_NOTI_NOT_RESP activity of the workflow **
550 *** When approver fails to respond to notification defined in GMD: Workflow timeout **
551 *** profile this procedure sets spec status to start status and ends the workflow **
552 *** approval process. **
553 ****************************************************************************************/
554
555 PROCEDURE NO_RESPONSE (
556 p_itemtype IN VARCHAR2,
557 p_itemkey IN VARCHAR2,
558 p_actid IN NUMBER,
559 p_funcmode IN VARCHAR2,
560 p_resultout OUT NOCOPY VARCHAR2) IS
561 lSpecId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_ID');
562 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
563 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
564 api_ret_status VARCHAR2(1);
565 api_err_mesg VARCHAR2(240);
566 BEGIN
567 IF p_funcmode = 'RUN' THEN
568 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_SPECIFICATIONS_B'
569 , p_id => lSpecId
570 , p_source_status => lStartStatus
571 , p_target_status => lTargetStatus
572 , p_mode => 'S'
573 , x_return_status => api_ret_status
574 , x_message => api_err_mesg );
575 IF api_ret_status <> 'S' THEN
576 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
577 raise APPLICATION_ERROR;
578 END IF;
579 END IF;
580 END NO_RESPONSE;
581
582 /****************************************************************************************
583 *** This procedure is associated with GMDQSPAP_NOTI_REWORK activity of the workflow **
584 *** When approver rejects status change request procedure sets spec status to **
585 *** rework status and ends the workflow approval process. **
586 ****************************************************************************************/
587
588
589 PROCEDURE REQ_REJECTED (
590 p_itemtype IN VARCHAR2,
591 p_itemkey IN VARCHAR2,
592 p_actid IN NUMBER,
593 p_funcmode IN VARCHAR2,
594 p_resultout OUT NOCOPY VARCHAR2) IS
595 applicationId number :=552;
596 transactionType varchar2(50) := 'oracle.apps.gmd.qm.spec.sts';
597 nextApprover ame_util.approverRecord;
598 lSpecId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_ID');
599 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
600 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
601 l_userID VARCHAR2(100) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'USER_ID');
602 new_user_id VARCHAR2(100);
603 api_ret_status VARCHAR2(1);
604 api_err_mesg VARCHAR2(240);
605 BEGIN
606 IF p_funcmode = 'RUN' THEN
607
608 --
609 -- Update Approver action
610 --
611 ame_api.getNextApprover(applicationIdIn => applicationId,
612 transactionIdIn => lSpecId,
613 transactionTypeIn => transactionType,
614 nextApproverOut => nextApprover);
615 IF nextApprover.person_id IS NOT NULL THEN
616 --
617 -- if we got HR Person then we have to find corresponding FND USER
618 -- assumption here is all HR user configured in AME will have
619 -- corresponding FND USER
620 --
621 new_user_id := ame_util.personIdToUserId(nextApprover.person_id);
622 ELSE
623 new_user_id := nextApprover.user_id;
624 END IF;
625 IF new_user_id = l_userID THEN
626 nextApprover.approval_status := ame_util.rejectStatus;
627 ame_api.updateApprovalStatus(applicationIdIn => applicationId,
628 transactionIdIn => lSpecId,
629 transactionTypeIn => transactionType,
630 ApproverIn => nextApprover);
631 END IF;
632 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_SPECIFICATIONS_B'
633 , p_id => lSpecId
634 , p_source_status => lStartStatus
635 , p_target_status => lTargetStatus
636 , p_mode => 'R'
637 , x_return_status => api_ret_status
638 , x_message => api_err_mesg );
639 IF api_ret_status <> 'S' THEN
640 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
641 raise APPLICATION_ERROR;
642 END IF;
643 END IF;
644
645 END REQ_REJECTED;
646
647 /****************************************************************************************
648 *** This procedure is associated with GMDQSPAP_NOTI_APPROVED activity of the workflow **
649 *** When approver approves status change request procedure sets AME Approver status **
650 *** to approved status and continues with approval process to verify any more **
651 *** approvals required **
652 ****************************************************************************************/
653
654
655 PROCEDURE REQ_APPROVED (
656 p_itemtype IN VARCHAR2,
657 p_itemkey IN VARCHAR2,
658 p_actid IN NUMBER,
659 p_funcmode IN VARCHAR2,
660 p_resultout OUT NOCOPY VARCHAR2) IS
661 applicationId number :=552;
662 transactionType varchar2(50) := 'oracle.apps.gmd.qm.spec.sts';
663 nextApprover ame_util.approverRecord;
664 lSpecId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_ID');
665 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
666 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
667 l_userID VARCHAR2(100) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'USER_ID');
668 new_user_id VARCHAR2(100);
669 api_ret_status VARCHAR2(1);
670 api_err_mesg VARCHAR2(240);
671 BEGIN
672 IF p_funcmode = 'RUN' THEN
673 --
674 --
675 -- Update Approver action
676 --
677 ame_api.getNextApprover(applicationIdIn => applicationId,
678 transactionIdIn => lSpecId,
679 transactionTypeIn => transactionType,
680 nextApproverOut => nextApprover);
681 IF nextApprover.person_id IS NOT NULL THEN
682 --
683 -- if we got HR Person then we have to find corresponding FND USER
684 -- assumption here is all HR user configured in AME will have
685 -- corresponding FND USER
686 --
687 new_user_id := ame_util.personIdToUserId(nextApprover.person_id);
688 ELSE
689 new_user_id := nextApprover.user_id;
690 END IF;
691 IF new_user_id = l_userID THEN
692 nextApprover.approval_status := ame_util.approvedStatus;
693 ame_api.updateApprovalStatus(applicationIdIn => applicationId,
694 transactionIdIn => lSpecId,
695 transactionTypeIn => transactionType,
696 ApproverIn => nextApprover);
697 END IF;
698
699 END IF;
700
701 END REQ_APPROVED;
702
703 /**************************************************************************************
704 *** Following procedure accepts Status Code and entity type and resolves to Meaning **
705 **************************************************************************************/
706
707 FUNCTION GET_STATUS_MEANING(P_STATUS_CODE NUMBER,
708 P_ENTITY_TYPE VARCHAR2) RETURN VARCHAR2 IS
709 CURSOR GET_STAT_MEANING IS
710 SELECT MEANING
711 FROM GMD_QC_STATUS
712 WHERE STATUS_CODE = P_STATUS_CODE
713 AND ENTITY_TYPE = P_ENTITY_TYPE;
714 l_status_meaning GMD_QC_STATUS.MEANING%TYPE;
715 BEGIN
716 OPEN GET_STAT_MEANING;
717 FETCH GET_STAT_MEANING INTO l_status_meaning;
718 CLOSE GET_STAT_MEANING;
719 RETURN l_status_meaning;
720 END;
721
722 /**************************************************************************************
723 *** Following procedure is to raise Spec approval business event **
724 **************************************************************************************/
725
726 PROCEDURE RAISE_SPEC_APPR_EVENT(p_SPEC_ID NUMBER,
727 p_START_STATUS NUMBER,
728 p_TARGET_STATUS NUMBER) IS
729 l_parameter_list wf_parameter_list_t :=wf_parameter_list_t( );
730 l_event_name VARCHAR2(80) := 'oracle.apps.gmd.qm.spec.sts';
731 BEGIN
732 wf_log_pkg.wf_debug_flag:=TRUE;
733 wf_event.AddParameterToList('SPEC_ID', p_SPEC_ID,l_parameter_list);
734 wf_event.AddParameterToList('START_STATUS',p_START_STATUS ,l_parameter_list);
735 wf_event.AddParameterToList('TARGET_STATUS',p_TARGET_STATUS ,l_parameter_list);
736 wf_event.raise(p_event_name => L_event_name,
737 p_event_key => p_SPEC_ID,
738 p_parameters => l_parameter_list);
739 l_parameter_list.DELETE;
740 END;
741 END GMDQSPEC_APPROVAL_WF_PKG;