View: BEN_CWB_REP_COMP_DTL_V

Product:	BEN - Advanced Benefits
Description:	View for CWB Compensation Detail Report Web ADI Download
Implementation/DBA Data:	APPS.BEN_CWB_REP_COMP_DTL_V

View Text

SELECT BCPIMGR.PERSON_ID MGRPERSONID
, BCGH.LVL_NUM LEVELNUM
, DECODE(BPIL.PER_IN_LER_STAT_CD||TO_CHAR(BCPR.COMP_POSTING_DATE)
, 'STRTD'
, FND_MESSAGE.GET_STRING('BEN'
, 'BEN_92789_TRANSACTION_PENDING')
, TO_CHAR(NVL(BCPR.COMP_POSTING_DATE
, BCPR.LF_EVT_OCRD_DT))) STATUS
, NVL(BCPR.COMP_POSTING_DATE
, BCPR.LF_EVT_OCRD_DT) TRANSACTIONDATE
, DECODE(NAME.PROFILE
, 'FN'
, BCPI.FULL_NAME
, 'CN'
, BCPI.CUSTOM_NAME
, BCPI.BRIEF_NAME) EMPLOYEENAME
, HR_GENERAL.DECODE_ORGANIZATION(BCPI.BUSINESS_GROUP_ID) BUSINESSGROUP
, BCPI.LEGISLATION_CODE COUNTRY
, BCPDGRP.NAME COMPPLAN
, BCPDGRP.PL_ID COMPPLANID
, BCPDACT.NAME LOCALPLAN
, BCPDACT.PL_ID LOCALPLANID
, BCPDOPT.NAME COMPONENTNAME
, BCPDOPT.OIPL_ID COMPONENTID
, BCPR.WS_VAL COMPAMT
, NVL(HR_GENERAL.DECODE_LOOKUP('BEN_NNMNTRY_UOM'
, NVL(BCPDACT.WS_NNMNTRY_UOM
, BCPDGRP.WS_NNMNTRY_UOM))
, NVL(BCPDACT.PL_UOM
, BCPDGRP.PL_UOM)) COMPUOM
, DECODE(NVL(BCPDACT.WS_NNMNTRY_UOM
, BCPDGRP.WS_NNMNTRY_UOM)
, NULL
, (BCPR.WS_VAL/ NVL(BCPDACT.PL_XCHG_RATE
, 1))
, NULL) CONVERTEDAMT
, DECODE(NVL(BCPDACT.WS_NNMNTRY_UOM
, BCPDGRP.WS_NNMNTRY_UOM)
, NULL
, BCPDGRP.PL_UOM
, NULL) CONVERSIONUOM
, DECODE(NVL(BCPDACT.WS_NNMNTRY_UOM
, BCPDGRP.WS_NNMNTRY_UOM)
, NULL
, NVL(BCPDACT.PL_XCHG_RATE
, 1)
, NULL) CONVERSIONEXCHGRATE
, BCPR.ELIG_SAL_VAL ELIGIBLESAL
, ROUND( (BCPR.WS_VAL/DECODE(BCPR.ELIG_SAL_VAL
, 0
, NULL
, BCPR.ELIG_SAL_VAL))*100
, 2) COMPAMTPER
, BCPI.BASE_SALARY BASESAL
, INITCAP(BCPI.BASE_SALARY_FREQUENCY) BASESALFREQ
, DECODE(NVL(BCPDACT.WS_SUB_ACTY_TYP_CD
, BCPDGRP.WS_SUB_ACTY_TYP_CD)
, 'ICM7'
, BCPR.WS_VAL+BCPI.BASE_SALARY) NEWSAL
, BCPR.MISC1_VAL MISCRATE1
, BCPR.MISC2_VAL MISCRATE2
, BCPR.MISC3_VAL MISCRATE3
, BCPR.REC_VAL RECOMMENDEDVAL
, BCPR.STAT_SAL_VAL STATEDSAL
, BCPR.TOT_COMP_VAL TOTALCOMP
, BCPR.OTH_COMP_VAL OTHERCOMP
, DECODE( BCPR.WS_MN_VAL
, NULL
, ''
, BCPR.WS_MN_VAL||' - '||BCPR.WS_MX_VAL) COMPAMTLMTS
, DECODE( BCPR.REC_MN_VAL
, NULL
, ''
, BCPR.REC_MN_VAL||' - '||BCPR.REC_MX_VAL) RECRANGE
, HR_GENERAL.DECODE_GRADE(BCPI.GRADE_ID) GRADE
, DECODE(BCPI.GRD_MIN_VAL
, NULL
, ''
, BCPI.GRD_MIN_VAL||' - '||BCPI.GRD_MAX_VAL) GRADERANGE
, BCPI.GRD_MID_POINT GRADEMIDPOINT
, HR_GENERAL.DECODE_JOB(BCPI.JOB_ID) JOB
, PJD.SEGMENT1 JOBSEGMENT1
, PJD.SEGMENT2 JOBSEGMENT2
, PJD.SEGMENT3 JOBSEGMENT3
, (SELECT TO_NUMBER (PAEI.AEI_INFORMATION1)
FROM PER_ASSIGNMENT_EXTRA_INFO PAEI
WHERE PAEI.ASSIGNMENT_ID = BCPI.ASSIGNMENT_ID
AND PAEI.INFORMATION_TYPE = 'CWBRANK'
AND PAEI.AEI_INFORMATION3 IS NULL
AND PAEI.AEI_INFORMATION2 = BCPIMGR.PERSON_ID) RANKING
, (SELECT HLK.MEANING
FROM BEN_TRANSACTION BT
, HR_LOOKUPS HLK
WHERE BT.TRANSACTION_ID = BCPI.ASSIGNMENT_ID
AND BT.TRANSACTION_TYPE = 'CWBPERF' || TO_CHAR (BCPDGRP.PERF_REVW_STRT_DT
, 'YYYY/MM/DD') || BCPDGRP.EMP_INTERVIEW_TYP_CD
AND HLK.LOOKUP_TYPE = 'PERFORMANCE_RATING'
AND HLK.LOOKUP_CODE = BT.ATTRIBUTE3
AND HLK.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL (HLK.START_DATE_ACTIVE
, SYSDATE)
AND NVL (HLK.END_DATE_ACTIVE
, SYSDATE)) RATING
, BCPI.START_DATE HIREDATE
, HR_GENERAL.DECODE_ORGANIZATION(BCPI.ORGANIZATION_ID) ORGANIZATION
, DECODE(NAME.PROFILE
, 'FN'
, BCPI.SUPERVISOR_FULL_NAME
, 'CN'
, BCPI.SUPERVISOR_CUSTOM_NAME
, BCPI.SUPERVISOR_BRIEF_NAME) WKSHTMGR
, BCPI.CUSTOM_SEGMENT1 CUSTOMSEG1
, BCPI.CUSTOM_SEGMENT2 CUSTOMSEG2
, BCPI.CUSTOM_SEGMENT3 CUSTOMSEG3
, BCPI.CUSTOM_SEGMENT4 CUSTOMSEG4
, BCPI.CUSTOM_SEGMENT5 CUSTOMSEG5
, BCPI.CUSTOM_SEGMENT6 CUSTOMSEG6
, BCPI.CUSTOM_SEGMENT7 CUSTOMSEG7
, BCPI.CUSTOM_SEGMENT8 CUSTOMSEG8
, BCPI.CUSTOM_SEGMENT9 CUSTOMSEG9
, BCPI.CUSTOM_SEGMENT10 CUSTOMSEG10
, BCPI.CUSTOM_SEGMENT11 CUSTOMSEG11
, BCPI.CUSTOM_SEGMENT12 CUSTOMSEG12
, BCPI.CUSTOM_SEGMENT13 CUSTOMSEG13
, BCPI.CUSTOM_SEGMENT14 CUSTOMSEG14
, BCPI.CUSTOM_SEGMENT15 CUSTOMSEG15
, BCPI.CPI_ATTRIBUTE1 CPIATTRIBUTE1
, BCPI.CPI_ATTRIBUTE2 CPIATTRIBUTE2
, BCPI.CPI_ATTRIBUTE3 CPIATTRIBUTE3
, BCPI.CPI_ATTRIBUTE4 CPIATTRIBUTE4
, BCPI.CPI_ATTRIBUTE5 CPIATTRIBUTE5
, BCPI.FULL_NAME EMPLOYEEFULLNAME
, BCPI.EMPLOYEE_NUMBER EMPNUMBER
, BCPI.EMAIL_ADDRESS EMAIL
, BEN_CWB_UTILS.GET_MANAGER_NAME(BCPI.GROUP_PER_IN_LER_ID
, 1) LEVEL1MGR
, BEN_CWB_UTILS.GET_MANAGER_NAME(BCPI.GROUP_PER_IN_LER_ID
, 2) LEVEL2MGR
, BEN_CWB_UTILS.GET_MANAGER_NAME(BCPI.GROUP_PER_IN_LER_ID
, 3) LEVEL3MGR
, BEN_CWB_UTILS.GET_MANAGER_NAME(BCPI.GROUP_PER_IN_LER_ID
, 4) LEVEL4MGR
, BEN_CWB_UTILS.GET_MANAGER_NAME(BCPI.GROUP_PER_IN_LER_ID
, 5) LEVEL5MGR
, BEN_CWB_UTILS.GET_MANAGER_NAME(BCPI.GROUP_PER_IN_LER_ID
, 6) LEVEL6MGR
, BEN_CWB_UTILS.GET_MANAGER_NAME(BCPI.GROUP_PER_IN_LER_ID
, 7) LEVEL7MGR
, BEN_CWB_UTILS.GET_MANAGER_NAME(BCPI.GROUP_PER_IN_LER_ID
, 8) LEVEL8MGR
FROM BEN_CWB_PERSON_INFO BCPIMGR
, BEN_CWB_GROUP_HRCHY BCGH
, BEN_CWB_PERSON_RATES BCPR
, BEN_PER_IN_LER BPIL
, BEN_CWB_PERSON_INFO BCPI
, BEN_CWB_PL_DSGN BCPDGRP
, BEN_CWB_PL_DSGN BCPDACT
, BEN_CWB_PL_DSGN BCPDOPT
, PER_JOB_DEFINITIONS PJD
, PER_JOBS PJ
, (SELECT BEN_CWB_UTILS.GET_PROFILE('BEN_DISPLAY_EMPLOYEE_NAME') PROFILE
FROM DUAL) NAME
WHERE BCPIMGR.GROUP_PER_IN_LER_ID = BCGH.MGR_PER_IN_LER_ID
AND BCGH.EMP_PER_IN_LER_ID = BCPR.GROUP_PER_IN_LER_ID
AND BCPR.ELIG_FLAG = 'Y'
AND BCPI.GROUP_PER_IN_LER_ID = BCGH.EMP_PER_IN_LER_ID
AND BCPI.GROUP_PER_IN_LER_ID = BPIL.PER_IN_LER_ID
AND BPIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND NVL(BCPI.JOB_ID
, -1) = PJ.JOB_ID (+)
AND PJ.JOB_DEFINITION_ID = PJD.JOB_DEFINITION_ID (+)
AND BCPDGRP.GROUP_PL_ID = BPIL.GROUP_PL_ID
AND BCPDGRP.LF_EVT_OCRD_DT = BPIL.LF_EVT_OCRD_DT
AND BCPDGRP.OIPL_ID = -1
AND BCPDGRP.PL_ID = BCPDGRP.GROUP_PL_ID
AND BCPDACT.GROUP_PL_ID(+) = BCPR.GROUP_PL_ID
AND BCPDACT.PL_ID(+) = BCPR.PL_ID
AND BCPDACT.LF_EVT_OCRD_DT (+) = BCPR.LF_EVT_OCRD_DT
AND BCPDACT.OIPL_ID (+) = -1
AND BCPDACT.PL_ID (+) <> BCPDACT.GROUP_PL_ID (+)
AND BCPDOPT.PL_ID(+) = BCPR.GROUP_PL_ID
AND BCPDOPT.OIPL_ID(+) = BCPR.GROUP_OIPL_ID
AND BCPDOPT.LF_EVT_OCRD_DT (+) = BCPR.LF_EVT_OCRD_DT
AND BCPDOPT.OIPL_ID (+) <> -1
AND BCPDOPT.OIPL_ID (+) = BCPDOPT.GROUP_OIPL_ID (+)

Columns