jmulhall00
b15515708a
updated view for meditech admin to compare against omnicell dispense (for example)
98 lines
No EOL
2 KiB
Text
98 lines
No EOL
2 KiB
Text
CREATE OR REPLACE VIEW cusrep.vw_meditech_dispense_report AS
|
|
|
|
SELECT
|
|
pv."MRN" AS mrn,
|
|
pv."Program Code" AS program_code,
|
|
pv."Patient Name" AS patient_name,
|
|
pv."DOB" AS dob,
|
|
|
|
rm.patientid,
|
|
rx.visitid,
|
|
rm.accountnumber,
|
|
|
|
d.prescriptionid,
|
|
d.seqid AS dispense_seqid,
|
|
|
|
TO_TIMESTAMP(
|
|
d.datetimefieldid,
|
|
'YYYYMMDD.HH24MI'
|
|
)::timestamp AS dispense_datetime,
|
|
|
|
d.datetimefieldid AS dispense_datetime_raw,
|
|
|
|
COALESCE(
|
|
d.componentdrugid,
|
|
d.odfdrugid,
|
|
m.drugid
|
|
) AS drugid,
|
|
|
|
d.odfdrugname,
|
|
|
|
d.dose AS dispense_dose,
|
|
d.doses AS dispense_doses,
|
|
d.items AS dispense_items,
|
|
|
|
m.dose AS ordered_dose,
|
|
m.dosedescription,
|
|
m.dispensingunit,
|
|
m.orderingunit,
|
|
m.totaldose,
|
|
m.totaldoseunits,
|
|
|
|
-- SIG
|
|
rx.directionid AS directionid,
|
|
sig.description AS sig,
|
|
|
|
-- Prescriber
|
|
rx.providerid AS prescriber_id,
|
|
|
|
TRIM(
|
|
COALESCE(prov.firstname, '') || ' ' ||
|
|
COALESCE(prov.lastname, '')
|
|
) AS prescriber_name,
|
|
|
|
-- Dispense details
|
|
d.type AS dispense_type,
|
|
d.dispensingmachineid,
|
|
d.bottlenumber,
|
|
d.bottletype,
|
|
d.billingcode,
|
|
d.charged,
|
|
|
|
-- Order details
|
|
rx.rxnumber,
|
|
rx.orderid,
|
|
rx.ordertype,
|
|
rx.routeofadministration,
|
|
rx.schedule,
|
|
rx.status,
|
|
|
|
-- Audit/reference
|
|
d.servicedatefield,
|
|
d.infodttm,
|
|
d.rowupdatedatetime
|
|
|
|
FROM npr.pharxdispensedmanualx d
|
|
|
|
JOIN npr.pharx rx
|
|
ON rx.sourceid = d.sourceid
|
|
AND rx.prescriptionid = d.prescriptionid
|
|
|
|
JOIN mat.regacct_main rm
|
|
ON rm.visitid = rx.visitid
|
|
|
|
LEFT JOIN cusrep.vw_patient_visit pv
|
|
ON pv.visitid = rm.visitid
|
|
AND pv.patientid = rm.patientid
|
|
|
|
LEFT JOIN npr.pharxmedications m
|
|
ON m.sourceid = d.sourceid
|
|
AND m.visitid = rx.visitid
|
|
AND m.prescriptionid = d.prescriptionid
|
|
|
|
LEFT JOIN npr.dmisdirection sig
|
|
ON sig.sourceid = rx.sourceid
|
|
AND sig.directionid = rx.directionid
|
|
|
|
LEFT JOIN npr.dmisprovider prov
|
|
ON prov.providerid = rx.providerid; |