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
