-- Interface Module: DeliverySlot_Fnd
-- Interface: Delivery_Slot_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE delivery_slot_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: DELIVERY_SLOT_OUT
-- This table is used to integrate Delivery Slot information.

CREATE TABLE DELIVERY_SLOT_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT delivery_slot_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',
  -- This column specifies when the store requested quantity is needed at the store. For example, it defines whether the stock is designated for AM or PM delivery.
  delivery_slot_id VARCHAR2(15) NOT NULL,
  -- Description of the delivery slot. This could contain the time element for the delivery schedule (i.e. Afternoon Slot 3 PM).
  delivery_slot_desc VARCHAR2(240) NOT NULL,
  -- This column will specify the precedence of the delivery slot on a particular day.
  delivery_slot_sequence NUMBER(8,0) NOT NULL
);

COMMENT ON TABLE DELIVERY_SLOT_OUT IS 'This table is used to integrate Delivery Slot information.';
COMMENT ON COLUMN DELIVERY_SLOT_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN DELIVERY_SLOT_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN DELIVERY_SLOT_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN DELIVERY_SLOT_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN DELIVERY_SLOT_OUT.delivery_slot_id IS 'This column specifies when the store requested quantity is needed at the store. For example, it defines whether the stock is designated for AM or PM delivery.';
COMMENT ON COLUMN DELIVERY_SLOT_OUT.delivery_slot_desc IS 'Description of the delivery slot. This could contain the time element for the delivery schedule (i.e. Afternoon Slot 3 PM).';
COMMENT ON COLUMN DELIVERY_SLOT_OUT.delivery_slot_sequence IS 'This column will specify the precedence of the delivery slot on a particular day.';


-- Add BDI primary key constraint

ALTER TABLE DELIVERY_SLOT_OUT ADD CONSTRAINT pk_delivery_slot_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE DELIVERY_SLOT_OUT ADD CONSTRAINT chk_type_delivery_slot_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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