-- order 502881 (order in new Lotus: 20010613.20)

insert into invoicelinetype
            (invoicelinetype_id,
             type,
             factorydescription,
             includeinminimumcalc,
             taxtype,
             defineturnovergroup,
             definespecialcharge,
             supressprinting,
             systemuser_id,
             timestamp)
          values
            (28,
             28,
             'Service Costs',
             'N',
             3,
             'Y',
             'Y',
             0,
             getminsystemuser(),
             sysdate);  

comment on column INVOICELINETYPE.TYPE
  is '01= Rent
      02= Delivery (wash)
      03= Soil Weight
      04= Clean Weight
      05= Sales
      06= Repair
      07= Replacement charge
      08= Automatic Replacement (non-garm)
      09= Loss charge
      10= Remaining value charge
      11= Overuse charge
      12= Prep / Emblem charge
      13= Finance charge
      14= Cash Delay line
      15= Miscellaneous Invoice line
      16= Special Charge
      17= Minimum invoice amount
      18= Tax
      19=Emblem Rent
      20=Locker Rent
      21=Locker service
      22=Lease
      23=Stock Charge
      24=Lost Customer Residual Value Charge
      25=Lost Revenue Charge
      26 = Residential wash
      27 = Residential labels
      28 = Service costs';


INSERT INTO invoicelinesubtype i
  (i.invoicelinesubtype_id,
   SUBTYPE,
   invoicelinetype_id,
   factorydescription,
   systemuser_id,
   TIMESTAMP)
  SELECT seq_invoicelinesubtype.NEXTVAL,
         0,
         28,
         'Service Costs LOG',
         1,
         SYSDATE
  FROM   dual;

INSERT INTO invoicelinesubtype i
  (i.invoicelinesubtype_id,
   SUBTYPE,
   invoicelinetype_id,
   factorydescription,
   systemuser_id,
   TIMESTAMP)
  SELECT seq_invoicelinesubtype.NEXTVAL,
         1,
         28,
         'Service Costs COG',
         1,
         SYSDATE
  FROM   dual;

INSERT INTO invoicelinesubtype i
  (i.invoicelinesubtype_id,
   SUBTYPE,
   invoicelinetype_id,
   factorydescription,
   systemuser_id,
   TIMESTAMP)
  SELECT seq_invoicelinesubtype.NEXTVAL,
         2,
         28,
         'Service Costs for max inventory',
         1,
         SYSDATE
  FROM   dual;

INSERT INTO invoicelinesubtype i
  (i.invoicelinesubtype_id,
   SUBTYPE,
   invoicelinetype_id,
   factorydescription,
   systemuser_id,
   TIMESTAMP)
  SELECT seq_invoicelinesubtype.NEXTVAL,
         3,
         28,
         'Product list specific Service Costs LOG',
         1,
         SYSDATE
  FROM   dual;

INSERT INTO invoicelinesubtype i
  (i.invoicelinesubtype_id,
   SUBTYPE,
   invoicelinetype_id,
   factorydescription,
   systemuser_id,
   TIMESTAMP)
  SELECT seq_invoicelinesubtype.NEXTVAL,
         4,
         28,
         'Product list specific Service Costs COG',
         1,
         SYSDATE
  FROM   dual;

INSERT INTO invoicelinesubtype i
  (i.invoicelinesubtype_id,
   SUBTYPE,
   invoicelinetype_id,
   factorydescription,
   systemuser_id,
   TIMESTAMP)
  SELECT seq_invoicelinesubtype.NEXTVAL,
         5,
         28,
         'Product list specific Service Costs for max inventory',
         1,
         SYSDATE
  FROM   dual;



      /*
      ACTION is CREATE Table CUSTOMERSERVICECOSTSPRICE
      */

CREATE TABLE CUSTOMERSERVICECOSTSPRICE (
       CUSTOMERSERVICECOSTSPRICE_ID NUMBER NOT NULL,
       CUSTOMER_ID          NUMBER NOT NULL,
       STARTDATE            DATE NOT NULL,
       PRICETYPE            NUMBER(1) DEFAULT 0 NOT NULL
                                   CHECK (PRICETYPE IN (0, 1)),
       PRICE                NUMBER(15,6) NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL
);


CREATE SEQUENCE SEQ_CUSTOMERSERVICECOSTSPRICE
START WITH 1
INCREMENT BY 1;

-- Add comments to the column

comment on column CUSTOMERSERVICECOSTSPRICE.PRICETYPE
  is '0 = LOG
      1 = COG';


CREATE UNIQUE INDEX XPKCUSTOMERSERVICECOSTSPRICE ON CUSTOMERSERVICECOSTSPRICE
(
       CUSTOMERSERVICECOSTSPRICE_ID   ASC
);

CREATE UNIQUE INDEX XAK1CUSTOMERSERVICECOSTSPRICE ON CUSTOMERSERVICECOSTSPRICE
(
       CUSTOMER_ID                    ,
       STARTDATE                      ,
       PRICETYPE                      
);


ALTER TABLE CUSTOMERSERVICECOSTSPRICE
       ADD ( CONSTRAINT XPKCUSTOMERSERVICECOSTSPRICE PRIMARY KEY (CUSTOMERSERVICECOSTSPRICE_ID) ) ;

      
      /*
      ACTION is CREATE Table PLPCSERVICECOSTSPRICE
      */

CREATE TABLE PLPCSERVICECOSTSPRICE (
       PLPCSERVICECOSTSPRICE_ID NUMBER NOT NULL,
       PRODUCTLISTPERCUSTOMER_ID NUMBER NOT NULL,
       STARTDATE            DATE NOT NULL,
       PRICETYPE            NUMBER(1) DEFAULT 0 NOT NULL
                                   CHECK (PRICETYPE IN (0, 1)),
       PRICE                NUMBER(15,6) NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL
);

CREATE SEQUENCE SEQ_PLPCSERVICECOSTSPRICE
START WITH 1
INCREMENT BY 1;

-- Add comments to the column

comment on column PLPCSERVICECOSTSPRICE.PRICETYPE
  is '0 = LOG
      1 = COG';

CREATE UNIQUE INDEX XPKPLPCSERVICECOSTSPRICE ON PLPCSERVICECOSTSPRICE
(
       PLPCSERVICECOSTSPRICE_ID       ASC
);

CREATE UNIQUE INDEX XAK1PLPCSERVICECOSTSPRICE ON PLPCSERVICECOSTSPRICE
(
       PRODUCTLISTPERCUSTOMER_ID      ,
       STARTDATE                      ,
       PRICETYPE                      
);


ALTER TABLE PLPCSERVICECOSTSPRICE
       ADD ( CONSTRAINT XPKPLPCSERVICECOSTSPRICE PRIMARY KEY (PLPCSERVICECOSTSPRICE_ID) ) ;


ALTER TABLE CUSTOMERSERVICECOSTSPRICE
       ADD ( CONSTRAINT CUSTSERVICECOSTSPRICE_CUSTOMER FOREIGN KEY (CUSTOMER_ID)
                             REFERENCES CUSTOMER
                             ON DELETE CASCADE ) ;


ALTER TABLE PLPCSERVICECOSTSPRICE
       ADD ( CONSTRAINT PLPCSERVICECOSTSPRICE_PLPC FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID)
                             REFERENCES PRODUCTLISTPERCUSTOMER
                             ON DELETE CASCADE ) ;


  
      /*
      ACTION is ALTER Table PRODUCTLISTPERCUSTOMER
      */

ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (
       CHARGESERVICECOSTS   NUMBER DEFAULT 0 NOT NULL
                                   CHECK (CHARGESERVICECOSTS IN (0, 1, 2)),
       CHARGESERVICECOSTSFOR NUMBER DEFAULT 0 NOT NULL
                                   CHECK (CHARGESERVICECOSTSFOR IN (0, 1))
);


-- Add comments to the columns

comment on column PRODUCTLISTPERCUSTOMER.CHARGESERVICECOSTS
  is '0 = No service costs charged
      1 = Charge service costs with customer defined price
      2 = Charge service costs with specific price';



comment on column PRODUCTLISTPERCUSTOMER.CHARGESERVICECOSTSFOR
  is '0 = Real Quantities in circulation
      1 = Max Quantities in circulation';


ALTER TRIGGER CUSTOMER_CHECK_CONTRACT_USAGE DISABLE;

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

ALTER TRIGGER CUSTOMER_CHECK_CONTRACT_USAGE ENABLE;  