1. Implementation Guide
  2. Business Events Resequencer Technical Accelerator
  3. Technical Accelerator Configuration
  4. Setting Up the Oracle ATP Instance
  5. Database (ATP) Objects

Database (ATP) Objects

Prerequisites

  • Download SQL scripts.

Table OHIP_OIC_EXT_SYS_CONFIG_TAB – Configurations

  • Error Status – ERR_STATUS

    • N – Set to N by default

    • Y marked as Y for an External System that has errored (notification email is sent while updating this flag).

  • Parking Time – PARKING_TIME

    • Default is 30 seconds.

    • Range can be from 30 to 150 seconds.

  • Purge Frequency – PURGE_FREQ

    • 7 days is the default to purge completed business events data and log table data.

    • 0 indicates no purge is executed.

  • Logging Enabled – LOGGING_ENABLE

    • N – Set to N by default to log only errored records.

    • Y – Will log errored and completed business events.

  • Maximum Number of Records – MAX_NUM_RECORDS

    • Set by default to 200 which sets the number of business events to be dispatched as one group JSON payload to the external system.

  • External System ID – EXTERNAL_SYSTEM_ID

    • To be set as the ID of the External System (configured in OPERA Cloud) that must consume business events.

  • Error Email – ERR_EMAIL

    • Email Address to receive notifications from this technical accelerator when errors occur.

  • External System Endpoint – EXT_SYS_ENDPOINT

    • REST Endpoint of the Target Integration flow in OIC that communicates to the external system. Once the target external system integration has been activated, the endpoint URL of the integration must be mentioned in this column against the external system.

Table 2-1 OHIP_OIC_EXT_SYS_CONFIG_TAB Table Columns

Column Name Data Type Description

ERR_STATUS

CHAR (1 BYTE)

The Error Status of the External System Id (Y/N).

PARKING_TIME

NUMBER

The parking time in seconds.

LOGGING_ENABLED

CHAR (1 BYTE)

Y – enable logging

N – disable logging

CREATION_TIMESTAMP

TIMESTAMP (6)

The timestamp at which the entry is created.

UPDATE_TIMESTAMP

TIMESTAMP (6)

The timestamp at which the entry is updated.

CALL_PARENT_INT

CHAR (1 BYTE)

Y – the parent integration is called.

N – the parent integration is not called.

L – Locked

EXTERNAL_SYSTEM_ID

VARCHAR2 (200 BYTE)

The Primary Key – EXTERNAL_SYSTEM_ID of the Target.

MAX_NUM_RECORDS

NUMBER

The maximum number of business events to be put in one group. The recommended number is 200.

PURGE_FREQ

VARCHAR2 (200 BYTE)

The number of days that processed business events data is allowed in the OHIP_OIC_BUSINESS_EVENTS_Q_TAB and OHIP_OIC_LOG_TAB tables.

ERR_EMAIL

VARCHAR2 (100 BYTE)

The email ID of the recipient who receives error notification emails.

EXT_SYS_ENDPOINT

VARCHAR2 (3999 BYTE)

The External system endpoint.

Inserting External System Configurations into the Configuration Table

The below steps are mandatory for inserting each external system configuration as one row into the Configuration table.

  1. Log in to the Oracle ATP instance using a valid username and password.

  2. Open a new SQL worksheet on Oracle Sql Developer.

  3. Verify the seeded configuration data is loaded.

  4. Run the following query to return one row with seeded configuration data:
    select * from OHIP_OIC_EXT_SYS_CONFIG_TAB where external_system_id = 'GLOBAL'
  5. Configure the following custom external system data:

    1. External System Id

    2. Notification Email Id

    3. External system REST endpoint of your integration

  6. Execute the following commands on your Oracle ATP instance:

    Note:

    The bind variables prefixed with "CUSTOM_" in the below query are placeholders and must be replaced per your custom configuration. 

    insert into OHIP_OIC_EXT_SYS_CONFIG_TAB (select err_status, parking_time, logging_enabled, systimestamp, systimestamp, call_parent_int, :CUSTOM_EXTERNAL_SYSTEM_ID, max_num_records, purge_freq, :CUSTOM_SUPPORT_EMAIL_TO_NOTIFY@YOURCORP.COM, :CUSTOM_EXTERNAL_SYSTEM_ENDPOINT from OHIP_OIC_EXT_SYS_CONFIG_TAB where external_system_id = 'GLOBAL');
commit;
  7. Update the global support email in the ATP instance by executing the below SQL query after replacing ‘YOUR_DEFAULT_SUPPORT_EMAIL_ID’ with the desired global support email address.
    update ohip_oic_ext_sys_config_tab set err_email = 'YOUR_DEFAULT_SUPPORT_EMAIL_ID' where external_system_id = 'GLOBAL'; commit;

Table OHIP_OIC_BUSINESS_EVENTS_Q_TAB

  • This table holds OPERA Business Events Data captured using the OHIP Oracle Hospitality Adapter.

  • It has primary constraints on the combination of BUSINESS_EVENT_ID and EXTERNAL_SYSTEM_ID.

Table 2-2 OHIP_OIC_BUSINESS_EVENTS_Q_TAB Table Columns

Column Name Data Type Description

BUSINESS_EVENTS_DATA

BLOB

The business events payload.

ACTION_TYPE

VARCHAR2 (200 BYTE)

NEW_PROFILE, UPDATE_PROFILE , RESERVATION

BUSINESS_EVENT_ID

VARCHAR2 (100 BYTE)

Primary Key – BUSINESS_EVENT_ID – extracted from business_events_data

EXTERNAL_SYSTEM_ID

VARCHAR2 (200 BYTE)

Primary Key – EXTERNAL_SYSTEM_ID of the Target 

STATUS

CHAR (1 BYTE)

N – NEW

L – LOCKED

P – PROCESSING

E – ERROR

C – COMPLETED

HOTEL_ID

VARCHAR2 (200 BYTE)

The hotel ID extracted from business_events_data.

RETRY_CNT

NUMBER

The number of times retried in case of error.

ERROR_MAIL_TIMESTAMP

TIMESTAMP (6)

The time stamp when mail was sent on error after the retry count exceeded 3.

BUSINESS_EVENT_TIMESTAMP

TIMESTAMP (6)

The business event timestamp from payload (createdDateTime).

BATCH_TIMESTAMP

TIMESTAMP (6)

The business event batch response time from payload (receivedDateTime).

EVENTS_SEQ

NUMBER

The events sequence (1 to 20)

CREATION_TIMESTAMP

TIMESTAMP (6)

The creation timestamp as systimestamp in the database.

UPDATE_TIMESTAMP

TIMESTAMP (6)

The update timestamp as systimestamp in the ATP database.

GROUP_ID

NUMBER

The group ID assigned post grouping business events.

ID

NUMBER

The Auto ID from the database sequence.

Table OHIP_OIC_EXT_SYS_CONFIG_TAB

This table holds the configured values for each external system id.

Table 2-3 OHIP_OIC_EXT_SYS_CONFIG_TAB Table Columns

Column Name Data Type Description

ERR_STATUS

CHAR (1 BYTE)

The Error Status of the External System ID (Y/N).

PARKING_TIME

NUMBER

The parking time in seconds.

LOGGING_ENABLED

CHAR (1 BYTE)

Y – enable logging

N – disable logging

CREATION_TIMESTAMP

TIMESTAMP (6)

The timestamp at which the entry is created.

UPDATE_TIMESTAMP

TIMESTAMP (6)

The timestamp at which the entry is updated.

CALL_PARENT_INT

CHAR (1 BYTE)

Y – the parent integration is called.

N – the parent integration is not called.

L – Locked

EXTERNAL_SYSTEM_ID

VARCHAR2 (200 BYTE)

Primary Key – EXTERNAL_SYSTEM_ID of the Target 

MAX_NUM_RECORDS

NUMBER

The maximum number of business events to be put in one group. The recommended number is 200.

PURGE_FREQ

VARCHAR2 (200 BYTE)

The number of days the processed business events data is allowed in the OHIP_OIC_BUSINESS_EVENTS_Q_TAB and OHIP_OIC_LOG_TAB tables.

ERR_EMAIL

VARCHAR2 (100 BYTE)

The email ID of the recipient who receives error notification emails.

EXT_SYS_ENDPOINT

VARCHAR2 (3999 BYTE)

The external system endpoint.

Table OHIP_OIC_LOG_TAB

This table holds all the processed and error business events data with log messages.

Table 2-4 OHIP_OIC_LOG_TAB Table Columns

Column Name Data Type Description

GROUP_ID

NUMBER

The group ID assigned post grouping business events.

INSTANCE_ID

VARCHAR2 (100 BYTE)

The OIC-Instance ID.

INTEGRATION_ID

VARCHAR2 (100 BYTE)

The OIC-Integration ID.

RETRY_CNT

NUMBER

The number of times retried in case of error.

ERROR_MAIL_TIMESTAMP

TIMESTAMP (6)

The timestamp when mail was sent on error after the retry count exceeded 3.

LOG_MESSAGE

VARCHAR2 (2000 BYTE)

The log message.

STATUS

CHAR (1 BYTE)

N – NEW

L – LOCKED

P – PROCESSING

E – ERROR

C – COMPLETED

CREATION_TIMESTAMP

TIMESTAMP (6)

The timestamp at which entry is created.

BUSINESS_EVENT_ID

VARCHAR2 (100 BYTE)

The business event ID.

EXTERNAL_SYSTEM_ID

VARCHAR2 (100 BYTE)

The EXTERNAL_SYSTEM_ID of the Target.

View OHIP_OIC_SORT_BE_VW

This view holds the sorted business events based on columns business_event_timestamp, batch_timestamp, events_seq, business_event_id for business events with an elapsed parking time.

Table 2-5 OHIP_OIC_SORT_BE_VW Table Columns

Column Name Data Type Description

BUSINESS_EVENT_TIMESTAMP

TIMESTAMP (6)

The business event timestamp from payload (createdDateTime).

EXTERNAL_SYSTEM_ID

VARCHAR2 (200)

The TARGET- EXTERNAL SYSTEM ID.

BATCH_TIMESTAMP

TIMESTAMP (6)

The business event batch response time from payload (receivedDateTime).

EVENTS_SEQ

NUMBER

The events sequence (1 to 20).

STATUS

CHAR (1)

N – NEW

L – LOCKED

P – PROCESSING

E – ERROR

C – COMPLETED

RETRY_CNT

NUMBER

The number of times retried in case of error.

BUSINESS_EVENT_ID

VARCHAR2 (100)

BUSINESS_EVENT_ID - extracted from business_events_data

BYTES

NUMBER

The size in bytes of the business events data payload for this business_event_id.

Package OHIP_OIC_BE_RSQ_PKG

All stored procedures used in this Accelerator are part of the OHIP_OIC_BE_RSQ_PKG package.

Stored Procedure OHIP_OIC_INSERT_BE_SP

  • This procedure parses the input business event JSON payload which contains n number of business events. It fetches each field from the JSON and inserts them into a table OHIP_OIC_BUSINESS_EVENTS_Q_TAB as one business event per row.

  • After business events are inserted into the OHIP_OIC_BUSINESS_EVENTS_Q_TAB table, in case there is no record found in OHIP_OIC_EXT_SYS_CONFIG_TAB for the given external system, the procedures make an entry in the configuration table OHIP_OIC_EXT_SYS_CONFIG_TAB for the given external system id with default values. The default values are the values provided for the record with external_system_id as GLOBAL.

  • Input Parameters:

    • p_business_events_data – The payload captured by the integration.

    • p_external_system_id – The target external system Id.

    • p_integration_id – The integration which makes the ATP call to this procedure.

    • p_batch_timestamp – The timestamp at which OHIP responded.

  • Output Parameter:

    • p_out_ins_atp_success – True = successful insert; False = Failure

Stored Procedure OHIP_OIC_GET_GROUP_ID_SP

The procedure assigns a group id to business events in the OHIP_OIC_BUSINESS_EVENTS_Q_TAB table sorted based on order of columns as business_event_timestamp, batch_timestamp,events_seq,business_event_id after their parking time is elapsed.

  • The number of records selected for assigning a group id is based on the following two important criteria, whichever is met first:

    • MAX_NUM_RECORDS in table: OHIP_OIC_EXT_SYS_CONFIG_TAB for the external system id in context

    • Maximum Bytes size of 5 MB as a constant value set within the procedure

  • Input Parameter:

    • external_system_id_pin – The target external System Id.

  • Output Parameters:

    • group_id_pout – The assigned group id.

    • wait_time_sec_pout – The waiting time.

Stored Procedure OHIP_OIC_GET_INTEGRATION_STATUS_SP

This procedure returns either of the following integration statuses for the given external system id:

  • PROCESSING – When there are records still to be processed.

  • CLOSED – When there are no records to be processed.

  • ERROR – When there are errored records.

  • Input Parameter:

    • external_system_id_pin – The target external System Id.

  • Output Parameter:

    • integration_status_pout – PROCESSING/CLOSED/ERROR

Stored Procedure OHIP_OIC_GET_EXT_SYS_LIST_SP

This procedure updates the column CALL_PAR_INT to 'L' in OHIP_OIC_EXT_SYS_CONFIG_TAB table for the list of external systems whose error status is not Y and CALL_PAR_INT is Y. It returns the list of external systems for which the update was successful.

  • Output Parameter:

    • Result_clob – JSON object as clob containing a list of external system ids.

Stored Procedure OHIP_OIC_UPD_BE_STATUS_SP

This procedure updates the status of business events of a group and also implements the Logging feature.

  • OIC integrations call this procedure to update the group event status for ALL types of status updates.

  • Updates the group event status to P or C or E, but when status is updated to E (errored event), it increments the retry count by 1 each time for that errored business event id.

  • When updating an event to E, all events BEFORE this errored event in the group are updated to C. 

  • Returns the updated retry_cnt for status update to E; otherwise, it returns retry_cnt = 0 for status updates to P and C of this group id.

  • Inserts into the log table whenever updating status to E, but when a status is updated to C, insert in the log table only if logging_enabled is Y in the config table for this external system id.

  • Input Parameters:

    • p_external_system_id –  The target External System Id.

    • p_group_id – The Group Id.

    • p_status – The status.

    • p_business_event_id – The business event Id.

  • Output Parameter:

    • p_out_retry_cnt – The retry count.

Stored Procedure OHIP_UPD_EXT_SYS_CALL_PAR_INT_SP

This procedure updates CALL_PAR_INT value for the given external system id in OHIP_OIC_EXT_SYS_CONFIG_TAB table with the given status.

  • Input Parameters:

    • external_system_id_pin – The target external System Id.

    • status_pin – The status (Y/N).

Stored Procedure OHIP_OIC_DEL_BE_SP

This procedure is called by the scheduled job OHIP_OIC_DEL_BE_JOB that runs every day.

  • It deletes processed business events from the OHIP_OIC_BUSINESS_EVENTS_Q_TAB table and OHIP_OIC_LOG_TAB table, which are PURGE_FREQ (configuration in OHIP_OIC_EXT_SYS_CONFIG_TAB table for each external system) number of days older.

  • Business events are allowed in the OHIP_OIC_BUSINESS_EVENTS_Q_TAB table for the number of days configured in the PURGE_FREQ column of the OHIP_OIC_EXT_SYS_CONFIG_TAB for each external system.

Stored Procedure OHIP_OIC_GET_ERR_EMAIL_SP

This procedure returns the error email id provided for an external system stored in the table OHIP_OIC_EXT_SYS_CONFIG_TAB.

  • Input Parameter:

    • external_system_id_pin – The target external System Id.

  • Output Parameter:

    • err_email_pout – The error email id.

Stored Procedure OHIP_OIC_UPD_EXT_SYS_ERR_STATUS_SP

This procedure updates the error status provided for the given external system in the OHIP_OIC_EXT_SYS_CONFIG_TAB table with the given status.

  • Input Parameters:

    • external_system_id_pin – The target external System Id.

    • status_pin – The status.

Stored Procedure OHIP_OIC_RESET_DATA_SP

This procedure is called by the integration Oracle REST REST Error Flag Reset to reset the data in case of error.

  • Resets the external system in table OHIP_OIC_EXT_SYS_CONFIG_TAB to start the processing of business events. 

  • Resets the CALL_PAR_INT value to Y and the ERR_STATUS value to N for the given the external system in table OHIP_OIC_EXT_SYS_CONFIG_TAB.

  • Additionally, this procedure also resets the status flag to N and retry count to 0 for this external system in table OHIP_OIC_BUSINESS_EVENTS_Q_TAB.

  • Input Parameters:

    • external_system_id_pin – The target external System Id.

Functions and Sequences

  • Function OHIP_OIC_CLOB_TO_BLOB_FN

    • This function converts clob data to blob data. It is used in the procedure OHIP_OIC_INSERT_BE_SP while inserting business events data.

  • Sequence OHIP_OIC_BE_Q_ID_SEQ

    • This sequence is created for populating the BUSINESS_EVENT_ID in table OHIP_OIC_BUSINESS_EVENTS_Q_TAB with an auto-incremented number.

Table 2-6 OHIP_OIC_BE_Q_ID_SEQ

Name Value

MIN_VALUE

1

MAX_VALUE

9999999999999999999999999999

INCREMENT_BY

1

CYCLE_FLAG

Y

ORDER_FLAG

N

CACHE_SIZE

20

SCALE_FLAG

N

EXTEND_FLAG

N

SHARDED_FLAG

N

SESSION_FLAG

N

KEEP_VALUE

N

DUPLICATED

N

SHARDED

N

  • Sequence OHIP_OIC_GROUP_ID_SEQ

    • This sequence is created for the population ID column in table OHIP_OIC_BUSINESS_EVENTS_Q_ TAB with an auto-incremented number.

Table 2-7 OHIP_OIC_GROUP_ID_SEQ

Name Value

MIN_VALUE

1

MAX_VALUE

9999999999999999999999999999

INCREMENT_BY

1

CYCLE_FLAG

Y

ORDER_FLAG

N

CACHE_SIZE

20

SCALE_FLAG

N

EXTEND_FLAG

N

SHARDED_FLAG

N

SESSION_FLAG

N

KEEP_VALUE

N

DUPLICATED

N

SHARDED

N

  • Job OHIP_OIC_DEL_BE_JOB

    • This job is used for purging data.

    • It runs every day and calls the procedure OHIP_OIC_DEL_BE_SP.

    • This procedure deletes processed business events from the OHIP_OIC_BUSINESS_EVENTS_Q_TAB table and the OHIP_OIC_LOG_TAB table, which are PURGE_FREQ (configuration in OHIP_OIC_EXT_SYS_CONFIG_TAB table for each external system) number of days older.

Parent topic: Setting Up the Oracle ATP Instance