-- ABS-158164 Rebill some negative invoice adjustments on next invoice

--INVOICELINEREBILL TABLE
CREATE TABLE INVOICELINEREBILL
(
  ROUTETRANSACTION_ID NUMBER NOT NULL,
  ADJUSTEDINVOICELINE_ID NUMBER NOT NULL,
  CONTACT_ID NUMBER NULL,
  REBILLEDINVOICELINE_ID NUMBER NULL,
  SYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP DATE
);

ALTER TABLE INVOICELINEREBILL ADD CONSTRAINT XPKINVOICELINEREBILL PRIMARY KEY (ROUTETRANSACTION_ID);

CREATE UNIQUE INDEX XAK1INVOICELINEREBILL ON INVOICELINEREBILL (ADJUSTEDINVOICELINE_ID ASC);
CREATE INDEX XIF1INVOICELINEREBILL ON INVOICELINEREBILL (CONTACT_ID ASC);
CREATE INDEX XIF2INVOICELINEREBILL ON INVOICELINEREBILL (REBILLEDINVOICELINE_ID ASC);

ALTER TABLE INVOICELINEREBILL
  ADD CONSTRAINT FK_INVOICELINEREBILL_ROUTETRANSACTION FOREIGN KEY (ROUTETRANSACTION_ID)
  REFERENCES ROUTETRANSACTION (ROUTETRANSACTION_ID) ON DELETE CASCADE;
  
ALTER TABLE INVOICELINEREBILL
  ADD CONSTRAINT FK_INVOICELINEREBILL_ADJUSTEDINVOICELINE FOREIGN KEY (ADJUSTEDINVOICELINE_ID)
  REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE CASCADE;

ALTER TABLE INVOICELINEREBILL
  ADD CONSTRAINT FK_INVOICELINEREBILL_CONTACT FOREIGN KEY (CONTACT_ID)
  REFERENCES CONTACT (CONTACT_ID) ON DELETE SET NULL;
  
ALTER TABLE INVOICELINEREBILL  
  ADD CONSTRAINT FK_INVOICELINEREBILL_REBILLEDINVOICELINE FOREIGN KEY (REBILLEDINVOICELINE_ID)
  REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE SET NULL;
  
--INVOICELINETYPE TABLE 
ALTER TABLE INVOICELINETYPE ADD (REBILLADJUSTMENT VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_INVOICELINETYPE_REBILLADJUSTMENT CHECK (REBILLADJUSTMENT IN ('Y', 'N')));

COMMENT ON COLUMN INVOICELINETYPE.REBILLADJUSTMENT IS 'Only used for module UNFCUSTOM';
  
