1 PACKAGE BODY pay_element_data_pump AS
2 /* $Header: pyeledpm.pkb 120.0 2005/05/29 04:31:38 appldev noship $ */
3
4 ------------------------------ user_key_to_id ---------------------------------
5 --
6 -- Returns an ID value from hr_pump_batch_line_user_keys alone.
7 -- Utility function to get _ID functions.
8 --
9 Function user_key_to_id(p_user_key_value in varchar2)
10 return number is
11 l_id number;
12 Begin
13 select unique_key_id
14 into l_id
15 from hr_pump_batch_line_user_keys
16 where user_key_value = p_user_key_value;
17 return(l_id);
18 End user_key_to_id;
19 --
20 -------------------------- get_balancing_keyflex_id ---------------------------
21 --
22 -- Returns a balancing_keyflex_id and requires a user_key
23 --
24 Function get_balancing_keyflex_id
25 (p_balancing_keyflex_user_key in varchar2
26 )
27 return number is
28 --
29 l_balancing_keyflex_id number;
30 --
31 Begin
32 --
33 l_balancing_keyflex_id :=
34 user_key_to_id( p_balancing_keyflex_user_key );
35 return(l_balancing_keyflex_id);
36 --
37 Exception
38 --
39 when others then
40 hr_data_pump.fail('get_balancing_keyflex_id', sqlerrm,
41 p_balancing_keyflex_user_key);
42 raise;
43 --
44 End get_balancing_keyflex_id;
45 --
46 ---------------------------- get_element_set_id -------------------------------
47 --
48 -- Returns an element_set_id
49 --
50 Function get_element_set_id
51 (p_business_group_id number
52 ,p_element_set_name varchar2
53 )
54 return number is
55 --
56 l_element_set_id number;
57 --
58 Begin
59 --
60 select els.element_set_id
61 into l_element_set_id
62 from pay_element_sets els
63 where upper(els.element_set_name) = upper(p_element_set_name)
64 and nvl(els.business_group_id,nvl(p_business_group_id,0)) =
65 nvl(p_business_group_id,0);
66 --
67 Exception
68 --
69 when others then
70 hr_data_pump.fail('get_element_set_id', sqlerrm,
71 p_business_group_id, p_element_set_name);
72 raise;
73 --
74 End get_element_set_id;
75 --
76 -------------------------- get_element_link_ovn -------------------------------
77 --
78 -- Returns the object version number (ovn) of the element link
79 --
80 Function get_element_link_ovn
81 (p_element_link_user_key in varchar2
82 ,p_effective_date in date
83 )
84 return number is
85 --
86 l_element_link_ovn number;
87 --
88 Begin
89 select pel.object_version_number
90 into l_element_link_ovn
91 from pay_element_links_f pel,
92 hr_pump_batch_line_user_keys key
93 where key.user_key_value = p_element_link_user_key
94 and pel.element_link_id = key.unique_key_id
95 and p_effective_date between
96 pel.effective_start_date and pel.effective_end_date;
97
98 return(l_element_link_ovn);
99 exception
100 when others then
101 hr_data_pump.fail('get_element_link_ovn', sqlerrm, p_element_link_user_key,
102 p_effective_date);
103 raise;
104 end get_element_link_ovn;
105 --
106 ------------------------ get_link_input_value_id ------------------------------
107 --
108 -- Returns the link input value id of the element link and input value
109 --
110 Function get_link_input_value_id
111 (p_element_link_user_key in varchar2
112 ,p_input_value_name in varchar
113 ,p_element_name in varchar2
114 ,p_business_group_id in number
115 ,p_language_code in varchar2
116 ,p_effective_date in date
117 )
118 return number is
119 --
120 l_input_value_id number;
121 l_link_input_value_id number;
122 --
123 Begin
124 --
125 l_input_value_id := hr_pump_get.get_input_value_id
126 (p_input_value_name
127 ,p_element_name
128 ,p_business_group_id
129 ,p_effective_date
130 ,p_language_code
131 );
132 --
133 select liv.link_input_value_id
134 into l_link_input_value_id
135 from pay_link_input_values_f liv,
136 hr_pump_batch_line_user_keys key
137 where key.user_key_value = p_element_link_user_key
138 and liv.element_link_id = key.unique_key_id
139 and liv.input_value_id = l_input_value_id
140 and p_effective_date between liv.effective_start_date
141 and liv.effective_end_date;
142 return(l_link_input_value_id);
143 exception
144 when others then
145 hr_data_pump.fail('get_link_input_value_id'
146 ,sqlerrm
147 ,p_element_link_user_key
148 ,p_input_value_name
149 ,p_element_name
150 ,p_business_group_id
151 ,p_language_code
152 ,p_effective_date
153 );
154 raise;
155 End get_link_input_value_id;
156 --
157 ------------------------- get_link_input_value_ovn ----------------------------
158 --
159 -- Returns the object version number (ovn) of the element link input value
160 --
161 Function get_link_input_value_ovn
162 (p_element_link_user_key in varchar2
163 ,p_input_value_name in varchar
164 ,p_element_name in varchar2
165 ,p_business_group_id in number
166 ,p_language_code in varchar2
167 ,p_effective_date in date
168 )
169 return number is
170 --
171 l_link_input_value_id number;
172 l_link_input_value_ovn number;
173 --
174 Begin
175 --
176 l_link_input_value_id := get_link_input_value_id
177 (p_element_link_user_key
178 ,p_input_value_name
179 ,p_element_name
180 ,p_business_group_id
181 ,p_language_code
182 ,p_effective_date
183 );
184 --
185 select liv.object_version_number
186 into l_link_input_value_ovn
187 from pay_link_input_values_f liv
188 where liv.link_input_value_id = l_link_input_value_id
189 and p_effective_date between liv.effective_start_date
190 and liv.effective_end_date;
191 return(l_link_input_value_ovn);
192 exception
193 when others then
194 hr_data_pump.fail('get_link_input_value_ovn'
195 ,sqlerrm
196 ,p_element_link_user_key
197 ,p_input_value_name
198 ,p_element_name
199 ,p_business_group_id
200 ,p_language_code
201 ,p_effective_date
202 );
203 raise;
204 end get_link_input_value_ovn;
205 -----------------------------get_classification_id-----------------------------
206
207 Function get_classification_id( p_classification_name varchar2,
208 p_business_group_id number
209 ) RETURN NUMBER IS
210
211 l_classification_id number;
212 Begin
213
214 Select pay.classification_id
215 into l_classification_id
216 From pay_element_classifications pay,
217 pay_element_classifications_tl paytl
218 Where pay.classification_id = paytl.classification_id
219 and paytl.language = userenv('LANG')
220 and pay.parent_classification_id is null
221 and nvl(pay.legislation_code,
222 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~'))=
223 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~')
224 and nvl(pay.business_group_id,nvl(p_business_group_id,-1)) =
225 nvl(p_business_group_id,-1)
226 and paytl.classification_name = p_classification_name;
227
228 RETURN(l_classification_id);
229
230 Exception
231 When OTHERS Then
232 hr_data_pump.fail('get_classification_id', sqlerrm,
233 p_classification_name,p_business_group_id);
234
235 RAISE;
236 End get_classification_id;
237
238 ------------------------------get_formula_id------------------------------------
239
240 Function get_formula_id( p_formula_Name in varchar2,
241 p_business_group_id in number,
242 p_effective_date in date
243 ) RETURN NUMBER IS
244 l_formula_id number ;
245
246 Begin
247
248 Select formula_id
249 into l_formula_id
250 From ff_formulas_f f1,
251 ff_formula_types f2
252 Where f1.formula_type_id = f2.formula_type_id
253 and f2.formula_type_name = 'Element Skip'
254 and p_effective_date between f1.effective_start_date and
255 f1.effective_end_date
256 and nvl(legislation_code,
257 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~')) =
258 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~')
259 and nvl(business_group_id,nvl(p_business_group_id,-1)) =
260 nvl(p_business_group_id,-1)
261 and upper(formula_name) = upper(p_formula_Name);
262
263 RETURN(l_formula_id);
264
265 Exception
266 When OTHERS Then
267 hr_data_pump.fail('get_formula_id', sqlerrm, p_formula_Name,
268 p_business_group_id,
269 p_effective_date);
270 RAISE;
271 End get_formula_id;
272
273 ---------------------------get_benefit_classification_id-----------------------
274
275 Function get_benefit_classification_id
276 ( p_benefit_classification_name in varchar2,
277 p_business_group_id in number
278 ) RETURN NUMBER IS
279
280 l_benefit_classification_id number;
281 Begin
282 Select benefit_classification_id
283 INTO l_benefit_classification_id
284 From ben_benefit_classifications
285 Where nvl(legislation_code,
286 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~'))=
287 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~')
288 and nvl(business_group_id,nvl(p_business_group_id,-1)) =
289 nvl(p_business_group_id,-1)
290 and benefit_classification_name = p_benefit_classification_name ;
291
292 RETURN (l_benefit_classification_id);
293
294 Exception
295 When OTHERS Then
296 hr_data_pump.fail('get_benefit_classification_id', sqlerrm,
297 p_benefit_classification_name,
298 p_business_group_id);
299 RAISE;
300 End get_benefit_classification_id;
301
302 ---------------------------get_iterative_formula_id----------------------------
303
304 Function get_iterative_formula_id (p_iterative_formula_name in varchar2,
305 p_business_group_id in number ,
306 p_effective_date in date
307 ) RETURN NUMBER IS
308
309 l_formula_id number;
310 Begin
311 Select formula_id
312 INTO l_formula_id
313 From ff_formulas_f f1, ff_formula_types f2
314 Where f1.formula_type_id = f2.formula_type_id
315 and f2.formula_type_name = 'Net to Gross'
316 and p_effective_date between f1.effective_start_date and
317 f1.effective_end_date
318 and nvl(legislation_code,
319 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~')) =
320 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~')
321 and nvl(business_group_id,nvl(p_business_group_id,-1)) =
322 nvl(p_business_group_id,-1)
323 and upper(formula_name) = upper(p_iterative_formula_name) ;
324
325 RETURN (l_formula_id);
326
327 Exception
328 When OTHERS Then
329 hr_data_pump.fail('get_iterative_formula_id', sqlerrm,
330 p_iterative_formula_name,
331 p_business_group_id,
332 p_effective_date);
333 RAISE;
334 End get_iterative_formula_id;
335
336 ------------------------------get_retro_summ_ele_id----------------------------
337
338 Function get_retro_summ_ele_id ( p_retro_summ_element_name in varchar2,
339 p_business_group_id in number ,
340 p_effective_date in date
341 ) RETURN NUMBER IS
342
343 l_element_type_id number;
344
345 Begin
346 Select et.element_type_id
347 INTO l_element_type_id
348 From pay_element_classifications ec, pay_element_types_f et
349 Where ec.classification_id = et.classification_id
350 and p_effective_date between et.effective_start_date and
351 et.effective_end_date
352 and nvl(et.legislation_code,
353 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~'))=
354 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~')
355 and nvl(et.business_group_id, nvl(p_business_group_id,-1)) =
356 nvl(p_business_group_id,-1)
357 and et.element_name = p_retro_summ_element_name ;
358
359 RETURN(l_element_type_id);
360
361 Exception
362 When OTHERS Then
363 hr_data_pump.fail('get_retro_summ_ele_id', sqlerrm,
364 p_retro_summ_element_name,
365 p_business_group_id,
366 p_effective_date);
367 RAISE;
368 End get_retro_summ_ele_id;
369
370 --------------------------------get_proration_group_id-------------------------
371
372 Function get_proration_group_id ( p_event_group_name in varchar2,
373 p_business_group_id in number
374 ) RETURN NUMBER IS
375
376 l_event_group_id number;
377
378 Begin
379 Select event_group_id
380 INTO l_event_group_id
381 From pay_event_groups peg
382 Where peg.event_group_type = 'P'
383 and (peg.legislation_code =
384 HR_API.RETURN_LEGISLATION_CODE(p_business_group_id)
385 or ( peg.legislation_code is null and
386 peg.business_group_id = p_business_group_id)
387 or ( peg.legislation_code is null and
388 peg.business_group_id is null))
389 and event_group_name = p_event_group_name;
390
391 RETURN(l_event_group_id);
392
393 Exception
394 When OTHERS Then
395 hr_data_pump.fail('get_proration_group_id', sqlerrm,
396 p_event_group_name,
397 p_business_group_id);
398 RAISE;
399 End get_proration_group_id;
400
401 ----------------------------get_proration_formula_id---------------------------
402
403 Function get_proration_formula_id( p_proration_formula_Name in varchar2,
404 p_business_group_id in number,
405 p_effective_date in date
406 ) RETURN NUMBER IS
407 l_formula_id number ;
408
409 Begin
410
411 Select formula_id
412 INTO l_formula_id
413 From ff_formulas_f f1, ff_formula_types f2
414 Where f1.formula_type_id = f2.formula_type_id
415 and f2.formula_type_name = 'Payroll Run Proration'
416 and p_effective_date between f1.effective_start_date and
417 f1.effective_end_date
418 and nvl(legislation_code,
419 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~'))=
420 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~')
421 and nvl(business_group_id,nvl(p_business_group_id,-1)) =
422 nvl(p_business_group_id,-1)
423 and upper(formula_name) = upper(p_proration_formula_Name) ;
424
425 RETURN(l_formula_id);
426
427 Exception
428 When OTHERS Then
429 hr_data_pump.fail('get_proration_formula_id', sqlerrm,
430 p_proration_formula_Name, p_business_group_id,
431 p_effective_date);
432 RAISE;
433 End get_proration_formula_id;
434
435 -----------------------------get_recalc_event_group_id------------------------
436
437 Function get_recalc_event_group_id( p_recalc_event_group_name in varchar2,
438 p_business_group_id in number
439 ) RETURN NUMBER IS
440 l_event_group_id number ;
441
442 Begin
443
444 Select event_group_id
445 INTO l_event_group_id
446 From pay_event_groups
447 Where event_group_type = 'R'
448 and (legislation_code = HR_API.RETURN_LEGISLATION_CODE(p_business_group_id)
449 or ( legislation_code is null and
450 business_group_id = p_business_group_id)
451 or ( legislation_code is null and
452 business_group_id is null))
453 and event_group_name = p_recalc_event_group_name;
454
455
456 RETURN(l_event_group_id);
457
458 Exception
459 When OTHERS Then
460 hr_data_pump.fail('get_recalc_event_group_id', sqlerrm,
461 p_recalc_event_group_name, p_business_group_id);
462
463 RAISE;
464 End get_recalc_event_group_id;
465
466 ----------------------------get_element_type_ovn-------------------------------
467
468 Function get_element_type_ovn ( p_element_type_user_key in varchar2,
469 p_effective_date in date
470 ) return number is
471 --
472 l_element_type_ovn number;
473 --
474 Begin
475 Select pet.object_version_number
476 into l_element_type_ovn
477 From pay_element_types_f pet,
478 hr_pump_batch_line_user_keys key
479 Where key.user_key_value = p_element_type_user_key
480 and pet.element_type_id = key.unique_key_id
481 and p_effective_date between
482 pet.effective_start_date and pet.effective_end_date;
483
484 return(l_element_type_ovn);
485
486 Exception
487 When others Then
488 hr_data_pump.fail('get_element_type_ovn', sqlerrm,
489 p_element_type_user_key,
490 p_effective_date);
491 raise;
492 end get_element_type_ovn;
493
494 -----------------------------get_input_value_ovn-------------------------------
495 Function get_input_value_ovn ( p_element_type_user_key in varchar2,
496 p_existing_input_name in varchar2,
497 p_business_group_id in number,
498 p_effective_date in date,
499 p_language_code in varchar2
500 ) return number is
501 --
502 l_input_value_ovn number;
503 l_input_value_id number;
504 --
505 Begin
506
507 l_input_value_id := get_input_value_id
508 (p_element_type_user_key,
509 p_existing_input_name ,
510 p_business_group_id ,
511 p_effective_date ,
512 p_language_code
513 ) ;
514
515 Select piv.object_version_number
516 into l_input_value_ovn
517 From pay_input_values_f piv
518 Where piv.input_value_id = l_input_value_id
519 and p_effective_date between
520 piv.effective_start_date and piv.effective_end_date;
521
522 return(l_input_value_ovn);
523
524 Exception
525 When others Then
526 hr_data_pump.fail('get_input_value_ovn', sqlerrm,
527 p_element_type_user_key,p_existing_input_name,
528 p_business_group_id,
529 p_effective_date,p_language_code);
530 raise;
531
532 end get_input_value_ovn;
533 ----------------------------get_element_type_id--------------------------------
534
535 Function get_element_type_id (p_element_type_user_key in varchar2
536 )
537 return number is
538
539 l_element_type_user_key number;
540 Begin
541
542 l_element_type_user_key := user_key_to_id(p_element_type_user_key);
543
544 return(l_element_type_user_key);
545 --
546 Exception
547 When OTHERS Then
548 hr_data_pump.fail('get_element_type_id', sqlerrm,
549 p_element_type_user_key);
550 RAISE;
551 End get_element_type_id;
552
553 ----------------------------get_input_value_formula_id------------------------
554
555 Function get_input_value_formula_id ( p_input_formula_name in varchar2,
556 p_business_group_id in number,
557 p_effective_date in date
558 ) RETURN NUMBER IS
559
560 l_formula_id number;
561
562 Begin
563 Select formula_id
564 INTO l_formula_id
565 From ff_formulas_f ff,
566 ff_formula_types ft
567 Where nvl(ff.legislation_code,
568 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~'))=
569 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~')
570 and nvl(ff.business_group_id, nvl(p_business_group_id,-1)) =
571 nvl(p_business_group_id,-1)
572 and p_effective_date between ff.effective_start_date and
573 ff.effective_end_date
574 and ff.formula_type_id = ft.formula_type_id
575 and upper (ft.formula_type_name) = 'ELEMENT INPUT VALIDATION'
576 and upper(formula_name) = upper(p_input_formula_name) ;
577
578 RETURN(l_formula_id);
579
580 Exception
581 When OTHERS Then
582 hr_data_pump.fail('get_input_value_formula_id', sqlerrm,
583 p_input_formula_name,
584 p_business_group_id,p_effective_date);
585 RAISE;
586 End get_input_value_formula_id;
587
588 -----------------------------get_input_value_id-----------------------------
589
590 Function get_input_value_id (p_element_type_user_key in varchar2,
591 p_existing_input_name in varchar2,
592 p_business_group_id in number,
593 p_effective_date in date,
594 p_language_code in varchar2
595 )
596 return number is
597
598 l_input_value_id number;
599 l_element_name pay_element_types_f_tl.ELEMENT_NAME%type;
600
601 Begin
602 select pettl.element_name
603 into l_element_name
604 from pay_element_types_f pet,
605 pay_element_types_f_tl pettl,
606 hr_pump_batch_line_user_keys
607 where pettl.language = p_language_code
608 and pet.element_type_id = pettl.element_type_id
609 and p_effective_date between
610 pet.effective_start_date and pet.effective_end_date
611 and pet.element_type_id = unique_key_id
612 and user_key_value = p_element_type_user_key ;
613
614 l_input_value_id := hr_pump_get.get_input_value_id
615 (p_existing_input_name,
616 l_element_name ,
617 p_business_group_id ,
618 p_effective_date ,
619 p_language_code );
620
621 return(l_input_value_id);
622
623 Exception
624 When OTHERS Then
625 hr_data_pump.fail('get_input_value_id', sqlerrm,
626 p_element_type_user_key,p_existing_input_name,
627 p_business_group_id , p_effective_date,
628 p_language_code
629 );
630 RAISE;
631 End get_input_value_id;
632 --
633 END pay_element_data_pump ;
[Home] [Help]