--ABS-162969
CREATE TABLE ERP_U_LOST_CUSTOMER
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  CUSTOMERNUMBER  NUMBER NOT NULL,
  INACTIVEDATE    DATE NOT NULL,
  LOSTREASONCODE  VARCHAR2(6) NULL,
  COMPETITORCODE  VARCHAR2(6) NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
ALTER TABLE ERP_U_LOST_CUSTOMER
  ADD CONSTRAINT PK_ERP_U_LOST_CUSTOMER PRIMARY KEY (TRANSFER_ID);
create index XIE1_ERP_U_LOST_CUSTOMER on ERP_U_LOST_CUSTOMER(TRANSFER_STATUS);
CREATE INDEX INDX_ERP_U_LOST_CUSTOMER_CUST ON ERP_U_LOST_CUSTOMER(CUSTOMERNUMBER);
COMMENT ON COLUMN ERP_U_LOST_CUSTOMER.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';

CREATE TABLE ERP_U_LOST_CUSTOMER_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
);
COMMENT ON COLUMN ERP_U_LOST_CUSTOMER_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_U_LOST_CUSTOMER_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_U_LOST_CUSTOMER_LOG
  ADD CONSTRAINT PK_ERP_U_LOST_CUSTOMER_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_U_LOST_CUSTOMER_LOG
  ADD CONSTRAINT FK_ERP_U_LOST_CUSTOMER_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_U_LOST_CUSTOMER (TRANSFER_ID) ON DELETE CASCADE;
ALTER TABLE ERP_U_LOST_CUSTOMER_LOG
  ADD CONSTRAINT CHK_ERP_U_LOST_CUSTOMER_LOG_V1
  CHECK (TRANSFER_STATUS IN (-1,0,1));
