--ABS-124631
CREATE TABLE ERP_I_COLOR
(
  TRANSFER_ID               NUMBER NOT NULL,
  TRANSFER_STATUS           NUMBER DEFAULT 0 NOT NULL,
  COLORCODE                 VARCHAR2(12) NOT NULL,
  RALCODE                   VARCHAR2(4),
  DESCRIPTION               VARCHAR2(50) NOT NULL,
  USEDFOREMBLEM             VARCHAR2(1) DEFAULT 'Y' NOT NULL,
  PANTONECODE               VARCHAR2(8),
  EMBLEMBACKGROUND          VARCHAR2(1) DEFAULT 'Y' NOT NULL,
  EMBLEMTEXT                VARCHAR2(1) DEFAULT 'Y' NOT NULL,
  EMBLEMBORDER              VARCHAR2(1) DEFAULT 'Y' NOT NULL,
  USEDFOREMBROIDERY         VARCHAR2(1) DEFAULT 'Y' NOT NULL,
  COUNTRYCODE               VARCHAR2(3),
  ONLYINBUSINESSUNIT        VARCHAR2(1) DEFAULT 'N' NOT NULL,
  USEFORRESIDENTIALSERVICES NUMBER DEFAULT 1 NOT NULL,
  USEDFOREMBLEMDEFINITION   NUMBER DEFAULT 0 NOT NULL,
  USEDFOREMBLEMTYPECOMPANY  VARCHAR2(1) DEFAULT 'N' NOT NULL,
  USEDFOREMBLEMTYPENAME     VARCHAR2(1) DEFAULT 'N' NOT NULL,
  USEDFOREMBLEMTYPEOTHER    VARCHAR2(1) DEFAULT 'N' NOT NULL,
  USEDFOREMBLEMTYPETITLE    VARCHAR2(1) DEFAULT 'N' NOT NULL,
  CUSTOMCOLOR               VARCHAR2(1) DEFAULT 'N' NOT NULL,
  TIMESTAMP                 DATE DEFAULT SYSDATE NOT NULL
);
ALTER TABLE ERP_I_COLOR
  ADD CONSTRAINT CHK_ERP_I_COLOR_EMBROID
  CHECK (USEDFOREMBROIDERY IN ('Y','N'));
ALTER TABLE ERP_I_COLOR
  ADD CONSTRAINT CHK_ERP_I_COLOR_ONLYINBUS
  CHECK (ONLYINBUSINESSUNIT IN ('Y','N'));
ALTER TABLE ERP_I_COLOR
  ADD CONSTRAINT CHK_ERP_I_COLOR_USEFOREM
  CHECK (USEDFOREMBLEM IN ('Y', 'N'));
ALTER TABLE ERP_I_COLOR
  ADD CONSTRAINT CHK_ERP_I_COLOR_USEFORRES
  CHECK (USEFORRESIDENTIALSERVICES IN (0,1));
ALTER TABLE ERP_I_COLOR
  ADD CONSTRAINT CHK_ERP_I_COLOR_TRANSFER
  CHECK (TRANSFER_STATUS IN (-1,0,1));
ALTER TABLE ERP_I_COLOR
  ADD CONSTRAINT CHK_ERP_I_COLOR_EMBACK
  CHECK (EMBLEMBACKGROUND IN ('Y','N'));
ALTER TABLE ERP_I_COLOR
  ADD CONSTRAINT CHK_ERP_I_COLOR_EMBLEMBORDER
  CHECK (EMBLEMBORDER IN ('Y','N'));
ALTER TABLE ERP_I_COLOR
  ADD CONSTRAINT CHK_ERP_I_COLOR_EMBLEMTEXT
  CHECK (EMBLEMTEXT IN ('Y','N'));
ALTER TABLE ERP_I_COLOR
  ADD CONSTRAINT CHK_ERP_I_COLOR_USEDFOREMBDEF
  CHECK (USEDFOREMBLEMDEFINITION IN (0,1,2,3));
ALTER TABLE ERP_I_COLOR
  ADD CONSTRAINT CHK_ERP_I_COLOR_USEDFOREMBTPCOMP
  CHECK (USEDFOREMBLEMTYPECOMPANY IN ('Y','N'));
ALTER TABLE ERP_I_COLOR
  ADD CONSTRAINT CHK_ERP_I_COLOR_USEDFOREMBTPNAME
  CHECK (USEDFOREMBLEMTYPENAME IN ('Y','N'));
ALTER TABLE ERP_I_COLOR
  ADD CONSTRAINT CHK_ERP_I_COLOR_USEDFOREMBTPOTHER
  CHECK (USEDFOREMBLEMTYPEOTHER IN ('Y','N'));
ALTER TABLE ERP_I_COLOR
  ADD CONSTRAINT CHK_ERP_I_COLOR_USEDFOREMBTPTITLE
  CHECK (USEDFOREMBLEMTYPETITLE IN ('Y','N'));
ALTER TABLE ERP_I_COLOR
  ADD CONSTRAINT CHK_ERP_I_CUSTOMCOLOR
  CHECK (CUSTOMCOLOR IN ('Y','N'));
COMMENT ON COLUMN ERP_I_COLOR.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_COLOR.RALCODE
  IS '4 digits   RAL= Standard Color Coding';
COMMENT ON COLUMN ERP_I_COLOR.USEFORRESIDENTIALSERVICES
  IS '0 = Not used for Residential Services 1 = Used for Residential Services (Product.Color)';
COMMENT ON COLUMN ERP_I_COLOR.USEDFOREMBLEMDEFINITION
  IS '0 = Not applicable 1 = Predefined emblem products 2 = User defined emblems 3 = Both';
ALTER TABLE ERP_I_COLOR
 ADD CONSTRAINT PK_ERP_I_COLOR PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIE1_ERP_I_COLOR ON ERP_I_COLOR(TRANSFER_STATUS); 
CREATE INDEX XIE2_ERP_I_COLOR ON ERP_I_COLOR(COLORCODE);

CREATE TABLE ERP_I_COLOR_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_COLOR_LOG_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_COLOR_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_COLOR_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_COLOR_LOG
  ADD CONSTRAINT PK_ERP_I_COLOR_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_COLOR_LOG
  ADD CONSTRAINT FK_ERP_I_COLOR_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_COLOR(TRANSFER_ID) ON DELETE CASCADE;

CREATE TABLE ERP_I_FABRIC
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_FABRIC_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  FABRICCODE      VARCHAR2(12) NOT NULL,
  USEDFOREMBLEM   VARCHAR2(1) NOT NULL,
  NAMECODE        VARCHAR2(10),
  COMPANYCODE     VARCHAR2(10),
  DISPLAYBGCOLOR  VARCHAR2(6) DEFAULT 'ffffff' NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_FABRIC.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_FABRIC.DISPLAYBGCOLOR
  IS 'Colour used for distinguishing textiles based on their fabric for sorting purposes';
ALTER TABLE ERP_I_FABRIC
  ADD CONSTRAINT CHK_ERP_I_FABRIC_USEDFOREMBLEM
  CHECK (USEDFOREMBLEM IN ('Y', 'N'));  
ALTER TABLE ERP_I_FABRIC
 ADD CONSTRAINT PK_ERP_I_FABRIC PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIE1_ERP_I_FABRIC ON ERP_I_FABRIC(TRANSFER_STATUS); 
CREATE INDEX XIE2_ERP_I_FABRIC ON ERP_I_FABRIC(FABRICCODE);

CREATE TABLE ERP_I_FABRIC_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_I_FABRICCHRG_LOG_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_FABRIC_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_FABRIC_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_FABRIC_LOG
  ADD CONSTRAINT PK_ERP_I_FABRIC_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_FABRIC_LOG
  ADD CONSTRAINT FK_ERP_I_FABRIC_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_FABRIC(TRANSFER_ID) ON DELETE CASCADE;
