1 PACKAGE BODY PO_APPROVAL_LIST_HISTORY_SV AS
2 /* $Header: POXWAHIB.pls 120.2.12000000.3 2007/03/29 05:05:36 rakchakr 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 -- dbms_output.put_line ('x_notification_id ' || to_char(x_notification_id));
224
225 /*
226 ** if we cannot find the responder within our system, either the ntf has not
227 ** been responded yet or the response came from outside.
228 ** Therefore we cannot record the responder in
229 ** PO_ACTION_HISTORY
230 */
231
232 /* internal name of responder */
233
234 /* bug 3090563, added to fetch more info role as well */
235
236 SELECT wfn.responder, wfn.recipient_role,
237 wfn.original_recipient, wfn.more_info_role
238 INTO l_responder, l_recipient_role,
239 l_original_recipient, l_more_info_role
240 FROM wf_notifications wfn
241 WHERE wfn.notification_id = x_notification_id;
242
243 /* csheu bug #1287135 use reponder value in wf_notification to find
244 its orig_system_id from wf_users. If no matched rows found from
245 wf_users then we will use l_recipient_role value from wf_notification
246 to find its orig_system_id from wf_users instead.
247 */
248
249 OPEN c_responderid(l_responder);
250 FETCH c_responderid INTO x_responder_id;
251
252
253 IF c_responderid%NOTFOUND THEN
254
255 CLOSE c_responderid;
256 OPEN c_responderid(l_recipient_role);
257 FETCH c_responderid INTO x_responder_id;
258 CLOSE c_responderid;
259
260 END IF;
261
262 IF (c_responderid%ISOPEN) THEN
263 CLOSE c_responderid;
264 END IF;
265
266 -- dbms_output.put_line ('x_responder_id' || to_char(x_responder_id));
267
268 OPEN c_responderid(l_original_recipient);
269 FETCH c_responderid INTO l_original_recipient_id;
270
271 IF c_responderid%NOTFOUND THEN
272
273 CLOSE c_responderid;
274
275 SELECT WFU.ORIG_SYSTEM_ID
276 INTO l_original_recipient_id
277 FROM WF_ROLES WFU
278 WHERE WFU.NAME = l_original_recipient
279 AND WFU.ORIG_SYSTEM NOT IN ('POS', 'ENG_LIST', 'CUST_CONT');
280
281 END IF;
282
283 IF (c_responderid%ISOPEN) THEN
284 CLOSE c_responderid;
285 END IF;
286
287 /* begin bug 3090563
288 * notification enhancement to support more info requested
289 */
290 if (l_more_info_role is not null) then
291 Wf_Directory.GetRoleOrigSysInfo(l_more_info_role, l_more_origsys, l_more_origsysid);
292 end if;
293
294 /* Bug 2893011: Move update history logic to private autonomus procedure. */
295
296 UpdateActionHistory(l_more_origsysid, --bug 3090563
297 l_original_recipient_id,
298 x_responder_id,
299 x_last_approver,
300 x_action_code,
301 x_note,
302 x_req_header_id,
303 l_appr_and_fwd_flag);
304
305 /* end bug 3090563 */
306
307 EXCEPTION
308
309 WHEN e_invalid_action THEN
310 RAISE;
311 WHEN OTHERS THEN
312 RAISE;
313
314 END Update_Action_History;
315
316 /* Bug# 2684757: kagarwal
317 ** Desc: Added new procedure to insert null action in
318 ** po_action_history for the Requisition if it does not exists.
319 */
320
321 procedure Reserve_Action_History(x_approval_path_id in number,
322 x_req_header_id in number,
323 x_approver_id in number) IS
324
325 pragma AUTONOMOUS_TRANSACTION;
326
327 l_progress VARCHAR2(100) := '000';
328 x_count number := 0;
329 x_sequence_num NUMBER;
330
331 CURSOR C IS
332 SELECT object_id,
333 object_type_code,
334 object_sub_type_code,
335 sequence_num,
336 action_code,
337 object_revision_num,
338 request_id,
339 program_application_id,
340 program_date,
341 program_id,
342 last_update_date,
343 employee_id
344 FROM PO_ACTION_HISTORY
345 WHERE object_type_code = 'REQUISITION'
346 AND object_id = x_req_header_id
347 AND sequence_num = x_sequence_num;
348
349 Recinfo C%ROWTYPE;
350
351 BEGIN
352
353 l_progress := '010';
354
355 SELECT max(sequence_num)
356 INTO x_sequence_num
357 FROM PO_ACTION_HISTORY
358 WHERE object_type_code = 'REQUISITION'
359 AND object_id = x_req_header_id;
360
361 OPEN C;
362
363 FETCH C INTO Recinfo;
364
365 IF (C%NOTFOUND) then
366 RAISE NO_DATA_FOUND;
367 END IF;
368
369 CLOSE C;
370
371 IF (Recinfo.action_code is NOT NULL) THEN
372 l_progress := '015';
373
374 po_forward_sv1.insert_action_history (
375 Recinfo.object_id,
376 Recinfo.object_type_code,
377 Recinfo.object_sub_type_code,
378 Recinfo.sequence_num+1,
379 NULL,
380 NULL,
381 x_approver_id,
382 x_approval_path_id,
383 NULL,
384 Recinfo.object_revision_num,
385 NULL, /* offline_code */
386 Recinfo.request_id,
387 Recinfo.program_application_id,
388 Recinfo.program_id,
389 Recinfo.program_date,
390 fnd_global.user_id,
391 fnd_global.login_id);
392
393 l_progress := '040';
394
395 commit;
396
397 END IF;
398
399 l_progress := '050';
400
401 EXCEPTION
402 WHEN others THEN
403 RAISE;
404
405 END Reserve_Action_History;
406
407 /*
408 * This method is a private method to update the action history in autonomous context.
409 */
410 PROCEDURE UpdateActionHistory(p_more_info_id IN NUMBER,
411 p_original_recipient_id IN NUMBER,
412 p_responder_id IN NUMBER,
413 p_last_approver IN BOOLEAN,
414 p_action_code IN VARCHAR2,
415 p_note IN VARCHAR2,
416 p_req_header_id IN NUMBER,
417 p_app_and_fwd_flag IN BOOLEAN )
418 IS
419
420 pragma AUTONOMOUS_TRANSACTION;
421
422
423 l_progress VARCHAR2(100) := '000';
424 x_sequence_num NUMBER;
425 l_note VARCHAR2(4000) := NULL;
426
427 CURSOR C IS
428
429 SELECT PH.ACTION_CODE action_code ,
430 PH.OBJECT_TYPE_CODE object_type_code ,
431 PH.OBJECT_SUB_TYPE_CODE object_sub_type_code ,
432 PH.SEQUENCE_NUM sequence_num ,
433 PH.OBJECT_REVISION_NUM object_revision_num ,
434 PH.APPROVAL_PATH_ID approval_path_id ,
435 PH.REQUEST_ID request_id ,
439 PH.LAST_UPDATE_DATE last_update_date ,
436 PH.PROGRAM_APPLICATION_ID program_application_id ,
437 PH.PROGRAM_DATE program_date ,
438 PH.PROGRAM_ID program_id ,
440 PH.OBJECT_ID object_id
441 FROM
442 PO_DOCUMENT_TYPES PODT,
443 PO_REQUISITION_HEADERS PRH,
444 PO_ACTION_HISTORY PH
445 WHERE PRH.REQUISITION_HEADER_ID = PH.OBJECT_ID AND
446 PODT.DOCUMENT_TYPE_CODE = 'REQUISITION' AND
447 PODT.DOCUMENT_SUBTYPE (+) = PRH.TYPE_LOOKUP_CODE AND
448 PODT.DOCUMENT_TYPE_CODE = PH.OBJECT_TYPE_CODE AND
449 PRH.TYPE_LOOKUP_CODE = PH.OBJECT_SUB_TYPE_CODE AND
450 PRH.requisition_header_id=p_req_header_id and
451 PH.SEQUENCE_NUM = X_SEQUENCE_NUM;
452
453 Recinfo C%ROWTYPE;
454
455 BEGIN
456
457 SELECT max(sequence_num)
458 INTO x_sequence_num
459 FROM PO_ACTION_HISTORY
460 WHERE object_type_code = 'REQUISITION'
461 AND object_id = p_req_header_id;
462
463 -- dbms_output.put_line ('x_sequence_num' || to_char(x_sequence_num));
464
465 OPEN C;
466
467 FETCH C INTO Recinfo;
468
469 IF (C%NOTFOUND) then
470 -- dbms_output.put_line ('not_here!!');
471 RAISE NO_DATA_FOUND;
472 END IF;
473
474 CLOSE C;
475
476 -- Add a blank line if the last line is not blank.
477 if (Recinfo.action_code is not null) then
478 po_forward_sv1.insert_action_history (
479 Recinfo.object_id,
480 Recinfo.object_type_code,
481 Recinfo.object_sub_type_code,
482 Recinfo.sequence_num + 1,
483 NULL,
484 NULL,
485 p_original_recipient_id,
486 Recinfo.approval_path_id,
487 NULL,
488 Recinfo.object_revision_num,
489 NULL, /* offline_code */
490 Recinfo.request_id,
491 Recinfo.program_application_id,
492 Recinfo.program_id,
493 Recinfo.program_date,
494 fnd_global.user_id,
495 fnd_global.login_id);
496
497 end if;
498
499 /*
500 ** if the ntf has been reassigned, update the original NULL row in POAH
501 ** with action NO ACTION and insert a new row with NULL action
502 ** for the new responder
503 */
504
505 IF (p_responder_id <> -9996) THEN
506
507 /** bug 3090563
508 ** the logic to handle re-assignment is now in post notification function
509 ** so that the update to action history can be viewed
510 ** at the moment of reassignment.
511 **
512 ** this following is used to handle request for more info:
513 ** 1. at the moment an approver requests for more info,
514 ** action history is updated (performed within post notification)
515 ** 2. if the approver approve/reject the requisition
516 ** before the more info request is responded
517 ** then we need to update the action history
518 ** to reflect 'no action' from the more info role
519 */
520 l_progress := '030';
521
522 IF (p_more_info_id is not null) THEN
523
524 /*
525 ** update the original NULL row for the original approver with
526 ** action code of 'NO ACTION'
527 */
528
529 l_progress := '040';
530 -- dbms_output.put_line ('l_progress!! -' || l_progress );
531
532 po_forward_sv1.update_action_history (
533 Recinfo.object_id,
534 Recinfo.object_type_code,
535 p_more_info_id,
536 'NO ACTION',
537 NULL,
538 fnd_global.user_id,
539 fnd_global.login_id
540 );
541
542 /*
543 ** insert a new NULL row into PO_ACTION_HISTORY for
544 ** the new approver
545 */
546
547 l_progress := '050';
548
549 po_forward_sv1.insert_action_history (
550 Recinfo.object_id,
551 Recinfo.object_type_code,
552 Recinfo.object_sub_type_code,
553 Recinfo.sequence_num + 1,
554 NULL,
555 NULL,
556 p_responder_id,
557 Recinfo.approval_path_id,
558 NULL,
559 Recinfo.object_revision_num,
560 NULL, /* offline_code */
561 Recinfo.request_id,
562 Recinfo.program_application_id,
563 Recinfo.program_id,
564 Recinfo.program_date,
565 fnd_global.user_id,
566 fnd_global.login_id);
567
568 -- dbms_output.put_line ('l_progress!! -' || l_progress );
569
570 END IF;
571
572 END IF; -- p_responder_id != -9996
573
574
575 l_progress := '070';
576
577 IF (not p_last_approver) THEN
578 if p_app_and_fwd_flag = FALSE then
579 l_note := substrb(p_note,1,4000); --x_note,
580 end if;
581
582 /*
583 ** update pending row of action history with approval action
584 */
585 UPDATE PO_ACTION_HISTORY
586 SET last_update_date = sysdate,
587 last_updated_by = fnd_global.user_id, --x_user_id,
588 last_update_login = fnd_global.login_id, --x_login_id,
589 action_date = sysdate,
590 action_code = p_action_code, --x_action_code,
591 note = l_note, --x_note,
592 offline_code = NULL
593 WHERE object_id = Recinfo.object_id
594 AND object_type_code = Recinfo.object_type_code
595 AND action_code IS NULL;
596
597 /* If 'APPROVE_AND_FORWARD' add a row for forward in action history table */
598 IF p_app_and_fwd_flag = TRUE THEN
599 po_forward_sv1.insert_action_history (
600 Recinfo.object_id,
601 Recinfo.object_type_code,
602 Recinfo.object_sub_type_code,
603 Recinfo.sequence_num + 1,
604 'FORWARD',
605 sysdate,
606 p_responder_id,
607 Recinfo.approval_path_id,
608 substrb(p_note,1,4000), -- Inserting note in forwarded row
609 Recinfo.object_revision_num,
610 NULL, /* offline_code */
611 Recinfo.request_id,
612 Recinfo.program_application_id,
613 Recinfo.program_id,
614 Recinfo.program_date,
615 fnd_global.user_id,
616 fnd_global.login_id);
617 END IF;
618 END IF;
619
620 l_progress := '080';
621
622 -- dbms_output.put_line ('l_progress!! -' || l_progress );
623
624 commit;
625
626 EXCEPTION
627
628 WHEN OTHERS THEN
629 RAISE;
630
631 END;
632
633 END PO_APPROVAL_LIST_HISTORY_SV;
[Home] [Help]