View: HR_CERIDIAN_SECONDARY_STATE_V

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

View Text

SELECT PP.EMPLOYEE_NUMBER
, PP.LAST_NAME
, PP.FIRST_NAME
, PP.MIDDLE_NAMES
, 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)
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, GREATEST(NVL(HL.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(SIT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(LIT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PJ.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))) LAST_UPDATE_DATE
FROM PER_ALL_PEOPLE_F PP
, HR_ALL_ORGANIZATION_UNITS HO
, HR_SOFT_CODING_KEYFLEX HS
, PAY_US_EMP_STATE_TAX_RULES_F SIT
, PAY_US_EMP_CITY_TAX_RULES_F LIT
, HR_LOCATIONS_ALL HL
, PER_ALL_ASSIGNMENTS_F PJ
, 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 HS.SEGMENT1 = HO.ORGANIZATION_ID
AND PP.PERSON_ID = PJ.PERSON_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 CN2.CITY_NAME = NVL(HL.LOC_INFORMATION18
, HL.TOWN_OR_CITY)
AND CN2.CITY_CODE = CN.CITY_CODE
AND CN2.COUNTY_CODE = CN.COUNTY_CODE
AND CN2.STATE_CODE = CN.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 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 HL.LOCATION_ID = NVL(HS.SEGMENT18
, PJ.LOCATION_ID)
AND PJ.PRIMARY_FLAG = 'Y'
AND PP.EMPLOYEE_NUMBER IS NOT NULL
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(SYSDATE)
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(SYSDATE)
AND LIT2.EMP_CITY_TAX_RULE_ID = LIT.EMP_CITY_TAX_RULE_ID))
AND TRUNC (SYSDATE) 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(SYSDATE)
AND A2.ASSIGNMENT_TYPE = 'E'
AND A2.PERSON_ID = PJ.PERSON_ID
AND A2.PRIMARY_FLAG = 'Y'))

Columns