[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.11 2011/01/19 09:34:12 vsdesai 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 AND Trunc(SYSDATE) BETWEEN Effective_Start_Date AND Effective_End_Date;
415
416 else
417
418 /* find the persond id from the position_id*/
419 SELECT person_id, full_name into l_next_approver_id,l_next_approver_name FROM (
420 SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
421 WHERE asg.position_id = g_next_approvers(l_approver_index).orig_system_id and trunc(sysdate) between person.effective_start_date
422 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
423 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
424 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
425 and asg.assignment_status_type_id not in (
426 SELECT assignment_status_type_id FROM per_assignment_status_types
427 WHERE per_system_status = 'TERM_ASSIGN'
428 ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
429 ) where rownum = 1;
430
431 end if;
432 exception
433 WHEN NO_DATA_FOUND THEN
434 RAISE;
435 END;
436
437 elsif (g_next_approvers(l_approver_index).orig_system = 'FND') then
438 SELECT employee_id
439 into l_next_approver_id
440 FROM fnd_user
441 WHERE user_id = g_next_approvers(l_approver_index).orig_system_id
442 and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
443 end if;
444
445 t_varname(19) := 'AME_APPROVER_TYPE';
446 t_varval(19) := g_next_approvers(l_approver_index).orig_system;
447
448
449 WF_DIRECTORY.GetUserName('PER', l_next_approver_id, l_next_approver_user_name, l_next_approver_disp_name);
450
451 l_progress := 'Launch_Parallel_Approval: 004 -- Next Approver User Name -- display Name:' || l_next_approver_user_name || ' -- ' || l_next_approver_disp_name;
452 IF (g_po_wf_debug = 'Y') THEN
453 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
454 END IF;
455
456 t_varname(20) := 'APPROVER_USER_NAME';
457 t_varval(20) := l_next_approver_user_name;
458
459 t_varname(21) := 'APPROVER_DISPLAY_NAME';
460 t_varval(21) := l_next_approver_disp_name;
461
462 /* Kick off the process */
463 l_progress:= '30: start_wf_line_process: Kicking off StartProcess';
464 IF (g_po_wf_debug = 'Y') THEN
465 po_wf_debug_pkg.insert_debug(itemtype,itemkey,l_progress);
466 END IF;
467
468 t_varname(22) := 'RCO_AME_IS_FYI_APPROVER';
469 if (g_next_approvers(l_approver_index).approver_category = ame_util.fyiApproverCategory) then
470 t_varval(22) :='Y';
471 else
472 t_varval(22) :='N';
473 l_start_block_activity := 'Y';
474 end if;
475
476 -- Set the item attributes.
477 Wf_Engine.SetItemAttrTextArray(itemtype, l_item_key,t_varname,t_varval);
478
479 n_varname(1) := 'DOCUMENT_ID';
480 n_varval(1) := l_document_id;
481
482 n_varname(2) := 'ORG_ID';
483 n_varval(2) := l_org_id;
484
485 n_varname(3) := 'AME_APPROVER_ID';
486 n_varval(3) := g_next_approvers(l_approver_index).orig_system_id;
487
488 n_varname(4) := 'APPROVER_EMPID';
489 n_varval(4) := l_next_approver_id;
490
491 n_varname(5) := 'APPROVAL_GROUP_ID';
492 n_varval(5) := g_next_approvers(l_approver_index).group_or_chain_id;
493
494 n_varname(6) := 'CHANGE_REQUEST_GROUP_ID';
495 n_varval(6) := l_change_request_group_id;
496
497 Wf_Engine.SetItemAttrNumberArray(itemtype, l_item_key,n_varname,n_varval);
498
499 l_progress := 'Launch_Parallel_Approval: 005 -- Launch Parallel Approval';
500 IF (g_po_wf_debug = 'Y') THEN
501 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
502 END IF;
503 wf_engine.StartProcess( itemtype => itemtype,
504 itemkey => l_item_key );
505
506 l_approver_index := g_next_approvers.next(l_approver_index);
507
508 end loop; -- end of for loop.
509
510 if l_start_block_activity = 'Y' then
511 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
512 else
513 resultout:='COMPLETE' || ':' || '';
514 end if;
515 g_next_approvers.delete;
516
517 RETURN;
518
519 END IF; --run mode
520
521 exception
522 when NO_DATA_FOUND then
523 l_progress:= '50: start_wf_line_process: NO_DATA_FOUND -- EXCEPTION';
524 l_doc_string := l_progress || PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
525 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
526 wf_core.context('POR_AME_RCO_WF_PVT','Launch_Parallel_Approval',l_progress,sqlerrm);
527
528 IF (g_po_wf_debug = 'Y') THEN
529 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );
530 END IF;
531 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_RCO_WF_PVT.GET_NEXT_APPROVER');
532 raise;
533 when others then
534 l_progress:= '50: start_wf_line_process: IN EXCEPTION';
535 l_doc_string := l_progress || PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
536 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
537 wf_core.context('POR_AME_RCO_WF_PVT','Launch_Parallel_Approval',l_progress,sqlerrm);
538
539 IF (g_po_wf_debug = 'Y') THEN
540 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );
541 END IF;
542
543 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_RCO_WF_PVT.GET_NEXT_APPROVER');
544 raise;
545
546 end Launch_Parallel_Approval;
547
548
549 --------------------------------------------------------------------------------
550 --Start of Comments
551 --Name: Process_Response_Internal
552 --Pre-reqs:
553 -- None.
554 --Modifies:
555 -- None.
556 --Locks:
557 -- None.
558 --Function:
559 -- Workflow activity PL/SQL handler. This procedure is used to inform AME about the approvers response.
560 --Parameters:
561 --IN:
562 -- Standard workflow IN parameters
563 --OUT:
564 -- Standard workflow OUT parameters
565 --Testing:
566 --
567 --End of Comments
568 -------------------------------------------------------------------------------
569 procedure Process_Response_Internal( itemtype in varchar2,
570 itemkey in varchar2,
571 p_response in varchar2 ) IS
572
573 l_progress VARCHAR2(500) := '000';
574 l_document_id number;
575 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
576 l_current_approver ame_util.approverRecord2;
577 l_approver_posoition_id number;
578 l_approver_type varchar2(10);
579
580 begin
581
582 l_progress := 'Process_Response_Internal: 001';
583 IF (g_po_wf_debug = 'Y') THEN
584 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
585 END IF;
586
587 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
588 itemkey => itemkey,
589 aname => 'DOCUMENT_ID');
590
591 l_transaction_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
592 itemkey => itemkey,
593 aname => 'AME_TRANSACTION_TYPE');
594
595 l_approver_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
596 itemkey => itemkey,
597 aname => 'AME_APPROVER_TYPE');
598
599 l_progress := 'Process_Response_Internal: 002 -- l_approver_type :' || l_approver_type ;
600 IF (g_po_wf_debug = 'Y') THEN
601 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
602 END IF;
603
604 if (l_approver_type = 'POS') then
605 l_current_approver.orig_system := 'POS';
606 elsif (l_approver_type = 'FND') then
607 l_current_approver.orig_system := 'FND';
608 else
609 l_current_approver.orig_system := 'PER';
610 l_current_approver.name := po_wf_util_pkg.GetItemAttrText( itemtype => itemType,
611 itemkey => itemkey,
612 aname => 'APPROVER_USER_NAME');
613 end if;
614
615 l_current_approver.orig_system_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
616 itemkey => itemkey,
617 aname => 'AME_APPROVER_ID');
618
619 l_progress := 'Process_Response_Internal: 003 -- l_current_approver.orig_system_id :' || l_current_approver.orig_system_id ;
620 IF (g_po_wf_debug = 'Y') THEN
621 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
622 END IF;
623
624 if( p_response = 'APPROVE') then
625 l_current_approver.approval_status := ame_util.approvedStatus;
626 elsif( p_response = 'REJECT') then
627 l_current_approver.approval_status := ame_util.rejectStatus;
628 elsif( p_response = 'TIMEOUT') then
629 l_current_approver.approval_status := ame_util.noResponseStatus;
630 end if;
631
632 l_progress := 'Process_Response_Internal: 004 -- p_response :' || p_response ;
633 IF (g_po_wf_debug = 'Y') THEN
634 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
635 END IF;
636
637 -- Get the name value for the approverRecord2.
638 -- This is a mandatory field. If we do not pass this value to AME, we will get invalid parameter exception.
639 -- bug# 4936145
640 IF l_current_approver.name IS NULL THEN
641 SELECT name into l_current_approver.name FROM
642 ( SELECT name FROM wf_roles WHERE orig_system = l_current_approver.orig_system
643 and orig_system_id = l_current_approver.orig_system_id
644 order by start_date
645 )
646 WHERE rownum = 1;
647 END IF;
648
649 IF l_current_approver.name IS NULL THEN
650 raise_application_error(-20001, 'Record Not Found in WF_ROLES for the orig_system_id :' ||
651 l_current_approver.orig_system_id || ' -- orig_system :' || l_current_approver.orig_system );
652 END IF;
653
654 -- Update the Approval status with the response from the approver.
655 ame_api2.updateApprovalStatus( applicationIdIn=>applicationId,
656 transactionIdIn=>l_document_id,
657 transactionTypeIn=>l_transaction_type,
658 approverIn => l_current_approver);
659
660 exception
661 when others then
662 raise;
663 end Process_Response_Internal;
664
665
666 --------------------------------------------------------------------------------
667 --Start of Comments
668 --Name: Process_Response_Approve
669 --Pre-reqs:
670 -- None.
671 --Modifies:
672 -- None.
673 --Locks:
674 -- None.
675 --Function:
676 -- Workflow activity PL/SQL handler.
677 -- This procedure is the wrapper procedure of Process_Response_Internal()
678 --Parameters:
679 --IN:
680 -- Standard workflow IN parameters
681 --OUT:
682 -- Standard workflow OUT parameters
683 --Testing:
684 --
685 --End of Comments
686 -------------------------------------------------------------------------------
687 procedure Process_Response_Approve( itemtype in varchar2,
688 itemkey in varchar2,
689 actid in number,
690 funcmode in varchar2,
691 resultout out NOCOPY varchar2) IS
692
693 l_progress VARCHAR2(500) := '000';
694 l_parent_item_type wf_items.parent_item_type%TYPE;
695 l_parent_item_key wf_items.parent_item_key%TYPE;
696
697 begin
698
699 l_progress := 'Process_Response_Approve: 001';
700 IF (g_po_wf_debug = 'Y') THEN
701 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
702 END IF;
703
704 SELECT parent_item_type, parent_item_key
705 into l_parent_item_type, l_parent_item_key
706 FROM wf_items
707 WHERE item_type = itemtype and item_key = itemkey;
708
709 Process_Response_Internal(itemtype, itemkey, 'APPROVE');
710
711 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
712 itemkey => itemkey,
713 aname => 'RCO_AME_SUB_APPROVAL_RESPONSE',
714 avalue => 'APPROVE');
715
716 l_progress := 'Process_Response_Approve: 002 -- Completing the BLOCK activity for the APPROVED notification.';
717 IF (g_po_wf_debug = 'Y') THEN
718 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
719 END IF;
720
721 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
722 RETURN;
723 end Process_Response_Approve;
724
725 --------------------------------------------------------------------------------
726 --Start of Comments
727 --Name: Process_Response_Reject
728 --Pre-reqs:
729 -- None.
730 --Modifies:
731 -- None.
732 --Locks:
733 -- None.
734 --Function:
735 -- Workflow activity PL/SQL handler.
736 -- This procedure is the wrapper procedure of Process_Response_Internal()
737 --Parameters:
738 --IN:
739 -- Standard workflow IN parameters
740 --OUT:
741 -- Standard workflow OUT parameters
742 --Testing:
743 --
744 --End of Comments
745 -------------------------------------------------------------------------------
746 procedure Process_Response_Reject( itemtype in varchar2,
747 itemkey in varchar2,
748 actid in number,
749 funcmode in varchar2,
750 resultout out NOCOPY varchar2) IS
751
752 l_progress VARCHAR2(500) := '000';
753 l_parent_item_type wf_items.parent_item_type%TYPE;
754 l_parent_item_key wf_items.parent_item_key%TYPE;
755
756 begin
757
758 l_progress := 'Process_Response_Reject: 001';
759 IF (g_po_wf_debug = 'Y') THEN
760 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
761 END IF;
762
763 Process_Response_Internal(itemtype, itemkey, 'REJECT');
764
765 SELECT parent_item_type, parent_item_key
766 into l_parent_item_type, l_parent_item_key
767 FROM wf_items
768 WHERE item_type = itemtype and item_key = itemkey;
769
770 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
771 itemkey => l_parent_item_key,
772 aname => 'RCO_AME_SUB_APPROVAL_RESPONSE',
773 avalue => 'REJECT');
774
775 l_progress := 'Process_Response_Reject: 002 -- Completing the BLOCK activity for the REJECTED notification.';
776 IF (g_po_wf_debug = 'Y') THEN
777 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
778 END IF;
779
780 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
781 RETURN;
782
783 end Process_Response_Reject;
784
785 --------------------------------------------------------------------------------
786 --Start of Comments
787 --Name: Process_Response_Timeout
788 --Pre-reqs:
789 -- None.
790 --Modifies:
791 -- None.
792 --Locks:
793 -- None.
794 --Function:
795 -- Workflow activity PL/SQL handler.
796 -- This procedure is the wrapper procedure of Process_Response_Internal()
797 --Parameters:
798 --IN:
799 -- Standard workflow IN parameters
800 --OUT:
801 -- Standard workflow OUT parameters
802 --Testing:
803 --
804 --End of Comments
805 -------------------------------------------------------------------------------
806 procedure Process_Response_Timeout( itemtype in varchar2,
807 itemkey in varchar2,
808 actid in number,
809 funcmode in varchar2,
810 resultout out NOCOPY varchar2) IS
811
812 l_progress VARCHAR2(500) := '000';
813 l_parent_item_type wf_items.parent_item_type%TYPE;
814 l_parent_item_key wf_items.parent_item_key%TYPE;
815
816 begin
817
818 l_progress := 'Process_Response_Timeout: 001';
819 IF (g_po_wf_debug = 'Y') THEN
820 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
821 END IF;
822
823 Process_Response_Internal(itemtype, itemkey, 'TIMEOUT');
824
825 SELECT parent_item_type, parent_item_key
826 into l_parent_item_type, l_parent_item_key
827 FROM wf_items
828 WHERE item_type = itemtype and item_key = itemkey;
829
830 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
831 itemkey => l_parent_item_key,
832 aname => 'AME_SUB_APPROVAL_RESPONSE',
833 avalue => 'REJECT');
834
835 wf_engine.CompleteActivity (itemtype => l_parent_item_type,
836 itemkey => l_parent_item_key,
837 activity => 'BLOCK',
838 result => null);
839
840 l_progress := 'Process_Response_Timeout: 002 -- Completing the BLOCK activity for the REJECTED notification.';
841 IF (g_po_wf_debug = 'Y') THEN
842 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
843 END IF;
844
845 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
846 RETURN;
847
848 end Process_Response_Timeout;
849
850
851 --------------------------------------------------------------------------------
852 --Start of Comments
853 --Name: insertActionHistory
854 --Pre-reqs:
855 -- None.
856 --Modifies:
857 -- None.
858 --Locks:
859 -- None.
860 --Function:
861 -- Workflow activity PL/SQL handler.
862 -- This procedure is used to keep the history of each notification.
863 -- The inserted records will be displayed in Approval History page.
864 --Parameters:
865 --IN:
866 -- Requistion Header Id
867 -- Employee Id
868 --OUT:
869 -- Standard workflow OUT parameters
870 --Testing:
871 --
872 --End of Comments
873 -------------------------------------------------------------------------------
874 procedure insertActionHistory( p_req_header_id in number,
875 p_employee_id in number,
876 p_approval_group_id in number )
877
878 is
879 pragma AUTONOMOUS_TRANSACTION;
880
881 l_object_sub_type_code PO_ACTION_HISTORY.OBJECT_SUB_TYPE_CODE%TYPE;
882 l_sequence_num PO_ACTION_HISTORY.SEQUENCE_NUM%TYPE;
883 l_object_revision_num PO_ACTION_HISTORY.OBJECT_REVISION_NUM%TYPE;
884 l_approval_path_id PO_ACTION_HISTORY.APPROVAL_PATH_ID%TYPE;
885 l_request_id PO_ACTION_HISTORY.REQUEST_ID%TYPE;
886 l_program_application_id PO_ACTION_HISTORY.PROGRAM_APPLICATION_ID%TYPE;
887 l_program_date PO_ACTION_HISTORY.PROGRAM_DATE%TYPE;
888 l_program_id PO_ACTION_HISTORY.PROGRAM_ID%TYPE;
889 l_progress VARCHAR2(100) := '000';
890
891 begin
892
893 SELECT max(sequence_num)
894 INTO l_sequence_num
895 FROM PO_ACTION_HISTORY
896 WHERE object_type_code = 'REQUISITION'
897 AND object_id = p_req_header_id;
898
899 SELECT object_sub_type_code,
900 object_revision_num, approval_path_id, request_id,
901 program_application_id, program_date, program_id
902 INTO l_object_sub_type_code,
903 l_object_revision_num, l_approval_path_id, l_request_id,
904 l_program_application_id, l_program_date, l_program_id
905 FROM PO_ACTION_HISTORY
906 WHERE object_type_code = 'REQUISITION'
907 AND object_id = p_req_header_id
908 AND sequence_num = l_sequence_num;
909
910 /* update po action history */
911 po_forward_sv1.insert_action_history (
912 p_req_header_id,
913 'REQUISITION',
914 l_object_sub_type_code,
915 l_sequence_num + 1,
916 NULL,
917 NULL,
918 p_employee_id,
919 NULL,
920 NULL,
921 l_object_revision_num,
922 NULL, /* offline_code */
923 l_request_id,
924 l_program_application_id,
925 l_program_id,
926 l_program_date,
927 fnd_global.user_id,
928 fnd_global.login_id,
929 p_approval_group_id);
930
931 commit;
932
933 end insertActionHistory;
934
935
936 --------------------------------------------------------------------------------
937 --Start of Comments
938 --Name: Insert_Action_History
939 --Pre-reqs:
940 -- None.
941 --Modifies:
942 -- None.
943 --Locks:
944 -- None.
945 --Function:
946 -- Workflow activity PL/SQL handler.
947 -- This procedure is the wrapper procedure of insertActionHistory()
948 --Parameters:
949 --IN:
950 -- Standard workflow IN parameters
951 --OUT:
952 -- Standard workflow OUT parameters
953 --Testing:
954 --
955 --End of Comments
956 -------------------------------------------------------------------------------
957 procedure Insert_Action_History( itemtype in varchar2,
958 itemkey in varchar2,
959 actid in number,
960 funcmode in varchar2,
961 resultout out NOCOPY varchar2) IS
962
963 l_progress VARCHAR2(500) := '000';
964 l_action VARCHAR2(30) := 'APPROVE';
965 l_next_approver_id NUMBER:='';
966 l_req_header_id NUMBER:='';
967 l_approval_group_id NUMBER:='';
968
969 l_doc_string varchar2(200);
970 l_preparer_user_name varchar2(100);
971
972 l_org_id number;
973
974 BEGIN
975
976 l_progress := 'Insert_Action_History: 001';
977 IF (g_po_wf_debug = 'Y') THEN
978 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
979 END IF;
980
981 IF (funcmode='RUN') THEN
982
983
984 l_next_approver_id := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
985 itemkey=>itemkey,
986 aname=>'APPROVER_EMPID');
987
988
989 l_req_header_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
990 itemkey => itemkey,
991 aname => 'DOCUMENT_ID');
992
993 l_approval_group_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
994 itemkey => itemkey,
995 aname => 'APPROVAL_GROUP_ID');
996
997 -- Set the multi-org context
998 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
999 itemkey => itemkey,
1000 aname => 'ORG_ID');
1001
1002 IF l_org_id is NOT NULL THEN
1003 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1004 END IF;
1005
1006 l_progress := 'Insert_Action_History: 004 - Forward_Action_History';
1007 insertActionHistory(l_req_header_id, l_next_approver_id, l_approval_group_id);
1008
1009 l_progress := 'Insert_Action_History: 005 - Forward_Action_History';
1010 IF (g_po_wf_debug = 'Y') THEN
1011 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1012 END IF;
1013
1014 /* Reset the FORWARD_TO_USERNAME_RESPONSE attribute */
1015 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
1016 itemkey => itemkey,
1017 aname => 'FORWARD_TO_USERNAME_RESPONSE',
1018 avalue => NULL);
1019
1020 /* Reset the NOTE attribute */
1021 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
1022 itemkey => itemkey,
1023 aname => 'NOTE',
1024 avalue => NULL);
1025
1026 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1027 return;
1028
1029 END IF; -- run mode
1030
1031 l_progress := 'Insert_Action_History: 999';
1032 IF (g_po_wf_debug = 'Y') THEN
1033 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1034 END IF;
1035
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1039 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1040 wf_core.context('POR_AME_RCO_WF_PVT','Insert_Action_History',l_progress,sqlerrm);
1041 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_RCO_WF_PVT.INSERT_ACTION_HISTORY');
1042 RAISE;
1043 END Insert_Action_History;
1044
1045
1046 --------------------------------------------------------------------------------
1047 --Start of Comments
1048 --Name: Update_Action_History_Approve
1049 --Pre-reqs:
1050 -- None.
1051 --Modifies:
1052 -- None.
1053 --Locks:
1054 -- None.
1055 --Function:
1056 -- Workflow activity PL/SQL handler.
1057 -- This procedure updates the po_action_history table based on the approvers response.
1058 --Parameters:
1059 --IN:
1060 -- Standard workflow IN parameters
1061 --OUT:
1062 -- Standard workflow OUT parameters
1063 --Testing:
1064 --
1065 --End of Comments
1066 -------------------------------------------------------------------------------
1067 procedure Update_Action_History_Approve( itemtype in varchar2,
1068 itemkey in varchar2,
1069 actid in number,
1070 funcmode in varchar2,
1071 resultout out NOCOPY varchar2) IS
1072 l_progress VARCHAR2(500) := '000';
1073 l_action VARCHAR2(30) := 'APPROVE';
1074 l_forward_to_id NUMBER:='';
1075 l_document_id NUMBER;
1076 l_document_type VARCHAR2(25):='';
1077 l_document_subtype VARCHAR2(25):='';
1078 l_return_code NUMBER;
1079 l_result BOOLEAN:=FALSE;
1080 l_note VARCHAR2(4000);
1081
1082 l_doc_string varchar2(200);
1083 l_preparer_user_name varchar2(100);
1084
1085 l_org_id number;
1086 l_current_approver number;
1087
1088 BEGIN
1089
1090 l_progress := 'Update_Action_History_Approve: 001';
1091 IF (g_po_wf_debug = 'Y') THEN
1092 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1093 END IF;
1094
1095 IF (funcmode='RUN') THEN
1096
1097 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
1098 itemkey=>itemkey,
1099 aname=>'APPROVER_EMPID');
1100
1101 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1102 itemkey => itemkey,
1103 aname => 'DOCUMENT_ID');
1104
1105 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1106 itemkey => itemkey,
1107 aname => 'DOCUMENT_TYPE');
1108
1109 l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1110 itemkey => itemkey,
1111 aname => 'DOCUMENT_SUBTYPE');
1112
1113 l_note := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1114 itemkey => itemkey,
1115 aname => 'NOTE');
1116
1117 -- Set the multi-org context
1118 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1119 itemkey => itemkey,
1120 aname => 'ORG_ID');
1121
1122 IF l_org_id is NOT NULL THEN
1123 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1124 END IF;
1125
1126 l_progress := 'Update_Action_History_Approve: 002-'|| to_char(l_document_id)||'-'||
1127 l_document_type||'-'||l_document_subtype;
1128 IF (g_po_wf_debug = 'Y') THEN
1129 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1130 END IF;
1131
1132 UpdateActionHistory(l_document_id, l_action,
1133 l_note, l_current_approver);
1134
1135 /*This attribute is set to be used in POR_AME_REQ_WF_PVT.Process_Beat_By_First.
1136 Based on this value the flow is determined whether approval or rejection*/
1137 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
1138 itemkey => itemkey,
1139 aname => 'APPROVER_RESPONSE',
1140 avalue => 'APPROVED' );
1141
1142 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1143
1144 return;
1145
1146 END IF; -- run mode
1147
1148 l_progress := 'Update_Action_History_Approve: 003';
1149 IF (g_po_wf_debug = 'Y') THEN
1150 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1151 END IF;
1152
1153 EXCEPTION
1154 WHEN OTHERS THEN
1155 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1156 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1157 wf_core.context('POR_AME_RCO_WF_PVT','Update_Action_History_Approve',l_progress,sqlerrm);
1158 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');
1159 RAISE;
1160
1161 END Update_Action_History_Approve;
1162
1163
1164 --------------------------------------------------------------------------------
1165 --Start of Comments
1166 --Name: Update_Action_History_Reject
1167 --Pre-reqs:
1168 -- None.
1169 --Modifies:
1170 -- None.
1171 --Locks:
1172 -- None.
1173 --Function:
1174 -- Workflow activity PL/SQL handler.
1175 -- This procedure updates the po_action_history table based on the approvers response.
1176 --Parameters:
1177 --IN:
1178 -- Standard workflow IN parameters
1179 --OUT:
1180 -- Standard workflow OUT parameters
1181 --Testing:
1182 --
1183 --End of Comments
1184 -------------------------------------------------------------------------------
1185 procedure Update_Action_History_Reject(itemtype in varchar2,
1186 itemkey in varchar2,
1187 actid in number,
1188 funcmode in varchar2,
1189 resultout out NOCOPY varchar2) IS
1190
1191 l_progress VARCHAR2(100) := '000';
1192 l_action VARCHAR2(30) := 'REJECT';
1193 l_forward_to_id NUMBER:='';
1194 l_document_id NUMBER;
1195 l_document_type VARCHAR2(25):='';
1196 l_document_subtype VARCHAR2(25):='';
1197 l_return_code NUMBER;
1198 l_result BOOLEAN:=FALSE;
1199 l_note VARCHAR2(4000);
1200
1201 l_doc_string varchar2(200);
1202 l_preparer_user_name varchar2(100);
1203
1204 l_org_id number;
1205 l_current_approver number;
1206
1207 BEGIN
1208
1209 l_progress := 'Update_Action_History_Reject: 001';
1210 IF (g_po_wf_debug = 'Y') THEN
1211 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1212 END IF;
1213
1214 IF (funcmode='RUN') THEN
1215
1216 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
1217 itemkey=>itemkey,
1218 aname=>'APPROVER_EMPID');
1219
1220 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1221 itemkey => itemkey,
1222 aname => 'DOCUMENT_ID');
1223
1224 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1225 itemkey => itemkey,
1226 aname => 'DOCUMENT_TYPE');
1227
1228 l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1229 itemkey => itemkey,
1230 aname => 'DOCUMENT_SUBTYPE');
1231
1232 l_note := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1233 itemkey => itemkey,
1234 aname => 'NOTE');
1235
1236 -- Set the multi-org context
1237 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1238 itemkey => itemkey,
1239 aname => 'ORG_ID');
1240
1241 IF l_org_id is NOT NULL THEN
1242 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1243 END IF;
1244
1245 l_progress := 'Update_Action_History_Reject: 002-'|| to_char(l_document_id)||'-'||
1246 l_document_type||'-'||l_document_subtype;
1247 IF (g_po_wf_debug = 'Y') THEN
1248 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1249 END IF;
1250
1251 UpdateActionHistory(l_document_id, l_action,
1252 l_note, l_current_approver);
1253
1254 /*This attribute is set to be used in POR_AME_REQ_WF_PVT.Process_Beat_By_First.
1255 Based on this value the flow is determined whether approval or rejection*/
1256 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
1257 itemkey => itemkey,
1258 aname => 'APPROVER_RESPONSE',
1259 avalue => 'REJECTED' );
1260
1261 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1262
1263 return;
1264
1265 END IF; -- run mode
1266
1267 l_progress := 'Update_Action_History_Reject: 003';
1268 IF (g_po_wf_debug = 'Y') THEN
1269 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1270 END IF;
1271
1272 EXCEPTION
1273 WHEN OTHERS THEN
1274 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1275 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1276 wf_core.context('POR_AME_RCO_WF_PVT','Update_Action_History_Reject',l_progress,sqlerrm);
1277 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');
1278 RAISE;
1279
1280 END Update_Action_History_Reject;
1281
1282 --------------------------------------------------------------------------------
1283 --Start of Comments
1284 --Name: Update_Action_History_Timeout
1285 --Pre-reqs:
1286 -- None.
1287 --Modifies:
1288 -- None.
1289 --Locks:
1290 -- None.
1291 --Function:
1292 -- Workflow activity PL/SQL handler.
1293 -- This procedure updates the po_action_history table based on the approvers response.
1294 --Parameters:
1295 --IN:
1296 -- Standard workflow IN parameters
1297 --OUT:
1298 -- Standard workflow OUT parameters
1299 --Testing:
1300 --
1301 --End of Comments
1302 -------------------------------------------------------------------------------
1303 procedure Update_Action_History_Timeout(itemtype in varchar2,
1304 itemkey in varchar2,
1305 actid in number,
1306 funcmode in varchar2,
1307 resultout out NOCOPY varchar2) IS
1308
1309 l_progress VARCHAR2(100) := '000';
1310 l_action VARCHAR2(30) := 'NO ACTION';
1311 l_forward_to_id NUMBER:='';
1312 l_document_id NUMBER;
1313 l_document_type VARCHAR2(25):='';
1314 l_document_subtype VARCHAR2(25):='';
1315 l_return_code NUMBER;
1316 l_result BOOLEAN:=FALSE;
1317 l_note VARCHAR2(4000);
1318
1319 l_doc_string varchar2(200);
1320 l_preparer_user_name varchar2(100);
1321
1322 l_org_id number;
1323 l_current_approver number;
1324
1325 BEGIN
1326
1327 l_progress := 'Update_Action_History_Timeout: 001';
1328 IF (g_po_wf_debug = 'Y') THEN
1329 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1330 END IF;
1331
1332 IF (funcmode='RUN') THEN
1333
1334 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
1335 itemkey=>itemkey,
1336 aname=>'APPROVER_EMPID');
1337
1338 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1339 itemkey => itemkey,
1340 aname => 'DOCUMENT_ID');
1341
1342 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1343 itemkey => itemkey,
1344 aname => 'DOCUMENT_TYPE');
1345
1346 l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1347 itemkey => itemkey,
1348 aname => 'DOCUMENT_SUBTYPE');
1349
1350 l_note := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1351 itemkey => itemkey,
1352 aname => 'NOTE');
1353
1354 -- Set the multi-org context
1355 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1356 itemkey => itemkey,
1357 aname => 'ORG_ID');
1358
1359 IF l_org_id is NOT NULL THEN
1360 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1361 END IF;
1362
1363 l_progress := 'Update_Action_History_Timeout: 002-'|| to_char(l_document_id)||'-'||
1364 l_document_type||'-'||l_document_subtype;
1365 IF (g_po_wf_debug = 'Y') THEN
1366 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1367 END IF;
1368
1369 UpdateActionHistory(l_document_id, l_action,
1370 l_note, l_current_approver);
1371
1372 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1373
1374 return;
1375
1376 END IF; -- run mode
1377
1378 l_progress := 'Update_Action_History_Timeout: 003';
1379 IF (g_po_wf_debug = 'Y') THEN
1380 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1381 END IF;
1382
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1386 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1387 wf_core.context('POR_AME_REQ_WF_PVT','Update_Action_History_Timeout',l_progress,sqlerrm);
1388 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');
1389 RAISE;
1390
1391 END Update_Action_History_Timeout;
1392
1393 --------------------------------------------------------------------------------
1394 --Start of Comments
1395 --Name: Set_Rco_Stat_Approved
1396 --Pre-reqs:
1397 -- None.
1398 --Modifies:
1399 -- None.
1400 --Locks:
1401 -- None.
1402 --Function:
1403 -- Workflow activity PL/SQL handler.
1404 -- This procedure updates the req approval status in po_requistion_headers_all table.
1405 -- po_action_history table also will be updated based on the approvers response.
1406 -- If it is a first responder wins setup in ame, then once the first reponder wins, others will not be able to take decisions.
1407 --Parameters:
1408 --IN:
1409 -- Standard workflow IN parameters
1410 --OUT:
1411 -- Standard workflow OUT parameters
1412 --Testing:
1413 --
1414 --End of Comments
1415 -------------------------------------------------------------------------------
1416 procedure Set_Rco_Stat_Approved( itemtype in varchar2,
1417 itemkey in varchar2,
1418 actid in number,
1419 funcmode in varchar2,
1420 resultout out NOCOPY varchar2 ) is
1421
1422 l_doc_header_id NUMBER;
1423 l_po_header_id NUMBER;
1424 l_doc_type VARCHAR2(14);
1425 l_note VARCHAR2(4000);
1426 x_progress varchar2(500);
1427
1428 l_doc_string varchar2(200);
1429 l_preparer_user_name varchar2(100);
1430
1431 BEGIN
1432
1433 x_progress := 'POR_AME_RCO_WF_PVT.Set_Rco_Stat_Approved: 01';
1434
1435 if (funcmode <> wf_engine.eng_run) then
1436 resultout := wf_engine.eng_null;
1437 return;
1438 end if;
1439
1440 l_doc_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1441 itemkey => itemkey,
1442 aname => 'DOCUMENT_ID');
1443
1444 l_doc_type := wf_engine.GetItemAttrText( itemtype => itemtype,
1445 itemkey => itemkey,
1446 aname => 'DOCUMENT_TYPE');
1447
1448 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 ;
1449 IF (g_po_wf_debug = 'Y') THEN
1450 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1451 END IF;
1452
1453 -- If the setup is first responder wins, then once the first approver responds, the others will not be able to take decisions.
1454
1455 UpdateActionHistory(l_doc_header_id, 'NO ACTION',
1456 fnd_message.get_string('ICX', 'ICX_POR_REQ_ALREADY_APPROVED'), NULL);
1457
1458 wf_engine.SetItemAttrText( itemtype => itemtype,
1459 itemkey => itemkey,
1460 aname => 'AUTHORIZATION_STATUS',
1461 avalue => 'APPROVED');
1462
1463 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
1464
1465 x_progress := 'POR_AME_RCO_WF_PVT.Set_Rco_Stat_Approved: 03';
1466 IF (g_po_wf_debug = 'Y') THEN
1467 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1468 END IF;
1469
1470 EXCEPTION
1471 WHEN OTHERS THEN
1472 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1473 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1474 wf_core.context('POR_AME_RCO_WF_PVT','Set_Rco_Stat_Approved',x_progress);
1475 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');
1476 raise;
1477 END Set_Rco_Stat_Approved;
1478
1479
1480 --------------------------------------------------------------------------------
1481 --Start of Comments
1482 --Name: Set_Rco_Stat_Rejected
1483 --Pre-reqs:
1484 -- None.
1485 --Modifies:
1486 -- None.
1487 --Locks:
1488 -- None.
1489 --Function:
1490 -- Workflow activity PL/SQL handler.
1491 -- This procedure updates the req approval status in po_requistion_headers_all table.
1492 -- po_action_history table also will be updated based on the approvers response.
1493 -- If it is a first responder wins setup in ame, then once the first reponder wins, others will not be able to take decisions.
1494 --Parameters:
1495 --IN:
1496 -- Standard workflow IN parameters
1497 --OUT:
1498 -- Standard workflow OUT parameters
1499 --Testing:
1500 --
1501 --End of Comments
1502 -------------------------------------------------------------------------------
1503 procedure Set_Rco_Stat_Rejected( itemtype in varchar2,
1504 itemkey in varchar2,
1505 actid in number,
1506 funcmode in varchar2,
1507 resultout out NOCOPY varchar2 ) is
1508
1509 l_doc_header_id NUMBER;
1510 l_po_header_id NUMBER;
1511 l_doc_type VARCHAR2(14);
1512 l_note VARCHAR2(4000);
1513 x_progress varchar2(500);
1514
1515 l_doc_string varchar2(200);
1516 l_preparer_user_name varchar2(100);
1517
1518 BEGIN
1519
1520 x_progress := 'POR_AME_RCO_WF_PVT.Set_Rco_Stat_Rejected: 01';
1521
1522 if (funcmode <> wf_engine.eng_run) then
1523 resultout := wf_engine.eng_null;
1524 return;
1525 end if;
1526
1527 l_doc_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1528 itemkey => itemkey,
1529 aname => 'DOCUMENT_ID');
1530
1531 x_progress := 'POR_AME_RCO_WF_PVT.Set_Rco_Stat_Rejected: 02 - l_doc_header_id ' || l_doc_header_id;
1532 IF (g_po_wf_debug = 'Y') THEN
1533 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1534 END IF;
1535
1536 -- If the setup is first responder wins, then once the first approver responds, the others will not be able to take decisions.
1537
1538 UpdateActionHistory(l_doc_header_id, 'NO ACTION',
1539 fnd_message.get_string('ICX', 'ICX_POR_REQ_ALREADY_REJECTED'), NULL);
1540
1541 wf_engine.SetItemAttrText( itemtype => itemtype,
1542 itemkey => itemkey,
1543 aname => 'AUTHORIZATION_STATUS',
1544 avalue => 'REJECTED');
1545
1546 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
1547
1548 x_progress := 'POR_AME_RCO_WF_PVT.Set_Rco_Stat_Rejected: 03';
1549 IF (g_po_wf_debug = 'Y') THEN
1550 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1551 END IF;
1552
1553 EXCEPTION
1554 WHEN OTHERS THEN
1555 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1556 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1557 wf_core.context('POR_AME_RCO_WF_PVT','Set_Rco_Stat_Rejected',x_progress);
1558 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');
1559 raise;
1560 END Set_Rco_Stat_Rejected;
1561
1562 --------------------------------------------------------------------------------
1563 --Start of Comments
1564 --Name: UpdateActionHistory
1565 --Pre-reqs:
1566 -- None.
1567 --Modifies:
1568 -- None.
1569 --Locks:
1570 -- None.
1571 --Function:
1572 -- This procedure updates the po_action_history table based on the approvers response.
1573 --Parameters:
1574 --IN:
1575 -- p_document_id : Requisition Header Id
1576 -- p_action : Action
1577 -- p_note : Notes
1578 -- p_current_approver: Approver person Id
1579 --OUT:
1580 --
1581 --End of Comments
1582 -------------------------------------------------------------------------------
1583 PROCEDURE UpdateActionHistory(p_document_id NUMBER,
1584 p_action VARCHAR2,
1585 p_note VARCHAR2,
1586 p_current_approver NUMBER) IS
1587
1588 pragma AUTONOMOUS_TRANSACTION;
1589
1590 BEGIN
1591
1592 if (p_current_approver is not null) then
1593
1594 UPDATE po_action_history
1595 SET action_code = p_action,
1596 note = p_note,
1597 action_date = sysdate
1598 WHERE object_id = p_document_id and
1599 employee_id = p_current_approver and
1600 action_code is null and
1601 object_type_code = 'REQUISITION'
1602 and rownum=1;
1603
1604 else
1605
1606 UPDATE po_action_history
1607 SET action_code = p_action,
1608 note = p_note,
1609 action_date = sysdate
1610 WHERE object_id = p_document_id and
1611 action_code is null and
1612 object_type_code = 'REQUISITION';
1613 end if;
1614
1615 COMMIT;
1616
1617 EXCEPTION
1618
1619 WHEN OTHERS THEN
1620 RAISE;
1621
1622 END UpdateActionHistory;
1623
1624 --------------------------------------------------------------------------------
1625 --Start of Comments
1626 --Name: position_has_valid_approvers
1627 --Pre-reqs:
1628 -- None.
1629 --Modifies:
1630 -- None.
1631 --Locks:
1632 -- None.
1633 --Function:
1634 -- Workflow activity PL/SQL handler.
1635 -- This function is used to check whether to launch the parallel approval process or not.
1636 -- If a position does not have any users, then this function will return 'N', otherwise return 'Y'
1637 --Parameters:
1638 --IN:
1639 -- documentId : ReqHeaderId
1640 -- documentType : AME Transaction Type
1641 --OUT:
1642 -- 'Y' We can launch the parallel approval process.
1643 -- 'N' Invalid approver. We can not launch the parallel approval process.
1644 -- 'NO_USERS' No users for position. This AME record will be deleted. Go to the next approver record.
1645 --Testing:
1646 --
1647 --End of Comments
1648 -------------------------------------------------------------------------------
1649 FUNCTION position_has_valid_approvers( documentId NUMBER, documentType VARCHAR2 )RETURN VARCHAR2 is
1650
1651 l_next_approver_id number;
1652 l_next_approver_name per_employees_current_x.full_name%TYPE;
1653 l_position_has_valid_approvers VARCHAR2(10);
1654 l_approver_index NUMBER;
1655 l_error_message varchar2(3000);
1656
1657 l_first_approver_id NUMBER;
1658 l_first_position_id NUMBER;
1659
1660 BEGIN
1661
1662 l_position_has_valid_approvers := 'Y';
1663 l_approver_index := g_next_approvers.first();
1664
1665 select first_position_id, first_approver_id
1666 into l_first_position_id, l_first_approver_id
1667 from po_requisition_headers_all
1668 where documentId = requisition_header_id;
1669
1670 while( l_approver_index is not null ) loop
1671 l_position_has_valid_approvers := 'Y';
1672 if (g_next_approvers(l_approver_index).orig_system = 'POS') then
1673
1674 BEGIN
1675
1676 if (l_first_position_id is not NULL AND l_first_position_id=g_next_approvers(l_approver_index).orig_system_id) then
1677
1678 l_next_approver_id := l_first_approver_id;
1679
1680 SELECT full_name
1681 INTO l_next_approver_name
1682 FROM per_all_people_f person
1683 WHERE person_id = l_first_approver_id
1684 AND Trunc(SYSDATE) BETWEEN Effective_Start_Date AND Effective_End_Date ;
1685
1686 else
1687
1688 /* find the persond id from the position_id*/
1689 SELECT person_id, full_name into l_next_approver_id,l_next_approver_name FROM (
1690 SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
1691 WHERE asg.position_id = g_next_approvers(l_approver_index).orig_system_id and trunc(sysdate) between person.effective_start_date
1692 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
1693 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
1694 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
1695 and asg.assignment_status_type_id not in (
1696 SELECT assignment_status_type_id FROM per_assignment_status_types
1697 WHERE per_system_status = 'TERM_ASSIGN'
1698 ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
1699 ) where rownum = 1;
1700
1701 end if;
1702
1703 EXCEPTION
1704 WHEN NO_DATA_FOUND THEN
1705
1706 -- No users for this position. Check whether this is last position or not.
1707 -- If this is last position then return the req to imcomplete status.
1708 -- Otherwise set this approver record to 'Approved'
1709 if( is_last_approver_record(documentId, documentType, g_next_approvers(l_approver_index)) = 'Y' )then
1710 return 'N';
1711 else
1712
1713 /*
1714 g_next_approvers(l_approver_index).approval_status := ame_util.noResponseStatus;
1715 -- Update the Approval status with the response from the approver.
1716 ame_api2.updateApprovalStatus( applicationIdIn =>applicationId,
1717 transactionIdIn =>documentId,
1718 transactionTypeIn =>documentType,
1719 approverIn => g_next_approvers(l_approver_index)
1720 );
1721 */
1722
1723 ame_api3.suppressApprover( applicationIdIn => applicationId,
1724 transactionIdIn => documentId,
1725 approverIn => g_next_approvers(l_approver_index),
1726 transactionTypeIn => documentType
1727 );
1728
1729 -- remove this approver from the global list.
1730 g_next_approvers.delete(l_approver_index);
1731 l_position_has_valid_approvers := 'NO_USERS';
1732
1733 end if;
1734 END;
1735 end if;
1736 l_approver_index := g_next_approvers.next(l_approver_index);
1737 end loop;
1738 return l_position_has_valid_approvers;
1739
1740 EXCEPTION
1741 WHEN OTHERS THEN
1742 return 'N';
1743 END position_has_valid_approvers;
1744
1745
1746 --------------------------------------------------------------------------------
1747 --Start of Comments
1748 --Name: is_last_approver_record
1749 --Pre-reqs:
1750 -- None.
1751 --Modifies:
1752 -- None.
1753 --Locks:
1754 -- None.
1755 --Function:
1756 -- Workflow activity PL/SQL handler.
1757 -- This function is used to check whether the approver/position is last in the approval chain or not
1758 -- This function will be invoked only if a particular position does not have any associated users.
1759 -- If this function returns 'Y', then the req will be put back in incomplete status.
1760 --Parameters:
1761 --IN:
1762 -- documentId : ReqHeaderId
1763 -- documentType : AME Transaction Type
1764 --OUT:
1765 -- 'Y' The approver/position is last in the approval chain.
1766 -- 'N' The approver/position is not last in the approval chain
1767 --Testing:
1768 --
1769 --End of Comments
1770 -------------------------------------------------------------------------------
1771 Function is_last_approver_record( documentId NUMBER, documentType VARCHAR2, approverRecord in ame_util.approverRecord2 ) RETURN VARCHAR2 is
1772
1773 l_is_last_approver_record VARCHAR2(1);
1774 l_total_approver_count NUMBER;
1775 l_current_approver_index NUMBER;
1776 tmpApproverList ame_util.approversTable2;
1777 l_process_out VARCHAR2(10);
1778
1779 BEGIN
1780
1781 ame_api2.getAllApprovers7( applicationIdIn =>applicationId,
1782 transactionIdIn =>documentId,
1783 transactionTypeIn =>documentType,
1784 approvalProcessCompleteYNOut => l_process_out,
1785 approversOut =>tmpApproverList
1786 );
1787
1788 l_total_approver_count := tmpApproverList.count;
1789 l_current_approver_index := 0;
1790
1791 for i in 1 .. tmpApproverList.count loop
1792
1793 l_current_approver_index := i;
1794 if ( tmpApproverList(i).name = approverRecord.name AND
1795 tmpApproverList(i).orig_system = approverRecord.orig_system AND
1796 tmpApproverList(i).orig_system_id = approverRecord.orig_system_id AND
1797 tmpApproverList(i).authority = approverRecord.authority AND
1798 tmpApproverList(i).group_or_chain_id = approverRecord.group_or_chain_id AND
1799 tmpApproverList(i).action_type_id = approverRecord.action_type_id AND
1800 tmpApproverList(i).item_id = approverRecord.item_id AND
1801 tmpApproverList(i).item_class = approverRecord.item_class AND
1802 tmpApproverList(i).approver_category = approverRecord.approver_category
1803 ) then
1804
1805 EXIT;
1806 end if;
1807 end loop;
1808
1809 if( l_current_approver_index = l_total_approver_count ) then
1810 return 'Y';
1811 else
1812 return 'N';
1813 end if;
1814
1815 EXCEPTION
1816 WHEN OTHERS THEN
1817 return 'Y';
1818 END is_last_approver_record;
1819
1820
1821 END POR_AME_RCO_WF_PVT;