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.

VIEW: APPS.HR_ADP_CITY_TAX_V

Source

View Text - Preformatted


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 , cn1.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 cn1 , pay_us_city_names cn2 , pay_us_counties c , per_all_people_f pp , per_periods_of_service pps , hr_lookups hrl , per_addresses pad , 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 pad.person_id = pp.person_id and pad.primary_flag = 'Y' and pad.style = 'US' and trunc(hr_adp.get_adp_extract_date) between pad.date_from and nvl(pad.date_to,to_date('31/12/4712', 'DD/MM/YYYY')) and pasg.assignment_id = ref.assignment_id and pasg.rowid = ref.asg_rowid 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 cn1.state_code = lit.state_code and cn1.county_code = lit.county_code and cn1.city_code = lit.city_code and ((cn1.primary_flag = 'Y') or (cn1.primary_flag = 'N' and cn1.city_code like 'U%')) and nvl(pad.add_information17, pad.region_2) = pust.state_abbrev and pust.state_code = c.state_code and nvl(pad.add_information19, pad.region_1) = c.county_name and pust.state_code = cn2.state_code and c.county_code = cn2.county_code and nvl(pad.add_information18, pad.town_or_city) = cn2.city_name and cn1.city_code = cn2.city_code and cn1.county_code = cn2.county_code and cn1.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(pad.add_information18, pad.town_or_city), 'Y', 'N1'), decode(c.county_name, nvl(pad.add_information19, pad.region_1), 'Y', 'N2'),'Y','N'), decode(pust.state_abbrev, nvl(pad.add_information17, pad.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 , cn1.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 cn1 , 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 cn1.state_code = lit.state_code and cn1.county_code = lit.county_code and cn1.city_code = lit.city_code and ((cn1.primary_flag = 'Y') or (cn1.primary_flag = 'N' and cn1.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 cn1.city_code = cn2.city_code and cn1.county_code = cn2.county_code and cn1.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'

View Text - HTML Formatted

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
, CN1.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 CN1
, PAY_US_CITY_NAMES CN2
, PAY_US_COUNTIES C
, PER_ALL_PEOPLE_F PP
, PER_PERIODS_OF_SERVICE PPS
, HR_LOOKUPS HRL
, PER_ADDRESSES PAD
, 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 PAD.PERSON_ID = PP.PERSON_ID
AND PAD.PRIMARY_FLAG = 'Y'
AND PAD.STYLE = 'US'
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PAD.DATE_FROM
AND NVL(PAD.DATE_TO
, TO_DATE('31/12/4712'
, 'DD/MM/YYYY'))
AND PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PASG.ROWID = REF.ASG_ROWID
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 CN1.STATE_CODE = LIT.STATE_CODE
AND CN1.COUNTY_CODE = LIT.COUNTY_CODE
AND CN1.CITY_CODE = LIT.CITY_CODE
AND ((CN1.PRIMARY_FLAG = 'Y') OR (CN1.PRIMARY_FLAG = 'N'
AND CN1.CITY_CODE LIKE 'U%'))
AND NVL(PAD.ADD_INFORMATION17
, PAD.REGION_2) = PUST.STATE_ABBREV
AND PUST.STATE_CODE = C.STATE_CODE
AND NVL(PAD.ADD_INFORMATION19
, PAD.REGION_1) = C.COUNTY_NAME
AND PUST.STATE_CODE = CN2.STATE_CODE
AND C.COUNTY_CODE = CN2.COUNTY_CODE
AND NVL(PAD.ADD_INFORMATION18
, PAD.TOWN_OR_CITY) = CN2.CITY_NAME
AND CN1.CITY_CODE = CN2.CITY_CODE
AND CN1.COUNTY_CODE = CN2.COUNTY_CODE
AND CN1.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(PAD.ADD_INFORMATION18
, PAD.TOWN_OR_CITY)
, 'Y'
, 'N1')
, DECODE(C.COUNTY_NAME
, NVL(PAD.ADD_INFORMATION19
, PAD.REGION_1)
, 'Y'
, 'N2')
, 'Y'
, 'N')
, DECODE(PUST.STATE_ABBREV
, NVL(PAD.ADD_INFORMATION17
, PAD.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
, CN1.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 CN1
, 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 CN1.STATE_CODE = LIT.STATE_CODE
AND CN1.COUNTY_CODE = LIT.COUNTY_CODE
AND CN1.CITY_CODE = LIT.CITY_CODE
AND ((CN1.PRIMARY_FLAG = 'Y') OR (CN1.PRIMARY_FLAG = 'N'
AND CN1.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 CN1.CITY_CODE = CN2.CITY_CODE
AND CN1.COUNTY_CODE = CN2.COUNTY_CODE
AND CN1.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'