View: HR_CERIDIAN_500_RESIDENT_TAX_V

Product:	PER - Human Resources
Description:	Ceridian Source 500 payroll interface view
Implementation/DBA Data:	APPS.HR_CERIDIAN_500_RESIDENT_TAX_V

View Text

SELECT PP.EMPLOYEE_NUMBER
, GREATEST(LIT.EFFECTIVE_START_DATE
, SIT.EFFECTIVE_START_DATE
, PFT.EFFECTIVE_START_DATE)
, DECODE(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, PFT.EFFECTIVE_END_DATE)
, PFT.EFFECTIVE_END_DATE
, TO_DATE(NULL))
, PJ.ASSIGNMENT_NUMBER
, PJ.PRIMARY_FLAG
, PP.NATIONAL_IDENTIFIER
, HOU.NAME
, DECODE (PFT.FILING_STATUS_CODE
, '01'
, 'S'
, '02'
, 'M'
, '03'
, 'S'
, '04'
, 'S'
, PFT.FILING_STATUS_CODE)
, HR_CERIDIAN.FICA_FUTA_EXEMPT(PFT.MEDICARE_TAX_EXEMPT
, PFT.SS_TAX_EXEMPT
, PFT.FUTA_TAX_EXEMPT)
, NVL(PFT.FIT_EXEMPT
, 'N')
, PFT.WITHHOLDING_ALLOWANCES
, DECODE(PFT.FIT_OVERRIDE_AMOUNT
, 0
, NULL
, PFT.FIT_OVERRIDE_AMOUNT)|| DECODE(PFT.FIT_OVERRIDE_RATE
, 0
, NULL
, NULL
, NULL
, '%')|| DECODE(PFT.FIT_OVERRIDE_RATE
, 0
, NULL
, PFT.FIT_OVERRIDE_RATE)|| DECODE(PFT.FIT_ADDITIONAL_TAX
, 0
, NULL
, NULL
, NULL
, '$')|| DECODE(PFT.FIT_ADDITIONAL_TAX
, 0
, NULL
, PFT.FIT_ADDITIONAL_TAX)
, NVL(SIT.SIT_EXEMPT
, 'N')
, SIT.WITHHOLDING_ALLOWANCES
, DECODE(SIT.SIT_OVERRIDE_AMOUNT
, 0
, NULL
, SIT.SIT_OVERRIDE_AMOUNT)|| DECODE(SIT.SIT_OVERRIDE_RATE
, 0
, NULL
, NULL
, NULL
, '%')|| DECODE(SIT.SIT_OVERRIDE_RATE
, 0
, NULL
, SIT.SIT_OVERRIDE_RATE)|| DECODE(SIT.SIT_ADDITIONAL_TAX
, 0
, NULL
, NULL
, NULL
, '$')|| DECODE(SIT.SIT_ADDITIONAL_TAX
, 0
, NULL
, SIT.SIT_ADDITIONAL_TAX)
, NVL(LIT.LIT_EXEMPT
, 'N')
, DECODE(LIT.LIT_OVERRIDE_AMOUNT
, 0
, NULL
, LIT.LIT_OVERRIDE_AMOUNT)|| DECODE(LIT.LIT_OVERRIDE_RATE
, 0
, NULL
, NULL
, NULL
, '%')|| DECODE(LIT.LIT_OVERRIDE_RATE
, 0
, NULL
, LIT.LIT_OVERRIDE_RATE)|| DECODE(LIT.LIT_ADDITIONAL_TAX
, 0
, NULL
, NULL
, NULL
, '$')|| DECODE(LIT.LIT_ADDITIONAL_TAX
, 0
, NULL
, LIT.LIT_ADDITIONAL_TAX)
, NVL(PA.ADD_INFORMATION17
, PA.REGION_2)
, LIT.JURISDICTION_CODE
, CN.CITY_NAME
, CN2.CITY_NAME
, PJ.ASSIGNMENT_ID
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, GREATEST( DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, PFT.EFFECTIVE_END_DATE)
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(PFT.EFFECTIVE_END_DATE
, NVL(PFT.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PFT.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, PFT.EFFECTIVE_START_DATE
, DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, LIT.EFFECTIVE_END_DATE)
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(LIT.EFFECTIVE_END_DATE
, NVL(LIT.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(LIT.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, LIT.EFFECTIVE_START_DATE
, DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, SIT.EFFECTIVE_END_DATE)
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(SIT.EFFECTIVE_END_DATE
, NVL(SIT.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(SIT.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, SIT.EFFECTIVE_START_DATE) LAST_UPDATE_DATE
FROM PER_ADDRESSES PA
, PER_ALL_PEOPLE_F PP
, PAY_US_STATES PUST
, PAY_US_COUNTIES C
, PAY_US_CITY_NAMES CN
, PAY_US_CITY_NAMES CN2
, PAY_US_EMP_FED_TAX_RULES_F PFT
, PAY_US_EMP_STATE_TAX_RULES_F SIT
, PAY_US_EMP_CITY_TAX_RULES_F LIT
, HR_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, PER_ALL_ASSIGNMENTS_F PJ
, PER_PERIODS_OF_SERVICE PPS
WHERE PP.PERSON_ID = PA.PERSON_ID
AND PJ.ASSIGNMENT_TYPE = 'E'
AND PPS.PERSON_ID = PP.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID = PJ.PERIOD_OF_SERVICE_ID
AND PFT.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PJ.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND C.COUNTY_NAME = NVL(PA.ADD_INFORMATION19
, PA.REGION_1)
AND SUBSTR(LIT.JURISDICTION_CODE
, 4
, 3) = C.COUNTY_CODE
AND SUBSTR(LIT.JURISDICTION_CODE
, 1
, 2) = C.STATE_CODE
AND SUBSTR(LIT.JURISDICTION_CODE
, 8
, 4) <> '0000'
AND CN2.CITY_NAME = NVL(PA.ADD_INFORMATION18
, PA.TOWN_OR_CITY)
AND CN.CITY_CODE = CN2.CITY_CODE
AND CN.COUNTY_CODE = CN2.COUNTY_CODE
AND CN.STATE_CODE = CN2.STATE_CODE
AND CN.STATE_CODE = LIT.STATE_CODE
AND CN.COUNTY_CODE = LIT.COUNTY_CODE
AND CN.CITY_CODE = LIT.CITY_CODE
AND ((CN.PRIMARY_FLAG = 'Y') OR (CN.PRIMARY_FLAG = 'N'
AND CN.CITY_CODE LIKE 'U%'))
AND PA.STYLE = 'US'
AND PJ.PRIMARY_FLAG = 'Y'
AND PA.PRIMARY_FLAG = 'Y'
AND SIT.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND SIT.STATE_CODE = PUST.STATE_CODE
AND PUST.STATE_ABBREV = NVL(PA.ADD_INFORMATION17
, PA.REGION_2)
AND LIT.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE) BETWEEN PA.DATE_FROM
AND NVL(PA.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND PJ.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND A2.ASSIGNMENT_TYPE = 'E'
AND A2.PERSON_ID = PJ.PERSON_ID
AND A2.PRIMARY_FLAG = 'Y'))
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_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PFT2.EMP_FED_TAX_RULE_ID = PFT.EMP_FED_TAX_RULE_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_CERIDIAN.GET_CER_EXTRACT_DATE)
AND SIT2.EMP_STATE_TAX_RULE_ID = SIT.EMP_STATE_TAX_RULE_ID))
AND LIT.EFFECTIVE_START_DATE = (SELECT MAX(LIT2.EFFECTIVE_START_DATE)
FROM PAY_US_EMP_CITY_TAX_RULES_F LIT2
WHERE (LIT2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND LIT2.EMP_CITY_TAX_RULE_ID = LIT.EMP_CITY_TAX_RULE_ID))
AND PPS.DATE_START = (SELECT MAX(PS.DATE_START)
FROM PER_PERIODS_OF_SERVICE PS
WHERE (PS.DATE_START <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PS.PERSON_ID = PP.PERSON_ID))

Columns