2 Integration Architecture

This section explains the different components that are used to make the whole integration work seamlessly.

Merchandising to SIOCS – Initial Data Load

For initial data load and on-demand seeding for new store setup, SIOCS batch processes query MFCS database views. Filter logic in the view called by SIOCS uses business logic that directly loads data into the SIOCS base tables. For further details on initial data seeding, refer to the ‘Data Seeding from Merchandising Foundation Cloud Service’ chapter of the SIOCS Administration Guide on the Oracle Help Center.

Merchandising to SIOCS – Transactions and Foundation Deltas

For transactions and foundation deltas, Merchandising data is replicated asynchronously into SIOCS. SIOCS queries Merchandising and Pricing database tables/views for data refresh and changes to foundation and transactional data.Identification of changes happens through periodic polling of Integration Change Log (ICL) tables. Events published to the ICL tables follows the same filter criteria as the older integration method (RIB publish). Merchandising database views work as the integration contract.

SIOCS to Merchandising

For sending transactions to Merchandising, SIOCS populates Merchandising import staging tables through asynchronous fire-and-forget publish. Merchandising polls for new messages through batch jobs and processes them through consume business logic that is also used by the service-based integrations. Additionally, there is a user interface-based review mechanism in Merchandising to help troubleshoot integration errors. New messages from SIOCS are not processed during nightly batch when inventory related batches are running because they impact inventory positions. Only Stock Count Schedule and Results are not processed during the entire duration of the nightly batch window.

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 Name	Datatype	Mandatory	Comments
ICL_ID	RAW(16)	yes	Default value - SYS_GUID
ICL_CONSUMER	VARCHAR2(10)	yes	SIOCS, and so on; one record for each target app that needs the change
ICL_CHANGE_TYPE	VARCHAR2(1)	yes	I (insert), U (update), D (delete)
ICL_TABLE_NAME	VARCHAR2(30)	yes	Merchandising source table that changed; for example: ADDR, ITEM_MASTER
ICL_CREATE_TIMESTAMP	TIMESTAMP(6) WITH TIME ZONE	yes	The timestamp in DBTIMEZONE when the record was created in this ICL table.
ICL_CREATE_ID	VARCHAR2(254)	yes	User ID of the user who created the record in this ICL table.
ICL_TRANSACTION_ID	VARCHAR2(120)	yes	Transaction ID of the transaction in which the record was committed in the source table. It's populated through dbms_transaction.local_transaction_id.
ICL_PROCESSED_IND	VARCHAR2(1)	yes	Indicates whether the record has been processed by the consuming application. The consuming application updates this to I (in-progress) or Y (processed). Default value is No.
<ENTITY SPECIFIC COLUMNS>			Source table to identify the entity that has changed. For example: for item_master, this will contain ITEM. For orders, the table will contain ORDER_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.

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).