CREATE OR REPLACE VIEW vw_ht_xttsxx_mzdzbl (
   xtdm00,
   xtmc00,
   xxbh00,
   xxnr00,
   yxq000,
   czyxm0,
   czrq00,
   xh0000,
   czyks0,
   czyksm,
   jsksbh,
   jsksmc,
   xxxz00,
   xxlx00,
   xxgllx,
   xxglbz,
   fsdx00,
   brid00 )
AS
select
  XTDM00,   --所属系统代码
  nvl((Select XTMC00 from XT_YYXT00  where XTDM00 = a.XTDM00),'全部') as XTMC00,   --所属系统名称
  XXBH00,   --消息编号
  XXNR00,   --消息内容
  YXQ000,   --有效期
  CZYXM0,   --消息制定人
  CZRQ00,   --操作日期
  XH0000,   --序号
  CZYKS0,
  nvl((select BMMC00 from BM_BMBM00 where BMBH00 = a.CZYKS0), '') as CZYKSM,  --发布科室名称
  decode(A.FSDX00,'0',JSKSBH,'1',A.JSYSZ0,'2',A.JSRY00,'3',JSKSBH)JSKSBH ,
  decode(A.Fsdx00, '0',SUBSTR((SELECT SF_HT_GETRECORD('SELECT BMMC00 FROM BM_BMBM00 WHERE INSTR(' ||
                                     CHR(39) || CHR(44) || a.jsksbh ||
                                     CHR(44) || CHR(39) ||
                                     ','',''||BMBH00||'','')>0',
                                     ',')
                FROM DUAL),
              1,
              100) ,'1',(SELECT SF_HT_GETRECORD('SELECT YSZMC0 FROM zs_ysz000 WHERE INSTR(' ||
                                      CHR(39) || CHR(44) || a.JSYSZ0 ||
                                      CHR(44) || CHR(39) ||
                                      ','',''||YSZID0||'','')>0',
                                      ',')FROM DUAL)
     ,'2',(SELECT SF_HT_GETRECORD('SELECT zwxm00 FROM bm_ygbm00 WHERE INSTR(' ||
                               CHR(39) || CHR(44) || a.Jsry00 || CHR(44) ||
                               CHR(39) || ','',''||ygbh00||'','')>0',
                               ',')
          FROM DUAL)
     ,'3',(SELECT SF_HT_GETRECORD('SELECT trim(yyjc00) yyjc00 FROM xt_yyxx00 WHERE INSTR(' ||
                               CHR(39) || CHR(44) || a.jsksbh || CHR(44) ||
                               CHR(39) || ','',''||trim(yyid00)||'','')>0',
                               ',')
          FROM DUAL)) JSKSMC,
  XXXZ00,
  decode(XXXZ00,'1','通知','0','消息') XXLX00,
  XXLX00 XXGLLX,  --消息关联类型 
  XXGLBZ,   --消息关联标志 
  FSDX00,   --发送对象
  BRID00    --消息关联的BRID00
from XT_XTTSXX A where zt0000='0'
AND  XTDM00='OE' AND SUBSTR(XXNR00,1,9)='脑卒中绿色通道患者'
/


-- End of DDL Script for View SD_HOSPITAL.VW_HT_XTTSXX_MZDZBL