Oracle Retail Knowledge Base

2 Integration Architecture

4 min read

Mirror of Oracle documentation

Converted for search and offline reading. Authoritative source: Oracle. Diagrams and some complex tables are simplified — check the PDF when in doubt.

Integration Components Description

Merchandising

Database Views

  • Merchandising has database views that are used to expose Merchandising data for SIOCS to query. Each merchandising entity has a view with a name prefixed with V_RMS_SIM . These database views are owned by Merchandising specifically for the use of SIOCS. Read access is provided to SIOCS to call these views via synonyms and is used for both initial data seeding and delta changes.

  • The views serve as an integration contract between Merchandising and SIOCS. The view queries filter rows and include columns based on SIOCS’s need.

  • See details of entities published from Merchandising and used by SIOCS in the appendix.

Integration Change Log (ICL) Tables

  • ICL tables are used to capture data changes (insert/update/deletes) in base Merchandising tables. For example, when an item is created or updated, the ICL table for Item contains the item number along with time of change and the type of database operations (insert, update or delete).

  • The data change capture to ICL table uses functional filter criteria used previously (RIB publish). For example, the capture of item changes to ICL table happens only after an item is approved.

  • Merchandising inserts into the ICL tables for incremental changes only. The consuming application (SIOCS) is responsible for deleting processed records from these tables.

  • SIOCS processes the data based on ICL record create time and the type of database operation (insert, update or delete).

  • Merchandising database triggers insert into the ICL tables. An entry for SIOCS is made when SIOCS and Merchandising are configured to use the direct database integration method.

The following is the typical ICL table structure:

Column NameDatatypeMandatoryComments
ICL_IDRAW(16)yesDefault value -
SYS_GUID
ICL_CONSUMERVARCHAR2(10)yesSIOCS, and so on; one
record for each target
app that needs the
change
ICL_CHANGE_TYPEVARCHAR2(1)yesI(insert),U(update),D
(delete)
ICL_TABLE_NAMEVARCHAR2(30)yesMerchandising source
table that changed; for
example:ADDR,
ITEM_MASTER

Implementation Guide G56162-01 Copyright© 2026, Oracle and/or its affiliates.

Column NameDatatypeMandatoryComments
ICL_CREATE_TIMESTA
MP		TIMESTAMP(6) WITH
TIME ZONE		yesThe timestamp in
DBTIMEZONEwhen the
record was created in
this ICL table.
ICL_CREATE_IDVARCHAR2(254)yesUser ID of the user
who created the record
in this ICL table.
ICL_TRANSACTION_IDVARCHAR2(120)yesTransaction ID of the
transaction in which
the record was
committed in the
source table. It’s
populated through
dbms_transaction.lo
cal_transaction_id.
ICL_PROCESSED_INDVARCHAR2(1)yesIndicates whether the
record has been
processed by the
consuming application.
The consuming
application updates
this toI(in-progress)
orY(processed).
Default value isNo.
<ENTITY SPECIFIC
COLUMNS>		Source table to identify
the entity that has
changed. For example:
for item_master, this
will containITEM. For
orders, the table will
containORDER_NO,
ITEM,LOCATION. Not all
key columns in an ICL
table are populated.
Key columns are
populated depending
on the entity that was
changed.

Import Tables

Merchandising has import tables that are populated by SIOCS with data to be interfaced to Merchandising. These tables are data entity specific and have a prefix SIM_RMS . They have specific columns that are published by SIOCS in addition to standard columns to be used for traceability, error handling and metadata driven processing.

Consume Jobs

These jobs poll the SIM RMS import tables for entries ordered by timestamp and can be scheduled as required by the retailer. These jobs prepare the data for processing. Successful processing moves data into import history tables.

Implementation Guide G56162-01 Copyright© 2026, Oracle and/or its affiliates.

SIOCS

Import Tables

SIOCS ICL Staging Tables

ICLS_CLEARANCE ICLS_ITEM_LOC ICLS_ITEM_LOC_CFA ICLS_PRICE_CHANGE

Consume Jobs

ICL table data is managed and consumed by DCS work types. When a record is removed from the ICL tables, it is written to the MPS_STAGED_MESSAGE table or supporting ICLS table. Then, the MPS work types activate and do final processing of the MPS message.

Data Collection System (DCS) Work Types – Data collection system work types is a series of worker threads based on data categories that manage the collection of ICL table and input into the message processing system. These work types are administrated by the DCS Work Type screen (See the EICS Administration Guide ).

Message Processing System (MPS) Work Types – Message processing system work types are a series of worker threads based on data categories that manage the processing of business data messages in the staged message table. These work types can be administrated by the MPS Work Type screen (See the EICS Administration Guide ).

MPS Staged Messages – Stages messages are stored within the mps_staged_message table and processed using MPS work types. This screen allows the user to monitor and manage a singular staged message. (See the EICS Administration Guide ).

Implementation Guide G56162-01 Copyright© 2026, Oracle and/or its affiliates.

In this guide

Graph View

Backlinks