[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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.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 acty_base_rt_id
from BEN_ACTY_BASE_RT_F
where (c_pgm_id is not null and c_pgm_id = pgm_id) or
(c_ptip_id is not null and c_ptip_id = ptip_id) or
(c_plip_id is not null and c_plip_id = plip_id) or
(c_pl_id is not null and c_pl_id = pl_id ) or
(c_oiplip_id is not null and c_oiplip_id = oiplip_id) or
(c_oipl_id is not null and c_oipl_id = oipl_id) or
(c_cmbn_plip_id is not null and c_cmbn_plip_id = cmbn_plip_id) or
(c_cmbn_ptip_id is not null and c_cmbn_ptip_id = cmbn_ptip_id) or
(c_cmbn_ptip_opt_id is not null and c_cmbn_ptip_opt_id = cmbn_ptip_opt_id) or
--
(c_opt_id is not null and c_opt_id = opt_id);
select abr.*
from BEN_ACTY_BASE_RT_F abr
where abr.acty_base_rt_id = c_acty_base_rt_id
-- and abr.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_ACTY_BASE_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_acty_base_rt_id
-- and information4 = abr.business_group_id
and information2 = abr.effective_start_date
and information3 = abr.effective_end_date);
select pet.element_name element_name
from pay_element_types_f pet
where (pet.business_group_id is null
or pet.business_group_id = p_business_group_id)
and pet.element_type_id = p_id
and p_date between nvl(pet.effective_start_date,p_date)
and nvl(pet.effective_end_date,p_date) ;
select min(effective_start_date) effective_start_date
from pay_element_types_f
where element_type_id = c_element_type_id;
select piv.name name
from pay_input_values_f piv
where ( piv.business_group_id is null or piv.business_group_id = p_business_group_id )
and piv.element_type_id = p_id1
and p_date between piv.effective_start_date and
piv.effective_end_date
and piv.input_value_id = p_id;
select min(effective_start_date) effective_start_date
from pay_input_values_f
where input_value_id = c_input_value_id;
select pl_id
from ben_plip_f cpp
where cpp.plip_id = c_plip_id
and rownum = 1;
select pl_id
from ben_oipl_f cop
where cop.oipl_id = c_oipl_id
and rownum = 1;
select cpp.pl_id
from ben_oiplip_f opp,
ben_plip_f cpp
where opp.oiplip_id = c_oiplip_id
and cpp.plip_id = opp.plip_id
and rownum = 1;
select acty_rt_pymt_sched_id
from BEN_ACTY_RT_PYMT_SCHED_F
where ACTY_BASE_RT_ID = c_ACTY_BASE_RT_ID ;
select apf.*
from BEN_ACTY_RT_PYMT_SCHED_F apf
where apf.acty_rt_pymt_sched_id = c_acty_rt_pymt_sched_id
--and apf.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 ( --l_mirror_src_entity_result_id is null or
mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_ACTY_RT_PYMT_SCHED_F'
and cpe.table_alias = c_table_alias
and information1 = c_acty_rt_pymt_sched_id
--and information4 = apf.business_group_id
and information2 = apf.effective_start_date
and information3 = apf.effective_end_date);
select pymt_sched_py_freq_id
from BEN_PYMT_SCHED_PY_FREQ
where ACTY_RT_PYMT_SCHED_ID = c_ACTY_RT_PYMT_SCHED_ID ;
select psq.*
from BEN_PYMT_SCHED_PY_FREQ psq
where psq.pymt_sched_py_freq_id = c_pymt_sched_py_freq_id
--and psq.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_PYMT_SCHED_PY_FREQ'
and cpe.table_alias = c_table_alias
and information1 = c_pymt_sched_py_freq_id
--and information4 = psq.business_group_id
);
select distinct actl_prem_id
from BEN_ACTY_BASE_RT_F
where ACTY_BASE_RT_ID = c_ACTY_BASE_RT_ID ;
select apr.*
from BEN_ACTL_PREM_F apr
where apr.actl_prem_id = c_actl_prem_id
-- and apr.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_ACTL_PREM_F'
and cpe.table_alias = c_table_alias
and information1 = c_actl_prem_id
-- and information4 = apr.business_group_id
and information2 = apr.effective_start_date
and information3 = apr.effective_end_date);
select acty_rt_ptd_lmt_id
from BEN_ACTY_RT_PTD_LMT_F
where ACTY_BASE_RT_ID = c_ACTY_BASE_RT_ID ;
select apl.*
from BEN_ACTY_RT_PTD_LMT_F apl
where apl.acty_rt_ptd_lmt_id = c_acty_rt_ptd_lmt_id
-- and apl.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_ACTY_RT_PTD_LMT_F'
and cpe.table_alias = c_table_alias
and information1 = c_acty_rt_ptd_lmt_id
-- and information4 = apl.business_group_id
and information2 = apl.effective_start_date
and information3 = apl.effective_end_date);
select distinct acty_vrbl_rt_id
from BEN_ACTY_VRBL_RT_F
where ACTY_BASE_RT_ID = c_ACTY_BASE_RT_ID ;
select avr.*
from BEN_ACTY_VRBL_RT_F avr
where avr.acty_vrbl_rt_id = c_acty_vrbl_rt_id
-- and avr.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_ACTY_VRBL_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_acty_vrbl_rt_id
-- and information4 = avr.business_group_id
and information2 = avr.effective_start_date
and information3 = avr.effective_end_date);
select distinct cpe.information262 vrbl_rt_prfl_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_ACTY_VRBL_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_acty_vrbl_rt_id
-- and information4 = p_business_group_id
;
select mtchg_rt_id
from BEN_MTCHG_RT_F
where ACTY_BASE_RT_ID = c_ACTY_BASE_RT_ID ;
select mtr.*
from BEN_MTCHG_RT_F mtr
where mtr.mtchg_rt_id = c_mtchg_rt_id
-- and mtr.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_MTCHG_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_mtchg_rt_id
-- and information4 = mtr.business_group_id
and information2 = mtr.effective_start_date
and information3 = mtr.effective_end_date);
select prtl_mo_rt_prtn_val_id
from BEN_PRTL_MO_RT_PRTN_VAL_F
where ACTY_BASE_RT_ID = c_ACTY_BASE_RT_ID ;
select pmr.*
from BEN_PRTL_MO_RT_PRTN_VAL_F pmr
where pmr.prtl_mo_rt_prtn_val_id = c_prtl_mo_rt_prtn_val_id
-- and pmr.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_PRTL_MO_RT_PRTN_VAL_F'
and cpe.table_alias = c_table_alias
and information1 = c_prtl_mo_rt_prtn_val_id
-- and information4 = pmr.business_group_id
and information2 = pmr.effective_start_date
and information3 = pmr.effective_end_date);
select ptd_lmt_id
from BEN_ACTY_RT_PTD_LMT_F
where ACTY_RT_PTD_LMT_ID = c_ACTY_RT_PTD_LMT_ID ;
select pdl.*
from BEN_PTD_LMT_F pdl
where pdl.ptd_lmt_id = c_ptd_lmt_id
-- and pdl.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_PTD_LMT_F'
and cpe.table_alias = c_table_alias
and information1 = c_ptd_lmt_id
-- and information4 = pdl.business_group_id
and information2 = pdl.effective_start_date
and information3 = pdl.effective_end_date);
select extra_input_value_id
from BEN_EXTRA_INPUT_VALUES
where ACTY_BASE_RT_ID = c_ACTY_BASE_RT_ID ;
select eiv.*
from BEN_EXTRA_INPUT_VALUES eiv
where eiv.extra_input_value_id = c_extra_input_value_id
-- and eiv.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_EXTRA_INPUT_VALUES'
and cpe.table_alias = c_table_alias
and information1 = c_extra_input_value_id
-- and information4 = eiv.business_group_id
);
select cpe.information174 element_type_id
from ben_copy_entity_results cpe
-- pqh_table_route tre
where cpe.information1 = c_acty_base_rt_id
and cpe.copy_entity_txn_id = c_copy_entity_txn_id
-- and cpe.table_route_id = tre.table_route_id
and cpe.table_alias = c_table_alias
order by cpe.information3 desc;
select acty_base_rt_ctfn_id
from BEN_ACTY_BASE_RT_CTFN_F
where ACTY_BASE_RT_ID = c_ACTY_BASE_RT_ID ;
select abc.*
from BEN_ACTY_BASE_RT_CTFN_F abc
where abc.acty_base_rt_ctfn_id = c_acty_base_rt_ctfn_id
-- and abc.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_ACTY_BASE_RT_CTFN_F'
and cpe.table_alias = c_table_alias
and information1 = c_acty_base_rt_ctfn_id
-- and information4 = abc.business_group_id
and information2 = abc.effective_start_date
and information3 = abc.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;
l_sql := 'select distinct acty_base_rt_id
from BEN_ACTY_BASE_RT_F
where pgm_id = :Pgm_Id';
l_sql := 'select distinct acty_base_rt_id
from BEN_ACTY_BASE_RT_F
where ptip_id = :Ptip_id';
l_Sql := 'select distinct acty_base_rt_id
from BEN_ACTY_BASE_RT_F
where plip_id = :plip_id';
l_sql := 'select distinct acty_base_rt_id
from BEN_ACTY_BASE_RT_F
where pl_id = :pl_Id';
l_sql := 'select distinct acty_base_rt_id
from BEN_ACTY_BASE_RT_F
where oipl_id = :oipl_id';
l_sql := 'select distinct acty_base_rt_id
from BEN_ACTY_BASE_RT_F
where oiplip_id = :oiplip_id';
l_sql := 'select distinct acty_base_rt_id
from BEN_ACTY_BASE_RT_F
where cmbn_plip_id = :cmbn_plip_id';
l_sql := 'select distinct acty_base_rt_id
from BEN_ACTY_BASE_RT_F
where cmbn_ptip_id = :cmbn_ptip_id';
l_sql := 'select distinct acty_base_rt_id
from BEN_ACTY_BASE_RT_F
where cmbn_ptip_opt_id = :cmbn_ptip_opt_id';
l_sql := 'select distinct acty_base_rt_id
from BEN_ACTY_BASE_RT_F
where opt_id = :opt_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.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 cvg_amt_calc_mthd_id
from BEN_CVG_AMT_CALC_MTHD_F
where (c_pl_id is not null and pl_id = c_pl_id) or
(c_plip_id is not null and c_plip_id = plip_id) or
(c_oipl_id is not null and c_oipl_id = oipl_id) ;
select ccm.*
from BEN_CVG_AMT_CALC_MTHD_F ccm
where ccm.cvg_amt_calc_mthd_id = c_cvg_amt_calc_mthd_id
-- and ccm.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_CVG_AMT_CALC_MTHD_F'
and cpe.table_alias = c_table_alias
and information1 = c_cvg_amt_calc_mthd_id
-- and information4 = ccm.business_group_id
and information2 = ccm.effective_start_date
and information3 = ccm.effective_end_date);
select bnft_vrbl_rt_rl_id
from BEN_BNFT_VRBL_RT_RL_F
where CVG_AMT_CALC_MTHD_ID = c_CVG_AMT_CALC_MTHD_ID ;
select brr.*
from BEN_BNFT_VRBL_RT_RL_F brr
where brr.bnft_vrbl_rt_rl_id = c_bnft_vrbl_rt_rl_id
-- and brr.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_BNFT_VRBL_RT_RL_F'
and cpe.table_alias = c_table_alias
and information1 = c_bnft_vrbl_rt_rl_id
-- and information4 = brr.business_group_id
and information2 = brr.effective_start_date
and information3 = brr.effective_end_date);
select distinct bnft_vrbl_rt_id
from BEN_BNFT_VRBL_RT_F
where CVG_AMT_CALC_MTHD_ID = c_CVG_AMT_CALC_MTHD_ID ;
select bvr.*
from BEN_BNFT_VRBL_RT_F bvr
where bvr.bnft_vrbl_rt_id = c_bnft_vrbl_rt_id
-- and bvr.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_BNFT_VRBL_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_bnft_vrbl_rt_id
-- and information4 = bvr.business_group_id
and information2 = bvr.effective_start_date
and information3 = bvr.effective_end_date);
select distinct cpe.information262 vrbl_rt_prfl_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_BNFT_VRBL_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_bnft_vrbl_rt_id
-- and information4 = p_business_group_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.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 actl_prem_id
from BEN_ACTL_PREM_F
where (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 apr.*
from BEN_ACTL_PREM_F apr
where apr.actl_prem_id = c_actl_prem_id
-- and apr.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_ACTL_PREM_F'
and cpe.table_alias = c_table_alias
and information1 = c_actl_prem_id
-- and information4 = apr.business_group_id
and information2 = apr.effective_start_date
and information3 = apr.effective_end_date);
select prtl_mo_rt_prtn_val_id
from BEN_PRTL_MO_RT_PRTN_VAL_F
where ACTL_PREM_ID = c_ACTL_PREM_ID;
select pmr.*
from BEN_PRTL_MO_RT_PRTN_VAL_F pmr
where pmr.prtl_mo_rt_prtn_val_id = c_prtl_mo_rt_prtn_val_id
-- and pmr.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_PRTL_MO_RT_PRTN_VAL_F'
and cpe.table_alias = c_table_alias
and information1 = c_prtl_mo_rt_prtn_val_id
-- and information4 = pmr.business_group_id
and information2 = pmr.effective_start_date
and information3 = pmr.effective_end_date);
select distinct actl_prem_vrbl_rt_id
from BEN_ACTL_PREM_VRBL_RT_F
where ACTL_PREM_ID = c_ACTL_PREM_ID ;
select apv.*
from BEN_ACTL_PREM_VRBL_RT_F apv
where apv.actl_prem_vrbl_rt_id = c_actl_prem_vrbl_rt_id
-- and apv.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_ACTL_PREM_VRBL_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_actl_prem_vrbl_rt_id
-- and information4 = apv.business_group_id
and information2 = apv.effective_start_date
and information3 = apv.effective_end_date);
select distinct cpe.information262 vrbl_rt_prfl_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_ACTL_PREM_VRBL_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_actl_prem_vrbl_rt_id
-- and information4 = p_business_group_id
;
select actl_prem_vrbl_rt_rl_id
from BEN_ACTL_PREM_VRBL_RT_RL_F
where ACTL_PREM_ID = c_ACTL_PREM_ID ;
select ava.*
from BEN_ACTL_PREM_VRBL_RT_RL_F ava
where ava.actl_prem_vrbl_rt_rl_id = c_actl_prem_vrbl_rt_rl_id
-- and ava.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_ACTL_PREM_VRBL_RT_RL_F'
and cpe.table_alias = c_table_alias
and information1 = c_actl_prem_vrbl_rt_rl_id
-- and information4 = ava.business_group_id
and information2 = ava.effective_start_date
and information3 = ava.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.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 min(cpe.information2) min_esd
from ben_copy_entity_results cpe
-- pqh_table_route trt
where cpe.information1= c_parent_pk_id
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 agf.*
from BEN_AGE_FCTR agf
where agf.age_fctr_id = c_age_fctr_id
-- and agf.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_AGE_FCTR'
and cpe.table_alias = c_table_alias
and information1 = c_age_fctr_id
-- and information4 = agf.business_group_id
);
select cla.*
from BEN_CMBN_AGE_LOS_FCTR cla
where cla.cmbn_age_los_fctr_id = c_cmbn_age_los_fctr_id
-- and cla.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_CMBN_AGE_LOS_FCTR'
and cpe.table_alias = c_table_alias
and information1 = c_cmbn_age_los_fctr_id
-- and information4 = cla.business_group_id
);
select clf.*
from BEN_COMP_LVL_FCTR clf
where clf.comp_lvl_fctr_id = c_comp_lvl_fctr_id
-- and clf.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_COMP_LVL_FCTR'
and cpe.table_alias = c_table_alias
and information1 = c_comp_lvl_fctr_id
-- and information4 = clf.business_group_id
);
select distinct cpe.information225 bnfts_bal_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_COMP_LVL_FCTR'
and cpe.table_alias = c_table_alias
and information1 = c_comp_lvl_fctr_id
-- and information4 = p_business_group_id
;
select hwf.*
from BEN_HRS_WKD_IN_PERD_FCTR hwf
where hwf.hrs_wkd_in_perd_fctr_id = c_hrs_wkd_in_perd_fctr_id
-- and hwf.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_HRS_WKD_IN_PERD_FCTR'
and cpe.table_alias = c_table_alias
and information1 = c_hrs_wkd_in_perd_fctr_id
-- and information4 = hwf.business_group_id
);
select distinct cpe.information225 bnfts_bal_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_HRS_WKD_IN_PERD_FCTR'
and cpe.table_alias = c_table_alias
and information1 = c_hrs_wkd_in_perd_fctr_id
-- and information4 = p_business_group_id
;
select lsf.*
from BEN_LOS_FCTR lsf
where lsf.los_fctr_id = c_los_fctr_id
-- and lsf.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_LOS_FCTR'
and cpe.table_alias = c_table_alias
and information1 = c_los_fctr_id
-- and information4 = lsf.business_group_id
);
select pff.*
from BEN_PCT_FL_TM_FCTR pff
where pff.pct_fl_tm_fctr_id = c_pct_fl_tm_fctr_id
-- and pff.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_PCT_FL_TM_FCTR'
and cpe.table_alias = c_table_alias
and information1 = c_pct_fl_tm_fctr_id
-- and information4 = pff.business_group_id
);
select pbt.balance_name||' - '||pbd.dimension_name dsp_name
-- pdb.defined_balance_id defined_balance_id
from pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
where nvl(pdb.business_group_id, p_business_group_id) = p_business_group_id
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pdb.defined_balance_id = p_defined_balance_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 distinct vrbl_rt_elig_prfl_id
from BEN_VRBL_RT_ELIG_PRFL_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select vep.*
from BEN_VRBL_RT_ELIG_PRFL_F vep
where vep.vrbl_rt_elig_prfl_id = c_vrbl_rt_elig_prfl_id
-- and vep.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_VRBL_RT_ELIG_PRFL_F'
and cpe.table_alias = c_table_alias
and information1 = c_vrbl_rt_elig_prfl_id
-- and information4 = vep.business_group_id
and information2 = vep.effective_start_date
and information3 = vep.effective_end_date
);
select distinct eligy_prfl_id
from BEN_VRBL_RT_ELIG_PRFL_F
where VRBL_RT_ELIG_PRFL_ID = c_VRBL_RT_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.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 vpf.*
from BEN_VRBL_RT_PRFL_F vpf
where vpf.vrbl_rt_prfl_id = c_vrbl_rt_prfl_id
-- and vpf.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_VRBL_RT_PRFL_F'
and cpe.table_alias = c_table_alias
and information1 = c_vrbl_rt_prfl_id
-- and information4 = vpf.business_group_id
and information2 = vpf.effective_start_date
and information3 = vpf.effective_end_date);
select distinct age_rt_id
from BEN_AGE_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select art.*
from BEN_AGE_RT_F art
where art.age_rt_id = c_age_rt_id
-- and art.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_AGE_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_age_rt_id
-- and information4 = art.business_group_id
and information2 = art.effective_start_date
and information3 = art.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_AGE_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_age_rt_id
-- and information4 = p_business_group_id
;
select asnt_set_rt_id
from BEN_ASNT_SET_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select asr.*
from BEN_ASNT_SET_RT_F asr
where asr.asnt_set_rt_id = c_asnt_set_rt_id
-- and asr.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_ASNT_SET_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_asnt_set_rt_id
-- and information4 = asr.business_group_id
and information2 = asr.effective_start_date
and information3 = asr.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 benfts_grp_rt_id
from BEN_BENFTS_GRP_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select brg.*
from BEN_BENFTS_GRP_RT_F brg
where brg.benfts_grp_rt_id = c_benfts_grp_rt_id
-- and brg.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_BENFTS_GRP_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_benfts_grp_rt_id
-- and information4 = brg.business_group_id
and information2 = brg.effective_start_date
and information3 = brg.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_BENFTS_GRP_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_benfts_grp_rt_id
-- and information4 = p_business_group_id
;
select brgng_unit_rt_id
from BEN_BRGNG_UNIT_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select bur.*
from BEN_BRGNG_UNIT_RT_F bur
where bur.brgng_unit_rt_id = c_brgng_unit_rt_id
-- and bur.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_BRGNG_UNIT_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_brgng_unit_rt_id
-- and information4 = bur.business_group_id
and information2 = bur.effective_start_date
and information3 = bur.effective_end_date);
select distinct cmbn_age_los_rt_id
from BEN_CMBN_AGE_LOS_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select cmr.*
from BEN_CMBN_AGE_LOS_RT_F cmr
where cmr.cmbn_age_los_rt_id = c_cmbn_age_los_rt_id
-- and cmr.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_CMBN_AGE_LOS_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_cmbn_age_los_rt_id
-- and information4 = cmr.business_group_id
and information2 = cmr.effective_start_date
and information3 = cmr.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_CMBN_AGE_LOS_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_cmbn_age_los_rt_id
-- and information4 = p_business_group_id
;
select distinct comp_lvl_rt_id
from BEN_COMP_LVL_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select clr.*
from BEN_COMP_LVL_RT_F clr
where clr.comp_lvl_rt_id = c_comp_lvl_rt_id
-- and clr.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_COMP_LVL_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_comp_lvl_rt_id
-- and information4 = clr.business_group_id
and information2 = clr.effective_start_date
and information3 = clr.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_COMP_LVL_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_comp_lvl_rt_id
-- and information4 = p_business_group_id
;
select dsbld_rt_id
from BEN_DSBLD_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select dbr.*
from BEN_DSBLD_RT_F dbr
where dbr.dsbld_rt_id = c_dsbld_rt_id
-- and dbr.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_DSBLD_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_dsbld_rt_id
-- and information4 = dbr.business_group_id
and information2 = dbr.effective_start_date
and information3 = dbr.effective_end_date);
select ee_stat_rt_id
from BEN_EE_STAT_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select esr.*
from BEN_EE_STAT_RT_F esr
where esr.ee_stat_rt_id = c_ee_stat_rt_id
-- and esr.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_EE_STAT_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_ee_stat_rt_id
-- and information4 = esr.business_group_id
and information2 = esr.effective_start_date
and information3 = esr.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 fl_tm_pt_tm_rt_id
from BEN_FL_TM_PT_TM_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select ftr.*
from BEN_FL_TM_PT_TM_RT_F ftr
where ftr.fl_tm_pt_tm_rt_id = c_fl_tm_pt_tm_rt_id
-- and ftr.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_FL_TM_PT_TM_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_fl_tm_pt_tm_rt_id
-- and information4 = ftr.business_group_id
and information2 = ftr.effective_start_date
and information3 = ftr.effective_end_date);
select gndr_rt_id
from BEN_GNDR_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select gnr.*
from BEN_GNDR_RT_F gnr
where gnr.gndr_rt_id = c_gndr_rt_id
-- and gnr.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_GNDR_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_gndr_rt_id
-- and information4 = gnr.business_group_id
and information2 = gnr.effective_start_date
and information3 = gnr.effective_end_date);
select grade_rt_id
from BEN_GRADE_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select grr.*
from BEN_GRADE_RT_F grr
where grr.grade_rt_id = c_grade_rt_id
-- and grr.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_GRADE_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_grade_rt_id
-- and information4 = grr.business_group_id
and information2 = grr.effective_start_date
and information3 = grr.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 hrly_slrd_rt_id
from BEN_HRLY_SLRD_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select hsr.*
from BEN_HRLY_SLRD_RT_F hsr
where hsr.hrly_slrd_rt_id = c_hrly_slrd_rt_id
-- and hsr.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_HRLY_SLRD_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_hrly_slrd_rt_id
-- and information4 = hsr.business_group_id
and information2 = hsr.effective_start_date
and information3 = hsr.effective_end_date);
select distinct hrs_wkd_in_perd_rt_id
from BEN_HRS_WKD_IN_PERD_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select hwr.*
from BEN_HRS_WKD_IN_PERD_RT_F hwr
where hwr.hrs_wkd_in_perd_rt_id = c_hrs_wkd_in_perd_rt_id
-- and hwr.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_HRS_WKD_IN_PERD_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_hrs_wkd_in_perd_rt_id
-- and information4 = hwr.business_group_id
and information2 = hwr.effective_start_date
and information3 = hwr.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_HRS_WKD_IN_PERD_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_hrs_wkd_in_perd_rt_id
-- and information4 = p_business_group_id
;
select lbr_mmbr_rt_id
from BEN_LBR_MMBR_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select lmm.*
from BEN_LBR_MMBR_RT_F lmm
where lmm.lbr_mmbr_rt_id = c_lbr_mmbr_rt_id
-- and lmm.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_LBR_MMBR_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_lbr_mmbr_rt_id
-- and information4 = lmm.business_group_id
and information2 = lmm.effective_start_date
and information3 = lmm.effective_end_date);
select lgl_enty_rt_id
from BEN_LGL_ENTY_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select ler.*
from BEN_LGL_ENTY_RT_F ler
where ler.lgl_enty_rt_id = c_lgl_enty_rt_id
-- and ler.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_LGL_ENTY_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_lgl_enty_rt_id
-- and information4 = ler.business_group_id
and information2 = ler.effective_start_date
and information3 = ler.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 loa_rsn_rt_id
from BEN_LOA_RSN_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select lar.*
from BEN_LOA_RSN_RT_F lar
where lar.loa_rsn_rt_id = c_loa_rsn_rt_id
-- and lar.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_LOA_RSN_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_loa_rsn_rt_id
-- and information4 = lar.business_group_id
and information2 = lar.effective_start_date
and information3 = lar.effective_end_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 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 date_effective
from per_absence_attendance_types
where absence_attendance_type_id = c_absence_attendance_type_id;
select distinct los_rt_id
from BEN_LOS_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select lsr.*
from BEN_LOS_RT_F lsr
where lsr.los_rt_id = c_los_rt_id
-- and lsr.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_LOS_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_los_rt_id
-- and information4 = lsr.business_group_id
and information2 = lsr.effective_start_date
and information3 = lsr.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_LOS_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_los_rt_id
-- and information4 = p_business_group_id
;
select lvg_rsn_rt_id
from BEN_LVG_RSN_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select lrn.*
from BEN_LVG_RSN_RT_F lrn
where lrn.lvg_rsn_rt_id = c_lvg_rsn_rt_id
-- and lrn.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_LVG_RSN_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_lvg_rsn_rt_id
-- and information4 = lrn.business_group_id
and information2 = lrn.effective_start_date
and information3 = lrn.effective_end_date);
select org_unit_rt_id
from BEN_ORG_UNIT_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select our.*
from BEN_ORG_UNIT_RT_F our
where our.org_unit_rt_id = c_org_unit_rt_id
-- and our.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_ORG_UNIT_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_org_unit_rt_id
-- and information4 = our.business_group_id
and information2 = our.effective_start_date
and information3 = our.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 distinct pct_fl_tm_rt_id
from BEN_PCT_FL_TM_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select pfr.*
from BEN_PCT_FL_TM_RT_F pfr
where pfr.pct_fl_tm_rt_id = c_pct_fl_tm_rt_id
-- and pfr.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_PCT_FL_TM_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_pct_fl_tm_rt_id
-- and information4 = pfr.business_group_id
and information2 = pfr.effective_start_date
and information3 = pfr.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_PCT_FL_TM_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_pct_fl_tm_rt_id
-- and information4 = p_business_group_id
;
select per_typ_rt_id
from BEN_PER_TYP_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select ptr.*
from BEN_PER_TYP_RT_F ptr
where ptr.per_typ_rt_id = c_per_typ_rt_id
-- and ptr.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_PER_TYP_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_per_typ_rt_id
-- and information4 = ptr.business_group_id
and information2 = ptr.effective_start_date
and information3 = ptr.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 poe_rt_id
from BEN_POE_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select prt.*
from BEN_POE_RT_F prt
where prt.poe_rt_id = c_poe_rt_id
-- and prt.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_POE_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_poe_rt_id
-- and information4 = prt.business_group_id
and information2 = prt.effective_start_date
and information3 = prt.effective_end_date);
select ppl_grp_rt_id
from BEN_PPL_GRP_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select pgr.*
from BEN_PPL_GRP_RT_F pgr
where pgr.ppl_grp_rt_id = c_ppl_grp_rt_id
-- and pgr.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_PPL_GRP_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_ppl_grp_rt_id
-- and information4 = pgr.business_group_id
and information2 = pgr.effective_start_date
and information3 = pgr.effective_end_date);
select distinct pstl_zip_rt_id
from BEN_PSTL_ZIP_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select pzr.*
from BEN_PSTL_ZIP_RT_F pzr
where pzr.pstl_zip_rt_id = c_pstl_zip_rt_id
-- and pzr.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_PSTL_ZIP_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_pstl_zip_rt_id
-- and information4 = pzr.business_group_id
and information2 = pzr.effective_start_date
and information3 = pzr.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_PSTL_ZIP_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_pstl_zip_rt_id
-- and information4 = p_business_group_id
;
select pyrl_rt_id
from BEN_PYRL_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select pr_.*
from BEN_PYRL_RT_F pr_
where pr_.pyrl_rt_id = c_pyrl_rt_id
-- and pr_.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_PYRL_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_pyrl_rt_id
-- and information4 = pr_.business_group_id
and information2 = pr_.effective_start_date
and information3 = pr_.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 py_bss_rt_id
from BEN_PY_BSS_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select pbr.*
from BEN_PY_BSS_RT_F pbr
where pbr.py_bss_rt_id = c_py_bss_rt_id
-- and pbr.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_PY_BSS_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_py_bss_rt_id
-- and information4 = pbr.business_group_id
and information2 = pbr.effective_start_date
and information3 = pbr.effective_end_date);
select name from per_pay_bases
where business_group_id = p_business_group_id
and pay_basis_id = p_id;
select schedd_hrs_rt_id
from BEN_SCHEDD_HRS_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select shr.*
from BEN_SCHEDD_HRS_RT_F shr
where shr.schedd_hrs_rt_id = c_schedd_hrs_rt_id
-- and shr.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_SCHEDD_HRS_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_schedd_hrs_rt_id
-- and information4 = shr.business_group_id
and information2 = shr.effective_start_date
and information3 = shr.effective_end_date);
select distinct svc_area_rt_id
from BEN_SVC_AREA_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select sar.*
from BEN_SVC_AREA_RT_F sar
where sar.svc_area_rt_id = c_svc_area_rt_id
-- and sar.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_SVC_AREA_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_svc_area_rt_id
-- and information4 = sar.business_group_id
and information2 = sar.effective_start_date
and information3 = sar.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_SVC_AREA_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_svc_area_rt_id
-- and information4 = p_business_group_id
;
select tbco_use_rt_id
from BEN_TBCO_USE_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select tur.*
from BEN_TBCO_USE_RT_F tur
where tur.tbco_use_rt_id = c_tbco_use_rt_id
-- and tur.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_TBCO_USE_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_tbco_use_rt_id
-- and information4 = tur.business_group_id
and information2 = tur.effective_start_date
and information3 = tur.effective_end_date);
select ttl_cvg_vol_rt_id
from BEN_TTL_CVG_VOL_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select tcv.*
from BEN_TTL_CVG_VOL_RT_F tcv
where tcv.ttl_cvg_vol_rt_id = c_ttl_cvg_vol_rt_id
-- and tcv.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_TTL_CVG_VOL_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_ttl_cvg_vol_rt_id
-- and information4 = tcv.business_group_id
and information2 = tcv.effective_start_date
and information3 = tcv.effective_end_date);
select ttl_prtt_rt_id
from BEN_TTL_PRTT_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select ttp.*
from BEN_TTL_PRTT_RT_F ttp
where ttp.ttl_prtt_rt_id = c_ttl_prtt_rt_id
-- and ttp.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_TTL_PRTT_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_ttl_prtt_rt_id
-- and information4 = ttp.business_group_id
and information2 = ttp.effective_start_date
and information3 = ttp.effective_end_date);
select vrbl_mtchg_rt_id
from BEN_VRBL_MTCHG_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select vmr.*
from BEN_VRBL_MTCHG_RT_F vmr
where vmr.vrbl_mtchg_rt_id = c_vrbl_mtchg_rt_id
-- and vmr.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_VRBL_MTCHG_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_vrbl_mtchg_rt_id
-- and information4 = vmr.business_group_id
and information2 = vmr.effective_start_date
and information3 = vmr.effective_end_date);
select vrbl_rt_prfl_rl_id
from BEN_VRBL_RT_PRFL_RL_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select vpr.*
from BEN_VRBL_RT_PRFL_RL_F vpr
where vpr.vrbl_rt_prfl_rl_id = c_vrbl_rt_prfl_rl_id
-- and vpr.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_VRBL_RT_PRFL_RL_F'
and cpe.table_alias = c_table_alias
and information1 = c_vrbl_rt_prfl_rl_id
-- and information4 = vpr.business_group_id
and information2 = vpr.effective_start_date
and information3 = vpr.effective_end_date);
select wk_loc_rt_id
from BEN_WK_LOC_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select wlr.*
from BEN_WK_LOC_RT_F wlr
where wlr.wk_loc_rt_id = c_wk_loc_rt_id
-- and wlr.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_WK_LOC_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_wk_loc_rt_id
-- and information4 = wlr.business_group_id
and information2 = wlr.effective_start_date
and information3 = wlr.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 comptncy_rt_id
from BEN_COMPTNCY_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select cty.*
from BEN_COMPTNCY_RT_F cty
where cty.comptncy_rt_id = c_comptncy_rt_id
-- and cty.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_COMPTNCY_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_comptncy_rt_id
-- and information4 = cty.business_group_id
and information2 = cty.effective_start_date
and information3 = cty.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 job_rt_id
from BEN_JOB_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select jrt.*
from BEN_JOB_RT_F jrt
where jrt.job_rt_id = c_job_rt_id
-- and jrt.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_JOB_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_job_rt_id
-- and information4 = jrt.business_group_id
and information2 = jrt.effective_start_date
and information3 = jrt.effective_end_date);
select name
from per_jobs_vl
where business_group_id = p_business_group_id
and 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 pstn_rt_id
from BEN_PSTN_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select pst.*
from BEN_PSTN_RT_F pst
where pst.pstn_rt_id = c_pstn_rt_id
-- and pst.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_PSTN_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_pstn_rt_id
-- and information4 = pst.business_group_id
and information2 = pst.effective_start_date
and information3 = pst.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 qual_titl_rt_id
from BEN_QUAL_TITL_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select qtr.*
from BEN_QUAL_TITL_RT_F qtr
where qtr.qual_titl_rt_id = c_qual_titl_rt_id
--and qtr.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_QUAL_TITL_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_qual_titl_rt_id
-- and information4 = qtr.business_group_id
and information2 = qtr.effective_start_date
and information3 = qtr.effective_end_date);
select name
from per_qualification_types_vl
where qualification_type_id = p_id;
select cbr_quald_bnf_rt_id
from BEN_CBR_QUALD_BNF_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select cqr.*
from BEN_CBR_QUALD_BNF_RT_F cqr
where cqr.cbr_quald_bnf_rt_id = c_cbr_quald_bnf_rt_id
--and cqr.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_CBR_QUALD_BNF_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_cbr_quald_bnf_rt_id
-- and information4 = cqr.business_group_id
and information2 = cqr.effective_start_date
and information3 = cqr.effective_end_date);
select cntng_prtn_prfl_rt_id
from BEN_CNTNG_PRTN_PRFL_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select cpn.*
from BEN_CNTNG_PRTN_PRFL_RT_F cpn
where cpn.cntng_prtn_prfl_rt_id = c_cntng_prtn_prfl_rt_id
--and cpn.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_PRFL_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_cntng_prtn_prfl_rt_id
-- and information4 = cpn.business_group_id
and information2 = cpn.effective_start_date
and information3 = cpn.effective_end_date);
select dpnt_cvrd_othr_pl_rt_id
from BEN_DPNT_CVRD_OTHR_PL_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select dcl.*
from BEN_DPNT_CVRD_OTHR_PL_RT_F dcl
where dcl.dpnt_cvrd_othr_pl_rt_id = c_dpnt_cvrd_othr_pl_rt_id
--and dcl.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_OTHR_PL_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_dpnt_cvrd_othr_pl_rt_id
-- and information4 = dcl.business_group_id
and information2 = dcl.effective_start_date
and information3 = dcl.effective_end_date);
select dpnt_cvrd_plip_rt_id
from BEN_DPNT_CVRD_PLIP_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select dcp.*
from BEN_DPNT_CVRD_PLIP_RT_F dcp
where dcp.dpnt_cvrd_plip_rt_id = c_dpnt_cvrd_plip_rt_id
--and dcp.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_PLIP_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_dpnt_cvrd_plip_rt_id
-- and information4 = dcp.business_group_id
and information2 = dcp.effective_start_date
and information3 = dcp.effective_end_date);
select dpnt_cvrd_othr_ptip_rt_id
from BEN_DPNT_CVRD_OTHR_PTIP_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select dco.*
from BEN_DPNT_CVRD_OTHR_PTIP_RT_F dco
where dco.dpnt_cvrd_othr_ptip_rt_id = c_dpnt_cvrd_othr_ptip_rt_id
--and dco.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_OTHR_PTIP_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_dpnt_cvrd_othr_ptip_rt_id
-- and information4 = dco.business_group_id
and information2 = dco.effective_start_date
and information3 = dco.effective_end_date);
select dpnt_cvrd_othr_pgm_rt_id
from BEN_DPNT_CVRD_OTHR_PGM_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select dop.*
from BEN_DPNT_CVRD_OTHR_PGM_RT_F dop
where dop.dpnt_cvrd_othr_pgm_rt_id = c_dpnt_cvrd_othr_pgm_rt_id
--and dop.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_OTHR_PGM_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_dpnt_cvrd_othr_pgm_rt_id
-- and information4 = dop.business_group_id
and information2 = dop.effective_start_date
and information3 = dop.effective_end_date);
select prtt_anthr_pl_rt_id
from BEN_PRTT_ANTHR_PL_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select pap.*
from BEN_PRTT_ANTHR_PL_RT_F pap
where pap.prtt_anthr_pl_rt_id = c_prtt_anthr_pl_rt_id
--and pap.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_PRTT_ANTHR_PL_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_prtt_anthr_pl_rt_id
-- and information4 = pap.business_group_id
and information2 = pap.effective_start_date
and information3 = pap.effective_end_date);
select enrld_anthr_oipl_rt_id
from BEN_ENRLD_ANTHR_OIPL_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select eao.*
from BEN_ENRLD_ANTHR_OIPL_RT_F eao
where eao.enrld_anthr_oipl_rt_id = c_enrld_anthr_oipl_rt_id
--and eao.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_ENRLD_ANTHR_OIPL_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_enrld_anthr_oipl_rt_id
-- and information4 = eao.business_group_id
and information2 = eao.effective_start_date
and information3 = eao.effective_end_date);
select enrld_anthr_pl_rt_id
from BEN_ENRLD_ANTHR_PL_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select enl.*
from BEN_ENRLD_ANTHR_PL_RT_F enl
where enl.enrld_anthr_pl_rt_id = c_enrld_anthr_pl_rt_id
--and enl.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_ENRLD_ANTHR_PL_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_enrld_anthr_pl_rt_id
-- and information4 = enl.business_group_id
and information2 = enl.effective_start_date
and information3 = enl.effective_end_date);
select enrld_anthr_plip_rt_id
from BEN_ENRLD_ANTHR_PLIP_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select ear.*
from BEN_ENRLD_ANTHR_PLIP_RT_F ear
where ear.enrld_anthr_plip_rt_id = c_enrld_anthr_plip_rt_id
--and ear.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_ENRLD_ANTHR_PLIP_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_enrld_anthr_plip_rt_id
-- and information4 = ear.business_group_id
and information2 = ear.effective_start_date
and information3 = ear.effective_end_date);
select enrld_anthr_ptip_rt_id
from BEN_ENRLD_ANTHR_PTIP_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select ent.*
from BEN_ENRLD_ANTHR_PTIP_RT_F ent
where ent.enrld_anthr_ptip_rt_id = c_enrld_anthr_ptip_rt_id
--and ent.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_ENRLD_ANTHR_PTIP_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_enrld_anthr_ptip_rt_id
-- and information4 = ent.business_group_id
and information2 = ent.effective_start_date
and information3 = ent.effective_end_date);
select enrld_anthr_pgm_rt_id
from BEN_ENRLD_ANTHR_PGM_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select epm.*
from BEN_ENRLD_ANTHR_PGM_RT_F epm
where epm.enrld_anthr_pgm_rt_id = c_enrld_anthr_pgm_rt_id
--and epm.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_ENRLD_ANTHR_PGM_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_enrld_anthr_pgm_rt_id
-- and information4 = epm.business_group_id
and information2 = epm.effective_start_date
and information3 = epm.effective_end_date);
select no_othr_cvg_rt_id
from BEN_NO_OTHR_CVG_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select noc.*
from BEN_NO_OTHR_CVG_RT_F noc
where noc.no_othr_cvg_rt_id = c_no_othr_cvg_rt_id
--and noc.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_NO_OTHR_CVG_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_no_othr_cvg_rt_id
-- and information4 = noc.business_group_id
and information2 = noc.effective_start_date
and information3 = noc.effective_end_date);
select optd_mdcr_rt_id
from BEN_OPTD_MDCR_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select omr.*
from BEN_OPTD_MDCR_RT_F omr
where omr.optd_mdcr_rt_id = c_optd_mdcr_rt_id
--and omr.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_OPTD_MDCR_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_optd_mdcr_rt_id
-- and information4 = omr.business_group_id
and information2 = omr.effective_start_date
and information3 = omr.effective_end_date);
select dpnt_othr_ptip_rt_id
from BEN_DPNT_OTHR_PTIP_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select dot.*
from BEN_DPNT_OTHR_PTIP_RT_F dot
where dot.dpnt_othr_ptip_rt_id = c_dpnt_othr_ptip_rt_id
--and dot.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_OTHR_PTIP_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_dpnt_othr_ptip_rt_id
-- and information4 = dot.business_group_id
and information2 = dot.effective_start_date
and information3 = dot.effective_end_date);
select perf_rtng_rt_id
from BEN_PERF_RTNG_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select prr.*
from BEN_PERF_RTNG_RT_F prr
where prr.perf_rtng_rt_id = c_perf_rtng_rt_id
--and prr.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_PERF_RTNG_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_perf_rtng_rt_id
-- and information4 = prr.business_group_id
and information2 = prr.effective_start_date
and information3 = prr.effective_end_date);
select qua_in_gr_rt_id
from BEN_QUA_IN_GR_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select qig.*
from BEN_QUA_IN_GR_RT_F qig
where qig.qua_in_gr_rt_id = c_qua_in_gr_rt_id
--and qig.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_QUA_IN_GR_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_qua_in_gr_rt_id
-- and information4 = qig.business_group_id
and information2 = qig.effective_start_date
and information3 = qig.effective_end_date);
select othr_ptip_rt_id
from BEN_OTHR_PTIP_RT_F
where VRBL_RT_PRFL_ID = c_VRBL_RT_PRFL_ID ;
select opr.*
from BEN_OTHR_PTIP_RT_F opr
where opr.othr_ptip_rt_id = c_othr_ptip_rt_id
--and opr.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_OTHR_PTIP_RT_F'
and cpe.table_alias = c_table_alias
and information1 = c_othr_ptip_rt_id
-- and information4 = opr.business_group_id
and information2 = opr.effective_start_date
and information3 = opr.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,
pqh_table_route trt
where cpe.information1= c_parent_pk_id
and cpe.result_type_cd = 'DISPLAY'
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 bnft_prvdr_pool_id
from BEN_BNFT_PRVDR_POOL_F
where (c_pgm_id is not null and c_pgm_id = pgm_id
and ptip_id is null and plip_id is null
and oiplip_id is null and cmbn_plip_id is null
and cmbn_ptip_id is null and cmbn_ptip_opt_id is null
) or
(c_ptip_id is not null and c_ptip_id = ptip_id) or
(c_plip_id is not null and c_plip_id = plip_id) or
(c_oiplip_id is not null and c_oiplip_id = oiplip_id) or
(c_cmbn_plip_id is not null and c_cmbn_plip_id = cmbn_plip_id) or
(c_cmbn_ptip_id is not null and c_cmbn_ptip_id = cmbn_ptip_id) or
(c_cmbn_ptip_opt_id is not null and c_cmbn_ptip_opt_id = cmbn_ptip_opt_id ) ;
select bpp.*
from BEN_BNFT_PRVDR_POOL_F bpp
where bpp.bnft_prvdr_pool_id = c_bnft_prvdr_pool_id
--and bpp.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_BNFT_PRVDR_POOL_F'
and cpe.table_alias = c_table_alias
and information1 = c_bnft_prvdr_pool_id
-- and information4 = bpp.business_group_id
and information2 = bpp.effective_start_date
and information3 = bpp.effective_end_date);
select aplcn_to_bnft_pool_id
from BEN_APLCN_TO_BNFT_POOL_F
where BNFT_PRVDR_POOL_ID = c_BNFT_PRVDR_POOL_ID ;
select abp.*
from BEN_APLCN_TO_BNFT_POOL_F abp
where abp.aplcn_to_bnft_pool_id = c_aplcn_to_bnft_pool_id
--and abp.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_APLCN_TO_BNFT_POOL_F'
and cpe.table_alias = c_table_alias
and information1 = c_aplcn_to_bnft_pool_id
-- and information4 = abp.business_group_id
and information2 = abp.effective_start_date
and information3 = abp.effective_end_date);
select bnft_pool_rlovr_rqmt_id
from BEN_BNFT_POOL_RLOVR_RQMT_F
where BNFT_PRVDR_POOL_ID = c_BNFT_PRVDR_POOL_ID ;
select bpr.*
from BEN_BNFT_POOL_RLOVR_RQMT_F bpr
where bpr.bnft_pool_rlovr_rqmt_id = c_bnft_pool_rlovr_rqmt_id
--and bpr.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_BNFT_POOL_RLOVR_RQMT_F'
and cpe.table_alias = c_table_alias
and information1 = c_bnft_pool_rlovr_rqmt_id
-- and information4 = bpr.business_group_id
and information2 = bpr.effective_start_date
and information3 = bpr.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.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 sva.*
from BEN_SVC_AREA_F sva
where sva.svc_area_id = c_svc_area_id
--and sva.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_SVC_AREA_F'
and cpe.table_alias = c_table_alias
and information1 = c_svc_area_id
-- and information4 = sva.business_group_id
and information2 = sva.effective_start_date
and information3 = sva.effective_end_date);
select distinct svc_area_pstl_zip_rng_id
from BEN_SVC_AREA_PSTL_ZIP_RNG_F
where SVC_AREA_ID = c_SVC_AREA_ID ;
select saz.*
from BEN_SVC_AREA_PSTL_ZIP_RNG_F saz
where saz.svc_area_pstl_zip_rng_id = c_svc_area_pstl_zip_rng_id
--and saz.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_SVC_AREA_PSTL_ZIP_RNG_F'
and cpe.table_alias = c_table_alias
and information1 = c_svc_area_pstl_zip_rng_id
-- and information4 = saz.business_group_id
and information2 = saz.effective_start_date
and information3 = saz.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_SVC_AREA_PSTL_ZIP_RNG_F'
and cpe.table_alias = c_table_alias
and information1 = c_svc_area_pstl_zip_rng_id
-- and information4 = p_business_group_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 = 'DISPLAY'
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 rzr.*
from BEN_PSTL_ZIP_RNG_F rzr
where rzr.pstl_zip_rng_id = c_pstl_zip_rng_id
--and rzr.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_PSTL_ZIP_RNG_F'
and cpe.table_alias = c_table_alias
and information1 = c_pstl_zip_rng_id
-- and information4 = rzr.business_group_id
and information2 = rzr.effective_start_date
and information3 = rzr.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,
pqh_table_route trt
where cpe.information1= c_parent_pk_id
and cpe.result_type_cd = 'DISPLAY'
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 bnb.*
from BEN_BNFTS_BAL_F bnb
where bnb.bnfts_bal_id = c_bnfts_bal_id
--and bnb.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_BNFTS_BAL_F'
and cpe.table_alias = c_table_alias
and information1 = c_bnfts_bal_id
-- and information4 = bnb.business_group_id
and information2 = bnb.effective_start_date
and information3 = bnb.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,
pqh_table_route trt
where cpe.information1= c_parent_pk_id
and cpe.result_type_cd = 'DISPLAY'
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 bng.*
from BEN_BENFTS_GRP bng
where bng.benfts_grp_id = c_benfts_grp_id
--and bng.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_BENFTS_GRP'
and cpe.table_alias = c_table_alias
and information1 = c_benfts_grp_id
-- and information4 = bng.business_group_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 = 'DISPLAY'
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 acrs_ptip_cvg_id
from BEN_ACRS_PTIP_CVG_F
where PGM_ID = c_PGM_ID;
select apc.*
from BEN_ACRS_PTIP_CVG_F apc
where apc.acrs_ptip_cvg_id = c_acrs_ptip_cvg_id
--and apc.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_ACRS_PTIP_CVG_F'
and cpe.table_alias = c_table_alias
and information1 = c_acrs_ptip_cvg_id
-- and information4 = apc.business_group_id
);