1 PACKAGE BODY PO_APPROVAL_LIST_HISTORY_SV AS
2 /* $Header: POXWAHIB.pls 120.6.12020000.2 2013/02/10 12:35:53 vegajula ship $*/
3
4 PROCEDURE UpdateActionHistory(p_more_info_id IN NUMBER,
5 p_original_recipient_id IN NUMBER,
6 p_responder_id IN NUMBER,
7 p_last_approver IN BOOLEAN,
8 p_action_code IN VARCHAR2,
9 p_note IN VARCHAR2,
10 p_req_header_id IN NUMBER,
11 p_app_and_fwd_flag IN BOOLEAN );
12
13 procedure Forward_Action_History(itemtype in varchar2,
14 itemkey in varchar2,
15 x_approval_path_id in number,
16 x_req_header_id in number,
17 x_forward_to_id in number) IS
18
19 pragma AUTONOMOUS_TRANSACTION;
20
21 l_progress VARCHAR2(100) := '000';
22 x_count number := 0;
23 x_sequence_num NUMBER;
24 e_invalid_action EXCEPTION;
25 e_no_forward_to_id EXCEPTION;
26 l_note varchar2(4000); /* Bug 5142600 */
27
28 CURSOR C IS
29 SELECT object_id,
30 object_type_code,
31 object_sub_type_code,
32 sequence_num,
33 object_revision_num,
34 request_id,
35 program_application_id,
36 program_date,
37 program_id,
38 last_update_date,
39 employee_id
40 FROM PO_ACTION_HISTORY
41 WHERE object_type_code = 'REQUISITION'
42 AND object_id = x_req_header_id
43 AND sequence_num = x_sequence_num;
44
45 Recinfo C%ROWTYPE;
46
47
48 BEGIN
49
50 SELECT count(*)
51 INTO x_count
52 FROM PO_ACTION_HISTORY
53 WHERE object_type_code = 'REQUISITION'
54 AND object_id = x_req_header_id
55 AND action_code IS NULL;
56
57 l_progress := '010';
58
59 /*
60 ** the only case where we can have a null role in
61 ** POAH is the first call, since the workflow submission
62 ** code inserts the first NULL row
63 */
64
65 IF (x_count > 1) THEN
66
67 RAISE e_invalid_action;
68
69 ELSE
70
71 SELECT max(sequence_num)
72 INTO x_sequence_num
73 FROM PO_ACTION_HISTORY
74 WHERE object_type_code = 'REQUISITION'
75 AND object_id = x_req_header_id;
76
77 OPEN C;
78
79 FETCH C INTO Recinfo;
80
81 IF (C%NOTFOUND) then
82 RAISE NO_DATA_FOUND;
83 END IF;
84
85 CLOSE C;
86
87 /*
88 ** if it is the first call and it gets here it means there is
89 ** an implicit forward. We want to update the
90 ** first NULL row in POAH with FORWARD action
91 */
92
93 IF (x_count = 1) THEN
94 /*bug 5142600: need to update the note also */
95 l_note := wf_engine.GetItemAttrText(itemtype=>itemtype,
96 itemkey=>itemkey,
97 aname=>'NOTE');
98 po_forward_sv1.update_action_history (
99 Recinfo.object_id,
100 Recinfo.object_type_code,
101 Recinfo.employee_id,
102 'FORWARD',
103 l_note,
104 fnd_global.user_id,
105 fnd_global.login_id
106 );
107
108 l_progress := '015';
109
110 END IF;
111
112 l_progress := '020';
113
114
115 IF (x_forward_to_id is NULL) THEN
116 -- dbms_output.put_line ('Null forward to id in approve/forward or forward ! ');
117 RAISE e_no_forward_to_id;
118 ELSE
119 po_forward_sv1.insert_action_history (
120 Recinfo.object_id,
121 Recinfo.object_type_code,
122 Recinfo.object_sub_type_code,
123 Recinfo.sequence_num+1,
124 NULL,
125 NULL,
126 x_forward_to_id,
127 x_approval_path_id,
128 NULL,
129 Recinfo.object_revision_num,
130 NULL, /* offline_code */
131 Recinfo.request_id,
132 Recinfo.program_application_id,
133 Recinfo.program_id,
134 Recinfo.program_date,
135 fnd_global.user_id,
136 fnd_global.login_id);
137
138 l_progress := '040';
139
140
141 END IF;
142 END IF;
143
144 l_progress := '050';
145
146 commit;
147
148 EXCEPTION
149 WHEN e_invalid_action THEN
150 RAISE;
151
152 WHEN e_no_forward_to_id THEN
153 RAISE;
154
155 WHEN others THEN
156 RAISE;
157
158 END Forward_Action_History;
159
160
161 procedure Update_Action_History(itemtype in varchar2,
162 itemkey in varchar2,
163 x_action in varchar2,
164 x_req_header_id in number,
165 x_last_approver in boolean,
166 x_note in varchar2) IS
167
168 l_progress VARCHAR2(100) := '000';
169 x_action_code VARCHAR2(24) := NULL;
170 x_responder_id wf_local_roles.ORIG_SYSTEM_ID%TYPE;
171 x_notification_id NUMBER;
172 e_invalid_action EXCEPTION;
173 l_responder wf_notifications.responder%TYPE;
174 l_employee_id NUMBER;
175 l_original_recipient_id NUMBER;
176 l_original_recipient wf_notifications.original_recipient%TYPE;
177 l_recipient_role wf_notifications.recipient_role%TYPE;
178
179 /* begin bug 3090563
180 * notification enhancement to support more info requested
181 */
182 l_more_info_role wf_notifications.more_info_role%TYPE;
183 l_more_origsys wf_roles.orig_system%TYPE;
184 l_more_origsysid wf_roles.orig_system_id%TYPE := null;
185 /* end bug 3090563 */
186 l_appr_and_fwd_flag boolean := FALSE;-- Flag to check if we have 'APPROVE_AND_FORWARD' action
187 /* bug 1817306 new cursor c_responderid is defined to replace c_responder */
188 CURSOR c_responderid(p_responder VARCHAR2) IS
189 SELECT nvl((wfu.orig_system_id), -9996)
190 FROM wf_users wfu
191 WHERE wfu.name = p_responder
192 AND wfu.orig_system not in ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
193
194 BEGIN
195 IF (x_action IN ('APPROVE', 'FORWARD', 'REJECT', 'RETURN', 'NO ACTION')) THEN
196 x_action_code := x_action;
197
198 ELSIF (x_action = 'APPROVE_AND_FORWARD') THEN
199 x_action_code := 'APPROVE';
200 l_appr_and_fwd_flag := TRUE; --Set flag TRUE
201 ELSE
202 RAISE e_invalid_action;
203 END IF;
204 -- dbms_output.put_line ('Action: ' || x_action_code);
205
206
207 l_progress := '020';
208
209 /*
210 ** use MAX to get the latest notification sent for
211 ** the wf item
212 */
213
214 SELECT NVL(MAX(wf.notification_id), -9995)
215 INTO x_notification_id
216 FROM WF_NOTIFICATIONS WF,
217 WF_ITEM_ACTIVITY_STATUSES WIAS
218 WHERE WIAS.ITEM_TYPE = itemtype AND
219 WIAS.ITEM_KEY = itemkey AND
220 WIAS.NOTIFICATION_ID = WF.group_id;
221
222
223 /*FP 14058500
224 Fix for bug 7391797
225 ** When an activity is skipped the old data is moved to
226 ** wf_item_activity_statuses_h. Hence added if condition
227 ** to return the notification_id in this case
228 */
229
230 IF (x_notification_id = -9995) THEN
231 SELECT NVL(MAX(wf.notification_id), -9995)
232 INTO x_notification_id
233 FROM WF_NOTIFICATIONS WF,
234 WF_ITEM_ACTIVITY_STATUSES_H WIAS
235 WHERE WIAS.ITEM_TYPE = itemtype AND
236 WIAS.ITEM_KEY = itemkey AND
237 WIAS.NOTIFICATION_ID = WF.group_id;
238 END IF;
239
240
241 -- dbms_output.put_line ('x_notification_id ' || to_char(x_notification_id));
242
243 /*
244 ** if we cannot find the responder within our system, either the ntf has not
245 ** been responded yet or the response came from outside.
246 ** Therefore we cannot record the responder in
247 ** PO_ACTION_HISTORY
248 */
249
250 /* internal name of responder */
251
252 /* bug 3090563, added to fetch more info role as well */
253
254 SELECT wfn.responder, wfn.recipient_role,
255 wfn.original_recipient, wfn.more_info_role
256 INTO l_responder, l_recipient_role,
257 l_original_recipient, l_more_info_role
258 FROM wf_notifications wfn
259 WHERE wfn.notification_id = x_notification_id;
260
261 /* csheu bug #1287135 use reponder value in wf_notification to find
262 its orig_system_id from wf_users. If no matched rows found from
263 wf_users then we will use l_recipient_role value from wf_notification
264 to find its orig_system_id from wf_users instead.
265 */
266
267 OPEN c_responderid(l_responder);
268 FETCH c_responderid INTO x_responder_id;
269
270
271 IF c_responderid%NOTFOUND THEN
272
273 CLOSE c_responderid;
274 OPEN c_responderid(l_recipient_role);
275 FETCH c_responderid INTO x_responder_id;
276 CLOSE c_responderid;
277
278 END IF;
279
280 IF (c_responderid%ISOPEN) THEN
281 CLOSE c_responderid;
282 END IF;
283
284 -- dbms_output.put_line ('x_responder_id' || to_char(x_responder_id));
285
286 OPEN c_responderid(l_original_recipient);
287 FETCH c_responderid INTO l_original_recipient_id;
288
289 IF c_responderid%NOTFOUND THEN
290
291 CLOSE c_responderid;
292
293 BEGIN
294 SELECT WFU.ORIG_SYSTEM_ID
295 INTO l_original_recipient_id
296 FROM WF_ROLES WFU
297 WHERE WFU.NAME = l_original_recipient
298 AND WFU.ORIG_SYSTEM NOT IN ('POS', 'ENG_LIST', 'CUST_CONT');
299 EXCEPTION
300 WHEN NO_DATA_FOUND THEN
301 l_original_recipient_id := fnd_global.employee_id;
302 END;
303
304 END IF;
305
306 IF (c_responderid%ISOPEN) THEN
307 CLOSE c_responderid;
308 END IF;
309
310 /* begin bug 3090563
311 * notification enhancement to support more info requested
312 */
313 if (l_more_info_role is not null) then
314 Wf_Directory.GetRoleOrigSysInfo(l_more_info_role, l_more_origsys, l_more_origsysid);
315 end if;
316
317 /* Bug 2893011: Move update history logic to private autonomus procedure. */
318
319 UpdateActionHistory(l_more_origsysid, --bug 3090563
320 l_original_recipient_id,
321 x_responder_id,
322 x_last_approver,
323 x_action_code,
324 x_note,
325 x_req_header_id,
326 l_appr_and_fwd_flag);
327
328 /* end bug 3090563 */
329
330 EXCEPTION
331
332 WHEN e_invalid_action THEN
333 RAISE;
334 WHEN OTHERS THEN
335 RAISE;
336
337 END Update_Action_History;
338
339 /* Bug# 2684757: kagarwal
340 ** Desc: Added new procedure to insert null action in
341 ** po_action_history for the Requisition if it does not exists.
342 */
343
344 procedure Reserve_Action_History(x_approval_path_id in number,
345 x_req_header_id in number,
346 x_approver_id in number) IS
347
348 pragma AUTONOMOUS_TRANSACTION;
349
350 l_progress VARCHAR2(100) := '000';
351 x_count number := 0;
352 x_sequence_num NUMBER;
353
354 CURSOR C IS
355 SELECT object_id,
356 object_type_code,
357 object_sub_type_code,
358 sequence_num,
359 action_code,
360 object_revision_num,
361 request_id,
362 program_application_id,
363 program_date,
364 program_id,
365 last_update_date,
366 employee_id
367 FROM PO_ACTION_HISTORY
368 WHERE object_type_code = 'REQUISITION'
369 AND object_id = x_req_header_id
370 AND sequence_num = x_sequence_num;
371
372 Recinfo C%ROWTYPE;
373
374 BEGIN
375
376 l_progress := '010';
377
378 SELECT max(sequence_num)
379 INTO x_sequence_num
380 FROM PO_ACTION_HISTORY
381 WHERE object_type_code = 'REQUISITION'
382 AND object_id = x_req_header_id;
383
384 OPEN C;
385
386 FETCH C INTO Recinfo;
387
388 IF (C%NOTFOUND) then
389 RAISE NO_DATA_FOUND;
390 END IF;
391
392 CLOSE C;
393
394 IF (Recinfo.action_code is NOT NULL) THEN
395 l_progress := '015';
396
397 po_forward_sv1.insert_action_history (
398 Recinfo.object_id,
399 Recinfo.object_type_code,
400 Recinfo.object_sub_type_code,
401 Recinfo.sequence_num+1,
402 NULL,
403 NULL,
404 x_approver_id,
405 x_approval_path_id,
406 NULL,
407 Recinfo.object_revision_num,
408 NULL, /* offline_code */
409 Recinfo.request_id,
410 Recinfo.program_application_id,
411 Recinfo.program_id,
412 Recinfo.program_date,
413 fnd_global.user_id,
414 fnd_global.login_id);
415
416 l_progress := '040';
417
418 commit;
419
420 END IF;
421
422 l_progress := '050';
423
424 EXCEPTION
425 WHEN others THEN
426 RAISE;
427
428 END Reserve_Action_History;
429
430 /*
431 * This method is a private method to update the action history in autonomous context.
432 */
433 PROCEDURE UpdateActionHistory(p_more_info_id IN NUMBER,
434 p_original_recipient_id IN NUMBER,
435 p_responder_id IN NUMBER,
436 p_last_approver IN BOOLEAN,
437 p_action_code IN VARCHAR2,
438 p_note IN VARCHAR2,
439 p_req_header_id IN NUMBER,
440 p_app_and_fwd_flag IN BOOLEAN )
441 IS
442
443 pragma AUTONOMOUS_TRANSACTION;
444
445
446 l_progress VARCHAR2(100) := '000';
447 x_sequence_num NUMBER;
448 l_note VARCHAR2(4000) := NULL;
449 l_sequence_num NUMBER;
450 CURSOR C IS
451
452 SELECT PH.ACTION_CODE action_code ,
453 PH.OBJECT_TYPE_CODE object_type_code ,
454 PH.OBJECT_SUB_TYPE_CODE object_sub_type_code ,
455 PH.SEQUENCE_NUM sequence_num ,
456 PH.OBJECT_REVISION_NUM object_revision_num ,
457 PH.APPROVAL_PATH_ID approval_path_id ,
458 PH.REQUEST_ID request_id ,
459 PH.PROGRAM_APPLICATION_ID program_application_id ,
460 PH.PROGRAM_DATE program_date ,
461 PH.PROGRAM_ID program_id ,
462 PH.LAST_UPDATE_DATE last_update_date ,
463 PH.OBJECT_ID object_id
464 FROM
465 PO_DOCUMENT_TYPES PODT,
466 PO_REQUISITION_HEADERS PRH,
467 PO_ACTION_HISTORY PH
468 WHERE PRH.REQUISITION_HEADER_ID = PH.OBJECT_ID AND
469 PODT.DOCUMENT_TYPE_CODE = 'REQUISITION' AND
470 PODT.DOCUMENT_SUBTYPE (+) = PRH.TYPE_LOOKUP_CODE AND
471 PODT.DOCUMENT_TYPE_CODE = PH.OBJECT_TYPE_CODE AND
472 PRH.TYPE_LOOKUP_CODE = PH.OBJECT_SUB_TYPE_CODE AND
473 PRH.requisition_header_id=p_req_header_id and
474 PH.SEQUENCE_NUM = X_SEQUENCE_NUM;
475
476 Recinfo C%ROWTYPE;
477
478 BEGIN
479
480 SELECT max(sequence_num)
481 INTO x_sequence_num
482 FROM PO_ACTION_HISTORY
483 WHERE object_type_code = 'REQUISITION'
484 AND object_id = p_req_header_id;
485
486 -- dbms_output.put_line ('x_sequence_num' || to_char(x_sequence_num));
487 l_sequence_num:=x_sequence_num;
488 OPEN C;
489
490 FETCH C INTO Recinfo;
491
492 IF (C%NOTFOUND) then
493 -- dbms_output.put_line ('not_here!!');
494 RAISE NO_DATA_FOUND;
495 END IF;
496
497 CLOSE C;
498
499 -- Add a blank line if the last line is not blank.
500 if (Recinfo.action_code is not null) then
501 l_sequence_num:= l_sequence_num +1;
502 po_forward_sv1.insert_action_history (
503 Recinfo.object_id,
504 Recinfo.object_type_code,
505 Recinfo.object_sub_type_code,
506 l_sequence_num,
507 NULL,
508 NULL,
509 p_original_recipient_id,
510 Recinfo.approval_path_id,
511 NULL,
512 Recinfo.object_revision_num,
513 NULL, /* offline_code */
514 Recinfo.request_id,
515 Recinfo.program_application_id,
516 Recinfo.program_id,
517 Recinfo.program_date,
518 fnd_global.user_id,
519 fnd_global.login_id);
520
521 end if;
522
523 /*
524 ** if the ntf has been reassigned, update the original NULL row in POAH
525 ** with action NO ACTION and insert a new row with NULL action
526 ** for the new responder
527 */
528
529 IF (p_responder_id <> -9996) THEN
530
531 /** bug 3090563
532 ** the logic to handle re-assignment is now in post notification function
533 ** so that the update to action history can be viewed
534 ** at the moment of reassignment.
535 **
536 ** this following is used to handle request for more info:
537 ** 1. at the moment an approver requests for more info,
538 ** action history is updated (performed within post notification)
539 ** 2. if the approver approve/reject the requisition
540 ** before the more info request is responded
541 ** then we need to update the action history
542 ** to reflect 'no action' from the more info role
543 */
544 l_progress := '030';
545
546 IF (p_more_info_id is not null) THEN
547
548 /*
549 ** update the original NULL row for the original approver with
550 ** action code of 'NO ACTION'
551 */
552
553 l_progress := '040';
554 -- dbms_output.put_line ('l_progress!! -' || l_progress );
555
556 po_forward_sv1.update_action_history (
557 Recinfo.object_id,
558 Recinfo.object_type_code,
559 p_more_info_id,
560 'NO ACTION',
561 NULL,
562 fnd_global.user_id,
563 fnd_global.login_id
564 );
565
566 /*
567 ** insert a new NULL row into PO_ACTION_HISTORY for
568 ** the new approver
569 */
570
571 l_progress := '050';
572 l_sequence_num:= l_sequence_num +1;
573
574 po_forward_sv1.insert_action_history (
578 l_sequence_num,
575 Recinfo.object_id,
576 Recinfo.object_type_code,
577 Recinfo.object_sub_type_code,
579 NULL,
580 NULL,
581 p_responder_id,
582 Recinfo.approval_path_id,
583 NULL,
584 Recinfo.object_revision_num,
585 NULL, /* offline_code */
586 Recinfo.request_id,
587 Recinfo.program_application_id,
588 Recinfo.program_id,
589 Recinfo.program_date,
590 fnd_global.user_id,
591 fnd_global.login_id);
592
593 -- dbms_output.put_line ('l_progress!! -' || l_progress );
594
595 END IF;
596
597 END IF; -- p_responder_id != -9996
598
599
600 l_progress := '070';
601
602 IF (not p_last_approver) THEN
603 if p_app_and_fwd_flag = FALSE then
604 l_note := substrb(p_note,1,4000); --x_note,
605 end if;
606
607 /*
608 ** update pending row of action history with approval action
609 */
610 /*
611 Bug 14580064 : Replaced p_responder_id by p_original_recipient_id for the case when recepient and responder are different.
612 */
613 UPDATE PO_ACTION_HISTORY
614 SET last_update_date = sysdate,
615 last_updated_by = fnd_global.user_id, --x_user_id,
616 last_update_login = fnd_global.login_id, --x_login_id,
617 action_date = sysdate,
618 action_code = p_action_code, --x_action_code,
619 note = l_note, --x_note,
620 offline_code = NULL
621 WHERE object_id = Recinfo.object_id
622 AND object_type_code = Recinfo.object_type_code
623 AND EMPLOYEE_ID =p_original_recipient_id
624 AND action_code IS NULL;
625
626 /* If 'APPROVE_AND_FORWARD' add a row for forward in action history table */
627 IF p_app_and_fwd_flag = TRUE THEN
628 l_sequence_num:= l_sequence_num +1;
629
630 po_forward_sv1.insert_action_history (
631 Recinfo.object_id,
632 Recinfo.object_type_code,
633 Recinfo.object_sub_type_code,
634 l_sequence_num ,
635 'FORWARD',
636 sysdate,
637 p_responder_id,
638 Recinfo.approval_path_id,
639 substrb(p_note,1,4000), -- Inserting note in forwarded row
640 Recinfo.object_revision_num,
641 NULL, /* offline_code */
642 Recinfo.request_id,
643 Recinfo.program_application_id,
644 Recinfo.program_id,
645 Recinfo.program_date,
646 fnd_global.user_id,
647 fnd_global.login_id);
648 END IF;
649 END IF;
650
651 l_progress := '080';
652
653 -- dbms_output.put_line ('l_progress!! -' || l_progress );
654
655 commit;
656
657 EXCEPTION
658
659 WHEN OTHERS THEN
660 RAISE;
661
662 END;
663
664 END PO_APPROVAL_LIST_HISTORY_SV;
[Home] [Help]