--ABS-73805

-------------- ERP_O_ROUTE_EXPENSES --------------
CREATE TABLE ERP_O_ROUTE_EXPENSES
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID35 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  ROUTENUMBER     NUMBER NOT NULL,
  DRIVINGDATE     DATE NOT NULL,
  DRIVERNUMBER    NUMBER NULL,
  VOUCHERAMOUNT   NUMBER NOT NULL);

COMMENT ON TABLE ERP_O_ROUTE_EXPENSES
  IS 'ROUTECONTROL.VOUCHERAMOUNT EXPORT';
COMMENT ON COLUMN ERP_O_ROUTE_EXPENSES.ROUTENUMBER
  IS 'VIA ROUTECONTROL.ROUTE_ID -> ROUTE.ROUTENUMBER';
COMMENT ON COLUMN ERP_O_ROUTE_EXPENSES.DRIVINGDATE
  IS 'VIA ROUTECONTROL.DRIVINGDATE';
COMMENT ON COLUMN ERP_O_ROUTE_EXPENSES.DRIVERNUMBER
  IS 'VIA ROUTECONTROL.DRIVERNUMBER';
COMMENT ON COLUMN ERP_O_ROUTE_EXPENSES.VOUCHERAMOUNT
  IS 'VIA ROUTECONTROL_ID.VOUCHERAMOUNT';

ALTER TABLE ERP_O_ROUTE_EXPENSES
  ADD CONSTRAINT PK_ERP_O_ROUTE_EXPENSES PRIMARY KEY (TRANSFER_ID);

-------------- ERP_O_ROUTE_EXPENSES_LOG --------------
CREATE TABLE ERP_O_ROUTE_EXPENSES_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID36 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_O_ROUTE_EXPENSES_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_O_ROUTE_EXPENSES_LOG.PROCESS_STATUS
  IS '0 = PROCESSED CORRECTLY, 1 = PROCESS STOPPED WITH ERRORS';

ALTER TABLE ERP_O_ROUTE_EXPENSES_LOG
  ADD CONSTRAINT PK_ERP_O_ROUTE_EXPENSES_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_ROUTE_EXPENSES_LOG
  ADD CONSTRAINT FK_ERP_O_ROUTE_EXPENSES_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_ROUTE_EXPENSES (TRANSFER_ID) ON DELETE CASCADE;
  
-------------- ERP_O_STOCKTRANS_LEDGERAMT --------------
CREATE TABLE ERP_O_STOCKTRANS_LEDGERAMT
( TRANSFER_ID      NUMBER NOT NULL,
  TRANSFER_STATUS  NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID37 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TRANSFERLOADDATE DATE NULL,
  TRANSACTIONDATE  DATE NOT NULL,
  LEGALENTITY      VARCHAR2(10) DEFAULT '005' NOT NULL,
  BUSINESSTYPE     NUMBER DEFAULT 100 NOT NULL,
  BUSINESSUNITCODE VARCHAR2(6) NOT NULL,
  DEPOT            VARCHAR2(12) DEFAULT '0000' NOT NULL,
  DEPARTMENT       VARCHAR2(12) DEFAULT '0000' NOT NULL,
  TRANSACT         NUMBER NOT NULL,
  LEDGERACCOUNT    NUMBER NOT NULL,
  AMOUT_DR         NUMBER NOT NULL,
  AMOUNT_CR        NUMBER NOT NULL,
  STARTDATE        DATE NOT NULL,
  ENDDATE          DATE NOT NULL);

COMMENT ON COLUMN ERP_O_STOCKTRANS_LEDGERAMT.TRANSACTIONDATE
  IS 'STOCKTRANSACTIONDATE';
COMMENT ON COLUMN ERP_O_STOCKTRANS_LEDGERAMT.LEGALENTITY
  IS 'FIXED VALUE 005';
COMMENT ON COLUMN ERP_O_STOCKTRANS_LEDGERAMT.BUSINESSTYPE
  IS 'DEFAULT VALUE IS 100, BUT FOR ISSUE A SPECIAL VALUE IS NEEDED';
COMMENT ON COLUMN ERP_O_STOCKTRANS_LEDGERAMT.DEPOT
  IS 'FIXED VALUE 0000';
COMMENT ON COLUMN ERP_O_STOCKTRANS_LEDGERAMT.DEPARTMENT
  IS 'FIXED VALUE 0000';
COMMENT ON COLUMN ERP_O_STOCKTRANS_LEDGERAMT.STARTDATE
  IS 'EXPORTED INTERVAL START DATE';
COMMENT ON COLUMN ERP_O_STOCKTRANS_LEDGERAMT.ENDDATE
  IS 'EXPORTED INTERVAL END DATE';

ALTER TABLE ERP_O_STOCKTRANS_LEDGERAMT
  ADD CONSTRAINT PK_ERP_O_STOCKTRANS_LEDGERAMT PRIMARY KEY (TRANSFER_ID);

CREATE INDEX XIF1ERP_O_STOCKTRANS_LEDGERAMT ON ERP_O_STOCKTRANS_LEDGERAMT (BUSINESSUNITCODE ASC);

ALTER TABLE ERP_O_STOCKTRANS_LEDGERAMT
  ADD CONSTRAINT FK_ERP_O_STOCKTRANS_LDGRMT_BU FOREIGN KEY (BUSINESSUNITCODE)
  REFERENCES BUSINESSUNIT (CODE);

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

ALTER TABLE ERP_O_STOCKTRANS_LEDGERAMT_LOG
  ADD CONSTRAINT PK_ERP_O_STOCKTRANS_LDGRMT_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_STOCKTRANS_LEDGERAMT_LOG
  ADD CONSTRAINT FK_ERP_O_STOCKTRANS_LDGRMT_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_STOCKTRANS_LEDGERAMT (TRANSFER_ID) ON DELETE CASCADE;

-------------- ERP_O_CONTACTPERSON --------------
ALTER TABLE ERP_O_CONTACTPERSON DROP CONSTRAINT CHK_ERP_O_CONTACTPERS_INVCNTP;
ALTER TABLE ERP_O_CONTACTPERSON DROP CONSTRAINT CHK_ERP_O_CONTACTPERS_ACTIVE;

CREATE TABLE ERP_O_CONTACTPERSON
( TRANSFER_ID               NUMBER NOT NULL,
  TRANSFER_STATUS           NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID39 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  CUSTOMERNUMBER            NUMBER NULL,
  CONTACTPERSONNUMBER       NUMBER NOT NULL,
  CONTACTPERSONFUNCTIONCODE VARCHAR2(6) NOT NULL,
  NAME                      VARCHAR2(40) NOT NULL,
  PHONE                     VARCHAR2(15) NULL,
  EMAIL                     VARCHAR2(40) NULL,
  MOBILEPHONE               VARCHAR2(15) NULL,
  INVOICECONTACTPERSON      VARCHAR2(1) NOT NULL CONSTRAINT CHK_ERP_O_CONTACTPERS_INVCNTP CHECK (INVOICECONTACTPERSON IN ('Y','N')),
  FAX                       VARCHAR2(15) NULL,
  ACTIVE                    VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_ERP_O_CONTACTPERS_ACTIVE CHECK (ACTIVE IN ('Y','N')));

COMMENT ON COLUMN ERP_O_CONTACTPERSON.INVOICECONTACTPERSON
  IS 'THIS CONTACT PERSON INFO WILL BE PRINTED ON INVOICE';

ALTER TABLE ERP_O_CONTACTPERSON
  ADD CONSTRAINT PK_ERP_O_CONTACTPERSON PRIMARY KEY (TRANSFER_ID);
  
-------------- ERP_O_CONTACTPERSON_LOG --------------
CREATE TABLE ERP_O_CONTACTPERSON_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID40 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);

ALTER TABLE ERP_O_CONTACTPERSON_LOG
  ADD CONSTRAINT PK_ERP_O_CONTACTPERSON_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_CONTACTPERSON_LOG
  ADD CONSTRAINT FK_ERP_O_CONTACTPERSON_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_CONTACTPERSON (TRANSFER_ID) ON DELETE CASCADE;

-------------- ERP_O_CUSTOMER --------------
ALTER TABLE ERP_O_CUSTOMER DROP CONSTRAINT CHK_ERP_O_CUSTOMER_ACTIVE;
ALTER TABLE ERP_O_CUSTOMER DROP CONSTRAINT CHK_ERP_O_CUSTOMER_TAXABLE;
ALTER TABLE ERP_O_CUSTOMER DROP CONSTRAINT CHK_ERP_O_CUSTOMER_PAYMENTTYPE;

CREATE TABLE ERP_O_CUSTOMER
( TRANSFER_ID             NUMBER NOT NULL,
  TRANSFER_STATUS         NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID41 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  CUSTOMERNUMBER          NUMBER NOT NULL,
  LEGACYCUSTOMERNUMBER    VARCHAR2(12) NULL,
  CUSTOMERNAME            VARCHAR2(40) NOT NULL,
  LEGALNAME               VARCHAR2(80) NOT NULL,
  BILLTOCUSTOMERNUMBER    NUMBER NOT NULL,
  BUSINESSUNIT            VARCHAR2(6) NOT NULL,
  SEARCHNAME              VARCHAR2(10) NULL,
  DATEACTIVE              DATE NOT NULL,
  DATEINACTIVE            DATE NOT NULL,
  ACTIVE                  VARCHAR2(1) NOT NULL CONSTRAINT CHK_ERP_O_CUSTOMER_ACTIVE CHECK (ACTIVE IN ('Y','N')),
  MAILADDRESS             VARCHAR2(30) NOT NULL,
  MAILADDRESS2            VARCHAR2(30) NULL,
  MAILZIPCODE             VARCHAR2(15) NOT NULL,
  MAILCITY                VARCHAR2(30) NOT NULL,
  COUNTRY                 VARCHAR2(3) NOT NULL,
  EMAIL                   VARCHAR2(300) NULL,
  PHONENO                 VARCHAR2(15) NULL,
  FAXNO                   VARCHAR2(15) NULL,
  LANGUAGECODE            VARCHAR2(5) NOT NULL,
  MARKETSEGMENTCODE       VARCHAR2(6) NOT NULL,
  SALESAREACODE           VARCHAR2(6) NOT NULL,
  CHAINCODE               VARCHAR2(6) NULL,
  MASTERACCOUNTNUMBER     NUMBER NULL,
  REMARK                  VARCHAR2(80) NULL,
  CURRENCYCODE            VARCHAR2(3) NULL,
  COCNUMBER               VARCHAR2(20) NULL,
  PNNUMBER                VARCHAR2(20) NULL,
  CUSTOMERRANKING         VARCHAR2(10) NULL,
  VISITADDRESS            VARCHAR2(30) NOT NULL,
  VISITADDRESS2           VARCHAR2(30) NULL,
  VISITZIPCODE            VARCHAR2(15) NOT NULL,
  VISITCITY               VARCHAR2(30) NOT NULL,
  TAXABLE                 VARCHAR2(1) NULL CONSTRAINT CHK_ERP_O_CUSTOMER_TAXABLE CHECK (TAXABLE IN ('Y','N')),
  VATNUMBER               VARCHAR2(15) NULL,
  SENDINVOICETO           NUMBER NOT NULL,
  EXTRANAME1              VARCHAR2(40) NULL,
  EXTRANAME2              VARCHAR2(40) NULL,
  INVOICEADDRESS          VARCHAR2(30) NULL,
  INVOICEADDRESS2         VARCHAR2(30) NULL,
  INVOICEZIPCODE          VARCHAR2(15) NULL,
  INVOICECITY             VARCHAR2(30) NULL,
  PAYMENTTERMSCODE        VARCHAR2(6) NOT NULL,
  PAYMENTTYPE             NUMBER NULL CONSTRAINT CHK_ERP_O_CUSTOMER_PAYMENTTYPE CHECK (PAYMENTTYPE BETWEEN 0 AND 7),
  ILNNUMBER               NUMBER NULL,
  SALESEMPLOYEE1          NUMBER NULL,
  SALESEMPLOYEE2          NUMBER NULL,
  COVERPAGE_NAME          VARCHAR2(40) NULL,
  COVERPAGE_EXTRANAME1    VARCHAR2(40) NULL,
  COVERPAGE_EXTRANAME2    VARCHAR2(40) NULL,
  COVERPAGE_CONTACTPERSON VARCHAR2(40) NULL,
  COVERPAGE_COUNTRY       VARCHAR2(2) NULL,
  COVERPAGE_ADDRESS       VARCHAR2(30) NULL,
  COVERPAGE_ADDRESS2      VARCHAR2(30) NULL,
  COVERPAGE_ZIPCODE       VARCHAR2(15) NULL,
  COVERPAGE_CITY          VARCHAR2(30) NULL,
  SICCODE                 VARCHAR2(6) NULL,
  DEFAULTINVBU            VARCHAR2(6) NOT NULL,
  VISITSTATE              VARCHAR2(20) NULL,
  INVOICESTATE            VARCHAR2(20) NULL,
  NATIONALACCOUNTCODE     VARCHAR2(6) NULL,
  STATETAXCODE            VARCHAR2(6) NULL,
  COUNTYTAXAUTHORITYCODE  VARCHAR2(12) NULL,
  CITYTAXAUTHORITYCODE    VARCHAR2(12) NULL,
  TAXEXEMPTCODE           VARCHAR2(18) NULL,
  MAILSTATE               VARCHAR2(20) NULL,
  INVOICELAYOUTCODE       VARCHAR2(6) NULL,
  ROUTENUMBER             NUMBER NULL,
  DISTRICT 				  VARCHAR2(6) NULL,
  PO_EXPIRATION_DATE      DATE NULL,
  AANMANINGEN             VARCHAR2(1000) NULL,
  INVOICEFREQUENCY        NUMBER NULL,
  RENTPERIOD              NUMBER NULL,
  BANKACCOUNT             VARCHAR2(30) NULL,
  IBANNO                  VARCHAR2(34) NULL,
  INVOICECONTACTPERSON_EMAIL VARCHAR2(300) NULL,
  CUSTOMERCLUSTER         VARCHAR2(6) NULL,
  CONSOLIDATEDINVOICE     VARCHAR2(1) NULL,
  PURCHASE_ORDER 		  NUMBER NULL);

COMMENT ON COLUMN ERP_O_CUSTOMER.RENTPERIOD
  IS '0 = In A arrear (default)
1 = In Advance. Correct additions only
2 = In Advance. Correct all'; 

ALTER TABLE ERP_O_CUSTOMER
  ADD CONSTRAINT PK_ERP_O_CUSTOMER PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XIE1ERP_O_CUSTOMER ON ERP_O_CUSTOMER (CUSTOMERNUMBER ASC);

CREATE INDEX XIF1ERP_O_CUSTOMER ON ERP_O_CUSTOMER (BUSINESSUNIT ASC);
CREATE INDEX XIF2ERP_O_CUSTOMER ON ERP_O_CUSTOMER (COUNTRY ASC);
CREATE INDEX XIF3ERP_O_CUSTOMER ON ERP_O_CUSTOMER (CURRENCYCODE ASC);
CREATE INDEX XIF4ERP_O_CUSTOMER ON ERP_O_CUSTOMER (DEFAULTINVBU ASC);
CREATE INDEX XIF5ERP_O_CUSTOMER ON ERP_O_CUSTOMER (LANGUAGECODE ASC);

--section break for pack_Script

ALTER TABLE ERP_O_CUSTOMER
  ADD CONSTRAINT FK_ERP_O_CUSTOMER_BU FOREIGN KEY (BUSINESSUNIT)
  REFERENCES BUSINESSUNIT (CODE) ON DELETE CASCADE;
ALTER TABLE ERP_O_CUSTOMER
  ADD CONSTRAINT FK_ERP_O_CUSTOMER_COUNTRY FOREIGN KEY (COUNTRY)
  REFERENCES COUNTRY (CODE) ON DELETE CASCADE;
ALTER TABLE ERP_O_CUSTOMER
  ADD CONSTRAINT FK_ERP_O_CUSTOMER_CURRENCY FOREIGN KEY (CURRENCYCODE)
  REFERENCES CURRENCY (CODE) ON DELETE CASCADE;
ALTER TABLE ERP_O_CUSTOMER
  ADD CONSTRAINT FK_ERP_O_CUSTOMER_INVBU FOREIGN KEY (DEFAULTINVBU)
  REFERENCES BUSINESSUNIT (CODE) ON DELETE CASCADE;
ALTER TABLE ERP_O_CUSTOMER
  ADD CONSTRAINT FK_ERP_O_CUSTOMER_LANGUAGE FOREIGN KEY (LANGUAGECODE)
  REFERENCES LANGUAGES (CODE) ON DELETE CASCADE;

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

ALTER TABLE ERP_O_CUSTOMER_LOG
  ADD CONSTRAINT PK_ERP_O_CUSTOMER_LOG PRIMARY KEY (TRANSFER_ID);
  
ALTER TABLE ERP_O_CUSTOMER_LOG
  ADD CONSTRAINT FK_ERP_O_CUSTOMER_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_CUSTOMER (TRANSFER_ID) ON DELETE CASCADE;

-------------- ERP_I_WORKORDER --------------
CREATE TABLE ERP_I_WORKORDER
( TRANSFER_ID              NUMBER NOT NULL,
  TRANSFER_STATUS          NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID43 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  WORKORDER_ID             NUMBER NULL,
  WORKORDERNUMBER          NUMBER NOT NULL,
  BUSINESSUNIT             VARCHAR2(6) NOT NULL,
  CANCEL_YN                VARCHAR2(1) DEFAULT 'N' NOT NULL);

ALTER TABLE ERP_I_WORKORDER
  ADD CONSTRAINT PK_ERP_I_WORKORDER PRIMARY KEY (TRANSFER_ID);
 
CREATE INDEX XIE1RP_I_WORKORDER ON ERP_I_WORKORDER (BUSINESSUNIT ASC, WORKORDERNUMBER ASC);

-------------- ERP_I_WORKORDERLINE --------------
CREATE TABLE ERP_I_WORKORDERLINE
( TRANSFER_ID              NUMBER NOT NULL,
  TRANSFER_STATUS          NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID44 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  WORKORDERNUMBER          NUMBER NOT NULL,
  WORKORDERLINE_ID         NUMBER NULL,
  QUALITYGRADECODE         VARCHAR2(1) NOT NULL,
  PRODUCTCODE              VARCHAR2(12) NOT NULL,
  SIZECODE                 VARCHAR2(12) NOT NULL,
  QUANTITYCANCELED         NUMBER NOT NULL);

ALTER TABLE ERP_I_WORKORDERLINE
  ADD CONSTRAINT PK_ERP_I_WORKORDERLINE PRIMARY KEY (TRANSFER_ID, WORKORDERNUMBER, QUALITYGRADECODE, PRODUCTCODE, SIZECODE);

CREATE INDEX XIF1ERP_I_WORKORDERLINE ON ERP_I_WORKORDERLINE (TRANSFER_ID ASC);
ALTER TABLE ERP_I_WORKORDERLINE
  ADD CONSTRAINT FK_ERP_I_WORKORDERLINE FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_WORKORDER (TRANSFER_ID) ON DELETE CASCADE;

-------------- ERP_I_WORKORDER_LOG --------------
CREATE TABLE ERP_I_WORKORDER_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID45 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_WORKORDER_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_WORKORDER_LOG.PROCESS_STATUS
  IS '0 = PROCESSED CORRECTLY, 1 = PROCESS STOPPED WITH ERRORS';

ALTER TABLE ERP_I_WORKORDER_LOG
  ADD CONSTRAINT PK_ERP_I_WORKORDER_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_WORKORDER_LOG
  ADD CONSTRAINT FK_ERP_I_WORKORDER_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_WORKORDER (TRANSFER_ID) ON DELETE CASCADE;

-------------- ERP_I_EXCHANGERATE --------------
CREATE TABLE ERP_I_EXCHANGERATE 
( TRANSFER_ID            NUMBER NOT NULL,
  TRANSFER_STATUS        NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID46 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  BASECURRENCY_CODE      VARCHAR2(3) NOT NULL,
  EXCHANGECURRENCY_CODE  VARCHAR2(3) NOT NULL,
  STARTDATE              DATE NOT NULL,
  EXCHANGERATE           NUMBER(15,6) NOT NULL);
  
ALTER TABLE ERP_I_EXCHANGERATE
  ADD CONSTRAINT PK_ERP_I_EXCHANGERATE PRIMARY KEY (TRANSFER_ID);

-------------- ERP_I_EXCHANGERATE_LOG --------------
CREATE TABLE ERP_I_EXCHANGERATE_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID47 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_EXCHANGERATE_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_EXCHANGERATE_LOG.PROCESS_STATUS
  IS '0 = PROCESSED CORRECTLY, 1 = PROCESS STOPPED WITH ERRORS';

ALTER TABLE ERP_I_EXCHANGERATE_LOG
  ADD CONSTRAINT PK_ERP_I_EXCHANGERATE_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_EXCHANGERATE_LOG
  ADD CONSTRAINT FK_ERP_I_EXCHANGERATE_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_EXCHANGERATE (TRANSFER_ID) ON DELETE CASCADE;
  
-------------- ERP_O_INVOICEHEADERCURRENCY --------------
CREATE TABLE ERP_O_INVOICEHEADERCURRENCY
( TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID48 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  INVOICEHEADER_ID    NUMBER NOT NULL,
  INVOICECURRENCYTYPE NUMBER NOT NULL,
  CURRENCYCODE        VARCHAR2(3) NOT NULL,
  EXCHANGERATEDATE    DATE NULL,
  EXCHANGERATE        NUMBER(15,6) NULL,
  CREDITSQUEEZEAMOUNT NUMBER(15,6) NULL,
  ROUNDINGAMOUNT      NUMBER(15,6) NULL,
  PREBILLAMOUNT       NUMBER(15,6) NULL,
  TOTALAMOUNTEXCLVAT  NUMBER(15,6) NULL,
  PAYTHISAMOUNT       NUMBER(15,6) NULL,
  PAIDAMOUNT          NUMBER(28,6) NULL,
  UNDERUSEAMOUNT      NUMBER(15,6) NULL);

ALTER TABLE ERP_O_INVOICEHEADERCURRENCY
  ADD CONSTRAINT PK_ERP_O_INVOICEHEADERCURRENCY PRIMARY KEY (TRANSFER_ID, INVOICEHEADER_ID, INVOICECURRENCYTYPE, CURRENCYCODE);

CREATE INDEX XIF1ERP_O_INVOICEHEADCURRENCY ON ERP_O_INVOICEHEADERCURRENCY (TRANSFER_ID ASC);
ALTER TABLE ERP_O_INVOICEHEADERCURRENCY
  ADD CONSTRAINT FK_ERP_O_INVOICEHEADERCURRENCY FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_INVOICEHEADER (TRANSFER_ID) ON DELETE CASCADE;

-------------- ERP_O_WORKORDER --------------
ALTER TABLE ERP_O_WORKORDER ADD (CUSTOMERNAME VARCHAR2(40) NULL,
                                 SHIPMENTPRIORITY VARCHAR2(30) NULL,
								 CREATEEMPLOYEENAME VARCHAR2(40) NULL,
								 SHIPDIRECT VARCHAR2(40) NULL,
								 ROUTECODE VARCHAR2(6) NULL,
								 LOCKER VARCHAR2(6) NULL);
								 
COMMENT ON COLUMN ERP_O_WORKORDER.SHIPMENTPRIORITY IS 'ORDERPRIORITY_D, FACTORYDESCRIPTION OR WHEN NULL PRIORITYCODE';
COMMENT ON COLUMN ERP_O_WORKORDER.CREATEEMPLOYEENAME IS 'FULLNAME FROM CREATIONUSER_ID';
COMMENT ON COLUMN ERP_O_WORKORDER.SHIPDIRECT IS 'WILL BE LEFT EMPTY!';

-------------- ERP_O_WORKORDERLINEMEBLEM --------------
ALTER TABLE ERP_O_WORKORDERLINEMEBLEM ADD (PLACEMENTCODE VARCHAR2(6));
