[Home] [Help]
PACKAGE BODY: APPS.GMDQSVRS_APPROVAL_WF_PKG
Source
1 PACKAGE BODY GMDQSVRS_APPROVAL_WF_PKG AS
2 /* $Header: GMDQSVRB.pls 120.3 2006/05/15 04:50:03 rkrishan noship $ */
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;
351 close get_from_role ;
348
349 open get_from_role ;
350 fetch get_from_role into l_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
479 ====================================================*/
476
477 /*====================================================
478 BUG#4912074 - get additional data.
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
578 FND_MESSAGE.SET_TOKEN('SPEC_STATUS', mont_disp_attr_rec.MEANING);
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);
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
656
653
654 /* Set the message attribute, MSG, in the workflow */
655 FND_MESSAGE.SET_TOKEN('MSG', FND_MESSAGE.GET() );
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)||
703 wf_core.newline||l_comment;
704 l_comment := null;
705 EXCEPTION WHEN OTHERS THEN
706 NULL;
707 END;
708 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
709 itemkey => p_itemkey,
710 aname => 'GMDQSVRS_DISP_COMMENT',
711 avalue => l_mesg_comment);
712 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
713 itemkey => p_itemkey,
714 aname => 'GMDQSVRS_COMMENT',
715 avalue => l_comment);
716 END IF;
717 END APPEND_COMMENTS;
718
719 /***************************************************************************************
720 *** This procedure is associated with VERIFY_ANY_MORE_APPR activity of the workflow **
721 *** once current approver approves status change request this procedure call AME API **
722 *** to verify any more approvers need to approve this request. if it needs some more **
723 *** approvals then it sets approver info to workflow attrbute. now workflow moves to **
724 *** next approval processing. this will continue either all approves approves the **
725 *** request or any one of the rejects. if all approvals are complete then it sets **
726 *** spec validity rule status to target status **
727 ***************************************************************************************/
728
729
730 PROCEDURE ANY_MORE_APPROVERS (
731 p_itemtype IN VARCHAR2,
732 p_itemkey IN VARCHAR2,
733 p_actid IN NUMBER,
734 p_funcmode IN VARCHAR2,
735 p_resultout OUT NOCOPY VARCHAR2) IS
736 applicationId number :=552;
737 transactionType varchar2(50) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'EVENT_NAME');
738 l_TABLE_NAME varchar2(50) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TABLE_NAME');
739 nextApprover ame_util.approverRecord;
740 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
741 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
742 lSpecVRId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_VR_ID');
743 l_userID integer;
744 l_userName FND_USER.USER_NAME%TYPE;
745 api_ret_status VARCHAR2(1);
746 api_err_mesg VARCHAR2(240);
747 BEGIN
748 IF p_funcmode = 'RUN' THEN
749 --
750 -- Get the next approver who need to approve the trasaction
751 --
752 ame_api.getNextApprover(applicationIdIn => applicationId,
753 transactionIdIn => lSpecVRId,
757 IF nextApprover.user_id IS NULL and nextApprover.person_id IS NULL
754 transactionTypeIn => transactionType,
755 nextApproverOut => nextApprover);
756
758 THEN
759 --
760 -- All Approvers are approved.
761 -- change status of the object to target status
762 --
763
764 IF (l_debug = 'Y') THEN
765 gmd_debug.put_line('No more approvers required');
766 END IF;
767
768 GMD_SPEC_GRP.change_status( p_table_name => l_TABLE_NAME
769 , p_id => lSpecVRId
770 , p_source_status => lStartStatus
771 , p_target_status => lTargetStatus
772 , p_mode => 'A'
773 , x_return_status => api_ret_status
774 , x_message => api_err_mesg );
775 IF api_ret_status <> 'S' THEN
776 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
777 raise APPLICATION_ERROR;
778 END IF;
779 p_resultout := 'COMPLETE:N';
780 ELSE
781
782 IF (l_debug = 'Y') THEN
783 gmd_debug.put_line('There is still more approvers required');
784 END IF;
785
786
787 IF nextApprover.person_id IS NOT NULL THEN
788 --
789 -- if we got HR Person then we have to find corresponding FND USER
790 -- assumption here is all HR user configured in AME will have
791 -- corresponding FND USER
792 --
793 l_userID := ame_util.personIdToUserId(nextApprover.person_id);
794 ELSE
795 l_userID := nextApprover.user_id;
796 END IF;
797
798 l_userName := GET_FND_USER_NAME(l_userId);
799 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'USER_ID',l_userID);
800 wf_engine.setitemattrtext(p_itemtype, p_itemkey,'APPROVER',l_userName);
801 p_resultout := 'COMPLETE:Y';
802 END IF;
803 END IF;
804 END ANY_MORE_APPROVERS;
805
806 /*************************************************************************************
807 *** Following procedure is to verify any reminder is required when workflow timeout**
808 *** occurs **
809 *************************************************************************************/
810
811
812 PROCEDURE REMINDAR_CHECK (
813 p_itemtype IN VARCHAR2,
814 p_itemkey IN VARCHAR2,
815 p_actid IN NUMBER,
816 p_funcmode IN VARCHAR2,
817 p_resultout OUT NOCOPY VARCHAR2) IS
818 l_mesg_cnt number:=wf_engine.getitemattrnumber(p_itemtype, p_itemkey,'GMDQSVRS_MESG_CNT');
819 l_approver VARCHAR2(80) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'APPROVER');
820 BEGIN
821 IF (p_funcmode = 'TIMEOUT') THEN
822 l_mesg_cnt := l_mesg_cnt + 1;
823 IF l_mesg_cnt <= 4 THEN
824 WF_ENGINE.SETITEMATTRNUMBER(itemtype => p_itemtype,itemkey => p_itemkey,
825 aname => 'GMDQSVRS_MESG_CNT',
826 avalue => l_mesg_cnt);
827 ELSE
828 p_resultout := 'COMPLETE:DEFAULT';
829 END IF;
830 ELSIF (p_funcmode = 'RESPOND') THEN
831 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
832 itemkey => p_itemkey,
833 aname => 'GMDQSVRS_CURR_PERFORMER',
834 avalue => l_approver);
835 END IF;
836 END;
837
838 /****************************************************************************************
839 *** This procedure is associated with GMDQSVRS_NOTI_NOT_RESP activity of the workflow **
840 *** When approver fails to respond to notification defined in GMD: Workflow timeout **
841 *** profile this procedure sets spec Validity Rule status to start status and ends **
842 *** the workflow approval process. **
843 ****************************************************************************************/
844
845 PROCEDURE NO_RESPONSE (
846 p_itemtype IN VARCHAR2,
847 p_itemkey IN VARCHAR2,
851 l_TABLE_NAME varchar2(50) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TABLE_NAME');
848 p_actid IN NUMBER,
849 p_funcmode IN VARCHAR2,
850 p_resultout OUT NOCOPY VARCHAR2) IS
852 lSpecVRId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_VR_ID');
853 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
854 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
855 api_ret_status VARCHAR2(1);
856 api_err_mesg VARCHAR2(240);
857 BEGIN
858 IF p_funcmode = 'RUN' THEN
859 GMD_SPEC_GRP.change_status( p_table_name => l_TABLE_NAME
860 , p_id => lSpecVRId
861 , p_source_status => lStartStatus
862 , p_target_status => lTargetStatus
863 , p_mode => 'S'
864 , x_return_status => api_ret_status
865 , x_message => api_err_mesg );
866 IF api_ret_status <> 'S' THEN
867 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
868 raise APPLICATION_ERROR;
869 END IF;
870 END IF;
871 END NO_RESPONSE;
872
873 /****************************************************************************************
874 *** This procedure is associated with GMDQSVRS_NOTI_REWORK activity of the workflow **
875 *** When approver rejects status change request procedure sets spec Validity rule **
876 *** status to rework status and ends the workflow approval process. **
877 ****************************************************************************************/
878
879 PROCEDURE REQ_REJECTED (
880 p_itemtype IN VARCHAR2,
881 p_itemkey IN VARCHAR2,
882 p_actid IN NUMBER,
883 p_funcmode IN VARCHAR2,
884 p_resultout OUT NOCOPY VARCHAR2) IS
885 applicationId number :=552;
886 transactionType varchar2(50) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'EVENT_NAME');
887 l_TABLE_NAME varchar2(50) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TABLE_NAME');
888 nextApprover ame_util.approverRecord;
889 lSpecVRId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_VR_ID');
890 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
891 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
892 l_userID VARCHAR2(100) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'USER_ID');
893 new_user_id VARCHAR2(100);
894 api_ret_status VARCHAR2(1);
895 api_err_mesg VARCHAR2(240);
896 BEGIN
897 IF p_funcmode = 'RUN' THEN
898
899 --
900 -- Update Approver action
901 --
902 ame_api.getNextApprover(applicationIdIn => applicationId,
903 transactionIdIn => lSpecVRId,
904 transactionTypeIn => transactionType,
905 nextApproverOut => nextApprover);
906 IF nextApprover.person_id IS NOT NULL THEN
907 --
908 -- if we got HR Person then we have to find corresponding FND USER
909 -- assumption here is all HR user configured in AME will have
910 -- corresponding FND USER
911 --
912 new_user_id := ame_util.personIdToUserId(nextApprover.person_id);
913 ELSE
914 new_user_id := nextApprover.user_id;
915 END IF;
916 IF new_user_id = l_userID THEN
917 nextApprover.approval_status := ame_util.rejectStatus;
918 ame_api.updateApprovalStatus(applicationIdIn => applicationId,
919 transactionIdIn => lSpecVRId,
920 transactionTypeIn => transactionType,
921 ApproverIn => nextApprover);
922 END IF;
923 GMD_SPEC_GRP.change_status( p_table_name => l_TABLE_NAME
924 , p_id => lSpecVRId
925 , p_source_status => lStartStatus
926 , p_target_status => lTargetStatus
927 , p_mode => 'R'
928 , x_return_status => api_ret_status
929 , x_message => api_err_mesg );
930 IF api_ret_status <> 'S' THEN
931 WF_CORE.CONTEXT ('GMDQSPEC_APPROVAL_WF_PKG','is_approval_req',p_itemtype,p_itemkey,api_err_mesg );
932 raise APPLICATION_ERROR;
933 END IF;
934 END IF;
935
936 END REQ_REJECTED;
937
938 /****************************************************************************************
939 *** This procedure is associated with GMDQSVRS_NOTI_APPROVED activity of the workflow **
940 *** When approver approves status change request procedure sets AME Approver status **
941 *** to approved status and continues with approval process to verify any more **
942 *** approvals required **
943 ****************************************************************************************/
944
945
946 PROCEDURE REQ_APPROVED (
947 p_itemtype IN VARCHAR2,
948 p_itemkey IN VARCHAR2,
949 p_actid IN NUMBER,
950 p_funcmode IN VARCHAR2,
951 p_resultout OUT NOCOPY VARCHAR2) IS
952 applicationId number :=552;
953 transactionType varchar2(50) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'EVENT_NAME');
954 nextApprover ame_util.approverRecord;
955 lSpecVRId number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'SPEC_VR_ID');
959 new_user_id VARCHAR2(100);
956 lStartStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'START_STATUS');
957 lTargetStatus Number := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'TARGET_STATUS');
958 l_userID VARCHAR2(100) := wf_engine.getitemattrtext(p_itemtype, p_itemkey,'USER_ID');
960 api_ret_status VARCHAR2(1);
961 api_err_mesg VARCHAR2(240);
962 BEGIN
963 IF p_funcmode = 'RUN' THEN
964 --
965 --
966 -- Update Approver action
967 --
968 ame_api.getNextApprover(applicationIdIn => applicationId,
969 transactionIdIn => lSpecVRId,
970 transactionTypeIn => transactionType,
971 nextApproverOut => nextApprover);
972 IF nextApprover.person_id IS NOT NULL THEN
973 --
974 -- if we got HR Person then we have to find corresponding FND USER
975 -- assumption here is all HR user configured in AME will have
976 -- corresponding FND USER
977 --
978 new_user_id := ame_util.personIdToUserId(nextApprover.person_id);
979 ELSE
980 new_user_id := nextApprover.user_id;
981 END IF;
982 IF new_user_id = l_userID THEN
983 nextApprover.approval_status := ame_util.approvedStatus;
984 ame_api.updateApprovalStatus(applicationIdIn => applicationId,
985 transactionIdIn => lSpecVRId,
986 transactionTypeIn => transactionType,
987 ApproverIn => nextApprover);
988 END IF;
989
990 END IF;
991
992 END REQ_APPROVED;
993
994 /**************************************************************************************
995 *** Following procedure accepts Status Code and entity type and resolves to Meaning **
996 **************************************************************************************/
997
998
999 FUNCTION GET_STATUS_MEANING(P_STATUS_CODE NUMBER,
1000 P_ENTITY_TYPE VARCHAR2) RETURN VARCHAR2 IS
1001 CURSOR GET_STAT_MEANING IS
1002 SELECT MEANING
1003 FROM GMD_QC_STATUS
1004 WHERE STATUS_CODE = P_STATUS_CODE
1005 AND ENTITY_TYPE = P_ENTITY_TYPE;
1006 l_status_meaning GMD_QC_STATUS.MEANING%TYPE;
1007 BEGIN
1008 OPEN GET_STAT_MEANING;
1009 FETCH GET_STAT_MEANING INTO l_status_meaning;
1010 CLOSE GET_STAT_MEANING;
1011 RETURN l_status_meaning;
1012 END;
1013
1014 /***********************************************************************************************
1015 *** Following procedure is to raise Spec Validity Rule Status change approval business event **
1016 ***********************************************************************************************/
1017
1018 PROCEDURE RAISE_SPEC_VR_APPR_EVENT(p_SPEC_VR_ID NUMBER,
1019 P_EVENT_NAME VARCHAR2,
1020 P_TABLE_NAME VARCHAR2,
1021 p_START_STATUS NUMBER,
1022 p_TARGET_STATUS NUMBER) IS
1023 l_parameter_list wf_parameter_list_t :=wf_parameter_list_t( );
1024 BEGIN
1025 wf_log_pkg.wf_debug_flag:=TRUE;
1026 wf_event.AddParameterToList('SPEC_VR_ID', p_SPEC_VR_ID,l_parameter_list);
1027 wf_event.AddParameterToList('START_STATUS',p_START_STATUS ,l_parameter_list);
1028 wf_event.AddParameterToList('TARGET_STATUS',p_TARGET_STATUS ,l_parameter_list);
1029 wf_event.AddParameterToList('TABLE_NAME',P_TABLE_NAME ,l_parameter_list);
1030 wf_event.raise(p_event_name => P_EVENT_NAME,
1031 p_event_key => P_SPEC_VR_ID,
1032 p_parameters => l_parameter_list);
1033 l_parameter_list.DELETE;
1034 END;
1035 END GMDQSVRS_APPROVAL_WF_PKG;