[Home] [Help]
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 )
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:
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
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
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;
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
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
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
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;
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;
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
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;
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;
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;
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;
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;
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;
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
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
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;
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
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
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;
3544: begin
3545: select pps.period_of_service_id
3546: into l_period_of_service_id
3547: from per_periods_of_service pps,
3548: hr_pump_batch_line_user_keys key
3549: where key.user_key_value = p_person_user_key
3550: and pps.person_id = key.unique_key_id
3551: and pps.business_group_id = p_business_group_id
3552: and pps.actual_termination_date is null;
3569: begin
3570: select pps.period_of_service_id
3571: into l_period_of_service_id
3572: from per_periods_of_service pps,
3573: hr_pump_batch_line_user_keys key
3574: where key.user_key_value = p_person_user_key
3575: and pps.person_id = key.unique_key_id
3576: and pps.business_group_id = p_business_group_id
3577: and pps.actual_termination_date is not null
3599:
3600: select pps.period_of_service_id
3601: into l_period_of_service_id
3602: from per_periods_of_service pps,
3603: hr_pump_batch_line_user_keys key
3604: where key.user_key_value = p_person_user_key
3605: and pps.person_id = key.unique_key_id
3606: and pps.business_group_id = p_business_group_id
3607: and p_effective_date between pps.date_start and NVL(pps.actual_termination_date,l_eot);
3788: begin
3789: select pc.object_version_number
3790: into l_object_version_number
3791: from per_contracts_f pc,
3792: hr_pump_batch_line_user_keys key
3793: where key.user_key_value = p_contract_user_key
3794: and pc.contract_id = key.unique_key_id
3795: and p_effective_date between
3796: pc.effective_start_date and pc.effective_end_date;
3831: begin
3832: select rules.object_version_number
3833: into l_ovn
3834: from pay_us_emp_fed_tax_rules_f rules,
3835: hr_pump_batch_line_user_keys key
3836: where key.user_key_value = p_emp_fed_tax_rule_user_key
3837: and rules.emp_fed_tax_rule_id = key.unique_key_id
3838: and p_effective_date between
3839: rules.effective_start_date and rules.effective_end_date;
3855: begin
3856: select rules.object_version_number
3857: into l_ovn
3858: from pay_us_emp_state_tax_rules_f rules,
3859: hr_pump_batch_line_user_keys key
3860: where key.user_key_value = p_emp_state_tax_rule_user_key
3861: and rules.emp_state_tax_rule_id = key.unique_key_id
3862: and p_effective_date between
3863: rules.effective_start_date and rules.effective_end_date;
3879: begin
3880: select rules.object_version_number
3881: into l_ovn
3882: from pay_us_emp_county_tax_rules_f rules,
3883: hr_pump_batch_line_user_keys key
3884: where key.user_key_value = p_emp_county_tax_rule_user_key
3885: and rules.emp_county_tax_rule_id = key.unique_key_id
3886: and p_effective_date between
3887: rules.effective_start_date and rules.effective_end_date;
3903: begin
3904: select rules.object_version_number
3905: into l_ovn
3906: from pay_us_emp_city_tax_rules_f rules,
3907: hr_pump_batch_line_user_keys key
3908: where key.user_key_value = p_emp_city_tax_rule_user_key
3909: and rules.emp_city_tax_rule_id = key.unique_key_id
3910: and p_effective_date between
3911: rules.effective_start_date and rules.effective_end_date;
3927: begin
3928: select per.object_version_number
3929: into l_ovn
3930: from per_people_f per,
3931: hr_pump_batch_line_user_keys key
3932: where key.user_key_value = p_person_user_key
3933: and per.person_id = key.unique_key_id
3934: and p_effective_date between
3935: per.effective_start_date and per.effective_end_date;
3951: begin
3952: select asg.object_version_number
3953: into l_ovn
3954: from per_assignments_f asg,
3955: hr_pump_batch_line_user_keys key
3956: where key.user_key_value = p_assignment_user_key
3957: and asg.assignment_id = key.unique_key_id
3958: and p_effective_date between
3959: asg.effective_start_date and asg.effective_end_date;
3975: begin
3976: select adr.object_version_number
3977: into l_ovn
3978: from per_addresses adr,
3979: hr_pump_batch_line_user_keys key
3980: where key.user_key_value = p_address_user_key
3981: and adr.address_id = key.unique_key_id;
3982: return(l_ovn);
3983: exception
4132: begin
4133: select ppm.object_version_number
4134: into l_ovn
4135: from pay_personal_payment_methods_f ppm
4136: , hr_pump_batch_line_user_keys key
4137: where key.user_key_value = p_personal_pay_method_user_key
4138: and ppm.personal_payment_method_id = key.unique_key_id
4139: and p_effective_date between
4140: ppm.effective_start_date and ppm.effective_end_date;
4156: begin
4157: select pee.object_version_number
4158: into l_element_entry_ovn
4159: from pay_element_entries_f pee,
4160: hr_pump_batch_line_user_keys key
4161: where key.user_key_value = p_element_entry_user_key
4162: and pee.element_entry_id = key.unique_key_id
4163: and p_effective_date between
4164: pee.effective_start_date and pee.effective_end_date;
4254: begin
4255: select pps.object_version_number
4256: into l_object_version_number
4257: from per_periods_of_service pps,
4258: hr_pump_batch_line_user_keys key
4259: where key.user_key_value = p_person_user_key
4260: and pps.person_id = key.unique_key_id
4261: and pps.business_group_id = p_business_group_id
4262: and pps.actual_termination_date is null;
4279: begin
4280: select pps.object_version_number
4281: into l_object_version_number
4282: from per_periods_of_service pps,
4283: hr_pump_batch_line_user_keys key
4284: where key.user_key_value = p_person_user_key
4285: and pps.person_id = key.unique_key_id
4286: and pps.business_group_id = p_business_group_id
4287: and pps.actual_termination_date is not null
4309:
4310: select pps.object_version_number
4311: into l_object_version_number
4312: from per_periods_of_service pps,
4313: hr_pump_batch_line_user_keys key
4314: where key.user_key_value = p_person_user_key
4315: and pps.person_id = key.unique_key_id
4316: and pps.business_group_id = p_business_group_id
4317: and p_effective_date between pps.date_start and NVL(pps.actual_termination_date,l_eot);
4363: begin
4364: select phn.object_version_number
4365: into l_ovn
4366: from per_phones phn,
4367: hr_pump_batch_line_user_keys key
4368: where key.user_key_value = p_phone_user_key
4369: and phn.phone_id = key.unique_key_id;
4370: return(l_ovn);
4371: exception
4385: begin
4386: select jgr.object_version_number
4387: into l_ovn
4388: from per_job_groups jgr,
4389: hr_pump_batch_line_user_keys key
4390: where key.user_key_value = p_job_group_user_key
4391: and jgr.job_group_id = key.unique_key_id;
4392: return(l_ovn);
4393: exception
4407: begin
4408: select rol.object_version_number
4409: into l_ovn
4410: from per_roles rol,
4411: hr_pump_batch_line_user_keys key
4412: where key.user_key_value = p_role_user_key
4413: and rol.role_id = key.unique_key_id;
4414: return(l_ovn);
4415: exception
4575: select flv.lookup_code
4576: into l_code
4577: from fnd_lookup_values flv,
4578: per_assignments_f asg,
4579: hr_pump_batch_line_user_keys key
4580: where key.user_key_value = p_assignment_user_key
4581: and asg.assignment_id = key.unique_key_id
4582: and p_effective_date between
4583: asg.effective_start_date and asg.effective_end_date
4929: begin
4930: select object_version_number
4931: into l_ovn
4932: from pay_us_emp_fed_tax_rules_f puek,
4933: hr_pump_batch_line_user_keys uk
4934: where uk.user_key_value = p_emp_fed_tax_rule_user_key
4935: and p_effective_date between puek.effective_start_date and puek.effective_end_date
4936: and puek.emp_fed_tax_rule_id = uk.unique_key_id;
4937: --
4958: begin
4959: select object_version_number
4960: into l_ovn
4961: from pay_us_emp_state_tax_rules_f puek,
4962: hr_pump_batch_line_user_keys uk
4963: where uk.user_key_value = p_emp_state_tax_rule_user_key
4964: and p_effective_date between puek.effective_start_date and puek.effective_end_date
4965: and puek.emp_state_tax_rule_id = uk.unique_key_id;
4966: --
4984: begin
4985: select object_version_number
4986: into l_ovn
4987: from pay_us_emp_county_tax_rules_f puek,
4988: hr_pump_batch_line_user_keys uk
4989: where uk.user_key_value = p_emp_county_tax_rule_user_key
4990: and p_effective_date between puek.effective_start_date and puek.effective_end_date
4991: and puek.emp_county_tax_rule_id = uk.unique_key_id;
4992: --
5011: begin
5012: select object_version_number
5013: into l_ovn
5014: from pay_us_emp_city_tax_rules_f puek,
5015: hr_pump_batch_line_user_keys uk
5016: where uk.user_key_value = p_emp_city_tax_rule_user_key
5017: and p_effective_date between puek.effective_start_date and puek.effective_end_date
5018: and puek.emp_city_tax_rule_id = uk.unique_key_id;
5019: --
5771: begin
5772: select pei.object_version_number
5773: into l_ovn
5774: from per_people_extra_info pei,
5775: hr_pump_batch_line_user_keys key
5776: where key.user_key_value = p_person_extra_info_user_key
5777: and pei.person_extra_info_id = key.unique_key_id;
5778: return(l_ovn);
5779: exception
5811: begin
5812: select rules.object_version_number
5813: into l_ovn
5814: from PAY_CA_EMP_FED_TAX_INFO_F rules,
5815: hr_pump_batch_line_user_keys key
5816: where key.user_key_value = P_EMP_FED_TAX_INF_USER_KEY
5817: and rules.EMP_FED_TAX_INF_ID = key.unique_key_id
5818: and p_effective_date between
5819: rules.effective_start_date and rules.effective_end_date;
5854: begin
5855: select rules.object_version_number
5856: into l_ovn
5857: from PAY_CA_EMP_PROV_TAX_INFO_F rules,
5858: hr_pump_batch_line_user_keys key
5859: where key.user_key_value = P_EMP_PROV_TAX_INF_USER_KEY
5860: and rules.EMP_PROVINCE_TAX_INF_ID = key.unique_key_id
5861: and p_effective_date between
5862: rules.effective_start_date and rules.effective_end_date;