-- ABS-67076/ ABS-81088

-- PRODUCT TABLE --
ALTER TABLE PRODUCT ADD (BULKPACKINGACTIVESTOCKTYPE NUMBER DEFAULT 0 NOT NULL
                         CONSTRAINT BULKPACKACTIVESTOCKTYPE_VALID1 CHECK (BULKPACKINGACTIVESTOCKTYPE IN (0, 1, 2, 3)));

COMMENT ON COLUMN PRODUCT.BULKPACKINGACTIVESTOCKTYPE
  IS '0 = Not applicable (default)
1 = Customer logo mat (Unique item in location, not Bulk)
2 = Customer Pool logo mat (Bulk Multi Customer)
3 = Bulk (Pool) product (Bulk No Customer)
';

-- ROUTECONTROLEXTRAINFO TABLE --
CREATE TABLE ROUTECONTROLEXTRAINFO
(
  ROUTECONTROL_ID         NUMBER NOT NULL,
  BULKPACKINGSTATUS       NUMBER NOT NULL 
                          CONSTRAINT BULKPACKINGSTATUS_VALID1 CHECK (BULKPACKINGSTATUS IN (1, 2, 3)),
  BULKPACKINGSTART        DATE NULL,
  BULKPACKINGEND          DATE NULL,
  BULKPACKEDSYSTEMUSER_ID NUMBER NULL,
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
)
;

COMMENT ON COLUMN ROUTECONTROLEXTRAINFO.BULKPACKINGSTATUS
  IS '1 = Not started (default)
2 = In progress
3 = Completed
';
COMMENT ON COLUMN ROUTECONTROLEXTRAINFO.BULKPACKEDSYSTEMUSER_ID
  IS 'System user who started bulk packing, overwritten by who completed the packing.  Not a foreign key';

ALTER TABLE ROUTECONTROLEXTRAINFO
  ADD CONSTRAINT XPKROUTECONTROLEXTRAINFO PRIMARY KEY (ROUTECONTROL_ID);
  
ALTER TABLE ROUTECONTROLEXTRAINFO
  ADD CONSTRAINT ROUTECONTROLEXTRAINFO_RC FOREIGN KEY (ROUTECONTROL_ID)
  REFERENCES ROUTECONTROL (ROUTECONTROL_ID) ON DELETE CASCADE;

-- ROUTECONTROLBULKPACKING TABLE --
CREATE SEQUENCE SEQ_ROUTECONTROLBULKPACKING
START WITH 1
INCREMENT BY 1;

CREATE TABLE ROUTECONTROLBULKPACKING
(
  ROUTECONTROLBULKPACKING_ID NUMBER NOT NULL,
  ROUTECONTROL_ID            NUMBER NOT NULL,
  CUSTOMER_LINK_ID           NUMBER NULL,
  PRODUCT_LINK_ID            NUMBER NOT NULL,
  SIZEDEFINITION_LINK_ID     NUMBER NOT NULL,
  FINISHINGMETHOD_ID         NUMBER NOT NULL,
  PRODUCTCODE                VARCHAR2(12) NOT NULL,
  PRODUCTDESCRIPTION         VARCHAR2(30) NOT NULL,
  BUNDLESIZE                 NUMBER DEFAULT 1 NOT NULL,
  STOCKLOCATION              VARCHAR2(20) NULL,
  TYPE                       NUMBER NOT NULL
                             CONSTRAINT TYPE_VALID2 CHECK (TYPE IN (1, 2, 3, 4)),
  QUANTITYPACKED             NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE DEFAULT SYSDATE NOT NULL
)
;

COMMENT ON COLUMN ROUTECONTROLBULKPACKING.TYPE
  IS '1 = Customer Logo Mat
2 = Bulk Logo Mat
3 = Pool
4 = One Way 
';

ALTER TABLE ROUTECONTROLBULKPACKING
  ADD CONSTRAINT XPKROUTECONTROLBULKPACKING PRIMARY KEY (ROUTECONTROLBULKPACKING_ID);

CREATE UNIQUE INDEX XAK1ROUTECONTROLBULKPACKING ON ROUTECONTROLBULKPACKING (NVL(CUSTOMER_LINK_ID,-1), PRODUCT_LINK_ID, SIZEDEFINITION_LINK_ID, FINISHINGMETHOD_ID);

CREATE INDEX XIF1ROUTECONTROLBULKPACKING ON ROUTECONTROLBULKPACKING (ROUTECONTROL_ID ASC);
CREATE INDEX XIF2ROUTECONTROLBULKPACKING ON ROUTECONTROLBULKPACKING (CUSTOMER_LINK_ID ASC);
CREATE INDEX XIF3ROUTECONTROLBULKPACKING ON ROUTECONTROLBULKPACKING (PRODUCT_LINK_ID ASC);
CREATE INDEX XIF4ROUTECONTROLBULKPACKING ON ROUTECONTROLBULKPACKING (SIZEDEFINITION_LINK_ID ASC);
CREATE INDEX XIF5ROUTECONTROLBULKPACKING ON ROUTECONTROLBULKPACKING (FINISHINGMETHOD_ID ASC);

ALTER TABLE ROUTECONTROLBULKPACKING
  ADD CONSTRAINT ROUTECONTROLBULKPACKING_RC FOREIGN KEY (ROUTECONTROL_ID)
  REFERENCES ROUTECONTROL (ROUTECONTROL_ID) ON DELETE CASCADE;
  
ALTER TABLE ROUTECONTROLBULKPACKING
  ADD CONSTRAINT ROUTECONTROLBULKPACKING_C FOREIGN KEY (CUSTOMER_LINK_ID)
  REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);
  
ALTER TABLE ROUTECONTROLBULKPACKING
  ADD CONSTRAINT ROUTECONTROLBULKPACKING_P FOREIGN KEY (PRODUCT_LINK_ID)
  REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);
  
ALTER TABLE ROUTECONTROLBULKPACKING
  ADD CONSTRAINT ROUTECONTROLBULKPACKING_S FOREIGN KEY (SIZEDEFINITION_LINK_ID)
  REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);
  
ALTER TABLE ROUTECONTROLBULKPACKING
  ADD CONSTRAINT ROUTECONTROLBULKPACKING_FM FOREIGN KEY (FINISHINGMETHOD_ID)
  REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);

-- ACTIVESTOCKLOCATIONRESERVE TABLE --
CREATE TABLE ACTIVESTOCKLOCATIONRESERVE
(
  ACTIVESTOCKLOCATION_ID NUMBER NOT NULL,
  ROUTE_ID               NUMBER NOT NULL,
  RESERVEDDATE           DATE NOT NULL,
  SYSTEMUSER_ID          NUMBER NOT NULL,
  TIMESTAMP              DATE NOT NULL
)
;

ALTER TABLE ACTIVESTOCKLOCATIONRESERVE
  ADD CONSTRAINT XPKACTIVESTOCKLOCATIONRESERVE PRIMARY KEY (ACTIVESTOCKLOCATION_ID);
  
CREATE INDEX XIF1ACTIVESTOCKLOCATIONRESERVE ON ACTIVESTOCKLOCATIONRESERVE (ROUTE_ID ASC);

ALTER TABLE ACTIVESTOCKLOCATIONRESERVE
  ADD CONSTRAINT ACTIVESTOCKLOCATIONRESERVE_R FOREIGN KEY (ROUTE_ID)
  REFERENCES ROUTE (ROUTE_ID) ON DELETE CASCADE;
  
ALTER TABLE ACTIVESTOCKLOCATIONRESERVE
  ADD CONSTRAINT ACTIVESTOCKLOCATIONRESERVE_AS FOREIGN KEY (ACTIVESTOCKLOCATION_ID)
  REFERENCES ACTIVESTOCKLOCATION (ACTIVESTOCKLOCATION_ID) ON DELETE CASCADE;

-- ACTIVESTOCKLOCATIONCONTENT TABLE --
CREATE TABLE ACTIVESTOCKLOCATIONCONTENT
(
  ACTIVESTOCKLOCATION_ID NUMBER NOT NULL,
  CUSTOMER_ID            NUMBER NULL,
  PRODUCT_ID             NUMBER NOT NULL,
  SIZEDEFINITION_ID      NUMBER NOT NULL,
  FINISHINGMETHOD_ID     NUMBER NULL,
  SYSTEMUSER_ID          NUMBER NOT NULL,
  TIMESTAMP              DATE NOT NULL
)
;

ALTER TABLE ACTIVESTOCKLOCATIONCONTENT
  ADD CONSTRAINT XPKACTIVESTOCKLOCATIONCONTENT PRIMARY KEY (ACTIVESTOCKLOCATION_ID, CUSTOMER_ID, PRODUCT_ID, SIZEDEFINITION_ID, FINISHINGMETHOD_ID);
  
CREATE INDEX XIF1ACTIVESTOCKLOCATIONCONTENT ON ACTIVESTOCKLOCATIONCONTENT (ACTIVESTOCKLOCATION_ID ASC);
CREATE INDEX XIF2ACTIVESTOCKLOCATIONCONTENT ON ACTIVESTOCKLOCATIONCONTENT (CUSTOMER_ID ASC);
CREATE INDEX XIF3ACTIVESTOCKLOCATIONCONTENT ON ACTIVESTOCKLOCATIONCONTENT (PRODUCT_ID ASC);
CREATE INDEX XIF4ACTIVESTOCKLOCATIONCONTENT ON ACTIVESTOCKLOCATIONCONTENT (SIZEDEFINITION_ID ASC);
CREATE INDEX XIF5ACTIVESTOCKLOCATIONCONTENT ON ACTIVESTOCKLOCATIONCONTENT (FINISHINGMETHOD_ID ASC);
CREATE INDEX XIE1ACTIVESTOCKLOCATIONCONTENT ON ACTIVESTOCKLOCATIONCONTENT (CUSTOMER_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, FINISHINGMETHOD_ID ASC);

ALTER TABLE ACTIVESTOCKLOCATIONCONTENT
  ADD CONSTRAINT ACTIVESTOCKLOCATIONCONTENT_AS FOREIGN KEY (ACTIVESTOCKLOCATION_ID)
  REFERENCES ACTIVESTOCKLOCATION (ACTIVESTOCKLOCATION_ID) ON DELETE CASCADE;
ALTER TABLE ACTIVESTOCKLOCATIONCONTENT
  ADD CONSTRAINT ACTIVESTOCKLOCATIONCONTENT_C FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;
ALTER TABLE ACTIVESTOCKLOCATIONCONTENT
  ADD CONSTRAINT ACTIVESTOCKLOCATIONCONTENT_P FOREIGN KEY (PRODUCT_ID)
  REFERENCES PRODUCT (PRODUCT_ID);
ALTER TABLE ACTIVESTOCKLOCATIONCONTENT
  ADD CONSTRAINT ACTIVESTOCKLOCATIONCONTENT_S FOREIGN KEY (SIZEDEFINITION_ID)
  REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID);
ALTER TABLE ACTIVESTOCKLOCATIONCONTENT
  ADD CONSTRAINT ACTIVESTOCKLOCATIONCONTENT_FM FOREIGN KEY (FINISHINGMETHOD_ID)
  REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);

-- ACTIVESTOCKLOCATIONADJACNT TABLE --
CREATE TABLE ACTIVESTOCKLOCATIONADJACNT
(
  ACTIVESTOCKLOCATION_ID    NUMBER NOT NULL,
  ADJACTIVESTOCKLOCATION_ID NUMBER NOT NULL,
  DISTANCEFACTOR            NUMBER DEFAULT 1 NOT NULL
                            CONSTRAINT DISTANCEFACTOR_VALID1 CHECK (DISTANCEFACTOR BETWEEN 0 AND 100),
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL
)
;

ALTER TABLE ACTIVESTOCKLOCATIONADJACNT
  ADD CONSTRAINT XPKACTIVESTOCKLOCATIONADJACNT PRIMARY KEY (ACTIVESTOCKLOCATION_ID, ADJACTIVESTOCKLOCATION_ID);
  
CREATE INDEX XIF1ACTIVESTOCKLOCATIONADJACNT ON ACTIVESTOCKLOCATIONADJACNT (ACTIVESTOCKLOCATION_ID ASC);
CREATE INDEX XIF2ACTIVESTOCKLOCATIONADJACNT ON ACTIVESTOCKLOCATIONADJACNT (ADJACTIVESTOCKLOCATION_ID ASC);

ALTER TABLE ACTIVESTOCKLOCATIONADJACNT
  ADD CONSTRAINT ACTIVESTOCKLOCATIONADJACNT_AS FOREIGN KEY (ACTIVESTOCKLOCATION_ID)
  REFERENCES ACTIVESTOCKLOCATION (ACTIVESTOCKLOCATION_ID) ON DELETE CASCADE;
ALTER TABLE ACTIVESTOCKLOCATIONADJACNT
  ADD CONSTRAINT ACTIVESTOCKLOCATIONADJACNT_AAS FOREIGN KEY (ADJACTIVESTOCKLOCATION_ID)
  REFERENCES ACTIVESTOCKLOCATION (ACTIVESTOCKLOCATION_ID) ON DELETE CASCADE;

-- ACTIVESTOCKLOCATIONDIST TABLE --
CREATE TABLE ACTIVESTOCKLOCATIONDIST
(
  BUSINESSUNIT_ID          NUMBER NOT NULL,
  ACTIVESTOCKLOCATION_ID   NUMBER NOT NULL,
  TOACTIVESTOCKLOCATION_ID NUMBER NOT NULL,
  DISTANCE                 NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID            NUMBER NOT NULL,
  TIMESTAMP                DATE NOT NULL
)
;

ALTER TABLE ACTIVESTOCKLOCATIONDIST
  ADD CONSTRAINT XPKACTIVESTOCKLOCATIONDIST PRIMARY KEY (BUSINESSUNIT_ID, ACTIVESTOCKLOCATION_ID, TOACTIVESTOCKLOCATION_ID);
 
CREATE UNIQUE INDEX XAK1ACTIVESTOCKLOCATIONDIST ON ACTIVESTOCKLOCATIONDIST (ACTIVESTOCKLOCATION_ID ASC, TOACTIVESTOCKLOCATION_ID ASC);

CREATE INDEX XIE1ACTIVESTOCKLOCATIONDIST ON ACTIVESTOCKLOCATIONDIST (BUSINESSUNIT_ID ASC, ACTIVESTOCKLOCATION_ID ASC);

CREATE INDEX XIF1ACTIVESTOCKLOCATIONDIST ON ACTIVESTOCKLOCATIONDIST (BUSINESSUNIT_ID ASC);
CREATE INDEX XIF2ACTIVESTOCKLOCATIONDIST ON ACTIVESTOCKLOCATIONDIST (ACTIVESTOCKLOCATION_ID ASC);
CREATE INDEX XIF3ACTIVESTOCKLOCATIONDIST ON ACTIVESTOCKLOCATIONDIST (TOACTIVESTOCKLOCATION_ID ASC);


ALTER TABLE ACTIVESTOCKLOCATIONDIST
  ADD CONSTRAINT ACTIVESTOCKLOCATIONDIST_B FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE CASCADE;
ALTER TABLE ACTIVESTOCKLOCATIONDIST
  ADD CONSTRAINT ACTIVESTOCKLOCATIONDIST_AS FOREIGN KEY (ACTIVESTOCKLOCATION_ID)
  REFERENCES ACTIVESTOCKLOCATION (ACTIVESTOCKLOCATION_ID) ON DELETE CASCADE;
ALTER TABLE ACTIVESTOCKLOCATIONDIST
  ADD CONSTRAINT ACTIVESTOCKLOCATIONDIST_TOAS FOREIGN KEY (TOACTIVESTOCKLOCATION_ID)
  REFERENCES ACTIVESTOCKLOCATION (ACTIVESTOCKLOCATION_ID) ON DELETE CASCADE;
