-- ABS-77806
ALTER TABLE SQ_DAILY_AR_U ADD (CURRENCY_ID NUMBER NOT NULL);
CREATE INDEX INDX_SQ_DAILY_AR_U_CURR ON SQ_DAILY_AR_U(CURRENCY_ID);
ALTER TABLE SQ_DAILY_AR_U
  ADD CONSTRAINT FK_SQ_DAILY_AR_U_CURR FOREIGN KEY (CURRENCY_ID)
  REFERENCES CURRENCY(CURRENCY_ID) ON DELETE CASCADE;
ALTER TABLE SQ_DAILY_AR_U
  DROP CONSTRAINT UK_SQ_DAILY_AR_U CASCADE;
ALTER TABLE SQ_DAILY_AR_U
  ADD CONSTRAINT UK_SQ_DAILY_AR_U UNIQUE (BUSINESSUNIT_ID, CURRENCY_ID, TURNOVERGROUP_ID, ARTRANSACTIONDATE, DEBITCREDIT);
ALTER TABLE ERP_O_LEDGERAMOUNTS ADD (CURRENCY VARCHAR2(3) NOT NULL);

--NEW TABLE ERP_O_PEOPLESOFT_AR--
CREATE TABLE ERP_O_PEOPLESOFT_AR
(
  TRANSFER_ID       NUMBER NOT NULL,
  TRANSFER_STATUS   NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_O_PEOPLESOFT_AR_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  STG_TRANS_ID      NUMBER NOT NULL,
  STG_ACTION        VARCHAR2(1) DEFAULT 'I' NOT NULL,
  STG_TRANS_STATUS  VARCHAR2(1) DEFAULT 'U' NOT NULL,
  STG_DTTM_STAMP    DATE DEFAULT SYSDATE NOT NULL,
  BUSINESS_UNIT     VARCHAR2(6) NOT NULL,
  UNF_ENTRY_ID      VARCHAR2(10) NULL,
  LINE_NBR          NUMBER(10) NOT NULL,
  ACCOUNTING_DT     DATE NULL,
  OPERATING_UNIT    VARCHAR2(8) NULL,
  ACCOUNT           VARCHAR2(10) NOT NULL,
  DEPTID            VARCHAR2(10) NULL,
  AFFILIATE         VARCHAR2(5) NULL,
  MONETARY_AMOUNT   NUMBER NOT NULL,
  FISCAL_YEAR       NUMBER(5) NOT NULL,
  ACCOUNTING_PERIOD NUMBER(5) NOT NULL
);
COMMENT ON COLUMN ERP_O_PEOPLESOFT_AR.ACCOUNTING_PERIOD
  IS 'Fiscal month based on fisical calendar';
COMMENT ON COLUMN ERP_O_PEOPLESOFT_AR.STG_TRANS_ID
  IS 'Transaction (batch) number, unique per run';
COMMENT ON COLUMN ERP_O_PEOPLESOFT_AR.LINE_NBR
  IS 'Unique within STG_TRANS_ID Transaction (batch) number';
COMMENT ON COLUMN ERP_O_PEOPLESOFT_AR.STG_ACTION
  IS 'Always filled with I';
COMMENT ON COLUMN ERP_O_PEOPLESOFT_AR.STG_TRANS_STATUS
  IS 'Always filled with U';
COMMENT ON COLUMN ERP_O_PEOPLESOFT_AR.ACCOUNT
  IS 'LEDGERACCOUNT';
COMMENT ON COLUMN ERP_O_PEOPLESOFT_AR.MONETARY_AMOUNT
  IS '(SUM) ARTRANSACTION AMOUNT';
ALTER TABLE ERP_O_PEOPLESOFT_AR
  ADD CONSTRAINT PK_ERP_O_PEOPLESOFT_AR PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX_ERP_O_PEOPLESOFT_AR_STAT ON ERP_O_PEOPLESOFT_AR(TRANSFER_STATUS);

-- ERP_O_PEOPLESOFT_AR_LOG --
CREATE TABLE ERP_O_PEOPLESOFT_AR_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_O_PEOPLESOFT_AR_LOG_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN  ERP_O_PEOPLESOFT_AR_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN  ERP_O_PEOPLESOFT_AR_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_O_PEOPLESOFT_AR_LOG
  ADD CONSTRAINT PK_ERP_O_PEOPLESOFT_AR_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_PEOPLESOFT_AR_LOG
  ADD CONSTRAINT FK_ERP_O_PEOPLESOFT_AR_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_PEOPLESOFT_AR(TRANSFER_ID) ON DELETE CASCADE;