-- Interface Module: StockOut_Tx
-- Interface: Stockout_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE stockout_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: STOCKOUT_OUT
-- This table is used to integrate the current out of stock item-locations to RDF.

CREATE TABLE STOCKOUT_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT stockout_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 that is out of stock at the store at the end of the week.
  item VARCHAR2(25) NOT NULL,
  -- Indicates the store that is out of stock for the item at the end of the week.
  store NUMBER(10) NOT NULL,
  -- Indicates the end of week date for which the data applies.
  eow_date TIMESTAMP NOT NULL,
  -- Defaults to 1 to indicate out of stock. Only item/locs that are actually out of stock should be included in the file.
  out_of_stock NUMBER(1) NOT NULL
);

COMMENT ON TABLE STOCKOUT_OUT IS 'This table is used to integrate the current out of stock item-locations to RDF.';
COMMENT ON COLUMN STOCKOUT_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN STOCKOUT_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN STOCKOUT_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN STOCKOUT_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN STOCKOUT_OUT.item IS 'Indicates the item that is out of stock at the store at the end of the week.';
COMMENT ON COLUMN STOCKOUT_OUT.store IS 'Indicates the store that is out of stock for the item at the end of the week.';
COMMENT ON COLUMN STOCKOUT_OUT.eow_date IS 'Indicates the end of week date for which the data applies.';
COMMENT ON COLUMN STOCKOUT_OUT.out_of_stock IS 'Defaults to 1 to indicate out of stock. Only item/locs that are actually out of stock should be included in the file.';


-- Add BDI primary key constraint

ALTER TABLE STOCKOUT_OUT ADD CONSTRAINT pk_stockout_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE STOCKOUT_OUT ADD CONSTRAINT chk_type_stockout_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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