CREATE VIEW SD_HOSPITAL.VW_SS_YW0000
 (
 BRLB00, JZBZZW, SSDH00, MZZYBZ, ZYHGHH, BRID00, SSTBH0, SSJH00, SSTC00, 
SSXH00, GLDJMC, SSDJMC, SSKS00, MZKS00, SQYS00, SQKS00, SQBQ00, SQR000, 
SRKSLB, SQRQ00, SQSJ00, YYSSRQ, YYSSSJ, SSQSRQ, SSQSSJ, SSJSRQ, SSJSSJ, 
SSYS00, SSZS00, MZYS00, SXYS00, TSHS00, GYHS00, SSYSXM, SSZSXM, MZYSXM, 
SXYSXM, TSHSXM, GYHSXM, MZBH00, BZ0000, SQTJ00, SSLXMC, MZMYMC, SSSLMC, 
ZRL000, ZCL000, SXL000, CXL000, SSZT00, HSHBBZ, JZBZ00, MZAPBZ, QXZBBZ, 
YPZBBZ, SSFYBZ, MZFYBZ, TZBZ00, QXSM00, YYBZ00, YZMXID, SQZD00, SQZDMC, 
SQBQSM, SHZD00, SHBQSM, ZYGHID, SFGR00, XM0000, XB0000, XX0000, CZRQ00, 
SQRQSJ, YYRQSJ, SSRQSJ, JSRQSJ, SSZTMC, QXZB00, YPZB00, SSFY00, MZFY00, 
JZ0000, TZ0000, GR0000, NL0000, MZMC00, GLBZ00, SQYSXM, SSKSMC, MZKSMC, 
SQKSMC, SQBQMC, SSMC00, SSBH00, QKDJMC, YHDJMC, BRSZKS, SZKSMC, RYCWH0, 
CZYXM0, SSJCSJ, GRQK00, SSZB00, SSSD00, BX0000, YYX000, BWMC00, XSHS00, 
XSHSXM, YYID00, BRSZBQ, SHRY00, SHRYXM, SSFZD0, FZDMC0, SSJSX0, SSDYZS, 
SSDEZS, SSDSZS, SSQJJC, MZDBH0, FSRQ00, FSSJ00, FSRQSJ, SQSHFH, SFSSTY, 
XJ0000, HXB000, LCD000, XXB000, YSZID0

 )
 AS 
select '住院' BRLB00,decode(M.jzbz00,'Y','急诊','择期') JZBZZW,
M.SSDH00 ,M.MZZYBZ ,M.ZYHGHH ,M.BRID00 ,M.SSTBH0 ,M.SSJH00 ,M.SSTC00 ,M.SSXH00 ,M.GLDJMC ,M.SSDJMC ,M.SSKS00 ,
M.MZKS00 ,M.SQYS00 ,M.SQKS00 ,M.SQBQ00 ,M.SQR000 ,M.SRKSLB ,M.SQRQ00 ,M.SQSJ00 ,M.YYSSRQ ,M.YYSSSJ ,
M.SSQSRQ ,M.SSQSSJ ,M.SSJSRQ ,M.SSJSSJ ,M.SSYS00 ,M.SSZS00 ,M.MZYS00 ,M.SXYS00 ,M.TSHS00 ,M.GYHS00 ,
M.SSYSXM ,M.SSZSXM ,M.MZYSXM ,M.SXYSXM ,M.TSHSXM ,M.GYHSXM ,M.MZBH00 ,M.BZ0000 ,M.SQTJ00 ,M.SSLXMC ,
M.MZMYMC ,M.SSSLMC ,M.ZRL000 ,M.ZCL000 ,M.SXL000 ,M.CXL000 ,M.SSZT00 ,M.HSHBBZ ,M.JZBZ00 ,M.MZAPBZ ,
M.QXZBBZ ,M.YPZBBZ ,M.SSFYBZ ,M.MZFYBZ ,M.TZBZ00 ,M.QXSM00 ,M.YYBZ00 ,M.YZMXID ,substr(M.SQZD00,1,50) SQZD00 ,substr(M.SQZDMC,1,50) SQZDMC ,M.SQBQSM ,
M.SHZD00 ,M.SHBQSM ,M.ZYGHID ,M.SFGR00 ,M.xm0000 ,M.xb0000 ,M.xx0000 ,M.CZRQ00 ,
M.SQRQ00||' '||substr(SQSJ00,1,5) SQRQSJ ,M.YYSSRQ||' '||substr(YYSSSJ,1,5) YYRQSJ,
M.SSQSRQ||' '||substr(SSQSSJ,1,5) SSRQSJ ,M.SSJSRQ||' '||substr(SSJSSJ,1,5) JSRQSJ,
decode(M.sszt00,'0','申请','1','退回','2','审批','3','取消','4','已做','6','未发送')ssztmc,
decode(M.qxzbbz,'0','无','1','未备','2','已备','3','吻合','4','不符')QXZB00,
decode(M.ypzbbz,'0','无','1','未备','2','已备')YPZB00,
decode(M.ssfybz,'0','无','1','已估','2','已收')SSFY00,
decode(M.mzfybz,'0','无','1','已估','2','已收')MZFY00,
decode(M.jzbz00,'Y','急',' ')JZ0000,
decode(M.tzbz00,'Y','是','否')TZ0000,
decode(M.SFGR00,'Y','是','否')GR0000,
SUBSTR(sf_bq_csrqtonl(M.csrq00),1,6)nl0000,
M.mzmc00,
DECODE(gldjmc,'隔离','★',' ')glbz00,
(select i.zwxm00 from bm_ygbm00 i where M.sqys00=i.ygbh00(+))sqysxm,
(select s.bmmc00 from bm_bmbm00 s where M.ssks00=s.bmbh00(+))ssksmc,
(select t.bmmc00 from bm_bmbm00 t where M.mzks00=t.bmbh00(+))mzksmc,
(select a.bmmc00 from bm_bmbm00 a where M.sqks00=a.bmbh00(+))sqksmc,
(select b.bmmc00 from bm_bmbm00 b where M.sqbq00=b.bmbh00(+))sqbqmc,
(select e.ssxmjc from ss_ywmx00 e where M.ssdh00=e.ssdh00 and e.sfzss0='0' and rownum=1)ssmc00,
(select e.ssbh00 from ss_ywmx00 e where M.ssdh00=e.ssdh00 and e.sfzss0='0' and rownum=1)ssbh00,
(select e.QKDJMC from ss_ywmx00 e where M.ssdh00=e.ssdh00 and e.sfzss0='0' and rownum=1)QKDJMC,--切口等级名称
(select e.YHDJMC from ss_ywmx00 e where M.ssdh00=e.ssdh00 and e.sfzss0='0' and rownum=1)YHDJMC,--愈合等级名称
P.DQKS00 BRSZKS,
U.BMMC00 SZKSMC,
P.RYCWH0,
(select ZWXM00 from bm_ygbm00 where sqr000=ygbh00) CZYXM0,
M.SSJCSJ,
M.GRQK00,--手术时长 感染情况
M.SSZB00,
M.SSSD00,
M.bx0000,
M.yyx000,
M.BWMC00,
M.xshs00,
M.xshsxm,
U.yyid00,
P.DQBQ00 BRSZBQ ,
M.shry00,
(select r.zwxm00 from bm_ygbm00 r where M.shry00=r.ygbh00(+)) shryxm ,
M.ssfzd0,
M.fzdmc0,
m.SSJSX0,
(select e.YSHSXM from SS_YSHSXX e where e.SSDH00=M.ssdh00 and e.YSHSLB='手术助手1' and rownum=1) SSDYZS,
(select e.YSHSXM from SS_YSHSXX e where e.SSDH00=M.ssdh00 and e.YSHSLB='手术助手2' and rownum=1) SSDEZS,
(select e.YSHSXM from SS_YSHSXX e where e.SSDH00=M.ssdh00 and e.YSHSLB='手术助手3' and rownum=1) SSDSZS,
'' SSQJJC,
m.MZDBH0,     --麻醉单编号
M.FSRQ00,
M.FSSJ00,
M.FSRQ00||' '||substr(FSSJ00,1,5)  FSRQSJ,
m.SQSHFH,
m.SFSSTY,
m.XJ0000,
m.HXB000,
m.LCD000,
m.XXB000,
m.YSZID0
from  ss_yw0000 m,ZY_BRXXB0 P,BM_BMBM00 U
where M.MZZYBZ='1' and M.ZYGHID=P.ZYID00 and P.DQKS00=U.BMBH00
union all
select '门诊' BRLB00,decode(M.jzbz00,'Y','急诊','择期') JZBZZW,
M.SSDH00 ,M.MZZYBZ ,M.ZYHGHH ,M.BRID00 ,M.SSTBH0 ,M.SSJH00 ,M.SSTC00 ,M.SSXH00 ,M.GLDJMC ,M.SSDJMC ,M.SSKS00 ,
M.MZKS00 ,M.SQYS00 ,M.SQKS00 ,M.SQBQ00 ,M.SQR000 ,M.SRKSLB ,M.SQRQ00 ,M.SQSJ00 ,M.YYSSRQ ,M.YYSSSJ ,
M.SSQSRQ ,M.SSQSSJ ,M.SSJSRQ ,M.SSJSSJ ,M.SSYS00 ,M.SSZS00 ,M.MZYS00 ,M.SXYS00 ,M.TSHS00 ,M.GYHS00 ,
M.SSYSXM ,M.SSZSXM ,M.MZYSXM ,M.SXYSXM ,M.TSHSXM ,M.GYHSXM ,M.MZBH00 ,M.BZ0000 ,M.SQTJ00 ,M.SSLXMC ,
M.MZMYMC ,M.SSSLMC ,M.ZRL000 ,M.ZCL000 ,M.SXL000 ,M.CXL000 ,M.SSZT00 ,M.HSHBBZ ,M.JZBZ00 ,M.MZAPBZ ,
M.QXZBBZ ,M.YPZBBZ ,M.SSFYBZ ,M.MZFYBZ ,M.TZBZ00 ,M.QXSM00 ,M.YYBZ00 ,M.YZMXID ,substr(M.SQZD00,1,50) SQZD00 ,substr(M.SQZDMC,1,50) SQZDMC ,M.SQBQSM ,
M.SHZD00 ,M.SHBQSM ,M.ZYGHID ,M.SFGR00 ,M.xm0000 ,M.xb0000 ,M.xx0000 ,M.CZRQ00 ,
M.SQRQ00||' '||substr(SQSJ00,1,5) SQRQSJ ,M.YYSSRQ||' '||substr(YYSSSJ,1,5) YYRQSJ,
M.SSQSRQ||' '||substr(SSQSSJ,1,5) SSRQSJ ,M.SSJSRQ||' '||substr(SSJSSJ,1,5) JSRQSJ,
decode(M.sszt00,'0','申请','1','退回','2','审批','3','取消','4','已做')ssztmc,
decode(M.qxzbbz,'0','无','1','未备','2','已备','3','吻合','4','不符')QXZB00,
decode(M.ypzbbz,'0','无','1','未备','2','已备')YPZB00,
decode(M.ssfybz,'0','无','1','已估','2','已收')SSFY00,
decode(M.mzfybz,'0','无','1','已估','2','已收')MZFY00,
decode(M.jzbz00,'Y','急',' ')JZ0000,
decode(M.tzbz00,'Y','是','否')TZ0000,
decode(M.SFGR00,'Y','是','否')GR0000,
SUBSTR(sf_bq_csrqtonl(M.csrq00),1,6)nl0000,
M.mzmc00,
DECODE(gldjmc,'隔离','★',' ')glbz00,
(select i.zwxm00 from bm_ygbm00 i where M.sqys00=i.ygbh00(+))sqysxm,
(select s.bmmc00 from bm_bmbm00 s where M.ssks00=s.bmbh00(+))ssksmc,
(select t.bmmc00 from bm_bmbm00 t where M.mzks00=t.bmbh00(+))mzksmc,
(select a.bmmc00 from bm_bmbm00 a where M.sqks00=a.bmbh00(+))sqksmc,
(select b.bmmc00 from bm_bmbm00 b where M.sqbq00=b.bmbh00(+))sqbqmc,
(select e.ssxmjc from ss_ywmx00 e where M.ssdh00=e.ssdh00 and e.sfzss0='0' and rownum=1)ssmc00,
(select e.ssbh00 from ss_ywmx00 e where M.ssdh00=e.ssdh00 and e.sfzss0='0' and rownum=1)ssbh00,
(select e.QKDJMC from ss_ywmx00 e where M.ssdh00=e.ssdh00 and e.sfzss0='0' and rownum=1)QKDJMC,--切口等级名称
(select e.YHDJMC from ss_ywmx00 e where M.ssdh00=e.ssdh00 and e.sfzss0='0' and rownum=1)YHDJMC,--愈合等级名称
P.GHKS00 BRSZKS,
U.BMMC00 SZKSMC,
'',
(select ZWXM00 from bm_ygbm00 where sqr000=ygbh00) CZYXM0,
M.SSJCSJ,
M.GRQK00,--手术时长 感染情况
M.SSZB00,
M.SSSD00,
M.bx0000,
M.yyx000,
M.BWMC00,
M.xshs00,
M.xshsxm,
U.yyid00,
P.GHKS00 BRSZBQ ,--改字段故意取值挂号科室用于统计by SSMZ-20110711-002
M.shry00,
(select r.zwxm00 from bm_ygbm00 r where M.shry00=r.ygbh00(+)) shryxm ,
M.ssfzd0,
M.fzdmc0,
M.SSJSX0,
(select e.YSHSXM from SS_YSHSXX e where e.SSDH00=M.ssdh00 and e.YSHSLB='手术助手1' and rownum=1) SSDYZS,
(select e.YSHSXM from SS_YSHSXX e where e.SSDH00=M.ssdh00 and e.YSHSLB='手术助手2' and rownum=1) SSDEZS,
(select e.YSHSXM from SS_YSHSXX e where e.SSDH00=M.ssdh00 and e.YSHSLB='手术助手3' and rownum=1) SSDSZS,
'' SSQJJC,
M.MZDBH0,
M.FSRQ00,
M.FSSJ00,
M.FSRQ00||' '||substr(FSSJ00,1,5)  FSRQSJ,
M.SQSHFH,
M.SFSSTY,
m.XJ0000,
m.HXB000,
m.LCD000,
m.XXB000,
m.YSZID0
from  ss_yw0000 M,SF_BRXXB0 P,BM_BMBM00 U
where M.MZZYBZ='0' and M.ZYGHID=P.GHID00 and P.GHKS00=U.BMBH00
/