[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct pln.pl_id,
null Information8
from BEN_PLIP_F plip,
BEN_PL_F PLN
where ( C_PLIP_ID is not null and plip.PLIP_ID = C_PLIP_ID )
and plip.pl_id=pln.pl_id
union
select distinct pln.pl_id,
'PLNIP' Information8
from BEN_PL_F PLN
where ( C_PL_ID is not null and pln.PL_ID = C_PL_ID);
select pln.pl_id,
ptp.name pl_typ_name,
hl.meaning Plan_Usage,
null Information8
from BEN_PLIP_F plip,
BEN_PL_F PLN,
ben_pl_typ_f ptp,
hr_lookups hl
where ( C_PLIP_ID is not null and plip.PLIP_ID = C_PLIP_ID )
and plip.pl_id=pln.pl_id
and pln.pl_typ_id = ptp.pl_typ_id
and pln.pl_cd = hl.lookup_code
and hl.lookup_type = 'BEN_PL'
union
select pln.pl_id,
ptp.name pl_typ_name,
hl.meaning Plan_Usage,
'PLNIP' Information8
from BEN_PL_F PLN,
ben_pl_typ_f ptp,
hr_lookups hl
where ( C_PL_ID is not null and pln.PL_ID = C_PL_ID )
and pln.pl_typ_id = ptp.pl_typ_id
and pln.pl_cd = hl.lookup_code
and hl.lookup_type = 'BEN_PL' ;
select pln.*
from BEN_PL_F pln
where pln.pl_id = c_pl_id
-- and pln.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_PL_F'
and cpe.table_alias = c_table_alias
and information1 = c_pl_id
-- and information4 = pln.business_group_id
and information2 = pln.effective_start_date
and information3 = pln.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 = 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)
and cpe.table_alias = c_parent_table_alias ;
select min(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.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 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 trt.from_clause = 'OAB'
-- and trt.where_clause = upper(c_parent_table_name);
select name group_pl_name
from ben_pl_f
where pl_id = p_group_pl_id
and p_effective_date between effective_start_date and effective_end_date;
select pl_typ_id
from BEN_PL_F
where PL_ID = c_PL_ID ;
select pl_gd_or_svc_id
from BEN_PL_GD_OR_SVC_F
where PL_ID = c_PL_ID ;
select vgs.*
from BEN_PL_GD_OR_SVC_F vgs
where vgs.pl_gd_or_svc_id = c_pl_gd_or_svc_id
-- and vgs.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_PL_GD_OR_SVC_F'
and cpe.table_alias = c_table_alias
and information1 = c_pl_gd_or_svc_id
-- and information4 = vgs.business_group_id
and information2 = vgs.effective_start_date
and information3 = vgs.effective_end_date
);
select cwb_wksht_grp_id
from BEN_CWB_WKSHT_GRP
where PL_ID = c_PL_ID ;
select cwg.*
from BEN_CWB_WKSHT_GRP cwg
where cwg.cwb_wksht_grp_id = c_cwb_wksht_grp_id
and cwg.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
c_mirror_src_entity_result_id = c_mirror_src_entity_result_id )
-- and trt.where_clause = 'BEN_CWB_WKSHT_GRP'
and cpe.table_alias = c_table_alias
and information1 = c_cwb_wksht_grp_id
--and information4 = cwg.business_group_id
);
select cri.*
from BEN_CUSTOM_REGION_ITEMS cri
where cri.custom_key = to_char(c_cwb_wksht_grp_id)
and cri.custom_type like 'Cwb%PG'
and not exists (
select /*+ */ null
from ben_copy_entity_results cpe
where copy_entity_txn_id = p_copy_entity_txn_id
and (c_mirror_src_entity_result_id = c_mirror_src_entity_result_id )
and cpe.table_alias = c_table_alias
and information1 = c_cwb_wksht_grp_id
);
select vald_rlshp_for_reimb_id
from BEN_VALD_RLSHP_FOR_REIMB_F
where PL_ID = c_PL_ID ;
select vrp.*
from BEN_VALD_RLSHP_FOR_REIMB_F vrp
where vrp.vald_rlshp_for_reimb_id = c_vald_rlshp_for_reimb_id
-- and vrp.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_VALD_RLSHP_FOR_REIMB_F'
and cpe.table_alias = c_table_alias
and information1 = c_vald_rlshp_for_reimb_id
-- and information4 = vrp.business_group_id
and information2 = vrp.effective_start_date
and information3 = vrp.effective_end_date
);
select wv_prtn_rsn_pl_id
from BEN_WV_PRTN_RSN_PL_F
where PL_ID = c_PL_ID ;
select wpn.*
from BEN_WV_PRTN_RSN_PL_F wpn
where wpn.wv_prtn_rsn_pl_id = c_wv_prtn_rsn_pl_id
-- and wpn.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_WV_PRTN_RSN_PL_F'
and cpe.table_alias = c_table_alias
and information1 = c_wv_prtn_rsn_pl_id
-- and information4 = wpn.business_group_id
and information2 = wpn.effective_start_date
and information3 = wpn.effective_end_date
);
select bnft_rstrn_ctfn_id
from BEN_BNFT_RSTRN_CTFN_F
where PL_ID = c_PL_ID ;
select brc.*
from BEN_BNFT_RSTRN_CTFN_F brc
where brc.bnft_rstrn_ctfn_id = c_bnft_rstrn_ctfn_id
-- and brc.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_RSTRN_CTFN_F'
and cpe.table_alias = c_table_alias
and information1 = c_bnft_rstrn_ctfn_id
-- and information4 = brc.business_group_id
and information2 = brc.effective_start_date
and information3 = brc.effective_end_date
);
select distinct ler_bnft_rstrn_id
from BEN_LER_BNFT_RSTRN_F
where PL_ID = c_PL_ID ;
select lbr.*
from BEN_LER_BNFT_RSTRN_F lbr
where lbr.ler_bnft_rstrn_id = c_ler_bnft_rstrn_id
-- and lbr.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_LER_BNFT_RSTRN_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_bnft_rstrn_id
-- and information4 = lbr.business_group_id
and information2 = lbr.effective_start_date
and information3 = lbr.effective_end_date
);
select distinct cpe.information257 ler_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_LER_BNFT_RSTRN_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_bnft_rstrn_id
-- and information4 = p_business_group_id
;
select enrt_ctfn_id
from BEN_ENRT_CTFN_F
where PL_ID = c_PL_ID ;
select ecf.*
from BEN_ENRT_CTFN_F ecf
where ecf.enrt_ctfn_id = c_enrt_ctfn_id
-- and ecf.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_ENRT_CTFN_F'
and cpe.table_alias = c_table_alias
and information1 = c_enrt_ctfn_id
-- and information4 = ecf.business_group_id
and information2 = ecf.effective_start_date
and information3 = ecf.effective_end_date
);
select distinct ler_chg_dpnt_cvg_id
from BEN_LER_CHG_DPNT_CVG_F
where PL_ID = c_PL_ID ;
select ldc.*
from BEN_LER_CHG_DPNT_CVG_F ldc
where ldc.ler_chg_dpnt_cvg_id = c_ler_chg_dpnt_cvg_id
-- and ldc.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_LER_CHG_DPNT_CVG_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_chg_dpnt_cvg_id
-- and information4 = ldc.business_group_id
and information2 = ldc.effective_start_date
and information3 = ldc.effective_end_date
);
select distinct cpe.information257 ler_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_LER_CHG_DPNT_CVG_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_chg_dpnt_cvg_id
-- and information4 = p_business_group_id
;
select distinct ler_chg_pl_nip_enrt_id
from BEN_LER_CHG_PL_NIP_ENRT_F
where PL_ID = c_PL_ID ;
select lpe.*
from BEN_LER_CHG_PL_NIP_ENRT_F lpe
where lpe.ler_chg_pl_nip_enrt_id = c_ler_chg_pl_nip_enrt_id
-- and lpe.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_LER_CHG_PL_NIP_ENRT_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_chg_pl_nip_enrt_id
-- and information4 = lpe.business_group_id
and information2 = lpe.effective_start_date
and information3 = lpe.effective_end_date
);
select distinct cpe.information257 ler_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_LER_CHG_PL_NIP_ENRT_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_chg_pl_nip_enrt_id
-- and information4 = p_business_group_id
;
select distinct ler_rqrs_enrt_ctfn_id
from BEN_LER_RQRS_ENRT_CTFN_F
where PL_ID = c_PL_ID ;
select lre.*
from BEN_LER_RQRS_ENRT_CTFN_F lre
where lre.ler_rqrs_enrt_ctfn_id = c_ler_rqrs_enrt_ctfn_id
-- and lre.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_LER_RQRS_ENRT_CTFN_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_rqrs_enrt_ctfn_id
-- and information4 = lre.business_group_id
and information2 = lre.effective_start_date
and information3 = lre.effective_end_date
);
select distinct cpe.information257 ler_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_LER_RQRS_ENRT_CTFN_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_rqrs_enrt_ctfn_id
-- and information4 = p_business_group_id
;
select pl_pcp_id
from BEN_PL_PCP
where PL_ID = c_PL_ID ;
select pcp.*
from BEN_PL_PCP pcp
where pcp.pl_pcp_id = c_pl_pcp_id
-- and pcp.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_PL_PCP'
and cpe.table_alias = c_table_alias
and information1 = c_pl_pcp_id
-- and information4 = pcp.business_group_id
);
select pl_pcp_typ_id
from BEN_PL_PCP_TYP
where PL_PCP_ID = c_PL_PCP_ID ;
select pty.*
from BEN_PL_PCP_TYP pty
where pty.pl_pcp_typ_id = c_pl_pcp_typ_id
-- and pty.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_PL_PCP_TYP'
and cpe.table_alias = c_table_alias
and information1 = c_pl_pcp_typ_id
-- and information4 = pty.business_group_id
);
select pl_bnf_ctfn_id
from BEN_PL_BNF_CTFN_F
where PL_ID = c_PL_ID ;
select pcx.*
from BEN_PL_BNF_CTFN_F pcx
where pcx.pl_bnf_ctfn_id = c_pl_bnf_ctfn_id
-- and pcx.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_PL_BNF_CTFN_F'
and cpe.table_alias = c_table_alias
and information1 = c_pl_bnf_ctfn_id
-- and information4 = pcx.business_group_id
and information2 = pcx.effective_start_date
and information3 = pcx.effective_end_date
);
select pl_dpnt_cvg_ctfn_id
from BEN_PL_DPNT_CVG_CTFN_F
where PL_ID = c_PL_ID ;
select pnd.*
from BEN_PL_DPNT_CVG_CTFN_F pnd
where pnd.pl_dpnt_cvg_ctfn_id = c_pl_dpnt_cvg_ctfn_id
-- and pnd.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_PL_DPNT_CVG_CTFN_F'
and cpe.table_alias = c_table_alias
and information1 = c_pl_dpnt_cvg_ctfn_id
-- and information4 = pnd.business_group_id
and information2 = pnd.effective_start_date
and information3 = pnd.effective_end_date
);
select elig_to_prte_rsn_id
from BEN_ELIG_TO_PRTE_RSN_F
where PL_ID = c_PL_ID ;
select peo.*
from BEN_ELIG_TO_PRTE_RSN_F peo
where peo.elig_to_prte_rsn_id = c_elig_to_prte_rsn_id
-- and peo.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_TO_PRTE_RSN_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_to_prte_rsn_id
-- and information4 = peo.business_group_id
and information2 = peo.effective_start_date
and information3 = peo.effective_end_date
);
select gd_or_svc_typ_id
from BEN_PL_GD_OR_SVC_F
where PL_GD_OR_SVC_ID = c_PL_GD_OR_SVC_ID ;
select gos.*
from BEN_GD_OR_SVC_TYP gos
where gos.gd_or_svc_typ_id = c_gd_or_svc_typ_id
-- and gos.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_GD_OR_SVC_TYP'
and cpe.table_alias = c_table_alias
and information1 = c_gd_or_svc_typ_id
-- and information4 = gos.business_group_id
);
select pl_gd_r_svc_ctfn_id
from BEN_PL_GD_R_SVC_CTFN_F
where PL_GD_OR_SVC_ID = c_PL_GD_OR_SVC_ID ;
select pct.*
from BEN_PL_GD_R_SVC_CTFN_F pct
where pct.pl_gd_r_svc_ctfn_id = c_pl_gd_r_svc_ctfn_id
-- and pct.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_PL_GD_R_SVC_CTFN_F'
and cpe.table_alias = c_table_alias
and information1 = c_pl_gd_r_svc_ctfn_id
-- and information4 = pct.business_group_id
and information2 = pct.effective_start_date
and information3 = pct.effective_end_date
);
select pl_regn_id
from BEN_PL_REGN_F
where PL_ID = c_PL_ID ;
select prg.*
from BEN_PL_REGN_F prg
where prg.pl_regn_id = c_pl_regn_id
-- and prg.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_PL_REGN_F'
and cpe.table_alias = c_table_alias
and information1 = c_pl_regn_id
-- and information4 = prg.business_group_id
and information2 = prg.effective_start_date
and information3 = prg.effective_end_date
);
select regn_id
from BEN_PL_REGN_F
where PL_REGN_ID = c_PL_REGN_ID ;
select reg.*
from BEN_REGN_F reg
where reg.regn_id = c_regn_id
-- and reg.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_REGN_F'
and cpe.table_alias = c_table_alias
and information1 = c_regn_id
-- and information4 = reg.business_group_id
and information2 = reg.effective_start_date
and information3 = reg.effective_end_date
);
select rptg_grp_id
from BEN_PL_REGN_F
where PL_REGN_ID = c_PL_REGN_ID ;
select bnr.*
from BEN_RPTG_GRP bnr
where bnr.rptg_grp_id = c_rptg_grp_id
-- and bnr.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_RPTG_GRP'
and cpe.table_alias = c_table_alias
and information1 = c_rptg_grp_id
-- and information4 = bnr.business_group_id
);
select wv_prtn_rsn_ctfn_pl_id
from BEN_WV_PRTN_RSN_CTFN_PL_F
where WV_PRTN_RSN_PL_ID = c_WV_PRTN_RSN_PL_ID ;
select wcn.*
from BEN_WV_PRTN_RSN_CTFN_PL_F wcn
where wcn.wv_prtn_rsn_ctfn_pl_id = c_wv_prtn_rsn_ctfn_pl_id
-- and wcn.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_WV_PRTN_RSN_CTFN_PL_F'
and cpe.table_alias = c_table_alias
and information1 = c_wv_prtn_rsn_ctfn_pl_id
-- and information4 = wcn.business_group_id
and information2 = wcn.effective_start_date
and information3 = wcn.effective_end_date
);
select ler_bnft_rstrn_ctfn_id
from BEN_LER_BNFT_RSTRN_CTFN_F
where LER_BNFT_RSTRN_ID = c_LER_BNFT_RSTRN_ID ;
select lbc.*
from BEN_LER_BNFT_RSTRN_CTFN_F lbc
where lbc.ler_bnft_rstrn_ctfn_id = c_ler_bnft_rstrn_ctfn_id
-- and lbc.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_LER_BNFT_RSTRN_CTFN_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_bnft_rstrn_ctfn_id
-- and information4 = lbc.business_group_id
and information2 = lbc.effective_start_date
and information3 = lbc.effective_end_date
);
select ler_enrt_ctfn_id
from BEN_LER_ENRT_CTFN_F
where LER_RQRS_ENRT_CTFN_ID = c_LER_RQRS_ENRT_CTFN_ID ;
select lnc.*
from BEN_LER_ENRT_CTFN_F lnc
where lnc.ler_enrt_ctfn_id = c_ler_enrt_ctfn_id
-- and lnc.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_LER_ENRT_CTFN_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_enrt_ctfn_id
-- and information4 = lnc.business_group_id
and information2 = lnc.effective_start_date
and information3 = lnc.effective_end_date
);
select ler_chg_dpnt_cvg_ctfn_id
from BEN_LER_CHG_DPNT_CVG_CTFN_F
where LER_CHG_DPNT_CVG_ID = c_LER_CHG_DPNT_CVG_ID ;
select lcc.*
from BEN_LER_CHG_DPNT_CVG_CTFN_F lcc
where lcc.ler_chg_dpnt_cvg_ctfn_id = c_ler_chg_dpnt_cvg_ctfn_id
-- and lcc.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_LER_CHG_DPNT_CVG_CTFN_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_chg_dpnt_cvg_ctfn_id
-- and information4 = lcc.business_group_id
and information2 = lcc.effective_start_date
and information3 = lcc.effective_end_date
);
select dsgn_rqmt_id
from BEN_DSGN_RQMT_F
where PL_ID = c_PL_ID ;
select ddr.*
from BEN_DSGN_RQMT_F ddr
where ddr.dsgn_rqmt_id = c_dsgn_rqmt_id
-- and ddr.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_DSGN_RQMT_F'
and cpe.table_alias = c_table_alias
and information1 = c_dsgn_rqmt_id
-- and information4 = ddr.business_group_id
and information2 = ddr.effective_start_date
and information3 = ddr.effective_end_date
);
select dsgn_rqmt_rlshp_typ_id
from BEN_DSGN_RQMT_RLSHP_TYP
where DSGN_RQMT_ID = c_DSGN_RQMT_ID ;
select drr.*
from BEN_DSGN_RQMT_RLSHP_TYP drr
where drr.dsgn_rqmt_rlshp_typ_id = c_dsgn_rqmt_rlshp_typ_id
-- and drr.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_DSGN_RQMT_RLSHP_TYP'
and cpe.table_alias = c_table_alias
and information1 = c_dsgn_rqmt_rlshp_typ_id
-- and information4 = drr.business_group_id
);
select distinct oipl_id
from BEN_OIPL_F
where PL_ID = c_PL_ID ;
select pl_regn_id
from BEN_PL_REGN_F
where RPTG_GRP_ID = c_RPTG_GRP_ID ;
select prg.*
from BEN_PL_REGN_F prg
where prg.pl_regn_id = c_pl_regn_id
-- and prg.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_PL_REGN_F'
and cpe.table_alias = c_table_alias
and information1 = c_pl_regn_id
-- and information4 = prg.business_group_id
and information2 = prg.effective_start_date
and information3 = prg.effective_end_date
);
select pl_regy_bod_id
from BEN_PL_REGY_BOD_F
where RPTG_GRP_ID = c_RPTG_GRP_ID ;
select prb.*
from BEN_PL_REGY_BOD_F prb
where prb.pl_regy_bod_id = c_pl_regy_bod_id
-- and prb.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_PL_REGY_BOD_F'
and cpe.table_alias = c_table_alias
and information1 = c_pl_regy_bod_id
-- and information4 = prb.business_group_id
and information2 = prb.effective_start_date
and information3 = prb.effective_end_date
);
select pl_regy_prps_id
from BEN_PL_REGY_PRP_F
where PL_REGY_BOD_ID = c_PL_REGY_BOD_ID ;
select prp.*
from BEN_PL_REGY_PRP_F prp
where prp.pl_regy_prps_id = c_pl_regy_prps_id
-- and prp.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_PL_REGY_PRP_F'
and cpe.table_alias = c_table_alias
and information1 = c_pl_regy_prps_id
-- and information4 = prp.business_group_id
and information2 = prp.effective_start_date
and information3 = prp.effective_end_date
);
select popl_rptg_grp_id
from BEN_POPL_RPTG_GRP_F
where RPTG_GRP_ID = c_RPTG_GRP_ID ;
select rgr.*
from BEN_POPL_RPTG_GRP_F rgr
where rgr.popl_rptg_grp_id = c_popl_rptg_grp_id
-- and rgr.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_POPL_RPTG_GRP_F'
and cpe.table_alias = c_table_alias
and information1 = c_popl_rptg_grp_id
-- and information4 = rgr.business_group_id
and information2 = rgr.effective_start_date
and information3 = rgr.effective_end_date
);
select regn_id
from BEN_PL_REGN_F
where PL_REGN_ID = c_PL_REGN_ID ;
select reg.*
from BEN_REGN_F reg
where reg.regn_id = c_regn_id
-- and reg.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_REGN_F'
and cpe.table_alias = c_table_alias
and information1 = c_regn_id
-- and information4 = reg.business_group_id
and information2 = reg.effective_start_date
and information3 = reg.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;
p_information16 =>l_cri_rec.update_attr,
p_information17 =>l_cri_rec.monetary,
p_information141 =>l_cri_rec.label,
p_information265 =>l_cri_rec.object_version_number,
p_information266 =>l_cri_rec.ordr_num,
p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date );
update ben_copy_entity_results
set information8 = NULL
where information1 = p_pl_id
and copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'PLN';
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 trt.from_clause = 'OAB'
-- and trt.where_clause = upper(c_parent_table_name) ;
select popl_actn_typ_id
from BEN_POPL_ACTN_TYP_F
-- where PL_ID = c_PL_ID ;
select pat.*
from BEN_POPL_ACTN_TYP_F pat
where pat.popl_actn_typ_id = c_popl_actn_typ_id
-- and pat.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_POPL_ACTN_TYP_F'
and cpe.table_alias = c_table_alias
and information1 = c_popl_actn_typ_id
-- and information4 = pat.business_group_id
and information2 = pat.effective_start_date
and information3 = pat.effective_end_date
);
select distinct popl_enrt_typ_cycl_id
from BEN_POPL_ENRT_TYP_CYCL_F
where ( c_PL_ID is not null and c_PL_ID = PL_ID ) or
( c_PGM_ID is not null and c_PGM_ID = PGM_ID);
select pet.*
from BEN_POPL_ENRT_TYP_CYCL_F pet
where pet.popl_enrt_typ_cycl_id = c_popl_enrt_typ_cycl_id
-- and pet.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_POPL_ENRT_TYP_CYCL_F'
and cpe.table_alias = c_table_alias
and information1 = c_popl_enrt_typ_cycl_id
-- and information4 = pet.business_group_id
and information2 = pet.effective_start_date
and information3 = pet.effective_end_date
);
select popl_org_id
from BEN_POPL_ORG_F
--where PL_ID = c_PL_ID ;
select cpo.*
from BEN_POPL_ORG_F cpo
where cpo.popl_org_id = c_popl_org_id
-- and cpo.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_POPL_ORG_F'
and cpe.table_alias = c_table_alias
and information1 = c_popl_org_id
-- and information4 = cpo.business_group_id
and information2 = cpo.effective_start_date
and information3 = cpo.effective_end_date
);
select org.name
from hr_organization_units org
where business_group_id = p_business_group_id
and organization_id = p_id
and p_date between Date_from
and nvl(Date_to, p_date) ;
select date_from
from hr_all_organization_units
where organization_id = c_organization_id;
select popl_yr_perd_id
from BEN_POPL_YR_PERD
--where PL_ID = c_PL_ID ;
select cpy.*
from BEN_POPL_YR_PERD cpy
where cpy.popl_yr_perd_id = c_popl_yr_perd_id
-- and cpy.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_POPL_YR_PERD'
and cpe.table_alias = c_table_alias
and information1 = c_popl_yr_perd_id
-- and information4 = cpy.business_group_id
);
select popl_rptg_grp_id
from BEN_POPL_RPTG_GRP_F
-- where PL_ID = c_PL_ID ;
select rgr.*
from BEN_POPL_RPTG_GRP_F rgr
where rgr.popl_rptg_grp_id = c_popl_rptg_grp_id
-- and rgr.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_POPL_RPTG_GRP_F'
and cpe.table_alias = c_table_alias
and information1 = c_popl_rptg_grp_id
-- and information4 = rgr.business_group_id
and information2 = rgr.effective_start_date
and information3 = rgr.effective_end_date
);
select distinct yr_perd_id
from BEN_POPL_YR_PERD
where POPL_YR_PERD_ID = c_POPL_YR_PERD_ID ;
select popl_org_role_id
from BEN_POPL_ORG_ROLE_F
where POPL_ORG_ID = c_POPL_ORG_ID ;
select cpr.*
from BEN_POPL_ORG_ROLE_F cpr
where cpr.popl_org_role_id = c_popl_org_role_id
-- and cpr.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_POPL_ORG_ROLE_F'
and cpe.table_alias = c_table_alias
and information1 = c_popl_org_role_id
-- and information4 = cpr.business_group_id
and information2 = cpr.effective_start_date
and information3 = cpr.effective_end_date
);
select rptg_grp_id
from BEN_POPL_RPTG_GRP_F
where POPL_RPTG_GRP_ID = c_POPL_RPTG_GRP_ID ;
select bnr.*
from BEN_RPTG_GRP bnr
where bnr.rptg_grp_id = c_rptg_grp_id
-- and bnr.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_RPTG_GRP'
and cpe.table_alias = c_table_alias
and information1 = c_rptg_grp_id
-- and information4 = bnr.business_group_id
);
select distinct lee_rsn_id
from BEN_LEE_RSN_F
where POPL_ENRT_TYP_CYCL_ID = c_POPL_ENRT_TYP_CYCL_ID ;
select len.*
from BEN_LEE_RSN_F len
where len.lee_rsn_id = c_lee_rsn_id
-- and len.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_LEE_RSN_F'
and cpe.table_alias = c_table_alias
and information1 = c_lee_rsn_id
-- and information4 = len.business_group_id
and information2 = len.effective_start_date
and information3 = len.effective_end_date
);
select distinct cpe.information257 ler_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_LEE_RSN_F'
and trt.table_alias = c_table_alias
and information1 = c_lee_rsn_id
-- and information4 = p_business_group_id
;
select enrt_perd_id
from BEN_ENRT_PERD
where POPL_ENRT_TYP_CYCL_ID = c_POPL_ENRT_TYP_CYCL_ID ;
select enp.*
from BEN_ENRT_PERD enp
where enp.enrt_perd_id = c_enrt_perd_id
-- and enp.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_ENRT_PERD'
and cpe.table_alias = c_table_alias
and information1 = c_enrt_perd_id
-- and information4 = enp.business_group_id
);
select pos.NAME name
from PER_POSITION_STRUCTURES pos,
PER_POS_STRUCTURE_VERSIONS pov
where pos.business_group_id = p_business_group_id
and POS_STRUCTURE_VERSION_ID = p_id
and pos.POSITION_STRUCTURE_ID = pov.POSITION_STRUCTURE_ID
and p_date between nvl(POV.DATE_FROM, p_date)
and nvl(POV.DATE_TO, p_date);
select date_from
from per_pos_structure_versions
where pos_structure_version_id = c_pos_structure_version_id;
select distinct yr_perd_id
from BEN_ENRT_PERD
where ENRT_PERD_ID = c_ENRT_PERD_ID ;
select ERP.enrt_perd_for_pl_id
from BEN_ENRT_PERD_FOR_PL_F ERP
where ENRT_PERD_ID = c_ENRT_PERD_ID
and exists (select null
from ben_plip_f plip
where plip.pl_id = ERP.pl_id
and plip.pgm_id = c_pgm_id
and ERP.effective_start_date between
plip.effective_start_date and plip.effective_end_date);
select erp.*
from BEN_ENRT_PERD_FOR_PL_F erp
where erp.enrt_perd_for_pl_id = c_enrt_perd_for_pl_id
-- and erp.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_ENRT_PERD_FOR_PL_F'
and cpe.table_alias = c_table_alias
and information1 = c_enrt_perd_for_pl_id
-- and information4 = erp.business_group_id
and information2 = erp.effective_start_date
and information3 = erp.effective_end_date
);
select schedd_enrt_rl_id
from BEN_SCHEDD_ENRT_RL_F
where ENRT_PERD_ID = c_ENRT_PERD_ID ;
select ser.*
from BEN_SCHEDD_ENRT_RL_F ser
where ser.schedd_enrt_rl_id = c_schedd_enrt_rl_id
-- and ser.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_ENRT_RL_F'
and cpe.table_alias = c_table_alias
and information1 = c_schedd_enrt_rl_id
-- and information4 = ser.business_group_id
and information2 = ser.effective_start_date
and information3 = ser.effective_end_date
);
select lee_rsn_rl_id
from BEN_LEE_RSN_RL_F
where LEE_RSN_ID = c_LEE_RSN_ID ;
select lrr.*
from BEN_LEE_RSN_RL_F lrr
where lrr.lee_rsn_rl_id = c_lee_rsn_rl_id
-- and lrr.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_LEE_RSN_RL_F'
and cpe.table_alias = c_table_alias
and information1 = c_lee_rsn_rl_id
-- and information4 = lrr.business_group_id
and information2 = lrr.effective_start_date
and information3 = lrr.effective_end_date
);
select enrt_perd_for_pl_id
from BEN_ENRT_PERD_FOR_PL_F ERP
where LEE_RSN_ID = c_LEE_RSN_ID
and exists (select null
from ben_plip_f plip
where plip.pl_id = ERP.pl_id
and plip.pgm_id = c_pgm_id
and ERP.effective_start_date between
plip.effective_start_date and plip.effective_end_date);
select erp.*
from BEN_ENRT_PERD_FOR_PL_F erp
where erp.enrt_perd_for_pl_id = c_enrt_perd_for_pl_id
-- and erp.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_ENRT_PERD_FOR_PL_F'
and cpe.table_alias = c_table_alias
and information1 = c_enrt_perd_for_pl_id
-- and information4 = erp.business_group_id
and information2 = erp.effective_start_date
and information3 = erp.effective_end_date
);
select actn_typ_id
from BEN_POPL_ACTN_TYP_F
where POPL_ACTN_TYP_ID = c_POPL_ACTN_TYP_ID ;
select eat.*
from BEN_ACTN_TYP eat
where eat.actn_typ_id = c_actn_typ_id
-- and eat.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_ACTN_TYP'
and cpe.table_alias = c_table_alias
and information1 = c_actn_typ_id
-- and information4 = eat.business_group_id
);
select pl_regn_id
from BEN_PL_REGN_F
where RPTG_GRP_ID = c_RPTG_GRP_ID ;
select prg.*
from BEN_PL_REGN_F prg
where prg.pl_regn_id = c_pl_regn_id
-- and prg.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_PL_REGN_F'
and cpe.table_alias = c_table_alias
and information1 = c_pl_regn_id
-- and information4 = prg.business_group_id
and information2 = prg.effective_start_date
and information3 = prg.effective_end_date
);
select pl_regy_bod_id
from BEN_PL_REGY_BOD_F
where RPTG_GRP_ID = c_RPTG_GRP_ID ;
select prb.*
from BEN_PL_REGY_BOD_F prb
where prb.pl_regy_bod_id = c_pl_regy_bod_id
-- and prb.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_PL_REGY_BOD_F'
and cpe.table_alias = c_table_alias
and information1 = c_pl_regy_bod_id
-- and information4 = prb.business_group_id
and information2 = prb.effective_start_date
and information3 = prb.effective_end_date
);
select pl_regy_prps_id
from BEN_PL_REGY_PRP_F
where PL_REGY_BOD_ID = c_PL_REGY_BOD_ID ;
select prp.*
from BEN_PL_REGY_PRP_F prp
where prp.pl_regy_prps_id = c_pl_regy_prps_id
-- and prp.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_PL_REGY_PRP_F'
and cpe.table_alias = c_table_alias
and information1 = c_pl_regy_prps_id
-- and information4 = prp.business_group_id
and information2 = prp.effective_start_date
and information3 = prp.effective_end_date
);
select popl_rptg_grp_id
from BEN_POPL_RPTG_GRP_F
where RPTG_GRP_ID = c_RPTG_GRP_ID ;
select rgr.*
from BEN_POPL_RPTG_GRP_F rgr
where rgr.popl_rptg_grp_id = c_popl_rptg_grp_id
-- and rgr.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_POPL_RPTG_GRP_F'
and cpe.table_alias = c_table_alias
and information1 = c_popl_rptg_grp_id
-- and information4 = rgr.business_group_id
and information2 = rgr.effective_start_date
and information3 = rgr.effective_end_date
);
select regn_id
from BEN_PL_REGN_F
where PL_REGN_ID = c_PL_REGN_ID ;
select reg.*
from BEN_REGN_F reg
where reg.regn_id = c_regn_id
-- and reg.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_REGN_F'
and cpe.table_alias = c_table_alias
and information1 = c_regn_id
-- and information4 = reg.business_group_id
and information2 = reg.effective_start_date
and information3 = reg.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 = 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 ler.*
from BEN_LER_F ler
where ler.ler_id = c_ler_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_LER_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_id
-- and information4 = ler.business_group_id
and information2 = ler.effective_start_date
and information3 = ler.effective_end_date
);
select css_rltd_per_per_in_ler_id
from BEN_CSS_RLTD_PER_PER_IN_LER_F
where LER_ID = c_LER_ID ;
select csr.*
from BEN_CSS_RLTD_PER_PER_IN_LER_F csr
where csr.css_rltd_per_per_in_ler_id = c_css_rltd_per_per_in_ler_id
-- and csr.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_CSS_RLTD_PER_PER_IN_LER_F'
and cpe.table_alias = c_table_alias
and information1 = c_css_rltd_per_per_in_ler_id
-- and information4 = csr.business_group_id
and information2 = csr.effective_start_date
and information3 = csr.effective_end_date
);
select ler_per_info_cs_ler_id
from BEN_LER_PER_INFO_CS_LER_F
where LER_ID = c_LER_ID
and ( exists ( select 'x'
from ben_ler_f
where LER_ID = c_LER_ID
and typ_cd = 'ABS') OR
nvl(p_no_dup_rslt, 'X') = 'Y'); -- For plan design wizard copy lpl also
select lpl.*
from BEN_LER_PER_INFO_CS_LER_F lpl
where lpl.ler_per_info_cs_ler_id = c_ler_per_info_cs_ler_id
-- and lpl.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_LER_PER_INFO_CS_LER_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_per_info_cs_ler_id
-- and information4 = lpl.business_group_id
and information2 = lpl.effective_start_date
and information3 = lpl.effective_end_date
);
select ler_rltd_per_cs_ler_id
from BEN_LER_RLTD_PER_CS_LER_F
where LER_ID = c_LER_ID ;
select lrc.*
from BEN_LER_RLTD_PER_CS_LER_F lrc
where lrc.ler_rltd_per_cs_ler_id = c_ler_rltd_per_cs_ler_id
-- and lrc.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_LER_RLTD_PER_CS_LER_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_rltd_per_cs_ler_id
-- and information4 = lrc.business_group_id
and information2 = lrc.effective_start_date
and information3 = lrc.effective_end_date
);
select psl.per_info_chg_cs_ler_id
from BEN_LER_PER_INFO_CS_LER_F lpl,
BEN_PER_INFO_CHG_CS_LER_F psl
where LER_PER_INFO_CS_LER_ID = c_LER_PER_INFO_CS_LER_ID
and psl.PER_INFO_CHG_CS_LER_id = lpl.PER_INFO_CHG_CS_LER_id
and source_table = 'PER_ABSENCE_ATTENDANCES';
select psl.*
from BEN_PER_INFO_CHG_CS_LER_F psl
where psl.per_info_chg_cs_ler_id = c_per_info_chg_cs_ler_id
-- and psl.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_INFO_CHG_CS_LER_F'
and cpe.table_alias = c_table_alias
and information1 = c_per_info_chg_cs_ler_id
-- and information4 = psl.business_group_id
and information2 = psl.effective_start_date
and information3 = psl.effective_end_date
);
SELECT distinct hl.meaning
FROM hr_lookups hl,
per_abs_attendance_reasons abs
WHERE hl.lookup_type = 'ABSENCE_REASON'
AND hl.lookup_code = abs.name
AND hl.lookup_code = cv_lookup_code
AND hl.enabled_flag = 'Y'
AND abs.business_group_id = p_business_group_id
AND cv_effective_date between nvl(start_date_active,cv_effective_date)
and nvl(end_date_active,cv_effective_date);
SELECT distinct name
from PER_ABSENCE_ATTENDANCE_TYPES
WHERE business_group_id = p_business_group_id
and to_char(absence_attendance_type_id) = cv_val;
select rltd_per_chg_cs_ler_id
from BEN_LER_RLTD_PER_CS_LER_F
where LER_RLTD_PER_CS_LER_ID = c_LER_RLTD_PER_CS_LER_ID ;
select rcl.*
from BEN_RLTD_PER_CHG_CS_LER_F rcl
where rcl.rltd_per_chg_cs_ler_id = c_rltd_per_chg_cs_ler_id
-- and rcl.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_RLTD_PER_CHG_CS_LER_F'
and cpe.table_alias = c_table_alias
and information1 = c_rltd_per_chg_cs_ler_id
-- and information4 = rcl.business_group_id
and information2 = rcl.effective_start_date
and information3 = rcl.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 = l_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 cop.*
from BEN_OIPL_F cop
where cop.oipl_id = c_oipl_id
-- and cop.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_OIPL_F'
and cpe.table_alias = c_table_alias
and information1 = c_oipl_id
-- and information4 = cop.business_group_id
and information2 = cop.effective_start_date
and information3 = cop.effective_end_date
);
select dsgn_rqmt_id
from BEN_DSGN_RQMT_F
where OIPL_ID = c_OIPL_ID ;
select ddr.*
from BEN_DSGN_RQMT_F ddr
where ddr.dsgn_rqmt_id = c_dsgn_rqmt_id
-- and ddr.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_DSGN_RQMT_F'
and cpe.table_alias = c_table_alias
and information1 = c_dsgn_rqmt_id
-- and information4 = ddr.business_group_id
and information2 = ddr.effective_start_date
and information3 = ddr.effective_end_date
);
select dsgn_rqmt_rlshp_typ_id
from BEN_DSGN_RQMT_RLSHP_TYP
where DSGN_RQMT_ID = c_DSGN_RQMT_ID ;
select drr.*
from BEN_DSGN_RQMT_RLSHP_TYP drr
where drr.dsgn_rqmt_rlshp_typ_id = c_dsgn_rqmt_rlshp_typ_id
-- and drr.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_DSGN_RQMT_RLSHP_TYP'
and cpe.table_alias = c_table_alias
and information1 = c_dsgn_rqmt_rlshp_typ_id
-- and information4 = drr.business_group_id
);
select elig_to_prte_rsn_id
from BEN_ELIG_TO_PRTE_RSN_F
where OIPL_ID = c_OIPL_ID ;
select peo.*
from BEN_ELIG_TO_PRTE_RSN_F peo
where peo.elig_to_prte_rsn_id = c_elig_to_prte_rsn_id
-- and peo.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_TO_PRTE_RSN_F'
and cpe.table_alias = c_table_alias
and information1 = c_elig_to_prte_rsn_id
-- and information4 = peo.business_group_id
and information2 = peo.effective_start_date
and information3 = peo.effective_end_date
);
select distinct ler_chg_oipl_enrt_id
from BEN_LER_CHG_OIPL_ENRT_F
where OIPL_ID = c_OIPL_ID ;
select lop.*
from BEN_LER_CHG_OIPL_ENRT_F lop
where lop.ler_chg_oipl_enrt_id = c_ler_chg_oipl_enrt_id
-- and lop.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_LER_CHG_OIPL_ENRT_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_chg_oipl_enrt_id
-- and information4 = lop.business_group_id
and information2 = lop.effective_start_date
and information3 = lop.effective_end_date
);
select distinct cpe.information257 ler_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_LER_CHG_OIPL_ENRT_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_chg_oipl_enrt_id
-- and information4 = p_business_group_id
;
select enrt_ctfn_id
from BEN_ENRT_CTFN_F
where OIPL_ID = c_OIPL_ID ;
select ecf.*
from BEN_ENRT_CTFN_F ecf
where ecf.enrt_ctfn_id = c_enrt_ctfn_id
-- and ecf.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_ENRT_CTFN_F'
and cpe.table_alias = c_table_alias
and information1 = c_enrt_ctfn_id
-- and information4 = ecf.business_group_id
and information2 = ecf.effective_start_date
and information3 = ecf.effective_end_date
);
select distinct ler_rqrs_enrt_ctfn_id
from BEN_LER_RQRS_ENRT_CTFN_F
where OIPL_ID = c_OIPL_ID ;
select lre.*
from BEN_LER_RQRS_ENRT_CTFN_F lre
where lre.ler_rqrs_enrt_ctfn_id = c_ler_rqrs_enrt_ctfn_id
-- and lre.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_LER_RQRS_ENRT_CTFN_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_rqrs_enrt_ctfn_id
-- and information4 = lre.business_group_id
and information2 = lre.effective_start_date
and information3 = lre.effective_end_date
);
select distinct cpe.information257 ler_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_LER_RQRS_ENRT_CTFN_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_rqrs_enrt_ctfn_id
-- and information4 = p_business_group_id
;
select ler_enrt_ctfn_id
from BEN_LER_ENRT_CTFN_F
where LER_RQRS_ENRT_CTFN_ID = c_LER_RQRS_ENRT_CTFN_ID ;
select lnc.*
from BEN_LER_ENRT_CTFN_F lnc
where lnc.ler_enrt_ctfn_id = c_ler_enrt_ctfn_id
-- and lnc.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_LER_ENRT_CTFN_F'
and cpe.table_alias = c_table_alias
and information1 = c_ler_enrt_ctfn_id
-- and information4 = lnc.business_group_id
and information2 = lnc.effective_start_date
and information3 = lnc.effective_end_date
);
select distinct opt_id
from BEN_OIPL_F
where OIPL_ID = c_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_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 opt.*
from BEN_OPT_F opt
where opt.opt_id = c_opt_id
-- and opt.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_OPT_F'
and cpe.table_alias = c_table_alias
and information1 = c_opt_id
-- and information4 = opt.business_group_id
and information2 = opt.effective_start_date
and information3 = opt.effective_end_date
);
select name group_opt_name
from ben_opt_f
where opt_id = p_group_opt_id
and p_effective_date between effective_start_date and effective_end_date;
select dsgn_rqmt_id
from BEN_DSGN_RQMT_F
where OPT_ID = c_OPT_ID ;
select ddr.*
from BEN_DSGN_RQMT_F ddr
where ddr.dsgn_rqmt_id = c_dsgn_rqmt_id
-- and ddr.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_DSGN_RQMT_F'
and cpe.table_alias = c_table_alias
and information1 = c_dsgn_rqmt_id
-- and information4 = ddr.business_group_id
and information2 = ddr.effective_start_date
and information3 = ddr.effective_end_date
);
select pl_typ_opt_typ_id
from BEN_PL_TYP_OPT_TYP_F
where OPT_ID = c_OPT_ID ;
select pon.*
from BEN_PL_TYP_OPT_TYP_F pon
where pon.pl_typ_opt_typ_id = c_pl_typ_opt_typ_id
-- and pon.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_PL_TYP_OPT_TYP_F'
and cpe.table_alias = c_table_alias
and information1 = c_pl_typ_opt_typ_id
-- and information4 = pon.business_group_id
and information2 = pon.effective_start_date
and information3 = pon.effective_end_date
);
select dsgn_rqmt_rlshp_typ_id
from BEN_DSGN_RQMT_RLSHP_TYP
where DSGN_RQMT_ID = c_DSGN_RQMT_ID ;
select drr.*
from BEN_DSGN_RQMT_RLSHP_TYP drr
where drr.dsgn_rqmt_rlshp_typ_id = c_dsgn_rqmt_rlshp_typ_id
-- and drr.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_DSGN_RQMT_RLSHP_TYP'
and cpe.table_alias = c_table_alias
and information1 = c_dsgn_rqmt_rlshp_typ_id
-- and information4 = drr.business_group_id
);
select distinct pl_typ_id
from BEN_PL_TYP_OPT_TYP_F
where PL_TYP_OPT_TYP_ID = c_PL_TYP_OPT_TYP_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_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 = l_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 ptp.*
from BEN_PL_TYP_F ptp
where ptp.pl_typ_id = c_pl_typ_id
-- and ptp.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_PL_TYP_F'
and cpe.table_alias = c_table_alias
and information1 = c_pl_typ_id
-- and information4 = ptp.business_group_id
and information2 = ptp.effective_start_date
and information3 = ptp.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 = l_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 yrp.*
from BEN_YR_PERD yrp
where yrp.yr_perd_id = c_yr_perd_id
-- and yrp.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_YR_PERD'
and cpe.table_alias = c_table_alias
and information1 = c_yr_perd_id
-- and information4 = yrp.business_group_id
);
select wthn_yr_perd_id
from BEN_WTHN_YR_PERD
where YR_PERD_ID = c_YR_PERD_ID ;
select wyp.*
from BEN_WTHN_YR_PERD wyp
where wyp.wthn_yr_perd_id = c_wthn_yr_perd_id
-- and wyp.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_WTHN_YR_PERD'
and cpe.table_alias = c_table_alias
and information1 = c_wthn_yr_perd_id
-- and information4 = wyp.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;