SQL Queries - Payslips

Here are two queries which can be used for fetching employee salary details. The user has to replace the classifications and element names with appropriate ones. The user can also add more elements in query no.2 to display more elements

1. SQL query to display employee salary (classification wise)



SELECT 
ppf.employee_number,
ppf.full_name,
(ppa.EFFECTIVE_DATE)Payroll_Run_Date,
TP.PERIOD_NAME,
sum(decode(pec.CLASSIFICATION_NAME,'Earnings',to_number(rrv.result_value),0)) Earnings,
sum(decode(pec.CLASSIFICATION_NAME,'Involuntary Deductions',to_number(rrv.result_value),0)) Involuntary_Deductions,
sum(decode(pec.CLASSIFICATION_NAME,'Employer Charges',to_number(rrv.result_value),0))Employer_Charges,
sum(decode(pec.CLASSIFICATION_NAME,'Information',to_number(rrv.result_value),0)) Information
FROM per_people_x ppf,
per_assignments_x paf,
pay_assignment_actions pas,
pay_payroll_actions ppa,
pay_run_results rr,
pay_run_result_values rrv,
pay_element_types_f ety,
pay_input_values_F I,
PER_TIME_PERIODS TP,
PAY_ELEMENT_CLASSIFICATIONS_VL pec
WHERE ppf.person_id = paf.person_id
AND paf.assignment_id = pas.assignment_id
AND pas.assignment_action_id = rr.assignment_action_id
AND ppa.payroll_action_id = pas.payroll_action_id
AND rr.element_type_id = ety.element_type_id
AND i.element_type_id = ety.element_type_id
AND rrv.run_result_id = rr.run_result_id
AND rrv.input_value_id = i.input_value_id
and  TP.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID
and ety.CLASSIFICATION_ID=pec.CLASSIFICATION_ID
AND i.name = 'Pay Value'
and ppa.EFFECTIVE_DATE  BETWEEN :p_st_effect_date  AND  :p_end_effect_date
and ppf.employee_number  = nvl(:p_emp_number,ppf.employee_number)
group by ppf.employee_number,
ppf.person_id,
ppf.full_name,
ppa.TIME_PERIOD_ID,
ppa.EFFECTIVE_DATE,
TP.PERIOD_NAME,
paf.ORGANIZATION_ID
order by ppf.employee_number

2. SQL query to display employee salary (element wise)

SELECT 
ppf.employee_number,
ppf.full_name,
ppa.EFFECTIVE_DATE,
TP.PERIOD_NAME,
sum(decode(ety.element_name,'REN Basic',TO_NUMBER(rrv.result_value),0))Basic,
sum(decode(ety.element_name,'REN PF Employee',TO_NUMBER(rrv.result_value),0))PF_Employee
FROM per_people_x ppf,
per_assignments_x paf,
pay_assignment_actions pas ,
pay_payroll_actions ppa,
pay_run_results rr,
pay_run_result_values rrv,
pay_element_types_f ety,
pay_input_values_F I ,
PER_TIME_PERIODS TP
WHERE ppf.person_id = paf.person_id
AND paf.assignment_id = pas.assignment_id
AND pas.assignment_action_id = rr.assignment_action_id
AND ppa.payroll_action_id = pas.payroll_action_id
AND rr.element_type_id = ety.element_type_id
AND i.element_type_id = ety.element_type_id
AND rrv.run_result_id = rr.run_result_id
AND rrv.input_value_id = i.input_value_id
and  TP.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID
AND i.name = 'Pay Value'
and ppa.EFFECTIVE_DATE  BETWEEN    :P_FROM_DATE      AND  :P_TO_DATE
and ppf.employee_number    = :P_Employee_number
group by  ppf.full_name,ppa.TIME_PERIOD_ID,effective_date,
ppf.employee_number,ppf.person_id ,TP.period_name
order  by  ppa.EFFECTIVE_DATE

PREVIOUS POST || NEXT POST