forked from lewisschmidt/CCL
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Orders_CSA_mapping
287 lines (263 loc) · 11.5 KB
/
Orders_CSA_mapping
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
select
oc.primary_mnemonic
,oc.cki
,ocs.mnemonic
,hidden = evaluate(ocs.hide_flag, 0, "No", "Yes")
,ocs.cki
,reference_info = "-->"
;The name of the Multum synonym
,multum_synonym_name = mdn.drug_name
;Indicates that this synonym is hidden
,hidden = evaluate(ocs.hide_flag, 0, "No", "Yes")
;Indicates the synonym is obsolete
,cki_obsolete_synonym = evaluate(mdn.is_obsolete, "T","Yes","No")
;The CSA schedule of the formulation (if any)
,Multum_mmdc_csa_schedule = evaluate(mmnm.main_multum_drug_code, 0, "N/A", mnmdc.csa_schedule)
,analysis_info = "-->"
;Indicates the synonym has a malformed or unexpected CKI, synonym CKI's should be of the format MUL.ORD-SYN!#
,bad_synonym_cki_format = evaluate(findstring("MUL.ORD-SYN", ocs.cki),0,"Yes", "No")
;Indicates the synonym's cki does not correspond to a Multum synonym, the number following the "!"
,cki_broken_link = evaluate(mdn.drug_synonym_id, 0.00, "Yes", "No")
;Indicates the if the associated multum synonym is not actualy a product.
,non_product_synonym_cki = evaluate(trim(cnvtstringchk(mdnm.function_id)), "59", "No", "60", "No", "0", "N/A", "Yes")
;Indicates the the MMCD link (formulation link) is broken for this synonym
,mmdc_no_link = evaluate(mmnm.main_multum_drug_code, 0, "Yes", "No")
from
order_catalog oc
,order_catalog_synonym ocs
,mltm_drug_name mdn
,mltm_drug_name_map mdnm
,mltm_mmdc_name_map mmnm
,mltm_ndc_main_drug_code mnmdc
,mltm_ndc_core_description mndc
Plan oc
where oc.active_ind = 1
and oc.catalog_type_cd = 2516.00
and oc.primary_mnemonic in (
"ALfentanil"
,"buprenorphine"
,"butalbital" ; <--primary it's wierd... no products or mnemonics
,"butalbital/ASA/caffeine"
,"Butorphanol"
,"codeine"
,"acetaminophen-codeine"
,"fentanyl"
,"HYDROcodone"
,"hydrocodone/brompheniramine/PE/guaifenesin"
,"HYDROcodone"
,"HYDROcodone-phenyltoloxamine"
,"Meperidine"
,"Methadone"
,"Normethadone"
,"Oxycodone"
,"oxyCODONE-acetaminophen"
,"oxyCODONE-acetylsalicylic acid"
,"Pentazocine"
,"Sufentanil"
,"Tapentadol"
)
join ocs
where ocs.catalog_cd = oc.catalog_cd
and ocs.active_ind = 1
/*
and ocs.mnemonic in (
;"ALfentanil" ; <-- primary
;,"Alfenta" ;<-- not in build domain
;----------------
;,"Anileridine" ; <-- as per wikipedia, no longer manufactured???
;----------------
;,"buprenorphine"
;,"Butran" ; <-- not in build domain, butran 10,15,20 is
;,"Suboxone" ; <-- zz'd out
;----------------------
;,"butalbital" ; <--primary it's wierd... no products or mnemonics
;,"Fiorinal" ;
;,"Fiorinal C 1/2" ; <-- not in build domain
;,"Fiorinal C 1/4" ; <-- not in build domain
;,"Ratio-Tecnal" ;
;,"Ratio-Tecnal C 1/2" ; <-- not in build domain
;,"Ratio-Tecnal C 1/4" ; <-- not in build domain
;----------------------
;,"Butorphanol" ; <--primary
;,"Stadol NS" ; <-- zz'd in the OC
;----------------------
;Codeine when prescribed as a single entity or
;when included in a preparation containing 60 mg or more per dosage unit
;-----------------------------------
;,"codeine" ; <-- primary
;,"Codeine 15 mg tablet"
;,"Codeine 30 mg tablet"
;,"Codeine 60 mg tablet" ;<-- inactivated, zz'd
;,"Codeine Contin"
;-----------------------------------
;acetaminophen-codeine ; <- primary
;,"Empracet-60" ;<--- not in build
;,"Ratio-Lenoltec No. 4"
;,"Tylenol with Codeine No. 4"
;-----------------------------------
;,"Ethchlorvynol" ;<--- not in build domain
;,"Placidyl" ;<--- not in build domain
;-----------------------------------
;,"Fentanyl" ; <--- primary
;,"Duragesic" ;
;---------------------------
;,"Hydrocodone" ; <-- Primary
;,"Dihydrocodeinone" ; <-- not in build
;,"Coristine-DH" ; <-- not in build
;------------------------------
;hydrocodone/brompheniramine/PE/guaifenesin; <-- PRIMARY
;,"Dimetane Expectorant-DC" ;
;----------------------------
;,"HYDROcodone";<-- primary
;,"Hycodan" ;
;----------------------------------
;,"ammonium chloride/HYDROcodone/PE/pyrilamine" ; <-- primary
;,"Hycomine syrup" ;
;,"Hycomine-S (pediatric syrup)"
;,"Novahistex DH"
;,"Novahistex DH Expectorant" ; <- not in build domain
;,"Novahistine DH"
;,"Ratio-Coristex-DH"
;,"HYDROcodone-phenyltoloxamine" ;<- primary
;,"Tussionex"
;------------------------------------
,"Hydromorphone" ; <-- primary
; Dihydromorphinone ;
,"Dilaudid" ;
;,"Dilaudid-HP" ; <-- zz'd and inactivated
,"Dilaudid-XP" ; <-- not in build
,"Hydromorph Contin" ;
;------------------------------------
,"Levorphanol" ; <-- not in build
;------------------------------------
,"Meperidine" ; <-- primary
;"(Pethidine)" ;
,"Demerol"
;------------------------------------
,"Methadone" ; <-- primary
;------------------------------------
,"Methaqualone" ; <-- not in order catalog build
;------------------------------------
;,"Morphine" ;<- primary
;,"M-Eslon"
,"Morphitec" ;<- not in build oc
,"Morphine HP" ;<- not in build oc
,"M.O.S."
,"M.O.S.-SR"
,"MS-IR"
,"MS Contin"
,"Tincture of Opium"
;------------------------------------
,"Normethadone" ; bad build
;------------------------------------
,"Oxycodone" ;<- primary
,"OxyContin"
,"Supeudol"
;------------------------------------
,"oxyCODONE-acetaminophen" ;<- primary
,"Endocet"
,"Percocet"
,"Percocet-Demi"
;-----------------------------------
,"oxyCODONE-acetylsalicylic acid"
,"Endodan"
,"Percodan"
,"Oxycocet" ; <--not in build oc
,"Oxycodan" ; <-- not in build oc
;------------------------------------
,"Pentazocine" ; <- primary
,"Talwin"
;------------------------------------
,"Propoxyphene" ; <- primary and inactive
;(Dextropropoxyphene) ; <- inactive in build oc
,"Darvon-N" ; <- inactive in build oc
,"692 Tablets" ; <- inactive in build oc
,"642 Tablets" ; <- inactive in build oc
,"Novo-Propoxyn" ; <- inactive in build oc
,"Novo-Propoxyn Compound" ; <- inactive in build oc
;-------------------------------------
,"Sufentanil" ; <--- Primary
,"Sufenta" ;
;-------------------------------------
,"Tapentadol" ; <--- Primary
,"Nucynta"
)
*/
and ocs.mnemonic_type_cd in (
;--Code values from
;select code_value from code_value where cdf_meaning in
;("GENERICPROD","GENERICTOP","TRADEPROD","TRADETOP") and code_set = 6011 go
;CODE_VALUE DISPLAY CDF_MEANING DESCRIPTION DISPLAY_KEY CKI
"2579.00" ; Ancillary ANCILLARY Ancillary ANCILLARY CKI.CODEVALUE!3124
,"2580.00"; Brand Name BRANDNAME Brand Name BRANDNAME CKI.CODEVALUE!3125
,"2581.00"; Direct Care Provider DCP Direct Care Provider DIRECTCAREPROVIDER CKI.CODEVALUE!3126
,"614542.00"; C - Dispensable Drug Names DISPDRUG Dispensable Drug Names CDISPENSABLEDRUGNAMES CKI.CODEVALUE!2119828
,"2582.00"; Generic Name GENERICNAME Generic Name GENERICNAME CKI.CODEVALUE!3127
,"614548.00"; Y - Generic Products GENERICPROD Generic Products YGENERICPRODUCTS CKI.CODEVALUE!2119825
,"614544.00"; M - Generic Miscellaneous Products GENERICTOP Generic Miscellaneous Products MGENERICMISCELLANEOUSPRODUCTS CKI.CODEVALUE!2119824
,"614543.00"; E - IV Fluids and Nicknames IVNAME IV Fluids and Nicknames EIVFLUIDSANDNICKNAMES CKI.CODEVALUE!2119829
,"614546.00"; Outreach OUTREACH Outreach OUTREACH CKI.CODEVALUE!146793
,"614547.00"; PathLink PATHLINK PathLink PATHLINK CKI.CODEVALUE!150685
,"2583.00"; Primary PRIMARY Primary PRIMARY CKI.CODEVALUE!3128
,"2584.00"; Rx Mnemonic RXMNEMONIC Rx Mnemonic RXMNEMONIC CKI.CODEVALUE!5384
,"2585.00"; Surgery Med SURGERYMED Surgery Med SURGERYMED CKI.CODEVALUE!3129
,"614549.00"; Z - Trade Products TRADEPROD Trade Products ZTRADEPRODUCTS CKI.CODEVALUE!2119827
,"614545.00"; N - Trade Miscellaneous Products TRADETOP Trade Miscellaneous Products NTRADEMISCELLANEOUSPRODUCTS CKI.CODEVALUE!2119826
)
and ocs.cki not in (
;--All CKIs reference multum generic product with valid NDCs
select concat("MUL.ORD-SYN!",trim(cnvtstring(mmnm.drug_synonym_id)))
from mltm_mmdc_name_map mmnm,
mltm_ndc_core_description mncd
where mmnm.function_id = 59
and mncd.main_multum_drug_code = mmnm.main_multum_drug_code
and mncd.repackaged = "F"
and mncd.obsolete_date is NULL
;--Remove any Brand Drug Names
and mncd.brand_code not in (
select drug_synonym_id
from mltm_drug_name_map
where function_id = 17
)
)
and ocs.cki not in (
;--All CKIs reference multum trade product with valid NDCs
select concat("MUL.ORD-SYN!",trim(cnvtstring(mmnm.drug_synonym_id)))
from mltm_mmdc_name_map mmnm,
mltm_drug_name_derivation mdnd,
mltm_ndc_core_description mncd
where mmnm.function_id = 60
and mncd.repackaged = "F"
and mncd.obsolete_date is NULL
and mdnd.derived_drug_synonym_id = mmnm.drug_synonym_id
and mdnd.derived_function_id = mmnm.function_id
and mdnd.base_function_id = 17
and mncd.main_multum_drug_code = mmnm.main_multum_drug_code
and mncd.brand_code = mdnd.base_drug_synonym_id)
join mdn
where outerjoin(ocs.cki) = concat("MUL.ORD-SYN!",trim(cnvtstring(mdn.drug_synonym_id)))
join mdnm
where outerjoin(mdn.drug_synonym_id) = mdnm.drug_synonym_id
join mmnm
where outerjoin(mdn.drug_synonym_id) = mmnm.drug_synonym_id
join mnmdc
where outerjoin(mmnm.main_multum_drug_code) = mnmdc.main_multum_drug_code
join mndc
where outerjoin(mnmdc.main_multum_drug_code) = mndc.main_multum_drug_code
and outerjoin("F") = mndc.repackaged
and mndc.obsolete_date = NULL
group by ocs.synonym_id
,ocs.mnemonic_type_cd
,ocs.mnemonic
,mdn.drug_name
,ocs.hide_flag
,ocs.cki
,mdn.drug_synonym_id
,mdnm.function_id
,mdn.is_obsolete
,mmnm.main_multum_drug_code
,mmnm.main_multum_drug_code
,mnmdc.csa_schedule
,mndc.main_multum_drug_code
order by oc.primary_mnemonic, ocs.mnemonic
with time = 120