Pasted

PROCEDURE
===
SELECT e1.encntr_id, ea.alias AS fin, proc.procedure_id, proc.encntr_id, proc.proc_priority, icd.source_identifier AS proc_code, icd.source_string AS proc_description, cv.display AS physician_role, pr.name_full_formatted, cv2.display AS attending_physician, pr2.name_full_formatted
FROM encounter e1
LEFT JOIN encntr_alias ea ON e1.encntr_id = ea.encntr_id AND ea.encntr_alias_type_cd = 1077 AND ea.alias_pool_cd = 706841
LEFT JOIN PROCEDURE proc ON e1.encntr_id = proc.encntr_id AND proc.active_ind = 1 AND proc.end_effective_dt_tm > SYSDATE
LEFT JOIN nomenclature icd ON proc.nomenclature_id = icd.nomenclature_id AND icd.active_ind = 1 --AND icd.source_vocabulary_cd = 1231
LEFT JOIN proc_prsnl_reltn ppr ON proc.procedure_id = ppr.procedure_id AND ppr.proc_prsnl_reltn_cd = 1209 --surgeon
LEFT JOIN prsnl pr ON ppr.prsnl_person_id = pr.person_id
LEFT JOIN code_value cv ON ppr.proc_prsnl_reltn_cd = cv.code_value AND cv.active_ind = 1
LEFT JOIN encntr_prsnl_reltn epr ON e1.encntr_id = epr.encntr_id AND epr.active_ind = 1 AND epr.encntr_prsnl_r_cd = 1119 AND epr.end_effective_dt_tm > SYSDATE
LEFT JOIN prsnl pr2 ON epr.prsnl_person_id = pr2.person_id
LEFT JOIN code_value cv2 ON epr.encntr_prsnl_r_cd = cv2.code_value AND cv2.active_ind = 1
WHERE 1=1
--AND e1.organization_id IN (123456, 654321)
AND e1.encntr_id = 123456;
 
-- 1116 admitting
-- 1119 attending
-- 1209 surgeon
 
DIAGNOSIS
===
SELECT e1.encntr_id, ea.alias AS fin, d.diagnosis_id, diag_type.display AS diag_type, d.diag_priority, poa.display AS present_on_admit, n.source_identifier AS diag_code, n.source_string AS diag_description
FROM encounter e1
LEFT JOIN encntr_alias ea ON e1.encntr_id = ea.encntr_id AND ea.encntr_alias_type_cd = 1077 AND ea.alias_pool_cd = 706841
LEFT JOIN diagnosis d ON e1.encntr_id = d.encntr_id AND d.active_ind = 1
LEFT JOIN nomenclature n ON d.nomenclature_id = n.nomenclature_id AND n.active_ind = 1 AND n.end_effective_dt_tm > SYSDATE
LEFT JOIN code_value diag_type ON d.diag_type_cd = diag_type.code_value AND diag_type.active_ind = 1
LEFT JOIN code_value poa ON d.present_on_admit_cd = poa.code_value AND poa.active_ind = 1
WHERE e1.encntr_id = 123456
ORDER BY d.diag_type_cd ASC, d.diag_priority ASC;