[Home] [Help]
PACKAGE BODY: APPS.GMDQSVRS_APPROVAL_WF_PKG
Source
1 PACKAGE BODY GMDQSVRS_APPROVAL_WF_PKG AS
2 /* $Header: GMDQSVRB.pls 120.4 2011/03/18 19:45:59 plowe ship $ */
3
4
5 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7
8 APPLICATION_ERROR EXCEPTION;
9 -- Following function accepts FND userId and returns
10 -- User name
11 FUNCTION GET_FND_USER_NAME( userId Integer) RETURN VARCHAR2 IS
12 CURSOR GET_USER_NAME IS
13 SELECT USER_NAME
14 FROM FND_USER
15 WHERE USER_ID = userId;
16 l_userName FND_USER.USER_NAME%TYPE;
17 BEGIN
18 OPEN GET_USER_NAME;
19 FETCH GET_USER_NAME INTO l_userName;
20 CLOSE GET_USER_NAME;
21 RETURN l_userName;
22 END GET_FND_USER_NAME;
23
24 /********************************************************************************
25 *** This procedure is associated with GMDQSVRS_ISAPROVAL_REQUIRED workflow. **
26 *** This code will execute when Spec Validity Rule Approval Business Event **
27 *** is raised. This verfifies whether approval required for this transaction**
28 *** or not. If approval is required then udated spec status to pending as **
29 *** defined GMD_QC_STATUS_NEXT and populates workflow attributes **
30 ********************************************************************************/
31
32 PROCEDURE IS_APPROVAL_REQ (
33 p_itemtype IN VARCHAR2,
34 p_itemkey IN VARCHAR2,
35 p_actid IN NUMBER,
36 p_funcmode IN VARCHAR2,
37 p_resultout OUT NOCOPY VARCHAR2) IS
38 applicationId number :=552;
39 transactionType varchar2(50) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'EVENT_NAME');
40 l_TABLE_NAME varchar2(50) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TABLE_NAME');
41 nextApprover ame_util.approverRecord;
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 lSpecVRId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_VR_ID');
47 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
48 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
49 l_wf_timeout NUMBER := TO_NUMBER(FND_PROFILE.VALUE ('GMD_WF_TIMEOUT'));
50 lStartStatus_DESC VARCHAR2(240);
51 lTargetStatus_DESC VARCHAR2(240);
52 api_ret_status VARCHAR2(1);
53 api_err_mesg VARCHAR2(240);
54 l_spec_type varchar2(10);
55
56 /*====================================================
57 BUG#491207 Replaced call to gmd_all_spec_vrs view
58 with the following cursor.
59 ====================================================*/
60
61 cursor get_spec_type is
62 SELECT 'I' spec_type
63 FROM GMD_INVENTORY_SPEC_VRS v
64 WHERE v.spec_vr_id = lSpecVRId
65 UNION
66 SELECT 'W' spec_type
67 FROM GMD_WIP_SPEC_VRS V
68 WHERE v.spec_vr_id = lSpecVRId
69 UNION
70 SELECT 'C' spec_type
71 FROM GMD_CUSTOMER_SPEC_VRS V
72 WHERE v.spec_vr_id = lSpecVRId
73 UNION
74 SELECT 'S' spec_type
75 FROM GMD_SUPPLIER_SPEC_VRS V
76 WHERE v.spec_vr_id = lSpecVRId
77 UNION
78 SELECT v.rule_type spec_type
79 FROM GMD_MONITORING_SPEC_VRS V
80 WHERE v.spec_vr_id = lSpecVRId
81 UNION
82 SELECT 'T' spec_type
83 FROM GMD_STABILITY_SPEC_VRS V
84 WHERE v.spec_vr_id = lSpecVRId;
85
86
87 /*=======================================
88 BUG#4912074 - Replaced get_disp_Attr
89 for performance and added subsequent
90 queried to get additional data.
91 ======================================*/
92
93 cursor get_disp_Attr IS
94 SELECT v.spec_vr_id, s.spec_name , s.spec_vers, 'I' spec_type,
95 v.organization_id,
96 p.description spec_status_desc,
97 t.description spec_vr_status_desc,
98 v.start_date, v.end_date,
99 s.revision,
100 s.grade_code grade_code,
101 NULL resources,
102 to_number(NULL) resource_instance_id,
103 v.last_updated_by ,
104 s.inventory_item_id,
105 v.subinventory, v.locator_id
106 FROM GMD_INVENTORY_SPEC_VRS v ,
107 GMD_SPECIFICATIONS_B s,
108 GMD_QC_STATUS_TL p,
109 GMD_QC_STATUS_TL t
110 WHERE V.SPEC_ID = S.SPEC_ID
111 AND s.spec_status = p.status_code
112 AND p.entity_type = 'S'
113 AND p.language = USERENV('LANG')
114 AND v.spec_vr_status = t.status_code
115 AND t.entity_type = 'S'
116 AND t.language = USERENV('LANG')
117 AND v.spec_vr_id = lSpecVRId
118 UNION
119 SELECT v.spec_vr_id, s.spec_name , s.spec_vers, 'W' spec_type,
120 v.organization_id,
121 p.description spec_status_desc,
122 t.description spec_vr_status_desc,
123 v.start_date, v.end_date,
124 s.revision,
125 s.grade_code grade_code,
126 NULL resources,
127 to_number(NULL) resource_instance_id,
128 v.last_updated_by ,
129 s.inventory_item_id,
130 NULL subinventory, NULL locator_id
131 FROM GMD_WIP_SPEC_VRS V ,
132 GMD_SPECIFICATIONS_B S,
133 GMD_QC_STATUS_TL p,
134 GMD_QC_STATUS_TL t
135 WHERE V.SPEC_ID = S.SPEC_ID
136 AND s.spec_status = p.status_code
137 AND p.entity_type = 'S'
138 AND p.language = USERENV('LANG')
139 AND v.spec_vr_status = t.status_code
140 AND t.entity_type = 'S'
141 AND t.language = USERENV('LANG')
142 AND v.spec_vr_id = lSpecVRId
143 UNION
144 SELECT v.spec_vr_id, s.spec_name , s.spec_vers, 'C' spec_type,
145 v.organization_id,
146 p.description spec_status_desc,
147 t.description spec_vr_status_desc,
148 v.start_date, v.end_date,
149 s.revision,
150 s.grade_code grade_code,
151 NULL resources,
152 to_number(NULL) resource_instance_id,
153 v.last_updated_by ,
154 s.inventory_item_id,
155 NULL subinventory, NULL locator_id
156 FROM GMD_CUSTOMER_SPEC_VRS V ,
157 GMD_SPECIFICATIONS_B S,
158 GMD_QC_STATUS_TL p,
159 GMD_QC_STATUS_TL t
160 WHERE V.SPEC_ID = S.SPEC_ID
161 AND s.spec_status = p.status_code
162 AND p.entity_type = 'S'
163 AND p.language = USERENV('LANG')
164 AND v.spec_vr_status = t.status_code
165 AND t.entity_type = 'S'
166 AND t.language = USERENV('LANG')
167 AND v.spec_vr_id = lSpecVRId
168 UNION
169 SELECT v.spec_vr_id, s.spec_name , s.spec_vers, 'S' spec_type,
170 v.organization_id,
171 p.description spec_status_desc,
172 t.description spec_vr_status_desc,
173 v.start_date, v.end_date,
174 s.revision,
175 s.grade_code grade_code,
176 NULL resources,
177 to_number(NULL) resource_instance_id,
178 v.last_updated_by ,
179 s.inventory_item_id,
180 NULL subinventory, NULL locator_id
181 FROM GMD_SUPPLIER_SPEC_VRS V ,
182 GMD_SPECIFICATIONS_B S,
183 GMD_QC_STATUS_TL p,
184 GMD_QC_STATUS_TL t
185 WHERE V.SPEC_ID = S.SPEC_ID
186 AND s.spec_status = p.status_code
187 AND p.entity_type = 'S'
188 AND p.language = USERENV('LANG')
189 AND v.spec_vr_status = t.status_code
190 AND t.entity_type = 'S'
191 AND t.language = USERENV('LANG')
192 AND v.spec_vr_id = lSpecVRId
193 UNION
194 SELECT v.spec_vr_id, s.spec_name , s.spec_vers, v.rule_type spec_type,
195 decode(rule_type,'R',v.resource_organization_id,'L',v.locator_organization_id,TO_NUMBER(NULL)) organization_id,
196 p.description spec_status_desc,
197 t.description spec_vr_status_desc,
198 v.start_date, v.end_date,
199 s.revision,
200 s.grade_code grade_code,
201 resources,
202 resource_instance_id,
203 v.last_updated_by ,
204 TO_NUMBER(NULL),
205 v.subinventory, v.locator_id
206 FROM GMD_MONITORING_SPEC_VRS V ,
207 GMD_SPECIFICATIONS_B S,
208 GMD_QC_STATUS_TL p,
209 GMD_QC_STATUS_TL t
210 WHERE V.SPEC_ID = S.SPEC_ID
211 AND s.spec_status = p.status_code
212 AND p.entity_type = 'S'
213 AND p.language = USERENV('LANG')
214 AND v.spec_vr_status = t.status_code
215 AND t.entity_type = 'S'
216 AND t.language = USERENV('LANG')
217 AND v.spec_vr_id = lSpecVRId
218 UNION
219 SELECT v.spec_vr_id, s.spec_name , s.spec_vers, 'T' spec_type,
220 NULL,
221 p.description spec_status_desc,
222 t.description spec_vr_status_desc,
223 v.start_date, v.end_date,
224 s.revision,
225 s.grade_code grade_code,
226 NULL resources,
227 to_number(NULL) resource_instance_id,
228 v.last_updated_by ,
229 TO_NUMBER(NULL),
230 NULL subinventory, NULL locator_id
231 FROM GMD_STABILITY_SPEC_VRS V ,
232 GMD_SPECIFICATIONS_B S,
233 GMD_QC_STATUS_TL p,
234 GMD_QC_STATUS_TL t
235 WHERE V.SPEC_ID = S.SPEC_ID
236 AND s.spec_status = p.status_code
237 AND p.entity_type = 'S'
238 AND p.language = USERENV('LANG')
239 AND v.spec_vr_status = t.status_code
240 AND t.entity_type = 'S'
241 AND t.language = USERENV('LANG')
242 AND v.spec_vr_id = lSpecVRId;
243
244 disp_attr_rec get_disp_Attr%ROWTYPE;
245
246 /*================================================
247 BUG#4912074 - Cursors to get additional data.
248 ================================================*/
249
250 CURSOR get_org_data (v_org_id NUMBER) IS
251 SELECT a.organization_code, hou.name organization_name
252 FROM mtl_parameters a, hr_all_organization_units hou
253 WHERE
254 a.organization_id = v_org_id
255 AND a.organization_id = hou.organization_id;
256
257 l_orgn_code mtl_parameters.organization_code%TYPE;
258 l_orgn_name hr_all_organization_units.name%TYPE;
259
260
261 CURSOR get_item_data (v_itemorg NUMBER, v_item_id NUMBER) IS
262 select concatenated_segments item_number, description item_description
263 FROM mtl_system_items_kfv
264 WHERE
265 organization_id = v_itemorg
266 AND inventory_item_id = v_item_id;
267
268 l_item_number mtl_system_items_kfv.concatenated_segments%TYPE;
269 l_item_desc mtl_system_items_kfv.description%TYPE;
270
271 CURSOR get_meaning (v_code VARCHAR2) IS
272 SELECT meaning
273 FROM gem_lookups
274 WHERE lookup_type = 'GMD_ERES_SOURCE'
275 AND lookup_code = v_code;
276
277 l_lookup_code gem_lookups.meaning%TYPE;
278
279 CURSOR get_location (v_loc_id NUMBER) IS
280 SELECT concatenated_segments loc
281 FROM mtl_item_locations_kfv
282 WHERE inventory_location_id = v_loc_id;
283
284 l_location mtl_item_locations_kfv.concatenated_segments%TYPE;
285
286 /*==============================================
287 BUG#4912074 Replaced get_mont_disp_Attr
288 using view gmd_all_spec_vrs for efficiency.
289 ==============================================*/
290
291 cursor get_mont_disp_Attr IS
292 SELECT s.spec_name, s.spec_vers, v.rule_type spec_type,
293 t.description spec_vr_status_desc,
294 p.description spec_status_desc,
295 v.start_date, v.end_date,
296 resources,
297 resource_instance_id,
298 v.last_updated_by ,
299 v.subinventory SUBINV,
300 src_type.meaning,
301 locations.concatenated_segments LOC,
302 a.organization_code,
303 hou.name organization_name
304 FROM GMD_MONITORING_SPEC_VRS V ,
305 GMD_SPECIFICATIONS_B S,
306 GMD_QC_STATUS_TL p,
307 GMD_QC_STATUS_TL t,
308 GEM_LOOKUPS src_type ,
309 MTL_ITEM_LOCATIONS_KFV locations,
310 MTL_PARAMETERS a,
311 HR_ALL_ORGANIZATION_UNITS hou
312 WHERE V.SPEC_ID = S.SPEC_ID
313 AND s.spec_status = p.status_code
314 AND p.entity_type = 'S'
315 AND p.language = USERENV('LANG')
316 AND v.spec_vr_status = t.status_code
317 AND t.entity_type = 'S'
318 AND t.language = USERENV('LANG')
319 AND src_type.lookup_type(+) = 'GMD_ERES_SOURCE'
320 AND src_type.lookup_code(+) = spec_type
321 AND locations.inventory_location_id(+) = v.locator_id
322 AND a.organization_id(+) =
323 decode(rule_type,'R',v.resource_organization_id,'L',v.locator_organization_id,TO_NUMBER(NULL))
324 AND a.organization_id = hou.organization_id
325 AND v.spec_vr_id = lSpecVRid;
326
327 mont_disp_attr_rec get_mont_disp_Attr%ROWTYPE;
328
329 -- INVCONV, NSRIVAST, END
330
331 cursor get_from_role is
332 select nvl( text, '')
333 from wf_Resources where name = 'WF_ADMIN_ROLE'
334 and language = userenv('LANG') ;
335
336 l_from_role varchar2(2000);
337
338
339 begin
340
341
342 IF (l_debug = 'Y') THEN
343 gmd_debug.log_initialize('SpecVRApp');
344 gmd_debug.put_line('Spec VR Id ' || lSpecVRId );
345 gmd_debug.put_line('Start Status ' || lStartStatus);
346 gmd_debug.put_line('Target Status ' || lTargetStatus);
347 END IF;
348
349 open get_from_role ;
350 fetch get_from_role into l_from_role ;
351 close get_from_role ;
352
353
354 IF p_funcmode = 'RUN' THEN
355 /* Find out which Spec type we are dealing with: item or monitor */
356 open get_spec_type;
357 fetch get_spec_type into l_spec_type;
358 close get_spec_type;
359
360
361 --
362 -- clear All Approvals from AME
363 -- following API removes previous instance of approval group from AME tables
364 --
365 ame_api.clearAllApprovals(applicationIdIn => applicationId,
366 transactionIdIn => lSpecVRId,
367 transactionTypeIn => transactionType);
368 --
369 -- Get the next approver who need to approve the trasaction
370 --
371
372 IF (l_debug = 'Y') THEN
373 gmd_debug.put_line('Getting approver ');
374 END IF;
375
376 ame_api.getNextApprover(applicationIdIn => applicationId,
377 transactionIdIn => lSpecVRId,
378 transactionTypeIn => transactionType,
379 nextApproverOut => nextApprover);
380
381 IF nextApprover.user_id IS NULL and nextApprover.person_id IS NULL
382 THEN
383
384 IF (l_debug = 'Y') THEN
385 gmd_debug.put_line('No approver required');
386 END IF;
387
388 --
389 -- Means either no AME rule is matching for this transaction ID or Approver list is empty.
390 -- change status of the object to target status
391 --
392 GMD_SPEC_GRP.change_status( p_table_name => l_TABLE_NAME
393 , p_id => lSpecVRId
394 , p_source_status => lStartStatus
395 , p_target_status => lTargetStatus
396 , p_mode => 'A'
397 , x_return_status => api_ret_status
398 , x_message => api_err_mesg );
399
400 IF api_ret_status <> 'S' THEN
401 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
402 raise APPLICATION_ERROR;
403 END IF;
404
405 p_resultout := 'COMPLETE:N';
406
407 ELSE
408 --
409 -- We got the first approver from AME
410 --
411
412 IF (l_debug = 'Y') THEN
413 gmd_debug.put_line('Approver required');
414 END IF;
415
416 IF nextApprover.person_id IS NOT NULL THEN
417 --
418 -- if we got HR Person then we have to find corresponding FND USER
419 -- assumption here is all HR user configured in AME will have
420 -- corresponding FND USER
421 --
422 l_userID := ame_util.personIdToUserId(nextApprover.person_id);
423 ELSE
424 l_userID := nextApprover.user_id;
425 END IF;
426 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'USER_ID',l_userID);
427 l_userName := GET_FND_USER_NAME(l_userId);
428
429 --
430 -- Update status to pending
431 --
432 GMD_SPEC_GRP.change_status( p_table_name => l_TABLE_NAME
433 , p_id => lSpecVRId
434 , p_source_status => lStartStatus
435 , p_target_status => lTargetStatus
436 , p_mode => 'P'
437 , x_return_status => api_ret_status
438 , x_message => api_err_mesg );
439 IF api_ret_status = 'S' THEN
440 -- Get attributes Required for display
441
442
443 IF (l_debug = 'Y') THEN
444 gmd_debug.put_line('Spec Type ' || l_spec_type);
445 END IF;
446
447
448 /*==============================================
449 BUG#4912074 Replaced spec_type of M with
450 R or L.
451 ==============================================*/
452 if (l_spec_type in ('R','L')) then
453 /* This is a monitoring Spec VR */
454 open get_mont_disp_Attr;
455 FETCH get_mont_disp_Attr INTO mont_disp_attr_rec;
456 IF get_mont_disp_Attr%NOTFOUND THEN
457 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,FND_MESSAGE.GET_STRING('GMD','GMD_QC_INVALID_SPEC_VR_ID for Monitoring'));
458 raise APPLICATION_ERROR;
459 END IF;
460
461 l_requester := GET_FND_USER_NAME(mont_disp_attr_rec.LAST_UPDATED_BY);
462 close get_mont_disp_Attr;
463 else
464 /* This is an item spec VR */
465 open get_disp_Attr;
466 FETCH get_disp_Attr INTO disp_attr_rec;
467 IF get_disp_Attr%NOTFOUND THEN
468 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,FND_MESSAGE.GET_STRING('GMD','GMD_QC_INVALID_SPEC_VR_ID'));
469 raise APPLICATION_ERROR;
470 END IF;
471
472 l_requester := GET_FND_USER_NAME(disp_attr_rec.LAST_UPDATED_BY);
473 close get_disp_Attr;
474 end if;
475
476
477 /*====================================================
478 BUG#4912074 - get additional data.
479 ====================================================*/
480
481 IF (disp_attr_rec.organization_id IS NOT NULL) THEN
482 OPEN get_org_data (disp_attr_rec.organization_id);
483 FETCH get_org_data INTO l_orgn_code, l_orgn_name;
484 IF (get_org_data%NOTFOUND) THEN
485 l_orgn_code := NULL;
486 l_orgn_name := NULL;
487 END IF;
488 CLOSE get_org_data;
489 ELSE
490 l_orgn_code := NULL;
491 l_orgn_name := NULL;
492 END IF;
493
494 IF (disp_attr_rec.organization_id IS NOT NULL AND disp_attr_rec.inventory_item_id IS NOT NULL) THEN
495 OPEN get_item_data (disp_attr_rec.organization_id, disp_attr_rec.inventory_item_id);
496 FETCH get_item_data INTO l_item_number, l_item_desc;
497 IF (get_item_data%NOTFOUND) THEN
498 l_item_number := NULL;
499 l_item_desc := NULL;
500 END IF;
501 CLOSE get_item_data;
502 ELSE
503 l_item_number := NULL;
504 l_item_desc := NULL;
505 END IF;
506
507 IF (disp_attr_rec.spec_type IS NOT NULL) THEN
508 OPEN get_meaning (disp_attr_rec.spec_type);
509 FETCH get_meaning INTO l_lookup_code;
510 IF (get_meaning%NOTFOUND) THEN
511 l_lookup_code := NULL;
512 END IF;
513 CLOSE get_meaning;
514 ELSE
515 l_lookup_code := NULL;
516 END IF;
517
518 IF (disp_attr_rec.locator_id IS NOT NULL) THEN
519 OPEN get_location (disp_attr_rec.locator_id);
520 FETCH get_location INTO l_location;
521 IF (get_location%NOTFOUND) THEN
522 l_location := NULL;
523 END IF;
524 CLOSE get_location;
525 ELSE
526 l_location := NULL;
527 END IF;
528
529 lStartStatus_DESC := GMDQSPEC_APPROVAL_WF_PKG.GET_STATUS_MEANING(lStartStatus,'S');
530 lTargetStatus_DESC:= GMDQSPEC_APPROVAL_WF_PKG.GET_STATUS_MEANING(lTargetStatus,'S');
531
532 IF (l_debug = 'Y') THEN
533 gmd_debug.put_line('Setting workflow attributes');
534 END IF;
535
536 /* Depending on whether the Spec VR is for an item or monitor, fill out the
537 tokenized message and set it in the workflow */
538
539 /*==============================================
540 BUG#4912074 Replaced spec_type of M with
541 R or L. Added set of value for attribute
542 RESOURCE.
543 ==============================================*/
544
545 if (l_spec_type in ('R','L')) then
546 /* This is a monitoring Spec VR */
547 FND_MESSAGE.SET_NAME('GMD','GMD_SPEC_APPROVAL_VR_MON');
548 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'RESOURCE',mont_disp_attr_rec.resources);
549 FND_MESSAGE.SET_TOKEN('RESOURCE', mont_disp_attr_rec.RESOURCES);
550 FND_MESSAGE.SET_TOKEN('RESOURCE_INSTANCE', mont_disp_attr_rec.RESOURCE_INSTANCE_ID);
551 FND_MESSAGE.SET_TOKEN('SUBINVENTORY', mont_disp_attr_rec.SUBINV); -- INVCONV, NSRIVAST
552 FND_MESSAGE.SET_TOKEN('LOCATOR', mont_disp_attr_rec.LOC); -- INVCONV, NSRIVAST
553
554
555 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SPEC_NAME',mont_disp_attr_rec.SPEC_NAME);
556 wf_engine.setitemattrnumber(p_itemtype, p_itemkey,'SPEC_VERS',mont_disp_attr_rec.SPEC_VERS);
557 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SPEC_STATUS',mont_disp_attr_rec.SPEC_STATUS_DESC);
558 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SOURCE_TYPE',mont_disp_attr_rec.MEANING);
559 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SPEC_VR_STATUS',mont_disp_attr_rec.SPEC_VR_STATUS_DESC);
560 wf_engine.setitemattrdate(p_itemtype, p_itemkey,'EFFECTIVE_FROM_DATE',mont_disp_attr_rec.START_DATE);
561 wf_engine.setitemattrdate(p_itemtype, p_itemkey,'EFFECTIVE_TO_DATE',mont_disp_attr_rec.END_DATE );
562 --wf_engine.setitemattrtext(p_itemtype, p_itemkey,'OWNER_ORGN_CODE',mont_disp_attr_rec.ORGN_CODE); -- INVCONV, NSRIVAST
563 --wf_engine.setitemattrtext(p_itemtype, p_itemkey,'OWNER_ORGN_NAME',mont_disp_attr_rec.ORGN_NAME); -- INVCONV, NSRIVAST
564 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'OWNER_ORGN_CODE',mont_disp_attr_rec.ORGANIZATION_CODE); -- INVCONV, NSRIVAST
565 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'OWNER_ORGN_NAME',mont_disp_attr_rec.ORGANIZATION_NAME); -- INVCONV, NSRIVAST
566 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'REQUESTER',l_requester);
567 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'START_STATUS_DESC',lStartStatus_DESC);
568 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS_DESC',lTargetStatus_DESC);
569 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'APPROVER',l_userName);
570
571 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SUBINVENTORY',mont_disp_attr_rec.SUBINV); -- INVCONV, NSRIVAST
572 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'LOCATOR',mont_disp_attr_rec.LOC); -- INVCONV, NSRIVAST
573
574
575 FND_MESSAGE.SET_TOKEN('SPEC_NAME', mont_disp_attr_rec.SPEC_NAME);
576 FND_MESSAGE.SET_TOKEN('SPEC_VERS', mont_disp_attr_rec.SPEC_VERS);
577 FND_MESSAGE.SET_TOKEN('SPEC_DESC', mont_disp_attr_rec.SPEC_STATUS_DESC);
578 FND_MESSAGE.SET_TOKEN('SPEC_STATUS', mont_disp_attr_rec.MEANING);
579 --FND_MESSAGE.SET_TOKEN('OWNER_ORGN_CODE', mont_disp_attr_rec.ORGN_CODE); -- INVCONV, NSRIVAST
580 --FND_MESSAGE.SET_TOKEN('OWNER_ORGN_NAME', mont_disp_attr_rec.ORGN_NAME); -- INVCONV, NSRIVAST
581 FND_MESSAGE.SET_TOKEN('OWNER_ORGN_CODE', mont_disp_attr_rec.ORGANIZATION_CODE); -- INVCONV, NSRIVAST
582 FND_MESSAGE.SET_TOKEN('OWNER_ORGN_NAME', mont_disp_attr_rec.ORGANIZATION_NAME ); -- INVCONV, NSRIVAST
583 FND_MESSAGE.SET_TOKEN('REQUESTER', l_requester);
584 FND_MESSAGE.SET_TOKEN('START_STATUS_DESC', lStartStatus_DESC);
585 FND_MESSAGE.SET_TOKEN('TARGET_STATUS_DESC', lTargetStatus_DESC);
586 FND_MESSAGE.SET_TOKEN('APPROVER', l_userName);
587
588 ELSE
589 /* This is an Item Spec VR */
590 FND_MESSAGE.SET_NAME('GMD','GMD_SPEC_APPROVAL_VR_ITEM');
591 /*=================================================
592 BUG#4912074 - Changed source of cursor data.
593 =================================================*/
594 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_number);
595 FND_MESSAGE.SET_TOKEN('ITEM_DESC', l_item_desc);
596 FND_MESSAGE.SET_TOKEN('GRADE', disp_attr_rec.GRADE_CODE); -- INVCONV, NSRIVAST
597 FND_MESSAGE.SET_TOKEN('SUBINVENTORY', disp_attr_rec.subinventory); -- INVCONV, NSRIVAST
598 FND_MESSAGE.SET_TOKEN('LOCATOR', l_location);
599
600
601
602 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,itemkey => p_itemkey,
603 aname => '#FROM_ROLE',
604 avalue => l_userName );
605
606 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SPEC_NAME',disp_attr_rec.SPEC_NAME);
607 wf_engine.setitemattrnumber(p_itemtype, p_itemkey,'SPEC_VERS',disp_attr_rec.SPEC_VERS);
608 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SPEC_STATUS',disp_attr_rec.SPEC_STATUS_DESC);
609 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SOURCE_TYPE',l_lookup_code);
610 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SPEC_VR_STATUS',disp_attr_rec.SPEC_VR_STATUS_DESC);
611 wf_engine.setitemattrdate(p_itemtype, p_itemkey,'EFFECTIVE_FROM_DATE',disp_attr_rec.START_DATE);
612 wf_engine.setitemattrdate(p_itemtype, p_itemkey,'EFFECTIVE_TO_DATE',disp_attr_rec.END_DATE );
613 /*=======================================
614 BUG#4912074 Changed source of data.
615 =======================================*/
616 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'OWNER_ORGN_CODE',l_orgn_code);
617 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'OWNER_ORGN_NAME',l_orgn_name);
618 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'GRADE',disp_attr_rec.GRADE_CODE);
619 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'ITEM_NO',l_item_number);
620 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'ITEM_DESC',l_item_desc);
621
622 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'REQUESTER',l_requester);
623 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'START_STATUS_DESC',lStartStatus_DESC);
624 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS_DESC',lTargetStatus_DESC);
625 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'APPROVER',l_userName);
626
627 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'ITEM_REVISION',disp_attr_rec.REVISION); -- INVCONV, NSRIVAST
628 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'SUBINVENTORY',disp_attr_rec.subinventory); -- INVCONV, NSRIVAST
629 /*=======================================
630 BUG#4912074 Changed source of data.
631 =======================================*/
632 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'OWNER_ORGN_CODE',l_orgn_code);
633 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'LOCATOR',l_location);
634
635
636 FND_MESSAGE.SET_TOKEN('SPEC_NAME', disp_attr_rec.SPEC_NAME);
637 FND_MESSAGE.SET_TOKEN('SPEC_VERS', disp_attr_rec.SPEC_VERS);
638 FND_MESSAGE.SET_TOKEN('SPEC_DESC', disp_attr_rec.SPEC_STATUS_DESC);
639 /*=======================================
640 BUG#4912074 Changed source of data.
641 =======================================*/
642 FND_MESSAGE.SET_TOKEN('SPEC_STATUS', l_lookup_code);
643 FND_MESSAGE.SET_TOKEN('OWNER_ORGN_CODE', l_orgn_code);
644 FND_MESSAGE.SET_TOKEN('OWNER_ORGN_NAME', l_orgn_name);
645 FND_MESSAGE.SET_TOKEN('REQUESTER', l_requester);
646 FND_MESSAGE.SET_TOKEN('START_STATUS_DESC', lStartStatus_DESC);
647 FND_MESSAGE.SET_TOKEN('TARGET_STATUS_DESC', lTargetStatus_DESC);
648 FND_MESSAGE.SET_TOKEN('APPROVER', l_userName);
649
650 END IF;
651
652
653
654 /* Set the message attribute, MSG, in the workflow */
655 FND_MESSAGE.SET_TOKEN('MSG', FND_MESSAGE.GET() );
656
657 l_wf_timeout := (l_wf_timeout * 24 * 60) / 4 ; -- Converting days into minutes
658
659 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
660 aname => 'GMDQSVRS_TIMEOUT',
661 avalue => l_wf_timeout);
662 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
663 aname => 'GMDQSVRS_MESG_CNT',
664 avalue => 1);
665 p_resultout := 'COMPLETE:Y';
666
667 IF (l_debug = 'Y') THEN
668 gmd_debug.put_line('Finished workflow attributes');
669 END IF;
670
671 ELSE
672 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
673 raise APPLICATION_ERROR;
674 END IF;
675 END IF;
676 END IF;
677 EXCEPTION WHEN NO_DATA_FOUND THEN
678 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,'Invalid Spec ID');
679 raise;
680 END IS_APPROVAL_REQ;
681
682
683 /**************************************************************************************
684 *** This procedure is associated with GMDQSVRS_APP_COMMENT activity of the workflow **
685 *** When user enters comments in response to a notification this procedure appends **
686 *** comments to internal variable so that full history can be shoed in notification **
687 *** body. **
688 **************************************************************************************/
689
690 PROCEDURE APPEND_COMMENTS (
691 p_itemtype IN VARCHAR2,
692 p_itemkey IN VARCHAR2,
693 p_actid IN NUMBER,
694 p_funcmode IN VARCHAR2,
695 p_resultout OUT NOCOPY VARCHAR2) IS
696 l_comment VARCHAR2(4000):= wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDQSVRS_COMMENT');
697 l_mesg_comment VARCHAR2(4000):= wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDQSVRS_DISP_COMMENT');
698 l_performer VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'GMDQSVRS_CURR_PERFORMER');
699 BEGIN
700 IF (p_funcmode = 'RUN' AND l_comment IS NOT NULL) THEN
701 BEGIN
702 --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
703 l_mesg_comment := l_mesg_comment||wf_core.newline||l_performer||' : '||FND_DATE.DATE_TO_CHARDT(DATEVAL => SYSDATE, CALENDAR_AWARE => 2)||
704 wf_core.newline||l_comment;
705 l_comment := null;
706 EXCEPTION WHEN OTHERS THEN
707 NULL;
708 END;
709 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
710 itemkey => p_itemkey,
711 aname => 'GMDQSVRS_DISP_COMMENT',
712 avalue => l_mesg_comment);
713 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
714 itemkey => p_itemkey,
715 aname => 'GMDQSVRS_COMMENT',
716 avalue => l_comment);
717 END IF;
718 END APPEND_COMMENTS;
719
720 /***************************************************************************************
721 *** This procedure is associated with VERIFY_ANY_MORE_APPR activity of the workflow **
722 *** once current approver approves status change request this procedure call AME API **
723 *** to verify any more approvers need to approve this request. if it needs some more **
724 *** approvals then it sets approver info to workflow attrbute. now workflow moves to **
725 *** next approval processing. this will continue either all approves approves the **
726 *** request or any one of the rejects. if all approvals are complete then it sets **
727 *** spec validity rule status to target status **
728 ***************************************************************************************/
729
730
731 PROCEDURE ANY_MORE_APPROVERS (
732 p_itemtype IN VARCHAR2,
733 p_itemkey IN VARCHAR2,
734 p_actid IN NUMBER,
735 p_funcmode IN VARCHAR2,
736 p_resultout OUT NOCOPY VARCHAR2) IS
737 applicationId number :=552;
738 transactionType varchar2(50) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'EVENT_NAME');
739 l_TABLE_NAME varchar2(50) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TABLE_NAME');
740 nextApprover ame_util.approverRecord;
741 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
742 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
743 lSpecVRId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_VR_ID');
744 l_userID integer;
745 l_userName FND_USER.USER_NAME%TYPE;
746 api_ret_status VARCHAR2(1);
747 api_err_mesg VARCHAR2(240);
748 BEGIN
749 IF p_funcmode = 'RUN' THEN
750 --
751 -- Get the next approver who need to approve the trasaction
752 --
753 ame_api.getNextApprover(applicationIdIn => applicationId,
754 transactionIdIn => lSpecVRId,
755 transactionTypeIn => transactionType,
756 nextApproverOut => nextApprover);
757
758 IF nextApprover.user_id IS NULL and nextApprover.person_id IS NULL
759 THEN
760 --
761 -- All Approvers are approved.
762 -- change status of the object to target status
763 --
764
765 IF (l_debug = 'Y') THEN
766 gmd_debug.put_line('No more approvers required');
767 END IF;
768
769 GMD_SPEC_GRP.change_status( p_table_name => l_TABLE_NAME
770 , p_id => lSpecVRId
771 , p_source_status => lStartStatus
772 , p_target_status => lTargetStatus
773 , p_mode => 'A'
774 , x_return_status => api_ret_status
775 , x_message => api_err_mesg );
776 IF api_ret_status <> 'S' THEN
777 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
778 raise APPLICATION_ERROR;
779 END IF;
780 p_resultout := 'COMPLETE:N';
781 ELSE
782
783 IF (l_debug = 'Y') THEN
784 gmd_debug.put_line('There is still more approvers required');
785 END IF;
786
787
788 IF nextApprover.person_id IS NOT NULL THEN
789 --
790 -- if we got HR Person then we have to find corresponding FND USER
791 -- assumption here is all HR user configured in AME will have
792 -- corresponding FND USER
793 --
794 l_userID := ame_util.personIdToUserId(nextApprover.person_id);
795 ELSE
796 l_userID := nextApprover.user_id;
797 END IF;
798
799 l_userName := GET_FND_USER_NAME(l_userId);
800 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'USER_ID',l_userID);
801 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'APPROVER',l_userName);
802 p_resultout := 'COMPLETE:Y';
803 END IF;
804 END IF;
805 END ANY_MORE_APPROVERS;
806
807 /*************************************************************************************
808 *** Following procedure is to verify any reminder is required when workflow timeout**
809 *** occurs **
810 *************************************************************************************/
811
812
813 PROCEDURE REMINDAR_CHECK (
814 p_itemtype IN VARCHAR2,
815 p_itemkey IN VARCHAR2,
816 p_actid IN NUMBER,
817 p_funcmode IN VARCHAR2,
818 p_resultout OUT NOCOPY VARCHAR2) IS
819 l_mesg_cnt number:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDQSVRS_MESG_CNT');
820 l_approver VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'APPROVER');
821 BEGIN
822 IF (p_funcmode = 'TIMEOUT') THEN
823 l_mesg_cnt := l_mesg_cnt + 1;
824 IF l_mesg_cnt <= 4 THEN
825 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
826 aname => 'GMDQSVRS_MESG_CNT',
827 avalue => l_mesg_cnt);
828 ELSE
829 p_resultout := 'COMPLETE:DEFAULT';
830 END IF;
831 ELSIF (p_funcmode = 'RESPOND') THEN
832 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
833 itemkey => p_itemkey,
834 aname => 'GMDQSVRS_CURR_PERFORMER',
835 avalue => l_approver);
836 END IF;
837 END;
838
839 /****************************************************************************************
840 *** This procedure is associated with GMDQSVRS_NOTI_NOT_RESP activity of the workflow **
841 *** When approver fails to respond to notification defined in GMD: Workflow timeout **
842 *** profile this procedure sets spec Validity Rule status to start status and ends **
843 *** the workflow approval process. **
844 ****************************************************************************************/
845
846 PROCEDURE NO_RESPONSE (
847 p_itemtype IN VARCHAR2,
848 p_itemkey IN VARCHAR2,
849 p_actid IN NUMBER,
850 p_funcmode IN VARCHAR2,
851 p_resultout OUT NOCOPY VARCHAR2) IS
852 l_TABLE_NAME varchar2(50) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TABLE_NAME');
853 lSpecVRId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_VR_ID');
854 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
855 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
856 api_ret_status VARCHAR2(1);
857 api_err_mesg VARCHAR2(240);
858 BEGIN
859 IF p_funcmode = 'RUN' THEN
860 GMD_SPEC_GRP.change_status( p_table_name => l_TABLE_NAME
861 , p_id => lSpecVRId
862 , p_source_status => lStartStatus
863 , p_target_status => lTargetStatus
864 , p_mode => 'S'
865 , x_return_status => api_ret_status
866 , x_message => api_err_mesg );
867 IF api_ret_status <> 'S' THEN
868 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
869 raise APPLICATION_ERROR;
870 END IF;
871 END IF;
872 END NO_RESPONSE;
873
874 /****************************************************************************************
875 *** This procedure is associated with GMDQSVRS_NOTI_REWORK activity of the workflow **
876 *** When approver rejects status change request procedure sets spec Validity rule **
877 *** status to rework status and ends the workflow approval process. **
878 ****************************************************************************************/
879
880 PROCEDURE REQ_REJECTED (
881 p_itemtype IN VARCHAR2,
882 p_itemkey IN VARCHAR2,
883 p_actid IN NUMBER,
884 p_funcmode IN VARCHAR2,
885 p_resultout OUT NOCOPY VARCHAR2) IS
886 applicationId number :=552;
887 transactionType varchar2(50) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'EVENT_NAME');
888 l_TABLE_NAME varchar2(50) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TABLE_NAME');
889 nextApprover ame_util.approverRecord;
890 lSpecVRId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_VR_ID');
891 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
892 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
893 l_userID VARCHAR2(100) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'USER_ID');
894 new_user_id VARCHAR2(100);
895 api_ret_status VARCHAR2(1);
896 api_err_mesg VARCHAR2(240);
897 BEGIN
898 IF p_funcmode = 'RUN' THEN
899
900 --
901 -- Update Approver action
902 --
903 ame_api.getNextApprover(applicationIdIn => applicationId,
904 transactionIdIn => lSpecVRId,
905 transactionTypeIn => transactionType,
906 nextApproverOut => nextApprover);
907 IF nextApprover.person_id IS NOT NULL THEN
908 --
909 -- if we got HR Person then we have to find corresponding FND USER
910 -- assumption here is all HR user configured in AME will have
911 -- corresponding FND USER
912 --
913 new_user_id := ame_util.personIdToUserId(nextApprover.person_id);
914 ELSE
915 new_user_id := nextApprover.user_id;
916 END IF;
917 IF new_user_id = l_userID THEN
918 nextApprover.approval_status := ame_util.rejectStatus;
919 ame_api.updateApprovalStatus(applicationIdIn => applicationId,
920 transactionIdIn => lSpecVRId,
921 transactionTypeIn => transactionType,
922 ApproverIn => nextApprover);
923 END IF;
924 GMD_SPEC_GRP.change_status( p_table_name => l_TABLE_NAME
925 , p_id => lSpecVRId
926 , p_source_status => lStartStatus
927 , p_target_status => lTargetStatus
928 , p_mode => 'R'
929 , x_return_status => api_ret_status
930 , x_message => api_err_mesg );
931 IF api_ret_status <> 'S' THEN
932 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
933 raise APPLICATION_ERROR;
934 END IF;
935 END IF;
936
937 END REQ_REJECTED;
938
939 /****************************************************************************************
940 *** This procedure is associated with GMDQSVRS_NOTI_APPROVED activity of the workflow **
941 *** When approver approves status change request procedure sets AME Approver status **
942 *** to approved status and continues with approval process to verify any more **
943 *** approvals required **
944 ****************************************************************************************/
945
946
947 PROCEDURE REQ_APPROVED (
948 p_itemtype IN VARCHAR2,
949 p_itemkey IN VARCHAR2,
950 p_actid IN NUMBER,
951 p_funcmode IN VARCHAR2,
952 p_resultout OUT NOCOPY VARCHAR2) IS
953 applicationId number :=552;
954 transactionType varchar2(50) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'EVENT_NAME');
955 nextApprover ame_util.approverRecord;
956 lSpecVRId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_VR_ID');
957 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
958 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
959 l_userID VARCHAR2(100) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'USER_ID');
960 new_user_id VARCHAR2(100);
961 api_ret_status VARCHAR2(1);
962 api_err_mesg VARCHAR2(240);
963 BEGIN
964 IF p_funcmode = 'RUN' THEN
965 --
966 --
967 -- Update Approver action
968 --
969 ame_api.getNextApprover(applicationIdIn => applicationId,
970 transactionIdIn => lSpecVRId,
971 transactionTypeIn => transactionType,
972 nextApproverOut => nextApprover);
973 IF nextApprover.person_id IS NOT NULL THEN
974 --
975 -- if we got HR Person then we have to find corresponding FND USER
976 -- assumption here is all HR user configured in AME will have
977 -- corresponding FND USER
978 --
979 new_user_id := ame_util.personIdToUserId(nextApprover.person_id);
980 ELSE
981 new_user_id := nextApprover.user_id;
982 END IF;
983 IF new_user_id = l_userID THEN
984 nextApprover.approval_status := ame_util.approvedStatus;
985 ame_api.updateApprovalStatus(applicationIdIn => applicationId,
986 transactionIdIn => lSpecVRId,
987 transactionTypeIn => transactionType,
988 ApproverIn => nextApprover);
989 END IF;
990
991 END IF;
992
993 END REQ_APPROVED;
994
995 /**************************************************************************************
996 *** Following procedure accepts Status Code and entity type and resolves to Meaning **
997 **************************************************************************************/
998
999
1000 FUNCTION GET_STATUS_MEANING(P_STATUS_CODE NUMBER,
1001 P_ENTITY_TYPE VARCHAR2) RETURN VARCHAR2 IS
1002 CURSOR GET_STAT_MEANING IS
1003 SELECT MEANING
1004 FROM GMD_QC_STATUS
1005 WHERE STATUS_CODE = P_STATUS_CODE
1006 AND ENTITY_TYPE = P_ENTITY_TYPE;
1007 l_status_meaning GMD_QC_STATUS.MEANING%TYPE;
1008 BEGIN
1009 OPEN GET_STAT_MEANING;
1010 FETCH GET_STAT_MEANING INTO l_status_meaning;
1011 CLOSE GET_STAT_MEANING;
1012 RETURN l_status_meaning;
1013 END;
1014
1015 /***********************************************************************************************
1016 *** Following procedure is to raise Spec Validity Rule Status change approval business event **
1017 ***********************************************************************************************/
1018
1019 PROCEDURE RAISE_SPEC_VR_APPR_EVENT(p_SPEC_VR_ID NUMBER,
1020 P_EVENT_NAME VARCHAR2,
1021 P_TABLE_NAME VARCHAR2,
1022 p_START_STATUS NUMBER,
1023 p_TARGET_STATUS NUMBER) IS
1024 l_parameter_list wf_parameter_list_t :=wf_parameter_list_t( );
1025 BEGIN
1026 wf_log_pkg.wf_debug_flag:=TRUE;
1027 wf_event.AddParameterToList('SPEC_VR_ID', p_SPEC_VR_ID,l_parameter_list);
1028 wf_event.AddParameterToList('START_STATUS',p_START_STATUS ,l_parameter_list);
1029 wf_event.AddParameterToList('TARGET_STATUS',p_TARGET_STATUS ,l_parameter_list);
1030 wf_event.AddParameterToList('TABLE_NAME',P_TABLE_NAME ,l_parameter_list);
1031 wf_event.raise(p_event_name => P_EVENT_NAME,
1032 p_event_key => P_SPEC_VR_ID,
1033 p_parameters => l_parameter_list);
1034 l_parameter_list.DELETE;
1035 END;
1036 END GMDQSVRS_APPROVAL_WF_PKG;