--Order 20011868.70 + 20011944.60

------------------------SORTINGDEFINITION TABLE----------------------------
BEGIN
EXECUTE IMMEDIATE ('ALTER TABLE SORTINGDEFINITION DROP CONSTRAINT SORTFIELD_valid');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

ALTER TABLE SORTINGDEFINITION
  ADD CONSTRAINT SORTFIELD_valid  CHECK (SORTFIELD BETWEEN 1 AND 16);

COMMENT ON COLUMN SORTINGDEFINITION.SORTFIELD IS '1=Department Number 
2=Wearer Number 
3=Wearer Name 
4=Customer Employee Number 
5=Bank/Locker 
6=Gender 
7=Product Code 
8=Size Code 
9=Delivery Point 
10=Garment Dispenser Code 
11=Customer/Laundry Owned 
12=Ordernumber 
13=Bank
14 = ProductSortGroup
15 = LockerSortGroup
16 = DistributionPointNumber
';
--------------------------------LOCKERSORTGROUP TABLE------------------------------


CREATE SEQUENCE SEQ_LOCKERSORTGROUP
START WITH 1
INCREMENT BY 1;

CREATE TABLE LOCKERSORTGROUP
(
  LOCKERSORTGROUP_ID   NUMBER NOT NULL ,
  CUSTOMER_ID          NUMBER NOT NULL ,
  LOCKERSORTGROUPNUMBER NUMBER(3) NOT NULL  CONSTRAINT  LOCKERSORTGROUPNUMB_ CHECK (LOCKERSORTGROUPNUMBER BETWEEN 1 AND 999),
  DESCRIPTION          VARCHAR2(30) NOT NULL ,
  ACTIVE               VARCHAR2(1) DEFAULT  'Y'  NOT NULL  CONSTRAINT  ACTIVE_valid CHECK (ACTIVE IN ('Y', 'N')),
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
);


ALTER TABLE LOCKERSORTGROUP
ADD CONSTRAINT  XPKLOCKERSORTGROUP PRIMARY KEY (LOCKERSORTGROUP_ID);


CREATE  INDEX XIF1LOCKERSORTGROUP ON LOCKERSORTGROUP
(CUSTOMER_ID   ASC);

ALTER TABLE LOCKERSORTGROUP
ADD CONSTRAINT LOCKERSORTGROUP_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;

CREATE UNIQUE INDEX XAK1LOCKERSORTGROUP ON LOCKERSORTGROUP
(CUSTOMER_ID   ASC,LOCKERSORTGROUPNUMBER   ASC);


--------------------------------LOCKER TABLE------------------------------


ALTER TABLE LOCKER
  ADD (LOCKERSORTGROUP_ID NUMBER);

CREATE  INDEX XIF3LOCKER ON LOCKER
(LOCKERSORTGROUP_ID   ASC);

ALTER TABLE LOCKER
ADD CONSTRAINT LOCKER_LOCKERSORTGROUP FOREIGN KEY (LOCKERSORTGROUP_ID) REFERENCES LOCKERSORTGROUP (LOCKERSORTGROUP_ID);

CREATE  INDEX XIE1LOCKER ON LOCKER
(CUSTOMER_ID   ASC,BANK   ASC,LOCKERSORTGROUP_ID   ASC);









