View: HR_ADP_STATE_TAX_V

Product:	PER - Human Resources
Description:	ADP payroll interface view
Implementation/DBA Data:	APPS.HR_ADP_STATE_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
, SIT.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, SIT.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, 'Y' RESIDENT_FLAG
, 'N' WORK_FLAG
, PUST.STATE_ABBREV STATE_ABBREV
, HRL.MEANING FILING_STATUS
, SIT.FILING_STATUS_CODE FILING_STATUS_CODE
, SIT.WITHHOLDING_ALLOWANCES ALLOWANCES
, SIT.SECONDARY_WA SECONDARY_ALLOWANCES
, TO_NUMBER(SIT.SIT_ADDITIONAL_TAX) ADDITIONAL_TAX
, SIT.SIT_EXEMPT SIT_EXEMPT_FLAG
, SIT.SUI_EXEMPT SUI_EXEMPT_FLAG
, SIT.SDI_EXEMPT SDI_EXEMPT_FLAG
, DECODE(SIT.WC_EXEMPT
, 'Y'
, 'Y'
, 'N') WC_EXEMPT_FLAG
, SIT.STATE_NON_RESIDENT_CERT STATE_NON_RESIDENT_CERT
, SIT.SUI_WAGE_BASE_OVERRIDE_AMOUNT SUI_WAGE_BASE_OVERRIDE_AMOUNT
, TO_NUMBER(SIT.SIT_OVERRIDE_RATE) OVERRIDE_RATE
, TO_NUMBER(SIT.SIT_OVERRIDE_AMOUNT) OVERRIDE_AMOUNT
, TO_NUMBER(SIT.SUPP_TAX_OVERRIDE_RATE) SUPP_OVERRIDE_RATE
, SIT.ADDITIONAL_WA_AMOUNT EXEMPTION_AMOUNT
, SIT.SIT_OPTIONAL_CALC_IND OPTIONAL_CALCULATION
, NVL(PSTX.SIT_EXISTS
, 'N') SIT_EXISTS
, NVL(SIT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')) GREATEST_LAST_UPDATE_DATE
FROM HR_ADP_EMP_REF_V REF
, PER_ALL_PEOPLE_F PP
, PER_ADDRESSES PA
, PER_PERIODS_OF_SERVICE PPS
, PER_TIME_PERIOD_TYPES PTP
, PAY_PAYROLLS_X PPR
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, PAY_US_STATES PUST
, HR_LOOKUPS HRL
, PAY_US_EMP_STATE_TAX_RULES_F SIT
, PER_ALL_ASSIGNMENTS_F PASG
, PAY_US_STATE_TAX_INFO_F PSTX
, PAY_STATE_RULES PSR
WHERE PP.EMPLOYEE_NUMBER IS NOT NULL
AND PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PPS.PERSON_ID = PASG.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID= PASG.PERIOD_OF_SERVICE_ID
AND PP.PERSON_ID = PA.PERSON_ID
AND PA.STYLE = 'US'
AND PA.PRIMARY_FLAG = 'Y'
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 PASG.PERSON_ID = PP.PERSON_ID
AND PASG.ASSIGNMENT_ID = SIT.ASSIGNMENT_ID
AND PASG.ASSIGNMENT_TYPE = 'E'
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 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 SIT.EFFECTIVE_START_DATE = (SELECT MAX(SIT2.EFFECTIVE_START_DATE)
FROM PAY_US_EMP_STATE_TAX_RULES_F SIT2
WHERE (SIT2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND SIT2.EMP_STATE_TAX_RULE_ID = SIT.EMP_STATE_TAX_RULE_ID))
AND TO_NUMBER(SIT.FILING_STATUS_CODE) = TO_NUMBER(HRL.LOOKUP_CODE)
AND PSR.FS_LOOKUP_TYPE = HRL.LOOKUP_TYPE
AND PSR.STATE_CODE = PUST.STATE_ABBREV
AND SIT.STATE_CODE = PUST.STATE_CODE
AND NVL(PA.ADD_INFORMATION17
, PA.REGION_2) = PUST.STATE_ABBREV
AND SIT.STATE_CODE = PSTX.STATE_CODE (+)
AND HR_ADP.GET_ADP_EXTRACT_DATE BETWEEN PSTX.EFFECTIVE_START_DATE (+)
AND PSTX.EFFECTIVE_END_DATE (+)
AND DECODE(PUST.STATE_ABBREV
, NVL(PA.ADD_INFORMATION17
, PA.REGION_2)
, 'Y'
, 'N') = 'Y' UNION 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
, SIT.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, SIT.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, 'N' RESIDENT_FLAG
, 'Y' WORK_FLAG
, PUST.STATE_ABBREV STATE_ABBREV
, HRL.MEANING FILING_STATUS
, SIT.FILING_STATUS_CODE FILING_STATUS_CODE
, SIT.WITHHOLDING_ALLOWANCES ALLOWANCES
, SIT.SECONDARY_WA SECONDARY_ALLOWANCES
, TO_NUMBER(SIT.SIT_ADDITIONAL_TAX) ADDITIONAL_TAX
, SIT.SIT_EXEMPT SIT_EXEMPT_FLAG
, SIT.SUI_EXEMPT SUI_EXEMPT_FLAG
, SIT.SDI_EXEMPT SDI_EXEMPT_FLAG
, DECODE(SIT.WC_EXEMPT
, 'Y'
, 'Y'
, 'N') WC_EXEMPT_FLAG
, SIT.STATE_NON_RESIDENT_CERT STATE_NON_RESIDENT_CERT
, SIT.SUI_WAGE_BASE_OVERRIDE_AMOUNT SUI_WAGE_BASE_OVERRIDE_AMOUNT
, TO_NUMBER(SIT.SIT_OVERRIDE_RATE) OVERRIDE_RATE
, TO_NUMBER(SIT.SIT_OVERRIDE_AMOUNT) OVERRIDE_AMOUNT
, TO_NUMBER(SIT.SUPP_TAX_OVERRIDE_RATE) SUPP_OVERRIDE_RATE
, SIT.ADDITIONAL_WA_AMOUNT EXEMPTION_AMOUNT
, SIT.SIT_OPTIONAL_CALC_IND OPTIONAL_CALCULATION
, NVL(PSTX.SIT_EXISTS
, 'N') SIT_EXISTS
, NVL(SIT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')) GREATEST_LAST_UPDATE_DATE
FROM HR_ADP_EMP_REF_V REF
, PER_ALL_PEOPLE_F PP
, PER_PERIODS_OF_SERVICE PPS
, PER_TIME_PERIOD_TYPES PTP
, PAY_PAYROLLS_X PPR
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_LOCATIONS_ALL HL
, HR_SOFT_CODING_KEYFLEX HS
, PAY_US_STATES PUST
, HR_LOOKUPS HRL
, PAY_US_EMP_STATE_TAX_RULES_F SIT
, PER_ALL_ASSIGNMENTS_F PASG
, PAY_US_STATE_TAX_INFO_F PSTX
, PAY_STATE_RULES PSR
WHERE PP.EMPLOYEE_NUMBER IS NOT NULL
AND PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PPS.PERSON_ID = PASG.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID= PASG.PERIOD_OF_SERVICE_ID
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 PASG.PERSON_ID = PP.PERSON_ID
AND PASG.ASSIGNMENT_ID = SIT.ASSIGNMENT_ID
AND PASG.ASSIGNMENT_TYPE = 'E'
AND HL.LOCATION_ID = NVL(HS.SEGMENT18
, PASG.LOCATION_ID)
AND HL.STYLE = 'US'
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))
AND SIT.EFFECTIVE_START_DATE = (SELECT MAX(SIT2.EFFECTIVE_START_DATE)
FROM PAY_US_EMP_STATE_TAX_RULES_F SIT2
WHERE (SIT2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND SIT2.EMP_STATE_TAX_RULE_ID = SIT.EMP_STATE_TAX_RULE_ID))
AND TO_NUMBER(SIT.FILING_STATUS_CODE) = TO_NUMBER(HRL.LOOKUP_CODE)
AND PSR.FS_LOOKUP_TYPE = HRL.LOOKUP_TYPE
AND PSR.STATE_CODE = PUST.STATE_ABBREV
AND SIT.STATE_CODE = PUST.STATE_CODE
AND SIT.STATE_CODE = PSTX.STATE_CODE (+)
AND HR_ADP.GET_ADP_EXTRACT_DATE BETWEEN PSTX.EFFECTIVE_START_DATE (+)
AND PSTX.EFFECTIVE_END_DATE (+)
AND NVL(HL.LOC_INFORMATION17
, HL.REGION_2) = PUST.STATE_ABBREV
AND DECODE(PUST.STATE_ABBREV
, NVL(HL.LOC_INFORMATION17
, HL.REGION_2)
, 'Y'
, 'N') = 'Y'

Columns