1 Reply Latest reply on Jul 31, 2013 11:07 AM by Vigneswar Battu

    How to get the Tax Value in US Payroll

    Gaurav Bhardwaj

      Hello All

      Good Day!


      We are on Oracle Applications :, For a particular pay period i need to get the 'Reduced Subj WHable ' and 'Withheld' value and Jurisdiction for City Tax , County Tax, SIT, Head Tax, School District Tax etc for all employees. I can see these values for a employee in View Tax forms but not able to query from back end. By using the run results, i was able to get the value of Withheld but not sure if i am using the correct method.


      Any help would be really appreciated.


      Many Thanks


        • 1. Re: How to get the Tax Value in US Payroll
          Vigneswar Battu

          Hi Gaurav,


          If you're looking at run result values then you'll have to loop through the






          If you're looking for balances, you can use

          select pp.payroll_name,

          ppf.employee_number,paf.Assignment_Number Assignment_Number

              ,ppa.effective_date payroll_run_date


              ,hou.name Org_Name

              ,nvl(sum(decode(pbt.balance_name,'Net',prrv.result_value * pbf.scale,0)),0) Net

          from       pay_payroll_actions         ppa

                     ,pay_assignment_actions     paa

              ,pay_run_results         prr

                ,pay_run_result_values         prrv

                ,pay_balance_feeds_x         pbf

                ,pay_balance_types        pbt

                ,per_assignments_f         paf

              ,per_people_f             ppf

              ,hr_all_organization_units hou

              ,pay_payrolls_f pp

              ,per_time_periods ptp

          where upper(pp.payroll_name) like UPPER('%ABC%')

          and pp.payroll_id = ppa.payroll_id

          and ppa.time_period_id = ptp.time_period_id

          and pp.payroll_id = ptp.payroll_id

          --and   ppa.time_period_id      = 18637

          and upper(ptp.period_name) like upper('2 2013%')

          and   paa.payroll_action_id   = ppa.payroll_action_id

          and   paa.ASSIGNMENT_ID    = paf.ASSIGNMENT_ID

          and   paf.PERSON_ID       = ppf.PERSON_ID

          and   prr.assignment_action_id= paa.assignment_action_id

          and   pbf.balance_type_id    = pbt.balance_type_id

          and   pbf.input_value_id = prrv.input_value_id +0

          and   prr.status in ('P', 'PA')

          and   prrv.run_result_id    = prr.run_result_id

          and   ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date

          and   ppa.effective_date between paf.effective_start_date and paf.effective_end_date

          and   ppa.effective_date between pp.effective_start_date and pp.effective_end_date

          and hou.organization_id = paf.organization_id

          group by hou.name, ppf.employee_number,ppa.effective_date, pp.payroll_name,



          order by ppf.Full_Name,ppa.effective_date,hou.name;


          Change parameters accordingly.