-- Interface Module: OnOrder_Tx
-- Interface: On_Order_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE on_order_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: ON_ORDER_OUT
-- This table is used to integrate On Order Items to MFP.

CREATE TABLE ON_ORDER_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT on_order_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',
  -- Indicates the end of week date that the on order information pertains to.
  eow_date TIMESTAMP NOT NULL,
  -- Transaction level item only.
  item VARCHAR2(25) NOT NULL,
  -- Indicates the end of week date that the on order information pertains to.
  location NUMBER(10) NOT NULL,
  -- Indicates if the location is a store or warehouse - S = Store; W = Warehouse.
  loc_type VARCHAR2(1) NOT NULL,
  -- Indicates if the item/location is on clearance.
  clear_ind VARCHAR2(1) NOT NULL,
  -- Indicates the total quantity of the item in the order in standard unit of measure.
  on_order_units NUMBER(12) NOT NULL,
  -- Indicates the Unit of Measure of on_order_units. It would be standard unit of measure of an item.
  standard_uom VARCHAR2(4) NOT NULL,
  -- Calculation of on order qty * PO cost in primary currency.
  on_order_cost NUMBER(20,4) NOT NULL,
  -- Calculation of on order qty * PO retail in primary currency.
  on_order_retail NUMBER(20,4) NOT NULL,
  -- Holds the primary currency code on_order_cost_prim and on_order_retail_prim are expressed in.
  currency_code VARCHAR2(3) NOT NULL
);

COMMENT ON TABLE ON_ORDER_OUT IS 'This table is used to integrate On Order Items to MFP.';
COMMENT ON COLUMN ON_ORDER_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN ON_ORDER_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN ON_ORDER_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN ON_ORDER_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN ON_ORDER_OUT.eow_date IS 'Indicates the end of week date that the on order information pertains to.';
COMMENT ON COLUMN ON_ORDER_OUT.item IS 'Transaction level item only.';
COMMENT ON COLUMN ON_ORDER_OUT.location IS 'Indicates the end of week date that the on order information pertains to.';
COMMENT ON COLUMN ON_ORDER_OUT.loc_type IS 'Indicates if the location is a store or warehouse - S = Store; W = Warehouse.';
COMMENT ON COLUMN ON_ORDER_OUT.clear_ind IS 'Indicates if the item/location is on clearance.';
COMMENT ON COLUMN ON_ORDER_OUT.on_order_units IS 'Indicates the total quantity of the item in the order in standard unit of measure.';
COMMENT ON COLUMN ON_ORDER_OUT.standard_uom IS 'Indicates the Unit of Measure of on_order_units. It would be standard unit of measure of an item.';
COMMENT ON COLUMN ON_ORDER_OUT.on_order_cost IS 'Calculation of on order qty * PO cost in primary currency.';
COMMENT ON COLUMN ON_ORDER_OUT.on_order_retail IS 'Calculation of on order qty * PO retail in primary currency.';
COMMENT ON COLUMN ON_ORDER_OUT.currency_code IS 'Holds the primary currency code on_order_cost_prim and on_order_retail_prim are expressed in.';


-- Add BDI primary key constraint

ALTER TABLE ON_ORDER_OUT ADD CONSTRAINT pk_on_order_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE ON_ORDER_OUT ADD CONSTRAINT chk_type_on_order_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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