--ABS-84144 / ABS-87471

-----------------LABELLAYOUT------------------

UPDATE LABELLAYOUT
SET LABELLAYOUT.LABELTYPE = 0
WHERE LABELLAYOUT.LABELTYPE NOT IN (1,2,3,4,5,6,7,8);
COMMIT;

ALTER TABLE LABELLAYOUT MODIFY (LABELTYPE DEFAULT 0 CONSTRAINT CHK_LABELLAYOUT_LABELTYPE CHECK (LABELTYPE IN (0,1,2,3,4,5,6,7,8)));

-- Add comments to the columns 
COMMENT ON COLUMN LABELLAYOUT.LABELTYPE
  IS '0 = Undefined
1 = Code label
2 = Name label
3 = Client label
4 = OR Assembly Label
5 = OR Delivery Label
6 = OR Production Label
7 = OR Cleanroom Label
8 = Curtain';

------ UNIQUECURTAIN -----------

CREATE SEQUENCE SEQ_UNIQUECURTAIN
START WITH 1
INCREMENT BY 1;

CREATE TABLE UNIQUECURTAIN
(
UNIQUECURTAIN_ID NUMBER NOT NULL,
BUSINESSUNIT_ID NUMBER NOT NULL,
PRIMARYID VARCHAR2(24) NOT NULL,
DISTRIBUTIONPOINT_LINK_ID NUMBER NOT NULL,
DEPARTMENTROOM_ID NUMBER NULL,
WEIGHINGCATEGORY_ID NUMBER NOT NULL,
COLOR_ID NUMBER NULL,
FABRIC_ID NUMBER NULL,
WASHINGPROCESS_ID NUMBER NULL,
STAY_ID NUMBER NOT NULL,
LENGTH NUMBER(5) NOT NULL,
WIDTH NUMBER(5) NOT NULL,
WEIGHT NUMBER(5,2) NOT NULL,
CREATIONTIMESTAMP DATE NOT NULL,
NUMBEROFWASHES NUMBER(4) NOT NULL,
LASTINSCAN DATE NOT NULL,
REMARK VARCHAR2(80) NULL,
NUMBEROFREWASHES NUMBER DEFAULT 0 NOT NULL,
LASTREWASHTIMESTAMP DATE NULL,
NUMBEROFREPAIRS NUMBER DEFAULT 0 NOT NULL,
LASTREPAIRTIMESTAMP DATE NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN UNIQUECURTAIN.UNIQUECURTAIN_ID
IS 'Primary key, generated';

COMMENT ON COLUMN UNIQUECURTAIN.BUSINESSUNIT_ID
IS 'Foreign key to BusinessUnit table';

COMMENT ON COLUMN UNIQUECURTAIN.PRIMARYID
IS 'ID code';

COMMENT ON COLUMN UNIQUECURTAIN.DISTRIBUTIONPOINT_LINK_ID
IS 'Foreign key to DistributionPoint_Link table';

COMMENT ON COLUMN UNIQUECURTAIN.DEPARTMENTROOM_ID
IS 'Foreign key to DepartmentRoom table';

COMMENT ON COLUMN UNIQUECURTAIN.WEIGHINGCATEGORY_ID
IS 'Foreign key to WeighingCategory table';

COMMENT ON COLUMN UNIQUECURTAIN.COLOR_ID
IS 'Foreign key to Color table';

COMMENT ON COLUMN UNIQUECURTAIN.FABRIC_ID
IS 'Foreign key to Fabric table';

COMMENT ON COLUMN UNIQUECURTAIN.WASHINGPROCESS_ID
IS 'Foreign key to WashingProcess table';

COMMENT ON COLUMN UNIQUECURTAIN.STAY_ID
IS 'Foreign key to Stay table';

COMMENT ON COLUMN UNIQUECURTAIN.LENGTH
IS 'Length in Centimeters (no decimals)';

COMMENT ON COLUMN UNIQUECURTAIN.WIDTH
IS 'Width in Centimeters (no decimals)';

COMMENT ON COLUMN UNIQUECURTAIN.WEIGHT
IS 'Weight (2 decimals)';

COMMENT ON COLUMN UNIQUECURTAIN.NUMBEROFWASHES
IS 'No decimals';

COMMENT ON COLUMN UNIQUECURTAIN.REMARK
IS 'Free text field for additional info';

COMMENT ON COLUMN UNIQUECURTAIN.NUMBEROFREWASHES
IS 'Default 0';

COMMENT ON COLUMN UNIQUECURTAIN.NUMBEROFREPAIRS
IS 'Default 0';

COMMENT ON COLUMN UNIQUECURTAIN.SYSTEMUSER_ID
IS 'Not a foreign key';

ALTER TABLE UNIQUECURTAIN ADD CONSTRAINT PKUNIQUECURTAIN PRIMARY KEY(UNIQUECURTAIN_ID);

CREATE UNIQUE INDEX XAK1UNIQUECURTAIN ON UNIQUECURTAIN(BUSINESSUNIT_ID ASC, PRIMARYID ASC);

CREATE INDEX XIF1UNIQUECURTAIN ON UNIQUECURTAIN(BUSINESSUNIT_ID ASC);
CREATE INDEX XIF2UNIQUECURTAIN ON UNIQUECURTAIN(DISTRIBUTIONPOINT_LINK_ID ASC);
CREATE INDEX XIF3UNIQUECURTAIN ON UNIQUECURTAIN(DEPARTMENTROOM_ID ASC);
CREATE INDEX XIF4UNIQUECURTAIN ON UNIQUECURTAIN(WEIGHINGCATEGORY_ID ASC);
CREATE INDEX XIF5UNIQUECURTAIN ON UNIQUECURTAIN(COLOR_ID ASC);
CREATE INDEX XIF6UNIQUECURTAIN ON UNIQUECURTAIN(FABRIC_ID ASC);
CREATE INDEX XIF7UNIQUECURTAIN ON UNIQUECURTAIN(WASHINGPROCESS_ID ASC);
CREATE INDEX XIF8UNIQUECURTAIN ON UNIQUECURTAIN(STAY_ID ASC);

ALTER TABLE UNIQUECURTAIN
  ADD CONSTRAINT FK_UNIQUECURTAIN_BU_ID FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);

ALTER TABLE UNIQUECURTAIN
  ADD CONSTRAINT FK_UNIQUECURTAIN_DP_LINK_ID FOREIGN KEY (DISTRIBUTIONPOINT_LINK_ID)
  REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);
  
ALTER TABLE UNIQUECURTAIN
  ADD CONSTRAINT FK_UNIQUECURTAIN_DEPROOM_ID FOREIGN KEY (DEPARTMENTROOM_ID)
  REFERENCES DEPARTMENTROOM (DEPARTMENTROOM_ID) ON DELETE SET NULL;

ALTER TABLE UNIQUECURTAIN
  ADD CONSTRAINT FK_UNIQUECURTAIN_WEIGHCATEG_ID FOREIGN KEY (WEIGHINGCATEGORY_ID)
  REFERENCES WEIGHINGCATEGORY (WEIGHINGCATEGORY_ID);
  
ALTER TABLE UNIQUECURTAIN
  ADD CONSTRAINT FK_UNIQUECURTAIN_COLOR_ID FOREIGN KEY (COLOR_ID)
  REFERENCES COLOR (COLOR_ID) ON DELETE SET NULL;

ALTER TABLE UNIQUECURTAIN
  ADD CONSTRAINT FK_UNIQUECURTAIN_FABRIC_ID FOREIGN KEY (FABRIC_ID)
  REFERENCES FABRIC (FABRIC_ID) ON DELETE SET NULL;

ALTER TABLE UNIQUECURTAIN
  ADD CONSTRAINT FK_UNIQUECURTAIN_WPROCESS_ID FOREIGN KEY (WASHINGPROCESS_ID)
  REFERENCES WASHINGPROCESS (WASHINGPROCESS_ID) ON DELETE SET NULL;

ALTER TABLE UNIQUECURTAIN
  ADD CONSTRAINT FK_UNIQUECURTAIN_STAY_ID FOREIGN KEY (STAY_ID)
  REFERENCES STAY (STAY_ID);
  
-----------------PRINTQUEUE------------------

UPDATE PRINTQUEUE
SET PRINTQUEUE.LABELTYPE = 0
WHERE PRINTQUEUE.LABELTYPE NOT IN (1,2,3,4,5,6,7,8,9,10,11);
COMMIT;

ALTER TABLE PRINTQUEUE MODIFY(LABELTYPE DEFAULT 0  CONSTRAINT CHK_PRINTQUEUE_LABELTYPE CHECK (LABELTYPE IN (0,1,2,3,4,5,6,7,8,9,10,11)));

-- Add comments to the columns
COMMENT ON COLUMN PRINTQUEUE.LABELTYPE
  IS '0=Undefined / Not used
1=Unique id (fill UniqueItem_Link_ID)
2=Wearer (fill WearerEmployment_Link_ID)
3=Wearer inventory (fill WearerEmpl.+Product+Size)
4=Used
5=Used
6=OR Assembly Label
7=OR Delivery Label
8=Stock Reception Label
9=OR Production Label
10=OR Cleanroom Label
11=Curtain Label';

ALTER TABLE PRINTQUEUE ADD (UNIQUECURTAIN_ID NUMBER  NULL,
                           CURTAINWEIGHT NUMBER NULL,
                           CURTAINWIDTH NUMBER(5) NULL,
                           CURTAINLENGTH NUMBER(5) NULL,
                           CURTAINROOMNUMBER VARCHAR2(12) NULL);

COMMENT ON COLUMN PRINTQUEUE.CURTAINWEIGHT
 IS 'Only filled for LabelType = 11 (Curtain).';

COMMENT ON COLUMN PRINTQUEUE.CURTAINWIDTH
 IS 'Only filled for LabelType = 11 (Curtain). Width in centimeters (no decimals)';

COMMENT ON COLUMN PRINTQUEUE.CURTAINLENGTH
 IS 'Only filled for LabelType = 11 (Curtain). Length in centimeters (no decimals)';

COMMENT ON COLUMN PRINTQUEUE.CURTAINROOMNUMBER
 IS 'Only filled for LabelType = 11 (Curtain)';

CREATE INDEX XIF14PRINTQUEUE ON PRINTQUEUE(UNIQUECURTAIN_ID ASC);
CREATE INDEX XIF15PRINTQUEUE ON PRINTQUEUE(CURTAINWEIGHT ASC);
CREATE INDEX XIF16PRINTQUEUE ON PRINTQUEUE(CURTAINWIDTH ASC);
CREATE INDEX XIF18PRINTQUEUE ON PRINTQUEUE(CURTAINLENGTH ASC);
CREATE INDEX XIF19PRINTQUEUE ON PRINTQUEUE(CURTAINROOMNUMBER ASC);
 
ALTER TABLE PRINTQUEUE
  ADD CONSTRAINT FK_PRINTQUEUE_UNIQUECURTAIN_ID FOREIGN KEY (UNIQUECURTAIN_ID)
  REFERENCES UNIQUECURTAIN (UNIQUECURTAIN_ID) ON DELETE SET NULL;

-----------------UNIQUEIDCONFIGURATION------------------
ALTER TABLE UNIQUEIDCONFIGURATION DROP CONSTRAINT PACKAGETYPE_VALID;

ALTER TABLE UNIQUEIDCONFIGURATION ADD CONSTRAINT PACKAGETYPE_VALID CHECK (PACKAGETYPE IN (1,2,3,4,5,6,7,8,9,10));

COMMENT ON COLUMN UNIQUEIDCONFIGURATION.PACKAGETYPE
 IS '1 = Product
2 = Storage Location
3 = Container
4 = Bundle (only available for module license)
5 = Residential/domestic
6 = OR Packs
7 = Carts
8 = Bags
9 = Packers
10 = Curtains';
-----------------WEIGHINGCATEGPERCUSTOMER------------------
ALTER TABLE WEIGHINGCATEGPERCUSTOMER ADD (USEDFORCURTAINS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_USECURTAINS CHECK (USEDFORCURTAINS IN (0,1)));

COMMENT ON COLUMN WEIGHINGCATEGPERCUSTOMER.USEDFORCURTAINS
 IS '0 = Not used for curtains or not applicable
1 = Used for Curtains';

-----------------PACKINGNOTE------------------

ALTER TABLE PACKINGNOTE DROP CONSTRAINT PNORIGIN_VALID1_1;

ALTER TABLE PACKINGNOTE ADD CONSTRAINT PNORIGIN_VALID1_1 CHECK (ORIGIN BETWEEN 0 AND 17);

COMMENT ON COLUMN PACKINGNOTE.ORIGIN
 IS '0 = Generated based on Order
1 = Manually added
2 = Generated based on Outscan
3 = Quantity Correction Ticket
4 = Stock Delivery Note
5 = Special Order
6 = Bulk Note
7 = Bin Note
8 = Transfer Weights
9 = Import from DW
10 = Mounting Order
11 = Sales Order
12 = Credit Note
13 = Residential Delivery Note
14 = MultiReadShipment
15 = Charge To Product Process
16 = Customer Packing Note
17 = Curtain outscan';
-----------------PACKINGNOTECONTAINER------------------
ALTER TABLE PACKINGNOTECONTAINER ADD (UNIQUECURTAIN_ID NUMBER NULL,
                             CURTAINSCANTIMESTAMP DATE NULL);

CREATE INDEX XIF17PACKINGNOTECONTAINER ON PACKINGNOTECONTAINER(UNIQUECURTAIN_ID ASC);
                             
ALTER TABLE PACKINGNOTECONTAINER
  ADD CONSTRAINT FK_PNCONTAINER_UNQCURTAIN_ID FOREIGN KEY (UNIQUECURTAIN_ID)
  REFERENCES UNIQUECURTAIN (UNIQUECURTAIN_ID) ON DELETE SET NULL;

-----------------STAY------------------

COMMENT ON COLUMN STAY.STAYTYPE
 IS 'Stay type. Possible values:
1 - Garments
2 - Containers
3 - OR packs
4 - Bundles
5 - Residential items
6 - Bag tags
7 - Curtains';