-- ABS-86084 ERP_O_CUSTOMER_WEEKLYREVENUE
CREATE TABLE ERP_O_CUSTOMER_WEEKLYREVENUE
(
TRANSFER_ID             NUMBER NOT NULL,
TRANSFER_STATUS         NUMBER NOT NULL,
INVOICEBUSINESSUNITCODE VARCHAR2(6) NOT NULL,
SQ_DATE                 DATE NOT NULL,
ROUTECODE               VARCHAR2(6) NULL,
WEEKENDINGDATE          DATE NULL,
WEEKLY_AVGERAGE_REVENUE NUMBER NULL,
DRIVERNUMBER            NUMBER NULL,
ASCCODE                 VARCHAR2(3) NULL,
CUSTOMERNUMBER          NUMBER NULL,
REGIONCODE              VARCHAR2(2) NULL,
MASTERACCOUNT           NUMBER NULL,
CONTRACTOWNER           NUMBER NULL,
CONTRACTCODE            VARCHAR2(7) NULL,
CONTRACTEXPIRATIONDATE  DATE NULL,
BILLTOOWNER             NUMBER NULL,
BILLTOCUSTNUMBER        NUMBER NULL,
LOSTDATE                DATE NULL,
CONTRACTSIGNDATE        DATE NULL,
CUSTOMERINSTALLDATE     DATE NULL,
CONTRACTINSTALLDATE     DATE NULL,
SOURCESYSTEM            VARCHAR2(25) NULL,
ESB_BATCH_ID            NUMBER NULL,
ESB_INST_ID             VARCHAR2(40) NULL,
ESB_TRANSFER_ID         NUMBER NULL,
ESB_ERR_CODE            VARCHAR2(20) NULL,
ESB_ERR_DESC            VARCHAR2(200) NULL,
ESB_STATUS              VARCHAR2(1) NULL,
CHANGEPROGRAM           VARCHAR2(10) NULL,
CHANGEUSER              VARCHAR2(10) NULL,
CHANGETIME              DATE NULL
);
COMMENT ON COLUMN ERP_O_CUSTOMER_WEEKLYREVENUE.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_O_CUSTOMER_WEEKLYREVENUE
  ADD CONSTRAINT XPKERP_O_CUST_WEEKLYREVENUE PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX_ERP_O_CUST_WEEKLYREVENUE1 ON ERP_O_CUSTOMER_WEEKLYREVENUE(TRANSFER_STATUS);  
CREATE INDEX INDX_ERP_O_CUST_WEEKLYREVENUE2 ON ERP_O_CUSTOMER_WEEKLYREVENUE(INVOICEBUSINESSUNITCODE,SQ_DATE);

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

--ABS-77806 ERP_O_ARTRANSACTION
ALTER TABLE ERP_O_ARTRANSACTION DROP COLUMN ARTRANSACTION_LEGACYCODE;
ALTER TABLE ERP_O_ARTRANSACTION RENAME COLUMN BILL2BUSINESSUNITCODE TO BILLTOBUSINESSUNITCODE;
ALTER TABLE ERP_O_ARTRANSACTION RENAME COLUMN BILL2LEGALENTITYCODE TO BILLTOLEGALENTITYCODE;
ALTER TABLE ERP_O_ARTRANSACTION RENAME COLUMN CUSTOMERNUMBER TO DELTOCUSTOMERNUMBER;
ALTER TABLE ERP_O_ARTRANSACTION RENAME COLUMN NAME TO DELTONAME;
ALTER TABLE ERP_O_ARTRANSACTION RENAME COLUMN CURRENCY TO CURRENCYCODE;
ALTER TABLE ERP_O_ARTRANSACTION RENAME COLUMN BILLTO_CUSTOMER TO BILLTOCUSTOMER;
ALTER TABLE ERP_O_ARTRANSACTION RENAME COLUMN BILLTO_NAME TO BILLTONAME;
