Calculating Employee Wise Earnings Deductions And Net Pay IN HRMS
SELECT a.Earnings, b.Deductions, (a.Earnings - b.Deductions) Net_Pay
FROM ( SELECT SUM (earnings) Earnings, emp_num
FROM (SELECT papf.employee_number emp_num,
papf.full_name,
ppa.effective_date,
pp.payroll_name,
pet.element_name,
piv.name input_value,
TO_NUMBER (prrv.result_value) earnings
FROM apps.pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_payrolls_f pp,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_element_types_f pet,
apps.per_all_assignments_f paaf,
apps.per_all_people_f papf,
pay_element_classifications_tl pectl,
pay_element_classifications pec
WHERE --ppa.payroll_action_id = :payroll_action_id -- give your payroll_action_id
--and
ppa.payroll_action_id = paa.payroll_action_id
AND ppa.payroll_id = pp.payroll_id
AND paa.assignment_action_id =
prr.assignment_action_id
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND paaf.assignment_id = paa.assignment_id
AND paaf.person_id = papf.person_id
AND piv.name = 'Pay Value'
AND pec.classification_id = pectl.classification_id
AND pectl.language = USERENV ('LANG')
-- Add All Earnings CLASSIFICATION_NAMES or ID's
AND pec.CLASSIFICATION_NAME IN
('Allowances',
'Fringe Benefits',
'Earnings',
'Advances')
AND pet.classification_id = pec.classification_id
AND papf.employee_number =
NVL (:p_emp_num, papf.employee_number)
AND TRUNC (SYSDATE) BETWEEN pp.effective_start_date
AND pp.effective_end_date
AND TRUNC (SYSDATE) BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND TRUNC (SYSDATE) BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date)
GROUP BY emp_num) a,
( SELECT SUM (dectuctions) Deductions, emp_num
FROM (SELECT papf.employee_number emp_num,
papf.full_name,
ppa.effective_date,
pp.payroll_name,
pet.element_name,
piv.name input_value,
TO_NUMBER (prrv.result_value) dectuctions
FROM apps.pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_payrolls_f pp,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_element_types_f pet,
apps.per_all_assignments_f paaf,
apps.per_all_people_f papf,
pay_element_classifications_tl pectl,
pay_element_classifications pec
WHERE --ppa.payroll_action_id = :payroll_action_id -- give your payroll_action_id
--and
ppa.payroll_action_id = paa.payroll_action_id
AND ppa.payroll_id = pp.payroll_id
AND paa.assignment_action_id =
prr.assignment_action_id
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND paaf.assignment_id = paa.assignment_id
AND paaf.person_id = papf.person_id
AND piv.name = 'Pay Value'
AND pec.classification_id = pectl.classification_id
AND pectl.language = USERENV ('LANG')
--Add All Deductions CLASSIFICATION_NAMES or ID's
AND pec.CLASSIFICATION_NAME IN
('Involuntary Deductions')
AND pet.classification_id = pec.classification_id
AND papf.employee_number =
NVL (:p_emp_num, papf.employee_number)
AND TRUNC (SYSDATE) BETWEEN pp.effective_start_date
AND pp.effective_end_date
AND TRUNC (SYSDATE) BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND TRUNC (SYSDATE) BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date)
GROUP BY emp_num) b
WHERE a.emp_num = b.emp_num;
No comments:
Post a Comment