-- ABS-77612/ ABS-79964

-------------- ERP_I_AR_BALANCES --------------
CREATE TABLE ERP_I_AR_BALANCES
( TRANSFER_ID      NUMBER NOT NULL,
  TRANSFER_STATUS  NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID33 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  BUSINESSUNITCODE VARCHAR2(6) NOT NULL,
  CUSTOMERNUMBER   NUMBER NOT NULL,
  LASTINVOICEDATE  DATE NOT NULL,
  COD              VARCHAR2(1) DEFAULT 'N' NOT NULL,
  PAYMENTTERMCODE  VARCHAR2(6) 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);

COMMENT ON COLUMN ERP_I_AR_BALANCES.OPEN_BALANCE
  IS 'TOTAL OPEN AMOUNT';
COMMENT ON COLUMN ERP_I_AR_BALANCES.CURRENT_BALANCE
  IS 'BILLING BALANCECURRENT';

ALTER TABLE ERP_I_AR_BALANCES
  ADD CONSTRAINT PK_ERP_I_AR_BALANCES PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XIF1ERP_I_AR_BALANCES ON ERP_I_AR_BALANCES (BUSINESSUNITCODE ASC);
CREATE INDEX XIF2ERP_I_AR_BALANCES ON ERP_I_AR_BALANCES (CUSTOMERNUMBER ASC);

ALTER TABLE ERP_I_AR_BALANCES
  ADD CONSTRAINT FK_ERP_I_AR_BALANCES_BU FOREIGN KEY (BUSINESSUNITCODE)
  REFERENCES BUSINESSUNIT (CODE);
ALTER TABLE ERP_I_AR_BALANCES
  ADD CONSTRAINT FK_ERP_I_AR_BALANCES_CUST FOREIGN KEY (CUSTOMERNUMBER)
  REFERENCES CUSTOMER (CUSTOMERNUMBER);

ALTER TABLE ERP_I_AR_BALANCES_LOG
  ADD CONSTRAINT FK_ERP_I_AR_BALANCES_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_AR_BALANCES(TRANSFER_ID) ON DELETE CASCADE;