--ABS-85346
CREATE TABLE ERP_I_INVOICEHEADER
(
  TRANSFER_ID           NUMBER NOT NULL,
  TRANSFER_STATUS       NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_INVHEAD_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  INVOICENUMBER         NUMBER NOT NULL,
  INVOICEDATE           DATE NOT NULL,
  INVOICEBUSINESSUNIT   VARCHAR2(6) NOT NULL,
  BILLTOCUSTOMERNUMBER  NUMBER NOT NULL,
  BILLTOCUSTOMERNAME    VARCHAR2(80) NULL,
  DELTOCUSTOMERNUMBER   NUMBER NULL,
  DELTOCUSTOMERNAME     VARCHAR2(80) NULL, 
  CITY                  VARCHAR2(30) NOT NULL,
  COUNTY                VARCHAR2(20) NULL, 
  STATE                 VARCHAR2(20) NULL, 
  ZIPCODE               VARCHAR2(15) NULL, 
  STREET2               VARCHAR2(30) NULL, 
  STREET                VARCHAR2(30) NULL,  
  EMAILSTATUS           NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_INVHEAD_V2 CHECK (EMAILSTATUS IN (0,1,2,3)),
  EMAILADDRESS          VARCHAR2(300) NULL,
  ROUTENUMBER           NUMBER NULL,
  STOPNUMBER            NUMBER NULL,
  PAYTHISAMOUNT         NUMBER NOT NULL,
  TOTALAMOUNTEXCLVAT    NUMBER NOT NULL,
  ROUNDINGAMOUNT        NUMBER NULL,
  CURRENCYCODE          VARCHAR2(3) NOT NULL,
  NETPAYMENTDAYS        NUMBER DEFAULT 0 NOT NULL,
  PAYMENTDISCOUNTDAYS   NUMBER DEFAULT 0 NOT NULL,
  STARTDATE             DATE NOT NULL,
  ENDDATE               DATE NOT NULL,
  DEPOTCODE             VARCHAR2(12) NULL,
  DELIVERINSTRUCTIONS   VARCHAR2(80) NULL,
  INVOICECOMMENTS       VARCHAR2(300) NULL,
  REFERENCE             NUMBER NULL,
  EXTRANAME1            VARCHAR2(40) NULL,
  EXTRANAME2            VARCHAR2(40) NULL,
  PURCHASEORDER         VARCHAR2(30) NULL,
  CONTACTPERSONNAME     VARCHAR2(90) NULL,
  CONTACTPERSONPHONE    VARCHAR2(30) NULL
);
COMMENT ON COLUMN ERP_I_INVOICEHEADER.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_INVOICEHEADER.EMAILSTATUS
  IS  '0=Not applicable (email address empty) 1=Ready to be send 2=Exported/Send 3=Needs to create PDF';
ALTER TABLE ERP_I_INVOICEHEADER
 ADD CONSTRAINT PK_ERP_I_INVOICEHEADER PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX01_ERP_I_INVOICEHEAD ON ERP_I_INVOICEHEADER(TRANSFER_STATUS);
ALTER TABLE ERP_I_INVOICEHEADER
  ADD CONSTRAINT UK_ERP_I_INVOICEHEADER UNIQUE (TRANSFER_ID, INVOICENUMBER);

CREATE TABLE ERP_I_INVOICELINE
(
  TRANSFER_ID                  NUMBER NOT NULL,
  TRANSFER_STATUS              NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_INVLINE_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  INVOICENUMBER                NUMBER NOT NULL,
  LINENUMBER                   NUMBER NOT NULL,
  INVOICELINETYPE_ID           NUMBER NOT NULL,
  CODE                         VARCHAR2(16) NOT NULL,
  DESCRIPTION                  VARCHAR2(40) NOT NULL,
  AMOUNT                       NUMBER(15,6) NOT NULL,
  DISCOUNTPERCENTAGE           NUMBER(15,2) NOT NULL,
  PRICE                        NUMBER(15,6),
  BILLEDQTY                    NUMBER NOT NULL,
  TAXTYPE                      NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_INVLINE_V2 CHECK (TAXTYPE IN (0,1,2,3)),
  TAXGROUPCODE                 VARCHAR2(6) NULL,
  TURNOVERGROUPCODE            VARCHAR2(12) NULL,
  REMARK                       VARCHAR2(300) NULL,
  INVOICELINESUBTYPE           NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_INVLINE_V3 CHECK (INVOICELINESUBTYPE BETWEEN 0 AND 37),
  BUDGETCODE                   VARCHAR2(6) NULL
);
COMMENT ON COLUMN ERP_I_INVOICELINE.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_INVOICELINE.TAXTYPE
  IS 'US:
0 = Not applicable
1 = Sales
2 = Rent
3 = Miscellaneous
EU
0 = Not applicable
1 = Define tax';
COMMENT ON COLUMN ERP_I_INVOICELINE.INVOICELINESUBTYPE
  IS '0=Not applicable/non garment.
1=Rent by Wearer Item
2=Rent by Wearer Item Condensed
3=Rent by Total Wearer
4=Rent by Product
5=Rent by Wearer Summary
6=Underuse
7=Rent in advance
8=Addition rent for current period
9=Prep Charges
10=Emblem Charges
11=Label Charges
12=Restocking Fee
13=Changes Per Week
14=Rent Deposit Stock
15=Wash COG
16=Loss Credit
19= Garment insurance for repair
20=Garment insurance for loss
21= Garment insurance for repair and loss
22= Garment insurance for residual value
23= Garment insurance for repair and residual value
24= Garment insurance for loss and residual value
25= Garment insurance for loss, repair and residual value
26 = Contract Change
27 = Replacement
28 = Out of circulation
29 = Laundry
30 = Dry Celan
31 = Labels Charged
32 = Labels Free of Charge
33 = Loss charge - plpc inactive
34 = Loss charge - wearerinventory inactive
35 = Loss charge - wearer inactive
36 = Loss charge - replacement
37 = Client Charge';
COMMENT ON COLUMN ERP_I_INVOICELINE.BUDGETCODE
  IS 'Code from Budget. If client or product or department is not in a budget then it must be left empty. Only used for module TEXTILECONTROLLING.';
ALTER TABLE ERP_I_INVOICELINE
  ADD CONSTRAINT PK_ERP_I_INVOICELINE PRIMARY KEY (TRANSFER_ID,INVOICENUMBER,LINENUMBER,CODE);
CREATE INDEX INDX01_ERP_I_INVOICELINE ON ERP_I_INVOICELINE(TRANSFER_STATUS);
ALTER TABLE ERP_I_INVOICELINE
  ADD CONSTRAINT FK_ERP_I_INVOICELINE_LOG FOREIGN KEY (TRANSFER_ID, INVOICENUMBER)
  REFERENCES ERP_I_INVOICEHEADER(TRANSFER_ID, INVOICENUMBER) ON DELETE CASCADE;
ALTER TABLE ERP_I_INVOICELINE
  ADD CONSTRAINT FK_ERP_I_INVOICELINE_TYPE FOREIGN KEY (INVOICELINETYPE_ID)
  REFERENCES INVOICELINETYPE (INVOICELINETYPE_ID);

CREATE TABLE ERP_I_INVOICEHEADER_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_I_INVHEAD_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_INVOICEHEADER_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_INVOICEHEADER_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_INVOICEHEADER_LOG
  ADD CONSTRAINT PK_ERP_I_INVHEADER_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_INVOICEHEADER_LOG
  ADD CONSTRAINT FK_ERP_I_INVHEADER_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_INVOICEHEADER(TRANSFER_ID) ON DELETE CASCADE;