View: BEN_CWB_REP_TOTAL_COMP_V

Product:	BEN - Advanced Benefits
Description:
Implementation/DBA Data:	APPS.BEN_CWB_REP_TOTAL_COMP_V

View Text

SELECT MIN(HIER.LVL_NUM+1) LEVELS_DOWN
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME EMP_BRIEF_NAME
, PPF.FULL_NAME EMP_FULL_NAME
, POPL.UOM CURRENCY
, PPP.PROPOSED_SALARY_N BEG_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) SALARY_VAL
, PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) END_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) BONUS_VAL
, NVL(PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(PRT.RT_VAL
, ENR.VAL))) + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(PRT.RT_VAL
, ENR.VAL)))
, 0) TOTAL_COMP
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)
, ''
, ROUND(NVL(PRT.RT_VAL
, ENR.VAL)/NVL(PRT2.RT_VAL
, ENR2.VAL)
, 0)))) STOCK_SHARES
, NVL(SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)*NVL(PRT2.RT_VAL
, ENR2.VAL)
, ''
, NVL(PRT.RT_VAL
, ENR.VAL))))
, 0) STOCK_VAL
, CHC.WS_MGR_ID MGR_ID_PARAM
, CHC.YR_PERD_ID PERD_ID_PARAM
, 1 LEVELS_DOWN_PARAM
FROM BEN_ELIG_PER_ELCTBL_CHC CHC
, BEN_PIL_ELCTBL_CHC_POPL POPL
, BEN_PL_F PL
, BEN_PL_TYP_F PLT
, BEN_CWB_MGR_HRCHY HIER
, BEN_PER_IN_LER PIL
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_ENRT_RT ENR
, BEN_PRTT_RT_VAL PRT
, BEN_ENRT_RT ENR2
, BEN_PRTT_RT_VAL PRT2
, BEN_YR_PERD YR
, PER_ALL_PEOPLE_F PPF
, PER_PAY_PROPOSALS PPP
WHERE CHC.YR_PERD_ID = YR.YR_PERD_ID
AND CHC.PL_ID = PL.PL_ID
AND CHC.PL_TYP_ID = PLT.PL_TYP_ID
AND PLT.COMP_TYP_CD IN ('ICM'
, 'ICM4'
, 'ICM7')
AND SYSDATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PLT.EFFECTIVE_START_DATE
AND PLT.EFFECTIVE_END_DATE
AND CHC.ELIG_PER_ELCTBL_CHC_ID = HIER.MGR_ELIG_PER_ELCTBL_CHC_ID
AND HIER.LVL_NUM+1 > 0
AND HIER.LVL_NUM+1 <= 1
AND HIER.EMP_ELIG_PER_ELCTBL_CHC_ID = CHC2.ELIG_PER_ELCTBL_CHC_ID
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID
AND ENR.ACTY_TYP_CD = 'CWBWS'
AND ENR.PRTT_RT_VAL_ID = PRT.PRTT_RT_VAL_ID (+)
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR2.ELIG_PER_ELCTBL_CHC_ID (+)
AND ENR2.ACTY_TYP_CD(+) = 'CWBGP'
AND ENR2.PRTT_RT_VAL_ID = PRT2.PRTT_RT_VAL_ID (+)
AND CHC2.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND CHC2.ELIG_FLAG = 'Y'
AND CHC2.ELIG_FLAG = 'Y'
AND PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PIL.PERSON_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND CHC2.PIL_ELCTBL_CHC_POPL_ID = POPL.PIL_ELCTBL_CHC_POPL_ID
AND CHC2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID (+)
AND PPP.APPROVED (+) = 'Y'
AND PPP.CHANGE_DATE = NVL((SELECT MAX(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE <= YR.START_DATE)
, (SELECT MIN(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE > YR.START_DATE)) GROUP BY CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME
, PPF.FULL_NAME
, POPL.UOM
, PPP.PROPOSED_SALARY_N UNION SELECT MIN(HIER.LVL_NUM+1) LEVELS_DOWN
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME EMP_BRIEF_NAME
, PPF.FULL_NAME EMP_FULL_NAME
, POPL.UOM CURRENCY
, PPP.PROPOSED_SALARY_N BEG_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) SALARY_VAL
, PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) END_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) BONUS_VAL
, NVL(PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(PRT.RT_VAL
, ENR.VAL))) + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(PRT.RT_VAL
, ENR.VAL)))
, 0) TOTAL_COMP
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)
, ''
, ROUND(NVL(PRT.RT_VAL
, ENR.VAL)/NVL(PRT2.RT_VAL
, ENR2.VAL)
, 0)))) STOCK_SHARES
, NVL(SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)*NVL(PRT2.RT_VAL
, ENR2.VAL)
, ''
, NVL(PRT.RT_VAL
, ENR.VAL))))
, 0) STOCK_VAL
, CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, 2
FROM BEN_ELIG_PER_ELCTBL_CHC CHC
, BEN_PIL_ELCTBL_CHC_POPL POPL
, BEN_PL_F PL
, BEN_PL_TYP_F PLT
, BEN_CWB_MGR_HRCHY HIER
, BEN_PER_IN_LER PIL
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_ENRT_RT ENR
, BEN_PRTT_RT_VAL PRT
, BEN_ENRT_RT ENR2
, BEN_PRTT_RT_VAL PRT2
, BEN_YR_PERD YR
, PER_ALL_PEOPLE_F PPF
, PER_PAY_PROPOSALS PPP
WHERE CHC.YR_PERD_ID = YR.YR_PERD_ID
AND CHC.PL_ID = PL.PL_ID
AND CHC.PL_TYP_ID = PLT.PL_TYP_ID
AND PLT.COMP_TYP_CD IN ('ICM'
, 'ICM4'
, 'ICM7')
AND SYSDATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PLT.EFFECTIVE_START_DATE
AND PLT.EFFECTIVE_END_DATE
AND CHC.ELIG_PER_ELCTBL_CHC_ID = HIER.MGR_ELIG_PER_ELCTBL_CHC_ID
AND HIER.LVL_NUM+1 > 0
AND HIER.LVL_NUM+1 <= 2
AND HIER.EMP_ELIG_PER_ELCTBL_CHC_ID = CHC2.ELIG_PER_ELCTBL_CHC_ID
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID
AND ENR.ACTY_TYP_CD = 'CWBWS'
AND ENR.PRTT_RT_VAL_ID = PRT.PRTT_RT_VAL_ID (+)
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR2.ELIG_PER_ELCTBL_CHC_ID (+)
AND ENR2.ACTY_TYP_CD(+) = 'CWBGP'
AND ENR2.PRTT_RT_VAL_ID = PRT2.PRTT_RT_VAL_ID (+)
AND CHC2.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND CHC2.ELIG_FLAG = 'Y'
AND CHC2.ELIG_FLAG = 'Y'
AND PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PIL.PERSON_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND CHC2.PIL_ELCTBL_CHC_POPL_ID = POPL.PIL_ELCTBL_CHC_POPL_ID
AND CHC2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID (+)
AND PPP.APPROVED (+) = 'Y'
AND PPP.CHANGE_DATE = NVL((SELECT MAX(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE <= YR.START_DATE)
, (SELECT MIN(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE > YR.START_DATE)) GROUP BY CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME
, PPF.FULL_NAME
, POPL.UOM
, PPP.PROPOSED_SALARY_N UNION SELECT MIN(HIER.LVL_NUM+1) LEVELS_DOWN
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME EMP_BRIEF_NAME
, PPF.FULL_NAME EMP_FULL_NAME
, POPL.UOM CURRENCY
, PPP.PROPOSED_SALARY_N BEG_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) SALARY_VAL
, PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) END_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) BONUS_VAL
, NVL(PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(PRT.RT_VAL
, ENR.VAL))) + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(PRT.RT_VAL
, ENR.VAL)))
, 0) TOTAL_COMP
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)
, ''
, ROUND(NVL(PRT.RT_VAL
, ENR.VAL)/NVL(PRT2.RT_VAL
, ENR2.VAL)
, 0)))) STOCK_SHARES
, NVL(SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)*NVL(PRT2.RT_VAL
, ENR2.VAL)
, ''
, NVL(PRT.RT_VAL
, ENR.VAL))))
, 0) STOCK_VAL
, CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, 3
FROM BEN_ELIG_PER_ELCTBL_CHC CHC
, BEN_PIL_ELCTBL_CHC_POPL POPL
, BEN_PL_F PL
, BEN_PL_TYP_F PLT
, BEN_CWB_MGR_HRCHY HIER
, BEN_PER_IN_LER PIL
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_ENRT_RT ENR
, BEN_PRTT_RT_VAL PRT
, BEN_ENRT_RT ENR2
, BEN_PRTT_RT_VAL PRT2
, BEN_YR_PERD YR
, PER_ALL_PEOPLE_F PPF
, PER_PAY_PROPOSALS PPP
WHERE CHC.YR_PERD_ID = YR.YR_PERD_ID
AND CHC.PL_ID = PL.PL_ID
AND CHC.PL_TYP_ID = PLT.PL_TYP_ID
AND PLT.COMP_TYP_CD IN ('ICM'
, 'ICM4'
, 'ICM7')
AND SYSDATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PLT.EFFECTIVE_START_DATE
AND PLT.EFFECTIVE_END_DATE
AND CHC.ELIG_PER_ELCTBL_CHC_ID = HIER.MGR_ELIG_PER_ELCTBL_CHC_ID
AND HIER.LVL_NUM+1 > 0
AND HIER.LVL_NUM+1 <= 3
AND HIER.EMP_ELIG_PER_ELCTBL_CHC_ID = CHC2.ELIG_PER_ELCTBL_CHC_ID
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID
AND ENR.ACTY_TYP_CD = 'CWBWS'
AND ENR.PRTT_RT_VAL_ID = PRT.PRTT_RT_VAL_ID (+)
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR2.ELIG_PER_ELCTBL_CHC_ID (+)
AND ENR2.ACTY_TYP_CD(+) = 'CWBGP'
AND ENR2.PRTT_RT_VAL_ID = PRT2.PRTT_RT_VAL_ID (+)
AND CHC2.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND CHC2.ELIG_FLAG = 'Y'
AND CHC2.ELIG_FLAG = 'Y'
AND PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PIL.PERSON_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND CHC2.PIL_ELCTBL_CHC_POPL_ID = POPL.PIL_ELCTBL_CHC_POPL_ID
AND CHC2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID (+)
AND PPP.APPROVED (+) = 'Y'
AND PPP.CHANGE_DATE = NVL((SELECT MAX(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE <= YR.START_DATE)
, (SELECT MIN(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE > YR.START_DATE)) GROUP BY CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME
, PPF.FULL_NAME
, POPL.UOM
, PPP.PROPOSED_SALARY_N UNION SELECT MIN(HIER.LVL_NUM+1) LEVELS_DOWN
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME EMP_BRIEF_NAME
, PPF.FULL_NAME EMP_FULL_NAME
, POPL.UOM CURRENCY
, PPP.PROPOSED_SALARY_N BEG_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) SALARY_VAL
, PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) END_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) BONUS_VAL
, NVL(PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(PRT.RT_VAL
, ENR.VAL))) + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(PRT.RT_VAL
, ENR.VAL)))
, 0) TOTAL_COMP
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)
, ''
, ROUND(NVL(PRT.RT_VAL
, ENR.VAL)/NVL(PRT2.RT_VAL
, ENR2.VAL)
, 0)))) STOCK_SHARES
, NVL(SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)*NVL(PRT2.RT_VAL
, ENR2.VAL)
, ''
, NVL(PRT.RT_VAL
, ENR.VAL))))
, 0) STOCK_VAL
, CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, 4
FROM BEN_ELIG_PER_ELCTBL_CHC CHC
, BEN_PIL_ELCTBL_CHC_POPL POPL
, BEN_PL_F PL
, BEN_PL_TYP_F PLT
, BEN_CWB_MGR_HRCHY HIER
, BEN_PER_IN_LER PIL
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_ENRT_RT ENR
, BEN_PRTT_RT_VAL PRT
, BEN_ENRT_RT ENR2
, BEN_PRTT_RT_VAL PRT2
, BEN_YR_PERD YR
, PER_ALL_PEOPLE_F PPF
, PER_PAY_PROPOSALS PPP
WHERE CHC.YR_PERD_ID = YR.YR_PERD_ID
AND CHC.PL_ID = PL.PL_ID
AND CHC.PL_TYP_ID = PLT.PL_TYP_ID
AND PLT.COMP_TYP_CD IN ('ICM'
, 'ICM4'
, 'ICM7')
AND SYSDATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PLT.EFFECTIVE_START_DATE
AND PLT.EFFECTIVE_END_DATE
AND CHC.ELIG_PER_ELCTBL_CHC_ID = HIER.MGR_ELIG_PER_ELCTBL_CHC_ID
AND HIER.LVL_NUM+1 > 0
AND HIER.LVL_NUM+1 <= 4
AND HIER.EMP_ELIG_PER_ELCTBL_CHC_ID = CHC2.ELIG_PER_ELCTBL_CHC_ID
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID
AND ENR.ACTY_TYP_CD = 'CWBWS'
AND ENR.PRTT_RT_VAL_ID = PRT.PRTT_RT_VAL_ID (+)
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR2.ELIG_PER_ELCTBL_CHC_ID (+)
AND ENR2.ACTY_TYP_CD(+) = 'CWBGP'
AND ENR2.PRTT_RT_VAL_ID = PRT2.PRTT_RT_VAL_ID (+)
AND CHC2.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND CHC2.ELIG_FLAG = 'Y'
AND CHC2.ELIG_FLAG = 'Y'
AND PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PIL.PERSON_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND CHC2.PIL_ELCTBL_CHC_POPL_ID = POPL.PIL_ELCTBL_CHC_POPL_ID
AND CHC2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID (+)
AND PPP.APPROVED (+) = 'Y'
AND PPP.CHANGE_DATE = NVL((SELECT MAX(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE <= YR.START_DATE)
, (SELECT MIN(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE > YR.START_DATE)) GROUP BY CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME
, PPF.FULL_NAME
, POPL.UOM
, PPP.PROPOSED_SALARY_N UNION SELECT MIN(HIER.LVL_NUM+1) LEVELS_DOWN
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME EMP_BRIEF_NAME
, PPF.FULL_NAME EMP_FULL_NAME
, POPL.UOM CURRENCY
, PPP.PROPOSED_SALARY_N BEG_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) SALARY_VAL
, PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) END_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) BONUS_VAL
, NVL(PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(PRT.RT_VAL
, ENR.VAL))) + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(PRT.RT_VAL
, ENR.VAL)))
, 0) TOTAL_COMP
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)
, ''
, ROUND(NVL(PRT.RT_VAL
, ENR.VAL)/NVL(PRT2.RT_VAL
, ENR2.VAL)
, 0)))) STOCK_SHARES
, NVL(SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)*NVL(PRT2.RT_VAL
, ENR2.VAL)
, ''
, NVL(PRT.RT_VAL
, ENR.VAL))))
, 0) STOCK_VAL
, CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, 5
FROM BEN_ELIG_PER_ELCTBL_CHC CHC
, BEN_PIL_ELCTBL_CHC_POPL POPL
, BEN_PL_F PL
, BEN_PL_TYP_F PLT
, BEN_CWB_MGR_HRCHY HIER
, BEN_PER_IN_LER PIL
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_ENRT_RT ENR
, BEN_PRTT_RT_VAL PRT
, BEN_ENRT_RT ENR2
, BEN_PRTT_RT_VAL PRT2
, BEN_YR_PERD YR
, PER_ALL_PEOPLE_F PPF
, PER_PAY_PROPOSALS PPP
WHERE CHC.YR_PERD_ID = YR.YR_PERD_ID
AND CHC.PL_ID = PL.PL_ID
AND CHC.PL_TYP_ID = PLT.PL_TYP_ID
AND PLT.COMP_TYP_CD IN ('ICM'
, 'ICM4'
, 'ICM7')
AND SYSDATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PLT.EFFECTIVE_START_DATE
AND PLT.EFFECTIVE_END_DATE
AND CHC.ELIG_PER_ELCTBL_CHC_ID = HIER.MGR_ELIG_PER_ELCTBL_CHC_ID
AND HIER.LVL_NUM+1 > 0
AND HIER.LVL_NUM+1 <= 5
AND HIER.EMP_ELIG_PER_ELCTBL_CHC_ID = CHC2.ELIG_PER_ELCTBL_CHC_ID
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID
AND ENR.ACTY_TYP_CD = 'CWBWS'
AND ENR.PRTT_RT_VAL_ID = PRT.PRTT_RT_VAL_ID (+)
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR2.ELIG_PER_ELCTBL_CHC_ID (+)
AND ENR2.ACTY_TYP_CD(+) = 'CWBGP'
AND ENR2.PRTT_RT_VAL_ID = PRT2.PRTT_RT_VAL_ID (+)
AND CHC2.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND CHC2.ELIG_FLAG = 'Y'
AND CHC2.ELIG_FLAG = 'Y'
AND PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PIL.PERSON_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND CHC2.PIL_ELCTBL_CHC_POPL_ID = POPL.PIL_ELCTBL_CHC_POPL_ID
AND CHC2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID (+)
AND PPP.APPROVED (+) = 'Y'
AND PPP.CHANGE_DATE = NVL((SELECT MAX(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE <= YR.START_DATE)
, (SELECT MIN(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE > YR.START_DATE)) GROUP BY CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME
, PPF.FULL_NAME
, POPL.UOM
, PPP.PROPOSED_SALARY_N UNION SELECT MIN(HIER.LVL_NUM+1) LEVELS_DOWN
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME EMP_BRIEF_NAME
, PPF.FULL_NAME EMP_FULL_NAME
, POPL.UOM CURRENCY
, PPP.PROPOSED_SALARY_N BEG_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) SALARY_VAL
, PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) END_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) BONUS_VAL
, NVL(PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(PRT.RT_VAL
, ENR.VAL))) + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(PRT.RT_VAL
, ENR.VAL)))
, 0) TOTAL_COMP
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)
, ''
, ROUND(NVL(PRT.RT_VAL
, ENR.VAL)/NVL(PRT2.RT_VAL
, ENR2.VAL)
, 0)))) STOCK_SHARES
, NVL(SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)*NVL(PRT2.RT_VAL
, ENR2.VAL)
, ''
, NVL(PRT.RT_VAL
, ENR.VAL))))
, 0) STOCK_VAL
, CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, 6
FROM BEN_ELIG_PER_ELCTBL_CHC CHC
, BEN_PIL_ELCTBL_CHC_POPL POPL
, BEN_PL_F PL
, BEN_PL_TYP_F PLT
, BEN_CWB_MGR_HRCHY HIER
, BEN_PER_IN_LER PIL
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_ENRT_RT ENR
, BEN_PRTT_RT_VAL PRT
, BEN_ENRT_RT ENR2
, BEN_PRTT_RT_VAL PRT2
, BEN_YR_PERD YR
, PER_ALL_PEOPLE_F PPF
, PER_PAY_PROPOSALS PPP
WHERE CHC.YR_PERD_ID = YR.YR_PERD_ID
AND CHC.PL_ID = PL.PL_ID
AND CHC.PL_TYP_ID = PLT.PL_TYP_ID
AND PLT.COMP_TYP_CD IN ('ICM'
, 'ICM4'
, 'ICM7')
AND SYSDATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PLT.EFFECTIVE_START_DATE
AND PLT.EFFECTIVE_END_DATE
AND CHC.ELIG_PER_ELCTBL_CHC_ID = HIER.MGR_ELIG_PER_ELCTBL_CHC_ID
AND HIER.LVL_NUM+1 > 0
AND HIER.EMP_ELIG_PER_ELCTBL_CHC_ID = CHC2.ELIG_PER_ELCTBL_CHC_ID
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID
AND ENR.ACTY_TYP_CD = 'CWBWS'
AND ENR.PRTT_RT_VAL_ID = PRT.PRTT_RT_VAL_ID (+)
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR2.ELIG_PER_ELCTBL_CHC_ID (+)
AND ENR2.ACTY_TYP_CD(+) = 'CWBGP'
AND ENR2.PRTT_RT_VAL_ID = PRT2.PRTT_RT_VAL_ID (+)
AND CHC2.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND CHC2.ELIG_FLAG = 'Y'
AND CHC2.ELIG_FLAG = 'Y'
AND PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PIL.PERSON_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND CHC2.PIL_ELCTBL_CHC_POPL_ID = POPL.PIL_ELCTBL_CHC_POPL_ID
AND CHC2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID (+)
AND PPP.APPROVED (+) = 'Y'
AND PPP.CHANGE_DATE = NVL((SELECT MAX(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE <= YR.START_DATE)
, (SELECT MIN(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE > YR.START_DATE)) GROUP BY CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME
, PPF.FULL_NAME
, POPL.UOM
, PPP.PROPOSED_SALARY_N

Columns