View: HR_ADP_BENEFIT_V

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

View Text

SELECT REF.COMPANY_CODE_EQUIVALENT
, HOU.NAME
, HS.SEGMENT1
, PASG.ORGANIZATION_ID
, PP.EMPLOYEE_NUMBER
, PP.PERSON_ID
, PP.BUSINESS_GROUP_ID
, PASG.ASSIGNMENT_ID
, PASG.ASSIGNMENT_NUMBER
, DECODE(PASG.PRIMARY_FLAG
, 'Y'
, 0
, PASG.ASSIGNMENT_SEQUENCE)
, PASG.PRIMARY_FLAG
, DECODE(PTP2.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP2.NUMBER_PER_FISCAL_YEAR)
, PT.ELEMENT_NAME
, BBC.BENEFIT_CLASSIFICATION_NAME
, TP.NUMBER_PER_FISCAL_YEAR
, PT.ELEMENT_INFORMATION4
, TO_NUMBER(NVL(PV2.SCREEN_ENTRY_VALUE
, BCON.EMPLOYER_CONTRIBUTION))
, TO_NUMBER(NVL(PV3.SCREEN_ENTRY_VALUE
, BCON.EMPLOYEE_CONTRIBUTION))
, BCON.COVERAGE_TYPE
, MIN(PB.EFFECTIVE_START_DATE)
, DECODE(MAX(PB.EFFECTIVE_END_DATE)
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')
, TO_DATE(NULL)
, MAX(PB.EFFECTIVE_END_DATE) + 1)
, GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE)
, GREATEST(NVL(PT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PT.EFFECTIVE_START_DATE)
, GREATEST( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, PB.EFFECTIVE_END_DATE)
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST (PB.EFFECTIVE_END_DATE
, NVL(PB.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, NVL(PB.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, PB.EFFECTIVE_START_DATE)
, GREATEST(NVL(BCON.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, BCON.EFFECTIVE_START_DATE)
, GREATEST(NVL(BBC.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, GREATEST(NVL(TP.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)
, PB.EFFECTIVE_END_DATE)
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST ( PB.EFFECTIVE_END_DATE
, NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, NVL(PT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PT.EFFECTIVE_START_DATE
, NVL(PB.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(BCON.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, BCON.EFFECTIVE_START_DATE
, NVL(BBC.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(TP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST ( NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, NVL(PT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PT.EFFECTIVE_START_DATE
, NVL(PB.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(BCON.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, BCON.EFFECTIVE_START_DATE
, NVL(BBC.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(TP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))))
, PB.EFFECTIVE_START_DATE)
FROM HR_ADP_EMP_REF_V REF
, PER_ALL_PEOPLE_F PP
, PER_PERIODS_OF_SERVICE PPS
, PER_TIME_PERIOD_TYPES TP
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, PER_TIME_PERIOD_TYPES PTP2
, PAY_PAYROLLS_X PPR
, PAY_ELEMENT_ENTRIES_F PB
, BEN_BENEFIT_CONTRIBUTIONS_F BCON
, BEN_BENEFIT_CLASSIFICATIONS BBC
, PER_ALL_ASSIGNMENTS_F PASG
, PAY_ELEMENT_ENTRY_VALUES_F PV1
, PAY_ELEMENT_ENTRY_VALUES_F PV2
, PAY_ELEMENT_ENTRY_VALUES_F PV3
, PAY_INPUT_VALUES_F IV1
, PAY_INPUT_VALUES_F IV2
, PAY_INPUT_VALUES_F IV3
, PAY_ELEMENT_LINKS_F EL
, PAY_ELEMENT_TYPES_F PT
WHERE PPR.PAYROLL_ID = PASG.PAYROLL_ID
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PPR.PERIOD_TYPE = PTP2.PERIOD_TYPE
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PASG.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID
AND PASG.ASSIGNMENT_ID = PB.ASSIGNMENT_ID
AND PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PPS.PERSON_ID = PP.PERSON_ID
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND TRUNC (HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE
AND PT.ELEMENT_INFORMATION4 = TP.PERIOD_TYPE
AND BBC.CONTRIBUTIONS_USED = 'Y' AND BBC.BENEFIT_CLASSIFICATION_ID = PT.BENEFIT_CLASSIFICATION_ID
AND PT.ELEMENT_TYPE_ID = BCON.ELEMENT_TYPE_ID
AND BCON.COVERAGE_TYPE = PV1.SCREEN_ENTRY_VALUE
AND (PV1.INPUT_VALUE_ID + 0 = IV1.INPUT_VALUE_ID
AND IV1.DISPLAY_SEQUENCE = 1
AND PB.ELEMENT_ENTRY_ID = PV1.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = IV1.ELEMENT_TYPE_ID
AND IV1.NAME = 'COVERAGE')
AND (PV2.INPUT_VALUE_ID + 0 = IV2.INPUT_VALUE_ID
AND IV2.DISPLAY_SEQUENCE = 2
AND PB.ELEMENT_ENTRY_ID = PV2.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = IV2.ELEMENT_TYPE_ID)
AND (PV3.INPUT_VALUE_ID + 0 = IV3.INPUT_VALUE_ID
AND IV3.DISPLAY_SEQUENCE = 3
AND PB.ELEMENT_ENTRY_ID = PV3.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = IV3.ELEMENT_TYPE_ID)
AND PB.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID
AND EL.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND BCON.EFFECTIVE_START_DATE = (SELECT MAX(BCON2.EFFECTIVE_START_DATE)
FROM BEN_BENEFIT_CONTRIBUTIONS_F BCON2
WHERE BCON2.ELEMENT_TYPE_ID = BCON.ELEMENT_TYPE_ID
AND BCON2.COVERAGE_TYPE = BCON.COVERAGE_TYPE
AND BCON2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE))
AND PB.EFFECTIVE_START_DATE = (SELECT MAX(PB2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PB2
, PAY_ELEMENT_LINKS_X EL2
WHERE (PB2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PB2.ELEMENT_LINK_ID = EL2.ELEMENT_LINK_ID
AND EL2.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND PB2.ASSIGNMENT_ID = PB.ASSIGNMENT_ID ))
AND PV1.EFFECTIVE_START_DATE= (SELECT MAX(PV12.EFFECTIVE_START_DATE) FROM PAY_ELEMENT_ENTRY_VALUES_F PV12 WHERE (PV12.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) AND PV12.ELEMENT_ENTRY_VALUE_ID = PV1.ELEMENT_ENTRY_VALUE_ID))
AND PV2.EFFECTIVE_START_DATE= (SELECT MAX(PV22.EFFECTIVE_START_DATE) FROM PAY_ELEMENT_ENTRY_VALUES_F PV22 WHERE (PV22.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) AND PV22.ELEMENT_ENTRY_VALUE_ID = PV2.ELEMENT_ENTRY_VALUE_ID))
AND PV3.EFFECTIVE_START_DATE= (SELECT MAX(PV32.EFFECTIVE_START_DATE) FROM PAY_ELEMENT_ENTRY_VALUES_F PV32 WHERE (PV32.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) AND PV32.ELEMENT_ENTRY_VALUE_ID = PV3.ELEMENT_ENTRY_VALUE_ID))
AND PT.EFFECTIVE_START_DATE = (SELECT MAX(PT2.EFFECTIVE_START_DATE) FROM PAY_ELEMENT_TYPES_F PT2 WHERE (PT2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) AND PT2.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID))
AND EL.EFFECTIVE_START_DATE = (SELECT MAX(EL2.EFFECTIVE_START_DATE) FROM PAY_ELEMENT_LINKS_F EL2 WHERE (EL2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) AND EL2.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID))
AND IV1.EFFECTIVE_START_DATE = (SELECT MAX(IV12.EFFECTIVE_START_DATE) FROM PAY_INPUT_VALUES_F IV12 WHERE (IV12.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) AND IV12.INPUT_VALUE_ID = IV1.INPUT_VALUE_ID))
AND IV2.EFFECTIVE_START_DATE = (SELECT MAX(IV22.EFFECTIVE_START_DATE) FROM PAY_INPUT_VALUES_F IV22 WHERE (IV22.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) AND IV22.INPUT_VALUE_ID = IV2.INPUT_VALUE_ID))
AND IV3.EFFECTIVE_START_DATE = (SELECT MAX(IV32.EFFECTIVE_START_DATE) FROM PAY_INPUT_VALUES_F IV32 WHERE (IV32.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) AND IV32.INPUT_VALUE_ID = IV3.INPUT_VALUE_ID))
AND PASG.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE) FROM PER_ALL_ASSIGNMENTS_F A2 WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) AND A2.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID)) GROUP BY REF.COMPANY_CODE_EQUIVALENT
, PP.EMPLOYEE_NUMBER
, HOU.NAME
, HS.SEGMENT1
, PASG.ORGANIZATION_ID
, PP.PERSON_ID
, PP.BUSINESS_GROUP_ID
, PASG.ASSIGNMENT_ID
, PASG.ASSIGNMENT_NUMBER
, PASG.ASSIGNMENT_SEQUENCE
, PASG.PRIMARY_FLAG
, BCON.COVERAGE_TYPE
, BCON.EMPLOYER_CONTRIBUTION
, PT.ELEMENT_NAME
, BBC.BENEFIT_CLASSIFICATION_NAME
, PV2.SCREEN_ENTRY_VALUE
, PV3.SCREEN_ENTRY_VALUE
, BCON.EMPLOYEE_CONTRIBUTION
, PT.ELEMENT_INFORMATION4
, TP.NUMBER_PER_FISCAL_YEAR
, PB.LAST_UPDATE_DATE
, PP.LAST_UPDATE_DATE
, PP.EFFECTIVE_START_DATE
, HOU.LAST_UPDATE_DATE
, HOU.DATE_FROM
, PASG.LAST_UPDATE_DATE
, PASG.EFFECTIVE_START_DATE
, PB.EFFECTIVE_START_DATE
, PB.EFFECTIVE_END_DATE
, BCON.LAST_UPDATE_DATE
, BBC.LAST_UPDATE_DATE
, BCON.EFFECTIVE_START_DATE
, BCON.EFFECTIVE_END_DATE
, PT.LAST_UPDATE_DATE
, PT.EFFECTIVE_START_DATE
, TP.LAST_UPDATE_DATE
, PTP2.NUMBER_PER_FISCAL_YEAR

Columns