--ABSN-4297

--LASTPRODUCTPRICEUSED TABLE
ALTER TABLE LASTPRODUCTPRICEUSED ADD (INVOICELINESUBTYPE NUMBER NULL);


--LASTSPECIALCHARGEPERCUSED TABLE
CREATE SEQUENCE SEQ_LASTSPECIALCHARGEPERCUSED
START WITH 1
INCREMENT BY 1;

CREATE TABLE LASTSPECIALCHARGEPERCUSED (
       LASTSPECIALCHARGEPERCUSED_ID 	NUMBER NOT NULL,
       CUSTOMER_LINK_ID 				NUMBER NOT NULL,
       SPECIALCHARGE_ID 				NUMBER NOT NULL,
       PERCENTAGE 						NUMBER NOT NULL,
	   GENERALPRICELIST_ID 				NUMBER NULL,
	   PRICELISTTYPE 					NUMBER NOT NULL,
	   INVOICEHEADER_ID 				NUMBER NULL,
	   CREATIONINVOICEHEADER_ID 		NUMBER NOT NULL,
       SYSTEMUSER_ID 					NUMBER NOT NULL,
       TIMESTAMP 						DATE NOT NULL);

COMMENT ON COLUMN LASTSPECIALCHARGEPERCUSED.PRICELISTTYPE IS 
	'1 = Customer Specific
	2 = General Price List
	3 = Contract Price List
	4 = Default price';
COMMENT ON COLUMN LASTSPECIALCHARGEPERCUSED.INVOICEHEADER_ID IS 'Updated with the header that this was discontinued on (so the first invoice it was no longer used on)';
COMMENT ON COLUMN LASTSPECIALCHARGEPERCUSED.CREATIONINVOICEHEADER_ID IS 'Set as the invoice header that created this line.';

ALTER TABLE LASTSPECIALCHARGEPERCUSED ADD CONSTRAINT XPK_LASTSPECIALCHARGEPERCUSED PRIMARY KEY (LASTSPECIALCHARGEPERCUSED_ID);

CREATE INDEX XIF1LASTSPECIALCHARGEPERCUSED ON LASTSPECIALCHARGEPERCUSED (CUSTOMER_LINK_ID ASC);
CREATE INDEX XIF2LASTSPECIALCHARGEPERCUSED ON LASTSPECIALCHARGEPERCUSED (SPECIALCHARGE_ID ASC);
CREATE INDEX XIF3LASTSPECIALCHARGEPERCUSED ON LASTSPECIALCHARGEPERCUSED (GENERALPRICELIST_ID ASC);
CREATE INDEX XIF4LASTSPECIALCHARGEPERCUSED ON LASTSPECIALCHARGEPERCUSED (INVOICEHEADER_ID ASC);
CREATE INDEX XIF5LASTSPECIALCHARGEPERCUSED ON LASTSPECIALCHARGEPERCUSED (CREATIONINVOICEHEADER_ID ASC);


ALTER TABLE LASTSPECIALCHARGEPERCUSED ADD CONSTRAINT FK_LASTSPCHPERCUSED_CUST_LINK_ID FOREIGN KEY (CUSTOMER_LINK_ID) REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);
ALTER TABLE LASTSPECIALCHARGEPERCUSED ADD CONSTRAINT FK_LASTSPCHPERCUSED_SPECIALCH_ID FOREIGN KEY (SPECIALCHARGE_ID) REFERENCES SPECIALCHARGE (SPECIALCHARGE_ID);
ALTER TABLE LASTSPECIALCHARGEPERCUSED ADD CONSTRAINT FK_LASTSPCHPERCUSED_GENPRICEL_ID FOREIGN KEY (GENERALPRICELIST_ID) REFERENCES GENERALPRICELIST (GENERALPRICELIST_ID) ON DELETE SET NULL;
ALTER TABLE LASTSPECIALCHARGEPERCUSED ADD CONSTRAINT FK_LASTSPCHPERCUSED_INVHEADER_ID FOREIGN KEY (INVOICEHEADER_ID) REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE SET NULL;
ALTER TABLE LASTSPECIALCHARGEPERCUSED ADD CONSTRAINT FK_LASTSPCHPERCUSED_CRINVHEAD_ID FOREIGN KEY (CREATIONINVOICEHEADER_ID) REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE SET NULL;