create or replace view vw_ss_yw0000 as select y.SSDH00, y.MZZYBZ, y.ZYGHID, y.ZYHGHH, decode(y.MZZYBZ,'0',0,'1',(select BRRYCS from ZY_BRXXB0 where ZYID00 = y.ZYGHID and rownum=1)) as BRRYCS, decode(y.MZZYBZ,'0',null,'1',(select BRZT00 from ZY_BRXXB0 where ZYID00 = y.ZYGHID and rownum=1)) as BRZYZT, --病人住院状态 null为门诊 y.BRID00, y.XM0000, y.XB0000, y.XX0000, y.CZRQ00, substr(SF_XT_CSRQTONL(y.CSRQ00),1,20) as NL0000, y.SSTBH0, y.SSJH00, y.SSTC00, y.SSXH00, y.GLDJMC, decode(GLDJMC,'隔离','★',' ') as GLBZ00, --隔离标识 decode(y.SSDJMC,null,(select e.SSDJMC from SS_YWMX00 e where y.SSDH00=e.SSDH00 and e.SFZSS0='0' and rownum=1),y.SSDJMC) as SSDJMC , --手术等级名称 decode(y.QKDJMC,null,(select e.QKDJMC from SS_YWMX00 e where y.SSDH00=e.SSDH00 and e.SFZSS0='0' and rownum=1),y.QKDJMC) as QKDJMC, --切口等级名称 (select e.YHDJMC from SS_YWMX00 e where y.SSDH00=e.SSDH00 and e.SFZSS0='0' and rownum=1) as YHDJMC,--愈合等级名称 y.SSKS00, (select s.BMMC00 from BM_BMBM00 s where y.SSKS00=s.BMBH00) as SSKSMC, y.MZKS00, (select t.BMMC00 from BM_BMBM00 t where y.MZKS00=t.BMBH00) as MZKSMC, y.SQKS00, (select a.BMMC00 from BM_BMBM00 a where y.SQKS00=a.BMBH00) as SQKSMC, y.SQBQ00, (select b.BMMC00 from BM_BMBM00 b where y.SQBQ00=b.BMBH00) as SQBQMC, y.SQYS00, (select i.ZWXM00 from BM_YGBM00 i where y.SQYS00=i.YGBH00) as SQYSXM, (select i.ZWXM00||'/'||i.XKH000 from BM_YGBM00 i where y.SQYS00=i.YGBH00) as SQYSX0, y.SQR000, y.SRKSLB, decode(y.SRKSLB,'0','手术','1','病区','2','医技','3','医生') as KSLBMC, --输入科室类别 y.SQRQ00, y.SQSJ00, y.YYSSRQ, y.YYSSSJ, y.SSQSRQ, y.SSQSSJ, y.SSJSRQ, y.SSJSSJ, y.BZ0000, y.SQTJ00, y.SSLXMC, y.SQBQSM, y.MZMYMC, y.SSSLMC, y.ZRL000, y.ZCL000, y.SXL000, y.CXL000, y.QXSM00, y.YYBZ00, y.YZMXID, y.SQZD00, substr(nvl(y.SQZDMC,decode(y.MZZYBZ,'1',SF_XT_GETZYZD(y.ZYGHID),SF_XT_GETMZZD(y.ZYHGHH))),1,100) as SQZDMC, --术前主要诊断名称 y.SHZD00, y.SHBQSM, y.SFGR00, substr(y.SQRQ00,1,4)||'-'||substr(y.SQRQ00,5,2)||'-'||substr(y.SQRQ00,7,2)||' '||substr(SQSJ00,1,5) as SQRQSJ, substr(y.YYSSRQ,1,4)||'-'||substr(y.YYSSRQ,5,2)||'-'||substr(y.YYSSRQ,7,2)||' '||substr(YYSSSJ,1,5) as YYRQSJ, substr(y.SSQSRQ,1,4)||'-'||substr(y.SSQSRQ,5,2)||'-'||substr(y.SSQSRQ,7,2)||' '||substr(SSQSSJ,1,5) as SSRQSJ, substr(y.SSJSRQ,1,4)||'-'||substr(y.SSJSRQ,5,2)||'-'||substr(y.SSJSRQ,7,2)||' '||substr(SSJSSJ,1,5) as JSRQSJ, y.SSZT00, decode(y.SSZT00,'0','已申请','1','已退回','2','已安排','3','已取消','4','已登记','6','无手术') as SSZTMC, --手术状态 y.JZBZ00, decode(y.JZBZ00,'Y','急诊','择期') as JZBZMC, y.MZAPBZ, decode(y.MZAPBZ,'Y','已安排','N','未安排') as MZAPMC, --麻醉安排名称 y.QXZBBZ, decode(y.QXZBBZ,'0','无','1','未备','2','已备','3','吻合','4','不符') as QXZB00, --器械准备标志 y.YPZBBZ, decode(y.YPZBBZ,'0','无','1','未备','2','已备') as YPZB00, --药品准备标志 y.SSFYBZ, decode(y.SSFYBZ,'0','无','1','已估','2','已收') as SSFY00, --手术费用标志 y.MZFYBZ, decode(y.MZFYBZ,'0','无','1','已估','2','已收') as MZFY00, --麻醉费用标志 y.TZBZ00, decode(y.TZBZ00,'Y','已通知','N','未通知') as TZBZMC, --通知标识 y.HSHBBZ, decode(y.HSHBBZ,'Y','是','N','否') as HSHBMC, --护士换班标识名称 (select e.SSXMJC from SS_YWMX00 e where y.SSDH00=e.SSDH00 and e.SFZSS0='0' and rownum=1) as SSMC00, (select e.SSBH00 from SS_YWMX00 e where y.SSDH00=e.SSDH00 and e.SFZSS0='0' and rownum=1) as SSBH00, y.MZBH00, y.MZMC00, --麻醉编号,麻醉名称 decode(y.MZZYBZ,'1',(select RYCWH0 from ZY_BRXXB0 where ZYID00=y.ZYGHID),null) as RYCWH0, y.SSJCSJ, Y.GRQK00, y.SSZB00, y.SSSD00, y.BX0000, y.YYX000, decode(y.BWMC00,null,(select e.BWMC00 from SS_YWMX00 e where y.SSDH00=e.SSDH00 and e.SFZSS0='0' and rownum=1),y.BWMC00) as BWMC00, --手术部位名称 (select e.FWMC00 from SS_YWMX00 e where y.SSDH00=e.SSDH00 and e.SFZSS0='0' and rownum=1) as FWMC00, --手术方位名称 (select a.YYID00 from BM_BMBM00 a,ZY_BRXXB0 b where ZYID00=y.ZYGHID and b.DQKS00=a.BMBH00) as YYID00, y.SHRY00, (select r.ZWXM00 from BM_YGBM00 r where y.SHRY00=r.YGBH00) as SHRYXM, decode(y.QKDJMC,null,(select e.QKDJMC from SS_YWMX00 e where y.SSDH00=e.SSDH00 and e.SFZSS0='0' and rownum=1),y.QKDJMC) as QKDJ00, y.SSYS00, --主刀手术医生 y.SSYSXM, decode(SSYS00,null,null,(select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00=y.SSYS00)) as SSYSX1, y.SXYS00, --输血医生 y.SXYSXM, decode(SXYS00,null,null,(select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00=y.SXYS00)) as SXYSX1, y.MZYS00, --麻醉医生 y.MZYSXM, (select a.YSHSXM||'/'||b.XKH000 from SS_YSHSXX a,BM_YGBM00 b where a.YSHSBH=b.YGBH00 and a.SSDH00=y.SSDH00 and a.YSHSLB='麻醉医生' and a.ZMFMBZ='0' and rownum<=1) as MZYSX0, (select a.YSHSXM||'/'||b.XKH000 from SS_YSHSXX a,BM_YGBM00 b where a.YSHSBH=b.YGBH00 and a.SSDH00=y.SSDH00 and a.YSHSLB='麻醉医生1') as MZYSX1, (select a.YSHSXM||'/'||b.XKH000 from SS_YSHSXX a,BM_YGBM00 b where a.YSHSBH=b.YGBH00 and a.SSDH00=y.SSDH00 and a.YSHSLB='麻醉医生2') as MZYSX2, y.SSZS00, --手术助手 y.SSZSXM, (select a.YSHSXM||'/'||b.XKH000 from SS_YSHSXX a,BM_YGBM00 b where a.YSHSBH=b.YGBH00 and a.SSDH00=y.SSDH00 and a.YSHSLB='手术助手1') as SSZSX1, (select a.YSHSXM||'/'||b.XKH000 from SS_YSHSXX a,BM_YGBM00 b where a.YSHSBH=b.YGBH00 and a.SSDH00=y.SSDH00 and a.YSHSLB='手术助手2') as SSZSX2, (select a.YSHSXM||'/'||b.XKH000 from SS_YSHSXX a,BM_YGBM00 b where a.YSHSBH=b.YGBH00 and a.SSDH00=y.SSDH00 and a.YSHSLB='手术助手3') as SSZSX3, (select a.YSHSXM||'/'||b.XKH000 from SS_YSHSXX a,BM_YGBM00 b where a.YSHSBH=b.YGBH00 and a.SSDH00=y.SSDH00 and a.YSHSLB='手术助手4') as SSZSX4, y.TSHS00, --台上护士 y.TSHSXM, (select a.YSHSXM||'/'||b.XKH000 from SS_YSHSXX a,BM_YGBM00 b where a.YSHSBH=b.YGBH00 and a.SSDH00=y.SSDH00 and a.YSHSLB='台上护士1') as TSHSX1, (select a.YSHSXM||'/'||b.XKH000 from SS_YSHSXX a,BM_YGBM00 b where a.YSHSBH=b.YGBH00 and a.SSDH00=y.SSDH00 and a.YSHSLB='台上护士2') as TSHSX2, y.GYHS00, --供应护士(巡回护士) y.GYHSXM, (select a.YSHSXM||'/'||b.XKH000 from SS_YSHSXX a,BM_YGBM00 b where a.YSHSBH=b.YGBH00 and a.SSDH00=y.SSDH00 and a.YSHSLB='巡回护士1') as XHHSX1, (select a.YSHSXM||'/'||b.XKH000 from SS_YSHSXX a,BM_YGBM00 b where a.YSHSBH=b.YGBH00 and a.SSDH00=y.SSDH00 and a.YSHSLB='巡回护士2') as XHHSX2, y.XSHS00, --洗手护士 y.XSHSXM, (select a.YSHSXM||'/'||b.XKH000 from SS_YSHSXX a,BM_YGBM00 b where a.YSHSBH=b.YGBH00 and a.SSDH00=y.SSDH00 and a.YSHSLB='洗手护士1') as XSHSX1, (select a.YSHSXM||'/'||b.XKH000 from SS_YSHSXX a,BM_YGBM00 b where a.YSHSBH=b.YGBH00 and a.SSDH00=y.SSDH00 and a.YSHSLB='洗手护士2') as XSHSX2, y.MZHS00, --麻醉护士 y.MZHSXM, (select a.YSHSXM||'/'||b.XKH000 from SS_YSHSXX a,BM_YGBM00 b where a.YSHSBH=b.YGBH00 and a.SSDH00=y.SSDH00 and a.YSHSLB='麻醉护士1') as MZHSX1, (select a.YSHSXM||'/'||b.XKH000 from SS_YSHSXX a,BM_YGBM00 b where a.YSHSBH=b.YGBH00 and a.SSDH00=y.SSDH00 and a.YSHSLB='麻醉护士2') as MZHSX2, decode(MZZYBZ,'0','门诊','住院') as BRLB00, (select ZWXM00 from BM_YGBM00 where YGBH00=y.CZY000) as CZYXM0, decode(y.MZZYBZ,'1',y.ZYHGHH,(select nvl(WDWID0,BRID00) from BM_BRXXB0 where BRID00=Y.BRID00)) as BYH000, (select BRZJBH from BM_BRXXB0 where BRID00=Y.BRID00) as BRZJBH, (select YJKSBH from YJ_YW0000 where YJDJH0=(select nvl(min(YJDJH0),0) from SS_SSYJD0 where SSDH00=y.SSDH00 and SSMZBZ='0' and Y.SSZT00 not in ('1','3','6','7'))) as YJKSBH, (select SXYSXM from ZY_BRXXB0 where ZYID00=y.ZYGHID and y.MZZYBZ='1' union select JZYSXM from SF_BRXXB0 where GHID00=y.ZYGHID and y.MZZYBZ='0') as GCYSXM, y.CLDBBM, (select MC0000 from BM_TYZD00 where ZDMC00='医保耗材打包编码' and BH0000=y.cldbbm and rownum=1) as CLDBMC, y.YSZID0, (select YSHSXM from SS_YSHSXX where SSDH00=y.SSDH00 and YSHSLB='麻醉医生' and ZMFMBZ='0' and rownum<=1) as MZYSX00, (select YSHSXM from SS_YSHSXX where SSDH00=y.SSDH00 and YSHSLB='麻醉医生1') as MZYSX11, (select YSHSXM from SS_YSHSXX where SSDH00=y.SSDH00 and YSHSLB='麻醉医生2') as MZYSX22, (select YSHSXM from SS_YSHSXX where SSDH00=y.SSDH00 and YSHSLB='手术助手1') as SSZSX11, (select YSHSXM from SS_YSHSXX where SSDH00=y.SSDH00 and YSHSLB='手术助手2') as SSZSX22, (select YSHSXM from SS_YSHSXX where SSDH00=y.SSDH00 and YSHSLB='手术助手3') as SSZSX33, (select YSHSXM from SS_YSHSXX where SSDH00=y.SSDH00 and YSHSLB='手术助手4') as SSZSX44, (select YSHSXM from SS_YSHSXX where SSDH00=y.SSDH00 and YSHSLB='台上护士1') as TSHSX11, (select YSHSXM from SS_YSHSXX where SSDH00=y.SSDH00 and YSHSLB='台上护士2') as TSHSX22, (select YSHSXM from SS_YSHSXX where SSDH00=y.SSDH00 and YSHSLB='巡回护士1') as XHHSX11, (select YSHSXM from SS_YSHSXX where SSDH00=y.SSDH00 and YSHSLB='巡回护士2') as XHHSX22, (select YSHSXM from SS_YSHSXX where SSDH00=y.SSDH00 and YSHSLB='麻醉护士1') as MZHSX11, (select YSHSXM from SS_YSHSXX where SSDH00=y.SSDH00 and YSHSLB='麻醉护士2') as MZHSX22, (select YSHSXM from SS_YSHSXX where SSDH00=y.SSDH00 and YSHSLB='洗手护士1') as XSHSX11, (select YSHSXM from SS_YSHSXX where SSDH00=y.SSDH00 and YSHSLB='洗手护士2') as XSHSX22, (select A.RYCS00 from BM_BRXXB0 A where A.BRID00=Y.BRID00 and rownum=1) as RYCS00 , --病人入院次数 Y.ZYGHID GHID00, --挂号ID (select D.ZYCS00 from BA_BRZYXX D where D.LSH000=Y.ZYGHID and rownum=1) as ZYCS00, --住院次数 decode(SSYS00,null,null,SSYSXM) SSYSX11 ,decode(TZDDY0,'1','已打印','未打印') as TZDDY0, y.BASYXS, y.YSSDH0, nvl((select SSSSMC from SS_SSSSMX where SSDH00 = y.SSDH00 and SFZSS0=0 and rownum=1),y.SSSSMC) as SSSSMC, --主实施手术名称 y.MZKSRQ, y.MZKSSJ, y.MZJSRQ, y.MZJSSJ, y.MZFJ00, substrb(y.CRBXX0,1,200) as CRBXX0, y.SSTCZF, y.SSJXZT, --手术进行状态 0准备中 1进行中 2麻醉复苏中 3返回病房 4通知病区准备 y.NNIS00, (select MC0000 from BM_TYZD00 where ZDMC00='手术进行状态' and BH0000=y.SSJXZT and rownum=1) as SSJXMC, decode(y.MZZYBZ,'1',(select decode(DBZBM0,null,null,'是') from ZY_BRXXB0 where ZYID00=y.ZYGHID),'0',null) as DBZBZ0, --单病种标识 decode(y.MZZYBZ,'1',(select BMMC00 from BM_BMBM00,ZY_BRXXB0 B where B.ZYID00=y.ZYGHID and BMBH00=B.DQKS00 and rownum=1),null) as DQKSMC, decode(y.MZZYBZ,'1',(select BMMC00 from BM_BMBM00,ZY_BRXXB0 B where B.ZYID00=y.ZYGHID and BMBH00=B.DQBQ00 and rownum=1),null) as DQBQMC, (select YZXX00 from VW_BQ_YZLL00_ZYYDHL where YZID00=Y.YZMXID and rownum=1) as YZXX00, y.SYZTB0, --是否使用止痛泵 0否1是 decode(y.SYZTB0,'1','是','0','否') as SYZTMC, decode(y.MZZYBZ,'1',(select DQKS00 from ZY_BRXXB0 where ZYID00=y.ZYGHID),(select GHKS00 from VW_SF_BRGHXX where GHH000=y.ZYHGHH and rownum=1)) GHZYKS, --挂号科室或住院科室 '0&'||y.ZYGHID EWM000, (select decode(SFJZFP, '1','1','0') from BM_BRXXB0 where BRID00 = y.BRID00 ) SFJZFP, --是否精准扶贫 1：是，0：否 to_number(substr(SF_SS_GETZJE(y.SSDH00),1,instr(SF_SS_GETZJE(y.SSDH00),';',-1)-1))SSKSFY, --手术科室费用 to_number(substr(SF_SS_GETZJE(y.SSDH00),instr(SF_SS_GETZJE(y.SSDH00),';',-1)+1)) MZKSFY, --麻醉科室费 substrb(SF_SS_GETMZZYBZ(y.MZZYBZ,y.ZYGHID,0),1,80) as DBZBM0, --单病种编码 substrb(SF_SS_GETMZZYBZ(y.MZZYBZ,y.ZYGHID,1),1,80) as DBZMC0, --单病种名称 decode(y.MZZYBZ,'1',(select FBBH00 from ZY_BRXXB0 where ZYID00=y.ZYGHID),(select FBBH00 from SF_BRXXB0 where GHH000=y.ZYHGHH and rownum=1)) as FBBH00, --费别编号 decode(y.MZZYBZ,'1',(select YBZXLB from ZY_BRXXB0 where ZYID00=y.ZYGHID),(select YBZXLB from SF_BRXXB0 where GHH000=y.ZYHGHH and rownum=1)) as YBZXLB, --医保中心类别 decode(y.MZZYBZ,'1',(select YBBRLB from ZY_BRXXB0 where ZYID00=y.ZYGHID),(select YBBRLB from SF_BRXXB0 where GHH000=y.ZYHGHH and rownum=1)) as YBBRLB, --医保病人类别 to_number((select bh0000 from BM_TYZD00 where ZDMC00='手术间' and trim(MC0000)=trim(Y.SSJH00))) as SSJBH0 , --手术间编号 y.ZDLSXM, decode(nvl(y.SFTS00,0),0,'否',1,'是') as SFTSMC, --特殊标识名称 y.SFTS00, --特殊标识 substr(SF_SS_GETHSJYJG(y.BRID00,y.SSDH00),1,200) as HSJYJG, --核酸检验结果 y.SQCWH0, y.PSJG00, --皮试结果 y.CRBXX0 as CRBXX1, --传染病信息(无截取) y.CXCZY0, --撤消操作员编号 (select ZWXM00 from BM_YGBM00 where YGBH00=y.CXCZY0) as CXYXM0, --撤消操作员姓名 y.CXCZKS, --撤消操作科室编码 (select BMMC00 from BM_BMBM00 where BMBH00=y.CXCZKS) as CXKSMC, --撤消操作科室名称 y.CXRQ00, --撤消日期 y.CXSJ00 --撤消时间 from SS_YW0000 y ;