--ABS-146169

-- ADD/MODIFY COLUMNS ERP_I_CUSTOMER
ALTER TABLE ERP_I_CUSTOMER ADD (CHARGERESIDUALVALUE VARCHAR2(1) DEFAULT 'N' NOT NULL);
ALTER TABLE ERP_I_CUSTOMER ADD (DISTRICT NUMBER DEFAULT 0 NOT NULL);
ALTER TABLE ERP_I_CUSTOMER ADD (STATEMENTFLAGCODE NUMBER DEFAULT 0 NOT NULL);
-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN ERP_I_CUSTOMER.STATEMENTFLAGCODE IS '0 = Customer pays by invoice 2 = Send to plant 3 = Monthly invoices 5 = Multiple statements mailed together 6 = Customer requires statements 7 = Weekly invoices';
-- CREATE/RECREATE CHECK CONSTRAINTS 
ALTER TABLE ERP_I_CUSTOMER ADD CONSTRAINT CHK_ERP_I_CUST_STATEMENTFLAGCODE  CHECK (STATEMENTFLAGCODE BETWEEN 0 AND 7);

--ERP_I_CONTRACTSPERCUSTOMER

ALTER TABLE ERP_I_CONTRACTSPERCUSTOMER
ADD (CPIBASED VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_ERPI_CONCUS_CPIBASED CHECK (CPIBASED in ('Y', 'N')),
     PRICEINCREASEPERCENTAGE NUMBER(5,2),
     GAINREASON	VARCHAR2(6),
     GAINCOMPETITOR	VARCHAR2(6),
     SIGNDATE	DATE,
     DATEACTIVE	DATE,
     NEXTANNIVERSARYDATE	DATE,
     DATEINACTIVE	DATE,
     ANNIVERSARYADJUSTTYPE	NUMBER(1) DEFAULT 1 NOT NULL CONSTRAINT CHK_ERPI_CONCUS_ANNIVERSARYADJUSTTYPE CHECK (ANNIVERSARYADJUSTTYPE IN (1,2,3)));

COMMENT ON COLUMN ERP_I_CONTRACTSPERCUSTOMER.CPIBASED IS 'Y = consumer price index, N = fixed';
COMMENT ON COLUMN ERP_I_CONTRACTSPERCUSTOMER.ANNIVERSARYADJUSTTYPE IS '1=never, 2=automatic, 3=signal only';

--ERP_I_CUSTOMERINSURANCE

CREATE TABLE ERP_I_CUSTOMERINSURANCE
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  CUSTOMERNUMBER  NUMBER NOT NULL,
  STARTDATE       DATE DEFAULT TRUNC(SYSDATE-1) NOT NULL,
  PRICE           NUMBER(15,6) NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN ERP_I_CUSTOMERINSURANCE.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';

CREATE INDEX INDX01_ERP_I_CUSTINSURANCE ON ERP_I_CUSTOMERINSURANCE (TRANSFER_STATUS);

ALTER TABLE ERP_I_CUSTOMERINSURANCE
  ADD CONSTRAINT PK_ERP_I_CUSTINSURANCE PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_CUSTOMERINSURANCE
  ADD CONSTRAINT CHK_ERP_I_CUSTINSURANCE
  CHECK (TRANSFER_STATUS IN (-1,0,1));


--ERP_I_CUSTOMERINSURANCE_LOG
CREATE TABLE ERP_I_CUSTOMERINSURANCE_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN ERP_I_CUSTOMERINSURANCE_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CUSTOMERINSURANCE_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';

ALTER TABLE ERP_I_CUSTOMERINSURANCE_LOG
  ADD CONSTRAINT PK_ERP_I_CUSTOMERINSURANCE_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_CUSTOMERINSURANCE_LOG
  ADD CONSTRAINT FK_ERP_I_CUSTOMERINSURANCE_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CUSTOMERINSURANCE (TRANSFER_ID) ON DELETE CASCADE;
  
-- add purchaseordervalue in ERP_O_CUSTOMER table needed for update
ALTER TABLE ERP_O_CUSTOMER
ADD PURCHASEORDERVALUE NUMBER(15,6);
