--ABS-73805

-------------- ERP_I_CUSTOMER --------------
ALTER TABLE ERP_I_CUSTOMER ADD (TIN VARCHAR2(20) NULL,
                               VAT_REGNO VARCHAR2(20) NULL);

COMMENT ON COLUMN ERP_I_CUSTOMER.TIN
  IS 'Flex field for customer table called TIN';
COMMENT ON COLUMN ERP_I_CUSTOMER.VAT_REGNO
  IS 'Flex field for customer table called VAT_REGNO';  

-------------- ERP_I_EMPLOYEE --------------
ALTER TABLE ERP_I_EMPLOYEE DROP CONSTRAINT CHK_ERP_I_EMPLOYEE_WORKSPOT;
ALTER TABLE ERP_I_EMPLOYEE DROP CONSTRAINT CHK_ERP_I_EMPLOYEE_RELIEFDRV;
ALTER TABLE ERP_I_EMPLOYEE DROP CONSTRAINT CHK_ERP_I_EMPLOYEE_DATES;

CREATE TABLE ERP_I_EMPLOYEE
( TRANSFER_ID          NUMBER NOT NULL,
  TRANSFER_STATUS      NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  PLANTCODE            VARCHAR2(6) NOT NULL,
  EMPLOYEENUMBER       NUMBER NOT NULL,
  NAME                 VARCHAR2(40) NOT NULL,
  SEARCHNAME           VARCHAR2(10) NOT NULL,
  DATEACTIVE           DATE DEFAULT TRUNC(SYSDATE-1) NOT NULL,
  DATEINACTIVE         DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  PDANUMBER            NUMBER DEFAULT 0 NULL,
  MOBILEDEVICECODE     VARCHAR2(6) NULL,
  WORKSPOT             NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_EMPLOYEE_WORKSPOT CHECK (WORKSPOT IN (0,1)),
  EMPLOYEEFUNCTIONCODE VARCHAR2(6) NOT NULL,
  PHONENO              VARCHAR2(15) NULL,
  MOBILENO             VARCHAR2(15) NULL,
  FAXNO                VARCHAR2(15) NULL,
  EMAIL                VARCHAR2(300) NULL,
  REMARK               VARCHAR2(80) NULL,
  EMPLOYEEFUNCTIONDESC VARCHAR2(30) NULL,
  POAPPROVALPINCODE    VARCHAR2(80) NULL,
  RELIEFDRIVER         VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT CHK_ERP_I_EMPLOYEE_RELIEFDRV CHECK (RELIEFDRIVER IN ('Y', 'N')));

COMMENT ON COLUMN ERP_I_EMPLOYEE.PLANTCODE
  IS 'Must be an existing plant code';
COMMENT ON COLUMN ERP_I_EMPLOYEE.WORKSPOT
  IS '0=Own plant only
1=All plants';
COMMENT ON COLUMN ERP_I_EMPLOYEE.EMPLOYEEFUNCTIONCODE
  IS 'Must already exist in ABSSolute as a function code';

ALTER TABLE ERP_I_EMPLOYEE
  ADD CONSTRAINT PK_ERP_I_EMPLOYEE PRIMARY KEY (TRANSFER_ID);

CREATE INDEX XIF1ERP_I_EMPLOYEE ON ERP_I_EMPLOYEE (PLANTCODE ASC);
  
ALTER TABLE ERP_I_EMPLOYEE
  ADD CONSTRAINT FK_ERP_I_EMPLOYEE_PLANT FOREIGN KEY (PLANTCODE)
  REFERENCES PLANT (CODE);

ALTER TABLE ERP_I_EMPLOYEE
  ADD CONSTRAINT CHK_ERP_I_EMPLOYEE_DATES
  CHECK (DATEACTIVE <= DATEINACTIVE);

ALTER TABLE ERP_I_EMPLOYEE DROP CONSTRAINT FK_ERP_I_EMPLOYEE_FUNCTION;

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

COMMENT ON COLUMN ERP_I_EMPLOYEE_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_EMPLOYEE_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';

ALTER TABLE ERP_I_EMPLOYEE_LOG
  ADD CONSTRAINT PK_ERP_I_EMPLOYEE_LOG PRIMARY KEY (TRANSFER_ID);

ALTER TABLE ERP_I_EMPLOYEE_LOG
  ADD CONSTRAINT FK_ERP_I_EMPLOYEE_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_EMPLOYEE (TRANSFER_ID) ON DELETE CASCADE;
  

-------------- ERP_I_COSTMERCHANDIZELIST --------------
ALTER TABLE ERP_I_COSTMERCHANDIZELIST DROP CONSTRAINT CHK_ERP_I_COSTMCHLIST_ACTIVE;
ALTER TABLE ERP_I_COSTMERCHANDIZELIST DROP CONSTRAINT CHK_ERP_I_COSTMCHLIST_COSTPLTP;
ALTER TABLE ERP_I_COSTMERCHANDIZELIST DROP CONSTRAINT CHK_ERP_I_COSTMCHLIST_SERVTYPE;
ALTER TABLE ERP_I_COSTMERCHANDIZELIST DROP CONSTRAINT CHK_ERP_I_COSTMCHLIST_PROD;
ALTER TABLE ERP_I_COSTMERCHANDIZELIST DROP CONSTRAINT CHK_ERP_I_COSTMCHLIST_SIZE;
ALTER TABLE ERP_I_COSTMERCHANDIZELIST DROP CONSTRAINT CHK_ERP_I_COSTMCHLIST_FINM;
ALTER TABLE ERP_I_COSTMERCHANDIZELIST DROP CONSTRAINT CHK_ERP_I_COSTMCHLIST_DETAILTP;

CREATE TABLE ERP_I_COSTMERCHANDIZELIST
( TRANSFER_ID                 NUMBER NOT NULL,
  TRANSFER_STATUS             NUMBER(1) DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID3 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  COSTPRICELISTCODE           VARCHAR2(15) NOT NULL,
  DESCRIPTION                 VARCHAR2(40) NOT NULL,
  ACTIVE                      VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_ERP_I_COSTMCHLIST_ACTIVE CHECK (ACTIVE IN ('Y','N')),
  COSTPRICELISTTYPE           NUMBER NOT NULL  CONSTRAINT CHK_ERP_I_COSTMCHLIST_COSTPLTP CHECK (COSTPRICELISTTYPE IN (1,2,3)),
  CURRENCYCODE                VARCHAR2(3) NOT NULL,
  SERVICETYPE                 NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_COSTMCHLIST_SERVTYPE CHECK (SERVICETYPE IN (0,1,2)),
  PROCESSBUSINESSUNIT         VARCHAR2(6) NULL,
  INTERNALPROCESSINGBU        VARCHAR2(6) NULL,
  PRODUCTCODE                 VARCHAR2(12) NOT NULL CONSTRAINT CHK_ERP_I_COSTMCHLIST_PROD CHECK (PRODUCTCODE = UPPER(TRIM(PRODUCTCODE))),
  SIZECODE                    VARCHAR2(12) NOT NULL CONSTRAINT CHK_ERP_I_COSTMCHLIST_SIZE CHECK (SIZECODE = UPPER(TRIM(SIZECODE))),
  FINISHINGMETHOD             VARCHAR2(6) NOT NULL CONSTRAINT CHK_ERP_I_COSTMCHLIST_FINM CHECK (FINISHINGMETHOD = UPPER(TRIM(FINISHINGMETHOD))),
  PRICE                       NUMBER(15,6) NULL,
  STARTDATE                   DATE DEFAULT SYSDATE NOT NULL,
  DEPRECIATIONPERCENTAGESTOCK NUMBER(15,3) DEFAULT 0 NOT NULL,
  DEPRECIATIONSTARTWEEK       NUMBER DEFAULT 0 NOT NULL,
  DEPRECIATIONPERCENTAGEPOOL  NUMBER(15,3) DEFAULT 0 NOT NULL,
  COSTPRICELISTDETAILTYPE     NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_COSTMCHLIST_DETAILTP CHECK (COSTPRICELISTDETAILTYPE IN (1,2,3)),
  INVOICEBUSINESSUNIT         VARCHAR2(6) NULL);

COMMENT ON COLUMN ERP_I_COSTMERCHANDIZELIST.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_COSTMERCHANDIZELIST.COSTPRICELISTTYPE
  IS '1 = Merchandise Cost
2 = Processing Cost
3 = Internal Calculation';
COMMENT ON COLUMN ERP_I_COSTMERCHANDIZELIST.SERVICETYPE
  IS '0 = Not applicable (default)
1 = Wash
2 = All-in-Rent';
COMMENT ON COLUMN ERP_I_COSTMERCHANDIZELIST.COSTPRICELISTDETAILTYPE
  IS '0 = All items (default)
1 = LOG only
2 = COG only';

ALTER TABLE ERP_I_COSTMERCHANDIZELIST
  ADD CONSTRAINT PK_ERP_I_COSTMERCHANDIZELIST PRIMARY KEY (TRANSFER_ID);

CREATE INDEX XIF1ERP_I_COSTMERCHANDIZELIST ON ERP_I_COSTMERCHANDIZELIST (CURRENCYCODE ASC); 
CREATE INDEX XIF2ERP_I_COSTMERCHANDIZELIST ON ERP_I_COSTMERCHANDIZELIST (INTERNALPROCESSINGBU ASC);
CREATE INDEX XIF3ERP_I_COSTMERCHANDIZELIST ON ERP_I_COSTMERCHANDIZELIST (INVOICEBUSINESSUNIT ASC);
CREATE INDEX XIF4ERP_I_COSTMERCHANDIZELIST ON ERP_I_COSTMERCHANDIZELIST (PROCESSBUSINESSUNIT ASC);
  
ALTER TABLE ERP_I_COSTMERCHANDIZELIST
  ADD CONSTRAINT FK_ERP_I_COSTMCHLIST_CURRENCY FOREIGN KEY (CURRENCYCODE)
  REFERENCES CURRENCY (CODE);
ALTER TABLE ERP_I_COSTMERCHANDIZELIST
  ADD CONSTRAINT FK_ERP_I_COSTMCHLIST_INTERBU FOREIGN KEY (INTERNALPROCESSINGBU)
  REFERENCES BUSINESSUNIT (CODE);
ALTER TABLE ERP_I_COSTMERCHANDIZELIST
  ADD CONSTRAINT FK_ERP_I_COSTMCHLIST_INVOIBU FOREIGN KEY (INVOICEBUSINESSUNIT)
  REFERENCES BUSINESSUNIT (CODE);
ALTER TABLE ERP_I_COSTMERCHANDIZELIST
  ADD CONSTRAINT FK_ERP_I_COSTMCHLIST_PROCSBU FOREIGN KEY (PROCESSBUSINESSUNIT)
  REFERENCES BUSINESSUNIT (CODE);

-------------- ERP_I_COSTMERCHANDIZELIST_LOG --------------
CREATE TABLE ERP_I_COSTMERCHANDIZELIST_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID4 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_COSTMERCHANDIZELIST_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_COSTMERCHANDIZELIST_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
  
ALTER TABLE ERP_I_COSTMERCHANDIZELIST_LOG
  ADD CONSTRAINT PK_ERP_I_COSTMERCHDLIST_LOG PRIMARY KEY (TRANSFER_ID);
  
ALTER TABLE ERP_I_COSTMERCHANDIZELIST_LOG
  ADD CONSTRAINT FK_ERP_I_COSTMERCHDLIST_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_COSTMERCHANDIZELIST(TRANSFER_ID) ON DELETE CASCADE;  
  
-------------- ERP_I_PRODUCTSIZE --------------
ALTER TABLE ERP_I_PRODUCTSIZE DROP CONSTRAINT CHK_ERP_I_PRODSIZE_UNFINLNGTH;
ALTER TABLE ERP_I_PRODUCTSIZE DROP CONSTRAINT CHK_ERP_I_PRODSIZE_RESALTSIZE;
ALTER TABLE ERP_I_PRODUCTSIZE DROP CONSTRAINT CHK_ERP_I_PRODSIZE_FOREMBLEM;

ALTER TABLE ERP_I_PRODUCTSIZE ADD (SIZE_DESCRIPTION VARCHAR2(30) NULL,
                                   WIDTH VARCHAR2(6) NULL,
                                   LENGTH VARCHAR2(6) NULL,
                                   UNFINISHEDSIZEDEFINITIONCODE VARCHAR2(12) NULL,
                                   UNFINISHEDLENGTH VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT CHK_ERP_I_PRODSIZE_UNFINLNGTH CHECK (UNFINISHEDLENGTH  IN ('Y','N')),
                                   RESERVEALTERNATIVESIZE VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT CHK_ERP_I_PRODSIZE_RESALTSIZE  CHECK (RESERVEALTERNATIVESIZE IN ('Y','N')),
                                   SIZE_USEDFOREMBLEM VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT CHK_ERP_I_PRODSIZE_FOREMBLEM CHECK (SIZE_USEDFOREMBLEM IN ('Y','N')));

-------------- ERP_I_EMBLEMTEMPLATE --------------
ALTER TABLE ERP_I_EMBLEMTEMPLATE DROP CONSTRAINT CHK_ERP_I_EMBLTMP_PRNTNMLABEL;
ALTER TABLE ERP_I_EMBLEMTEMPLATE DROP CONSTRAINT CHK_ERP_I_EMBLTMP_RENTEMBLEM;
ALTER TABLE ERP_I_EMBLEMTEMPLATE DROP CONSTRAINT CHK_ERP_I_EMBLTMP_EMBROIDERY;
ALTER TABLE ERP_I_EMBLEMTEMPLATE DROP CONSTRAINT CHK_ERP_I_EMBLTMP_ACTIVE;

CREATE TABLE ERP_I_EMBLEMTEMPLATE
( TRANSFER_ID                   NUMBER NOT NULL,
  TRANSFER_STATUS               NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID5 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEMPLATECODE                  VARCHAR2(6) NOT NULL,
  CUSTOMERNUMBER                NUMBER NOT NULL,
  TEMPLATEDESCRIPTION           VARCHAR2(30) NULL,
  PRINTNAMELABEL                VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT CHK_ERP_I_EMBLTMP_PRNTNMLABEL CHECK (PRINTNAMELABEL IN ('Y', 'N')),
  RENTEMBLEM                    VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT CHK_ERP_I_EMBLTMP_RENTEMBLEM CHECK (RENTEMBLEM IN ('Y', 'N')),
  EMBROIDERY                    VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT CHK_ERP_I_EMBLTMP_EMBROIDERY CHECK (EMBROIDERY IN ('Y', 'N')),
  EMBLEMTEMPLATEPRICE           NUMBER(15,6) NULL,
  EMBLEMTEMPLATEPRICESTARTDATE  DATE NULL,
  ACTIVE                        VARCHAR2(1) DEFAULT 'Y' NULL CONSTRAINT CHK_ERP_I_EMBLTMP_ACTIVE CHECK (ACTIVE IN ('Y', 'N')));

COMMENT ON COLUMN ERP_I_EMBLEMTEMPLATE.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';

ALTER TABLE ERP_I_EMBLEMTEMPLATE
  ADD CONSTRAINT PK_ERP_I_EMBLEMTEMPLATE PRIMARY KEY (TRANSFER_ID);

ALTER TABLE ERP_I_EMBLEMTEMPLATE DROP CONSTRAINT PK2_ERP_I_EMBLEMTEMPLATE CASCADE;
ALTER TABLE ERP_I_EMBLEMTEMPLATE DROP CONSTRAINT XAK1ERP_I_EMBLEMTEMPLATE CASCADE;

ALTER TABLE ERP_I_EMBLEMTEMPLATE
  ADD CONSTRAINT XAK1ERP_I_EMBLEMTEMPLATE UNIQUE (TRANSFER_ID, TEMPLATECODE, CUSTOMERNUMBER);


-------------- ERP_I_EMBLEMTEMPLATELINE --------------
ALTER TABLE ERP_I_EMBLEMTEMPLATELINE DROP CONSTRAINT ERP_I_EMBLINE_EMBLEMTYPE;
ALTER TABLE ERP_I_EMBLEMTEMPLATELINE DROP CONSTRAINT ERP_I_EMBLINE_ATTACHMENTBY;
ALTER TABLE ERP_I_EMBLEMTEMPLATELINE DROP CONSTRAINT ERP_I_EMBLINE_EMBROIDLETTR;
ALTER TABLE ERP_I_EMBLEMTEMPLATELINE DROP CONSTRAINT ERP_I_EMBLINE_PRODPRICECHK;

CREATE TABLE ERP_I_EMBLEMTEMPLATELINE
( TRANSFER_ID                   NUMBER NOT NULL,
  TRANSFER_STATUS               NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID6 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEMPLATECODE                  VARCHAR2(6) NOT NULL,
  CUSTOMERNUMBER                NUMBER 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_EMBLINE_EMBLEMTYPE CHECK (EMBLEMTYPE IN (1, 2, 3)),
  ATTACHMENTBY                  NUMBER DEFAULT 0 NOT NULL CONSTRAINT ERP_I_EMBLINE_ATTACHMENTBY 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_EMBLINE_EMBROIDLETTR CHECK (EMBROIDERYLETTERING IN ('Y', 'N')),
  EMBROIDERYLETTERINGLINE1      VARCHAR2(35) NULL,
  EMBROIDERYLETTERINGLINE2      VARCHAR2(35) NULL,
  EMBROIDERYLETTERINGLINE3      VARCHAR2(35) NULL,
  EMBLEMTEMPLATELINEPIECEPRICE  NUMBER(15,6) NULL,
  EMTEMPLINEPIECEPRICESTARTDATE DATE NULL,
  EMBLEMTEMPLATESALESPRICE      NUMBER(15,6) NULL,
  EMTEMPSALESPRICESTARTDATE     DATE NULL);

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

CREATE INDEX XIE1ERP_I_EMBLEMTEMPLINE ON ERP_I_EMBLEMTEMPLATELINE (TRANSFER_ID ASC, TEMPLATECODE ASC, CUSTOMERNUMBER ASC);

ALTER TABLE ERP_I_EMBLEMTEMPLATELINE
  ADD CONSTRAINT PK_ERP_I_EMBLEMTEMPLATELINE UNIQUE (TRANSFER_ID, TEMPLATECODE, CUSTOMERNUMBER, PRODUCTCODE, SIZEDEFINITIONCODE, TEXTCOLORCODE, BORDERCOLORCODE, BKGCOLORCODE, FABRICCODE, PLACEMENTCODE, EMBLEMFONTDESCRIPTION, EMBLEMTYPE);
ALTER TABLE ERP_I_EMBLEMTEMPLATELINE
  ADD CONSTRAINT FK_ERP_I_EMBLEMTEMPLATELINE FOREIGN KEY (TRANSFER_ID, TEMPLATECODE, CUSTOMERNUMBER)
  REFERENCES ERP_I_EMBLEMTEMPLATE (TRANSFER_ID, TEMPLATECODE, CUSTOMERNUMBER) ON DELETE CASCADE;
ALTER TABLE ERP_I_EMBLEMTEMPLATELINE
  ADD CONSTRAINT ERP_I_EMBLINE_PRODPRICECHK
  CHECK ((EMBLEMTEMPLATESALESPRICE IS NULL) OR (EMBLEMTEMPLATESALESPRICE IS NOT NULL AND PRODUCTCODE IS NOT NULL));

--section break for pack_Script
-------------- ERP_I_EMBLEMTEMPLATE_LOG --------------
CREATE TABLE ERP_I_EMBLEMTEMPLATE_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID7 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_EMBLEMTEMPLATE_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_EMBLEMTEMPLATE_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';

ALTER TABLE ERP_I_EMBLEMTEMPLATE_LOG
  ADD CONSTRAINT PK_ERP_I_EMBLEMTEMPLATE_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_EMBLEMTEMPLATE_LOG
  ADD CONSTRAINT FK_ERP_I_EMBLEMTEMPLATE_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_EMBLEMTEMPLATE (TRANSFER_ID) ON DELETE CASCADE;

-------------- ERP_I_PRODUCT --------------
ALTER TABLE ERP_I_PRODUCT DROP CONSTRAINT CHK_ERP_I_PRODUCT_EMBLEMTYPE;
ALTER TABLE ERP_I_PRODUCT DROP CONSTRAINT CHK_ERP_I_PRODUCT_SALESONLY;

ALTER TABLE ERP_I_PRODUCT ADD (EMBLEMTYPE NUMBER DEFAULT 3 NULL CONSTRAINT CHK_ERP_I_PRODUCT_EMBLEMTYPE CHECK (EMBLEMTYPE IN (1,2,3)),
                               EMBLEMTEXTCOLOR VARCHAR2(12) NULL,
                               EMBLEMTEXTCOLOR_DESCRIPTION VARCHAR2(50) NULL,
                               EMBLEMBORDERCOLOR VARCHAR2(12) NULL,
                               EMBLEMBORDERCOLOR_DESCRIPTION VARCHAR2(50) NULL,
                               EMBLEMFONTDESCRIPTION VARCHAR2(30) NULL,
							   SALESONLY VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT CHK_ERP_I_PRODUCT_SALESONLY CHECK (SALESONLY IN ('Y','N')),
							   MRPCODE VARCHAR2(8) NULL,
							   TURNOVERGROUPRENT VARCHAR2(12) NULL,
							   TURNOVERGROUPWASH VARCHAR2(12) NULL,
							   TURNOVERGROUPREPL VARCHAR2(12) NULL,
							   TURNOVERGROUPSALES VARCHAR2(12) NULL,
							   TURNOVERGROUPLOSS VARCHAR2(12) NULL,
							   TURNOVERGROUPADVANCE VARCHAR2(12) NULL,
							   TURNOVERGROUPCORRECTIONS VARCHAR2(12) NULL,
							   TURNOVERGROUPWASHCOG VARCHAR2(12) NULL);

COMMENT ON COLUMN ERP_I_PRODUCT.EMBLEMTYPE
  IS 'Only for type of merchandise 4 options: 
1=Company
2=Name
3=Other/ Not Defined';
COMMENT ON COLUMN ERP_I_PRODUCT.EMBLEMTEXTCOLOR
  IS 'Emblem text color for emblems, type of merchandise 4';
COMMENT ON COLUMN ERP_I_PRODUCT.EMBLEMTEXTCOLOR_DESCRIPTION
  IS 'Emblem text color description for emblems, type of merchandise 4, only for new colors';
COMMENT ON COLUMN ERP_I_PRODUCT.EMBLEMBORDERCOLOR
  IS 'Emblem border color for emblems, type of merchandise 4';
COMMENT ON COLUMN ERP_I_PRODUCT.EMBLEMBORDERCOLOR_DESCRIPTION
  IS 'Emblem border color description for emblems, type of merchandise 4, only for new colors';
COMMENT ON COLUMN ERP_I_PRODUCT.EMBLEMFONTDESCRIPTION
  IS 'Emblem font description for emblems, type of merchandise 4'; 
COMMENT ON COLUMN ERP_I_PRODUCT.SALESONLY
  IS 'If this is set, only Billing Method 4 is allowed';
 
-------------- ERP_I_CONTRACT --------------
ALTER TABLE ERP_I_CONTRACT DROP CONSTRAINT ERP_I_CONTRACT_COMMPAYASALESYN;
ALTER TABLE ERP_I_CONTRACT DROP CONSTRAINT ERP_I_CONTRACT_COMMPAYABLAGRYN;
ALTER TABLE ERP_I_CONTRACT DROP CONSTRAINT ERP_I_CONTRACT_ALLOWNOTAGRPRYN;
ALTER TABLE ERP_I_CONTRACT DROP CONSTRAINT ERP_I_CONTRACT_CATEGORY;
ALTER TABLE ERP_I_CONTRACT DROP CONSTRAINT ERP_I_CONTRACT_DATECHECK;
ALTER TABLE ERP_I_CONTRACT DROP CONSTRAINT ERP_I_CONTRACT_OWNER_CUSTOMER;

CREATE TABLE ERP_I_CONTRACT
( TRANSFER_ID                 NUMBER NOT NULL,
  TRANSFER_STATUS             NUMBER(1) DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID8 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  CONTRACTCODE                VARCHAR2(15) DEFAULT 0 NOT NULL,
  CONTRACTDESCRIPTION         VARCHAR2(50) DEFAULT '-' NOT NULL,
  CONTRACTTYPECODE            VARCHAR2(6) DEFAULT '-' NOT NULL,
  CONTRACTTYPEDESC            VARCHAR2(30) DEFAULT '-' NOT NULL,
  USERGROUPNAME               VARCHAR2(20) NULL,
  CONTRACTDOCUMENT            VARCHAR2(255) NULL,
  COMMISSIONPAYABLESALESYN    VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT ERP_I_CONTRACT_COMMPAYASALESYN CHECK (COMMISSIONPAYABLESALESYN IN ('Y','N')),
  SALESEMPLOYEENUMBER         NUMBER NULL,
  SALESEMPLOYEENAME           VARCHAR2(40) NULL,
  COMMISSIONPAYABLEACCMGRYN   VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT ERP_I_CONTRACT_COMMPAYABLAGRYN CHECK (COMMISSIONPAYABLEACCMGRYN IN ('Y','N')),
  ACCOUNTMANAGERNUMBER        NUMBER NULL,
  ACCOUNTMANAGERNAME          VARCHAR2(40) NULL,
  CUSTOMERNUMBER              NUMBER NULL,
  OWNER                       VARCHAR2(40) NULL,
  OWNER_STREET                VARCHAR2(30) NULL,
  OWNER_ZIPCODE               VARCHAR2(15) NULL,
  OWNER_CITY                  VARCHAR2(30) NULL,
  OWNER_STATE                 VARCHAR2(20) NULL,
  OWNER_COUNTY                VARCHAR2(20) NULL,
  OWNER_COUNTRYCODE           VARCHAR2(3) NULL,
  CVRNUMBER                   VARCHAR2(15) NULL,
  PHONENUMBER                 VARCHAR2(30) NULL,
  SIGNDATE                    DATE DEFAULT TRUNC(SYSDATE)-1 NOT NULL,
  STARTDATE                   DATE DEFAULT TRUNC(SYSDATE)-1 NOT NULL,
  ENDDATE                     DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  DURATION                    NUMBER(2) DEFAULT 48 NOT NULL,
  TERMOFNOTICE                NUMBER(2) DEFAULT 3 NOT NULL,
  TERMINATIONDATE             DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  AUTEXTENSIONPERIOD          NUMBER(2) DEFAULT 12 NOT NULL,
  CUSTOMERCONTACTPERSONNUMBER NUMBER NULL,
  CONTACTPERSONNAME           VARCHAR2(30) NULL,
  GAINREASONCODE              VARCHAR2(6) NULL,
  GAINREASONDESCRIPTION       VARCHAR2(30) NULL,
  LOSTREASONCODE              VARCHAR2(6) NULL,
  LOSTREASONDESCRIPTION       VARCHAR2(30) NULL,
  GAINCOMPETITORCODE          VARCHAR2(6) NULL,
  GAINCOMPETITORNAME          VARCHAR2(40) NULL,
  LOSTCOMPETITORCODE          VARCHAR2(6) NULL,
  LOSTCOMPETITORNAME          VARCHAR2(40) NULL,
  STARTDATEPRICEINCREASE      DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  UPDATEEVERY                 NUMBER(2) DEFAULT 6 NOT NULL,
  PREVIOUSPRICEINCREASE       DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  NEXTSCHEDULEDPRICEINCREASE  DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  AGREEDPRICEINDEXCODE        VARCHAR2(6) DEFAULT '1' NOT NULL,
  AGREEDPRICEINDEXDESC        VARCHAR2(30) DEFAULT '-' NOT NULL,
  NOTAGREEDPRICEINDEXCODE     VARCHAR2(6) DEFAULT '2' NULL,
  NOTAGREEDPRICEINDEXDESC     VARCHAR2(30) DEFAULT '-' NULL,
  ALLOWNOTAGREEDPRICEINDEXYN  VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT ERP_I_CONTRACT_ALLOWNOTAGRPRYN CHECK (ALLOWNOTAGREEDPRICEINDEXYN IN ('Y','N')),
  PURCHASEORDER               VARCHAR2(30) NULL,
  PURCHASEORDERVALUE          NUMBER NULL,
  PURCHASEORDEREXPIRATIONDATE DATE NULL,
  ESTIMATEDWEEKLYSALES        NUMBER NULL,
  REMARK                      VARCHAR2(300) NULL,
  RENEGOTIATIONDATE           DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  WARNDAYSPRICEINCREASE       NUMBER(2) DEFAULT 0 NOT NULL,
  WARNPRICEINCREASE           VARCHAR2(1) DEFAULT 'N' NOT NULL,
  CATEGORY                    NUMBER DEFAULT 1 NOT NULL CONSTRAINT ERP_I_CONTRACT_CATEGORY CHECK (CATEGORY IN (1,3,4)),
  CURRENCY                    VARCHAR2(3) NULL,
  EMAIL                       VARCHAR2(300) NULL,
  GARMENTSCONTRACT            NUMBER DEFAULT 0 NOT NULL,
  SALESEMPLOYEENUMBER2        NUMBER NULL,
  SALESEMPLOYEENAME2          VARCHAR2(40) NULL,
  SALESEMPLOYEE1PERCENTAGE    NUMBER NULL,
  SALESEMPLOYEE2PERCENTAGE    NUMBER NULL,
  SALESLEADEMPLOYEENUMBER     NUMBER NULL,
  SALESLEADEMPLOYEENAME       VARCHAR2(40) NULL,
  PRIMARYCONTRACTCODE 		    VARCHAR2(15) NULL);
  
COMMENT ON COLUMN ERP_I_CONTRACT.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRACT.CONTRACTCODE
  IS 'Unique contract code';
COMMENT ON COLUMN ERP_I_CONTRACT.CONTRACTTYPECODE
  IS 'Contract type table code';
COMMENT ON COLUMN ERP_I_CONTRACT.CONTRACTTYPEDESC
  IS 'Contract type table description';
COMMENT ON COLUMN ERP_I_CONTRACT.USERGROUPNAME
  IS 'Foreign key to usergroup table name';
COMMENT ON COLUMN ERP_I_CONTRACT.CONTRACTDOCUMENT
  IS 'Document name including full path';
COMMENT ON COLUMN ERP_I_CONTRACT.COMMISSIONPAYABLESALESYN
  IS 'Y,N Default N';
COMMENT ON COLUMN ERP_I_CONTRACT.SALESEMPLOYEENUMBER
  IS 'Employee table';
COMMENT ON COLUMN ERP_I_CONTRACT.SALESEMPLOYEENAME
  IS 'Employee table';
COMMENT ON COLUMN ERP_I_CONTRACT.COMMISSIONPAYABLEACCMGRYN
  IS 'Y,N Default N';
COMMENT ON COLUMN ERP_I_CONTRACT.ACCOUNTMANAGERNUMBER
  IS 'Employee table';
COMMENT ON COLUMN ERP_I_CONTRACT.ACCOUNTMANAGERNAME
  IS 'Employee table';
COMMENT ON COLUMN ERP_I_CONTRACT.CUSTOMERNUMBER
  IS 'FK to ERP_I_CUSTOMER if not null';
COMMENT ON COLUMN ERP_I_CONTRACT.OWNER
  IS 'Only linked to a customer or OWNER fields filled';
COMMENT ON COLUMN ERP_I_CONTRACT.OWNER_STREET
  IS 'FK to address';
COMMENT ON COLUMN ERP_I_CONTRACT.OWNER_ZIPCODE
  IS 'FK to address';
COMMENT ON COLUMN ERP_I_CONTRACT.OWNER_CITY
  IS 'FK to address';
COMMENT ON COLUMN ERP_I_CONTRACT.OWNER_STATE
  IS 'FK to address';
COMMENT ON COLUMN ERP_I_CONTRACT.OWNER_COUNTY
  IS 'FK to address';
COMMENT ON COLUMN ERP_I_CONTRACT.OWNER_COUNTRYCODE
  IS 'FK to country table';
COMMENT ON COLUMN ERP_I_CONTRACT.ENDDATE
  IS 'Must be >= STARTDATE';
COMMENT ON COLUMN ERP_I_CONTRACT.DURATION
  IS 'Duration in months default 48, between 1 and 99';
COMMENT ON COLUMN ERP_I_CONTRACT.TERMOFNOTICE
  IS 'Between 0 and duration';
COMMENT ON COLUMN ERP_I_CONTRACT.TERMINATIONDATE
  IS 'Default ''31-12-2099'', must be > signdate';
COMMENT ON COLUMN ERP_I_CONTRACT.AUTEXTENSIONPERIOD
  IS 'Duration in months default 3, between 0 and 99';
COMMENT ON COLUMN ERP_I_CONTRACT.CUSTOMERCONTACTPERSONNUMBER
  IS 'Must exist in ERP_I_CONTACTPERSON, in case customernumber is not null';
COMMENT ON COLUMN ERP_I_CONTRACT.CONTACTPERSONNAME
  IS 'In case customernumber is null';
COMMENT ON COLUMN ERP_I_CONTRACT.GAINREASONCODE
  IS 'FK gainlost reason';
COMMENT ON COLUMN ERP_I_CONTRACT.GAINREASONDESCRIPTION
  IS 'FK gainlost reason desc';
COMMENT ON COLUMN ERP_I_CONTRACT.LOSTREASONCODE
  IS 'FK gainlost reason';
COMMENT ON COLUMN ERP_I_CONTRACT.LOSTREASONDESCRIPTION
  IS 'FK gainlost reason desc';
COMMENT ON COLUMN ERP_I_CONTRACT.GAINCOMPETITORCODE
  IS 'FK competitor';
COMMENT ON COLUMN ERP_I_CONTRACT.GAINCOMPETITORNAME
  IS 'FK competitor';
COMMENT ON COLUMN ERP_I_CONTRACT.LOSTCOMPETITORCODE
  IS 'FK competitor';
COMMENT ON COLUMN ERP_I_CONTRACT.LOSTCOMPETITORNAME
  IS 'FK competitor';
COMMENT ON COLUMN ERP_I_CONTRACT.UPDATEEVERY
  IS 'Duration in months default 6, between 0 and 99';
COMMENT ON COLUMN ERP_I_CONTRACT.PREVIOUSPRICEINCREASE
  IS 'Default ''31-12-2099'', means no previous price increase';
COMMENT ON COLUMN ERP_I_CONTRACT.NEXTSCHEDULEDPRICEINCREASE
  IS 'Must be > STARTDATEPRICEINCREASE';
COMMENT ON COLUMN ERP_I_CONTRACT.AGREEDPRICEINDEXCODE
  IS 'FK priceindex';
COMMENT ON COLUMN ERP_I_CONTRACT.AGREEDPRICEINDEXDESC
  IS 'FK priceindex';
COMMENT ON COLUMN ERP_I_CONTRACT.NOTAGREEDPRICEINDEXCODE
  IS 'FK priceindex';
COMMENT ON COLUMN ERP_I_CONTRACT.NOTAGREEDPRICEINDEXDESC
  IS 'FK priceindex';
COMMENT ON COLUMN ERP_I_CONTRACT.ALLOWNOTAGREEDPRICEINDEXYN
  IS 'Y,N Default N';
COMMENT ON COLUMN ERP_I_CONTRACT.REMARK
  IS 'Free text';
COMMENT ON COLUMN ERP_I_CONTRACT.WARNDAYSPRICEINCREASE
  IS '0 - 99, default 0';
COMMENT ON COLUMN ERP_I_CONTRACT.WARNPRICEINCREASE
  IS 'Y / N default N';
COMMENT ON COLUMN ERP_I_CONTRACT.CATEGORY
  IS '1 (Secondary), 3 (Primary), 4 (RSC)';
COMMENT ON COLUMN ERP_I_CONTRACT.GARMENTSCONTRACT
  IS '0 = No Garments 
1 = Include Garments in Contract';
COMMENT ON COLUMN ERP_I_CONTRACT.PRIMARYCONTRACTCODE
  IS 'Must be an existing primary contract and must be filled when it is a secondary contract';

ALTER TABLE ERP_I_CONTRACT
  ADD CONSTRAINT PK_ERP_I_CONTRACT PRIMARY KEY (TRANSFER_ID);

CREATE INDEX XIE1ERP_I_CONTRACT_CODE ON ERP_I_CONTRACT (CONTRACTCODE ASC);
CREATE INDEX XIF1ERP_I_CONTRACT_CODE ON ERP_I_CONTRACT (OWNER_COUNTRYCODE ASC);
CREATE INDEX XIF2ERP_I_CONTRACT_CODE ON ERP_I_CONTRACT (CURRENCY ASC);

ALTER TABLE ERP_I_CONTRACT
  ADD CONSTRAINT FK_ERP_I_CONTRACT_COUNTRYCODE FOREIGN KEY (OWNER_COUNTRYCODE)
  REFERENCES COUNTRY (CODE);
ALTER TABLE ERP_I_CONTRACT
  ADD CONSTRAINT ERP_I_CONTRACT_CURRENCY FOREIGN KEY (CURRENCY)
  REFERENCES CURRENCY (CODE);

ALTER TABLE ERP_I_CONTRACT
  ADD CONSTRAINT ERP_I_CONTRACT_DATECHECK
  CHECK (ENDDATE >= STARTDATE);
ALTER TABLE ERP_I_CONTRACT
  ADD CONSTRAINT ERP_I_CONTRACT_OWNER_CUSTOMER
  CHECK ((OWNER IS NOT NULL AND CUSTOMERNUMBER IS NULL) OR (OWNER IS NULL AND CUSTOMERNUMBER IS NOT NULL));

-------------- ERP_I_CONTRACT_LOG --------------
CREATE TABLE ERP_I_CONTRACT_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID9 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_CONTRACT_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRACT_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
  
ALTER TABLE ERP_I_CONTRACT_LOG
  ADD CONSTRAINT PK_ERP_I_CONTRACT_LOG PRIMARY KEY (TRANSFER_ID);

CREATE INDEX XIE1ERP_I_CONTRACT_LOG ON ERP_I_CONTRACT_LOG (TIMESTAMP ASC);

ALTER TABLE ERP_I_CONTRACT_LOG
  ADD CONSTRAINT FK_ERP_I_CONTRACT_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CONTRACT (TRANSFER_ID) ON DELETE CASCADE;  

-------------- ERP_I_CONTRACTCOMPONENT --------------
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_ALLOWAUTOMATICSALES;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_CNTRCTCMPONENTYN343;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT CHK_ERP_CHARGEREPAIRS;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT CHK_ERP_CHARGEREPAIRSCOG;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_CNTRCTCMPONENTYN344;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_CNTRCTCMPONENTYN345;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_CNTRCTCMPONENTYN346;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_CHK_ALLOWCHARGERETURNRENT;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_CHK_RETURNRENTWEEKS;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT CHK_ERP_ALLOWCHARGEMINRENT;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_CHK_MINRENTWEEKS;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_CNTRCTCMPONENTYN347;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT CHK_ERP_DAYSSPECIALGRADE;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT CHK_ERP_GARMENTRENTSPECIFIC;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_CHARGEPREPCHARGE;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_CHK_SENDSTATEMENT;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_ATTACHINVOICECOPIES;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_STATEMENTPERCUSTOMER;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_CNTRCTCMPONENT304;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_SENDSTATEMENTBYFAX;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_SPLITINVOICESPERACTIVITY;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_CHK_CHRLOSSFORPOOLITEMS;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_CHK_CHRLOSSFORUNIQPOOL;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_CHK_FINANCECHARGEDAYS;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_CHK_PUTFINANCECHARGEON;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_CHK_CUSTOMERRELATED;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_CHK_POREQUIRED;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_CHK_SOILCOUNT;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_CHK_YN_INTERNETORDERING;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_CNTRCTCMPONENTYN391;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_CONTRCOMPBGPROTECT;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_CNTRCTCMPONENTYN392;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_CNTRCTCMPONENTYN393;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT YERP_I_CNTRCTCMPONENTYN394;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_CNTRCTCMPONENTYN395;
ALTER TABLE ERP_I_CONTRACTCOMPONENT DROP CONSTRAINT ERP_I_CNTRCTCMPONENTYN396;

CREATE TABLE ERP_I_CONTRACTCOMPONENT
( TRANSFER_ID                   NUMBER NOT NULL,
  TRANSFER_STATUS               NUMBER(1) DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID10 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  CONTRACTCODE                  VARCHAR2(15) NOT NULL,
  ALLOWAUTOMATICSALES           VARCHAR2(1) NULL CONSTRAINT ERP_I_ALLOWAUTOMATICSALES CHECK (ALLOWAUTOMATICSALES IN ('Y','N')),
  DEPOSITSTOCKALLOWED           VARCHAR2(1) NULL CONSTRAINT ERP_I_CNTRCTCMPONENTYN343 CHECK (DEPOSITSTOCKALLOWED IN ('Y','N')),
  CHARGEREPAIRS                 NUMBER NULL CONSTRAINT CHK_ERP_CHARGEREPAIRS CHECK (CHARGEREPAIRS IN (0,1,2)),
  CHARGEREPAIRCOG               NUMBER NULL CONSTRAINT CHK_ERP_CHARGEREPAIRSCOG CHECK (CHARGEREPAIRCOG IN (0,1,2)),
  VARIABLESERVICES              NUMBER NULL,
  CHARGERESIDUALVALUE           VARCHAR2(1) NULL CONSTRAINT ERP_I_CNTRCTCMPONENTYN344 CHECK (CHARGERESIDUALVALUE IN ('Y','N')),
  CHARGEWASHFIRSTISSUE          VARCHAR2(1) NULL CONSTRAINT ERP_I_CNTRCTCMPONENTYN345 CHECK (CHARGEWASHFIRSTISSUE IN ('Y','N')),
  CHARGELOSSCHARGE              VARCHAR2(1) NULL CONSTRAINT ERP_I_CNTRCTCMPONENTYN346 CHECK (CHARGELOSSCHARGE IN ('Y','N')),
  ALLOWCHARGERETURNRENT         NUMBER NULL CONSTRAINT ERP_CHK_ALLOWCHARGERETURNRENT CHECK (ALLOWCHARGERETURNRENT IN (0, 1)),
  RETURNRENTWEEKS               NUMBER NULL CONSTRAINT ERP_CHK_RETURNRENTWEEKS CHECK (RETURNRENTWEEKS BETWEEN 0 AND 999),
  ALLOWCHARGEMINRENT            NUMBER NULL CONSTRAINT CHK_ERP_ALLOWCHARGEMINRENT CHECK (ALLOWCHARGEMINRENT IN (0, 1)),
  MINRENTWEEKS                  NUMBER NULL CONSTRAINT ERP_CHK_MINRENTWEEKS CHECK (MINRENTWEEKS BETWEEN 0 AND 999),
  CHARGESERVICECOSTS            VARCHAR2(1) NULL CONSTRAINT ERP_I_CNTRCTCMPONENTYN347 CHECK (CHARGESERVICECOSTS IN ('Y','N')),
  DAYSSPECIALGRADE              NUMBER NULL CONSTRAINT CHK_ERP_DAYSSPECIALGRADE CHECK (DAYSSPECIALGRADE BETWEEN 0 AND 999),
  FIRSTDAYSGRADE_CODE           VARCHAR2(1) NULL,
  FIRSTISSUEGRADE_CODE          VARCHAR2(1) NULL,
  QUALITYGRADE_CODE             VARCHAR2(1) NULL,
  GARMENTRENTSPECIFICATION      NUMBER NULL CONSTRAINT CHK_ERP_GARMENTRENTSPECIFIC CHECK (GARMENTRENTSPECIFICATION BETWEEN 0 AND 7),
  CHARGEPREPCHARGE              VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT ERP_I_CHARGEPREPCHARGE CHECK (CHARGEPREPCHARGE IN ('Y','N')),
  INVOICEFREQUENCY              NUMBER NULL,
  MINIMUMINVOICEAMOUNT          NUMBER DEFAULT 0 NULL,
  SENDSTATEMENT                 NUMBER DEFAULT 3 NULL CONSTRAINT ERP_CHK_SENDSTATEMENT CHECK (SENDSTATEMENT IN (1, 2, 3)),
  STATEMENTFREQUENCY            NUMBER NULL,
  ATTACHINVOICECOPIES           VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT ERP_I_ATTACHINVOICECOPIES CHECK (ATTACHINVOICECOPIES IN ('Y','N')),
  STATEMENTPERCUSTOMER          VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT ERP_I_STATEMENTPERCUSTOMER CHECK (STATEMENTPERCUSTOMER IN ('Y','N')),  
  SENDSTATEMENTBYEMAIL          VARCHAR2(1) NULL CONSTRAINT ERP_I_CNTRCTCMPONENT304 CHECK (SENDSTATEMENTBYEMAIL IN ('Y', 'N')),
  SENDSTATEMENTBYFAX            VARCHAR2(1) NULL CONSTRAINT ERP_I_SENDSTATEMENTBYFAX CHECK (SENDSTATEMENTBYFAX IN ('Y', 'N')),
  SPLITINVOICESPERACTIVITY      VARCHAR2(1) NULL CONSTRAINT ERP_I_SPLITINVOICESPERACTIVITY CHECK (SPLITINVOICESPERACTIVITY IN ('Y','N')),
  CHARGELOSSFORPOOLITEMS        NUMBER NULL CONSTRAINT ERP_CHK_CHRLOSSFORPOOLITEMS CHECK (CHARGELOSSFORPOOLITEMS IN (0, 1)),
  CHARGELOSSFORUNIQUEPOOLDAYS   NUMBER NULL CONSTRAINT ERP_CHK_CHRLOSSFORUNIQPOOL CHECK (CHARGELOSSFORUNIQUEPOOLDAYS BETWEEN 1 AND 999),
  PAYMENTTERMCODE               VARCHAR2(6) NULL,
  FINANCECHARGEDAYS             NUMBER DEFAULT 0 NULL CONSTRAINT ERP_CHK_FINANCECHARGEDAYS CHECK (FINANCECHARGEDAYS BETWEEN 0 AND 999),
  PUTFINANCECHARGEON            NUMBER DEFAULT 1 NULL CONSTRAINT ERP_CHK_PUTFINANCECHARGEON CHECK (PUTFINANCECHARGEON IN (1, 2)),
  CUSTOMERRELATED               NUMBER NULL CONSTRAINT ERP_CHK_CUSTOMERRELATED CHECK (CUSTOMERRELATED BETWEEN 0 AND 5),
  RELATEDCUSTOMERNUMBER         NUMBER NULL,
  POREQUIRED                    NUMBER NULL CONSTRAINT ERP_CHK_POREQUIRED CHECK (POREQUIRED IN (0, 1)),
  SOILCOUNT                     VARCHAR2(1) NULL CONSTRAINT ERP_CHK_SOILCOUNT CHECK (SOILCOUNT IN ('Y','N')),
  INTERNETORDERING              VARCHAR2(1) NULL CONSTRAINT ERP_CHK_YN_INTERNETORDERING CHECK (INTERNETORDERING IN ('Y','N')),
  CUSTOMGARMENTCHARGE           VARCHAR2(1) NULL CONSTRAINT ERP_I_CNTRCTCMPONENTYN391 CHECK (CUSTOMGARMENTCHARGE IN ('Y','N')),
  MATERIALMAINTENANCEPERCENTAGE NUMBER NULL CONSTRAINT ERP_I_CONTRCOMPBGPROTECT CHECK ((MATERIALMAINTENANCEPERCENTAGE IS NULL) OR (MATERIALMAINTENANCEPERCENTAGE IS NOT NULL AND MATERIALMAINTENANCEPERCENTAGE BETWEEN 0 AND 100)),
  GARMENTSETUP                  VARCHAR2(1) NULL CONSTRAINT ERP_I_CNTRCTCMPONENTYN392 CHECK (GARMENTSETUP IN ('Y', 'N')),
  GARMENTMAINTENANCE            VARCHAR2(1) NULL CONSTRAINT ERP_I_CNTRCTCMPONENTYN393 CHECK (GARMENTMAINTENANCE IN ('Y', 'N')),
  GARMENTLOSS                   VARCHAR2(1) NULL CONSTRAINT ERP_I_CNTRCTCMPONENTYN394 CHECK (GARMENTLOSS IN ('Y', 'N')),
  GARMENTREPAIR                 VARCHAR2(1) NULL CONSTRAINT ERP_I_CNTRCTCMPONENTYN395 CHECK (GARMENTREPAIR IN ('Y', 'N')),
  USEPRODSIZESURCHARGEPRICING   VARCHAR2(1) NULL CONSTRAINT ERP_I_CNTRCTCMPONENTYN396 CHECK (USEPRODSIZESURCHARGEPRICING IN ('Y', 'N')));
  
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.CHARGEREPAIRS
  IS '0 = No Repair allowed and No charge repair 
1 = Repair allowed and No charge repair 
2 = Repair allowed and Charge repair';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.CHARGEREPAIRCOG
  IS '0 = No Repair allowed and No charge repair 
1 = Repair allowed and No charge repair 
2 = Repair allowed and Charge repair';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.VARIABLESERVICES
  IS '0 = No Repair allowed and No charge repair 
1 = Repair allowed and No charge repair 
2 = Repair allowed and Charge repair';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.ALLOWCHARGERETURNRENT
  IS 'Flag that indicates whether return rent should be charged for this customer. Possible values: 0 = Do not charge return rent 1 = Do charge return rent';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.RETURNRENTWEEKS
  IS '0 – 999. This attributes indicates the number of weeks that still rent may be charged after the item has been taken out of circulation.';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.ALLOWCHARGEMINRENT
  IS 'Flag that indicates whether min rent should be charged for this customer. 
Possible values: 0 = Do not charge return rent 1 = Do charge return rent';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.MINRENTWEEKS
  IS '0 – 999. This attribute indicates the minimum number of weeks that rent will be charged for this customer. 
When an item has been taken out of circulation prior to reaching this number of weeks then still rent will be charged (as compensation) during this period.';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.DAYSSPECIALGRADE
  IS '0 – 999';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.GARMENTRENTSPECIFICATION
  IS '0 = Not applicable 1 = Products per Wearer
2 = Products per Wearer Condensed
3 = Wearer totals only
4 = Quantities per Product
5 = Number of men per total price
6 = Wearer function
7 = Wearer Function Fixed Total Price';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.SENDSTATEMENT
  IS '1 = Always, with frequency
2 = Only if invoices are Past Due
3 = Never';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.CHARGELOSSFORPOOLITEMS
  IS '0 = Not applicable / do not charge loss for pool
1 = Charge loss for pool items';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.CHARGELOSSFORUNIQUEPOOLDAYS
  IS '1 – 999';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.PUTFINANCECHARGEON
  IS '1 = Invoice 2 = Statement';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.CUSTOMERRELATED
  IS 'Possible values: 0=Not applicable
1=Related to this customer
2=Related to bill to customer (only valid when bill to customer <> customer)
3=Related to master account (only valid if customer is connected to master account)
4=Related to common customer (only valid if customer is connected to common customer)
5=Related to other customer (only valid if customer is connected to other customer)';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.RELATEDCUSTOMERNUMBER
  IS 'Only filled when CustomerRelated > 1.';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT.POREQUIRED
  IS '0 = PO is not required 1 = PO is required';
  
ALTER TABLE ERP_I_CONTRACTCOMPONENT
  ADD CONSTRAINT PK_ERP_I_CONTRACTCOMPONENT PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XIE1ERP_I_CONTRACTCOMP_CODE ON ERP_I_CONTRACTCOMPONENT (CONTRACTCODE ASC); 
CREATE INDEX XIF1ERP_I_CONTRACTCOMP_CODE ON ERP_I_CONTRACTCOMPONENT (STATEMENTFREQUENCY ASC);
CREATE INDEX XIF2ERP_I_CONTRACTCOMP_CODE ON ERP_I_CONTRACTCOMPONENT (INVOICEFREQUENCY ASC);

ALTER TABLE ERP_I_CONTRACTCOMPONENT
  ADD CONSTRAINT ERP_I_CNTRCTCOMP_STATEMENTFREQ FOREIGN KEY (STATEMENTFREQUENCY)
  REFERENCES INVOICEFREQUENCY (INVOICEFREQUENCY_ID) ON DELETE CASCADE;
ALTER TABLE ERP_I_CONTRACTCOMPONENT
  ADD CONSTRAINT ERP_I_CONTRACTCOMP_INVFREQ FOREIGN KEY (INVOICEFREQUENCY)
  REFERENCES INVOICEFREQUENCY (INVOICEFREQUENCY_ID) ON DELETE CASCADE;

--section break for pack_Script
  
-------------- ERP_I_CONTRACTCOMPONENT_LOG --------------
CREATE TABLE ERP_I_CONTRACTCOMPONENT_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID11 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_CONTRACTCOMPONENT_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRACTCOMPONENT_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';

CREATE INDEX XIE1ERP_I_CONTRACTCOMPONT_LOG ON ERP_I_CONTRACTCOMPONENT_LOG (TIMESTAMP ASC);

ALTER TABLE ERP_I_CONTRACTCOMPONENT_LOG
  ADD CONSTRAINT PK_ERP_I_CONTRACTCOMPONENT_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_CONTRACTCOMPONENT_LOG
  ADD CONSTRAINT FK_ERP_I_CONTRACTCOMPONENT_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CONTRACTCOMPONENT (TRANSFER_ID) ON DELETE CASCADE;

-------------- ERP_I_CONTRACTCUSTOMER --------------  
ALTER TABLE ERP_I_CONTRACTCUSTOMER DROP CONSTRAINT ERP_I_CONTRACTCUSTOM_CNTRUSAGE;
ALTER TABLE ERP_I_CONTRACTCUSTOMER DROP CONSTRAINT ERP_I_CONTRACTCUSTOM_DATECHECK;

CREATE TABLE ERP_I_CONTRACTCUSTOMER
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER(1) DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID12 CHECK (TRANSFER_STATUS IN (-1,0,1)),  
  CONTRACTCODE    VARCHAR2(15) NOT NULL,
  CUSTOMERNUMBER  NUMBER NOT NULL,
  STARTDATE       DATE DEFAULT TRUNC(SYSDATE)-1 NOT NULL,
  ENDDATE         DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  CONTRACTUSAGE   NUMBER DEFAULT 1 NOT NULL CONSTRAINT ERP_I_CONTRACTCUSTOM_CNTRUSAGE CHECK (CONTRACTUSAGE BETWEEN 0 AND 1),
  EXPIRATIONDATE  DATE NULL,
  POSTARTDATE     DATE NULL,
  INSTALLDATE     DATE NULL);
  
COMMENT ON COLUMN ERP_I_CONTRACTCUSTOMER.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRACTCUSTOMER.CONTRACTCODE
  IS 'Unique contract code';
COMMENT ON COLUMN ERP_I_CONTRACTCUSTOMER.CONTRACTUSAGE
  IS 'Must be between 0-1';
COMMENT ON COLUMN ERP_I_CONTRACTCUSTOMER.INSTALLDATE
  IS 'Set when the first invoice is settled (US settlementactive = Y only)'; 
  
ALTER TABLE ERP_I_CONTRACTCUSTOMER
  ADD CONSTRAINT PK_ERP_I_CONTRACTCUSTOMER PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XIE1ERP_I_CONTRACTCUSTOMER ON ERP_I_CONTRACTCUSTOMER (CONTRACTCODE ASC, CUSTOMERNUMBER ASC, TRUNC(STARTDATE) ASC);

ALTER TABLE ERP_I_CONTRACTCUSTOMER
  ADD CONSTRAINT ERP_I_CONTRACTCUSTOM_DATECHECK
  CHECK (ENDDATE >= STARTDATE);

-------------- ERP_I_CONTRACTCUSTOMER_LOG --------------
CREATE TABLE ERP_I_CONTRACTCUSTOMER_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID13 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_CONTRACTCUSTOMER_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRACTCUSTOMER_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';

ALTER TABLE ERP_I_CONTRACTCUSTOMER_LOG
  ADD CONSTRAINT PK_ERP_I_CONTRACTCUSTOMER_LOG PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XIE1ERP_I_CONTRACTCUSTOM_LOG ON ERP_I_CONTRACTCUSTOMER_LOG (TIMESTAMP ASC);

ALTER TABLE ERP_I_CONTRACTCUSTOMER_LOG
  ADD CONSTRAINT FK_ERP_I_CONTRACTCUSTOMER_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CONTRACTCUSTOMER (TRANSFER_ID) ON DELETE CASCADE;  

-------------- ERP_I_CONTRACTPRICELIST --------------
ALTER TABLE ERP_I_CONTRACTPRICELIST DROP CONSTRAINT ERP_I_CNTRPRCLST_GNRPRICETYPE;
ALTER TABLE ERP_I_CONTRACTPRICELIST DROP CONSTRAINT ERP_I_CNTRPRCLST_GNRLPRAUTPRCP;
ALTER TABLE ERP_I_CONTRACTPRICELIST DROP CONSTRAINT ERP_I_CNTRPRCLST_AGREED;
ALTER TABLE ERP_I_CONTRACTPRICELIST DROP CONSTRAINT ERP_I_CNTRPRCLST_PRIORITY;
ALTER TABLE ERP_I_CONTRACTPRICELIST DROP CONSTRAINT ERP_I_CNTRPRCLST_PRICETYPE;
ALTER TABLE ERP_I_CONTRACTPRICELIST DROP CONSTRAINT ERP_I_CNTRPRCLST_DATECHECK;
ALTER TABLE ERP_I_CONTRACTPRICELIST DROP CONSTRAINT ERP_I_CNTRPRCLST_DATECHECK;

CREATE TABLE ERP_I_CONTRACTPRICELIST
( TRANSFER_ID                   NUMBER NOT NULL,
  TRANSFER_STATUS               NUMBER(1) DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID14 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  CONTRACTCODE                  VARCHAR2(15) NOT NULL,
  GENERALPRICELISTCODE          VARCHAR2(15) NOT NULL,
  GENERALPRICETYPE              NUMBER NOT NULL CONSTRAINT ERP_I_CNTRPRCLST_GNRPRICETYPE CHECK (GENERALPRICETYPE BETWEEN 1 AND 12),
  GENERALPRICECURRENCY          VARCHAR2(3) NOT NULL,
  GENERALPRICEINDEXCODE         VARCHAR2(6) NULL,
  GENERALPRICEAUTOPRICEUPDATEYN VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT ERP_I_CNTRPRCLST_GNRLPRAUTPRCP CHECK (GENERALPRICEAUTOPRICEUPDATEYN IN ('Y','N')),
  STARTDATE                     DATE DEFAULT TRUNC(SYSDATE)-1 NOT NULL,
  ENDDATE                       DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  AGREED                        VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT ERP_I_CNTRPRCLST_AGREED CHECK (AGREED IN ('Y','N')),
  USAGE                         NUMBER DEFAULT 0 NOT NULL,
  PRIORITY                      NUMBER DEFAULT 0 NOT NULL CONSTRAINT ERP_I_CNTRPRCLST_PRIORITY CHECK (PRIORITY BETWEEN 0 AND 9999),
  PRICETYPE                     NUMBER DEFAULT 0 NOT NULL CONSTRAINT ERP_I_CNTRPRCLST_PRICETYPE CHECK (PRICETYPE IN (0,1,2,3,4,5,6,7,8,9,10,11)));
  
COMMENT ON COLUMN ERP_I_CONTRACTPRICELIST.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRACTPRICELIST.CONTRACTCODE
  IS 'Link to ERP_I_CONTRACT';
COMMENT ON COLUMN ERP_I_CONTRACTPRICELIST.GENERALPRICELISTCODE
  IS 'Generalpricelist code';
COMMENT ON COLUMN ERP_I_CONTRACTPRICELIST.GENERALPRICETYPE
  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 = Residential rent
12 special size billing';
COMMENT ON COLUMN ERP_I_CONTRACTPRICELIST.GENERALPRICECURRENCY
  IS 'Generalpricelist currency';
COMMENT ON COLUMN ERP_I_CONTRACTPRICELIST.GENERALPRICEINDEXCODE
  IS 'Optional Generalpricelist priceindexcode';
COMMENT ON COLUMN ERP_I_CONTRACTPRICELIST.GENERALPRICEAUTOPRICEUPDATEYN
  IS 'Y,N Default N';
COMMENT ON COLUMN ERP_I_CONTRACTPRICELIST.STARTDATE
  IS 'contractprice list start date';
COMMENT ON COLUMN ERP_I_CONTRACTPRICELIST.ENDDATE
  IS 'contractprice list end date';
COMMENT ON COLUMN ERP_I_CONTRACTPRICELIST.AGREED
  IS 'Y,N Default N';
COMMENT ON COLUMN ERP_I_CONTRACTPRICELIST.USAGE
  IS '0,1 Default 0, 1 is contract only generalpricelist';
COMMENT ON COLUMN ERP_I_CONTRACTPRICELIST.PRICETYPE
  IS '0 = Undefined / inherited 
1 = Wash 
2 = Rent 
3 = Replacement 
4 = Sales 
5 = Weight 
6 = Repair 
7 = Emblem 
8 = Emblem Template 
9 = Garment Insurance 
10 = Special Charge 
11 = Residential rent';
COMMENT ON COLUMN ERP_I_CONTRACTPRICELIST.PRIORITY
  IS 'Must be between 0 and 9999';  
  
ALTER TABLE ERP_I_CONTRACTPRICELIST
  ADD CONSTRAINT PK_ERP_I_CONTRACTPRICELIST PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XIE1ERP_I_CONTRACTPRICELIST ON ERP_I_CONTRACTPRICELIST (CONTRACTCODE ASC,GENERALPRICELISTCODE ASC,GENERALPRICETYPE ASC,TRUNC(STARTDATE) ASC);     

CREATE INDEX XIF1ERP_I_CONTRACTPRICELIST ON ERP_I_CONTRACTPRICELIST (GENERALPRICECURRENCY ASC);

ALTER TABLE ERP_I_CONTRACTPRICELIST
  ADD CONSTRAINT FK_ERP_I_CONTRTPRCLST_CURRENCY FOREIGN KEY (GENERALPRICECURRENCY)
  REFERENCES CURRENCY (CODE);

ALTER TABLE ERP_I_CONTRACTPRICELIST
  ADD CONSTRAINT ERP_I_CNTRPRCLST_DATECHECK
  CHECK (ENDDATE >= STARTDATE);

-------------- ERP_I_CONTRACTPRICELIST_LOG --------------  
CREATE TABLE ERP_I_CONTRACTPRICELIST_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID15 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_CONTRACTPRICELIST_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRACTPRICELIST_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';

ALTER TABLE ERP_I_CONTRACTPRICELIST_LOG
  ADD CONSTRAINT PK_ERP_I_CONTRACTPRICELIST_LOG PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XIE1ERP_I_CONTRPRICELIST_LOG ON ERP_I_CONTRACTPRICELIST_LOG (TIMESTAMP ASC);

ALTER TABLE ERP_I_CONTRACTPRICELIST_LOG
  ADD CONSTRAINT FK_ERP_I_CONTRACTPRICELIST_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CONTRACTPRICELIST (TRANSFER_ID) ON DELETE CASCADE;  

