-- ABS-74890/ ABS-91359

-- INVOICELINETAX TABLE --
CREATE SEQUENCE SEQ_INVOICELINETAX
START WITH 1
INCREMENT BY 1;

CREATE TABLE INVOICELINETAX
(
  INVOICELINETAX_ID NUMBER NOT NULL,
  INVOICELINE_ID    NUMBER NOT NULL,
  JURISDICTIONLAYER NUMBER DEFAULT 0 NOT NULL
                    CONSTRAINT CHK_INVLTAX_JURISDICTIONLAYER CHECK (JURISDICTIONLAYER BETWEEN 0 AND 2),
  STATES_ID         NUMBER NULL,
  CITYCOUNTY_ID     NUMBER NULL,
  TAXAUTHORITYCODE  VARCHAR2(12) NULL,
  PERCENTAGE        NUMBER(7,3) NOT NULL,
  AMOUNT            NUMBER NOT NULL,
  SYSTEMUSER_ID     NUMBER NOT NULL,
  TIMESTAMP         DATE NOT NULL
)
;

COMMENT ON COLUMN INVOICELINETAX.JURISDICTIONLAYER
  IS '0 = State (US) or Country (EU)
1 = City
2 = County
For EU tax always 0
';

ALTER TABLE INVOICELINETAX
  ADD CONSTRAINT XPKINVOICELINETAX PRIMARY KEY (INVOICELINETAX_ID);
 
CREATE UNIQUE INDEX XAK1INVOICELINETAX ON INVOICELINETAX (INVOICELINE_ID ASC, JURISDICTIONLAYER ASC);

CREATE INDEX XIF1INVOICELINETAX ON INVOICELINETAX (INVOICELINE_ID ASC);
CREATE INDEX XIF2INVOICELINETAX ON INVOICELINETAX (STATES_ID ASC);
CREATE INDEX XIF3INVOICELINETAX ON INVOICELINETAX (CITYCOUNTY_ID ASC);

ALTER TABLE INVOICELINETAX
  ADD CONSTRAINT FK_INVLINETAX_INVOICELINE_ID FOREIGN KEY (INVOICELINE_ID)
  REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE CASCADE;
  
ALTER TABLE INVOICELINETAX
  ADD CONSTRAINT FK_INVLINETAX_STATES_ID FOREIGN KEY (STATES_ID)
  REFERENCES STATES (STATES_ID) ON DELETE SET NULL;
  
ALTER TABLE INVOICELINETAX
  ADD CONSTRAINT FK_INVLINETAX_CITYCOUNTY_ID FOREIGN KEY (CITYCOUNTY_ID)
  REFERENCES CITYCOUNTY (CITYCOUNTY_ID) ON DELETE SET NULL;
