View: HR_ADP_OAB_BENEFIT_V

Product:	PER - Human Resources
Description:	ADP payroll interface view
Implementation/DBA Data:	APPS.HR_ADP_OAB_BENEFIT_V

View Text

SELECT REF.COMPANY_CODE_EQUIVALENT
, HOU.NAME
, HS.SEGMENT1
, ASG.ORGANIZATION_ID
, PER.EMPLOYEE_NUMBER
, PER.PERSON_ID
, PER.BUSINESS_GROUP_ID
, ASG.ASSIGNMENT_ID
, ASG.ASSIGNMENT_NUMBER
, DECODE(ASG.PRIMARY_FLAG
, 'Y'
, 0
, ASG.ASSIGNMENT_SEQUENCE)
, ASG.PRIMARY_FLAG
, DECODE(PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, BPG.NAME
, BPL.NAME
, BPT.NAME
, PEV.SCREEN_ENTRY_VALUE
, NULL
, PTD_LIMIT.MX_VAL
, NULL
, PBO.COVERAGE_TYPE
, BPRV1.RT_STRT_DT
, DECODE(TO_DATE('31-12-4712'
, 'DD-MM-YYYY')
, TRUNC(BPRV2.RT_END_DT)
, BPRV2.RT_END_DT
, BPRV2.RT_END_DT+1)
, 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_ADP.GET_ADP_EXTRACT_DATE)
, NVL(BPRV2.RT_END_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST(NVL(BPRV2.RT_END_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(BPRV.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, NVL(BPRV.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, NVL(BPRV1.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_ADP.GET_ADP_EXTRACT_DATE)
, NVL(BPRV2.RT_END_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST(NVL(BPRV2.RT_END_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(BPRV.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'))
, NVL(PTP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST(NVL(BPRV.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'))
, NVL(PTP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))))
, NVL(BPRV1.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
, PER_ALL_PEOPLE_F PER
, PER_ALL_ASSIGNMENTS_F ASG
, HR_ADP_EMP_REF_V REF
, HR_SOFT_CODING_KEYFLEX HS
, HR_ALL_ORGANIZATION_UNITS HOU
, PAY_PAYROLLS_X PRL
, PER_TIME_PERIOD_TYPES PTP
, PAY_ELEMENT_ENTRIES_F PEE
, (SELECT BPL.MX_VAL MX_VAL
, BPRV.PRTT_RT_VAL_ID PRTT_RT_VAL_ID FROM BEN_PTD_LMT_F BPL
, BEN_ACTY_RT_PTD_LMT_F BARPL
, BEN_ACTY_BASE_RT_F BABR
, BEN_PRTT_RT_VAL BPRV
WHERE BPRV.ACTY_BASE_RT_ID = BABR.ACTY_BASE_RT_ID
AND BABR.ACTY_BASE_RT_ID = BARPL.ACTY_BASE_RT_ID
AND BARPL.PTD_LMT_ID = BPL.PTD_LMT_ID
AND BPL.EFFECTIVE_START_DATE = (SELECT MAX(BPL2.EFFECTIVE_START_DATE)
FROM BEN_PTD_LMT_F BPL2
WHERE BPL.PTD_LMT_ID = BPL2.PTD_LMT_ID
AND BPL2.EFFECTIVE_START_DATE <= HR_ADP.GET_ADP_EXTRACT_DATE )
AND BABR.EFFECTIVE_START_DATE = (SELECT MAX(BABR2.EFFECTIVE_START_DATE)
FROM BEN_ACTY_BASE_RT_F BABR2
WHERE BABR.ACTY_BASE_RT_ID = BABR2.ACTY_BASE_RT_ID
AND BABR2.EFFECTIVE_START_DATE <= HR_ADP.GET_ADP_EXTRACT_DATE )
AND BARPL.EFFECTIVE_START_DATE = (SELECT MAX(BARPL2.EFFECTIVE_START_DATE)
FROM BEN_ACTY_RT_PTD_LMT_F BARPL2
WHERE BARPL.ACTY_RT_PTD_LMT_ID = BARPL2.ACTY_RT_PTD_LMT_ID
AND BARPL2.EFFECTIVE_START_DATE <= HR_ADP.GET_ADP_EXTRACT_DATE ) ) PTD_LIMIT
, PAY_ELEMENT_ENTRY_VALUES_F PEV
, BEN_PRTT_RT_VAL BPRV
, BEN_PRTT_RT_VAL BPRV1
, BEN_PRTT_RT_VAL BPRV2
WHERE BPRV.ELEMENT_ENTRY_VALUE_ID = PEV.ELEMENT_ENTRY_VALUE_ID
AND HR_ADP.GET_ADP_EXTRACT_DATE >= BPRV.RT_STRT_DT
AND BPRV.ACTY_TYP_CD IN ('EEPYC')
AND BPRV1.ACTY_TYP_CD IN ('EEPYC')
AND BPRV2.ACTY_TYP_CD IN ('EEPYC')
AND BPRV.PRTT_ENRT_RSLT_ID = BPRV1.PRTT_ENRT_RSLT_ID
AND BPRV.PRTT_ENRT_RSLT_ID = PEN.PRTT_ENRT_RSLT_ID
AND BPRV1.PRTT_ENRT_RSLT_ID = BPRV2.PRTT_ENRT_RSLT_ID
AND BPRV.RT_STRT_DT = (SELECT MAX(BPRVO.RT_STRT_DT)
FROM BEN_PRTT_RT_VAL BPRVO
WHERE BPRVO.PRTT_ENRT_RSLT_ID = BPRV.PRTT_ENRT_RSLT_ID
AND BPRVO.ACTY_TYP_CD = BPRV.ACTY_TYP_CD
AND BPRVO.RT_STRT_DT <= HR_ADP.GET_ADP_EXTRACT_DATE )
AND BPRV1.RT_STRT_DT = (SELECT MIN(BPRVS.RT_STRT_DT)
FROM BEN_PRTT_RT_VAL BPRVS
WHERE BPRVS.PRTT_ENRT_RSLT_ID = BPRV.PRTT_ENRT_RSLT_ID
AND BPRVS.ACTY_TYP_CD IN ('EEPYC'
, 'ERPYC')
AND BPRVS.RT_STRT_DT <= HR_ADP.GET_ADP_EXTRACT_DATE )
AND BPRV2.RT_END_DT = (SELECT MAX(BPRVE.RT_END_DT)
FROM BEN_PRTT_RT_VAL BPRVE
WHERE BPRVE.PRTT_ENRT_RSLT_ID = BPRV.PRTT_ENRT_RSLT_ID
AND BPRVE.ACTY_TYP_CD IN ('EEPYC'
, 'ERPYC') )
AND PEV.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PEE.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND PEE.EFFECTIVE_START_DATE = PEV.EFFECTIVE_START_DATE
AND PEV.EFFECTIVE_END_DATE = PEE.EFFECTIVE_END_DATE
AND PTD_LIMIT.PRTT_RT_VAL_ID (+)= BPRV.PRTT_RT_VAL_ID
AND PEN.EFFECTIVE_START_DATE <= HR_ADP.GET_ADP_EXTRACT_DATE
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND PEN.PGM_ID = BPG.PGM_ID(+)
AND PEN.PL_ID = BPL.PL_ID
AND BPL.PL_TYP_ID = BPT.PL_TYP_ID
AND PEN.OIPL_ID = PBO.OIPL_ID (+)
AND PEN.EFFECTIVE_START_DATE = (SELECT MAX(PEN1.EFFECTIVE_START_DATE)
FROM BEN_PRTT_ENRT_RSLT_F PEN1
WHERE PEN.PRTT_ENRT_RSLT_ID = PEN1.PRTT_ENRT_RSLT_ID
AND HR_ADP.GET_ADP_EXTRACT_DATE BETWEEN PEN1.EFFECTIVE_START_DATE
AND PEN1.EFFECTIVE_END_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 ASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
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 BPRV.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_ADP.GET_ADP_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_ADP.GET_ADP_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_ADP.GET_ADP_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_ADP.GET_ADP_EXTRACT_DATE )
AND ASG.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE A2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND A2.PRIMARY_FLAG = 'Y'
AND A2.ASSIGNMENT_TYPE = 'E'
AND A2.EFFECTIVE_START_DATE <= HR_ADP.GET_ADP_EXTRACT_DATE )
AND PEE.EFFECTIVE_START_DATE = (SELECT MAX(EE2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F EE2
WHERE EE2.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND EE2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) )

Columns