[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select def.view_attribute rslt_col
,def.exe_ordr
,def.rndg_cd
,def.calc_type
,cond.ordr_num cond_ordr
,cond.value cond_val
,cond_eq.ordr_num cond_eq_ordr
,cond_eq.view_attribute cond_eq_col
,cond_eq.operator cond_eq_oper
,cond_eq.logical cond_eq_log
,cond_eq.attribute1 cond_eq_attr1
,cond_eq.attribute2 cond_eq_attr2
,eq.ordr_num calc_eq_ordr
,eq.view_attribute calc_eq_col
,eq.operator calc_eq_oper
,eq.attribute1 calc_eq_attr1
,cond_eq.data_type cond_eq_data_type
,cond.view_attribute cond_col
,cond.message_type cond_msg_type
,cond_eq.view_attribute1 cond_eq_col1
,cond_eq.view_attribute2 cond_eq_col2
from ben_calc_definitions def
,ben_calc_conditions cond
,ben_calc_equations cond_eq
,ben_calc_equations eq
where def.def_key = v_def_key
and def.key_type = v_def_type
and def.definition_id = cond.definition_id (+)
and cond.condition_id = cond_eq.condition_id (+)
and def.definition_id = eq.definition_id (+)
and eq.condition_id (+) is null
order by def.exe_ordr, def.definition_id,
cond.ordr_num, cond_eq.ordr_num,
eq.ordr_num ;
select inf.*
,pl.elig_sal_val
,pl.stat_sal_val
,pl.ws_val
,pl.ws_mn_val
,pl.ws_mx_val
,pl.ws_incr_val
,pl.tot_comp_val
,pl.oth_comp_val
,pl.rec_val
,pl.rec_mn_val
,pl.rec_mx_val
,pl.misc1_val
,pl.misc2_val
,pl.misc3_val
,pl.currency
,pl.object_version_number ovn_pl
,dsgn.pl_annulization_factor
,dsgn.pl_id
,dsgn.ws_abr_id
,perf.attribute3 new_perf_rating
,asg.attribute3 new_change_reason
,asg.attribute5 new_job_id
,asg.attribute6 new_position_id
,asg.attribute7 new_grade_id
,opt1.elig_sal_val elig_sal_val_opt1
,opt1.stat_sal_val stat_sal_val_opt1
,opt1.ws_val ws_val_opt1
,opt1.ws_mn_val ws_mn_val_opt1
,opt1.ws_mx_val ws_mx_val_opt1
,opt1.ws_incr_val ws_incr_val_opt1
,opt1.tot_comp_val tot_comp_val_opt1
,opt1.oth_comp_val oth_comp_val_opt1
,opt1.rec_val rec_val_opt1
,opt1.rec_mn_val rec_mn_val_opt1
,opt1.rec_mx_val rec_mx_val_opt1
,opt1.misc1_val misc1_val_opt1
,opt1.misc2_val misc2_val_opt1
,opt1.misc3_val misc3_val_opt1
,opt1.currency currency_opt1
,opt1.object_version_number ovn_opt1
,opt1.oipl_id oipl_id_opt1
,opt1.group_oipl_id group_oipl_id_opt1
,opt2.elig_sal_val elig_sal_val_opt2
,opt2.stat_sal_val stat_sal_val_opt2
,opt2.ws_val ws_val_opt2
,opt2.ws_mn_val ws_mn_val_opt2
,opt2.ws_mx_val ws_mx_val_opt2
,opt2.ws_incr_val ws_incr_val_opt2
,opt2.tot_comp_val tot_comp_val_opt2
,opt2.oth_comp_val oth_comp_val_opt2
,opt2.rec_val rec_val_opt2
,opt2.rec_mn_val rec_mn_val_opt2
,opt2.rec_mx_val rec_mx_val_opt2
,opt2.misc1_val misc1_val_opt2
,opt2.misc2_val misc2_val_opt2
,opt2.misc3_val misc3_val_opt2
,opt2.currency currency_opt2
,opt2.object_version_number ovn_opt2
,opt2.oipl_id oipl_id_opt2
,opt2.group_oipl_id group_oipl_id_opt2
,opt3.elig_sal_val elig_sal_val_opt3
,opt3.stat_sal_val stat_sal_val_opt3
,opt3.ws_val ws_val_opt3
,opt3.ws_mn_val ws_mn_val_opt3
,opt3.ws_mx_val ws_mx_val_opt3
,opt3.ws_incr_val ws_incr_val_opt3
,opt3.tot_comp_val tot_comp_val_opt3
,opt3.oth_comp_val oth_comp_val_opt3
,opt3.rec_val rec_val_opt3
,opt3.rec_mn_val rec_mn_val_opt3
,opt3.rec_mx_val rec_mx_val_opt3
,opt3.misc1_val misc1_val_opt3
,opt3.misc2_val misc2_val_opt3
,opt3.misc3_val misc3_val_opt3
,opt3.currency currency_opt3
,opt3.object_version_number ovn_opt3
,opt3.oipl_id oipl_id_opt3
,opt3.group_oipl_id group_oipl_id_opt3
,opt4.elig_sal_val elig_sal_val_opt4
,opt4.stat_sal_val stat_sal_val_opt4
,opt4.ws_val ws_val_opt4
,opt4.ws_mn_val ws_mn_val_opt4
,opt4.ws_mx_val ws_mx_val_opt4
,opt4.ws_incr_val ws_incr_val_opt4
,opt4.tot_comp_val tot_comp_val_opt4
,opt4.oth_comp_val oth_comp_val_opt4
,opt4.rec_val rec_val_opt4
,opt4.rec_mn_val rec_mn_val_opt4
,opt4.rec_mx_val rec_mx_val_opt4
,opt4.misc1_val misc1_val_opt4
,opt4.misc2_val misc2_val_opt4
,opt4.misc3_val misc3_val_opt4
,opt4.currency currency_opt4
,opt4.object_version_number ovn_opt4
,opt4.oipl_id oipl_id_opt4
,opt4.group_oipl_id group_oipl_id_opt4
,null new_salary
,null new_grd_min_val
,null new_grd_max_val
,null new_grd_mid_point
,null new_grd_comparatio
,null new_grd_quartile
from ben_cwb_person_info inf
,ben_cwb_person_rates pl
,ben_cwb_pl_dsgn dsgn
,ben_transaction perf
,ben_transaction asg
,ben_cwb_person_rates opt1
,ben_cwb_person_rates opt2
,ben_cwb_person_rates opt3
,ben_cwb_person_rates opt4
where inf.group_per_in_ler_id = v_group_per_in_ler_id
and inf.group_per_in_ler_id = pl.group_per_in_ler_id
and pl.oipl_id = -1
and pl.elig_flag = 'Y'
and pl.pl_id = dsgn.pl_id
and pl.oipl_id = dsgn.oipl_id
and pl.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
and pl.group_per_in_ler_id = opt1.group_per_in_ler_id (+)
and pl.pl_id = opt1.pl_id (+)
and opt1.group_oipl_id (+) = v_group_oipl_id1
and opt1.elig_flag (+) = 'Y'
and pl.group_per_in_ler_id = opt2.group_per_in_ler_id (+)
and pl.pl_id = opt2.pl_id (+)
and opt2.group_oipl_id (+) = v_group_oipl_id2
and opt2.elig_flag (+) = 'Y'
and pl.group_per_in_ler_id = opt3.group_per_in_ler_id (+)
and pl.pl_id = opt3.pl_id (+)
and opt3.group_oipl_id (+) = v_group_oipl_id3
and opt3.elig_flag (+) = 'Y'
and pl.group_per_in_ler_id = opt4.group_per_in_ler_id (+)
and pl.pl_id = opt4.pl_id (+)
and opt4.group_oipl_id (+) = v_group_oipl_id4
and opt4.elig_flag (+) = 'Y'
and pl.assignment_id = perf.transaction_id (+)
and perf.transaction_type (+) = v_perf_txn_type
and pl.assignment_id = asg.transaction_id (+)
and asg.transaction_type (+) = v_asg_txn_type;
select o1.oipl_id oipl_id1
,o2.oipl_id oipl_id2
,o3.oipl_id oipl_id3
,o4.oipl_id oipl_id4
,ben_cwb_asg_update.g_ws_perf_rec_type||
to_char(pl.perf_revw_strt_dt,'yyyy/mm/dd')||
pl.emp_interview_typ_cd perf_txn_type
,ben_cwb_asg_update.g_ws_asg_rec_type||
to_char(pl.asg_updt_eff_date,'yyyy/mm/dd') asg_txn_type
from ben_cwb_pl_dsgn pl
,ben_cwb_pl_dsgn o1
,ben_cwb_pl_dsgn o2
,ben_cwb_pl_dsgn o3
,ben_cwb_pl_dsgn o4
where pl.pl_id = v_group_pl_id
and pl.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
and pl.oipl_id = -1
and pl.pl_id = o1.pl_id (+)
and pl.lf_evt_ocrd_dt = o1.lf_evt_ocrd_dt (+)
and o1.oipl_ordr_num (+) = 1
and o1.pl_id = o2.pl_id (+)
and o1.lf_evt_ocrd_dt = o2.lf_evt_ocrd_dt (+)
and o2.oipl_ordr_num (+) = 2
and o2.pl_id = o3.pl_id (+)
and o2.lf_evt_ocrd_dt = o3.lf_evt_ocrd_dt (+)
and o3.oipl_ordr_num (+) = 3
and o3.pl_id = o4.pl_id (+)
and o3.lf_evt_ocrd_dt = o4.lf_evt_ocrd_dt (+)
and o4.oipl_ordr_num (+) = 4;
select oipl_id,
oipl_ordr_num
from ben_cwb_pl_dsgn
where pl_id = g_allocation_row.pl_id
and lf_evt_ocrd_dt = g_allocation_row.lf_evt_ocrd_dt
and group_pl_id = g_allocation_row.group_pl_id
and ws_sub_acty_typ_cd = 'ICM7';
g_calc_details.delete;
update ben_cwb_person_info
set custom_segment1 = g_allocation_row.custom_segment1
,custom_segment2 = g_allocation_row.custom_segment2
,custom_segment3 = g_allocation_row.custom_segment3
,custom_segment4 = g_allocation_row.custom_segment4
,custom_segment5 = g_allocation_row.custom_segment5
,custom_segment6 = g_allocation_row.custom_segment6
,custom_segment7 = g_allocation_row.custom_segment7
,custom_segment8 = g_allocation_row.custom_segment8
,custom_segment9 = g_allocation_row.custom_segment9
,custom_segment10 = g_allocation_row.custom_segment10
,custom_segment11 = g_allocation_row.custom_segment11
,custom_segment12 = g_allocation_row.custom_segment12
,custom_segment13 = g_allocation_row.custom_segment13
,custom_segment14 = g_allocation_row.custom_segment14
,custom_segment15 = g_allocation_row.custom_segment15
,custom_segment16 = g_allocation_row.custom_segment16
,custom_segment17 = g_allocation_row.custom_segment17
,custom_segment18 = g_allocation_row.custom_segment18
,custom_segment19 = g_allocation_row.custom_segment19
,custom_segment20 = g_allocation_row.custom_segment20
,cpi_attribute1 = g_allocation_row.cpi_attribute1
,cpi_attribute2 = g_allocation_row.cpi_attribute2
,cpi_attribute3 = g_allocation_row.cpi_attribute3
,cpi_attribute4 = g_allocation_row.cpi_attribute4
,cpi_attribute5 = g_allocation_row.cpi_attribute5
,cpi_attribute6 = g_allocation_row.cpi_attribute6
,cpi_attribute7 = g_allocation_row.cpi_attribute7
,cpi_attribute8 = g_allocation_row.cpi_attribute8
,cpi_attribute9 = g_allocation_row.cpi_attribute9
,cpi_attribute10 = g_allocation_row.cpi_attribute10
,cpi_attribute11 = g_allocation_row.cpi_attribute11
,cpi_attribute12 = g_allocation_row.cpi_attribute12
,cpi_attribute13 = g_allocation_row.cpi_attribute13
,cpi_attribute14 = g_allocation_row.cpi_attribute14
,cpi_attribute15 = g_allocation_row.cpi_attribute15
,cpi_attribute16 = g_allocation_row.cpi_attribute16
,cpi_attribute17 = g_allocation_row.cpi_attribute17
,cpi_attribute18 = g_allocation_row.cpi_attribute18
,cpi_attribute19 = g_allocation_row.cpi_attribute19
,cpi_attribute20 = g_allocation_row.cpi_attribute20
,cpi_attribute21 = g_allocation_row.cpi_attribute21
,cpi_attribute22 = g_allocation_row.cpi_attribute22
,cpi_attribute23 = g_allocation_row.cpi_attribute23
,cpi_attribute24 = g_allocation_row.cpi_attribute24
,cpi_attribute25 = g_allocation_row.cpi_attribute25
,cpi_attribute26 = g_allocation_row.cpi_attribute26
,cpi_attribute27 = g_allocation_row.cpi_attribute27
,cpi_attribute28 = g_allocation_row.cpi_attribute28
,cpi_attribute29 = g_allocation_row.cpi_attribute29
,cpi_attribute30 = g_allocation_row.cpi_attribute30
where group_per_in_ler_id = g_allocation_row.group_per_in_ler_id;
ben_cwb_person_rates_api.update_person_rate
(p_validate => false
,p_group_per_in_ler_id => g_allocation_row.group_per_in_ler_id
,p_pl_id => g_allocation_row.pl_id
,p_oipl_id => -1
,p_group_pl_id => g_allocation_row.group_pl_id
,p_group_oipl_id => -1
,p_lf_evt_ocrd_dt => g_allocation_row.lf_evt_ocrd_dt
,p_ws_val => g_allocation_row.ws_val
,p_stat_sal_val => g_allocation_row.stat_sal_val
,p_oth_comp_val => g_allocation_row.oth_comp_val
,p_tot_comp_val => g_allocation_row.tot_comp_val
,p_misc1_val => g_allocation_row.misc1_val
,p_misc2_val => g_allocation_row.misc2_val
,p_misc3_val => g_allocation_row.misc3_val
,p_rec_val => g_allocation_row.rec_val
,p_object_version_number => g_allocation_row.ovn_pl);
ben_cwb_person_rates_api.update_person_rate
(p_validate => false
,p_group_per_in_ler_id => g_allocation_row.group_per_in_ler_id
,p_pl_id => g_allocation_row.pl_id
,p_oipl_id => g_allocation_row.oipl_id_opt1
,p_group_pl_id => g_allocation_row.group_pl_id
,p_group_oipl_id => g_allocation_row.group_oipl_id_opt1
,p_lf_evt_ocrd_dt => g_allocation_row.lf_evt_ocrd_dt
,p_ws_val => g_allocation_row.ws_val_opt1
,p_stat_sal_val => g_allocation_row.stat_sal_val_opt1
,p_oth_comp_val => g_allocation_row.oth_comp_val_opt1
,p_tot_comp_val => g_allocation_row.tot_comp_val_opt1
,p_misc1_val => g_allocation_row.misc1_val_opt1
,p_misc2_val => g_allocation_row.misc2_val_opt1
,p_misc3_val => g_allocation_row.misc3_val_opt1
,p_rec_val => g_allocation_row.rec_val_opt1
,p_object_version_number => g_allocation_row.ovn_opt1);
ben_cwb_person_rates_api.update_person_rate
(p_validate => false
,p_group_per_in_ler_id => g_allocation_row.group_per_in_ler_id
,p_pl_id => g_allocation_row.pl_id
,p_oipl_id => g_allocation_row.oipl_id_opt2
,p_group_pl_id => g_allocation_row.group_pl_id
,p_group_oipl_id => g_allocation_row.group_oipl_id_opt2
,p_lf_evt_ocrd_dt => g_allocation_row.lf_evt_ocrd_dt
,p_ws_val => g_allocation_row.ws_val_opt2
,p_stat_sal_val => g_allocation_row.stat_sal_val_opt2
,p_oth_comp_val => g_allocation_row.oth_comp_val_opt2
,p_tot_comp_val => g_allocation_row.tot_comp_val_opt2
,p_misc1_val => g_allocation_row.misc1_val_opt2
,p_misc2_val => g_allocation_row.misc2_val_opt2
,p_misc3_val => g_allocation_row.misc3_val_opt2
,p_rec_val => g_allocation_row.rec_val_opt2
,p_object_version_number => g_allocation_row.ovn_opt2);
ben_cwb_person_rates_api.update_person_rate
(p_validate => false
,p_group_per_in_ler_id => g_allocation_row.group_per_in_ler_id
,p_pl_id => g_allocation_row.pl_id
,p_oipl_id => g_allocation_row.oipl_id_opt3
,p_group_pl_id => g_allocation_row.group_pl_id
,p_group_oipl_id => g_allocation_row.group_oipl_id_opt3
,p_lf_evt_ocrd_dt => g_allocation_row.lf_evt_ocrd_dt
,p_ws_val => g_allocation_row.ws_val_opt3
,p_stat_sal_val => g_allocation_row.stat_sal_val_opt3
,p_oth_comp_val => g_allocation_row.oth_comp_val_opt3
,p_tot_comp_val => g_allocation_row.tot_comp_val_opt3
,p_misc1_val => g_allocation_row.misc1_val_opt3
,p_misc2_val => g_allocation_row.misc2_val_opt3
,p_misc3_val => g_allocation_row.misc3_val_opt3
,p_rec_val => g_allocation_row.rec_val_opt3
,p_object_version_number => g_allocation_row.ovn_opt3);
ben_cwb_person_rates_api.update_person_rate
(p_validate => false
,p_group_per_in_ler_id => g_allocation_row.group_per_in_ler_id
,p_pl_id => g_allocation_row.pl_id
,p_oipl_id => g_allocation_row.oipl_id_opt4
,p_group_pl_id => g_allocation_row.group_pl_id
,p_group_oipl_id => g_allocation_row.group_oipl_id_opt4
,p_lf_evt_ocrd_dt => g_allocation_row.lf_evt_ocrd_dt
,p_ws_val => g_allocation_row.ws_val_opt4
,p_stat_sal_val => g_allocation_row.stat_sal_val_opt4
,p_oth_comp_val => g_allocation_row.oth_comp_val_opt4
,p_tot_comp_val => g_allocation_row.tot_comp_val_opt4
,p_misc1_val => g_allocation_row.misc1_val_opt4
,p_misc2_val => g_allocation_row.misc2_val_opt4
,p_misc3_val => g_allocation_row.misc3_val_opt4
,p_rec_val => g_allocation_row.rec_val_opt4
,p_object_version_number => g_allocation_row.ovn_opt4);