Query to find Employee Tax Info State Tax rules

select ppf.employee_number "employee number",

  hrl.lookup_type,

  --pus.state_name "state",

  hrl.meaning "w4 info :filing status",

  str.withholding_allowances "w4 info :allowances",

  str.secondary_wa "w4 info: secon allowances",

  str.additional_wa_amount "w4 info:exemption aaount",

  str.sit_additional_tax "w4 info:addn tax",

  str.sit_optional_calc_ind "w4 info :opt calc",

  str.sit_exempt "tax ex: sit",

  str.sdi_exempt "tax ex: sdi",

  str.sui_exempt "tax ex: sui",

  str.wc_exempt "tax ex: wc",

  str.sit_override_rate "sit override:regular rate",

  str.sit_override_amount "sit override:regular amount",

  str.supp_tax_override_rate "sit override: supp rate",

  str.sui_wage_base_override_amount "sui base override",

  str.excessive_wa_reject_date "lock in date",

  str.state_non_resident_cert "non resi certi",

  str.wage_exempt "sit exm fm wg accm",

  str.effective_start_date "effective date : from",

  decode (str.effective_end_date, to_date ('31124712', 'ddmmyyyy'), null, str.effective_end_date ) "effective date: to",

  str.assignment_id,

  str.business_group_id

from pay_us_emp_state_tax_rul str,

  pay_us_states pus,

  pay_state_rules psr,

  hr_lookups hrl,

  per_all_assignments_f paaf,

  per_all_people_f ppf

where 1 =1

  and str.state_code=pus.state_code

  and psr.state_code   = pus.state_abbrev

and hrl.lookup_type = psr.fs_lookup_type

and sysdate between paaf.effective_start_date and paaf.effective_end_date

and nvl (str.effective_end_date, sysdate)  > sysdate - 1

and hrl.lookup_code                        = ltrim (str.filing_status_code, '0')

and str.assignment_id                      = paaf.assignment_id

and nvl (paaf.effective_end_date, sysdate) > sysdate - 1

and paaf.person_id                         = ppf.person_id

--and ppf.employee_number                    = '30987'

--and paaf.assignment_number   = '471-96-6472'

group by ppf.employee_number,

  hrl.lookup_type,

  pus.state_name,pus.state_code,

  hrl.meaning,psr.state_code , pus.state_abbrev,

  str.withholding_allowances,

  str.secondary_wa,

  str.additional_wa_amount,

  str.sit_additional_tax,

  str.sit_optional_calc_ind,

  str.sit_exempt,

  str.sdi_exempt,

  str.sui_exempt,

  str.wc_exempt,

  str.sit_override_rate,

  str.sit_override_amount,

  str.supp_tax_override_rate,

  str.sui_wage_base_override_amount,

  str.excessive_wa_reject_date,

  str.state_non_resident_cert,

  str.wage_exempt,

  str.effective_start_date,

  decode (str.effective_end_date, to_date ('31124712', 'ddmmyyyy'), null, str.effective_end_date ),

  str.assignment_id,

  str.business_group_id;