-- ABS-62311 / ABS-74524
--- UNIQUEITEMMBYO ---
ALTER TABLE UNIQUEITEMMBYO DROP COLUMN MBYOCHARGEINVOICELINE_ID CASCADE CONSTRAINTS;

ALTER TABLE UNIQUEITEMMBYO ADD (CUSTOMER_ID NUMBER NOT NULL,
                               CHARGEAFTERDATE DATE NOT NULL,
                               INVOICELINE_ID NUMBER NULL);
                               
COMMENT ON COLUMN UNIQUEITEMMBYO.CHARGEAFTERDATE IS 'The date which the charge cannot be raised before';

CREATE INDEX XIF1UNIQUEITEMMBYO ON UNIQUEITEMMBYO (CUSTOMER_ID ASC);
CREATE INDEX XIF2UNIQUEITEMMBYO ON UNIQUEITEMMBYO (INVOICELINE_ID ASC);

ALTER TABLE UNIQUEITEMMBYO ADD CONSTRAINT UNIQUEITEMMBYO_CID FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMER (CUSTOMER_ID);

ALTER TABLE UNIQUEITEMMBYO ADD CONSTRAINT UNIQUEITEMMBYO_ILID FOREIGN KEY (INVOICELINE_ID)
REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE SET NULL;

--- NONUNIQUEITEMMBYO ---
ALTER TABLE NONUNIQUEITEMMBYO DROP COLUMN MBYOCHARGEINVOICELINE_ID CASCADE CONSTRAINT;

ALTER TABLE NONUNIQUEITEMMBYO ADD (CUSTOMER_ID NUMBER NOT NULL,
                               CHARGEAFTERDATE DATE NOT NULL,
                               INVOICELINE_ID NUMBER NULL);
                               
COMMENT ON COLUMN NONUNIQUEITEMMBYO.CHARGEAFTERDATE IS 'The date which the charge cannot be raised before';

CREATE INDEX XIF1NONUNIQUEITEMMBYO ON NONUNIQUEITEMMBYO (CUSTOMER_ID ASC);
CREATE INDEX XIF2NONUNIQUEITEMMBYO ON NONUNIQUEITEMMBYO (INVOICELINE_ID ASC);

ALTER TABLE NONUNIQUEITEMMBYO ADD CONSTRAINT NONUNIQUEITEMMBYO_CID FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMER (CUSTOMER_ID);

ALTER TABLE NONUNIQUEITEMMBYO ADD CONSTRAINT NONUNIQUEITEMMBYO_ILID FOREIGN KEY (INVOICELINE_ID)
REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE SET NULL;

