Defining Flex business rules statements and procedures - HxGN EAM - 12.0 - Administration & Configuration

HxGN EAM Databridge System Administrator Guide

Language
English
Product
HxGN EAM
Search by Category
Administration & Configuration
HxGN EAM Version
12

Define Flex SQL statements to define validation rules that are specific to your organization. You can set up one or more statements to be processed for post-insert or post-update events.

Flex SQL processing supports data query (select) and data manipulation (insert, update, delete) statements. Use select statements to perform a check condition.

Uppercase and lowercase characters are allowed in the SQL statement. The row identifier token, however, must be either all uppercase or all lowercase (i.e, :ROWID or :rowid).

Every Flex SQL statement requires the use of a predefined :ROWID token. This token refers to the database row identifier for the record being processed in the specified table. The statement is executed for each record in the table affected by the insert or update operation.

The maximum statement length is 4000 characters. No statement termination character (;) is required.

Data manipulation statements are allowed, but make sure you do not begin an infinite cascading of trigger steps. For example, generally it is not recommended to create a Flex SQL statement that updates the base table identified in Table.

We recommend that you define Flex SQL procedures in close cooperation with your HxGN EAM consultant.

  1. Select Administration > Screen Configuration > Flex Business Rules.

  2. Specify the Table for which to define Flex SQL statements and procedures.

  3. Click Add Flex SQL.

  4. Specify this information:

    Sequence Number - Specify the order of Flex SQL processing.

    Trigger - Specify the database operation that initiates the event. Post-insert and post-update triggers are supported.

    SQL Statement - Specify the SQL statement to be executed when the specified operation takes place for a table.

    Failure Message - Specify the message to display when the Flex SQL statement fails. A Flex SQL statement is successful when the statement processes one or more rows in a table. A Flex SQL statement fails when no rows are processed. To make the failure message active, select Abort on Failure.

    Comments - Specify a description of the purpose of the Flex SQL statement.

  5. Optionally, select the Must Exist check box to make the Flex SQL processor stop processing consecutive Flex SQL statements when the current statement processes no rows.

  6. Optionally, select the Abort on Failure check box to stop the current operation when it fails and issue an error.

  7. Optionally, select the Reverse Return Code check box to reverse the return status code for the current Flex SQL statement. If selected and the statement processes one or more rows, then the processor returns with a failure status. If the statement processes no rows, then the processor returns a success status.

  8. Optionally, select the Active check box to enable the Flex SQL statement.

  9. Click Test Flex SQL to ensure that the current Flex SQL statement is valid. If the statement contains errors (invalid syntax, for example), a message is displayed.

  10. Click Submit.

    See the following examples of Flex SQL definitions:

    Example 1 - Specify check statements or edits that supplement standard HxGN EAM processing. For example, entering a value for cost code is optional when creating a requisition in HxGN EAM. If the implementation depends on the use of cost code to derive account segment values, you must define a check statement that does not allow updating or insertion of requisitions without entering a value for cost code.

    See the following example of a check statement to supplement the standard processing of the creation of a requisition in HxGN EAM:

    • Table - Specify R5REQUISITIONS.

    • Trigger - Specify POST-INSERT.

    • Sequence Number - Specify 10.

    • SQL Statement - Specify the following SQL statement to be executed:

      SELECT NULL
      FROM R5REQUISITIONS
      WHERE ROWID=:ROWID
      AND REQ_COSTCODE IS NOT NULL

    • Failure Message - Specify Please enter a value for cost code.

    • Select Must Exist.

    • Select Abort on Failure.

      Example 2 - Create rows for entries and transactions to interface with Oracle Financials in the R5ACCOUNTDETAIL table in HxGN EAM on the Flex SQL screen.

      See the following example of creating rows for account detail entries using constant default values. This statement follows the check statement in the previous example for entering values for cost code when creating requisitions. The sequence number identifies the order in which the Flex SQL statements are executed.

    • Table - Specify R5REQUISITIONS.

    • Trigger - Specify POST-INSERT.

    • Sequence Number - Specify 20.

    • SQL Statement - Specify the following SQL statement to be executed:

      INSERT INTO R5ACCOUNTDETAI
      (ACD_CODE, ACD_RENTITY, ACD_SEGMENT1, ACD_SEGMENT2, ACD_SEGMENT3,
      ACD_SEGMENT4)
      SELECT REQ_ACD, 'REQ',
      '01',
      '000',
      '000',
      '0000'
      FROM R5REQUISITIONS
      WHERE ROWID=:ROWID

    • Failure Message - Specify Error creating account detail.

    • Select Must Exist.

    • Select Abort on Failure.

    • Select Active.

      The example above illustrates the requisition reference to R5ACCOUNTDETAIL for which REQ_ACD is already populated. HxGN EAM automatically generates the requisition account detail key. You do not need to create the account detail key value with Flex SQL.

      Determining the Flex SQL statement definitions to use in HxGN EAM for account details depends on which HxGN EAM and ERP modules are integrated. Begin by outlining the entire procurement process. Then work with each transaction set to determine the need for validation and the generation of accounts.

      These needs are unique not only to each integration scenario but also to each customer within any scenario. Therefore, it is extremely important to make sure that you have considered the entire process very carefully.