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


--------------------- line 2 --------------------------

-- Create constraint for PRICETYPE 
ALTER TABLE DISCOUNTLISTPERCUSTOMER
  ADD ( CONSTRAINT PRICETYPE_VALID
  CHECK (PRICETYPE IN (1, 2, 3, 4, 5, 6)));


-- Create constraint for DOCUMENTTYPE 
ALTER TABLE EMAILSETTING
  ADD ( CONSTRAINT DOCUMENTTYPEEMAILSETT_VALID
  CHECK (DOCUMENTTYPE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)));


-- Create constraint for EMBLEMTYPE
ALTER TABLE EMBLEMPRODUCT
  ADD ( CONSTRAINT EMBLEMPRODUCT_VALID
  CHECK (EMBLEMTYPE IN (1, 2, 3)));


-- Create constraint for EMBLEMTYPE
ALTER TABLE EMBLEMTEMPLATELINE
  ADD ( CONSTRAINT EMBLEMTYPEEMBLTEMPLLINE_VALID
  CHECK (EMBLEMTYPE IN (1, 2, 3)));


-- CREATE CONSTRAINT FOR ATTACHMENTBY
ALTER TABLE EMBLEMTEMPLATELINE
  ADD ( CONSTRAINT ATTACHMENTBY_VALID
  CHECK (ATTACHMENTBY IN (0, 1, 2, 3, 4)));


-- DEFAULT FOR DATEINACTIVE
ALTER TABLE EMPLOYEE MODIFY DATEINACTIVE DEFAULT to_date('31-12-2099','dd-mm-yyyy');


ALTER TABLE EMPLOYEEFUNCTIONDIALOG
  DROP CONSTRAINT EMPLOYEEFUNCTIONDIALOG_DIALOG_;

ALTER TABLE EMPLOYEEFUNCTIONDIALOG
  ADD CONSTRAINT EMPLOYEEFUNCTIONDIALOG_DIALOG_ FOREIGN KEY (DIALOG_ID)
  REFERENCES DIALOGS (DIALOG_ID) ON DELETE CASCADE;


ALTER TABLE EMPLOYEETURNOVERGROUP ADD ( 
            CONSTRAINT COMMISSIONPERCENTAGE_VALID 
            CHECK (COMMISSIONPERCENTAGE BETWEEN 0.00 AND 99.99));


-- DEFAULT FOR ACTIVE
ALTER TABLE FINISHINGCODE MODIFY ACTIVE DEFAULT 'Y';


-- CREATE CONSTRAINT FOR WASHTYPE
ALTER TABLE FINISHINGMETHOD
  ADD ( CONSTRAINT WASHTYPE_VALID
  CHECK (WASHTYPE IN (0, 1)));


COMMENT ON COLUMN FLAG.TRANSFER IS '0 = No transfer allowed
1 = Remove flag from item
2 = Move flag with item
3 = Ask user input';


--DEFAULT FOR SHOW, REMOVE, SELECTABLE, CONFIRM, ASKACCEPT FIELDS
ALTER TABLE FLAGOPTIONS MODIFY SHOW DEFAULT 'N';

ALTER TABLE FLAGOPTIONS MODIFY REMOVE DEFAULT 'N';

ALTER TABLE FLAGOPTIONS MODIFY SELECTABLE DEFAULT 'N';

ALTER TABLE FLAGOPTIONS MODIFY CONFIRM DEFAULT 'N';

ALTER TABLE FLAGOPTIONS MODIFY ASKACCEPT DEFAULT 'N'; 



--------------------------------------- line 4 ---------------------------


COMMENT ON COLUMN ACTIVESTOCKDELIVERYDETAIL.DETAILINFO IS '1 = scanned out, 
2 = not found,
3 = replaced,
4 = emergency procedure
5 = Quit mat (scanned out),
6 = Quit mat (not scanned)';


ALTER TABLE ACTIVESTOCKLOCATION MODIFY MULTIPLESTORAGEALLOWED DEFAULT 'N';

ALTER TABLE ACTIVESTOCKLOCATION MODIFY MULTIPLECUSTOMERSALLOWED DEFAULT 'N'; 

ALTER TABLE ACTIVESTOCKLOCATION MODIFY MULTIPLEPRODUCTSIZEALLOWED DEFAULT 'N'; 

ALTER TABLE ACTIVESTOCKLOCATION MODIFY PRIORITY DEFAULT '9999'; 

ALTER TABLE ACTIVESTOCKLOCATION MODIFY ACTIVE DEFAULT 'Y'; 


ALTER TABLE BATCH
  DROP CONSTRAINT BATCH_BULKMASTERBATCH_ID;

ALTER TABLE BATCH
  ADD CONSTRAINT BATCH_BULKMASTERBATCH_ID FOREIGN KEY (BULKMASTERBATCH_ID)
  REFERENCES BATCH (BATCH_ID) ON DELETE CASCADE;


COMMENT ON COLUMN BUSINESSUNIT.CREATECONTACTCOG IS '0 = No contact (default)
1 = Create contact for soil count per distrib. Point and SER
2 = Create contact for inscan
3 = Both (1 + 2)';


COMMENT ON COLUMN CLIENTINVOICECATEGORY.CLIENTINVOICETYPE IS '0 = Client Invoice
1 = Direct invoice / receipt (in case the customer has payment method cash then the direct invoice is a receipt)';



---------------------------- LINE 5 ------------------------

ALTER TABLE CONTACT MODIFY SHOWONROUTELIST DEFAULT 'N';


ALTER TABLE CONTRACT ADD ( 
            CONSTRAINT UPDATEEVERYCONTRACT_VALID 
            CHECK (UPDATEEVERY BETWEEN 0 AND 99));


ALTER TABLE COUNTRYPOSTALINFO MODIFY PCRETURNSCITY DEFAULT 'Y'; 


ALTER TABLE CUSTOMER MODIFY BONUSAGREEMENT DEFAULT 'Y';


ALTER TABLE CUSTOMERACTIVITIES MODIFY FINALINVOICECREATED DEFAULT 'N'; 


ALTER TABLE CUSTOMERCLUSTER MODIFY ACTIVE DEFAULT 'Y';



-------------------------------- LINE 6 ----------------------------------------

COMMENT ON COLUMN CART.STATUS_ID IS 'The following statuses are defined (type CA):
10 = Available
20 = Loaded
30 = Ready for sterilization';


COMMENT ON COLUMN CLIENTDELIVERYNOTE.ORIGIN IS '1 = Manual created,
2 = Soil receiving scan per Wash
3 = Soil receiving scan in Bulk
4 = Outscan/sort
5 = Conversion
6 = Soil receiving – return to owner
7 = Generate label';


-- CREATE CONSTRAINT FOR INSERTSTATUS
ALTER TABLE CUSTOMERINTERFACE
  ADD ( CONSTRAINT INSERTSTATUS_VALID
  CHECK (INSERTSTATUS IN (0, 1)));
  
  
  -- CREATE CONSTRAINT FOR UPDATESTATUS
ALTER TABLE CUSTOMERINTERFACE
  ADD ( CONSTRAINT UPDATESTATUS_VALID
  CHECK (UPDATESTATUS IN (0, 1)));


-- CREATE CONSTRAINT FOR LABELTYPE
ALTER TABLE CUSTOMERLABELLAYOUTPRICE
  ADD ( CONSTRAINT LABELTYPE_VALID
  CHECK (LABELTYPE IN (1, 2, 3)));


-- CREATE CONSTRAINT FOR PRICETYPE
ALTER TABLE CUSTOMERLOCKERPRICE
  ADD ( CONSTRAINT PRICETYPECUSTLOCKERPRICE_VALID
  CHECK (PRICETYPE IN (1, 2)));


-- CREATE CONSTRAINT FOR TYPE
ALTER TABLE CUSTOMERPRODUCTTAXDEF
  ADD ( CONSTRAINT TYPECUSTPRODTAXDEF_VALID
  CHECK (TYPE IN (1, 2, 3, 4, 5)));


-- CREATE CONSTRAINT FOR TYPE
ALTER TABLE CUSTOMERPRODUCTTOGDEF
  ADD ( CONSTRAINT TYPECUSTPRODTOGDEF_VALID
  CHECK (TYPE IN (1, 2, 3, 4, 5)));



-- CREATE CONSTRAINT FOR CUSTOMERCLIENTS
ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
  ADD ( CONSTRAINT CUSTOMERCLIENTS_VALID
  CHECK (CUSTOMERCLIENTS IN (0, 1)));
  
  
  -- CREATE CONSTRAINT FOR INSCANBULKALLOWED
ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
  ADD ( CONSTRAINT INSCANBULKALLOWED_VALID
  CHECK (INSCANBULKALLOWED IN (0, 1, 2)));
  
    
  -- CREATE CONSTRAINT FOR ACCEPTOTHERCLIENTS
ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
  ADD ( CONSTRAINT ACCEPTOTHERCLIENTS_VALID
  CHECK (ACCEPTOTHERCLIENTS IN (0, 1, 2)));
  
    
  -- CREATE CONSTRAINT FOR SORTORDER
ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
  ADD ( CONSTRAINT SORTORDER_VALID
  CHECK (SORTORDER IN (0, 1, 2, 3)));
  
    
  -- CREATE CONSTRAINT FOR PRINTPRICEONDELNOTE
ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
  ADD ( CONSTRAINT PRINTPRICEONDELNOTE_VALID
  CHECK (PRINTPRICEONDELNOTE IN (0, 1, 2, 3)));
  
    
  -- CREATE CONSTRAINT FOR OUTSCANSORT
ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
  ADD ( CONSTRAINT OUTSCANSORT_VALID
  CHECK (OUTSCANSORT IN (0, 1, 2, 3, 4, 5, 6, 7)));