[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select hl.meaning
from hr_lookups hl
where hl.lookup_code = c_lookup_code
and hl.lookup_type = c_lookup_type;
SELECT os.NAME
FROM per_organization_structures_v os,
per_org_structure_versions osv
WHERE os.business_group_id = p_business_group_id
AND os.organization_structure_id = osv.organization_structure_id
AND osv.org_structure_version_id = cv_org_stru_ver_id
AND p_effective_date BETWEEN osv.date_from
AND NVL (osv.date_to, p_effective_date);
SELECT NAME
FROM hr_all_organization_units org
WHERE org.organization_id = cv_start_org_id
AND business_group_id = p_business_group_id;
SELECT ps.NAME
FROM per_position_structures_v ps, per_pos_structure_versions psv
WHERE ps.business_group_id = p_business_group_id
AND ps.position_structure_id = psv.position_structure_id
AND psv.pos_structure_version_id = cv_pos_stru_ver_id
AND p_effective_date BETWEEN psv.date_from
AND NVL (psv.date_to, p_effective_date);
SELECT NAME
FROM hr_all_positions_f_vl pos
WHERE position_id = cv_start_pos_id
AND business_group_id = cv_business_group_id
AND cv_effective_date between pos.effective_start_date
and pos.effective_end_date;
select copy_entity_result_id mirror_src_entity_result_id
from ben_copy_entity_results cpe
-- pqh_table_route trt
where cpe.information1= c_parent_pk_id
-- and cpe.result_type_cd = 'DISPLAY'
and cpe.result_type_cd = l_cv_result_type_cd
and cpe.copy_entity_txn_id = c_copy_entity_txn_id
-- and cpe.table_route_id = trt.table_route_id
--and trt.from_clause = 'OAB'
--and trt.where_clause = upper(c_parent_table_name) ;
select distinct prtn_elig_id
from BEN_PRTN_ELIG_F
where (c_pgm_id is not null and pgm_id = c_pgm_id ) or
(c_ptip_id is not null and ptip_id = c_ptip_id ) or
(c_plip_id is not null and plip_id = c_plip_id ) or
(c_pl_id is not null and pl_id = c_pl_id ) or
(c_oipl_id is not null and oipl_id = c_oipl_id ) ;
select epa.*
from BEN_PRTN_ELIG_F epa
where epa.prtn_elig_id = c_prtn_elig_id
-- and epa.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_PRTN_ELIG_F'
and cpe.table_alias = c_table_alias
and information1 = c_prtn_elig_id
-- and information4 = epa.business_group_id
and information2 = epa.effective_start_date
and information3 = epa.effective_end_date
);
select distinct prtn_elig_prfl_id
from BEN_PRTN_ELIG_PRFL_F
where PRTN_ELIG_ID = c_PRTN_ELIG_ID;
select cep.*
from BEN_PRTN_ELIG_PRFL_F cep
where cep.prtn_elig_prfl_id = c_prtn_elig_prfl_id
-- and cep.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_PRTN_ELIG_PRFL_F'
and cpe.table_alias = c_table_alias
and information1 = c_prtn_elig_prfl_id
-- and information4 = cep.business_group_id
and information2 = cep.effective_start_date
and information3 = cep.effective_end_date
);
select distinct eligy_prfl_id
from BEN_PRTN_ELIG_PRFL_F
where PRTN_ELIG_PRFL_ID = c_PRTN_ELIG_PRFL_ID;
select prtn_eligy_rl_id
from BEN_PRTN_ELIGY_RL_F
where PRTN_ELIG_ID = c_PRTN_ELIG_ID ;
select cer.*
from BEN_PRTN_ELIGY_RL_F cer
where cer.prtn_eligy_rl_id = c_prtn_eligy_rl_id
-- and cpe.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe1
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe1.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_PRTN_ELIGY_RL_F'
and cpe1.table_alias = c_table_alias
and information1 = c_prtn_eligy_rl_id
-- and information4 = cpe.business_group_id
and information2 = cer.effective_start_date
and information3 = cer.effective_end_date
);
l_Sql := 'select distinct prtn_elig_id from BEN_PRTN_ELIG_F where pgm_id = :pgm_id';
l_sql := 'select distinct prtn_elig_id from BEN_PRTN_ELIG_F where ptip_id = :ptip_id';
l_sql := 'select distinct prtn_elig_id from BEN_PRTN_ELIG_F where plip_id = :plip_id';
l_sql := 'select distinct prtn_elig_id from BEN_PRTN_ELIG_F where pl_id = :pl_id';
l_sql := 'select distinct prtn_elig_id from BEN_PRTN_ELIG_F where Oipl_Id = :Oipl_Id';
select copy_entity_result_id mirror_src_entity_result_id
from ben_copy_entity_results cpe
-- pqh_table_route trt
where cpe.information1= c_parent_pk_id
and cpe.result_type_cd = l_cv_result_type_cd
and cpe.copy_entity_txn_id = c_copy_entity_txn_id
-- and cpe.table_route_id = trt.table_route_id
and cpe.table_alias = c_parent_table_alias ;
select distinct apld_dpnt_cvg_elig_prfl_id
from BEN_APLD_DPNT_CVG_ELIG_PRFL_F
where (c_pgm_id is not null and pgm_id = c_pgm_id ) or
(c_ptip_id is not null and ptip_id = c_ptip_id) or
(c_pl_id is not null and pl_id = c_pl_id) ;
select ade.*
from BEN_APLD_DPNT_CVG_ELIG_PRFL_F ade
where ade.apld_dpnt_cvg_elig_prfl_id = c_apld_dpnt_cvg_elig_prfl_id
-- and ade.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_APLD_DPNT_CVG_ELIG_PRFL_F'
and cpe.table_alias = c_table_alias
and information1 = c_apld_dpnt_cvg_elig_prfl_id
-- and information4 = ade.business_group_id
and information2 = ade.effective_start_date
and information3 = ade.effective_end_date
);
select distinct dpnt_cvg_eligy_prfl_id
from BEN_APLD_DPNT_CVG_ELIG_PRFL_F
where APLD_DPNT_CVG_ELIG_PRFL_ID = c_APLD_DPNT_CVG_ELIG_PRFL_ID ;
select copy_entity_result_id mirror_src_entity_result_id
from ben_copy_entity_results cpe
-- pqh_table_route trt
where cpe.information1= c_parent_pk_id
-- and cpe.result_type_cd = 'DISPLAY'
and cpe.result_type_cd = l_cv_result_type_cd
and cpe.copy_entity_txn_id = c_copy_entity_txn_id
-- and cpe.table_route_id = trt.table_route_id
--and trt.from_clause = 'OAB'
--and trt.where_clause = upper(c_parent_table_name) ;
select elp.*
from BEN_ELIGY_PRFL_F elp
where elp.eligy_prfl_id = c_eligy_prfl_id
-- and elp.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIGY_PRFL_F'
and cpe.table_alias = c_table_alias
and information1 = c_eligy_prfl_id
-- and information4 = elp.business_group_id
and information2 = elp.effective_start_date
and information3 = elp.effective_end_date
);
select cntng_prtn_elig_prfl_id
from BEN_CNTNG_PRTN_ELIG_PRFL_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select cgp.*
from BEN_CNTNG_PRTN_ELIG_PRFL_F cgp
where cgp.cntng_prtn_elig_prfl_id = c_cntng_prtn_elig_prfl_id
-- and cgp.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_CNTNG_PRTN_ELIG_PRFL_F'
and cpe.table_alias = c_table_alias
and information1 = c_cntng_prtn_elig_prfl_id
-- and information4 = cgp.business_group_id
and information2 = cgp.effective_start_date
and information3 = cgp.effective_end_date
);
select eligy_prfl_rl_id
from BEN_ELIGY_PRFL_RL_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select erl.*
from BEN_ELIGY_PRFL_RL_F erl
where erl.eligy_prfl_rl_id = c_eligy_prfl_rl_id
-- and erl.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIGY_PRFL_RL_F'
and cpe.table_alias = c_table_alias
and information1 = c_eligy_prfl_rl_id
-- and information4 = erl.business_group_id
and information2 = erl.effective_start_date
and information3 = erl.effective_end_date
);
select distinct elig_age_prte_id
from BEN_ELIG_AGE_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select eap.*
from BEN_ELIG_AGE_PRTE_F eap
where eap.elig_age_prte_id = c_elig_age_prte_id
-- and eap.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_AGE_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_age_prte_id
-- and information4 = eap.business_group_id
and information2 = eap.effective_start_date
and information3 = eap.effective_end_date
);
select distinct cpe.information246 age_fctr_id
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and mirror_src_entity_result_id = c_mirror_src_entity_result_id
-- and trt.where_clause = 'BEN_ELIG_AGE_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_age_prte_id
-- and information4 = p_business_group_id
;
select elig_asnt_set_prte_id
from BEN_ELIG_ASNT_SET_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select ean.*
from BEN_ELIG_ASNT_SET_PRTE_F ean
where ean.elig_asnt_set_prte_id = c_elig_asnt_set_prte_id
-- and ean.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_ASNT_SET_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_asnt_set_prte_id
-- and information4 = ean.business_group_id
and information2 = ean.effective_start_date
and information3 = ean.effective_end_date
);
select assignment_set_name
from hr_assignment_sets
where business_group_id = p_business_group_id
and assignment_set_id = p_id;
select distinct elig_benfts_grp_prte_id
from BEN_ELIG_BENFTS_GRP_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select ebn.*
from BEN_ELIG_BENFTS_GRP_PRTE_F ebn
where ebn.elig_benfts_grp_prte_id = c_elig_benfts_grp_prte_id
-- and ebn.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_BENFTS_GRP_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_benfts_grp_prte_id
-- and information4 = ebn.business_group_id
and information2 = ebn.effective_start_date
and information3 = ebn.effective_end_date
);
select distinct cpe.information222 benfts_grp_id
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and mirror_src_entity_result_id = c_mirror_src_entity_result_id
-- and trt.where_clause = 'BEN_ELIG_BENFTS_GRP_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_benfts_grp_prte_id
-- and information4 = p_business_group_id
;
select elig_brgng_unit_prte_id
from BEN_ELIG_BRGNG_UNIT_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select ebu.*
from BEN_ELIG_BRGNG_UNIT_PRTE_F ebu
where ebu.elig_brgng_unit_prte_id = c_elig_brgng_unit_prte_id
-- and ebu.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_BRGNG_UNIT_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_brgng_unit_prte_id
-- and information4 = ebu.business_group_id
and information2 = ebu.effective_start_date
and information3 = ebu.effective_end_date
);
select elig_cbr_quald_bnf_id
from BEN_ELIG_CBR_QUALD_BNF_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select ecq.*
from BEN_ELIG_CBR_QUALD_BNF_F ecq
where ecq.elig_cbr_quald_bnf_id = c_elig_cbr_quald_bnf_id
-- and ecq.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_CBR_QUALD_BNF_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_cbr_quald_bnf_id
-- and information4 = ecq.business_group_id
and information2 = ecq.effective_start_date
and information3 = ecq.effective_end_date
);
select distinct elig_cmbn_age_los_prte_id
from BEN_ELIG_CMBN_AGE_LOS_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select ecp.*
from BEN_ELIG_CMBN_AGE_LOS_PRTE_F ecp
where ecp.elig_cmbn_age_los_prte_id = c_elig_cmbn_age_los_prte_id
-- and ecp.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_CMBN_AGE_LOS_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_cmbn_age_los_prte_id
-- and information4 = ecp.business_group_id
and information2 = ecp.effective_start_date
and information3 = ecp.effective_end_date
);
select distinct cpe.information223 cmbn_age_los_fctr_id
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and mirror_src_entity_result_id = c_mirror_src_entity_result_id
-- and trt.where_clause = 'BEN_ELIG_CMBN_AGE_LOS_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_cmbn_age_los_prte_id
-- and information4 = p_business_group_id
;
select distinct elig_comp_lvl_prte_id
from BEN_ELIG_COMP_LVL_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select ecl.*
from BEN_ELIG_COMP_LVL_PRTE_F ecl
where ecl.elig_comp_lvl_prte_id = c_elig_comp_lvl_prte_id
-- and ecl.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_COMP_LVL_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_comp_lvl_prte_id
-- and information4 = ecl.business_group_id
and information2 = ecl.effective_start_date
and information3 = ecl.effective_end_date
);
select distinct cpe.information254 comp_lvl_fctr_id
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and mirror_src_entity_result_id = c_mirror_src_entity_result_id
-- and trt.where_clause = 'BEN_ELIG_COMP_LVL_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_comp_lvl_prte_id
-- and information4 = p_business_group_id
;
select elig_dpnt_cvrd_othr_pgm_id
from BEN_ELIG_DPNT_CVRD_OTHR_PGM_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select edg.*
from BEN_ELIG_DPNT_CVRD_OTHR_PGM_F edg
where edg.elig_dpnt_cvrd_othr_pgm_id = c_elig_dpnt_cvrd_othr_pgm_id
-- and edg.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_DPNT_CVRD_OTHR_PGM_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_dpnt_cvrd_othr_pgm_id
-- and information4 = edg.business_group_id
and information2 = edg.effective_start_date
and information3 = edg.effective_end_date
);
select elig_dpnt_cvrd_othr_pl_id
from BEN_ELIG_DPNT_CVRD_OTHR_PL_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select edp.*
from BEN_ELIG_DPNT_CVRD_OTHR_PL_F edp
where edp.elig_dpnt_cvrd_othr_pl_id = c_elig_dpnt_cvrd_othr_pl_id
-- and edp.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_DPNT_CVRD_OTHR_PL_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_dpnt_cvrd_othr_pl_id
-- and information4 = edp.business_group_id
and information2 = edp.effective_start_date
and information3 = edp.effective_end_date
);
select elig_dpnt_cvrd_othr_ptip_id
from BEN_ELIG_DPNT_CVRD_OTHR_PTIP_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select edt.*
from BEN_ELIG_DPNT_CVRD_OTHR_PTIP_F edt
where edt.elig_dpnt_cvrd_othr_ptip_id = c_elig_dpnt_cvrd_othr_ptip_id
-- and edt.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_DPNT_CVRD_OTHR_PTIP_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_dpnt_cvrd_othr_ptip_id
-- and information4 = edt.business_group_id
and information2 = edt.effective_start_date
and information3 = edt.effective_end_date
);
select elig_dpnt_cvrd_plip_id
from BEN_ELIG_DPNT_CVRD_PLIP_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select edi.*
from BEN_ELIG_DPNT_CVRD_PLIP_F edi
where edi.elig_dpnt_cvrd_plip_id = c_elig_dpnt_cvrd_plip_id
-- and edi.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_DPNT_CVRD_PLIP_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_dpnt_cvrd_plip_id
-- and information4 = edi.business_group_id
and information2 = edi.effective_start_date
and information3 = edi.effective_end_date
);
select elig_dpnt_othr_ptip_id
from BEN_ELIG_DPNT_OTHR_PTIP_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select etd.*
from BEN_ELIG_DPNT_OTHR_PTIP_F etd
where etd.elig_dpnt_othr_ptip_id = c_elig_dpnt_othr_ptip_id
-- and etd.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_DPNT_OTHR_PTIP_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_dpnt_othr_ptip_id
-- and information4 = etd.business_group_id
and information2 = etd.effective_start_date
and information3 = etd.effective_end_date
);
select elig_dsbld_stat_prte_id
from BEN_ELIG_DSBLD_STAT_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select eds.*
from BEN_ELIG_DSBLD_STAT_PRTE_F eds
where eds.elig_dsbld_stat_prte_id = c_elig_dsbld_stat_prte_id
-- and eds.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_DSBLD_STAT_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_dsbld_stat_prte_id
-- and information4 = eds.business_group_id
and information2 = eds.effective_start_date
and information3 = eds.effective_end_date
);
select elig_ee_stat_prte_id
from BEN_ELIG_EE_STAT_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select ees.*
from BEN_ELIG_EE_STAT_PRTE_F ees
where ees.elig_ee_stat_prte_id = c_elig_ee_stat_prte_id
-- and ees.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_EE_STAT_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_ee_stat_prte_id
-- and information4 = ees.business_group_id
and information2 = ees.effective_start_date
and information3 = ees.effective_end_date
);
select nvl(atl.user_status, stl.user_status) dsp_meaning
from per_assignment_status_types s,
per_ass_status_type_amends a ,
per_business_groups bus ,
per_assignment_status_types_tl stl ,
per_ass_status_type_amends_tl atl
where a.assignment_status_type_id (+) = s.assignment_status_type_id
and a.business_group_id (+) = p_business_group_id
and nvl(s.business_group_id, p_business_group_id) = p_business_group_id
and nvl(s.legislation_code, bus.legislation_code) = bus.legislation_code
-- and bus.business_group_id = p_business_group_id
and bus.business_group_id = nvl(s.business_group_id, p_business_group_id)
and s.assignment_status_type_id = p_id
and nvl(a.active_flag, s.active_flag) = 'Y'
and atl.ass_status_type_amend_id (+) = a.ass_status_type_amend_id
and atl.language (+) = userenv('LANG')
and stl.assignment_status_type_id = s.assignment_status_type_id
and stl.language = userenv('LANG');
select elig_enrld_anthr_oipl_id
from BEN_ELIG_ENRLD_ANTHR_OIPL_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select eei.*
from BEN_ELIG_ENRLD_ANTHR_OIPL_F eei
where eei.elig_enrld_anthr_oipl_id = c_elig_enrld_anthr_oipl_id
-- and eei.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_ENRLD_ANTHR_OIPL_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_enrld_anthr_oipl_id
-- and information4 = eei.business_group_id
and information2 = eei.effective_start_date
and information3 = eei.effective_end_date
);
select elig_enrld_anthr_pgm_id
from BEN_ELIG_ENRLD_ANTHR_PGM_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select eeg.*
from BEN_ELIG_ENRLD_ANTHR_PGM_F eeg
where eeg.elig_enrld_anthr_pgm_id = c_elig_enrld_anthr_pgm_id
-- and eeg.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_ENRLD_ANTHR_PGM_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_enrld_anthr_pgm_id
-- and information4 = eeg.business_group_id
and information2 = eeg.effective_start_date
and information3 = eeg.effective_end_date
);
select elig_enrld_anthr_plip_id
from BEN_ELIG_ENRLD_ANTHR_PLIP_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select eai.*
from BEN_ELIG_ENRLD_ANTHR_PLIP_F eai
where eai.elig_enrld_anthr_plip_id = c_elig_enrld_anthr_plip_id
-- and eai.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_ENRLD_ANTHR_PLIP_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_enrld_anthr_plip_id
-- and information4 = eai.business_group_id
and information2 = eai.effective_start_date
and information3 = eai.effective_end_date
);
select elig_enrld_anthr_pl_id
from BEN_ELIG_ENRLD_ANTHR_PL_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select eep.*
from BEN_ELIG_ENRLD_ANTHR_PL_F eep
where eep.elig_enrld_anthr_pl_id = c_elig_enrld_anthr_pl_id
-- and eep.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_ENRLD_ANTHR_PL_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_enrld_anthr_pl_id
-- and information4 = eep.business_group_id
and information2 = eep.effective_start_date
and information3 = eep.effective_end_date
);
select elig_enrld_anthr_ptip_id
from BEN_ELIG_ENRLD_ANTHR_PTIP_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select eet.*
from BEN_ELIG_ENRLD_ANTHR_PTIP_F eet
where eet.elig_enrld_anthr_ptip_id = c_elig_enrld_anthr_ptip_id
-- and eet.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_ENRLD_ANTHR_PTIP_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_enrld_anthr_ptip_id
-- and information4 = eet.business_group_id
and information2 = eet.effective_start_date
and information3 = eet.effective_end_date
);
select elig_fl_tm_pt_tm_prte_id
from BEN_ELIG_FL_TM_PT_TM_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select efp.*
from BEN_ELIG_FL_TM_PT_TM_PRTE_F efp
where efp.elig_fl_tm_pt_tm_prte_id = c_elig_fl_tm_pt_tm_prte_id
-- and efp.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_FL_TM_PT_TM_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_fl_tm_pt_tm_prte_id
-- and information4 = efp.business_group_id
and information2 = efp.effective_start_date
and information3 = efp.effective_end_date
);
select elig_grd_prte_id
from BEN_ELIG_GRD_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select egr.*
from BEN_ELIG_GRD_PRTE_F egr
where egr.elig_grd_prte_id = c_elig_grd_prte_id
-- and egr.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_GRD_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_grd_prte_id
-- and information4 = egr.business_group_id
and information2 = egr.effective_start_date
and information3 = egr.effective_end_date
);
select gra.name dsp_name
from per_grades_vl gra
where business_group_id = p_business_group_id
and gra.grade_id = p_id
and p_date between date_from and nvl(date_to, p_date) ;
select date_from
from per_grades
where grade_id = c_grade_id;
select elig_hrly_slrd_prte_id
from BEN_ELIG_HRLY_SLRD_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select ehs.*
from BEN_ELIG_HRLY_SLRD_PRTE_F ehs
where ehs.elig_hrly_slrd_prte_id = c_elig_hrly_slrd_prte_id
-- and ehs.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_HRLY_SLRD_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_hrly_slrd_prte_id
-- and information4 = ehs.business_group_id
and information2 = ehs.effective_start_date
and information3 = ehs.effective_end_date
);
select distinct elig_hrs_wkd_prte_id
from BEN_ELIG_HRS_WKD_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select ehw.*
from BEN_ELIG_HRS_WKD_PRTE_F ehw
where ehw.elig_hrs_wkd_prte_id = c_elig_hrs_wkd_prte_id
-- and ehw.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_HRS_WKD_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_hrs_wkd_prte_id
-- and information4 = ehw.business_group_id
and information2 = ehw.effective_start_date
and information3 = ehw.effective_end_date
);
select distinct cpe.information224 hrs_wkd_in_perd_fctr_id
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and mirror_src_entity_result_id = c_mirror_src_entity_result_id
-- and trt.where_clause = 'BEN_ELIG_HRS_WKD_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_hrs_wkd_prte_id
-- and information4 = p_business_group_id
;
select elig_job_prte_id
from BEN_ELIG_JOB_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select ejp.*
from BEN_ELIG_JOB_PRTE_F ejp
where ejp.elig_job_prte_id = c_elig_job_prte_id
-- and ejp.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_JOB_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_job_prte_id
-- and information4 = ejp.business_group_id
and information2 = ejp.effective_start_date
and information3 = ejp.effective_end_date
);
select job.name dsp_name
from per_jobs_vl job
where job.business_group_id = p_business_group_id
and job.job_id = p_id
and p_date between date_from and nvl(date_to,p_date);
select date_from
from per_jobs
where job_id = c_job_id;
select elig_lbr_mmbr_prte_id
from BEN_ELIG_LBR_MMBR_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select elu.*
from BEN_ELIG_LBR_MMBR_PRTE_F elu
where elu.elig_lbr_mmbr_prte_id = c_elig_lbr_mmbr_prte_id
-- and elu.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_LBR_MMBR_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_lbr_mmbr_prte_id
-- and information4 = elu.business_group_id
and information2 = elu.effective_start_date
and information3 = elu.effective_end_date
);
select elig_lgl_enty_prte_id
from BEN_ELIG_LGL_ENTY_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select eln.*
from BEN_ELIG_LGL_ENTY_PRTE_F eln
where eln.elig_lgl_enty_prte_id = c_elig_lgl_enty_prte_id
-- and eln.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_LGL_ENTY_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_lgl_enty_prte_id
-- and information4 = eln.business_group_id
and information2 = eln.effective_start_date
and information3 = eln.effective_end_date
);
select hou.name dsp_name
from hr_organization_units_v hou ,
hr_organization_information hoi
where business_group_id = p_business_group_id
and hou.organization_id = p_id
and p_date between date_from and nvl(date_to, p_date)
and hou.organization_id = hoi.organization_id
and hoi.org_information2 = 'Y'
and hoi.org_information1 = 'HR_LEGAL'
and hoi.org_information_context || '' ='CLASS' ;
select date_from
from hr_all_organization_units
where organization_id = c_organization_id;
select elig_loa_rsn_prte_id
from BEN_ELIG_LOA_RSN_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select elr.*
from BEN_ELIG_LOA_RSN_PRTE_F elr
where elr.elig_loa_rsn_prte_id = c_elig_loa_rsn_prte_id
-- and elr.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_LOA_RSN_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_loa_rsn_prte_id
-- and information4 = elr.business_group_id
and information2 = elr.effective_start_date
and information3 = elr.effective_end_date
);
select hl.meaning name
from per_abs_attendance_reasons abr,
hr_leg_lookups hl
where abr.business_group_id = p_business_group_id
and abr.absence_attendance_type_id = p_id
and abr.abs_attendance_reason_id = p_id1
and abr.name = hl.lookup_code
and hl.lookup_type = 'ABSENCE_REASON'
and hl.enabled_flag = 'Y'
and p_date between
nvl(hl.start_date_active, p_date)
and nvl(hl.end_date_active, p_date);
select abt.name
from per_absence_attendance_types abt
where abt.business_group_id = p_business_group_id
and abt.absence_attendance_type_id = p_id
and p_date between abt.date_effective
and nvl(abt.date_end, p_date);
select date_effective
from per_absence_attendance_types
where absence_attendance_type_id = c_absence_attendance_type_id;
select distinct elig_los_prte_id
from BEN_ELIG_LOS_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select els.*
from BEN_ELIG_LOS_PRTE_F els
where els.elig_los_prte_id = c_elig_los_prte_id
-- and els.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_LOS_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_los_prte_id
-- and information4 = els.business_group_id
and information2 = els.effective_start_date
and information3 = els.effective_end_date
);
select distinct cpe.information243 los_fctr_id
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and mirror_src_entity_result_id = c_mirror_src_entity_result_id
-- and trt.where_clause = 'BEN_ELIG_LOS_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_los_prte_id
-- and information4 = p_business_group_id
;
select elig_lvg_rsn_prte_id
from BEN_ELIG_LVG_RSN_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select elv.*
from BEN_ELIG_LVG_RSN_PRTE_F elv
where elv.elig_lvg_rsn_prte_id = c_elig_lvg_rsn_prte_id
-- and elv.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_LVG_RSN_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_lvg_rsn_prte_id
-- and information4 = elv.business_group_id
and information2 = elv.effective_start_date
and information3 = elv.effective_end_date
);
select elig_no_othr_cvg_prte_id
from BEN_ELIG_NO_OTHR_CVG_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select eno.*
from BEN_ELIG_NO_OTHR_CVG_PRTE_F eno
where eno.elig_no_othr_cvg_prte_id = c_elig_no_othr_cvg_prte_id
-- and eno.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_NO_OTHR_CVG_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_no_othr_cvg_prte_id
-- and information4 = eno.business_group_id
and information2 = eno.effective_start_date
and information3 = eno.effective_end_date
);
select elig_optd_mdcr_prte_id
from BEN_ELIG_OPTD_MDCR_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select eom.*
from BEN_ELIG_OPTD_MDCR_PRTE_F eom
where eom.elig_optd_mdcr_prte_id = c_elig_optd_mdcr_prte_id
-- and eom.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_OPTD_MDCR_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_optd_mdcr_prte_id
-- and information4 = eom.business_group_id
and information2 = eom.effective_start_date
and information3 = eom.effective_end_date
);
select elig_org_unit_prte_id
from BEN_ELIG_ORG_UNIT_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select eou.*
from BEN_ELIG_ORG_UNIT_PRTE_F eou
where eou.elig_org_unit_prte_id = c_elig_org_unit_prte_id
-- and eou.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_ORG_UNIT_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_org_unit_prte_id
-- and information4 = eou.business_group_id
and information2 = eou.effective_start_date
and information3 = eou.effective_end_date
);
select name
from hr_all_organization_units_vl
where business_group_id = business_group_id
and organization_id = p_id
and internal_external_flag = 'INT'
and p_date between nvl(date_from, p_date)
and nvl(date_to, p_date)
order by name;
select elig_othr_ptip_prte_id
from BEN_ELIG_OTHR_PTIP_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select eoy.*
from BEN_ELIG_OTHR_PTIP_PRTE_F eoy
where eoy.elig_othr_ptip_prte_id = c_elig_othr_ptip_prte_id
-- and eoy.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_OTHR_PTIP_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_othr_ptip_prte_id
-- and information4 = eoy.business_group_id
and information2 = eoy.effective_start_date
and information3 = eoy.effective_end_date
);
select distinct elig_pct_fl_tm_prte_id
from BEN_ELIG_PCT_FL_TM_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select epf.*
from BEN_ELIG_PCT_FL_TM_PRTE_F epf
where epf.elig_pct_fl_tm_prte_id = c_elig_pct_fl_tm_prte_id
-- and epf.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_PCT_FL_TM_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_pct_fl_tm_prte_id
-- and information4 = epf.business_group_id
and information2 = epf.effective_start_date
and information3 = epf.effective_end_date
);
select distinct cpe.information233 pct_fl_tm_fctr_id
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and mirror_src_entity_result_id = c_mirror_src_entity_result_id
-- and trt.where_clause = 'BEN_ELIG_PCT_FL_TM_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_pct_fl_tm_prte_id
-- and information4 = p_business_group_id
;
select elig_per_typ_prte_id
from BEN_ELIG_PER_TYP_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select ept.*
from BEN_ELIG_PER_TYP_PRTE_F ept
where ept.elig_per_typ_prte_id = c_elig_per_typ_prte_id
-- and ept.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_PER_TYP_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_per_typ_prte_id
-- and information4 = ept.business_group_id
and information2 = ept.effective_start_date
and information3 = ept.effective_end_date
);
select ptl.user_person_type
from per_person_types ppt,
hr_leg_lookups hrlkup,
per_person_types_tl ptl
where active_flag = 'Y'
-- and business_group_id = p_business_group_id
and hrlkup.lookup_type = 'PERSON_TYPE'
and hrlkup.lookup_code = ppt.system_person_type
and ppt.active_flag = 'Y'
and ppt.person_type_id = p_person_typ_id
and ppt.person_type_id = ptl.person_type_id
and ptl.language = userenv('LANG');
select elig_ppl_grp_prte_id
from BEN_ELIG_PPL_GRP_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select epg.*
from BEN_ELIG_PPL_GRP_PRTE_F epg
where epg.elig_ppl_grp_prte_id = c_elig_ppl_grp_prte_id
-- and epg.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_PPL_GRP_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_ppl_grp_prte_id
-- and information4 = epg.business_group_id
and information2 = epg.effective_start_date
and information3 = epg.effective_end_date
);
select elig_prtt_anthr_pl_prte_id
from BEN_ELIG_PRTT_ANTHR_PL_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select epp.*
from BEN_ELIG_PRTT_ANTHR_PL_PRTE_F epp
where epp.elig_prtt_anthr_pl_prte_id = c_elig_prtt_anthr_pl_prte_id
-- and epp.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_PRTT_ANTHR_PL_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_prtt_anthr_pl_prte_id
-- and information4 = epp.business_group_id
and information2 = epp.effective_start_date
and information3 = epp.effective_end_date
);
select distinct elig_pstl_cd_r_rng_prte_id
from BEN_ELIG_PSTL_CD_R_RNG_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select epz.*
from BEN_ELIG_PSTL_CD_R_RNG_PRTE_F epz
where epz.elig_pstl_cd_r_rng_prte_id = c_elig_pstl_cd_r_rng_prte_id
-- and epz.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_PSTL_CD_R_RNG_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_pstl_cd_r_rng_prte_id
-- and information4 = epz.business_group_id
and information2 = epz.effective_start_date
and information3 = epz.effective_end_date
);
select distinct cpe.information245 pstl_zip_rng_id
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and mirror_src_entity_result_id = c_mirror_src_entity_result_id
-- and trt.where_clause = 'BEN_ELIG_PSTL_CD_R_RNG_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_pstl_cd_r_rng_prte_id
-- and information4 = p_business_group_id
;
select elig_pyrl_prte_id
from BEN_ELIG_PYRL_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select epy.*
from BEN_ELIG_PYRL_PRTE_F epy
where epy.elig_pyrl_prte_id = c_elig_pyrl_prte_id
-- and epy.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_PYRL_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_pyrl_prte_id
-- and information4 = epy.business_group_id
and information2 = epy.effective_start_date
and information3 = epy.effective_end_date
);
select prl.payroll_name dsp_payroll_name
from pay_all_payrolls_f prl
where prl.business_group_id = p_business_group_id
and prl.payroll_id = p_id
and p_date between prl.effective_start_date and prl.effective_end_date ;
select min(effective_start_date) effective_start_date
from pay_all_payrolls_f
where payroll_id = c_payroll_id;
select elig_py_bss_prte_id
from BEN_ELIG_PY_BSS_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select epb.*
from BEN_ELIG_PY_BSS_PRTE_F epb
where epb.elig_py_bss_prte_id = c_elig_py_bss_prte_id
-- and epb.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_PY_BSS_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_py_bss_prte_id
-- and information4 = epb.business_group_id
and information2 = epb.effective_start_date
and information3 = epb.effective_end_date
);
select name from per_pay_bases
where business_group_id = p_business_group_id
and pay_basis_id = p_id;
select elig_schedd_hrs_prte_id
from BEN_ELIG_SCHEDD_HRS_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select esh.*
from BEN_ELIG_SCHEDD_HRS_PRTE_F esh
where esh.elig_schedd_hrs_prte_id = c_elig_schedd_hrs_prte_id
-- and esh.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_SCHEDD_HRS_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_schedd_hrs_prte_id
-- and information4 = esh.business_group_id
and information2 = esh.effective_start_date
and information3 = esh.effective_end_date
);
select distinct elig_svc_area_prte_id
from BEN_ELIG_SVC_AREA_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select esa.*
from BEN_ELIG_SVC_AREA_PRTE_F esa
where esa.elig_svc_area_prte_id = c_elig_svc_area_prte_id
-- and esa.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_SVC_AREA_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_svc_area_prte_id
-- and information4 = esa.business_group_id
and information2 = esa.effective_start_date
and information3 = esa.effective_end_date
);
select distinct cpe.information241 svc_area_id
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and mirror_src_entity_result_id = c_mirror_src_entity_result_id
-- and trt.where_clause = 'BEN_ELIG_SVC_AREA_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_svc_area_prte_id
-- and information4 = p_business_group_id
;
select elig_wk_loc_prte_id
from BEN_ELIG_WK_LOC_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select ewl.*
from BEN_ELIG_WK_LOC_PRTE_F ewl
where ewl.elig_wk_loc_prte_id = c_elig_wk_loc_prte_id
-- and ewl.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_WK_LOC_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_wk_loc_prte_id
-- and information4 = ewl.business_group_id
and information2 = ewl.effective_start_date
and information3 = ewl.effective_end_date
);
select loc.location_code dsp_location_code
from hr_locations loc
where loc.location_id = p_id
and p_date <= nvl( loc.inactive_date, p_date);
select inactive_date
from hr_locations
where location_id = c_location_id;
select elig_sp_clng_prg_prte_id
from BEN_ELIG_SP_CLNG_PRG_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select esp.*
from BEN_ELIG_SP_CLNG_PRG_PRTE_F esp
where esp.elig_sp_clng_prg_prte_id = c_elig_sp_clng_prg_prte_id
-- and esp.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_SP_CLNG_PRG_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_sp_clng_prg_prte_id
-- and information4 = esp.business_group_id
and information2 = esp.effective_start_date
and information3 = esp.effective_end_date
);
select a.name grade
, d.step_id
from per_grades_vl a,
per_parent_spines b,
per_spinal_points c,
per_spinal_point_steps_f d,
per_grade_spines e
where d.spinal_point_id = c.spinal_point_id
and d.grade_spine_id = e.grade_spine_id
and e.grade_id = a.grade_id
and e.parent_spine_id = b.parent_spine_id
and p_date between
nvl(d.effective_start_date,p_date)
and nvl(d.effective_end_date,p_date);
select min(effective_start_date) effective_start_date
from per_spinal_point_steps_f
where step_id = c_step_id;
select elig_pstn_prte_id
from BEN_ELIG_PSTN_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select eps.*
from BEN_ELIG_PSTN_PRTE_F eps
where eps.elig_pstn_prte_id = c_elig_pstn_prte_id
-- and eps.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_PSTN_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_pstn_prte_id
-- and information4 = eps.business_group_id
and information2 = eps.effective_start_date
and information3 = eps.effective_end_date
);
select name
from per_positions
where business_group_id = p_business_group_id
and position_id = p_id;
select date_effective
from per_positions
where position_id = c_position_id;
select elig_prbtn_perd_prte_id
from BEN_ELIG_PRBTN_PERD_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select epn.*
from BEN_ELIG_PRBTN_PERD_PRTE_F epn
where epn.elig_prbtn_perd_prte_id = c_elig_prbtn_perd_prte_id
-- and epn.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_PRBTN_PERD_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_prbtn_perd_prte_id
-- and information4 = epn.business_group_id
and information2 = epn.effective_start_date
and information3 = epn.effective_end_date
);
select elig_mrtl_sts_prte_id
from BEN_ELIG_MRTL_STS_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select emp.*
from BEN_ELIG_MRTL_STS_PRTE_F emp
where emp.elig_mrtl_sts_prte_id = c_elig_mrtl_sts_prte_id
-- and emp.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_MRTL_STS_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_mrtl_sts_prte_id
-- and information4 = emp.business_group_id
and information2 = emp.effective_start_date
and information3 = emp.effective_end_date
);
select elig_gndr_prte_id
from BEN_ELIG_GNDR_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select egn.*
from BEN_ELIG_GNDR_PRTE_F egn
where egn.elig_gndr_prte_id = c_elig_gndr_prte_id
-- and egn.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_GNDR_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_gndr_prte_id
-- and information4 = egn.business_group_id
and information2 = egn.effective_start_date
and information3 = egn.effective_end_date
);
select elig_dsblty_rsn_prte_id
from BEN_ELIG_DSBLTY_RSN_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select edr.*
from BEN_ELIG_DSBLTY_RSN_PRTE_F edr
where edr.elig_dsblty_rsn_prte_id = c_elig_dsblty_rsn_prte_id
-- and edr.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_DSBLTY_RSN_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_dsblty_rsn_prte_id
-- and information4 = edr.business_group_id
and information2 = edr.effective_start_date
and information3 = edr.effective_end_date
);
select elig_dsblty_dgr_prte_id
from BEN_ELIG_DSBLTY_DGR_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select edd.*
from BEN_ELIG_DSBLTY_DGR_PRTE_F edd
where edd.elig_dsblty_dgr_prte_id = c_elig_dsblty_dgr_prte_id
-- and edd.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_DSBLTY_DGR_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_dsblty_dgr_prte_id
-- and information4 = edd.business_group_id
and information2 = edd.effective_start_date
and information3 = edd.effective_end_date
);
select elig_qual_titl_prte_id
from BEN_ELIG_QUAL_TITL_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select eqt.*
from BEN_ELIG_QUAL_TITL_PRTE_F eqt
where eqt.elig_qual_titl_prte_id = c_elig_qual_titl_prte_id
-- and eqt.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_QUAL_TITL_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_qual_titl_prte_id
-- and information4 = eqt.business_group_id
and information2 = eqt.effective_start_date
and information3 = eqt.effective_end_date
);
select name
from per_qualification_types_vl
where qualification_type_id = p_id;
select elig_suppl_role_prte_id
from BEN_ELIG_SUPPL_ROLE_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select est.*
from BEN_ELIG_SUPPL_ROLE_PRTE_F est
where est.elig_suppl_role_prte_id = c_elig_suppl_role_prte_id
-- and est.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_SUPPL_ROLE_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_suppl_role_prte_id
-- and information4 = est.business_group_id
and information2 = est.effective_start_date
and information3 = est.effective_end_date
);
select displayed_name
from per_job_groups
where business_group_id = p_business_group_id
and job_group_id = p_id;
select name
from per_jobs_vl
where business_group_id = p_business_group_id
and job_id = p_id
and job_group_id = p_id1;
select elig_dsblty_ctg_prte_id
from BEN_ELIG_DSBLTY_CTG_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select ect.*
from BEN_ELIG_DSBLTY_CTG_PRTE_F ect
where ect.elig_dsblty_ctg_prte_id = c_elig_dsblty_ctg_prte_id
-- and ect.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_DSBLTY_CTG_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_dsblty_ctg_prte_id
-- and information4 = ect.business_group_id
and information2 = ect.effective_start_date
and information3 = ect.effective_end_date
);
select elig_comptncy_prte_id
from BEN_ELIG_COMPTNCY_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select ecy.*
from BEN_ELIG_COMPTNCY_PRTE_F ecy
where ecy.elig_comptncy_prte_id = c_elig_comptncy_prte_id
-- and ecy.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_COMPTNCY_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_comptncy_prte_id
-- and information4 = ecy.business_group_id
and information2 = ecy.effective_start_date
and information3 = ecy.effective_end_date
);
select name
from per_competences_vl
where competence_id = p_competence_id
and p_date
between Date_from and nvl(Date_to, p_date);
select rtl.name name
from per_rating_levels_vl rtl
where nvl(rtl.business_group_id, p_business_group_id) = p_business_group_id
and rtl.rating_level_id = p_rating_level_id;
select date_from
from per_competences
where competence_id = c_competence_id;
select elig_perf_rtng_prte_id
from BEN_ELIG_PERF_RTNG_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select erg.*
from BEN_ELIG_PERF_RTNG_PRTE_F erg
where erg.elig_perf_rtng_prte_id = c_elig_perf_rtng_prte_id
-- and erg.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_PERF_RTNG_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_perf_rtng_prte_id
-- and information4 = erg.business_group_id
and information2 = erg.effective_start_date
and information3 = erg.effective_end_date
);
select elig_qua_in_gr_prte_id
from BEN_ELIG_QUA_IN_GR_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select eqg.*
from BEN_ELIG_QUA_IN_GR_PRTE_F eqg
where eqg.elig_qua_in_gr_prte_id = c_elig_qua_in_gr_prte_id
-- and eqg.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_QUA_IN_GR_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_qua_in_gr_prte_id
-- and information4 = eqg.business_group_id
and information2 = eqg.effective_start_date
and information3 = eqg.effective_end_date
);
select elig_tbco_use_prte_id
from BEN_ELIG_TBCO_USE_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select etu.*
from BEN_ELIG_TBCO_USE_PRTE_F etu
where etu.elig_tbco_use_prte_id = c_elig_tbco_use_prte_id
-- and etu.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_TBCO_USE_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_tbco_use_prte_id
-- and information4 = etu.business_group_id
and information2 = etu.effective_start_date
and information3 = etu.effective_end_date
);
select elig_ttl_cvg_vol_prte_id
from BEN_ELIG_TTL_CVG_VOL_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select etc.*
from BEN_ELIG_TTL_CVG_VOL_PRTE_F etc
where etc.elig_ttl_cvg_vol_prte_id = c_elig_ttl_cvg_vol_prte_id
-- and etc.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_TTL_CVG_VOL_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_ttl_cvg_vol_prte_id
-- and information4 = etc.business_group_id
and information2 = etc.effective_start_date
and information3 = etc.effective_end_date
);
select elig_ttl_prtt_prte_id
from BEN_ELIG_TTL_PRTT_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select etp.*
from BEN_ELIG_TTL_PRTT_PRTE_F etp
where etp.elig_ttl_prtt_prte_id = c_elig_ttl_prtt_prte_id
-- and etp.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_TTL_PRTT_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_ttl_prtt_prte_id
-- and information4 = etp.business_group_id
and information2 = etp.effective_start_date
and information3 = etp.effective_end_date
);
select elig_dsbld_prte_id
from BEN_ELIG_DSBLD_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select edb.*
from BEN_ELIG_DSBLD_PRTE_F edb
where edb.elig_dsbld_prte_id = c_elig_dsbld_prte_id
-- and edb.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_DSBLD_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_dsbld_prte_id
-- and information4 = edb.business_group_id
and information2 = edb.effective_start_date
and information3 = edb.effective_end_date
);
select elig_hlth_cvg_prte_id
from BEN_ELIG_HLTH_CVG_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select ehc.*
from BEN_ELIG_HLTH_CVG_PRTE_F ehc
where ehc.elig_hlth_cvg_prte_id = c_elig_hlth_cvg_prte_id
-- and ehc.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_HLTH_CVG_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_hlth_cvg_prte_id
-- and information4 = ehc.business_group_id
and information2 = ehc.effective_start_date
and information3 = ehc.effective_end_date
);
select elig_anthr_pl_prte_id
from BEN_ELIG_ANTHR_PL_PRTE_F
where ELIGY_PRFL_ID = c_ELIGY_PRFL_ID ;
select eop.*
from BEN_ELIG_ANTHR_PL_PRTE_F eop
where eop.elig_anthr_pl_prte_id = c_elig_anthr_pl_prte_id
-- and eop.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_ANTHR_PL_PRTE_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_anthr_pl_prte_id
-- and information4 = eop.business_group_id
and information2 = eop.effective_start_date
and information3 = eop.effective_end_date
);
SELECT eligy_crit_values_id
FROM ben_eligy_crit_values_f
WHERE eligy_prfl_id = cv_eligy_prfl_id;
SELECT ecv.*
FROM ben_eligy_crit_values_f ecv
WHERE ecv.eligy_crit_values_id = cv_eligy_crit_values_id
AND NOT EXISTS (
SELECT NULL
FROM ben_copy_entity_results cpe
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND mirror_src_entity_result_id =
cv_mirror_src_entity_result_id
AND cpe.table_alias = cv_table_alias
AND information1 = cv_eligy_crit_values_id
AND information2 = ecv.effective_start_date
AND information3 = ecv.effective_end_date);
SELECT crit_col1_val_type_cd
FROM ben_eligy_criteria egl
WHERE eligy_criteria_id = cv_eligy_criteria_id;
SELECT egl.*
FROM ben_eligy_criteria egl
WHERE egl.eligy_criteria_id = cv_eligy_criteria_id
AND NOT EXISTS (
SELECT NULL
FROM ben_copy_entity_results cpe
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND mirror_src_entity_result_id =
cv_mirror_src_entity_result_id
AND cpe.table_alias = cv_table_alias
AND information1 = cv_eligy_criteria_id);
SELECT flex_value_set_name
FROM fnd_flex_value_sets
WHERE flex_value_set_id = cv_flex_value_set_id;
select null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and cpe.table_alias = c_table_alias
and information1 = c_pk_id;
select copy_entity_result_id mirror_src_entity_result_id
from ben_copy_entity_results cpe
-- pqh_table_route trt
where cpe.information1= c_parent_pk_id
and cpe.result_type_cd = l_cv_result_type_cd
and cpe.copy_entity_txn_id = c_copy_entity_txn_id
-- and cpe.table_route_id = trt.table_route_id
and cpe.table_alias = c_parent_table_alias ;
select dce.*
from BEN_DPNT_CVG_ELIGY_PRFL_F dce
where dce.dpnt_cvg_eligy_prfl_id = c_dpnt_cvg_eligy_prfl_id
-- and dce.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_DPNT_CVG_ELIGY_PRFL_F'
and cpe.table_alias = c_table_alias
and information1 = c_dpnt_cvg_eligy_prfl_id
-- and information4 = dce.business_group_id
and information2 = dce.effective_start_date
and information3 = dce.effective_end_date
);
select dpnt_cvg_rqd_rlshp_id
from BEN_DPNT_CVG_RQD_RLSHP_F
where DPNT_CVG_ELIGY_PRFL_ID = c_DPNT_CVG_ELIGY_PRFL_ID ;
select dcr.*
from BEN_DPNT_CVG_RQD_RLSHP_F dcr
where dcr.dpnt_cvg_rqd_rlshp_id = c_dpnt_cvg_rqd_rlshp_id
-- and dcr.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_DPNT_CVG_RQD_RLSHP_F'
and cpe.table_alias = c_table_alias
and information1 = c_dpnt_cvg_rqd_rlshp_id
-- and information4 = dcr.business_group_id
and information2 = dcr.effective_start_date
and information3 = dcr.effective_end_date
);
select dpnt_cvrd_anthr_pl_cvg_id
from BEN_DPNT_CVRD_ANTHR_PL_CVG_F
where DPNT_CVG_ELIGY_PRFL_ID = c_DPNT_CVG_ELIGY_PRFL_ID ;
select dpc.*
from BEN_DPNT_CVRD_ANTHR_PL_CVG_F dpc
where dpc.dpnt_cvrd_anthr_pl_cvg_id = c_dpnt_cvrd_anthr_pl_cvg_id
-- and dpc.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_DPNT_CVRD_ANTHR_PL_CVG_F'
and cpe.table_alias = c_table_alias
and information1 = c_dpnt_cvrd_anthr_pl_cvg_id
-- and information4 = dpc.business_group_id
and information2 = dpc.effective_start_date
and information3 = dpc.effective_end_date
);
select dsgntr_enrld_cvg_id
from BEN_DSGNTR_ENRLD_CVG_F
where DPNT_CVG_ELIGY_PRFL_ID = c_DPNT_CVG_ELIGY_PRFL_ID ;
select dec.*
from BEN_DSGNTR_ENRLD_CVG_F dec
where dec.dsgntr_enrld_cvg_id = c_dsgntr_enrld_cvg_id
-- and dec.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_DSGNTR_ENRLD_CVG_F'
and cpe.table_alias = c_table_alias
and information1 = c_dsgntr_enrld_cvg_id
-- and information4 = dec.business_group_id
and information2 = dec.effective_start_date
and information3 = dec.effective_end_date
);
select distinct elig_age_cvg_id
from BEN_ELIG_AGE_CVG_F
where DPNT_CVG_ELIGY_PRFL_ID = c_DPNT_CVG_ELIGY_PRFL_ID ;
select eac.*
from BEN_ELIG_AGE_CVG_F eac
where eac.elig_age_cvg_id = c_elig_age_cvg_id
-- and eac.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_AGE_CVG_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_age_cvg_id
-- and information4 = eac.business_group_id
and information2 = eac.effective_start_date
and information3 = eac.effective_end_date
);
select distinct cpe.information246 age_fctr_id
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and mirror_src_entity_result_id = c_mirror_src_entity_result_id
-- and trt.where_clause = 'BEN_ELIG_AGE_CVG_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_age_cvg_id
-- and information4 = p_business_group_id
;
select elig_dsbld_stat_cvg_id
from BEN_ELIG_DSBLD_STAT_CVG_F
where DPNT_CVG_ELIGY_PRFL_ID = c_DPNT_CVG_ELIGY_PRFL_ID ;
select edc.*
from BEN_ELIG_DSBLD_STAT_CVG_F edc
where edc.elig_dsbld_stat_cvg_id = c_elig_dsbld_stat_cvg_id
-- and edc.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_DSBLD_STAT_CVG_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_dsbld_stat_cvg_id
-- and information4 = edc.business_group_id
and information2 = edc.effective_start_date
and information3 = edc.effective_end_date
);
select elig_mltry_stat_cvg_id
from BEN_ELIG_MLTRY_STAT_CVG_F
where DPNT_CVG_ELIGY_PRFL_ID = c_DPNT_CVG_ELIGY_PRFL_ID ;
select emc.*
from BEN_ELIG_MLTRY_STAT_CVG_F emc
where emc.elig_mltry_stat_cvg_id = c_elig_mltry_stat_cvg_id
-- and emc.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_MLTRY_STAT_CVG_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_mltry_stat_cvg_id
-- and information4 = emc.business_group_id
and information2 = emc.effective_start_date
and information3 = emc.effective_end_date
);
select elig_mrtl_stat_cvg_id
from BEN_ELIG_MRTL_STAT_CVG_F
where DPNT_CVG_ELIGY_PRFL_ID = c_DPNT_CVG_ELIGY_PRFL_ID ;
select ems.*
from BEN_ELIG_MRTL_STAT_CVG_F ems
where ems.elig_mrtl_stat_cvg_id = c_elig_mrtl_stat_cvg_id
-- and ems.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_MRTL_STAT_CVG_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_mrtl_stat_cvg_id
-- and information4 = ems.business_group_id
and information2 = ems.effective_start_date
and information3 = ems.effective_end_date
);
select distinct elig_pstl_cd_r_rng_cvg_id
from BEN_ELIG_PSTL_CD_R_RNG_CVG_F
where DPNT_CVG_ELIGY_PRFL_ID = c_DPNT_CVG_ELIGY_PRFL_ID ;
select epl.*
from BEN_ELIG_PSTL_CD_R_RNG_CVG_F epl
where epl.elig_pstl_cd_r_rng_cvg_id = c_elig_pstl_cd_r_rng_cvg_id
-- and epl.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_PSTL_CD_R_RNG_CVG_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_pstl_cd_r_rng_cvg_id
-- and information4 = epl.business_group_id
and information2 = epl.effective_start_date
and information3 = epl.effective_end_date
);
select distinct cpe.information245 pstl_zip_rng_id
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and mirror_src_entity_result_id = c_mirror_src_entity_result_id
-- and trt.where_clause = 'BEN_ELIG_PSTL_CD_R_RNG_CVG_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_pstl_cd_r_rng_cvg_id
-- and information4 = p_business_group_id
;
select elig_stdnt_stat_cvg_id
from BEN_ELIG_STDNT_STAT_CVG_F
where DPNT_CVG_ELIGY_PRFL_ID = c_DPNT_CVG_ELIGY_PRFL_ID ;
select esc.*
from BEN_ELIG_STDNT_STAT_CVG_F esc
where esc.elig_stdnt_stat_cvg_id = c_elig_stdnt_stat_cvg_id
-- and esc.business_group_id = p_business_group_id
and not exists (
select /* */ null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and ( -- c_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ELIG_STDNT_STAT_CVG_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_stdnt_stat_cvg_id
-- and information4 = esc.business_group_id
and information2 = esc.effective_start_date
and information3 = esc.effective_end_date
);
select null
from ben_copy_entity_results cpe
-- pqh_table_route trt
where copy_entity_txn_id = p_copy_entity_txn_id
-- and trt.table_route_id = cpe.table_route_id
and cpe.table_alias = c_table_alias
and information1 = c_pk_id;
select copy_entity_result_id mirror_src_entity_result_id
from ben_copy_entity_results cpe
where cpe.information1 = c_parent_pk_id
and cpe.result_type_cd = l_cv_result_type_cd
and cpe.copy_entity_txn_id = c_copy_entity_txn_id
and cpe.table_alias = c_parent_table_alias;
SELECT egl.*
FROM ben_eligy_criteria egl
WHERE egl.eligy_criteria_id = cv_eligy_criteria_id
AND NOT EXISTS (
SELECT NULL
FROM ben_copy_entity_results cpe
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND mirror_src_entity_result_id =
cv_mirror_src_entity_result_id
AND cpe.table_alias = cv_table_alias
AND information1 = cv_eligy_criteria_id);
SELECT flex_value_set_name
FROM fnd_flex_value_sets
WHERE flex_value_set_id = cv_flex_value_set_id;
select null
from ben_copy_entity_results cpe
where copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_alias = c_table_alias
and information1 = c_pk_id;