View: HR_CERIDIAN_500_WORK_TAX_V

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

View Text

SELECT PP.EMPLOYEE_NUMBER
, GREATEST(LIT.EFFECTIVE_START_DATE
, SIT.EFFECTIVE_START_DATE)
, DECODE(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, SIT.EFFECTIVE_END_DATE)
, SIT.EFFECTIVE_END_DATE
, TO_DATE(NULL))
, PJ.ASSIGNMENT_NUMBER
, PJ.PRIMARY_FLAG
, PP.NATIONAL_IDENTIFIER
, HO.NAME
, NVL(HL.LOC_INFORMATION17
, HL.REGION_2)
, LIT.JURISDICTION_CODE
, CN.CITY_NAME
, CN.CITY_NAME
, 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)
, PJ.ASSIGNMENT_ID
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, GREATEST( 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_ALL_PEOPLE_F PP
, HR_ALL_ORGANIZATION_UNITS HO
, HR_SOFT_CODING_KEYFLEX HS
, PER_ALL_ASSIGNMENTS_F PJ
, PER_TIME_PERIOD_TYPES PTP
, PAY_PAYROLLS_X PPR
, PAY_US_EMP_STATE_TAX_RULES_F SIT
, PAY_US_EMP_CITY_TAX_RULES_F LIT
, PER_PERIODS_OF_SERVICE PPS
, HR_LOCATIONS_ALL HL
, PAY_US_CITY_NAMES CN
, PAY_US_CITY_NAMES CN2
, PAY_US_COUNTIES C
, PAY_US_STATES STA
WHERE HS.SOFT_CODING_KEYFLEX_ID = PJ.SOFT_CODING_KEYFLEX_ID
AND HL.STYLE = 'US'
AND PJ.ASSIGNMENT_TYPE = 'E'
AND PPS.PERSON_ID = PP.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID = PJ.PERIOD_OF_SERVICE_ID
AND HS.SEGMENT1 = HO.ORGANIZATION_ID AND PP.PERSON_ID = PJ.PERSON_ID
AND PTP.PERIOD_TYPE = PPR.PERIOD_TYPE
AND PPR.PAYROLL_ID = PJ.PAYROLL_ID AND SIT.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND SIT.STATE_CODE = STA.STATE_CODE
AND STA.STATE_ABBREV = NVL(HL.LOC_INFORMATION17
, HL.REGION_2)
AND LIT.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND C.COUNTY_NAME = NVL(HL.LOC_INFORMATION19
, HL.REGION_1) AND HL.LOCATION_ID = NVL(HS.SEGMENT18
, PJ.LOCATION_ID) AND PJ.PRIMARY_FLAG = 'Y'
AND PP.EMPLOYEE_NUMBER IS NOT NULL
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(HL.LOC_INFORMATION18
, HL.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 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 TRUNC (HR_CERIDIAN.GET_CER_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
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 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