PROMPT 32  未创建病历病人列表  VW_ZS_WCJBL0
-- MODIFICATION HISTORY
-- Person      Date        Comments 
-- zhuyj      2013.08.28   DZBL-20130827-056:护理级别简称增加判断用字母I、II、III、IV命名的护理级别名称
-- rbh        2014.12.20    取值zydzbl  DZBL-20141210-007 
create or replace view VW_ZS_WCJBL0 as
select A.RYZDMC,
       A.SZYS00,
       A.ZZYS00,
       A.ZRYS00,
       A.SXYS00,
       A.ZYID00,
       A.BRID00,
       A.MRN000,
       A.ZYH000,
       A.XM0000,
       A.XB0000,
       A.CSRQ00,
       A.BRZT00,
       A.RYCWH0,
       A.RYRQ00,
       A.RYSJ00,
       A.RYZD00,
       B.YBMC00,
       A.RZKS00,
       C.BMMC00,
       A.YSZID0,
       A.CYDJRQ,
       SF_BQ_CSRQTONL(A.CSRQ00) BRNL00,    --病人年龄
       decode(substr(A.BRZT00,1,1),'2',to_char(greatest(to_date(to_char(sysdate,'yyyyMMdd'),'yyyyMMdd')-to_date(A.RYRQ00,'yyyyMMdd'),1))||'天',substr(A.SJCYRQ,1,4)||'-'||substr(A.SJCYRQ,5,2)||'-'||substr(A.SJCYRQ,7,2)) as RYTSMS, --住院天数描述
       (select ' '||X.SSMC00 from VW_SS_YW0000 X where X.ZYGHID(+)=A.ZYID00 and X.MZZYBZ='1' and X.SSZT00 not in ('1','3') and X.SSQSRQ=(select trim(max(Y.SSQSRQ)) from SS_YW0000 Y where Y.ZYGHID(+)=X.ZYGHID and Y.MZZYBZ='1' and Y.SSZT00 not in ('1','3')) and rownum=1) ssmc00, --手术名称
       (select decode(trim(max(X.SSQSRQ)),null,null,((to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd')-to_date(trim(max(X.SSQSRQ)),'yyyymmdd')))) from SS_YW0000 X where X.ZYGHID(+)=A.ZYID00 and X.MZZYBZ='1' and X.SSZT00 not in ('1','3')) SSTS00,  --手术天数
       (select Y.ZLXMJC from BM_ZLZD00 Y where Y.ZLXMID(+)=A.HLJBID) HLJB00,   --护理级别
       (select decode(substr(trim(Y.ZLXMJC),1,1),'一','Ⅰ','二','Ⅱ','三','Ⅲ','四','Ⅳ',decode(substr(trim(Y.ZLXMJC),1,3),'I级护','Ⅰ','II级','Ⅱ','III','Ⅲ','IV级','Ⅳ',substr(trim(Y.ZLXMJC),1,1))) from BM_ZLZD00 Y where Y.ZLXMID(+)=A.HLJBID) HLJBJC,   --护理级别简称
       (SELECT ZDMC00 FROM VW_DZBL_BRZDXX_ONE WHERE ZYID00=A.ZYID00 And rownum=1) ZDMC00,  --诊断名称
       A.DQBQ00,   --当前病区
       (select BRZJBH from BM_BRXXB0 where BRID00=A.BRID00) BRZJBH, --病人身份证
       SF_BQ_KYJE00_ZYDZBL(A.ZYID00,BRID00)  KYJE00,
       (select YPMC00  from YJ_BRGMJL T WHERE T.BRID00(+)=A.BRID00  and rownum=1) BRGMJL
from ZY_BRXXB0 A, IC_YBBRLB B ,BM_BMBM00 C
where    A.BRZT00<>'9' and A.YBLB00=B.YBLB00 and A.FBBH00=B.FBBH00
 and A.RZKS00=C.BMBH00 and not exists (select 1 from  ZS_BLXX01 where  ZYID00=A.ZYID00 and BLZT00 <> '4' );
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%