1 PACKAGE BODY ENG_WORKFLOW_API_PKG as
2 /* $Header: engwkfwb.pls 120.4 2006/07/13 10:25:36 grastogi noship $ */
3
4 -- IN
5 -- itemtype - type of the current item
6 -- itemkey - key of the current item
7 -- actid - process activity instance id
8 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
9 -- OUT
10 -- result
11 -- - COMPLETE[:<result>]
12 -- activity has completed with the indicated result
13 -- - WAITING
14 -- activity is waiting for additional transitions
15 -- - DEFERED
16 -- execution should be defered to background
17 -- - NOTIFIED[:<notification_id>:<assigned_user>]
18 -- activity has notified an external entity that this
19 -- step must be performed. A call to wf_engine.CompleteActivty
20 -- will signal when this step is complete. Optional
21 -- return of notification ID and assigned user.
22 -- - ERROR[:<error_code>]
23 -- function encountered an error.
24
25 X_org_id NUMBER;
26 X_change_notice VARCHAR2(10);
27
28 /* Fix for bug 5131658-Added below procedure to get user's name*/
29 FUNCTION GetWFItemOwnerRole (itemtype IN VARCHAR2,
30 itemkey IN VARCHAR2)
31 RETURN VARCHAR2 IS
32 x_item_owner_role Varchar2(320);
33
34 BEGIN
35 /* Find User's User Name */
36 SELECT owner_role
37 INTO x_item_owner_role
38 FROM WF_ITEMS
39 WHERE item_type = itemtype
40 AND item_key = itemkey ;
41
42 Return x_item_owner_role;
43 EXCEPTION
44 WHEN OTHERS THEN
45 Return(NULL);
46
47 END GetWFItemOwnerRole ;
48
49 /* ************************************************************************
50 This procedure gets the Change Notice and Org Id and puts them into the
51 variables, X_change_notice and X_org_id.
52 ************************************************************************ */
53
54 PROCEDURE Get_ECO_and_OrgId(itemtype IN VARCHAR2,
55 itemkey IN VARCHAR2,
56 actid IN NUMBER,
57 funcmode IN VARCHAR2,
58 result IN OUT NOCOPY VARCHAR2) IS
59
60 X_length1 NUMBER := 0; /* length of itemkey */
61 X_hyphen1 NUMBER := 0; /* pos of separator bet org and rev */
62 X_length2 NUMBER := 0; /* length of eco||org */
63 X_hyphen2 NUMBER := 0; /* pos of separator bet eco and org */
64 X_ecoorg VARCHAR2(50);
65 X_rev_id NUMBER;
66 BEGIN
67 --
68 -- RUN mode - normal process execution
69 --
70 IF (funcmode = 'RUN') THEN
71 X_change_notice := Wf_Engine.GetItemAttrText(
72 itemtype => itemtype,
73 itemkey => itemkey,
74 aname => 'CHANGE_NOTICE');
75 X_org_id := Wf_Engine.GetItemAttrNumber(
76 itemtype => itemtype,
77 itemkey => itemkey,
78 aname => 'ORG_ID');
79 X_rev_id := Wf_Engine.GetItemAttrNumber(
80 itemtype => itemtype,
81 itemkey => itemkey,
82 aname => 'REV_ID');
83 IF (X_change_notice is null or X_org_id is null or
84 X_rev_id is null) THEN
85 X_length1 := LENGTH(itemkey);
86 FOR j IN 0..(X_length1 - 1) LOOP
87
88 IF (SUBSTR(itemkey, X_length1 - j, 1) = '-') THEN
89 X_hyphen1 := X_length1 - j;
90 GOTO get_rev;
91 END IF;
92 END LOOP;
93 <<get_rev>>
94 IF (X_hyphen1 <> 0) THEN
95 X_rev_id := substr(itemkey, X_hyphen1 + 1);
96 X_ecoorg := substr(itemkey,1, X_hyphen1 - 1);
97 X_length2 := length(X_ecoorg);
98 FOR k IN 0..(X_length2 - 1) LOOP
99 IF (SUBSTR(X_ecoorg, X_length2 - k, 1) = '-') THEN
100 X_hyphen2 := X_length2 - k;
101 GOTO get_org;
102 END IF;
103 END LOOP;
104 <<get_org>>
105 IF (X_hyphen2 <> 0) THEN
106 X_org_id := substr(X_ecoorg, X_hyphen2 +1);
107 X_change_notice := substr(X_ecoorg, 1, X_hyphen2 -1);
108 Wf_Engine.SetItemAttrText(itemtype => itemtype,
109 itemkey => itemkey,
110 aname => 'CHANGE_NOTICE',
111 avalue => X_change_notice);
112 Wf_Engine.SetItemAttrNumber(itemtype => itemtype,
113 itemkey => itemkey,
114 aname => 'ORG_ID',
115 avalue => X_org_id);
116 Wf_Engine.SetItemAttrNumber(itemtype => itemtype,
117 itemkey => itemkey,
118 aname => 'REV_ID',
119 avalue => X_rev_id);
120 ELSE
121 GOTO end_get;
122 END IF;
123 ELSE
124 GOTO end_get;
125 END IF;
126 ELSE
127 null;
128 END IF;
129 <<end_get>>
130 result := 'COMPLETE:FOUND ECO';
131 return;
132
133 --
134 -- CANCEL mode
135 --
136 -- This event point is called when the activity must
137 -- be undone, for example when a process is reset to an earlier point
138 -- due to a loop back.
139 --
140 ELSIF (funcmode = 'CANCEL') THEN
141 result := 'COMPLETE';
142 return;
143 END IF;
144
145 --
146 -- Other execution modes may be created in the future. Your
147 -- activity will indicate that it does not implement a mode
148 -- by returning null
149 --
150 result := '';
151 return;
152
153 EXCEPTION
154 WHEN OTHERS THEN
155 -- The line below records this function call in the error system
156 -- in the case of an exception.
157 WF_CORE.Context('ECO_APP', 'Get_ECO_and_OrgId',
158 itemtype, itemkey, to_char(actid), funcmode);
159 RAISE;
160
161 END Get_ECO_and_OrgId;
162
163
164 /* ************************************************************************
165 This procedure gets the ECO's attribute values and sets them in the Item
166 Type Attributes.
167 ************************************************************************ */
168
169 PROCEDURE Get_Eco_Attributes(itemtype IN VARCHAR2,
170 itemkey IN VARCHAR2,
171 actid IN NUMBER,
172 funcmode IN VARCHAR2,
173 result IN OUT NOCOPY VARCHAR2) IS
174 X_length NUMBER := 0;
175 X_colon NUMBER;
176 X_description VARCHAR2(2000);
177 X_eco_status VARCHAR2(80);
178 X_initiation_date DATE;
179 X_priority_code VARCHAR2(10);
180 X_reason_code VARCHAR2(10);
181 X_estimated_eng_cost NUMBER;
182 X_estimated_mfg_cost NUMBER;
183 X_attribute_category VARCHAR2(30);
184 X_attribute1 VARCHAR2(150);
185 X_attribute2 VARCHAR2(150);
186 X_attribute3 VARCHAR2(150);
187 X_attribute4 VARCHAR2(150);
188 X_attribute5 VARCHAR2(150);
189 X_attribute6 VARCHAR2(150);
190 X_attribute7 VARCHAR2(150);
191 X_attribute8 VARCHAR2(150);
192 X_attribute9 VARCHAR2(150);
193 X_attribute10 VARCHAR2(150);
194 X_attribute11 VARCHAR2(150);
195 X_attribute12 VARCHAR2(150);
196 X_attribute13 VARCHAR2(150);
197 X_attribute14 VARCHAR2(150);
198 X_attribute15 VARCHAR2(150);
199 X_approval_status VARCHAR2(80);
200 X_org_code VARCHAR2(3);
201 /* changing for UTF8 Column Expansion */
202 X_org_name VARCHAR2(240);
203 X_requestor VARCHAR2(240);
204 X_change_type VARCHAR2(80);
205 /* changing for UTF8 Column Expansion */
206 X_eco_dept_name VARCHAR2(240);
207 X_eco_dept_code VARCHAR2(3);
208 X_result VARCHAR2(2000);
209 l_requestor_name VARCHAR2(60);
210
211 -- added by ERES
212 X_approval_list_name VARCHAR2(360);
213
214 X_owner_value VARCHAR2(320); /* Added to fix bug 5131658*/
215
216 /* Added below two vars for fixing bug 5215778*/
217 X_task_id NUMBER;
218 X_project_id NUMBER;
219
220
221 BEGIN
222 --
223 -- RUN mode - normal process execution
224 --changed query for perf issue bug 5099572
225 IF (funcmode = 'RUN') THEN
226 Get_ECO_and_OrgId(itemtype => itemtype,
227 itemkey => itemkey,
228 actid => actid,
229 funcmode => funcmode,
230 result => X_result);
231
232 /* Fix for bug 5215778- Added task_id, project_id to the select statement.
233 Fix for bug 5200489- In the where clause, replaced person_id with party_id in the join between eec and mev */
234
235 SELECT eec.description,
236 (SELECT meaning FROM mfg_lookups WHERE eec.status_type = lookup_code
237 AND lookup_type = 'ECG_ECN_STATUS'), /* eco status */
238 eec.initiation_date,
239 eec.priority_code,
240 eec.reason_code,
241 eec.estimated_eng_cost,
242 eec.estimated_mfg_cost,
243 eec.attribute_category,
244 eec.attribute1,
245 eec.attribute2,
246 eec.attribute3,
247 eec.attribute4,
248 eec.attribute5,
249 eec.attribute6,
250 eec.attribute7,
251 eec.attribute8,
252 eec.attribute9,
253 eec.attribute10,
254 eec.attribute11,
255 eec.attribute12,
256 eec.attribute13,
257 eec.attribute14,
258 eec.attribute15,
259 (SELECT meaning FROM mfg_lookups WHERE eec.approval_status_type =
260 lookup_code
261 AND lookup_type = 'ENG_ECN_APPROVAL_STATUS'), /*approval status*/
262 (SELECT organization_code FROM mtl_parameters WHERE
263 organization_id=eec.organization_id),
264 (SELECT NAME FROM hr_all_organization_units WHERE
265 organization_id=eec.organization_id),
266 mev.full_name,
267 ecot.type_name,
268 (SELECT NAME FROM hr_all_organization_units WHERE
269 organization_id=eec.responsible_organization_id),
270 (SELECT organization_code FROM mtl_parameters WHERE
271 organization_id=eec.responsible_organization_id),
272 eec.task_id,
273 eec.project_id
274 INTO X_description,
275 X_eco_status,
276 X_initiation_date,
277 X_priority_code,
278 X_reason_code,
279 X_estimated_eng_cost,
280 X_estimated_mfg_cost,
281 X_attribute_category,
282 X_attribute1,
283 X_attribute2,
284 X_attribute3,
285 X_attribute4,
286 X_attribute5,
287 X_attribute6,
288 X_attribute7,
289 X_attribute8,
290 X_attribute9,
291 X_attribute10,
292 X_attribute11,
293 X_attribute12,
294 X_attribute13,
295 X_attribute14,
296 X_attribute15,
297 X_approval_status,
298 X_org_code,
299 X_org_name,
300 X_requestor,
301 X_change_type,
302 X_eco_dept_name,
303 X_eco_dept_code,
304 X_task_id,
305 X_project_id
306 FROM per_people_f mev,
307 eng_change_order_types_vl ecot,
308 eng_engineering_changes eec
309 WHERE eec.organization_id = X_org_id
310 AND eec.change_notice = X_change_notice
311 AND eec.requestor_id = mev.party_id(+)--mev.person_id(+) -- Bug 4644000
312 AND eec.change_order_type_id = ecot.change_order_type_id
313 AND rownum = 1;
314
315 -- ERES Begin
316 -- added by ERES to get APPROVAL_LIST_NAME
317
318 /*
319 OLD CODE, removed for performance bug 3666795
320 SELECT r.name
321 INTO X_approval_list_name
322 FROM wf_roles r, eng_engineering_changes eec, eng_ecn_approval_lists al
323 WHERE eec.approval_list_id = al.approval_list_id
324 AND eec.organization_id = X_org_id
325 AND eec.change_notice = X_change_notice
326 AND al.approval_list_name = r.display_name ;
327
328 */
329 -- Bug 4260372 : Added exception handling - If the approval list id is null
330 Begin
331 /* new code - bug 3666795 */
332 select r.name
333 INTO X_approval_list_name
334 from wf_local_roles r
335 where r.display_name in (SELECT al.approval_list_name
336 FROM eng_ecn_approval_lists al, eng_engineering_changes eec
337 WHERE eec.approval_list_id = al.approval_list_id
338 AND eec.organization_id = X_org_id
339 AND eec.change_notice = X_change_notice) ;
340 Exception
341 When NO_DATA_FOUND then
342 X_approval_list_name := null;
343 end;
344
345 -- Add aproval list name in attribute APPROVAL_LIST
346 Wf_Engine.SetItemAttrText(itemtype => itemtype,
347 itemkey => itemkey,
348 aname => 'APPROVAL_LIST',
349 avalue => X_approval_list_name );
350 -- ERES end
351
352
353 Wf_Engine.SetItemAttrText(itemtype => itemtype,
354 itemkey => itemkey,
355 aname => 'CHANGE_NOTICE',
356 avalue => X_change_notice);
357 Wf_Engine.SetItemAttrText(itemtype => itemtype,
358 itemkey => itemkey,
359 aname => 'ECO_DESCRIPTION',
360 avalue => X_description);
361 Wf_Engine.SetItemAttrText(itemtype => itemtype,
362 itemkey => itemkey,
363 aname => 'ECO_STATUS',
364 avalue => X_eco_status);
365 Wf_Engine.SetItemAttrDate(itemtype => itemtype,
366 itemkey => itemkey,
367 aname => 'INITIATION_DATE',
368 avalue => X_initiation_date);
369
370 l_requestor_name := FND_GLOBAL.USER_NAME;
371 Wf_Engine.SetItemOwner( itemtype => itemtype,
372 itemkey => itemkey,
373 owner => l_requestor_name );
374
375 Wf_Engine.SetItemAttrText(itemtype => itemtype,
376 itemkey => itemkey,
377 aname => 'PRIORITY_CODE',
378 avalue => X_priority_code);
379 Wf_Engine.SetItemAttrText(itemtype => itemtype,
380 itemkey => itemkey,
381 aname => 'REASON_CODE',
382 avalue => X_reason_code);
383 Wf_Engine.SetItemAttrNumber(itemtype => itemtype,
384 itemkey => itemkey,
385 aname => 'ESTIMATED_ENG_COST',
386 avalue => X_estimated_eng_cost);
387 Wf_Engine.SetItemAttrNumber(itemtype => itemtype,
388 itemkey => itemkey,
389 aname => 'ESTIMATED_MFG_COST',
390 avalue => X_estimated_mfg_cost);
391 Wf_Engine.SetItemAttrText(itemtype => itemtype,
392 itemkey => itemkey,
393 aname => 'ATTRIBUTE_CATEGORY',
394 avalue => X_attribute_category);
395 Wf_Engine.SetItemAttrText(itemtype => itemtype,
396 itemkey => itemkey,
397 aname => 'ATTRIBUTE1',
398 avalue => X_attribute1);
399 Wf_Engine.SetItemAttrText(itemtype => itemtype,
400 itemkey => itemkey,
401 aname => 'ATTRIBUTE2',
402 avalue => X_attribute2);
403 Wf_Engine.SetItemAttrText(itemtype => itemtype,
404 itemkey => itemkey,
405 aname => 'ATTRIBUTE3',
406 avalue => X_attribute3);
407 Wf_Engine.SetItemAttrText(itemtype => itemtype,
408 itemkey => itemkey,
409 aname => 'ATTRIBUTE4',
413 aname => 'ATTRIBUTE5',
410 avalue => X_attribute4);
411 Wf_Engine.SetItemAttrText(itemtype => itemtype,
412 itemkey => itemkey,
414 avalue => X_attribute5);
415 Wf_Engine.SetItemAttrText(itemtype => itemtype,
416 itemkey => itemkey,
417 aname => 'ATTRIBUTE6',
418 avalue => X_attribute6);
419 Wf_Engine.SetItemAttrText(itemtype => itemtype,
420 itemkey => itemkey,
421 aname => 'ATTRIBUTE7',
422 avalue => X_attribute7);
423 Wf_Engine.SetItemAttrText(itemtype => itemtype,
424 itemkey => itemkey,
425 aname => 'ATTRIBUTE8',
426 avalue => X_attribute8);
427 Wf_Engine.SetItemAttrText(itemtype => itemtype,
428 itemkey => itemkey,
429 aname => 'ATTRIBUTE9',
430 avalue => X_attribute9);
431 Wf_Engine.SetItemAttrText(itemtype => itemtype,
432 itemkey => itemkey,
433 aname => 'ATTRIBUTE10',
434 avalue => X_attribute10);
435 Wf_Engine.SetItemAttrText(itemtype => itemtype,
436 itemkey => itemkey,
437 aname => 'ATTRIBUTE11',
438 avalue => X_attribute11);
439 Wf_Engine.SetItemAttrText(itemtype => itemtype,
440 itemkey => itemkey,
441 aname => 'ATTRIBUTE12',
442 avalue => X_attribute12);
443 Wf_Engine.SetItemAttrText(itemtype => itemtype,
444 itemkey => itemkey,
445 aname => 'ATTRIBUTE13',
446 avalue => X_attribute13);
447 Wf_Engine.SetItemAttrText(itemtype => itemtype,
448 itemkey => itemkey,
449 aname => 'ATTRIBUTE14',
450 avalue => X_attribute14);
451 Wf_Engine.SetItemAttrText(itemtype => itemtype,
452 itemkey => itemkey,
453 aname => 'ATTRIBUTE15',
454 avalue => X_attribute15);
455 Wf_Engine.SetItemAttrText(itemtype => itemtype,
456 itemkey => itemkey,
457 aname => 'APPROVAL_STATUS',
458 avalue => X_approval_status);
459 Wf_Engine.SetItemAttrText(itemtype => itemtype,
460 itemkey => itemkey,
461 aname => 'ORGANIZATION_CODE',
462 avalue => X_org_code);
463 Wf_Engine.SetItemAttrText(itemtype => itemtype,
464 itemkey => itemkey,
465 aname => 'ORGANIZATION_NAME',
466 avalue => X_org_name);
467 Wf_Engine.SetItemAttrText(itemtype => itemtype,
468 itemkey => itemkey,
469 aname => 'FULL_NAME',
470 avalue => X_requestor);
471 Wf_Engine.SetItemAttrText(itemtype => itemtype,
472 itemkey => itemkey,
473 aname => 'CHANGE_TYPE',
474 avalue => X_change_type);
475 Wf_Engine.SetItemAttrText(itemtype => itemtype,
476 itemkey => itemkey,
477 aname => 'ECO_DEPT_NAME',
478 avalue => X_eco_dept_name);
479 Wf_Engine.SetItemAttrText(itemtype => itemtype,
480 itemkey => itemkey,
481 aname => 'ECO_DEPT_CODE',
485 Wf_Engine.SetItemAttrNumber(itemtype=> itemtype,
482 avalue => X_eco_dept_code);
483
484 /* Fix for bug 5215778 - Added below code to assign values to Wf attributes TASK_ID, PROJECT_ID */
486 itemkey => itemkey,
487 aname => 'TASK_ID',
488 avalue => X_task_id);
489 Wf_Engine.SetItemAttrNumber(itemtype=> itemtype,
490 itemkey => itemkey,
491 aname => 'PROJECT_ID',
492 avalue => X_project_id);
493
494 /* Fix for bug 5131658- Get FND User who starts workflow*/
495 X_owner_value := GetWFItemOwnerRole(itemtype,
496 itemkey);
497 /* Set the value to WF_SIGN_REQUESTER */
498 Wf_Engine.SetItemAttrText(itemtype => itemtype,
499 itemkey => itemkey,
500 aname => '#WF_SIGN_REQUESTER',
501 avalue => X_owner_value);
502 /* End of fix for bug 5131658 */
503 result := 'COMPLETE:ASSIGNED ATTRIBUTES';
504 RETURN;
505 --
506 -- CANCEL mode
507 --
508 -- This event point is called when the activity must
509 -- be undone, for example when a process is reset to an earlier point
510 -- due to a loop back.
511 --
512 ELSIF (funcmode = 'CANCEL') THEN
513 result := 'COMPLETE';
514 return;
515 END IF;
516
517 --
518 -- Other execution modes may be created in the future. Your
519 -- activity will indicate that it does not implement a mode
520 -- by returning null
521 --
522 result := '';
523 RETURN;
524
525 EXCEPTION
526 WHEN OTHERS THEN
527 -- The line below records this function call in the error system
528 -- in the case of an exception.
529 WF_CORE.Context('ECO_APP', 'Get_Eco_Attributes',
530 itemtype, itemkey, to_char(actid), funcmode);
531 RAISE;
532
533 END Get_Eco_Attributes;
534
535
536 /* ************************************************************************
537 This procedure updates an ECO's Approval Status to "Approved".
538 If the user updates the status to "Approved" then the Approval Date gets
539 updated to today's date.
540 ************************************************************************ */
541
542 PROCEDURE Approve_Eco( itemtype IN VARCHAR2,
543 itemkey IN VARCHAR2,
544 actid IN NUMBER,
545 funcmode IN VARCHAR2,
546 result IN OUT NOCOPY VARCHAR2)
547 IS
548 X_eco_result VARCHAR2(2000);
549 BEGIN
550 --
551 -- RUN mode - normal process execution
552 --
553 IF (funcmode = 'RUN') THEN
554 Get_ECO_and_OrgId(itemtype => itemtype,
555 itemkey => itemkey,
556 actid => actid,
557 funcmode => funcmode,
558 result => X_eco_result);
559 UPDATE eng_engineering_changes
560 SET approval_status_type = 5,
561 approval_date = sysdate
562 WHERE organization_id = X_org_id
563 AND change_notice = X_change_notice;
564
565 UPDATE eng_revised_items
566 SET status_type = 4 /* Set Rev Item Status = Scheduled */
567 ,status_code = 4 --Bug 3526627: Changes for 11.5.10, set the status_code also
568 WHERE change_notice = X_change_notice
569 AND organization_id = X_org_id
570 AND status_type = 1; /* Rev Item Status = Open */
571
572 --bug 2307416
573 UPDATE eng_engineering_changes
574 SET status_type = 4
575 ,status_code = 4 --Bug 3526627: Changes for 11.5.10, set the status_code also
576 WHERE change_notice = X_change_notice
577 AND organization_id = X_org_id
578 AND status_type = 1;
579
580 commit;
581 <<end_procedure>>
582 result := 'COMPLETE:APPROVED';
583 RETURN;
584
585 --
586 -- CANCEL mode
587 --
588 -- This event point is called when the activity must
589 -- be undone, for example when a process is reset to an earlier point
590 -- due to a loop back.
591 --
592 ELSIF (funcmode = 'CANCEL') THEN
593 result := 'COMPLETE';
594 return;
595 END IF;
596
597 --
598 -- Other execution modes may be created in the future. Your
599 -- activity will indicate that it does not implement a mode
600 -- by returning null
601 --
602 result := '';
603 RETURN;
604
605 EXCEPTION
606 WHEN OTHERS THEN
607 -- The line below records this function call in the error system
608 -- in the case of an exception.
609 WF_CORE.Context('ECO_APP', 'Approve_Eco',
610 itemtype, itemkey, to_char(actid), funcmode);
611 RAISE;
612
613 END Approve_Eco;
614
615 /* ************************************************************************
616 This procedure updates an ECO's Approval Status to "Rejected".
617 The Approval Date is set to null.
621 itemkey IN VARCHAR2,
618 ************************************************************************ */
619
620 PROCEDURE Reject_Eco( itemtype IN VARCHAR2,
622 actid IN NUMBER,
623 funcmode IN VARCHAR2,
624 result IN OUT NOCOPY VARCHAR2)
625 IS
626 X_eco_result VARCHAR2(2000);
627 BEGIN
628 --
629 -- RUN mode - normal process execution
630 --
631 IF (funcmode = 'RUN') THEN
632 Get_ECO_and_OrgId(itemtype => itemtype,
633 itemkey => itemkey,
634 actid => actid,
635 funcmode => funcmode,
636 result => X_eco_result);
637 UPDATE eng_engineering_changes
638 SET approval_status_type = 4,
639 approval_date = null
640 WHERE organization_id = X_org_id
641 AND change_notice = X_change_notice;
642
643 commit;
644 <<end_procedure>>
645 result := 'COMPLETE:REJECTED';
646 RETURN;
647 --
648 -- CANCEL mode
649 --
650 -- This event point is called when the activity must
651 -- be undone, for example when a process is reset to an earlier point
652 -- due to a loop back.
653 --
654 ELSIF (funcmode = 'CANCEL') THEN
655 result := 'COMPLETE';
656 RETURN;
657 END IF;
658
659 --
660 -- Other execution modes may be created in the future. Your
661 -- activity will indicate that it does not implement a mode
662 -- by returning null
663 --
664 result := '';
665 RETURN;
666
667 EXCEPTION
668 WHEN OTHERS THEN
669 -- The line below records this function call in the error system
670 -- in the case of an exception.
671 WF_CORE.Context('ECO_APP', 'Reject_Eco',
672 itemtype, itemkey, to_char(actid), funcmode);
673 RAISE;
674 END Reject_Eco;
675
676
677 /* ************************************************************************
678 This procedure sets an ECO's Approval Status to 'Processing Error'. This
679 procedure is meant to be used in the Default Error Process.
680 ************************************************************************ */
681
682 PROCEDURE Set_Eco_Approval_Error(itemtype IN VARCHAR2,
683 itemkey IN VARCHAR2,
684 actid IN NUMBER,
685 funcmode IN VARCHAR2,
686 result IN OUT NOCOPY VARCHAR2) IS
687
688 X_itemkey VARCHAR2(80);
689 X_itemtype VARCHAR2(80);
690 X_eco_result VARCHAR2(2000);
691 BEGIN
692 --
693 -- RUN mode - normal process execution
694 --
695 IF (funcmode = 'RUN') THEN
696 X_itemkey := Wf_Engine.GetItemAttrText(itemtype => itemtype,
697 itemkey => itemkey,
698 aname => 'ERROR_ITEM_KEY');
699 X_itemtype := Wf_Engine.GetItemAttrText(itemtype => itemtype,
700 itemkey => itemkey,
701 aname => 'ERROR_ITEM_TYPE');
702 Get_ECO_and_OrgId(itemtype => X_itemtype,
703 itemkey => X_itemkey,
704 actid => actid,
705 funcmode => funcmode,
706 result => X_eco_result);
707 UPDATE eng_engineering_changes
708 SET approval_status_type = 7,
709 approval_date = ''
710 WHERE organization_id = X_org_id
711 AND change_notice = X_change_notice;
712
713 result := 'COMPLETE:ERRORED';
714 RETURN;
715 --
716 -- CANCEL mode
717 --
718 -- This event point is called when the activity must
719 -- be undone, for example when a process is reset to an earlier point
720 -- due to a loop back.
721 --
722 ELSIF (funcmode = 'CANCEL') THEN
723 result := 'COMPLETE';
724 RETURN;
725 END IF;
726
727 <<end_procedure>>
728 --
729 -- Other execution modes may be created in the future. Your
730 -- activity will indicate that it does not implement a mode
731 -- by returning null
732 --
733 result := '';
734 RETURN;
735
736 EXCEPTION
737 WHEN OTHERS THEN
738 -- The line below records this function call in the error system
739 -- in the case of an exception.
740 WF_CORE.Context('ECO_APP', 'Set_Eco_Approval_Error',
741 itemtype, itemkey, to_char(actid), funcmode);
742 RAISE;
743 END Set_Eco_Approval_Error;
744
745
746 /* ************************************************************************
747 This procedure updates the MRP Active flag to 'Yes' for all the revised
748 items for a given ECO only if the revised item is at Status 'Open' or
749 'Scheduled'.
750 ************************************************************************ */
751
752 PROCEDURE Set_Mrp_Active( itemtype IN VARCHAR2,
753 itemkey IN VARCHAR2,
757 IS
754 actid IN NUMBER,
755 funcmode IN VARCHAR2,
756 result IN OUT NOCOPY VARCHAR2)
758 X_eco_result VARCHAR2(2000);
759 BEGIN
760 --
761 -- RUN mode - normal process execution
762 --
763 IF (funcmode = 'RUN') THEN
764 Get_ECO_and_OrgId(itemtype => itemtype,
765 itemkey => itemkey,
766 actid => actid,
767 funcmode => funcmode,
768 result => X_eco_result);
769 UPDATE eng_revised_items
770 SET mrp_active = 1 /* Set MRP Active=Yes */
771 WHERE change_notice = X_change_notice
772 AND organization_id = X_org_id
773 AND status_type in (1, 4); /* Rev Item Status=Open or Scheduled */
774
775 commit;
776
777 <<end_procedure>>
778 result := 'COMPLETE:MRP ACTIVE';
779 RETURN;
780 --
781 -- CANCEL mode
782 --
783 -- This event point is called when the activity must
784 -- be undone, for example when a process is reset to an earlier point
785 -- due to a loop back.
786 --
787 ELSIF (funcmode = 'CANCEL') THEN
788 result := 'COMPLETE';
789 RETURN;
790 END IF;
791
792 --
793 -- Other execution modes may be created in the future. Your
794 -- activity will indicate that it does not implement a mode
795 -- by returning null
796 --
797 result := '';
798 RETURN;
799
800 EXCEPTION
801 WHEN OTHERS THEN
802 -- The line below records this function call in the error system
803 -- in the case of an exception.
804 WF_CORE.Context('ECO_APP', 'Set_Mrp_Active',
805 itemtype, itemkey, to_char(actid), funcmode);
806 RAISE;
807
808 END Set_Mrp_Active;
809
810
811 /* ************************************************************************
812 This procedure updates the MRP Active flag to 'No' for all the revised
813 items for a given ECO only if the revised item is at Status 'Open' or
814 'Scheduled'.
815 ************************************************************************ */
816
817 PROCEDURE Set_Mrp_Inactive( itemtype IN VARCHAR2,
818 itemkey IN VARCHAR2,
819 actid IN NUMBER,
820 funcmode IN VARCHAR2,
821 result IN OUT NOCOPY VARCHAR2)
822 IS
823 X_eco_result VARCHAR2(2000);
824 BEGIN
825 --
826 -- RUN mode - normal process execution
827 --
828 IF (funcmode = 'RUN') THEN
829 Get_ECO_and_OrgId(itemtype => itemtype,
830 itemkey => itemkey,
831 actid => actid,
832 funcmode => funcmode,
833 result => X_eco_result);
834 UPDATE eng_revised_items
835 SET mrp_active = 2 /* Set MRP Active=Yes */
836 WHERE change_notice = X_change_notice
837 AND organization_id = X_org_id
838 AND status_type in (1, 4); /* Rev Item Status=Open or Scheduled */
839
840 commit;
841 <<end_procedure>>
842
843 result := 'COMPLETE:MRP INACTIVE';
844 RETURN;
845 --
846 -- CANCEL mode
847 --
848 -- This event point is called when the activity must
849 -- be undone, for example when a process is reset to an earlier point
850 -- due to a loop back.
851 --
852 ELSIF (funcmode = 'CANCEL') THEN
853 result := 'COMPLETE';
854 RETURN;
855 END IF;
856
857 --
858 -- Other execution modes may be created in the future. Your
859 -- activity will indicate that it does not implement a mode
860 -- by returning null
861 --
862 result := '';
863 RETURN;
864
865 EXCEPTION
866 WHEN OTHERS THEN
867 -- The line below records this function call in the error system
868 -- in the case of an exception.
869 WF_CORE.Context('ECO_APP', 'Set_Mrp_Inactive',
870 itemtype, itemkey, to_char(actid), funcmode);
871 RAISE;
872 END Set_Mrp_Inactive;
873
874
875 /************************************************
876 This procedure will post ERES eRecord into the evidence store.
877 For both Approve and Rejected case.
878 *****************************************/
879
880 PROCEDURE UPDATE_EVIDENCE (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
885 ) IS
886
887 l_requester varchar2(240);
888 l_Event_key NUMBER;
889 l_Event_name varchar2(240) := 'oracle.apps.eng.ecoApproval';
890 l_change_notice varchar2(240);
891 l_user_response varchar2(30);
892
893 l_doc_id number;
894 l_error number;
895 l_error_msg varchar2(4000);
896 l_doc_params qa_edr_standard.params_tbl_type;
897 l_sig_id number;
898 l_notification_result varchar2(1000);
902 l_nid number;
899 l_ret_status varchar2(30);
900 l_msg_count number;
901 l_msg_data varchar2(1000);
903
904 l_eRecord_id NUMBER;
905 l_return_status VARCHAR2(1);
906 l_trans_status VARCHAR2(30);
907 l_msg_index NUMBER;
908 l_send_ackn boolean;
909 l_autonomous_commit VARCHAR2(1);
910
911 l_eres_doc NUMBER;
912
913 l_parameters qa_edr_standard.Params_tbl_type;
914 l_sign_params qa_edr_standard.Params_tbl_type;
915
916 BEGIN
917 IF P_FUNCMODE ='RUN' THEN
918
919 l_requester := FND_GLOBAL.USER_NAME;
920
921 l_Event_Key := wf_engine.GETITEMATTRNUMBER(itemtype => p_itemtype,
922 itemkey => p_itemkey,
923 aname => 'CHANGE_ID');
924
925 l_eres_doc := wf_engine.GETITEMATTRNUMBER(itemtype => p_itemtype,
926 itemkey => p_itemkey,
927 aname => 'OPEN_ERES_DOC');
928
929 l_change_notice := wf_engine.GETITEMATTRTEXT(itemtype => p_itemtype,
930 itemkey => p_itemkey,
931 aname => 'CHANGE_NOTICE');
932
933 IF l_eres_doc = 0
934 then
935
936 -- This is only done once, flag l_eres_doc is used to control this
937 -- One eRecord is generated per approval workflow notification, therefor
938 -- we call open_Documentjust the once
939
940 /* Getting Notification Id */
941 SELECT NOTIFICATION_ID
942 INTO l_nid
943 FROM WF_ITEM_ACTIVITY_STATUSES
944 WHERE ITEM_KEY = p_itemkey
945 AND ITEM_TYPE = p_itemtype
946 AND NOTIFICATION_ID IS NOT NULL;
947
948
949 /***** opendocument ******/
950
951 qa_edr_standard.open_Document (
952 p_api_version => 1.0,
953 p_init_msg_list => 'T',
954 p_commit => 'FALSE',
955 x_return_status => l_return_status,
956 x_msg_count => l_msg_count,
957 x_msg_data => l_msg_data,
958 P_PSIG_XML => NULL,
959 P_PSIG_DOCUMENT => NULL,
960 P_PSIG_DOCUMENTFORMAT => NULL,
961 P_PSIG_REQUESTER => l_requester,
962 P_PSIG_SOURCE => NULL,
963 P_EVENT_NAME => l_Event_name,
964 P_EVENT_KEY => l_Event_Key,
965 p_wf_notif_id => l_nid,
966 X_DOCUMENT_ID => l_doc_id);
967
968
969 /* Post document parameters */
970
971 l_parameters(1).param_name:='PSIG_USER_KEY_LABEL';
972 FND_MESSAGE.SET_NAME('ENG','ENG_ECO_APPROVAL');
973 l_parameters(1).param_value:=FND_MESSAGE.GET;
974 l_parameters(1).param_displayname:=NULL;
975
976 l_parameters(2).param_name:='PSIG_USER_KEY_VALUE';
977 l_parameters(2).param_value:= l_change_notice;
978 l_parameters(2).param_displayname:=NULL;
979
980 /******** postDocumentParameter ************/
981 qa_edr_standard.Post_DocumentParameters (
982 p_api_version => 1.0,
983 p_init_msg_list => 'T',
984 p_commit => 'FALSE',
985 x_return_status => l_return_status,
986 x_msg_count => l_msg_count,
987 x_msg_data => l_msg_data,
988 p_document_id => l_doc_id,
989 p_doc_parameters_tbl => l_parameters);
990
991 -- SET DOC ID
992 Wf_Engine.SetItemAttrNumber(itemtype => p_itemtype,
993 itemkey => p_itemkey,
994 aname => 'DOC_ID',
995 avalue => l_doc_id);
996 end if;
997 -- the rest below will be called many times, per # of signers
998
999 Wf_Engine.SetItemAttrNumber(itemtype => p_itemtype,
1000 itemkey => p_itemkey,
1001 aname => 'OPEN_ERES_DOC',
1002 avalue => 1);
1003
1004 l_doc_id := wf_engine.GETITEMATTRNUMBER(itemtype => p_itemtype,
1005 itemkey => p_itemkey,
1006 aname => 'DOC_ID');
1007
1008
1009 -- get the result of the notification i.e approve/rejected
1010 l_notification_result := wf_engine.GETITEMATTRTEXT(itemtype => p_itemtype,
1011 itemkey => p_itemkey,
1012 aname => 'RESULT');
1013
1014
1015 /* Post Signature Parameters */
1016
1017 -- Singning Reason
1018 l_sign_params(1).param_name:= 'REASON_CODE';
1019 l_sign_params(1).param_value:= wf_engine.getitemattrtext(p_itemtype,p_itemkey,'SIG_REASON');
1020 l_sign_params(1).param_displayname:= 'Signing Reason';
1021
1022 -- Signer comments
1023 l_sign_params(2).param_name:='SIGNERS_COMMENT';
1024 l_sign_params(2).param_value:= wf_engine.getitemattrtext(p_itemtype,p_itemkey,'SIGNERS_COMMENTS');
1025 l_sign_params(2).param_displayname:='Signer Comments';
1026
1027
1028 -- Signature Type
1029 l_sign_params(3).param_name:='WF_SIGNER_TYPE';
1033 IF l_notification_result ='Y'
1030 l_sign_params(3).param_value:= wf_engine.getitemattrtext(p_itemtype,p_itemkey,'WF_SIGNER_TYPE');
1031 l_sign_params(3).param_displayname:='Signature Type ';
1032
1034 THEN
1035 l_user_response := 'Approved';
1036 ELSE
1037 l_user_response := 'Rejected';
1038 END IF;
1039
1040 qa_edr_standard.Request_Signature (
1041 p_api_version => 1.0,
1042 p_init_msg_list => 'T',
1043 p_commit => 'FALSE',
1044 x_return_status => l_return_status,
1045 x_msg_count => l_msg_count,
1046 x_msg_data => l_msg_data,
1047 P_DOCUMENT_ID => l_doc_id,
1048 P_USER_NAME => l_requester,
1049 P_ORIGINAL_RECIPIENT => NULL,
1050 P_OVERRIDING_COMMENT => NULL,
1051 x_signature_id => l_sig_id
1052 );
1053
1054 qa_edr_standard.Post_Signature (
1055 p_api_version => 1.0,
1056 p_init_msg_list => 'T',
1057 p_commit => 'FALSE',
1058 x_return_status => l_return_status,
1059 x_msg_count => l_msg_count,
1060 x_msg_data => l_msg_data,
1061 P_DOCUMENT_ID => l_doc_id,
1062 p_evidenceStore_id => '1',
1063 P_USER_NAME => l_requester,
1064 P_USER_RESPONSE => l_user_response,
1065 P_ORIGINAL_RECIPIENT => NULL,
1066 P_OVERRIDING_COMMENT => NULL,
1067 x_signature_id => l_sig_id
1068 );
1069
1070 qa_edr_standard.Post_SignatureParameters (
1071 p_api_version => 1.0,
1072 p_init_msg_list => 'T',
1073 p_commit => 'FALSE',
1074 x_return_status => l_return_status,
1075 x_msg_count => l_msg_count,
1076 x_msg_data => l_msg_data,
1077 p_signature_id => l_sig_id,
1078 p_sig_parameters_tbl => l_sign_params
1079 );
1080
1081 END IF;
1082
1083 p_resultout := 'COMPLETE:UPDATE_EVIDENCE';
1084 EXCEPTION
1085 WHEN OTHERS THEN
1086 WF_CORE.CONTEXT ('ENG_WORKFLOW_API_PKG','UPDATE_EVIDENCE',
1087 p_itemtype,p_itemkey,SQLERRM);
1088 raise;
1089
1090 END UPDATE_EVIDENCE;
1091
1092
1093 /* ************************************************************************
1094 This procedure gets the ERES attribute values for the ERES process,
1095 and sets them in the Item Type Attributes.
1096 ************************************************************************ */
1097
1098 PROCEDURE Get_ERES_Attributes(itemtype IN VARCHAR2,
1099 itemkey IN VARCHAR2,
1100 actid IN NUMBER,
1101 funcmode IN VARCHAR2,
1102 result IN OUT NOCOPY VARCHAR2) IS
1103
1104 X_change_id NUMBER;
1105 l_requester_name VARCHAR2(60);
1106 i_param_list wf_parameter_list_t;
1107 p_xmldoc clob;
1108
1109 l_psig_event WF_EVENT_T;
1110 l_event_name varchar2(240);
1111 l_event_key varchar2(240);
1112
1113
1114 BEGIN
1115 --
1116 -- RUN mode - normal process execution
1117 --
1118 IF (funcmode = 'RUN') THEN
1119
1120 -- get Event Key
1121 SELECT
1122 eec.change_id
1123 INTO
1124 X_change_id
1125 FROM eng_engineering_changes eec
1126 WHERE eec.organization_id = X_org_id
1127 AND eec.change_notice = X_change_notice;
1128
1129 l_requester_name := FND_GLOBAL.USER_NAME;
1130
1131 wf_engine.setitemattrtext(itemtype => itemtype,
1132 itemkey => itemkey,
1133 aname => '#WF_SIGN_REQUESTER',
1134 avalue => l_requester_name );
1135
1136 Wf_Engine.SetItemAttrNumber(itemtype => itemtype,
1137 itemkey => itemkey,
1138 aname => 'CHANGE_ID',
1139 avalue => X_change_id);
1140
1141 -- Attachments hookup calls
1142 wf_engine.setitemattrtext(itemtype => itemtype,
1143 itemkey => itemkey,
1144 aname => '#ATTACHMENTS',
1145 avalue => 'FND:entity=ENG_ENGINEERING_CHANGES'||'&'||'pk1name=CHANGE_ID'||'&'||'pk1value='||X_change_id);
1146
1147
1148 /* Generate XML form business event*/
1149
1150 i_param_list := wf_parameter_list_t();
1151
1152 wf_event.addParameterToList(p_name => 'ECX_MAP_CODE',
1153 p_value => 'oracle.apps.eng.ecoGeneric',
1154 p_parameterlist => i_param_list);
1155
1156 wf_event.addParameterToList(p_name => 'ECX_DEBUG_LEVEL',
1157 p_value => 5,
1158 p_parameterlist => i_param_list);
1159
1160 wf_event.AddParameterToList('ECX_DOCUMENT_ID', X_change_id,i_param_list);
1161
1162 p_xmldoc := ecx_standard.GENERATE(p_event_name => 'oracle.apps.eng.ecoApproval',
1163 p_event_key => X_change_id,
1164 p_parameter_list => i_param_list
1165 ) ;
1166
1167 /* Generate Event Payload */
1168
1169 l_psig_event := wf_engine.getItemAttrEvent(itemtype, itemkey, '#PSIG_EVENT');
1170
1171 l_psig_event.setEventName('oracle.apps.eng.ecoApproval');
1172 l_psig_event.setEventKey(X_change_id);
1173 l_psig_event.setEventData(p_xmldoc);
1174
1175 wf_engine.setItemAttrEvent(itemtype, itemkey,'#PSIG_EVENT',l_psig_event);
1176
1177 /* get the From attributes */
1178
1179 wf_event.addParameterToList(p_name => '#FROM_ROLE',
1180 p_value => l_requester_name,
1181 p_parameterlist => i_param_list);
1182
1183
1184 result := 'COMPLETE:ASSIGNED ERES ATTRIBUTES';
1185 RETURN;
1186 --
1187 -- CANCEL mode
1188 --
1189 -- This event point is called when the activity must
1190 -- be undone, for example when a process is reset to an earlier point
1191 -- due to a loop back.
1192 --
1193 ELSIF (funcmode = 'CANCEL') THEN
1194 result := 'COMPLETE';
1195 return;
1196 END IF;
1197
1198 --
1199 -- Other execution modes may be created in the future. Your
1200 -- activity will indicate that it does not implement a mode
1201 -- by returning null
1202 --
1203 result := '';
1204 RETURN;
1205
1206 EXCEPTION
1207 WHEN OTHERS THEN
1208 -- The line below records this function call in the error system
1209 -- in the case of an exception.
1210 WF_CORE.Context('ECO_APP', 'Get_ERES_Attributes',
1211 itemtype, itemkey, to_char(actid), funcmode);
1212 RAISE;
1213
1214 END Get_ERES_Attributes;
1215
1216 -- VoteForResultType
1217 -- Standard Voting Function
1218 -- IN
1219 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1223 -- OUT
1220 -- itemkey - A string generated from the application object's primary key.
1221 -- actid - The process activity(instance id).
1222 -- funcmode - Run/Cancel
1224 -- result -
1225 --
1226 -- USED BY ACTIVITIES
1227 --
1228 -- WFSTD.VoteForResultType
1229 --
1230 -- ACTIVITY ATTRIBUTES REFERENCED
1231 -- VOTING_OPTION
1235 -- - voting percentages are calculated as a
1232 -- - WAIT_FOR_ALL_VOTES - Evaluate voting after all votes are cast
1233 -- - or a Timeout condition closes the voting
1234 -- - polls. When a Timeout occurs the
1236 -- - percentage ofvotes cast.
1237 --
1238 -- - REQUIRE_ALL_VOTES - Evaluate voting after all votes are cast.
1239 -- - If a Timeout occurs and all votes have not
1240 -- - been cast then the standard timeout
1241 -- - transition is taken. Votes are calculated
1242 -- - as a percenatage of users notified to vote.
1243 --
1244 -- - TALLY_ON_EVERY_VOTE - Evaluate voting after every vote or a
1245 -- - Timeout condition closes the voting polls.
1246 -- - After every vote voting percentages are
1247 -- - calculated as a percentage of user notified
1248 -- - to vote. After a timeout voting
1249 -- - percentages are calculated as a percentage
1250 -- - of votes cast.
1251 --
1252 -- "One attribute for each of the activities result type codes"
1253 --
1254 -- - The standard Activity VOTEFORRESULTTYPE has the WFSTD_YES_NO
1255 -- - result type assigned.
1256 -- - Thefore activity has two activity attributes.
1257 --
1258 -- Y - Percenatage required for Yes transition
1259 -- N - Percentage required for No transition
1260 --
1261 procedure VoteForResultType( itemtype in varchar2,
1262 itemkey in varchar2,
1263 actid in number,
1264 funcmode in varchar2,
1265 resultout in out nocopy varchar2)
1266 is
1267 -- Select all lookup codes for an activities result type
1268 cursor result_codes is
1269 select wfl.lookup_code result_code
1270 from wf_lookups wfl,
1271 wf_activities wfa,
1272 wf_process_activities wfpa,
1273 wf_items wfi where wfl.lookup_type = wfa.result_type
1274 and wfa.name = wfpa.activity_name
1275 and wfi.begin_date >= wfa.begin_date
1276 and wfi.begin_date < nvl(wfa.end_date,wfi.begin_date+1)
1277 and wfpa.activity_item_type = wfa.item_type
1278 and wfpa.instance_id = actid
1279 and wfi.item_key = itemkey
1280 and wfi.item_type = itemtype;
1281
1282 l_code_count pls_integer;
1283 l_group_id pls_integer;
1284 l_user varchar2(320);
1285 l_voting_option varchar2(30);
1286 l_per_of_total number;
1287 l_per_of_vote number;
1288 l_per_code number;
1289 per_success number;
1290 max_default pls_integer := 0;
1291 default_result varchar2(30) := '';
1292 result varchar2(30) := '';
1293 wf_invalid_command exception;
1294
1295 l_resultout VARCHAR2(2000);
1296 l_response_read varchar2(2);
1297 begin
1298
1299 --
1300 -- Added read_response check to fix bug 3610452
1301 -- The attribute stores user's response of "I have read the e-record".
1302 -- if the answer is No, then raise application error
1303 --
1304 IF (funcmode = 'RESPOND') THEN
1305 l_response_read := wf_notification.getattrtext(wf_engine.context_nid, 'READ_RESPONSE');
1306 IF (l_response_read = 'N') THEN
1307 WF_CORE.CONTEXT('ENG_WORKFLOW_API_PKG', 'VoteForResultType',itemtype, itemkey,
1308 FND_MESSAGE.GET_STRING('EDR','EDR_EREC_NOT_REVIEWED_ERR'));
1309 raise_application_error(-20002,FND_MESSAGE.GET_STRING('EDR','EDR_EREC_NOT_REVIEWED_ERR'));
1310 END IF;
1311 END IF;
1312
1313
1314 /* Call ERES api to start the eRecord generation process */
1315 UPDATE_EVIDENCE (p_itemtype =>itemtype,
1316 p_itemkey =>itemkey,
1317 p_actid =>actid,
1318 p_funcmode => funcmode,
1319 p_resultout => l_resultout);
1320
1321
1322
1323 -- Do nothing unless in RUN or TIMEOUT modes
1324 if (funcmode <> wf_engine.eng_run)
1325 and (funcmode <> wf_engine.eng_timeout) then
1326 resultout := wf_engine.eng_null;
1327 return;
1328 end if;
1329
1330 -- SYNCHMODE: Not allowed
1331 if (itemkey = wf_engine.eng_synch) then
1332 Wf_Core.Token('OPERATION', 'Wf_Standard.VotForResultType');
1333 Wf_Core.Raise('WFENG_SYNCH_DISABLED');
1334 end if;
1335
1336 -- Get Notifications group_id for activity
1337 Wf_Item_Activity_Status.Notification_Status(itemtype,itemkey,actid,
1338 l_group_id,l_user);
1339 l_voting_option := Wf_Engine.GetActivityAttrText(itemtype,itemkey,
1340 actid,'VOTING_OPTION');
1341 if (l_voting_option not in ('REQUIRE_ALL_VOTES', 'WAIT_FOR_ALL_VOTES',
1342 'TALLY_ON_EVERY_VOTE')) then
1343 raise wf_invalid_command;
1344 end if;
1345
1346 -- If the mode is one of:
1347 -- a. REQUIRE_ALL_VOTES
1348 -- b. WAIT_FOR_ALL_VOTES and no timeout has occurred
1349 -- and there are still open notifications, then return WAITING to
1350 -- either continue voting (in run mode) or trigger timeout processing
1351 -- (in timeout mode).
1352 if ((l_voting_option = 'REQUIRE_ALL_VOTES') or
1353 ((funcmode = wf_engine.eng_run) and
1354 (l_voting_option = 'WAIT_FOR_ALL_VOTES'))) then
1355 if (wf_notification.OpenNotificationsExist(l_group_id)) then
1356 resultout := wf_engine.eng_waiting;
1357 return;
1358 end if;
1362 -- a. TALLY_ON_ALL_VOTES
1359 end if;
1360
1361 -- If here, then the mode is one of:
1363 -- b. WAIT_FOR_ALL_VOTES and timeout has occurred
1364 -- c. WAIT_FOR_ALL_VOTES and all votes are cast
1365 -- d. REQUIRE_ALL_VOTES and all votes are cast
1366 -- Tally votes.
1367 for result_rec in result_codes loop
1368 -- Tally Vote Count for this result code
1369 Wf_Notification.VoteCount(l_group_id,result_rec.result_code,
1370 l_code_count,l_per_of_total,l_per_of_vote);
1371
1375 l_per_code := l_per_of_vote;
1372 -- If this is timeout mode, then use the percent of votes cast so far.
1373 -- If this is run mode, then use the percent of total votes possible.
1374 if (funcmode = wf_engine.eng_timeout) then
1376 else
1377 l_per_code := l_per_of_total;
1378 end if;
1379
1380 -- Get percent vote needed for this result to succeed
1381 per_success := Wf_Engine.GetActivityAttrNumber(itemtype,itemkey,
1382 actid,result_rec.result_code);
1383
1384 if (per_success is null) then
1385 -- Null value means this is a default result.
1386 -- Save the default result with max code_count.
1387 if (l_code_count > max_default) then
1388 max_default := l_code_count;
1389 default_result := result_rec.result_code;
1390 elsif (l_code_count = max_default) then
1391 -- Tie for default result.
1392 default_result := wf_engine.eng_tie;
1393 end if;
1394 else
1395 -- If:
1396 -- a. % vote for this result > % needed for success OR
1397 -- b. % vote is 100% AND
1398 -- c. at least 1 vote for this result
1399 -- then this result succeeds.
1400 if (((l_per_code > per_success) or (l_per_code = 100)) and
1401 (l_code_count > 0))
1402 then
1403 if (result is null) then
1404 -- Save satisfied result.
1405 result := result_rec.result_code;
1406 else
1407 -- This is the second result to be satisfied. Return a tie.
1408 resultout := wf_engine.eng_completed||':'||wf_engine.eng_tie;
1409 return;
1410 end if;
1411 end if;
1412 end if;
1413 end loop;
1414
1415 if (result is not null) then
1416
1417 -- Return the satisfied result code.
1418 resultout := wf_engine.eng_completed||':'||result;
1419 else
1420 -- If we get here no non-default results were satisfied.
1421 if (funcmode = wf_engine.eng_run and
1422 wf_notification.OpenNotificationsExist(l_group_id)) then
1423 -- Not timed out and still open notifications.
1424 -- Return waiting to continue voting.
1425 resultout := wf_engine.eng_waiting;
1426 elsif (default_result is not null) then
1427 -- Either timeout or all notifications closed
1428 -- Return default result if one found.
1429 resultout := wf_engine.eng_completed||':'||default_result;
1430 elsif (funcmode = wf_engine.eng_timeout) then
1431 -- If Timeout has occured then return result Timeout so the Timeout
1432 -- transition will occur - BUG2885157
1433 resultout := wf_engine.eng_completed||':'||wf_engine.eng_timeout;
1434 else
1435 -- All notifications closed, and no default.
1436 -- Return nomatch
1437
1438
1439 resultout := wf_engine.eng_completed||':'||wf_engine.eng_nomatch;
1440
1441 end if;
1442 end if;
1443
1444 return;
1445 exception
1446 when wf_invalid_command then
1447 Wf_Core.Context('Wf_Standard', 'VoteForResultType', itemtype,
1448 itemkey, to_char(actid), funcmode);
1449 Wf_Core.Token('COMMAND', l_voting_option);
1450 Wf_Core.Raise('WFSQL_COMMAND');
1451 when others then
1452 Wf_Core.Context('Wf_Standard', 'VoteForResultType',itemtype,
1453 itemkey, to_char(actid), funcmode);
1454 raise;
1455 end VoteForResultType;
1456
1457 /* ************************************************************************
1458 This procedure will close the ERES document once the document is finished.
1459 i.e all singers viewed and signed the eRecord
1460 Also the document will be Acknowledgement with the corrected status
1461
1462 ************************************************************************ */
1463
1464
1465 PROCEDURE CLOSE_AND_ACK_ERES_DOC (p_itemtype IN VARCHAR2,
1466 p_itemkey IN VARCHAR2,
1467 p_actid IN NUMBER,
1468 p_funcmode IN VARCHAR2,
1469 p_resultout OUT NOCOPY VARCHAR2
1470 ) IS
1471
1472 l_Event_key NUMBER;
1473 l_Event_name varchar2(240) := 'oracle.apps.eng.ecoApproval';
1474
1475 l_doc_id number;
1476 l_msg_count number;
1477 l_msg_data varchar2(1000);
1481 l_return_status VARCHAR2(1);
1478
1479
1480 l_erecord_id NUMBER;
1482 l_trans_status VARCHAR2(30);
1483 l_send_ackn boolean;
1484 l_autonomous_commit VARCHAR2(1);
1485
1486
1487 BEGIN
1488 IF P_FUNCMODE ='RUN' THEN
1489
1490
1491 /***** ERES: closeDocument ******/
1492
1493 l_doc_id := wf_engine.GETITEMATTRNUMBER(itemtype => p_itemtype,
1494 itemkey => p_itemkey,
1495 aname => 'DOC_ID');
1496 l_Event_Key := wf_engine.GETITEMATTRNUMBER(itemtype => p_itemtype,
1497 itemkey => p_itemkey,
1498 aname => 'CHANGE_ID');
1499 QA_EDR_STANDARD.Close_Document (
1500 p_api_version => 1.0,
1501 p_init_msg_list => 'T',
1502 p_commit => 'FALSE',
1503 x_return_status => l_return_status,
1504 x_msg_count => l_msg_count,
1505 x_msg_data => l_msg_data,
1506 P_DOCUMENT_ID => l_doc_id
1507 );
1508 -- update edr_psig_documents set psig_xml=psig_document where document_id=l_doc_id;
1509
1510 l_erecord_id := l_doc_id;
1511 IF l_erecord_id IS NOT NULL
1512 THEN
1513 l_send_ackn := TRUE;
1514 l_trans_status := 'SUCCESS';
1515 l_autonomous_commit := 'F';
1516 ELSE
1517 l_send_ackn := TRUE;
1518 l_trans_status := 'ERROR';
1519 l_autonomous_commit := 'T';
1520 END IF;
1521
1522 IF l_send_ackn = TRUE
1523 then
1524 QA_EDR_STANDARD.SEND_ACKN
1525 (p_api_version => 1.0
1526 ,p_init_msg_list => 'T'
1527 ,x_return_status => l_return_status
1528 ,x_msg_count => l_msg_count
1529 ,x_msg_data => l_msg_data
1530 ,p_event_name => l_Event_name
1531 ,p_event_key => l_Event_Key
1532 ,p_erecord_id => l_erecord_id
1533 ,p_trans_status => l_trans_status
1534 ,p_ackn_by => 'ECO APPROVAL WORKFLOW'
1535 ,p_ackn_note => 'WF Acknowledgement'
1536 ,p_autonomous_commit=> l_autonomous_commit
1537 );
1538 END IF;
1539
1540 p_resultout := 'COMPLETE:Closed ERES Document and Acknowledged';
1541 END IF;
1542
1543 EXCEPTION
1544 WHEN OTHERS THEN
1545 WF_CORE.CONTEXT ('ENG_WORKFLOW_API_PKG','CLOSE_AND_ACK_ERES_DOC',
1546 p_itemtype,p_itemkey,SQLERRM);
1547 raise;
1548
1549 END CLOSE_AND_ACK_ERES_DOC;
1550
1551
1552 END ENG_WORKFLOW_API_PKG;
[Home] [Help]