View: HR_ADP_EMP_REF_V

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

View Text

SELECT HS.SEGMENT1
, HS.SEGMENT1
, PP.EMPLOYEE_NUMBER
, PP.PERSON_ID
, PP.BUSINESS_GROUP_ID
, PASG.ASSIGNMENT_ID
, DECODE(PASG.PRIMARY_FLAG
, 'Y'
, 0
, PASG.ASSIGNMENT_SEQUENCE)
, PASG.PRIMARY_FLAG
, PP.NATIONAL_IDENTIFIER
, PP.FIRST_NAME
, PP.LAST_NAME
, PP.MIDDLE_NAMES
, PP.SUFFIX
, PASG.ROWID
, PP.ROWID
, PPR.PERIOD_TYPE
FROM PER_ALL_ASSIGNMENTS_F PASG
, PER_ALL_PEOPLE_F PP
, HR_SOFT_CODING_KEYFLEX HS
, PER_PERIODS_OF_SERVICE PPS
, PAY_PAYROLLS_F PPR
WHERE TRUNC(SYSDATE) BETWEEN PPR.EFFECTIVE_START_DATE
AND PPR.EFFECTIVE_END_DATE
AND PPR.PAYROLL_ID = PASG.PAYROLL_ID
AND PASG.PERSON_ID = PP.PERSON_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_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 PASG.ASSIGNMENT_TYPE = 'E'
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)
AND PPS.PERSON_ID = PP.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID = PASG.PERIOD_OF_SERVICE_ID
AND PPS.DATE_START = (SELECT MAX(PS.DATE_START)
FROM PER_PERIODS_OF_SERVICE PS
WHERE (PS.DATE_START <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PS.PERSON_ID = PP.PERSON_ID))

Columns