--order 502383      
      /*
      CHANGE REPORT for Table DWH_SETTINGS
          - modify validation rule from FISCAL_TYPE IN (445, 544) to FISCAL_TYPE IN (445, 544, 0) of column FISCAL_TYPE
          WARNING : Load data statement for table DWH_SETTINGS or the statement to add a validation rule may fail (existing data may violate the new validation rule for column FISCAL_TYPE).
          - Adding column PERIOD_TYPE
          - Adding column SHORT_DATE_FORMAT
          - Adding column LONG_DATE_FORMAT
          - Adding column NLS_LANGUAGE
          - Adding column NLS_TERRITORY
          - Adding column FISCAL_WEEK_OFFSET
          - Adding column FISCAL_MONTH_OFFSET
      ACTION is ALTER or modify Table DWH_SETTINGS
      */

ALTER TABLE DWH_SETTINGS MODIFY ( YEAR_START_DATE DEFAULT TO_DATE('01-01-2000','DD-MM-YYYY') );

ALTER TABLE DWH_SETTINGS DROP CONSTRAINT FISCALTYPE_VALID;

ALTER TABLE DWH_SETTINGS
  ADD CONSTRAINT FISCALTYPE_VALID
  CHECK (FISCAL_TYPE IN (445, 544, 0));

ALTER TABLE DWH_SETTINGS DROP COLUMN FISCALYEAR_START_DATE;

ALTER TABLE DWH_SETTINGS DROP COLUMN DAY_OF_FISCAL_WEEK;

ALTER TABLE DWH_SETTINGS ADD (PERIOD_TYPE          NUMBER DEFAULT 544 NOT NULL
                                   CONSTRAINT PeriodType_valid
                                          CHECK (PERIOD_TYPE IN (445, 544, 444))
    );

COMMENT ON COLUMN DWH_SETTINGS.PERIOD_TYPE IS 'Defines how the 13 weeks in a quarter are spread over the 3 months.
For generating date by calendar week dimension. 444 is for the situation when there needs to be 13 periods of 4 weeks.';

ALTER TABLE DWH_SETTINGS ADD (SHORT_DATE_FORMAT    VARCHAR2(30) DEFAULT 
    'FmDD-Mon-YYYY' NOT NULL);


ALTER TABLE DWH_SETTINGS ADD (LONG_DATE_FORMAT     VARCHAR2(30) DEFAULT 'FmDD Month YYYY' NOT NULL);


ALTER TABLE DWH_SETTINGS ADD (NLS_LANGUAGE         VARCHAR2(30) DEFAULT 'ENGLISH' 
    NOT NULL);


ALTER TABLE DWH_SETTINGS ADD (NLS_TERRITORY        VARCHAR2(30) DEFAULT 'AMERICA' 
    NOT NULL);


ALTER TABLE DWH_SETTINGS ADD (FISCAL_WEEK_OFFSET   NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN DWH_SETTINGS.FISCAL_WEEK_OFFSET IS'Defines the offset in weeks between ISO week and fiscal week. 0 Means that fiscal calendar runs parallel with regular calendar.';


ALTER TABLE DWH_SETTINGS ADD (FISCAL_MONTH_OFFSET  NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN DWH_SETTINGS.FISCAL_MONTH_OFFSET IS'Defines the offset in weeks between ISO week and fiscal week. 0 means that fiscal calendar runs parallel with regular calendar.';


      
      /*
      ACTION is CREATE Table SQARAGING
      */
CREATE SEQUENCE SEQ_SQARAGING
START WITH 1
INCREMENT BY 1;

CREATE TABLE SQARAGING (
       SQARAGING_ID         NUMBER NOT NULL,
       STATUSQUOHEADER_ID   NUMBER NOT NULL,
       CUSTOMER_LINK_ID     NUMBER NOT NULL,
       CURRENCY_ID          NUMBER NOT NULL,
       AMOUNTCURRENT        NUMBER(15,2) NULL,
       AMOUNT30             NUMBER(15,2) NULL,
       AMOUNT60             NUMBER(15,2) NULL,
       AMOUNT90             NUMBER(15,2) NULL,
       AMOUNT120            NUMBER(15,2) NULL,
       UNAPPLIEDCREDITAMOUNT NUMBER(15,2) NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1SQARAGING ON SQARAGING
(
       STATUSQUOHEADER_ID             ASC
);

CREATE INDEX XIF2SQARAGING ON SQARAGING
(
       CUSTOMER_LINK_ID               ASC
);

CREATE INDEX XIF3SQARAGING ON SQARAGING
(
       CURRENCY_ID                    ASC
);


ALTER TABLE SQARAGING
       ADD  ( CONSTRAINT XPKSQARAGING PRIMARY KEY (SQARAGING_ID) ) ;



      
      /*
      CHANGE REPORT for Table STATUSQUOHEADER
          - Changing column definition of STATUSQUOTYPE
      ACTION is ALTER or modify Table STATUSQUOHEADER
      */

COMMENT ON COLUMN STATUSQUOHEADER.STATUSQUOTYPE IS '1= Items In Circ
2= Sets
3= Wearers
4= Stock Quantities
5 = Lease
6 = Stock Charge
7 = Mats
8 = On-site Inventory
9 = Profitability by Account
10 = Commission
11 = Clean up
12 = Commission
13 = Max items in circ
14 = AR Aging
';


ALTER TABLE SQARAGING
       ADD  ( CONSTRAINT SQARAGING_CURRENCY_ID
              FOREIGN KEY (CURRENCY_ID)
                             REFERENCES CURRENCY ) ;


ALTER TABLE SQARAGING
       ADD  ( CONSTRAINT SQARAGING_CUSTOMER_LINK_ID
              FOREIGN KEY (CUSTOMER_LINK_ID)
                             REFERENCES CUSTOMER_LINK
                             ON DELETE CASCADE ) ;


ALTER TABLE SQARAGING
       ADD  ( CONSTRAINT SQARAGING_STATUSQUOHEADER_ID
              FOREIGN KEY (STATUSQUOHEADER_ID)
                             REFERENCES STATUSQUOHEADER
                             ON DELETE CASCADE ) ;



CREATE SEQUENCE SEQ_GROWTHHISTORY
START WITH 1
INCREMENT BY 1;

CREATE TABLE GROWTHHISTORY
(
  GROWTHHISTORY_ID  NUMBER NOT NULL,
  TRANSACTIONDATE   DATE NOT NULL,
  TRANSACTION_ID    NUMBER NOT NULL,
  BUSINESSUNIT_ID   NUMBER NOT NULL,
  ROUTE_ID          NUMBER,
  CUSTOMER_LINK_ID  NUMBER NOT NULL,
  PRODUCT_LINK_ID   NUMBER NOT NULL,
  WEAREREMP_LINK_ID NUMBER,
  CURRENCY_ID       NUMBER NOT NULL,
  QUANTITY          NUMBER NOT NULL,
  PRICE             NUMBER(15,4) NOT NULL,
  AMOUNT            NUMBER(15,2) NOT NULL,
  SYSTEMUSER_ID     NUMBER NOT NULL,
  TIMESTAMP         DATE NOT NULL,
  BILLINGMETHOD	    NUMBER DEFAULT 0 NOT NULL
);

CREATE INDEX XIF5GROWTHHISTORY ON GROWTHHISTORY (BUSINESSUNIT_ID);

CREATE INDEX XIF6GROWTHHISTORY ON GROWTHHISTORY (CUSTOMER_LINK_ID);

CREATE INDEX XIF7GROWTHHISTORY ON GROWTHHISTORY (PRODUCT_LINK_ID);

CREATE INDEX XIF8GROWTHHISTORY ON GROWTHHISTORY (WEAREREMP_LINK_ID);

CREATE INDEX XIF9GROWTHHISTORY ON GROWTHHISTORY (CURRENCY_ID);



ALTER TABLE GROWTHHISTORY
  ADD ( CONSTRAINT XPKGROWTHHISTORY PRIMARY KEY (GROWTHHISTORY_ID));
  
ALTER TABLE GROWTHHISTORY
  ADD ( CONSTRAINT GROWTHHISTORY_CURRENCY_ID FOREIGN KEY (CURRENCY_ID)
	  REFERENCES CURRENCY );

ALTER TABLE GROWTHHISTORY
  ADD ( CONSTRAINT GROWTHHISTORY_WEAREREMPL FOREIGN KEY (WEAREREMP_LINK_ID)
  	REFERENCES WEAREREMPLOYMENT_LINK );

  
ALTER TABLE GROWTHHISTORY
  ADD ( CONSTRAINT GROWTHHISTORY_PRODUCT_LINK_ID FOREIGN KEY (PRODUCT_LINK_ID)
	  REFERENCES PRODUCT_LINK );
  
ALTER TABLE GROWTHHISTORY
  ADD ( CONSTRAINT GROWTHHISTORY_CUSTOMER_LINK_ID FOREIGN KEY (CUSTOMER_LINK_ID)
	  REFERENCES CUSTOMER_LINK );

  
ALTER TABLE GROWTHHISTORY
  ADD ( CONSTRAINT GROWTHHISTORY_BUSINESSUNIT_ID FOREIGN KEY (BUSINESSUNIT_ID)
	  REFERENCES BUSINESSUNIT );

  
