      
      /*
      CHANGE REPORT for Table CUSTOMER
          - modify validation rule from (empty) to (MasterAccount < 3 and MasterAccount_ID = Customer_ID) or (MasterAccount = 3 and MasterAccount_ID <> Customer_ID) of column MASTERACCOUNT
          WARNING : Load data statement for table CUSTOMER or the statement to add a validation rule may fail (existing data may violate the new validation rule for column MASTERACCOUNT).
          - modify default from (empty) to 0 of column CHARGEREPAIRS
          - Changing column definition of MINIMUMINVOICEAMOUNT
          - modify validation rule from DEPOSITSTOCKALLOWED IN ('Y',
      'N') to DEPOSITSTOCKALLOWED IN ('Y',
          'N') of column DEPOSITSTOCKALLOWED
          WARNING : Load data statement for table CUSTOMER or the statement to add a validation rule may fail (existing data may violate the new validation rule for column DEPOSITSTOCKALLOWED).
          - Changing column definition of STANDARDSBASEDON
          - Adding column CUSTOMERTYPE
          - Adding column ORDERINGSTOCKROOM_ID
          - Adding index XIF20CUSTOMER
          - Adding index XIF36CUSTOMER
      ACTION is ALTER or modify Table CUSTOMER
      */


create sequence SEQ_LOCATIONTYPE
start with 1 increment by 1;
      
      /*
      ACTION is CREATE Table LOCATIONTYPE
      */

CREATE TABLE LOCATIONTYPE (
       LOCATIONTYPE_ID      NUMBER NOT NULL,
       CODE                 VARCHAR2(12) NOT NULL,
       FACTORYDESCRIPTION   VARCHAR2(40) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       PICKINGLOCATION      VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew80
                                          CHECK (PICKINGLOCATION IN ('Y', 'N')),
       VALUECONTRIBUTION    VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew81
                                          CHECK (VALUECONTRIBUTION IN ('Y', 'N')),
       PRIORITY             NUMBER DEFAULT 0 NULL,
       ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew82
                                          CHECK (ACTIVE IN ('Y', 'N'))
);


ALTER TABLE LOCATIONTYPE
       ADD  ( CONSTRAINT XPKLOCATIONTYPE PRIMARY KEY (LOCATIONTYPE_ID) ) ;

      
      /*
      ACTION is CREATE Table LOCATIONTYPE_DESC
      */

CREATE TABLE LOCATIONTYPE_DESC (
       LOCATIONTYPE_ID      NUMBER NOT NULL,
       LANGUAGE_ID          NUMBER NOT NULL,
       DESCRIPTION          VARCHAR2(40) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1LOCATIONTYPE_DESC ON LOCATIONTYPE_DESC
(
       LOCATIONTYPE_ID                ASC
);

CREATE INDEX XIF2LOCATIONTYPE_DESC ON LOCATIONTYPE_DESC
(
       LANGUAGE_ID                    ASC
);


ALTER TABLE LOCATIONTYPE_DESC
       ADD  ( CONSTRAINT XPKLOCATIONTYPE_DESC PRIMARY KEY (
              LOCATIONTYPE_ID, LANGUAGE_ID) ) ;

      
      /*
      ACTION is CREATE Table LOCATIONTYPEPERSTOCKROOM
      */

CREATE TABLE LOCATIONTYPEPERSTOCKROOM (
       LOCATIONTYPE_ID      NUMBER NOT NULL,
       STOCKROOM_ID         NUMBER NOT NULL,
       DEFAULTLOCATION      VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew95
                                          CHECK (DEFAULTLOCATION IN ('Y', 'N')),
       PICKINGLOCATION      VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew59
                                          CHECK (PICKINGLOCATION IN ('Y', 'N')),
       VALUECONTRIBUTION    VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew60
                                          CHECK (VALUECONTRIBUTION IN ('Y', 'N')),	
       PRIORITY             NUMBER DEFAULT 0 NOT NULL,	
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1LOCATIONTYPEPERSTOCKROOM ON LOCATIONTYPEPERSTOCKROOM
(
       LOCATIONTYPE_ID                ASC
);

CREATE INDEX XIF2LOCATIONTYPEPERSTOCKROOM ON LOCATIONTYPEPERSTOCKROOM
(
       STOCKROOM_ID                   ASC
);


ALTER TABLE LOCATIONTYPEPERSTOCKROOM
       ADD  ( CONSTRAINT XPKLOCATIONTYPEPERSTOCKROOM PRIMARY KEY (
              LOCATIONTYPE_ID, STOCKROOM_ID) ) ;


COMMENT ON COLUMN CUSTOMER.MINIMUMINVOICEAMOUNT IS 'Minimum amount for invoice';


ALTER TABLE CUSTOMER ADD ( CONSTRAINT YN_ValidationNew54 
    CHECK(DEPOSITSTOCKALLOWED IN ('Y',
    'N')));


COMMENT ON COLUMN CUSTOMER.STANDARDSBASEDON IS '0 = Manual Entry
1 = Average per Week
2 = Average per day of week';


ALTER TABLE CUSTOMER ADD (CUSTOMERTYPE         NUMBER DEFAULT 0 NULL
                                   CONSTRAINT CustomerType_valid
                                          CHECK (CUSTOMERTYPE BETWEEN 0 AND 1))
    ;


ALTER TABLE CUSTOMER ADD (ORDERINGSTOCKROOM_ID NUMBER NULL);


CREATE INDEX XIF36CUSTOMER ON CUSTOMER
(
       ORDERINGSTOCKROOM_ID           ASC
);

      
      /*
      CHANGE REPORT for Table QUALITYGRADE
          - Adding column DEFECTITEM
          - Adding column STOCKVALUATIONMETHOD
      ACTION is ALTER or modify Table QUALITYGRADE
      */

ALTER TABLE QUALITYGRADE ADD (DEFECTITEM           VARCHAR2(1) DEFAULT 'N' NOT 
    NULL
                                   CONSTRAINT YN_ValidationNew85
                                          CHECK (DEFECTITEM IN ('Y', 'N')));


ALTER TABLE QUALITYGRADE ADD (STOCKVALUATIONMETHOD NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT StockValuationMethod_valid
                                          CHECK (STOCKVALUATIONMETHOD IN (0, 1, 
    2, 3)));

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

COMMENT ON COLUMN STANDARDLOCATIONPERPRODUCT.ALLOWEDGRADE IS 'N = only new allowed
U = only used allowed
B = both new and used allowed
D = only defect items
Default B';

      
      /*
      CHANGE REPORT for Table STOCKROOM
          - Adding column USEGRADECATEGORYDEFECT
          - Adding column INTERNALSUPPLIER
          - Adding column INTERNALCUSTOMER
      ACTION is ALTER or modify Table STOCKROOM
      */

ALTER TABLE STOCKROOM ADD (USEGRADECATEGORYDEFECT VARCHAR2(1) DEFAULT 'N' NOT 
    NULL
                                   CONSTRAINT YN_ValidationNew56
                                          CHECK (USEGRADECATEGORYDEFECT IN ('Y',
     'N')));


ALTER TABLE STOCKROOM ADD (INTERNALSUPPLIER     VARCHAR2(1) DEFAULT 'N' NOT 
    NULL
                                   CONSTRAINT YN_ValidationNew57
                                          CHECK (INTERNALSUPPLIER IN ('Y', 'N'))
    );


ALTER TABLE STOCKROOM ADD (INTERNALCUSTOMER     VARCHAR2(1) DEFAULT 'N' NOT 
    NULL
                                   CONSTRAINT YN_ValidationNew58
                                          CHECK (INTERNALCUSTOMER IN ('Y', 'N'))
    );

      
      /*
      CHANGE REPORT for Table STOCKROOMLOCATION
          - Adding a new relationship STOCKROOMLOCATION_LOCATIONTYPE
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK STOCKROOMLOCATION_LOCATIONTYPE of table STOCKROOMLOCATION, which references table LOCATIONTYPE).
          - Adding column LOCATIONTYPE_ID
          - Adding index XIF2STOCKROOMLOCATION
      ACTION is ALTER or modify Table STOCKROOMLOCATION
      */

ALTER TABLE STOCKROOMLOCATION ADD (LOCATIONTYPE_ID      NUMBER DEFAULT 0 NOT NULL);

CREATE INDEX XIF2STOCKROOMLOCATION ON STOCKROOMLOCATION
(
       LOCATIONTYPE_ID                ASC
);

      
      /*
      CHANGE REPORT for Table SUPPLIER
          - Adding a new relationship SUPPLIER_SUPPLYINGSTOCKROOM_ID
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK SUPPLIER_SUPPLYINGSTOCKROOM_ID of table SUPPLIER, which references table STOCKROOM).
          - Adding column SUPPLIERTYPE
          - Adding column SUPPLYINGSTOCKROOM_ID
          - Adding index XIF5SUPPLIER
      ACTION is ALTER or modify Table SUPPLIER
      */

ALTER TABLE SUPPLIER ADD (SUPPLIERTYPE  NUMBER DEFAULT 0 NOT NULL);


ALTER TABLE SUPPLIER ADD (SUPPLYINGSTOCKROOM_ID NUMBER NULL);

CREATE INDEX XIF5SUPPLIER ON SUPPLIER
(
       SUPPLYINGSTOCKROOM_ID          ASC
);

      
      /*
      CHANGE REPORT for Table SUPPLIERPRODUCT
          - modify validation rule from (empty) to PACKING BETWEEN 0 AND 3 of column PACKING
          WARNING : Load data statement for table SUPPLIERPRODUCT or the statement to add a validation rule may fail (existing data may violate the new validation rule for column PACKING).
          - Adding column TRANSPORTCHARGEPERCENTAGE
      ACTION is ALTER or modify Table SUPPLIERPRODUCT
      */

ALTER TABLE SUPPLIERPRODUCT ADD (TRANSPORTCHARGEPERCENTAGE NUMBER DEFAULT 0 NOT 
    NULL
                                   CONSTRAINT TransportCharge_valid
                                          CHECK (TRANSPORTCHARGEPERCENTAGE 
    BETWEEN 0 AND 100));





insert into locationtype (locationtype_id,code,factorydescription,systemuser_id,timestamp,pickinglocation,valuecontribution,priority,active)
values(seq_locationtype.nextval, 'INSPECT', 'Inspection', getminsystemuser(), getcurrentdatetime(),'N', 'Y', 0, 'N');

insert into locationtype (locationtype_id,code,factorydescription,systemuser_id,timestamp,pickinglocation,valuecontribution,priority,active)
values(seq_locationtype.nextval, 'QA', 'Quarantine', getminsystemuser(), getcurrentdatetime(),'N', 'Y', 0, 'N');

insert into locationtype (locationtype_id,code,factorydescription,systemuser_id,timestamp,pickinglocation,valuecontribution,priority,active)
values(seq_locationtype.nextval, 'BULK', 'Bulk', getminsystemuser(), getcurrentdatetime(),'N', 'Y', 0, 'N');

insert into locationtype (locationtype_id,code,factorydescription,systemuser_id,timestamp,pickinglocation,valuecontribution,priority,active)
values(seq_locationtype.nextval, 'PICK', 'Picking', getminsystemuser(), getcurrentdatetime(),'Y', 'Y', 1, 'Y');

insert into locationtype (locationtype_id,code,factorydescription,systemuser_id,timestamp,pickinglocation,valuecontribution,priority,active)
values(seq_locationtype.nextval, 'OVERFLOW', 'Overflow', getminsystemuser(), getcurrentdatetime(),'N', 'Y', 0, 'Y');

insert into locationtype (locationtype_id,code,factorydescription,systemuser_id,timestamp,pickinglocation,valuecontribution,priority,active)
values(seq_locationtype.nextval, 'DEFECT', 'Defect', getminsystemuser(), getcurrentdatetime(),'N', 'Y', 0, 'Y');

insert into locationtype (locationtype_id,code,factorydescription,systemuser_id,timestamp,pickinglocation,valuecontribution,priority,active)
values(seq_locationtype.nextval, 'SHOP', 'Shop Floor', getminsystemuser(), getcurrentdatetime(),'N', 'Y', 0, 'N');

insert into locationtype (locationtype_id,code,factorydescription,systemuser_id,timestamp,pickinglocation,valuecontribution,priority,active)
values(seq_locationtype.nextval, 'SERVICE', 'Service Truck', getminsystemuser(), getcurrentdatetime(),'N', 'Y', 0, 'Y');

insert into locationtype (locationtype_id,code,factorydescription,systemuser_id,timestamp,pickinglocation,valuecontribution,priority,active)
values(seq_locationtype.nextval, 'REPAIR', 'Repair', getminsystemuser(), getcurrentdatetime(),'N', 'Y', 0, 'N');

insert into locationtype (locationtype_id,code,factorydescription,systemuser_id,timestamp,pickinglocation,valuecontribution,priority,active)
values(seq_locationtype.nextval, 'RAG', 'Ragged', getminsystemuser(), getcurrentdatetime(),'N', 'Y', 0, 'N');

insert into locationtype (locationtype_id,code,factorydescription,systemuser_id,timestamp,pickinglocation,valuecontribution,priority,active)
values(seq_locationtype.nextval, 'BLOCK', 'Blocked', getminsystemuser(), getcurrentdatetime(),'N', 'Y', 0, 'Y');




update stockroomlocation sl
set sl.locationtype_id = (select lt.locationtype_id from locationtype lt where lt.code = 'PICK')
where sl.overflowlocation = 'N'
and sl.locationtype_id=0;

update stockroomlocation sl
set sl.locationtype_id = (select lt.locationtype_id from locationtype lt where lt.code = 'OVERFLOW')
where sl.overflowlocation = 'Y'
and sl.locationtype_id=0;


ALTER TABLE LOCATIONTYPE_DESC
       ADD  ( CONSTRAINT LOCATIONTYPE_D_LANGUAGE_ID
              FOREIGN KEY (LANGUAGE_ID)
                             REFERENCES LANGUAGES ) ;


ALTER TABLE LOCATIONTYPE_DESC
       ADD  ( CONSTRAINT LOCATIONTYPE_D_LOCATIONTYPE_ID
              FOREIGN KEY (LOCATIONTYPE_ID)
                             REFERENCES LOCATIONTYPE ) ;


ALTER TABLE LOCATIONTYPEPERSTOCKROOM
       ADD  ( CONSTRAINT LOCATIONTYPEPERSTOCKROOM_STOCK
              FOREIGN KEY (STOCKROOM_ID)
                             REFERENCES STOCKROOM ) ;


ALTER TABLE LOCATIONTYPEPERSTOCKROOM
       ADD  ( CONSTRAINT LOCATIONTYPEPERSTOCKROOM_LOCAT
              FOREIGN KEY (LOCATIONTYPE_ID)
                             REFERENCES LOCATIONTYPE ) ;


ALTER TABLE STOCKROOMLOCATION
       ADD  ( CONSTRAINT STOCKROOMLOCATION_LOCATIONTYPE
              FOREIGN KEY (LOCATIONTYPE_ID)
                             REFERENCES LOCATIONTYPE ) ;


ALTER TABLE SUPPLIER
       ADD  ( CONSTRAINT SUPPLIER_SUPPLYINGSTOCKROOM_ID
              FOREIGN KEY (SUPPLYINGSTOCKROOM_ID)
                             REFERENCES STOCKROOM ) ;


