--ABSN-13952

--WEIGHINGTRANSACTION TABLE
ALTER TABLE WEIGHINGTRANSACTION ADD (PACKINGNOTE_ID NUMBER DEFAULT NULL);

COMMENT ON COLUMN WEIGHINGTRANSACTION.PACKINGNOTE_ID IS 'Foreign key to PackingNote table, is in fact only relevant in case of clean packing weighing, optional so may be left empty';

CREATE INDEX XIE3WEIGHINGTRANSACTION ON WEIGHINGTRANSACTION (WEIGHINGTRANSACTIONTYPE ASC, CONTAINER_ID ASC, PACKINGNOTE_ID ASC);
CREATE INDEX XIF11WEIGHINGTRANSACTION ON WEIGHINGTRANSACTION (PACKINGNOTE_ID ASC);

ALTER TABLE WEIGHINGTRANSACTION ADD CONSTRAINT FK_WEIGHINGTRANSACTION_PACKINGNOTE_ID FOREIGN KEY (PACKINGNOTE_ID) REFERENCES PACKINGNOTE (PACKINGNOTE_ID) ON DELETE SET NULL;

ALTER TABLE WEIGHINGTRANSACTION DROP CONSTRAINT CHK_WEIGHINGTRANSACTION_WEIGHINGTRANSTYPE;
ALTER TABLE WEIGHINGTRANSACTION ADD CONSTRAINT CHK_WEIGHINGTRANSACTION_WEIGHINGTRANSTYPE CHECK (WEIGHINGTRANSACTIONTYPE IN (1,2,3,4,5));
COMMENT ON COLUMN WEIGHINGTRANSACTION.WEIGHINGTRANSACTIONTYPE IS '1 = Soil Weighing 2 = Credit weighing 3 = Extraction weighing 4 = Clean return weighing 5 = Clean packing weighing';

--PACKINGNOTE TABLE
ALTER TABLE PACKINGNOTE ADD (PARENTPACKINGNOTE_ID NUMBER NULL);

COMMENT ON COLUMN PACKINGNOTE.PARENTPACKINGNOTE_ID IS 'Set to null for every normal packing note except for a container packing note that will be linked to its originating generated packing note';

CREATE INDEX XIF21PACKINGNOTE ON PACKINGNOTE (PARENTPACKINGNOTE_ID ASC);
ALTER TABLE PACKINGNOTE ADD CONSTRAINT FK_PACKINGNOTE_PARENTPACKINGNOTE_ID FOREIGN KEY (PARENTPACKINGNOTE_ID) REFERENCES PACKINGNOTE (PACKINGNOTE_ID) ON DELETE SET NULL;


