Implementing Transaction Processing Using System Function in Batch Application

Purpose of Document

This document is describes how Transaction Processing is implemented in EnterpriseOne Batch Application using Transaction Processing System Functions like Begin Transaction, Commit Transaction and Rollback Transaction.

Table of Contents

  • Transaction Processing System Functions
  •     Begin Transaction
  •     Commit Transaction
  •     Rollback Transaction
    •
  • System Variable: SV TP Commit Status
  • Enabling Transaction Processing
  • Example of Transaction Processing Implementation

    • Transaction Processing System Functions

    EnterpriseOne uses the following systems functions for transaction processing in batch application:

    Transaction Processing System Functions

     

    Begin Transaction

    Call the Begin Transaction system function to start a manual transaction. Calling this system function initiates the beginning of the table updates that are part of the transaction. This system function has one MathNumeric type parameter named Transaction ID. You can use a Report Level Math Numeric variable to pass into the Transaction ID parameter. Even though you must pass in a value for Transaction ID, the value is not currently used by the system. This is because at any one time, only one transaction can be started from Event Rules.

    After calling the "Begin Transaction" system function, the report can use Table I/O statements or in most standard E1 batch applications which use Master Business Functions (MBF) to conduct database table operations through cache usage. Please see <Document 1265902.1> - E1: BSFN: Master Business Function in EnterpriseOne.

    To include any Table I/O statements in the current transaction:

    1. Click the system function you have assigned to the event rule.
    2. Click the Table I/O icon.
    3. On Insert Table IO, in the Advanced Operations section, select Open, and then click Next.
    4. On Data Source, click the Advanced Options button.
    5. On Advanced Options, click the Include in Transaction option, and then OK.

     Include Table I/O statements in current transaction

    To include Business Function call in current transaction, select the "Include in Transaction" checkbox on the business function call.

     Include BSFN in transaction

    Commit Transaction

    Call the Commit Transaction system function to commit all the database operations started since the Begin Transaction call. Similar to Begin Transaction, this system function takes in a Transaction ID parameter. You can use the same variable as the one used in Begin Transaction system function.

    EnterpriseOne will commit all database operations started between the Begin Transaction call and the Commit Transaction call. If the Commit Transaction fails, EnterpriseOne automatically rolls back the database operations so that none of the database operations will be committed. There is no need for Event Rules (ER) to call Rollback Transaction after Commit Transaction.

    You need to carefully define the location of the Begin Transaction and Commit Transaction calls. The transaction boundary must be just big enough to include all relevant database operations and not too large that it blocks other code from accessing the same database records.

    The following example shows how to use transaction processing Commit Transaction system function through Event Rules:

    // Start Example
    Begin Transaction (transaction ID)
     
    Table IO_Open Table 1
     
    Table IO_Insert Table 1
     
    Table IO_Open Table 2
     
    Table IO_Open Table 2
     
    Commit Transaction (transaction ID)
    // End Example

    Rollback Transaction

    Call this system function to cancel all the database operations started since the Begin Transaction. This system function takes a Math Numeric type parameter called Transaction ID. Use the same variable as used in Begin Transaction. EnterpriseOne will end the transaction without committing any database operations started between the Begin Transaction and Rollback Transaction calls. You should only use this system function when absolutely necessary.

    System Variable: SV TP Commit Status

    The system variable "SV TP Commit Status" tells you the status of the Commit Transaction or Rollback Transaction call. It may contain one of the four valid values:

     
    Valid Values Description
    CO TP_ACTION_FAIL If SV TP Commit Status is equal to CO TP_ACTION_FAIL, then the last transaction action has failed. The last transaction action can be either Commit Transaction or Rollback Transaction. If the last transaction is Commit Transaction then the Commit Transaction has failed and no database operation has been committed. If the last transaction is Rollback Transaction, then the rollback operation has failed. No database records have been committed.
    CO TP_ACTION_SUCCESS If SV TP Commit Status is equal to CO TP_ACTION_SUCCESS, then the last transaction action has succeeded. The last transaction action can be either Commit Transaction or Rollback Transaction. If the last transaction is Commit Transaction, then the commit has succeeded and all database operations have been committed. If the last transaction is Rollback Transaction, then the rollback operation has succeeded.
    CO TP_IN_TRANSACTION  If SV TP Commit Status is equal to CO TP_IN_TRANSACTION, then the transaction has been started, but no Commit Transaction or Rollback Transaction system function has been called yet.
    CO TP_NO_TRANSACTION  If SV TP Commit Status is equal to CO TP_NO_TRANSACTION, then the no transaction has not been started or completed by the Transaction Processing system functions.

    Event Rules Sample Using System Variable TP_Commit_Status

    Begin Transaction (transaction ID)
     
    Table IO_Open Table 1
     
    Table IO_Insert Table 1
     
    Table IO_Open Table 2
     
    Table IO_Open Table 2
     
    Business function inserts into Table 3 in a separate transaction.
     
    Commit Transaction (transaction ID)
     
    if (SV TP_Commit_Status is equal to CO TP_ACTION_FAIL)
    {
            // this means the transaction failed (system has rolled it back)
    // call another Business Function to roll back Table 3 since Table 3 Insert
    // is done outside of current transaction
    }
    else
    {
            // notify user transaction successful

    Enabling Transaction Processing

    To enable Transaction Processing:

    1. Open the report for which you want to use transaction processing in Report Design Aid. (Note: this can be enabled on the template level or on the version level)
    2. Click the File menu, select Report Properties, and then select the Advanced tab. 
    3. Ensure that the Transaction Processing option is selected.

       Enabling Transaction Processing in RDA

    Example of Transaction Processing Implementation

    The following shows you a typical implementation of Transaction Processing in E1 standard batch applications.

    1.  Call the business function RetrieveProcessAndTransactionID (B0001060, Retrieve Process and Transaction IDs) to get mnProcessID which is returned through API jdeGetPid().
          Retrieve Process and Transaction IDs
              VA sec_TransactionID_TCID <- BF mnProcessID
         
         NOTE: Currently, a single batch job can handle only one transaction.

    2. Call Master Business Function(MBF) Begin Doc (only once - commonly at the header level)

    3. Call (MBF) Edit Line (Commonly at Do Section event, this is to default, validate, and create cache)

    4. Call the system function Begin Transaction(VA sec_TransactionID_TCID) - this system functions sets the Transaction Processing boundaries. Any Data Manipulation Language (DML) statement in the following End Doc routine will be written into redo log file (or Journal for DB2)

    5. Call (MBF) End Doc business function - this commits the cache created from Edit Line

    6. Check the error status of End Doc which commits cache created through Begin Doc or Edit Line to the database

    If SV Error_Status is equal to CO SUCCESS
                Commit Transaction(VA sec_TransactionID_TCID)
    Else
                Rollback Transaction(VA sec_TransactionID_TCID)
    End If

    Note: