--- ABS-17657
---------------------------SUPPLIERMODEL---------------------------------
CREATE SEQUENCE SEQ_SUPPLIERMODEL
START WITH 1
INCREMENT BY 1;

 CREATE TABLE SUPPLIERMODEL
(SUPPLIERMODEL_ID     NUMBER NOT NULL,
  SUPPLIER_ID                  NUMBER NOT NULL,
  CODE                               VARCHAR2(12) NOT NULL,
  DESCRIPTION                 VARCHAR2(50) NOT NULL,
  REMARK                          VARCHAR2(1000) NULL,
  DATEACTIVE                   DATE DEFAULT SYSDATE NOT NULL,
  DATEINACTIVE               DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  SYSTEMUSER_ID            NUMBER NOT NULL,
  TIMESTAMP                     DATE NOT NULL);

ALTER TABLE SUPPLIERMODEL
  ADD CONSTRAINT XPKSUPPLIERMODEL PRIMARY KEY (SUPPLIERMODEL_ID);

CREATE UNIQUE INDEX XAK1SUPPLIERMODEL ON SUPPLIERMODEL (SUPPLIER_ID ASC, CODE ASC);

CREATE INDEX XIF1SUPPLIERMODEL ON SUPPLIERMODEL(SUPPLIER_ID ASC);

ALTER TABLE SUPPLIERMODEL
  ADD CONSTRAINT SUPPLIERMODEL_SUPPLIER FOREIGN KEY (SUPPLIER_ID)
  REFERENCES SUPPLIER (SUPPLIER_ID);

---------------------------AGGSUPPLIERPRODUCT---------------------------------

CREATE SEQUENCE SEQ_AGGSUPPLIERPRODUCT
START WITH 1
INCREMENT BY 1;

 CREATE TABLE AGGSUPPLIERPRODUCT
(
  AGGSUPPLIERPRODUCT_ID     NUMBER NOT NULL,
  SUPPLIER_ID                               NUMBER NOT NULL,
  CODE                                           VARCHAR2(12) NOT NULL,
  DESCRIPTION                             VARCHAR2(50) NOT NULL,
  REMARK                                      VARCHAR2(1000) NULL,
  DATEACTIVE                               DATE DEFAULT SYSDATE NOT NULL,
  DATEINACTIVE                           DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  ORDERTERMSLEVEL                 NUMBER DEFAULT 2 NOT NULL CONSTRAINT ORDERTERMSLEVEL_VALID  CHECK (ORDERTERMSLEVEL IN (1, 2)),
  PRICETERMSLEVEL                   NUMBER DEFAULT 2 NOT NULL CONSTRAINT PRICETERMSLEVEL_VALID  CHECK (PRICETERMSLEVEL IN (1, 2)),
  SUPPLIERMODEL_ID                 NUMBER NULL,
  SIZETYPE_ID                              NUMBER NOT NULL,
  PREWASH                                    NUMBER DEFAULT 0 NOT NULL CONSTRAINT PREWASH_VALID  CHECK (PREWASH IN (0, 1)),
  CODEUNIQUELY                         NUMBER DEFAULT 0 NOT NULL CONSTRAINT CODEUNIQUELY_VALID  CHECK (CODEUNIQUELY IN (0, 1)),
  SYSTEMUSER_ID            	       NUMBER NOT NULL,
  TIMESTAMP                                DATE NOT NULL);

COMMENT ON COLUMN AGGSUPPLIERPRODUCT.ORDERTERMSLEVEL IS '1 = Supplier Model
2 = Supplier product
';

COMMENT ON COLUMN AGGSUPPLIERPRODUCT.PRICETERMSLEVEL IS '1 = Supplier Model
2 = Supplier product
';

COMMENT ON COLUMN AGGSUPPLIERPRODUCT.PREWASH IS '0 = Not applicable / No prewash
1 = Prewash
';

COMMENT ON COLUMN AGGSUPPLIERPRODUCT.CODEUNIQUELY IS '0 = Not applicable / Not coded uniquely
1 = Code uniquely
';

ALTER TABLE AGGSUPPLIERPRODUCT
  ADD CONSTRAINT XPKAGGSUPPLIERPRODUCT PRIMARY KEY (AGGSUPPLIERPRODUCT_ID);

CREATE UNIQUE INDEX XAK1AGGSUPPLIERPRODUCT ON AGGSUPPLIERPRODUCT (SUPPLIER_ID ASC, CODE ASC);

CREATE INDEX XIF1AGGSUPPLIERPRODUCT ON AGGSUPPLIERPRODUCT(SUPPLIER_ID ASC);

CREATE INDEX XIF2AGGSUPPLIERPRODUCT ON AGGSUPPLIERPRODUCT(SUPPLIERMODEL_ID ASC);

CREATE INDEX XIF3AGGSUPPLIERPRODUCT ON AGGSUPPLIERPRODUCT(SIZETYPE_ID ASC);

ALTER TABLE AGGSUPPLIERPRODUCT
  ADD CONSTRAINT AGGSUPPLIERPRODUCT_SUPPLIER FOREIGN KEY (SUPPLIER_ID)
  REFERENCES SUPPLIER (SUPPLIER_ID);

ALTER TABLE AGGSUPPLIERPRODUCT
  ADD CONSTRAINT AGGSUPPLIERPRODUCT_SUPPLIERMOD FOREIGN KEY (SUPPLIERMODEL_ID)
  REFERENCES SUPPLIERMODEL (SUPPLIERMODEL_ID);

ALTER TABLE AGGSUPPLIERPRODUCT
  ADD CONSTRAINT AGGSUPPLIERPRODUCT_SIZETYPE FOREIGN KEY (SIZETYPE_ID)
  REFERENCES SIZETYPE(SIZETYPE_ID);

---------------------------------------AGGSUPPLIERPRODUCTSIZE-------------------------------

CREATE SEQUENCE SEQ_AGGSUPPLIERPRODUCTSIZE
START WITH 1
INCREMENT BY 1;

 CREATE TABLE AGGSUPPLIERPRODUCTSIZE
(
  AGGSUPPLIERPRODUCTSIZE_ID     NUMBER NOT NULL,
  AGGSUPPLIERPRODUCT_ID             NUMBER NOT NULL,
  SIZEDEFINITION_ID                           NUMBER NOT NULL,
  SEQUENCENUMBER                           NUMBER DEFAULT 0 NOT NULL,
  REMARK                                              VARCHAR2(1000) NULL,
  DATEACTIVE                               DATE DEFAULT SYSDATE NOT NULL,
  DATEINACTIVE                           DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  ORDERTERMSLEVEL                 NUMBER DEFAULT 2 NOT NULL CONSTRAINT ORDERTERMSLEVEL_VALID1  CHECK (ORDERTERMSLEVEL IN (1, 2, 3)),
  PRICETERMSLEVEL                   NUMBER DEFAULT 2 NOT NULL CONSTRAINT PRICETERMSLEVEL_VALID1  CHECK (PRICETERMSLEVEL IN (1, 2, 3)),
  SYSTEMUSER_ID            	       NUMBER NOT NULL,
  TIMESTAMP                                DATE NOT NULL);
  

COMMENT ON COLUMN AGGSUPPLIERPRODUCTSIZE.ORDERTERMSLEVEL IS '1 = Supplier model
2 = Supplier product
3 = Supplier product size
';

COMMENT ON COLUMN AGGSUPPLIERPRODUCTSIZE.PRICETERMSLEVEL IS '1 = Supplier model
2 = Supplier product
3 = Supplier product size
';

ALTER TABLE AGGSUPPLIERPRODUCTSIZE
  ADD CONSTRAINT XPKAGGSUPPLIERPRODUCTSIZE PRIMARY KEY (AGGSUPPLIERPRODUCTSIZE_ID);

CREATE UNIQUE INDEX XAK1AGGSUPPLIERPRODUCTSIZE ON AGGSUPPLIERPRODUCTSIZE (AGGSUPPLIERPRODUCT_ID ASC, SIZEDEFINITION_ID ASC);

CREATE UNIQUE INDEX XAK2AGGSUPPLIERPRODUCTSIZE ON AGGSUPPLIERPRODUCTSIZE (AGGSUPPLIERPRODUCT_ID ASC, SEQUENCENUMBER ASC);

CREATE INDEX XIF1AGGSUPPLIERPRODUCTSIZE ON AGGSUPPLIERPRODUCTSIZE(AGGSUPPLIERPRODUCT_ID ASC);

CREATE INDEX XIF2AGGSUPPLIERPRODUCTSIZE ON AGGSUPPLIERPRODUCTSIZE(SIZEDEFINITION_ID ASC);

ALTER TABLE AGGSUPPLIERPRODUCTSIZE
  ADD CONSTRAINT AGGSUPPLIERPRODSZ_AGGSUPPROD FOREIGN KEY (AGGSUPPLIERPRODUCT_ID)
  REFERENCES AGGSUPPLIERPRODUCT (AGGSUPPLIERPRODUCT_ID) ON DELETE CASCADE;

ALTER TABLE AGGSUPPLIERPRODUCTSIZE
  ADD CONSTRAINT AGGSUPPLIERPRODSZ_SIZEDEF FOREIGN KEY (SIZEDEFINITION_ID)
  REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID);

---------------------------------------PRODSIZEAGGSUPPLPRODSIZE------------------------------
CREATE SEQUENCE SEQ_PRODSIZEAGGSUPPLPRODSIZE
START WITH 1
INCREMENT BY 1;

 CREATE TABLE PRODSIZEAGGSUPPLPRODSIZE
(
  PRODSIZEAGGSUPPLPRODSIZE_ID     NUMBER NOT NULL,
  AGGSUPPLIERPRODUCTSIZE_ID             NUMBER NOT NULL,
  PRODUCT_ID                                              NUMBER NOT NULL,
  SIZEDEFINITION_ID                           NUMBER NOT NULL,
  SYSTEMUSER_ID            	       NUMBER NOT NULL,
  TIMESTAMP                                DATE NOT NULL);
  
ALTER TABLE PRODSIZEAGGSUPPLPRODSIZE
  ADD CONSTRAINT XPKPRODSZPERAGGSUPPRODSIZE PRIMARY KEY (PRODSIZEAGGSUPPLPRODSIZE_ID);

CREATE UNIQUE INDEX XAK1PRODSZPERAGGSUPPRODSIZE ON PRODSIZEAGGSUPPLPRODSIZE (AGGSUPPLIERPRODUCTSIZE_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC);

CREATE INDEX XIF1PRODSZPERAGGSUPPRODSIZE ON PRODSIZEAGGSUPPLPRODSIZE(AGGSUPPLIERPRODUCTSIZE_ID ASC);

CREATE INDEX XIF2PRODSZPERAGGSUPPRODSIZE ON PRODSIZEAGGSUPPLPRODSIZE(PRODUCT_ID ASC);

CREATE INDEX XIF3PRODSZPERAGGSUPPRODSIZE ON PRODSIZEAGGSUPPLPRODSIZE(SIZEDEFINITION_ID ASC);

ALTER TABLE PRODSIZEAGGSUPPLPRODSIZE
  ADD CONSTRAINT PRODSZAGGSUPPRODSZ_AGGSUPRODSZ FOREIGN KEY (AGGSUPPLIERPRODUCTSIZE_ID)
  REFERENCES AGGSUPPLIERPRODUCTSIZE (AGGSUPPLIERPRODUCTSIZE_ID);

ALTER TABLE PRODSIZEAGGSUPPLPRODSIZE
  ADD CONSTRAINT PRODSZAGGSUPPRODSZ_PROD FOREIGN KEY (PRODUCT_ID)
  REFERENCES PRODUCT (PRODUCT_ID) ON DELETE CASCADE;

ALTER TABLE PRODSIZEAGGSUPPLPRODSIZE
  ADD CONSTRAINT PRODSZAGGSUPPRODSZ_SIZE FOREIGN KEY (SIZEDEFINITION_ID)
  REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID) ON DELETE CASCADE;


---------------------------AGGSUPPLIERPRICETERMS---------------------------------

CREATE SEQUENCE SEQ_AGGSUPPLIERPRICETERMS
START WITH 1
INCREMENT BY 1;

 CREATE TABLE AGGSUPPLIERPRICETERMS
(
  SUPPLIERPRICETERMS_ID               NUMBER NOT NULL,
  SUPPLIERMODEL_ID                          NUMBER NULL,
  AGGSUPPLIERPRODUCT_ID             NUMBER NULL,
  AGGSUPPLIERPRODUCTSIZE_ID     NUMBER NULL,
  PRICETERMSLEVEL                           NUMBER DEFAULT 2 NOT NULL CONSTRAINT PRICETERMSLEVEL_VALID2  CHECK (PRICETERMSLEVEL IN (1, 2, 3)),
  STARTDATE                                        DATE DEFAULT SYSDATE NOT NULL,
  ORDERQUANTITY                             NUMBER DEFAULT 2 NOT NULL,
  DELIVERYDAYS                                  NUMBER DEFAULT 1 NOT NULL,
  PREWASH                                            NUMBER DEFAULT 0 NOT NULL CONSTRAINT PREWASH_VALID1  CHECK (PREWASH IN (0, 1)),
  CODEUNIQUELY                                 NUMBER DEFAULT 0 NOT NULL CONSTRAINT CODEUNIQUELY_VALID1  CHECK (CODEUNIQUELY IN (0, 1)),
  PURCHASEPRICE                               NUMBER(15,6) DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID            	               NUMBER NOT NULL,
  TIMESTAMP                                        DATE NOT NULL);


ALTER TABLE AGGSUPPLIERPRICETERMS ADD CONSTRAINT AGGSUPPLIERPRICETERMS_VALID CHECK (((SUPPLIERMODEL_ID IS NOT NULL) AND (AGGSUPPLIERPRODUCTSIZE_ID IS NULL) AND (AGGSUPPLIERPRODUCT_ID IS NULL) AND (PRICETERMSLEVEL = 1)) OR
((SUPPLIERMODEL_ID IS NULL) AND (AGGSUPPLIERPRODUCTSIZE_ID IS NULL) AND (AGGSUPPLIERPRODUCT_ID IS NOT NULL) AND (PRICETERMSLEVEL = 2)) OR
((SUPPLIERMODEL_ID IS NULL) AND (AGGSUPPLIERPRODUCTSIZE_ID IS NOT NULL) AND (AGGSUPPLIERPRODUCT_ID IS NULL) AND (PRICETERMSLEVEL = 3)));

COMMENT ON COLUMN AGGSUPPLIERPRICETERMS.PRICETERMSLEVEL IS '1 = Supplier model
2 = Supplier product
3 = Supplier product size
';
 
COMMENT ON COLUMN AGGSUPPLIERPRICETERMS.PREWASH IS '0 = Not applicable / No prewash
1 = Prewash
';

COMMENT ON COLUMN AGGSUPPLIERPRICETERMS.CODEUNIQUELY IS '0 = Not applicable / Not coded uniquely
1 = Code uniquely
';

ALTER TABLE AGGSUPPLIERPRICETERMS
  ADD CONSTRAINT XPKAGGSUPPLIERPRICETERMS  PRIMARY KEY (SUPPLIERPRICETERMS_ID);

CREATE UNIQUE INDEX XAK1AGGSUPPLIERPRICETERMS ON AGGSUPPLIERPRICETERMS (SUPPLIERMODEL_ID ASC, AGGSUPPLIERPRODUCT_ID ASC, AGGSUPPLIERPRODUCTSIZE_ID ASC, PRICETERMSLEVEL ASC, STARTDATE ASC, ORDERQUANTITY ASC, DELIVERYDAYS ASC, PREWASH ASC, CODEUNIQUELY ASC);

CREATE INDEX XIF1AGGSUPPLIERPRICETERMS ON AGGSUPPLIERPRICETERMS(SUPPLIERMODEL_ID ASC);

CREATE INDEX XIF2AGGSUPPLIERPRICETERMS ON AGGSUPPLIERPRICETERMS(AGGSUPPLIERPRODUCT_ID ASC);

CREATE INDEX XIF3AGGSUPPLIERPRICETERMS ON AGGSUPPLIERPRICETERMS(AGGSUPPLIERPRODUCTSIZE_ID ASC);

ALTER TABLE AGGSUPPLIERPRICETERMS
  ADD CONSTRAINT AGGSUPPLPRICETERMS_SUPPMOD FOREIGN KEY (SUPPLIERMODEL_ID)
  REFERENCES SUPPLIERMODEL (SUPPLIERMODEL_ID) ON DELETE CASCADE;

ALTER TABLE AGGSUPPLIERPRICETERMS
  ADD CONSTRAINT AGGSUPPLPRICETERMS_AGGSUPPROD FOREIGN KEY (AGGSUPPLIERPRODUCT_ID)
  REFERENCES AGGSUPPLIERPRODUCT (AGGSUPPLIERPRODUCT_ID) ON DELETE CASCADE;

ALTER TABLE AGGSUPPLIERPRICETERMS
  ADD CONSTRAINT AGGSUPPRICETERM_AGGSUPPRODSZ FOREIGN KEY (AGGSUPPLIERPRODUCTSIZE_ID)
  REFERENCES AGGSUPPLIERPRODUCTSIZE (AGGSUPPLIERPRODUCTSIZE_ID) ON DELETE CASCADE;

---------------------------AGGSUPPLIERORDERINGTERMS---------------------------------

CREATE SEQUENCE SEQ_AGGSUPPLIERORDERINGTERMS
START WITH 1
INCREMENT BY 1;

 CREATE TABLE AGGSUPPLIERORDERINGTERMS
(
  SUPPLIERORDERINGTERMS_ID       NUMBER NOT NULL,
  SUPPLIERMODEL_ID                          NUMBER NULL,
  AGGSUPPLIERPRODUCT_ID             NUMBER NULL,
  AGGSUPPLIERPRODUCTSIZE_ID     NUMBER NULL,
  ORDERTERMSLEVEL                         NUMBER DEFAULT 2 NOT NULL CONSTRAINT ORDERTERMSLEVEL_VALID2  CHECK (ORDERTERMSLEVEL IN (1, 2, 3)),
  STARTDATE                                        DATE DEFAULT SYSDATE NOT NULL,
  DELIVERYDAYS                                  NUMBER DEFAULT 1 NOT NULL,
  MINORDERQUANTITY                       NUMBER DEFAULT 1 NOT NULL,
  PACKAGEQUANTITY                         NUMBER DEFAULT 1 NOT NULL,
  TRANSPORTCHARGEPERCENTAGE   NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSPORTCHARGEPERC_VALID CHECK (TRANSPORTCHARGEPERCENTAGE BETWEEN 0 AND 100),
  SYSTEMUSER_ID            	       NUMBER NOT NULL,
  TIMESTAMP                                DATE NOT NULL);

ALTER TABLE AGGSUPPLIERORDERINGTERMS ADD CONSTRAINT AGGSUPPLIERORDERINGTERMS_VALID CHECK (((SUPPLIERMODEL_ID IS NOT NULL) AND (AGGSUPPLIERPRODUCTSIZE_ID IS NULL) AND (AGGSUPPLIERPRODUCT_ID IS NULL) AND (ORDERTERMSLEVEL = 1)) OR
((SUPPLIERMODEL_ID IS NULL) AND (AGGSUPPLIERPRODUCTSIZE_ID IS NULL) AND (AGGSUPPLIERPRODUCT_ID IS NOT NULL) AND (ORDERTERMSLEVEL = 2)) OR
((SUPPLIERMODEL_ID IS NULL) AND (AGGSUPPLIERPRODUCTSIZE_ID IS NOT NULL) AND (AGGSUPPLIERPRODUCT_ID IS NULL) AND (ORDERTERMSLEVEL = 3)));

COMMENT ON COLUMN AGGSUPPLIERORDERINGTERMS.ORDERTERMSLEVEL IS '1 = Supplier model
2 = Supplier product
3 = Supplier product size
';

ALTER TABLE AGGSUPPLIERORDERINGTERMS
  ADD CONSTRAINT XPKAGGSUPPLIERORDERINGTERMS PRIMARY KEY (SUPPLIERORDERINGTERMS_ID);

CREATE UNIQUE INDEX XAK1AGGSUPPLIERORDERINGTERMS ON AGGSUPPLIERORDERINGTERMS (SUPPLIERMODEL_ID ASC, AGGSUPPLIERPRODUCT_ID ASC, AGGSUPPLIERPRODUCTSIZE_ID ASC, ORDERTERMSLEVEL ASC, STARTDATE ASC);

CREATE INDEX XIF1AGGSUPPLIERORDERINGTERMS ON AGGSUPPLIERORDERINGTERMS(SUPPLIERMODEL_ID ASC);

CREATE INDEX XIF2AGGSUPPLIERORDERINGTERMS ON AGGSUPPLIERORDERINGTERMS(AGGSUPPLIERPRODUCT_ID ASC);

CREATE INDEX XIF3AGGSUPPLIERORDERINGTERMS ON AGGSUPPLIERORDERINGTERMS(AGGSUPPLIERPRODUCTSIZE_ID ASC);

ALTER TABLE AGGSUPPLIERORDERINGTERMS
  ADD CONSTRAINT AGGSUPPLORDERTERMS_SUPPMOD FOREIGN KEY (SUPPLIERMODEL_ID)
  REFERENCES SUPPLIERMODEL (SUPPLIERMODEL_ID) ON DELETE CASCADE;

ALTER TABLE AGGSUPPLIERORDERINGTERMS
  ADD CONSTRAINT AGGSUPPLORDERTERMS_AGGSUPPROD FOREIGN KEY (AGGSUPPLIERPRODUCT_ID)
  REFERENCES AGGSUPPLIERPRODUCT (AGGSUPPLIERPRODUCT_ID) ON DELETE CASCADE;

ALTER TABLE AGGSUPPLIERORDERINGTERMS
  ADD CONSTRAINT AGGSUPORDTERM_AGGSUPPRODSZ FOREIGN KEY (AGGSUPPLIERPRODUCTSIZE_ID)
  REFERENCES AGGSUPPLIERPRODUCTSIZE (AGGSUPPLIERPRODUCTSIZE_ID) ON DELETE CASCADE;


-------------------------------SIZETYPE--------------
ALTER TABLE SIZETYPE
	ADD (USAGETYPE NUMBER DEFAULT 1 NOT NULL CONSTRAINT USAGETYPE_VALID CHECK (USAGETYPE IN (1, 2)));

COMMENT ON COLUMN SIZETYPE.USAGETYPE IS '1 = Laundry sizes
2 = Supplier sizes
';

DROP INDEX XAK2SIZETYPE;

CREATE UNIQUE INDEX XAK1SIZETYPE ON SIZETYPE (USAGETYPE ASC, CODE ASC);

UPDATE SIZETYPE S
SET S.USAGETYPE = 1;
COMMIT;

