0 Replies Latest reply: Nov 19, 2012 3:55 AM by yasser_egypt RSS

    forms 6i problem in procedures for make update

    yasser_egypt
      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;