--ABS-109567
CREATE TABLE ERP_I_INFOUSA
(
TRANSFER_ID     NUMBER NOT NULL,
TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL 
 CONSTRAINT CHK_ERP_I_INFOUSA_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
CUSTOMERNUMBER  NUMBER NOT NULL, 
CUSTOMERNAME    VARCHAR2(40) NOT NULL,
STREET          VARCHAR2(30) NULL,
CITY          VARCHAR2(30) NOT NULL,
STATE          VARCHAR2(20) NULL,
ZIPCODE          VARCHAR2(15) NULL,
SICCODE          VARCHAR2(4) NOT NULL,
SICDESC         VARCHAR2(128) NULL,
NAICSCODE       VARCHAR2(8) NOT NULL,
NAICSDESC       VARCHAR2(40) NULL, 
TIMESTAMP DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_INFOUSA.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_I_INFOUSA
 ADD CONSTRAINT PK_ERP_I_INFOUSA PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIF1ERP_I_INFOUSA ON ERP_I_INFOUSA(TRANSFER_STATUS);
CREATE INDEX XIF2ERP_I_INFOUSA ON ERP_I_INFOUSA(CUSTOMERNUMBER);

CREATE TABLE ERP_I_INFOUSA_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_I_INFOUSA_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_INFOUSA_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_INFOUSA_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_INFOUSA_LOG
  ADD CONSTRAINT PK_ERP_I_INFOUSA_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_INFOUSA_LOG
  ADD CONSTRAINT FK_ERP_I_INFOUSA_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_INFOUSA(TRANSFER_ID) ON DELETE CASCADE;
  
CREATE TABLE ERP_O_INFOUSA
(
TRANSFER_ID     NUMBER NOT NULL,
TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL 
 CONSTRAINT CHK_ERP_O_INFOUSA_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
CUSTOMERNUMBER  NUMBER NOT NULL, 
CUSTOMERNAME    VARCHAR2(40) NOT NULL,
STREET	        VARCHAR2(30) NULL,
CITY	        VARCHAR2(30) NOT NULL,
STATE	        VARCHAR2(20) NULL,
ZIPCODE	        VARCHAR2(15) NULL,
SICCODE	        VARCHAR2(4) NOT NULL,
SICDESC         VARCHAR2(128) NULL,
NAICSCODE       VARCHAR2(8) NOT NULL,
NAICSDESC       VARCHAR2(40) NULL, 
TIMESTAMP DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_O_INFOUSA.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_O_INFOUSA
 ADD CONSTRAINT PK_ERP_O_INFOUSA PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIF1ERP_O_INFOUSA ON ERP_O_INFOUSA(TRANSFER_STATUS);
CREATE INDEX XIF2ERP_O_INFOUSA ON ERP_O_INFOUSA(CUSTOMERNUMBER);

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