1 Package body HR_HELPDESK_UTIL_SS As
2 /* $Header: hrhdutilss.pkb 120.1.12010000.8 2009/01/30 07:30:59 tkghosh ship $ */
3 --
4 -- Package variables
5 --
6
7 FUNCTION get_person_id
8 ( emp_id IN per_all_people_f.employee_number%type,
9 bg_id IN per_all_people_f.business_group_id%type,
10 cwk_id IN per_all_people_f.npw_number%type)
11 RETURN varchar2
12 IS
13 pers_id varchar2(10);
14 begin
15
16 if (trim(cwk_id) is not null or trim(cwk_id) <> 0) then
17
18 select distinct person_id into pers_id
19 from per_all_people_f
20 where npw_number = cwk_id and
21 business_group_id = bg_id and
22 current_npw_flag='Y' and
23 sysdate between effective_start_date and
24 effective_end_date;
25
26 else
27
28 select distinct person_id into pers_id
29 from per_all_people_f
30 where employee_number = emp_id and
31 business_group_id = bg_id and
32 current_employee_flag='Y' and
33 sysdate between effective_start_date and
34 effective_end_date;
35
36 end if;
37
38 return pers_id;
39
40 exception
41 when others then
42 return '0';
43
44 end get_person_id;
45
46 FUNCTION get_assgn_id
47 (pers_id per_all_people_f.person_id%type )
48 RETURN varchar2 is
49 --pers_id varchar2(10);
50 ass_id varchar2(10);
51 begin
52
53 --pers_id := get_person_id(emp_id,bg_id,cwk_id);
54
55 select distinct assignment_id into ass_id
56 from per_all_assignments_f
57 where person_id = pers_id
58 and assignment_type in('E','A','C')
59 and sysdate between effective_start_date and
60 effective_end_date and
61 primary_flag ='Y';
62
63 return ass_id;
64
65 exception
66 when others then
67 return '0';
68
69 end get_assgn_id;
70
71 FUNCTION get_person_status
72 ( person_id IN per_all_people_f.person_id%type)
73 RETURN varchar2 is
74 per_sts number;
75 begin
76 select count(*) into per_sts from per_people_f
77 where person_id = person_id and
78 sysdate between effective_start_date and effective_end_date;
79
80 if per_sts > 0 then
81 return 'Y';
82 else
83 return 'N';
84 end if;
85
86
87 exception
88 when others then
89 return '0';
90
91 end get_person_status;
92
93 FUNCTION get_assign_status
94 ( assig_id IN per_all_assignments_f.assignment_id%type)
95 RETURN varchar2 is
96 ass_sts number;
97 begin
98 select count(*) into ass_sts from per_assignments_f
99 where assignment_id = assig_id and
100 sysdate between effective_start_date and
101 effective_end_date and
102 primary_flag ='Y';
103
104 if ass_sts > 0 then
105 return 'Y';
106 else
107 return 'N';
108 end if;
109
110 exception
111 when others then
112 return '0';
113
114 end get_assign_status;
115
116 FUNCTION validate_function
117 RETURN varchar2 is
118 l_count number;
119 Begin
120
121 select count(function_id) into l_count from (
122 select fme.function_id,fme.menu_id from fnd_menu_entries fme
123 start with fme.menu_id IN
124 (select menu_id from fnd_responsibility where responsibility_id in(
125 select responsibility_id from fnd_user_resp_groups
126 where user_id = fnd_global.USER_ID))
127 connect by fme.menu_id = prior fme.sub_menu_id )
128 where function_id IN (select function_id from fnd_form_functions
129 where function_name = 'HR_HELPDESK_SS') ;
130
131 if l_count > 0 then
132 return 'Y';
133 else
134 return 'N';
135 end if;
136
137 exception
138 when others then
139 return '0';
140
141 End validate_function;
142
143 FUNCTION get_function_type(func_name IN VARCHAR2)
144 return varchar2 is
145
146 func_type varchar2(5);
147 Begin
148
149 SELECT type INTO func_type FROM fnd_form_functions WHERE function_name = func_name;
150 If func_type = 'FORM' then
151 return 'F';
152 Elsif func_type = 'JSP' then
153 return 'J';
154 Else
155 return 'E';
156 End if;
157
158 exception
159 When others then
160 return 'E';
161
162 End get_function_type;
163
164 FUNCTION get_resp_name(resp_id IN fnd_responsibility.RESPONSIBILITY_ID%type)
165 RETURN VARCHAR2 IS
166
167 l_resp_name fnd_responsibility.RESPONSIBILITY_KEY%type;
168 BEGIN
169
170 SELECT responsibility_key
171 INTO l_resp_name
172 FROM fnd_responsibility
173 WHERE application_id = 800
174 AND responsibility_id = resp_id;
175
176 RETURN l_resp_name;
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 RETURN '0';
181
182 END get_resp_name;
183
184
185 FUNCTION get_secgrp_key(sec_grp_id IN fnd_security_groups.SECURITY_GROUP_KEY%type)
186 RETURN VARCHAR2 IS
187
188 l_secgrp_key fnd_security_groups.SECURITY_GROUP_KEY%type;
189 BEGIN
190
191 SELECT security_group_key
192 INTO l_secgrp_key
193 FROM fnd_security_groups
194 WHERE security_group_id = sec_grp_id;
195
196 RETURN l_secgrp_key;
197
198 EXCEPTION
199 WHEN OTHERS THEN
200 RETURN '0';
201
202 END get_secgrp_key;
203
204 END;
[Home] [Help]