--Order 20013402.70

---BAGTAG---

CREATE SEQUENCE SEQ_BAGTAG
START WITH 1
INCREMENT BY 1;

CREATE TABLE BAGTAG
(
	BAGTAG_ID            NUMBER NOT NULL ,
	PRIMARYID            VARCHAR2(24) NOT NULL ,
	DISTRIBUTIONPOINT_ID NUMBER NOT NULL ,
	PACKINGNOTE_ID       NUMBER NULL ,
	STAY_ID              NUMBER NOT NULL ,
	STATUS               NUMBER NOT NULL  CONSTRAINT  Status_valid3_2 CHECK (STATUS IN (0, 1)),
	UNEXPECTED           VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField_86 CHECK (UNEXPECTED IN ('Y', 'N')),
	DELIVERYDATE         DATE NULL ,
	UNLOADEDDATE         DATE NULL ,
	UNLOADSYSTEMUSER_ID  NUMBER NULL ,
	SORTEDDATE           DATE NULL ,
	SORTSYSTEMUSER_ID    NUMBER NULL ,
	PICKUPDATE           DATE NULL ,
	PICKUPROUTE_ID       NUMBER NULL ,
	PICKUPSTOP_ID        NUMBER NULL ,
	PLANNEDDELIVERYDATE  DATE NULL ,
	ROUTE_ID             NUMBER NULL ,
	STOP_ID              NUMBER NULL ,
	CONTAINER_ID         NUMBER NULL ,
	PRIORITY             NUMBER NOT NULL  CONSTRAINT  Priority_valid1_1 CHECK (PRIORITY IN (0, 1, 2, 3, 4)),
	LASTSCANTIMESTAMP    DATE NULL ,
	WORKSTATION_ID       NUMBER NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN BAGTAG.PRIMARYID IS 'Barcode, uniquely indexed.
Note: bagtags are overall unique, not per business unit.';

COMMENT ON COLUMN BAGTAG.STATUS IS '0 = Attached to bag
1 = Detached, cycle completed';

COMMENT ON COLUMN BAGTAG.PRIORITY IS 'Priority defined on customer, copied at unloading
0 = Not applicable
1 = 5 deliveries per week
2 = 6 deliveries per week
3 = 2 – 4 deliveries per week
4 = 1 delivery per week
';

CREATE UNIQUE INDEX XAK1BAGTAG ON BAGTAG
(PRIMARYID   ASC);

CREATE  INDEX XIF1BAGTAG ON BAGTAG
(DISTRIBUTIONPOINT_ID   ASC);

CREATE  INDEX XIF2BAGTAG ON BAGTAG
(PACKINGNOTE_ID   ASC);

CREATE  INDEX XIF3BAGTAG ON BAGTAG
(STAY_ID   ASC);

CREATE  INDEX XIF4BAGTAG ON BAGTAG
(UNLOADSYSTEMUSER_ID   ASC);

CREATE  INDEX XIF5BAGTAG ON BAGTAG
(SORTSYSTEMUSER_ID   ASC);

CREATE  INDEX XIF6BAGTAG ON BAGTAG
(PICKUPROUTE_ID   ASC);

CREATE  INDEX XIF7BAGTAG ON BAGTAG
(PICKUPSTOP_ID   ASC);

CREATE  INDEX XIF8BAGTAG ON BAGTAG
(ROUTE_ID   ASC);

CREATE  INDEX XIF9BAGTAG ON BAGTAG
(STOP_ID   ASC);

CREATE  INDEX XIF10BAGTAG ON BAGTAG
(CONTAINER_ID   ASC);

CREATE  INDEX XIF11BAGTAG ON BAGTAG
(WORKSTATION_ID   ASC);

ALTER TABLE BAGTAG
ADD CONSTRAINT  XPKBAGTAG PRIMARY KEY (BAGTAG_ID);

ALTER TABLE BAGTAG
ADD CONSTRAINT BAGTAG_DISTRIBUTIONPOINT FOREIGN KEY (DISTRIBUTIONPOINT_ID) REFERENCES DISTRIBUTIONPOINT (DISTRIBUTIONPOINT_ID);

ALTER TABLE BAGTAG
ADD CONSTRAINT BAGTAG_PACKINGNOTE FOREIGN KEY (PACKINGNOTE_ID) REFERENCES PACKINGNOTE (PACKINGNOTE_ID) ON DELETE CASCADE;

ALTER TABLE BAGTAG
ADD CONSTRAINT BAGTAG_STAY FOREIGN KEY (STAY_ID) REFERENCES STAY (STAY_ID);

ALTER TABLE BAGTAG
ADD CONSTRAINT BAGTAG_UNLOADSYSTEMUSER FOREIGN KEY (UNLOADSYSTEMUSER_ID) REFERENCES SYSTEMUSER (SYSTEMUSER_ID) ON DELETE SET NULL;

ALTER TABLE BAGTAG
ADD CONSTRAINT BAGTAG_SORTSYSTEMUSER FOREIGN KEY (SORTSYSTEMUSER_ID) REFERENCES SYSTEMUSER (SYSTEMUSER_ID) ON DELETE SET NULL;

ALTER TABLE BAGTAG
ADD CONSTRAINT BAGTAG_PICKUPROUTE FOREIGN KEY (PICKUPROUTE_ID) REFERENCES ROUTE (ROUTE_ID) ON DELETE SET NULL;

ALTER TABLE BAGTAG
ADD CONSTRAINT BAGTAG_PICKUPSTOP FOREIGN KEY (PICKUPSTOP_ID) REFERENCES STOP (STOP_ID) ON DELETE SET NULL;

ALTER TABLE BAGTAG
ADD CONSTRAINT BAGTAG_ROUTE FOREIGN KEY (ROUTE_ID) REFERENCES ROUTE (ROUTE_ID) ON DELETE SET NULL;

ALTER TABLE BAGTAG
ADD CONSTRAINT BAGTAG_STOP FOREIGN KEY (STOP_ID) REFERENCES STOP (STOP_ID) ON DELETE SET NULL;

ALTER TABLE BAGTAG
ADD CONSTRAINT BAGTAG_CONTAINER FOREIGN KEY (CONTAINER_ID) REFERENCES CONTAINER (CONTAINER_ID) ON DELETE SET NULL;

ALTER TABLE BAGTAG
ADD CONSTRAINT BAGTAG_WORKSTATION FOREIGN KEY (WORKSTATION_ID) REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE SET NULL;


---STAY---
COMMENT ON COLUMN STAY.STAYTYPE IS 'Possible stay types are:
1 = Garment stays
2 = OR-pack stays
3 = Container stays
6 = Bag tags
Note that the staytypes match with the flagtypes.';

---ROUTE---
ALTER TABLE ROUTE
	ADD (COLOR VARCHAR2(6) NULL);

ALTER TABLE ROUTE
	ADD (TEXTCOLOR NUMBER  DEFAULT 0 NOT NULL CONSTRAINT TextColor_valid1_1 CHECK (TEXTCOLOR IN (0, 1)));

COMMENT ON COLUMN ROUTE.COLOR IS 'Batch color, stored as RGB Hexadecimal value';

COMMENT ON COLUMN ROUTE.TEXTCOLOR IS '0 = Black
1 = white
';

---STOP---
ALTER TABLE STOP
	ADD (UNLOADINGSTATUS NUMBER  DEFAULT 1 NOT NULL CONSTRAINT UnloadingStatus_valid1_1 CHECK (UNLOADINGSTATUS IN (1, 2, 3)));

ALTER TABLE STOP
	ADD (SORTINGSTATUS NUMBER  DEFAULT 1 NOT NULL CONSTRAINT SortingStatus_valid1_1 CHECK (SORTINGSTATUS IN (1, 2, 3)));


COMMENT ON COLUMN STOP.UNLOADINGSTATUS IS '1 = Not started
2 = In progress
3 = Completed
';

COMMENT ON COLUMN STOP.SORTINGSTATUS IS '1 = Not started
2 = In progress
3 = Completed
';



---insert into stay table---

insert into stay
          (stay_id,
           staytype,
           staynr,
           factorydescription,
           systemuser_id,
           timestamp)
        values
          (seq_stay.nextval,
           6,
           10,
           'New (not scanned)',
           getminsystemuser(),
           sysdate);

insert into stay
          (stay_id,
           staytype,
           staynr,
           factorydescription,
           systemuser_id,
           timestamp)
        values
          (seq_stay.nextval,
           6,
           20,
           'Scanned at conveyor belt',
           getminsystemuser(),
           sysdate);

insert into stay
          (stay_id,
           staytype,
           staynr,
           factorydescription,
           systemuser_id,
           timestamp)
        values
          (seq_stay.nextval,
           6,
           30,
           'Received at shipping',
           getminsystemuser(),
           sysdate);

insert into stay
          (stay_id,
           staytype,
           staynr,
           factorydescription,
           systemuser_id,
           timestamp)
        values
          (seq_stay.nextval,
           6,
           40,
           'Delivered at customer',
           getminsystemuser(),
           sysdate);

insert into stay
          (stay_id,
           staytype,
           staynr,
           factorydescription,
           systemuser_id,
           timestamp)
        values
          (seq_stay.nextval,
           6,
           50,
           'Picked up at customer',
           getminsystemuser(),
           sysdate);

insert into stay
          (stay_id,
           staytype,
           staynr,
           factorydescription,
           systemuser_id,
           timestamp)
        values
          (seq_stay.nextval,
           6,
           60,
           'Unloaded from truck',
           getminsystemuser(),
           sysdate);

insert into stay
          (stay_id,
           staytype,
           staynr,
           factorydescription,
           systemuser_id,
           timestamp)
        values
          (seq_stay.nextval,
           6,
           70,
           'Soil sorted',
           getminsystemuser(),
           sysdate);


commit;

insert into stay_desc
  (stay_id, language_id, description, systemuser_id, timestamp)
  select s.stay_id, l.language_id, s.factorydescription, getminsystemuser(), s.timestamp
    from stay s,languages l
   where s.staytype = 6
     and s.staynr in (10, 20, 30, 40, 50, 60, 70);

commit;

--insert into purgetable

insert into purgetable
  (purgetable_id,
   tablename,
   description,
   remark,
   whereclause,
   datefieldname,
   systemuser_id,
   timestamp)
  select seq_purgetable.nextval,
         'Bagtag',
         'Bag tags',
         'Only detached bag tags are purged.',
         'Status = 1',
         'Lastscantimestamp',
         getminsystemuser(),
         sysdate
    from dual;

commit;