Notifications
Clear all
Topic starter
Anyone know of a report or query to show which items are on each custom list? Preferably also displaying the item's type (service, test, referral) and category it's located in. Trying to clean up the system (EMR 9.5).
Thanks,
Sam
Posted : August 7, 2012 10:28 pm
This is the query I use to find our custom orders lists and categories:
SELECT q.DESCRIPTION [Custom Order list], corders.DESCRIPTION [Order Name], ISNULL(corders.CODE, '') [CPT Code],
CASE
WHEN corders.ORDERTYPE = 'T'
THEN 'Test'
WHEN corders.ORDERTYPE = 'S'
THEN 'Service'
WHEN corders.ORDERTYPE = 'R'
THEN 'Referral'
WHEN corders.ORDERTYPE = 'H'
THEN 'Orders List Header'
WHEN corders.ORDERTYPE = 'P'
THEN 'Order Set (Panel)'
ELSE ''
END [Type], oc.CATNAME [Category]
FROM CUSTOMORDERS corders
LEFT JOIN QPICKS q ON corders.GID = q.QPID
JOIN ORDERCODES o ON corders.ORDCODEID = o.ORDCODEID
JOIN ORDERCAT oc ON o.ORDCATID = oc.ORDCATID
ORDER BY q.DESCRIPTION
Posted : August 7, 2012 11:07 pm
Topic starter
That query doesn't appear to group the results by the custom list they are on... or does it?
The task at hand is trying to track down which custom list an orderable is located on.
Posted : August 7, 2012 11:09 pm
Yeah, I ordered it by custom list. It will show your custom list, the order's name, the cpt code for the order, order type, and the category from where the order came from.
Posted : August 7, 2012 11:11 pm
Topic starter
Cool. Although we're on EMR 9.5 oracle... bummer. 🙁
Posted : August 7, 2012 11:17 pm
Just quickly thrown together - first 3 queries combined give you problem custom lists, then the next is handouts then the next is orders (
/* Problem Custom Lists */ select q.qpicktype,q.description,c.code,c.description from ml.cptdept c, ml.qpicks q where c.gid = q.qpid order by q.qpicktype,q.description select q.qpicktype,q.description,i.code,i.description from ml.icddept i, ml.qpicks q where i.gid = q.qpid order by q.qpicktype,q.description select q.qpicktype,q.description,m.code,m.description from ml.miscdept m, ml.qpicks q where m.gid = q.qpid order by q.qpicktype,q.description select q.qpicktype,q.description,h.folder,h.description from ml.handdept h, ml.qpicks q where h.gid = q.qpid order by q.qpicktype,q.description --handout custom lists
select q.qpicktype,q.description,o.ordcodeid,o.ordertype,o.code,o.description from ml.customorders o, ml.qpicks q where o.gid = q.qpid order by q.qpicktype,q.description,o.ordertype --orders custom list
David Shower
OU Tulsa School of Community Medicine
Posted : August 8, 2012 12:23 am
Topic starter
DavidShower said:
Just quickly thrown together - first 3 queries combined give you problem custom lists, then the next is handouts then the next is orders (
/* Problem Custom Lists */ select q.qpicktype,q.description,c.code,c.description from ml.cptdept c, ml.qpicks q where c.gid = q.qpid order by q.qpicktype,q.description select q.qpicktype,q.description,i.code,i.description from ml.icddept i, ml.qpicks q where i.gid = q.qpid order by q.qpicktype,q.description select q.qpicktype,q.description,m.code,m.description from ml.miscdept m, ml.qpicks q where m.gid = q.qpid order by q.qpicktype,q.description select q.qpicktype,q.description,h.folder,h.description from ml.handdept h, ml.qpicks q where h.gid = q.qpid order by q.qpicktype,q.description --handout custom listsselect q.qpicktype,q.description,o.ordcodeid,o.ordertype,o.code,o.description from ml.customorders o, ml.qpicks q where o.gid = q.qpid order by q.qpicktype,q.description,o.ordertype --orders custom listDavid ShowerOU Tulsa School of Community Medicine
Is this for Oracle EMR?
Posted : August 8, 2012 1:13 am
Yes
Posted : August 8, 2012 1:18 am
Topic starter
Ran within Oracle or ???
Posted : August 8, 2012 1:20 am
Yes, EMR 9.5 patch 1. Ran with the report user. Are you getting some kind of error?
Here is what the output from the handout query:
H *Flu (Influenza) 2009 Enterprise Flu (Influenza) Test is Postive - 2009 H *Flu (Influenza) 2009 Enterprise Flu (Influenza) Test is Negative - 2009 H Adult Patient Instructions CCC *Patient Instructions H Adult Patient Instructions Enterprise Clinical Visit Summary H Allergy Patient Instructions Enterprise Clinical Visit Summary H Allergy Patient Instructions CCC *Patient Instructions H CCC Enterprise Clinical Visit Summary H CCC CCC *Patient Instructions H CCC CCC Admission Orders etc....
Posted : August 8, 2012 1:58 am
