[Home] [Help]
PACKAGE BODY: APPS.POR_AME_APPROVAL_LIST_WF1S
Source
1 PACKAGE BODY POR_AME_APPROVAL_LIST_WF1S AS
2 /* $Header: POXAME1B.pls 120.4 2011/05/24 13:16:41 rojain ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6
7 -- private procedure
8 --------------------------------------------------------------------------------
9
10 --Start of Comments
11 --Name: updateApprovalListResponse
12 --Pre-reqs:
13 -- None.
14 --Modifies:
15 -- None.
16 --Locks:
17 -- None.
18 --Function:
19 -- Call AME API to update approval response
20 --Parameters:
21 --IN:
22 --itemtype
23 -- workflow item type
24 --itemtype
25 -- workflow item key
26 --p_transaction_type
27 -- AME transaction type
28 --p_document_id
29 -- document ID
30 --p_approver_id
31 -- approver ID, who responds to the notification
32 --p_insertion_type
33 -- AME insertion type of the approver who responds to the notification
34 --p_authority_type
35 -- AME authority type of the approver who responds to the notification
36 --p_forward_to_id
37 -- Forward to person ID
38 --p_response
39 -- Notification response
40 --OUT:
41 --Testing:
42 --
43 --End of Comments
44 -------------------------------------------------------------------------------
45 PROCEDURE updateApprovalListResponse(itemtype in varchar2,
46 itemkey in varchar2,
47 p_transaction_type IN VARCHAR2,
48 p_document_id IN NUMBER,
49 p_approver_id IN NUMBER,
50 p_insertion_type IN VARCHAR2 default null,
51 p_authority_type IN VARCHAR2 default null,
52 p_forward_to_id IN NUMBER default null,
53 p_response IN VARCHAR2);
54
55 --------------------------------------------------------------------------------
56
57 --Public procedures
58 --------------------------------------------------------------------------------
59 --Start of Comments
60 --Name: setAmeAttributes
61 --Pre-reqs:
62 -- None.
63 --Modifies:
64 -- None.
65 --Locks:
66 -- None.
67 --Function:
68 -- Workflow activity PL/SQL handler
69 -- set ame related attribute values and change first approver if user performs 'forwarding' via core-apps
70 --Parameters:
71 --IN:
72 -- Standard workflow IN parameters
73 --OUT:
74 -- Standard workflow OUT parameters
75 --End of Comments
76 -------------------------------------------------------------------------------
77
78 Procedure setAmeAttributes(itemtype in varchar2,
79 itemkey in varchar2,
80 actid in number,
81 funcmode in varchar2,
82 resultout out NOCOPY varchar2 )
83 is
84
85 l_progress VARCHAR2(100) := '000';
86 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
87 l_document_id NUMBER;
88 l_interface_source VARCHAR2(30);
89
90 l_tmp_approver ame_util.approverRecord2;
91 l_forward_to NUMBER;
92 l_ApprovalListStr VARCHAR2(32000);
93 l_ApprovalListCount NUMBER;
94 l_QuoteChar VARCHAR2(1);
95 l_FieldDelimiter VARCHAR2(1);
96 l_doc_string varchar2(200);
97 l_preparer_user_name varchar2(100);
98
99
100 begin
101
102 IF (funcmode = 'RUN') THEN
103 l_progress := '001';
104 IF (g_po_wf_debug = 'Y') THEN
105 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
106 END IF;
107
108 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
109 itemkey => itemkey,
110 aname => 'DOCUMENT_ID');
111
112 l_transaction_type := wf_engine.GetItemAttrText (itemtype => itemtype,
113 itemkey => itemkey,
114 aname => 'AME_TRANSACTION_TYPE');
115
116 l_interface_source := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
117 itemkey => itemkey,
118 aname => 'INTERFACE_SOURCE_CODE');
119
120 IF ( l_transaction_type is not null) THEN
121 IF l_interface_source = 'REMIND_NOTIF' THEN
122
123 l_progress := 'for ame when remin_notif clear all approvers';
124 IF (g_po_wf_debug = 'Y') THEN
125 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
126 END IF;
127
128 BEGIN
129
130 ame_api2.clearAllApprovals( applicationIdIn => por_ame_approval_list.applicationId ,
131 transactionIdIn => l_document_id,
132 transactionTypeIn => l_transaction_type
133 );
134
135 EXCEPTION
136 WHEN OTHERS THEN
137
138 l_progress := 'for ame clear all approvers had exceptions '|| SQLERRM || ' code='|| sqlcode;
139 IF (g_po_wf_debug = 'Y') THEN
140 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
141 END IF;
142 END;
143 END IF;
144
145 IF l_interface_source = 'PO_FORM' THEN
146
147 l_forward_to := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
148 itemkey => itemkey,
149 aname => 'FORWARD_TO_ID');
150 l_progress := '002';
151 IF (g_po_wf_debug = 'Y') THEN
152 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
153 END IF;
154
155 If ( l_forward_to is not null ) then
156 por_ame_approval_list.change_first_approver ( pReqHeaderId => l_document_id,
157 pPersonId => l_forward_to,
158 pApprovalListStr => l_ApprovalListStr,
159 pApprovalListCount => l_ApprovalListCount,
160 pQuoteChar => l_QuoteChar,
161 pFieldDelimiter => l_FieldDelimiter );
162
163 l_progress := '003';
164 IF (g_po_wf_debug = 'Y') THEN
165 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
166 END IF;
167
168 -- return the req to requester if forwarding fails
169 if ( l_ApprovalListCount = 0 or l_ApprovalListStr = 'NO_DATA_FOUND' or l_ApprovalListStr = 'EXCEPTION' ) then
170 resultout:='COMPLETE:'||'N';
171 return;
172 end if;
173
174 End If; -- for l_forward_to is not null
175
176 END IF; -- for 'is_form'
177
178 resultout:='COMPLETE:'||'Y';
179
180 ELSE
181
182 resultout:='COMPLETE:'||'N';
183
184 END IF;
185
186 l_progress := '004';
187 IF (g_po_wf_debug = 'Y') THEN
188 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
189 END IF;
190
191 END IF; -- FOR 'RUN' MODE
192
193 EXCEPTION
194 WHEN OTHERS THEN
195 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
196 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
197 wf_core.context('POR_AME_APPROVAL_LIST_WF1S','setAmeAttributes',l_progress,sqlerrm);
198
199 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_APPROVAL_LIST_WF1S.setAmeAttributes');
200 RAISE;
201
202 end;
203
204
205 --Public procedures
206 --------------------------------------------------------------------------------
207 --Start of Comments
208 --Name: Is_Ame_For_Approval
209 --Pre-reqs:
210 -- None.
211 --Modifies:
212 -- None.
213 --Locks:
214 -- None.
215 --Function:
216 -- Workflow activity PL/SQL handler
217 -- Check if AME should be used for requisition approval process
218 -- if Yes then
219 -- initialize ame approval process
220 -- set attribute 'AME_TRANSACTION_TYPE' and 'IS_AME_APPROVAL'
221 -- Returns 'Y' if the workflow should be routed using AME for approval.
222 -- Returns 'N' if the workflow should not be routed using AME for approval.
223 --Parameters:
224 --IN:
225 -- Standard workflow IN parameters
226 --OUT:
227 -- Standard workflow OUT parameters
228 --Testing:
229 --
230 --End of Comments
231 -------------------------------------------------------------------------------
232 procedure Is_Ame_For_Approval(itemtype in varchar2,
233 itemkey in varchar2,
234 actid in number,
235 funcmode in varchar2,
236 resultout out NOCOPY varchar2) IS
237 l_progress VARCHAR2(100) := '000';
238 l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
239 l_document_subtype PO_DOCUMENT_TYPES.DOCUMENT_SUBTYPE%TYPE;
240 l_is_ame_approval boolean;
241 l_doc_string varchar2(200);
242 l_preparer_user_name varchar2(100);
243 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
244
245 begin
246
247 IF (funcmode = 'RUN') THEN
248 l_progress := 'Is_Ame_For_Approval: 001';
249 IF (g_po_wf_debug = 'Y') THEN
250 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
251 END IF;
252
253 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
254 itemkey => itemkey,
255 aname => 'DOCUMENT_TYPE');
256
257 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
258 itemkey => itemkey,
259 aname => 'DOCUMENT_SUBTYPE');
260
261
262 SELECT ame_transaction_type
263 INTO l_transaction_type
264 FROM po_document_types
265 WHERE document_type_code = l_document_type
266 and document_subtype = l_document_subtype;
267
268 if (l_transaction_type is not null) then
269
270 PO_WF_UTIL_PKG.SetItemAttrText( itemtype => itemType,
271 itemkey => itemkey,
272 aname => 'AME_TRANSACTION_TYPE',
273 avalue => l_transaction_type);
274 PO_WF_UTIL_PKG.SetItemAttrText( itemtype => itemType,
275 itemkey => itemkey,
276 aname => 'IS_AME_APPROVAL',
277 avalue => 'Y');
278
279 resultout:='COMPLETE:'||'Y';
280
281 else
282 PO_WF_UTIL_PKG.SetItemAttrText( itemtype => itemType,
283 itemkey => itemkey,
284 aname => 'IS_AME_APPROVAL',
285 avalue => 'N');
286
287 resultout:='COMPLETE:'||'N';
288
289 end if;
290
291 END IF;
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
296 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
297 wf_core.context('POR_AME_APPROVAL_LIST_WF1S','Is_Ame_For_Approval',l_progress,sqlerrm);
298
299 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_APPROVAL_LIST_WF1S.Is_Ame_For_Approval');
300 RAISE;
301
302 END Is_Ame_For_Approval;
303
304 --------------------------------------------------------------------------------
305 --Start of Comments
306 --Name: Is_Ame_For_Rco_Approval
307 --Pre-reqs:
308 -- None.
309 --Modifies:
310 -- None.
311 --Locks:
312 -- None.
313 --Function:
314 -- Workflow activity PL/SQL handler
315 -- Check if AME should be used for RCO approval process
316 -- if Yes then
317 -- initialize ame approval process
318 -- set attribute 'AME_TRANSACTION_TYPE' and 'IS_AME_APPROVAL'
319 -- Returns 'Y' if the workflow should be routed using AME for approval.
320 -- Returns 'N' if the workflow should not be routed using AME for approval.
321 --Parameters:
322 --IN:
323 -- Standard workflow IN parameters
324 --OUT:
325 -- Standard workflow OUT parameters
326 --Testing:
327 --
328 --End of Comments
329 -------------------------------------------------------------------------------
330 procedure Is_Ame_For_Rco_Approval(itemtype in varchar2,
331 itemkey in varchar2,
332 actid in number,
333 funcmode in varchar2,
334 resultout out NOCOPY varchar2) IS
335 l_progress VARCHAR2(100) := '000';
336 l_is_ame_approval boolean;
337 l_doc_string varchar2(200);
338 l_preparer_user_name varchar2(100);
339 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
340
341 begin
342
343 IF (funcmode = 'RUN') THEN
344 l_progress := 'Is_Ame_For_Rco_Approval: 001';
345 IF (g_po_wf_debug = 'Y') THEN
346 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
347 END IF;
348
349
350 begin
351 SELECT ame_transaction_type
352 INTO l_transaction_type
353 FROM po_document_types
354 WHERE document_type_code = 'CHANGE_REQUEST'
355 and document_subtype = 'REQUISITION' ;
356 exception
357 when others then
358 PO_WF_UTIL_PKG.SetItemAttrText( itemtype => itemType,
359 itemkey => itemkey,
360 aname => 'IS_AME_APPROVAL',
361 avalue => 'N');
362 return;
363 end;
364
365 l_progress := 'Is_Ame_For_Rco_Approval: 002';
366 IF (g_po_wf_debug = 'Y') THEN
367 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
368 END IF;
369
370 if (l_transaction_type is not null) then
371
372 PO_WF_UTIL_PKG.SetItemAttrText( itemtype => itemType,
373 itemkey => itemkey,
374 aname => 'AME_TRANSACTION_TYPE',
375 avalue => l_transaction_type);
376 PO_WF_UTIL_PKG.SetItemAttrText( itemtype => itemType,
377 itemkey => itemkey,
378 aname => 'IS_AME_APPROVAL',
379 avalue => 'Y');
380
381 resultout:='COMPLETE:'||'Y';
382
383 else
384
385 PO_WF_UTIL_PKG.SetItemAttrText( itemtype => itemType,
386 itemkey => itemkey,
387 aname => 'IS_AME_APPROVAL',
388 avalue => 'N');
389 resultout:='COMPLETE:'||'N';
390 end if;
391
392 END IF;
393
394 EXCEPTION
395 WHEN OTHERS THEN
396 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
397 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
398 wf_core.context('POR_AME_APPROVAL_LIST_WF1S','Is_Ame_For_Rco_Approval',l_progress,sqlerrm);
399
400 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_APPROVAL_LIST_WF1S.Is_Ame_For_Rco_Approval');
401 RAISE;
402
403 END Is_Ame_For_Rco_Approval;
404
405 --------------------------------------------------------------------------------
406 --Start of Comments
407 --Name:
408 --Pre-reqs:
409 -- None.
410 --Modifies:
411 -- None.
412 --Locks:
413 -- None.
414 --Function:
415 -- Workflow activity PL/SQL handler
416 -- Get the next approver name from the AME approval list
417 -- And update workflow attributes.
418 -- If no next approver is found, approval routing will terminate.
419 --Parameters:
420 --IN:
421 -- Standard workflow IN parameters
422 --OUT:
423 -- Standard workflow OUT parameters
424 --Testing:
425 --
426 --End of Comments
427
428 --Note: For 11.5 WF only. Obsoleted in R12
429 -------------------------------------------------------------------------------
430 procedure Get_Next_Approver(itemtype in varchar2,
431 itemkey in varchar2,
432 actid in number,
433 funcmode in varchar2,
434 resultout out NOCOPY varchar2) IS
435 l_progress VARCHAR2(100) := '000';
436 l_document_id NUMBER;
437 l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
438 l_document_subtype PO_DOCUMENT_TYPES.DOCUMENT_SUBTYPE%TYPE;
439 l_next_approver_id NUMBER;
440 l_next_approver_user_name fnd_user.user_name%TYPE;
441 l_next_approver_disp_name wf_users.display_name%TYPE;
442 l_orig_system wf_users.orig_system%TYPE := 'PER';
443 l_sequence_num NUMBER;
444 l_approver_type VARCHAR2(30);
445 E_FAILURE EXCEPTION;
446
447 l_doc_string varchar2(200);
448 l_preparer_user_name fnd_user.user_name%TYPE;
449 l_org_id number;
450
451 l_next_approver ame_util.approverRecord;
452 l_insertion_type VARCHAR2(30);
453 l_authority_type VARCHAR2(30);
454 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
455
456 BEGIN
457 IF (funcmode = 'RUN') THEN
458
459 l_progress := 'Get_Next_Approver: 001';
460 IF (g_po_wf_debug = 'Y') THEN
461 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
462 END IF;
463
464 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
465 itemkey => itemkey,
466 aname => 'DOCUMENT_ID');
467
468 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
469 itemkey => itemkey,
470 aname => 'DOCUMENT_TYPE');
471
472 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
473 itemkey => itemkey,
474 aname => 'DOCUMENT_SUBTYPE');
475
476 l_transaction_type := wf_engine.GetItemAttrText (itemtype => itemtype,
477 itemkey => itemkey,
478 aname => 'AME_TRANSACTION_TYPE');
479
480
481 l_progress := 'Get_Next_Approver: 002-'||to_char(l_document_id)||'-'||
482 l_document_type||'-'||l_document_subtype;
483 IF (g_po_wf_debug = 'Y') THEN
484 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
485 END IF;
486 ame_api.getNextApprover(applicationIdIn=>applicationId,
487 transactionIdIn=>l_document_id,
488 transactionTypeIn=>l_transaction_type,
489 nextApproverOut=>l_next_approver);
490 l_progress := ('l_next_approver=' || to_char(l_next_approver.person_id));
491 IF (g_po_wf_debug = 'Y') THEN
492 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
493 END IF;
494
495 IF l_next_approver.approval_status = ame_util.exceptionStatus THEN
496 raise E_FAILURE;
497 ELSIF ((l_next_approver.user_id is null) and
498 (l_next_approver.person_id is null) and
499 (l_next_approver.first_name is null) and
500 (l_next_approver.last_name is null) and
501 (l_next_approver.api_insertion is null) and
502 (l_next_approver.authority is null) and
503 (l_next_approver.approval_status is null)) THEN
504 resultout:='COMPLETE:'||'NO_NEXT_APPROVER';
505 return;
506 ELSE
507 l_next_approver_id := l_next_approver.person_id;
508 l_insertion_type := l_next_approver.api_insertion;
509 l_authority_type := l_next_approver.authority;
510 l_progress := 'Get_Next_Approver: 003- get_next_approver - '||
511 to_char(l_next_approver_id);
512
513 IF (g_po_wf_debug = 'Y') THEN
514 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
515 END IF;
516
517 wf_engine.SetItemAttrNumber ( itemtype => itemType,
518 itemkey => itemkey,
519 aname => 'APPROVER_EMPID',
520 avalue => l_next_approver_id);
521
522
523 wf_engine.SetItemAttrNumber ( itemtype => itemType,
524 itemkey => itemkey,
525 aname => 'FORWARD_TO_ID',
526 avalue => l_next_approver_id);
527 wf_engine.SetItemAttrText( itemtype => itemType,
528 itemkey => itemkey,
529 aname => 'AME_INSERTION_TYPE' ,
530 avalue => l_insertion_type);
531
532 wf_engine.SetItemAttrText( itemtype => itemType,
533 itemkey => itemkey,
534 aname => 'AME_AUTHORITY_TYPE' ,
535 avalue => l_authority_type);
536
537 l_orig_system:= 'PER';
538
539 WF_DIRECTORY.GetUserName(l_orig_system,
540 l_next_approver_id,
541 l_next_approver_user_name,
542 l_next_approver_disp_name);
543
544 l_progress := 'Get_Next_Approver: 004- GetUserName - '||
545 l_next_approver_user_name;
546 IF (g_po_wf_debug = 'Y') THEN
547 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
548 END IF;
549
550
551 wf_engine.SetItemAttrText( itemtype => itemType,
552 itemkey => itemkey,
553 aname => 'APPROVER_USER_NAME' ,
554 avalue => l_next_approver_user_name);
555
556 wf_engine.SetItemAttrText( itemtype => itemType,
557 itemkey => itemkey,
558 aname => 'APPROVER_DISPLAY_NAME' ,
559 avalue => l_next_approver_disp_name);
560
561 wf_engine.SetItemAttrText( itemtype => itemType,
562 itemkey => itemkey,
563 aname => 'FORWARD_TO_USERNAME' ,
564 avalue => l_next_approver_user_name);
565
566 wf_engine.SetItemAttrText( itemtype => itemType,
567 itemkey => itemkey,
568 aname => 'FORWARD_TO_DISPLAY_NAME' ,
569 avalue => l_next_approver_disp_name);
570
571 resultout:='COMPLETE:'||'VALID_APPROVER';
572 return;
573 END IF;
574 END IF;
575
576 EXCEPTION
577 WHEN OTHERS THEN
578 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
579 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
580 wf_core.context('POR_AME_APPROVAL_LIST_WF1S','Get_Next_Approver',l_progress,sqlerrm);
581
582 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_APPROVAL_LIST_WF1S.GET_NEXT_APPROVER');
583
584 RAISE;
585
586 END Get_Next_Approver;
587
588
589
590 --------------------------------------------------------------------------------
591 --Start of Comments
592 --Name: Update_Approval_List_Response
593 --Pre-reqs:
594 -- None.
595 --Modifies:
596 -- None.
597 --Locks:
598 -- None.
599 --Function:
600 -- Workflow activity PL/SQL handler
601 -- After an approval notification is responded, update AME approval list.
602 --Parameters:
603 --IN:
604 -- Standard workflow IN parameters
605 --OUT:
606 -- Standard workflow OUT parameters
607 --Testing:
608 --
609 --End of Comments
610 -------------------------------------------------------------------------------
611 procedure Update_Approval_List_Response(itemtype in varchar2,
612 itemkey in varchar2,
613 actid in number,
614 funcmode in varchar2,
615 resultout out NOCOPY varchar2) IS
616 l_progress VARCHAR2(300) := '000';
617 l_approver_id NUMBER := NULL;
618 l_document_id NUMBER;
619
620
621 l_doc_string varchar2(200);
622
623 l_org_id number;
624 l_insertion_type VARCHAR2(30);
625 l_authority_type VARCHAR2(30);
626 l_value VARCHAR2(2000);
627 l_responder_id NUMBER := NULL;
628 l_forward_to_id NUMBER := NULL;
629 l_end_date DATE; -- notification end date
630 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
631
632 l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
633 l_document_subtype PO_DOCUMENT_TYPES.DOCUMENT_SUBTYPE%TYPE;
634 l_preparer_user_name fnd_user.user_name%TYPE;
635
636 BEGIN
637
638 l_progress := ' Update_Approval_List_Response: 001- at beginning of function';
639 IF (g_po_wf_debug = 'Y') THEN
640 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
641 END IF;
642
643 IF (funcmode='RUN') THEN
644
645 PO_APPROVAL_LIST_WF1S.get_approval_response(itemtype => itemtype,
646 itemkey => itemkey,
647 responderId => l_responder_id,
648 response =>l_value,
649 responseEndDate =>l_end_date,
650 forwardToId => l_forward_to_id);
651
652 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
653 itemkey => itemkey,
654 aname => 'DOCUMENT_ID');
655
656 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
657 itemkey => itemkey,
658 aname => 'DOCUMENT_TYPE');
659
660 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
661 itemkey => itemkey,
662 aname => 'DOCUMENT_SUBTYPE');
663
664 l_approver_id := wf_engine.GetItemAttrNumber(itemtype=>itemtype,
665 itemkey=>itemkey,
666 aname=>'APPROVER_EMPID');
667
668 l_insertion_type := wf_engine.GetItemAttrText(itemtype => itemtype,
669 itemkey => itemkey,
670 aname => 'AME_INSERTION_TYPE');
671
672 l_authority_type := wf_engine.GetItemAttrText(itemtype => itemtype,
673 itemkey => itemkey,
674 aname => 'AME_AUTHORITY_TYPE');
675
676 l_progress := 'Update_Approval_List_Response: 010 APP'||
677 to_char(l_approver_id) || ' RES'||to_char(l_responder_id);
678
679 l_progress := l_progress || ' FWD'||to_char(l_forward_to_id) ||
680 ' RESPONSE' || l_value || ' INSERTION? '||
681 l_insertion_type|| ' AUTHORITY? ' || l_authority_type;
682
683 IF (g_po_wf_debug = 'Y') THEN
684 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
685 END IF;
686
687 l_transaction_type := wf_engine.GetItemAttrText (itemtype => itemtype,
688 itemkey => itemkey,
689 aname => 'AME_TRANSACTION_TYPE');
690
691 updateApprovalListResponse(itemtype=>itemtype,
692 itemkey=>itemkey,
693 p_transaction_type=>l_transaction_type,
694 p_document_id=>l_document_id,
695 p_approver_id=>l_approver_id,
696 p_insertion_type=>l_insertion_type,
697 p_authority_type=>l_authority_type,
698 p_forward_to_id=>l_forward_to_id,
699 p_response=>l_value);
700
701 IF (g_po_wf_debug = 'Y') THEN
702 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
703 END IF;
704
705 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
706 RETURN;
707 END IF; -- run mode
708
709 EXCEPTION
710
711 WHEN OTHERS THEN
712
713 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
714 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
715 wf_core.context('POR_AME_APPROVAL_LIST_WF1S',
716 'Update_Approval_List_Response',l_progress,sqlerrm);
717
718 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQAPPRV_PVT.UPDATE_APPROVAL_LIST_RESPONSE');
719
720 RAISE;
721
722 END Update_Approval_List_Response;
723
724
725 --------------------------------------------------------------------------------
726 --Start of Comments
727 --Name: Update_Approver_Timeout
728 --Pre-reqs:
729 -- None.
730 --Modifies:
731 -- None.
732 --Locks:
733 -- None.
734 --Function:
735 -- Workflow activity PL/SQL handler
736 -- After an approval notification is timed out, update AME approval list.
737 --Parameters:
738 --IN:
739 -- Standard workflow IN parameters
740 --OUT:
741 -- Standard workflow OUT parameters
742 --Testing:
743 --
744 --End of Comments
745 -------------------------------------------------------------------------------
746 procedure Update_Approver_Timeout(itemtype in varchar2,
747 itemkey in varchar2,
748 actid in number,
749 funcmode in varchar2,
750 resultout out NOCOPY varchar2) IS
751 l_progress VARCHAR2(300) := '000';
752 l_approver_id NUMBER := NULL;
753 l_document_id NUMBER;
754 l_doc_string varchar2(200);
755 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
756 l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
757 l_document_subtype PO_DOCUMENT_TYPES.DOCUMENT_SUBTYPE%TYPE;
758 l_preparer_user_name fnd_user.user_name%TYPE;
759
760
761 BEGIN
762
763 l_progress := ' Update_Approver_timeout: 001- at beginning of function';
764 IF (g_po_wf_debug = 'Y') THEN
765 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
766 END IF;
767
768 IF (funcmode='RUN') THEN
769 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
770 itemkey => itemkey,
771 aname => 'DOCUMENT_ID');
772
773 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
774 itemkey => itemkey,
775 aname => 'DOCUMENT_TYPE');
776
777 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
778 itemkey => itemkey,
779 aname => 'DOCUMENT_SUBTYPE');
780
781 l_approver_id := wf_engine.GetItemAttrNumber(itemtype=>itemtype,
782 itemkey=>itemkey,
783 aname=>'APPROVER_EMPID');
784
785 l_transaction_type := wf_engine.GetItemAttrText (itemtype => itemtype,
786 itemkey => itemkey,
787 aname => 'AME_TRANSACTION_TYPE');
788 updateApprovalListResponse(itemtype=>itemtype,
789 itemkey=>itemkey,
790 p_transaction_type=>l_transaction_type,
791 p_document_id=>l_document_id,
792 p_approver_id=>l_approver_id,
793 p_response=>'TIMEOUT');
794
795 l_progress := ' Update_Approver_timeout: 002- at end of function';
796 IF (g_po_wf_debug = 'Y') THEN
797 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
798 END IF;
799
800 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
801 RETURN;
802 END IF; -- run mode
803
804 EXCEPTION
805
806 WHEN OTHERS THEN
807
808 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
809 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
810 wf_core.context('POR_AME_APPROVAL_LIST_WF1S',
811 'Update_Approval_List_Response',l_progress,sqlerrm);
812
813 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQAPPRV_PVT.UPDATE_APPROVAL_LIST_RESPONSE');
814
815 RAISE;
816
817 END Update_Approver_Timeout;
818
819
820
821 --Note: procedure updateApprovalListResponse is For 11.5 WF only. Obsoleted in R12.
822 PROCEDURE updateApprovalListResponse(itemtype in varchar2,
823 itemkey in varchar2,
824 p_transaction_type IN VARCHAR2,
825 p_document_id IN NUMBER,
826 p_approver_id IN NUMBER,
827 p_insertion_type IN VARCHAR2 default null,
828 p_authority_type IN VARCHAR2 default null,
829 p_forward_to_id IN NUMBER default null,
830 p_response IN VARCHAR2) IS
831
832 l_progress VARCHAR2(100) := '000';
833 forwardee ame_util.approverRecord;
834 currentApprover ame_util.approverRecord;
835
836 BEGIN
837
838 l_progress := 'transaction: '|| p_transaction_type || '; response:' || p_response;
839 currentApprover.person_id :=p_approver_id;
840 currentApprover.api_insertion :=p_insertion_type;
841 currentApprover.authority := p_authority_type;
842
843 if(p_response='APPROVE') then
844 ame_api.updateApprovalStatus2(applicationIdIn=>applicationId,
845 transactionIdIn=>p_document_id,
846 approvalStatusIn=>ame_util.approvedStatus,
847 approverPersonIdIn=>p_approver_id,
848 transactionTypeIn=>p_transaction_type);
849
850 elsif(p_response='REJECT') then
851 ame_api.updateApprovalStatus2(applicationIdIn=>applicationId,
852 transactionIdIn=>p_document_id,
853 approvalStatusIn=>ame_util.rejectStatus,
854 approverPersonIdIn=>p_approver_id,
855 transactionTypeIn=>p_transaction_type);
856
857 elsif(p_response='FORWARD') then
858 forwardee.authority := currentApprover.authority;
859 forwardee.person_id := p_forward_to_id;
860 if(currentApprover.authority = ame_util.authorityApprover and
861 (currentApprover.api_insertion = ame_util.apiAuthorityInsertion or
862 currentApprover.api_insertion = ame_util.oamGenerated)) then
863 forwardee.api_insertion := ame_util.apiAuthorityInsertion;
864 else
865 forwardee.api_insertion := ame_util.apiInsertion;
866 end if;
867
868 currentApprover.approval_status := ame_util.forwardStatus;
869 ame_api.updateApprovalStatus(applicationIdIn=>applicationId,
870 transactionIdIn=>p_document_id,
871 transactionTypeIn=>p_transaction_type,
872 approverIn=>currentApprover,
873 forwardeeIn=>forwardee);
874
875 elsif (p_response='APPROVE_AND_FORWARD') THEN
876
877 forwardee.authority := currentApprover.authority;
878 forwardee.person_id := p_forward_to_id;
879 if(currentApprover.authority = ame_util.authorityApprover and
880 (currentApprover.api_insertion = ame_util.apiAuthorityInsertion or
881 currentApprover.api_insertion = ame_util.oamGenerated)) then
882 forwardee.api_insertion := ame_util.apiAuthorityInsertion;
883 else
884 forwardee.api_insertion := ame_util.apiInsertion;
885 end if;
886
887 currentApprover.approval_status := ame_util.approveAndForwardStatus;
888 ame_api.updateApprovalStatus(applicationIdIn=>applicationId,
889 transactionIdIn=>p_document_id,
890 transactionTypeIn=>p_transaction_type,
891 approverIn=>currentApprover,
892 forwardeeIn=>forwardee);
893 elsif(p_response='TIMEOUT') then
894 ame_api.updateApprovalStatus2(applicationIdIn=>applicationId,
895 transactionIdIn=>p_document_id,
896 approvalStatusIn=>ame_util.noResponseStatus,
897 approverPersonIdIn=>p_approver_id,
898 transactionTypeIn=>p_transaction_type);
899
900
901 end if;
902
903 RETURN;
904 EXCEPTION
905
906 WHEN OTHERS THEN
907 wf_core.context('PO_AME_APPROVAL_LIST_WF1S',
908 'updateApprovalListResponse',l_progress,sqlerrm);
909
910 RAISE;
911 END updateApprovalListResponse;
912
913
914 procedure SET_FORWARD_RESERVE_APPROVER(itemtype in varchar2,
915 itemkey in varchar2,
916 actid in number,
917 funcmode in varchar2,
918 resultout out NOCOPY varchar2) IS
919 l_progress VARCHAR2(300) := '000';
920
921
922 l_doc_string varchar2(200);
923
924 l_value VARCHAR2(2000);
925 l_responder_id NUMBER := NULL;
926 l_forward_to_id NUMBER := NULL;
927 l_end_date DATE; -- notification end date
928
929 l_user_name fnd_user.user_name%TYPE;
930 l_preparer_user_name fnd_user.user_name%TYPE;
931
932 l_disp_user_name VARCHAR2(2000);
933 BEGIN
934
935 l_progress := ' SET_FORWARD_RESERVE_APPROVER: 001- at beginning of function';
936 IF (g_po_wf_debug = 'Y') THEN
937 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
938 END IF;
939
940
941 IF (funcmode='RUN') THEN
942
943
944 PO_APPROVAL_LIST_WF1S.get_approval_response(itemtype => itemtype,
945 itemkey => itemkey,
946 responderId => l_responder_id,
947 response =>l_value,
948 responseEndDate =>l_end_date,
949 forwardToId => l_forward_to_id);
950
951
952 l_progress := ' DO 2 Roopal Update_Approval_List_Response:- p_itemtype: ' || itemtype || ' l_forward_to_id: ' || l_forward_to_id;
953 IF (g_po_wf_debug = 'Y') THEN
954 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
955 END IF;
956
957 po_wf_util_pkg.SetItemAttrNumber( itemtype => itemtype,
958 itemkey => itemkey,
959 aname => 'APPROVER_EMPID',
960 avalue => l_forward_to_id
961 );
962
963
964
965 PO_REQAPPROVAL_INIT1.get_user_name
966 (
967 p_employee_id =>l_forward_to_id,
968 x_username =>l_user_name,
969 x_user_display_name => l_disp_user_name);
970
971
972 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
973 itemkey => itemkey,
974 aname => 'APPROVER_USER_NAME',
975 avalue => l_user_name
976 );
977
978
979
980 l_progress := ' DO 2 Roopal Update_Approval_List_Response:- l_user_name: ' || l_user_name || ' l_disp_user_name: ' || l_disp_user_name;
981 IF (g_po_wf_debug = 'Y') THEN
982 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
983 END IF;
984
985 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
986 RETURN;
987 END IF; -- run mode
988
989 EXCEPTION
990
991 WHEN OTHERS THEN
992
993 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
994 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
995 wf_core.context('POR_AME_APPROVAL_LIST_WF1S',
996 'SET_FORWARD_RESERVE_APPROVER',l_progress,sqlerrm);
997
998 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQAPPRV_PVT.SET_FORWARD_RESERVE_APPROVER');
999
1000 RAISE;
1001
1002 END SET_FORWARD_RESERVE_APPROVER;
1003
1004
1005
1006 END POR_AME_APPROVAL_LIST_WF1S;