1 package BODY PER_TMPROFILE_PKG as
2 /* $Header: pertppkg.pkb 120.3 2012/01/10 13:24:38 sgnanama ship $ */
3 function is_subordinate (p_subordinate_person_id in number
4 ,p_person_id in number
5 ,p_effective_date in date
6 ) return varchar2 as
7 l_supervisor_id number;
8 l_current_person_id number;
9 begin
10 l_current_person_id := p_subordinate_person_id;
11 loop
12 begin
13 select supervisor_id
14 into l_supervisor_id
15 from per_all_assignments_f
16 where per_all_assignments_f.person_id = l_current_person_id
17 and per_all_assignments_f.primary_flag = 'Y'
18 and per_all_assignments_f.assignment_type in ('E','C')
19 and per_all_assignments_f.assignment_status_type_id not in
20 (select assignment_status_type_id
21 from per_assignment_status_types
22 where per_system_status = 'TERM_ASSIGN')
23 and trunc(p_effective_date)
24 between per_all_assignments_f.effective_start_date
25 and per_all_assignments_f.effective_end_date;
26 exception
27 when no_data_found then
28 return 'N';
29 end;
30
31 if l_supervisor_id is null then
32 return 'N';
33 end if;
34 if l_supervisor_id = p_person_id then
35 return 'Y';
36 end if;
37 l_current_person_id := l_supervisor_id;
38 end loop;
39 end is_subordinate;
40
41 function get_address (p_person_id in number
42 ,p_effective_date in date
43 ) return varchar2 as
44 l_address_line1 per_addresses.address_line1%type;
45 l_address_line2 per_addresses.address_line2%type;
46 l_address_line3 per_addresses.address_line3%type;
47 l_region_1 per_addresses.region_1%type;
48 l_region_2 per_addresses.region_2%type;
49 l_region_3 per_addresses.region_3%type;
50 l_town_or_city per_addresses.town_or_city%type;
51 l_postal_code per_addresses.postal_code%type;
52 l_country per_addresses.country%type;
53
54 l_address varchar2(1000) := null;
55 begin
56 select address_line1,
57 address_line2,
58 address_line3,
59 region_1,
60 region_2,
61 region_3,
62 town_or_city,
63 postal_code,
64 country
65 into l_address_line1,
66 l_address_line2,
67 l_address_line3,
68 l_region_1,
69 l_region_2,
70 l_region_3,
71 l_town_or_city,
72 l_postal_code,
73 l_country
74 from per_addresses
75 where person_id = p_person_id
76 and primary_flag = 'Y'
77 and p_effective_date
78 between date_from
79 and nvl(date_to,p_effective_date);
80
81 if l_address_line1 is not null then
82 l_address := l_address_line1;
83 end if;
84
85 if l_address_line2 is not null then
86 if l_address is null then
87 l_address := l_address_line2;
88 else
89 l_address := l_address || ',' || l_address_line2;
90 end if;
91 end if;
92
93 if l_address_line3 is not null then
94 if l_address is null then
95 l_address := l_address_line3;
96 else
97 l_address := l_address || ',' || l_address_line3;
98 end if;
99 end if;
100
101 if l_region_1 is not null then
102 if l_address is null then
103 l_address := l_region_1;
104 else
105 l_address := l_address || ',' || l_region_1;
106 end if;
107 end if;
108
109 if l_region_2 is not null then
110 if l_address is null then
111 l_address := l_region_2;
112 else
113 l_address := l_address || ',' || l_region_2;
114 end if;
115 end if;
116
117 if l_region_3 is not null then
118 if l_address is null then
119 l_address := l_region_3;
120 else
121 l_address := l_address || ',' || l_region_3;
122 end if;
123 end if;
124
125 if l_town_or_city is not null then
126 if l_address is null then
127 l_address := l_town_or_city;
128 else
129 l_address := l_address || ',' || l_town_or_city;
130 end if;
131 end if;
132
133 if l_postal_code is not null then
134 if l_address is null then
135 l_address := l_postal_code;
136 else
137 l_address := l_address || ',' || l_postal_code;
138 end if;
139 end if;
140
141 if l_country is not null then
142 if l_address is null then
143 l_address := l_country;
144 else
145 l_address := l_address || ',' || l_country;
146 end if;
147 end if;
148
149 return l_address;
150 exception
151 when no_data_found then
152 return '-';
153 when too_many_rows then
154 return '<Too Many Rows>';
155 end get_address;
156
157 function encode64 (p_blob in blob) return clob as
158 l_result clob;
159 begin
160 if p_blob is not null then
161 dbms_lob.createtemporary
162 (lob_loc => l_result
163 ,cache => false
164 ,dur => 0);
165 wf_mail_util.encodeblob (p_blob,l_result);
166 end if ;
167 return l_result;
168 end encode64;
169
170 FUNCTION get_value_for_9box(p_person_id IN NUMBER,
171 p_effective_date IN DATE,
172 p_type IN VARCHAR2) RETURN NUMBER IS
173 l_value NUMBER(15);
174 l_performance NUMBER(15);
175 l_potential NUMBER(15);
176 l_retention NUMBER(15);
177 BEGIN
178 l_performance := hr_wpm_util.get_performance_for_9box(p_person_id,p_effective_date);
179 l_potential := hr_wpm_util.get_potential_for_9box(p_person_id,p_effective_date);
180 l_retention := hr_wpm_util.get_retention_for_9box(p_person_id,p_effective_date);
181
182 IF l_performance > 0 THEN
183 IF (p_type = 'POT' and l_potential >0) THEN
184 l_value := ((l_potential-1)*3 + l_performance);
185 ELSIF (p_type = 'RET' and l_retention >0) THEN
186 l_value := ((l_retention-1)*3 + l_performance);
187 ELSE
188 l_value := 0;
189 END IF;
190 ELSE
191 l_value := 0;
192
193
194 END IF;
195
196 return l_value;
197 END get_value_for_9box;
198
199
200 FUNCTION get_sshr_actions_menu_name(p_person_id IN NUMBER
201 ,p_effective_date IN DATE ) RETURN VARCHAR2 IS
202
203 cursor c_person_type is
204 select ppt.SYSTEM_PERSON_TYPE person_type
205 from per_all_people_f papf,
206 per_person_types ppt
207 where papf.person_id = p_person_id
208 and sysdate between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE
209 and papf.PERSON_TYPE_ID = ppt.PERSON_TYPE_ID
210 and papf.BUSINESS_GROUP_ID = ppt.BUSINESS_GROUP_ID;
211
212 cursor c_person_id is
213 select hr_util_misc_ss.get_person_id from dual;
214
215 l_return varchar2(100) := null;
216 l_person_id number;
217 BEGIN
218 if(p_person_id is null) then
219 return null;
220 end if;
221 open c_person_id;
222 fetch c_person_id into l_person_id;
223 close c_person_id;
224 for l_person_type in c_person_type loop
225 --for employee
226 if(l_person_type.person_type is not null) then
227 if(l_person_type.person_type = 'EMP' OR l_person_type.person_type = 'EMP_APL') then
228 if(l_person_id is not null and l_person_id = p_person_id) then
229 l_return := fnd_profile.value('HR_PERSONAL_ACTIONS_MENU');
230 else
231 l_return := fnd_profile.value('HR_MANAGER_ACTIONS_MENU');
232 end if;
233 end if;
234 end if;
235 --for contingent
236 if(l_person_type.person_type is not null) then
237 if(l_person_type.person_type = 'CWK' OR l_person_type.person_type = 'CWK_APL' OR l_person_type.person_type = 'OTHER') then
238 if(l_person_id is not null and l_person_id = p_person_id) then
239 l_return := fnd_profile.value('HR_CWK_ACTIONS_MENU');
240 else
241 l_return := fnd_profile.value('HR_CWK_MANAGER_ACTIONS_MENU');
242 end if;
243 end if;
244 end if;
245 end loop;
246 return l_return;
247 END get_sshr_actions_menu_name;
248
249 end per_tmprofile_pkg;
[Home] [Help]