1 Reply Latest reply on Dec 7, 2007 11:01 PM by Mark Striekwold

    Urgent please -- got error while doing a simple update -- Thanks:)

      Hi guys,

      I have a procedure that updates a table, when I run the update manually from sqlplus it works and when I run the procedure by it self from sqlplus it also works the problem comes when I call that procedure from another procedure that I have I get the following error:

      ERROR at line 1:
      ORA-20998: Transaction Failed
      ORA-06512: at "HSO.QMS$ERRORS", line 128
      ORA-06512: at "HSO.QMS_TRANSACTION_MGT", line 900
      ORA-06512: at "IAIGC.CG$AUS_CSH_LEDGER_HEADER", line 115
      ORA-04088: error during execution of trigger 'IAIGC.CG$AUS_CSH_LEDGER_HEADER'
      ORA-06512: at "IAIGC.CSH_GL_PKG", line 7634
      ORA-06512: at "IAIGC.CSH_GL_PKG", line 266
      ORA-06512: at line 1

      This procedure was working fine until I took an export of my production dB and import it in my test database to test a fix in a diff procedure, here is my code
      -- Get the Last day in the period
      v_date := last_day(to_date(p_period,'RRRRMM'));

      -- delete the trail revaluation etries from the detail
      delete from csh_v_ledger_detail
      where leh_period = p_period
      and leh_own_id = p_own_id
      and leh_jv_type in ('PR','RV');

      -- delete the trial revaluation etries from the header
      /*delete from csh_v_ledger_HEADER
      where period = p_period
      and own_id = p_own_id
      and jv_type in ('PR','RV');

      -- adjust difference between debit and credit in jvs
      -- update balances
      FOR ret_rec IN c_gen_mon_bal(v_date) LOOP
      -- initialize balances for each account
      v_bal_base := 0;
      v_bal_fc := 0;
      v_end_bal_fc := 0;
      v_end_bal_base := 0;
      v_trn_dr_fc := 0;
      v_trn_cr_fc := 0;
      v_trn_dr_base := 0;
      v_trn_cr_base := 0;
      v_ins := 0;
      -- Get Beginning Balances for the period from the
      -- csh_balances table
      select round(amount_base,ret_rec.own_dec)
      into v_bal_base
      from csh_balances
      where gla_id = ret_rec.gla_id
      and period = p_period
      and own_id = p_own_id;
      v_ins := 0;

      WHEN NO_DATA_FOUND THEN v_bal_fc := 0;
      v_bal_base := 0;
      v_ins := 1;
      -- Get the sum of all debit and credit transaction
      -- from the transactions table for the current month
      select sum(round(entered_dr,ret_rec.bal_dec))
      into v_trn_dr_fc
      from csh_v_ledger_detail
      where gla_id = ret_rec.gla_id
      and to_char(leh_value_date,'RRRRMM') = p_period
      and leh_own_id = p_own_id
      and leh_closed_flag = 'N';

      -- Add Balances for the month to all the transactions
      v_end_bal_fc := nvl(v_bal_fc,0) - nvl(v_trn_dr_fc,0) + nvl(v_trn_cr_fc,0);
      v_end_bal_base := nvl(v_bal_base,0) - nvl(v_trn_dr_base,0) + nvl(v_trn_cr_base,0);

      -- Get the exchange rate for the month
      v_rate := csh_acc_pkg.csh_get_rate(p_own_id,ret_rec.cur_id,v_date,'ACC');

      if v_ins = 1 then
      insert into csh_balances(
      update csh_balances
      set amount_fc = v_end_bal_fc,
      amount_base = v_end_bal_base,
      period_status = 'C',
      conversion_rate = v_rate
      where gla_id = ret_rec.gla_id
      and period = p_period
      and own_id = p_own_id;
      end if;
      END LOOP;
      /* update csh_v_ledger_header
      set closed_flag = 'Y'
      where own_id = p_own_id
      and period = p_period
      and closed_flag = 'N'; */