The Append Where Clause action is used for outbound messages only. It is used to pass the document selection criteria to the database views used in the transaction map.
The Append Where Clause action is defined in the source at the document level as a preprocess activity. The bind variables and bind values are set up in advance of the document being processed. The actual bind to the database views occurs as an in-process activity when the data becomes available. The default where clause of "where 1=1" is appended at runtime to dynamically construct the where clause based on the selection criteria provided.
Each Append Where Clause action accepts one set of a bind variable and a bind value. To pass multiple selection criteria, you must define multiple Append Where Clause actions.
The document selection criteria are identified in the event and event subscription.
If you are using the ECX_OAG_CONTROLAREA_V view in your map, you will need to bind to the TRANSACTION_TYPE and TRANSACTION_SUBTYPE columns for this view to execute properly.
If you are using the ECX_OAG_CONTROLAREA_TP_V view in your map, you will need to bind to the TRANSACTION_TYPE, TRANSACTION_SUBTYPE, PARTY_ID, PARTY_SITE_ID, and PARTY_TYPE columns for this view to execute properly.
Note: You must add the ECX_EVENT_MESSAGE item attribute to your Workflow item type to have access to the event details stored in the REFERENCE ID column of the ECX_OAG_CONTROLAREA_TP_V view.
The following table shows an example:
| Where Clause | Bind Variable | Bind Value |
|---|---|---|
| and ECX_OAG_CONTROLAREA_TP_V.TRANSACTION_TYPE=:TTYPE | TYPE | TRANSACTION_TYPE |
| and ECX_OAG_CONTROLAREA_TP_V.TRANSACTION_SUBTYPE=:SUBTYPE | SUBTYPE | TRANSACTION_SUBTYPE |
| and ECX_OAG_CONTROLAREA_TP_V.PARTY_ID=:PARTY_ID | PARTY_ID | TP_ID |
| and ECX_OAG_CONTROLAREA_TP_V.PARTY_SITE_ID=:PARTY_SITE_ID | PARTY_SITE_ID | TP_SITE_ID |
| and ECX_OAG_CONTROLAREA_TP_PARTY_TYPE=:PARTY_TYPE | PARTY_TYPE | TP_TYPE |
| and <APPS_HEADER_V>.<document id> =:DOCID | DOCID | document_id |
Note: For this method, use the Create Global Variable action to define variables to store the bind value. The global variables must be defined with the exact spelling of the parameters. In this example, the global variables are TRANSACTION_TYPE, TRANSACTION_SUBTYPE, TP_ID, TP_SITE_ID, TP_TYPE, and DOCUMENT_ID.
The following are reserved names for global variables:
TRANSACTION_TYPE
TRANSACTION_SUBTYPE
DOCUMENT_ID
TP_SITE_ID (also known as PARTY_SITE_ID)
TP_ID (also known as PARTY_ID)
TP_TYPE (also known as PARTY_TYPE)
PARAMETER1, PARAMETER2, PARAMETER3, PARAMETER4, and PARAMETER5
Other source variables not related to the event triggering process can be used as bind values to ensure that the correct document is selected from the Oracle E-Business Suite database.
If you know the key values, you can define them in your where clause. With this method, you do not have to use the Bind Variable and Bind Value. The following table shows an example:
| Where Clause | Bind Variable | Bind Value |
|---|---|---|
| and ECX_OAG_CONTROLAREA_TP_V.TRANSACTION_ TYPE ='POO' | N/A | N/A |
| and ECX_OAG_CONTROLAREA_TP_V.TRANSACTION_ SUBTYPE ='POOB' | N/A | N/A |
| and ECX_PO_HEADER_V.PO_NUMBER ='A754739' | N/A | N/A |
Note: Literal strings must be bound by single quotes.
See Map Action Editor - Overview for details on the common components of the window.
The Optional Conditional Expression is not available for the Append Where Clause action because it is a preprocess action and therefore the data is not available yet.
Identify the where clause to bind the database views used for the outbound message. This where clause will be appended to the default where clause of "where 1=1".
If you have multiple selection criteria based on the same database view, define an Append Where Clause action for each of the criteria. Define all fields for the first Append Where Clause action (Where Clause, Bind Variable, and Bind Value). For the subsequent Append Where Clause actions, define only the Bind Variable and Bind Value (leaving the Where Clause blank).
Identify the bind variable to be used by the where clause.
Identify the bind value to be assigned to the bind variable used by the where clause.
The bind value is a global variable whose value is determined at run time, or a literal value.