-- ABS-29914

-----------------------WEARERTEMPASSIGNMENT -----------------------
CREATE SEQUENCE SEQ_WEARERTEMPASSIGNMENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE WEARERTEMPASSIGNMENT
(WEARERTEMPASSIGNMENT_ID NUMBER NOT NULL,
  DISTRIBUTIONPOINT_ID NUMBER NOT NULL,
  WEARERINVENTORY_ID NUMBER NOT NULL,
  UNIQUEITEM_LINK_ID NUMBER NOT NULL,
  ISSUETOWEARERDATE DATE NOT NULL,
  RETURNFROMWEARERDATE DATE NULL,
  RFWTRANSACTIONTYPE_ID NUMBER NULL,
  SYSTEMUSER_ID    NUMBER NOT NULL,
  TIMESTAMP        DATE NOT NULL);

ALTER TABLE WEARERTEMPASSIGNMENT
  ADD CONSTRAINT XPKWEARERTEMPASSIGNMENT PRIMARY KEY (WEARERTEMPASSIGNMENT_ID);
  
CREATE INDEX XIE1WEARERTEMPASSIGNMENT  ON                                      
WEARERTEMPASSIGNMENT(DISTRIBUTIONPOINT_ID ASC, WEARERINVENTORY_ID ASC);

CREATE INDEX XIF1WEARERTEMPASSIGNMENT ON WEARERTEMPASSIGNMENT (DISTRIBUTIONPOINT_ID ASC);

CREATE INDEX XIF2WEARERTEMPASSIGNMENT ON WEARERTEMPASSIGNMENT (WEARERINVENTORY_ID ASC);

CREATE INDEX XIF3WEARERTEMPASSIGNMENT ON WEARERTEMPASSIGNMENT (UNIQUEITEM_LINK_ID ASC);

CREATE INDEX XIF4WEARERTEMPASSIGNMENT ON WEARERTEMPASSIGNMENT (RFWTRANSACTIONTYPE_ID ASC);

ALTER TABLE WEARERTEMPASSIGNMENT
  ADD CONSTRAINT WEARERTEMPASSIGNMENT_DISTRIBPT FOREIGN KEY (DISTRIBUTIONPOINT_ID)  
  REFERENCES DISTRIBUTIONPOINT (DISTRIBUTIONPOINT_ID);

ALTER TABLE WEARERTEMPASSIGNMENT
  ADD CONSTRAINT WEARERTEMPASSIGNMENT_WEARERINV FOREIGN KEY (WEARERINVENTORY_ID)  
  REFERENCES WEARERINVENTORY (WEARERINVENTORY_ID);

ALTER TABLE WEARERTEMPASSIGNMENT
  ADD CONSTRAINT WEARERTEMPASSIGN_UNIQUEIT_LK FOREIGN KEY (UNIQUEITEM_LINK_ID)  
  REFERENCES UNIQUEITEM_LINK (UNIQUEITEM_LINK_ID);

ALTER TABLE WEARERTEMPASSIGNMENT
  ADD CONSTRAINT WEARERTEMPASSIGN_TRANSTYP FOREIGN KEY (RFWTRANSACTIONTYPE_ID)  
  REFERENCES TRANSACTIONTYPE (TRANSACTIONTYPE_ID);


-----------------------WEBUSER -----------------------
ALTER TABLE WEBUSER ADD (
ITEMDISPENSEINUSE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD316 CHECK (ITEMDISPENSEINUSE IN ('Y', 'N')),
ITEMDISPENSEINQUIRYONLY VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD317 CHECK (ITEMDISPENSEINQUIRYONLY IN ('Y', 'N')),
ITEMDISPENSEDEFAULTDP_ID NUMBER NULL,
ITEMDISPENSESIZERULE VARCHAR2(1) DEFAULT 'I' NOT NULL CONSTRAINT ITEMDISPENSESIZERULE_valid1_1 CHECK (ITEMDISPENSESIZERULE IN ('I', 'A', 'B')),
ITEMDISPENSEOVERISSUERULE VARCHAR2(1) DEFAULT 'I' NOT NULL CONSTRAINT ITEMDISPENSEISSUERULE_valid1_1 CHECK (ITEMDISPENSEOVERISSUERULE IN ('I', 'A', 'B')),
ITEMDISPENSESOILDSCAN VARCHAR2(1) DEFAULT 'S' NOT NULL CONSTRAINT ITEMDISPENSESOILDSCAN_valid1_1 CHECK (ITEMDISPENSESOILDSCAN IN ('S', 'C')));

COMMENT ON COLUMN WEBUSER.ITEMDISPENSESIZERULE IS 'Allowed values are 
I - Ignore
A - Allow with a warning
B – Block – do now allow
';

COMMENT ON COLUMN WEBUSER.ITEMDISPENSEOVERISSUERULE IS 'Allowed values are 
I - Ignore
A - Allow with a warning
B – Block – do now allow
';

COMMENT ON COLUMN WEBUSER.ITEMDISPENSESOILDSCAN IS 'S = Soiled scan - default value
C = Customer outscan
';

CREATE INDEX XIF3WEBUSER ON WEBUSER (ITEMDISPENSEDEFAULTDP_ID ASC);

ALTER TABLE WEBUSER
  ADD CONSTRAINT WEBUSER_DISTRIBPT FOREIGN KEY (ITEMDISPENSEDEFAULTDP_ID)  
  REFERENCES DISTRIBUTIONPOINT (DISTRIBUTIONPOINT_ID);