1 package body hr_pump_get as
2 /* $Header: hrdpget.pkb 120.1 2005/07/08 20:10:10 ssattini noship $ */
3 /*
4 NOTES
5 Please refer to the package header for documentation on these
6 functions.
7 */
8 /*---------------------------------------------------------------------------*/
9 /*----------------------- constant definitions ------------------------------*/
10 /*---------------------------------------------------------------------------*/
11 END_OF_TIME constant date := to_date('4712/12/31', 'YYYY/MM/DD');
12 START_OF_TIME constant date := to_date('0001/01/01', 'YYYY/MM/DD');
13 HR_API_G_VARCHAR2 constant varchar2(128) := hr_api.g_varchar2;
14 HR_API_G_NUMBER constant number := hr_api.g_number;
15 HR_API_G_DATE constant date := hr_api.g_date;
16
17 /*---------------------------------------------------------------------------*/
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
1298 return(l_elig_per_id);
1295 return null;
1296 end if;
1297 l_elig_per_id := user_key_to_id( p_elig_per_user_key );
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 /* returns a vendor_id */
2522 function get_vendor_id
2523 (
2524 p_vendor_name in varchar2
2525 ) return number is
2526 l_vendor_id number;
2527 begin
2528 select pov.vendor_id
2529 into l_vendor_id
2530 from po_vendors pov
2531 where pov.vendor_name = p_vendor_name;
2532 return(l_vendor_id);
2533 exception
2534 when others then
2535 hr_data_pump.fail('get_vendor_id', sqlerrm, p_vendor_name);
2536 raise;
2537 end get_vendor_id;
2538
2539 /* returns an assignment_status_type_id */
2540 function get_assignment_status_type_id
2541 (
2542 p_user_status in varchar2,
2543 p_business_group_id in number,
2544 p_language_code in varchar2
2545 ) return number is
2546 l_assignment_status_type_id number;
2547 begin
2548 -- Need to check for amended row in per_ass_status_type_amends first.
2549 begin
2550 select typ.assignment_status_type_id
2551 into l_assignment_status_type_id
2552 from per_ass_status_type_amends_tl typtl,
2553 per_ass_status_type_amends typ
2554 where typtl.user_status = p_user_status
2555 and typ.business_group_id + 0 = p_business_group_id
2556 and typ.ass_status_type_amend_id = typtl.ass_status_type_amend_id
2557 and typtl.LANGUAGE = p_language_code;
2558 return(l_assignment_status_type_id);
2559 exception
2560 when no_data_found then
2561 null;
2562 when others then
2563 raise;
2564 end;
2565
2566 -- Can look in per_assignment_status_types now.
2567 select typ.assignment_status_type_id
2568 into l_assignment_status_type_id
2569 from per_assignment_status_types_tl typtl,
2570 per_assignment_status_types typ
2571 where typtl.user_status = p_user_status
2572 and typ.assignment_status_type_id = typtl.assignment_status_type_id
2573 and typtl.LANGUAGE = p_language_code
2574 and
2575 (
2576 (typ.business_group_id is null and typ.legislation_code is null)
2577 or
2578 (typ.business_group_id is not null
2579 and typ.business_group_id + 0 = p_business_group_id)
2580 or
2581 (typ.business_group_id is null
2582 and typ.legislation_code is not null
2583 and typ.legislation_code =
2584 (select legislation_code from per_business_groups
2585 where business_group_id = p_business_group_id))
2586 );
2587 return(l_assignment_status_type_id);
2588 exception
2589 when others then
2590 hr_data_pump.fail('get_assignment_status_type_id', sqlerrm, p_user_status,
2591 p_business_group_id, p_language_code);
2592 raise;
2593 end get_assignment_status_type_id;
2594
2595 /* returns an organization_id */
2596 function get_organization_id
2597 (
2598 p_organization_name in varchar2,
2599 p_business_group_id in number,
2600 p_effective_date in date
2601 ,p_language_code in varchar2
2602 ) return number is
2603 l_organization_id number;
2604 begin
2605 select org.organization_id
2606 into l_organization_id
2607 from hr_all_organization_units org
2608 , hr_all_organization_units_tl orgtl
2609 where orgtl.name = p_organization_name
2610 and orgtl.language = p_language_code
2611 and org.organization_id = orgtl.organization_id
2612 and org.business_group_id + 0 = p_business_group_id;
2613 return(l_organization_id);
2614 exception
2615 when others then
2616 hr_data_pump.fail('get_organization_id', sqlerrm, p_organization_name,
2617 p_business_group_id, p_effective_date, p_language_code);
2618 raise;
2619 end get_organization_id;
2620
2621 /* returns a establishment_org_id */
2622 function get_establishment_org_id
2623 (
2624 p_establishment_org_name in varchar2,
2625 p_business_group_id in number,
2626 p_effective_date in date
2627 , p_language_code in varchar2
2628 ) return number is
2629 l_establishment_org_id number;
2630 begin
2631 l_establishment_org_id :=
2632 get_organization_id( p_establishment_org_name, p_business_group_id,
2633 p_effective_date, p_language_code );
2634 return(l_establishment_org_id);
2635 exception
2636 when others then
2637 hr_data_pump.fail('get_establishment_org_id', sqlerrm,
2638 p_establishment_org_name, p_business_group_id,
2639 p_effective_date, p_language_code);
2640 raise;
2641 end get_establishment_org_id;
2642
2643 /* returns a source_organization_id */
2644 function get_source_organization_id
2645 (
2646 p_source_organization_name in varchar2,
2647 p_business_group_id in number,
2648 p_effective_date in date
2649 , p_language_code in varchar2
2650 ) return number is
2651 l_source_organization_id number;
2652 begin
2653 l_source_organization_id :=
2654 get_organization_id( p_source_organization_name, p_business_group_id,
2655 p_effective_date, p_language_code );
2656 return(l_source_organization_id);
2657 exception
2658 when others then
2659 hr_data_pump.fail('get_source_organization_id', sqlerrm,
2660 p_source_organization_name, p_business_group_id,
2661 p_effective_date, p_language_code);
2662 raise;
2663 end get_source_organization_id;
2664
2665 /* returns a grade_id */
2666 function get_grade_id
2667 (
2668 p_grade_name in varchar2,
2669 p_business_group_id in number,
2670 p_effective_date in date
2671 ) return number is
2672 l_grade_id number;
2673 begin
2674 -- Note that the grade name can be null on the
2675 -- per_grades table, but I think grades are created
2676 -- with a name - otherwise identifying them would be
2677 -- rather difficult...
2678 select gra.grade_id
2679 into l_grade_id
2680 from per_grades_vl gra
2681 where gra.name = p_grade_name
2682 and gra.business_group_id + 0 = p_business_group_id;
2683 return(l_grade_id);
2684 exception
2685 when others then
2686 hr_data_pump.fail('get_grade_id', sqlerrm, p_grade_name, p_business_group_id, p_effective_date);
2687 raise;
2688 end get_grade_id;
2689
2690 /* returns a grade_id */
2691 function get_entry_grade_id
2692 (
2693 p_entry_grade_name in varchar2,
2694 p_business_group_id in number,
2695 p_effective_date in date
2696 ) return number is
2697 l_entry_grade_id number;
2698 begin
2699 l_entry_grade_id :=
2700 get_grade_id
2701 (p_grade_name => p_entry_grade_name
2702 ,p_business_group_id => p_business_group_id
2703 ,p_effective_date => p_effective_date
2704 );
2705 return(l_entry_grade_id);
2706 exception
2707 when others then
2708 raise;
2709 end get_entry_grade_id;
2710
2711 /* return availability_status_id */
2712 function get_availability_status_id
2713 (p_shared_type_name in varchar2
2714 ,p_system_type_cd in varchar2
2715 ,p_business_group_id in number
2716 ,p_language_code in varchar2
2717 ) return number is
2718 cursor csr_lookup is
2719 select pst.shared_type_id
2720 from per_shared_types pst
2721 , per_shared_types_tl psttl
2722 where psttl.shared_type_name = p_shared_type_name
2723 and psttl.language = p_language_code
2724 and pst.shared_type_id = psttl.shared_type_id
2725 and pst.lookup_type = 'POSITION_AVAILABILITY_STATUS'
2726 and pst.system_type_cd = p_system_type_cd
2727 and nvl(pst.business_group_id, p_business_group_id) = p_business_group_id;
2728 --
2729 v_shared_type_id number(15) := null;
2730 begin
2731 if p_shared_type_name is not null then
2732 open csr_lookup;
2733 fetch csr_lookup into v_shared_type_id;
2734 close csr_lookup;
2735 end if;
2736 return v_shared_type_id;
2737 exception
2738 when others then
2739 hr_data_pump.fail('get_availability_status_id', sqlerrm,
2740 p_shared_type_name, p_system_type_cd,
2741 p_business_group_id, p_language_code);
2742 raise;
2743 end get_availability_status_id;
2744
2745 /* returns a position_id */
2746 function get_position_id
2747 (
2748 p_position_name in varchar2,
2749 p_business_group_id in number,
2750 p_effective_date in date
2751 ) return number is
2752 l_position_id number;
2753 begin
2754 select pos.position_id
2755 into l_position_id
2756 from hr_all_positions_f pos
2757 where pos.name = p_position_name
2758 and pos.business_group_id + 0 = p_business_group_id
2759 and p_effective_date between
2760 pos.effective_start_date and pos.effective_end_date;
2761 return(l_position_id);
2762 exception
2763 when others then
2764 hr_data_pump.fail('get_position_id', sqlerrm, p_position_name,
2765 p_business_group_id, p_effective_date);
2766 raise;
2767 end get_position_id;
2768
2769 /* returns a successor_position_id */
2770 function get_successor_position_id
2771 (
2772 p_successor_position_name in varchar2,
2773 p_business_group_id in number,
2774 p_effective_date in date
2775 ) return number is
2776 l_pos_id number;
2777 begin
2778 -- Just call the get_position_id function.
2779 l_pos_id := get_position_id(p_successor_position_name,
2780 p_business_group_id,
2781 p_effective_date);
2782 return(l_pos_id);
2783 exception
2787 p_effective_date);
2784 when others then
2785 hr_data_pump.fail('get_successor_position_id', sqlerrm,
2786 p_successor_position_name, p_business_group_id,
2788 raise;
2789 end get_successor_position_id;
2790
2791 /* returns a relief_position_id */
2792 function get_relief_position_id
2793 (
2794 p_relief_position_name in varchar2,
2795 p_business_group_id in number,
2796 p_effective_date in date
2797 ) return number is
2798 l_pos_id number;
2799 begin
2800 -- Just call the get_position_id function.
2801 l_pos_id := get_position_id(p_relief_position_name,
2802 p_business_group_id,
2803 p_effective_date);
2804 return(l_pos_id);
2805 exception
2806 when others then
2807 hr_data_pump.fail('get_relief_position_id', sqlerrm,
2808 p_relief_position_name, p_business_group_id,
2809 p_effective_date);
2810 raise;
2811 end get_relief_position_id;
2812
2813 /* returns a prior_position_id */
2814 function get_prior_position_id
2815 (
2816 p_prior_position_name in varchar2,
2817 p_business_group_id in number,
2818 p_effective_date in date
2819 ) return number is
2820 l_pos_id number;
2821 begin
2822 -- Just call the get_position_id function.
2823 l_pos_id := get_position_id(p_prior_position_name,
2824 p_business_group_id,
2825 p_effective_date);
2826 return(l_pos_id);
2827 exception
2828 when others then
2829 hr_data_pump.fail('get_prior_position_id', sqlerrm,
2830 p_prior_position_name, p_business_group_id,
2831 p_effective_date);
2832 raise;
2833 end get_prior_position_id;
2834
2835 /* returns a supervisor_position_id */
2836 function get_supervisor_position_id
2837 (
2838 p_supervisor_position_name in varchar2,
2839 p_business_group_id in number,
2840 p_effective_date in date
2841 ) return number is
2842 l_pos_id number;
2843 begin
2844 -- Just call the get_position_id function.
2845 l_pos_id := get_position_id(p_supervisor_position_name,
2846 p_business_group_id,
2847 p_effective_date);
2848 return(l_pos_id);
2849 exception
2850 when others then
2851 hr_data_pump.fail('get_supervisor_position_id', sqlerrm,
2852 p_supervisor_position_name, p_business_group_id,
2853 p_effective_date);
2854 raise;
2855 end get_supervisor_position_id;
2856
2857 /* returns a job_id */
2858 function get_job_id
2859 (
2860 p_job_name in varchar2,
2861 p_effective_date in date,
2862 p_business_group_id in number
2863 ) return number is
2864 l_job_id number;
2865 begin
2866 select job.job_id
2867 into l_job_id
2868 from per_jobs_vl job
2869 where job.name = p_job_name
2870 and job.business_group_id + 0 = p_business_group_id;
2871 return(l_job_id);
2872 exception
2873 when others then
2874 hr_data_pump.fail('get_job_id', sqlerrm,
2875 p_job_name, p_effective_date, p_business_group_id);
2876 raise;
2877 end get_job_id;
2878
2879 /* returns a payroll_id */
2880 function get_payroll_id
2881 (
2882 p_payroll_name in varchar2,
2883 p_business_group_id in number,
2884 p_effective_date in date
2885 ) return number is
2886 l_payroll_id number;
2887 begin
2888 select pay.payroll_id
2889 into l_payroll_id
2890 from pay_payrolls_f pay
2891 where pay.payroll_name = p_payroll_name
2892 and pay.business_group_id + 0 = p_business_group_id
2893 and p_effective_date between
2894 pay.effective_start_date and pay.effective_end_date;
2895 return(l_payroll_id);
2896 exception
2897 when others then
2898 hr_data_pump.fail('get_payroll_id', sqlerrm, p_payroll_name,
2899 p_business_group_id, p_effective_date);
2900 raise;
2901 end get_payroll_id;
2902
2903 /* returns a pay_freq_payroll_id */
2904 function get_pay_freq_payroll_id
2905 (
2906 p_pay_freq_payroll_name in varchar2,
2907 p_business_group_id in number,
2908 p_effective_date in date
2909 ) return number is
2910 begin
2911 return
2912 get_payroll_id
2913 (p_payroll_name => p_pay_freq_payroll_name
2914 ,p_business_group_id => p_business_group_id
2915 ,p_effective_date => p_effective_date
2916 );
2917 exception
2918 when others then
2919 raise;
2920 end get_pay_freq_payroll_id;
2921
2922 /* Returns a location_id for the update_location APIs. */
2923 function get_location_id_update
2924 (
2925 p_existing_location_code in varchar2,
2926 p_business_group_id in number,
2927 p_language_code in varchar2
2928 ) return number is
2929 l_location_id number;
2930 begin
2931 l_location_id :=
2932 get_location_id(p_existing_location_code, p_business_group_id,
2933 p_language_code);
2934 return(l_location_id);
2935 exception
2936 when others then
2937 hr_data_pump.fail('get_location_id_update', sqlerrm,
2938 p_existing_location_code,
2939 p_business_group_id, p_language_code);
2940 raise;
2941 end get_location_id_update;
2942
2943 /* returns a location_id */
2944 function get_location_id
2945 (
2946 p_location_code in varchar2,
2947 p_business_group_id in number,
2948 p_language_code in varchar2
2949 ) return number is
2950 l_location_id number;
2951 begin
2952 select loc.location_id
2953 into l_location_id
2954 from hr_locations_all loc,
2955 hr_locations_all_tl lot
2956 where lot.location_code = p_location_code
2957 and lot.language = p_language_code
2958 and lot.location_id = loc.location_id
2959 and (loc.business_group_id is null or
2960 loc.business_group_id + 0 = p_business_group_id);
2961 return(l_location_id);
2962 exception
2963 when others then
2964 hr_data_pump.fail('get_location_id', sqlerrm, p_location_code,
2965 p_business_group_id, p_language_code);
2966 raise;
2967 end get_location_id;
2968
2969 /* returns receiver id */
2970 function get_designated_receiver_id
2971 (
2972 p_designated_receiver_name Varchar2,
2973 p_business_group_id Number,
2974 p_effective_date Date
2975 ) return number Is
2976 l_designated_receiver_id Number;
2977 begin
2978 Select person_id
2979 Into l_designated_receiver_id
2980 From per_all_people_f
2981 Where employee_number Is Not Null
2982 and business_group_id = p_business_group_id
2983 and p_effective_date between effective_start_date and effective_end_date
2984 and full_name = p_designated_receiver_name;
2985 return l_designated_receiver_id;
2986 Exception
2987 When Others Then
2988 hr_data_pump.fail('get_designated_receiver_id',sqlerrm,p_designated_receiver_name,
2989 p_business_group_id);
2990 raise;
2991 end get_designated_receiver_id;
2992
2993 function get_ship_to_location_id
2994 (
2995 p_ship_to_location_code in varchar2,
2996 p_business_group_id in number,
2997 p_language_code in varchar2
2998 ) return number is
2999 l_location_id number;
3000 begin
3001 select loc.location_id
3002 into l_location_id
3003 from hr_locations_all loc,
3004 hr_locations_all_tl lot
3005 where lot.location_code = p_ship_to_location_code
3006 and lot.language = p_language_code
3007 and lot.location_id = loc.location_id
3008 and (loc.business_group_id is null or
3009 loc.business_group_id + 0 = p_business_group_id);
3010 return(l_location_id);
3011 exception
3012 when others then
3013 hr_data_pump.fail('get_ship_to_location_id', sqlerrm, p_ship_to_location_code,
3014 p_business_group_id, p_language_code);
3015 raise;
3016 end get_ship_to_location_id;
3017
3018 /* returns a pay_basis_id */
3019 function get_pay_basis_id
3020 (
3021 p_pay_basis_name in varchar2,
3022 p_business_group_id in number
3023 ) return number is
3024 l_pay_basis_id number;
3025 begin
3026 select ppb.pay_basis_id
3027 into l_pay_basis_id
3028 from per_pay_bases ppb
3029 where ppb.name = p_pay_basis_name
3030 and ppb.business_group_id + 0 = p_business_group_id;
3031 return(l_pay_basis_id);
3032 exception
3033 when others then
3034 hr_data_pump.fail('get_pay_basis_id', sqlerrm, p_pay_basis_name,
3035 p_business_group_id);
3036 raise;
3037 end get_pay_basis_id;
3038
3039 /* returns a recruitment_activity_id */
3040 function get_recruitment_activity_id
3041 (
3042 p_recruitment_activity_name in varchar2,
3043 p_business_group_id in number,
3044 p_effective_date in date
3045 ) return number is
3046 l_raid number;
3047 begin
3048 select pra.recruitment_activity_id
3049 into l_raid
3050 from per_recruitment_activities pra
3051 where pra.name = p_recruitment_activity_name
3052 and pra.business_group_id + 0 = p_business_group_id
3053 and p_effective_date between
3054 pra.date_start and nvl(pra.date_end, END_OF_TIME);
3055 return(l_raid);
3056 exception
3057 when others then
3058 hr_data_pump.fail('get_recruitment_activity_id', sqlerrm,
3059 p_recruitment_activity_name, p_business_group_id,
3060 p_effective_date);
3061 raise;
3062 end get_recruitment_activity_id;
3063
3064 /* returns a vacancy_id */
3065 function get_vacancy_id
3066 (
3067 p_vacancy_user_key in varchar2
3068 ) return number is
3069 l_vacancy_id number;
3070 begin
3071 l_vacancy_id := user_key_to_id( p_vacancy_user_key );
3072 return(l_vacancy_id);
3073 exception
3074 when others then
3075 hr_data_pump.fail('get_vacancy_id', sqlerrm, p_vacancy_user_key );
3076 raise;
3077 end get_vacancy_id;
3078
3079 /* returns an org_payment_method_id */
3080 function get_org_payment_method_id
3081 (
3082 p_org_payment_method_user_key in varchar2
3083 ) return number is
3084 l_opmid number;
3085 begin
3086 l_opmid := user_key_to_id( p_org_payment_method_user_key );
3087 return(l_opmid);
3088 exception
3089 when others then
3090 hr_data_pump.fail('get_org_payment_method_id', sqlerrm,
3091 p_org_payment_method_user_key );
3092 raise;
3093 end get_org_payment_method_id;
3094
3095 /* returns a payee organization_id */
3096 function get_payee_org_id
3097 (
3098 p_payee_organization_name in varchar2,
3099 p_business_group_id in number,
3100 p_effective_date in date
3101 , p_language_code in varchar2
3102 ) return number is
3103 l_organization_id number;
3104 begin
3105 l_organization_id :=
3106 get_organization_id( p_payee_organization_name, p_business_group_id,
3107 p_effective_date, p_language_code );
3108 return(l_organization_id);
3109 exception
3110 when others then
3111 hr_data_pump.fail('get_payee_org_id', sqlerrm, p_payee_organization_name,
3112 p_business_group_id, p_effective_date, p_language_code);
3113 raise;
3114 end get_payee_org_id;
3115
3116 /* return payee person_id - requires user key */
3117 function get_payee_person_id
3118 (
3119 p_payee_person_user_key in varchar2
3120 ) return number is
3121 l_person_id number;
3122 begin
3123 l_person_id := get_person_id( p_payee_person_user_key );
3124 return(l_person_id);
3125 exception
3126 when others then
3127 hr_data_pump.fail('get_payee_person_id', sqlerrm, p_payee_person_user_key);
3128 raise;
3129 end get_payee_person_id;
3130
3131 /* return payee_id for an organization or person payee. */
3132 function get_payee_id
3133 (
3134 p_data_pump_always_call in varchar2,
3135 p_payee_type in varchar2,
3136 p_business_group_id in number,
3137 p_payee_org in varchar2 default null,
3138 p_payee_person_user_key in varchar2 default null,
3139 p_effective_date in date
3140 , p_language_code in varchar2
3141 ) return number is
3142 l_payee_id number;
3143 begin
3144 --
3145 -- Check for a payee person.
3146 --
3147 if p_payee_type = 'P' and p_payee_person_user_key is not null then
3148 l_payee_id := get_payee_person_id( p_payee_person_user_key );
3149 return(l_payee_id);
3150 --
3151 -- Check for a payee organization.
3152 --
3153 elsif p_payee_type = 'O' and p_payee_org is not null then
3154 l_payee_id :=
3155 get_payee_org_id( p_payee_org, p_business_group_id, p_effective_date,
3156 p_language_code );
3157 return(l_payee_id);
3158 --
3159 -- Everything is NULL so return NULL.
3160 --
3161 elsif p_payee_type is null and p_payee_person_user_key is null and
3162 p_payee_org is null then
3163 return null;
3164 --
3165 -- Everything is HR_API-defaulted, so return HR_API default value.
3166 -- User Keys are set to NULL, if defaulted, on UPDATE.
3167 --
3168 elsif p_payee_type = HR_API_G_VARCHAR2 and
3169 (p_payee_person_user_key is null or p_payee_person_user_key =
3170 HR_API_G_VARCHAR2) and p_payee_org = HR_API_G_VARCHAR2 then
3171 return HR_API_G_NUMBER;
3172 --
3173 -- User has supplied an erroneous combination of arguments.
3174 --
3175 else
3176 raise value_error;
3177 end if;
3178 exception
3179 when others then
3180 hr_data_pump.fail('get_payee_id', sqlerrm, p_payee_type, p_payee_org,
3181 p_payee_person_user_key, p_effective_date, p_language_code);
3182 raise;
3183 end get_payee_id;
3184
3185 /* returns a personal_payment_method_id */
3186 function get_personal_payment_method_id
3187 (
3188 p_personal_pay_method_user_key in varchar2
3189 ) return number is
3190 l_ppmid number;
3191 begin
3192 l_ppmid := user_key_to_id( p_personal_pay_method_user_key );
3193 return(l_ppmid);
3194 exception
3195 when others then
3196 hr_data_pump.fail('get_personal_payment_method_id', sqlerrm,
3197 p_personal_pay_method_user_key);
3198 raise;
3199 end get_personal_payment_method_id;
3200
3201 /* returns a set_of_books_id */
3202 function get_set_of_books_id
3203 (
3204 p_set_of_books_name varchar2
3205 ) return number is
3206 l_id number;
3207 begin
3208 select sob.set_of_books_id
3209 into l_id
3210 from gl_sets_of_books sob
3211 where sob.name = p_set_of_books_name;
3212 return(l_id);
3213 exception
3214 when others then
3215 hr_data_pump.fail('get_set_of_books_id', sqlerrm, p_set_of_books_name);
3216 raise;
3217 end get_set_of_books_id;
3218
3219 /* returns a tax_unit_id */
3220 function get_tax_unit_id
3221 (
3222 p_tax_unit_name in varchar2,
3223 p_effective_date in date
3224 ) return varchar2 is
3225 l_tax_unit_id number;
3226 begin
3227 select tax.tax_unit_id
3228 into l_tax_unit_id
3229 from hr_tax_units_v tax
3230 where tax.name = p_tax_unit_name;
3231 return(l_tax_unit_id);
3232 exception
3233 when others then
3234 hr_data_pump.fail('get_tax_unit_id', sqlerrm, p_tax_unit_name,
3238
3235 p_effective_date);
3236 raise;
3237 end get_tax_unit_id;
3239 /* returns a user_column_id for tax schedule */
3240 function get_work_schedule
3241 (
3242 p_work_schedule in varchar2,
3243 p_organization_name in varchar2,
3244 p_business_group_id in number,
3245 p_effective_date in date
3246 ,p_language_code in varchar2
3247 ) return number is
3248 l_id number;
3249 begin
3250 select puc.user_column_id
3251 into l_id
3252 from pay_user_columns puc,
3253 hr_organization_information hoi,
3254 hr_all_organization_units org
3255 , hr_all_organization_units_tl orgtl
3256 where orgtl.name = p_organization_name
3257 and orgtl.language = p_language_code
3258 and org.organization_id = orgtl.organization_id
3259 and org.business_group_id + 0 = p_business_group_id
3260 and puc.user_column_name = p_work_schedule
3261 and hoi.org_information_context = 'Work Schedule'
3262 and hoi.organization_id = org.organization_id
3263 and (puc.user_table_id = hoi.org_information1 or
3264 hoi.org_information1 is null);
3265 return(l_id);
3266 exception
3267 when others then
3268 hr_data_pump.fail('get_work_schedule', sqlerrm, p_work_schedule,
3269 p_organization_name, p_business_group_id,
3270 p_effective_date, p_language_code);
3271 raise;
3272 end get_work_schedule;
3273
3274 /* returns an establishment_id */
3275 function get_eeo_1_establishment_id
3276 (
3277 p_eeo_1_establishment in varchar2,
3278 p_business_group_id in number,
3279 p_effective_date in date
3280 ) return number is
3281 l_id number;
3282 begin
3283 select est.establishment_id
3284 into l_id
3285 from HR_ESTABLISHMENTS_V est
3286 where est.name = p_eeo_1_establishment
3287 and est.business_group_id + 0 = p_business_group_id;
3288 return(l_id);
3289 exception
3290 when others then
3291 hr_data_pump.fail('get_eeo_1_establishment_id', sqlerrm,
3292 p_eeo_1_establishment, p_business_group_id,
3293 p_effective_date);
3294 raise;
3295 end get_eeo_1_establishment_id;
3296
3297 /* get_program_application_id - standard who column */
3298 function get_program_application_id return number is
3299 begin
3300 return(null);
3301 end get_program_application_id;
3302
3303 /* get_program_id - standard who column */
3304 function get_program_id return number is
3305 begin
3306 return(null);
3307 end get_program_id;
3308
3309 /* get_request_id - standard who column */
3310 function get_request_id return number is
3311 begin
3312 return(null);
3313 end get_request_id;
3314
3315 /* get_creator_id - standard who column */
3316 function get_creator_id return number is
3317 begin
3318 return(null);
3319 end get_creator_id;
3320
3321 /* get_id_flex_num - requires user key */
3322 function get_id_flex_num( p_id_flex_num_user_key in varchar2 )
3323 return number is
3324 l_id_flex_num number;
3325 begin
3326 l_id_flex_num := user_key_to_id( p_id_flex_num_user_key );
3327 return(l_id_flex_num);
3328 exception
3329 when others then
3330 hr_data_pump.fail('get_id_flex_num', sqlerrm, p_id_flex_num_user_key);
3331 raise;
3332 end get_id_flex_num;
3333
3334 /* get_gr_grade_rule_id */
3335 function get_gr_grade_rule_id
3336 (
3337 p_grade_name in varchar2,
3338 p_rate_name in varchar2,
3339 p_business_group_id in number,
3340 p_effective_date in date
3341 )
3342 return number is
3343 l_grade_rule_id number;
3344 begin
3345 select pgr.grade_rule_id
3346 into l_grade_rule_id
3347 from pay_grade_rules_f pgr,
3348 per_grades_vl pg,
3349 pay_rates pr
3350 where pg.name = p_grade_name
3351 and pg.business_group_id + 0 = p_business_group_id
3352 and pr.name = p_rate_name
3353 and pr.business_group_id + 0 = p_business_group_id
3354 and pgr.rate_id = pr.rate_id
3355 and pgr.grade_or_spinal_point_id = pg.grade_id
3356 and pgr.rate_type = 'G'
3357 and pgr.business_group_id + 0 = p_business_group_id
3358 and p_effective_date between pgr.effective_start_date and
3359 pgr.effective_end_date;
3360 return(l_grade_rule_id);
3361 exception
3362 when others then
3363 hr_data_pump.fail('get_gr_grade_rule_id', sqlerrm, p_grade_name,
3364 p_rate_name, p_business_group_id, p_effective_date);
3365 raise;
3366 end get_gr_grade_rule_id;
3367
3368 /* get_pp_grade_rule_id */
3369 function get_pp_grade_rule_id
3370 (
3371 p_progression_point in varchar2,
3372 p_pay_scale in varchar2,
3373 p_rate_name in varchar2,
3374 p_business_group_id in number,
3375 p_effective_date in date
3376 )
3377 return number is
3378 l_grade_rule_id number;
3379 begin
3380 select pgr.grade_rule_id
3381 into l_grade_rule_id
3382 from per_parent_spines pps,
3383 per_spinal_points psp,
3384 pay_grade_rules_f pgr,
3385 pay_rates pr
3386 where pps.name = p_pay_scale
3387 and pps.business_group_id + 0 = p_business_group_id
3388 and psp.spinal_point = p_progression_point
3389 and psp.business_group_id + 0 = p_business_group_id
3390 and psp.parent_spine_id = pps.parent_spine_id
3391 and pr.name = p_rate_name
3392 and pr.business_group_id + 0 = p_business_group_id
3393 and pgr.rate_id = pr.rate_id
3394 and pgr.grade_or_spinal_point_id = psp.spinal_point_id
3395 and pgr.rate_type = 'SP'
3396 and pgr.business_group_id + 0 = p_business_group_id
3397 and p_effective_date between pgr.effective_start_date and
3398 pgr.effective_end_date;
3399 return( l_grade_rule_id );
3400 exception
3401 when others then
3402 hr_data_pump.fail('get_pp_grade_rule_id', sqlerrm, p_progression_point,
3403 p_pay_scale, p_rate_name, p_business_group_id,
3404 p_effective_date);
3405 raise;
3406 end get_pp_grade_rule_id;
3407
3408 /* get_ar_grade_rule_id */
3409 function get_ar_grade_rule_id
3410 (
3411 p_rate_name in varchar2,
3412 p_business_group_id in number,
3413 p_effective_date in date
3414 )
3415 return number is
3416 l_grade_rule_id number;
3417 begin
3418 select pgr.grade_rule_id
3419 into l_grade_rule_id
3420 from pay_grade_rules_f pgr,
3421 pay_rates pr
3422 where pr.name = p_rate_name
3423 and pr.business_group_id + 0 = p_business_group_id
3424 and pgr.rate_id = pr.rate_id
3425 and pgr.rate_type = 'A'
3426 and pgr.business_group_id + 0 = p_business_group_id
3427 and p_effective_date between pgr.effective_start_date and
3428 pgr.effective_end_date;
3429 return(l_grade_rule_id);
3430 exception
3431 when others then
3432 hr_data_pump.fail('get_ar_grade_rule_id', sqlerrm,
3433 p_rate_name, p_business_group_id, p_effective_date);
3434 raise;
3435 end get_ar_grade_rule_id;
3436
3437 function get_organization_structure_id
3438 (
3439 p_name in varchar2,
3440 p_business_group_id in number
3441 )
3442 return number is
3443 l_organization_structure_id number;
3444 begin
3445 select pos.organization_structure_id
3446 into l_organization_structure_id
3447 from per_organization_structures pos
3448 where pos.name = p_name
3449 and pos.business_group_id + 0 = p_business_group_id;
3450 return (l_organization_structure_id);
3451 exception
3452 when others then
3453 hr_data_pump.fail('get_organization_structure_id', sqlerrm, p_name, p_business_group_id);
3454 raise;
3455 end get_organization_structure_id;
3456
3457 function get_org_str_ver_id
3458 (
3459 p_business_group_id in number,
3460 p_organization_structure_id in number,
3461 p_date_from in date,
3462 p_version_number in number
3463 )
3464 return number is
3465 l_org_str_ver_id number;
3466 begin
3467 select osv.org_structure_version_id
3468 into l_org_str_ver_id
3469 from per_org_structure_versions osv
3470 where osv.organization_structure_id = p_organization_structure_id
3471 and osv.date_from = p_date_from
3472 and osv.version_number = p_version_number
3473 and osv.business_group_id + 0 = p_business_group_id;
3474 return (l_org_str_ver_id);
3475 exception
3476 when others then
3477 hr_data_pump.fail('get_org_str_ver_id', sqlerrm, p_business_group_id, p_organization_structure_id, p_date_from, p_version_number);
3478 raise;
3479 end get_org_str_ver_id;
3480
3481 /* get_spinal_point_id */
3482 function get_spinal_point_id
3483 (
3484 p_progression_point in varchar2,
3485 p_pay_scale in varchar2,
3486 p_business_group_id in number,
3487 p_effective_date in date
3488 )
3489 return number is
3490 l_spinal_point_id number;
3491 begin
3492 select psp.spinal_point_id
3493 into l_spinal_point_id
3494 from per_parent_spines pps,
3495 per_spinal_points psp
3496 where pps.name = p_pay_scale
3497 and pps.business_group_id + 0 = p_business_group_id
3498 and psp.spinal_point = p_progression_point
3499 and psp.business_group_id + 0 = p_business_group_id
3500 and psp.parent_spine_id = pps.parent_spine_id;
3501 return( l_spinal_point_id );
3502 exception
3503 when others then
3504 hr_data_pump.fail('get_spinal_point_id', sqlerrm, p_progression_point,
3505 p_pay_scale, p_business_group_id, p_effective_date);
3506 raise;
3507 end get_spinal_point_id;
3508
3509 /* get_at_period_of_service_id */
3510 function get_at_period_of_service_id
3511 (
3512 p_person_user_key in varchar2,
3513 p_business_group_id in number
3514 )
3515 return number is
3516 l_period_of_service_id number;
3517 begin
3518 select pps.period_of_service_id
3519 into l_period_of_service_id
3520 from per_periods_of_service pps,
3521 hr_pump_batch_line_user_keys key
3522 where key.user_key_value = p_person_user_key
3523 and pps.person_id = key.unique_key_id
3524 and pps.business_group_id = p_business_group_id
3525 and pps.actual_termination_date is null;
3526 return(l_period_of_service_id);
3527 exception
3528 when others then
3529 hr_data_pump.fail('get_at_period_of_service_id', sqlerrm,
3530 p_person_user_key, p_business_group_id);
3531 raise;
3532 end get_at_period_of_service_id;
3533
3534 /* get_fp_period_of_service_id */
3535 function get_fp_period_of_service_id
3536 (
3537 p_person_user_key in varchar2,
3538 p_business_group_id in number
3539 )
3540 return number is
3541 l_period_of_service_id number;
3542 begin
3543 select pps.period_of_service_id
3544 into l_period_of_service_id
3545 from per_periods_of_service pps,
3546 hr_pump_batch_line_user_keys key
3547 where key.user_key_value = p_person_user_key
3548 and pps.person_id = key.unique_key_id
3549 and pps.business_group_id = p_business_group_id
3550 and pps.actual_termination_date is not null
3551 and pps.final_process_date is null;
3552 return(l_period_of_service_id);
3553 exception
3554 when others then
3555 hr_data_pump.fail('get_fp_period_of_service_id', sqlerrm, p_person_user_key,
3556 p_business_group_id);
3557 raise;
3558 end get_fp_period_of_service_id;
3559
3560 /* Added for 11i,Rvydyana,02-DEC-1999 */
3561 /* get_ut_period_of_service_id */
3562 function get_ut_period_of_service_id
3563 (
3564 p_person_user_key in varchar2,
3565 p_effective_date in date,
3566 p_business_group_id in number
3567 )
3568 return number is
3569 l_eot constant date := to_date('4712/12/31', 'YYYY/MM/DD');
3570 l_period_of_service_id number;
3571 begin
3572
3573 select pps.period_of_service_id
3574 into l_period_of_service_id
3575 from per_periods_of_service pps,
3576 hr_pump_batch_line_user_keys key
3577 where key.user_key_value = p_person_user_key
3578 and pps.person_id = key.unique_key_id
3579 and pps.business_group_id = p_business_group_id
3580 and p_effective_date between pps.date_start and NVL(pps.actual_termination_date,l_eot);
3581 return(l_period_of_service_id);
3582 exception
3583 when others then
3584 hr_data_pump.fail('get_ut_period_of_service_id', sqlerrm, p_person_user_key,
3585 p_effective_date,p_business_group_id);
3586 raise;
3587 end get_ut_period_of_service_id;
3588
3589 /* get_special_ceiling_step_id */
3590 function get_special_ceiling_step_id
3591 (
3592 p_special_ceilin_step_user_key varchar2
3593 )
3594 return number is
3595 l_special_ceiling_step_id number;
3596 begin
3597 l_special_ceiling_step_id :=
3598 user_key_to_id( p_special_ceilin_step_user_key );
3599 return(l_special_ceiling_step_id);
3600 exception
3601 when others then
3602 hr_data_pump.fail('get_special_ceiling_step_id', sqlerrm,
3603 p_special_ceilin_step_user_key );
3604 raise;
3605 end get_special_ceiling_step_id;
3606
3607 /* get_default_code_comb_id */
3608 function get_default_code_comb_id
3609 (
3610 p_default_code_comb_user_key varchar2
3611 )
3612 return number is
3613 l_default_code_comb_id number;
3614 begin
3615 l_default_code_comb_id := user_key_to_id( p_default_code_comb_user_key );
3616 return(l_default_code_comb_id);
3617 exception
3618 when others then
3619 hr_data_pump.fail('get_default_code_comb_id', sqlerrm,
3620 p_default_code_comb_user_key );
3621 raise;
3622 end get_default_code_comb_id;
3623
3624 /* Added for 11i - Rvydyana - 06-DEC-1999 */
3625 /* get_phone_id - requires user key */
3626 function get_phone_id
3627 (
3628 p_phone_user_key in varchar2
3629 ) return number is
3630 l_phone_id number;
3631 begin
3632 l_phone_id := user_key_to_id( p_phone_user_key );
3633 return(l_phone_id);
3634 exception
3635 when others then
3636 hr_data_pump.fail('get_phone_id', sqlerrm, p_phone_user_key);
3637 raise;
3638 end get_phone_id;
3639
3640 /*-------------------------- get_grade_ladder_pgm_id -----------------------*/
3641 function get_grade_ladder_pgm_id
3642 ( p_grade_ladder_name in varchar2
3643 ,p_business_group_id in number
3644 ,p_effective_date in date
3645 ) return number is
3646 l_grade_ladder_pgm_id number;
3647 begin
3648 select pgm.pgm_id
3649 into l_grade_ladder_pgm_id
3650 from ben_pgm_f pgm
3651 where pgm.name = p_grade_ladder_name
3652 and pgm.pgm_typ_cd = 'GSP'
3653 and pgm.business_group_id + 0 = p_business_group_id
3654 and p_effective_date
3655 between pgm.effective_start_date and pgm.effective_end_date;
3656 return(l_grade_ladder_pgm_id);
3657 exception
3658 when others then
3659 hr_data_pump.fail('get_grade_ladder_pgm_id', sqlerrm,
3660 p_grade_ladder_name);
3661 raise;
3662 end get_grade_ladder_pgm_id;
3663 /*---------------------- get_supervisor_assignment_id -----------------------*/
3664 function get_supervisor_assignment_id
3665 /* return supervisor assignment_id - requires user key */
3666 (
3667 p_svr_assignment_user_key in varchar2
3668 ) return number is
3669 l_supervisor_assignment_id number;
3670 begin
3671 l_supervisor_assignment_id := get_assignment_id(p_svr_assignment_user_key );
3672 return(l_supervisor_assignment_id);
3673 exception
3674 when others then
3675 hr_data_pump.fail('get_supervisor_assignment_id', sqlerrm,
3676 p_svr_assignment_user_key);
3677 raise;
3678 end get_supervisor_assignment_id;
3679
3680 /*--------------------- get_parent_spine_id ----------------------------------*/
3681 function get_parent_spine_id
3682 (
3683 p_parent_spine in varchar2
3684 ,p_business_group_id in number
3685 )
3686 return number is
3687 l_parent_spine_id number;
3688 begin
3689 select parent_spine_id
3690 into l_parent_spine_id
3691 from per_parent_spines
3692 where name = p_parent_spine
3693 and business_group_id = p_business_group_id;
3694 return(l_parent_spine_id);
3695 exception
3696 when others then
3697 hr_data_pump.fail('get_parent_spine_id', sqlerrm, p_parent_spine,
3698 p_business_group_id);
3699 raise;
3700 end get_parent_spine_id;
3701 /*--------------------- get_ceiling_step_id ----------------------------------*/
3702 function get_ceiling_step_id
3703 (
3704 p_ceiling_point in varchar2,
3705 p_business_group_id in number,
3706 p_effective_date in date
3707 )
3708 return number is
3709 l_ceiling_step_id number;
3710 begin
3711 select sps.step_id
3712 into l_ceiling_step_id
3713 from per_spinal_points psp
3714 ,per_spinal_point_steps_f sps
3715 where psp.spinal_point = p_ceiling_point
3716 and psp.spinal_point_id = sps.spinal_point_id
3717 and sps.business_group_id = p_business_group_id
3718 and p_effective_date between
3719 sps.effective_start_date and sps.effective_end_date;
3720 return(l_ceiling_step_id);
3721 exception
3722 when others then
3723 hr_data_pump.fail('get_ceiling_step_id', sqlerrm, p_ceiling_point,
3724 p_business_group_id, p_effective_date);
3725 raise;
3726 end get_ceiling_step_id;
3727
3728 /*---------------------------------------------------------------------------*/
3729 /*------------------- get object version number functions -------------------*/
3730 /*---------------------------------------------------------------------------*/
3731
3732 /*-------------------- get_collective_agreement_ovn ------------------------*/
3733 function get_collective_agreement_ovn
3734 (p_business_group_id in number
3735 ,p_cagr_name in varchar2
3736 ,p_effective_date in date
3737 ) return number is
3738 l_object_version_number number;
3739 begin
3740 select pc.object_version_number
3741 into l_object_version_number
3742 from per_collective_agreements pc
3743 where pc.business_group_id = p_business_group_id
3744 and pc.name = p_cagr_name
3745 and p_effective_date between
3746 nvl(start_date,START_OF_TIME) and nvl(end_date,END_OF_TIME);
3747 return l_object_version_number;
3748 exception
3749 when others then
3750 hr_data_pump.fail('get_collective_agreement_ovn', sqlerrm,
3751 p_business_group_id, p_cagr_name, p_effective_date);
3752 raise;
3753 end get_collective_agreement_ovn;
3754
3755 /*----------------------------- get_contract_ovn ---------------------------*/
3756 function get_contract_ovn
3757 (p_contract_user_key in varchar2
3758 ,p_effective_date in date
3759 ) return number is
3760 l_object_version_number number;
3761 begin
3762 select pc.object_version_number
3763 into l_object_version_number
3764 from per_contracts_f pc,
3765 hr_pump_batch_line_user_keys key
3766 where key.user_key_value = p_contract_user_key
3767 and pc.contract_id = key.unique_key_id
3768 and p_effective_date between
3769 pc.effective_start_date and pc.effective_end_date;
3770 return(l_object_version_number);
3771 exception
3772 when others then
3773 hr_data_pump.fail('get_contract_ovn', sqlerrm, p_contract_user_key);
3774 raise;
3775 end get_contract_ovn;
3776
3777 /*--------------------------- get_establishment_ovn ---------------------------*/
3778 function get_establishment_ovn
3779 (p_establishment_name in varchar2
3780 ,p_location in varchar2
3781 ) return number is
3782 l_object_version_number number;
3783 begin
3784 select pe.object_version_number
3785 into l_object_version_number
3786 from per_establishments pe
3787 where pe.location = p_location
3788 and pe.name = p_establishment_name;
3789 return l_object_version_number;
3790 exception
3791 when others then
3792 hr_data_pump.fail('get_establishment_ovn', sqlerrm,
3793 p_establishment_name, p_location);
3794 raise;
3795 end get_establishment_ovn;
3796
3797 /* returns a federal tax rule object version number */
3798 function get_us_emp_fed_tax_rule_ovn
3799 (
3800 p_emp_fed_tax_rule_user_key in varchar2,
3801 p_effective_date in date
3802 ) return number is
3803 l_ovn number;
3804 begin
3805 select rules.object_version_number
3806 into l_ovn
3807 from pay_us_emp_fed_tax_rules_f rules,
3808 hr_pump_batch_line_user_keys key
3809 where key.user_key_value = p_emp_fed_tax_rule_user_key
3810 and rules.emp_fed_tax_rule_id = key.unique_key_id
3814 exception
3811 and p_effective_date between
3812 rules.effective_start_date and rules.effective_end_date;
3813 return(l_ovn);
3815 when others then
3816 hr_data_pump.fail('get_us_emp_fed_tax_rule_ovn', sqlerrm,
3817 p_emp_fed_tax_rule_user_key, p_effective_date);
3818 raise;
3819 end get_us_emp_fed_tax_rule_ovn;
3820
3821 /* returns a state tax rule object version number */
3822 function get_us_emp_state_tax_rule_ovn
3823 (
3824 p_emp_state_tax_rule_user_key in varchar2,
3825 p_effective_date in date
3826 ) return number is
3827 l_ovn number;
3828 begin
3829 select rules.object_version_number
3830 into l_ovn
3831 from pay_us_emp_state_tax_rules_f rules,
3832 hr_pump_batch_line_user_keys key
3833 where key.user_key_value = p_emp_state_tax_rule_user_key
3834 and rules.emp_state_tax_rule_id = key.unique_key_id
3835 and p_effective_date between
3836 rules.effective_start_date and rules.effective_end_date;
3837 return(l_ovn);
3838 exception
3839 when others then
3840 hr_data_pump.fail('get_us_emp_state_tax_rule_ovn', sqlerrm,
3841 p_emp_state_tax_rule_user_key, p_effective_date);
3842 raise;
3843 end get_us_emp_state_tax_rule_ovn;
3844
3845 /* returns a county tax rule object version number */
3846 function get_us_emp_county_tax_rule_ovn
3847 (
3848 p_emp_county_tax_rule_user_key in varchar2,
3849 p_effective_date in date
3850 ) return number is
3851 l_ovn number;
3852 begin
3853 select rules.object_version_number
3854 into l_ovn
3855 from pay_us_emp_county_tax_rules_f rules,
3856 hr_pump_batch_line_user_keys key
3857 where key.user_key_value = p_emp_county_tax_rule_user_key
3858 and rules.emp_county_tax_rule_id = key.unique_key_id
3859 and p_effective_date between
3860 rules.effective_start_date and rules.effective_end_date;
3861 return(l_ovn);
3862 exception
3863 when others then
3864 hr_data_pump.fail('get_us_emp_county_tax_rule_ovn', sqlerrm,
3865 p_emp_county_tax_rule_user_key, p_effective_date);
3866 raise;
3867 end get_us_emp_county_tax_rule_ovn;
3868
3869 /* returns a city tax rule object version number */
3870 function get_us_emp_city_tax_rule_ovn
3871 (
3872 p_emp_city_tax_rule_user_key in varchar2,
3873 p_effective_date in date
3874 ) return number is
3875 l_ovn number;
3876 begin
3877 select rules.object_version_number
3878 into l_ovn
3879 from pay_us_emp_city_tax_rules_f rules,
3880 hr_pump_batch_line_user_keys key
3881 where key.user_key_value = p_emp_city_tax_rule_user_key
3882 and rules.emp_city_tax_rule_id = key.unique_key_id
3883 and p_effective_date between
3884 rules.effective_start_date and rules.effective_end_date;
3885 return(l_ovn);
3886 exception
3887 when others then
3888 hr_data_pump.fail('get_us_emp_city_tax_rule_ovn', sqlerrm,
3889 p_emp_city_tax_rule_user_key, p_effective_date);
3890 raise;
3891 end get_us_emp_city_tax_rule_ovn;
3892
3893 /* returns a person object version number */
3894 function get_per_ovn
3895 (
3896 p_person_user_key in varchar2,
3897 p_effective_date in date
3898 ) return number is
3899 l_ovn number;
3900 begin
3901 select per.object_version_number
3902 into l_ovn
3903 from per_people_f per,
3904 hr_pump_batch_line_user_keys key
3905 where key.user_key_value = p_person_user_key
3906 and per.person_id = key.unique_key_id
3907 and p_effective_date between
3908 per.effective_start_date and per.effective_end_date;
3909 return(l_ovn);
3910 exception
3911 when others then
3912 hr_data_pump.fail('get_per_ovn', sqlerrm, p_person_user_key,
3913 p_effective_date);
3914 raise;
3915 end get_per_ovn;
3916
3917 /* returns an assignment object version number */
3918 function get_asg_ovn
3919 (
3920 p_assignment_user_key in varchar2,
3921 p_effective_date in date
3922 ) return number is
3923 l_ovn number;
3924 begin
3925 select asg.object_version_number
3926 into l_ovn
3927 from per_assignments_f asg,
3928 hr_pump_batch_line_user_keys key
3929 where key.user_key_value = p_assignment_user_key
3930 and asg.assignment_id = key.unique_key_id
3931 and p_effective_date between
3932 asg.effective_start_date and asg.effective_end_date;
3933 return(l_ovn);
3934 exception
3935 when others then
3936 hr_data_pump.fail('get_asg_ovn', sqlerrm, p_assignment_user_key,
3937 p_effective_date);
3938 raise;
3939 end get_asg_ovn;
3940
3941 /* returns an address object version number */
3942 function get_adr_ovn
3943 (
3944 p_address_user_key in varchar2,
3945 p_effective_date in date
3946 ) return number is
3947 l_ovn number;
3948 begin
3949 select adr.object_version_number
3950 into l_ovn
3951 from per_addresses adr,
3952 hr_pump_batch_line_user_keys key
3953 where key.user_key_value = p_address_user_key
3954 and adr.address_id = key.unique_key_id;
3955 return(l_ovn);
3956 exception
3957 when others then
3958 hr_data_pump.fail('get_adr_ovn', sqlerrm, p_address_user_key,
3959 p_effective_date);
3960 raise;
3961 end get_adr_ovn;
3962
3963 /* returns a location object version number */
3964 function get_loc_ovn
3965 (
3966 p_location_code in varchar2
3967 ) return number is
3968 l_ovn number;
3969 begin
3970 --
3971 -- Changed to use hr_locations_all for WWBUG 1833930.
3972 --
3973 select loc.object_version_number
3974 into l_ovn
3975 from hr_locations_all loc
3976 where loc.location_code = p_location_code;
3977 return(l_ovn);
3978 exception
3979 when others then
3980 hr_data_pump.fail('get_loc_ovn', sqlerrm, p_location_code);
3981 raise;
3982 end get_loc_ovn;
3983
3984 /* returns a job object version number */
3985 function get_org_str_ovn
3986 (
3987 p_name in varchar2,
3988 p_business_group_id in number
3989 ) return number is
3990 l_ovn number;
3991 begin
3992 select ors.object_version_number
3993 into l_ovn
3994 from per_organization_structures ors
3995 where ors.name = p_name
3996 and ors.business_group_id + 0 = p_business_group_id;
3997 return(l_ovn);
3998 exception
3999 when others then
4000 hr_data_pump.fail('get_org_str_ovn', sqlerrm, p_name, p_business_group_id);
4001 raise;
4002 end get_org_str_ovn;
4003
4004 /* returns a organization structure version object version number */
4005 function get_org_str_ver_ovn
4006 (
4007 p_business_group_id in number,
4008 p_organization_structure_id in number,
4009 p_date_from in date,
4010 p_version_number in number
4011 ) return number is
4012 l_ovn number;
4013 begin
4014 select osv.object_version_number
4015 into l_ovn
4016 from per_org_structure_versions osv
4017 where osv.organization_structure_id = p_organization_structure_id
4018 and osv.date_from = p_date_from
4019 and osv.version_number = p_version_number
4020 and osv.business_group_id + 0 = p_business_group_id;
4021 return(l_ovn);
4022 exception
4023 when others then
4024 hr_data_pump.fail('get_org_str_ver_ovn', sqlerrm, p_business_group_id, p_organization_structure_id, p_date_from, p_version_number);
4025 raise;
4026 end get_org_str_ver_ovn;
4027
4028 /* returns an organization object version number */
4029 function get_org_ovn
4030 (
4031 p_business_group_id in number,
4032 p_organization_name in varchar2,
4033 p_language_code in varchar2
4034 ) return number is
4035 l_ovn number;
4036 begin
4037 select org.object_version_number
4038 into l_ovn
4039 from hr_all_organization_units org
4040 , hr_all_organization_units_tl orgtl
4041 where orgtl.name = p_organization_name
4042 and orgtl.language = p_language_code
4043 and org.organization_id = orgtl.organization_id
4044 and org.business_group_id + 0 = p_business_group_id;
4045 return(l_ovn);
4046 exception
4047 when others then
4048 hr_data_pump.fail('get_org_ovn', sqlerrm, p_business_group_id, p_organization_name, p_language_code);
4049 raise;
4050 end get_org_ovn;
4051
4052 /* returns a job object version number */
4053 function get_job_ovn
4054 (
4055 p_job_name in varchar2,
4056 p_effective_date in date,
4057 p_business_group_id in number
4058 ) return number is
4059 l_ovn number;
4060 begin
4061 select job.object_version_number
4062 into l_ovn
4063 from per_jobs_vl job
4064 where job.name = p_job_name
4065 and job.business_group_id + 0 = p_business_group_id;
4066 return(l_ovn);
4067 exception
4068 when others then
4069 hr_data_pump.fail('get_job_ovn', sqlerrm, p_job_name, p_effective_date,
4070 p_business_group_id);
4071 raise;
4072 end get_job_ovn;
4073
4074 /* returns a position object version number */
4075 function get_pos_ovn
4076 (
4077 p_position_name in varchar2,
4078 p_business_group_id in number,
4079 p_effective_date in date
4080 ) return number is
4081 l_ovn number;
4082 begin
4083 select pos.object_version_number
4084 into l_ovn
4085 from hr_all_positions_f pos
4086 where pos.name = p_position_name
4087 and pos.business_group_id + 0 = p_business_group_id
4088 and p_effective_date between
4089 pos.effective_start_date and pos.effective_end_date;
4090 return(l_ovn);
4091 exception
4092 when others then
4093 hr_data_pump.fail('get_pos_ovn', sqlerrm, p_position_name,
4094 p_business_group_id, p_effective_date);
4095 raise;
4096 end get_pos_ovn;
4097
4098 /* returns a personal_payment_method object version number */
4099 function get_ppm_ovn
4100 (
4101 p_personal_pay_method_user_key in varchar2,
4102 p_effective_date in date
4103 ) return number is
4104 l_ovn number;
4105 begin
4106 select ppm.object_version_number
4107 into l_ovn
4108 from pay_personal_payment_methods_f ppm
4112 and p_effective_date between
4109 , hr_pump_batch_line_user_keys key
4110 where key.user_key_value = p_personal_pay_method_user_key
4111 and ppm.personal_payment_method_id = key.unique_key_id
4113 ppm.effective_start_date and ppm.effective_end_date;
4114 return(l_ovn);
4115 exception
4116 when others then
4117 hr_data_pump.fail('get_ppm_ovn', sqlerrm,
4118 p_personal_pay_method_user_key, p_effective_date);
4119 raise;
4120 end get_ppm_ovn;
4121
4122 /* get_element_entry_ovn - requires user key */
4123 function get_element_entry_ovn
4124 (
4125 p_element_entry_user_key in varchar2,
4126 p_effective_date in date
4127 ) return number is
4128 l_element_entry_ovn number;
4129 begin
4130 select pee.object_version_number
4131 into l_element_entry_ovn
4132 from pay_element_entries_f pee,
4133 hr_pump_batch_line_user_keys key
4134 where key.user_key_value = p_element_entry_user_key
4135 and pee.element_entry_id = key.unique_key_id
4136 and p_effective_date between
4137 pee.effective_start_date and pee.effective_end_date;
4138 return(l_element_entry_ovn);
4139 exception
4140 when others then
4141 hr_data_pump.fail('get_element_entry_ovn', sqlerrm, p_element_entry_user_key, p_effective_date);
4142 raise;
4143 end get_element_entry_ovn;
4144
4145 /* get_gr_grade_rule_ovn */
4146 function get_gr_grade_rule_ovn
4147 (
4148 p_grade_name in varchar2,
4149 p_rate_name in varchar2,
4150 p_business_group_id in number,
4151 p_effective_date in date
4152 )
4153 return number is
4154 l_object_version_number number;
4155 begin
4156 select pgr.object_version_number
4157 into l_object_version_number
4158 from pay_grade_rules_f pgr,
4159 per_grades_vl pg,
4160 pay_rates pr
4161 where pg.name = p_grade_name
4162 and pg.business_group_id + 0 = p_business_group_id
4163 and pr.name = p_rate_name
4164 and pr.business_group_id + 0 = p_business_group_id
4165 and pgr.rate_id = pr.rate_id
4166 and pgr.grade_or_spinal_point_id = pg.grade_id
4167 and pgr.rate_type = 'G'
4168 and pgr.business_group_id + 0 = p_business_group_id
4169 and p_effective_date between pgr.effective_start_date and
4170 pgr.effective_end_date;
4171 return(l_object_version_number);
4172 exception
4173 when others then
4174 hr_data_pump.fail('get_gr_grade_rule_ovn', sqlerrm, p_grade_name,
4175 p_rate_name, p_business_group_id, p_effective_date);
4176 raise;
4177 end get_gr_grade_rule_ovn;
4178
4179 /* get_pp_grade_rule_ovn */
4180 function get_pp_grade_rule_ovn
4181 (
4182 p_progression_point in varchar2,
4183 p_pay_scale in varchar2,
4184 p_rate_name in varchar2,
4185 p_business_group_id in number,
4186 p_effective_date in date
4187 )
4188 return number is
4189 l_object_version_number number;
4190 begin
4191 select pgr.object_version_number
4192 into l_object_version_number
4193 from per_parent_spines pps,
4194 per_spinal_points psp,
4195 pay_grade_rules_f pgr,
4196 pay_rates pr
4197 where pps.name = p_pay_scale
4198 and pps.business_group_id + 0 = p_business_group_id
4199 and psp.spinal_point = p_progression_point
4200 and psp.business_group_id + 0 = p_business_group_id
4201 and psp.parent_spine_id = pps.parent_spine_id
4202 and pr.name = p_rate_name
4203 and pr.business_group_id + 0 = p_business_group_id
4204 and pgr.rate_id = pr.rate_id
4205 and pgr.grade_or_spinal_point_id = psp.spinal_point_id
4206 and pgr.rate_type = 'SP'
4207 and pgr.business_group_id + 0 = p_business_group_id
4208 and p_effective_date between pgr.effective_start_date and
4209 pgr.effective_end_date;
4210 return(l_object_version_number);
4211 exception
4212 when others then
4213 hr_data_pump.fail('get_pp_grade_rule_id', sqlerrm, p_progression_point,
4214 p_pay_scale, p_rate_name, p_business_group_id,
4215 p_effective_date);
4216 raise;
4217 end get_pp_grade_rule_ovn;
4218
4219 /* get_at_period_of_service_ovn */
4220 function get_at_period_of_service_ovn
4221 (
4222 p_person_user_key in varchar2,
4223 p_business_group_id in number
4224 )
4225 return number is
4226 l_object_version_number number;
4227 begin
4228 select pps.object_version_number
4229 into l_object_version_number
4230 from per_periods_of_service pps,
4231 hr_pump_batch_line_user_keys key
4232 where key.user_key_value = p_person_user_key
4233 and pps.person_id = key.unique_key_id
4234 and pps.business_group_id = p_business_group_id
4235 and pps.actual_termination_date is null;
4236 return(l_object_version_number);
4237 exception
4238 when others then
4239 hr_data_pump.fail('get_at_period_of_service_ovn', sqlerrm, p_person_user_key,
4240 p_business_group_id);
4241 raise;
4242 end get_at_period_of_service_ovn;
4243
4244 /* get_fp_period_of_service_ovn */
4245 function get_fp_period_of_service_ovn
4246 (
4250 return number is
4247 p_person_user_key in varchar2,
4248 p_business_group_id in number
4249 )
4251 l_object_version_number number;
4252 begin
4253 select pps.object_version_number
4254 into l_object_version_number
4255 from per_periods_of_service pps,
4256 hr_pump_batch_line_user_keys key
4257 where key.user_key_value = p_person_user_key
4258 and pps.person_id = key.unique_key_id
4259 and pps.business_group_id = p_business_group_id
4260 and pps.actual_termination_date is not null
4261 and pps.final_process_date is null;
4262 return(l_object_version_number);
4263 exception
4264 when others then
4265 hr_data_pump.fail('get_fp_period_of_service_ovn', sqlerrm, p_person_user_key,
4266 p_business_group_id);
4267 raise;
4268 end get_fp_period_of_service_ovn;
4269
4270 /* Added for 11i,Rvydyana,02-DEC-1999 */
4271 /* get_ut_period_of_service_ovn */
4272 function get_ut_period_of_service_ovn
4273 (
4274 p_person_user_key in varchar2,
4275 p_effective_date in date,
4276 p_business_group_id in number
4277 )
4278 return number is
4279 l_eot constant date := to_date('4712/12/31', 'YYYY/MM/DD');
4280 l_object_version_number number;
4281 begin
4282
4283 select pps.object_version_number
4284 into l_object_version_number
4285 from per_periods_of_service pps,
4286 hr_pump_batch_line_user_keys key
4287 where key.user_key_value = p_person_user_key
4288 and pps.person_id = key.unique_key_id
4289 and pps.business_group_id = p_business_group_id
4290 and p_effective_date between pps.date_start and NVL(pps.actual_termination_date,l_eot);
4291 return(l_object_version_number);
4292 exception
4293 when others then
4294 hr_data_pump.fail('get_ut_period_of_service_id', sqlerrm, p_person_user_key,
4295 p_effective_date,p_business_group_id);
4296 raise;
4297 end get_ut_period_of_service_ovn;
4298
4299 /* get entry_step_id - requires user key */
4300 function get_entry_step_id
4301 (
4302 p_entry_step_user_key in varchar2
4303 ) return number is
4304 l_entry_step_id number;
4305 begin
4306 l_entry_step_id := user_key_to_id( p_entry_step_user_key );
4307 return(l_entry_step_id);
4308 exception
4309 when others then
4310 hr_data_pump.fail('get_entry_step_id', sqlerrm, p_entry_step_user_key);
4311 raise;
4312 end get_entry_step_id;
4313
4314 /* get entry_grade_rule_id - requires user key */
4315 function get_entry_grade_rule_id
4316 (
4317 p_entry_grade_rule_user_key in varchar2
4318 ) return number is
4319 l_entry_grade_rule_id number;
4320 begin
4321 l_entry_grade_rule_id := user_key_to_id( p_entry_grade_rule_user_key );
4322 return(l_entry_grade_rule_id);
4323 exception
4324 when others then
4325 hr_data_pump.fail('get_entry_grade_rule_id', sqlerrm, p_entry_grade_rule_user_key);
4326 raise;
4327 end get_entry_grade_rule_id;
4328
4329 /* Added for 11i - Rvydyana - 06-DEC-1999 */
4330 /* returns a phone object version number */
4331 function get_phn_ovn
4332 (
4333 p_phone_user_key in varchar2
4334 ) return number is
4335 l_ovn number;
4336 begin
4337 select phn.object_version_number
4338 into l_ovn
4339 from per_phones phn,
4340 hr_pump_batch_line_user_keys key
4341 where key.user_key_value = p_phone_user_key
4342 and phn.phone_id = key.unique_key_id;
4343 return(l_ovn);
4344 exception
4345 when others then
4346 hr_data_pump.fail('get_phn_ovn', sqlerrm, p_phone_user_key);
4347 raise;
4348 end get_phn_ovn;
4349 --
4350 /* --------------------------------------------------- */
4351 /* ----------------- get_jgr_ovn---------------------- */
4352 /* --------------------------------------------------- */
4353 function get_jgr_ovn
4354 (
4355 p_job_group_user_key in varchar2
4356 ) return number is
4357 l_ovn number;
4358 begin
4359 select jgr.object_version_number
4360 into l_ovn
4361 from per_job_groups jgr,
4362 hr_pump_batch_line_user_keys key
4363 where key.user_key_value = p_job_group_user_key
4364 and jgr.job_group_id = key.unique_key_id;
4365 return(l_ovn);
4366 exception
4367 when others then
4368 hr_data_pump.fail('get_jgr_ovn', sqlerrm, p_job_group_user_key);
4369 raise;
4370 end get_jgr_ovn;
4371
4372 /* --------------------------------------------------- */
4373 /* ----------------- get_rol_ovn---------------------- */
4374 /* --------------------------------------------------- */
4375 function get_rol_ovn
4376 (
4377 p_role_user_key in varchar2
4378 ) return number is
4379 l_ovn number;
4380 begin
4381 select rol.object_version_number
4382 into l_ovn
4383 from per_roles rol,
4384 hr_pump_batch_line_user_keys key
4385 where key.user_key_value = p_role_user_key
4386 and rol.role_id = key.unique_key_id;
4387 return(l_ovn);
4388 exception
4389 when others then
4390 hr_data_pump.fail('get_rol_ovn', sqlerrm, p_role_user_key);
4391 raise;
4392 end get_rol_ovn;
4393 /*-------------- returns a pay scale object version number --------------------*/
4397 p_business_group_id in number
4394 function get_pay_scale_ovn
4395 (
4396 p_pay_scale in varchar2,
4398 ) return number is
4399 l_ovn number;
4400 begin
4401 select object_version_number
4402 into l_ovn
4403 from per_parent_spines
4404 where name = p_pay_scale
4405 and business_group_id + 0 = p_business_group_id;
4406 return(l_ovn);
4407 exception
4408 when others then
4409 hr_data_pump.fail('get_pay_scale_ovn', sqlerrm, p_pay_scale,
4410 p_business_group_id);
4411 raise;
4412 end get_pay_scale_ovn;
4413 /*-------------- returns a preogresion point object version number ------------*/
4414 function get_progression_point_ovn
4415 (
4416 p_point in varchar2,
4417 p_business_group_id in number
4418 ) return number is
4419 l_ovn number;
4420 begin
4421 select object_version_number
4422 into l_ovn
4423 from per_spinal_points
4424 where spinal_point = p_point
4425 and business_group_id + 0 = p_business_group_id;
4426 return(l_ovn);
4427 exception
4428 when others then
4429 hr_data_pump.fail('get_progression_point_ovn', sqlerrm, p_point,
4430 p_business_group_id);
4431 raise;
4432 end get_progression_point_ovn;
4433 /*-------------- returns a grade scale object version number ------------*/
4434 function get_grade_scale_ovn
4435 (
4436 p_grade in varchar2,
4437 p_pay_scale in varchar2,
4438 p_effective_date in date,
4439 p_business_group_id in number
4440 ) return number is
4441 l_ovn number;
4442 begin
4443 select pgs.object_version_number
4444 into l_ovn
4445 from per_grade_spines_f pgs
4446 ,per_grades pg
4447 ,per_parent_spines pps
4448 where pg.name = p_grade
4449 and pg.grade_id = pgs.grade_id
4450 and pps.name = p_pay_scale
4451 and pps.parent_spine_id = pgs.parent_spine_id
4452 and pgs.business_group_id = p_business_group_id
4453 and p_effective_date between
4454 pgs.effective_start_date and pgs.effective_end_date;
4455 return(l_ovn);
4456 exception
4457 when others then
4458 hr_data_pump.fail('get_grade_scale_ovn', sqlerrm, p_grade,
4459 p_pay_scale, p_effective_date ,p_business_group_id);
4460 raise;
4461 end get_grade_scale_ovn;
4462 /*-------------- returns a grade step object version number ------------*/
4463 function get_grade_step_ovn
4464 (
4465 p_point in varchar2,
4466 p_sequence in number,
4467 p_effective_date in date,
4468 p_business_group_id in number
4469 ) return number is
4470 l_ovn number;
4471 begin
4472 select sps.object_version_number
4473 into l_ovn
4474 from per_spinal_point_steps_f sps
4475 ,per_spinal_points psp
4476 ,per_grade_spines_f pgs
4477 where psp.spinal_point = p_point
4478 and psp.spinal_point_id = sps.spinal_point_id
4479 and sps.sequence = p_sequence
4480 and sps.step_id = pgs.ceiling_step_id
4481 and pgs.grade_spine_id = sps.grade_spine_id
4482 and sps.business_group_id = p_business_group_id
4483 and p_effective_date between
4484 sps.effective_start_date and sps.effective_end_date;
4485 return(l_ovn);
4486 exception
4487 when others then
4488 hr_data_pump.fail('get_grade_step_ovn', sqlerrm, p_point,
4489 p_sequence,p_effective_date, p_business_group_id);
4490 raise;
4491 end get_grade_step_ovn;
4492 /*---------------------------------------------------------------------------*/
4493 /*----------------------- other special get functions -----------------------*/
4494 /*---------------------------------------------------------------------------*/
4495
4496 /* returns a language code */
4497 function get_correspondence_language
4498 (
4499 p_correspondence_language varchar2
4500 ) return varchar2 is
4501 l_code fnd_languages.language_code%type;
4502 begin
4503 select l.language_code
4504 into l_code
4505 from fnd_languages l
4506 where l.nls_language = p_correspondence_language;
4507 return(l_code);
4508 exception
4509 -- If the nls_language could not be matched, assume that the user was
4510 -- entering the code directly.
4511 when no_data_found then
4512 return(p_correspondence_language);
4513 when others then
4514 hr_data_pump.fail('get_correspondence_language', sqlerrm,
4515 p_correspondence_language);
4516 raise;
4517 end get_correspondence_language;
4518
4519 /* get_country */
4520 function get_country( p_country in varchar2 ) return varchar2 is
4521 l_territory_code varchar2(2);
4522 begin
4523 select territory_code
4524 into l_territory_code
4525 from fnd_territories_vl
4526 where territory_short_name = p_country;
4527 return(l_territory_code);
4528 exception
4529 -- If the short_name could not be matched, assume that the user was
4530 -- entering the code directly.
4531 when no_data_found then
4532 return(p_country);
4533 when others then
4534 hr_data_pump.fail('get_country', sqlerrm, p_country );
4535 raise;
4536 end get_country;
4537
4538 /* get change reason lookup code, dependent on assignment */
4539 function get_change_reason
4540 (
4541 p_change_reason in varchar2,
4542 p_assignment_user_key in varchar2,
4543 p_effective_date in date,
4544 p_language_code in varchar2
4545 ) return varchar2 is
4546 l_code varchar2(30);
4547 begin
4548 select flv.lookup_code
4549 into l_code
4550 from fnd_lookup_values flv,
4551 per_assignments_f asg,
4552 hr_pump_batch_line_user_keys key
4553 where key.user_key_value = p_assignment_user_key
4554 and asg.assignment_id = key.unique_key_id
4555 and p_effective_date between
4556 asg.effective_start_date and asg.effective_end_date
4557 and flv.lookup_type = decode(asg.assignment_type,
4558 'E', 'EMP_ASSIGN_REASON',
4559 'A', 'APL_ASSIGN_REASON')
4560 and flv.meaning = p_change_reason
4561 and flv.language = p_language_code
4562 and flv.view_application_id = 3
4563 and flv.security_group_id =
4564 fnd_global.lookup_security_group
4565 (flv.lookup_type
4566 ,flv.view_application_id
4567 )
4568 and p_effective_date between
4569 nvl(flv.start_date_active, START_OF_TIME) and
4570 nvl(flv.end_date_active, END_OF_TIME);
4571 return(l_code);
4572 exception
4573 -- Assume that the user entered the code directly when no data found.
4574 when no_data_found then
4575 return(p_change_reason);
4576 when others then
4577 hr_data_pump.fail('get_change_reason', sqlerrm, p_change_reason,
4578 p_assignment_user_key, p_effective_date);
4579 raise;
4580 end get_change_reason;
4581
4582 /* get_job_group_id - requires user key */
4583 function get_job_group_id
4584 (
4585 p_job_group_user_key in varchar2
4586 ) return number is
4587 l_job_group_id number;
4588 begin
4589 l_job_group_id := user_key_to_id( p_job_group_user_key );
4590 return(l_job_group_id);
4591 exception
4592 when others then
4593 hr_data_pump.fail('get_job_group_id', sqlerrm, p_job_group_user_key);
4594 raise;
4595 end get_job_group_id;
4596
4597 /* get_benchmark_job_id - requires user key */
4598 function get_benchmark_job_id
4599 (
4600 p_benchmark_job_user_key in varchar2
4601 ) return number is
4602 l_benchmark_job_id number;
4603 begin
4604 l_benchmark_job_id := user_key_to_id( p_benchmark_job_user_key );
4605 return(l_benchmark_job_id);
4606 exception
4607 when others then
4608 hr_data_pump.fail('get_benchmark_job_id', sqlerrm, p_benchmark_job_user_key);
4609 raise;
4610 end get_benchmark_job_id;
4611
4612 /* get_role_id - requires user key */
4613 function get_role_id
4614 (
4615 p_role_user_key in varchar2
4616 ) return number is
4617 l_role_id number;
4618 begin
4619 l_role_id := user_key_to_id( p_role_user_key );
4620 return(l_role_id);
4621 exception
4622 when others then
4623 hr_data_pump.fail('get_role_id', sqlerrm, p_role_user_key);
4624 raise;
4625 end get_role_id;
4626
4627 /* get_loc_id - requires user key */
4628 function get_loc_id
4629 (
4630 p_location_user_key in varchar2
4631 ) return number is
4632 l_location_id number;
4633 begin
4634 l_location_id := user_key_to_id( p_location_user_key );
4635 return(l_location_id);
4636 exception
4637 when others then
4638 hr_data_pump.fail('get_loc_id', sqlerrm, p_location_user_key);
4639 raise;
4640 end get_loc_id;
4641
4642 /* get_org_structure_id - requires user key */
4643 function get_org_structure_id
4644 (
4645 p_org_structure_user_key in varchar2
4646 ) return number is
4647 l_organization_structure_id number;
4648 begin
4649 l_organization_structure_id := user_key_to_id( p_org_structure_user_key );
4650 return(l_organization_structure_id);
4651 exception
4652 when others then
4653 hr_data_pump.fail('get_org_structure_id', sqlerrm, p_org_structure_user_key);
4654 raise;
4655 end get_org_structure_id;
4656
4657 /* get_org_str_version_id - requires user key */
4658 function get_org_str_version_id
4659 (
4660 p_org_str_version_user_key in varchar2
4661 ) return number is
4662 l_org_structure_version_id number;
4663 begin
4664 l_org_structure_version_id := user_key_to_id( p_org_str_version_user_key );
4665 return(l_org_structure_version_id);
4666 exception
4667 when others then
4668 hr_data_pump.fail('get_org_str_version_id', sqlerrm, p_org_str_version_user_key);
4669 raise;
4670 end get_org_str_version_id;
4671
4672 /* get_org_id - requires user key */
4673 function get_org_id
4674 (
4675 p_org_user_key in varchar2
4676 ) return number is
4677 l_org_id number;
4678 begin
4679 l_org_id := user_key_to_id( p_org_user_key );
4680 return(l_org_id);
4681 exception
4682 when others then
4683 hr_data_pump.fail('get_org_id', sqlerrm, p_org_user_key);
4684 raise;
4685 end get_org_id;
4686
4687 /* get_grade_rule_id - requires user key */
4688 function get_grade_rule_id
4689 (
4690 p_grade_rule_user_key in varchar2
4691 ) return number is
4692 l_grade_rule_id number;
4693 begin
4694 l_grade_rule_id := user_key_to_id( p_grade_rule_user_key );
4695 return(l_grade_rule_id);
4696 exception
4697 when others then
4698 hr_data_pump.fail('get_grade_rule_id', sqlerrm, p_grade_rule_user_key);
4699 raise;
4700 end get_grade_rule_id;
4701
4702 /* returns lookup_code */
4703 function gl
4704 (
4705 p_meaning_or_code in varchar2,
4706 p_lookup_type in varchar2,
4707 p_effective_date in date default null,
4708 p_language_code in varchar2 default null
4709 ) return varchar2 is
4710 l_code hr_lookups.lookup_code%type;
4711 l_effective_date date;
4712 l_language_code varchar2(2000);
4713 begin
4714 --
4715 -- Is lookup checking disabled ?
4716 --
4717 if hr_data_pump.g_disable_lookup_checks then
4718 return p_meaning_or_code;
4719 end if;
4720 --
4721 -- Set language code (handling possible defaults).
4722 --
4723 if p_language_code = hr_api.g_varchar2 then
4724 --
4725 -- nvl() in the query will take care of defaulting.
4726 --
4727 l_language_code := null;
4728 else
4729 l_language_code := p_language_code;
4730 end if;
4731 --
4732 -- Set the effective date (handling possible defaults).
4733 --
4734 if p_effective_date is null or p_effective_date = hr_api.g_date then
4735 l_effective_date := hr_api.g_sys;
4736 else
4737 l_effective_date := p_effective_date;
4738 end if;
4739 if p_meaning_or_code is null or p_meaning_or_code = hr_api.g_varchar2
4740 then
4741 --
4742 -- Defaulted values will be returned unchanged.
4743 --
4744 l_code := p_meaning_or_code;
4745 else
4746 --
4747 -- Check against meaning using the new R11.5 lookup tables.
4748 --
4749 select flv.lookup_code
4750 into l_code
4751 from fnd_lookup_values flv
4752 where flv.meaning = p_meaning_or_code
4753 and flv.lookup_type = p_lookup_type
4754 and flv.language = nvl(l_language_code, userenv('LANG'))
4755 and flv.view_application_id = 3
4756 and flv.security_group_id =
4757 fnd_global.lookup_security_group
4758 (flv.lookup_type
4759 ,flv.view_application_id
4760 )
4761 and l_effective_date between
4762 nvl(flv.start_date_active, START_OF_TIME) and
4763 nvl(flv.end_date_active, END_OF_TIME);
4764 end if;
4765 --
4766 return(l_code);
4767 exception
4768 when no_data_found then
4769 --
4770 -- If the meaning could not be matched, assume that the user
4771 -- entered the code directly. This part of the code used to
4772 -- check against HR_LOOKUPS, but HR_LOOKUPS requires the
4773 -- legislation context to be set in HR_SESSION_DATA now.
4774 -- Such an additional check is unnecessary because the API
4775 -- should be able to pick up bad code values.
4776 --
4777 return(p_meaning_or_code);
4778 when others then
4779 hr_data_pump.fail
4780 ('get_lookup_code', sqlerrm, p_meaning_or_code,
4781 p_lookup_type, p_effective_date, p_language_code);
4782 raise;
4783 end gl;
4784
4785 function get_lookup_code
4786 (
4787 p_meaning_or_code in varchar2,
4788 p_lookup_type in varchar2,
4789 p_effective_date in date default null,
4790 p_language_code in varchar2 default null
4791 ) return varchar2 is
4792 begin
4793 return gl( p_meaning_or_code, p_lookup_type, p_effective_date,
4794 p_language_code );
4795 end get_lookup_code;
4796 /* return people_group_id */
4797 function get_people_group_id
4798 (
4799 p_people_group_user_name in varchar2,
4800 p_effective_date in date
4801 ) return number is
4802 l_people_group_id number;
4803 begin
4804 --
4805 select people_group_id
4806 into l_people_group_id
4807 from pay_people_groups
4808 where GROUP_NAME = p_people_group_user_name
4809 and p_effective_date
4810 between nvl(start_date_active,START_OF_TIME)
4811 and nvl(end_date_active,END_OF_TIME);
4812 --
4813 return(l_people_group_id);
4814 exception
4815 when others then
4816 hr_data_pump.fail('get_people_group_id', sqlerrm,
4817 p_people_group_user_name,
4818 p_effective_date);
4819 raise;
4820 end get_people_group_id;
4821
4822 /* return absence_attendance_type_id */
4823 function get_absence_attendance_type_id
4824 (
4825 p_aat_user_name in varchar2,
4826 p_business_group_id in number,
4827 p_effective_date in date
4828 ) return number is
4829 l_absence_attendance_type_id number;
4830 begin
4831 --
4832 select aat.absence_attendance_type_id
4833 into l_absence_attendance_type_id
4834 from per_abs_attendance_types_vl aat
4835 where aat.name = p_aat_user_name
4836 and aat.business_group_id = p_business_group_id
4837 and p_effective_date between
4838 nvl(DATE_EFFECTIVE, START_OF_TIME) AND
4839 nvl(DATE_END, END_OF_TIME);
4840 --
4841 return(l_absence_attendance_type_id);
4842 exception
4843 when others then
4844 hr_data_pump.fail('get_absence_attendance_type_id', sqlerrm,
4845 p_aat_user_name,
4846 p_business_group_id,
4847 p_effective_date);
4848 raise;
4849 end get_absence_attendance_type_id;
4850 /* return soft_coding_keyflex_id */
4851 function get_soft_coding_keyflex_id
4852 (
4853 p_con_seg_user_name in varchar2,
4854 p_effective_date in date
4855 ) return number is
4856 l_soft_coding_keyflex_id number;
4857 begin
4858 --
4859 select soft_coding_keyflex_id
4860 into l_soft_coding_keyflex_id
4861 from hr_soft_coding_keyflex
4862 where concatenated_segments = p_con_seg_user_name
4863 and p_effective_date between
4864 nvl(START_DATE_ACTIVE, START_OF_TIME) AND
4865 nvl(END_DATE_ACTIVE, END_OF_TIME);
4866 --
4867 return(l_soft_coding_keyflex_id);
4868 exception
4869 when others then
4870 hr_data_pump.fail('get_soft_coding_keyflex_id', sqlerrm,
4871 p_con_seg_user_name,
4872 p_effective_date);
4873 raise;
4874 end get_soft_coding_keyflex_id;
4875
4876 /* return pk_id */
4877 function get_pk_id
4878 (
4879 p_pk_name in varchar2
4880 ) return number is
4881 l_pk_id number := null;
4882 begin
4883 --
4884 -- This column should not be populated by datapump.
4885 --
4886 return(l_pk_id);
4887 exception
4888 when others then
4889 hr_data_pump.fail('get_pk_id', sqlerrm,
4890 p_pk_name
4891 );
4892 raise;
4893 end get_pk_id;
4894
4895 /* Bug 3275173 -- get object versionn number*/
4896 function get_fed_tax_rule_ovn
4897 (
4898 p_emp_fed_tax_rule_user_key in varchar2,
4899 p_effective_date in date
4900 ) return number is
4901 l_ovn number;
4902 begin
4903 select object_version_number
4904 into l_ovn
4905 from pay_us_emp_fed_tax_rules_f puek,
4906 hr_pump_batch_line_user_keys uk
4907 where uk.user_key_value = p_emp_fed_tax_rule_user_key
4908 and p_effective_date between puek.effective_start_date and puek.effective_end_date
4909 and puek.emp_fed_tax_rule_id = uk.unique_key_id;
4910 --
4911
4912 return l_ovn;
4913 exception
4914 when others then
4915 hr_data_pump.fail('get_fed_tax_rule_ovn', sqlerrm,
4916 p_emp_fed_tax_rule_user_key,
4917 p_effective_date);
4918 raise;
4919 end get_fed_tax_rule_ovn;
4920
4921 --
4922 -- Bug 3783381 -- get object versionn number for state, county and city
4923 --
4924
4925 function get_state_tax_rule_ovn
4926 (
4927 p_emp_state_tax_rule_user_key in varchar2,
4928 p_effective_date in date
4929 ) return number is
4930 l_ovn number;
4931 begin
4932 select object_version_number
4933 into l_ovn
4934 from pay_us_emp_state_tax_rules_f puek,
4935 hr_pump_batch_line_user_keys uk
4936 where uk.user_key_value = p_emp_state_tax_rule_user_key
4937 and p_effective_date between puek.effective_start_date and puek.effective_end_date
4938 and puek.emp_state_tax_rule_id = uk.unique_key_id;
4939 --
4940
4941 return l_ovn;
4942 exception
4943 when others then
4944 hr_data_pump.fail('get_state_tax_rule_ovn', sqlerrm,
4945 p_emp_state_tax_rule_user_key,
4946 p_effective_date);
4947 raise;
4948 end get_state_tax_rule_ovn;
4949
4950 ---
4951 function get_county_tax_rule_ovn
4952 (
4953 p_emp_county_tax_rule_user_key in varchar2,
4954 p_effective_date in date
4955 ) return number is
4956 l_ovn number;
4957 begin
4958 select object_version_number
4959 into l_ovn
4960 from pay_us_emp_county_tax_rules_f puek,
4961 hr_pump_batch_line_user_keys uk
4962 where uk.user_key_value = p_emp_county_tax_rule_user_key
4963 and p_effective_date between puek.effective_start_date and puek.effective_end_date
4964 and puek.emp_county_tax_rule_id = uk.unique_key_id;
4965 --
4966
4967 return l_ovn;
4968 exception
4969 when others then
4970 hr_data_pump.fail('get_county_tax_rule_ovn', sqlerrm,
4971 p_emp_county_tax_rule_user_key,
4972 p_effective_date);
4973 raise;
4974 end get_county_tax_rule_ovn;
4975
4976 ---
4977
4978 function get_city_tax_rule_ovn
4979 (
4980 p_emp_city_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_city_tax_rules_f puek,
4988 hr_pump_batch_line_user_keys uk
4989 where uk.user_key_value = p_emp_city_tax_rule_user_key
4990 and p_effective_date between puek.effective_start_date and puek.effective_end_date
4991 and puek.emp_city_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_city_tax_rule_ovn', sqlerrm,
4998 p_emp_city_tax_rule_user_key,
4999 p_effective_date);
5000 raise;
5001 end get_city_tax_rule_ovn;
5002
5003 --
5004 --
5005
5006 --
5007 -- -------------------------------------------------------------------------
5008 -- ------------< get_parent_comp_element_id >-------------------------
5009 -- -------------------------------------------------------------------------
5010 -- DESCRIPTION
5011 -- This function returns the
5012 --
5013 FUNCTION get_parent_comp_element_id
5014 RETURN BINARY_INTEGER
5015 IS
5016 BEGIN
5017 return (null);
5018 EXCEPTION
5019 WHEN OTHERS THEN
5020 hr_data_pump.fail('get_parent_comp_element_id'
5021 , sqlerrm
5022 );
5023 RAISE;
5024 END get_parent_comp_element_id;
5025 -- -------------------------------------------------------------------------
5026 -- --------------------< get_competence_id >--------------------------------
5027 -- -------------------------------------------------------------------------
5028 FUNCTION get_competence_id
5029 (p_data_pump_always_call IN varchar2
5030 ,p_competence_name IN VARCHAR2
5031 ,p_business_group_id IN NUMBER)
5032 RETURN BINARY_INTEGER
5033 IS
5034 l_competence_id NUMBER DEFAULT null;
5035 BEGIN
5036
5037 IF p_competence_name is NULL then
5038
5039 return null;
5040
5041 ELSIF p_competence_name = hr_api_g_varchar2 then
5042
5043 return hr_api_g_number;
5044
5045 ELSE
5046
5047 IF p_business_group_id is null THEN
5048
5049 SELECT competence_id
5050 INTO l_competence_id
5051 FROM per_competences_vl
5052 WHERE name = p_competence_name
5053 AND business_group_id is null;
5054
5055 ELSE
5056
5057 SELECT competence_id
5058 INTO l_competence_id
5059 FROM per_competences_vl
5060 WHERE name = p_competence_name
5061 AND business_group_id = p_business_group_id;
5062
5063 END IF;
5064
5065 END IF;
5066
5067 RETURN(l_competence_id);
5068 EXCEPTION
5069 WHEN OTHERS THEN
5070 hr_data_pump.fail('get_competence_id'
5071 , sqlerrm
5072 , p_competence_name
5073 , p_business_group_id);
5074 RAISE;
5075 END get_competence_id;
5076 -- -------------------------------------------------------------------------
5077 -- --------------------< get_cpn_ovn >---------------------------
5078 -- -------------------------------------------------------------------------
5079 -- DESCRIPTION
5080 -- This function returns the ovn of a competence
5081 --
5082 FUNCTION get_cpn_ovn
5083 (p_data_pump_always_call IN varchar2
5084 ,p_competence_name IN VARCHAR2
5085 ,p_business_group_id IN NUMBER)
5086 RETURN BINARY_INTEGER
5087 IS
5088 l_cpn_ovn NUMBER DEFAULT null;
5089 BEGIN
5090
5091 IF p_competence_name is NULL then
5092
5093 return null;
5094
5095 ELSIF p_competence_name = hr_api_g_varchar2 then
5096
5097 return hr_api_g_number;
5098
5099 ELSE
5100
5101 IF p_business_group_id is null THEN
5102
5103 SELECT object_version_number
5104 INTO l_cpn_ovn
5105 FROM per_competences_vl
5106 WHERE name = p_competence_name
5107 AND business_group_id is null;
5108
5109 ELSE
5110
5111 SELECT object_version_number
5112 INTO l_cpn_ovn
5113 FROM per_competences_vl
5114 WHERE name = p_competence_name
5115 AND business_group_id = p_business_group_id;
5116
5117 END IF;
5118
5119 END IF;
5120
5121 RETURN(l_cpn_ovn);
5122 EXCEPTION
5123 WHEN OTHERS THEN
5124 hr_data_pump.fail('get_cpn_ovn'
5125 , sqlerrm
5126 , p_competence_name
5127 , p_business_group_id);
5128 RAISE;
5129 END get_cpn_ovn;
5130 -- -------------------------------------------------------------------------
5131 -- -----------------< get_qualification_type_id >---------------------------
5132 -- -------------------------------------------------------------------------
5133 FUNCTION get_qualification_type_id
5134 (p_data_pump_always_call IN varchar2
5135 ,p_qualification_type_name IN VARCHAR2
5136 )
5137 RETURN BINARY_INTEGER
5138 IS
5139 l_qualification_type_id NUMBER DEFAULT null;
5140 BEGIN
5141
5142 IF p_qualification_type_name is NULL then
5143
5144 return null;
5145
5146 ELSIF p_qualification_type_name = hr_api_g_varchar2 then
5147
5148 return hr_api_g_number;
5149
5150 ELSE
5151
5152 SELECT qualification_type_id
5153 INTO l_qualification_type_id
5154 FROM per_qualification_types_vl
5155 WHERE name = p_qualification_type_name;
5156
5157 END IF;
5158
5159 RETURN(l_qualification_type_id);
5160 EXCEPTION
5161 WHEN OTHERS THEN
5162 hr_data_pump.fail('get_qualification_type_id'
5163 , sqlerrm
5164 , p_qualification_type_name);
5165 RAISE;
5166 END get_qualification_type_id;
5167 --
5168 -- -------------------------------------------------------------------------
5169 -- ---------------------< get_outcome_id >--------------------------------
5170 -- -------------------------------------------------------------------------
5171 FUNCTION get_outcome_id
5172 (p_data_pump_always_call IN varchar2
5173 ,p_outcome_name IN VARCHAR2
5174 )
5175 RETURN BINARY_INTEGER
5176 IS
5177 l_outcome_id NUMBER DEFAULT null;
5178 BEGIN
5179
5180 IF p_outcome_name is NULL then
5181
5182 return null;
5183
5184 ELSIF p_outcome_name = hr_api_g_varchar2 then
5185
5186 return hr_api_g_number;
5187
5188 ELSE
5189
5190 SELECT outcome_id
5191 INTO l_outcome_id
5192 FROM per_competence_outcomes_vl
5193 WHERE name = p_outcome_name;
5194
5195 END IF;
5196
5197 RETURN(l_outcome_id);
5198 EXCEPTION
5199 WHEN OTHERS THEN
5200 hr_data_pump.fail('get_outcome_id'
5201 , sqlerrm
5202 , p_outcome_name
5203 );
5204 RAISE;
5205 END get_outcome_id;
5206 -- -------------------------------------------------------------------------
5207 -- --------------------< get_cpo_ovn >---------------------------
5208 -- -------------------------------------------------------------------------
5209 -- DESCRIPTION
5210 -- This function returns the ovn of a competence outcome
5211 --
5212 FUNCTION get_cpo_ovn
5213 (p_data_pump_always_call IN varchar2
5214 ,p_outcome_name IN VARCHAR2
5215 )
5216 RETURN BINARY_INTEGER
5217 IS
5218 l_cpo_ovn NUMBER DEFAULT null;
5219 BEGIN
5220
5221 IF p_outcome_name is NULL then
5222
5223 return null;
5224
5225 ELSIF p_outcome_name = hr_api_g_varchar2 then
5226
5227 return hr_api_g_number;
5228
5229 ELSE
5230
5231 SELECT object_version_number
5232 INTO l_cpo_ovn
5233 FROM per_competence_outcomes_vl
5234 WHERE name = p_outcome_name;
5235
5236 END IF;
5237
5238 RETURN(l_cpo_ovn);
5239 EXCEPTION
5240 WHEN OTHERS THEN
5241 hr_data_pump.fail('get_cpo_ovn'
5242 , sqlerrm
5243 , p_outcome_name
5244 );
5245 RAISE;
5246 END get_cpo_ovn;
5247 -- -------------------------------------------------------------------------
5248 -- --------------------< get_eqt_ovn >---------------------------
5249 -- -------------------------------------------------------------------------
5250 -- DESCRIPTION
5251 -- This function returns the ovn of a qualification type
5252 --
5253 FUNCTION get_eqt_ovn
5254 (p_data_pump_always_call IN varchar2
5255 ,p_qualification_type_name IN VARCHAR2
5256 )
5257 RETURN BINARY_INTEGER
5258 IS
5259 l_eqt_ovn NUMBER DEFAULT null;
5260 BEGIN
5261
5262 IF p_qualification_type_name is NULL then
5263
5264 return null;
5265
5266 ELSIF p_qualification_type_name = hr_api_g_varchar2 then
5267
5268 return hr_api_g_number;
5269
5270 ELSE
5271
5272 SELECT object_version_number
5273 INTO l_eqt_ovn
5274 FROM per_qualification_types_vl
5275 WHERE name = p_qualification_type_name;
5276
5277 END IF;
5278
5279 RETURN(l_eqt_ovn);
5280 EXCEPTION
5281 WHEN OTHERS THEN
5282 hr_data_pump.fail('get_eqt_ovn'
5283 , sqlerrm
5284 , p_qualification_type_name
5285 );
5286 RAISE;
5287 END get_eqt_ovn;
5288 -- -------------------------------------------------------------------------
5289 -- --------------------< get_ceo_ovn >---------------------------
5290 -- -------------------------------------------------------------------------
5291 -- DESCRIPTION
5292 -- This function returns the ovn of a comp element outcomes
5293 --
5294 FUNCTION get_ceo_ovn
5295 (p_data_pump_always_call IN varchar2
5296 ,p_element_outcome_name IN VARCHAR2
5297 )
5298 RETURN BINARY_INTEGER
5299 IS
5300 l_ceo_ovn NUMBER DEFAULT null;
5301 BEGIN
5302
5303 IF p_element_outcome_name is NULL then
5304
5305 return null;
5306
5307 ELSIF p_element_outcome_name = hr_api_g_varchar2 then
5308
5309 return hr_api_g_number;
5310
5311 ELSE
5312
5313 SELECT object_version_number
5314 INTO l_ceo_ovn
5315 FROM per_comp_element_outcomes_vl
5316 WHERE name = p_element_outcome_name;
5317
5318 END IF;
5319
5320 RETURN(l_ceo_ovn);
5321 EXCEPTION
5322 WHEN OTHERS THEN
5323 hr_data_pump.fail('get_ceo_ovn'
5324 , sqlerrm
5325 , p_element_outcome_name
5326 );
5327 RAISE;
5328 END get_ceo_ovn;
5329 -- -------------------------------------------------------------------------
5330 -- ------------------< get_competence_element_id >------------------------
5331 -- -------------------------------------------------------------------------
5332 -- DESCRIPTION
5333 -- This function returns the ovn of a comp element outcomes
5334 --
5335 FUNCTION get_competence_element_id
5336 (p_data_pump_always_call in varchar2
5337 ,p_competence_name in varchar2
5338 ,p_person_user_key in varchar2
5339 ,p_business_group_id in number
5340 )
5341 return binary_integer
5342 IS
5343 l_competence_element_id number default null;
5344 l_person_id number default null;
5345 BEGIN
5346
5347 l_person_id := user_key_to_id(p_person_user_key);
5348
5349 SELECT competence_element_id
5350 INTO l_competence_element_id
5351 FROM per_competence_elements CEL
5352 ,per_competences CPN
5353 WHERE
5354 CEL.type = 'PERSONAL'
5355 and CPN.name = p_competence_name
5356 and CEL.competence_id = CPN.competence_id
5357 and CEL.business_group_id = p_business_group_id
5358 and CEL.person_id = l_person_id;
5359
5360 RETURN(l_competence_element_id);
5361 EXCEPTION
5362 WHEN OTHERS THEN
5363 hr_data_pump.fail('get_competence_element_id'
5364 , sqlerrm
5365 , p_competence_name
5366 , p_person_user_key
5367 , p_business_group_id
5368 );
5369 RAISE;
5370 END get_competence_element_id;
5371 --
5372 --
5373 FUNCTION get_cost_flex_stru_num
5374 (p_data_pump_always_call IN varchar2
5375 ,p_cost_flex_stru_code IN VARCHAR2
5376 )
5377 RETURN BINARY_INTEGER is
5378 l_cost_code number;
5379 begin
5380
5381 IF p_cost_flex_stru_code is NULL then
5382
5383 return null;
5384
5385 ELSIF p_cost_flex_stru_code = hr_api_g_varchar2 then
5386
5387 return hr_api_g_number;
5388
5389 ELSE
5390
5391 select id_flex_num into l_cost_code
5392 from fnd_id_flex_structures
5393 where id_flex_structure_code = p_cost_flex_stru_code
5394 and id_flex_code ='COST';
5395 END IF;
5396
5397 RETURN(l_cost_code);
5398 EXCEPTION
5399 WHEN OTHERS THEN
5400 hr_data_pump.fail('get_cost_flex_stru_num'
5401 , sqlerrm
5402 , p_cost_flex_stru_code
5403 );
5404 RAISE;
5405 END get_cost_flex_stru_num;
5406 --
5407 FUNCTION get_grade_flex_stru_num
5408 (p_data_pump_always_call IN varchar2
5409 ,p_grade_flex_stru_code IN VARCHAR2
5410 )
5411 RETURN BINARY_INTEGER is
5412 l_grade_code number;
5413 begin
5414
5415 IF p_grade_flex_stru_code is NULL then
5416
5417 return null;
5418
5419 ELSIF p_grade_flex_stru_code = hr_api_g_varchar2 then
5420
5421 return hr_api_g_number;
5422
5423 ELSE
5424 select id_flex_num into l_grade_code
5425 from fnd_id_flex_structures
5426 where id_flex_structure_code = p_grade_flex_stru_code
5427 and id_flex_code ='GRD';
5428
5429 END IF;
5430
5431 RETURN(l_grade_code);
5432 EXCEPTION
5433 WHEN OTHERS THEN
5434 hr_data_pump.fail('get_grade_flex_stru_num'
5435 , sqlerrm
5436 , p_grade_flex_stru_code
5437 );
5438 RAISE;
5439 END get_grade_flex_stru_num;
5440 --
5441 FUNCTION get_job_flex_stru_num
5442 (p_data_pump_always_call IN varchar2
5443 ,p_job_flex_stru_code IN VARCHAR2
5444 )
5445 RETURN BINARY_INTEGER is
5446 l_job_code number;
5447 begin
5448
5449 IF p_job_flex_stru_code is NULL then
5450
5451 return null;
5452
5453 ELSIF p_job_flex_stru_code = hr_api_g_varchar2 then
5454
5455 return hr_api_g_number;
5456
5457 ELSE
5458
5459 select id_flex_num into l_job_code
5460 from fnd_id_flex_structures
5461 where id_flex_structure_code = p_job_flex_stru_code
5462 and id_flex_code ='JOB';
5463 END IF;
5464
5465 RETURN(l_job_code);
5466 EXCEPTION
5467 WHEN OTHERS THEN
5468 hr_data_pump.fail('get_job_flex_stru_num'
5469 , sqlerrm
5470 , p_job_flex_stru_code
5471 );
5472 RAISE;
5473 END get_job_flex_stru_num;
5474 --
5475 FUNCTION get_position_flex_stru_num
5476 (p_data_pump_always_call IN varchar2
5477 ,p_position_flex_stru_code IN VARCHAR2
5478 )
5479 RETURN BINARY_INTEGER is
5480 l_position_code number;
5481 begin
5482
5483 IF p_position_flex_stru_code is NULL then
5484
5485 return null;
5486
5487 ELSIF p_position_flex_stru_code = hr_api_g_varchar2 then
5488
5489 return hr_api_g_number;
5490
5491 ELSE
5492
5493 select id_flex_num into l_position_code
5494 from fnd_id_flex_structures
5495 where id_flex_structure_code = p_position_flex_stru_code
5496 and id_flex_code ='POS';
5497 END IF;
5498
5499 RETURN(l_position_code);
5500 EXCEPTION
5501 WHEN OTHERS THEN
5502 hr_data_pump.fail('get_position_flex_stru_num'
5503 , sqlerrm
5504 , p_position_flex_stru_code
5505 );
5506 RAISE;
5507 END get_position_flex_stru_num;
5508 --
5509 FUNCTION get_group_flex_stru_num
5510 (p_data_pump_always_call IN varchar2
5511 ,p_group_flex_stru_code IN VARCHAR2
5512 )
5513 RETURN BINARY_INTEGER is
5514 l_group_code number;
5515 begin
5516
5517 IF p_group_flex_stru_code is NULL then
5518
5519 return null;
5520
5521 ELSIF p_group_flex_stru_code = hr_api_g_varchar2 then
5522
5523 return hr_api_g_number;
5524
5525 ELSE
5526
5527 select id_flex_num into l_group_code
5528 from fnd_id_flex_structures
5529 where id_flex_structure_code = p_group_flex_stru_code
5530 and id_flex_code ='GRP';
5531 END IF;
5532
5533 RETURN(l_group_code);
5534 EXCEPTION
5535 WHEN OTHERS THEN
5536 hr_data_pump.fail('get_group_flex_stru_num'
5537 , sqlerrm
5538 , p_group_flex_stru_code
5539 );
5540 RAISE;
5541 END get_group_flex_stru_num;
5542 --
5543 FUNCTION get_competence_flex_stru_num
5544 (p_data_pump_always_call IN varchar2
5545 ,p_competence_flex_stru_code IN VARCHAR2
5546 )
5547 RETURN BINARY_INTEGER is
5548 l_competence_code number;
5549 begin
5550
5551 IF p_competence_flex_stru_code is NULL then
5552
5553 return null;
5554
5555 ELSIF p_competence_flex_stru_code = hr_api_g_varchar2 then
5556
5557 return hr_api_g_number;
5558
5559 ELSE
5560
5561 select id_flex_num into l_competence_code
5562 from fnd_id_flex_structures
5563 where id_flex_structure_code = p_competence_flex_stru_code
5564 and id_flex_code ='CMP';
5565 END IF;
5566
5567 RETURN(l_competence_code);
5568 EXCEPTION
5569 WHEN OTHERS THEN
5570 hr_data_pump.fail('get_competence_flex_stru_num'
5574 RAISE;
5571 , sqlerrm
5572 , p_competence_flex_stru_code
5573 );
5575 END get_competence_flex_stru_num;
5576 --
5577 --
5578 FUNCTION get_sec_group_id
5579 (p_data_pump_always_call IN varchar2
5580 ,p_security_group_name IN VARCHAR2
5581 )
5582 RETURN BINARY_INTEGER is
5583 l_get_sec_group_id number;
5584 begin
5585
5586 IF p_security_group_name is NULL then
5587
5588 return null;
5589
5590 ELSIF p_security_group_name = hr_api_g_varchar2 then
5591
5592 return hr_api_g_number;
5593
5594 ELSE
5595
5596 select security_group_id into l_get_sec_group_id
5597 from fnd_security_groups_tl
5598 where upper(security_group_name) = upper(p_security_group_name)
5599 and language = userenv('LANG');
5600
5601 END IF;
5602
5603 RETURN(l_get_sec_group_id);
5604 EXCEPTION
5605 WHEN OTHERS THEN
5606 hr_data_pump.fail('get_sec_group_id'
5607 , sqlerrm
5608 , p_security_group_name
5609 );
5610 RAISE;
5611 END get_sec_group_id;
5612 --
5613 FUNCTION get_security_profile_id
5614 (p_data_pump_always_call IN VARCHAR2
5615 ,p_security_profile_name IN VARCHAR2
5616 ,p_business_group_id IN NUMBER
5617 )
5618 RETURN BINARY_INTEGER is
5619 l_sec_profile_id number;
5620 begin
5621
5622 IF p_security_profile_name is NULL then
5623
5624 return null;
5625
5626 ELSIF p_security_profile_name = hr_api_g_varchar2 then
5627
5628 return hr_api_g_number;
5629
5630 ELSE
5631
5632 select security_profile_id into l_sec_profile_id
5633 from per_security_profiles
5634 where upper(security_profile_name) = upper(p_security_profile_name)
5635 and business_group_id = p_business_group_id;
5636
5637 END IF;
5638
5639 RETURN(l_sec_profile_id);
5640 EXCEPTION
5641 WHEN OTHERS THEN
5642 hr_data_pump.fail('get_security_profile_id'
5643 , sqlerrm
5644 , p_security_profile_name
5645 , p_business_group_id
5646 );
5647 RAISE;
5648 END get_security_profile_id;
5649 --
5650 --
5651 FUNCTION get_parent_organization_id
5652 ( p_parent_organization_name in varchar2,
5653 p_business_group_id in number,
5654 p_effective_date in date,
5655 p_language_code in varchar2
5656 ) RETURN BINARY_INTEGER is
5657 l_organization_id number;
5658 BEGIN
5659 IF p_parent_organization_name is NULL then
5660
5661 return null;
5662
5663 ELSIF p_parent_organization_name = hr_api_g_varchar2 then
5664
5665 return hr_api_g_number;
5666
5667 ELSE
5668
5669 select org.organization_id
5670 into l_organization_id
5671 from hr_all_organization_units org
5672 , hr_all_organization_units_tl orgtl
5673 where orgtl.name = p_parent_organization_name
5674 and orgtl.language = p_language_code
5675 and org.organization_id = orgtl.organization_id
5676 and (org.business_group_id = p_business_group_id
5677 or p_business_group_id is null); --Bug 3823374
5678 END IF;
5679 return(l_organization_id);
5680 EXCEPTION
5681 WHEN OTHERS THEN
5682 hr_data_pump.fail('get_parent_organization_id', sqlerrm, p_parent_organization_name,
5683 p_business_group_id, p_effective_date, p_language_code);
5684 raise;
5685 end get_parent_organization_id;
5686 --
5687 --
5688 FUNCTION get_child_organization_id
5689 ( p_child_organization_name in varchar2,
5690 p_business_group_id in number,
5691 p_effective_date in date,
5692 p_language_code in varchar2
5693 ) RETURN BINARY_INTEGER is
5694 l_organization_id number;
5695 BEGIN
5696 IF p_child_organization_name is NULL then
5697
5698 return null;
5699
5700 ELSIF p_child_organization_name = hr_api_g_varchar2 then
5701
5702 return hr_api_g_number;
5703
5704 ELSE
5705 select org.organization_id
5706 into l_organization_id
5707 from hr_all_organization_units org
5708 , hr_all_organization_units_tl orgtl
5709 where orgtl.name = p_child_organization_name
5710 and orgtl.language = p_language_code
5711 and org.organization_id = orgtl.organization_id
5712 and (org.business_group_id = p_business_group_id
5713 or p_business_group_id is null); --Bug fix 3823374
5714 END IF;
5715 --
5716 return(l_organization_id);
5717 EXCEPTION
5718 WHEN OTHERS THEN
5719 hr_data_pump.fail('get_child_organization_id', sqlerrm, p_child_organization_name,
5720 p_business_group_id, p_effective_date, p_language_code);
5721 raise;
5722 end get_child_organization_id;
5723 --
5724 --
5725 function get_person_extra_info_id
5726 (
5727 p_person_extra_info_user_key in varchar2
5728 ) return number is
5729 l_person_extra_info_id number;
5730 begin
5731 l_person_extra_info_id := user_key_to_id( p_person_extra_info_user_key );
5732 return(l_person_extra_info_id);
5733 exception
5734 when others then
5735 hr_data_pump.fail('get_person_extra_info_id', sqlerrm, p_person_extra_info_user_key);
5736 raise;
5737 end get_person_extra_info_id;
5738 --
5739 --
5740 function get_person_extra_info_ovn
5741 ( p_person_extra_info_user_key in varchar2
5742 ) return number is
5743 l_ovn number;
5744 begin
5745 select pei.object_version_number
5746 into l_ovn
5747 from per_people_extra_info pei,
5748 hr_pump_batch_line_user_keys key
5749 where key.user_key_value = p_person_extra_info_user_key
5750 and pei.person_extra_info_id = key.unique_key_id;
5751 return(l_ovn);
5752 exception
5753 when others then
5754 hr_data_pump.fail('get_person_extra_info_ovn', sqlerrm, p_person_extra_info_user_key);
5755 raise;
5756 end get_person_extra_info_ovn;
5757
5758 --
5759 --
5760 /* GET_EMP_FED_TAX_INF_ID - requires user key */
5761 function GET_EMP_FED_TAX_INF_ID
5762 (
5763 P_EMP_FED_TAX_INF_USER_KEY in varchar2
5764 ) return number is
5765 l_emp_fed_tax_inf_id number;
5766 begin
5767 l_emp_fed_tax_inf_id := user_key_to_id( P_EMP_FED_TAX_INF_USER_KEY );
5768 return(l_emp_fed_tax_inf_id);
5769 exception
5770 when others then
5771 hr_data_pump.fail('GET_EMP_FED_TAX_INF_ID', sqlerrm, P_EMP_FED_TAX_INF_USER_KEY);
5772 raise;
5773 end GET_EMP_FED_TAX_INF_ID;
5774
5775
5776 --
5777 /* returns a Canada Employee federal tax Inf object version number */
5778 function GET_CA_EMP_FEDTAX_INF_OVN
5779 (
5780 P_EMP_FED_TAX_INF_USER_KEY in varchar2,
5781 p_effective_date in date
5782 ) return number is
5783 l_ovn number;
5784 begin
5785 select rules.object_version_number
5786 into l_ovn
5787 from PAY_CA_EMP_FED_TAX_INFO_F rules,
5788 hr_pump_batch_line_user_keys key
5789 where key.user_key_value = P_EMP_FED_TAX_INF_USER_KEY
5790 and rules.EMP_FED_TAX_INF_ID = key.unique_key_id
5791 and p_effective_date between
5792 rules.effective_start_date and rules.effective_end_date;
5793 return(l_ovn);
5794 exception
5795 when others then
5796 hr_data_pump.fail('GET_CA_EMP_FEDTAX_INF_OVN', sqlerrm,
5797 P_EMP_FED_TAX_INF_USER_KEY, p_effective_date);
5798 raise;
5799 end GET_CA_EMP_FEDTAX_INF_OVN;
5800
5801 --
5802 --
5803 /* GET_EMP_PROVINCE_TAX_INF_ID - requires user key */
5804 function GET_EMP_PROVINCE_TAX_INF_ID
5805 (
5806 P_EMP_PROV_TAX_INF_USER_KEY in varchar2
5807 ) return number is
5808 l_emp_prov_tax_inf_id number;
5809 begin
5810 l_emp_prov_tax_inf_id := user_key_to_id( P_EMP_PROV_TAX_INF_USER_KEY );
5811 return(l_emp_prov_tax_inf_id);
5812 exception
5813 when others then
5814 hr_data_pump.fail('GET_EMP_PROVINCE_TAX_INF_ID', sqlerrm, P_EMP_PROV_TAX_INF_USER_KEY);
5815 raise;
5816 end GET_EMP_PROVINCE_TAX_INF_ID;
5817
5818
5819 --
5820 /* returns a Canada Employee federal tax Inf object version number */
5821 function GET_CA_EMP_PRVTAX_INF_OVN
5822 (
5823 P_EMP_PROV_TAX_INF_USER_KEY in varchar2,
5824 p_effective_date in date
5825 ) return number is
5826 l_ovn number;
5827 begin
5828 select rules.object_version_number
5829 into l_ovn
5830 from PAY_CA_EMP_PROV_TAX_INFO_F rules,
5831 hr_pump_batch_line_user_keys key
5832 where key.user_key_value = P_EMP_PROV_TAX_INF_USER_KEY
5833 and rules.EMP_PROVINCE_TAX_INF_ID = key.unique_key_id
5834 and p_effective_date between
5835 rules.effective_start_date and rules.effective_end_date;
5836 return(l_ovn);
5837 exception
5838 when others then
5839 hr_data_pump.fail('GET_CA_EMP_PRVTAX_INF_OVN', sqlerrm,
5840 P_EMP_PROV_TAX_INF_USER_KEY, p_effective_date);
5841 raise;
5842 end GET_CA_EMP_PRVTAX_INF_OVN;
5843
5844
5845 --
5846 /*
5847 * Get ID initialisation section.
5848 */
5849 begin
5850 -- Initialise the debugging information structure.
5851 null;
5852
5853 end hr_pump_get;
[Home] [Help]