--Order 20011112.20 (old order number 502898)
--Modify old tables

------------------------BATCH--------------------

COMMENT ON COLUMN BATCH.ORIGIN IS '1 = Manual created batch maintenance
2 = Manual created soil receiving
3 = Generated at soil receiving
4 = Manual created outscan/sort
5 = Conversion
6 = Generate label
7 = Generated at sort device scan';


------------------------CLIENT--------------------

ALTER TABLE CLIENT
	ADD (SHORTCOUNTCODE VARCHAR2(6) NULL);


COMMENT ON COLUMN CLIENT.SHORTCOUNTCODE IS 'This attribute can be used to specify a short code for counting. Only used for module LAUNDRYTECHNIX.';


CREATE  INDEX XIE6CLIENT ON CLIENT
(CUSTOMER_ID   ASC,SHORTCOUNTCODE   ASC) TABLESPACE INDX;


ALTER TABLE CLIENT ADD CONSTRAINT SHORTCOUNTCODE_UPPERCASE1
	CHECK (SHORTCOUNTCODE = UPPER(SHORTCOUNTCODE));


------------------------CLIENTRESIDENT--------------------


ALTER TABLE CLIENTRESIDENT
	ADD (NUMBEROFLOCATIONS NUMBER(1)  DEFAULT 0 NOT NULL CONSTRAINT NumberOfLocations_valid1 CHECK (NUMBEROFLOCATIONS BETWEEN 0 AND 9));


------------------------PRODUCTLISTPERCUSTOMER--------------------

ALTER TABLE PRODUCTLISTPERCUSTOMER
	ADD (SHORTCOUNTCODE VARCHAR2(6) NULL);

COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.SHORTCOUNTCODE IS 'Only used for module LAUNDRYTECHNIX and for residential products. Can be used for counting.';


CREATE  INDEX XIE24PRODUCTLISTPERCUSTOMER ON PRODUCTLISTPERCUSTOMER
(CUSTOMER_ID   ASC,SHORTCOUNTCODE   ASC) TABLESPACE INDX;


ALTER TABLE PRODUCTLISTPERCUSTOMER ADD CONSTRAINT SHORTCOUNTCODE_UPPERCASE2
	CHECK (SHORTCOUNTCODE = UPPER(SHORTCOUNTCODE));


------------------------DEPARTMENTROOM--------------------

ALTER TABLE DEPARTMENTROOM
	ADD (SORTORDER VARCHAR2(12) NULL);



------------------------CUSTOMERRESIDENTIALSETTINGS--------------------

ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
	ADD(
		SORTGROUP            VARCHAR2(12) NULL ,
		LTXSORTINGMETHOD_ID  NUMBER NULL,
		REJECTONDEVICEFULL   VARCHAR2(1) DEFAULT 'N' NOT NULL  CONSTRAINT  YN_ValidationField37 CHECK 				(REJECTONDEVICEFULL IN ('Y', 'N'))
	    );


CREATE  INDEX XIE1CUSTOMERRESIDENTIALSETTING ON CUSTOMERRESIDENTIALSETTINGS
(SORTGROUP   ASC) TABLESPACE INDX;


CREATE  INDEX XIF1CUSTOMERRESIDENTIALSETTING ON CUSTOMERRESIDENTIALSETTINGS
(LTXSORTINGMETHOD_ID   ASC) TABLESPACE INDX;


ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
ADD CONSTRAINT CUSTRESSETTINGS_LTXSORTINGMETH FOREIGN KEY (LTXSORTINGMETHOD_ID) REFERENCES LTXSORTINGMETHOD (LTXSORTINGMETHOD_ID);

--insert into serialdevice

insert into serialdevice
  (serialdevice_id,
   brandtype,
   name,
   devicetype,
   baudrate,
   parity,
   databits,
   stopbits,
   endcharacter1)
values
  (seq_serialdevice.nextval,
   'Alba',
   'Sorting Cabinet',
   'SRT',
   9600,
   'N',
   8,
   1,
   0);