-- ABS-77215 / ABS-77265
------------ ERP_I_CONTRACTEMBTEMP ------------
CREATE TABLE ERP_I_CONTRACTEMBTEMP
(TRANSFER_ID                    NUMBER NOT NULL,
  TRANSFER_STATUS                NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID56 CHECK (TRANSFER_STATUS IN (0,1)),
  TEMPLATECODE                   VARCHAR2(6) NOT NULL,
  CONTRACTCODE                   VARCHAR2(15) NOT NULL,
  TEMPLATEDESCRIPTION            VARCHAR2(30) NULL,
  PRINTNAMELABEL                 VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT CHK_ERP_I_CONTEMBLTMP_PRNTMLBL CHECK (PRINTNAMELABEL IN ('Y', 'N')),
  RENTEMBLEM                     VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT CHK_ERP_I_CONTEMBLTMP_RNTEMBLM CHECK (RENTEMBLEM IN ('Y', 'N')),
  EMBROIDERY                     VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT CHK_ERP_I_CONTEMBLTMP_EMBRDRY CHECK (EMBROIDERY IN ('Y', 'N')),
  COMPLIANCYRULE                 NUMBER NULL CONSTRAINT CHK_ERP_I_CONTEMBLTMP_COMPLYRL CHECK (COMPLIANCYRULE IN (0, 1, 2)),
  CONTRACTEMBTEMPPRICE           NUMBER(15,6) NULL,
  CONTRACTEMBTEMPPRICESTARTDATE  DATE NULL,
  ACTIVE                         VARCHAR2(1) DEFAULT 'Y' NULL CONSTRAINT CHK_ERP_I_CONTEMBLTMP_ACTIVE CHECK (ACTIVE IN ('Y', 'N')));

COMMENT ON COLUMN ERP_I_CONTRACTEMBTEMP.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRACTEMBTEMP.COMPLIANCYRULE
  IS '0 = Contract suggested, can be overruled 
1 = Contract enforced, can only be overruled by maintenance user 
2 = Contract enforced';  

ALTER TABLE ERP_I_CONTRACTEMBTEMP
  ADD CONSTRAINT PK_ERP_I_CONTRACTEMBTEMP PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_CONTRACTEMBTEMP
  ADD CONSTRAINT PK2_ERP_I_CONTRACTEMBTEMP UNIQUE (TRANSFER_ID, TEMPLATECODE, CONTRACTCODE);

------------ ERP_I_CONTRACTEMBTEMPLINE ------------
CREATE TABLE ERP_I_CONTRACTEMBTEMPLINE
( TRANSFER_ID                   NUMBER NOT NULL,
  TRANSFER_STATUS               NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID57 CHECK (TRANSFER_STATUS IN (0,1)),
  TEMPLATECODE                  VARCHAR2(6) NOT NULL,
  CONTRACTCODE                  VARCHAR2(15) NOT NULL,
  PRODUCTCODE                   VARCHAR2(12) NULL,
  SIZEDEFINITIONCODE            VARCHAR2(12) NOT NULL,
  DESCRIPTION                   VARCHAR2(80) NULL,
  TEXTCOLORCODE                 VARCHAR2(12) NULL,
  BORDERCOLORCODE               VARCHAR2(12) NULL,
  BKGCOLORCODE                  VARCHAR2(12) NULL,
  FABRICCODE                    VARCHAR2(12) NOT NULL,
  PLACEMENTCODE                 VARCHAR2(6) NULL,
  EMBLEMTYPE                    NUMBER DEFAULT 3 NOT NULL CONSTRAINT ERP_I_CONTEMBLINE_EMBLEMTYPE CHECK (EMBLEMTYPE IN (1, 2, 3)),
  ATTACHMENTBY                  NUMBER DEFAULT 0 NOT NULL CONSTRAINT ERP_I_CONTEMBLINE_ATTACHMTBY CHECK (ATTACHMENTBY IN (0, 1, 2, 3, 4)),
  QTYPERGARMENT                 NUMBER DEFAULT 1 NOT NULL,
  EMBLEMIMAGECODE               VARCHAR2(12) NULL,
  PLACEMENTIMAGECODE            VARCHAR2(12) NULL,
  EMBLEMFONTDESCRIPTION         VARCHAR2(30) NULL,
  EMBLEMFONTSIZE                NUMBER DEFAULT 8 NOT NULL,
  EMBROIDERYFILEPATHORIGINAL    VARCHAR2(60) NULL,
  EMBROIDERYFILEPATHPRODUCTION  VARCHAR2(60) NULL,
  STITCHCOUNT                   NUMBER DEFAULT 0 NOT NULL,
  SETUPFEE                      NUMBER DEFAULT 0 NOT NULL,
  COLORPALETTE                  VARCHAR2(30) NULL,
  EMBROIDERYLETTERING           VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT ERP_I_CONTEMBLINE_EMBROIDLTR CHECK (EMBROIDERYLETTERING IN ('Y', 'N')),
  EMBROIDERYLETTERINGLINE1      VARCHAR2(35) NULL,
  EMBROIDERYLETTERINGLINE2      VARCHAR2(35) NULL,
  EMBROIDERYLETTERINGLINE3      VARCHAR2(35) NULL,
  CONTRACTEMBTEMPLINEPIECEPRICE  NUMBER(15,6) NULL,
  EMTEMPLINEPIECEPRICESTARTDATE DATE NULL,
  CONTRACTEMBTEMPSALESPRICE      NUMBER(15,6) NULL,
  EMTEMPSALESPRICESTARTDATE     DATE NULL);

COMMENT ON COLUMN ERP_I_CONTRACTEMBTEMPLINE.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRACTEMBTEMPLINE.EMBLEMTYPE
  IS '1= Company
2 = Name
3 = Other/ Not Defined';
COMMENT ON COLUMN ERP_I_CONTRACTEMBTEMPLINE.ATTACHMENTBY
  IS '0 = Unknown
1 = Local Stockroom (default)
2 = Central Stockroom
3 = Supplier
4 = Stocked';
COMMENT ON COLUMN ERP_I_CONTRACTEMBTEMPLINE.EMBLEMFONTSIZE
  IS 'Font size in mm. Default 8';

CREATE INDEX INDX_FK_ERP_I_CONTRACTEMBTMPLN ON ERP_I_CONTRACTEMBTEMPLINE (TRANSFER_ID ASC, TEMPLATECODE ASC, CONTRACTCODE ASC);

ALTER TABLE ERP_I_CONTRACTEMBTEMPLINE
  ADD CONSTRAINT PK_ERP_I_CONTRACTEMBTEMPLINE UNIQUE (TRANSFER_ID, TEMPLATECODE, CONTRACTCODE, PRODUCTCODE, SIZEDEFINITIONCODE, TEXTCOLORCODE, BORDERCOLORCODE, BKGCOLORCODE, FABRICCODE, PLACEMENTCODE, EMBLEMFONTDESCRIPTION, EMBLEMTYPE);
ALTER TABLE ERP_I_CONTRACTEMBTEMPLINE
  ADD CONSTRAINT FK_ERP_I_CONTRACTEMBTEMPLINE FOREIGN KEY (TRANSFER_ID, TEMPLATECODE, CONTRACTCODE)
  REFERENCES ERP_I_CONTRACTEMBTEMP (TRANSFER_ID, TEMPLATECODE, CONTRACTCODE) ON DELETE CASCADE;
   
ALTER TABLE ERP_I_CONTRACTEMBTEMPLINE DROP CONSTRAINT ERP_I_CONTEMBLINE_PRODPRCCHK;

ALTER TABLE ERP_I_CONTRACTEMBTEMPLINE
  ADD CONSTRAINT ERP_I_CONTEMBLINE_PRODPRCCHK
  CHECK ((CONTRACTEMBTEMPSALESPRICE IS NULL) OR (CONTRACTEMBTEMPSALESPRICE IS NOT NULL AND PRODUCTCODE IS NOT NULL));

------------ ERP_I_CONTRACTEMBTEMP_LOG ------------
CREATE TABLE ERP_I_CONTRACTEMBTEMP_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID58 CHECK (TRANSFER_STATUS IN (0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);

COMMENT ON COLUMN ERP_I_CONTRACTEMBTEMP_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRACTEMBTEMP_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
 
ALTER TABLE ERP_I_CONTRACTEMBTEMP_LOG
  ADD CONSTRAINT PK_ERP_I_CONTRACTEMBTEMP_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_CONTRACTEMBTEMP_LOG
  ADD CONSTRAINT FK_ERP_I_CONTRACTEMBTEMP_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CONTRACTEMBTEMP (TRANSFER_ID) ON DELETE CASCADE;
