[Home] [Help]
PACKAGE BODY: APPS.POR_AME_REQ_WF_PVT
Source
1 PACKAGE BODY POR_AME_REQ_WF_PVT AS
2 /* $Header: POXAMEPB.pls 120.41.12010000.2 2008/08/02 10:33:44 rramasam 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 g_next_approvers ame_util.approversTable2;
8
9 FUNCTION position_has_valid_approvers( documentId NUMBER, documentType VARCHAR2 )RETURN VARCHAR2;
10
11 Function is_last_approver_record( documentId NUMBER, documentType VARCHAR2, approverRecord in ame_util.approverRecord2 ) RETURN VARCHAR2;
12
13 PROCEDURE UpdateActionHistory(p_document_id NUMBER,
14 p_action VARCHAR2,
15 p_note VARCHAR2,
16 p_current_approver NUMBER);
17
18 --------------------------------------------------------------------------------
19 --Start of Comments
20 --Name: Get_Next_Approvers
21 --Pre-reqs:
22 -- None.
23 --Modifies:
24 -- None.
25 --Locks:
26 -- None.
27 --Function:
28 -- Workflow activity PL/SQL handler
29 -- Get the next approver name from the AME approval list
30 -- And update workflow attributes.
31 -- If no next approver is found, approval routing will terminate.
32 --Parameters:
33 --IN:
34 -- Standard workflow IN parameters
35 --OUT:
36 -- Standard workflow OUT parameters
37 --Testing:
38 --
39 --End of Comments
40 -------------------------------------------------------------------------------
41 procedure Get_Next_Approvers(itemtype in varchar2,
42 itemkey in varchar2,
43 actid in number,
44 funcmode in varchar2,
45 resultout out NOCOPY varchar2) IS
46 l_progress VARCHAR2(3500) := '000';
47 l_document_id NUMBER;
48 l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
49 l_document_subtype PO_DOCUMENT_TYPES.DOCUMENT_SUBTYPE%TYPE;
50 l_next_approver_id NUMBER;
51 l_next_approver_user_name fnd_user.user_name%TYPE;
52 l_next_approver_disp_name wf_users.display_name%TYPE;
53 l_orig_system wf_users.orig_system%TYPE := ame_util.perOrigSystem;
54 l_sequence_num NUMBER;
55 l_approver_type VARCHAR2(30);
56
57 l_doc_string varchar2(200);
58 l_preparer_user_name fnd_user.user_name%TYPE;
59 l_org_id number;
60
61 l_next_approver ame_util.approverRecord;
62 l_insertion_type VARCHAR2(30);
63 l_authority_type VARCHAR2(30);
64 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
65
66 l_completeYNO varchar2(100);
67 l_position_has_valid_approvers varchar2(10);
68 l_need_to_get_next_approver boolean;
69
70 l_ame_exception ame_util.longestStringType;
71
72 BEGIN
73 IF (funcmode = 'RUN') THEN
74
75 l_progress := 'Get_Next_Approver: 001';
76 IF (g_po_wf_debug = 'Y') THEN
77 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
78 END IF;
79
80 /* Check if there is any AME exception.
81 If yes, then return 'invalid approver' */
82 l_ame_exception := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
83 itemkey => itemkey,
84 aname => 'AME_EXCEPTION');
85 IF l_ame_exception IS NOT NULL THEN
86 resultout:='COMPLETE:'||'INVALID_APPROVER';
87 RETURN;
88 END IF;
89
90 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
91 itemkey => itemkey,
92 aname => 'DOCUMENT_ID');
93
94 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
95 itemkey => itemkey,
96 aname => 'DOCUMENT_TYPE');
97
98 l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
99 itemkey => itemkey,
100 aname => 'DOCUMENT_SUBTYPE');
101
102 l_transaction_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
103 itemkey => itemkey,
104 aname => 'AME_TRANSACTION_TYPE');
105
106 l_progress := 'Get_Next_Approver: 002-'||to_char(l_document_id)||'-'|| l_document_type||'-'||l_document_subtype;
107
108 IF (g_po_wf_debug = 'Y') THEN
109 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
110 END IF;
111
112 -- Get the next approver from AME.
113 LOOP
114
115 l_need_to_get_next_approver := FALSE;
116 BEGIN
117
118 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanTrue;
119 ame_api2.getNextApprovers4( applicationIdIn=>applicationId,
120 transactionIdIn=>l_document_id,
121 transactionTypeIn=>l_transaction_type,
122 approvalProcessCompleteYNOut=>l_completeYNO,
123 nextApproversOut=>g_next_approvers
124 );
125
126 EXCEPTION
127 WHEN OTHERS THEN
128
129 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
130 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
131 wf_core.context('POR_AME_REQ_WF_PVT','Get_Next_Approvers: Unable to get the next approvers from AME.',l_progress,sqlerrm);
132
133 IF (g_po_wf_debug = 'Y') THEN
134 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string);
135 END IF;
136
137 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.GET_NEXT_APPROVERS',l_document_id);
138
139 resultout:='COMPLETE:'||'INVALID_APPROVER';
140 return;
141 END;
142
143 l_progress := 'Get_Next_Approver: 003- getNextApprovers4(). Approvers :' || g_next_approvers.count || ' -- Approval Process Completed :' || l_completeYNO ;
144 IF (g_po_wf_debug = 'Y') THEN
145 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
146 END IF;
147
148 if ( g_next_approvers.count > 0 ) then
149
150 l_position_has_valid_approvers := position_has_valid_approvers(l_document_id, l_transaction_type) ;
151
152 l_progress := 'Get_Next_Approver: 004 - l_position_has_valid_approvers :' || l_position_has_valid_approvers;
153 IF (g_po_wf_debug = 'Y') THEN
154 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
155 END IF;
156
157 l_progress := 'Get_Next_Approver: 005- Approvers after the validation process :' || g_next_approvers.count;
158 IF (g_po_wf_debug = 'Y') THEN
159 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
160 END IF;
161
162 if( g_next_approvers.count = 0 AND 'NO_USERS' = l_position_has_valid_approvers ) then
163 l_need_to_get_next_approver := TRUE;
164 end if;
165
166 end if;
167
168 EXIT WHEN l_need_to_get_next_approver = FALSE;
169 END LOOP;
170
171 -- Check the number of next approvers. If the count is zero, then verify the approval process is completed or not.
172 if ( g_next_approvers.count > 0 ) then
173
174 if( 'N' = l_position_has_valid_approvers ) then
175 resultout:='COMPLETE:'||'INVALID_APPROVER';
176 else
177 resultout:='COMPLETE:'||'VALID_APPROVER';
178 end if;
179 return;
180
181 else
182
183 -- 'X' is the code when there is no rule needed and applied.
184
185 if (l_completeYNO in ('X','Y')) then
186 resultout:='COMPLETE:'||'NO_NEXT_APPROVER';
187 return;
188 else
189 resultout:='COMPLETE:'||'';
190 return;
191 end if;
192 end if;
193 end if;
194 EXCEPTION
195 WHEN OTHERS THEN
196
197 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
198 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
199 wf_core.context('POR_AME_REQ_WF_PVT','Get_Next_Approvers - Unexpected Exception: ',l_progress,sqlerrm);
200
201 IF (g_po_wf_debug = 'Y') THEN
202 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string);
203 END IF;
204
205 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.GET_NEXT_APPROVERS');
206 resultout:='COMPLETE:'||'INVALID_APPROVER';
207 return;
208 END Get_Next_Approvers;
209
210
211 --------------------------------------------------------------------------------
212 --Start of Comments
213 --Name: Launch_Parallel_Approval
214 --Pre-reqs:
215 -- None.
216 --Modifies:
217 -- None.
218 --Locks:
219 -- None.
220 --Function:
221 -- Workflow activity PL/SQL handler. This procedure is used to send the notification for the approvers.
222 -- Iterate through the list of approvers got from the API call ame_api2.getNextApprovers4.
223 -- Get the next approver name from the global variable g_next_approvers and for each retrieved approver
224 -- separate workflow process is kicked. Each process is called child process.
225 -- If there are 3 approvers, then 3 child process will be created and each of them will be notified at the same time.
226 --
227 -- If the next approver record is of Position Hierarchy type, then the users associated to the position_id will be
228 -- retrieved, will be alphabetically sorted using last_name and to the first user notification will be sent.
229 --
230 --Parameters:
231 --IN:
232 -- Standard workflow IN parameters
233 --OUT:
234 -- Standard workflow OUT parameters
235 --Testing:
236 --
237 --End of Comments
238 -------------------------------------------------------------------------------
239 procedure Launch_Parallel_Approval(itemtype in varchar2,
240 itemkey in varchar2,
241 actid in number,
242 funcmode in varchar2,
243 resultout out NOCOPY varchar2) IS
244
245 l_progress VARCHAR2(500) := '000';
246 l_document_id number;
247 l_item_key wf_items.item_key%TYPE;
248 l_next_approver_id number;
249 l_next_approver_name per_employees_current_x.full_name%TYPE;
250 l_next_approver_user_name VARCHAR2(100);
251 l_next_approver_disp_name VARCHAR2(240);
252 l_orig_system VARCHAR2(48);
253 l_org_id number;
254 l_functional_currency VARCHAR2(30);
255 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
256
257 n_varname Wf_Engine.NameTabTyp;
258 n_varval Wf_Engine.NumTabTyp;
259
260 t_varname Wf_Engine.NameTabTyp;
261 t_varval Wf_Engine.TextTabTyp;
262
263 l_no_positionholder exception;
264 l_preparer_user_name fnd_user.user_name%TYPE;
265 l_doc_string varchar2(200);
266 l_start_block_activity varchar2(1);
267 l_has_fyi_app varchar2(1);
268 l_approver_index NUMBER;
269
270 l_first_position_id NUMBER;
271 l_first_approver_id NUMBER;
272
273 begin
274 IF (funcmode='RUN') THEN
275
276 l_progress := 'Launch_Parallel_Approval: 001';
277 IF (g_po_wf_debug = 'Y') THEN
278 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
279 END IF;
280
281 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
282 itemkey => itemkey,
283 aname => 'DOCUMENT_ID');
284
285 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
286 itemkey => itemkey,
287 aname => 'ORG_ID');
288
289 l_start_block_activity := 'N';
290 l_has_fyi_app := 'N';
291 -- Iterate through the list of next approvers.
292 l_approver_index := g_next_approvers.first();
293 while ( l_approver_index is not null ) loop
294
295 l_progress := 'Launch_Parallel_Approval: 002 -- Next Approver :' || g_next_approvers(l_approver_index).name;
296 IF (g_po_wf_debug = 'Y') THEN
297 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
298 END IF;
299
300 SELECT
301 to_char(l_document_id) || '-' || to_char(PO_WF_ITEMKEY_S.nextval)
302 INTO l_item_key
303 FROM sys.dual;
304
305 -- Create a child process for the retrieved approver.
306 wf_engine.CreateProcess( itemtype => itemtype,
307 itemkey => l_item_key,
308 process => 'AME_PARALLEL_APPROVAL');
309
310
311 /* Need to set the parent child relationship between processes */
312 wf_engine.SetItemParent( itemtype => itemtype,
313 itemkey => l_item_key,
314 parent_itemtype => itemtype,
315 parent_itemkey => itemkey,
316 parent_context => NULL);
317
318 t_varname(1) := 'DOCUMENT_TYPE';
319 t_varval(1) := 'REQUISITION';
320
321 t_varname(2) := 'DOCUMENT_SUBTYPE';
322 t_varval(2) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
323 itemkey => itemkey,
324 aname => 'DOCUMENT_SUBTYPE');
325
326 t_varname(3) := 'PREPARER_USER_NAME';
327 t_varval(3) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
328 itemkey => itemkey,
329 aname => 'PREPARER_USER_NAME');
330
331 t_varname(4) := 'PREPARER_DISPLAY_NAME';
332 t_varval(4) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
333 itemkey => itemkey,
334 aname => 'PREPARER_DISPLAY_NAME');
335
336 t_varname(5) := 'FUNCTIONAL_CURRENCY';
337 t_varval(5) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
338 itemkey => itemkey,
339 aname => 'FUNCTIONAL_CURRENCY');
340
341 t_varname(6) := 'IS_AME_APPROVAL';
342 t_varval(6) := 'Y';
343
344 t_varname(7) := 'TOTAL_AMOUNT_DSP';
345 t_varval(7) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
346 itemkey => itemkey,
347 aname => 'TOTAL_AMOUNT_DSP');
348
349 t_varname(8) := 'FORWARD_FROM_DISP_NAME';
350 t_varval(8) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
351 itemkey => itemkey,
352 aname => 'FORWARD_FROM_DISP_NAME');
353
354 t_varname(9) := 'FORWARD_FROM_USER_NAME';
355 t_varval(9) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
356 itemkey => itemkey,
357 aname => 'FORWARD_FROM_USER_NAME');
358
359 t_varname(10) := 'REQ_DESCRIPTION';
360 t_varval(10) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
361 itemkey => itemkey,
362 aname => 'REQ_DESCRIPTION');
363
364 t_varname(11) := 'REQ_AMOUNT_CURRENCY_DSP';
365 t_varval(11) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
366 itemkey => itemkey,
367 aname => 'REQ_AMOUNT_CURRENCY_DSP');
368
369 t_varname(12) := 'TAX_AMOUNT_CURRENCY_DSP';
370 t_varval(12) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
371 itemkey => itemkey,
372 aname => 'TAX_AMOUNT_CURRENCY_DSP');
373
374 t_varname(13) := 'JUSTIFICATION';
375 t_varval(13) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
376 itemkey => itemkey,
377 aname => 'JUSTIFICATION');
378
379 t_varname(14) := 'CONTRACTOR_REQUISITION_FLAG';
380 t_varval(14) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
381 itemkey => itemkey,
382 aname => 'CONTRACTOR_REQUISITION_FLAG');
383
384 t_varname(15) := 'CONTRACTOR_REQUISITION_FLAG';
385 t_varval(15) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
386 itemkey => itemkey,
387 aname => 'CONTRACTOR_REQUISITION_FLAG');
388
389 t_varname(16) := 'CONTRACTOR_STATUS';
390 t_varval(16) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
391 itemkey => itemkey,
392 aname => 'CONTRACTOR_STATUS');
393
394 t_varname(17) := 'VENDOR_DISPLAY_NAME';
395 t_varval(17) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
396 itemkey => itemkey,
397 aname => 'VENDOR_DISPLAY_NAME');
398
399 t_varname(18) := 'IS_SUPPLIER_EMAIL_NOT_AVAIL';
400 t_varval(18) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
401 itemkey => itemkey,
402 aname => 'IS_SUPPLIER_EMAIL_NOT_AVAIL');
403
404 t_varname(19) := 'CONTRACTOR_ASSIGNMENT_REQD';
405 t_varval(19) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
406 itemkey => itemkey,
407 aname => 'CONTRACTOR_ASSIGNMENT_REQD');
408
409 t_varname(20) := 'DOCUMENT_NUMBER';
410 t_varval(20) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
411 itemkey => itemkey,
412 aname => 'DOCUMENT_NUMBER');
413
414 t_varname(21) := 'AME_TRANSACTION_TYPE';
415 t_varval(21) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
416 itemkey => itemkey,
417 aname => 'AME_TRANSACTION_TYPE');
418
419 l_progress := 'Launch_Parallel_Approval: 003 -- Record Type :' || g_next_approvers(l_approver_index).orig_system;
420 IF (g_po_wf_debug = 'Y') THEN
421 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
422 END IF;
423
424 -- Check whether Position Hierarchy or Employee-Sup Hierarchy setup or FND users.
425 if (g_next_approvers(l_approver_index).orig_system = ame_util.perOrigSystem) then
426 l_next_approver_id := g_next_approvers(l_approver_index).orig_system_id;
427 elsif (g_next_approvers(l_approver_index).orig_system = ame_util.posOrigSystem) then
428
429 begin
430
431 select first_position_id, first_approver_id
432 into l_first_position_id, l_first_approver_id
433 from po_requisition_headers_all
434 where l_document_id = requisition_header_id;
435
436 if (l_first_position_id is not NULL AND l_first_position_id=g_next_approvers(l_approver_index).orig_system_id ) then
437
438 l_next_approver_id := l_first_approver_id;
439
440 SELECT full_name
441 INTO l_next_approver_name
442 FROM per_all_people_f person
443 WHERE person_id = l_first_approver_id
444 --Bug#7207213#This query fetches multiple records so adding a filter
445 and trunc(sysdate) between person.effective_start_date and nvl(person.effective_end_date, trunc(sysdate));
446
447
448 else
449
450 /* find the persond id from the position_id*/
451 SELECT person_id, full_name into l_next_approver_id,l_next_approver_name FROM (
452 SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
453 WHERE asg.position_id = g_next_approvers(l_approver_index).orig_system_id and trunc(sysdate) between person.effective_start_date
454 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
455 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
456 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
457 and asg.assignment_status_type_id not in (
458 SELECT assignment_status_type_id FROM per_assignment_status_types
459 WHERE per_system_status = 'TERM_ASSIGN'
460 ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
461 ) where rownum = 1;
462
463 end if;
464
465 exception
466 WHEN NO_DATA_FOUND THEN
467 RAISE;
468 END;
469
470 elsif (g_next_approvers(l_approver_index).orig_system = ame_util.fndUserOrigSystem) then
471 SELECT employee_id
472 into l_next_approver_id
473 FROM fnd_user
474 WHERE user_id = g_next_approvers(l_approver_index).orig_system_id
475 and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
476 end if;
477
478 t_varname(22) := 'AME_APPROVER_TYPE';
479 t_varval(22) := g_next_approvers(l_approver_index).orig_system;
480
481
482 WF_DIRECTORY.GetUserName(ame_util.perOrigSystem, l_next_approver_id, l_next_approver_user_name, l_next_approver_disp_name);
483
484 l_progress := 'Launch_Parallel_Approval: 004 -- Next Approver User Name -- display Name:' || l_next_approver_user_name || ' -- ' || l_next_approver_disp_name;
485 IF (g_po_wf_debug = 'Y') THEN
486 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
487 END IF;
488 --bug6843383 start
489 IF (g_next_approvers(l_approver_index).orig_system = ame_util.perOrigSystem) then
490 t_varname(23) := 'APPROVER_USER_NAME';
491 t_varval(23) := g_next_approvers(l_approver_index).name;
492
493 t_varname(24) := 'APPROVER_DISPLAY_NAME';
494 t_varval(24) := g_next_approvers(l_approver_index).display_name;
495
496 ELSE
497
498 t_varname(23) := 'APPROVER_USER_NAME';
499 t_varval(23) := l_next_approver_user_name;
500
501 t_varname(24) := 'APPROVER_DISPLAY_NAME';
502 t_varval(24) := l_next_approver_disp_name;
503 END IF;
504 /* Kick off the process */
505 l_progress:= '30: start_wf_line_process: Kicking off StartProcess';
506 IF (g_po_wf_debug = 'Y') THEN
507 po_wf_debug_pkg.insert_debug(itemtype,itemkey,l_progress);
508 END IF;
509
510 t_varname(25) := 'AME_IS_FYI_APPROVER';
511 if (g_next_approvers(l_approver_index).approver_category = ame_util.fyiApproverCategory) then
512 t_varval(25) :='Y';
513 l_has_fyi_app := 'Y';
514 l_start_block_activity := 'N';
515 else
516 t_varval(25) :='N';
517
518 if (l_has_fyi_app = 'N') then
519 -- only start BLOCK if there are no FYI approvers
520 l_start_block_activity := 'Y';
521 end if;
522
523 end if;
524
525 t_varname(26) := 'VIEW_REQ_DTLS_URL';
526 t_varval(26) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
527 itemkey => itemkey,
528 aname => 'VIEW_REQ_DTLS_URL');
529 t_varval (26) := t_varval(26) || '&' || 'item_key=' || l_item_key;
530
531 t_varname(27) := 'EDIT_REQ_URL';
532 t_varval(27) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
533 itemkey => itemkey,
534 aname => 'EDIT_REQ_URL');
535 t_varval (27) := t_varval(27) || '&' || 'item_key=' || l_item_key;
536
537 t_varname(28) := 'RESUBMIT_REQ_URL';
538 t_varval(28) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
539 itemkey => itemkey,
540 aname => 'RESUBMIT_REQ_URL');
541
542 t_varname(28) := 'OPEN_FORM_COMMAND';
543 t_varval(28) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
544 itemkey => itemkey,
545 aname => 'OPEN_FORM_COMMAND');
546
547 -- Set the item attributes.
548 Wf_Engine.SetItemAttrTextArray(itemtype, l_item_key,t_varname,t_varval);
549
550 n_varname(1) := 'DOCUMENT_ID';
551 n_varval(1) := l_document_id;
552
553 n_varname(2) := 'ORG_ID';
554 n_varval(2) := l_org_id;
555
556 n_varname(3) := 'AME_APPROVER_ID';
557 n_varval(3) := g_next_approvers(l_approver_index).orig_system_id;
558
559 n_varname(4) := 'APPROVER_EMPID';
560 n_varval(4) := l_next_approver_id;
561
562 -- Set the approval group id as 1 for adhoc approvers
563 n_varname(5) := 'APPROVAL_GROUP_ID';
564
565 if (g_next_approvers(l_approver_index).api_insertion = 'Y') then
566 n_varval(5) := 1;
567 else
568 n_varval(5) := g_next_approvers(l_approver_index).group_or_chain_id;
569 end if;
570
571 Wf_Engine.SetItemAttrNumberArray(itemtype, l_item_key,n_varname,n_varval);
572
573 wf_engine.SetItemAttrDocument( itemtype => itemtype,
574 itemkey => l_item_key,
575 aname => 'ATTACHMENT',
576 documentid => ( wf_engine.GetItemAttrDocument( itemtype => itemtype,
577 itemkey => itemkey,
578 aname => 'ATTACHMENT')));
579
580
581 l_progress := 'Launch_Parallel_Approval: 005 -- Launch Parallel Approval';
582 IF (g_po_wf_debug = 'Y') THEN
583 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
584 END IF;
585
586
587 wf_engine.StartProcess( itemtype => itemtype,
588 itemkey => l_item_key );
589
590 l_approver_index := g_next_approvers.next(l_approver_index);
591 end loop; -- end of for loop.
592
593 if l_start_block_activity = 'Y' then
594 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
595 else
596 resultout:='COMPLETE' || ':' || '';
597 end if;
598 g_next_approvers.delete;
599
600 RETURN;
601
602 END IF; --run mode
603
604 exception
605 when NO_DATA_FOUND then
606 l_progress:= '50: start_wf_line_process: NO_DATA_FOUND -- EXCEPTION';
607 l_doc_string := l_progress || PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
608 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
609 wf_core.context('POR_AME_REQ_WF_PVT','Launch_Parallel_Approval-NO_DATA_FOUND Exception:',l_progress,sqlerrm);
610
611 IF (g_po_wf_debug = 'Y') THEN
612 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );
613 END IF;
614 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.GET_NEXT_APPROVER');
615 raise;
616 when others then
617 l_progress:= '50: start_wf_line_process: IN EXCEPTION';
618 l_doc_string := l_progress || PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
619 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
620 wf_core.context('POR_AME_REQ_WF_PVT','Launch_Parallel_Approval-Unexpected Exception:',l_progress,sqlerrm);
621
622 IF (g_po_wf_debug = 'Y') THEN
623 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );
624 END IF;
625
626 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.GET_NEXT_APPROVER');
627 raise;
628
629 end Launch_Parallel_Approval;
630
631
632 --------------------------------------------------------------------------------
633 --Start of Comments
634 --Name: Process_Response_Internal
635 --Pre-reqs:
636 -- None.
637 --Modifies:
638 -- None.
639 --Locks:
640 -- None.
641 --Function:
642 -- Workflow activity PL/SQL handler. This procedure is used to inform AME about the approvers response.
643 --Parameters:
644 --IN:
645 -- Standard workflow IN parameters
646 --OUT:
647 -- Standard workflow OUT parameters
648 --Testing:
649 --
650 --End of Comments
651 -------------------------------------------------------------------------------
652 procedure Process_Response_Internal( itemtype in varchar2,
653 itemkey in varchar2,
654 p_response in varchar2 ) IS
655
656 l_progress VARCHAR2(500) := '000';
657 l_document_id number;
658 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
659 l_current_approver ame_util.approverRecord2;
660 l_approver_posoition_id number;
661 l_approver_type varchar2(10);
662 l_error_code NUMBER;
663 l_error_message ame_util.longestStringType;
664 l_parent_item_type wf_items.parent_item_type%TYPE;
665 l_parent_item_key wf_items.parent_item_key%TYPE;
666 begin
667
668 l_progress := 'Process_Response_Internal: 001';
669 IF (g_po_wf_debug = 'Y') THEN
670 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
671 END IF;
672
673 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
674 itemkey => itemkey,
675 aname => 'DOCUMENT_ID');
676
677 l_transaction_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
678 itemkey => itemkey,
679 aname => 'AME_TRANSACTION_TYPE');
680
681 l_approver_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
682 itemkey => itemkey,
683 aname => 'AME_APPROVER_TYPE');
684
685 l_progress := 'Process_Response_Internal: 002 -- l_approver_type :' || l_approver_type ;
686 IF (g_po_wf_debug = 'Y') THEN
687 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
688 END IF;
689
690 if (l_approver_type = ame_util.posOrigSystem) then
691 l_current_approver.orig_system := ame_util.posOrigSystem;
692 elsif (l_approver_type = ame_util.fndUserOrigSystem) then
693 l_current_approver.orig_system := ame_util.fndUserOrigSystem;
694 else
695 l_current_approver.orig_system := ame_util.perOrigSystem;
696 l_current_approver.name := po_wf_util_pkg.GetItemAttrText( itemtype => itemType,
697 itemkey => itemkey,
698 aname => 'APPROVER_USER_NAME');
699 end if;
700
701 l_current_approver.orig_system_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
702 itemkey => itemkey,
703 aname => 'AME_APPROVER_ID');
704
705 l_progress := 'Process_Response_Internal: 003 -- l_current_approver.orig_system_id :' || l_current_approver.orig_system_id ;
706 IF (g_po_wf_debug = 'Y') THEN
707 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
708 END IF;
709
710 if( p_response = 'APPROVE') then
711 l_current_approver.approval_status := ame_util.approvedStatus;
712 elsif( p_response = 'REJECT') then
713 l_current_approver.approval_status := ame_util.rejectStatus;
714 elsif( p_response = 'TIMEOUT') then
715 l_current_approver.approval_status := ame_util.noResponseStatus;
716 end if;
717
718 l_progress := 'Process_Response_Internal: 004 -- p_response :' || p_response ;
719 IF (g_po_wf_debug = 'Y') THEN
720 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
721 END IF;
722
723 -- Get the name value for the approverRecord2.
724 -- This is a mandatory field. If we do not pass this value to AME, we will get invalid parameter exception.
725 -- bug# 4936145
726 IF l_current_approver.name IS NULL THEN
727 SELECT name into l_current_approver.name FROM
728 ( SELECT name FROM wf_roles WHERE orig_system = l_current_approver.orig_system
729 and orig_system_id = l_current_approver.orig_system_id
730 order by start_date
731 )
732 WHERE rownum = 1;
733 END IF;
734
735 IF l_current_approver.name IS NULL THEN
736 raise_application_error(-20001, 'Record Not Found in WF_ROLES for the orig_system_id :' ||
737 l_current_approver.orig_system_id || ' -- orig_system :' || l_current_approver.orig_system );
738 END IF;
739
740 -- Update the Approval status with the response from the approver.
741 ame_api2.updateApprovalStatus( applicationIdIn=>applicationId,
742 transactionIdIn=>l_document_id,
743 transactionTypeIn=>l_transaction_type,
744 approverIn => l_current_approver);
745
746 exception
747 when others then
748 l_error_code := SQLCODE;
749 /* Get the sql code of the exception. IF code is -20001 then this is a valid AME exception .We need to
750 get the exception text and store it in an attribute */
751 IF l_error_code = -20001 THEN
752 l_error_message := SQLERRM;
753
754 SELECT parent_item_type, parent_item_key
755 into l_parent_item_type, l_parent_item_key
756 FROM wf_items
757 WHERE item_type = itemtype and item_key = itemkey;
758
759 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
760 itemkey => l_parent_item_key,
761 aname => 'AME_EXCEPTION',
762 avalue => l_error_message );
763
764 ELSE -- If not valid excpetion , just raise the error
765 raise;
766 END IF;
767 end Process_Response_Internal;
768
769
770 --------------------------------------------------------------------------------
771 --Name: Process_Beat_By_First
772 --Pre-reqs:
773 -- None.
774 --Modifies:
775 -- None.
776 --Locks:
777 -- None.
778 --Function:
779 -- Workflow activity PL/SQL handler.
780 -- This procedure handles the stopping of workflow and the updating of the
781 -- action history table in the case of approvers being beat by first
782 -- responder.
783 --Parameters:
784 --IN:
785 -- Standard workflow IN parameters
786 --OUT:
787 -- Standard workflow OUT parameters
788 --Testing:
789 --
790 --End of Comments
791 -------------------------------------------------------------------------------
792 procedure Process_Beat_By_First( itemtype in varchar2,
793 itemkey in varchar2,
794 actid in number,
795 funcmode in varchar2,
796 resultout out NOCOPY varchar2) IS
797
798 l_progress VARCHAR2(500) := '000';
799 l_parent_item_type wf_items.parent_item_type%TYPE;
800 l_parent_item_key wf_items.parent_item_key%TYPE;
801
802 l_child_approver_empid NUMBER;
803 l_child_approver_groupid NUMBER;
804
805 l_approver_group_id NUMBER;
806 l_req_header_id NUMBER;
807 l_process_out VARCHAR2(10);
808 approverList ame_util.approversTable2;
809 ameTransactionType po_document_types.ame_transaction_type%TYPE;
810 l_response_action VARCHAR2(20);
811 l_note VARCHAR2(4000);
812 l_person_id NUMBER;
813 l_orig_system VARCHAR2(3);
814 l_orig_system_id NUMBER;
815 l_first_approver_id NUMBER;
816 l_first_position_id NUMBER;
817
818 l_preparer_user_name fnd_user.user_name%TYPE;
819 l_doc_string VARCHAR2(200);
820 l_ame_exception ame_util.longestStringType;
821 l_approver_response varchar2(20);
822 CURSOR l_child_wf (itemtype IN wf_items.parent_item_type%TYPE,itemkey IN wf_items.parent_item_key%TYPE) IS
823 SELECT wfi.item_type, wfi.item_key
824 FROM wf_items wfi,wf_item_activity_statuses wfias
825 WHERE wfi.parent_item_key =itemkey
826 and wfi.item_type=itemtype
827 AND wfias.item_type=wfi.item_type
828 AND wfias.item_key=wfi.item_key
829 AND wfias.activity_status='NOTIFIED'
830 AND wfias.notification_id IS NOT null;
831 l_child_wf_cur l_child_wf%ROWTYPE;
832
833 begin
834
835 l_progress := 'Process_Beat_By_First: 001';
836 IF (g_po_wf_debug = 'Y') THEN
837 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
838 END IF;
839
840 SELECT parent_item_type, parent_item_key
841 into l_parent_item_type, l_parent_item_key
842 FROM wf_items
843 WHERE item_type = itemtype and item_key = itemkey;
844
845 /* Check if there we have encountered any ame exception.
846 If the value of ame_exception is not null, then we have faced some exception.
847 So just comlete the block activity and return */
848 l_ame_exception := po_wf_util_pkg.GetItemAttrText( itemtype => l_parent_item_type,
849 itemkey => l_parent_item_key,
850 aname => 'AME_EXCEPTION' );
851
852 IF l_ame_exception IS NOT NULL THEN
853 wf_engine.CompleteActivity( itemtype => l_parent_item_type,
854 itemkey => l_parent_item_key,
855 activity => 'BLOCK',
856 result => null);
857 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
858 RETURN;
859 END IF;
860
861 l_progress := 'Process_Beat_By_First: 002';
862 IF (g_po_wf_debug = 'Y') THEN
863 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
864 END IF;
865
866 l_approver_group_id := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
867 itemkey => itemkey,
868 aname => 'APPROVAL_GROUP_ID');
869
870 l_req_header_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
871 itemkey => itemkey,
872 aname => 'DOCUMENT_ID');
873
874 l_approver_response := po_wf_util_pkg.GetItemAttrText(itemtype => itemtype,
875 itemkey => itemkey,
876 aname => 'APPROVER_RESPONSE');
877
878 select first_position_id, first_approver_id
879 into l_first_position_id, l_first_approver_id
880 from po_requisition_headers_all
881 where l_req_header_id = requisition_header_id;
882
883 l_progress := 'Process_Beat_By_First: 003';
884 IF (g_po_wf_debug = 'Y') THEN
885 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
886 END IF;
887
888 if l_approver_response = 'APPROVED' then
889 por_ame_approval_list.getAmeTransactionType(pReqHeaderId => l_req_header_id,
890 pAmeTransactionType => ameTransactionType);
891
892 l_progress := 'Process_Beat_By_First: 004';
893 IF (g_po_wf_debug = 'Y') THEN
894 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
895 END IF;
896
897 -- Note for Approve
898 l_note := fnd_message.get_string('ICX', 'ICX_POR_REQ_ALREADY_APPROVED');
899
900 ame_api2.getAllApprovers7( applicationIdIn => applicationId,
901 transactionIdIn => l_req_header_id,
902 transactionTypeIn => ameTransactionType,
903 approvalProcessCompleteYNOut => l_process_out,
904 approversOut => approverList
905 );
906
907 l_progress := 'Process_Beat_By_First: 005';
908 IF (g_po_wf_debug = 'Y') THEN
909 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
910 END IF;
911
912 -- Once we get the approvers list from AME, we iterate through the approvers list,
913 -- to find out the current first authority approver.
914 for i in 1 .. approverList.count loop
915
916 IF (g_po_wf_debug = 'Y') THEN
917 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
918
919 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, approverList(i).orig_system || to_char(i) || ' ' ||
920 approverList(i).orig_system_id);
921 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'authority' || ' ' || approverList(i).authority);
922 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'approval_status'|| ' ' || approverList(i).approval_status);
923 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'api_insertion'|| ' ' || approverList(i).api_insertion);
924 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'group_or_chain_id' || ' ' || approverList(i).group_or_chain_id);
925
926 END IF;
927
928 if( approverList(i).approval_status = ame_util.beatByFirstResponderStatus
929 and approverList(i).api_insertion = ame_util.oamGenerated
930 and approverList(i).group_or_chain_id = l_approver_group_id) then
931
932 l_orig_system := approverList(i).orig_system;
933 l_orig_system_id := approverList(i).orig_system_id;
934
935 if ( l_orig_system = ame_util.perOrigSystem) then
936
937 -- Employee Supervisor Record.
938 l_person_id := l_orig_system_id;
939
940 elsif ( l_orig_system = ame_util.posOrigSystem) then
941
942 -- Position Hierarchy Record.
943 begin
944
945 if (l_first_position_id is not NULL AND l_first_position_id = l_orig_system_id) then
946 l_person_id := l_first_approver_id;
947
948 else
949 SELECT person_id into l_person_id FROM (
950 SELECT person.person_id FROM per_all_people_f person, per_all_assignments_f asg
951 WHERE asg.position_id = l_orig_system_id and trunc(sysdate) between person.effective_start_date
952 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
953 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
954 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
955 and asg.assignment_status_type_id not in (
956 SELECT assignment_status_type_id FROM per_assignment_status_types
957 WHERE per_system_status = 'TERM_ASSIGN'
958 ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
959 ) where rownum = 1;
960
961 end if;
962
963 exception
964 WHEN NO_DATA_FOUND THEN
965 l_person_id := -1;
966 end;
967
968 elsif (l_orig_system = ame_util.fndUserOrigSystem) then
969
970 -- FND User Record.
971 SELECT employee_id into l_person_id
972 FROM fnd_user
973 WHERE user_id = l_orig_system_id
974 and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
975
976 end if;
977
978 -- stop the workflow
979 OPEN l_child_wf(l_parent_item_type, l_parent_item_key);
980
981 LOOP
982 FETCH l_child_wf INTO l_child_wf_cur;
983 EXIT WHEN l_child_wf%NOTFOUND;
984
985 l_child_approver_empid := po_wf_util_pkg.GetItemAttrNumber( itemtype => l_child_wf_cur.item_type,
986 itemkey => l_child_wf_cur.item_key,
987 aname => 'APPROVER_EMPID');
988
989 l_child_approver_groupid := po_wf_util_pkg.GetItemAttrNumber( itemtype => l_child_wf_cur.item_type,
990 itemkey => l_child_wf_cur.item_key,
991 aname => 'APPROVAL_GROUP_ID');
992
993
994 IF (l_child_approver_empid = l_person_id and
995 l_child_approver_groupid = l_approver_group_id) THEN
996
997 wf_engine.AbortProcess(l_child_wf_cur.item_type ,l_child_wf_cur.item_key);
998 EXIT;
999
1000 END IF;
1001
1002 END LOOP;
1003
1004 CLOSE l_child_wf;
1005
1006 -- update the action history table
1007 UpdateActionHistory(l_req_header_id, 'NO ACTION',
1008 l_note, l_person_id);
1009
1010 end if;
1011 end loop;
1012 elsif (l_approver_response = 'REJECTED') then
1013 OPEN l_child_wf(l_parent_item_type, l_parent_item_key);
1014
1015 LOOP
1016 FETCH l_child_wf INTO l_child_wf_cur;
1017 EXIT WHEN l_child_wf%NOTFOUND;
1018
1019 -- Get the approver id as the person id to update the action history
1020 l_person_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => l_child_wf_cur.item_type,
1021 itemkey => l_child_wf_cur.item_key,
1022 aname => 'APPROVER_EMPID');
1023
1024 l_progress := 'Process_Beat_By_First: 006';
1025 IF (g_po_wf_debug = 'Y') THEN
1026 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1027 END IF;
1028
1029 -- Note for rejection
1030 l_note := fnd_message.get_string('ICX', 'ICX_POR_REQ_ALREADY_REJECTED');
1031
1032 wf_engine.AbortProcess(l_child_wf_cur.item_type ,l_child_wf_cur.item_key);
1033
1034 -- update the action history table
1035 UpdateActionHistory(l_req_header_id, 'NO ACTION',
1036 l_note, l_person_id);
1037
1038 l_progress := 'Process_Beat_By_First: 007';
1039 IF (g_po_wf_debug = 'Y') THEN
1040 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1041 END IF;
1042
1043 END LOOP;
1044
1045 CLOSE l_child_wf;
1046
1047 end if;
1048
1049 l_progress := 'Process_Beat_By_First: 008';
1050 IF (g_po_wf_debug = 'Y') THEN
1051 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1052 END IF;
1053
1054 wf_engine.CompleteActivity( itemtype => l_parent_item_type,
1055 itemkey => l_parent_item_key,
1056 activity => 'BLOCK',
1057 result => null);
1058
1059 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1060 RETURN;
1061
1062 exception
1063 when others then
1064 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1065 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1066 wf_core.context('POR_AME_REQ_WF_PVT','Process_Beat_By_First',l_progress,sqlerrm);
1067 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.Process_Beat_By_First');
1068 RAISE;
1069
1070 end Process_Beat_By_First;
1071
1072
1073 --------------------------------------------------------------------------------
1074 --Start of Comments
1075 --Name: Process_Response_Approve
1076 --Pre-reqs:
1077 -- None.
1078 --Modifies:
1079 -- None.
1080 --Locks:
1081 -- None.
1082 --Function:
1083 -- Workflow activity PL/SQL handler.
1084 -- This procedure is the wrapper procedure of Process_Response_Internal()
1085 --Parameters:
1086 --IN:
1087 -- Standard workflow IN parameters
1088 --OUT:
1089 -- Standard workflow OUT parameters
1090 --Testing:
1091 --
1092 --End of Comments
1093 -------------------------------------------------------------------------------
1094 procedure Process_Response_Approve( itemtype in varchar2,
1095 itemkey in varchar2,
1096 actid in number,
1097 funcmode in varchar2,
1098 resultout out NOCOPY varchar2) IS
1099
1100 l_progress VARCHAR2(500) := '000';
1101 l_parent_item_type wf_items.parent_item_type%TYPE;
1102 l_parent_item_key wf_items.parent_item_key%TYPE;
1103
1104 l_child_approver_empid number;
1105 l_child_approver_user_name wf_users.name%TYPE;
1106 l_child_approver_display_name wf_users.display_name%TYPE;
1107
1108 begin
1109
1110 l_progress := 'Process_Response_Approve: 001';
1111 IF (g_po_wf_debug = 'Y') THEN
1112 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1113 END IF;
1114
1115 SELECT parent_item_type, parent_item_key
1116 into l_parent_item_type, l_parent_item_key
1117 FROM wf_items
1118 WHERE item_type = itemtype and item_key = itemkey;
1119
1120 Process_Response_Internal(itemtype, itemkey, 'APPROVE');
1121 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1122 itemkey => l_parent_item_key,
1123 aname => 'AME_SUB_APPROVAL_RESPONSE',
1124 avalue => 'APPROVE');
1125
1126
1127 l_child_approver_empid := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1128 itemkey => itemkey,
1129 aname => 'APPROVER_EMPID');
1130
1131 l_child_approver_user_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1132 itemkey => itemkey,
1133 aname => 'APPROVER_USER_NAME');
1134
1135 l_child_approver_display_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1136 itemkey => itemkey,
1137 aname => 'APPROVER_DISPLAY_NAME');
1138
1139
1140 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1141 itemkey => l_parent_item_key,
1142 aname => 'FORWARD_FROM_ID',
1143 avalue => l_child_approver_empid );
1144
1145 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1146 itemkey => l_parent_item_key,
1147 aname => 'FORWARD_FROM_USER_NAME',
1148 avalue => l_child_approver_user_name);
1149
1150 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1151 itemkey => l_parent_item_key,
1152 aname => 'FORWARD_FROM_DISP_NAME',
1153 avalue => l_child_approver_display_name);
1154
1155 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1156 itemkey => l_parent_item_key,
1157 aname => 'APPROVER_EMPID',
1158 avalue => l_child_approver_empid );
1159
1160 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1161 itemkey => l_parent_item_key,
1162 aname => 'APPROVER_USER_NAME',
1163 avalue => l_child_approver_user_name );
1164
1165
1166 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1167 itemkey => l_parent_item_key,
1168 aname => 'APPROVER_DISPLAY_NAME',
1169 avalue => l_child_approver_display_name );
1170
1171 l_progress := 'Process_Response_Approve: 002 -- Completing the BLOCK activity for the APPROVED notification.';
1172 IF (g_po_wf_debug = 'Y') THEN
1173 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1174 END IF;
1175
1176 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1177 RETURN;
1178 end Process_Response_Approve;
1179
1180 --------------------------------------------------------------------------------
1181 --Start of Comments
1182 --Name: Process_Response_Reject
1183 --Pre-reqs:
1184 -- None.
1185 --Modifies:
1186 -- None.
1187 --Locks:
1188 -- None.
1189 --Function:
1190 -- Workflow activity PL/SQL handler.
1191 -- This procedure is the wrapper procedure of Process_Response_Internal()
1192 --Parameters:
1193 --IN:
1194 -- Standard workflow IN parameters
1195 --OUT:
1196 -- Standard workflow OUT parameters
1197 --Testing:
1198 --
1199 --End of Comments
1200 -------------------------------------------------------------------------------
1201 procedure Process_Response_Reject( itemtype in varchar2,
1202 itemkey in varchar2,
1203 actid in number,
1204 funcmode in varchar2,
1205 resultout out NOCOPY varchar2) IS
1206
1207 l_progress VARCHAR2(500) := '000';
1208 l_parent_item_type wf_items.parent_item_type%TYPE;
1209 l_parent_item_key wf_items.parent_item_key%TYPE;
1210
1211 l_child_approver_empid number;
1212 l_child_approver_user_name wf_users.name%TYPE;
1213 l_child_approver_display_name wf_users.display_name%TYPE;
1214
1215 begin
1216
1217 l_progress := 'Process_Response_Reject: 001';
1218 IF (g_po_wf_debug = 'Y') THEN
1219 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1220 END IF;
1221
1222 Process_Response_Internal(itemtype, itemkey, 'REJECT');
1223
1224 SELECT parent_item_type, parent_item_key
1225 into l_parent_item_type, l_parent_item_key
1226 FROM wf_items
1227 WHERE item_type = itemtype and item_key = itemkey;
1228
1229 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1230 itemkey => l_parent_item_key,
1231 aname => 'AME_SUB_APPROVAL_RESPONSE',
1232 avalue => 'REJECT');
1233
1234 l_child_approver_empid := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1235 itemkey => itemkey,
1236 aname => 'APPROVER_EMPID');
1237
1238 l_child_approver_user_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1239 itemkey => itemkey,
1240 aname => 'APPROVER_USER_NAME');
1241
1242 l_child_approver_display_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1243 itemkey => itemkey,
1244 aname => 'APPROVER_DISPLAY_NAME');
1245
1246
1247 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1248 itemkey => l_parent_item_key,
1249 aname => 'FORWARD_FROM_ID',
1250 avalue => l_child_approver_empid );
1251
1252
1253 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1254 itemkey => l_parent_item_key,
1255 aname => 'FORWARD_FROM_USER_NAME',
1256 avalue => l_child_approver_user_name);
1257
1258 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1259 itemkey => l_parent_item_key,
1260 aname => 'FORWARD_FROM_DISP_NAME',
1261 avalue => l_child_approver_display_name);
1262
1263
1264
1265 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1266 itemkey => l_parent_item_key,
1267 aname => 'APPROVER_EMPID',
1268 avalue => l_child_approver_empid );
1269
1270
1271 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1272 itemkey => l_parent_item_key,
1273 aname => 'APPROVER_USER_NAME',
1274 avalue => l_child_approver_user_name );
1275
1276
1277 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1278 itemkey => l_parent_item_key,
1279 aname => 'APPROVER_DISPLAY_NAME',
1280 avalue => l_child_approver_display_name );
1281
1282
1283 l_progress := 'Process_Response_Reject: 002 -- Completing the BLOCK activity for the REJECTED notification.';
1284 IF (g_po_wf_debug = 'Y') THEN
1285 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1286 END IF;
1287
1288 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1289 RETURN;
1290
1291 end Process_Response_Reject;
1292
1293
1294 --------------------------------------------------------------------------------
1295 --Start of Comments
1296 --Name: Process_Response_Timeout
1297 --Pre-reqs:
1298 -- None.
1299 --Modifies:
1300 -- None.
1301 --Locks:
1302 -- None.
1303 --Function:
1304 -- Workflow activity PL/SQL handler.
1305 -- This procedure is the wrapper procedure of Process_Response_Internal()
1306 --Parameters:
1307 --IN:
1308 -- Standard workflow IN parameters
1309 --OUT:
1310 -- Standard workflow OUT parameters
1311 --Testing:
1312 --
1313 --End of Comments
1314 -------------------------------------------------------------------------------
1315 procedure Process_Response_Timeout( itemtype in varchar2,
1316 itemkey in varchar2,
1317 actid in number,
1318 funcmode in varchar2,
1319 resultout out NOCOPY varchar2) IS
1320
1321 l_progress VARCHAR2(500) := '000';
1322 l_parent_item_type wf_items.parent_item_type%TYPE;
1323 l_parent_item_key wf_items.parent_item_key%TYPE;
1324
1325 l_child_approver_empid number;
1326 l_child_approver_user_name wf_users.name%TYPE;
1327 l_child_approver_display_name wf_users.display_name%TYPE;
1328
1329
1330 begin
1331
1332 l_progress := 'Process_Response_Timeout: 001';
1333 IF (g_po_wf_debug = 'Y') THEN
1334 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1335 END IF;
1336
1337 Process_Response_Internal(itemtype, itemkey, 'TIMEOUT');
1338
1339 SELECT parent_item_type, parent_item_key
1340 into l_parent_item_type, l_parent_item_key
1341 FROM wf_items
1342 WHERE item_type = itemtype and item_key = itemkey;
1343
1344 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1345 itemkey => l_parent_item_key,
1346 aname => 'AME_SUB_APPROVAL_RESPONSE',
1347 avalue => 'TIMEOUT');
1348
1349 l_child_approver_empid := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1350 itemkey => itemkey,
1351 aname => 'APPROVER_EMPID');
1352
1353 l_child_approver_user_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1354 itemkey => itemkey,
1355 aname => 'APPROVER_USER_NAME');
1356
1357 l_child_approver_display_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1358 itemkey => itemkey,
1359 aname => 'APPROVER_DISPLAY_NAME');
1360
1361
1362 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1363 itemkey => l_parent_item_key,
1364 aname => 'FORWARD_FROM_ID',
1365 avalue => l_child_approver_empid );
1366
1367
1368 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1369 itemkey => l_parent_item_key,
1370 aname => 'FORWARD_FROM_USER_NAME',
1371 avalue => l_child_approver_user_name);
1372
1373 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1374 itemkey => l_parent_item_key,
1375 aname => 'FORWARD_FROM_DISP_NAME',
1376 avalue => l_child_approver_display_name);
1377
1378
1379
1380 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1381 itemkey => l_parent_item_key,
1382 aname => 'APPROVER_EMPID',
1383 avalue => l_child_approver_empid );
1384
1385
1386 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1387 itemkey => l_parent_item_key,
1388 aname => 'APPROVER_USER_NAME',
1389 avalue => l_child_approver_user_name );
1390
1391
1392 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1393 itemkey => l_parent_item_key,
1394 aname => 'APPROVER_DISPLAY_NAME',
1395 avalue => l_child_approver_display_name );
1396
1397
1398 wf_engine.CompleteActivity (itemtype => l_parent_item_type,
1399 itemkey => l_parent_item_key,
1400 activity => 'BLOCK',
1401 result => null);
1402
1403 l_progress := 'Process_Response_Timeout: 002 -- Completing the BLOCK activity for the REJECTED notification.';
1404 IF (g_po_wf_debug = 'Y') THEN
1405 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1406 END IF;
1407
1408 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1409 RETURN;
1410
1411 end Process_Response_Timeout;
1412
1413 --------------------------------------------------------------------------------
1414 --Start of Comments
1415 --Name: insertActionHistory
1416 --Pre-reqs:
1417 -- None.
1418 --Modifies:
1419 -- None.
1420 --Locks:
1421 -- None.
1422 --Function:
1423 -- Workflow activity PL/SQL handler.
1424 -- This procedure is used to keep the history of each notification.
1425 -- The inserted records will be displayed in Approval History page.
1426 --Parameters:
1427 --IN:
1428 -- Requistion Header Id
1429 -- Employee Id
1430 -- Approver Group Id
1431 --OUT:
1432 -- Standard workflow OUT parameters
1433 --Testing:
1434 --
1435 --End of Comments
1436 -------------------------------------------------------------------------------
1437 procedure insertActionHistory( p_req_header_id in number,
1438 p_employee_id in number,
1439 p_approval_group_id in number)
1440
1441 is
1442 pragma AUTONOMOUS_TRANSACTION;
1443
1444 l_object_sub_type_code PO_ACTION_HISTORY.OBJECT_SUB_TYPE_CODE%TYPE;
1445 l_sequence_num PO_ACTION_HISTORY.SEQUENCE_NUM%TYPE;
1446 l_object_revision_num PO_ACTION_HISTORY.OBJECT_REVISION_NUM%TYPE;
1447 l_approval_path_id PO_ACTION_HISTORY.APPROVAL_PATH_ID%TYPE;
1448 l_request_id PO_ACTION_HISTORY.REQUEST_ID%TYPE;
1449 l_program_application_id PO_ACTION_HISTORY.PROGRAM_APPLICATION_ID%TYPE;
1450 l_program_date PO_ACTION_HISTORY.PROGRAM_DATE%TYPE;
1451 l_program_id PO_ACTION_HISTORY.PROGRAM_ID%TYPE;
1452 l_progress VARCHAR2(100) := '000';
1453
1454 begin
1455
1456 SELECT max(sequence_num)
1457 INTO l_sequence_num
1458 FROM PO_ACTION_HISTORY
1459 WHERE object_type_code = 'REQUISITION'
1460 AND object_id = p_req_header_id;
1461
1462 SELECT object_sub_type_code,
1463 object_revision_num, approval_path_id, request_id,
1464 program_application_id, program_date, program_id
1465 INTO l_object_sub_type_code,
1466 l_object_revision_num, l_approval_path_id, l_request_id,
1467 l_program_application_id, l_program_date, l_program_id
1468 FROM PO_ACTION_HISTORY
1469 WHERE object_type_code = 'REQUISITION'
1470 AND object_id = p_req_header_id
1471 AND sequence_num = l_sequence_num;
1472
1473 /* update po action history */
1474 po_forward_sv1.insert_action_history (
1475 p_req_header_id,
1476 'REQUISITION',
1477 l_object_sub_type_code,
1478 l_sequence_num + 1,
1479 NULL,
1480 NULL,
1481 p_employee_id,
1482 NULL,
1483 NULL,
1484 l_object_revision_num,
1485 NULL, /* offline_code */
1486 l_request_id,
1487 l_program_application_id,
1488 l_program_id,
1489 l_program_date,
1490 fnd_global.user_id,
1491 fnd_global.login_id,
1492 p_approval_group_id);
1493
1494 commit;
1495
1496 end insertActionHistory;
1497
1498
1499 --------------------------------------------------------------------------------
1500 --Start of Comments
1501 --Name: Insert_Action_History
1502 --Pre-reqs:
1503 -- None.
1504 --Modifies:
1505 -- None.
1506 --Locks:
1507 -- None.
1508 --Function:
1509 -- Workflow activity PL/SQL handler.
1510 -- This procedure is the wrapper procedure of insertActionHistory()
1511 --Parameters:
1512 --IN:
1513 -- Standard workflow IN parameters
1514 --OUT:
1515 -- Standard workflow OUT parameters
1516 --Testing:
1517 --
1518 --End of Comments
1519 -------------------------------------------------------------------------------
1520 procedure Insert_Action_History( itemtype in varchar2,
1521 itemkey in varchar2,
1522 actid in number,
1523 funcmode in varchar2,
1524 resultout out NOCOPY varchar2) IS
1525
1526 l_progress VARCHAR2(500) := '000';
1527 l_action VARCHAR2(30) := 'APPROVE';
1528 l_next_approver_id NUMBER:='';
1529 l_req_header_id NUMBER:='';
1530 l_approval_group_id NUMBER:='';
1531
1532 l_doc_string varchar2(200);
1533 l_preparer_user_name varchar2(100);
1534
1535 l_org_id number;
1536
1537 BEGIN
1538
1539 l_progress := 'Insert_Action_History: 001';
1540 IF (g_po_wf_debug = 'Y') THEN
1541 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1542 END IF;
1543
1544 IF (funcmode='RUN') THEN
1545
1546
1547 l_next_approver_id := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
1548 itemkey=>itemkey,
1549 aname=>'APPROVER_EMPID');
1550
1551
1552 l_req_header_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1553 itemkey => itemkey,
1554 aname => 'DOCUMENT_ID');
1555
1556 l_approval_group_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1557 itemkey => itemkey,
1558 aname => 'APPROVAL_GROUP_ID');
1559
1560 -- Set the multi-org context
1561 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1562 itemkey => itemkey,
1563 aname => 'ORG_ID');
1564
1565 IF l_org_id is NOT NULL THEN
1566 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1567 END IF;
1568
1569 l_progress := 'Insert_Action_History: 004 - Calling insertActionHistory.';
1570 insertActionHistory(l_req_header_id, l_next_approver_id, l_approval_group_id);
1571
1572 l_progress := 'Insert_Action_History: 005 - Done with insertActionHistory.';
1573 IF (g_po_wf_debug = 'Y') THEN
1574 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1575 END IF;
1576
1577 /* Reset the FORWARD_TO_USERNAME_RESPONSE attribute */
1578 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
1579 itemkey => itemkey,
1580 aname => 'FORWARD_TO_USERNAME_RESPONSE',
1581 avalue => NULL);
1582
1583 /* Reset the NOTE attribute */
1584 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
1585 itemkey => itemkey,
1586 aname => 'NOTE',
1587 avalue => NULL);
1588
1589 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1590 return;
1591
1592 END IF; -- run mode
1593
1594 l_progress := 'Insert_Action_History: 999';
1595 IF (g_po_wf_debug = 'Y') THEN
1596 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1597 END IF;
1598
1599 EXCEPTION
1600 WHEN OTHERS THEN
1601 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1602 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1603 wf_core.context('POR_AME_REQ_WF_PVT','Insert_Action_History',l_progress,sqlerrm);
1604 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.INSERT_ACTION_HISTORY');
1605 RAISE;
1606 END Insert_Action_History;
1607
1608
1609 --------------------------------------------------------------------------------
1610 --Start of Comments
1611 --Name: Update_Action_History_Approve
1612 --Pre-reqs:
1613 -- None.
1614 --Modifies:
1615 -- None.
1616 --Locks:
1617 -- None.
1618 --Function:
1619 -- Workflow activity PL/SQL handler.
1620 -- This procedure updates the po_action_history table based on the approvers response.
1621 --Parameters:
1622 --IN:
1623 -- Standard workflow IN parameters
1624 --OUT:
1625 -- Standard workflow OUT parameters
1626 --Testing:
1627 --
1628 --End of Comments
1629 -------------------------------------------------------------------------------
1630 procedure Update_Action_History_Approve( itemtype in varchar2,
1631 itemkey in varchar2,
1632 actid in number,
1633 funcmode in varchar2,
1634 resultout out NOCOPY varchar2) IS
1635 l_progress VARCHAR2(500) := '000';
1636 l_action VARCHAR2(30) := 'APPROVE';
1637 l_forward_to_id NUMBER:='';
1638 l_document_id NUMBER;
1639 l_document_type VARCHAR2(25):='';
1640 l_document_subtype VARCHAR2(25):='';
1641 l_return_code NUMBER;
1642 l_result BOOLEAN:=FALSE;
1643 l_note VARCHAR2(4000);
1644
1645 l_doc_string varchar2(200);
1646 l_preparer_user_name varchar2(100);
1647
1648 l_org_id number;
1649 l_current_approver number;
1650
1651 BEGIN
1652
1653 l_progress := 'Update_Action_History_Approve: 001';
1654 IF (g_po_wf_debug = 'Y') THEN
1655 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1656 END IF;
1657
1658 IF (funcmode='RUN') THEN
1659
1660 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
1661 itemkey=>itemkey,
1662 aname=>'APPROVER_EMPID');
1663
1664 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1665 itemkey => itemkey,
1666 aname => 'DOCUMENT_ID');
1667
1668 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1669 itemkey => itemkey,
1670 aname => 'DOCUMENT_TYPE');
1671
1672 l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1673 itemkey => itemkey,
1674 aname => 'DOCUMENT_SUBTYPE');
1675
1676 l_note := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1677 itemkey => itemkey,
1678 aname => 'NOTE');
1679
1680 -- Set the multi-org context
1681 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1682 itemkey => itemkey,
1683 aname => 'ORG_ID');
1684
1685 IF l_org_id is NOT NULL THEN
1686 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1687 END IF;
1688
1689 l_progress := 'Update_Action_History_Approve: 002-'|| to_char(l_document_id)||'-'||
1690 l_document_type||'-'||l_document_subtype;
1691 IF (g_po_wf_debug = 'Y') THEN
1692 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1693 END IF;
1694
1695 UpdateActionHistory(l_document_id, l_action,
1696 l_note, l_current_approver);
1697
1698 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
1699 itemkey => itemkey,
1700 aname => 'APPROVER_RESPONSE',
1701 avalue => 'APPROVED' );
1702
1703 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1704
1705 END IF; -- run mode
1706
1707 l_progress := 'Update_Action_History_Approve: 003';
1708 IF (g_po_wf_debug = 'Y') THEN
1709 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1710 END IF;
1711
1712
1713 EXCEPTION
1714 WHEN OTHERS THEN
1715 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1716 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1717 wf_core.context('POR_AME_REQ_WF_PVT','Update_Action_History_Approve',l_progress,sqlerrm);
1718 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.UPDATE_ACTION_HISTORY_APPROVE');
1719 RAISE;
1720
1721 END Update_Action_History_Approve;
1722
1723
1724 --------------------------------------------------------------------------------
1725 --Start of Comments
1726 --Name: Update_Action_History_Reject
1727 --Pre-reqs:
1728 -- None.
1729 --Modifies:
1730 -- None.
1731 --Locks:
1732 -- None.
1733 --Function:
1734 -- Workflow activity PL/SQL handler.
1735 -- This procedure updates the po_action_history table based on the approvers response.
1736 --Parameters:
1737 --IN:
1738 -- Standard workflow IN parameters
1739 --OUT:
1740 -- Standard workflow OUT parameters
1741 --Testing:
1742 --
1743 --End of Comments
1744 -------------------------------------------------------------------------------
1745 procedure Update_Action_History_Reject(itemtype in varchar2,
1746 itemkey in varchar2,
1747 actid in number,
1748 funcmode in varchar2,
1749 resultout out NOCOPY varchar2) IS
1750
1751 l_progress VARCHAR2(100) := '000';
1752 l_action VARCHAR2(30) := 'REJECT';
1753 l_forward_to_id NUMBER:='';
1754 l_document_id NUMBER;
1755 l_document_type VARCHAR2(25):='';
1756 l_document_subtype VARCHAR2(25):='';
1757 l_return_code NUMBER;
1758 l_result BOOLEAN:=FALSE;
1759 l_note VARCHAR2(4000);
1760
1761 l_doc_string varchar2(200);
1762 l_preparer_user_name varchar2(100);
1763
1764 l_org_id number;
1765 l_current_approver number;
1766
1767 BEGIN
1768
1769 l_progress := 'Update_Action_History_Reject: 001';
1770 IF (g_po_wf_debug = 'Y') THEN
1771 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1772 END IF;
1773
1774 IF (funcmode='RUN') THEN
1775
1776 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
1777 itemkey=>itemkey,
1778 aname=>'APPROVER_EMPID');
1779
1780 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1781 itemkey => itemkey,
1782 aname => 'DOCUMENT_ID');
1783
1784 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1785 itemkey => itemkey,
1786 aname => 'DOCUMENT_TYPE');
1787
1788 l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1789 itemkey => itemkey,
1790 aname => 'DOCUMENT_SUBTYPE');
1791
1792 l_note := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1793 itemkey => itemkey,
1794 aname => 'NOTE');
1795
1796 -- Set the multi-org context
1797 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1798 itemkey => itemkey,
1799 aname => 'ORG_ID');
1800
1801 IF l_org_id is NOT NULL THEN
1802 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1803 END IF;
1804
1805 l_progress := 'Update_Action_History_Reject: 002-'|| to_char(l_document_id)||'-'||
1806 l_document_type||'-'||l_document_subtype;
1807 IF (g_po_wf_debug = 'Y') THEN
1808 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1809 END IF;
1810
1811 UpdateActionHistory(l_document_id, l_action,
1812 l_note, l_current_approver);
1813
1814 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
1815 itemkey => itemkey,
1816 aname => 'APPROVER_RESPONSE',
1817 avalue => 'REJECTED' );
1818
1819 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1820
1821 END IF; -- run mode
1822
1823 l_progress := 'Update_Action_History_Reject: 003';
1824 IF (g_po_wf_debug = 'Y') THEN
1825 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1826 END IF;
1827
1828 EXCEPTION
1829 WHEN OTHERS THEN
1830 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1831 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1832 wf_core.context('POR_AME_REQ_WF_PVT','Update_Action_History_Reject',l_progress,sqlerrm);
1833 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.Update_Action_History_Reject');
1834 RAISE;
1835
1836 END Update_Action_History_Reject;
1837
1838
1839 --------------------------------------------------------------------------------
1840 --Start of Comments
1841 --Name: Update_Action_History_Timeout
1842 --Pre-reqs:
1843 -- None.
1844 --Modifies:
1845 -- None.
1846 --Locks:
1847 -- None.
1848 --Function:
1849 -- Workflow activity PL/SQL handler.
1850 -- This procedure updates the po_action_history table based on the approvers response.
1851 --Parameters:
1852 --IN:
1853 -- Standard workflow IN parameters
1854 --OUT:
1855 -- Standard workflow OUT parameters
1856 --Testing:
1857 --
1858 --End of Comments
1859 -------------------------------------------------------------------------------
1860 procedure Update_Action_History_Timeout(itemtype in varchar2,
1861 itemkey in varchar2,
1862 actid in number,
1863 funcmode in varchar2,
1864 resultout out NOCOPY varchar2) IS
1865
1866 l_progress VARCHAR2(100) := '000';
1867 l_action VARCHAR2(30) := 'NO ACTION';
1868 l_forward_to_id NUMBER:='';
1869 l_document_id NUMBER;
1870 l_document_type VARCHAR2(25):='';
1871 l_document_subtype VARCHAR2(25):='';
1872 l_return_code NUMBER;
1873 l_result BOOLEAN:=FALSE;
1874 l_note VARCHAR2(4000);
1875
1876 l_doc_string varchar2(200);
1877 l_preparer_user_name varchar2(100);
1878
1879 l_org_id number;
1880 l_current_approver number;
1881
1882 BEGIN
1883
1884 l_progress := 'Update_Action_History_Timeout: 001';
1885 IF (g_po_wf_debug = 'Y') THEN
1886 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1887 END IF;
1888
1889 IF (funcmode='RUN') THEN
1890
1891 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
1892 itemkey=>itemkey,
1893 aname=>'APPROVER_EMPID');
1894
1895 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1896 itemkey => itemkey,
1897 aname => 'DOCUMENT_ID');
1898
1899 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1900 itemkey => itemkey,
1901 aname => 'DOCUMENT_TYPE');
1902
1903 l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1904 itemkey => itemkey,
1905 aname => 'DOCUMENT_SUBTYPE');
1906
1907 l_note := fnd_message.get_string('ICX', 'ICX_POR_NOTIF_TIMEOUT');
1908
1909 -- Set the multi-org context
1910 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1911 itemkey => itemkey,
1912 aname => 'ORG_ID');
1913
1914 IF l_org_id is NOT NULL THEN
1915 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1916 END IF;
1917
1918 l_progress := 'Update_Action_History_Timeout: 002-'|| to_char(l_document_id)||'-'||
1919 l_document_type||'-'||l_document_subtype;
1920
1921 IF (g_po_wf_debug = 'Y') THEN
1922 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1923 END IF;
1924
1925 UpdateActionHistory(l_document_id, l_action,
1926 l_note, l_current_approver);
1927
1928 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1929
1930 END IF; -- run mode
1931
1932 l_progress := 'Update_Action_History_Timeout: 003';
1933 IF (g_po_wf_debug = 'Y') THEN
1934 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1935 END IF;
1936
1937 EXCEPTION
1938 WHEN OTHERS THEN
1939 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1940 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1941 wf_core.context('POR_AME_REQ_WF_PVT','Update_Action_History_Timeout',l_progress,sqlerrm);
1942 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.Update_Action_History_Timeout');
1943 RAISE;
1944
1945 END Update_Action_History_Timeout;
1946
1947 --------------------------------------------------------------------------------
1948 --Start of Comments
1949 --Name: Update_Action_History_No_Action
1950 --Pre-reqs:
1951 -- None.
1952 --Modifies:
1953 -- None.
1954 --Locks:
1955 -- None.
1956 --Function:
1957 -- Workflow activity PL/SQL handler.
1958 -- If it is a first responder wins setup in ame, then once the first reponder wins, others will not be able to take decisions.
1959 --Parameters:
1960 --IN:
1961 -- Standard workflow IN parameters
1962 --OUT:
1963 -- Standard workflow OUT parameters
1964 --Testing:
1965 --
1966 --End of Comments
1967 -------------------------------------------------------------------------------
1968 procedure Update_Action_History_No_Act (itemtype in varchar2,
1969 itemkey in varchar2,
1970 actid in number,
1971 funcmode in varchar2,
1972 resultout out NOCOPY varchar2 ) is
1973 l_doc_header_id NUMBER;
1974 l_doc_type VARCHAR2(14);
1975 l_note VARCHAR2(4000);
1976 x_progress varchar2(500);
1977 l_response_action VARCHAR2(20);
1978
1979 l_doc_string varchar2(200);
1980 l_preparer_user_name varchar2(100);
1981
1982 BEGIN
1983
1984 x_progress := 'POR_AME_REQ_WF_PVT.Update_Action_History_No_Act: 01';
1985
1986 if (funcmode <> wf_engine.eng_run) then
1987 resultout := wf_engine.eng_null;
1988 return;
1989 end if;
1990
1991
1992 l_doc_header_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1993 itemkey => itemkey,
1994 aname => 'DOCUMENT_ID');
1995
1996 l_doc_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1997 itemkey => itemkey,
1998 aname => 'DOCUMENT_TYPE');
1999
2000 BEGIN
2001
2002 l_response_action := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2003 itemkey => itemkey,
2004 aname => 'AME_SUB_APPROVAL_RESPONSE');
2005 IF( l_response_action = 'APPROVE' ) THEN
2006 l_note := fnd_message.get_string('ICX', 'ICX_POR_REQ_ALREADY_APPROVED');
2007 ELSIF ( l_response_action = 'REJECT' ) THEN
2008 l_note := fnd_message.get_string('ICX', 'ICX_POR_REQ_ALREADY_REJECTED');
2009 ELSE
2010 l_note := NULL;
2011 END IF;
2012
2013 EXCEPTION
2014 WHEN OTHERS THEN
2015 l_note := NULL;
2016 END;
2017
2018
2019 x_progress := 'POR_AME_REQ_WF_PVT.Update_Action_History_No_Act: 02 - l_doc_header_id ' || l_doc_header_id || ' -- l_doc_type :' || l_doc_type ;
2020
2021 IF (g_po_wf_debug = 'Y') THEN
2022 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2023 END IF;
2024
2025 -- If the setup is first responder wins, then once the first approver responds, the others will not be able to take decisions.
2026
2027 IF ( l_response_action is not null) THEN
2028
2029 UpdateActionHistory(l_doc_header_id, 'NO ACTION', l_note, NULL);
2030
2031 END IF;
2032
2033 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
2034
2035 x_progress := 'POR_AME_REQ_WF_PVT.Update_Action_History_No_Act: 03';
2036 IF (g_po_wf_debug = 'Y') THEN
2037 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2038 END IF;
2039
2040 EXCEPTION
2041 WHEN OTHERS THEN
2042 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2043 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2044 wf_core.context('POR_AME_REQ_WF_PVT','Update_Action_History_No_Act',x_progress);
2045 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.Update_Action_History_No_Act');
2046 raise;
2047 END Update_Action_History_No_Act;
2048
2049 --------------------------------------------------------------------------------
2050 --Start of Comments
2051 --Name: UpdateActionHistory
2052 --Pre-reqs:
2053 -- None.
2054 --Modifies:
2055 -- None.
2056 --Locks:
2057 -- None.
2058 --Function:
2059 -- This procedure updates the po_action_history table based on the approvers response.
2060 --Parameters:
2061 --IN:
2062 -- p_document_id : Requisition Header Id
2063 -- p_action : Action
2064 -- p_note : Notes
2065 -- p_current_approver: Approver person Id
2066 --OUT:
2067 --
2068 --End of Comments
2069 -------------------------------------------------------------------------------
2070 PROCEDURE UpdateActionHistory(p_document_id NUMBER,
2071 p_action VARCHAR2,
2072 p_note VARCHAR2,
2073 p_current_approver NUMBER) IS
2074
2075 pragma AUTONOMOUS_TRANSACTION;
2076
2077 BEGIN
2078
2079 if (p_current_approver is not null) then
2080
2081 UPDATE po_action_history
2082 SET action_code = p_action,
2083 note = p_note,
2084 action_date = sysdate
2085 WHERE object_id = p_document_id and
2086 employee_id = p_current_approver and
2087 action_code is null and
2088 object_type_code = 'REQUISITION'
2089 and rownum=1;
2090
2091 else
2092
2093 UPDATE po_action_history
2094 SET action_code = p_action,
2095 note = p_note,
2096 action_date = sysdate
2097 WHERE object_id = p_document_id and
2098 action_code is null and
2099 object_type_code = 'REQUISITION'
2100 ;
2101 end if;
2102
2103 COMMIT;
2104
2105 EXCEPTION
2106
2107 WHEN OTHERS THEN
2108 RAISE;
2109
2110 END UpdateActionHistory;
2111
2112 --------------------------------------------------------------------------------
2113 --Start of Comments
2114 --Name: IS_AME_EXCEPTION
2115 --Pre-reqs:
2116 -- None.
2117 --Modifies:
2118 -- None.
2119 --Locks:
2120 -- None.
2121 --Function:
2122 -- Workflow activity PL/SQL handler.
2123 -- It checks if the AME_EXCEPTION attribute is NULL or not.
2124 -- If not NULL, it means there have been some AME exception encountered,
2125 -- and it returns 'Y'.
2126 -- Else it will return 'N'
2127 --Parameters:
2128 --IN:
2129 -- Standard workflow IN parameters
2130 --OUT:
2131 -- Standard workflow OUT parameters
2132 --Testing:
2133 --
2134 --End of Comments
2135 -------------------------------------------------------------------------------
2136 PROCEDURE IS_AME_EXCEPTION ( itemtype in varchar2,
2137 itemkey in varchar2,
2138 actid in number,
2139 funcmode in varchar2,
2140 resultout out NOCOPY varchar2) IS
2141 l_ame_exception ame_util.longestStringType;
2142 l_progress VARCHAR2(500) := '000';
2143 l_doc_string VARCHAR2(200);
2144 Begin
2145 IF (funcmode = 'RUN') THEN
2146 l_progress := 'IS_AME_EXCEPTION: 001';
2147 l_ame_exception :=PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
2148 itemkey => itemkey,
2149 aname => 'AME_EXCEPTION');
2150
2151 if l_ame_exception IS NOT NULL then
2152 resultout := wf_engine.eng_completed || ':' ||'Y';
2153 else
2154 resultout := wf_engine.eng_completed || ':' ||'N';
2155 end if;
2156 END IF;
2157 EXCEPTION
2158 when others then
2159 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2160 wf_core.context('POR_AME_REQ_WF_PVT','IS_AME_EXCEPTION: Unexpected Exception:',l_progress,sqlerrm);
2161 IF (g_po_wf_debug = 'Y') THEN
2162 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );
2163 END IF;
2164 raise;
2165 End IS_AME_EXCEPTION;
2166
2167 --------------------------------------------------------------------------------
2168 --Start of Comments
2169 --Name: position_has_valid_approvers
2170 --Pre-reqs:
2171 -- None.
2172 --Modifies:
2173 -- None.
2174 --Locks:
2175 -- None.
2176 --Function:
2177 -- Workflow activity PL/SQL handler.
2178 -- This function is used to check whether to launch the parallel approval process or not.
2179 -- If a position does not have any users, then this function will return 'N', otherwise return 'Y'
2180 --Parameters:
2181 --IN:
2182 -- documentId : ReqHeaderId
2183 -- documentType : AME Transaction Type
2184 --OUT:
2185 -- 'Y' We can launch the parallel approval process.
2186 -- 'N' Invalid approver. We can not launch the parallel approval process.
2187 -- 'NO_USERS' No users for position. This AME record will be deleted. Go to the next approver record.
2188 --Testing:
2189 --
2190 --End of Comments
2191 -------------------------------------------------------------------------------
2192 FUNCTION position_has_valid_approvers( documentId NUMBER, documentType VARCHAR2 )RETURN VARCHAR2 is
2193
2194 l_next_approver_id number;
2195 l_next_approver_name per_employees_current_x.full_name%TYPE;
2196 l_position_has_valid_approvers VARCHAR2(10);
2197 l_approver_index NUMBER;
2198
2199 l_first_approver_id NUMBER;
2200 l_first_position_id NUMBER;
2201
2202 BEGIN
2203
2204 l_position_has_valid_approvers := 'Y';
2205 l_approver_index := g_next_approvers.first();
2206
2207 select first_position_id, first_approver_id
2208 into l_first_position_id, l_first_approver_id
2209 from po_requisition_headers_all
2210 where documentId = requisition_header_id;
2211
2212 while( l_approver_index is not null ) loop
2213 l_position_has_valid_approvers := 'Y';
2214 if (g_next_approvers(l_approver_index).orig_system = ame_util.posOrigSystem) then
2215
2216 BEGIN
2217
2218 if (l_first_position_id is not NULL AND l_first_position_id=g_next_approvers(l_approver_index).orig_system_id) then
2219
2220 l_next_approver_id := l_first_approver_id;
2221
2222 SELECT full_name
2223 INTO l_next_approver_name
2224 FROM per_all_people_f person
2225 WHERE person_id = l_first_approver_id
2226 --Bug#7207213#This query fetches multiple records so adding a filter
2227 and trunc(sysdate) between person.effective_start_date and nvl(person.effective_end_date, trunc(sysdate));
2228
2229 else
2230
2231 /* find the persond id from the position_id*/
2232 SELECT person_id, full_name into l_next_approver_id,l_next_approver_name FROM (
2233 SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
2234 WHERE asg.position_id = g_next_approvers(l_approver_index).orig_system_id and trunc(sysdate) between person.effective_start_date
2235 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
2236 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
2237 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
2238 and asg.assignment_status_type_id not in (
2239 SELECT assignment_status_type_id FROM per_assignment_status_types
2240 WHERE per_system_status = 'TERM_ASSIGN'
2241 ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
2242 ) where rownum = 1;
2243
2244 end if;
2245
2246 EXCEPTION
2247 WHEN NO_DATA_FOUND THEN
2248
2249 -- No users for this position. Check whether this is last position or not.
2250 -- If this is last position then return the req to imcomplete status.
2251 -- Otherwise set this approver record to 'Approved'
2252 if( is_last_approver_record(documentId, documentType, g_next_approvers(l_approver_index)) = 'Y' )then
2253 return 'N';
2254 else
2255
2256 /*
2257 g_next_approvers(l_approver_index).approval_status := ame_util.noResponseStatus;
2258 -- Update the Approval status with the response from the approver.
2259 ame_api2.updateApprovalStatus( applicationIdIn =>applicationId,
2260 transactionIdIn =>documentId,
2261 transactionTypeIn =>documentType,
2262 approverIn => g_next_approvers(l_approver_index)
2263 );
2264 */
2265
2266 ame_api3.suppressApprover( applicationIdIn => applicationId,
2267 transactionIdIn => documentId,
2268 approverIn => g_next_approvers(l_approver_index),
2269 transactionTypeIn => documentType
2270 );
2271
2272 -- remove this approver from the global list.
2273 g_next_approvers.delete(l_approver_index);
2274 l_position_has_valid_approvers := 'NO_USERS';
2275
2276 end if;
2277 END;
2278 end if;
2279 l_approver_index := g_next_approvers.next(l_approver_index);
2280 end loop;
2281 return l_position_has_valid_approvers;
2282
2283 EXCEPTION
2284 WHEN OTHERS THEN
2285 return 'N';
2286 END position_has_valid_approvers;
2287
2288
2289 --------------------------------------------------------------------------------
2290 --Start of Comments
2291 --Name: is_last_approver_record
2292 --Pre-reqs:
2293 -- None.
2294 --Modifies:
2295 -- None.
2296 --Locks:
2297 -- None.
2298 --Function:
2299 -- Workflow activity PL/SQL handler.
2300 -- This function is used to check whether the approver/position is last in the approval chain or not
2301 -- This function will be invoked only if a particular position does not have any associated users.
2302 -- If this function returns 'Y', then the req will be put back in incomplete status.
2303 --Parameters:
2304 --IN:
2305 -- documentId : ReqHeaderId
2306 -- documentType : AME Transaction Type
2307 --OUT:
2308 -- 'Y' The approver/position is last in the approval chain.
2309 -- 'N' The approver/position is not last in the approval chain
2310 --Testing:
2311 --
2312 --End of Comments
2313 -------------------------------------------------------------------------------
2314 Function is_last_approver_record( documentId NUMBER, documentType VARCHAR2, approverRecord in ame_util.approverRecord2 ) RETURN VARCHAR2 is
2315
2316 l_is_last_approver_record VARCHAR2(1);
2317 l_total_approver_count NUMBER;
2318 l_current_approver_index NUMBER;
2319 tmpApproverList ame_util.approversTable2;
2320 l_process_out VARCHAR2(10);
2321
2322 BEGIN
2323
2324 ame_api2.getAllApprovers7( applicationIdIn =>applicationId,
2325 transactionIdIn =>documentId,
2326 transactionTypeIn =>documentType,
2327 approvalProcessCompleteYNOut => l_process_out,
2328 approversOut =>tmpApproverList
2329 );
2330
2331 l_total_approver_count := tmpApproverList.count;
2332 l_current_approver_index := 0;
2333
2334 for i in 1 .. tmpApproverList.count loop
2335
2336 l_current_approver_index := i;
2337 if ( tmpApproverList(i).name = approverRecord.name AND
2338 tmpApproverList(i).orig_system = approverRecord.orig_system AND
2339 tmpApproverList(i).orig_system_id = approverRecord.orig_system_id AND
2340 tmpApproverList(i).authority = approverRecord.authority AND
2341 tmpApproverList(i).group_or_chain_id = approverRecord.group_or_chain_id AND
2342 tmpApproverList(i).action_type_id = approverRecord.action_type_id AND
2343 tmpApproverList(i).item_id = approverRecord.item_id AND
2344 tmpApproverList(i).item_class = approverRecord.item_class AND
2345 tmpApproverList(i).approver_category = approverRecord.approver_category
2346 ) then
2347
2348 EXIT;
2349 end if;
2350 end loop;
2351
2352 if( l_current_approver_index = l_total_approver_count ) then
2353 return 'Y';
2354 else
2355 return 'N';
2356 end if;
2357
2358 EXCEPTION
2359 WHEN OTHERS THEN
2360 return 'Y';
2361 END is_last_approver_record;
2362
2363 END POR_AME_REQ_WF_PVT;