-- Start of DDL Script for View SD_HOSPITAL.VW_CZK_HISKD
-- Generated 2021-11-22 16:38:07 from SD_HOSPITAL@fw2

CREATE OR REPLACE VIEW vw_czk_hiskd (
   djh000,
   kdksbh,
   xmzt00,
   kdrq00,
   zyhghh,
   zlxmid,
   zlxmjc,
   sl0000,
   zje000,
   kdysxm,
   yjdjh0,
   yyid00,
   brid00,
   yjksbh,
   kdksmc,
   yjksmc )
AS
select
b.djh000,
a.KDKSBH,
a.XMZT00,
a.kdrq00,
a.ZYHGHH,
a.ZLXMID,
a.ZLXMJC,
--a.SL0000,
a.SL0000-a.cxsl00,
--a.ZJE000,
(A.ZJE000-ABS(NVL(E.ZJE000,0))) ZJE000,
a.KDYSXM,
a.YJDJH0,
d.YYID00,
a.BRID00,
a.YJKSBH,
(select BMMC00 from BM_BMBM00 where BMBH00=a.KDKSBH) KDKSMC,
(select BMMC00 from BM_BMBM00 where BMBH00=a.YJKSBH) YJKSMC
   from yj_yw0000 a,sf_brfy00 b,BM_BMBM00 d,YJ_YW0000 E
where b.djh000=a.sfdjh0 and a.kdksbh=d.bmbh00 AND A.yjdjh0=E.cxdjh0(+)
--and a.cxsl00='0'
 and a.sl0000>'0'
and A.zlxmid not in ('21060','21061','21062','21063') and a.mzzybz='0'
union all
select
b.djh000,
a.KDKSBH,
a.XMZT00,
a.kdrq00,
a.ZYHGHH,
a.ZLXMID,
a.ZLXMJC,
--a.SL0000,
a.SL0000-a.cxsl00,
--a.ZJE000,
(A.ZJE000-ABS(NVL(E.ZJE000,0))) ZJE000,
a.KDYSXM,
a.YJDJH0,
d.YYID00,
a.BRID00,
a.YJKSBH,
(select BMMC00 from BM_BMBM00 where BMBH00=a.KDKSBH) KDKSMC,
(select BMMC00 from BM_BMBM00 where BMBH00=a.YJKSBH) YJKSMC
   from yj_yw0000 a,zy_brfy00 b,BM_BMBM00 d,YJ_YW0000 E
where b.djh000=a.sfdjh0 and a.kdksbh=d.bmbh00 AND A.yjdjh0=E.cxdjh0(+)
--and a.cxsl00='0'
and a.sl0000>'0'
and A.zlxmid not in ('21060','21061','21062','21063','6753','6754') and a.mzzybz='1' and a.kdrq00>='20131008'
/


-- End of DDL Script for View SD_HOSPITAL.VW_CZK_HISKD