--ABSN-7980

--CONTACTCATEGORY TABLE
ALTER TABLE CONTACTCATEGORY ADD (CREDITHOLD NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_CONTACTCATEGORY_CREDITHOLD CHECK (CREDITHOLD IN (0,1,2)));
                                 
COMMENT ON COLUMN CONTACTCATEGORY.CREDITHOLD IS '0 = No 1 = Pre Credit Hold (so warning status) 2 = Credit Hold';

--CONTACTCATEGORYRATEXT_DESC
CREATE TABLE CONTACTCATEGORYRATEXT_DESC (
CONTACTCATEGORY_ID NUMBER NOT NULL,
LANGUAGE_ID        NUMBER NOT NULL,
DESCRIPTION        VARCHAR2(30) NOT NULL,
SYSTEMUSER_ID      NUMBER NOT NULL,
TIMESTAMP          DATE NOT NULL);

COMMENT ON COLUMN CONTACTCATEGORYRATEXT_DESC.DESCRIPTION IS 'Full description of the TextShownOnRAIfOpen in the current language';

ALTER TABLE CONTACTCATEGORYRATEXT_DESC ADD CONSTRAINT XPKCONTACTCATEGORYRATEXT_DESC PRIMARY KEY (CONTACTCATEGORY_ID, LANGUAGE_ID);

CREATE INDEX XIF1CONTACTCATEGORYRATEXT_DESC ON CONTACTCATEGORYRATEXT_DESC (CONTACTCATEGORY_ID ASC);
CREATE INDEX XIF2CONTACTCATEGORYRATEXT_DESC ON CONTACTCATEGORYRATEXT_DESC (LANGUAGE_ID ASC);

ALTER TABLE CONTACTCATEGORYRATEXT_DESC ADD CONSTRAINT FK_CNTCTCTGRYRATXT_DESC_CNTCTCTGRY FOREIGN KEY (CONTACTCATEGORY_ID) REFERENCES CONTACTCATEGORY (CONTACTCATEGORY_ID) ON DELETE CASCADE;
ALTER TABLE CONTACTCATEGORYRATEXT_DESC ADD CONSTRAINT FK_CNTCTCTGRYRATXT_DESC_LANGUAGE_ID FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID);

--ROUTECONTROLEXTRAINFO TABLE
ALTER TABLE ROUTECONTROLEXTRAINFO ADD (CUSTOMERHOLDREVIEW VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_ROUTECONTROLEXTRAINFO_CUSTOMERHOLDREVIEW CHECK (CUSTOMERHOLDREVIEW IN ('Y','N')));

COMMENT ON COLUMN ROUTECONTROLEXTRAINFO.CUSTOMERHOLDREVIEW IS 'Used for module AUSCUSTOM Set to Y when the Customer Holds tab is reviewed in WebX route check in';

--GARMENTSOILSCANHIST TABLE
CREATE TABLE GARMENTSOILSCANHIST (
INVOICEDATE       DATE NOT NULL,
ROUTE_ID          NUMBER NOT NULL,
CUSTOMER_LINK_ID  NUMBER NOT NULL,
RASOILSCANCOUNT   NUMBER NOT NULL,
SUMOFMAXINVENTORY NUMBER NOT NULL,
INVOICEHEADER_ID  NUMBER NOT NULL,
TIMESTAMP         DATE NOT NULL,
SYSTEMUSER_ID     NUMBER NOT NULL);

ALTER TABLE GARMENTSOILSCANHIST ADD CONSTRAINT XPKGARMENTSOILSCANHIST PRIMARY KEY (INVOICEDATE, ROUTE_ID, CUSTOMER_LINK_ID);

CREATE INDEX XIF1GARMENTSOILSCANHIST ON GARMENTSOILSCANHIST (ROUTE_ID ASC);
CREATE INDEX XIF2GARMENTSOILSCANHIST ON GARMENTSOILSCANHIST (CUSTOMER_LINK_ID ASC);
CREATE INDEX XIF3GARMENTSOILSCANHIST ON GARMENTSOILSCANHIST (INVOICEHEADER_ID ASC);

ALTER TABLE GARMENTSOILSCANHIST ADD CONSTRAINT FK_GARMENTSOILSCNHIST_ROUTE_ID FOREIGN KEY (ROUTE_ID) REFERENCES ROUTE (ROUTE_ID) ON DELETE CASCADE;
ALTER TABLE GARMENTSOILSCANHIST ADD CONSTRAINT FK_GARMENTSOILSCANHIST_CUSTOMER_LINK_ID FOREIGN KEY (CUSTOMER_LINK_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;
ALTER TABLE GARMENTSOILSCANHIST ADD CONSTRAINT FK_GARMENTSOILSCNHIST_INVOICEHEADR_ID FOREIGN KEY (INVOICEHEADER_ID) REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE CASCADE;
