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