eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: HR_ADP_CITY_TAX_V

Product:	PER - Human Resources
Description:	ADP payroll interface view
Implementation/DBA Data:	APPS.HR_ADP_CITY_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
, LIT.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, LIT.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, 'Y' RESIDENT_FLAG
, 'N' WORK_FLAG
, PUST.STATE_ABBREV STATE_ABBREV
, C.COUNTY_NAME COUNTY_NAME
, CN.CITY_NAME CITY_NAME
, LIT.JURISDICTION_CODE JURISDICTION_CODE
, HRL.MEANING FILING_STATUS
, LIT.FILING_STATUS_CODE FILING_STATUS_CODE
, TO_NUMBER(LIT.WITHHOLDING_ALLOWANCES) ALLOWANCES
, TO_NUMBER(LIT.LIT_ADDITIONAL_TAX) ADDITIONAL_TAX
, TO_NUMBER(LIT.ADDITIONAL_WA_RATE) ADDITIONAL_ALLOWANCE_RATE
, LIT.LIT_EXEMPT LIT_EXEMPT_FLAG
, DECODE(LIT.SD_EXEMPT
, 'Y'
, 'Y'
, 'N') SD_EXEMPT_FLAG
, DECODE(LIT.HT_EXEMPT
, 'Y'
, 'Y'
, 'N') OCC_PRIV_TAX_EXEMPT
, TO_NUMBER(LIT.LIT_OVERRIDE_RATE) OVERRIDE_RATE
, TO_NUMBER(LIT.LIT_OVERRIDE_AMOUNT) OVERRIDE_AMOUNT
, LIT.SCHOOL_DISTRICT_CODE SCHOOL_DISTRICT_CODE
, SCHOOL.SCHOOL_DST_NAME SCHOOL_DISTRICT_NAME
, NVL(PCTX.CITY_TAX
, 'N') LIT_EXISTS
, NVL(LIT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')) GREATEST_LAST_UPDATE_DATE
FROM HR_ADP_EMP_REF_V REF
, PAY_US_CITY_NAMES CN
, PAY_US_CITY_NAMES CN2
, PAY_US_COUNTIES C
, PER_ALL_PEOPLE_F PP
, PER_PERIODS_OF_SERVICE PPS
, HR_LOOKUPS HRL
, PER_ADDRESSES PA
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, PER_TIME_PERIOD_TYPES PTP
, PAY_PAYROLLS_X PPR
, PAY_US_CITY_SCHOOL_DSTS SCHOOL
, PAY_US_STATES PUST
, PAY_US_EMP_CITY_TAX_RULES_F LIT
, PER_ALL_ASSIGNMENTS_F PASG
, PAY_US_CITY_TAX_INFO_F PCTX
WHERE PP.EMPLOYEE_NUMBER IS NOT NULL
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PA.PERSON_ID = PP.PERSON_ID
AND PA.PRIMARY_FLAG = 'Y'
AND PA.STYLE = 'US'
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 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 PASG.PERSON_ID = PP.PERSON_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PASG.ASSIGNMENT_ID = LIT.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 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_ADP.GET_ADP_EXTRACT_DATE)
AND LIT2.EMP_CITY_TAX_RULE_ID = LIT.EMP_CITY_TAX_RULE_ID))
AND LIT.STATE_CODE = C.STATE_CODE
AND LIT.COUNTY_CODE = C.COUNTY_CODE
AND LIT.CITY_CODE <> '0000'
AND LIT.STATE_CODE = PUST.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 NVL(PA.ADD_INFORMATION17
, PA.REGION_2) = PUST.STATE_ABBREV
AND PUST.STATE_CODE = C.STATE_CODE
AND NVL(PA.ADD_INFORMATION19
, PA.REGION_1) = C.COUNTY_NAME
AND PUST.STATE_CODE = CN2.STATE_CODE
AND C.COUNTY_CODE = CN2.COUNTY_CODE
AND NVL(PA.ADD_INFORMATION18
, PA.TOWN_OR_CITY) = CN2.CITY_NAME
AND CN.CITY_CODE = CN2.CITY_CODE
AND CN.COUNTY_CODE = CN2.COUNTY_CODE
AND CN.STATE_CODE = CN2.STATE_CODE
AND LIT.FILING_STATUS_CODE = HRL.LOOKUP_CODE
AND HRL.LOOKUP_TYPE = 'US_LIT_FILING_STATUS'
AND LIT.SCHOOL_DISTRICT_CODE = SCHOOL.SCHOOL_DST_CODE (+)
AND LIT.STATE_CODE = SCHOOL.STATE_CODE (+)
AND LIT.COUNTY_CODE = SCHOOL.COUNTY_CODE (+)
AND LIT.CITY_CODE = SCHOOL.CITY_CODE (+)
AND LIT.JURISDICTION_CODE = PCTX.JURISDICTION_CODE (+)
AND HR_ADP.GET_ADP_EXTRACT_DATE BETWEEN PCTX.EFFECTIVE_START_DATE (+)
AND PCTX.EFFECTIVE_END_DATE (+)
AND DECODE(DECODE (DECODE(CN2.CITY_NAME
, NVL(PA.ADD_INFORMATION18
, PA.TOWN_OR_CITY)
, 'Y'
, 'N1')
, DECODE(C.COUNTY_NAME
, NVL(PA.ADD_INFORMATION19
, PA.REGION_1)
, 'Y'
, 'N2')
, 'Y'
, 'N')
, DECODE(PUST.STATE_ABBREV
, NVL(PA.ADD_INFORMATION17
, PA.REGION_2)
, 'Y'
, 'N3')
, '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
, LIT.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, LIT.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, 'N' RESIDENT_FLAG
, 'Y' WORK_FLAG
, PUST.STATE_ABBREV STATE_ABBREV
, C.COUNTY_NAME COUNTY_NAME
, CN.CITY_NAME CITY_NAME
, LIT.JURISDICTION_CODE JURISDICTION_CODE
, HRL.MEANING FILING_STATUS
, LIT.FILING_STATUS_CODE FILING_STATUS_CODE
, TO_NUMBER(LIT.WITHHOLDING_ALLOWANCES) ALLOWANCES
, TO_NUMBER(LIT.LIT_ADDITIONAL_TAX) ADDITIONAL_TAX
, TO_NUMBER(LIT.ADDITIONAL_WA_RATE) ADDITIONAL_ALLOWANCE_RATE
, LIT.LIT_EXEMPT LIT_EXEMPT_FLAG
, DECODE(LIT.SD_EXEMPT
, 'Y'
, 'Y'
, 'N') SD_EXEMPT_FLAG
, DECODE(LIT.HT_EXEMPT
, 'Y'
, 'Y'
, 'N') OCC_PRIV_TAX_EXEMPT
, TO_NUMBER(LIT.LIT_OVERRIDE_RATE) OVERRIDE_RATE
, TO_NUMBER(LIT.LIT_OVERRIDE_AMOUNT) OVERRIDE_AMOUNT
, LIT.SCHOOL_DISTRICT_CODE SCHOOL_DISTRICT_CODE
, SCHOOL.SCHOOL_DST_NAME SCHOOL_DISTRICT_NAME
, NVL(PCTX.CITY_TAX
, 'N') LIT_EXISTS
, NVL(LIT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')) GREATEST_LAST_UPDATE_DATE
FROM HR_ADP_EMP_REF_V REF
, PAY_US_CITY_NAMES CN
, PAY_US_CITY_NAMES CN2
, PAY_US_COUNTIES C
, PER_ALL_PEOPLE_F PP
, PER_PERIODS_OF_SERVICE PPS
, HR_LOOKUPS HRL
, HR_LOCATIONS_ALL HL
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, PER_TIME_PERIOD_TYPES PTP
, PAY_PAYROLLS_X PPR
, PAY_US_CITY_SCHOOL_DSTS SCHOOL
, PAY_US_STATES PUST
, PAY_US_EMP_CITY_TAX_RULES_F LIT
, PER_ALL_ASSIGNMENTS_F PASG
, PAY_US_CITY_TAX_INFO_F PCTX
WHERE PP.EMPLOYEE_NUMBER IS NOT NULL
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
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 PASG.PERSON_ID = PP.PERSON_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PASG.ASSIGNMENT_ID = LIT.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 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_ADP.GET_ADP_EXTRACT_DATE)
AND LIT2.EMP_CITY_TAX_RULE_ID = LIT.EMP_CITY_TAX_RULE_ID))
AND LIT.STATE_CODE = C.STATE_CODE
AND LIT.COUNTY_CODE = C.COUNTY_CODE
AND LIT.CITY_CODE <> '0000'
AND LIT.STATE_CODE = PUST.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 NVL(HL.LOC_INFORMATION17
, HL.REGION_2) = PUST.STATE_ABBREV
AND PUST.STATE_CODE = C.STATE_CODE
AND NVL(HL.LOC_INFORMATION19
, HL.REGION_1) = C.COUNTY_NAME
AND PUST.STATE_CODE = CN2.STATE_CODE
AND C.COUNTY_CODE = CN2.COUNTY_CODE
AND NVL(HL.LOC_INFORMATION18
, HL.TOWN_OR_CITY) = CN2.CITY_NAME
AND CN.CITY_CODE = CN2.CITY_CODE
AND CN.COUNTY_CODE = CN2.COUNTY_CODE
AND CN.STATE_CODE = CN2.STATE_CODE
AND HL.LOCATION_ID = NVL(HS.SEGMENT18
, PASG.LOCATION_ID)
AND HL.STYLE = 'US'
AND LIT.FILING_STATUS_CODE = HRL.LOOKUP_CODE
AND HRL.LOOKUP_TYPE = 'US_LIT_FILING_STATUS'
AND LIT.SCHOOL_DISTRICT_CODE = SCHOOL.SCHOOL_DST_CODE (+)
AND LIT.STATE_CODE = SCHOOL.STATE_CODE (+)
AND LIT.COUNTY_CODE = SCHOOL.COUNTY_CODE (+)
AND LIT.CITY_CODE = SCHOOL.CITY_CODE (+)
AND LIT.JURISDICTION_CODE = PCTX.JURISDICTION_CODE (+)
AND HR_ADP.GET_ADP_EXTRACT_DATE BETWEEN PCTX.EFFECTIVE_START_DATE (+)
AND PCTX.EFFECTIVE_END_DATE (+)
AND DECODE(DECODE (DECODE(CN2.CITY_NAME
, NVL(HL.LOC_INFORMATION18
, HL.TOWN_OR_CITY)
, 'Y'
, 'N1')
, DECODE(C.COUNTY_NAME
, NVL(HL.LOC_INFORMATION19
, HL.REGION_1)
, 'Y'
, 'N2')
, 'Y'
, 'N')
, DECODE(PUST.STATE_ABBREV
, NVL(HL.LOC_INFORMATION17
, HL.REGION_2)
, 'Y'
, 'N3')
, 'Y'
, 'N') = 'Y'

Columns

Name
COMPANY_CODE_EQUIVALENT
GRE_NAME
GRE_ID
ORGANIZATION_ID
EMPLOYEE_NUMBER
BUSINESS_GROUP_ID
PERSON_ID
PAY_FREQUENCY
ASSIGNMENT_ID
ASSIGNMENT_NUMBER
ASSIGNMENT_SEQUENCE
PRIMARY_FLAG
EFFECTIVE_START_DATE
EFFECTIVE_END_DATE
RESIDENT_FLAG
WORK_FLAG
STATE_ABBREV
COUNTY_NAME
CITY_NAME
JURISDICTION_CODE
FILING_STATUS
FILING_STATUS_CODE
ALLOWANCES
ADDITIONAL_TAX
ADDITIONAL_ALLOWANCE_RATE
LIT_EXEMPT_FLAG
SD_EXEMPT_FLAG
OCC_PRIV_TAX_EXEMPT
OVERRIDE_RATE
OVERRIDE_AMOUNT
SCHOOL_DISTRICT_CODE
SCHOOL_DISTRICT_NAME
LIT_EXISTS
GREATEST_LAST_UPDATE_DATE