1 PACKAGE BODY per_in_data_pump AS
2 /* $Header: hrindpmf.pkb 120.0 2005/05/31 00:51 appldev noship $ */
3
4 -- -----------------------------------------------------------------------+
5 -- Name : user_key_to_id --+
6 -- Type : Function --+
7 -- Access : Public --+
8 -- Description : Returns the id corresponding to the user key --+
9 -- --+
10 -- Change History : --+
11 --------------------------------------------------------------------------+
12 -- Rev# Date Userid Description --+
13 --------------------------------------------------------------------------+
14 -- 1.0 15-APR-2004 gaugupta Created this function --+
15 --------------------------------------------------------------------------+
16 FUNCTION user_key_to_id( p_user_key_value IN VARCHAR2)
17 RETURN NUMBER IS
18 l_id NUMBER;
19 BEGIN
20 SELECT unique_key_id
21 INTO l_id
22 FROM hr_pump_batch_line_user_keys
23 WHERE user_key_value = p_user_key_value;
24
25 RETURN(l_id);
26 END user_key_to_id;
27
28
29 -- -----------------------------------------------------------------------+
30 -- Name : get_opm_ovn --+
31 -- Type : Function --+
32 -- Access : Public --+
33 -- Description : Returns the organization payment method ovn --+
34 -- --+
35 -- Change History : --+
36 --------------------------------------------------------------------------+
37 -- Rev# Date Userid Description --+
38 --------------------------------------------------------------------------+
39 -- 1.0 15-APR-2004 gaugupta Created this function --+
40 --------------------------------------------------------------------------+
41 FUNCTION get_opm_ovn
42 (
43 p_org_payment_method_user_key IN VARCHAR2,
44 p_effective_date IN DATE
45 ) RETURN NUMBER IS
46 l_ovn NUMBER;
47 BEGIN
48 SELECT opm.object_version_number
49 INTO l_ovn
50 FROM PAY_ORG_PAYMENT_METHODS_F opm
51 ,hr_pump_batch_line_user_keys key
52 WHERE key.user_key_value = p_org_payment_method_user_key
53 AND opm.ORG_PAYMENT_METHOD_ID = key.unique_key_id
54 AND p_effective_date between
55 opm.effective_start_date and opm.effective_end_date;
56 RETURN(l_ovn);
57 EXCEPTION
58 WHEN OTHERS THEN
59 hr_data_pump.fail('get_opm_ovn', sqlerrm,
60 p_org_payment_method_user_key ,p_effective_date);
61 RAISE;
62 END get_opm_ovn;
63
64 -- -----------------------------------------------------------------------+
65 -- Name : get_payment_type_id --+
66 -- Type : Function --+
67 -- Access : Public --+
68 -- Description : Returns the payment method type id --+
69 -- --+
70 -- Change History : --+
71 --------------------------------------------------------------------------+
72 -- Rev# Date Userid Description --+
73 --------------------------------------------------------------------------+
74 -- 1.0 15-APR-2004 gaugupta Created this function --+
75 --------------------------------------------------------------------------+
76
77 FUNCTION get_payment_type_id
78 (
79 payment_type_id IN NUMBER
80 ) RETURN NUMBER IS
81 BEGIN
82 RETURN payment_type_id;
83 END get_payment_type_id;
84
85 -- -----------------------------------------------------------------------+
86 -- Name : get_contact_relationship_ovn --+
87 -- Type : Function --+
88 -- Access : Public --+
89 -- Description : Returns the extra contact relationship ovn --+
90 -- --+
91 -- Change History : --+
92 --------------------------------------------------------------------------+
93 -- Rev# Date Userid Description --+
94 --------------------------------------------------------------------------+
95 -- 1.0 15-APR-2004 gaugupta Created this function --+
96 --------------------------------------------------------------------------+
97 FUNCTION get_contact_relationship_ovn(p_contact_rel_user_key IN VARCHAR2)
98 RETURN NUMBER IS
99 l_contact_relationship_ovn NUMBER;
100 BEGIN
101 SELECT ctk_rel.object_version_number
102 INTO l_contact_relationship_ovn
103 FROM per_contact_relationships ctk_rel,
104 hr_pump_batch_line_user_keys key
105 WHERE key.user_key_value = p_contact_rel_user_key
106 AND ctk_rel.contact_relationship_id = key.unique_key_id;
107
108 RETURN l_contact_relationship_ovn;
109
110 EXCEPTION
111 WHEN OTHERS THEN
112 hr_data_pump.fail('get_contact_relationship_ovn',
113 sqlerrm,
114 p_contact_rel_user_key);
115 RAISE;
116 END get_contact_relationship_ovn;
117
118
119 -- -----------------------------------------------------------------------+
120 -- Name : get_contact_extra_info_ovn --+
121 -- Type : Function --+
122 -- Access : Public --+
123 -- Description : Returns the contact extra info ovn --+
124 -- --+
125 -- Change History : --+
126 --------------------------------------------------------------------------+
127 -- Rev# Date Userid Bug Description --+
128 --------------------------------------------------------------------------+
129 -- 1.0 15-APR-2004 gaugupta Created this function --+
130 -- 1.1 23-Jul-2004 lnagaraj 3762728 Modified the cursor to take --+
131 -- p_contact_extra_info_user_key --+
132 -- and not p_person_extra_info_user--+
133 -- key --+
134 --------------------------------------------------------------------------+
135 FUNCTION get_contact_extra_info_ovn
136 (
137 p_contact_extra_info_user_key IN VARCHAR2,
138 p_effective_date IN DATE
139 ) RETURN NUMBER IS
140 l_ovn NUMBER;
141 BEGIN
142 SELECT contact_info.object_version_number
143 INTO l_ovn
144 FROM per_contact_extra_info_f contact_info,
145 hr_pump_batch_line_user_keys key
146 WHERE key.user_key_value = p_contact_extra_info_user_key
147 AND contact_info.contact_extra_info_id = key.unique_key_id
148 AND p_effective_date BETWEEN
149 contact_info.effective_start_date AND contact_info.effective_end_date;
150 RETURN(l_ovn);
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 hr_data_pump.fail('get_contact_extra_info_ovn', sqlerrm, p_contact_extra_info_user_key ,
155 p_effective_date);
156 RAISE;
157 END get_contact_extra_info_ovn;
158
159 -- -----------------------------------------------------------------------+
160 -- Name : get_person_extra_info_ovn --+
161 -- Type : Function --+
162 -- Access : Public --+
163 -- Description : Returns the extra person information ovn --+
164 -- --+
165 -- Change History : --+
166 --------------------------------------------------------------------------+
167 -- Rev# Date Userid Description --+
168 --------------------------------------------------------------------------+
169 -- 1.0 15-APR-2004 gaugupta Created this function --+
170 --------------------------------------------------------------------------+
171 FUNCTION get_person_extra_info_ovn(p_person_extra_info_user_key IN VARCHAR2)
172 RETURN NUMBER IS
173 l_person_extra_info_ovn NUMBER;
174 BEGIN
175 SELECT people_extra_info.object_version_number
176 INTO l_person_extra_info_ovn
177 FROM per_people_extra_info people_extra_info,
178 hr_pump_batch_line_user_keys key
179 WHERE key.user_key_value = p_person_extra_info_user_key
180 AND people_extra_info.PERSON_EXTRA_INFO_ID = key.unique_key_id;
181
182 RETURN l_person_extra_info_ovn;
183 EXCEPTION
184 WHEN OTHERS THEN
185 hr_data_pump.fail('get_person_extra_info_ovn',
186 sqlerrm,
187 p_person_extra_info_user_key );
188 RAISE;
189 END get_person_extra_info_ovn;
190
191 -- -----------------------------------------------------------------------+
192 -- Name : get_person_extra_info_id --+
193 -- Type : Function --+
194 -- Access : Public --+
195 -- Description : Returns the extra person information id --+
196 -- --+
197 -- Change History : --+
198 --------------------------------------------------------------------------+
199 -- Rev# Date Userid Description --+
200 --------------------------------------------------------------------------+
201 -- 1.0 15-APR-2004 gaugupta Created this function --+
202 --------------------------------------------------------------------------+
203 FUNCTION get_person_extra_info_id(p_person_extra_info_user_key IN VARCHAR2)
204 RETURN NUMBER IS
205 l_person_extra_info_id NUMBER;
206 BEGIN
207 l_person_extra_info_id := user_key_to_id(p_person_extra_info_user_key);
208
209 RETURN(l_person_extra_info_id);
210 EXCEPTION
211 WHEN OTHERS THEN
212 hr_data_pump.fail('get_person_extra_info_id', sqlerrm, p_person_extra_info_user_key);
213 RAISE;
214 END get_person_extra_info_id;
215
216
217
218
219
220 -- -----------------------------------------------------------------------+
221 -- Name : get_passport_country --+
222 -- Type : Function --+
223 -- Access : Public --+
224 -- Description : Returns the country code --+
225 -- --+
226 -- Change History : --+
227 --------------------------------------------------------------------------+
228 -- Rev# Date Userid Description --+
229 --------------------------------------------------------------------------+
230 -- 1.0 26-May-2004 gaugupta Created this function --+
231 --------------------------------------------------------------------------+
232 FUNCTION get_passport_country(p_issuing_country IN VARCHAR2)
233 RETURN VARCHAR2 IS
234 l_territory_code VARCHAR2(2);
235 BEGIN
236 SELECT ttl.territory_code into l_territory_code
237 FROM FND_TERRITORIES_TL ttl , FND_TERRITORIES t
238 WHERE ttl.TERRITORY_SHORT_NAME = p_issuing_country
239 AND ttl.TERRITORY_CODE = t.TERRITORY_CODE;
240
241 RETURN l_territory_code;
242 EXCEPTION
243 WHEN OTHERS THEN
244 hr_data_pump.fail('get_passport_country',
245 sqlerrm,
246 p_issuing_country );
247 RAISE;
248 END get_passport_country;
249
250
251 -- -----------------------------------------------------------------------+
252 -- Name : get_contact_extra_info_id --+
253 -- Type : Function --+
254 -- Access : Public --+
255 -- Description : Returns the extra contact relationship id --+
256 -- --+
257 -- Change History : --+
258 --------------------------------------------------------------------------+
259 -- Rev# Date Userid Description --+
260 --------------------------------------------------------------------------+
261 -- 1.0 15-APR-2004 gaugupta Created this function --+
262 --------------------------------------------------------------------------+
263 FUNCTION get_contact_extra_info_id(p_contact_extra_info_user_key IN VARCHAR2)
264 RETURN NUMBER IS
265 l_contact_extra_info_id NUMBER;
266 BEGIN
267 l_contact_extra_info_id := user_key_to_id(p_contact_extra_info_user_key);
268
269 RETURN(l_contact_extra_info_id);
270 EXCEPTION
271 WHEN OTHERS THEN
272 hr_data_pump.fail('get_contact_extra_info_id', sqlerrm,p_contact_extra_info_user_key);
273 RAISE;
274 END get_contact_extra_info_id;
275
276 -- -----------------------------------------------------------------------+
277 -- Name : get_sets_of_book_id --+
278 -- Type : Function --+
279 -- Access : Public --+
280 -- Description : Returns the Sets Of Book id --+
281 -- --+
282 -- Change History : --+
283 --------------------------------------------------------------------------+
284 -- Rev# Date Userid Description --+
285 --------------------------------------------------------------------------+
286 -- 1.0 15-APR-2004 gaugupta Created this function --+
287 --------------------------------------------------------------------------+
288
289 FUNCTION get_sets_of_book_id
290 (
294 RETURN(hr_pump_get.get_set_of_books_id(p_sets_of_book_name)) ;
291 p_sets_of_book_name IN VARCHAR2
292 ) RETURN NUMBER IS
293 BEGIN
295 END get_sets_of_book_id;
296
297 -- -----------------------------------------------------------------------+
298 -- Name : get_id --+
299 -- Type : Function --+
300 -- Access : Public --+
301 -- Description : Returns the id of a given organization name. --+
302 -- --+
303 -- Change History : --+
304 --------------------------------------------------------------------------+
305 -- Rev# Date Userid Bug Description --+
306 --------------------------------------------------------------------------+
307 -- 1.0 15-APR-2004 gaugupta Created this function --+
308 -- 1.1 19-Jul-2004 lnagaraj3762728 changed select stmt to get --+
309 -- organization id
310 --------------------------------------------------------------------------+
311 FUNCTION get_id( p_org_name IN VARCHAR2)
312 RETURN NUMBER IS
313 l_organization_id HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE;
314 BEGIN
315
316 SELECT organization_id INTO l_organization_id
317 FROM HR_ALL_ORGANIZATION_UNITS
318 WHERE name = p_org_name;
319
320 RETURN (l_organization_id);
321 END get_id;
322
323 -- -----------------------------------------------------------------------+
324 -- Name : get_gre_id --+
325 -- Type : Function --+
326 -- Access : Public --+
327 -- Description : Returns the id of the GRE/LEGAL entity --+
328 -- --+
329 -- Change History : --+
330 --------------------------------------------------------------------------+
331 -- Rev# Date Userid Description --+
332 --------------------------------------------------------------------------+
333 -- 1.0 15-APR-2004 gaugupta Created this function --+
334 --------------------------------------------------------------------------+
335 FUNCTION get_gre_id(p_gre_legal_entity IN VARCHAR2)
336 RETURN NUMBER IS
337 BEGIN
338 RETURN get_id(p_gre_legal_entity );
339
340 EXCEPTION
341 WHEN OTHERS THEN
342 hr_data_pump.fail('get_gre_id', sqlerrm,p_gre_legal_entity);
343 RAISE;
344 END get_gre_id;
345
346
347 -- -----------------------------------------------------------------------+
348 -- Name : get_pf_org_id --+
349 -- Type : Function --+
350 -- Access : Public --+
351 -- Description : Returns the id of the PF organization --+
352 -- --+
353 -- Change History : --+
354 --------------------------------------------------------------------------+
355 -- Rev# Date Userid Description --+
356 --------------------------------------------------------------------------+
357 -- 1.0 15-APR-2004 gaugupta Created this function --+
358 --------------------------------------------------------------------------+
359
360 FUNCTION get_pf_org_id(p_pf_organization IN VARCHAR2)
361 RETURN NUMBER IS
362 BEGIN
363 RETURN get_id( p_pf_organization);
364
365 EXCEPTION
366 WHEN OTHERS THEN
367 hr_data_pump.fail('get_pf_org_id', sqlerrm,p_pf_organization);
368 RAISE;
369 END get_pf_org_id;
370
371 -- -----------------------------------------------------------------------+
372 -- Name : get_prof_tax_org_id --+
373 -- Type : Function --+
374 -- Access : Public --+
375 -- Description : Returns the id of the Proff Tax organization --+
376 -- --+
377 -- Change History : --+
378 --------------------------------------------------------------------------+
379 -- Rev# Date Userid Description --+
380 --------------------------------------------------------------------------+
381 -- 1.0 15-APR-2004 gaugupta Created this function --+
382 --------------------------------------------------------------------------+
383 FUNCTION get_prof_tax_org_id(p_prof_tax_organization IN VARCHAR2)
384 RETURN NUMBER IS
385 BEGIN
386 RETURN get_id( p_prof_tax_organization);
387
388 EXCEPTION
389 WHEN OTHERS THEN
390 hr_data_pump.fail('get_prof_tax_org_id', sqlerrm,p_prof_tax_organization);
391 RAISE;
392 END get_prof_tax_org_id;
393
394 -- -----------------------------------------------------------------------+
395 -- Name : get_esi_id --+
396 -- Type : Function --+
400 -- Change History : --+
397 -- Access : Public --+
398 -- Description : Returns the id of the esi organization --+
399 -- --+
401 --------------------------------------------------------------------------+
402 -- Rev# Date Userid Description --+
403 --------------------------------------------------------------------------+
404 -- 1.0 15-APR-2004 gaugupta Created this function --+
405 --------------------------------------------------------------------------+
406 FUNCTION get_esi_id(p_esi_organization IN VARCHAR2)
407 RETURN NUMBER IS
408 BEGIN
409 RETURN get_id( p_esi_organization);
410
411 EXCEPTION
412 WHEN OTHERS THEN
413 hr_data_pump.fail('get_esi_id', sqlerrm,p_esi_organization);
414 RAISE;
415
416 END get_esi_id;
417
418 -- -----------------------------------------------------------------------+
419 -- Name : get_factory_id --+
420 -- Type : Function --+
421 -- Access : Public --+
422 -- Description : Returns the id of the factory organization --+
423 -- --+
424 -- Change History : --+
425 --------------------------------------------------------------------------+
426 -- Rev# Date Userid Description --+
427 --------------------------------------------------------------------------+
428 -- 1.0 15-APR-2004 gaugupta Created this function --+
429 --------------------------------------------------------------------------+
430 FUNCTION get_factory_id(p_factory IN VARCHAR2)
431 RETURN NUMBER IS
432 BEGIN
433 RETURN get_id( p_factory);
434
435 EXCEPTION
436 WHEN OTHERS THEN
437 hr_data_pump.fail('get_factory_id', sqlerrm,p_factory);
438 RAISE;
439
440 END get_factory_id;
441
442 -- -----------------------------------------------------------------------+
443 -- Name : get_est_id --+
444 -- Type : Function --+
445 -- Access : Public --+
446 -- Description : Returns the id of the Establishment --+
447 -- --+
448 -- Change History : --+
449 --------------------------------------------------------------------------+
450 -- Rev# Date Userid Description --+
451 --------------------------------------------------------------------------+
452 -- 1.0 15-APR-2004 gaugupta Created this function --+
453 --------------------------------------------------------------------------+
454 FUNCTION get_est_id(p_establishment IN VARCHAR2)
455 RETURN NUMBER IS
456 BEGIN
457 RETURN get_id( p_establishment);
458
459 EXCEPTION
460 WHEN OTHERS THEN
461 hr_data_pump.fail('get_est_id', sqlerrm,p_establishment);
462 RAISE;
463
464 END get_est_id;
465
466
467
468
469 -- -----------------------------------------------------------------------+
470 -- Name : get_tp_header_id --+
471 -- Type : Function --+
472 -- Access : Public --+
473 -- Description : --+
474 -- --+
475 -- Change History : --+
476 --------------------------------------------------------------------------+
477 -- Rev# Date Userid Description --+
478 --------------------------------------------------------------------------+
479 -- 1.0 15-APR-2004 gaugupta Created this function --+
480 --------------------------------------------------------------------------+
481 FUNCTION get_tp_header_id
482 RETURN NUMBER IS
483 BEGIN
484 RETURN null;
485 END get_tp_header_id;
486
487 -- -----------------------------------------------------------------------+
488 -- Name : get_designated_receiver_id --+
489 -- Type : Function --+
490 -- Access : Public --+
491 -- Description : --+
492 -- --+
493 -- Change History : --+
494 --------------------------------------------------------------------------+
495 -- Rev# Date Userid Description --+
496 --------------------------------------------------------------------------+
497 -- 1.0 15-APR-2004 gaugupta Created this function --+
498 --------------------------------------------------------------------------+
499 FUNCTION get_designated_receiver_id
500 RETURN NUMBER IS
501 BEGIN
502 RETURN null;
503 END get_designated_receiver_id;
507 -- Name : get_operating_unit_id --+
504
505
506 -- -----------------------------------------------------------------------+
508 -- Type : Function --+
509 -- Access : Public --+
510 -- Description : --+
511 -- --+
512 -- Change History : --+
513 --------------------------------------------------------------------------+
514 -- Rev# Date Userid Description --+
515 --------------------------------------------------------------------------+
516 -- 1.0 15-APR-2004 gaugupta Created this function --+
517 --------------------------------------------------------------------------+
518 FUNCTION get_operating_unit_id
519 RETURN NUMBER IS
520 BEGIN
521 RETURN null;
522 END get_operating_unit_id;
523
524 -- -----------------------------------------------------------------------+
525 -- Name : get_inventory_organization_id --+
526 -- Type : Function --+
527 -- Access : Public --+
528 -- Description : --+
529 -- --+
530 -- Change History : --+
531 --------------------------------------------------------------------------+
532 -- Rev# Date Userid Description --+
533 --------------------------------------------------------------------------+
534 -- 1.0 15-APR-2004 gaugupta Created this function --+
535 --------------------------------------------------------------------------+
536 FUNCTION get_inventory_organization_id
537 RETURN NUMBER IS
538 BEGIN
539 RETURN null;
540 END get_inventory_organization_id;
541
542 -- -----------------------------------------------------------------------+
543 -- Name : get_ship_to_location_id --+
544 -- Type : Function --+
545 -- Access : Public --+
546 -- Description : --+
547 -- --+
548 -- Change History : --+
549 --------------------------------------------------------------------------+
550 -- Rev# Date Userid Description --+
551 --------------------------------------------------------------------------+
552 -- 1.0 15-APR-2004 gaugupta Created this function --+
553 --------------------------------------------------------------------------+
554 FUNCTION get_ship_to_location_id(p_ship_to_location_id IN NUMBER)
555 RETURN NUMBER IS
556 BEGIN
557 RETURN p_ship_to_location_id;
558 END get_ship_to_location_id;
559
560 -- -----------------------------------------------------------------------+
561 -- Name : get_vendor_site_id --+
562 -- Type : Function --+
563 -- Access : Public --+
564 -- Description : --+
565 -- --+
566 -- Change History : --+
567 --------------------------------------------------------------------------+
568 -- Rev# Date Userid Description --+
569 --------------------------------------------------------------------------+
570 -- 1.0 15-APR-2004 gaugupta Created this function --+
571 --------------------------------------------------------------------------+
572 FUNCTION get_vendor_site_id (p_vendor_site_id IN NUMBER)
573 RETURN NUMBER IS
574 BEGIN
575 RETURN p_vendor_site_id;
576 END get_vendor_site_id;
577
578
579 -- -----------------------------------------------------------------------+
580 -- Name : get_po_header_id --+
581 -- Type : Function --+
582 -- Access : Public --+
583 -- Description : --+
584 -- --+
585 -- Change History : --+
586 --------------------------------------------------------------------------+
587 -- Rev# Date Userid Description --+
588 --------------------------------------------------------------------------+
589 -- 1.0 15-APR-2004 gaugupta Created this function --+
590 --------------------------------------------------------------------------+
591 FUNCTION get_po_header_id(p_po_header_id IN NUMBER)
592 RETURN NUMBER IS
593 BEGIN
594 RETURN p_po_header_id;
595 END get_po_header_id;
596
597 -- -----------------------------------------------------------------------+
598 -- Name : get_po_line_id --+
599 -- Type : Function --+
603 -- Change History : --+
600 -- Access : Public --+
601 -- Description : --+
602 -- --+
604 --------------------------------------------------------------------------+
605 -- Rev# Date Userid Description --+
606 --------------------------------------------------------------------------+
607 -- 1.0 15-APR-2004 gaugupta Created this function --+
608 --------------------------------------------------------------------------+
609 FUNCTION get_po_line_id (p_po_line_id IN NUMBER)
610 RETURN NUMBER IS
611 BEGIN
612 RETURN p_po_line_id;
613 END get_po_line_id;
614
615 -- -----------------------------------------------------------------------+
616 -- Name : get_issue_date --+
617 -- Type : Function --+
618 -- Access : Public --+
619 -- Description : --+
620 -- --+
621 -- Change History : --+
622 --------------------------------------------------------------------------+
623 -- Rev# Date Userid Bug Description --+
624 --------------------------------------------------------------------------+
625 -- 1.0 22-Jul-2004 LNAGARAJ 3762728 Created this function --+
626 --------------------------------------------------------------------------+
627 FUNCTION get_issue_date(p_issue_date VARCHAR2)
628 RETURN varchar2 IS
629 BEGIN
630 RETURN fnd_date.date_to_canonical(fnd_date.chardt_to_date(p_issue_date));
631 EXCEPTION
632 WHEN OTHERS THEN
633 hr_data_pump.fail('get_issue_date', sqlerrm, p_issue_date);
634 RAISE;
635 END get_issue_date;
636
637 -- -----------------------------------------------------------------------+
638 -- Name : get_birth_date --+
639 -- Type : Function --+
640 -- Access : Public --+
641 -- Description : --+
642 -- --+
643 -- Change History : --+
644 --------------------------------------------------------------------------+
645 -- Rev# Date Userid Bug Description --+
646 --------------------------------------------------------------------------+
647 -- 1.0 22-Jul-2004 LNAGARAJ 3762728 Created this function --+
648 --------------------------------------------------------------------------+
649
650 FUNCTION get_birth_date(p_guardian_birth_date VARCHAR2)
651 RETURN varchar2 IS
652 BEGIN
653 RETURN fnd_date.date_to_canonical(fnd_date.chardt_to_date(p_guardian_birth_date));
654 EXCEPTION
655 WHEN OTHERS THEN
656 hr_data_pump.fail('get_birth_date', sqlerrm, p_guardian_birth_date);
657 RAISE;
658 END get_birth_date;
659
660 -- -----------------------------------------------------------------------+
661 -- Name : get_expiry_date --+
662 -- Type : Function --+
663 -- Access : Public --+
667 --------------------------------------------------------------------------+
664 -- Description : --+
665 -- --+
666 -- Change History : --+
668 -- Rev# Date Userid Bug Description --+
669 --------------------------------------------------------------------------+
670 -- 1.0 22-Jul-2004 LNAGARAJ 3762728 Created this function --+
671 --------------------------------------------------------------------------+
672 FUNCTION get_expiry_date(p_expiry_date VARCHAR2)
673 RETURN varchar2 IS
674 BEGIN
675 RETURN fnd_date.date_to_canonical(fnd_date.chardt_to_date(p_expiry_date));
676 EXCEPTION
677 WHEN OTHERS THEN
678 hr_data_pump.fail('get_expiry_date', sqlerrm, p_expiry_date);
679 RAISE;
680 END get_expiry_date;
681
682 -- -----------------------------------------------------------------------+
683 -- Name : get_height --+
684 -- Type : Function --+
685 -- Access : Public --+
686 -- Description : --+
687 -- --+
688 -- Change History : --+
689 --------------------------------------------------------------------------+
690 -- Rev# Date Userid Bug Description --+
691 --------------------------------------------------------------------------+
692 -- 1.0 22-Jul-2004 LNAGARAJ 3762728 Created this function --+
693 --------------------------------------------------------------------------+
694 FUNCTION get_height(p_height IN VARCHAR2)
695 RETURN VARCHAR2 IS
696 l_height NUMBER;
697 E_INVALID_DATA_ERR EXCEPTION;
698 BEGIN
699 l_height := to_number(p_height);
700 IF l_height >=1.00 AND L_HEIGHT<=3.00 THEN
701 RETURN(p_height);
702 ELSE
703 RAISE E_INVALID_DATA_ERR;
704 END IF;
705 EXCEPTION
706 WHEN E_INVALID_DATA_ERR THEN
707 hr_data_pump.fail('get_height', sqlerrm, p_height);
708 RAISE;
709 WHEN OTHERS THEN
710 hr_data_pump.fail('get_height', sqlerrm, p_height);
711 RAISE;
712 END get_height;
713 -- -----------------------------------------------------------------------+
714 -- Name : get_weight --+
715 -- Type : Function --+
716 -- Access : Public --+
717 -- Description : --+
718 -- --+
719 -- Change History : --+
720 --------------------------------------------------------------------------+
721 -- Rev# Date Userid Bug Description --+
722 --------------------------------------------------------------------------+
723 -- 1.0 22-Jul-2004 LNAGARAJ 3762728 Created this function --+
724 --------------------------------------------------------------------------+
725 FUNCTION get_weight(p_weight IN VARCHAR2)
726 RETURN VARCHAR2 IS
727 l_weight NUMBER;
728 E_INVALID_DATA_ERR EXCEPTION;
729 BEGIN
730 l_weight := to_number(p_weight);
731 IF l_weight >=0.00 AND l_weight<=999.99 THEN
732 RETURN(p_weight);
733 ELSE
734 RAISE E_INVALID_DATA_ERR;
735 END IF;
736 EXCEPTION
737 WHEN E_INVALID_DATA_ERR THEN
738 hr_data_pump.fail('get_weight', sqlerrm, p_weight);
739 RAISE;
740 WHEN OTHERS THEN
741 hr_data_pump.fail('get_weight', sqlerrm, p_weight);
742 RAISE;
743 END get_weight;
744
745 -- -----------------------------------------------------------------------+
746 -- Name : get_scl_contractor_id --+
747 -- Type : Function --+
748 -- Access : Public --+
749 -- Description : Returns the id of the Contractor --+
750 -- --+
751 -- Change History : --+
752 --------------------------------------------------------------------------+
753 -- Rev# Date Userid Bug Description --+
754 --------------------------------------------------------------------------+
755 -- 1.0 19-Jul-2004 lnagaraj 3762728 Created this function --+
756 --------------------------------------------------------------------------+
757 FUNCTION get_scl_contractor_id(p_scl_contractor_name IN VARCHAR2)
758 RETURN NUMBER IS
759 BEGIN
760 RETURN get_id( p_scl_contractor_name);
761
762 EXCEPTION
763 WHEN OTHERS THEN
764 hr_data_pump.fail('get_scl_contractor_id', sqlerrm,p_scl_contractor_name);
765 RAISE;
766
767 END get_scl_contractor_id;
768
769 END per_in_data_pump ;
[Home] [Help]