-- ABS-33950/ ABS-46329

---CURRENCY table---
ALTER TABLE CURRENCY ADD (ROUNDTOTALINVOICEAMOUNT NUMBER DEFAULT 0 NOT NULL CONSTRAINT ROUNDTOTALINVAMOUNT_VALID1_1
                                                  CHECK (ROUNDTOTALINVOICEAMOUNT BETWEEN 0 AND 1),
                          ROUNDINGMETHOD NUMBER DEFAULT 0 NOT NULL CONSTRAINT ROUNDINGMETHOD_VALID1_1
                                                  CHECK (ROUNDINGMETHOD BETWEEN 0 AND 3));
                                                  
COMMENT ON COLUMN CURRENCY.ROUNDTOTALINVOICEAMOUNT
  IS '0 = No rounding is done
1 = Rounding will be done according to defined rounding method
';
COMMENT ON COLUMN CURRENCY.ROUNDINGMETHOD
  IS '0 = No rounding
1 = Round to 5 cents (to x.x5 or to x.x0)
2 = Round to 0 cents (to x.00)
3 = Round to the nearest number that is dividable by 5 (no decimals).
';

ALTER TABLE CURRENCY
  ADD CONSTRAINT ROUNDING_CHECK
  CHECK ((ROUNDTOTALINVOICEAMOUNT = 0 AND ROUNDINGMETHOD = 0) OR (ROUNDTOTALINVOICEAMOUNT > 0 AND ROUNDINGMETHOD > 0));


---CURRENCYEXCHANGERATE table---
CREATE SEQUENCE SEQ_CURRENCYEXCHANGERATE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CURRENCYEXCHANGERATE
(
  CURRENCYEXCHANGERATE_ID NUMBER NOT NULL,
  BASECURRENCY_ID         NUMBER NOT NULL,
  EXCHANGECURRENCY_ID     NUMBER NOT NULL,
  STARTDATE               DATE NOT NULL,
  EXCHANGERATE            NUMBER(15,6) NOT NULL,
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
)
;

COMMENT ON COLUMN CURRENCYEXCHANGERATE.EXCHANGERATE
  IS 'Exchange rate for the exchange currency when compared to base currency';

ALTER TABLE CURRENCYEXCHANGERATE
  ADD CONSTRAINT XPKCURRENCYEXCHANGERATE PRIMARY KEY (CURRENCYEXCHANGERATE_ID);
  
CREATE UNIQUE INDEX XAK1CURRENCYEXCHANGERATE ON CURRENCYEXCHANGERATE (BASECURRENCY_ID, EXCHANGECURRENCY_ID, STARTDATE);

CREATE INDEX XIF1CURRENCYEXCHANGERATE ON CURRENCYEXCHANGERATE (BASECURRENCY_ID ASC);
CREATE INDEX XIF2CURRENCYEXCHANGERATE ON CURRENCYEXCHANGERATE (EXCHANGECURRENCY_ID ASC);

ALTER TABLE CURRENCYEXCHANGERATE
  ADD CONSTRAINT CURRENCYEXCHRATE_BASECURRENCY FOREIGN KEY (BASECURRENCY_ID)
  REFERENCES CURRENCY (CURRENCY_ID) ON DELETE CASCADE;
  
ALTER TABLE CURRENCYEXCHANGERATE
  ADD CONSTRAINT CURRENCYEXCHRATE_EXCHCURRENCY FOREIGN KEY (EXCHANGECURRENCY_ID)
  REFERENCES CURRENCY (CURRENCY_ID) ON DELETE CASCADE;

ALTER TABLE CURRENCYEXCHANGERATE
  ADD CONSTRAINT CURRENCYEXCHANGERATE_CHECK1
  CHECK (BASECURRENCY_ID  =  EXCHANGECURRENCY_ID AND EXCHANGERATE = 1);
