-- ABS-77806
--CREATE TABLE SQ_DAILY_AR_U --
CREATE TABLE SQ_DAILY_AR_U
(
  SQ_DAILY_AR_U_ID  NUMBER NOT NULL,
  BUSINESSUNIT_ID   NUMBER NOT NULL,
  TURNOVERGROUP_ID  NUMBER NOT NULL,
  ARTRANSACTIONDATE DATE NOT NULL,
  DEBITCREDIT       VARCHAR2(1) NOT NULL CONSTRAINT CHK_SQ_DAILY_AR_U_V1 CHECK (DEBITCREDIT IN ('C','D')),
  FISCALMONTH       NUMBER(2) NOT NULL CONSTRAINT CHK_SQ_DAILY_AR_U_V2 CHECK (FISCALMONTH IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14)),
  DEBITAMOUNT       NUMBER DEFAULT 0 NOT NULL,
  CREDITAMOUNT      NUMBER DEFAULT 0 NOT NULL,
  PROCES_STATUS     NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_SQ_DAILY_AR_U_V3 CHECK (PROCES_STATUS IN (-1,0,1))
);
ALTER TABLE SQ_DAILY_AR_U
  ADD CONSTRAINT PK_SQ_DAILY_AR_U PRIMARY KEY (SQ_DAILY_AR_U_ID);
ALTER TABLE SQ_DAILY_AR_U
  ADD CONSTRAINT UK_SQ_DAILY_AR_U UNIQUE (BUSINESSUNIT_ID, TURNOVERGROUP_ID, ARTRANSACTIONDATE, DEBITCREDIT);
CREATE INDEX INDX_SQ_DAILY_AR_U_BUSS ON SQ_DAILY_AR_U(BUSINESSUNIT_ID); 
CREATE INDEX INDX_SQ_DAILY_AR_U_TURN ON SQ_DAILY_AR_U(TURNOVERGROUP_ID);
ALTER TABLE SQ_DAILY_AR_U
  ADD CONSTRAINT FK_SQ_DAILY_AR_U_BUSS FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT(BUSINESSUNIT_ID) ON DELETE CASCADE;
ALTER TABLE SQ_DAILY_AR_U
  ADD CONSTRAINT FK_SQ_DAILY_AR_U_TURN FOREIGN KEY (TURNOVERGROUP_ID)
  REFERENCES TURNOVERGROUP(TURNOVERGROUP_ID) ON DELETE SET NULL;
COMMENT ON COLUMN SQ_DAILY_AR_U.SQ_DAILY_AR_U_ID
  IS 'Transaction number';

-- CREATE TABLE ERP_O_LEDGERAMOUNTS --
CREATE TABLE ERP_O_LEDGERAMOUNTS
(
  TRANSFER_ID       NUMBER NOT NULL,
  TRANSFER_STATUS   NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_O_LEDGERAMOUNTS_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  SQ_DAILY_AR_U_ID  NUMBER NOT NULL,
  BUSINESSUNITCODE  VARCHAR2(6) NOT NULL,
  BUSINESSUNITDESC  VARCHAR2(30) NOT NULL,
  MONTHENDDATE      DATE NOT NULL,
  FISCALMONTH       NUMBER(2) NOT NULL,
  TURNOVERGROUP     VARCHAR2(12) NOT NULL,
  TURNOVERGROUPDESC VARCHAR2(30) NULL,
  DEBITAMOUNT       NUMBER DEFAULT 0 NOT NULL,
  CREDITAMOUNT      NUMBER DEFAULT 0 NOT NULL
);
COMMENT ON COLUMN ERP_O_LEDGERAMOUNTS.MONTHENDDATE
  IS 'Based on fisical calendar';
COMMENT ON COLUMN ERP_O_LEDGERAMOUNTS.SQ_DAILY_AR_U_ID
  IS 'Transaction number';
ALTER TABLE ERP_O_LEDGERAMOUNTS
  ADD CONSTRAINT PK_ERP_O_LEDGERAMOUNTS PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX_ERP_O_LEDGERAMOUNT_TRANS ON ERP_O_LEDGERAMOUNTS(TRANSFER_STATUS);  

-- ERP_O_LEDGERAMOUNTS_LOG --
CREATE TABLE ERP_O_LEDGERAMOUNTS_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_O_LEDGERAMOUNTS_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_LEDGERAMOUNTS_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN  ERP_O_LEDGERAMOUNTS_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_O_LEDGERAMOUNTS_LOG
  ADD CONSTRAINT PK_ERP_O_LEDGERAMOUNTS_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_LEDGERAMOUNTS_LOG
  ADD CONSTRAINT FK_ERP_O_LEDGERAMOUNTS_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_LEDGERAMOUNTS(TRANSFER_ID) ON DELETE CASCADE;

-- DATE_FISCALYEAR --
CREATE TABLE DATE_FISCALYEAR
(
  DATE_       DATE NOT NULL,
  FISCALYEAR  NUMBER(4) NOT NULL CONSTRAINT CHK_DATE_FISCALYEAR_V1 CHECK (FISCALYEAR BETWEEN 1999 AND 2099),
  FISCALMONTH NUMBER(2) NOT NULL CONSTRAINT CHK_DATE_FISCALYEAR_V2 CHECK (FISCALMONTH IN (1,2,3,4,5,6,7,8,9,10,11,12,13))
);
ALTER TABLE DATE_FISCALYEAR
  ADD CONSTRAINT XP_DATE_FISCALYEAR PRIMARY KEY (DATE_);