--Order 20013408.70

---SOILCOUNT---

ALTER TABLE SOILCOUNT
  ADD (BAGTAG_ID NUMBER NULL);

DROP INDEX XAK1SOILCOUNT;

CREATE UNIQUE INDEX XAK1SOILCOUNT ON SOILCOUNT
(DISTRIBUTIONPOINT_LINK_ID   ASC,COUNTDATE   ASC,PROCESSBUSINESSUNIT_ID   ASC,BAGTAG_ID   ASC);

CREATE UNIQUE INDEX XAK3SOILCOUNT ON SOILCOUNT (BAGTAG_ID ASC);

ALTER TABLE SOILCOUNT
ADD CONSTRAINT SOILCOUNT_BAGTAG FOREIGN KEY (BAGTAG_ID) REFERENCES BAGTAG (BAGTAG_ID) ON DELETE SET NULL;


---SOILCOUNTLINE---

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

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

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

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

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


---SOILHISTORY---

ALTER TABLE SOILHISTORY
	ADD (BAGTAG_ID NUMBER  DEFAULT 0 NOT NULL);

ALTER TABLE SOILHISTORY
	ADD (QUANTITYHEAVYSOIL NUMBER  DEFAULT 0 NOT NULL);

ALTER TABLE SOILHISTORY
	ADD (QUANTITYCLEANRETURN NUMBER  DEFAULT 0 NOT NULL);

COMMENT ON COLUMN SOILHISTORY.QUANTITYSOIL IS 'Soil quantity incl. abused,
Heavy soil and Clean 
return 
';

CREATE  INDEX XIE10SOILHISTORY ON SOILHISTORY
(BAGTAG_ID   ASC);

DROP INDEX XAK1SOILHISTORY;

CREATE UNIQUE INDEX XAK1SOILHISTORY ON SOILHISTORY
(CUSTOMER_LINK_ID   ASC,DISTRIBUTIONPOINT_LINK_ID   ASC,PRODUCT_LINK_ID   ASC,SIZEDEFINITION_LINK_ID   ASC,PROCESSBUSINESSUNIT_ID   ASC,RECEIVINGDATE   ASC,BAGTAG_ID   ASC);


---GENERALWASHPRICE---

COMMENT ON COLUMN GENERALWASHPRICE.PRICETYPE IS '1 = All items
2 = COG items only
4 = Split price
5 = Heavy Soil';

---CUSTOMERWASHPRICE---

COMMENT ON COLUMN CUSTOMERWASHPRICE.PRICETYPE IS '1 = All items
2 = COG items only
3 = Price on client delivery note
4 = Split
5 = Heavy Soil
';


---PACKINGNOTELINE---

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

COMMENT ON COLUMN PACKINGNOTELINE.HEAVYSOILQTY IS 'Quantity delivered with heavy soil. Only used for module HEAVYSOIL. The heavy soil is also included in the variation and to be delivered amount.';

---invoicelinesubtype---

insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             43,
             2,
             'Heavy Soil',
             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 = 2;
commit;