2 Replies Latest reply: Nov 21, 2012 9:51 PM by rthampi RSS

    Developer 10g, COMMIT issues

    rthampi
      Hi guys
      We have developed one particular application for ORACLE EBS R12 Order Management using Oracle public APIs. This custom application saves the user fed data into local tables first and then calls database level packages and procedures to facilitate Order Booking.
      We are using this application from last one year, and throughout the year we have created 10,000+ transactions. Unfortunately, while reconciling the transactions we found four transactions where missing from the custom application tables, however the Order Management side tables populated with the same missing information.

      Order booking is done by a dedicated button click and the sample coding is something like following for WHEN-BUTTON-PRESSED
      Begin
      Commit_form;
      --Call first procedure;
      stage_1(:header_blk.order_number);
      stage_2(:PARAMETER.STAGE_1_SUCCESS);
      End;
      Commit_form call is supposed to save the transaction on the data entry screen first to the custom application table(s) and then invoke the procedures as listed in the coding. However in our case, the consecutive procedures are reading the value from form (:header_blk.order_number) and gone ahead with processing the data over Order management repositories for missing transactions!!! (user's identified these were the cases when there were network failures, client systems hanging up, java crashing etc)


      As an immediate resolution, we changed the coding like following
      We created a view against the header table and checked whether the order_number is available in the view prior calling the procedures. Sample coding
      Declare
      l_trx_check VARCHAR2(1);
      Begin
      Commit_form;
      Begin
      Select 'X' into l_trx_check from order_hdr_id_v
      where order_number = :header_blk.order_number;
      Exception
      when no_data_found then
      message('Local transaction not saved. Please try again');
      raise form_trigger_failure;
      End;
      --Call first procedure;
      stage_1(:header_blk.order_number);
      stage_2(:PARAMETER.STAGE_1_SUCCESS);
      End;
      Now my query is whether there is a better approach towards this requirement and whether depending upon a view for a cross verification is really a practical solution.

      regards,

      raj
        • 1. Re: Developer 10g, COMMIT issues
          Andreas Weiden
          I would check if you could use a POST instead of a COMMIT_FORM, so that all your actions are done in the same transaction. With your coding there is the chance that the first commit is excuted, but then the form craches in the afterwards processing.
          • 2. Re: Developer 10g, COMMIT issues
            rthampi
            Andreas Weiden wrote:
            I would check if you could use a POST instead of a COMMIT_FORM, so that all your actions are done in the same transaction. With your coding there is the chance that the first commit is excuted, but then the form craches in the afterwards processing.
            Thank you Andreas
            Unfortunately in our case, the exact opposite is happening. The first commit is not happening, however other commits through called procedures are happening at the database level.

            More thoughts please???

            regards,

            raj