-- ABS-7286

---------------------   STANDARDDELIVERY   -------------------------------------

ALTER TABLE STANDARDDELIVERY ADD (RECALCULATEMINSTOCKQTY VARCHAR2(1) DEFAULT 'N' NOT NULL 
                                    CONSTRAINT YN_VALIDATIONFIELD247 CHECK (RECALCULATEMINSTOCKQTY IN ('Y','N')),
                                  RECALCULATESTDDELIVERYQTY VARCHAR2(1) DEFAULT 'N' NOT NULL
                                    CONSTRAINT YN_VALIDATIONFIELD248 CHECK (RECALCULATESTDDELIVERYQTY IN ('Y','N')),
                                  CONSUMPTIONDAYS NUMBER DEFAULT 5 NOT NULL CONSTRAINT
                                    CONSUMPTIONDAYS_VALID CHECK (CONSUMPTIONDAYS BETWEEN 5 AND 7));

---------------------   STANDARDDELIVERYLINE   ---------------------------------

ALTER TABLE STANDARDDELIVERYLINE ADD (RECALCULATEMINSTOCKQTY VARCHAR2(1) DEFAULT 'N' NOT NULL 
                                        CONSTRAINT YN_VALIDATIONFIELD249 CHECK (RECALCULATEMINSTOCKQTY IN ('Y','N')),
                                      RECALCULATESTDDELIVERYQTY VARCHAR2(1) DEFAULT 'N' NOT NULL
                                        CONSTRAINT YN_VALIDATIONFIELD250 CHECK (RECALCULATESTDDELIVERYQTY IN ('Y','N')),
                                      MINSTOCKQTY NUMBER(4) NULL,
                                      MAXSTOCKQTY NUMBER(4) NULL);
                                      
ALTER TABLE STANDARDDELIVERYLINE ADD CONSTRAINT MINSTOCKQTY_CHECK CHECK (MINSTOCKQTY IS NULL OR (MINSTOCKQTY IS NOT NULL AND MINSTOCKQTY BETWEEN 0 AND 9999));

ALTER TABLE STANDARDDELIVERYLINE ADD CONSTRAINT MAXSTOCKQTY_CHECK CHECK (MAXSTOCKQTY IS NULL OR (MAXSTOCKQTY IS NOT NULL AND MAXSTOCKQTY BETWEEN 0 AND 9999));

----------------------   STANDARDDELIVERYAMOUNT   -----------------------------------

ALTER TABLE STANDARDDELIVERYAMOUNT ADD (PREVIOUSAMOUNT NUMBER(28,6) NULL);

COMMENT ON COLUMN STANDARDDELIVERYAMOUNT.PREVIOUSAMOUNT IS 'Only used when recalculating the standard delivery';

----------------------   NORMSTOCKAMOUNT   -----------------------------------

ALTER TABLE NORMSTOCKAMOUNT ADD (PREVIOUSAMOUNT NUMBER DEFAULT 0 NOT NULL);


----------------------   EMAILSETTINGS   -------------------------------------

ALTER TABLE EMAILSETTING
DROP CONSTRAINT DOCUMENTTYPEEMAILSETT_VALID;

ALTER TABLE EMAILSETTING ADD CONSTRAINT DOCUMENTTYPEEMAILSETT_VALID CHECK (DOCUMENTTYPE BETWEEN 1 AND 18);

COMMENT ON COLUMN EMAILSETTING.DOCUMENTTYPE IS '1 = Invoice 
2 = Statement
3 = Purchase order
4 = E-service password reminder
5 = Work order via web
6 = Invoice job checklist
7 = Customer
8 = Log Message
9 = Delivery Note / Consignment Note
10 = Stock Count / Order
11 = Wash List
12 = POD (Proof of delivery)
13 = On site soil scan list
14 = Shipment List
15 = Commission Report
16 = Wearer Web Login Credentials
17 = Recalculation Norm Stock Levels
18 = Recalculation Standard Deliveries';