--ABS-168739

--ERP_U_DEPARTMENT TABLE
CREATE SEQUENCE SEQ_ERP_U_DEPARTMENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE ERP_U_DEPARTMENT (
TRANSFER_ID          NUMBER NOT NULL,
TRANSFER_STATUS      NUMBER DEFAULT 0 NOT NULL ,
CUSTOMERNUMBER       NUMBER NOT NULL,
LEGACYDEPARTMENTCODE VARCHAR2(12) NULL,
TIMESTAMP            DATE DEFAULT SYSDATE NOT NULL);

COMMENT ON COLUMN ERP_U_DEPARTMENT.TRANSFER_STATUS IS '-1 = processed stopped with errors 0 = ready to be read 1 = read correctly';

ALTER TABLE ERP_U_DEPARTMENT ADD CONSTRAINT XPKERP_U_DEPARTMENT PRIMARY KEY (TRANSFER_ID);

ALTER TABLE ERP_U_DEPARTMENT ADD CONSTRAINT CHK_ERP_U_DEPARTMENT_STATUS CHECK (TRANSFER_STATUS IN (-1, 0, 1));

--ERP_U_DEPARTMENT_LOG TABLE
CREATE TABLE ERP_U_DEPARTMENT_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_U_DEPARTMENT_LOG.TRANSFER_STATUS IS '-1 = processed stopped with errors 0 = ready to be read 1 = read correctly';
COMMENT ON COLUMN ERP_U_DEPARTMENT_LOG.PROCESS_STATUS IS '0 = processed correctly 1 = processed stopped with errors';

CREATE INDEX XIF1ERP_U_DEPARTMENT_LOG ON ERP_U_DEPARTMENT_LOG (TRANSFER_ID ASC); 

ALTER TABLE ERP_U_DEPARTMENT_LOG
ADD CONSTRAINT FK_ERP_U_DEPT_LOG FOREIGN KEY (TRANSFER_ID) REFERENCES ERP_U_DEPARTMENT (TRANSFER_ID);

ALTER TABLE ERP_U_DEPARTMENT_LOG ADD CONSTRAINT CHK_ERP_U_DEPTLOG_STATUS CHECK (TRANSFER_STATUS IN (-1, 0, 1));

ALTER TABLE ERP_U_DEPARTMENT_LOG ADD CONSTRAINT CHK_ERP_U_DEPTLOG_PRCSTATUS CHECK (PROCESS_STATUS IN (0, 1));
