Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Sunday, 5 February 2017

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;
Share this article :

0 comments:

Post a Comment