L Appendix: Third Party Pricing Integration

Overview

By default EICS integrate with RPCS clearance and regular price change via BDI process flow.

EICS does not integrate with Promotion Offer interface due to it does not provide pricing at item/date level, so there is nothing for SIOCS to consume.

As an alternative, SIOCS does have a generic pricing interface for the retailer to integrate promotion price into.

Third Party Pricing Import provides a generic pricing interfaces for retailer which can provide pricing data for EICS pricing imports from third party. This third party pricing import provides an alternative for import promotion data into EICS.

BDI Implementation Considerations

To load third party data into EICS via BDI, external system need to provide data sourcing implementations.

There are two approaches for data sourcing implementations, Integration Implementer may choose the data provider implementations based on their need.

Option A: Third Party Pricing Integration Via BDI External Process Flow Implementation

Figure L-1 BDI Process Flow Process

BDI Process Flow Process

The BDI process:

  • An Extractor extracts data from a data producing system into an outgoing intermediate integration interface table.

  • A Downloader and transporter services data to receiver service.

  • An Uploader uploads the data to an incoming intermediate integration interface tables.

  • An Importer imports the data from the interface stage tables into SIOCS application tables.

For more information about data seeding, seeData Seeding.

With this approach, the BDI Ext Process Flow will move the data from sourcing system to destination system's application table. BDI process flow provides BDI job scheduling, end to end data transportation from sourcing system's database table into destination application's tables.

Customer need to implement the BDI Ext-EdgeApp to move data into the BDI outbound interface table.

BDI Process Flow: ExtPrice_Tx_ProcessFlow_From_EXTERNAL

See Oracle Retail Enterprise Integration Overview Guide: BDI Third Party Integrations.

Option B: Third Party Pricing Integration Via BDI CLI File Transmitter

BDI CLI File Transmitter is a client command line tool, is applicable for smaller data file size which does not require concurrent processing.

BDI CLI file transmitter only transport data files from sourcing systems to destination system's interface tables. It does not provide end to end data transportation. If you choose BDI CLI File transmitter for load third party data into EICS application table, it would require three steps:

Figure L-2 BDI CLI File Transmitter

BDI CLI File transmitter

  • Download BDI CLI File Transmitter and install on customer's system.

  • Run BDI CLI Transmitter batch to load data file from sourcing system into destination system's interface table.

  • Once the data are loaded into receiver app's inbound interface tables, EICS's importer job will move the data from inbound interface table into EICS app tables.

Note:

By default, batch scheduler Third Party Price Import in EICS is disabled; this job should only be enabled if you choose to use BDI CLI File transmitter for data sourcing provider.

See Integration.

This section provides BDI CLI File Transmitter as an implementation to describe how to import third partying pricing into EICS via BDI CLI File Transmitter.

Third Party Pricing Import via BDI CLI File Transmitter

Each file contains price records for a single store, store/item/action date uniquely identify a pricing record.

1. BDI CLI File Transmitter Process

The BDI CLI Transmitter process load data files to an intermediate BDI inbound tables in SIOCS BDI Schema.

Once the records are created on the SIOCS BDI table, BDI process also create a control record for the module and interface for importer to process. The module interface along with the data set id will be used by importer to identify the records to be imported into the EICS in importer process.

See Appendix: BDI Transmitter CLI Procedure - Load External Data Filefor details on how to run BDI CLI File Transmitter.

2. EICS Importer Process

Once the file data is loaded into the SIOCS BDI inbound interface table, the importer process will process the BDI interface tables into SIOCS application tables. You can setup SIOCS Batch job schedule to import data from inbound interface tables into EICS application tables. For importer details, see Third Party Pricing Import in batch chapter for details.

3. Job Scheduling

You can schedule EICS job scheduler to start the third party price TX importer job automatically, or You can run importer as adhoc job.

By default, EICS batch scheduling for third party pricing TX import job is disabled, as for customers who choose BDI External Process Flow for third party pricing end to end integration, the BDI Job scheduler should be used.

4. Error Handling and Logging

When the job execution failed, only the failed record reference ids are stored in the BATCH_DATA_DETAIL table, the data which are successfully processed are removed from the BDI inbound table. Sys ops user can view the batch detail errors via EICS Job admin console. The number of maximum errors (for example 10 errors) per partition are logged for each batch/partition run.

EXT_PRICE_IN

Field Name Description Required Type
BDI_SEQ_ID Bulk Data Integration data sequence. Yes NUMBER(19)
BDI_APP_NAME Bulk Data Integration Application Name. Yes VARCHAR2(50)
BDI_DATASET_TYPE Bulk Data Integration Data Set Type, valid values are FULL, or PARTIAL. Yes VARCHAR2(20)
BDI_DATASET_ACTION EICS only support REPLACE OR DELETE as dataset action for third party pricing, UPDATE type is not supported, use replace for updating a record. Yes VARCHAR2(20)
ITEM_ID The unique alphanumeric value for the transaction level item. Yes VARCHAR2(25)
STORE_ID The number that uniquely identifies the store. Yes Number(10)
EFFECTIVE_DATE The date on which the price change went effect. Yes Date
END_DATE Promotion or clearance price end date.

For price change with an end date, if the clearance indicator is 'Y', the end date is for clearance end date; otherwise the end date is promotional ending date.

 No Date
PRICE_TYPE The item price type. Valid values:

200- Clearance

201- Promotional

202- Regular

 Yes NUMBER(3)
PROMOTION_NAME Promotion name. No VARCHAR2(160)
SELLING_UNIT_RETAIL Contains the current single unit retail in the selling unit of measure. Yes NUMBER(20,4)
SELLING_UOM Contains the selling unit of measure for an items single-unit retail. Yes VARCHAR2(4)
SELLING_UNIT_RETAIL_CURRENCY Contains the selling unit retail currency. Yes VARCHAR2(3)
MULTI_UNITS Contains the current multi-units. If the record is being written as a result of a change in the multi-unit retail, then this field contains the new multi-units. No NUMBER(12,4)
MULTI_UNIT_RETAIL Contains the current multi-unit retail in the selling unit of measure. No NUMBER(20,4)
MULTI_UNIT_RETAIL_CURRENCY Contains the multi-unit retail currency. No VARCHAR2(3)
MULTI_UNIT_SELLING_UOM Contains the selling unit of measure for an items multi-unit retail. No VARCHAR2(4)
CREATE_DATETIME Contains the record creation date. No Date
REC_ID The id of the record. Yes NUMBER(15)
RETAIL_CHANGE_IND Indicates whether the retail changed with this price change. Valid values are:

0 - retail price not changed

1 - retail price changed

 No NUMBER(6)
MULTI_UNIT_IMPACT Indicates if the Price Change has impact to Multi Unit retail. Valid value are:

AU - Multi Unit information is added or updated

R - Multi Unit information is removed

N - Multi unit information is not changed.

 Yes VARCHAR2(4)
PRICE_EVENT_ID The id of the price event. No NUMBER(15)

External Price CSV File Fields

The CSV data fields matches the non-BDI heading columns in EXT_PRICE_IN (four BDI heading columns are: BDI_SEQ_ID, BDI_APP_NAME, BDI_DATASET_TYPE, BDI_DATASET_ACTION).

For example, the first fields in the file would matches the fifth column in EXT_PRICE_IN table, the second field in the file matches the sixth column in the table.

Sample CSV File:

ext_price_<YYYYMMDDHHMMSS>.csv

"100637113",5000,2018-07-03T00:00:00,,202,"",49.99,"USD","EA",,,"","",2018-10-12 12:00:00.000000000"100637148",5000,2018-07-03T19:00:00,,202,"",49.99,"USD","EA",,,"","",2018-10-12 12:00:00.000000000

File Contents Explanation

  • Create CSV file: ext_price _<YYYYMMDDHHMMSS>.csv

  • It is expected that the pricing provider to ensure the record uniqueness (A unique record is identified by store/item/effective date time), within a file, each record must be unique. The record action is denoted by action type, only a dataset action is allowed for unique store/item/date, for example, for store 5000, item A, a price on date 2018 Dec 10 00:00:00 record in the file can be one of the (REPLACE, DELETE), the same record with more than one dataset action will be rejected. EICS only support REPLACE OR DELETE as dataset action for third party pricing, UPDATE type is not supported, use replace for updating a record.

  • To end clearance price: To end a clearance price for a store/item, the pricing provider can either include the end date on the clearance item record; or sending a new price record (regular or promotional).

  • The same file can't have two records with this combination store/item/effective with different price type, if clearance need to be on today, then this file should only have a single record for clearance type; to end the clearance (for example, tomorrow), the file need either mark the end date for that clearance or need to include a regular price record with a different effective date (for example, tomorrow).

  • In EICS, there is no client UI which needed the prom id, clearance id or price change id. For data import integration backend processing, the record is uniquely identified by item/store/effective date time and price type external pricing change identifier has no meaning to our system. Promotion name is used in EICS as context type, therefore include in the integration interface.

  • Split the Data into Multiple Files

    EICS loads the data in parallel from multiple files. Loading files from multiple files in parallel provides performance advantage than loading from a single file. It is recommended to file provider to split the data into multiple files to load data efficiently in parallel loading, each file contains single store is recommended.

  • Compress the data files

    If data file contains large datasets, it is recommended that compress the load files individually, when load the data file. Use EICS System Configuration Console to specify the file suffix (for example, gzip, or zip).