----------------------------------------
--create create sequence SEQ_TRANSFER
create sequence SEQ_TRANSFER
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;
----------------------------------------
--table called ERP_I_CUSTOMER
Create table ERP_I_CUSTOMER
(
  TRANSFER_ID              NUMBER not null,
  TRANSFER_STATUS          NUMBER default 0 not null,
  CUSTOMERNUMBER           NUMBER not null,
  CUSTOMERNUMBER_OLD       NUMBER,
  LEGACYCUSTOMERNUMBER     VARCHAR2(12),
  CUSTOMERNAME             VARCHAR2(40) not null,
  LEGALNAME                VARCHAR2(80) not null,
  BILLTOCUSTOMERNUMBER     NUMBER not null,
  BUSINESSUNIT             VARCHAR2(6) not null,
  SEARCHNAME               VARCHAR2(10),
  DATEACTIVE               DATE not null,
  DATEINACTIVE             DATE default to_date('31-12-2099','dd-mm-yyyy') not null,
  ACTIVE                   VARCHAR2(1) not null,
  MAILADDRESS              VARCHAR2(30) not null,
  MAILADDRESS2             VARCHAR2(30),
  MAILZIPCODE              VARCHAR2(15) not null,
  MAILCITY                 VARCHAR2(30) not null,
  COUNTRY                  VARCHAR2(2) not null,
  EMAIL                    VARCHAR2(300),
  PHONENO                  VARCHAR2(15),
  FAXNO                    VARCHAR2(15),
  LANGUAGECODE             VARCHAR2(5) not null,
  MARKETSEGMENTCODE        VARCHAR2(6) not null,
  SALESAREACODE            VARCHAR2(6) not null,
  CHAINCODE                VARCHAR2(6),
  MASTERACCOUNTNUMBER      NUMBER,  
 --- when it is a master account same as customer-number
  REMARK                   VARCHAR2(80),
  CURRENCYCODE             VARCHAR2(3),
  COCNUMBER                VARCHAR2(20),
  PNNUMBER                 VARCHAR2(20),
  CUSTOMERRANKING          VARCHAR2(10),
  VISITADDRESS             VARCHAR2(30) not null,
  VISITADDRESS2            VARCHAR2(30),
  VISITZIPCODE             VARCHAR2(15) not null,
  VISITCITY                VARCHAR2(30) not null,
  TAXABLE                  VARCHAR2(1) default 'Y',
  VATNUMBER                VARCHAR2(15),
  SENDINVOICETO            NUMBER not null,----> ERP will send a customernumber!!!
  EXTRANAME1               VARCHAR2(40),
  EXTRANAME2               VARCHAR2(40),
  INVOICEADDRESS           VARCHAR2(30),
  INVOICEADDRESS2          VARCHAR2(30),
  INVOICEZIPCODE           VARCHAR2(15),
  INVOICECITY              VARCHAR2(30),
  PAYMENTTERMSCODE         VARCHAR2(6) not null,
  PAYMENTTYPE              NUMBER,
  ILNNUMBER                NUMBER,  -----?
  SALESEMPLOYEE1           NUMBER,  --> remark, employee number is unique over all plants!
  SALESEMPLOYEE2           NUMBER,
  COVERPAGE_NAME           VARCHAR2(40),
  COVERPAGE_EXTRANAME1     VARCHAR2(40),
  COVERPAGE_EXTRANAME2     VARCHAR2(40),
  COVERPAGE_CONTACTPERSON  VARCHAR2(40),
  COVERPAGE_COUNTRY        VARCHAR2(2),--->New field in ABSSolute
  COVERPAGE_ADDRESS        VARCHAR2(30),
  COVERPAGE_ADDRESS2       VARCHAR2(30),
  COVERPAGE_ZIPCODE        VARCHAR2(15),
  COVERPAGE_CITY           VARCHAR2(30),
  SICCODE                  VARCHAR2(6)
);
-- Add comments to the columns 
comment on column ERP_I_CUSTOMER.SENDINVOICETO
  is 'ERP will send the number of the customer, 
customer number = sendinvoice to
 then 1, otherwise 0, 
see options
0 = Del to customer
1 = Bill to customer
2 = Pay-by Customer->not used in the interface!';
comment on column ERP_I_CUSTOMER.PAYMENTTYPE
  is '0=Cash
1=Check
2=Credit Card
3=EFT / Bank (=default)
4=EDI
5=Accept Giro
6=Automatic Collection
7=Via Pay by Customer';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_CUSTOMER
  add constraint PK_ERP_I_CUSTOMER primary key (TRANSFER_ID);
alter table ERP_I_CUSTOMER
  add constraint FK_ERP_I_CUSTOMER_BU foreign key (BUSINESSUNIT)
  references BUSINESSUNIT (CODE) on delete cascade;
alter table ERP_I_CUSTOMER
  add constraint FK_ERP_I_CUSTOMER_country foreign key (COUNTRY)
  references country (CODE) on delete cascade;  
alter table ERP_I_CUSTOMER
  add constraint FK_ERP_I_CUSTOMER_CURRENCY foreign key (CURRENCYCODE)
  references currency (CODE) on delete cascade;  
-- Create/Recreate indexes 
create index INDX_ERP_I_CUSTOMER_CUST on ERP_I_CUSTOMER (CUSTOMERNUMBER);
create index INDX_ERP_I_CUSTOMER_CUSTOLD on ERP_I_CUSTOMER (CUSTOMERNUMBER_OLD);

--Create table ERP_I_CUSTOMER_LOG
create table ERP_I_CUSTOMER_LOG
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_I_CUSTOMER_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_I_CUSTOMER_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_CUSTOMER_LOG
  add constraint PK_ERP_I_CUSTOMER_LOG primary key (TRANSFER_ID);
alter table ERP_I_CUSTOMER_LOG
  add constraint FK_ERP_I_CUSTOMER_LOG foreign key (TRANSFER_ID)
  references ERP_I_CUSTOMER (TRANSFER_ID) on delete cascade;

----------------------------------------
--Create table ERP_I_COSTCENTER
Create table ERP_I_COSTCENTER
(
  TRANSFER_ID               NUMBER not null,
  TRANSFER_STATUS           NUMBER default 0 not null,
  CUSTOMERNUMBER            NUMBER not null,
  COSTCENTERCODE            VARCHAR2(12) not null,
  COSTCENTERCODE_OLD        VARCHAR2(12),
  DESCRIPTION               VARCHAR2(30) not null,
  EXPORTEDI                 NUMBER default 0,
  INVOICEADDRESS            VARCHAR2(30),
  INVOICEADDRESS2           VARCHAR2(30),
  INVOICEZIPCODE            VARCHAR2(15),
  INVOICECITY               VARCHAR2(30)
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_COSTCENTER
  add constraint PK_ERP_I_COSTCENTER primary key (TRANSFER_ID);
-- Create/Recreate indexes 
create index INDX_ERP_I_COSTCENTER on ERP_I_COSTCENTER (CUSTOMERNUMBER, COSTCENTERCODE);
create index INDX_ERP_I_COSTCENTEROLD on ERP_I_COSTCENTER (CUSTOMERNUMBER, COSTCENTERCODE_OLD);

--Create table ERP_I_COSTCENTER_LOG
create table ERP_I_COSTCENTER_LOG
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_I_COSTCENTER_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_I_COSTCENTER_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_COSTCENTER_LOG
  add constraint PK_ERP_I_COSTCENTER_LOG primary key (TRANSFER_ID);
alter table ERP_I_COSTCENTER_LOG
  add constraint FK_ERP_I_COSTCENTER_LOG foreign key (TRANSFER_ID)
  references ERP_I_COSTCENTER (TRANSFER_ID) on delete cascade;

----------------------------------------
--Create table ERP_I_DELIVERYPOINT
create table ERP_I_DELIVERYPOINT
(
  TRANSFER_ID       NUMBER not null,
  TRANSFER_STATUS   NUMBER default 0 not null,
  CUSTOMERNUMBER    NUMBER not null,
  DELIVERYPOINTCODE VARCHAR2(12) not null,
  DELIVERYPOINTCODE_OLD VARCHAR2(12),
  DESCRIPTION       VARCHAR2(30) not null,
  ADDRESS           VARCHAR2(30) not null,
  ADDRESS2          VARCHAR2(30) not null,
  ZIPCODE           VARCHAR2(15) not null,
  CITY              VARCHAR2(30) not null
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_DELIVERYPOINT
  add constraint PK_ERP_I_DELIVERYPOINT primary key (TRANSFER_ID);
-- Create/Recreate indexes 
create index INDX_ERP_I_DELIVERYPOINT on ERP_I_DELIVERYPOINT (CUSTOMERNUMBER, DELIVERYPOINTCODE);
create index INDX_ERP_I_DELIVERYPOINTOLD on ERP_I_DELIVERYPOINT (CUSTOMERNUMBER, DELIVERYPOINTCODE_OLD);

--Create table ERP_I_DELIVERYPOINT_LOG
create table ERP_I_DELIVERYPOINT_LOG
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_I_DELIVERYPOINT_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_I_DELIVERYPOINT_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_DELIVERYPOINT_LOG
  add constraint PK_ERP_I_DELIVERYPOINT_LOG primary key (TRANSFER_ID);
alter table ERP_I_DELIVERYPOINT_LOG
  add constraint FK_ERP_I_DELIVERYPOINT_LOG foreign key (TRANSFER_ID)
  references ERP_I_DELIVERYPOINT (TRANSFER_ID) on delete cascade;

----------------------------------------
--Create table ERP_I_DEPARTMENT
create table ERP_I_DEPARTMENT
(
  TRANSFER_ID      NUMBER not null,
  TRANSFER_STATUS  NUMBER default 0 not null,
  CUSTOMERNUMBER   NUMBER not null,
  DEPARTMENTNUMBER NUMBER not null,
  DEPARTMENTNUMBER_OLD NUMBER,
  DESCRIPTION      VARCHAR2(40) not null,
  COSTCENTERCODE   VARCHAR2(12) not null
)
tablespace ABS_DATA;
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_DEPARTMENT
  add constraint PK_ERP_I_DEPARTMENT primary key (TRANSFER_ID);
-- Create/Recreate indexes 
create index INDX_ERP_I_DEPARTMENT on ERP_I_DEPARTMENT (CUSTOMERNUMBER, DEPARTMENTNUMBER);
create index INDX_ERP_I_DEPARTMENTOLD on ERP_I_DEPARTMENT (CUSTOMERNUMBER, DEPARTMENTNUMBER_OLD);

--Create table ERP_I_DEPARTMENT_LOG
create table ERP_I_DEPARTMENT_LOG
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_I_DEPARTMENT_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_I_DEPARTMENT_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_DEPARTMENT_LOG
  add constraint PK_ERP_I_DEPARTMENT_LOG primary key (TRANSFER_ID);
alter table ERP_I_DEPARTMENT_LOG
  add constraint FK_ERP_I_DEPARTMENT_LOG foreign key (TRANSFER_ID)
  references ERP_I_DEPARTMENT (TRANSFER_ID) on delete cascade;

----------------------------------------
--Create table ERP_I_CONSUMPTIONPOINT
Create table ERP_I_CONSUMPTIONPOINT
(
  TRANSFER_ID            NUMBER not null,
  TRANSFER_STATUS        NUMBER default 0 not null,
  CUSTOMERNUMBER         NUMBER not null,
  DEPARTMENTNUMBER       NUMBER not null,
  CONSUMPTIONPOINTNUMBER NUMBER not null,
  CONSUMPTIONPOINTNUMBER_OLD NUMBER,
  LEGACYDISTRIBUTIONPOINTNUMBER VARCHAR2(6),
  DESCRIPTION            VARCHAR2(30) not null,
  DELIVERYPOINTCODE      VARCHAR2(12) not null,
  COSTCENTERCODE         VARCHAR2(12) not null,
  ACTIVE                 VARCHAR2(1) default 'Y' not null
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_CONSUMPTIONPOINT
  add constraint PK_ERP_I_CONSUMPTIONPOINT primary key (TRANSFER_ID);
create index INDX_ERP_I_CONSUMPTIONPOINT on ERP_I_CONSUMPTIONPOINT (CUSTOMERNUMBER, DEPARTMENTNUMBER, CONSUMPTIONPOINTNUMBER);
create index INDX_ERP_CONSUMPTIONPNTOLD on ERP_I_CONSUMPTIONPOINT (CUSTOMERNUMBER, DEPARTMENTNUMBER, CONSUMPTIONPOINTNUMBER_OLD);

--create table  ERP_I_CONSUMPTIONPOINT_LOG
Create table ERP_I_CONSUMPTIONPOINT_LOG
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_I_CONSUMPTIONPOINT_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_I_CONSUMPTIONPOINT_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_CONSUMPTIONPOINT_LOG
  add constraint PK_ERP_I_CONSUMPTIONPOINT_LOG primary key (TRANSFER_ID);
alter table ERP_I_CONSUMPTIONPOINT_LOG
  add constraint FK_ERP_I_CONSUMPTIONPOINT_LOG foreign key (TRANSFER_ID)
  references ERP_I_CONSUMPTIONPOINT (TRANSFER_ID) on delete cascade;

---------------------------------------- 
-- Create table ERP_I_SUPPLIER
create table ERP_I_SUPPLIER
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  SUPPLIERNUMBER  NUMBER not null,
  NAME            VARCHAR2(40) not null,
  SUPPLIERTYPE    NUMBER default 0 not null,
  PHONE           VARCHAR2(15),
  FAX             VARCHAR2(15),
  EMAIL           VARCHAR2(40),
  WEBSITE         VARCHAR2(40),
  STREET          VARCHAR2(30),
  STREET2         VARCHAR2(30),
  ZIPCODE         VARCHAR2(15) not null,
  CITY            VARCHAR2(30) not null,
  COUNTRY         VARCHAR2(3) not null,
  CURRENCYCODE    VARCHAR2(3) not null,
  ORDERFORMREMARK VARCHAR2(80),
  PAYMENTTERMS    VARCHAR2(80),
  DELIVERYTERMS   VARCHAR2(80),
  DATEACTIVE      DATE not null,
  DATEINACTIVE    DATE default to_date('31-12-2099','dd-mm-yyyy') not null
);
-- Add comments to the columns 
comment on column ERP_I_SUPPLIER.SUPPLIERTYPE
  is '0 = External, 1 = Internal (stockroom)';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_SUPPLIER
  add constraint PK_ERP_I_SUPPLIER primary key (TRANSFER_ID);
alter table ERP_I_SUPPLIER
  add constraint FK_ERP_I_SUPPLIER_COUNTRY foreign key (COUNTRY)
  references COUNTRY (CODE) on delete cascade;
alter table ERP_I_SUPPLIER
  add constraint FK_ERP_I_SUPPLIER_CURRENCY foreign key (CURRENCYCODE)
  references CURRENCY (CODE) on delete cascade;
-- Create/Recreate check constraints 
alter table ERP_I_SUPPLIER
  add constraint CHK_ERP_I_SUPPLIER_TYPE
  check (SUPPLIERTYPE in (0,1));  
-- Create/Recreate indexes 
create index INDX_ERP_I_SUPPLIER on ERP_I_SUPPLIER (SUPPLIERNUMBER);

--create table  ERP_I_SUPPLIER_LOG
create table ERP_I_SUPPLIER_LOG
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_I_SUPPLIER_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_I_SUPPLIER_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_SUPPLIER_LOG
  add constraint PK_ERP_I_SUPPLIER_LOG primary key (TRANSFER_ID);
alter table ERP_I_SUPPLIER_LOG
  add constraint FK_ERP_I_SUPPLIER_LOG foreign key (TRANSFER_ID)
  references ERP_I_SUPPLIER (TRANSFER_ID) on delete cascade;

----------------------------------------
-- Create table ERP_I_SIZE
create table ERP_I_SIZE
(
  TRANSFER_ID                  NUMBER not null,
  TRANSFER_STATUS              NUMBER default 0 not null,
  SIZECODE                     VARCHAR2(12) not null,
  SIZECODE_OLD                 VARCHAR2(12),
  LEGACYSIZECODE               VARCHAR2(6),
  DESCRIPTION                  VARCHAR2(30),
  WIDTH                        VARCHAR2(6),
  LENGTH                       VARCHAR2(6),
  UNFINISHEDLENGTH             VARCHAR2(1) default 'N' not null,
  UNFINISHEDSIZEDEFINITIONCODE VARCHAR2(12),
  RESERVEALTERNATIVESIZE       VARCHAR2(1) default 'N' not null,
  USEDFOREMBLEM                VARCHAR2(1) default 'N' not null
);
-- Add comments to the columns 
comment on column ERP_I_SIZE.UNFINISHEDLENGTH
  is 'If set to Y the the length of this size is not defined yet.
This flag is only valid if for the sizetype a splitsize is used.
When set to Y a unfinishedsize must be chosen';
comment on column ERP_I_SIZE.UNFINISHEDSIZEDEFINITIONCODE
  is 'Link to the same size table, must exist as size!
Must be filled when UNFINISHEDLENGTH=Y';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_SIZE
  add constraint PK_ERP_I_SIZE primary key (TRANSFER_ID);
-- Create/Recreate check constraints 
alter table ERP_I_SIZE
  add constraint CHK_ERP_I_SIZE_RESERVALTRNSIZE
  check (RESERVEALTERNATIVESIZE IN ('Y','N'));
alter table ERP_I_SIZE
  add constraint CHK_ERP_I_SIZE_UNFINISHLENGT
  check (UNFINISHEDLENGTH IN ('Y','N'));
alter table ERP_I_SIZE
  add constraint CHK_ERP_I_SIZE_USEDFOREMBLEM
  check (USEDFOREMBLEM IN ('Y','N'));
-- Create/Recreate indexes 
create index INDX_ERP_I_SIZE on ERP_I_SIZE (SIZECODE);

-- Create table ERP_I_SIZE_LOG
create table ERP_I_SIZE_LOG
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_I_SIZE_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_I_SIZE_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_SIZE_LOG
  add constraint PK_ERP_I_SIZE_LOG primary key (TRANSFER_ID);
alter table ERP_I_SIZE_LOG
  add constraint FK_ERP_I_SIZE_LOG foreign key (TRANSFER_ID)
  references ERP_I_SIZE (TRANSFER_ID) on delete cascade;
    
----------------------------------------
-- Create table ERP_I_PRODUCT
create table ERP_I_PRODUCT
(
  TRANSFER_ID                NUMBER not null,
  TRANSFER_STATUS            NUMBER default 0 not null,
  CODE                       VARCHAR2(12) not null,
  CODE_OLD                   VARCHAR2(12),
  ACTIVE                     VARCHAR2(1) default 'Y' not null,
  LEGACYPRODUCTCODE          VARCHAR2(12),
  DESCRIPTION                VARCHAR2(30) not null,
  SHORTDESCRIPTION           VARCHAR2(6),
  LONGDESCRIPTION            VARCHAR2(60) not null,
  REGISTRATIONUNITCODE       VARCHAR2(3) not null,
  TYPEOFMERCHANDISE          NUMBER,
  PRODUCTGROUPCODE           VARCHAR2(6),
  WEIGHINGCATEGORYCODE       VARCHAR2(6),
  COLORCODE                  VARCHAR2(12) not null,
  FABRICCODE                 VARCHAR2(12) not null,
  WASHINGPROCESSCODE         VARCHAR2(6) not null,
  SUPPLIERNUMBER             NUMBER not null,
  TAXGROUPCODE               VARCHAR2(6) not null,
  EXCHANGEMETHOD             NUMBER not null,
  SOILCOUNTMETHOD            NUMBER not null,
  FINISHINGCODE              VARCHAR2(6),
  PUTUPTYPE                  NUMBER not null,
  RECORDPASSIVESTOCK         VARCHAR2(1) default 'N' not null,
  OCCUPANCYRELATED           VARCHAR2(1) default 'N' not null,
  UNIQUEPOOLITEM             VARCHAR2(1) default 'N' not null,
  DEFAULTRETURNGRADECODE     VARCHAR2(1),
  SIZETYPECODE               VARCHAR2(6) not null,
  SPECIALSIZE                VARCHAR2(1) default 'N' not null,
  ALTERNATIVESIZERESERVATION VARCHAR2(1) default 'N' not null,
  PACKINGAMOUNT              NUMBER(15,6) default 1 not null,
  THEORETICALWEIGHT          NUMBER(15,6),
  LOTNUMBERREQUIREDYN        VARCHAR2(1),
  PACKTYPE                   NUMBER(1) default 0 not null,
  ORPACKCONTENTSALLOWED      NUMBER(1) default 0 not null,
  ORPACK_STERILE             NUMBER(1) default 0 not null,
  ORPACK_TREATMENTTYPECODE   VARCHAR2(12),
  ORPACK_EXPIRATIONCODE      VARCHAR2(6),
  ORPACK_MINACTIVESTOCKLEVEL NUMBER(4),
  ORPACK_ASSEMBLYLABELLAYOUT VARCHAR2(6)
);
-- Add comments to the columns 
comment on column ERP_I_PRODUCT.ACTIVE
  is 'Values ''Y'',''N''';
comment on column ERP_I_PRODUCT.TYPEOFMERCHANDISE
  is '1=linen
2=garments
3=sales only items
4=emblems
5=residential/domestic';
comment on column ERP_I_PRODUCT.EXCHANGEMETHOD
  is '1 = Delayed even exchange
2 = Straight Even Exchange
3 = Delivery Based on Order/Standards';
comment on column ERP_I_PRODUCT.SOILCOUNTMETHOD
  is '0=no soil count,
1=manual,
2=auto return assumed,
3=weight based';
comment on column ERP_I_PRODUCT.PUTUPTYPE
  is '1 = Put up per customer (Packingnote)
2 = Bulk put up
3 = Bundle tag
4 = Bulk put up scan per route
5 = Packer - Loose linen';
comment on column ERP_I_PRODUCT.OCCUPANCYRELATED
  is 'Usage related to the occupancy of department';
comment on column ERP_I_PRODUCT.UNIQUEPOOLITEM
  is 'means ALLOW Unique pool';
comment on column ERP_I_PRODUCT.DEFAULTRETURNGRADECODE
  is 'Default return qualitygrade, must exist in qualitygrade table';
comment on column ERP_I_PRODUCT.SPECIALSIZE
  is 'Allow Y/N special sizes';
comment on column ERP_I_PRODUCT.PACKINGAMOUNT
  is 'Bundle quantity';
comment on column ERP_I_PRODUCT.PACKTYPE
  is '0 = No pack
1 = Non-OR Pack
2 = OR Pack, remark field PRODUCT.PACK=''Y'' when <> 0 else ''N'' when No pack.
If the product is OR-Pack,
Then the product must have the check box Unique Pool Allowed selected (tab Logistic)!';
comment on column ERP_I_PRODUCT.ORPACKCONTENTSALLOWED
  is '0 = Not allowed as pack contents
1 = Non-sterile pack contents allowed
2 = Sterile pack contents allowed
3 = Both sterile and non-sterile pack contents allowed.';
comment on column ERP_I_PRODUCT.ORPACK_STERILE
  is '0 = Not sterile
1 = Sterile';
comment on column ERP_I_PRODUCT.ORPACK_ASSEMBLYLABELLAYOUT
  is 'Link to table labellayout via code with label type=4!';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_PRODUCT
  add constraint PK_ERP_I_PRODUCT primary key (TRANSFER_ID);
alter table ERP_I_PRODUCT
  add constraint PK_ERP_I_PRODUCT_FK unique (TRANSFER_ID, CODE);  
-- Create/Recreate check constraints 
alter table ERP_I_PRODUCT
  add constraint CHK_ERP_I_PRODUCT_ACTIVE
  check (ACTIVE IN ('Y','N'));
alter table ERP_I_PRODUCT
  add constraint CHK_ERP_I_PRODUCT_ALTRNATVSZR
  check (ALTERNATIVESIZERESERVATION IN ('Y','N'));
alter table ERP_I_PRODUCT
  add constraint CHK_ERP_I_PRODUCT_EXCHANGMET
  check (EXCHANGEMETHOD BETWEEN 1 AND 3);
alter table ERP_I_PRODUCT
  add constraint CHK_ERP_I_PRODUCT_OCCUPANCYRE
  check (OCCUPANCYRELATED IN ('Y','N'));
alter table ERP_I_PRODUCT
  add constraint CHK_ERP_I_PRODUCT_ORPACK_STER
  check (ORPACK_STERILE BETWEEN 0 AND 1);
alter table ERP_I_PRODUCT
  add constraint CHK_ERP_I_PRODUCT_ORPCKCNTAL
  check (ORPACKCONTENTSALLOWED BETWEEN 0 AND 3);
alter table ERP_I_PRODUCT
  add constraint CHK_ERP_I_PRODUCT_PACKTYPE
  check (PACKTYPE BETWEEN 0 AND 2);
alter table ERP_I_PRODUCT
  add constraint CHK_ERP_I_PRODUCT_PUTUPTYPE
  check (PUTUPTYPE BETWEEN 1 AND 5);
alter table ERP_I_PRODUCT
  add constraint CHK_ERP_I_PRODUCT_RECORDPASIV
  check (RECORDPASSIVESTOCK IN ('Y','N'));
alter table ERP_I_PRODUCT
  add constraint CHK_ERP_I_PRODUCT_SOILCOUNT
  check (SOILCOUNTMETHOD in (0,1,2,3));
alter table ERP_I_PRODUCT
  add constraint CHK_ERP_I_PRODUCT_SPECIALSIZE
  check (SPECIALSIZE IN ('Y','N'));
alter table ERP_I_PRODUCT
  add constraint CHK_ERP_I_PRODUCT_TYPEOMERCH
  check (TYPEOFMERCHANDISE BETWEEN 1 AND 5);
alter table ERP_I_PRODUCT
  add constraint CHK_ERP_I_PRODUCT_UNIQUEPOOL
  check (UNIQUEPOOLITEM IN ('Y','N'));
-- Create/Recreate indexes 
create index INDX_ERP_I_PRODUCT_CODE on ERP_I_PRODUCT (CODE);
create index INDX_ERP_I_PRODUCT_OLD on ERP_I_PRODUCT (CODE_OLD);

-- Every product should have at least 1 size.
-- Create table ERP_I_PRODUCTSIZE
create table ERP_I_PRODUCTSIZE
(
  TRANSFER_ID          NUMBER not null,
  TRANSFER_STATUS      NUMBER default 0 not null,
  PRODUCTCODE          VARCHAR2(12) not null,
  SIZECODE             VARCHAR2(12) not null,
  SEQUENCENUMBER       NUMBER not null,
  SPECIALSIZE          VARCHAR2(1) default 'N' not null,
  PACKINGAMOUNT        NUMBER(15,6) default 1 not null,
  THEORETICALWEIGHT    NUMBER(15,6) default 0 not null,
  LEGACYCODE           VARCHAR2(12),
  SQUAREMETERS         NUMBER(8,3),
  DEFAULTSTOCKROOMCODE VARCHAR2(12),
  FIXEDSTOCKROOM       VARCHAR2(1) default 'N' not null
);
-- Add comments to the columns 
comment on column ERP_I_PRODUCTSIZE.SEQUENCENUMBER
  is 'Sizes for product will be displayed on the screen in this order, MUST BE UNIQUE per product';
comment on column ERP_I_PRODUCTSIZE.SPECIALSIZE
  is 'Is a special size (very large or very small)';
comment on column ERP_I_PRODUCTSIZE.DEFAULTSTOCKROOMCODE
  is 'must exist as a stockroom code when used!';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_PRODUCTSIZE
  add constraint PK_ERP_I_PRODUCTSIZE primary key (TRANSFER_ID, PRODUCTCODE, SIZECODE);
alter table ERP_I_PRODUCTSIZE
  add constraint FK_ERP_I_PRODUCTSIZE_STOCKROOM foreign key (DEFAULTSTOCKROOMCODE)
  references STOCKROOM (CODE) on delete cascade;
alter table ERP_I_PRODUCTSIZE
  add constraint FK_ERP_I_PRODUCTSIZE foreign key (TRANSFER_ID, PRODUCTCODE)
  references ERP_I_PRODUCT (TRANSFER_ID, CODE) on delete cascade;
-- Create/Recreate check constraints 
alter table ERP_I_PRODUCTSIZE
  add constraint CHK_ERP_I_PRODUCTSIZE_FIXSTOC
  check (FIXEDSTOCKROOM IN ('Y','N'));
alter table ERP_I_PRODUCTSIZE
  add constraint CHK_ERP_I_PRODUCTSIZE_SPECIAL
  check (SPECIALSIZE IN ('Y','N'));
-- Create/Recreate indexes 
create index INDX_ERP_I_PRODUCTSIZE on ERP_I_PRODUCTSIZE (PRODUCTCODE, SIZECODE);

-- Every product should have at least 1 finishingmethod.
-- Create table ERP_I_PRODUCTFINISHINGMETHOD
create table ERP_I_PRODUCTFINISHINGMETHOD
(
  TRANSFER_ID         NUMBER not null,
  TRANSFER_STATUS     NUMBER default 0 not null,
  PRODUCTCODE         VARCHAR2(12) not null,
  FINISHINGMETHODCODE VARCHAR2(6) not null,
  EXPIRATIONDAYS      NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_I_PRODUCTFINISHINGMETHOD.PRODUCTCODE
  is 'Product code, also in ERP_I_PRODUCT.CODE';
comment on column ERP_I_PRODUCTFINISHINGMETHOD.FINISHINGMETHODCODE
  is 'In ABSSolute link to Finishingmethod.code';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_PRODUCTFINISHINGMETHOD
  add constraint PK_ERP_I_PRODUCTFINISHINGMETHD primary key (TRANSFER_ID, PRODUCTCODE, FINISHINGMETHODCODE);
alter table ERP_I_PRODUCTFINISHINGMETHOD
  add constraint FK_ERP_I_PRODUCTFINISHINGMETHD foreign key (TRANSFER_ID, PRODUCTCODE)
  references ERP_I_PRODUCT (TRANSFER_ID, CODE) on delete cascade;
-- Create/Recreate indexes 
create index INDX_ERP_I_PRODUCTFINISHNGM on ERP_I_PRODUCTFINISHINGMETHOD (PRODUCTCODE, FINISHINGMETHODCODE);

-- Create table ERP_I_PRODUCT_LOG
create table ERP_I_PRODUCT_LOG
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_I_PRODUCT_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_I_PRODUCT_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_PRODUCT_LOG
  add constraint PK_ERP_I_PRODUCT_LOG primary key (TRANSFER_ID);
alter table ERP_I_PRODUCT_LOG
  add constraint FK_ERP_I_PRODUCT_LOG foreign key (TRANSFER_ID)
  references ERP_I_PRODUCT (TRANSFER_ID) on delete cascade;

----------------------------------------
-- Create table ERP_I_WEIGHINGCATEGORY
create table ERP_I_WEIGHINGCATEGORY
(
  TRANSFER_ID                NUMBER not null,
  TRANSFER_STATUS            NUMBER default 0 not null,
  WEIGHINGCATEGORYCODE       VARCHAR2(6) not null,
  WEIGHINGCATEGORYCODE_OLD   VARCHAR2(6),
  DESCRIPTION                VARCHAR2(30) not null,
  ACTIVE                     VARCHAR2(1) default 'Y' not null,
  REWASH                     VARCHAR2(1) default 'N' not null,
  LEGACYWEIGHINGCATEGORYCODE VARCHAR2(6),
  TURNOVERGROUPCODE          VARCHAR2(6),
  ACTIVITYCODE               VARCHAR2(6) not null,
  RESIDENTIALLAUNDRY         VARCHAR2(1) default 'N' not null
);
-- Add comments to the columns 
comment on column ERP_I_WEIGHINGCATEGORY.REWASH
  is 'For recording rewash';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_WEIGHINGCATEGORY
  add constraint PK_ERP_WEIGHINGCAT primary key (TRANSFER_ID);
-- Create/Recreate check constraints 
alter table ERP_I_WEIGHINGCATEGORY
  add constraint CHK_ERP_WEIGHING_ACTIVE
  check (ACTIVE IN ('Y', 'N'));
alter table ERP_I_WEIGHINGCATEGORY
  add constraint CHK_ERP_WEIGHING_RESIDENT
  check (RESIDENTIALLAUNDRY IN ('Y', 'N'));
alter table ERP_I_WEIGHINGCATEGORY
  add constraint CHK_ERP_WEIGHING_REWASH
  check (REWASH IN ('Y', 'N'));
-- Create/Recreate indexes 
create index INDX_ERP_WEIGHINGCAT on ERP_I_WEIGHINGCATEGORY (WEIGHINGCATEGORYCODE);
create index INDX_ERP_WEIGHINGCATOLD on ERP_I_WEIGHINGCATEGORY (WEIGHINGCATEGORYCODE_OLD);

-- Create table ERP_I_WEIGHINGCATEGORY_LOG
create table ERP_I_WEIGHINGCATEGORY_LOG
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_I_WEIGHINGCATEGORY_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_I_WEIGHINGCATEGORY_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_WEIGHINGCATEGORY_LOG
  add constraint PK_ERP_I_WEIGHINGCATEGORY_LOG primary key (TRANSFER_ID);
alter table ERP_I_WEIGHINGCATEGORY_LOG
  add constraint FK_ERP_I_WEIGHINGCATEGORY_LOG foreign key (TRANSFER_ID)
  references ERP_I_WEIGHINGCATEGORY (TRANSFER_ID) on delete cascade;

----------------------------------------
-- Create table ERP_O_WORKORDER
create table ERP_O_WORKORDER
(
  TRANSFER_ID              NUMBER not null,
  TRANSFER_STATUS          NUMBER default 0 not null,
  WORKORDER_ID             NUMBER not null,  --> will be filled by ABS
  WORKORDERNUMBER          NUMBER not null,
  BUSINESSUNIT             VARCHAR2(6) not null,
  INVOICEBUSINESSUNIT      VARCHAR2(6),
  DEFAULTBUSINESSUNIT      VARCHAR2(6) not null,  --> default business unit customer
  MARKETSEGMENTCODE        VARCHAR2(6) not null,  --> from customer
  ORIGIN                   NUMBER not null,
  PROCESSINGBU  	         VARCHAR(6), 
  PROCESSINGSTOCKROOM_CODE VARCHAR2(12),
  STATUS                   NUMBER not null,
  REFERENCENUMBER          VARCHAR2(15),
  WORKORDERREFERENCENO     NUMBER,  
  REQUESTDATE              DATE not null,
  ENTRYDATE                DATE not null,
  NEEDEDDATE               DATE not null,
  EXPECTEDCOMPLETIONDATE   DATE not null,
  COMPLETIONDATE           DATE default to_date('31-12-2099','dd-mm-yyyy') not null,
  ALLOWEDPHASEDDELIVERY    VARCHAR2(1) default 'N' not null,
  CUSTOMERNUMBER           NUMBER,
  DEPARTMENTNUMBER       NUMBER not null,
  CONSUMPTIONPOINTNUMBER NUMBER not null,
  REMARK                   VARCHAR2(80)
);
-- Add comments to the columns 
comment on column ERP_O_WORKORDER.ORIGIN
  is '1 = Generated from wearer inventory
2 = Manual created work order
3 = Created by web module
4 = Created by route assistant
5 = Conversion
6 = Sales note
7 = Credit note
8 = Internal Purchase Order
9 = Stock Delivery Order
10 = Mounting Order
11 = Dismounting Order';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_WORKORDER
  add constraint PK_ERP_O_WORKORDER primary key (TRANSFER_ID);
-- Create/Recreate check constraints 
alter table ERP_O_WORKORDER
  add constraint CHK_ERP_O_WORKORDER_ALLPHASED
  check (ALLOWEDPHASEDDELIVERY in ('Y','N'));
alter table ERP_O_WORKORDER
  add constraint CHK_ERP_O_WORKORDER_ORIGIN
  check (ORIGIN between 1 and 11);
alter table ERP_O_WORKORDER
  add constraint CHK_ERP_O_WORKORDER_STATUS
  check (STATUS in ('05',
'10',
'15',
'20',
'25',
'30',
'40'
));

--section break for pack_Script

-- Create/Recreate indexes 
create index INDX_ERP_O_WORKORDER1 on ERP_O_WORKORDER (BUSINESSUNIT, WORKORDERNUMBER);

-- Create table ERP_O_WORKORDERLINE
create table ERP_O_WORKORDERLINE
(
  TRANSFER_ID         NUMBER not null,
  TRANSFER_STATUS     NUMBER default 0 not null,
  WORKORDERNUMBER     NUMBER not null,
  WORKORDER_ID        NUMBER not null,  --> will be filled by ABS
  WORKORDERLINE_ID    NUMBER not null,  -- will be filled by ABS
  QUALITYGRADECODE    VARCHAR2(1) not null,
  PRODUCTCODE         VARCHAR2(12) not null,
  SIZECODE            VARCHAR2(12) not null,
  FINISHINGMETHODCODE VARCHAR2(6) not null,
  REASONCODE          VARCHAR2(6) not null,
  CODEUNIQUELY        VARCHAR2(1) not null,
  PURCHASEORDERLINE_ID NUMBER,
  QUANTITYORDERED     NUMBER not null,
  QUANTITYFILLED      NUMBER not null,
  QUANTITYPEDINGORDER NUMBER not null,
  QUANTITYCANCELED    NUMBER not null,
  COMPLETIONDATE      DATE default to_date('31-12-2099','dd-mm-yyyy') not null,
  INSTRUCTIONS        VARCHAR2(80),
  SALESITEM           VARCHAR2(1) not null
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_WORKORDERLINE
  add constraint PK_ERP_O_WORKORDERLINE primary key (TRANSFER_ID, WORKORDERLINE_ID);
-- Create/Recreate check constraints 
alter table ERP_O_WORKORDERLINE
  add constraint ERP_O_WORKORDERLINE_CODEUNIQUE
  check (CODEUNIQUELY in ('Y','N'));
alter table ERP_O_WORKORDERLINE
  add constraint ERP_O_WORKORDERLINE_SALESITEM
  check (SALESITEM  in ('Y','N'));
-- Create/Recreate indexes 
create index INDX_ERP_O_WORKORDERLINE_1 on ERP_O_WORKORDERLINE (WORKORDERNUMBER, QUALITYGRADECODE, PRODUCTCODE, SIZECODE, FINISHINGMETHODCODE);

-- Create table ERP_O_WORKORDERLINERES
create table ERP_O_WORKORDERLINERES
(
  TRANSFER_ID         NUMBER not null,
  TRANSFER_STATUS     NUMBER default 0 not null,
  WORKORDERNUMBER     NUMBER not null,
  WORKORDER_ID        NUMBER not null,
  WORKORDERLINE_ID    NUMBER not null,
  SEQUENCENUMBER      NUMBER default 0 not null,
  STOCKTYPE           NUMBER,
  QUALITYGRADECODE    VARCHAR2(1) not null,
  PRODUCTCODE         VARCHAR2(12) not null,
  SIZECODE            VARCHAR2(12) not null,
  FINISHINGMETHODCODE VARCHAR2(6) not null,
  QUANTITY            NUMBER
);
-- Add comments to the columns 
comment on column ERP_O_WORKORDERLINERES.PRODUCTCODE
  is 'Product from stockreservation per grade/stockdefinition per item';
comment on column ERP_O_WORKORDERLINERES.SIZECODE
  is 'Same as product via stock table';
comment on column ERP_O_WORKORDERLINERES.FINISHINGMETHODCODE
  is 'Same as product via stock table';
comment on column ERP_O_WORKORDERLINERES.STOCKTYPE
  is '1 = Not Unique
2 = Unique
3 = Unique With Modifications';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_WORKORDERLINERES
  add constraint PK_ERP_O_ERP_O_WORKORDERLRES primary key (TRANSFER_ID, WORKORDERLINE_ID, SEQUENCENUMBER);
-- Create/Recreate check constraints 
alter table ERP_O_WORKORDERLINERES
  add constraint ERP_O_WORKORDERLRES_STOCKTYP
  check (STOCKTYPE in (1,2,3));
-- Create/Recreate indexes 
create index INDX_ERP_O_WORKORDERLINERES_1 on ERP_O_WORKORDERLINERES (WORKORDERNUMBER, SEQUENCENUMBER, QUALITYGRADECODE, PRODUCTCODE, SIZECODE, FINISHINGMETHODCODE);

-- Create table ERP_O_WORKORDERLINEMEBLEM
create table ERP_O_WORKORDERLINEMEBLEM
(
  TRANSFER_ID          NUMBER not null,
  TRANSFER_STATUS      NUMBER default 0 not null,
  WORKORDERNUMBER      NUMBER not null,
  WORKORDER_ID         NUMBER not null,
  WORKORDERLINE_ID     NUMBER not null,
  WORKORDERLINEEMBLEM_ID NUMBER not null,
  PRODUCTCODE          VARCHAR2(12) not null,
  SIZECODE             VARCHAR2(12) not null,
  QTYREQUESTED         NUMBER not null,
  QTYISSUED            NUMBER not null,
  QTYRESERVED          NUMBER,  
  PURCHASEORDERLINE_ID NUMBER not null
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_WORKORDERLINEMEBLEM
  add constraint PK_ERP_ERP_O_WORKORDERLINEMEBL primary key (TRANSFER_ID, WORKORDERLINEEMBLEM_ID);
alter table ERP_O_WORKORDERLINEMEBLEM
  add constraint FK_ERP_ERP_O_WORKORDERLINEMEBL foreign key (TRANSFER_ID)
  references ERP_O_WORKORDER (TRANSFER_ID) on delete cascade;
create index INDX_FK_ERP_O_WORKORDERLNMEBL on ERP_O_WORKORDERLINEMEBLEM (WORKORDERNUMBER, PRODUCTCODE, SIZECODE);

-- Create table ERP_O_WORKORDER_LOG
create table ERP_O_WORKORDER_LOG
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_O_WORKORDER_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_O_WORKORDER_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_WORKORDER_LOG
  add constraint PK_ERP_O_WORKORDER_LOG primary key (TRANSFER_ID);
alter table ERP_O_WORKORDER_LOG
  add constraint FK_ERP_O_WORKORDER_LOG foreign key (TRANSFER_ID)
  references ERP_O_WORKORDER (TRANSFER_ID) on delete cascade;

----------------------------------------
-- Create table ERP_O_PACKINGNOTE
create table ERP_O_PACKINGNOTE
(
  TRANSFER_ID             NUMBER not null,
  TRANSFER_STATUS         NUMBER default 0 not null,
  PACKINGNOTE_ID          NUMBER not null, -- be filled by ABS
  NOTENUMBER              NUMBER not null,
  SEQUENCENUMBER          NUMBER not null,
  STATUS                  NUMBER not null,
  PRODUCTIONDATE          DATE not null,
  DELIVERYDATE            DATE not null,
  CUSTOMERNUMBER          NUMBER not null,
  DEPARTMENTNUMBER        NUMBER not null,
  CONSUMPTIONPOINTNUMBER  NUMBER not null,
  DELIVERYPOINTCODE       VARCHAR2(12) not null,
  PROCESSBUSINESSUNIT     VARCHAR2(6) not null,
  INTERNALPROCESSBUSINESSUNIT VARCHAR2(6),
  DEADLINE                DATE,
  INVOICEBUSINESSUNIT     VARCHAR2(6) not null,
  ORIGINALDELIVERYDATE    DATE not null,
  ENDDATEFORUSE           DATE not null,
  ORIGIN                  NUMBER not null,
  STOPNUMBER              NUMBER,
  CORRECTEDNOTENUMBER     NUMBER default 0 not null,
  CREATIONTIMESTAMP       DATE default sysdate,
  REFERENCENUMBER         VARCHAR2(30),
  RETURNDATELOAN          DATE,
  FREEOFCHARGEREASONCODE  VARCHAR2(6),
  TOTALAMOUNT             NUMBER(15,2) default 0 not null,
  TOTALAMOUNTEXCLUDINGVAT NUMBER(15,2) default 0 not null,
  DELIVERYREMARK          VARCHAR2(1) default 'N' not null,
  REMARK                  VARCHAR2(1000),
  PROCESSEDTIMESTAMP      DATE
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_PACKINGNOTE
  add constraint PK_ERP_O_PACKINGNOTE primary key (TRANSFER_ID);
-- Create/Recreate indexes 
create index INDX_ERP_O_PACKINGNOTE_NOTE on ERP_O_PACKINGNOTE (NOTENUMBER);

-- Create table ERP_O_PACKINGNOTELINE
create table ERP_O_PACKINGNOTELINE
(
  TRANSFER_ID            NUMBER not null,
  TRANSFER_STATUS        NUMBER default 0 not null,
  PACKINGNOTE_ID         NUMBER not null, -- will be filled by ABS
  PACKINGNOTELINE_ID     NUMBER not null, -- will be filled by ABS
  NOTENUMBER             NUMBER not null,
  PACKINGSEQUENCE        NUMBER not null,
  ORIGIN                 NUMBER not null,
  PRODUCTCODE            VARCHAR2(12) not null,
  SIZECODE               VARCHAR2(12) not null,
  FINISHINGMETHODCODE    VARCHAR2(6) not null,
  LOCATION               VARCHAR2(6),
  CUSTOMCODE             VARCHAR2(12),
  CUSTOMDESCRIPTION      VARCHAR2(30),
  DELIVEREDAMOUNT        NUMBER(15,6) not null,
  STANDARDAMOUNT         NUMBER(15,6) not null,
  VARIATIONAMOUNT        NUMBER(15,6) not null,
  TOBEDELIVEREDAMOUNT    NUMBER(15,6) not null,
  REPLACEMENTQTY         NUMBER not null,
  AUTOREPLACEMENTQTY     NUMBER not null,
  UPDATERENTQTY          NUMBER not null,
  DELIVEREDCOGQTY        NUMBER default 0 not null,
  BUNDLEQTY              NUMBER default 0 not null,
  NOCHARGEFIRSTISSUECOG  NUMBER default 0 not null,
  SCANNEDBUNDLES         NUMBER default 0 not null,
  ADJUSTMENTREASONCODE   VARCHAR2(6),
  FREEOFCHARGEREASONCODE VARCHAR2(6),
  PRICE                  NUMBER,
  PRICECOG               NUMBER default 0,
  DISCOUNT               NUMBER(15,6) not null,
  AMOUNT                 NUMBER(15,2) default 0 not null,
  TAXGROUPCODE           VARCHAR2(6),
  NODELIVERYAMOUNT       NUMBER(4) default 0 not null,
  NOCHARGEPREVREJECTED   NUMBER default 0 not null,
  NOCHARGEPREVSHORTAGE   NUMBER default 0 not null,
  DELIVEREDQTYFROMSTOCK  NUMBER(15,6),
  DELIVEREDSALESQTY      NUMBER(15,6)
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_PACKINGNOTELINE
  add constraint PK_ERP_O_PACKINGNOTELINE primary key (TRANSFER_ID, PACKINGNOTELINE_ID);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_PACKINGNOTELINE
  add constraint fk_ERP_O_PACKINGNOTELINE foreign key (TRANSFER_ID)
  references ERP_o_packingnote (TRANSFER_ID) on delete cascade;

-- Create table ERP_O_PACKINGNOTE_LOG
create table ERP_O_PACKINGNOTE_LOG
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_O_PACKINGNOTE_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_O_PACKINGNOTE_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_PACKINGNOTE_LOG
  add constraint PK_ERP_O_PACKINGNOTE_LOG primary key (TRANSFER_ID);
alter table ERP_O_PACKINGNOTE_LOG
  add constraint FK_ERP_O_PACKINGNOTE_LOG foreign key (TRANSFER_ID)
  references ERP_O_PACKINGNOTE (TRANSFER_ID) on delete cascade;
  
----------------------------------------
-- Create table ERP_O_STOCKTRANSACTION
create table ERP_O_STOCKTRANSACTION
(
  TRANSFER_ID               NUMBER not null,
  TRANSFER_STATUS           NUMBER not null,
  STOCKTRANSACTION_ID       NUMBER not null,
  STOCKROOMCODE             VARCHAR2(12) not null,
  STOCKROOM_BU       	    VARCHAR2(12), 
  TRANSACTIONDATE           DATE not null,
  TRANSACTIONTYPE_ID        NUMBER not null,
  REASONCODE                VARCHAR2(6),
  UNIQUESTOCK               VARCHAR2(1),
  FROMSTOCKROOMCODE         VARCHAR2(12),
  FROMSTOCKROOM_BU          VARCHAR2(12), 
  FROMLOCATIONTYPECODE      VARCHAR2(12),
  TOSTOCKROOMCODE           VARCHAR2(12),
  TOSTOCKROOM_BU            VARCHAR2(12), 
  TOLOCATIONTYPECODE        VARCHAR2(12),
  DOCUMENTTYPE              VARCHAR2(2),
  DOCUMENTNUMBER            NUMBER,
  DOCUMENT_LINE_ID          NUMBER,
  PRODUCTCODE               VARCHAR2(12),
  SIZECODE                  VARCHAR2(12),
  CUSTOMERNUMBER            NUMBER,
  DEFAULTBUSINESSUNIT       VARCHAR2(6),
  CUSTOMEROWNED             VARCHAR2(1),
  QUALITYGRADECODE          VARCHAR2(1),
  QUANTITY                  NUMBER not null,
  PONUMBER                  NUMBER,
  SUPPLIERDELIVERYNOTE      NUMBER
);
-- Add comments to the columns 
comment on column ERP_O_STOCKTRANSACTION.STOCKTRANSACTION_ID
  is 'ABS internal ID';
comment on column ERP_O_STOCKTRANSACTION.TRANSACTIONTYPE_ID
  is 'Linked to a common list';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_STOCKTRANSACTION
  add constraint PK_ERP_O_STOCKTRANSACTION primary key (TRANSFER_ID);
-- Create/Recreate indexes 
create index INDX_ERP_O_STOCKTRANSACTION_1 on ERP_O_STOCKTRANSACTION (TRANSFER_STATUS, STOCKROOMCODE, TRANSACTIONDATE, TRANSACTIONTYPE_ID);

-- Create table ERP_O_STOCKTRANSACTION_LOG
create table ERP_O_STOCKTRANSACTION_LOG
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_O_STOCKTRANSACTION_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_O_STOCKTRANSACTION_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_STOCKTRANSACTION_LOG
  add constraint PK_ERP_O_STOCKTRANSACTION_LOG primary key (TRANSFER_ID);
alter table ERP_O_STOCKTRANSACTION_LOG
  add constraint FK_ERP_O_STOCKTRANSACTION_LOG foreign key (TRANSFER_ID)
  references ERP_O_STOCKTRANSACTION (TRANSFER_ID) on delete cascade;

----------------------------------------
-- Create table  ERP_O_INVOICEHEADER
create table ERP_O_INVOICEHEADER
(
  TRANSFER_ID             NUMBER not null,
  TRANSFER_STATUS         NUMBER default 0 not null,
  INVOICEDATE             DATE not null,
  INVOICEHEADER_ID        NUMBER not null,
  PARENTINVOICENUMBER     NUMBER, 
  INTERFACEINVOICETYPE    NUMBER, 
  INVOICENUMBER           NUMBER not null,
  INVOICETYPE             NUMBER,
  DOCUMENTTYPE            NUMBER default 1 not null,
  INVOICEFREQUENCY_ID     NUMBER,
  INVOICEBUSINESSUNITCODE VARCHAR2(6) not null,
  BILLTOCUSTOMERNUMBER    NUMBER not null,
  DELTOCUSTOMERNUMBER     NUMBER,
  STARTWEEK               NUMBER not null,
  ENDWEEK                 NUMBER not null,
  STARTMONTH              NUMBER not null,
  ENDMONTH                NUMBER not null,
  STARTYEAR               NUMBER not null,
  ENDYEAR                 NUMBER not null,
  STARTDATE               DATE,
  ENDDATE                 DATE,
  INVOICEYEAR             NUMBER not null,
  INVOICEWEEK             NUMBER not null,
  INVOICEDAYNUMBER        NUMBER not null,
  STREET                  VARCHAR2(30),
  STREET2                 VARCHAR2(30),
  ZIPCODE                 VARCHAR2(15) not null,
  CITY                    VARCHAR2(30) not null,
  EMAILADDRESS            VARCHAR2(40),
  CONTACTPERSONNAME       VARCHAR2(40),
  SPLITUPCODE             VARCHAR2(12),
  SPLITUPDESCRIPTION      VARCHAR2(30),
  DELTOSTREET             VARCHAR2(30),
  DELTOSTREET2            VARCHAR2(30),
  DELTOZIPCODE            VARCHAR2(15),
  DELTOCITY               VARCHAR2(30),
  CURRENCYCODE            VARCHAR2(3),
  CREDITED                NUMBER,
  CREDITEDINVOICENO       NUMBER,
  TOTALAMOUNTEXCLVAT      NUMBER(15,6) not null,
  TOTALMINUSADJUSTMENT    NUMBER(15,6),
  TOTALPLUSADJUSTMENT     NUMBER(15,6),
  PREBILLAMOUNT           NUMBER(15,6),
  PAIDAMOUNT              NUMBER(28,6),
  ROUNDINGAMOUNT          NUMBER(15,6) not null,
  PAYTHISAMOUNT           NUMBER(15,6),
  CASHCOLLECTED           NUMBER(15,6),
  UNDERUSEAMOUNT          NUMBER default 0 not null,
  NETPAYMENTDAYS          NUMBER,
  PAYMENTDISCOUNTDAYS     NUMBER(15,2),
  CREDITSQUEEZEAMOUNT     NUMBER(15,6) not null,
  CREDITSQUEEZEPERC       NUMBER(15,2) not null,
  INVOICECOMMENTS         VARCHAR2(300),
  EXPORTED                VARCHAR2(1),
  NEXTPERIODSTARTWEEK     NUMBER default 0 not null,
  NEXTPERIODENDWEEK       NUMBER default 0 not null,
  PACKINGNOTENUMBERLIST   VARCHAR2(1000)
);
-- Add comments to the columns 
comment on column ERP_O_INVOICEHEADER.INVOICEHEADER_ID
  is 'Unique ABS related ID';
comment on column ERP_O_INVOICEHEADER.PARENTINVOICENUMBER
  is 'related to PARENTINVOICEHEADER_ID NUMBER, --> For a debtor invoice it is a self reference. 
For a del to customer invoice put on a debtor invoice is the link to the debtor invoice. 
For any normal invoices is null';
comment on column ERP_O_INVOICEHEADER.INTERFACEINVOICETYPE
  is '0 standard invoices, 1 child invoices, 2 consolidated invoices. 
This can be determined via the parentinvoiceheader_id as well. 
Will be used in ERP for sorting, because the child invoices must be read first';
comment on column ERP_O_INVOICEHEADER.INVOICETYPE
  is '1=Generated
2=Manual
4=Additional Generated
5=Lost Customer
6=Corrections Invoice';
comment on column ERP_O_INVOICEHEADER.DOCUMENTTYPE
  is '1=Invoice, 2=Statement, 3=Client invoice';
comment on column ERP_O_INVOICEHEADER.CREDITED
  is '0 = invoice not credited (default value)
1 = invoice credited without restoring of services or cut off dates
2 = invoice credited with restoring of services and cut off dates (only for periodical generated and additional generated invoices)';
comment on column ERP_O_INVOICEHEADER.ROUNDINGAMOUNT
  is 'Can only be not zero when system setting
ROUNDTOTALAMNTINV = Y
and the number of invoice decimals for the currency of the invoice is 2';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_INVOICEHEADER
  add constraint PK_ERP_O_INVOICEHEADER primary key (TRANSFER_ID);
-- Create/Recreate indexes 
create index INDX_ERP_O_INVOICHEADER_1 on ERP_O_INVOICEHEADER (INVOICENUMBER);
create index INDX_ERP_O_INVOICHEADER_2 on ERP_O_INVOICEHEADER (PARENTINVOICENUMBER);
create index INDX_ERP_O_INVOICHEADER_3 on ERP_O_INVOICEHEADER (INTERFACEINVOICETYPE);

-- Create table ERP_O_INVOICELINE
create table ERP_O_INVOICELINE
(
  TRANSFER_ID          NUMBER not null,
  TRANSFER_STATUS      NUMBER default 0 not null,
  INVOICEHEADER_ID     NUMBER not null, --link to invoiceheader
  INVOICELINE_ID       NUMBER not null,
  INVOICENUMBER        NUMBER not null,
  LINENUMBER           NUMBER not null,
  INVOICELINETYPE_ID   NUMBER not null,
  INVOICELINESUBTYPE   NUMBER,
  CODE                 VARCHAR2(12),
  DESCRIPTION          VARCHAR2(40),
  PRICE                NUMBER(15,6),
  BILLEDQTY            NUMBER,
  AMOUNT               NUMBER(15,6),
  DISCOUNTPERCENTAGE   NUMBER(15,2),
  TAXTYPE              NUMBER,
  TAXGROUPCODE         VARCHAR2(6),
  REMARK               VARCHAR2(300),
  GENERATED            VARCHAR2(1),
  CREDIT               VARCHAR2(1),
  SUBJECTTOMINIMUM     VARCHAR2(1),
  ADJUSTMENTPERMITED   VARCHAR2(1),
  ADJUSTMENTREASONCODE VARCHAR2(6),
  ADJUSTEDPRICE        NUMBER(15,6),
  ADJUSTMENTBILLEDQTY  NUMBER,
  ADJUSTEDAMOUNT       NUMBER(15,6),
  PRODUCTCODE          VARCHAR2(12),
  SIZECODE             VARCHAR2(12),
  FINISHINGMETHODCODE  VARCHAR2(6),
  WEARERNUMBER         NUMBER,
  WEARERNAME           VARCHAR2(40),
  TURNOVERGROUPCODE    VARCHAR2(12),
  PRICEFACTOR          NUMBER,
  DISCOUNTAMOUNT       NUMBER(15,6),
  WEIGHINGCATEGORYCODE VARCHAR2(6)
);
-- Add comments to the columns 
comment on column ERP_O_INVOICELINE.INVOICEHEADER_ID
  is 'Unique ABS related ID';
comment on column ERP_O_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_O_INVOICELINE.TAXTYPE
  is 'US:
0 = Not applicable
1 = Sales
2 = Rent
3 = Miscellaneous
EU
0 = Not applicable
1 = Define tax';
comment on column ERP_O_INVOICELINE.PRODUCTCODE
  is 'From invoicelinedetail, it is empty when it is not a product';
comment on column ERP_O_INVOICELINE.SIZECODE
  is 'From invoicelinedetail, it is empty when it is not a product';
comment on column ERP_O_INVOICELINE.FINISHINGMETHODCODE
  is 'From invoicelinedetail, it is empty when it is not a product';
comment on column ERP_O_INVOICELINE.WEARERNUMBER
  is 'From invoicelinedetail, filled when its for a wearer';
comment on column ERP_O_INVOICELINE.WEARERNAME
  is 'From invoicelinedetail, filled when its for a wearer';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_INVOICELINE
  add constraint PK_ERP_O_INVOICELINE primary key (TRANSFER_ID, INVOICELINE_ID);
-- Create/Recreate indexes 
create index INDX_ERP_O_INVOICELINE_1 on ERP_O_INVOICELINE (INVOICENUMBER, LINENUMBER);

-- Create table
create table ERP_O_INVOICE_LOG
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_O_INVOICE_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_O_INVOICE_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_INVOICE_LOG
  add constraint PK_ERP_O_INVOICE_LOG primary key (TRANSFER_ID);
alter table ERP_O_INVOICE_LOG
  add constraint FK_ERP_O_INVOICE_LOG foreign key (TRANSFER_ID)
  references ERP_O_INVOICEHEADER (TRANSFER_ID) on delete cascade;

----------------------------------------
-- Create table ERP_I_PURCHASEORDERHEADER
create table ERP_I_PURCHASEORDERHEADER
(
  TRANSFER_ID            NUMBER not null,
  TRANSFER_STATUS        NUMBER default 0 not null,
  PURCHASEORDERHEADER_ID NUMBER not null,
  PURCHASEORDERNUMBER    NUMBER not null,
  SUPPLIERNUMBER         NUMBER not null,
  BUSINESSUNITCODE       VARCHAR2(6) not null,
  STOCKROOMCODE          VARCHAR2(12),
  ORDERDATE              DATE not null,
  REQUESTEDDATE          DATE not null,
  STATUS                 NUMBER,
  ORDERTYPE              NUMBER not null,
  PURCHASEORDERTYPE      NUMBER not null,
  ORIGIN                 NUMBER not null,
  PRIORITY               NUMBER not null,
  CONFIRMEDDATE          DATE not null,
  CUSTOMERNUMBER         NUMBER,
  APPROVALDATE           DATE,
  CURRENCYCODE           VARCHAR2(3) not null,
  CANCELDATE             DATE,
  ORDERAMOUNT            NUMBER(15,6) not null,
  REMARK                 VARCHAR2(300),
  PROCESSEDTOWORKORDER   VARCHAR2(1) default 'N' not null
);
-- Add comments to the columns 
comment on column ERP_I_PURCHASEORDERHEADER.STATUS
  is '5  = Waiting for approval
10 = New (approved)
20 = Send
25 = Confirmed
30 = Partly received
40 = Completed
50 = CancelledStatus
type PO (purchase order)';
comment on column ERP_I_PURCHASEORDERHEADER.ORDERTYPE
  is '0 = Manual / no AC / not applicable
1 = Personalized order
2 = Standard order';
comment on column ERP_I_PURCHASEORDERHEADER.PURCHASEORDERTYPE
  is '0 = External
1 = Internal';
comment on column ERP_I_PURCHASEORDERHEADER.ORIGIN
  is '1 = Purchase request
2 = Manual created
3 = Conversion
4 = Work order approval
5 = Work order status control';
comment on column ERP_I_PURCHASEORDERHEADER.PRIORITY
  is '1 = Replacement purchase order
2 = Others
3 = Direct orders';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_PURCHASEORDERHEADER
  add constraint PK_ERP_I_PURCHASEORDERHEADER primary key (TRANSFER_ID);
-- Create/Recreate check constraints 
alter table ERP_I_PURCHASEORDERHEADER
  add constraint CHK_ERP_I_PURCHASEORDERHEAD_OI
  check (ORIGIN between 1 and 5);
alter table ERP_I_PURCHASEORDERHEADER
  add constraint CHK_ERP_I_PURCHASEORDERHEAD_OR
  check (ORDERTYPE IN (0,1,2));
alter table ERP_I_PURCHASEORDERHEADER
  add constraint CHK_ERP_I_PURCHASEORDERHEAD_OT
  check (PURCHASEORDERTYPE IN (0,1));
alter table ERP_I_PURCHASEORDERHEADER
  add constraint CHK_ERP_I_PURCHASEORDERHEAD_PR
  check (PRIORITY in (1,2,3));
alter table ERP_I_PURCHASEORDERHEADER
  add constraint CHK_ERP_I_PURCHASEORDERHEAD_ST
  check (STATUS IN (5, 10, 20, 25, 30, 40, 50));
-- Create/Recreate indexes 
create index INDX_ERP_I_PURCHASEORDERHEADER on ERP_I_PURCHASEORDERHEADER (PURCHASEORDERNUMBER);

--section break for pack_Script
-- Create table ERP_I_PURCHASEORDERLINE
create table ERP_I_PURCHASEORDERLINE
(
  TRANSFER_ID                 NUMBER not null,
  TRANSFER_STATUS             NUMBER default 0 not null,
  EXT_PURCHASEORDERLINE_ID    NUMBER not null, 
  PURCHASEORDERNUMBER         NUMBER not null,
  PRODUCTCODE                 VARCHAR2(12) not null,
  SIZECODE                    VARCHAR2(12) not null,
  REQUESTEDGRADE              VARCHAR2(1) not null,
  QUANTITYORDERED             NUMBER,
  QUANTITYCANCELLED           NUMBER,
  QUANTITYCONFIRMED           NUMBER,
  PURCHASEPRICE               NUMBER(15,6),
  CONFIRMEDDATE               date,
  REMARK                      VARCHAR2(300)
);
-- Add comments to the columns 
comment on column ERP_I_PURCHASEORDERLINE.PRODUCTCODE
  is 'Link to product code';
comment on column ERP_I_PURCHASEORDERLINE.SIZECODE
  is 'Link to size code';
comment on column ERP_I_PURCHASEORDERLINE.REQUESTEDGRADE
  is 'Link to qualitygrade code';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_PURCHASEORDERLINE
  add constraint PK_ERP_I_PURCHASEORDERLINE primary key (TRANSFER_ID, EXT_PURCHASEORDERLINE_ID);
alter table ERP_I_PURCHASEORDERLINE
  add constraint FK_ERP_I_PURCHASEORDERLINE foreign key (TRANSFER_ID)
  references ERP_I_PURCHASEORDERheader (TRANSFER_ID) on delete cascade;
-- Create/Recreate indexes 
create index INDX_ERP_I_PURCHASEORDERLINE on ERP_I_PURCHASEORDERLINE (PURCHASEORDERNUMBER, PRODUCTCODE, SIZECODE, REQUESTEDGRADE);

-- Create table
create table ERP_I_PURCHASEORDER_WO_LINK
(
  TRANSFER_ID              NUMBER not null,
  TRANSFER_STATUS          NUMBER default 0 not null,
  EXT_PURCHASEORDERLINE_ID NUMBER not null,
  WORKORDERLINE_ID         NUMBER not null,
  WORKORDERLINEEMBLEM_ID   NUMBER
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_PURCHASEORDER_WO_LINK
  add constraint PK_ERP_I_PURCHASEORDER_WO_LINK primary key (TRANSFER_ID, EXT_PURCHASEORDERLINE_ID , WORKORDERLINE_ID);
alter table ERP_I_PURCHASEORDER_WO_LINK
  add constraint FK_ERP_I_PURCHASEORDER_WO_LINK foreign key (TRANSFER_ID)
  references ERP_I_PURCHASEORDERHEADER (TRANSFER_ID) on delete cascade;


-- Create table ERP_I_PURCHASEORDER_LOG
create table ERP_I_PURCHASEORDER_LOG
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_I_PURCHASEORDER_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_I_PURCHASEORDER_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_PURCHASEORDER_LOG
  add constraint PK_ERP_I_PURCHASEORDER_LOG primary key (TRANSFER_ID);
alter table ERP_I_PURCHASEORDER_LOG
  add constraint FK_ERP_I_PURCHASEORDER_LOG foreign key (TRANSFER_ID)
  references ERP_I_PURCHASEORDERHEADER(TRANSFER_ID) on delete cascade;
----------------------------------------

-- Create table ERP_O_INTERNALCALCULATION
create table ERP_O_INTERNALCALCULATION
(
  TRANSFER_ID             NUMBER not null,
  TRANSFER_STATUS         NUMBER default 0 not null,
  PACKINGNOTELINE_ID      NUMBER,
  PACKINGNOTECONTAINER_ID NUMBER,
  BUSINESSUNITCODE        VARCHAR2(6) not null,
  PRODUCTION_BUCODE       VARCHAR2(6) not null,
  INTERNAL_BUCODE         VARCHAR2(6) not null,
  INVOICE_BUCODE          VARCHAR2(6) not null,
  CUSTOMERNUMBER          NUMBER not null,
  MARKETSEGMENTCODE       VARCHAR2(6) not null,
  DEPARTMENTNUMBER        NUMBER not null,
  CONSUMPTIONPOINTNUMBER  NUMBER not null,
  PRODUCTCODE             VARCHAR2(12),
  SIZECODE                VARCHAR2(12),
  FINISHINGMETHODCODE     VARCHAR2(6),
  DELIVERED_WASH_LOG      NUMBER,
  DELIVERED_WASH_COG      NUMBER,
  WASH_AMOUNT_LOG         NUMBER(15,6),
  WASH_AMOUNT_COG         NUMBER(15,6),
  WEIGHINGCATEGORYCODE    VARCHAR2(6),
  WEIGHING_AMOUNT         NUMBER(15,6),
  NETWEIGHT               NUMBER(15,6),
  DELIVERYDATE            DATE,
  NOTENUMBER              NUMBER not null,
  FIXED_YESNO             VARCHAR2(1)
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_INTERNALCALCULATION
  add constraint PK_ERP_O_INTERNALCALCUL primary key (TRANSFER_ID);
-- Create/Recreate check constraints 
alter table ERP_O_INTERNALCALCULATION
  add constraint CHK_ERP_O_INTERNALCALC_FIXEDYN
  check (FIXED_YESNO in ('Y','N'));

-- Create table ERP_O_INTERNALCALCULATION_LOG
create table ERP_O_INTERNALCALCULATION_LOG
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_O_INTERNALCALCULATION_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_O_INTERNALCALCULATION_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_INTERNALCALCULATION_LOG
  add constraint PK_ERP_O_INTERNALCALC_LOG primary key (TRANSFER_ID);
alter table ERP_O_INTERNALCALCULATION_LOG
  add constraint FK_ERP_O_INTERNALCALC_LOG foreign key (TRANSFER_ID)
  references ERP_O_INTERNALCALCULATION(TRANSFER_ID) on delete cascade;

----------------------------------------

-- Create table ERP_O_QUANTITYCUSTOMER
create table ERP_O_QUANTITYCUSTOMER
(
  TRANSFER_ID         NUMBER not null,
  TRANSFER_STATUS     NUMBER default 0 not null,
  EXPORT_DATE         DATE,
  CUSTOMERNUMBER      NUMBER not null,
  PRODUCTCODE         VARCHAR2(12) not null,
  SIZECODE            VARCHAR2(12) not null,
  FINISHINGMETHODCODE VARCHAR2(6) not null,
  QUANTITY            NUMBER
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_QUANTITYCUSTOMER
  add constraint PK_ERP_O_QUANTITYCUSTOMER primary key (TRANSFER_ID);
  
-- Extra constrain that might be handy to prevent wrong data in table!
-- Create/Recreate primary, unique and foreign key constraints 
alter table LANGUAGES
  add constraint XAK2LANGUAGES unique (CODE);
  
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_CUSTOMER
  add constraint FK_ERP_I_CUSTOMER_LANGUAGE foreign key (LANGUAGECODE)
  references languages (CODE) on delete cascade;

-- Create table ERP_O_QUANTITYCUSTOMER_LOG
create table ERP_O_QUANTITYCUSTOMER_LOG
(
  TRANSFER_ID     NUMBER not null,
  TRANSFER_STATUS NUMBER default 0 not null,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER default 0 not null
);
-- Add comments to the columns 
comment on column ERP_O_QUANTITYCUSTOMER_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_O_QUANTITYCUSTOMER_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_QUANTITYCUSTOMER_LOG
  add constraint PK_ERP_O_QUANTITYCUSTOMER_LOG primary key (TRANSFER_ID);
alter table ERP_O_QUANTITYCUSTOMER_LOG
  add constraint FK_ERP_O_QUANTITYCUSTOMER_LOG foreign key (TRANSFER_ID)
  references ERP_O_QUANTITYCUSTOMER (TRANSFER_ID) on delete cascade;
----------------------------------------

--update erp_i_product 6-juli-2011
alter table ERP_I_PRODUCT add PRODUCTSORTGROUPCODE varchar2(6);

--13-juli-2011
-- Add/modify columns 
alter table ERP_I_CUSTOMER add DEFAULTINVBU VARCHAR2(6) not null;
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_CUSTOMER
  add constraint FK_ERP_I_CUSTOMER_INVBU foreign key (DEFAULTINVBU)
  references businessunit (CODE) on delete cascade;

-- Create/Recreate indexes for speed..
create index indx_packingnote_exp on PACKINGNOTE (exported);

--extra column for table Workorder
alter table WORKORDER add ERP_EXPORTED NUMBER default 0 not null;
alter table WORKORDER
  add constraint CHK_WORKORDER_ERP_EXP
  check (ERP_EXPORTED in (0,10,30,40));

-- Create/Recreate indexes 
create index XIF22workorder on WORKORDER (erp_exported, status_id);

-- Add/modify columns , allow null, as in document.
alter table ERP_O_WORKORDER modify DEFAULTBUSINESSUNIT null;
alter table ERP_O_WORKORDER modify MARKETSEGMENTCODE null;
alter table ERP_O_WORKORDER modify DEPARTMENTNUMBER null;
alter table ERP_O_WORKORDER modify CONSUMPTIONPOINTNUMBER null;

--changes 18-july-2011, for speed during export only select not exported!
create index XIF6STOCKTRANSACTION on STOCKTRANSACTION (EXPORTED);
create index XIE6INVOICEHEADER on INVOICEHEADER (EXPORTED);
create index XIF8PURCHASEORDERHEADER on PURCHASEORDERHEADER (exportdate);

-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_WORKORDERLINE
  add constraint FK_ERP_O_WORKORDERLINE foreign key (TRANSFER_ID)
  references erp_o_workorder (TRANSFER_ID) on delete cascade;

-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_INVOICELINE
  add constraint FK_ERP_O_INVOICELINE foreign key (TRANSFER_ID)
  references erp_o_invoiceheader (TRANSFER_ID) on delete cascade;

-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_WORKORDERLINEMEBLEM
  drop constraint FK_ERP_ERP_O_WORKORDERLINEMEBL;
alter table ERP_O_WORKORDERLINEMEBLEM
  add constraint FK_ERP_ERP_O_WORKORDERLINEMEBL foreign key (TRANSFER_ID, WORKORDERLINE_ID)
  references ERP_O_WORKORDERline (TRANSFER_ID, WORKORDERLINE_ID) on delete cascade;

-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_WORKORDERLINERES
  add constraint FK_ERP_ERP_O_WORKORDERlineres foreign key (TRANSFER_ID, WORKORDERLINE_ID)
  references erp_o_workorderline (TRANSFER_ID, WORKORDERLINE_ID) on delete cascade;

-- Create/Recreate check constraints 
alter table ERP_I_CONSUMPTIONPOINT
  add constraint CHK_ERP_I_CONSUMPTIONPOINT_ACT
  check (ACTIVE in ('Y','N'));

-- Create/Recreate check constraints 
alter table ERP_I_CUSTOMER
  add constraint CHK_ERP_I_CUSTOMER_TAXABLE
  check (TAXABLE IN ('Y','N'));

alter table ERP_I_CUSTOMER
  add constraint CHK_ERP_I_CUSTOMER_PAYMENTTYPE
  check (PAYMENTTYPE BETWEEN 0 AND 7);

-- Add/modify columns 
alter table ERP_I_PRODUCT modify LOTNUMBERREQUIREDYN default 'N';
-- Create/Recreate check constraints 
alter table ERP_I_PRODUCT
  add constraint CHK_ERP_I_PRODUCT_LOTNUMBER
  check (LOTNUMBERREQUIREDYN IN (NULL,'Y','N'));

-- Changes 22-July-2011
-- Add/modify columns 
alter table ERP_O_STOCKTRANSACTION add NEWSIZECODE varchar2(12);
-- Add comments to the columns 
comment on column ERP_O_STOCKTRANSACTION.SIZECODE
  is 'this is the size for this transaction';
comment on column ERP_O_STOCKTRANSACTION.NEWSIZECODE
  is 'new size code, that will be used. so the sizecode will be transformed into this size!';

--drop column that is not needed.
alter table erp_o_internalcalculation drop column BUSINESSUNITCODE;

--make address optional
-- Add/modify columns 
alter table ERP_I_DELIVERYPOINT modify ADDRESS null;
alter table ERP_I_DELIVERYPOINT modify ADDRESS2 null;
alter table ERP_I_DELIVERYPOINT modify ZIPCODE null;
alter table ERP_I_DELIVERYPOINT modify CITY null;

-- Create/Recreate check constraints for product TOM!
alter table ERP_I_PRODUCT
  add constraint CHK_ERP_I_PRODYCT_TOM
  check (TYPEOFMERCHANDISE IN (1,2,3,4,5));

-- Create/Recreate check constraints 
alter table ERP_I_PURCHASEORDERHEADER
  add constraint CHK_ERP_I_PURCHASEORDERHEAD_WO
  check (PROCESSEDTOWORKORDER IN ('Y','N'));

-- Changes 26-July-2011
alter table ERP_I_CONSUMPTIONPOINT_LOG add TIMESTAMP date default SYSDATE not null;
alter table ERP_I_COSTCENTER_LOG add TIMESTAMP date default SYSDATE not null;
alter table ERP_I_CUSTOMER_LOG add TIMESTAMP date default SYSDATE not null;
alter table ERP_I_DELIVERYPOINT_LOG add TIMESTAMP date default SYSDATE not null;
alter table ERP_I_DEPARTMENT_LOG add TIMESTAMP date default SYSDATE not null;
alter table ERP_I_PRODUCT_LOG add TIMESTAMP date default SYSDATE not null;
alter table ERP_I_PURCHASEORDER_LOG add TIMESTAMP date default SYSDATE not null;
alter table ERP_I_SIZE_LOG add TIMESTAMP date default SYSDATE not null;
alter table ERP_I_SUPPLIER_LOG add TIMESTAMP date default SYSDATE not null;
alter table ERP_I_WEIGHINGCATEGORY_LOG add TIMESTAMP date default SYSDATE not null;
alter table ERP_O_INTERNALCALCULATION_LOG add TIMESTAMP date default SYSDATE not null;
alter table ERP_O_INVOICE_LOG add TIMESTAMP date default SYSDATE not null;
alter table ERP_O_PACKINGNOTE_LOG add TIMESTAMP date default SYSDATE not null;
alter table ERP_O_QUANTITYCUSTOMER_LOG add TIMESTAMP date default SYSDATE not null;
alter table ERP_O_STOCKTRANSACTION_LOG add TIMESTAMP date default SYSDATE not null;
alter table ERP_O_WORKORDER_LOG add TIMESTAMP date default SYSDATE not null;

-- 2 aug 2011 added missing column
alter table ERP_O_STOCKTRANSACTION add WORKORDERLINEEMBLEM_ID number;

--9-aug-2011 removed and added column
alter table  ERP_O_STOCKTRANSACTION drop column WORKORDERLINEEMBLEM_ID;
alter table  ERP_O_STOCKTRANSACTION add  NEWQUALITYGRADE varchar2(1);

--11-aug-2011
-- Make same length as in ABSSolute
alter table ERP_I_CUSTOMER modify COUNTRY VARCHAR2(3);
-- Add/modify columns ERP_I_WEIGHINGCATEGORY 
alter table ERP_I_WEIGHINGCATEGORY modify TURNOVERGROUPCODE not null;
alter table ERP_I_WEIGHINGCATEGORY add TAXGROUPCODE VARCHAR2(6) not null;
alter table ERP_I_WEIGHINGCATEGORY add COUNTRY VARCHAR2(3) not null;
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_WEIGHINGCATEGORY
  add constraint FK_ERP_WEIGHINGCAT_COUNTRY foreign key (COUNTRY)
  references country (CODE) on delete cascade;

--7-sep-2011
-- Add/modify columns ERP_I_WEIGHINGCATEGORY
alter table ERP_I_WEIGHINGCATEGORY add REGISTRATIONUNITCODE VARCHAR2(3);
-- Add comments to the columns 
comment on column ERP_I_WEIGHINGCATEGORY.REGISTRATIONUNITCODE
  is 'Optional registration unit';
-- Add/modify columns 
alter table ERP_I_WEIGHINGCATEGORY add EXTRAINPUT varchar2(1) default 'N' not null;
-- Add comments to the columns 
comment on column ERP_I_WEIGHINGCATEGORY.EXTRAINPUT
  is 'Extra input N or Y, N is default';
-- Create/Recreate check constraints 
alter table ERP_I_WEIGHINGCATEGORY
  add constraint CHK_ERP_WEIGHING_EXTRAINPUT
  check (EXTRAINPUT IN ('Y', 'N'));

--12-sep-2011
create table ERP_TRANSACTION_LINK
(
  ERP_TABLENAME     varchar2(32),
  ERP_PROCEDURECALL varchar2(128)
);
alter table ERP_TRANSACTION_LINK
  add constraint PK_ERP_TRANSACTION_LINK primary key (ERP_TABLENAME);

--12-sep-2011
DELETE FROM ERP_TRANSACTION_LINK;

insert into ERP_TRANSACTION_LINK (ERP_TABLENAME, ERP_PROCEDURECALL)
values ('ERP_I_CONSUMPTIONPOINT_LOG', 'ERP_INTERFACE.PROC_I_CONS');
insert into ERP_TRANSACTION_LINK (ERP_TABLENAME, ERP_PROCEDURECALL)
values ('ERP_I_COSTCENTER_LOG', 'ERP_INTERFACE.PROC_I_COST');
insert into ERP_TRANSACTION_LINK (ERP_TABLENAME, ERP_PROCEDURECALL)
values ('ERP_I_CUSTOMER_LOG', 'ERP_INTERFACE.PROC_I_CUST');
insert into ERP_TRANSACTION_LINK (ERP_TABLENAME, ERP_PROCEDURECALL)
values ('ERP_I_DELIVERYPOINT_LOG', 'ERP_INTERFACE.PROC_I_DELP');
insert into ERP_TRANSACTION_LINK (ERP_TABLENAME, ERP_PROCEDURECALL)
values ('ERP_I_DEPARTMENT_LOG', 'ERP_INTERFACE.PROC_I_DEPART');
insert into ERP_TRANSACTION_LINK (ERP_TABLENAME, ERP_PROCEDURECALL)
values ('ERP_I_PRODUCT_LOG', 'ERP_INTERFACE.PROC_I_PROD');
insert into ERP_TRANSACTION_LINK (ERP_TABLENAME, ERP_PROCEDURECALL)
values ('ERP_I_PURCHASEORDER_LOG', 'ERP_INTERFACE.PROC_I_PURCHASEORDER');
insert into ERP_TRANSACTION_LINK (ERP_TABLENAME, ERP_PROCEDURECALL)
values ('ERP_I_SIZE_LOG', 'ERP_INTERFACE.PROC_I_SIZE');
insert into ERP_TRANSACTION_LINK (ERP_TABLENAME, ERP_PROCEDURECALL)
values ('ERP_I_SUPPLIER_LOG', 'ERP_INTERFACE.PROC_I_SUPP');
insert into ERP_TRANSACTION_LINK (ERP_TABLENAME, ERP_PROCEDURECALL)
values ('ERP_I_WEIGHINGCATEGORY_LOG', 'ERP_INTERFACE.PROC_I_WEIGH');
