Custom Tables Used with Send Services
All send services use three custom tables to process events in the Oracle Applications system that require data to be sent to Integration Server: WM_TRACKCHANGES, WM_CONTROL, and WM_SEND_REFERENCE_T.
The following sections describe these custom tables, how they are populated, and how they are used by send services to capture data and send it to Integration Server.
WM_TRACKCHANGES Table
The WM_TRACKCHANGES table records INSERT, UPDATE, and DELETE events in the Oracle Applications production tables for business objects that you want to track. The recording of these events is done by triggers and procedures. Each send service depends on one or more triggers or procedures to track the events that you want to capture.
Each trigger inserts a new record in the WM_TRACKCHANGES table whenever an event occurs in an Oracle Applications production table that causes the trigger to fire.
Note:
You must install and enable triggers on your Oracle Applications system for every send service you want to use. See
Database Scripts for more information about installing and enabling triggers.
Send services poll the WM_TRACKCHANGES table to determine whether they need to send business documents to Integration Server.
This section lists the structure of the table, and describes how the table is populated.
Field | Description |
TRANSACTION_TYPE | Type of business object being inserted, updated, or deleted. For example, VENDOR, PO, or INVOICE. |
TRANSACTION_ID | Unique Oracle Applications identifier to identify the business object associated with the record. For example, for a Vendor business object this field is AP_SUPPLIERS.AP_SUPPLIER_ID. For each record in the table associated with this business object, the value for this field will be the specific Vendor ID associated with the Vendor business document. |
DATE_CREATED | Date the record was added to the table. |
WEB_
TRANSACTION_
ID | Unique identifier for records in the table. This value is a unique sequence number within this table that determines the order in which the records were added to the table. This information is used to make sure the records are processed in sequence according to the sequence of events in Oracle Applications. |
TRANSACTION_
STATUS | Type of event occurring on the business object. Valid values include:  0 = UPDATE 1 = INSERT 2 = DELETE When you add a record to a header table, it will have a status of 1 (for INSERT). When you add a record to any other table related to the new record, it will have a status of 0 (for UPDATE). The send services determine what action to take on the business documents according to the sum of the TRANSACTION_STATUS field for all related records. That is, the TRANSACTION_STATUS field for all related records are added together to determine what action the send service will take when it polls the WM_TRACKCHANGES table. For information about how the send services process the business documents based on the TRANSACTION_STATUS, see
Overview of Send Service Transaction Processing. |
COMMENTS | Comments describing the record. This value is provided by the trigger that adds the record to the table, or by the service that creates a new record if the business document transfer fails. |
PROCESSED_FLAG | Indicates whether the record has been processed (Y), or not processed (N). The PROCESSED_FLAG defaults to N. If you are using a send service in debug mode, the service sets the value of this field to Y and retains the record in the table. If you are not running the service in debug mode, the service deletes the record after it is processed. |
DATE_PROCESSED | Date the polling service retrieved the record from the table. If you are using a send service in debug mode, the service sets the value of this field to the date the record was processed in the database’s date format; for example, 30-Jun-04. If you are not running the service in debug mode, the service deletes the record after it is processed. |
Example of How the WM_TRACKCHANGES Table is Populated
Suppose that a new Vendor with four Sites and five Contacts is added to Oracle Applications. That event causes the WM_TRACKCHANGES table to receive ten records: one for the Vendor, four for the Sites, and Five for the contacts. In this case, the parent record is Vendor and the child records are Site and Contact.
The following table shows how triggers insert the records into the WM_TRACKCHANGES table for this example. For all of the records in the example, the TRANSACTION_TYPE is VENDOR, the TRANSACTION_ID is 101, and the PROCESSED_FLAG is set to N. The other fields are as follows:
DATE_CHANGED | WEB_TRANSACTION_ID | TRANSACTION_STATUS_ID | COMMENT |
30-Jun-04 | 5001 | 1 | Vendor inserted |
30-Jun-04 | 5002 | 0 | Site inserted |
30-Jun-04 | 5003 | 0 | Site inserted |
30-Jun-04 | 5004 | 0 | Contact inserted |
30-Jun-04 | 5005 | 0 | Contact inserted |
30-Jun-04 | 5006 | 0 | Contact inserted |
30-Jun-04 | 5007 | 0 | Site inserted |
30-Jun-04 | 5008 | 0 | Site inserted |
30-Jun-04 | 5009 | 0 | Contact inserted |
30-Jun-04 | 5010 | 0 | Contact inserted |
Notice that in this example, the values for the Site and Contact records are assigned a status of 0. This is because these records are child records of the Vendor record. The parent record is treated as an INSERT event. The child records are treated as UPDATE events.
The table records the records like this so that the send service accurately creates a single business document for the parent business document and does not create separate business documents for the child records. The parent record will include the new child data in its business document.
WM_CONTROL Table
The WM_CONTROL table prevents a service from processing the same business document multiple times. This situation could occur if the polling interval for a particular transaction is shorter than the time it takes to execute the service, or if you are using the service in a clustered Integration Server environment.
If a service attempts to process a record and the WM_CONTROL table is locked, the service defers its execution until the next polling interval.
The fields in the table are as follows:
Field | Description |
TRANSACTION_TYPE | Type of business object. For example, VENDOR, PO, or INVOICE. |
STATUS | Indicates the status of the service’s processing. Valid values are READY and IN PROCESS. |
WM_SEND_REFERENCE_T Table
The WM_SEND_REFERENCE_T table records the time stamps of the send services.
The fields in the table are as follows:
Field | Description |
TRANSACTION_TYPE | Type of business object. For example, VENDOR, PO, or INVOICE. |
LAST_SEND_DATE | Contains the time stamp of the previous send service. |