--ABS-105846
CREATE TABLE ERP_I_INACTIVATE_CUSTOMER
(
TRANSFER_ID           NUMBER NOT NULL,
TRANSFER_STATUS       NUMBER DEFAULT 0 NOT NULL 
 CONSTRAINT CHK_ERP_INACTIVATECUST_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
LOST_CUSTOMERNUMBER   NUMBER NOT NULL,
LOST_DATE             DATE NOT NULL,
LOST_REASON           VARCHAR2(6), 
LOST_COMPETITORCODE   VARCHAR2(6),
MARKWEARERINACTIVE_YN VARCHAR2(1)
 CONSTRAINT CHK_ERP_INACTIVATECUST_V2 CHECK (MARKWEARERINACTIVE_YN IN ('Y','N')),
FLAGITEMSCUSTOMER_YN  VARCHAR2(1)
 CONSTRAINT CHK_ERP_INACTIVATECUST_V3 CHECK (FLAGITEMSCUSTOMER_YN IN ('Y','N')),
FLAG_STARTDATE        DATE,
TIMESTAMP             DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_INACTIVATE_CUSTOMER.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_I_INACTIVATE_CUSTOMER
 ADD CONSTRAINT PK_ERP_I_INACTIVATECUST PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIF1ERP_I_INACTIVATECUST ON ERP_I_INACTIVATE_CUSTOMER(TRANSFER_STATUS);
CREATE INDEX XIF2ERP_I_INACTIVATECUST ON ERP_I_INACTIVATE_CUSTOMER(LOST_CUSTOMERNUMBER);

CREATE TABLE ERP_I_INACTIVATE_CUSTOMER_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_I_INACTIVATECUST_LOG_V1 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_I_INACTIVATE_CUSTOMER_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_INACTIVATE_CUSTOMER_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_INACTIVATE_CUSTOMER_LOG
  ADD CONSTRAINT PK_ERP_I_INACTIVATECUST_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_INACTIVATE_CUSTOMER_LOG
  ADD CONSTRAINT FK_ERP_I_INACTIVATECUST_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_INACTIVATE_CUSTOMER(TRANSFER_ID) ON DELETE CASCADE;