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 number to closed if all ALLOC_HEADER records have been closed.