Flex Business Rules
Definition
Flex SQL statements serve to define validation rules that are specific to the organization. You can set up one or more statements to be processed for post-insert or -update events.
Flex SQL processing supports data query (select) and data manipulation (insert, update, delete) statements. Use select statements to perform a check condition.
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. 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).
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.
Set up a new Flex Rule
WARNING: This feature, if implemented incorrectly, could alter or delete data. Hexagon recommends that these features be enabled only by experts who have received training on these features.
To set up a new Flex Business Rule, follow the steps (see screenshots below):
- Administration > Screen Configuration > Flex Business Rules
- New Record ([+] icon)
- "Table": Specify the database table, e.g. R5OBJECTS
- "Sequence Number": Specify the sequence number for the Flex SQL statement.
- "Trigger": Select one of the following options:
- Post Insert: the flex rule will be trigger by a new record in the selected table (e.g. new entry in R5OBJECTS)
- Post Update: the flex rule will be trigger by a new update of an existing record in the selected table (e.g. any field of a record in R5OBJECTS)
- "SQL Statement": Specify the SQL statement for the selected database table.
- "Failure Message": Specify the failure message.
- "Comments" Specify comments regarding the Flex SQL statement, such as a brief explanation on the purpose of the flex rule.
- Optionally, select the "Must Exist" checkbox if the Flex SQL statement must exist in the database table.
- If "Must Exist" is selected, SQL statements are run in order of Sequence Number. If one statement fails, the remainder do not run.
- If "Must Exist" is not selected, each statement runs separately.
- Optionally, select the "Abort on Failure" to abort the statement upon failure.
- If "Abort on Failure" is checked, the Failure Message field value is returned, any remaining SQL statements are not processed, and the save that triggered the flex SQL is cancelled.
- Optionally, select the "Reverse Return Code" checkbox to automatically reverse the returned value upon statement completion.
- i.e., Null becomes Not Null. Reverse Return Code is used only with Abort on Failure.
- Click on "Test Flex Business Rule" to make sure that the SQL statement is valid.
- If the test is successful, a message appears "The SQL statement is OK.".
-
Note: a successful test does not imply a "correct" SQL statement that fulfills the business logic. It only means that the statement is "grammatically" valid. A valid statement can still damage the database integrity.
- If the statement is not valid, an error message appears, indicating the type of error, e.g.: "The SQL Statement is invalid: ORA-00900: invalid SQL statement."
- Select the "Active" checkbox to set this Flex SQL statement record to active; the Flex Rule is inactive as long as the checkbox is not selected.
- Click "Save Record" (floppy disc icon).
Note: Records flagged as "Mobile Only" cannot be deleted on this screen. You must delete them on the mobile device. Click Test Flex SQL to check the validity of the SQL statement.
Deactivate a Flex Rule
To make a Flex Rule inactive, follow the steps (see screenshot below):
- Administration > Screen Configuration > Flex Business Rules
- Select the Flex Rule to deactivate
- Clear the "Active" checkbox to set this Flex SQL statement record to inactive.
- Click "Save Record" (floppy disc icon).
Delete a Flex Rule
To delete a Flex Rule, follow the steps (see screenshot below):
- Administration > Screen Configuration > Flex Business Rules
- Select the Flex Rule to delete
- Click "Delete Record" (litter bin icon).
Currently active Flex Rules in Hexagon
The following flex rules are active in Hexagon as of May 2025. The following table describe the table involved, the associated trigger as well as its sequence number and description.
Table
Sequence Nb.
Trigger
Description or goal
R5ALERTGRIDDATA
15
Post Insert
Update TrackUnit data for each time zone
R5ALERTGRIDDATA
20
Post Insert
Insert Depreciation records when the monthy process is executed.
R5ALERTGRIDDATA
25
Post Insert
Add R5READINGS into R5TRACKINGDATA for Location (airport)
R5ALERTGRIDDATA
40
Post Insert
TrackUnit Notifications table, to record erroneous or strange meter data
R5ALERTHISTORY
10
Post Insert
Send information to Smartfleet
R5AUDVALUES
10
Post Insert
Update Smartfleet GSE (Manual Update)
R5CONTRACTCHARGEDEFS
10
Post Insert
Fill GSE details in Charge defs after Insert
R5CONTRACTCHARGEDEFS
10
Post Update
Fill GSE details in Charge defs after Update
R5CONTRACTITEMS
111
Post Insert
Fill GSE parameters on RES line and basic data after conversion to SALE
R5CONTRACTITEMS
112
Post Insert
GSE Components: Insert record into table U5CONTRACTCOMPONENTS after insert record in table R5CONTRACTITEMS, when not exist. Compute number of referential lines.
R5CONTRACTITEMS
113
Post Insert
Delete the record of the purchased GSE item, if it exists, after entering it into the items
R5CONTRACTITEMS
114
Post Insert
Display error message if GSE has wrong status
R5CUSTOMERCONTRACTS
10
Post Insert
Update Customer Description on Contract page -- used to show on Dataspy (List of Contracts)
R5CUSTOMERCONTRACTS
20
Post Update
Update Customer Description in Contract page CCT_UDFCHAR20 is Cust Desc
R5CUSTOMERCONTRACTS
30
Post Insert
Update User Code in Contract Creation CCT_UDFCHAR28 is "Created by"
R5CUSTOMERCONTRACTS
40
Post Update
Prevent approval of contract by the Person who created the contract (4-Augen Prinzipiell) only for user group GSE_AC - also applies to Sale Contracts
R5CUSTOMERCONTRACTS
50
Post Insert
Copy-paste the list of purchased GSE during RES revision (nRevision > 0)
R5CUSTOMERCONTRACTS
111
Post Insert
GSE reservations: Creation Date + dummy charge def.; Sale contracts: address + Start date
R5CUSTOMERCONTRACTS
112
Post Update
GSE reservations: Convert reservation to contract. Add option to convert RES into SALE contract
R5CUSTOMERCONTRACTS
113
Post Insert
DELETE Delivery Notes, if creates new revision on Customer or Sale contract
R5CUSTOMERCONTRACTS
113
Post Update
Generate Delivery Notes depending of instalation parameter GENDNTYP
R5CUSTOMERCONTRACTS
114
Post Update
Update GSE data of active GSE based on RES and SALE
R5CUSTOMERCONTRACTS
115
Post Update
Sale contracts: GSE data modification. Find Customer Description and display on GSE page
R5CUSTOMERCONTRACTS
118
Post Update
Update GSE data of active GSE during SALE
R5CUSTOMERCONTRACTS
119
Post Update
Delete Purchased Items if rental Contract is finished
R5CUSTOMERCONTRACTS
122
Post Update
Prevent editing of RES after conversion to CCT; if Status is U, conversion already done
R5CUSTOMERCONTRACTS
122
Post Insert
Prevent editing of RES after conversion to CCT; if Status is U, conversion already done
R5CUSTOMERCONTRACTS
123
Post Update
Prevent CCT status RA from being selected if cit_tax of items do not match cct_tax
R5CUSTOMERCONTRACTS
124
Post Update
Prevent completion of SALE contract if unfinished DN
R5CUSTOMERINVOICEDETAILS
20
Post Insert
Flag Invoice Details Record so the Monthly process only considers these new lines.
R5CUSTOMERINVOICEDETAILS
112
Post Insert
Insert four new GSE Object fields from r5objects into r5customerinvoicedetails
R5CUSTOMERINVOICES
20
Post Update
Compare invoice lines of the latest rev with the rev 0 when when invoice rev is > 0
R5CUSTOMERINVOICES
30
Post Update
Calculate the diff of two invoice revisions.
R5EQUIPCONFIGSESSIONS
100
Post Update
Updater asset_on equipment generation
R5EQUIPCONFIGSESSIONS
101
Post Update
Update assets serial-no from R5ECSESSIONEQUIPMENT
R5OBJECTS
15
Post Update
Update GSE status, PO status, commision
R5OBJECTS
20
Post Insert
Create meters for GSE -> OH1, OH1-2; except for unmotorised Classes
R5OBJECTS
33
Post Update
1. Remove connection GSE-TU when Detach TU Box is clicked inside GSE screen by setting the respective association fields to NULL 2. Create connection GSE-TU when TU Box is attached by updating GSE number inside the TU screen
R5OBJECTS
40
Post Insert
Update Customer Description based on Customer-ID on GSE page
R5OBJECTS
45
Post Update
Fill an empty OBJ field with PO NUMBER -- extract PO number from field OBJ_ORDER which is invisible -- then we can create a hyperlink to POs
R5OBJECTS
50
Post Update
Update Customer Description based on Customer-ID on GSE page
R5OBJECTS
60
Post Update
Update TU data upon request when checkbox is checked then uncheck the checkbox once finished
R5OBJECTS
100
Post Update
Retrieve GSE Data from Smartfleet upon request when checkbox is checked then uncheck the checkbox once finished
R5ORDERS
10
Post Update
GSE status Chnge based on PO status
R5PROPERTYVALUES
10
Post Insert
Impacts based on property value of AB Accepted changes
R5STRUCTURES
10
Post Insert
Remove GSE parent in structure if S
R5TRACKINGDATA
10
Post Insert
What is this Flex?? u5hileas contains only data regarding leasing of GSE
R5TRACKINGDATA
20
Post Insert
Update Smartfleet Station ID
R5TRACKINGDATA
5001
Post Insert
TrackUnit-Process TrackUnit readings for GSE
R5TRACKINGDATA
5002
Post Insert
Calling the Track Unit Airport update Dynamic SQL
U5CONTRACTPURCHITEMS
1
Post Insert
U5CONTRACTPURCHITEMS
2
Post Insert
Update GSE data for purchased GSE in active RES
U5CUSTOMERINFLATIONS
10
Post Insert
Compute Inflation and Insurance AGM 08.05: also modify freeupto and maxcharge
U5DELIVERYNOTES
111
Post Update
Sale contracts: GSE status + withdrawal Date + others after DN signing
U5DELIVNOTELINES
10
Post Insert
Fill in DN line data by Insert: Manufact, Weight, etc
U5INVOICEADDCOSTS
1
Post Update
U5INVOICEADDCOSTS
1
Post Insert
Prevent adding additional costs to PO Vouchers when it has been matched
U5ORDERADDCOSTS
1
Post Update
Prevent adding additional costs to PO when it is approved, Awaiting approval or rejected.
U5ORDERADDCOSTS
1
Post Insert
Prevent adding additional costs to PO when it is approved, Awaiting approval or rejected.
U5USERSEQUENCES
1
Post Update
Create MS SQL Sequence as Database Object
U5USERSEQUENCES
1
Post Insert
Create MS SQL Sequence as Database Object, user must have database GRANT to create database object