View: PAY_US_EMPLOYEE_ACTION_INFO_V

Product:	PAY - Payroll
Description:	This view returns the Employee Details from the live tables for employees which have been archived by External Process Archiver
Implementation/DBA Data:	APPS.PAY_US_EMPLOYEE_ACTION_INFO_V

View Text

SELECT PAA.ASSIGNMENT_ACTION_ID ACTION_NUMBER
, PPA2.EFFECTIVE_DATE
, PPF.PERSON_ID
, PPF.LAST_NAME
, PPF.FIRST_NAME
, PPF.MIDDLE_NAMES
, PPF.EMPLOYEE_NUMBER
, PPF.NATIONAL_IDENTIFIER
, PA.ADDRESS_LINE1||PA.ADDRESS_LINE2||PA.ADDRESS_LINE3 ADDRESS_LINE
, PA.REGION_1 COUNTY
, PA.TOWN_OR_CITY
, PA.REGION_2 STATE
, PA.POSTAL_CODE
, PA.COUNTRY
, PAF.ASSIGNMENT_NUMBER
, PAF.PAYROLL_ID
, PPA.BUSINESS_GROUP_ID
, PAY_MAG_UTILS.GET_PARAMETER('TRANSFER_CONSOLIDATION_SET_ID'
, NULL
, PPA.LEGISLATIVE_PARAMETERS) CONSOLIDATION_SET_ID
, HL.LOCATION_ID
, HL.LOCATION_CODE
, PAA.TAX_UNIT_ID TAX_UNIT_ID
, HOU.NAME GRE_NAME
, PAA.ASSIGNMENT_ID
, PAF.ORGANIZATION_ID
, HOU2.NAME ORG_NAME
, HOI.ORG_INFORMATION1 FED_EIN
, FCL.MEANING ACTION_TYPE
FROM FND_COMMON_LOOKUPS FCL
, PER_ALL_PEOPLE_F PPF
, HR_LOCATIONS_ALL HL
, PER_ADDRESSES PA
, PER_ALL_ASSIGNMENTS_F PAF
, HR_ORGANIZATION_INFORMATION HOI
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_ALL_ORGANIZATION_UNITS HOU2
, PAY_PAYROLL_ACTIONS PPA2
, PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.REPORT_TYPE = 'XFR_INTERFACE'
AND PPA.REPORT_CATEGORY = 'RT'
AND PPA.REPORT_QUALIFIER = 'FED'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA2.ASSIGNMENT_ACTION_ID = SUBSTR(PAA.SERIAL_NUMBER
, 3)
AND PPA2.PAYROLL_ACTION_ID = PAA2.PAYROLL_ACTION_ID
AND PAA.TAX_UNIT_ID = HOU.ORGANIZATION_ID
AND PAA.TAX_UNIT_ID = HOI.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'EMPLOYER IDENTIFICATION'
AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND SUBSTR(PAA.SERIAL_NUMBER
, 1
, 1) = FCL.LOOKUP_CODE
AND LOOKUP_TYPE = 'ACTION_TYPE'
AND PAF.EFFECTIVE_START_DATE = (SELECT MAX(PAF1.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F PAF1
WHERE PAF1.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PAF1.EFFECTIVE_START_DATE <= PPA.EFFECTIVE_DATE)
AND PAF.LOCATION_ID = HL.LOCATION_ID
AND PAF.PERSON_ID = PA.PERSON_ID
AND PA.PRIMARY_FLAG = 'Y'
AND PPA2.EFFECTIVE_DATE BETWEEN PA.DATE_FROM
AND NVL(PA.DATE_TO
, TO_DATE('4712/12/31'
, 'YYYY/MM/DD'))
AND PAF.ORGANIZATION_ID = HOU2.ORGANIZATION_ID
AND PAF.PERSON_ID = PPF.PERSON_ID
AND PPA.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE

Columns