[Home] [Help]
PACKAGE BODY: APPS.IA_WF_REQUEST_PKG
Source
1 PACKAGE BODY IA_WF_REQUEST_PKG AS
2 /* $Header: IAWFREQB.pls 120.1 2006/01/31 14:46:25 appldev noship $ */
3
4 /*
5 PROCEDURE Insert_Approval
6 (p_request_id in number
7 ,p_approver_id in number
8 ,p_approval_status in varchar2
9 ,p_approval_chain_phase in varchar2
10 ,p_approval_id out nocopy number
11 );
12 */
13
14 /*
15 PROCEDURE Repopulate_Approvers_List
16 (p_request_id in number,
17 p_releasing_approvers in AME_UTIL.approversTable,
18 p_receiving_approvers in AME_UTIL.approversTable
19 );
20 */
21
22 /*
23 PROCEDURE Update_Approval_Status
24 (p_approval_id in number
25 ,p_chain_phase in varchar2
26 ,p_approval_status in varchar2
27 );
28 */
29
30 /*
31 PROCEDURE Update_Approval_Notify
32 (p_approval_id in number
33 ,p_notification_id in number
34 ,p_user_comment in varchar2
35 );
36 */
37
38 PROCEDURE Update_Request_Header_Status
39 (p_request_id in number
40 ,p_status in varchar2
41 );
42
43 PROCEDURE Update_Request_Line_Status
44 (p_request_id in number
45 ,p_status in varchar2
46 );
47
48 FUNCTION Start_Process(p_request_id in number)
49 return NUMBER
50 IS
51
52 l_itemkey VARCHAR2(30) := p_request_id;
53
54 l_book_type_code VARCHAR2(30);
55
56 l_person_id NUMBER(15);
57
58 l_preparer_id NUMBER(15);
59 l_preparer_name VARCHAR2(30);
60 l_preparer_name_display VARCHAR2(80);
61 l_preparer_user_id NUMBER(15);
62
63 l_system_admin VARCHAR2(30) := NULL;
64
65 l_requester_id NUMBER(15);
66 l_requester_name VARCHAR2(30);
67 l_requester_name_display VARCHAR2(80);
68
69 l_releasing_approver_id NUMBER(15);
70 l_releasing_approver_name VARCHAR2(30);
71 l_releasing_approver_name_disp VARCHAR2(80);
72
73 l_receiving_approver_id NUMBER(15);
74 l_receiving_approver_name VARCHAR2(30);
75 l_receiving_approver_name_disp VARCHAR2(80);
76
77 l_responsibility_id NUMBER(15);
78 l_rule_id NUMBER(15);
79 l_status VARCHAR2(30);
80
81 l_dummy NUMBER(15);
82 l_dummy_text VARCHAR2(80);
83
84 l_request_type VARCHAR2(15);
85 l_purpose VARCHAR2(2000);
86
87 l_approval_type VARCHAR2(30);
88 l_chain_phase VARCHAR2(30);
89
90 l_attribute VARCHAR2(30);
91 l_error_message VARCHAR2(2000);
92
93 debugInfo VARCHAR2(255) := NULL;
94
95 localException EXCEPTION;
96 localWFException EXCEPTION;
97
98 callingProgram VARCHAR2(80) := 'Start_Process';
99
100 BEGIN
101
102 savepoint START_PROCESS_STEP1;
103
104 /***************************
105 * Initializing variables
106 ***************************/
107 -----------------------------------------------------
108 debugInfo := 'Initialize error message stack';
109 -----------------------------------------------------
110 FA_SRVR_MSG.init_server_message;
111 IA_WF_UTIL_PKG.InitializeDebugMessage;
112
113
114 -----------------------------------------------------
115 debugInfo := 'Retrieve book type code from IA_REQUEST_HEADERS';
116 -----------------------------------------------------
117 begin
118
119 select book_type_code
120 ,preparer_id
121 ,requester_id
122 ,responsibility_id
123 ,request_type
124 ,purpose
125 ,status
126 ,releasing_approver_id
127 ,receiving_approver_id
128 into l_book_type_code
129 ,l_preparer_id
130 ,l_requester_id
131 ,l_responsibility_id
132 ,l_request_type
133 ,l_purpose
134 ,l_status
135 ,l_releasing_approver_id
136 ,l_receiving_approver_id
137 from ia_request_headers
138 where request_id=p_request_id;
139
140 exception
141 when others then
142 FND_MESSAGE.set_name('IA', 'IA_NO_REQUEST_FOUND'); -- Error: Unable to find request id, REQUEST_ID
143 FND_MESSAGE.set_token('REQUEST_ID', p_request_id);
144 l_error_message := FND_MESSAGE.Get;
145 raise localException;
146 end;
147
148 if (l_status is NULL or l_status <> IA_WF_UTIL_PKG.HeaderStatusSubmitted) then
149 FND_MESSAGE.set_name('IA', 'IA_INVALID_STATUS_FOR_START_WF'); -- Error: You can not submit the request id, REQUEST_ID. The status of request must be set to Submitted in order for Workflow to initiate the request.
150 FND_MESSAGE.set_token('REQUEST_ID', p_request_id);
151 l_error_message := FND_MESSAGE.Get;
152 raise localException;
153 end if;
154
155
156 begin
157
158 -----------------------------------------------------
159 debugInfo := 'Get Preparer Name';
160 -----------------------------------------------------
161 l_person_id := l_preparer_id;
162 WF_DIRECTORY.GetUserName('PER',
163 l_person_id,
164 l_preparer_name,
165 l_preparer_name_display);
166
167
168 -----------------------------------------------------
169 debugInfo := 'Get Requester Name';
170 -----------------------------------------------------
171 l_person_id := l_requester_id;
172 WF_DIRECTORY.GetUserName('PER',
173 l_person_id,
174 l_requester_name,
175 l_requester_name_display);
176
177 -----------------------------------------------------
178 debugInfo := 'Validate Releasing Approver ID and Get the Name';
179 -----------------------------------------------------
180 l_person_id := l_releasing_approver_id;
181 WF_DIRECTORY.GetUserName('PER',
182 l_person_id,
183 l_releasing_approver_name,
184 l_releasing_approver_name_disp);
185
186 -----------------------------------------------------
187 debugInfo := 'Validate Receiving Approver ID and Get the Name';
188 -----------------------------------------------------
189 l_person_id := l_receiving_approver_id;
190 WF_DIRECTORY.GetUserName('PER',
191 l_person_id,
192 l_receiving_approver_name,
193 l_receiving_approver_name_disp);
194
195 exception
196 when others then
197 FND_MESSAGE.set_name('IA', 'IA_NO_PERSON_FOUND'); -- Error: Unable to find person id, PERSON_ID
198 FND_MESSAGE.set_token('PERSON_ID', l_person_id);
199 l_error_message := FND_MESSAGE.Get;
200 raise localException;
201 end;
202
203
204 -----------------------------------------------------
205 debugInfo := 'Validate Book Type Code';
206 -----------------------------------------------------
207 if (l_request_type = IA_WF_UTIL_PKG.RequestTypeTransfer) then
208
209 begin
210
211 select book_type_code
212 into l_dummy_text
213 from fa_book_controls
214 where book_type_code=l_book_type_code
215 and book_class='CORPORATE'
216 and rownum < 2;
217
218 exception
219 when others then
220 FND_MESSAGE.set_name('IA', 'IA_NO_BOOK_FOUND'); -- Error: Unable to find book, BOOK_TYPE_CODE with book class of Corporate
221 FND_MESSAGE.set_token('BOOK_TYPE_CODE', l_book_type_code);
222 l_error_message := FND_MESSAGE.Get;
223 raise localException;
224 end;
225
226 end if;
227
228 -----------------------------------------------------
229 debugInfo := 'Validate Responsibility ID';
230 -----------------------------------------------------
231 begin
232
233 select responsibility_id
234 into l_dummy
235 from fnd_responsibility
236 where responsibility_id=l_responsibility_id
237 and application_id=IA_WF_UTIL_PKG.GetApplicationID;
238
239 exception
240 when others then
241 FND_MESSAGE.set_name('IA', 'IA_NO_RESPONSIBILITY_FOUND'); -- Error: Unable to find responsibility id, RESPONSIBILITY_ID
242 FND_MESSAGE.set_token('RESPONSIBILITY_ID', l_responsibility_id);
243 l_error_message := FND_MESSAGE.Get;
244 raise localException;
245 end;
246
247 -----------------------------------------------------
248 debugInfo := 'Get User ID of Preparer';
249 -----------------------------------------------------
250 begin
251
252 l_preparer_user_id := to_number(FND_PROFILE.VALUE('USER_ID'));
253
254 /* To avoid error when launched from pl/sql
255 if (l_preparer_user_id is NULL) then
256 raise localException;
257 end if;
258 */
259
260 end;
261
262 -----------------------------------------------------
263 debugInfo := 'Initialize Profile';
264 -----------------------------------------------------
265 if (not IA_WF_UTIL_PKG.InitializeProfile(p_user_id => l_preparer_user_id,
266 p_responsibility_id => l_responsibility_id) ) then
267 raise localException;
268 end if;
269
270 -----------------------------------------------------
271 debugInfo := 'Get Rule ID';
272 -----------------------------------------------------
273 begin
274
275 l_rule_id := IA_WF_UTIL_PKG.GetRuleID(p_responsibility_id => l_responsibility_id);
276
277 exception
278 when others then
279 FND_MESSAGE.set_name('IA', 'IA_NO_RULE_ASSIGNED'); -- Error: No rule has been defined for responsibility id, RESPONSIBILITY_ID.
280 FND_MESSAGE.set_token('RESPONSIBILITY_ID', l_responsibility_id);
281 l_error_message := FND_MESSAGE.Get;
282 raise localException;
283 end;
284
285
286 -----------------------------------------------------
287 debugInfo := 'Initialize rule setup';
288 -----------------------------------------------------
289 if (not IA_WF_UTIL_PKG.ResetRuleSetup(p_rule_id => l_rule_id,
290 p_book_type_code => l_book_type_code) ) then
291 FND_MESSAGE.set_name('IA', 'IA_RULE_RETRIEVAL_ERROR'); -- Error: Unable to find rule id, RULE_ID
292 FND_MESSAGE.set_token('RULE_ID', l_rule_id);
293 l_error_message := FND_MESSAGE.Get;
294 raise localException;
295 end if;
296
297 -----------------------------------------------------
298 debugInfo := 'Get Approval Type';
299 -----------------------------------------------------
300 l_approval_type := IA_WF_UTIL_PKG.GetApprovalType(p_rule_id => l_rule_id,
301 p_book_type_code => l_book_type_code);
302
303 if (l_approval_type = IA_WF_UTIL_PKG.ApprovalTypeDestination) then
304 l_chain_phase := IA_WF_UTIL_PKG.ApprovalTypeDestination;
305 else
306 l_chain_phase := IA_WF_UTIL_PKG.ApprovalTypeReleasing;
307 end if;
308
309 /***************************
310 * Initializing AME session
311 ***************************/
312
313 /*
314 -----------------------------------------------------
315 debugInfo := 'Clear all AME PL/SQL session context and approvals for the transaciton ID';
316 -----------------------------------------------------
317 if (not IA_AME_REQUEST_PKG.InitializeAME(RequestId => p_request_id)) then
318 FND_MESSAGE.set_name('IA', 'IA_AME_INITIALIZE_ERROR'); -- Error: Error occurred when initializing a session for Oracle Approvals Management.
319 l_error_message := FND_MESSAGE.Get;
320 raise localException;
321 end if;
322 */
323
324
325 begin
326
327 -----------------------------------------------------
328 debugInfo := 'Set the current request status to Pending';
329 -----------------------------------------------------
330 update ia_request_headers
331 set status = IA_WF_UTIL_PKG.HeaderStatusPendingApproval
332 ,last_update_date = SYSDATE
333 ,last_updated_by = nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
334 ,last_update_login = nvl(to_number(FND_PROFILE.VALUE('LOGIN_ID')),-1)
335 where request_id = p_request_id;
336
337 update ia_request_details
338 set status = IA_WF_UTIL_PKG.LineStatusPending
339 ,last_update_date = SYSDATE
340 ,last_updated_by = nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
341 ,last_update_login = nvl(to_number(FND_PROFILE.VALUE('LOGIN_ID')),-1)
342 where request_id = p_request_id;
343
344 exception
345 when others then
346 FND_MESSAGE.set_name('IA', 'IA_HEADER_STATUS_UPDATE_ERROR'); -- Error: Unable to update the status for request id, REQUEST_ID
347 FND_MESSAGE.set_token('REQUEST_ID', p_request_id);
348 l_error_message := FND_MESSAGE.Get;
349 raise localException;
350 end;
351
352 /****************************
353 * Launching Workflow Process
354 *****************************/
355
356 -- WF_ENGINE.threshold := -1;
357
358
359 begin
360 -----------------------------------------------------
361 debugInfo := 'Create a new process';
362 -----------------------------------------------------
363 WF_ENGINE.createProcess(ItemType => IA_WF_UTIL_PKG.WF_TransactionType,
364 ItemKey => l_itemkey,
365 process => IA_WF_UTIL_PKG.WF_MainProcess);
366
367 exception
368 when others then
369 FND_MESSAGE.set_name('IA', 'IA_WF_CREATE_PROCESS_ERROR'); -- Error: Unable to create a workflow process for request id, REQUEST_ID
370 FND_MESSAGE.set_token('REQUEST_ID', p_request_id);
371 l_error_message := FND_MESSAGE.Get;
372 raise localException;
373 end;
374
375
376 begin
377
378 l_system_admin := IA_WF_UTIL_PKG.GetSystemAdministrator;
379
380 -----------------------------------------------------
381 debugInfo := 'Set system item attributes';
382 -----------------------------------------------------
383 if (l_system_admin is NOT NULL) then
384 WF_ENGINE.SetItemOwner(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
385 itemkey => l_itemkey,
386 owner => l_system_admin); -- l_preparer_name);
387 end if;
388
389 WF_ENGINE.SetItemUserKey(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
390 itemkey => l_itemkey,
391 userkey => p_request_id); -- p_request_id);
392
393 exception
394 when others then
395 FND_MESSAGE.set_name('IA', 'IA_WF_SET_SYSTEM_ATTRS_ERROR'); -- Error: Unable to set the workflow system attributes for request id, REQUEST_ID
396 FND_MESSAGE.set_token('REQUEST_ID', p_request_id);
397 l_error_message := FND_MESSAGE.Get;
398 raise localWFException;
399 end;
400
401
402 begin
403 -----------------------------------------------------
404 debugInfo := 'Set item attributes';
405 -----------------------------------------------------
406 l_attribute := 'REQUEST_ID';
407 WF_ENGINE.SetItemAttrNumber(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
408 itemkey => l_itemkey,
409 aname => 'REQUEST_ID',
410 avalue => p_request_id);
411
412 l_attribute := 'PREPARER_ID';
413 WF_ENGINE.SetItemAttrNumber(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
414 itemkey => l_itemkey,
415 aname => 'PREPARER_ID',
416 avalue => l_preparer_id);
417
418 l_attribute := 'PREPARER_NAME';
419 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
420 itemkey => l_itemkey,
421 aname => 'PREPARER_NAME',
422 avalue => l_preparer_name);
423
424 l_attribute := 'PREPARER_NAME_DISP';
425 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
426 itemkey => l_itemkey,
427 aname => 'PREPARER_NAME_DISP',
428 avalue => l_preparer_name_display);
429
430 l_attribute := 'REQUESTER_ID';
431 WF_ENGINE.SetItemAttrNumber(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
432 itemkey => l_itemkey,
433 aname => 'REQUESTER_ID',
434 avalue => l_requester_id);
435
436 l_attribute := 'REQUESTER_NAME';
437 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
438 itemkey => l_itemkey,
439 aname => 'REQUESTER_NAME',
440 avalue => l_requester_name);
441
442 l_attribute := 'REQUESTER_NAME_DISP';
443 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
444 itemkey => l_itemkey,
445 aname => 'REQUESTER_NAME_DISP',
446 avalue => l_requester_name_display);
447
448 l_attribute := 'DELEGATEE_ID';
449 WF_ENGINE.SetItemAttrNumber(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
450 itemkey => l_itemkey,
451 aname => 'DELEGATEE_ID',
452 avalue => '');
453
454 l_attribute := 'BOOK_TYPE_CODE';
455 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
456 itemkey => l_itemkey,
457 aname => 'BOOK_TYPE_CODE',
458 avalue => l_book_type_code);
459
460 l_attribute := 'REQUEST_TYPE';
461 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
462 itemkey => l_itemkey,
463 aname => 'REQUEST_TYPE',
464 avalue => l_request_type);
465
466 l_attribute := 'REQUEST_TYPE_DISP';
467 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
468 itemkey => l_itemkey,
469 aname => 'REQUEST_TYPE_DISP',
470 avalue => IA_WF_UTIL_PKG.GetLookupMeaning(p_lookup_type=>'REQUEST_TYPE',
471 p_lookup_code=>l_request_type));
472
473 l_attribute := 'REQUEST_PURPOSE';
474 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
475 itemkey => l_itemkey,
476 aname => 'REQUEST_PURPOSE',
477 avalue => l_purpose);
478
479 l_attribute := 'RULE_ID';
480 WF_ENGINE.SetItemAttrNumber(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
481 itemkey => l_itemkey,
482 aname => 'RULE_ID',
483 avalue => l_rule_id);
484
485 l_attribute := 'APPROVAL_CHAIN_PHASE';
486 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
487 itemkey => l_itemkey,
488 aname => 'APPROVAL_CHAIN_PHASE',
489 avalue => l_chain_phase);
490
491 l_attribute := 'APPROVAL_CHAIN_PHASE_DISP';
492 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
493 itemkey => l_itemkey,
494 aname => 'APPROVAL_CHAIN_PHASE_DISP',
495 avalue => IA_WF_UTIL_PKG.GetLookupMeaning(p_lookup_type=>'APPROVAL_TYPE',
496 p_lookup_code=>l_chain_phase));
497
498 l_attribute := 'NO_MORE_APPROVER_FLAG';
499 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
500 itemkey => l_itemkey,
501 aname => 'NO_MORE_APPROVER_FLAG',
502 avalue => 'N');
503
504 /** Reference for settting other item types
505
506 WF_ENGINE.SetItemAttrDate(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
507 itemkey => l_itemkey,
508 aname => 'AAA_DATE',
509 avalue => p_aaa_date);
510 ***/
511
512 l_attribute := 'REQUEST_STATUS';
513 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
514 itemkey => l_itemkey,
515 aname => 'REQUEST_STATUS',
516 avalue => IA_WF_UTIL_PKG.HeaderStatusPendingApproval);
517
518 l_attribute := 'REQUEST_STATUS_DISP';
519 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
520 itemkey => l_itemkey,
521 aname => 'REQUEST_STATUS_DISP',
522 avalue => IA_WF_UTIL_PKG.GetLookupMeaning(p_lookup_type=>'REQ_HDR_STATUS',
523 p_lookup_code=>IA_WF_UTIL_PKG.HeaderStatusPendingApproval));
524 exception
525 when others then
526 FND_MESSAGE.set_name('IA', 'IA_WF_SET_ATTRIBUTE_ERROR'); -- Error: Unable to set the workflow attribute ATTRIBUTE for request id, REQUEST_ID
527 FND_MESSAGE.set_token('ATTRIBUTE', l_attribute);
528 FND_MESSAGE.set_token('REQUEST_ID', p_request_id);
529 l_error_message := FND_MESSAGE.Get;
530 raise localWFException;
531 end;
532
533
534 begin
535
536 -----------------------------------------------------
537 debugInfo := 'Start Work Flow process';
538 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => l_itemkey,
539 p_calling_fn => callingProgram,
540 p_parameter1 => debugInfo);
541 -----------------------------------------------------
542 WF_ENGINE.StartProcess(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
543 itemkey => l_itemkey);
544
545 exception
546 when others then
547 FND_MESSAGE.set_name('IA', 'IA_WF_START_PROCESS_ERROR'); -- Error: Unable to start a workflow process for request id, REQUEST_ID
548 FND_MESSAGE.set_token('REQUEST_ID', p_request_id);
549 l_error_message := FND_MESSAGE.Get;
550 raise localWFException;
551 end;
552
553 commit;
554
555 return 1;
556
557 EXCEPTION
558 /*
559 message_name: IA_DEBUG
560 message_text: ERROR occurred in
561 CALLING_SEQUENCE
562 with parameters (PARAMETERS)
563 while performing the following operation:
564 DEBUG_INFO
565 */
566 WHEN localException THEN
567 rollback to START_PROCESS_STEP1;
568 FA_SRVR_MSG.add_message(calling_fn => l_error_message);
569 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo||' '||l_error_message, 'Error');
570
571 return -1;
572
573 WHEN localWFException THEN
574 rollback to START_PROCESS_STEP1;
575 FA_SRVR_MSG.add_message(calling_fn => l_error_message);
576 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo||' '||l_error_message, 'Error');
577 /*
578 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => p_request_id,
579 p_calling_fn => callingProgram,
580 p_parameter1 => debugInfo||' '||l_error_message);
581 */
582
583 return -1;
584
585 WHEN OTHERS THEN
586 rollback to START_PROCESS_STEP1;
587 FA_SRVR_MSG.Add_SQL_Error(calling_fn => callingProgram);
588 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, 'Error');
589
590 return -1;
591
592 END Start_Process;
593
594 FUNCTION Abort_Process(p_request_id in number)
595 return NUMBER
596 IS
597
598 l_error_message VARCHAR2(2000);
599
600 debugInfo VARCHAR2(255) := NULL;
601
602 localException EXCEPTION;
603 localWFException EXCEPTION;
604
605 callingProgram VARCHAR2(80) := 'Abort_Process';
606
607
608 BEGIN
609
610 savepoint ABORT_PROCESS_STEP1;
611
612
613 -----------------------------------------------------
614 debugInfo := 'Calling AbortProcess';
615 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => p_request_id,
616 p_calling_fn => callingProgram,
617 p_parameter1 => debugInfo);
618 -----------------------------------------------------
619
620 WF_ENGINE.AbortProcess(IA_WF_UTIL_PKG.WF_TransactionType,
621 p_request_id);
622
623 commit;
624
625 return 1; -- If successful
626
627 EXCEPTION
628
629 WHEN OTHERS THEN
630 rollback to ABORT_PROCESS_STEP1;
631 FA_SRVR_MSG.Add_SQL_Error(calling_fn => callingProgram);
632 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, 'Error');
633
634 return -1; -- If errored out
635
636 END Abort_Process;
637
638 PROCEDURE Check_Approval_Type
639 (itemtype in varchar2
640 ,itemkey in varchar2
641 ,actid in number
642 ,funcmode in varchar2
643 ,result out nocopy varchar2)
644 IS
645
646 debugInfo VARCHAR2(255) := NULL;
647
648 localException EXCEPTION;
649
650 callingProgram VARCHAR2(80) := 'Check_Approval_Type';
651
652
653 l_approval_id NUMBER(15);
654 l_rule_id NUMBER(15);
655
656 l_approval_type VARCHAR2(30);
657 l_approvals_required VARCHAR2(30);
658
659 l_attribute VARCHAR2(30);
660 l_error_message VARCHAR2(2000);
661
662 l_book_type_code VARCHAR2(30);
663
664 localWFException EXCEPTION;
665
666
667 BEGIN
668
669 if (funcmode = 'RUN') then
670
671 -----------------------------------------------------
672 debugInfo := 'Get rule ID';
673 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
674 p_calling_fn => callingProgram,
675 p_parameter1 => debugInfo);
676 -----------------------------------------------------
677 l_rule_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
678 ,itemkey => itemkey
679 ,aname => 'RULE_ID');
680
681 -----------------------------------------------------
682 debugInfo := 'Get Book Type Code';
683 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
684 p_calling_fn => callingProgram,
685 p_parameter1 => debugInfo);
686 -----------------------------------------------------
687 l_book_type_code := WF_ENGINE.GetItemAttrText(itemtype => itemtype
688 ,itemkey => itemkey
689 ,aname => 'BOOK_TYPE_CODE');
690
691 -----------------------------------------------------
692 debugInfo := 'Get Approval Type';
693 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
694 p_calling_fn => callingProgram,
695 p_parameter1 => debugInfo);
696 -----------------------------------------------------
697 l_approval_type := IA_WF_UTIL_PKG.GetApprovalType(p_rule_id => l_rule_id
698 ,p_book_type_code => l_book_type_code);
699
700 if (l_approval_type = IA_WF_UTIL_PKG.ApprovalTypeNone) then
701 l_approvals_required := 'NO';
702 else
703 l_approvals_required := 'YES';
704 end if;
705
706
707 begin
708
709 l_attribute := 'APPROVALS_REQUIRED';
710 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
711 itemkey => itemkey,
712 aname => 'APPROVALS_REQUIRED',
713 avalue => l_approvals_required);
714 exception
715 when others then
716 FND_MESSAGE.set_name('IA', 'IA_WF_SET_ATTRIBUTE_ERROR'); -- Error: Unable to set the workflow attribute ATTRIBUTE for request id, REQUEST_ID
717 FND_MESSAGE.set_token('ATTRIBUTE', l_attribute);
718 FND_MESSAGE.set_token('REQUEST_ID', itemkey);
719 l_error_message := FND_MESSAGE.Get;
720 raise localWFException;
721 end;
722
723 result := 'COMPLETE:'||l_approvals_required;
724
725 elsif (funcmode = 'CANCEL') THEN
726
727 result := 'COMPLETE';
728
729 end if;
730
731 EXCEPTION
732 WHEN localWFException THEN
733 FA_SRVR_MSG.add_message(calling_fn => l_error_message);
734 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo||' '||l_error_message, 'Error');
735 result := 'COMPLETE:ERROR';
736 WF_CORE.Context(IA_WF_UTIL_PKG.WF_TransactionType, callingProgram,
737 itemtype, itemkey, to_char(actid), debugInfo);
738 RAISE;
739
740 WHEN OTHERS THEN
741 FA_SRVR_MSG.add_message(
742 calling_fn => callingProgram||':'||debugInfo);
743 FA_SRVR_MSG.Add_SQL_Error(
744 calling_fn => callingProgram);
745 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, 'Error');
746 result := 'COMPLETE:ERROR';
747
748 WF_CORE.Context(IA_WF_UTIL_PKG.WF_TransactionType, callingProgram,
749 itemtype, itemkey, to_char(actid), debugInfo);
750 RAISE;
751
752 END Check_Approval_Type;
753
754 PROCEDURE Get_Next_Approver
755 (itemtype in varchar2
756 ,itemkey in varchar2
757 ,actid in number
758 ,funcmode in varchar2
759 ,result out nocopy varchar2)
760 IS
761
762 l_approval_id NUMBER(15) := NULL;
763 l_request_id NUMBER(15) := -1;
764
765
766 debugInfo VARCHAR2(255) := NULL;
767
768 l_error_message VARCHAR2(2000);
769
770 localException EXCEPTION;
771
772 callingProgram VARCHAR2(80) := 'Get_Next_Approver';
773
774
775 tempApprover AME_UTIL.approverRecord;
776 tempApprovers AME_UTIL.approversTable;
777
778
779 l_approver_id NUMBER(15) := NULL;
780 l_approver_name VARCHAR2(150);
781 l_approver_name_display VARCHAR2(150);
782
783 l_approver_role VARCHAR2(50);
784 l_approver_role_display VARCHAR2(150);
785
786 l_chain_phase VARCHAR2(30);
787 l_no_more_approver_flag VARCHAR2(1);
788
789
790 BEGIN
791
792 if (funcmode = 'RUN') then
793
794 -----------------------------------------------------
795 debugInfo := 'Get item attributes';
796 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
797 p_calling_fn => callingProgram,
798 p_parameter1 => debugInfo);
799 -----------------------------------------------------
800 l_request_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
801 ,itemkey => itemkey
802 ,aname => 'REQUEST_ID');
803
804 -----------------------------------------------------
805 l_chain_phase := WF_ENGINE.GetItemAttrText(itemtype => itemtype
806 ,itemkey => itemkey
807 ,aname => 'APPROVAL_CHAIN_PHASE');
808
809 -----------------------------------------------------
810 -- debugInfo := 'Get next approver ID';
811 debugInfo := 'Get next approver ID: l_chain_phase='||l_chain_phase;
812 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
813 p_calling_fn => callingProgram,
814 p_parameter1 => debugInfo);
815 -----------------------------------------------------
816 if (not IA_AME_REQUEST_PKG.GetNextApprover(RequestId => l_request_id
817 ,ChainPhase => l_chain_phase -- IN OUT
818 ,Approver => tempApprover
819 ,NoMoreApproverFlag => l_no_more_approver_flag )) then
820
821 FND_MESSAGE.set_name('IA', 'IA_AME_NEXT_APPROVER_ERROR'); -- Error: Error occurred when fetching a next approver from Oracle Approval Management.
822 l_error_message := FND_MESSAGE.Get;
823 raise localException;
824 end if;
825
826 l_approver_id := tempApprover.person_id;
827
828
829 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
830 itemkey => itemkey,
831 aname => 'APPROVAL_CHAIN_PHASE',
832 avalue => l_chain_phase);
833
834 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
835 itemkey => itemkey,
836 aname => 'APPROVAL_CHAIN_PHASE_DISP',
837 avalue => IA_WF_UTIL_PKG.GetLookupMeaning(p_lookup_type=>'APPROVAL_TYPE',
838 p_lookup_code=>l_chain_phase));
839
840 if (l_approver_id is NULL) then
841 -----------------------------------------------------
842 debugInfo := 'Logic for finally approved';
843 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
844 p_calling_fn => callingProgram,
845 p_parameter1 => debugInfo);
846 -----------------------------------------------------
847 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
848 itemkey => itemkey,
849 aname => 'NO_MORE_APPROVER_FLAG',
850 avalue => 'Y');
851
852 result := 'COMPLETE:NOT_FOUND';
853
854 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
855 p_calling_fn => callingProgram,
856 p_parameter1 => debugInfo);
857
858 else
859
860 -----------------------------------------------------
861 debugInfo := 'Derive the role name for the approver ID from DIRECTORY and set item attributes';
862 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
863 p_calling_fn => callingProgram,
864 p_parameter1 => debugInfo);
865 -----------------------------------------------------
866
867 /*
868 WF_DIRECTORY.GetRoleName('PER',
869 l_approver_id,
870 l_approver_role,
871 l_approver_role_display);
872 */
873
874 WF_DIRECTORY.GetUserName('PER',
875 l_approver_id,
876 l_approver_name,
877 l_approver_name_display);
878
879 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
880 itemkey => itemkey,
881 aname => 'APPROVER_ID',
882 avalue => l_approver_id);
883
884 -- l_approver_role := 'DEMO'; -- I guess fnd user needs to be set up for the role to be retrieved correctly.
885
886 /*
887 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
888 itemkey => itemkey,
889 aname => 'APPROVER_ROLE',
890 avalue => l_approver_role);
891 */
892
893 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
894 itemkey => itemkey,
895 aname => 'APPROVER_NAME',
896 avalue => l_approver_name);
897
898 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
899 itemkey => itemkey,
900 aname => 'APPROVER_NAME_DISP',
901 avalue => l_approver_name_display);
902
903 result := 'COMPLETE:FOUND';
904
905 end if;
906
907 elsif (funcmode = 'CANCEL') THEN
908
909 result := 'COMPLETE';
910
911 end if;
912
913 EXCEPTION
914 WHEN OTHERS THEN
915 FA_SRVR_MSG.add_message(
916 calling_fn => l_error_message);
917 FA_SRVR_MSG.add_message(
918 calling_fn => callingProgram||':'||debugInfo);
919 FA_SRVR_MSG.Add_SQL_Error(
920 calling_fn => callingProgram);
921 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, 'Error');
922 result := 'COMPLETE:ERROR';
923
924 WF_CORE.Context(IA_WF_UTIL_PKG.WF_TransactionType, callingProgram,
925 itemtype, itemkey, to_char(actid), debugInfo);
926 RAISE;
927
928
929 END Get_Next_Approver;
930
931
932 PROCEDURE Set_Role(p_role_name in varchar2)
933 IS
934 -- v_user_name fnd_user.user_name%type := 'FA_USER1';
935 user_name varchar2(100):=null;
936 user_display_name varchar2(100):=null;
937 language varchar2(100):=userenv('LANG');
938 territory varchar2(100):='America';
939 description varchar2(100):=NULL;
940 notification_preference varchar2(100):='MAILTEXT';
941 email_address varchar2(100):=NULL;
942 fax varchar2(100):=NULL;
943 status varchar2(100):='ACTIVE';
944 expiration_date varchar2(100):=NULL;
945 role_name varchar2(100):=NULL;
946 role_display_name varchar2(100):=NULL;
947 role_description varchar2(100):=NULL;
948 wf_id Number;
949
950 duplicate_user_or_role exception;
951 PRAGMA EXCEPTION_INIT (duplicate_user_or_role, -20002);
952
953 BEGIN
954
955 /* Create a role for ad hoc user */
956
957 role_name := p_role_name;
958
959 role_display_name := role_name || 'Dis';
960 -- email_address := '[email protected]';
961 email_address := '[email protected]';
962
963 begin
964
965 WF_Directory.CreateAdHocRole
966 (role_name, role_display_name,
967 language, territory, role_description, notification_preference,
968 user_name, email_address, fax, status, expiration_date);
969
970 exception
971 when duplicate_user_or_role then
972 WF_Directory.SetAdHocRoleAttr (role_name, role_display_name,
973 notification_preference, language, territory, email_address, fax);
974 end;
975
976 END;
977
978
979 /*
980 FUNCTION Respond
981 (p_request_id in NUMBER
982 ,p_result in VARCHAR2
983 ,p_delegatee_id in NUMBER
984 ,p_comment in VARCHAR2
985 )
986 return NUMBER
987 IS
988
989 l_error_message VARCHAR2(2000);
990
991 debugInfo VARCHAR2(255) := NULL;
992
993 localException EXCEPTION;
994
995 callingProgram VARCHAR2(80) := 'Respond';
996
997 l_notification_id NUMBER(15);
998 l_recipient_role VARCHAR2(320);
999 l_original_recipient VARCHAR2(320);
1000
1001 l_dummy NUMBER(15);
1002 l_approval_id NUMBER(15);
1003
1004 BEGIN
1005
1006 savepoint RESPOND_STEP1;
1007
1008 -----------------------------------------------------
1009 debugInfo := 'Initialize error message stack';
1010 -----------------------------------------------------
1011 FA_SRVR_MSG.init_server_message;
1012 IA_WF_UTIL_PKG.InitializeDebugMessage;
1013
1014 -----------------------------------------------------
1015 debugInfo := 'Check if the given request_id is valid';
1016 -----------------------------------------------------
1017 begin
1018
1019 select 1
1020 into l_dummy
1021 from ia_request_headers
1022 where request_id=p_request_id;
1023
1024 select max(approval_id)
1025 into l_approval_id
1026 from ia_request_approvals
1027 where request_id=p_request_id
1028 and status=IA_WF_UTIL_PKG.ApprovalStatusPendingApproval;
1029
1030 exception
1031 when others then
1032 FND_MESSAGE.set_name('IA', 'IA_NO_REQUEST_FOUND'); -- Error: Unable to find request id, REQUEST_ID
1033 FND_MESSAGE.set_token('REQUEST_ID', p_request_id);
1034 l_error_message := FND_MESSAGE.Get;
1035 raise localException;
1036 end;
1037
1038 -----------------------------------------------------
1039 debugInfo := 'Retrieve the notification ID for the given request_id';
1040 -----------------------------------------------------
1041 begin
1042
1043 select *+ leading(grp_id_view) *
1044 notification_id
1045 ,recipient_role
1046 ,original_recipient
1047 into l_notification_id
1048 ,l_recipient_role
1049 ,l_original_recipient
1050 from wf_notifications wfn ,
1051 ( select notification_id group_id
1052 from wf_item_activity_statuses
1053 where item_type = 'IAWF'
1054 and item_key = p_request_id
1055 ) grp_id_view
1056 where grp_id_view.group_id = wfn.group_id;
1057
1058 exception
1059 when others then
1060 FND_MESSAGE.set_name('IA', 'IA_NO_NOTIFICATION_FOUND'); -- Error: Unable to find the notification for request id, REQUEST_ID
1061 FND_MESSAGE.set_token('REQUEST_ID', p_request_id);
1062 l_error_message := FND_MESSAGE.Get;
1063 raise localException;
1064 end;
1065
1066 -----------------------------------------------------
1067 debugInfo := 'Set the result code for the given notification';
1068 -----------------------------------------------------
1069 WF_NOTIFICATION.SetAttrText(l_notification_id, 'RESULT', p_result);
1070 -- wf_notification.SetAttrText(l_notification_id, 'RESULT', 'APPROVED');
1071 -- wf_notification.SetAttrText(l_notification_id, 'APPROVAL_RESPONSE', 'APPROVED');
1072
1073 if (p_delegatee_id is NOT NULL) then
1074 -----------------------------------------------------
1075 debugInfo := 'Set the delegatee ID for the given notification';
1076 -----------------------------------------------------
1077 WF_NOTIFICATION.SetAttrNumber(l_notification_id, 'DELEGATEE_ID', p_delegatee_id);
1078 end if;
1079
1080 if (p_result='CANCELLED') then
1081
1082 -----------------------------------------------------
1083 debugInfo := 'Cancel the notification';
1084 -----------------------------------------------------
1085 -- WF_NOTIFICATION.Respond(l_notification_id, p_comment, 'DEMO');
1086 WF_NOTIFICATION.Cancel(l_notification_id, p_comment);
1087
1088 else
1089
1090 -----------------------------------------------------
1091 debugInfo := 'Respond the notification';
1092 -----------------------------------------------------
1093 -- WF_NOTIFICATION.Respond(l_notification_id, p_comment, 'DEMO');
1094 WF_NOTIFICATION.Respond(l_notification_id, p_comment, l_recipient_role);
1095
1096 end if;
1097
1098 -----------------------------------------------------
1099 debugInfo := 'Update ia_approval_requests with the notification ID';
1100 -----------------------------------------------------
1101 * COMMENTED OUT DUE TO NEW STANDARD WORKFLOW
1102 Update_Approval_Notify(p_approval_id => l_approval_id
1103 ,p_notification_id => l_notification_id
1104 ,p_user_comment => substr(p_comment,1,4000));
1105
1106 *
1107
1108 commit;
1109
1110 return 1;
1111
1112 EXCEPTION
1113
1114 WHEN localException THEN
1115 rollback to RESPOND_STEP1;
1116 FA_SRVR_MSG.add_message(calling_fn => l_error_message);
1117 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => p_request_id,
1118 p_calling_fn => callingProgram,
1119 p_parameter1 => debugInfo||' '||l_error_message);
1120
1121 return -1;
1122
1123 WHEN OTHERS THEN
1124 rollback to RESPOND_STEP1;
1125 FA_SRVR_MSG.add_message(calling_fn => l_error_message);
1126 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => p_request_id,
1127 p_calling_fn => callingProgram,
1128 p_parameter1 => debugInfo||' '||l_error_message);
1129
1130 return -1;
1131
1132 END Respond;
1133 */
1134
1135 /*
1136 PROCEDURE Insert_Next_Approver
1137 (itemtype in varchar2
1138 ,itemkey in varchar2
1139 ,actid in number
1140 ,funcmode in varchar2
1141 ,result out nocopy varchar2)
1142 IS
1143
1144 l_error_message VARCHAR2(2000);
1145
1146 debugInfo VARCHAR2(255) := NULL;
1147
1148 localException EXCEPTION;
1149
1150 callingProgram VARCHAR2(80) := 'Insert_Next_Approver';
1151
1152 l_request_id NUMBER(15) := NULL;
1153 l_chain_phase VARCHAR2(30);
1154 l_approver_id NUMBER(15) := NULL;
1155 l_approval_id NUMBER(15) := NULL;
1156
1157 tempReleasingApprovers AME_UTIL.approversTable;
1158 tempReceivingApprovers AME_UTIL.approversTable;
1159
1160 BEGIN
1161
1162 if (funcmode = 'RUN') then
1163
1164 null;
1165
1166 * COMMENTED OUT DUE TO NEW STANDARD WORKFLOW
1167 -----------------------------------------------------
1168 debugInfo := 'Get request ID';
1169 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1170 p_calling_fn => callingProgram,
1171 p_parameter1 => debugInfo);
1172 -----------------------------------------------------
1173 l_request_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1174 ,itemkey => itemkey
1175 ,aname => 'REQUEST_ID');
1176
1177 -----------------------------------------------------
1178 l_chain_phase := WF_ENGINE.GetItemAttrText(itemtype => itemtype
1179 ,itemkey => itemkey
1180 ,aname => 'APPROVAL_CHAIN_PHASE');
1181
1182 -----------------------------------------------------
1183 debugInfo := 'Get approver ID';
1184 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1185 p_calling_fn => callingProgram,
1186 p_parameter1 => debugInfo);
1187 -----------------------------------------------------
1188 l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1189 ,itemkey => itemkey
1190 ,aname => 'APPROVER_ID');
1191
1192 -----------------------------------------------------
1193 debugInfo := 'Insert into IA_REQUEST_APPROVALS table the next approver with status of Pending Approval';
1194 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1195 p_calling_fn => callingProgram,
1196 p_parameter1 => debugInfo);
1197 -----------------------------------------------------
1198 Insert_Approval(p_request_id => l_request_id
1199 ,p_approver_id => l_approver_id
1200 ,p_approval_status => IA_WF_UTIL_PKG.ApprovalStatusPendingApproval
1201 ,p_approval_chain_phase => l_chain_phase
1202 ,p_approval_id => l_approval_id);
1203
1204
1205 -----------------------------------------------------
1206 debugInfo := 'Set approval ID';
1207 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1208 p_calling_fn => callingProgram,
1209 p_parameter1 => debugInfo);
1210 -----------------------------------------------------
1211 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype
1212 ,itemkey => itemkey
1213 ,aname => 'APPROVAL_ID'
1214 ,avalue => l_approval_id);
1215
1216 -----------------------------------------------------
1217 debugInfo := 'Insert into IA_APPROVERS_LIST_T';
1218 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1219 p_calling_fn => callingProgram,
1220 p_parameter1 => debugInfo);
1221
1222 -----------------------------------------------------
1223 debugInfo := 'Calling IA_AME_REQUEST_PKG.GetAllApprovers';
1224 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1225 p_calling_fn => callingProgram,
1226 p_parameter1 => debugInfo);
1227
1228 -----------------------------------------------------
1229 if (not IA_AME_REQUEST_PKG.GetAllApprovers(RequestID => l_request_id,
1230 ReleasingApprovers => tempReleasingApprovers,
1231 ReceivingApprovers => tempReceivingApprovers) ) then
1232
1233 FND_MESSAGE.set_name('IA', 'IA_AME_NEXT_APPROVER_ERROR');
1234 l_error_message := FND_MESSAGE.Get;
1235 raise localException;
1236 end if;
1237
1238 Repopulate_Approvers_List(p_request_id => l_request_id,
1239 p_releasing_approvers => tempReleasingApprovers,
1240 p_receiving_approvers => tempReceivingApprovers);
1241 *
1242
1243
1244 elsif (funcmode = 'CANCEL') THEN
1245
1246 result := 'COMPLETE';
1247
1248 end if;
1249
1250 EXCEPTION
1251 WHEN OTHERS THEN
1252 FA_SRVR_MSG.add_message(
1253 calling_fn => callingProgram||':'||debugInfo);
1254 FA_SRVR_MSG.Add_SQL_Error(
1255 calling_fn => callingProgram);
1256 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, 'Error');
1257 result := 'COMPLETE:ERROR';
1258
1259 WF_CORE.Context(IA_WF_UTIL_PKG.WF_TransactionType, callingProgram,
1260 itemtype, itemkey, to_char(actid), debugInfo);
1261 RAISE;
1262
1263 END Insert_Next_Approver;
1264 */
1265
1266 /*
1267 PROCEDURE Insert_Approval
1268 (p_request_id in number
1269 ,p_approver_id in number
1270 ,p_approval_status in varchar2
1271 ,p_approval_chain_phase in varchar2
1272 ,p_approval_id out nocopy number
1273 )
1274 IS
1275
1276 debugInfo VARCHAR2(255) := NULL;
1277
1278 localException EXCEPTION;
1279
1280 callingProgram VARCHAR2(80) := 'Insert_Approval';
1281
1282
1283 l_approval_id number(15);
1284
1285
1286 PRAGMA AUTONOMOUS_TRANSACTION;
1287
1288 BEGIN
1289
1290 -----------------------------------------------------
1291 debugInfo := 'Get a new approval ID';
1292 -----------------------------------------------------
1293 select ia_request_approvals_s.nextval
1294 into l_approval_id
1295 from dual;
1296
1297 -----------------------------------------------------
1298 debugInfo := 'Insert into IA_REQUEST_APPROVALS';
1299 -----------------------------------------------------
1300 insert into ia_request_approvals
1301 (approval_id
1302 ,request_id
1303 ,approver_id
1304 ,status
1305 ,transaction_date
1306 ,approval_chain_phase
1307 ,created_by
1308 ,creation_date
1309 ,last_update_date
1310 ,last_updated_by
1311 ,last_update_login
1312 )
1313 values(l_approval_id
1314 ,p_request_id
1315 ,p_approver_id
1316 ,p_approval_status
1317 ,NULL
1318 ,p_approval_chain_phase
1319 ,nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
1320 ,sysdate
1321 ,sysdate
1322 ,nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
1323 ,nvl(to_number(FND_PROFILE.VALUE('LOGIN_ID')),-1)
1324 );
1325
1326 p_approval_id := l_approval_id;
1327
1328 commit;
1329
1330 EXCEPTION
1331 WHEN OTHERS THEN
1332 rollback;
1333 FA_SRVR_MSG.add_message(
1334 calling_fn => callingProgram||':'||debugInfo);
1335 FA_SRVR_MSG.Add_SQL_Error(
1336 calling_fn => callingProgram);
1337 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, 'Error');
1338 END Insert_Approval;
1339 */
1340
1341 /*
1342 PROCEDURE Repopulate_Approvers_List
1343 (p_request_id in number,
1344 p_releasing_approvers in AME_UTIL.approversTable,
1345 p_receiving_approvers in AME_UTIL.approversTable
1346 )
1347 IS
1348
1349 debugInfo VARCHAR2(255) := NULL;
1350
1351 l_error_message VARCHAR2(2000);
1352
1353 localException EXCEPTION;
1354
1355 callingProgram VARCHAR2(80) := 'Repopulate_Approvers_List';
1356
1357 itemkey NUMBER(15) := p_request_id;
1358
1359 tempApprovers AME_UTIL.approversTable;
1360 l_list_id NUMBER(15);
1361 l_approver_id NUMBER(15);
1362 l_ame_approval_status VARCHAR2(30);
1363 l_approval_status VARCHAR2(30);
1364 l_chain_phase VARCHAR2(30);
1365 l_phase_id NUMBER(15);
1366 l_approval_order NUMBER(15) := 0;
1367
1368 l_pending_approver_skipped VARCHAR2(1);
1369
1370 -- PRAGMA AUTONOMOUS_TRANSACTION;
1371
1372 BEGIN
1373
1374 -----------------------------------------------------
1375 debugInfo := 'Delete rows from IA_APPROVERS_LIST_T';
1376 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1377 p_calling_fn => callingProgram,
1378 p_parameter1 => debugInfo);
1379
1380 -----------------------------------------------------
1381 begin
1382 delete from ia_approvers_list_t
1383 where request_id = p_request_id;
1384 exception
1385 when others then
1386 null;
1387 end;
1388
1389 l_approval_order := 0;
1390 l_pending_approver_skipped := 'N';
1391
1392 for l_phase_id in 1 .. 2 loop
1393
1394 if ( l_phase_id = 1 ) then
1395 tempApprovers := p_releasing_approvers;
1396 l_chain_phase := IA_WF_UTIL_PKG.ApprovalTypeReleasing;
1397 else
1398 tempApprovers := p_receiving_approvers;
1399 l_chain_phase := IA_WF_UTIL_PKG.ApprovalTypeDestination;
1400 end if;
1401
1402 for i in 1 .. tempApprovers.count loop
1403
1404 l_approval_order := l_approval_order + 1;
1405 l_ame_approval_status := tempApprovers(i).approval_status;
1406
1407 * Please note that a person whose approval_status is null
1408 * is required to approve for a given request *
1409
1410 if (l_ame_approval_status is NULL and l_pending_approver_skipped = 'N') then
1411
1412 l_pending_approver_skipped := 'Y';
1413
1414 elsif (l_ame_approval_status is NULL and l_pending_approver_skipped = 'Y') then
1415
1416 -----------------------------------------------------
1417 debugInfo := 'Get a new list ID';
1418 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1419 p_calling_fn => callingProgram,
1420 p_parameter1 => debugInfo);
1421 -----------------------------------------------------
1422 select ia_approvers_list_t_s.nextval
1423 into l_list_id
1424 from dual;
1425
1426 l_approver_id := tempApprovers(i).person_id;
1427
1428 -----------------------------------------------------
1429 debugInfo := 'Insert into IA_APPROVERS_LIST_T';
1430 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1431 p_calling_fn => callingProgram,
1432 p_parameter1 => debugInfo);
1433 -----------------------------------------------------
1434 insert into ia_approvers_list_t
1435 (list_id
1436 ,request_id
1437 ,approver_id
1438 ,approval_order
1439 ,status
1440 ,approval_chain_phase
1441 ,created_by
1442 ,creation_date
1443 ,last_update_date
1444 ,last_updated_by
1445 ,last_update_login
1446 )
1447 values(l_list_id
1448 ,p_request_id
1449 ,l_approver_id
1450 ,l_approval_order
1451 ,IA_WF_UTIL_PKG.ApprovalStatusPendingApproval -- PENDING
1452 ,l_chain_phase
1453 ,nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
1454 ,sysdate
1455 ,sysdate
1456 ,nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
1457 ,nvl(to_number(FND_PROFILE.VALUE('LOGIN_ID')),-1)
1458 );
1459
1460 end if;
1461
1462 end loop;
1463
1464 end loop;
1465
1466 commit;
1467
1468 EXCEPTION
1469 WHEN OTHERS THEN
1470 rollback;
1471 FA_SRVR_MSG.add_message(
1472 calling_fn => callingProgram||':'||debugInfo);
1473 FA_SRVR_MSG.Add_SQL_Error(
1474 calling_fn => callingProgram);
1475 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, 'Error');
1476 END Repopulate_Approvers_List;
1477 */
1478
1479
1480 /*
1481 PROCEDURE Update_Approval_Notify
1482 (p_approval_id in number
1483 ,p_notification_id in number
1484 ,p_user_comment in varchar2
1485 )
1486 IS
1487
1488 debugInfo VARCHAR2(255) := NULL;
1489
1490 localException EXCEPTION;
1491
1492 callingProgram VARCHAR2(80) := 'Update_Approval_Notify';
1493
1494 BEGIN
1495
1496 update ia_request_approvals
1497 set notification_id = p_notification_id
1498 ,user_comment = p_user_comment
1499 ,last_update_date = SYSDATE
1500 ,last_updated_by = nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
1501 ,last_update_login = nvl(to_number(FND_PROFILE.VALUE('LOGIN_ID')),-1)
1502 where approval_id = p_approval_id;
1503
1504 END Update_Approval_Notify;
1505 */
1506
1507 /*
1508 PROCEDURE Update_Approval_Status
1509 (p_approval_id in number
1510 ,p_chain_phase in varchar2
1511 ,p_approval_status in varchar2
1512 )
1513 IS
1514
1515 debugInfo VARCHAR2(255) := NULL;
1516
1517 localException EXCEPTION;
1518
1519 callingProgram VARCHAR2(80) := 'Update_Approval_Status';
1520
1521 tempApprover AME_UTIL.approverRecord;
1522
1523 PRAGMA AUTONOMOUS_TRANSACTION;
1524
1525 BEGIN
1526
1527 update ia_request_approvals
1528 set status = p_approval_status
1529 ,approval_chain_phase = p_chain_phase
1530 ,transaction_date = SYSDATE
1531 ,last_update_date = SYSDATE
1532 ,last_updated_by = nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
1533 ,last_update_login = nvl(to_number(FND_PROFILE.VALUE('LOGIN_ID')),-1)
1534 where approval_id = p_approval_id;
1535
1536 COMMIT;
1537
1538 END Update_Approval_Status;
1539 */
1540
1541 PROCEDURE Update_Request_Header_Status
1542 (p_request_id in number
1543 ,p_status in varchar2
1544 )
1545 IS
1546
1547 debugInfo VARCHAR2(255) := NULL;
1548
1549 localException EXCEPTION;
1550
1551 callingProgram VARCHAR2(80) := 'Update_Request_Header_Status';
1552
1553 -- PRAGMA AUTONOMOUS_TRANSACTION;
1554
1555 BEGIN
1556
1557 update ia_request_headers
1558 set status = p_status
1559 ,last_update_date = SYSDATE
1560 ,last_updated_by = nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
1561 ,last_update_login = nvl(to_number(FND_PROFILE.VALUE('LOGIN_ID')),-1)
1562 where request_id = p_request_id;
1563
1564 COMMIT;
1565
1566 END Update_Request_Header_Status;
1567
1568
1569 PROCEDURE Update_Request_Line_Status
1570 (p_request_id in number
1571 ,p_status in varchar2
1572 )
1573 IS
1574
1575 debugInfo VARCHAR2(255) := NULL;
1576
1577 localException EXCEPTION;
1578
1579 callingProgram VARCHAR2(80) := 'Update_Request_Line_Status';
1580
1581 PRAGMA AUTONOMOUS_TRANSACTION;
1582
1583 BEGIN
1584
1585 /* We are updating the status only when the current status is 'PENDING'
1586 * since Super user can intervene the process by changing the line-level detail status.
1587 * As per a discussion with project team on Feb 19, 2004, we will have Super user see
1588 * the requests with status PENDING, POST, ON_REVIEW, or ON_HOLD on the SuperUser page.
1589 */
1590 update ia_request_details
1591 set status = p_status
1592 ,last_update_date = SYSDATE
1593 ,last_updated_by = nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
1594 ,last_update_login = nvl(to_number(FND_PROFILE.VALUE('LOGIN_ID')),-1)
1595 where request_id = p_request_id
1596 and status = IA_WF_UTIL_PKG.LineStatusPending;
1597
1598 COMMIT;
1599
1600 END Update_Request_Line_Status;
1601
1602
1603 PROCEDURE Send_Response_Notification
1604 (itemtype in varchar2
1605 ,itemkey in varchar2
1606 ,actid in number
1607 ,funcmode in varchar2
1608 ,result out nocopy varchar2)
1609 IS
1610
1611 l_request_id NUMBER(15) := -1;
1612 l_approval_id NUMBER(15) := -1;
1613 l_approver_role VARCHAR2(320);
1614 l_notification_id NUMBER(15);
1615
1616 l_user_comment VARCHAR2(4000) := NULL;
1617
1618 BEGIN
1619
1620 if (funcmode = 'RUN') then
1621
1622 l_request_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1623 ,itemkey => itemkey
1624 ,aname => 'REQUEST_ID');
1625
1626 l_approval_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1627 ,itemkey => itemkey
1628 ,aname => 'APPROVAL_ID');
1629
1630 l_approver_role := WF_ENGINE.GetItemAttrText(itemtype => itemtype
1631 ,itemkey => itemkey
1632 ,aname => 'APPROVER_ROLE');
1633
1634 l_notification_id := WF_NOTIFICATION.SEND(
1635 role => l_approver_role,
1636 msg_type => itemtype,
1637 msg_name => 'IA_MSG_REQUIRE_APPROVAL');
1638
1639 commit; -- absolutely necessary for notification ?
1640
1641 /* COMMENTED OUT DUE TO NEW STANDARD WORKFLOW
1642 Update_Approval_Notify(p_approval_id => l_approval_id
1643 ,p_notification_id => l_notification_id
1644 ,p_user_comment => l_user_comment);
1645 */
1646
1647 result := 'COMPLETE:XXXXXXX';
1648
1649
1650 elsif (funcmode = 'CANCEL') THEN
1651
1652 result := 'COMPLETE';
1653
1654 end if;
1655
1656
1657
1658 END Send_Response_Notification;
1659
1660 PROCEDURE Process_Approved
1661 (itemtype in varchar2
1662 ,itemkey in varchar2
1663 ,actid in number
1664 ,funcmode in varchar2
1665 ,result out nocopy varchar2)
1666 IS
1667
1668 debugInfo VARCHAR2(255) := NULL;
1669
1670 l_error_message VARCHAR2(2000);
1671
1672 localException EXCEPTION;
1673
1674 callingProgram VARCHAR2(80) := 'Process_Approved';
1675
1676 l_request_id NUMBER(15);
1677 l_chain_phase VARCHAR2(30);
1678 l_approval_id NUMBER(15);
1679 l_approver_id NUMBER(15);
1680
1681 tempApprover AME_UTIL.approverRecord;
1682
1683 BEGIN
1684
1685 if (funcmode = 'RUN') then
1686 -----------------------------------------------------
1687 debugInfo := 'Get request ID';
1688 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1689 p_calling_fn => callingProgram,
1690 p_parameter1 => debugInfo);
1691 -----------------------------------------------------
1692 l_request_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1693 ,itemkey => itemkey
1694 ,aname => 'REQUEST_ID');
1695
1696 -----------------------------------------------------
1697 l_chain_phase := WF_ENGINE.GetItemAttrText(itemtype => itemtype
1698 ,itemkey => itemkey
1699 ,aname => 'APPROVAL_CHAIN_PHASE');
1700
1701 -----------------------------------------------------
1702 debugInfo := 'Get approval ID';
1703 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1704 p_calling_fn => callingProgram,
1705 p_parameter1 => debugInfo);
1706 -----------------------------------------------------
1707 l_approval_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1708 ,itemkey => itemkey
1709 ,aname => 'APPROVAL_ID');
1710
1711 -----------------------------------------------------
1712 debugInfo := 'Get approver ID';
1713 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1714 p_calling_fn => callingProgram,
1715 p_parameter1 => debugInfo);
1716 -----------------------------------------------------
1717 l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1718 ,itemkey => itemkey
1719 ,aname => 'APPROVER_ID');
1720
1721 tempApprover.person_id := l_approver_id;
1722 tempApprover.approval_status := AME_UTIL.approvedStatus;
1723
1724 -----------------------------------------------------
1725 debugInfo := 'Update Approval Status';
1726 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1727 p_calling_fn => callingProgram,
1728 p_parameter1 => debugInfo);
1729 -----------------------------------------------------
1730 -- Bug#5002756: Disabled UpdateApprovalStatus in case of CostCenter method
1731 if (IA_WF_UTIL_PKG.GetApprovalMethod(p_rule_id => WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1732 ,itemkey => itemkey
1733 ,aname => 'RULE_ID')
1734 ,p_book_type_code => WF_ENGINE.GetItemAttrText(itemtype => itemtype
1735 ,itemkey => itemkey
1736 ,aname => 'BOOK_TYPE_CODE')
1737 )
1738 = IA_WF_UTIL_PKG.ApprovalMethodHierarchy ) then
1739
1740 if (not IA_AME_REQUEST_PKG.UpdateApprovalStatus(RequestId => l_request_id
1741 ,ChainPhase => l_chain_phase
1742 ,Approver => tempApprover)) then
1743 FND_MESSAGE.set_name('IA', 'IA_AME_UPDATE_STATUS_ERROR'); -- Error occurred when updating approval status in AME.
1744 l_error_message := FND_MESSAGE.Get;
1745 raise localException;
1746 end if;
1747 end if;
1748
1749 -----------------------------------------------------
1750 debugInfo := 'Set the current approval status to Approved';
1751 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1752 p_calling_fn => callingProgram,
1753 p_parameter1 => debugInfo);
1754 -----------------------------------------------------
1755 /* COMMENTED OUT DUE TO NEW STANDARD WORKFLOW
1756 Update_Approval_Status(p_approval_id => l_approval_id
1757 ,p_chain_phase => l_chain_phase
1758 ,p_approval_status => IA_WF_UTIL_PKG.ApprovalStatusApproved);
1759 */
1760
1761 elsif (funcmode = 'CANCEL') THEN
1762
1763 result := 'COMPLETE';
1764
1765 end if;
1766
1767 EXCEPTION
1768 WHEN OTHERS THEN
1769 FA_SRVR_MSG.add_message(
1770 calling_fn => callingProgram||':'||debugInfo);
1771 FA_SRVR_MSG.Add_SQL_Error(
1772 calling_fn => callingProgram);
1773 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, 'Error');
1774 result := 'COMPLETE:ERROR';
1775
1776 WF_CORE.Context(IA_WF_UTIL_PKG.WF_TransactionType, callingProgram,
1777 itemtype, itemkey, to_char(actid), debugInfo);
1778 RAISE;
1779
1780 END Process_Approved;
1781
1782 PROCEDURE Process_Delegated
1783 (itemtype in varchar2
1784 ,itemkey in varchar2
1785 ,actid in number
1786 ,funcmode in varchar2
1787 ,result out nocopy varchar2)
1788 IS
1789
1790 debugInfo VARCHAR2(255) := NULL;
1791
1792 l_error_message VARCHAR2(2000);
1793
1794 localException EXCEPTION;
1795
1796 callingProgram VARCHAR2(80) := 'Process_Delegated';
1797
1798 l_request_id NUMBER(15);
1799 l_chain_phase VARCHAR2(30);
1800 l_approval_id NUMBER(15);
1801 l_approver_id NUMBER(15);
1802 l_delegatee_id NUMBER(15);
1803
1804 tempApprover AME_UTIL.approverRecord;
1805 tempDelegatee AME_UTIL.approverRecord;
1806
1807 BEGIN
1808
1809 if (funcmode = 'RUN') then
1810 -----------------------------------------------------
1811 debugInfo := 'Get request ID';
1812 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1813 p_calling_fn => callingProgram,
1814 p_parameter1 => debugInfo);
1815 -----------------------------------------------------
1816 l_request_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1817 ,itemkey => itemkey
1818 ,aname => 'REQUEST_ID');
1819
1820 -----------------------------------------------------
1821 l_chain_phase := WF_ENGINE.GetItemAttrText(itemtype => itemtype
1822 ,itemkey => itemkey
1823 ,aname => 'APPROVAL_CHAIN_PHASE');
1824
1825 -----------------------------------------------------
1826 debugInfo := 'Get current approval ID';
1827 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1828 p_calling_fn => callingProgram,
1829 p_parameter1 => debugInfo);
1830 -----------------------------------------------------
1831 l_approval_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1832 ,itemkey => itemkey
1833 ,aname => 'APPROVAL_ID');
1834
1835 -----------------------------------------------------
1836 debugInfo := 'Get approver ID';
1837 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1838 p_calling_fn => callingProgram,
1839 p_parameter1 => debugInfo);
1840 -----------------------------------------------------
1841 l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1842 ,itemkey => itemkey
1843 ,aname => 'APPROVER_ID');
1844
1845 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, l_approver_id);
1846
1847 -----------------------------------------------------
1848 debugInfo := 'Get delegatee ID';
1849 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1850 p_calling_fn => callingProgram,
1851 p_parameter1 => debugInfo);
1852 -----------------------------------------------------
1853 l_delegatee_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1854 ,itemkey => itemkey
1855 ,aname => 'DELEGATEE_ID');
1856
1857 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, l_delegatee_id);
1858
1859 tempApprover.user_id := NULL;
1860 tempApprover.person_id := l_approver_id;
1861 tempApprover.authority := AME_UTIL.authorityApprover; -- ???
1862 tempApprover.approval_status := AME_UTIL.forwardStatus;
1863
1864 tempDelegatee.person_id := l_delegatee_id;
1865 tempDelegatee.api_insertion := AME_UTIL.apiInsertion;
1866 tempDelegatee.authority := AME_UTIL.authorityApprover;
1867 tempDelegatee.approval_status := NULL;
1868
1869 -----------------------------------------------------
1870 debugInfo := 'Update AME Approval Status';
1871 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1872 p_calling_fn => callingProgram,
1873 p_parameter1 => debugInfo);
1874 -----------------------------------------------------
1875 -- Bug#5002756: Disabled UpdateApprovalStatus in case of CostCenter method
1876 if (IA_WF_UTIL_PKG.GetApprovalMethod(p_rule_id => WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1877 ,itemkey => itemkey
1878 ,aname => 'RULE_ID')
1879 ,p_book_type_code => WF_ENGINE.GetItemAttrText(itemtype => itemtype
1880 ,itemkey => itemkey
1881 ,aname => 'BOOK_TYPE_CODE')
1882 )
1883 = IA_WF_UTIL_PKG.ApprovalMethodHierarchy ) then
1884 if (not IA_AME_REQUEST_PKG.UpdateApprovalStatus(RequestId => l_request_id
1885 ,ChainPhase => l_chain_phase
1886 ,Approver => tempApprover
1887 ,Forwardee => tempDelegatee)) then
1888 FND_MESSAGE.set_name('IA', 'IA_AME_UPDATE_STATUS_ERROR'); -- Error occurred when updating approval status in Oracle Approval Management.
1889 l_error_message := FND_MESSAGE.Get;
1890 raise localException;
1891 end if;
1892 end if;
1893
1894 -----------------------------------------------------
1895 debugInfo := 'Set the current approval status to Delegated';
1896 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1897 p_calling_fn => callingProgram,
1898 p_parameter1 => debugInfo);
1899 -----------------------------------------------------
1900 /* COMMENTED OUT DUE TO NEW STANDARD WORKFLOW
1901 Update_Approval_Status(p_approval_id => l_approval_id
1902 ,p_chain_phase => l_chain_phase
1903 ,p_approval_status => IA_WF_UTIL_PKG.ApprovalStatusDelegated);
1904 */
1905
1906 elsif (funcmode = 'CANCEL') THEN
1907
1908 result := 'COMPLETE';
1909
1910 end if;
1911
1912 EXCEPTION
1913 WHEN OTHERS THEN
1914 FA_SRVR_MSG.add_message(
1915 calling_fn => callingProgram||':'||debugInfo);
1916 FA_SRVR_MSG.Add_SQL_Error(
1917 calling_fn => callingProgram);
1918 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, 'Error');
1919 result := 'COMPLETE:ERROR';
1920
1921 WF_CORE.Context(IA_WF_UTIL_PKG.WF_TransactionType, callingProgram||'--'||debugInfo||'--SQLERRM:'||substr(sqlerrm, 1, 240),
1922 itemtype, itemkey, to_char(actid), debugInfo);
1923 RAISE;
1924
1925 END Process_Delegated;
1926
1927 PROCEDURE Process_Rejected
1928 (itemtype in varchar2
1929 ,itemkey in varchar2
1930 ,actid in number
1931 ,funcmode in varchar2
1932 ,result out nocopy varchar2)
1933 IS
1934
1935 debugInfo VARCHAR2(255) := NULL;
1936
1937 l_error_message VARCHAR2(2000);
1938
1939 localException EXCEPTION;
1940
1941 callingProgram VARCHAR2(80) := 'Process_Rejected';
1942
1943 l_request_id NUMBER(15);
1944 l_chain_phase VARCHAR2(30);
1945 l_approval_id NUMBER(15);
1946 l_approver_id NUMBER(15);
1947
1948 tempApprover AME_UTIL.approverRecord;
1949
1950 BEGIN
1951
1952 if (funcmode = 'RUN') then
1953 -----------------------------------------------------
1954 debugInfo := 'Get request ID';
1955 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1956 p_calling_fn => callingProgram,
1957 p_parameter1 => debugInfo);
1958 -----------------------------------------------------
1959 l_request_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1960 ,itemkey => itemkey
1961 ,aname => 'REQUEST_ID');
1962
1963 -----------------------------------------------------
1964 l_chain_phase := WF_ENGINE.GetItemAttrText(itemtype => itemtype
1965 ,itemkey => itemkey
1966 ,aname => 'APPROVAL_CHAIN_PHASE');
1967
1968 -----------------------------------------------------
1969 debugInfo := 'Get approval ID';
1970 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1971 p_calling_fn => callingProgram,
1972 p_parameter1 => debugInfo);
1973 -----------------------------------------------------
1974 l_approval_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1975 ,itemkey => itemkey
1976 ,aname => 'APPROVAL_ID');
1977
1978 -----------------------------------------------------
1979 debugInfo := 'Get approver ID';
1980 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1981 p_calling_fn => callingProgram,
1982 p_parameter1 => debugInfo);
1983 -----------------------------------------------------
1984 l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1985 ,itemkey => itemkey
1986 ,aname => 'APPROVER_ID');
1987
1988 tempApprover.person_id := l_approver_id;
1989 tempApprover.approval_status := AME_UTIL.rejectStatus;
1990
1991 -----------------------------------------------------
1992 debugInfo := 'Update Approval Status';
1993 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
1994 p_calling_fn => callingProgram,
1995 p_parameter1 => debugInfo);
1996 -----------------------------------------------------
1997 -- Bug#5002756: Disabled UpdateApprovalStatus in case of CostCenter method
1998 if (IA_WF_UTIL_PKG.GetApprovalMethod(p_rule_id => WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1999 ,itemkey => itemkey
2000 ,aname => 'RULE_ID')
2001 ,p_book_type_code => WF_ENGINE.GetItemAttrText(itemtype => itemtype
2002 ,itemkey => itemkey
2003 ,aname => 'BOOK_TYPE_CODE')
2004 )
2005 = IA_WF_UTIL_PKG.ApprovalMethodHierarchy ) then
2006 if (not IA_AME_REQUEST_PKG.UpdateApprovalStatus(RequestId => l_request_id
2007 ,ChainPhase => l_chain_phase
2008 ,Approver => tempApprover)) then
2009 FND_MESSAGE.set_name('IA', 'IA_AME_UPDATE_STATUS_ERROR'); -- Error occurred when updating approval status in Oracle Approvals Management.
2010 l_error_message := FND_MESSAGE.Get;
2011 raise localException;
2012 end if;
2013 end if;
2014
2015 -----------------------------------------------------
2016 debugInfo := 'Set the current approval status to Rejected';
2017 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2018 p_calling_fn => callingProgram,
2019 p_parameter1 => debugInfo);
2020 -----------------------------------------------------
2021 /* COMMENTED OUT DUE TO NEW STANDARD WORKFLOW
2022 Update_Approval_Status(p_approval_id => l_approval_id
2023 ,p_chain_phase => l_chain_phase
2024 ,p_approval_status => IA_WF_UTIL_PKG.ApprovalStatusRejected);
2025 */
2026
2027 -----------------------------------------------------
2028 debugInfo := 'Set the current request status to Rejected';
2029 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2030 p_calling_fn => callingProgram,
2031 p_parameter1 => debugInfo);
2032 -----------------------------------------------------
2033 Update_Request_Header_Status(p_request_id => l_request_id
2034 ,p_status => IA_WF_UTIL_PKG.HeaderStatusRejected);
2035
2036 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
2037 itemkey => itemkey,
2038 aname => 'REQUEST_STATUS',
2039 avalue => IA_WF_UTIL_PKG.HeaderStatusRejected);
2040
2041 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
2042 itemkey => itemkey,
2043 aname => 'REQUEST_STATUS_DISP',
2044 avalue => IA_WF_UTIL_PKG.GetLookupMeaning(p_lookup_type=>'REQ_HDR_STATUS',
2045 p_lookup_code=>IA_WF_UTIL_PKG.HeaderStatusRejected));
2046 -----------------------------------------------------
2047 debugInfo := 'Set the current detail status to Rejected';
2048 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2049 p_calling_fn => callingProgram,
2050 p_parameter1 => debugInfo);
2051 -----------------------------------------------------
2052 Update_Request_Line_Status(p_request_id => l_request_id
2053 ,p_status => IA_WF_UTIL_PKG.LineStatusRejected);
2054
2055 elsif (funcmode = 'CANCEL') THEN
2056
2057 result := 'COMPLETE';
2058
2059 end if;
2060
2061 EXCEPTION
2062 WHEN OTHERS THEN
2063 FA_SRVR_MSG.add_message(
2064 calling_fn => callingProgram||':'||debugInfo);
2065 FA_SRVR_MSG.Add_SQL_Error(
2066 calling_fn => callingProgram);
2067 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, 'Error');
2068 result := 'COMPLETE:ERROR';
2069
2070 WF_CORE.Context(IA_WF_UTIL_PKG.WF_TransactionType, callingProgram,
2071 itemtype, itemkey, to_char(actid), debugInfo);
2072 RAISE;
2073
2074 END Process_Rejected;
2075
2076 PROCEDURE Process_Cancelled
2077 (itemtype in varchar2
2078 ,itemkey in varchar2
2079 ,actid in number
2080 ,funcmode in varchar2
2081 ,result out nocopy varchar2)
2082 IS
2083
2084 debugInfo VARCHAR2(255) := NULL;
2085
2086 l_error_message VARCHAR2(2000);
2087
2088 localException EXCEPTION;
2089
2090 callingProgram VARCHAR2(80) := 'Process_Cancelled';
2091
2092 l_request_id NUMBER(15);
2093 l_chain_phase VARCHAR2(30);
2094 l_approval_id NUMBER(15);
2095 l_approver_id NUMBER(15);
2096
2097 tempApprover AME_UTIL.approverRecord;
2098
2099 BEGIN
2100
2101 if (funcmode = 'RUN') then
2102 -----------------------------------------------------
2103 debugInfo := 'Get request ID';
2104 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2105 p_calling_fn => callingProgram,
2106 p_parameter1 => debugInfo);
2107 -----------------------------------------------------
2108 l_request_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
2109 ,itemkey => itemkey
2110 ,aname => 'REQUEST_ID');
2111
2112 -----------------------------------------------------
2113 l_chain_phase := WF_ENGINE.GetItemAttrText(itemtype => itemtype
2114 ,itemkey => itemkey
2115 ,aname => 'APPROVAL_CHAIN_PHASE');
2116
2117 -----------------------------------------------------
2118 debugInfo := 'Get approval ID';
2119 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2120 p_calling_fn => callingProgram,
2121 p_parameter1 => debugInfo);
2122 -----------------------------------------------------
2123 l_approval_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
2124 ,itemkey => itemkey
2125 ,aname => 'APPROVAL_ID');
2126
2127 -----------------------------------------------------
2128 debugInfo := 'Get approver ID';
2129 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2130 p_calling_fn => callingProgram,
2131 p_parameter1 => debugInfo);
2132 -----------------------------------------------------
2133 l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
2134 ,itemkey => itemkey
2135 ,aname => 'APPROVER_ID');
2136
2137
2138 /****
2139 -----------------------------------------------------
2140 debugInfo := 'Set the current approval status to Rejected';
2141 -----------------------------------------------------
2142 Update_Approval_Status(p_approval_id => l_approval_id
2143 ,p_chain_phase => l_chain_phase
2144 ,p_approval_status => IA_WF_UTIL_PKG.ApprovalStatusRejected);
2145
2146 -----------------------------------------------------
2147 debugInfo := 'Set the current request status to Rejected';
2148 -----------------------------------------------------
2149 Update_Request_Header_Status(p_request_id => l_request_id
2150 ,p_status => IA_WF_UTIL_PKG.HeaderStatusRejected);
2151
2152 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
2153 itemkey => itemkey,
2154 aname => 'REQUEST_STATUS',
2155 avalue => IA_WF_UTIL_PKG.HeaderStatusRejected);
2156
2157 WF_ENGINE.SetItemAttrText(itemtype => IA_WF_UTIL_PKG.WF_TransactionType,
2158 itemkey => itemkey,
2159 aname => 'REQUEST_STATUS_DISP',
2160 avalue => IA_WF_UTIL_PKG.GetLookupMeaning(p_lookup_type=>'REQ_HDR_STATUS',
2161 p_lookup_code=>IA_WF_UTIL_PKG.HeaderStatusRejected));
2162 ****/
2163
2164
2165 elsif (funcmode = 'CANCEL') THEN
2166
2167 result := 'COMPLETE';
2168
2169 end if;
2170
2171 EXCEPTION
2172 WHEN OTHERS THEN
2173 FA_SRVR_MSG.add_message(
2174 calling_fn => callingProgram||':'||debugInfo);
2175 FA_SRVR_MSG.Add_SQL_Error(
2176 calling_fn => callingProgram);
2177 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, 'Error');
2178 result := 'COMPLETE:ERROR';
2179
2180 WF_CORE.Context(IA_WF_UTIL_PKG.WF_TransactionType, callingProgram,
2181 itemtype, itemkey, to_char(actid), debugInfo);
2182 RAISE;
2183
2184 END Process_Cancelled;
2185
2186 PROCEDURE Update_ApprovalStatus_To_Final
2187 (itemtype in varchar2
2188 ,itemkey in varchar2
2189 ,actid in number
2190 ,funcmode in varchar2
2191 ,result out nocopy varchar2)
2192 IS
2193
2194 debugInfo VARCHAR2(255) := NULL;
2195
2196 localException EXCEPTION;
2197
2198 callingProgram VARCHAR2(80) := 'Update_ApprovalStatus_To_Final';
2199
2200 l_approvals_required VARCHAR2(3);
2201
2202 l_request_id NUMBER(15);
2203 l_chain_phase VARCHAR2(30);
2204 l_approval_id NUMBER(15);
2205
2206 BEGIN
2207
2208 if (funcmode = 'RUN') then
2209
2210 -----------------------------------------------------
2211 debugInfo := 'Get request ID';
2212 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2213 p_calling_fn => callingProgram,
2214 p_parameter1 => debugInfo);
2215 -----------------------------------------------------
2216 l_request_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
2217 ,itemkey => itemkey
2218 ,aname => 'REQUEST_ID');
2219
2220 -----------------------------------------------------
2221 debugInfo := 'Get approvals required flag';
2222 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2223 p_calling_fn => callingProgram,
2224 p_parameter1 => debugInfo);
2225 -----------------------------------------------------
2226 l_approvals_required := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
2227 ,itemkey => itemkey
2228 ,aname => 'APPROVALS_REQUIRED');
2229
2230 if (l_approvals_required = 'YES') then
2231 -----------------------------------------------------
2232 debugInfo := 'Get approval chain phase';
2233 l_chain_phase := WF_ENGINE.GetItemAttrText(itemtype => itemtype
2234 ,itemkey => itemkey
2235 ,aname => 'APPROVAL_CHAIN_PHASE');
2236
2237 -----------------------------------------------------
2238 debugInfo := 'Get approval ID';
2239 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2240 p_calling_fn => callingProgram,
2241 p_parameter1 => debugInfo);
2242 -----------------------------------------------------
2243 l_approval_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
2244 ,itemkey => itemkey
2245 ,aname => 'APPROVAL_ID');
2246
2247 -----------------------------------------------------
2248 debugInfo := 'Update Approval Status to Finally Approved';
2249 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2250 p_calling_fn => callingProgram,
2251 p_parameter1 => debugInfo);
2252 -----------------------------------------------------
2253 /* COMMENTED OUT DUE TO NEW STANDARD WORKFLOW
2254 Update_Approval_Status(p_approval_id => l_approval_id
2255 ,p_chain_phase => l_chain_phase
2256 ,p_approval_status => IA_WF_UTIL_PKG.ApprovalStatusFinallyApproved);
2257 */
2258 end if;
2259
2260 -----------------------------------------------------
2261 debugInfo := 'Update Header Status to Approved';
2262 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2263 p_calling_fn => callingProgram,
2264 p_parameter1 => debugInfo);
2265 -----------------------------------------------------
2266 Update_Request_Header_Status(p_request_id => l_request_id
2267 ,p_status => IA_WF_UTIL_PKG.HeaderStatusApproved);
2268
2269 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2270 itemkey => itemkey,
2271 aname => 'REQUEST_STATUS',
2272 avalue => IA_WF_UTIL_PKG.HeaderStatusApproved);
2273
2274 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2275 itemkey => itemkey,
2276 aname => 'REQUEST_STATUS_DISP',
2277 avalue => IA_WF_UTIL_PKG.GetLookupMeaning(p_lookup_type=>'REQ_HDR_STATUS',
2278 p_lookup_code=>IA_WF_UTIL_PKG.HeaderStatusApproved));
2279
2280 result := 'COMPLETE:OK';
2281
2282 elsif (funcmode = 'CANCEL') THEN
2283
2284 result := 'COMPLETE';
2285
2286 end if;
2287
2288 EXCEPTION
2289 WHEN OTHERS THEN
2290 FA_SRVR_MSG.add_message(
2291 calling_fn => callingProgram||':'||debugInfo);
2292 FA_SRVR_MSG.Add_SQL_Error(
2293 calling_fn => callingProgram);
2294 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, 'Error');
2295 result := 'COMPLETE:ERROR';
2296
2297 WF_CORE.Context(IA_WF_UTIL_PKG.WF_TransactionType, callingProgram,
2298 itemtype, itemkey, to_char(actid), debugInfo);
2299 RAISE;
2300
2301 END Update_ApprovalStatus_To_Final;
2302
2303 PROCEDURE SuperUser_Approval_Required
2304 (itemtype in varchar2
2305 ,itemkey in varchar2
2306 ,actid in number
2307 ,funcmode in varchar2
2308 ,result out nocopy varchar2)
2309 IS
2310
2311 debugInfo VARCHAR2(255) := NULL;
2312
2313 localException EXCEPTION;
2314
2315 callingProgram VARCHAR2(80) := 'SuperUser_Approval_Required';
2316
2317
2318 l_approval_id NUMBER(15);
2319 l_rule_id NUMBER(15);
2320
2321 l_superuser_required VARCHAR2(1);
2322
2323 l_book_type_code VARCHAR2(30);
2324
2325 BEGIN
2326
2327 if (funcmode = 'RUN') then
2328 -----------------------------------------------------
2329 debugInfo := 'Get approval ID';
2330 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2331 p_calling_fn => callingProgram,
2332 p_parameter1 => debugInfo);
2333 -----------------------------------------------------
2334 l_approval_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
2335 ,itemkey => itemkey
2336 ,aname => 'APPROVAL_ID');
2337
2338 -----------------------------------------------------
2339 debugInfo := 'Get rule ID';
2340 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2341 p_calling_fn => callingProgram,
2342 p_parameter1 => debugInfo);
2343 -----------------------------------------------------
2344 l_rule_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
2345 ,itemkey => itemkey
2346 ,aname => 'RULE_ID');
2347
2348 -----------------------------------------------------
2349 debugInfo := 'Get Book Type Code';
2350 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2351 p_calling_fn => callingProgram,
2352 p_parameter1 => debugInfo);
2353 -----------------------------------------------------
2354 l_book_type_code := WF_ENGINE.GetItemAttrText(itemtype => itemtype
2355 ,itemkey => itemkey
2356 ,aname => 'BOOK_TYPE_CODE');
2357
2358 -----------------------------------------------------
2359 debugInfo := 'Check whether Super User approval is required';
2360 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2361 p_calling_fn => callingProgram,
2362 p_parameter1 => debugInfo);
2363 -----------------------------------------------------
2364 l_superuser_required := IA_WF_UTIL_PKG.IsSuperUserApprovalRequired(l_rule_id
2365 ,l_book_type_code);
2366
2367 result := 'COMPLETE:'||l_superuser_required;
2368
2369 elsif (funcmode = 'CANCEL') THEN
2370
2371 result := 'COMPLETE';
2372
2373 end if;
2374
2375 EXCEPTION
2376 WHEN OTHERS THEN
2377 FA_SRVR_MSG.add_message(
2378 calling_fn => callingProgram||':'||debugInfo);
2379 FA_SRVR_MSG.Add_SQL_Error(
2380 calling_fn => callingProgram);
2381 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, 'Error');
2382 result := 'COMPLETE:ERROR';
2383
2384 WF_CORE.Context(IA_WF_UTIL_PKG.WF_TransactionType, callingProgram,
2385 itemtype, itemkey, to_char(actid), debugInfo);
2386 RAISE;
2387
2388 END SuperUser_Approval_Required;
2389
2390 PROCEDURE Update_LineStatus_To_OnReview
2391 (itemtype in varchar2
2392 ,itemkey in varchar2
2393 ,actid in number
2394 ,funcmode in varchar2
2395 ,result out nocopy varchar2)
2396 IS
2397
2398 l_request_id number(15);
2399
2400 debugInfo VARCHAR2(255) := NULL;
2401
2402 localException EXCEPTION;
2403
2404 callingProgram VARCHAR2(80) := 'Update_LineStatus_To_Post';
2405
2406 BEGIN
2407
2408 if (funcmode = 'RUN') then
2409 -----------------------------------------------------
2410 debugInfo := 'Get request ID';
2411 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2412 p_calling_fn => callingProgram,
2413 p_parameter1 => debugInfo);
2414 -----------------------------------------------------
2415 l_request_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
2416 ,itemkey => itemkey
2417 ,aname => 'REQUEST_ID');
2418
2419 -----------------------------------------------------
2420 debugInfo := 'Update request line status to ON_REVIEW';
2421 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2422 p_calling_fn => callingProgram,
2423 p_parameter1 => debugInfo);
2424 -----------------------------------------------------
2425 Update_Request_Line_Status(p_request_id => l_request_id
2426 ,p_status => IA_WF_UTIL_PKG.LineStatusOnReview);
2427
2428 elsif (funcmode = 'CANCEL') THEN
2429
2430 result := 'COMPLETE';
2431
2432 end if;
2433
2434 EXCEPTION
2435 WHEN OTHERS THEN
2436 FA_SRVR_MSG.add_message(
2437 calling_fn => callingProgram||':'||debugInfo);
2438 FA_SRVR_MSG.Add_SQL_Error(
2439 calling_fn => callingProgram);
2440 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, 'Error');
2441 result := 'COMPLETE:ERROR';
2442
2443 WF_CORE.Context(IA_WF_UTIL_PKG.WF_TransactionType, callingProgram,
2444 itemtype, itemkey, to_char(actid), debugInfo);
2445 RAISE;
2446
2447 END Update_LineStatus_To_OnReview;
2448
2449 PROCEDURE Update_LineStatus_To_Post
2450 (itemtype in varchar2
2451 ,itemkey in varchar2
2452 ,actid in number
2453 ,funcmode in varchar2
2454 ,result out nocopy varchar2)
2455 IS
2456
2457 l_request_id number(15);
2458
2459 debugInfo VARCHAR2(255) := NULL;
2460
2461 localException EXCEPTION;
2462
2463 callingProgram VARCHAR2(80) := 'Update_LineStatus_To_Post';
2464
2465 BEGIN
2466
2467 if (funcmode = 'RUN') then
2468
2469 -----------------------------------------------------
2470 debugInfo := 'Get request ID';
2471 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2472 p_calling_fn => callingProgram,
2473 p_parameter1 => debugInfo);
2474 -----------------------------------------------------
2475 l_request_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
2476 ,itemkey => itemkey
2477 ,aname => 'REQUEST_ID');
2478
2479 -----------------------------------------------------
2480 debugInfo := 'Update request line status to POST';
2481 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2482 p_calling_fn => callingProgram,
2483 p_parameter1 => debugInfo);
2484 -----------------------------------------------------
2485 Update_Request_Line_Status(p_request_id => l_request_id
2486 ,p_status => IA_WF_UTIL_PKG.LineStatusPost);
2487
2488
2489 /*
2490 Added for Super User Feature
2491 */
2492 -----------------------------------------------------
2493 debugInfo := 'Update Header Status to Post';
2494 IA_WF_UTIL_PKG.AddWFDebugMessage(p_request_id => itemkey,
2495 p_calling_fn => callingProgram,
2496 p_parameter1 => debugInfo);
2497 -----------------------------------------------------
2498 Update_Request_Header_Status(p_request_id => l_request_id
2499 ,p_status => IA_WF_UTIL_PKG.HeaderStatusPost);
2500
2501 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2502 itemkey => itemkey,
2503 aname => 'REQUEST_STATUS',
2504 avalue => IA_WF_UTIL_PKG.HeaderStatusPost);
2505
2506 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2507 itemkey => itemkey,
2508 aname => 'REQUEST_STATUS_DISP',
2509 avalue => IA_WF_UTIL_PKG.GetLookupMeaning(p_lookup_type=>'REQ_HDR_STATUS',
2510 p_lookup_code=>IA_WF_UTIL_PKG.HeaderStatusPost));
2511
2512 result := 'COMPLETE:OK';
2513
2514 elsif (funcmode = 'CANCEL') THEN
2515
2516 result := 'COMPLETE';
2517
2518 end if;
2519
2520 EXCEPTION
2521 WHEN OTHERS THEN
2522 FA_SRVR_MSG.add_message(
2523 calling_fn => callingProgram||':'||debugInfo);
2524 FA_SRVR_MSG.Add_SQL_Error(
2525 calling_fn => callingProgram);
2526 IA_WF_UTIL_PKG.AddDebugMessage(callingProgram, debugInfo, 'Error');
2527 result := 'COMPLETE:ERROR';
2528
2529 WF_CORE.Context(IA_WF_UTIL_PKG.WF_TransactionType, callingProgram,
2530 itemtype, itemkey, to_char(actid), debugInfo);
2531 RAISE;
2532
2533 END Update_LineStatus_To_Post;
2534
2535 END IA_WF_REQUEST_PKG;