      
      /*
      ACTION is CREATE Table INVOICEJOB
      */
create sequence SEQ_INVOICEJOB
start with 1 increment by 1;


CREATE TABLE INVOICEJOB (
       INVOICEJOB_ID        NUMBER NOT NULL,
       CODE                 VARCHAR2(6) NOT NULL,
       DESCRIPTION          VARCHAR2(40) NOT NULL,
       ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_VALIDATIONNEW94
                                          CHECK (ACTIVE IN ('Y', 'N')),
       CLUSTER_ID           NUMBER NULL,
       INVOICEFREQUENCY_ID  NUMBER NULL,
       INVOICETYPE          NUMBER NOT NULL,
       GENERATIONRUNFREQUENCY_ID NUMBER NULL,
       GENERATIONRUNJOBDAY  NUMBER DEFAULT 0  NOT NULL,
       SIMULATIONRUNFREQUENCY_ID NUMBER  NULL,
       SIMULATIONRUNJOBDAY  NUMBER DEFAULT 0  NOT NULL,
       EMAILADDRESSSUMMARY  VARCHAR2(40) NULL,
	SYSTEMUSER_ID        NUMBER NOT NULL,
	TIMESTAMP            DATE NOT NULL
);

COMMENT ON COLUMN INVOICEJOB.INVOICETYPE IS '1 = Not yet invoiced period
2 = Additional invoice
3 = In advance invoice';
CREATE UNIQUE INDEX XAK1INVOICEJOB ON INVOICEJOB
(
       CODE                           ASC
);

CREATE INDEX XIF1INVOICEJOB ON INVOICEJOB
(
       CLUSTER_ID                     ASC
);

CREATE INDEX XIF2INVOICEJOB ON INVOICEJOB
(
       INVOICEFREQUENCY_ID            ASC
);


ALTER TABLE INVOICEJOB
       ADD  ( CONSTRAINT XPKINVOICEJOB PRIMARY KEY (INVOICEJOB_ID) ) ;

      
      

CREATE SEQUENCE SEQ_INVOICEJOBBUSINESSUNIT  START WITH 1 INCREMENT BY 1;   
      /*
      ACTION is CREATE Table INVOICEJOBBUSINESSUNIT
      */

CREATE TABLE INVOICEJOBBUSINESSUNIT (
       INVOICEJOBBUSINESSUNIT_ID NUMBER NOT NULL,
       INVOICEJOB_ID        NUMBER NOT NULL,
       BUSINESSUNIT_ID      NUMBER NOT NULL,
       EMAILADDRESS         VARCHAR2(40) NULL,
       SIMULATIONRUN        VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew99
                                          CHECK (SIMULATIONRUN IN ('Y', 'N')),
       GENERATIONRUN        VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew100
                                          CHECK (GENERATIONRUN IN ('Y', 'N')),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1INVOICEJOBBUSINESSUNIT ON INVOICEJOBBUSINESSUNIT
(
       INVOICEJOB_ID                  ,
       BUSINESSUNIT_ID                
);

CREATE INDEX XIF1INVOICEJOBBUSINESSUNIT ON INVOICEJOBBUSINESSUNIT
(
       INVOICEJOB_ID                  ASC
);

CREATE INDEX XIF2INVOICEJOBBUSINESSUNIT ON INVOICEJOBBUSINESSUNIT
(
       BUSINESSUNIT_ID                ASC
);


ALTER TABLE INVOICEJOBBUSINESSUNIT
       ADD  ( CONSTRAINT XPKINVOICEJOBBUSINESSUNIT PRIMARY KEY (
              INVOICEJOBBUSINESSUNIT_ID) ) ;


ALTER TABLE INVOICEJOBBUSINESSUNIT
       ADD  ( CONSTRAINT INVOICEJOBBUSINESSUNIT_BUSINES
              FOREIGN KEY (BUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT ) ;


ALTER TABLE INVOICEJOBBUSINESSUNIT
       ADD  ( CONSTRAINT INVOICEJOBBUSINESSUNIT_INVOICE
              FOREIGN KEY (INVOICEJOB_ID)
                             REFERENCES INVOICEJOB ) ;





      
      /*
      ACTION is CREATE Table INVOICEJOBSCHEDULE
      */

create sequence SEQ_INVOICEJOBSCHEDULE
start with 1 increment by 1;

CREATE TABLE INVOICEJOBSCHEDULE (
       INVOICEJOBSCHEDULE_ID NUMBER NOT NULL,
       INVOICEJOB_ID        NUMBER NOT NULL,
       PROCESSINGDATE       DATE NOT NULL,
       TYPERUN              NUMBER NOT NULL,
       PRIORITY             NUMBER DEFAULT 9999 NOT NULL,
       STATUS               NUMBER DEFAULT 1 NOT NULL,
       AUTOSCHEDULENEXTRUN  VARCHAR2(20) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_VALIDATIONNEW91
                                          CHECK (AUTOSCHEDULENEXTRUN IN ('Y', 'N')),
       CUTOFFDATE           DATE NULL,
       FROMDATE             DATE NULL,
       TODATE               DATE NULL,
       FROMDATENEXTPERIOD   DATE NULL,
       TODATENEXTPERIOD     DATE NULL,
       WEEKSINADVANCE       NUMBER DEFAULT 0 NOT NULL,
       CORRECTIONS          VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_VALIDATIONNEW92
                                          CHECK (CORRECTIONS IN ('Y', 'N')),
       GENERATECREDITINVOICE VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_VALIDATIONNEW93
                                          CHECK (GENERATECREDITINVOICE IN ('Y', 'N')),
       INVOICEDATE          DATE NOT NULL,
       STARTTIMESTAMP       DATE NULL,
       ENDTIMESTAMP         DATE NULL,
       INVOICECREATED       NUMBER DEFAULT 0 NOT NULL,
       INVOICEFAILED        NUMBER DEFAULT 0 NOT NULL,
       TOTALVALUEEXCLVAT    NUMBER DEFAULT 0.00 NOT NULL,
       TOTALVALUEINCLVAT    NUMBER DEFAULT 0.00 NOT NULL,
	SYSTEMUSER_ID        NUMBER NOT NULL,
	TIMESTAMP            DATE NOT NULL
);

COMMENT ON COLUMN INVOICEJOBSCHEDULE.TYPERUN IS '0 = Simulation run
1 = Generation run';
COMMENT ON COLUMN INVOICEJOBSCHEDULE.PRIORITY IS '1 – 9999, default 9999';
COMMENT ON COLUMN INVOICEJOBSCHEDULE.STATUS IS '1 = Scheduled (default)
2 = In progress
3 = Test invoices generated
4 = Finalized
5 = Done
6 = Waiting';
CREATE INDEX XIF1INVOICEJOBSCHEDULE ON INVOICEJOBSCHEDULE
(
       INVOICEJOB_ID                  ASC
);

CREATE INDEX XIE1INVOICEJOBSCHEDULE ON INVOICEJOBSCHEDULE
(
       PROCESSINGDATE                 ,
       PRIORITY                       ,
       INVOICEJOB_ID                  
);

CREATE INDEX XIE2INVOICEJOBSCHEDULE ON INVOICEJOBSCHEDULE
(
       STATUS                         ,
       PROCESSINGDATE                 
);


ALTER TABLE INVOICEJOBSCHEDULE
       ADD  ( CONSTRAINT XPKINVOICEJOBSCHEDULE PRIMARY KEY (
              INVOICEJOBSCHEDULE_ID) ) ;

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

ALTER TABLE INVOICEHEADER ADD (INVOICEJOBSCHEDULE_ID NUMBER NULL);


ALTER TABLE INVOICEHEADER ADD (PRINTDATE            DATE NULL);


ALTER TABLE INVOICEJOB
       ADD  ( CONSTRAINT INVOICEJOB_INVOICEFREQUENCY_ID
              FOREIGN KEY (INVOICEFREQUENCY_ID)
                             REFERENCES INVOICEFREQUENCY ) ;


ALTER TABLE INVOICEJOB
       ADD  ( CONSTRAINT INVOICEJOB_CLUSTER_ID
              FOREIGN KEY (CLUSTER_ID)
                             REFERENCES CUSTOMERCLUSTER ) ;

ALTER TABLE INVOICEJOBSCHEDULE
       ADD  ( CONSTRAINT INVOICEJOBSCHEDULE_INVOICEJOB_
              FOREIGN KEY (INVOICEJOB_ID)
                             REFERENCES INVOICEJOB ) ;







