3 Replies Latest reply: Jan 3, 2013 4:26 AM by Jenny_Gomez RSS

    payroll report query

      in the initiation stage of project i managed to create a view that can pick the total additions and total deductions and compute the net salary of the employee. after doing the customer room pilot ...now there is a change in the requirement.

      total additions and total deductions and net salary must appear based on the following:
      payment method of the employee and the currency will be picked from the element definition screen as element output currency... now we have multiple payroll runs based on the Assignment set defined. Also in case a quick pay is processed at the begin of the month and an actual payroll is run at the end of the pay period (monthly payroll) then system must show 2 payslips on the number of times quick pay and/or payroll was run.

      i have managed to write a select command as follows:

      SELECT TO_CHAR (TO_DATE (ppa.effective_date, 'DD-MON-RRRR'),'Month RRRR') PaySlip_of_month,pea.segment3 Account_number, hrl.meaning bank_name,
      +          sum(prrv.result_value) total_earnings,+
      +     (SELECT SUM (prrv_dedu.result_value)+
      FROM   pay_payroll_actions ppa_dedu,
      pay_assignment_actions paa_dedu,
      pay_run_results prr_dedu,
      pay_run_result_values prrv_dedu,
      per_all_assignments_f paaf_dedu,
      pay_input_values_f pivf_dedu,
      pay_element_types_f petf_dedu,
      pay_element_classifications pec_dedu
      WHERE   ppa_dedu.payroll_action_id = paa_dedu.payroll_action_id
      AND ppa_dedu.business_group_id = paaf_dedu.business_group_id
      AND paa_dedu.assignment_id = paaf_dedu.assignment_id
      AND prr_dedu.assignment_action_id = paa_dedu.assignment_action_id
      AND prrv_dedu.run_result_id = prr_dedu.run_result_id
      AND prr_dedu.element_type_id = petf_dedu.element_type_id
      AND paaf_dedu.assignment_id = paa_dedu.assignment_id
      AND ppa_dedu.effective_date BETWEEN paaf_dedu.effective_start_date AND  paaf_dedu.effective_end_date
      AND pivf_dedu.input_value_id = prrv_dedu.input_value_id
      AND petf_dedu.classification_id = pec_dedu.classification_id
      AND ppa_dedu.effective_date BETWEEN pivf_dedu.effective_start_date AND  pivf_dedu.effective_end_date
      AND ppa_dedu.effective_date BETWEEN petf_dedu.effective_start_date AND  petf_dedu.effective_end_date
      AND ( (pivf_dedu.name = 'Pay Value' AND pivf_dedu.uom = 'M') OR (pivf_dedu.name = 'Days'AND pivf_dedu.uom = 'ND'))
      AND pec_dedu.classification_name IN('Voluntary Deductions')
      AND paaf_dedu.person_id = paaf.person_id
      +               AND ppa_dedu.effective_date=ppa.effective_date) Total_Deductions,+
      +               TO_CHAR (ppa.effective_date, 'DD-MON-RRRR') payslip_date,paaf.person_id, paaf.assignment_id,paaf.business_group_id+
      FROM per_all_assignments_f paaf,
      per_all_people_f papf,
      pay_assignment_actions paa,
      pay_payroll_actions ppa,
      pay_org_payment_methods_f_tl opmtl,
      pay_personal_payment_methods_f ppm,
      pay_org_payment_methods_f opm,
      pay_external_accounts pea,
      hr_lookups hrl,
      pay_run_results prr,
      pay_run_result_values prrv,
      pay_input_values_f pivf,
      pay_element_types_f petf,
      pay_element_classifications pec
      WHERE papf.person_id = paaf.person_id
      AND ppa.effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
      AND ppa.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
      AND ppa.effective_date BETWEEN ppm.effective_start_date AND ppm.effective_end_date
      AND ppa.effective_date BETWEEN pivf.effective_start_date
      AND  pivf.effective_end_date AND ppa.effective_date BETWEEN petf.effective_start_date AND  petf.effective_end_date
      AND papf.current_employee_flag = 'Y'
      AND paaf.primary_flag = 'Y'
      AND paaf.assignment_type = 'E'
      AND paaf.business_group_id = '81'
      AND paa.assignment_id = paaf.assignment_id
      AND ppa.business_group_id = paaf.business_group_id
      AND ppa.payroll_action_id = paa.payroll_action_id
      AND ppm.assignment_id = paaf.assignment_id
      AND opm.org_payment_method_id = opmtl.org_payment_method_id
      AND ppm.org_payment_method_id = opm.org_payment_method_id
      AND ppm.external_account_id = pea.external_account_id
      AND hrl.lookup_code = pea.segment1
      AND prr.assignment_action_id = paa.assignment_action_id
      AND prrv.run_result_id = prr.run_result_id
      AND prr.element_type_id = petf.element_type_id
      AND pec.classification_id = petf.classification_id
      AND pivf.input_value_id = prrv.input_value_id
      AND hrl.lookup_type = 'BANK_NAMES'
      AND hrl.enabled_flag = 'Y'
      AND opmtl.org_payment_method_name = 'USD Payment Method'
      --AND papf.employee_number =:employee_number+
      AND ((pivf.NAME = 'Pay Value' AND pivf.uom = 'M') OR (pivf.NAME = 'Days' AND pivf.uom = 'ND') )
      AND pec.classification_name = 'Earnings'
      AND pec.legislation_code = 'US'               
      group by  TO_CHAR (TO_DATE (ppa.effective_date, 'DD-MON-RRRR'),'Month RRRR'),pea.segment3 , pea.segment1, hrl.meaning, paaf.person_id,ppa.effective_date, paaf.assignment_id,paaf.business_group_id

      when i execute the above code on the toad it gives me only output for 1 type of payment method and one type of currency attached to the payment method i.e US. this is obvious because i have entered the payment method.

      Now, i would like system to dynamically pass parameter and whether the currency type (US or GB) this query must pick the payment method matching the currency and name of the payment method and then display the payslip summary details (i.e. additions, deductions & net sal).

      could any one out there assist me on this?

      appreciate all comments!