View: HR_ADP_ADDRESS_V

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

View Text

SELECT REF.COMPANY_CODE_EQUIVALENT COMPANY_CODE_EQUIVALENT
, HOU.NAME GRE_NAME
, HS.SEGMENT1 GRE_ID
, PASG.ORGANIZATION_ID ORGANIZATION_ID
, PP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PP.PERSON_ID PERSON_ID
, DECODE(PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR) PAY_FREQUENCY
, 0
, PA.ADDRESS_ID ADDRESS_ID
, PA.PRIMARY_FLAG ADDRESS_PRIMARY_FLAG
, PA.ADDRESS_TYPE ADDRESS_TYPE
, PA.DATE_FROM DATE_FROM
, PA.DATE_TO DATE_TO
, PA.ADDRESS_LINE1 ADDRESS_LINE1
, PA.ADDRESS_LINE2 ADDRESS_LINE2
, PA.ADDRESS_LINE3 ADDRESS_LINE3
, PA.TOWN_OR_CITY CITY
, PA.REGION_2 STATE
, PA.POSTAL_CODE ZIP_CODE
, PA.COUNTRY COUNTRY
, GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PA.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PA.DATE_FROM
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PA.DATE_TO
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
FROM HR_ADP_EMP_REF_V REF
, PER_ALL_ASSIGNMENTS_F PASG
, PER_ADDRESSES PA
, HR_ALL_ORGANIZATION_UNITS HOU
, PAY_PAYROLLS_X PPR
, PER_TIME_PERIOD_TYPES PTP
, HR_SOFT_CODING_KEYFLEX HS
, PER_ALL_PEOPLE_F PP
WHERE PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PASG.PERSON_ID = PP.PERSON_ID
AND PASG.PRIMARY_FLAG = 'Y'
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PA.PERSON_ID = PP.PERSON_ID
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND PA.STYLE IN ('US'
, 'US_GLB')
AND PPR.PERIOD_TYPE = PTP.PERIOD_TYPE
AND PPR.PAYROLL_ID = PASG.PAYROLL_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE
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))

Columns