6 Replies Latest reply: May 1, 2012 1:35 AM by 933841 RSS

    Need Query for the Monthly Total Pay

    933841
      dear all,
      please i need a query that it will show me the name ,ID number and the total pay
      the Total Pay= Total Earning - Total Deduction
      i can find the total pay in the payslips but i need the Query to do some reports

      thanx in advance
        • 1. Re: Need Query for the Monthly Total Pay
          933841
          please guys any help
          at least i need the table name where i can get the net pay salary
          • 2. Re: Need Query for the Monthly Total Pay
            Guest_0607
            Below is the query.

            select papf.EMPLOYEE_NUMBER, sum(prrv.RESULT_VALUE) Paid_Value
            from pay_payroll_actions ppa
            ,pay_assignment_actions paa
            ,pay_run_results prr
            ,pay_run_result_values prrv
            ,pay_input_values_f piv
            ,pay_element_types_f pet
            ,per_all_assignments_f paaf
            ,per_all_people_f papf
            where ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
            and paa.ASSIGNMENT_ID = paaf.ASSIGNMENT_ID
            and ppa.EFFECTIVE_DATE between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE
            and ppa.ACTION_TYPE = 'R'
            -- and ppa.BUSINESS_GROUP_ID = :BG_ID
            -- and ppa.ACTION_STATUS ='C'
            and prr.ASSIGNMENT_ACTION_ID = paa.ASSIGNMENT_ACTION_ID
            and prr.RUN_RESULT_ID = prrv.RUN_RESULT_ID
            and prrv.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
            and piv.NAME ='Pay Value'
            and pet.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
            and ppa.EFFECTIVE_DATE = :PAYROLL_DATE --'23-FEB-2012'
            and papf.EMPLOYEE_NUMBER = :EMP_ID
            and paaf.PERSON_ID = papf.person_id
            and ppa.EFFECTIVE_DATE between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE
            group by papf.EMPLOYEE_NUMBER
            • 3. Re: Need Query for the Monthly Total Pay
              933841
              thanx for the query but unfortunately the query take the deduction Elements as earning Elements
              so im getting a wrong amount
              i wish to get the same total net pay as it shown in the payslip
              regards
              • 4. Re: Need Query for the Monthly Total Pay
                Vigneswar Battu
                If you've run pre-payments, then all the 'Net Pay' amounts are in the table PAY_PRE_PAYMENTS

                Other option is to get it from balance - 'Net Pay'
                You can use pay_balance_pkg.get_value() to get balance values.

                Cheers,
                VB
                • 5. Re: Need Query for the Monthly Total Pay
                  Guest_0607
                  Changed Query is Below. You may change Classification Names if require.

                  select papf.EMPLOYEE_NUMBER, sum(DECODE (pec.classification_name,
                  'Involuntary Deductions', ( (-1)
                  * TO_NUMBER (prrv.result_value)),
                  'Statutory Deductions', ( (-1)
                  * TO_NUMBER (prrv.result_value)),
                  'Earnings', TO_NUMBER (prrv.result_value)
                  )) Paid_Value
                  from apps.pay_payroll_actions ppa
                  ,apps.pay_assignment_actions paa
                  ,apps.pay_run_results prr
                  ,apps.pay_run_result_values prrv
                  ,apps.pay_input_values_f piv
                  ,apps.pay_element_types_f pet
                  ,apps.pay_element_classifications pec
                  ,apps.per_all_assignments_f paaf
                  ,apps.per_all_people_f papf
                  where ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
                  and paa.ASSIGNMENT_ID = paaf.ASSIGNMENT_ID
                  and ppa.EFFECTIVE_DATE between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE
                  and ppa.ACTION_TYPE = 'R'
                  -- and ppa.BUSINESS_GROUP_ID = :BG_ID
                  -- and ppa.ACTION_STATUS ='C'
                  and prr.ASSIGNMENT_ACTION_ID = paa.ASSIGNMENT_ACTION_ID
                  and prr.RUN_RESULT_ID = prrv.RUN_RESULT_ID
                  and prrv.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
                  and piv.NAME ='Pay Value'
                  and pet.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
                  and pet.CLASSIFICATION_ID = pec.CLASSIFICATION_ID
                  and ppa.EFFECTIVE_DATE = :PAYROLL_DATE --'23-FEB-2012' --:PAYROLL_DATE --'23-FEB-2012'
                  and papf.EMPLOYEE_NUMBER = :EMP_ID
                  and paaf.PERSON_ID = papf.person_id
                  and ppa.EFFECTIVE_DATE between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE
                  group by papf.EMPLOYEE_NUMBER
                  • 6. Re: Need Query for the Monthly Total Pay
                    933841
                    really thanx alot guys its solved