| Oracle® Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs 16.0.024 E89599-02 |
|
 Previous |
 Next |
| Oracle® Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs 16.0.024 E89599-02 |
|
Previous |
Next |
This chapter contains information about the batch processes that related to the integration of Xstore.
The integration of the Merchandising applications and the Xstore Suite consists of two major data flows:
Foundation and price data from Oracle Retail Merchandising System (RMS) and Oracle Retail Price Management (RPM) to Oracle Retail Xcenter and Xstore Office
Point of Service transactions from Oracle Retail Xstore Point of Service to Oracle Retail Sales Audit (ReSA).
In combination, these data flows represent the round trip of data between the stores and headquarters. New items, other foundation data, and prices from headquarters are communicated to Xstore. Sales and returns from Xstore are communicated to Merchandising, where these transactions impact inventory. Merchandising further integrates summarized sales and inventory information from Xstore to other Oracle Retail applications, such as Planning and Analytics.
MS serves as the system of record for retail foundation data in the Oracle Retail enterprise. Many customers use RMS as the system of record for retail foundation data in their larger IT operations.
Foundation data needs to be integrated out of RMS to both Oracle Retail and 3rd party/legacy systems. RMS supports two categories of foundation data export:
RMS publishes near real time messages to the Oracle Retail Integration Bus (RIB) to client applications. These messages describe the changes (additions, modifications, deletes) that have occurred.
In the Oracle Retail enterprise, SIM and WMS subscribe to these foundation data messages to stay in synch with RMS foundation data.
In most implementations, customers configure other 3rd party systems to also subscribe to these messages.
See Oracle Retail Integration Guide for more information about RIB integration.
RMS creates files of foundation data information. Files can contain either
Changes (additions, modifications, deletes) since last bulk export
Full set of data for the entity
The goal of both forms of integration is to present complete entities to downstream systems in a neutral format. RMS expects that downstream systems will filter and transform the foundation data
There are some entity specific variations (detailed in the program specific details in this chapter), but RMS uses a general pattern for foundation data bulk export:
Pattern Conceptual Flow:
(1A) Using RMS application UI, business user or (1B) API/Batch Process performs an insert/update/delete on a System of Record table.
(2A) Trigger on SOR entity table fires on insert/update/delete. (2B) Trigger writes new/changed/deleted information to outbound staging table.
In a delta mode, program reads bulk export staging table to get recently created, modified and deleted records and writes them to a file. Records are marked as exported.
In a full mode, program reads all current records from the SOR table and writes them to a file. Note that recently deleted records are not part of the data set.
export_stg_purge.ksh drops aged partitions from the export outbound staging tables.
|
Note: If bulk extract programs are not run for some time, it is possible that delta records will be purges without having been exported. It is important to run these jobs daily. |
These bulk exports contain all information RMS knows about an entity that might be useful to downstream systems. It is the responsibility of integration code to drop unneeded information.
Naming convention for export staging tables is <entity>_EXPORT_STG. Examples include:
MERCHHIER_EXPORT_STG
ITEM_EXPORT_STG
Naming convention for triggers in SOR tables is de_table_<table abbreviation>_aiudr.trg
de_table_grp_aiudr.trg
de_table_dept_aiudr.trg
ITEM is a very complex entity. In addition to ITEM_EXPORT_STG, there is an additional helper table, ITEM_EXPORT_INFO. This table helps to ensure new items are complete before they are published.
The foundation data bulk export programs in this chapter are used in the integration between RMS and Xcenter/Xstore.
See implementation guide for details
In future releases, other bulk foundation data integration jobs will be deprecated in favor of these processes.
Oracle Retail recommends that these jobs also be used for
Initial load of data to 3rd party systems that will be operationally integrated using RIB.
File based Integration with 3rd party POS.
File based Integration with other 3rd party systems
The following batch designs are included in this functional area:
export_merchhier.ksh
export_orghier.ksh
export_stores.ksh
export_diffs.ksh
export_diffgrp.ksh
export_itemloc.ksh
export_itemvat.ksh
export_itemmaster.ksh
export_vat.ksh
export_relitem.ksh
export_stg_purge.ksh
| Module Name | export_merchhier.ksh |
| Description | Extraction of merchandise hierarchy data. |
| Functional Area | Foundation |
| Module Type | Integration |
| Module Technology | ksh |
| Catalog ID | 260 |
| Runtime Parameters | Database connection and mode of extract ('full' or 'delta') |
This batch job will extract new, updated and deleted RMS merchandise hierarchy information from division to subclass into a flat file. Data to be extracted will be pulled off from the MERCHHIER_EXPORT_STG table and the main merchandise hierarchy tables. The mode (full vs. delta) will be an input parameter for this new batch. The mode will allow a full extract (all merchandise hierarchy records in RMS) as well as delta processing (all merchandise hierarchy changes since the last export) of data.For a full extract, records will be solely retrieved from the main merchandise hierarchy tables. For a delta extract, the action type and entity ID will be retrieved from the MERCHHIER_EXPORT_STG table and the attributes of the entities will be retrieved from their corresponding man entity tables.
| Module Name | export_orghier.ksh |
| Description | Extraction of organizational hierarchy data. |
| Functional Area | Foundation |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS261 |
| Runtime Parameters | Database connection and mode of extract ('full' or 'delta') |
This batch job will extract new, updated and deleted RMS organizational hierarchy information from company to stores and warehouses into a flat file. Data to be extracted will be pulled off from the ORGHIER_EXPORT_STG table and the main organizational hierarchy tables. The mode (full vs. delta) will be an input parameter for this new batch. The mode will allow a full extract (all organizational hierarchy records in RMS) as well as delta processing (all organizational hierarchy changes since the last export) of data.For a full extract, records will be solely retrieved from the main organizational hierarchy tables. For a delta extract, the action type and entity ID will be retrieved from the ORGHIER_EXPORT_STG table and the attributes of the entities will be retrieved from their corresponding man entity tables.
| Module Name | export_stores.ksh |
| Description | Extraction of store data. |
| Functional Area | Foundation |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS263 |
| Runtime Parameters | Database connection and mode of extract ('full' or 'delta') |
This batch job will extract new, updated and deleted RMS store information into two flat files - one for store and one for store addresses. Data to be extracted will be pulled from the STORE_EXPORT_STG, STORE and ADDR tables. The mode (full vs. delta) will be an input parameter for this batch. The mode will allow a full extract (all store records in RMS) as well as delta processing (all store changes since the last export) of data.For a full extract, records will be solely retrieved from the STORE table for store information and ADDR table for store addresses. For a delta extract, the action type, store ID and address will be retrieved from the STORE_EXPORT_STG table and the details of the store will be retrieved from both the STORE and ADDR tables.
| Module Name | export_diffs.ksh |
| Description | Extraction of differentiator's data defined for a differentiator type. |
| Functional Area | Foundation |
| Module Type | Integration |
| Module Technology | ksh |
| Catalog ID | 256 |
| Runtime Parameters | Database connection and mode of extract ('full' or 'delta') |
This new batch job will extract new, updated and deleted RMS differentiator information into a flat file. Data to be extracted will be pulled off from the DIFFS_EXPORT_STG and the DIFF_IDS table.The mode (full vs. delta) will be an input parameter for this new batch. The mode will allow a full extract (all differentiator records in RMS) as well as delta processing (all differentiator record changes in the time frame passed in the program) of data. For a full extract, records will be solely retrieved from the DIFF_IDS table. For a delta extract, the action type and differentiator ID will be retrieved from the DIFFS_EXPORT_STG table and the attributes will be retrieved from the DIFF_IDS table.
| Module Name | export_diffgrp.ksh |
| Description | Extraction of differentiator groups data. |
| Functional Area | Foundation |
| Module Type | Integration |
| Module Technology | ksh |
| Catalog ID | RMS255 |
| Runtime Parameters | Database connection and mode of extract ('full' or 'delta') |
This new batch job will extract new, updated and deleted RMS diff group information into a flat file. Data to be extracted will be pulled off from the DIFFGRP_EXPORT_STG, DIFF_GROUP_HEAD and DIFF_GROUP_DETAIL tables. The mode (full vs. delta) will be an input parameter for this new batch. The mode will allow a full extract (all diff group records in RMS) as well as delta processing (all diff group record changes in the time frame passed in the program) of data. For a full extract, records will be retrieved from the DIFF_GROUP_HEAD and DIFF_GROUP_DETAIL tables. For a delta extract, the action type and diff group ID will be retrieved from the DIFFGRP_EXPORT_STG table and the attributes will be retrieved from the DIFF_GROUP_HEAD and DIFF_GROUP_DETAIL tables.
| Module Name | export_itemloc.ksh |
| Description | Extraction of item location data. |
| Functional Area | Foundation |
| Module Type | Integration |
| Module Technology | ksh |
| Catalog ID | RMS257 |
| Runtime Parameters | Database connection, mode of extract ('full' or 'delta') and threading indicator (Y/N).
With 'full/delta' mode optional parameter ('store number') for single store file. |
This batch job extracts new, updated and deleted RMS item-location information into a flat file.
This batch supports both a full and delta export of item-location data.
A threading indicator parameter should be passed. Passing 'Y' means a thread number (1-20) will be passed in. Passing 'N' means no thread number will be passed in and the program will use a default thread number.
An optional location parameter may be passed in for either modes. If this value is passed in, the batch will create a flat file for the location passed in. If it is not passed in, the batch will create flat files for all locations.
This creates separate files per location (Store, Warehouse or External Finisher).
This exports delta item header information for each applicable store location.
This will export data only for approved, sellable items.
This will export item location information from the ITEM_EXPORT_STG, ITEM_LOC and ITEM_LOC_TRAITS tables.
This should also include the item parent as its own record in the extract.
The flat files that will be created will now be pipe delimited.
The flat files that will be created will be pipe delimited.
Table 24-12 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
ITEM_EXPORT_INFO |
Yes |
No |
No |
No |
|
ITEM_EXPORT_STG |
Yes |
No |
Yes |
No |
|
ITEM_MASTER |
Yes |
No |
No |
No |
|
ITEM_LOC |
Yes |
No |
No |
No |
|
ITEM_MASTER |
Yes |
No |
No |
No |
|
ITEM_LOC_TRAITS |
Yes |
No |
No |
No |
|
STORE |
Yes |
No |
No |
No |
|
WH |
Yes |
No |
No |
No |
|
PARTNER |
Yes |
No |
No |
No |
|
DATA_EXPORT_HIST |
No |
Yes |
No |
No |
| Module Name | export_itemvat.ksh |
| Description | Extraction of vat item data. |
| Functional Area | Foundation |
| Module Type | Integration |
| Module Technology | ksh |
| Catalog ID | RMS259 |
| Runtime Parameters | Database connection and mode of extract ('full' or 'delta').
Threading indicator (Y/N). With 'full' mode optional parameter ('store') for single store file. |
This batch job will extract new, updated and deleted RMS item VAT information into a flat file.
This batch supports both a full and delta export of item VAT data.
A threading indicator parameter should be passed. Passing 'Y' means a thread number (1-20) will be passed in. Passing 'N' means no thread number will be passed in and the program will use a default thread number.
In full mode, normal operation will produce both a corporate level file and files for all stores. An optional input parameter will also allow the program to produce a location level file for a specified store.
In full mode for store specific file if store belong to such a vat region, which is exempt (In case of tax type SVAT), then files for that store won't get generated.
In delta mode, this will produce both corporate level files and files for all stores the modified items are ranged to and the vat region the store is associated with.
In delta mode for store specific file if store belong to such a vat region, which is exempt, then files for that store won't get generated.
This will export data only for approved, sellable items.
This will export item VAT information from the ITEM_EXPORT_STG and VAT_ITEM tables.
This should also include the item parent as its own record in the extract.
The flat files that will be created will now be pipe delimited.
| Module Name | export_itemmaster.ksh |
| Description | Extraction of item data |
| Functional Area | Foundation |
| Module Type | Integration |
| Module Technology | ksh |
| Catalog ID | RMS258 |
| Runtime Parameters | Database connection and mode of extract ('full' or 'delta'). Threading indicator (Y/N).
With 'full' mode optional parameter ('store') for single store file. |
This new batch job will extract new, updated and deleted RMS item master information into a flat file.
Data to be extracted will be pulled off from the ITEM_EXPORT_INFO, ITEM_EXPORT_STG and ITEM_MASTER tables.
The mode (full vs. delta) will be an input parameter for this new batch. The mode will allow a full extract (all approved, sellable items in RMS) as well as delta processing (all approved, sellable item changes in ITEM_MASTER since the last export) of data.
A threading indicator parameter should be passed. Passing 'Y' means a thread number (1-20) will be passed in. Passing 'N' means no thread number will be passed in and the program will use a default thread number.
In full mode, normal operation will produce both a corporate level file and files for all stores. An optional input parameter will also allow the program to produce a location level file for a specified store.
In delta mode, the only option is to produce corporate level files. Item header files at the store level will be created in the export_itemloc.ksh for delta mode.
The store specific file will also include UPC items. To determine which UPC Items to include, the store where the UPC's parent and/or grandparent item is ranged should be taken into consideration.
The flat files that will be created will now be pipe delimited.
Table 24-16 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
ITEM_EXPORT_INFO |
Yes |
No |
Yes |
No |
|
ITEM_EXPORT_STG |
Yes |
No |
Yes |
No |
|
CLASS |
Yes |
No |
No |
No |
|
SUBCLASS |
Yes |
No |
No |
No |
|
SYSTEM_OPTIONS |
Yes |
No |
No |
No |
|
DIFF_IDS |
Yes |
No |
No |
No |
|
DIFF_GROUP_HEAD |
Yes |
No |
No |
No |
|
STORE |
Yes |
No |
No |
No |
|
ITEM_MASTER |
Yes |
No |
No |
No |
|
ITEM_LOC |
Yes |
No |
No |
No |
|
DATA_EXPORT_HIST |
No |
Yes |
No |
No |
| Module Name | export_vat.ksh |
| Description | Extraction of vat data |
| Functional Area | Foundation |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS264 |
| Runtime Parameters | Database connection and mode of extract ('full' or 'delta') |
This batch job will extract new, updated and deleted RMS VAT information into a flat file. Data to be extracted will be pulled off from the VAT_EXPORT_STG, VAT_REGION, VAT_CODES and VAT_CODE_RATES tables.
The mode (full vs. delta) will be an input parameter for this new batch. The mode will allow a full extract (all vat region/vat code/vat code rate combination records in RMS) as well as delta processing (all VAT record changes in the time frame passed in the program) of data.
In either of the mode exempt vat region won't get fetched in case of SVAT tax type.
For a full extract, records will be retrieved from the VAT_REGION, VAT_CODE and VAT_CODE_RATES tables. For a delta extract, the action type, vat region, vat code and active date will be retrieved from the VAT_EXPORT_STG table and the attributes will be retrieved from the main table.
| Module Name | export_relitem.ksh |
| Description | Extraction of related item data |
| Functional Area | Foundation |
| Module Type | Integration |
| Module Technology | ksh |
| Catalog ID | RMS262 |
| Runtime Parameters | Database connection and mode of extract ('full' or 'delta'). Threading indicator (Y/N).
With 'full' mode optional parameter ('store') for single store file. |
This batch job will extract new, updated and deleted RMS related items information into a flat file.
This batch will support both a full and delta export of related item data.
A threading indicator parameter should be passed. Passing 'Y' means a thread number (1-20) will be passed in. Passing 'N' means no thread number will be passed in and the program will use a default thread number.
In full mode, normal operation will produce both a corporate level files and files for all stores. An optional input parameter will also allow the program to produce location level files for a specified store.
In delta mode, this will produce both corporate level files and files for all stores the modified data are ranged to.
This will export data only for approved, sellable items.
This will export item related item information from the RELITEM_EXPORT_STG, RELATED_ITEM_HEAD and RELATED_ITEM_DETAIL tables.
Two types of flat files will be created for this extract - one for the related item header information (those from the RELATED_ITEM_HEAD table) and one for the related item detail information (those from the RELATED_ITEM_DETAIL table).
When creating the location level files, ensure that both items (the main item and related item) are ranged in the location.
The flat files that will be created will now be pipe delimited.
| Integration Type | Extract from RMS |
| File Name | relitemhead_date_corp_[full/delta]_[#ofLines].dat
relitemhead_date_[Location]_[full/delta]_[#ofLines].dat relitemdet_date_corp_[full/delta]_[#ofLines].dat relitemdet_date_[Location]_[full/delta]_[#ofLines].dat |
| Integration Contract | IntCon000210
IntCon000211 |
| Module Name | export_stg_purge.ksh |
| Description | Purging of all the extracted records (week old) for Xstore. |
| Functional Area | Foundation |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS265 |
| Runtime Parameters | Database connection. |
This batch job will be used to remove records that are a week old from the following staging tables.
MERCHHIER_EXPORT_STG
ORGHHIER_EXPORT_STG
STORE_EXPORT_STG
DIFFS_EXPORT_STG
DIFFGRP_EXPORT_STG
ITEM_EXPORT_STG
VAT_EXPORT_STG
RELITEM_EXPORT_STG
DATA_EXPORT_HIST
Batch will purge all the records (Week old records) from its respective staging table whether data get extracted or not.
Table 24-22 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
MERCHHIER_EXPORT_STG |
No |
No |
No |
Yes |
|
ORGHIER_EXPORT_STG |
No |
No |
No |
Yes |
|
STORE_EXPORT_STG |
No |
No |
No |
Yes |
|
DIFFS_EXPORT_STG |
No |
No |
No |
Yes |
|
DIFFGRP_EXPORT_STG |
No |
No |
No |
Yes |
|
ITEM_EXPORT_STG |
No |
No |
No |
Yes |
|
VAT_EXPORT_STG |
No |
No |
No |
Yes |
|
RELITEM_EXPORT_STG |
No |
No |
No |
Yes |
|
DATA_EXPORT_HIST |
No |
No |
No |
Yes |
