--ABS-162550
CREATE TABLE ERP_I_SYSTEMUSER
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL,
  USERNAME            VARCHAR2(20) NOT NULL,
  PASSWORD            VARCHAR2(80) NOT NULL,
  FULLNAME            VARCHAR2(40) NOT NULL,
  USERGROUPNAME       VARCHAR2(20) NOT NULL,
  LANGUAGECODE        VARCHAR2(5) NOT NULL,
  ACTIVE              VARCHAR2(1) DEFAULT 'Y' NOT NULL,
  TIMESTAMP           DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_SYSTEMUSER.TRANSFER_STATUS
  IS '0 = to be processed, 1 = processed';
ALTER TABLE ERP_I_SYSTEMUSER ADD CONSTRAINT PK_ERP_I_SYSTEMUSER PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIE1_ERP_I_SYSTEMUSER ON ERP_I_SYSTEMUSER(USERNAME);
CREATE INDEX XIE2_ERP_I_SYSTEMUSER ON ERP_I_SYSTEMUSER(USERGROUPNAME);
ALTER TABLE ERP_I_SYSTEMUSER ADD CONSTRAINT CHK_ERP_I_SYSTEMUSER_ACTIVE CHECK (ACTIVE IN ('Y', 'N'));

CREATE TABLE ERP_I_SYSTEMUSER_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_SYSTEMUSER_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_SYSTEMUSER_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_SYSTEMUSER_LOG ADD CONSTRAINT PK_ERP_I_SYSTEMUSER_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_SYSTEMUSER_LOG ADD CONSTRAINT FK_ERP_I_SYSTEMUSER_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_SYSTEMUSER(TRANSFER_ID) ON DELETE CASCADE;

CREATE TABLE ERP_I_BUSINESSUNITSPERUSERGROUP
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL,
  GROUPNAME           VARCHAR2(20) NOT NULL,
  BUSINESSUNITCODE    VARCHAR2(6) NOT NULL,
  MODIFICATIONTYPE    VARCHAR2(1) NOT NULL,
  TIMESTAMP           DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_BUSINESSUNITSPERUSERGROUP.TRANSFER_STATUS
  IS '0 = to be processed, 1 = processed';
COMMENT ON COLUMN ERP_I_BUSINESSUNITSPERUSERGROUP.MODIFICATIONTYPE
  IS   'A = Add Business Unit to user group
D = Delete Business Unit from user group';
ALTER TABLE ERP_I_BUSINESSUNITSPERUSERGROUP ADD CONSTRAINT PK_ERP_I_BUPERUSERGROUP PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIE1_ERP_I_BUPERUSERGROUP ON ERP_I_BUSINESSUNITSPERUSERGROUP(GROUPNAME);
CREATE INDEX XIE2_ERP_I_BUPERUSERGROUP ON ERP_I_BUSINESSUNITSPERUSERGROUP(BUSINESSUNITCODE);
ALTER TABLE ERP_I_BUSINESSUNITSPERUSERGROUP ADD CONSTRAINT CHK_ERP_I_BUPERUSERGROUP_MODTYPE CHECK (MODIFICATIONTYPE IN ('A', 'D'));

CREATE TABLE ERP_I_BUSINESSUNITSPERUSERGROUP_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_BUSINESSUNITSPERUSERGROUP_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_BUSINESSUNITSPERUSERGROUP_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_BUSINESSUNITSPERUSERGROUP_LOG ADD CONSTRAINT PK_ERP_I_BUPERUSERGROUP_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_BUSINESSUNITSPERUSERGROUP_LOG ADD CONSTRAINT FK_ERP_I_BUPERUSERGROUP_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_BUSINESSUNITSPERUSERGROUP(TRANSFER_ID) ON DELETE CASCADE;
