View: HR_CERIDIAN_OAB_BENEFIT_V

Product:	PER - Human Resources
Description:	Ceridian Source 100 payroll interface view
Implementation/DBA Data:	APPS.HR_CERIDIAN_OAB_BENEFIT_V

View Text

SELECT PER.EMPLOYEE_NUMBER
, HOU.NAME
, BPG.NAME
, BPT.NAME
, BPL.NAME
, PBO.COVERAGE_TYPE
, PBV1.CONTRIBUTION_AMOUNT
, PBV1.MAX_ANNUAL_AMOUNT
, DECODE(PBV1.MAX_RT_END_DT
, GREATEST(PBV1.MAX_RT_END_DT
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE))
, 'Y'
, 'N')
, PER.BUSINESS_GROUP_ID
, PER.PERSON_ID
, GREATEST(NVL(PER.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PER.EFFECTIVE_START_DATE)
, GREATEST(NVL(ASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, ASG.EFFECTIVE_START_DATE)
, GREATEST( DECODE( GREATEST( TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PBV1.MAX_RT_END_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PBV1.MAX_RT_END_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PBV1.OABV_LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, NVL(PBV1.OABV_LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, NVL(PBV1.MIN_RT_STRT_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST(NVL(PTP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, GREATEST( DECODE( GREATEST( TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PBV1.MAX_RT_END_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PBV1.MAX_RT_END_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PBV1.OABV_LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PER.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PER.EFFECTIVE_START_DATE
, NVL(ASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, ASG.EFFECTIVE_START_DATE
, NVL(PEN.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PEN.EFFECTIVE_START_DATE
, NVL(PTP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST(NVL(PBV1.OABV_LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PER.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PER.EFFECTIVE_START_DATE
, NVL(ASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, ASG.EFFECTIVE_START_DATE
, NVL(PEN.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PEN.EFFECTIVE_START_DATE
, NVL(PTP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))))
, NVL(PBV1.MIN_RT_STRT_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD' )))
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_PGM_F BPG
, BEN_PL_F BPL
, BEN_PL_TYP_F BPT
, HR_PAY_INTERFACE_OAB_OPTION_V PBO
, HR_PAY_INTERFACE_OAB_VALUE_V PBV1
, PER_ALL_PEOPLE_F PER
, PER_ALL_ASSIGNMENTS_F ASG
, HR_SOFT_CODING_KEYFLEX HS
, HR_ALL_ORGANIZATION_UNITS HOU
, PAY_PAYROLLS_X PRL
, PER_TIME_PERIOD_TYPES PTP
WHERE PEN.EFFECTIVE_START_DATE <= HR_CERIDIAN.GET_CER_EXTRACT_DATE
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND PEN.PL_ID = BPL.PL_ID
AND BPL.PL_TYP_ID = BPT.PL_TYP_ID
AND PEN.PGM_ID = BPG.PGM_ID (+)
AND PEN.OIPL_ID = PBO.OIPL_ID (+)
AND PEN.PRTT_ENRT_RSLT_ID = PBV1.PRTT_ENRT_RSLT_ID(+)
AND PBV1.RT_STRT_DT(+) BETWEEN PEN.EFFECTIVE_START_DATE AND PEN.EFFECTIVE_END_DATE
AND NVL(PBV1.ACTY_TYP_CD(+)
, 'EEPYC') = 'EEPYC'
AND PEN.PERSON_ID = PER.PERSON_ID
AND PER.PERSON_ID = ASG.PERSON_ID
AND ASG.PRIMARY_FLAG = 'Y'
AND ASG.ASSIGNMENT_TYPE = 'E'
AND HS.SOFT_CODING_KEYFLEX_ID = ASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PRL.PAYROLL_ID = ASG.PAYROLL_ID
AND PRL.PERIOD_TYPE = PTP.PERIOD_TYPE
AND PBV1.PRTT_ENRT_RSLT_ID IS NOT NULL
AND BPL.EFFECTIVE_START_DATE = (SELECT MAX(BPL2.EFFECTIVE_START_DATE)
FROM BEN_PL_F BPL2
WHERE BPL.PL_ID = BPL2.PL_ID
AND BPL2.EFFECTIVE_START_DATE <= HR_CERIDIAN.GET_CER_EXTRACT_DATE )
AND (BPG.PGM_ID IS NULL OR BPG.EFFECTIVE_START_DATE = (SELECT MAX(BPG1.EFFECTIVE_START_DATE)
FROM BEN_PGM_F BPG1
WHERE BPG1.PGM_ID = BPG.PGM_ID
AND BPG1.EFFECTIVE_START_DATE <= HR_CERIDIAN.GET_CER_EXTRACT_DATE))
AND BPT.EFFECTIVE_START_DATE = (SELECT MAX(BPT2.EFFECTIVE_START_DATE)
FROM BEN_PL_TYP_F BPT2
WHERE BPT.PL_TYP_ID = BPT2.PL_TYP_ID
AND BPT2.EFFECTIVE_START_DATE <= HR_CERIDIAN.GET_CER_EXTRACT_DATE )
AND PER.EFFECTIVE_START_DATE = (SELECT MAX(PER2.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PER2
WHERE PER2.PERSON_ID = PER.PERSON_ID
AND PER2.EFFECTIVE_START_DATE <= HR_CERIDIAN.GET_CER_EXTRACT_DATE )
AND ASG.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE A2.PERSON_ID = ASG.PERSON_ID
AND A2.PRIMARY_FLAG = 'Y'
AND A2.ASSIGNMENT_TYPE = 'E'
AND A2.EFFECTIVE_START_DATE <= HR_CERIDIAN.GET_CER_EXTRACT_DATE )

Columns