--SO 20013790

------------------------CUSTOMER TABLE-----------------------


ALTER TABLE CUSTOMER
	ADD (GARMENTBATCHSORTING NUMBER  DEFAULT 0 NOT NULL CONSTRAINT GARMENTBATCHSORTING_valid CHECK (GARMENTBATCHSORTING IN (0, 1, 2, 3, 4, 5, 6, 7)));

ALTER TABLE CUSTOMER
	ADD (BOXDELIVERYDOCUMENT VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField_103 CHECK (BOXDELIVERYDOCUMENT IN ('Y', 'N')));

COMMENT ON COLUMN CUSTOMER.GARMENTBATCHSORTING IS '0 = No sorting / customer is not accepted in a batch
1 = Department
2 = Delivery point
3 = Cupboard / Locker
4 = Wearer Number
5 = Product / Size
6 = Product
7 = Size
';


-----------------------WORKSTATION TABLE-----------------

ALTER TABLE WORKSTATION
	ADD (GARMENTBATCHSCAN NUMBER(1)  DEFAULT 0 CONSTRAINT GARMENTBATCHSCAN_valid CHECK (GARMENTBATCHSCAN IN (0, 1, 2)));

COMMENT ON COLUMN WORKSTATION.GARMENTBATCHSCAN IS '0 = Standard scan / Garment Batch scan not allowed
1 = Garment Batch scan
2 = Choose scan mode at startup
';

----------------------GARMENTBATCH TABLE--------------------
CREATE SEQUENCE SEQ_GARMENTBATCH
START WITH 1
INCREMENT BY 1;

CREATE TABLE GARMENTBATCH
(
	GARMENTBATCH_ID      NUMBER NOT NULL ,
	BUSINESSUNIT_ID      NUMBER NOT NULL ,
	YEAR                 NUMBER(4) Default 0 NOT NULL ,
	WEEK                 NUMBER(2) Default 1 NOT NULL  CONSTRAINT  WEEK_valid_1 CHECK (WEEK BETWEEN 0 AND 53),
	GARMENTBATCHNUMBER   NUMBER(6) Default 0 NOT NULL ,
	BATCHSTATUS          NUMBER DEFAULT  0  NOT NULL  CONSTRAINT  BATCHSTATUS_valid CHECK (BATCHSTATUS IN (0, 1, 2, 3, 4)),
	DELIVERYDATE         DATE NULL ,
	SEQUENCENUMBER       NUMBER(4) Default 0 NOT NULL  CONSTRAINT  SEQUENCENUMBER_VALID5 CHECK (SEQUENCENUMBER BETWEEN 0 AND 9999),
	REMARK               VARCHAR2(30) NULL ,
	INFECTION            VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField_104 CHECK (INFECTION IN ('Y', 'N')),
	WORKSTATION_ID       NUMBER NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN GARMENTBATCH.BATCHSTATUS IS '0 = New / scanning in
1 = Closed / Batch Cards printed
2 = Sorted
3 = Scanning out
4 = Completed
';

COMMENT ON COLUMN WORKSTATION.GARMENTBATCHSCAN IS '0 = Standard scan / Garment Batch scan not allowed
1 = Garment Batch scan
2 = Choose scan mode at startup
';


ALTER TABLE GARMENTBATCH
ADD CONSTRAINT  XPKGARMENTBATCH PRIMARY KEY (GARMENTBATCH_ID);

CREATE INDEX XIF1GARMENTBATCH ON GARMENTBATCH
(BUSINESSUNIT_ID   ASC);


ALTER TABLE GARMENTBATCH
ADD CONSTRAINT GARMENTBATCH_BU_ID FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);

CREATE INDEX XIF2GARMENTBATCH ON GARMENTBATCH
(WORKSTATION_ID   ASC);

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

CREATE UNIQUE INDEX XAK1GARMENTBATCH ON GARMENTBATCH
(BUSINESSUNIT_ID   ASC,YEAR   ASC,WEEK   ASC,GARMENTBATCHNUMBER   ASC);


ALTER TABLE GARMENTBATCH
ADD CONSTRAINT  XAK1GARMENTBATCH UNIQUE (BUSINESSUNIT_ID,YEAR,WEEK,GARMENTBATCHNUMBER);

CREATE INDEX XIE1GARMENTBATCH ON GARMENTBATCH
(BUSINESSUNIT_ID   ASC,BATCHSTATUS   ASC);

-------------------------------GARMENTBATCHSORT TABLE---------------------
CREATE SEQUENCE SEQ_GARMENTBATCHSORT
START WITH 1
INCREMENT BY 1;

CREATE TABLE GARMENTBATCHSORT
(
	GARMENTBATCHSORT_ID  NUMBER NOT NULL ,
	GARMENTBATCH_ID      NUMBER NULL ,
	CUSTOMER_LINK_ID     NUMBER NULL ,
	SORTSTRING           VARCHAR2(24) Default '-' NOT NULL ,
	GARMENTBATCHSORTING  NUMBER(1) DEFAULT  0  NOT NULL  CONSTRAINT  GARMENTBATCHSORTING_valid2 CHECK (GARMENTBATCHSORTING IN (0, 1, 2, 3, 4, 5, 6, 7)),
	BOXNUMBER            NUMBER(4) DEFAULT  0  NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN GARMENTBATCHSORT.GARMENTBATCHSORTING IS '1 = Department
2 = Delivery Point
3 = Cupboard / Locker
4 = Wearer Number
5 = Product / Size
6 = Product
7 = Size
';


ALTER TABLE GARMENTBATCHSORT
ADD CONSTRAINT  XPKGARMENTBATCHSORT PRIMARY KEY (GARMENTBATCHSORT_ID);

CREATE INDEX XIF1GARMENTBATCHSORT ON GARMENTBATCHSORT
(GARMENTBATCH_ID   ASC);

ALTER TABLE GARMENTBATCHSORT
ADD CONSTRAINT GARMENTBATCHSORT_GARMENTBATCH FOREIGN KEY (GARMENTBATCH_ID) REFERENCES GARMENTBATCH (GARMENTBATCH_ID) ON DELETE CASCADE;

CREATE INDEX XIF2GARMENTBATCHSORT ON GARMENTBATCHSORT
(CUSTOMER_LINK_ID   ASC);

ALTER TABLE GARMENTBATCHSORT
ADD CONSTRAINT GARMENTBATCHSORT_CUST_LINK FOREIGN KEY (CUSTOMER_LINK_ID) REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);

CREATE UNIQUE INDEX XAK1GARMENTBATCHSORT ON GARMENTBATCHSORT
(GARMENTBATCH_ID   ASC,CUSTOMER_LINK_ID   ASC,SORTSTRING   ASC);


ALTER TABLE GARMENTBATCHSORT
ADD CONSTRAINT  XAK1GARMENTBATCHSORT UNIQUE (GARMENTBATCH_ID,CUSTOMER_LINK_ID,SORTSTRING);


CREATE INDEX XIE1GARMENTBATCHSORT ON GARMENTBATCHSORT
(GARMENTBATCH_ID   ASC,BOXNUMBER   ASC);

-------------------------------GARMENTBATCHSCAN TABLE-----------------------------


CREATE TABLE GARMENTBATCHSCAN
(
	GARMENTBATCH_ID      NUMBER NOT NULL ,
	UNIQUEITEM_ID        NUMBER NOT NULL ,
	SCANTYPE             NUMBER(1)  Default 0 NOT NULL  CONSTRAINT  SCANTYPE_valid CHECK (SCANTYPE IN (0, 1)),
	GARMENTBATCHSORT_ID  NUMBER NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN GARMENTBATCHSCAN.SCANTYPE IS '0 = Inscan
1 = Outscan
';

ALTER TABLE GARMENTBATCHSCAN
ADD CONSTRAINT  XPKGARMENTBATCHSCAN PRIMARY KEY (GARMENTBATCH_ID,UNIQUEITEM_ID,SCANTYPE);

CREATE INDEX XIF1GARMENTBATCHSCAN ON GARMENTBATCHSCAN
(GARMENTBATCH_ID   ASC);

ALTER TABLE GARMENTBATCHSCAN
ADD CONSTRAINT GARMENTBATCHSCAN_GARMENTBATCH FOREIGN KEY (GARMENTBATCH_ID) REFERENCES GARMENTBATCH (GARMENTBATCH_ID) ON DELETE CASCADE;

CREATE INDEX XIF2GARMENTBATCHSCAN ON GARMENTBATCHSCAN
(UNIQUEITEM_ID   ASC);

ALTER TABLE GARMENTBATCHSCAN
ADD CONSTRAINT GARMENTBATCHSCAN_UNIQUEITEM_ID FOREIGN KEY (UNIQUEITEM_ID) REFERENCES UNIQUEITEM (UNIQUEITEM_ID) ON DELETE CASCADE;

CREATE INDEX XIF3GARMENTBATCHSCAN ON GARMENTBATCHSCAN
(GARMENTBATCHSORT_ID   ASC);

ALTER TABLE GARMENTBATCHSCAN
ADD CONSTRAINT GARMENTBATCHSCAN_GARMENTBATCHS FOREIGN KEY (GARMENTBATCHSORT_ID) REFERENCES GARMENTBATCHSORT (GARMENTBATCHSORT_ID) ON DELETE CASCADE;







