[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select *
from ben_dsgn_rqmt_f ddr
where (ddr.oipl_id = p_oipl_id
or ddr.pl_id = p_pl_id
or ddr.opt_id = p_opt_id)
and ddr.dsgn_typ_cd = 'DPNT'
and ddr.business_group_id = p_business_group_id
and nvl(l_fonm_cvg_strt_dt,nvl(p_lf_evt_ocrd_dt,p_effective_date))
between ddr.effective_start_date
and ddr.effective_end_date;
select *
from ben_dsgn_rqmt_rlshp_typ drr
where drr.dsgn_rqmt_id = p_dsgn_rqmt_id
and drr.business_group_id = p_business_group_id;
select null
from per_contact_relationships ctr,
per_all_people_f ppf
where ctr.person_id = p_person_id
and ctr.personal_flag = 'Y'
and nvl(l_fonm_cvg_strt_dt,nvl(p_lf_evt_ocrd_dt, p_effective_date))
between nvl(ctr.date_start, hr_api.g_sot)
and nvl(ctr.date_end, hr_api.g_eot)
and ctr.business_group_id = p_business_group_id
and ctr.contact_person_id = ppf.person_id
and nvl(l_fonm_cvg_strt_dt,nvl(p_lf_evt_ocrd_dt, p_effective_date))
between ppf.effective_start_date
and ppf.effective_end_date
and (ctr.contact_type in
(select rlshp_typ_cd drr
from ben_dsgn_rqmt_rlshp_typ drr
where drr.dsgn_rqmt_id = p_dsgn_rqmt_id
and drr.business_group_id = p_business_group_id)
or not exists (select null
from ben_dsgn_rqmt_rlshp_typ drr
where drr.dsgn_rqmt_id = p_dsgn_rqmt_id
and drr.business_group_id = p_business_group_id))
having count(*) >= nvl(p_mn_dpnts_rqd_num, 0);
select 'W'
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id = v_per_in_ler_id
and ler.ler_id = pil.ler_id
and ler.typ_cd not in ('COMP', 'ABS', 'SCHEDDU', 'GSP', 'IREC') -- iRec
and v_effective_date
between ler.effective_start_date
and ler.effective_end_date ;
select *
from ben_elig_per_f
where elig_per_id = v_elig_per_id
and effective_start_date = v_effective_date
and nvl(per_in_ler_id , -1) = v_per_in_ler_id ;
select *
from ben_elig_per_opt_f
where elig_per_opt_id = v_elig_per_opt_id
and nvl(per_in_ler_id , -1) = v_per_in_ler_id
and effective_start_date = v_effective_date;
hr_utility.set_location('Inserting into BEN_LE_CLSN_N_RSTR PEP: '||p_elig_per_id,20);
insert into BEN_LE_CLSN_N_RSTR (
PER_IN_LER_ENDED_ID,
BKUP_TBL_TYP_CD,
PLIP_ID,
PTIP_ID,
WAIT_PERD_CMPLTN_DT,
PER_IN_LER_ID,
RT_FRZ_PCT_FL_TM_FLAG,
RT_FRZ_HRS_WKD_FLAG,
RT_FRZ_COMB_AGE_AND_LOS_FLAG,
ONCE_R_CNTUG_CD,
BKUP_TBL_ID, -- ELIG_PER_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
PL_ID,
PGM_ID,
LER_ID,
PERSON_ID,
DPNT_OTHR_PL_CVRD_RL_FLAG,
PRTN_OVRIDN_THRU_DT,
PL_KEY_EE_FLAG,
PL_HGHLY_COMPD_FLAG,
ELIG_FLAG,
COMP_REF_AMT,
CMBN_AGE_N_LOS_VAL,
COMP_REF_UOM,
AGE_VAL,
LOS_VAL,
PRTN_END_DT,
PRTN_STRT_DT,
WV_CTFN_TYP_CD,
HRS_WKD_VAL,
HRS_WKD_BNDRY_PERD_CD,
PRTN_OVRIDN_FLAG,
NO_MX_PRTN_OVRID_THRU_FLAG,
PRTN_OVRIDN_RSN_CD,
AGE_UOM,
LOS_UOM,
OVRID_SVC_DT,
FRZ_LOS_FLAG,
FRZ_AGE_FLAG,
FRZ_CMP_LVL_FLAG,
FRZ_PCT_FL_TM_FLAG,
FRZ_HRS_WKD_FLAG,
FRZ_COMB_AGE_AND_LOS_FLAG,
DSTR_RSTCN_FLAG,
PCT_FL_TM_VAL,
WV_PRTN_RSN_CD,
PL_WVD_FLAG,
LCR_ATTRIBUTE_CATEGORY,
LCR_ATTRIBUTE1,
LCR_ATTRIBUTE2,
LCR_ATTRIBUTE3,
LCR_ATTRIBUTE4,
LCR_ATTRIBUTE5,
LCR_ATTRIBUTE6,
LCR_ATTRIBUTE7,
LCR_ATTRIBUTE8,
LCR_ATTRIBUTE9,
LCR_ATTRIBUTE10,
LCR_ATTRIBUTE11,
LCR_ATTRIBUTE12,
LCR_ATTRIBUTE13,
LCR_ATTRIBUTE14,
LCR_ATTRIBUTE15,
LCR_ATTRIBUTE16,
LCR_ATTRIBUTE17,
LCR_ATTRIBUTE18,
LCR_ATTRIBUTE19,
LCR_ATTRIBUTE20,
LCR_ATTRIBUTE21,
LCR_ATTRIBUTE22,
LCR_ATTRIBUTE23,
LCR_ATTRIBUTE24,
LCR_ATTRIBUTE25,
LCR_ATTRIBUTE26,
LCR_ATTRIBUTE27,
LCR_ATTRIBUTE28,
LCR_ATTRIBUTE29,
LCR_ATTRIBUTE30,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
MUST_ENRL_ANTHR_PL_ID,
RT_COMP_REF_AMT,
RT_CMBN_AGE_N_LOS_VAL,
RT_COMP_REF_UOM,
RT_AGE_VAL,
RT_LOS_VAL,
RT_HRS_WKD_VAL,
RT_HRS_WKD_BNDRY_PERD_CD,
RT_AGE_UOM,
RT_LOS_UOM,
RT_PCT_FL_TM_VAL,
RT_FRZ_LOS_FLAG,
RT_FRZ_AGE_FLAG,
RT_FRZ_CMP_LVL_FLAG,
INELG_RSN_CD,
PL_ORDR_NUM,
PLIP_ORDR_NUM,
PTIP_ORDR_NUM )
values (
p_current_per_in_ler_id,
'BEN_ELIG_PER_F_CORRECT',
l_upd_pep_rec.PLIP_ID,
l_upd_pep_rec.PTIP_ID,
l_upd_pep_rec.WAIT_PERD_CMPLTN_DT,
l_upd_pep_rec.PER_IN_LER_ID,
l_upd_pep_rec.RT_FRZ_PCT_FL_TM_FLAG,
l_upd_pep_rec.RT_FRZ_HRS_WKD_FLAG,
l_upd_pep_rec.RT_FRZ_COMB_AGE_AND_LOS_FLAG,
l_upd_pep_rec.ONCE_R_CNTUG_CD,
l_upd_pep_rec.ELIG_PER_ID,
l_upd_pep_rec.EFFECTIVE_START_DATE,
l_upd_pep_rec.EFFECTIVE_END_DATE,
l_upd_pep_rec.BUSINESS_GROUP_ID,
l_upd_pep_rec.PL_ID,
l_upd_pep_rec.PGM_ID,
l_upd_pep_rec.LER_ID,
l_upd_pep_rec.PERSON_ID,
l_upd_pep_rec.DPNT_OTHR_PL_CVRD_RL_FLAG,
l_upd_pep_rec.PRTN_OVRIDN_THRU_DT,
l_upd_pep_rec.PL_KEY_EE_FLAG,
l_upd_pep_rec.PL_HGHLY_COMPD_FLAG,
l_upd_pep_rec.ELIG_FLAG,
l_upd_pep_rec.COMP_REF_AMT,
l_upd_pep_rec.CMBN_AGE_N_LOS_VAL,
l_upd_pep_rec.COMP_REF_UOM,
l_upd_pep_rec.AGE_VAL,
l_upd_pep_rec.LOS_VAL,
l_upd_pep_rec.PRTN_END_DT,
l_upd_pep_rec.PRTN_STRT_DT,
l_upd_pep_rec.WV_CTFN_TYP_CD,
l_upd_pep_rec.HRS_WKD_VAL,
l_upd_pep_rec.HRS_WKD_BNDRY_PERD_CD,
l_upd_pep_rec.PRTN_OVRIDN_FLAG,
l_upd_pep_rec.NO_MX_PRTN_OVRID_THRU_FLAG,
l_upd_pep_rec.PRTN_OVRIDN_RSN_CD,
l_upd_pep_rec.AGE_UOM,
l_upd_pep_rec.LOS_UOM,
l_upd_pep_rec.OVRID_SVC_DT,
l_upd_pep_rec.FRZ_LOS_FLAG,
l_upd_pep_rec.FRZ_AGE_FLAG,
l_upd_pep_rec.FRZ_CMP_LVL_FLAG,
l_upd_pep_rec.FRZ_PCT_FL_TM_FLAG,
l_upd_pep_rec.FRZ_HRS_WKD_FLAG,
l_upd_pep_rec.FRZ_COMB_AGE_AND_LOS_FLAG,
l_upd_pep_rec.DSTR_RSTCN_FLAG,
l_upd_pep_rec.PCT_FL_TM_VAL,
l_upd_pep_rec.WV_PRTN_RSN_CD,
l_upd_pep_rec.PL_WVD_FLAG,
l_upd_pep_rec.PEP_ATTRIBUTE_CATEGORY,
l_upd_pep_rec.PEP_ATTRIBUTE1,
l_upd_pep_rec.PEP_ATTRIBUTE2,
l_upd_pep_rec.PEP_ATTRIBUTE3,
l_upd_pep_rec.PEP_ATTRIBUTE4,
l_upd_pep_rec.PEP_ATTRIBUTE5,
l_upd_pep_rec.PEP_ATTRIBUTE6,
l_upd_pep_rec.PEP_ATTRIBUTE7,
l_upd_pep_rec.PEP_ATTRIBUTE8,
l_upd_pep_rec.PEP_ATTRIBUTE9,
l_upd_pep_rec.PEP_ATTRIBUTE10,
l_upd_pep_rec.PEP_ATTRIBUTE11,
l_upd_pep_rec.PEP_ATTRIBUTE12,
l_upd_pep_rec.PEP_ATTRIBUTE13,
l_upd_pep_rec.PEP_ATTRIBUTE14,
l_upd_pep_rec.PEP_ATTRIBUTE15,
l_upd_pep_rec.PEP_ATTRIBUTE16,
l_upd_pep_rec.PEP_ATTRIBUTE17,
l_upd_pep_rec.PEP_ATTRIBUTE18,
l_upd_pep_rec.PEP_ATTRIBUTE19,
l_upd_pep_rec.PEP_ATTRIBUTE20,
l_upd_pep_rec.PEP_ATTRIBUTE21,
l_upd_pep_rec.PEP_ATTRIBUTE22,
l_upd_pep_rec.PEP_ATTRIBUTE23,
l_upd_pep_rec.PEP_ATTRIBUTE24,
l_upd_pep_rec.PEP_ATTRIBUTE25,
l_upd_pep_rec.PEP_ATTRIBUTE26,
l_upd_pep_rec.PEP_ATTRIBUTE27,
l_upd_pep_rec.PEP_ATTRIBUTE28,
l_upd_pep_rec.PEP_ATTRIBUTE29,
l_upd_pep_rec.PEP_ATTRIBUTE30,
l_upd_pep_rec.LAST_UPDATE_DATE,
l_upd_pep_rec.LAST_UPDATED_BY,
l_upd_pep_rec.LAST_UPDATE_LOGIN,
l_upd_pep_rec.CREATED_BY,
l_upd_pep_rec.CREATION_DATE,
l_upd_pep_rec.REQUEST_ID,
l_upd_pep_rec.PROGRAM_APPLICATION_ID,
l_upd_pep_rec.PROGRAM_ID,
l_upd_pep_rec.PROGRAM_UPDATE_DATE,
l_upd_pep_rec.OBJECT_VERSION_NUMBER,
l_upd_pep_rec.MUST_ENRL_ANTHR_PL_ID,
l_upd_pep_rec.RT_COMP_REF_AMT,
l_upd_pep_rec.RT_CMBN_AGE_N_LOS_VAL,
l_upd_pep_rec.RT_COMP_REF_UOM,
l_upd_pep_rec.RT_AGE_VAL,
l_upd_pep_rec.RT_LOS_VAL,
l_upd_pep_rec.RT_HRS_WKD_VAL,
l_upd_pep_rec.RT_HRS_WKD_BNDRY_PERD_CD,
l_upd_pep_rec.RT_AGE_UOM,
l_upd_pep_rec.RT_LOS_UOM,
l_upd_pep_rec.RT_PCT_FL_TM_VAL,
l_upd_pep_rec.RT_FRZ_LOS_FLAG,
l_upd_pep_rec.RT_FRZ_AGE_FLAG,
l_upd_pep_rec.RT_FRZ_CMP_LVL_FLAG,
l_upd_pep_rec.INELG_RSN_CD,
l_upd_pep_rec.PL_ORDR_NUM,
l_upd_pep_rec.PLIP_ORDR_NUM,
l_upd_pep_rec.PTIP_ORDR_NUM
);
hr_utility.set_location('Inserting into BEN_LE_CLSN_N_RSTR EPO: '||p_elig_per_opt_id,20);
insert into BEN_LE_CLSN_N_RSTR (
PER_IN_LER_ENDED_ID,
BKUP_TBL_TYP_CD,
INELG_RSN_CD,
PER_IN_LER_ID,
AGE_UOM,
LOS_UOM,
FRZ_LOS_FLAG,
FRZ_AGE_FLAG,
FRZ_CMP_LVL_FLAG,
FRZ_PCT_FL_TM_FLAG,
FRZ_HRS_WKD_FLAG,
FRZ_COMB_AGE_AND_LOS_FLAG,
OVRID_SVC_DT,
WAIT_PERD_CMPLTN_DT,
COMP_REF_AMT,
CMBN_AGE_N_LOS_VAL,
COMP_REF_UOM,
AGE_VAL,
LOS_VAL,
HRS_WKD_VAL,
HRS_WKD_BNDRY_PERD_CD,
RT_COMP_REF_AMT,
RT_CMBN_AGE_N_LOS_VAL,
RT_COMP_REF_UOM,
RT_AGE_VAL,
RT_LOS_VAL,
RT_HRS_WKD_VAL,
RT_HRS_WKD_BNDRY_PERD_CD,
RT_AGE_UOM,
RT_LOS_UOM,
RT_PCT_FL_TM_VAL,
RT_FRZ_LOS_FLAG,
RT_FRZ_AGE_FLAG,
RT_FRZ_CMP_LVL_FLAG,
RT_FRZ_PCT_FL_TM_FLAG,
RT_FRZ_HRS_WKD_FLAG,
RT_FRZ_COMB_AGE_AND_LOS_FLAG,
BKUP_TBL_ID, -- ELIG_PER_OPT_ID,
ELIG_PER_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
PRTN_OVRIDN_FLAG,
PRTN_OVRIDN_THRU_DT,
NO_MX_PRTN_OVRID_THRU_FLAG,
ELIG_FLAG,
PRTN_STRT_DT,
PRTN_OVRIDN_RSN_CD,
PCT_FL_TM_VAL,
OPT_ID,
BUSINESS_GROUP_ID,
LCR_ATTRIBUTE_CATEGORY,
LCR_ATTRIBUTE1,
LCR_ATTRIBUTE2,
LCR_ATTRIBUTE3,
LCR_ATTRIBUTE4,
LCR_ATTRIBUTE5,
LCR_ATTRIBUTE6,
LCR_ATTRIBUTE7,
LCR_ATTRIBUTE8,
LCR_ATTRIBUTE9,
LCR_ATTRIBUTE10,
LCR_ATTRIBUTE11,
LCR_ATTRIBUTE12,
LCR_ATTRIBUTE13,
LCR_ATTRIBUTE14,
LCR_ATTRIBUTE15,
LCR_ATTRIBUTE16,
LCR_ATTRIBUTE17,
LCR_ATTRIBUTE18,
LCR_ATTRIBUTE19,
LCR_ATTRIBUTE20,
LCR_ATTRIBUTE21,
LCR_ATTRIBUTE22,
LCR_ATTRIBUTE23,
LCR_ATTRIBUTE24,
LCR_ATTRIBUTE25,
LCR_ATTRIBUTE26,
LCR_ATTRIBUTE27,
LCR_ATTRIBUTE28,
LCR_ATTRIBUTE29,
LCR_ATTRIBUTE30,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
ONCE_R_CNTUG_CD,
OIPL_ORDR_NUM,
PRTN_END_DT )
values (
p_current_per_in_ler_id,
'BEN_ELIG_PER_OPT_F_CORRECT',
l_upd_epo_rec.INELG_RSN_CD,
l_upd_epo_rec.PER_IN_LER_ID,
l_upd_epo_rec.AGE_UOM,
l_upd_epo_rec.LOS_UOM,
l_upd_epo_rec.FRZ_LOS_FLAG,
l_upd_epo_rec.FRZ_AGE_FLAG,
l_upd_epo_rec.FRZ_CMP_LVL_FLAG,
l_upd_epo_rec.FRZ_PCT_FL_TM_FLAG,
l_upd_epo_rec.FRZ_HRS_WKD_FLAG,
l_upd_epo_rec.FRZ_COMB_AGE_AND_LOS_FLAG,
l_upd_epo_rec.OVRID_SVC_DT,
-- l_upd_epo_rec.WAIT_PERD_CMPLTN_DT,
l_upd_epo_rec.WAIT_PERD_CMPLTN_DATE,
l_upd_epo_rec.COMP_REF_AMT,
l_upd_epo_rec.CMBN_AGE_N_LOS_VAL,
l_upd_epo_rec.COMP_REF_UOM,
l_upd_epo_rec.AGE_VAL,
l_upd_epo_rec.LOS_VAL,
l_upd_epo_rec.HRS_WKD_VAL,
l_upd_epo_rec.HRS_WKD_BNDRY_PERD_CD,
l_upd_epo_rec.RT_COMP_REF_AMT,
l_upd_epo_rec.RT_CMBN_AGE_N_LOS_VAL,
l_upd_epo_rec.RT_COMP_REF_UOM,
l_upd_epo_rec.RT_AGE_VAL,
l_upd_epo_rec.RT_LOS_VAL,
l_upd_epo_rec.RT_HRS_WKD_VAL,
l_upd_epo_rec.RT_HRS_WKD_BNDRY_PERD_CD,
l_upd_epo_rec.RT_AGE_UOM,
l_upd_epo_rec.RT_LOS_UOM,
l_upd_epo_rec.RT_PCT_FL_TM_VAL,
l_upd_epo_rec.RT_FRZ_LOS_FLAG,
l_upd_epo_rec.RT_FRZ_AGE_FLAG,
l_upd_epo_rec.RT_FRZ_CMP_LVL_FLAG,
l_upd_epo_rec.RT_FRZ_PCT_FL_TM_FLAG,
l_upd_epo_rec.RT_FRZ_HRS_WKD_FLAG,
l_upd_epo_rec.RT_FRZ_COMB_AGE_AND_LOS_FLAG,
l_upd_epo_rec.ELIG_PER_OPT_ID,
l_upd_epo_rec.ELIG_PER_ID,
l_upd_epo_rec.EFFECTIVE_START_DATE,
l_upd_epo_rec.EFFECTIVE_END_DATE,
l_upd_epo_rec.PRTN_OVRIDN_FLAG,
l_upd_epo_rec.PRTN_OVRIDN_THRU_DT,
l_upd_epo_rec.NO_MX_PRTN_OVRID_THRU_FLAG,
l_upd_epo_rec.ELIG_FLAG,
l_upd_epo_rec.PRTN_STRT_DT,
l_upd_epo_rec.PRTN_OVRIDN_RSN_CD,
l_upd_epo_rec.PCT_FL_TM_VAL,
l_upd_epo_rec.OPT_ID,
l_upd_epo_rec.BUSINESS_GROUP_ID,
l_upd_epo_rec.EPO_ATTRIBUTE_CATEGORY,
l_upd_epo_rec.EPO_ATTRIBUTE1,
l_upd_epo_rec.EPO_ATTRIBUTE2,
l_upd_epo_rec.EPO_ATTRIBUTE3,
l_upd_epo_rec.EPO_ATTRIBUTE4,
l_upd_epo_rec.EPO_ATTRIBUTE5,
l_upd_epo_rec.EPO_ATTRIBUTE6,
l_upd_epo_rec.EPO_ATTRIBUTE7,
l_upd_epo_rec.EPO_ATTRIBUTE8,
l_upd_epo_rec.EPO_ATTRIBUTE9,
l_upd_epo_rec.EPO_ATTRIBUTE10,
l_upd_epo_rec.EPO_ATTRIBUTE11,
l_upd_epo_rec.EPO_ATTRIBUTE12,
l_upd_epo_rec.EPO_ATTRIBUTE13,
l_upd_epo_rec.EPO_ATTRIBUTE14,
l_upd_epo_rec.EPO_ATTRIBUTE15,
l_upd_epo_rec.EPO_ATTRIBUTE16,
l_upd_epo_rec.EPO_ATTRIBUTE17,
l_upd_epo_rec.EPO_ATTRIBUTE18,
l_upd_epo_rec.EPO_ATTRIBUTE19,
l_upd_epo_rec.EPO_ATTRIBUTE20,
l_upd_epo_rec.EPO_ATTRIBUTE21,
l_upd_epo_rec.EPO_ATTRIBUTE22,
l_upd_epo_rec.EPO_ATTRIBUTE23,
l_upd_epo_rec.EPO_ATTRIBUTE24,
l_upd_epo_rec.EPO_ATTRIBUTE25,
l_upd_epo_rec.EPO_ATTRIBUTE26,
l_upd_epo_rec.EPO_ATTRIBUTE27,
l_upd_epo_rec.EPO_ATTRIBUTE28,
l_upd_epo_rec.EPO_ATTRIBUTE29,
l_upd_epo_rec.EPO_ATTRIBUTE30,
l_upd_epo_rec.LAST_UPDATE_DATE,
l_upd_epo_rec.LAST_UPDATED_BY,
l_upd_epo_rec.LAST_UPDATE_LOGIN,
l_upd_epo_rec.CREATED_BY,
l_upd_epo_rec.CREATION_DATE,
l_upd_epo_rec.REQUEST_ID,
l_upd_epo_rec.PROGRAM_APPLICATION_ID,
l_upd_epo_rec.PROGRAM_ID,
l_upd_epo_rec.PROGRAM_UPDATE_DATE,
l_upd_epo_rec.OBJECT_VERSION_NUMBER,
l_upd_epo_rec.ONCE_R_CNTUG_CD,
l_upd_epo_rec.OIPL_ORDR_NUM,
l_upd_epo_rec.PRTN_END_DT
);