Employee tax info and fedral tax rules

select ppf.employee_number "employee number",

  (select state_name from pay_us_states where state_abbrev = add_information17

  ) "resident",

  (select hlat.region_2

  from hr_locations_all hlat

  where hlat.location_id = paaf.location_id

  ) "work",

  pus.state_name "sui state",

  paa.add_information18 "resident city",

  pus.state_name "work city",

  paa.add_information19 "resident county",

  (select hlat.region_1

  from hr_locations_all hlat

  where hlat.location_id = paaf.location_id

  ) "work county",

  hrl1.meaning "filing status",

  ftr.withholding_allowances "allowances",

  ftr.fit_additional_tax "additional tax",

  ftr.fit_exempt "tax exemption :fit",

  ftr.futa_tax_exempt "tax exemption :futa",

  ftr.medicare_tax_exempt "tax exemption :medicare",

  ftr.ss_tax_exempt "tax exemption :ss",

  ftr.fit_override_amount "fo:regular amount",

  ftr.fit_override_rate "fo:regular rate",

  ftr.supp_tax_override_rate "fo:supplemental rate",

  ftr.excessive_wa_reject_date "lock in date",

  ftr.statutory_employee "statutory employee",

  ftr.cumulative_taxation "cumulative taxation",

  ftr.wage_exempt "fit exempt wage acc",

  hrl2.meaning "eic filling status",

  ftr.effective_start_date "effec dates: from",

  decode (ftr.effective_end_date, to_date ('31124712', 'ddmmyyyy'), null, ftr.effective_end_date ) "effec dates: to",

  ftr.emp_fed_tax_rule_id,

  paaf.assignment_id,

  paaf.location_id

from pay_us_emp_fed_tax_rul ftr,

  per_all_assignments_f paaf,

  pay_us_states pus,

  per_addresses paa,

  hr_lookups hrl1,

  hr_lookups hrl2,

  per_all_people_f ppf

where 1=1

and ftr.sui_state_code         = pus.state_code

and ftr.assignment_id          = paaf.assignment_id

and paa.person_id              = paaf.person_id

and ftr.filing_status_code     = hrl1.lookup_code

and hrl1.lookup_type           = 'us_fit_filing_status'

and ftr.eic_filing_status_code = hrl2.lookup_code

and hrl2.lookup_type           = 'us_eic_filing_status'

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

and ftr.assignment_id   = paaf.assignment_id

and paaf.person_id      = ppf.person_id

---and ppf.employee_number = '30987'

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

group by ppf.employee_number,

  pus.state_name,

  paa.add_information18,

  pus.state_name,

  add_information17,

  paa.add_information19,

  hrl1.meaning,

  ftr.withholding_allowances,

  ftr.fit_additional_tax,

  ftr.fit_exempt,

  ftr.futa_tax_exempt,

  ftr.medicare_tax_exempt,

  ftr.ss_tax_exempt,

  ftr.fit_override_amount,

  ftr.fit_override_rate,

  ftr.supp_tax_override_rate,

  ftr.excessive_wa_reject_date,

  ftr.statutory_employee,

  ftr.cumulative_taxation,

  ftr.wage_exempt,

  hrl2.meaning,

  ftr.effective_start_date,

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

  ftr.emp_fed_tax_rule_id,

  paaf.assignment_id,

  paaf.location_id

order by 1,

  2;