-- Interface Module: ItemLocHist_Tx
-- Interface: Item_Loc_Hist_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE item_loc_hist_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: ITEM_LOC_HIST_OUT
-- This table is used to integrate sales and issues history by week in RMS.  It will contain one row for each item, location, week, sales type combination that occurred during the week.  Sales occur only at stores.  Issues are recorded only for warehouses based on outbound transfer and allocation transactions to stores.

CREATE TABLE ITEM_LOC_HIST_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT item_loc_hist_out_seq.nextval NOT NULL,
  -- bdi internal column
  bdi_app_name VARCHAR2(50) DEFAULT sys_context('userenv', 'current_schema') NOT NULL,
  -- bdi internal column
  bdi_dataset_type VARCHAR2(20) DEFAULT 'FULL',
  -- bdi internal column
  bdi_dataset_action VARCHAR2(20) DEFAULT 'REPLACE',
  -- Item whose sales and issues history is described.
  item VARCHAR2(25) NOT NULL,
  -- Location at which the item has sales and issues.
  location NUMBER(10,0) NOT NULL,
  -- Type of location.  S - store, W - warehouse.
  loc_type VARCHAR2(1) NOT NULL,
  -- Indicates the end of week date in YYYYMMDD format.
  eow_date TIMESTAMP NOT NULL,
  -- Holds the week in which the transactions occurred based on the 454 calendar.
  week_454 NUMBER(2),
  -- Holds the month in which the transactions occurred based on the 454 calendar.
  month_454 NUMBER(2),
  -- Holds the year in which the transactions occurred based on the 454 calendar.
  year_454 NUMBER(4),
  -- Holds the type of sale or inventory movement.  R - Regular Sales, P - Promotional Sales, C - Clearance Sales, I - Issues from Warehouses to Stores.
  sales_type VARCHAR2(1) NOT NULL,
  -- Holds the number of units sold or transferred for the item,location,week,sales type combination. If the location type is a store, then this value represents sales. If the location type is a warehouse, then this value represents outbound transfer or allocation transactions to stores.
  sales_issues NUMBER(12,4),
  -- Holds the retail value of the sales for the item,location,week,sales type combination. This field is stored in the local currency.  This field will only hold a value for stores.
  value NUMBER(20,4),
  -- Holds the total gross profit calculated for the item,location,week,sales type combination.  This is updated for each sale that occurs during the week and is calculated as (sales retail - cost of sales). This field is stored in the local currency.  This field will only hold a value for stores.
  gross_profit NUMBER(20,4),
  -- Holds the stock on hand for the item,location,week,sales type combination at the end of the week.
  stock NUMBER(12,4),
  -- Holds the unit retail price of the item at the location at the end of the week. This field is stored in the local currency.
  retail NUMBER(20,4),
  -- Holds the single unit average cost of the item at the location at the end of the week. This field is stored in the local currency.
  av_cost NUMBER(20,4),
  -- Holds the date and timestamp when the record was created.
  create_datetime TIMESTAMP NOT NULL,
  -- Holds the date timestamp of the most recent update by the last_update_id.
  last_update_datetime TIMESTAMP NOT NULL,
  -- Holds the user who most recently updated this record.
  last_update_id VARCHAR2(30) NOT NULL
);

COMMENT ON TABLE ITEM_LOC_HIST_OUT IS 'This table is used to integrate sales and issues history by week in RMS.  It will contain one row for each item, location, week, sales type combination that occurred during the week.  Sales occur only at stores.  Issues are recorded only for warehouses based on outbound transfer and allocation transactions to stores.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.item IS 'Item whose sales and issues history is described.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.location IS 'Location at which the item has sales and issues.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.loc_type IS 'Type of location.  S - store, W - warehouse.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.eow_date IS 'Indicates the end of week date in YYYYMMDD format.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.week_454 IS 'Holds the week in which the transactions occurred based on the 454 calendar.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.month_454 IS 'Holds the month in which the transactions occurred based on the 454 calendar.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.year_454 IS 'Holds the year in which the transactions occurred based on the 454 calendar.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.sales_type IS 'Holds the type of sale or inventory movement.  R - Regular Sales, P - Promotional Sales, C - Clearance Sales, I - Issues from Warehouses to Stores.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.sales_issues IS 'Holds the number of units sold or transferred for the item,location,week,sales type combination. If the location type is a store, then this value represents sales. If the location type is a warehouse, then this value represents outbound transfer or allocation transactions to stores.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.value IS 'Holds the retail value of the sales for the item,location,week,sales type combination. This field is stored in the local currency.  This field will only hold a value for stores.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.gross_profit IS 'Holds the total gross profit calculated for the item,location,week,sales type combination.  This is updated for each sale that occurs during the week and is calculated as (sales retail - cost of sales). This field is stored in the local currency.  This field will only hold a value for stores.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.stock IS 'Holds the stock on hand for the item,location,week,sales type combination at the end of the week.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.retail IS 'Holds the unit retail price of the item at the location at the end of the week. This field is stored in the local currency.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.av_cost IS 'Holds the single unit average cost of the item at the location at the end of the week. This field is stored in the local currency.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.create_datetime IS 'Holds the date and timestamp when the record was created.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.last_update_datetime IS 'Holds the date timestamp of the most recent update by the last_update_id.';
COMMENT ON COLUMN ITEM_LOC_HIST_OUT.last_update_id IS 'Holds the user who most recently updated this record.';


-- Add BDI primary key constraint

ALTER TABLE ITEM_LOC_HIST_OUT ADD CONSTRAINT pk_item_loc_hist_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE ITEM_LOC_HIST_OUT ADD CONSTRAINT chk_type_item_loc_hist_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

ALTER TABLE ITEM_LOC_HIST_OUT ADD CONSTRAINT chk_actn_item_loc_hist_out CHECK (bdi_dataset_action IN ('REPLACE', 'CREATE', 'UPDATE', 'DELETE'));