--ABS-128820
CREATE TABLE ERP_I_STOCKROOM_INVENTORY
(
  TRANSFER_ID           NUMBER NOT NULL,
  TRANSFER_STATUS       NUMBER DEFAULT 0 NOT NULL,
  INVENTORYDATE         DATE DEFAULT TRUNC(SYSDATE) NOT NULL,
  STOCKROOMCODE         VARCHAR2(12) NOT NULL,
  LOCATIONCODE          VARCHAR2(12) DEFAULT 'NULL' NOT NULL,
  ROWCODE               VARCHAR2(12) NULL,
  AREACODE              VARCHAR2(12) NULL,
  BUSINESSUNIT          VARCHAR2(12) NOT NULL,
  PRIMARYID             VARCHAR2(24) NOT NULL,
  IDCODESEQUENCENUMBER  NUMBER DEFAULT 1 NOT NULL,
  TIMESTAMP             DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_STOCKROOM_INVENTORY.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_STOCKROOM_INVENTORY.INVENTORYDATE
  IS  'Date that stockroom inventory is taken';
COMMENT ON COLUMN ERP_I_STOCKROOM_INVENTORY.LOCATIONCODE
  IS  'When locationcode = ''NULL'', then the location/row/area codes within stockroom do not need to match';  
ALTER TABLE ERP_I_STOCKROOM_INVENTORY
 ADD CONSTRAINT PK_ERP_I_STOCKROOM_INVENTORY PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX01_ERP_I_STOCKROOM_INV ON ERP_I_STOCKROOM_INVENTORY(TRANSFER_STATUS);
ALTER TABLE ERP_I_STOCKROOM_INVENTORY
  ADD CONSTRAINT UK_ERP_I_STOCKROOM_INVENTORY UNIQUE (TRANSFER_ID, BUSINESSUNIT, PRIMARYID, IDCODESEQUENCENUMBER);
CREATE INDEX XIE1_ERP_I_STOCKROOM_INVENTORY ON ERP_I_STOCKROOM_INVENTORY(STOCKROOMCODE, LOCATIONCODE, ROWCODE);

CREATE TABLE ERP_I_STOCKROOM_INVENTORY_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_I_STOCKROOM_INV_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_I_STOCKROOM_INVENTORY_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_STOCKROOM_INVENTORY_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_STOCKROOM_INVENTORY_LOG
  ADD CONSTRAINT PK_ERP_I_STOCKROOM_INV_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_STOCKROOM_INVENTORY_LOG
  ADD CONSTRAINT FK_ERP_I_STOCKROOM_INV_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_STOCKROOM_INVENTORY(TRANSFER_ID) ON DELETE CASCADE;
 
  
CREATE TABLE ERP_O_STOCKROOM_INVENTORY
(
  TRANSFER_ID           NUMBER NOT NULL,
  TRANSFER_STATUS       NUMBER DEFAULT 0 NOT NULL,
  INVENTORYDATE         DATE DEFAULT TRUNC(SYSDATE) NOT NULL,
  STOCKROOMCODE         VARCHAR2(12) NOT NULL,
  LOCATIONCODE          VARCHAR2(12) DEFAULT 'NULL' NOT NULL,
  ROWCODE               VARCHAR2(12) NULL,
  AREACODE              VARCHAR2(12) NULL,
  BUSINESSUNIT          VARCHAR2(12) NOT NULL,
  PRIMARYID             VARCHAR2(24) NOT NULL,
  IDCODESEQUENCENUMBER  NUMBER DEFAULT 1 NOT NULL,
  FROM_STOCKROOMCODE    VARCHAR2(12) NULL,
  FROM_LOCATIONCODE     VARCHAR2(12) NULL,
  FROM_ROWCODE          VARCHAR2(12) NULL,
  FROM_AREACODE         VARCHAR2(12) NULL,
  PROCESS_STATUS        NUMBER DEFAULT 0 NOT NULL,
  STATUS_TEXT           VARCHAR2(1000) NULL,
  TIMESTAMP             DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_O_STOCKROOM_INVENTORY.TRANSFER_ID
  IS 'Will use the transfer status id from the ERP_I_STOCKROOM_INVENTORY table';
COMMENT ON COLUMN ERP_O_STOCKROOM_INVENTORY.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_O_STOCKROOM_INVENTORY.INVENTORYDATE
  IS  'Date that stockroom inventory is taken';
COMMENT ON COLUMN ERP_O_STOCKROOM_INVENTORY.LOCATIONCODE
  IS  'When locationcode = ''NULL'', then the location/row/area codes within stockroom do not need to match';
COMMENT ON COLUMN ERP_O_STOCKROOM_INVENTORY.PROCESS_STATUS IS
'PROCESS_STATUS	Possible value:
1: Found
2: Not found
3: Active in customer in current BU
4: Another stockroom
5: Unknown
6: Active in customer of another BU';
ALTER TABLE ERP_O_STOCKROOM_INVENTORY
 ADD CONSTRAINT PK_ERP_O_STOCKROOM_INVENTORY PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX01_ERP_O_STOCKROOM_INV ON ERP_O_STOCKROOM_INVENTORY(TRANSFER_STATUS);
ALTER TABLE ERP_O_STOCKROOM_INVENTORY
  ADD CONSTRAINT UK_ERP_O_STOCKROOM_INVENTORY UNIQUE (TRANSFER_ID, BUSINESSUNIT, PRIMARYID, IDCODESEQUENCENUMBER);
CREATE INDEX XIE1_ERP_O_STOCKROOM_INVENTORY ON ERP_O_STOCKROOM_INVENTORY(STOCKROOMCODE, LOCATIONCODE, ROWCODE);

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