View: BEN_CWB_REPT_STOCK_DETAIL_V

Product:	BEN - Advanced Benefits
Description:	Stock Detail Report.
Implementation/DBA Data:	APPS.BEN_CWB_REPT_STOCK_DETAIL_V

View Text

SELECT NVL(PRT.RT_STRT_DT
, WS_RT.RT_STRT_DT) RT_STRT_DT
, MAX(PL.NAME) PLAN_NAME
, MAX(WS_MGR_PPF.FULL_NAME) WS_MGR_FULL_NAME
, MAX(EMP_PPF.FULL_NAME) EMP_FULL_NAME
, MAX(EMP_PPF.EMPLOYEE_NUMBER) EMPNO
, MAX(IMM_MGR_PPF.FULL_NAME) IMM_MGR_FULL_NAME
, MAX(WS_MGR_PPF.FIRST_NAME)||' '||MAX(WS_MGR_PPF.LAST_NAME)||' '||MAX(WS_MGR_PPF.SUFFIX) WS_MGR_BRIEF_NAME
, MAX(EMP_PPF.FIRST_NAME)||' '||MAX(EMP_PPF.LAST_NAME)||' '||MAX(EMP_PPF.SUFFIX) EMP_BRIEF_NAME
, MAX(IMM_MGR_PPF.FIRST_NAME)||' '||MAX(IMM_MGR_PPF.LAST_NAME)||' '||MAX(IMM_MGR_PPF.SUFFIX) IMM_MGR_BRIEF_NAME
, MAX(EMP_PPF.ATTRIBUTE1) PERSON_ATTRIBUTE1
, MAX(EMP_PPF.ATTRIBUTE2) PERSON_ATTRIBUTE2
, MAX(EMP_PPF.ATTRIBUTE3) PERSON_ATTRIBUTE3
, MAX(EMP_PPF.ATTRIBUTE4) PERSON_ATTRIBUTE4
, MAX(EMP_PPF.ATTRIBUTE5) PERSON_ATTRIBUTE5
, MAX(EMP_PPF.ATTRIBUTE6) PERSON_ATTRIBUTE6
, MAX(EMP_PPF.ATTRIBUTE7) PERSON_ATTRIBUTE7
, MAX(EMP_PPF.ATTRIBUTE8) PERSON_ATTRIBUTE8
, MAX(EMP_PPF.ATTRIBUTE9) PERSON_ATTRIBUTE9
, MAX(EMP_PPF.ATTRIBUTE10) PERSON_ATTRIBUTE10
, SUM(NVL(PRT.RT_VAL
, WS_RT.VAL)) WS_AMOUNT
, MAX(NVL(NNMNTRY_LOOKUP.MEANING
, PL.NIP_PL_UOM)) WS_AMOUNT_UNITS
, MAX(MGR_HRCHY.LVL_NUM) LEVELS_DOWN
, MAX(DECODE(EMP_POPL.COMMENTS
, NULL
, NULL
, SUBSTR(EMP_POPL.COMMENTS
, 0
, 35))) COMMENTS
, MAX(JOB_TL.NAME) JOB
, MAX(JOB_DEF.SEGMENT1) JOB_SEGMENT1
, MAX(JOB_DEF.SEGMENT2) JOB_SEGMENT2
, MAX(JOB_DEF.SEGMENT3) JOB_SEGMENT3
, MAX(JOB_DEF.SEGMENT4) JOB_SEGMENT4
, MAX(JOB_DEF.SEGMENT5) JOB_SEGMENT5
, MAX(GRADE_TL.NAME) GRADE
, MAX(BENUTILS.BASIS_TO_PLAN_CONVERSION (PL.PL_ID
, WS_RT.RT_STRT_DT
, (SELECT PPP.PROPOSED_SALARY_N
FROM PER_PAY_PROPOSALS PPP
WHERE PPP.ASSIGNMENT_ID = EMP_POPL.ASSIGNMENT_ID
AND PPP.CHANGE_DATE = (SELECT MAX(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = EMP_POPL.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE < NVL(NVL(PRT.RT_STRT_DT
, WS_RT.RT_STRT_DT)
, SYSDATE)))
, ASG.ASSIGNMENT_ID)) BASE_SALARY
, MAX(PL.NIP_PL_UOM) || ' ' || MAX(ACTY_REF_LOOKUP.MEANING) BASE_SAL_CURR_FREQ
, MAX((SELECT MAX(AEI.AEI_INFORMATION1)
FROM PER_ASSIGNMENT_EXTRA_INFO AEI
WHERE AEI.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND AEI.INFORMATION_TYPE = 'CWBRANK'
AND AEI.AEI_INFORMATION2 = MGR_PIL.PERSON_ID
AND NVL(AEI.AEI_INFORMATION3
, -1) = NVL(EMP_CHC.PRTT_ENRT_RSLT_ID
, -1))) RANK
, MAX(EMP_PPF.START_DATE) HIRE_DATE
, MAX(ORG_TL.NAME) ORG_NAME
, MAX(BG.LEGISLATION_CODE) COUNTRY
, MAX((SELECT MAX(PT.USER_PERSON_TYPE)
FROM PER_PERSON_TYPES PT
, PER_PERSON_TYPE_USAGES_F PTU
WHERE PT.SYSTEM_PERSON_TYPE IN ('EMP'
, 'EX_EMP'
, 'EMP_APL'
, 'EX_EMP_APL')
AND PT.PERSON_TYPE_ID = PTU.PERSON_TYPE_ID
AND PTU.PERSON_ID = EMP_PPF.PERSON_ID
AND SYSDATE BETWEEN PTU.EFFECTIVE_START_DATE
AND PTU.EFFECTIVE_END_DATE)) TODAYS_EMPLOYMENT_STATUS
, MGR_POPL.ENRT_PERD_ID
, MGR_PIL.PERSON_ID
, MGR_HRCHY.LVL_NUM
FROM BEN_PIL_ELCTBL_CHC_POPL MGR_POPL
, BEN_PIL_ELCTBL_CHC_POPL EMP_POPL
, BEN_PIL_ELCTBL_CHC_POPL IMM_MGR_POPL
, BEN_PER_IN_LER MGR_PIL
, BEN_PER_IN_LER EMP_PIL
, BEN_PER_IN_LER IMM_MGR_PIL
, BEN_CWB_HRCHY MGR_HRCHY
, BEN_CWB_HRCHY IMM_MGR_HRCHY
, PER_ALL_PEOPLE_F WS_MGR_PPF
, PER_ALL_PEOPLE_F EMP_PPF
, PER_ALL_PEOPLE_F IMM_MGR_PPF
, BEN_ELIG_PER_ELCTBL_CHC EMP_CHC
, BEN_PL_F PL
, BEN_ENRT_RT WS_RT
, BEN_PRTT_RT_VAL PRT
, BEN_ACTY_BASE_RT_F ABR
, PER_ALL_ASSIGNMENTS_F ASG
, PER_BUSINESS_GROUPS_PERF BG
, PER_JOBS JOB
, PER_JOB_DEFINITIONS JOB_DEF
, PER_GRADES GRADE
, HR_ORGANIZATION_UNITS ORG
, HR_LOOKUPS NNMNTRY_LOOKUP
, HR_LOOKUPS ACTY_REF_LOOKUP
, PER_JOBS_TL JOB_TL
, HR_ALL_ORGANIZATION_UNITS_TL ORG_TL
, PER_GRADES_TL GRADE_TL
WHERE MGR_POPL.PER_IN_LER_ID = MGR_PIL.PER_IN_LER_ID
AND MGR_POPL.PL_ID = PL.PL_ID
AND MGR_POPL.PIL_ELCTBL_CHC_POPL_ID = MGR_HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND MGR_HRCHY.LVL_NUM > 0
AND MGR_HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID = EMP_POPL.PIL_ELCTBL_CHC_POPL_ID
AND EMP_POPL.WS_MGR_ID = WS_MGR_PPF.PERSON_ID(+)
AND EMP_POPL.PIL_ELCTBL_CHC_POPL_ID = EMP_CHC.PIL_ELCTBL_CHC_POPL_ID
AND EMP_CHC.ELIG_PER_ELCTBL_CHC_ID = WS_RT.ELIG_PER_ELCTBL_CHC_ID
AND WS_RT.ACTY_TYP_CD = 'CWBWS'
AND WS_RT.PRTT_RT_VAL_ID = PRT.PRTT_RT_VAL_ID (+)
AND WS_RT.ACTY_BASE_RT_ID = ABR.ACTY_BASE_RT_ID (+)
AND NNMNTRY_LOOKUP.LOOKUP_CODE (+) = ABR.NNMNTRY_UOM
AND NNMNTRY_LOOKUP.LOOKUP_TYPE (+) = 'BEN_NNMNTRY_UOM'
AND EMP_POPL.PER_IN_LER_ID = EMP_PIL.PER_IN_LER_ID
AND EMP_PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND EMP_PIL.PERSON_ID = EMP_PPF.PERSON_ID
AND EMP_POPL.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID (+)
AND EMP_CHC.ELIG_FLAG = 'Y'
AND EMP_CHC.ELCTBL_FLAG = 'Y'
AND ASG.JOB_ID = JOB.JOB_ID (+)
AND JOB.JOB_ID = JOB_TL.JOB_ID(+)
AND DECODE(JOB_TL.JOB_ID
, NULL
, '1'
, JOB_TL.LANGUAGE) = DECODE(JOB_TL.JOB_ID
, NULL
, '1'
, USERENV('LANG'))
AND JOB.JOB_DEFINITION_ID = JOB_DEF.JOB_DEFINITION_ID (+)
AND ASG.GRADE_ID = GRADE.GRADE_ID (+)
AND GRADE.GRADE_ID = GRADE_TL.GRADE_ID(+)
AND DECODE(GRADE_TL.GRADE_ID
, NULL
, '1'
, GRADE_TL.LANGUAGE) = DECODE(GRADE_TL.GRADE_ID
, NULL
, '1'
, USERENV('LANG'))
AND ASG.ORGANIZATION_ID = ORG.ORGANIZATION_ID (+)
AND ORG.ORGANIZATION_ID = ORG_TL.ORGANIZATION_ID(+)
AND DECODE(ORG_TL.ORGANIZATION_ID
, NULL
, '1'
, ORG_TL.LANGUAGE) = DECODE(ORG_TL.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND EMP_POPL.PIL_ELCTBL_CHC_POPL_ID = IMM_MGR_HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND IMM_MGR_HRCHY.LVL_NUM = DECODE(MGR_HRCHY.LVL_NUM
, 1
, 1
, MGR_HRCHY.LVL_NUM-1)
AND IMM_MGR_HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID = IMM_MGR_POPL.PIL_ELCTBL_CHC_POPL_ID
AND IMM_MGR_POPL.PER_IN_LER_ID = IMM_MGR_PIL.PER_IN_LER_ID
AND IMM_MGR_PIL.PERSON_ID = IMM_MGR_PPF.PERSON_ID
AND ACTY_REF_LOOKUP.LOOKUP_CODE(+) = PL.NIP_ACTY_REF_PERD_CD
AND ACTY_REF_LOOKUP.LOOKUP_TYPE(+) = 'BEN_ACTY_REF_PERD'
AND BG.BUSINESS_GROUP_ID = EMP_PPF.BUSINESS_GROUP_ID
AND SYSDATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN ABR.EFFECTIVE_START_DATE
AND ABR.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN ASG.EFFECTIVE_START_DATE (+)
AND ASG.EFFECTIVE_END_DATE(+)
AND SYSDATE BETWEEN WS_MGR_PPF.EFFECTIVE_START_DATE (+)
AND WS_MGR_PPF.EFFECTIVE_END_DATE (+)
AND SYSDATE BETWEEN EMP_PPF.EFFECTIVE_START_DATE
AND EMP_PPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN IMM_MGR_PPF.EFFECTIVE_START_DATE
AND IMM_MGR_PPF.EFFECTIVE_END_DATE GROUP BY EMP_POPL.PIL_ELCTBL_CHC_POPL_ID
, PL.PL_ID
, PRT.RT_STRT_DT
, WS_RT.RT_STRT_DT
, MGR_POPL.ENRT_PERD_ID
, MGR_PIL.PERSON_ID
, MGR_HRCHY.LVL_NUM ORDER BY RT_STRT_DT DESC
, PLAN_NAME
, EMP_FULL_NAME

Columns