--ABS-73805

----------- ERP_I_CONTRACT -----------
ALTER TABLE ERP_I_CONTRACT DROP CONSTRAINT CHK_ERP_I_CONTRACT_COMMPAYSALS;
ALTER TABLE ERP_I_CONTRACT DROP CONSTRAINT CHK_ERP_I_CONTRACT_COMMPAYACCM;
ALTER TABLE ERP_I_CONTRACT DROP CONSTRAINT CHK_ERP_I_CONTRACT_ALLNOTAGRPR;
ALTER TABLE ERP_I_CONTRACT DROP CONSTRAINT CHK_ERP_I_CONTRACT_ALLOWNONCON;
ALTER TABLE ERP_I_CONTRACT DROP CONSTRAINT CHK_ERP_I_CONTRACT_USEPRODSIZE;
ALTER TABLE ERP_I_CONTRACT DROP CONSTRAINT CHK_ERP_I_CONTRACT_PRICEINDEX;

ALTER TABLE ERP_I_CONTRACT ADD (COMMISIONPAYABLESALES       VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_ERP_I_CONTRACT_COMMPAYSALS
                                                            CHECK (COMMISIONPAYABLESALES IN ('Y', 'N')),
                                COMMISIONPAYABLEACCMGR      VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_ERP_I_CONTRACT_COMMPAYACCM
                                                            CHECK (COMMISIONPAYABLEACCMGR IN ('Y', 'N')),
                                ADDRESS_STREET              VARCHAR2(30) NULL,
                                ADDRESS_STREET2             VARCHAR2(30) NULL,
                                ADDRESS_ZIPCODE             VARCHAR2(15) NULL,
                                ADDRESS_CITY                VARCHAR2(30) NULL,
                                ADDRESS_STATE               VARCHAR2(20) NULL,
                                ADDRESS_COUNTY              VARCHAR2(20) NULL,
                                COUNTRYCODE                 VARCHAR2(3) NULL,
                                CONTACTPERSONNUMBER         NUMBER NULL,
                                NEXTSCHEDULEPRICEINCREASE   DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
                                ALLOWNOTAGREEDPRICELISTS    VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_ERP_I_CONTRACT_ALLNOTAGRPR
                                                            CHECK (ALLOWNOTAGREEDPRICELISTS IN ('Y', 'N')),
                                STATUSCODE                  VARCHAR2(2) DEFAULT '10' NOT NULL,
                                MASTERAGREEMENTCODE         VARCHAR2(15) NULL,
                                CURRENCYCODE                VARCHAR2(3) NULL,
                                TRANSACTIONTYPE_ID          NUMBER NULL,
                                COMPLIANCYTEMPLATECODE      VARCHAR2(6) NULL,
                                ALLOWNONCONTRACTPRODUCTS    VARCHAR2(1) DEFAULT 'Y' NULL CONSTRAINT CHK_ERP_I_CONTRACT_ALLOWNONCON
                                                            CHECK (ALLOWNONCONTRACTPRODUCTS IN ('Y', 'N')),
                                USEPRODSIZESURCHARGEPRICING VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT CHK_ERP_I_CONTRACT_USEPRODSIZE
                                                            CHECK (USEPRODSIZESURCHARGEPRICING IN ('Y', 'N')));

CREATE INDEX XIF3ERP_I_CONTRACT_CODE ON ERP_I_CONTRACT (TRANSACTIONTYPE_ID ASC);
CREATE INDEX XIF4ERP_I_CONTRACT_CODE ON ERP_I_CONTRACT (COUNTRYCODE ASC);
CREATE INDEX XIF5ERP_I_CONTRACT_CODE ON ERP_I_CONTRACT (CURRENCYCODE ASC);
                              
ALTER TABLE ERP_I_CONTRACT
  ADD CONSTRAINT ERP_I_CONTRACT_TRANSACTTYPE FOREIGN KEY (TRANSACTIONTYPE_ID)
  REFERENCES TRANSACTIONTYPE (TRANSACTIONTYPE_ID);          
ALTER TABLE ERP_I_CONTRACT
  ADD CONSTRAINT FK_ERP_I_CONTRACT_COUNTRY FOREIGN KEY (COUNTRYCODE)
  REFERENCES COUNTRY (CODE);  
ALTER TABLE ERP_I_CONTRACT
  ADD CONSTRAINT FK_ERP_I_CONTRACT_CURRENCY FOREIGN KEY (CURRENCYCODE)
  REFERENCES COUNTRY (CODE);       
ALTER TABLE ERP_I_CONTRACT
  ADD CONSTRAINT CHK_ERP_I_CONTRACT_PRICEINDEX
  CHECK ((ALLOWNOTAGREEDPRICELISTS = 'N') OR 
         (ALLOWNOTAGREEDPRICELISTS = 'Y' AND NOT AGREEDPRICEINDEXCODE IS NOT NULL));
              
----------- ERP_I_INVOICEPAYMENTS -----------
ALTER TABLE ERP_I_INVOICEPAYMENTS DROP CONSTRAINT PAYMENTMETHODS_VALID;

CREATE TABLE ERP_I_INVOICEPAYMENTS
( TRANSFER_ID             NUMBER NOT NULL,
  TRANSFER_STATUS         NUMBER DEFAULT 0 NOT NULL,
  CUSTOMERNUMBER        NUMBER NULL,
  INVOICENUMBER            NUMBER NULL,
  AMOUNT                NUMBER(15,6) NULL,
  PAYMENTDATE            DATE NULL,
  PAYMENTMETHOD          NUMBER DEFAULT 1 CONSTRAINT PAYMENTMETHODS_VALID
                CHECK (PAYMENTMETHOD IN (0,1,2,3,4,5,6,7,8,9)));

ALTER TABLE ERP_I_INVOICEPAYMENTS
  ADD CONSTRAINT PK_ERP_I_INVOICEPAYMENTS PRIMARY KEY (TRANSFER_ID);
  
----------- ERP_I_INVOICEPAYMENTS_LOG -----------
CREATE TABLE ERP_I_INVOICEPAYMENTS_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER NOT NULL CONSTRAINT TRANSFER_STATUS_VALID50 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);

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

ALTER TABLE ERP_I_INVOICEPAYMENTS_LOG
  ADD CONSTRAINT PK_ERP_I_INVOICEPAYMENTS_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_INVOICEPAYMENTS_LOG
  ADD CONSTRAINT FK_ERP_I_INVOICEPAYMENTS_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_INVOICEPAYMENTS (TRANSFER_ID) ON DELETE CASCADE; 

----------- ERP_O_PURCHASEORDERHEADER -----------
ALTER TABLE ERP_O_PURCHASEORDERHEADER MODIFY (PRIORITYPURCHASECATEGORY VARCHAR2(1) NULL);

ALTER TABLE ERP_O_PURCHASEORDERHEADER ADD (LASTRECEIVINGDATE DATE NULL);

ALTER TABLE CONTACTPERSON ADD (ERPEXPORT VARCHAR2(1) DEFAULT 'N' NULL);
CREATE INDEX XIE3CONTACTPERSON ON CONTACTPERSON (ERPEXPORT ASC);

----------- ERP_O_SAGE_LEDGER -----------
CREATE TABLE ERP_O_SAGE_LEDGER
( TRANSFER_ID             NUMBER NOT NULL,
  TRANSFER_STATUS         NUMBER DEFAULT 0 NOT NULL,
  INVOICEDATE             DATE NULL,
  INVOICEHEADER_ID        NUMBER NULL,
  INVOICENUMBER           NUMBER NULL,
  INVOICEBUSINESSUNITCODE VARCHAR2(6) NULL,
  BILLTOCUSTOMERNUMBER    NUMBER NULL,
  DELTOCUSTOMERNUMBER     NUMBER NULL,
  CURRENCYCODE            VARCHAR2(3) NULL,
  NETPAYMENTDAYS          NUMBER NULL,
  PAYMENTDISCOUNTDAYS     NUMBER(15,2) NULL,
  DUEDATE                 DATE NULL,
  INVOICELINE_ID          NUMBER NULL,
  LINENUMBER              NUMBER NULL,
  AMOUNT                  NUMBER NULL,
  TURNOVERGROUPCODE       VARCHAR2(12) NULL,
  FINALIZATIONDATE        DATE NULL);
 
ALTER TABLE ERP_O_SAGE_LEDGER
  ADD CONSTRAINT PK_ERP_O_SAGE_LEDGER PRIMARY KEY (TRANSFER_ID);

COMMENT ON COLUMN ERP_O_SAGE_LEDGER.FINALIZATIONDATE
  IS 'Comes from invoiceheader FINALIZATIONTIMESTAMP, date part';

----------- ERP_O_SAGE_LEDGER_LOG -----------
CREATE TABLE ERP_O_SAGE_LEDGER_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID51 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);

COMMENT ON COLUMN ERP_O_SAGE_LEDGER_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_O_SAGE_LEDGER_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
  
ALTER TABLE ERP_O_SAGE_LEDGER_LOG
  ADD CONSTRAINT PK_ERP_O_SAGE_LEDGER_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_SAGE_LEDGER_LOG
  ADD CONSTRAINT FK_ERP_O_SAGE_LEDGER_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_SAGE_LEDGER (TRANSFER_ID) ON DELETE CASCADE; 

----------- CUSTOMER -----------
ALTER TABLE CUSTOMER ADD (FIRSTSERVICEDATE DATE NULL);

COMMENT ON COLUMN CUSTOMER.FIRSTSERVICEDATE
  IS 'The first date the customer is serviced and invoiced';
  
----------- ERP_O_CUSTOMER -----------
ALTER TABLE ERP_O_CUSTOMER ADD (FIRSTSERVICEDATE DATE NULL,
                CUSTOMERCONTRACTCODE VARCHAR2(15) NULL);

COMMENT ON COLUMN ERP_O_CUSTOMER.FIRSTSERVICEDATE
  IS 'The first date the customer is serviced and invoiced';

----------- ERP_O_PURCHASEORDERLINE -----------
ALTER TABLE ERP_O_PURCHASEORDERLINE ADD (DELTAQUANTITYORDER NUMBER NULL, 
                     DELTAQUANTITYCANCELLED NUMBER NULL,
                     DELTAQUANTITYRECEIVED NUMBER NULL);
