-----changes 17-03-2015 
-- Create table ERP_I_POCONFIRMATION 
CREATE TABLE ERP_I_POCONFIRMATION 
( 
TRANSFER_ID              NUMBER NOT NULL, 
TRANSFER_STATUS          NUMBER NOT NULL, 
PURCHASEORDERNUMBER      NUMBER NOT NULL, 
PURCHASEORDERLINENUMBER  NUMBER NOT NULL, --EXT_PURCHASEORDERLINE_ID 
SEQUENTIALNUMBER         NUMBER NOT NULL, --LINESEQUENTIALNUMBER 
SUPPLIERPRODUCTCODE      VARCHAR2(30), 
DELIVERYDATE             VARCHAR2(8), 
DELIVERYTIME             VARCHAR2(8), 
CONFIRMEDQUANTITY        NUMBER(15,6) NOT NULL,   
NETVALUE                 NUMBER(15,6),   --confirmed purchase price will be processed by MAWI-Web 
CREATIONDATE             VARCHAR2(8),    --creation date/time of the confirmation will be processed by MAWI-Web 
CREATIONTIME             VARCHAR2(8),    --creation date/time of the confirmation will be processed by MAWI-Web 
CURRENCYCODE             VARCHAR2(3) NOT NULL, 
EXTDOCUMENTNUMBER        VARCHAR2(20),   --reference number will be processed by MAWI-Web 
FLAGUNRELIABLE           VARCHAR2(1) DEFAULT 'N' NOT NULL, 
FLAGUNDELIVERABLE        VARCHAR2(1) DEFAULT 'N' NOT NULL 
); 
-- Add comments to the columns 
comment on column ERP_I_POCONFIRMATION.FLAGUNRELIABLE 
is 'Records in which flagunreliable is not N must be set to processed without further processing'; 
comment on column ERP_I_POCONFIRMATION.FLAGUNDELIVERABLE 
is 'Records in which flagundeliverable is not N must be set to processed without further processing'; 
comment on column ERP_I_POCONFIRMATION.PURCHASEORDERLINENUMBER 
 is 'refers to EXT_PURCHASEORDERLINE_ID'; 
comment on column ERP_I_POCONFIRMATION.SEQUENTIALNUMBER 
 is 'refers to LINESEQUENTIALNUMBER'; 
comment on column ERP_I_POCONFIRMATION.NETVALUE 
 is 'confirmed purchase price will be processed by MAWI-Web'; 
comment on column ERP_I_POCONFIRMATION.CREATIONDATE 
 is 'creation date/time of the confirmation will be processed by MAWI-Web'; 
comment on column ERP_I_POCONFIRMATION.CREATIONTIME 
 is 'creation date/time of the confirmation will be processed by MAWI-Web'; 
comment on column ERP_I_POCONFIRMATION.EXTDOCUMENTNUMBER 
 is 'reference number will be processed by MAWI-Web'; 
alter table ERP_I_POCONFIRMATION 
add constraint PK_ERP_I_POCONFIRMATION primary key (TRANSFER_ID); 
 
create table ERP_I_POCONFIRMATION_LOG 
( 
TRANSFER_ID     NUMBER NOT NULL, 
TRANSFER_STATUS NUMBER NOT NULL, 
TEXT            VARCHAR2(1000), 
PROCESS_STATUS  NUMBER NOT NULL, 
TIMESTAMP       DATE NOT NULL 
); 
-- Add comments to the columns 
comment on column ERP_I_POCONFIRMATION_LOG.transfer_status 
is '0 = ready to be read, 1 = read correctly'; 
comment on column ERP_I_POCONFIRMATION_LOG.process_status 
is '0 = processed correctly, 1 = process stopped with errors'; 
-- Create/Recreate indexes 
create index INDX_ERP_I_POCONFIRMATION_LOG on ERP_I_POCONFIRMATION_LOG (TIMESTAMP); 
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_POCONFIRMATION_LOG 
add constraint PK_ERP_I_POCONFIRMATION_LOG primary key (TRANSFER_ID); 
alter table ERP_I_POCONFIRMATION_LOG 
add constraint FK_ERP_I_POCONFIRMATION_LOG foreign key (TRANSFER_ID) 
references ERP_I_POCONFIRMATION(TRANSFER_ID) on delete cascade;

---CWS ERP Table adding script

-- Create table ERP_O_PURCHASEORDERHEADER
create table ERP_O_PURCHASEORDERHEADER
(
  transfer_id            NUMBER not null,
  transfer_status        NUMBER 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,
  statuscode             VARCHAR2(2) not null,
  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) not null,
  approvaluser           VARCHAR2(20),
  canceluser             VARCHAR2(20),
  orderuser              VARCHAR2(20),
  confirmeduser          VARCHAR2(20),
  exportuser             VARCHAR2(20),
  exportdate             DATE,
  exportbatchnumber      NUMBER
)
;
-- Add comments to the columns 
comment on column ERP_O_PURCHASEORDERHEADER.statuscode
  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_O_PURCHASEORDERHEADER.ordertype
  is '0 = Manual / no AC / not applicable 1 = Personalized order 2 = Standard order';
comment on column ERP_O_PURCHASEORDERHEADER.purchaseordertype
  is '0 = External 1 = Internal';
comment on column ERP_O_PURCHASEORDERHEADER.origin
  is '1 = Purchase request 2 = Manual created 3 = Conversion 4 = Work order approval 5 = Work order status control';
comment on column ERP_O_PURCHASEORDERHEADER.priority
  is '1 = Replacement purchase order 2 = Others 3 = Direct orders';
comment on column ERP_O_PURCHASEORDERHEADER.exportbatchnumber
  is 'This field will be used to pass the EXTERNAL ORACLE PO number!';
-- Create/Recreate indexes 
create index INDX_ERP_O_PURCHASEORDERHEADER on ERP_O_PURCHASEORDERHEADER (PURCHASEORDERNUMBER);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_PURCHASEORDERHEADER
  add constraint PK_ERP_O_PURCHASEORDERHEADER primary key (TRANSFER_ID);
-- Create/Recreate check constraints 
alter table ERP_O_PURCHASEORDERHEADER
  add constraint CHK_ERP_O_PURCHASEORDERHEAD_OI
  check (ORIGIN between 1 and 5);
alter table ERP_O_PURCHASEORDERHEADER
  add constraint CHK_ERP_O_PURCHASEORDERHEAD_OR
  check (ORDERTYPE IN (0,1,2));
alter table ERP_O_PURCHASEORDERHEADER
  add constraint CHK_ERP_O_PURCHASEORDERHEAD_OT
  check (PURCHASEORDERTYPE IN (0,1));
alter table ERP_O_PURCHASEORDERHEADER
  add constraint CHK_ERP_O_PURCHASEORDERHEAD_PR
  check (PRIORITY in (1,2,3));
alter table ERP_O_PURCHASEORDERHEADER
  add constraint CHK_ERP_O_PURCHASEORDERHEAD_WO
  check (PROCESSEDTOWORKORDER IN ('Y','N'));

create table ERP_O_PURCHASEORDERLINE
(
  transfer_id              NUMBER not null,
  transfer_status          NUMBER not null,
  ext_purchaseorderline_id NUMBER not null,
  purchaseordernumber      NUMBER not null,
  purchaseorderline_id     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),
  uniqueitem               VARCHAR2(1) not null
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_PURCHASEORDERLINE
  add constraint PK_ERP_O_PURCHASEORDERLINE primary key (TRANSFER_ID, PURCHASEORDERLINE_ID);
alter table ERP_O_PURCHASEORDERLINE
  add constraint FK_ERP_O_PURCHASEORDERLINE foreign key (TRANSFER_ID)
  references ERP_O_PURCHASEORDERHEADER (TRANSFER_ID) on delete cascade;

-- Create table ERP_O_PURCHASEORDER_LOG
create table ERP_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 ERP_O_PURCHASEORDER_LOG.transfer_status
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_O_PURCHASEORDER_LOG.process_status
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_PURCHASEORDER_LOG
  add constraint PK_ERP_O_PURCHASEORDER_LOG primary key (TRANSFER_ID);
alter table ERP_O_PURCHASEORDER_LOG
  add constraint FK_ERP_O_PURCHASEORDER_LOG foreign key (TRANSFER_ID)
  references ERP_O_PURCHASEORDERHEADER (TRANSFER_ID) on delete cascade;


-- Add column EXPORTBATCHNUMBER to ERP_I_PURCHASEORDERHEADER
alter table ERP_I_PURCHASEORDERHEADER add EXPORTBATCHNUMBER number;
-- Add comments to the columns 
comment on column ERP_I_PURCHASEORDERHEADER.EXPORTBATCHNUMBER
  is 'Will be used to fill the EXTERNAL ORACLE PO number!';


-- Add column EXPORTBATCHNUMBER to ERP_O_PURCHASEORDERHEADER
alter table ERP_O_PURCHASEORDERHEADER add EXPORTBATCHNUMBER number;
-- Add comments to the columns 
comment on column ERP_O_PURCHASEORDERHEADER.EXPORTBATCHNUMBER
  is 'This field will be used to pass the EXTERNAL ORACLE PO number!';

-- Modify column EXT_PURCHASEORDERLINE_ID to NULL allowed
alter table ERP_O_PURCHASEORDERLINE modify EXT_PURCHASEORDERLINE_ID NULL;

-- Add column to ROUTECONTROL 
alter table ROUTECONTROL add ERPINVHEXPORT VARCHAR2(1) default 'N' not null;
-- Add comments to the column 
comment on column ROUTECONTROL.ERPINVHEXPORT
  is 'Export for ERP_O_INVOICEHEADER';

create index XIE1PURCHASEORDERLINE on PURCHASEORDERLINE (PURCHASEORDERHEADER_ID, EXT_PURCHASEORDERLINE_ID, LINESEQUENTIALNUMBER);