[Home] [Help]
PACKAGE BODY: APPS.POR_AME_RCO_WF_PVT
Source
1 PACKAGE BODY POR_AME_RCO_WF_PVT AS
2 /* $Header: POXAMERB.pls 120.9.12000000.2 2007/02/13 00:57:49 dkfchan 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 Function is_last_approver_record( documentId NUMBER, documentType VARCHAR2, approverRecord in ame_util.approverRecord2 ) RETURN VARCHAR2;
11
12 PROCEDURE UpdateActionHistory(p_document_id NUMBER,
13 p_action VARCHAR2,
14 p_note VARCHAR2,
15 p_current_approver NUMBER);
16
17 --------------------------------------------------------------------------------
18 --Start of Comments
19 --Name: Get_Next_Approvers
20 --Pre-reqs:
21 -- None.
22 --Modifies:
23 -- None.
24 --Locks:
25 -- None.
26 --Function:
27 -- Workflow activity PL/SQL handler
28 -- Get the next approver name from the AME approval list
29 -- And update workflow attributes.
30 -- If no next approver is found, approval routing will terminate.
31 --Parameters:
32 --IN:
33 -- Standard workflow IN parameters
34 --OUT:
35 -- Standard workflow OUT parameters
36 --Testing:
37 --
38 --End of Comments
39 -------------------------------------------------------------------------------
40 procedure Get_Next_Approvers(itemtype in varchar2,
41 itemkey in varchar2,
42 actid in number,
43 funcmode in varchar2,
44 resultout out NOCOPY varchar2) IS
45 l_progress VARCHAR2(500) := '000';
46 l_document_id NUMBER;
47 l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
48 l_document_subtype PO_DOCUMENT_TYPES.DOCUMENT_SUBTYPE%TYPE;
49 l_next_approver_id NUMBER;
50 l_next_approver_user_name fnd_user.user_name%TYPE;
51 l_next_approver_disp_name wf_users.display_name%TYPE;
52 l_orig_system wf_users.orig_system%TYPE := 'PER';
53 l_sequence_num NUMBER;
54 l_approver_type VARCHAR2(30);
55
56 l_doc_string varchar2(200);
57 l_preparer_user_name fnd_user.user_name%TYPE;
58 l_org_id number;
59
60 l_next_approver ame_util.approverRecord;
61 l_insertion_type VARCHAR2(30);
62 l_authority_type VARCHAR2(30);
63 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
64 l_completeYNO varchar2(100);
65 l_position_has_valid_approvers varchar2(10);
66 l_need_to_get_next_approver boolean;
67 BEGIN
68 IF (funcmode = 'RUN') THEN
69
70 l_progress := 'Get_Next_Approver: 001';
71 IF (g_po_wf_debug = 'Y') THEN
72 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
73 END IF;
74
75 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
76 itemkey => itemkey,
77 aname => 'DOCUMENT_ID');
78
79 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
80 itemkey => itemkey,
81 aname => 'DOCUMENT_TYPE');
82
83 l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
84 itemkey => itemkey,
85 aname => 'DOCUMENT_SUBTYPE');
86
87 l_transaction_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
88 itemkey => itemkey,
89 aname => 'AME_TRANSACTION_TYPE');
90
91 l_progress := 'Get_Next_Approver: 002-'||to_char(l_document_id)||'-'|| l_document_type||'-'||l_document_subtype;
92
93 IF (g_po_wf_debug = 'Y') THEN
94 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
95 END IF;
96
97 -- Get the next approver from AME.
98 LOOP
99
100 l_need_to_get_next_approver := FALSE;
101 BEGIN
102
103 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanTrue;
104 ame_api2.getNextApprovers4( applicationIdIn=>applicationId,
105 transactionIdIn=>l_document_id,
106 transactionTypeIn=>l_transaction_type,
107 approvalProcessCompleteYNOut=>l_completeYNO,
108 nextApproversOut=>g_next_approvers
109 );
110 EXCEPTION
111 WHEN OTHERS THEN
112
113 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
114 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
115 wf_core.context('POR_AME_REQ_WF_PVT','Get_Next_Approvers: Unable to get the next approvers from AME.',l_progress,sqlerrm);
116
117 IF (g_po_wf_debug = 'Y') THEN
118 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string);
119 END IF;
120
121 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.GET_NEXT_APPROVERS');
122
123 resultout:='COMPLETE:'||'INVALID_APPROVER';
124 return;
125 END;
126
127 l_progress := 'Get_Next_Approver: 003- getNextApprovers4(). Approvers :' || g_next_approvers.count || ' -- Approval Process Completed :' || l_completeYNO ;
128 IF (g_po_wf_debug = 'Y') THEN
129 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
130 END IF;
131
132 if ( g_next_approvers.count > 0 ) then
133
134 l_position_has_valid_approvers := position_has_valid_approvers(l_document_id, l_transaction_type) ;
135
136 l_progress := 'Get_Next_Approver: 004 - l_position_has_valid_approvers :' || l_position_has_valid_approvers;
137 IF (g_po_wf_debug = 'Y') THEN
138 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
139 END IF;
140
141 l_progress := 'Get_Next_Approver: 005- Approvers after the validation process :' || g_next_approvers.count;
142 IF (g_po_wf_debug = 'Y') THEN
143 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
144 END IF;
145
146 if( g_next_approvers.count = 0 AND 'NO_USERS' = l_position_has_valid_approvers ) then
147 l_need_to_get_next_approver := TRUE;
148 end if;
149
150 end if;
151
152 EXIT WHEN l_need_to_get_next_approver = FALSE;
153 END LOOP;
154
155 -- Check the number of next approvers. If the count is zero, then verify the approval process is completed or not.
156 if ( g_next_approvers.count > 0 ) then
157
158 if( 'N' = l_position_has_valid_approvers ) then
159 resultout:='COMPLETE:'||'INVALID_APPROVER';
160 else
161 resultout:='COMPLETE:'||'VALID_APPROVER';
162 end if;
163 return;
164
165 else
166
167 -- 'X' is the code when there is no rule needed and applied.
168
169 if (l_completeYNO in ('X','Y')) then
170 resultout:='COMPLETE:'||'NO_NEXT_APPROVER';
171 return;
172 else
173 resultout:='COMPLETE:'||'';
174 return;
175 end if;
176 end if;
177 end if;
178 EXCEPTION
179 WHEN OTHERS THEN
180 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
181 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
182 wf_core.context('POR_AME_RCO_WF_PVT','Get_Next_Approvers-Unexpected Exception:',l_progress,sqlerrm);
183
184 IF (g_po_wf_debug = 'Y') THEN
185 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string);
186 END IF;
187
188 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_RCO_WF_PVT.GET_NEXT_APPROVERS');
189 resultout:='COMPLETE:'||'INVALID_APPROVER';
190 return;
191 END Get_Next_Approvers;
192
193
194 --------------------------------------------------------------------------------
195 --Start of Comments
196 --Name: Launch_Parallel_Approval
197 --Pre-reqs:
198 -- None.
199 --Modifies:
200 -- None.
201 --Locks:
202 -- None.
203 --Function:
204 -- Workflow activity PL/SQL handler. This procedure is used to send the notification for the approvers.
205 -- Iterate through the list of approvers got from the API call ame_api2.getNextApprovers4.
206 -- Get the next approver name from the global variable g_next_approvers and for each retrieved approver
207 -- separate workflow process is kicked. Each process is called child process.
208 -- If there are 3 approvers, then 3 child process will be created and each of them will be notified at the same time.
209 --
210 -- If the next approver record is of Position Hierarchy type, then the users associated to the position_id will be
211 -- retrieved, will be alphabetically sorted using last_name and to the first user notification will be sent.
212 --
213 --Parameters:
214 --IN:
215 -- Standard workflow IN parameters
216 --OUT:
217 -- Standard workflow OUT parameters
218 --Testing:
219 --
220 --End of Comments
221 -------------------------------------------------------------------------------
222 procedure Launch_Parallel_Approval(itemtype in varchar2,
223 itemkey in varchar2,
224 actid in number,
225 funcmode in varchar2,
226 resultout out NOCOPY varchar2) IS
227
228 l_progress VARCHAR2(500) := '000';
229 l_document_id number;
230 l_item_key wf_items.item_key%TYPE;
231 l_next_approver_id number;
232 l_next_approver_name per_employees_current_x.full_name%TYPE;
233 l_next_approver_user_name VARCHAR2(100);
234 l_next_approver_disp_name VARCHAR2(240);
235 l_orig_system VARCHAR2(48);
236 l_org_id number;
237 l_functional_currency VARCHAR2(30);
238 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
239
240 n_varname Wf_Engine.NameTabTyp;
241 n_varval Wf_Engine.NumTabTyp;
242
243 t_varname Wf_Engine.NameTabTyp;
244 t_varval Wf_Engine.TextTabTyp;
245
246 l_no_positionholder exception;
247 l_preparer_user_name fnd_user.user_name%TYPE;
248 l_doc_string varchar2(200);
249 l_start_block_activity varchar2(1);
250 l_approver_index NUMBER;
251 l_change_request_group_id NUMBER;
252
253 l_first_position_id NUMBER;
254 l_first_approver_id NUMBER;
255
256 begin
257 IF (funcmode='RUN') THEN
258
259 l_progress := 'Launch_Parallel_Approval: 001';
260 IF (g_po_wf_debug = 'Y') THEN
261 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
262 END IF;
263
264 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
265 itemkey => itemkey,
266 aname => 'DOCUMENT_ID');
267
268 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
269 itemkey => itemkey,
270 aname => 'ORG_ID');
271
272 l_change_request_group_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
273 itemkey => itemkey,
274 aname => 'CHANGE_REQUEST_GROUP_ID');
275
276 l_start_block_activity := 'N';
277 -- Iterate through the list of next approvers.
278 l_approver_index := g_next_approvers.first();
279 while ( l_approver_index is not null ) loop
280
281 l_progress := 'Launch_Parallel_Approval: 002 -- Next Approver :' || g_next_approvers(l_approver_index).name;
282 IF (g_po_wf_debug = 'Y') THEN
283 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
284 END IF;
285
286 SELECT
287 to_char(l_document_id) || '-' || to_char(PO_WF_ITEMKEY_S.nextval)
288 INTO l_item_key
289 FROM sys.dual;
290
291 -- Create a child process for the retrieved approver.
292 wf_engine.CreateProcess( itemtype => itemtype,
293 itemkey => l_item_key,
294 process => 'RCO_AME_PARALLEL_APPROVAL');
295
296
297 /* Need to set the parent child relationship between processes */
298 wf_engine.SetItemParent( itemtype => itemtype,
299 itemkey => l_item_key,
300 parent_itemtype => itemtype,
301 parent_itemkey => itemkey,
302 parent_context => NULL);
303
304 t_varname(1) := 'DOCUMENT_TYPE';
305 t_varval(1) := 'REQUISITION';
306 t_varname(2) := 'DOCUMENT_TYPE_DISP';
307 t_varval(2) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
308 itemkey => itemkey,
309 aname => 'DOCUMENT_TYPE_DISP');
310
311 t_varname(3) := 'PREPARER_USER_NAME';
312 t_varval(3) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
313 itemkey => itemkey,
314 aname => 'PREPARER_USER_NAME');
315
316 t_varname(4) := 'PREPARER_DISPLAY_NAME';
317 t_varval(4) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
318 itemkey => itemkey,
319 aname => 'PREPARER_DISPLAY_NAME');
320
321 t_varname(5) := 'FUNCTIONAL_CURRENCY';
322 t_varval(5) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
323 itemkey => itemkey,
324 aname => 'FUNCTIONAL_CURRENCY');
325
326 t_varname(6) := 'IS_AME_APPROVAL';
327 t_varval(6) := 'Y';
328
329 t_varname(7) := 'TOTAL_AMOUNT_DSP';
330 t_varval(7) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
331 itemkey => itemkey,
332 aname => 'TOTAL_AMOUNT_DSP');
333
334 t_varname(8) := 'FORWARD_FROM_DISP_NAME';
335 t_varval(8) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
336 itemkey => itemkey,
337 aname => 'FORWARD_FROM_DISP_NAME');
338
339 t_varname(9) := 'FORWARD_FROM_USER_NAME';
340 t_varval(9) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
341 itemkey => itemkey,
342 aname => 'FORWARD_FROM_USER_NAME');
343
344 t_varname(10) := 'REQ_DESCRIPTION';
345 t_varval(10) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
346 itemkey => itemkey,
347 aname => 'REQ_DESCRIPTION');
348
349 t_varname(11) := 'REQ_AMOUNT_CURRENCY_DSP';
350 t_varval(11) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
351 itemkey => itemkey,
352 aname => 'REQ_AMOUNT_CURRENCY_DSP');
353
354 t_varname(12) := 'TAX_AMOUNT_CURRENCY_DSP';
355 t_varval(12) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
356 itemkey => itemkey,
357 aname => 'TAX_AMOUNT_CURRENCY_DSP');
358
359 t_varname(13) := 'JUSTIFICATION';
360 t_varval(13) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
361 itemkey => itemkey,
362 aname => 'JUSTIFICATION');
363
364 t_varname(14) := 'CONTRACTOR_REQUISITION_FLAG';
365 t_varval(14) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
366 itemkey => itemkey,
367 aname => 'CONTRACTOR_REQUISITION_FLAG');
368
369 t_varname(15) := 'CONTRACTOR_REQUISITION_FLAG';
370 t_varval(15) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
371 itemkey => itemkey,
372 aname => 'CONTRACTOR_REQUISITION_FLAG');
373
374 t_varname(16) := 'CONTRACTOR_STATUS';
375 t_varval(16) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
376 itemkey => itemkey,
377 aname => 'CONTRACTOR_STATUS');
378
379 t_varname(17) := 'DOCUMENT_NUMBER';
380 t_varval(17) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
381 itemkey => itemkey,
382 aname => 'DOCUMENT_NUMBER');
383
384 t_varname(18) := 'AME_TRANSACTION_TYPE';
385 t_varval(18) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
386 itemkey => itemkey,
387 aname => 'AME_TRANSACTION_TYPE');
388
389 l_progress := 'Launch_Parallel_Approval: 003 -- Record Type :' || g_next_approvers(l_approver_index).orig_system;
390 IF (g_po_wf_debug = 'Y') THEN
391 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
392 END IF;
393
394 -- Check whether Position Hierarchy or Employee-Sup Hierarchy setup or FND users.
395 if (g_next_approvers(l_approver_index).orig_system = 'PER') then
396 l_next_approver_id := g_next_approvers(l_approver_index).orig_system_id;
397 elsif (g_next_approvers(l_approver_index).orig_system = 'POS') then
398
399 begin
400
401 select first_position_id, first_approver_id
402 into l_first_position_id, l_first_approver_id
403 from po_requisition_headers_all
404 where l_document_id = requisition_header_id;
405
406 if (l_first_position_id is not NULL AND l_first_position_id=g_next_approvers(l_approver_index).orig_system_id ) then
407
408 l_next_approver_id := l_first_approver_id;
409
410 SELECT full_name
411 INTO l_next_approver_name
412 FROM per_all_people_f person
413 WHERE person_id = l_first_approver_id;
414
415 else
416
417 /* find the persond id from the position_id*/
418 SELECT person_id, full_name into l_next_approver_id,l_next_approver_name FROM (
419 SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
420 WHERE asg.position_id = g_next_approvers(l_approver_index).orig_system_id and trunc(sysdate) between person.effective_start_date
421 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
422 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
423 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
424 and asg.assignment_status_type_id not in (
425 SELECT assignment_status_type_id FROM per_assignment_status_types
426 WHERE per_system_status = 'TERM_ASSIGN'
427 ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
428 ) where rownum = 1;
429
430 end if;
431 exception
432 WHEN NO_DATA_FOUND THEN
433 RAISE;
434 END;
435
436 elsif (g_next_approvers(l_approver_index).orig_system = 'FND') then
437 SELECT employee_id
438 into l_next_approver_id
439 FROM fnd_user
440 WHERE user_id = g_next_approvers(l_approver_index).orig_system_id
441 and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
442 end if;
443
444 t_varname(19) := 'AME_APPROVER_TYPE';
445 t_varval(19) := g_next_approvers(l_approver_index).orig_system;
446
447
448 WF_DIRECTORY.GetUserName('PER', l_next_approver_id, l_next_approver_user_name, l_next_approver_disp_name);
449
450 l_progress := 'Launch_Parallel_Approval: 004 -- Next Approver User Name -- display Name:' || l_next_approver_user_name || ' -- ' || l_next_approver_disp_name;
451 IF (g_po_wf_debug = 'Y') THEN
452 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
453 END IF;
454
455 t_varname(20) := 'APPROVER_USER_NAME';
456 t_varval(20) := l_next_approver_user_name;
457
458 t_varname(21) := 'APPROVER_DISPLAY_NAME';
459 t_varval(21) := l_next_approver_disp_name;
460
461 /* Kick off the process */
462 l_progress:= '30: start_wf_line_process: Kicking off StartProcess';
463 IF (g_po_wf_debug = 'Y') THEN
464 po_wf_debug_pkg.insert_debug(itemtype,itemkey,l_progress);
465 END IF;
466
467 t_varname(22) := 'RCO_AME_IS_FYI_APPROVER';
468 if (g_next_approvers(l_approver_index).approver_category = ame_util.fyiApproverCategory) then
469 t_varval(22) :='Y';
470 else
471 t_varval(22) :='N';
472 l_start_block_activity := 'Y';
473 end if;
474
475 -- Set the item attributes.
476 Wf_Engine.SetItemAttrTextArray(itemtype, l_item_key,t_varname,t_varval);
477
478 n_varname(1) := 'DOCUMENT_ID';
479 n_varval(1) := l_document_id;
480
481 n_varname(2) := 'ORG_ID';
482 n_varval(2) := l_org_id;
483
484 n_varname(3) := 'AME_APPROVER_ID';
485 n_varval(3) := g_next_approvers(l_approver_index).orig_system_id;
486
487 n_varname(4) := 'APPROVER_EMPID';
488 n_varval(4) := l_next_approver_id;
489
490 n_varname(5) := 'APPROVAL_GROUP_ID';
491 n_varval(5) := g_next_approvers(l_approver_index).group_or_chain_id;
492
493 n_varname(6) := 'CHANGE_REQUEST_GROUP_ID';
494 n_varval(6) := l_change_request_group_id;
495
496 Wf_Engine.SetItemAttrNumberArray(itemtype, l_item_key,n_varname,n_varval);
497
498 l_progress := 'Launch_Parallel_Approval: 005 -- Launch Parallel Approval';
499 IF (g_po_wf_debug = 'Y') THEN
500 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
501 END IF;
502 wf_engine.StartProcess( itemtype => itemtype,
503 itemkey => l_item_key );
504
505 l_approver_index := g_next_approvers.next(l_approver_index);
506
507 end loop; -- end of for loop.
508
509 if l_start_block_activity = 'Y' then
510 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
511 else
512 resultout:='COMPLETE' || ':' || '';
513 end if;
514 g_next_approvers.delete;
515
516 RETURN;
517
518 END IF; --run mode
519
520 exception
521 when NO_DATA_FOUND then
522 l_progress:= '50: start_wf_line_process: NO_DATA_FOUND -- EXCEPTION';
523 l_doc_string := l_progress || PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
524 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
525 wf_core.context('POR_AME_RCO_WF_PVT','Launch_Parallel_Approval',l_progress,sqlerrm);
526
527 IF (g_po_wf_debug = 'Y') THEN
528 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );
529 END IF;
530 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_RCO_WF_PVT.GET_NEXT_APPROVER');
531 raise;
532 when others then
533 l_progress:= '50: start_wf_line_process: IN EXCEPTION';
534 l_doc_string := l_progress || PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
535 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
536 wf_core.context('POR_AME_RCO_WF_PVT','Launch_Parallel_Approval',l_progress,sqlerrm);
537
538 IF (g_po_wf_debug = 'Y') THEN
539 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );
540 END IF;
541
542 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_RCO_WF_PVT.GET_NEXT_APPROVER');
543 raise;
544
545 end Launch_Parallel_Approval;
546
547
548 --------------------------------------------------------------------------------
549 --Start of Comments
550 --Name: Process_Response_Internal
551 --Pre-reqs:
552 -- None.
553 --Modifies:
554 -- None.
555 --Locks:
556 -- None.
557 --Function:
558 -- Workflow activity PL/SQL handler. This procedure is used to inform AME about the approvers response.
559 --Parameters:
560 --IN:
561 -- Standard workflow IN parameters
562 --OUT:
563 -- Standard workflow OUT parameters
564 --Testing:
565 --
566 --End of Comments
567 -------------------------------------------------------------------------------
568 procedure Process_Response_Internal( itemtype in varchar2,
569 itemkey in varchar2,
570 p_response in varchar2 ) IS
571
572 l_progress VARCHAR2(500) := '000';
573 l_document_id number;
574 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
575 l_current_approver ame_util.approverRecord2;
576 l_approver_posoition_id number;
577 l_approver_type varchar2(10);
578
579 begin
580
581 l_progress := 'Process_Response_Internal: 001';
582 IF (g_po_wf_debug = 'Y') THEN
583 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
584 END IF;
585
586 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
587 itemkey => itemkey,
588 aname => 'DOCUMENT_ID');
589
590 l_transaction_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
591 itemkey => itemkey,
592 aname => 'AME_TRANSACTION_TYPE');
593
594 l_approver_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
595 itemkey => itemkey,
596 aname => 'AME_APPROVER_TYPE');
597
598 l_progress := 'Process_Response_Internal: 002 -- l_approver_type :' || l_approver_type ;
599 IF (g_po_wf_debug = 'Y') THEN
600 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
601 END IF;
602
603 if (l_approver_type = 'POS') then
604 l_current_approver.orig_system := 'POS';
605 elsif (l_approver_type = 'FND') then
606 l_current_approver.orig_system := 'FND';
607 else
608 l_current_approver.orig_system := 'PER';
609 l_current_approver.name := po_wf_util_pkg.GetItemAttrText( itemtype => itemType,
610 itemkey => itemkey,
611 aname => 'APPROVER_USER_NAME');
612 end if;
613
614 l_current_approver.orig_system_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
615 itemkey => itemkey,
616 aname => 'AME_APPROVER_ID');
617
618 l_progress := 'Process_Response_Internal: 003 -- l_current_approver.orig_system_id :' || l_current_approver.orig_system_id ;
619 IF (g_po_wf_debug = 'Y') THEN
620 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
621 END IF;
622
623 if( p_response = 'APPROVE') then
624 l_current_approver.approval_status := ame_util.approvedStatus;
625 elsif( p_response = 'REJECT') then
626 l_current_approver.approval_status := ame_util.rejectStatus;
627 elsif( p_response = 'TIMEOUT') then
628 l_current_approver.approval_status := ame_util.noResponseStatus;
629 end if;
630
631 l_progress := 'Process_Response_Internal: 004 -- p_response :' || p_response ;
632 IF (g_po_wf_debug = 'Y') THEN
633 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
634 END IF;
635
636 -- Get the name value for the approverRecord2.
637 -- This is a mandatory field. If we do not pass this value to AME, we will get invalid parameter exception.
638 -- bug# 4936145
639 IF l_current_approver.name IS NULL THEN
640 SELECT name into l_current_approver.name FROM
641 ( SELECT name FROM wf_roles WHERE orig_system = l_current_approver.orig_system
642 and orig_system_id = l_current_approver.orig_system_id
643 order by start_date
644 )
645 WHERE rownum = 1;
646 END IF;
647
648 IF l_current_approver.name IS NULL THEN
649 raise_application_error(-20001, 'Record Not Found in WF_ROLES for the orig_system_id :' ||
650 l_current_approver.orig_system_id || ' -- orig_system :' || l_current_approver.orig_system );
651 END IF;
652
653 -- Update the Approval status with the response from the approver.
654 ame_api2.updateApprovalStatus( applicationIdIn=>applicationId,
655 transactionIdIn=>l_document_id,
656 transactionTypeIn=>l_transaction_type,
657 approverIn => l_current_approver);
658
659 exception
660 when others then
661 raise;
662 end Process_Response_Internal;
663
664
665 --------------------------------------------------------------------------------
666 --Start of Comments
667 --Name: Process_Response_Approve
668 --Pre-reqs:
669 -- None.
670 --Modifies:
671 -- None.
672 --Locks:
673 -- None.
674 --Function:
675 -- Workflow activity PL/SQL handler.
676 -- This procedure is the wrapper procedure of Process_Response_Internal()
677 --Parameters:
678 --IN:
679 -- Standard workflow IN parameters
680 --OUT:
681 -- Standard workflow OUT parameters
682 --Testing:
683 --
684 --End of Comments
685 -------------------------------------------------------------------------------
686 procedure Process_Response_Approve( itemtype in varchar2,
687 itemkey in varchar2,
688 actid in number,
689 funcmode in varchar2,
690 resultout out NOCOPY varchar2) IS
691
692 l_progress VARCHAR2(500) := '000';
693 l_parent_item_type wf_items.parent_item_type%TYPE;
694 l_parent_item_key wf_items.parent_item_key%TYPE;
695
696 begin
697
698 l_progress := 'Process_Response_Approve: 001';
699 IF (g_po_wf_debug = 'Y') THEN
700 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
701 END IF;
702
703 SELECT parent_item_type, parent_item_key
704 into l_parent_item_type, l_parent_item_key
705 FROM wf_items
706 WHERE item_type = itemtype and item_key = itemkey;
707
708 Process_Response_Internal(itemtype, itemkey, 'APPROVE');
709
710 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
711 itemkey => itemkey,
712 aname => 'RCO_AME_SUB_APPROVAL_RESPONSE',
713 avalue => 'APPROVE');
714
715 l_progress := 'Process_Response_Approve: 002 -- Completing the BLOCK activity for the APPROVED notification.';
716 IF (g_po_wf_debug = 'Y') THEN
717 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
718 END IF;
719
720 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
721 RETURN;
722 end Process_Response_Approve;
723
724 --------------------------------------------------------------------------------
725 --Start of Comments
726 --Name: Process_Response_Reject
727 --Pre-reqs:
728 -- None.
729 --Modifies:
730 -- None.
731 --Locks:
732 -- None.
733 --Function:
734 -- Workflow activity PL/SQL handler.
735 -- This procedure is the wrapper procedure of Process_Response_Internal()
736 --Parameters:
737 --IN:
738 -- Standard workflow IN parameters
739 --OUT:
740 -- Standard workflow OUT parameters
741 --Testing:
742 --
743 --End of Comments
744 -------------------------------------------------------------------------------
745 procedure Process_Response_Reject( itemtype in varchar2,
746 itemkey in varchar2,
747 actid in number,
748 funcmode in varchar2,
749 resultout out NOCOPY varchar2) IS
750
751 l_progress VARCHAR2(500) := '000';
752 l_parent_item_type wf_items.parent_item_type%TYPE;
753 l_parent_item_key wf_items.parent_item_key%TYPE;
754
755 begin
756
757 l_progress := 'Process_Response_Reject: 001';
758 IF (g_po_wf_debug = 'Y') THEN
759 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
760 END IF;
761
762 Process_Response_Internal(itemtype, itemkey, 'REJECT');
763
764 SELECT parent_item_type, parent_item_key
765 into l_parent_item_type, l_parent_item_key
766 FROM wf_items
767 WHERE item_type = itemtype and item_key = itemkey;
768
769 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
770 itemkey => l_parent_item_key,
771 aname => 'RCO_AME_SUB_APPROVAL_RESPONSE',
772 avalue => 'REJECT');
773
774 l_progress := 'Process_Response_Reject: 002 -- Completing the BLOCK activity for the REJECTED notification.';
775 IF (g_po_wf_debug = 'Y') THEN
776 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
777 END IF;
778
779 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
780 RETURN;
781
782 end Process_Response_Reject;
783
784 --------------------------------------------------------------------------------
785 --Start of Comments
786 --Name: Process_Response_Timeout
787 --Pre-reqs:
788 -- None.
789 --Modifies:
790 -- None.
791 --Locks:
792 -- None.
793 --Function:
794 -- Workflow activity PL/SQL handler.
795 -- This procedure is the wrapper procedure of Process_Response_Internal()
796 --Parameters:
797 --IN:
798 -- Standard workflow IN parameters
799 --OUT:
800 -- Standard workflow OUT parameters
801 --Testing:
802 --
803 --End of Comments
804 -------------------------------------------------------------------------------
805 procedure Process_Response_Timeout( itemtype in varchar2,
806 itemkey in varchar2,
807 actid in number,
808 funcmode in varchar2,
809 resultout out NOCOPY varchar2) IS
810
811 l_progress VARCHAR2(500) := '000';
812 l_parent_item_type wf_items.parent_item_type%TYPE;
813 l_parent_item_key wf_items.parent_item_key%TYPE;
814
815 begin
816
817 l_progress := 'Process_Response_Timeout: 001';
818 IF (g_po_wf_debug = 'Y') THEN
819 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
820 END IF;
821
822 Process_Response_Internal(itemtype, itemkey, 'TIMEOUT');
823
824 SELECT parent_item_type, parent_item_key
825 into l_parent_item_type, l_parent_item_key
826 FROM wf_items
827 WHERE item_type = itemtype and item_key = itemkey;
828
829 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
830 itemkey => l_parent_item_key,
831 aname => 'AME_SUB_APPROVAL_RESPONSE',
832 avalue => 'REJECT');
833
834 wf_engine.CompleteActivity (itemtype => l_parent_item_type,
835 itemkey => l_parent_item_key,
836 activity => 'BLOCK',
837 result => null);
838
839 l_progress := 'Process_Response_Timeout: 002 -- Completing the BLOCK activity for the REJECTED notification.';
840 IF (g_po_wf_debug = 'Y') THEN
841 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
842 END IF;
843
844 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
845 RETURN;
846
847 end Process_Response_Timeout;
848
849
850 --------------------------------------------------------------------------------
851 --Start of Comments
852 --Name: insertActionHistory
853 --Pre-reqs:
854 -- None.
855 --Modifies:
856 -- None.
857 --Locks:
858 -- None.
859 --Function:
860 -- Workflow activity PL/SQL handler.
861 -- This procedure is used to keep the history of each notification.
862 -- The inserted records will be displayed in Approval History page.
863 --Parameters:
864 --IN:
865 -- Requistion Header Id
866 -- Employee Id
867 --OUT:
868 -- Standard workflow OUT parameters
869 --Testing:
870 --
871 --End of Comments
872 -------------------------------------------------------------------------------
873 procedure insertActionHistory( p_req_header_id in number,
874 p_employee_id in number,
875 p_approval_group_id in number )
876
877 is
878 pragma AUTONOMOUS_TRANSACTION;
879
880 l_object_sub_type_code PO_ACTION_HISTORY.OBJECT_SUB_TYPE_CODE%TYPE;
881 l_sequence_num PO_ACTION_HISTORY.SEQUENCE_NUM%TYPE;
882 l_object_revision_num PO_ACTION_HISTORY.OBJECT_REVISION_NUM%TYPE;
883 l_approval_path_id PO_ACTION_HISTORY.APPROVAL_PATH_ID%TYPE;
884 l_request_id PO_ACTION_HISTORY.REQUEST_ID%TYPE;
885 l_program_application_id PO_ACTION_HISTORY.PROGRAM_APPLICATION_ID%TYPE;
886 l_program_date PO_ACTION_HISTORY.PROGRAM_DATE%TYPE;
887 l_program_id PO_ACTION_HISTORY.PROGRAM_ID%TYPE;
888 l_progress VARCHAR2(100) := '000';
889
890 begin
891
892 SELECT max(sequence_num)
893 INTO l_sequence_num
894 FROM PO_ACTION_HISTORY
895 WHERE object_type_code = 'REQUISITION'
896 AND object_id = p_req_header_id;
897
898 SELECT object_sub_type_code,
899 object_revision_num, approval_path_id, request_id,
900 program_application_id, program_date, program_id
901 INTO l_object_sub_type_code,
902 l_object_revision_num, l_approval_path_id, l_request_id,
903 l_program_application_id, l_program_date, l_program_id
904 FROM PO_ACTION_HISTORY
905 WHERE object_type_code = 'REQUISITION'
906 AND object_id = p_req_header_id
907 AND sequence_num = l_sequence_num;
908
909 /* update po action history */
910 po_forward_sv1.insert_action_history (
911 p_req_header_id,
912 'REQUISITION',
913 l_object_sub_type_code,
914 l_sequence_num + 1,
915 NULL,
916 NULL,
917 p_employee_id,
918 NULL,
919 NULL,
920 l_object_revision_num,
921 NULL, /* offline_code */
922 l_request_id,
923 l_program_application_id,
924 l_program_id,
925 l_program_date,
926 fnd_global.user_id,
927 fnd_global.login_id,
928 p_approval_group_id);
929
930 commit;
931
932 end insertActionHistory;
933
934
935 --------------------------------------------------------------------------------
936 --Start of Comments
937 --Name: Insert_Action_History
938 --Pre-reqs:
939 -- None.
940 --Modifies:
941 -- None.
942 --Locks:
943 -- None.
944 --Function:
945 -- Workflow activity PL/SQL handler.
946 -- This procedure is the wrapper procedure of insertActionHistory()
947 --Parameters:
948 --IN:
949 -- Standard workflow IN parameters
950 --OUT:
951 -- Standard workflow OUT parameters
952 --Testing:
953 --
954 --End of Comments
955 -------------------------------------------------------------------------------
956 procedure Insert_Action_History( itemtype in varchar2,
957 itemkey in varchar2,
958 actid in number,
959 funcmode in varchar2,
960 resultout out NOCOPY varchar2) IS
961
962 l_progress VARCHAR2(500) := '000';
963 l_action VARCHAR2(30) := 'APPROVE';
964 l_next_approver_id NUMBER:='';
965 l_req_header_id NUMBER:='';
966 l_approval_group_id NUMBER:='';
967
968 l_doc_string varchar2(200);
969 l_preparer_user_name varchar2(100);
970
971 l_org_id number;
972
973 BEGIN
974
975 l_progress := 'Insert_Action_History: 001';
976 IF (g_po_wf_debug = 'Y') THEN
977 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
978 END IF;
979
980 IF (funcmode='RUN') THEN
981
982
983 l_next_approver_id := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
984 itemkey=>itemkey,
985 aname=>'APPROVER_EMPID');
986
987
988 l_req_header_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
989 itemkey => itemkey,
990 aname => 'DOCUMENT_ID');
991
992 l_approval_group_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
993 itemkey => itemkey,
994 aname => 'APPROVAL_GROUP_ID');
995
996 -- Set the multi-org context
997 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
998 itemkey => itemkey,
999 aname => 'ORG_ID');
1000
1001 IF l_org_id is NOT NULL THEN
1002 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1003 END IF;
1004
1005 l_progress := 'Insert_Action_History: 004 - Forward_Action_History';
1006 insertActionHistory(l_req_header_id, l_next_approver_id, l_approval_group_id);
1007
1008 l_progress := 'Insert_Action_History: 005 - Forward_Action_History';
1009 IF (g_po_wf_debug = 'Y') THEN
1010 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1011 END IF;
1012
1013 /* Reset the FORWARD_TO_USERNAME_RESPONSE attribute */
1014 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
1015 itemkey => itemkey,
1016 aname => 'FORWARD_TO_USERNAME_RESPONSE',
1017 avalue => NULL);
1018
1019 /* Reset the NOTE attribute */
1020 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
1021 itemkey => itemkey,
1022 aname => 'NOTE',
1023 avalue => NULL);
1024
1025 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1026 return;
1027
1028 END IF; -- run mode
1029
1030 l_progress := 'Insert_Action_History: 999';
1031 IF (g_po_wf_debug = 'Y') THEN
1032 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1033 END IF;
1034
1035 EXCEPTION
1036 WHEN OTHERS THEN
1037 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1038 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1039 wf_core.context('POR_AME_RCO_WF_PVT','Insert_Action_History',l_progress,sqlerrm);
1040 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_RCO_WF_PVT.INSERT_ACTION_HISTORY');
1041 RAISE;
1042 END Insert_Action_History;
1043
1044
1045 --------------------------------------------------------------------------------
1046 --Start of Comments
1047 --Name: Update_Action_History_Approve
1048 --Pre-reqs:
1049 -- None.
1050 --Modifies:
1051 -- None.
1052 --Locks:
1053 -- None.
1054 --Function:
1055 -- Workflow activity PL/SQL handler.
1056 -- This procedure updates the po_action_history table based on the approvers response.
1057 --Parameters:
1058 --IN:
1059 -- Standard workflow IN parameters
1060 --OUT:
1061 -- Standard workflow OUT parameters
1062 --Testing:
1063 --
1064 --End of Comments
1065 -------------------------------------------------------------------------------
1066 procedure Update_Action_History_Approve( itemtype in varchar2,
1067 itemkey in varchar2,
1068 actid in number,
1069 funcmode in varchar2,
1070 resultout out NOCOPY varchar2) IS
1071 l_progress VARCHAR2(500) := '000';
1072 l_action VARCHAR2(30) := 'APPROVE';
1073 l_forward_to_id NUMBER:='';
1074 l_document_id NUMBER;
1075 l_document_type VARCHAR2(25):='';
1076 l_document_subtype VARCHAR2(25):='';
1077 l_return_code NUMBER;
1078 l_result BOOLEAN:=FALSE;
1079 l_note VARCHAR2(4000);
1080
1081 l_doc_string varchar2(200);
1082 l_preparer_user_name varchar2(100);
1083
1084 l_org_id number;
1085 l_current_approver number;
1086
1087 BEGIN
1088
1089 l_progress := 'Update_Action_History_Approve: 001';
1090 IF (g_po_wf_debug = 'Y') THEN
1091 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1092 END IF;
1093
1094 IF (funcmode='RUN') THEN
1095
1096 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
1097 itemkey=>itemkey,
1098 aname=>'APPROVER_EMPID');
1099
1100 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1101 itemkey => itemkey,
1102 aname => 'DOCUMENT_ID');
1103
1104 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1105 itemkey => itemkey,
1106 aname => 'DOCUMENT_TYPE');
1107
1108 l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1109 itemkey => itemkey,
1110 aname => 'DOCUMENT_SUBTYPE');
1111
1112 l_note := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1113 itemkey => itemkey,
1114 aname => 'NOTE');
1115
1116 -- Set the multi-org context
1117 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1118 itemkey => itemkey,
1119 aname => 'ORG_ID');
1120
1121 IF l_org_id is NOT NULL THEN
1122 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1123 END IF;
1124
1125 l_progress := 'Update_Action_History_Approve: 002-'|| to_char(l_document_id)||'-'||
1126 l_document_type||'-'||l_document_subtype;
1127 IF (g_po_wf_debug = 'Y') THEN
1128 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1129 END IF;
1130
1131 UpdateActionHistory(l_document_id, l_action,
1132 l_note, l_current_approver);
1133
1134 /*This attribute is set to be used in POR_AME_REQ_WF_PVT.Process_Beat_By_First.
1135 Based on this value the flow is determined whether approval or rejection*/
1136 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
1137 itemkey => itemkey,
1138 aname => 'APPROVER_RESPONSE',
1139 avalue => 'APPROVED' );
1140
1141 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1142
1143 return;
1144
1145 END IF; -- run mode
1146
1147 l_progress := 'Update_Action_History_Approve: 003';
1148 IF (g_po_wf_debug = 'Y') THEN
1149 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1150 END IF;
1151
1152 EXCEPTION
1153 WHEN OTHERS THEN
1154 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1155 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1156 wf_core.context('POR_AME_RCO_WF_PVT','Update_Action_History_Approve',l_progress,sqlerrm);
1157 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_RCO_WF_PVT.UPDATE_ACTION_HISTORY_APPROVE');
1158 RAISE;
1159
1160 END Update_Action_History_Approve;
1161
1162
1163 --------------------------------------------------------------------------------
1164 --Start of Comments
1165 --Name: Update_Action_History_Reject
1166 --Pre-reqs:
1167 -- None.
1168 --Modifies:
1169 -- None.
1170 --Locks:
1171 -- None.
1172 --Function:
1173 -- Workflow activity PL/SQL handler.
1174 -- This procedure updates the po_action_history table based on the approvers response.
1175 --Parameters:
1176 --IN:
1177 -- Standard workflow IN parameters
1178 --OUT:
1179 -- Standard workflow OUT parameters
1180 --Testing:
1181 --
1182 --End of Comments
1183 -------------------------------------------------------------------------------
1184 procedure Update_Action_History_Reject(itemtype in varchar2,
1185 itemkey in varchar2,
1186 actid in number,
1187 funcmode in varchar2,
1188 resultout out NOCOPY varchar2) IS
1189
1190 l_progress VARCHAR2(100) := '000';
1191 l_action VARCHAR2(30) := 'REJECT';
1192 l_forward_to_id NUMBER:='';
1193 l_document_id NUMBER;
1194 l_document_type VARCHAR2(25):='';
1195 l_document_subtype VARCHAR2(25):='';
1196 l_return_code NUMBER;
1197 l_result BOOLEAN:=FALSE;
1198 l_note VARCHAR2(4000);
1199
1200 l_doc_string varchar2(200);
1201 l_preparer_user_name varchar2(100);
1202
1203 l_org_id number;
1204 l_current_approver number;
1205
1206 BEGIN
1207
1208 l_progress := 'Update_Action_History_Reject: 001';
1209 IF (g_po_wf_debug = 'Y') THEN
1210 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1211 END IF;
1212
1213 IF (funcmode='RUN') THEN
1214
1215 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
1216 itemkey=>itemkey,
1217 aname=>'APPROVER_EMPID');
1218
1219 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1220 itemkey => itemkey,
1221 aname => 'DOCUMENT_ID');
1222
1223 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1224 itemkey => itemkey,
1225 aname => 'DOCUMENT_TYPE');
1226
1227 l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1228 itemkey => itemkey,
1229 aname => 'DOCUMENT_SUBTYPE');
1230
1231 l_note := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1232 itemkey => itemkey,
1233 aname => 'NOTE');
1234
1235 -- Set the multi-org context
1236 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1237 itemkey => itemkey,
1238 aname => 'ORG_ID');
1239
1240 IF l_org_id is NOT NULL THEN
1241 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1242 END IF;
1243
1244 l_progress := 'Update_Action_History_Reject: 002-'|| to_char(l_document_id)||'-'||
1245 l_document_type||'-'||l_document_subtype;
1246 IF (g_po_wf_debug = 'Y') THEN
1247 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1248 END IF;
1249
1250 UpdateActionHistory(l_document_id, l_action,
1251 l_note, l_current_approver);
1252
1253 /*This attribute is set to be used in POR_AME_REQ_WF_PVT.Process_Beat_By_First.
1254 Based on this value the flow is determined whether approval or rejection*/
1255 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
1256 itemkey => itemkey,
1257 aname => 'APPROVER_RESPONSE',
1258 avalue => 'REJECTED' );
1259
1260 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1261
1262 return;
1263
1264 END IF; -- run mode
1265
1266 l_progress := 'Update_Action_History_Reject: 003';
1267 IF (g_po_wf_debug = 'Y') THEN
1268 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1269 END IF;
1270
1271 EXCEPTION
1272 WHEN OTHERS THEN
1273 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1274 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1275 wf_core.context('POR_AME_RCO_WF_PVT','Update_Action_History_Reject',l_progress,sqlerrm);
1276 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_RCO_WF_PVT.Update_Action_History_Reject');
1277 RAISE;
1278
1279 END Update_Action_History_Reject;
1280
1281 --------------------------------------------------------------------------------
1282 --Start of Comments
1283 --Name: Update_Action_History_Timeout
1284 --Pre-reqs:
1285 -- None.
1286 --Modifies:
1287 -- None.
1288 --Locks:
1289 -- None.
1290 --Function:
1291 -- Workflow activity PL/SQL handler.
1292 -- This procedure updates the po_action_history table based on the approvers response.
1293 --Parameters:
1294 --IN:
1295 -- Standard workflow IN parameters
1296 --OUT:
1297 -- Standard workflow OUT parameters
1298 --Testing:
1299 --
1300 --End of Comments
1301 -------------------------------------------------------------------------------
1302 procedure Update_Action_History_Timeout(itemtype in varchar2,
1303 itemkey in varchar2,
1304 actid in number,
1305 funcmode in varchar2,
1306 resultout out NOCOPY varchar2) IS
1307
1308 l_progress VARCHAR2(100) := '000';
1309 l_action VARCHAR2(30) := 'NO ACTION';
1310 l_forward_to_id NUMBER:='';
1311 l_document_id NUMBER;
1312 l_document_type VARCHAR2(25):='';
1313 l_document_subtype VARCHAR2(25):='';
1314 l_return_code NUMBER;
1315 l_result BOOLEAN:=FALSE;
1316 l_note VARCHAR2(4000);
1317
1318 l_doc_string varchar2(200);
1319 l_preparer_user_name varchar2(100);
1320
1321 l_org_id number;
1322 l_current_approver number;
1323
1324 BEGIN
1325
1326 l_progress := 'Update_Action_History_Timeout: 001';
1327 IF (g_po_wf_debug = 'Y') THEN
1328 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1329 END IF;
1330
1331 IF (funcmode='RUN') THEN
1332
1333 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
1334 itemkey=>itemkey,
1335 aname=>'APPROVER_EMPID');
1336
1337 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1338 itemkey => itemkey,
1339 aname => 'DOCUMENT_ID');
1340
1341 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1342 itemkey => itemkey,
1343 aname => 'DOCUMENT_TYPE');
1344
1345 l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1346 itemkey => itemkey,
1347 aname => 'DOCUMENT_SUBTYPE');
1348
1349 l_note := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1350 itemkey => itemkey,
1351 aname => 'NOTE');
1352
1353 -- Set the multi-org context
1354 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1355 itemkey => itemkey,
1356 aname => 'ORG_ID');
1357
1358 IF l_org_id is NOT NULL THEN
1359 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1360 END IF;
1361
1362 l_progress := 'Update_Action_History_Timeout: 002-'|| to_char(l_document_id)||'-'||
1363 l_document_type||'-'||l_document_subtype;
1364 IF (g_po_wf_debug = 'Y') THEN
1365 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1366 END IF;
1367
1368 UpdateActionHistory(l_document_id, l_action,
1369 l_note, l_current_approver);
1370
1371 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1372
1373 return;
1374
1375 END IF; -- run mode
1376
1377 l_progress := 'Update_Action_History_Timeout: 003';
1378 IF (g_po_wf_debug = 'Y') THEN
1379 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1380 END IF;
1381
1382 EXCEPTION
1383 WHEN OTHERS THEN
1384 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1385 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1386 wf_core.context('POR_AME_REQ_WF_PVT','Update_Action_History_Timeout',l_progress,sqlerrm);
1387 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');
1388 RAISE;
1389
1390 END Update_Action_History_Timeout;
1391
1392 --------------------------------------------------------------------------------
1393 --Start of Comments
1394 --Name: Set_Rco_Stat_Approved
1395 --Pre-reqs:
1396 -- None.
1397 --Modifies:
1398 -- None.
1399 --Locks:
1400 -- None.
1401 --Function:
1402 -- Workflow activity PL/SQL handler.
1403 -- This procedure updates the req approval status in po_requistion_headers_all table.
1404 -- po_action_history table also will be updated based on the approvers response.
1405 -- If it is a first responder wins setup in ame, then once the first reponder wins, others will not be able to take decisions.
1406 --Parameters:
1407 --IN:
1408 -- Standard workflow IN parameters
1409 --OUT:
1410 -- Standard workflow OUT parameters
1411 --Testing:
1412 --
1413 --End of Comments
1414 -------------------------------------------------------------------------------
1415 procedure Set_Rco_Stat_Approved( itemtype in varchar2,
1416 itemkey in varchar2,
1417 actid in number,
1418 funcmode in varchar2,
1419 resultout out NOCOPY varchar2 ) is
1420
1421 l_doc_header_id NUMBER;
1422 l_po_header_id NUMBER;
1423 l_doc_type VARCHAR2(14);
1424 l_note VARCHAR2(4000);
1425 x_progress varchar2(500);
1426
1427 l_doc_string varchar2(200);
1428 l_preparer_user_name varchar2(100);
1429
1430 BEGIN
1431
1432 x_progress := 'POR_AME_RCO_WF_PVT.Set_Rco_Stat_Approved: 01';
1433
1434 if (funcmode <> wf_engine.eng_run) then
1435 resultout := wf_engine.eng_null;
1436 return;
1437 end if;
1438
1439 l_doc_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1440 itemkey => itemkey,
1441 aname => 'DOCUMENT_ID');
1442
1443 l_doc_type := wf_engine.GetItemAttrText( itemtype => itemtype,
1444 itemkey => itemkey,
1445 aname => 'DOCUMENT_TYPE');
1446
1447 x_progress := 'POR_AME_RCO_WF_PVT.Set_Rco_Stat_Approved: 02 - l_doc_header_id ' || l_doc_header_id || ' -- l_doc_type :' || l_doc_type ;
1448 IF (g_po_wf_debug = 'Y') THEN
1449 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1450 END IF;
1451
1452 -- If the setup is first responder wins, then once the first approver responds, the others will not be able to take decisions.
1453
1454 UpdateActionHistory(l_doc_header_id, 'NO ACTION',
1455 fnd_message.get_string('ICX', 'ICX_POR_REQ_ALREADY_APPROVED'), NULL);
1456
1457 wf_engine.SetItemAttrText( itemtype => itemtype,
1458 itemkey => itemkey,
1459 aname => 'AUTHORIZATION_STATUS',
1460 avalue => 'APPROVED');
1461
1462 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
1463
1464 x_progress := 'POR_AME_RCO_WF_PVT.Set_Rco_Stat_Approved: 03';
1465 IF (g_po_wf_debug = 'Y') THEN
1466 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1467 END IF;
1468
1469 EXCEPTION
1470 WHEN OTHERS THEN
1471 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1472 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1473 wf_core.context('POR_AME_RCO_WF_PVT','Set_Rco_Stat_Approved',x_progress);
1474 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_RCO_WF_PVT.Set_Rco_Stat_Approved');
1475 raise;
1476 END Set_Rco_Stat_Approved;
1477
1478
1479 --------------------------------------------------------------------------------
1480 --Start of Comments
1481 --Name: Set_Rco_Stat_Rejected
1482 --Pre-reqs:
1483 -- None.
1484 --Modifies:
1485 -- None.
1486 --Locks:
1487 -- None.
1488 --Function:
1489 -- Workflow activity PL/SQL handler.
1490 -- This procedure updates the req approval status in po_requistion_headers_all table.
1491 -- po_action_history table also will be updated based on the approvers response.
1492 -- If it is a first responder wins setup in ame, then once the first reponder wins, others will not be able to take decisions.
1493 --Parameters:
1494 --IN:
1495 -- Standard workflow IN parameters
1496 --OUT:
1497 -- Standard workflow OUT parameters
1498 --Testing:
1499 --
1500 --End of Comments
1501 -------------------------------------------------------------------------------
1502 procedure Set_Rco_Stat_Rejected( itemtype in varchar2,
1503 itemkey in varchar2,
1504 actid in number,
1505 funcmode in varchar2,
1506 resultout out NOCOPY varchar2 ) is
1507
1508 l_doc_header_id NUMBER;
1509 l_po_header_id NUMBER;
1510 l_doc_type VARCHAR2(14);
1511 l_note VARCHAR2(4000);
1512 x_progress varchar2(500);
1513
1514 l_doc_string varchar2(200);
1515 l_preparer_user_name varchar2(100);
1516
1517 BEGIN
1518
1519 x_progress := 'POR_AME_RCO_WF_PVT.Set_Rco_Stat_Rejected: 01';
1520
1521 if (funcmode <> wf_engine.eng_run) then
1522 resultout := wf_engine.eng_null;
1523 return;
1524 end if;
1525
1526 l_doc_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1527 itemkey => itemkey,
1528 aname => 'DOCUMENT_ID');
1529
1530 x_progress := 'POR_AME_RCO_WF_PVT.Set_Rco_Stat_Rejected: 02 - l_doc_header_id ' || l_doc_header_id;
1531 IF (g_po_wf_debug = 'Y') THEN
1532 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1533 END IF;
1534
1535 -- If the setup is first responder wins, then once the first approver responds, the others will not be able to take decisions.
1536
1537 UpdateActionHistory(l_doc_header_id, 'NO ACTION',
1538 fnd_message.get_string('ICX', 'ICX_POR_REQ_ALREADY_REJECTED'), NULL);
1539
1540 wf_engine.SetItemAttrText( itemtype => itemtype,
1541 itemkey => itemkey,
1542 aname => 'AUTHORIZATION_STATUS',
1543 avalue => 'REJECTED');
1544
1545 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
1546
1547 x_progress := 'POR_AME_RCO_WF_PVT.Set_Rco_Stat_Rejected: 03';
1548 IF (g_po_wf_debug = 'Y') THEN
1549 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1550 END IF;
1551
1552 EXCEPTION
1553 WHEN OTHERS THEN
1554 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1555 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1556 wf_core.context('POR_AME_RCO_WF_PVT','Set_Rco_Stat_Rejected',x_progress);
1557 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_RCO_WF_PVT.Set_Rco_Stat_Rejected');
1558 raise;
1559 END Set_Rco_Stat_Rejected;
1560
1561 --------------------------------------------------------------------------------
1562 --Start of Comments
1563 --Name: UpdateActionHistory
1564 --Pre-reqs:
1565 -- None.
1566 --Modifies:
1567 -- None.
1568 --Locks:
1569 -- None.
1570 --Function:
1571 -- This procedure updates the po_action_history table based on the approvers response.
1572 --Parameters:
1573 --IN:
1574 -- p_document_id : Requisition Header Id
1575 -- p_action : Action
1576 -- p_note : Notes
1577 -- p_current_approver: Approver person Id
1578 --OUT:
1579 --
1580 --End of Comments
1581 -------------------------------------------------------------------------------
1582 PROCEDURE UpdateActionHistory(p_document_id NUMBER,
1583 p_action VARCHAR2,
1584 p_note VARCHAR2,
1585 p_current_approver NUMBER) IS
1586
1587 pragma AUTONOMOUS_TRANSACTION;
1588
1589 BEGIN
1590
1591 if (p_current_approver is not null) then
1592
1593 UPDATE po_action_history
1594 SET action_code = p_action,
1595 note = p_note,
1596 action_date = sysdate
1597 WHERE object_id = p_document_id and
1598 employee_id = p_current_approver and
1599 action_code is null and
1600 object_type_code = 'REQUISITION'
1601 and rownum=1;
1602
1603 else
1604
1605 UPDATE po_action_history
1606 SET action_code = p_action,
1607 note = p_note,
1608 action_date = sysdate
1609 WHERE object_id = p_document_id and
1610 action_code is null and
1611 object_type_code = 'REQUISITION';
1612 end if;
1613
1614 COMMIT;
1615
1616 EXCEPTION
1617
1618 WHEN OTHERS THEN
1619 RAISE;
1620
1621 END UpdateActionHistory;
1622
1623 --------------------------------------------------------------------------------
1624 --Start of Comments
1625 --Name: position_has_valid_approvers
1626 --Pre-reqs:
1627 -- None.
1628 --Modifies:
1629 -- None.
1630 --Locks:
1631 -- None.
1632 --Function:
1633 -- Workflow activity PL/SQL handler.
1634 -- This function is used to check whether to launch the parallel approval process or not.
1635 -- If a position does not have any users, then this function will return 'N', otherwise return 'Y'
1636 --Parameters:
1637 --IN:
1638 -- documentId : ReqHeaderId
1639 -- documentType : AME Transaction Type
1640 --OUT:
1641 -- 'Y' We can launch the parallel approval process.
1642 -- 'N' Invalid approver. We can not launch the parallel approval process.
1643 -- 'NO_USERS' No users for position. This AME record will be deleted. Go to the next approver record.
1644 --Testing:
1645 --
1646 --End of Comments
1647 -------------------------------------------------------------------------------
1648 FUNCTION position_has_valid_approvers( documentId NUMBER, documentType VARCHAR2 )RETURN VARCHAR2 is
1649
1650 l_next_approver_id number;
1651 l_next_approver_name per_employees_current_x.full_name%TYPE;
1652 l_position_has_valid_approvers VARCHAR2(10);
1653 l_approver_index NUMBER;
1654 l_error_message varchar2(3000);
1655
1656 l_first_approver_id NUMBER;
1657 l_first_position_id NUMBER;
1658
1659 BEGIN
1660
1661 l_position_has_valid_approvers := 'Y';
1662 l_approver_index := g_next_approvers.first();
1663
1664 select first_position_id, first_approver_id
1665 into l_first_position_id, l_first_approver_id
1666 from po_requisition_headers_all
1667 where documentId = requisition_header_id;
1668
1669 while( l_approver_index is not null ) loop
1670 l_position_has_valid_approvers := 'Y';
1671 if (g_next_approvers(l_approver_index).orig_system = 'POS') then
1672
1673 BEGIN
1674
1675 if (l_first_position_id is not NULL AND l_first_position_id=g_next_approvers(l_approver_index).orig_system_id) then
1676
1677 l_next_approver_id := l_first_approver_id;
1678
1679 SELECT full_name
1680 INTO l_next_approver_name
1681 FROM per_all_people_f person
1682 WHERE person_id = l_first_approver_id;
1683
1684 else
1685
1686 /* find the persond id from the position_id*/
1687 SELECT person_id, full_name into l_next_approver_id,l_next_approver_name FROM (
1688 SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
1689 WHERE asg.position_id = g_next_approvers(l_approver_index).orig_system_id and trunc(sysdate) between person.effective_start_date
1690 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
1691 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
1692 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
1693 and asg.assignment_status_type_id not in (
1694 SELECT assignment_status_type_id FROM per_assignment_status_types
1695 WHERE per_system_status = 'TERM_ASSIGN'
1696 ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
1697 ) where rownum = 1;
1698
1699 end if;
1700
1701 EXCEPTION
1702 WHEN NO_DATA_FOUND THEN
1703
1704 -- No users for this position. Check whether this is last position or not.
1705 -- If this is last position then return the req to imcomplete status.
1706 -- Otherwise set this approver record to 'Approved'
1707 if( is_last_approver_record(documentId, documentType, g_next_approvers(l_approver_index)) = 'Y' )then
1708 return 'N';
1709 else
1710
1711 /*
1712 g_next_approvers(l_approver_index).approval_status := ame_util.noResponseStatus;
1713 -- Update the Approval status with the response from the approver.
1714 ame_api2.updateApprovalStatus( applicationIdIn =>applicationId,
1715 transactionIdIn =>documentId,
1716 transactionTypeIn =>documentType,
1717 approverIn => g_next_approvers(l_approver_index)
1718 );
1719 */
1720
1721 ame_api3.suppressApprover( applicationIdIn => applicationId,
1722 transactionIdIn => documentId,
1723 approverIn => g_next_approvers(l_approver_index),
1724 transactionTypeIn => documentType
1725 );
1726
1727 -- remove this approver from the global list.
1728 g_next_approvers.delete(l_approver_index);
1729 l_position_has_valid_approvers := 'NO_USERS';
1730
1731 end if;
1732 END;
1733 end if;
1734 l_approver_index := g_next_approvers.next(l_approver_index);
1735 end loop;
1736 return l_position_has_valid_approvers;
1737
1738 EXCEPTION
1739 WHEN OTHERS THEN
1740 return 'N';
1741 END position_has_valid_approvers;
1742
1743
1744 --------------------------------------------------------------------------------
1745 --Start of Comments
1746 --Name: is_last_approver_record
1747 --Pre-reqs:
1748 -- None.
1749 --Modifies:
1750 -- None.
1751 --Locks:
1752 -- None.
1753 --Function:
1754 -- Workflow activity PL/SQL handler.
1755 -- This function is used to check whether the approver/position is last in the approval chain or not
1756 -- This function will be invoked only if a particular position does not have any associated users.
1757 -- If this function returns 'Y', then the req will be put back in incomplete status.
1758 --Parameters:
1759 --IN:
1760 -- documentId : ReqHeaderId
1761 -- documentType : AME Transaction Type
1762 --OUT:
1763 -- 'Y' The approver/position is last in the approval chain.
1764 -- 'N' The approver/position is not last in the approval chain
1765 --Testing:
1766 --
1767 --End of Comments
1768 -------------------------------------------------------------------------------
1769 Function is_last_approver_record( documentId NUMBER, documentType VARCHAR2, approverRecord in ame_util.approverRecord2 ) RETURN VARCHAR2 is
1770
1771 l_is_last_approver_record VARCHAR2(1);
1772 l_total_approver_count NUMBER;
1773 l_current_approver_index NUMBER;
1774 tmpApproverList ame_util.approversTable2;
1775 l_process_out VARCHAR2(10);
1776
1777 BEGIN
1778
1779 ame_api2.getAllApprovers7( applicationIdIn =>applicationId,
1780 transactionIdIn =>documentId,
1781 transactionTypeIn =>documentType,
1782 approvalProcessCompleteYNOut => l_process_out,
1783 approversOut =>tmpApproverList
1784 );
1785
1786 l_total_approver_count := tmpApproverList.count;
1787 l_current_approver_index := 0;
1788
1789 for i in 1 .. tmpApproverList.count loop
1790
1791 l_current_approver_index := i;
1792 if ( tmpApproverList(i).name = approverRecord.name AND
1793 tmpApproverList(i).orig_system = approverRecord.orig_system AND
1794 tmpApproverList(i).orig_system_id = approverRecord.orig_system_id AND
1795 tmpApproverList(i).authority = approverRecord.authority AND
1796 tmpApproverList(i).group_or_chain_id = approverRecord.group_or_chain_id AND
1797 tmpApproverList(i).action_type_id = approverRecord.action_type_id AND
1798 tmpApproverList(i).item_id = approverRecord.item_id AND
1799 tmpApproverList(i).item_class = approverRecord.item_class AND
1800 tmpApproverList(i).approver_category = approverRecord.approver_category
1801 ) then
1802
1803 EXIT;
1804 end if;
1805 end loop;
1806
1807 if( l_current_approver_index = l_total_approver_count ) then
1808 return 'Y';
1809 else
1810 return 'N';
1811 end if;
1812
1813 EXCEPTION
1814 WHEN OTHERS THEN
1815 return 'Y';
1816 END is_last_approver_record;
1817
1818
1819 END POR_AME_RCO_WF_PVT;