eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.BEN_CWB_WS_DOWNLOAD_V

Source

View Text - Preformatted


SELECT /*+ ordered */ DECODE(grp_pl.name_profile, 'CN', per.custom_name, 'BN', per.brief_name, per.full_name) emp_name , DECODE(grp_pl.name_profile, 'CN', per.supervisor_custom_name, 'BN', per.supervisor_brief_name, per.supervisor_full_name) mgr_name, to_number ( ( SELECT rank.aei_information1 FROM per_assignment_extra_info rank, ben_cwb_person_info mper WHERE rank.assignment_id = per.assignment_id AND rank.information_type = 'CWBRANK' AND rank.aei_information3 IS NULL AND rank.aei_information2 = mper.person_id AND mper.group_per_in_ler_id = hrchy.mgr_per_in_ler_id ) ) rank , per.years_employed years_employed, ROUND(per.base_salary * per.pay_annulization_factor / NVL(pl1.planufactor, grp_pl.grpufactor) , NVL( ( SELECT PRECISION FROM fnd_currencies basecurr WHERE basecurr.currency_code = per.base_salary_currency ) ,2)) base_salary , NVL(pl1.plname, grp_pl.grpplname) pl_name , plxchg.xchg_rate pl_xchg_rate , ROUND(plrt.stat_sal_val,PlCurr.precision) pl_stat_sal_val, ROUND(plrt.elig_sal_val,PlCurr.precision) pl_elig_sal_val, ROUND(plrt.tot_comp_val,PlCurr.precision) pl_tot_comp_val, ROUND(plrt.oth_comp_val,PlCurr.precision) pl_oth_comp_val, ROUND(plrt.ws_val,PlCurr.precision) pl_ws_val , ROUND(plrt.ws_mn_val,PlCurr.precision) pl_ws_min_val , ROUND(plrt.ws_mx_val,PlCurr.precision) pl_ws_max_val , ROUND(plrt.ws_incr_val,PlCurr.precision) pl_ws_incr_val , ROUND(plrt.rec_val,PlCurr.precision) pl_rec_val , ROUND(plrt.rec_mn_val,PlCurr.precision) pl_rec_min_val , ROUND(plrt.rec_mx_val,PlCurr.precision) pl_rec_max_val , ROUND(plrt.misc1_val,PlCurr.precision) pl_misc1_val , ROUND(plrt.misc2_val,PlCurr.precision) pl_misc2_val , ROUND(plrt.misc3_val,PlCurr.precision) pl_misc3_val , plrt.ws_val_last_upd_date pl_ws_last_upd_date , ( SELECT ppl.full_name FROM per_all_people_f ppl WHERE ppl.person_id = plrt.ws_val_last_upd_by AND TRUNC(sysdate) BETWEEN ppl.effective_start_date AND ppl.effective_end_date ) pl_ws_last_upd_name , temp1.opt1name opt1_name , NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', opt1xchg.xchg_rate, NULL), NULL) opt1_xchg_rate , NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', ROUND(opt1rt.stat_sal_val,Opt1Curr.precision), NULL), NULL) opt1_stat_sal_val, NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', ROUND(opt1rt.elig_sal_val,Opt1Curr.precision), NULL), NULL) opt1_elig_sal_val, NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', ROUND(opt1rt.tot_comp_val,Opt1Curr.precision), NULL), NULL) opt1_tot_comp_val, NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', ROUND(opt1rt.oth_comp_val,Opt1Curr.precision), NULL), NULL) opt1_oth_comp_val, NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', ROUND(opt1rt.ws_val,Opt1Curr.precision), NULL), NULL) opt1_ws_val , NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', ROUND(opt1rt.ws_mn_val,Opt1Curr.precision), NULL), NULL) opt1_ws_min_val , NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', ROUND(opt1rt.ws_mx_val,Opt1Curr.precision), NULL), NULL) opt1_ws_max_val , NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', ROUND(opt1rt.ws_incr_val,Opt1Curr.precision), NULL), NULL) opt1_ws_incr_val , NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', ROUND(opt1rt.rec_val,Opt1Curr.precision), NULL), NULL) opt1_rec_val , NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', ROUND(opt1rt.rec_mn_val,Opt1Curr.precision), NULL), NULL) opt1_rec_min_val , NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', ROUND(opt1rt.rec_mx_val,Opt1Curr.precision), NULL), NULL) opt1_rec_max_val , NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', ROUND(opt1rt.misc1_val,Opt1Curr.precision), NULL), NULL) opt1_misc1_val , NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', ROUND(opt1rt.misc2_val,Opt1Curr.precision), NULL), NULL) opt1_misc2_val , NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', ROUND(opt1rt.misc3_val,Opt1Curr.precision), NULL), NULL) opt1_misc3_val , NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', opt1rt.ws_val_last_upd_date, NULL), NULL) opt1_ws_last_upd_date , NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', ( SELECT ppl.full_name FROM per_all_people_f ppl WHERE ppl.person_id = opt1rt.ws_val_last_upd_by AND TRUNC(sysdate) BETWEEN ppl.effective_start_date AND ppl.effective_end_date ) , NULL), NULL) opt1_ws_last_upd_name , temp2.opt2name opt2_name , NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', opt2xchg.xchg_rate, NULL), NULL) opt2_xchg_rate , NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', ROUND(opt2rt.stat_sal_val,Opt2Curr.precision), NULL), NULL) opt2_stat_sal_val, NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', ROUND(opt2rt.elig_sal_val,Opt2Curr.precision), NULL), NULL) opt2_elig_sal_val, NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', ROUND(opt2rt.tot_comp_val,Opt2Curr.precision), NULL), NULL) opt2_tot_comp_val, NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', ROUND(opt2rt.oth_comp_val,Opt2Curr.precision), NULL), NULL) opt2_oth_comp_val, NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', ROUND(opt2rt.ws_val,Opt2Curr.precision), NULL), NULL) opt2_ws_val , NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', ROUND(opt2rt.ws_mn_val,Opt2Curr.precision), NULL), NULL) opt2_ws_min_val , NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', ROUND(opt2rt.ws_mx_val,Opt2Curr.precision), NULL), NULL) opt2_ws_max_val , NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', ROUND(opt2rt.ws_incr_val,Opt2Curr.precision), NULL), NULL) opt2_ws_incr_val , NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', ROUND(opt2rt.rec_val,Opt2Curr.precision), NULL), NULL) opt2_rec_val , NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', ROUND(opt2rt.rec_mn_val,Opt2Curr.precision), NULL), NULL) opt2_rec_min_val , NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', ROUND(opt2rt.rec_mx_val,Opt2Curr.precision), NULL), NULL) opt2_rec_max_val , NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', ROUND(opt2rt.misc1_val,Opt2Curr.precision), NULL), NULL) opt2_misc1_val , NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', ROUND(opt2rt.misc2_val,Opt2Curr.precision), NULL), NULL) opt2_misc2_val , NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', ROUND(opt2rt.misc3_val,Opt2Curr.precision), NULL), NULL) opt2_misc3_val , NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', opt2rt.ws_val_last_upd_date, NULL), NULL) opt2_ws_last_upd_date , NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', ( SELECT ppl.full_name FROM per_all_people_f ppl WHERE ppl.person_id = opt2rt.ws_val_last_upd_by AND TRUNC(sysdate) BETWEEN ppl.effective_start_date AND ppl.effective_end_date ) , NULL), NULL) opt2_ws_last_upd_name , temp3.opt3name opt3_name , NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', opt3xchg.xchg_rate , NULL), NULL) opt3_xchg_rate , NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', ROUND(opt3rt.stat_sal_val,Opt3Curr.precision), NULL), NULL) opt3_stat_sal_val, NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', ROUND(opt3rt.elig_sal_val,Opt3Curr.precision), NULL), NULL) opt3_elig_sal_val, NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', ROUND(opt3rt.tot_comp_val,Opt3Curr.precision), NULL), NULL) opt3_tot_comp_val, NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', ROUND(opt3rt.oth_comp_val,Opt3Curr.precision), NULL), NULL) opt3_oth_comp_val, NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', ROUND(opt3rt.ws_val,Opt3Curr.precision), NULL), NULL) opt3_ws_val , NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', ROUND(opt3rt.ws_mn_val,Opt3Curr.precision), NULL), NULL) opt3_ws_min_val , NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', ROUND(opt3rt.ws_mx_val,Opt3Curr.precision), NULL), NULL) opt3_ws_max_val , NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', ROUND(opt3rt.ws_incr_val,Opt3Curr.precision), NULL), NULL) opt3_ws_incr_val , NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', ROUND(opt3rt.rec_val,Opt3Curr.precision), NULL), NULL) opt3_rec_val , NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', ROUND(opt3rt.rec_mn_val,Opt3Curr.precision), NULL), NULL) opt3_rec_min_val , NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', ROUND(opt3rt.rec_mx_val,Opt3Curr.precision), NULL), NULL) opt3_rec_max_val , NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', ROUND(opt3rt.misc1_val,Opt3Curr.precision), NULL), NULL) opt3_misc1_val , NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', ROUND(opt3rt.misc2_val,Opt3Curr.precision), NULL), NULL) opt3_misc2_val , NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', ROUND(opt3rt.misc3_val,Opt3Curr.precision), NULL), NULL) opt3_misc3_val , NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', opt3rt.ws_val_last_upd_date, NULL), NULL) opt3_ws_last_upd_date , NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', ( SELECT ppl.full_name FROM per_all_people_f ppl WHERE ppl.person_id = opt3rt.ws_val_last_upd_by AND TRUNC(sysdate) BETWEEN ppl.effective_start_date AND ppl.effective_end_date ) , NULL), NULL) opt3_ws_last_upd_name , temp4.opt4name opt4_name , NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', opt4xchg.xchg_rate , NULL), NULL) opt4_xchg_rate , NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', ROUND(opt4rt.stat_sal_val,Opt4Curr.precision), NULL), NULL) opt4_stat_sal_val, NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', ROUND(opt4rt.elig_sal_val,Opt4Curr.precision), NULL), NULL) opt4_elig_sal_val, NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', ROUND(opt4rt.tot_comp_val,Opt4Curr.precision), NULL), NULL) opt4_tot_comp_val, NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', ROUND(opt4rt.oth_comp_val,Opt4Curr.precision), NULL), NULL) opt4_oth_comp_val, NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', ROUND(opt4rt.ws_val,Opt4Curr.precision), NULL), NULL) opt4_ws_val , NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', ROUND(opt4rt.ws_mn_val,Opt4Curr.precision), NULL), NULL) opt4_ws_min_val , NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', ROUND(opt4rt.ws_mx_val,Opt4Curr.precision), NULL), NULL) opt4_ws_max_val , NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', ROUND(opt4rt.ws_incr_val,Opt4Curr.precision), NULL), NULL) opt4_ws_incr_val , NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', ROUND(opt4rt.rec_val,Opt4Curr.precision), NULL), NULL) opt4_rec_val , NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', ROUND(opt4rt.rec_mn_val,Opt4Curr.precision), NULL), NULL) opt4_rec_min_val , NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', ROUND(opt4rt.rec_mx_val,Opt4Curr.precision), NULL), NULL) opt4_rec_max_val , NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', ROUND(opt4rt.misc1_val,Opt4Curr.precision), NULL), NULL) opt4_misc1_val , NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', ROUND(opt4rt.misc2_val,Opt4Curr.precision), NULL), NULL) opt4_misc2_val , NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', ROUND(opt4rt.misc3_val,Opt4Curr.precision), NULL), NULL) opt4_misc3_val , NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', opt4rt.ws_val_last_upd_date, NULL), NULL) opt4_ws_last_upd_date , NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', ( SELECT ppl.full_name FROM per_all_people_f ppl WHERE ppl.person_id = opt4rt.ws_val_last_upd_by AND TRUNC(sysdate) BETWEEN ppl.effective_start_date AND ppl.effective_end_date ) , NULL), NULL) opt4_ws_last_upd_name , per.employee_number employee_number , EmpCatLkp.meaning emp_category , ast.user_status assignment_status , per.people_group_name people_group_name , per.email_address email_addr , per.start_date start_date , per.original_start_date original_start_date , per.normal_hours normal_hours , per.payroll_name payroll_name , bg.name business_group_name , org.name org_name , NVL(loc.description, loc.location_code) loc_name, job.name job_name , pos.name pos_name , grd.name grd_name , per.legislation_code country , per.years_in_job years_in_job , per.years_in_position years_in_position , per.years_in_grade years_in_grade , ltrim(TO_CHAR(per.grd_min_val*per.grade_annulization_factor/pl1.planufactor,'999G999G999G990D00')) ||'-' ||ltrim(TO_CHAR(per.grd_max_val*per.grade_annulization_factor/pl1.planufactor,'999G999G999G990D00')) grade_range, ROUND(per.grd_mid_point *per.grade_annulization_factor/pl1.planufactor,2) grade_mid_point , GrdQrtleLkp.meaning grd_quartile , per.grd_comparatio grd_comparatio , PerfRatingLkp.meaning performance_rating , PerfRatingTypeLkp.meaning performance_rating_type , per.performance_rating_date performance_rating_date , to_number ( ( SELECT lst.aei_information1 FROM per_assignment_extra_info lst WHERE lst.assignment_id = per.assignment_id AND lst.aei_information2 = last_mgr_per.person_id AND lst.aei_information3 IS NULL AND lst.information_type = 'CWBRANK' ) ) last_rank , last_mgr_per.full_name last_mgr_name, to_number(NULL) rank_quartile , to_number(NULL) total_rank , ( SELECT hlk.meaning FROM ben_transaction txn, hr_lookups hlk WHERE txn.transaction_id = per.assignment_id AND txn.transaction_type = 'CWBASG' || grp_pl.asg_date AND hlk.lookup_code = txn.attribute3 AND txn.attribute3 IS NOT NULL AND hlk.lookup_type = 'EMP_ASSIGN_REASON' AND hlk.enabled_flag = 'Y' AND sysdate BETWEEN NVL(hlk.start_date_active, sysdate) AND NVL(hlk.end_date_active, sysdate) ) change_reason , per.base_salary_change_date base_salary_change_date , plrt.lf_evt_ocrd_dt lf_evt_ocrd_dt , hrchy.mgr_per_in_ler_id mgr_ler_id , DECODE(temp1.oipl1id, NULL, ben_cwb_webadi_utils.encrypt(rpad(TO_CHAR(plrt.person_rate_id), 8, ' ')), NULL) pl_person_rate_id, NVL2(temp1.oipl1id, ben_cwb_webadi_utils.encrypt(rpad(TO_CHAR(opt1rt.person_rate_id), 8, ' ')), NULL) p_opt1_person_rate_id , NVL2(temp2.oipl2id, ben_cwb_webadi_utils.encrypt(rpad(TO_CHAR(opt2rt.person_rate_id), 8, ' ')), NULL) p_opt2_person_rate_id , NVL2(temp3.oipl3id, ben_cwb_webadi_utils.encrypt(rpad(TO_CHAR(opt3rt.person_rate_id), 8, ' ')), NULL) p_opt3_person_rate_id , NVL2(temp4.oipl4id, ben_cwb_webadi_utils.encrypt(rpad(TO_CHAR(opt4rt.person_rate_id), 8, ' ')), NULL) p_opt4_person_rate_id , hrchy.lvl_num , per.custom_segment1 custom_segment1 , per.custom_segment2 custom_segment2 , per.custom_segment3 custom_segment3 , per.custom_segment4 custom_segment4 , per.custom_segment5 custom_segment5 , per.custom_segment6 custom_segment6 , per.custom_segment7 custom_segment7 , per.custom_segment8 custom_segment8 , per.custom_segment9 custom_segment9 , per.custom_segment10 custom_segment10 , per.custom_segment11 custom_segment11 , per.custom_segment12 custom_segment12 , per.custom_segment13 custom_segment13 , per.custom_segment14 custom_segment14 , per.custom_segment15 custom_segment15 , ( SELECT hr_general.decode_lookup('PERFORMANCE_RATING', ptxn.attribute3) FROM ben_transaction ptxn WHERE ptxn.transaction_id = per.assignment_id AND ptxn.transaction_type = 'CWBPERF' || grp_pl.perf_date || grp_pl.perf_type ) proposed_performance_rating, ( SELECT job1.name FROM ben_transaction atxn, per_jobs_tl job1 WHERE atxn.transaction_id = per.assignment_id AND atxn.transaction_type = 'CWBASG' || grp_pl.asg_date AND to_number(atxn.attribute5) = job1.job_id AND job1.LANGUAGE = userenv('lang') ) proposed_job , NVL(pl1.plunits, plrt.currency) plan_uom , NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', NVL(temp1.opt1units, opt1rt.currency), NULL), NULL) opt1_uom, NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', NVL(temp2.opt2units, opt2rt.currency), NULL), NULL) opt2_uom, NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', NVL(temp3.opt3units, opt3rt.currency), NULL), NULL) opt3_uom, NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', NVL(temp4.opt4units, opt4rt.currency), NULL), NULL) opt4_uom, ( SELECT grd1.Name FROM ben_transaction atxn, per_grades_tl grd1 WHERE atxn.transaction_id = per.assignment_id AND atxn.transaction_type = 'CWBASG' || grp_pl.asg_date AND to_number(atxn.attribute7) = grd1.grade_id AND grd1.LANGUAGE = userenv('lang') ) proposed_grade, ( SELECT pos1.Name FROM ben_transaction atxn, hr_all_positions_f_tl pos1 WHERE atxn.transaction_id = per.assignment_id AND atxn.transaction_type = 'CWBASG' || grp_pl.asg_date AND to_number(atxn.attribute6) = pos1.position_id AND pos1.LANGUAGE = userenv('lang') ) proposed_position , TO_CHAR(NULL) proposed_group , per.cpi_attribute_category cpi_attribute_category , per.cpi_attribute1 cpi_attribute1 , per.cpi_attribute2 cpi_attribute2 , per.cpi_attribute3 cpi_attribute3 , per.cpi_attribute4 cpi_attribute4 , per.cpi_attribute5 cpi_attribute5 , per.cpi_attribute6 cpi_attribute6 , per.cpi_attribute7 cpi_attribute7 , per.cpi_attribute8 cpi_attribute8 , per.cpi_attribute9 cpi_attribute9 , per.cpi_attribute10 cpi_attribute10 , per.cpi_attribute11 cpi_attribute11 , per.cpi_attribute12 cpi_attribute12 , per.cpi_attribute13 cpi_attribute13 , per.cpi_attribute14 cpi_attribute14 , per.cpi_attribute15 cpi_attribute15 , per.cpi_attribute16 cpi_attribute16 , per.cpi_attribute17 cpi_attribute17 , per.cpi_attribute18 cpi_attribute18 , per.cpi_attribute19 cpi_attribute19 , per.cpi_attribute20 cpi_attribute20 , per.cpi_attribute21 cpi_attribute21 , per.cpi_attribute22 cpi_attribute22 , per.cpi_attribute23 cpi_attribute23 , per.cpi_attribute24 cpi_attribute24 , per.cpi_attribute25 cpi_attribute25 , per.cpi_attribute26 cpi_attribute26 , per.cpi_attribute27 cpi_attribute27 , per.cpi_attribute28 cpi_attribute28 , per.cpi_attribute29 cpi_attribute29 , per.cpi_attribute30 cpi_attribute30 , per.custom_segment16 custom_segment16 , per.custom_segment17 custom_segment17 , per.custom_segment18 custom_segment18 , per.custom_segment19 custom_segment19 , per.custom_segment20 custom_segment20 , plrt.currency pl_currency , NVL2(temp1.oipl1id, opt1rt.currency, NULL) opt1_currency, NVL2(temp2.oipl2id, opt2rt.currency, NULL) opt2_currency, NVL2(temp3.oipl3id, opt3rt.currency, NULL) opt3_currency, NVL2(temp4.oipl4id, opt4rt.currency, NULL) opt4_currency, plrt.ws_rt_start_date pl_ws_rt_start_date, NVL2(temp1.oipl1id, DECODE(opt1rt.elig_flag, 'Y', opt1rt.ws_rt_start_date, NULL), NULL) opt1_ws_rt_start_date, NVL2(temp2.oipl2id, DECODE(opt2rt.elig_flag, 'Y', opt2rt.ws_rt_start_date, NULL), NULL) opt2_ws_rt_start_date, NVL2(temp3.oipl3id, DECODE(opt3rt.elig_flag, 'Y', opt3rt.ws_rt_start_date, NULL), NULL) opt3_ws_rt_start_date, NVL2(temp4.oipl4id, DECODE(opt4rt.elig_flag, 'Y', opt4rt.ws_rt_start_date, NULL), NULL) opt4_ws_rt_start_date FROM ben_cwb_group_hrchy hrchy , ben_cwb_person_rates plrt , ( SELECT grp_pl.pl_id grpplid , grp_pl.oipl_id grpoiplid , GRP_PL.GROUP_PL_ID , GRP_PL.GROUP_OIPL_ID , GRP_PL.OIPL_ID , grp_pl.lf_evt_ocrd_dt grplfevtorcddt , grp_pl.name grpPlName , TO_CHAR(grp_pl.perf_revw_strt_dt, 'yyyy/mm/dd') perf_date , grp_pl.emp_interview_typ_cd perf_type , TO_CHAR(grp_pl.asg_updt_eff_date, 'yyyy/mm/dd') asg_date , grp_pl.ws_abr_id grpWsAbrId , grp_pl.actual_flag grpActulFlag , grp_pl.pl_annulization_factor grpufactor , hr_general.decode_lookup('BEN_NNMNTRY_UOM', grp_pl.ws_nnmntry_uom) grpunits, grp_pl.pl_uom grpcurr , fnd_profile.VALUE('BEN_DISPLAY_EMPLOYEE_NAME') name_profile FROM ben_cwb_pl_dsgn grp_pl ) grp_pl , ( SELECT pl1.pl_id , pl1.lf_evt_ocrd_dt , PL1.OIPL_ID , pl1.group_pl_id , PL1.ACTUAL_FLAG , pl1.name PlName , pl1.oipl_id PlOiplId , pl1.ws_abr_id PlWsAbrId , pl1.actual_flag PlActulFlag , pl1.pl_annulization_factor planufactor , hr_general.decode_lookup('BEN_NNMNTRY_UOM', pl1.ws_nnmntry_uom) plunits FROM ben_cwb_pl_dsgn pl1 ) pl1 , ( SELECT opt1.oipl_id Oipl1Id , opt1.lf_evt_ocrd_dt opt1_lf_evt_ocrd_dt , opt1.group_pl_id oip1grpplid , opt1.group_oipl_id oip1grpoiplid , opt1.pl_id oip1plid , opt1.name Opt1Name , opt1.ws_abr_id Opt1WsAbrID , opt1.oipl_ordr_num Opt1OiplOrdrNum , hr_general.decode_lookup('BEN_NNMNTRY_UOM', opt1.ws_nnmntry_uom) opt1units FROM ben_cwb_pl_dsgn opt1 ) temp1 , ( SELECT opt2.oipl_id Oipl2Id , opt2.lf_evt_ocrd_dt opt2_lf_evt_ocrd_dt , opt2.group_pl_id oip2grpplid , opt2.group_oipl_id oip2grpoiplid , opt2.pl_id oip2plid , opt2.name Opt2Name , opt2.ws_abr_id Opt2WsAbrID , opt2.oipl_ordr_num Opt2OiplOrdrNum , hr_general.decode_lookup('BEN_NNMNTRY_UOM', opt2.ws_nnmntry_uom) opt2units FROM ben_cwb_pl_dsgn opt2 ) temp2 , ( SELECT opt3.oipl_id Oipl3Id , opt3.lf_evt_ocrd_dt opt3_lf_evt_ocrd_dt , opt3.group_pl_id oip3grpplid , opt3.group_oipl_id oip3grpoiplid , opt3.pl_id oip3plid , opt3.name Opt3Name , opt3.ws_abr_id Opt3WsAbrID , opt3.oipl_ordr_num Opt3OiplOrdrNum , hr_general.decode_lookup('BEN_NNMNTRY_UOM', opt3.ws_nnmntry_uom) opt3units FROM ben_cwb_pl_dsgn opt3 ) temp3, ( SELECT opt4.oipl_id Oipl4Id , opt4.lf_evt_ocrd_dt opt4_lf_evt_ocrd_dt , opt4.group_pl_id oip4grpplid , opt4.group_oipl_id oip4grpoiplid , opt4.pl_id oip4plid , opt4.name Opt4Name , opt4.ws_abr_id Opt4WsAbrID , opt4.oipl_ordr_num Opt4OiplOrdrNum , hr_general.decode_lookup('BEN_NNMNTRY_UOM', opt4.ws_nnmntry_uom) opt4units FROM ben_cwb_pl_dsgn opt4 ) temp4 , ben_cwb_person_rates opt1rt , ben_cwb_person_rates opt2rt , ben_cwb_person_rates opt3rt , ben_cwb_person_rates opt4rt , ben_cwb_xchg plxchg , ben_cwb_xchg opt1xchg , ben_cwb_xchg opt2xchg , ben_cwb_xchg opt3xchg , ben_cwb_xchg opt4xchg , ben_cwb_person_info per , per_assignment_status_types_tl ast , hr_all_organization_units_tl bg , hr_all_organization_units_tl org , hr_locations_all_tl loc , per_jobs_tl job , hr_all_positions_f_tl pos , per_grades_tl grd , ben_cwb_group_hrchy last_mgr , ben_cwb_person_info last_mgr_per , hr_lookups EmpCatLkp , hr_lookups GrdQrtleLkp , hr_lookups PerfRatingLkp , hr_lookups PerfRatingTypeLkp , fnd_currencies plcurr , fnd_currencies opt1curr , fnd_currencies opt2curr , fnd_currencies opt3curr , fnd_currencies opt4curr WHERE grp_pl.oipl_id = -1 AND grp_pl.group_pl_id = grp_pl.grpplid AND grp_pl.oipl_id = grp_pl.group_oipl_id AND pl1.group_pl_id(+) = grp_pl.group_pl_id AND pl1.lf_evt_ocrd_dt(+) = grp_pl.grplfevtorcddt AND pl1.actual_flag = 'Y' AND pl1.oipl_id(+) = -1 AND TEMP1.oip1plid (+) = pl1.pl_id AND TEMP1.opt1_lf_evt_ocrd_dt(+) = pl1.lf_evt_ocrd_dt AND temp1.Oipl1Id(+) <> -1 AND TEMP1.Opt1OiplOrdrNum(+) = 1 AND TEMP2.oip2plid(+) = pl1.pl_id AND TEMP2.opt2_lf_evt_ocrd_dt(+) = pl1.lf_evt_ocrd_dt AND temp2.Oipl2Id(+) <> -1 AND temp2.Opt2OiplOrdrNum(+) = 2 AND TEMP3.oip3plid(+) = pl1.pl_id AND TEMP3.opt3_lf_evt_ocrd_dt(+) = pl1.lf_evt_ocrd_dt AND TEMP3.Oipl3Id(+) <> -1 AND temp3.Opt3OiplOrdrNum(+) = 3 AND temp4.oip4plid(+) = pl1.pl_id AND temp4.opt4_lf_evt_ocrd_dt(+) = pl1.lf_evt_ocrd_dt AND temp4.Oipl4Id(+) <> -1 AND temp4.Opt4OiplOrdrNum(+) = 4 AND hrchy.lvl_num > 0 AND hrchy.emp_per_in_ler_id = plrt.group_per_in_ler_id AND plrt.elig_flag = 'Y' AND plrt.pl_id = pl1.pl_id AND plrt.lf_evt_ocrd_dt = grp_pl.grplfevtorcddt AND plrt.oipl_id = -1 AND plrt.group_pl_id = plxchg.group_pl_id AND plrt.lf_evt_ocrd_dt = plxchg.lf_evt_ocrd_dt AND plrt.currency = plxchg.currency AND plrt.currency = plcurr.currency_code AND hrchy.emp_per_in_ler_id = opt1rt.group_per_in_ler_id AND opt1rt.pl_id = plrt.pl_id AND opt1rt.lf_evt_ocrd_dt = plrt.lf_evt_ocrd_dt AND opt1rt.oipl_id = NVL(temp1.Oipl1Id, -1) AND opt1rt.group_pl_id = opt1xchg.group_pl_id AND opt1rt.lf_evt_ocrd_dt = opt1xchg.lf_evt_ocrd_dt AND opt1rt.currency = opt1xchg.currency AND opt1rt.currency = opt1curr.currency_code AND hrchy.emp_per_in_ler_id = opt2rt.group_per_in_ler_id AND opt2rt.pl_id = plrt.pl_id AND opt2rt.lf_evt_ocrd_dt = plrt.lf_evt_ocrd_dt AND opt2rt.oipl_id = NVL(temp2.Oipl2Id, -1) AND opt2rt.group_pl_id = opt2xchg.group_pl_id AND opt2rt.lf_evt_ocrd_dt = opt2xchg.lf_evt_ocrd_dt AND opt2rt.currency = opt2xchg.currency AND opt2rt.currency = opt2curr.currency_code AND hrchy.emp_per_in_ler_id = opt3rt.group_per_in_ler_id AND opt3rt.pl_id = plrt.pl_id AND opt3rt.lf_evt_ocrd_dt = plrt.lf_evt_ocrd_dt AND opt3rt.oipl_id = NVL(temp3.Oipl3Id, -1) AND opt3rt.group_pl_id = opt3xchg.group_pl_id AND opt3rt.lf_evt_ocrd_dt = opt3xchg.lf_evt_ocrd_dt AND opt3rt.currency = opt3xchg.currency AND opt3rt.currency = opt3curr.currency_code AND hrchy.emp_per_in_ler_id = opt4rt.group_per_in_ler_id AND opt4rt.pl_id = plrt.pl_id AND opt4rt.lf_evt_ocrd_dt = plrt.lf_evt_ocrd_dt AND opt4rt.oipl_id = NVL(temp4.Oipl4Id, -1) AND opt4rt.group_pl_id = opt4xchg.group_pl_id AND opt4rt.lf_evt_ocrd_dt = opt4xchg.lf_evt_ocrd_dt AND opt4rt.currency = opt4xchg.currency AND opt4rt.currency = opt4curr.currency_code AND per.group_per_in_ler_id = hrchy.emp_per_in_ler_id AND per.assignment_status_type_id = ast.assignment_status_type_id AND ast.LANGUAGE(+) = USERENV('lang') AND per.business_group_id = bg.organization_id(+) AND bg.LANGUAGE(+) = USERENV('lang') AND per.organization_id = org.organization_id(+) AND org.LANGUAGE(+) = USERENV('lang') AND per.location_id = loc.location_id(+) AND loc.LANGUAGE(+) = USERENV('lang') AND per.job_id = job.job_id(+) AND job.LANGUAGE(+) = USERENV('lang') AND per.position_id = pos.position_id(+) AND pos.LANGUAGE(+) = USERENV('lang') AND per.grade_id = grd.grade_id(+) AND grd.LANGUAGE(+) = USERENV('lang') AND hrchy.emp_per_in_ler_id = last_mgr.emp_per_in_ler_id(+) AND ( hrchy.lvl_num - 1 ) = last_mgr.lvl_num(+) AND last_mgr.lvl_num(+) > 0 AND last_mgr.mgr_per_in_ler_id = last_mgr_per.group_per_in_ler_id(+) AND EmpCatLkp.lookup_type (+) = 'EMP_CAT' AND EmpCatLkp.lookup_code (+) = per.emp_category AND EmpCatLkp.enabled_flag (+) = 'Y' AND GrdQrtleLkp.lookup_type (+) = 'BEN_CWB_QUAR_IN_GRD' AND GrdQrtleLkp.lookup_code (+) = per.grd_quartile AND GrdQrtleLkp.enabled_flag (+) = 'Y' AND PerfRatingLkp.lookup_type (+) = 'PERFORMANCE_RATING' AND PerfRatingLkp.lookup_code (+) = per.performance_rating AND PerfRatingLkp.enabled_flag (+) = 'Y' AND PerfRatingTypeLkp.lookup_type (+) = 'EMP_INTERVIEW_TYPE' AND PerfRatingTypeLkp.lookup_code (+) = per.performance_rating_type AND PerfRatingTypeLkp.enabled_flag (+) = 'Y'

View Text - HTML Formatted

SELECT /*+ ORDERED */ DECODE(GRP_PL.NAME_PROFILE
, 'CN'
, PER.CUSTOM_NAME
, 'BN'
, PER.BRIEF_NAME
, PER.FULL_NAME) EMP_NAME
, DECODE(GRP_PL.NAME_PROFILE
, 'CN'
, PER.SUPERVISOR_CUSTOM_NAME
, 'BN'
, PER.SUPERVISOR_BRIEF_NAME
, PER.SUPERVISOR_FULL_NAME) MGR_NAME
, TO_NUMBER ( ( SELECT RANK.AEI_INFORMATION1
FROM PER_ASSIGNMENT_EXTRA_INFO RANK
, BEN_CWB_PERSON_INFO MPER
WHERE RANK.ASSIGNMENT_ID = PER.ASSIGNMENT_ID
AND RANK.INFORMATION_TYPE = 'CWBRANK'
AND RANK.AEI_INFORMATION3 IS NULL
AND RANK.AEI_INFORMATION2 = MPER.PERSON_ID
AND MPER.GROUP_PER_IN_LER_ID = HRCHY.MGR_PER_IN_LER_ID ) ) RANK
, PER.YEARS_EMPLOYED YEARS_EMPLOYED
, ROUND(PER.BASE_SALARY * PER.PAY_ANNULIZATION_FACTOR / NVL(PL1.PLANUFACTOR
, GRP_PL.GRPUFACTOR)
, NVL( ( SELECT PRECISION
FROM FND_CURRENCIES BASECURR
WHERE BASECURR.CURRENCY_CODE = PER.BASE_SALARY_CURRENCY )
, 2)) BASE_SALARY
, NVL(PL1.PLNAME
, GRP_PL.GRPPLNAME) PL_NAME
, PLXCHG.XCHG_RATE PL_XCHG_RATE
, ROUND(PLRT.STAT_SAL_VAL
, PLCURR.PRECISION) PL_STAT_SAL_VAL
, ROUND(PLRT.ELIG_SAL_VAL
, PLCURR.PRECISION) PL_ELIG_SAL_VAL
, ROUND(PLRT.TOT_COMP_VAL
, PLCURR.PRECISION) PL_TOT_COMP_VAL
, ROUND(PLRT.OTH_COMP_VAL
, PLCURR.PRECISION) PL_OTH_COMP_VAL
, ROUND(PLRT.WS_VAL
, PLCURR.PRECISION) PL_WS_VAL
, ROUND(PLRT.WS_MN_VAL
, PLCURR.PRECISION) PL_WS_MIN_VAL
, ROUND(PLRT.WS_MX_VAL
, PLCURR.PRECISION) PL_WS_MAX_VAL
, ROUND(PLRT.WS_INCR_VAL
, PLCURR.PRECISION) PL_WS_INCR_VAL
, ROUND(PLRT.REC_VAL
, PLCURR.PRECISION) PL_REC_VAL
, ROUND(PLRT.REC_MN_VAL
, PLCURR.PRECISION) PL_REC_MIN_VAL
, ROUND(PLRT.REC_MX_VAL
, PLCURR.PRECISION) PL_REC_MAX_VAL
, ROUND(PLRT.MISC1_VAL
, PLCURR.PRECISION) PL_MISC1_VAL
, ROUND(PLRT.MISC2_VAL
, PLCURR.PRECISION) PL_MISC2_VAL
, ROUND(PLRT.MISC3_VAL
, PLCURR.PRECISION) PL_MISC3_VAL
, PLRT.WS_VAL_LAST_UPD_DATE PL_WS_LAST_UPD_DATE
, ( SELECT PPL.FULL_NAME
FROM PER_ALL_PEOPLE_F PPL
WHERE PPL.PERSON_ID = PLRT.WS_VAL_LAST_UPD_BY
AND TRUNC(SYSDATE) BETWEEN PPL.EFFECTIVE_START_DATE
AND PPL.EFFECTIVE_END_DATE ) PL_WS_LAST_UPD_NAME
, TEMP1.OPT1NAME OPT1_NAME
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, OPT1XCHG.XCHG_RATE
, NULL)
, NULL) OPT1_XCHG_RATE
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, ROUND(OPT1RT.STAT_SAL_VAL
, OPT1CURR.PRECISION)
, NULL)
, NULL) OPT1_STAT_SAL_VAL
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, ROUND(OPT1RT.ELIG_SAL_VAL
, OPT1CURR.PRECISION)
, NULL)
, NULL) OPT1_ELIG_SAL_VAL
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, ROUND(OPT1RT.TOT_COMP_VAL
, OPT1CURR.PRECISION)
, NULL)
, NULL) OPT1_TOT_COMP_VAL
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, ROUND(OPT1RT.OTH_COMP_VAL
, OPT1CURR.PRECISION)
, NULL)
, NULL) OPT1_OTH_COMP_VAL
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, ROUND(OPT1RT.WS_VAL
, OPT1CURR.PRECISION)
, NULL)
, NULL) OPT1_WS_VAL
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, ROUND(OPT1RT.WS_MN_VAL
, OPT1CURR.PRECISION)
, NULL)
, NULL) OPT1_WS_MIN_VAL
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, ROUND(OPT1RT.WS_MX_VAL
, OPT1CURR.PRECISION)
, NULL)
, NULL) OPT1_WS_MAX_VAL
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, ROUND(OPT1RT.WS_INCR_VAL
, OPT1CURR.PRECISION)
, NULL)
, NULL) OPT1_WS_INCR_VAL
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, ROUND(OPT1RT.REC_VAL
, OPT1CURR.PRECISION)
, NULL)
, NULL) OPT1_REC_VAL
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, ROUND(OPT1RT.REC_MN_VAL
, OPT1CURR.PRECISION)
, NULL)
, NULL) OPT1_REC_MIN_VAL
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, ROUND(OPT1RT.REC_MX_VAL
, OPT1CURR.PRECISION)
, NULL)
, NULL) OPT1_REC_MAX_VAL
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, ROUND(OPT1RT.MISC1_VAL
, OPT1CURR.PRECISION)
, NULL)
, NULL) OPT1_MISC1_VAL
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, ROUND(OPT1RT.MISC2_VAL
, OPT1CURR.PRECISION)
, NULL)
, NULL) OPT1_MISC2_VAL
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, ROUND(OPT1RT.MISC3_VAL
, OPT1CURR.PRECISION)
, NULL)
, NULL) OPT1_MISC3_VAL
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, OPT1RT.WS_VAL_LAST_UPD_DATE
, NULL)
, NULL) OPT1_WS_LAST_UPD_DATE
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, ( SELECT PPL.FULL_NAME
FROM PER_ALL_PEOPLE_F PPL
WHERE PPL.PERSON_ID = OPT1RT.WS_VAL_LAST_UPD_BY
AND TRUNC(SYSDATE) BETWEEN PPL.EFFECTIVE_START_DATE
AND PPL.EFFECTIVE_END_DATE )
, NULL)
, NULL) OPT1_WS_LAST_UPD_NAME
, TEMP2.OPT2NAME OPT2_NAME
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, OPT2XCHG.XCHG_RATE
, NULL)
, NULL) OPT2_XCHG_RATE
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, ROUND(OPT2RT.STAT_SAL_VAL
, OPT2CURR.PRECISION)
, NULL)
, NULL) OPT2_STAT_SAL_VAL
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, ROUND(OPT2RT.ELIG_SAL_VAL
, OPT2CURR.PRECISION)
, NULL)
, NULL) OPT2_ELIG_SAL_VAL
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, ROUND(OPT2RT.TOT_COMP_VAL
, OPT2CURR.PRECISION)
, NULL)
, NULL) OPT2_TOT_COMP_VAL
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, ROUND(OPT2RT.OTH_COMP_VAL
, OPT2CURR.PRECISION)
, NULL)
, NULL) OPT2_OTH_COMP_VAL
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, ROUND(OPT2RT.WS_VAL
, OPT2CURR.PRECISION)
, NULL)
, NULL) OPT2_WS_VAL
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, ROUND(OPT2RT.WS_MN_VAL
, OPT2CURR.PRECISION)
, NULL)
, NULL) OPT2_WS_MIN_VAL
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, ROUND(OPT2RT.WS_MX_VAL
, OPT2CURR.PRECISION)
, NULL)
, NULL) OPT2_WS_MAX_VAL
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, ROUND(OPT2RT.WS_INCR_VAL
, OPT2CURR.PRECISION)
, NULL)
, NULL) OPT2_WS_INCR_VAL
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, ROUND(OPT2RT.REC_VAL
, OPT2CURR.PRECISION)
, NULL)
, NULL) OPT2_REC_VAL
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, ROUND(OPT2RT.REC_MN_VAL
, OPT2CURR.PRECISION)
, NULL)
, NULL) OPT2_REC_MIN_VAL
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, ROUND(OPT2RT.REC_MX_VAL
, OPT2CURR.PRECISION)
, NULL)
, NULL) OPT2_REC_MAX_VAL
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, ROUND(OPT2RT.MISC1_VAL
, OPT2CURR.PRECISION)
, NULL)
, NULL) OPT2_MISC1_VAL
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, ROUND(OPT2RT.MISC2_VAL
, OPT2CURR.PRECISION)
, NULL)
, NULL) OPT2_MISC2_VAL
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, ROUND(OPT2RT.MISC3_VAL
, OPT2CURR.PRECISION)
, NULL)
, NULL) OPT2_MISC3_VAL
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, OPT2RT.WS_VAL_LAST_UPD_DATE
, NULL)
, NULL) OPT2_WS_LAST_UPD_DATE
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, ( SELECT PPL.FULL_NAME
FROM PER_ALL_PEOPLE_F PPL
WHERE PPL.PERSON_ID = OPT2RT.WS_VAL_LAST_UPD_BY
AND TRUNC(SYSDATE) BETWEEN PPL.EFFECTIVE_START_DATE
AND PPL.EFFECTIVE_END_DATE )
, NULL)
, NULL) OPT2_WS_LAST_UPD_NAME
, TEMP3.OPT3NAME OPT3_NAME
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, OPT3XCHG.XCHG_RATE
, NULL)
, NULL) OPT3_XCHG_RATE
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, ROUND(OPT3RT.STAT_SAL_VAL
, OPT3CURR.PRECISION)
, NULL)
, NULL) OPT3_STAT_SAL_VAL
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, ROUND(OPT3RT.ELIG_SAL_VAL
, OPT3CURR.PRECISION)
, NULL)
, NULL) OPT3_ELIG_SAL_VAL
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, ROUND(OPT3RT.TOT_COMP_VAL
, OPT3CURR.PRECISION)
, NULL)
, NULL) OPT3_TOT_COMP_VAL
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, ROUND(OPT3RT.OTH_COMP_VAL
, OPT3CURR.PRECISION)
, NULL)
, NULL) OPT3_OTH_COMP_VAL
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, ROUND(OPT3RT.WS_VAL
, OPT3CURR.PRECISION)
, NULL)
, NULL) OPT3_WS_VAL
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, ROUND(OPT3RT.WS_MN_VAL
, OPT3CURR.PRECISION)
, NULL)
, NULL) OPT3_WS_MIN_VAL
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, ROUND(OPT3RT.WS_MX_VAL
, OPT3CURR.PRECISION)
, NULL)
, NULL) OPT3_WS_MAX_VAL
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, ROUND(OPT3RT.WS_INCR_VAL
, OPT3CURR.PRECISION)
, NULL)
, NULL) OPT3_WS_INCR_VAL
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, ROUND(OPT3RT.REC_VAL
, OPT3CURR.PRECISION)
, NULL)
, NULL) OPT3_REC_VAL
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, ROUND(OPT3RT.REC_MN_VAL
, OPT3CURR.PRECISION)
, NULL)
, NULL) OPT3_REC_MIN_VAL
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, ROUND(OPT3RT.REC_MX_VAL
, OPT3CURR.PRECISION)
, NULL)
, NULL) OPT3_REC_MAX_VAL
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, ROUND(OPT3RT.MISC1_VAL
, OPT3CURR.PRECISION)
, NULL)
, NULL) OPT3_MISC1_VAL
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, ROUND(OPT3RT.MISC2_VAL
, OPT3CURR.PRECISION)
, NULL)
, NULL) OPT3_MISC2_VAL
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, ROUND(OPT3RT.MISC3_VAL
, OPT3CURR.PRECISION)
, NULL)
, NULL) OPT3_MISC3_VAL
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, OPT3RT.WS_VAL_LAST_UPD_DATE
, NULL)
, NULL) OPT3_WS_LAST_UPD_DATE
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, ( SELECT PPL.FULL_NAME
FROM PER_ALL_PEOPLE_F PPL
WHERE PPL.PERSON_ID = OPT3RT.WS_VAL_LAST_UPD_BY
AND TRUNC(SYSDATE) BETWEEN PPL.EFFECTIVE_START_DATE
AND PPL.EFFECTIVE_END_DATE )
, NULL)
, NULL) OPT3_WS_LAST_UPD_NAME
, TEMP4.OPT4NAME OPT4_NAME
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, OPT4XCHG.XCHG_RATE
, NULL)
, NULL) OPT4_XCHG_RATE
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, ROUND(OPT4RT.STAT_SAL_VAL
, OPT4CURR.PRECISION)
, NULL)
, NULL) OPT4_STAT_SAL_VAL
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, ROUND(OPT4RT.ELIG_SAL_VAL
, OPT4CURR.PRECISION)
, NULL)
, NULL) OPT4_ELIG_SAL_VAL
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, ROUND(OPT4RT.TOT_COMP_VAL
, OPT4CURR.PRECISION)
, NULL)
, NULL) OPT4_TOT_COMP_VAL
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, ROUND(OPT4RT.OTH_COMP_VAL
, OPT4CURR.PRECISION)
, NULL)
, NULL) OPT4_OTH_COMP_VAL
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, ROUND(OPT4RT.WS_VAL
, OPT4CURR.PRECISION)
, NULL)
, NULL) OPT4_WS_VAL
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, ROUND(OPT4RT.WS_MN_VAL
, OPT4CURR.PRECISION)
, NULL)
, NULL) OPT4_WS_MIN_VAL
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, ROUND(OPT4RT.WS_MX_VAL
, OPT4CURR.PRECISION)
, NULL)
, NULL) OPT4_WS_MAX_VAL
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, ROUND(OPT4RT.WS_INCR_VAL
, OPT4CURR.PRECISION)
, NULL)
, NULL) OPT4_WS_INCR_VAL
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, ROUND(OPT4RT.REC_VAL
, OPT4CURR.PRECISION)
, NULL)
, NULL) OPT4_REC_VAL
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, ROUND(OPT4RT.REC_MN_VAL
, OPT4CURR.PRECISION)
, NULL)
, NULL) OPT4_REC_MIN_VAL
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, ROUND(OPT4RT.REC_MX_VAL
, OPT4CURR.PRECISION)
, NULL)
, NULL) OPT4_REC_MAX_VAL
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, ROUND(OPT4RT.MISC1_VAL
, OPT4CURR.PRECISION)
, NULL)
, NULL) OPT4_MISC1_VAL
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, ROUND(OPT4RT.MISC2_VAL
, OPT4CURR.PRECISION)
, NULL)
, NULL) OPT4_MISC2_VAL
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, ROUND(OPT4RT.MISC3_VAL
, OPT4CURR.PRECISION)
, NULL)
, NULL) OPT4_MISC3_VAL
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, OPT4RT.WS_VAL_LAST_UPD_DATE
, NULL)
, NULL) OPT4_WS_LAST_UPD_DATE
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, ( SELECT PPL.FULL_NAME
FROM PER_ALL_PEOPLE_F PPL
WHERE PPL.PERSON_ID = OPT4RT.WS_VAL_LAST_UPD_BY
AND TRUNC(SYSDATE) BETWEEN PPL.EFFECTIVE_START_DATE
AND PPL.EFFECTIVE_END_DATE )
, NULL)
, NULL) OPT4_WS_LAST_UPD_NAME
, PER.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, EMPCATLKP.MEANING EMP_CATEGORY
, AST.USER_STATUS ASSIGNMENT_STATUS
, PER.PEOPLE_GROUP_NAME PEOPLE_GROUP_NAME
, PER.EMAIL_ADDRESS EMAIL_ADDR
, PER.START_DATE START_DATE
, PER.ORIGINAL_START_DATE ORIGINAL_START_DATE
, PER.NORMAL_HOURS NORMAL_HOURS
, PER.PAYROLL_NAME PAYROLL_NAME
, BG.NAME BUSINESS_GROUP_NAME
, ORG.NAME ORG_NAME
, NVL(LOC.DESCRIPTION
, LOC.LOCATION_CODE) LOC_NAME
, JOB.NAME JOB_NAME
, POS.NAME POS_NAME
, GRD.NAME GRD_NAME
, PER.LEGISLATION_CODE COUNTRY
, PER.YEARS_IN_JOB YEARS_IN_JOB
, PER.YEARS_IN_POSITION YEARS_IN_POSITION
, PER.YEARS_IN_GRADE YEARS_IN_GRADE
, LTRIM(TO_CHAR(PER.GRD_MIN_VAL*PER.GRADE_ANNULIZATION_FACTOR/PL1.PLANUFACTOR
, '999G999G999G990D00')) ||'-' ||LTRIM(TO_CHAR(PER.GRD_MAX_VAL*PER.GRADE_ANNULIZATION_FACTOR/PL1.PLANUFACTOR
, '999G999G999G990D00')) GRADE_RANGE
, ROUND(PER.GRD_MID_POINT *PER.GRADE_ANNULIZATION_FACTOR/PL1.PLANUFACTOR
, 2) GRADE_MID_POINT
, GRDQRTLELKP.MEANING GRD_QUARTILE
, PER.GRD_COMPARATIO GRD_COMPARATIO
, PERFRATINGLKP.MEANING PERFORMANCE_RATING
, PERFRATINGTYPELKP.MEANING PERFORMANCE_RATING_TYPE
, PER.PERFORMANCE_RATING_DATE PERFORMANCE_RATING_DATE
, TO_NUMBER ( ( SELECT LST.AEI_INFORMATION1
FROM PER_ASSIGNMENT_EXTRA_INFO LST
WHERE LST.ASSIGNMENT_ID = PER.ASSIGNMENT_ID
AND LST.AEI_INFORMATION2 = LAST_MGR_PER.PERSON_ID
AND LST.AEI_INFORMATION3 IS NULL
AND LST.INFORMATION_TYPE = 'CWBRANK' ) ) LAST_RANK
, LAST_MGR_PER.FULL_NAME LAST_MGR_NAME
, TO_NUMBER(NULL) RANK_QUARTILE
, TO_NUMBER(NULL) TOTAL_RANK
, ( SELECT HLK.MEANING
FROM BEN_TRANSACTION TXN
, HR_LOOKUPS HLK
WHERE TXN.TRANSACTION_ID = PER.ASSIGNMENT_ID
AND TXN.TRANSACTION_TYPE = 'CWBASG' || GRP_PL.ASG_DATE
AND HLK.LOOKUP_CODE = TXN.ATTRIBUTE3
AND TXN.ATTRIBUTE3 IS NOT NULL
AND HLK.LOOKUP_TYPE = 'EMP_ASSIGN_REASON'
AND HLK.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(HLK.START_DATE_ACTIVE
, SYSDATE)
AND NVL(HLK.END_DATE_ACTIVE
, SYSDATE) ) CHANGE_REASON
, PER.BASE_SALARY_CHANGE_DATE BASE_SALARY_CHANGE_DATE
, PLRT.LF_EVT_OCRD_DT LF_EVT_OCRD_DT
, HRCHY.MGR_PER_IN_LER_ID MGR_LER_ID
, DECODE(TEMP1.OIPL1ID
, NULL
, BEN_CWB_WEBADI_UTILS.ENCRYPT(RPAD(TO_CHAR(PLRT.PERSON_RATE_ID)
, 8
, ' '))
, NULL) PL_PERSON_RATE_ID
, NVL2(TEMP1.OIPL1ID
, BEN_CWB_WEBADI_UTILS.ENCRYPT(RPAD(TO_CHAR(OPT1RT.PERSON_RATE_ID)
, 8
, ' '))
, NULL) P_OPT1_PERSON_RATE_ID
, NVL2(TEMP2.OIPL2ID
, BEN_CWB_WEBADI_UTILS.ENCRYPT(RPAD(TO_CHAR(OPT2RT.PERSON_RATE_ID)
, 8
, ' '))
, NULL) P_OPT2_PERSON_RATE_ID
, NVL2(TEMP3.OIPL3ID
, BEN_CWB_WEBADI_UTILS.ENCRYPT(RPAD(TO_CHAR(OPT3RT.PERSON_RATE_ID)
, 8
, ' '))
, NULL) P_OPT3_PERSON_RATE_ID
, NVL2(TEMP4.OIPL4ID
, BEN_CWB_WEBADI_UTILS.ENCRYPT(RPAD(TO_CHAR(OPT4RT.PERSON_RATE_ID)
, 8
, ' '))
, NULL) P_OPT4_PERSON_RATE_ID
, HRCHY.LVL_NUM
, PER.CUSTOM_SEGMENT1 CUSTOM_SEGMENT1
, PER.CUSTOM_SEGMENT2 CUSTOM_SEGMENT2
, PER.CUSTOM_SEGMENT3 CUSTOM_SEGMENT3
, PER.CUSTOM_SEGMENT4 CUSTOM_SEGMENT4
, PER.CUSTOM_SEGMENT5 CUSTOM_SEGMENT5
, PER.CUSTOM_SEGMENT6 CUSTOM_SEGMENT6
, PER.CUSTOM_SEGMENT7 CUSTOM_SEGMENT7
, PER.CUSTOM_SEGMENT8 CUSTOM_SEGMENT8
, PER.CUSTOM_SEGMENT9 CUSTOM_SEGMENT9
, PER.CUSTOM_SEGMENT10 CUSTOM_SEGMENT10
, PER.CUSTOM_SEGMENT11 CUSTOM_SEGMENT11
, PER.CUSTOM_SEGMENT12 CUSTOM_SEGMENT12
, PER.CUSTOM_SEGMENT13 CUSTOM_SEGMENT13
, PER.CUSTOM_SEGMENT14 CUSTOM_SEGMENT14
, PER.CUSTOM_SEGMENT15 CUSTOM_SEGMENT15
, ( SELECT HR_GENERAL.DECODE_LOOKUP('PERFORMANCE_RATING'
, PTXN.ATTRIBUTE3)
FROM BEN_TRANSACTION PTXN
WHERE PTXN.TRANSACTION_ID = PER.ASSIGNMENT_ID
AND PTXN.TRANSACTION_TYPE = 'CWBPERF' || GRP_PL.PERF_DATE || GRP_PL.PERF_TYPE ) PROPOSED_PERFORMANCE_RATING
, ( SELECT JOB1.NAME
FROM BEN_TRANSACTION ATXN
, PER_JOBS_TL JOB1
WHERE ATXN.TRANSACTION_ID = PER.ASSIGNMENT_ID
AND ATXN.TRANSACTION_TYPE = 'CWBASG' || GRP_PL.ASG_DATE
AND TO_NUMBER(ATXN.ATTRIBUTE5) = JOB1.JOB_ID
AND JOB1.LANGUAGE = USERENV('LANG') ) PROPOSED_JOB
, NVL(PL1.PLUNITS
, PLRT.CURRENCY) PLAN_UOM
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, NVL(TEMP1.OPT1UNITS
, OPT1RT.CURRENCY)
, NULL)
, NULL) OPT1_UOM
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, NVL(TEMP2.OPT2UNITS
, OPT2RT.CURRENCY)
, NULL)
, NULL) OPT2_UOM
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, NVL(TEMP3.OPT3UNITS
, OPT3RT.CURRENCY)
, NULL)
, NULL) OPT3_UOM
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, NVL(TEMP4.OPT4UNITS
, OPT4RT.CURRENCY)
, NULL)
, NULL) OPT4_UOM
, ( SELECT GRD1.NAME
FROM BEN_TRANSACTION ATXN
, PER_GRADES_TL GRD1
WHERE ATXN.TRANSACTION_ID = PER.ASSIGNMENT_ID
AND ATXN.TRANSACTION_TYPE = 'CWBASG' || GRP_PL.ASG_DATE
AND TO_NUMBER(ATXN.ATTRIBUTE7) = GRD1.GRADE_ID
AND GRD1.LANGUAGE = USERENV('LANG') ) PROPOSED_GRADE
, ( SELECT POS1.NAME
FROM BEN_TRANSACTION ATXN
, HR_ALL_POSITIONS_F_TL POS1
WHERE ATXN.TRANSACTION_ID = PER.ASSIGNMENT_ID
AND ATXN.TRANSACTION_TYPE = 'CWBASG' || GRP_PL.ASG_DATE
AND TO_NUMBER(ATXN.ATTRIBUTE6) = POS1.POSITION_ID
AND POS1.LANGUAGE = USERENV('LANG') ) PROPOSED_POSITION
, TO_CHAR(NULL) PROPOSED_GROUP
, PER.CPI_ATTRIBUTE_CATEGORY CPI_ATTRIBUTE_CATEGORY
, PER.CPI_ATTRIBUTE1 CPI_ATTRIBUTE1
, PER.CPI_ATTRIBUTE2 CPI_ATTRIBUTE2
, PER.CPI_ATTRIBUTE3 CPI_ATTRIBUTE3
, PER.CPI_ATTRIBUTE4 CPI_ATTRIBUTE4
, PER.CPI_ATTRIBUTE5 CPI_ATTRIBUTE5
, PER.CPI_ATTRIBUTE6 CPI_ATTRIBUTE6
, PER.CPI_ATTRIBUTE7 CPI_ATTRIBUTE7
, PER.CPI_ATTRIBUTE8 CPI_ATTRIBUTE8
, PER.CPI_ATTRIBUTE9 CPI_ATTRIBUTE9
, PER.CPI_ATTRIBUTE10 CPI_ATTRIBUTE10
, PER.CPI_ATTRIBUTE11 CPI_ATTRIBUTE11
, PER.CPI_ATTRIBUTE12 CPI_ATTRIBUTE12
, PER.CPI_ATTRIBUTE13 CPI_ATTRIBUTE13
, PER.CPI_ATTRIBUTE14 CPI_ATTRIBUTE14
, PER.CPI_ATTRIBUTE15 CPI_ATTRIBUTE15
, PER.CPI_ATTRIBUTE16 CPI_ATTRIBUTE16
, PER.CPI_ATTRIBUTE17 CPI_ATTRIBUTE17
, PER.CPI_ATTRIBUTE18 CPI_ATTRIBUTE18
, PER.CPI_ATTRIBUTE19 CPI_ATTRIBUTE19
, PER.CPI_ATTRIBUTE20 CPI_ATTRIBUTE20
, PER.CPI_ATTRIBUTE21 CPI_ATTRIBUTE21
, PER.CPI_ATTRIBUTE22 CPI_ATTRIBUTE22
, PER.CPI_ATTRIBUTE23 CPI_ATTRIBUTE23
, PER.CPI_ATTRIBUTE24 CPI_ATTRIBUTE24
, PER.CPI_ATTRIBUTE25 CPI_ATTRIBUTE25
, PER.CPI_ATTRIBUTE26 CPI_ATTRIBUTE26
, PER.CPI_ATTRIBUTE27 CPI_ATTRIBUTE27
, PER.CPI_ATTRIBUTE28 CPI_ATTRIBUTE28
, PER.CPI_ATTRIBUTE29 CPI_ATTRIBUTE29
, PER.CPI_ATTRIBUTE30 CPI_ATTRIBUTE30
, PER.CUSTOM_SEGMENT16 CUSTOM_SEGMENT16
, PER.CUSTOM_SEGMENT17 CUSTOM_SEGMENT17
, PER.CUSTOM_SEGMENT18 CUSTOM_SEGMENT18
, PER.CUSTOM_SEGMENT19 CUSTOM_SEGMENT19
, PER.CUSTOM_SEGMENT20 CUSTOM_SEGMENT20
, PLRT.CURRENCY PL_CURRENCY
, NVL2(TEMP1.OIPL1ID
, OPT1RT.CURRENCY
, NULL) OPT1_CURRENCY
, NVL2(TEMP2.OIPL2ID
, OPT2RT.CURRENCY
, NULL) OPT2_CURRENCY
, NVL2(TEMP3.OIPL3ID
, OPT3RT.CURRENCY
, NULL) OPT3_CURRENCY
, NVL2(TEMP4.OIPL4ID
, OPT4RT.CURRENCY
, NULL) OPT4_CURRENCY
, PLRT.WS_RT_START_DATE PL_WS_RT_START_DATE
, NVL2(TEMP1.OIPL1ID
, DECODE(OPT1RT.ELIG_FLAG
, 'Y'
, OPT1RT.WS_RT_START_DATE
, NULL)
, NULL) OPT1_WS_RT_START_DATE
, NVL2(TEMP2.OIPL2ID
, DECODE(OPT2RT.ELIG_FLAG
, 'Y'
, OPT2RT.WS_RT_START_DATE
, NULL)
, NULL) OPT2_WS_RT_START_DATE
, NVL2(TEMP3.OIPL3ID
, DECODE(OPT3RT.ELIG_FLAG
, 'Y'
, OPT3RT.WS_RT_START_DATE
, NULL)
, NULL) OPT3_WS_RT_START_DATE
, NVL2(TEMP4.OIPL4ID
, DECODE(OPT4RT.ELIG_FLAG
, 'Y'
, OPT4RT.WS_RT_START_DATE
, NULL)
, NULL) OPT4_WS_RT_START_DATE
FROM BEN_CWB_GROUP_HRCHY HRCHY
, BEN_CWB_PERSON_RATES PLRT
, ( SELECT GRP_PL.PL_ID GRPPLID
, GRP_PL.OIPL_ID GRPOIPLID
, GRP_PL.GROUP_PL_ID
, GRP_PL.GROUP_OIPL_ID
, GRP_PL.OIPL_ID
, GRP_PL.LF_EVT_OCRD_DT GRPLFEVTORCDDT
, GRP_PL.NAME GRPPLNAME
, TO_CHAR(GRP_PL.PERF_REVW_STRT_DT
, 'YYYY/MM/DD') PERF_DATE
, GRP_PL.EMP_INTERVIEW_TYP_CD PERF_TYPE
, TO_CHAR(GRP_PL.ASG_UPDT_EFF_DATE
, 'YYYY/MM/DD') ASG_DATE
, GRP_PL.WS_ABR_ID GRPWSABRID
, GRP_PL.ACTUAL_FLAG GRPACTULFLAG
, GRP_PL.PL_ANNULIZATION_FACTOR GRPUFACTOR
, HR_GENERAL.DECODE_LOOKUP('BEN_NNMNTRY_UOM'
, GRP_PL.WS_NNMNTRY_UOM) GRPUNITS
, GRP_PL.PL_UOM GRPCURR
, FND_PROFILE.VALUE('BEN_DISPLAY_EMPLOYEE_NAME') NAME_PROFILE
FROM BEN_CWB_PL_DSGN GRP_PL ) GRP_PL
, ( SELECT PL1.PL_ID
, PL1.LF_EVT_OCRD_DT
, PL1.OIPL_ID
, PL1.GROUP_PL_ID
, PL1.ACTUAL_FLAG
, PL1.NAME PLNAME
, PL1.OIPL_ID PLOIPLID
, PL1.WS_ABR_ID PLWSABRID
, PL1.ACTUAL_FLAG PLACTULFLAG
, PL1.PL_ANNULIZATION_FACTOR PLANUFACTOR
, HR_GENERAL.DECODE_LOOKUP('BEN_NNMNTRY_UOM'
, PL1.WS_NNMNTRY_UOM) PLUNITS
FROM BEN_CWB_PL_DSGN PL1 ) PL1
, ( SELECT OPT1.OIPL_ID OIPL1ID
, OPT1.LF_EVT_OCRD_DT OPT1_LF_EVT_OCRD_DT
, OPT1.GROUP_PL_ID OIP1GRPPLID
, OPT1.GROUP_OIPL_ID OIP1GRPOIPLID
, OPT1.PL_ID OIP1PLID
, OPT1.NAME OPT1NAME
, OPT1.WS_ABR_ID OPT1WSABRID
, OPT1.OIPL_ORDR_NUM OPT1OIPLORDRNUM
, HR_GENERAL.DECODE_LOOKUP('BEN_NNMNTRY_UOM'
, OPT1.WS_NNMNTRY_UOM) OPT1UNITS
FROM BEN_CWB_PL_DSGN OPT1 ) TEMP1
, ( SELECT OPT2.OIPL_ID OIPL2ID
, OPT2.LF_EVT_OCRD_DT OPT2_LF_EVT_OCRD_DT
, OPT2.GROUP_PL_ID OIP2GRPPLID
, OPT2.GROUP_OIPL_ID OIP2GRPOIPLID
, OPT2.PL_ID OIP2PLID
, OPT2.NAME OPT2NAME
, OPT2.WS_ABR_ID OPT2WSABRID
, OPT2.OIPL_ORDR_NUM OPT2OIPLORDRNUM
, HR_GENERAL.DECODE_LOOKUP('BEN_NNMNTRY_UOM'
, OPT2.WS_NNMNTRY_UOM) OPT2UNITS
FROM BEN_CWB_PL_DSGN OPT2 ) TEMP2
, ( SELECT OPT3.OIPL_ID OIPL3ID
, OPT3.LF_EVT_OCRD_DT OPT3_LF_EVT_OCRD_DT
, OPT3.GROUP_PL_ID OIP3GRPPLID
, OPT3.GROUP_OIPL_ID OIP3GRPOIPLID
, OPT3.PL_ID OIP3PLID
, OPT3.NAME OPT3NAME
, OPT3.WS_ABR_ID OPT3WSABRID
, OPT3.OIPL_ORDR_NUM OPT3OIPLORDRNUM
, HR_GENERAL.DECODE_LOOKUP('BEN_NNMNTRY_UOM'
, OPT3.WS_NNMNTRY_UOM) OPT3UNITS
FROM BEN_CWB_PL_DSGN OPT3 ) TEMP3
, ( SELECT OPT4.OIPL_ID OIPL4ID
, OPT4.LF_EVT_OCRD_DT OPT4_LF_EVT_OCRD_DT
, OPT4.GROUP_PL_ID OIP4GRPPLID
, OPT4.GROUP_OIPL_ID OIP4GRPOIPLID
, OPT4.PL_ID OIP4PLID
, OPT4.NAME OPT4NAME
, OPT4.WS_ABR_ID OPT4WSABRID
, OPT4.OIPL_ORDR_NUM OPT4OIPLORDRNUM
, HR_GENERAL.DECODE_LOOKUP('BEN_NNMNTRY_UOM'
, OPT4.WS_NNMNTRY_UOM) OPT4UNITS
FROM BEN_CWB_PL_DSGN OPT4 ) TEMP4
, BEN_CWB_PERSON_RATES OPT1RT
, BEN_CWB_PERSON_RATES OPT2RT
, BEN_CWB_PERSON_RATES OPT3RT
, BEN_CWB_PERSON_RATES OPT4RT
, BEN_CWB_XCHG PLXCHG
, BEN_CWB_XCHG OPT1XCHG
, BEN_CWB_XCHG OPT2XCHG
, BEN_CWB_XCHG OPT3XCHG
, BEN_CWB_XCHG OPT4XCHG
, BEN_CWB_PERSON_INFO PER
, PER_ASSIGNMENT_STATUS_TYPES_TL AST
, HR_ALL_ORGANIZATION_UNITS_TL BG
, HR_ALL_ORGANIZATION_UNITS_TL ORG
, HR_LOCATIONS_ALL_TL LOC
, PER_JOBS_TL JOB
, HR_ALL_POSITIONS_F_TL POS
, PER_GRADES_TL GRD
, BEN_CWB_GROUP_HRCHY LAST_MGR
, BEN_CWB_PERSON_INFO LAST_MGR_PER
, HR_LOOKUPS EMPCATLKP
, HR_LOOKUPS GRDQRTLELKP
, HR_LOOKUPS PERFRATINGLKP
, HR_LOOKUPS PERFRATINGTYPELKP
, FND_CURRENCIES PLCURR
, FND_CURRENCIES OPT1CURR
, FND_CURRENCIES OPT2CURR
, FND_CURRENCIES OPT3CURR
, FND_CURRENCIES OPT4CURR
WHERE GRP_PL.OIPL_ID = -1
AND GRP_PL.GROUP_PL_ID = GRP_PL.GRPPLID
AND GRP_PL.OIPL_ID = GRP_PL.GROUP_OIPL_ID
AND PL1.GROUP_PL_ID(+) = GRP_PL.GROUP_PL_ID
AND PL1.LF_EVT_OCRD_DT(+) = GRP_PL.GRPLFEVTORCDDT
AND PL1.ACTUAL_FLAG = 'Y'
AND PL1.OIPL_ID(+) = -1
AND TEMP1.OIP1PLID (+) = PL1.PL_ID
AND TEMP1.OPT1_LF_EVT_OCRD_DT(+) = PL1.LF_EVT_OCRD_DT
AND TEMP1.OIPL1ID(+) <> -1
AND TEMP1.OPT1OIPLORDRNUM(+) = 1
AND TEMP2.OIP2PLID(+) = PL1.PL_ID
AND TEMP2.OPT2_LF_EVT_OCRD_DT(+) = PL1.LF_EVT_OCRD_DT
AND TEMP2.OIPL2ID(+) <> -1
AND TEMP2.OPT2OIPLORDRNUM(+) = 2
AND TEMP3.OIP3PLID(+) = PL1.PL_ID
AND TEMP3.OPT3_LF_EVT_OCRD_DT(+) = PL1.LF_EVT_OCRD_DT
AND TEMP3.OIPL3ID(+) <> -1
AND TEMP3.OPT3OIPLORDRNUM(+) = 3
AND TEMP4.OIP4PLID(+) = PL1.PL_ID
AND TEMP4.OPT4_LF_EVT_OCRD_DT(+) = PL1.LF_EVT_OCRD_DT
AND TEMP4.OIPL4ID(+) <> -1
AND TEMP4.OPT4OIPLORDRNUM(+) = 4
AND HRCHY.LVL_NUM > 0
AND HRCHY.EMP_PER_IN_LER_ID = PLRT.GROUP_PER_IN_LER_ID
AND PLRT.ELIG_FLAG = 'Y'
AND PLRT.PL_ID = PL1.PL_ID
AND PLRT.LF_EVT_OCRD_DT = GRP_PL.GRPLFEVTORCDDT
AND PLRT.OIPL_ID = -1
AND PLRT.GROUP_PL_ID = PLXCHG.GROUP_PL_ID
AND PLRT.LF_EVT_OCRD_DT = PLXCHG.LF_EVT_OCRD_DT
AND PLRT.CURRENCY = PLXCHG.CURRENCY
AND PLRT.CURRENCY = PLCURR.CURRENCY_CODE
AND HRCHY.EMP_PER_IN_LER_ID = OPT1RT.GROUP_PER_IN_LER_ID
AND OPT1RT.PL_ID = PLRT.PL_ID
AND OPT1RT.LF_EVT_OCRD_DT = PLRT.LF_EVT_OCRD_DT
AND OPT1RT.OIPL_ID = NVL(TEMP1.OIPL1ID
, -1)
AND OPT1RT.GROUP_PL_ID = OPT1XCHG.GROUP_PL_ID
AND OPT1RT.LF_EVT_OCRD_DT = OPT1XCHG.LF_EVT_OCRD_DT
AND OPT1RT.CURRENCY = OPT1XCHG.CURRENCY
AND OPT1RT.CURRENCY = OPT1CURR.CURRENCY_CODE
AND HRCHY.EMP_PER_IN_LER_ID = OPT2RT.GROUP_PER_IN_LER_ID
AND OPT2RT.PL_ID = PLRT.PL_ID
AND OPT2RT.LF_EVT_OCRD_DT = PLRT.LF_EVT_OCRD_DT
AND OPT2RT.OIPL_ID = NVL(TEMP2.OIPL2ID
, -1)
AND OPT2RT.GROUP_PL_ID = OPT2XCHG.GROUP_PL_ID
AND OPT2RT.LF_EVT_OCRD_DT = OPT2XCHG.LF_EVT_OCRD_DT
AND OPT2RT.CURRENCY = OPT2XCHG.CURRENCY
AND OPT2RT.CURRENCY = OPT2CURR.CURRENCY_CODE
AND HRCHY.EMP_PER_IN_LER_ID = OPT3RT.GROUP_PER_IN_LER_ID
AND OPT3RT.PL_ID = PLRT.PL_ID
AND OPT3RT.LF_EVT_OCRD_DT = PLRT.LF_EVT_OCRD_DT
AND OPT3RT.OIPL_ID = NVL(TEMP3.OIPL3ID
, -1)
AND OPT3RT.GROUP_PL_ID = OPT3XCHG.GROUP_PL_ID
AND OPT3RT.LF_EVT_OCRD_DT = OPT3XCHG.LF_EVT_OCRD_DT
AND OPT3RT.CURRENCY = OPT3XCHG.CURRENCY
AND OPT3RT.CURRENCY = OPT3CURR.CURRENCY_CODE
AND HRCHY.EMP_PER_IN_LER_ID = OPT4RT.GROUP_PER_IN_LER_ID
AND OPT4RT.PL_ID = PLRT.PL_ID
AND OPT4RT.LF_EVT_OCRD_DT = PLRT.LF_EVT_OCRD_DT
AND OPT4RT.OIPL_ID = NVL(TEMP4.OIPL4ID
, -1)
AND OPT4RT.GROUP_PL_ID = OPT4XCHG.GROUP_PL_ID
AND OPT4RT.LF_EVT_OCRD_DT = OPT4XCHG.LF_EVT_OCRD_DT
AND OPT4RT.CURRENCY = OPT4XCHG.CURRENCY
AND OPT4RT.CURRENCY = OPT4CURR.CURRENCY_CODE
AND PER.GROUP_PER_IN_LER_ID = HRCHY.EMP_PER_IN_LER_ID
AND PER.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.LANGUAGE(+) = USERENV('LANG')
AND PER.BUSINESS_GROUP_ID = BG.ORGANIZATION_ID(+)
AND BG.LANGUAGE(+) = USERENV('LANG')
AND PER.ORGANIZATION_ID = ORG.ORGANIZATION_ID(+)
AND ORG.LANGUAGE(+) = USERENV('LANG')
AND PER.LOCATION_ID = LOC.LOCATION_ID(+)
AND LOC.LANGUAGE(+) = USERENV('LANG')
AND PER.JOB_ID = JOB.JOB_ID(+)
AND JOB.LANGUAGE(+) = USERENV('LANG')
AND PER.POSITION_ID = POS.POSITION_ID(+)
AND POS.LANGUAGE(+) = USERENV('LANG')
AND PER.GRADE_ID = GRD.GRADE_ID(+)
AND GRD.LANGUAGE(+) = USERENV('LANG')
AND HRCHY.EMP_PER_IN_LER_ID = LAST_MGR.EMP_PER_IN_LER_ID(+)
AND ( HRCHY.LVL_NUM - 1 ) = LAST_MGR.LVL_NUM(+)
AND LAST_MGR.LVL_NUM(+) > 0
AND LAST_MGR.MGR_PER_IN_LER_ID = LAST_MGR_PER.GROUP_PER_IN_LER_ID(+)
AND EMPCATLKP.LOOKUP_TYPE (+) = 'EMP_CAT'
AND EMPCATLKP.LOOKUP_CODE (+) = PER.EMP_CATEGORY
AND EMPCATLKP.ENABLED_FLAG (+) = 'Y'
AND GRDQRTLELKP.LOOKUP_TYPE (+) = 'BEN_CWB_QUAR_IN_GRD'
AND GRDQRTLELKP.LOOKUP_CODE (+) = PER.GRD_QUARTILE
AND GRDQRTLELKP.ENABLED_FLAG (+) = 'Y'
AND PERFRATINGLKP.LOOKUP_TYPE (+) = 'PERFORMANCE_RATING'
AND PERFRATINGLKP.LOOKUP_CODE (+) = PER.PERFORMANCE_RATING
AND PERFRATINGLKP.ENABLED_FLAG (+) = 'Y'
AND PERFRATINGTYPELKP.LOOKUP_TYPE (+) = 'EMP_INTERVIEW_TYPE'
AND PERFRATINGTYPELKP.LOOKUP_CODE (+) = PER.PERFORMANCE_RATING_TYPE
AND PERFRATINGTYPELKP.ENABLED_FLAG (+) = 'Y'