[Home] [Help]
PACKAGE BODY: APPS.POR_AME_REQ_WF_PVT
Source
1 PACKAGE BODY POR_AME_REQ_WF_PVT AS
2 /* $Header: POXAMEPB.pls 120.77.12020000.5 2013/03/22 10:40:04 inagdeo ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6
7 g_next_approvers ame_util.approversTable2;
8
9 FUNCTION position_has_valid_approvers( documentId NUMBER, documentType VARCHAR2 )RETURN VARCHAR2;
10
11 Function is_last_approver_record( documentId NUMBER, documentType VARCHAR2, approverRecord in ame_util.approverRecord2 ) RETURN VARCHAR2;
12
13 PROCEDURE UpdateActionHistory(p_document_id NUMBER,
14 p_action VARCHAR2,
15 p_note VARCHAR2,
16 p_current_approver NUMBER);
17
18 -- <CLM code> --
19 procedure insertActionHistoryPo( p_document_id in number,
20 p_draft_id in number,
21 p_document_type in varchar2,
22 p_document_subtype in varchar2,
23 p_transaction_type in varchar2,
24 p_employee_id in number,
25 p_approval_group_id in number);
26
27 procedure UpdateActionHistoryPo(p_transaction_type VARCHAR2,
28 p_document_id NUMBER,
29 p_draft_id NUMBER,
30 p_document_type IN VARCHAR2, --Bug 13444730
31 p_document_subtype IN VARCHAR2, --Bug 13444730
32 p_action VARCHAR2,
33 p_note VARCHAR2,
34 p_current_approver NUMBER);
35
36
37 --------------------------------------------------------------------------------
38 --Start of Comments
39 --Name: Get_Next_Approvers
40 --Pre-reqs:
41 -- None.
42 --Modifies:
43 -- None.
44 --Locks:
45 -- None.
46 --Function:
47 -- Workflow activity PL/SQL handler
48 -- Get the next approver name from the AME approval list
49 -- And update workflow attributes.
50 -- If no next approver is found, approval routing will terminate.
51 --Parameters:
52 --IN:
53 -- Standard workflow IN parameters
54 --OUT:
55 -- Standard workflow OUT parameters
56 --Testing:
57 --
58 --End of Comments
59 -------------------------------------------------------------------------------
60 procedure Get_Next_Approvers(itemtype in varchar2,
61 itemkey in varchar2,
62 actid in number,
63 funcmode in varchar2,
64 resultout out NOCOPY varchar2) IS
65 l_progress VARCHAR2(3500) := '000';
66 l_document_id NUMBER;
67 l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
68 l_document_subtype PO_DOCUMENT_TYPES.DOCUMENT_SUBTYPE%TYPE;
69 l_next_approver_id NUMBER;
70 l_next_approver_user_name fnd_user.user_name%TYPE;
71 l_next_approver_disp_name wf_users.display_name%TYPE;
72 l_orig_system wf_users.orig_system%TYPE := ame_util.perOrigSystem;
73 l_sequence_num NUMBER;
74 l_approver_type VARCHAR2(30);
75
76 l_doc_string varchar2(200);
77 l_preparer_user_name fnd_user.user_name%TYPE;
78 l_org_id number;
79
80 l_next_approver ame_util.approverRecord;
81 l_insertion_type VARCHAR2(30);
82 l_authority_type VARCHAR2(30);
83 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
84
85 l_completeYNO varchar2(100);
86 l_position_has_valid_approvers varchar2(10);
87 l_need_to_get_next_approver boolean;
88
89 l_ame_exception ame_util.longestStringType;
90 l_ame_transaction_id po_headers_all.ame_approval_id%TYPE;
91
92 BEGIN
93 l_progress := 'In Get_Next_Approver';
94 IF (g_po_wf_debug = 'Y') THEN
95 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
96 END IF;
97 IF (funcmode = 'RUN') THEN
98
99 l_progress := 'Get_Next_Approver: 001';
100 IF (g_po_wf_debug = 'Y') THEN
101 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
102 END IF;
103
104 /* Check if there is any AME exception.
105 If yes, then return 'invalid approver' */
106 l_ame_exception := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
107 itemkey => itemkey,
108 aname => 'AME_EXCEPTION');
109 IF l_ame_exception IS NOT NULL THEN
110 resultout:='COMPLETE:'||'INVALID_APPROVER';
111 RETURN;
112 END IF;
113
114
115 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
116 itemkey => itemkey,
117 aname => 'DOCUMENT_TYPE');
118
119 l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
120 itemkey => itemkey,
121 aname => 'DOCUMENT_SUBTYPE');
122
123 -- CLM Apprvl
124
125 l_transaction_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
126 itemkey => itemkey,
127 aname => 'AME_TRANSACTION_TYPE');
128
129 if l_document_type = 'REQUISITION' then
130 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
131 itemkey => itemkey,
132 aname => 'DOCUMENT_ID');
133 else
134 l_document_id := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
135 itemkey => itemkey,
136 aname => 'AME_TRANSACTION_ID');
137 end if;
138
139 -- CLM Apprvl
140
141 l_progress := 'Get_Next_Approver: 002-'||to_char(l_document_id)||'-'|| l_document_type||'-'||l_document_subtype;
142
143 IF (g_po_wf_debug = 'Y') THEN
144 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
145 END IF;
146
147 -- Get the next approver from AME.
148 LOOP
149
150 l_need_to_get_next_approver := FALSE;
151 BEGIN
152 l_progress := 'Get_Next_Approver: 003-'||
153 'Calling AME API '||
154 'ApplicationId: '||
155 to_char(applicationId)||'-'||
156 'Document Id: '||
157 to_char(l_document_id)||'-'||
158 'Transaction Type: '||
159 l_transaction_type;
160
161 IF (g_po_wf_debug = 'Y') THEN
162 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
163 END IF;
164
165
166 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanTrue;
167 ame_api2.getNextApprovers4( applicationIdIn=>applicationId,
168 transactionIdIn=>l_document_id,
169 transactionTypeIn=>l_transaction_type,
170 approvalProcessCompleteYNOut=>l_completeYNO,
171 nextApproversOut=>g_next_approvers
172 );
173
174 EXCEPTION
175 WHEN OTHERS THEN
176
177 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
178 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
179 wf_core.context('POR_AME_REQ_WF_PVT','Get_Next_Approvers: Unable to get the next approvers from AME.',l_progress,sqlerrm);
180
181 IF (g_po_wf_debug = 'Y') THEN
182 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,'POR_AME_REQ_WF_PVT Get_Next_Approvers: Unable to get the next approvers from AME.'||l_progress||sqlerrm);
183 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string);
184 END IF;
185
186 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.GET_NEXT_APPROVERS',l_document_id);
187
188 resultout:='COMPLETE:'||'INVALID_APPROVER';
189 return;
190 END;
191
192 l_progress := 'Get_Next_Approver: 003- getNextApprovers4(). Approvers :' || g_next_approvers.count || ' -- Approval Process Completed :' || l_completeYNO ;
193 IF (g_po_wf_debug = 'Y') THEN
194 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
195 END IF;
196
197 if ( g_next_approvers.count > 0 ) then
198
199 l_position_has_valid_approvers := position_has_valid_approvers(l_document_id, l_transaction_type) ;
200
201 l_progress := 'Get_Next_Approver: 004 - l_position_has_valid_approvers :' || l_position_has_valid_approvers;
202 IF (g_po_wf_debug = 'Y') THEN
203 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
204 END IF;
205
206 l_progress := 'Get_Next_Approver: 005- Approvers after the validation process :' || g_next_approvers.count;
207 IF (g_po_wf_debug = 'Y') THEN
208 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
209 END IF;
210
211 if( g_next_approvers.count = 0 AND 'NO_USERS' = l_position_has_valid_approvers ) then
212 l_need_to_get_next_approver := TRUE;
213 end if;
214
215 end if;
216
217 EXIT WHEN l_need_to_get_next_approver = FALSE;
218 END LOOP;
219
220 -- Check the number of next approvers. If the count is zero, then verify the approval process is completed or not.
221 if ( g_next_approvers.count > 0 ) then
222
223 if( 'N' = l_position_has_valid_approvers ) then
224 resultout:='COMPLETE:'||'INVALID_APPROVER';
225 else
226 resultout:='COMPLETE:'||'VALID_APPROVER';
227 end if;
228 return;
229
230 else
231
232 -- 'X' is the code when there is no rule needed and applied.
233
234 if (l_completeYNO in ('X','Y')) then
235 resultout:='COMPLETE:'||'NO_NEXT_APPROVER';
236 return;
237 else
238 resultout:='COMPLETE:'||'';
239 return;
240 end if;
241 end if;
242 end if;
243 EXCEPTION
244 WHEN OTHERS THEN
245
246 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
247 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
248 wf_core.context('POR_AME_REQ_WF_PVT','Get_Next_Approvers - Unexpected Exception: ',l_progress,sqlerrm);
249
250 IF (g_po_wf_debug = 'Y') THEN
251 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string);
252 END IF;
253
254 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.GET_NEXT_APPROVERS');
255 resultout:='COMPLETE:'||'INVALID_APPROVER';
256 return;
257 END Get_Next_Approvers;
258
259
260 --------------------------------------------------------------------------------
261 --Start of Comments
262 --Name: Launch_Parallel_Approval
263 --Pre-reqs:
264 -- None.
265 --Modifies:
266 -- None.
267 --Locks:
268 -- None.
269 --Function:
270 -- Workflow activity PL/SQL handler. This procedure is used to send the notification for the approvers.
271 -- Iterate through the list of approvers got from the API call ame_api2.getNextApprovers4.
272 -- Get the next approver name from the global variable g_next_approvers and for each retrieved approver
273 -- separate workflow process is kicked. Each process is called child process.
274 -- If there are 3 approvers, then 3 child process will be created and each of them will be notified at the same time.
275 --
276 -- If the next approver record is of Position Hierarchy type, then the users associated to the position_id will be
277 -- retrieved, will be alphabetically sorted using last_name and to the first user notification will be sent.
278 --
279 --Parameters:
280 --IN:
281 -- Standard workflow IN parameters
282 --OUT:
283 -- Standard workflow OUT parameters
284 --Testing:
285 --
286 --End of Comments
287 -------------------------------------------------------------------------------
288 procedure Launch_Parallel_Approval(itemtype in varchar2,
289 itemkey in varchar2,
290 actid in number,
291 funcmode in varchar2,
292 resultout out NOCOPY varchar2) IS
293
294 l_progress VARCHAR2(500) := '000';
295 l_document_id number;
296 l_item_key wf_items.item_key%TYPE;
297 l_next_approver_id number;
298 l_next_approver_name per_employees_current_x.full_name%TYPE;
299 l_next_approver_user_name VARCHAR2(100);
300 l_next_approver_disp_name VARCHAR2(240);
301 l_orig_system VARCHAR2(48);
302 l_org_id number;
303 l_functional_currency VARCHAR2(30);
304 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
305
306 n_varname Wf_Engine.NameTabTyp;
307 n_varval Wf_Engine.NumTabTyp;
308
309 t_po_varname Wf_Engine.NameTabTyp;
310 t_po_varval Wf_Engine.TextTabTyp;
311
312 t_req_varname Wf_Engine.NameTabTyp;
313 t_req_varval Wf_Engine.TextTabTyp;
314
315 l_no_positionholder exception;
316 l_preparer_user_name fnd_user.user_name%TYPE;
317 l_doc_string varchar2(200);
318 l_start_block_activity varchar2(1);
319 l_has_fyi_app varchar2(1);
320 l_approver_index NUMBER;
321
322 l_first_position_id NUMBER := null;
323 l_first_approver_id NUMBER := null;
324 l_ame_transaction_id PO_HEADERS_ALL.ame_approval_id%TYPE;
325 l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
326
327 begin
328 IF (funcmode='RUN') THEN
329
330 l_progress := 'Launch_Parallel_Approval: 001';
331 IF (g_po_wf_debug = 'Y') THEN
332 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
333 END IF;
334
335 /*Bug 11727653: BYPASSING MULTIPLE SUBMISSION CHECKS IN WORKFLOW
336 - setting the bypass flag to N if document is getting forwarded.*/
337 po_wf_util_pkg.SetItemAttrText(itemtype => itemtype,
338 itemkey => itemkey,
339 aname => 'BYPASS_CHECKS_FLAG',
340 avalue => 'N');
341
342 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
343 itemkey => itemkey,
344 aname => 'ORG_ID');
345
346 l_document_type := po_wf_util_pkg.GetItemAttrText(itemtype => itemtype,
347 itemkey => itemkey,
348 aname => 'DOCUMENT_TYPE');
349 l_document_id := po_wf_util_pkg.GetItemAttrNumber(itemtype => itemtype,
350 itemkey => itemkey,
351 aname => 'DOCUMENT_ID');
352 l_transaction_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
353 itemkey => itemkey,
354 aname => 'AME_TRANSACTION_TYPE');
355
356 -- CLM Apprvl
357 if l_document_type <> 'REQUISITION' then
358 l_ame_transaction_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
359 itemkey => itemkey,
360 aname => 'AME_TRANSACTION_ID');
361 end if;
362
363
364 l_start_block_activity := 'N';
365 l_has_fyi_app := 'N';
366 -- Iterate through the list of next approvers.
367 l_approver_index := g_next_approvers.first();
368 while ( l_approver_index is not null ) loop
369
370 l_progress := 'Launch_Parallel_Approval: 002 -- Next Approver :' || g_next_approvers(l_approver_index).name;
371 IF (g_po_wf_debug = 'Y') THEN
372 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
373 END IF;
374
375 SELECT
376 to_char(l_document_id) || '-' || to_char(PO_WF_ITEMKEY_S.nextval)
377 INTO l_item_key
378 FROM sys.dual;
379
380 -- Create a child process for the retrieved approver.
381
382 if l_document_type = 'REQUISITION' then
383 wf_engine.CreateProcess( itemtype => itemtype,
384 itemkey => l_item_key,
385 process => 'AME_PARALLEL_APPROVAL');
386 ELSE
387 wf_engine.CreateProcess( itemtype => itemtype,
388 itemkey => l_item_key,
389 process => 'PARALLEL_APPROVAL_PROCESS');
390 END IF;
391
392 /* Need to set the parent child relationship between processes */
393 wf_engine.SetItemParent( itemtype => itemtype,
394 itemkey => l_item_key,
395 parent_itemtype => itemtype,
396 parent_itemkey => itemkey,
397 parent_context => NULL);
398
399
400 -- CLM Apprvl
401 if l_document_type = 'REQUISITION' then
402 t_req_varname(1) := 'DOCUMENT_TYPE';
403 t_req_varval(1) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
404 itemkey => itemkey,
405 aname => 'DOCUMENT_TYPE');
406 t_req_varname(2) := 'DOCUMENT_SUBTYPE';
407 t_req_varval(2) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
408 itemkey => itemkey,
409 aname => 'DOCUMENT_SUBTYPE');
410 t_req_varname(3) := 'PREPARER_USER_NAME';
411 t_req_varval(3) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
412 itemkey => itemkey,
413 aname => 'PREPARER_USER_NAME');
414 t_req_varname(4) := 'PREPARER_DISPLAY_NAME';
415 t_req_varval(4) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
416 itemkey => itemkey,
417 aname => 'PREPARER_DISPLAY_NAME');
418 t_req_varname(5) := 'FUNCTIONAL_CURRENCY';
419 t_req_varval(5) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
420 itemkey => itemkey,
421 aname => 'FUNCTIONAL_CURRENCY');
422 t_req_varname(6) := 'IS_AME_APPROVAL';
423 t_req_varval(6) := 'Y';
424 t_req_varname(7) := 'TOTAL_AMOUNT_DSP';
425 t_req_varval(7) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
426 itemkey => itemkey,
427 aname => 'TOTAL_AMOUNT_DSP');
428 t_req_varname(8) := 'FORWARD_FROM_DISP_NAME';
429 t_req_varval(8) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
430 itemkey => itemkey,
431 aname => 'FORWARD_FROM_DISP_NAME');
432 t_req_varname(9) := 'FORWARD_FROM_USER_NAME';
433 t_req_varval(9) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
434 itemkey => itemkey,
435 aname => 'FORWARD_FROM_USER_NAME');
436 t_req_varname(10) := 'TAX_AMOUNT_CURRENCY_DSP';
437 t_req_varval(10) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
438 itemkey => itemkey,
439 aname => 'TAX_AMOUNT_CURRENCY_DSP');
440 t_req_varname(11) := 'DOCUMENT_NUMBER';
441 t_req_varval(11) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
442 itemkey => itemkey,
443 aname => 'DOCUMENT_NUMBER');
444 t_req_varname(12) := 'AME_TRANSACTION_TYPE';
445 t_req_varval(12) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
446 itemkey => itemkey,
447 aname => 'AME_TRANSACTION_TYPE');
448 t_req_varname(13) := 'OPEN_FORM_COMMAND';
449 t_req_varval(13) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
450 itemkey => itemkey,
451 aname => 'OPEN_FORM_COMMAND');
452 t_req_varname(14) := 'REQ_DESCRIPTION';
453 t_req_varval(14) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
454 itemkey => itemkey,
455 aname => 'REQ_DESCRIPTION');
456 t_req_varname(15) := 'REQ_AMOUNT_CURRENCY_DSP';
457 t_req_varval(15) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
458 itemkey => itemkey,
459 aname => 'REQ_AMOUNT_CURRENCY_DSP');
460 t_req_varname(16) := 'CONTRACTOR_REQUISITION_FLAG';
461 t_req_varval(16) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
462 itemkey => itemkey,
463 aname => 'CONTRACTOR_REQUISITION_FLAG');
464 t_req_varname(17) := 'CONTRACTOR_REQUISITION_FLAG';
465 t_req_varval(17) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
466 itemkey => itemkey,
467 aname => 'CONTRACTOR_REQUISITION_FLAG');
468 t_req_varname(18) := 'VIEW_REQ_DTLS_URL';
469 t_req_varval(18) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
470 itemkey => itemkey,
471 aname => 'VIEW_REQ_DTLS_URL');
472 t_req_varval (18) := t_req_varval(18) || '&' || 'item_key=' || l_item_key;
473 t_req_varname(19) := 'EDIT_REQ_URL';
474 t_req_varval(19) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
475 itemkey => itemkey,
476 aname => 'EDIT_REQ_URL');
477 t_req_varval (19) := t_req_varval(19) || '&' || 'item_key=' || l_item_key;
478 t_req_varname(20) := 'RESUBMIT_REQ_URL';
479 t_req_varval(20) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
480 itemkey => itemkey,
481 aname => 'RESUBMIT_REQ_URL');
482 t_req_varname(21) := 'JUSTIFICATION';
483 t_req_varval(21) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
484 itemkey => itemkey,
485 aname => 'JUSTIFICATION');
486 t_req_varname(22) := 'CONTRACTOR_STATUS';
487 t_req_varval(22) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
488 itemkey => itemkey,
489 aname => 'CONTRACTOR_STATUS');
490 t_req_varname(23) := 'VENDOR_DISPLAY_NAME';
491 t_req_varval(23) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
492 itemkey => itemkey,
493 aname => 'VENDOR_DISPLAY_NAME');
494 t_req_varname(24) := 'IS_SUPPLIER_EMAIL_NOT_AVAIL';
495 t_req_varval(24) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
496 itemkey => itemkey,
497 aname => 'IS_SUPPLIER_EMAIL_NOT_AVAIL');
498 t_req_varname(25) := 'CONTRACTOR_ASSIGNMENT_REQD';
499 t_req_varval(25) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
500 itemkey => itemkey,
501 aname => 'CONTRACTOR_ASSIGNMENT_REQD');
502 else
503 t_po_varname(1) := 'DOCUMENT_TYPE';
504 t_po_varval(1) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
505 itemkey => itemkey,
506 aname => 'DOCUMENT_TYPE');
507 t_po_varname(2) := 'DOCUMENT_SUBTYPE';
508 t_po_varval(2) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
509 itemkey => itemkey,
510 aname => 'DOCUMENT_SUBTYPE');
511 t_po_varname(3) := 'PREPARER_USER_NAME';
512 t_po_varval(3) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
513 itemkey => itemkey,
514 aname => 'PREPARER_USER_NAME');
515 t_po_varname(4) := 'PREPARER_DISPLAY_NAME';
516 t_po_varval(4) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
517 itemkey => itemkey,
518 aname => 'PREPARER_DISPLAY_NAME');
519 t_po_varname(5) := 'FUNCTIONAL_CURRENCY';
520 t_po_varval(5) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
521 itemkey => itemkey,
522 aname => 'FUNCTIONAL_CURRENCY');
523 /*t_po_varname(6) := 'IS_AME_APPROVAL';
524 t_po_varval(6) := 'Y';*/
525 t_po_varname(6) := 'RESUBMIT_DOC_URL';
526 t_po_varval(6) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
527 itemkey => itemkey,
528 aname => 'RESUBMIT_DOC_URL');
529 t_po_varname(7) := 'TOTAL_AMOUNT_DSP';
530 t_po_varval(7) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
531 itemkey => itemkey,
532 aname => 'TOTAL_AMOUNT_DSP');
533 t_po_varname(8) := 'FORWARD_FROM_DISP_NAME';
534 t_po_varval(8) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
535 itemkey => itemkey,
536 aname => 'FORWARD_FROM_DISP_NAME');
537 t_po_varname(9) := 'FORWARD_FROM_USER_NAME';
538 t_po_varval(9) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
539 itemkey => itemkey,
540 aname => 'FORWARD_FROM_USER_NAME');
541 t_po_varname(10) := 'TAX_AMOUNT_CURRENCY_DSP';
542 t_po_varval(10) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
543 itemkey => itemkey,
544 aname => 'TAX_AMOUNT_CURRENCY_DSP');
545 t_po_varname(11) := 'DOCUMENT_NUMBER';
546 t_po_varval(11) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
547 itemkey => itemkey,
548 aname => 'DOCUMENT_NUMBER');
549 t_po_varname(12) := 'AME_TRANSACTION_TYPE';
550 t_po_varval(12) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
551 itemkey => itemkey,
552 aname => 'AME_TRANSACTION_TYPE');
553 t_po_varname(13) := 'OPEN_FORM_COMMAND';
554 t_po_varval(13) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
555 itemkey => itemkey,
556 aname => 'OPEN_FORM_COMMAND');
557 t_po_varname(14) := 'PO_DESCRIPTION';
558 t_po_varval(14) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
559 itemkey => itemkey,
560 aname => 'PO_DESCRIPTION');
561 t_po_varname(15) := 'PO_AMOUNT_DSP';
562 t_po_varval(15) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
563 itemkey => itemkey,
564 aname => 'PO_AMOUNT_DSP');
565 t_po_varname(16) := 'VIEW_DOC_URL';
566 t_po_varval(16) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
567 itemkey => itemkey,
568 aname => 'VIEW_DOC_URL');
569 t_po_varval (16) := t_po_varval(16) || '&' || 'item_key=' || l_item_key;
570 t_po_varname(17) := 'EDIT_DOC_URL';
571 t_po_varval(17) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
572 itemkey => itemkey,
573 aname => 'EDIT_DOC_URL');
574 if(t_po_varval (17) is not NULL) then --Bug 12810660
575 t_po_varval (17) := t_po_varval(17) || '&' || 'item_key=' || l_item_key;
576 end if;
577
578 end if;
579
580
581
582 l_progress := 'Launch_Parallel_Approval: 003 -- Record Type :' || g_next_approvers(l_approver_index).orig_system;
583 IF (g_po_wf_debug = 'Y') THEN
584 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
585 END IF;
586
587 -- Check whether Position Hierarchy or Employee-Sup Hierarchy setup or FND users.
588 if (g_next_approvers(l_approver_index).orig_system = ame_util.perOrigSystem) then
589 l_next_approver_id := g_next_approvers(l_approver_index).orig_system_id;
590 elsif (g_next_approvers(l_approver_index).orig_system = ame_util.posOrigSystem) then
591
592 begin
593
594 -- CLM Apprvl --
595 if l_document_type = 'REQUISITION' then
596 select first_position_id, first_approver_id
597 into l_first_position_id, l_first_approver_id
598 from po_requisition_headers_all
599 where l_document_id = requisition_header_id;
600
601 end if;
602
603
604 if (l_first_position_id is not NULL AND l_first_position_id=g_next_approvers(l_approver_index).orig_system_id ) then
605
606 l_next_approver_id := l_first_approver_id;
607
608 SELECT full_name
609 INTO l_next_approver_name
610 FROM per_all_people_f person
611 WHERE person_id = l_first_approver_id
612 --Bug#7207213#This query fetches multiple records so adding a filter
613 and trunc(sysdate) between person.effective_start_date and nvl(person.effective_end_date, trunc(sysdate));
614
615
616 else
617
618 /* find the persond id from the position_id*/
619 SELECT person_id, full_name into l_next_approver_id,l_next_approver_name FROM (
620 SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
621 WHERE asg.position_id = g_next_approvers(l_approver_index).orig_system_id and trunc(sysdate) between person.effective_start_date
622 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
623 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
624 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
625 and asg.assignment_status_type_id not in (
626 SELECT assignment_status_type_id FROM per_assignment_status_types
627 WHERE per_system_status = 'TERM_ASSIGN'
628 ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
629 ) where rownum = 1;
630
631 end if;
632
633 exception
634 WHEN NO_DATA_FOUND THEN
635 RAISE;
636 END;
637
638 elsif (g_next_approvers(l_approver_index).orig_system = ame_util.fndUserOrigSystem) then
639 SELECT employee_id
640 into l_next_approver_id
641 FROM fnd_user
642 WHERE user_id = g_next_approvers(l_approver_index).orig_system_id
643 and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
644 end if;
645
646 t_req_varname(26) := 'AME_APPROVER_TYPE';
647 t_req_varval(26) := g_next_approvers(l_approver_index).orig_system;
648
649 t_po_varname(18) := 'AME_APPROVER_TYPE';
650 t_po_varval(18) := g_next_approvers(l_approver_index).orig_system;
651
652
653
654 WF_DIRECTORY.GetUserName(ame_util.perOrigSystem, l_next_approver_id, l_next_approver_user_name, l_next_approver_disp_name);
655
656 l_progress := 'Launch_Parallel_Approval: 004 -- Next Approver User Name -- display Name:' || l_next_approver_user_name || ' -- ' || l_next_approver_disp_name;
657 IF (g_po_wf_debug = 'Y') THEN
658 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
659 END IF;
660 --bug6843383 start
661 IF (g_next_approvers(l_approver_index).orig_system = ame_util.perOrigSystem) then
662 t_req_varname(27) := 'APPROVER_USER_NAME';
663 t_req_varval(27) := g_next_approvers(l_approver_index).name;
664
665 t_po_varname(19) := 'APPROVER_USER_NAME';
666 t_po_varval(19) := g_next_approvers(l_approver_index).name;
667
668 t_req_varname(28) := 'APPROVER_DISPLAY_NAME';
669 t_req_varval(28) := g_next_approvers(l_approver_index).display_name;
670
671 t_po_varname(20) := 'APPROVER_DISPLAY_NAME';
672 t_po_varval(20) := g_next_approvers(l_approver_index).display_name;
673
674 ELSE
675
676 t_req_varname(27) := 'APPROVER_USER_NAME';
677 t_req_varval(27) := l_next_approver_user_name;
678
679 t_req_varname(28) := 'APPROVER_DISPLAY_NAME';
680 t_req_varval(28) := l_next_approver_disp_name;
681
682 t_po_varname(19) := 'APPROVER_USER_NAME';
683 t_po_varval(19) := l_next_approver_user_name;
684
685 t_po_varname(20) := 'APPROVER_DISPLAY_NAME';
686 t_po_varval(20) := l_next_approver_disp_name;
687 END IF;
688 /* Kick off the process */
689 l_progress:= '30: start_wf_line_process: Kicking off StartProcess';
690 IF (g_po_wf_debug = 'Y') THEN
691 po_wf_debug_pkg.insert_debug(itemtype,itemkey,l_progress);
692 END IF;
693 t_req_varname(29) := 'AME_IS_FYI_APPROVER';
694 t_po_varname(21) := 'IS_FYI_APPROVER';
695 if (g_next_approvers(l_approver_index).approver_category = ame_util.fyiApproverCategory) then
696 t_req_varval(29) :='Y';
697 t_po_varval(21) := 'Y';
698 l_has_fyi_app := 'Y';
699 l_start_block_activity := 'N';
700 else
701 t_req_varval(29) :='N';
702 t_po_varval(21) := 'N';
703
704 if (l_has_fyi_app = 'N') then
705 -- only start BLOCK if there are no FYI approvers
706 l_start_block_activity := 'Y';
707 end if;
708
709 end if;
710 if l_document_type <> 'REQUISITION' then
711
712 t_po_varname(22) := 'DOCUMENT_TYPE_DISP';
713 t_po_varval(22) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
714 itemkey => itemkey,
715 aname => 'DOCUMENT_TYPE_DISP');
716
717
718 t_po_varname(23) := 'REQUIRES_APPROVAL_MSG';
719 t_po_varval(23) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
720 itemkey => itemkey,
721 aname => 'REQUIRES_APPROVAL_MSG');
722
723
724 t_po_varname(24) := 'WRONG_FORWARD_TO_MSG';
725 t_po_varval(24) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
726 itemkey => itemkey,
727 aname => 'WRONG_FORWARD_TO_MSG');
728
729
730 t_po_varname(25) := 'OPERATING_UNIT_NAME';
731 t_po_varval(25) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
732 itemkey => itemkey,
733 aname => 'OPERATING_UNIT_NAME');
734
735 t_po_varname(26) := 'NOTE';
736 t_po_varval(26) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
737 itemkey => itemkey,
738 aname => 'NOTE');
739
740
741 t_po_varname(27) := 'PO_LINES_DETAILS';
742 t_po_varval(27) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
743 itemkey => itemkey,
744 aname => 'PO_LINES_DETAILS');
745
746
747 t_po_varname(28) := 'DOCUMENT_SUBTYPE_DISP';
748 t_po_varval(28) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
749 itemkey => itemkey,
750 aname => 'DOCUMENT_SUBTYPE_DISP');
751
752
753 t_po_varname(29) := 'ACTION_HISTORY';
754 t_po_varval(29) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
755 itemkey => itemkey,
756 aname => 'ACTION_HISTORY');
757
758 t_po_varname(30) := 'PO_APPROVE_MSG';
759 t_po_varval(30) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
760 itemkey => itemkey,
761 aname => 'PO_APPROVE_MSG');
762
763
764 t_po_varname(31) := 'SUPPLIER';
765 t_po_varval(31) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
766 itemkey => itemkey,
767 aname => 'SUPPLIER');
768
769
770 t_po_varname(32) := 'SUPPLIER_SITE';
771 t_po_varval(32) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
772 itemkey => itemkey,
773 aname => 'SUPPLIER_SITE');
774
775
776 t_po_varname(33) := 'FUNDED_TITLE_DSP';
777 t_po_varval(33) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
778 itemkey => itemkey,
779 aname => 'FUNDED_TITLE_DSP');
780
781
782 t_po_varname(34) := 'FUNDED_AMOUNT_DISPLAY';
783 t_po_varval(34) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
784 itemkey => itemkey,
785 aname => 'FUNDED_AMOUNT_DISPLAY');
786
787 t_po_varname(35) := 'AUTHORIZATION_STATUS';
788 t_po_varval(35) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
789 itemkey => itemkey,
790 aname => 'AUTHORIZATION_STATUS');
791
792 t_po_varname(36) := 'WITH_TERMS';
793 t_po_varval(36) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
794 itemkey => itemkey,
795 aname => 'WITH_TERMS');
796
797 t_po_varname(37) := 'LANGUAGE_CODE';
798 t_po_varval(37) := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => itemtype,
799 itemkey => itemkey,
800 aname=>'LANGUAGE_CODE');
801
802 --multi-mod changes start
803 t_po_varname(38) := 'SUPPLIER_CHANGE_NOTE';
804 t_po_varval(38) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
805 itemkey => itemkey,
806 aname => 'SUPPLIER_CHANGE_NOTE');
807 t_po_varname(39) := 'SUPPLIER_SITE_CHANGE_NOTE';
808 t_po_varval(39) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
809 itemkey => itemkey,
810 aname => 'SUPPLIER_SITE_CHANGE_NOTE');
811 t_po_varname(40) := 'VALIDATION_DETAILS_URL';
812 t_po_varval(40) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
813 itemkey => itemkey,
814 aname => 'VALIDATION_DETAILS_URL');
815 t_po_varname(41) := 'APPROVAL_SOURCE';
816 t_po_varval(41) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
817 itemkey => itemkey,
818 aname => 'APPROVAL_SOURCE');
819 --multi-mod changes end
820 -- PAR Approval
821 t_po_varname(42) := 'DRAFT_TYPE';
822 t_po_varval(42) := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
823 itemkey => itemkey,
824 aname => 'DRAFT_TYPE');
825
826
827 end if;
828 -- Set the item attributes.
829 -- CLM Apprvl --
830 if l_document_type = 'REQUISITION' then
831 Wf_Engine.SetItemAttrTextArray(itemtype, l_item_key,t_req_varname,t_req_varval);
832 else
833
834
835 Wf_Engine.SetItemAttrTextArray(itemtype, l_item_key,t_po_varname,t_po_varval);
836 end if;
837
838 l_progress:= 'after setting text array';
839 IF (g_po_wf_debug = 'Y') THEN
840 po_wf_debug_pkg.insert_debug(itemtype,itemkey,l_progress);
841 END IF;
842
843 n_varname(1) := 'DOCUMENT_ID';
844 n_varval(1) := l_document_id;
845
846 n_varname(2) := 'ORG_ID';
847 n_varval(2) := l_org_id;
848
849 n_varname(3) := 'AME_APPROVER_ID';
850 n_varval(3) := g_next_approvers(l_approver_index).orig_system_id;
851
852 n_varname(4) := 'APPROVER_EMPID';
853 n_varval(4) := l_next_approver_id;
854
855 -- Set the approval group id as 1 for adhoc approvers
856 n_varname(5) := 'APPROVAL_GROUP_ID';
857
858
859 l_progress:= 'g_next_approvers(l_approver_index).api_insertion: '||g_next_approvers(l_approver_index).api_insertion;
860 IF (g_po_wf_debug = 'Y') THEN
861 po_wf_debug_pkg.insert_debug(itemtype,itemkey,l_progress);
862 END IF;
863 if (g_next_approvers(l_approver_index).api_insertion = 'Y') then
864 n_varval(5) := 1;
865 else
866 n_varval(5) := g_next_approvers(l_approver_index).group_or_chain_id;
867 end if;
868
869 --Bug: 9877170 Setting Responsibility_id and Application_id
870 n_varname(6) := 'RESPONSIBILITY_ID';
871 n_varval(6) := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
872 itemkey => itemkey,
873 aname => 'RESPONSIBILITY_ID');
874 n_varname(7) := 'APPLICATION_ID';
875 n_varval(7) := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
876 itemkey => itemkey,
877 aname => 'APPLICATION_ID');
878 l_progress := 'Launch_Parallel_Approval - setting responsibility_id:'||n_varval(6)||' and application_id:'||n_varval(7);
879 IF (g_po_wf_debug = 'Y') THEN
880 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
881 END IF;
882
883 if l_document_type <> 'REQUISITION' then
884 n_varname(8) := 'AME_TRANSACTION_ID';
885 n_varval(8) := l_ame_transaction_id;
886 n_varname(9) := 'DRAFT_ID';
887 n_varval(9) := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
888 itemkey => itemkey,
889 aname => 'DRAFT_ID');
890 n_varname(10) := 'PO_REVISION_NUM';
891 n_varval(10) := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
892 itemkey => itemkey,
893 aname => 'PO_REVISION_NUM');
894
895 n_varname(11) := 'REVISION_NUMBER';
896 n_varval(11) := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
897 itemkey => itemkey,
898 aname => 'REVISION_NUMBER');
899
900 end if;
901
902
903 Wf_Engine.SetItemAttrNumberArray(itemtype, l_item_key,n_varname,n_varval);
904
905 wf_engine.SetItemAttrDocument( itemtype => itemtype,
906 itemkey => l_item_key,
907 aname => 'ATTACHMENT',
908 documentid => ( wf_engine.GetItemAttrDocument( itemtype => itemtype,
909 itemkey => itemkey,
910 aname => 'ATTACHMENT')));
911 po_wf_util_pkg.SetItemAttrText(itemtype => itemtype,
912 itemkey => l_item_key,
913 aname => 'PDF_ATTACHMENT_BUYER',
914 avalue => 'PLSQLBLOB:PO_COMMUNICATION_PVT.PDF_ATTACH_APP/'|| itemtype||':'||l_item_key);
915
916
917 l_progress := 'Launch_Parallel_Approval: 005 -- Launch Parallel Approval';
918 IF (g_po_wf_debug = 'Y') THEN
919 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
920 END IF;
921
922
923 wf_engine.StartProcess( itemtype => itemtype,
924 itemkey => l_item_key );
925
926 l_approver_index := g_next_approvers.next(l_approver_index);
927 end loop; -- end of for loop.
928
929 if l_start_block_activity = 'Y' then
930 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
931 else
932 resultout:='COMPLETE' || ':' || '';
933 end if;
934 g_next_approvers.delete;
935
936 RETURN;
937
938 END IF; --run mode
939
940 exception
941 when NO_DATA_FOUND then
942 l_progress:= '50: start_wf_line_process: NO_DATA_FOUND -- EXCEPTION';
943 l_doc_string := l_progress || PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
944 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
945 wf_core.context('POR_AME_REQ_WF_PVT','Launch_Parallel_Approval-NO_DATA_FOUND Exception:',l_progress,sqlerrm);
946
947 IF (g_po_wf_debug = 'Y') THEN
948 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );
949 END IF;
950 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.GET_NEXT_APPROVER');
951 raise;
952 when others then
953 l_progress:= '50: start_wf_line_process: IN EXCEPTION';
954 l_doc_string := l_progress || PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
955 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
956 wf_core.context('POR_AME_REQ_WF_PVT','Launch_Parallel_Approval-Unexpected Exception:',l_progress,sqlerrm);
957
958 IF (g_po_wf_debug = 'Y') THEN
959 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );
960 END IF;
961
962 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.GET_NEXT_APPROVER');
963 raise;
964
965 end Launch_Parallel_Approval;
966
967
968 --------------------------------------------------------------------------------
969 --Start of Comments
970 --Name: Process_Response_Internal
971 --Pre-reqs:
972 -- None.
973 --Modifies:
974 -- None.
975 --Locks:
976 -- None.
977 --Function:
978 -- Workflow activity PL/SQL handler. This procedure is used to inform AME about the approvers response.
979 --Parameters:
980 --IN:
981 -- Standard workflow IN parameters
982 --OUT:
983 -- Standard workflow OUT parameters
984 --Testing:
985 --
986 --End of Comments
987 -------------------------------------------------------------------------------
988 procedure Process_Response_Internal( itemtype in varchar2,
989 itemkey in varchar2,
990 p_response in varchar2 ) IS
991
992 l_progress VARCHAR2(500) := '000';
993 l_document_id number;
994 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
995 l_current_approver ame_util.approverRecord2;
996 l_forwardee ame_util.approverRecord2; -- CLM Apprvl
997 l_approver_posoition_id number;
998 l_approver_type varchar2(10);
999 l_error_code NUMBER;
1000 l_error_message ame_util.longestStringType;
1001 l_parent_item_type wf_items.parent_item_type%TYPE;
1002 l_parent_item_key wf_items.parent_item_key%TYPE;
1003 l_document_type PO_DOCUMENT_TYPES.document_type_code%TYPE; --<CLM code>
1004 l_ame_transaction_id PO_HEADERS_ALL.ame_approval_id%TYPE; --<CLM code>
1005 begin
1006
1007 l_progress := 'Process_Response_Internal: 001';
1008 IF (g_po_wf_debug = 'Y') THEN
1009 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1010 END IF;
1011
1012 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1013 itemkey => itemkey,
1014 aname => 'DOCUMENT_ID');
1015 --<CLM code>
1016 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1017 itemkey => itemkey,
1018 aname => 'DOCUMENT_TYPE');
1019
1020
1021 if l_document_type <> 'REQUISITION' then
1022 l_ame_transaction_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1023 itemkey => itemkey,
1024 aname => 'AME_TRANSACTION_ID');
1025
1026
1027 end if;
1028 --<CLM code end>
1029
1030 l_transaction_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1031 itemkey => itemkey,
1032 aname => 'AME_TRANSACTION_TYPE');
1033
1034 l_approver_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1035 itemkey => itemkey,
1036 aname => 'AME_APPROVER_TYPE');
1037
1038 l_progress := 'Process_Response_Internal: 002 -- l_approver_type :' || l_approver_type||
1039 ', l_ame_transaction_id: '||l_ame_transaction_id||',l_transaction_type: '||l_transaction_type ;
1040 IF (g_po_wf_debug = 'Y') THEN
1041 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1042 END IF;
1043
1044 if (l_approver_type = ame_util.posOrigSystem) then
1045 l_current_approver.orig_system := ame_util.posOrigSystem;
1046 l_forwardee.orig_system := ame_util.posOrigSystem;
1047 elsif (l_approver_type = ame_util.fndUserOrigSystem) then
1048 l_current_approver.orig_system := ame_util.fndUserOrigSystem;
1049 l_forwardee.orig_system := ame_util.fndUserOrigSystem;
1050 else
1051 l_current_approver.orig_system := ame_util.perOrigSystem;
1052 l_current_approver.name := po_wf_util_pkg.GetItemAttrText( itemtype => itemType,
1053 itemkey => itemkey,
1054 aname => 'APPROVER_USER_NAME');
1055 -- CLM Apprvl
1056 l_forwardee.orig_system := ame_util.perOrigSystem;
1057 l_forwardee.name := po_wf_util_pkg.GetItemAttrText( itemtype => itemType,
1058 itemkey => itemkey,
1059 aname => 'FORWARD_TO_USERNAME_RESPONSE');
1060
1061 end if;
1062
1063 l_current_approver.orig_system_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1064 itemkey => itemkey,
1065 aname => 'AME_APPROVER_ID');
1066
1067
1068
1069 l_progress := 'Process_Response_Internal: 003 -- l_current_approver.orig_system_id :' ||
1070 l_current_approver.orig_system_id||', l_current_approver.name: '||l_current_approver.name ;
1071 IF (g_po_wf_debug = 'Y') THEN
1072 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1073 END IF;
1074
1075 if( p_response = 'APPROVE') then
1076 l_current_approver.approval_status := ame_util.approvedStatus;
1077 elsif( p_response = 'REJECT') then
1078 l_current_approver.approval_status := ame_util.rejectStatus;
1079 elsif( p_response = 'TIMEOUT') then
1080 l_current_approver.approval_status := ame_util.noResponseStatus;
1081 elsif( p_response = 'FORWARD') then -- CLM Apprvl
1082 l_current_approver.approval_status := ame_util.forwardStatus;
1083 l_progress := 'Process_Response_Internal: 004 -- l_forwardee.orig_system_id :' ||
1084 l_forwardee.orig_system_id||', l_forwardee.name: '||l_forwardee.name ;
1085 IF (g_po_wf_debug = 'Y') THEN
1086 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1087 END IF;
1088 elsif( p_response = 'APPROVE AND FORWARD') then -- CLM Apprvl
1089 l_current_approver.approval_status := ame_util.approveAndForwardStatus;
1090 --CLM bug11736458 - exception response must be sent back to AME in case of error during PDF generation in Parallel approval process.
1091 elsif( p_response = 'EXCEPTION') then
1092 l_current_approver.approval_status := ame_util.exceptionStatus;
1093 end if;
1094
1095 l_progress := 'Process_Response_Internal: 005 -- p_response :' || p_response ;
1096 IF (g_po_wf_debug = 'Y') THEN
1097 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1098 END IF;
1099
1100 -- Get the name value for the approverRecord2.
1101 -- This is a mandatory field. If we do not pass this value to AME, we will get invalid parameter exception.
1102 -- bug# 4936145
1103 --CLM Apprvl
1104 IF p_response IN ('FORWARD', 'APPROVE AND FORWARD') THEN
1105 IF l_forwardee.name IS NULL THEN
1106 SELECT name into l_forwardee.name FROM
1107 ( SELECT name FROM wf_roles WHERE orig_system = l_forwardee.orig_system
1108 and orig_system_id = l_forwardee.orig_system_id
1109 order by start_date)
1110 WHERE rownum = 1;
1111 END IF;
1112 ELSE
1113 IF l_current_approver.name IS NULL THEN
1114 SELECT name into l_current_approver.name FROM
1115 ( SELECT name FROM wf_roles WHERE orig_system = l_current_approver.orig_system
1116 and orig_system_id = l_current_approver.orig_system_id
1117 order by start_date)
1118 WHERE rownum = 1;
1119 END IF;
1120 END IF;
1121
1122 IF l_current_approver.name IS NULL THEN
1123 raise_application_error(-20001, 'Record Not Found in WF_ROLES for the orig_system_id :' ||
1124 l_current_approver.orig_system_id || ' -- orig_system :' ||
1125 l_current_approver.orig_system );
1126 END IF;
1127
1128 -- Update the Approval status with the response from the approver.
1129 --<CLM code> Introduced the condition to check the document type and pass the ame transaction is accordingly
1130 if l_document_type = 'REQUISITION' then
1131 ame_api2.updateApprovalStatus( applicationIdIn=>applicationId,
1132 transactionIdIn=>l_document_id,
1133 transactionTypeIn=>l_transaction_type,
1134 approverIn => l_current_approver);
1135 elsif p_response IN ('FORWARD', 'APPROVE AND FORWARD') then
1136 ame_api2.updateApprovalStatus(applicationIdIn=>applicationId,
1137 transactionIdIn=>l_ame_transaction_id,
1138 transactionTypeIn=>l_transaction_type,
1139 approverIn => l_current_approver,
1140 forwardeeIn =>l_forwardee);
1141 else
1142 ame_api2.updateApprovalStatus(applicationIdIn=>applicationId,
1143 transactionIdIn=>l_ame_transaction_id,
1144 transactionTypeIn=>l_transaction_type,
1145 approverIn => l_current_approver);
1146 end if;
1147
1148 exception
1149 when others then
1150 l_error_code := SQLCODE;
1151 /* Get the sql code of the exception. IF code is -20001 then this is a valid AME exception .We need to
1152 get the exception text and store it in an attribute */
1153 IF l_error_code = -20001 THEN
1154 l_error_message := SQLERRM;
1155
1156 SELECT parent_item_type, parent_item_key
1157 into l_parent_item_type, l_parent_item_key
1158 FROM wf_items
1159 WHERE item_type = itemtype and item_key = itemkey;
1160
1161 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1162 itemkey => l_parent_item_key,
1163 aname => 'AME_EXCEPTION',
1164 avalue => l_error_message );
1165
1166 ELSE -- If not valid excpetion , just raise the error
1167 raise;
1168 END IF;
1169 end Process_Response_Internal;
1170
1171
1172 --------------------------------------------------------------------------------
1173 --Name: Process_Beat_By_First
1174 --Pre-reqs:
1175 -- None.
1176 --Modifies:
1177 -- None.
1178 --Locks:
1179 -- None.
1180 --Function:
1181 -- Workflow activity PL/SQL handler.
1182 -- This procedure handles the stopping of workflow and the updating of the
1183 -- action history table in the case of approvers being beat by first
1184 -- responder.
1185 --Parameters:
1186 --IN:
1187 -- Standard workflow IN parameters
1188 --OUT:
1189 -- Standard workflow OUT parameters
1190 --Testing:
1191 --
1192 --End of Comments
1193 -------------------------------------------------------------------------------
1194 procedure Process_Beat_By_First( itemtype in varchar2,
1195 itemkey in varchar2,
1196 actid in number,
1197 funcmode in varchar2,
1198 resultout out NOCOPY varchar2) IS
1199
1200 l_progress VARCHAR2(500) := '000';
1201 l_parent_item_type wf_items.parent_item_type%TYPE;
1202 l_parent_item_key wf_items.parent_item_key%TYPE;
1203
1204 l_child_approver_empid NUMBER;
1205 l_child_approver_groupid NUMBER;
1206
1207 l_approver_group_id NUMBER;
1208 l_req_header_id NUMBER;
1209 l_process_out VARCHAR2(10);
1210 approverList ame_util.approversTable2;
1211 ameTransactionType po_document_types.ame_transaction_type%TYPE;
1212 l_response_action VARCHAR2(20);
1213 l_note VARCHAR2(4000);
1214 l_person_id NUMBER;
1215 l_orig_system VARCHAR2(3);
1216 l_orig_system_id NUMBER;
1217 l_first_approver_id NUMBER := null;
1218 l_first_position_id NUMBER := null;
1219
1220 l_preparer_user_name fnd_user.user_name%TYPE;
1221 l_doc_string VARCHAR2(200);
1222 l_ame_exception ame_util.longestStringType;
1223 l_approver_response varchar2(20);
1224 l_transaction_type po_document_types.ame_transaction_type%TYPE; --<CLM code>
1225 l_ame_transaction_id NUMBER; --<CLM code>
1226 l_document_type PO_DOCUMENT_TYPES.document_type_code%TYPE; --<CLM code>
1227
1228 CURSOR l_child_wf (itemtype IN wf_items.parent_item_type%TYPE,itemkey IN wf_items.parent_item_key%TYPE) IS
1229 SELECT wfi.item_type, wfi.item_key,wfn.recipient_role, wfn.original_recipient
1230 FROM wf_items wfi,wf_item_activity_statuses wfias,wf_notifications wfn
1231 WHERE wfi.parent_item_key =itemkey
1232 and wfi.item_type=itemtype
1233 AND wfias.item_type=wfi.item_type
1234 AND wfias.item_key=wfi.item_key
1235 AND wfias.activity_status='NOTIFIED'
1236 AND wfias.notification_id IS NOT null
1237 AND wfias.notification_id = wfn.notification_id;
1238 l_child_wf_cur l_child_wf%ROWTYPE;
1239
1240 l_draft_id NUMBER; --Bug 12944203
1241 l_document_subtype PO_DOCUMENT_TYPES.DOCUMENT_SUBTYPE%TYPE; --Bug 13444730
1242 l_approver_disp_name VARCHAR2(200) := null;
1243 begin
1244
1245 l_progress := 'Process_Beat_By_First: 001';
1246 IF (g_po_wf_debug = 'Y') THEN
1247 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1248 END IF;
1249
1250 SELECT parent_item_type, parent_item_key
1251 into l_parent_item_type, l_parent_item_key
1252 FROM wf_items
1253 WHERE item_type = itemtype and item_key = itemkey;
1254
1255 /* Check if there we have encountered any ame exception.
1256 If the value of ame_exception is not null, then we have faced some exception.
1257 So just comlete the block activity and return */
1258 l_ame_exception := po_wf_util_pkg.GetItemAttrText( itemtype => l_parent_item_type,
1259 itemkey => l_parent_item_key,
1260 aname => 'AME_EXCEPTION' );
1261
1262 IF l_ame_exception IS NOT NULL THEN
1263 wf_engine.CompleteActivity( itemtype => l_parent_item_type,
1264 itemkey => l_parent_item_key,
1265 activity => 'BLOCK',
1266 result => null);
1267 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1268 RETURN;
1269 END IF;
1270
1271 l_progress := 'Process_Beat_By_First: 002';
1272 IF (g_po_wf_debug = 'Y') THEN
1273 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1274 END IF;
1275
1276 --Bug 12944203
1277 l_approver_group_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1278 itemkey => itemkey,
1279 aname => 'APPROVAL_GROUP_ID');
1280
1281
1282 l_req_header_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1283 itemkey => itemkey,
1284 aname => 'DOCUMENT_ID');
1285
1286 l_approver_response := po_wf_util_pkg.GetItemAttrText(itemtype => itemtype,
1287 itemkey => itemkey,
1288 aname => 'APPROVER_RESPONSE');
1289
1290 -- CLM Apprvl --
1291 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1292 itemkey => itemkey,
1293 aname => 'DOCUMENT_TYPE');
1294
1295 --Bug 13444730
1296 l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
1297 itemkey => itemkey,
1298 aname => 'DOCUMENT_SUBTYPE');
1299
1300 if l_document_type <> 'REQUISITION' then
1301 l_ame_transaction_id := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1302 itemkey => itemkey,
1303 aname => 'AME_TRANSACTION_ID');
1304
1305 l_approver_disp_name := po_wf_util_pkg.GetItemAttrText ( itemtype => itemtype,
1306 itemkey => itemkey,
1307 aname => 'APPROVER_DISPLAY_NAME');
1308
1309 end if;
1310
1311
1312 l_transaction_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1313 itemkey => itemkey,
1314 aname => 'AME_TRANSACTION_TYPE');
1315
1316 -- CLM Apprvl --
1317 if l_document_type = 'REQUISITION' then
1318 select first_position_id, first_approver_id
1319 into l_first_position_id, l_first_approver_id
1320 from po_requisition_headers_all
1321 where l_req_header_id = requisition_header_id;
1322
1323 end if;
1324
1325
1326 l_progress := 'Process_Beat_By_First: 003';
1327 IF (g_po_wf_debug = 'Y') THEN
1328 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1329 END IF;
1330
1331 if l_approver_response = 'APPROVED' then
1332
1333 if l_document_type = 'REQUISITION' then
1334 por_ame_approval_list.getAmeTransactionType(pReqHeaderId => l_req_header_id,
1335 pAmeTransactionType => ameTransactionType);
1336
1337 l_progress := 'Process_Beat_By_First: 004, ameTransactionType: '||ameTransactionType;
1338 IF (g_po_wf_debug = 'Y') THEN
1339 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1340 END IF;
1341
1342 -- Note for Approve
1343 l_note := fnd_message.get_string('ICX', 'ICX_POR_REQ_ALREADY_APPROVED');
1344
1345 ame_api2.getAllApprovers7( applicationIdIn => applicationId,
1346 transactionIdIn => l_req_header_id,
1347 transactionTypeIn => ameTransactionType,
1348 approvalProcessCompleteYNOut => l_process_out,
1349 approversOut => approverList
1350 );
1351 else
1352 l_progress := 'Process_Beat_By_First: 004, l_ame_transaction_id: '||l_ame_transaction_id||
1353 ', l_ame_transaction_type: '||l_transaction_type;
1354 IF (g_po_wf_debug = 'Y') THEN
1355 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1356 END IF;
1357
1358 --Bug 12944203
1359
1360 fnd_message.set_name('PO', 'PO_ALREADY_APPROVED');
1361 fnd_message.set_token('PERSON_NAME', l_approver_disp_name);
1362 l_note := fnd_message.get;
1363
1364 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1365 itemkey => itemkey,
1366 aname => 'DRAFT_ID');
1367
1368 ame_api2.getAllApprovers7( applicationIdIn => applicationId,
1369 transactionIdIn => l_ame_transaction_id,
1370 transactionTypeIn => l_transaction_type,
1371 approvalProcessCompleteYNOut => l_process_out,
1372 approversOut => approverList
1373 );
1374 end if; --end l_document_type = 'REQUISITION'
1375
1376
1377 l_progress := 'Process_Beat_By_First: 005';
1378 IF (g_po_wf_debug = 'Y') THEN
1379 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1380 END IF;
1381
1382 -- Once we get the approvers list from AME, we iterate through the approvers list,
1383 -- to find out the current first authority approver.
1384 for i in 1 .. approverList.count loop
1385
1386 IF (g_po_wf_debug = 'Y') THEN
1387 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1388
1389 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, approverList(i).orig_system || to_char(i) || ' ' ||
1390 approverList(i).orig_system_id);
1391 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'authority' || ' ' || approverList(i).authority);
1392 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'approval_status'|| ' ' || approverList(i).approval_status);
1393 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'api_insertion'|| ' ' || approverList(i).api_insertion);
1394 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'group_or_chain_id' || ' ' || approverList(i).group_or_chain_id);
1395
1396 END IF;
1397
1398 if( approverList(i).approval_status = ame_util.beatByFirstResponderStatus
1399 and approverList(i).api_insertion = ame_util.oamGenerated
1400 and approverList(i).group_or_chain_id = l_approver_group_id) then
1401
1402 l_orig_system := approverList(i).orig_system;
1403 l_orig_system_id := approverList(i).orig_system_id;
1404
1405 if ( l_orig_system = ame_util.perOrigSystem) then
1406
1407 -- Employee Supervisor Record.
1408 l_person_id := l_orig_system_id;
1409
1410 elsif ( l_orig_system = ame_util.posOrigSystem) then
1411
1412 -- Position Hierarchy Record.
1413 begin
1414
1415 if (l_first_position_id is not NULL AND l_first_position_id = l_orig_system_id) then
1416 l_person_id := l_first_approver_id;
1417
1418 else
1419 SELECT person_id into l_person_id FROM (
1420 SELECT person.person_id FROM per_all_people_f person, per_all_assignments_f asg
1421 WHERE asg.position_id = l_orig_system_id and trunc(sysdate) between person.effective_start_date
1422 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
1423 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
1424 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
1425 and asg.assignment_status_type_id not in (
1426 SELECT assignment_status_type_id FROM per_assignment_status_types
1427 WHERE per_system_status = 'TERM_ASSIGN'
1428 ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
1429 ) where rownum = 1;
1430
1431 end if;
1432
1433 exception
1434 WHEN NO_DATA_FOUND THEN
1435 l_person_id := -1;
1436 end;
1437
1438 elsif (l_orig_system = ame_util.fndUserOrigSystem) then
1439
1440 -- FND User Record.
1441 SELECT employee_id into l_person_id
1442 FROM fnd_user
1443 WHERE user_id = l_orig_system_id
1444 and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
1445
1446 end if;
1447
1448 -- stop the workflow
1449 OPEN l_child_wf(l_parent_item_type, l_parent_item_key);
1450
1451 LOOP
1452 FETCH l_child_wf INTO l_child_wf_cur;
1453 EXIT WHEN l_child_wf%NOTFOUND;
1454
1455 l_child_approver_empid := po_wf_util_pkg.GetItemAttrNumber( itemtype => l_child_wf_cur.item_type,
1456 itemkey => l_child_wf_cur.item_key,
1457 aname => 'APPROVER_EMPID');
1458
1459 l_child_approver_groupid := po_wf_util_pkg.GetItemAttrNumber( itemtype => l_child_wf_cur.item_type,
1460 itemkey => l_child_wf_cur.item_key,
1461 aname => 'APPROVAL_GROUP_ID');
1462
1463 -- Bug 10043085 extra condition is added to check whether the notification is a delegated one.
1464 IF ((l_child_approver_empid = l_person_id OR (l_child_wf_cur.recipient_role <> l_child_wf_cur.original_recipient)) and
1465 l_child_approver_groupid = l_approver_group_id) THEN
1466
1467 wf_engine.AbortProcess(l_child_wf_cur.item_type ,l_child_wf_cur.item_key);
1468 EXIT;
1469
1470 END IF;
1471
1472 END LOOP;
1473
1474 CLOSE l_child_wf;
1475
1476 --Bug 12944203
1477
1478 if l_document_type = 'REQUISITION' then
1479 -- update the action history table
1480 UpdateActionHistory(l_req_header_id, 'NO ACTION',
1481 l_note, l_person_id);
1482 ELSE
1483 --Bug 13444730 Passing document_type and document_subtype
1484 UpdateActionHistoryPo(l_transaction_type,l_req_header_id, l_draft_id,
1485 l_document_type,l_document_subtype, 'NO ACTION',
1486 l_note, l_person_id);
1487
1488 END IF;
1489
1490 end if;
1491 end loop;
1492 --Bug15881165 Begin
1493 OPEN l_child_wf(l_parent_item_type, l_parent_item_key);
1494 LOOP
1495 FETCH l_child_wf INTO l_child_wf_cur;
1496 EXIT WHEN l_child_wf%NOTFOUND;
1497
1498 -- Get the approver id as the person id to update the action history
1499 l_person_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => l_child_wf_cur.item_type,
1500 itemkey => l_child_wf_cur.item_key,
1501 aname => 'APPROVER_EMPID');
1502
1503 l_progress := 'Process_Beat_By_First: 0051';
1504 IF (g_po_wf_debug = 'Y') THEN
1505 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1506 END IF;
1507
1508 wf_engine.AbortProcess(l_child_wf_cur.item_type ,l_child_wf_cur.item_key);
1509
1510 -- update the action history table with the ICX_POR_REQ_ALREADY_APPROVED note
1511 UpdateActionHistory(l_req_header_id, 'NO ACTION',
1512 l_note, l_person_id);
1513
1514 l_progress := 'Process_Beat_By_First: 0052';
1515 IF (g_po_wf_debug = 'Y') THEN
1516 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1517 END IF;
1518
1519 END LOOP;
1520 CLOSE l_child_wf;
1521 --Bug15881165 End
1522 elsif (l_approver_response = 'REJECTED') then
1523 OPEN l_child_wf(l_parent_item_type, l_parent_item_key);
1524
1525 LOOP
1526 FETCH l_child_wf INTO l_child_wf_cur;
1527 EXIT WHEN l_child_wf%NOTFOUND;
1528
1529 -- Get the approver id as the person id to update the action history
1530 l_person_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => l_child_wf_cur.item_type,
1531 itemkey => l_child_wf_cur.item_key,
1532 aname => 'APPROVER_EMPID');
1533
1534 l_progress := 'Process_Beat_By_First: 006';
1535 IF (g_po_wf_debug = 'Y') THEN
1536 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1537 END IF;
1538
1539 wf_engine.AbortProcess(l_child_wf_cur.item_type ,l_child_wf_cur.item_key);
1540
1541 -- update the action history table
1542 --Bug 12944203
1543 if l_document_type = 'REQUISITION' THEN
1544 l_note := fnd_message.get_string('ICX', 'ICX_POR_REQ_ALREADY_REJECTED');
1545 UpdateActionHistory(l_req_header_id, 'NO ACTION',
1546 l_note, l_person_id);
1547 ELSE
1548 fnd_message.set_name('PO', 'PO_ALREADY_REJECTED');
1549 fnd_message.set_token('PERSON_NAME', l_approver_disp_name);
1550 l_note := fnd_message.get;
1551
1552 --Bug 13444730 Passing document_type and document_subtype
1553 UpdateActionHistoryPo(l_transaction_type,l_req_header_id, l_draft_id,
1554 l_document_type,l_document_subtype, 'NO ACTION',
1555 l_note, l_person_id);
1556
1557 END IF;
1558
1559 l_progress := 'Process_Beat_By_First: 007';
1560 IF (g_po_wf_debug = 'Y') THEN
1561 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1562 END IF;
1563
1564 END LOOP;
1565
1566 CLOSE l_child_wf;
1567
1568 end if;
1569
1570 l_progress := 'Process_Beat_By_First: 008';
1571 IF (g_po_wf_debug = 'Y') THEN
1572 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1573 END IF;
1574
1575 wf_engine.CompleteActivity( itemtype => l_parent_item_type,
1576 itemkey => l_parent_item_key,
1577 activity => 'BLOCK',
1578 result => null);
1579
1580 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1581 RETURN;
1582
1583 exception
1584 when others then
1585 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1586 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1587 wf_core.context('POR_AME_REQ_WF_PVT','Process_Beat_By_First',l_progress,sqlerrm);
1588 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.Process_Beat_By_First');
1589 RAISE;
1590
1591 end Process_Beat_By_First;
1592
1593
1594 --------------------------------------------------------------------------------
1595 --Start of Comments
1596 --Name: Process_Response_Approve
1597 --Pre-reqs:
1598 -- None.
1599 --Modifies:
1600 -- None.
1601 --Locks:
1602 -- None.
1603 --Function:
1604 -- Workflow activity PL/SQL handler.
1605 -- This procedure is the wrapper procedure of Process_Response_Internal()
1606 --Parameters:
1607 --IN:
1608 -- Standard workflow IN parameters
1609 --OUT:
1610 -- Standard workflow OUT parameters
1611 --Testing:
1612 --
1613 --End of Comments
1614 -------------------------------------------------------------------------------
1615 procedure Process_Response_Approve( itemtype in varchar2,
1616 itemkey in varchar2,
1617 actid in number,
1618 funcmode in varchar2,
1619 resultout out NOCOPY varchar2) IS
1620
1621 l_progress VARCHAR2(500) := '000';
1622 l_parent_item_type wf_items.parent_item_type%TYPE;
1623 l_parent_item_key wf_items.parent_item_key%TYPE;
1624
1625 l_child_approver_empid number;
1626 l_child_approver_user_name wf_users.name%TYPE;
1627 l_child_approver_display_name wf_users.display_name%TYPE;
1628
1629 begin
1630
1631 l_progress := 'Process_Response_Approve: 001';
1632 IF (g_po_wf_debug = 'Y') THEN
1633 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1634 END IF;
1635
1636 SELECT parent_item_type, parent_item_key
1637 into l_parent_item_type, l_parent_item_key
1638 FROM wf_items
1639 WHERE item_type = itemtype and item_key = itemkey;
1640
1641 Process_Response_Internal(itemtype, itemkey, 'APPROVE');
1642 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1643 itemkey => l_parent_item_key,
1644 aname => 'AME_SUB_APPROVAL_RESPONSE',
1645 avalue => 'APPROVE');
1646
1647
1648 l_child_approver_empid := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1649 itemkey => itemkey,
1650 aname => 'APPROVER_EMPID');
1651
1652 l_child_approver_user_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1653 itemkey => itemkey,
1654 aname => 'APPROVER_USER_NAME');
1655
1656 l_child_approver_display_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1657 itemkey => itemkey,
1658 aname => 'APPROVER_DISPLAY_NAME');
1659
1660
1661 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1662 itemkey => l_parent_item_key,
1663 aname => 'FORWARD_FROM_ID',
1664 avalue => l_child_approver_empid );
1665
1666 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1667 itemkey => l_parent_item_key,
1668 aname => 'FORWARD_FROM_USER_NAME',
1669 avalue => l_child_approver_user_name);
1670
1671 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1672 itemkey => l_parent_item_key,
1673 aname => 'FORWARD_FROM_DISP_NAME',
1674 avalue => l_child_approver_display_name);
1675
1676 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1677 itemkey => l_parent_item_key,
1678 aname => 'APPROVER_EMPID',
1679 avalue => l_child_approver_empid );
1680
1681 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1682 itemkey => l_parent_item_key,
1683 aname => 'APPROVER_USER_NAME',
1684 avalue => l_child_approver_user_name );
1685
1686
1687 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1688 itemkey => l_parent_item_key,
1689 aname => 'APPROVER_DISPLAY_NAME',
1690 avalue => l_child_approver_display_name );
1691
1692 l_progress := 'Process_Response_Approve: 002 -- Completing the BLOCK activity for the APPROVED notification.';
1693 IF (g_po_wf_debug = 'Y') THEN
1694 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1695 END IF;
1696
1697 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1698 RETURN;
1699 end Process_Response_Approve;
1700
1701 --------------------------------------------------------------------------------
1702 --Start of Comments
1703 --Name: Process_Response_Reject
1704 --Pre-reqs:
1705 -- None.
1706 --Modifies:
1707 -- None.
1708 --Locks:
1709 -- None.
1710 --Function:
1711 -- Workflow activity PL/SQL handler.
1712 -- This procedure is the wrapper procedure of Process_Response_Internal()
1713 --Parameters:
1714 --IN:
1715 -- Standard workflow IN parameters
1716 --OUT:
1717 -- Standard workflow OUT parameters
1718 --Testing:
1719 --
1720 --End of Comments
1721 -------------------------------------------------------------------------------
1722 procedure Process_Response_Reject( itemtype in varchar2,
1723 itemkey in varchar2,
1724 actid in number,
1725 funcmode in varchar2,
1726 resultout out NOCOPY varchar2) IS
1727
1728 l_progress VARCHAR2(500) := '000';
1729 l_parent_item_type wf_items.parent_item_type%TYPE;
1730 l_parent_item_key wf_items.parent_item_key%TYPE;
1731
1732 l_child_approver_empid number;
1733 l_child_approver_user_name wf_users.name%TYPE;
1734 l_child_approver_display_name wf_users.display_name%TYPE;
1735
1736 begin
1737
1738 l_progress := 'Process_Response_Reject: 001';
1739 IF (g_po_wf_debug = 'Y') THEN
1740 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1741 END IF;
1742
1743 Process_Response_Internal(itemtype, itemkey, 'REJECT');
1744
1745 SELECT parent_item_type, parent_item_key
1746 into l_parent_item_type, l_parent_item_key
1747 FROM wf_items
1748 WHERE item_type = itemtype and item_key = itemkey;
1749
1750 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1751 itemkey => l_parent_item_key,
1752 aname => 'AME_SUB_APPROVAL_RESPONSE',
1753 avalue => 'REJECT');
1754
1755 l_child_approver_empid := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1756 itemkey => itemkey,
1757 aname => 'APPROVER_EMPID');
1758
1759 l_child_approver_user_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1760 itemkey => itemkey,
1761 aname => 'APPROVER_USER_NAME');
1762
1763 l_child_approver_display_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1764 itemkey => itemkey,
1765 aname => 'APPROVER_DISPLAY_NAME');
1766
1767
1768 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1769 itemkey => l_parent_item_key,
1770 aname => 'FORWARD_FROM_ID',
1771 avalue => l_child_approver_empid );
1772
1773
1774 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1775 itemkey => l_parent_item_key,
1776 aname => 'FORWARD_FROM_USER_NAME',
1777 avalue => l_child_approver_user_name);
1778
1779 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1780 itemkey => l_parent_item_key,
1781 aname => 'FORWARD_FROM_DISP_NAME',
1782 avalue => l_child_approver_display_name);
1783
1784
1785
1786 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1787 itemkey => l_parent_item_key,
1788 aname => 'APPROVER_EMPID',
1789 avalue => l_child_approver_empid );
1790
1791
1792 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1793 itemkey => l_parent_item_key,
1794 aname => 'APPROVER_USER_NAME',
1795 avalue => l_child_approver_user_name );
1796
1797
1798 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1799 itemkey => l_parent_item_key,
1800 aname => 'APPROVER_DISPLAY_NAME',
1801 avalue => l_child_approver_display_name );
1802
1803
1804 l_progress := 'Process_Response_Reject: 002 -- Completing the BLOCK activity for the REJECTED notification.';
1805 IF (g_po_wf_debug = 'Y') THEN
1806 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1807 END IF;
1808
1809 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1810 RETURN;
1811
1812 end Process_Response_Reject;
1813
1814
1815 --------------------------------------------------------------------------------
1816 --Start of Comments
1817 --Name: Process_Response_Timeout
1818 --Pre-reqs:
1819 -- None.
1820 --Modifies:
1821 -- None.
1822 --Locks:
1823 -- None.
1824 --Function:
1825 -- Workflow activity PL/SQL handler.
1826 -- This procedure is the wrapper procedure of Process_Response_Internal()
1827 --Parameters:
1828 --IN:
1829 -- Standard workflow IN parameters
1830 --OUT:
1831 -- Standard workflow OUT parameters
1832 --Testing:
1833 --
1834 --End of Comments
1835 -------------------------------------------------------------------------------
1836 procedure Process_Response_Timeout( itemtype in varchar2,
1837 itemkey in varchar2,
1838 actid in number,
1839 funcmode in varchar2,
1840 resultout out NOCOPY varchar2) IS
1841
1842 l_progress VARCHAR2(500) := '000';
1843 l_parent_item_type wf_items.parent_item_type%TYPE;
1844 l_parent_item_key wf_items.parent_item_key%TYPE;
1845
1846 l_child_approver_empid number;
1847 l_child_approver_user_name wf_users.name%TYPE;
1848 l_child_approver_display_name wf_users.display_name%TYPE;
1849
1850
1851 begin
1852
1853 l_progress := 'Process_Response_Timeout: 001';
1854 IF (g_po_wf_debug = 'Y') THEN
1855 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1856 END IF;
1857
1858 Process_Response_Internal(itemtype, itemkey, 'TIMEOUT');
1859
1860 SELECT parent_item_type, parent_item_key
1861 into l_parent_item_type, l_parent_item_key
1862 FROM wf_items
1863 WHERE item_type = itemtype and item_key = itemkey;
1864
1865 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1866 itemkey => l_parent_item_key,
1867 aname => 'AME_SUB_APPROVAL_RESPONSE',
1868 avalue => 'TIMEOUT');
1869
1870 l_child_approver_empid := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1871 itemkey => itemkey,
1872 aname => 'APPROVER_EMPID');
1873
1874 l_child_approver_user_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1875 itemkey => itemkey,
1876 aname => 'APPROVER_USER_NAME');
1877
1878 l_child_approver_display_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1879 itemkey => itemkey,
1880 aname => 'APPROVER_DISPLAY_NAME');
1881
1882
1883 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1884 itemkey => l_parent_item_key,
1885 aname => 'FORWARD_FROM_ID',
1886 avalue => l_child_approver_empid );
1887
1888
1889 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1890 itemkey => l_parent_item_key,
1891 aname => 'FORWARD_FROM_USER_NAME',
1892 avalue => l_child_approver_user_name);
1893
1894 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1895 itemkey => l_parent_item_key,
1896 aname => 'FORWARD_FROM_DISP_NAME',
1897 avalue => l_child_approver_display_name);
1898
1899
1900
1901 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1902 itemkey => l_parent_item_key,
1903 aname => 'APPROVER_EMPID',
1904 avalue => l_child_approver_empid );
1905
1906
1907 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1908 itemkey => l_parent_item_key,
1909 aname => 'APPROVER_USER_NAME',
1910 avalue => l_child_approver_user_name );
1911
1912
1913 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1914 itemkey => l_parent_item_key,
1915 aname => 'APPROVER_DISPLAY_NAME',
1916 avalue => l_child_approver_display_name );
1917
1918
1919 wf_engine.CompleteActivity (itemtype => l_parent_item_type,
1920 itemkey => l_parent_item_key,
1921 activity => 'BLOCK',
1922 result => null);
1923
1924 l_progress := 'Process_Response_Timeout: 002 -- Completing the BLOCK activity for the REJECTED notification.';
1925 IF (g_po_wf_debug = 'Y') THEN
1926 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1927 END IF;
1928
1929 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1930 RETURN;
1931
1932 end Process_Response_Timeout;
1933
1934 --------------------------------------------------------------------------------
1935 -- CLM Apprvl
1936 --Start of Comments
1937 --Name: Process_Response_Forward
1938 --Pre-reqs:
1939 -- None.
1940 --Modifies:
1941 -- None.
1942 --Locks:
1943 -- None.
1944 --Function:
1945 -- Workflow activity PL/SQL handler.
1946 -- This procedure is the wrapper procedure of Process_Response_Internal()
1947 --Parameters:
1948 --IN:
1949 -- Standard workflow IN parameters
1950 --OUT:
1951 -- Standard workflow OUT parameters
1952 --Testing:
1953 --
1954 --End of Comments
1955 -------------------------------------------------------------------------------
1956 procedure Process_Response_Forward( itemtype in varchar2,
1957 itemkey in varchar2,
1958 actid in number,
1959 funcmode in varchar2,
1960 resultout out NOCOPY varchar2) IS
1961
1962 l_progress VARCHAR2(500) := '000';
1963 l_parent_item_type wf_items.parent_item_type%TYPE;
1964 l_parent_item_key wf_items.parent_item_key%TYPE;
1965
1966 l_child_approver_empid number;
1967 l_child_approver_user_name wf_users.name%TYPE;
1968 l_child_approver_display_name wf_users.display_name%TYPE;
1969
1970 begin
1971
1972 l_progress := 'Process_Response_Forward: 001';
1973 IF (g_po_wf_debug = 'Y') THEN
1974 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1975 END IF;
1976
1977 SELECT parent_item_type, parent_item_key
1978 into l_parent_item_type, l_parent_item_key
1979 FROM wf_items
1980 WHERE item_type = itemtype and item_key = itemkey;
1981
1982 Process_Response_Internal(itemtype, itemkey, 'FORWARD');
1983 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
1984 itemkey => l_parent_item_key,
1985 aname => 'AME_SUB_APPROVAL_RESPONSE',
1986 avalue => 'FORWARD');
1987
1988
1989 l_child_approver_empid := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
1990 itemkey => itemkey,
1991 aname => 'APPROVER_EMPID');
1992
1993 l_child_approver_user_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1994 itemkey => itemkey,
1995 aname => 'APPROVER_USER_NAME');
1996
1997 l_child_approver_display_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
1998 itemkey => itemkey,
1999 aname => 'APPROVER_DISPLAY_NAME');
2000
2001
2002 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
2003 itemkey => l_parent_item_key,
2004 aname => 'FORWARD_FROM_ID',
2005 avalue => l_child_approver_empid );
2006
2007 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
2008 itemkey => l_parent_item_key,
2009 aname => 'FORWARD_FROM_USER_NAME',
2010 avalue => l_child_approver_user_name);
2011
2012 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
2013 itemkey => l_parent_item_key,
2014 aname => 'FORWARD_FROM_DISP_NAME',
2015 avalue => l_child_approver_display_name);
2016
2017 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
2018 itemkey => l_parent_item_key,
2019 aname => 'APPROVER_EMPID',
2020 avalue => l_child_approver_empid );
2021
2022 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
2023 itemkey => l_parent_item_key,
2024 aname => 'APPROVER_USER_NAME',
2025 avalue => l_child_approver_user_name );
2026
2027
2028 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
2029 itemkey => l_parent_item_key,
2030 aname => 'APPROVER_DISPLAY_NAME',
2031 avalue => l_child_approver_display_name );
2032
2033 l_progress := 'Process_Response_Forward: 002 -- Completing the BLOCK activity for the Forwarded notification.';
2034 IF (g_po_wf_debug = 'Y') THEN
2035 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
2036 END IF;
2037
2038 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2039 RETURN;
2040 end Process_Response_Forward;
2041
2042 --------------------------------------------------------------------------------
2043 -- CLM Apprvl
2044 --Start of Comments
2045 --Name: Process_Response_App_Forward
2046 --Pre-reqs:
2047 -- None.
2048 --Modifies:
2049 -- None.
2050 --Locks:
2051 -- None.
2052 --Function:
2053 -- Workflow activity PL/SQL handler.
2054 -- This procedure is the wrapper procedure of Process_Response_Internal()
2055 --Parameters:
2056 --IN:
2057 -- Standard workflow IN parameters
2058 --OUT:
2059 -- Standard workflow OUT parameters
2060 --Testing:
2061 --
2062 --End of Comments
2063 -------------------------------------------------------------------------------
2064 procedure Process_Response_App_Forward( itemtype in varchar2,
2065 itemkey in varchar2,
2066 actid in number,
2067 funcmode in varchar2,
2068 resultout out NOCOPY varchar2) IS
2069
2070 l_progress VARCHAR2(500) := '000';
2071 l_parent_item_type wf_items.parent_item_type%TYPE;
2072 l_parent_item_key wf_items.parent_item_key%TYPE;
2073
2074 l_child_approver_empid number;
2075 l_child_approver_user_name wf_users.name%TYPE;
2076 l_child_approver_display_name wf_users.display_name%TYPE;
2077
2078 begin
2079
2080 l_progress := 'Process_Response_Approve_Forward: 001';
2081 IF (g_po_wf_debug = 'Y') THEN
2082 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
2083 END IF;
2084
2085 SELECT parent_item_type, parent_item_key
2086 into l_parent_item_type, l_parent_item_key
2087 FROM wf_items
2088 WHERE item_type = itemtype and item_key = itemkey;
2089
2090 Process_Response_Internal(itemtype, itemkey, 'APPROVE AND FORWARD');
2091 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
2092 itemkey => l_parent_item_key,
2093 aname => 'AME_SUB_APPROVAL_RESPONSE',
2094 avalue => 'APPROVE AND FORWARD');
2095
2096
2097 l_child_approver_empid := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2098 itemkey => itemkey,
2099 aname => 'APPROVER_EMPID');
2100
2101 l_child_approver_user_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2102 itemkey => itemkey,
2103 aname => 'APPROVER_USER_NAME');
2104
2105 l_child_approver_display_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2106 itemkey => itemkey,
2107 aname => 'APPROVER_DISPLAY_NAME');
2108
2109
2110 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
2111 itemkey => l_parent_item_key,
2112 aname => 'FORWARD_FROM_ID',
2113 avalue => l_child_approver_empid );
2114
2115 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
2116 itemkey => l_parent_item_key,
2117 aname => 'FORWARD_FROM_USER_NAME',
2118 avalue => l_child_approver_user_name);
2119
2120 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
2121 itemkey => l_parent_item_key,
2122 aname => 'FORWARD_FROM_DISP_NAME',
2123 avalue => l_child_approver_display_name);
2124
2125 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
2126 itemkey => l_parent_item_key,
2127 aname => 'APPROVER_EMPID',
2128 avalue => l_child_approver_empid );
2129
2130 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
2131 itemkey => l_parent_item_key,
2132 aname => 'APPROVER_USER_NAME',
2133 avalue => l_child_approver_user_name );
2134
2135
2136 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
2137 itemkey => l_parent_item_key,
2138 aname => 'APPROVER_DISPLAY_NAME',
2139 avalue => l_child_approver_display_name );
2140
2141 l_progress := 'Process_Response_Approve_Forward: 002 -- Completing the BLOCK activity for the Forwarded notification.';
2142 IF (g_po_wf_debug = 'Y') THEN
2143 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
2144 END IF;
2145
2146 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2147 RETURN;
2148 end Process_Response_App_Forward;
2149
2150 --------------------------------------------------------------------------------
2151 --Start of Comments
2152 --Name: insertActionHistory
2153 --Pre-reqs:
2154 -- None.
2155 --Modifies:
2156 -- None.
2157 --Locks:
2158 -- None.
2159 --Function:
2160 -- Workflow activity PL/SQL handler.
2161 -- This procedure is used to keep the history of each notification.
2162 -- The inserted records will be displayed in Approval History page.
2163 --Parameters:
2164 --IN:
2165 -- Requistion Header Id
2166 -- Employee Id
2167 -- Approver Group Id
2168 --OUT:
2169 -- Standard workflow OUT parameters
2170 --Testing:
2171 --
2172 --End of Comments
2173 -------------------------------------------------------------------------------
2174 procedure insertActionHistory( p_req_header_id in number,
2175 p_employee_id in number,
2176 p_approval_group_id in number)
2177
2178 is
2179 pragma AUTONOMOUS_TRANSACTION;
2180
2181 l_object_sub_type_code PO_ACTION_HISTORY.OBJECT_SUB_TYPE_CODE%TYPE;
2182 l_sequence_num PO_ACTION_HISTORY.SEQUENCE_NUM%TYPE;
2183 l_object_revision_num PO_ACTION_HISTORY.OBJECT_REVISION_NUM%TYPE;
2184 l_approval_path_id PO_ACTION_HISTORY.APPROVAL_PATH_ID%TYPE;
2185 l_request_id PO_ACTION_HISTORY.REQUEST_ID%TYPE;
2186 l_program_application_id PO_ACTION_HISTORY.PROGRAM_APPLICATION_ID%TYPE;
2187 l_program_date PO_ACTION_HISTORY.PROGRAM_DATE%TYPE;
2188 l_program_id PO_ACTION_HISTORY.PROGRAM_ID%TYPE;
2189 l_progress VARCHAR2(100) := '000';
2190
2191 begin
2192
2193 SELECT max(sequence_num)
2194 INTO l_sequence_num
2195 FROM PO_ACTION_HISTORY
2196 WHERE object_type_code = 'REQUISITION'
2197 AND object_id = p_req_header_id;
2198
2199 SELECT object_sub_type_code,
2200 object_revision_num, approval_path_id, request_id,
2201 program_application_id, program_date, program_id
2202 INTO l_object_sub_type_code,
2203 l_object_revision_num, l_approval_path_id, l_request_id,
2204 l_program_application_id, l_program_date, l_program_id
2205 FROM PO_ACTION_HISTORY
2206 WHERE object_type_code = 'REQUISITION'
2207 AND object_id = p_req_header_id
2208 AND sequence_num = l_sequence_num;
2209
2210 /* update po action history */
2211 po_forward_sv1.insert_action_history (
2212 p_req_header_id,
2213 'REQUISITION',
2214 l_object_sub_type_code,
2215 l_sequence_num + 1,
2216 NULL,
2217 NULL,
2218 p_employee_id,
2219 NULL,
2220 NULL,
2221 l_object_revision_num,
2222 NULL, /* offline_code */
2223 l_request_id,
2224 l_program_application_id,
2225 l_program_id,
2226 l_program_date,
2227 fnd_global.user_id,
2228 fnd_global.login_id,
2229 p_approval_group_id);
2230
2231 commit;
2232
2233 end insertActionHistory;
2234
2235
2236 --------------------------------------------------------------------------------
2237 --Start of Comments
2238 --Name: Insert_Action_History
2239 --Pre-reqs:
2240 -- None.
2241 --Modifies:
2242 -- None.
2243 --Locks:
2244 -- None.
2245 --Function:
2246 -- Workflow activity PL/SQL handler.
2247 -- This procedure is the wrapper procedure of insertActionHistory()
2248 --Parameters:
2249 --IN:
2250 -- Standard workflow IN parameters
2251 --OUT:
2252 -- Standard workflow OUT parameters
2253 --Testing:
2254 --
2255 --End of Comments
2256 -------------------------------------------------------------------------------
2257 procedure Insert_Action_History( itemtype in varchar2,
2258 itemkey in varchar2,
2259 actid in number,
2260 funcmode in varchar2,
2261 resultout out NOCOPY varchar2) IS
2262
2263 l_progress VARCHAR2(500) := '000';
2264 l_action VARCHAR2(30) := 'APPROVE';
2265 l_next_approver_id NUMBER:='';
2266 l_req_header_id NUMBER:='';
2267 l_approval_group_id NUMBER:='';
2268
2269 l_doc_string varchar2(200);
2270 l_preparer_user_name varchar2(100);
2271
2272 l_org_id number;
2273 l_document_id NUMBER;
2274 l_draft_id NUMBER;
2275 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
2276 l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
2277 l_document_subtype PO_DOCUMENT_TYPES_ALL_B.DOCUMENT_SUBTYPE%TYPE;
2278
2279 BEGIN
2280
2281 l_progress := 'Insert_Action_History: 001';
2282 IF (g_po_wf_debug = 'Y') THEN
2283 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2284 END IF;
2285
2286 IF (funcmode='RUN') THEN
2287
2288
2289 l_next_approver_id := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
2290 itemkey=>itemkey,
2291 aname=>'APPROVER_EMPID');
2292
2293 l_document_type := po_wf_util_pkg.GetItemAttrText(itemtype => itemtype,
2294 itemkey => itemkey,
2295 aname => 'DOCUMENT_TYPE');
2296 l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText (
2297 itemtype => itemtype,
2298 itemkey => itemkey,
2299 aname => 'DOCUMENT_SUBTYPE');
2300
2301 l_transaction_type := po_wf_util_pkg.GetItemAttrText(itemtype => itemtype,
2302 itemkey => itemkey,
2303 aname => 'AME_TRANSACTION_TYPE');
2304 l_approval_group_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2305 itemkey => itemkey,
2306 aname => 'APPROVAL_GROUP_ID');
2307
2308 -- Set the multi-org context
2309 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2310 itemkey => itemkey,
2311 aname => 'ORG_ID');
2312
2313 IF l_org_id is NOT NULL THEN
2314 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
2315 END IF;
2316
2317 if l_document_type = 'REQUISITION' then
2318 l_req_header_id := po_wf_util_pkg.GetItemAttrNumber(itemtype => itemtype,
2319 itemkey => itemkey,
2320 aname => 'DOCUMENT_ID');
2321 l_progress := 'Insert_Action_History: 004 - Calling insertActionHistory.';
2322 IF (g_po_wf_debug = 'Y') THEN
2323 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2324 END IF;
2325 insertActionHistory(l_req_header_id, l_next_approver_id, l_approval_group_id);
2326
2327 l_progress := 'Insert_Action_History: 005 - Done with insertActionHistory.';
2328 IF (g_po_wf_debug = 'Y') THEN
2329 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2330 END IF;
2331
2332 else
2333 /* l_ame_transaction_id := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2334 itemkey => itemkey,
2335 aname => 'AME_TRANSACTION_ID');*/
2336 l_document_id:= po_wf_util_pkg.GetItemAttrNumber(itemtype => itemtype,
2337 itemkey => itemkey,
2338 aname => 'DOCUMENT_ID');
2339 l_draft_id := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2340 itemkey => itemkey,
2341 aname => 'DRAFT_ID');
2342 l_progress := 'Insert_Action_History: 006 - Calling insertActionHistory.';
2343 IF (g_po_wf_debug = 'Y') THEN
2344 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2345 END IF;
2346
2347 insertActionHistoryPo(l_document_id, l_draft_id,
2348 l_document_type,
2349 l_document_subtype ,
2350 l_transaction_type,
2351 l_next_approver_id, l_approval_group_id);
2352
2353 l_progress := 'Insert_Action_History: 007 - Done with insertActionHistory.';
2354 IF (g_po_wf_debug = 'Y') THEN
2355 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2356 END IF;
2357 end if;
2358
2359 /* Reset the FORWARD_TO_USERNAME_RESPONSE attribute */
2360 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
2361 itemkey => itemkey,
2362 aname => 'FORWARD_TO_USERNAME_RESPONSE',
2363 avalue => NULL);
2364
2365 /* Reset the NOTE attribute */
2366 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
2367 itemkey => itemkey,
2368 aname => 'NOTE',
2369 avalue => NULL);
2370
2371 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2372 return;
2373
2374 END IF; -- run mode
2375
2376 l_progress := 'Insert_Action_History: 999';
2377 IF (g_po_wf_debug = 'Y') THEN
2378 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2379 END IF;
2380
2381 EXCEPTION
2382 WHEN OTHERS THEN
2383 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2384 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2385 wf_core.context('POR_AME_REQ_WF_PVT','Insert_Action_History',l_progress,sqlerrm);
2386 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.INSERT_ACTION_HISTORY');
2387 RAISE;
2388 END Insert_Action_History;
2389
2390
2391 --------------------------------------------------------------------------------
2392 --Start of Comments
2393 --Name: Update_Action_History_Approve
2394 --Pre-reqs:
2395 -- None.
2396 --Modifies:
2397 -- None.
2398 --Locks:
2399 -- None.
2400 --Function:
2401 -- Workflow activity PL/SQL handler.
2402 -- This procedure updates the po_action_history table based on the approvers response.
2403 --Parameters:
2404 --IN:
2405 -- Standard workflow IN parameters
2406 --OUT:
2407 -- Standard workflow OUT parameters
2408 --Testing:
2409 --
2410 --End of Comments
2411 -------------------------------------------------------------------------------
2412 procedure Update_Action_History_Approve( itemtype in varchar2,
2413 itemkey in varchar2,
2414 actid in number,
2415 funcmode in varchar2,
2416 resultout out NOCOPY varchar2) IS
2417 l_progress VARCHAR2(500) := '000';
2418 l_action VARCHAR2(30) := 'APPROVE';
2419 l_forward_to_id NUMBER:='';
2420 l_document_id NUMBER;
2421 l_document_type VARCHAR2(25):='';
2422 l_document_subtype VARCHAR2(25):='';
2423 l_return_code NUMBER;
2424 l_result BOOLEAN:=FALSE;
2425 l_note VARCHAR2(4000);
2426
2427 l_doc_string varchar2(200);
2428 l_preparer_user_name varchar2(100);
2429
2430 l_org_id number;
2431 l_current_approver number;
2432 -- CLM Apprvl --
2433 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
2434 l_draft_id NUMBER;
2435
2436 BEGIN
2437
2438 l_progress := 'Update_Action_History_Approve: 001';
2439 IF (g_po_wf_debug = 'Y') THEN
2440 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2441 END IF;
2442
2443 IF (funcmode='RUN') THEN
2444
2445 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
2446 itemkey=>itemkey,
2447 aname=>'APPROVER_EMPID');
2448
2449 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2450 itemkey => itemkey,
2451 aname => 'DOCUMENT_ID');
2452
2453 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2454 itemkey => itemkey,
2455 aname => 'DOCUMENT_TYPE');
2456
2457 l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2458 itemkey => itemkey,
2459 aname => 'DOCUMENT_SUBTYPE');
2460
2461 l_transaction_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2462 itemkey => itemkey,
2463 aname => 'AME_TRANSACTION_TYPE');
2464
2465
2466 l_note := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2467 itemkey => itemkey,
2468 aname => 'NOTE');
2469
2470 -- Set the multi-org context
2471 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2472 itemkey => itemkey,
2473 aname => 'ORG_ID');
2474
2475 IF l_org_id is NOT NULL THEN
2476 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
2477 END IF;
2478
2479 -- CLM Apprvl --
2480 if l_document_type='REQUISITION' then
2481
2482 l_progress := 'Update_Action_History_Approve: 002-'|| to_char(l_document_id)||'-'||
2483 l_document_type||'-'||l_document_subtype;
2484 IF (g_po_wf_debug = 'Y') THEN
2485 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2486 END IF;
2487
2488 /* UpdateActionHistory(l_document_id, l_action,
2489 l_note, l_current_approver);bug 10100356*/
2490 PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History( itemtype=>itemtype,
2491 itemkey=>itemkey,
2492 x_action=>l_action,
2493 x_req_header_id=>l_document_id,
2494 x_last_approver=>l_result,
2495 x_note=>l_note);
2496
2497 else
2498 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2499 itemkey => itemkey,
2500 aname => 'DRAFT_ID');
2501
2502 l_progress := 'Update_Action_History_Approve: 003-'|| to_char(l_document_id)||
2503 '-'||to_char(l_draft_id)||'-'||
2504 l_document_type||'-'||l_document_subtype;
2505 IF (g_po_wf_debug = 'Y') THEN
2506 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2507 END IF;
2508
2509 --Bug 13444730 Passing document_type and document_subtype
2510 UpdateActionHistoryPo(l_transaction_type,l_document_id, l_draft_id,
2511 l_document_type,l_document_subtype, l_action,
2512 l_note, l_current_approver);
2513
2514 end if;
2515
2516 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
2517 itemkey => itemkey,
2518 aname => 'APPROVER_RESPONSE',
2519 avalue => 'APPROVED' );
2520
2521 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2522
2523 END IF; -- run mode
2524
2525 l_progress := 'Update_Action_History_Approve: 004';
2526 IF (g_po_wf_debug = 'Y') THEN
2527 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2528 END IF;
2529
2530
2531 EXCEPTION
2532 WHEN OTHERS THEN
2533 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2534 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2535 wf_core.context('POR_AME_REQ_WF_PVT','Update_Action_History_Approve',l_progress,sqlerrm);
2536 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.UPDATE_ACTION_HISTORY_APPROVE');
2537 RAISE;
2538
2539 END Update_Action_History_Approve;
2540
2541
2542 --------------------------------------------------------------------------------
2543 --Start of Comments
2544 --Name: Update_Action_History_Reject
2545 --Pre-reqs:
2546 -- None.
2547 --Modifies:
2548 -- None.
2549 --Locks:
2550 -- None.
2551 --Function:
2552 -- Workflow activity PL/SQL handler.
2553 -- This procedure updates the po_action_history table based on the approvers response.
2554 --Parameters:
2555 --IN:
2556 -- Standard workflow IN parameters
2557 --OUT:
2558 -- Standard workflow OUT parameters
2559 --Testing:
2560 --
2561 --End of Comments
2562 -------------------------------------------------------------------------------
2563 procedure Update_Action_History_Reject(itemtype in varchar2,
2564 itemkey in varchar2,
2565 actid in number,
2566 funcmode in varchar2,
2567 resultout out NOCOPY varchar2) IS
2568
2569 l_progress VARCHAR2(100) := '000';
2570 l_action VARCHAR2(30) := 'REJECT';
2571 l_forward_to_id NUMBER:='';
2572 l_document_id NUMBER;
2573 l_document_type VARCHAR2(25):='';
2574 l_document_subtype VARCHAR2(25):='';
2575 l_return_code NUMBER;
2576 l_result BOOLEAN:=FALSE;
2577 l_note VARCHAR2(4000);
2578
2579 l_doc_string varchar2(200);
2580 l_preparer_user_name varchar2(100);
2581
2582 l_org_id number;
2583 l_current_approver number;
2584 -- <CLM code> --
2585 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
2586 l_draft_id NUMBER;
2587
2588 BEGIN
2589
2590 l_progress := 'Update_Action_History_Reject: 001';
2591 IF (g_po_wf_debug = 'Y') THEN
2592 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2593 END IF;
2594
2595 IF (funcmode='RUN') THEN
2596
2597 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
2598 itemkey=>itemkey,
2599 aname=>'APPROVER_EMPID');
2600
2601 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2602 itemkey => itemkey,
2603 aname => 'DOCUMENT_ID');
2604
2605 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2606 itemkey => itemkey,
2607 aname => 'DOCUMENT_TYPE');
2608
2609 l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2610 itemkey => itemkey,
2611 aname => 'DOCUMENT_SUBTYPE');
2612
2613 -- CLM Apprvl --
2614 l_transaction_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2615 itemkey => itemkey,
2616 aname => 'AME_TRANSACTION_TYPE');
2617
2618
2619 l_note := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2620 itemkey => itemkey,
2621 aname => 'NOTE');
2622
2623 -- Set the multi-org context
2624 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2625 itemkey => itemkey,
2626 aname => 'ORG_ID');
2627
2628 IF l_org_id is NOT NULL THEN
2629 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
2630 END IF;
2631
2632 -- CLM Apprvl --
2633 if l_document_type='REQUISITION' then
2634
2635 l_progress := 'Update_Action_History_Reject: 002-'|| to_char(l_document_id)||'-'||
2636 l_document_type||'-'||l_document_subtype;
2637 IF (g_po_wf_debug = 'Y') THEN
2638 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2639 END IF;
2640
2641 /*UpdateActionHistory(l_document_id, l_action,
2642 l_note, l_current_approver);bug 10100356*/
2643 PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History( itemtype=>itemtype,
2644 itemkey=>itemkey,
2645 x_action=>l_action,
2646 x_req_header_id=>l_document_id,
2647 x_last_approver=>l_result,
2648 x_note=>l_note);
2649 else
2650
2651 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2652 itemkey => itemkey,
2653 aname => 'DRAFT_ID');
2654 l_progress := 'Update_Action_History_Reject: 003-'|| to_char(l_document_id)||'-'||to_char(l_draft_id)||'-'||
2655 l_document_type||'-'||l_document_subtype;
2656 IF (g_po_wf_debug = 'Y') THEN
2657 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2658 END IF;
2659
2660 --Bug 13444730 Passing document_type and document_subtype
2661 UpdateActionHistoryPo(l_transaction_type,l_document_id, l_draft_id,
2662 l_document_type,l_document_subtype, l_action,
2663 l_note, l_current_approver);
2664
2665 end if;
2666
2667 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
2668 itemkey => itemkey,
2669 aname => 'APPROVER_RESPONSE',
2670 avalue => 'REJECTED' );
2671
2672 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2673
2674 END IF; -- run mode
2675
2676 l_progress := 'Update_Action_History_Reject: 004';
2677 IF (g_po_wf_debug = 'Y') THEN
2678 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2679 END IF;
2680
2681 EXCEPTION
2682 WHEN OTHERS THEN
2683 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2684 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2685 wf_core.context('POR_AME_REQ_WF_PVT','Update_Action_History_Reject',l_progress,sqlerrm);
2686 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.Update_Action_History_Reject');
2687 RAISE;
2688
2689 END Update_Action_History_Reject;
2690
2691
2692 --------------------------------------------------------------------------------
2693 --Start of Comments
2694 --Name: Update_Action_History_Timeout
2695 --Pre-reqs:
2696 -- None.
2697 --Modifies:
2698 -- None.
2699 --Locks:
2700 -- None.
2701 --Function:
2702 -- Workflow activity PL/SQL handler.
2703 -- This procedure updates the po_action_history table based on the approvers response.
2704 --Parameters:
2705 --IN:
2706 -- Standard workflow IN parameters
2707 --OUT:
2708 -- Standard workflow OUT parameters
2709 --Testing:
2710 --
2711 --End of Comments
2712 -------------------------------------------------------------------------------
2713 procedure Update_Action_History_Timeout(itemtype in varchar2,
2714 itemkey in varchar2,
2715 actid in number,
2716 funcmode in varchar2,
2717 resultout out NOCOPY varchar2) IS
2718
2719 l_progress VARCHAR2(100) := '000';
2720 l_action VARCHAR2(30) := 'NO ACTION';
2721 l_forward_to_id NUMBER:='';
2722 l_document_id NUMBER;
2723 l_document_type VARCHAR2(25):='';
2724 l_document_subtype VARCHAR2(25):='';
2725 l_return_code NUMBER;
2726 l_result BOOLEAN:=FALSE;
2727 l_note VARCHAR2(4000);
2728
2729 l_doc_string varchar2(200);
2730 l_preparer_user_name varchar2(100);
2731
2732 l_org_id number;
2733 l_current_approver number;
2734 -- <CLM code> --
2735 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
2736 l_draft_id NUMBER;
2737
2738 BEGIN
2739
2740 l_progress := 'Update_Action_History_Timeout: 001';
2741 IF (g_po_wf_debug = 'Y') THEN
2742 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2743 END IF;
2744
2745 IF (funcmode='RUN') THEN
2746
2747 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
2748 itemkey=>itemkey,
2749 aname=>'APPROVER_EMPID');
2750
2751 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2752 itemkey => itemkey,
2753 aname => 'DOCUMENT_ID');
2754
2755 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2756 itemkey => itemkey,
2757 aname => 'DOCUMENT_TYPE');
2758
2759 l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2760 itemkey => itemkey,
2761 aname => 'DOCUMENT_SUBTYPE');
2762
2763 -- CLM Apprvl --
2764 l_transaction_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2765 itemkey => itemkey,
2766 aname => 'AME_TRANSACTION_TYPE');
2767
2768
2769 l_note := fnd_message.get_string('ICX', 'ICX_POR_NOTIF_TIMEOUT');
2770
2771 -- Set the multi-org context
2772 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2773 itemkey => itemkey,
2774 aname => 'ORG_ID');
2775
2776 IF l_org_id is NOT NULL THEN
2777 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
2778 END IF;
2779
2780 -- CLM Apprvl --
2781 if l_document_type='REQUISITION' then
2782
2783 l_progress := 'Update_Action_History_Timeout: 002-'|| to_char(l_document_id)||'-'||
2784 l_document_type||'-'||l_document_subtype;
2785 IF (g_po_wf_debug = 'Y') THEN
2786 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2787 END IF;
2788
2789 /*UpdateActionHistory(l_document_id, l_action,
2790 l_note, l_current_approver);bug 10100356*/
2791 PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History( itemtype=>itemtype,
2792 itemkey=>itemkey,
2793 x_action=>l_action,
2794 x_req_header_id=>l_document_id,
2795 x_last_approver=>l_result,
2796 x_note=>l_note);
2797 else
2798
2799 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2800 itemkey => itemkey,
2801 aname => 'DRAFT_ID');
2802
2803 l_progress := 'Update_Action_History_Timeout: 003-'|| to_char(l_document_id)||'-'||to_char(l_draft_id)||'-'||
2804 l_document_type||'-'||l_document_subtype;
2805 IF (g_po_wf_debug = 'Y') THEN
2806 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2807 END IF;
2808
2809 --Bug 13444730 Passing document_type and document_subtype
2810 UpdateActionHistoryPo(l_transaction_type,l_document_id, l_draft_id,
2811 l_document_type,l_document_subtype, l_action,
2812 l_note, l_current_approver);
2813
2814 end if;
2815
2816 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2817
2818 END IF; -- run mode
2819
2820 l_progress := 'Update_Action_History_Timeout: 003';
2821 IF (g_po_wf_debug = 'Y') THEN
2822 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2823 END IF;
2824
2825 EXCEPTION
2826 WHEN OTHERS THEN
2827 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2828 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2829 wf_core.context('POR_AME_REQ_WF_PVT','Update_Action_History_Timeout',l_progress,sqlerrm);
2830 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');
2831 RAISE;
2832
2833 END Update_Action_History_Timeout;
2834
2835 --------------------------------------------------------------------------------
2836 --Start of Comments
2837 --Name: Update_Action_History_Forward
2838 --Pre-reqs:
2839 -- None.
2840 --Modifies:
2841 -- None.
2842 --Locks:
2843 -- None.
2844 --Function:
2845 -- Workflow activity PL/SQL handler.
2846 -- This procedure updates the po_action_history table based on the approvers response.
2847 --Parameters:
2848 --IN:
2849 -- Standard workflow IN parameters
2850 --OUT:
2851 -- Standard workflow OUT parameters
2852 --Testing:
2853 --
2854 --End of Comments
2855 -------------------------------------------------------------------------------
2856 procedure Update_Action_History_Forward( itemtype in varchar2,
2857 itemkey in varchar2,
2858 actid in number,
2859 funcmode in varchar2,
2860 resultout out NOCOPY varchar2) IS
2861 l_progress VARCHAR2(500) := '000';
2862 l_action VARCHAR2(30) := 'FORWARD';
2863 l_forward_to_id NUMBER:='';
2864 l_document_id NUMBER;
2865 l_document_type VARCHAR2(25):='';
2866 l_document_subtype VARCHAR2(25):='';
2867 l_return_code NUMBER;
2868 l_result BOOLEAN:=FALSE;
2869 l_note VARCHAR2(4000);
2870
2871 l_doc_string varchar2(200);
2872 l_preparer_user_name varchar2(100);
2873
2874 l_org_id number;
2875 l_current_approver number;
2876 -- <CLM code> --
2877 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
2878 l_draft_id NUMBER;
2879
2880 BEGIN
2881
2882 l_progress := 'Update_Action_History_Forward: 001';
2883 IF (g_po_wf_debug = 'Y') THEN
2884 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2885 END IF;
2886
2887 IF (funcmode='RUN') THEN
2888
2889 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( itemtype=>itemtype,
2890 itemkey=>itemkey,
2891 aname=>'APPROVER_EMPID');
2892
2893 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2894 itemkey => itemkey,
2895 aname => 'DOCUMENT_ID');
2896
2897 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2898 itemkey => itemkey,
2899 aname => 'DOCUMENT_TYPE');
2900
2901 l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2902 itemkey => itemkey,
2903 aname => 'DOCUMENT_SUBTYPE');
2904
2905 l_transaction_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2906 itemkey => itemkey,
2907 aname => 'AME_TRANSACTION_TYPE');
2908
2909
2910 l_note := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2911 itemkey => itemkey,
2912 aname => 'NOTE');
2913
2914 -- Set the multi-org context
2915 l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2916 itemkey => itemkey,
2917 aname => 'ORG_ID');
2918
2919 IF l_org_id is NOT NULL THEN
2920 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
2921 END IF;
2922
2923 -- <CLM code> --
2924 if l_document_type='REQUISITION' then
2925
2926 l_progress := 'Update_Action_History_Forward: 002-'|| to_char(l_document_id)||'-'||
2927 l_document_type||'-'||l_document_subtype;
2928 IF (g_po_wf_debug = 'Y') THEN
2929 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2930 END IF;
2931
2932 UpdateActionHistory(l_document_id, l_action,
2933 l_note, l_current_approver);
2934 else
2935
2936 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2937 itemkey => itemkey,
2938 aname => 'DRAFT_ID');
2939
2940
2941 l_progress := 'Update_Action_History_Forward: 003-'|| to_char(l_document_id)||'-'||to_char(l_draft_id)||'-'||
2942 l_document_type||'-'||l_document_subtype;
2943 IF (g_po_wf_debug = 'Y') THEN
2944 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2945 END IF;
2946
2947 --Bug 13444730 Passing document_type and document_subtype
2948 UpdateActionHistoryPo(l_transaction_type,l_document_id, l_draft_id,
2949 l_document_type,l_document_subtype, l_action,
2950 l_note, l_current_approver);
2951
2952 end if;
2953
2954 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
2955 itemkey => itemkey,
2956 aname => 'APPROVER_RESPONSE',
2957 avalue => 'FORWARD' );
2958
2959 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2960
2961 END IF; -- run mode
2962
2963 l_progress := 'Update_Action_History_Forward: 004';
2964 IF (g_po_wf_debug = 'Y') THEN
2965 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2966 END IF;
2967
2968
2969 EXCEPTION
2970 WHEN OTHERS THEN
2971 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2972 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2973 wf_core.context('POR_AME_REQ_WF_PVT','Update_Action_History_Forward',l_progress,sqlerrm);
2974 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.UPDATE_ACTION_HISTORY_FORWARD');
2975 RAISE;
2976
2977 END Update_Action_History_Forward;
2978
2979
2980 --------------------------------------------------------------------------------
2981 --Start of Comments
2982 --Name: Update_Action_History_No_Action
2983 --Pre-reqs:
2984 -- None.
2985 --Modifies:
2986 -- None.
2987 --Locks:
2988 -- None.
2989 --Function:
2990 -- Workflow activity PL/SQL handler.
2991 -- If it is a first responder wins setup in ame, then once the first reponder wins, others will not be able to take decisions.
2992 --Parameters:
2993 --IN:
2994 -- Standard workflow IN parameters
2995 --OUT:
2996 -- Standard workflow OUT parameters
2997 --Testing:
2998 --
2999 --End of Comments
3000 -------------------------------------------------------------------------------
3001 procedure Update_Action_History_No_Act (itemtype in varchar2,
3002 itemkey in varchar2,
3003 actid in number,
3004 funcmode in varchar2,
3005 resultout out NOCOPY varchar2 ) is
3006 l_doc_header_id NUMBER;
3007 l_doc_type VARCHAR2(14);
3008 l_note VARCHAR2(4000);
3009 x_progress varchar2(500);
3010 l_response_action VARCHAR2(20);
3011
3012 l_doc_string varchar2(200);
3013 l_preparer_user_name varchar2(100);
3014
3015 BEGIN
3016
3017 x_progress := 'POR_AME_REQ_WF_PVT.Update_Action_History_No_Act: 01';
3018
3019 if (funcmode <> wf_engine.eng_run) then
3020 resultout := wf_engine.eng_null;
3021 return;
3022 end if;
3023
3024
3025 l_doc_header_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
3026 itemkey => itemkey,
3027 aname => 'DOCUMENT_ID');
3028
3029 l_doc_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
3030 itemkey => itemkey,
3031 aname => 'DOCUMENT_TYPE');
3032
3033 BEGIN
3034
3035 l_response_action := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
3036 itemkey => itemkey,
3037 aname => 'AME_SUB_APPROVAL_RESPONSE');
3038 IF( l_response_action = 'APPROVE' ) THEN
3039 l_note := fnd_message.get_string('ICX', 'ICX_POR_REQ_ALREADY_APPROVED');
3040 ELSIF ( l_response_action = 'REJECT' ) THEN
3041 l_note := fnd_message.get_string('ICX', 'ICX_POR_REQ_ALREADY_REJECTED');
3042 ELSE
3043 l_note := NULL;
3044 END IF;
3045
3046 EXCEPTION
3047 WHEN OTHERS THEN
3048 l_note := NULL;
3049 END;
3050
3051
3052 x_progress := 'POR_AME_REQ_WF_PVT.Update_Action_History_No_Act: 02 - l_doc_header_id ' || l_doc_header_id || ' -- l_doc_type :' || l_doc_type ;
3053
3054 IF (g_po_wf_debug = 'Y') THEN
3055 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
3056 END IF;
3057
3058 -- If the setup is first responder wins, then once the first approver responds, the others will not be able to take decisions.
3059
3060 IF ( l_response_action is not null) THEN
3061
3062 UpdateActionHistory(l_doc_header_id, 'NO ACTION', l_note, NULL);
3063
3064 END IF;
3065
3066 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
3067
3068 x_progress := 'POR_AME_REQ_WF_PVT.Update_Action_History_No_Act: 03';
3069 IF (g_po_wf_debug = 'Y') THEN
3070 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
3071 END IF;
3072
3073 EXCEPTION
3074 WHEN OTHERS THEN
3075 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
3076 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
3077 wf_core.context('POR_AME_REQ_WF_PVT','Update_Action_History_No_Act',x_progress);
3078 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.Update_Action_History_No_Act');
3079 raise;
3080 END Update_Action_History_No_Act;
3081
3082 --------------------------------------------------------------------------------
3083 --Start of Comments
3084 --Name: UpdateActionHistory
3085 --Pre-reqs:
3086 -- None.
3087 --Modifies:
3088 -- None.
3089 --Locks:
3090 -- None.
3091 --Function:
3092 -- This procedure updates the po_action_history table based on the approvers response.
3093 --Parameters:
3094 --IN:
3095 -- p_document_id : Requisition Header Id
3096 -- p_action : Action
3097 -- p_note : Notes
3098 -- p_current_approver: Approver person Id
3099 --OUT:
3100 --
3101 --End of Comments
3102 -------------------------------------------------------------------------------
3103 PROCEDURE UpdateActionHistory(p_document_id NUMBER,
3104 p_action VARCHAR2,
3105 p_note VARCHAR2,
3106 p_current_approver NUMBER) IS
3107
3108 pragma AUTONOMOUS_TRANSACTION;
3109
3110 BEGIN
3111
3112 if (p_current_approver is not null) then
3113
3114 UPDATE po_action_history
3115 SET action_code = p_action,
3116 note = p_note,
3117 action_date = sysdate
3118 WHERE object_id = p_document_id and
3119 employee_id = p_current_approver and
3120 action_code is null and
3121 object_type_code = 'REQUISITION'
3122 and rownum=1;
3123
3124 else
3125
3126 UPDATE po_action_history
3127 SET action_code = p_action,
3128 note = p_note,
3129 action_date = sysdate
3130 WHERE object_id = p_document_id and
3131 action_code is null and
3132 object_type_code = 'REQUISITION'
3133 ;
3134 end if;
3135
3136 COMMIT;
3137
3138 EXCEPTION
3139
3140 WHEN OTHERS THEN
3141 RAISE;
3142
3143 END UpdateActionHistory;
3144
3145 --------------------------------------------------------------------------------
3146 --Start of Comments
3147 --Name: IS_AME_EXCEPTION
3148 --Pre-reqs:
3149 -- None.
3150 --Modifies:
3151 -- None.
3152 --Locks:
3153 -- None.
3154 --Function:
3155 -- Workflow activity PL/SQL handler.
3156 -- It checks if the AME_EXCEPTION attribute is NULL or not.
3157 -- If not NULL, it means there have been some AME exception encountered,
3158 -- and it returns 'Y'.
3159 -- Else it will return 'N'
3160 --Parameters:
3161 --IN:
3162 -- Standard workflow IN parameters
3163 --OUT:
3164 -- Standard workflow OUT parameters
3165 --Testing:
3166 --
3167 --End of Comments
3168 -------------------------------------------------------------------------------
3169 PROCEDURE IS_AME_EXCEPTION ( itemtype in varchar2,
3170 itemkey in varchar2,
3171 actid in number,
3172 funcmode in varchar2,
3173 resultout out NOCOPY varchar2) IS
3174 l_ame_exception ame_util.longestStringType;
3175 l_progress VARCHAR2(500) := '000';
3176 l_doc_string VARCHAR2(200);
3177 Begin
3178 IF (funcmode = 'RUN') THEN
3179 l_progress := 'IS_AME_EXCEPTION: 001';
3180 l_ame_exception :=PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
3181 itemkey => itemkey,
3182 aname => 'AME_EXCEPTION');
3183
3184 if l_ame_exception IS NOT NULL then
3185 resultout := wf_engine.eng_completed || ':' ||'Y';
3186 else
3187 resultout := wf_engine.eng_completed || ':' ||'N';
3188 end if;
3189 END IF;
3190 EXCEPTION
3191 when others then
3192 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
3193 wf_core.context('POR_AME_REQ_WF_PVT','IS_AME_EXCEPTION: Unexpected Exception:',l_progress,sqlerrm);
3194 IF (g_po_wf_debug = 'Y') THEN
3195 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );
3196 END IF;
3197 raise;
3198 End IS_AME_EXCEPTION;
3199
3200 --------------------------------------------------------------------------------
3201 --Start of Comments
3202 --Name: position_has_valid_approvers
3203 --Pre-reqs:
3204 -- None.
3205 --Modifies:
3206 -- None.
3207 --Locks:
3208 -- None.
3209 --Function:
3210 -- Workflow activity PL/SQL handler.
3211 -- This function is used to check whether to launch the parallel approval process or not.
3212 -- If a position does not have any users, then this function will return 'N', otherwise return 'Y'
3213 --Parameters:
3214 --IN:
3215 -- documentId : ReqHeaderId
3216 -- documentType : AME Transaction Type
3217 --OUT:
3218 -- 'Y' We can launch the parallel approval process.
3219 -- 'N' Invalid approver. We can not launch the parallel approval process.
3220 -- 'NO_USERS' No users for position. This AME record will be deleted. Go to the next approver record.
3221 --Testing:
3222 --
3223 --End of Comments
3224 -------------------------------------------------------------------------------
3225 FUNCTION position_has_valid_approvers( documentId NUMBER, documentType VARCHAR2 )RETURN VARCHAR2 is
3226
3227 l_next_approver_id number;
3228 l_next_approver_name per_employees_current_x.full_name%TYPE;
3229 l_position_has_valid_approvers VARCHAR2(10);
3230 l_approver_index NUMBER;
3231
3232 l_first_approver_id NUMBER := null;
3233 l_first_position_id NUMBER := null;
3234
3235 BEGIN
3236
3237 l_position_has_valid_approvers := 'Y';
3238 l_approver_index := g_next_approvers.first();
3239
3240 -- CLM Apprvl --
3241 if documentType in ('PURCHASE_REQ','INTERNAL_REQ') then
3242 select first_position_id, first_approver_id
3243 into l_first_position_id, l_first_approver_id
3244 from po_requisition_headers_all
3245 where documentId = requisition_header_id;
3246 end if;
3247
3248
3249 while( l_approver_index is not null ) loop
3250 l_position_has_valid_approvers := 'Y';
3251 if (g_next_approvers(l_approver_index).orig_system = ame_util.posOrigSystem) then
3252
3253 BEGIN
3254
3255 if (l_first_position_id is not NULL AND l_first_position_id=g_next_approvers(l_approver_index).orig_system_id) then
3256
3257 l_next_approver_id := l_first_approver_id;
3258
3259 SELECT full_name
3260 INTO l_next_approver_name
3261 FROM per_all_people_f person
3262 WHERE person_id = l_first_approver_id
3263 --Bug#7207213#This query fetches multiple records so adding a filter
3264 and trunc(sysdate) between person.effective_start_date and nvl(person.effective_end_date, trunc(sysdate));
3265
3266 else
3267
3268 /* find the persond id from the position_id*/
3269 SELECT person_id, full_name into l_next_approver_id,l_next_approver_name FROM (
3270 SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
3271 WHERE asg.position_id = g_next_approvers(l_approver_index).orig_system_id and trunc(sysdate) between person.effective_start_date
3272 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
3273 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
3274 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
3275 and asg.assignment_status_type_id not in (
3276 SELECT assignment_status_type_id FROM per_assignment_status_types
3277 WHERE per_system_status = 'TERM_ASSIGN'
3278 ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
3279 ) where rownum = 1;
3280
3281 end if;
3282
3283 EXCEPTION
3284 WHEN NO_DATA_FOUND THEN
3285
3286 -- No users for this position. Check whether this is last position or not.
3287 -- If this is last position then return the req to imcomplete status.
3288 -- Otherwise set this approver record to 'Approved'
3289 if( is_last_approver_record(documentId, documentType, g_next_approvers(l_approver_index)) = 'Y' )then
3290 return 'N';
3291 else
3292
3293 /*
3294 g_next_approvers(l_approver_index).approval_status := ame_util.noResponseStatus;
3295 -- Update the Approval status with the response from the approver.
3296 ame_api2.updateApprovalStatus( applicationIdIn =>applicationId,
3297 transactionIdIn =>documentId,
3298 transactionTypeIn =>documentType,
3299 approverIn => g_next_approvers(l_approver_index)
3300 );
3301 */
3302 --- need to add profile
3303 -- bug 11849654
3304 fnd_profile.put('POR_SYS_GENERATED_APPROVERS_SUPPRESS', 'Y');
3305
3306 ame_api3.suppressApprover( applicationIdIn => applicationId,
3307 transactionIdIn => documentId,
3308 approverIn => g_next_approvers(l_approver_index),
3309 transactionTypeIn => documentType
3310 );
3311
3312 fnd_profile.put('POR_SYS_GENERATED_APPROVERS_SUPPRESS', 'N');
3313
3314 -- remove this approver from the global list.
3315 g_next_approvers.delete(l_approver_index);
3316 l_position_has_valid_approvers := 'NO_USERS';
3317
3318 end if;
3319 END;
3320 end if;
3321 l_approver_index := g_next_approvers.next(l_approver_index);
3322 end loop;
3323 return l_position_has_valid_approvers;
3324
3325 EXCEPTION
3326 WHEN OTHERS THEN
3327 return 'N';
3328 END position_has_valid_approvers;
3329
3330
3331 --------------------------------------------------------------------------------
3332 --Start of Comments
3333 --Name: is_last_approver_record
3334 --Pre-reqs:
3335 -- None.
3336 --Modifies:
3337 -- None.
3338 --Locks:
3339 -- None.
3340 --Function:
3341 -- Workflow activity PL/SQL handler.
3342 -- This function is used to check whether the approver/position is last in the approval chain or not
3343 -- This function will be invoked only if a particular position does not have any associated users.
3344 -- If this function returns 'Y', then the req will be put back in incomplete status.
3345 --Parameters:
3346 --IN:
3347 -- documentId : ReqHeaderId
3348 -- documentType : AME Transaction Type
3349 --OUT:
3350 -- 'Y' The approver/position is last in the approval chain.
3351 -- 'N' The approver/position is not last in the approval chain
3352 --Testing:
3353 --
3354 --End of Comments
3355 -------------------------------------------------------------------------------
3356 Function is_last_approver_record( documentId NUMBER, documentType VARCHAR2, approverRecord in ame_util.approverRecord2 ) RETURN VARCHAR2 is
3357
3358 l_is_last_approver_record VARCHAR2(1);
3359 l_total_approver_count NUMBER;
3360 l_current_approver_index NUMBER;
3361 tmpApproverList ame_util.approversTable2;
3362 l_process_out VARCHAR2(10);
3363
3364 BEGIN
3365
3366 ame_api2.getAllApprovers7( applicationIdIn =>applicationId,
3367 transactionIdIn =>documentId,
3368 transactionTypeIn =>documentType,
3369 approvalProcessCompleteYNOut => l_process_out,
3370 approversOut =>tmpApproverList
3371 );
3372
3373 l_total_approver_count := tmpApproverList.count;
3374 l_current_approver_index := 0;
3375
3376 for i in 1 .. tmpApproverList.count loop
3377
3378 l_current_approver_index := i;
3379 if ( tmpApproverList(i).name = approverRecord.name AND
3380 tmpApproverList(i).orig_system = approverRecord.orig_system AND
3381 tmpApproverList(i).orig_system_id = approverRecord.orig_system_id AND
3382 tmpApproverList(i).authority = approverRecord.authority AND
3383 tmpApproverList(i).group_or_chain_id = approverRecord.group_or_chain_id AND
3384 tmpApproverList(i).action_type_id = approverRecord.action_type_id AND
3385 tmpApproverList(i).item_id = approverRecord.item_id AND
3386 tmpApproverList(i).item_class = approverRecord.item_class AND
3387 tmpApproverList(i).approver_category = approverRecord.approver_category
3388 ) then
3389
3390 EXIT;
3391 end if;
3392 end loop;
3393
3394 if( l_current_approver_index = l_total_approver_count ) then
3395 return 'Y';
3396 else
3397 return 'N';
3398 end if;
3399
3400 EXCEPTION
3401 WHEN OTHERS THEN
3402 return 'Y';
3403 END is_last_approver_record;
3404
3405 --------------------------------------------------------------------------------
3406 -- CLM Apprvl
3407 --Start of Comments
3408 --Name: insertActionHistoryPo
3409 --Pre-reqs:
3410 -- None.
3411 --Modifies:
3412 -- None.
3413 --Locks:
3414 -- None.
3415 --Function:
3416 -- Workflow activity PL/SQL handler.
3417 -- This procedure is used to keep the history of each notification.
3418 -- The inserted records will be displayed in Approval History page.
3419 --Parameters:
3420 --IN:
3421 -- Requistion Header Id
3422 -- Employee Id
3423 -- Approver Group Id
3424 --OUT:
3425 -- Standard workflow OUT parameters
3426 --Testing:
3427 --
3428 --End of Comments
3429 -------------------------------------------------------------------------------
3430 procedure insertActionHistoryPo( p_document_id in number,
3431 p_draft_id in number,
3432 p_document_type in varchar2,
3433 p_document_subtype in varchar2,
3434 p_transaction_type in varchar2,
3435 p_employee_id in number,
3436 p_approval_group_id in number)
3437
3438 is
3439 pragma AUTONOMOUS_TRANSACTION;
3440
3441 l_object_sub_type_code PO_ACTION_HISTORY.OBJECT_SUB_TYPE_CODE%TYPE;
3442 l_object_type_code PO_ACTION_HISTORY.OBJECT_TYPE_CODE%TYPE;
3443 l_sequence_num PO_ACTION_HISTORY.SEQUENCE_NUM%TYPE;
3444 l_object_revision_num PO_ACTION_HISTORY.OBJECT_REVISION_NUM%TYPE;
3445 l_approval_path_id PO_ACTION_HISTORY.APPROVAL_PATH_ID%TYPE;
3446 l_request_id PO_ACTION_HISTORY.REQUEST_ID%TYPE;
3447 l_program_application_id PO_ACTION_HISTORY.PROGRAM_APPLICATION_ID%TYPE;
3448 l_program_date PO_ACTION_HISTORY.PROGRAM_DATE%TYPE;
3449 l_program_id PO_ACTION_HISTORY.PROGRAM_ID%TYPE;
3450 l_progress VARCHAR2(100) := '000';
3451 l_object_id NUMBER;
3452 --l_record_exists VARCHAR2(1) := 'N';
3453
3454
3455 begin
3456
3457
3458 if (p_draft_id is NULL or p_draft_id = -1) then
3459
3460 SELECT max(sequence_num)
3461 INTO l_sequence_num
3462 FROM PO_ACTION_HISTORY
3463 WHERE object_type_code = p_document_type --'PO'
3464 AND object_sub_type_code = p_document_subtype --'STANDARD'
3465 AND object_id = p_document_id;
3466
3467 --Bug 12944203
3468 /*
3469 BEGIN
3470 SELECT 'Y'
3471 INTO l_record_exists
3472 FROM PO_ACTION_HISTORY
3473 WHERE object_type_code = p_document_type --'PO'
3474 AND object_sub_type_code = p_document_subtype --'STANDARD'
3475 AND object_id = p_document_id
3476 AND action_code is null;
3477 EXCEPTION
3478 WHEN no_data_found THEN
3479 l_record_exists := 'N';
3480 END;*/
3481
3482 SELECT object_id, object_type_code,object_sub_type_code,
3483 object_revision_num, approval_path_id, request_id,
3484 program_application_id, program_date, program_id
3485 INTO l_object_id, l_object_type_code, l_object_sub_type_code,
3486 l_object_revision_num, l_approval_path_id, l_request_id,
3487 l_program_application_id, l_program_date, l_program_id
3488 FROM PO_ACTION_HISTORY
3489 WHERE object_type_code = p_document_type --'PO'
3490 AND object_sub_type_code = p_document_subtype --'STANDARD'
3491 AND object_id = p_document_id
3492 AND sequence_num = l_sequence_num;
3493
3494 else
3495
3496 SELECT max(sequence_num)
3497 INTO l_sequence_num
3498 FROM PO_ACTION_HISTORY
3499 WHERE object_type_code = p_document_type --'PO'
3500 AND object_sub_type_code IN ('MODIFICATION', 'POST_AWARD_REQUEST') -- PAR Approval
3501 AND object_id = p_draft_id;
3502
3503 --Bug 12944203
3504 /*
3505 BEGIN
3506 SELECT 'Y'
3507 INTO l_record_exists
3508 FROM PO_ACTION_HISTORY
3509 WHERE object_type_code = p_document_type --'PO'
3510 AND object_sub_type_code = 'MODIFICATION' --'STANDARD'
3511 AND object_id = p_draft_id
3512 AND action_code is null;
3513 EXCEPTION
3514 WHEN no_data_found THEN
3515 l_record_exists := 'N';
3516 END;*/
3517
3518 SELECT object_id, object_type_code,object_sub_type_code,
3519 object_revision_num, approval_path_id, request_id,
3520 program_application_id, program_date, program_id
3521 INTO l_object_id,l_object_type_code, l_object_sub_type_code,
3522 l_object_revision_num, l_approval_path_id, l_request_id,
3523 l_program_application_id, l_program_date, l_program_id
3524 FROM PO_ACTION_HISTORY
3525 WHERE object_type_code = p_document_type --'PO'
3526 AND object_sub_type_code IN ('MODIFICATION', 'POST_AWARD_REQUEST') -- PAR Approval
3527 AND object_id = p_draft_id
3528 AND sequence_num = l_sequence_num;
3529
3530 end if;
3531 --Bug 12944203
3532 /*if l_record_exists = 'Y' then
3533 update po_action_history
3534 set employee_id = p_employee_id,
3535 approval_group_id = p_approval_group_id
3536 where object_type_code = l_object_type_code
3537 AND object_sub_type_code = l_object_sub_type_code
3538 AND object_id = l_object_id
3539 AND action_code is null;
3540 else*/
3541 /* update po action history */
3542 po_forward_sv1.insert_action_history (
3543 l_object_id,
3544 l_object_type_code,
3545 l_object_sub_type_code,
3546 l_sequence_num + 1,
3547 NULL,
3548 NULL,
3549 p_employee_id,
3550 NULL,
3551 NULL,
3552 l_object_revision_num,
3553 NULL, /* offline_code */
3554 l_request_id,
3555 l_program_application_id,
3556 l_program_id,
3557 l_program_date,
3558 fnd_global.user_id,
3559 fnd_global.login_id,
3560 p_approval_group_id);
3561 --end if;
3562 commit;
3563
3564 end insertActionHistoryPo;
3565
3566 --------------------------------------------------------------------------------
3567 -- CLM Apprvl
3568 --Start of Comments
3569 --Name: UpdateActionHistoryPo
3570 --Pre-reqs:
3571 -- None.
3572 --Modifies:
3573 -- None.
3574 --Locks:
3575 -- None.
3576 --Function:
3577 -- This procedure updates the po_action_history table based on the approvers response.
3578 --Parameters:
3579 --IN:
3580 -- p_transaction_type
3581 -- p_document_id : po_header_id
3582 -- p_draft_id
3583 -- p_document_type
3584 -- p_document_subtype
3585 --OUT:
3586 --
3587 --End of Comments
3588 -------------------------------------------------------------------------------
3589 PROCEDURE UpdateActionHistoryPo(p_transaction_type VARCHAR2,
3590 p_document_id NUMBER,
3591 p_draft_id NUMBER,
3592 p_document_type IN VARCHAR2, --Bug 13444730
3593 p_document_subtype IN VARCHAR2, --Bug 13444730
3594 p_action VARCHAR2,
3595 p_note VARCHAR2,
3596 p_current_approver NUMBER) IS
3597
3598 pragma AUTONOMOUS_TRANSACTION;
3599
3600 BEGIN
3601
3602 if p_transaction_type = 'PURCHASE_ORDER' then
3603 if (p_current_approver is not null) then
3604
3605 UPDATE po_action_history
3606 SET action_code = p_action,
3607 note = p_note,
3608 action_date = sysdate
3609 WHERE object_id = p_document_id and
3610 employee_id = p_current_approver and
3611 action_code is null and
3612 object_type_code = p_document_type and
3613 object_sub_type_code=p_document_subtype
3614 and rownum=1;
3615
3616 else
3617
3618 UPDATE po_action_history
3619 SET action_code = p_action,
3620 note = p_note,
3621 action_date = sysdate
3622 WHERE object_id = p_document_id and
3623 action_code is null and
3624 object_type_code = p_document_type and
3625 object_sub_type_code=p_document_subtype;
3626
3627 end if;
3628 elsif p_transaction_type = 'PURCHASE_MOD' then
3629 if (p_current_approver is not null) then
3630
3631 UPDATE po_action_history
3632 SET action_code = p_action,
3633 note = p_note,
3634 action_date = sysdate
3635 WHERE object_id = p_draft_id and
3636 employee_id = p_current_approver and
3637 action_code is null and
3638 object_type_code = p_document_type and
3639 object_sub_type_code='MODIFICATION'
3640 and rownum=1;
3641
3642 else
3643
3644 UPDATE po_action_history
3645 SET action_code = p_action,
3646 note = p_note,
3647 action_date = sysdate
3648 WHERE object_id = p_draft_id and
3649 action_code is null and
3650 object_type_code = p_document_type and
3651 object_sub_type_code='MODIFICATION';
3652
3653 end if;
3654 -- PAR Approval
3655 elsif p_transaction_type = 'PURCHASE_PAR' then
3656 if (p_current_approver is not null) then
3657
3658 UPDATE po_action_history
3659 SET action_code = p_action,
3660 note = p_note,
3661 action_date = sysdate
3662 WHERE object_id = p_draft_id and
3663 employee_id = p_current_approver and
3664 action_code is null and
3665 object_type_code = p_document_type and
3666 object_sub_type_code='POST_AWARD_REQUEST'
3667 and rownum=1;
3668
3669 else
3670
3671 UPDATE po_action_history
3672 SET action_code = p_action,
3673 note = p_note,
3674 action_date = sysdate
3675 WHERE object_id = p_draft_id and
3676 action_code is null and
3677 object_type_code = p_document_type and
3678 object_sub_type_code='POST_AWARD_REQUEST';
3679
3680 end if;
3681 end if;
3682
3683 COMMIT;
3684
3685 EXCEPTION
3686
3687 WHEN OTHERS THEN
3688 RAISE;
3689
3690 END UpdateActionHistoryPo;
3691
3692 --------------------------------------------------------------------------------
3693 -- CLM bug11736458 - exception response must be sent back to AME in case of error during PDF generation in Parallel approval process.
3694 --Start of Comments
3695 --Name: Process_Response_Exception
3696 --Pre-reqs:
3697 -- None.
3698 --Modifies:
3699 -- None.
3700 --Locks:
3701 -- None.
3702 --Function:
3703 -- Workflow activity PL/SQL handler.
3704 -- This procedure is the wrapper procedure of Process_Response_Internal()
3705 --Parameters:
3706 --IN:
3707 -- Standard workflow IN parameters
3708 --OUT:
3709 -- Standard workflow OUT parameters
3710 --Testing:
3711 --
3712 --End of Comments
3713 -------------------------------------------------------------------------------
3714 procedure Process_Response_Exception( itemtype in varchar2,
3715 itemkey in varchar2,
3716 actid in number,
3717 funcmode in varchar2,
3718 resultout out NOCOPY varchar2) IS
3719
3720 l_progress VARCHAR2(500) := '000';
3721 l_parent_item_type wf_items.parent_item_type%TYPE;
3722 l_parent_item_key wf_items.parent_item_key%TYPE;
3723
3724 l_child_approver_empid number;
3725 l_child_approver_user_name wf_users.name%TYPE;
3726 l_child_approver_display_name wf_users.display_name%TYPE;
3727
3728 begin
3729
3730 l_progress := 'Process_Response_Exception: 001';
3731 IF (g_po_wf_debug = 'Y') THEN
3732 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
3733 END IF;
3734
3735 SELECT parent_item_type, parent_item_key
3736 into l_parent_item_type, l_parent_item_key
3737 FROM wf_items
3738 WHERE item_type = itemtype and item_key = itemkey;
3739
3740 Process_Response_Internal(itemtype, itemkey, 'EXCEPTION');
3741 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
3742 itemkey => l_parent_item_key,
3743 aname => 'AME_SUB_APPROVAL_RESPONSE',
3744 avalue => 'EXCEPTION');
3745
3746
3747
3748 l_progress := 'Process_Response_Exception: 002 -- Completing the BLOCK activity for the exception.';
3749 IF (g_po_wf_debug = 'Y') THEN
3750 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
3751 END IF;
3752
3753 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
3754 RETURN;
3755 end Process_Response_Exception;
3756
3757
3758 END POR_AME_REQ_WF_PVT;