--ABS-78465 STOP VALUE CALCULATION
CREATE SEQUENCE SEQ_SQ_STOPVALUE
START WITH 1
INCREMENT BY 1;

CREATE TABLE SQ_STOPVALUE
(
  SQ_STOPVALUE_ID         NUMBER NOT NULL,
  INVOICEBUSINESSUNIT_ID  NUMBER NOT NULL,
  SQ_DATE                 DATE NOT NULL,
  INVOICEYEAR             NUMBER NOT NULL,
  INVOICEWEEK             NUMBER NOT NULL,
  DELTOCUSTOMER_LINK_ID   NUMBER NOT NULL,
  TURNOVERGROUP_ID        NUMBER NULL,
  INVOICELINE_CODE        VARCHAR2(16) NOT NULL,
  INVOICELINE_DESC        VARCHAR2(40) NOT NULL,
  SIZECODE                VARCHAR2(12) NULL,
  DELIVERYSCHEME_ID       NUMBER NULL,
  CURRENCY_ID             NUMBER NOT NULL,
  INVOICE_AMOUNT          NUMBER NULL,
  EXPECTED_REVENUE        NUMBER DEFAULT 0 NOT NULL,
  AVGERAGE_INVOICE_AMOUNT NUMBER DEFAULT 0 NOT NULL,
  AVERAGEWEEKS            NUMBER DEFAULT 4 NOT NULL,
  PROCES_STATUS           NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_SQ_STOPVALUE_V1 CHECK (PROCES_STATUS IN (-1,0,1)),
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE DEFAULT SYSDATE NOT NULL  
);
ALTER TABLE SQ_STOPVALUE
  ADD CONSTRAINT XPKSQ_STOPVALUE PRIMARY KEY (SQ_STOPVALUE_ID);
ALTER TABLE SQ_STOPVALUE
  ADD CONSTRAINT UK1_SQ_STOPVALUE UNIQUE (INVOICEBUSINESSUNIT_ID,INVOICEYEAR,INVOICEWEEK,DELTOCUSTOMER_LINK_ID,TURNOVERGROUP_ID,INVOICELINE_CODE,INVOICELINE_DESC,SIZECODE);
ALTER TABLE SQ_STOPVALUE
  ADD CONSTRAINT UK2_SQ_STOPVALUE UNIQUE (INVOICEBUSINESSUNIT_ID, SQ_DATE, DELTOCUSTOMER_LINK_ID,TURNOVERGROUP_ID,INVOICELINE_CODE,INVOICELINE_DESC,SIZECODE);
CREATE INDEX INDX_SQ_STOPVALUE_BUSS ON SQ_STOPVALUE(INVOICEBUSINESSUNIT_ID); 
CREATE INDEX INDX_SQ_STOPVALUE_DELT ON SQ_STOPVALUE(DELTOCUSTOMER_LINK_ID);
CREATE INDEX INDX_SQ_STOPVALUE_TURN ON SQ_STOPVALUE(TURNOVERGROUP_ID);
CREATE INDEX INDX_SQ_STOPVALUE_CURR ON SQ_STOPVALUE(CURRENCY_ID);
ALTER TABLE SQ_STOPVALUE
  ADD CONSTRAINT FK_SQ_STOPVALUE_BUSS FOREIGN KEY (INVOICEBUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT(BUSINESSUNIT_ID) ON DELETE CASCADE;
ALTER TABLE SQ_STOPVALUE
  ADD CONSTRAINT FK_SQ_STOPVALUE_DELT FOREIGN KEY (DELTOCUSTOMER_LINK_ID)
  REFERENCES CUSTOMER_LINK(CUSTOMER_LINK_ID) ON DELETE CASCADE;  
ALTER TABLE SQ_STOPVALUE
  ADD CONSTRAINT FK_SQ_STOPVALUE_TURN FOREIGN KEY (TURNOVERGROUP_ID)
  REFERENCES TURNOVERGROUP(TURNOVERGROUP_ID) ON DELETE CASCADE;
ALTER TABLE SQ_STOPVALUE
  ADD CONSTRAINT FK_SQ_STOPVALUE_CURR FOREIGN KEY (CURRENCY_ID)
  REFERENCES CURRENCY(CURRENCY_ID) ON DELETE CASCADE;  

--ERP_O_STOPVALUE--
CREATE TABLE ERP_O_STOPVALUE
(
  TRANSFER_ID             NUMBER NOT NULL,
  TRANSFER_STATUS         NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_O_STOPVALUE_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  BUSINESS_UNIT           VARCHAR2(6) NOT NULL,
  YEAR                    NUMBER(4) NOT NULL,
  WEEK                    NUMBER(2) NOT NULL,
  CUSTOMERNUMBER          NUMBER NOT NULL,
  LEGALNAME               VARCHAR2(80) NULL,
  TURNOVERGROUPCODE       VARCHAR2(12) NULL,
  CODE                    VARCHAR2(16) NOT NULL,
  DESCRIPTION             VARCHAR2(40) NOT NULL,
  SIZECODE                VARCHAR2(12) NULL,
  DELIVERYSCHEMECODE      VARCHAR2(6) NULL,
  DELIVERY_FREQUENCY      NUMBER DEFAULT 1 NOT NULL,
  CURRENCY_CODE           VARCHAR2(3) NOT NULL,
  INVOICE_AMOUNT          NUMBER NULL,
  EXPECTED_REVENUE        NUMBER DEFAULT 0 NOT NULL,
  AVGERAGE_INVOICE_AMOUNT NUMBER DEFAULT 0 NOT NULL,
  AVERAGEWEEKS            NUMBER DEFAULT 4 NOT NULL 
);
ALTER TABLE ERP_O_STOPVALUE
  ADD CONSTRAINT XPKERP_O_STOPVALUE PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX_ERP_O_STOPVALUE_STAT ON ERP_O_STOPVALUE(TRANSFER_STATUS);

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