-- ABS-32305
-------------   SIZEBILLINGGROUP   --------------
CREATE SEQUENCE SEQ_SIZEBILLINGGROUP
START WITH 1
INCREMENT BY 1;

CREATE TABLE SIZEBILLINGGROUP
(
SIZEBILLINGGROUP_ID NUMBER NOT NULL,
CODE VARCHAR2(12) NOT NULL,
DESCRIPTION VARCHAR2(30) NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL,
STANDARD VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD389 CHECK (STANDARD IN ('Y','N'))
);

ALTER TABLE SIZEBILLINGGROUP ADD CONSTRAINT XPKSIZEBILLINGGROUP PRIMARY KEY (SIZEBILLINGGROUP_ID);

CREATE UNIQUE INDEX XAK1SIZEBILLINGGROUP ON SIZEBILLINGGROUP (CODE ASC);

-------------   CUSTOMERREPLACEMENTPRICE   --------------
DROP INDEX XAK1CUSTOMERREPLACEMENTPRICE;
DROP INDEX TIDXCUSTOMERREPLACEMENTPRICE;

ALTER TABLE CUSTOMERREPLACEMENTPRICE DROP COLUMN SIZEGROUPINGCODE_ID;

ALTER TABLE CUSTOMERREPLACEMENTPRICE ADD (SIZEBILLINGGROUP_ID NUMBER NULL);

COMMENT ON COLUMN CUSTOMERREPLACEMENTPRICE.SIZEBILLINGGROUP_ID IS 'Only used when module CHARGESPECIALSIZE is in the license';

CREATE UNIQUE INDEX XAK1CUSTOMERREPLACEMENTPRICE ON CUSTOMERREPLACEMENTPRICE (CUSTOMER_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, STARTDATE ASC, SIZEBILLINGGROUP_ID ASC);
CREATE INDEX XIE1CUSTOMERREPLACEMENTPRICE ON CUSTOMERREPLACEMENTPRICE (PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, SIZEBILLINGGROUP_ID ASC);
CREATE INDEX XIF2CUSTOMERREPLACEMENTPRICE ON CUSTOMERREPLACEMENTPRICE (SIZEBILLINGGROUP_ID ASC);

ALTER TABLE CUSTOMERREPLACEMENTPRICE ADD CONSTRAINT CUSTREPLACEPRICE_SIZEBIL FOREIGN KEY (SIZEBILLINGGROUP_ID)
REFERENCES SIZEBILLINGGROUP (SIZEBILLINGGROUP_ID);

-------------   CUSTOMERSALESPRICE   --------------
DROP INDEX XAK1CUSTOMERSALESPRICE;
DROP INDEX TIDXCUSTOMERSALESPRICE;

ALTER TABLE CUSTOMERSALESPRICE DROP COLUMN SIZEGROUPINGCODE_ID;

ALTER TABLE CUSTOMERSALESPRICE ADD (SIZEBILLINGGROUP_ID NUMBER NULL);

COMMENT ON COLUMN CUSTOMERSALESPRICE.SIZEBILLINGGROUP_ID IS 'Only used when module CHARGESPECIALSIZE is in the license';

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

CREATE INDEX XIF2CUSTOMERSALESPRICE ON CUSTOMERSALESPRICE (SIZEBILLINGGROUP_ID ASC);

ALTER TABLE CUSTOMERSALESPRICE ADD CONSTRAINT CUSTOMERSALESPRICE_SIZEBIL FOREIGN KEY (SIZEBILLINGGROUP_ID)
REFERENCES SIZEBILLINGGROUP (SIZEBILLINGGROUP_ID);

-------------   GENERALRENTPRICE   --------------
DROP INDEX XAK1GENERALRENTPRICE;

ALTER TABLE GENERALRENTPRICE DROP COLUMN SIZEGROUPINGCODE_ID;

ALTER TABLE GENERALRENTPRICE ADD (SIZEBILLINGGROUP_ID NUMBER NULL);

COMMENT ON COLUMN GENERALRENTPRICE.SIZEBILLINGGROUP_ID IS 'Only used when module CHARGESPECIALSIZE is in the license';

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, SIZEBILLINGGROUP_ID ASC);

CREATE INDEX XIF2GENERALRENTPRICE ON  GENERALRENTPRICE (SIZEBILLINGGROUP_ID ASC);

ALTER TABLE GENERALRENTPRICE ADD CONSTRAINT GENERALRENTPRICE_SIZEBIL FOREIGN KEY (SIZEBILLINGGROUP_ID)
REFERENCES SIZEBILLINGGROUP (SIZEBILLINGGROUP_ID);

-------------   GENERALREPLACEMENTPRICE   --------------
DROP INDEX XAK1GENERALREPLACEMENTPRICE;

ALTER TABLE GENERALREPLACEMENTPRICE DROP COLUMN SIZEGROUPINGCODE_ID;

ALTER TABLE GENERALREPLACEMENTPRICE ADD (SIZEBILLINGGROUP_ID NUMBER NULL);

COMMENT ON COLUMN GENERALREPLACEMENTPRICE.SIZEBILLINGGROUP_ID IS 'Only used when module CHARGESPECIALSIZE is in the license';

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

CREATE INDEX XIF2GENERALREPLACEMENTPRICE ON GENERALREPLACEMENTPRICE (SIZEBILLINGGROUP_ID ASC);

ALTER TABLE GENERALREPLACEMENTPRICE ADD CONSTRAINT GENERALREPLACEMENTPRICE_SIZEB FOREIGN KEY (SIZEBILLINGGROUP_ID)
REFERENCES SIZEBILLINGGROUP (SIZEBILLINGGROUP_ID);

-------------   GENERALSALESPRICE   --------------
DROP INDEX XAK1GENERALSALESPRICE;

ALTER TABLE GENERALSALESPRICE DROP COLUMN SIZEGROUPINGCODE_ID;

ALTER TABLE GENERALSALESPRICE ADD (SIZEBILLINGGROUP_ID NUMBER NULL);

COMMENT ON COLUMN GENERALSALESPRICE.SIZEBILLINGGROUP_ID IS 'Only used when module CHARGESPECIALSIZE is in the license';

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

CREATE INDEX XIF2GENERALSALESPRICE ON GENERALSALESPRICE (SIZEBILLINGGROUP_ID ASC);

ALTER TABLE GENERALSALESPRICE ADD CONSTRAINT GENERALSALESPRICE_SIZEBIL FOREIGN KEY (SIZEBILLINGGROUP_ID)
REFERENCES SIZEBILLINGGROUP (SIZEBILLINGGROUP_ID);

-------------   CUSTOMERRENTPRICE   --------------
DROP INDEX XAK1CUSTOMERRENTPRICE;

ALTER TABLE CUSTOMERRENTPRICE DROP COLUMN SIZEGROUPINGCODE_ID;

ALTER TABLE CUSTOMERRENTPRICE ADD (SIZEBILLINGGROUP_ID NUMBER NULL);

COMMENT ON COLUMN CUSTOMERRENTPRICE.SIZEBILLINGGROUP_ID IS 'Only used when module CHARGESPECIALSIZE is in the license';

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

CREATE INDEX XIF2CUSTOMERRENTPRICE ON CUSTOMERRENTPRICE (SIZEBILLINGGROUP_ID ASC);

ALTER TABLE CUSTOMERRENTPRICE ADD CONSTRAINT CUSTOMERRENTPRICE_SIZEBIL FOREIGN KEY (SIZEBILLINGGROUP_ID)
REFERENCES SIZEBILLINGGROUP (SIZEBILLINGGROUP_ID);

-------------   PRODUCTSIZE   --------------
ALTER TABLE PRODUCTSIZE DROP CONSTRAINT XPKPRODUCTSIZE;

DROP INDEX XIE1PRODUCTSIZE;

ALTER TABLE PRODUCTSIZE ADD CONSTRAINT XPKPRODUCTSIZE PRIMARY KEY (PRODUCT_ID, SIZEDEFINITION_ID);

ALTER TABLE PRODUCTSIZE DROP COLUMN SIZEGROUPINGCODE_ID;

ALTER TABLE PRODUCTSIZE ADD (SIZEBILLINGGROUP_ID NUMBER NULL);

COMMENT ON COLUMN PRODUCTSIZE.SIZEBILLINGGROUP_ID IS 'Only used when module CHARGESPECIALSIZE is in the license';

CREATE INDEX XIE1PRODUCTSIZE ON PRODUCTSIZE (PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, STANDARDLENGTH ASC, SIZEBILLINGGROUP_ID ASC);

CREATE INDEX XIF2PRODUCTSIZE ON PRODUCTSIZE (SIZEBILLINGGROUP_ID ASC);

ALTER TABLE PRODUCTSIZE ADD CONSTRAINT PRODUCTSIZE_SIZEBIL FOREIGN KEY (SIZEBILLINGGROUP_ID)
REFERENCES SIZEBILLINGGROUP (SIZEBILLINGGROUP_ID);

-------------   SIZEBILLGROUPPERPRODGROUP   --------------
DROP TABLE SIZEGROUPPERPRODUCTGROUP;

DROP SEQUENCE SEQ_SIZEGROUPPERPRODUCTGROUP;

CREATE SEQUENCE SEQ_SIZEBILLGROUPPERPRODGROUP
START WITH 1
INCREMENT BY 1;

CREATE TABLE SIZEBILLGROUPPERPRODGROUP
(
SIZEBILLGROUPPERPRODGROUP_ID NUMBER NOT NULL,
SIZEBILLINGGROUP_ID NUMBER NOT NULL,
PRODUCTGROUP_ID NUMBER NOT NULL,
SURCHARGEPERCENTAGE NUMBER NOT NULL CONSTRAINT SURCHARGEPERCENTAGE_VALID1_1 CHECK (SURCHARGEPERCENTAGE BETWEEN 0 AND 99.99),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE SIZEBILLGROUPPERPRODGROUP ADD CONSTRAINT XPKSIZEBILLGROUPPERPRODGROUP PRIMARY KEY (SIZEBILLGROUPPERPRODGROUP_ID);

CREATE UNIQUE INDEX XAK1SIZEBILLGROUPPERPRODGROUP ON SIZEBILLGROUPPERPRODGROUP (SIZEBILLINGGROUP_ID ASC, PRODUCTGROUP_ID ASC);

CREATE INDEX XIF1SIZEBILLGROUPPERPRODGROUP ON SIZEBILLGROUPPERPRODGROUP (SIZEBILLINGGROUP_ID ASC);
CREATE INDEX XIF2SIZEBILLGROUPPERPRODGROUP ON SIZEBILLGROUPPERPRODGROUP (PRODUCTGROUP_ID ASC);

ALTER TABLE SIZEBILLGROUPPERPRODGROUP ADD CONSTRAINT SIZEBILLGROUPPERPRODGROUP_SBG FOREIGN KEY (SIZEBILLINGGROUP_ID)
REFERENCES SIZEBILLINGGROUP (SIZEBILLINGGROUP_ID);

ALTER TABLE SIZEBILLGROUPPERPRODGROUP ADD CONSTRAINT SIZEBILLGROUPPERPRODGROUP_PG FOREIGN KEY (PRODUCTGROUP_ID)
REFERENCES PRODUCTGROUP (PRODUCTGROUP_ID);

-------------   SIZEDEFINITION  --------------
ALTER TABLE SIZEDEFINITION ADD (SIZEBILLINGGROUP_ID NUMBER NULL);

COMMENT ON COLUMN SIZEDEFINITION.SIZEBILLINGGROUP_ID IS 'Only used when module CHARGESPECIALSIZE is in the license';

CREATE INDEX XIF3SIZEDEFINITION ON SIZEDEFINITION (SIZEBILLINGGROUP_ID ASC);

ALTER TABLE SIZEDEFINITION ADD CONSTRAINT SIZEDEFINITION_SBGID FOREIGN KEY (SIZEBILLINGGROUP_ID)
REFERENCES SIZEBILLINGGROUP (SIZEBILLINGGROUP_ID);

-------------   SIZEGROUPINGCODE --------------
ALTER TABLE SIZEGROUPINGCODE DROP COLUMN STANDARD;
