1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to see the net amount to be paid to each employee in a particular period

Discussion in 'Oracle HRMS & Payroll' started by anutosh100, Sep 6, 2014.

  1. anutosh100

    anutosh100 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Dear all,

    I am new to payroll module. Could you please tell me How to see the net amount to be paid to each employee in a particular period.
    This report will run after the monthly prepayment and payment process run.

    I want the report in below format

    employee name, salary amount, remitters name, remitters account
    ----------------- ---------------- ---------------- ---------------------

    or

    employee name, salary amount
    ----------------- ----------------

    this much information will be good for me.

    regards
    Rajat
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Rajat,

    From the application side (front end), you can view these information through :
    View -> Assign. Process Results (for a given employee and period) or View -> Payroll Process Results (for a given payroll run) and choose to view either the balances or SOE Report.

    From the back end :
    - employee name => PER_ALL_PEOPLE_F.last_name or full_name
    - net salary amount => one way to get this amount is through balances; I suppose a balance has been defined (PTD) for Net Pay and you can use the standard oracle package "hr_dirbal.get_balance" to get the net salary amount (i.e. net pay).

    A sample SQL is shown below - you can build up on this query to get what you need:

    Code (SQL):

    --
    -- <p_bg>: your business group id
    -- <p_balance_id> : balance id for PTD Net pay balance
    -- <p_date> : end date of your payroll period, for ex. '31-AUG-2014' for August payroll
    --
    SELECT papf.full_name,
      hr_dirbal.get_balance(
         p_assignment_id => paaf.assignment_id,
         p_defined_balance_id => <p_balance_id>,
         p_effective_date => <p_date>
      ) net_pay
    FROM per_all_assignments_f paaf,
      per_all_people_f papf
    WHERE 1=1
    AND papf.business_group_id = <p_bg>
    AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
    AND paaf.person_id = papf.person_id
    AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date
    AND paaf.primary_flag = 'Y'
    ;
     
    This is just a starting point...

    Another way is to start from PAY_PAYROLL_ACTIONS, PAY_ASSIGNMENT_ACTIONS, PAY_RUN_RESULTS etc...for a given payroll period to arrive at the same results.

    HTH.
     
    anutosh100 likes this.
  3. anutosh100

    anutosh100 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Dear rajenb ,

    Thanks for your reply. I have prepared one sql for the above requirement . Could you please check and tell me whether the approach is correct or not.

    Could you please tell me how could you embedded the sql inside the editor. Every time I tried I encountered an error.

    Regards
    Rajat
     

    Attached Files:

  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Rajat,

    That's another way of getting your results - approach looks Ok; I suppose you use Payroll run types in your legislation.

    You have to check and test it within your environment and instance.

    As for embedding sql code in the posts:
    1) Paste your code in post area
    2) Select and highlight it
    3) Click on "SQL" icon found on top right of post area
     
    anutosh100 likes this.
  5. anutosh100

    anutosh100 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Dear rajenb ,

    Thank you very much for the code verification. Could you please tell me which tables I should query if I want to know how much money is paid through which bank after the payroll processing.

    Regards
    Rajat
     
  6. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Rajat,

    I suppose you can use same query and adapt it to get the total amount per bank.

    The bank code is stored in pay_external_accounts table (segment? - depends on how you've configured your bank accounts for Payroll).
    You just have to SUM(ppp.value) ... and GROUP BY pea.segment?