السلام عليكم
ياإخوان ممكن أحد يساعدني عن ايجاد جدول الرواتب الاجمالية( Net PAY)
يعني نفسي أعرف الراتب اللي نزل في حسابه بعد الخصومات
وأبغاه اس كيو ال كود ومو عن طريق السيستم
وشكرا

الحمدلله وجدت الحل

select papf.EMPLOYEE_NUMBER,ppa.EFFECTIVE_DATE, sum(DECODE (pec.classification_name,
'Involuntary Deductions', ( (-1)
* TO_NUMBER (prrv.result_value)),
'Statutory Deductions', ( (-1)
* TO_NUMBER (prrv.result_value)),
'Earnings', TO_NUMBER (prrv.result_value)
)) Paid_Value
from pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
,pay_run_result_values prrv
,pay_input_values_x piv
,pay_element_types_x pet
,per_assignments_x paaf
,per_people_x papf
,apps.pay_element_classifications pec
where ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
and paa.ASSIGNMENT_ID = paaf.ASSIGNMENT_ID
and ppa.EFFECTIVE_DATE between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE
and ppa.ACTION_TYPE = 'R'
-- and ppa.BUSINESS_GROUP_ID = :BG_ID
-- and ppa.ACTION_STATUS ='C'
and prr.ASSIGNMENT_ACTION_ID = paa.ASSIGNMENT_ACTION_ID
and prr.RUN_RESULT_ID = prrv.RUN_RESULT_ID
and prrv.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
and piv.NAME ='Pay Value'
and pet.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and pet.CLASSIFICATION_ID = pec.CLASSIFICATION_ID
and ppa.EFFECTIVE_DATE=:E
--'23-FEB-2012' --:PAYROLL_DATE
and papf.EMPLOYEE_NUMBER=:ID
and paaf.PERSON_ID = papf.person_id
and ppa.EFFECTIVE_DATE between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE
group by papf.EMPLOYEE_NUMBER,ppa.EFFECTIVE_DATE
order by ppa.EFFECTIVE_DATE

Great!

You can also use the following script to find basc salary:

SELECT papf.employee_number
,papf.full_name
,pj.NAME job
,haou.NAME ORGANIZATION
,ppp.proposed_salary_n salary
FROM per_all_people_f papf
,per_all_assignments_f paaf
,per_jobs pj
,hr_all_organization_units haou
,per_position_definitions ppd
,per_all_positions pap
,per_pay_proposals ppp
WHERE 1 = 1
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.current_employee_flag = 'Y'
AND papf.employee_number IS NOT NULL
AND paaf.person_id = papf.person_id
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.job_id = pj.job_id
And papf.full_name like '%MALIK%'
AND paaf.organization_id = haou.organization_id
AND paaf.position_id = pap.position_id
AND pap.position_definition_id = ppd.position_definition_id
AND ppp.pay_proposal_id = (SELECT MAX (pay_proposal_id)
FROM per_pay_proposals
WHERE assignment_id = paaf.assignment_id)