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

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.