/* 2018.09.17 gzj 福建省福清市医院新院 SSMZ9-20180820-002 手术室要求，在手术信息维护下，新增一个手术进行状态为：通知病区准备（如附件），当选择通知病区准备并修改保存时，需在住院护士工作站给出提醒，让护士做好相关术前准备。此需求同时提至住院护士系统。 处理：新增字典“手术进行状态”维护手术进行状态，修改保存后状态‘通知病区准备’保存到表SS_YW0000对应的字段SSJXZT=4 */ --增加通知病区准备状态判断 -- y.SSJXZT, --手术进行状态 0准备中 1进行中 2麻醉复苏中 3返回病房 4 通知病区准备 -- decode(y.SSJXZT,'0','准备中','1','进行中','2','麻醉复苏中','3','返回病房','4','通知病区准备','') as SSJXMC, --手术业务项目 create or replace view VW_SS_YW0000 -- MODIFICATION HISTORY -- Person Date Comments -- lintj 2013.03.06 重新整理 -- liuj 2012.01.23 加字段 -- qks 2015.06.19 增加字段 -- qks 2015.12.23 修改字段BYH000取值 by SSMZ9-20151223-001 -- xuzw 2016.01.19 增加字段：MZYSX00,MZYSX11,MZYSX22,TSHSX11,TSHSX22,XHHSX11,XHHSX22,SSZSX11,SSZSX22,SSZSX33,SSZSX44 （需求：SSMZ9-20160120-001） -- jlg 2016.04.20 增加字段 BASYXS等 SSMZ9-20160421-001 -- jlg 2016.05.11 增加或修改字段取值ssdjmc,qkdjmc,bwmc00等 SSMZ9-20160510-001 -- jlg 2016.07.26 增加CRBXX0(传染病信息)字段 SSMZ9-20160629-001 -- jlg 2016.08.01 补全一些字段的中文说明，如KSLBMC SSMZ9-20160125-001 -- wusq 2017.01.18 增加字段SSTCZF by SSMZ9-20161213-001 -- qks 2017.04.17 SSYSX1字段中的姓名取值改用关联字典表; by SSMZ9-20170417-003 -- jlg 2017.04.27 增加SSJXZT字段 SSMZ9-20170419-001 -- qks 2017.06.09 增加是否单病种标识字段DBZBZ0; for SSMZ9-20170612-002 -- whm 2017.07.14 增加NNIS00字段 SSMZ9-20170515-001 -- whm 2017.08.07 增加BRRYCS字段 SSMZ9-20170707-001 -- gzj 2018.04.20 增加DQKSMC,DQBQMC字段 SSMZ9-20180418-001 -- liwm 2018.05.24 修改GHID00,ZYCS00字段错误 SSMZ9-20180524-001 -- linyx 2018.09.05 增加yzxx00 for BQHS9.0-20180817-002 -- jlg 2018.09.07 新增 是否使用止痛泵，麻醉护士1，麻醉护士2，洗手护士1，洗手护士2 等信息 SSMZ9-20180907-001 -- gzj 2018.11.28 新增病人的挂号科室或住院当前科室GHZYKS SSMZ9-20181127-001 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, 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, 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 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='麻醉医生') 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 a.YSHSXM||'/'||b.XKH000 from SS_YSHSXX a,BM_YGBM00 b where a.YSHSBH=b.YGBH00 and a.SSDH00=y.SSDH00 and a.YSHSLB='麻醉医生') as MZYSX00, (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 MZYSX11, (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 MZYSX22, (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 SSZSX11, (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 SSZSX22, (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 SSZSX33, (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 SSZSX44, (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 TSHSX11, (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 TSHSX22, (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 XHHSX11, (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 XHHSX22, (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 MZHSX11, (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 MZHSX22, (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 XSHSX11, (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 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, y.SSSSMC, y.MZKSRQ, y.MZKSSJ, y.MZJSRQ, y.MZJSSJ, y.MZFJ00, y.CRBXX0, y.SSTCZF, y.SSJXZT, --手术进行状态 0准备中 1进行中 2麻醉复苏中 3返回病房 4 通知病区准备 y.NNIS00, decode(y.SSJXZT,'0','准备中','1','进行中','2','麻醉复苏中','3','返回病房','4','通知病区准备','') 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)) GHZYKS --挂号科室或住院科室 from SS_YW0000 y / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%