-- Interface Module: LocClosed_Fnd
-- Interface: Location_Closed_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE location_closed_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: LOCATION_CLOSED_OUT
-- This table is used to integrate locations that are not available for certain functions even though the company as a whole is open.

CREATE TABLE LOCATION_CLOSED_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT location_closed_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',
  -- Numeric ID of location.
  location NUMBER(10) NOT NULL,
  -- Contains the date on which the location is closed.
  close_date TIMESTAMP NOT NULL,
  -- Describes the type of location. Valid values include S (store), W (warehouse).
  loc_type VARCHAR2(1) NOT NULL,
  -- Indicator used to determine if the location is closed for sales. Only stores can be closed for sales.
  sales_ind VARCHAR2(1) NOT NULL,
  -- Indicator used to determine if the location is closed for receiving.
  recv_ind VARCHAR2(1) NOT NULL,
  -- Indicator used to determine if the location is closed for shipping.
  ship_ind VARCHAR2(1) NOT NULL,
  -- Contains the reason why the store is closed.
  reason VARCHAR2(250)
);

COMMENT ON TABLE LOCATION_CLOSED_OUT IS 'This table is used to integrate locations that are not available for certain functions even though the company as a whole is open.';
COMMENT ON COLUMN LOCATION_CLOSED_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN LOCATION_CLOSED_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN LOCATION_CLOSED_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN LOCATION_CLOSED_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN LOCATION_CLOSED_OUT.location IS 'Numeric ID of location.';
COMMENT ON COLUMN LOCATION_CLOSED_OUT.close_date IS 'Contains the date on which the location is closed.';
COMMENT ON COLUMN LOCATION_CLOSED_OUT.loc_type IS 'Describes the type of location. Valid values include S (store), W (warehouse).';
COMMENT ON COLUMN LOCATION_CLOSED_OUT.sales_ind IS 'Indicator used to determine if the location is closed for sales. Only stores can be closed for sales.';
COMMENT ON COLUMN LOCATION_CLOSED_OUT.recv_ind IS 'Indicator used to determine if the location is closed for receiving.';
COMMENT ON COLUMN LOCATION_CLOSED_OUT.ship_ind IS 'Indicator used to determine if the location is closed for shipping.';
COMMENT ON COLUMN LOCATION_CLOSED_OUT.reason IS 'Contains the reason why the store is closed.';


-- Add BDI primary key constraint

ALTER TABLE LOCATION_CLOSED_OUT ADD CONSTRAINT pk_location_closed_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE LOCATION_CLOSED_OUT ADD CONSTRAINT chk_type_location_closed_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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