-- ABS-110301/ ABS-111225

-----------DELIVERYVARIATIONS table--------------------
COMMENT ON COLUMN DELIVERYVARIATIONS.ORIGIN
  IS '1 = soilcount 2 = stock count 3 = manual entry 4 = packing note 5 = delivery note 6 = electronic order 7 = replacement 8 = settlement 11 = Reduction 12 = BM6 Ordering';

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

-------- PRODUCT table -------------
ALTER TABLE PRODUCT ADD (ALLOWONEOFFORDERINGONRA VARCHAR2(1) DEFAULT 'N' NOT NULL
                                                 CONSTRAINT CHK_PRODUCT_ALLOWONEOFF CHECK (ALLOWONEOFFORDERINGONRA IN ('Y','N')));
 
COMMENT ON COLUMN PRODUCT.ALLOWONEOFFORDERINGONRA
  IS 'Defines if the product can be ordered for a one time delivery on the route assistant.  
Only used for module ROUTEASSISTANTONEOFFORDER
';

UPDATE PRODUCT P
SET P.ALLOWONEOFFORDERINGONRA = 'Y'
WHERE P.TYPEOFMERCHANDISE IN (1, 3);
COMMIT;

------- PACKINGNOTE table ---------
COMMENT ON COLUMN PACKINGNOTE.ORIGIN
  IS '0 = Generated based on Order 1 = Manually added 2 = Generated based on Outscan 3 = Quantity Correction Ticket 4 = Stock Delivery Note 5 = Special Order 6 = Bulk Note 7 = Bin Note 8 = Transfer Weights 9 = Import from DW 10 = Mounting Order 11 = Sales Order 12 = Credit Note 13 = Residential Delivery Note 14 = MultiReadShipment 15 = Charge To Product Process 16 = Customer Packing Note 17 = Curtain outscan 19 = BM6 Packing Note';

ALTER TABLE PACKINGNOTE
  DROP CONSTRAINT PNORIGIN_VALID1_1;

ALTER TABLE PACKINGNOTE
  ADD CONSTRAINT PNORIGIN_VALID1_1
  CHECK (ORIGIN BETWEEN 0 AND 19);

------- PACKINGNOTELINE table ---------
COMMENT ON COLUMN PACKINGNOTELINE.ORIGIN
  IS '0 = Generated based on Order 1 = Manually added 2 = Generated based on Outscan 3 = Quantity Correction Ticket 4 = Stock Delivery Note 9 = Import from DW 10 = Mounting Order 11 = Sales Order 12 = Credit Note 13 = Residential Delivery Note 15 = Charge To Product Process 16 = BM6 Ordering';

ALTER TABLE PACKINGNOTELINE
  DROP CONSTRAINT PNLORIGIN_VALID;
  
ALTER TABLE PACKINGNOTELINE
  ADD CONSTRAINT PNLORIGIN_VALID
  CHECK (ORIGIN IN (0, 1, 2, 3, 4, 9, 10, 11, 12, 13, 15, 16));