--ABSN-6270

--CONTAINER TABLE
ALTER TABLE CONTAINER ADD(LASTRETAREDATE  DATE,
                          NUMBEROFRETARES NUMBER DEFAULT 0 NOT NULL,
                          PREVTAREWEIGHT  NUMBER);
      
COMMENT ON COLUMN CONTAINER.LASTRETAREDATE IS 'Set to date/time when tare weight was newly determined last time';
COMMENT ON COLUMN CONTAINER.NUMBEROFRETARES IS 'The number of times the tare weight has been determined new again';
COMMENT ON COLUMN CONTAINER.PREVTAREWEIGHT IS 'Previous tare weight';


--CONTAINERREPAIRTYPE TABLE
CREATE SEQUENCE SEQ_CONTAINERREPAIRTYPE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTAINERREPAIRTYPE(
CONTAINERREPAIRTYPE_ID NUMBER NOT NULL,
CODE                   VARCHAR2(12) NOT NULL,
REPAIRTYPEGROUP_ID     NUMBER NOT NULL,
DEFAULTTIME            NUMBER DEFAULT 1 NOT NULL,
TIMEINCREMENT          NUMBER DEFAULT 1 NOT NULL,
AUTOADDONREPAIRSCAN    VARCHAR2(1) DEFAULT 'N' CONSTRAINT CHK_CONTAINERREPAIRTYPE_AUTOADDONREPAIRSCAN CHECK (AUTOADDONREPAIRSCAN IN ('Y','N')),
SELECTABLEFOR          NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_CONTAINERREPAIRTYPE_SELECTABLEFOR CHECK (SELECTABLEFOR IN (0,1,2)),
ACTIVE                 VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_CONTAINERREPAIRTYPE_ACTIVE CHECK (ACTIVE IN ('Y','N')),
SYSTEMUSER_ID          NUMBER NOT NULL,
TIMESTAMP              DATE NOT NULL,
CREATIONSYSTEMUSER_ID  NUMBER NOT NULL,
CREATIONTIMESTAMP      DATE DEFAULT SYSDATE NOT NULL
);
       
COMMENT ON COLUMN CONTAINERREPAIRTYPE.DEFAULTTIME IS 'Default (norm) repair time in minutes for this kind of repair';
COMMENT ON COLUMN CONTAINERREPAIRTYPE.TIMEINCREMENT IS 'Total repair time fort this kind of repair will be measured in increments of n minutes';
COMMENT ON COLUMN CONTAINERREPAIRTYPE.SELECTABLEFOR IS 'Possible values: 0 = LOG and COG (default) 1 = LOG only 2= COG only';

ALTER TABLE CONTAINERREPAIRTYPE ADD CONSTRAINT XPKCONTAINERREPAIRTYPE PRIMARY KEY (CONTAINERREPAIRTYPE_ID);

CREATE UNIQUE INDEX XAK1CONTAINERREPAIRTYPE ON CONTAINERREPAIRTYPE (CODE);

CREATE INDEX XIF1CONTAINERREPAIRTYPE ON CONTAINERREPAIRTYPE (REPAIRTYPEGROUP_ID ASC);

ALTER TABLE CONTAINERREPAIRTYPE ADD CONSTRAINT FK_CONTAINERREPAIRTYPE_REPAIRTYPEGROUP_ID FOREIGN KEY (REPAIRTYPEGROUP_ID) REFERENCES REPAIRTYPEGROUP (REPAIRTYPEGROUP_ID);

--CONTAINERREPAIRTYPE_DESC TABLE
CREATE TABLE CONTAINERREPAIRTYPE_DESC(
CONTAINERREPAIRTYPE_ID NUMBER NOT NULL,
LANGUAGE_ID            NUMBER NOT NULL,
DESCRIPTION            VARCHAR2(30) NOT NULL,
SYSTEMUSER_ID          NUMBER NOT NULL,
TIMESTAMP              DATE NOT NULL
);
                                      
ALTER TABLE CONTAINERREPAIRTYPE_DESC ADD CONSTRAINT XPKCONTAINERREPAIRTYPE_DESC PRIMARY KEY (CONTAINERREPAIRTYPE_ID, LANGUAGE_ID);

CREATE INDEX XIF1CONTAINERREPAIRTYPE_DESC ON CONTAINERREPAIRTYPE_DESC (CONTAINERREPAIRTYPE_ID ASC);
CREATE INDEX XIF2CONTAINERREPAIRTYPE_DESC ON CONTAINERREPAIRTYPE_DESC (LANGUAGE_ID ASC);

ALTER TABLE CONTAINERREPAIRTYPE_DESC ADD CONSTRAINT FK_CONTAINERREPAIRTYPE_DESC_CONTAINERREPAIRTYPE_ID FOREIGN KEY (CONTAINERREPAIRTYPE_ID) REFERENCES CONTAINERREPAIRTYPE (CONTAINERREPAIRTYPE_ID) ON DELETE CASCADE;
ALTER TABLE CONTAINERREPAIRTYPE_DESC ADD CONSTRAINT FK_CONTAINERREPAIRTYPE_DESC_LANGUAGE_ID FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID);

--CONTAINERTYPE TABLE
ALTER TABLE CONTAINERTYPE ADD (SCHEDULEDRETAREDAYS      NUMBER DEFAULT 0 NOT NULL,
                               SCHEDULEDRETARECLEANINGS NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN CONTAINERTYPE.SCHEDULEDRETAREDAYS IS 'The number of (scheduled) days after which the tare weight for containers of this type has to be determined again. A value of 0 means that no re-taring is needed.';
COMMENT ON COLUMN CONTAINERTYPE.SCHEDULEDRETARECLEANINGS IS 'The number of (scheduled) cleanings after which the tare weight for containers of this type has to be determined again. A value of 0 means that no re-taring is needed.';

--CONTAINERFLAG TABLE
CREATE TABLE CONTAINERFLAG (
CONTAINER_ID  NUMBER NOT NULL,
FLAG_ID       NUMBER NOT NULL,
FLAGSTARTDATE DATE NOT NULL,
FREETEXTITEM  VARCHAR2(80),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP     DATE NOT NULL
);
       
ALTER TABLE CONTAINERFLAG ADD CONSTRAINT XPK_CONTAINERFLAG PRIMARY KEY (CONTAINER_ID, FLAG_ID);

CREATE INDEX XIF1CONTAINERFLAG ON CONTAINERFLAG (CONTAINER_ID ASC);
CREATE INDEX XIF2CONTAINERFLAG ON CONTAINERFLAG (FLAG_ID ASC);

ALTER TABLE CONTAINERFLAG ADD CONSTRAINT FK_CONTAINERFLAG_CONTAINER_ID FOREIGN KEY (CONTAINER_ID) REFERENCES CONTAINER (CONTAINER_ID) ON DELETE CASCADE;
ALTER TABLE CONTAINERFLAG ADD CONSTRAINT FK_CONTAINERFLAG_FLAG_ID FOREIGN KEY (FLAG_ID) REFERENCES FLAG (FLAG_ID);


--CONTAINERREPAIRHISTORY TABLE
CREATE SEQUENCE SEQ_CONTAINERREPAIRHISTORY
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTAINERREPAIRHISTORY (
CONTAINERREPAIRHISTORY_ID  NUMBER NOT NULL,
BUSINESSUNIT_ID            NUMBER NOT NULL,
CONTAINERREPAIRTYPE_ID     NUMBER NOT NULL,
CONTAINER_ID               NUMBER NOT NULL,
REPAIRTIMESTAMP            DATE NOT NULL,
MINUTES                    NUMBER DEFAULT 1 NOT NULL CONSTRAINT CHK_CONTAINERREPAIRHISTORY_MINUTES CHECK (MINUTES BETWEEN 1 AND 999),
CALCULATEDMINUTES          NUMBER DEFAULT 1 NOT NULL,
QUANTITY                   NUMBER DEFAULT 1 NOT NULL CONSTRAINT CHK_CONTAINERREPAIRHISTORY_QUANTITY CHECK (QUANTITY BETWEEN 1 AND 999),
CUSTOMER_LINK_ID           NUMBER,
CUSTOMEROWNED              VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CONTAINERREPAIRHISTORY_CUSTOMEROWNED CHECK (CUSTOMEROWNED IN ('Y','N')),
DISTRIBUTION_POINT_LINK_ID NUMBER,
COSTCENTER_ID              NUMBER,
INVOICEBUSINESSUNIT_ID     NUMBER NOT NULL,
INVOICEHEADER_ID           NUMBER,
UNITPRICE                  NUMBER,
PRICEPERMINUTE             NUMBER,
TIMEINCREMENT              NUMBER DEFAULT 1 NOT NULL,
AMOUNT                     NUMBER DEFAULT 0 NOT NULL ,
STATUS                     NUMBER NOT NULL CONSTRAINT CHK_CONTAINERREPAIRHISTORY_STATUS CHECK (STATUS IN (0,1,2,3)),
SYSTEMUSER_ID              NUMBER NOT NULL,
TIMESTAMP                  DATE NOT NULL,
CREATIONUSER_ID            NUMBER NOT NULL,
CREATIONTIMESTAMP          DATE NOT NULL
);
       
COMMENT ON COLUMN CONTAINERREPAIRHISTORY.MINUTES IS 'Duration (number of minutes) spent on current repair type for the current container on current repair date.';
COMMENT ON COLUMN CONTAINERREPAIRHISTORY.CALCULATEDMINUTES IS 'Calculated duration (number of minutes) spent on current repair type on current container, considering the defined time increments for the repair type.';
COMMENT ON COLUMN CONTAINERREPAIRHISTORY.QUANTITY IS 'Number of repairs of current repair type for the current container on current repair date.';
COMMENT ON COLUMN CONTAINERREPAIRHISTORY.STATUS IS '0 = Charge 1 = Free of Charge 2 = Charge Cancelled 3 = No Repair Charge';
       
ALTER TABLE CONTAINERREPAIRHISTORY ADD CONSTRAINT XPKCONTAINERREPAIRHISTORY PRIMARY KEY (CONTAINERREPAIRHISTORY_ID);

CREATE INDEX XIE1CONTAINERREPAIRHISTORY ON CONTAINERREPAIRHISTORY (BUSINESSUNIT_ID ASC, CONTAINER_ID ASC, REPAIRTIMESTAMP ASC, CONTAINERREPAIRTYPE_ID ASC);

CREATE INDEX XIF1CONTAINERREPAIRHISTORY ON CONTAINERREPAIRHISTORY (BUSINESSUNIT_ID ASC);
CREATE INDEX XIF2CONTAINERREPAIRHISTORY ON CONTAINERREPAIRHISTORY (CONTAINERREPAIRTYPE_ID ASC);
CREATE INDEX XIF3CONTAINERREPAIRHISTORY ON CONTAINERREPAIRHISTORY (CONTAINER_ID ASC);
CREATE INDEX XIF4CONTAINERREPAIRHISTORY ON CONTAINERREPAIRHISTORY (CUSTOMER_LINK_ID ASC);
CREATE INDEX XIF5CONTAINERREPAIRHISTORY ON CONTAINERREPAIRHISTORY (DISTRIBUTION_POINT_LINK_ID ASC);
CREATE INDEX XIF6CONTAINERREPAIRHISTORY ON CONTAINERREPAIRHISTORY (COSTCENTER_ID ASC);
CREATE INDEX XIF7CONTAINERREPAIRHISTORY ON CONTAINERREPAIRHISTORY (INVOICEBUSINESSUNIT_ID ASC);
CREATE INDEX XIF8CONTAINERREPAIRHISTORY ON CONTAINERREPAIRHISTORY (INVOICEHEADER_ID ASC);

ALTER TABLE CONTAINERREPAIRHISTORY ADD CONSTRAINT FK_CONTAINERREPAIRHISTORY_BUSINESSUNIT_ID FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);
ALTER TABLE CONTAINERREPAIRHISTORY ADD CONSTRAINT FK_CONTAINERREPAIRHISTORY_CONTAINERREPAIRTYPE_ID FOREIGN KEY (CONTAINERREPAIRTYPE_ID) REFERENCES CONTAINERREPAIRTYPE (CONTAINERREPAIRTYPE_ID);
ALTER TABLE CONTAINERREPAIRHISTORY ADD CONSTRAINT FK_CONTAINERREPAIRHISTORY_CONTAINER_ID FOREIGN KEY (CONTAINER_ID) REFERENCES CONTAINER (CONTAINER_ID);
ALTER TABLE CONTAINERREPAIRHISTORY ADD CONSTRAINT FK_CONTAINERREPAIRHISTORY_CUSTOMER_LINK_ID FOREIGN KEY (CUSTOMER_LINK_ID) REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);
ALTER TABLE CONTAINERREPAIRHISTORY ADD CONSTRAINT FK_CONTAINERREPAIRHISTORY_DISTR_PT_LNK_ID FOREIGN KEY (DISTRIBUTION_POINT_LINK_ID) REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);
ALTER TABLE CONTAINERREPAIRHISTORY ADD CONSTRAINT FK_CONTAINERREPAIRHISTORY_COSTCENTER_ID FOREIGN KEY (COSTCENTER_ID) REFERENCES COSTCENTER (COSTCENTER_ID);
ALTER TABLE CONTAINERREPAIRHISTORY ADD CONSTRAINT FK_CONTAINERREPAIRHISTORY_INVOICEBUSINESSUNIT_ID FOREIGN KEY (INVOICEBUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);
ALTER TABLE CONTAINERREPAIRHISTORY ADD CONSTRAINT FK_CONTAINERREPAIRHISTORY_INVOICEHEADER_ID FOREIGN KEY (INVOICEHEADER_ID) REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE SET NULL;

--PRICELISTPERCUSTOMER TABLE 

COMMENT ON COLUMN PRICELISTPERCUSTOMER.PRICETYPE IS '1 = Wash 2 = Rent 3 = Replacement 4 = Sales 5 = Weight 6 = Repair 7 = Emblem 8 = Emblem Template 9 = Garment Insurance 10 = Special Charge 13 = Container Rent 14 = Container Delivery 15 = Container Return 16 = Service Event 17 = Container Repair';

ALTER TABLE PRICELISTPERCUSTOMER DROP CONSTRAINT CHK_PRICELISTPERCUSTOMER_PRICETYPE;
ALTER TABLE PRICELISTPERCUSTOMER ADD CONSTRAINT CHK_PRICELISTPERCUSTOMER_PRICETYPE CHECK (PRICETYPE IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17));

--GENERALPRICELIST TABLE

COMMENT ON COLUMN GENERALPRICELIST.PRICETYPE IS '1 = Wash 2 = Rent 3 = Replacement 4 = Sales 5 = Weight 6 = Repair 7 = Emblem 8 = Emblem Template 9 = Garment Insurance 10 = Special Charge 11 = Wearer Function Special Type 12 = Special Size Charge 13 = Container Rent 14 = Container Delivery 15 = Container Return 16 = Service Event 17 = Container Repair';

ALTER TABLE GENERALPRICELIST DROP CONSTRAINT CHK_GENERALPRICELIST_PRICETYPE;
ALTER TABLE GENERALPRICELIST ADD CONSTRAINT CHK_GENERALPRICELIST_PRICETYPE CHECK (PRICETYPE IN (1,2,3,4,5,6,7,8,9,10,11,12, 13, 14, 15,16,17));

--GENERALCONTAINERREPPRICE TABLE
CREATE SEQUENCE SEQ_GENERALCONTAINERREPPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE GENERALCONTAINERREPPRICE(
GENERALCONTAINERREPPRICE_ID NUMBER NOT NULL,
GENERALPRICELIST_ID         NUMBER NOT NULL,
CONTAINERREPAIRTYPE_ID      NUMBER NOT NULL,
STARTDATE                   DATE NOT NULL,
PRICE                       NUMBER,
PRICEPERMINUTE              NUMBER,
SYSTEMUSER_ID               NUMBER NOT NULL,
TIMESTAMP                   DATE NOT NULL,
CREATIONUSER_ID             NUMBER NOT NULL,
CREATIONTIMESTAMP           DATE NOT NULL
);
       
ALTER TABLE GENERALCONTAINERREPPRICE ADD CONSTRAINT XPKGENERALCONTAINERREPPRICE PRIMARY KEY (GENERALCONTAINERREPPRICE_ID);

CREATE UNIQUE INDEX XAK1GENERALCONTAINERREPPRICE ON GENERALCONTAINERREPPRICE (GENERALPRICELIST_ID, CONTAINERREPAIRTYPE_ID, STARTDATE);

CREATE INDEX XIF1GENERALCONTAINERREPPRICE ON GENERALCONTAINERREPPRICE (GENERALPRICELIST_ID ASC);
CREATE INDEX XIF2GENERALCONTAINERREPPRICE ON GENERALCONTAINERREPPRICE (CONTAINERREPAIRTYPE_ID ASC);

ALTER TABLE GENERALCONTAINERREPPRICE ADD CONSTRAINT FK_GENERALCONTAINERREPPRICE_GENERALPRICELIST_ID FOREIGN KEY (GENERALPRICELIST_ID) REFERENCES GENERALPRICELIST (GENERALPRICELIST_ID);
ALTER TABLE GENERALCONTAINERREPPRICE ADD CONSTRAINT FK_GENERALCONTAINERREPPRICE_CONTAINERREPAIRTYPE_ID FOREIGN KEY (CONTAINERREPAIRTYPE_ID) REFERENCES CONTAINERREPAIRTYPE (CONTAINERREPAIRTYPE_ID);

--CUSTOMERCONTAINERREPPRICE
CREATE SEQUENCE SEQ_CUSTOMERCONTAINERREPPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERCONTAINERREPPRICE(
CUSTOMERCONTAINERREPPRICE_ID NUMBER NOT NULL,
CUSTOMER_ID                  NUMBER NOT NULL,
CONTAINERREPAIRTYPE_ID       NUMBER NOT NULL,
STARTDATE                    DATE NOT NULL,
PRICE                        NUMBER,
PRICEPERMINUTE               NUMBER,
SYSTEMUSER_ID                NUMBER NOT NULL,
TIMESTAMP                    DATE NOT NULL,
CREATIONUSER_ID              NUMBER NOT NULL,
CREATIONTIMESTAMP            DATE NOT NULL
);

ALTER TABLE CUSTOMERCONTAINERREPPRICE ADD CONSTRAINT XPKCUSTOMERCONTAINERREPPRICE PRIMARY KEY (CUSTOMERCONTAINERREPPRICE_ID);

CREATE UNIQUE INDEX XAK1CUSTOMERCONTAINERREPPRICE ON CUSTOMERCONTAINERREPPRICE (CUSTOMER_ID, CONTAINERREPAIRTYPE_ID, STARTDATE);

CREATE INDEX XIF1CUSTOMERCONTAINERREPPRICE ON CUSTOMERCONTAINERREPPRICE (CUSTOMER_ID ASC);
CREATE INDEX XIF2CUSTOMERCONTAINERREPPRICE ON CUSTOMERCONTAINERREPPRICE (CONTAINERREPAIRTYPE_ID ASC);

ALTER TABLE CUSTOMERCONTAINERREPPRICE ADD CONSTRAINT FK_CUSTOMERCONTAINERREPPRICE_CUSTOMER_ID FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID);
ALTER TABLE CUSTOMERCONTAINERREPPRICE ADD CONSTRAINT FK_CUSTOMERCONTAINERREPPRICE_CSTCNTREPAIRTYPE_ID FOREIGN KEY (CONTAINERREPAIRTYPE_ID) REFERENCES CONTAINERREPAIRTYPE (CONTAINERREPAIRTYPE_ID);

--AUTOMATICFLAG TABLE

COMMENT ON COLUMN AUTOMATICFLAG.FLAGTYPE IS '1=Garment/wearer flags 2=OR-pack flags 3=Container flags 4=Residential/domestic, 5=Unique Item Service The flagtype corresponds with the FlagType from the Flag table. Type 2 is not (yet) used for automatic flags.';
