-- order 502894 (new Lotus order: 20010658)


-- Add comments to the column

comment on column SPECIALCHARGE.FREQUENCY
  is '1=Per Invoice
      2=Once a week on Invoice
      3=On Statement
      4=Every Direct Invoice only
      5=Per delivered piece
      6=Every Processed Delivery Note
      7=Every Normal Visit
      8=Every special Visit
      9=Per Active Wearer per Invoice
     10=Per Registered Weight';



      /*
      CREATE Table WCPCSPECIALCHARGE
      */

CREATE TABLE WCPCSPECIALCHARGE (
       WCPCSPECIALCHARGE_ID NUMBER NOT NULL,
       WEIGHINGCATEGPERCUSTOMER_ID NUMBER NOT NULL,
       SPECIALCHARGE_ID     NUMBER NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);


CREATE SEQUENCE SEQ_WCPCSPECIALCHARGE
START WITH 1
INCREMENT BY 1;


CREATE UNIQUE INDEX XPKWCPCSPECIALCHARGE ON WCPCSPECIALCHARGE
(
       WCPCSPECIALCHARGE_ID           ASC
);

CREATE UNIQUE INDEX XAK1WCPCSPECIALCHARGE ON WCPCSPECIALCHARGE
(
       WEIGHINGCATEGPERCUSTOMER_ID    ,
       SPECIALCHARGE_ID               
);


ALTER TABLE WCPCSPECIALCHARGE
       ADD ( CONSTRAINT XPKWCPCSPECIALCHARGE PRIMARY KEY (WCPCSPECIALCHARGE_ID) ) ;


ALTER TABLE WCPCSPECIALCHARGE
       ADD ( CONSTRAINT WCPCSPECIALCHARGE_SPECIALCH FOREIGN KEY (SPECIALCHARGE_ID)
                             REFERENCES SPECIALCHARGE ) ;


ALTER TABLE WCPCSPECIALCHARGE
       ADD ( CONSTRAINT WCPCSPECIALCHARGE_WEIGHINGCAT FOREIGN KEY (WEIGHINGCATEGPERCUSTOMER_ID)
                             REFERENCES WEIGHINGCATEGPERCUSTOMER
                             ON DELETE CASCADE ) ;





