Understanding Flex business rules

Flex SQL is an application method to create database triggers (post-insert or post-update) to validate, insert, or update all Infor EAM database objects. Flex SQL is used manually through the definition of SQL*Plus or PL/SQL statements. The primary function of these statements is to create and update account details for interface transactions, validate transactions for business process compliance, and customize Infor EAM to meet the needs of your organization.

Specifically, creating and updating account details is required for any transaction originates in Infor EAM that will be interfaced to the external (third-party) application. See these topics:

Defining account detail requirements for Oracle General Ledger

Defining general ledger processes

Defining general ledger references

Flex SQL must be written in SQL*Plus or PL/SQL syntax. SQL*Plus is Oracle's tool for issuing database-level programming commands, and it is based on Structured Query Language (SQL). SQL enables you to insert, update, query, or delete database records with a simple language syntax. SQL statements begin with a command word, followed by a specification of what information to select within the command, followed by the object from which to select the information. The query is generally terminated with a semicolon (;). For the purposes of Flex SQL usage, the semicolon (;) terminator should always be left out. The syntax is dynamically compiled and executed so that the terminator is not required.

See the following example of syntax:

insert into r5accountdetail
                      (acd_code, acd_rentity, acd_segment1, acd_segment2)
                     select rql_acd, 'REQL', '01', rql_expensetype
                     from r5requislines
                      where rowid = :rowid

Flex SQL also utilizes a second type of structured query language called Programming Language/Structured Query Language (PL/SQL). PL/SQL is used to process commands in blocks, rather than using individual SQL statements. Blocks are groups of related SQL statements that can be nested inside larger blocks, which allows you to organize your SQL syntax to perform several commands at once.

See the following example of PL/SQL syntax:

DECLARE
                        cpar r5parts.par_code%type;
                        cref r5catalogue.cat_ref%type;
                       csupp r5companies.com_code%type;
                       cursor rql is
                       select rql_ref, rql_part, rql_supplier, rql_rstatus
                       from r5requislines
                       where rowid = :rowid;
                  BEGIN
                    OPEN rql;
                    FETCH rql into cref, cpar, csupp, crstat;
                    CLOSE rql;
                    IF cref is not null
                      AND crstat = 'A' THEN
                    UPDATE r5catalogue
                      SET cat_ref = cref
                      WHERE cat_part = cpar
                        AND cat_supplier = csupp;
                    END IF;
                  END;

Both the SQL*Plus and the PL/SQL examples would be placed in SQL Statement on the Flex SQL form. In these examples, the R5REQUISLINES table is being triggered; the SQL*Plus example is a post-insert trigger, and the PL/SQL example is a post-update trigger.