Friday, November 29, 2013

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect