Here is a list of general-use Workview queries. The class ID is found by selecting the class in Studio and looking in the upper right corner of the Properties pane. Attribute numbers are in the attribute tab of the same pane. Columns with ATTR prefix are native to that class, and columns with FK prefix are foreign keys, i.e. they came from another class via a LinkTo.
-- WV Classes by Application
SELECT rmapp.RMAPPLICATIONID
, trim(rmapp.rmapplicationname)
, rmc.classid
, trim(rmc.classname)
FROM hsi.rmclass rmc
JOIN hsi.rmapplicationclasses rmac ON rmac.classid = rmc.classid
JOIN hsi.rmapplication rmapp ON rmapp.rmapplicationid = rmac.rmapplicationid
order by 2, 3
;
-- WV Attributes by Class
SELECT
rmapp.rmapplicationid AppID
, trim(rmapp.rmapplicationname) AppName
, rmc.classid ClassID
, trim(rmc.classname) ClassName
, rmatt.attributeID AttributeID
, trim(rmatt.AttributeName) AttributeName
, CASE rmatt.datatype
WHEN 1 THEN 'Integer'
WHEN 2 THEN 'Currency'
WHEN 3 THEN 'Floating Point'
WHEN 4 THEN 'Date'
WHEN 5 THEN 'Date/Time'
WHEN 6 THEN 'Alphanumeric'
WHEN 7 THEN 'Text'
WHEN 8 THEN 'Relationship'
else 'Other' end DataType
FROM hsi.rmclass rmc
JOIN hsi.rmapplicationclasses rmac ON rmac.classid = rmc.classid
JOIN hsi.rmapplication rmapp ON rmapp.rmapplicationid = rmac.rmapplicationid
JOIN hsi.rmclassattributes rmca ON rmca.classid = rmc.classid
JOIN hsi.rmattribute rmatt ON rmatt.attributeid = rmca.attributeid
ORDER BY 2, 4, 6
;
-- basic info about a workview object (name, ID, createdate)
SELECT --rmobjectid, rmobjectname
* FROM hsi.rmobject WHERE objectid = '2918766' -- analogous to the itemdata table
;
-- find all attributes (analogous to keywords) on a workview object
SELECT * FROM hsi.rmobjectinstance1047 -- enter the classID - analogous to the hsi.keyitem### tables
WHERE objectid = '2918766'
;
--WFLOG of WV objects with attribute values (example)
SELECT
wf.itemnum AS DocHandle,
rm1050.attr1670 AS "MIHP ID",
trim(rm1050.attr1671) AS "First Name",
trim(rm1050.attr1672) AS "Last Name",
rm1050.attr1673 AS "Medicaid ID",
rm1046.attr1485 AS "Provider ID",
trim(rm1046.attr1486) as "Provider Name",
rm1050.attr1510 AS "Prov Doc Uploaded",
rm1050.attr1511 AS "Referral Type",
trim(rm.rmobjectname) AS "WV Item Name",
wf.lcnum AS LCNUM,
trim(lc.lifecyclename) AS LCName,
wf.statenum AS QNum,
trim(lcs.statename) AS QName,
to_CHAR(wf.entrytime, 'MM-DD-YYYY HH:MI:SS') AS EntryTime,
to_CHAR(wf.exittime, 'MM-DD-YYYY HH:MI:SS') AS ExitTime,
wf.statenumto AS StateNumTo,
trim(ua.username) AS UserName
FROM hsi.wflog wf
left outer join hsi.rmobject rm on rm.objectid = wf.itemnum
LEFT OUTER JOIN hsi.lcstate lcs ON lcs.statenum = wf.statenum
LEFT OUTER JOIN hsi.lifecycle lc ON lc.lcnum = lcs.SCOPE
LEFT OUTER JOIN hsi.useraccount ua ON ua.usernum = wf.usernum
LEFT OUTER JOIN hsi.rmobjectinstance1050 rm1050 ON rm1050.objectid = wf.itemnum --rmobjectinstance1050 is the memberenrollment class ID
LEFT OUTER JOIN HSI.rmobjectinstance1046 rm1046 on rm1046.objectid = rm1050.fk1509
WHERE wf.wfcontenttype = 3 -- type denotes WV objects in WF
AND wf.contentclassnum = 1050 -- Class # in studio for MIHP Enrollment objects sent to WF
-- AND wf.itemnum = '120245' -- filter by WV objectnum
-- AND rm1050.attr1670 = '744' -- filter by MIHP_ID
ORDER BY 2, ENTRYTIME, EXITTIME
;
