This discussion is archived
0 Replies Latest reply: Nov 19, 2012 1:55 AM by yasser_egypt RSS

forms 6i problem in procedures for make update

yasser_egypt Newbie
Currently Being Moderated
dear sirs,
i had procedure to make update for tree of accounts, but make update to some levels and other level no, and here is the procedure if inside it any problem pls advice me
-----
CREATE OR REPLACE PROCEDURE updup_acc_trbal2(
wyear IN NUMBER ,
wperiod IN NUMBER ,
wcost_center IN VARCHAR2 ,
wacc_no IN VARCHAR2,
wcurr_code IN VARCHAR2 ,
lval_db IN NUMBER ,
lval_cr IN NUMBER ,
val_db IN NUMBER ,
val_cr IN NUMBER )
IS
BEGIN
declare
------------acc----
acc_a varchar2(15);
acc_b varchar2(15);
cost_a varchar2(15);
cost_m varchar2(15);
cursor c0 is
select a1,a2,a3,a4,a5,a6,a7,a8 from acc_master_file
where acc_no = wacc_no;
type acc_arr0 is table of varchar2(15)
index by binary_integer;
arr_acc acc_arr0;
i binary_integer:= 0;
-------------cost----
cursor c is
select c1,c2,c3,c4,c5,c6,c7,c8,c9 from cost_tables
where cost_center = wcost_center;
type acc_arr is table of varchar2(15)
index by binary_integer;
cost_acc acc_arr;
i_cost binary_integer:= 0;
begin
open c0;
fetch c0 into arr_acc(1),arr_acc(2),arr_acc(3),
arr_acc(4),arr_acc(5),arr_acc(6),
arr_acc(7),arr_acc(8);
close c0;

open c;
fetch c into cost_acc(1),cost_acc(2),cost_acc(3),
cost_acc(4),cost_acc(5),cost_acc(6),
cost_acc(7),cost_acc(8),cost_acc(9);
close c;

i := 0;
i_cost := 0;
loop
i_cost := i_cost + 1;
exit when (i_cost > 9);
loop
i := i + 1;
exit when (i > 8);
acc_a := arr_acc(i);
cost_a := wcost_center;--cost_acc(i_cost);
cost_m := cost_acc(i_cost);
acc_b := wacc_no;
if cost_m is not null then
if acc_a is not null then
if acc_a <> wacc_no then --v then
if cost_m <> wcost_center then
update cost_bal
set tr_deb = nvl(tr_deb,0) + nvl(lval_db,0),
tr_cr = nvl(tr_cr,0) + nvl(lval_cr,0),
c_tr_deb = nvl(c_tr_deb,0)+ nvl(val_db,0),
c_tr_cr = nvl(c_tr_cr,0) + nvl(val_cr,0)
where year = wyear
and period_no = wperiod
and acc_no = acc_a
and cost_center = cost_m wcost_centercost_a
and ((curr_code = wcurr_code
and wcurr_code is not null)
or(curr_code is null
and wcurr_code is null));
if sql%notfound then
insert into cost_bal
(year,period_no,cost_center,acc_no,curr_code,
op_deb,op_cr,c_op_deb,c_op_cr,
tr_deb,tr_cr,c_tr_deb,c_tr_cr,
created_by,created_date,
changed_by,changed_date)
values
(wyear,wperiod,cost_m,acc_a,wcurr_code,
0,0,0,0,
nvl(lval_db,0),nvl(lval_cr,0),nvl(val_db,0),
nvl(val_cr,0),user,sysdate,null,null);
end if;
end if;
end if;
end if;
end if;
end loop;
end loop;
end;
END updup_acc_trbal2;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points