View: HR_ADP_FED_TAX_V

Product:	PER - Human Resources
Description:	ADP payroll interface view
Implementation/DBA Data:	APPS.HR_ADP_FED_TAX_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)
, PASG.ASSIGNMENT_ID ASSIGNMENT_ID
, PASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, DECODE(PASG.PRIMARY_FLAG
, 'Y'
, 0
, PASG.ASSIGNMENT_SEQUENCE) ASSIGNMENT_SEQUENCE
, PASG.PRIMARY_FLAG PRIMARY_FLAG
, PFT.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, PFT.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, HRL.MEANING FILING_STATUS
, PFT.FILING_STATUS_CODE FILING_STATUS_CODE
, TO_NUMBER(PFT.WITHHOLDING_ALLOWANCES) ALLOWANCES
, TO_NUMBER(PFT.FIT_ADDITIONAL_TAX) ADDITIONAL_TAX
, HRL2.MEANING EIC_FILING_STATUS
, PFT.FIT_EXEMPT FIT_EXEMPT_FLAG
, PFT.MEDICARE_TAX_EXEMPT MEDICARE_EXEMPT_FLAG
, PFT.FUTA_TAX_EXEMPT FUTA_EXEMPT_FLAG
, PFT.SS_TAX_EXEMPT SS_EXEMPT_FLAG
, TO_NUMBER(PFT.FIT_OVERRIDE_RATE) OVERRIDE_RATE
, TO_NUMBER(PFT.FIT_OVERRIDE_AMOUNT) OVERRIDE_AMOUNT
, TO_NUMBER(PFT.SUPP_TAX_OVERRIDE_RATE) SUPP_OVERRIDE_RATE
, PFT.STATUTORY_EMPLOYEE STATUTORY_EMPLOYEE_FLAG
, PFT.CUMULATIVE_TAXATION CUMULATIVE_TAXATION_FLAG
, NVL(PFT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')) GREATEST_LAST_UPDATE_DATE
FROM HR_ADP_EMP_REF_V REF
, HR_LOCATIONS_ALL HL
, PER_ADDRESSES PA
, PER_ALL_PEOPLE_F PP
, PER_PERIODS_OF_SERVICE PPS
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, HR_LOOKUPS HRL
, HR_LOOKUPS HRL2
, PER_TIME_PERIOD_TYPES PTP
, PAY_PAYROLLS_X PPR
, PAY_US_EMP_FED_TAX_RULES_F PFT
, PER_ALL_ASSIGNMENTS_F PASG
WHERE PP.EMPLOYEE_NUMBER IS NOT NULL
AND PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PP.PERSON_ID = PA.PERSON_ID
AND PA.STYLE = 'US'
AND PA.PRIMARY_FLAG = 'Y'
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PA.DATE_FROM
AND NVL(PA.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PPS.PERSON_ID = PASG.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID= PASG.PERIOD_OF_SERVICE_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PASG.PERSON_ID = PP.PERSON_ID
AND PASG.LOCATION_ID = HL.LOCATION_ID
AND PPR.PERIOD_TYPE = PTP.PERIOD_TYPE
AND PPR.PAYROLL_ID = PASG.PAYROLL_ID
AND PASG.ASSIGNMENT_ID = PFT.ASSIGNMENT_ID
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 PFT.EFFECTIVE_START_DATE = (SELECT MAX(PFT2.EFFECTIVE_START_DATE)
FROM PAY_US_EMP_FED_TAX_RULES_F PFT2
WHERE (PFT2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PFT2.EMP_FED_TAX_RULE_ID = PFT.EMP_FED_TAX_RULE_ID))
AND PFT.FILING_STATUS_CODE = HRL.LOOKUP_CODE AND HRL.LOOKUP_TYPE = 'US_FIT_FILING_STATUS'
AND PFT.EIC_FILING_STATUS_CODE = HRL2.LOOKUP_CODE
AND HRL2.LOOKUP_TYPE = 'US_EIC_FILING_STATUS'

Columns