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_ASSIGNMENT_V

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

View Text

SELECT REF.COMPANY_CODE_EQUIVALENT
, HOU.NAME
, HS.SEGMENT1
, PASG.ORGANIZATION_ID
, PP.EMPLOYEE_NUMBER
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, DECODE (PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, PASG.ASSIGNMENT_ID
, PASG.ASSIGNMENT_NUMBER
, PASG.PRIMARY_FLAG
, PASG.EFFECTIVE_START_DATE
, PASG.EFFECTIVE_END_DATE
, DECODE(PASG.PRIMARY_FLAG
, 'Y'
, 0
, PASG.ASSIGNMENT_SEQUENCE)
, PJT.NAME
, PJ.JOB_INFORMATION3
, PJ.JOB_INFORMATION1
, TO_NUMBER (PBEV.SCREEN_ENTRY_VALUE)
, DECODE (PPB.PAY_BASIS
, 'PERIOD'
, 'P'
, 'HOURLY'
, 'H'
, 'MONTHLY'
, 'M'
, 'ANNUAL'
, 'A'
, NULL)
, PASG.NORMAL_HOURS
, PPP.LAST_CHANGE_DATE
, PPP.NEXT_SAL_REVIEW_DATE
, PPP.PROPOSAL_REASON
, PBEE.EFFECTIVE_START_DATE
, HS.SEGMENT10
, HS.SEGMENT11
, PPS.FINAL_PROCESS_DATE
, DECODE (DECODE (LEAST (PASG.EFFECTIVE_END_DATE + 1
, TRUNC (HR_ADP.GET_ADP_EXTRACT_DATE))
, PASG.EFFECTIVE_END_DATE + 1
, 'Y'
, 'NO')
, 'Y'
, 'D'
, NVL (ASTA.PAY_SYSTEM_STATUS
, AST.PAY_SYSTEM_STATUS)) PAYROLL_STATUS
, HOU2.NAME
, PJT.NAME
, PASG.CHANGE_REASON
, HR_GENERAL.DECODE_LOOKUP('EMP_ASSIGN_REASON'
, PASG.CHANGE_REASON)
, HL.LOCATION_CODE
, NVL(HL.LOC_INFORMATION17
, HL.REGION_2)
, 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 (PPP.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PPP.CHANGE_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')))
, GREATEST (NVL (PBEE.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PBEE.EFFECTIVE_START_DATE)
, GREATEST (NVL (PJ.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, NVL (PJ.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST (NVL (AST.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, NVL (ASTA.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST ( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, NVL(REF.FINAL_PROCESS_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST(NVL(REF.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(REF.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(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, NVL(PPP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PPP.CHANGE_DATE
, NVL(PBEE.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PBEE.EFFECTIVE_START_DATE
, NVL(PJ.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PJ.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(AST.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(ASTA.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST( NVL(REF.LAST_UPDATE_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(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, NVL(PPP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PPP.CHANGE_DATE
, NVL(PBEE.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PBEE.EFFECTIVE_START_DATE
, NVL(PJ.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PJ.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(AST.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(ASTA.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))))
, REF.DATE_START) GREATEST_LAST_UPDATE_DATE
FROM HR_ADP_EMP_REF_V REF
, PER_ASS_STATUS_TYPE_AMENDS ASTA
, PER_ASSIGNMENT_STATUS_TYPES AST
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_ALL_ORGANIZATION_UNITS HOU2
, HR_SOFT_CODING_KEYFLEX HS
, PER_TIME_PERIOD_TYPES PTP
, PER_PAY_PROPOSALS PPP
, PER_PERIODS_OF_SERVICE PPS
, PER_JOBS_TL PJT
, PER_JOBS PJ
, HR_LOCATIONS_ALL HL
, PER_ALL_PEOPLE_F PP
, PER_PAY_BASES PPB
, PER_ALL_ASSIGNMENTS_F PASG
, PAY_ELEMENT_ENTRY_VALUES_F PBEV
, PAY_ELEMENT_ENTRIES_F PBEE
, PAY_INPUT_VALUES_F PPIV
, PAY_ELEMENT_LINKS_F PBEL
, PAY_ELEMENT_TYPES_F PBET
WHERE TRUNC(SYSDATE) BETWEEN PBEL.EFFECTIVE_START_DATE
AND PBEL.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PBET.EFFECTIVE_START_DATE
AND PBET.EFFECTIVE_END_DATE
AND PASG.PERSON_ID = PP.PERSON_ID
AND PASG.ROWID = REF.ASG_ROWID
AND PP.ROWID = REF.PER_ROWID
AND NVL(HS.SEGMENT18
, PASG.LOCATION_ID) = HL.LOCATION_ID
AND HL.STYLE IN ( 'US'
, 'US_GLB')
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PASG.PERSON_ID = PPS.PERSON_ID
AND PASG.JOB_ID = PJ.JOB_ID
AND PJT.JOB_ID = PJ.JOB_ID
AND PJT.LANGUAGE = USERENV('LANG')
AND PPS.PERIOD_OF_SERVICE_ID = PASG.PERIOD_OF_SERVICE_ID
AND REF.PERIOD_TYPE = PTP.PERIOD_TYPE
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PASG.ORGANIZATION_ID = HOU2.ORGANIZATION_ID
AND PBEV.EFFECTIVE_START_DATE = (SELECT MAX (PBEV2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PBEV2
WHERE PBEV2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PBEV2.ELEMENT_ENTRY_ID = PBEV.ELEMENT_ENTRY_ID)
AND PASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND PPB.INPUT_VALUE_ID = PBEV.INPUT_VALUE_ID + 0
AND PPB.INPUT_VALUE_ID = PPIV.INPUT_VALUE_ID
AND PPIV.ELEMENT_TYPE_ID = PBET.ELEMENT_TYPE_ID
AND PBEL.ELEMENT_TYPE_ID = PBET.ELEMENT_TYPE_ID
AND PBEE.ELEMENT_LINK_ID = PBEL.ELEMENT_LINK_ID
AND PASG.ASSIGNMENT_ID = PBEE.ASSIGNMENT_ID
AND PBEV.ELEMENT_ENTRY_ID = PBEE.ELEMENT_ENTRY_ID
AND PBEV.EFFECTIVE_START_DATE = PBEE.EFFECTIVE_START_DATE
AND PBEV.EFFECTIVE_END_DATE = PBEE.EFFECTIVE_END_DATE
AND PASG.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND AST.BUSINESS_GROUP_ID = ASTA.BUSINESS_GROUP_ID (+)
AND PPP.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID
AND PPP.CHANGE_DATE = (SELECT MAX(PP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PP2
WHERE PP2.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID
AND PP2.CHANGE_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE))
AND PPIV.EFFECTIVE_START_DATE = (SELECT MAX(PPIV2.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PPIV2
WHERE PPIV2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PPIV2.INPUT_VALUE_ID = PPIV.INPUT_VALUE_ID)
AND PBEE.EFFECTIVE_START_DATE = (SELECT MAX(PBEE2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PBEE2
WHERE PBEE2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PBEE2.ASSIGNMENT_ID = PBEE.ASSIGNMENT_ID
AND PBEE2.ELEMENT_LINK_ID = PBEL.ELEMENT_LINK_ID)

Columns

Name
COMPANY_CODE_EQUIVALENT
GRE_NAME
GRE_ID
ORGANIZATION_ID
EMPLOYEE_NUMBER
BUSINESS_GROUP_ID
PERSON_ID
PAY_FREQUENCY
ASSIGNMENT_ID
ASSIGNMENT_NUMBER
PRIMARY_FLAG
EFFECTIVE_START_DATE
EFFECTIVE_END_DATE
ASSIGNMENT_SEQUENCE
JOB_NAME
FLSA_CODE
EEO_CATEGORY
RATE
PAY_BASIS
NORMAL_HOURS
PREVIOUS_SALARY_CHANGE_DATE
NEXT_SAL_REVIEW_DATE
SALARY_CHANGE_REASON
RATE_EFFECTIVE_DATE
SEASONAL_EMPLOYEE_FLAG
CORPORATE_OFFICER_FLAG
FINAL_PROCESS_DATE
PAYROLL_STATUS
HOME_DEPT
HOME_JOB
CHANGE_REASON_CODE
CHANGE_REASON_MEANING
LOCATION
WORK_STATE
PER_LAST_UPDATE_DATE
ASG_LAST_UPDATE_DATE
PPP_LAST_UPDATE_DATE
POS_LAST_UPDATE_DATE
SAL_LAST_UPDATE_DATE
JOB_LAST_UPDATE_DATE
OTHER_LAST_UPDATE_DATE
GREATEST_LAST_UPDATE_DATE