-- ABS-58755/ ABS-61262

--- TRANSACTIONTYPE table ---
INSERT INTO TRANSACTIONTYPE
  (TRANSACTIONTYPE_ID, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
VALUES(57,
         'Send to Sub Contractor',
         GETMINSYSTEMUSER(),
         SYSDATE);
COMMIT;

INSERT INTO TRANSACTIONTYPE
  (TRANSACTIONTYPE_ID, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
VALUES(58,
         'Received from Sub Contractor',
         GETMINSYSTEMUSER(),
         SYSDATE);
COMMIT;

INSERT INTO TRANSACTIONTYPE_DESC
  (TRANSACTIONTYPE_ID, LANGUAGE_ID, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
  SELECT TT.TRANSACTIONTYPE_ID,
         GETMINLANGUAGE(),
         TT.FACTORYDESCRIPTION,
         GETMINSYSTEMUSER(),
         SYSDATE
    FROM TRANSACTIONTYPE TT
    WHERE TT.TRANSACTIONTYPE_ID in (57, 58);
COMMIT;

--- STAY table ---
INSERT INTO STAY
  (STAY_ID, STAYTYPE, STAYNR, SYSTEMUSER_ID, TIMESTAMP, FACTORYDESCRIPTION)
  VALUES
  (SEQ_STAY.NEXTVAL, 5, 15,  GETMINSYSTEMUSER(), SYSDATE, 'Inscan/Returned from Sub Contractor');
COMMIT;

INSERT INTO STAY
  (STAY_ID, STAYTYPE, STAYNR, SYSTEMUSER_ID, TIMESTAMP, FACTORYDESCRIPTION)
  VALUES
  (SEQ_STAY.NEXTVAL, 5, 25,  GETMINSYSTEMUSER(), SYSDATE, '(Send to) Sub Contractor');
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 L.TRANSLATEDESCRIPTION = 'Y'
AND S.STAYTYPE = 5 AND S.STAYNR in (15, 25);
COMMIT;

--- SPECIALSCANS table ---
insert into specialscans sp
  (specialscans_id, transactiontype_id, stay_id, systemuser_id, timestamp)
  select 6,
         57,
         (select s.stay_id
            from stay s
           where s.staytype = 5
             and s.staynr = 25),
         0,
         sysdate
    from dual;
commit;

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