--SO 20014560.90

--------------------------------SOILTICKET TABLE---------------------------

CREATE INDEX XIE3SOILTICKET ON SOILTICKET
(PROCESSBUSINESSUNIT_ID   ASC,INVOICENUMBER   ASC);


--------------------------------SOILTICKETLINE TABLE---------------------------

ALTER TABLE SOILTICKETLINE
	ADD (COUNTLOSS NUMBER  DEFAULT 0 NOT NULL);

COMMENT ON COLUMN SOILTICKETLINE.COUNTKEYPAD IS 'Number of items counted manuallyNull if not yet counted. (in application fixed value -999). To distinct between no count and a zero count. Quantity excl. abused and loss.';

COMMENT ON COLUMN SOILTICKETLINE.COUNTCONVERTED IS 'Number of pieces determined via weight to piece conversion. Later used in interfaces.Null if not yet counted. (in application fixed value -999). To distinct between no count and a zero count. Quantity excl. abused and loss.';

COMMENT ON COLUMN SOILTICKETLINE.COUNTLOSS IS 'Number of loss items, to be charged for replacement';


--------------------------------SOILHISTORY TABLE---------------------------

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


--------------------------------DELIVERYVARIATIONS TABLE---------------------------

BEGIN
EXECUTE IMMEDIATE ('ALTER TABLE DELIVERYVARIATIONS
  DROP CONSTRAINT SOILCOUNTSUBTYPE_VALID');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
ALTER TABLE DELIVERYVARIATIONS
  ADD CONSTRAINT SOILCOUNTSUBTYPE_VALID
  CHECK (SOILCOUNTSUBTYPE IN (0,1,2,3,4,5,6));


COMMENT ON COLUMN DELIVERYVARIATIONS.SOILCOUNTSUBTYPE IS '0 - Not applicable
1 - Regular soil count
2 - Shortage
3 - Rejected
4 = Heavy Soil
5 = Clean Return
6 = Loss
Default value 0
Note: option 6 is used in combination with Origin = 7 (Replacement). Others are used in combination with Origin = 1 (soil count).
';

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


--------------------------------INVOICELINESUBTYPE table-------------------------

insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             1,
             7,
             'Loss (replacement)',
             getminsystemuser(),
             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 i.invoicelinetype_id = 7 and i.subtype = 1;
commit; 
