[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
INTO l_check
FROM BEN_CWB_COMP_DETAILS
WHERE Nvl(bg_id, -1) = -2
AND Nvl(org_id, -1) = -2;
PROCEDURE Insert_refresh_jobs
IS
PRAGMA autonomous_transaction;
INSERT INTO BEN_CWB_COMP_DETAILS
(GROUP_PER_IN_LER_ID,
PERSON_ID,
GROUP_PL_ID,
LF_EVT_OCRD_DT,
bg_id,
org_id,
conc_request_id)
VALUES (-2,
-2,
-2,
To_date('01-01-0001', 'dd-mm-yyyy'),
-2,
-2,
Conc_Prog_Id);
ben_cwb_cd_summary_pkg.Message_log_proc('Inserted Pilot row in BEN_CWB_COMP_DETAILS', 5);
END insert_refresh_jobs;
PROCEDURE Delete_refresh_jobs
IS
PRAGMA autonomous_transaction;
DELETE FROM BEN_CWB_COMP_DETAILS
WHERE Nvl(bg_id, -1) = -2
AND Nvl(org_id, -1) = -2
AND Nvl(GROUP_PER_IN_LER_ID, -1) = -2
AND Nvl(PERSON_ID, -1) = -2;
END delete_refresh_jobs;
CURSOR COMPANALYTICS IS SELECT per.group_per_in_ler_id
, per.group_pl_id
, per.lf_evt_ocrd_dt
, per.person_id
, temp.ploiplid
, temp.oip1plid
, temp.oip2plid
, temp.oip3plid
, temp.oip4plid
, temp.oipl1id
, temp.oipl2id
, temp.oipl3id
, temp.oipl4id
, temp.opt1oiplordrnum
, temp.opt2oiplordrnum
, temp.opt3oiplordrnum
, temp.opt4oiplordrnum
, temp.oip1grpoiplid
, temp.oip2grpoiplid
, temp.oip3grpoiplid
, temp.oip4grpoiplid
, plrt.elig_flag
, nvl2 (temp.oipl1id, opt1rt.elig_flag
, NULL) opt1_elig_flag
, nvl2 (temp.oipl2id, opt2rt.elig_flag
, NULL) opt2_elig_flag
, nvl2 (temp.oipl3id, opt3rt.elig_flag
, NULL) opt3_elig_flag
, nvl2 (temp.oipl4id, opt4rt.elig_flag
, NULL) opt4_elig_flag
, temp.planufactor
, temp.grpufactor
, pil.per_in_ler_stat_cd
, pil.procd_dt
, bgtl.organization_id bg_id
, orgtl.organization_id org_id
, per.location_id loc_id
, decode (per.grd_min_val, NULL
, to_char (NULL), ltrim (to_char (per.grd_min_val * per.grade_annulization_factor / per.pay_annulization_factor, '999G999G999G990D00'))
|| '~'
|| ltrim (to_char (per.grd_max_val * per.grade_annulization_factor / per.pay_annulization_factor, '999G999G999G990D00'))) grade_range
, per.grd_mid_point * per.grade_annulization_factor / per.pay_annulization_factor grade_mid_point
, decode (per.base_salary_frequency, 'HOURLY'
, per.base_salary, per.base_salary / decode (per.fte_factor, 0
, to_number (NULL), per.fte_factor)) prior_fte_salary
, (per.base_salary + decode (temp.plsalarycomp, 'ICM7'
, plrt.ws_val, (nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, decode (temp.opt1salarycomp, 'ICM7'
, opt1rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, decode (temp.opt2salarycomp, 'ICM7'
, opt2rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, decode (temp.opt3salarycomp, 'ICM7'
, opt3rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, decode (temp.opt4salarycomp, 'ICM7'
, opt4rt.ws_val, 0), 0)
, 0))) * (temp.planufactor / per.pay_annulization_factor)) / decode (per.base_salary_frequency, 'HOURLY'
, 1, decode (per.fte_factor, 0
, to_number (NULL), per.fte_factor)) new_fte_salary
, ben_cwb_person_info_pkg.get_grd_quartile ((per.base_salary + decode (temp.plsalarycomp, 'ICM7'
, plrt.ws_val, (nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, decode (temp.opt1salarycomp, 'ICM7'
, opt1rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, decode (temp.opt2salarycomp, 'ICM7'
, opt2rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, decode (temp.opt3salarycomp, 'ICM7'
, opt3rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, decode (temp.opt4salarycomp, 'ICM7'
, opt4rt.ws_val, 0), 0)
, 0))) * (temp.planufactor / per.pay_annulization_factor)) * per.pay_annulization_factor / decode (per.base_salary_frequency, 'HOURLY'
, 1, decode (per.fte_factor, 0
, to_number (NULL), per.fte_factor)), per.grd_min_val * per.grade_annulization_factor
, per.grd_max_val * per.grade_annulization_factor, per.grd_mid_point * per.grade_annulization_factor) new_quartile
, ben_cwb_person_info_pkg.get_grd_quintile ((per.base_salary + decode (temp.plsalarycomp, 'ICM7'
, plrt.ws_val, (nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, decode (temp.opt1salarycomp, 'ICM7'
, opt1rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, decode (temp.opt2salarycomp, 'ICM7'
, opt2rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, decode (temp.opt3salarycomp, 'ICM7'
, opt3rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, decode (temp.opt4salarycomp, 'ICM7'
, opt4rt.ws_val, 0), 0)
, 0))) * (temp.planufactor / per.pay_annulization_factor)) * per.pay_annulization_factor / decode (per.base_salary_frequency, 'HOURLY'
, 1, decode (per.fte_factor, 0
, to_number (NULL), per.fte_factor)), per.grd_min_val * per.grade_annulization_factor
, per.grd_max_val * per.grade_annulization_factor) new_quintile
, ben_cwb_person_info_pkg.get_grd_decile ((per.base_salary + decode (temp.plsalarycomp, 'ICM7'
, plrt.ws_val, (nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, decode (temp.opt1salarycomp, 'ICM7'
, opt1rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, decode (temp.opt2salarycomp, 'ICM7'
, opt2rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, decode (temp.opt3salarycomp, 'ICM7'
, opt3rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, decode (temp.opt4salarycomp, 'ICM7'
, opt4rt.ws_val, 0), 0)
, 0))) * (temp.planufactor / per.pay_annulization_factor)) * per.pay_annulization_factor / decode (per.base_salary_frequency, 'HOURLY'
, 1, decode (per.fte_factor, 0
, to_number (NULL), per.fte_factor)), per.grd_min_val * per.grade_annulization_factor
, per.grd_max_val * per.grade_annulization_factor) new_decile
, round (ben_cwb_person_info_pkg.get_grd_pct_in_range ((per.base_salary + decode (temp.plsalarycomp, 'ICM7'
, plrt.ws_val, (nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, decode (temp.opt1salarycomp, 'ICM7'
, opt1rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, decode (temp.opt2salarycomp, 'ICM7'
, opt2rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, decode (temp.opt3salarycomp, 'ICM7'
, opt3rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, decode (temp.opt4salarycomp, 'ICM7'
, opt4rt.ws_val, 0), 0)
, 0))) * (temp.planufactor / per.pay_annulization_factor)) * per.pay_annulization_factor / decode (per.base_salary_frequency, 'HOURLY'
, 1, decode (per.fte_factor, 0
, to_number (NULL), per.fte_factor)), per.grd_min_val * per.grade_annulization_factor
, per.grd_max_val * per.grade_annulization_factor), temp.pct_decs) new_percentile
, round (ben_cwb_person_info_pkg.get_grd_comparatio ((per.base_salary + decode (temp.plsalarycomp, 'ICM7'
, plrt.ws_val, (nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, decode (temp.opt1salarycomp, 'ICM7'
, opt1rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, decode (temp.opt2salarycomp, 'ICM7'
, opt2rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, decode (temp.opt3salarycomp, 'ICM7'
, opt3rt.ws_val, 0), 0)
, 0) + nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, decode (temp.opt4salarycomp, 'ICM7'
, opt4rt.ws_val, 0), 0)
, 0))) * (temp.planufactor / per.pay_annulization_factor)) * per.pay_annulization_factor / decode (per.base_salary_frequency, 'HOURLY'
, 1, decode (per.fte_factor, 0
, to_number (NULL), per.fte_factor)), per.grd_mid_point * per.grade_annulization_factor), temp.pct_decs) new_comparatio
,
(
SELECT ptxn.attribute3
FROM ben_transaction ptxn
WHERE ptxn.transaction_id = per.assignment_id
AND ptxn.transaction_type = 'CWBPERF'
|| temp.perf_date
|| temp.perf_type
) proposed_performance_rating
, jobtl.job_id job_id
, jobdef.segment1 job_flex1
, jobdef.segment2 job_flex2
, jobdef.segment3 job_flex3
, jobdef.segment4 job_flex4
, jobdef.segment5 job_flex5
,
(
SELECT to_number (atxn.attribute5)
FROM ben_transaction atxn
WHERE atxn.transaction_id = per.assignment_id
AND atxn.transaction_type = 'CWBASG'
|| temp.asg_date
) proposed_job_id
, postl.position_id position_id
,
(
SELECT to_number (atxn.attribute6)
FROM ben_transaction atxn
WHERE atxn.transaction_id = per.assignment_id
AND atxn.transaction_type = 'CWBASG'
|| temp.asg_date
) proposed_position_id
, grdtl.grade_id grade_id
,
(
SELECT to_number (atxn.attribute7)
FROM ben_transaction atxn
WHERE atxn.transaction_id = per.assignment_id
AND atxn.transaction_type = 'CWBASG'
|| temp.asg_date
) proposed_grade_id
, per.people_group_name people_group
,
(
SELECT grp.group_name
FROM ben_transaction atxn
, pay_people_groups grp
WHERE atxn.transaction_id = per.assignment_id
AND atxn.transaction_type = 'CWBASG'
|| temp.asg_date
AND to_number (atxn.attribute8) = grp.people_group_id
) proposed_group
, asttl.assignment_status_type_id assignment_status_id
, ((per.base_salary * per.pay_annulization_factor / nvl (temp.planufactor, temp.grpufactor)) /
(
SELECT xchg_rate
FROM ben_cwb_xchg xchg
WHERE xchg.group_pl_id = temp.grpplid
AND xchg.lf_evt_ocrd_dt = temp.grplfevtocrddt
AND xchg.currency = per.base_salary_currency
)) corp_base_salary
, nvl (temp.plname, temp.grpplname) pl_name
, temp.opt1name opt1_name
, temp.opt2name opt2_name
, temp.opt3name opt3_name
, temp.opt4name opt4_name
, nvl (temp.plunits, plrt.currency) pl_uom
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, nvl (temp.opt1units, opt1rt.currency), to_char (NULL))
, to_char (NULL)) opt1_uom
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, nvl (temp.opt2units, opt2rt.currency), to_char (NULL))
, to_char (NULL)) opt2_uom
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, nvl (temp.opt3units, opt3rt.currency), to_char (NULL))
, to_char (NULL)) opt3_uom
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, nvl (temp.opt4units, opt4rt.currency), to_char (NULL))
, to_char (NULL)) opt4_uom
, plrt.currency pl_currency
, nvl2 (temp.oipl1id, opt1rt.currency
, to_char (NULL)) opt1_currency
, nvl2 (temp.oipl2id, opt2rt.currency
, to_char (NULL)) opt2_currency
, nvl2 (temp.oipl3id, opt3rt.currency
, to_char (NULL)) opt3_currency
, nvl2 (temp.oipl4id, opt4rt.currency
, to_char (NULL)) opt4_currency
, plrt.elig_sal_val pl_elig_sal_val
, plrt.ws_val pl_ws_val
, round (decode (plrt.elig_sal_val, NULL
, to_number (NULL), 0
, to_number (NULL), plrt.ws_val * 100 / plrt.elig_sal_val), temp.pct_decs) pl_pct_of_elig_sal
, plrt.ws_mn_val pl_ws_min_val
, plrt.ws_mx_val pl_ws_max_val
, plrt.ws_incr_val pl_ws_incr_val
, decode (temp.plsalarycomp, 'ICM7'
, (per.base_salary * per.pay_annulization_factor / temp.planufactor) + plrt.ws_val, to_number (NULL)) pl_new_salary
, decode (plrt.ws_mn_val, NULL
, to_char (NULL), ltrim (to_char (plrt.ws_mn_val, '999G999G999G990D00'))
|| '~'
|| ltrim (to_char (plrt.ws_mx_val, '999G999G999G990D00'))) pl_ws_val_limit
, plrt.rec_val pl_rec_val
, plrt.rec_mn_val pl_rec_mn_val
, plrt.rec_mx_val pl_rec_mx_val
, decode (plrt.rec_mn_val, NULL
, to_char (NULL), ltrim (to_char (plrt.rec_mn_val, '999G999G999G990D00'))
|| '~'
|| ltrim (to_char (plrt.rec_mx_val, '999G999G999G990D00'))) pl_rec_val_limit
, round (decode (plrt.elig_sal_val, NULL
, to_number (NULL), 0
, to_number (NULL), plrt.rec_val * 100 / plrt.elig_sal_val), temp.pct_decs) pl_rec_val_pct
, decode (plrt.rec_mn_val, NULL
, to_char (NULL), decode (plrt.elig_sal_val, NULL
, to_char (NULL), 0
, to_char (NULL), round (plrt.rec_mn_val * 100 / plrt.elig_sal_val, temp.pct_decs)
|| '~'
|| round (plrt.rec_mx_val * 100 / plrt.elig_sal_val, temp.pct_decs))) pl_rec_val_pct_limit
, plrt.misc1_val pl_misc1_val
, plrt.misc2_val pl_misc2_val
, plrt.misc3_val pl_misc3_val
, plrt.stat_sal_val pl_stat_sal_val
, plrt.tot_comp_val pl_tot_comp_val
, plrt.oth_comp_val pl_oth_comp_val
, round (plxchg.xchg_rate, 4) pl_xchg_rate
, plrt.elig_sal_val / decode (temp.plesunits, NULL
, plxchg.xchg_rate, 1) pl_corp_elig_sal_val
, plrt.ws_val / decode (temp.plunits, NULL
, plxchg.xchg_rate, 1) pl_corp_ws_val
, decode (temp.plsalarycomp, 'ICM7'
, (per.base_salary * per.pay_annulization_factor / temp.planufactor) + plrt.ws_val, to_number (NULL)) / plxchg.xchg_rate pl_corp_new_salary
, temp.plsalarycomp pl_salary_comp
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1rt.elig_sal_val, to_number (NULL))
, to_number (NULL)) opt1_elig_sal_val
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1rt.ws_val, to_number (NULL))
, to_number (NULL)) opt1_ws_val
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, round (decode (opt1rt.elig_sal_val, NULL
, to_number (NULL), 0
, to_number (NULL), opt1rt.ws_val * 100 / opt1rt.elig_sal_val), temp.pct_decs), to_number (NULL))
, to_number (NULL)) opt1_pct_of_elig_sal
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1rt.ws_mn_val, to_number (NULL))
, to_number (NULL)) opt1_ws_min_val
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1rt.ws_mx_val, to_number (NULL))
, to_number (NULL)) opt1_ws_max_val
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1rt.ws_incr_val, to_number (NULL))
, to_number (NULL)) opt1_ws_incr_val
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, decode (temp.opt1salarycomp, 'ICM7'
, (per.base_salary * per.pay_annulization_factor / temp.planufactor) + opt1rt.ws_val, to_number (NULL)), to_number (NULL))
, to_number (NULL)) opt1_new_salary
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, decode (opt1rt.ws_mn_val, NULL
, to_char (NULL), ltrim (to_char (opt1rt.ws_mn_val, '999G999G999G990D00'))
|| '~'
|| ltrim (to_char (opt1rt.ws_mx_val, '999G999G999G990D00'))), to_char (NULL))
, to_char (NULL)) opt1_ws_val_limit
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1rt.rec_val, to_number (NULL))
, to_number (NULL)) opt1_rec_val
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1rt.rec_mn_val, to_number (NULL))
, to_number (NULL)) opt1_rec_mn_val
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1rt.rec_mx_val, to_number (NULL))
, to_number (NULL)) opt1_rec_mx_val
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, decode (opt1rt.rec_mn_val, NULL
, to_char (NULL), ltrim (to_char (opt1rt.rec_mn_val, '999G999G999G990D00'))
|| '~'
|| ltrim (to_char (opt1rt.rec_mx_val, '999G999G999G990D00'))), to_char (NULL))
, to_char (NULL)) opt1_rec_val_limit
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, round (decode (opt1rt.elig_sal_val, NULL
, to_number (NULL), 0
, to_number (NULL), opt1rt.rec_val * 100 / opt1rt.elig_sal_val), temp.pct_decs), to_number (NULL))
, to_number (NULL)) opt1_rec_val_pct
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, decode (opt1rt.rec_mn_val, NULL
, to_char (NULL), decode (opt1rt.elig_sal_val, NULL
, to_char (NULL), 0
, to_char (NULL), round (opt1rt.rec_mn_val * 100 / opt1rt.elig_sal_val, temp.pct_decs)
|| '~'
|| round (opt1rt.rec_mx_val * 100 / opt1rt.elig_sal_val, temp.pct_decs))), to_char (NULL))
, to_char (NULL)) opt1_rec_val_pct_limit
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1rt.misc1_val, to_number (NULL))
, to_number (NULL)) opt1_misc1_val
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1rt.misc2_val, to_number (NULL))
, to_number (NULL)) opt1_misc2_val
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1rt.misc3_val, to_number (NULL))
, to_number (NULL)) opt1_misc3_val
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1rt.stat_sal_val, to_number (NULL))
, to_number (NULL)) opt1_stat_sal_val
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1rt.tot_comp_val, to_number (NULL))
, to_number (NULL)) opt1_tot_comp_val
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1rt.oth_comp_val, to_number (NULL))
, to_number (NULL)) opt1_oth_comp_val
, round (nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1xchg.xchg_rate, to_number (NULL))
, to_number (NULL)), 4) opt1_xchg_rate
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1rt.elig_sal_val / decode (temp.opt1esunits, NULL
, opt1xchg.xchg_rate, 1), to_number (NULL))
, to_number (NULL)) opt1_corp_elig_sal_val
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, opt1rt.ws_val / decode (temp.opt1units, NULL
, opt1xchg.xchg_rate, 1), to_number (NULL))
, to_number (NULL)) opt1_corp_ws_val
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, decode (temp.opt1salarycomp, 'ICM7'
, (per.base_salary * per.pay_annulization_factor / temp.planufactor) + opt1rt.ws_val, to_number (NULL)) / opt1xchg.xchg_rate, to_number (NULL))
, to_number (NULL)) opt1_corp_new_salary
, nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
, temp.opt1salarycomp, to_number (NULL))
, to_number (NULL)) opt1_salary_comp
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2rt.elig_sal_val, to_number (NULL))
, to_number (NULL)) opt2_elig_sal_val
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2rt.ws_val, to_number (NULL))
, to_number (NULL)) opt2_ws_val
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, round (decode (opt2rt.elig_sal_val, NULL
, to_number (NULL), 0
, to_number (NULL), opt2rt.ws_val * 100 / opt2rt.elig_sal_val), temp.pct_decs), to_number (NULL))
, to_number (NULL)) opt2_pct_of_elig_sal
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2rt.ws_mn_val, to_number (NULL))
, to_number (NULL)) opt2_ws_min_val
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2rt.ws_mx_val, to_number (NULL))
, to_number (NULL)) opt2_ws_max_val
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2rt.ws_incr_val, to_number (NULL))
, to_number (NULL)) opt2_ws_incr_val
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, decode (temp.opt2salarycomp, 'ICM7'
, (per.base_salary * per.pay_annulization_factor / temp.planufactor) + opt2rt.ws_val, to_number (NULL)), to_number (NULL))
, to_number (NULL)) opt2_new_salary
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, decode (opt2rt.ws_mn_val, NULL
, to_char (NULL), ltrim (to_char (opt2rt.ws_mn_val, '999G999G999G990D00'))
|| '~'
|| ltrim (to_char (opt2rt.ws_mx_val, '999G999G999G990D00'))), to_char (NULL))
, to_char (NULL)) opt2_ws_val_limit
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2rt.rec_val, to_number (NULL))
, to_number (NULL)) opt2_rec_val
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2rt.rec_mn_val, to_number (NULL))
, to_number (NULL)) opt2_rec_mn_val
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2rt.rec_mx_val, to_number (NULL))
, to_number (NULL)) opt2_rec_mx_val
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, decode (opt2rt.rec_mn_val, NULL
, to_char (NULL), ltrim (to_char (opt2rt.rec_mn_val, '999G999G999G990D00'))
|| '~'
|| ltrim (to_char (opt2rt.rec_mx_val, '999G999G999G990D00'))), to_char (NULL))
, to_char (NULL)) opt2_rec_val_limit
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, round (decode (opt2rt.elig_sal_val, NULL
, to_number (NULL), 0
, to_number (NULL), opt2rt.rec_val * 100 / opt2rt.elig_sal_val), temp.pct_decs), to_number (NULL))
, to_number (NULL)) opt2_rec_val_pct
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, decode (opt2rt.rec_mn_val, NULL
, to_char (NULL), decode (opt2rt.elig_sal_val, NULL
, to_char (NULL), 0
, to_char (NULL), round (opt2rt.rec_mn_val * 100 / opt2rt.elig_sal_val, temp.pct_decs)
|| '~'
|| round (opt2rt.rec_mx_val * 100 / opt2rt.elig_sal_val, temp.pct_decs))), to_char (NULL))
, to_char (NULL)) opt2_rec_val_pct_limit
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2rt.misc1_val, to_number (NULL))
, to_number (NULL)) opt2_misc1_val
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2rt.misc2_val, to_number (NULL))
, to_number (NULL)) opt2_misc2_val
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2rt.misc3_val, to_number (NULL))
, to_number (NULL)) opt2_misc3_val
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2rt.stat_sal_val, to_number (NULL))
, to_number (NULL)) opt2_stat_sal_val
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2rt.tot_comp_val, to_number (NULL))
, to_number (NULL)) opt2_tot_comp_val
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2rt.oth_comp_val, to_number (NULL))
, to_number (NULL)) opt2_oth_comp_val
, round (nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2xchg.xchg_rate, to_number (NULL))
, to_number (NULL)), 4) opt2_xchg_rate
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2rt.elig_sal_val / decode (temp.opt2esunits, NULL
, opt2xchg.xchg_rate, 1), to_number (NULL))
, to_number (NULL)) opt2_corp_elig_sal_val
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, opt2rt.ws_val / decode (temp.opt2units, NULL
, opt2xchg.xchg_rate, 1), to_number (NULL))
, to_number (NULL)) opt2_corp_ws_val
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, decode (temp.opt2salarycomp, 'ICM7'
, (per.base_salary * per.pay_annulization_factor / temp.planufactor) + opt2rt.ws_val, to_number (NULL)) / opt2xchg.xchg_rate, to_number (NULL))
, to_number (NULL)) opt2_corp_new_salary
, nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
, temp.opt2salarycomp, to_number (NULL))
, to_number (NULL)) opt2_salary_comp
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3rt.elig_sal_val, to_number (NULL))
, to_number (NULL)) opt3_elig_sal_val
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3rt.ws_val, to_number (NULL))
, to_number (NULL)) opt3_ws_val
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, round (decode (opt3rt.elig_sal_val, NULL
, to_number (NULL), 0
, to_number (NULL), opt3rt.ws_val * 100 / opt3rt.elig_sal_val), temp.pct_decs), to_number (NULL))
, to_number (NULL)) opt3_pct_of_elig_sal
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3rt.ws_mn_val, to_number (NULL))
, to_number (NULL)) opt3_ws_min_val
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3rt.ws_mx_val, to_number (NULL))
, to_number (NULL)) opt3_ws_max_val
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3rt.ws_incr_val, to_number (NULL))
, to_number (NULL)) opt3_ws_incr_val
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, decode (temp.opt3salarycomp, 'ICM7'
, (per.base_salary * per.pay_annulization_factor / temp.planufactor) + opt3rt.ws_val, to_number (NULL)), to_number (NULL))
, to_number (NULL)) opt3_new_salary
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, decode (opt3rt.ws_mn_val, NULL
, to_char (NULL), ltrim (to_char (opt3rt.ws_mn_val, '999G999G999G990D00'))
|| '~'
|| ltrim (to_char (opt3rt.ws_mx_val, '999G999G999G990D00'))), to_char (NULL))
, to_char (NULL)) opt3_ws_val_limit
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3rt.rec_val, to_number (NULL))
, to_number (NULL)) opt3_rec_val
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3rt.rec_mn_val, to_number (NULL))
, to_number (NULL)) opt3_rec_mn_val
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3rt.rec_mx_val, to_number (NULL))
, to_number (NULL)) opt3_rec_mx_val
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, decode (opt3rt.rec_mn_val, NULL
, to_char (NULL), ltrim (to_char (opt3rt.rec_mn_val, '999G999G999G990D00'))
|| '~'
|| ltrim (to_char (opt3rt.rec_mx_val, '999G999G999G990D00'))), to_char (NULL))
, to_char (NULL)) opt3_rec_val_limit
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, round (decode (opt3rt.elig_sal_val, NULL
, to_number (NULL), 0
, to_number (NULL), opt3rt.rec_val * 100 / opt3rt.elig_sal_val), temp.pct_decs), to_number (NULL))
, to_number (NULL)) opt3_rec_val_pct
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, decode (opt3rt.rec_mn_val, NULL
, to_char (NULL), decode (opt3rt.elig_sal_val, NULL
, to_char (NULL), 0
, to_char (NULL), round (opt3rt.rec_mn_val * 100 / opt3rt.elig_sal_val, temp.pct_decs)
|| '~'
|| round (opt3rt.rec_mx_val * 100 / opt3rt.elig_sal_val, temp.pct_decs))), to_char (NULL))
, to_char (NULL)) opt3_rec_val_pct_limit
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3rt.misc1_val, to_number (NULL))
, to_number (NULL)) opt3_misc1_val
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3rt.misc2_val, to_number (NULL))
, to_number (NULL)) opt3_misc2_val
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3rt.misc3_val, to_number (NULL))
, to_number (NULL)) opt3_misc3_val
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3rt.stat_sal_val, to_number (NULL))
, to_number (NULL)) opt3_stat_sal_val
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3rt.tot_comp_val, to_number (NULL))
, to_number (NULL)) opt3_tot_comp_val
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3rt.oth_comp_val, to_number (NULL))
, to_number (NULL)) opt3_oth_comp_val
, round (nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3xchg.xchg_rate, to_number (NULL))
, to_number (NULL)), 4) opt3_xchg_rate
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3rt.elig_sal_val / decode (temp.opt3esunits, NULL
, opt3xchg.xchg_rate, 1), to_number (NULL))
, to_number (NULL)) opt3_corp_elig_sal_val
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, opt3rt.ws_val / decode (temp.opt3units, NULL
, opt3xchg.xchg_rate, 1), to_number (NULL))
, to_number (NULL)) opt3_corp_ws_val
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, decode (temp.opt3salarycomp, 'ICM7'
, (per.base_salary * per.pay_annulization_factor / temp.planufactor) + opt3rt.ws_val, to_number (NULL)) / opt3xchg.xchg_rate, to_number (NULL))
, to_number (NULL)) opt3_corp_new_salary
, nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
, temp.opt3salarycomp, to_number (NULL))
, to_number (NULL)) opt3_salary_comp
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4rt.elig_sal_val, to_number (NULL))
, to_number (NULL)) opt4_elig_sal_val
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4rt.ws_val, to_number (NULL))
, to_number (NULL)) opt4_ws_val
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, round (decode (opt4rt.elig_sal_val, NULL
, to_number (NULL), 0
, to_number (NULL), opt4rt.ws_val * 100 / opt4rt.elig_sal_val), temp.pct_decs), to_number (NULL))
, to_number (NULL)) opt4_pct_of_elig_sal
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4rt.ws_mn_val, to_number (NULL))
, to_number (NULL)) opt4_ws_min_val
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4rt.ws_mx_val, to_number (NULL))
, to_number (NULL)) opt4_ws_max_val
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4rt.ws_incr_val, to_number (NULL))
, to_number (NULL)) opt4_ws_incr_val
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, decode (temp.opt4salarycomp, 'ICM7'
, (per.base_salary * per.pay_annulization_factor / temp.planufactor) + opt4rt.ws_val, to_number (NULL)), to_number (NULL))
, to_number (NULL)) opt4_new_salary
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, decode (opt4rt.ws_mn_val, NULL
, to_char (NULL), ltrim (to_char (opt4rt.ws_mn_val, '999G999G999G990D00'))
|| '~'
|| ltrim (to_char (opt4rt.ws_mx_val, '999G999G999G990D00'))), to_char (NULL))
, to_char (NULL)) opt4_ws_val_limit
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4rt.rec_val, to_number (NULL))
, to_number (NULL)) opt4_rec_val
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4rt.rec_mn_val, to_number (NULL))
, to_number (NULL)) opt4_rec_mn_val
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4rt.rec_mx_val, to_number (NULL))
, to_number (NULL)) opt4_rec_mx_val
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, decode (opt4rt.rec_mn_val, NULL
, to_char (NULL), ltrim (to_char (opt4rt.rec_mn_val, '999G999G999G990D00'))
|| '~'
|| ltrim (to_char (opt4rt.rec_mx_val, '999G999G999G990D00'))), to_char (NULL))
, to_char (NULL)) opt4_rec_val_limit
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, round (decode (opt4rt.elig_sal_val, NULL
, to_number (NULL), 0
, to_number (NULL), opt4rt.rec_val * 100 / opt4rt.elig_sal_val), temp.pct_decs), to_number (NULL))
, to_number (NULL)) opt4_rec_val_pct
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, decode (opt4rt.rec_mn_val, NULL
, to_char (NULL), decode (opt4rt.elig_sal_val, NULL
, to_char (NULL), 0
, to_char (NULL), round (opt4rt.rec_mn_val * 100 / opt4rt.elig_sal_val, temp.pct_decs)
|| '~'
|| round (opt4rt.rec_mx_val * 100 / opt4rt.elig_sal_val, temp.pct_decs))), to_char (NULL))
, to_char (NULL)) opt4_rec_val_pct_limit
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4rt.misc1_val, to_number (NULL))
, to_number (NULL)) opt4_misc1_val
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4rt.misc2_val, to_number (NULL))
, to_number (NULL)) opt4_misc2_val
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4rt.misc3_val, to_number (NULL))
, to_number (NULL)) opt4_misc3_val
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4rt.stat_sal_val, to_number (NULL))
, to_number (NULL)) opt4_stat_sal_val
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4rt.tot_comp_val, to_number (NULL))
, to_number (NULL)) opt4_tot_comp_val
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4rt.oth_comp_val, to_number (NULL))
, to_number (NULL)) opt4_oth_comp_val
, round (nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4xchg.xchg_rate, to_number (NULL))
, to_number (NULL)), 4) opt4_xchg_rate
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4rt.elig_sal_val / decode (temp.opt4esunits, NULL
, opt4xchg.xchg_rate, 1), to_number (NULL))
, to_number (NULL)) opt4_corp_elig_sal_val
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, opt4rt.ws_val / decode (temp.opt4units, NULL
, opt4xchg.xchg_rate, 1), to_number (NULL))
, to_number (NULL)) opt4_corp_ws_val
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, decode (temp.opt4salarycomp, 'ICM7'
, (per.base_salary * per.pay_annulization_factor / temp.planufactor) + opt4rt.ws_val, to_number (NULL)) / opt4xchg.xchg_rate, to_number (NULL))
, to_number (NULL)) opt4_corp_new_salary
, nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
, temp.opt4salarycomp, to_number (NULL))
, to_number (NULL)) opt4_salary_comp
,
(
SELECT txn.attribute3
FROM ben_transaction txn
WHERE txn.transaction_id = per.assignment_id
AND txn.transaction_type = 'CWBASG'
|| temp.asg_date
AND txn.attribute3 IS NOT NULL
) change_reason
,
(
SELECT bcpi.full_name
FROM ben_cwb_person_info bcpi
, ben_cwb_group_hrchy bcgh
WHERE bcgh.emp_per_in_ler_id = per.group_per_in_ler_id
AND bcgh.lvl_num =
(
SELECT max (lvl_num) - 1 + 1
FROM ben_cwb_group_hrchy
WHERE emp_per_in_ler_id = per.group_per_in_ler_id
)
AND bcgh.lvl_num > 0
AND bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id
) level1mgr
,
(
SELECT bcpi.full_name
FROM ben_cwb_person_info bcpi
, ben_cwb_group_hrchy bcgh
WHERE bcgh.emp_per_in_ler_id = per.group_per_in_ler_id
AND bcgh.lvl_num =
(
SELECT max (lvl_num) - 2 + 1
FROM ben_cwb_group_hrchy
WHERE emp_per_in_ler_id = per.group_per_in_ler_id
)
AND bcgh.lvl_num > 0
AND bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id
) level2mgr
,
(
SELECT bcpi.full_name
FROM ben_cwb_person_info bcpi
, ben_cwb_group_hrchy bcgh
WHERE bcgh.emp_per_in_ler_id = per.group_per_in_ler_id
AND bcgh.lvl_num =
(
SELECT max (lvl_num) - 3 + 1
FROM ben_cwb_group_hrchy
WHERE emp_per_in_ler_id = per.group_per_in_ler_id
)
AND bcgh.lvl_num > 0
AND bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id
) level3mgr
,
(
SELECT bcpi.full_name
FROM ben_cwb_person_info bcpi
, ben_cwb_group_hrchy bcgh
WHERE bcgh.emp_per_in_ler_id = per.group_per_in_ler_id
AND bcgh.lvl_num =
(
SELECT max (lvl_num) - 4 + 1
FROM ben_cwb_group_hrchy
WHERE emp_per_in_ler_id = per.group_per_in_ler_id
)
AND bcgh.lvl_num > 0
AND bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id
) level4mgr
,
(
SELECT bcpi.full_name
FROM ben_cwb_person_info bcpi
, ben_cwb_group_hrchy bcgh
WHERE bcgh.emp_per_in_ler_id = per.group_per_in_ler_id
AND bcgh.lvl_num =
(
SELECT max (lvl_num) - 5 + 1
FROM ben_cwb_group_hrchy
WHERE emp_per_in_ler_id = per.group_per_in_ler_id
)
AND bcgh.lvl_num > 0
AND bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id
) level5mgr
,
(
SELECT bcpi.full_name
FROM ben_cwb_person_info bcpi
, ben_cwb_group_hrchy bcgh
WHERE bcgh.emp_per_in_ler_id = per.group_per_in_ler_id
AND bcgh.lvl_num =
(
SELECT max (lvl_num) - 6 + 1
FROM ben_cwb_group_hrchy
WHERE emp_per_in_ler_id = per.group_per_in_ler_id
)
AND bcgh.lvl_num > 0
AND bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id
) level6mgr
,
(
SELECT bcpi.full_name
FROM ben_cwb_person_info bcpi
, ben_cwb_group_hrchy bcgh
WHERE bcgh.emp_per_in_ler_id = per.group_per_in_ler_id
AND bcgh.lvl_num =
(
SELECT max (lvl_num) - 7 + 1
FROM ben_cwb_group_hrchy
WHERE emp_per_in_ler_id = per.group_per_in_ler_id
)
AND bcgh.lvl_num > 0
AND bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id
) level7mgr
,
(
SELECT bcpi.full_name
FROM ben_cwb_person_info bcpi
, ben_cwb_group_hrchy bcgh
WHERE bcgh.emp_per_in_ler_id = per.group_per_in_ler_id
AND bcgh.lvl_num =
(
SELECT max (lvl_num) - 8 + 1
FROM ben_cwb_group_hrchy
WHERE emp_per_in_ler_id = per.group_per_in_ler_id
)
AND bcgh.lvl_num > 0
AND bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id
) level8mgr
, decode (temp.plsalarycomp, 'ICM7'
, plrt.currency, decode (temp.opt1salarycomp, 'ICM7'
, opt1rt.currency, decode (temp.opt2salarycomp, 'ICM7'
, opt2rt.currency, decode (temp.opt3salarycomp, 'ICM7'
, opt3rt.currency, decode (temp.opt4salarycomp, 'ICM7'
, opt4rt.currency, temp.grpcurr))))) sal_stat_currency
,
(
SELECT decode (temp.name_profile, 'BN'
, mgrper.brief_name, 'CN'
, mgrper.custom_name, mgrper.full_name)
FROM ben_cwb_group_hrchy hrchy1
, ben_cwb_person_info mgrper
WHERE hrchy1.emp_per_in_ler_id = per.group_per_in_ler_id
AND hrchy1.lvl_num = 1
AND hrchy1.mgr_per_in_ler_id = mgrper.group_per_in_ler_id
) wrk_mgr_name
, temp.grpcurr corp_currency
, fnd_global.conc_request_id conc_request_id
, NULL start_date
, NULL end_date
FROM ben_cwb_person_rates plrt
, 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_per_in_ler pil
, ben_cwb_person_info per
, per_assignment_status_types_tl asttl
, hr_all_organization_units_tl bgtl
, hr_all_organization_units_tl orgtl
, hr_locations_all_tl loctl
, per_jobs job
, per_jobs_tl jobtl
, per_job_definitions jobdef
, hr_all_positions_f_tl postl
, per_grades_tl grdtl
,
(
SELECT /*+ merge leading(grp_pl)*/
fnd_profile.value ('BEN_DISPLAY_EMPLOYEE_NAME') name_profile
, nvl (fnd_profile.value ('BEN_CWB_WS_PCT_ES_DECS_DISP'), 2) pct_decs
, pl1.pl_id plid
, pl1.name plname
, pl1.oipl_id ploiplid
, pl1.ws_abr_id plwsabrid
, pl1.pl_annulization_factor planufactor
, pl1.ws_sub_acty_typ_cd plsalarycomp
, hrl1.meaning plunits
, pl1.elig_sal_nnmntry_uom plesunits
, opt1.oipl_id oipl1id
, 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
, opt1.ws_sub_acty_typ_cd opt1salarycomp
, hrl2.meaning opt1units
, opt1.elig_sal_nnmntry_uom opt1esunits
, opt2.oipl_id oipl2id
, 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
, opt2.ws_sub_acty_typ_cd opt2salarycomp
, hrl3.meaning opt2units
, opt2.elig_sal_nnmntry_uom opt2esunits
, opt3.oipl_id oipl3id
, 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
, opt3.ws_sub_acty_typ_cd opt3salarycomp
, hrl4.meaning opt3units
, opt3.elig_sal_nnmntry_uom opt3esunits
, opt4.oipl_id oipl4id
, 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
, opt4.ws_sub_acty_typ_cd opt4salarycomp
, hrl5.meaning opt4units
, opt4.elig_sal_nnmntry_uom opt4esunits
, grp_pl.pl_id grpplid
, grp_pl.oipl_id grpoiplid
, grp_pl.lf_evt_ocrd_dt grplfevtocrddt
, 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.pl_annulization_factor grpufactor
, hr_general.decode_lookup ('BEN_NNMNTRY_UOM', grp_pl.ws_nnmntry_uom) grpunits
, grp_pl.pl_uom grpcurr
FROM ben_cwb_pl_dsgn grp_pl
, ben_cwb_pl_dsgn pl1
, ben_cwb_pl_dsgn opt1
, ben_cwb_pl_dsgn opt2
, ben_cwb_pl_dsgn opt3
, ben_cwb_pl_dsgn opt4
, hr_lookups hrl1
, hr_lookups hrl2
, hr_lookups hrl3
, hr_lookups hrl4
, hr_lookups hrl5
WHERE p_group_pl_id = grp_pl.pl_id
AND p_lf_evt_ocrd_dt = grp_pl.lf_evt_ocrd_dt
AND grp_pl.oipl_id = - 1
AND grp_pl.group_pl_id = grp_pl.pl_id
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.lf_evt_ocrd_dt
AND pl1.actual_flag (+) = 'Y'
AND pl1.oipl_id (+) = - 1
AND opt1.pl_id (+) = pl1.pl_id
AND opt1.lf_evt_ocrd_dt (+) = pl1.lf_evt_ocrd_dt
AND opt1.oipl_id (+) <> - 1
AND opt1.oipl_ordr_num (+) = 1
AND opt2.pl_id (+) = pl1.pl_id
AND opt2.lf_evt_ocrd_dt (+) = pl1.lf_evt_ocrd_dt
AND opt2.oipl_id (+) <> - 1
AND opt2.oipl_ordr_num (+) = 2
AND opt3.pl_id (+) = pl1.pl_id
AND opt3.lf_evt_ocrd_dt (+) = pl1.lf_evt_ocrd_dt
AND opt3.oipl_id (+) <> - 1
AND opt3.oipl_ordr_num (+) = 3
AND opt4.pl_id (+) = pl1.pl_id
AND opt4.lf_evt_ocrd_dt (+) = pl1.lf_evt_ocrd_dt
AND opt4.oipl_id (+) <> - 1
AND opt4.oipl_ordr_num (+) = 4
AND hrl1.lookup_type (+) = 'BEN_NNMNTRY_UOM'
AND hrl1.lookup_code (+) = pl1.ws_nnmntry_uom
AND hrl2.lookup_type (+) = 'BEN_NNMNTRY_UOM'
AND hrl2.lookup_code (+) = opt1.ws_nnmntry_uom
AND hrl3.lookup_type (+) = 'BEN_NNMNTRY_UOM'
AND hrl3.lookup_code (+) = opt2.ws_nnmntry_uom
AND hrl4.lookup_type (+) = 'BEN_NNMNTRY_UOM'
AND hrl4.lookup_code (+) = opt3.ws_nnmntry_uom
AND hrl5.lookup_type (+) = 'BEN_NNMNTRY_UOM'
AND hrl5.lookup_code (+) = opt4.ws_nnmntry_uom
) temp
WHERE per.group_per_in_ler_id = pil.per_in_ler_id
AND pil.per_in_ler_stat_cd <> 'BCKDT'
AND p_lf_evt_ocrd_dt = per.lf_evt_ocrd_dt
AND per.group_pl_id = temp.grpplid
AND per.group_per_in_ler_id = plrt.group_per_in_ler_id
AND plrt.oipl_id = - 1
AND plrt.elig_flag = 'Y'
AND plrt.pl_id = temp.plid
AND plrt.lf_evt_ocrd_dt = temp.grplfevtocrddt
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 per.group_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 (temp.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 per.group_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 (temp.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 per.group_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 (temp.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 per.group_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 (temp.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 per.assignment_status_type_id = asttl.assignment_status_type_id (+)
AND asttl.language (+) = userenv ('lang')
AND per.business_group_id = bgtl.organization_id (+)
AND bgtl.language (+) = userenv ('lang')
AND per.organization_id = orgtl.organization_id (+)
AND orgtl.language (+) = userenv ('lang')
AND per.location_id = loctl.location_id (+)
AND loctl.language (+) = userenv ('lang')
AND per.job_id = job.job_id (+)
AND job.job_definition_id = jobdef.job_definition_id (+)
AND job.job_id = jobtl.job_id (+)
AND jobtl.language (+) = userenv ('lang')
AND per.position_id = postl.position_id (+)
AND postl.language (+) = userenv ('lang')
AND per.grade_id = grdtl.grade_id (+)
AND grdtl.language (+) = userenv ('lang');
INSERT INTO BEN_CWB_COMP_DETAILS
VALUES Psa(i);
ben_cwb_cd_summary_pkg.Message_log_proc('INSERTED '||SQL%ROWCOUNT || ' Rows in to BEN_CWB_COMP_DETAILS TABLE', 50);
INSERT INTO BEN_CWB_COMP_DETAILS
(GROUP_PER_IN_LER_ID,
PERSON_ID,
GROUP_PL_ID,
LF_EVT_OCRD_DT,
conc_request_id,
Start_date,
end_Date)
VALUES (-1,
-1,
P_GROUP_PL_ID,
P_LF_EVT_OCRD_DT,
Conc_Prog_Id,
l_Start_Date,
SYSDATE);
SELECT DISTINCT pl.pl_id,
pl.name,
pl.lf_evt_ocrd_dt
FROM ben_cwb_pl_dsgn pl
WHERE pl.pl_stat_cd IN ( 'A', 'I' )
AND pl.group_pl_id = pl.pl_id
AND pl.oipl_id = -1
AND ( fnd_profile.Value('HR_CROSS_BUSINESS_GROUP') = 'Y'
OR hr_general.get_business_group_id = pl.business_group_id )
AND ( Nvl(fnd_profile.Value('BEN_CWB_PLAN_SECURITY'), 'N') = 'N'
OR EXISTS (SELECT NULL
FROM ben_resp_plan_mapping mapg
WHERE mapg.responsibility_id = fnd_global.resp_id
AND mapg.pl_id = pl.pl_id) )
AND Nvl(p_pl_id, pl.group_pl_id) = pl.group_pl_id
AND Nvl(p_lf_evt_ocrd_dt, pl.lf_evt_ocrd_dt) = pl.lf_evt_ocrd_dt
ORDER BY pl.pl_id,
pl.lf_evt_ocrd_dt;
SELECT FND_GLOBAL.conc_request_id
INTO conc_prog_id
FROM DUAL;
SELECT s.table_owner
||'.'
||Nvl(ev.table_name, s.table_name) table_name
INTO table_name_var
FROM user_synonyms s,
dba_editioning_views ev
WHERE synonym_name = 'BEN_CWB_COMP_DETAILS'
AND ev.owner(+) = s.table_owner
AND ev.view_name(+) = s.table_name;
BEN_CWB_CD_SUMMARY_PKG.insert_refresh_jobs;
BEN_CWB_CD_SUMMARY_PKG.insert_refresh_jobs;
ben_cwb_cd_summary_pkg.Message_log_proc('Specific Plan is Selected (And/or) Plan Security is Enabled', 20);
DELETE FROM BEN_CWB_COMP_DETAILS
WHERE group_pl_id = L_PARAM_GROUP_PL_ID
AND LF_EVT_OCRD_DT = L_PARAM_EVT_OCRD_DT;
ben_cwb_cd_summary_pkg.Message_log_proc('Deleted '||SQL%ROWCOUNT ||' Rows for ' ||L_PARAM_PL_NAME||'-'||L_PARAM_EVT_OCRD_DT||' from BEN_CWB_COMP_DETAILS', 40);
BEN_CWB_CD_SUMMARY_PKG.delete_refresh_jobs;
DELETE FROM BEN_CWB_COMP_DETAILS
WHERE CONC_REQUEST_ID = Conc_Prog_Id;
ben_cwb_cd_summary_pkg.Message_log_proc('Deleted '||SQL%ROWCOUNT ||' from summary Table', 90);