Skip Headers
Oracle® Retail Advanced Science Engine Implementation Guide
Release 14.1
E59126-02
  Go To Table Of Contents
Contents

Previous
Previous
 
Next
Next
 

E Retail Analytics Interfaces Files

Note that ORASE consumes this data directly from RADM and is not needed if RADM is properly configured and populated. It is included purely for reference.

Interfaces

Product Hierarchy

The hierarchy that groups products into related groups.

Table E-1 Product Hierarchy File

Field Type Description

Merchandise Hierarchy External ID

Text

Customer-visible Merchandise ID, used to denote any non-leaf node of the product hierarchy. (Source: lvl#anc_prodcat_id or top_lvl_prodcat_id from w_prod_cat_dh)

Merchandise Hierarchy PK

Text

Primary key used to identify a level of the merchandise hierarchy. (Source: ROW_WID from w_prod_cat_dh)

Merchandise Hierarchy External Key

Text

Externally suitable integration ID for the merchandise hierarchy. (Source integration_id from w_prod_cat_dh)

Effective From Date

Text

Starting date the source record is effective from. (Source: effective_from_dt in w_prod_cat_dh)

Effective To Date

Text

Ending date the source record is effective from. (Source: effective_to_dt in w_prod_cat_dh)

Current Flag

Text

Y/N flag to indicate if the record is considered a currently active hierarchy record. (Source: current_flg in w_prod_cat_dh)

Delete Flag

Text

Y/N flag to indicate if the record is considered deleted. (Source: delete_flg in w_prod_cat_dh)

Merchandise Hierarchy Name

Text

Name of the merchandise hierarchy. (Source: domain_member_name from w_domain_member_lkp_tl)

Merchandise Hierarchy Description

Text

Description of the merchandise hierarchy. (Source: domain_member_descr from w_domain_member_lkp_tl)


Product

Product is the terminal node of the product/merchandise hierarchy.

Table E-2 Product File

Field Type Description

Merchandise External ID

Text

External Merchandise ID, used to denote the leaf node of the product hierarchy. (Source:prod_num from w_product_d)

Merchandise PK

Text

Primary key used to identify a SKU of the merchandise hierarchy. (Source: SCD1_WID from w_product_d)

Merchandise External Key

Text

Externally suitable integration ID for the merchandise hierarchy. (Source integration_id from w_product_d)

Parent Product Hierarchy ID

Text

FK to the parent hierarchy this product belongs to. (Source prod_cat5_wid from w_product_d)

Effective From Date

Date

Starting date the source record was effective from. (Source: effective_from_dt in w_product_d)

Effective To Date

Date

Ending date the source record was effective from. (Source: effective_to_dt in w_product_d)

Current Flag

Text

Y/N flag to indicate if the record is considered a currently active hierarchy record. (Source: current_flg in w_product_d)

Delete Flag

Text

Y/N flag to indicate if the record is considered deleted. (Source: delete_flg in w_product_d)

Merchandise Name

Text

Name of the SKU. (Source: product_name from w_product_d_tl)

Merchandise Description

Text

Description of the SKU. (Source: product_descr from w_product_d_tl)


Product Attributes

Generic attributes for products.

Table E-3 Product Attributes File

Field Type Description

Merchandise PK

Text

Primary key used to identify a SKU of the merchandise hierarchy.

Attribute Number Value

Number

Numeric value for a generic product attribute. (Source: product_attr#_num_value from w_product_attr_d)

Attribute Text Value

Text

Text value for a generic product attribute. (Source: product_attr#_name from w_product_attr_d)


User Defined Product Attributes

User defined attributes for products.

Table E-4 User Defined Product Attributes File

Field Type Description

Merchandise PK

Text

Primary key used to identify a SKU of the merchandise hierarchy.(Source: prod_scd1_wid from w_rtl_item_grp1_d)

Attribute ID

Text

External ID for the UDA. (Source: flex_attrib_1_char from w_rtl_item_grp1_d)

Attribute Value ID

Text

External ID for the UDA value. (Source: flex_attrib_4_char from w_rtl_item_grp1_d)

Attribute Value Descr

Text

Descriptive value of the attribute value. (Source: Domain_member_name from w_rtl_domain_member_lkp_tl where domain_code = ITEM_UDA)


Organization Hierarchy

A hierarchy under which the organization can be organized.

Table E-5 Organization Hierarchy File

Field Type Description

Organization Hierarchy External ID

Text

Customer visible ID used to identify an organization hierarchy level member. (Source: org_top_num/org_hier#_num columns from w_int_org_dh)

Organization Hierarchy PK

Text

Primary key used to identify a level of the organization hierarchy. (Source: scd1_wid from w_int_org_dh)

Organization Hierarchy External Key

Text

Externally suitable integration ID for the location hierarchy. (Source integration_id from w_int_org_dh)

Effective From Date

Date

Starting date the source record was effective from. (Source: effective_from_dt in w_int_org_dh)

Effective To Date

Date

Ending date the source record was effective from. (Source: effective_to_dt in w_int_org_dh)

Current Flag

Text

Y/N flag to indicate if the record is considered a currently active hierarchy record. (Source: current_flg in w_int_org_dh)

Delete Flag

Text

Y/N flag to indicate if the record is considered deleted. (Source: delete_flg in w_int_org_dh)

Organization Hierarchy Name

Text

Name of the organization hierarchy. (Source: domain_member_name from w_domain_memeber_lkp_tl)

Organization Hierarchy Description

Text

Description of the organization hierarchy. (Source: domain_member_descr from w_domain_member_lkp_tl)


Store Locations

Store locations is the terminal node of the organization hierarchy

Table E-6 Store Locations File

Field Type Description

Location External ID

Text

Customer visible ID used to identify an organization location. (Source: Appropriate org_hier#_num column from w_int_org_dh)

Location PK

Text

Primary key used to identify a store of the location hierarchy. (Source: SCD1_WID from w_int_org_dh)

Location External Key

Text

Externally suitable integration ID for the location hierarchy. (Source integration_id from w_int_org_dh)

Parent Organization Hierarchy ID

Text

FK to the organization hierarchy this location belongs to. (Source: appropriate org_hier#_num column from w_int_org_dh)

Effective From Date

Date

Starting date the source record was effective from. (Source: effective_from_dt in w_int_org_dh)

Effective To Date

Date

Ending date the source record was effective from. (Source: effective_to_dt in w_int_org_dh)

Current Flag

Text

Y/N flag to indicate if the record is considered a currently active hierarchy record. (Source: current_flg in w_int_org_dh)

Delete Flag

Text

Y/N flag to indicate if the record is considered deleted. (Source: delete_flg in w_int_org_dh)

Location Name

Text

Name of the Location. (Source: org_name from w_int_org_d_tl)

Location Description

Text

Description of the Location. (Source: org_descr from w_int_org_d_tl)


Location Attributes

Generic attributes for locations.

Table E-7 Location Attributes File

Field Type Description

Location PK

Text

Primary key used to identify a store of the location hierarchy.

Attribute Number Value

Number

Numeric value for a generic location attribute. (Source: org_attr#_num_value from w_int_org_attr_d)

Attribute Text Value

Text

Text value for a generic location attribute. (Source: org_attr#_name from w_int_org_attr_d)


Customer Segments

The list of customer segments that enable aggregate processing of customer data.

Table E-8 Customer Segments File

Field Type Description

Customer Segment External ID

Text

Customer visible ID used to identify an customer segment. (Source: custseg_id from w_rtl_custseg_d)

Customer Segment PK

Text

Primary key used to identify a customer segment. (Source: row_wid from w_rtl_custseg_d)

Customer Segment External Key

Text

Externally suitable integration ID for the customer segment. (Source integration_id from w_rtl_custseg_d)

Effective From Date

Date

Starting date the source record was effective from. (Source: effective_from_dt in w_rtl_custseg_d)

Effective To Date

Date

Ending date the source record was effective from. (Source: effective_to_dt in w_rtl_custseg_d)

Current Flag

Text

Y/N flag to indicate if the record is considered a currently active hierarchy record. (Source: current_flg in w_rtl_custseg_d)

Delete Flag

Text

Y/N flag to indicate if the record is considered deleted. (Source: delete_flg in w_rtl_custseg_d)

Customer Segment Name

Text

Name of the customer segment. (Source: custseg_name from w_rtl_custseg_d)


Customer Segment Customer Members

Associates customer IDs to customer segment IDs, so that a customer's identity can be classified under a customer segment.

Table E-9 Customer Segment Customer Members File

Field Type Description

Customer Segment ID

Text

ID of the customer segment (Source: custseg_hier_id from w_rtl_cust_custseg_d)

Customer ID

Text

ID of the customer (Source: customer_id from w_rtl_cust_custseg_d)


Customer Segment

This interface provides the hierarchy members for the Consumer Segment dimension.

Table E-10 Customer Segment File

Field Type Description

Consumer Segment External ID

Text

Customer visible ID used to identify a Consumer Segment.

Consumer Segment PK

Text

Primary key used to identify a Consumer Segment.

Consumer Segment External Key

Text

Externally suitable integration ID for the Consumer Segment.

Effective From Date

Date

Starting date the source record was effective from.

Effective To Date

Date

Ending date the source record was effective from.

Current Flag

Text

Y/N flag to indicate if the record is considered a currently active record.

Delete Flag

Text

Y/N flag to indicate if the record is considered deleted.

Consumer Segment Name

Text

Name of the Consumer Segment.


Consumer Segment/Location/Product Mapping

An allocation of store locations to any number of product hierarchy members per consumer segment.

Table E-11 Consumer Segment/Location/Product Mapping File

Field Type Description

Consumer Segment PK

Text

Primary key of a consumer segment.

Location PK

Text

Primary key used to identify a store location.

Product Hierarchy PK

Text

Primary key used to identify a member of the product hierarchy.

Allocation Percent

Number

Percent of this location and product intersection that is allocated to this consumer segment.


Fiscal Year

Fiscal calendar year level data.

Table E-12 Fiscal Year File

Field Type Description

Fiscal Year PK

Text

Primary Key suitable for identifying the fiscal year. (Source: row_wid from w_mcal_year_d)

Fiscal Year

Number

Externally known identifier for the fiscal year. (Source: mcal_year from w_mcal_year_d)

Fiscal Year Name

Text

The name associated with the fiscal year. (Source: mcal_per_name_year from w_mcal_year_d)

Start Date

Date

The date the fiscal year starts. (Source: mcal_year_start_dt from w_mcal_year_d)

End Date

Date

The date the fiscal year ends. (Source: mcal_year_end_dt from w_mcal_year_d)


Fiscal Quarter

Fiscal calendar's quarter level data.

Table E-13 Fiscal Quarter File

Field Type Description

Fiscal Quarter PK

Text

Primary Key suitable for identifying the fiscal quarter. (Source: row_wid from w_mcal_qtr_d)

Fiscal Quarter

Number

Externally known identifier for the fiscal quarter. (Source: mcal_qtr from w_mcal_qtr_d)

Fiscal Quarter Name

Text

The name associated with the fiscal quarter. (Source: mcal_per_name_qtr from w_mcal_qtr_d)

Start Date

Date

The date the fiscal quarter starts. (Source: mcal_qtr_start_dt from w_mcal_qtr_d)

End Date

Date

The date the fiscal quarter ends. (Source: mcal_qtr_end_dt from w_mcal_qtr_d)


Fiscal Period

Fiscal calendar's period level data.

Table E-14 Fiscal Period File

Field Type Description

Fiscal Period PK

Text

Primary Key suitable for identifying the fiscal period. (Source: row_wid from w_mcal_period_d)

Fiscal Period

Number

Externally known identifier for the fiscal period. (Source: mcal_period from w_mcal_period_d)

Fiscal Period Name

Text

The name associated with the fiscal period. (Source: mcal_per_name_period from w_mcal_period_d)

Start Date

Date

The date the fiscal period starts. (Source: mcal_period_start_dt from w_mcal_period_d

End Date

Date

The date the fiscal period ends. (Source: mcal_period_end_dt from w_mcal_period_d)


Fiscal Day

Fiscal Calendar day information.

Table E-15 Fiscal Day File

Field Type Description

Fiscal Day PK

Text

Primary Key suitable for identifying the fiscal day. (Source: row_wid from w_mcal_day_d)

Fiscal Day

Number

Externally known identifier for the fiscal day. (Source: mcal_day_of_year from w_mcal_day_d)

Date

Date

The date of the fiscal day. (Source: mcal_day_dt from w_mcal_day_d)


Fiscal Week

Fiscal calendar week level data.

Table E-16 Fiscal Week File

Field Type Description

Fiscal Week PK

Text

Primary Key suitable for identifying the fiscal week. (Source: row_wid from w_mcal_week_d)

Fiscal Week

Number

Externally known identifier for the fiscal week. (Source: mcal_week from w_mcal_week_d)

Fiscal Week Name

Text

The name associated with the fiscal week. (Source: mcal_per_name_week from w_mcal_week_d)

Start Date

Date

The date the fiscal week starts. (Source: mcal_week_start_dt from w_mcal_week_d)

End Date

Date

The date the fiscal week ends. (Source: mcal_week_end_dt from w_mcal_week_d)


Trade Area Hierarchy Levels

Provides the level descriptions for the trade area alternate location hierarchy.

Table E-17 Trade Area Hierarchy Levels File

Field Type Description

Level Number

Number

A number that represents the level of the hierarchy.

Level Description

Text

Descriptive text for the hierarchy level.

Source Level Description

Text

The level description for the level of the location hierarchy this hierarchy converges with.


Trade Area Hierarchy

An alternate hierarchy for the organization hierarchy, which will group locations together by trading area.

Table E-18 Trade Area Hierarchy File

Field Type Description

Trade Area Hierarchy External ID

Text

Customer visible ID used to identify a trade area hierarchy member.

Trade Area Hierarchy PK

Text

Primary key used to identify a level of the trade area hierarchy.

Trade Area Hierarchy External Key

Text

Externally suitable integration ID for the trade area hierarchy.

Effective From Date

Date

Starting date the source record was effective from.

Effective To Date

Date

Ending date the source record was effective from.

Current Flag

Text

Y/N flag to indicate if the record is considered a currently active hierarchy record.

Delete Flag

Text

Y/N flag to indicate if the record is considered deleted.

Trade Area Hierarchy Name

Text

Name of the trade area hierarchy.

Organization Hierarchy FK

Text

At the point this hierarchy converges with the organization hierarchy, a link to the organization hierarchy should be provided here.


Category Management Group Hierarchy

This is an alternate hierarchy for the product hierarchy, which can be used to group products together into groups that a customer would perceive as the same type of product.

Table E-19 Category Management Group Hierarchy File

Field Type Description

Category Management Group Hierarchy External ID

Text

Customer visible Category Management Group ID, used to denote any non-leaf node of the Category Management Group hierarchy. (Source: lvl#anc_prodcat_id or top_lvl_prodcat_id from w_prod_cat_dh)

Category Management Group Hierarchy PK

Text

Primary key used to identify a level of the Category Management Group hierarchy. (Source: ROW_WID from w_prod_cat_dh)

Category Management Group Hierarchy External Key

Text

Externally suitable integration ID for the Category Management Group hierarchy. (Source integration_id from w_prod_cat_dh)

Effective From Date

Date

Starting date the source record was effective from. (Source: effective_from_dt in w_prod_cat_dh)

Effective To Date

Date

Ending date the source record was effective from. (Source: effective_to_dt in w_prod_cat_dh)

Current Flag

Text

Y/N flag to indicate if the record is considered a currently active hierarchy record. (Source: current_flg in w_prod_cat_dh)

Delete Flag

Text

Y/N flag to indicate if the record is considered deleted. (Source: delete_flg in w_prod_cat_dh)

Category Management Group Hierarchy Name

Text

Name of the Category Management Group hierarchy. (Source: domain_member_name from w_domain_memeber_lkp_tl)

Category Management Group Hierarchy Description

Text

Description of the Category Management Group hierarchy. (Source: domain_member_descr from w_domain_member_lkp_tl)

Merchandise Hierarchy FK

Text

At the point this hierarchy converges with the merchandise hierarchy, a link to the merchandise hierarchy should be provided here.


Category Management Group Hierarchy Levels

This interface defines the levels that are part of the alternate category management group hierarchy.

Table E-20 Category Management Group Hierarchy Levels File

Field Type Description

Level Number

Number

A number that represents the level of the hierarchy.

Level Description

Text

Descriptive text for the hierarchy level.

Source Level Description

Text

The level description that this alternate hierarchy converges with the primary product hierarchy.


Location/Product Price and Cost

Current (at a given date) price and cost information provided at the product store level.

Table E-21 Location/Product Price and Cost File

Field Type Description

Product PK

Text

Primary key used to identify a product.

Location PK

Text

Primary key used to identify a store location.

Price

Number

Available price for the given date for the product/store combination.

Cost

Number

Available cost for the given date for the product/store combination.


Sales Transaction Data

Sales data at the product, location, day, and transaction level.

Table E-22 Sales Transaction Data File

Field Type Description

Product PK

Text

Primary key used to identify a product.(Source: prod_scd1_wid in w_rtl_sls_trx_it_lc_dy_f)

Location PK

Text

Primary key used to identify a store location.

(Source: org_scd1_wid in w_rtl_sls_trx_it_lc_dy_f)

Day PK

Number

Primary key used to identify the date of the transaction.

(Source: dt_wid in w_rtl_sls_trx_it_lc_dy_f)

Transaction ID

Text

Unique transaction identifier for a collection of sales for a given sales transaction.

(Source: sls_trx_id in w_rtl_sls_trx_it_lc_dy_f)

Sales Amount


Amount of sales expressed in global currency

(Source: Sls_amt_lcl / loc_exchange_rate in w_sls_trx_it_lc_dy_f)

Sales Units


Units sold.

(Source: sls_qty in w_rtl_sls_trx_it_lc_dy_f)

Sales Profit


Profit amount expressed in global currency

(Source: sls_profit_amt_lcl / loc_exchange_rate in w_rtl_sls_trx_it_lc_dy_f)

Customer ID

Text

PK for the customer who completed the transaction.

(Source: customer_wid in w_rtl_sls_trx_it_lc_dy_f)

Promotion ID

Text

Primary Key for the promotion component for this sales record.

(Source: promo_comp_wid in w_rtl_slspr_trx_it_lc_dy_f)


Item Ranging

Source: W_RTL_IT_LC_D table.

Table E-23 Item Ranging File

Field Type Description

Product ID

VARCHAR2(30)

This is a foreign key to the W_INT_ORG_D table using the SCD Type 1 WID.(Source: PRODUCT_SCD1_WID)

Location ID

VARCHAR2(30)

This is a foreign key to the W_INT_ORG_D table using the SCD Type 1 WID.

(Source: ORG_SCD1_WID

Ranged indicator

VARCHAR2(30)

This is the indicator from the source table that states that even though the item is not present in a particular location it exists in inventory.

This assists in acceptance of item not ranged in a location to be accepted in case of customer or cross channel returns.

(Source: RANGED)

Status of the item

VARCHAR2(1)

Current status of item at the store. Valid values are: A = Active (item is valid and can be ordered) and sold, I = Inactive (item is valid but cannot be ordered) or sold, C = Discontinued (item is valid and sellable but no longer order-able, and D = Delete (item is invalid and cannot be ordered or sold).

(Source: ITEM_STATUS)

Location type

VARCHAR2(1)

Type of location in the location field. Valid values are S (store), W (warehouse), and E (external finisher).

(Source: LOC_TYPE)

Start effective date

Date

(Source: EFFECTIVE_FROM_DT)

End effective date

Date

(Source: EFFECTIVE_TO_DT)