-- Start of DDL Script for View SD_HOSPITAL.VW_SS_GZHCJK
-- Generated 20-6月-2022 17:35:32 from SD_HOSPITAL@orcl

CREATE OR REPLACE VIEW vw_ss_gzhcjk (
   id0000,
   yjdjh0,
   yjksbh,
   bmmc00,
   brid00,
   brxm00,
   kdys00,
   kdysxm,
   kdksbh,
   kdksmc,
   sl0000,
   sfxmid,
   sfje00,
   tm0000,
   hctype,
   yzzt00,
   mrn000 )
AS
select
a.id0000,
a.YJDJH0,
b.yjksbh,
(select BMMC00 from bm_bmbm00 x where x.bmbh00 = b.yjksbh)BMMC00,
b.brid00,
(select BRXM00 from bm_brxxb0 where BRID00 = b.BRID00)BRXM00,
b.kdys00,
b.kdysxm,
b.kdksbh,
(select BMMC00 from bm_bmbm00 x where x.bmbh00 = b.kdksbh)KDKSMC,
b.sl0000,
--(select YPNM00 from bm_wzqtxx where SFXMID = a.SFXMID and rownum = 1) WZID00,
a.sfxmid,
d.SFJE00,                                    --(select SFJE00 from bm_yysfxm where SFXMID = a.SFXMID) DJ0000,
(select tm0000 from SS_GZHCJK where YJDJH0 = a.YJDJH0 and JJID00=A.ID0000 and rownum = 1) TM0000,
decode(nvl(d.sfgz00,'N'),'Y','5','2') HCTYPE,
decode(CXDJH0,null,'10','00')YZZT00,
(select mrn000 from bm_brxxb0 where brid00=b.brid00) mrn000   ---2022.06.20 spd接口新增病案号字段(林术修改)
from YJ_YWJJ00 A,yj_yw0000 b,bm_yysfxm d  --,ss_yw0000 c
where  a.yjdjh0 = b.yjdjh0   --and a.ssdh00=c.SSDH00
 and  A.SFXMID = d.SFXMID
 and  nvl(d.sfgz00,'N') = 'Y'  ---2022.06.02 林术增加判断不传非高值耗材的项目
 --and  exists ( select 1 from SS_GZHCJK where YJDJH0 = A.YJDJH0 and JJID00 = a.ID0000 ) ----2022.05.12 林术新增条件 此条件会导致退费的时候提示无法找到表1
/


-- End of DDL Script for View SD_HOSPITAL.VW_SS_GZHCJK