Intermediate Schema for Oracle

-- Salescast Push DB schema v1 for Oracle
-- Create database and execute queries in it.
-- Make sure to read documentation of how to customize meta data field list

CREATE TABLE Lokad_Items (
  Id NUMBER,
-- Meta data field samples
  "LabelProduct" NVARCHAR2(256) DEFAULT NULL,
  "LabelSupplier" NVARCHAR2(256) DEFAULT NULL,
  "TagCategory" NVARCHAR2(50) DEFAULT NULL,
-- end of metadata field samples
-- Inventory fields
  "StockOnHand" REAL DEFAULT NULL,
  "StockOnOrder" REAL DEFAULT NULL,
  "LeadTime" REAL DEFAULT NULL,
  "ServiceLevel" REAL DEFAULT NULL,
  "LotMultiplier" REAL DEFAULT NULL,
-- end of inventory fields
-- Custom
  -- NULL          Let Lokad use slow-movers filtering
  -- 0             Do not forecast this item
  -- other than 0  Forecast this item
  IsForecasted NUMBER(1) NULL,
  CONSTRAINT PK_Lokad_Items PRIMARY KEY (Id)
);

CREATE TABLE Lokad_Orders
(
  Id NUMBER,
  Item_Id NUMBER NOT NULL,
  OrderDate DATE NOT NULL,
  Quantity REAL NOT NULL,
  CONSTRAINT PK_Lokad_Orders PRIMARY KEY (Id)
);

ALTER TABLE Lokad_Orders
  ADD CONSTRAINT FK_Lokad_Orders_Items
  FOREIGN KEY (Item_Id)
  REFERENCES Lokad_Items (Id) ON DELETE CASCADE;

CREATE INDEX FK_Lokad_Orders_Items ON Lokad_Orders (Item_Id ASC);

-- This table is typically omitted in the early setup
-- (event info is usually harder to gather)
CREATE TABLE Lokad_Events
(
  Id NUMBER,
  Item_Id NUMBER NOT NULL,
  StartDate DATE NOT NULL,
-- Custom tags to describe the event
  "TagPromotionMechanism" NVARCHAR2 (50),
  "TagPromotionMarketing" NVARCHAR2 (50),
  "TagPromotionPackaging" NVARCHAR2 (50),
  "TagPromotionFacing" NVARCHAR2 (50),
-- end of custom tags
  CONSTRAINT PK_Lokad_Events PRIMARY KEY (Id)
);

ALTER TABLE Lokad_Events
  ADD CONSTRAINT FK_Lokad_Events_Items
  FOREIGN KEY (Item_Id)
  REFERENCES Lokad_Items (Id) ON DELETE CASCADE;

CREATE INDEX FK_Lokad_Events_Items ON Lokad_Events (Item_Id ASC);

CREATE INDEX IX_Lokad_Orders_Query ON Lokad_Orders
(
  Item_Id ASC,
  OrderDate ASC
);

CREATE INDEX IX_Lokad_Orders_Date ON Lokad_Orders
(  
  OrderDate ASC
);

-- Include those tables only if you want forecasts to be pushed
-- into the intermediate table.
CREATE TABLE Lokad_ForecastSync
(
  Id NUMBER,
  SyncDate DATE NOT NULL,
  CompletedDate DATE DEFAULT NULL,
  SyncReference NVARCHAR2(36) NOT NULL,
  CONSTRAINT PK_Lokad_ForecastSync PRIMARY KEY (Id)
);

CREATE TABLE Lokad_Forecasts
(
  Id NUMBER,
  Sync_Id NUMBER NOT NULL,
  Item_Id NUMBER NOT NULL,
  ForecastDate DATE NOT NULL,
  Quantity REAL NOT NULL,
  Accuracy REAL NOT NULL,
  CONSTRAINT PK_Lokad_Forecasts PRIMARY KEY (Id)
);

ALTER TABLE Lokad_Forecasts
  ADD CONSTRAINT FK_Lokad_Forecasts_FSync
  FOREIGN KEY (Sync_Id)
  REFERENCES Lokad_ForecastSync (Id) ON DELETE CASCADE;

CREATE INDEX FK_Lokad_Forecasts_FSync ON Lokad_Forecasts (Sync_Id ASC);

-- Configure this table with the desired output format
-- for the optimization information
CREATE TABLE Lokad_OptimizedItems
(
  -- Required columns that SHOULD be included exactly as specified
  Id NUMBER,
  Sync_Id NUMBER NOT NULL,
  Item_Id NUMBER NOT NULL,
-- Optional product-specific columns
-- if provided, they should be NULLable and match original fields from Lokad_Items
-- do not change case of names!
  "LabelProduct" NVARCHAR2(256) DEFAULT NULL,
  "LabelSupplier" NVARCHAR2(256) DEFAULT NULL,
  "TagColor" NVARCHAR2(50) DEFAULT NULL,
  "StockOnHand" REAL DEFAULT NULL,
  "StockOnOrder" REAL DEFAULT NULL,
  "LeadTime" REAL DEFAULT NULL,
  "ServiceLevel" REAL DEFAULT NULL,
-- end of optional data field samples
-- optional calculated fields
  "ReorderPoint" REAL DEFAULT NULL,
  "StockCover" REAL DEFAULT NULL,
  "LeadDemand" REAL DEFAULT NULL,
-- end of optional calculated fields
  CONSTRAINT PK_Lokad_OptimizedItems PRIMARY KEY (Id)
);

ALTER TABLE Lokad_OptimizedItems
  ADD CONSTRAINT FK_Lokad_OptimizedItems_FSync
  FOREIGN KEY (Sync_Id)
  REFERENCES Lokad_ForecastSync (Id) ON DELETE CASCADE;

CREATE INDEX FK_Lokad_OptimizedItems_FSync ON Lokad_OptimizedItems (Sync_Id ASC);

CREATE SEQUENCE SEQ_Lokad_Items START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_Lokad_Orders START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_Lokad_Events START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_Lokad_ForecastSync START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_Lokad_Forecasts START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_Lokad_OptimizedItems START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER TR_Lokad_Items BEFORE INSERT ON Lokad_Items REFERENCING NEW AS NEWROW FOR EACH ROW
BEGIN
  SELECT SEQ_Lokad_Items.nextval INTO :NEWROW.Id FROM dual;
END;
/

CREATE OR REPLACE TRIGGER TR_Lokad_Orders BEFORE INSERT ON Lokad_Orders REFERENCING NEW AS NEWROW FOR EACH ROW
BEGIN
  SELECT SEQ_Lokad_Orders.nextval INTO :NEWROW.Id FROM dual;
END;
/

CREATE OR REPLACE TRIGGER TR_Lokad_Events BEFORE INSERT ON Lokad_Events REFERENCING NEW AS NEWROW FOR EACH ROW
BEGIN
  SELECT SEQ_Lokad_Events.nextval INTO :NEWROW.Id FROM dual;
END;
/

CREATE OR REPLACE TRIGGER TR_Lokad_ForecastSync BEFORE INSERT ON Lokad_ForecastSync REFERENCING NEW AS NEWROW FOR EACH ROW
BEGIN
  SELECT SEQ_Lokad_ForecastSync.nextval INTO :NEWROW.Id FROM dual;
END;
/

CREATE OR REPLACE TRIGGER TR_Lokad_Forecasts BEFORE INSERT ON Lokad_Forecasts REFERENCING NEW AS NEWROW FOR EACH ROW
BEGIN
  SELECT SEQ_Lokad_Forecasts.nextval INTO :NEWROW.Id FROM dual;
END;
/

CREATE OR REPLACE TRIGGER TR_Lokad_OptimizedItems BEFORE INSERT ON Lokad_OptimizedItems REFERENCING NEW AS NEWROW FOR EACH ROW
BEGIN
  SELECT SEQ_Lokad_OptimizedItems.nextval INTO :NEWROW.Id FROM dual;
END;
/

DROP TABLE Lokad_Items CASCADE CONSTRAINTS PURGE;
DROP TABLE Lokad_Orders CASCADE CONSTRAINTS PURGE;
DROP TABLE Lokad_Events CASCADE CONSTRAINTS PURGE;
DROP TABLE Lokad_ForecastSync CASCADE CONSTRAINTS PURGE;
DROP TABLE Lokad_Forecasts CASCADE CONSTRAINTS PURGE;
DROP TABLE Lokad_OptimizedItems CASCADE CONSTRAINTS PURGE;

DROP SEQUENCE SEQ_Lokad_Items;
DROP SEQUENCE SEQ_Lokad_Orders;
DROP SEQUENCE SEQ_Lokad_Events;
DROP SEQUENCE SEQ_Lokad_ForecastSync;
DROP SEQUENCE SEQ_Lokad_Forecasts;
DROP SEQUENCE SEQ_Lokad_OptimizedItems;