-- Interface Module: Inventory_Tx
-- Interface: Inventory_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE inventory_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: INVENTORY_OUT
-- This table is used to integrate the current item-location inventory levels to MFP.

CREATE TABLE INVENTORY_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT inventory_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,
  -- If Y, item/location is currently on clearance, then inventory values below will be mapped to the clearance measures in MFP; if N, then they will be mapped to the regular/promotion inventory measures in MFP.
  clear_ind VARCHAR2(1) NOT NULL,
  -- Inventory Level for the item-location, it is computed as: stock_on_hand + pack_comp_soh + in_transit_qty + pack_comp_intran. It is held in a standard unit of measure of an item.
  regular_inventory_units NUMBER(12,4) NOT NULL,
  -- Indicates the Unit of Measure of regular inventory units. It would be a standard unit of measure of an item.
  standard_uom VARCHAR2(4) NOT NULL,
  -- The inventory cost value based on the computation:  inventory units * cost.  The cost will differ based on accounting method a) If item is in a cost department and running average cost, use av_cost b) If item is in a cost department and running standard cost, use unit_cost c) If item is in a retail department, use cum_markon_pct for subclass/location * unit_retail. It is held in primary currency.
  regular_inventory_cost NUMBER(20,4) NOT NULL,
  -- The inventory retail value based on computation: inventory units * unit_retail.  a) If system option set to have stock ledger run VAT exclusive, then a VAT exclusive unit_retail should be used. It is held in primary currency.
  regular_inventory_retail NUMBER(20,4) NOT NULL,
  -- Holds the primary currency that the regular_inventory_cost_prim and regular_inventory_retail_prim are expressed in.
  currency_code VARCHAR2(3) NOT NULL,
  -- Indicates the single unit cost of the item/location at the end of the week.
  unit_cost NUMBER(20,4) NOT NULL,
  -- Indicates the single unit average cost of the item/location at the end of the week.
  av_cost NUMBER(20,4) NOT NULL,
  -- Indicates the single unit retail of the item/location at the end of the week. Will be VAT inclusive or exclusive based on the RMS stock ledger system option.
  unit_retail NUMBER(20,4) NOT NULL
);

COMMENT ON TABLE INVENTORY_OUT IS 'This table is used to integrate the current item-location inventory levels to MFP.';
COMMENT ON COLUMN INVENTORY_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN INVENTORY_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN INVENTORY_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN INVENTORY_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN INVENTORY_OUT.eow_date IS 'Indicates the end of week date that the on order information pertains to.';
COMMENT ON COLUMN INVENTORY_OUT.item IS 'Transaction level item only.';
COMMENT ON COLUMN INVENTORY_OUT.location IS 'Indicates the end of week date that the on order information pertains to.';
COMMENT ON COLUMN INVENTORY_OUT.loc_type IS 'Indicates if the location is a store or warehouse - S = Store; W = Warehouse.';
COMMENT ON COLUMN INVENTORY_OUT.clear_ind IS 'If Y, item/location is currently on clearance, then inventory values below will be mapped to the clearance measures in MFP; if N, then they will be mapped to the regular/promotion inventory measures in MFP.';
COMMENT ON COLUMN INVENTORY_OUT.regular_inventory_units IS 'Inventory Level for the item-location, it is computed as: stock_on_hand + pack_comp_soh + in_transit_qty + pack_comp_intran. It is held in a standard unit of measure of an item.';
COMMENT ON COLUMN INVENTORY_OUT.standard_uom IS 'Indicates the Unit of Measure of regular inventory units. It would be a standard unit of measure of an item.';
COMMENT ON COLUMN INVENTORY_OUT.regular_inventory_cost IS 'The inventory cost value based on the computation:  inventory units * cost.  The cost will differ based on accounting method a) If item is in a cost department and running average cost, use av_cost b) If item is in a cost department and running standard cost, use unit_cost c) If item is in a retail department, use cum_markon_pct for subclass/location * unit_retail. It is held in primary currency.';
COMMENT ON COLUMN INVENTORY_OUT.regular_inventory_retail IS 'The inventory retail value based on computation: inventory units * unit_retail.  a) If system option set to have stock ledger run VAT exclusive, then a VAT exclusive unit_retail should be used. It is held in primary currency.';
COMMENT ON COLUMN INVENTORY_OUT.currency_code IS 'Holds the primary currency that the regular_inventory_cost_prim and regular_inventory_retail_prim are expressed in.';
COMMENT ON COLUMN INVENTORY_OUT.unit_cost IS 'Indicates the single unit cost of the item/location at the end of the week.';
COMMENT ON COLUMN INVENTORY_OUT.av_cost IS 'Indicates the single unit average cost of the item/location at the end of the week.';
COMMENT ON COLUMN INVENTORY_OUT.unit_retail IS 'Indicates the single unit retail of the item/location at the end of the week. Will be VAT inclusive or exclusive based on the RMS stock ledger system option.';


-- Add BDI primary key constraint

ALTER TABLE INVENTORY_OUT ADD CONSTRAINT pk_inventory_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE INVENTORY_OUT ADD CONSTRAINT chk_type_inventory_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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