[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.2 2006/03/10 09:38:38 yqian noship $ */
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
122 IF l_interface_source = 'PO_FORM' THEN
123
124 l_forward_to := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
125 itemkey => itemkey,
126 aname => 'FORWARD_TO_ID');
127 l_progress := '002';
128 IF (g_po_wf_debug = 'Y') THEN
129 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
130 END IF;
131
132 If ( l_forward_to is not null ) then
133 por_ame_approval_list.change_first_approver ( pReqHeaderId => l_document_id,
134 pPersonId => l_forward_to,
135 pApprovalListStr => l_ApprovalListStr,
136 pApprovalListCount => l_ApprovalListCount,
137 pQuoteChar => l_QuoteChar,
138 pFieldDelimiter => l_FieldDelimiter );
139
140 l_progress := '003';
141 IF (g_po_wf_debug = 'Y') THEN
142 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
143 END IF;
144
145 -- return the req to requester if forwarding fails
146 if ( l_ApprovalListCount = 0 or l_ApprovalListStr = 'NO_DATA_FOUND' or l_ApprovalListStr = 'EXCEPTION' ) then
147 resultout:='COMPLETE:'||'N';
148 return;
149 end if;
150
151 End If; -- for l_forward_to is not null
152
153 END IF; -- for 'is_form'
154
155 resultout:='COMPLETE:'||'Y';
156
157 ELSE
158
159 resultout:='COMPLETE:'||'N';
160
161 END IF;
162
163 l_progress := '004';
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 END IF; -- FOR 'RUN' MODE
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
173 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
174 wf_core.context('POR_AME_APPROVAL_LIST_WF1S','setAmeAttributes',l_progress,sqlerrm);
175
176 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_APPROVAL_LIST_WF1S.setAmeAttributes');
177 RAISE;
178
179 end;
180
181
182 --Public procedures
183 --------------------------------------------------------------------------------
184 --Start of Comments
185 --Name: Is_Ame_For_Approval
186 --Pre-reqs:
187 -- None.
188 --Modifies:
189 -- None.
190 --Locks:
191 -- None.
192 --Function:
193 -- Workflow activity PL/SQL handler
194 -- Check if AME should be used for requisition approval process
195 -- if Yes then
196 -- initialize ame approval process
197 -- set attribute 'AME_TRANSACTION_TYPE' and 'IS_AME_APPROVAL'
198 -- Returns 'Y' if the workflow should be routed using AME for approval.
199 -- Returns 'N' if the workflow should not be routed using AME for approval.
200 --Parameters:
201 --IN:
202 -- Standard workflow IN parameters
203 --OUT:
204 -- Standard workflow OUT parameters
205 --Testing:
206 --
207 --End of Comments
208 -------------------------------------------------------------------------------
209 procedure Is_Ame_For_Approval(itemtype in varchar2,
210 itemkey in varchar2,
211 actid in number,
212 funcmode in varchar2,
213 resultout out NOCOPY varchar2) IS
214 l_progress VARCHAR2(100) := '000';
215 l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
216 l_document_subtype PO_DOCUMENT_TYPES.DOCUMENT_SUBTYPE%TYPE;
217 l_is_ame_approval boolean;
218 l_doc_string varchar2(200);
219 l_preparer_user_name varchar2(100);
220 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
221
222 begin
223
224 IF (funcmode = 'RUN') THEN
225 l_progress := 'Is_Ame_For_Approval: 001';
226 IF (g_po_wf_debug = 'Y') THEN
227 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
228 END IF;
229
230 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
231 itemkey => itemkey,
232 aname => 'DOCUMENT_TYPE');
233
234 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
235 itemkey => itemkey,
236 aname => 'DOCUMENT_SUBTYPE');
237
238
239 SELECT ame_transaction_type
240 INTO l_transaction_type
241 FROM po_document_types
242 WHERE document_type_code = l_document_type
243 and document_subtype = l_document_subtype;
244
245 if (l_transaction_type is not null) then
246
247 PO_WF_UTIL_PKG.SetItemAttrText( itemtype => itemType,
248 itemkey => itemkey,
249 aname => 'AME_TRANSACTION_TYPE',
250 avalue => l_transaction_type);
251 PO_WF_UTIL_PKG.SetItemAttrText( itemtype => itemType,
252 itemkey => itemkey,
253 aname => 'IS_AME_APPROVAL',
254 avalue => 'Y');
255
256 resultout:='COMPLETE:'||'Y';
257
258 else
259 PO_WF_UTIL_PKG.SetItemAttrText( itemtype => itemType,
260 itemkey => itemkey,
261 aname => 'IS_AME_APPROVAL',
262 avalue => 'N');
263
264 resultout:='COMPLETE:'||'N';
265
266 end if;
267
268 END IF;
269
270 EXCEPTION
271 WHEN OTHERS THEN
272 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
273 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
274 wf_core.context('POR_AME_APPROVAL_LIST_WF1S','Is_Ame_For_Approval',l_progress,sqlerrm);
275
276 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');
277 RAISE;
278
279 END Is_Ame_For_Approval;
280
281 --------------------------------------------------------------------------------
282 --Start of Comments
283 --Name: Is_Ame_For_Rco_Approval
284 --Pre-reqs:
285 -- None.
286 --Modifies:
287 -- None.
288 --Locks:
289 -- None.
290 --Function:
291 -- Workflow activity PL/SQL handler
292 -- Check if AME should be used for RCO approval process
293 -- if Yes then
294 -- initialize ame approval process
295 -- set attribute 'AME_TRANSACTION_TYPE' and 'IS_AME_APPROVAL'
296 -- Returns 'Y' if the workflow should be routed using AME for approval.
297 -- Returns 'N' if the workflow should not be routed using AME for approval.
298 --Parameters:
299 --IN:
300 -- Standard workflow IN parameters
301 --OUT:
302 -- Standard workflow OUT parameters
303 --Testing:
304 --
305 --End of Comments
306 -------------------------------------------------------------------------------
307 procedure Is_Ame_For_Rco_Approval(itemtype in varchar2,
308 itemkey in varchar2,
309 actid in number,
310 funcmode in varchar2,
311 resultout out NOCOPY varchar2) IS
312 l_progress VARCHAR2(100) := '000';
313 l_is_ame_approval boolean;
314 l_doc_string varchar2(200);
315 l_preparer_user_name varchar2(100);
316 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
317
318 begin
319
320 IF (funcmode = 'RUN') THEN
321 l_progress := 'Is_Ame_For_Rco_Approval: 001';
322 IF (g_po_wf_debug = 'Y') THEN
323 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
324 END IF;
325
326
327 begin
328 SELECT ame_transaction_type
329 INTO l_transaction_type
330 FROM po_document_types
331 WHERE document_type_code = 'CHANGE_REQUEST'
332 and document_subtype = 'REQUISITION' ;
333 exception
334 when others then
335 PO_WF_UTIL_PKG.SetItemAttrText( itemtype => itemType,
336 itemkey => itemkey,
337 aname => 'IS_AME_APPROVAL',
338 avalue => 'N');
339 return;
340 end;
341
342 l_progress := 'Is_Ame_For_Rco_Approval: 002';
343 IF (g_po_wf_debug = 'Y') THEN
344 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
345 END IF;
346
347 if (l_transaction_type is not null) then
348
349 PO_WF_UTIL_PKG.SetItemAttrText( itemtype => itemType,
350 itemkey => itemkey,
351 aname => 'AME_TRANSACTION_TYPE',
352 avalue => l_transaction_type);
353 PO_WF_UTIL_PKG.SetItemAttrText( itemtype => itemType,
354 itemkey => itemkey,
355 aname => 'IS_AME_APPROVAL',
356 avalue => 'Y');
357
358 resultout:='COMPLETE:'||'Y';
359
360 else
361
362 PO_WF_UTIL_PKG.SetItemAttrText( itemtype => itemType,
363 itemkey => itemkey,
364 aname => 'IS_AME_APPROVAL',
365 avalue => 'N');
366 resultout:='COMPLETE:'||'N';
367 end if;
368
369 END IF;
370
371 EXCEPTION
372 WHEN OTHERS THEN
373 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
374 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
375 wf_core.context('POR_AME_APPROVAL_LIST_WF1S','Is_Ame_For_Rco_Approval',l_progress,sqlerrm);
376
377 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');
378 RAISE;
379
380 END Is_Ame_For_Rco_Approval;
381
382 --------------------------------------------------------------------------------
383 --Start of Comments
384 --Name:
385 --Pre-reqs:
386 -- None.
387 --Modifies:
388 -- None.
389 --Locks:
390 -- None.
391 --Function:
392 -- Workflow activity PL/SQL handler
393 -- Get the next approver name from the AME approval list
394 -- And update workflow attributes.
395 -- If no next approver is found, approval routing will terminate.
396 --Parameters:
397 --IN:
398 -- Standard workflow IN parameters
399 --OUT:
400 -- Standard workflow OUT parameters
401 --Testing:
402 --
403 --End of Comments
404
405 --Note: For 11.5 WF only. Obsoleted in R12
406 -------------------------------------------------------------------------------
407 procedure Get_Next_Approver(itemtype in varchar2,
408 itemkey in varchar2,
409 actid in number,
410 funcmode in varchar2,
411 resultout out NOCOPY varchar2) IS
412 l_progress VARCHAR2(100) := '000';
413 l_document_id NUMBER;
414 l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
415 l_document_subtype PO_DOCUMENT_TYPES.DOCUMENT_SUBTYPE%TYPE;
416 l_next_approver_id NUMBER;
417 l_next_approver_user_name fnd_user.user_name%TYPE;
418 l_next_approver_disp_name wf_users.display_name%TYPE;
419 l_orig_system wf_users.orig_system%TYPE := 'PER';
420 l_sequence_num NUMBER;
421 l_approver_type VARCHAR2(30);
422 E_FAILURE EXCEPTION;
423
424 l_doc_string varchar2(200);
425 l_preparer_user_name fnd_user.user_name%TYPE;
426 l_org_id number;
427
428 l_next_approver ame_util.approverRecord;
429 l_insertion_type VARCHAR2(30);
430 l_authority_type VARCHAR2(30);
431 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
432
433 BEGIN
434 IF (funcmode = 'RUN') THEN
435
436 l_progress := 'Get_Next_Approver: 001';
437 IF (g_po_wf_debug = 'Y') THEN
438 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
439 END IF;
440
441 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
442 itemkey => itemkey,
443 aname => 'DOCUMENT_ID');
444
445 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
446 itemkey => itemkey,
447 aname => 'DOCUMENT_TYPE');
448
449 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
450 itemkey => itemkey,
451 aname => 'DOCUMENT_SUBTYPE');
452
453 l_transaction_type := wf_engine.GetItemAttrText (itemtype => itemtype,
454 itemkey => itemkey,
455 aname => 'AME_TRANSACTION_TYPE');
456
457
458 l_progress := 'Get_Next_Approver: 002-'||to_char(l_document_id)||'-'||
459 l_document_type||'-'||l_document_subtype;
460 IF (g_po_wf_debug = 'Y') THEN
461 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
462 END IF;
463 ame_api.getNextApprover(applicationIdIn=>applicationId,
464 transactionIdIn=>l_document_id,
465 transactionTypeIn=>l_transaction_type,
466 nextApproverOut=>l_next_approver);
467 l_progress := ('l_next_approver=' || to_char(l_next_approver.person_id));
468 IF (g_po_wf_debug = 'Y') THEN
469 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
470 END IF;
471
472 IF l_next_approver.approval_status = ame_util.exceptionStatus THEN
473 raise E_FAILURE;
474 ELSIF ((l_next_approver.user_id is null) and
475 (l_next_approver.person_id is null) and
476 (l_next_approver.first_name is null) and
477 (l_next_approver.last_name is null) and
478 (l_next_approver.api_insertion is null) and
479 (l_next_approver.authority is null) and
480 (l_next_approver.approval_status is null)) THEN
481 resultout:='COMPLETE:'||'NO_NEXT_APPROVER';
482 return;
483 ELSE
484 l_next_approver_id := l_next_approver.person_id;
485 l_insertion_type := l_next_approver.api_insertion;
486 l_authority_type := l_next_approver.authority;
487 l_progress := 'Get_Next_Approver: 003- get_next_approver - '||
488 to_char(l_next_approver_id);
489
490 IF (g_po_wf_debug = 'Y') THEN
491 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
492 END IF;
493
494 wf_engine.SetItemAttrNumber ( itemtype => itemType,
495 itemkey => itemkey,
496 aname => 'APPROVER_EMPID',
497 avalue => l_next_approver_id);
498
499
500 wf_engine.SetItemAttrNumber ( itemtype => itemType,
501 itemkey => itemkey,
502 aname => 'FORWARD_TO_ID',
503 avalue => l_next_approver_id);
504 wf_engine.SetItemAttrText( itemtype => itemType,
505 itemkey => itemkey,
506 aname => 'AME_INSERTION_TYPE' ,
507 avalue => l_insertion_type);
508
509 wf_engine.SetItemAttrText( itemtype => itemType,
510 itemkey => itemkey,
511 aname => 'AME_AUTHORITY_TYPE' ,
512 avalue => l_authority_type);
513
514 l_orig_system:= 'PER';
515
516 WF_DIRECTORY.GetUserName(l_orig_system,
517 l_next_approver_id,
518 l_next_approver_user_name,
519 l_next_approver_disp_name);
520
521 l_progress := 'Get_Next_Approver: 004- GetUserName - '||
522 l_next_approver_user_name;
523 IF (g_po_wf_debug = 'Y') THEN
524 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
525 END IF;
526
527
528 wf_engine.SetItemAttrText( itemtype => itemType,
529 itemkey => itemkey,
530 aname => 'APPROVER_USER_NAME' ,
531 avalue => l_next_approver_user_name);
532
533 wf_engine.SetItemAttrText( itemtype => itemType,
534 itemkey => itemkey,
535 aname => 'APPROVER_DISPLAY_NAME' ,
536 avalue => l_next_approver_disp_name);
537
538 wf_engine.SetItemAttrText( itemtype => itemType,
539 itemkey => itemkey,
540 aname => 'FORWARD_TO_USERNAME' ,
541 avalue => l_next_approver_user_name);
542
543 wf_engine.SetItemAttrText( itemtype => itemType,
544 itemkey => itemkey,
545 aname => 'FORWARD_TO_DISPLAY_NAME' ,
546 avalue => l_next_approver_disp_name);
547
548 resultout:='COMPLETE:'||'VALID_APPROVER';
549 return;
550 END IF;
551 END IF;
552
553 EXCEPTION
554 WHEN OTHERS THEN
555 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
556 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
557 wf_core.context('POR_AME_APPROVAL_LIST_WF1S','Get_Next_Approver',l_progress,sqlerrm);
558
559 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_APPROVAL_LIST_WF1S.GET_NEXT_APPROVER');
560
561 RAISE;
562
563 END Get_Next_Approver;
564
565
566
567 --------------------------------------------------------------------------------
568 --Start of Comments
569 --Name: Update_Approval_List_Response
570 --Pre-reqs:
571 -- None.
572 --Modifies:
573 -- None.
574 --Locks:
575 -- None.
576 --Function:
577 -- Workflow activity PL/SQL handler
578 -- After an approval notification is responded, update AME approval list.
579 --Parameters:
580 --IN:
581 -- Standard workflow IN parameters
582 --OUT:
583 -- Standard workflow OUT parameters
584 --Testing:
585 --
586 --End of Comments
587 -------------------------------------------------------------------------------
588 procedure Update_Approval_List_Response(itemtype in varchar2,
589 itemkey in varchar2,
590 actid in number,
591 funcmode in varchar2,
592 resultout out NOCOPY varchar2) IS
593 l_progress VARCHAR2(300) := '000';
594 l_approver_id NUMBER := NULL;
595 l_document_id NUMBER;
596
597
598 l_doc_string varchar2(200);
599
600 l_org_id number;
601 l_insertion_type VARCHAR2(30);
602 l_authority_type VARCHAR2(30);
603 l_value VARCHAR2(2000);
604 l_responder_id NUMBER := NULL;
605 l_forward_to_id NUMBER := NULL;
606 l_end_date DATE; -- notification end date
607 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
608
609 l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
610 l_document_subtype PO_DOCUMENT_TYPES.DOCUMENT_SUBTYPE%TYPE;
611 l_preparer_user_name fnd_user.user_name%TYPE;
612
613 BEGIN
614
615 l_progress := ' Update_Approval_List_Response: 001- at beginning of function';
616 IF (g_po_wf_debug = 'Y') THEN
617 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
618 END IF;
619
620 IF (funcmode='RUN') THEN
621
622 PO_APPROVAL_LIST_WF1S.get_approval_response(itemtype => itemtype,
623 itemkey => itemkey,
624 responderId => l_responder_id,
625 response =>l_value,
626 responseEndDate =>l_end_date,
627 forwardToId => l_forward_to_id);
628
629 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
630 itemkey => itemkey,
631 aname => 'DOCUMENT_ID');
632
633 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
634 itemkey => itemkey,
635 aname => 'DOCUMENT_TYPE');
636
637 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
638 itemkey => itemkey,
639 aname => 'DOCUMENT_SUBTYPE');
640
641 l_approver_id := wf_engine.GetItemAttrNumber(itemtype=>itemtype,
642 itemkey=>itemkey,
643 aname=>'APPROVER_EMPID');
644
645 l_insertion_type := wf_engine.GetItemAttrText(itemtype => itemtype,
646 itemkey => itemkey,
647 aname => 'AME_INSERTION_TYPE');
648
649 l_authority_type := wf_engine.GetItemAttrText(itemtype => itemtype,
650 itemkey => itemkey,
651 aname => 'AME_AUTHORITY_TYPE');
652
653 l_progress := 'Update_Approval_List_Response: 010 APP'||
654 to_char(l_approver_id) || ' RES'||to_char(l_responder_id);
655
656 l_progress := l_progress || ' FWD'||to_char(l_forward_to_id) ||
657 ' RESPONSE' || l_value || ' INSERTION? '||
658 l_insertion_type|| ' AUTHORITY? ' || l_authority_type;
659
660 IF (g_po_wf_debug = 'Y') THEN
661 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
662 END IF;
663
664 l_transaction_type := wf_engine.GetItemAttrText (itemtype => itemtype,
665 itemkey => itemkey,
666 aname => 'AME_TRANSACTION_TYPE');
667
668 updateApprovalListResponse(itemtype=>itemtype,
669 itemkey=>itemkey,
670 p_transaction_type=>l_transaction_type,
671 p_document_id=>l_document_id,
672 p_approver_id=>l_approver_id,
673 p_insertion_type=>l_insertion_type,
674 p_authority_type=>l_authority_type,
675 p_forward_to_id=>l_forward_to_id,
676 p_response=>l_value);
677
678 IF (g_po_wf_debug = 'Y') THEN
679 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
680 END IF;
681
682 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
683 RETURN;
684 END IF; -- run mode
685
686 EXCEPTION
687
688 WHEN OTHERS THEN
689
690 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
691 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
692 wf_core.context('POR_AME_APPROVAL_LIST_WF1S',
693 'Update_Approval_List_Response',l_progress,sqlerrm);
694
695 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQAPPRV_PVT.UPDATE_APPROVAL_LIST_RESPONSE');
696
697 RAISE;
698
699 END Update_Approval_List_Response;
700
701
702 --------------------------------------------------------------------------------
703 --Start of Comments
704 --Name: Update_Approver_Timeout
705 --Pre-reqs:
706 -- None.
707 --Modifies:
708 -- None.
709 --Locks:
710 -- None.
711 --Function:
712 -- Workflow activity PL/SQL handler
713 -- After an approval notification is timed out, update AME approval list.
714 --Parameters:
715 --IN:
716 -- Standard workflow IN parameters
717 --OUT:
718 -- Standard workflow OUT parameters
719 --Testing:
720 --
721 --End of Comments
722 -------------------------------------------------------------------------------
723 procedure Update_Approver_Timeout(itemtype in varchar2,
724 itemkey in varchar2,
725 actid in number,
726 funcmode in varchar2,
727 resultout out NOCOPY varchar2) IS
728 l_progress VARCHAR2(300) := '000';
729 l_approver_id NUMBER := NULL;
730 l_document_id NUMBER;
731 l_doc_string varchar2(200);
732 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
733 l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
734 l_document_subtype PO_DOCUMENT_TYPES.DOCUMENT_SUBTYPE%TYPE;
735 l_preparer_user_name fnd_user.user_name%TYPE;
736
737
738 BEGIN
739
740 l_progress := ' Update_Approver_timeout: 001- at beginning of function';
741 IF (g_po_wf_debug = 'Y') THEN
742 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
743 END IF;
744
745 IF (funcmode='RUN') THEN
746 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
747 itemkey => itemkey,
748 aname => 'DOCUMENT_ID');
749
750 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
751 itemkey => itemkey,
752 aname => 'DOCUMENT_TYPE');
753
754 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
755 itemkey => itemkey,
756 aname => 'DOCUMENT_SUBTYPE');
757
758 l_approver_id := wf_engine.GetItemAttrNumber(itemtype=>itemtype,
759 itemkey=>itemkey,
760 aname=>'APPROVER_EMPID');
761
762 l_transaction_type := wf_engine.GetItemAttrText (itemtype => itemtype,
763 itemkey => itemkey,
764 aname => 'AME_TRANSACTION_TYPE');
765 updateApprovalListResponse(itemtype=>itemtype,
766 itemkey=>itemkey,
767 p_transaction_type=>l_transaction_type,
768 p_document_id=>l_document_id,
769 p_approver_id=>l_approver_id,
770 p_response=>'TIMEOUT');
771
772 l_progress := ' Update_Approver_timeout: 002- at end of function';
773 IF (g_po_wf_debug = 'Y') THEN
774 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
775 END IF;
776
777 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
778 RETURN;
779 END IF; -- run mode
780
781 EXCEPTION
782
783 WHEN OTHERS THEN
784
785 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
786 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
787 wf_core.context('POR_AME_APPROVAL_LIST_WF1S',
788 'Update_Approval_List_Response',l_progress,sqlerrm);
789
790 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQAPPRV_PVT.UPDATE_APPROVAL_LIST_RESPONSE');
791
792 RAISE;
793
794 END Update_Approver_Timeout;
795
796
797
798 --Note: procedure updateApprovalListResponse is For 11.5 WF only. Obsoleted in R12.
799 PROCEDURE updateApprovalListResponse(itemtype in varchar2,
800 itemkey in varchar2,
801 p_transaction_type IN VARCHAR2,
802 p_document_id IN NUMBER,
803 p_approver_id IN NUMBER,
804 p_insertion_type IN VARCHAR2 default null,
805 p_authority_type IN VARCHAR2 default null,
806 p_forward_to_id IN NUMBER default null,
807 p_response IN VARCHAR2) IS
808
809 l_progress VARCHAR2(100) := '000';
810 forwardee ame_util.approverRecord;
811 currentApprover ame_util.approverRecord;
812
813 BEGIN
814
815 l_progress := 'transaction: '|| p_transaction_type || '; response:' || p_response;
816 currentApprover.person_id :=p_approver_id;
817 currentApprover.api_insertion :=p_insertion_type;
818 currentApprover.authority := p_authority_type;
819
820 if(p_response='APPROVE') then
821 ame_api.updateApprovalStatus2(applicationIdIn=>applicationId,
822 transactionIdIn=>p_document_id,
823 approvalStatusIn=>ame_util.approvedStatus,
824 approverPersonIdIn=>p_approver_id,
825 transactionTypeIn=>p_transaction_type);
826
827 elsif(p_response='REJECT') then
828 ame_api.updateApprovalStatus2(applicationIdIn=>applicationId,
829 transactionIdIn=>p_document_id,
830 approvalStatusIn=>ame_util.rejectStatus,
831 approverPersonIdIn=>p_approver_id,
832 transactionTypeIn=>p_transaction_type);
833
834 elsif(p_response='FORWARD') then
835 forwardee.authority := currentApprover.authority;
836 forwardee.person_id := p_forward_to_id;
837 if(currentApprover.authority = ame_util.authorityApprover and
838 (currentApprover.api_insertion = ame_util.apiAuthorityInsertion or
839 currentApprover.api_insertion = ame_util.oamGenerated)) then
840 forwardee.api_insertion := ame_util.apiAuthorityInsertion;
841 else
842 forwardee.api_insertion := ame_util.apiInsertion;
843 end if;
844
845 currentApprover.approval_status := ame_util.forwardStatus;
846 ame_api.updateApprovalStatus(applicationIdIn=>applicationId,
847 transactionIdIn=>p_document_id,
848 transactionTypeIn=>p_transaction_type,
849 approverIn=>currentApprover,
850 forwardeeIn=>forwardee);
851
852 elsif (p_response='APPROVE_AND_FORWARD') THEN
853
854 forwardee.authority := currentApprover.authority;
855 forwardee.person_id := p_forward_to_id;
856 if(currentApprover.authority = ame_util.authorityApprover and
857 (currentApprover.api_insertion = ame_util.apiAuthorityInsertion or
858 currentApprover.api_insertion = ame_util.oamGenerated)) then
859 forwardee.api_insertion := ame_util.apiAuthorityInsertion;
860 else
861 forwardee.api_insertion := ame_util.apiInsertion;
862 end if;
863
864 currentApprover.approval_status := ame_util.approveAndForwardStatus;
865 ame_api.updateApprovalStatus(applicationIdIn=>applicationId,
866 transactionIdIn=>p_document_id,
867 transactionTypeIn=>p_transaction_type,
868 approverIn=>currentApprover,
869 forwardeeIn=>forwardee);
870 elsif(p_response='TIMEOUT') then
871 ame_api.updateApprovalStatus2(applicationIdIn=>applicationId,
872 transactionIdIn=>p_document_id,
873 approvalStatusIn=>ame_util.noResponseStatus,
874 approverPersonIdIn=>p_approver_id,
875 transactionTypeIn=>p_transaction_type);
876
877
878 end if;
879
880 RETURN;
881 EXCEPTION
882
883 WHEN OTHERS THEN
884 wf_core.context('PO_AME_APPROVAL_LIST_WF1S',
885 'updateApprovalListResponse',l_progress,sqlerrm);
886
887 RAISE;
888 END updateApprovalListResponse;
889
890 END POR_AME_APPROVAL_LIST_WF1S;