-- order 502841 (order in new Lotus: 20010618.20)


  insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (63,
       'Check if the customer bank accounts are valid (matching the 11-proof)',
       'select cu.customernumber, cu.name, cbi.bankaccount, bi.idcode, bi.description, bi.internalidcode
        from customer cu, customerbillinginfo cbi, bankinstitution bi
        where cbi.customer_id = cu.customer_id
        and bi.bankinstitution_id = cbi.bankinstitution_id
        and bi.elevenproof = ''Y''
        and IsElevenProof(cbi.bankaccount) = 0',
       'There are bank accounts defined for a customer that do not match the 11-Proof!',
       'N',
       'They need to be corrected manually!',
       'Y');


 insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (64,
       'Check if the client bank accounts are valid (matching the 11-proof)',
       'select cu.customernumber, cu.name customername, cl.clientnumber, cl.name clientname,
       cbi.bankaccountnumber, bi.idcode, bi.description, bi.internalidcode
       from client cl, clientbillinginfo cbi, customer cu, bankinstitution bi
       where cbi.client_id = cl.client_id
       and cu.customer_id = cl.customer_id
       and bi.bankinstitution_id = cbi.bankinstitution_id
       and bi.elevenproof =''Y''
       and IsElevenProof(cbi.bankaccountnumber) = 0',
       'There are bank accounts defined for a client that do not match the 11-Proof!',
       'N',
       'They need to be corrected manually!',
       'Y');


      insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (65,
       'Check if the laundry bank accounts are valid (matching the 11-proof)',
       'select bi.idcode, bi.description, bi.internalidcode, bi.laundrybankaccount
        from bankinstitution bi
        where bi.elevenproof = ''Y''
        and IsElevenProof(bi.laundrybankaccount) = 0',
       'There are laundry bank accounts defined for a bank institution that do not match the 11-Proof!',
       'N',
       'They need to be corrected manually!',
       'Y');


      /*
      CHANGE REPORT for Table BANKINSTITUTION
          - Adding column ELEVENPROOF
      ACTION is ALTER or modify Table BANKINSTITUTION
      */

ALTER TABLE BANKINSTITUTION ADD (ELEVENPROOF          VARCHAR2(1) DEFAULT 'N' 
    NOT NULL
                                   CONSTRAINT YN_ValidationField19
                                          CHECK (ELEVENPROOF IN ('Y', 'N')));


      
      /*
      CHANGE REPORT for Table INVOICEHEADER
          - Adding column EXPORTCLIEOP03
      ACTION is ALTER or modify Table INVOICEHEADER
      */

ALTER TABLE INVOICEHEADER ADD (EXPORTCLIEOP03       DATE NULL);
      
 
      /*
      ACTION is CREATE Table CLIEOP03BATCH
      */

CREATE TABLE CLIEOP03BATCH (
       CLIEOP03BATCH_ID     NUMBER NOT NULL,
       CREATIONDATE         DATE NOT NULL,
       SEQUENCENUMBER       NUMBER NOT NULL,
       BATCHNUMBER          VARCHAR2(4) NOT NULL,
       CURRENCYCODE         VARCHAR2(3) NOT NULL,
       BUSINESSUNIT_ID      NUMBER NOT NULL,
       INVOICEBUSINESSUNITS VARCHAR2(1000) NULL,
       MININVOICENUMBER     NUMBER NULL,
       MAXINVOICENUMBER     NUMBER NULL,
       MININVOICEDATE       DATE NULL,
       MAXINVOICEDATE       DATE NULL,
       DESIREDPROCESSDATE   DATE NOT NULL,
       PATHNAME             VARCHAR2(100) NOT NULL,
       FILENAME             VARCHAR2(50) NOT NULL,
       SENDERID             VARCHAR2(5) NOT NULL,
       TESTCODE             VARCHAR2(1) DEFAULT 'T' NOT NULL,
       CREATIONUSER         NUMBER NOT NULL,
       CREATIONTIMESTAMP    DATE NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE SEQUENCE SEQ_CLIEOP03BATCH
START WITH 1
INCREMENT BY 1;


COMMENT ON COLUMN CLIEOP03BATCH.TESTCODE IS 'T - The export file has been sent from a Test environment (default)
P - The export file has been sent from a Production environment';

CREATE INDEX XIF1CLIEOP03BATCH ON CLIEOP03BATCH
(
       BUSINESSUNIT_ID                ASC
);


ALTER TABLE CLIEOP03BATCH
       ADD  ( CONSTRAINT XPKCLIEOP03BATCH PRIMARY KEY (
              CLIEOP03BATCH_ID) ) ;


ALTER TABLE CLIEOP03BATCH
       ADD  ( CONSTRAINT CLIEOP03BATCH_BUSINESSUNIT_ID
              FOREIGN KEY (BUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT 
                             ON DELETE SET NULL) ;



