View: BEN_CWB_REPT_COMP_COMPARISON_V

Product:	BEN - Advanced Benefits
Description:	Compensation Report Comparison.
Implementation/DBA Data:	APPS.BEN_CWB_REPT_COMP_COMPARISON_V

View Text

SELECT ASND_LF_EVT_DT
, MANAGER_NAME
, MANAGER_FULL_NAME
, PLAN_NAME
, COUNTRY
, ELIGIBLE_EMPLOYEES
, ELIGIBLE_EMPS_W_VAL
, ROUND((ELIGIBLE_EMPS_W_VAL/DECODE(ELIGIBLE_EMPLOYEES
, 0
, 1
, ELIGIBLE_EMPLOYEES)*100)
, 1) PCT_W_VAL
, TOTAL_ALLOCATED
, NVL(RATE_UNITS
, PL_CURRENCY) UNITS
, ROUND((TOTAL_ALLOCATED/DECODE(ELIGIBLE_EMPS_W_VAL
, 0
, 1
, ELIGIBLE_EMPS_W_VAL))) AVERAGE_ALLOCATED
, DECODE(PRESERVE_CD
, 'A'
, BUDGET_AMOUNT
, (BUDGET_PERCENT*ELIGIBLE_SALARIES/100)) BUDGET
, DECODE(PRESERVE_CD
, 'A'
, BUDGET_AMOUNT
, (BUDGET_PERCENT*ELIGIBLE_SALARIES/100))-NVL(TOTAL_ALLOCATED
, 0) UNUSED_BUDGET
, MANAGER_ID
, ENRT_PERD_ID
FROM ( SELECT PPF.PERSON_ID PERSON_ID
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME || ' ' || PPF.SUFFIX MANAGER_NAME
, PPF.FULL_NAME MANAGER_FULL_NAME
, PERD.ASND_LF_EVT_DT ASND_LF_EVT_DT
, PL.NAME || ' ' || OPT.NAME PLAN_NAME
, BG.LEGISLATION_CODE COUNTRY
, LOOKUP.MEANING RATE_UNITS
, PL.NIP_PL_UOM PL_CURRENCY
, PERD.PRSVR_BDGT_CD PRESERVE_CD
, MAX(DECODE (HRCHY_ALL.LVL_NUM
, '0'
, (SELECT COUNT(CHC1.ELIG_PER_ELCTBL_CHC_ID) FROM BEN_PIL_ELCTBL_CHC_POPL POPL1
, BEN_ELIG_PER_ELCTBL_CHC CHC1
, BEN_CWB_HRCHY HRCHY WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID AND POPL1.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID AND HRCHY.LVL_NUM = 1 AND POPL1.PIL_ELCTBL_CHC_POPL_ID = CHC1.PIL_ELCTBL_CHC_POPL_ID AND CHC1.ELIG_FLAG='Y' AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC1.OIPL_ID
, CHC1.PL_ID) )
, (SELECT COUNT(CHC2.ELIG_PER_ELCTBL_CHC_ID) FROM BEN_PIL_ELCTBL_CHC_POPL POPL2
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_CWB_HRCHY HRCHY WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID AND POPL2.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID AND HRCHY.LVL_NUM > 0 AND POPL2.PIL_ELCTBL_CHC_POPL_ID = CHC2.PIL_ELCTBL_CHC_POPL_ID AND CHC2.ELIG_FLAG='Y' AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC2.OIPL_ID
, CHC2.PL_ID) )) ) ELIGIBLE_EMPLOYEES
, MAX(DECODE (HRCHY_ALL.LVL_NUM
, '0'
, (SELECT COUNT(CHC1.ELIG_PER_ELCTBL_CHC_ID)
FROM BEN_PIL_ELCTBL_CHC_POPL POPL1
, BEN_ELIG_PER_ELCTBL_CHC CHC1
, BEN_CWB_HRCHY HRCHY
, BEN_ENRT_RT ENR WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID AND POPL1.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID AND HRCHY.LVL_NUM = 1 AND POPL1.PIL_ELCTBL_CHC_POPL_ID = CHC1.PIL_ELCTBL_CHC_POPL_ID AND CHC1.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC1.OIPL_ID
, CHC1.PL_ID) AND CHC1.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID AND ENR.ACTY_TYP_CD = 'CWBWS' AND ENR.VAL > 0 )
, (SELECT COUNT(CHC2.ELIG_PER_ELCTBL_CHC_ID)
FROM BEN_PIL_ELCTBL_CHC_POPL POPL2
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_CWB_HRCHY HRCHY
, BEN_ENRT_RT ENR
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL2.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND HRCHY.LVL_NUM > 0
AND POPL2.PIL_ELCTBL_CHC_POPL_ID = CHC2.PIL_ELCTBL_CHC_POPL_ID
AND CHC2.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC2.OIPL_ID
, CHC2.PL_ID)
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID
AND ENR.ACTY_TYP_CD = 'CWBWS'
AND ENR.VAL > 0 ) )) ELIGIBLE_EMPS_W_VAL
, MAX(DECODE (HRCHY_ALL.LVL_NUM
, '0'
, (SELECT SUM(ELIG_SAL_DRT.VAL)
FROM BEN_ENRT_RT ELIG_SAL_DRT
, BEN_CWB_HRCHY HRCHY
, BEN_PIL_ELCTBL_CHC_POPL POPL1
, BEN_ELIG_PER_ELCTBL_CHC CHC_DSAL
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL1.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND CHC_DSAL.PIL_ELCTBL_CHC_POPL_ID = POPL1.PIL_ELCTBL_CHC_POPL_ID
AND CHC_DSAL.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC_DSAL.OIPL_ID
, CHC_DSAL.PL_ID)
AND ELIG_SAL_DRT.ELIG_PER_ELCTBL_CHC_ID(+) = CHC_DSAL.ELIG_PER_ELCTBL_CHC_ID
AND ELIG_SAL_DRT.ACTY_TYP_CD(+)='CWBES'
AND HRCHY.LVL_NUM = 1 )
, (SELECT SUM(ELIG_SAL_DRT.VAL)
FROM BEN_ENRT_RT ELIG_SAL_DRT
, BEN_CWB_HRCHY HRCHY
, BEN_PIL_ELCTBL_CHC_POPL POPL2
, BEN_ELIG_PER_ELCTBL_CHC CHC_ASAL
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL2.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND CHC_ASAL.PIL_ELCTBL_CHC_POPL_ID = POPL2.PIL_ELCTBL_CHC_POPL_ID
AND CHC_ASAL.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC_ASAL.OIPL_ID
, CHC_ASAL.PL_ID)
AND ELIG_SAL_DRT.ELIG_PER_ELCTBL_CHC_ID(+) = CHC_ASAL.ELIG_PER_ELCTBL_CHC_ID
AND ELIG_SAL_DRT.ACTY_TYP_CD(+)='CWBES'
AND HRCHY.LVL_NUM > 0 ) )) ELIGIBLE_SALARIES
, MAX(DECODE(PERD.PRSVR_BDGT_CD
, 'A'
, (DECODE (HRCHY_ALL.LVL_NUM
, '0'
, WB_BDGT.ISS_VAL
, NVL(DB_BDGT.ISS_VAL
, (SELECT SUM(NVL(WB_BDGT.ISS_VAL
, 0))
FROM BEN_CWB_HRCHY HRCHY
, BEN_PIL_ELCTBL_CHC_POPL POPL1
, BEN_ENRT_RT WB_BDGT
, BEN_ELIG_PER_ELCTBL_CHC CHC_WB
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL1.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND CHC_WB.PIL_ELCTBL_CHC_POPL_ID = POPL1.PIL_ELCTBL_CHC_POPL_ID
AND CHC_WB.ELIG_FLAG='Y'
AND HRCHY.LVL_NUM >= 0
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC_WB.OIPL_ID
, CHC_WB.PL_ID)
AND WB_BDGT.ELIG_PER_ELCTBL_CHC_ID(+) = CHC_WB.ELIG_PER_ELCTBL_CHC_ID
AND WB_BDGT.ACTY_TYP_CD(+) ='CWBWB') ) ) )
, NULL ) ) BUDGET_AMOUNT
, MAX(DECODE(PERD.PRSVR_BDGT_CD
, 'P'
, (DECODE (HRCHY_ALL.LVL_NUM
, '0'
, WB_BDGT.ISS_VAL
, NVL(DB_BDGT.ISS_VAL
, ( (SELECT NVL(SUM(WB_RATE.ISS_VAL * LOWER_RATE.VAL /100)
, 0) AMT
FROM BEN_ENRT_RT LOWER_RATE
, BEN_ELIG_PER_ELCTBL_CHC LOWER_CHC
, BEN_CWB_HRCHY LOWER_HRCHY
, BEN_CWB_HRCHY HRCHY
, BEN_ELIG_PER_ELCTBL_CHC CHC_ASAL
, BEN_PIL_ELCTBL_CHC_POPL POPL1
, BEN_ENRT_RT WB_RATE
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL1.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND CHC_ASAL.PIL_ELCTBL_CHC_POPL_ID = POPL1.PIL_ELCTBL_CHC_POPL_ID
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC_ASAL.OIPL_ID
, CHC_ASAL.PL_ID)
AND WB_RATE.ELIG_PER_ELCTBL_CHC_ID(+)=CHC_ASAL.ELIG_PER_ELCTBL_CHC_ID
AND WB_RATE.ACTY_TYP_CD(+)='CWBWB'
AND HRCHY.LVL_NUM > 0
AND LOWER_HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND LOWER_CHC.PIL_ELCTBL_CHC_POPL_ID = LOWER_HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(LOWER_CHC.OIPL_ID
, LOWER_CHC.PL_ID)
AND LOWER_RATE.ELIG_PER_ELCTBL_CHC_ID(+)=LOWER_CHC.ELIG_PER_ELCTBL_CHC_ID
AND LOWER_RATE.ACTY_TYP_CD(+) = 'CWBES'
AND LOWER_CHC.ELIG_FLAG='Y'
AND LOWER_HRCHY.LVL_NUM = 1) + (SELECT NVL(SUM(MGR_RATE.VAL * ELIG_SAL_DRT.VAL /100 )
, 0) AMT
FROM BEN_ENRT_RT ELIG_SAL_DRT
, BEN_CWB_HRCHY HRCHY
, BEN_ELIG_PER_ELCTBL_CHC MGR_CHC
, BEN_ELIG_PER_ELCTBL_CHC CHC_DRT
, BEN_ENRT_RT MGR_RATE
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND MGR_CHC.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(MGR_CHC.OIPL_ID
, MGR_CHC.PL_ID)
AND MGR_RATE.ELIG_PER_ELCTBL_CHC_ID = MGR_CHC.PIL_ELCTBL_CHC_POPL_ID
AND MGR_RATE.ACTY_TYP_CD(+)='CWBWB'
AND CHC_DRT.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND CHC_DRT.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC_DRT.OIPL_ID
, CHC_DRT.PL_ID)
AND ELIG_SAL_DRT.ELIG_PER_ELCTBL_CHC_ID(+)=CHC_DRT.ELIG_PER_ELCTBL_CHC_ID
AND ELIG_SAL_DRT.ACTY_TYP_CD(+)='CWBES'
AND HRCHY.LVL_NUM = 1) ) / (SELECT SUM(ELIG_SAL_DRT.VAL)
FROM BEN_ENRT_RT ELIG_SAL_DRT
, BEN_CWB_HRCHY HRCHY
, BEN_PIL_ELCTBL_CHC_POPL POPL1
, BEN_ELIG_PER_ELCTBL_CHC CHC_ASAL
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL1.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND CHC_ASAL.PIL_ELCTBL_CHC_POPL_ID = POPL1.PIL_ELCTBL_CHC_POPL_ID
AND CHC_ASAL.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC_ASAL.OIPL_ID
, CHC_ASAL.PL_ID)
AND ELIG_SAL_DRT.ELIG_PER_ELCTBL_CHC_ID(+)=CHC_ASAL.ELIG_PER_ELCTBL_CHC_ID
AND ELIG_SAL_DRT.ACTY_TYP_CD(+)='CWBES'
AND HRCHY.LVL_NUM > 0 ) * 100 ) ) )
, NULL ) ) BUDGET_PERCENT
, MAX(DECODE (HRCHY_ALL.LVL_NUM
, '0'
, (SELECT SUM(USED.VAL)
FROM BEN_CWB_HRCHY HRCHY
, BEN_ENRT_RT USED
, BEN_ELIG_PER_ELCTBL_CHC CHC_DUSED
, BEN_PIL_ELCTBL_CHC_POPL POPL1
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL1.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND CHC_DUSED.PIL_ELCTBL_CHC_POPL_ID = POPL1.PIL_ELCTBL_CHC_POPL_ID
AND CHC_DUSED.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC_DUSED.OIPL_ID
, CHC_DUSED.PL_ID)
AND HRCHY.LVL_NUM = 1
AND USED.ELIG_PER_ELCTBL_CHC_ID(+) = CHC_DUSED.ELIG_PER_ELCTBL_CHC_ID
AND USED.ACTY_TYP_CD(+) ='CWBWS' )
, (SELECT SUM(USED.VAL)
FROM BEN_CWB_HRCHY HRCHY
, BEN_ENRT_RT USED
, BEN_ELIG_PER_ELCTBL_CHC CHC_AUSED
, BEN_PIL_ELCTBL_CHC_POPL POPL1
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL1.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND CHC_AUSED.PIL_ELCTBL_CHC_POPL_ID = POPL1.PIL_ELCTBL_CHC_POPL_ID
AND CHC_AUSED.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC_AUSED.OIPL_ID
, CHC_AUSED.PL_ID)
AND HRCHY.LVL_NUM > 0
AND USED.ELIG_PER_ELCTBL_CHC_ID(+) = CHC_AUSED.ELIG_PER_ELCTBL_CHC_ID
AND USED.ACTY_TYP_CD(+) ='CWBWS' ) )) TOTAL_ALLOCATED
, POPL.WS_STAT_CD WS_STATUS
, POPL.BDGT_STAT_CD BUDGET_STATUS
, CHC.ELIG_PER_ELCTBL_CHC_ID CHOICE_ID
, POPL.PIL_ELCTBL_CHC_POPL_ID POPL_ID
, POPL.BDGT_ACC_CD BDGT_ACCESS
, POPL.OBJECT_VERSION_NUMBER POPL_OBJ_VER_NUM
, POPL.WS_ACC_CD WS_ACCESS
, MGRPIL.PERSON_ID MANAGER_ID
, PERD.ENRT_PERD_ID
FROM BEN_ENRT_RT DB_BDGT
, BEN_ENRT_RT WB_BDGT
, BEN_CWB_HRCHY HRCHY_ALL
, BEN_CWB_HRCHY HRCHY_DRT
, PER_ALL_PEOPLE_F PPF
, BEN_ELIG_PER_ELCTBL_CHC CHC
, BEN_PIL_ELCTBL_CHC_POPL POPL
, BEN_PER_IN_LER PIL
, BEN_ELIG_PER_ELCTBL_CHC DRT_CHC_WB
, BEN_ELIG_PER_ELCTBL_CHC DRT_CHC_DB
, BEN_PER_IN_LER MGRPIL
, BEN_PIL_ELCTBL_CHC_POPL MGRPOPL
, BEN_ENRT_PERD PERD
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_PL_F PL
, PER_BUSINESS_GROUPS_PERF BG
, BEN_ACTY_BASE_RT_F ABR
, HR_LOOKUPS LOOKUP
WHERE MGRPOPL.PER_IN_LER_ID = MGRPIL.PER_IN_LER_ID
AND MGRPOPL.ENRT_PERD_ID = PERD.ENRT_PERD_ID
AND MGRPOPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY_ALL.MGR_PIL_ELCTBL_CHC_POPL_ID
AND HRCHY_ALL.LVL_NUM >=0
AND HRCHY_DRT.MGR_PIL_ELCTBL_CHC_POPL_ID = HRCHY_ALL.EMP_PIL_ELCTBL_CHC_POPL_ID
AND POPL.PIL_ELCTBL_CHC_POPL_ID = CHC.PIL_ELCTBL_CHC_POPL_ID
AND CHC.PL_ID = PL.PL_ID
AND CHC.OIPL_ID = OIPL.OIPL_ID(+)
AND OIPL.OPT_ID = OPT.OPT_ID(+)
AND NVL(ABR.OIPL_ID
, ABR.PL_ID) = NVL(CHC.OIPL_ID
, PL.PL_ID)
AND ABR.ACTY_TYP_CD = 'CWBWS'
AND LOOKUP.LOOKUP_CODE (+) = ABR.NNMNTRY_UOM
AND LOOKUP.LOOKUP_TYPE (+) = 'BEN_NNMNTRY_UOM'
AND PL.BUSINESS_GROUP_ID = BG.BUSINESS_GROUP_ID
AND POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY_ALL.EMP_PIL_ELCTBL_CHC_POPL_ID
AND HRCHY_DRT.LVL_NUM = 0
AND DRT_CHC_DB.PIL_ELCTBL_CHC_POPL_ID = HRCHY_DRT.MGR_PIL_ELCTBL_CHC_POPL_ID
AND DB_BDGT.ELIG_PER_ELCTBL_CHC_ID(+)= DRT_CHC_DB.ELIG_PER_ELCTBL_CHC_ID
AND DB_BDGT.ACTY_TYP_CD(+) = 'CWBDB'
AND CHC.ELIG_PER_ELCTBL_CHC_ID = DRT_CHC_DB.ELIG_PER_ELCTBL_CHC_ID
AND DRT_CHC_WB.PIL_ELCTBL_CHC_POPL_ID = HRCHY_DRT.EMP_PIL_ELCTBL_CHC_POPL_ID
AND WB_BDGT.ELIG_PER_ELCTBL_CHC_ID(+)= DRT_CHC_WB.ELIG_PER_ELCTBL_CHC_ID
AND WB_BDGT.ACTY_TYP_CD(+) = 'CWBWB'
AND CHC.ELIG_PER_ELCTBL_CHC_ID = DRT_CHC_WB.ELIG_PER_ELCTBL_CHC_ID
AND PIL.PER_IN_LER_ID=POPL.PER_IN_LER_ID
AND PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PERD.ASND_LF_EVT_DT BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PPF.PERSON_ID=PIL.PERSON_ID GROUP BY HRCHY_ALL.LVL_NUM
, PPF.FULL_NAME
, PPF.PERSON_ID
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME || ' ' || PPF.SUFFIX
, PPF.FULL_NAME
, PERD.ASND_LF_EVT_DT
, PL.NAME || ' ' || OPT.NAME
, BG.LEGISLATION_CODE
, LOOKUP.MEANING
, PL.NIP_PL_UOM
, PERD.PRSVR_BDGT_CD
, POPL.WS_STAT_CD
, POPL.BDGT_STAT_CD
, CHC.ELIG_PER_ELCTBL_CHC_ID
, POPL.PIL_ELCTBL_CHC_POPL_ID
, POPL.BDGT_ACC_CD
, POPL.OBJECT_VERSION_NUMBER
, POPL.WS_ACC_CD
, CHC.ELIG_PER_ELCTBL_CHC_ID
, MGRPIL.PERSON_ID
, PERD.ENRT_PERD_ID HAVING HRCHY_ALL.LVL_NUM BETWEEN 0
AND 1 ORDER BY PERD.ASND_LF_EVT_DT
, PPF.FULL_NAME
, PL.NAME || ' ' || OPT.NAME )

Columns