--Order 20015066

---PURCHASEORDERHEADER table---

begin
execute immediate ('ALTER TABLE PURCHASEORDERHEADER
  ADD (ERPEXPORT VARCHAR2(1)  DEFAULT ''Y'' NOT NULL CONSTRAINT YN_ValidationField215 CHECK (ERPEXPORT IN (''Y'', ''N'')))');
exception when others then null;
end;
/
 
COMMENT ON COLUMN PURCHASEORDERHEADER.ERPEXPORT
  IS 'Exported to ERP, new records by default Y, when exported they get N';


---SHIPMENTLISTLINE table---

ALTER TABLE SHIPMENTLISTLINE
	ADD (DNGENERATED NUMBER  DEFAULT 0 NOT NULL);

COMMENT ON COLUMN SHIPMENTLISTLINE.DNGENERATED IS 'Possible values :
0 = The DN was not generated (default value)
1 = The DN was generated
';


---ERP_I_SHIPMENTNOTIFICATION table---

-- Create tables
create table ERP_I_SHIPMENTNOTIFICATION
(
  transfer_id                NUMBER not null,
  transfer_status            NUMBER default 0 not null,
  purchaseordernumber        NUMBER not null,
  purchaseorderlinenumber    varchar2(6),
  linesequentialnumber       NUMBER not null,
  supplierdeliverynotenumber NUMBER not null,
  supplierdeliverynotedate   DATE not null,
  confirmeddate              DATE not null,
  supplierproductcode        VARCHAR2(30) not null,
  suppliersizecode           VARCHAR2(20) not null,
  deliveryplant_scm          VARCHAR2(4),
  actualquantitydelivered    NUMBER not null,
  unitofmeasure_actqtydel     VARCHAR2(3) not null,
  palett                     VARCHAR2(64),
  transportunit              VARCHAR2(20),
  countryoforigin            VARCHAR2(3) not null,
  commoditycode              VARCHAR2(35) not null,
  netweight                  varchar2(18),
  totalweight                varchar2(18),
  weightunit                 varchar2(3),
  remark                     VARCHAR2(300)
);
-- Add comments to the columns 
comment on column ERP_I_SHIPMENTNOTIFICATION.purchaseordernumber
  is 'SUPPLIERORDERNUMBER';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_SHIPMENTNOTIFICATION
  add constraint PK_ERP_I_SHIPMENTNOTIFICATION primary key (TRANSFER_ID);

---ERP_I_SHIPMENTNOTIFICATION_LOG table---

create table ERP_I_SHIPMENTNOTIFICATION_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_I_SHIPMENTNOTIFICATION_LOG.transfer_status
  is '0 = ready to be read, 1 = read correctly';
comment on column ERP_I_SHIPMENTNOTIFICATION_LOG.process_status
  is '0 = processed correctly, 1 = process stopped with errors';

-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_SHIPMENTNOTIFICATION_LOG
  add constraint PK_ERP_I_SHIPMENTNOTIFTION_LOG primary key (TRANSFER_ID);

alter table ERP_I_SHIPMENTNOTIFICATION_LOG
  add constraint FK_ERP_I_SHIPMENTNOTIFICAT_LOG foreign key (TRANSFER_ID)
  references ERP_I_SHIPMENTNOTIFICATION (TRANSFER_ID) on delete cascade;


