PACKAGE BODY: APPS.HXC_APPROVAL_HELPER

Source

1 PACKAGE body hxc_approval_helper AS
2 /* $Header: hxcaprhlp.pkb 120.1 2006/03/06 19:16:07 arundell noship $ */
3 
4   type user_info is record
5       (person_id number,
6        last_name varchar2(150),
7        full_name varchar2(240)
8        );
9 
10   p_user_info user_info;
11 
12   c_role_prefix constant varchar2(4) := 'OTL-';
13 
14   Function formAdHocRoleName
15      (p_user_info in user_info)
16      Return varchar2 is
17   Begin
18 
19      return c_role_prefix||p_user_info.person_id||'-'||upper(p_user_info.last_name);
20 
21   End formAdHocRoleName;
22 
23   Function getPersonInfo
24      (p_person_id in per_all_people_f.person_id%type)
25      Return user_info is
26 
27      cursor c_person_info(p_person_id in per_all_people_f.person_id%type) is
28        select p1.last_name,
29               p1.full_name
30          from per_all_people_f p1
31         where p1.person_id = p_person_id
32           and p1.effective_end_date =
33               (select max(p2.effective_end_date)
34                  from per_all_people_f p2
35                 where p2.person_id = p1.person_id);
36 
37      l_user_info user_info;
38 
39   Begin
40      l_user_info.person_id := p_person_id;
41      open c_person_info(p_person_id);
42      fetch c_person_info into l_user_info.last_name, l_user_info.full_name;
43      if(c_person_info%notfound) then
44         close c_person_info;
45         fnd_message.set_name('PER','PER_289467_REQ_INV_PERSON_ID');
46         fnd_message.raise_error;
47      else
48         close c_person_info;
49      end if;
50 
51      return l_user_info;
52   End getPersonInfo;
53 
54 /* Public Functions and Procedures */
55 
56   Function createAdHocUser
57      (p_resource_id in hxc_time_building_blocks.resource_id%type,
58       p_effective_date in hxc_time_building_blocks.start_time%type)
59      Return varchar2 is
60 
61      cursor c_dup_check
62         (p_name in wf_users.name%type) is
63        select 'Y'
64          from wf_users
65         where name = p_name;
66 
67      l_dummy varchar2(1);
68 
69      l_name wf_local_roles.name%type;
70      l_user_info user_info;
71 
72   Begin
73 
74      l_user_info := getPersonInfo(p_resource_id);
75      l_name := formAdHocRoleName(l_user_info);
76 
77      open c_dup_check(l_name);
78      fetch c_dup_check into l_dummy;
79      if(c_dup_check%notfound) then
80         wf_directory.createAdHocUser
81            (name => l_name,
82             display_name => l_user_info.full_name,
83             description => 'OTL Approval: AdHocRole Generated'
84             );
85      end if;
86      close c_dup_check;
87 
88      return l_name;
89 
90   End createAdHocUser;
91 
92 END hxc_approval_helper;