[Home] [Help]
PACKAGE BODY: APPS.FEM_FEMAPPR_ITEM_TYPE
Source
1 PACKAGE BODY FEM_FEMAPPR_ITEM_TYPE AS
2 /* $Header: FEMAPPRB.pls 120.2 2006/09/21 08:25:40 nmartine noship $ */
3
4 --------------------------------------------------------------------------------
5 -- PRIVATE CONSTANTS
6 --------------------------------------------------------------------------------
7
8 G_PKG_NAME constant varchar2(30) := 'FEM_FEMAPPR_ITEM_TYPE';
9
10 -- Item Type Constants
11 G_FEMAPPR constant varchar2(30) := 'FEMAPPR';
12 G_FEM_APPROVAL_PROCESS constant varchar2(30) := 'FEM_APPROVAL_PROCESS';
13
14 -- AME Constants (Bug Fix 3922758: WORKFLOW SUBMIT NEEDS TO CHANGE APP ID LOGIC)
15 G_FEM_APPLICATION_ID constant number := 274;
16
17 -- Workflow Directory Services Constants
18 G_PER constant varchar2(30) := 'PER';
19 G_FND_USR constant varchar2(30) := 'FND_USR';
20
21 -- Types and constants representing all notification response values
22 G_APPROVE constant varchar2(30) := 'APPROVED';
23 G_REJECT constant varchar2(30) := 'REJECTED';
24 G_NO_RESPONSE constant varchar2(30) := 'NO_RESPONSE';
25 G_SUCCESS constant varchar2(30) := 'SUCCESS';
26 G_FAILURE constant varchar2(30) := 'FAILURE';
27
28 -- Types and constants representing yes/no values
29 G_YES constant varchar2(1) := 'Y';
30 G_NO constant varchar2(1) := 'N';
31
32 -- Types and constants representing boolean values
33 G_TRUE constant varchar2(30) := 'TRUE';
34 G_FALSE constant varchar2(30) := 'FALSE';
35
36 -- Constants used in dynamic message generation
37 NL constant varchar2(1) := FND_GLOBAL.newline;
38
39 -- Miscellaneous Constants
40 G_NULL constant varchar2(30) := '';
41
42
43
44 --------------------------------------------------------------------------------
45 -- VARIABLE TYPE DEFINITIONS
46 --------------------------------------------------------------------------------
47
48 -- Business Event System
49 t_event_key varchar2(240);
50 t_event_name varchar2(240);
51
52 -- Lookup Code
53 t_code FND_LOOKUP_VALUES.lookup_code%TYPE;
54 t_meaning FND_LOOKUP_VALUES.meaning%TYPE;
55
56 -- Workflow
57 t_item_type WF_ITEMS.item_type%TYPE;
58 t_item_key WF_ITEMS.item_key%TYPE;
59
60 -- Oracle Applications
61 t_org_id number;
62 t_user_id FND_USER.user_id%TYPE;
63 t_user_name FND_USER.user_name%TYPE;
64 t_responsibility_id FND_RESPONSIBILITY.responsibility_id%TYPE;
65 t_application_id FND_APPLICATION.application_id%TYPE;
66
67 -- Oracle Approval Management (AME)
68 t_approval_status varchar2(50);
69
70 -- Workflow Directory Services
71 t_id number;
72 t_name varchar2(30);
73 t_display_name varchar2(80);
74 t_orig_system varchar2(30);
75
76 -- FEM Approval Workflow
77 t_request_id FEM_WF_REQUESTS.wf_request_id%TYPE;
78 t_plsql_pkg_name FEM_WF_REQ_ITEMS.item_plsql_pkg_name%TYPE;
79 t_document varchar2(32000);
80 t_url varchar2(2000);
81 t_justification varchar2(2000);
82
83 -- API Error Messages
84 t_msg_count number;
85 t_msg_data varchar2(2000);
86
87
88
89 --------------------------------------------------------------------------------
90 -- PRIVATE SPECIFICATIONS
91 --------------------------------------------------------------------------------
92
93 PROCEDURE InitApprovalRequestInternal (
94 p_item_type in varchar2,
95 p_item_key in varchar2
96 );
97
98 PROCEDURE UpdateApproval (
99 p_item_type in varchar2,
100 p_item_key in varchar2,
101 p_approval_status in varchar2
102 );
103
104 PROCEDURE PurgeApprovalRequest(
105 p_item_type in varchar2
106 ,p_item_key in varchar2
107 );
108
109 FUNCTION GetUserId (
110 p_user_name in varchar2
111 )
112 RETURN t_id%TYPE;
113
114 PROCEDURE BuildErrorMsg (
115 p_api_name in varchar2
116 ,p_item_type in varchar2
117 ,p_item_key in varchar2
118 ,p_act_id in number
119 );
120
121
122
123 --------------------------------------------------------------------------------
124 -- PUBLIC BODIES
125 --------------------------------------------------------------------------------
126
127 --
128 -- FUNCTION
129 -- CreateWfRequestRow
130 --
131 -- DESCRIPTION
132 -- PL/SQL API for creating a row in the FEM_WF_REQUESTS table. Used for
133 -- raising FEM Business Events.
134 --
135 -- IN
136 -- p_request_item_code - Request Item Code (ie: BUSINESS_RULE)
137 -- p_request_type_code - Request Type Code (ie: APPROVAL, DELETE)
138 --
139 -- RETURN
140 -- wf_request_id - Workflow Request Id
141 --
142 --------------------------------------------------------------------------------
143 FUNCTION CreateWfRequestRow (
144 p_request_item_code in varchar2
145 ,p_request_type_code in varchar2
146 )
147 RETURN number
148 --------------------------------------------------------------------------------
149 IS
150
151 l_wf_request_id number;
152
153 BEGIN
154
155 select fem_wf_request_id_seq.NEXTVAL
156 into l_wf_request_id
157 from dual;
158
159 insert into fem_wf_requests (
160 wf_request_id
161 ,wf_request_item_code
162 ,wf_request_type_code
163 ,creation_date
164 ,created_by
165 ,last_updated_by
166 ,last_update_date
167 ,last_update_login
168 ,object_version_number
169 ) values (
170 l_wf_request_id
171 ,p_request_item_code
172 ,p_request_type_code
173 ,sysdate
174 ,FND_GLOBAL.User_Id
175 ,FND_GLOBAL.User_Id
176 ,sysdate
177 ,FND_GLOBAL.Login_Id
178 ,1
179 );
180
181 return l_wf_request_id;
182
183 END CreateWfRequestRow;
184
185
186 --
187 -- PROCEDURE
188 -- RaiseEvent
189 --
190 -- DESCRIPTION
191 -- PL/SQL API for Raising any FEM Business Events
192 --
193 -- IN
194 -- p_event_name - Business Event Name
195 -- p_request_id - Workflow Request Id (FEM_WF_REQUEST_ID_SEQ)
196 -- p_user_id - FND User ID
197 -- p_user_name - FND User Name
198 -- p_responsibility_id - FND Responsibility ID
199 -- p_application_id - FND Application ID
200 -- p_org_id - FND Organization ID
201 --
202 --------------------------------------------------------------------------------
203 PROCEDURE RaiseEvent (
204 p_event_name in varchar2,
205 p_request_id in number,
206 p_user_id in number,
207 p_user_name in varchar2,
208 p_responsibility_id in number,
209 p_application_id in number,
210 p_org_id in number
211 )
212 --------------------------------------------------------------------------------
213 IS
214
215 l_parameter_list wf_parameter_list_t;
216 l_event wf_event_t;
217 l_event_key t_event_key%TYPE;
218
219 BEGIN
220
221 l_event_key := to_char(p_request_id);
222
223 wf_event_t.initialize(l_event);
224
225 l_event.AddParameterToList(G_REQUEST_ID, p_request_id);
226 l_event.AddParameterToList(G_USER_ID, p_user_id);
227 l_event.AddParameterToList(G_USER_NAME, p_user_name);
228 l_event.AddParameterToList(G_RESPONSIBILITY_ID, p_responsibility_id);
229 l_event.AddParameterToList(G_APPLICATION_ID, p_application_id);
233
230 l_event.AddParameterToList(G_ORG_ID, p_org_id);
231
232 l_parameter_list := l_event.getParameterList();
234 WF_EVENT.raise(
235 p_event_name => p_event_name
236 ,p_event_key => l_event_key
237 ,p_parameters => l_parameter_list);
238
239 l_parameter_list.delete;
240
241 commit;
242
243 END RaiseEvent;
244
245
246 --
247 -- PROCEDURE
248 -- InitApprovalRequest
249 --
250 -- DESCRIPTION
251 -- Initializes the all the item attributes of an FEM Approvals Workflow.
252 --
253 -- IN
254 -- p_item_type - The workflow item type (FEMAPPR)
255 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
256 -- p_act_id - The function activity
257 -- p_func_mode - Run/Cancel/Timeout
258 --
259 -- OUT
260 -- x_result_out - Indicates completion of workflow activity and it's result
261 --
262 -- USED BY ACTIVITIES
263 -- FEMAPPR FEM_INIT_APPROVAL_REQ
264 --
265 --------------------------------------------------------------------------------
266 PROCEDURE InitApprovalRequest (
267 p_item_type in varchar2,
268 p_item_key in varchar2,
269 p_act_id in number,
270 p_func_mode in varchar2,
271 x_result_out out nocopy varchar2
272 )
273 --------------------------------------------------------------------------------
274 IS
275
276 l_api_name constant varchar2(30) := 'InitApprovalRequest';
277
278 l_user_name t_user_name%TYPE;
279 l_submitter_orig_system t_orig_system%TYPE;
280 l_submitter_id t_id%TYPE;
281 l_submitter_name t_name%TYPE;
282 l_submitter_display_name t_display_name%TYPE;
283
284 BEGIN
285
286 if (p_func_mode = 'RUN') then
287
288 -- Initialize API message list
289 FND_MSG_PUB.Initialize;
290
291 -- Set all the Approval Type information on the Item Attributes
292 InitApprovalRequestInternal(
293 p_item_type => p_item_type,
294 p_item_key => p_item_key
295 );
296
297 -- Set all the Submitter information on the Item Attributes
298 l_user_name := WF_ENGINE.GetItemAttrText(
299 p_item_type, p_item_key, G_USER_NAME);
300
301 WF_DIRECTORY.GetRoleOrigSysInfo(
302 l_user_name,
303 l_submitter_orig_system,
304 l_submitter_id);
305
306 WF_DIRECTORY.GetRoleName(
307 l_submitter_orig_system
308 ,l_submitter_id
309 ,l_submitter_name
310 ,l_submitter_display_name);
311
312 WF_ENGINE.SetItemAttrText(
313 p_item_type, p_item_key, G_SUBMITTER_ORIG_SYSTEM, l_submitter_orig_system);
314
315 WF_ENGINE.SetItemAttrText(
316 p_item_type, p_item_key, G_SUBMITTER_ID, l_submitter_id);
317
318 WF_ENGINE.SetItemAttrText(
319 p_item_type, p_item_key, G_SUBMITTER_NAME, l_submitter_name);
320
321 WF_ENGINE.SetItemAttrText(
322 p_item_type, p_item_key, G_SUBMITTER_DISPLAY_NAME, l_submitter_display_name);
323
324 -- Set the Approval information on the Item Attributes
325 WF_ENGINE.SetItemAttrDate(
326 p_item_type, p_item_key, G_REQUEST_DATE, trunc(sysdate));
327
328 x_result_out := WF_ENGINE.eng_completed;
329 return;
330
331 end if;
332
333 EXCEPTION
334
335 when FND_API.G_EXC_ERROR or FND_API.G_EXC_UNEXPECTED_ERROR then
336 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
337 raise;
338
339 when others then
340 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
341 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
342 raise;
343
344 END InitApprovalRequest;
345
346
347 --
348 -- PROCEDURE
349 -- CheckApprovalItems
350 --
351 -- DESCRIPTION
352 -- Checks to see if this approval request contains approval items.
353 --
354 -- IN
355 -- p_item_type - The workflow item type (FEMAPPR)
356 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
357 -- p_act_id - The function activity
358 -- p_func_mode - Run/Cancel/Timeout
359 --
360 -- OUT
361 -- x_result_out - Indicates completion of workflow activity and it's result
362 --
363 -- USED BY ACTIVITIES
364 -- FEMAPPR FEM_CHECK_APPROVAL_ITEMS
365 --
366 --------------------------------------------------------------------------------
367 PROCEDURE CheckApprovalItems (
368 p_item_type in varchar2,
369 p_item_key in varchar2,
370 p_act_id in number,
371 p_func_mode in varchar2,
372 x_result_out out nocopy varchar2
373 )
374 --------------------------------------------------------------------------------
375 IS
376
377 l_api_name constant varchar2(30) := 'CheckApprovalItems';
378
379 l_request_item_code t_code%TYPE;
380 l_item_plsql_pkg_name t_plsql_pkg_name%TYPE;
381 l_dynamic_query long;
382
383 BEGIN
384
385 if (p_func_mode = 'RUN') then
386
387 -- Initialize API message list
388 FND_MSG_PUB.Initialize;
389
390 l_item_plsql_pkg_name := WF_ENGINE.GetItemAttrText(
394
391 p_item_type, p_item_key, G_ITEM_PLSQL_PKG_NAME);
392
393 if (l_item_plsql_pkg_name is not null) then
395 l_dynamic_query :=
396 ' begin '||
397 ' :b_result_out := '||l_item_plsql_pkg_name||'.CheckApprovalItems('||
398 ' p_item_type => :b_item_type'||
399 ' ,p_item_key => :b_item_key'||
400 ' );'||
401 ' end;';
402
403 execute immediate l_dynamic_query
404 using out x_result_out
405 ,p_item_type
406 ,p_item_key;
407
408 else
409
410 l_request_item_code := WF_ENGINE.GetItemAttrText(
411 p_item_type, p_item_key, G_REQUEST_ITEM_CODE);
412
413 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name,
414 'Invalid Approval Request Item: '||l_request_item_code);
415 raise FND_API.G_EXC_UNEXPECTED_ERROR;
416
417 end if;
418
419 -- Finished Checking Approval Items
420 x_result_out := WF_ENGINE.eng_completed || ':' || x_result_out;
421 return;
422
423 end if;
424
425 EXCEPTION
426
427 when FND_API.G_EXC_ERROR or FND_API.G_EXC_UNEXPECTED_ERROR then
428 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
429 raise;
430
431 when others then
432 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
433 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
434 raise;
435
436 END CheckApprovalItems;
437
438
439 --
440 -- PROCEDURE
441 -- InitApprovalItems
442 --
443 -- DESCRIPTION
444 -- Initializes all the approval items.
445 --
446 -- IN
447 -- p_item_type - The workflow item type (FEMAPPR)
448 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
449 -- p_act_id - The function activity
450 -- p_func_mode - Run/Cancel/Timeout
451 --
452 -- OUT
453 -- x_result_out - Indicates completion of workflow activity and it's result
454 --
455 -- USED BY ACTIVITIES
456 -- FEMAPPR FEM_INIT_APPROVAL_ITEMS
457 --
458 --------------------------------------------------------------------------------
459 PROCEDURE InitApprovalItems (
460 p_item_type in varchar2,
461 p_item_key in varchar2,
462 p_act_id in number,
463 p_func_mode in varchar2,
464 x_result_out out nocopy varchar2
465 )
469 l_api_name constant varchar2(30) := 'InitApprovalItems';
466 --------------------------------------------------------------------------------
467 IS
468
470
471 l_request_item_code t_code%TYPE;
472 l_item_plsql_pkg_name t_plsql_pkg_name%TYPE;
473 l_dynamic_query long;
474
475 BEGIN
476
477 if (p_func_mode = 'RUN') then
478
479 -- Initialize API message list
480 FND_MSG_PUB.Initialize;
481
482 l_item_plsql_pkg_name := WF_ENGINE.GetItemAttrText(
483 p_item_type, p_item_key, G_ITEM_PLSQL_PKG_NAME);
484
485 if (l_item_plsql_pkg_name is not null) then
486
487 l_dynamic_query :=
488 ' begin '||
489 l_item_plsql_pkg_name||'.InitApprovalItems('||
490 ' p_item_type => :b_item_type'||
491 ' ,p_item_key => :b_item_key'||
492 ' );'||
493 ' end;';
494
495 execute immediate l_dynamic_query
496 using p_item_type
497 ,p_item_key;
498
499 else
500
501 l_request_item_code := WF_ENGINE.GetItemAttrText(
502 p_item_type, p_item_key, G_REQUEST_ITEM_CODE);
503
504 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name,
505 'Invalid Approval Request Item: '||l_request_item_code);
506 raise FND_API.G_EXC_UNEXPECTED_ERROR;
507
508 end if;
509
510 -- Finished Initializing Approval Items
511 x_result_out := WF_ENGINE.eng_completed;
512 return;
513
514 end if;
515
516 EXCEPTION
517
518 when FND_API.G_EXC_ERROR or FND_API.G_EXC_UNEXPECTED_ERROR then
519 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
520 raise;
521
522 when others then
523 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
524 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
525 raise;
526
527 END InitApprovalItems;
528
529
530 --
531 -- PROCEDURE
532 -- GetNextApprover
533 --
534 -- DESCRIPTION
535 -- Gets the next approver for the approval request.
536 --
537 -- IN
538 -- p_item_type - The workflow item type (FEMAPPR)
539 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
540 -- p_act_id - The function activity
541 -- p_func_mode - Run/Cancel/Timeout
542 --
543 -- OUT
544 -- x_result_out - Indicates completion of workflow activity and it's result
545 --
546 -- USED BY ACTIVITIES
547 -- FEMAPPR FEM_GET_NEXT_APPROVER
548 --
549 --------------------------------------------------------------------------------
550 PROCEDURE GetNextApprover (
551 p_item_type in varchar2,
552 p_item_key in varchar2,
553 p_act_id in number,
554 p_func_mode in varchar2,
555 x_result_out out nocopy varchar2
556 )
557 --------------------------------------------------------------------------------
558 IS
559
560 l_api_name constant varchar2(30) := 'GetNextApprover';
561
562 l_application_id t_application_id%TYPE;
563 l_request_id t_request_id%TYPE;
564
565 l_approver_id t_id%TYPE;
566 l_approver_user_id t_id%TYPE;
567 l_approver_name t_name%TYPE;
568 l_approver_display_name t_display_name%TYPE;
569 l_approver_orig_system t_orig_system%TYPE;
570
571 l_role_name t_name%TYPE;
572 l_role_display_name t_display_name%TYPE;
573
574 l_next_approver_rec AME_UTIL.approverRecord;
575
576 BEGIN
577
578 if (p_func_mode = 'RUN') then
579
580 -- Initialize API message list
581 FND_MSG_PUB.Initialize;
582
583 l_application_id := WF_ENGINE.GetItemAttrNumber(
584 p_item_type, p_item_key, G_APPLICATION_ID);
585
586 l_request_id := WF_ENGINE.GetItemAttrNumber(
587 p_item_type, p_item_key, G_REQUEST_ID);
588
589 AME_API.GetNextApprover(
590 -- Bug Fix 3922758: WORKFLOW SUBMIT NEEDS TO CHANGE APP ID LOGIC
591 G_FEM_APPLICATION_ID --l_application_id
592 ,l_request_id
593 ,G_FEMAPPR
594 ,l_next_approver_rec
595 );
596
597 if (l_next_approver_rec.approval_status = AME_UTIL.exceptionStatus) then
598
599 FND_MESSAGE.set_name('FEM', 'FEM_WF_APPR_NEXT_APPR_ERR');
600 FND_MSG_PUB.Add;
601 raise FND_API.G_EXC_ERROR;
602
603 end if;
604
605 if (l_next_approver_rec.person_id is not null) then
606
607 -- An Approver was found
608 l_approver_id := l_next_approver_rec.person_id;
609 l_approver_user_id := l_next_approver_rec.user_id;
610 l_approver_orig_system := G_PER;
611
612 elsif (l_next_approver_rec.user_id is not null) then
613
614 -- An Approver was found
615 l_approver_id := l_next_approver_rec.user_id;
616 l_approver_user_id := l_next_approver_rec.user_id;
617 l_approver_orig_system := G_FND_USR;
618
619 else
620
621 -- No more approvers were found
622 x_result_out := WF_ENGINE.eng_completed || ':' || G_NO;
623 return;
624
625 end if;
626
627 -- Setting all approver attributes
628 WF_DIRECTORY.GetRoleName(
629 l_approver_orig_system
630 ,l_approver_id
631 ,l_role_name
632 ,l_role_display_name
633 );
634
635 WF_DIRECTORY.GetUserName(
636 l_approver_orig_system
637 ,l_approver_id
638 ,l_approver_name
642 WF_ENGINE.SetItemAttrText(
639 ,l_approver_display_name
640 );
641
643 p_item_type, p_item_key, G_ROLE_NAME, l_role_name);
644
645 WF_ENGINE.SetItemAttrNumber(
646 p_item_type, p_item_key, G_APPROVER_ID, l_approver_id);
647
648 WF_ENGINE.SetItemAttrNumber(
649 p_item_type, p_item_key, G_APPROVER_USER_ID, l_approver_user_id);
650
651 WF_ENGINE.SetItemAttrText(
652 p_item_type, p_item_key, G_APPROVER_NAME, l_approver_name);
653
654 WF_ENGINE.SetItemAttrText(
655 p_item_type, p_item_key, G_APPROVER_DISPLAY_NAME, l_approver_display_name);
656
657 WF_ENGINE.SetItemAttrText(
658 p_item_type, p_item_key, G_APPROVER_ORIG_SYSTEM, l_approver_orig_system);
659
660 -- Setting all AME approver record attributes
661 WF_ENGINE.SetItemAttrText(
662 p_item_type, p_item_key, G_AME_AUTHORITY, l_next_approver_rec.authority);
663
664 WF_ENGINE.SetItemAttrText(
665 p_item_type, p_item_key, G_AME_API_INSERTION, l_next_approver_rec.api_insertion);
666
667 WF_ENGINE.SetItemAttrNumber(
668 p_item_type, p_item_key, G_AME_APPROVAL_TYPE_ID, l_next_approver_rec.approval_type_id);
669
670 WF_ENGINE.SetItemAttrNumber(
671 p_item_type, p_item_key, G_AME_GROUP_OR_CHAIN_ID, l_next_approver_rec.group_or_chain_id);
672
673 WF_ENGINE.SetItemAttrNumber(
674 p_item_type, p_item_key, G_AME_OCCURRENCE, l_next_approver_rec.occurrence);
675
676 x_result_out := WF_ENGINE.eng_completed || ':' || G_YES;
677 return;
678
679 end if;
680
681 EXCEPTION
682
683 when FND_API.G_EXC_ERROR or FND_API.G_EXC_UNEXPECTED_ERROR then
684 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
685 raise;
686
687 when others then
688 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
689 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
690 raise;
691
692 END GetNextApprover;
693
694
695 --
696 -- PROCEDURE
697 -- UpdateApprovalApproved
698 --
699 -- DESCRIPTION
700 -- Update the approval request with the approved response from the current
701 -- approver.
702 --
703 -- IN
704 -- p_item_type - The workflow item type (FEMAPPR)
705 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
706 -- p_act_id - The function activity
707 -- p_func_mode - Run/Cancel/Timeout
708 --
709 -- OUT
710 -- x_result_out - Indicates completion of workflow activity and it's result
711 --
712 -- USED BY ACTIVITIES
713 -- FEMAPPR FEM_UPDATE_APPROVAL_APPROVED
714 --
715 --------------------------------------------------------------------------------
716 PROCEDURE UpdateApprovalApproved (
717 p_item_type in varchar2,
718 p_item_key in varchar2,
719 p_act_id in number,
720 p_func_mode in varchar2,
721 x_result_out out nocopy varchar2
722 )
723 --------------------------------------------------------------------------------
724 IS
725
726 l_api_name constant varchar2(30) := 'UpdateApprovalApproved';
727
728 BEGIN
729
730 if (p_func_mode = 'RUN') then
731
732 -- Initialize API message list
733 FND_MSG_PUB.Initialize;
734
735 UpdateApproval(
736 p_item_type => p_item_type
737 ,p_item_key => p_item_key
738 ,p_approval_status => AME_UTIL.approvedStatus
739 );
740
741 x_result_out := WF_ENGINE.eng_completed;
742 return;
743
744 end if;
745
746 EXCEPTION
747
748 when FND_API.G_EXC_ERROR or FND_API.G_EXC_UNEXPECTED_ERROR then
749 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
750 raise;
751
752 when others then
753 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
754 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
755 raise;
756
757 END UpdateApprovalApproved;
758
759
760 --
761 -- PROCEDURE
762 -- UpdateApprovalRejected
763 --
764 -- DESCRIPTION
765 -- Update the approval request with the rejected response from the current
766 -- approver.
767 --
768 -- IN
769 -- p_item_type - The workflow item type (FEMAPPR)
770 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
771 -- p_act_id - The function activity
772 -- p_func_mode - Run/Cancel/Timeout
773 --
774 -- OUT
775 -- x_result_out - Indicates completion of workflow activity and it's result
776 --
777 -- USED BY ACTIVITIES
778 -- FEMAPPR FEM_UPDATE_APPROVAL_REJECTED
779 --
780 --------------------------------------------------------------------------------
781 PROCEDURE UpdateApprovalRejected (
782 p_item_type in varchar2,
783 p_item_key in varchar2,
784 p_act_id in number,
785 p_func_mode in varchar2,
786 x_result_out out nocopy varchar2
787 )
788 --------------------------------------------------------------------------------
789 IS
790
791 l_api_name constant varchar2(30) := 'UpdateApprovalRejected';
792
793 BEGIN
794
795 if (p_func_mode = 'RUN') then
796
797 -- Initialize API message list
798 FND_MSG_PUB.Initialize;
799
800 UpdateApproval(
801 p_item_type => p_item_type
802 ,p_item_key => p_item_key
803 ,p_approval_status => AME_UTIL.rejectStatus
804 );
805
809 end if;
806 x_result_out := WF_ENGINE.eng_completed;
807 return;
808
810
811 EXCEPTION
812
813 when FND_API.G_EXC_ERROR or FND_API.G_EXC_UNEXPECTED_ERROR then
814 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
815 raise;
816
817 when others then
818 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
819 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
820 raise;
821
822 END UpdateApprovalRejected;
823
824
825 --
826 -- PROCEDURE
827 -- UpdateApprovalNoResponse
828 --
829 -- DESCRIPTION
830 -- Update the approval request to indicate the lack of response from the
831 -- current approver.
832 --
833 -- IN
834 -- p_item_type - The workflow item type (FEMAPPR)
835 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
836 -- p_act_id - The function activity
837 -- p_func_mode - Run/Cancel/Timeout
838 --
839 -- OUT
840 -- x_result_out - Indicates completion of workflow activity and it's result
841 --
842 -- USED BY ACTIVITIES
843 -- FEMAPPR FEM_UPDATE_APPROVAL_NO_RESP
844 --
845 --------------------------------------------------------------------------------
846 PROCEDURE UpdateApprovalNoResponse (
847 p_item_type in varchar2,
848 p_item_key in varchar2,
849 p_act_id in number,
850 p_func_mode in varchar2,
851 x_result_out out nocopy varchar2
852 )
853 --------------------------------------------------------------------------------
854 IS
855
856 l_api_name constant varchar2(30) := 'UpdateApprovalNoResponse';
857
858 BEGIN
859
860 if (p_func_mode = 'RUN') then
861
862 -- Initialize API message list
863 FND_MSG_PUB.Initialize;
864
865 UpdateApproval(
866 p_item_type => p_item_type
867 ,p_item_key => p_item_key
868 ,p_approval_status => AME_UTIL.noResponseStatus
869 );
870
871 x_result_out := WF_ENGINE.eng_completed;
872 return;
873
874 end if;
875
876 EXCEPTION
877
878 when FND_API.G_EXC_ERROR or FND_API.G_EXC_UNEXPECTED_ERROR then
879 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
880 raise;
881
882 when others then
883 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
884 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
885 raise;
886
887 END UpdateApprovalNoResponse;
888
889
890 --
891 -- PROCEDURE
892 -- ApprovalRequestHandler
893 --
894 -- DESCRIPTION
895 -- Notification Handler for FEM_APPROVAL_REQ_NTF.
896 --
897 -- IN
898 -- p_item_type - The workflow item type (FEMAPPR)
899 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
900 -- p_act_id - The function activity
901 -- p_func_mode - Run/Cancel/Timeout
902 --
903 -- OUT
904 -- x_result_out - Indicates completion of workflow activity and it's result
905 --
906 -- USED BY ACTIVITIES
907 -- FEMAPPR FEM_APPROVAL_REQ_NTF
908 --
909 --------------------------------------------------------------------------------
910 PROCEDURE ApprovalRequestHandler (
911 p_item_type in varchar2,
912 p_item_key in varchar2,
913 p_act_id in number,
914 p_func_mode in varchar2,
915 x_result_out out nocopy varchar2
916 )
917 --------------------------------------------------------------------------------
918 IS
919
920 l_api_name constant varchar2(30) := 'ApprovalRequestHandler';
921
922 l_application_id t_application_id%TYPE;
923 l_request_id t_request_id%TYPE;
924
925 l_old_approver_id t_id%TYPE;
926 l_old_approver_orig_system t_orig_system%TYPE;
927
928 l_new_approver_id t_id%TYPE;
929 l_new_approver_user_id t_id%TYPE;
930 l_new_approver_name WF_ENGINE.context_text%TYPE;
931 l_new_approver_display_name t_display_name%TYPE;
932 l_new_approver_orig_system t_orig_system%TYPE;
933
934 l_old_approver_record AME_UTIL.approverRecord;
935 l_new_approver_record AME_UTIL.approverRecord;
936
937 l_approval_status t_approval_status%TYPE;
938
939 BEGIN
940
941 if (p_func_mode in ('FORWARD','TRANSFER')) then
942
943 -- Initialize API message list
944 FND_MSG_PUB.Initialize;
945
946 -- Get necessary attribute values for call to AME_API
947 l_application_id := WF_ENGINE.GetItemAttrNumber(
948 p_item_type, p_item_key, G_APPLICATION_ID);
949
950 l_request_id := WF_ENGINE.GetItemAttrNumber(
951 p_item_type, p_item_key, G_REQUEST_ID);
952
953 -- Identify if a forward or a transfer
954 if (p_func_mode = 'FORWARD') then
955
956 l_approval_status := AME_UTIL.approveAndForwardStatus;
957
958 elsif (p_func_mode = 'TRANSFER') then
959
960 l_approval_status := AME_UTIL.forwardStatus;
961
962 end if;
963
964 -- Get all AME approver record attributes
965 l_old_approver_record.approval_status := l_approval_status;
966 l_old_approver_record.authority := WF_ENGINE.GetItemAttrText(
967 p_item_type, p_item_key, G_AME_AUTHORITY);
968 l_old_approver_record.api_insertion := WF_ENGINE.GetItemAttrText(
969 p_item_type, p_item_key, G_AME_API_INSERTION);
970 l_old_approver_record.approval_type_id := WF_ENGINE.GetItemAttrNumber(
974 l_old_approver_record.occurrence := WF_ENGINE.GetItemAttrNumber(
971 p_item_type, p_item_key, G_AME_APPROVAL_TYPE_ID);
972 l_old_approver_record.group_or_chain_id := WF_ENGINE.GetItemAttrNumber(
973 p_item_type, p_item_key, G_AME_GROUP_OR_CHAIN_ID);
975 p_item_type, p_item_key, G_AME_OCCURRENCE);
976
977 -- Get the forwarding approver info
978 l_old_approver_id := WF_ENGINE.GetItemAttrNumber(
979 p_item_type, p_item_key, G_APPROVER_ID);
980
981 l_old_approver_orig_system := WF_ENGINE.GetItemAttrText(
982 p_item_type, p_item_key, G_APPROVER_ORIG_SYSTEM);
983
984 if (l_old_approver_orig_system = G_PER) then
985
986 l_old_approver_record.person_id := l_old_approver_id;
987
988 elsif (l_old_approver_orig_system = G_FND_USR) then
989
990 l_old_approver_record.user_id := l_old_approver_id;
991
992 else
993
994 -- Invalid Original System for Old Approver
995 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name,
996 'Invalid Original System for Old Approver: '||l_old_approver_orig_system);
997 raise FND_API.G_EXC_UNEXPECTED_ERROR;
998
999 end if;
1000
1001 -- Get the new approver name for the forwarded notification
1002 l_new_approver_name := WF_ENGINE.context_text;
1003
1004 WF_DIRECTORY.GetRoleOrigSysInfo(
1005 l_new_approver_name,
1006 l_new_approver_orig_system,
1007 l_new_approver_id);
1008
1009 WF_DIRECTORY.GetRoleName(
1010 l_new_approver_orig_system
1011 ,l_new_approver_id
1012 ,l_new_approver_name
1013 ,l_new_approver_display_name);
1014
1015 -- Populate the New Approver record
1016 if (l_new_approver_orig_system = G_PER) then
1017
1018 l_new_approver_user_id := GetUserId(l_new_approver_name);
1019 l_new_approver_record.person_id := l_new_approver_id;
1020
1021 elsif (l_new_approver_orig_system = G_FND_USR) then
1022
1023 l_new_approver_user_id := l_new_approver_id;
1024 l_new_approver_record.user_id := l_new_approver_id;
1025
1026 else
1027
1028 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name,
1029 'Invalid Original System for New Approver: '||l_new_approver_orig_system);
1030 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1031
1032 end if;
1033
1034 -- Default some values from forwarding approver record
1035 l_new_approver_record.authority := l_old_approver_record.authority;
1036 if (l_new_approver_record.authority = AME_UTIL.authorityApprover) then
1037
1038 l_new_approver_record.api_insertion := AME_UTIL.apiAuthorityInsertion;
1039
1040 else
1041
1042 l_new_approver_record.api_insertion := AME_UTIL.apiInsertion;
1043
1044 end if;
1045
1046 -- Updating Approval Request with new approver info
1047 AME_API.updateApprovalStatus(
1048 -- Bug Fix 3922758: WORKFLOW SUBMIT NEEDS TO CHANGE APP ID LOGIC
1049 applicationIdIn => G_FEM_APPLICATION_ID --l_application_id
1050 ,transactionIdIn => l_request_id
1051 ,approverIn => l_old_approver_record
1052 ,forwardeeIn => l_new_approver_record
1053 ,transactionTypeIn => G_FEMAPPR
1054 );
1055
1056 -- Setting all AME approver record attributes
1057 WF_ENGINE.SetItemAttrText(
1058 p_item_type, p_item_key, G_AME_AUTHORITY, l_new_approver_record.authority);
1059
1060 WF_ENGINE.SetItemAttrText(
1061 p_item_type, p_item_key, G_AME_API_INSERTION, l_new_approver_record.api_insertion);
1062
1063 WF_ENGINE.SetItemAttrNumber(
1064 p_item_type, p_item_key, G_AME_APPROVAL_TYPE_ID, l_new_approver_record.approval_type_id);
1065
1066 WF_ENGINE.SetItemAttrNumber(
1067 p_item_type, p_item_key, G_AME_GROUP_OR_CHAIN_ID, l_new_approver_record.group_or_chain_id);
1068
1069 WF_ENGINE.SetItemAttrNumber(
1070 p_item_type, p_item_key, G_AME_OCCURRENCE, l_new_approver_record.occurrence);
1071
1072 -- Update Approver Attributes with new approver info
1073 WF_ENGINE.SetItemAttrNumber(
1074 p_item_type, p_item_key, G_APPROVER_ID, l_new_approver_id);
1075
1076 WF_ENGINE.SetItemAttrNumber(
1077 p_item_type, p_item_key, G_APPROVER_USER_ID, l_new_approver_user_id);
1078
1079 WF_ENGINE.SetItemAttrText(
1080 p_item_type, p_item_key, G_APPROVER_NAME, l_new_approver_name);
1081
1082 WF_ENGINE.SetItemAttrText(
1083 p_item_type, p_item_key, G_APPROVER_DISPLAY_NAME, l_new_approver_display_name);
1084
1085 WF_ENGINE.SetItemAttrText(
1086 p_item_type, p_item_key, G_APPROVER_ORIG_SYSTEM, l_new_approver_orig_system);
1087
1088 WF_ENGINE.SetItemAttrText(
1089 p_item_type, p_item_key, G_ROLE_NAME, l_new_approver_name);
1090
1091 -- Finished Forwarding Approval Request to new Approver
1092 x_result_out := WF_ENGINE.eng_completed;
1093 return;
1094
1095 end if;
1096
1097 EXCEPTION
1098
1099 when FND_API.G_EXC_ERROR or FND_API.G_EXC_UNEXPECTED_ERROR then
1100 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
1101 raise;
1102
1103 when others then
1104 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1105 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
1106 raise;
1107
1108 END ApprovalRequestHandler;
1109
1110
1111 --
1112 -- PROCEDURE
1113 -- SetSubmittedState
1114 --
1115 -- DESCRIPTION
1116 -- Sets the approval items to the submitted state.
1117 --
1118 -- IN
1119 -- p_item_type - The workflow item type (FEMAPPR)
1120 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
1121 -- p_act_id - The function activity
1122 -- p_func_mode - Run/Cancel/Timeout
1123 --
1127 -- USED BY ACTIVITIES
1124 -- OUT
1125 -- x_result_out - Indicates completion of workflow activity and it's result
1126 --
1128 -- FEMAPPR FEM_SET_SUBMITTED_STATE
1129 --
1130 --------------------------------------------------------------------------------
1131 PROCEDURE SetSubmittedState (
1132 p_item_type in varchar2,
1133 p_item_key in varchar2,
1134 p_act_id in number,
1135 p_func_mode in varchar2,
1136 x_result_out out nocopy varchar2
1137 )
1138 --------------------------------------------------------------------------------
1139 IS
1140
1141 l_api_name constant varchar2(30) := 'SetSubmittedState';
1142
1143 l_request_item_code t_code%TYPE;
1144 l_item_plsql_pkg_name t_plsql_pkg_name%TYPE;
1145 l_dynamic_query long;
1146
1147 BEGIN
1148
1149 if (p_func_mode = 'RUN') then
1150
1151 -- Initialize API message list
1152 FND_MSG_PUB.Initialize;
1153
1154 l_item_plsql_pkg_name := WF_ENGINE.GetItemAttrText(
1155 p_item_type, p_item_key, G_ITEM_PLSQL_PKG_NAME);
1156
1157 if (l_item_plsql_pkg_name is not null) then
1158
1159 l_dynamic_query :=
1160 ' begin '||
1161 l_item_plsql_pkg_name||'.SetSubmittedState('||
1162 ' p_item_type => :b_item_type'||
1163 ' ,p_item_key => :b_item_key'||
1164 ' );'||
1165 ' end;';
1166
1167 execute immediate l_dynamic_query
1168 using p_item_type
1169 ,p_item_key;
1170
1171 else
1172
1173 l_request_item_code := WF_ENGINE.GetItemAttrText(
1174 p_item_type, p_item_key, G_REQUEST_ITEM_CODE);
1175
1176 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name,
1177 'Invalid Approval Request Item: '||l_request_item_code);
1178 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1179
1180 end if;
1181
1182 -- Success in Setting Submitted State
1183 x_result_out := WF_ENGINE.eng_completed;
1184 return;
1185
1186 end if;
1187
1188 EXCEPTION
1189
1190 when FND_API.G_EXC_ERROR or FND_API.G_EXC_UNEXPECTED_ERROR then
1191 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
1192 raise;
1193
1194 when others then
1195 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1196 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
1197 raise;
1198
1199 END SetSubmittedState;
1200
1201
1202 --
1203 -- PROCEDURE
1204 -- SetApprovedState
1205 --
1206 -- DESCRIPTION
1210 -- p_item_type - The workflow item type (FEMAPPR)
1207 -- Sets the approval items to the approved state.
1208 --
1209 -- IN
1211 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
1212 -- p_act_id - The function activity
1213 -- p_func_mode - Run/Cancel/Timeout
1214 --
1215 -- OUT
1216 -- x_result_out - Indicates completion of workflow activity and it's result
1217 --
1218 -- USED BY ACTIVITIES
1219 -- FEMAPPR FEM_SET_APPROVED_STATE
1220 --
1221 --------------------------------------------------------------------------------
1222 PROCEDURE SetApprovedState (
1223 p_item_type in varchar2,
1224 p_item_key in varchar2,
1225 p_act_id in number,
1226 p_func_mode in varchar2,
1227 x_result_out out nocopy varchar2
1228 )
1229 --------------------------------------------------------------------------------
1230 IS
1231
1232 l_api_name constant varchar2(30) := 'SetApprovedState';
1233
1234 l_request_item_code t_code%TYPE;
1235 l_item_plsql_pkg_name t_plsql_pkg_name%TYPE;
1236 l_dynamic_query long;
1237
1238 BEGIN
1239
1240 if (p_func_mode = 'RUN') then
1241
1242 -- Initialize API message list
1243 FND_MSG_PUB.Initialize;
1244
1245 l_item_plsql_pkg_name := WF_ENGINE.GetItemAttrText(
1246 p_item_type, p_item_key, G_ITEM_PLSQL_PKG_NAME);
1247
1248 if (l_item_plsql_pkg_name is not null) then
1249
1250 begin
1251
1252 l_dynamic_query :=
1253 ' begin '||
1254 l_item_plsql_pkg_name||'.SetApprovedState('||
1255 ' p_item_type => :b_item_type'||
1256 ' ,p_item_key => :b_item_key'||
1257 ' );'||
1258 ' end;';
1259
1260 execute immediate l_dynamic_query
1261 using p_item_type
1262 ,p_item_key;
1263
1264 exception
1265 when others then
1266 x_result_out := WF_ENGINE.eng_completed || ':' || G_FAILURE;
1267 return;
1268 end;
1269
1270 else
1271
1272 l_request_item_code := WF_ENGINE.GetItemAttrText(
1273 p_item_type, p_item_key, G_REQUEST_ITEM_CODE);
1274
1275 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name,
1276 'Invalid Approval Request Item: '||l_request_item_code);
1277 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1278
1279 end if;
1280
1281 -- Success in Setting Approved State
1282 x_result_out := WF_ENGINE.eng_completed || ':' || G_SUCCESS;
1283 return;
1284
1285 end if;
1286
1287 EXCEPTION
1288
1289 when FND_API.G_EXC_ERROR or FND_API.G_EXC_UNEXPECTED_ERROR then
1290 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
1291 raise;
1292
1293 when others then
1294 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1295 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
1296 raise;
1297
1298 END SetApprovedState;
1299
1300
1301 --
1302 -- PROCEDURE
1303 -- SetRejectedState
1304 --
1305 -- DESCRIPTION
1306 -- Sets the approval items to the rejected state.
1307 --
1308 -- IN
1309 -- p_item_type - The workflow item type (FEMAPPR)
1310 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
1311 -- p_act_id - The function activity
1312 -- p_func_mode - Run/Cancel/Timeout
1313 --
1314 -- OUT
1315 -- x_result_out - Indicates completion of workflow activity and it's result
1316 --
1317 -- USED BY ACTIVITIES
1318 -- FEMAPPR FEM_SET_REJECTED_STATE
1319 --
1320 --------------------------------------------------------------------------------
1321 PROCEDURE SetRejectedState (
1322 p_item_type in varchar2,
1323 p_item_key in varchar2,
1324 p_act_id in number,
1325 p_func_mode in varchar2,
1326 x_result_out out nocopy varchar2
1327 )
1328 --------------------------------------------------------------------------------
1329 IS
1330
1331 l_api_name constant varchar2(30) := 'SetRejectedState';
1332
1333 l_request_item_code t_code%TYPE;
1334 l_item_plsql_pkg_name t_plsql_pkg_name%TYPE;
1335 l_dynamic_query long;
1336
1337 BEGIN
1338
1339 if (p_func_mode = 'RUN') then
1340
1341 -- Initialize API message list
1342 FND_MSG_PUB.Initialize;
1343
1344 l_item_plsql_pkg_name := WF_ENGINE.GetItemAttrText(
1345 p_item_type, p_item_key, G_ITEM_PLSQL_PKG_NAME);
1346
1347 if (l_item_plsql_pkg_name is not null) then
1348
1349 l_dynamic_query :=
1350 ' begin '||
1351 l_item_plsql_pkg_name||'.SetRejectedState('||
1352 ' p_item_type => :b_item_type'||
1353 ' ,p_item_key => :b_item_key'||
1354 ' );'||
1355 ' end;';
1356
1357 execute immediate l_dynamic_query
1358 using p_item_type
1359 ,p_item_key;
1360
1361 else
1362
1363 l_request_item_code := WF_ENGINE.GetItemAttrText(
1364 p_item_type, p_item_key, G_REQUEST_ITEM_CODE);
1365
1366 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name,
1367 'Invalid Approval Request Item: '||l_request_item_code);
1368 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1369
1370 end if;
1371
1372 -- Success in Setting Rejected State
1373 x_result_out := WF_ENGINE.eng_completed;
1374 return;
1375
1376 end if;
1377
1378 EXCEPTION
1382 raise;
1379
1380 when FND_API.G_EXC_ERROR or FND_API.G_EXC_UNEXPECTED_ERROR then
1381 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
1383
1384 when others then
1385 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1386 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
1387 raise;
1388
1389 END SetRejectedState;
1390
1391
1392 --
1393 -- PROCEDURE
1394 -- ApprovedStateFailure
1395 --
1396 -- DESCRIPTION
1397 -- Handles the case when SetApprovedState fails.
1398 --
1399 -- IN
1400 -- p_item_type - The workflow item type (FEMAPPR)
1401 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
1402 -- p_act_id - The function activity
1403 -- p_func_mode - Run/Cancel/Timeout
1404 --
1405 -- OUT
1406 -- x_result_out - Indicates completion of workflow activity and it's result
1407 --
1408 -- USED BY ACTIVITIES
1409 -- FEMAPPR FEM_APPROVED_STATE_FAILURE
1410 --
1411 --------------------------------------------------------------------------------
1412 PROCEDURE ApprovedStateFailure (
1413 p_item_type in varchar2,
1414 p_item_key in varchar2,
1415 p_act_id in number,
1416 p_func_mode in varchar2,
1417 x_result_out out nocopy varchar2
1418 )
1419 --------------------------------------------------------------------------------
1420 IS
1421
1422 l_api_name constant varchar2(30) := 'ApprovedStateFailure';
1423
1424 l_justification t_justification%TYPE;
1425
1426 BEGIN
1427
1428 if (p_func_mode = 'RUN') then
1429
1430 -- Initialize API message list
1431 FND_MSG_PUB.Initialize;
1432
1433 l_justification :=
1434 FND_MESSAGE.get_string('FEM', 'FEM_WF_APPROVED_STATE_ERR');
1435
1436 WF_ENGINE.SetItemAttrText(
1437 p_item_type, p_item_key, G_JUSTIFICATION, l_justification);
1438
1439 x_result_out := WF_ENGINE.eng_completed;
1440 return;
1441
1442 end if;
1443
1444 EXCEPTION
1445
1446 when FND_API.G_EXC_ERROR or FND_API.G_EXC_UNEXPECTED_ERROR then
1447 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
1448 raise;
1449
1450 when others then
1451 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1452 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
1453 raise;
1454
1455 END ApprovedStateFailure;
1456
1457
1458 --
1459 -- PROCEDURE
1460 -- FinalizeApprovalRequest
1461 --
1462 -- DESCRIPTION
1463 -- Finalizes the approval request.
1464 --
1465 -- IN
1466 -- p_item_type - The workflow item type (FEMAPPR)
1467 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
1468 -- p_act_id - The function activity
1469 -- p_func_mode - Run/Cancel/Timeout
1470 --
1471 -- OUT
1472 -- x_result_out - Indicates completion of workflow activity and it's result
1473 --
1474 -- USED BY ACTIVITIES
1475 -- FEMAPPR FEM_FINALIZE_APPROVAL_REQ
1476 --
1477 --------------------------------------------------------------------------------
1478 PROCEDURE FinalizeApprovalRequest (
1479 p_item_type in varchar2,
1480 p_item_key in varchar2,
1481 p_act_id in number,
1482 p_func_mode in varchar2,
1483 x_result_out out nocopy varchar2
1484 )
1485 --------------------------------------------------------------------------------
1486 IS
1487
1488 l_api_name constant varchar2(30) := 'FinalizeApprovalRequest';
1489
1490 BEGIN
1491
1492 if (p_func_mode = 'RUN') then
1493
1494 -- Initialize API message list
1495 FND_MSG_PUB.Initialize;
1496
1497 PurgeApprovalRequest (
1498 p_item_type => p_item_type
1499 ,p_item_key => p_item_key
1500 );
1501
1502 -- Finished Initializing Approval Request
1503 x_result_out := WF_ENGINE.eng_completed;
1504
1505 return;
1506
1507 end if;
1508
1509 EXCEPTION
1510
1511 when FND_API.G_EXC_ERROR or FND_API.G_EXC_UNEXPECTED_ERROR then
1512 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
1513 raise;
1514
1515 when others then
1516 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1517 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
1518 raise;
1519
1520 END FinalizeApprovalRequest;
1521
1522
1523 --
1524 -- PROCEDURE
1525 -- CallbackFunction
1526 --
1527 -- DESCRIPTION
1528 -- Callback function.
1529 --
1530 -- IN
1531 -- p_item_type - The workflow item type (FEMAPPR)
1532 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
1533 -- p_act_id - The function activity
1534 -- p_func_mode - Run/Cancel/Timeout
1535 --
1536 -- OUT
1537 -- x_result_out - Indicates completion of workflow activity and it's result
1538 --
1539 -- USED BY ACTIVITIES
1540 -- FEMAPPR
1541 --
1542 --------------------------------------------------------------------------------
1543 PROCEDURE CallbackFunction (
1544 p_item_type in varchar2,
1545 p_item_key in varchar2,
1546 p_act_id in number,
1547 p_command in varchar2,
1548 x_result_out out nocopy varchar2
1549 )
1550 --------------------------------------------------------------------------------
1551 IS
1552
1553 l_api_name constant varchar2(30) := 'CallbackFunction';
1554
1558 l_application_id t_application_id%TYPE;
1555 l_org_id t_org_id%TYPE;
1556 l_user_id t_user_id%TYPE;
1557 l_responsibility_id t_responsibility_id%TYPE;
1559
1560 l_session_org_id t_org_id%TYPE;
1561
1562 BEGIN
1563
1564 if (p_command = 'RUN') then
1565
1566 x_result_out := WF_ENGINE.eng_completed || ':' || G_FEM_APPROVAL_PROCESS;
1567 return;
1568
1569 elsif (p_command = 'TEST_CTX') THEN
1570
1571 -- Code that compares current session context
1572 -- with the work item context required to execute
1573 -- the workflow safely
1574 FND_PROFILE.Get(name => G_ORG_ID, val => l_session_org_id);
1575
1576 l_org_id := WF_ENGINE.GetItemAttrNumber(
1577 p_item_type, p_item_key, G_ORG_ID);
1578
1579 if (l_session_org_id = l_org_id) then
1580 x_result_out := WF_ENGINE.eng_completed || ':' || G_TRUE;
1581 return;
1582 else
1583 -- If the background engine is executing the
1584 -- Selector/Callback function, the workflow engine
1585 -- will immediately run the Selector/Callback
1586 -- function in SET_CTX mode
1587 x_result_out := WF_ENGINE.eng_completed || ':' || G_FALSE;
1588 return;
1589 end if;
1590
1591 elsif (p_command = 'SET_CTX') then
1592
1593 -- Initialize API message list
1594 FND_MSG_PUB.Initialize;
1595
1596 l_org_id := WF_ENGINE.GetItemAttrNumber(
1597 p_item_type, p_item_key, G_ORG_ID);
1598
1599 l_user_id := WF_ENGINE.GetItemAttrNumber(
1600 p_item_type, p_item_key, G_USER_ID);
1601
1602 l_responsibility_id := WF_ENGINE.GetItemAttrNumber(
1603 p_item_type, p_item_key, G_RESPONSIBILITY_ID);
1604
1605 l_application_id := WF_ENGINE.GetItemAttrNumber(
1606 p_item_type, p_item_key, G_APPLICATION_ID);
1607
1608 -- Set the database session context which also sets the org
1609 FND_GLOBAL.Apps_Initialize(l_user_id, l_responsibility_id, l_application_id);
1610
1611 -- Finished the Callback Function
1612 x_result_out := WF_ENGINE.eng_completed;
1613 return;
1614
1615 else
1616
1617 x_result_out := WF_ENGINE.eng_completed;
1618 return;
1619
1620 end if;
1621
1622 EXCEPTION
1623
1624 when FND_API.G_EXC_ERROR or FND_API.G_EXC_UNEXPECTED_ERROR then
1625 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
1626 raise;
1627
1628 when others then
1629 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1630 BuildErrorMsg(l_api_name, p_item_type, p_item_key, p_act_id);
1631 raise;
1632
1633 END CallbackFunction;
1634
1635
1636
1637 --------------------------------------------------------------------------------
1638 -- PRIVATE PROCEDURES BODIES
1639 --------------------------------------------------------------------------------
1640
1641 --
1642 -- PROCEDURE
1643 -- UpdateApproval
1644 --
1645 -- DESCRIPTION
1646 -- Update the approval request with the responses from the approver.
1647 --
1648 -- IN
1649 -- p_item_type - The workflow item type (FEMAPPR)
1650 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
1651 -- p_approval_status - AME Approval Status
1652 --
1653 --------------------------------------------------------------------------------
1654 PROCEDURE UpdateApproval (
1655 p_item_type in varchar2,
1656 p_item_key in varchar2,
1657 p_approval_status in varchar2
1658 )
1659 --------------------------------------------------------------------------------
1660 IS
1661
1662 l_api_name constant varchar2(30) := 'UpdateApproval';
1663
1664 l_application_id t_application_id%TYPE;
1665 l_request_id t_request_id%TYPE;
1666
1667 l_approver_id t_id%TYPE;
1668 l_approver_orig_system t_orig_system%TYPE;
1669
1670 l_approver_rec AME_UTIL.approverRecord;
1671
1672 BEGIN
1673
1674 l_approver_rec.approval_status := p_approval_status;
1675 l_approver_rec.authority := WF_ENGINE.GetItemAttrText(
1676 p_item_type, p_item_key, G_AME_AUTHORITY);
1677 l_approver_rec.api_insertion := WF_ENGINE.GetItemAttrText(
1678 p_item_type, p_item_key, G_AME_API_INSERTION);
1679 l_approver_rec.approval_type_id := WF_ENGINE.GetItemAttrNumber(
1680 p_item_type, p_item_key, G_AME_APPROVAL_TYPE_ID);
1681 l_approver_rec.group_or_chain_id := WF_ENGINE.GetItemAttrNumber(
1682 p_item_type, p_item_key, G_AME_GROUP_OR_CHAIN_ID);
1683 l_approver_rec.occurrence := WF_ENGINE.GetItemAttrNumber(
1684 p_item_type, p_item_key, G_AME_OCCURRENCE);
1685
1686 l_application_id := WF_ENGINE.GetItemAttrNumber(
1687 p_item_type, p_item_key, G_APPLICATION_ID);
1688
1689 l_request_id := WF_ENGINE.GetItemAttrNumber(
1690 p_item_type, p_item_key, G_REQUEST_ID);
1691
1692 l_approver_id := WF_ENGINE.GetItemAttrNumber(
1693 p_item_type, p_item_key, G_APPROVER_ID);
1694
1695 l_approver_orig_system := WF_ENGINE.GetItemAttrText(
1696 p_item_type, p_item_key, G_APPROVER_ORIG_SYSTEM);
1697
1698 if (l_approver_orig_system = G_PER) then
1699
1700 l_approver_rec.person_id := l_approver_id;
1701
1702 AME_API.updateApprovalStatus(
1703 -- Bug Fix 3922758: WORKFLOW SUBMIT NEEDS TO CHANGE APP ID LOGIC
1704 applicationIdIn => G_FEM_APPLICATION_ID --l_application_id
1705 ,transactionIdIn => l_request_id
1706 ,approverIn => l_approver_rec
1707 ,transactionTypeIn => G_FEMAPPR
1708 );
1709
1710 elsif (l_approver_orig_system = G_FND_USR) then
1711
1715 -- Bug Fix 3922758: WORKFLOW SUBMIT NEEDS TO CHANGE APP ID LOGIC
1712 l_approver_rec.user_id := l_approver_id;
1713
1714 AME_API.updateApprovalStatus(
1716 applicationIdIn => G_FEM_APPLICATION_ID --l_application_id
1717 ,transactionIdIn => l_request_id
1718 ,approverIn => l_approver_rec
1719 ,transactionTypeIn => G_FEMAPPR
1720 );
1721
1722 else
1723
1724 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name,
1725 'Invalid Approver Original System: '||l_approver_orig_system);
1726 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1727
1728 end if;
1729
1730 END UpdateApproval;
1731
1732
1733 --
1734 -- PROCEDURE
1735 -- PurgeApprovalRequest
1736 --
1737 -- DESCRIPTION
1738 -- Deletes all the appropriate approval request records from the
1739 -- FEM_WF_REQUESTS and FEM_WF_REQ_OBJECT_DEFS tables.
1740 --
1741 -- IN
1742 -- p_request_id - Workflow Request ID.
1743 --
1744 --------------------------------------------------------------------------------
1745 PROCEDURE PurgeApprovalRequest(
1746 p_item_type in varchar2
1747 ,p_item_key in varchar2
1748 )
1749 --------------------------------------------------------------------------------
1750 IS
1751
1752 l_api_name constant varchar2(30) := 'PurgeApprovalRequest';
1753
1754 l_request_item_code t_code%TYPE;
1755 l_item_plsql_pkg_name t_plsql_pkg_name%TYPE;
1756 l_request_id t_request_id%TYPE;
1757 l_dynamic_query long;
1758
1759 BEGIN
1760
1761 l_item_plsql_pkg_name := WF_ENGINE.GetItemAttrText(
1762 p_item_type, p_item_key, G_ITEM_PLSQL_PKG_NAME);
1763
1764 if (l_item_plsql_pkg_name is not null) then
1765
1766 l_dynamic_query :=
1767 ' begin '||
1768 l_item_plsql_pkg_name||'.PurgeApprovalRequest('||
1769 ' p_item_type => :b_item_type'||
1770 ' ,p_item_key => :b_item_key'||
1771 ' );'||
1772 ' end;';
1773
1774 execute immediate l_dynamic_query
1775 using p_item_type
1776 ,p_item_key;
1777
1778 else
1779
1780 l_request_item_code := WF_ENGINE.GetItemAttrText(
1781 p_item_type, p_item_key, G_REQUEST_ITEM_CODE);
1782
1783 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name,
1784 'Invalid Approval Request Item: '||l_request_item_code);
1785 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1786
1787 end if;
1788
1789 l_request_id := WF_ENGINE.GetItemAttrNumber(
1790 p_item_type, p_item_key, G_REQUEST_ID);
1791
1792 delete from fem_wf_requests
1793 where wf_request_id = l_request_id;
1794
1795 END PurgeApprovalRequest;
1796
1797
1798 --
1799 -- PROCEDURE
1800 -- InitApprovalRequestInternal
1801 --
1802 -- DESCRIPTION
1803 -- Internal implementation for initializing an FEM Approvals Workflow.
1804 --
1805 -- IN
1806 -- p_item_type - The workflow item type (FEMAPPR)
1807 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
1808 --
1809 --------------------------------------------------------------------------------
1810 PROCEDURE InitApprovalRequestInternal (
1811 p_item_type in varchar2,
1812 p_item_key in varchar2
1813 )
1814 --------------------------------------------------------------------------------
1815 IS
1816
1817 l_api_name constant varchar2(30) := 'InitApprovalRequestInternal';
1818
1819 l_event_name t_event_name%TYPE;
1820 l_request_item_code t_code%TYPE;
1821 l_request_item t_meaning%TYPE;
1822 l_request_type_code t_code%TYPE;
1823 l_request_type t_meaning%TYPE;
1824 l_item_plsql_pkg_name t_plsql_pkg_name%TYPE;
1825
1826 BEGIN
1827
1828 -- Set all the Approval Type information on the Item Attributes
1829 l_event_name := WF_ENGINE.GetItemAttrText(
1830 p_item_type, p_item_key, G_EVENT_NAME);
1831
1832 if l_event_name in (
1833 G_BUSINESS_RULE_EVENT_APPROVAL
1834 ,G_BUSINESS_RULE_EVENT_DELETE
1835 ) then
1836
1837 select wf_request_item_code, wf_request_type_code
1838 into l_request_item_code, l_request_type_code
1839 from fem_wf_requests
1840 where wf_request_id = p_item_key;
1841
1842 if (l_request_item_code is not null) then
1843
1844 WF_ENGINE.SetItemAttrText(
1845 p_item_type, p_item_key, G_REQUEST_ITEM_CODE, l_request_item_code);
1846
1847 select item_plsql_pkg_name
1848 into l_item_plsql_pkg_name
1849 from fem_wf_req_items
1850 where wf_request_item_code = l_request_item_code;
1851
1852 WF_ENGINE.SetItemAttrText(
1853 p_item_type, p_item_key, G_ITEM_PLSQL_PKG_NAME, l_item_plsql_pkg_name);
1854
1855 select meaning
1856 into l_request_item
1857 from fem_lookups
1858 where lookup_type = 'FEM_WF_REQUEST_ITEM_DSC'
1859 and lookup_code = l_request_item_code;
1860
1861 WF_ENGINE.SetItemAttrText(
1862 p_item_type, p_item_key, G_REQUEST_ITEM, l_request_item);
1863
1864 else
1865
1866 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name,
1867 'Approval Request ID cannot be NULL');
1868 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1869
1870 end if;
1871
1872 if (l_request_type_code is not null) then
1873
1874 WF_ENGINE.SetItemAttrText(
1875 p_item_type, p_item_key, G_REQUEST_TYPE_CODE, l_request_type_code);
1876
1877 select meaning
1878 into l_request_type
1882
1879 from fem_lookups
1880 where lookup_type = 'FEM_WF_REQUEST_TYPE_DSC'
1881 and lookup_code = l_request_type_code;
1883 WF_ENGINE.SetItemAttrText(
1884 p_item_type, p_item_key, G_REQUEST_TYPE, l_request_type);
1885
1886 else
1887
1888 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name,
1889 'Approval Request Type cannot be NULL');
1890 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1891
1892 end if;
1893
1894 else
1895
1896 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name,
1897 'Invalid Event Name: '||l_event_name);
1898 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1899
1900 end if;
1901
1902 END InitApprovalRequestInternal;
1903
1904
1905 --
1906 -- FUNCTION
1907 -- GetUserId
1908 --
1909 -- DESCRIPTION
1910 -- Gets the FND user id.
1911 --
1912 -- IN
1913 -- p_user_name - FND User Name
1914 --
1915 -- RETURNS
1916 -- user_id - FND User Id
1917 --
1918 --------------------------------------------------------------------------------
1919 FUNCTION GetUserId (
1920 p_user_name in varchar2
1921 )
1922 RETURN t_id%TYPE
1923 --------------------------------------------------------------------------------
1924 IS
1925
1926 l_user_id fnd_user.user_id%TYPE := -1;
1927
1928 BEGIN
1929
1930 select user_id
1931 into l_user_id
1932 from fnd_user
1933 where user_name = p_user_name;
1934
1935 return l_user_id;
1936
1937 END GetUserId;
1938
1939
1940 --
1941 -- PROCEDURE
1942 -- BuildErrorMsg
1943 --
1944 -- DESCRIPTION
1945 -- Builds the Workflow Error Message by checking if there are any errors
1946 -- in the FND_MSG_PUB message stack.
1947 --
1948 -- IN
1949 -- p_api_name - The PL/SQL Procedure or Function name.
1950 -- p_item_type - The workflow item type (FEMAPPR)
1951 -- p_item_key - The workflow request id (FEM_WF_REQUEST_ID_SEQ)
1952 -- p_act_id - The function activity
1953 --
1954 --------------------------------------------------------------------------------
1955 PROCEDURE BuildErrorMsg (
1956 p_api_name in varchar2
1957 ,p_item_type in varchar2
1958 ,p_item_key in varchar2
1959 ,p_act_id in number
1960 )
1961 --------------------------------------------------------------------------------
1962 IS
1963
1964 l_msg_count t_msg_count%TYPE;
1965 l_msg_data t_msg_data%TYPE;
1966
1967 BEGIN
1968
1969 FND_MSG_PUB.Count_And_Get(
1970 p_count => l_msg_count
1971 ,p_data => l_msg_data
1972 );
1973
1974 if (l_msg_count > 1) then
1975
1976 l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_FIRST);
1977
1978 end if;
1979
1980 WF_CORE.Context(
1981 G_PKG_NAME
1982 ,p_api_name
1983 ,p_item_type
1984 ,p_item_key
1985 ,to_char(p_act_id)
1986 ,l_msg_data
1987 );
1988
1989 END BuildErrorMsg;
1990
1991
1992
1993 END FEM_FEMAPPR_ITEM_TYPE;