--SO20011069.10 clean up database order - OLD ORDER NUMBER IS 501898


--------------------- line 8 --------------------------

ALTER TABLE INVOICEFREQUENCY_DESC MODIFY DESCRIPTION VARCHAR2(40);



ALTER TABLE INVOICEJOBSCHEDULE MODIFY AUTOSCHEDULENEXTRUN VARCHAR2(1);



ALTER TABLE INVOICEJOBBUSINESSUNIT DROP CONSTRAINT INVOICEJOBBUSINESSUNIT_BUSINES;

ALTER TABLE INVOICEJOBBUSINESSUNIT DROP CONSTRAINT INVOICEJOBBUSINESSUNIT_INVOICE;

ALTER TABLE INVOICEJOBBUSINESSUNIT
       ADD   CONSTRAINT INVOICEJOBBUSINESSUNIT_BUSINES
              FOREIGN KEY (BUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT 
                             ON DELETE CASCADE ;

ALTER TABLE INVOICEJOBBUSINESSUNIT
       ADD   CONSTRAINT INVOICEJOBBUSINESSUNIT_INVOICE
              FOREIGN KEY (INVOICEJOB_ID)
                             REFERENCES INVOICEJOB
                             ON DELETE CASCADE  ;



ALTER TABLE INVOICELINESUBTYPE DROP CONSTRAINT INVOICELINESUBTYPE_INVOICELINE;

ALTER TABLE INVOICELINESUBTYPE
       ADD   CONSTRAINT INVOICELINESUBTYPE_INVOICELINE
              FOREIGN KEY (INVOICELINETYPE_ID)
                             REFERENCES INVOICELINETYPE 
                             ON DELETE CASCADE ;



--------------------- line 9 --------------------------

COMMENT ON COLUMN LOOKUPVALUES.LOOKUPTYPE
  IS 'Possible LOOKUPTYPES are: ILLEGALCHARNAME, ILLEGALCHARADDRESS, ILLEGALCHARCITY, ILLEGALCHARCOUNTY';



ALTER TABLE MODIFICATION MODIFY  STOCKWITHSTANDARDSIZE DEFAULT 'N';      

ALTER TABLE MODIFICATION MODIFY  USEINWEBAPPLICATION DEFAULT 'N';



DROP TABLE MSSQL_SEQUENCE;

DROP PROCEDURE FILLMSSQLSEQUENCESTABLE;

DROP PACKAGE MS;



ALTER TABLE NONUNIQUEITEM MODIFY FREEITEM DEFAULT 'N';



--------------------- line 10 --------------------------

ALTER TABLE PACKINGNOTECONTAINER MODIFY TAXGROUPCODE VARCHAR2(12);



ALTER TABLE PICKLISTLINELOCATION
  DROP CONSTRAINT PICKLISTLINELOCATION_PICKLISTL;

ALTER TABLE PICKLISTLINELOCATION
  ADD CONSTRAINT PICKLISTLINELOCATION_PICKLISTL FOREIGN KEY (PICKLISTLINE_ID)
  REFERENCES PICKLISTLINE (PICKLISTLINE_ID)
  ON DELETE CASCADE;



ALTER TABLE PICKLISTLINEORIGIN
  DROP CONSTRAINT PICKLISTLINEORIGIN_PICKLISTLIN;

ALTER TABLE PICKLISTLINEORIGIN
  ADD CONSTRAINT PICKLISTLINEORIGIN_PICKLISTLIN FOREIGN KEY (PICKLISTLINE_ID)
  REFERENCES PICKLISTLINE (PICKLISTLINE_ID)
  ON DELETE CASCADE;



ALTER TABLE PLANTCOMPANY
  DROP CONSTRAINT PLANTCOMPANY_ADDRESS_ID;

ALTER TABLE PLANTCOMPANY
  ADD CONSTRAINT PLANTCOMPANY_ADDRESS_ID FOREIGN KEY (ADDRESS_ID)
  REFERENCES ADDRESS (ADDRESS_ID)
  ON DELETE CASCADE;



COMMENT ON COLUMN PLANTINVOICELINETYPE.SUPPRESSPRINTING IS '0 = Always when Amount is Zero
1 = Only when Quantity is Zero
2 = Never
3 = Default from invoicelinetype (default)';



ALTER TABLE POSTALCODE
  DROP CONSTRAINT POSTALCODE_COUNTRY_ID;

ALTER TABLE POSTALCODE
  ADD CONSTRAINT POSTALCODE_COUNTRY_ID FOREIGN KEY (COUNTRY_ID)
  REFERENCES COUNTRY (COUNTRY_ID)
  ON DELETE CASCADE;



ALTER TABLE POSTALCODEDEFINITION
  DROP CONSTRAINT POSTALCODEDEFINITION_COUNTRY_I;

ALTER TABLE POSTALCODEDEFINITION
  ADD CONSTRAINT POSTALCODEDEFINITION_COUNTRY_I FOREIGN KEY (COUNTRY_ID)
  REFERENCES COUNTRY (COUNTRY_ID)
  ON DELETE CASCADE;


--------------------- line 11 --------------------------

--comented the adding of the constraint because it was added with TD 11903 (route change is shown on the outscan screen), script 8.03.00.51
/*
-- Create constraint for DELAYEDEVENEXCHANGE 
ALTER TABLE PRODUCTLISTPERCUSTOMER
  ADD ( CONSTRAINT DELAYEDEVENEXCHANGE_VALID
  CHECK (DELAYEDEVENEXCHANGE IN (0, 1, 2, 3, 4, 5, 6, 7)));
*/



ALTER TABLE PROOFOFVISITHISTORY
  DROP CONSTRAINT PROOFOFVISITHISTORY_STOP_ID;

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



COMMENT ON COLUMN PURCHASEORDERLINE.PACKING IS '0 = Each (default)
1 = Box
2 = Case
3 = Dozen
Only used in interface POS.';


COMMENT ON COLUMN REASONCODE.INVENTORYTRIGGER IS '0 - None
1 - Direct inventory adjustment
2 - Future inventory adjustment
3 - Temporary inventory adjustment
4 - Flat rate adjustment
5 - Free extra inventory
6 - Replacement
7 - Decrease lost quantity (returned goods)
8 - Kill loss charge 
9 - Automatic sales
10 – Order without inventory adjustment';


COMMENT ON COLUMN ROUTECHANGETRANSACTION.TRANSACTIONNUMBER IS '0 = Dummy (default value)
1 = Add new route
2 = Change depot from route
3 = Add new stop to route
4 = Remove stop from route
5 = Change stop number within route
6 = Move stop (to another route or day)
7 = Change delivery scheme';


COMMENT ON COLUMN ROUTECHANGETRANSACTION.STATUS IS '0 = New (not processed)
1 = Succesfully processed
2 = Error
3 = Not processed because of batch rejected
4 = Cancelled';



--------------------- line 13 --------------------------


ALTER TABLE SOILCOUNTLINE MODIFY UNRELATEDLOAN DEFAULT 'N';


-- Create constraint for EXPECTATIONLEVEL 
ALTER TABLE SORTBATCHCONTENTS
  ADD ( CONSTRAINT EXPECTATIONLEVEL_VALID
  CHECK (EXPECTATIONLEVEL IN (0, 1, 2)));


-- Create constraint for SORTFIELD 
ALTER TABLE SORTINGDEFINITION
  ADD ( CONSTRAINT SORTFIELD_VALID
  CHECK (SORTFIELD BETWEEN 1 AND 13));


-- Create constraint for FREQUENCY 
ALTER TABLE SPECIALCHARGE
  ADD ( CONSTRAINT FREQUENCY_VALID
  CHECK (FREQUENCY BETWEEN 1 AND 10));



--------------------- line 14 --------------------------


ALTER TABLE STANDARDLOCATIONPERPRODUCT MODIFY PRIORITY DEFAULT 0;


ALTER TABLE STANDARDLOCATIONPERPRODUCT MODIFY CUSTOMEROWNED DEFAULT 'N';


ALTER TABLE STANDARDLOCATIONPERPRODUCT MODIFY ALLOWEDGRADE DEFAULT 'B';


ALTER TABLE STANDARDLOCATIONPERPRODUCT MODIFY DEFINEPERSIZE DEFAULT 'N';


ALTER TABLE STANDARDLOCATIONPERPRODUCT MODIFY ACTIVE DEFAULT 'Y';


-- Create constraint for ALLOWEDGRADE 
ALTER TABLE STANDARDLOCATIONPERPRODUCT
  ADD ( CONSTRAINT ALLOWEDGRADE_VALID
  CHECK (ALLOWEDGRADE IN ('N', 'U', 'B', 'D')));


COMMENT ON COLUMN STATUSQUOHEADER.STATUSQUOTYPE IS '1= Items In Circ
2= Sets
3= Wearers
4= Stock Quantities
5 = Lease
6 = stock charge
7 = mats in circulation
8 = on site inventory
9 = costs PBA
10 = compress
11 = clean up
12 = commission
13 = max items in circ
14 = AR Aging';


COMMENT ON COLUMN STATUSQUOHEADER.SUBTYPE IS '0 = Regular (default)
1 = Reference SQ
2 = Both regular and reference SQ';


-- Create constraint for STATUSQUOTYPE 
ALTER TABLE STATUSQUOHEADER
  ADD ( CONSTRAINT STATUSQUOTYPE_VALID
  CHECK (STATUSQUOTYPE BETWEEN 1 AND 14));


-- Add comments to the columns 
COMMENT ON COLUMN STAY.STAYTYPE
  IS 'POSSIBLE STAY TYPES ARE:
1= GARMENT STAYS
2= CONTAINER STAYS 
3= OR-PACK STAYS';



--------------------- line 15 --------------------------


COMMENT ON COLUMN STOCKTRANSFERLINE.UNIQUELYCODED IS 'Possible values:
0 = None
1 = Some
2 = All';


COMMENT ON COLUMN SUPPLIERPRODUCT.PACKING IS '0 = Each (default)
1 = Box
2 = Case
3 = Dozen
Only used in interface POS.';


--------------------- line 16 --------------------------


ALTER TABLE UNIQUEBUNDLE MODIFY CONTENTSTYPE DEFAULT 'U';

-- Create constraint for CONTENTSTYPE 
ALTER TABLE UNIQUEBUNDLE
  ADD ( CONSTRAINT CONTENTSTYPE_VALID
  CHECK (CONTENTSTYPE IN ('U','N')));


-- Add comments to the columns 
COMMENT ON COLUMN UNIQUEIDCONFIGURATION.PACKAGETYPE
  IS '1 = Product
2 = Storage Location
3 = Container
4 = Bundles
5 = Residential/domestic
6 = OR Packs
7 = Carts
8 = Bags';


-- Create constraint for PACKAGETYPE 
ALTER TABLE UNIQUEIDCONFIGURATION
  ADD ( CONSTRAINT PACKAGETYPE_VALID
  CHECK (PACKAGETYPE BETWEEN 1 AND 8));
  
  
    -- Create constraint for TYPE 
ALTER TABLE UNIQUEIDCONFIGURATION
  ADD ( CONSTRAINT IDCODEFORMAT_VALID
  CHECK (IDCODEFORMAT BETWEEN 1 AND 3));


 -- Create constraint for POOLITEM 
ALTER TABLE UNIQUEITEM
  ADD ( CONSTRAINT POOLITEM_VALID
  CHECK (POOLITEM BETWEEN 0 AND 3));
  
  
 -- Create constraint for STARTRENTFROM 
ALTER TABLE UNIQUEITEM
  ADD ( CONSTRAINT STARTRENTFROM_VALID
  CHECK (STARTRENTFROM BETWEEN 0 AND 5));


 -- Create constraint for EMBLEMTYPE 
ALTER TABLE UNIQUEITEMEMBLEM
  ADD ( CONSTRAINT EMBLEMTYPE_VALID
  CHECK (EMBLEMTYPE BETWEEN 1 AND 3));



--------------------- line 17 --------------------------

ALTER TABLE WEIGHINGCATEGORY_DESC MODIFY DESCRIPTION VARCHAR2(40);



--------------------- line 18 --------------------------


COMMENT ON COLUMN PRINTQUEUE.LABELTYPE IS '
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';


ALTER TABLE PRODUCT MODIFY ACTIVE DEFAULT 'Y';


ALTER TABLE PRODUCT MODIFY GARMENTSET DEFAULT 'N';


ALTER TABLE PRODUCT MODIFY MODEL DEFAULT 'N';