2 Integration
This chapter discusses the integration among Oracle Retail Allocation and other systems and it provides the following:
-
An integration interface allocation-related dataflow across the enterprise.
-
The tables and triggers that are in external systems or related to external systems that Allocation uses (for example, Merchandising).
-
A functional description of Merchandising dependencies and assumptions that affect Oracle Retail Allocation.
-
Information necessary to integrate Oracle Retail Allocation and Oracle Retail Workspace.
Allocation Integration Overview
This section provides an overview as to how Oracle Retail Allocation is functionally integrated with other systems (including other Oracle Retail systems). The discussion primarily concerns the flow of allocation-related business data across the enterprise.
Figure 2-1 Oracle Retail Allocation-Related Dataflow
![Oracle Retail Allocation-Related Dataflow Oracle Retail Allocation-Related Dataflow](img/alloc_integ_diag.png)
Note:
Oracle Retail Allocation pulls the data from the Merchandising tables using the JDBC connection.
The diagram above shows the overall direction of the dataflow among the products. The accompanying explanations are written from a system-to-system perspective, illustrating the movement of data.
From Oracle Retail Demand Forecasting
Oracle Retail Allocation pulls the following data from Merchandising:
-
Forecasting data: Oracle Retail Allocation accesses forecasting data that originates in the Oracle Retail Demand Forecasting (RDF) system. RDF is Oracle Retail's statistical and causal forecasting solution. It uses state-of-the-art modeling techniques to produce high quality forecasts with minimal human intervention. RDF is an application that resides on the Oracle Retail Predictive Application Server (RPAS). Oracle Retail Allocation uses forecasting data as a basis for calculating gross need and can access the following five levels of forecasting data: department, class, subclass, style-color and item.
From Planning
Oracle Retail Allocation accesses plan data that originates in planning applications (including Oracle Retail's planning applications). The Oracle Retail Planning products are applications that provide functionality for developing, reconciling, and approving plans. When interfacing with Oracle Retail planning applications, Oracle Retail Allocation accesses department, class, subclass, parent/diff, or SKU plan data at the store-week level. Oracle Retail Allocation can be used as a tool to verify the final product-store plans and to initiate a PO to execute the plan. In other words, Oracle Retail Allocation can take the retailer's plan and execute it. Both the Oracle Retail and the legacy planning applications populate a planning table, ALC_PLAN, which resides within Oracle Retail Allocation.
Note:
Merchandising is the system of record for Oracle Retail Allocation. Hence Allocation inherits the merchandise hierarchy from Merchandising. However, Merchandising and Assortment Planning (AP) can have different merchandise hierarchies. If you wish to export information from AP to Allocation, you must ensure that the AP merchandise hierarchy is compatible with that of Merchandising/Allocation.
From Size Profiles
Oracle Retail Allocation uses size profile data from planning or science based solutions, like Oracle Retail Size Profile Optimization (SPO). The size profile data can be imported at the following levels: department level, class level, sub-class level, and/or item level. The data for all the levels are imported in a single file. For more information, see the "Planning and Size Profile Integration" chapter.
From Warehouse Management System
Allocation does not integrate with a WMS directly. Instead, it leverages its integration with Merchandising to have access to the following data:
-
Appointments
Appointment data is one source that identifies item(s) to be allocated.
-
Warehouse inventory
-
ASNs, BOLs, and Transfers
From Oracle Retail Pricing
Pricing provides the following to Allocation:
-
Future Retail Price Data - Oracle Retail Allocation has the ability to get a real time price from Pricing as it is integrated directly with Pricing. Allocation uses this data to provide you with the future retail price value of the entire allocation (based on its quantities). In addition, you can access future retail price values by location and by item.
From Merchandising
Note:
Item, purchase order, supplier, sales and other data are accessed directly from the Merchandising tables, with no need to interface data via batch modules.
-
Items
-
Purchase Orders
-
Hierarchy data
-
Sales history data (for items, user-defined attributes (UDA), warehouses, stores, and so on)
-
Foundation data (supplier data, shipping tables, and so on)
From Allocation to Merchandising
Oracle Retail Allocation calculates the allocation based on the information it has received from Merchandising. Once the retailer reviews and approves the allocation, Oracle Retail Allocation sends the following information back to Merchandising:
-
Approved or reserved allocation data
-
Worksheet status POs that contain product, supplier and quantity information (the only remaining actions to be taken in Merchandising are to approve the PO and, if desired, to truck scale the PO.) These worksheet status purchase orders may be created or updated from within the Oracle Retail Allocation front end.
Based upon the approved allocation information from Oracle Retail Allocation, Merchandising sends the following information to the warehouse management system:
-
Approved allocation data represents the store quantity instructions for allocating a specific quantity of stock at the store level.
Persistence Layer Integration
This section addresses Oracle Retail Allocation's persistence layer method of integration:
Persistence Layer Integration (Including Tables and Triggers)
Tables Populated by External Systems
The following tables are owned by Oracle Retail Allocation. The data within them is populated by external systems. For descriptions of each table and its columns, see the Oracle Retail Allocation Data Model.
-
ALC_CORPORATE_RULE_DETAIL
-
ALC_CORPORATE_RULE_HEAD
-
ALC_IDEAL_WEEKS_OF_SUPPLY
-
ALC_PLAN
-
ALC_RECEIPT_PLAN
-
ALC_SIZE_PROFILE
-
Can also be populated through size profile setup via the front end of the application.
-
Planning Table in Oracle Retail Allocation
Planning applications populate a planning table, ALC_PLAN, that resides within Oracle Retail Allocation. This table includes the following columns:
-
Plan ID
-
Location
-
EOW date
-
Department
-
Class
-
Subclass
-
Item
-
Diff1_id, Diff2_id, Diff3_id, Diff4_id
-
Quantity
A record can thus exist at any of the following levels by week-store-quantity:
-
Department
-
Department-class
-
Department-class-subclass
-
Item-color
-
Item
The ALC_RECEIPT_PLAN table includes the following columns:
-
Plan ID
-
Loc
-
EOW.date
-
Department
-
Class
-
Subclass
-
Item
-
Diff1_id, Diff2_id, Diff3_id, Diff4_id
-
Quantity
A record can thus exist at any of the following levels by week-store-quantity:
-
Department
-
Department-class
-
Department-class-subclass
-
Item-color
-
Item
-
Pack
Merchandising Interface Tables
Oracle Retail Allocation and Merchandising share certain database tables and processing logic. This integration provides the following two important benefits:
-
The number of interface points that need to be maintained is minimized.
-
The amount of redundant data (required if the rest of the Oracle Retail product suite is installed) is limited.
Oracle Retail Allocation exchanges data and processing with Merchandising in four ways:
-
By reading directly from Merchandising tables.
-
By directly calling Merchandising packages.
-
By reading Oracle Retail Allocation views based on Merchandising tables.
-
Oracle Retail Allocation triggers reside in Merchandising tables. These triggers cause actions (create, delete, update) on Merchandising tables based on Oracle Retail Allocation business rules.
Merchandising Tables used by Allocation
The following table illustrates the tables from which Oracle Retail Allocation gets its data from Merchandising.
Table 2-1 Merchandising Tables Used by Allocation
Merchandising Tables | |
---|---|
Functional Area |
Associated Tables |
Item data |
SUB_ITEMS_HEAD |
SUB_ITEMS_DETAIL |
|
ITEM_MASTER |
|
ITEM_SUPP_COUNTRY |
|
ITEM_SUPPLIER |
|
ITEM_LOC |
|
ITEM_LOC_HIST |
|
ITEM_LOC_SOH |
|
ITEM_PARENT_LOC_HIST |
|
Skulist data |
SKULIST_HEAD |
SKULIST_DETAIL |
|
Pack data |
PACKITEM |
ITEM_MASTER |
|
ITEM_LOC |
|
Order data |
ORDHEAD |
ORDLOC_WKSHT |
|
ORDLOC |
|
ORDSKU |
|
ALLOC_HEADER |
|
ALLOC_DETAIL |
|
SHIPMENT |
|
Supplier data |
SUPS |
ITEM_SUPPLIER |
|
Location list data |
LOC_LIST_HEAD |
LOC_LIST_DETAIL |
|
LOC_LIST_CRITERIA |
|
Merchandise hierarchy data |
DEPS |
CLASS |
|
SUBCLASS |
|
ITEM_PARENT |
|
DIFF |
|
SKU |
|
Organizational hierarchy data |
STORE |
WH |
|
WH_STORE_ASSIGN |
|
Shipment data |
SHIPMENT |
SHIPSKU |
|
Store grade data |
STORE_GRADE_GROUP |
STORE_GRADE |
|
STORE |
|
BUYER |
|
STORE_GRADE_STORE |
|
Location traits data |
LOC_TRAITS |
LOC_TRAITS_MATRIX |
|
Transfer data |
TSFHEAD |
TSFDETAIL |
|
User defined attribute (UDA) data |
UDA |
UDA_VALUES |
|
UDA_ITEM_LOV |
|
Forecast data |
DEPT_SALES_FORECAST |
CLASS_SALES_FORECAST |
|
SUBCLASS_SALES_FORECAST |
|
ITEM_FORECAST |
|
Sales data |
DEPT_SALES_HIST |
CLASS_SALES_HIST |
|
SUBCLASS_SALES_HIST |
|
ITEM_LOC_HIST |
|
ITEM_PARENT_LOC_HIST |
|
Appointment data |
APPT_HEAD |
APPT_DETAIL |
Oracle Retail Allocation-Owned Triggers Residing on Merchandising Tables
Table 2-2 Triggers
Triggers | Details |
---|---|
ALC_TABLE_ALD_AUR - 1 - 4 |
This trigger is involved in the following processing: Whenever a portion of an allocation order is worked on by the distribution center by selecting, distributing, transferring or receiving inventory, the allocation within Oracle Retail Allocation is placed into a 'Processed' status. The user can no longer change that allocation in Oracle Retail Allocation. |
ALLOC_STATUS_TRIGGER |
This trigger is on the Merchandising table ALLOC_HEADER. The trigger updates the status in Oracle Retail Allocation table ALC_ALLOC to 4 (closed). This trigger is fired only if the status on Merchandising table ALLOC_HEADER is updated to 'C' (closed). |
ALLOC_STATUS_TRIGGER_AU |
The closure logic within the Oracle Retail Allocation application accounts for the multiplicity between ALLOC_HEADER records and the Oracle Retail Allocation (ALC_XXX) tables. The table triggers only set a Oracle Retail Allocation allocation number to closed if all ALLOC_HEADER records have been closed. |