forked from lewisschmidt/CCL
-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathEprescribe_Hist_NPI
93 lines (76 loc) · 2.17 KB
/
Eprescribe_Hist_NPI
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
SELECT DISTINCT
ma_rx_id = M.RX_IDENTIFIER
, type = sa.msg_trig_action_txt
, status = uar_get_code_display(m.status_cd)
, error = uar_get_code_display(m.error_cd)
, organization = org.org_name
, send_date = format(o.orig_order_dt_tm, "MM/DD/YYYY HH:MM;;d")
, ord_provider = p.name_full_formatted
, patient_name = pe.name_full_formatted
, encounter_id = m.encntr_id
, pharmacy = od.oe_field_display_value
, pharmacy_id = m.pharmacy_identifier
, order_id = o.order_id
, ORDERED_AS = o.ordered_as_mnemonic
, DEA_SCHEDULE = mmdc.csa_schedule
, ACTING_USER = p1.name_full_formatted
, NPI = uar_get_code_display(pa.prsnl_alias_type_cd)
, Reference = "--->"
, NPI_Value = pa.alias
FROM
MESSAGING_AUDIT M
, SI_AUDIT SA
, ORGANIZATION ORG
, ORDERS O
, ORDER_CATALOG OC
, MLTM_NDC_MAIN_DRUG_CODE MMDC
, PRSNL P;<----
, PERSON PE ;<---
, PRSNL_ALIAS PA;<---
, ORDER_DETAIL OD
, PRSNL P1
, ENCOUNTER E
Plan M
where m.messaging_audit_id != 0
and m.updt_dt_tm between cnvtlookbehind("14,d") and cnvtdatetime(curdate,curtime)
Join SA
where sa.msg_ident = outerjoin(m.rx_identifier)
and sa.msg_trig_action_txt = "NEWRX"
Join ORG
where org.organization_id = m.org_id
Join O
where o.order_id = m.order_id
Join OC
where o.catalog_cd = oc.catalog_cd
Join MMDC
Where (OC.CKI = CONCAT("MUL.ORD!" , TRIM ( CNVTSTRING (MMDC.drug_identifier))))
and mmdc.main_multum_drug_code =
(
select max (mmdc2.main_multum_drug_code)
from mltm_ndc_main_drug_code mmdc2
where (OC.CKI = CONCAT("MUL.ORD!" , TRIM ( CNVTSTRING (MMDC2.drug_identifier))))
)
Join P
where p.person_id = m.ordering_phys_id
and p.physician_ind = 1
and p.active_ind = 1
Join PE
where m.person_id = pe.person_id
and pe.active_ind = 1
Join PA
where p.person_id = pa.person_id
and pa.prsnl_alias_type_cd = value(uar_get_code_by("MEANING", 320, "NPI"))
and pa.active_ind = 1
Join OD
where od.order_id = o.order_id
and od.oe_field_meaning = "ROUTINGPHARMACYNAME"
Join P1
Where m.action_prsnl_id = p1.person_id
Join E
Where e.encntr_id = m.encntr_id
ORDER BY
o.orig_order_dt_tm DESC
, M.RX_IDENTIFIER
, m.status_cd DESC
, 0
WITH NOCOUNTER, SEPARATOR=" ", FORMAT, time = 200, maxrec = 10