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 : 11.5.10.2, 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

      Gaurav

        • 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

          pay_payroll_actions

          pay_assignment_actions

          pay_run_results
          pay_run_result_values

           

           

          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

              ,ppf.full_name

              ,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,

               paf.Assignment_Number

              ,ppf.Full_Name

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

           

          Change parameters accordingly.

           

          Cheers,

          Vignesh