-- Interface Module: WklyDmdFst_Tx
-- Interface: Wkly_Dmnd_Frcst_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE wkly_dmnd_frcst_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: WKLY_DMND_FRCST_OUT
-- This table is used to integrate weekly demand forecast RDF-RMS.

CREATE TABLE WKLY_DMND_FRCST_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT wkly_dmnd_frcst_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',
  -- Holds the end of week date for the day, in YYYYMMDD format.
  week TIMESTAMP NOT NULL,
  -- Holds position name for sku.
  sku VARCHAR2(25) NOT NULL,
  -- Holds position name for stor.
  store VARCHAR2(10) NOT NULL,
  -- Forecast Sales Unit.
  appwkfrcst NUMBER(12,4),
  -- Approved Baseline Forecast.
  appwkbaseline NUMBER(12,4),
  -- Approved Forecast Error.
  appwkcumint NUMBER(12,4)
);

COMMENT ON TABLE WKLY_DMND_FRCST_OUT IS 'This table is used to integrate weekly demand forecast RDF-RMS.';
COMMENT ON COLUMN WKLY_DMND_FRCST_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN WKLY_DMND_FRCST_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN WKLY_DMND_FRCST_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN WKLY_DMND_FRCST_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN WKLY_DMND_FRCST_OUT.week IS 'Holds the end of week date for the day, in YYYYMMDD format.';
COMMENT ON COLUMN WKLY_DMND_FRCST_OUT.sku IS 'Holds position name for sku.';
COMMENT ON COLUMN WKLY_DMND_FRCST_OUT.store IS 'Holds position name for stor.';
COMMENT ON COLUMN WKLY_DMND_FRCST_OUT.appwkfrcst IS 'Forecast Sales Unit.';
COMMENT ON COLUMN WKLY_DMND_FRCST_OUT.appwkbaseline IS 'Approved Baseline Forecast.';
COMMENT ON COLUMN WKLY_DMND_FRCST_OUT.appwkcumint IS 'Approved Forecast Error.';


-- Add BDI primary key constraint

ALTER TABLE WKLY_DMND_FRCST_OUT ADD CONSTRAINT pk_wkly_dmnd_frcst_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE WKLY_DMND_FRCST_OUT ADD CONSTRAINT chk_type_wkly_dmnd_frcst_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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