Flex SQL is an application method to create database triggers (post-insert or post-update) to validate, insert, or update all HxGN 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 HxGN EAM to meet the needs of your organization.
Specifically, creating and updating account details is required for any transaction originates in HxGN EAM that will be interfaced to the external (third-party) application. See these topics:
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.