-- Start of DDL Script for View SD_HOSPITAL.VW_XT_ZLMBMX
-- Generated 2021-01-07 13:54:48 from SD_HOSPITAL@30

CREATE OR REPLACE VIEW vw_xt_zlmbmx (
   mxid00,
   mbid00,
   yzlb00,
   clbz00,
   zlxmid,
   zhxmbz,
   zlxmjc,
   xsxh00,
   ts0000,
   tsdw00,
   pcid00,
   pcmc00,
   mcsl00,
   dw0000,
   bz0000,
   lbbh00,
   bbid00,
   bb0000,
   cjks00,
   cjksmc,
   tzrq00,
   zhcxbh,
   jzbz00,
   yzlx00,
   zxks00,
   zlsfbz,
   sfxmid,
   plxh00,
   sfje00,
   xmzk00,
   xmjm00,
   xmje00,
   jcbz00 )
AS
select z.MXID00,z.MBID00, z.YZLB00, z.CLBZ00, decode(z.ZLSFBZ,'0',z.ZLXMID,'Y',z.ZLXMID,z.SFXMID), z.ZHXMBZ, decode(z.ZLSFBZ,'0',z.ZLXMJC,'Y',z.ZLXMID,z.SFXMMC), z.XSXH00, z.TS0000, 
       z.TSDW00, z.PCID00, z.PCMC00, z.MCSL00, z.DW0000, z.BZ0000,c.LBBH00,z.BBID00,z.BB0000,
       z.CJKS00,z.CJKSMC,c.TZRQ00, z.ZHCXBH,z.JZBZ00,z.YZLX00,z.ZXKS00,Z.ZLSFBZ,Z.SFXMID,
       z.PLXH00,nvl(decode(z.ZLSFBZ,'0',(select sum(nvl(C.TCJE00,b.SFJE00)*c.SFCS00)
       from VW_BM_YYSFXM b, XT_ZLSFGX c where c.ZLXMID = z.ZLXMID AND b.SFXMID = c.SFXMID),
       (select SFJE00 from BM_YYSFXM where SFXMID=z.SFXMID)),0) SFJE00,z.XMZK00,Z.XMJM00,Z.XMJE00,C.JCBZ00
from   XT_ZLMBMX z,BM_ZLZD00 c where c.ZLXMID=z.ZLXMID and c.SFYXSQ='Y' and (z.ZLXMID>100 or z.ZLXMID=0)
       and c.TZRQ00 is null
       and nvl(c.QYRQ00,to_char(sysdate+1,'yyyymmdd'))<=to_char(sysdate,'yyyymmdd')
/


-- End of DDL Script for View SD_HOSPITAL.VW_XT_ZLMBMX