1 package body hr_pump_get as
2 /* $Header: hrdpget.pkb 120.2 2010/03/18 12:32:49 pvelvano ship $ */
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 /*---------------------------------------------------------------------------*/
18 /*------------- internal Get ID functions data structures -------------------*/
19 /*---------------------------------------------------------------------------*/
20
21 ------------------------------ user_key_to_id ----------------------------------
22 /*
23 NAME
24 user_key_to_id
25 DESCRIPTION
26 Returns an ID value from hr_pump_batch_line_user_keys alone.
27 NOTES
28 Utility function to get _ID functions.
29 */
30 function user_key_to_id( p_user_key_value in varchar2 )
31 return number is
32 l_id number;
33 begin
34 select unique_key_id
35 into l_id
36 from hr_pump_batch_line_user_keys
37 where user_key_value = p_user_key_value;
38 return(l_id);
39 end user_key_to_id;
40
41 /*---------------------------------------------------------------------------*/
42 /*----------------------- Get ID function globals ---------------------------*/
43 /*---------------------------------------------------------------------------*/
44
45 /*---------------------------------------------------------------------------*/
46 /*------------------ local functions and procedures -------------------------*/
47 /*---------------------------------------------------------------------------*/
48
49 /*---------------------------------------------------------------------------*/
50 /*----------------------------- get id functions ----------------------------*/
51 /*---------------------------------------------------------------------------*/
52
53 /* returns a position definition id */
54 function get_position_definition_id
55 (
56 p_position_name in varchar2,
57 p_business_group_id in number,
58 p_effective_date in date
59 ) return number is
60 l_position_definition_id number;
61 begin
62 return(l_position_definition_id);
63 exception
64 when others then
65 hr_data_pump.fail('get_position_definition_id', sqlerrm, p_position_name,
66 p_business_group_id, p_effective_date);
67 raise;
68 end get_position_definition_id;
69 /*--------------------- get_collective_agreement_id ------------------------*/
70 function get_collective_agreement_id
71 (p_business_group_id in number
72 ,p_cagr_name in varchar2
73 ,p_effective_date in date
74 ) return number is
75 l_collective_agreement_id number;
76 begin
77 select pc.collective_agreement_id
78 into l_collective_agreement_id
79 from per_collective_agreements pc
80 where pc.business_group_id = p_business_group_id
81 and pc.name = p_cagr_name
82 and p_effective_date between
83 nvl(start_date,START_OF_TIME) and nvl(end_date,END_OF_TIME);
84 return l_collective_agreement_id;
85 exception
86 when others then
87 hr_data_pump.fail('get_collective_agreement_id', sqlerrm,
88 p_business_group_id, p_cagr_name, p_effective_date);
89 raise;
90 end get_collective_agreement_id;
91
92 /*------------------------------ get_contract_id ---------------------------*/
93 function get_contract_id
94 (p_contract_user_key in varchar2
95 ) return number is
96 l_contract_id number;
97 begin
98 l_contract_id := user_key_to_id( p_contract_user_key );
99 return(l_contract_id);
100 exception
101 when others then
102 hr_data_pump.fail('get_contract_id', sqlerrm, p_contract_user_key);
103 raise;
104 end get_contract_id;
105
106 /*---------------------------- get_establishment_id ---------------------------*/
107 function get_establishment_id
108 (p_establishment_name in varchar2
109 ,p_location in varchar2
110 ) return number is
111 l_establishment_id number;
112 begin
113 select pe.establishment_id
114 into l_establishment_id
115 from per_establishments pe
116 where pe.location = p_location
117 and pe.name = p_establishment_name;
118 return l_establishment_id;
119 exception
120 when others then
121 hr_data_pump.fail('get_establishment_id', sqlerrm,
122 p_establishment_name, p_location);
123 raise;
124 end get_establishment_id;
125
126 /*------------------------- get_cagr_id_flex_num ------------------------------*/
127 function get_cagr_id_flex_num
128 (p_cagr_id_flex_num_user_key varchar2
129 ) return number is
130 l_cagr_id_flex_num number;
131 begin
132 l_cagr_id_flex_num := user_key_to_id( p_cagr_id_flex_num_user_key );
133 return(l_cagr_id_flex_num);
134 exception
135 when others then
136 hr_data_pump.fail('get_cagr_id_flex_num', sqlerrm, p_cagr_id_flex_num_user_key);
137 raise;
138 end get_cagr_id_flex_num;
139
140 /* get element_entry_id - requires user key */
141 function get_element_entry_id
142 (
143 p_element_entry_user_key in varchar2
144 ) return number is
145 l_element_entry_id number;
146 begin
147 l_element_entry_id := user_key_to_id( p_element_entry_user_key );
148 return(l_element_entry_id);
149 exception
150 when others then
151 hr_data_pump.fail('get_element_entry_id', sqlerrm, p_element_entry_user_key);
152 raise;
153 end get_element_entry_id;
154
155 /* get original element_entry_id - requires user key */
156 function get_original_entry_id
157 (
158 p_original_entry_user_key in varchar2
159 ) return number is
160 l_original_entry_id number;
161 begin
162 l_original_entry_id := get_element_entry_id( p_original_entry_user_key );
163 return(l_original_entry_id);
164 exception
165 when others then
166 hr_data_pump.fail('get_original_entry_id', sqlerrm,
167 p_original_entry_user_key);
168 raise;
169 end get_original_entry_id;
170
171 /* get target element_entry_id - requires user key */
172 function get_target_entry_id
173 (
174 p_target_entry_user_key in varchar2
175 ) return number is
176 l_target_entry_id number;
177 begin
178 l_target_entry_id := get_element_entry_id( p_target_entry_user_key );
179 return(l_target_entry_id);
180 exception
181 when others then
182 hr_data_pump.fail('get_target_entry_id', sqlerrm, p_target_entry_user_key);
183 raise;
184 end get_target_entry_id;
185
186 /* get_element_link_id - requires user key */
187 function get_element_link_id
188 (
189 p_element_link_user_key in varchar2
190 ) return number is
191 l_element_link_id number;
192 begin
193 l_element_link_id := user_key_to_id( p_element_link_user_key );
194 return(l_element_link_id);
195 exception
196 when others then
197 hr_data_pump.fail('get_element_link_id', sqlerrm, p_element_link_user_key);
198 raise;
199 end get_element_link_id;
200
201 /* get_cost_allocation_key_flex_id - requires user key */
202 function get_cost_allocation_keyflex_id
203 (
204 p_cost_alloc_keyflex_user_key in varchar2
205 ) return number is
206 l_cost_allocation_keyflex_id number;
207 begin
208 l_cost_allocation_keyflex_id :=
209 user_key_to_id( p_cost_alloc_keyflex_user_key );
210 return(l_cost_allocation_keyflex_id);
211 exception
212 when others then
213 hr_data_pump.fail('get_cost_allocation_keyflex_id', sqlerrm,
214 p_cost_alloc_keyflex_user_key);
215 raise;
216 end get_cost_allocation_keyflex_id;
217
218 /* get_comment_id - requires user key */
219 function get_comment_id( p_comment_user_key in varchar2 )
220 return number is
221 l_comment_id number;
222 begin
223 l_comment_id := user_key_to_id( p_comment_user_key );
224 return(l_comment_id);
225 exception
226 when others then
227 hr_data_pump.fail('get_comment_id', sqlerrm, p_comment_user_key);
228 raise;
229 end get_comment_id;
230
231 /* get_assignment_action_id - requires user key */
232 function get_assignment_action_id( p_assignment_action_user_key in varchar2 )
233 return number is
234 l_assignment_action_id number;
235 begin
236 l_assignment_action_id := user_key_to_id( p_assignment_action_user_key );
237 return(l_assignment_action_id);
238 exception
239 when others then
240 hr_data_pump.fail('get_assignment_action_id', sqlerrm,
241 p_assignment_action_user_key);
242 raise;
243 end get_assignment_action_id;
244
245 /* get updating assignment_action_id - requires user key */
246 function get_updating_action_id( p_updating_action_user_key in varchar2 )
247 return number is
248 l_updating_action_id number;
249 begin
250 l_updating_action_id :=
251 get_assignment_action_id( p_updating_action_user_key );
252 return(l_updating_action_id);
253 exception
254 when others then
255 hr_data_pump.fail('get_updating_action_id', sqlerrm,
256 p_updating_action_user_key);
257 raise;
258 end get_updating_action_id;
259
260 /* get_input_value_id */
261 function get_input_value_id
262 (
263 p_input_value_name in varchar2,
264 p_element_name in varchar2,
265 p_business_group_id in number,
266 p_effective_date in date,
267 p_language_code in varchar2
268 ) return number is
269 l_input_value_id number;
270 begin
271 select piv.input_value_id
272 into l_input_value_id
273 from pay_input_values_f_tl pivtl,
274 pay_input_values_f piv,
275 pay_element_types_f pet,
276 pay_element_types_f_tl pettl,
277 per_business_groups pbg
278 where pbg.business_group_id = p_business_group_id
279 and pettl.element_name = p_element_name
280 and pettl.language = p_language_code
281 and pet.element_type_id = pettl.element_type_id
282 and p_effective_date between
283 pet.effective_start_date and pet.effective_end_date
284 and
285 (
286 (pet.business_group_id is null and pet.legislation_code is null) or
287 (pet.business_group_id is null
288 and pet.legislation_code = pbg.legislation_code) or
289 (pet.legislation_code is null
290 and pet.business_group_id = p_business_group_id)
291 )
292 and piv.element_type_id = pet.element_type_id
293 and p_effective_date between
294 piv.effective_start_date and piv.effective_end_date
295 and pivtl.input_value_id = piv.input_value_id
296 and pivtl.name = p_input_value_name
297 and pivtl.LANGUAGE = p_language_code;
298 return(l_input_value_id);
299 exception
300 when others then
301 hr_data_pump.fail('get_input_value_id', sqlerrm, p_input_value_name,
302 p_element_name, p_business_group_id, p_effective_date,
303 p_language_code);
304 raise;
305 end get_input_value_id;
306
307 /* get_input_value_id1 */
308 function get_input_value_id1
309 (
310 p_input_value_name1 in varchar2,
311 p_element_name in varchar2,
312 p_business_group_id in number,
313 p_effective_date in date,
314 p_language_code in varchar2
315 ) return number is
316 l_input_value_id1 number;
317 begin
318 l_input_value_id1 :=
319 get_input_value_id( p_input_value_name1, p_element_name,
320 p_business_group_id, p_effective_date,
321 p_language_code );
322 return(l_input_value_id1);
323 exception
324 when others then
325 hr_data_pump.fail('get_input_value_id1', sqlerrm, p_input_value_name1,
326 p_element_name, p_business_group_id, p_effective_date,
327 p_language_code);
328 raise;
329 end get_input_value_id1;
330
331 /* get_input_value_id2 */
332 function get_input_value_id2
333 (
334 p_input_value_name2 in varchar2,
335 p_element_name in varchar2,
336 p_business_group_id in number,
337 p_effective_date in date,
338 p_language_code in varchar2
339 ) return number is
340 l_input_value_id2 number;
341 begin
342 l_input_value_id2 :=
343 get_input_value_id( p_input_value_name2, p_element_name,
344 p_business_group_id, p_effective_date,
345 p_language_code );
346 return(l_input_value_id2);
347 exception
348 when others then
349 hr_data_pump.fail('get_input_value_id2', sqlerrm, p_input_value_name2,
350 p_element_name, p_business_group_id, p_effective_date,
351 p_language_code);
352 raise;
353 end get_input_value_id2;
354
355 /* get_input_value_id3 */
356 function get_input_value_id3
357 (
358 p_input_value_name3 in varchar2,
359 p_element_name in varchar2,
360 p_business_group_id in number,
361 p_effective_date in date,
362 p_language_code in varchar2
363 ) return number is
364 l_input_value_id3 number;
365 begin
366 l_input_value_id3 :=
367 get_input_value_id( p_input_value_name3, p_element_name,
368 p_business_group_id, p_effective_date,
369 p_language_code );
370 return(l_input_value_id3);
371 exception
372 when others then
373 hr_data_pump.fail('get_input_value_id3', sqlerrm, p_input_value_name3,
374 p_element_name, p_business_group_id, p_effective_date,
375 p_language_code);
376 raise;
377 end get_input_value_id3;
378
379 /* get_input_value_id4 */
380 function get_input_value_id4
381 (
382 p_input_value_name4 in varchar2,
383 p_element_name in varchar2,
384 p_business_group_id in number,
385 p_effective_date in date,
386 p_language_code in varchar2
387 ) return number is
388 l_input_value_id4 number;
389 begin
390 l_input_value_id4 :=
391 get_input_value_id( p_input_value_name4, p_element_name,
392 p_business_group_id, p_effective_date,
393 p_language_code );
394 return(l_input_value_id4);
395 exception
396 when others then
397 hr_data_pump.fail('get_input_value_id4', sqlerrm, p_input_value_name4,
398 p_element_name, p_business_group_id, p_effective_date,
399 p_language_code);
400 raise;
401 end get_input_value_id4;
402
403 /* get_input_value_id5 */
404 function get_input_value_id5
405 (
406 p_input_value_name5 in varchar2,
407 p_element_name in varchar2,
408 p_business_group_id in number,
409 p_effective_date in date,
410 p_language_code in varchar2
411 ) return number is
412 l_input_value_id5 number;
413 begin
414 l_input_value_id5 :=
415 get_input_value_id( p_input_value_name5, p_element_name,
416 p_business_group_id, p_effective_date,
417 p_language_code );
418 return(l_input_value_id5);
419 exception
420 when others then
421 hr_data_pump.fail('get_input_value_id5', sqlerrm, p_input_value_name5,
422 p_element_name, p_business_group_id, p_effective_date,
423 p_language_code);
424 raise;
425 end get_input_value_id5;
426
427 /* get_input_value_id6 */
428 function get_input_value_id6
429 (
430 p_input_value_name6 in varchar2,
431 p_element_name in varchar2,
432 p_business_group_id in number,
433 p_effective_date in date,
434 p_language_code in varchar2
435 ) return number is
436 l_input_value_id6 number;
437 begin
438 l_input_value_id6 :=
439 get_input_value_id( p_input_value_name6, p_element_name,
440 p_business_group_id, p_effective_date,
441 p_language_code );
442 return(l_input_value_id6);
443 exception
444 when others then
445 hr_data_pump.fail('get_input_value_id6', sqlerrm, p_input_value_name6,
446 p_element_name, p_business_group_id, p_effective_date,
447 p_language_code);
448 raise;
449 end get_input_value_id6;
450
451 /* get_input_value_id7 */
452 function get_input_value_id7
453 (
454 p_input_value_name7 in varchar2,
455 p_element_name in varchar2,
456 p_business_group_id in number,
457 p_effective_date in date,
458 p_language_code in varchar2
459 ) return number is
460 l_input_value_id7 number;
461 begin
462 l_input_value_id7 :=
463 get_input_value_id( p_input_value_name7, p_element_name,
464 p_business_group_id, p_effective_date,
465 p_language_code );
466 return(l_input_value_id7);
467 exception
468 when others then
469 hr_data_pump.fail('get_input_value_id7', sqlerrm, p_input_value_name7,
470 p_element_name, p_business_group_id, p_effective_date,
471 p_language_code);
472 raise;
473 end get_input_value_id7;
474
475 /* get_input_value_id8 */
476 function get_input_value_id8
477 (
478 p_input_value_name8 in varchar2,
479 p_element_name in varchar2,
480 p_business_group_id in number,
481 p_effective_date in date,
482 p_language_code in varchar2
483 ) return number is
484 l_input_value_id8 number;
485 begin
486 l_input_value_id8 :=
487 get_input_value_id( p_input_value_name8, p_element_name,
488 p_business_group_id, p_effective_date,
489 p_language_code );
490 return(l_input_value_id8);
491 exception
492 when others then
493 hr_data_pump.fail('get_input_value_id8', sqlerrm, p_input_value_name8,
494 p_element_name, p_business_group_id, p_effective_date,
495 p_language_code);
496 raise;
497 end get_input_value_id8;
498
499 /* get_input_value_id9 */
500 function get_input_value_id9
501 (
502 p_input_value_name9 in varchar2,
503 p_element_name in varchar2,
504 p_business_group_id in number,
505 p_effective_date in date,
506 p_language_code in varchar2
507 ) return number is
508 l_input_value_id9 number;
509 begin
510 l_input_value_id9 :=
511 get_input_value_id( p_input_value_name9, p_element_name,
512 p_business_group_id, p_effective_date,
513 p_language_code );
514 return(l_input_value_id9);
515 exception
516 when others then
517 hr_data_pump.fail('get_input_value_id9', sqlerrm, p_input_value_name9,
518 p_element_name, p_business_group_id, p_effective_date,
519 p_language_code);
520 raise;
521 end get_input_value_id9;
522
523 /* get_input_value_id10 */
524 function get_input_value_id10
525 (
526 p_input_value_name10 in varchar2,
527 p_element_name in varchar2,
528 p_business_group_id in number,
529 p_effective_date in date,
530 p_language_code in varchar2
531 ) return number is
532 l_input_value_id10 number;
533 begin
534 l_input_value_id10 :=
535 get_input_value_id( p_input_value_name10, p_element_name,
536 p_business_group_id, p_effective_date,
537 p_language_code );
538 return(l_input_value_id10);
539 exception
540 when others then
541 hr_data_pump.fail('get_input_value_id10', sqlerrm, p_input_value_name10,
542 p_element_name, p_business_group_id, p_effective_date,
543 p_language_code);
544 raise;
545 end get_input_value_id10;
546
547 /* get_input_value_id11 */
548 function get_input_value_id11
549 (
550 p_input_value_name11 in varchar2,
551 p_element_name in varchar2,
552 p_business_group_id in number,
553 p_effective_date in date,
554 p_language_code in varchar2
555 ) return number is
556 l_input_value_id11 number;
557 begin
558 l_input_value_id11 :=
559 get_input_value_id( p_input_value_name11, p_element_name,
560 p_business_group_id, p_effective_date,
561 p_language_code );
562 return(l_input_value_id11);
563 exception
564 when others then
565 hr_data_pump.fail('get_input_value_id11', sqlerrm, p_input_value_name11,
566 p_element_name, p_business_group_id, p_effective_date,
567 p_language_code);
568 raise;
569 end get_input_value_id11;
570
571 /* get_input_value_id12 */
572 function get_input_value_id12
573 (
574 p_input_value_name12 in varchar2,
575 p_element_name in varchar2,
576 p_business_group_id in number,
577 p_effective_date in date,
578 p_language_code in varchar2
579 ) return number is
580 l_input_value_id12 number;
581 begin
582 l_input_value_id12 :=
583 get_input_value_id( p_input_value_name12, p_element_name,
584 p_business_group_id, p_effective_date,
585 p_language_code );
586 return(l_input_value_id12);
587 exception
588 when others then
589 hr_data_pump.fail('get_input_value_id12', sqlerrm, p_input_value_name12,
590 p_element_name, p_business_group_id, p_effective_date,
591 p_language_code);
592 raise;
593 end get_input_value_id12;
594
595 /* get_input_value_id13 */
596 function get_input_value_id13
597 (
598 p_input_value_name13 in varchar2,
599 p_element_name in varchar2,
600 p_business_group_id in number,
601 p_effective_date in date,
602 p_language_code in varchar2
603 ) return number is
604 l_input_value_id13 number;
605 begin
606 l_input_value_id13 :=
607 get_input_value_id( p_input_value_name13, p_element_name,
608 p_business_group_id, p_effective_date,
609 p_language_code );
610 return(l_input_value_id13);
611 exception
612 when others then
613 hr_data_pump.fail('get_input_value_id13', sqlerrm, p_input_value_name13,
614 p_element_name, p_business_group_id, p_effective_date,
615 p_language_code);
616 raise;
617 end get_input_value_id13;
618
619 /* get_input_value_id14 */
620 function get_input_value_id14
621 (
622 p_input_value_name14 in varchar2,
623 p_element_name in varchar2,
624 p_business_group_id in number,
625 p_effective_date in date,
626 p_language_code in varchar2
627 ) return number is
628 l_input_value_id14 number;
629 begin
630 l_input_value_id14 :=
631 get_input_value_id( p_input_value_name14, p_element_name,
632 p_business_group_id, p_effective_date,
633 p_language_code );
634 return(l_input_value_id14);
635 exception
636 when others then
637 hr_data_pump.fail('get_input_value_id14', sqlerrm, p_input_value_name14,
638 p_element_name, p_business_group_id, p_effective_date,
639 p_language_code);
640 raise;
641 end get_input_value_id14;
642
643 /* get_input_value_id15 */
644 function get_input_value_id15
645 (
646 p_input_value_name15 in varchar2,
647 p_element_name in varchar2,
648 p_business_group_id in number,
649 p_effective_date in date,
650 p_language_code in varchar2
651 ) return number is
652 l_input_value_id15 number;
653 begin
654 l_input_value_id15 :=
655 get_input_value_id( p_input_value_name15, p_element_name,
656 p_business_group_id, p_effective_date,
657 p_language_code );
658 return(l_input_value_id15);
659 exception
660 when others then
661 hr_data_pump.fail('get_input_value_id15', sqlerrm, p_input_value_name15,
662 p_element_name, p_business_group_id, p_effective_date,
663 p_language_code);
664 raise;
665 end get_input_value_id15;
666
667 /* get_rate_id */
668 function get_rate_id
669 ( p_rate_name in varchar2,
670 p_business_group_id in number
671 ) return number is
672 l_rate_id number;
673 begin
674 select rate_id
675 into l_rate_id
676 from pay_rates
677 where name = p_rate_name
678 and business_group_id + 0 = p_business_group_id;
679 return(l_rate_id);
680 exception
681 when others then
682 hr_data_pump.fail('get_rate_id', sqlerrm, p_rate_name, p_business_group_id);
683 raise;
684 end get_rate_id;
685
686 /* get_emp_fed_tax_rule_id - requires user key */
687 function get_emp_fed_tax_rule_id
688 (
689 p_emp_fed_tax_rule_user_key in varchar2
690 ) return number is
691 l_emp_fed_tax_rule_id number;
692 begin
693 l_emp_fed_tax_rule_id := user_key_to_id( p_emp_fed_tax_rule_user_key );
694 return(l_emp_fed_tax_rule_id);
695 exception
696 when others then
697 hr_data_pump.fail('get_emp_fed_tax_rule_id', sqlerrm, p_emp_fed_tax_rule_user_key);
698 raise;
699 end get_emp_fed_tax_rule_id;
700
701 /* get_emp_state_tax_rule_id - requires user key */
702 function get_emp_state_tax_rule_id
703 (
704 p_emp_state_tax_rule_user_key in varchar2
705 ) return number is
706 l_emp_state_tax_rule_id number;
707 begin
708 l_emp_state_tax_rule_id := user_key_to_id( p_emp_state_tax_rule_user_key );
709 return(l_emp_state_tax_rule_id);
710 exception
711 when others then
712 hr_data_pump.fail('get_emp_state_tax_rule_id', sqlerrm, p_emp_state_tax_rule_user_key);
713 raise;
714 end get_emp_state_tax_rule_id;
715
716 /* get_emp_county_tax_rule_id - requires user key */
717 function get_emp_county_tax_rule_id
718 (
719 p_emp_county_tax_rule_user_key in varchar2
720 ) return number is
721 l_emp_county_tax_rule_id number;
722 begin
723 l_emp_county_tax_rule_id := user_key_to_id( p_emp_county_tax_rule_user_key );
724 return(l_emp_county_tax_rule_id);
725 exception
726 when others then
727 hr_data_pump.fail('get_emp_county_tax_rule_id', sqlerrm, p_emp_county_tax_rule_user_key);
728 raise;
729 end get_emp_county_tax_rule_id;
730
731 /* get_emp_city_tax_rule_id - requires user key */
732 function get_emp_city_tax_rule_id
733 (
734 p_emp_city_tax_rule_user_key in varchar2
735 ) return number is
736 l_emp_city_tax_rule_id number;
737 begin
738 l_emp_city_tax_rule_id := user_key_to_id( p_emp_city_tax_rule_user_key );
739 return(l_emp_city_tax_rule_id);
740 exception
741 when others then
742 hr_data_pump.fail('get_emp_city_tax_rule_id', sqlerrm, p_emp_city_tax_rule_user_key);
743 raise;
744 end get_emp_city_tax_rule_id;
745
746 /* get_ler_internal */
747 function get_ler_internal
748 (p_business_group_id in number
749 ,p_effective_date in date
750 ,p_ler_name in varchar2
751 ,p_caller in varchar2
752 ) return number is
753 l_ler_id number;
754 begin
755 l_ler_id :=
756 get_ler_id(p_business_group_id, p_ler_name, p_effective_date);
757 return(l_ler_id);
758 exception
759 when others then
760 hr_data_pump.fail(p_caller, sqlerrm, p_business_group_id,
761 p_effective_date, p_ler_name);
762 raise;
763 end get_ler_internal;
764
765 /* get_start_life_reason_id */
766 function get_start_life_reason_id
767 (p_business_group_id in number
768 ,p_effective_date in date
769 ,p_start_life_reason in varchar2
770 ) return number is
771 l_start_life_reason_id number;
772 begin
773 l_start_life_reason_id :=
774 get_ler_internal
775 (p_business_group_id, p_effective_date, p_start_life_reason,
776 'get_start_life_reason_id');
777 return(l_start_life_reason_id);
778 end get_start_life_reason_id;
779
780 /* get_end_life_reason_id */
781 function get_end_life_reason_id
782 (p_business_group_id in number
783 ,p_effective_date in date
784 ,p_end_life_reason in varchar2
785 ) return number is
786 l_end_life_reason_id number;
787 begin
788 l_end_life_reason_id :=
789 get_ler_internal
790 (p_business_group_id, p_effective_date, p_end_life_reason,
791 'get_end_life_reason_id');
792 return(l_end_life_reason_id);
793 end get_end_life_reason_id;
794
795 /* get_benefit_group_id */
796 function get_benefit_group_id
797 (p_business_group_id in number
798 ,p_benefit_group in varchar2
799 ) return number is
800 l_benefit_group_id number;
801 begin
802 select bbg.benfts_grp_id
803 into l_benefit_group_id
804 from ben_benfts_grp bbg
805 where bbg.name = p_benefit_group
806 and bbg.business_group_id + 0 = p_business_group_id;
807 return(l_benefit_group_id);
808 exception
809 when others then
810 hr_data_pump.fail('get_benefit_group_id', sqlerrm, p_business_group_id,
811 p_benefit_group);
812 raise;
813 end get_benefit_group_id;
814
815 /****** start OAB additions ******/
816
817 /** start of USER_KEY additions */
818
819 /* returns a ptnl_ler_for_per_id from supplied user_key */
820 function get_ptnl_ler_for_per_id
821 ( p_ptnl_ler_for_per_user_key in varchar2
822 ) return number is
823 l_ptnl_ler_for_per_id number;
824 begin
825 if p_ptnl_ler_for_per_user_key is null then
826 return null;
827 end if;
828 l_ptnl_ler_for_per_id := user_key_to_id( p_ptnl_ler_for_per_user_key );
829 return(l_ptnl_ler_for_per_id);
830 exception
831 when others then
832 hr_data_pump.fail('get_ptnl_ler_for_per_id', sqlerrm, p_ptnl_ler_for_per_user_key);
833 raise;
834 end get_ptnl_ler_for_per_id;
835 -------------
836 function get_ws_mgr_id
837 ( p_ws_mgr_user_key in varchar2
838 ) return number is
839 l_ws_mgr_id number;
840 begin
841 if p_ws_mgr_user_key is null then
842 return null;
843 end if;
844 l_ws_mgr_id := user_key_to_id( p_ws_mgr_user_key );
845 return(l_ws_mgr_id);
846 exception
847 when others then
848 hr_data_pump.fail('GET_WS_MGR_ID', sqlerrm, p_ws_mgr_user_key);
849 raise;
850 end get_ws_mgr_id;
851 --
852 function get_group_pl_id
853 ( p_group_pl_user_key in varchar2
854 ) return number is
855 l_group_pl_id number;
856 begin
857 if p_group_pl_user_key is null then
858 return null;
859 end if;
860 l_group_pl_id := user_key_to_id( p_group_pl_user_key );
861 return(l_group_pl_id);
862 exception
863 when others then
864 hr_data_pump.fail('get_group_pl_id', sqlerrm, p_group_pl_user_key);
865 raise;
866 end get_group_pl_id;
867 --
868 function get_mgr_ovrid_person_id
869 ( p_mgr_ovrid_person_user_key in varchar2
870 ) return number is
871 l_mgr_ovrid_person_id number;
872 begin
873 if p_mgr_ovrid_person_user_key is null then
874 return null;
875 end if;
876 l_mgr_ovrid_person_id := user_key_to_id( p_mgr_ovrid_person_user_key );
877 return(l_mgr_ovrid_person_id);
878 exception
879 when others then
880 hr_data_pump.fail('get_mgr_ovrid_person_id', sqlerrm, p_mgr_ovrid_person_user_key);
881 raise;
882 end get_mgr_ovrid_person_id;
883 ------------
884
885 /* returns a csd_by_ptnl_ler_for_per_id from supplied user_key */
886 function get_csd_by_ptnl_ler_for_per_id
887 ( p_csd_by_ppl_user_key in varchar2 -- note abbreviation
888 ) return number is
889 l_csd_by_ptnl_ler_for_per_id number;
890 begin
891 if p_csd_by_ppl_user_key is null then
892 return null;
893 end if;
894 l_csd_by_ptnl_ler_for_per_id := user_key_to_id( p_csd_by_ppl_user_key );
895 return(l_csd_by_ptnl_ler_for_per_id);
896 exception
897 when others then
898 hr_data_pump.fail('get_csd_by_ptnl_ler_for_per_id', sqlerrm, p_csd_by_ppl_user_key);
899 raise;
900 end get_csd_by_ptnl_ler_for_per_id;
901
902 /* returns a ptnl_ler_for_per object_version_number */
903 function get_ptnl_ler_for_per_ovn
904 ( p_ptnl_ler_for_per_user_key in varchar2
905 ) return number is
906 l_ovn number;
907 begin
908 select ppl.object_version_number
909 into l_ovn
910 from ben_ptnl_ler_for_per ppl,
911 hr_pump_batch_line_user_keys key
912 where key.user_key_value = p_ptnl_ler_for_per_user_key
913 and ppl.ptnl_ler_for_per_id = key.unique_key_id;
914 return(l_ovn);
915 exception
916 when others then
917 hr_data_pump.fail('get_ptnl_ler_for_per_ovn', sqlerrm, p_ptnl_ler_for_per_user_key);
918 raise;
919 end get_ptnl_ler_for_per_ovn;
920
921 /* returns a per_in_ler_id from supplied user_key */
922 function get_per_in_ler_id
923 ( p_per_in_ler_user_key in varchar2
924 ) return number is
925 l_per_in_ler_id number;
926 begin
927 if p_per_in_ler_user_key is null then
928 return null;
929 end if;
930 l_per_in_ler_id := user_key_to_id( p_per_in_ler_user_key );
931 return(l_per_in_ler_id);
932 exception
933 when others then
934 hr_data_pump.fail('get_per_in_ler_id', sqlerrm, p_per_in_ler_user_key);
935 raise;
936 end get_per_in_ler_id;
937
938 /* returns a per_in_ler_id from supplied user_key */
939 function get_trgr_table_pk_id
940 ( p_trgr_table_pk_user_key in varchar2
941 ) return number is
942 l_trgr_table_pk_id number;
943 begin
944 /* if p_per_in_ler_user_key is null then
945 return null;
946 end if;
947 l_trgr_table_pk_id := user_key_to_id( p_trgr_table_pk_user_key );
948 return(l_trgr_table_pk_id); */
949 -- return a null value for now
950 return null;
951 exception
952 when others then
953 hr_data_pump.fail('get_trgr_table_pk_id', sqlerrm, p_trgr_table_pk_user_key);
954 raise;
955 end get_trgr_table_pk_id;
956
957 /* returns a bckt_per_in_ler_id from supplied user_key */
958 function get_bckt_per_in_ler_id
959 ( p_bckt_per_in_ler_user_key in varchar2
960 ) return number is
961 l_bckt_per_in_ler_id number;
962 begin
963 if p_bckt_per_in_ler_user_key is null then
964 return null;
965 end if;
966 l_bckt_per_in_ler_id := user_key_to_id( p_bckt_per_in_ler_user_key );
967 return(l_bckt_per_in_ler_id);
968 exception
969 when others then
970 hr_data_pump.fail('get_bckt_per_in_ler_id', sqlerrm, p_bckt_per_in_ler_user_key);
971 raise;
972 end get_bckt_per_in_ler_id;
973
974 /* returns a ended_per_in_ler_id from supplied user_key */
975 function get_ended_per_in_ler_id
976 ( p_ended_per_in_ler_user_key in varchar2
977 ) return number is
978 l_ended_per_in_ler_id number;
979 begin
980 if p_ended_per_in_ler_user_key is null then
981 return null;
982 end if;
983 l_ended_per_in_ler_id := user_key_to_id( p_ended_per_in_ler_user_key );
984 return(l_ended_per_in_ler_id);
985 exception
986 when others then
987 hr_data_pump.fail('get_ended_per_in_ler_id', sqlerrm, p_ended_per_in_ler_user_key);
988 raise;
989 end get_ended_per_in_ler_id;
990
991 /* returns a per_in_ler object_version_number */
992 function get_per_in_ler_ovn
993 ( p_per_in_ler_user_key in varchar2
994 ) return number is
995 l_ovn number;
996 begin
997 select pil.object_version_number
998 into l_ovn
999 from ben_per_in_ler pil,
1000 hr_pump_batch_line_user_keys key
1001 where key.user_key_value = p_per_in_ler_user_key
1002 and pil.per_in_ler_id = key.unique_key_id;
1003 return(l_ovn);
1004 exception
1005 when others then
1006 hr_data_pump.fail('get_per_in_ler_ovn', sqlerrm, p_per_in_ler_user_key);
1007 raise;
1008 end get_per_in_ler_ovn;
1009
1010 /* returns a prtt_enrt_rslt_id from supplied user_key */
1011 function get_prtt_enrt_rslt_id
1012 ( p_prtt_enrt_rslt_user_key in varchar2
1013 ) return number is
1014 l_prtt_enrt_rslt_id number;
1015 begin
1016 if p_prtt_enrt_rslt_user_key is null then
1017 return null;
1018 end if;
1019 l_prtt_enrt_rslt_id := user_key_to_id( p_prtt_enrt_rslt_user_key );
1020 return(l_prtt_enrt_rslt_id);
1021 exception
1022 when others then
1023 hr_data_pump.fail('get_prtt_enrt_rslt_id', sqlerrm, p_prtt_enrt_rslt_user_key);
1024 raise;
1025 end get_prtt_enrt_rslt_id;
1026
1027 /* returns a rplcs_sspndd_rslt_id from supplied user_key */
1028 function get_rplcs_sspndd_rslt_id
1029 ( p_rplcs_sspndd_rslt_user_key in varchar2
1030 ) return number is
1031 l_rplcs_sspndd_rslt_id number;
1032 begin
1033 if p_rplcs_sspndd_rslt_user_key is null then
1034 return null;
1035 end if;
1036 l_rplcs_sspndd_rslt_id := user_key_to_id( p_rplcs_sspndd_rslt_user_key );
1037 return(l_rplcs_sspndd_rslt_id);
1038 exception
1039 when others then
1040 hr_data_pump.fail('get_rplcs_sspndd_rslt_id', sqlerrm, p_rplcs_sspndd_rslt_user_key);
1041 raise;
1042 end get_rplcs_sspndd_rslt_id;
1043
1044 /* returns a prtt_enrt_rslt object_version_number */
1045 function get_prtt_enrt_rslt_ovn
1046 ( p_prtt_enrt_rslt_user_key in varchar2,
1047 p_effective_date in date
1048 ) return number is
1049 l_ovn number;
1050 begin
1051 select pen.object_version_number
1052 into l_ovn
1053 from ben_prtt_enrt_rslt_f pen,
1054 hr_pump_batch_line_user_keys key
1055 where key.user_key_value = p_prtt_enrt_rslt_user_key
1056 and pen.prtt_enrt_rslt_id = key.unique_key_id
1057 and p_effective_date between
1058 pen.effective_start_date and pen.effective_end_date;
1059 return(l_ovn);
1060 exception
1061 when others then
1062 hr_data_pump.fail('get_prtt_enrt_rslt_ovn', sqlerrm, p_prtt_enrt_rslt_user_key);
1063 raise;
1064 end get_prtt_enrt_rslt_ovn;
1065
1066 /* returns a prtt_rt_val_id from supplied user_key */
1067 function get_prtt_rt_val_id
1068 ( p_prtt_rt_val_user_key in varchar2
1069 ) return number is
1070 l_prtt_rt_val_id number;
1071 begin
1072 if p_prtt_rt_val_user_key is null then
1073 return null;
1074 end if;
1075 l_prtt_rt_val_id := user_key_to_id( p_prtt_rt_val_user_key );
1076 return(l_prtt_rt_val_id);
1077 exception
1078 when others then
1079 hr_data_pump.fail('get_prtt_rt_val_id', sqlerrm, p_prtt_rt_val_user_key);
1080 raise;
1081 end get_prtt_rt_val_id;
1082
1083 /* returns a prtt_rt_val object_version_number */
1084 function get_prtt_rt_val_ovn
1085 ( p_prtt_rt_val_user_key in varchar2
1086 ) return number is
1087 l_ovn number;
1088 begin
1089 select prv.object_version_number
1090 into l_ovn
1091 from ben_prtt_rt_val prv,
1092 hr_pump_batch_line_user_keys key
1093 where key.user_key_value = p_prtt_rt_val_user_key
1094 and prv.prtt_rt_val_id = key.unique_key_id;
1095 return(l_ovn);
1096 exception
1097 when others then
1098 hr_data_pump.fail('get_prtt_rt_val_ovn', sqlerrm, p_prtt_rt_val_user_key);
1099 raise;
1100 end get_prtt_rt_val_ovn;
1101
1102 /* returns a cbr_quald_bnf_id from supplied user_key */
1103 function get_cbr_quald_bnf_id
1104 ( p_cbr_quald_bnf_user_key in varchar2
1105 ) return number is
1106 l_cbr_quald_bnf_id number;
1107 begin
1108 if p_cbr_quald_bnf_user_key is null then
1109 return null;
1110 end if;
1111 l_cbr_quald_bnf_id := user_key_to_id( p_cbr_quald_bnf_user_key );
1112 return(l_cbr_quald_bnf_id);
1113 exception
1114 when others then
1115 hr_data_pump.fail('get_cbr_quald_bnf_id', sqlerrm, p_cbr_quald_bnf_user_key);
1116 raise;
1117 end get_cbr_quald_bnf_id;
1118
1119 /* returns a cbr_quald_bnf object_version_number */
1120 function get_cbr_quald_bnf_ovn
1121 ( p_cbr_quald_bnf_user_key in varchar2
1122 ) return number is
1123 l_ovn number;
1124 begin
1125 select cqb.object_version_number
1126 into l_ovn
1127 from ben_cbr_quald_bnf cqb,
1128 hr_pump_batch_line_user_keys key
1129 where key.user_key_value = p_cbr_quald_bnf_user_key
1130 and cqb.cbr_quald_bnf_id = key.unique_key_id;
1131 return(l_ovn);
1132 exception
1133 when others then
1134 hr_data_pump.fail('get_cbr_quald_bnf_ovn', sqlerrm, p_cbr_quald_bnf_user_key);
1135 raise;
1136 end get_cbr_quald_bnf_ovn;
1137
1138 /* returns a cbr_per_in_ler_id from supplied user_key */
1139 function get_cbr_per_in_ler_id
1140 ( p_cbr_per_in_ler_user_key in varchar2
1141 ) return number is
1142 l_cbr_per_in_ler_id number;
1143 begin
1144 if p_cbr_per_in_ler_user_key is null then
1145 return null;
1146 end if;
1147 l_cbr_per_in_ler_id := user_key_to_id( p_cbr_per_in_ler_user_key );
1148 return(l_cbr_per_in_ler_id);
1149 exception
1150 when others then
1151 hr_data_pump.fail('get_cbr_per_in_ler_id', sqlerrm, p_cbr_per_in_ler_user_key);
1152 raise;
1153 end get_cbr_per_in_ler_id;
1154
1155 /* returns a cbr_per_in_ler object_version_number */
1156 function get_cbr_per_in_ler_ovn
1157 ( p_cbr_per_in_ler_user_key in varchar2
1158 ) return number is
1159 l_ovn number;
1160 begin
1161 select crp.object_version_number
1162 into l_ovn
1163 from ben_cbr_per_in_ler crp,
1164 hr_pump_batch_line_user_keys key
1165 where key.user_key_value = p_cbr_per_in_ler_user_key
1166 and crp.cbr_per_in_ler_id = key.unique_key_id;
1167 return(l_ovn);
1168 exception
1169 when others then
1170 hr_data_pump.fail('get_cbr_per_in_ler_ovn', sqlerrm, p_cbr_per_in_ler_user_key);
1171 raise;
1172 end get_cbr_per_in_ler_ovn;
1173
1174 /* returns a elig_cvrd_dpnt_id from supplied user_key */
1175 function get_elig_cvrd_dpnt_id
1176 ( p_elig_cvrd_dpnt_user_key in varchar2
1177 ) return number is
1178 l_elig_cvrd_dpnt_id number;
1179 begin
1180 if p_elig_cvrd_dpnt_user_key is null then
1181 return null;
1182 end if;
1183 l_elig_cvrd_dpnt_id := user_key_to_id( p_elig_cvrd_dpnt_user_key );
1184 return(l_elig_cvrd_dpnt_id);
1185 exception
1186 when others then
1187 hr_data_pump.fail('get_elig_cvrd_dpnt_id', sqlerrm, p_elig_cvrd_dpnt_user_key);
1188 raise;
1189 end get_elig_cvrd_dpnt_id;
1190
1191 /* returns a elig_cvrd_dpnt object_version_number */
1192 function get_elig_cvrd_dpnt_ovn
1193 ( p_elig_cvrd_dpnt_user_key in varchar2,
1194 p_effective_date in date
1195 ) return number is
1196 l_ovn number;
1197 begin
1198 select pdp.object_version_number
1199 into l_ovn
1200 from ben_elig_cvrd_dpnt_f pdp,
1201 hr_pump_batch_line_user_keys key
1202 where key.user_key_value = p_elig_cvrd_dpnt_user_key
1203 and pdp.elig_cvrd_dpnt_id = key.unique_key_id
1204 and p_effective_date between
1205 pdp.effective_start_date and pdp.effective_end_date;
1206 return(l_ovn);
1207 exception
1208 when others then
1209 hr_data_pump.fail('get_elig_cvrd_dpnt_ovn', sqlerrm, p_effective_date, p_elig_cvrd_dpnt_user_key);
1210 raise;
1211 end get_elig_cvrd_dpnt_ovn;
1212
1213 /* returns a prtt_prem_id from supplied user_key */
1214 function get_prtt_prem_id
1215 ( p_prtt_prem_user_key in varchar2
1216 ) return number is
1217 l_prtt_prem_id number;
1218 begin
1219 if p_prtt_prem_user_key is null then
1220 return null;
1221 end if;
1222 l_prtt_prem_id := user_key_to_id( p_prtt_prem_user_key );
1223 return(l_prtt_prem_id);
1224 exception
1225 when others then
1226 hr_data_pump.fail('get_prtt_prem_id', sqlerrm, p_prtt_prem_user_key);
1227 raise;
1228 end get_prtt_prem_id;
1229
1230 /* returns a prtt_prem object_version_number */
1231 function get_prtt_prem_ovn
1232 ( p_prtt_prem_user_key in varchar2,
1233 p_effective_date in date
1234 ) return number is
1235 l_ovn number;
1236 begin
1237 select ppe.object_version_number
1238 into l_ovn
1239 from ben_prtt_prem_f ppe,
1240 hr_pump_batch_line_user_keys key
1241 where key.user_key_value = p_prtt_prem_user_key
1242 and ppe.prtt_prem_id = key.unique_key_id
1243 and p_effective_date between
1244 ppe.effective_start_date and ppe.effective_end_date;
1245 return(l_ovn);
1246 exception
1247 when others then
1248 hr_data_pump.fail('get_prtt_prem_ovn', sqlerrm, p_effective_date, p_prtt_prem_user_key);
1249 raise;
1250 end get_prtt_prem_ovn;
1251
1252 /* returns a elig_dpnt_id from supplied user_key */
1253 function get_elig_dpnt_id
1254 ( p_elig_dpnt_user_key in varchar2
1255 ) return number is
1256 l_elig_dpnt_id number;
1257 begin
1258 if p_elig_dpnt_user_key is null then
1259 return null;
1260 end if;
1261 l_elig_dpnt_id := user_key_to_id( p_elig_dpnt_user_key );
1262 return(l_elig_dpnt_id);
1263 exception
1264 when others then
1265 hr_data_pump.fail('get_elig_dpnt_id', sqlerrm, p_elig_dpnt_user_key);
1266 raise;
1267 end get_elig_dpnt_id;
1268
1269 /* returns a elig_dpnt object_version_number */
1270 function get_elig_dpnt_ovn
1271 ( p_elig_dpnt_user_key in varchar2
1272 ) return number is
1273 l_ovn number;
1274 begin
1275 select egd.object_version_number
1276 into l_ovn
1277 from ben_elig_dpnt egd,
1278 hr_pump_batch_line_user_keys key
1279 where key.user_key_value = p_elig_dpnt_user_key
1280 and egd.elig_dpnt_id = key.unique_key_id;
1281 return(l_ovn);
1282 exception
1283 when others then
1284 hr_data_pump.fail('get_elig_dpnt_ovn', sqlerrm, p_elig_dpnt_user_key);
1285 raise;
1286 end get_elig_dpnt_ovn;
1287
1288 /* returns a elig_per_id from supplied user_key */
1289 function get_elig_per_id
1290 ( p_elig_per_user_key in varchar2
1291 ) return number is
1292 l_elig_per_id number;
1293 begin
1294 if p_elig_per_user_key is null then
1295 return null;
1296 end if;
1297 l_elig_per_id := user_key_to_id( p_elig_per_user_key );
1298 return(l_elig_per_id);
1299 exception
1300 when others then
1301 hr_data_pump.fail('get_elig_per_id', sqlerrm, p_elig_per_user_key);
1302 raise;
1303 end get_elig_per_id;
1304
1305 /* returns a elig_per object_version_number */
1306 function get_elig_per_ovn
1307 ( p_elig_per_user_key in varchar2,
1308 p_effective_date in date
1309 ) return number is
1310 l_ovn number;
1311 begin
1312 select pep.object_version_number
1313 into l_ovn
1314 from ben_elig_per_f pep,
1315 hr_pump_batch_line_user_keys key
1316 where key.user_key_value = p_elig_per_user_key
1317 and pep.elig_per_id = key.unique_key_id
1318 and p_effective_date between
1319 pep.effective_start_date and pep.effective_end_date;
1320 return(l_ovn);
1321 exception
1322 when others then
1323 hr_data_pump.fail('get_elig_per_ovn', sqlerrm, p_effective_date, p_elig_per_user_key);
1324 raise;
1325 end get_elig_per_ovn;
1326
1327 /* returns a elig_per_opt_id from supplied user_key */
1328 function get_elig_per_opt_id
1329 ( p_elig_per_opt_user_key in varchar2
1330 ) return number is
1331 l_elig_per_opt_id number;
1332 begin
1333 if p_elig_per_opt_user_key is null then
1334 return null;
1335 end if;
1336 l_elig_per_opt_id := user_key_to_id( p_elig_per_opt_user_key );
1337 return(l_elig_per_opt_id);
1338 exception
1339 when others then
1340 hr_data_pump.fail('get_elig_per_opt_id', sqlerrm, p_elig_per_opt_user_key);
1341 raise;
1342 end get_elig_per_opt_id;
1343
1344 /* returns a elig_per_opt object_version_number */
1345 function get_elig_per_opt_ovn
1346 ( p_elig_per_opt_user_key in varchar2,
1347 p_effective_date in date
1348 ) return number is
1349 l_ovn number;
1350 begin
1351 select epo.object_version_number
1352 into l_ovn
1353 from ben_elig_per_opt_f epo,
1354 hr_pump_batch_line_user_keys key
1355 where key.user_key_value = p_elig_per_opt_user_key
1356 and epo.elig_per_opt_id = key.unique_key_id
1357 and p_effective_date between
1358 epo.effective_start_date and epo.effective_end_date;
1359 return(l_ovn);
1360 exception
1361 when others then
1362 hr_data_pump.fail('get_elig_per_opt_ovn', sqlerrm, p_effective_date, p_elig_per_opt_user_key);
1363 raise;
1364 end get_elig_per_opt_ovn;
1365
1366 /* returns a pl_bnf_id from supplied user_key */
1367 function get_pl_bnf_id
1368 ( p_pl_bnf_user_key in varchar2
1369 ) return number is
1370 l_pl_bnf_id number;
1371 begin
1372 if p_pl_bnf_user_key is null then
1373 return null;
1374 end if;
1375 l_pl_bnf_id := user_key_to_id( p_pl_bnf_user_key );
1376 return(l_pl_bnf_id);
1377 exception
1378 when others then
1379 hr_data_pump.fail('get_pl_bnf_id', sqlerrm, p_pl_bnf_user_key);
1380 raise;
1381 end get_pl_bnf_id;
1382
1383 /* returns a pl_bnf object_version_number */
1384 function get_pl_bnf_ovn
1385 ( p_pl_bnf_user_key in varchar2,
1386 p_effective_date in date
1387 ) return number is
1388 l_ovn number;
1389 begin
1390 select pbn.object_version_number
1391 into l_ovn
1392 from ben_pl_bnf_f pbn,
1393 hr_pump_batch_line_user_keys key
1394 where key.user_key_value = p_pl_bnf_user_key
1395 and pbn.pl_bnf_id = key.unique_key_id
1396 and p_effective_date between
1397 pbn.effective_start_date and pbn.effective_end_date;
1398 return(l_ovn);
1399 exception
1400 when others then
1401 hr_data_pump.fail('get_pl_bnf_ovn', sqlerrm, p_effective_date, p_pl_bnf_user_key);
1402 raise;
1403 end get_pl_bnf_ovn;
1404
1405 /* returns a oipl_id from supplied user_key */
1406 function get_oipl_id
1407 ( p_oipl_user_key in varchar2
1408 ) return number is
1409 l_oipl_id number;
1410 begin
1411 if p_oipl_user_key is null then
1412 return null;
1413 end if;
1414 l_oipl_id := user_key_to_id( p_oipl_user_key );
1415 return(l_oipl_id);
1416 exception
1417 when others then
1418 hr_data_pump.fail('get_oipl_id', sqlerrm, p_oipl_user_key);
1419 raise;
1420 end get_oipl_id;
1421
1422 /* returns a oipl object_version_number */
1423 function get_oipl_ovn
1424 ( p_oipl_user_key in varchar2,
1425 p_effective_date in date
1426 ) return number is
1427 l_ovn number;
1428 begin
1429 select cop.object_version_number
1430 into l_ovn
1431 from ben_oipl_f cop,
1432 hr_pump_batch_line_user_keys key
1433 where key.user_key_value = p_oipl_user_key
1434 and cop.oipl_id = key.unique_key_id
1435 and p_effective_date between
1436 cop.effective_start_date and cop.effective_end_date;
1437 return(l_ovn);
1438 exception
1439 when others then
1440 hr_data_pump.fail('get_oipl_ovn', sqlerrm, p_effective_date, p_oipl_user_key);
1441 raise;
1442 end get_oipl_ovn;
1443
1444 /* returns a plip_id from supplied user_key */
1445 function get_plip_id
1446 ( p_plip_user_key in varchar2
1447 ) return number is
1448 l_plip_id number;
1449 begin
1450 if p_plip_user_key is null then
1451 return null;
1452 end if;
1453 l_plip_id := user_key_to_id( p_plip_user_key );
1454 return(l_plip_id);
1455 exception
1456 when others then
1457 hr_data_pump.fail('get_plip_id', sqlerrm, p_plip_user_key);
1458 raise;
1459 end get_plip_id;
1460
1461 /* returns a plip object_version_number */
1462 function get_plip_ovn
1463 ( p_plip_user_key in varchar2,
1464 p_effective_date in date
1465 ) return number is
1466 l_ovn number;
1467 begin
1468 select cpp.object_version_number
1469 into l_ovn
1470 from ben_plip_f cpp,
1471 hr_pump_batch_line_user_keys key
1472 where key.user_key_value = p_plip_user_key
1473 and cpp.plip_id = key.unique_key_id
1474 and p_effective_date between
1475 cpp.effective_start_date and cpp.effective_end_date;
1476 return(l_ovn);
1477 exception
1478 when others then
1479 hr_data_pump.fail('get_plip_ovn', sqlerrm, p_effective_date, p_plip_user_key);
1480 raise;
1481 end get_plip_ovn;
1482
1483 /* returns a ptip_id from supplied user_key */
1484 function get_ptip_id
1485 ( p_ptip_user_key in varchar2
1486 ) return number is
1487 l_ptip_id number;
1488 begin
1489 if p_ptip_user_key is null then
1490 return null;
1491 end if;
1492 l_ptip_id := user_key_to_id( p_ptip_user_key );
1493 return(l_ptip_id);
1494 exception
1495 when others then
1496 hr_data_pump.fail('get_ptip_id', sqlerrm, p_ptip_user_key);
1497 raise;
1498 end get_ptip_id;
1499
1500 /* returns a ptip object_version_number */
1501 function get_ptip_ovn
1502 ( p_ptip_user_key in varchar2,
1503 p_effective_date in date
1504 ) return number is
1505 l_ovn number;
1506 begin
1507 select ctp.object_version_number
1508 into l_ovn
1509 from ben_ptip_f ctp,
1510 hr_pump_batch_line_user_keys key
1511 where key.user_key_value = p_ptip_user_key
1512 and ctp.ptip_id = key.unique_key_id
1513 and p_effective_date between
1514 ctp.effective_start_date and ctp.effective_end_date;
1515 return(l_ovn);
1516 exception
1517 when others then
1518 hr_data_pump.fail('get_ptip_ovn', sqlerrm, p_effective_date, p_ptip_user_key);
1519 raise;
1520 end get_ptip_ovn;
1521
1522 /* returns a enrt_rt_id from supplied user_key */
1523 function get_enrt_rt_id
1524 ( p_enrt_rt_user_key in varchar2
1525 ) return number is
1526 l_enrt_rt_id number;
1527 begin
1528 if p_enrt_rt_user_key is null then
1529 return null;
1530 end if;
1531 l_enrt_rt_id := user_key_to_id( p_enrt_rt_user_key );
1532 return(l_enrt_rt_id);
1533 exception
1534 when others then
1535 hr_data_pump.fail('get_enrt_rt_id', sqlerrm, p_enrt_rt_user_key);
1536 raise;
1537 end get_enrt_rt_id;
1538
1539 /* returns a enrt_rt object_version_number */
1540 function get_enrt_rt_ovn
1541 ( p_enrt_rt_user_key in varchar2
1542 ) return number is
1543 l_ovn number;
1544 begin
1545 select ecr.object_version_number
1546 into l_ovn
1547 from ben_enrt_rt ecr,
1548 hr_pump_batch_line_user_keys key
1549 where key.user_key_value = p_enrt_rt_user_key
1550 and ecr.enrt_rt_id = key.unique_key_id;
1551 return(l_ovn);
1552 exception
1553 when others then
1554 hr_data_pump.fail('get_enrt_rt_ovn', sqlerrm, p_enrt_rt_user_key);
1555 raise;
1556 end get_enrt_rt_ovn;
1557
1558 /* returns a enrt_perd_id from supplied user_key */
1559 function get_enrt_perd_id
1560 ( p_enrt_perd_user_key in varchar2
1561 ) return number is
1562 l_enrt_perd_id number;
1563 begin
1564 if p_enrt_perd_user_key is null then
1565 return null;
1566 end if;
1567 l_enrt_perd_id := user_key_to_id( p_enrt_perd_user_key );
1568 return(l_enrt_perd_id);
1569 exception
1570 when others then
1571 hr_data_pump.fail('get_enrt_perd_id', sqlerrm, p_enrt_perd_user_key);
1572 raise;
1573 end get_enrt_perd_id;
1574
1575 /* returns a enrt_perd object_version_number */
1576 function get_enrt_perd_ovn
1577 ( p_enrt_perd_user_key in varchar2
1578 ) return number is
1579 l_ovn number;
1580 begin
1581 select enp.object_version_number
1582 into l_ovn
1583 from ben_enrt_perd enp,
1584 hr_pump_batch_line_user_keys key
1585 where key.user_key_value = p_enrt_perd_user_key
1586 and enp.enrt_perd_id = key.unique_key_id;
1587 return(l_ovn);
1588 exception
1589 when others then
1590 hr_data_pump.fail('get_enrt_perd_ovn', sqlerrm, p_enrt_perd_user_key);
1591 raise;
1592 end get_enrt_perd_ovn;
1593
1594 /* returns a prtt_reimbmt_rqst_id from supplied user_key */
1595 function get_prtt_reimbmt_rqst_id
1596 ( p_prtt_reimbmt_rqst_user_key in varchar2
1597 ) return number is
1598 l_prtt_reimbmt_rqst_id number;
1599 begin
1600 if p_prtt_reimbmt_rqst_user_key is null then
1601 return null;
1602 end if;
1603 l_prtt_reimbmt_rqst_id := user_key_to_id( p_prtt_reimbmt_rqst_user_key );
1604 return(l_prtt_reimbmt_rqst_id);
1605 exception
1606 when others then
1607 hr_data_pump.fail('get_prtt_reimbmt_rqst_id', sqlerrm, p_prtt_reimbmt_rqst_user_key);
1608 raise;
1609 end get_prtt_reimbmt_rqst_id;
1610
1611 /* returns a prtt_reimbmt_rqst object_version_number */
1612 function get_prtt_reimbmt_rqst_ovn
1613 ( p_prtt_reimbmt_rqst_user_key in varchar2,
1614 p_effective_date in date
1615 ) return number is
1616 l_ovn number;
1617 begin
1618 select prc.object_version_number
1619 into l_ovn
1620 from ben_prtt_reimbmt_rqst_f prc,
1621 hr_pump_batch_line_user_keys key
1622 where key.user_key_value = p_prtt_reimbmt_rqst_user_key
1623 and prc.prtt_reimbmt_rqst_id = key.unique_key_id
1624 and p_effective_date between
1625 prc.effective_start_date and prc.effective_end_date;
1626 return(l_ovn);
1627 exception
1628 when others then
1629 hr_data_pump.fail('get_prtt_reimbmt_rqst_ovn', sqlerrm, p_prtt_reimbmt_rqst_user_key);
1630 raise;
1631 end get_prtt_reimbmt_rqst_ovn;
1632
1633 /* returns a elig_per_elctbl_chc_id from supplied user_key */
1634 function get_elig_per_elctbl_chc_id
1635 ( p_elig_per_elctbl_chc_user_key in varchar2
1636 ) return number is
1637 l_elig_per_elctbl_chc_id number;
1638 begin
1639 if p_elig_per_elctbl_chc_user_key is null then
1640 return null;
1641 end if;
1642 l_elig_per_elctbl_chc_id := user_key_to_id( p_elig_per_elctbl_chc_user_key );
1643 return(l_elig_per_elctbl_chc_id);
1644 exception
1645 when others then
1646 hr_data_pump.fail('get_elig_per_elctbl_chc_id', sqlerrm, p_elig_per_elctbl_chc_user_key);
1647 raise;
1648 end get_elig_per_elctbl_chc_id;
1649
1650 /* returns a elig_per_elctbl_chc object_version_number */
1651 function get_elig_per_elctbl_chc_ovn
1652 ( p_elig_per_elctbl_chc_user_key in varchar2
1653 ) return number is
1654 l_ovn number;
1655 begin
1656 select epe.object_version_number
1657 into l_ovn
1658 from ben_elig_per_elctbl_chc epe,
1659 hr_pump_batch_line_user_keys key
1660 where key.user_key_value = p_elig_per_elctbl_chc_user_key
1661 and epe.elig_per_elctbl_chc_id = key.unique_key_id;
1662 return(l_ovn);
1663 exception
1664 when others then
1665 hr_data_pump.fail('get_elig_per_elctbl_chc_ovn', sqlerrm, p_elig_per_elctbl_chc_user_key);
1666 raise;
1667 end get_elig_per_elctbl_chc_ovn;
1668
1669 /** end of USER_KEY additions */
1670
1671 /** start of NAME additions for BEN tables */
1672 -- Note: a similar routine named get_benefit_group_id predates this. Left it for upward compat.
1673 /* returns a benfts_grp_id */
1674 function get_benfts_grp_id
1675 ( p_business_group_id in number,
1676 p_benefits_group in varchar2
1677 ) return number is
1678 l_benefit_group_id number;
1679 begin
1680 select bng.benfts_grp_id
1681 into l_benefit_group_id
1682 from ben_benfts_grp bng
1683 where bng.name = p_benefits_group
1684 and bng.business_group_id + 0 = p_business_group_id;
1685 return(l_benefit_group_id);
1686 exception
1687 when others then
1688 hr_data_pump.fail('get_benfts_grp_id', sqlerrm, p_business_group_id, p_benefits_group);
1689 raise;
1690 end get_benfts_grp_id;
1691
1692 /* returns a benefits group object version number */
1693 function get_benfts_grp_ovn
1694 ( p_business_group_id in number,
1695 p_benefits_group in varchar2
1696 ) return number is
1697 l_ovn number;
1698 begin
1699 select bng.object_version_number
1700 into l_ovn
1701 from ben_benfts_grp bng
1702 where bng.name = p_benefits_group
1703 and bng.business_group_id + 0 = p_business_group_id;
1704 return(l_ovn);
1705 exception
1706 when others then
1707 hr_data_pump.fail('get_benfts_grp_ovn', sqlerrm, p_business_group_id, p_benefits_group);
1708 raise;
1709 end get_benfts_grp_ovn;
1710
1711 /* returns a pl_typ_id */
1712 function get_pl_typ_id
1713 ( p_business_group_id in number,
1714 p_plan_type in varchar2,
1715 p_effective_date in date
1716 ) return number is
1717 l_plan_type_id number;
1718 begin
1719 select ptp.pl_typ_id
1720 into l_plan_type_id
1721 from ben_pl_typ_f ptp
1722 where ptp.name = p_plan_type
1723 and ptp.business_group_id + 0 = p_business_group_id
1724 and p_effective_date between
1725 ptp.effective_start_date and ptp.effective_end_date;
1726 return(l_plan_type_id);
1727 exception
1728 when others then
1729 hr_data_pump.fail('get_pl_typ_id', sqlerrm, p_business_group_id, p_plan_type, p_effective_date);
1730 raise;
1731 end get_pl_typ_id;
1732
1733 /* returns a plan type object version number */
1734 function get_pl_typ_ovn
1735 ( p_business_group_id in number,
1736 p_plan_type in varchar2,
1737 p_effective_date in date
1738 ) return number is
1739 l_ovn number;
1740 begin
1741 select ptp.object_version_number
1742 into l_ovn
1743 from ben_pl_typ_f ptp
1744 where ptp.name = p_plan_type
1745 and ptp.business_group_id + 0 = p_business_group_id
1746 and p_effective_date between
1747 ptp.effective_start_date and ptp.effective_end_date;
1748 return(l_ovn);
1749 exception
1750 when others then
1751 hr_data_pump.fail('get_pl_typ_ovn', sqlerrm, p_business_group_id, p_plan_type, p_effective_date);
1752 raise;
1753 end get_pl_typ_ovn;
1754
1755 -- Note: an overloaded routine predates this. Left it for upward compat.
1756 /* returns a ler_id */
1757 function get_ler_id
1758 ( p_business_group_id in number,
1759 p_life_event_reason in varchar2,
1760 p_effective_date in date
1761 ) return number is
1762 l_ler_id number;
1763 begin
1764 select ler.ler_id
1765 into l_ler_id
1766 from ben_ler_f ler
1767 where ler.name = p_life_event_reason
1768 and ler.business_group_id + 0 = p_business_group_id
1769 and p_effective_date between
1770 ler.effective_start_date and ler.effective_end_date;
1771 return(l_ler_id);
1772 exception
1773 when others then
1774 hr_data_pump.fail('get_ler_id', sqlerrm, p_business_group_id, p_life_event_reason, p_effective_date);
1775 raise;
1776 end get_ler_id;
1777
1778 /* returns a life event reason object version number */
1779 function get_ler_ovn
1780 ( p_business_group_id in number,
1781 p_life_event_reason in varchar2,
1782 p_effective_date in date
1783 ) return number is
1784 l_ovn number;
1785 begin
1786 select ler.object_version_number
1787 into l_ovn
1788 from ben_ler_f ler
1789 where ler.name = p_life_event_reason
1790 and ler.business_group_id + 0 = p_business_group_id
1791 and p_effective_date between
1792 ler.effective_start_date and ler.effective_end_date;
1793 return(l_ovn);
1794 exception
1795 when others then
1796 hr_data_pump.fail('get_ler_ovn', sqlerrm, p_business_group_id, p_life_event_reason, p_effective_date);
1797 raise;
1798 end get_ler_ovn;
1799
1800 /* returns an acty_base_rt_id */
1801 function get_acty_base_rt_id
1802 ( p_business_group_id in number,
1803 p_acty_base_rate in varchar2,
1804 p_effective_date in date
1805 ) return number is
1806 l_acty_base_rt_id number;
1807 begin
1808 select abr.acty_base_rt_id
1809 into l_acty_base_rt_id
1810 from ben_acty_base_rt_f abr
1811 where abr.name = p_acty_base_rate
1812 and abr.business_group_id + 0 = p_business_group_id
1813 and p_effective_date between
1814 abr.effective_start_date and abr.effective_end_date;
1815 return(l_acty_base_rt_id);
1816 exception
1817 when others then
1818 hr_data_pump.fail('get_acty_base_rt_id', sqlerrm, p_business_group_id, p_acty_base_rate, p_effective_date);
1819 raise;
1820 end get_acty_base_rt_id;
1821
1822 /* returns an acty base rate object version number */
1823 function get_acty_base_rt_ovn
1824 ( p_business_group_id in number,
1825 p_acty_base_rate in varchar2,
1826 p_effective_date in date
1827 ) return number is
1828 l_ovn number;
1829 begin
1830 select abr.object_version_number
1831 into l_ovn
1832 from ben_acty_base_rt_f abr
1833 where abr.name = p_acty_base_rate
1834 and abr.business_group_id + 0 = p_business_group_id
1835 and p_effective_date between
1836 abr.effective_start_date and abr.effective_end_date;
1837 return(l_ovn);
1838 exception
1839 when others then
1840 hr_data_pump.fail('get_acty_base_rt_ovn', sqlerrm, p_business_group_id, p_acty_base_rate, p_effective_date);
1841 raise;
1842 end get_acty_base_rt_ovn;
1843
1844 /* returns an actl_prem_id */
1845 function get_actl_prem_id
1846 ( p_business_group_id in number,
1847 p_actual_premium in varchar2,
1848 p_effective_date in date
1849 ) return number is
1850 l_actl_prem_id number;
1851 begin
1852 select apr.actl_prem_id
1853 into l_actl_prem_id
1854 from ben_actl_prem_f apr
1855 where apr.name = p_actual_premium
1856 and apr.business_group_id + 0 = p_business_group_id
1857 and p_effective_date between
1858 apr.effective_start_date and apr.effective_end_date;
1859 return(l_actl_prem_id);
1860 exception
1861 when others then
1862 hr_data_pump.fail('get_actl_prem_id', sqlerrm, p_business_group_id, p_actual_premium, p_effective_date);
1863 raise;
1864 end get_actl_prem_id;
1865
1866 /* returns an actual premium object version number */
1867 function get_actl_prem_ovn
1868 ( p_business_group_id in number,
1869 p_actual_premium in varchar2,
1870 p_effective_date in date
1871 ) return number is
1872 l_ovn number;
1873 begin
1874 select apr.object_version_number
1875 into l_ovn
1876 from ben_actl_prem_f apr
1877 where apr.name = p_actual_premium
1878 and apr.business_group_id + 0 = p_business_group_id
1879 and p_effective_date between
1880 apr.effective_start_date and apr.effective_end_date;
1881 return(l_ovn);
1882 exception
1883 when others then
1884 hr_data_pump.fail('get_actl_prem_ovn', sqlerrm, p_business_group_id, p_actual_premium, p_effective_date);
1885 raise;
1886 end get_actl_prem_ovn;
1887
1888 /* returns a comp_lvl_fctr_id */
1889 function get_comp_lvl_fctr_id
1890 ( p_business_group_id in number,
1891 p_comp_level_factor in varchar2
1892 ) return number is
1893 l_comp_lvl_fctr_id number;
1894 begin
1895 select clf.comp_lvl_fctr_id
1896 into l_comp_lvl_fctr_id
1897 from ben_comp_lvl_fctr clf
1898 where clf.name = p_comp_level_factor
1899 and clf.business_group_id + 0 = p_business_group_id;
1900 return(l_comp_lvl_fctr_id);
1901 exception
1902 when others then
1903 hr_data_pump.fail('get_comp_lvl_fctr_id', sqlerrm, p_business_group_id, p_comp_level_factor);
1904 raise;
1905 end get_comp_lvl_fctr_id;
1906
1907 /* returns a comp level factor object version number */
1908 function get_comp_lvl_fctr_ovn
1909 ( p_business_group_id in number,
1910 p_comp_level_factor in varchar2
1911 ) return number is
1912 l_ovn number;
1913 begin
1914 select clf.object_version_number
1915 into l_ovn
1916 from ben_comp_lvl_fctr clf
1917 where clf.name = p_comp_level_factor
1918 and clf.business_group_id + 0 = p_business_group_id;
1919 return(l_ovn);
1920 exception
1921 when others then
1922 hr_data_pump.fail('get_comp_lvl_fctr_ovn', sqlerrm, p_business_group_id, p_comp_level_factor);
1923 raise;
1924 end get_comp_lvl_fctr_ovn;
1925
1926 /* returns a cvg_amt_calc_mthd_id */
1927 function get_cvg_amt_calc_mthd_id
1928 ( p_business_group_id in number,
1929 p_cvg_amt_calc in varchar2,
1930 p_effective_date in date
1931 ) return number is
1932 l_cvg_amt_calc_mthd_id number;
1933 begin
1934 select ccm.cvg_amt_calc_mthd_id
1935 into l_cvg_amt_calc_mthd_id
1936 from ben_cvg_amt_calc_mthd_f ccm
1937 where ccm.name = p_cvg_amt_calc
1938 and ccm.business_group_id + 0 = p_business_group_id
1939 and p_effective_date between
1940 ccm.effective_start_date and ccm.effective_end_date;
1941 return(l_cvg_amt_calc_mthd_id);
1942 exception
1943 when others then
1944 hr_data_pump.fail('get_cvg_amt_calc_mthd_id', sqlerrm, p_business_group_id, p_cvg_amt_calc, p_effective_date);
1945 raise;
1946 end get_cvg_amt_calc_mthd_id;
1947
1948 /* returns a cvg amt calc object version number */
1949 function get_cvg_amt_calc_mthd_ovn
1950 ( p_business_group_id in number,
1951 p_cvg_amt_calc in varchar2,
1952 p_effective_date in date
1953 ) return number is
1954 l_ovn number;
1955 begin
1956 select ccm.object_version_number
1957 into l_ovn
1958 from ben_cvg_amt_calc_mthd_f ccm
1959 where ccm.name = p_cvg_amt_calc
1960 and ccm.business_group_id + 0 = p_business_group_id
1961 and p_effective_date between
1962 ccm.effective_start_date and ccm.effective_end_date;
1963 return(l_ovn);
1964 exception
1965 when others then
1966 hr_data_pump.fail('get_cvg_amt_calc_mthd_ovn', sqlerrm, p_business_group_id, p_cvg_amt_calc, p_effective_date);
1967 raise;
1968 end get_cvg_amt_calc_mthd_ovn;
1969
1970 /* returns an opt_id */
1971 function get_opt_id
1972 ( p_business_group_id in number,
1973 p_option_definition in varchar2,
1974 p_effective_date in date
1975 ) return number is
1976 l_opt_id number;
1977 begin
1978 select opt.opt_id
1979 into l_opt_id
1980 from ben_opt_f opt
1981 where opt.name = p_option_definition
1982 and opt.business_group_id + 0 = p_business_group_id
1983 and p_effective_date between
1984 opt.effective_start_date and opt.effective_end_date;
1985 return(l_opt_id);
1986 exception
1987 when others then
1988 hr_data_pump.fail('get_opt_id', sqlerrm, p_business_group_id, p_option_definition, p_effective_date);
1989 raise;
1990 end get_opt_id;
1991
1992 /* returns an option definition object version number */
1993 function get_opt_ovn
1994 ( p_business_group_id in number,
1995 p_option_definition in varchar2,
1996 p_effective_date in date
1997 ) return number is
1998 l_ovn number;
1999 begin
2000 select opt.object_version_number
2001 into l_ovn
2002 from ben_opt_f opt
2003 where opt.name = p_option_definition
2004 and opt.business_group_id + 0 = p_business_group_id
2005 and p_effective_date between
2006 opt.effective_start_date and opt.effective_end_date;
2007 return(l_ovn);
2008 exception
2009 when others then
2010 hr_data_pump.fail('get_opt_ovn', sqlerrm, p_business_group_id, p_option_definition, p_effective_date);
2011 raise;
2012 end get_opt_ovn;
2013
2014 /* returns a pl_id */
2015 function get_pl_id
2016 ( p_business_group_id in number,
2017 p_plan in varchar2,
2018 p_effective_date in date
2019 ) return number is
2020 l_pl_id number;
2021 begin
2022 select pln.pl_id
2023 into l_pl_id
2024 from ben_pl_f pln
2025 where pln.name = p_plan
2026 and pln.business_group_id + 0 = p_business_group_id
2027 and p_effective_date between
2028 pln.effective_start_date and pln.effective_end_date;
2029 return(l_pl_id);
2030 exception
2031 when others then
2032 hr_data_pump.fail('get_pl_id', sqlerrm, p_business_group_id, p_plan, p_effective_date);
2033 raise;
2034 end get_pl_id;
2035
2036 /* returns a plan object version number */
2037 function get_pl_ovn
2038 ( p_business_group_id in number,
2039 p_plan in varchar2,
2040 p_effective_date in date
2041 ) return number is
2042 l_ovn number;
2043 begin
2044 select pln.object_version_number
2045 into l_ovn
2046 from ben_pl_f pln
2047 where pln.name = p_plan
2048 and pln.business_group_id + 0 = p_business_group_id
2049 and p_effective_date between
2050 pln.effective_start_date and pln.effective_end_date;
2051 return(l_ovn);
2052 exception
2053 when others then
2054 hr_data_pump.fail('get_pl_ovn', sqlerrm, p_business_group_id, p_plan, p_effective_date);
2055 raise;
2056 end get_pl_ovn;
2057
2058 /* returns a pgm_id */
2059 function get_pgm_id
2060 ( p_business_group_id in number,
2061 p_program in varchar2,
2062 p_effective_date in date
2063 ) return number is
2064 l_pgm_id number;
2065 begin
2066 select pgm.pgm_id
2067 into l_pgm_id
2068 from ben_pgm_f pgm
2069 where pgm.name = p_program
2070 and pgm.business_group_id + 0 = p_business_group_id
2071 and p_effective_date between
2072 pgm.effective_start_date and pgm.effective_end_date;
2073 return(l_pgm_id);
2074 exception
2075 when others then
2076 hr_data_pump.fail('get_pgm_id', sqlerrm, p_business_group_id, p_program, p_effective_date);
2077 raise;
2078 end get_pgm_id;
2079
2080 /* returns a program object version number */
2081 function get_pgm_ovn
2082 ( p_business_group_id in number,
2083 p_program in varchar2,
2084 p_effective_date in date
2085 ) return number is
2086 l_ovn number;
2087 begin
2088 select pgm.object_version_number
2089 into l_ovn
2090 from ben_pgm_f pgm
2091 where pgm.name = p_program
2092 and pgm.business_group_id + 0 = p_business_group_id
2093 and p_effective_date between
2094 pgm.effective_start_date and pgm.effective_end_date;
2095 return(l_ovn);
2096 exception
2097 when others then
2098 hr_data_pump.fail('get_pgm_ovn', sqlerrm, p_business_group_id, p_program, p_effective_date);
2099 raise;
2100 end get_pgm_ovn;
2101
2102 /** start of NAME lookups for PER/PAY/HR tables **/
2103
2104 /* returns an element_type_id */
2105 function get_element_type_id
2106 ( p_business_group_id in number,
2107 --p_legislation_code in varchar2,
2108 p_element_name in varchar2,
2109 p_effective_date in date
2110 ) return number is
2111 l_element_type_id number;
2112 begin
2113 select pet.element_type_id
2114 into l_element_type_id
2115 from pay_element_types_f pet
2116 where pet.element_name = p_element_name
2117 and business_group_id + 0 = p_business_group_id
2118 and p_effective_date between
2119 pet.effective_start_date and pet.effective_end_date;
2120 return(l_element_type_id);
2121 exception
2122 when others then
2123 hr_data_pump.fail('get_element_type_id', sqlerrm, p_business_group_id, p_element_name);
2124 raise;
2125 end get_element_type_id;
2126
2127 /** end of NAME additions **/
2128
2129 /* returns currency_code from fnd_currencies_vl */
2130 function get_currency_code
2131 ( p_name_or_code in varchar2,
2132 p_effective_date in date
2133 ) return varchar2 is
2134 l_code fnd_currencies_vl.currency_code%type;
2135 begin
2136 --hr_data_pump.message('**bp** in hr_pump_get.get_cur_code: '|| p_name_or_code);
2137 --
2138 begin
2139 if p_name_or_code is null or p_name_or_code = hr_api.g_varchar2
2140 then
2141 --
2142 -- Defaulted values will be returned unchanged.
2143 --
2144 l_code := p_name_or_code;
2145 else
2146 --
2147 -- Check name
2148 --
2149 select currency_code
2150 into l_code
2151 from fnd_currencies_vl
2152 where name = p_name_or_code
2153 and enabled_flag = 'Y'
2154 and p_effective_date between
2155 nvl(start_date_active, START_OF_TIME) and nvl(end_date_active, END_OF_TIME);
2156 end if;
2157 --
2158 --hr_data_pump.message('**bp** out hr_pump_get.get_cur_code: '||l_code);
2159 return(l_code);
2160 exception
2161 when no_data_found then
2162 --
2163 -- If the name could not be matched, check if the code was used.
2164 --
2165 select currency_code
2166 into l_code
2167 from fnd_currencies -- user underlying table for faster code lookup
2168 where currency_code = p_name_or_code
2169 and enabled_flag = 'Y'
2170 and p_effective_date between
2171 nvl(start_date_active, START_OF_TIME) and nvl(end_date_active, END_OF_TIME);
2172 --
2173 --hr_data_pump.message('**bp** out hr_pump_get.get_cur_code: '||l_code);
2174 return(l_code);
2175 when others then
2176 raise;
2177 end;
2178 --
2179 exception
2180 --
2181 when no_data_found then
2182 hr_data_pump.fail('get_currency_code', sqlerrm, p_name_or_code);
2183 raise value_error;
2184 when others then
2185 hr_data_pump.fail('get_currency_code', sqlerrm, p_name_or_code);
2186 raise;
2187 end get_currency_code;
2188
2189 /* start HR/PER additional get_xyz routines for BEN */
2190
2191 /* returns a uom code */
2192 function get_uom_code
2193 ( p_uom in varchar2,
2194 p_effective_date in date
2195 ) return varchar2 is
2196 begin
2197 return(get_currency_code( p_uom, p_effective_date ));
2198 exception
2199 when others then
2200 hr_data_pump.fail('get_uom_code', sqlerrm, p_uom);
2201 raise;
2202 end get_uom_code;
2203
2204 /* returns a std_prem_uom code */
2205 function get_std_prem_uom_code
2206 ( p_std_prem_uom in varchar2,
2207 p_effective_date in date
2208 ) return varchar2 is
2209 begin
2210 return(get_currency_code( p_std_prem_uom, p_effective_date ));
2211 exception
2212 when others then
2213 hr_data_pump.fail('get_std_prem_uom_code', sqlerrm, p_std_prem_uom);
2214 raise;
2215 end get_std_prem_uom_code;
2216
2217 /* returns a comp_ref_uom code */
2218 function get_comp_ref_uom_code
2219 ( p_comp_ref_uom in varchar2,
2220 p_effective_date in date
2221 ) return varchar2 is
2222 begin
2223 return(get_currency_code( p_comp_ref_uom, p_effective_date ));
2224 exception
2225 when others then
2226 hr_data_pump.fail('get_comp_ref_uom_code', sqlerrm, p_comp_ref_uom);
2227 raise;
2228 end get_comp_ref_uom_code;
2229
2230 /* returns a rt_comp_ref_uom code */
2231 function get_rt_comp_ref_uom_code
2232 ( p_rt_comp_ref_uom in varchar2,
2233 p_effective_date in date
2234 ) return varchar2 is
2235 begin
2236 return(get_currency_code( p_rt_comp_ref_uom, p_effective_date ));
2237 exception
2238 when others then
2239 hr_data_pump.fail('get_rt_comp_ref_uom_code', sqlerrm, p_rt_comp_ref_uom);
2240 raise;
2241 end get_rt_comp_ref_uom_code;
2242
2243 /* returns a amt_dsgd_uom code */
2244 function get_amt_dsgd_uom_code
2245 ( p_amt_dsgd_uom in varchar2,
2246 p_effective_date in date
2247 ) return varchar2 is
2248 begin
2249 return(get_currency_code( p_amt_dsgd_uom, p_effective_date ));
2250 exception
2251 when others then
2252 hr_data_pump.fail('get_amt_dsgd_uom_code', sqlerrm, p_amt_dsgd_uom);
2253 raise;
2254 end get_amt_dsgd_uom_code;
2255
2256 /* get_quald_bnf_person_id - requires user key */
2257 function get_quald_bnf_person_id
2258 (
2259 p_quald_bnf_person_user_key in varchar2
2260 ) return number is
2261 l_person_id number;
2262 begin
2263 if p_quald_bnf_person_user_key is null then
2264 return null;
2265 end if;
2266 l_person_id := user_key_to_id( p_quald_bnf_person_user_key );
2267 return(l_person_id);
2268 exception
2269 when others then
2270 hr_data_pump.fail('get_quald_bnf_person_id', sqlerrm, p_quald_bnf_person_user_key);
2271 raise;
2272 end get_quald_bnf_person_id;
2273
2274 /* get_cvrd_emp_person_id - requires user key */
2275 function get_cvrd_emp_person_id
2276 (
2277 p_cvrd_emp_person_user_key in varchar2
2278 ) return number is
2279 l_person_id number;
2280 begin
2281 if p_cvrd_emp_person_user_key is null then
2282 return null;
2283 end if;
2284 l_person_id := user_key_to_id( p_cvrd_emp_person_user_key );
2285 return(l_person_id);
2286 exception
2287 when others then
2288 hr_data_pump.fail('get_cvrd_emp_person_id', sqlerrm, p_cvrd_emp_person_user_key);
2289 raise;
2290 end get_cvrd_emp_person_id;
2291
2292 /* get_dpnt_person_id - requires user key */
2293 function get_dpnt_person_id
2294 (
2295 p_dpnt_person_user_key in varchar2
2296 ) return number is
2297 l_person_id number;
2298 begin
2299 if p_dpnt_person_user_key is null then
2300 return null;
2301 end if;
2302 l_person_id := user_key_to_id( p_dpnt_person_user_key );
2303 return(l_person_id);
2304 exception
2305 when others then
2306 hr_data_pump.fail('get_dpnt_person_id', sqlerrm, p_dpnt_person_user_key);
2307 raise;
2308 end get_dpnt_person_id;
2309
2310 /* get_bnf_person_id - requires user key */
2311 function get_bnf_person_id
2312 (
2313 p_bnf_person_user_key in varchar2
2314 ) return number is
2315 l_person_id number;
2316 begin
2317 if p_bnf_person_user_key is null then
2318 return null;
2319 end if;
2320 l_person_id := user_key_to_id( p_bnf_person_user_key );
2321 return(l_person_id);
2322 exception
2323 when others then
2324 hr_data_pump.fail('get_bnf_person_id', sqlerrm, p_bnf_person_user_key);
2325 raise;
2326 end get_bnf_person_id;
2327
2328 /* get_ttee_person_id - requires user key */
2329 function get_ttee_person_id
2330 (
2331 p_ttee_person_user_key in varchar2
2332 ) return number is
2333 l_person_id number;
2334 begin
2335 if p_ttee_person_user_key is null then
2336 return null;
2337 end if;
2338 l_person_id := user_key_to_id( p_ttee_person_user_key );
2339 return(l_person_id);
2340 exception
2341 when others then
2342 hr_data_pump.fail('get_ttee_person_id', sqlerrm, p_ttee_person_user_key);
2343 raise;
2344 end get_ttee_person_id;
2345
2346 /****** end OAB additions ******/
2347
2348 /* get_person_id - requires user key */
2349 function get_person_id
2350 (
2351 p_person_user_key in varchar2
2352 ) return number is
2353 l_person_id number;
2354 begin
2355 l_person_id := user_key_to_id( p_person_user_key );
2356 return(l_person_id);
2357 exception
2358 when others then
2359 hr_data_pump.fail('get_person_id', sqlerrm, p_person_user_key);
2360 raise;
2361 end get_person_id;
2362
2363 /* return person_id for contact person. */
2364 function get_contact_person_id
2365 (
2366 p_contact_person_user_key in varchar2
2367 ) return number is
2368 l_contact_person_id number;
2369 begin
2370 l_contact_person_id := get_person_id(p_contact_person_user_key);
2371 return(l_contact_person_id);
2372 exception
2373 when others then
2374 hr_data_pump.fail('get_contact_person_id', sqlerrm,
2375 p_contact_person_user_key);
2376 raise;
2377 end get_contact_person_id;
2378
2379 /* return assignment_id - requires user key */
2380 function get_assignment_id
2381 (
2382 p_assignment_user_key in varchar2
2383 ) return number is
2384 l_assignment_id number;
2385 begin
2386 l_assignment_id := user_key_to_id( p_assignment_user_key );
2387 return(l_assignment_id);
2388 exception
2389 when others then
2390 hr_data_pump.fail('get_assignment_id', sqlerrm, p_assignment_user_key);
2391 raise;
2392 end get_assignment_id;
2393
2394 /* return address_id - requires user key */
2395 function get_address_id
2396 (
2397 p_address_user_key in varchar2
2398 ) return number is
2399 l_address_id number;
2400 begin
2401 l_address_id := user_key_to_id( p_address_user_key );
2402 return(l_address_id);
2403 exception
2404 when others then
2405 hr_data_pump.fail('get_address_id', sqlerrm, p_address_user_key);
2406 raise;
2407 end get_address_id;
2408
2409 /* return supervisor person_id - requires user key */
2410 function get_supervisor_id
2411 (
2412 p_supervisor_user_key in varchar2
2413 ) return number is
2414 l_supervisor_id number;
2415 begin
2416 l_supervisor_id := get_person_id( p_supervisor_user_key );
2417 return(l_supervisor_id);
2418 exception
2419 when others then
2420 hr_data_pump.fail('get_supervisor_id', sqlerrm, p_supervisor_user_key);
2421 raise;
2422 end get_supervisor_id;
2423
2424 /* return recruiter person_id - requires user key */
2425 function get_recruiter_id
2426 (
2427 p_recruiter_user_key in varchar2
2428 ) return number is
2429 l_recruiter_id number;
2430 begin
2431 l_recruiter_id := get_person_id( p_recruiter_user_key );
2432 return(l_recruiter_id);
2433 exception
2434 when others then
2435 hr_data_pump.fail('get_recruiter_id', sqlerrm, p_recruiter_user_key);
2436 raise;
2437 end get_recruiter_id;
2438
2439 /* return person_referred_by_id - requires user key */
2440 function get_person_referred_by_id
2441 (
2442 p_person_referred_by_user_key in varchar2
2443 ) return number is
2444 l_person_referred_by_id number;
2445 begin
2446 l_person_referred_by_id := get_person_id( p_person_referred_by_user_key );
2447 return(l_person_referred_by_id);
2448 exception
2449 when others then
2450 hr_data_pump.fail('get_person_referred_by_id', sqlerrm,
2451 p_person_referred_by_user_key);
2452 raise;
2453 end get_person_referred_by_id;
2454
2455 /* return person_id for timecard approver. */
2456 function get_timecard_approver_id
2457 (
2458 p_timecard_approver_user_key in varchar2
2459 ) return number is
2460 l_timecard_approver_id number;
2461 begin
2462 l_timecard_approver_id := get_person_id(p_timecard_approver_user_key);
2463 return(l_timecard_approver_id);
2464 exception
2465 when others then
2466 hr_data_pump.fail('get_timecard_approver_id', sqlerrm,
2467 p_timecard_approver_user_key);
2468 raise;
2469 end get_timecard_approver_id;
2470
2471 /* returns contact_relationship_id */
2472 function get_contact_relationship_id
2473 (
2474 p_contact_user_key in varchar2,
2475 p_contactee_user_key in varchar2
2476 ) return number is
2477 l_contact_relationship_id number;
2478 begin
2479 select pcr.contact_relationship_id
2480 into l_contact_relationship_id
2481 from per_contact_relationships pcr,
2482 hr_pump_batch_line_user_keys contact_key,
2483 hr_pump_batch_line_user_keys contactee_key
2484 where contact_key.user_key_value = p_contact_user_key
2485 and pcr.contact_person_id = contact_key.unique_key_id
2486 and contactee_key.user_key_value = p_contactee_user_key
2487 and pcr.person_id = contactee_key.unique_key_id;
2488 return(l_contact_relationship_id);
2489 exception
2490 when others then
2491 hr_data_pump.fail('get_contact_relationship_id', sqlerrm,
2492 p_contact_user_key, p_contactee_user_key );
2493 raise;
2494 end get_contact_relationship_id;
2495
2496 /* return person_type_id */
2497 function get_person_type_id
2498 (
2499 p_user_person_type in varchar2,
2500 p_business_group_id in number,
2501 p_language_code in varchar2
2502 ) return number is
2503 l_person_type_id number;
2504 begin
2505 select typ.person_type_id
2506 into l_person_type_id
2507 from per_person_types_tl typtl,
2508 per_person_types typ
2509 where typtl.user_person_type = p_user_person_type
2510 and typ.business_group_id + 0 = p_business_group_id
2511 and typ.person_type_id = typtl.person_type_id
2512 and typtl.LANGUAGE = p_language_code;
2513 return(l_person_type_id);
2514 exception
2515 when others then
2516 hr_data_pump.fail('get_person_type_id', sqlerrm, p_user_person_type,
2517 p_business_group_id, p_language_code);
2518 raise;
2519 end get_person_type_id;
2520
2521 /* Bug 9480965: Added new function for the newly added paramter 'p_original_person_type_id'
2522 to the procedure 'BEN_ASSIGNMENT_API.CREATE_BEN_ASG' */
2523 /* return person_type_id */
2524 function get_original_person_type_id
2525 (
2526 p_user_person_type in varchar2,
2527 p_business_group_id in number,
2528 p_language_code in varchar2
2529 ) return number is
2530 l_person_type_id number;
2531 begin
2532 select typ.person_type_id
2533 into l_person_type_id
2534 from per_person_types_tl typtl,
2535 per_person_types typ
2536 where typtl.user_person_type = p_user_person_type
2537 and typ.business_group_id + 0 = p_business_group_id
2538 and typ.person_type_id = typtl.person_type_id
2539 and typtl.LANGUAGE = p_language_code;
2540 return(l_person_type_id);
2541 exception
2542 when others then
2543 hr_data_pump.fail('get_original_person_type_id', sqlerrm, p_user_person_type,
2544 p_business_group_id, p_language_code);
2545 raise;
2546 end get_original_person_type_id;
2547
2548 /* returns a vendor_id */
2549 function get_vendor_id
2550 (
2551 p_vendor_name in varchar2
2552 ) return number is
2553 l_vendor_id number;
2554 begin
2555 select pov.vendor_id
2556 into l_vendor_id
2557 from po_vendors pov
2558 where pov.vendor_name = p_vendor_name;
2559 return(l_vendor_id);
2560 exception
2561 when others then
2562 hr_data_pump.fail('get_vendor_id', sqlerrm, p_vendor_name);
2563 raise;
2564 end get_vendor_id;
2565
2566 /* returns an assignment_status_type_id */
2567 function get_assignment_status_type_id
2568 (
2569 p_user_status in varchar2,
2570 p_business_group_id in number,
2571 p_language_code in varchar2
2572 ) return number is
2573 l_assignment_status_type_id number;
2574 begin
2575 -- Need to check for amended row in per_ass_status_type_amends first.
2576 begin
2577 select typ.assignment_status_type_id
2578 into l_assignment_status_type_id
2579 from per_ass_status_type_amends_tl typtl,
2580 per_ass_status_type_amends typ
2581 where typtl.user_status = p_user_status
2582 and typ.business_group_id + 0 = p_business_group_id
2583 and typ.ass_status_type_amend_id = typtl.ass_status_type_amend_id
2584 and typtl.LANGUAGE = p_language_code;
2585 return(l_assignment_status_type_id);
2586 exception
2587 when no_data_found then
2588 null;
2589 when others then
2590 raise;
2591 end;
2592
2593 -- Can look in per_assignment_status_types now.
2594 select typ.assignment_status_type_id
2595 into l_assignment_status_type_id
2596 from per_assignment_status_types_tl typtl,
2597 per_assignment_status_types typ
2598 where typtl.user_status = p_user_status
2599 and typ.assignment_status_type_id = typtl.assignment_status_type_id
2600 and typtl.LANGUAGE = p_language_code
2601 and
2602 (
2603 (typ.business_group_id is null and typ.legislation_code is null)
2604 or
2605 (typ.business_group_id is not null
2606 and typ.business_group_id + 0 = p_business_group_id)
2607 or
2608 (typ.business_group_id is null
2609 and typ.legislation_code is not null
2610 and typ.legislation_code =
2611 (select legislation_code from per_business_groups
2612 where business_group_id = p_business_group_id))
2613 );
2614 return(l_assignment_status_type_id);
2615 exception
2616 when others then
2617 hr_data_pump.fail('get_assignment_status_type_id', sqlerrm, p_user_status,
2618 p_business_group_id, p_language_code);
2619 raise;
2620 end get_assignment_status_type_id;
2621
2622 /* returns an organization_id */
2623 function get_organization_id
2624 (
2625 p_organization_name in varchar2,
2626 p_business_group_id in number,
2627 p_effective_date in date
2628 ,p_language_code in varchar2
2629 ) return number is
2630 l_organization_id number;
2631 begin
2632 select org.organization_id
2633 into l_organization_id
2634 from hr_all_organization_units org
2635 , hr_all_organization_units_tl orgtl
2636 where orgtl.name = p_organization_name
2637 and orgtl.language = p_language_code
2638 and org.organization_id = orgtl.organization_id
2639 and org.business_group_id + 0 = p_business_group_id;
2640 return(l_organization_id);
2641 exception
2642 when others then
2643 hr_data_pump.fail('get_organization_id', sqlerrm, p_organization_name,
2644 p_business_group_id, p_effective_date, p_language_code);
2645 raise;
2646 end get_organization_id;
2647
2648 /* returns a establishment_org_id */
2649 function get_establishment_org_id
2650 (
2651 p_establishment_org_name in varchar2,
2652 p_business_group_id in number,
2653 p_effective_date in date
2654 , p_language_code in varchar2
2655 ) return number is
2656 l_establishment_org_id number;
2657 begin
2658 l_establishment_org_id :=
2659 get_organization_id( p_establishment_org_name, p_business_group_id,
2660 p_effective_date, p_language_code );
2661 return(l_establishment_org_id);
2662 exception
2663 when others then
2664 hr_data_pump.fail('get_establishment_org_id', sqlerrm,
2665 p_establishment_org_name, p_business_group_id,
2666 p_effective_date, p_language_code);
2667 raise;
2668 end get_establishment_org_id;
2669
2670 /* returns a source_organization_id */
2671 function get_source_organization_id
2672 (
2673 p_source_organization_name in varchar2,
2674 p_business_group_id in number,
2675 p_effective_date in date
2676 , p_language_code in varchar2
2677 ) return number is
2678 l_source_organization_id number;
2679 begin
2680 l_source_organization_id :=
2681 get_organization_id( p_source_organization_name, p_business_group_id,
2682 p_effective_date, p_language_code );
2683 return(l_source_organization_id);
2684 exception
2685 when others then
2686 hr_data_pump.fail('get_source_organization_id', sqlerrm,
2687 p_source_organization_name, p_business_group_id,
2688 p_effective_date, p_language_code);
2689 raise;
2690 end get_source_organization_id;
2691
2692 /* returns a grade_id */
2693 function get_grade_id
2694 (
2695 p_grade_name in varchar2,
2696 p_business_group_id in number,
2697 p_effective_date in date
2698 ) return number is
2699 l_grade_id number;
2700 begin
2701 -- Note that the grade name can be null on the
2702 -- per_grades table, but I think grades are created
2703 -- with a name - otherwise identifying them would be
2704 -- rather difficult...
2705 select gra.grade_id
2706 into l_grade_id
2707 from per_grades_vl gra
2708 where gra.name = p_grade_name
2709 and gra.business_group_id + 0 = p_business_group_id;
2710 return(l_grade_id);
2711 exception
2712 when others then
2713 hr_data_pump.fail('get_grade_id', sqlerrm, p_grade_name, p_business_group_id, p_effective_date);
2714 raise;
2715 end get_grade_id;
2716
2717 /* returns a grade_id */
2718 function get_entry_grade_id
2719 (
2720 p_entry_grade_name in varchar2,
2721 p_business_group_id in number,
2722 p_effective_date in date
2723 ) return number is
2724 l_entry_grade_id number;
2725 begin
2726 l_entry_grade_id :=
2727 get_grade_id
2728 (p_grade_name => p_entry_grade_name
2729 ,p_business_group_id => p_business_group_id
2730 ,p_effective_date => p_effective_date
2731 );
2732 return(l_entry_grade_id);
2733 exception
2734 when others then
2735 raise;
2736 end get_entry_grade_id;
2737
2738 /* return availability_status_id */
2739 function get_availability_status_id
2740 (p_shared_type_name in varchar2
2741 ,p_system_type_cd in varchar2
2742 ,p_business_group_id in number
2743 ,p_language_code in varchar2
2744 ) return number is
2745 cursor csr_lookup is
2746 select pst.shared_type_id
2747 from per_shared_types pst
2748 , per_shared_types_tl psttl
2749 where psttl.shared_type_name = p_shared_type_name
2750 and psttl.language = p_language_code
2751 and pst.shared_type_id = psttl.shared_type_id
2752 and pst.lookup_type = 'POSITION_AVAILABILITY_STATUS'
2753 and pst.system_type_cd = p_system_type_cd
2754 and nvl(pst.business_group_id, p_business_group_id) = p_business_group_id;
2755 --
2756 v_shared_type_id number(15) := null;
2757 begin
2758 if p_shared_type_name is not null then
2759 open csr_lookup;
2760 fetch csr_lookup into v_shared_type_id;
2761 close csr_lookup;
2762 end if;
2763 return v_shared_type_id;
2764 exception
2765 when others then
2766 hr_data_pump.fail('get_availability_status_id', sqlerrm,
2767 p_shared_type_name, p_system_type_cd,
2768 p_business_group_id, p_language_code);
2769 raise;
2770 end get_availability_status_id;
2771
2772 /* returns a position_id */
2773 function get_position_id
2774 (
2775 p_position_name in varchar2,
2776 p_business_group_id in number,
2777 p_effective_date in date
2778 ) return number is
2779 l_position_id number;
2780 begin
2781 select pos.position_id
2782 into l_position_id
2783 from hr_all_positions_f pos
2784 where pos.name = p_position_name
2785 and pos.business_group_id + 0 = p_business_group_id
2786 and p_effective_date between
2787 pos.effective_start_date and pos.effective_end_date;
2788 return(l_position_id);
2789 exception
2790 when others then
2791 hr_data_pump.fail('get_position_id', sqlerrm, p_position_name,
2792 p_business_group_id, p_effective_date);
2793 raise;
2794 end get_position_id;
2795
2796 /* returns a successor_position_id */
2797 function get_successor_position_id
2798 (
2799 p_successor_position_name in varchar2,
2800 p_business_group_id in number,
2801 p_effective_date in date
2802 ) return number is
2803 l_pos_id number;
2804 begin
2805 -- Just call the get_position_id function.
2806 l_pos_id := get_position_id(p_successor_position_name,
2807 p_business_group_id,
2808 p_effective_date);
2809 return(l_pos_id);
2810 exception
2811 when others then
2812 hr_data_pump.fail('get_successor_position_id', sqlerrm,
2813 p_successor_position_name, p_business_group_id,
2814 p_effective_date);
2815 raise;
2816 end get_successor_position_id;
2817
2818 /* returns a relief_position_id */
2819 function get_relief_position_id
2820 (
2821 p_relief_position_name in varchar2,
2822 p_business_group_id in number,
2823 p_effective_date in date
2824 ) return number is
2825 l_pos_id number;
2826 begin
2827 -- Just call the get_position_id function.
2828 l_pos_id := get_position_id(p_relief_position_name,
2829 p_business_group_id,
2830 p_effective_date);
2831 return(l_pos_id);
2832 exception
2833 when others then
2834 hr_data_pump.fail('get_relief_position_id', sqlerrm,
2835 p_relief_position_name, p_business_group_id,
2836 p_effective_date);
2837 raise;
2838 end get_relief_position_id;
2839
2840 /* returns a prior_position_id */
2841 function get_prior_position_id
2842 (
2843 p_prior_position_name in varchar2,
2844 p_business_group_id in number,
2845 p_effective_date in date
2846 ) return number is
2847 l_pos_id number;
2848 begin
2849 -- Just call the get_position_id function.
2850 l_pos_id := get_position_id(p_prior_position_name,
2851 p_business_group_id,
2852 p_effective_date);
2853 return(l_pos_id);
2854 exception
2855 when others then
2856 hr_data_pump.fail('get_prior_position_id', sqlerrm,
2857 p_prior_position_name, p_business_group_id,
2858 p_effective_date);
2859 raise;
2860 end get_prior_position_id;
2861
2862 /* returns a supervisor_position_id */
2863 function get_supervisor_position_id
2864 (
2865 p_supervisor_position_name in varchar2,
2866 p_business_group_id in number,
2867 p_effective_date in date
2868 ) return number is
2869 l_pos_id number;
2870 begin
2871 -- Just call the get_position_id function.
2872 l_pos_id := get_position_id(p_supervisor_position_name,
2873 p_business_group_id,
2874 p_effective_date);
2875 return(l_pos_id);
2876 exception
2877 when others then
2878 hr_data_pump.fail('get_supervisor_position_id', sqlerrm,
2879 p_supervisor_position_name, p_business_group_id,
2880 p_effective_date);
2881 raise;
2882 end get_supervisor_position_id;
2883
2884 /* returns a job_id */
2885 function get_job_id
2886 (
2887 p_job_name in varchar2,
2888 p_effective_date in date,
2889 p_business_group_id in number
2890 ) return number is
2891 l_job_id number;
2892 begin
2893 select job.job_id
2894 into l_job_id
2895 from per_jobs_vl job
2896 where job.name = p_job_name
2897 and job.business_group_id + 0 = p_business_group_id;
2898 return(l_job_id);
2899 exception
2900 when others then
2901 hr_data_pump.fail('get_job_id', sqlerrm,
2902 p_job_name, p_effective_date, p_business_group_id);
2903 raise;
2904 end get_job_id;
2905
2906 /* returns a payroll_id */
2907 function get_payroll_id
2908 (
2909 p_payroll_name in varchar2,
2910 p_business_group_id in number,
2911 p_effective_date in date
2912 ) return number is
2913 l_payroll_id number;
2914 begin
2915 select pay.payroll_id
2916 into l_payroll_id
2917 from pay_payrolls_f pay
2918 where pay.payroll_name = p_payroll_name
2919 and pay.business_group_id + 0 = p_business_group_id
2920 and p_effective_date between
2921 pay.effective_start_date and pay.effective_end_date;
2922 return(l_payroll_id);
2923 exception
2924 when others then
2925 hr_data_pump.fail('get_payroll_id', sqlerrm, p_payroll_name,
2926 p_business_group_id, p_effective_date);
2927 raise;
2928 end get_payroll_id;
2929
2930 /* returns a pay_freq_payroll_id */
2931 function get_pay_freq_payroll_id
2932 (
2933 p_pay_freq_payroll_name in varchar2,
2934 p_business_group_id in number,
2935 p_effective_date in date
2936 ) return number is
2937 begin
2938 return
2939 get_payroll_id
2940 (p_payroll_name => p_pay_freq_payroll_name
2941 ,p_business_group_id => p_business_group_id
2942 ,p_effective_date => p_effective_date
2943 );
2944 exception
2945 when others then
2946 raise;
2947 end get_pay_freq_payroll_id;
2948
2949 /* Returns a location_id for the update_location APIs. */
2950 function get_location_id_update
2951 (
2952 p_existing_location_code in varchar2,
2953 p_business_group_id in number,
2954 p_language_code in varchar2
2955 ) return number is
2956 l_location_id number;
2957 begin
2958 l_location_id :=
2959 get_location_id(p_existing_location_code, p_business_group_id,
2960 p_language_code);
2961 return(l_location_id);
2962 exception
2963 when others then
2964 hr_data_pump.fail('get_location_id_update', sqlerrm,
2965 p_existing_location_code,
2966 p_business_group_id, p_language_code);
2967 raise;
2968 end get_location_id_update;
2969
2970 /* returns a location_id */
2971 function get_location_id
2972 (
2973 p_location_code in varchar2,
2974 p_business_group_id in number,
2975 p_language_code in varchar2
2976 ) return number is
2977 l_location_id number;
2978 begin
2979 select loc.location_id
2980 into l_location_id
2981 from hr_locations_all loc,
2982 hr_locations_all_tl lot
2983 where lot.location_code = p_location_code
2984 and lot.language = p_language_code
2985 and lot.location_id = loc.location_id
2986 and (loc.business_group_id is null or
2987 loc.business_group_id + 0 = p_business_group_id);
2988 return(l_location_id);
2989 exception
2990 when others then
2991 hr_data_pump.fail('get_location_id', sqlerrm, p_location_code,
2992 p_business_group_id, p_language_code);
2993 raise;
2994 end get_location_id;
2995
2996 /* returns receiver id */
2997 function get_designated_receiver_id
2998 (
2999 p_designated_receiver_name Varchar2,
3000 p_business_group_id Number,
3001 p_effective_date Date
3002 ) return number Is
3003 l_designated_receiver_id Number;
3004 begin
3005 Select person_id
3006 Into l_designated_receiver_id
3007 From per_all_people_f
3008 Where employee_number Is Not Null
3009 and business_group_id = p_business_group_id
3010 and p_effective_date between effective_start_date and effective_end_date
3011 and full_name = p_designated_receiver_name;
3012 return l_designated_receiver_id;
3013 Exception
3014 When Others Then
3015 hr_data_pump.fail('get_designated_receiver_id',sqlerrm,p_designated_receiver_name,
3016 p_business_group_id);
3017 raise;
3018 end get_designated_receiver_id;
3019
3020 function get_ship_to_location_id
3021 (
3022 p_ship_to_location_code in varchar2,
3023 p_business_group_id in number,
3024 p_language_code in varchar2
3025 ) return number is
3026 l_location_id number;
3027 begin
3028 select loc.location_id
3029 into l_location_id
3030 from hr_locations_all loc,
3031 hr_locations_all_tl lot
3032 where lot.location_code = p_ship_to_location_code
3033 and lot.language = p_language_code
3034 and lot.location_id = loc.location_id
3035 and (loc.business_group_id is null or
3036 loc.business_group_id + 0 = p_business_group_id);
3037 return(l_location_id);
3038 exception
3039 when others then
3040 hr_data_pump.fail('get_ship_to_location_id', sqlerrm, p_ship_to_location_code,
3041 p_business_group_id, p_language_code);
3042 raise;
3043 end get_ship_to_location_id;
3044
3045 /* returns a pay_basis_id */
3046 function get_pay_basis_id
3047 (
3048 p_pay_basis_name in varchar2,
3049 p_business_group_id in number
3050 ) return number is
3051 l_pay_basis_id number;
3052 begin
3053 select ppb.pay_basis_id
3054 into l_pay_basis_id
3055 from per_pay_bases ppb
3056 where ppb.name = p_pay_basis_name
3057 and ppb.business_group_id + 0 = p_business_group_id;
3058 return(l_pay_basis_id);
3059 exception
3060 when others then
3061 hr_data_pump.fail('get_pay_basis_id', sqlerrm, p_pay_basis_name,
3062 p_business_group_id);
3063 raise;
3064 end get_pay_basis_id;
3065
3066 /* returns a recruitment_activity_id */
3067 function get_recruitment_activity_id
3068 (
3069 p_recruitment_activity_name in varchar2,
3070 p_business_group_id in number,
3071 p_effective_date in date
3072 ) return number is
3073 l_raid number;
3074 begin
3075 select pra.recruitment_activity_id
3076 into l_raid
3077 from per_recruitment_activities pra
3078 where pra.name = p_recruitment_activity_name
3079 and pra.business_group_id + 0 = p_business_group_id
3080 and p_effective_date between
3081 pra.date_start and nvl(pra.date_end, END_OF_TIME);
3082 return(l_raid);
3083 exception
3084 when others then
3085 hr_data_pump.fail('get_recruitment_activity_id', sqlerrm,
3086 p_recruitment_activity_name, p_business_group_id,
3087 p_effective_date);
3088 raise;
3089 end get_recruitment_activity_id;
3090
3091 /* returns a vacancy_id */
3092 function get_vacancy_id
3093 (
3094 p_vacancy_user_key in varchar2
3095 ) return number is
3096 l_vacancy_id number;
3097 begin
3098 l_vacancy_id := user_key_to_id( p_vacancy_user_key );
3099 return(l_vacancy_id);
3100 exception
3101 when others then
3102 hr_data_pump.fail('get_vacancy_id', sqlerrm, p_vacancy_user_key );
3103 raise;
3104 end get_vacancy_id;
3105
3106 /* returns an org_payment_method_id */
3107 function get_org_payment_method_id
3108 (
3109 p_org_payment_method_user_key in varchar2
3110 ) return number is
3111 l_opmid number;
3112 begin
3113 l_opmid := user_key_to_id( p_org_payment_method_user_key );
3114 return(l_opmid);
3115 exception
3116 when others then
3117 hr_data_pump.fail('get_org_payment_method_id', sqlerrm,
3118 p_org_payment_method_user_key );
3119 raise;
3120 end get_org_payment_method_id;
3121
3122 /* returns a payee organization_id */
3123 function get_payee_org_id
3124 (
3125 p_payee_organization_name in varchar2,
3126 p_business_group_id in number,
3127 p_effective_date in date
3128 , p_language_code in varchar2
3129 ) return number is
3130 l_organization_id number;
3131 begin
3132 l_organization_id :=
3133 get_organization_id( p_payee_organization_name, p_business_group_id,
3134 p_effective_date, p_language_code );
3135 return(l_organization_id);
3136 exception
3137 when others then
3138 hr_data_pump.fail('get_payee_org_id', sqlerrm, p_payee_organization_name,
3139 p_business_group_id, p_effective_date, p_language_code);
3140 raise;
3141 end get_payee_org_id;
3142
3143 /* return payee person_id - requires user key */
3144 function get_payee_person_id
3145 (
3146 p_payee_person_user_key in varchar2
3147 ) return number is
3148 l_person_id number;
3149 begin
3150 l_person_id := get_person_id( p_payee_person_user_key );
3151 return(l_person_id);
3152 exception
3153 when others then
3154 hr_data_pump.fail('get_payee_person_id', sqlerrm, p_payee_person_user_key);
3155 raise;
3156 end get_payee_person_id;
3157
3158 /* return payee_id for an organization or person payee. */
3159 function get_payee_id
3160 (
3161 p_data_pump_always_call in varchar2,
3162 p_payee_type in varchar2,
3163 p_business_group_id in number,
3164 p_payee_org in varchar2 default null,
3165 p_payee_person_user_key in varchar2 default null,
3166 p_effective_date in date
3167 , p_language_code in varchar2
3168 ) return number is
3169 l_payee_id number;
3170 begin
3171 --
3172 -- Check for a payee person.
3173 --
3174 if p_payee_type = 'P' and p_payee_person_user_key is not null then
3175 l_payee_id := get_payee_person_id( p_payee_person_user_key );
3176 return(l_payee_id);
3177 --
3178 -- Check for a payee organization.
3179 --
3180 elsif p_payee_type = 'O' and p_payee_org is not null then
3181 l_payee_id :=
3182 get_payee_org_id( p_payee_org, p_business_group_id, p_effective_date,
3183 p_language_code );
3184 return(l_payee_id);
3185 --
3186 -- Everything is NULL so return NULL.
3187 --
3188 elsif p_payee_type is null and p_payee_person_user_key is null and
3189 p_payee_org is null then
3190 return null;
3191 --
3192 -- Everything is HR_API-defaulted, so return HR_API default value.
3193 -- User Keys are set to NULL, if defaulted, on UPDATE.
3194 --
3195 elsif p_payee_type = HR_API_G_VARCHAR2 and
3196 (p_payee_person_user_key is null or p_payee_person_user_key =
3197 HR_API_G_VARCHAR2) and p_payee_org = HR_API_G_VARCHAR2 then
3198 return HR_API_G_NUMBER;
3199 --
3200 -- User has supplied an erroneous combination of arguments.
3201 --
3202 else
3203 raise value_error;
3204 end if;
3205 exception
3206 when others then
3207 hr_data_pump.fail('get_payee_id', sqlerrm, p_payee_type, p_payee_org,
3208 p_payee_person_user_key, p_effective_date, p_language_code);
3209 raise;
3210 end get_payee_id;
3211
3212 /* returns a personal_payment_method_id */
3213 function get_personal_payment_method_id
3214 (
3215 p_personal_pay_method_user_key in varchar2
3216 ) return number is
3217 l_ppmid number;
3218 begin
3219 l_ppmid := user_key_to_id( p_personal_pay_method_user_key );
3220 return(l_ppmid);
3221 exception
3222 when others then
3223 hr_data_pump.fail('get_personal_payment_method_id', sqlerrm,
3224 p_personal_pay_method_user_key);
3225 raise;
3226 end get_personal_payment_method_id;
3227
3228 /* returns a set_of_books_id */
3229 function get_set_of_books_id
3230 (
3231 p_set_of_books_name varchar2
3232 ) return number is
3233 l_id number;
3234 begin
3235 select sob.set_of_books_id
3236 into l_id
3237 from gl_sets_of_books sob
3238 where sob.name = p_set_of_books_name;
3239 return(l_id);
3240 exception
3241 when others then
3242 hr_data_pump.fail('get_set_of_books_id', sqlerrm, p_set_of_books_name);
3243 raise;
3244 end get_set_of_books_id;
3245
3246 /* returns a tax_unit_id */
3247 function get_tax_unit_id
3248 (
3249 p_tax_unit_name in varchar2,
3250 p_effective_date in date
3251 ) return varchar2 is
3252 l_tax_unit_id number;
3253 begin
3254 select tax.tax_unit_id
3255 into l_tax_unit_id
3256 from hr_tax_units_v tax
3257 where tax.name = p_tax_unit_name;
3258 return(l_tax_unit_id);
3259 exception
3260 when others then
3261 hr_data_pump.fail('get_tax_unit_id', sqlerrm, p_tax_unit_name,
3262 p_effective_date);
3263 raise;
3264 end get_tax_unit_id;
3265
3266 /* returns a user_column_id for tax schedule */
3267 function get_work_schedule
3268 (
3269 p_work_schedule in varchar2,
3270 p_organization_name in varchar2,
3271 p_business_group_id in number,
3272 p_effective_date in date
3273 ,p_language_code in varchar2
3274 ) return number is
3275 l_id number;
3276 begin
3277 select puc.user_column_id
3278 into l_id
3279 from pay_user_columns puc,
3280 hr_organization_information hoi,
3281 hr_all_organization_units org
3282 , hr_all_organization_units_tl orgtl
3283 where orgtl.name = p_organization_name
3284 and orgtl.language = p_language_code
3285 and org.organization_id = orgtl.organization_id
3286 and org.business_group_id + 0 = p_business_group_id
3287 and puc.user_column_name = p_work_schedule
3288 and hoi.org_information_context = 'Work Schedule'
3289 and hoi.organization_id = org.organization_id
3290 and (puc.user_table_id = hoi.org_information1 or
3291 hoi.org_information1 is null);
3292 return(l_id);
3293 exception
3294 when others then
3295 hr_data_pump.fail('get_work_schedule', sqlerrm, p_work_schedule,
3296 p_organization_name, p_business_group_id,
3297 p_effective_date, p_language_code);
3298 raise;
3299 end get_work_schedule;
3300
3301 /* returns an establishment_id */
3302 function get_eeo_1_establishment_id
3303 (
3304 p_eeo_1_establishment in varchar2,
3305 p_business_group_id in number,
3306 p_effective_date in date
3307 ) return number is
3308 l_id number;
3309 begin
3310 select est.establishment_id
3311 into l_id
3312 from HR_ESTABLISHMENTS_V est
3313 where est.name = p_eeo_1_establishment
3314 and est.business_group_id + 0 = p_business_group_id;
3315 return(l_id);
3316 exception
3317 when others then
3318 hr_data_pump.fail('get_eeo_1_establishment_id', sqlerrm,
3319 p_eeo_1_establishment, p_business_group_id,
3320 p_effective_date);
3321 raise;
3322 end get_eeo_1_establishment_id;
3323
3324 /* get_program_application_id - standard who column */
3325 function get_program_application_id return number is
3326 begin
3327 return(null);
3328 end get_program_application_id;
3329
3330 /* get_program_id - standard who column */
3331 function get_program_id return number is
3332 begin
3333 return(null);
3334 end get_program_id;
3335
3336 /* get_request_id - standard who column */
3337 function get_request_id return number is
3338 begin
3339 return(null);
3340 end get_request_id;
3341
3342 /* get_creator_id - standard who column */
3343 function get_creator_id return number is
3344 begin
3345 return(null);
3346 end get_creator_id;
3347
3348 /* get_id_flex_num - requires user key */
3349 function get_id_flex_num( p_id_flex_num_user_key in varchar2 )
3350 return number is
3351 l_id_flex_num number;
3352 begin
3353 l_id_flex_num := user_key_to_id( p_id_flex_num_user_key );
3354 return(l_id_flex_num);
3355 exception
3356 when others then
3357 hr_data_pump.fail('get_id_flex_num', sqlerrm, p_id_flex_num_user_key);
3358 raise;
3359 end get_id_flex_num;
3360
3361 /* get_gr_grade_rule_id */
3362 function get_gr_grade_rule_id
3363 (
3364 p_grade_name in varchar2,
3365 p_rate_name in varchar2,
3366 p_business_group_id in number,
3367 p_effective_date in date
3368 )
3369 return number is
3370 l_grade_rule_id number;
3371 begin
3372 select pgr.grade_rule_id
3373 into l_grade_rule_id
3374 from pay_grade_rules_f pgr,
3375 per_grades_vl pg,
3376 pay_rates pr
3377 where pg.name = p_grade_name
3378 and pg.business_group_id + 0 = p_business_group_id
3379 and pr.name = p_rate_name
3380 and pr.business_group_id + 0 = p_business_group_id
3381 and pgr.rate_id = pr.rate_id
3382 and pgr.grade_or_spinal_point_id = pg.grade_id
3383 and pgr.rate_type = 'G'
3384 and pgr.business_group_id + 0 = p_business_group_id
3385 and p_effective_date between pgr.effective_start_date and
3386 pgr.effective_end_date;
3387 return(l_grade_rule_id);
3388 exception
3389 when others then
3390 hr_data_pump.fail('get_gr_grade_rule_id', sqlerrm, p_grade_name,
3391 p_rate_name, p_business_group_id, p_effective_date);
3392 raise;
3393 end get_gr_grade_rule_id;
3394
3395 /* get_pp_grade_rule_id */
3396 function get_pp_grade_rule_id
3397 (
3398 p_progression_point in varchar2,
3399 p_pay_scale in varchar2,
3400 p_rate_name in varchar2,
3401 p_business_group_id in number,
3402 p_effective_date in date
3403 )
3404 return number is
3405 l_grade_rule_id number;
3406 begin
3407 select pgr.grade_rule_id
3408 into l_grade_rule_id
3409 from per_parent_spines pps,
3410 per_spinal_points psp,
3411 pay_grade_rules_f pgr,
3412 pay_rates pr
3413 where pps.name = p_pay_scale
3414 and pps.business_group_id + 0 = p_business_group_id
3415 and psp.spinal_point = p_progression_point
3416 and psp.business_group_id + 0 = p_business_group_id
3417 and psp.parent_spine_id = pps.parent_spine_id
3418 and pr.name = p_rate_name
3419 and pr.business_group_id + 0 = p_business_group_id
3420 and pgr.rate_id = pr.rate_id
3421 and pgr.grade_or_spinal_point_id = psp.spinal_point_id
3422 and pgr.rate_type = 'SP'
3423 and pgr.business_group_id + 0 = p_business_group_id
3424 and p_effective_date between pgr.effective_start_date and
3425 pgr.effective_end_date;
3426 return( l_grade_rule_id );
3427 exception
3428 when others then
3429 hr_data_pump.fail('get_pp_grade_rule_id', sqlerrm, p_progression_point,
3430 p_pay_scale, p_rate_name, p_business_group_id,
3431 p_effective_date);
3432 raise;
3433 end get_pp_grade_rule_id;
3434
3435 /* get_ar_grade_rule_id */
3436 function get_ar_grade_rule_id
3437 (
3438 p_rate_name in varchar2,
3439 p_business_group_id in number,
3440 p_effective_date in date
3441 )
3442 return number is
3443 l_grade_rule_id number;
3444 begin
3445 select pgr.grade_rule_id
3446 into l_grade_rule_id
3447 from pay_grade_rules_f pgr,
3448 pay_rates pr
3449 where pr.name = p_rate_name
3450 and pr.business_group_id + 0 = p_business_group_id
3451 and pgr.rate_id = pr.rate_id
3452 and pgr.rate_type = 'A'
3453 and pgr.business_group_id + 0 = p_business_group_id
3454 and p_effective_date between pgr.effective_start_date and
3455 pgr.effective_end_date;
3456 return(l_grade_rule_id);
3457 exception
3458 when others then
3459 hr_data_pump.fail('get_ar_grade_rule_id', sqlerrm,
3460 p_rate_name, p_business_group_id, p_effective_date);
3461 raise;
3462 end get_ar_grade_rule_id;
3463
3464 function get_organization_structure_id
3465 (
3466 p_name in varchar2,
3467 p_business_group_id in number
3468 )
3469 return number is
3470 l_organization_structure_id number;
3471 begin
3472 select pos.organization_structure_id
3473 into l_organization_structure_id
3474 from per_organization_structures pos
3475 where pos.name = p_name
3476 and pos.business_group_id + 0 = p_business_group_id;
3477 return (l_organization_structure_id);
3478 exception
3479 when others then
3480 hr_data_pump.fail('get_organization_structure_id', sqlerrm, p_name, p_business_group_id);
3481 raise;
3482 end get_organization_structure_id;
3483
3484 function get_org_str_ver_id
3485 (
3486 p_business_group_id in number,
3487 p_organization_structure_id in number,
3488 p_date_from in date,
3489 p_version_number in number
3490 )
3491 return number is
3492 l_org_str_ver_id number;
3493 begin
3494 select osv.org_structure_version_id
3495 into l_org_str_ver_id
3496 from per_org_structure_versions osv
3497 where osv.organization_structure_id = p_organization_structure_id
3498 and osv.date_from = p_date_from
3499 and osv.version_number = p_version_number
3500 and osv.business_group_id + 0 = p_business_group_id;
3501 return (l_org_str_ver_id);
3502 exception
3503 when others then
3504 hr_data_pump.fail('get_org_str_ver_id', sqlerrm, p_business_group_id, p_organization_structure_id, p_date_from, p_version_number);
3505 raise;
3506 end get_org_str_ver_id;
3507
3508 /* get_spinal_point_id */
3509 function get_spinal_point_id
3510 (
3511 p_progression_point in varchar2,
3512 p_pay_scale in varchar2,
3513 p_business_group_id in number,
3514 p_effective_date in date
3515 )
3516 return number is
3517 l_spinal_point_id number;
3518 begin
3519 select psp.spinal_point_id
3520 into l_spinal_point_id
3521 from per_parent_spines pps,
3522 per_spinal_points psp
3523 where pps.name = p_pay_scale
3524 and pps.business_group_id + 0 = p_business_group_id
3525 and psp.spinal_point = p_progression_point
3526 and psp.business_group_id + 0 = p_business_group_id
3527 and psp.parent_spine_id = pps.parent_spine_id;
3528 return( l_spinal_point_id );
3529 exception
3530 when others then
3531 hr_data_pump.fail('get_spinal_point_id', sqlerrm, p_progression_point,
3532 p_pay_scale, p_business_group_id, p_effective_date);
3533 raise;
3534 end get_spinal_point_id;
3535
3536 /* get_at_period_of_service_id */
3537 function get_at_period_of_service_id
3538 (
3539 p_person_user_key in varchar2,
3540 p_business_group_id in number
3541 )
3542 return number is
3543 l_period_of_service_id number;
3544 begin
3545 select pps.period_of_service_id
3546 into l_period_of_service_id
3547 from per_periods_of_service pps,
3548 hr_pump_batch_line_user_keys key
3549 where key.user_key_value = p_person_user_key
3550 and pps.person_id = key.unique_key_id
3551 and pps.business_group_id = p_business_group_id
3552 and pps.actual_termination_date is null;
3553 return(l_period_of_service_id);
3554 exception
3555 when others then
3556 hr_data_pump.fail('get_at_period_of_service_id', sqlerrm,
3557 p_person_user_key, p_business_group_id);
3558 raise;
3559 end get_at_period_of_service_id;
3560
3561 /* get_fp_period_of_service_id */
3562 function get_fp_period_of_service_id
3563 (
3564 p_person_user_key in varchar2,
3565 p_business_group_id in number
3566 )
3567 return number is
3568 l_period_of_service_id number;
3569 begin
3570 select pps.period_of_service_id
3571 into l_period_of_service_id
3572 from per_periods_of_service pps,
3573 hr_pump_batch_line_user_keys key
3574 where key.user_key_value = p_person_user_key
3575 and pps.person_id = key.unique_key_id
3576 and pps.business_group_id = p_business_group_id
3577 and pps.actual_termination_date is not null
3578 and pps.final_process_date is null;
3579 return(l_period_of_service_id);
3580 exception
3581 when others then
3582 hr_data_pump.fail('get_fp_period_of_service_id', sqlerrm, p_person_user_key,
3583 p_business_group_id);
3584 raise;
3585 end get_fp_period_of_service_id;
3586
3587 /* Added for 11i,Rvydyana,02-DEC-1999 */
3588 /* get_ut_period_of_service_id */
3589 function get_ut_period_of_service_id
3590 (
3591 p_person_user_key in varchar2,
3592 p_effective_date in date,
3593 p_business_group_id in number
3594 )
3595 return number is
3596 l_eot constant date := to_date('4712/12/31', 'YYYY/MM/DD');
3597 l_period_of_service_id number;
3598 begin
3599
3600 select pps.period_of_service_id
3601 into l_period_of_service_id
3602 from per_periods_of_service pps,
3603 hr_pump_batch_line_user_keys key
3604 where key.user_key_value = p_person_user_key
3605 and pps.person_id = key.unique_key_id
3606 and pps.business_group_id = p_business_group_id
3607 and p_effective_date between pps.date_start and NVL(pps.actual_termination_date,l_eot);
3608 return(l_period_of_service_id);
3609 exception
3610 when others then
3611 hr_data_pump.fail('get_ut_period_of_service_id', sqlerrm, p_person_user_key,
3612 p_effective_date,p_business_group_id);
3613 raise;
3614 end get_ut_period_of_service_id;
3615
3616 /* get_special_ceiling_step_id */
3617 function get_special_ceiling_step_id
3618 (
3619 p_special_ceilin_step_user_key varchar2
3620 )
3621 return number is
3622 l_special_ceiling_step_id number;
3623 begin
3624 l_special_ceiling_step_id :=
3625 user_key_to_id( p_special_ceilin_step_user_key );
3626 return(l_special_ceiling_step_id);
3627 exception
3628 when others then
3629 hr_data_pump.fail('get_special_ceiling_step_id', sqlerrm,
3630 p_special_ceilin_step_user_key );
3631 raise;
3632 end get_special_ceiling_step_id;
3633
3634 /* get_default_code_comb_id */
3635 function get_default_code_comb_id
3636 (
3637 p_default_code_comb_user_key varchar2
3638 )
3639 return number is
3640 l_default_code_comb_id number;
3641 begin
3642 l_default_code_comb_id := user_key_to_id( p_default_code_comb_user_key );
3643 return(l_default_code_comb_id);
3644 exception
3645 when others then
3646 hr_data_pump.fail('get_default_code_comb_id', sqlerrm,
3647 p_default_code_comb_user_key );
3648 raise;
3649 end get_default_code_comb_id;
3650
3651 /* Added for 11i - Rvydyana - 06-DEC-1999 */
3652 /* get_phone_id - requires user key */
3653 function get_phone_id
3654 (
3655 p_phone_user_key in varchar2
3656 ) return number is
3657 l_phone_id number;
3658 begin
3659 l_phone_id := user_key_to_id( p_phone_user_key );
3660 return(l_phone_id);
3661 exception
3662 when others then
3663 hr_data_pump.fail('get_phone_id', sqlerrm, p_phone_user_key);
3664 raise;
3665 end get_phone_id;
3666
3667 /*-------------------------- get_grade_ladder_pgm_id -----------------------*/
3668 function get_grade_ladder_pgm_id
3669 ( p_grade_ladder_name in varchar2
3670 ,p_business_group_id in number
3671 ,p_effective_date in date
3672 ) return number is
3673 l_grade_ladder_pgm_id number;
3674 begin
3675 select pgm.pgm_id
3676 into l_grade_ladder_pgm_id
3677 from ben_pgm_f pgm
3678 where pgm.name = p_grade_ladder_name
3679 and pgm.pgm_typ_cd = 'GSP'
3680 and pgm.business_group_id + 0 = p_business_group_id
3681 and p_effective_date
3682 between pgm.effective_start_date and pgm.effective_end_date;
3683 return(l_grade_ladder_pgm_id);
3684 exception
3685 when others then
3686 hr_data_pump.fail('get_grade_ladder_pgm_id', sqlerrm,
3687 p_grade_ladder_name);
3688 raise;
3689 end get_grade_ladder_pgm_id;
3690 /*---------------------- get_supervisor_assignment_id -----------------------*/
3691 function get_supervisor_assignment_id
3692 /* return supervisor assignment_id - requires user key */
3693 (
3694 p_svr_assignment_user_key in varchar2
3695 ) return number is
3696 l_supervisor_assignment_id number;
3697 begin
3698 l_supervisor_assignment_id := get_assignment_id(p_svr_assignment_user_key );
3699 return(l_supervisor_assignment_id);
3700 exception
3701 when others then
3702 hr_data_pump.fail('get_supervisor_assignment_id', sqlerrm,
3703 p_svr_assignment_user_key);
3704 raise;
3705 end get_supervisor_assignment_id;
3706
3707 /*--------------------- get_parent_spine_id ----------------------------------*/
3708 function get_parent_spine_id
3709 (
3710 p_parent_spine in varchar2
3711 ,p_business_group_id in number
3712 )
3713 return number is
3714 l_parent_spine_id number;
3715 begin
3716 select parent_spine_id
3717 into l_parent_spine_id
3718 from per_parent_spines
3719 where name = p_parent_spine
3720 and business_group_id = p_business_group_id;
3721 return(l_parent_spine_id);
3722 exception
3723 when others then
3724 hr_data_pump.fail('get_parent_spine_id', sqlerrm, p_parent_spine,
3725 p_business_group_id);
3726 raise;
3727 end get_parent_spine_id;
3728 /*--------------------- get_ceiling_step_id ----------------------------------*/
3729 function get_ceiling_step_id
3730 (
3731 p_ceiling_point in varchar2,
3732 p_business_group_id in number,
3733 p_effective_date in date
3734 )
3735 return number is
3736 l_ceiling_step_id number;
3737 begin
3738 select sps.step_id
3739 into l_ceiling_step_id
3740 from per_spinal_points psp
3741 ,per_spinal_point_steps_f sps
3742 where psp.spinal_point = p_ceiling_point
3743 and psp.spinal_point_id = sps.spinal_point_id
3744 and sps.business_group_id = p_business_group_id
3745 and p_effective_date between
3746 sps.effective_start_date and sps.effective_end_date;
3747 return(l_ceiling_step_id);
3748 exception
3749 when others then
3750 hr_data_pump.fail('get_ceiling_step_id', sqlerrm, p_ceiling_point,
3751 p_business_group_id, p_effective_date);
3752 raise;
3753 end get_ceiling_step_id;
3754
3755 /*---------------------------------------------------------------------------*/
3756 /*------------------- get object version number functions -------------------*/
3757 /*---------------------------------------------------------------------------*/
3758
3759 /*-------------------- get_collective_agreement_ovn ------------------------*/
3760 function get_collective_agreement_ovn
3761 (p_business_group_id in number
3762 ,p_cagr_name in varchar2
3763 ,p_effective_date in date
3764 ) return number is
3765 l_object_version_number number;
3766 begin
3767 select pc.object_version_number
3768 into l_object_version_number
3769 from per_collective_agreements pc
3770 where pc.business_group_id = p_business_group_id
3771 and pc.name = p_cagr_name
3772 and p_effective_date between
3773 nvl(start_date,START_OF_TIME) and nvl(end_date,END_OF_TIME);
3774 return l_object_version_number;
3775 exception
3776 when others then
3777 hr_data_pump.fail('get_collective_agreement_ovn', sqlerrm,
3778 p_business_group_id, p_cagr_name, p_effective_date);
3779 raise;
3780 end get_collective_agreement_ovn;
3781
3782 /*----------------------------- get_contract_ovn ---------------------------*/
3783 function get_contract_ovn
3784 (p_contract_user_key in varchar2
3785 ,p_effective_date in date
3786 ) return number is
3787 l_object_version_number number;
3788 begin
3789 select pc.object_version_number
3790 into l_object_version_number
3791 from per_contracts_f pc,
3792 hr_pump_batch_line_user_keys key
3793 where key.user_key_value = p_contract_user_key
3794 and pc.contract_id = key.unique_key_id
3795 and p_effective_date between
3796 pc.effective_start_date and pc.effective_end_date;
3797 return(l_object_version_number);
3798 exception
3799 when others then
3800 hr_data_pump.fail('get_contract_ovn', sqlerrm, p_contract_user_key);
3801 raise;
3802 end get_contract_ovn;
3803
3804 /*--------------------------- get_establishment_ovn ---------------------------*/
3805 function get_establishment_ovn
3806 (p_establishment_name in varchar2
3807 ,p_location in varchar2
3808 ) return number is
3809 l_object_version_number number;
3810 begin
3811 select pe.object_version_number
3812 into l_object_version_number
3813 from per_establishments pe
3814 where pe.location = p_location
3815 and pe.name = p_establishment_name;
3816 return l_object_version_number;
3817 exception
3818 when others then
3819 hr_data_pump.fail('get_establishment_ovn', sqlerrm,
3820 p_establishment_name, p_location);
3821 raise;
3822 end get_establishment_ovn;
3823
3824 /* returns a federal tax rule object version number */
3825 function get_us_emp_fed_tax_rule_ovn
3826 (
3827 p_emp_fed_tax_rule_user_key in varchar2,
3828 p_effective_date in date
3829 ) return number is
3830 l_ovn number;
3831 begin
3832 select rules.object_version_number
3833 into l_ovn
3834 from pay_us_emp_fed_tax_rules_f rules,
3835 hr_pump_batch_line_user_keys key
3836 where key.user_key_value = p_emp_fed_tax_rule_user_key
3837 and rules.emp_fed_tax_rule_id = key.unique_key_id
3838 and p_effective_date between
3839 rules.effective_start_date and rules.effective_end_date;
3840 return(l_ovn);
3841 exception
3842 when others then
3843 hr_data_pump.fail('get_us_emp_fed_tax_rule_ovn', sqlerrm,
3844 p_emp_fed_tax_rule_user_key, p_effective_date);
3845 raise;
3846 end get_us_emp_fed_tax_rule_ovn;
3847
3848 /* returns a state tax rule object version number */
3849 function get_us_emp_state_tax_rule_ovn
3850 (
3851 p_emp_state_tax_rule_user_key in varchar2,
3852 p_effective_date in date
3853 ) return number is
3854 l_ovn number;
3855 begin
3856 select rules.object_version_number
3857 into l_ovn
3858 from pay_us_emp_state_tax_rules_f rules,
3859 hr_pump_batch_line_user_keys key
3860 where key.user_key_value = p_emp_state_tax_rule_user_key
3861 and rules.emp_state_tax_rule_id = key.unique_key_id
3862 and p_effective_date between
3863 rules.effective_start_date and rules.effective_end_date;
3864 return(l_ovn);
3865 exception
3866 when others then
3867 hr_data_pump.fail('get_us_emp_state_tax_rule_ovn', sqlerrm,
3868 p_emp_state_tax_rule_user_key, p_effective_date);
3869 raise;
3870 end get_us_emp_state_tax_rule_ovn;
3871
3872 /* returns a county tax rule object version number */
3873 function get_us_emp_county_tax_rule_ovn
3874 (
3875 p_emp_county_tax_rule_user_key in varchar2,
3876 p_effective_date in date
3877 ) return number is
3878 l_ovn number;
3879 begin
3880 select rules.object_version_number
3881 into l_ovn
3882 from pay_us_emp_county_tax_rules_f rules,
3883 hr_pump_batch_line_user_keys key
3884 where key.user_key_value = p_emp_county_tax_rule_user_key
3885 and rules.emp_county_tax_rule_id = key.unique_key_id
3886 and p_effective_date between
3887 rules.effective_start_date and rules.effective_end_date;
3888 return(l_ovn);
3889 exception
3890 when others then
3891 hr_data_pump.fail('get_us_emp_county_tax_rule_ovn', sqlerrm,
3892 p_emp_county_tax_rule_user_key, p_effective_date);
3893 raise;
3894 end get_us_emp_county_tax_rule_ovn;
3895
3896 /* returns a city tax rule object version number */
3897 function get_us_emp_city_tax_rule_ovn
3898 (
3899 p_emp_city_tax_rule_user_key in varchar2,
3900 p_effective_date in date
3901 ) return number is
3902 l_ovn number;
3903 begin
3904 select rules.object_version_number
3905 into l_ovn
3906 from pay_us_emp_city_tax_rules_f rules,
3907 hr_pump_batch_line_user_keys key
3908 where key.user_key_value = p_emp_city_tax_rule_user_key
3909 and rules.emp_city_tax_rule_id = key.unique_key_id
3910 and p_effective_date between
3911 rules.effective_start_date and rules.effective_end_date;
3912 return(l_ovn);
3913 exception
3914 when others then
3915 hr_data_pump.fail('get_us_emp_city_tax_rule_ovn', sqlerrm,
3916 p_emp_city_tax_rule_user_key, p_effective_date);
3917 raise;
3918 end get_us_emp_city_tax_rule_ovn;
3919
3920 /* returns a person object version number */
3921 function get_per_ovn
3922 (
3923 p_person_user_key in varchar2,
3924 p_effective_date in date
3925 ) return number is
3926 l_ovn number;
3927 begin
3928 select per.object_version_number
3929 into l_ovn
3930 from per_people_f per,
3931 hr_pump_batch_line_user_keys key
3932 where key.user_key_value = p_person_user_key
3933 and per.person_id = key.unique_key_id
3934 and p_effective_date between
3935 per.effective_start_date and per.effective_end_date;
3936 return(l_ovn);
3937 exception
3938 when others then
3939 hr_data_pump.fail('get_per_ovn', sqlerrm, p_person_user_key,
3940 p_effective_date);
3941 raise;
3942 end get_per_ovn;
3943
3944 /* returns an assignment object version number */
3945 function get_asg_ovn
3946 (
3947 p_assignment_user_key in varchar2,
3948 p_effective_date in date
3949 ) return number is
3950 l_ovn number;
3951 begin
3952 select asg.object_version_number
3953 into l_ovn
3954 from per_assignments_f asg,
3955 hr_pump_batch_line_user_keys key
3956 where key.user_key_value = p_assignment_user_key
3957 and asg.assignment_id = key.unique_key_id
3958 and p_effective_date between
3959 asg.effective_start_date and asg.effective_end_date;
3960 return(l_ovn);
3961 exception
3962 when others then
3963 hr_data_pump.fail('get_asg_ovn', sqlerrm, p_assignment_user_key,
3964 p_effective_date);
3965 raise;
3966 end get_asg_ovn;
3967
3968 /* returns an address object version number */
3969 function get_adr_ovn
3970 (
3971 p_address_user_key in varchar2,
3972 p_effective_date in date
3973 ) return number is
3974 l_ovn number;
3975 begin
3976 select adr.object_version_number
3977 into l_ovn
3978 from per_addresses adr,
3979 hr_pump_batch_line_user_keys key
3980 where key.user_key_value = p_address_user_key
3981 and adr.address_id = key.unique_key_id;
3982 return(l_ovn);
3983 exception
3984 when others then
3985 hr_data_pump.fail('get_adr_ovn', sqlerrm, p_address_user_key,
3986 p_effective_date);
3987 raise;
3988 end get_adr_ovn;
3989
3990 /* returns a location object version number */
3991 function get_loc_ovn
3992 (
3993 p_location_code in varchar2
3994 ) return number is
3995 l_ovn number;
3996 begin
3997 --
3998 -- Changed to use hr_locations_all for WWBUG 1833930.
3999 --
4000 select loc.object_version_number
4001 into l_ovn
4002 from hr_locations_all loc
4003 where loc.location_code = p_location_code;
4004 return(l_ovn);
4005 exception
4006 when others then
4007 hr_data_pump.fail('get_loc_ovn', sqlerrm, p_location_code);
4008 raise;
4009 end get_loc_ovn;
4010
4011 /* returns a job object version number */
4012 function get_org_str_ovn
4013 (
4014 p_name in varchar2,
4015 p_business_group_id in number
4016 ) return number is
4017 l_ovn number;
4018 begin
4019 select ors.object_version_number
4020 into l_ovn
4021 from per_organization_structures ors
4022 where ors.name = p_name
4023 and ors.business_group_id + 0 = p_business_group_id;
4024 return(l_ovn);
4025 exception
4026 when others then
4027 hr_data_pump.fail('get_org_str_ovn', sqlerrm, p_name, p_business_group_id);
4028 raise;
4029 end get_org_str_ovn;
4030
4031 /* returns a organization structure version object version number */
4032 function get_org_str_ver_ovn
4033 (
4034 p_business_group_id in number,
4035 p_organization_structure_id in number,
4036 p_date_from in date,
4037 p_version_number in number
4038 ) return number is
4039 l_ovn number;
4040 begin
4041 select osv.object_version_number
4042 into l_ovn
4043 from per_org_structure_versions osv
4044 where osv.organization_structure_id = p_organization_structure_id
4045 and osv.date_from = p_date_from
4046 and osv.version_number = p_version_number
4047 and osv.business_group_id + 0 = p_business_group_id;
4048 return(l_ovn);
4049 exception
4050 when others then
4051 hr_data_pump.fail('get_org_str_ver_ovn', sqlerrm, p_business_group_id, p_organization_structure_id, p_date_from, p_version_number);
4052 raise;
4053 end get_org_str_ver_ovn;
4054
4055 /* returns an organization object version number */
4056 function get_org_ovn
4057 (
4058 p_business_group_id in number,
4059 p_organization_name in varchar2,
4060 p_language_code in varchar2
4061 ) return number is
4062 l_ovn number;
4063 begin
4064 select org.object_version_number
4065 into l_ovn
4066 from hr_all_organization_units org
4067 , hr_all_organization_units_tl orgtl
4068 where orgtl.name = p_organization_name
4069 and orgtl.language = p_language_code
4070 and org.organization_id = orgtl.organization_id
4071 and org.business_group_id + 0 = p_business_group_id;
4072 return(l_ovn);
4073 exception
4074 when others then
4075 hr_data_pump.fail('get_org_ovn', sqlerrm, p_business_group_id, p_organization_name, p_language_code);
4076 raise;
4077 end get_org_ovn;
4078
4079 /* returns a job object version number */
4080 function get_job_ovn
4081 (
4082 p_job_name in varchar2,
4083 p_effective_date in date,
4084 p_business_group_id in number
4085 ) return number is
4086 l_ovn number;
4087 begin
4088 select job.object_version_number
4089 into l_ovn
4090 from per_jobs_vl job
4091 where job.name = p_job_name
4092 and job.business_group_id + 0 = p_business_group_id;
4093 return(l_ovn);
4094 exception
4095 when others then
4096 hr_data_pump.fail('get_job_ovn', sqlerrm, p_job_name, p_effective_date,
4097 p_business_group_id);
4098 raise;
4099 end get_job_ovn;
4100
4101 /* returns a position object version number */
4102 function get_pos_ovn
4103 (
4104 p_position_name in varchar2,
4105 p_business_group_id in number,
4106 p_effective_date in date
4107 ) return number is
4108 l_ovn number;
4109 begin
4110 select pos.object_version_number
4111 into l_ovn
4112 from hr_all_positions_f pos
4113 where pos.name = p_position_name
4114 and pos.business_group_id + 0 = p_business_group_id
4115 and p_effective_date between
4116 pos.effective_start_date and pos.effective_end_date;
4117 return(l_ovn);
4118 exception
4119 when others then
4120 hr_data_pump.fail('get_pos_ovn', sqlerrm, p_position_name,
4121 p_business_group_id, p_effective_date);
4122 raise;
4123 end get_pos_ovn;
4124
4125 /* returns a personal_payment_method object version number */
4126 function get_ppm_ovn
4127 (
4128 p_personal_pay_method_user_key in varchar2,
4129 p_effective_date in date
4130 ) return number is
4131 l_ovn number;
4132 begin
4133 select ppm.object_version_number
4134 into l_ovn
4135 from pay_personal_payment_methods_f ppm
4136 , hr_pump_batch_line_user_keys key
4137 where key.user_key_value = p_personal_pay_method_user_key
4138 and ppm.personal_payment_method_id = key.unique_key_id
4139 and p_effective_date between
4140 ppm.effective_start_date and ppm.effective_end_date;
4141 return(l_ovn);
4142 exception
4143 when others then
4144 hr_data_pump.fail('get_ppm_ovn', sqlerrm,
4145 p_personal_pay_method_user_key, p_effective_date);
4146 raise;
4147 end get_ppm_ovn;
4148
4149 /* get_element_entry_ovn - requires user key */
4150 function get_element_entry_ovn
4151 (
4152 p_element_entry_user_key in varchar2,
4153 p_effective_date in date
4154 ) return number is
4155 l_element_entry_ovn number;
4156 begin
4157 select pee.object_version_number
4158 into l_element_entry_ovn
4159 from pay_element_entries_f pee,
4160 hr_pump_batch_line_user_keys key
4161 where key.user_key_value = p_element_entry_user_key
4162 and pee.element_entry_id = key.unique_key_id
4163 and p_effective_date between
4164 pee.effective_start_date and pee.effective_end_date;
4165 return(l_element_entry_ovn);
4166 exception
4167 when others then
4168 hr_data_pump.fail('get_element_entry_ovn', sqlerrm, p_element_entry_user_key, p_effective_date);
4169 raise;
4170 end get_element_entry_ovn;
4171
4172 /* get_gr_grade_rule_ovn */
4173 function get_gr_grade_rule_ovn
4174 (
4175 p_grade_name in varchar2,
4176 p_rate_name in varchar2,
4177 p_business_group_id in number,
4178 p_effective_date in date
4179 )
4180 return number is
4181 l_object_version_number number;
4182 begin
4183 select pgr.object_version_number
4184 into l_object_version_number
4185 from pay_grade_rules_f pgr,
4186 per_grades_vl pg,
4187 pay_rates pr
4188 where pg.name = p_grade_name
4189 and pg.business_group_id + 0 = p_business_group_id
4190 and pr.name = p_rate_name
4191 and pr.business_group_id + 0 = p_business_group_id
4192 and pgr.rate_id = pr.rate_id
4193 and pgr.grade_or_spinal_point_id = pg.grade_id
4194 and pgr.rate_type = 'G'
4195 and pgr.business_group_id + 0 = p_business_group_id
4196 and p_effective_date between pgr.effective_start_date and
4197 pgr.effective_end_date;
4198 return(l_object_version_number);
4199 exception
4200 when others then
4201 hr_data_pump.fail('get_gr_grade_rule_ovn', sqlerrm, p_grade_name,
4202 p_rate_name, p_business_group_id, p_effective_date);
4203 raise;
4204 end get_gr_grade_rule_ovn;
4205
4206 /* get_pp_grade_rule_ovn */
4207 function get_pp_grade_rule_ovn
4208 (
4209 p_progression_point in varchar2,
4210 p_pay_scale in varchar2,
4211 p_rate_name in varchar2,
4212 p_business_group_id in number,
4213 p_effective_date in date
4214 )
4215 return number is
4216 l_object_version_number number;
4217 begin
4218 select pgr.object_version_number
4219 into l_object_version_number
4220 from per_parent_spines pps,
4221 per_spinal_points psp,
4222 pay_grade_rules_f pgr,
4223 pay_rates pr
4224 where pps.name = p_pay_scale
4225 and pps.business_group_id + 0 = p_business_group_id
4226 and psp.spinal_point = p_progression_point
4227 and psp.business_group_id + 0 = p_business_group_id
4228 and psp.parent_spine_id = pps.parent_spine_id
4229 and pr.name = p_rate_name
4230 and pr.business_group_id + 0 = p_business_group_id
4231 and pgr.rate_id = pr.rate_id
4232 and pgr.grade_or_spinal_point_id = psp.spinal_point_id
4233 and pgr.rate_type = 'SP'
4234 and pgr.business_group_id + 0 = p_business_group_id
4235 and p_effective_date between pgr.effective_start_date and
4236 pgr.effective_end_date;
4237 return(l_object_version_number);
4238 exception
4239 when others then
4240 hr_data_pump.fail('get_pp_grade_rule_id', sqlerrm, p_progression_point,
4241 p_pay_scale, p_rate_name, p_business_group_id,
4242 p_effective_date);
4243 raise;
4244 end get_pp_grade_rule_ovn;
4245
4246 /* get_at_period_of_service_ovn */
4247 function get_at_period_of_service_ovn
4248 (
4249 p_person_user_key in varchar2,
4250 p_business_group_id in number
4251 )
4252 return number is
4253 l_object_version_number number;
4254 begin
4255 select pps.object_version_number
4256 into l_object_version_number
4257 from per_periods_of_service pps,
4258 hr_pump_batch_line_user_keys key
4259 where key.user_key_value = p_person_user_key
4260 and pps.person_id = key.unique_key_id
4261 and pps.business_group_id = p_business_group_id
4262 and pps.actual_termination_date is null;
4263 return(l_object_version_number);
4264 exception
4265 when others then
4266 hr_data_pump.fail('get_at_period_of_service_ovn', sqlerrm, p_person_user_key,
4267 p_business_group_id);
4268 raise;
4269 end get_at_period_of_service_ovn;
4270
4271 /* get_fp_period_of_service_ovn */
4272 function get_fp_period_of_service_ovn
4273 (
4274 p_person_user_key in varchar2,
4275 p_business_group_id in number
4276 )
4277 return number is
4278 l_object_version_number number;
4279 begin
4280 select pps.object_version_number
4281 into l_object_version_number
4282 from per_periods_of_service pps,
4283 hr_pump_batch_line_user_keys key
4284 where key.user_key_value = p_person_user_key
4285 and pps.person_id = key.unique_key_id
4286 and pps.business_group_id = p_business_group_id
4287 and pps.actual_termination_date is not null
4288 and pps.final_process_date is null;
4289 return(l_object_version_number);
4290 exception
4291 when others then
4292 hr_data_pump.fail('get_fp_period_of_service_ovn', sqlerrm, p_person_user_key,
4293 p_business_group_id);
4294 raise;
4295 end get_fp_period_of_service_ovn;
4296
4297 /* Added for 11i,Rvydyana,02-DEC-1999 */
4298 /* get_ut_period_of_service_ovn */
4299 function get_ut_period_of_service_ovn
4300 (
4301 p_person_user_key in varchar2,
4302 p_effective_date in date,
4303 p_business_group_id in number
4304 )
4305 return number is
4306 l_eot constant date := to_date('4712/12/31', 'YYYY/MM/DD');
4307 l_object_version_number number;
4308 begin
4309
4310 select pps.object_version_number
4311 into l_object_version_number
4312 from per_periods_of_service pps,
4313 hr_pump_batch_line_user_keys key
4314 where key.user_key_value = p_person_user_key
4315 and pps.person_id = key.unique_key_id
4316 and pps.business_group_id = p_business_group_id
4317 and p_effective_date between pps.date_start and NVL(pps.actual_termination_date,l_eot);
4318 return(l_object_version_number);
4319 exception
4320 when others then
4321 hr_data_pump.fail('get_ut_period_of_service_id', sqlerrm, p_person_user_key,
4322 p_effective_date,p_business_group_id);
4323 raise;
4324 end get_ut_period_of_service_ovn;
4325
4326 /* get entry_step_id - requires user key */
4327 function get_entry_step_id
4328 (
4329 p_entry_step_user_key in varchar2
4330 ) return number is
4331 l_entry_step_id number;
4332 begin
4333 l_entry_step_id := user_key_to_id( p_entry_step_user_key );
4334 return(l_entry_step_id);
4335 exception
4336 when others then
4337 hr_data_pump.fail('get_entry_step_id', sqlerrm, p_entry_step_user_key);
4338 raise;
4339 end get_entry_step_id;
4340
4341 /* get entry_grade_rule_id - requires user key */
4342 function get_entry_grade_rule_id
4343 (
4344 p_entry_grade_rule_user_key in varchar2
4345 ) return number is
4346 l_entry_grade_rule_id number;
4347 begin
4348 l_entry_grade_rule_id := user_key_to_id( p_entry_grade_rule_user_key );
4349 return(l_entry_grade_rule_id);
4350 exception
4351 when others then
4352 hr_data_pump.fail('get_entry_grade_rule_id', sqlerrm, p_entry_grade_rule_user_key);
4353 raise;
4354 end get_entry_grade_rule_id;
4355
4356 /* Added for 11i - Rvydyana - 06-DEC-1999 */
4357 /* returns a phone object version number */
4358 function get_phn_ovn
4359 (
4360 p_phone_user_key in varchar2
4361 ) return number is
4362 l_ovn number;
4363 begin
4364 select phn.object_version_number
4365 into l_ovn
4366 from per_phones phn,
4367 hr_pump_batch_line_user_keys key
4368 where key.user_key_value = p_phone_user_key
4369 and phn.phone_id = key.unique_key_id;
4370 return(l_ovn);
4371 exception
4372 when others then
4373 hr_data_pump.fail('get_phn_ovn', sqlerrm, p_phone_user_key);
4374 raise;
4375 end get_phn_ovn;
4376 --
4377 /* --------------------------------------------------- */
4378 /* ----------------- get_jgr_ovn---------------------- */
4379 /* --------------------------------------------------- */
4380 function get_jgr_ovn
4381 (
4382 p_job_group_user_key in varchar2
4383 ) return number is
4384 l_ovn number;
4385 begin
4386 select jgr.object_version_number
4387 into l_ovn
4388 from per_job_groups jgr,
4389 hr_pump_batch_line_user_keys key
4390 where key.user_key_value = p_job_group_user_key
4391 and jgr.job_group_id = key.unique_key_id;
4392 return(l_ovn);
4393 exception
4394 when others then
4395 hr_data_pump.fail('get_jgr_ovn', sqlerrm, p_job_group_user_key);
4396 raise;
4397 end get_jgr_ovn;
4398
4399 /* --------------------------------------------------- */
4400 /* ----------------- get_rol_ovn---------------------- */
4401 /* --------------------------------------------------- */
4402 function get_rol_ovn
4403 (
4404 p_role_user_key in varchar2
4405 ) return number is
4406 l_ovn number;
4407 begin
4408 select rol.object_version_number
4409 into l_ovn
4410 from per_roles rol,
4411 hr_pump_batch_line_user_keys key
4412 where key.user_key_value = p_role_user_key
4413 and rol.role_id = key.unique_key_id;
4414 return(l_ovn);
4415 exception
4416 when others then
4417 hr_data_pump.fail('get_rol_ovn', sqlerrm, p_role_user_key);
4418 raise;
4419 end get_rol_ovn;
4420 /*-------------- returns a pay scale object version number --------------------*/
4421 function get_pay_scale_ovn
4422 (
4423 p_pay_scale in varchar2,
4424 p_business_group_id in number
4425 ) return number is
4426 l_ovn number;
4427 begin
4428 select object_version_number
4429 into l_ovn
4430 from per_parent_spines
4431 where name = p_pay_scale
4432 and business_group_id + 0 = p_business_group_id;
4433 return(l_ovn);
4434 exception
4435 when others then
4436 hr_data_pump.fail('get_pay_scale_ovn', sqlerrm, p_pay_scale,
4437 p_business_group_id);
4438 raise;
4439 end get_pay_scale_ovn;
4440 /*-------------- returns a preogresion point object version number ------------*/
4441 function get_progression_point_ovn
4442 (
4443 p_point in varchar2,
4444 p_business_group_id in number
4445 ) return number is
4446 l_ovn number;
4447 begin
4448 select object_version_number
4449 into l_ovn
4450 from per_spinal_points
4451 where spinal_point = p_point
4452 and business_group_id + 0 = p_business_group_id;
4453 return(l_ovn);
4454 exception
4455 when others then
4456 hr_data_pump.fail('get_progression_point_ovn', sqlerrm, p_point,
4457 p_business_group_id);
4458 raise;
4459 end get_progression_point_ovn;
4460 /*-------------- returns a grade scale object version number ------------*/
4461 function get_grade_scale_ovn
4462 (
4463 p_grade in varchar2,
4464 p_pay_scale in varchar2,
4465 p_effective_date in date,
4466 p_business_group_id in number
4467 ) return number is
4468 l_ovn number;
4469 begin
4470 select pgs.object_version_number
4471 into l_ovn
4472 from per_grade_spines_f pgs
4473 ,per_grades pg
4474 ,per_parent_spines pps
4475 where pg.name = p_grade
4476 and pg.grade_id = pgs.grade_id
4477 and pps.name = p_pay_scale
4478 and pps.parent_spine_id = pgs.parent_spine_id
4479 and pgs.business_group_id = p_business_group_id
4480 and p_effective_date between
4481 pgs.effective_start_date and pgs.effective_end_date;
4482 return(l_ovn);
4483 exception
4484 when others then
4485 hr_data_pump.fail('get_grade_scale_ovn', sqlerrm, p_grade,
4486 p_pay_scale, p_effective_date ,p_business_group_id);
4487 raise;
4488 end get_grade_scale_ovn;
4489 /*-------------- returns a grade step object version number ------------*/
4490 function get_grade_step_ovn
4491 (
4492 p_point in varchar2,
4493 p_sequence in number,
4494 p_effective_date in date,
4495 p_business_group_id in number
4496 ) return number is
4497 l_ovn number;
4498 begin
4499 select sps.object_version_number
4500 into l_ovn
4501 from per_spinal_point_steps_f sps
4502 ,per_spinal_points psp
4503 ,per_grade_spines_f pgs
4504 where psp.spinal_point = p_point
4505 and psp.spinal_point_id = sps.spinal_point_id
4506 and sps.sequence = p_sequence
4507 and sps.step_id = pgs.ceiling_step_id
4508 and pgs.grade_spine_id = sps.grade_spine_id
4509 and sps.business_group_id = p_business_group_id
4510 and p_effective_date between
4511 sps.effective_start_date and sps.effective_end_date;
4512 return(l_ovn);
4513 exception
4514 when others then
4515 hr_data_pump.fail('get_grade_step_ovn', sqlerrm, p_point,
4516 p_sequence,p_effective_date, p_business_group_id);
4517 raise;
4518 end get_grade_step_ovn;
4519 /*---------------------------------------------------------------------------*/
4520 /*----------------------- other special get functions -----------------------*/
4521 /*---------------------------------------------------------------------------*/
4522
4523 /* returns a language code */
4524 function get_correspondence_language
4525 (
4526 p_correspondence_language varchar2
4527 ) return varchar2 is
4528 l_code fnd_languages.language_code%type;
4529 begin
4530 select l.language_code
4531 into l_code
4532 from fnd_languages l
4533 where l.nls_language = p_correspondence_language;
4534 return(l_code);
4535 exception
4536 -- If the nls_language could not be matched, assume that the user was
4537 -- entering the code directly.
4538 when no_data_found then
4539 return(p_correspondence_language);
4540 when others then
4541 hr_data_pump.fail('get_correspondence_language', sqlerrm,
4542 p_correspondence_language);
4543 raise;
4544 end get_correspondence_language;
4545
4546 /* get_country */
4547 function get_country( p_country in varchar2 ) return varchar2 is
4548 l_territory_code varchar2(2);
4549 begin
4550 select territory_code
4551 into l_territory_code
4552 from fnd_territories_vl
4553 where territory_short_name = p_country;
4554 return(l_territory_code);
4555 exception
4556 -- If the short_name could not be matched, assume that the user was
4557 -- entering the code directly.
4558 when no_data_found then
4559 return(p_country);
4560 when others then
4561 hr_data_pump.fail('get_country', sqlerrm, p_country );
4562 raise;
4563 end get_country;
4564
4565 /* get change reason lookup code, dependent on assignment */
4566 function get_change_reason
4567 (
4568 p_change_reason in varchar2,
4569 p_assignment_user_key in varchar2,
4570 p_effective_date in date,
4571 p_language_code in varchar2
4572 ) return varchar2 is
4573 l_code varchar2(30);
4574 begin
4575 select flv.lookup_code
4576 into l_code
4577 from fnd_lookup_values flv,
4578 per_assignments_f asg,
4579 hr_pump_batch_line_user_keys key
4580 where key.user_key_value = p_assignment_user_key
4581 and asg.assignment_id = key.unique_key_id
4582 and p_effective_date between
4583 asg.effective_start_date and asg.effective_end_date
4584 and flv.lookup_type = decode(asg.assignment_type,
4585 'E', 'EMP_ASSIGN_REASON',
4586 'A', 'APL_ASSIGN_REASON')
4587 and flv.meaning = p_change_reason
4588 and flv.language = p_language_code
4589 and flv.view_application_id = 3
4590 and flv.security_group_id =
4591 fnd_global.lookup_security_group
4592 (flv.lookup_type
4593 ,flv.view_application_id
4594 )
4595 and p_effective_date between
4596 nvl(flv.start_date_active, START_OF_TIME) and
4597 nvl(flv.end_date_active, END_OF_TIME);
4598 return(l_code);
4599 exception
4600 -- Assume that the user entered the code directly when no data found.
4601 when no_data_found then
4602 return(p_change_reason);
4603 when others then
4604 hr_data_pump.fail('get_change_reason', sqlerrm, p_change_reason,
4605 p_assignment_user_key, p_effective_date);
4606 raise;
4607 end get_change_reason;
4608
4609 /* get_job_group_id - requires user key */
4610 function get_job_group_id
4611 (
4612 p_job_group_user_key in varchar2
4613 ) return number is
4614 l_job_group_id number;
4615 begin
4616 l_job_group_id := user_key_to_id( p_job_group_user_key );
4617 return(l_job_group_id);
4618 exception
4619 when others then
4620 hr_data_pump.fail('get_job_group_id', sqlerrm, p_job_group_user_key);
4621 raise;
4622 end get_job_group_id;
4623
4624 /* get_benchmark_job_id - requires user key */
4625 function get_benchmark_job_id
4626 (
4627 p_benchmark_job_user_key in varchar2
4628 ) return number is
4629 l_benchmark_job_id number;
4630 begin
4631 l_benchmark_job_id := user_key_to_id( p_benchmark_job_user_key );
4632 return(l_benchmark_job_id);
4633 exception
4634 when others then
4635 hr_data_pump.fail('get_benchmark_job_id', sqlerrm, p_benchmark_job_user_key);
4636 raise;
4637 end get_benchmark_job_id;
4638
4639 /* get_role_id - requires user key */
4640 function get_role_id
4641 (
4642 p_role_user_key in varchar2
4643 ) return number is
4644 l_role_id number;
4645 begin
4646 l_role_id := user_key_to_id( p_role_user_key );
4647 return(l_role_id);
4648 exception
4649 when others then
4650 hr_data_pump.fail('get_role_id', sqlerrm, p_role_user_key);
4651 raise;
4652 end get_role_id;
4653
4654 /* get_loc_id - requires user key */
4655 function get_loc_id
4656 (
4657 p_location_user_key in varchar2
4658 ) return number is
4659 l_location_id number;
4660 begin
4661 l_location_id := user_key_to_id( p_location_user_key );
4662 return(l_location_id);
4663 exception
4664 when others then
4665 hr_data_pump.fail('get_loc_id', sqlerrm, p_location_user_key);
4666 raise;
4667 end get_loc_id;
4668
4669 /* get_org_structure_id - requires user key */
4670 function get_org_structure_id
4671 (
4672 p_org_structure_user_key in varchar2
4673 ) return number is
4674 l_organization_structure_id number;
4675 begin
4676 l_organization_structure_id := user_key_to_id( p_org_structure_user_key );
4677 return(l_organization_structure_id);
4678 exception
4679 when others then
4680 hr_data_pump.fail('get_org_structure_id', sqlerrm, p_org_structure_user_key);
4681 raise;
4682 end get_org_structure_id;
4683
4684 /* get_org_str_version_id - requires user key */
4685 function get_org_str_version_id
4686 (
4687 p_org_str_version_user_key in varchar2
4688 ) return number is
4689 l_org_structure_version_id number;
4690 begin
4691 l_org_structure_version_id := user_key_to_id( p_org_str_version_user_key );
4692 return(l_org_structure_version_id);
4693 exception
4694 when others then
4695 hr_data_pump.fail('get_org_str_version_id', sqlerrm, p_org_str_version_user_key);
4696 raise;
4697 end get_org_str_version_id;
4698
4699 /* get_org_id - requires user key */
4700 function get_org_id
4701 (
4702 p_org_user_key in varchar2
4703 ) return number is
4704 l_org_id number;
4705 begin
4706 l_org_id := user_key_to_id( p_org_user_key );
4707 return(l_org_id);
4708 exception
4709 when others then
4710 hr_data_pump.fail('get_org_id', sqlerrm, p_org_user_key);
4711 raise;
4712 end get_org_id;
4713
4714 /* get_grade_rule_id - requires user key */
4715 function get_grade_rule_id
4716 (
4717 p_grade_rule_user_key in varchar2
4718 ) return number is
4719 l_grade_rule_id number;
4720 begin
4721 l_grade_rule_id := user_key_to_id( p_grade_rule_user_key );
4722 return(l_grade_rule_id);
4723 exception
4724 when others then
4725 hr_data_pump.fail('get_grade_rule_id', sqlerrm, p_grade_rule_user_key);
4726 raise;
4727 end get_grade_rule_id;
4728
4729 /* returns lookup_code */
4730 function gl
4731 (
4732 p_meaning_or_code in varchar2,
4733 p_lookup_type in varchar2,
4734 p_effective_date in date default null,
4735 p_language_code in varchar2 default null
4736 ) return varchar2 is
4737 l_code hr_lookups.lookup_code%type;
4738 l_effective_date date;
4739 l_language_code varchar2(2000);
4740 begin
4741 --
4742 -- Is lookup checking disabled ?
4743 --
4744 if hr_data_pump.g_disable_lookup_checks then
4745 return p_meaning_or_code;
4746 end if;
4747 --
4748 -- Set language code (handling possible defaults).
4749 --
4750 if p_language_code = hr_api.g_varchar2 then
4751 --
4752 -- nvl() in the query will take care of defaulting.
4753 --
4754 l_language_code := null;
4755 else
4756 l_language_code := p_language_code;
4757 end if;
4758 --
4759 -- Set the effective date (handling possible defaults).
4760 --
4761 if p_effective_date is null or p_effective_date = hr_api.g_date then
4762 l_effective_date := hr_api.g_sys;
4763 else
4764 l_effective_date := p_effective_date;
4765 end if;
4766 if p_meaning_or_code is null or p_meaning_or_code = hr_api.g_varchar2
4767 then
4768 --
4769 -- Defaulted values will be returned unchanged.
4770 --
4771 l_code := p_meaning_or_code;
4772 else
4773 --
4774 -- Check against meaning using the new R11.5 lookup tables.
4775 --
4776 select flv.lookup_code
4777 into l_code
4778 from fnd_lookup_values flv
4779 where flv.meaning = p_meaning_or_code
4780 and flv.lookup_type = p_lookup_type
4781 and flv.language = nvl(l_language_code, userenv('LANG'))
4782 and flv.view_application_id = 3
4783 and flv.security_group_id =
4784 fnd_global.lookup_security_group
4785 (flv.lookup_type
4786 ,flv.view_application_id
4787 )
4788 and l_effective_date between
4789 nvl(flv.start_date_active, START_OF_TIME) and
4790 nvl(flv.end_date_active, END_OF_TIME);
4791 end if;
4792 --
4793 return(l_code);
4794 exception
4795 when no_data_found then
4796 --
4797 -- If the meaning could not be matched, assume that the user
4798 -- entered the code directly. This part of the code used to
4799 -- check against HR_LOOKUPS, but HR_LOOKUPS requires the
4800 -- legislation context to be set in HR_SESSION_DATA now.
4801 -- Such an additional check is unnecessary because the API
4802 -- should be able to pick up bad code values.
4803 --
4804 return(p_meaning_or_code);
4805 when others then
4806 hr_data_pump.fail
4807 ('get_lookup_code', sqlerrm, p_meaning_or_code,
4808 p_lookup_type, p_effective_date, p_language_code);
4809 raise;
4810 end gl;
4811
4812 function get_lookup_code
4813 (
4814 p_meaning_or_code in varchar2,
4815 p_lookup_type in varchar2,
4816 p_effective_date in date default null,
4817 p_language_code in varchar2 default null
4818 ) return varchar2 is
4819 begin
4820 return gl( p_meaning_or_code, p_lookup_type, p_effective_date,
4821 p_language_code );
4822 end get_lookup_code;
4823 /* return people_group_id */
4824 function get_people_group_id
4825 (
4826 p_people_group_user_name in varchar2,
4827 p_effective_date in date
4828 ) return number is
4829 l_people_group_id number;
4830 begin
4831 --
4832 select people_group_id
4833 into l_people_group_id
4834 from pay_people_groups
4835 where GROUP_NAME = p_people_group_user_name
4836 and p_effective_date
4837 between nvl(start_date_active,START_OF_TIME)
4838 and nvl(end_date_active,END_OF_TIME);
4839 --
4840 return(l_people_group_id);
4841 exception
4842 when others then
4843 hr_data_pump.fail('get_people_group_id', sqlerrm,
4844 p_people_group_user_name,
4845 p_effective_date);
4846 raise;
4847 end get_people_group_id;
4848
4849 /* return absence_attendance_type_id */
4850 function get_absence_attendance_type_id
4851 (
4852 p_aat_user_name in varchar2,
4853 p_business_group_id in number,
4854 p_effective_date in date
4855 ) return number is
4856 l_absence_attendance_type_id number;
4857 begin
4858 --
4859 select aat.absence_attendance_type_id
4860 into l_absence_attendance_type_id
4861 from per_abs_attendance_types_vl aat
4862 where aat.name = p_aat_user_name
4863 and aat.business_group_id = p_business_group_id
4864 and p_effective_date between
4865 nvl(DATE_EFFECTIVE, START_OF_TIME) AND
4866 nvl(DATE_END, END_OF_TIME);
4867 --
4868 return(l_absence_attendance_type_id);
4869 exception
4870 when others then
4871 hr_data_pump.fail('get_absence_attendance_type_id', sqlerrm,
4872 p_aat_user_name,
4873 p_business_group_id,
4874 p_effective_date);
4875 raise;
4876 end get_absence_attendance_type_id;
4877 /* return soft_coding_keyflex_id */
4878 function get_soft_coding_keyflex_id
4879 (
4880 p_con_seg_user_name in varchar2,
4881 p_effective_date in date
4882 ) return number is
4883 l_soft_coding_keyflex_id number;
4884 begin
4885 --
4886 select soft_coding_keyflex_id
4887 into l_soft_coding_keyflex_id
4888 from hr_soft_coding_keyflex
4889 where concatenated_segments = p_con_seg_user_name
4890 and p_effective_date between
4891 nvl(START_DATE_ACTIVE, START_OF_TIME) AND
4892 nvl(END_DATE_ACTIVE, END_OF_TIME);
4893 --
4894 return(l_soft_coding_keyflex_id);
4895 exception
4896 when others then
4897 hr_data_pump.fail('get_soft_coding_keyflex_id', sqlerrm,
4898 p_con_seg_user_name,
4899 p_effective_date);
4900 raise;
4901 end get_soft_coding_keyflex_id;
4902
4903 /* return pk_id */
4904 function get_pk_id
4905 (
4906 p_pk_name in varchar2
4907 ) return number is
4908 l_pk_id number := null;
4909 begin
4910 --
4911 -- This column should not be populated by datapump.
4912 --
4913 return(l_pk_id);
4914 exception
4915 when others then
4916 hr_data_pump.fail('get_pk_id', sqlerrm,
4917 p_pk_name
4918 );
4919 raise;
4920 end get_pk_id;
4921
4922 /* Bug 3275173 -- get object versionn number*/
4923 function get_fed_tax_rule_ovn
4924 (
4925 p_emp_fed_tax_rule_user_key in varchar2,
4926 p_effective_date in date
4927 ) return number is
4928 l_ovn number;
4929 begin
4930 select object_version_number
4931 into l_ovn
4932 from pay_us_emp_fed_tax_rules_f puek,
4933 hr_pump_batch_line_user_keys uk
4934 where uk.user_key_value = p_emp_fed_tax_rule_user_key
4935 and p_effective_date between puek.effective_start_date and puek.effective_end_date
4936 and puek.emp_fed_tax_rule_id = uk.unique_key_id;
4937 --
4938
4939 return l_ovn;
4940 exception
4941 when others then
4942 hr_data_pump.fail('get_fed_tax_rule_ovn', sqlerrm,
4943 p_emp_fed_tax_rule_user_key,
4944 p_effective_date);
4945 raise;
4946 end get_fed_tax_rule_ovn;
4947
4948 --
4949 -- Bug 3783381 -- get object versionn number for state, county and city
4950 --
4951
4952 function get_state_tax_rule_ovn
4953 (
4954 p_emp_state_tax_rule_user_key in varchar2,
4955 p_effective_date in date
4956 ) return number is
4957 l_ovn number;
4958 begin
4959 select object_version_number
4960 into l_ovn
4961 from pay_us_emp_state_tax_rules_f puek,
4962 hr_pump_batch_line_user_keys uk
4963 where uk.user_key_value = p_emp_state_tax_rule_user_key
4964 and p_effective_date between puek.effective_start_date and puek.effective_end_date
4965 and puek.emp_state_tax_rule_id = uk.unique_key_id;
4966 --
4967
4968 return l_ovn;
4969 exception
4970 when others then
4971 hr_data_pump.fail('get_state_tax_rule_ovn', sqlerrm,
4972 p_emp_state_tax_rule_user_key,
4973 p_effective_date);
4974 raise;
4975 end get_state_tax_rule_ovn;
4976
4977 ---
4978 function get_county_tax_rule_ovn
4979 (
4980 p_emp_county_tax_rule_user_key in varchar2,
4981 p_effective_date in date
4982 ) return number is
4983 l_ovn number;
4984 begin
4985 select object_version_number
4986 into l_ovn
4987 from pay_us_emp_county_tax_rules_f puek,
4988 hr_pump_batch_line_user_keys uk
4989 where uk.user_key_value = p_emp_county_tax_rule_user_key
4990 and p_effective_date between puek.effective_start_date and puek.effective_end_date
4991 and puek.emp_county_tax_rule_id = uk.unique_key_id;
4992 --
4993
4994 return l_ovn;
4995 exception
4996 when others then
4997 hr_data_pump.fail('get_county_tax_rule_ovn', sqlerrm,
4998 p_emp_county_tax_rule_user_key,
4999 p_effective_date);
5000 raise;
5001 end get_county_tax_rule_ovn;
5002
5003 ---
5004
5005 function get_city_tax_rule_ovn
5006 (
5007 p_emp_city_tax_rule_user_key in varchar2,
5008 p_effective_date in date
5009 ) return number is
5010 l_ovn number;
5011 begin
5012 select object_version_number
5013 into l_ovn
5014 from pay_us_emp_city_tax_rules_f puek,
5015 hr_pump_batch_line_user_keys uk
5016 where uk.user_key_value = p_emp_city_tax_rule_user_key
5017 and p_effective_date between puek.effective_start_date and puek.effective_end_date
5018 and puek.emp_city_tax_rule_id = uk.unique_key_id;
5019 --
5020
5021 return l_ovn;
5022 exception
5023 when others then
5024 hr_data_pump.fail('get_city_tax_rule_ovn', sqlerrm,
5025 p_emp_city_tax_rule_user_key,
5026 p_effective_date);
5027 raise;
5028 end get_city_tax_rule_ovn;
5029
5030 --
5031 --
5032
5033 --
5034 -- -------------------------------------------------------------------------
5035 -- ------------< get_parent_comp_element_id >-------------------------
5036 -- -------------------------------------------------------------------------
5037 -- DESCRIPTION
5038 -- This function returns the
5039 --
5040 FUNCTION get_parent_comp_element_id
5041 RETURN BINARY_INTEGER
5042 IS
5043 BEGIN
5044 return (null);
5045 EXCEPTION
5046 WHEN OTHERS THEN
5047 hr_data_pump.fail('get_parent_comp_element_id'
5048 , sqlerrm
5049 );
5050 RAISE;
5051 END get_parent_comp_element_id;
5052 -- -------------------------------------------------------------------------
5053 -- --------------------< get_competence_id >--------------------------------
5054 -- -------------------------------------------------------------------------
5055 FUNCTION get_competence_id
5056 (p_data_pump_always_call IN varchar2
5057 ,p_competence_name IN VARCHAR2
5058 ,p_business_group_id IN NUMBER)
5059 RETURN BINARY_INTEGER
5060 IS
5061 l_competence_id NUMBER DEFAULT null;
5062 BEGIN
5063
5064 IF p_competence_name is NULL then
5065
5066 return null;
5067
5068 ELSIF p_competence_name = hr_api_g_varchar2 then
5069
5070 return hr_api_g_number;
5071
5072 ELSE
5073
5074 IF p_business_group_id is null THEN
5075
5076 SELECT competence_id
5077 INTO l_competence_id
5078 FROM per_competences_vl
5079 WHERE name = p_competence_name
5080 AND business_group_id is null;
5081
5082 ELSE
5083
5084 SELECT competence_id
5085 INTO l_competence_id
5086 FROM per_competences_vl
5087 WHERE name = p_competence_name
5088 AND business_group_id = p_business_group_id;
5089
5090 END IF;
5091
5092 END IF;
5093
5094 RETURN(l_competence_id);
5095 EXCEPTION
5096 WHEN OTHERS THEN
5097 hr_data_pump.fail('get_competence_id'
5098 , sqlerrm
5099 , p_competence_name
5100 , p_business_group_id);
5101 RAISE;
5102 END get_competence_id;
5103 -- -------------------------------------------------------------------------
5104 -- --------------------< get_cpn_ovn >---------------------------
5105 -- -------------------------------------------------------------------------
5106 -- DESCRIPTION
5107 -- This function returns the ovn of a competence
5108 --
5109 FUNCTION get_cpn_ovn
5110 (p_data_pump_always_call IN varchar2
5111 ,p_competence_name IN VARCHAR2
5112 ,p_business_group_id IN NUMBER)
5113 RETURN BINARY_INTEGER
5114 IS
5115 l_cpn_ovn NUMBER DEFAULT null;
5116 BEGIN
5117
5118 IF p_competence_name is NULL then
5119
5120 return null;
5121
5122 ELSIF p_competence_name = hr_api_g_varchar2 then
5123
5124 return hr_api_g_number;
5125
5126 ELSE
5127
5128 IF p_business_group_id is null THEN
5129
5130 SELECT object_version_number
5131 INTO l_cpn_ovn
5132 FROM per_competences_vl
5133 WHERE name = p_competence_name
5134 AND business_group_id is null;
5135
5136 ELSE
5137
5138 SELECT object_version_number
5139 INTO l_cpn_ovn
5140 FROM per_competences_vl
5141 WHERE name = p_competence_name
5142 AND business_group_id = p_business_group_id;
5143
5144 END IF;
5145
5146 END IF;
5147
5148 RETURN(l_cpn_ovn);
5149 EXCEPTION
5150 WHEN OTHERS THEN
5151 hr_data_pump.fail('get_cpn_ovn'
5152 , sqlerrm
5153 , p_competence_name
5154 , p_business_group_id);
5155 RAISE;
5156 END get_cpn_ovn;
5157 -- -------------------------------------------------------------------------
5158 -- -----------------< get_qualification_type_id >---------------------------
5159 -- -------------------------------------------------------------------------
5160 FUNCTION get_qualification_type_id
5161 (p_data_pump_always_call IN varchar2
5162 ,p_qualification_type_name IN VARCHAR2
5163 )
5164 RETURN BINARY_INTEGER
5165 IS
5166 l_qualification_type_id NUMBER DEFAULT null;
5167 BEGIN
5168
5169 IF p_qualification_type_name is NULL then
5170
5171 return null;
5172
5173 ELSIF p_qualification_type_name = hr_api_g_varchar2 then
5174
5175 return hr_api_g_number;
5176
5177 ELSE
5178
5179 SELECT qualification_type_id
5180 INTO l_qualification_type_id
5181 FROM per_qualification_types_vl
5182 WHERE name = p_qualification_type_name;
5183
5184 END IF;
5185
5186 RETURN(l_qualification_type_id);
5187 EXCEPTION
5188 WHEN OTHERS THEN
5189 hr_data_pump.fail('get_qualification_type_id'
5190 , sqlerrm
5191 , p_qualification_type_name);
5192 RAISE;
5193 END get_qualification_type_id;
5194 --
5195 -- -------------------------------------------------------------------------
5196 -- ---------------------< get_outcome_id >--------------------------------
5197 -- -------------------------------------------------------------------------
5198 FUNCTION get_outcome_id
5199 (p_data_pump_always_call IN varchar2
5200 ,p_outcome_name IN VARCHAR2
5201 )
5202 RETURN BINARY_INTEGER
5203 IS
5204 l_outcome_id NUMBER DEFAULT null;
5205 BEGIN
5206
5207 IF p_outcome_name is NULL then
5208
5209 return null;
5210
5211 ELSIF p_outcome_name = hr_api_g_varchar2 then
5212
5213 return hr_api_g_number;
5214
5215 ELSE
5216
5217 SELECT outcome_id
5218 INTO l_outcome_id
5219 FROM per_competence_outcomes_vl
5220 WHERE name = p_outcome_name;
5221
5222 END IF;
5223
5224 RETURN(l_outcome_id);
5225 EXCEPTION
5226 WHEN OTHERS THEN
5227 hr_data_pump.fail('get_outcome_id'
5228 , sqlerrm
5229 , p_outcome_name
5230 );
5231 RAISE;
5232 END get_outcome_id;
5233 -- -------------------------------------------------------------------------
5234 -- --------------------< get_cpo_ovn >---------------------------
5235 -- -------------------------------------------------------------------------
5236 -- DESCRIPTION
5237 -- This function returns the ovn of a competence outcome
5238 --
5239 FUNCTION get_cpo_ovn
5240 (p_data_pump_always_call IN varchar2
5241 ,p_outcome_name IN VARCHAR2
5242 )
5243 RETURN BINARY_INTEGER
5244 IS
5245 l_cpo_ovn NUMBER DEFAULT null;
5246 BEGIN
5247
5248 IF p_outcome_name is NULL then
5249
5250 return null;
5251
5252 ELSIF p_outcome_name = hr_api_g_varchar2 then
5253
5254 return hr_api_g_number;
5255
5256 ELSE
5257
5258 SELECT object_version_number
5259 INTO l_cpo_ovn
5260 FROM per_competence_outcomes_vl
5261 WHERE name = p_outcome_name;
5262
5263 END IF;
5264
5265 RETURN(l_cpo_ovn);
5266 EXCEPTION
5267 WHEN OTHERS THEN
5268 hr_data_pump.fail('get_cpo_ovn'
5269 , sqlerrm
5270 , p_outcome_name
5271 );
5272 RAISE;
5273 END get_cpo_ovn;
5274 -- -------------------------------------------------------------------------
5275 -- --------------------< get_eqt_ovn >---------------------------
5276 -- -------------------------------------------------------------------------
5277 -- DESCRIPTION
5278 -- This function returns the ovn of a qualification type
5279 --
5280 FUNCTION get_eqt_ovn
5281 (p_data_pump_always_call IN varchar2
5282 ,p_qualification_type_name IN VARCHAR2
5283 )
5284 RETURN BINARY_INTEGER
5285 IS
5286 l_eqt_ovn NUMBER DEFAULT null;
5287 BEGIN
5288
5289 IF p_qualification_type_name is NULL then
5290
5291 return null;
5292
5293 ELSIF p_qualification_type_name = hr_api_g_varchar2 then
5294
5295 return hr_api_g_number;
5296
5297 ELSE
5298
5299 SELECT object_version_number
5300 INTO l_eqt_ovn
5301 FROM per_qualification_types_vl
5302 WHERE name = p_qualification_type_name;
5303
5304 END IF;
5305
5306 RETURN(l_eqt_ovn);
5307 EXCEPTION
5308 WHEN OTHERS THEN
5309 hr_data_pump.fail('get_eqt_ovn'
5310 , sqlerrm
5311 , p_qualification_type_name
5312 );
5313 RAISE;
5314 END get_eqt_ovn;
5315 -- -------------------------------------------------------------------------
5316 -- --------------------< get_ceo_ovn >---------------------------
5317 -- -------------------------------------------------------------------------
5318 -- DESCRIPTION
5319 -- This function returns the ovn of a comp element outcomes
5320 --
5321 FUNCTION get_ceo_ovn
5322 (p_data_pump_always_call IN varchar2
5323 ,p_element_outcome_name IN VARCHAR2
5324 )
5325 RETURN BINARY_INTEGER
5326 IS
5327 l_ceo_ovn NUMBER DEFAULT null;
5328 BEGIN
5329
5330 IF p_element_outcome_name is NULL then
5331
5332 return null;
5333
5334 ELSIF p_element_outcome_name = hr_api_g_varchar2 then
5335
5336 return hr_api_g_number;
5337
5338 ELSE
5339
5340 SELECT object_version_number
5341 INTO l_ceo_ovn
5342 FROM per_comp_element_outcomes_vl
5343 WHERE name = p_element_outcome_name;
5344
5345 END IF;
5346
5347 RETURN(l_ceo_ovn);
5348 EXCEPTION
5349 WHEN OTHERS THEN
5350 hr_data_pump.fail('get_ceo_ovn'
5351 , sqlerrm
5352 , p_element_outcome_name
5353 );
5354 RAISE;
5355 END get_ceo_ovn;
5356 -- -------------------------------------------------------------------------
5357 -- ------------------< get_competence_element_id >------------------------
5358 -- -------------------------------------------------------------------------
5359 -- DESCRIPTION
5360 -- This function returns the ovn of a comp element outcomes
5361 --
5362 FUNCTION get_competence_element_id
5363 (p_data_pump_always_call in varchar2
5364 ,p_competence_name in varchar2
5365 ,p_person_user_key in varchar2
5366 ,p_business_group_id in number
5367 )
5368 return binary_integer
5369 IS
5370 l_competence_element_id number default null;
5371 l_person_id number default null;
5372 BEGIN
5373
5374 l_person_id := user_key_to_id(p_person_user_key);
5375
5376 SELECT competence_element_id
5377 INTO l_competence_element_id
5378 FROM per_competence_elements CEL
5379 ,per_competences CPN
5380 WHERE
5381 CEL.type = 'PERSONAL'
5382 and CPN.name = p_competence_name
5383 and CEL.competence_id = CPN.competence_id
5384 and CEL.business_group_id = p_business_group_id
5385 and CEL.person_id = l_person_id;
5386
5387 RETURN(l_competence_element_id);
5388 EXCEPTION
5389 WHEN OTHERS THEN
5390 hr_data_pump.fail('get_competence_element_id'
5391 , sqlerrm
5392 , p_competence_name
5393 , p_person_user_key
5394 , p_business_group_id
5395 );
5396 RAISE;
5397 END get_competence_element_id;
5398 --
5399 --
5400 FUNCTION get_cost_flex_stru_num
5401 (p_data_pump_always_call IN varchar2
5402 ,p_cost_flex_stru_code IN VARCHAR2
5403 )
5404 RETURN BINARY_INTEGER is
5405 l_cost_code number;
5406 begin
5407
5408 IF p_cost_flex_stru_code is NULL then
5409
5410 return null;
5411
5412 ELSIF p_cost_flex_stru_code = hr_api_g_varchar2 then
5413
5414 return hr_api_g_number;
5415
5416 ELSE
5417
5418 select id_flex_num into l_cost_code
5419 from fnd_id_flex_structures
5420 where id_flex_structure_code = p_cost_flex_stru_code
5421 and id_flex_code ='COST';
5422 END IF;
5423
5424 RETURN(l_cost_code);
5425 EXCEPTION
5426 WHEN OTHERS THEN
5427 hr_data_pump.fail('get_cost_flex_stru_num'
5428 , sqlerrm
5429 , p_cost_flex_stru_code
5430 );
5431 RAISE;
5432 END get_cost_flex_stru_num;
5433 --
5434 FUNCTION get_grade_flex_stru_num
5435 (p_data_pump_always_call IN varchar2
5436 ,p_grade_flex_stru_code IN VARCHAR2
5437 )
5438 RETURN BINARY_INTEGER is
5439 l_grade_code number;
5440 begin
5441
5442 IF p_grade_flex_stru_code is NULL then
5443
5444 return null;
5445
5446 ELSIF p_grade_flex_stru_code = hr_api_g_varchar2 then
5447
5448 return hr_api_g_number;
5449
5450 ELSE
5451 select id_flex_num into l_grade_code
5452 from fnd_id_flex_structures
5453 where id_flex_structure_code = p_grade_flex_stru_code
5454 and id_flex_code ='GRD';
5455
5456 END IF;
5457
5458 RETURN(l_grade_code);
5459 EXCEPTION
5460 WHEN OTHERS THEN
5461 hr_data_pump.fail('get_grade_flex_stru_num'
5462 , sqlerrm
5463 , p_grade_flex_stru_code
5464 );
5465 RAISE;
5466 END get_grade_flex_stru_num;
5467 --
5468 FUNCTION get_job_flex_stru_num
5469 (p_data_pump_always_call IN varchar2
5470 ,p_job_flex_stru_code IN VARCHAR2
5471 )
5472 RETURN BINARY_INTEGER is
5473 l_job_code number;
5474 begin
5475
5476 IF p_job_flex_stru_code is NULL then
5477
5478 return null;
5479
5480 ELSIF p_job_flex_stru_code = hr_api_g_varchar2 then
5481
5482 return hr_api_g_number;
5483
5484 ELSE
5485
5486 select id_flex_num into l_job_code
5487 from fnd_id_flex_structures
5488 where id_flex_structure_code = p_job_flex_stru_code
5489 and id_flex_code ='JOB';
5490 END IF;
5491
5492 RETURN(l_job_code);
5493 EXCEPTION
5494 WHEN OTHERS THEN
5495 hr_data_pump.fail('get_job_flex_stru_num'
5496 , sqlerrm
5497 , p_job_flex_stru_code
5498 );
5499 RAISE;
5500 END get_job_flex_stru_num;
5501 --
5502 FUNCTION get_position_flex_stru_num
5503 (p_data_pump_always_call IN varchar2
5504 ,p_position_flex_stru_code IN VARCHAR2
5505 )
5506 RETURN BINARY_INTEGER is
5507 l_position_code number;
5508 begin
5509
5510 IF p_position_flex_stru_code is NULL then
5511
5512 return null;
5513
5514 ELSIF p_position_flex_stru_code = hr_api_g_varchar2 then
5515
5516 return hr_api_g_number;
5517
5518 ELSE
5519
5520 select id_flex_num into l_position_code
5521 from fnd_id_flex_structures
5522 where id_flex_structure_code = p_position_flex_stru_code
5523 and id_flex_code ='POS';
5524 END IF;
5525
5526 RETURN(l_position_code);
5527 EXCEPTION
5528 WHEN OTHERS THEN
5529 hr_data_pump.fail('get_position_flex_stru_num'
5530 , sqlerrm
5531 , p_position_flex_stru_code
5532 );
5533 RAISE;
5534 END get_position_flex_stru_num;
5535 --
5536 FUNCTION get_group_flex_stru_num
5537 (p_data_pump_always_call IN varchar2
5538 ,p_group_flex_stru_code IN VARCHAR2
5539 )
5540 RETURN BINARY_INTEGER is
5541 l_group_code number;
5542 begin
5543
5544 IF p_group_flex_stru_code is NULL then
5545
5546 return null;
5547
5548 ELSIF p_group_flex_stru_code = hr_api_g_varchar2 then
5549
5550 return hr_api_g_number;
5551
5552 ELSE
5553
5554 select id_flex_num into l_group_code
5555 from fnd_id_flex_structures
5556 where id_flex_structure_code = p_group_flex_stru_code
5557 and id_flex_code ='GRP';
5558 END IF;
5559
5560 RETURN(l_group_code);
5561 EXCEPTION
5562 WHEN OTHERS THEN
5563 hr_data_pump.fail('get_group_flex_stru_num'
5564 , sqlerrm
5565 , p_group_flex_stru_code
5566 );
5567 RAISE;
5568 END get_group_flex_stru_num;
5569 --
5570 FUNCTION get_competence_flex_stru_num
5571 (p_data_pump_always_call IN varchar2
5572 ,p_competence_flex_stru_code IN VARCHAR2
5573 )
5574 RETURN BINARY_INTEGER is
5575 l_competence_code number;
5576 begin
5577
5578 IF p_competence_flex_stru_code is NULL then
5579
5580 return null;
5581
5582 ELSIF p_competence_flex_stru_code = hr_api_g_varchar2 then
5583
5584 return hr_api_g_number;
5585
5586 ELSE
5587
5588 select id_flex_num into l_competence_code
5589 from fnd_id_flex_structures
5590 where id_flex_structure_code = p_competence_flex_stru_code
5591 and id_flex_code ='CMP';
5592 END IF;
5593
5594 RETURN(l_competence_code);
5595 EXCEPTION
5596 WHEN OTHERS THEN
5597 hr_data_pump.fail('get_competence_flex_stru_num'
5598 , sqlerrm
5599 , p_competence_flex_stru_code
5600 );
5601 RAISE;
5602 END get_competence_flex_stru_num;
5603 --
5604 --
5605 FUNCTION get_sec_group_id
5606 (p_data_pump_always_call IN varchar2
5607 ,p_security_group_name IN VARCHAR2
5608 )
5609 RETURN BINARY_INTEGER is
5610 l_get_sec_group_id number;
5611 begin
5612
5613 IF p_security_group_name is NULL then
5614
5615 return null;
5616
5617 ELSIF p_security_group_name = hr_api_g_varchar2 then
5618
5619 return hr_api_g_number;
5620
5621 ELSE
5622
5623 select security_group_id into l_get_sec_group_id
5624 from fnd_security_groups_tl
5625 where upper(security_group_name) = upper(p_security_group_name)
5626 and language = userenv('LANG');
5627
5628 END IF;
5629
5630 RETURN(l_get_sec_group_id);
5631 EXCEPTION
5632 WHEN OTHERS THEN
5633 hr_data_pump.fail('get_sec_group_id'
5634 , sqlerrm
5635 , p_security_group_name
5636 );
5637 RAISE;
5638 END get_sec_group_id;
5639 --
5640 FUNCTION get_security_profile_id
5641 (p_data_pump_always_call IN VARCHAR2
5642 ,p_security_profile_name IN VARCHAR2
5643 ,p_business_group_id IN NUMBER
5644 )
5645 RETURN BINARY_INTEGER is
5646 l_sec_profile_id number;
5647 begin
5648
5649 IF p_security_profile_name is NULL then
5650
5651 return null;
5652
5653 ELSIF p_security_profile_name = hr_api_g_varchar2 then
5654
5655 return hr_api_g_number;
5656
5657 ELSE
5658
5659 select security_profile_id into l_sec_profile_id
5660 from per_security_profiles
5661 where upper(security_profile_name) = upper(p_security_profile_name)
5662 and business_group_id = p_business_group_id;
5663
5664 END IF;
5665
5666 RETURN(l_sec_profile_id);
5667 EXCEPTION
5668 WHEN OTHERS THEN
5669 hr_data_pump.fail('get_security_profile_id'
5670 , sqlerrm
5671 , p_security_profile_name
5672 , p_business_group_id
5673 );
5674 RAISE;
5675 END get_security_profile_id;
5676 --
5677 --
5678 FUNCTION get_parent_organization_id
5679 ( p_parent_organization_name in varchar2,
5680 p_business_group_id in number,
5681 p_effective_date in date,
5682 p_language_code in varchar2
5683 ) RETURN BINARY_INTEGER is
5684 l_organization_id number;
5685 BEGIN
5686 IF p_parent_organization_name is NULL then
5687
5688 return null;
5689
5690 ELSIF p_parent_organization_name = hr_api_g_varchar2 then
5691
5692 return hr_api_g_number;
5693
5694 ELSE
5695
5696 select org.organization_id
5697 into l_organization_id
5698 from hr_all_organization_units org
5699 , hr_all_organization_units_tl orgtl
5700 where orgtl.name = p_parent_organization_name
5701 and orgtl.language = p_language_code
5702 and org.organization_id = orgtl.organization_id
5703 and (org.business_group_id = p_business_group_id
5704 or p_business_group_id is null); --Bug 3823374
5705 END IF;
5706 return(l_organization_id);
5707 EXCEPTION
5708 WHEN OTHERS THEN
5709 hr_data_pump.fail('get_parent_organization_id', sqlerrm, p_parent_organization_name,
5710 p_business_group_id, p_effective_date, p_language_code);
5711 raise;
5712 end get_parent_organization_id;
5713 --
5714 --
5715 FUNCTION get_child_organization_id
5716 ( p_child_organization_name in varchar2,
5717 p_business_group_id in number,
5718 p_effective_date in date,
5719 p_language_code in varchar2
5720 ) RETURN BINARY_INTEGER is
5721 l_organization_id number;
5722 BEGIN
5723 IF p_child_organization_name is NULL then
5724
5725 return null;
5726
5727 ELSIF p_child_organization_name = hr_api_g_varchar2 then
5728
5729 return hr_api_g_number;
5730
5731 ELSE
5732 select org.organization_id
5733 into l_organization_id
5734 from hr_all_organization_units org
5735 , hr_all_organization_units_tl orgtl
5736 where orgtl.name = p_child_organization_name
5737 and orgtl.language = p_language_code
5738 and org.organization_id = orgtl.organization_id
5739 and (org.business_group_id = p_business_group_id
5740 or p_business_group_id is null); --Bug fix 3823374
5741 END IF;
5742 --
5743 return(l_organization_id);
5744 EXCEPTION
5745 WHEN OTHERS THEN
5746 hr_data_pump.fail('get_child_organization_id', sqlerrm, p_child_organization_name,
5747 p_business_group_id, p_effective_date, p_language_code);
5748 raise;
5749 end get_child_organization_id;
5750 --
5751 --
5752 function get_person_extra_info_id
5753 (
5754 p_person_extra_info_user_key in varchar2
5755 ) return number is
5756 l_person_extra_info_id number;
5757 begin
5758 l_person_extra_info_id := user_key_to_id( p_person_extra_info_user_key );
5759 return(l_person_extra_info_id);
5760 exception
5761 when others then
5762 hr_data_pump.fail('get_person_extra_info_id', sqlerrm, p_person_extra_info_user_key);
5763 raise;
5764 end get_person_extra_info_id;
5765 --
5766 --
5767 function get_person_extra_info_ovn
5768 ( p_person_extra_info_user_key in varchar2
5769 ) return number is
5770 l_ovn number;
5771 begin
5772 select pei.object_version_number
5773 into l_ovn
5774 from per_people_extra_info pei,
5775 hr_pump_batch_line_user_keys key
5776 where key.user_key_value = p_person_extra_info_user_key
5777 and pei.person_extra_info_id = key.unique_key_id;
5778 return(l_ovn);
5779 exception
5780 when others then
5781 hr_data_pump.fail('get_person_extra_info_ovn', sqlerrm, p_person_extra_info_user_key);
5782 raise;
5783 end get_person_extra_info_ovn;
5784
5785 --
5786 --
5787 /* GET_EMP_FED_TAX_INF_ID - requires user key */
5788 function GET_EMP_FED_TAX_INF_ID
5789 (
5790 P_EMP_FED_TAX_INF_USER_KEY in varchar2
5791 ) return number is
5792 l_emp_fed_tax_inf_id number;
5793 begin
5794 l_emp_fed_tax_inf_id := user_key_to_id( P_EMP_FED_TAX_INF_USER_KEY );
5795 return(l_emp_fed_tax_inf_id);
5796 exception
5797 when others then
5798 hr_data_pump.fail('GET_EMP_FED_TAX_INF_ID', sqlerrm, P_EMP_FED_TAX_INF_USER_KEY);
5799 raise;
5800 end GET_EMP_FED_TAX_INF_ID;
5801
5802
5803 --
5804 /* returns a Canada Employee federal tax Inf object version number */
5805 function GET_CA_EMP_FEDTAX_INF_OVN
5806 (
5807 P_EMP_FED_TAX_INF_USER_KEY in varchar2,
5808 p_effective_date in date
5809 ) return number is
5810 l_ovn number;
5811 begin
5812 select rules.object_version_number
5813 into l_ovn
5814 from PAY_CA_EMP_FED_TAX_INFO_F rules,
5815 hr_pump_batch_line_user_keys key
5816 where key.user_key_value = P_EMP_FED_TAX_INF_USER_KEY
5817 and rules.EMP_FED_TAX_INF_ID = key.unique_key_id
5818 and p_effective_date between
5819 rules.effective_start_date and rules.effective_end_date;
5820 return(l_ovn);
5821 exception
5822 when others then
5823 hr_data_pump.fail('GET_CA_EMP_FEDTAX_INF_OVN', sqlerrm,
5824 P_EMP_FED_TAX_INF_USER_KEY, p_effective_date);
5825 raise;
5826 end GET_CA_EMP_FEDTAX_INF_OVN;
5827
5828 --
5829 --
5830 /* GET_EMP_PROVINCE_TAX_INF_ID - requires user key */
5831 function GET_EMP_PROVINCE_TAX_INF_ID
5832 (
5833 P_EMP_PROV_TAX_INF_USER_KEY in varchar2
5834 ) return number is
5835 l_emp_prov_tax_inf_id number;
5836 begin
5837 l_emp_prov_tax_inf_id := user_key_to_id( P_EMP_PROV_TAX_INF_USER_KEY );
5838 return(l_emp_prov_tax_inf_id);
5839 exception
5840 when others then
5841 hr_data_pump.fail('GET_EMP_PROVINCE_TAX_INF_ID', sqlerrm, P_EMP_PROV_TAX_INF_USER_KEY);
5842 raise;
5843 end GET_EMP_PROVINCE_TAX_INF_ID;
5844
5845
5846 --
5847 /* returns a Canada Employee federal tax Inf object version number */
5848 function GET_CA_EMP_PRVTAX_INF_OVN
5849 (
5850 P_EMP_PROV_TAX_INF_USER_KEY in varchar2,
5851 p_effective_date in date
5852 ) return number is
5853 l_ovn number;
5854 begin
5855 select rules.object_version_number
5856 into l_ovn
5857 from PAY_CA_EMP_PROV_TAX_INFO_F rules,
5858 hr_pump_batch_line_user_keys key
5859 where key.user_key_value = P_EMP_PROV_TAX_INF_USER_KEY
5860 and rules.EMP_PROVINCE_TAX_INF_ID = key.unique_key_id
5861 and p_effective_date between
5862 rules.effective_start_date and rules.effective_end_date;
5863 return(l_ovn);
5864 exception
5865 when others then
5866 hr_data_pump.fail('GET_CA_EMP_PRVTAX_INF_OVN', sqlerrm,
5867 P_EMP_PROV_TAX_INF_USER_KEY, p_effective_date);
5868 raise;
5869 end GET_CA_EMP_PRVTAX_INF_OVN;
5870
5871
5872 --
5873 /*
5874 * Get ID initialisation section.
5875 */
5876 begin
5877 -- Initialise the debugging information structure.
5878 null;
5879
5880 end hr_pump_get;
[Home] [Help]