--Created by:  Sandy Porter
--Date:  11/12/03
--Purpose:  To update missing uom values in TSRRCI and update incorrect uom values in TSRRSULT.

--In prior versions of SIM, Single Taxon Frequency Class data for Physical and Bio Measures was
--	inserted into the TSRRCI table.  The Physical Measure records correctly records the unit 
--	of measure(uom); however, a NULL value was inserted into the uom column for Bio Measures.
--	This script will update Bio Measure records with a uom_is_number of 72 and a uom_org_id
--	of 'ZZZZZZZZ'.  Bio Measure records will be selected based on prim_class_desc  
--	being NOT NULL.
--	Only records where the D_USERID_CODE like 'SIM' should be modified.

UPDATE tsrrci
SET tsruom_is_number = 72,
    tsruom_org_id = 'ZZZZZZZZ'
WHERE prim_class_desc IS NOT NULL
AND substr(d_userid_code,1,3) = 'SIM'
/

COMMIT
/


--In prior versions of SIM, Single Taxon Frequency Class data for Physical and Bio Measures was
--	inserted into the TSRRSULT table.  For Bio Measure records in the TSRRCI table, the 
--	TSRRSULT.TSMUOM_IS_NUMBER AND TSRRSULT.TSMUOM_ORG_ID should be NULL.  For Physical Measures,
--	the TSRRSULT.TSMUOM_IS_NUMBER AND TSRRSULT.TSMUOM_ORG_ID should be '72' and 'ZZZZZZZZ'.
--	Only records where the D_USERID_CODE like 'SIM' should be modified.
--	Bio Measure records will be selected based on prim_class_desc being NOT NULL.

--For Bio Measures

UPDATE tsrrsult t
SET t.tsruom_is_number = NULL,
    t.tsruom_org_id = NULL
WHERE exists (SELECT 'x'
  FROM tsrrci r
  WHERE r.prim_class_desc IS NOT NULL
  AND t.tsrrsult_is_number = r.tsrrsult_is_number
  AND t.tsrrsult_org_id = r.tsrrsult_org_id
  AND substr(r.d_userid_code,1,3) = 'SIM')
/

COMMIT
/


--For Physical Measures.

UPDATE tsrrsult t
SET t.tsruom_is_number = 72,
    t.tsruom_org_id = 'ZZZZZZZZ'
WHERE exists (SELECT 'x'
  FROM tsrrci r
  WHERE r.prim_class_desc IS NULL
  AND t.tsrrsult_is_number = r.tsrrsult_is_number
  AND t.tsrrsult_org_id = r.tsrrsult_org_id
  AND substr(r.d_userid_code,1,3) = 'SIM')
/

COMMIT
/