[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select null
from ben_round_code_values_efc
where efc_action_id = c_action_id;
select efc.uom
from ben_actl_prem_f_efc efc
where efc.efc_action_id = c_action_id
and efc.actl_prem_id = c_apr_id
and efc.effective_start_date = c_esd;
select nvl(efc.pgm_uom,efc.nip_pl_uom)
from ben_acty_base_rt_f_efc efc
where efc.efc_action_id = c_action_id
and efc.acty_base_rt_id = c_abr_id
and efc.effective_start_date = c_esd;
select nvl(efc.pgm_uom,efc.nip_pl_uom)
from ben_bnft_pool_rlovr_rqmt_f_efc efc
where efc.efc_action_id = c_action_id
and efc.bnft_pool_rlovr_rqmt_id = c_bpr_id
and efc.effective_start_date = c_esd;
select efc.pgm_uom
from ben_bnft_prvdr_pool_f_efc efc
where efc.efc_action_id = c_action_id
and efc.bnft_prvdr_pool_id = c_bpp_id
and efc.effective_start_date = c_esd;
select efc.comp_lvl_uom
from ben_comp_lvl_fctr_efc efc
where efc.efc_action_id = c_action_id
and efc.comp_lvl_fctr_id = c_clf_id;
select nvl(efc.pgm_uom,efc.nip_pl_uom)
from ben_cvg_amt_calc_mthd_f_efc efc
where efc.efc_action_id = c_action_id
and efc.cvg_amt_calc_mthd_id = c_ccm_id
and efc.effective_start_date = c_esd;
select nvl(efc.pgm_uom,efc.nip_pl_uom)
from ben_poe_rt_f prt,
ben_vrbl_rt_prfl_f_efc efc
where efc.efc_action_id = c_action_id
and prt.poe_rt_id = c_prt_id
and efc.vrbl_rt_prfl_id = prt.vrbl_rt_prfl_id
and c_esd
between prt.effective_start_date and prt.effective_end_date
and efc.effective_start_date = c_esd;
select nvl(efc.pgm_uom,efc.nip_pl_uom)
from ben_prtl_mo_rt_prtn_val_f ppv,
ben_acty_base_rt_f_efc efc
where efc.efc_action_id = c_action_id
and ppv.acty_base_rt_id = c_ppv_id
and efc.acty_base_rt_id = ppv.acty_base_rt_id
and c_esd
between ppv.effective_start_date and ppv.effective_end_date
and efc.effective_start_date = c_esd;
select nvl(efc.pgm_uom,efc.nip_pl_uom)
from ben_vrbl_rt_prfl_f_efc efc
where efc.efc_action_id = c_action_id
and efc.vrbl_rt_prfl_id = c_vpf_id
and efc.effective_start_date = c_esd;
hr_efc_info.insert_line
('-- ');
hr_efc_info.insert_line
('-- Checking upgradeable rounding codes for business group ID '||p_business_group_id
||' EFC action ID: '||p_action_id
);
hr_efc_info.insert_line
('-- ');
l_sql_str := 'select count(*) '
||' from '||l_rndgtab_name
||' where '||l_rndgtabnm_set(rndgele_num).rndgcol_name||' is not null '
||' and business_group_id = '||p_business_group_id;
hr_efc_info.insert_line
('-- Mapping '||l_count||' rounding code values to EFC rounding code values in table '
||l_rndgtab_name
);
hr_efc_info.insert_line
('-- '||l_count||' Potential rounding code values to be upgraded exist in '
||l_rndgtab_name
);
hr_efc_info.insert_line
('-- '||l_count||' rounding code values being backed up from '
||l_rndgtab_name
);
l_sql_str := 'select '||l_sel_str
||' from '||l_rndgtab_name
||' where '||l_rndgtabnm_set(rndgele_num).rndgcol_name||' is not null '
||' and business_group_id = '||p_business_group_id;
insert into ben_round_code_values_efc
(efc_action_id
,table_name
,rndcdcol_name
,pk_id
,effective_start_date
,effective_end_date
,rndcdcol_value
)
values
(p_action_id
,l_rndgtab_name
,l_rndgtabnm_set(rndgele_num).rndgcol_name
,l_rndgcodedets.id
,l_esd
,l_eed
,l_rndgcodedets.rndgcol_value
);
update ben_actl_prem_f
set rndg_cd = l_newrndg_code
where actl_prem_id = l_rndgcodedets.id
and effective_start_date = l_rndgcodedets.effective_start_date;
update ben_acty_base_rt_f
set rndg_cd = l_newrndg_code
where acty_base_rt_id = l_rndgcodedets.id
and effective_start_date = l_rndgcodedets.effective_start_date;
update ben_bnft_pool_rlovr_rqmt_f
set val_rndg_cd = l_newrndg_code
where bnft_pool_rlovr_rqmt_id = l_rndgcodedets.id
and effective_start_date = l_rndgcodedets.effective_start_date;
update ben_bnft_prvdr_pool_f
set val_rndg_cd = l_newrndg_code
where bnft_prvdr_pool_id = l_rndgcodedets.id
and effective_start_date = l_rndgcodedets.effective_start_date;
update ben_comp_lvl_fctr
set rndg_cd = l_newrndg_code
where comp_lvl_fctr_id = l_rndgcodedets.id;
update ben_cvg_amt_calc_mthd_f
set rndg_cd = l_newrndg_code
where cvg_amt_calc_mthd_id = l_rndgcodedets.id
and effective_start_date = l_rndgcodedets.effective_start_date;
update ben_poe_rt_f
set rndg_cd = l_newrndg_code
where poe_rt_id = l_rndgcodedets.id
and effective_start_date = l_rndgcodedets.effective_start_date;
update ben_prtl_mo_rt_prtn_val_f
set rndg_cd = l_newrndg_code
where prtl_mo_rt_prtn_val_id = l_rndgcodedets.id
and effective_start_date = l_rndgcodedets.effective_start_date;
update ben_vrbl_rt_prfl_f
set rndg_cd = l_newrndg_code
where vrbl_rt_prfl_id = l_rndgcodedets.id
and effective_start_date = l_rndgcodedets.effective_start_date;
hr_efc_info.insert_line
('-- Mapped ID '||l_rndgcodedets.id||' in '||l_rndgtab_name
||' for to '||l_newrndg_code||' for currency '||l_currency_code
);