
-- order 502838 (new Lotus - order 20010601)

insert into invoiceappendixdefinition
      (invoiceappendixdefinition_id,
       invoiceappendixtype,
       description,
       reportname,
       customercode,
       module,
       MANUAL,
       DIRECTINVOICE,
       OTHERSERVICES,
       RESIDENTIALSERVICES,
       systemuser_id,
       timestamp)
    values
      (29,
       29,
       'Weighing Details',
       'InvoiceAppendixWeighingDetails.rpt',
       'LTS',
       '',
       'N',
       'Y',
       'Y',
       'Y',
       getminsystemuser(),
       sysdate);



-- order 502836 (new Lotus - order 20010598)

insert into invoiceappendixdefinition
      (invoiceappendixdefinition_id,
       invoiceappendixtype,
       description,
       reportname,
       customercode,
       module,
       MANUAL,
       DIRECTINVOICE,
       OTHERSERVICES,
       RESIDENTIALSERVICES,
       systemuser_id,
       timestamp)
    values
      (31,
       31,
       'Cost Specification Per Cost Center',
       'CostSpecificationPerCostCenter.rpt',
       '',
       'CUSTOMERCOSTSPEC',
       'N',
       'Y',
       'Y',
       'N',
       getminsystemuser(),
       sysdate);


insert into invoiceappendixdefinition
      (invoiceappendixdefinition_id,
       invoiceappendixtype,
       description,
       reportname,
       customercode,
       module,
       MANUAL,
       DIRECTINVOICE,
       OTHERSERVICES,
       RESIDENTIALSERVICES,
       systemuser_id,
       timestamp)
    values
      (32,
       32,
       'Cost Spec Per Customer Per Cost Spec',
       'CostSpecificationPerCustomerPerCostSpec.rpt',
       '',
       'CUSTOMERCOSTSPEC',
       'N',
       'Y',
       'Y',
       'N',
       getminsystemuser(),
       sysdate);


-- order 502837 (new Lotus - order 20010589)

      /*
      CHANGE Table CUSTOMERACTIVITIES 
          - Adding column ACTIVITYREFERENCE
      */

ALTER TABLE CUSTOMERACTIVITIES ADD (ACTIVITYREFERENCE VARCHAR2(30) NULL);

      /*
      CHANGE Table INVOICEHEADER
          - Adding column ACTIVITYREFERENCE
      */

ALTER TABLE INVOICEHEADER ADD (ACTIVITYREFERENCE    VARCHAR2(30) NULL);



-- order 502842 (new Lotus - order 20010590)

 /*   
         CHANGE Table CUSTOMER
      */

ALTER TRIGGER CUSTOMER_CHECK_CONTRACT_USAGE DISABLE;


ALTER TABLE CUSTOMER ADD ( EXPORTDELNOTEINFO    VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationField15
                                   CHECK (EXPORTDELNOTEINFO IN ('Y', 'N')));

ALTER TRIGGER CUSTOMER_CHECK_CONTRACT_USAGE ENABLE;


-- order 502836 (new Lotus - order 20010598)

      /*
      ACTION is CREATE Table CUSTOMERCOSTSPECIFICATION
      */

CREATE TABLE CUSTOMERCOSTSPECIFICATION (
       CUSTOMERCOSTSPECIFICATION_ID NUMBER NOT NULL,
       CUSTOMER_LINK_ID     NUMBER NOT NULL,
       CODE                 VARCHAR2(12) NOT NULL,
       DESCRIPTION          VARCHAR2(40) NOT NULL,
       ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationField16
                                          CHECK (ACTIVE IN ('Y', 'N')),
       USEASDEFAULT              VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationField17
                                          CHECK (USEASDEFAULT IN ('Y', 'N')),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE SEQUENCE SEQ_CUSTOMERCOSTSPECIFICATION
START WITH 1
INCREMENT BY 1;

CREATE UNIQUE INDEX XAK1CUSTOMERCOSTSPECIFICATION ON CUSTOMERCOSTSPECIFICATION
(
       CUSTOMER_LINK_ID               ,
       CODE                           
);

CREATE INDEX XIF1CUSTOMERCOSTSPECIFICATION ON CUSTOMERCOSTSPECIFICATION
(
       CUSTOMER_LINK_ID               ASC
);


ALTER TABLE CUSTOMERCOSTSPECIFICATION
       ADD  ( CONSTRAINT XPKCUSTOMERCOSTSPECIFICATION PRIMARY KEY (
              CUSTOMERCOSTSPECIFICATION_ID) ) ;


ALTER TABLE CUSTOMERCOSTSPECIFICATION
       ADD  ( CONSTRAINT CUSTOMERCOSTSPECIFICATION_CUST
              FOREIGN KEY (CUSTOMER_LINK_ID)
                             REFERENCES CUSTOMER_LINK ) ;


  
      /*
      ACTION is CREATE Table PRODUCTLISTPERCUSTOMER
      */


ALTER TABLE PRODUCTLISTPERCUSTOMER ADD  ( CUSTOMERCOSTSPECIFICATION_ID NUMBER NULL );


CREATE INDEX XIF1PRODUCTLISTPERCUSTOMER ON PRODUCTLISTPERCUSTOMER
(
       CUSTOMERCOSTSPECIFICATION_ID  ASC
);


ALTER TABLE PRODUCTLISTPERCUSTOMER
       ADD  ( CONSTRAINT PLPC_CUSTOMERCOSTSPECIFICATION
              FOREIGN KEY (CUSTOMERCOSTSPECIFICATION_ID)
                             REFERENCES CUSTOMERCOSTSPECIFICATION 
                             ON DELETE SET NULL) ;


 /*
      ACTION is CREATE Table WEIGHINGCATEGPERCUSTOMER
      */


ALTER TABLE WEIGHINGCATEGPERCUSTOMER ADD  ( CUSTOMERCOSTSPECIFICATION_ID NUMBER NULL );


CREATE INDEX XIF4WEIGHINGCATEGPERCUSTOMER ON WEIGHINGCATEGPERCUSTOMER
(
       CUSTOMERCOSTSPECIFICATION_ID  ASC
);


ALTER TABLE WEIGHINGCATEGPERCUSTOMER
       ADD  ( CONSTRAINT WCPC_CUSTOMERCOSTSPECIFICATION
              FOREIGN KEY (CUSTOMERCOSTSPECIFICATION_ID)
                             REFERENCES CUSTOMERCOSTSPECIFICATION 
                             ON DELETE SET NULL) ;


