1 PACKAGE BODY ENG_WORKFLOW_API_PKG as
2 /* $Header: engwkfwb.pls 120.5.12020000.3 2013/01/04 09:59:20 ntungare ship $ */
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',
410 avalue => X_attribute4);
411 Wf_Engine.SetItemAttrText(itemtype => itemtype,
412 itemkey => itemkey,
413 aname => 'ATTRIBUTE5',
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',
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 */
485 Wf_Engine.SetItemAttrNumber(itemtype=> itemtype,
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
560 UPDATE eng_engineering_changes
561 SET approval_status_type = 5,
562 approval_date = sysdate
563 WHERE organization_id = X_org_id
564 AND change_notice = X_change_notice;
565
566 UPDATE eng_revised_items
567 SET status_type = 4 /* Set Rev Item Status = Scheduled */
568 ,status_code = 4 --Bug 3526627: Changes for 11.5.10, set the status_code also
569 WHERE change_notice = X_change_notice
570 AND organization_id = X_org_id
571 AND status_type = 1; /* Rev Item Status = Open */
572
573 --bug 2307416
574 UPDATE eng_engineering_changes
575 SET status_type = 4
576 ,status_code = 4 --Bug 3526627: Changes for 11.5.10, set the status_code also
577 WHERE change_notice = X_change_notice
578 AND organization_id = X_org_id
579 AND status_type = 1;
580
581 commit;
582 <<end_procedure>>
583 result := 'COMPLETE:APPROVED';
584 RETURN;
585
586 --
587 -- CANCEL mode
588 --
589 -- This event point is called when the activity must
590 -- be undone, for example when a process is reset to an earlier point
591 -- due to a loop back.
592 --
593 ELSIF (funcmode = 'CANCEL') THEN
594 result := 'COMPLETE';
595 return;
596 END IF;
597
598 --
599 -- Other execution modes may be created in the future. Your
600 -- activity will indicate that it does not implement a mode
601 -- by returning null
602 --
603 result := '';
604 RETURN;
605
606 EXCEPTION
607 WHEN OTHERS THEN
608 -- The line below records this function call in the error system
609 -- in the case of an exception.
610 WF_CORE.Context('ECO_APP', 'Approve_Eco',
611 itemtype, itemkey, to_char(actid), funcmode);
612 RAISE;
613
614 END Approve_Eco;
615
616 /* ************************************************************************
617 This procedure updates an ECO's Approval Status to "Rejected".
618 The Approval Date is set to null.
619 ************************************************************************ */
620
621 PROCEDURE Reject_Eco( itemtype IN VARCHAR2,
622 itemkey IN VARCHAR2,
623 actid IN NUMBER,
624 funcmode IN VARCHAR2,
625 result IN OUT NOCOPY VARCHAR2)
626 IS
627 X_eco_result VARCHAR2(2000);
628 BEGIN
629 --
630 -- RUN mode - normal process execution
631 --
632 IF (funcmode = 'RUN') THEN
633 Get_ECO_and_OrgId(itemtype => itemtype,
634 itemkey => itemkey,
635 actid => actid,
636 funcmode => funcmode,
637 result => X_eco_result);
638 UPDATE eng_engineering_changes
639 SET approval_status_type = 4,
640 approval_date = null
641 WHERE organization_id = X_org_id
642 AND change_notice = X_change_notice;
643
644 commit;
645 <<end_procedure>>
646 result := 'COMPLETE:REJECTED';
647 RETURN;
648 --
649 -- CANCEL mode
650 --
651 -- This event point is called when the activity must
652 -- be undone, for example when a process is reset to an earlier point
653 -- due to a loop back.
654 --
655 ELSIF (funcmode = 'CANCEL') THEN
656 result := 'COMPLETE';
657 RETURN;
658 END IF;
659
660 --
661 -- Other execution modes may be created in the future. Your
662 -- activity will indicate that it does not implement a mode
663 -- by returning null
664 --
665 result := '';
666 RETURN;
667
668 EXCEPTION
669 WHEN OTHERS THEN
670 -- The line below records this function call in the error system
671 -- in the case of an exception.
672 WF_CORE.Context('ECO_APP', 'Reject_Eco',
673 itemtype, itemkey, to_char(actid), funcmode);
674 RAISE;
675 END Reject_Eco;
676
677
678 /* ************************************************************************
679 This procedure sets an ECO's Approval Status to 'Processing Error'. This
680 procedure is meant to be used in the Default Error Process.
681 ************************************************************************ */
682
683 PROCEDURE Set_Eco_Approval_Error(itemtype IN VARCHAR2,
684 itemkey IN VARCHAR2,
685 actid IN NUMBER,
686 funcmode IN VARCHAR2,
687 result IN OUT NOCOPY VARCHAR2) IS
688
689 X_itemkey VARCHAR2(80);
690 X_itemtype VARCHAR2(80);
691 X_eco_result VARCHAR2(2000);
692 BEGIN
693 --
694 -- RUN mode - normal process execution
695 --
696 IF (funcmode = 'RUN') THEN
697 X_itemkey := Wf_Engine.GetItemAttrText(itemtype => itemtype,
698 itemkey => itemkey,
699 aname => 'ERROR_ITEM_KEY');
700 X_itemtype := Wf_Engine.GetItemAttrText(itemtype => itemtype,
701 itemkey => itemkey,
702 aname => 'ERROR_ITEM_TYPE');
703 Get_ECO_and_OrgId(itemtype => X_itemtype,
704 itemkey => X_itemkey,
705 actid => actid,
706 funcmode => funcmode,
707 result => X_eco_result);
708 UPDATE eng_engineering_changes
709 SET approval_status_type = 7,
710 approval_date = ''
711 WHERE organization_id = X_org_id
712 AND change_notice = X_change_notice;
713
714 result := 'COMPLETE:ERRORED';
715 RETURN;
716 --
717 -- CANCEL mode
718 --
719 -- This event point is called when the activity must
720 -- be undone, for example when a process is reset to an earlier point
721 -- due to a loop back.
722 --
723 ELSIF (funcmode = 'CANCEL') THEN
724 result := 'COMPLETE';
725 RETURN;
726 END IF;
727
728 <<end_procedure>>
729 --
730 -- Other execution modes may be created in the future. Your
731 -- activity will indicate that it does not implement a mode
732 -- by returning null
733 --
734 result := '';
735 RETURN;
736
737 EXCEPTION
738 WHEN OTHERS THEN
739 -- The line below records this function call in the error system
740 -- in the case of an exception.
741 WF_CORE.Context('ECO_APP', 'Set_Eco_Approval_Error',
742 itemtype, itemkey, to_char(actid), funcmode);
743 RAISE;
744 END Set_Eco_Approval_Error;
745
746
747 /* ************************************************************************
748 This procedure updates the MRP Active flag to 'Yes' for all the revised
749 items for a given ECO only if the revised item is at Status 'Open' or
750 'Scheduled'.
751 ************************************************************************ */
752
753 PROCEDURE Set_Mrp_Active( itemtype IN VARCHAR2,
754 itemkey IN VARCHAR2,
755 actid IN NUMBER,
756 funcmode IN VARCHAR2,
757 result IN OUT NOCOPY VARCHAR2)
758 IS
759 X_eco_result VARCHAR2(2000);
760 l_duplicate NUMBER := 0; --Changes from bug 7602108 which are merged in bug 9726856
761
762 --changes for bug 9726856 begin
763 l_rev_seq_id NUMBER;
764 other_rev_seq_id NUMBER;
765 l_bill_seq_id NUMBER;
766 l_comp_item_id NUMBER;
767 m_bill_seq_id NUMBER;
768 m_comp_item_id NUMBER;
769 found_duplicate NUMBER := 0;
770 num_comp_other_rev_item NUMBER;
771 num_comp_rev_item NUMBER;
772 found_dup_rev_wo_comp NUMBER := 0;
773
774 Cursor c_rev_items (c_change_notice VARCHAR2, c_org_id NUMBER)
775 IS
776 select revised_item_sequence_id from eng_revised_items
777 where change_notice = c_change_notice
778 and organization_id = c_org_id;
779
780 Cursor c_dup_rev_items (c_rev_item_seq_id NUMBER, c_change_notice VARCHAR2, c_org_id NUMBER)
781 IS
782 select eri1.revised_item_sequence_id
783 from eng_revised_items eri, eng_revised_items eri1
784 where eri.change_notice = c_change_notice
785 AND eri.organization_id = c_org_id
786 AND eri.revised_item_sequence_id = c_rev_item_seq_id
787 AND eri1.organization_id = c_org_id
788 AND eri1.revised_item_sequence_id <> eri.revised_item_sequence_id
789 AND eri1.revised_item_id = eri.revised_item_id
790 AND eri1.scheduled_date = eri.scheduled_date
791 AND eri1.mrp_active = 1
792 AND eri1.status_type <> 5;
793
794 Cursor c_comp_on_rev_item (c_rev_item_seq_id NUMBER)
795 IS
796 Select bill_sequence_id, component_item_id from bom_components_b
797 where revised_item_sequence_id = c_rev_item_seq_id;
798
799 Cursor c_comp_on_rev_item_dup (c_rev_item_seq_id NUMBER)
800 IS
801 Select bill_sequence_id, component_item_id from bom_components_b
802 where revised_item_sequence_id = c_rev_item_seq_id;
803 --changes for bug 9726856 end
804
805 BEGIN
806 --
807 -- RUN mode - normal process execution
808 --
809 IF (funcmode = 'RUN') THEN
810 Get_ECO_and_OrgId(itemtype => itemtype,
811 itemkey => itemkey,
812 actid => actid,
813 funcmode => funcmode,
814 result => X_eco_result);
815
816 /* --Changes from bug 7602108 which are merged in bug 9726856 */
817 SELECT COUNT(*)
818 INTO l_duplicate
819 FROM eng_revised_items eri,
820 eng_revised_items eri1
821 WHERE eri.change_notice = X_change_notice
822 AND eri.organization_id = X_org_id
823 AND eri1.organization_id = X_org_id
824 AND eri1.revised_item_sequence_id <> eri.revised_item_sequence_id
825 AND eri1.revised_item_id = eri.revised_item_id
826 AND eri1.scheduled_date = eri.scheduled_date
827 AND eri1.mrp_active = 1
828 AND eri1.status_type <> 5; -- Not Cancelled
829
830 if (l_duplicate <> 0) then
831 --changes for bug 9726856 begin
832 --more tests to see if the revised components in duplicate revised items overlap as well
833 open c_rev_items (X_change_notice, X_org_id);
834 loop
835 fetch c_rev_items into l_rev_seq_id;
836 exit when c_rev_items%NOTFOUND;
837
838 Select count(*) into num_comp_rev_item from bom_components_b
839 where revised_item_sequence_id = l_rev_seq_id;
840
841 open c_dup_rev_items (l_rev_seq_id, X_change_notice, X_org_id);
842 loop
843 fetch c_dup_rev_items into other_rev_seq_id;
844 exit when c_dup_rev_items%NOTFOUND;
845
846 --if neither revised_item has any components then we cannot allow
847 --duplication of revised items since the quantity of revised item will
848 -- be inflated due to multiple unimplemented ecos
849
850 Select count(*) into num_comp_other_rev_item from bom_components_b
851 where revised_item_sequence_id = other_rev_seq_id;
852
853 --our revised item under consideration has no components
854 --and there exists at least another revised item that has no components
855 --either but their scheduled_date match
856 --we cannot allow this and hence, throw error
857 if ((num_comp_other_rev_item = 0 ) and (num_comp_rev_item = 0))then
858 found_dup_rev_wo_comp := 1;
859 exit;
860 else
861 --now that you have found the relevant revised items
862 --get the components on the original revised item
863 open c_comp_on_rev_item (l_rev_seq_id);
864 loop
865 fetch c_comp_on_rev_item into l_bill_seq_id, l_comp_item_id;
866 exit when c_comp_on_rev_item%NOTFOUND;
867
868 open c_comp_on_rev_item_dup (other_rev_seq_id);
869 loop
870 fetch c_comp_on_rev_item_dup into m_bill_seq_id, m_comp_item_id;
871
872 exit when c_comp_on_rev_item_dup%NOTFOUND;
873 --finding one duplicate is good enough, you can exit loop
874 if l_bill_seq_id = m_bill_seq_id and l_comp_item_id = m_comp_item_id then
875 found_duplicate := 1;
876 exit;
877 end if;
878 end loop;
879 close c_comp_on_rev_item_dup;
880 if found_duplicate = 1 then
881 exit;
882 end if;
883
884 end loop;
885 close c_comp_on_rev_item;
886 end if; --when there exists components on the revised items
887 if found_duplicate = 1 or found_dup_rev_wo_comp = 1 then
888 exit;
889 end if;
890 end loop;
891 close c_dup_rev_items;
892 if found_duplicate = 1 or found_dup_rev_wo_comp = 1 then
893 exit;
894 end if;
895 end loop;
896 close c_rev_items;
897 --raise error only now
898 if found_duplicate = 1 or found_dup_rev_wo_comp = 1 then
899 result := 'ERROR:DUPLICATE';
900 RETURN;
901 else
902 UPDATE eng_revised_items
903 SET mrp_active = 1 /* Set MRP Active=Yes */
904 WHERE change_notice = X_change_notice
905 AND organization_id = X_org_id
906 AND status_type in (1, 4); /* Rev Item Status=Open or Scheduled */
907 commit;
908 end if;
909 --changes for bug 9726856 end
910 --result := 'ERROR:DUPLICATE'; --error check outside is commented out
911 --RETURN;
912 else
913 UPDATE eng_revised_items
914 SET mrp_active = 1 /* Set MRP Active=Yes */
915 WHERE change_notice = X_change_notice
916 AND organization_id = X_org_id
917 AND status_type in (1, 4); /* Rev Item Status=Open or Scheduled */
918
919 commit;
920 end if;
921
922 <<end_procedure>>
923 result := 'COMPLETE:MRP ACTIVE';
924 RETURN;
925 --
926 -- CANCEL mode
927 --
928 -- This event point is called when the activity must
929 -- be undone, for example when a process is reset to an earlier point
930 -- due to a loop back.
931 --
932 ELSIF (funcmode = 'CANCEL') THEN
933 result := 'COMPLETE';
934 RETURN;
935 END IF;
936
937 --
938 -- Other execution modes may be created in the future. Your
939 -- activity will indicate that it does not implement a mode
940 -- by returning null
941 --
942 result := '';
943 RETURN;
944
945 EXCEPTION
946 WHEN OTHERS THEN
947 -- The line below records this function call in the error system
948 -- in the case of an exception.
949 WF_CORE.Context('ECO_APP', 'Set_Mrp_Active',
950 itemtype, itemkey, to_char(actid), funcmode);
951 RAISE;
952
953 END Set_Mrp_Active;
954
955
956 /* ************************************************************************
957 This procedure updates the MRP Active flag to 'No' for all the revised
958 items for a given ECO only if the revised item is at Status 'Open' or
959 'Scheduled'.
960 ************************************************************************ */
961
962 PROCEDURE Set_Mrp_Inactive( itemtype IN VARCHAR2,
963 itemkey IN VARCHAR2,
964 actid IN NUMBER,
965 funcmode IN VARCHAR2,
966 result IN OUT NOCOPY VARCHAR2)
967 IS
968 X_eco_result VARCHAR2(2000);
969 BEGIN
970 --
971 -- RUN mode - normal process execution
972 --
973 IF (funcmode = 'RUN') THEN
974 Get_ECO_and_OrgId(itemtype => itemtype,
975 itemkey => itemkey,
976 actid => actid,
977 funcmode => funcmode,
978 result => X_eco_result);
979 UPDATE eng_revised_items
980 SET mrp_active = 2 /* Set MRP Active=Yes */
981 WHERE change_notice = X_change_notice
982 AND organization_id = X_org_id
983 AND status_type in (1, 4); /* Rev Item Status=Open or Scheduled */
984
985 commit;
986 <<end_procedure>>
987
988 result := 'COMPLETE:MRP INACTIVE';
989 RETURN;
990 --
991 -- CANCEL mode
992 --
993 -- This event point is called when the activity must
994 -- be undone, for example when a process is reset to an earlier point
995 -- due to a loop back.
996 --
997 ELSIF (funcmode = 'CANCEL') THEN
998 result := 'COMPLETE';
999 RETURN;
1000 END IF;
1001
1002 --
1003 -- Other execution modes may be created in the future. Your
1004 -- activity will indicate that it does not implement a mode
1005 -- by returning null
1006 --
1007 result := '';
1008 RETURN;
1009
1010 EXCEPTION
1011 WHEN OTHERS THEN
1012 -- The line below records this function call in the error system
1013 -- in the case of an exception.
1014 WF_CORE.Context('ECO_APP', 'Set_Mrp_Inactive',
1015 itemtype, itemkey, to_char(actid), funcmode);
1016 RAISE;
1017 END Set_Mrp_Inactive;
1018
1019
1020 /************************************************
1021 This procedure will post ERES eRecord into the evidence store.
1022 For both Approve and Rejected case.
1023 *****************************************/
1024
1025 PROCEDURE UPDATE_EVIDENCE (p_itemtype IN VARCHAR2,
1026 p_itemkey IN VARCHAR2,
1027 p_actid IN NUMBER,
1028 p_funcmode IN VARCHAR2,
1029 p_resultout OUT NOCOPY VARCHAR2
1030 ) IS
1031
1032 l_requester varchar2(240);
1033 l_Event_key NUMBER;
1034 l_Event_name varchar2(240) := 'oracle.apps.eng.ecoApproval';
1035 l_change_notice varchar2(240);
1036 l_user_response varchar2(30);
1037
1038 l_doc_id number;
1039 l_error number;
1040 l_error_msg varchar2(4000);
1041 l_doc_params qa_edr_standard.params_tbl_type;
1042 l_sig_id number;
1043 l_notification_result varchar2(1000);
1044 l_ret_status varchar2(30);
1045 l_msg_count number;
1046 l_msg_data varchar2(1000);
1047 l_nid number;
1048
1049 l_eRecord_id NUMBER;
1050 l_return_status VARCHAR2(1);
1051 l_trans_status VARCHAR2(30);
1052 l_msg_index NUMBER;
1053 l_send_ackn boolean;
1054 l_autonomous_commit VARCHAR2(1);
1055
1056 l_eres_doc NUMBER;
1057
1058 l_parameters qa_edr_standard.Params_tbl_type;
1059 l_sign_params qa_edr_standard.Params_tbl_type;
1060
1061 BEGIN
1062 IF P_FUNCMODE ='RUN' THEN
1063
1064 l_requester := FND_GLOBAL.USER_NAME;
1065
1066 l_Event_Key := wf_engine.GETITEMATTRNUMBER(itemtype => p_itemtype,
1067 itemkey => p_itemkey,
1068 aname => 'CHANGE_ID');
1069
1070 l_eres_doc := wf_engine.GETITEMATTRNUMBER(itemtype => p_itemtype,
1071 itemkey => p_itemkey,
1072 aname => 'OPEN_ERES_DOC');
1073
1074 l_change_notice := wf_engine.GETITEMATTRTEXT(itemtype => p_itemtype,
1075 itemkey => p_itemkey,
1076 aname => 'CHANGE_NOTICE');
1077
1078 IF l_eres_doc = 0
1079 then
1080
1081 -- This is only done once, flag l_eres_doc is used to control this
1082 -- One eRecord is generated per approval workflow notification, therefor
1083 -- we call open_Documentjust the once
1084
1085 /* Getting Notification Id */
1086 SELECT NOTIFICATION_ID
1087 INTO l_nid
1088 FROM WF_ITEM_ACTIVITY_STATUSES
1089 WHERE ITEM_KEY = p_itemkey
1090 AND ITEM_TYPE = p_itemtype
1091 AND NOTIFICATION_ID IS NOT NULL;
1092
1093
1094 /***** opendocument ******/
1095
1096 --
1097 -- Bug 16063500
1098 -- The ECO Approval event should use the document format text/plain
1099 -- As otherwise the E-Record cannot be printed.
1100 --
1101 qa_edr_standard.open_Document (
1102 p_api_version => 1.0,
1103 p_init_msg_list => 'T',
1104 p_commit => 'FALSE',
1105 x_return_status => l_return_status,
1106 x_msg_count => l_msg_count,
1107 x_msg_data => l_msg_data,
1108 P_PSIG_XML => NULL,
1109 P_PSIG_DOCUMENT => NULL,
1110 P_PSIG_DOCUMENTFORMAT => WF_NOTIFICATION.doc_text,
1111 P_PSIG_REQUESTER => l_requester,
1112 P_PSIG_SOURCE => NULL,
1113 P_EVENT_NAME => l_Event_name,
1114 P_EVENT_KEY => l_Event_Key,
1115 p_wf_notif_id => l_nid,
1116 X_DOCUMENT_ID => l_doc_id);
1117
1118
1119 /* Post document parameters */
1120
1121 l_parameters(1).param_name:='PSIG_USER_KEY_LABEL';
1122 FND_MESSAGE.SET_NAME('ENG','ENG_ECO_APPROVAL');
1123 l_parameters(1).param_value:=FND_MESSAGE.GET;
1124 l_parameters(1).param_displayname:=NULL;
1125
1126 l_parameters(2).param_name:='PSIG_USER_KEY_VALUE';
1127 l_parameters(2).param_value:= l_change_notice;
1128 l_parameters(2).param_displayname:=NULL;
1129
1130 /******** postDocumentParameter ************/
1131 qa_edr_standard.Post_DocumentParameters (
1132 p_api_version => 1.0,
1133 p_init_msg_list => 'T',
1134 p_commit => 'FALSE',
1135 x_return_status => l_return_status,
1136 x_msg_count => l_msg_count,
1137 x_msg_data => l_msg_data,
1138 p_document_id => l_doc_id,
1139 p_doc_parameters_tbl => l_parameters);
1140
1141 -- SET DOC ID
1142 Wf_Engine.SetItemAttrNumber(itemtype => p_itemtype,
1143 itemkey => p_itemkey,
1144 aname => 'DOC_ID',
1145 avalue => l_doc_id);
1146 end if;
1147 -- the rest below will be called many times, per # of signers
1148
1149 Wf_Engine.SetItemAttrNumber(itemtype => p_itemtype,
1150 itemkey => p_itemkey,
1151 aname => 'OPEN_ERES_DOC',
1152 avalue => 1);
1153
1154 l_doc_id := wf_engine.GETITEMATTRNUMBER(itemtype => p_itemtype,
1155 itemkey => p_itemkey,
1156 aname => 'DOC_ID');
1157
1158
1159 -- get the result of the notification i.e approve/rejected
1160 l_notification_result := wf_engine.GETITEMATTRTEXT(itemtype => p_itemtype,
1161 itemkey => p_itemkey,
1162 aname => 'RESULT');
1163
1164
1165 /* Post Signature Parameters */
1166
1167 -- Singning Reason
1168 l_sign_params(1).param_name:= 'REASON_CODE';
1169 l_sign_params(1).param_value:= wf_engine.getitemattrtext(p_itemtype,p_itemkey,'SIG_REASON');
1170 l_sign_params(1).param_displayname:= 'Signing Reason';
1171
1172 -- Signer comments
1173 l_sign_params(2).param_name:='SIGNERS_COMMENT';
1174 l_sign_params(2).param_value:= wf_engine.getitemattrtext(p_itemtype,p_itemkey,'SIGNERS_COMMENTS');
1175 l_sign_params(2).param_displayname:='Signer Comments';
1176
1177
1178 -- Signature Type
1179 l_sign_params(3).param_name:='WF_SIGNER_TYPE';
1180 l_sign_params(3).param_value:= wf_engine.getitemattrtext(p_itemtype,p_itemkey,'WF_SIGNER_TYPE');
1181 l_sign_params(3).param_displayname:='Signature Type ';
1182
1183 IF l_notification_result ='Y'
1184 THEN
1185 l_user_response := 'Approved';
1186 ELSE
1187 l_user_response := 'Rejected';
1188 END IF;
1189
1190 qa_edr_standard.Request_Signature (
1191 p_api_version => 1.0,
1192 p_init_msg_list => 'T',
1193 p_commit => 'FALSE',
1194 x_return_status => l_return_status,
1195 x_msg_count => l_msg_count,
1196 x_msg_data => l_msg_data,
1197 P_DOCUMENT_ID => l_doc_id,
1198 P_USER_NAME => l_requester,
1199 P_ORIGINAL_RECIPIENT => NULL,
1200 P_OVERRIDING_COMMENT => NULL,
1201 x_signature_id => l_sig_id
1202 );
1203
1204 qa_edr_standard.Post_Signature (
1205 p_api_version => 1.0,
1206 p_init_msg_list => 'T',
1207 p_commit => 'FALSE',
1208 x_return_status => l_return_status,
1209 x_msg_count => l_msg_count,
1210 x_msg_data => l_msg_data,
1211 P_DOCUMENT_ID => l_doc_id,
1212 p_evidenceStore_id => '1',
1213 P_USER_NAME => l_requester,
1214 P_USER_RESPONSE => l_user_response,
1215 P_ORIGINAL_RECIPIENT => NULL,
1216 P_OVERRIDING_COMMENT => NULL,
1217 x_signature_id => l_sig_id
1218 );
1219
1220 qa_edr_standard.Post_SignatureParameters (
1221 p_api_version => 1.0,
1222 p_init_msg_list => 'T',
1223 p_commit => 'FALSE',
1224 x_return_status => l_return_status,
1225 x_msg_count => l_msg_count,
1226 x_msg_data => l_msg_data,
1227 p_signature_id => l_sig_id,
1228 p_sig_parameters_tbl => l_sign_params
1229 );
1230
1231 END IF;
1232
1233 p_resultout := 'COMPLETE:UPDATE_EVIDENCE';
1234 EXCEPTION
1235 WHEN OTHERS THEN
1236 WF_CORE.CONTEXT ('ENG_WORKFLOW_API_PKG','UPDATE_EVIDENCE',
1237 p_itemtype,p_itemkey,SQLERRM);
1238 raise;
1239
1240 END UPDATE_EVIDENCE;
1241
1242
1243 /* ************************************************************************
1244 This procedure gets the ERES attribute values for the ERES process,
1245 and sets them in the Item Type Attributes.
1246 ************************************************************************ */
1247
1248 PROCEDURE Get_ERES_Attributes(itemtype IN VARCHAR2,
1249 itemkey IN VARCHAR2,
1250 actid IN NUMBER,
1251 funcmode IN VARCHAR2,
1252 result IN OUT NOCOPY VARCHAR2) IS
1253
1254 X_change_id NUMBER;
1255 l_requester_name VARCHAR2(60);
1256 i_param_list wf_parameter_list_t;
1257 p_xmldoc clob;
1258
1259 l_psig_event WF_EVENT_T;
1260 l_event_name varchar2(240);
1261 l_event_key varchar2(240);
1262
1263
1264 BEGIN
1265 --
1266 -- RUN mode - normal process execution
1267 --
1268 IF (funcmode = 'RUN') THEN
1269
1270 -- get Event Key
1271 SELECT
1272 eec.change_id
1273 INTO
1274 X_change_id
1275 FROM eng_engineering_changes eec
1276 WHERE eec.organization_id = X_org_id
1277 AND eec.change_notice = X_change_notice;
1278
1279 l_requester_name := FND_GLOBAL.USER_NAME;
1280
1281 wf_engine.setitemattrtext(itemtype => itemtype,
1282 itemkey => itemkey,
1283 aname => '#WF_SIGN_REQUESTER',
1284 avalue => l_requester_name );
1285
1286 Wf_Engine.SetItemAttrNumber(itemtype => itemtype,
1287 itemkey => itemkey,
1288 aname => 'CHANGE_ID',
1289 avalue => X_change_id);
1290
1291 -- Attachments hookup calls
1292 wf_engine.setitemattrtext(itemtype => itemtype,
1293 itemkey => itemkey,
1294 aname => '#ATTACHMENTS',
1295 avalue => 'FND:entity=ENG_ENGINEERING_CHANGES'||'&'||'pk1name=CHANGE_ID'||'&'||'pk1value='||X_change_id);
1296
1297
1298 /* Generate XML form business event*/
1299
1300 i_param_list := wf_parameter_list_t();
1301
1302 wf_event.addParameterToList(p_name => 'ECX_MAP_CODE',
1303 p_value => 'oracle.apps.eng.ecoGeneric',
1304 p_parameterlist => i_param_list);
1305
1306 wf_event.addParameterToList(p_name => 'ECX_DEBUG_LEVEL',
1307 p_value => 5,
1308 p_parameterlist => i_param_list);
1309
1310 wf_event.AddParameterToList('ECX_DOCUMENT_ID', X_change_id,i_param_list);
1311
1312 p_xmldoc := ecx_standard.GENERATE(p_event_name => 'oracle.apps.eng.ecoApproval',
1313 p_event_key => X_change_id,
1314 p_parameter_list => i_param_list
1315 ) ;
1316
1317 /* Generate Event Payload */
1318
1319 l_psig_event := wf_engine.getItemAttrEvent(itemtype, itemkey, '#PSIG_EVENT');
1320
1321 l_psig_event.setEventName('oracle.apps.eng.ecoApproval');
1322 l_psig_event.setEventKey(X_change_id);
1323 l_psig_event.setEventData(p_xmldoc);
1324
1325 wf_engine.setItemAttrEvent(itemtype, itemkey,'#PSIG_EVENT',l_psig_event);
1326
1327 /* get the From attributes */
1328
1329 wf_event.addParameterToList(p_name => '#FROM_ROLE',
1330 p_value => l_requester_name,
1331 p_parameterlist => i_param_list);
1332
1333
1334 result := 'COMPLETE:ASSIGNED ERES ATTRIBUTES';
1335 RETURN;
1336 --
1337 -- CANCEL mode
1338 --
1339 -- This event point is called when the activity must
1340 -- be undone, for example when a process is reset to an earlier point
1341 -- due to a loop back.
1342 --
1343 ELSIF (funcmode = 'CANCEL') THEN
1344 result := 'COMPLETE';
1345 return;
1346 END IF;
1347
1348 --
1349 -- Other execution modes may be created in the future. Your
1350 -- activity will indicate that it does not implement a mode
1351 -- by returning null
1352 --
1353 result := '';
1354 RETURN;
1355
1356 EXCEPTION
1357 WHEN OTHERS THEN
1358 -- The line below records this function call in the error system
1359 -- in the case of an exception.
1360 WF_CORE.Context('ECO_APP', 'Get_ERES_Attributes',
1361 itemtype, itemkey, to_char(actid), funcmode);
1362 RAISE;
1363
1364 END Get_ERES_Attributes;
1365
1366 -- VoteForResultType
1367 -- Standard Voting Function
1368 -- IN
1369 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1370 -- itemkey - A string generated from the application object's primary key.
1371 -- actid - The process activity(instance id).
1372 -- funcmode - Run/Cancel
1373 -- OUT
1374 -- result -
1375 --
1376 -- USED BY ACTIVITIES
1377 --
1378 -- WFSTD.VoteForResultType
1379 --
1380 -- ACTIVITY ATTRIBUTES REFERENCED
1381 -- VOTING_OPTION
1382 -- - WAIT_FOR_ALL_VOTES - Evaluate voting after all votes are cast
1383 -- - or a Timeout condition closes the voting
1384 -- - polls. When a Timeout occurs the
1385 -- - voting percentages are calculated as a
1386 -- - percentage ofvotes cast.
1387 --
1388 -- - REQUIRE_ALL_VOTES - Evaluate voting after all votes are cast.
1389 -- - If a Timeout occurs and all votes have not
1390 -- - been cast then the standard timeout
1391 -- - transition is taken. Votes are calculated
1392 -- - as a percenatage of users notified to vote.
1393 --
1394 -- - TALLY_ON_EVERY_VOTE - Evaluate voting after every vote or a
1395 -- - Timeout condition closes the voting polls.
1396 -- - After every vote voting percentages are
1397 -- - calculated as a percentage of user notified
1398 -- - to vote. After a timeout voting
1399 -- - percentages are calculated as a percentage
1400 -- - of votes cast.
1401 --
1402 -- "One attribute for each of the activities result type codes"
1403 --
1404 -- - The standard Activity VOTEFORRESULTTYPE has the WFSTD_YES_NO
1405 -- - result type assigned.
1406 -- - Thefore activity has two activity attributes.
1407 --
1408 -- Y - Percenatage required for Yes transition
1409 -- N - Percentage required for No transition
1410 --
1411 procedure VoteForResultType( itemtype in varchar2,
1412 itemkey in varchar2,
1413 actid in number,
1414 funcmode in varchar2,
1415 resultout in out nocopy varchar2)
1416 is
1417 -- Select all lookup codes for an activities result type
1418 cursor result_codes is
1419 select wfl.lookup_code result_code
1420 from wf_lookups wfl,
1421 wf_activities wfa,
1422 wf_process_activities wfpa,
1423 wf_items wfi where wfl.lookup_type = wfa.result_type
1424 and wfa.name = wfpa.activity_name
1425 and wfi.begin_date >= wfa.begin_date
1426 and wfi.begin_date < nvl(wfa.end_date,wfi.begin_date+1)
1427 and wfpa.activity_item_type = wfa.item_type
1428 and wfpa.instance_id = actid
1429 and wfi.item_key = itemkey
1430 and wfi.item_type = itemtype;
1431
1432 l_code_count pls_integer;
1433 l_group_id pls_integer;
1434 l_user varchar2(320);
1435 l_voting_option varchar2(30);
1436 l_per_of_total number;
1437 l_per_of_vote number;
1438 l_per_code number;
1439 per_success number;
1440 max_default pls_integer := 0;
1441 default_result varchar2(30) := '';
1442 result varchar2(30) := '';
1443 wf_invalid_command exception;
1444
1445 l_resultout VARCHAR2(2000);
1446 l_response_read varchar2(2);
1447 begin
1448
1449 --
1450 -- Added read_response check to fix bug 3610452
1451 -- The attribute stores user's response of "I have read the e-record".
1452 -- if the answer is No, then raise application error
1453 --
1454 IF (funcmode = 'RESPOND') THEN
1455 l_response_read := wf_notification.getattrtext(wf_engine.context_nid, 'READ_RESPONSE');
1456 IF (l_response_read = 'N') THEN
1457 WF_CORE.CONTEXT('ENG_WORKFLOW_API_PKG', 'VoteForResultType',itemtype, itemkey,
1458 FND_MESSAGE.GET_STRING('EDR','EDR_EREC_NOT_REVIEWED_ERR'));
1459 raise_application_error(-20002,FND_MESSAGE.GET_STRING('EDR','EDR_EREC_NOT_REVIEWED_ERR'));
1460 END IF;
1461 END IF;
1462
1463
1464 /* Call ERES api to start the eRecord generation process */
1465 UPDATE_EVIDENCE (p_itemtype =>itemtype,
1466 p_itemkey =>itemkey,
1467 p_actid =>actid,
1468 p_funcmode => funcmode,
1469 p_resultout => l_resultout);
1470
1471
1472
1473 -- Do nothing unless in RUN or TIMEOUT modes
1474 if (funcmode <> wf_engine.eng_run)
1475 and (funcmode <> wf_engine.eng_timeout) then
1476 resultout := wf_engine.eng_null;
1477 return;
1478 end if;
1479
1480 -- SYNCHMODE: Not allowed
1481 if (itemkey = wf_engine.eng_synch) then
1482 Wf_Core.Token('OPERATION', 'Wf_Standard.VotForResultType');
1483 Wf_Core.Raise('WFENG_SYNCH_DISABLED');
1484 end if;
1485
1486 -- Get Notifications group_id for activity
1487 Wf_Item_Activity_Status.Notification_Status(itemtype,itemkey,actid,
1488 l_group_id,l_user);
1489 l_voting_option := Wf_Engine.GetActivityAttrText(itemtype,itemkey,
1490 actid,'VOTING_OPTION');
1491 if (l_voting_option not in ('REQUIRE_ALL_VOTES', 'WAIT_FOR_ALL_VOTES',
1492 'TALLY_ON_EVERY_VOTE')) then
1493 raise wf_invalid_command;
1494 end if;
1495
1496 -- If the mode is one of:
1497 -- a. REQUIRE_ALL_VOTES
1498 -- b. WAIT_FOR_ALL_VOTES and no timeout has occurred
1499 -- and there are still open notifications, then return WAITING to
1500 -- either continue voting (in run mode) or trigger timeout processing
1501 -- (in timeout mode).
1502 if ((l_voting_option = 'REQUIRE_ALL_VOTES') or
1503 ((funcmode = wf_engine.eng_run) and
1504 (l_voting_option = 'WAIT_FOR_ALL_VOTES'))) then
1505 if (wf_notification.OpenNotificationsExist(l_group_id)) then
1506 resultout := wf_engine.eng_waiting;
1507 return;
1508 end if;
1509 end if;
1510
1511 -- If here, then the mode is one of:
1512 -- a. TALLY_ON_ALL_VOTES
1513 -- b. WAIT_FOR_ALL_VOTES and timeout has occurred
1514 -- c. WAIT_FOR_ALL_VOTES and all votes are cast
1515 -- d. REQUIRE_ALL_VOTES and all votes are cast
1516 -- Tally votes.
1517 for result_rec in result_codes loop
1518 -- Tally Vote Count for this result code
1519 Wf_Notification.VoteCount(l_group_id,result_rec.result_code,
1520 l_code_count,l_per_of_total,l_per_of_vote);
1521
1522 -- If this is timeout mode, then use the percent of votes cast so far.
1523 -- If this is run mode, then use the percent of total votes possible.
1524 if (funcmode = wf_engine.eng_timeout) then
1525 l_per_code := l_per_of_vote;
1526 else
1527 l_per_code := l_per_of_total;
1528 end if;
1529
1530 -- Get percent vote needed for this result to succeed
1531 per_success := Wf_Engine.GetActivityAttrNumber(itemtype,itemkey,
1532 actid,result_rec.result_code);
1533
1534 if (per_success is null) then
1535 -- Null value means this is a default result.
1536 -- Save the default result with max code_count.
1537 if (l_code_count > max_default) then
1538 max_default := l_code_count;
1539 default_result := result_rec.result_code;
1540 elsif (l_code_count = max_default) then
1541 -- Tie for default result.
1542 default_result := wf_engine.eng_tie;
1543 end if;
1544 else
1545 -- If:
1546 -- a. % vote for this result > % needed for success OR
1547 -- b. % vote is 100% AND
1548 -- c. at least 1 vote for this result
1549 -- then this result succeeds.
1550 if (((l_per_code > per_success) or (l_per_code = 100)) and
1551 (l_code_count > 0))
1552 then
1553 if (result is null) then
1554 -- Save satisfied result.
1555 result := result_rec.result_code;
1556 else
1557 -- This is the second result to be satisfied. Return a tie.
1558 resultout := wf_engine.eng_completed||':'||wf_engine.eng_tie;
1559 return;
1560 end if;
1561 end if;
1562 end if;
1563 end loop;
1564
1565 if (result is not null) then
1566
1567 -- Return the satisfied result code.
1568 resultout := wf_engine.eng_completed||':'||result;
1569 else
1570 -- If we get here no non-default results were satisfied.
1571 if (funcmode = wf_engine.eng_run and
1572 wf_notification.OpenNotificationsExist(l_group_id)) then
1573 -- Not timed out and still open notifications.
1574 -- Return waiting to continue voting.
1575 resultout := wf_engine.eng_waiting;
1576 elsif (default_result is not null) then
1577 -- Either timeout or all notifications closed
1578 -- Return default result if one found.
1579 resultout := wf_engine.eng_completed||':'||default_result;
1580 elsif (funcmode = wf_engine.eng_timeout) then
1581 -- If Timeout has occured then return result Timeout so the Timeout
1582 -- transition will occur - BUG2885157
1583 resultout := wf_engine.eng_completed||':'||wf_engine.eng_timeout;
1584 else
1585 -- All notifications closed, and no default.
1586 -- Return nomatch
1587
1588
1589 resultout := wf_engine.eng_completed||':'||wf_engine.eng_nomatch;
1590
1591 end if;
1592 end if;
1593
1594 return;
1595 exception
1596 when wf_invalid_command then
1597 Wf_Core.Context('Wf_Standard', 'VoteForResultType', itemtype,
1598 itemkey, to_char(actid), funcmode);
1599 Wf_Core.Token('COMMAND', l_voting_option);
1600 Wf_Core.Raise('WFSQL_COMMAND');
1601 when others then
1602 Wf_Core.Context('Wf_Standard', 'VoteForResultType',itemtype,
1603 itemkey, to_char(actid), funcmode);
1604 raise;
1605 end VoteForResultType;
1606
1607 /* ************************************************************************
1608 This procedure will close the ERES document once the document is finished.
1609 i.e all singers viewed and signed the eRecord
1610 Also the document will be Acknowledgement with the corrected status
1611
1612 ************************************************************************ */
1613
1614
1615 PROCEDURE CLOSE_AND_ACK_ERES_DOC (p_itemtype IN VARCHAR2,
1616 p_itemkey IN VARCHAR2,
1617 p_actid IN NUMBER,
1618 p_funcmode IN VARCHAR2,
1619 p_resultout OUT NOCOPY VARCHAR2
1620 ) IS
1621
1622 l_Event_key NUMBER;
1623 l_Event_name varchar2(240) := 'oracle.apps.eng.ecoApproval';
1624
1625 l_doc_id number;
1626 l_msg_count number;
1627 l_msg_data varchar2(1000);
1628
1629
1630 l_erecord_id NUMBER;
1631 l_return_status VARCHAR2(1);
1632 l_trans_status VARCHAR2(30);
1633 l_send_ackn boolean;
1634 l_autonomous_commit VARCHAR2(1);
1635
1636
1637 BEGIN
1638 IF P_FUNCMODE ='RUN' THEN
1639
1640
1641 /***** ERES: closeDocument ******/
1642
1643 l_doc_id := wf_engine.GETITEMATTRNUMBER(itemtype => p_itemtype,
1644 itemkey => p_itemkey,
1645 aname => 'DOC_ID');
1646 l_Event_Key := wf_engine.GETITEMATTRNUMBER(itemtype => p_itemtype,
1647 itemkey => p_itemkey,
1648 aname => 'CHANGE_ID');
1649 QA_EDR_STANDARD.Close_Document (
1650 p_api_version => 1.0,
1651 p_init_msg_list => 'T',
1652 p_commit => 'FALSE',
1653 x_return_status => l_return_status,
1654 x_msg_count => l_msg_count,
1655 x_msg_data => l_msg_data,
1656 P_DOCUMENT_ID => l_doc_id
1657 );
1658 -- update edr_psig_documents set psig_xml=psig_document where document_id=l_doc_id;
1659
1660 l_erecord_id := l_doc_id;
1661 IF l_erecord_id IS NOT NULL
1662 THEN
1663 l_send_ackn := TRUE;
1664 l_trans_status := 'SUCCESS';
1665 l_autonomous_commit := 'F';
1666 ELSE
1667 l_send_ackn := TRUE;
1668 l_trans_status := 'ERROR';
1669 l_autonomous_commit := 'T';
1670 END IF;
1671
1672 IF l_send_ackn = TRUE
1673 then
1674 QA_EDR_STANDARD.SEND_ACKN
1675 (p_api_version => 1.0
1676 ,p_init_msg_list => 'T'
1677 ,x_return_status => l_return_status
1678 ,x_msg_count => l_msg_count
1679 ,x_msg_data => l_msg_data
1680 ,p_event_name => l_Event_name
1681 ,p_event_key => l_Event_Key
1682 ,p_erecord_id => l_erecord_id
1683 ,p_trans_status => l_trans_status
1684 ,p_ackn_by => 'ECO APPROVAL WORKFLOW'
1685 ,p_ackn_note => 'WF Acknowledgement'
1686 ,p_autonomous_commit=> l_autonomous_commit
1687 );
1688 END IF;
1689
1690 p_resultout := 'COMPLETE:Closed ERES Document and Acknowledged';
1691 END IF;
1692
1693 EXCEPTION
1694 WHEN OTHERS THEN
1695 WF_CORE.CONTEXT ('ENG_WORKFLOW_API_PKG','CLOSE_AND_ACK_ERES_DOC',
1696 p_itemtype,p_itemkey,SQLERRM);
1697 raise;
1698
1699 END CLOSE_AND_ACK_ERES_DOC;
1700
1701
1702 END ENG_WORKFLOW_API_PKG;
[Home] [Help]