CREATE TABLE ERP_I_UNIQUEITEM
(
TRANSFER_ID     NUMBER NOT NULL,
TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL 
  CONSTRAINT CHK_ERP_I_UNIQUEITEM_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
BUSINESSUNIT             VARCHAR2(6) NOT NULL,
PRIMARYID                VARCHAR2(24) NOT NULL,
IDCODESEQUENCENUMBER     NUMBER DEFAULT 1 NOT NULL,
SECONDARYID              VARCHAR2(24),
STATUS                   NUMBER NOT NULL,
STAY                     NUMBER NOT NULL,
CUSTOMERNUMBER           NUMBER NULL,
WEARERNUMBER             NUMBER NULL,
WEAREREMPLOYMENTNUMBER   NUMBER NULL,
PRODUCTCODE              VARCHAR2(12) NOT NULL,
SIZECODE                 VARCHAR2(12) NOT NULL,
FINISHINGMETHODCODE      VARCHAR2(6) DEFAULT '-' NOT NULL,
GARMENTSEQUENCENUMBER    NUMBER NULL,
WASHESTOTAL              NUMBER DEFAULT 0 NULL,
REPAIRTOTAL              NUMBER DEFAULT 0 NULL,
REWASHESTOTAL            NUMBER DEFAULT 0 NULL,
WASHESTHISISSUE          NUMBER NULL,
REPAIRTHISISSUE          NUMBER NULL,
REWASHESTHISISSUE        NUMBER NULL,
CUSTOMEROWNED            VARCHAR2(1) DEFAULT 'N' NULL,
RELATEDCUSTOMERNUMBER    NUMBER NULL,
DELIVERFROMSTOCK         VARCHAR2(1) DEFAULT 'Y' NULL,
LASTINSCANDATE           DATE NULL,
STARTRENTFROM            NUMBER DEFAULT 2 NULL,
LASTOUTSCANDATE          DATE NULL,
LASTSCANDATE             DATE NULL,
STATUSCHANGEDATE         DATE NULL,
STAYCHANGEDATE           DATE NULL,
PURCHASEDATE             DATE DEFAULT TRUNC(SYSDATE)-1 NULL,
NUMBEROFISSUES           NUMBER DEFAULT 1 NULL,
FIRSTISSUEDATE           DATE NULL,
LASTISSUEDATE            DATE NULL,
ENDDATEFULLRENT          DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NULL,
DAYSINCIRCPREVISSUE      NUMBER DEFAULT 0 NULL,
DAYSINSTOCKPREVISSUE     NUMBER DEFAULT 0 NULL,
SUPPLIERNUMBER           NUMBER DEFAULT 1 NULL,
PURCHASEPRICE            NUMBER(15,6) DEFAULT 0 NULL,
CURRENCYCODE             VARCHAR2(3) NULL,
QUALITYGRADECODE         VARCHAR2(1) NULL,
FREEEXTRAGARMENTS        VARCHAR2(1) DEFAULT 'N' NULL,
ENDDATEFREEEXTRA         DATE NULL,
MODIFICATIONCODE1        VARCHAR2(10) NULL,
MODIFICATIONDESCRIPTION1 VARCHAR2(40) NULL,
MODIFICATIONCODE2        VARCHAR2(10) NULL,
MODIFICATIONDESCRIPTION2 VARCHAR2(40) NULL,
MODIFICATIONCODE3        VARCHAR2(10) NULL,
MODIFICATIONDESCRIPTION3 VARCHAR2(40) NULL,
REASONCODE               VARCHAR2(6) NULL,
FLAGCODE                 VARCHAR2(6) NULL,
FLAGDESCRIPTION          VARCHAR2(50) NULL,
FLAGSTARTDATE            DATE NULL,
FLAGFREETEXT             VARCHAR2(40) NULL,
REMARK                   VARCHAR2(30) NULL,
STOCKROOMCODE            VARCHAR2(12) NULL,
LOCATIONCODE             VARCHAR2(12) NULL,
ROWCODE                  VARCHAR2(12) NULL,
POOLITEM                 NUMBER DEFAULT 2 NOT NULL,
TYPEOFMERCHANDISE        NUMBER DEFAULT 2 NOT NULL
);
COMMENT ON COLUMN ERP_I_UNIQUEITEM.STATUS
  IS '10 Stock
20 In Circulation
30 Assigned (assigned to be in circ in future)';
COMMENT ON COLUMN ERP_I_UNIQUEITEM.STAY
  IS '10  In Stockroom
11  STOCKROOM/PICKED
20  Outscan/At Customer
22  Clean in Garment Dispenser
23  Desk Scan/Issue to Wearer
24  Out of Garment Dispenser
25  Soiled in Garment Dispenser
26  OUTSCAN/TRANSFER STOCK
27  On-site at Customer
28  Sent to Laundry
30  In Scan/Soiled Received
31  Rewash
32  Repair/Mending
33  Checkpoint/Inspection
34  Sorted
35  OR Packing
36  Late Piece
37  Deposit Stock
38  Treatment
39  Bundle Scan
40  ON SHELF';
COMMENT ON COLUMN ERP_I_UNIQUEITEM.STARTRENTFROM
  IS '2 = Moment of delivery  (outscan)
3 = Moment of taking in use (inscan or desk issue, dispenser issue)
4 = Specific future date (date stored in issue date)
5 = Requested delivery date  (date stored in issue date)';
COMMENT ON COLUMN ERP_I_UNIQUEITEM.POOLITEM
  IS '0 = Stock
1 = Pool
2 = Non Pool
3 = OR Pack';
COMMENT ON COLUMN ERP_I_UNIQUEITEM.TYPEOFMERCHANDISE
  IS 'TYPEOFMERCHANDISE Can 1 or 2, DEFAULT=2, 1=FOR POOL';
COMMENT ON COLUMN ERP_I_UNIQUEITEM.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_I_UNIQUEITEM
 ADD CONSTRAINT PK_ERP_I_UNIQUEITEM PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIF1ERP_I_UNIQUEITEM ON ERP_I_UNIQUEITEM (BUSINESSUNIT);
CREATE INDEX XIF2ERP_I_UNIQUEITEM ON ERP_I_UNIQUEITEM (CUSTOMERNUMBER, WEARERNUMBER, WEAREREMPLOYMENTNUMBER);
CREATE INDEX XIF3ERP_I_UNIQUEITEM ON ERP_I_UNIQUEITEM (PRODUCTCODE);
CREATE INDEX XIF5ERP_I_UNIQUEITEM ON ERP_I_UNIQUEITEM (STOCKROOMCODE);
CREATE INDEX XIF6ERP_I_UNIQUEITEM ON ERP_I_UNIQUEITEM (SECONDARYID);
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT CHK_ERP_I_UNIQUEI_POOLCOMBO
  CHECK ((POOLITEM = 0 AND STATUS = 10) OR POOLITEM <> 0);
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT CHK_I_ERPUNIQUEITEM_ISSUEDATES
  CHECK ((POOLITEM = 3) OR
(FIRSTISSUEDATE IS NOT NULL AND LASTISSUEDATE IS NOT NULL AND FIRSTISSUEDATE <= LASTISSUEDATE) OR
(FIRSTISSUEDATE IS NULL AND LASTISSUEDATE IS NULL AND STATUS <> '20'));
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT I_ERP_UNIQUEITEM_BU
  CHECK (BUSINESSUNIT=UPPER(TRIM(BUSINESSUNIT)));
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT I_ERP_UNIQUEITEM_CUSTRELATED
  CHECK ((CUSTOMEROWNED = 'N') OR (CUSTOMEROWNED = 'Y' AND RELATEDCUSTOMERNUMBER IS NOT NULL));
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT I_ERP_UNIQUEITEM_POOLITEM
  CHECK (POOLITEM IN (0,1,2,3));
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT I_ERP_UNIQUEITEM_SIZECHECK
  CHECK (SIZECODE = UPPER(TRIM(SIZECODE)));
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT CHK_I_ERP_UNIQUEITEM_STAR
  CHECK (SIZECODE <> '*');
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT I_ERP_UNIQUEITEM_TYPEOFMERCHANDISE
  CHECK (TYPEOFMERCHANDISE=2 OR (TYPEOFMERCHANDISE=1 AND POOLITEM=1) OR POOLITEM IN (0, 3));
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT CHK_I_ERP_UNIQUESTOCKCHECK
  CHECK ((STATUS = 10 AND STOCKROOMCODE IS NOT NULL AND ROWCODE IS NOT NULL AND LOCATIONCODE IS NOT NULL) OR STATUS <> 10);
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT CHK_ERP_STARTRENTFROM_VALID
  CHECK (STARTRENTFROM IN (0,2,3,4,5));
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT CHK_ERP_I_UNIQUESTATUS_VALID
  CHECK (((STATUS IN (10,20) AND STARTRENTFROM = 0) OR (STATUS = 30 AND STARTRENTFROM IN (2,3,4,5)) OR (POOLITEM IN (1,3))));
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT CHK_ERP_UNIQUESTAY_VALID
  CHECK (STAY BETWEEN 10 AND 99);
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT CHK_ERP_UNIQUE_CUSTOMOWNED
  CHECK (CUSTOMEROWNED IN ('Y', 'N'));
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT CHK_ERP_UNIQUE_DELIVERYFROM
  CHECK (DELIVERFROMSTOCK IN ('Y', 'N'));
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT CHK_ERP_UNIQUE_FREEEXTRAGARM
  CHECK (FREEEXTRAGARMENTS IN ('Y', 'N'));
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT CHK_I_ERPUNIQUEITEM_FINMETHOD
  CHECK (FINISHINGMETHODCODE=UPPER(TRIM(FINISHINGMETHODCODE)));
ALTER TABLE ERP_I_UNIQUEITEM ADD CONSTRAINT CHK_I_ERPUNIQUEITEM_PROD CHECK (PRODUCTCODE=UPPER(TRIM(PRODUCTCODE)));
ALTER TABLE ERP_I_UNIQUEITEM
  ADD CONSTRAINT CHK_I_ERP_UNIQUEITEM_WEARER
  CHECK ((WEARERNUMBER IS NOT NULL AND WEAREREMPLOYMENTNUMBER IS NOT NULL) OR (WEARERNUMBER IS NULL AND WEAREREMPLOYMENTNUMBER IS NULL));
  
CREATE TABLE ERP_I_UNIQUEITEM_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_I_UNIQUEITEM_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_UNIQUEITEM_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_UNIQUEITEM_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_UNIQUEITEM_LOG
  ADD CONSTRAINT PK_ERP_I_UNIQUEITEM_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_UNIQUEITEM_LOG
  ADD CONSTRAINT FK_ERP_I_UNIQUEITEM_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_UNIQUEITEM(TRANSFER_ID) ON DELETE CASCADE;