[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select name
into l_scl_name
from per_parent_spines
where parent_spine_id = p_scl_id;
select information255,substr(information98,1,30),substr(information5,1,61)
into l_scl_id,l_point_name,l_opt_name
from ben_copy_entity_results
where copy_entity_result_id = p_opt_cer_id;
update ben_copy_entity_results
set information279 = p_elp_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CRRATE'
and information160 = p_crset_id;
hr_utility.set_location('num of crrs updated'||sql%rowcount,20);
select eligy_prfl_rl_id,object_version_number
into l_pk,l_ovn
from BEN_ELIGY_PRFL_RL_F
where eligy_prfl_id = p_elig_prfl_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('pk selected is'||l_pk,100);
BEN_ELIGY_PROFILE_RULE_API.delete_ELIGY_PROFILE_RULE(
p_eligy_prfl_rl_id => l_pk
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_delete
);
select elig_perf_rtng_prte_id,object_version_number
into l_pk,l_ovn
from BEN_ELIG_PERF_RTNG_PRTE_F
where eligy_prfl_id = p_elig_prfl_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('pk selected is'||l_pk,100);
BEN_ELIG_PERF_RTNG_PRTE_API.delete_ELIG_PERF_RTNG_PRTE(
p_elig_perf_rtng_prte_id => l_pk
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_delete
);
select elig_per_typ_prte_id,object_version_number
into l_pk,l_ovn
from BEN_ELIG_PER_TYP_PRTE_F
where eligy_prfl_id = p_elig_prfl_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('pk selected is'||l_pk,100);
BEN_ELIG_PER_TYP_PRTE_API.delete_ELIG_PER_TYP_PRTE(
p_elig_per_typ_prte_id => l_pk
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_delete
);
select elig_fl_tm_pt_tm_prte_id,object_version_number
into l_pk,l_ovn
from BEN_ELIG_FL_TM_PT_TM_PRTE_F
where eligy_prfl_id = p_elig_prfl_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('pk selected is'||l_pk,100);
BEN_ELIG_FL_TM_PT_TM_PRTE_API.delete_ELIG_FL_TM_PT_TM_PRTE(
p_elig_fl_tm_pt_tm_prte_id => l_pk
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_delete
);
select elig_brgng_unit_prte_id,object_version_number
into l_pk,l_ovn
from BEN_ELIG_BRGNG_UNIT_PRTE_F
where eligy_prfl_id = p_elig_prfl_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('pk selected is'||l_pk,100);
BEN_ELIG_BRGNG_UNIT_PRTE_API.delete_ELIG_BRGNG_UNIT_PRTE(
p_elig_brgng_unit_prte_id => l_pk
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_delete
);
select elig_svc_area_prte_id,object_version_number
into l_pk,l_ovn
from BEN_ELIG_SVC_AREA_PRTE_F
where eligy_prfl_id = p_elig_prfl_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('pk selected is'||l_pk,100);
BEN_ELIG_SVC_AREA_PRTE_API.delete_ELIG_SVC_AREA_PRTE(
p_elig_svc_area_prte_id => l_pk
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_delete
);
select elig_wk_loc_prte_id,object_version_number
into l_pk,l_ovn
from BEN_ELIG_WK_LOC_PRTE_F
where eligy_prfl_id = p_elig_prfl_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('pk selected is'||l_pk,100);
BEN_ELIG_WK_LOC_PRTE_API.delete_ELIG_WK_LOC_PRTE(
p_elig_wk_loc_prte_id => l_pk
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_delete
);
select elig_org_unit_prte_id,object_version_number
into l_pk,l_ovn
from BEN_ELIG_ORG_UNIT_PRTE_F
where eligy_prfl_id = p_elig_prfl_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('pk selected is'||l_pk,100);
BEN_ELIG_ORG_UNIT_PRTE_API.delete_ELIG_ORG_UNIT_PRTE(
p_elig_org_unit_prte_id => l_pk
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_delete
);
select elig_job_prte_id,object_version_number
into l_pk,l_ovn
from BEN_ELIG_JOB_PRTE_F
where eligy_prfl_id = p_elig_prfl_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('pk selected is'||l_pk,100);
BEN_ELIGY_JOB_PRTE_API.delete_ELIGY_JOB_PRTE(
p_elig_job_prte_id => l_pk
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_delete
);
select system_person_type
into l_per_typ_cd
from per_person_types
where person_type_id = P_PERSON_TYPE_ID;
select substr(information151,1,150)
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CRSET'
and information161 = p_crset_id
order by information2;
select substr(information102,1,30)
into l_grd_name
from ben_copy_entity_results
where copy_entity_result_id = p_grade_cer_id;
hr_utility.set_location('issue in selecting grd_name',11);
query_str := 'select object_version_number from '
|| p_table_name
|| ' where '
|| p_key_column_name || '= :key_column_value' ;
function get_update_mode(p_table_name varchar2,
p_key_column_name varchar2,
p_key_column_value number,
p_effective_date in date) return varchar2 is
query_str varchar2(2000);
query_str := 'select min(effective_start_date) from '
|| p_table_name
|| ' where '
|| p_key_column_name || '= :key_column_value'
|| ' and effective_start_date >= :effective_date';
l_dt_mode := 'UPDATE';
l_dt_mode := 'UPDATE_OVERRIDE';
end get_update_mode;
select oipl_id
into l_oipl_id
from ben_oipl_f
where pl_id = p_pl_id
and opt_id = p_opt_id
and p_effective_date between effective_start_date and effective_end_date;
select pl_typ_id
from ben_pl_typ_f
where opt_typ_cd ='GSP'
and business_group_id = p_business_group_id
and pl_typ_stat_cd ='A'
and p_effective_date between effective_start_date and effective_end_date;
update ben_copy_entity_results
set information1 = p_plip_id
where copy_entity_result_id = p_plip_cer_id;
hr_utility.set_location('num of plips updated'||sql%rowcount,20);
update ben_copy_entity_results
set information261 = p_pl_id
where gs_parent_entity_result_id = p_plip_cer_id
and table_alias ='COP'
and copy_entity_txn_id = p_copy_entity_txn_id;
hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
update ben_copy_entity_results
set information1 = p_plip_id
where copy_entity_result_id = p_plip_cer_id;
hr_utility.set_location('num of epas updated'||sql%rowcount,20);
update ben_copy_entity_results
set information1 = p_oipl_id
where copy_entity_result_id = p_oipl_cer_id;
hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
update ben_copy_entity_results
set information247 = p_opt_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'COP'
and information262 = p_opt_cer_id;
hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
update ben_copy_entity_results
set information247 = p_opt_id,
information170 = p_opt_name
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'ABR'
and information278 = p_opt_cer_id;
hr_utility.set_location('num of opts updated'||sql%rowcount,20);
update ben_copy_entity_results
set information261 = p_pl_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CPP'
and copy_entity_result_id = p_plip_cer_id;
hr_utility.set_location('num of plips updated'||sql%rowcount,20);
update ben_copy_entity_results
set information261 = p_pl_id,
information170 = p_pl_name
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'ABR'
and information277 = p_pl_cer_id;
hr_utility.set_location('num of abrs updated'||sql%rowcount,20);
update ben_copy_entity_results
set information261 = p_pl_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'COP'
and gs_parent_entity_result_id = p_plip_cer_id;
hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
update ben_copy_entity_results
set information1 = p_pl_id
where copy_entity_result_id = p_pl_cer_id;
hr_utility.set_location('num of plans updated'||sql%rowcount,20);
update ben_copy_entity_results
set information260 = p_pgm_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CPP';
hr_utility.set_location('num of plips updated'||sql%rowcount,20);
update ben_copy_entity_results
set information260 = p_pgm_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CPD';
hr_utility.set_location('num of cpd updated'||sql%rowcount,20);
update ben_copy_entity_results
set information1 = p_pgm_id
where copy_entity_result_id = p_pgm_cer_id;
hr_utility.set_location('num of pgms updated'||sql%rowcount,20);
select ler_id
into l_ler_id
from ben_ler_f
where typ_cd = 'GSP'
and lf_evt_oper_cd = p_oper_code
and business_group_id = p_bg_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('issues in selecting ler',2);
hr_utility.set_location('After per insert ',2);
select popl_enrt_typ_cycl_id
into l_pet_id
from ben_popl_enrt_typ_cycl_f
where pgm_id = p_pgm_id
and p_effective_date between effective_start_date and effective_end_date;
update ben_lee_rsn_f
set ENRT_CVG_STRT_DT_CD = p_pro_cvg_st_dt,
RT_STRT_DT_CD = p_pro_rt_st_dt
where POPL_ENRT_TYP_CYCL_ID = l_pet_id
and ler_id = l_ler_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('prog le enrl updated ',4);
update ben_lee_rsn_f
set ENRT_CVG_STRT_DT_CD = p_syn_rt_st_dt,
RT_STRT_DT_CD = p_syn_rt_st_dt
where POPL_ENRT_TYP_CYCL_ID = l_pet_id
and ler_id = l_ler_id
and p_effective_date between effective_start_date and effective_end_date;
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'PGI'
and dml_operation in ('INSERT','UPDATE') ;-- only insert/ updates should be there
hr_utility.set_location('inside pgm_extra_info_update',10);
SELECT information1, information5, information2
INTO l_pgm_id, l_pgm_name, l_pgm_esd
FROM ben_copy_entity_results
WHERE copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'PGM'
and result_type_cd='DISPLAY';
hr_utility.set_location('insert pgm extra info ',10);
hr_utility.set_location('update pgm extra info',10);
ben_pgm_extra_info_api.update_pgm_extra_info
( p_pgm_extra_info_id => r_pgi.information174
,p_object_version_number => l_peit_ovn
,p_pgi_information1 => r_pgi.information11
,p_pgi_information2 => r_pgi.information12
,p_pgi_information3 => r_pgi.information13
,p_pgi_information4 => r_pgi.information14
);
hr_utility.set_location('leaving pgm_extra_info_update',10);
hr_utility.set_location('elp row update',30);
hr_utility.set_location('opt row updated',40);
hr_utility.set_location('plan row updated',50);
hr_utility.set_location('pgm row updated',60);
hr_utility.set_location('pgi row updated',70);
hr_utility.set_location('cpd row updated',60);
hr_utility.set_location('oipl row updated',70);
hr_utility.set_location('plip row updated',70);
hr_utility.set_location('abr row updated',70);
hr_utility.set_location('var row updated',70);
hr_utility.set_location('epa row updated',70);
hr_utility.set_location('cep row updated',70);
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'OPT'
and dml_operation <> 'REUSE' ;
if r_opt.dml_operation in ('INSERT','COPIED','UPD_INS') then
l_effective_date := pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
if r_opt.dml_operation in ('INSERT','COPIED','UPD_INS')
and l_opt_id is null
and r_opt.information257 is not null
and l_opt_name is not null then
-- option may have been created thru step api. get the opt id
l_opt_id := pqh_gsp_hr_to_stage.get_opt_for_point
(p_point_id => r_opt.information257,
p_effective_date => l_effective_date);
and r_opt.dml_operation = 'UPDATE'
and r_opt.information257 is not null
and l_opt_name is not null then
hr_utility.set_location(' BEN_OPT_F UPDATE_OPTION_DEFINITION ',30);
l_dt_mode := get_update_mode('BEN_OPT_F','OPT_ID', l_opt_id, l_effective_date) ;
BEN_OPTION_DEFINITION_API.UPDATE_OPTION_DEFINITION(
P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_business_group_id
,P_COMPONENT_REASON => r_OPT.INFORMATION13
,P_INVK_WV_OPT_FLAG => r_OPT.INFORMATION14
,P_MAPPING_TABLE_NAME => r_OPT.INFORMATION141
,P_MAPPING_TABLE_PK_ID => r_opt.information257
,P_NAME => l_opt_name
,P_OPT_ID => l_opt_id
,P_RQD_PERD_ENRT_NENRT_RL => ''
,P_RQD_PERD_ENRT_NENRT_UOM => r_OPT.INFORMATION15
,P_RQD_PERD_ENRT_NENRT_VAL => r_OPT.INFORMATION259
,P_SHORT_CODE => r_OPT.INFORMATION11
,P_SHORT_NAME => r_OPT.INFORMATION12
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_ovn
,P_DATETRACK_MODE => l_dt_mode
);
elsif r_opt.dml_operation in ('DELETE') then
hr_utility.set_location('nothing needs to be done',100);
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'PLN'
and dml_operation <> 'REUSE' ;
if r_pln.dml_operation in ('INSERT','COPIED','UPD_INS') then
l_effective_date := pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
if l_pl_id is null and r_pln.dml_operation in ('INSERT','COPIED','UPD_INS')
and r_pln.information294 is not null then
hr_utility.set_location(' BEN_PL_F CREATE_PLAN ',4);
hr_utility.set_location('After pl insert'||l_pl_id,12);
elsif r_pln.dml_operation = 'UPDATE' and l_pl_id is not null
and r_pln.information294 is not null then
hr_utility.set_location(' BEN_PL_F UPDATE_PLAN ',30);
l_dt_mode := get_update_mode(p_table_name => 'BEN_PL_F',
p_key_column_name => 'PL_ID',
p_key_column_value => l_pl_id,
p_effective_date => l_effective_date);
BEN_PLAN_API.UPDATE_PLAN(
P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_business_group_id
,P_NAME => l_pl_name
,P_SHORT_CODE => r_PLN.INFORMATION93
,P_SHORT_NAME => r_PLN.INFORMATION94
,P_PL_ID => l_pl_id
,P_RT_STRT_DT_RL => ''
,P_VRFY_FMLY_MMBR_RL => ''
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_ovn
,P_DATETRACK_MODE => l_dt_mode
);
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'PGM'
and dml_operation in ('INSERT','UPDATE') ;-- only insert/ updates should be there
l_sal_upd_cd := 'NO_UPDATE' ;
if l_pgm_id is null and r_pgm.dml_operation = 'INSERT' then
hr_utility.set_location('dflt_step '||l_dflt_step_cd,1);
,P_UPDATE_SALARY_CD => l_sal_upd_cd
,P_ENRT_CD => 'CCKCNCC'
,P_DFLT_STEP_RL => r_PGM.INFORMATION259
,P_DPNT_ADRS_RQD_FLAG => nvl(r_PGM.INFORMATION21,'N')
,P_DPNT_CVG_END_DT_CD => r_PGM.INFORMATION43
,P_DPNT_CVG_END_DT_RL => r_PGM.INFORMATION269
,P_DPNT_CVG_STRT_DT_CD => r_PGM.INFORMATION44
,P_DPNT_CVG_STRT_DT_RL => r_PGM.INFORMATION268
,P_DPNT_DOB_RQD_FLAG => nvl(r_PGM.INFORMATION23,'N')
,P_DPNT_DSGN_CD => r_PGM.INFORMATION40
,P_DPNT_DSGN_LVL_CD => r_PGM.INFORMATION37
,P_DPNT_DSGN_NO_CTFN_RQD_FLAG => nvl(r_PGM.INFORMATION31,'N')
,P_DPNT_LEGV_ID_RQD_FLAG => nvl(r_PGM.INFORMATION25,'N')
,P_DRVBL_FCTR_APLS_RTS_FLAG => nvl(r_PGM.INFORMATION34,'N')
,P_DRVBL_FCTR_DPNT_ELIG_FLAG => nvl(r_PGM.INFORMATION32,'N')
,P_DRVBL_FCTR_PRTN_ELIG_FLAG => nvl(r_PGM.INFORMATION33,'N')
,P_ELIG_APLS_FLAG => nvl(r_PGM.INFORMATION26,'N')
,P_ENRT_CVG_END_DT_CD => 'ODBED'
,P_ENRT_CVG_END_DT_RL => r_PGM.INFORMATION266
,P_ENRT_CVG_STRT_DT_CD => nvl(r_PGM.INFORMATION45,'AED')
,P_ENRT_CVG_STRT_DT_RL => r_PGM.INFORMATION267
,P_ENRT_INFO_RT_FREQ_CD => 'MO'
,P_ENRT_MTHD_CD => r_PGM.INFORMATION52
,P_ENRT_RL => r_PGM.INFORMATION273
,P_IVR_IDENT => r_PGM.INFORMATION141
,P_MX_DPNT_PCT_PRTT_LF_AMT => r_PGM.INFORMATION287
,P_MX_SPS_PCT_PRTT_LF_AMT => r_PGM.INFORMATION288
,P_NAME => r_PGM.INFORMATION170
,P_PER_CVRD_CD => r_PGM.INFORMATION20
,P_PGM_DESC => r_PGM.INFORMATION219
,P_PGM_GRP_CD => r_PGM.INFORMATION49
,P_PGM_ID => l_pgm_id
,P_PGM_PRVDS_NO_AUTO_ENRT_FLAG => nvl(r_PGM.INFORMATION22,'N')
,P_PGM_PRVDS_NO_DFLT_ENRT_FLAG => nvl(r_PGM.INFORMATION24,'N')
,P_PGM_TYP_CD => r_PGM.INFORMATION39
,P_PGM_UOM => r_PGM.INFORMATION50
,P_PGM_USE_ALL_ASNTS_ELIG_FLAG => nvl(r_PGM.INFORMATION29,'N')
-- ,P_POE_LVL_CD => r_PGM.INFORMATION53
,P_PRTN_ELIG_OVRID_ALWD_FLAG => nvl(r_PGM.INFORMATION28,'N')
,P_RT_END_DT_CD => nvl(r_PGM.INFORMATION48,'ODBED')
,P_RT_END_DT_RL => r_PGM.INFORMATION271
,P_RT_STRT_DT_CD => nvl(r_PGM.INFORMATION47,'AED')
,P_RT_STRT_DT_RL => r_PGM.INFORMATION270
,P_SCORES_CALC_MTHD_CD => r_PGM.INFORMATION15
,P_SCORES_CALC_RL => r_PGM.INFORMATION261
,P_SHORT_CODE => r_PGM.INFORMATION11
,P_SHORT_NAME => r_PGM.INFORMATION12
,P_TRK_INELIG_PER_FLAG => nvl(r_PGM.INFORMATION35,'N')
,P_URL_REF_NAME => r_PGM.INFORMATION185
,P_USES_ALL_ASMTS_FOR_RTS_FLAG => nvl(r_PGM.INFORMATION27,'N')
,P_USE_MULTI_PAY_RATES_FLAG => nvl(r_PGM.INFORMATION17,'N')
,P_USE_PROG_POINTS_FLAG => nvl(r_PGM.INFORMATION18,'N')
,P_USE_SCORES_CD => r_PGM.INFORMATION19
,P_VRFY_FMLY_MMBR_CD => r_PGM.INFORMATION54
,P_VRFY_FMLY_MMBR_RL => r_PGM.INFORMATION274
,P_USE_VARIABLE_RATES_FLAG => NVL(r_PGM.INFORMATION69,'N')
,P_SALARY_CALC_MTHD_CD => r_PGM.INFORMATION70
,P_GSP_ALLOW_OVERRIDE_FLAG => NVL(r_PGM.INFORMATION72,'N')
,P_SALARY_CALC_MTHD_RL => r_PGM.INFORMATION293
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_ovn
);
hr_utility.set_location('After pgm insert '||l_pgm_id,1);
elsif r_pgm.dml_operation ='UPDATE' and l_pgm_id is not null then
hr_utility.set_location(' BEN_PGM_F UPDATE_PROGRAM ',30);
l_dt_mode := get_update_mode(p_table_name => 'BEN_PGM_F',
p_key_column_name => 'PGM_ID',
p_key_column_value => l_pgm_id,
p_effective_date => l_effective_date);
BEN_PROGRAM_API.UPDATE_PROGRAM(
P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_business_group_id
,P_ENRT_CD => 'CCKCNCC'
,P_ACTY_REF_PERD_CD => r_PGM.INFORMATION41
,P_ALWS_UNRSTRCTD_ENRT_FLAG => r_PGM.INFORMATION36
,P_AUTO_ENRT_MTHD_RL => r_PGM.INFORMATION272
,P_COORD_CVG_FOR_ALL_PLS_FLG => r_PGM.INFORMATION30
,P_DFLT_ELEMENT_TYPE_ID => r_PGM.INFORMATION257
,P_DFLT_INPUT_VALUE_ID => r_PGM.INFORMATION258
,P_DFLT_PGM_FLAG => r_PGM.INFORMATION13
,P_DFLT_STEP_CD => l_dflt_step_cd
,P_DFLT_STEP_RL => r_PGM.INFORMATION259
,P_DPNT_ADRS_RQD_FLAG => r_PGM.INFORMATION21
,P_DPNT_CVG_END_DT_CD => r_PGM.INFORMATION43
,P_DPNT_CVG_END_DT_RL => r_PGM.INFORMATION269
,P_DPNT_CVG_STRT_DT_CD => r_PGM.INFORMATION44
,P_DPNT_CVG_STRT_DT_RL => r_PGM.INFORMATION268
,P_DPNT_DOB_RQD_FLAG => r_PGM.INFORMATION23
,P_DPNT_DSGN_CD => r_PGM.INFORMATION40
,P_DPNT_DSGN_LVL_CD => r_PGM.INFORMATION37
,P_DPNT_DSGN_NO_CTFN_RQD_FLAG => r_PGM.INFORMATION31
,P_DPNT_LEGV_ID_RQD_FLAG => r_PGM.INFORMATION25
,P_DRVBL_FCTR_APLS_RTS_FLAG => r_PGM.INFORMATION34
,P_DRVBL_FCTR_DPNT_ELIG_FLAG => r_PGM.INFORMATION32
,P_DRVBL_FCTR_PRTN_ELIG_FLAG => r_PGM.INFORMATION33
,P_ELIG_APLS_FLAG => r_PGM.INFORMATION26
,P_ENRT_CVG_END_DT_CD => nvl(r_PGM.INFORMATION42,'ODBED')
,P_ENRT_CVG_END_DT_RL => r_PGM.INFORMATION266
,P_ENRT_CVG_STRT_DT_CD => nvl(r_PGM.INFORMATION45,'AED')
,P_ENRT_CVG_STRT_DT_RL => r_PGM.INFORMATION267
,P_ENRT_INFO_RT_FREQ_CD => r_PGM.INFORMATION46
,P_ENRT_MTHD_CD => r_PGM.INFORMATION52
,P_ENRT_RL => r_PGM.INFORMATION273
,P_IVR_IDENT => r_PGM.INFORMATION141
,P_MX_DPNT_PCT_PRTT_LF_AMT => r_PGM.INFORMATION287
,P_MX_SPS_PCT_PRTT_LF_AMT => r_PGM.INFORMATION288
,P_NAME => r_PGM.INFORMATION170
,P_PER_CVRD_CD => r_PGM.INFORMATION20
,P_PGM_DESC => r_PGM.INFORMATION219
,P_PGM_GRP_CD => r_PGM.INFORMATION49
,P_PGM_ID => l_pgm_id
,P_PGM_PRVDS_NO_AUTO_ENRT_FLAG => r_PGM.INFORMATION22
,P_PGM_PRVDS_NO_DFLT_ENRT_FLAG => r_PGM.INFORMATION24
,P_PGM_STAT_CD => r_PGM.INFORMATION38
,P_PGM_TYP_CD => r_PGM.INFORMATION39
,P_PGM_UOM => r_PGM.INFORMATION50
,P_PGM_USE_ALL_ASNTS_ELIG_FLAG => r_PGM.INFORMATION29
-- ,P_POE_LVL_CD => r_PGM.INFORMATION53
,P_PRTN_ELIG_OVRID_ALWD_FLAG => r_PGM.INFORMATION28
,P_RT_END_DT_CD => nvl(r_PGM.INFORMATION48,'ODBED')
,P_RT_END_DT_RL => r_PGM.INFORMATION271
,P_RT_STRT_DT_CD => nvl(r_PGM.INFORMATION47,'AED')
,P_RT_STRT_DT_RL => r_PGM.INFORMATION270
,P_SCORES_CALC_MTHD_CD => r_PGM.INFORMATION15
,P_SCORES_CALC_RL => r_PGM.INFORMATION261
,P_SHORT_CODE => r_PGM.INFORMATION11
,P_SHORT_NAME => r_PGM.INFORMATION12
,P_TRK_INELIG_PER_FLAG => r_PGM.INFORMATION35
,P_UPDATE_SALARY_CD => l_sal_upd_cd
,P_URL_REF_NAME => r_PGM.INFORMATION185
,P_USES_ALL_ASMTS_FOR_RTS_FLAG => r_PGM.INFORMATION27
,P_USE_MULTI_PAY_RATES_FLAG => r_PGM.INFORMATION17
,P_USE_PROG_POINTS_FLAG => r_PGM.INFORMATION18
,P_USE_SCORES_CD => r_PGM.INFORMATION19
,P_VRFY_FMLY_MMBR_CD => r_PGM.INFORMATION54
,P_VRFY_FMLY_MMBR_RL => r_PGM.INFORMATION274
,P_USE_VARIABLE_RATES_FLAG => NVL(r_PGM.INFORMATION69,'N')
,P_SALARY_CALC_MTHD_CD => r_PGM.INFORMATION70
,P_GSP_ALLOW_OVERRIDE_FLAG => NVL(r_PGM.INFORMATION72,'N')
,P_SALARY_CALC_MTHD_RL => r_PGM.INFORMATION293
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_ovn
,P_DATETRACK_MODE => l_dt_mode
);
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'COP'
and dml_operation <> 'REUSE' ;
select information261
into l_pl_id
from ben_copy_entity_results
where copy_entity_result_id = r_cop.gs_parent_entity_result_id;
select information1
into l_opt_id
from ben_copy_entity_results
where copy_entity_result_id = r_cop.information262;
and r_cop.dml_operation in ('INSERT','COPIED','UPD_INS')
and l_pl_id is not null
and l_ovn is null
and l_opt_id is not null then
if l_oipl_id is null then
hr_utility.set_location(' BEN_OIPL_F CREATE_OPTION_IN_PLAN ',20);
SELECT grd.date_from
INTO l_grd_effstdt
FROM per_grades grd,
ben_pl_f pln
WHERE pln.pl_id = l_pl_id
AND p_effective_date BETWEEN pln.effective_start_date
AND pln.effective_end_date
AND grd.grade_id = pln.mapping_table_pk_id;
elsif l_oipl_id is not null and r_cop.dml_operation ='UPDATE'
and l_ovn is not null
and l_pl_id is not null
and l_opt_id is not null then
hr_utility.set_location(' BEN_OIPL_F UPDATE_OPTION_IN_PLAN ',30);
l_dt_mode := get_update_mode(p_table_name => 'BEN_OIPL_F',
p_key_column_name => 'OIPL_ID',
p_key_column_value => l_oipl_id,
p_effective_date => l_effective_date);
BEN_OPTION_IN_PLAN_API.UPDATE_OPTION_IN_PLAN(
P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_business_group_id
,P_OIPL_ID => l_oipl_id
,P_ORDR_NUM => r_COP.INFORMATION263
,P_OPT_ID => l_opt_id
,P_PL_ID => l_pl_id
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_ovn
,P_DATETRACK_MODE => l_dt_mode
);
elsif l_oipl_id is not null and r_cop.dml_operation in ('INSERT','COPIED','UPD_INS') then
hr_utility.set_location('step api call created oipl'||l_proc,100);
elsif l_oipl_id is not null and r_cop.dml_operation in ('DELETE') then
hr_utility.set_location('oipl is being deleted '||l_proc,100);
cursor c_updated_cpp is
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CPP'
and information1 is not null
and dml_operation = 'UPDATE' ; -- only updated plips should be selected
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CPP'
and dml_operation in ('INSERT','UPDATE') -- only insert/ updates should be there
order by information263 desc;-- order by highest seq 1st
for r_upd_cpp in c_updated_cpp loop
-- api is not called as we don't want to update ovn
update ben_plip_f
set ordr_num = null
where plip_id = r_upd_cpp.information1;
select information1
into l_pgm_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'PGM'
and copy_entity_result_id = r_cpp.gs_parent_entity_result_id; -- 7610624
select information1
into l_pl_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'PLN'
and copy_entity_result_id = r_CPP.INFORMATION252;
SELECT information1
INTO l_corps_definition_id
FROM ben_copy_entity_results
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'CPD';
if l_plip_id is null and r_cpp.dml_operation = 'INSERT'
and l_pgm_id is not null and l_pl_id is not null then
hr_utility.set_location(' BEN_PLIP_F CREATE_PLAN_IN_PROGRAM ',20);
pqh_cpd_hr_to_stage.grd_quota_update(p_effective_date => l_effective_date,
p_grade_id => r_cpp.information253,
p_corps_definition_id => l_corps_definition_id,
p_corps_extra_info_id => r_cpp.information290,
p_perc_quota => r_cpp.information287,
p_population_cd => r_cpp.information99,
p_comb_grades => r_cpp.information219,
p_max_speed_quota => r_cpp.information288,
p_avg_speed_quota => r_cpp.information289);
elsif l_plip_id is not null and r_cpp.dml_operation = 'UPDATE'
and l_ovn is not null and l_pgm_id is not null
and l_pl_id is not null then
hr_utility.set_location(' BEN_PLIP_F UPDATE_PLAN_IN_PROGRAM ',30);
l_dt_mode := get_update_mode(p_table_name => 'BEN_PLIP_F',
p_key_column_name => 'PLIP_ID',
p_key_column_value => l_plip_id,
p_effective_date => l_effective_date);
BEN_PLAN_IN_PROGRAM_API.UPDATE_PLAN_IN_PROGRAM(
P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_business_group_id
,P_ORDR_NUM => r_CPP.INFORMATION263
,P_PGM_ID => l_pgm_id
,P_PLIP_ID => l_plip_id
,P_PL_ID => l_pl_id
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_ovn
,P_DATETRACK_MODE => l_dt_mode
);
pqh_cpd_hr_to_stage.grd_quota_update(p_effective_date => l_effective_date,
p_grade_id => r_cpp.information253,
p_corps_definition_id => l_corps_definition_id,
p_corps_extra_info_id => r_cpp.information290,
p_perc_quota => r_cpp.information287,
p_population_cd => r_cpp.information99,
p_comb_grades => r_cpp.information219,
p_max_speed_quota => r_cpp.information288,
p_avg_speed_quota => r_cpp.information289);
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CRSET'
and dml_operation in ('INSERT','UPDATE') -- only insert/ updates should be there
order by information161,information2;
if r_elp.dml_operation = 'INSERT' and l_elp_id is null then
l_dml_operation := 'INSERT';
elsif r_elp.dml_operation in ('INSERT','UPDATE') and l_elp_id is not null then
l_dml_operation := 'UPDATE';
if l_dml_operation = 'INSERT' then
hr_utility.set_location(' BEN_ELIGY_PRFL_F CREATE_ELIGY_PROFILE ',20);
elsif l_dml_operation = 'UPDATE' then
hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
l_dt_mode := get_update_mode(p_table_name => 'BEN_ELIGY_PRFL_F',
p_key_column_name => 'ELIGY_PRFL_ID',
p_key_column_value => l_elp_id,
p_effective_date => l_effective_date);
hr_utility.set_location(' BEN_ELIGY_PRFL_F UPDATE_ELIGY_PROFILE ',30);
BEN_ELIGY_PROFILE_API.UPDATE_ELIGY_PROFILE(
P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_business_group_id
,P_DESCRIPTION => r_ELP.INFORMATION151
,P_ELIGY_PRFL_ID => l_elp_id
,P_ELIGY_PRFL_RL_FLAG => 'N'
,P_ELIG_BRGNG_UNIT_FLAG => 'N'
,P_ELIG_FL_TM_PT_TM_FLAG => 'N'
,P_ELIG_JOB_FLAG => 'N'
,P_ELIG_ORG_UNIT_FLAG => 'N'
,P_ELIG_PERF_RTNG_FLAG => 'N'
,P_ELIG_PER_TYP_FLAG => 'N'
,P_ELIG_SVC_AREA_FLAG => 'N'
,P_ELIG_WK_LOC_FLAG => 'N'
,P_NAME => r_ELP.INFORMATION151
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_elp_ovn
,P_DATETRACK_MODE => l_dt_mode
);
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CEP'
and dml_operation in ('INSERT','UPDATE') ; -- only insert/update should be there
select information1,table_alias
into l_pk, l_tab
from ben_copy_entity_results
where copy_entity_result_id = r_cep.gs_mirror_src_entity_result_id;
if l_cep_id is null and l_epa_id is not null and l_ovn is null and r_cep.dml_operation = 'INSERT' then
hr_utility.set_location(' BEN_PRTN_ELIG_PRFL_F CREATE_PRTN_ELIG_PRFL ',20);
elsif l_cep_id is not null and l_epa_id is not null and l_ovn is not null and r_cep.dml_operation = 'UPDATE' then
hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
l_dt_mode := get_update_mode(p_table_name => 'BEN_PRTN_ELIG_PRFL_F',
p_key_column_name => 'PRTN_ELIG_PRFL_ID',
p_key_column_value => l_cep_id,
p_effective_date => l_effective_date);
hr_utility.set_location(' BEN_PRTN_ELIG_PRFL_F UPDATE_PRTN_ELIG_PRFL ',30);
BEN_PRTN_ELIG_PRFL_API.UPDATE_PRTN_ELIG_PRFL(
P_EFFECTIVE_DATE => l_effective_date
,P_BUSINESS_GROUP_ID => p_business_group_id
,P_ELIGY_PRFL_ID => r_CEP.INFORMATION263
,P_MNDTRY_FLAG => r_CEP.INFORMATION12
,P_COMPUTE_SCORE_FLAG => r_CEP.INFORMATION13
,P_PRTN_ELIG_ID => l_epa_id
,P_PRTN_ELIG_PRFL_ID => l_cep_id
,P_EFFECTIVE_START_DATE => l_effective_start_date
,P_EFFECTIVE_END_DATE => l_effective_end_date
,P_OBJECT_VERSION_NUMBER => l_ovn
,P_DATETRACK_MODE => l_dt_mode);
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'ABR'
and dml_operation = 'INSERT' ;-- only insert should be there
select information1
into l_pl_id
from ben_copy_entity_results
where copy_entity_result_id = r_ABR.INFORMATION277;
select information1
into l_opt_id
from ben_copy_entity_results
where copy_entity_result_id = r_ABR.INFORMATION278;
update ben_copy_entity_results
set information1 = l_abr_id
where copy_entity_result_id = r_abr.copy_entity_result_id;
hr_utility.set_location('abr id updated '||l_abr_id,222);
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'EPA'
and dml_operation = 'INSERT' ; -- only insert should be there
select information1,table_alias
into l_pk, l_tab
from ben_copy_entity_results
where copy_entity_result_id = r_epa.gs_mirror_src_entity_result_id;
if l_epa_id is null and l_pk is not null and r_epa.dml_operation = 'INSERT' then
hr_utility.set_location(' BEN_PRTN_ELIG_F CREATE_PARTICIPATION_ELIG ',20);
update ben_copy_entity_results
set information1 = l_epa_id
where copy_entity_result_id = r_epa.copy_entity_result_id;
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CRRATE'
and dml_operation in ('INSERT','UPDATE') -- only insert/ updates should be there
order by information230,information169,information160,information2;
hr_utility.set_location('existing vpf is being updated'||l_proc,10);
if crr_rec.dml_operation = 'INSERT'
and nvl(crr_rec.datetrack_mode,'CORRECTION') <> 'UPDATE_REPLACE' then
l_dml_operation := 'INSERT';
elsif crr_rec.dml_operation = 'INSERT' and crr_rec.datetrack_mode = 'UPDATE_REPLACE' then
l_dml_operation := 'UPDATE';
elsif crr_rec.dml_operation = 'UPDATE' then
l_dml_operation := 'UPDATE';
select information1
into l_abr_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and copy_entity_result_id = crr_rec.information161;
if l_dml_operation = 'INSERT' and l_abr_id is not null and l_crr_id is null and l_crr_ovn is null then
begin
hr_utility.set_location(' BEN_VRBL_RT_PRFL_F CREATE_VRBL_RATE_PROFILE ',20);
hr_utility.set_location('after vpf insert ',222);
select null
into dummy
from ben_acty_base_rt_f
where acty_base_rt_id = l_abr_id
and USES_VARBL_RT_FLAG = 'Y'
and l_effective_date between effective_start_date
and effective_end_date;
update ben_acty_base_rt_f
set USES_VARBL_RT_FLAG = 'Y'
where acty_base_rt_id = l_abr_id;
hr_utility.set_location('updated to Yes',223);
select nvl(max(ordr_num),0) + 1
into l_avr_num
from ben_acty_vrbl_rt_f
where acty_base_rt_id = l_abr_id;
select information277
into l_elp_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CRSET'
and information161 = crr_rec.information160;
elsif l_dml_operation = 'UPDATE'
and l_abr_id is not null
and l_crr_id is not null
and l_crr_ovn is not null then
hr_utility.set_location(' BEN_VRBL_RT_PRFL_F UPDATE_VRBL_RATE_PROFILE ',30);
l_dt_mode := get_update_mode(p_table_name => 'BEN_VRBL_RT_PRFL_F',
p_key_column_name => 'VRBL_RT_PRFL_ID',
p_key_column_value => l_crr_id,
p_effective_date => l_effective_date);
BEN_VRBL_RATE_PROFILE_API.UPDATE_VRBL_RATE_PROFILE(
P_EFFECTIVE_DATE => l_effective_date
,P_VAL => crr_rec.INFORMATION293
,P_VRBL_RT_PRFL_ID => l_crr_id
,P_EFFECTIVE_START_DATE => l_esd
,P_EFFECTIVE_END_DATE => l_eed
,P_OBJECT_VERSION_NUMBER => l_crr_ovn
,P_DATETRACK_MODE => l_dt_mode
);
SELECT NAME
INTO l_pgm_name
FROM ben_pgm_f
WHERE pgm_id = p_pgm_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT popl_enrt_typ_cycl_id
FROM ben_popl_enrt_typ_cycl_f
WHERE pgm_id = p_pgm_id;
SELECT NULL
FROM ben_lee_rsn_f
WHERE ler_id = p_ler_id
AND business_group_id = p_business_group_id
AND popl_enrt_typ_cycl_id = p_popl_enrt_typ_cycl_id;
SELECT ler_id
FROM ben_ler_f
WHERE typ_cd = 'GSP'
AND lf_evt_oper_cd = 'SYNC'
AND business_group_id = p_business_group_id
AND effective_start_date = pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id, null);
SELECT pgm_id
FROM ben_pgm_f
WHERE business_group_id = p_business_group_id
AND pgm_typ_cd = 'GSP'
AND eff_date BETWEEN effective_start_date AND effective_end_date;
select min(effective_start_date)
from ben_pgm_f
where pgm_id = p_program_id;
update ben_copy_entity_results cer
set information263 =
( select ELIGY_PRFL_ID
from BEN_ELIGY_PRFL_F elp
where elp.BUSINESS_GROUP_ID = p_business_group_id
and elp.name = cer.information5
and p_effective_date between
elp.effective_start_date and elp.effective_end_date)
where cer.copy_entity_txn_id = p_copy_entity_txn_id
and cer.information4 = p_business_group_id
and cer.table_alias = 'CEP'
and p_effective_date between
cer.information2 and nvl(cer.information3,to_date('4712/12/31','YYYY/MM/DD'))
and exists ( select ELIGY_PRFL_ID
from BEN_ELIGY_PRFL_F elp
where elp.BUSINESS_GROUP_ID = p_business_group_id
and elp.name = cer.information5
and p_effective_date between
elp.effective_start_date and elp.effective_end_date);
hr_utility.set_location('No of staging rows updated :'||sql%rowcount||':',99);
procedure cre_update_elig_prfl(
p_copy_entity_txn_id in number
,p_effective_date in date
,p_business_group_id in number
,p_business_area in varchar2 default 'PQH_GSP_TASK_LIST')
is
l_delete_failed varchar2(10);
hr_utility.set_location('Entering pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
UPDATE ben_copy_entity_results cer
set dml_operation = 'GSPDEL'
where cer.copy_entity_txn_id = p_copy_entity_txn_id
and cer.dml_operation = 'DELETE'
and table_alias in ('CPP','CEP','EPA','COP','OPT','ABR');
ben_plan_design_wizard_api.update_result_rows(p_copy_entity_txn_id => p_copy_entity_txn_id);
ben_plan_design_delete_api.call_delete_apis(
p_copy_entity_txn_id => p_copy_entity_txn_id
,p_delete_failed => l_delete_failed
);
UPDATE ben_copy_entity_results cer
set number_of_copies = 0
where cer.copy_entity_txn_id = p_copy_entity_txn_id
and p_effective_date between nvl(information2,p_effective_date)
and nvl(information3,p_effective_date)
and cer.dml_operation = 'DELETE';
BEN_PD_COPY_TO_BEN_ONE.g_pk_tbl.delete;
hr_utility.set_location(' Update elig_prfl_id in staging area ',999);
UPDATE ben_copy_entity_results cer
set dml_operation = 'DELETE'
where cer.copy_entity_txn_id = p_copy_entity_txn_id
and cer.dml_operation = 'GSPDEL';
hr_utility.set_location('Leaving pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
end cre_update_elig_prfl ;