-- ABS-86084 ERP_O_WEEKLYREVENUE --
CREATE TABLE ERP_O_WEEKLYREVENUE
(
TRANSFER_ID NUMBER NOT NULL,
TRANSFER_STATUS NUMBER NOT NULL,
INVOICEBUSINESSUNITCODE VARCHAR2(6) NOT NULL,
SQ_DATE DATE NOT NULL,
INVOICEYEAR NUMBER NOT NULL,
INVOICEWEEK NUMBER NOT NULL,
DELTOCUSTOMERNUMBER NUMBER NOT NULL,
TURNOVEROVERGROUPCODE VARCHAR2(12) NULL,
INVOICELINE_CODE VARCHAR2(16) NOT NULL,
INVOICELINE_DESC VARCHAR2(40) NOT NULL,
SIZECODE VARCHAR2(12) NULL,
DELIVERYSCHEMECODE VARCHAR2(6) NULL,
CURRENCYCODE VARCHAR2(3) NOT NULL,
INVOICE_AMOUNT NUMBER DEFAULT NULL,
WEEKLY_REVENUE NUMBER DEFAULT 0 NOT NULL,
WEEKLY_AVGERAGE_REVENUE NUMBER NULL,
AVERAGEWEEKS NUMBER NOT NULL,
SQ_WEEKLY_REVENUE_TYPE NUMBER(1) DEFAULT 1 NOT NULL,
FINISHINGMETHODCODE VARCHAR2(6) DEFAULT NULL,
SQ_WEEKCYCLES NUMBER DEFAULT NULL,
BILLINGMETHOD NUMBER DEFAULT NULL,
INVOICELINETYPE NUMBER DEFAULT NULL,
INVOICELINESUBTYPE NUMBER DEFAULT NULL
);
COMMENT ON COLUMN ERP_O_WEEKLYREVENUE.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_O_WEEKLYREVENUE.SQ_WEEKLY_REVENUE_TYPE
  IS '1 = Based on invoice 2 = Copied from week in past 3 = Uninstalled customer estimate';
ALTER TABLE ERP_O_WEEKLYREVENUE
  ADD CONSTRAINT XPKERP_O_WEEKLYREVENUE PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX_ERP_O_WEEKLYREVENUE_STAT ON ERP_O_WEEKLYREVENUE(TRANSFER_STATUS);  
CREATE INDEX INDX_ERP_O_WEEKLYREVENUE_01 ON ERP_O_WEEKLYREVENUE(INVOICEBUSINESSUNITCODE,SQ_DATE);

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

--CLEANUP ABOVE TABLES REPLACE ERP_O_STOPVALUE/LOG TABLES
DROP TABLE ERP_O_STOPVALUE_LOG;
DROP TABLE ERP_O_STOPVALUE;