Prerequisites for starter activities in Oracle E-Business Suite
This section contains the prerequisites to configure the Oracle E-Business Suite inbound activities.
You must create an event store and triggers in Oracle database as a prerequisite for using the starter activities.
The event store is a table that holds events that represent data changes until the polling connector can process them. The connector uses the event store to keep track of event entities. Each time a database record is created, updated, or deleted, the connector updates the status of the event in the event store. The Event Store Name field can be found in the Configure panel of the Get Created, Get Deleted, and Get Updated activities.
You can set triggers on user tables as needed to populate the event store. For Oracle database, set up triggers on user tables so that changes to the user tables can automatically generate events that is stored in the event store.
Creating the event store and triggers in Oracle E-Business Suite
- Create a new table. For example, if the event store name is configured
as IBM_CI_EVENTS, use the script available in <STUDIO ROOT>/etc/oracleebs/scripts/IBM_CI_EVENTS.SQL. The structure of the event table is described as follows:Note: It is recommended that a separate event table is created and used for every configured table/view.
Table 1. Event table structure Column name Type Description event_id Number The unique event ID, which is a primary key for the table. This can have the same value as the object_key. object_key String A string that contains the primary key of the record that is retrieved. This column cannot be null.
object_name String Each business object refers to a table or view. The name of the business object is constructed with the schema name and table name. This column cannot be null.
object_function String The operation corresponding to the event (Delete, Create, and Update). This column cannot be null
.event_priority Number Identifies the event priority. This value must be a positive integer. This column cannot be null.
event_time Timestamp Date and time when event was generated. The format is mm/dd/yyyy hh:mm:ss. event_status The event status is initially set to a value for a new event and updated by the adapter as it processes the event. The status can have one of the following values: - 0: Identifies a new event.
- 1: Identifies an event that has been delivered to an export.
- -1: An error occurred while processing the event.
This column cannot be null.
event_comment Any comment associated with the event. event_message
this is optional. This field can be blank. xid this is optional. This field can be blank. connector_ID The unique identifier for the connector instance that receives a specific event. - To create Triggers for your table, refer to the sample <STUDIO ROOT>/etc/oracleebs/scripts/sample_trigger.sql. The trigger is used to populate the event store when a row is created, updated. or deleted.
create or replace
TRIGGER SCOTT.event_create
after insert on SCOTT.CUSTOMER for each row
begin
insert into "SCOTT"."IBM_CI_EVENTS"
(
event_id
, object_key
,object_name
,object_function
,event_priority
,event_status
)
values
(
event_sequence.nextval
,:new.PKEY
,'ScottCustomer'
,'Create'
,0
,0
);
end;
Column name | Values |
---|---|
event_id | For a unique event_id, a sequence called event_sequence is used. Or else, you can provide a unique event ID for every row that is inserted. |
object_key | This contains the primary key of the record. |
object_name | The object name is constructed with the schema name and table name. For example, if the table CUSTOMER is present in Scott's schema, then the value of this property will be ScottCustomer. |
object_function | The value of object_function will be Create/Update/Delete depending on whether it is an insert/update/delete trigger. |
event_priority | The value of event_priority must be 0 or a positive integer. |
event_status | The value of event_status must be 0, so that it is considered as a new event. |