Query for Oracle Payroll Salary Slip

Query for Oracle Payroll Salary Slip 

Query for Oracle Payroll Salary Slip /  Salary Slip Detail Report Query / How to find out detail of salary slip/ Element wise Landscape Salary Slip at Payroll Run Levle  or quick pay level:

*************

SELECT ppf.employee_number,ppf.person_id,ppf.full_name,ppa.TIME_PERIOD_ID,ppa.EFFECTIVE_DATE  ,TP.PERIOD_NAME,
sum(decode(ety.element_name,’Basic Salary’                         ,TO_NUMBER(rrv.result_value),
‘Basic Sick Leave Payment’             ,TO_NUMBER(rrv.result_value),
‘Basic Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value),
0)) Earned_salary,
/********************************************************************************************************/
sum(decode(ety.element_name,’Transportation Allowance’                      ,TO_NUMBER(rrv.result_value),
‘Transportation Sick Leave Payment’             ,TO_NUMBER(rrv.result_value),
‘Transportation Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value),
0)) Transportation_allowance,
/*************************************************************************************************************/
sum(decode(ety.element_name,’Work Type Allowance’                      ,TO_NUMBER(rrv.result_value),
‘Work Type Sick Leave Payment’             ,TO_NUMBER(rrv.result_value),
‘Work Type Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value),
0)) worktype_allowance,
/***************************************************************************************************************/
sum(decode(ety.element_name,’Damages Allowance’                      ,TO_NUMBER(rrv.result_value),
‘Damages Sick Leave Payment’             ,TO_NUMBER(rrv.result_value),
‘Damages Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value),
0)) Damage_allowance,
/*****************************************************************************************************************/
sum(decode(ety.element_name,’Danger Allowance’                      ,TO_NUMBER(rrv.result_value),
‘Danger Sick Leave Payment’             ,TO_NUMBER(rrv.result_value),
‘Danger Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value),
0)) Danger_allowance,
/*************************************************************************************************************/
sum(decode(ety.element_name,’Inflation Allowance’    ,TO_NUMBER(rrv.result_value),0)) Inflation_Allowance,
/*********************************************************  Deductions  ************************************************/
sum(decode(ety.element_name,’Loan Recovery’         ,TO_NUMBER(rrv.result_value),0)) Loan_recovery,
sum(decode(ety.element_name,’Loan Recovery Housing’ ,TO_NUMBER(rrv.result_value),0)) Loan_Recovery_Housing,
sum(decode(ety.element_name,’Loan Recovery Others’  ,TO_NUMBER(rrv.result_value),0)) Loan_Recovery_Others,
sum(decode(ety.element_name,’Housing Deduction’     ,TO_NUMBER(rrv.result_value),0)) Housing_Deduction,
sum(decode(ety.element_name,’Penalty’               ,TO_NUMBER(rrv.result_value),0)) Penalty,
/***********************************************************************************************************************/
sum(decode(ety.element_name,’Civil Pension’                      ,TO_NUMBER(rrv.result_value),
‘Social Insurance’             ,TO_NUMBER(rrv.result_value),
0)) Civil_pension,
/************************************************************************************************************************************/
sum(decode(ety.element_name,’In Out Leave Deduction’ ,TO_NUMBER(rrv.result_value),0)) In_Out_Leave_Deduction,
sum(decode(ety.element_name,’Unpaid Leave Deduction’ ,TO_NUMBER(rrv.result_value),0)) Unpaid_Leave_Deduction,
sum(decode(ety.element_name,’Retrieve Mony Recovery’ ,TO_NUMBER(rrv.result_value),0)) Retrieve_Mony_Recovery
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
–     PER_POSITION_DEFINITIONS PD
– PAY_INPUT_VALUES_F
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 HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(PAF.POSITION_ID) = PD.POSITION_DEFINITION_ID
and ppa.EFFECTIVE_DATE  BETWEEN    :P_FROM_DATE      AND  :P_TO_DATE
and ppf.employee_number    = :P_Employee_number
–in(34000/*1546014859,14666,35343,15201,15202*/)
group by  ppf.full_name,ppa.TIME_PERIOD_ID,effective_date,
ppf.employee_number,ppf.person_id ,–PD.SEGMENT5 ,PD.SEGMENT6,
TP.period_name
order  by  ppa.EFFECTIVE_DATE

**************************