-- ABS-32305
------------   CUSTOMERRENTPRICE --------------

ALTER TABLE CUSTOMERRENTPRICE ADD (SIZEGROUPINGCODE_ID NUMBER NULL);

DROP INDEX XAK1CUSTOMERRENTPRICE;

CREATE UNIQUE INDEX XAK1CUSTOMERRENTPRICE ON CUSTOMERRENTPRICE (PRODUCTLISTPERCUSTOMER_ID ASC, STARTDATE ASC, CHANGESPERWEEK ASC, TYPEOFRENT ASC, INVOICEFREQUENCYNUMBER ASC, SIZEGROUPINGCODE_ID ASC);

CREATE INDEX XIF1CUSTOMERRENTPRICE ON CUSTOMERRENTPRICE (SIZEGROUPINGCODE_ID ASC);

ALTER TABLE CUSTOMERRENTPRICE ADD CONSTRAINT CUSTOMERRENTPRICE_SZGROUPCODE FOREIGN KEY (SIZEGROUPINGCODE_ID)
REFERENCES SIZEGROUPINGCODE (SIZEGROUPINGCODE_ID);




------------   CUSTOMERREPLACEMENTPRICE --------------

ALTER TABLE CUSTOMERREPLACEMENTPRICE ADD (SIZEGROUPINGCODE_ID NUMBER NULL);

DROP INDEX XAK1CUSTOMERREPLACEMENTPRICE;

CREATE UNIQUE INDEX XAK1CUSTOMERREPLACEMENTPRICE ON CUSTOMERREPLACEMENTPRICE (CUSTOMER_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, STARTDATE ASC, SIZEGROUPINGCODE_ID ASC);

DROP INDEX TIDXCUSTOMERREPLACEMENTPRICE;

CREATE INDEX TIDXCUSTOMERREPLACEMENTPRICE ON CUSTOMERREPLACEMENTPRICE (PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, SIZEGROUPINGCODE_ID ASC);

CREATE INDEX XIF1CUSTOMERREPLACEMENTPRICE ON CUSTOMERREPLACEMENTPRICE (SIZEGROUPINGCODE_ID ASC);

ALTER TABLE CUSTOMERREPLACEMENTPRICE ADD CONSTRAINT CUSTOMERREPLPRICE_SZGROUPCODE FOREIGN KEY (SIZEGROUPINGCODE_ID)
REFERENCES SIZEGROUPINGCODE (SIZEGROUPINGCODE_ID);

------------   CUSTOMERSALESPRICE --------------

ALTER TABLE CUSTOMERSALESPRICE ADD (SIZEGROUPINGCODE_ID NUMBER NULL);

DROP INDEX XAK1CUSTOMERSALESPRICE;

CREATE UNIQUE INDEX XAK1CUSTOMERSALESPRICE ON CUSTOMERSALESPRICE (CUSTOMER_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, STARTDATE ASC, VOLUMEPRICE ASC, SIZEGROUPINGCODE_ID ASC);

CREATE INDEX XIF1CUSTOMERSALESPRICE ON CUSTOMERSALESPRICE (SIZEGROUPINGCODE_ID ASC);

ALTER TABLE CUSTOMERSALESPRICE ADD CONSTRAINT CUSTOMERSALESPRICE_SZGROUPCODE FOREIGN KEY (SIZEGROUPINGCODE_ID)
REFERENCES SIZEGROUPINGCODE (SIZEGROUPINGCODE_ID);


------------   CUSTOMER --------------

ALTER TABLE CUSTOMER ADD (USEPRODSIZESURCHARGEPRICING VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD387 CHECK (USEPRODSIZESURCHARGEPRICING IN ('Y','N')));

------------   GENERALRENTPRICE --------------

ALTER TABLE GENERALRENTPRICE ADD (SIZEGROUPINGCODE_ID NUMBER NULL);

DROP INDEX XAK1GENERALRENTPRICE;

CREATE UNIQUE INDEX XAK1GENERALRENTPRICE ON GENERALRENTPRICE (GENERALPRICELIST_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, FINISHINGMETHOD_ID ASC, TYPEOFRENT ASC, STARTDATE ASC, CHANGESPERWEEK ASC, INVOICEFREQUENCYNUMBER ASC, SIZEGROUPINGCODE_ID ASC);

CREATE INDEX XIF1GENERALRENTPRICE ON GENERALRENTPRICE (SIZEGROUPINGCODE_ID ASC);

ALTER TABLE GENERALRENTPRICE ADD CONSTRAINT GENERALRENTPRICE_SZGROUPCODE FOREIGN KEY (SIZEGROUPINGCODE_ID)
REFERENCES SIZEGROUPINGCODE (SIZEGROUPINGCODE_ID);

------------   GENERALREPLACEMENTPRICE --------------

ALTER TABLE GENERALREPLACEMENTPRICE ADD (SIZEGROUPINGCODE_ID NUMBER NULL);

DROP INDEX XAK1GENERALREPLACEMENTPRICE;

CREATE UNIQUE INDEX XAK1GENERALREPLACEMENTPRICE ON GENERALREPLACEMENTPRICE (GENERALPRICELIST_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, STARTDATE ASC, SIZEGROUPINGCODE_ID ASC);

CREATE INDEX XIF1GENERALREPLACEMENTPRICE ON GENERALREPLACEMENTPRICE (SIZEGROUPINGCODE_ID ASC);

ALTER TABLE GENERALREPLACEMENTPRICE ADD CONSTRAINT GENERALREPLPRICE_SZGROUPCODE FOREIGN KEY (SIZEGROUPINGCODE_ID)
REFERENCES SIZEGROUPINGCODE (SIZEGROUPINGCODE_ID);

------------   GENERALSALESPRICE --------------

ALTER TABLE GENERALSALESPRICE ADD (SIZEGROUPINGCODE_ID NUMBER NULL);

DROP INDEX XAK1GENERALSALESPRICE;

CREATE UNIQUE INDEX XAK1GENERALSALESPRICE ON GENERALSALESPRICE (GENERALPRICELIST_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, STARTDATE ASC, VOLUMEPRICE ASC, SIZEGROUPINGCODE_ID ASC);

CREATE INDEX XIF1GENERALSALESPRICE ON GENERALSALESPRICE (SIZEGROUPINGCODE_ID ASC);

ALTER TABLE GENERALSALESPRICE ADD CONSTRAINT GENERALSALESPRICE_SZGROUPCODE FOREIGN KEY (SIZEGROUPINGCODE_ID)
REFERENCES SIZEGROUPINGCODE (SIZEGROUPINGCODE_ID);

------------   PRODUCTSIZE --------------

ALTER TABLE PRODUCTSIZE ADD (SIZEGROUPINGCODE_ID NUMBER NULL);

CREATE INDEX XIF1PRODUCTSIZE ON PRODUCTSIZE (SIZEGROUPINGCODE_ID ASC);

ALTER TABLE PRODUCTSIZE ADD CONSTRAINT PRODUCTSIZE_SZGROUPCODE FOREIGN KEY (SIZEGROUPINGCODE_ID)
REFERENCES SIZEGROUPINGCODE (SIZEGROUPINGCODE_ID);


------------   SIZEGROUPINGCODE --------------

ALTER TABLE SIZEGROUPINGCODE ADD (STANDARD VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD388 CHECK (STANDARD IN ('Y','N')));


------------   SIZEGROUPPERPRODUCTGROUP --------------
CREATE SEQUENCE SEQ_SIZEGROUPPERPRODUCTGROUP
START WITH 1
INCREMENT BY 1;

CREATE TABLE SIZEGROUPPERPRODUCTGROUP
(
SIZEGROUPPERPRODUCTGROUP_ID NUMBER NOT NULL,
SIZEGROUPINGCODE_ID NUMBER NOT NULL,
PRODUCTGROUP_ID NUMBER NOT NULL,
SURCHARGEPERCENTAGE NUMBER(4,2) DEFAULT 0 NOT NULL CONSTRAINT SURCHARGEPERCENTAGE_VALID1_1 CHECK (SURCHARGEPERCENTAGE BETWEEN 0 AND 99.99),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE SIZEGROUPPERPRODUCTGROUP ADD CONSTRAINT XPKSIZEGROUPPERPRODUCTGROUP PRIMARY KEY (SIZEGROUPPERPRODUCTGROUP_ID);

CREATE UNIQUE INDEX XAK1SIZEGROUPPERPRODUCTGROUP ON SIZEGROUPPERPRODUCTGROUP (SIZEGROUPINGCODE_ID ASC, PRODUCTGROUP_ID ASC);

CREATE INDEX XIF1SIZEGROUPPERPRODUCTGROUP ON SIZEGROUPPERPRODUCTGROUP (SIZEGROUPINGCODE_ID ASC);
CREATE INDEX XIF2SIZEGROUPPERPRODUCTGROUP ON SIZEGROUPPERPRODUCTGROUP (PRODUCTGROUP_ID ASC);


ALTER TABLE SIZEGROUPPERPRODUCTGROUP ADD CONSTRAINT SIZEGROUPPRODGROUP_SZGRCD FOREIGN KEY (SIZEGROUPINGCODE_ID)
REFERENCES SIZEGROUPINGCODE (SIZEGROUPINGCODE_ID);

ALTER TABLE SIZEGROUPPERPRODUCTGROUP ADD CONSTRAINT SIZEGROUPPRODGROUP_PRODGR FOREIGN KEY (PRODUCTGROUP_ID)
REFERENCES PRODUCTGROUP (PRODUCTGROUP_ID);












