View: BEN_CWB_WS_DOWNLOAD_V

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

View Text

SELECT DECODE(FND_PROFILE.VALUE('BEN_DISPLAY_EMPLOYEE_NAME')
, 'CN'
, PER.CUSTOM_NAME
, 'BN'
, PER.BRIEF_NAME
, PER.FULL_NAME) EMP_NAME
, DECODE(FND_PROFILE.VALUE('BEN_DISPLAY_EMPLOYEE_NAME')
, 'CN'
, PER.SUPERVISOR_CUSTOM_NAME
, 'BN'
, PER.SUPERVISOR_BRIEF_NAME
, PER.SUPERVISOR_FULL_NAME) MGR_NAME
, ( SELECT TO_NUMBER (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
, (PER.BASE_SALARY * PER.PAY_ANNULIZATION_FACTOR / NVL(TEMP.PLANUFACTOR
, TEMP.GRPUFACTOR)) BASE_SALARY
, NVL(TEMP.PLNAME
, TEMP.GRPPLNAME) PL_NAME
, NVL(TEMP.PLXCHGRT
, TEMP.GRPXCHGRT) PL_XCHG_RATE
, PLRT.STAT_SAL_VAL PL_STAT_SAL_VAL
, PLRT.ELIG_SAL_VAL PL_ELIG_SAL_VAL
, PLRT.TOT_COMP_VAL PL_TOT_COMP_VAL
, PLRT.OTH_COMP_VAL PL_OTH_COMP_VAL
, PLRT.WS_VAL PL_WS_VAL
, PLRT.WS_MN_VAL PL_WS_MIN_VAL
, PLRT.WS_MX_VAL PL_WS_MAX_VAL
, PLRT.WS_INCR_VAL PL_WS_INCR_VAL
, PLRT.REC_VAL PL_REC_VAL
, PLRT.REC_MN_VAL PL_REC_MIN_VAL
, PLRT.REC_MX_VAL PL_REC_MAX_VAL
, PLRT.MISC1_VAL PL_MISC1_VAL
, PLRT.MISC2_VAL PL_MISC2_VAL
, PLRT.MISC3_VAL PL_MISC3_VAL
, PLRT.WS_VAL_LAST_UPD_DATE PL_WS_LAST_UPD_DATE
, (SELECT PPL.FIRST_NAME ||' ' || PPL.LAST_NAME ||' ' || PPL.SUFFIX
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
, TEMP.OPT1NAME OPT1_NAME
, TEMP.OPT1XCHGRT OPT1_XCHG_RATE
, NVL2 (TEMP.OIPL1ID
, OPT1RT.STAT_SAL_VAL
, NULL) OPT1_STAT_SAL_VAL
, NVL2 (TEMP.OIPL1ID
, OPT1RT.ELIG_SAL_VAL
, NULL) OPT1_ELIG_SAL_VAL
, NVL2 (TEMP.OIPL1ID
, OPT1RT.TOT_COMP_VAL
, NULL) OPT1_TOT_COMP_VAL
, NVL2 (TEMP.OIPL1ID
, OPT1RT.OTH_COMP_VAL
, NULL) OPT1_OTH_COMP_VAL
, NVL2 (TEMP.OIPL1ID
, OPT1RT.WS_VAL
, NULL) OPT1_WS_VAL
, NVL2 (TEMP.OIPL1ID
, OPT1RT.WS_MN_VAL
, NULL) OPT1_WS_MIN_VAL
, NVL2 (TEMP.OIPL1ID
, OPT1RT.WS_MX_VAL
, NULL) OPT1_WS_MAX_VAL
, NVL2 (TEMP.OIPL1ID
, OPT1RT.WS_INCR_VAL
, NULL) OPT1_WS_INCR_VAL
, NVL2 (TEMP.OIPL1ID
, OPT1RT.REC_VAL
, NULL) OPT1_REC_VAL
, NVL2 (TEMP.OIPL1ID
, OPT1RT.REC_MN_VAL
, NULL) OPT1_REC_MIN_VAL
, NVL2 (TEMP.OIPL1ID
, OPT1RT.REC_MX_VAL
, NULL) OPT1_REC_MAX_VAL
, NVL2 (TEMP.OIPL1ID
, OPT1RT.MISC1_VAL
, NULL) OPT1_MISC1_VAL
, NVL2 (TEMP.OIPL1ID
, OPT1RT.MISC2_VAL
, NULL) OPT1_MISC2_VAL
, NVL2 (TEMP.OIPL1ID
, OPT1RT.MISC3_VAL
, NULL) OPT1_MISC3_VAL
, NVL2 (TEMP.OIPL1ID
, OPT1RT.WS_VAL_LAST_UPD_DATE
, NULL) OPT1_WS_LAST_UPD_DATE
, OPT1PER.FULL_NAME OPT1_WS_LAST_UPD_NAME
, TEMP.OPT2NAME OPT2_NAME
, TEMP.OPT2XCHGRT OPT2_XCHG_RATE
, NVL2 (TEMP.OIPL2ID
, OPT2RT.STAT_SAL_VAL
, NULL) OPT2_STAT_SAL_VAL
, NVL2 (TEMP.OIPL2ID
, OPT2RT.ELIG_SAL_VAL
, NULL) OPT2_ELIG_SAL_VAL
, NVL2 (TEMP.OIPL2ID
, OPT2RT.TOT_COMP_VAL
, NULL) OPT2_TOT_COMP_VAL
, NVL2 (TEMP.OIPL2ID
, OPT2RT.OTH_COMP_VAL
, NULL) OPT2_OTH_COMP_VAL
, NVL2 (TEMP.OIPL2ID
, OPT2RT.WS_VAL
, NULL) OPT2_WS_VAL
, NVL2 (TEMP.OIPL2ID
, OPT2RT.WS_MN_VAL
, NULL) OPT2_WS_MIN_VAL
, NVL2 (TEMP.OIPL2ID
, OPT2RT.WS_MX_VAL
, NULL) OPT2_WS_MAX_VAL
, NVL2 (TEMP.OIPL2ID
, OPT2RT.WS_INCR_VAL
, NULL) OPT2_WS_INCR_VAL
, NVL2 (TEMP.OIPL2ID
, OPT2RT.REC_VAL
, NULL) OPT2_REC_VAL
, NVL2 (TEMP.OIPL2ID
, OPT2RT.REC_MN_VAL
, NULL) OPT2_REC_MIN_VAL
, NVL2 (TEMP.OIPL2ID
, OPT2RT.REC_MX_VAL
, NULL) OPT2_REC_MAX_VAL
, NVL2 (TEMP.OIPL2ID
, OPT2RT.MISC1_VAL
, NULL) OPT2_MISC1_VAL
, NVL2 (TEMP.OIPL2ID
, OPT2RT.MISC2_VAL
, NULL) OPT2_MISC2_VAL
, NVL2 (TEMP.OIPL2ID
, OPT2RT.MISC3_VAL
, NULL) OPT2_MISC3_VAL
, NVL2 (TEMP.OIPL2ID
, OPT2RT.WS_VAL_LAST_UPD_DATE
, NULL) OPT2_WS_LAST_UPD_DATE
, OPT2PER.FULL_NAME OPT2_WS_LAST_UPD_NAME
, TEMP.OPT3NAME OPT3_NAME
, TEMP.OPT3XCHGRT OPT3_XCHG_RATE
, NVL2 (TEMP.OIPL3ID
, OPT3RT.STAT_SAL_VAL
, NULL) OPT3_STAT_SAL_VAL
, NVL2 (TEMP.OIPL3ID
, OPT3RT.ELIG_SAL_VAL
, NULL) OPT3_ELIG_SAL_VAL
, NVL2 (TEMP.OIPL3ID
, OPT3RT.TOT_COMP_VAL
, NULL) OPT3_TOT_COMP_VAL
, NVL2 (TEMP.OIPL3ID
, OPT3RT.OTH_COMP_VAL
, NULL) OPT3_OTH_COMP_VAL
, NVL2 (TEMP.OIPL3ID
, OPT3RT.WS_VAL
, NULL) OPT3_WS_VAL
, NVL2 (TEMP.OIPL3ID
, OPT3RT.WS_MN_VAL
, NULL) OPT3_WS_MIN_VAL
, NVL2 (TEMP.OIPL3ID
, OPT3RT.WS_MX_VAL
, NULL) OPT3_WS_MAX_VAL
, NVL2 (TEMP.OIPL3ID
, OPT3RT.WS_INCR_VAL
, NULL) OPT3_WS_INCR_VAL
, NVL2 (TEMP.OIPL3ID
, OPT3RT.REC_VAL
, NULL) OPT3_REC_VAL
, NVL2 (TEMP.OIPL3ID
, OPT3RT.REC_MN_VAL
, NULL) OPT3_REC_MIN_VAL
, NVL2 (TEMP.OIPL3ID
, OPT3RT.REC_MX_VAL
, NULL) OPT3_REC_MAX_VAL
, NVL2 (TEMP.OIPL3ID
, OPT3RT.MISC1_VAL
, NULL) OPT3_MISC1_VAL
, NVL2 (TEMP.OIPL3ID
, OPT3RT.MISC2_VAL
, NULL) OPT3_MISC2_VAL
, NVL2 (TEMP.OIPL3ID
, OPT3RT.MISC3_VAL
, NULL) OPT3_MISC3_VAL
, NVL2 (TEMP.OIPL3ID
, OPT3RT.WS_VAL_LAST_UPD_DATE
, NULL) OPT3_WS_LAST_UPD_DATE
, OPT3PER.FULL_NAME OPT3_WS_LAST_UPD_NAME
, TEMP.OPT4NAME OPT4_NAME
, TEMP.OPT4XCHGRT OPT4_XCHG_RATE
, NVL2 (TEMP.OIPL4ID
, OPT4RT.STAT_SAL_VAL
, NULL) OPT4_STAT_SAL_VAL
, NVL2 (TEMP.OIPL4ID
, OPT4RT.ELIG_SAL_VAL
, NULL) OPT4_ELIG_SAL_VAL
, NVL2 (TEMP.OIPL4ID
, OPT4RT.TOT_COMP_VAL
, NULL) OPT4_TOT_COMP_VAL
, NVL2 (TEMP.OIPL4ID
, OPT4RT.OTH_COMP_VAL
, NULL) OPT4_OTH_COMP_VAL
, NVL2 (TEMP.OIPL4ID
, OPT4RT.WS_VAL
, NULL) OPT4_WS_VAL
, NVL2 (TEMP.OIPL4ID
, OPT4RT.WS_MN_VAL
, NULL) OPT4_WS_MIN_VAL
, NVL2 (TEMP.OIPL4ID
, OPT4RT.WS_MX_VAL
, NULL) OPT4_WS_MAX_VAL
, NVL2 (TEMP.OIPL4ID
, OPT4RT.WS_INCR_VAL
, NULL) OPT4_WS_INCR_VAL
, NVL2 (TEMP.OIPL4ID
, OPT4RT.REC_VAL
, NULL) OPT4_REC_VAL
, NVL2 (TEMP.OIPL4ID
, OPT4RT.REC_MN_VAL
, NULL) OPT4_REC_MIN_VAL
, NVL2 (TEMP.OIPL4ID
, OPT4RT.REC_MX_VAL
, NULL) OPT4_REC_MAX_VAL
, NVL2 (TEMP.OIPL4ID
, OPT4RT.MISC1_VAL
, NULL) OPT4_MISC1_VAL
, NVL2 (TEMP.OIPL4ID
, OPT4RT.MISC2_VAL
, NULL) OPT4_MISC2_VAL
, NVL2 (TEMP.OIPL4ID
, OPT4RT.MISC3_VAL
, NULL) OPT4_MISC3_VAL
, NVL2 (TEMP.OIPL4ID
, OPT4RT.WS_VAL_LAST_UPD_DATE
, NULL) OPT4_WS_LAST_UPD_DATE
, OPT4PER.FULL_NAME OPT4_WS_LAST_UPD_NAME
, PER.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, HR_GENERAL.DECODE_LOOKUP ('EMP_CAT'
, PER.EMP_CATEGORY) 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
, DECODE (TO_CHAR(PER.GRD_MAX_VAL)
, NULL
, TO_CHAR(PER.GRD_MIN_VAL)
, (TO_CHAR(PER.GRD_MIN_VAL) || ' - ' || TO_CHAR(PER.GRD_MAX_VAL)) ) GRADE_RANGE
, PER.GRD_MID_POINT GRADE_MID_POINT
, PER.GRD_QUARTILE GRD_QUARTILE
, PER.GRD_COMPARATIO GRD_COMPARATIO
, HR_GENERAL.DECODE_LOOKUP('PERFORMANCE_RATING'
, PER.PERFORMANCE_RATING) PERFORMANCE_RATING
, PER.PERFORMANCE_RATING_TYPE PERFORMANCE_RATING_TYPE
, PER.PERFORMANCE_RATING_DATE PERFORMANCE_RATING_DATE
, (SELECT TO_NUMBER (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
, PER.CHANGE_REASON 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 (TEMP.OIPL1ID
, NULL
, BEN_CWB_WEBADI_UTILS.ENCRYPT (RPAD (TO_CHAR (PLRT.PERSON_RATE_ID)
, 8
, ' '))
, NULL ) PL_PERSON_RATE_ID
, NVL2 (TEMP.OIPL1ID
, BEN_CWB_WEBADI_UTILS.ENCRYPT (RPAD (TO_CHAR (OPT1RT.PERSON_RATE_ID)
, 8
, ' '))
, NULL ) P_OPT1_PERSON_RATE_ID
, NVL2 (TEMP.OIPL2ID
, BEN_CWB_WEBADI_UTILS.ENCRYPT (RPAD (TO_CHAR (OPT2RT.PERSON_RATE_ID)
, 8
, ' '))
, NULL ) P_OPT2_PERSON_RATE_ID
, NVL2 (TEMP.OIPL3ID
, BEN_CWB_WEBADI_UTILS.ENCRYPT (RPAD (TO_CHAR (OPT3RT.PERSON_RATE_ID)
, 8
, ' '))
, NULL ) P_OPT3_PERSON_RATE_ID
, NVL2 (TEMP.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'||TEMP.PERF_DATE||TEMP.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'||TEMP.ASG_DATE
AND TO_NUMBER(ATXN.ATTRIBUTE5) = JOB1.JOB_ID(+)
AND JOB1.LANGUAGE(+) = USERENV ('LANG') ) PROPOSED_JOB
, NVL(TEMP.PLUNITS
, TEMP. GRPUNITS) PLAN_UOM
, NVL2 (TEMP.OIPL1ID
, TEMP.OPT1UNITS
, NULL) OPT1_UOM
, NVL2 (TEMP.OIPL2ID
, TEMP.OPT2UNITS
, NULL) OPT2_UOM
, NVL2 (TEMP.OIPL3ID
, TEMP.OPT3UNITS
, NULL) OPT3_UOM
, NVL2 (TEMP.OIPL4ID
, TEMP.OPT4UNITS
, 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'||TEMP.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'||TEMP.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
FROM BEN_CWB_GROUP_HRCHY HRCHY
, 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_PERSON_INFO PER
, BEN_CWB_PERSON_INFO PLPER
, BEN_CWB_PERSON_INFO OPT1PER
, BEN_CWB_PERSON_INFO OPT2PER
, BEN_CWB_PERSON_INFO OPT3PER
, BEN_CWB_PERSON_INFO OPT4PER
, 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
, BEN_PER_IN_LER MGR_PIL
, (SELECT PL1.PL_ID PLID
, PL1.NAME PLNAME
, PL1.OIPL_ID PLOIPLID
, PL1.WS_ABR_ID PLWSABRID
, PL1.ACTUAL_FLAG PLACTULFLAG
, PL1.PL_XCHG_RATE PLXCHGRT
, PL1.PL_ANNULIZATION_FACTOR PLANUFACTOR
, NVL(HR_GENERAL.DECODE_LOOKUP('BEN_NNMNTRY_UOM'
, PL1.WS_NNMNTRY_UOM)
, PL1.PL_UOM) PLUNITS
, 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.PL_XCHG_RATE OPT1XCHGRT
, NVL(HR_GENERAL.DECODE_LOOKUP('BEN_NNMNTRY_UOM'
, OPT1.WS_NNMNTRY_UOM)
, OPT1.PL_UOM) OPT1UNITS
, 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.PL_XCHG_RATE OPT2XCHGRT
, NVL(HR_GENERAL.DECODE_LOOKUP('BEN_NNMNTRY_UOM'
, OPT2.WS_NNMNTRY_UOM)
, OPT2.PL_UOM) OPT2UNITS
, 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.PL_XCHG_RATE OPT3XCHGRT
, NVL(HR_GENERAL.DECODE_LOOKUP('BEN_NNMNTRY_UOM'
, OPT3.WS_NNMNTRY_UOM)
, OPT3.PL_UOM) OPT3UNITS
, 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.PL_XCHG_RATE OPT4XCHGRT
, NVL(HR_GENERAL.DECODE_LOOKUP('BEN_NNMNTRY_UOM'
, OPT4.WS_NNMNTRY_UOM)
, OPT4.PL_UOM) OPT4UNITS
, GRP_PL.PL_ID GRPPLID
, GRP_PL.OIPL_ID GRPOIPLID
, 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_XCHG_RATE GRPXCHGRT
, GRP_PL.PL_ANNULIZATION_FACTOR GRPUFACTOR
, NVL(HR_GENERAL.DECODE_LOOKUP('BEN_NNMNTRY_UOM'
, GRP_PL.WS_NNMNTRY_UOM)
, GRP_PL.PL_UOM) GRPUNITS
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
WHERE 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.GROUP_PL_ID(+) <> PL1.PL_ID(+)
AND PL1.OIPL_ID(+) = -1
AND OPT1.GROUP_PL_ID(+) = GRP_PL.GROUP_PL_ID
AND OPT1.LF_EVT_OCRD_DT(+) = GRP_PL.LF_EVT_OCRD_DT
AND OPT1.OIPL_ID(+) <> -1
AND OPT1.OIPL_ORDR_NUM(+) = 1
AND (OPT1.PL_ID = DECODE(PL1.ACTUAL_FLAG
, 'Y'
, PL1.PL_ID
, GRP_PL.GROUP_PL_ID) OR OPT1.PL_ID IS NULL)
AND OPT2.GROUP_PL_ID(+) = GRP_PL.GROUP_PL_ID
AND OPT2.LF_EVT_OCRD_DT(+) = GRP_PL.LF_EVT_OCRD_DT
AND OPT2.OIPL_ID(+) <> -1
AND OPT2.OIPL_ORDR_NUM(+) = 2
AND (OPT2.PL_ID = DECODE(PL1.ACTUAL_FLAG
, 'Y'
, PL1.PL_ID
, GRP_PL.GROUP_PL_ID) OR OPT2.PL_ID IS NULL)
AND OPT3.GROUP_PL_ID(+) = GRP_PL.GROUP_PL_ID
AND OPT3.LF_EVT_OCRD_DT(+) = GRP_PL.LF_EVT_OCRD_DT
AND OPT3.OIPL_ID(+) <> -1
AND OPT3.OIPL_ORDR_NUM(+) = 3
AND (OPT3.PL_ID = DECODE(PL1.ACTUAL_FLAG
, 'Y'
, PL1.PL_ID
, GRP_PL.GROUP_PL_ID) OR OPT3.PL_ID IS NULL)
AND OPT4.GROUP_PL_ID(+) = GRP_PL.GROUP_PL_ID
AND OPT4.LF_EVT_OCRD_DT(+) = GRP_PL.LF_EVT_OCRD_DT
AND OPT4.OIPL_ID(+) <> -1
AND OPT4.OIPL_ORDR_NUM(+) = 4
AND (OPT4.PL_ID = DECODE(PL1.ACTUAL_FLAG
, 'Y'
, PL1.PL_ID
, GRP_PL.GROUP_PL_ID) OR OPT4.PL_ID IS NULL)) TEMP
WHERE HRCHY.LVL_NUM > 0
AND HRCHY.EMP_PER_IN_LER_ID = PLRT.GROUP_PER_IN_LER_ID
AND PLRT.GROUP_PER_IN_LER_ID = PLPER.GROUP_PER_IN_LER_ID(+)
AND PLRT.WS_VAL_LAST_UPD_BY = PLPER.PERSON_ID(+)
AND PLRT.ELIG_FLAG = 'Y'
AND PLRT.GROUP_PL_ID = TEMP.GRPPLID
AND PLRT.LF_EVT_OCRD_DT = TEMP.GRPLFEVTORCDDT
AND PLRT.GROUP_OIPL_ID = -1
AND PLRT.PL_ID = NVL(TEMP.PLID
, TEMP.GRPPLID)
AND HRCHY.EMP_PER_IN_LER_ID = OPT1RT.GROUP_PER_IN_LER_ID
AND OPT1RT.GROUP_PER_IN_LER_ID = OPT1PER.GROUP_PER_IN_LER_ID(+)
AND OPT1RT.WS_VAL_LAST_UPD_BY = OPT1PER.PERSON_ID(+)
AND OPT1RT.GROUP_PL_ID = TEMP.GRPPLID
AND OPT1RT.LF_EVT_OCRD_DT = TEMP.GRPLFEVTORCDDT
AND OPT1RT.GROUP_OIPL_ID = NVL(TEMP.OIP1GRPOIPLID
, -1)
AND OPT1RT.PL_ID = NVL(TEMP.OIP1PLID
, NVL(TEMP.PLID
, TEMP.GRPPLID))
AND OPT1RT.OIPL_ID = NVL(NVL(TEMP.OIPL1ID
, TEMP.OIP1GRPOIPLID)
, -1)
AND OPT1RT.ELIG_FLAG = 'Y'
AND HRCHY.EMP_PER_IN_LER_ID = OPT2RT.GROUP_PER_IN_LER_ID
AND OPT2RT.GROUP_PER_IN_LER_ID = OPT2PER.GROUP_PER_IN_LER_ID(+)
AND OPT2RT.WS_VAL_LAST_UPD_BY = OPT2PER.PERSON_ID(+)
AND OPT2RT.GROUP_PL_ID = TEMP.GRPPLID
AND OPT2RT.LF_EVT_OCRD_DT = TEMP.GRPLFEVTORCDDT
AND OPT2RT.GROUP_OIPL_ID = NVL(TEMP.OIP2GRPOIPLID
, -1)
AND OPT2RT.PL_ID = NVL(TEMP.OIP2PLID
, NVL(TEMP.PLID
, TEMP.GRPPLID))
AND OPT2RT.OIPL_ID = NVL(NVL(TEMP.OIPL2ID
, TEMP.OIP2GRPOIPLID)
, -1)
AND OPT2RT.ELIG_FLAG = 'Y'
AND HRCHY.EMP_PER_IN_LER_ID = OPT3RT.GROUP_PER_IN_LER_ID
AND OPT3RT.GROUP_PER_IN_LER_ID = OPT3PER.GROUP_PER_IN_LER_ID(+)
AND OPT3RT.WS_VAL_LAST_UPD_BY = OPT3PER.PERSON_ID(+)
AND OPT3RT.GROUP_PL_ID = TEMP.GRPPLID
AND OPT3RT.LF_EVT_OCRD_DT = TEMP.GRPLFEVTORCDDT
AND OPT3RT.GROUP_OIPL_ID = NVL(TEMP.OIP3GRPOIPLID
, -1)
AND OPT3RT.PL_ID = NVL(TEMP.OIP3PLID
, NVL(TEMP.PLID
, TEMP.GRPPLID))
AND OPT3RT.OIPL_ID = NVL(NVL(TEMP.OIPL3ID
, TEMP.OIP3GRPOIPLID)
, -1)
AND OPT3RT.ELIG_FLAG = 'Y'
AND HRCHY.EMP_PER_IN_LER_ID = OPT4RT.GROUP_PER_IN_LER_ID
AND OPT4RT.GROUP_PER_IN_LER_ID = OPT4PER.GROUP_PER_IN_LER_ID(+)
AND OPT4RT.WS_VAL_LAST_UPD_BY = OPT4PER.PERSON_ID(+)
AND OPT4RT.GROUP_PL_ID = TEMP.GRPPLID
AND OPT4RT.LF_EVT_OCRD_DT = TEMP.GRPLFEVTORCDDT
AND OPT4RT.GROUP_OIPL_ID = NVL(TEMP.OIP4GRPOIPLID
, -1)
AND OPT4RT.PL_ID = NVL(TEMP.OIP4PLID
, NVL(TEMP.PLID
, TEMP.GRPPLID))
AND OPT4RT.OIPL_ID = NVL(NVL(TEMP.OIPL4ID
, TEMP.OIP4GRPOIPLID)
, -1)
AND OPT4RT.ELIG_FLAG = 'Y'
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 HRCHY.MGR_PER_IN_LER_ID = MGR_PIL.PER_IN_LER_ID
AND MGR_PIL.GROUP_PL_ID = TEMP.GRPPLID
AND MGR_PIL.LF_EVT_OCRD_DT = TEMP.GRPLFEVTORCDDT

Columns