السلام عليكم

الاخوه الكرام ,

ارجو المساعدة في جملة سليكت لإحضار الراتب التفصيلي للموظف (الراتب الاساسي , بدل نقل , بدل سكن ).

وبارك الله فيكم

select distinct pe.EMPLOYEE_NUMBER
,pe.full_name
,pap.payroll_name payroll_name
,pap.payroll_id
,pay_ac.DATE_EARNED
,pos.name position_name
,loc.location_code location_name
,pe.original_date_of_hire
,ass.assignment_id

,nvl(other_fees.RESULT_VALUE,0)other_fees
,nvl(special_bonus.RESULT_VALUE,0)special_bonus
,nvl(SI_salary.RESULT_VALUE,0)SI_salary
,nvl(overtime.RESULT_VALUE,0)overtime
,nvl(grants.RESULT_VALUE,0)grants
,nvl(comp_insu.RESULT_VALUE,0)comp_insu
,nvl(supervision_fees.RESULT_VALUE,0)supervision_fees
,nvl(working_fees.RESULT_VALUE,0)working_fees
,nvl(transportations.RESULT_VALUE,0)transportations
,nvl(travelling.RESULT_VALUE,0)travelling



,nvl(latness.RESULT_VALUE,0)latness
,nvl(leaving.RESULT_VALUE,0)leaving
,nvl(execuse.RESULT_VALUE,0)execuse
,nvl(absence_without_permission.RESULT_VALUE,0)absence_without_permission
,nvl(absence_with_permission.RESULT_VALUE,0)absence_with_permission
,nvl(college_box.RESULT_VALUE,0)college_box
,nvl(penalties.RESULT_VALUE,0)penalties
,nvl(emp_insu.RESULT_VALUE,0)emp_insu
,nvl(orphans_box.RESULT_VALUE,0)orphans_box
,nvl(company_loan.RESULT_VALUE,0)company_loan
,nvl(box_loan.RESULT_VALUE,0)box_loan
,nvl(box_travelling_loan.RESULT_VALUE,0)box_travelling_loan
,nvl(repealed_loan.RESULT_VALUE,0)repealed_loan
,nvl(inability_loan.RESULT_VALUE,0)inability_loan



from PER_PEOPLE_f pe
,per_assignments_f ass
,hr_all_positions_f pos
,hr_locations loc
,pay_run_results prr
,pay_run_result_values prrv
,pay_input_values_f piv
,pay_payrolls_f pap
,pay_payroll_actions pay_ac
,pay_assignment_actions paa
,PAY_ELEMENT_TYPES_F ele
,PAY_ELEMENT_CLASSIFICATIONS Cls

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in(91,90,92,101,86)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) other_fees

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in (99,108)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) special_bonus

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in (82)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) SI_salary

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in (104)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) overtime

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in (114)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) grants

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
--and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in (105)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) comp_insu


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in(97,100,98,84,96,93)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) supervision_fees


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in(95)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) working_fees


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in(89)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) transportations

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in(85,141)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) travelling









,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID =106
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) latness

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID =110
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) leaving

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID =111
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) execuse

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID =103
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) absence_without_permission

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID =102
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) absence_with_permission

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(116)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) college_box

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(115)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) penalties


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(488)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) emp_insu


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(478)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) orphans_box


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(491)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) company_loan


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(460)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) box_loan


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(462)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) box_travelling_loan


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(577)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) repealed_loan


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(539)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) inability_loan



where pe.PERSON_ID = ass.PERSON_ID
and ass.position_id=pos.position_id
and ass.location_id=loc.location_id
and pay_ac.DATE_EARNED between ass.effective_start_date and ass.effective_end_date
and pay_ac.DATE_EARNED between pe.effective_start_date and pe.effective_end_date
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and prrv.RESULT_VALUE!=0

and other_fees.person_id (+)=pe.person_id
and special_bonus.person_id (+)=pe.person_id
and SI_salary.person_id(+)=pe.person_id
and overtime.person_id(+)=pe.person_id
and grants.person_id(+)=pe.person_id
and comp_insu.person_id(+)=pe.person_id
and supervision_fees.person_id(+)=pe.person_id
and working_fees.person_id(+)=pe.person_id
and transportations.person_id(+)=pe.person_id
and travelling.person_id(+)=pe.person_id


and latness.person_id(+)=pe.person_id
and leaving.person_id(+)=pe.person_id
and execuse.person_id(+)=pe.person_id
and absence_without_permission.person_id(+)=pe.person_id
and absence_with_permission.person_id(+)=pe.person_id
and college_box.person_id(+)=pe.person_id
and penalties.person_id(+)=pe.person_id
and emp_insu.person_id(+)=pe.person_id
and orphans_box.person_id(+)=pe.person_id
and company_loan.person_id(+)=pe.person_id
and box_loan.person_id(+)=pe.person_id
and box_travelling_loan.person_id(+)=pe.person_id
and repealed_loan.person_id(+)=pe.person_id
and inability_loan.person_id(+)=pe.person_id


and pe.employee_number between nvl(:from_emp,pe.employee_number) and nvl(:to_emp,pe.employee_number)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')

order by to_number(pe.EMPLOYEE_NUMBER)


السلام عليكم
لابد من تعديل ارقام element طبقا للــــsetup الى عندك

select distinct pe.EMPLOYEE_NUMBER
,pe.full_name
,pap.payroll_name payroll_name
,pap.payroll_id
,pay_ac.DATE_EARNED
,pos.name position_name
,loc.location_code location_name
,pe.original_date_of_hire
,ass.assignment_id

,nvl(other_fees.RESULT_VALUE,0)other_fees
,nvl(special_bonus.RESULT_VALUE,0)special_bonus
,nvl(SI_salary.RESULT_VALUE,0)SI_salary
,nvl(overtime.RESULT_VALUE,0)overtime
,nvl(grants.RESULT_VALUE,0)grants
,nvl(comp_insu.RESULT_VALUE,0)comp_insu
,nvl(supervision_fees.RESULT_VALUE,0)supervision_fees
,nvl(working_fees.RESULT_VALUE,0)working_fees
,nvl(transportations.RESULT_VALUE,0)transportations
,nvl(travelling.RESULT_VALUE,0)travelling



,nvl(latness.RESULT_VALUE,0)latness
,nvl(leaving.RESULT_VALUE,0)leaving
,nvl(execuse.RESULT_VALUE,0)execuse
,nvl(absence_without_permission.RESULT_VALUE,0)absence_without_permission
,nvl(absence_with_permission.RESULT_VALUE,0)absence_with_permission
,nvl(college_box.RESULT_VALUE,0)college_box
,nvl(penalties.RESULT_VALUE,0)penalties
,nvl(emp_insu.RESULT_VALUE,0)emp_insu
,nvl(orphans_box.RESULT_VALUE,0)orphans_box
,nvl(company_loan.RESULT_VALUE,0)company_loan
,nvl(box_loan.RESULT_VALUE,0)box_loan
,nvl(box_travelling_loan.RESULT_VALUE,0)box_travelling_loan
,nvl(repealed_loan.RESULT_VALUE,0)repealed_loan
,nvl(inability_loan.RESULT_VALUE,0)inability_loan



from PER_PEOPLE_f pe
,per_assignments_f ass
,hr_all_positions_f pos
,hr_locations loc
,pay_run_results prr
,pay_run_result_values prrv
,pay_input_values_f piv
,pay_payrolls_f pap
,pay_payroll_actions pay_ac
,pay_assignment_actions paa
,PAY_ELEMENT_TYPES_F ele
,PAY_ELEMENT_CLASSIFICATIONS Cls

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in(91,90,92,101,86)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) other_fees

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in (99,108)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) special_bonus

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in (82)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) SI_salary

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in (104)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) overtime

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in (114)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) grants

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
--and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in (105)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) comp_insu


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in(97,100,98,84,96,93)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) supervision_fees


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in(95)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) working_fees


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in(89)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) transportations

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Earnings'
and ele.ELEMENT_TYPE_ID in(85,141)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) travelling









,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID =106
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) latness

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID =110
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) leaving

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID =111
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) execuse

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID =103
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) absence_without_permission

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID =102
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) absence_with_permission

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(116)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) college_box

,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(115)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) penalties


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(488)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) emp_insu


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(478)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) orphans_box


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(491)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) company_loan


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(460)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) box_loan


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(462)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) box_travelling_loan


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(577)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) repealed_loan


,(
select person_id,sum(prrv.RESULT_VALUE) RESULT_VALUE
from per_assignments_f ass ,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_payrolls_f pap ,
pay_payroll_actions pay_ac,
pay_assignment_actions paa,
PAY_ELEMENT_TYPES_F ele,
PAY_ELEMENT_CLASSIFICATIONS Cls
where to_char(ass.effective_end_date,'dd-mm-yyyy')= '31-12-4712'
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
and piv.NAME ='Pay Value'
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and cls.CLASSIFICATION_NAME='Voluntary Deductions'
and ele.ELEMENT_TYPE_ID in(539)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')
group by person_id
) inability_loan



where pe.PERSON_ID = ass.PERSON_ID
and ass.position_id=pos.position_id
and ass.location_id=loc.location_id
and pay_ac.DATE_EARNED between ass.effective_start_date and ass.effective_end_date
and pay_ac.DATE_EARNED between pe.effective_start_date and pe.effective_end_date
AND prr.assignment_action_id = paa.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id +0
AND pap.payroll_id =ass.payroll_id
and pay_ac.action_type in('R','Q')
and pap.payroll_id=pay_ac.payroll_id
and pay_ac.payroll_action_id = paa.payroll_action_id
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and ele.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and ele.CLASSIFICATION_ID = cls.CLASSIFICATION_ID
and prrv.RESULT_VALUE!=0

and other_fees.person_id (+)=pe.person_id
and special_bonus.person_id (+)=pe.person_id
and SI_salary.person_id(+)=pe.person_id
and overtime.person_id(+)=pe.person_id
and grants.person_id(+)=pe.person_id
and comp_insu.person_id(+)=pe.person_id
and supervision_fees.person_id(+)=pe.person_id
and working_fees.person_id(+)=pe.person_id
and transportations.person_id(+)=pe.person_id
and travelling.person_id(+)=pe.person_id


and latness.person_id(+)=pe.person_id
and leaving.person_id(+)=pe.person_id
and execuse.person_id(+)=pe.person_id
and absence_without_permission.person_id(+)=pe.person_id
and absence_with_permission.person_id(+)=pe.person_id
and college_box.person_id(+)=pe.person_id
and penalties.person_id(+)=pe.person_id
and emp_insu.person_id(+)=pe.person_id
and orphans_box.person_id(+)=pe.person_id
and company_loan.person_id(+)=pe.person_id
and box_loan.person_id(+)=pe.person_id
and box_travelling_loan.person_id(+)=pe.person_id
and repealed_loan.person_id(+)=pe.person_id
and inability_loan.person_id(+)=pe.person_id


and pe.employee_number between nvl(:from_emp,pe.employee_number) and nvl(:to_emp,pe.employee_number)
and to_char(pay_ac.DATE_EARNED,'mm-yyyy')=to_char(:dat,'mm-yyyy')

order by to_number(pe.EMPLOYEE_NUMBER)


السلام عليكم
لابد من تعديل ارقام element طبقا للــــsetup الى عندك

Thank's A lot
Tomorrow i will check in company . and confirm you

thanks again