--Order 20013729.80

----STAY table----

insert into stay
  (stay_id, staytype, staynr, factorydescription, systemuser_id, timestamp)
  select seq_stay.nextval,
         5,
         55,
         'Infectious Wash',
         getminsystemuser(),
         sysdate
    from dual;
commit;

insert into stay
  (stay_id, staytype, staynr, factorydescription, systemuser_id, timestamp)
  select seq_stay.nextval,
         5,
         65,
         'Wrongly received',
         getminsystemuser(),
         sysdate
    from dual;
commit;

insert into stay_desc
	(stay_id, language_id, description, systemuser_id, timestamp) 
  select s.stay_id,
         l.language_id,
         s.factorydescription,
         getminsystemuser(),
         sysdate
    from stay s, languages l
    where s.staytype = 5 and s.staynr in (55, 65);
commit;

----TRANSACTIONTYPE table----

insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 66,
         'Infectious scan',
         getminsystemuser(),
         sysdate
    from dual;
commit;
    
insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 67,
         'Wrong received scan',
         getminsystemuser(),
         sysdate
    from dual;
commit;

insert into transactiontype_desc
  (transactiontype_id, language_id, description, systemuser_id, timestamp)
  select transactiontype_id,
         getminlanguage(),
         factorydescription,
         systemuser_id,
         timestamp
    from transactiontype
   where transactiontype.transactiontype_id in (66, 67) ;
commit;


----SPECIALSCANS table----

CREATE SEQUENCE SEQ_SPECIALSCANS
START WITH 1
INCREMENT BY 1;

CREATE TABLE SPECIALSCANS
(
  SPECIALSCANS_ID      NUMBER NOT NULL ,
  TRANSACTIONTYPE_ID   NUMBER NOT NULL ,
  STAY_ID              NUMBER NOT NULL ,
  SYSTEMUSER_ID        NUMBER NOT NULL,
  TIMESTAMP            DATE NOT NULL
);

ALTER TABLE SPECIALSCANS
ADD CONSTRAINT  XPKSPECIALSCANS PRIMARY KEY (SPECIALSCANS_ID);

CREATE  INDEX XIF1SPECIALSCANS ON SPECIALSCANS
(TRANSACTIONTYPE_ID   ASC);

CREATE  INDEX XIF2SPECIALSCANS ON SPECIALSCANS
(STAY_ID   ASC);

ALTER TABLE SPECIALSCANS
ADD CONSTRAINT SPECIALSCANS_TRANSACTIONTYPE FOREIGN KEY (TRANSACTIONTYPE_ID) REFERENCES TRANSACTIONTYPE (TRANSACTIONTYPE_ID) ON DELETE CASCADE;

ALTER TABLE SPECIALSCANS
ADD CONSTRAINT SPECIALSCANS_STAY FOREIGN KEY (STAY_ID) REFERENCES STAY (STAY_ID) ON DELETE SET NULL;

insert into specialscans sp
  (specialscans_id, transactiontype_id, stay_id, systemuser_id, timestamp)
  select 1,
         6,
         (select s.stay_id
            from stay s
           where s.staytype = 5
             and s.staynr = 30),
         0,
         sysdate
    from dual;
commit;
    
insert into specialscans sp
  (specialscans_id, transactiontype_id, stay_id, systemuser_id, timestamp)
  select 2,
         5,
         (select s.stay_id
            from stay s
           where s.staytype = 5
             and s.staynr = 60),
         0,
         sysdate
    from dual;
commit;
    
insert into specialscans sp
  (specialscans_id, transactiontype_id, stay_id, systemuser_id, timestamp)
  select 3,
         4,
         (select s.stay_id
            from stay s
           where s.staytype = 5
             and s.staynr = 50),
         0,
         sysdate
    from dual;
commit;
    
insert into specialscans sp
  (specialscans_id, transactiontype_id, stay_id, systemuser_id, timestamp)
  select 4,
         66,
         (select s.stay_id
            from stay s
           where s.staytype = 5
             and s.staynr = 55),
         0,
         sysdate
    from dual;
commit;

insert into specialscans sp
  (specialscans_id, transactiontype_id, stay_id, systemuser_id, timestamp)
  select 5,
         67,
         (select s.stay_id
            from stay s
           where s.staytype = 5
             and s.staynr = 65),
         0,
         sysdate
    from dual;
commit;

----WORKSTATION table----

ALTER TABLE WORKSTATION ADD (LASTSPECIALSCANS_ID NUMBER NULL);
ALTER TABLE WORKSTATION ADD (SPECIALSCANSTARTUP NUMBER DEFAULT 0 NOT NULL CONSTRAINT SPECIALSCANSTARTUP_VALID1
  CHECK (SPECIALSCANSTARTUP IN (0, 1)));
 
COMMENT ON COLUMN WORKSTATION.SPECIALSCANSTARTUP
  IS '0 = Force scan mode selection during start up
1 = Remember last used scan mode
';

CREATE INDEX XIF7WORKSTATION ON WORKSTATION (LASTSPECIALSCANS_ID);
 
ALTER TABLE WORKSTATION
  ADD CONSTRAINT WORKSTATION_LASTSPECIALSCANS FOREIGN KEY (LASTSPECIALSCANS_ID)
  REFERENCES SPECIALSCANS (SPECIALSCANS_ID) ON DELETE SET NULL;

----WORKSTATIONSPECIALSCANS table----

CREATE TABLE WORKSTATIONSPECIALSCANS
(
  WORKSTATION_ID      NUMBER NOT NULL ,
  SPECIALSCANS_ID     NUMBER NOT NULL ,
  SYSTEMUSER_ID       NUMBER NOT NULL,
  TIMESTAMP           DATE NOT NULL
);

ALTER TABLE WORKSTATIONSPECIALSCANS
ADD CONSTRAINT  XPKWORKSTATIONSPECIALSCANS PRIMARY KEY (WORKSTATION_ID, SPECIALSCANS_ID);

CREATE  INDEX XIF1WORKSTATIONSPECIALSCANS ON WORKSTATIONSPECIALSCANS
(WORKSTATION_ID   ASC);

CREATE  INDEX XIF2WORKSTATIONSPECIALSCANS ON WORKSTATIONSPECIALSCANS
(SPECIALSCANS_ID   ASC);

ALTER TABLE WORKSTATIONSPECIALSCANS
ADD CONSTRAINT WORKSTATIONSPECIALSCANS_WS FOREIGN KEY (WORKSTATION_ID) REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE CASCADE;

ALTER TABLE WORKSTATIONSPECIALSCANS
ADD CONSTRAINT WORKSTATIONSPECIALSCANS_SS FOREIGN KEY (SPECIALSCANS_ID) REFERENCES SPECIALSCANS (SPECIALSCANS_ID) ON DELETE CASCADE;

