-- Interface Module: Clearance_Tx
-- Interface: Clearance_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE clearance_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: CLEARANCE_OUT
-- Table to hold clearance specific price event information.

CREATE TABLE CLEARANCE_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT clearance_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',
  -- The id of the record.
  rec_id NUMBER(10) NOT NULL,
  -- The record type.  Valid Values: Create/Update/Delete
  record_type VARCHAR2(50) NOT NULL,
  -- The clearance ID.
  clearance_id NUMBER(15) NOT NULL,
  -- The Item id
  item VARCHAR2(25),
  -- The location id.
  location NUMBER(10),
  -- Location Type.  Valid values: S(Store)/W(Warehouse)
  location_type VARCHAR2(30),
  -- Effective date of clearance
  effective_date TIMESTAMP(2),
  -- The retail with for the item and location based on the clearance.
  retail NUMBER(20,4),
  -- The Retail Unit Of Measure
  uom VARCHAR2(25),
  -- The currency for the location.
  currency VARCHAR2(25),
  -- Indicates if the clearance event is a reset.  Valid values: 0 - The record is not a reset; 1 - The record is a reset
  reset_indicator NUMBER(1) NOT NULL
);

COMMENT ON TABLE CLEARANCE_OUT IS 'Table to hold clearance specific price event information.';
COMMENT ON COLUMN CLEARANCE_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN CLEARANCE_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN CLEARANCE_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN CLEARANCE_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN CLEARANCE_OUT.rec_id IS 'The id of the record.';
COMMENT ON COLUMN CLEARANCE_OUT.record_type IS 'The record type.  Valid Values: Create/Update/Delete';
COMMENT ON COLUMN CLEARANCE_OUT.clearance_id IS 'The clearance ID.';
COMMENT ON COLUMN CLEARANCE_OUT.item IS 'The Item id';
COMMENT ON COLUMN CLEARANCE_OUT.location IS 'The location id.';
COMMENT ON COLUMN CLEARANCE_OUT.location_type IS 'Location Type.  Valid values: S(Store)/W(Warehouse)';
COMMENT ON COLUMN CLEARANCE_OUT.effective_date IS 'Effective date of clearance';
COMMENT ON COLUMN CLEARANCE_OUT.retail IS 'The retail with for the item and location based on the clearance.';
COMMENT ON COLUMN CLEARANCE_OUT.uom IS 'The Retail Unit Of Measure';
COMMENT ON COLUMN CLEARANCE_OUT.currency IS 'The currency for the location.';
COMMENT ON COLUMN CLEARANCE_OUT.reset_indicator IS 'Indicates if the clearance event is a reset.  Valid values: 0 - The record is not a reset; 1 - The record is a reset';


-- Add BDI primary key constraint

ALTER TABLE CLEARANCE_OUT ADD CONSTRAINT pk_clearance_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE CLEARANCE_OUT ADD CONSTRAINT chk_type_clearance_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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