-- order 502880 (new Lotus Notes order: 20010616.20)


-- Add comments to the columns 

comment on column INVOICEJOBSCHEDULE.TYPERUN
  is '0 = Simulation run
1 = Generation run
2 = Revenue ';


ALTER TABLE INVOICEJOBSCHEDULE ADD (
       EXPORTREVENUE   VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationField21
                                          CHECK (EXPORTREVENUE IN ('Y', 'N')),
       WEEKSTATISTICS   VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationField22
                                          CHECK (WEEKSTATISTICS IN ('Y', 'N')),
       RESIDENTIALSERVICES NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT ResidentialServices_Valid
                                          CHECK (RESIDENTIALSERVICES IN (0, 1, 2)),
       CLIENTINVOICES VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationField23
                                          CHECK (CLIENTINVOICES IN ('Y', 'N')));


-- Add comments to the columns 

comment on column INVOICEJOBSCHEDULE.RESIDENTIALSERVICES
  is '0 = No residential services
1 = Residential services only
2 = Both';


-- Add comments to the columns 
comment on column INVOICEHEADER.DOCUMENTTYPE
  is '1 = Invoice
2 = Statement
3 = Client invoice
4 = Revenue invoice
5 = Revenue client invoice';


CREATE TABLE WEEKSTATISTICS (
       WEEKSTATISTICS_ID NUMBER NOT NULL,
       WEEKSTATISTICSTYPE NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT WeekStatisticsType_Valid
                                          CHECK (WEEKSTATISTICSTYPE  IN (0, 1)),
       PROCESSBUSINESSUNIT_ID     NUMBER NOT NULL,
       INVOICEBUSINESSUNIT_ID     NUMBER NOT NULL,
       YEAR     NUMBER NOT NULL,
       WEEK     NUMBER NOT NULL,
       PERIOD     NUMBER NOT NULL,
       BILTOCUSTOMER_LINK_ID NUMBER NOT NULL,
       DELTOCUSTOMER_LINK_ID NUMBER NOT NULL,
       COSTCENTER_LINK_ID NUMBER NOT NULL,
       INVOICEFREQUENCY_ID NUMBER NOT NULL,
       INVOICELINETYPE_ID NUMBER NOT NULL,
       INVOICELINESUBTYPE NUMBER NOT NULL,
       CODE      VARCHAR2(12) NOT NULL,
       DESCRIPTION          VARCHAR2(40) NOT NULL,
       ACTIVITY_ID NUMBER NOT NULL,
       PRODUCT_LINK_ID NUMBER NOT NULL,
       SIZEDEFINITION_LINK_ID NUMBER NOT NULL,
       FINISHINGMETHOD_ID NUMBER NOT NULL,
       TURNOVERGROUP_ID NUMBER NOT NULL,
       QUANTITY NUMBER NOT NULL,
       PRICE NUMBER NOT NULL,
       AMOUNT NUMBER NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);


CREATE SEQUENCE SEQ_WEEKSTATISTICS
START WITH 1
INCREMENT BY 1;


-- Add comments to the columns 
comment on column WEEKSTATISTICS.WEEKSTATISTICSTYPE
  is '0 = Revenue run (based on revenue invoice)
      1 = Invoice generation run (based on real invoice)';


CREATE UNIQUE INDEX XPKWEEKSTATISTICS ON WEEKSTATISTICS
(
       WEEKSTATISTICS_ID           ASC
);


ALTER TABLE WEEKSTATISTICS
       ADD  ( CONSTRAINT XPKWEEKSTATISTICS 
              PRIMARY KEY (WEEKSTATISTICS_ID) ) ;


CREATE INDEX XIF1WEEKSTATISTICS ON WEEKSTATISTICS
(
       PROCESSBUSINESSUNIT_ID               ASC
);


CREATE INDEX XIF2WEEKSTATISTICS ON WEEKSTATISTICS
(
       INVOICEBUSINESSUNIT_ID               ASC
);


CREATE INDEX XIF3WEEKSTATISTICS ON WEEKSTATISTICS
(
       BILTOCUSTOMER_LINK_ID               ASC
);


CREATE INDEX XIF4WEEKSTATISTICS ON WEEKSTATISTICS
(
       DELTOCUSTOMER_LINK_ID               ASC
);


CREATE INDEX XIF5WEEKSTATISTICS ON WEEKSTATISTICS
(
       COSTCENTER_LINK_ID               ASC
);


CREATE INDEX XIF6WEEKSTATISTICS ON WEEKSTATISTICS
(
       INVOICEFREQUENCY_ID               ASC
);


CREATE INDEX XIF7WEEKSTATISTICS ON WEEKSTATISTICS
(
       INVOICELINETYPE_ID               ASC
);


CREATE INDEX XIF8WEEKSTATISTICS ON WEEKSTATISTICS
(
       ACTIVITY_ID               ASC
);


CREATE INDEX XIF9WEEKSTATISTICS ON WEEKSTATISTICS
(
       PRODUCT_LINK_ID               ASC
);


CREATE INDEX XIF10WEEKSTATISTICS ON WEEKSTATISTICS
(
       SIZEDEFINITION_LINK_ID               ASC
);


CREATE INDEX XIF11WEEKSTATISTICS ON WEEKSTATISTICS
(
       FINISHINGMETHOD_ID               ASC
);


CREATE INDEX XIF12WEEKSTATISTICS ON WEEKSTATISTICS
(
       TURNOVERGROUP_ID               ASC
);


CREATE INDEX XIE1WEEKSTATISTICS ON WEEKSTATISTICS
(
       PROCESSBUSINESSUNIT_ID         ,
       YEAR                           ,
       WEEK                           ,
       BILTOCUSTOMER_LINK_ID          ,
       DELTOCUSTOMER_LINK_ID          ,
       COSTCENTER_LINK_ID             ,
       INVOICELINETYPE_ID             ,
       INVOICELINESUBTYPE  
);


CREATE INDEX XIE2WEEKSTATISTICS ON WEEKSTATISTICS
(
       PROCESSBUSINESSUNIT_ID         ,
       YEAR                           ,
       PERIOD                         ,
       BILTOCUSTOMER_LINK_ID          ,
       DELTOCUSTOMER_LINK_ID          ,
       COSTCENTER_LINK_ID             ,
       INVOICELINETYPE_ID             ,
       INVOICELINESUBTYPE  
);


CREATE INDEX XIE3WEEKSTATISTICS ON WEEKSTATISTICS
(
       WEEKSTATISTICSTYPE             ,
       INVOICEBUSINESSUNIT_ID         ,
       BILTOCUSTOMER_LINK_ID          ,
       DELTOCUSTOMER_LINK_ID          
);


ALTER TABLE WEEKSTATISTICS
       ADD  ( CONSTRAINT WEEKSTATISTICS_PROCESSBU
              FOREIGN KEY (PROCESSBUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT
                             ON DELETE CASCADE ) ;
                             

                             ALTER TABLE WEEKSTATISTICS
ADD  ( CONSTRAINT WEEKSTATISTICS_INVOICEBU
              FOREIGN KEY (INVOICEBUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT
                             ON DELETE CASCADE ) ;
                             
                             
ALTER TABLE WEEKSTATISTICS
        ADD  ( CONSTRAINT WEEKSTATISTICS_BILTOCUST
              FOREIGN KEY (BILTOCUSTOMER_LINK_ID)
                             REFERENCES CUSTOMER_LINK ) ;
                             

                             ALTER TABLE WEEKSTATISTICS
        ADD  ( CONSTRAINT WEEKSTATISTICS_DELTOCUST
              FOREIGN KEY (DELTOCUSTOMER_LINK_ID)
                             REFERENCES CUSTOMER_LINK ) ;
                             
                                                     
ALTER TABLE WEEKSTATISTICS
       ADD  ( CONSTRAINT WEEKSTATISTICS_COSTCENTER
              FOREIGN KEY (COSTCENTER_LINK_ID)
                             REFERENCES COSTCENTER_LINK) ;
                             
                             
                             ALTER TABLE WEEKSTATISTICS
       ADD  ( CONSTRAINT WEEKSTATISTICS_INVOICEFREQ
              FOREIGN KEY (INVOICEFREQUENCY_ID)
                             REFERENCES INVOICEFREQUENCY) ;
                             
                         
                ALTER TABLE WEEKSTATISTICS
       ADD  ( CONSTRAINT WEEKSTATISTICS_INVOICELINETYPE
              FOREIGN KEY (INVOICELINETYPE_ID)
                             REFERENCES INVOICELINETYPE) ;  
                             
                             
                             ALTER TABLE WEEKSTATISTICS
       ADD  ( CONSTRAINT WEEKSTATISTICS_ACTIVITY
              FOREIGN KEY (ACTIVITY_ID)
                             REFERENCES ACTIVITY) ;   
                             

                            ALTER TABLE WEEKSTATISTICS
       ADD  ( CONSTRAINT WEEKSTATISTICS_PROD_LINK
              FOREIGN KEY (PRODUCT_LINK_ID)
                             REFERENCES PRODUCT_LINK) ;  
                             

                             ALTER TABLE WEEKSTATISTICS
       ADD  ( CONSTRAINT WEEKSTATISTICS_SIZEDEF_LINK
              FOREIGN KEY (SIZEDEFINITION_LINK_ID)
                             REFERENCES SIZEDEFINITION_LINK) ;  
                             

                              ALTER TABLE WEEKSTATISTICS
       ADD  ( CONSTRAINT WEEKSTATISTICS_FINMETHOD_ID
              FOREIGN KEY (FINISHINGMETHOD_ID)
                             REFERENCES FINISHINGMETHOD) ;  
                             
                             
                               ALTER TABLE WEEKSTATISTICS
       ADD  ( CONSTRAINT WEEKSTATISTICS_TURNOVERGROUP
              FOREIGN KEY (TURNOVERGROUP_ID)
                             REFERENCES TURNOVERGROUP) ; 
                             
                             