Note: We no longer publish the latest version of our code here. We primarily use a kumc-bmi github organization. The heron ETL repository, in particular, is not public. Peers in the informatics community should see MultiSiteDev for details on requesting access.

source: heron_load/epic_concepts_load.sql @ 0:42ad7288920a

heron-michigan tip
Last change on this file since 0:42ad7288920a was 0:42ad7288920a, checked in by Matt Hoag <mhoag@…>, 6 years ago

Merge with demo_concepts_3800

File size: 102.3 KB
Line 
1/* epic_concepts_load.sql -- load i2b2 concept hierarchy from source data
2
3Copyright (c) 2012 University of Kansas Medical Center
4part of the HERON* open source codebase; see NOTICE file for license details.
5* http://informatics.kumc.edu/work/wiki/HERON
6*/
7
8/* epic_audit_info looks up the source_cd for this ETL job. */
9create or replace view epic_audit_info as
10select * from BlueHeronData.source_master
11where source_cd like 'Epic@%';
12
13create or replace view gpc_audit_info as
14select * from BlueHeronData.source_master
15where source_cd like 'GPC%';
16
17
18-- check for curated data
19select category from local_labs where 1=0;
20
21-- Enough preliminaries. Let's wipe out the concepts and start.
22truncate table BLUEHERONMETADATA.epic_terms;
23
24/**
25* Alerts / General Alerts / Medical Alerts / Alert Description Concepts
26-- general_alt_type_c - zc_alt_type
27---- med_alert_type_c
28  ------ alert desc
29
30drug-drug interaction alerts (med_alert_type_c=1)
31There are 1700 distinct alert descriptions for drug-drug interaction. Interactions are determined at the drug ingredient level.
32In general, they look like ABCIXIMAB / ANTICOAGULANTS. Group the 1700 concepts in alphabetical folders (A, B, C, etc)
33**/
34
35insert into BLUEHERONMETADATA.epic_terms (
36  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
37  c_visualattributes,
38  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
39  c_operator,  m_applied_path,
40  update_date, import_date, sourcesystem_cd
41)
42with
43/* mapping of general_alt_type_c to corresponding zc table id*/
44alt_table_to_type as
45(select 1 as gen_alt_type_c, 3 as med_alt_type_c from dual),
46
47medical_alert_type as
48(select med_alert_type_c, name, title, abbr from clarity.zc_med_alert_type@id),
49
50base_alert_hierarchy as
51(select '\i2b2\Alerts\' as concept_path,
52        'Alerts (DRAFT)' as name_char,
53        null as concept_cd,
54        'FA' as c_visualattributes
55 from dual
56),
57
58base_gen_hierarchy as
59(select bah.concept_path||aty.alt_type_c||'\'  as concept_path,
60        aty.name as name_char,
61        'KUH|GEN_ALERT:'||aty.alt_type_c as concept_cd,
62        case when exists (select 1 from clarity.alert@id al where al.general_alt_type_c = aty.alt_type_c)
63             then
64               case when exists (select 1 from alt_table_to_type a
65                                 where (a.gen_alt_type_c <> aty.alt_type_c
66                                   and a.med_alt_type_c <>  aty.alt_type_c ))   
67                  then 'LA'
68                  else 'FA'
69               end
70             else 'LH'
71        end as c_visualattributes
72 from base_alert_hierarchy bah
73    , clarity.zc_alt_type@id aty
74),
75   
76base_med_hierarchy as
77(select bah.concept_path||att.med_alt_type_c||'\'||alm.med_alert_type_c||'\'  as concept_path,
78        alm.name as name_char,
79        'KUH|MED_ALERT:'||alm.med_alert_type_c as concept_cd,
80        case when exists (select 1 from clarity.alert@id al where al.med_alert_type_c = alm.med_alert_type_c)
81             then 'FA'
82             else 'LH'
83        end as c_visualattributes
84  from base_alert_hierarchy bah
85     , medical_alert_type alm
86     , alt_table_to_type att
87),
88   
89desc_locator as
90(select BPA_LOCATOR_ID, ALERT_DESC
91 from clarity.alert@id
92 where BPA_LOCATOR_ID is not null
93 group by BPA_LOCATOR_ID, ALERT_DESC
94 order by BPA_LOCATOR_ID),
95
96multi_name as
97(select BPA_LOCATOR_ID, count(*) count
98 from desc_locator
99 group by BPA_LOCATOR_ID having count(*) >= 1),
100
101bpa_locator_hierarchy as
102(select bah.concept_path||att.gen_alt_type_c||'\'||dl.BPA_LOCATOR_ID||'\'  as concept_path,
103        max(dl.alert_desc) as name_char,
104        'KUH|BPA_LOCATOR_ID:'||dl.BPA_LOCATOR_ID as concept_cd,
105        'LA' as c_visualattributes
106 from base_alert_hierarchy bah
107    , alt_table_to_type att
108    , multi_name mn
109 join desc_locator dl on dl.BPA_LOCATOR_ID = mn.BPA_LOCATOR_ID
110 group by dl.BPA_LOCATOR_ID, att.gen_alt_type_c, bah.concept_path
111 order by dl.BPA_LOCATOR_ID
112),
113
114clarity_al as
115(select distinct alm.alert_desc,alm.med_alert_type_c
116 from clarity.alert@id alm where med_alert_type_c is not null),
117 
118al_hash_value as
119(select alert_desc, med_alert_type_c,
120        -- Sometimes the first character of `alert_desc` is non-alphanumeric
121        -- which is undesirable for bucketing. 
122        -- In that case use the second character of `alert_desc`.
123        case when (REGEXP_LIKE(substr(alert_desc,1,1), '[[:alnum:]]'))
124             then substr(alert_desc,1,1)
125             else substr(alert_desc,2,1)
126        end as first_char,
127 -- see epic_alerts_transform.sql for discussion of hashing
128 DBMS_CRYPTO.Hash(UTL_I18N.STRING_TO_RAW(alert_desc),3) as desc_hash
129 from clarity_al alm),
130 
131first_char_grouping as
132(select distinct first_char from al_hash_value),
133
134alert_alpha_concepts as
135(select bah.concept_path||att.med_alt_type_c||'\'||elg.med_alert_type_c||
136        '\'||fcg.first_char||'\' as concept_path,
137        fcg.first_char as name,
138        to_char(null) as concept_cd,
139        'FA' as c_visualattributes 
140 from base_alert_hierarchy bah,
141      alt_table_to_type att,
142      medical_alert_type elg,
143      first_char_grouping fcg),
144     
145alert_concepts_with_alpha as
146(select bah.concept_path||att.med_alt_type_c||
147        '\'||elg.med_alert_type_c||'\'||elg.first_char||
148        '\'||elg.desc_hash||'\' as concept_path,
149        elg.alert_desc as name_char,
150        'KUH|DI:'||elg.desc_hash as concept_cd,
151        'LA' as c_visualattributes 
152 from base_alert_hierarchy bah,
153      alt_table_to_type att,
154      al_hash_value elg)
155     
156select
157  length(con.concept_path) - length(replace(con.concept_path, '\', '')) - 2 as c_hlevel,
158  con.concept_path as c_fullname, con.name_char, con.concept_cd, con.concept_path, c_visualattributes,
159  norm.*,
160  sysdate update_date, sysdate import_date, aud.source_cd
161from ( 
162  select * from base_alert_hierarchy
163  union all 
164  select * from base_gen_hierarchy
165  union all 
166  select * from bpa_locator_hierarchy
167  union all 
168  select * from base_med_hierarchy
169  union all 
170  select * from alert_alpha_concepts
171  union all 
172  select * from alert_concepts_with_alpha
173) con
174, BlueHeronMetadata.normal_concept norm, epic_audit_info aud;
175
176
177/* Visit Details Concept (Inpatient: EPIC, Outpatient: IDX) */
178insert into BLUEHERONMETADATA.epic_terms (
179  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
180  c_visualattributes,
181  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
182  c_operator,  m_applied_path,
183  update_date, import_date, sourcesystem_cd
184)
185select
186  l, con.concept_path, con.name_char, con.concept_cd, con.concept_path, c_visualattributes,
187  norm.*,
188  sysdate, sysdate, aud.source_cd
189from (
190     select 1 as l
191        , '\i2b2\Visit Details\' as concept_path
192        ,null as concept_cd
193        , 'Visit Details' as name_char -- #997
194        , 'FA' as c_visualattributes --FA for folders
195  from dual
196  union all
197  select 2 as l
198        , '\i2b2\Visit Details\Services\' as concept_path
199        ,null as concept_cd
200        , 'Clinical Services' as name_char
201        , 'FA' as c_visualattributes --FA for folders
202  from dual
203  union all
204   select 3 as l
205        , '\i2b2\Visit Details\Services\Inpatient Visits\' as concept_path
206        ,null as concept_cd
207        , 'Inpatient Visits' as name_char
208        , 'FA' as c_visualattributes --FA for folders
209  from dual
210   union all
211   select 4 as l
212        , '\i2b2\Visit Details\Services\Inpatient Visits\' || service.hosp_serv_c || '\'  as concept_path
213        ,'KUMC|VISITDETAIL|HSPSERVICES:'|| service.hosp_serv_c as concept_cd
214        , service.name as name_char
215        , 'LA' as c_visualattributes --LA for leaf
216  from CLARITY.zc_pat_service@id service 
217  union all
218     select 3 as l  -- 3 communicates number of hierarchical levels
219        , '\i2b2\Visit Details\Services\Outpatient Visits\' as concept_path
220        ,null as concept_cd
221        , 'Outpatient Visits' as name_char
222        , 'FA' as c_visualattributes --FA for folders
223  from dual
224  union all
225  select distinct 4 as l -- 4 indicates number of hierarchical levels
226       , '\i2b2\Visit Details\Services\Outpatient Visits\' || idx_service.division_code || '\'  as concept_path
227       , null as concept_cd
228       , idx_service.division_name as name_char
229       , 'FA' as c_visualattributes  --FA for folders
230  from KUPI.idx_table@id idx_service
231    union all
232  select distinct 5 as l -- 5 indicates number of hierarchical levels
233       , '\i2b2\Visit Details\Services\Outpatient Visits\' || idx_service.division_code || '\' || idx_service.billing_area_code || '\'  as concept_path
234       ,'KUMC|VISITDETAIL|OPSERVICES:'|| idx_service.division_code||'|OPAREAS:'|| idx_service.billing_area_code as concept_cd
235       , idx_service.billing_area_name as name_char
236       , 'LA' as c_visualattributes --LA for leaf
237  from KUPI.idx_table@id idx_service
238  ) con
239, BlueHeronMetadata.normal_concept norm, epic_audit_info aud;
240
241
242/* Visit Details Concept (Place of Service) */
243insert into BLUEHERONMETADATA.epic_terms (
244  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
245  c_visualattributes,
246  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
247  c_operator,  m_applied_path,
248  update_date, import_date, sourcesystem_cd
249)
250
251with base_pofs as
252(select 2 as l
253      , '\i2b2\Visit Details\Place of Service (IDX)\' as concept_path
254      , null as concept_cd
255      , 'Place of Service (IDX)' as name_char
256      , 'FA' as c_visualattributes  -- FA for folders
257  from dual),
258
259pofs as
260(select distinct base.l + 1 as l
261      , base.concept_path||nvl(idx.hcfavalue, 'NOS')||'\' as concept_path
262      , 'KUMC|VISITDETAIL|POS(IDX):'||nvl(idx.hcfavalue, 'NOS') as concept_cd
263      -- Using RTRIM to remove padding at end of POS
264      , rtrim(nvl(idx.placeofservicename, 'Not Otherwise Specified')) as name_char
265      , 'LA' as c_visualattributes  -- LA for leaf
266 from KUPI.idx_table@id idx, base_pofs base),
267 
268pofs_merge as
269(select * from base_pofs union all
270 select * from pofs)
271
272select
273  l, con.concept_path, con.name_char, con.concept_cd, con.concept_path,
274  c_visualattributes,
275  norm.*,
276  sysdate, sysdate, aud.source_cd
277from pofs_merge con, BlueHeronMetadata.normal_concept norm, epic_audit_info aud;
278
279
280/**************
281 * EPIC Report and Notes concept
282 */
283
284
285--=== BEGIN EPIC Report and Notes concept PREP ===--
286--There are several joins of the clarity_concept, smrtdta_elem_data, and
287--smrtdta_elem_value tables.  If these occur over a database link it can
288--take a very long time, hence the creation of temporary tables.
289whenever sqlerror continue;
290
291truncate table clarity_concept_temp;
292drop table clarity_concept_temp;
293
294truncate table smrtdta_elem_data_temp;
295drop table smrtdta_elem_data_temp;
296
297truncate table smrtdta_elem_value_temp;
298drop table smrtdta_elem_value_temp;
299
300truncate table smrtdta_elem_temp;
301drop table smrtdta_elem_temp;
302
303drop index clarity_concept_temp_index;
304drop index smrtdta_elem_temp_index;
305
306whenever sqlerror exit;
307
308create table clarity_concept_temp
309as select distinct name, concept_id, parent_concept, data_type_c from clarity.clarity_concept@id;
310create index clarity_concept_temp_index on clarity_concept_temp(concept_id);
311
312create table smrtdta_elem_data_temp
313as select distinct element_id, hlv_id, context_name from clarity.smrtdta_elem_data@id;
314
315create table smrtdta_elem_value_temp
316as select distinct smrtdta_elem_value, hlv_id from clarity.smrtdta_elem_value@id where line = 1;
317
318create table smrtdta_elem_temp as
319select * from smrtdta_elem_data_temp csed
320natural join smrtdta_elem_value_temp csev
321where trim(csev.smrtdta_elem_value) is not null;
322create index smrtdta_elem_temp_index on smrtdta_elem_temp(element_id);
323
324--=== End EPIC Report and Notes concept PREP ===--
325
326
327--create a view of report and note concept extraction such that it is possible
328--to generate an explain plan.
329create or replace view epic_report_and_notes as
330
331with base_reports as
332
333(select 1 as l
334      , '\i2b2\Reports\' as concept_path
335      , null as concept_cd
336      , 'Reports' as name_char -- #997
337      , 'FA' as c_visualattributes --FA for folders
338    from dual),
339   
340base_visit_notes as
341(select 2 as l
342      , base_reports.concept_path || 'Visit Notes\' as concept_path
343      , null as concept_cd
344      , 'Visit Notes' as name_char
345      , 'FA' as c_visualattributes --FA for folders
346    from base_reports),
347   
348base_note_types as
349(select 3 as l
350      , base_visit_notes.concept_path || 'Note Types\' as concept_path
351      , null as concept_cd
352      , 'Note Types' as name_char
353      , 'FA' as c_visualattributes --FA for folders
354    from base_visit_notes),
355
356note_types as
357(select 4 as l
358      , base_note_types.concept_path || notetype.name || '\'  as concept_path
359      , schemes.note_type_scheme || notetype.type_ip_c as concept_cd
360      , notetype.name as name_char
361      , 'LA' as c_visualattributes --LA for leaf
362    from clarity.zc_note_type_ip@id notetype
363    cross join scheme_labels schemes
364    cross join base_note_types),
365
366-- Recursively build the note concepts hierarchy from the clarity concept table
367note_concept_hierarchy as
368(select
369      level +2 as l,
370      '\i2b2\Reports\Visit Notes' || sys_connect_by_path(concept_id, '\') || '\' as concept_path,
371      'KUMC|SMRT|N:'|| conc.concept_id as concept_cd,
372      regexp_replace(conc.name, 'EPIC#ROOTNODE', 'Note Concepts') as name_char, -- give EPIC#ROOTNODE a human readable name
373      'LA' as c_visualattributes --set all as leaf initially
374      --TODO add c_metadataxml to support clarity concepts of the "Numbers" type
375    from clarity_concept_temp conc
376    start with conc.concept_id =  'EPIC#ROOTNODE'
377    connect by prior conc.concept_id = conc.parent_concept),
378
379-- Build the i2b2 concepts representing the discrete selections of clarity concepts
380-- (with the type "category", "boolean", and "element id")
381clarity_concept_collections as
382(select distinct
383     ccc.concept_id as concept_id,
384     (case when ccc.data_type_c = 5
385           then to_char(ora_hash(sdet.smrtdta_elem_value))
386           else sdet.smrtdta_elem_value
387      end
388     ) as raw_data_value,  -- This use of ora_hash is... unfortunate.
389     --The "c_basecode" column in "BLUEHERONMETADATA.epic_terms" has a maximum length
390     --of 50 which makes using all of "smrtdta_elem_value" impossible since they
391     --also require the prefix 'KUMC|SMRT|N:' and a parent concept (likely around 12 characters)
392     --Testing for collisions here (over links), proves to be unnecessarily computationally expensive,
393     --so the test is done when the facts are loaded (epic_notes_transform.sql).
394     (case when sdet.smrtdta_elem_value = '0' and ccc.data_type_c = 6 then 'No'
395           when sdet.smrtdta_elem_value = '1' and ccc.data_type_c = 6 then 'Yes'
396           when cccrename.name is not null then cccrename.name || ' (' || sdet.smrtdta_elem_value || ')'
397           else sdet.smrtdta_elem_value
398      end) as name
399 
400 from clarity_concept_temp ccc
401 join smrtdta_elem_temp sdet on ccc.concept_id = sdet.element_id
402                           --and sdet.context_name = 'NOTE'
403                           --and substr(ccc.concept_id, 1, 3) in ('EPI','MED','KU#','UKP')
404 left join clarity_concept_temp cccrename on cccrename.concept_id = sdet.smrtdta_elem_value
405                                       and ccc.data_type_c = 8
406 where ccc.data_type_c in (5 --category
407                          ,8 --element id
408                          )
409    or (ccc.data_type_c = 6 and smrtdta_elem_value in ('0', '1')) -- for boolean type (6)
410    -- don't bring in elem values that are not 0 or 1 (they are improper uses of the type).
411 order by concept_id),
412 
413--establish the correct concept path for discrete selections identified in clarity_concept_collections
414note_concept_category as
415(select distinct
416     nch.l + 1 as l,
417     nch.concept_path||ccc.raw_data_value||'-cat'||'\' as concept_path, --there are some instances where there are
418     --concepts from the note concept hierarchy that can have replicated concept paths if a duplicate element_id
419     --exists.  Neither is neccessarily invalid, thus uniqueness must be preserved via the '-cat'
420     'KUMC|SMRT|N:'|| ccc.concept_id ||'$'||ccc.raw_data_value as concept_cd,
421     name as name_char,
422     'LA' as c_visualattributes
423 from note_concept_hierarchy nch
424 join clarity_concept_collections ccc on nch.concept_cd = 'KUMC|SMRT|N:'|| ccc.concept_id)
425
426--collect all the concepts and prep them for insertion into BLUEHERONMETADATA.epic_terms
427select
428l, cpath1,
429
430/* Append (<epic_concept>) to the end of the names (#1598)
431*/
432case
433  when concept_cd is not null then
434    case
435      when instr(concept_cd, ':') != 0 and instr(concept_cd, '$') = 0 then -- get the postfix (after ':')
436      --if the concept cd has a ':' but not a '$' (<--- concept_cd with '$' have already had their name_char
437      --processed in clarity_concept_collections).
438        name_char || ' (' || substr(concept_cd, instr(concept_cd, ':', -1) + 1) || ')'
439      else name_char
440    end
441  else name_char
442end name_char,
443concept_cd, cpath2, c_visualattributes,
444norm.*,
445sysdate as update_date, sysdate as import_date, aud.source_cd
446
447from(
448  select
449    l, con.concept_path cpath1,
450
451    /* Take everything after the ' - ' since the names contain redundant path
452    information and become very long (#1598)
453    */
454    case
455      when length(con.name_char) <= length(' - ') or instr(con.name_char, ' - ', -1) = 0 then con.name_char
456      else substr(con.name_char, instr(con.name_char, ' - ', -1) + 3)
457    end 
458    name_char,
459   
460    con.concept_cd, con.concept_path cpath2, c_visualattributes
461  from (
462    select * from base_reports union all
463    select * from base_visit_notes union all
464    select * from base_note_types union all
465    select * from note_types union all
466    select * from note_concept_hierarchy union all
467    select * from note_concept_category
468    ) con
469  ), BlueHeronMetadata.normal_concept norm, epic_audit_info aud
470;
471
472
473--Show explain plan for debugging purposes as this concept load has some computational risk
474--associated with it
475explain plan for (select * from epic_report_and_notes);
476SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
477
478
479insert into BLUEHERONMETADATA.epic_terms (
480  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
481  c_visualattributes,
482  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
483  c_operator,  m_applied_path,
484  update_date, import_date, sourcesystem_cd
485)
486select * from epic_report_and_notes;
487
488
489/* fix note concepts (primarily generated from note_concept_hierarchy) that
490   need to be considered folders*/
491update BLUEHERONMETADATA.epic_terms eT
492set eT.c_visualattributes = 'FA'
493where eT.c_fullname like '\i2b2\Reports\Visit Notes\E%'
494and et.c_basecode in(
495select 'KUMC|SMRT|N:'|| conc.parent_concept as pConcept from
496BLUEHERONMETADATA.epic_terms eTerms,
497CLARITY.clarity_concept@id conc
498where eterms.c_fullname like '\i2b2\Reports\Visit Notes\E%'
499and substr(eTerms.c_basecode, length('KUMC|SMRT|N:')+1) = conc.parent_concept
500group by conc.parent_concept
501having count(*) >= 1);
502
503/* fix note concepts which have a discrete selection to be considered folders
504   NOTE: '$' is the key descriptor to identify note concepts that are
505   a part of a discrete selection
506*/
507update BLUEHERONMETADATA.epic_terms eT
508set eT.c_visualattributes = 'FA'
509where et.c_fullname like '\i2b2\Reports\Visit Notes\E%'
510and et.c_basecode in
511  (
512   select distinct substr(bet.c_basecode,0,instr(bet.c_basecode, '$', -1)-1)
513   from blueheronmetadata.epic_terms bet
514   where bet.c_basecode like '%$%'
515  );
516
517/* Release temp table storage
518 */
519whenever sqlerror continue;
520
521truncate table clarity_concept_temp;
522drop table clarity_concept_temp;
523
524truncate table smrtdta_elem_data_temp;
525drop table smrtdta_elem_data_temp;
526
527truncate table smrtdta_elem_value_temp;
528drop table smrtdta_elem_value_temp;
529
530truncate table smrtdta_elem_temp;
531drop table smrtdta_elem_temp;
532
533whenever sqlerror exit;
534 
535
536
537/*******************
538 * Diagnosis Modifiers... not strictly epic... hmm.
539 */
540insert into BlueHeronMetadata.epic_terms (
541  c_hlevel, c_fullname, c_name, c_synonym_cd,
542  c_visualattributes, c_basecode, c_metadataxml, c_facttablecolumn,
543  c_tablename, c_columnname, c_columndatatype, c_operator,
544  c_dimcode, c_tooltip, m_applied_path, update_date,
545  import_date, sourcesystem_cd )
546
547with dx_mod_terms as (
548/* D = Modifier folder
549   R = Modifier leaf
550    -- section 2.3.5 c_visualattributes
551       of i2b2 Design Document: Ontology Management (ONT) Cell */
552
553select 'DA' va, 1 lvl, 'Billing\' path,                          'Billing (IDX, UHC)' c_name,         null c_basecode from dual union all
554select 'RA',    2,     'Billing\UHC_PROCEDURE\',                   'Hospital procedure (UHC)',     'DiagObs:UHC_PROCEDURE' from dual union all
555select 'RA',    2,     'Billing\UHC_DIAGNOSIS\',                   'Hospital diagnosis (UHC)',     'DiagObs:UHC_DIAGNOSIS' from dual union all
556select 'RA',    2,     'Billing\UKP\',                             'Clinic (IDX)',                 'DiagObs:Clinic' from dual union all
557select 'RA',    2,     'Billing\Primary\',                         'Primary billing diagnosis (IDX, UHC)', 'DiagObs:Primary' from dual union all
558
559
560select 'DA',    1,     'Clinical\',                              'Clinical (O2 EMR)',               null from dual union all
561select 'RA',    2,     'Clinical\PROBLEM_LIST\',                   'Problem List',               'DiagObs:PROBLEM_LIST' from dual union all
562select 'RA',    2,     'Clinical\PRINCIPAL_PL_YN\',                'Principal problem',          'DiagObs:PRINCIPAL_PL_YN' from dual union all
563select 'RA',    2,     'Clinical\HOSPITAL_PL_YN\',                 'Hospital problem',           'DiagObs:HOSPITAL_PL_YN' from dual union all
564
565select 'RA',    2,     'Clinical\PAT_ENC_DX\',                     'Encounter diagnosis',          'DiagObs:PAT_ENC_DX' from dual union all
566select 'RA',    2,     'Clinical\PRIMARY_DX_YN\',                  'Primary diagnosis',         'DiagObs:PRIMARY_DX_YN' from dual union all
567
568select 'RA',    2,     'Clinical\MEDICAL_HISTORY_DX\',             'Medical History diagnosis', 'DiagObs:MEDICAL_HX' from dual union all
569
570select 'DA',    1,     'STATUS_C\',                              'Problem status',             null from dual union all
571
572-- GPC modifiers - #3082
573select c_visualattributes va, to_number(c_hlevel) lvl, c_fullname path, c_name, c_basecode from gpc_billing_diag_modifiers
574),
575problem_status_terms as (
576select 'RA' c_visualattributes
577     , 1+st.lvl lvl
578     , st.path || zps.title || '\' path
579     , zps.name c_name
580     , 'PROBLEM_STATUS_C:' || zps.problem_status_c c_basecode
581from clarity.zc_problem_status@id zps
582join dx_mod_terms st on st.path = 'STATUS_C\'
583)
584
585select
586  lvl c_hlevel, '\Diagnosis\' || mt.path c_fullname, mt.c_name, 'N' c_synonym_cd,
587  mt.va c_visualattributes, mt.c_basecode, null c_metadataxml, 'MODIFIER_CD' c_facttablecolumn,
588  'MODIFIER_DIMENSION' c_tablename, 'MODIFIER_PATH' c_columnname, 'T' c_columndatatype, 'LIKE' c_operator,
589  '\Diagnosis\' || mt.path c_dimcode, mt.c_name c_tooltip, '\i2b2\Diagnoses\%' m_applied_path
590  , sysdate update_date, sysdate import_date, aud.source_cd
591from (select * from dx_mod_terms
592      union all
593      select * from problem_status_terms) mt, epic_audit_info aud;
594
595
596  /***** Microbio Lab Results Concepts****/
597insert into BlueHeronMetadata.epic_terms (
598c_hlevel, c_fullname, c_name, c_synonym_cd,
599c_visualattributes, c_basecode, c_facttablecolumn,
600c_tablename, c_columnname, c_columndatatype, c_operator,
601c_dimcode, c_tooltip, m_applied_path, update_date,
602import_date, sourcesystem_cd )
603
604with
605micro_concept_fill as
606(select to_char(null) as concept_cd,'FA' as va from dual),
607
608base_microbiology as
609(select '\i2b2\Microbiology\' as concept_path,
610    'Microbiology (DRAFT)' as name_char,
611    fill.*
612    from micro_concept_fill fill
613),
614
615order_proc as
616(select * from clarity.order_proc@id),
617
618order_sensitivity as
619(select * from clarity.order_sensitivity@id),
620
621clarity_eap as
622(select * from clarity.clarity_eap@id),
623
624clarity_organism as
625(select * from clarity.clarity_organism@id),
626
627zc_antibiotic as
628(select * from clarity.zc_antibiotic@id),
629
630partial_micro_concept_path as
631(select distinct ceap.proc_id as proc_id
632      , ceap.proc_name as proc_name
633      , coo.organism_id as organism_id
634      , coo.name as organism_name
635      , cza.antibiotic_c as antibiotic_id
636      , cza.name as antibiotic_name
637from order_proc cop
638join clarity_eap ceap      on cop.proc_id = ceap.proc_id
639join order_sensitivity cos on cos.order_proc_id = cop.order_proc_id
640join clarity_organism coo  on cos.organism_id = coo.organism_id
641join zc_antibiotic cza     on cos.antibiotic_c = cza.antibiotic_c
642),
643
644/**
645 START of regex define organism categories
646 */
647filter_all as
648--!replace the temporary delimiter with the "or" separator '|'
649(select regexp_replace(regex, '~', '|') as regex
650 from
651 /***
652  * Using connect by and partitioning to do pure oracle sql string aggregation
653  * TODO - use a more generic string aggregation methodology
654  */
655  --!use '~' as a temporary delimiter; to be replaced with '|' later
656   (select ltrim(max(sys_connect_by_path(regex,'~'))
657         keep (dense_rank last order by curr),'~') as regex
658    from   (select regex,
659                 removal,
660                 row_number() over (partition by removal order by regex) as curr,
661                 row_number() over (partition by removal order by regex)  -1 as prev
662          --! is a curated table that contains all the regular expressions to filter out
663          --! comes from curated_data/organism_name_categorization_filter.csv
664          from organism_name_cat_filter
665          where removal = 'all')
666    connect by prev = prior curr
667    start with curr = 1)
668),
669
670regex_filtered_cat as (
671  select distinct trunc_cat from (
672    select distinct organism_name, trim(organism_name_trunc) as trunc_cat from (
673      select regexp_replace(organism_name,
674                            (select regex from filter_all),
675                            '') organism_name_trunc
676                           , organism_name
677      from partial_micro_concept_path order by organism_name
678    )
679  )
680),
681
682
683bestguess as (
684  select trunc_cat, rownum as org_cat_id from
685   (select cat1.trunc_cat
686    from regex_filtered_cat cat1
687    join regex_filtered_cat cat2 on cat1.trunc_cat like '%'||cat2.trunc_cat||'%'
688    group by cat1.trunc_cat
689    having count(*) = 2
690    order by cat1.trunc_cat)
691),
692
693all_organisms as (
694  select distinct organism_id, organism_name from partial_micro_concept_path
695),
696
697best_guess_to_org as (
698  select ao.*,
699      (case
700        when (bg.trunc_cat is not null)
701        then bg.trunc_cat
702        else 'UNCATEGORIZED ORGANISMS'
703       end) as trunc_cat,
704      (case
705        when (bg.org_cat_id is not null)
706        then bg.org_cat_id
707        else 0
708       end) as org_cat_id
709     
710  from all_organisms ao
711  left join bestguess bg on ao.organism_name like '%'||bg.trunc_cat||'%'
712  order by bg.trunc_cat
713),
714
715/**
716 * END of regex defined organism categories
717 */
718
719full_micro_concept_path as (
720  select pmcp.*, bgto.trunc_cat as organism_cat, bgto.org_cat_id from partial_micro_concept_path pmcp
721  join best_guess_to_org bgto on pmcp.organism_id = bgto.organism_id
722),
723
724proc_tree as
725( select distinct base.concept_path||fmcp.proc_id||'\' as concept_path
726       , fmcp.proc_name as name_char
727       , fill.*
728     from full_micro_concept_path fmcp, micro_concept_fill fill, base_microbiology base
729),
730
731
732organism_cat_tree as
733( select distinct base.concept_path||fmcp.proc_id||'\'||fmcp.org_cat_id||'\' as concept_path
734       , fmcp.organism_cat as name_char
735       , fill.*
736  from full_micro_concept_path fmcp, micro_concept_fill fill, base_microbiology base
737),
738
739
740organism_tree as
741( select distinct base.concept_path||fmcp.proc_id||'\'||fmcp.org_cat_id||'\'||fmcp.organism_id||'\' as concept_path
742       , fmcp.organism_name as name_char
743       , fill.*
744  from full_micro_concept_path fmcp, micro_concept_fill fill, base_microbiology base
745),
746
747antibiotic_tree as
748( select distinct base.concept_path||fmcp.proc_id||'\'||fmcp.org_cat_id||'\'||fmcp.organism_id||'\'||fmcp.antibiotic_id||'\' as concept_path
749       , fmcp.antibiotic_name as name_char
750       , 'KUMC|Micro|Org|Med:'||fmcp.proc_id||'|'||fmcp.organism_id||'|'||fmcp.antibiotic_id as concept_cd
751       , 'LA' as va
752  from full_micro_concept_path fmcp, base_microbiology base
753),
754   
755merge_of_micro_tree as
756   ( select * from base_microbiology union all
757     select * from proc_tree union all
758     select * from organism_cat_tree union all
759     select * from organism_tree union all
760     select * from antibiotic_tree)
761
762select length(momt.concept_path) - length(replace(momt.concept_path, '\', '')) - 2 as c_hlevel,
763       momt.concept_path as c_fullname,
764       momt.name_char as c_name,
765       'N' as c_synonym_cd,
766       momt.va as c_visualattributes,
767       momt.concept_cd as c_basecode,
768       'CONCEPT_CD' as c_facttablecolumn,
769       'concept_dimension' as c_tablename,
770       'CONCEPT_PATH' as c_columnname,
771       'T' as c_columndatatype,
772       'LIKE' c_operator,
773       momt.concept_path as c_dimcode,
774       null as c_tooltip,
775       '@' as m_applied_path,
776       sysdate as update_date,
777       sysdate as import_date,
778       aud.source_cd as sourcesystem_cd
779       
780  from merge_of_micro_tree momt, epic_audit_info aud
781  ;
782
783
784  /***** Microbio Lab Results Susceptibility Modifier****/
785insert into BlueHeronMetadata.epic_terms (
786  c_hlevel, c_fullname, c_name, c_synonym_cd,
787  c_visualattributes, c_basecode, c_facttablecolumn,
788  c_tablename, c_columnname, c_columndatatype, c_operator,
789  c_dimcode, c_tooltip, m_applied_path, update_date,
790  import_date, sourcesystem_cd )
791
792with base_suscept as
793(select '\Susceptability\' as modifier_path
794      , 'Susceptability' as name_char
795      , 'DA' as va
796      , null as modifier_cd
797 from dual),
798
799--include only susceptibility classifications that are being used in
800--susceptibility results
801used_susceptability as
802(select os.suscept_c as suscept_c,
803        zs.name as name
804 from clarity.order_sensitivity@id os
805 join clarity.zc_suscept@id zs on os.suscept_c = zs.suscept_c
806 group by os.suscept_c, zs.name
807 having count(*) > 0 and zs.name is not null
808),
809
810suscept as
811(select '\Susceptability\'||suscept_c||'\' as modifier_path
812      , name as name_char
813      , 'RA' as va
814      , 'MicroSensitivity:'||suscept_c as modifier_cd
815 from used_susceptability),
816 
817suscept_all as
818(select * from base_suscept union all
819 select * from suscept)
820
821select length(sa.modifier_path) - length(replace(sa.modifier_path, '\', '')) - 1 as c_hlevel,
822       sa.modifier_path as c_fullname,
823       sa.name_char as c_name,
824       'N' as c_synonym_cd,
825       sa.va as c_visualattributes,
826       sa.modifier_cd as c_basecode,
827       'MODIFIER_CD' as c_facttablecolumn,
828       'MODIFIER_DIMENSION' as c_tablename,
829       'MODIFIER_PATH' as c_columnname,
830        'T' as c_columndatatype,
831       'LIKE' as c_operator,
832       sa.modifier_path as c_dimcode,
833       sa.name_char as c_tooltip,
834       '\i2b2\Microbiology\%' as m_applied_path,
835       sysdate as update_date,
836       sysdate as import_date,
837       aud.source_cd as sourcesystem_cd
838       
839  from suscept_all sa, epic_audit_info aud;
840
841
842
843/************************
844 * micro_negative_results -- build concepts/terms for microbiology negative results.
845 */
846 
847create or replace view micro_negative_results as
848select
849       cor.order_proc_id,
850       cop.proc_id,
851       ceap.proc_name,
852       cor.component_id,
853       cccc.name as componentname,
854       (case when czs.specimen_source_c is null
855             then 'Specimen source not recorded '
856             else czs.name
857        end) as specimensourcename,
858       (case when cop.specimen_source_c is null
859             then '0'
860             else to_char(cop.specimen_source_c)
861        end) as specimen_source_c
862from clarity.order_proc@id         cop
863join clarity.order_results@id      cor on cop.order_proc_id=cor.order_proc_id
864join clarity.clarity_component@id  cccc on cor.component_id=cccc.component_id
865join clarity.clarity_eap@id        ceap on cop.proc_id=ceap.proc_id
866left join clarity.zc_specimen_source@id czs on cop.specimen_source_c=czs.specimen_source_c
867
868-- Must remain in sync with where clause in microbiology_negativeresults view
869-- [epic_microbiology_transform.sql]
870--TODO remove dependency or delegate dependency to shared location
871where cop.order_type_c=3/*Changing to Order type='Microbiology' instead of Procedure Category MICROBIOLOGY ORDERABLES*/
872/*ceap.proc_cat like '%MICROBIOLOGY ORDERABLES%'*/
873  and   (upper(cor.ord_value) like 'NEG%'
874     or upper(cor.ord_value) like '%NONE%'
875     or upper(cor.ord_value) like 'NOS%'
876     or upper(cor.ord_value) like 'NO %'
877     or upper(cor.ord_value) like '%NOT %'
878     or upper(cor.ord_value) like 'NEGATIVE%'       
879     or upper(cor.ord_value) like '%<15CFU%'
880     or upper(cor.ord_value) like 'NORMAL%'
881     )
882  and cor.result_status_c=3
883  and cor.component_id in
884  (select distinct component_id from microlab_component_whitelist@id)
885  and cop.pat_enc_csn_id is NOT NULL
886   
887order by proc_name,specimensourcename
888;
889
890insert into BLUEHERONMETADATA.epic_terms (
891  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
892  c_visualattributes,
893  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
894  c_operator,  m_applied_path,
895  update_date, import_date, sourcesystem_cd
896)
897select
898  con.c_hlevel, con.concept_path, con.name_char, con.concept_cd, con.concept_path,
899  con.c_visualattributes,
900  norm.*,
901  sysdate, sysdate, aud.source_cd
902from (
903
904  select 1 as c_hlevel
905        , '\i2b2\MicroNegative\' as concept_path
906        , to_char(null) as concept_cd
907        , 'Microbiology Negative Results (DRAFT)' as name_char
908        , 'FA' as c_visualattributes --FA for folders
909  from dual
910 
911  union all
912 
913   select DISTINCT 2 as c_hlevel
914        , '\i2b2\MicroNegative\'||proc_id||'\' as concept_path
915        , to_char(null) as concept_cd
916        , proc_name as name_char
917        , 'FA' as c_visualattributes --FA for folders
918  from micro_negative_results
919  where order_proc_id is NOT NULL
920 
921  union all
922 
923  select distinct 3 as c_hlevel
924        , '\i2b2\MicroNegative\'||proc_id||'\'||specimen_source_c||'\' as concept_path
925        , 'KUMC|MicroNegative:'||proc_id||'|'||specimen_source_c as concept_cd
926        , specimensourcename as name_char
927        , 'LA' as c_visualattributes --LA for leaf elements
928  from micro_negative_results
929  where order_proc_id is NOT NULL
930     
931  ) con
932, BlueHeronMetadata.normal_concept norm, epic_audit_info aud;
933
934
935/*****micro_positive_results -- build concepts/terms for microbiology positive results without sensitivity test. *****/
936       
937insert into BLUEHERONMETADATA.epic_terms (
938  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
939  c_visualattributes,
940  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
941  c_operator,  m_applied_path,
942  update_date, import_date, sourcesystem_cd
943)
944select
945  con.c_hlevel, con.concept_path, con.name_char, con.concept_cd, con.concept_path,
946  con.c_visualattributes,
947  norm.*,
948  sysdate, sysdate, aud.source_cd
949from (
950
951  select 1 as c_hlevel
952        , '\i2b2\MicroPositive\' as concept_path
953        , to_char(null) as concept_cd
954        , 'Microbiology Positive Results (DRAFT)' as name_char
955        , 'FA' as c_visualattributes --FA for folders
956  from dual
957 
958  union all
959 
960   select DISTINCT 2 as c_hlevel
961        , '\i2b2\MicroPositive\'||proc_id||'\' as concept_path
962        , to_char(null) as concept_cd
963        , coalesce(ceapname,copdescription) as name_char
964        , 'FA' as c_visualattributes --FA for folders
965  from microbiology_positiveresults@id
966  where order_proc_id is NOT NULL
967 
968  union all
969 
970  select distinct 3 as c_hlevel
971        , '\i2b2\MicroPositive\'||proc_id||'\'||specimen_source_c||'\' as concept_path
972        , 'KUMC|MicroPositive:'||proc_id||'|'||specimen_source_c as concept_cd
973        , specimensourcename as name_char
974        , 'LA' as c_visualattributes --LA for leaf elements
975  from microbiology_positiveresults@id
976  where order_proc_id is NOT NULL
977     
978  ) con
979, BlueHeronMetadata.normal_concept norm, epic_audit_info aud;
980
981
982
983
984/***************
985 * Allergies / Reactions Concepts
986 */
987insert into BlueHeronMetadata.epic_terms (
988c_hlevel, c_fullname, c_name, c_synonym_cd,
989c_visualattributes, c_basecode, c_facttablecolumn,
990c_tablename, c_columnname, c_columndatatype, c_operator,
991c_dimcode, c_tooltip, m_applied_path, update_date,
992import_date, sourcesystem_cd )
993
994with allergy_concept_fill as
995(select to_char(null) as concept_cd,'FA' as va from dual),
996
997base_allergy_reaction as --This is used as a constant for the string '\i2b2\Allergy\'
998                         --because it contain only one row and we only want to
999                         --write out the magic string once.
1000(select '\i2b2\Allergy\' as concept_path,
1001    'Allergy (DRAFT)' as name_char,
1002    fill.*
1003    from allergy_concept_fill fill
1004),
1005
1006clarity_allergy as -- used to determine what allergen_ids are actually used
1007                   -- in practice this reduces the total number by an order of
1008                   -- magnitude
1009(select distinct allergen_id from clarity.allergy@id),
1010
1011clarity_cl_elg as
1012(select distinct elg.allergen_id,
1013        elg.allergen_name,
1014        (case when elg.allergen_type_c is null
1015              then -1 --sometimes the allergen_type_c is null
1016                      --in these cases we want to put allergen in the
1017                      --'Unclassified' bucket
1018              else elg.allergen_type_c
1019         end) allergen_type_c
1020 from clarity.cl_elg@id elg
1021 join clarity_allergy alg on elg.allergen_id = alg.allergen_id),
1022
1023
1024clarity_zc_allergen_type as
1025(select zat.allergen_type_c,
1026        zat.name
1027 from clarity.zc_allergen_type@id zat union
1028 select -1 as allergen_type_c, --Make an 'Unclassified' bucket for the allergen types
1029        'Unclassified' as name
1030 from dual),
1031
1032base_allergen_type as
1033(select bar.concept_path || alltype.allergen_type_c ||'\' as concept_path,
1034        alltype.name as name_char,
1035        fill.*
1036 from clarity_zc_allergen_type alltype,
1037      base_allergy_reaction bar,
1038      allergy_concept_fill fill),
1039
1040type_needing_alpha as
1041(select 8 as allergen_type_c from dual union --buckets 8 and 9 (Drug Ingredients
1042                                             --and Drugs, respectively) have
1043                                             --nearly 2000 associated concepts in
1044                                             --production.  These need to be further
1045                                             --categorized (TODO standard ontology)
1046                                             --but for now they will be
1047                                             --broken up alphabetically.
1048 select 9 as allergen_type_c from dual),
1049
1050allergy_concepts_non_alpha as --concepts that are small enough buckets that
1051                              --they do not need broken up alphabetically
1052                              --TODO this code will need to be refactored when we move
1053                              --to a standard ontology
1054(select bar.concept_path || elg.allergen_type_c || '\' || elg.allergen_id || '\' as concept_path,
1055        elg.allergen_name as name_char,
1056        'KUH|ALLERGEN:'||elg.allergen_id,
1057        'LA' as va       
1058 from clarity_cl_elg elg,
1059      base_allergy_reaction bar
1060 where elg.allergen_type_c not in (select * from type_needing_alpha)),
1061
1062first_char_grouping as --derive alphabetic buckets
1063(select distinct substr(allergen_name,1,1) as first_char
1064 from clarity_cl_elg elg
1065 where elg.allergen_type_c in (select * from type_needing_alpha)),
1066
1067allergy_alpha_concepts as --make the alphabetic concept folders
1068(select bar.concept_path||tna.allergen_type_c||'\'||fcg.first_char||'\' as concept_path,
1069        fcg.first_char as name,
1070        fill.* 
1071 from base_allergy_reaction bar,
1072      type_needing_alpha tna,
1073      first_char_grouping fcg,
1074      allergy_concept_fill fill),
1075     
1076allergy_concepts_with_alpha as --make the allergens in the alphabetic buckets
1077(select bar.concept_path||elg.allergen_type_c||'\'||fcg.first_char||'\'|| elg.allergen_id ||'\' as concept_path,
1078        elg.allergen_name as name_char,
1079        'KUH|ALLERGEN:'||elg.allergen_id,
1080        'LA' as va 
1081 from base_allergy_reaction bar,
1082      clarity_cl_elg elg
1083      join first_char_grouping fcg on elg.allergen_name like ''||fcg.first_char||'%'
1084 where elg.allergen_type_c in (select * from type_needing_alpha)),
1085
1086all_allergy_concepts as
1087(select * from base_allergy_reaction union all
1088 select * from base_allergen_type union all
1089 select * from allergy_concepts_non_alpha union all
1090 select * from allergy_alpha_concepts union all
1091 select * from allergy_concepts_with_alpha)
1092
1093select length(aac.concept_path) - length(replace(aac.concept_path, '\', '')) - 2 as c_hlevel,
1094       aac.concept_path as c_fullname,
1095       aac.name_char as c_name,
1096       'N' as c_synonym_cd,
1097       aac.va as c_visualattributes,
1098       aac.concept_cd as c_basecode,
1099       'CONCEPT_CD' as c_facttablecolumn,
1100       'concept_dimension' as c_tablename,
1101       'CONCEPT_PATH' as c_columnname,
1102       'T' as c_columndatatype,
1103       'LIKE' c_operator,
1104       aac.concept_path as c_dimcode,
1105       null as c_tooltip,
1106       '@' as m_applied_path,
1107       sysdate as update_date,
1108       sysdate as import_date,
1109       aud.source_cd as sourcesystem_cd
1110       
1111  from all_allergy_concepts aac, epic_audit_info aud
1112  order by c_fullname
1113  ;
1114 
1115
1116/***************
1117 * Allergies / Reactions Modifier
1118 */
1119insert into BlueHeronMetadata.epic_terms (
1120  c_hlevel, c_fullname, c_name, c_synonym_cd,
1121  c_visualattributes, c_basecode, c_facttablecolumn,
1122  c_tablename, c_columnname, c_columndatatype, c_operator,
1123  c_dimcode, c_tooltip, m_applied_path, update_date,
1124  import_date, sourcesystem_cd )
1125
1126with base_reaction as
1127(select '\Reaction\' as modifier_path
1128      , 'Reaction' as name_char
1129      , 'DA' as va
1130      , null as modifier_cd
1131 from dual),
1132
1133base_has_reaction as
1134(select modifier_path||'Documented\' as modifier_path
1135      , 'Reaction List' as name_char
1136      , 'DA' as va
1137      , null as modifier_cd
1138 from base_reaction),
1139
1140 
1141base_no_reaction as
1142(select modifier_path||'Not Documented\' as modifier_path
1143      , 'Reaction not Documented' as name_char
1144      , 'RA' as va
1145      , 'Allergy|Reaction:na' as modifier_cd
1146 from base_reaction),
1147
1148clarity_zc_reaction as
1149(select to_char(zr.reaction_c) reaction_c, zr.name as name
1150 from clarity.zc_reaction@id zr
1151 ),
1152
1153reaction as
1154(select bhr.modifier_path||zr.reaction_c||'\' as modifier_path
1155      , zr.name as name_char
1156      , 'RA' as va
1157      , 'Allergy|Reaction:'||zr.reaction_c as modifier_cd
1158 from base_has_reaction bhr,
1159      clarity_zc_reaction zr
1160      ),
1161 
1162reaction_all as
1163(select * from base_reaction union all
1164 select * from base_has_reaction union all
1165 select * from base_no_reaction union all
1166 select * from reaction)
1167
1168select length(ra.modifier_path) - length(replace(ra.modifier_path, '\', '')) - 1 as c_hlevel,
1169       ra.modifier_path as c_fullname,
1170       ra.name_char as c_name,
1171       'N' as c_synonym_cd,
1172       ra.va as c_visualattributes,
1173       ra.modifier_cd as c_basecode,
1174       'MODIFIER_CD' as c_facttablecolumn,
1175       'MODIFIER_DIMENSION' as c_tablename,
1176       'MODIFIER_PATH' as c_columnname,
1177        'T' as c_columndatatype,
1178       'LIKE' as c_operator,
1179       ra.modifier_path as c_dimcode,
1180       ra.name_char as c_tooltip,
1181       '\i2b2\Allergy\%' as m_applied_path,
1182       sysdate as update_date,
1183       sysdate as import_date,
1184       aud.source_cd as sourcesystem_cd
1185       
1186  from reaction_all ra, epic_audit_info aud
1187;
1188
1189
1190/***************
1191 * Family History Diagnosis Concepts
1192 */
1193 
1194insert into BlueHeronMetadata.epic_terms (
1195  c_hlevel, c_fullname, c_name, c_synonym_cd,
1196  c_visualattributes, c_basecode, c_facttablecolumn,
1197  c_tablename, c_columnname, c_columndatatype, c_operator,
1198  c_dimcode, c_tooltip, m_applied_path, update_date,
1199  import_date, sourcesystem_cd )
1200
1201/* Create a template to fill in empty modifier info */
1202with
1203
1204family_hx_concept_fill
1205  as (select to_char(null) as concept_cd, 'FA' as va from dual),
1206
1207/* zc_medical_hx has the potential for duplicate names for diagnoses, find them */
1208find_duplicate_names
1209  as (select count(*) as duplicate, zcmedhx.name
1210      from clarity.zc_medical_hx@id zcmedhx group by zcmedhx.name having count(*) > 1),
1211
1212/* if a duplicate name is found concatenate the name with its medical_hx_c
1213   for uniqueness */ 
1214hash_duplicate_names
1215  as (select zcmedhx.medical_hx_c,
1216             (case when find_duplicate_names.name is not null
1217                   then zcmedhx.name ||'('||zcmedhx.medical_hx_c||')'
1218                   else zcmedhx.name
1219              end) as hash_name
1220      from clarity.zc_medical_hx@id zcmedhx
1221           left join find_duplicate_names
1222           on zcmedhx.name = find_duplicate_names.name),
1223
1224base_history
1225  as (select '\i2b2\History\' as concept_path,
1226    'History' as name_char,
1227    fill.*
1228    from dual, family_hx_concept_fill fill
1229  order by name_char),
1230
1231
1232
1233base_family_history_diag
1234  as (select '\i2b2\History\Family History Diagnosis\' as concept_path,         'Family History Diagnosis' as name_char, fill.* from dual, family_hx_concept_fill fill union
1235      select '\i2b2\History\Family History Diagnosis\Allergy\',                 'Allergy', fill.* from dual, family_hx_concept_fill fill union
1236      select '\i2b2\History\Family History Diagnosis\Cancer and Tumor\',        'Cancer and Tumor', fill.* from dual, family_hx_concept_fill fill union
1237      select '\i2b2\History\Family History Diagnosis\Circulatory System\',      'Circulatory System', fill.* from dual, family_hx_concept_fill fill union
1238      select '\i2b2\History\Family History Diagnosis\Blood and Blood-forming Organs\', 'Blood and Blood-forming Organs', fill.* from dual, family_hx_concept_fill fill union
1239      select '\i2b2\History\Family History Diagnosis\Death\',                   'Death', fill.* from dual, family_hx_concept_fill fill union
1240      select '\i2b2\History\Family History Diagnosis\Mental, Behavioral and Neurodevelopmental Disorders\', 'Mental, Behavioral and Neurodevelopmental Disorders', fill.* from dual, family_hx_concept_fill fill union
1241      select '\i2b2\History\Family History Diagnosis\Digestive System\',        'Digestive System', fill.* from dual, family_hx_concept_fill fill union
1242      select '\i2b2\History\Family History Diagnosis\Congenital anomolies\',    'Congenital anomolies', fill.* from dual, family_hx_concept_fill fill union
1243      select '\i2b2\History\Family History Diagnosis\Endocrine nutitional and metabolic diseases, and immunity disorders\','Endocrine nutitional and metabolic diseases, and immunity disorders', fill.* from dual, family_hx_concept_fill fill union
1244      select '\i2b2\History\Family History Diagnosis\Genitourinary System\',    'Genitourinary System', fill.* from dual, family_hx_concept_fill fill union
1245      select '\i2b2\History\Family History Diagnosis\Infectious and parasitic diseases\','Infectious and parasitic diseases', fill.* from dual, family_hx_concept_fill fill union
1246      select '\i2b2\History\Family History Diagnosis\Musculoskeletal system and connective tissue\','Musculoskeletal system and connective tissue', fill.* from dual, family_hx_concept_fill fill union
1247      select '\i2b2\History\Family History Diagnosis\Nervous system and sense organs\','Nervous system and sense organs', fill.* from dual, family_hx_concept_fill fill union
1248      select '\i2b2\History\Family History Diagnosis\Respiratory System\',      'Respiratory System', fill.* from dual, family_hx_concept_fill fill union
1249      select '\i2b2\History\Family History Diagnosis\Skin and subcutaneous tissue\','Skin and subcutaneous tissue', fill.* from dual, family_hx_concept_fill fill union
1250      select '\i2b2\History\Family History Diagnosis\General Family History\',  'General Family History', fill.* from dual, family_hx_concept_fill fill
1251      ),
1252
1253/* extract and annotate */
1254extract_family_hx_diag
1255  as (
1256  select '\i2b2\History\Family History Diagnosis\'||
1257    (case
1258        when regexp_like (hash_duplicate_names.hash_name, 'allerg', 'i')
1259        then 'Allergy\'
1260        when regexp_like (hash_duplicate_names.hash_name,
1261                          'cancer|malignant hyperthermia|carcinoma|neoplasm|melanoma|tumor|fibroids|colon polyps|lipoma', 'i')
1262        then 'Cancer and Tumor\'
1263        when regexp_like (hash_duplicate_names.hash_name,
1264                          'circul|pulmonary|heart|cardi|coronary|stroke|tension|aneur|atrial|aortic|arrhythmia|dvt|dysrhythmia|pacemaker|paroxysmal|sick sinus', 'i')
1265        then 'Circulatory System\'
1266        when regexp_like (hash_duplicate_names.hash_name,
1267                          'blood|bleed|anemia|clot|pseudochol|sickle|splenomegaly', 'i')
1268        then 'Blood and Blood-forming Organs\'
1269        when regexp_like (hash_duplicate_names.hash_name,
1270                          'death', 'i')
1271        then 'Death\'
1272        when regexp_like (hash_duplicate_names.hash_name,
1273                          'anxiety|^add|alcohol|abuse|autism|mental|bipolar|learning|OCD|suicide|Schizophrenia|psychiatry|depression|anorexia|bulimia|intellectual|Dementia|sleep disorder|^Tics|Tobacco disorder|Panic disorder', 'i')
1274        then 'Mental, Behavioral and Neurodevelopmental Disorders\'
1275        when regexp_like (hash_duplicate_names.hash_name,
1276                          'digestive|bowel|esophagitis|divert|celiac|cholelithiasis|cirrhosis|crohn|^gi($| )|ulcerative|pancreatitis|liver disease|lactose intolerance|ileus|collagen|aphthous|anal fissure', 'i')
1277        then 'Digestive System\'
1278        when regexp_like (hash_duplicate_names.hash_name,
1279                          'birth defect|cleft|cystic kidney disease|down.*syndrome', 'i')
1280        then 'Congenital anomolies\'
1281        when regexp_like (hash_duplicate_names.hash_name,
1282                          'cystic fibrosis|dehydration|diabetes|lipids|goiter|gout|hemochromatosis|cholesterol|hyperkalemia|Hyperlipidemia|Hypernatremia|thyroid|Hypoglycemia|Obesity|Protein C Deficiency|Puberty|Wilson', 'i')
1283        then 'Endocrine nutitional and metabolic diseases, and immunity disorders\'
1284        when regexp_like (hash_duplicate_names.hash_name,
1285                          'Cystitis|Cystocoele|rectocoele|DUB|Dysmenorrhea|Dyspareunia|Endometriosis|Epididymitis/orchitis|^Genit|Fibrocystic|Breast Disease|Galactorrhea|Hydrocele|Impotence|Infertility|Kidney Disease|Kidney Failure|Kidney Stones|Menopause|PROLAPSE|Pyelonephritis|Renal problems|Undescended Testes|^Urol|Amenorrhea|BPH|Balanitis|bell.*palsy|Breast lump|Cervical Polyp|Cervicitis|Pelvic Inflammatory Disease|Prostatitis', 'i')
1286        then 'Genitourinary System\'
1287        when regexp_like (hash_duplicate_names.hash_name,
1288                          'Giardiasis|Angioedema|Candidiasis|Chicken Pox|Fever|Moliuscum|Pinworms|Infections|Sarcoidosis|Scabies|Thrush|Tinea|Trichomoniasis|Tuberculosis|Onychomycosis|Viral|Fifth|Scarlatina', 'i')
1289        then 'Infectious and parasitic diseases\'
1290        when regexp_like (hash_duplicate_names.hash_name,
1291                          'Chondromalacia|Costochondritis|walking|lumbar|Muscle|Osteopenia|Osteoporosis|Ankylosing Spondylitis|Arthralgia|Arthritis|Arthropathy|Back pain|Bunion|Bursitis|Fibromyalgia|Hip|Kyphosis/scoliosis|Polymyalgia rheumatica|Scleroderma|Short Stature|Sprain|jaw|Tendinitis|Tennis elbow|Tenosynovitis|Baker|Plantar fasciitis|Scoliosis|Joint', 'i')
1292        then 'Musculoskeletal system and connective tissue\'
1293        when regexp_like (hash_duplicate_names.hash_name,
1294                          'Chorea|Conjunctivitis|Epilepsy|Glaucoma|Hearing Loss|Huntington|Labyrinthitis|Macular Degen|Migraines|Motor Neuron|Multiple sclerosis|Neurologic|Neuropathy|Otitis|Parkinson|Restless legs|Retinal Detachment|Seizures|Speech|Visual|Hearing Problems|Strabismus|Vision Loss|Alzheimer|Bartholin|Blindness|Carpal tunnel|Cataract|Cerebral Palsy|Chalazion|Childhood hearing loss|Corneal ulcer|Deafness|Dysfunct eustachian tube|Amblyopia', 'i')
1295        then 'Nervous system and sense organs\'
1296        when regexp_like (hash_duplicate_names.hash_name,
1297                          'Bronch|Croup|Pleurisy|Recurrent URI|Rhinitis|Septal Deviation|Sinusitis|Tonsillitis', 'i')
1298        then 'Respiratory System\'
1299        when regexp_like (hash_duplicate_names.hash_name,
1300                          'Acne|Actinic keratosis|Alopecia|Cellulitis|Decubitus ulcer|Eczema|Erythema nodosum|FOLLICULITIS|Hirsutism|Hyperhidrosis|Impetigo|Nevus|Photo Dermatitis|Psoriasis|Rashes|Roseacea|Sweat Gland Disease|Urticaria', 'i')
1301        then 'Skin and subcutaneous tissue\'
1302        else 'General Family History\'
1303     end)
1304    || hash_duplicate_names.hash_name ||'\' as concept_path      ,
1305  hash_duplicate_names.hash_name as name_char                    ,
1306  'KUMC|FAMILYHISTORYDIAG:'||zcmedhx.medical_hx_c as concept_cd  ,
1307  'LA' as va
1308  from clarity.zc_medical_hx@id zcmedhx
1309     join hash_duplicate_names
1310     on zcmedhx.medical_hx_c = hash_duplicate_names.medical_hx_c),
1311
1312merge_of_family_hx
1313  as (select * from base_history union --TODO remove after merge with social history
1314      select * from base_family_history_diag union
1315      select * from extract_family_hx_diag)
1316 
1317select length(efhd.concept_path) - length(replace(efhd.concept_path, '\', '')) - 2 as c_hlevel,
1318       efhd.concept_path as c_fullname,
1319       efhd.name_char as c_name,
1320       'N' as c_synonym_cd,
1321       efhd.va as c_visualattributes,
1322       efhd.concept_cd as c_basecode,
1323       'CONCEPT_CD' as c_facttablecolumn,
1324       'CONCEPT_DIMENSION' as c_tablename,
1325       'CONCEPT_PATH' as c_columnname,
1326       'T' as c_columndatatype,
1327       'LIKE' c_operator,
1328       efhd.concept_path as c_dimcode,
1329       null as c_tooltip,
1330       '@' as m_applied_path,
1331       sysdate as update_date,
1332       sysdate as import_date,
1333       aud.source_cd as sourcesystem_cd
1334       
1335  from merge_of_family_hx efhd, epic_audit_info aud;
1336 
1337 
1338/***************
1339 * Family History Diagnosis Modifiers
1340 */
1341--Create a view for the modifier dimension - Family Members
1342insert into BlueHeronMetadata.epic_terms (
1343  c_hlevel, c_fullname, c_name, c_synonym_cd,
1344  c_visualattributes, c_basecode, c_facttablecolumn,
1345  c_tablename, c_columnname, c_columndatatype, c_operator,
1346  c_dimcode, c_tooltip, m_applied_path, update_date,
1347  import_date, sourcesystem_cd )
1348 
1349-- Create a templated to fill in empty modifier info
1350with
1351family_hx_modifier_fill
1352  as (select null as modifier_cd, null as modifier_blob, 'DA' as va from dual),
1353
1354-- Create the extracted and annotated version of the family relations
1355extract_family_relations
1356  as (
1357 
1358  -- Add in branches for family relation modifiers
1359  select '\Family History\' as modifier_path, 'Family History' as name_char, fill.* from dual, family_hx_modifier_fill fill
1360  union
1361  select '\Family History\Unspecified Rel\',  'Unspecified Relation',        'FamRelation:null' as modifier_cd, null as modifier_blob, 'RA' as va from dual
1362  union
1363  select '\Family History\Parent\',           'Parent',                      fill.* from dual, family_hx_modifier_fill fill
1364  union
1365  select '\Family History\GrandParent\',      'GrandParent',                 fill.* from dual, family_hx_modifier_fill fill
1366  union
1367  select '\Family History\Sibling\',          'Sibling',                     fill.* from dual, family_hx_modifier_fill fill
1368  union
1369  select '\Family History\Extended Family\',  'Extended Family',             fill.* from dual, family_hx_modifier_fill fill
1370  union
1371  select
1372      (case
1373        when rel.name like 'Neg Hx' then '\No Family History\'
1374        else '\Family History\' ||
1375          (case
1376             when regexp_like (rel.name, '^Father|^Mother') then 'Parent\'
1377             when regexp_like (rel.name, 'Grand[mf]') then 'GrandParent\'
1378             when regexp_like (rel.name, '^Son|^Daughter') then 'Child\'
1379             when regexp_like (rel.name, 'Sister|Brother') then 'Sibling\'
1380             when regexp_like (rel.name, 'Aunt|Uncle|Cousin') then 'Extended Family\'
1381           end) || rel.name || '\'
1382      end)
1383     
1384        as modifier_path                                 ,
1385    rel.name as name_char                                ,
1386    'FamRelation:'|| rel.msg_caller_rel_c as modifier_cd ,
1387    rel.abbr as modifier_blob,
1388    -- Identify modifiers as leaves (with the exception of "Child" which is a branch)
1389    (case when regexp_like(rel.name, '^Child') then 'DA' else 'RA' end) as va
1390   
1391  from clarity.zc_msg_caller_rel@id rel)
1392 
1393select length(efr.modifier_path) - length(replace(efr.modifier_path, '\', '')) - 1 as c_hlevel,
1394       efr.modifier_path as c_fullname,
1395       efr.name_char as c_name,
1396       'N' as c_synonym_cd,
1397       efr.va as c_visualattributes,
1398       efr.modifier_cd as c_basecode,
1399       'MODIFIER_CD' as c_facttablecolumn,
1400       'MODIFIER_DIMENSION' as c_tablename,
1401       'MODIFIER_PATH' as c_columnname,
1402        'T' as c_columndatatype,
1403       'LIKE' as c_operator,
1404       efr.modifier_path as c_dimcode,
1405       efr.name_char as c_tooltip,
1406       '\i2b2\History\Family History Diagnosis\%' as m_applied_path,
1407       sysdate as update_date,
1408       sysdate as import_date,
1409       aud.source_cd as sourcesystem_cd
1410       
1411  from extract_family_relations efr, epic_audit_info aud;
1412
1413
1414/***************
1415 * Lab concepts
1416 */
1417
1418create or replace view kuh_lab_scheme as
1419select * from BlueHeronMetadata.schemes
1420where c_name = 'KUH|COMPONENT_ID';
1421
1422
1423update local_labs
1424set category = 'Other'
1425where category is null;
1426
1427create or replace view cardiology_labtypes as
1428select * from local_labs
1429where category in('Device Check','Echo','Imaging','Nuclear Cardiology','Vascular Ultrasound');
1430
1431delete from BlueHeronMetadata.epic_terms
1432where c_fullname like '\i2b2\Labtests\_%' or c_fullname like '\i2b2\CardioLabtests\_%';
1433
1434insert into BlueHeronMetadata.epic_terms (
1435  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
1436  c_visualattributes,c_tooltip, c_metadataxml,
1437  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
1438  c_operator, m_applied_path,
1439  update_date, import_date, sourcesystem_cd)
1440with
1441non_cardiology_labtypes as (
1442  select distinct category from local_labs
1443  where category not in (select category from cardiology_labtypes)
1444  ),
1445lab_info as (
1446  select
1447     '\i2b2\Labtests\KUH\' c_fullname
1448     , '(Hidden) Base KUH Lab Concept' c_name
1449     , '\i2b2\Labtests\KUH\' c_dimcode
1450  from dual
1451  ),
1452cardio_lab_info as (
1453  select
1454     '\i2b2\CardioLabtests\' as c_fullname
1455     , 'Cardiology Lab Results' as c_name
1456     , '\i2b2\CardioLabtests\' as c_dimcode
1457  from dual
1458  ),
1459combined_lab_types as (
1460  select * from lab_info
1461  union all
1462  select * from cardio_lab_info
1463  ),
1464/* Map which concept paths should contain which categories */
1465path_to_cat as (
1466  select distinct c_fullname, category
1467  from lab_info, (select category from non_cardiology_labtypes)
1468  union all
1469  select distinct c_fullname, category
1470  from cardio_lab_info, (select category from cardiology_labtypes)
1471  )
1472select 1 c_hlevel
1473     , c_fullname
1474     , c_name
1475     , null as c_basecode
1476     , c_dimcode
1477     /* Special case hidden level for non-cardio labs */
1478     , case when c_fullname = (select c_fullname from lab_info) then 'FH' else 'FA' end c_visualattributes
1479     , null as c_tooltip
1480     , null as c_metadataxml
1481     , norm.*
1482     , sysdate as update_date, sysdate as import_date, aud.source_cd sourcesystem_cd
1483     from blueheronmetadata.normal_concept norm, epic_audit_info aud, combined_lab_types
1484union all
1485select 1 + l
1486     , ptc.c_fullname || concept_path as c_fullname
1487     , name_char as c_name
1488     , concept_cd as c_basecode
1489     , c_fullname || concept_path as c_dimcode
1490     , c_visualattributes
1491     , null as tooltip
1492     , c_metadataxml
1493     , norm.*
1494     , sysdate as update_date, sysdate as import_Date, aud.source_cd sourcesystem_cd
1495from BlueHeronMetadata.normal_concept norm, epic_audit_info aud, (
1496  select 1 as l
1497       , ll.category || '\' as concept_path
1498       , ll.category || ' (KUH)' as name_char
1499       , null as concept_cd
1500       , 'FA' as c_visualattributes
1501       , null as c_metadataxml
1502       , ll.category
1503  from (select distinct category from local_labs, combined_lab_types) ll
1504
1505  union all
1506 
1507  select 2 as l
1508       , ll.category || '\' || sub_category || '\' as concept_path
1509       , ll.sub_category as name_char
1510       , null as concept_cd
1511       , 'FA' as c_visualattributes
1512       , null as c_metadataxml
1513       , ll.category
1514  from (select distinct category, sub_category from local_labs) ll
1515
1516  union all
1517 
1518  select 3 as l
1519       , ll.category || '\' || ll.sub_category || '\' || ll.id
1520         || ': ' || ll.description || '\' as concept_path
1521       , ll.common_name || ' (#' || ll.id || ')' as name_char
1522       , kuh_lab_scheme.c_key || ll.id as concept_cd
1523       , 'LA' as c_visualattributes
1524       , case when ll.resulttype='PosFloat'
1525         then
1526         '<?xml version="1.0"?>
1527 <ValueMetadata>
1528 <Version>3.02</Version>
1529 <CreationDateTime>'|| sysdate ||'</CreationDateTime>
1530 <TestID>'|| kuh_lab_scheme.c_key || ll.id ||'</TestID>
1531 <TestName>'|| ll.common_name ||'</TestName>
1532 <DataType>PosFloat</DataType>
1533 <CodeType>GRP</CodeType>
1534 <Loinc>' || kuh_lab_scheme.c_key || ll.id || '</Loinc>
1535 <Flagstouse>HL</Flagstouse>
1536 <Oktousevalues>The units in this set of tests are not equivalent,
1537 therefore numeric comparisons between the tests are not possible.</Oktousevalues>
1538 <MaxStringLength></MaxStringLength>
1539 <LowofLowValue></LowofLowValue>
1540 <HighofLowValue></HighofLowValue>
1541 <LowofHighValue></LowofHighValue>
1542 <HighofHighValue></HighofHighValue>
1543 <LowofToxicValue></LowofToxicValue>
1544 <HighofToxicValue></HighofToxicValue>
1545 <EnumValues></EnumValues>
1546 <CommentsDeterminingExclusion>
1547   <Com></Com>
1548 </CommentsDeterminingExclusion>
1549 <UnitValues>
1550   <NormalUnits>Default</NormalUnits>
1551   <ConvertingUnits>
1552     <Units></Units>
1553     <MultiplyingFactor></MultiplyingFactor>
1554   </ConvertingUnits>
1555 </UnitValues>
1556 <Analysis>
1557   <Enums />
1558   <Counts />
1559   <New />
1560 </Analysis>
1561</ValueMetadata>'
1562else ''
1563end, ll.category
1564
1565  from local_labs ll, kuh_lab_scheme
1566  where ll.id in (select distinct componentid from component_whitelist@id)
1567  ) common_labs
1568join path_to_cat ptc on ptc.category = common_labs.category
1569;
1570
1571
1572  /***** Lab Aggregation Modifiers****/
1573insert into BlueHeronMetadata.epic_terms (
1574  c_hlevel, c_fullname, c_name, c_synonym_cd,
1575  c_visualattributes, c_metadataxml, c_basecode, c_facttablecolumn,
1576  c_tablename, c_columnname, c_columndatatype, c_operator,
1577  c_dimcode, c_tooltip, m_applied_path, update_date,
1578  import_date, sourcesystem_cd )
1579
1580with base_labs_agg as
1581(select '\Labs Aggregation\' as modifier_path
1582      , 'Lab Analytical Modifiers' as name_char
1583      , 'DA' as va
1584      , null as modifier_cd
1585      , null as c_metadataxml
1586 from dual),
1587
1588labs_agg_enum as
1589(select 'Last' as agg_key, 'Last Lab Result' as agg_name from dual union all
1590 select 'Median' as agg_key, 'Median Lab Result' as agg_name from dual
1591),
1592
1593labs_agg as
1594(select '\Labs Aggregation\'||agg_key||'\' as modifier_path
1595      , agg_name as name_char
1596      , 'RA' as va
1597      , 'Labs|Aggregate:'||agg_key as modifier_cd
1598      , '<?xml version="1.0"?>
1599 <ValueMetadata>
1600 <Version>3.02</Version>
1601 <CreationDateTime>'|| sysdate ||'</CreationDateTime>
1602 <TestID>'|| 'Labs|Aggregate:'|| agg_key ||'</TestID>
1603 <TestName>'|| agg_name ||'</TestName>
1604 <DataType>PosFloat</DataType>
1605 <CodeType>GRP</CodeType>
1606 <Flagstouse>HL</Flagstouse>
1607 <Oktousevalues>The units in this set of tests are not equivalent,
1608 therefore numeric comparisons between the tests are not possible.</Oktousevalues>
1609 <MaxStringLength></MaxStringLength>
1610 <LowofLowValue></LowofLowValue>
1611 <HighofLowValue></HighofLowValue>
1612 <LowofHighValue></LowofHighValue>
1613 <HighofHighValue></HighofHighValue>
1614 <LowofToxicValue></LowofToxicValue>
1615 <HighofToxicValue></HighofToxicValue>
1616 <EnumValues></EnumValues>
1617 <CommentsDeterminingExclusion>
1618   <Com></Com>
1619 </CommentsDeterminingExclusion>
1620 <UnitValues>
1621   <NormalUnits>Default</NormalUnits>
1622   <ConvertingUnits>
1623     <Units></Units>
1624     <MultiplyingFactor></MultiplyingFactor>
1625   </ConvertingUnits>
1626 </UnitValues>
1627 <Analysis>
1628   <Enums />
1629   <Counts />
1630   <New />
1631 </Analysis>
1632</ValueMetadata>' as c_metadataxml
1633 from labs_agg_enum),
1634 
1635labs_agg_all as
1636(select * from base_labs_agg union all
1637 select * from labs_agg)
1638
1639select length(la.modifier_path) - length(replace(la.modifier_path, '\', '')) - 1 as c_hlevel,
1640       la.modifier_path as c_fullname,
1641       la.name_char as c_name,
1642       'N' as c_synonym_cd,
1643       la.va as c_visualattributes,
1644       la.c_metadataxml as c_metadataxml,
1645       la.modifier_cd as c_basecode,
1646       'MODIFIER_CD' as c_facttablecolumn,
1647       'MODIFIER_DIMENSION' as c_tablename,
1648       'MODIFIER_PATH' as c_columnname,
1649        'T' as c_columndatatype,
1650       'LIKE' as c_operator,
1651       la.modifier_path as c_dimcode,
1652       la.name_char as c_tooltip,
1653       '\i2b2\Labtests\KUH\%' as m_applied_path,
1654       sysdate as update_date,
1655       sysdate as import_date,
1656       aud.source_cd as sourcesystem_cd
1657       
1658  from labs_agg_all la, epic_audit_info aud;
1659
1660
1661
1662
1663-- Build the dose units
1664create or replace view dose_paths as select * from(
1665  with
1666  dose_level_one as (
1667    select
1668      'Cumulative Daily Dose of Single Order' path_name, 'Cumulative Daily Dose of Single Order' disp_name, null modifier_cd,
1669      null data_type, null unit_xml, 'MedObs:Dose' mod_prefix
1670    from dual
1671    union all
1672    select
1673      'Medication Administration Record (MAR) Dose' path_name, 'Medication Administration Record (MAR) Dose' disp_name, null modifier_cd,
1674      null data_type, null unit_xml, 'MedObs:MAR_Dose' mod_prefix
1675    from dual
1676    ),
1677  dose_levels as (
1678  select * from dose_level_one
1679  union all
1680
1681  /*****************************************************************************
1682  To add new dose values, just add them below:
1683  lvl = level (should be 2)
1684  path_name = full path entry for the c_fullname
1685  dsp_name = value for c_name
1686  data_type = data type string for the c_metadataxml
1687  unit_xml = c_metadataxml piece that describes unit conversion
1688 
1689  c_metadataxml documentation:
1690  https://community.i2b2.org/wiki/display/DevForum/Metadata+XML+for+Medication+Modifiers
1691  *****************************************************************************/
1692
1693  select
1694    'mg' path_name,
1695    'Dose Unit: mcg/mg/g' disp_name,
1696    m.mod_prefix || '|mg' modifier_cd,
1697    'PosFloat' data_type,
1698    '<NormalUnits>mg</NormalUnits>
1699     <EqualUnits>g</EqualUnits>
1700     <EqualUnits>mcg</EqualUnits>
1701     <ConvertingUnits>
1702       <Units>g</Units>
1703       <MultiplyingFactor>1000</MultiplyingFactor>
1704     </ConvertingUnits>
1705     <ConvertingUnits>
1706       <Units>mcg</Units>
1707       <MultiplyingFactor>0.001</MultiplyingFactor>
1708     </ConvertingUnits>' unit_xml,
1709    m.mod_prefix
1710  from dose_level_one m
1711
1712  union
1713
1714  select
1715    'tab' path_name,
1716    'Dose Unit: tab' disp_name,
1717    m.mod_prefix || '|tab' modifier_cd,
1718    'PosInteger' data_type,
1719    '<NormalUnits>tab</NormalUnits>
1720     <EqualUnits></EqualUnits>
1721     <ConvertingUnits></ConvertingUnits>' unit_xml,
1722    m.mod_prefix
1723  from dose_level_one m
1724
1725  union
1726
1727  select
1728    'l' path_name,
1729    'Dose Unit: ml/l' disp_name,
1730    m.mod_prefix || '|l' modifier_cd,
1731    'PosFloat' data_type,
1732    '<NormalUnits>ml</NormalUnits>
1733     <EqualUnits>l</EqualUnits>
1734     <ConvertingUnits>
1735       <Units>l</Units>
1736       <MultiplyingFactor>1000</MultiplyingFactor>
1737     </ConvertingUnits>' unit_xml,
1738    m.mod_prefix
1739  from dose_level_one m
1740
1741  union
1742 
1743  select
1744    'units' path_name,
1745    'Dose Unit: units' disp_name,
1746    m.mod_prefix || '|units' modifier_cd,
1747    'PosFloat' data_type,
1748    '<NormalUnits>units</NormalUnits>
1749     <EqualUnits></EqualUnits>
1750     <ConvertingUnits></ConvertingUnits>' unit_xml,
1751    m.mod_prefix
1752  from dose_level_one m 
1753
1754  union
1755 
1756  select
1757    'meq' path_name,
1758    'Dose Unit: milliequivalents (mEq)' disp_name,
1759    m.mod_prefix || '|meq' modifier_cd,
1760    'PosFloat' data_type,
1761    '<NormalUnits>meq</NormalUnits>
1762     <EqualUnits></EqualUnits>
1763     <ConvertingUnits></ConvertingUnits>' unit_xml,
1764    m.mod_prefix
1765  from dose_level_one m 
1766
1767  union
1768 
1769  select
1770    'cap' path_name,
1771    'Dose Unit: cap' disp_name,
1772    m.mod_prefix || '|cap' modifier_cd,
1773    'PosInteger' data_type,
1774    '<NormalUnits>cap</NormalUnits>
1775     <EqualUnits></EqualUnits>
1776     <ConvertingUnits></ConvertingUnits>' unit_xml,
1777    m.mod_prefix
1778  from dose_level_one m 
1779
1780  union
1781 
1782  select
1783    'puff' path_name,
1784    'Dose Unit: puff' disp_name,
1785    m.mod_prefix || '|puff' modifier_cd,
1786    'PosInteger' data_type,
1787    '<NormalUnits>puff</NormalUnits>
1788     <EqualUnits></EqualUnits>
1789     <ConvertingUnits></ConvertingUnits>' unit_xml,
1790    m.mod_prefix
1791  from dose_level_one m 
1792
1793  union
1794 
1795  select
1796    'drop' path_name,
1797    'Dose Unit: drop' disp_name,
1798    m.mod_prefix || '|drop' modifier_cd,
1799    'PosInteger' data_type,
1800    '<NormalUnits>drop</NormalUnits>
1801     <EqualUnits></EqualUnits>
1802     <ConvertingUnits></ConvertingUnits>' unit_xml,
1803    m.mod_prefix
1804  from dose_level_one m
1805  ),
1806doses as(
1807  select 'DA' va, null par, dl.* from dose_levels dl where dl.modifier_cd is null
1808  union
1809  /* In order for the SYS_CONNECT_BY_PATH call below to work, we need to fill in
1810  the parent for each modifier.  So, determine the parent/child relationship in
1811  the "where" clause by the modifier_cd being null (parent) or not (child) and
1812  by the modifier prefix.
1813  */ 
1814  select 'RA' va, dla.path_name par, dlb.* from dose_levels dla, dose_levels dlb
1815  where dla.modifier_cd is null and dlb.modifier_cd is not null and dla.mod_prefix = dlb.mod_prefix
1816  )
1817select
1818    level lvl, SYS_CONNECT_BY_PATH(d.path_name, '\') pth, d.disp_name,
1819    d.va, d.modifier_cd, d.data_type, d.unit_xml
1820  from doses d
1821  start with d.par is null
1822  connect by prior d.path_name = d.par
1823);
1824
1825
1826/*
1827Delete from the terms so we can start over.
1828*/
1829delete from
1830  blueheronmetadata.epic_terms
1831where c_tablename = 'MODIFIER_DIMENSION'
1832  and c_fullname like '\Medication\%';
1833
1834/*******************
1835 * Medication Modifiers:
1836 *   - Historical (home) medication orders.
1837 *   - All other medication orders
1838 */
1839insert into BlueHeronMetadata.epic_terms (
1840  c_hlevel, c_fullname, c_name, c_synonym_cd,
1841  c_visualattributes, c_basecode, c_metadataxml, c_facttablecolumn,
1842  c_tablename, c_columnname, c_columndatatype, c_operator,
1843  c_dimcode, c_tooltip, m_applied_path, update_date,
1844  import_date, sourcesystem_cd )
1845select
1846  mt.lvl c_hlevel, '\Medication\' || mt.path c_fullname, mt.c_name, 'N' c_synonym_cd,
1847  mt.va c_visualattributes, mt.c_basecode, mt.c_metadataxml, 'MODIFIER_CD' c_facttablecolumn,
1848  'MODIFIER_DIMENSION' c_tablename, 'MODIFIER_PATH' c_columnname, 'T' c_columndatatype, 'LIKE' c_operator,
1849  '\Medication\' || mt.path c_dimcode, mt.c_name c_tooltip, '\i2b2\Medications\%' m_applied_path, sysdate update_date,
1850  sysdate import_date, aud.source_cd
1851from
1852  (
1853  select 1 as lvl, 'RA' as va, 'Historical\' path, 'Historical Medications' c_name,
1854    'MedObs:Historical' c_basecode, null c_metadataxml
1855  from dual
1856  union all
1857  select 1 as lvl, 'RA' as va, 'Other Orders\' path, 'Other Medication Orders' c_name,
1858    'MedObs:Other' c_basecode, null c_metadataxml
1859  from dual
1860  union all
1861  select 1 as lvl, 'RA' as va, 'Dispensed\' path, 'Dispensed Medications' c_name,
1862    'MedObs:Dispensed' c_basecode, null c_metadataxml 
1863  from dual
1864  union all
1865  select 1 as lvl, 'RA' as va, 'Outpatient\' path, 'Outpatient Medication Orders' c_name,
1866    'MedObs:Outpatient' c_basecode, null c_metadataxml   
1867  from dual
1868  union all
1869  select 1 as lvl, 'RA' as va, 'Inpatient\' path, 'Inpatient Medication Orders' c_name,
1870    'MedObs:Inpatient' c_basecode, null c_metadataxml   
1871  from dual
1872  union all
1873  select 1 as lvl, 'RA' as va, 'PRN Inpatient Order\' path, 'PRN Inpatient Order' c_name,
1874    'MedObs:PRN' c_basecode, null c_metadataxml
1875  from dual
1876  union all
1877  select 1 as lvl, 'DA' as va, 'MAR Result\' path, 'Medication Administration Record (MAR) Result Type' c_name,
1878    null c_basecode, null c_metadataxml
1879  from dual
1880  union all
1881  select 2 as lvl,  'RA' as va, 'MAR Result\' || cmrslt.name || '\' path, cmrslt.name c_name,
1882  'MedObs|MAR:' || cmrslt.name c_basecode, null c_metadataxml
1883  from clarity.zc_mar_rslt@id cmrslt
1884  union all
1885  select
1886    dp.lvl, dp.va, ltrim(dp.pth, '\') || '\' path, dp.disp_name c_name, dp.modifier_cd c_basecode,
1887    case
1888      when dp.va = 'RA' then
1889       '<?xml version="1.0"?>
1890          <ValueMetadata>
1891            <Version>3.02</Version>
1892            <CreationDateTime>09/25/2012 00:00:00</CreationDateTime>' ||
1893             '<TestID>' || dp.modifier_cd || '</TestID>' ||
1894             '<TestName>' || dp.disp_name || '</TestName>' ||
1895             '<DataType>' || dp.data_type || '</DataType>' ||
1896             '<Flagstouse></Flagstouse>
1897              <Oktousevalues>Y</Oktousevalues>
1898              <EnumValues></EnumValues>
1899              <UnitValues>' ||
1900              dp.unit_xml ||
1901             '</UnitValues>
1902          </ValueMetadata>'
1903    else null end c_metadataxml
1904  from dose_paths dp
1905  ) mt, epic_audit_info aud;
1906
1907
1908/*******************
1909 * Flowsheet Concepts
1910 */
1911
1912insert into BlueHeronMetadata.epic_terms (
1913  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
1914  c_visualattributes, c_metadataxml,
1915  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
1916  c_operator, m_applied_path,
1917  update_date, import_date, sourcesystem_cd,c_tooltip)
1918select depth
1919     , concept_path
1920     , name_char
1921     , concept_cd
1922     , concept_path
1923     , visualattributes
1924     , c_metadataxml
1925     , norm.*
1926     , sysdate
1927     , sysdate
1928     , aud.source_cd
1929     , null
1930from kuh_flowsheet_concept@id, epic_audit_info aud
1931   , BlueHeronMetadata.normal_concept norm;
1932-- 239,043 rows inserted.
1933-- Elapsed: 00:00:53.026
1934-- select * from BlueHeronMetadata.i2b2 where c_fullname like '\i2b2\Flowsheet\%';
1935-- delete from BlueHeronMetadata.i2b2 where c_fullname like '\i2b2\Flowsheet\%';
1936
1937
1938/* finding flowsheet measures in the hierarchy:
1939
1940select super.*
1941from BlueHeronMetadata.i2b2 super,
1942BlueHeronMetadata.i2b2 sub
1943where sub.c_basecode = 'KUH|FLO_MEAS_ID:1108'
1944 and sub.c_fullname like (super.c_fullname || '%')
1945order by super.c_fullname;
1946
1947*/
1948
1949
1950
1951/***************
1952 * Surgical History Procedure Concepts Draft
1953 */
1954insert into BlueHeronMetadata.epic_terms (
1955  c_hlevel, c_fullname, c_name, c_synonym_cd,
1956  c_visualattributes, c_basecode, c_facttablecolumn,
1957  c_tablename, c_columnname, c_columndatatype, c_operator,
1958  c_dimcode, c_tooltip, m_applied_path, update_date,
1959  import_date, sourcesystem_cd )
1960
1961-- Create a template to fill in empty concept info
1962with
1963
1964surgical_hx_procs
1965  as
1966  (select distinct ce.proc_id          as proc_id
1967                 , ce.proc_code        as proc_code
1968                 , ce.proc_name        as proc_name
1969                 , (case when cepci.proc_cat_name is null
1970                         then 'Other'
1971                         else cepci.proc_cat_name
1972                    end) as proc_cat_name
1973
1974   from clarity.clarity_eap@id ce
1975   --only register surgical procedures concepts for which there is at least one
1976   --instance of that proc id in surgical history table.
1977   join clarity.surgical_hx@id shx on ce.proc_id=shx.proc_id
1978   left join clarity.edp_proc_cat_info@id cepci on ce.proc_cat_id = cepci.proc_cat_id),
1979
1980surgical_hx_concept_fill
1981  as (select to_char(null) as concept_cd, 'FA' as va from dual),
1982
1983base_surgical_history_diag
1984  as (select '\i2b2\History\Surgical History\' as concept_path, 'Surgical History (DRAFT)' as name_char, fill.* from surgical_hx_concept_fill fill union all
1985      select distinct '\i2b2\History\Surgical History\'||shc.proc_cat_name||'\' as concept_path
1986                    , shc.proc_cat_name as name_char
1987                    , fill.* from surgical_hx_procs shc, surgical_hx_concept_fill fill where shc.proc_cat_name is not null),
1988
1989-- extract and annotate
1990extract_surgical_hx_procs
1991  as (
1992  select '\i2b2\History\Surgical History\'||proc_cat_name||'\'||proc_name||':'||proc_id||'\' as concept_path     
1993       , proc_name as name_char
1994       , 'KUH|SURG_PROC:'||proc_id as concept_cd
1995       , 'LA' as va
1996  from surgical_hx_procs),
1997
1998merge_of_surgical_hx
1999  as (select * from base_surgical_history_diag union all
2000      select * from extract_surgical_hx_procs)
2001 
2002select length(shx.concept_path) - length(replace(shx.concept_path, '\', '')) - 2 as c_hlevel
2003     , shx.concept_path as c_fullname
2004     , shx.name_char as c_name
2005     , 'N' as c_synonym_cd
2006     , shx.va as c_visualattributes
2007     , shx.concept_cd as c_basecode
2008     , 'CONCEPT_CD' as c_facttablecolumn
2009     , 'CONCEPT_DIMENSION' as c_tablename
2010     , 'CONCEPT_PATH' as c_columnname
2011     , 'T' as c_columndatatype
2012     , 'LIKE' c_operator
2013     , shx.concept_path as c_dimcode
2014     , null as c_tooltip
2015     , '@' as m_applied_path
2016     , sysdate as update_date
2017     , sysdate as import_date
2018     , aud.source_cd as sourcesystem_cd
2019       
2020  from merge_of_surgical_hx shx, epic_audit_info aud;
2021
2022/* This is where we use clarity_eap for the ontology
2023create or replace view proc_orders_conc as
2024select
2025  ce.proc_id
2026, proc_code
2027, substr(proc_code, 0,4) as code_4
2028, ce.proc_name
2029, NVL(ce.proc_cat, 'Miscellaneous') proc_cat
2030, ce.proc_cat_id
2031, ce.short_name
2032, ce.bill_desc
2033, NVL(
2034  case substr(proc_cat, 0,4)
2035  when 'AMB ' then 'AMBULATORY'
2036  when 'CARD' then 'CARDIOLOGY'
2037  when 'DIET' then 'DIET'
2038  when 'HCHG' THEN 'HCHG'
2039  WHEN 'NURS' then 'NURSING'
2040  when 'PR -' then 'PR'
2041  when 'PR- ' then 'PR'
2042  else proc_cat
2043  END, 'MISCELLANEOUS') as broad_category1
2044from
2045clarity.clarity_eap ce
2046where
2047proc_cat IS NOT NULL -- Some proc_names are weird with names of people
2048order by proc_cat
2049;*/
2050
2051/** proc_orders_conc -- build concepts/terms for procedures.
2052
2053Include only procedures that have been ordered atleast once.
2054*/
2055create or replace view proc_orders_conc as
2056  select proc_id
2057  , proc_code
2058  , order_type_c
2059  , order_type_name
2060  , NVL(order_type_name, 'MISCELLANEOUS') as broad_category1
2061  , coalesce(proc_cat, proc_cat_name, order_type_name, 'MISCELLANEOUS') as proc_cat
2062  , proc_name
2063  , proc_cat_id
2064  from (
2065    select distinct op.proc_id
2066      , op.proc_code as proc_code
2067      , op.order_type_c     
2068      , zot.name as order_type_name
2069      , cce.proc_name as proc_name
2070      , cce.proc_cat as proc_cat
2071      , cce.proc_cat_id
2072      , cepc.proc_cat_name
2073      from clarity.order_proc@id op
2074      join
2075      clarity.clarity_eap@id cce
2076      on cce.proc_id=op.proc_id
2077      left join clarity.zc_order_type@id zot
2078      on op.order_type_c=zot.order_type_c
2079      left join clarity.EDP_PROC_CAT_INFO@id cepc
2080      on cce.proc_cat_id=cepc.proc_cat_id
2081    )
2082  order by broad_category1, proc_cat
2083;
2084
2085
2086/*-- To look at how the categories might line up
2087  select count(proc_id), broad_category1, proc_cat from proc_orders_conc
2088  group by broad_category1, proc_cat
2089  order by broad_category1, proc_cat;
2090
2091  select count(proc_id), broad_category1, proc_cat from proc_orders_conc
2092  group by broad_category1, proc_cat
2093  order by broad_category1, proc_cat; 
2094*/
2095-- 6999 rows
2096
2097
2098
2099
2100
2101
2102
2103insert into BLUEHERONMETADATA.epic_terms (
2104  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
2105  c_visualattributes,
2106  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
2107  c_operator,  m_applied_path,
2108  update_date, import_date, sourcesystem_cd
2109)
2110select
2111  c_hlevel, con.concept_path, con.name_char, con.concept_cd, con.concept_path,
2112  c_visualattributes,
2113  norm.*,
2114  sysdate, sysdate, aud.source_cd
2115from (
2116
2117  select 1 as c_hlevel
2118        , '\i2b2\Procorders\' as concept_path
2119        ,null as concept_cd
2120        , 'Procedure Orders (DRAFT)' as name_char
2121        , 'FA' as c_visualattributes --FA for folders
2122  from dual
2123 
2124  union all
2125 
2126   select DISTINCT 2 as c_hlevel
2127        , '\i2b2\Procorders\'||broad_category1||'\' as concept_path
2128        ,null as concept_cd
2129        , broad_category1 as name_char
2130        , 'FA' as c_visualattributes --FA for folders
2131  from proc_orders_conc
2132  where broad_category1 is NOT NULL
2133 
2134   union all
2135   
2136   select DISTINCT 3 as c_hlevel
2137        , '\i2b2\Procorders\'||broad_category1||'\'||proc_cat||'\' as concept_path
2138        ,null as concept_cd
2139        , proc_cat as name_char
2140        , 'FA' as c_visualattributes --FA for folders
2141  from proc_orders_conc
2142  where broad_category1!=proc_cat
2143 
2144  union all
2145   
2146   select DISTINCT 3 as c_hlevel
2147        , '\i2b2\Procorders\'||broad_category1||'\'||proc_id||'\' as concept_path
2148        ,'KUH|PROC_ID:'||proc_id as concept_cd
2149        , proc_name as name_char
2150        , 'LA' as c_visualattributes --FA for folders
2151  from proc_orders_conc
2152  where broad_category1=proc_cat
2153 
2154    union all
2155   
2156   select DISTINCT 4 as c_hlevel
2157        , '\i2b2\Procorders\'||broad_category1||'\'||proc_cat||'\'||proc_id||'\' as concept_path
2158        ,'KUH|PROC_ID:'||proc_id as concept_cd
2159        , proc_name as name_char
2160        , 'LA' as c_visualattributes --FA for folders
2161  from proc_orders_conc
2162  where broad_category1!=proc_cat
2163   
2164  ) con
2165, BlueHeronMetadata.normal_concept norm, epic_audit_info aud;
2166
2167
2168insert into BLUEHERONMETADATA.epic_terms (
2169  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
2170  c_visualattributes,
2171  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
2172  c_operator,  m_applied_path,
2173  update_date, import_date, sourcesystem_cd
2174)
2175select
2176  c_hlevel, con.concept_path, con.name_char, con.concept_cd, con.concept_path,
2177  c_visualattributes,
2178  'N' C_SYNONYM_CD, 'MODIFIER_CD' c_facttablecolumn,
2179  'MODIFIER_DIMENSION' c_tablename, 'MODIFIER_PATH' c_columnname, 'T' c_columndatatype,
2180  'LIKE' c_operator,
2181  '\i2b2\Procorders\%' M_APPLIED_PATH, 
2182  -- m_applied_path is the hierarchy that modifier will be applied to
2183  sysdate, sysdate, aud.source_cd
2184  from (
2185  select 1 as c_hlevel
2186        , '\Procorders\Inpatient\' as concept_path
2187        -- doesn't have to be a real path. Primary key for modifier_dimension
2188         ,'PROCORDERS:Inpatient' as concept_cd
2189        , 'Inpatient Orders' as name_char
2190        , 'RA'  as c_visualattributes
2191  from dual
2192 
2193      union all
2194     
2195  select 1 as c_hlevel
2196        , '\Procorders\Outpatient\' as concept_path
2197         ,'PROCORDERS:Outpatient' as concept_cd
2198        , 'Outpatient Orders' as name_char
2199        , 'RA'  as c_visualattributes
2200  from dual
2201     
2202      union all
2203     
2204  select 1 as c_hlevel
2205        , '\Procorders\Notrecorded\' as concept_path
2206         ,'PROCORDERS:Notrecorded' as concept_cd
2207        , 'Not Recorded' as name_char
2208        , 'RA'  as c_visualattributes
2209  from dual
2210 
2211  ) con, epic_audit_info aud;
2212
2213
2214/*******************
2215 * Orderset Concepts
2216 */
2217/*
2218whenever sqlerror continue;
2219truncate table complete_orderset_tree;
2220drop table complete_orderset_tree;
2221
2222whenever sqlerror exit;
2223 
2224create table complete_orderset_tree as
2225with
2226make_base_order_sets as
2227(select 'FA' as va,
2228  '\i2b2\Ordersets\' as concept_path,
2229  'Order Sets (DRAFT)' as name_char,
2230  null as concept_cd
2231  from dual
2232 union all
2233 select 'FA' as va,
2234  '\i2b2\Ordersets\Order Sets\' as concept_path,
2235  'Order Sets' as name_char,
2236  null as concept_cd
2237  from dual
2238 union all
2239 select 'FA' as va,
2240  '\i2b2\Ordersets\Order Set Hierarchy\' as concept_path,
2241  'Order Set Hierarchy' as name_char,
2242  null as concept_cd
2243  from dual
2244 
2245  ),
2246
2247all_proc_ordersets as
2248(select distinct cosq.orderset_id,
2249                 cosq.orderset_name,
2250                 'P'||cop.proc_id as order_id,
2251                 ceap.proc_name as order_name
2252  from clarity.order_proc@id cop
2253  join clarity.order_proc_3@id cop3 on cop.order_proc_id=cop3.order_id
2254  join clarity.cl_osq cosq@id on cop3.ord_osq_id=cosq.orderset_id
2255  join clarity.order_proc_2@id cop2 on cop.order_proc_id = cop2.order_proc_id
2256  join clarity.clarity_eap@id ceap on cop.proc_id = ceap.proc_id
2257  where cop2.order_source_c=6 and cop.ordering_date is not null
2258  order by cosq.orderset_id
2259  ),
2260 
2261all_med_orders as
2262(select distinct cosq.orderset_id,
2263                 cosq.orderset_name,
2264                 'M'||com.medication_id as order_id,
2265                 cm.name as order_name
2266  from clarity.order_med@id com
2267  join clarity.order_med_2@id com2 on com.order_med_id = com2.order_id
2268  join clarity.cl_osq@id cosq on com2.ord_osq_id = cosq.orderset_id
2269  join clarity.clarity_medication@id cm on com.medication_id = cm.medication_id
2270  ),
2271
2272all_used_orders as
2273(select * from all_proc_ordersets union
2274 select * from all_med_orders
2275),
2276
2277all_used_ordersets as
2278(select distinct orderset_id, orderset_name from all_used_orders
2279),
2280
2281orderset_concepts as
2282(select 'LA' as va
2283      , '\i2b2\Ordersets\Order Sets\'||orderset_id||'\' as concept_path
2284      , orderset_name as name_char
2285      , 'KUH|ORDERSET_ID:'||orderset_id as concept_cd
2286 from all_used_ordersets
2287),
2288
2289orderset_folders as
2290(select 'FA' as va
2291      , '\i2b2\Ordersets\Order Set Hierarchy\'||orderset_id||'\' as concept_path
2292      , orderset_name as name_char
2293      , null as concept_cd
2294 from all_used_ordersets
2295),
2296 
2297order_concepts as
2298(select 'LA' as va
2299      , '\i2b2\Ordersets\Order Set Hierarchy\'||orderset_id||'\'||order_id||'\' as concept_path
2300      , order_name as name_char
2301      , 'KUH|ORDERSET_ID_M_P:'||orderset_id||'/'||order_id as concept_cd
2302 from all_used_orders
2303)
2304 
2305select * from make_base_order_sets union all
2306select * from orderset_concepts union all
2307select * from orderset_folders union all
2308select * from order_concepts
2309;
2310
2311 
2312insert into BlueHeronMetadata.epic_terms (
2313  c_hlevel, c_fullname, c_name, c_synonym_cd,
2314  c_visualattributes, c_basecode, c_metadataxml, c_facttablecolumn,
2315  c_tablename, c_columnname, c_columndatatype, c_operator,
2316  c_dimcode, c_tooltip, m_applied_path, update_date,
2317  import_date, sourcesystem_cd )
2318
2319select length(cot.concept_path) - length(replace(cot.concept_path, '\', '')) - 2 as c_hlevel,
2320       cot.concept_path as c_fullname,
2321       cot.name_char as c_name,
2322       'N' as c_synonym_cd,
2323       cot.va as c_visualattributes,
2324       cot.concept_cd as c_basecode,
2325       null as c_metadataxml,
2326       'CONCEPT_CD' as c_facttablecolumn,
2327       'CONCEPT_DIMENSION' as c_tablename,
2328       'CONCEPT_PATH' as c_columnname,
2329       'T' as c_columndatatype,
2330       'LIKE' c_operator,
2331       cot.concept_path as c_dimcode,
2332       null as c_tooltip,
2333       '@' as m_applied_path,
2334       sysdate as update_date,
2335       sysdate as import_date,
2336       aud.source_cd as sourcesystem_cd
2337       
2338  from complete_orderset_tree cot, epic_audit_info aud
2339;
2340*/
2341
2342
2343/*******************
2344 * Social History Concepts
2345 * (also makes "History" Root)
2346 */
2347
2348--Create a view for the concept dimension - Smoking and Tobacco Use 
2349insert into BlueHeronMetadata.epic_terms (
2350  c_hlevel, c_fullname, c_name, c_synonym_cd,
2351  c_visualattributes, c_basecode, c_metadataxml, c_facttablecolumn,
2352  c_tablename, c_columnname, c_columndatatype, c_operator,
2353  c_dimcode, c_tooltip, m_applied_path, update_date,
2354  import_date, sourcesystem_cd )
2355
2356with
2357social_hx_concept_fill as
2358  (select null as concept_cd, null as concept_blob, null as c_metadataxml from dual),
2359 
2360make_base_social_history as
2361(select
2362    'FA' as va,
2363    '\i2b2\History\Social History\' as concept_path,
2364    'Social History' as name_char,
2365    fill.*
2366    from dual, social_hx_concept_fill fill
2367    ),
2368
2369extract_smoking_tob_use_c as
2370(select
2371  'LA' as va,
2372  '\i2b2\History\Social History\Tobacco Usage\Smoking Tobacco Use\'||extracttab.name||'\' as concept_path,
2373  extracttab.name as name_char,
2374  'KUMC|SMOKING_TOB_USE:'||extracttab.smoking_tob_use_c as concept_cd,
2375  extracttab.internal_id as concept_blob,
2376  null as c_metadataxml
2377  from clarity.ZC_SMOKING_TOB_USE@id extracttab
2378  union select
2379    'FA' as va,
2380    '\i2b2\History\Social History\Tobacco Usage\Smoking Tobacco Use\' as concept_path,
2381    'Smoking Tobacco Use' as name_char,
2382    fill.*
2383    from dual, social_hx_concept_fill fill),
2384
2385smoking_quit_date as
2386(select
2387    'LA' as va,
2388    '\i2b2\History\Social History\Tobacco Usage\Smoking Quit Date\' as concept_path,
2389    'Smoking Quit Date' as name_char,
2390    'KUMC|SMOKING_QUIT_DATE' as concept_cd,
2391    null as concept_blob,
2392    null as c_metadataxml from dual),
2393   
2394extract_smokeless_tob_use_c as
2395(select
2396  'LA' as va,
2397  '\i2b2\History\Social History\Tobacco Usage\Smokeless Tobacco Use\'||extracttab.name||'\' as concept_path,
2398  extracttab.name as name_char,
2399  'KUMC|SMOKELESS_TOB_USE:'||extracttab.smokeless_tob_u_c as concept_cd,
2400  extracttab.internal_id as concept_blob,
2401  null as c_metadataxml
2402  from clarity.ZC_SMOKELESS_TOB_U@id extracttab
2403  union select
2404    'FA' as va,
2405    '\i2b2\History\Social History\Tobacco Usage\Smokeless Tobacco Use\' as concept_path,
2406    'Smokeless Tobacco Use' as name_char,
2407    fill.*
2408    from dual, social_hx_concept_fill fill),
2409
2410smokeless_quit_date as
2411(select
2412    'LA' as va,
2413    '\i2b2\History\Social History\Tobacco Usage\Smokeless Quit Date\' as concept_path,
2414    'Smokeless Quit Date' as name_char,
2415    'KUMC|SMOKELESS_QUIT_DATE' as concept_cd,
2416    null as concept_blob,
2417    null as c_metadataxml from dual),
2418   
2419extract_tobacco_user_c as
2420(select
2421  'LA' as va,
2422  '\i2b2\History\Social History\Tobacco Usage\'||extracttab.name||'\' as concept_path,
2423  extracttab.name as name_char,
2424  'KUMC|TOBACCO_USER:'||extracttab.tobacco_user_c as concept_cd,
2425  extracttab.internal_id as concept_blob,
2426  null as c_metadataxml
2427  from clarity.ZC_TOBACCO_USER@id extracttab
2428  union select
2429    'FA' as va,
2430    '\i2b2\History\Social History\Tobacco Usage\' as concept_path ,
2431    'Tobacco Usage' as name_char,
2432    fill.*
2433    from dual, social_hx_concept_fill fill),
2434
2435pack_per_day as
2436(select
2437    'LA' as va,
2438    '\i2b2\History\Social History\Tobacco Usage\Packs Per Day\' as concept_path,
2439    'Packs Per Day' as name_char,
2440    'KUMC|PACK_PER_DAY' as concept_cd,
2441    null as concept_blob,
2442    '
2443<?xml version="1.0"?>
2444<ValueMetadata>
2445     <Version>3.02</Version>
2446     <CreationDateTime>'|| sysdate ||'</CreationDateTime>
2447     <TestID>SOCHIST:PACKPERDAY</TestID>
2448     <TestName>Packs Per Day</TestName>
2449     <DataType>PosFloat</DataType>
2450     <Flagstouse></Flagstouse>
2451     <Oktousevalues>Y</Oktousevalues>
2452     <EnumValues></EnumValues>
2453     <UnitValues>
2454          <NormalUnits>Packs</NormalUnits>
2455     </UnitValues>
2456</ValueMetadata>
2457    ' as c_metadataxml from dual),
2458   
2459tobacco_used_years as
2460(select
2461    'LA' as va,
2462    '\i2b2\History\Social History\Tobacco Usage\Years of Tobacco Usage\' as concept_path,
2463    'Years of Tobacco Usage' as name_char,
2464    'KUMC|TOBACCO_USED_YEARS' as concept_cd,
2465    null as concept_blob,
2466    '
2467<?xml version="1.0"?>
2468<ValueMetadata>
2469     <Version>3.02</Version>
2470     <CreationDateTime>'|| sysdate ||'</CreationDateTime>
2471     <TestID>SOCHIST:TOBACCO_USED_YEARS</TestID>
2472     <TestName>Years of Tobacco Usage</TestName>
2473     <DataType>PosFloat</DataType>
2474     <Flagstouse></Flagstouse>
2475     <Oktousevalues>Y</Oktousevalues>
2476     <EnumValues></EnumValues>
2477     <UnitValues>
2478          <NormalUnits>Years</NormalUnits>
2479     </UnitValues>
2480</ValueMetadata>
2481    ' as c_metadataxml from dual),
2482   
2483extract_sexual_activity_c as
2484(select
2485  'LA' as va,
2486  '\i2b2\History\Social History\Sexually Active\'||extracttab.name||'\' as concept_path,
2487  extracttab.name as name_char,
2488  'KUMC|SEXUALLY_ACTIVE:'||extracttab.sexually_active_c as concept_cd,
2489  extracttab.internal_id as concept_blob,
2490  null as c_metadataxml
2491  from clarity.ZC_SEXUALLY_ACTIVE@id extracttab
2492  union select
2493    'FA' as va,
2494    '\i2b2\History\Social History\Sexually Active\' as concept_path ,
2495    'Sexually Active' as name_char,
2496    fill.*
2497    from dual, social_hx_concept_fill fill),
2498   
2499social_concepts as
2500(select * from make_base_social_history
2501 union select * from extract_smoking_tob_use_c
2502 union select * from smoking_quit_date
2503 union select * from extract_smokeless_tob_use_c
2504 union select * from smokeless_quit_date
2505 union select * from extract_tobacco_user_c
2506 union select * from pack_per_day
2507 union select * from tobacco_used_years
2508 union select * from extract_sexual_activity_c)
2509
2510select length(sc.concept_path) - length(replace(sc.concept_path, '\', '')) - 2 as c_hlevel,
2511       sc.concept_path as c_fullname,
2512       sc.name_char as c_name,
2513       'N' as c_synonym_cd,
2514       sc.va as c_visualattributes,
2515       sc.concept_cd as c_basecode,
2516       sc.c_metadataxml as c_metadataxml,
2517       'CONCEPT_CD' as c_facttablecolumn,
2518       'CONCEPT_DIMENSION' as c_tablename,
2519       'CONCEPT_PATH' as c_columnname,
2520       'T' as c_columndatatype,
2521       'LIKE' c_operator,
2522       sc.concept_path as c_dimcode,
2523       null as c_tooltip,
2524       '@' as m_applied_path,
2525       sysdate as update_date,
2526       sysdate as import_date,
2527       aud.source_cd as sourcesystem_cd
2528       
2529  from social_concepts sc, epic_audit_info aud
2530  ;
2531
2532/* To load the concepts for EPIC encounter related vitals
2533TODO: separate this code into another file
2534*/
2535create or replace view kuh_encounter_scheme as
2536select
2537  'KUH|PAT_ENC:' c_key,
2538  'KUH|PAT_ENC' c_name,
2539  'Vitals etc from patient encounters, as used in Epic deployed at kumed.com' c_description,
2540  '\i2b2\VitalSignMeasure\' concept_path
2541  from dual;
2542
2543insert into BLUEHERONMETADATA.epic_terms (
2544  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode, C_METADATAXML,
2545  c_visualattributes,
2546  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
2547  c_operator,  m_applied_path,
2548  update_date, import_date, sourcesystem_cd
2549)
2550select
2551  c_hlevel, concept_path, name_char, concept_cd, concept_path, C_METADATAXML,
2552  c_visualattributes,
2553  norm.*
2554  -- had to do the following to overcome ORA-00904: : invalid identifier
2555  , (select sysdate from dual), (select sysdate from dual)
2556  , aud.source_cd
2557from (
2558select
2559data.c_hlevel
2560-- TODO: clean up the path construction
2561, case when path is NULL and data.c_visualattributes = 'LA'
2562         then '\i2b2\Visit Details\Vitals\'||data.column_name||'\'
2563      when data.c_visualattributes = 'FA'
2564         then '\i2b2\Visit Details\Vitals\'||path
2565      else '\i2b2\Visit Details\Vitals\'||path||data.column_name||'\'
2566  end concept_path
2567, data.name_char
2568, case when data.c_visualattributes = 'FA' then NULL
2569    else scheme.c_key || data.column_name end concept_cd
2570, data.column_name
2571, data.c_visualattributes
2572, case when data.units is not null
2573then '<?xml version="1.0"?>
2574<ValueMetadata>
2575 <Version>3.02</Version>
2576 <CreationDateTime>'|| sysdate ||'</CreationDateTime>
2577 <TestID>'|| scheme.c_key || data.column_name ||'</TestID>
2578 <TestName>'|| data.name_char ||'</TestName>
2579 <DataType>PosFloat</DataType>
2580 <CodeType>GRP</CodeType>
2581 <Loinc>'||scheme.c_key || data.column_name||'</Loinc>
2582 <Flagstouse>HL</Flagstouse>
2583 <Oktousevalues>The units in this set of tests are not equivalent,
2584 therefore numeric comparisons between the tests are not possible.</Oktousevalues>
2585 <MaxStringLength></MaxStringLength>
2586 <LowofLowValue></LowofLowValue>
2587 <HighofLowValue></HighofLowValue>
2588 <LowofHighValue></LowofHighValue>
2589 <HighofHighValue></HighofHighValue>
2590 <LowofToxicValue></LowofToxicValue>
2591 <HighofToxicValue></HighofToxicValue>
2592 <EnumValues></EnumValues>
2593 <CommentsDeterminingExclusion>
2594   <Com></Com>
2595 </CommentsDeterminingExclusion>
2596 <UnitValues>
2597   <NormalUnits>'|| data.units || '</NormalUnits>
2598   <ConvertingUnits>
2599     <Units></Units>
2600     <MultiplyingFactor></MultiplyingFactor>
2601   </ConvertingUnits>
2602 </UnitValues>
2603 <Analysis>
2604   <Enums />
2605   <Counts />
2606   <New />
2607 </Analysis>
2608</ValueMetadata>'
2609else NULL
2610end C_METADATAXML
2611from kuh_encounter_scheme scheme,
2612  (
2613  (select 'BMI' column_name, 'Body Mass Index' name_char, 'kg/m^2 ??' units
2614   , NULL as path
2615   , 3 as c_hlevel, 'LA' as  c_visualattributes   from dual)
2616   union
2617  (select 'BMI_PERCENTILE' column_name, 'Body Mass Index Percentile (age 2 to 20 years)' name_char, 'percentile' units
2618   , NULL as path
2619   , 3 as c_hlevel, 'LA' as  c_visualattributes   from dual)
2620   union
2621   (select NULL, 'Visit Vitals', null
2622    , '' as path
2623    , 2 as c_hlevel, 'FA' as  c_visualattributes from dual)
2624   union 
2625   (select 'BSA', 'Body Surface Area', 'm^2'
2626   , NULL as path
2627   , 3 as c_hlevel, 'LA' as  c_visualattributes from dual)
2628   union
2629   (select 'HEIGHT', 'Height (cm)', 'cm'
2630    , NULL as path   
2631   , 3 as c_hlevel, 'LA' as  c_visualattributes from dual)
2632   union
2633   (select 'WEIGHT', 'Weight (oz)', 'oz'
2634    , NULL as path   
2635    , 3 as c_hlevel, 'LA' as  c_visualattributes from dual)
2636   union
2637   (select 'PULSE', 'Pulse', 'beat/min'
2638    , NULL as path   
2639    , 3 as c_hlevel, 'LA' as  c_visualattributes from dual)
2640   union
2641   (select 'HEAD_CIRCUMFERENCE', 'Head Circumference (cm)', 'cm'
2642    , NULL as path
2643    , 3 as c_hlevel, 'LA' as  c_visualattributes from dual)
2644   union
2645   (select 'LMP', 'Last Menstrual Period', null
2646    , 'lmp\' as path   
2647    , 3 as c_hlevel, 'FA' as  c_visualattributes from dual)
2648    union
2649   (select 'LMP'||LMP_OTHER_C, name, null
2650    , 'lmp\' as path   
2651    , 4 as c_hlevel, 'LA' as  c_visualattributes from clarity.ZC_LMP_OTHER@id)
2652   union
2653   (select 'RESPIRATIONS', 'Respiration Rate', '1/min'
2654    , NULL as path   
2655    , 3 as c_hlevel, 'LA' as  c_visualattributes from dual)
2656   union
2657   (select 'TEMPERATURE', 'Temperature (F)', 'deg F'
2658    , NULL as path   
2659    , 3 as c_hlevel, 'LA' as  c_visualattributes from dual)
2660   union
2661   (select 'BP_DIASTOLIC', 'Diastolic Blood Pressure', 'mm Hg'
2662    , NULL as path   
2663    , 3 as c_hlevel, 'LA' as  c_visualattributes from dual)
2664   union
2665   (select 'BP_SYSTOLIC', 'Systolic Blood Pressure', 'mm Hg'
2666    , NULL as path   
2667    , 3 as c_hlevel, 'LA' as  c_visualattributes from dual)
2668   ) data   
2669   )con
2670, BlueHeronMetadata.normal_concept norm, epic_audit_info aud;
2671
2672/**
2673 * Test to insure that all c_fullnames in epic_terms are unique.  If they are not
2674 * there is the possibility that the term_tree will be be corrupted.  A child may
2675 * think it has multiple different parents.
2676 **/
2677select (case when count(*) > 0 then 1/0 else 1 end) as no_duplicate_full_names
2678from
2679
2680   select * from blueheronmetadata.epic_terms et
2681   join (select c_fullname, count(*)
2682         from blueheronmetadata.epic_terms
2683         group by c_fullname having count(*) > 1) unq
2684   on et.c_fullname = unq.c_fullname
2685   order by et.c_fullname
2686);
2687
2688
2689/**
2690 * Build concepts/terms for patient encounter class in the hospital
2691 * (e.g. Inpatient, Outpatient, Emergency)
2692 **/
2693insert into BlueHeronMetadata.epic_terms (
2694c_hlevel, c_fullname, c_name, c_synonym_cd,
2695c_visualattributes, c_basecode, c_facttablecolumn,
2696c_tablename, c_columnname, c_columndatatype, c_operator,
2697c_dimcode, c_tooltip, m_applied_path, update_date,
2698import_date, sourcesystem_cd )
2699
2700with encounter_type_concept_fill as
2701(select to_char(null) as c_basecode,'FA' as va from dual),
2702
2703base_encounter_type as
2704(select '\i2b2\Visit Details\ENC_TYPE\' as concept_path,
2705    'Encounter Type (DRAFT)' as name_char,
2706    fill.*
2707    from encounter_type_concept_fill fill
2708),
2709gpc_enc_folders as (
2710  select distinct bet.concept_path || em.path_fragment || '\' concept_path,
2711  em.c_name name_char, null concept_cd, 'FA' va
2712  from base_encounter_type bet, clarity.zc_pat_class@id pat_c
2713  join enc_type_adt_map em on em.adt_pat_class_c = pat_c.adt_pat_class_c
2714  ),
2715encounter_type as(
2716  select
2717    bet.concept_path || em.path_fragment || '\' || pat_c.adt_pat_class_c || '\' as concept_path,
2718    pat_c.name as name_char,
2719    'KUH|HOSP_ADT_CLASS:'||pat_c.adt_pat_class_c,
2720    'LA' as va 
2721  from base_encounter_type bet, clarity.zc_pat_class@id pat_c
2722  join enc_type_adt_map em on em.adt_pat_class_c = pat_c.adt_pat_class_c
2723  union
2724  select bet.concept_path || 'ED\ED_EPISODE\' as concept_path,
2725    'Emergency Episode' as name_char, 'KUH|ED_EPISODE' as concept_cd,
2726    'LA' as va
2727  from base_encounter_type bet
2728  ),
2729encounter_type_tree as
2730(
2731  select * from (
2732    select * from base_encounter_type union all
2733    select * from encounter_type
2734    ), epic_audit_info
2735  union all
2736  select * from gpc_enc_folders, gpc_audit_info
2737)
2738
2739select length(ett.concept_path) - length(replace(ett.concept_path, '\', '')) - 2 as c_hlevel,
2740       ett.concept_path as c_fullname,
2741       ett.name_char as c_name,
2742       'N' as c_synonym_cd,
2743       ett.va as c_visualattributes,
2744       ett.c_basecode as c_basecode,
2745       'CONCEPT_CD' as c_facttablecolumn,
2746       'concept_dimension' as c_tablename,
2747       'CONCEPT_PATH' as c_columnname,
2748       'T' as c_columndatatype,
2749       'LIKE' c_operator,
2750       ett.concept_path as c_dimcode,
2751       null as c_tooltip,
2752       '@' as m_applied_path,
2753       sysdate as update_date,
2754       sysdate as import_date,
2755       source_cd as sourcesystem_cd
2756       
2757  from encounter_type_tree ett
2758  order by c_fullname
2759  ;
2760
2761
2762
2763 /*epic_dischargedisposition -- build concepts/terms for discharge disposition codes.
2764 */
2765insert into BLUEHERONMETADATA.epic_terms (
2766  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
2767  c_visualattributes,
2768  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
2769  c_operator,  m_applied_path,
2770  update_date, import_date, sourcesystem_cd
2771)
2772select
2773  con.l c_hlevel, con.concept_path c_fullname, con.name_char c_name, con.concept_cd c_basecode,
2774  con.concept_path c_dimcode, con.c_visualattributes,
2775  norm.*,
2776  sysdate update_date, sysdate import_date, aud.source_cd
2777from (
2778  with
2779  base as (
2780    select
2781      '\i2b2\Visit Details\' path,
2782      'Discharge Disposition Codes' disp_name,
2783      -- We could get this concept code from epic_discharge_disposition@id - or use a common schemes table?
2784      'KUMC|DischargeDisposition:' disp_code,
2785      'Discharge Disposition Not Recorded' not_rec,
2786      'LA ' leaf,
2787      'FA ' folder
2788    from dual
2789    ),
2790  zcd as (
2791    select to_char(z.disch_disp_c) disch_disp_c, z.name
2792    from clarity.zc_disch_disp@id z
2793    -- Join against the fact view so that we only show disposition concepts for which we have facts
2794    join (select distinct disch_disp_c from epic_discharge_disposition@id) ddf on ddf.disch_disp_c = z.disch_disp_c
2795    union all
2796    select '0' disch_disp_c, not_rec name
2797    from base
2798    )
2799  select
2800    2 l, path || disp_name || '\' concept_path, null concept_cd, disp_name name_char, folder c_visualattributes
2801    from base
2802  union all
2803  select
2804    3 l, path || disp_name || '\' || to_char(zcd.disch_disp_c) || '\' concept_path, disp_code || zcd.disch_disp_c concept_cd,
2805    zcd.name name_char, leaf c_visualattributes
2806  from base, zcd
2807  ) con, BlueHeronMetadata.normal_concept norm, epic_audit_info aud
2808;
2809
2810/*LOINC Lab Concepts */
2811insert into BLUEHERONMETADATA.epic_terms (
2812  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
2813  c_visualattributes,
2814  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
2815  c_operator,  m_applied_path,
2816  update_date, import_date, sourcesystem_cd
2817  )
2818select
2819  con.c_hlevel, con.c_fullname, con.c_name, con.c_basecode, con.c_fullname,
2820  con.c_visualattributes,
2821  norm.*,
2822  sysdate, sysdate, aud.source_cd
2823from BlueHeronMetadata.normal_concept norm, epic_audit_info aud, (
2824  with
2825  clarity_to_loinc as (
2826    select cc.component_id ccid, cc.name, ltc.loinccode
2827    from clarity.clarity_component@id cc
2828    -- loinctestcodes is the staged component_id to LOINC code mapping we got from KUH
2829    -- Throw away lab component ids where we don't have a loinc mapping entry
2830    join i2b2metadata2.loinctestcodes ltc on cc.abbreviation = ltc.testcode
2831    where loinccode is not null
2832    ),
2833  loinc_parents as (
2834    select
2835      c_hlevel,
2836      /* For leaves, replace the last part of the c_fullname with the c_name
2837      (which includes the LOINC code).  Otherwise, we get multiple LOINC codes
2838      for a single path.
2839      */
2840      case when c_visualattributes like 'L%' then
2841        substr(c_fullname, 1, instr(c_fullname, '\', -2)) || c_name || '\'
2842      else c_fullname end c_fullname,
2843      c_name, c_basecode, c_visualattributes
2844    -- lab_results is the staged LOINC hierarchy
2845    from i2b2metadata2.lab_results
2846    -- Some c_fullnames in staged hierarchy are NULL
2847    where c_fullname is not null
2848    ),
2849  clarity_children as (
2850    -- Park Clarity component IDs under the appropriate LOINC code
2851    select
2852      lp.c_hlevel + 1 c_hlevel,
2853      -- Mimic c_fullname construction of our old lab hierarchy
2854      lp.c_fullname || ctl.ccid || ': ' || ctl.name || '\' c_fullname,
2855      ctl.name || '(#' || ctl.ccid || ')' c_name,
2856      'KUH|COMPONENT_ID:' || ctl.ccid c_basecode,
2857      'LA' c_visualattributes
2858    from clarity_to_loinc ctl
2859    join loinc_parents lp on 'LOINC:' || ctl.loinccode = lp.c_basecode
2860    )
2861  select * from clarity_children
2862  union all
2863  select c_hlevel, c_fullname,
2864  case when c_hlevel = 1 then c_name || ' (DRAFT)' else c_name end c_name,
2865  c_basecode,
2866    case when c_visualattributes like 'L%' then 'FA'
2867    else c_visualattributes end c_visualattributes
2868  from loinc_parents
2869  ) con
2870;
Note: See TracBrowser for help on using the repository browser.