Oracle® Retail Advanced Science Engine Implementation Guide Release 14.1 E59126-02 |
|
![]() Previous |
![]() Next |
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.
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 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) |
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 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) |
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 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) |
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) |
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) |
Associates customer IDs to customer segment IDs, so that a customer's identity can be classified under a 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. |
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 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 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 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 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 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) |
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. |
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. |
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. |
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. |
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 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) |
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) |