--Order 20013177.80

---REASONCODE---
ALTER TABLE REASONCODE
	ADD (EXCLUDEEXCESSCHARGE VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField83 CHECK (EXCLUDEEXCESSCHARGE IN ('Y', 'N')));

COMMENT ON COLUMN REASONCODE.EXCLUDEEXCESSCHARGE IS 'When set to yes a delivery variation with this reasoncode will be excluded from excess calculation. Only applicable for module BM2EXCESS.';


---CUSTOMER---
ALTER TABLE CUSTOMER
	ADD (CLEANRETURNFREEOFCHARGE VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField84 CHECK (CLEANRETURNFREEOFCHARGE IN ('Y', 'N')));

COMMENT ON COLUMN CUSTOMER.CLEANRETURNFREEOFCHARGE IS 'Only used for module BM2EXCESS. When set to Y clean returns are excluded from excess.';


---PRODUCTLISTPERCUSTOMER---
ALTER TABLE PRODUCTLISTPERCUSTOMER
	ADD (CHARGEEXCESS NUMBER(1)  DEFAULT 0 NOT NULL CONSTRAINT ChargeExcess_valid1_1 CHECK (CHARGEEXCESS IN (0, 1, 2)));

COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.CHARGEEXCESS IS '0 = Do not charge excess / not applicable
1 = Charge excess per size
2 = Charge excess over all sizes';


---NONBUSINESSDAY---
COMMENT ON COLUMN NONBUSINESSDAY.CLOSESTATUS IS '1 = Closed
2 = Closed for delivery
3 = Open
4 = Closed including rent stop';


---INVOICEHEADER---
ALTER TABLE INVOICEHEADER
	ADD (
             RENTSTOPSTARTDATE DATE NULL,
             RENTSTOPENDDATE DATE NULL
	    );

COMMENT ON COLUMN INVOICEHEADER.RENTSTOPSTARTDATE IS 'Only used for module BM2EXCESS';

COMMENT ON COLUMN INVOICEHEADER.RENTSTOPENDDATE IS 'Only used for module BM2EXCESS';


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

COMMENT ON COLUMN PACKINGNOTELINE.DELIVEREDEXCLEXCESSQTY IS 'Quantity delivered that is not included for the Excess calculation. This attribute is used only for module BM2EXCESS.';


---INVOICELINEPRODUCT---
ALTER TABLE INVOICELINEPRODUCT
	ADD (NUMBEROFWEEKS NUMBER  DEFAULT 0 NOT NULL);

COMMENT ON COLUMN INVOICELINEPRODUCT.NUMBEROFWEEKS IS 'Only used for module BM2EXCESS, store the number of weeks for correction.';


---invoicelinesubtype---

insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             43,
             1,
             'Excess',
             1,
             sysdate
        from dual;
commit;

insert into invoicelinesubtype_desc
      (invoicelinesubtype_id,
       language_id,
       description,
       systemuser_id,
       timestamp)
      select i.invoicelinesubtype_id,
             l.language_id,
             i.factorydescription,
             1,
             sysdate
        from invoicelinesubtype i, languages l
       where l.translatedescription = 'Y' and  subtype = 43 and i.invoicelinetype_id = 1;
commit;
