--WEIGHINGCATEGPERCUSTOMER--

ALTER TABLE WEIGHINGCATEGPERCUSTOMER ADD (REFERENCEWEIGHING VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD251  CHECK (REFERENCEWEIGHING IN ('Y', 'N')));

------------ REFERENCEWEIGHING -------------------
CREATE SEQUENCE SEQ_REFERENCEWEIGHING
START WITH 1
INCREMENT BY 1;

CREATE TABLE REFERENCEWEIGHING
(
  REFERENCEWEIGHING_ID      NUMBER NOT NULL,
  CUSTOMER_ID               NUMBER NOT NULL,
  WEIGHINGCATEGORY_ID       NUMBER NOT NULL,
  WEIGHINGDATE              DATE DEFAULT TRUNC(SYSDATE) NOT NULL,
  WEIGHT                    NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL
);

ALTER TABLE REFERENCEWEIGHING
  ADD CONSTRAINT XPKREFERENCEWEIGHING PRIMARY KEY (REFERENCEWEIGHING_ID);
  
CREATE UNIQUE INDEX XAK1REFERENCEWEIGHING ON REFERENCEWEIGHING (CUSTOMER_ID ASC, WEIGHINGCATEGORY_ID ASC, WEIGHINGDATE ASC);

CREATE INDEX XIF1REFERENCEWEIGHING ON REFERENCEWEIGHING (CUSTOMER_ID ASC);

CREATE INDEX XIF2REFERENCEWEIGHING ON REFERENCEWEIGHING (WEIGHINGCATEGORY_ID ASC);
 
ALTER TABLE REFERENCEWEIGHING
ADD CONSTRAINT REFERENCEWEIGHING_CUSTOMER FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;
  
ALTER TABLE REFERENCEWEIGHING
ADD CONSTRAINT REFERENCEWEIGHING_WC FOREIGN KEY (WEIGHINGCATEGORY_ID)
REFERENCES WEIGHINGCATEGORY (WEIGHINGCATEGORY_ID) ON DELETE CASCADE;



------------ REFERENCEWEIGHINGDETAILS -------------------
CREATE SEQUENCE SEQ_REFERENCEWEIGHINGDETAILS
START WITH 1
INCREMENT BY 1;

CREATE TABLE REFERENCEWEIGHINGDETAILS
(
  REFERENCEWEIGHINGDETAILS_ID  NUMBER NOT NULL,
  REFERENCEWEIGHING_ID         NUMBER NOT NULL,
  PRODUCT_LINK_ID              NUMBER NOT NULL,
  SIZEDEFINITION_LINK_ID       NUMBER NOT NULL,
  FINISHINGMETHOD_ID           NUMBER NOT NULL,
  QUANTITY                     NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID                NUMBER NOT NULL,
  TIMESTAMP                    DATE NOT NULL
);

ALTER TABLE REFERENCEWEIGHINGDETAILS
  ADD CONSTRAINT XPKREFERENCEWEIGHINGDETAILS PRIMARY KEY (REFERENCEWEIGHINGDETAILS_ID);
  
CREATE UNIQUE INDEX XAK1REFERENCEWEIGHINGDETAILS ON REFERENCEWEIGHINGDETAILS (REFERENCEWEIGHING_ID ASC, PRODUCT_LINK_ID ASC, SIZEDEFINITION_LINK_ID ASC, FINISHINGMETHOD_ID ASC);

CREATE INDEX XIF1REFERENCEWEIGHINGDETAILS ON REFERENCEWEIGHINGDETAILS (REFERENCEWEIGHING_ID ASC);

CREATE INDEX XIF2REFERENCEWEIGHINGDETAILS ON REFERENCEWEIGHINGDETAILS (PRODUCT_LINK_ID ASC);

CREATE INDEX XIF3REFERENCEWEIGHINGDETAILS ON REFERENCEWEIGHINGDETAILS (SIZEDEFINITION_LINK_ID ASC);

CREATE INDEX XIF4REFERENCEWEIGHINGDETAILS ON REFERENCEWEIGHINGDETAILS (FINISHINGMETHOD_ID ASC);
 
ALTER TABLE REFERENCEWEIGHINGDETAILS
ADD CONSTRAINT REFERENCEWEIGHINGDETAILS_RF FOREIGN KEY (REFERENCEWEIGHING_ID)
REFERENCES REFERENCEWEIGHING (REFERENCEWEIGHING_ID) ON DELETE CASCADE;
  
ALTER TABLE REFERENCEWEIGHINGDETAILS
ADD CONSTRAINT REFERENCEWEIGHINGDETAILS_P FOREIGN KEY (PRODUCT_LINK_ID)
REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);
  
ALTER TABLE REFERENCEWEIGHINGDETAILS
ADD CONSTRAINT REFERENCEWEIGHINGDETAILS_SD FOREIGN KEY (SIZEDEFINITION_LINK_ID)
REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);
  
ALTER TABLE REFERENCEWEIGHINGDETAILS
ADD CONSTRAINT REFERENCEWEIGHINGDETAILS_FM FOREIGN KEY (FINISHINGMETHOD_ID)
REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);


-----WEIGHTBASEDDELHISTORY------

CREATE SEQUENCE SEQ_WEIGHTBASEDDELHISTORY
START WITH 1
INCREMENT BY 1;

CREATE TABLE WEIGHTBASEDDELHISTORY
(
  WEIGHTBASEDDELHISTORY_ID    NUMBER NOT NULL,
  CUSTOMER_LINK_ID            NUMBER NOT NULL,
  DISTRIBUTIONPOINT_LINK_ID   NUMBER NOT NULL,
  BUSINESSUNIT_ID             NUMBER NOT NULL,
  PRODUCT_LINK_ID             NUMBER NOT NULL,
  SIZEDEFINITION_LINK_ID      NUMBER NOT NULL,
  FINISHINGMETHOD_ID          NUMBER NOT NULL,
  DELIVERYDATE                DATE NOT NULL,
  QUANTITYDELIVERED           NUMBER DEFAULT 0 NOT NULL,
  REFERENCEWEIGHING_ID        NUMBER NOT NULL,
  SYSTEMUSER_ID               NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL
);

ALTER TABLE WEIGHTBASEDDELHISTORY
  ADD CONSTRAINT XPKWEIGHTBASEDDELHISTORY PRIMARY KEY (WEIGHTBASEDDELHISTORY_ID);
  
CREATE UNIQUE INDEX XAK1WEIGHTBASEDDELHISTORY ON WEIGHTBASEDDELHISTORY (CUSTOMER_LINK_ID ASC, DISTRIBUTIONPOINT_LINK_ID ASC, BUSINESSUNIT_ID ASC, PRODUCT_LINK_ID ASC, SIZEDEFINITION_LINK_ID ASC, FINISHINGMETHOD_ID ASC, DELIVERYDATE ASC);

CREATE INDEX XIF1WEIGHTBASEDDELHISTORY ON WEIGHTBASEDDELHISTORY (CUSTOMER_LINK_ID ASC);

CREATE INDEX XIF2WEIGHTBASEDDELHISTORY ON WEIGHTBASEDDELHISTORY (DISTRIBUTIONPOINT_LINK_ID ASC);

CREATE INDEX XIF3WEIGHTBASEDDELHISTORY ON WEIGHTBASEDDELHISTORY (BUSINESSUNIT_ID ASC);

CREATE INDEX XIF4WEIGHTBASEDDELHISTORY ON WEIGHTBASEDDELHISTORY (PRODUCT_LINK_ID ASC);

CREATE INDEX XIF5WEIGHTBASEDDELHISTORY ON WEIGHTBASEDDELHISTORY (SIZEDEFINITION_LINK_ID ASC);

CREATE INDEX XIF6WEIGHTBASEDDELHISTORY ON WEIGHTBASEDDELHISTORY (FINISHINGMETHOD_ID ASC);

CREATE INDEX XIF7WEIGHTBASEDDELHISTORY ON WEIGHTBASEDDELHISTORY (REFERENCEWEIGHING_ID ASC);
 
ALTER TABLE WEIGHTBASEDDELHISTORY
ADD CONSTRAINT WEIGHTBASEDDELHISTORY_CUSTOMER FOREIGN KEY (CUSTOMER_LINK_ID)
REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);
  
ALTER TABLE WEIGHTBASEDDELHISTORY
ADD CONSTRAINT WEIGHTBASEDDELHISTORY_DP FOREIGN KEY (DISTRIBUTIONPOINT_LINK_ID)
REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);
  
ALTER TABLE WEIGHTBASEDDELHISTORY
ADD CONSTRAINT WEIGHTBASEDDELHISTORY_BU FOREIGN KEY (BUSINESSUNIT_ID)
REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);
  
ALTER TABLE WEIGHTBASEDDELHISTORY
ADD CONSTRAINT WEIGHTBASEDDELHISTORY_PL FOREIGN KEY (PRODUCT_LINK_ID)
REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);
  
ALTER TABLE WEIGHTBASEDDELHISTORY
ADD CONSTRAINT WEIGHTBASEDDELHISTORY_SD FOREIGN KEY (SIZEDEFINITION_LINK_ID)
REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);
  
ALTER TABLE WEIGHTBASEDDELHISTORY
ADD CONSTRAINT WEIGHTBASEDDELHISTORY_FM FOREIGN KEY (FINISHINGMETHOD_ID)
REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);
  
ALTER TABLE WEIGHTBASEDDELHISTORY
ADD CONSTRAINT WEIGHTBASEDDELHISTORY_RW FOREIGN KEY (REFERENCEWEIGHING_ID)
REFERENCES REFERENCEWEIGHING (REFERENCEWEIGHING_ID) ON DELETE SET NULL;



-------------PACKINGNOTECONTAINER -------------------------------
ALTER TABLE PACKINGNOTECONTAINER ADD (WEIGHTBASEDDELHISTORY_ID NUMBER NULL,
                                      REFERENCEWEIGHING VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD252  CHECK (REFERENCEWEIGHING IN ('Y', 'N')));

CREATE INDEX XIF6PACKINGNOTECONTAINER ON PACKINGNOTECONTAINER (WEIGHTBASEDDELHISTORY_ID ASC);
 
ALTER TABLE PACKINGNOTECONTAINER
ADD CONSTRAINT PACKINGNOTECONTAINER_WBDH FOREIGN KEY (WEIGHTBASEDDELHISTORY_ID)
REFERENCES WEIGHTBASEDDELHISTORY (WEIGHTBASEDDELHISTORY_ID) ON DELETE SET NULL;



-------------WEIGHINGTICKETLINE-------------------------------

ALTER TABLE WEIGHINGTICKETLINE ADD (WEIGHTBASEDDELHISTORY_ID NUMBER NULL,
                                    REFERENCEWEIGHING VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD253  CHECK (REFERENCEWEIGHING IN ('Y', 'N')));

CREATE INDEX XIF8WEIGHINGTICKETLINE ON WEIGHINGTICKETLINE (WEIGHTBASEDDELHISTORY_ID ASC);
 
ALTER TABLE WEIGHINGTICKETLINE
ADD CONSTRAINT WEIGHINGTICKETLINE_WBDH FOREIGN KEY (WEIGHTBASEDDELHISTORY_ID)
REFERENCES WEIGHTBASEDDELHISTORY (WEIGHTBASEDDELHISTORY_ID) ON DELETE SET NULL;