-- ABS-79624/ ABS-81787

-- EXTERNAL_AR_BALANCES TABLE --
CREATE TABLE EXTERNAL_AR_BALANCES
(
  BUSINESSUNIT_ID NUMBER NOT NULL,
  CUSTOMER_ID     NUMBER NOT NULL,
  LASTINVOICEDATE DATE NOT NULL,
  COD             NUMBER NULL,
  PAYMENTTERMS_ID NUMBER NOT NULL,
  OPEN_BALANCE    NUMBER DEFAULT 0 NOT NULL,
  CURRENT_BALANCE NUMBER DEFAULT 0 NOT NULL,
  "30DAYS"        NUMBER DEFAULT 0 NOT NULL,
  "60DAYS"        NUMBER DEFAULT 0 NOT NULL,
  "90DAYS"        NUMBER DEFAULT 0 NOT NULL,
  "120DAYS"       NUMBER DEFAULT 0 NOT NULL,
  "120DAYSPLUS"   NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID   NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
)
;

ALTER TABLE EXTERNAL_AR_BALANCES
  ADD CONSTRAINT XPKEXTERNAL_AR_BALANCES PRIMARY KEY (BUSINESSUNIT_ID, CUSTOMER_ID);
  
CREATE INDEX XIF1EXTERNAL_AR_BALANCES ON EXTERNAL_AR_BALANCES (BUSINESSUNIT_ID ASC);
CREATE INDEX XIF2EXTERNAL_AR_BALANCES ON EXTERNAL_AR_BALANCES (CUSTOMER_ID ASC);
CREATE INDEX XIF3EXTERNAL_AR_BALANCES ON EXTERNAL_AR_BALANCES (PAYMENTTERMS_ID ASC);

ALTER TABLE EXTERNAL_AR_BALANCES
  ADD CONSTRAINT EXTERNAL_AR_BALANCES_BU FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);
ALTER TABLE EXTERNAL_AR_BALANCES
  ADD CONSTRAINT EXTERNAL_AR_BALANCES_CU FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;
ALTER TABLE EXTERNAL_AR_BALANCES
  ADD CONSTRAINT EXTERNAL_AR_BALANCES_PT FOREIGN KEY (PAYMENTTERMS_ID)
  REFERENCES PAYMENTTERMS (PAYMENTTERMS_ID);
