eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: HR_ADP_EMPLOYEE_V

Product:	PER - Human Resources
Description:	ADP payroll interface view
Implementation/DBA Data:	APPS.HR_ADP_EMPLOYEE_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
, DECODE (PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, 0
, PP.FIRST_NAME
, PP.LAST_NAME
, PP.MIDDLE_NAMES
, PP.SUFFIX
, PP.SEX
, PA.TELEPHONE_NUMBER_1
, PP.NATIONAL_IDENTIFIER
, PA.REGION_2
, PA.POSTAL_CODE
, PASG.SOURCE_TYPE
, DECODE (AST.PER_SYSTEM_STATUS
, 'TERM_ASSIGN'
, 'T'
, 'ACTIVE_ASSIGN'
, DECODE (DECODE (LEAST (PPS.FINAL_PROCESS_DATE + 1
, TRUNC (HR_ADP.GET_ADP_EXTRACT_DATE))
, PPS.FINAL_PROCESS_DATE + 1
, 'Y'
, 'N1')
, DECODE (NVL (TO_CHAR (PPS.ACTUAL_TERMINATION_DATE)
, 'ATP_NULL')
, NVL (TO_CHAR (PPS.FINAL_PROCESS_DATE)
, 'FPD_NULL')
, 'Y'
, 'N2')
, 'T'
, 'A')
, 'SUSP_ASSIGN'
, DECODE (DECODE (LEAST (PPS.FINAL_PROCESS_DATE + 1
, TRUNC (HR_ADP.GET_ADP_EXTRACT_DATE))
, PPS.FINAL_PROCESS_DATE + 1
, 'Y'
, 'N1')
, DECODE (NVL (TO_CHAR (PPS.ACTUAL_TERMINATION_DATE)
, 'ATP_NULL')
, NVL (TO_CHAR (PPS.FINAL_PROCESS_DATE)
, 'FPD_NULL')
, 'Y'
, 'N2')
, 'T'
, 'S')
, 'O') ASSIGNMENT_STATUS
, PPS.LEAVING_REASON
, PASG.EMPLOYMENT_CATEGORY
, HOU2.NAME
, JBT.NAME
, HS.SEGMENT5
, PPG.SEGMENT1
, PP.DATE_OF_BIRTH
, PP.DATE_OF_DEATH
, NVL(PP.ORIGINAL_DATE_OF_HIRE
, PPS2.DATE_START)
, DECODE(NVL(PP.ORIGINAL_DATE_OF_HIRE
, PPS2.DATE_START)
, PPS.DATE_START
, NULL
, PPS.DATE_START)
, PPS.ACTUAL_TERMINATION_DATE
, PP.MARITAL_STATUS
, PA.ADDRESS_LINE1
, PA.ADDRESS_LINE2
, PA.TOWN_OR_CITY
, PP.EXPENSE_CHECK_SEND_TO_ADDRESS
, DECODE(PP.DATE_OF_DEATH
, NULL
, 'N'
, 'Y')
, PP.PER_INFORMATION1
, SPO.SPOUSE_DATE_OF_BIRTH
, PPS.FINAL_PROCESS_DATE
, PPS.LAST_STANDARD_PROCESS_DATE
, GREATEST (NVL (PP.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST (NVL (PA.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PA.DATE_FROM)
, GREATEST (NVL (PASG.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE)
, GREATEST( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, NVL(PPS.FINAL_PROCESS_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST( NVL(PPS.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PPS.FINAL_PROCESS_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, NVL(PPS.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, PPS.DATE_START)
, NVL (AST.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, GREATEST ( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, NVL(PPS.FINAL_PROCESS_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST( NVL(PPS.FINAL_PROCESS_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PP.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE
, NVL(PA.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PA.DATE_FROM
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, NVL(PPS.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(PA.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PA.DATE_FROM
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, NVL(PPS.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))))
, PPS.DATE_START)
FROM HR_ADP_SPOUSE_V SPO
, HR_ADP_EMP_REF_V REF
, PER_JOBS_TL JBT
, PAY_PEOPLE_GROUPS PPG
, PER_ASSIGNMENT_STATUS_TYPES AST
, PER_PERIODS_OF_SERVICE PPS
, PER_PERIODS_OF_SERVICE PPS2
, PER_ADDRESSES PA
, PER_ALL_PEOPLE_F PP
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_ALL_ORGANIZATION_UNITS HOU2
, HR_SOFT_CODING_KEYFLEX HS
, PER_TIME_PERIOD_TYPES PTP
, PER_ALL_ASSIGNMENTS_F PASG
WHERE REF.PERIOD_TYPE = PTP.PERIOD_TYPE
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PPS.PERIOD_OF_SERVICE_ID = PASG.PERIOD_OF_SERVICE_ID
AND PPS.PERSON_ID = PP.PERSON_ID
AND PPS2.PERSON_ID = PP.PERSON_ID
AND PPS2.DATE_START = (SELECT MIN(PPS3.DATE_START)
FROM PER_PERIODS_OF_SERVICE PPS3
WHERE PPS3.PERSON_ID = PPS2.PERSON_ID)
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PASG.ROWID = REF.ASG_ROWID
AND PP.ROWID = REF.PER_ROWID
AND PASG.PEOPLE_GROUP_ID = PPG.PEOPLE_GROUP_ID (+)
AND PASG.ORGANIZATION_ID = HOU2.ORGANIZATION_ID
AND PASG.PERSON_ID = PP.PERSON_ID
AND PASG.JOB_ID = JBT.JOB_ID (+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND PASG.PRIMARY_FLAG = 'Y'
AND PASG.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND PP.PERSON_ID = SPO.PERSON_ID (+)
AND PP.PERSON_ID = PA.PERSON_ID
AND PA.STYLE IN ('US'
, 'US_GLB')
AND PA.PRIMARY_FLAG = 'Y'
AND ((TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) >= PA.DATE_FROM)
AND (TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) <= PA.DATE_TO OR PA.DATE_TO IS NULL))

Columns

Name
COMPANY_CODE_EQUIVALENT
GRE_NAME
GRE_ID
ORGANIZATION_ID
EMPLOYEE_NUMBER
PERSON_ID
BUSINESS_GROUP_ID
PAY_FREQUENCY
ASSIGNMENT_SEQUENCE
FIRST_NAME
LAST_NAME
MIDDLE_NAMES
SUFFIX
SEX
PHONE_NUMBER
SOCIAL_SECURITY_NUMBER
STATE
ZIP_CODE
HIRING_SOURCE_TYPE
ASSIGNMENT_STATUS
LEAVING_REASON
EMPLOYMENT_CATEGORY
HOME_DEPT
HOME_JOB
SHIFT
UNION_CODE
BIRTH_DATE
DATE_OF_DEATH
HIRE_DATE
REHIRE_DATE
ACTUAL_TERMINATION_DATE
MARITAL_STATUS
ADDRESS_LINE1
ADDRESS_LINE2
CITY
HOME_OR_OFFICE_FLAG
DECEASED_FLAG
EEO_ETHNIC_CODE
SPOUSE_BIRTH_DATE
FINAL_PROCESS_DATE
LAST_STANDARD_PROCESS_DATE
PER_LAST_UPDATE_DATE
ADR_LAST_UPDATE_DATE
ASG_LAST_UPDATE_DATE
POS_LAST_UPDATE_DATE
AST_LAST_UPDATE_DATE
GREATEST_LAST_UPDATE_DATE