OS_w_infuseover

select ;distinct  distinct will create a CLOB ora error https://connect.ucern.com/message/308733#308733
 primary_syn = substring(1, 45, oc.primary_mnemonic) ; primary order
, OCS_MNEMONIC = substring(1, 50, ocs.mnemonic) ; order
, mnemonic_type = uar_get_code_display(ocs.mnemonic_type_cd); mnemonic type
, os.order_sentence_display_line; OS displayed

 
from
	order_catalog oc
	, order_catalog_synonym ocs
	, ord_cat_sent_r ocsr
	, order_sentence os

	, order_sentence_detail osd
	, oe_field_meaning ofm

 
 
Plan oc
	where oc.catalog_type_cd = 2516.00
;	and oc.catalog_cd = 643041.00 ; acetaminophen
	and oc.active_ind = 1
	and oc.activity_type_cd = 705.00; pharmacy
 
Join ocs
	where oc.catalog_cd = ocs.catalog_cd
	and ocs.synonym_id > 0
 
Join ocsr
	where ocs.synonym_id = ocsr.synonym_id
 
Join os
	where os.order_sentence_id = ocsr.order_sentence_id
;	and cnvtlower(os.order_sentence_display_line) = VALUE(cnvtlower(CONCAT("*","FEVER","*"))) ; remove b/c the cs4005 is now incuded
 
Join osd
	where osd.order_sentence_id = os.order_sentence_id
	and osd.oe_field_value = 1
	
 
Join ofm
	where ofm.oe_field_meaning_id = osd.oe_field_meaning_id
	and ofm.oe_field_meaning =  "INFUSEOVER"
 
with time = 100, maxrec = 1000