The INLINE condition type allows you to evaluate a condition in the driving table without having to run an UPDATE WHERE statement (which is extra processing) or without adding a restrictive Where clause statement on INSERT. For example, our sample rule checks the invoice_status field in the ap_invoice_headers table. The ap_invoice_headers table is also our driving table, which is used to populate the interim table with the list of candidates eligible for archive/purge. We could enforce this business rule as we have already demonstrated with the WHERE or SET condition types. Or we could enforce the business requirement upon insert into the interim table:

INSERT INTO INTERIM A
(INVOICE_HEADER_ID)
SELECT invoice_header_id 
FROM ap_invoice_headers H
WHERE invoice_date < :p_period_to
AND invoice_status=’CLOSED’

This would populate the interim table with all invoice header transactions that had an invoice_status of CLOSED, which meets the business requirement. This also allows us to not have to run an UPDATE statement using the WHERE or SET condition types, thereby eliminating the need for a potentially time-consuming SQL statement. However, this does not identify all of the invoice header transactions that do not have an invoice status of CLOSED. It is useful information to know what transactions failed a rule. By adding business requirements to the WHERE clause of the INSERT statement, the ability to obtain this information is not possible.

However, using the INLINE condition type, we can take advantage of the INSERT statement to meet the business requirement while also providing the exception reporting capability that is critical to Candidate Generation. The INLINE condition type is written as follows:

INSERT INTO INTERIM A
(INVOICE_HEADER_ID, INVOICE_NOT_CLOSED)
SELECT invoice_header_id , DECODE(H.invoice_status,’CLOSED’,0,1) 
FROM ap_invoice_headers H
WHERE invoice_date < :p_period_to

The INLINE condition type is added to the SELECT statement used to INSERT into the interim table. Like the SET condition type, the INLINE condition type uses a DECODE (or CASE) statement to evaluate the column value and selects a 0 or 1 accordingly. In EDM the INLINE condition is written as:

DECODE(H.invoice_status,’CLOSED’,0,1)

Note that the invoice_status column is aliased with an H. It is good practice to alias the columns in the INLINE in case there are multiple tables in the INSERT FROM clause. This will ensure you are looking at the correct column in the correct table.