-- ABS-43648/ ABS-46393

---ARTRANSACTION table---
ALTER TABLE ARTRANSACTION ADD (EXCHANGERATEDATE DATE NULL,
                               EXCHANGERATE NUMBER(15,6) NULL,
                               PLANTCOUNTRYCURRENCY_ID NUMBER NULL);

COMMENT ON COLUMN ARTRANSACTION.EXCHANGERATE
  IS 'Exchange rate applicable on exchange rate date when compared to customer currency.';

CREATE INDEX XIF15ARTRANSACTION ON ARTRANSACTION (PLANTCOUNTRYCURRENCY_ID ASC);

ALTER TABLE ARTRANSACTION
  ADD CONSTRAINT ARTRANSACTION_PLANTCURRENCY FOREIGN KEY (PLANTCOUNTRYCURRENCY_ID)
  REFERENCES CURRENCY (CURRENCY_ID);

---CUSTOMER table---
ALTER TABLE CUSTOMER ADD (USESPECIFICINVOICECURRENCY NUMBER DEFAULT 0 NOT NULL CONSTRAINT USESPECINVCURRENCY_VALID1_1
  							CHECK (USESPECIFICINVOICECURRENCY IN (0, 1)),
                          INVOICECURRENCY_ID NUMBER NULL);

COMMENT ON COLUMN CUSTOMER.USESPECIFICINVOICECURRENCY
  IS 'This attribute indicates whether a specific invoice currency should be used or not. Possible values:
0 = Invoice in customer currency
1 = Invoice in specific currency
';

CREATE INDEX XIF51CUSTOMER ON CUSTOMER (INVOICECURRENCY_ID);

ALTER TABLE CUSTOMER
  ADD CONSTRAINT CUST_INVOICECURRENCY FOREIGN KEY (INVOICECURRENCY_ID)
  REFERENCES CURRENCY (CURRENCY_ID);

ALTER TABLE CUSTOMER
  ADD CONSTRAINT CHECK_SPEC_INVOICECURRENCY1
  CHECK ((USESPECIFICINVOICECURRENCY = 0) OR ((USESPECIFICINVOICECURRENCY = 1) AND (INVOICECURRENCY_ID IS NOT NULL)));
ALTER TABLE CUSTOMER
  ADD CONSTRAINT CHECK_SPEC_INVOICECURRENCY2
  CHECK ((INVOICECURRENCY_ID IS NULL) OR ((INVOICECURRENCY_ID IS NOT NULL) AND (INVOICECURRENCY_ID !=  CURRENCY_ID)));

---INVOICEHEADERCURRENCY table---
CREATE TABLE INVOICEHEADERCURRENCY
(
  INVOICEHEADER_ID    NUMBER NOT NULL,
  INVOICECURRENCYTYPE NUMBER DEFAULT 0 NOT NULL CONSTRAINT INVOICECURRENCYTYPE_VALID1_1
  			CHECK (INVOICECURRENCYTYPE BETWEEN 0 AND 2),
  CURRENCY_ID         NUMBER NOT NULL,
  EXCHANGERATEDATE    DATE NOT NULL,
  EXCHANGERATE        NUMBER(15,6) DEFAULT 0 NOT NULL,
  CREDITSQUEEZEAMOUNT NUMBER(15,6) DEFAULT 0 NOT NULL,
  ROUNDINGAMOUNT      NUMBER(15,6) DEFAULT 0 NOT NULL,
  PREBILLAMOUNT       NUMBER(15,6) DEFAULT 0 NOT NULL,
  TOTALAMOUNTEXCLVAT  NUMBER(15,6) DEFAULT 0 NOT NULL,
  PAYTHISAMOUNT       NUMBER(15,6) DEFAULT 0 NOT NULL,
  PAIDAMOUNT          NUMBER(28,6) DEFAULT 0 NOT NULL,
  UNDERUSEAMOUNT      NUMBER(15,6) DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID       NUMBER NOT NULL,
  TIMESTAMP           DATE NOT NULL
)
;

COMMENT ON COLUMN INVOICEHEADERCURRENCY.INVOICECURRENCYTYPE
  IS 'Indicates which kind of invoice currency it concerns. Possible values:
0 = Customer currency
1 = Customer invoice currency
2 = Plant country currency
';
COMMENT ON COLUMN INVOICEHEADERCURRENCY.EXCHANGERATE
  IS 'Exchange rate applicable on exchange rate date when compared to customer currency.';

ALTER TABLE INVOICEHEADERCURRENCY
  ADD CONSTRAINT XPKINVOICEHEADERCURRENCY PRIMARY KEY (INVOICEHEADER_ID, INVOICECURRENCYTYPE, CURRENCY_ID);
  
CREATE INDEX XIF1INVOICEHEADERCURRENCY ON INVOICEHEADERCURRENCY (INVOICEHEADER_ID ASC);
CREATE INDEX XIF2INVOICEHEADERCURRENCY ON INVOICEHEADERCURRENCY (CURRENCY_ID ASC);

ALTER TABLE INVOICEHEADERCURRENCY
  ADD CONSTRAINT INVOICEHEADERCURRENCY_IH FOREIGN KEY (INVOICEHEADER_ID)
  REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE CASCADE;
ALTER TABLE INVOICEHEADERCURRENCY
  ADD CONSTRAINT INVOICEHEADERCURRENCY_CURR FOREIGN KEY (CURRENCY_ID)
  REFERENCES CURRENCY (CURRENCY_ID);

---INVOICELINEVARIOUSCURRENCY table---
CREATE TABLE INVOICELINEVARIOUSCURRENCY
(
  INVOICELINE_ID      NUMBER NOT NULL,
  INVOICECURRENCYTYPE NUMBER DEFAULT 0 NOT NULL CONSTRAINT INVOICECURRENCYTYPE_VALID1_2
                             CHECK (INVOICECURRENCYTYPE BETWEEN 0 AND 2),
  CURRENCY_ID         NUMBER NOT NULL,
  OVERAMOUNT          NUMBER(15,6) DEFAULT 0 NOT NULL,
  VATAMOUNT           NUMBER(15,6) DEFAULT 0 NOT NULL,
  MINIMUMAMOUNT       NUMBER(15,6) NULL,
  MAXIMUMAMOUNT       NUMBER(15,6) NULL,
  SYSTEMUSER_ID       NUMBER NOT NULL,
  TIMESTAMP           DATE NOT NULL
)
;

COMMENT ON COLUMN INVOICELINEVARIOUSCURRENCY.INVOICECURRENCYTYPE
  IS 'Indicates which kind of invoice currency it concerns. Possible values:
0 = Customer currency
1 = Customer invoice currency
2 = Plant country currency
';

ALTER TABLE INVOICELINEVARIOUSCURRENCY
  ADD CONSTRAINT XPKINVOICELINEVARIOUSCURRENCY PRIMARY KEY (INVOICELINE_ID, INVOICECURRENCYTYPE, CURRENCY_ID);
  
CREATE INDEX XIF1INVOICELINEVARIOUSCURRENCY ON INVOICELINEVARIOUSCURRENCY (INVOICELINE_ID);
CREATE INDEX XIF2INVOICELINEVARIOUSCURRENCY ON INVOICELINEVARIOUSCURRENCY (CURRENCY_ID);

ALTER TABLE INVOICELINEVARIOUSCURRENCY
  ADD CONSTRAINT INVOICELINEVARIOUSCURRENCY_IL FOREIGN KEY (INVOICELINE_ID)
  REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE CASCADE;
  
ALTER TABLE INVOICELINEVARIOUSCURRENCY
  ADD CONSTRAINT INVOICELINEVARIOUSCURRENCY_C FOREIGN KEY (CURRENCY_ID)
  REFERENCES CURRENCY (CURRENCY_ID);

