-- ABS-45551
-- Create table EDI_O_PURCHASEORDERHEADER
create table EDI_O_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,
  ILNNUMBER              NUMBER,
  POEXPORTFORMAT         NUMBER DEFAULT 1 NOT NULL,
  PLANTCODE              VARCHAR2(6) NOT NULL,
  BUSINESSUNITCODE       VARCHAR2(6) NOT NULL,
  STOCKROOMCODE          VARCHAR2(12),
  ORDERDATE              DATE NOT NULL,
  REQUESTEDDATE          DATE NOT NULL,
  STATUS                 VARCHAR2(2),
  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,
  APPROVALUSER           VARCHAR2(20),
  CANCELUSER             VARCHAR2(20),
  ORDERUSER              VARCHAR2(20),
  CONFIRMEDUSER          VARCHAR2(20),
  EXPORTUSER             VARCHAR2(20),
  EXPORTDATE             DATE,
  SHIPTO                 NUMBER DEFAULT 0 NOT NULL,
  ORDSTOCKROOMCODE       VARCHAR2(12) NOT NULL,
  ORDLEGALENTITYCODE     VARCHAR2(10),
  ORDLEGALENTITYNAME     VARCHAR2(40),
  ORDLEGALNAME           VARCHAR2(80),
  ORDSTREET1             VARCHAR2(30),
  ORDSTREET2             VARCHAR2(30),
  ORDZIPCODE             VARCHAR2(15),
  ORDCITY                VARCHAR2(30),
  ORDCOUNTRY             VARCHAR2(30),
  SHTSTOCKROOMCODE       VARCHAR2(12),
  SHTLEGALENTITYCODE     VARCHAR2(10),
  SHTLEGALENTITYNAME     VARCHAR2(40),
  SHTLEGALNAME           VARCHAR2(80),
  SHTNAME                VARCHAR2(40),  
  SHTEXTRANAME1          VARCHAR2(40),  
  SHTEXTRANAME2          VARCHAR2(40),  
  SHTSTREET1             VARCHAR2(30),
  SHTSTREET2             VARCHAR2(30),
  SHTZIPCODE             VARCHAR2(15),
  SHTCITY                VARCHAR2(30),
  SHTCOUNTRY             VARCHAR2(30),
  BILLTO		 NUMBER DEFAULT 0 NOT NULL,
  BILSTOCKROOMCODE       VARCHAR2(12),
  BILLEGALENTITYCODE     VARCHAR2(10),
  BILLEGALENTITYNAME     VARCHAR2(40),
  BILLEGALNAME           VARCHAR2(80),
  BILSTREET1             VARCHAR2(30),
  BILSTREET2             VARCHAR2(30),
  BILZIPCODE             VARCHAR2(15),
  BILCITY                VARCHAR2(30),
  BILCOUNTRY             VARCHAR2(30),
  SITEXTRANAME1          VARCHAR2(40),
  SITEXTRANAME2          VARCHAR2(40),
  SITSTREET1             VARCHAR2(30),
  SITSTREET2             VARCHAR2(30),
  SITZIPCODE             VARCHAR2(15),
  SITCITY                VARCHAR2(30),
  SITCOUNTRY             VARCHAR2(30) 
);
-- Add comments to the columns 
comment on column EDI_O_PURCHASEORDERHEADER.POEXPORTFORMAT
  is '1 = ABS standard PO Export
2 = Mulliez PO Export';
comment on column EDI_O_PURCHASEORDERHEADER.STATUS
  is '5 = Waiting for approval 10 = New (approved) 20 = Send 25 = Confirmed 30 = Partly received 40 = Completed 50 = Cancelled Status type PO (purchase order)';
comment on column EDI_O_PURCHASEORDERHEADER.ORDERTYPE
  is '0 = Manual / no AC / not applicable 1 = Personalized order 2 = Standard order';
comment on column EDI_O_PURCHASEORDERHEADER.PURCHASEORDERTYPE
  is '0 = External 1 = Internal';
comment on column EDI_O_PURCHASEORDERHEADER.ORIGIN
  is '1 = Purchase request 2 = Manual created 3 = Conversion 4 = Work order approval 5 = Work order status control';
comment on column EDI_O_PURCHASEORDERHEADER.PRIORITY
  is '1 = Replacement purchase order 2 = Others 3 = Direct orders';
comment on column EDI_O_PURCHASEORDERHEADER.SHIPTO
  is '0 = Ordering Stockroom
1 = Other Stockroom
2 = Ordering customer
3 = Specific delivery address';
comment on column EDI_O_PURCHASEORDERHEADER.ORDSTOCKROOMCODE
  is 'Ordering stockroom, Will always be filled';
comment on column EDI_O_PURCHASEORDERHEADER.ORDLEGALENTITYCODE
  is 'Code of the legal entity the ordering stock room belongs to';
comment on column EDI_O_PURCHASEORDERHEADER.ORDLEGALENTITYNAME
  is 'Name of the legal entity the ordering stock room belongs to';
comment on column EDI_O_PURCHASEORDERHEADER.ORDLEGALNAME
  is 'Legal name of the legal entity ordering stock room belongs to';
comment on column EDI_O_PURCHASEORDERHEADER.ORDSTREET1
  is 'Delivery address info of the ordering stockroom (Street 1)';
comment on column EDI_O_PURCHASEORDERHEADER.ORDSTREET2
  is 'Delivery address info of the ordering stockroom (Street 2)';
comment on column EDI_O_PURCHASEORDERHEADER.ORDZIPCODE
  is 'Delivery address info of the ordering stockroom (ZIP code)';
comment on column EDI_O_PURCHASEORDERHEADER.ORDCITY
  is 'Delivery address info of the ordering stockroom (City)';
comment on column EDI_O_PURCHASEORDERHEADER.ORDCOUNTRY
  is 'Delivery address info of the ordering stockroom (Country)';
comment on column EDI_O_PURCHASEORDERHEADER.SHTSTOCKROOMCODE
  is 'Ship-To stockroom. Will only be filled when ShipTo = 1, otherwise empty';
comment on column EDI_O_PURCHASEORDERHEADER.SHTLEGALENTITYCODE
  is 'Code of the legal entity the ship-to stock room belongs to';
comment on column EDI_O_PURCHASEORDERHEADER.SHTLEGALENTITYNAME
  is 'Name of the legal entity the ship-to stock room belongs to';
comment on column EDI_O_PURCHASEORDERHEADER.SHTLEGALNAME
  is 'Legal name of legal entity the ship-to stock room belongs to';
comment on column EDI_O_PURCHASEORDERHEADER.SHTNAME
  is 'Additional delivery address info of the ship-to party (Name). 
Will in fact only be filled in case of a specific delivery address (ship-to = 4)';
comment on column EDI_O_PURCHASEORDERHEADER.SHTEXTRANAME1
  is 'Additional delivery address info of the ship-to party (Extra name 1)
Will in fact only be filled in case of a specific delivery address (ship-to = 4)';
comment on column EDI_O_PURCHASEORDERHEADER.SHTEXTRANAME2
  is 'Additional delivery address info of the ship-to party (Extra name 2)
Will in fact only be filled in case of a specific delivery address (ship-to = 4)';
comment on column EDI_O_PURCHASEORDERHEADER.SHTSTREET1
  is 'Delivery address info of the ship-to party (Street 1)';
comment on column EDI_O_PURCHASEORDERHEADER.SHTSTREET2
  is 'Delivery address info of the ship-to party (Street 2)';
comment on column EDI_O_PURCHASEORDERHEADER.SHTZIPCODE
  is 'Delivery address info of the ship-to party (ZIP code)';
comment on column EDI_O_PURCHASEORDERHEADER.SHTCITY
  is 'Delivery address info of the ship-to party (City)';
comment on column EDI_O_PURCHASEORDERHEADER.SHTCOUNTRY
  is 'Delivery address info of the ship-to party (County)';
comment on column EDI_O_PURCHASEORDERHEADER.BILLTO
  is '0 = Ordering Stockroom
1 = Ship-to Stockroom
2 = Other';
comment on column EDI_O_PURCHASEORDERHEADER.BILSTOCKROOMCODE
  is 'Code of the stockroom considered the billing party. 
Will only be filled when the billing party is indeed a stockroom (BillTo = 0 or 1).';
comment on column EDI_O_PURCHASEORDERHEADER.BILLEGALENTITYCODE
  is 'Code of the legal entity that is considered the billing company';
comment on column EDI_O_PURCHASEORDERHEADER.BILLEGALENTITYNAME
  is 'Name of the legal entity that is considered the billing company';
comment on column EDI_O_PURCHASEORDERHEADER.BILLEGALNAME
  is 'Name of the billing company';
comment on column EDI_O_PURCHASEORDERHEADER.BILSTREET1
  is 'General address info of the bill-to company (Street 1)';
comment on column EDI_O_PURCHASEORDERHEADER.BILSTREET2
  is 'General address info of the bill-to company (Street 2)';
comment on column EDI_O_PURCHASEORDERHEADER.BILZIPCODE
  is 'General address info of the bill-to company (Zipcode)';
comment on column EDI_O_PURCHASEORDERHEADER.BILCITY
  is 'General address info of the bill-to company (City)';
comment on column EDI_O_PURCHASEORDERHEADER.BILCOUNTRY
  is 'Delivery address info of the bill-to company (Country)';
comment on column EDI_O_PURCHASEORDERHEADER.SITEXTRANAME1
  is 'Additional invoice address info of the bill-to company (Extra name 1)';
comment on column EDI_O_PURCHASEORDERHEADER.SITEXTRANAME2
  is 'Additional invoice address info of the bill-to company (Extra name 2)';
comment on column EDI_O_PURCHASEORDERHEADER.SITSTREET1
  is 'Invoice address info of the bill-to company (Street 1)';
comment on column EDI_O_PURCHASEORDERHEADER.SITSTREET2
  is 'Invoice address info of the bill-to company (Street 2)';
comment on column EDI_O_PURCHASEORDERHEADER.SITZIPCODE
  is 'Invoice address info of the bill-to company (ZIP code)';
comment on column EDI_O_PURCHASEORDERHEADER.SITCITY
  is 'Invoice address info of the bill-to company (City)';
comment on column EDI_O_PURCHASEORDERHEADER.SITCOUNTRY
  is 'Invoice address info of the bill-to company (Country)';
-- Create/Recreate indexes 
create index INDX_EDI_O_PURCHASEORDERHEADER on EDI_O_PURCHASEORDERHEADER (PURCHASEORDERNUMBER);
-- Create/Recreate primary, unique and foreign key constraints 
alter table EDI_O_PURCHASEORDERHEADER
  add constraint PK_EDI_O_PURCHASEORDERHEADER primary key (TRANSFER_ID);
-- Create/Recreate check constraints 
alter table EDI_O_PURCHASEORDERHEADER
  add constraint CHK_EDI_O_PURCHASEORDERHEAD_PO
  check (POEXPORTFORMAT IN (0,1));
alter table EDI_O_PURCHASEORDERHEADER
  add constraint CHK_EDI_O_PURCHASEORDERHEAD_OI
  check (ORIGIN between 1 and 5);
alter table EDI_O_PURCHASEORDERHEADER
  add constraint CHK_EDI_O_PURCHASEORDERHEAD_OR
  check (ORDERTYPE IN (0,1,2));
alter table EDI_O_PURCHASEORDERHEADER
  add constraint CHK_EDI_O_PURCHASEORDERHEAD_ST
  check (SHIPTO IN (0,1,2,3));
alter table EDI_O_PURCHASEORDERHEADER
  add constraint CHK_EDI_O_PURCHASEORDERHEAD_OT
  check (PURCHASEORDERTYPE IN (0,1));
alter table EDI_O_PURCHASEORDERHEADER
  add constraint CHK_EDI_O_PURCHASEORDERHEAD_PR
  check (PRIORITY IN (1,2,3));
alter table EDI_O_PURCHASEORDERHEADER
  add constraint CHK_EDI_O_PURCHASEORDERHEAD_WO
  check (PROCESSEDTOWORKORDER IN ('Y','N'));
alter table EDI_O_PURCHASEORDERHEADER
  add constraint CHK_EDI_O_PURCHASEORDERHEAD_BT
  check (BILLTO IN (0,1,2));
  
-- Create table EDI_O_PURCHASEORDERLINE
create table EDI_O_PURCHASEORDERLINE
(
  TRANSFER_ID              NUMBER NOT NULL,
  TRANSFER_STATUS          NUMBER DEFAULT 0 NOT NULL,
  PURCHASEORDERNUMBER      NUMBER NOT NULL,
  PURCHASEORDERLINENUMBER  NUMBER NOT NULL,
  PURCHASEORDERLINE_ID     NUMBER NOT NULL,
  SUPPLIERPRODUCTCODE      VARCHAR2(40),
  SUPPLIERSIZE             VARCHAR2(30),
  PRODUCTCODE              VARCHAR2(12) NOT NULL,
  PRODUCTDESCRIPTION       VARCHAR2(30) NOT NULL,
  SIZECODE                 VARCHAR2(12) NOT NULL,
  REQUESTEDGRADE           VARCHAR2(1) NOT NULL,
  QUANTITYORDERED          NUMBER NOT NULL,
  QUANTITYCANCELLED        NUMBER,
  QUANTITYCONFIRMED        NUMBER,
  PURCHASEPRICE            NUMBER(15,6),
  CONFIRMEDDATE            DATE,
  REMARK                   VARCHAR2(300),
  UNIQUEITEM               VARCHAR2(1) DEFAULT 'N' NOT NULL,
  QUANTITYRECEIVED         NUMBER,
  MODIFICATION1        VARCHAR2(10),
  MODIFICATION2        VARCHAR2(10),
  MODIFICATION3        VARCHAR2(10),
  PREWASHBYSUPPLIER        NUMBER DEFAULT 0 NOT NULL,
  EXT_PURCHASEORDERLINE_ID NUMBER  
);
comment on column EDI_O_PURCHASEORDERLINE.PREWASHBYSUPPLIER
  is '0=No prewash needed, 1=Prewash needed';
-- Create/Recreate primary, unique and foreign key constraints 
alter table EDI_O_PURCHASEORDERLINE
  add constraint PK_EDI_O_PURCHASEORDERLINE primary key (TRANSFER_ID, PURCHASEORDERLINE_ID)
  using index 
  tablespace indx;
alter table EDI_O_PURCHASEORDERLINE
  add constraint FK_EDI_O_PURCHASEORDERLINE foreign key (TRANSFER_ID)
  references EDI_O_PURCHASEORDERHEADER (TRANSFER_ID) on delete cascade;
-- Create/Recreate check constraints 
alter table EDI_O_PURCHASEORDERLINE
  add constraint CHK_EDI_O_PURCHASEORDERLINE_PS
  check (PREWASHBYSUPPLIER IN (0,1));
  
-- Create table EDI_O_PURCHASEORDER_LOG
create table EDI_O_PURCHASEORDER_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
-- Add comments to the columns 
comment on column EDI_O_PURCHASEORDER_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column EDI_O_PURCHASEORDER_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table EDI_O_PURCHASEORDER_LOG
  add constraint PK_EDI_O_PURCHASEORDER_LOG primary key (TRANSFER_ID)
  using index 
  tablespace indx;
alter table EDI_O_PURCHASEORDER_LOG
  add constraint FK_EDI_O_PURCHASEORDER_LOG foreign key (TRANSFER_ID)
  references EDI_O_PURCHASEORDERHEADER (TRANSFER_ID) on delete cascade;
-- Create/Recreate check constraints 
alter table EDI_O_PURCHASEORDER_LOG
  add constraint CHK_EDI_O_PURCHASEORDER_LOG_TS
  check (TRANSFER_STATUS IN (0,1));
alter table EDI_O_PURCHASEORDER_LOG
  add constraint CHK_EDI_O_PURCHASEORDER_LOG_PS
  check (PROCESS_STATUS IN (0,1));

-- Create table EDI_I_PURCHASEORDERCONF
create table EDI_I_PURCHASEORDERCONF
(
  TRANSFER_ID              NUMBER NOT NULL,
  TRANSFER_STATUS          NUMBER DEFAULT 0 NOT NULL,
  PURCHASEORDERHEADER_ID   NUMBER NOT NULL,
  PURCHASEORDERNUMBER      NUMBER NOT NULL,
  POIMPORTFORMAT           NUMBER DEFAULT 1 NOT NULL,
  PLANTCODE                VARCHAR2(6) NOT NULL,
  SUPPLIERNUMBER           NUMBER NOT NULL,
  CONFIRMEDDATE            DATE NOT NULL,
  CONFIRMATIONTIMESTAMP    DATE NOT NULL,
  CONFIRMATIONVERNR        VARCHAR2(3),
  CONFIRMATIONEDITNR       VARCHAR2(3)
);
-- Create/Recreate indexes 
create index INDX_EDI_I_PURCHASEORDERCONF on EDI_I_PURCHASEORDERCONF (PURCHASEORDERNUMBER)
  tablespace INDX;
-- Create/Recreate primary, unique and foreign key constraints 
alter table EDI_I_PURCHASEORDERCONF
  add constraint PK_EDI_I_PURCHASEORDERCONF primary key (TRANSFER_ID);
-- Create/Recreate check constraints 
alter table EDI_I_PURCHASEORDERCONF
  add constraint CHK_EDI_I_PURCHASEORDERCONF_PO
  check (POIMPORTFORMAT IN (1,2));
  
-- Create table EDI_I_PURCHASEORDERCONFLINE
create table EDI_I_PURCHASEORDERCONFLINE
(
  TRANSFER_ID              NUMBER NOT NULL,
  TRANSFER_STATUS          NUMBER DEFAULT 0 NOT NULL,
  PURCHASEORDERNUMBER      NUMBER NOT NULL,
  PURCHASEORDERLINENUMBER  NUMBER NOT NULL,
  PURCHASEORDERLINE_ID     NUMBER NOT NULL,
  SUPPLIERPRODUCTCODE      VARCHAR2(40) NOT NULL,
  SUPPLIERSIZE             VARCHAR2(30),
  PRODUCTCODE              VARCHAR2(12) NOT NULL,
  SIZECODE                 VARCHAR2(12) NOT NULL,
  QUANTITYCONFIRMED        NUMBER,
  CONFIRMEDDELIVERYDATE    DATE,
  CONFIRMEDPRICE           NUMBER(15,6),
  CONFIRMATIONCODE         NUMBER
);
-- Create/Recreate indexes 
create index INDX_EDI_I_PURCHORDERCONFLINE on EDI_I_PURCHASEORDERCONFLINE (PURCHASEORDERNUMBER, PRODUCTCODE, SIZECODE)
  tablespace INDX;
-- Create/Recreate primary, unique and foreign key constraints 
alter table EDI_I_PURCHASEORDERCONFLINE
  add constraint PK_EDI_I_PURCHASEORDERCONFLINE primary key (TRANSFER_ID, PURCHASEORDERLINE_ID);
alter table EDI_I_PURCHASEORDERCONFLINE
  add constraint FK_EDI_I_PURCHASEORDERCONFLINE foreign key (TRANSFER_ID)
  references EDI_I_PURCHASEORDERCONF (TRANSFER_ID) on delete cascade;  
  
-- Create table EDI_I_PURCHASEORDERCONF_LOG
create table EDI_I_PURCHASEORDERCONF_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
-- Add comments to the columns 
comment on column EDI_I_PURCHASEORDERCONF_LOG.TRANSFER_STATUS
  is '0 = ready to be read, 1 = read correctly';
comment on column EDI_I_PURCHASEORDERCONF_LOG.PROCESS_STATUS
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table EDI_I_PURCHASEORDERCONF_LOG
  add constraint PK_EDI_I_PURCHASEORDERCONF_LOG primary key (TRANSFER_ID)
  using index 
  tablespace indx;
alter table EDI_I_PURCHASEORDERCONF_LOG
  add constraint FK_EDI_I_PURCHASEORDERCONF_LOG foreign key (TRANSFER_ID)
  references EDI_I_PURCHASEORDERCONF (TRANSFER_ID) on delete cascade;
-- Create/Recreate check constraints 
alter table EDI_I_PURCHASEORDERCONF_LOG
  add constraint CHK_PURCHASEORDERCONF_LOG_TS
  check (TRANSFER_STATUS IN (0,1));
alter table EDI_O_PURCHASEORDER_LOG
  add constraint CHK_PURCHASEORDERCONF_LOG_PS
  check (PROCESS_STATUS IN (0,1));

--------  PURCHASEORDERLINE  ----------
ALTER TABLE PURCHASEORDERLINE ADD (LINENUMBER NUMBER NULL);

COMMENT ON COLUMN PURCHASEORDERLINE.LINENUMBER IS 'Will only be filled when the module EDIPURCHASING is active, in that case it is the logical line number within PO';

CREATE INDEX XIE2PURCHASEORDERLINE ON PURCHASEORDERLINE (PURCHASEORDERHEADER_ID ASC, LINENUMBER ASC);