Display the problems part of "Problems and Diagnosis" from Cerner Powerchart

problems_and_diagnosis.prg

; ----------------------------------------------------------------------------
; Script Author: Robert Holland
; Program Name: problems_and_diagnosis.prg
; Creation Date: Tue Aug 20 2013 16:51:29 GMT-0700 (US Mountain Standard Time)
; Last Modified:
; No copyright: Free
; Purpose: Display the problems part of "Problems and Diagnosis" from Cerner Powerchart
; Request #:
; Note 0: This CCL will produce the information shown in Powerchart "Problems and Diagnosis"
; Note 1: person_id = 37298420 is Test, Patient D in AZB.
; ----------------------------------------------------------------------------
 
select
                Name_of_Problem = pr.annotated_display
                , Course = uar_get_code_display(pr.course_cd)
                , Ranking = uar_get_code_display(pr.ranking_cd)
                , Persistence = uar_get_code_display(pr.persistence_cd)
                , Life_Cycle_Status_disp = uar_get_code_display(pr.life_cycle_status_cd)
                , Last_Updated = pr.updt_dt_tm
                , Responsible_Provider = if (pp.problem_reltn_cd = 3321) replace(prs.name_full_formatted
                , prs.name_full_formatted,"",0) else (prs.name_full_formatted) endif
                ;,pp.problem_reltn_dt_tm "@SHORTDATETIME"
                ;, pp.updt_dt_tm ";;"
from
                problem   pr
                , problem_prsnl_r   pp
                , prsnl prs
plan pr where pr.person_id = 33395010.00 and pr.active_ind = 1
and pr.problem_instance_id in
(
                select max(pr2.problem_instance_id) from problem pr2 where pr2.person_id = 33395010.00
                group by pr2.problem_id
)
join pp where pp.problem_id = pr.problem_id and pp.problem_reltn_cd in (3322, 3321)
and pp.problem_prsnl_id in
(
                select max(pp2.problem_prsnl_id) from problem_prsnl_r   pp2
                where pp2.problem_id in (select pr2.problem_id from problem pr2 where pr2.person_id = 33395010.00)
                group by pp2.problem_id
)
join prs where pp.problem_reltn_prsnl_id = prs.person_id
order by pr.annotated_display