-- Interface Module: ExtRfid_Tx
-- Interface: Ext_Rfid_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE ext_rfid_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: EXT_RFID_OUT
-- This table contains RFID data coming from an external RFID application

CREATE TABLE EXT_RFID_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT ext_rfid_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',
  -- Electronic product code (SGTIN-96).
  epc VARCHAR2(256) NOT NULL,
  -- Identifier of the item/sku.
  item_id VARCHAR2(25) NOT NULL,
  -- location identifer
  location_id NUMBER(10) NOT NULL,
  -- location type, 1 - store, 2 - warehouse
  location_type NUMBER(2) NOT NULL,
  -- The zone within the location that the RFID is located
  zone_id NUMBER(15),
  -- The timestamp of the RFID read
  event_date TIMESTAMP
);

COMMENT ON TABLE EXT_RFID_OUT IS 'This table contains RFID data coming from an external RFID application';
COMMENT ON COLUMN EXT_RFID_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN EXT_RFID_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN EXT_RFID_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN EXT_RFID_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN EXT_RFID_OUT.epc IS 'Electronic product code (SGTIN-96).';
COMMENT ON COLUMN EXT_RFID_OUT.item_id IS 'Identifier of the item/sku.';
COMMENT ON COLUMN EXT_RFID_OUT.location_id IS 'location identifer';
COMMENT ON COLUMN EXT_RFID_OUT.location_type IS 'location type, 1 - store, 2 - warehouse';
COMMENT ON COLUMN EXT_RFID_OUT.zone_id IS 'The zone within the location that the RFID is located';
COMMENT ON COLUMN EXT_RFID_OUT.event_date IS 'The timestamp of the RFID read';


-- Add BDI primary key constraint

ALTER TABLE EXT_RFID_OUT ADD CONSTRAINT pk_ext_rfid_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE EXT_RFID_OUT ADD CONSTRAINT chk_type_ext_rfid_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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