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

To create an ‘Event Store’ and ‘Triggers’ in Oracle database, perform the following:
  1. 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.
  2. 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.
Here is a sample of an insert trigger called event_create which populates the event store IBM_CI_EVENTS when a row is added to the selected table.
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;
Table 2. Trigger description
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.



Feedback | Notices


Timestamp icon Last updated: Tuesday, 27 September 2016


https://www.ibm.com/support/knowledgecenter/en/SS3LC4_7.5.3/com.ibm.wci.doc/OracleEBS_prerequisites_inbound_activity.html