-- ABS-20373/ ABS-26886

---AUTOMATICFLAG table---
insert into automaticflag
          (automaticflag_id,
           event,
           systemuser_id,
           timestamp,
           selectable,
           deletable,
           transferable,
           flagtype)
      select 26,
           'Returned',
           getminsystemuser(),
           getcurrentdatetime(),
           'N',
           'N',
           'Y',
            1 from dual;
commit;

---STAY table---
insert into stay
  (stay_id, staytype, staynr, factorydescription, systemuser_id, timestamp)
  select seq_stay.nextval,
         1,
         29,
         'Returned / In Transit',
         getminsystemuser(),
         getcurrentdatetime()
    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(),
         getcurrentdatetime()
    from stay s, languages l
    where s.staytype = 1 and s.staynr = 29;
commit;

---STATUS table---
insert into status
          (status_id,
           statustype,
           code,
           legacystatuscode,
           systemuser_id,
           timestamp,
           factorydescription)
        values
          (seq_status.nextval,
           'UI',
           '70',
           '',
           getminsystemuser(),
           getcurrentdatetime(),
           'Returned by customer');
commit;

insert into status_desc
  (status_id,
   language_id,
   description, 
   systemuser_id, 
   timestamp)
  select status_id,
         getminlanguage(),
         factorydescription,
         systemuser_id,
         timestamp
    from status
   where status.statustype = 'UI'
     and status.code = '70';
commit;


---REASONCODE table---
ALTER TABLE REASONCODE ADD (ADJUSTLOSS VARCHAR2(1) DEFAULT 'N' NOT NULL 
					CONSTRAINT YN_VALIDATIONFIELD282 CHECK (ADJUSTLOSS IN ('Y','N')),
			    ADJUSTRETURNED VARCHAR2(1) DEFAULT 'N' NOT NULL
					CONSTRAINT YN_VALIDATIONFIELD283 CHECK (ADJUSTRETURNED IN ('Y','N'))
			   );
 
COMMENT ON COLUMN REASONCODE.ADJUSTLOSS
  IS 'Can only be set to Y if InvoiceAdjustment = 2. There can only be 1 reason code marked as ‘Y’.';
COMMENT ON COLUMN REASONCODE.ADJUSTRETURNED
  IS 'Can only be set to Y if InvoiceAdjustment = 2. There can only be 1 reason code marked as ‘Y’.';

---TRANSACTIONTYPE table---

ALTER TABLE TRANSACTIONTYPE MODIFY (FACTORYDESCRIPTION VARCHAR2(80));

ALTER TABLE TRANSACTIONTYPE_DESC MODIFY (DESCRIPTION VARCHAR2(80));

insert into transactiontype (transactiontype_id, factorydescription, systemuser_id, timestamp, legacytransactioncode)
values (18,'Return to stock (Returned by customer)',getminsystemuser(),sysdate,null);
commit;

insert into transactiontype (transactiontype_id, factorydescription, systemuser_id, timestamp, legacytransactioncode)
values (47,'Ragged while in circ., returned from customer',getminsystemuser(),sysdate,null);
commit;

insert into transactiontype (transactiontype_id, factorydescription, systemuser_id, timestamp, legacytransactioncode)
values (48,'Ragged while in circ., returned from customer via Rag in Batch',getminsystemuser(),sysdate,null);
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 (18, 47, 48);
commit;
