--- ABS-20501
--------------------------   BACKORDERWOL   -----------------------------
CREATE SEQUENCE SEQ_BACKORDERWOL
START WITH 1
INCREMENT BY 1;

CREATE TABLE BACKORDERWOL
(
BACKORDERWOL_ID NUMBER NOT NULL,
WORKORDERLINE_ID NUMBER NULL,
STOCKRESERVATIONPERGRADE_ID NUMBER NULL,
QUANTITY NUMBER NOT NULL,
STOCKTYPE NUMBER DEFAULT 1 NOT NULL CONSTRAINT STOCKTYPE_VALID1_1 CHECK (STOCKTYPE BETWEEN 1 AND 3),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN BACKORDERWOL.STOCKTYPE IS '1 = Non Unique 
2 = Unique
3 = Unique with Modifications';


ALTER TABLE BACKORDERWOL ADD CONSTRAINT XPKBACKORDERWOL PRIMARY KEY (BACKORDERWOL_ID);

CREATE UNIQUE INDEX XAK1BACKORDERWOL ON BACKORDERWOL (WORKORDERLINE_ID ASC, STOCKRESERVATIONPERGRADE_ID ASC);

CREATE INDEX XIF1BACKORDERWOL ON BACKORDERWOL (WORKORDERLINE_ID ASC);

CREATE INDEX XIF2BACKORDERWOL ON BACKORDERWOL (STOCKRESERVATIONPERGRADE_ID ASC);

ALTER TABLE BACKORDERWOL ADD CONSTRAINT BACKORDERWOL_WOLID FOREIGN KEY (WORKORDERLINE_ID)
REFERENCES WORKORDERLINE (WORKORDERLINE_ID) ON DELETE CASCADE;

ALTER TABLE BACKORDERWOL ADD CONSTRAINT BACKORDERWOL_SRPGID FOREIGN KEY (STOCKRESERVATIONPERGRADE_ID)
REFERENCES STOCKRESERVATIONPERGRADE (STOCKRESERVATIONPERGRADE_ID);

--------------------------   BACKORDERWOLE   -----------------------------
CREATE SEQUENCE SEQ_BACKORDERWOLE
START WITH 1
INCREMENT BY 1;

CREATE TABLE BACKORDERWOLE
(
BACKORDERWOLE_ID NUMBER NOT NULL,
WORKORDERLINEEMBLEM_ID NUMBER NULL,
STOCKRESERVATIONPERGRADE_ID NUMBER NULL,
QUANTITY NUMBER NOT NULL,
STOCKTYPE NUMBER DEFAULT 1 NOT NULL CONSTRAINT STOCKTYPE_VALID1_2 CHECK (STOCKTYPE BETWEEN 1 AND 3),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN BACKORDERWOLE.STOCKTYPE IS '1 = Non Unique 
2 = Unique
3 = Unique with Modifications';

ALTER TABLE BACKORDERWOLE ADD CONSTRAINT XPKBACKORDERWOLE PRIMARY KEY (BACKORDERWOLE_ID);

CREATE UNIQUE INDEX XAK1BACKORDERWOLE ON BACKORDERWOLE (WORKORDERLINEEMBLEM_ID ASC, STOCKRESERVATIONPERGRADE_ID ASC);

CREATE INDEX XIF1BACKORDERWOLE ON BACKORDERWOLE (WORKORDERLINEEMBLEM_ID ASC);

CREATE INDEX XIF2BACKORDERWOLE ON BACKORDERWOLE (STOCKRESERVATIONPERGRADE_ID ASC);

ALTER TABLE BACKORDERWOLE ADD CONSTRAINT BACKORDERWOLE_WOLEID FOREIGN KEY (WORKORDERLINEEMBLEM_ID)
REFERENCES WORKORDERLINEEMBLEM (WORKORDERLINEEMBLEM_ID) ON DELETE CASCADE;

ALTER TABLE BACKORDERWOLE ADD CONSTRAINT BACKORDERWOLE_SRPGID FOREIGN KEY (STOCKRESERVATIONPERGRADE_ID)
REFERENCES STOCKRESERVATIONPERGRADE (STOCKRESERVATIONPERGRADE_ID);

--------------------------   BACKORDEREXECUTIONWOL   -----------------------------
CREATE SEQUENCE SEQ_BACKORDEREXECUTIONWOL
START WITH 1
INCREMENT BY 1;

CREATE TABLE BACKORDEREXECUTIONWOL
(
BACKORDEREXECUTIONWOL_ID NUMBER NOT NULL,
BACKORDERWOL_ID NUMBER NOT NULL,
EXECUTIONTYPE NUMBER DEFAULT 0 NOT NULL CONSTRAINT EXECUTIONTYPE_VALID1_1 CHECK (EXECUTIONTYPE BETWEEN 0 AND 1),
SUPPLIER_ID NUMBER NOT NULL,
SHIPTO NUMBER DEFAULT 0 NOT NULL CONSTRAINT SHIPTO_VALID1_1 CHECK (SHIPTO BETWEEN 0 AND 2),
SHIPTOSTOCKROOM_ID NUMBER NULL,
QUANTITYORDERED NUMBER NOT NULL,
SEQUENCENUMBER NUMBER NOT NULL,
ERRORCODE NUMBER DEFAULT 0 NOT NULL CONSTRAINT ERRORCODE_VALID1_1 CHECK (ERRORCODE BETWEEN 0 AND 8),
SYSTEMUSER NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN BACKORDEREXECUTIONWOL.EXECUTIONTYPE IS '0 = External 
1 = Internal';

COMMENT ON COLUMN BACKORDEREXECUTIONWOL.SHIPTO IS '0 = Ordering Stockroom
1 = Other Stockroom
2 = Ordering customer';

COMMENT ON COLUMN BACKORDEREXECUTIONWOL.ERRORCODE IS '0 = Ok
1 = No supplier product/size defined
2 = No active supplier product/size defined
3 = No stockroom product defined
4 = No stockroom product/size defined
5 = No stockroom product/size/grade defined
6 = Stockroom product/size/grade out of service
7 = No stock available for stockroom product/size/grade
8 = No sufficient stock available for stockroom product/size/grade';

ALTER TABLE BACKORDEREXECUTIONWOL ADD CONSTRAINT XPKBACKORDEREXECUTIONWOL PRIMARY KEY (BACKORDEREXECUTIONWOL_ID);

CREATE UNIQUE INDEX XAK1BACKORDEREXECUTIONWOL ON BACKORDEREXECUTIONWOL (BACKORDERWOL_ID ASC, EXECUTIONTYPE ASC, SUPPLIER_ID ASC);

CREATE INDEX XIF1BACKORDEREXECUTIONWOL ON BACKORDEREXECUTIONWOL (BACKORDERWOL_ID ASC);

CREATE INDEX XIF2BACKORDEREXECUTIONWOL ON BACKORDEREXECUTIONWOL (SUPPLIER_ID ASC);

CREATE INDEX XIF3BACKORDEREXECUTIONWOL ON BACKORDEREXECUTIONWOL (SHIPTOSTOCKROOM_ID ASC);

ALTER TABLE BACKORDEREXECUTIONWOL ADD CONSTRAINT BACKORDEREXECUTIONWOL_BOWOLID FOREIGN KEY (BACKORDERWOL_ID)
REFERENCES BACKORDERWOL (BACKORDERWOL_ID) ON DELETE CASCADE;

ALTER TABLE BACKORDEREXECUTIONWOL ADD CONSTRAINT BACKORDEREXECUTIONWOL_SUPPID FOREIGN KEY (SUPPLIER_ID)
REFERENCES SUPPLIER (SUPPLIER_ID);

ALTER TABLE BACKORDEREXECUTIONWOL ADD CONSTRAINT BACKORDEREXECUTIONWOL_SHIPTO FOREIGN KEY (SHIPTOSTOCKROOM_ID)
REFERENCES STOCKROOM (STOCKROOM_ID);

ALTER TABLE BACKORDEREXECUTIONWOL ADD CONSTRAINT SHIPTO_CHECK CHECK 
((SHIPTO=2 AND SHIPTOSTOCKROOM_ID IS NOT NULL) OR (SHIPTO <> 2 AND SHIPTOSTOCKROOM_ID IS NULL));

--------------------------   BACKORDEREXECUTIONWOLE   -----------------------------
CREATE SEQUENCE SEQ_BACKORDEREXECUTIONWOLE
START WITH 1
INCREMENT BY 1;

CREATE TABLE BACKORDEREXECUTIONWOLE
(
BACKORDEREXECUTIONWOLE_ID NUMBER NOT NULL,
BACKORDERWOLE_ID NUMBER NOT NULL,
EXECUTIONTYPE NUMBER DEFAULT 0 NOT NULL CONSTRAINT EXECUTIONTYPE_VALID1_2 CHECK (EXECUTIONTYPE BETWEEN 0 AND 1),
SUPPLIER_ID NUMBER NOT NULL,
SHIPTO NUMBER DEFAULT 0 NOT NULL CONSTRAINT SHIPTO_VALID1_2 CHECK (SHIPTO BETWEEN 0 AND 2),
SHIPTOSTOCKROOM_ID NUMBER NULL,
QUANTITYORDERED NUMBER NOT NULL,
SEQUENCENUMBER NUMBER NOT NULL,
ERRORCODE NUMBER DEFAULT 0 NOT NULL CONSTRAINT ERRORCODE_VALID1_2 CHECK (ERRORCODE BETWEEN 0 AND 8),
SYSTEMUSER NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN BACKORDEREXECUTIONWOLE.EXECUTIONTYPE IS '0 = External 
1 = Internal';

COMMENT ON COLUMN BACKORDEREXECUTIONWOLE.SHIPTO IS '0 = Ordering Stockroom
1 = Other Stockroom
2 = Ordering customer';

COMMENT ON COLUMN BACKORDEREXECUTIONWOLE.ERRORCODE IS '0 = Ok
1 = No supplier product/size defined
2 = No active supplier product/size defined
3 = No stockroom product defined
4 = No stockroom product/size defined
5 = No stockroom product/size/grade defined
6 = Stockroom product/size/grade out of service
7 = No stock available for stockroom product/size/grade
8 = No sufficient stock available for stockroom product/size/grade';

ALTER TABLE BACKORDEREXECUTIONWOLE ADD CONSTRAINT XPKBACKORDEREXECUTIONWOLE PRIMARY KEY (BACKORDEREXECUTIONWOLE_ID);

CREATE UNIQUE INDEX XAK1BACKORDEREXECUTIONWOLE ON BACKORDEREXECUTIONWOLE (BACKORDERWOLE_ID ASC, EXECUTIONTYPE ASC, SUPPLIER_ID ASC);

CREATE INDEX XIF1BACKORDEREXECUTIONWOLE ON BACKORDEREXECUTIONWOLE (BACKORDERWOLE_ID ASC);

CREATE INDEX XIF2BACKORDEREXECUTIONWOLE ON BACKORDEREXECUTIONWOLE (SUPPLIER_ID ASC);

CREATE INDEX XIF3BACKORDEREXECUTIONWOLE ON BACKORDEREXECUTIONWOLE (SHIPTOSTOCKROOM_ID ASC);

ALTER TABLE BACKORDEREXECUTIONWOLE ADD CONSTRAINT BACKORDEREXECUTIONWOLE_BOWOLID FOREIGN KEY (BACKORDERWOLE_ID)
REFERENCES BACKORDERWOLE (BACKORDERWOLE_ID) ON DELETE CASCADE;

ALTER TABLE BACKORDEREXECUTIONWOLE ADD CONSTRAINT BACKORDEREXECUTIONWOLE_SUPPID FOREIGN KEY (SUPPLIER_ID)
REFERENCES SUPPLIER (SUPPLIER_ID);

ALTER TABLE BACKORDEREXECUTIONWOLE ADD CONSTRAINT BACKORDEREXECUTIONWOLE_SHIPTO FOREIGN KEY (SHIPTOSTOCKROOM_ID)
REFERENCES STOCKROOM (STOCKROOM_ID);

ALTER TABLE BACKORDEREXECUTIONWOLE ADD CONSTRAINT SHIPTO_CHECK1 CHECK 
((SHIPTO=2 AND SHIPTOSTOCKROOM_ID IS NOT NULL) OR (SHIPTO <> 2 AND SHIPTOSTOCKROOM_ID IS NULL));

--------------------------   BACKORDERALLOCATIONWOL   -----------------------------
CREATE SEQUENCE SEQ_BACKORDERALLOCATIONWOL
START WITH 1
INCREMENT BY 1;

CREATE TABLE BACKORDERALLOCATIONWOL
(
BACKORDERALLOCATIONWOL_ID NUMBER NOT NULL,
BACKORDEREXECUTIONWOL_ID NUMBER NOT NULL,
STOCKRESERVATIONPERGRADE_ID NUMBER NOT NULL,
STOCKTYPE NUMBER DEFAULT 1 NOT NULL CONSTRAINT STOCKTYPE_VALID1_3 CHECK (STOCKTYPE BETWEEN 1 AND 3),
QUANTITYALLOCATED NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN BACKORDERALLOCATIONWOL.STOCKTYPE IS '1 = Non Unique 
2 = Unique
3 = Unique with Modifications';

ALTER TABLE BACKORDERALLOCATIONWOL ADD CONSTRAINT XPKBACKORDERALLOCATIONWOL PRIMARY KEY (BACKORDERALLOCATIONWOL_ID);

CREATE UNIQUE INDEX XAK1BACKORDERALLOCATIONWOL ON BACKORDERALLOCATIONWOL 
(BACKORDEREXECUTIONWOL_ID ASC, STOCKRESERVATIONPERGRADE_ID ASC, STOCKTYPE ASC);

CREATE INDEX XIF1BACKORDERALLOCATIONWOL ON BACKORDERALLOCATIONWOL (BACKORDEREXECUTIONWOL_ID ASC);

CREATE INDEX XIF2BACKORDERALLOCATIONWOL ON BACKORDERALLOCATIONWOL (STOCKRESERVATIONPERGRADE_ID ASC);

ALTER TABLE BACKORDERALLOCATIONWOL ADD CONSTRAINT BACKORDERALLOCATIONWOL_BOEID FOREIGN KEY (BACKORDEREXECUTIONWOL_ID)
REFERENCES BACKORDEREXECUTIONWOL (BACKORDEREXECUTIONWOL_ID) ON DELETE CASCADE;

ALTER TABLE BACKORDERALLOCATIONWOL ADD CONSTRAINT BACKORDERALLOCATIONWOL_SRPGID FOREIGN KEY (STOCKRESERVATIONPERGRADE_ID)
REFERENCES STOCKRESERVATIONPERGRADE (STOCKRESERVATIONPERGRADE_ID);

--------------------------   BACKORDERALLOCATIONWOLE   -----------------------------
CREATE SEQUENCE SEQ_BACKORDERALLOCATIONWOLE
START WITH 1
INCREMENT BY 1;

CREATE TABLE BACKORDERALLOCATIONWOLE
(
BACKORDERALLOCATIONWOLE_ID NUMBER NOT NULL,
BACKORDEREXECUTIONWOLE_ID NUMBER NOT NULL,
STOCKRESERVATIONPERGRADE_ID NUMBER NOT NULL,
STOCKTYPE NUMBER DEFAULT 1 NOT NULL CONSTRAINT STOCKTYPE_VALID1_4 CHECK (STOCKTYPE BETWEEN 1 AND 3),
QUANTITYALLOCATED NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN BACKORDERALLOCATIONWOLE.STOCKTYPE IS '1 = Non Unique 
2 = Unique
3 = Unique with Modifications';

ALTER TABLE BACKORDERALLOCATIONWOLE ADD CONSTRAINT XPKBACKORDERALLOCATIONWOLE PRIMARY KEY (BACKORDERALLOCATIONWOLE_ID);

CREATE UNIQUE INDEX XAK1BACKORDERALLOCATIONWOLE ON BACKORDERALLOCATIONWOLE (BACKORDEREXECUTIONWOLE_ID ASC, STOCKRESERVATIONPERGRADE_ID ASC, STOCKTYPE ASC);

CREATE INDEX XIF1BACKORDERALLOCATIONWOLE ON BACKORDERALLOCATIONWOLE (BACKORDEREXECUTIONWOLE_ID ASC);

CREATE INDEX XIF2BACKORDERALLOCATIONWOLE ON BACKORDERALLOCATIONWOLE (STOCKRESERVATIONPERGRADE_ID ASC);

ALTER TABLE BACKORDERALLOCATIONWOLE ADD CONSTRAINT BACKORDERALLOCATIONWOLE_BOEID FOREIGN KEY (BACKORDEREXECUTIONWOLE_ID)
REFERENCES BACKORDEREXECUTIONWOLE (BACKORDEREXECUTIONWOLE_ID) ON DELETE CASCADE;

ALTER TABLE BACKORDERALLOCATIONWOLE ADD CONSTRAINT BACKORDERALLOCATIONWOLE_SRPGID FOREIGN KEY (STOCKRESERVATIONPERGRADE_ID)
REFERENCES STOCKRESERVATIONPERGRADE (STOCKRESERVATIONPERGRADE_ID);

--------------------------   STOCKROOM   -----------------------------
ALTER TABLE STOCKROOM ADD (BACKORDERSINAVAILABLECALC NUMBER DEFAULT 0 NOT NULL CONSTRAINT BOAVAILABLECALC_VALID1_1 CHECK 
					(BACKORDERSINAVAILABLECALC BETWEEN 0 AND 1),
			   LOCKEDFORSTOCKREPGENERATION NUMBER DEFAULT 0 NOT NULL CONSTRAINT BOLOCKEDFORSTOCK_VALID1_1 CHECK
					(LOCKEDFORSTOCKREPGENERATION BETWEEN 0 AND 1),
			   LOCKEDFORSTOCKREPGENERATION_ID NUMBER NULL CONSTRAINT BOLOCKEDFORSTOCKID_VALID1_1 CHECK 									(LOCKEDFORSTOCKREPGENERATION_ID IS NULL OR LOCKEDFORSTOCKREPGENERATION_ID IS NOT NULL));

COMMENT ON COLUMN STOCKROOM.BACKORDERSINAVAILABLECALC IS '0 = Do not include in calculation
1 = Do include in calculation';

COMMENT ON COLUMN STOCKROOM.LOCKEDFORSTOCKREPGENERATION IS '0 = No backorders for stock replenishment are being generated
1 = Backorders for stock replenishment are being generated';

COMMENT ON COLUMN STOCKROOM.LOCKEDFORSTOCKREPGENERATION_ID IS 'Null = No backorders for stock replenishment are being generated for this stockroom
Not null = Backorders for stock replenishment are being generated for this stockroom by this workstation';
