-- ABS-94766/ ABS-99894

-- INVOICEHEADERREASON TABLE --
CREATE TABLE INVOICEHEADERREASON
(
  INVOICEHEADER_ID    NUMBER NOT NULL,
  CREDITREASONCODE_ID NUMBER NOT NULL,
  SYSTEMUSER_ID       NUMBER NOT NULL,
  TIMESTAMP           DATE NOT NULL
)
;

ALTER TABLE INVOICEHEADERREASON
  ADD CONSTRAINT XPKINVOICEHEADERREASON PRIMARY KEY (INVOICEHEADER_ID);
  
CREATE INDEX XIF1INVOICEHEADERREASON ON INVOICEHEADERREASON (CREDITREASONCODE_ID);

ALTER TABLE INVOICEHEADERREASON
  ADD CONSTRAINT FK_IHREASON_INVOICEHEADER FOREIGN KEY (INVOICEHEADER_ID)
  REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE CASCADE;
  
ALTER TABLE INVOICEHEADERREASON
  ADD CONSTRAINT FK_IHREASON_CREDITREASONCODE FOREIGN KEY (CREDITREASONCODE_ID)
  REFERENCES REASONCODE (REASONCODE_ID) ON DELETE SET NULL;

-- REASONCODE TABLE --
ALTER TABLE REASONCODE ADD (CREDITINVOICE VARCHAR2(1) DEFAULT 'N' NOT NULL
                                          CONSTRAINT CHK_REASONCODE_CREDITINVOICE CHECK (CREDITINVOICE IN ('Y', 'N')));

COMMENT ON COLUMN REASONCODE.CREDITINVOICE
  IS 'Used for marking a reason code as used for credit invoice reason codes.';
