--order 502563    
      /*
      CHANGE REPORT for Table DELIVERYVARIATIONS
          - Adding column SOILCOUNTSUBTYPE
          - Adding column definition to SOILCOUNTSUBTYPE
      ACTION is ALTER or modify Table DELIVERYVARIATIONS
      */

ALTER TABLE DELIVERYVARIATIONS ADD (SOILCOUNTSUBTYPE     NUMBER DEFAULT 0 NOT NULL
					CONSTRAINT SoilCountSubType_valid CHECK(SOILCOUNTSUBTYPE IN (0, 1, 2, 3)));


COMMENT ON COLUMN DELIVERYVARIATIONS.SOILCOUNTSUBTYPE IS '0 - Not applicable
1 - Regular soil count
2 - Shortage
3 - Rejected';

DROP INDEX XAK2DELIVERYVARIATIONS;

CREATE UNIQUE INDEX XAK2DELIVERYVARIATIONS ON DELIVERYVARIATIONS
(
       DISTRIBUTIONPOINT_ID ASC, 
       PRODUCT_ID ASC, 
       SIZEDEFINITION_ID ASC, 
       FINISHINGMETHOD_ID ASC, 
       PACKINGNOTE_ID ASC, 
       PROCESSED ASC, 
       DELIVERYDATE ASC, 
       ORIGIN ASC, 
       CAUSENOTE_ID ASC, 
       UPDATERENTQTY  ASC,
       SOILCOUNTSUBTYPE ASC
);

update deliveryvariations
   set origin = 1
 where origin not in (1, 2, 3, 4, 5, 6, 7, 8);

ALTER TABLE DELIVERYVARIATIONS 
      ADD (CONSTRAINT Origin_DV_valid CHECK(ORIGIN IN (1, 2, 3, 4, 5, 6, 7, 8)));

update deliveryvariations dv 
set dv.soilcountsubtype = 1
where dv.origin = 1;

      /*
      CHANGE REPORT for Table PACKINGNOTELINE
          - Adding column NOCHARGEPREVREJECTED
          - Adding column definition to NOCHARGEPREVREJECTED
          - Adding column NOCHARGEPREVSHORTAGE
          - Adding column definition to NOCHARGEPREVSHORTAGE
      ACTION is ALTER or modify Table PACKINGNOTELINE
      */

ALTER TABLE PACKINGNOTELINE ADD (NOCHARGEPREVREJECTED NUMBER DEFAULT 0 NOT NULL);


COMMENT ON COLUMN PACKINGNOTELINE.NOCHARGEPREVREJECTED IS 'Number of items that should not be charged since they are due to rejection of delivered items at a previous delivery. Otherwise they will be charged twice.';


ALTER TABLE PACKINGNOTELINE ADD (NOCHARGEPREVSHORTAGE NUMBER DEFAULT 0 NOT NULL);


COMMENT ON COLUMN PACKINGNOTELINE.NOCHARGEPREVSHORTAGE IS 'Number of items that should not be charged since they are due to a shortage when delivering items at a previous delivery. Otherwise they will be charged twice.';

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

COMMENT ON COLUMN ONSITEINVENTORYADJUSTMENT.ORIGIN IS '1 = Manual Correction
2 = Delivery Note
3 = Correction Ticket
4 = Soil Count
5 = Inscan
6 = Straight Even Return
7 = On-site Inventory Check
9 = Conversion
10 = Shortage At Delivery
11 = Rejected At Delivery';

      
      /*
      CHANGE REPORT for Table SOILCOUNTLINE
          - Adding column COUNTREJECTED
          - Adding column definition to COUNTREJECTED
          - Adding column COUNTSHORTAGE
          - Adding column definition to COUNTSHORTAGE
          - Adding column COUNTCUSTOMER
          - Adding column definition to COUNTCUSTOMER
      ACTION is ALTER or modify Table SOILCOUNTLINE
      */

ALTER TABLE SOILCOUNTLINE ADD (COUNTREJECTED        NUMBER DEFAULT 0 NOT NULL);


COMMENT ON COLUMN SOILCOUNTLINE.COUNTREJECTED IS 'Number of items reported as rejected (from customer) at last delivery to customer';


ALTER TABLE SOILCOUNTLINE ADD (COUNTSHORTAGE        NUMBER DEFAULT 0 NOT NULL);


COMMENT ON COLUMN SOILCOUNTLINE.COUNTSHORTAGE IS 'Number of items reported as shortage (from customer) at last delivery to customer';


ALTER TABLE SOILCOUNTLINE ADD (COUNTCUSTOMER        NUMBER DEFAULT 0 NOT NULL);


COMMENT ON COLUMN SOILCOUNTLINE.COUNTCUSTOMER IS 'Number of items reported as soil counted from customer';

