Creating Oracle Applications -to-Integration Server Transaction Definitions
Perform the following procedure to create transaction definitions that select data from Oracle Applications and delivers it to webMethods Integration Server.
For information about how SQL trees are defined in
Oracle Applications -to-
Integration Server transaction definitions, see
Working with SQL Trees in
Oracle
Applications
-to-Integration Server Transactions.
Note:
You cannot combine stored procedures with SQL queries in an Oracle Applications -to-Integration Server transaction definition. Use only stored procedures or SQL trees.
To create a new
Oracle Applications -to-
Integration Server transaction definition
1. In the Adapters menu in the Integration Server Administrator navigation area, click Oracle Apps Adapter.
2. In the Oracle Apps Adapter menu in the navigation area, click Transaction Mgmt.
3. Click Transaction Configuration.
4. Select the Oracle Applications release from the list.
5. Select Oracle-to-IS in the Transaction Type field.
6. Click Add. The adapter displays the New Transaction screen.
7. Specify a name for the transaction in the Transaction Name field.
8. Click Submit. The adapter displays the Edit Transaction screen.
9. Specify the top-level alias for the transaction, as follows:
a. Enter the alias name in the Alias Name field.
b. In the SQL field, enter or edit the SQL statement that performs the query, or enter the name of a stored procedure to execute:
To expose an SQL input parameter as a service input in
Designer, enclose the input parameter within an ampersand (&) and an asterisk (*) (for example, &varname*). Stored procedure input parameters are exposed as service inputs in
Designer.
To call a stored procedure, enter the stored procedure name in the format
schema.package.procname. If the stored procedure is not located in a package, you do not have to enter the schema and package names.
c. Click Submit.
10. To specify a child for an alias, select the alias to which you want to add a child and click Next Level. If you want to add a new sibling alias but an alias is currently selected, click Cancel to de-select the current alias. Then specify the alias information, as follows:
a. Enter the alias name in the Alias Name field.
b. In the SQL field, enter or edit the SQL statement that performs the query.
c. To specify the relationship of the current alias to its parent alias, enter it in the Relations field.
If you are creating an alias with a SELECT statement, Relations defines the WHERE clause and join parameters. Enter the values as follows:
COLUMN = PARENT_ALIAS.COLUMN AND COLUMN2 = PARENT_ALIAS.COLUMN2 …
d. Click Submit.
11. To add SQL to acknowledge the transaction, click Edit Ack SQL. The adapter displays the Acknowledgement Service SQL for transaction screen. Add the SQL in the Acknowledgement SQL field, and then click Submit.
Note:
The purpose of an Acknowledgement SQL is to prevent the query from retrieving the same piece of data again.
12. Repeat the previous step for each alias you are adding.