1 Reply Latest reply: Dec 7, 2007 5:01 PM by Mark Striekwold RSS

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

    512181
      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
      ================================================
      BEGIN
      -- 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
      BEGIN
      select round(amount_base,ret_rec.own_dec)
      ,round(amount_fc,ret_rec.bal_dec)
      into v_bal_base
      ,v_bal_fc
      from csh_balances
      where gla_id = ret_rec.gla_id
      and period = p_period
      and own_id = p_own_id;
      v_ins := 0;

      EXCEPTION
      WHEN NO_DATA_FOUND THEN v_bal_fc := 0;
      v_bal_base := 0;
      v_ins := 1;
      END;
      -- 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))
      ,sum(round(entered_cr,ret_rec.bal_dec))
      ,sum(round(accounted_dr,ret_rec.own_dec))
      ,sum(round(accounted_cr,ret_Rec.own_dec))
      into v_trn_dr_fc
      ,v_trn_cr_fc
      ,v_trn_dr_base
      ,v_trn_cr_base
      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(
      OWN_ID
      ,GLA_ID
      ,MONTH_END
      ,AMOUNT_BASE
      ,AMOUNT_FC
      ,CONVERSION_RATE
      ,PERIOD_STATUS
      ,PERIOD)
      values(
      p_own_id
      ,ret_rec.gla_id
      ,v_date
      ,v_end_bal_base
      ,v_end_bal_fc
      ,v_rate
      ,'C'
      ,p_period);
      else
      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'; */
      csh_gl_pkg.csh_upd_leh_close(p_own_id,p_period);
      END;
      ================================================