--ABS-157195
ALTER TABLE CONSIGNMENTNOTE ADD( ERP_EXPORTED VARCHAR2(1) DEFAULT 'N' NOT NULL);
CREATE INDEX XIE4CONSIGNMENTNOTE ON CONSIGNMENTNOTE (ERP_EXPORTED);

CREATE TABLE ERP_O_CONSIGNMENTNOTE
(
  TRANSFER_ID                 NUMBER NOT NULL,
  TRANSFER_STATUS             NUMBER DEFAULT 0 NOT NULL,
  CONSIGNMENTNOTE_ID          NUMBER NOT NULL,
  CONSIGNMENTNOTENUMBER       NUMBER NOT NULL,
  BUSINESSUNITCODE            VARCHAR2(6) NOT NULL,
  DEFAULTPROCESS_BU           VARCHAR2(6) NULL,
  DEFAULTINVOICE_BU           VARCHAR2(6) NULL,
  CUSTOMERNUMBER              NUMBER NOT NULL,
  CUSTOMERNAME                VARCHAR2(40) NULL,
  DELIVERYDATE                DATE NOT NULL,
  CUSTOMER_COCNUMBER          VARCHAR2(20) NULL,
  ROUTENUMBER                 NUMBER NULL,
  STOPNUMBER                  NUMBER NULL,
  DEPARTMENTNUMBER            NUMBER NULL,
  DELIVERYPOINTCODE           VARCHAR2(12) NULL,
  TOTAL_CONTAINER_GROSSWEIGHT NUMBER NULL,
  TOTAL_CONTAINER_TAREWEIGHT  NUMBER NULL,
  TOTAL_THEORETICALWEIGHT     NUMBER NULL,
  EXTERNALORDERNUMBER         VARCHAR2(18) NULL,
  REMARK                      VARCHAR2(1000) NULL,
  TIMESTAMP                   DATE DEFAULT SYSDATE NOT NULL
);
ALTER TABLE ERP_O_CONSIGNMENTNOTE ADD CONSTRAINT PK_ERP_O_CONSIGNMENTNOTE PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX_ERP_O_CONSIGNMENTNOTE_STATUS ON ERP_O_CONSIGNMENTNOTE (TRANSFER_STATUS);

CREATE TABLE ERP_O_CONSIGNMENTNOTELINE
(
  TRANSFER_ID                 NUMBER NOT NULL,
  TRANSFER_STATUS             NUMBER DEFAULT 0 NOT NULL,
  CONSIGNMENTNOTENUMBER       NUMBER NOT NULL,  
  PRODUCT_ID                  NUMBER NOT NULL,
  PRODUCTCODE                 VARCHAR2(12) NOT NULL,
  TYPEOFMERCHANDISE           NUMBER NOT NULL,
  SIZEDEFINITION_ID           NUMBER NOT NULL,
  SIZECODE                    VARCHAR2(12) NOT NULL,  
  FINISHINGMETHOD_ID          NUMBER NOT NULL,
  FINISHINGMETHOD             VARCHAR2(6) NOT NULL,
  PRODUCTLISTPERCUSTOMER_ID   NUMBER NULL,
  THEORETICALWEIGHT           NUMBER(15,6) NULL,
  CALCULATED_WEIGHT           NUMBER NULL,
  DELIVERED_WEIGHT            NUMBER NULL,
  BUNDLEQTY                   NUMBER NULL,
  DELIVEREDQUANTITY           NUMBER NULL,
  DELIVEREDCOGQUANTITY        NUMBER NULL,
  FF_WLPCODESAP               VARCHAR2(128) NULL,
  FF_WLPDESCSAP               VARCHAR2(128) NULL,
  FF_POSCONSAP                NUMBER NULL,
  FF_POSPRODSAP               NUMBER NULL,
  TIMESTAMP                   DATE DEFAULT SYSDATE NOT NULL
);
CREATE INDEX INDX1_ERP_O_CONSIGNMENTNOTELINE ON ERP_O_CONSIGNMENTNOTELINE (TRANSFER_ID);
ALTER TABLE ERP_O_CONSIGNMENTNOTELINE ADD CONSTRAINT PK_ERP_O_CONSIGNMENTNOTELINE PRIMARY KEY (TRANSFER_ID, PRODUCTCODE, SIZECODE, FINISHINGMETHOD);
ALTER TABLE ERP_O_CONSIGNMENTNOTELINE
  ADD CONSTRAINT FK_ERP_O_CONSIGNMENTNOTELINE FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_CONSIGNMENTNOTE (TRANSFER_ID) ON DELETE CASCADE;


CREATE TABLE ERP_O_CONSIGNMENTNOTE_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_CONSIGNMENTNOTE_LOG.TRANSFER_STATUS
  IS '0=ready to be read, 1=read correctly';
COMMENT ON COLUMN ERP_O_CONSIGNMENTNOTE_LOG.PROCESS_STATUS
  IS '0=processed correctly, 1=process stopped with errors';
ALTER TABLE ERP_O_CONSIGNMENTNOTE_LOG
  ADD CONSTRAINT PK_ERP_O_CONSIGNMENTNOTE_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_CONSIGNMENTNOTE_LOG
  ADD CONSTRAINT FK_ERP_O_CONSIGNMENTNOTE_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_CONSIGNMENTNOTE (TRANSFER_ID) ON DELETE CASCADE;
