-- Interface Module: InvAvailWh_Tx
-- Interface: Inv_Avail_Wh_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE inv_avail_wh_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: INV_AVAIL_WH_OUT
-- This table is used to integrate current available inventory for items at warehouses.

CREATE TABLE INV_AVAIL_WH_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT inv_avail_wh_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',
  -- Item whose current inventory position described.
  item VARCHAR2(25) NOT NULL,
  -- Warehouse at which the item has inventory.  This is always a virtual warehouse.
  location VARCHAR2(10) NOT NULL,
  -- Type of location.  W represents the virtual warehouse.
  loc_type VARCHAR2(1) NOT NULL,
  -- Available quantity of the item at the location.  This qty is calculated by subtracting transfer reserved qty, customer reserved qty, non sellable inventory and RTV from stock on hand (e.g. current available inventory  = stock on hand - (tsf reserved + customer reserved + non sellable + RTV qty)).  Available inventory is in the standard unit of measure.
  avail_qty NUMBER(20,4) NOT NULL,
  -- Current stock on hand for the item.
  stock_on_hand NUMBER(20,4) NOT NULL,
  -- Unit of measure in which stock of the item is tracked at a corporate level.
  standard_uom VARCHAR2(4) NOT NULL,
  -- This column will contain the number of the physical warehouse that is assigned to the virtual warehouse. It will only contain a value when location is a warehouse.
  physical_wh NUMBER(10,0)
);

COMMENT ON TABLE INV_AVAIL_WH_OUT IS 'This table is used to integrate current available inventory for items at warehouses.';
COMMENT ON COLUMN INV_AVAIL_WH_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN INV_AVAIL_WH_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN INV_AVAIL_WH_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN INV_AVAIL_WH_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN INV_AVAIL_WH_OUT.item IS 'Item whose current inventory position described.';
COMMENT ON COLUMN INV_AVAIL_WH_OUT.location IS 'Warehouse at which the item has inventory.  This is always a virtual warehouse.';
COMMENT ON COLUMN INV_AVAIL_WH_OUT.loc_type IS 'Type of location.  W represents the virtual warehouse.';
COMMENT ON COLUMN INV_AVAIL_WH_OUT.avail_qty IS 'Available quantity of the item at the location.  This qty is calculated by subtracting transfer reserved qty, customer reserved qty, non sellable inventory and RTV from stock on hand (e.g. current available inventory  = stock on hand - (tsf reserved + customer reserved + non sellable + RTV qty)).  Available inventory is in the standard unit of measure.';
COMMENT ON COLUMN INV_AVAIL_WH_OUT.stock_on_hand IS 'Current stock on hand for the item.';
COMMENT ON COLUMN INV_AVAIL_WH_OUT.standard_uom IS 'Unit of measure in which stock of the item is tracked at a corporate level.';
COMMENT ON COLUMN INV_AVAIL_WH_OUT.physical_wh IS 'This column will contain the number of the physical warehouse that is assigned to the virtual warehouse. It will only contain a value when location is a warehouse.';


-- Add BDI primary key constraint

ALTER TABLE INV_AVAIL_WH_OUT ADD CONSTRAINT pk_inv_avail_wh_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE INV_AVAIL_WH_OUT ADD CONSTRAINT chk_type_inv_avail_wh_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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