1 package body ben_pump_get as
2 /* $Header: bedpget.pkb 120.3 2007/11/28 13:42:53 sallumwa noship $ */
3 /*
4 NOTES
5 Please refer to the package header for documentation on these
6 functions.
7 */
8 /*---------------------------------------------------------------------------*/
9 /*----------------------- constant definitions ------------------------------*/
10 /*---------------------------------------------------------------------------*/
11 END_OF_TIME constant date := to_date('4712/12/31', 'YYYY/MM/DD');
12 START_OF_TIME constant date := to_date('0001/01/01', 'YYYY/MM/DD');
13 HR_API_G_VARCHAR2 constant varchar2(128) := hr_api.g_varchar2;
14 HR_API_G_NUMBER constant number := hr_api.g_number;
15 HR_API_G_DATE constant date := hr_api.g_date;
16 --
17 /* returns an acty_base_rt_id */
18 function get_acty_base_rt_id1
19 ( p_data_pump_always_call in varchar2,
20 p_business_group_id in number,
21 p_acty_base_rate_name1 in varchar2 default null,
22 p_acty_base_rate_num1 in number default null,
23 p_effective_date in date
24 ) return number is
25 l_acty_base_rt_id number;
26 begin
27 IF p_acty_base_rate_num1 IS NOT NULL and p_acty_base_rate_num1 <> HR_API_G_NUMBER THEN
28 l_acty_base_rt_id := p_acty_base_rate_num1 ;
29 ELSIF p_acty_base_rate_name1 IS NOT NULL THEN
30 select abr.acty_base_rt_id
31 into l_acty_base_rt_id
32 from ben_acty_base_rt_f abr
33 where abr.name = p_acty_base_rate_name1
34 and abr.business_group_id + 0 = p_business_group_id
35 and p_effective_date between
36 abr.effective_start_date and abr.effective_end_date;
37 END IF;
38 return(l_acty_base_rt_id);
39 exception
40 when others then
41 hr_data_pump.fail('get_acty_base_rt_id1', sqlerrm, p_business_group_id, p_acty_base_rate_name1, p_effective_date);
42 raise;
43 end get_acty_base_rt_id1;
44 --
45 /* returns an acty_base_rt_id2 */
46 function get_acty_base_rt_id2
47 ( p_data_pump_always_call in varchar2,
48 p_business_group_id in number,
49 p_acty_base_rate_name2 in varchar2 default null,
50 p_acty_base_rate_num2 in number default null,
51 p_effective_date in date
52 ) return number is
53 l_acty_base_rt_id number;
54 begin
55 IF p_acty_base_rate_num2 IS NOT NULL and p_acty_base_rate_num2 <> HR_API_G_NUMBER THEN
56 l_acty_base_rt_id := p_acty_base_rate_num2 ;
57 ELSIF p_acty_base_rate_name2 IS NOT NULL THEN
58 select abr.acty_base_rt_id
59 into l_acty_base_rt_id
60 from ben_acty_base_rt_f abr
61 where abr.name = p_acty_base_rate_name2
62 and abr.business_group_id + 0 = p_business_group_id
63 and p_effective_date between
64 abr.effective_start_date and abr.effective_end_date;
65 END IF;
66 return(l_acty_base_rt_id);
67 exception
68 when others then
69 hr_data_pump.fail('get_acty_base_rt_id2', sqlerrm, p_business_group_id, p_acty_base_rate_name2, p_effective_date);
70 raise;
71 end get_acty_base_rt_id2;
72 --
73 /* returns an acty_base_rt_id */
74 function get_acty_base_rt_id3
75 ( p_data_pump_always_call in varchar2,
76 p_business_group_id in number,
77 p_acty_base_rate_name3 in varchar2 default null,
78 p_acty_base_rate_num3 in number default null,
79 p_effective_date in date
80 ) return number is
81 l_acty_base_rt_id number;
82 begin
83 IF p_acty_base_rate_num3 IS NOT NULL and p_acty_base_rate_num3 <> HR_API_G_NUMBER THEN
84 l_acty_base_rt_id := p_acty_base_rate_num3 ;
85 ELSIF p_acty_base_rate_name3 IS NOT NULL THEN
86 select abr.acty_base_rt_id
87 into l_acty_base_rt_id
88 from ben_acty_base_rt_f abr
89 where abr.name = p_acty_base_rate_name3
90 and abr.business_group_id + 0 = p_business_group_id
91 and p_effective_date between
92 abr.effective_start_date and abr.effective_end_date;
93 END IF;
94 return(l_acty_base_rt_id);
95 exception
96 when others then
97 hr_data_pump.fail('get_acty_base_rt_id3', sqlerrm, p_business_group_id, p_acty_base_rate_name3, p_effective_date);
98 raise;
99 end get_acty_base_rt_id3;
100 --
101 /* returns an acty_base_rt_id4 */
102 function get_acty_base_rt_id4
103 ( p_data_pump_always_call in varchar2,
104 p_business_group_id in number,
105 p_acty_base_rate_name4 in varchar2 default null,
106 p_acty_base_rate_num4 in number default null,
107 p_effective_date in date
108 ) return number is
109 l_acty_base_rt_id number;
110 begin
111 IF p_acty_base_rate_num4 IS NOT NULL and p_acty_base_rate_num4 <> HR_API_G_NUMBER THEN
112 l_acty_base_rt_id := p_acty_base_rate_num4 ;
113 ELSIF p_acty_base_rate_name4 IS NOT NULL THEN
114 select abr.acty_base_rt_id
115 into l_acty_base_rt_id
116 from ben_acty_base_rt_f abr
117 where abr.name = p_acty_base_rate_name4
118 and abr.business_group_id + 0 = p_business_group_id
119 and p_effective_date between
120 abr.effective_start_date and abr.effective_end_date;
121 END IF;
122 return(l_acty_base_rt_id);
123 exception
124 when others then
125 hr_data_pump.fail('get_acty_base_rt_id4', sqlerrm, p_business_group_id, p_acty_base_rate_name4, p_effective_date);
126 raise;
127 end get_acty_base_rt_id4;
128 --
129 ------------------------------ get_pgm_id ---------------------------
130 /*
131 NAME
132 get_pgm_id
133 DESCRIPTION
134 Returns a Program ID.
135 NOTES
136 This function returns a pgm_id and is designed for use with the Data Pump.
137 */
138 function get_pgm_id
139 ( p_data_pump_always_call in varchar2,
140 p_business_group_id in number,
141 p_program in varchar2 default null,
142 p_program_num in number default null,
143 p_effective_date in date
144 ) return number is
145 l_pgm_id number;
146 begin
147 IF p_program_num IS NOT NULL and p_program_num <> HR_API_G_NUMBER THEN
148 l_pgm_id := p_program_num ;
149 ELSIF p_program IS NOT NULL and p_program <> HR_API_G_VARCHAR2 THEN--Bug : 6652591
150 select pgm.pgm_id
151 into l_pgm_id
152 from ben_pgm_f pgm
153 where pgm.name = p_program
154 and pgm.business_group_id + 0 = p_business_group_id
155 and p_effective_date between
156 pgm.effective_start_date and pgm.effective_end_date;
157 END IF;
158 return(l_pgm_id);
159 exception
160 when others then
161 hr_data_pump.fail('get_pgm_id', sqlerrm, p_business_group_id, p_program,p_program_num, p_effective_date);
162 raise;
163 end get_pgm_id;
164 --
165
166 /* returns a pl_id */
167 function get_pl_id
168 ( p_data_pump_always_call in varchar2,
169 p_business_group_id in number,
170 p_plan in varchar2 default null,
171 p_plan_num in number default null,
172 p_effective_date in date
173 ) return number is
174 l_pl_id number;
175 begin
176 IF p_plan_num IS NOT NULL and p_plan_num <> HR_API_G_NUMBER THEN
177 l_pl_id := p_plan_num ;
178 ELSIF p_plan IS NOT NULL THEN
179 select pln.pl_id
180 into l_pl_id
181 from ben_pl_f pln
182 where pln.name = p_plan
183 and pln.business_group_id + 0 = p_business_group_id
184 and p_effective_date between
185 pln.effective_start_date and pln.effective_end_date;
186 END IF;
187 return(l_pl_id);
188 exception
189 when others then
190 hr_data_pump.fail('get_pl_id', sqlerrm, p_business_group_id, p_plan, p_effective_date);
191 raise;
192 end get_pl_id;
193 --
194 /* returns a ended_pl_id */
195 function get_ended_pl_id
196 ( p_data_pump_always_call in varchar2,
197 p_business_group_id in number,
198 p_ended_plan in varchar2 default null,
199 p_ended_plan_num in number default null,
200 p_effective_date in date
201 ) return number is
202 l_pl_id number;
203 begin
204 IF p_ended_plan_num IS NOT NULL and p_ended_plan_num <> HR_API_G_NUMBER THEN
205 l_pl_id := p_ended_plan_num ;
206 ELSIF p_ended_plan IS NOT NULL THEN
207 select pln.pl_id
208 into l_pl_id
209 from ben_pl_f pln
210 where pln.name = p_ended_plan
211 and pln.business_group_id + 0 = p_business_group_id
212 and p_effective_date between
213 pln.effective_start_date and pln.effective_end_date;
214 END IF;
215 return(l_pl_id);
216 exception
217 when others then
218 hr_data_pump.fail('get_ended_pl_id', sqlerrm, p_business_group_id, p_ended_plan, p_effective_date);
219 raise;
220 end get_ended_pl_id;
221 --
222 /* returns an opt_id */
223 function get_opt_id
224 ( p_data_pump_always_call in varchar2,
225 p_business_group_id in number,
226 p_option in varchar2 default null,
227 p_option_num in number default null,
228 p_effective_date in date
229 ) return number is
230 l_opt_id number;
231 begin
232 IF p_option_num IS NOT NULL and p_option_num <> HR_API_G_NUMBER THEN
233 l_opt_id := p_option_num ;
234 ELSIF p_option IS NOT NULL and p_option <> HR_API_G_VARCHAR2 THEN --BUG 6148609
235 select opt.opt_id
236 into l_opt_id
237 from ben_opt_f opt
238 where opt.name = p_option
239 and opt.business_group_id + 0 = p_business_group_id
240 and p_effective_date between
241 opt.effective_start_date and opt.effective_end_date;
242 END IF;
243 return(l_opt_id);
244 exception
245 when others then
246 hr_data_pump.fail('get_opt_id', sqlerrm, p_business_group_id, p_option, p_effective_date);
247 raise;
248 end get_opt_id;
249 --
250 /* returns an ended_opt_id */
251 function get_ended_opt_id
252 ( p_data_pump_always_call in varchar2,
253 p_business_group_id in number,
254 p_ended_option in varchar2 default null,
255 p_ended_option_num in number default null,
256 p_effective_date in date
257 ) return number is
258 l_opt_id number;
259 begin
260 --
261 IF p_ended_option_num IS NOT NULL and p_ended_option_num <> HR_API_G_NUMBER THEN
262 l_opt_id := p_ended_option_num ;
263 ELSIF p_ended_option IS NOT NULL THEN
264 select opt.opt_id
265 into l_opt_id
266 from ben_opt_f opt
267 where opt.name = p_ended_option
268 and opt.business_group_id + 0 = p_business_group_id
269 and p_effective_date between
270 opt.effective_start_date and opt.effective_end_date;
271 END IF;
272 --
273 return(l_opt_id);
274 exception
275 when others then
276 hr_data_pump.fail('get_ended_opt_id', sqlerrm, p_business_group_id, p_ended_option, p_effective_date);
277 raise;
278 end get_ended_opt_id;
279 --
280 ------------------------------ get_pen_person_id ---------------------------
281 /*
282 NAME
283 get_pen_person_id
284 DESCRIPTION
285 Returns an person_id
286 NOTES
287 This function returns a eperson_id and is designed for use with the Data Pump.
288 */
289 --
290 function get_pen_person_id
291 ( p_data_pump_always_call in varchar2,
292 p_business_group_id in number,
293 p_employee_number in varchar2 default null,
294 p_national_identifier in varchar2 default null,
295 p_full_name in varchar2 default null,
296 p_date_of_birth in date default null,
297 p_person_num in number default null,
298 p_effective_date in date
299 ) return number is
300 --
301 l_person_id NUMBER;
302 value_error exception ;
303 begin
304 --
305 IF p_person_num IS NOT NULL and p_person_num <> HR_API_G_NUMBER THEN
306 --
307 l_person_id := p_person_num ;
308 --
309 ELSIF p_employee_number IS NOT NULL THEN
310 --
311 select person_id
312 into l_person_id
313 from per_all_people_f
314 where business_group_id = p_business_group_id
315 and p_effective_date between effective_start_date
316 and effective_end_date
317 and employee_number = p_employee_number ;
318 --
319 ELSIF p_national_identifier IS NOT NULL THEN
320 --
321 select person_id
322 into l_person_id
323 from per_all_people_f
324 where business_group_id = p_business_group_id
325 and p_effective_date between effective_start_date
326 and effective_end_date
327 and national_identifier = p_national_identifier ;
328 --
329 ELSIF p_full_name IS NOT NULL AND p_date_of_birth IS NOT NULL THEN
330 --
331 select person_id
332 into l_person_id
333 from per_all_people_f
334 where business_group_id = p_business_group_id
335 and p_effective_date between effective_start_date
336 and effective_end_date
337 and full_name = p_full_name
338 and date_of_birth = p_date_of_birth ;
339 --
340 ELSE
341 --
342 raise value_error;
343 --
344 END IF;
345 --
346 --
347 return l_person_id ;
348 --
349 exception when others then
350 hr_data_pump.fail('get_pen_person_id', sqlerrm,
351 p_business_group_id, p_employee_number, p_national_identifier,
352 p_full_name,p_date_of_birth, p_effective_date);
353 raise;
354 end get_pen_person_id ;
355 --
356
357 function get_con_person_id
358 ( p_data_pump_always_call in varchar2,
359 p_business_group_id in number,
360 p_con_employee_number in varchar2 default null,
361 p_con_national_identifier in varchar2 default null,
362 p_con_full_name in varchar2 default null,
363 p_con_date_of_birth in date default null,
364 p_con_person_num in number default null,
365 p_effective_date in date
366 ) return number is
367 --
368 l_person_id NUMBER;
369 value_error exception ;
370 begin
371 --
372 IF p_con_person_num IS NOT NULL and p_con_person_num <> HR_API_G_NUMBER THEN
373 --
374 l_person_id := p_con_person_num ;
375 --
376 ELSIF p_con_employee_number IS NOT NULL THEN
377 --
378 select person_id
379 into l_person_id
380 from per_all_people_f
381 where business_group_id = p_business_group_id
382 and p_effective_date between effective_start_date
383 and effective_end_date
384 and employee_number = p_con_employee_number ;
385 --
386 ELSIF p_con_national_identifier IS NOT NULL THEN
387 --
388 select person_id
389 into l_person_id
390 from per_all_people_f
391 where business_group_id = p_business_group_id
392 and p_effective_date between effective_start_date
393 and effective_end_date
394 and national_identifier = p_con_national_identifier ;
395 --
396 ELSIF p_con_full_name IS NOT NULL AND p_con_date_of_birth IS NOT NULL THEN
397 --
398 select person_id
399 into l_person_id
400 from per_all_people_f
401 where business_group_id = p_business_group_id
402 and p_effective_date between effective_start_date
403 and effective_end_date
404 and full_name = p_con_full_name
405 and date_of_birth = p_con_date_of_birth ;
406 --
407 /*
408 ELSE
409 --
410 raise value_error;
411 --
412 */
413 END IF;
414 --
415 --
416 return l_person_id ;
417 --
418 exception when others then
419 hr_data_pump.fail('get_con_person_id', sqlerrm,
420 p_business_group_id, p_con_employee_number, p_con_national_identifier,
421 p_con_full_name,p_con_date_of_birth, p_effective_date);
422 raise;
423 end get_con_person_id ;
424 --
425 begin
426 -- Initialise the debugging information structure.
427 null;
428
429 end ben_pump_get;
[Home] [Help]