--From PVD: EUT, ERP tables extensions software order 20016030

-- Add columns ERP_O_WORKORDERLINE
alter table ERP_O_WORKORDERLINE add productsap VARCHAR2(24);
alter table ERP_O_WORKORDERLINE add productasset VARCHAR2(24);
-- Add comments to the columns 
comment on column ERP_O_WORKORDERLINE.productsap
  is 'Optional SAP code';
comment on column ERP_O_WORKORDERLINE.productasset
  is 'Optional ASSET code';
  
  -- Add columns ERP_O_WORKORDERLINERES
alter table ERP_O_WORKORDERLINERES add productsap VARCHAR2(24);
alter table ERP_O_WORKORDERLINERES add productasset VARCHAR2(24);
-- Add comments to the columns 
comment on column ERP_O_WORKORDERLINERES.productsap
  is 'Optional SAP code';
comment on column ERP_O_WORKORDERLINERES.productasset
  is 'Optional ASSET code';

  -- Add columns ERP_O_STOCKTRANSACTION
alter table ERP_O_STOCKTRANSACTION add productsap VARCHAR2(24);
alter table ERP_O_STOCKTRANSACTION add productasset VARCHAR2(24);
-- Add comments to the columns 
comment on column ERP_O_STOCKTRANSACTION.productsap
  is 'Optional SAP code';
comment on column ERP_O_STOCKTRANSACTION.productasset
  is 'Optional ASSET code';
  
  -- Add columns ERP_O_INVOICELINE
alter table ERP_O_INVOICELINE add productsap VARCHAR2(24);
alter table ERP_O_INVOICELINE add productasset VARCHAR2(24);
-- Add comments to the columns 
comment on column ERP_O_INVOICELINE.productsap
  is 'Optional SAP code';
comment on column ERP_O_INVOICELINE.productasset
  is 'Optional ASSET code';  
  
-- Add columns ERP_I_PURCHASEORDERLINE
alter table ERP_I_PURCHASEORDERLINE add productsap VARCHAR2(24);
alter table ERP_I_PURCHASEORDERLINE add productasset VARCHAR2(24);
-- Add comments to the columns 
comment on column ERP_I_PURCHASEORDERLINE.productsap
  is 'Optional SAP code';
comment on column ERP_I_PURCHASEORDERLINE.productasset
  is 'Optional ASSET code';
  
-- Create table ERP_O_WORKORDERLINE_ISSUE
create table ERP_O_WORKORDERLINE_ISSUE
(
  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,
  customernumber	 NUMBER not null,
  wearernumber           NUMBER,
  wearername             VARCHAR2(40),
  weareremploymentnumber NUMBER,
  costcentercode         VARCHAR2(12),
  productcode            VARCHAR2(12) not null,
  sizecode               VARCHAR2(12) not null,
  finishingmethodcode    VARCHAR2(6) not null,
  productsap             VARCHAR2(24),
  productasset           VARCHAR2(24),
  primaryid              VARCHAR2(24) not null
);
-- Add comments to the columns 
comment on column ERP_O_WORKORDERLINE_ISSUE.productcode
  is 'Product from stockreservation per grade/stockdefinition per item';
comment on column ERP_O_WORKORDERLINE_ISSUE.sizecode
  is 'Same as product via stock table';
comment on column ERP_O_WORKORDERLINE_ISSUE.finishingmethodcode
  is 'Same as product via stock table';
comment on column ERP_O_WORKORDERLINE_ISSUE.productsap
  is 'Optional SAP code';
comment on column ERP_O_WORKORDERLINE_ISSUE.productasset
  is 'Optional ASSET code';
comment on column ERP_O_WORKORDERLINE_ISSUE.wearername
  is 'Wearer Full name';
comment on column ERP_O_WORKORDERLINE_ISSUE.costcentercode
  is 'Via distributionpoint issuehistory
This is the SAP sold to party';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_WORKORDERLINE_ISSUE
  add constraint PK_ERP_O_WORKORDERLINE_ISSUE primary key (TRANSFER_ID, WORKORDERLINE_ID, PRIMARYID);
alter table ERP_O_WORKORDERLINE_ISSUE
  add constraint FK_ERP_O_WORKORDERLINE_ISSUE foreign key (TRANSFER_ID)
  references ERP_O_WORKORDER (TRANSFER_ID) on delete cascade;
  

-- Create table ERP_O_RAGGEDITEM
create table ERP_O_RAGGEDITEM
(
  transfer_id           NUMBER not null,
  transfer_status       NUMBER default 0 not null,
  businessunitcode      VARCHAR2(6) not null,
  customernumber        NUMBER, 
  departmentnumber      NUMBER,
  productcode           VARCHAR2(12),
  sizecode              VARCHAR2(12),
  raggeditem            VARCHAR2(24) not null,
  ragdate               DATE not null,
  ragyearmonth          NUMBER(4),
  purchasedate          DATE,
  purchaseyearmonth     NUMBER(4),
  lastonstockdate       DATE,
  firstonstockdate      DATE,
  firstonstockyearmonth NUMBER(4),
  ragreasoncode         VARCHAR2(6),
  purchaseprice         NUMBER,
  productsap            VARCHAR2(24),
  productasset          VARCHAR2(24)
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_RAGGEDITEM
  add constraint PK_ERP_O_RAGGEDITEM primary key (TRANSFER_ID);
-- Create table ERP_O_RAGGEDITEM_LOG
create table ERP_O_RAGGEDITEM_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_RAGGEDITEM_LOG.transfer_status
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_O_RAGGEDITEM_LOG.process_status
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate indexes 
create index INDX_ERP_O_RAGGEDITEM_LO_LOG on ERP_O_RAGGEDITEM_LOG (TIMESTAMP);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_RAGGEDITEM_LOG
  add constraint PK_ERP_O_RAGGEDITEM_LOG primary key (TRANSFER_ID);
alter table ERP_O_RAGGEDITEM_LOG
  add constraint FK_ERP_O_RAGGEDITEM_LOG foreign key (TRANSFER_ID)
  references ERP_O_RAGGEDITEM (TRANSFER_ID) on delete cascade;

-- Create table ERP_O_RESIDUALVALUE
create table ERP_O_RESIDUALVALUE
(
  transfer_id            NUMBER not null,
  transfer_status        NUMBER default 0 not null,
  customernumber         NUMBER not null,
  departmentnumber       NUMBER not null,
  productcode            VARCHAR2(12) not null,
  sizecode               VARCHAR2(12) not null,
  residualvaluedate      DATE not null,
  residualvalueyearmonth NUMBER(4) not null,
  purchaseprice          NUMBER not null,
  residualvalue          NUMBER not null,
  productsap             VARCHAR2(24),
  productasset           VARCHAR2(24)
);
-- Add comments to the columns 
comment on column ERP_O_RESIDUALVALUE.residualvalueyearmonth
  is 'YYYYMM format of the residual value date';
comment on column ERP_O_RESIDUALVALUE.residualvalue
  is 'From the SQ for residual value';
comment on column ERP_O_RESIDUALVALUE.productsap
  is 'SAP Product code, via ERP_C_PRODUCT';
comment on column ERP_O_RESIDUALVALUE.productasset
  is 'SAP Asset code, via ERP_C_PRODUCT';
alter table ERP_O_RESIDUALVALUE
  add constraint PK_ERP_O_RESIDUALVALUE primary key (TRANSFER_ID);  
  
-- Create table ERP_O_RESIDUALVALUE_LOG
create table ERP_O_RESIDUALVALUE_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_RESIDUALVALUE_LOG.transfer_status
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_O_RESIDUALVALUE_LOG.process_status
  is '0 = processed correctly, 1 = process stopped with errors';
-- Create/Recreate indexes 
create index INDX1_ERP_O_RESIDUALVALUE_LOG on ERP_O_RESIDUALVALUE_LOG (TIMESTAMP);
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_RESIDUALVALUE_LOG
  add constraint PK_ERP_O_RESIDUALVALUE_LOG primary key (TRANSFER_ID);
alter table ERP_O_RESIDUALVALUE_LOG
  add constraint FK_ERP_O_RESIDUALVAL_LOG foreign key (TRANSFER_ID)
  references ERP_O_RESIDUALVALUE (TRANSFER_ID) on delete cascade;

-- Create table ERP_C_CUSTOMER
create table ERP_C_CUSTOMER
(
  CUSTOMERNUMBER NUMBER not null,
  COMPANY        VARCHAR2(12) not null,
  CONTRACT        VARCHAR2(24) not null 
);
-- Add comments to the columns 
comment on column ERP_C_CUSTOMER.CUSTOMERNUMBER
  is 'Customer number';
comment on column ERP_C_CUSTOMER.COMPANY
  is 'Company or Sales Organisation';
comment on column ERP_C_CUSTOMER.CONTRACT
  is 'ERP contract code';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_C_CUSTOMER
  add constraint PK_ERP_C_CUSTOMER primary key (CUSTOMERNUMBER);

-- Create table ERP_C_PRODUCT
create table ERP_C_PRODUCT
(
  PRODUCTCODE  VARCHAR2(12) not null,
  SIZECODE     VARCHAR2(12) not null,
  PRODUCTSAP   VARCHAR2(24) not null,
  PRODUCTASSET VARCHAR2(24) not null
);
-- Add comments to the columns 
comment on column ERP_C_PRODUCT.productcode
  is 'ABS Product code';
comment on column ERP_C_PRODUCT.SIZECODE
  is 'ABS Size code';
comment on column ERP_C_PRODUCT.PRODUCTSAP
  is 'SAP Product code';
comment on column ERP_C_PRODUCT.PRODUCTASSET
  is 'SAP Asset code';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_C_PRODUCT
  add constraint PK_ERP_C_PRODUCT primary key (PRODUCTCODE, SIZECODE);

-- Add/modify columns 
alter table ERP_O_INVOICEHEADER add company varchar2(12);
alter table ERP_O_INVOICEHEADER add contract varchar2(24);
-- Add comments to the columns 
comment on column ERP_O_INVOICEHEADER.company
  is 'Via table ERP_C_CUSTOMER, Company or Sales Organisation, of the del to customer';
comment on column ERP_O_INVOICEHEADER.contract
  is 'Via table ERP_C_CUSTOMER, ERP contract code of the del to customer';

-- Add/modify columns 
alter table RAGGEDITEM add exported varchar2(1) default 'N' not null;

