--ABS-73805

------------ ERP_I_ORPACK ------------
CREATE TABLE ERP_I_ORPACK
( TRANSFER_ID             NUMBER NOT NULL,
  TRANSFER_STATUS         NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID52 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  BUSINESSUNITCODE        VARCHAR2(6) NOT NULL, 
  PRIMARY_ID              VARCHAR2(24) NOT NULL,
  PRODUCTCODE             VARCHAR2(12) NOT NULL,
  SIZECODE                VARCHAR2(12) DEFAULT '-' NOT NULL,
  REASONCODE              VARCHAR2(6) DEFAULT 'R1',
  CURRENCYCODE            VARCHAR2(3) DEFAULT 'EUR',
  CREATIONTIMESTAMP       DATE DEFAULT SYSDATE NOT NULL,
  CREATIONEMPLOYEENUMBER  NUMBER NOT NULL,
  ASSEMBLETIMESTAMP       DATE NOT NULL,
  ASSEMBLYLAYOUT          VARCHAR2(6) NULL,
  ASSEMBLEEMPLOYEENUMBER  NUMBER NOT NULL, 
  STOCKEXPIRATIONDATE     DATE NULL,
  STERILIZERCODE          VARCHAR2(6) NULL, 
  STERILIZATIONEMPLOYEENUMBER NUMBER NULL, 
  STERILIZATIONTIMESTAMP  DATE NULL,  
  CARTNUMBER              NUMBER NULL,
  HIBC_CODE               VARCHAR2(32) NULL,
  DELIVERY_BUSINESSUNIT   VARCHAR2(6) NOT NULL);

ALTER TABLE ERP_I_ORPACK
  ADD CONSTRAINT  PK_ERP_I_ORPACK PRIMARY KEY (TRANSFER_ID);

------------ ERP_I_ORPACK_LOG ------------
CREATE TABLE ERP_I_ORPACK_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);

COMMENT ON COLUMN ERP_I_ORPACK_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_ORPACK_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';

ALTER TABLE ERP_I_ORPACK_LOG
  ADD CONSTRAINT PK_ERP_I_ORPACK_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_ORPACK_LOG
  ADD CONSTRAINT FK_ERP_I_ORPACK_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_ORPACK (TRANSFER_ID) ON DELETE CASCADE;  
  
------------ ERP_O_CANCELEDORPACK ------------
CREATE TABLE ERP_O_CANCELEDORPACK
( TRANSFER_ID             NUMBER NOT NULL,
  TRANSFER_STATUS         NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID53 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  BUSINESSUNITCODE        VARCHAR2(6) NOT NULL,
  PRIMARY_ID              VARCHAR2(24) NOT NULL,
  HIBC_CODE               VARCHAR2(32) NULL,
  CANCELREASONCODE        VARCHAR2(6) NULL);

ALTER TABLE ERP_O_CANCELEDORPACK
  ADD CONSTRAINT PK_ERP_O_CANCELEDORPACK PRIMARY KEY (TRANSFER_ID);

------------ ERP_O_CANCELEDORPACK_LOG ------------
CREATE TABLE ERP_O_CANCELEDORPACK_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID54 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);

COMMENT ON COLUMN ERP_O_CANCELEDORPACK_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_O_CANCELEDORPACK_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';

ALTER TABLE ERP_O_CANCELEDORPACK_LOG
  ADD CONSTRAINT PK_ERP_O_CANCELEDORPACK_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_CANCELEDORPACK_LOG
  ADD CONSTRAINT FK_ERP_O_CANCELEDORPACK_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES  ERP_O_CANCELEDORPACK(TRANSFER_ID) ON DELETE CASCADE;  
  
------------ ERP_O_DELIVERYORPACK ------------
CREATE TABLE ERP_O_DELIVERYORPACK
( TRANSFER_ID             NUMBER NOT NULL,
  TRANSFER_STATUS         NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID55 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  BUSINESSUNITCODE        VARCHAR2(6) NOT NULL,
  PRIMARY_ID              VARCHAR2(24) NOT NULL,
  DELIVERYDATE            DATE NULL,
  DELIVERY_BUSINESSUNIT   VARCHAR2(6) NULL,
  HIBC_CODE               VARCHAR2(32) NULL,
  DELIVERYNOTENUMBER      NUMBER NULL,
  CUSTOMERNUMBER          NUMBER NULL,
  DEPARTMENTNUMBER        NUMBER NULL,
  CONSUMPTIONPOINTNUMBER  NUMBER NULL);

ALTER TABLE ERP_O_DELIVERYORPACK
  ADD CONSTRAINT PK_ERP_O_DELIVERYORPACK PRIMARY KEY (TRANSFER_ID);

------------ ERP_O_DELIVERYORPACK_LOG ------------
CREATE TABLE ERP_O_DELIVERYORPACK_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);

COMMENT ON COLUMN ERP_O_DELIVERYORPACK_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_O_DELIVERYORPACK_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';

ALTER TABLE ERP_O_DELIVERYORPACK_LOG
  ADD CONSTRAINT PK_ERP_O_DELIVERYORPACK_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_DELIVERYORPACK_LOG
  ADD CONSTRAINT FK_ERP_O_DELIVERYORPACK_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES  ERP_O_DELIVERYORPACK(TRANSFER_ID) ON DELETE CASCADE;  