Message Schemas for Insert, Update, Delete, and Select Operations
The Microsoft BizTalk Adapter for Oracle E-Business Suitesurfaces basic Insert, Update, Delete, and Select operations for each interface table in Oracle E-Business Suite and each table in the underlying database. The adapter also surfaces the Select operation for each interface view in Oracle E-Business Suite and each view in the underlying database. These operations perform the appropriate SQL statement qualified by a WHERE clause. The Oracle E-Business adapter uses strongly-typed records and record sets in these operations.
Message Structure for Basic Operations
The following table shows the XML message structure for the basic operations exposed by the Oracle E-Business adapter on Oracle E-Business Suite interface tables and interface views and on the underlying database tables and views. The target object for an operation is specified in the message action and also appears in the target namespace.
Note
See attribute descriptions after table.
| Operation | XML Message | Description | SQL Executed by the Adapter |
|---|---|---|---|
| Insert | <Insert xmlns="[VERSION]/InterfaceTables/[SCHEMA]/[APP_NAME]/[INTERFACETABLE_NAME]"> <RECORDSET> <InsertRecord> <[FIELD1_NAME] InlineValue="value">[value1]</[FIELD1_NAME]> <[FIELD2_NAME] InlineValue="value">[value2]</[FIELD2_NAME]> … </InsertRecord> </RECORDSET> </Insert> |
The value for the InlineValue attribute, if specified, overrides the value of an element. | INSERT INTO TABLE_NAME (FIELD1_NAME, FIELD2_NAME, …) VALUES (value1, value2, …); |
| Insert Response | <InsertResponse xmlns="[VERSION]/InterfaceTables/[SCHEMA]/[APP_NAME]/[INTERFACETABLE_NAME]"> <InsertResult>[rows inserted]</InsertResult> </InsertResponse> |
The number of rows inserted is returned in the InsertResult element. | -- |
| Select | <Select xmlns="[VERSION]/InterfaceTables/[SCHEMA]/[APP_NAME]/[INTERFACETABLE_NAME]"> <COLUMN_NAMES>[COLUMN_list]</COLUMN_NAMES> <FILTER>WHERE_clause</FILTER> </Select> |
A SELECT query is performed on the target table using the WHERE clause specified in the FILTER element. The result set contains the columns in the comma-separated list of column names specified in the COLUMN_NAMES element. Important: This is the only operation that is applicable for interface views and database views. |
SELECT COLUMN_list FROM TABLE_NAME WHERE WHERE_clause; |
| Select Response | <SelectResponse xmlns="[VERSION]/InterfaceTables/[SCHEMA]/[APP_NAME]/[INTERFACETABLE_NAME]"> <SelectResult> <SelectRecord> <[FIELD1_NAME]>value1</[FIELD1_NAME]> <[FIELD2_NAME]>value2</[FIELD2_NAME]> … </SelectRecord> </SelectResult> </SelectResponse> |
The result set generated by the SELECT query. | -- |
| Update | <Update xmlns="[VERSION]/InterfaceTables/[SCHEMA]/[APP_NAME]/[INTERFACETABLE_NAME]"> <RECORDSET> <[FIELD1_NAME]>value1</[FIELD1_NAME]> <[FIELD2_NAME]>value2</[FIELD2_NAME]> … </RECORDSET> <FILTER>WHERE_clause</FILTER> </Update> |
Rows that match the where clause specified in the FILTER element are updated to the values specified in the RECORDSET. Only the columns that are specified in the RECORDSET element are updated in each matching row. | UPDATE [TABLE_NAME] SET [FIELD1_NAME] = value1, [FIELD2_NAME] = value2, … WHERE WHERE_clause; |
| Update Response | <UpdateResponse xmlns="[VERSION]/InterfaceTables/[SCHEMA]/[APP_NAME]/[INTERFACETABLE_NAME]"> <UpdateResult>[rows inserted]</UpdateResult> </UpdateResponse> |
The number of rows updated is returned in the UpdateResult element. | -- |
| Delete | <Delete xmlns="[VERSION]/InterfaceTables/[SCHEMA]/[APP_NAME]/[INTERFACETABLE_NAME]"> <FILTER>WHERE_clause</FILTER> </Delete> |
Rows matching the WHERE clause specified by the FILTER element are deleted. | DELETE FROM [TABLE_NAME] WHERE WHERE_clause; |
| Delete Response | <DeleteResponse xmlns="[VERSION]/InterfaceTables/[SCHEMA]/[APP_NAME]/[INTERFACETABLE_NAME]"> <DeleteResult>[rows deleted]</DeleteResult> </DeleteResponse> |
The number of rows deleted is returned in the DeleteResult element. | -- |
Attribute descriptions:
[VERSION] = The message version string; for example, http://schemas.microsoft.com/OracleEBS/2008/05.
[SCHEMA] = Collection of Oracle artifacts; for example, SCOTT.
[APP_NAME] = Application short name.
[INTERFACETABLE_NAME] = Name of the interface table.
[FIELD1_NAME] = Table field name.
[COLUMN_list] = Comma-separated list of columns.
[WHERE_clause] = WHERE_clause for the SELECT statement used for the operation; for example, ID > 10.
Important
The message structure for the basic operations on interface views, database tables, and database views is the same as that on interface tables, but the namespace for the operation specifies an interface view, database table, or database view rather than an interface table.
Message Actions for Basic Operations
The following table shows the message actions that the Oracle E-Business adapter uses for the basic operations on the interface tables and interface view in Oracle E-Business Suite, and the tables and views in the underlying database. The Oracle E-Business adapter uses the interface table, interface view, database table, or database view specified in the message action to determine the target of the operation.
Note
See entity descriptions after table.
| Operation | Message Action | Example |
|---|---|---|
| Insert | Applications: InterfaceTables/Insert/[SHORT_NAME]/[APP_NAME]/[TABLE_NAME] Database: Tables/Insert/[SCHEMA]/[TABLE_NAME] |
Applications: InterfaceTables/Insert/SQLGL/GL/GL_ALLOC_HISTORY Database: Tables/Insert/GL/GL_ALLOC_HISTORY |
| Insert Response | Applications: InterfaceTables/Insert/[SHORT_NAME]/[APP_NAME]/[TABLE_NAME]/response Database: Tables/Insert/[SCHEMA]/[TABLE_NAME]/response |
Applications: InterfaceTables/Insert/SQLGL/GL/GL_ALLOC_HISTORY/response Database: Tables/Insert/GL/GL_ALLOC_HISTORY/response |
| Select | Applications: InterfaceTables/Select/[SHORT_NAME]/[APP_NAME]/[TABLE_NAME] Database: Tables/Select/[SCHEMA]/[TABLE_NAME] |
Applications: InterfaceTables/Select/SQLGL/GL/GL_ALLOC_HISTORY Database: Tables/Select/GL/GL_ALLOC_HISTORY |
| Select Response | Applications: InterfaceTables/Select/[SHORT_NAME]/[APP_NAME]/[TABLE_NAME]/response Database: Tables/Select/[SCHEMA]/[TABLE_NAME]/response |
Applications: InterfaceTables/Select/SQLGL/GL/GL_ALLOC_HISTORY/response Database: Tables/Select/GL/GL_ALLOC_HISTORY/response |
| Update | Applications: InterfaceTables/Update/[SHORT_NAME]/[APP_NAME]/[TABLE_NAME] Database: Tables/Update/[SCHEMA]/[TABLE_NAME] |
Applications: InterfaceTables/Update/SQLGL/GL/GL_ALLOC_HISTORY Database: Tables/Update/GL/GL_ALLOC_HISTORY |
| Update Response | Applications: InterfaceTables/Update/[SHORT_NAME]/[APP_NAME]/[TABLE_NAME]/response Database: Tables/Update/[SCHEMA]/[TABLE_NAME]/response |
Applications: InterfaceTables/Update/SQLGL/GL/GL_ALLOC_HISTORY/response Database: Tables/Update/GL/GL_ALLOC_HISTORY/response |
| Delete | Applications: InterfaceTables/Delete/[SHORT_NAME]/[APP_NAME]/[TABLE_NAME] Database: Tables/Delete/[SCHEMA]/[TABLE_NAME] |
Applications: InterfaceTables/Delete/SQLGL/GL/GL_ALLOC_HISTORY Database: Tables/Delete/GL/GL_ALLOC_HISTORY |
| Delete Response | Applications: InterfaceTables/Delete/[SHORT_NAME]/[APP_NAME]/[TABLE_NAME]/response Database: Tables/Delete/[SCHEMA]/[TABLE_NAME]/response |
Applications: InterfaceTables/Delete/SQLGL/GL/GL_ALLOC_HISTORY/response Database: Tables/Delete/GL/GL_ALLOC_HISTORY/response |
Entity descriptions:
[SCHEMA] - Collection of Oracle artifacts (for example, GL).
[TABLE_NAME] - Name of the table (for example, GL_ALLOC_HISTORY).
Important
The message action for the Select operation on an interface view is the same as that for the interface table, except that "InterfaceViews" replaces "InterfaceTables." Similarly, the message action for the Select operation on a database view is the same as that for the database table, except that "Views" replaces "Tables.”
See Also
Messages and Message Schemas for BizTalk Adapter for Oracle E-Business Suite