0 Replies Latest reply on Mar 1, 2017 10:48 AM by Mussa

    Unable to add items to cart in oracle iprocurement home page because of a customized trigger

    Mussa

      I am using oracle EBS R12.1.3 , 11.2.0.3 g database.

       

       

       

      Whenever I add item to cart (often more than one item),then I face the following error:

      Unable to perform transaction on the record.
      Cause: The record contains stale data.  The record has been modified by another user.
      Action: Cancel the transaction and re-query the record to get the new data.

       

      I am attaching the screenshot at the end of this post to demonstrate where the error is produced.

       

      This error occurs after I have created a customized trigger to update the column "need_by_date " in the table " PO.PO_REQUISITION_LINES_ALL"

      I don't want to disable this trigger or drop it. I need it to do the update on a column...

       

      The trigger code is as following:

       

      create or replace TRIGGER PO.RIC_Due_Date_Update  -- to update Need Date based on UOM
      
      BEFORE INSERT -- OR UPDATE
      
      ON PO.PO_REQUISITION_LINES_ALL
      
      REFERENCING NEW AS new OLD AS old
      
      FOR EACH ROW
      
      DECLARE
      
      Due_date      PO.PO_REQUISITION_LINES_ALL.NEED_BY_DATE%Type;
      
      Qty PO.PO_REQUISITION_LINES_ALL.QUANTITY%Type;
      
      UOM PO.PO_REQUISITION_LINES_ALL.UNIT_MEAS_LOOKUP_CODE%Type;
      
      BEGIN  
      
      Qty := :new.QUANTITY;
      
      Due_date := :New.NEED_BY_DATE;
      
      UOM := :New.UNIT_MEAS_LOOKUP_CODE;
      
        If  UOM ='EA' then
      
        BEGIN
      
          :new.NEED_BY_DATE := Due_date+90; 
      
          EXCEPTION
      
           WHEN OTHERS THEN
      
             RAISE;
      
         end;
      
         end if;
      
          If  UOM ='MOU' then
      
        BEGIN
      
          :new.NEED_BY_DATE := Due_date+(Qty * 30); 
      
          EXCEPTION
      
           WHEN OTHERS THEN
      
             RAISE;
      
         end;
      
         end if;
      
          If  UOM ='Year' then
      
        BEGIN
      
          :new.NEED_BY_DATE := Due_date+(Qty * 365); 
      
          EXCEPTION
      
           WHEN OTHERS THEN
      
             RAISE;
      
         end;
      
         end if;
      
      end;
      

       

       

      unable to add to cart.png

       

      Kindly, help how to update that column in a way it doesn't affect the functionality as shown in the attached file! Would be much appreciated!