--Order 20012138.90

----BUDGETUNIT table----

CREATE SEQUENCE SEQ_BUDGETUNIT
START WITH 1
INCREMENT BY 1;

CREATE TABLE BUDGETUNIT
(
  BUDGETUNIT_ID      NUMBER NOT NULL,
  CODE               VARCHAR2(6) NOT NULL,
  SYSTEMUSER_ID      NUMBER NOT NULL,
  TIMESTAMP          DATE NOT NULL
);

ALTER TABLE BUDGETUNIT
ADD CONSTRAINT  XPKBUDGETUNIT PRIMARY KEY (BUDGETUNIT_ID);

CREATE UNIQUE INDEX XAK1BUDGETUNIT ON BUDGETUNIT (CODE);

----BUDGETUNIT_DESC table----

CREATE TABLE BUDGETUNIT_DESC
(
  BUDGETUNIT_ID      NUMBER NOT NULL ,
  LANGUAGE_ID        NUMBER NOT NULL,
  DESCRIPTION        VARCHAR2(30) NOT NULL,
  SYSTEMUSER_ID      NUMBER NOT NULL,
  TIMESTAMP          DATE NOT NULL
);

ALTER TABLE BUDGETUNIT_DESC
ADD CONSTRAINT  XPKBUDGETUNIT_DESC PRIMARY KEY (BUDGETUNIT_ID, LANGUAGE_ID);

CREATE INDEX XIF1BUDGETUNIT_DESC ON BUDGETUNIT_DESC (BUDGETUNIT_ID);

CREATE INDEX XIF2BUDGETUNIT_DESC ON BUDGETUNIT_DESC (LANGUAGE_ID);

ALTER TABLE BUDGETUNIT_DESC
ADD CONSTRAINT BUDGETUNIT_DESC_BU FOREIGN KEY (BUDGETUNIT_ID) REFERENCES BUDGETUNIT (BUDGETUNIT_ID) ON DELETE CASCADE;

ALTER TABLE BUDGETUNIT_DESC
ADD CONSTRAINT BUDGETUNIT_DESC_LA FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID) ON DELETE CASCADE;


----BUDGET2 table----

CREATE SEQUENCE SEQ_BUDGET2
START WITH 1
INCREMENT BY 1;

CREATE TABLE BUDGET2
(
  BUDGET2_ID      NUMBER NOT NULL,
  BUDGETUNIT_ID   NUMBER NOT NULL,
  CUSTOMER_ID     NUMBER NOT NULL,
  DEPARTMENT_ID   NUMBER NULL,
  YEAR            NUMBER(4) NOT NULL CONSTRAINT YEAR_VALID1_1
                                      CHECK (YEAR BETWEEN 0 AND 9999),
  MONTH           NUMBER(2) NOT NULL CONSTRAINT MONTH_VALID1_1
                                      CHECK (MONTH BETWEEN 1 AND 12),
  QUANTITY        NUMBER(6) NOT NULL CONSTRAINT QUANTITY_VALID1_2
                                      CHECK (QUANTITY BETWEEN 0 AND 999999),
  SYSTEMUSER_ID   NUMBER NOT NULL,
  TIMESTAMP       DATE NOT NULL
);

ALTER TABLE BUDGET2
ADD CONSTRAINT  XPKBUDGET2 PRIMARY KEY (BUDGET2_ID);

CREATE UNIQUE INDEX XAK1BUDGET2 ON BUDGET2 (BUDGETUNIT_ID, CUSTOMER_ID, NVL(DEPARTMENT_ID,0), YEAR, MONTH);

CREATE INDEX XIF1BUDGET2 ON BUDGET2 (BUDGETUNIT_ID);

CREATE INDEX XIF2BUDGET2 ON BUDGET2 (CUSTOMER_ID);

CREATE INDEX XIF3BUDGET2 ON BUDGET2 (DEPARTMENT_ID);

ALTER TABLE BUDGET2
ADD CONSTRAINT BUDGET2_BUDGETUNIT FOREIGN KEY (BUDGETUNIT_ID) REFERENCES BUDGETUNIT (BUDGETUNIT_ID) ON DELETE CASCADE;

ALTER TABLE BUDGET2
ADD CONSTRAINT BUDGET2_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;

ALTER TABLE BUDGET2
ADD CONSTRAINT BUDGET2_DEPARTMENT FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT (DEPARTMENT_ID) ON DELETE CASCADE;


----CUSTOMERBUDGETDEFINITION table----

CREATE SEQUENCE SEQ_CUSTOMERBUDGETDEFINITION
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERBUDGETDEFINITION
(
  CUSTOMERBUDGETDEFINITION_ID   NUMBER NOT NULL,
  CUSTOMER_ID                   NUMBER NOT NULL,
  CODE                          VARCHAR2(6) NOT NULL,
  DESCRIPTION                   VARCHAR2(30) NOT NULL,
  BUDGETUNIT_ID                 NUMBER NOT NULL,
  STARTDATE                     DATE NOT NULL,
  ENDDATE                       DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  BUDGETTYPE                    NUMBER NOT NULL CONSTRAINT BUDGETTYPE_VALID1_1 CHECK (BUDGETTYPE IN (0, 1)),
  USEDFORINVOICING              VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD100 CHECK (USEDFORINVOICING IN ('Y', 'N')),
  USEDFORREPORTING              VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD101 CHECK (USEDFORREPORTING IN ('Y', 'N')),
  SYSTEMUSER_ID                 NUMBER NOT NULL,
  TIMESTAMP                     DATE NOT NULL
);

ALTER TABLE CUSTOMERBUDGETDEFINITION
ADD CONSTRAINT  XPKCUSTOMERBUDGETDEFINITION PRIMARY KEY (CUSTOMERBUDGETDEFINITION_ID);

CREATE INDEX XIF1CUSTOMERBUDGETDEFINITION ON CUSTOMERBUDGETDEFINITION (CUSTOMER_ID);

CREATE INDEX XIF2CUSTOMERBUDGETDEFINITION ON CUSTOMERBUDGETDEFINITION (BUDGETUNIT_ID);

CREATE UNIQUE INDEX XAK1CUSTOMERBUDGETDEFINITION ON CUSTOMERBUDGETDEFINITION (CUSTOMER_ID, CODE);

ALTER TABLE CUSTOMERBUDGETDEFINITION
ADD CONSTRAINT CUSTBUDGETDEF_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;

ALTER TABLE CUSTOMERBUDGETDEFINITION
ADD CONSTRAINT CUSTBUDGETDEF_BUDGETUNIT FOREIGN KEY (BUDGETUNIT_ID) REFERENCES BUDGETUNIT (BUDGETUNIT_ID);


----CUSTOMERBUDGETPRICE table----

CREATE SEQUENCE SEQ_CUSTOMERBUDGETPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERBUDGETPRICE
(
  CUSTOMERBUDGETPRICE_ID        NUMBER NOT NULL,
  CUSTOMERBUDGETDEFINITION_ID   NUMBER NOT NULL,
  STARTDATE                     DATE NOT NULL,
  PRICE                         NUMBER(15,6) NOT NULL,
  SYSTEMUSER_ID                 NUMBER NOT NULL,
  TIMESTAMP                     DATE NOT NULL
);

ALTER TABLE CUSTOMERBUDGETPRICE
ADD CONSTRAINT  XPKCUSTOMERBUDGETPRICE PRIMARY KEY (CUSTOMERBUDGETPRICE_ID);

CREATE INDEX XIF1CUSTOMERBUDGETPRICE ON CUSTOMERBUDGETPRICE (CUSTOMERBUDGETDEFINITION_ID);

CREATE UNIQUE INDEX XAK1CUSTOMERBUDGETPRICE ON CUSTOMERBUDGETPRICE (CUSTOMERBUDGETDEFINITION_ID, STARTDATE);

ALTER TABLE CUSTOMERBUDGETPRICE
ADD CONSTRAINT CUSTBUDGETPRICE_CUSTBUDGETDEF FOREIGN KEY (CUSTOMERBUDGETDEFINITION_ID) 
    REFERENCES CUSTOMERBUDGETDEFINITION (CUSTOMERBUDGETDEFINITION_ID) ON DELETE CASCADE;


----NOBUDGETDEPARTMENT table----

CREATE TABLE NOBUDGETDEPARTMENT
(
  CUSTOMERBUDGETDEFINITION_ID   NUMBER NOT NULL,
  DEPARTMENT_ID                 NUMBER NOT NULL,
  SYSTEMUSER_ID                 NUMBER NOT NULL,
  TIMESTAMP                     DATE NOT NULL
);


ALTER TABLE NOBUDGETDEPARTMENT
ADD CONSTRAINT  XPKNOBUDGETDEPARTMENT PRIMARY KEY (CUSTOMERBUDGETDEFINITION_ID, DEPARTMENT_ID);

CREATE INDEX XIF1NOBUDGETDEPARTMENT ON NOBUDGETDEPARTMENT (CUSTOMERBUDGETDEFINITION_ID);

CREATE INDEX XIF2NOBUDGETDEPARTMENT ON NOBUDGETDEPARTMENT (DEPARTMENT_ID);

ALTER TABLE NOBUDGETDEPARTMENT
ADD CONSTRAINT NOBUDGETDEPARTMENT_CUSTBUDGET FOREIGN KEY (CUSTOMERBUDGETDEFINITION_ID) 
               REFERENCES CUSTOMERBUDGETDEFINITION (CUSTOMERBUDGETDEFINITION_ID) ON DELETE CASCADE;

ALTER TABLE NOBUDGETDEPARTMENT
ADD CONSTRAINT NOBUDGETDEPARTMENT_DEPT FOREIGN KEY (DEPARTMENT_ID) 
               REFERENCES DEPARTMENT (DEPARTMENT_ID) ON DELETE CASCADE;


----NOBUDGETCLIENT table----

CREATE TABLE NOBUDGETCLIENT
(
  CUSTOMERBUDGETDEFINITION_ID   NUMBER NOT NULL,
  CLIENT_ID                     NUMBER NOT NULL,
  SYSTEMUSER_ID                 NUMBER NOT NULL,
  TIMESTAMP                     DATE NOT NULL
);

ALTER TABLE NOBUDGETCLIENT
ADD CONSTRAINT  XPKNOBUDGETCLIENT PRIMARY KEY (CUSTOMERBUDGETDEFINITION_ID, CLIENT_ID);

CREATE INDEX XIF1NOBUDGETCLIENT ON NOBUDGETCLIENT (CUSTOMERBUDGETDEFINITION_ID);

CREATE INDEX XIF2NOBUDGETCLIENT ON NOBUDGETCLIENT (CLIENT_ID);

ALTER TABLE NOBUDGETCLIENT
ADD CONSTRAINT NOBUDGETCLIENT_CUSTBUDGET FOREIGN KEY (CUSTOMERBUDGETDEFINITION_ID) 
               REFERENCES CUSTOMERBUDGETDEFINITION (CUSTOMERBUDGETDEFINITION_ID) ON DELETE CASCADE;

ALTER TABLE NOBUDGETCLIENT
ADD CONSTRAINT NOBUDGETCLIENT_DEPT FOREIGN KEY (CLIENT_ID) 
               REFERENCES CLIENT (CLIENT_ID) ON DELETE CASCADE;


----CUSTOMERINVOICEBUDGET table----

CREATE TABLE CUSTOMERINVOICEBUDGET
(
  CUSTOMERBUDGETDEFINITION_ID   NUMBER NOT NULL, 
  YEAR            NUMBER(4) NOT NULL CONSTRAINT YEAR_VALID1_2
                                      CHECK (YEAR BETWEEN 0 AND 9999),
  MONTH           NUMBER(2) NOT NULL CONSTRAINT MONTH_VALID1_2
                                      CHECK (MONTH BETWEEN 1 AND 12),
  QUANTITY        NUMBER(6) NOT NULL CONSTRAINT QUANTITY_VALID1_3
                                      CHECK (QUANTITY BETWEEN 0 AND 999999),
  SYSTEMUSER_ID                 NUMBER NOT NULL,
  TIMESTAMP                     DATE NOT NULL
);

ALTER TABLE CUSTOMERINVOICEBUDGET
ADD CONSTRAINT  XPKCUSTOMERINVOICEBUDGET PRIMARY KEY (CUSTOMERBUDGETDEFINITION_ID, YEAR, MONTH);

CREATE INDEX XIF1CUSTOMERINVOICEBUDGET ON CUSTOMERINVOICEBUDGET (CUSTOMERBUDGETDEFINITION_ID);

ALTER TABLE CUSTOMERINVOICEBUDGET
ADD CONSTRAINT CUSTINVBUDGET_CUSTBUDGETDEF FOREIGN KEY (CUSTOMERBUDGETDEFINITION_ID) 
    REFERENCES CUSTOMERBUDGETDEFINITION (CUSTOMERBUDGETDEFINITION_ID) ON DELETE CASCADE;


----PRODUCTLISTPERCUSTOMER table----


ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (BUDGETUSAGE NUMBER DEFAULT 0 NOT NULL CONSTRAINT BUDGETUSAGE_VALID1_1
                                                    CHECK (BUDGETUSAGE IN (0, 1, 2)));
ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (CUSTOMERBUDGETDEFINITION_ID NUMBER NULL);
 
COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.BUDGETUSAGE
  IS '0 = Not in Budget
1 = Self paid, not in Budget
2 = In Budget
';
 
CREATE INDEX XIF21PRODUCTLISTPERCUSTOMER ON PRODUCTLISTPERCUSTOMER (CUSTOMERBUDGETDEFINITION_ID);

ALTER TABLE PRODUCTLISTPERCUSTOMER
  ADD CONSTRAINT PLPC_CUSTBUDGETDEF FOREIGN KEY (CUSTOMERBUDGETDEFINITION_ID)
  REFERENCES CUSTOMERBUDGETDEFINITION (CUSTOMERBUDGETDEFINITION_ID) ON DELETE SET NULL;


----CLIENT table----

ALTER TABLE CLIENT ADD (BUDGETCLIENT NUMBER DEFAULT 0 NOT NULL CONSTRAINT BUDGETCLIENT_VALID1_1
                                     CHECK (BUDGETCLIENT IN (0, 1, 2)));
 
COMMENT ON COLUMN CLIENT.BUDGETCLIENT
  IS '0 = Client is not using budget and no self payer
1 = Client is not using budget and is self payer
2 = Client is using budget.
This attribute is only used for module TEXTILECONTROLLING.
';

----INVOICELINETYPE table----

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 29 = Capped Excess Amounts 30 = Client rent 31 = Budget';

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

insert into invoicelinetype_desc
          (invoicelinetype_id,
           language_id,
           description,
           systemuser_id,
           timestamp)
          select x.invoicelinetype_id,
                 l.language_id,
                 x.factorydescription,
                 x.systemuser_id,
                 x.timestamp
            from invoicelinetype x, languages l
            where x.invoicelinetype_id = 31;
commit;


----INVOICELINESUBTYPE table----

insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             0,
             31,
             'Budget',
             getminsystemuser(),
             sysdate
        from dual;
commit;
        
insert into invoicelinesubtype_desc
      (invoicelinesubtype_id,
       language_id,
       description,
       systemuser_id,
       timestamp)
      select i.invoicelinesubtype_id,
             l.language_id,
             i.factorydescription,
             1,
             sysdate
        from invoicelinesubtype i, languages l
       where l.translatedescription = 'Y' and i.invoicelinetype_id = 31;
commit; 


----INVOICELINE table----

ALTER TABLE INVOICELINE ADD (BUDGETCODE VARCHAR2(6) NULL);
ALTER TABLE INVOICELINE ADD (ORIGINALAMOUNT NUMBER(15,6) DEFAULT 0 NOT NULL);

COMMENT ON COLUMN INVOICELINE.BUDGETCODE
  IS 'Code from Budget. If client or product or department is not in a budget then it must be left empty. Only used for module TEXTILECONTROLLING.';
COMMENT ON COLUMN INVOICELINE.ORIGINALAMOUNT
  IS 'This field is used to store the original amount of the invoiceline. When in a budget the amount is recalculated. Only used for module TEXTILECONTROLLING.';

CREATE INDEX XIE2INVOICELINE ON INVOICELINE (INVOICEHEADER_ID, BUDGETCODE);


----COSTCENTERINVOICELINE table----

ALTER TABLE COSTCENTERINVOICELINE ADD (ORIGINALAMOUNT NUMBER(15,6) DEFAULT 0 NOT NULL);

COMMENT ON COLUMN COSTCENTERINVOICELINE.ORIGINALAMOUNT
  IS 'Only used for budget calculation';  
