--ABS-157195
CREATE TABLE ERP_O_CONS_REJECTS
(
  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_CONS_REJECTS ADD CONSTRAINT PK_ERP_O_CONS_REJECTS PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX_ERP_O_CONS_REJECTS_STATUS ON ERP_O_CONS_REJECTS (TRANSFER_STATUS);

CREATE TABLE ERP_O_CONS_REJECTSLINE
(
  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,
  REJECTEDQTY                 NUMBER NULL,
  TIMESTAMP                   DATE DEFAULT SYSDATE NOT NULL
);
CREATE INDEX INDX1_ERP_O_CONS_REJECTSLINE ON ERP_O_CONS_REJECTSLINE (TRANSFER_ID);
ALTER TABLE ERP_O_CONS_REJECTSLINE ADD CONSTRAINT PK_ERP_O_CONS_REJECTSLINE PRIMARY KEY (TRANSFER_ID, PRODUCTCODE, SIZECODE, FINISHINGMETHOD);
ALTER TABLE ERP_O_CONS_REJECTSLINE
  ADD CONSTRAINT FK_ERP_O_CONS_REJECTSLINE FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_CONS_REJECTS (TRANSFER_ID) ON DELETE CASCADE;


CREATE TABLE ERP_O_CONS_REJECTS_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_CONS_REJECTS_LOG.TRANSFER_STATUS
  IS '0=ready to be read, 1=read correctly';
COMMENT ON COLUMN ERP_O_CONS_REJECTS_LOG.PROCESS_STATUS
  IS '0=processed correctly, 1=process stopped with errors';
ALTER TABLE ERP_O_CONS_REJECTS_LOG
  ADD CONSTRAINT PK_ERP_O_CONS_REJECTS_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_CONS_REJECTS_LOG
  ADD CONSTRAINT FK_ERP_O_CONS_REJECTS_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_CONS_REJECTS (TRANSFER_ID) ON DELETE CASCADE;
