-- Interface Module: WeeklySales_Tx
-- Interface: Weekly_Sales_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE weekly_sales_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: WEEKLY_SALES_OUT
-- This table is used to integrate the weekly sales information to RDF.

CREATE TABLE WEEKLY_SALES_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT weekly_sales_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',
  -- Indicates the item.
  item VARCHAR2(25) NOT NULL,
  -- Indicates the store.
  store NUMBER(10) NOT NULL,
  -- Indicates the end of week date. Item_loc_hist.eow_date in YYYYMMDD format.
  eow_date TIMESTAMP,
  -- Indicates the total sales units for the item/location for the week by sales type. a) If the sales type = R, then this should be mapped to the rsal measure b) If the sales type = C, then this should be mapped to the csal measure c) If the sales type = P, then this should be mapped to the psal measure.
  sales_units NUMBER(12,4),
  -- Indicates the sales type. Item_loc_hist.sales_type = R (Regular Sales), P (Promotional Sales), C (Clearance Sales).
  sales_type VARCHAR2(1) NOT NULL
);

COMMENT ON TABLE WEEKLY_SALES_OUT IS 'This table is used to integrate the weekly sales information to RDF.';
COMMENT ON COLUMN WEEKLY_SALES_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN WEEKLY_SALES_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN WEEKLY_SALES_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN WEEKLY_SALES_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN WEEKLY_SALES_OUT.item IS 'Indicates the item.';
COMMENT ON COLUMN WEEKLY_SALES_OUT.store IS 'Indicates the store.';
COMMENT ON COLUMN WEEKLY_SALES_OUT.eow_date IS 'Indicates the end of week date. Item_loc_hist.eow_date in YYYYMMDD format.';
COMMENT ON COLUMN WEEKLY_SALES_OUT.sales_units IS 'Indicates the total sales units for the item/location for the week by sales type. a) If the sales type = R, then this should be mapped to the rsal measure b) If the sales type = C, then this should be mapped to the csal measure c) If the sales type = P, then this should be mapped to the psal measure.';
COMMENT ON COLUMN WEEKLY_SALES_OUT.sales_type IS 'Indicates the sales type. Item_loc_hist.sales_type = R (Regular Sales), P (Promotional Sales), C (Clearance Sales).';


-- Add BDI primary key constraint

ALTER TABLE WEEKLY_SALES_OUT ADD CONSTRAINT pk_weekly_sales_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE WEEKLY_SALES_OUT ADD CONSTRAINT chk_type_weekly_sales_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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