-- order in new Lotus 20010603.20 (old order 502839)
  

insert into invoiceappendixdefinition
      (invoiceappendixdefinition_id,
       invoiceappendixtype,
       description,
       reportname,
       customercode,
       module,
       MANUAL,
       DIRECTINVOICE,
       OTHERSERVICES,
       RESIDENTIALSERVICES,
       systemuser_id,
       timestamp)
    values
      (30,
       30,
       'CSV Export File',
       'CSV Export File ',     
       '',
       'EXPORTTOCSV',
       'N',
       'Y',
       'Y',
       'N',
       getminsystemuser(),
       sysdate);



      /*
      ACTION is CREATE Table CSVEXPORT
      */

CREATE TABLE CSVEXPORT (
       CSVEXPORT_ID         NUMBER NOT NULL,
       INVOICEHEADER_ID     NUMBER NOT NULL,
       BUSINESSUNIT_ID      NUMBER NOT NULL,
       CREATIONTIMESTAMP    DATE NULL,
       TIMESTAMP            DATE NULL,
       EXPORTTIMESTAMP      DATE NULL,
       EXPORTED             VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationField18
                                          CHECK (EXPORTED IN ('Y', 'N')),
       INVOICENO            VARCHAR2(20) NULL,
       BILLTOCUSTOMERNO     VARCHAR2(20) NULL,
       DELTOCUSTOMERNO      VARCHAR2(20) NULL,
       DELTOCUSTOMERNAME    VARCHAR2(80) NULL,
       YEAR                 VARCHAR2(4) NULL,
       WEEK                 VARCHAR2(2) NULL,
       COSTCENTERCODE       VARCHAR2(12) NULL,
       COSTCENTERNAME       VARCHAR2(40) NULL,
       COSTSPECIFICATIONCODE VARCHAR2(3) NULL,
       INVOICELINETYPECODE  VARCHAR2(12) NULL,
       INVOICELINETYPEDESC  VARCHAR2(40) NULL,
       ACTIVITYCODE         VARCHAR2(12) NULL,
       ACTIVITYDESC         VARCHAR2(40) NULL,
       CODE                 VARCHAR2(12) NULL,
       SIZECODE             VARCHAR2(12) NULL,
       FINISHINGMETHODCODE  VARCHAR2(12) NULL,
       QUANTITY             VARCHAR2(16) NULL,
       REGISTRATIONUNITCODE VARCHAR2(3) NULL,
       PRICE                VARCHAR2(16) NULL,
       AMOUNT               VARCHAR2(20) NULL,
       DESCRIPTION          VARCHAR2(40) NULL,
       TAXPERCENTAGE        VARCHAR2(6) NULL,
       DISCOUNTPERCENTAGE   VARCHAR2(6) NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL
);


CREATE SEQUENCE SEQ_CSVEXPORT
START WITH 1
INCREMENT BY 1;


CREATE INDEX XIF1CSVEXPORT ON CSVEXPORT
(
       INVOICEHEADER_ID               ASC
);

CREATE INDEX XIF2CSVEXPORT ON CSVEXPORT
(
       BUSINESSUNIT_ID                ASC
);

CREATE INDEX XIE1CSVEXPORT ON CSVEXPORT
(
       BUSINESSUNIT_ID                ,
       EXPORTED                       ,
       EXPORTTIMESTAMP                
);


ALTER TABLE CSVEXPORT
       ADD  ( CONSTRAINT XPKCSVEXPORT PRIMARY KEY (CSVEXPORT_ID) ) ;


ALTER TABLE CSVEXPORT
       ADD  ( CONSTRAINT CSVEXPORT_BUSINESSUNIT_ID
              FOREIGN KEY (BUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT ) ;


ALTER TABLE CSVEXPORT
       ADD  ( CONSTRAINT CSVEXPORT_INVOICEHEADER_ID
              FOREIGN KEY (INVOICEHEADER_ID)
                             REFERENCES INVOICEHEADER
                             ON DELETE CASCADE ) ;


  /*
      CHANGE REPORT for Table INVOICEAPPENDIXDEFINITION
          - Adding column REPORTTYPE
      ACTION is ALTER or modify Table INVOICEAPPENDIXDEFINITION
      */

ALTER TABLE INVOICEAPPENDIXDEFINITION ADD (REPORTTYPE           NUMBER DEFAULT 
    0 NOT NULL
                                   CONSTRAINT ReportTypeAppendixDef_Valid
                                          CHECK (REPORTTYPE IN (0, 1, 2)));

-- Add comments to the column

comment on column INVOICEAPPENDIXDEFINITION.REPORTTYPE
  is '0 = Predefined Report (Default)
      1 = User Defined Report
      2 = CSV File';



     
      /*
      CHANGE REPORT for Table INVOICEAPPENDIX
          - Adding column REPORTTYPE
      ACTION is ALTER or modify Table INVOICEAPPENDIX
      */

ALTER TABLE INVOICEAPPENDIX ADD (REPORTTYPE           NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT ReportTypeAppendix_Valid
                                          CHECK (REPORTTYPE IN (0, 1, 2)));

-- Add comments to the column

comment on column INVOICEAPPENDIX.REPORTTYPE
  is '0 = Predefined Report (Default)
      1 = User Defined Report
      2 = CSV File';


 /*
      ACTION is ALTER Table CUSTOMER
      */

ALTER TABLE CUSTOMER ADD (
       CSVFILESEPARATOR     VARCHAR2(1) NULL
                                   CONSTRAINT CSVFileSeparator_Valid
                                          CHECK ((CSVFILESEPARATOR IS NULL) OR (CSVFILESEPARATOR IN (';', ',' ) )));




-- Add comments to the column (receive from Paul remark that comment was missing for this field)

comment on column CUSTOMERRENTDISCOUNT.TYPEOFRENT
  is '1=Piece price, 2=Changes per week';


