-- Start of DDL Script for View SD_HOSPITAL.VW_PACS_BGCX00_DZBL
-- Generated 2019-07-16 16:00:44 from SD_HOSPITAL@FW2

CREATE OR REPLACE VIEW vw_pacs_bgcx00_dzbl (
   brid00,
   zyhghh,
   mzzybz,
   zyghid,
   brxm00,
   xb0000,
   nl0000,
   yjdjh0,
   zlxmid,
   zlxmjc,
   bz0000,
   sl0000,
   kdksmc,
   kdrq00,
   kdysxm,
   xmzt00,
   jclbid,
   jclbmc,
   bgurl0,
   yxurl0,
   kdksbh,
   kdys00,
   zyzbid,
   zyzbmc )
AS
select a.BRID00,a.ZYHGHH,a.MZZYBZ,a.ZYGHID,a.BRXM00,a.XB0000,a.NL0000,
       to_char(a.YJDJH0),a.ZLXMID,a.ZLXMJC,a.BZ0000||a.BWMC00 BZ0000,a.SL0000,c.BMMC00 KDKSMC,a.KDRQ00,a.KDYSXM,
       decode(a.XMZT00,'0','申请','1','计价','2','已收费','3','已执行','4','已报告','5','疑退','6','作废') XMZT00,
       d.JCLBID,b.jclbmc as JCLBMC,b.BGURL0,b.YXURL0,a.KDKSBH,a.KDYS00,
        decode(d.LBBH00, 4,(select e.ZYZBID from BM_JYZYZB e where d.ZYZBID = e.ZYZBID and rownum<=1) , f.LBBH00) as ZYZBID,
       decode(d.LBBH00, 4,(select e.ZYZBMC from BM_JYZYZB e where d.ZYZBID = e.ZYZBID and rownum<=1) , b.JCLBMC) as ZYZBMC
from  YJ_YW0000 a,BM_ZlZD00 d,BM_JCLB00 b,BM_BMBM00 c,BM_ZLXMLB f 
where a.ZLXMID=d.ZLXMID and d.LBBH00=f.LBBH00 and d.JCLBID=b.JCLBID and a.KDKSBH=c.BMBH00 and b.SFJK00='1' and a.SL0000>'0'  and
      not exists (select 1 from YJ_YW0000 x where a.ZYGHID=x.ZYGHID and a.MZZYBZ=x.MZZYBZ and x.CXDJH0=a.YJDJH0 and x.XMZT00<>'0')
/


-- End of DDL Script for View SD_HOSPITAL.VW_PACS_BGCX00_DZBL