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

group by 

ppf.employee_number,

ppf.person_id,

ppf.full_name,

ppa.TIME_PERIOD_ID,

ppa.EFFECTIVE_DATE,

TP.PERIOD_NAME,

order  by  ppa.EFFECTIVE_DATE