View: HR_CERIDIAN_500_OAB_BENEFIT_V

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

View Text

SELECT PER.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, NVL(HR_PAY_INTERFACE_PKG.GET_EEPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MIN_RT_STRT_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, HR_PAY_INTERFACE_PKG.GET_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MIN_RT_STRT_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)) EFFECTIVE_START_DATE
, DECODE(TO_DATE('31-12-4712'
, 'DD-MM-YYYY')
, NVL(HR_PAY_INTERFACE_PKG.GET_EEPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, HR_PAY_INTERFACE_PKG.GET_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE))
, TO_DATE(NULL)
, NVL(HR_PAY_INTERFACE_PKG.GET_EEPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, HR_PAY_INTERFACE_PKG.GET_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE))) EFFECTIVE_END_DATE
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASG.PRIMARY_FLAG PRIMARY_FLAG
, PER.NATIONAL_IDENTIFIER SOCIAL_SECURITY_NUMBER
, HOU.NAME COMPANY
, BPG.NAME PROGRAM
, BPT.NAME PLAN_TYPE
, BPL.NAME PLAN_NAME
, OPT.NAME OPTION_NAME
, HR_PAY_INTERFACE_PKG.GET_EEPYC_VARCHAR2(PEN.PRTT_ENRT_RSLT_ID
, 'CONTRIBUTION_AMOUNT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE) EE_CONTRIBUTION
, HR_PAY_INTERFACE_PKG.GET_ERPYC_VARCHAR2(PEN.PRTT_ENRT_RSLT_ID
, 'CONTRIBUTION_AMOUNT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE) ER_CONTRIBUTION
, HR_PAY_INTERFACE_PKG.GET_EEPYC_NUMBER(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_ANNUAL_AMOUNT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE) EE_CONTRIBUTION_LIMIT
, HR_PAY_INTERFACE_PKG.GET_ERPYC_NUMBER(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_ANNUAL_AMOUNT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE) ER_CONTRIBUTION_LIMIT
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, PER.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PER.PERSON_ID PERSON_ID
, GREATEST(NVL(PER.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PER.EFFECTIVE_START_DATE) PER_LAST_UPDATE_DATE
, GREATEST(NVL(ASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, ASG.EFFECTIVE_START_DATE) ASG_LAST_UPDATE_DATE
, GREATEST(DECODE(GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(HR_PAY_INTERFACE_PKG.GET_EEPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(HR_PAY_INTERFACE_PKG.GET_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL( HR_PAY_INTERFACE_PKG.GET_EEPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(HR_PAY_INTERFACE_PKG.GET_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(HR_PAY_INTERFACE_PKG.GET_EEPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'OABV_LAST_UPDATE_DATE'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(HR_PAY_INTERFACE_PKG.GET_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'OABV_LAST_UPDATE_DATE'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST(NVL( HR_PAY_INTERFACE_PKG.GET_EEPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'OABV_LAST_UPDATE_DATE'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(HR_PAY_INTERFACE_PKG.GET_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'OABV_LAST_UPDATE_DATE'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))))
, NVL(NVL(HR_PAY_INTERFACE_PKG.GET_EEPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MIN_RT_STRT_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, HR_PAY_INTERFACE_PKG.GET_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MIN_RT_STRT_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE))
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))) BEN_LAST_UPDATE_DATE
, GREATEST(NVL(PTP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')) TP_LAST_UPDATE_DATE
, GREATEST(DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(HR_PAY_INTERFACE_PKG.GET_EEPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(HR_PAY_INTERFACE_PKG.GET_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL( HR_PAY_INTERFACE_PKG.GET_EEPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(HR_PAY_INTERFACE_PKG.GET_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(HR_PAY_INTERFACE_PKG.GET_EEPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'OABV_LAST_UPDATE_DATE'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(HR_PAY_INTERFACE_PKG.GET_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'OABV_LAST_UPDATE_DATE'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_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( HR_PAY_INTERFACE_PKG.GET_EEPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'OABV_LAST_UPDATE_DATE'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(HR_PAY_INTERFACE_PKG.GET_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'OABV_LAST_UPDATE_DATE'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_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(NVL(HR_PAY_INTERFACE_PKG.GET_EEPYC_DATE( PEN.PRTT_ENRT_RSLT_ID
, 'MIN_RT_STRT_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, HR_PAY_INTERFACE_PKG.GET_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MIN_RT_STRT_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE))
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))) GREATEST_LAST_UPDATE_DATE
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_PL_F BPL
, BEN_PGM_F BPG
, BEN_PL_TYP_F BPT
, BEN_OPT_F OPT
, BEN_OIPL_F COP
, 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 = COP.OIPL_ID (+)
AND OPT.OPT_ID (+) = COP.OPT_ID
AND (COP.OIPL_ID IS NULL OR COP.EFFECTIVE_START_DATE = (SELECT MAX(COP1.EFFECTIVE_START_DATE)
FROM BEN_OIPL_F COP1
WHERE COP1.OIPL_ID = COP.OIPL_ID
AND COP1.EFFECTIVE_START_DATE <= HR_CERIDIAN.GET_CER_EXTRACT_DATE))
AND (OPT.OPT_ID IS NULL OR OPT.EFFECTIVE_START_DATE = (SELECT MAX(OPT1.EFFECTIVE_START_DATE)
FROM BEN_OPT_F OPT1
WHERE OPT1.OPT_ID = OPT.OPT_ID
AND OPT1.EFFECTIVE_START_DATE <= HR_CERIDIAN.GET_CER_EXTRACT_DATE))
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 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)
AND HR_PAY_INTERFACE_PKG.EEPYC_ERPYC_EXIST(PEN.PRTT_ENRT_RSLT_ID
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE) = 'Y'

Columns