حساب تفصيلي راتب الموظف

[mohamedhanyhekal]

انا عملت البروسيدجر بيجيب الراتب الاساسي والبدلات والاضافات ولاإستقطاعات الجزء بتاع الاضافات والإستقطاعات بيجيب داتا مضبوطه في الSQL
بس في الفورم بيرجع اصفار

حد ممكن يساعدني ؟ ولكم جزيل الشكر

PROCEDURE emp_sal_review IS
cursor get_employee IS
select emp_code,emp_ar_name,EMP_NAME ,EMP_RG_OFFICE ,EMP_RG_BRANCH ,EMP_DEP_code,EMP_JOB_code,basic_salary
from hr_employee
where emp_status='A'
and emp_rg_office=:rg_code
and emp_rg_branch=:rg_branch
or exists (select emp_code
from hr_emp_allowances
where hr_emp_allowances.emp_code = hr_employee.emp_code
or exists (select emp_code
from hr_emp_addition
where EMP_ADD_FDATE >=to_date( :d1,'dd-mm-yyyy')
and EMP_ADD_FDATE <= to_date(:d2,'dd-mm-yyyy')
and hr_emp_addition.emp_code = hr_employee.emp_code)
or exists( select emp_code
from hr_emp_deduction
where DED_DATE=to_date(:d1,'dd-mm-yyyy')
and hr_emp_deduction.emp_code=hr_employee.emp_code
)
)
ORDER BY TO_NUMBER(HR_EMPLOYEE.EMP_CODE);

A number:=0;
B number:=0;
C number:=0;
D number:=0;
E number:=0;
F number:=0;
G number:=0;
H number:=0;
TOT_ALLOW NUMBER:=0;
TOTAL_SALARY NUMBER:=0;
J number;
K number:=0;
L number:=0;
M number:=0;
N number:=0;
O number:=0;
P number:=0;
Q number:=0;
R number:=0;
S number:=0;
T number:=0;
BEGIN
delete from payroll_review1 ;
commit;
for i in get_employee loop

select
NVL(sum(EMP_TOT_VALUE),0),
NVL(sum(SALES_COMMISION),0),
NVL(sum(MANAGERIAL_BONUES),0),
nvl(SUM(COLLECTING_BONUSES),0),
nvl(SUM(MARKETING_BONUESE),0),
NVL(sum(COMPENSATION),0)
into J,K,L,M,N,O
from hr_emp_addition
where EMP_ADD_FDATE>=to_date(:d1,'dd-mm-yyyy')
and EMP_ADD_FDATE<=to_date(:d2,'dd-mm-yyyy')
and EMP_ADD_TDATE>=to_date(:d1,'dd-mm-yyyy')
and EMP_ADD_TDATE<=to_date(:d2,'dd-mm-yyyy')
and emp_code=i.emp_code;

select NVL(sum(MANAGMENT_ALLOWANCE),0),
NVL(sum(OTHERTASK_ALLOWANCE),0),
NVL(sum(OTHERS_ALLOWANCE),0),
NVL(sum(HOUSING_ALLOWANCE),0),
NVL(sum(MOBILE_ALLOWANCE),0),
NVL(sum(TRANSPORTATION_ALLOWANCE),0),
NVL(sum(TRAVEL_ALLOWNACE), 0),
NVL(sum(CAR_ALLOWANCE), 0)
into A,B,C,D,E,F,G,H --->
from hr_emp_allowances
where emp_code=i.emp_code;

TOT_ALLOW:=A+B+C+D+E+F+G+H;
TOTAL_SALARY:=TOT_ALLOW+I.BASIC_SALARY;


select NVL(sum(DED_AMT),0),
NVL(sum(LOANS_AMT),0),
NVL(sum(MANGERIAL_AMT),0),
NVL(sum(OTHERS_AMT), 0),
NVL(sum(DED_VALUE),0)
INTO P,Q,R,s,t
from HR_EMP_DEDUCTION
where DED_DATE >= to_date(:d1,'dd-mm-yyyy')
and DED_DATE<=to_date(:d2,'dd-mm-yyyy')
and emp_code=I.emp_code;


-- AND ADEMP_RG_BRANCH=:rg_code
-- AND ADEMP_RG_BRANCH=:rg_branch
--AND EMP_CODE IN (SELECT EMP_CODE FROM HR_EMPLOYEE WHERE HR_EMPLOYEE.EMP_CODE=hr_emp_addition.EMP_CODE)


insert into payroll_review1(EMP_CODE, EMP_AR_NAME, EMP_NAME, EMP_RG_OFFICE, EMP_RG_BRANCH, EMP_DEPT, EMP_JOB,basic_salary,
MANAGMENT_ALLOWANCE, OTHERTASK_ALLOWANCE, OTHERS_ALLOWANCE, HOUSING_ALLOWANCE, MOBILE_ALLOWANCE,
TRANSPORTATION_ALLOWANCE, TRAVEL_ALLOWNACE, CAR_ALLOWANCE,TOTAL_ALLAOWANCES,TOTAL_SALARY,
OVERTIME,SALES_COMMISION, MANAGERIAL_BONUES, COLLECTING_BONUSES, MARKETING_BONUESE, COMPENSATION,
DED_AMT, LOANS_AMT, MANGERIAL_AMT, OTHERS_AMT)
values(i.EMP_CODE, i.EMP_AR_NAME, i.EMP_NAME, i.EMP_RG_OFFICE, i.EMP_RG_BRANCH, i.EMP_DEP_code,i.EMP_JOB_code,i.basic_salary,
A,B,C,D,E,
F,G,H,TOT_ALLOW,TOTAL_SALARY,
J,K,L,M,N,O,
P,Q,R,S );

commit;
end loop;
END;