-- Start of DDL Script for View SD_HOSPITAL.VW_YJ_YBFYYZ_DJBZ
-- Generated 27-10月-2022 11:13:26 from SD_HOSPITAL@phxyy

-- Drop the old instance of VW_YJ_YBFYYZ_DJBZ
CREATE OR REPLACE VIEW vw_yj_ybfyyz_djbz (
   zlxmid,
   lbbh00,
   lbbhmc,
   zlxmjc,
   zlxmbh,
   pysm00,
   wbsm00,
   dw0000,
   bbid00,
   bwbh00,
   bqzy00,
   mdsfbt,
   mdsfbt_mc,
   jclbid,
   ybzxlb,
   yblbmc,
   ybbrlb,
   sfje00,
   zfbl00,
   fbbh00,
   sfsfxm,
   colour,
   mzzybz,
   sfbzdj,
   fyid00,
   sfyxsq )
AS
select     
    a.ZLXMID,                                      --项目类别ID
    a.LBBH00,                                      --类别编号
    a.LBBHMC,                                      --类别名称    
    a.ZLXMJC,                                      --项目简称
    a.ZLXMBH,                                      --诊疗项目编号
    a.PYSM00,                                      --拼音首码
    a.WBSM00,                                      --五笔首码
    a.DW0000,                                      --单位
    a.BBID00,                                      --标本号    
    a.BWBH00,                                      --部位编号，多个用逗号分隔
    a.BQZY00,                                      --项目开单目的（或称检查目的、病情摘要）
    a.MDSFBT,                                      --开单目的是否必填 0不必填 1必填，默认0        
    a.MDSFBT_MC,                                   --开单目的是否必填_名称  
    a.JCLBID,                                      --检查类别ID
    c.YBZXLB,                                      --医保中心类别
   --decode(round(sum(decode(nvl(d.TCJE00,b.SFJE00),null,1,0,1,nvl(d.TCJE00,b.SFJE00))*d.SFCS00*c.ZFBL00)/sum(decode(nvl(d.TCJE00,b.SFJE00),null,1,0,1,nvl(d.TCJE00,b.SFJE00))*d.SFCS00),2),0,'医保',1,'非医保','部分医保') as YBLBMC,
   decode(decode (sum(decode(nvl(d.TCJE00,b.SFJE00),null,1,0,1,nvl(d.TCJE00,b.SFJE00))*d.SFCS00),0,0,
   round(sum(decode(nvl(d.TCJE00,b.SFJE00),null,1,0,1,nvl(d.TCJE00,b.SFJE00))*d.SFCS00*c.ZFBL00)/sum(decode(nvl(d.TCJE00,b.SFJE00),null,1,0,1,nvl(d.TCJE00,b.SFJE00))*d.SFCS00),2)),0,'医保',1,'非医保','部分医保') as YBLBMC,--why20221027
    c.YBBRLB,                                          --医保病人类别
    sum(nvl(d.TCJE00,b.SFJE00) * d.SFCS00) as SFJE00,  --单价
     --round(sum(decode(nvl(d.TCJE00,b.SFJE00),null,1,0,1,nvl(d.TCJE00,b.SFJE00))*d.SFCS00*c.ZFBL00)/sum(decode(nvl(d.TCJE00,b.SFJE00),null,1,0,1,nvl(d.TCJE00,b.SFJE00))*d.SFCS00),2)*100||'%' as ZFBL00,--自付比例
     decode (sum(decode(nvl(d.TCJE00,b.SFJE00),null,1,0,1,nvl(d.TCJE00,b.SFJE00))*d.SFCS00),0,0,
     round(sum(decode(nvl(d.TCJE00,b.SFJE00),null,1,0,1,nvl(d.TCJE00,b.SFJE00))*d.SFCS00*c.ZFBL00)/sum(decode(nvl(d.TCJE00,b.SFJE00),null,1,0,1,nvl(d.TCJE00,b.SFJE00))*d.SFCS00),2))*100||'%' as ZFBL00,--自付比例 why20221027
    c.FBBH00+0 as FBBH00,                              --
    'Y' as SFSFXM,                                     --是否收费项目
  --decode(round(sum(decode(b.SFJE00,null,1,0,1,b.SFJE00)*d.SFCS00*c.ZFBL00)/sum(decode(b.SFJE00,null,1,0,1,b.SFJE00)*d.SFCS00),2),1,'$0000FF','') as COLOUR,
    decode(decode(sum(decode(b.SFJE00,null,1,0,1,b.SFJE00)*d.SFCS00),0,0,
  round(sum(decode(b.SFJE00,null,1,0,1,b.SFJE00)*d.SFCS00*c.ZFBL00)/sum(decode(b.SFJE00,null,1,0,1,b.SFJE00)*d.SFCS00),2)),1,'$0000FF','') as COLOUR, --why20221027
    a.MZZYBZ,                                           --
    b.SFBZDJ,                                           --
    nvl(a.FYID00,'0') as FYID00,                        --分院ID
    a.SFYXSQ                                            --  
from VW_BM_ZLZD00 a,
     VW_BM_YYSFXM_DJBZ b,
     VW_BM_YBSFDY c,
     XT_ZLSFGX d
where c.SFXMID = b.SFXMID
  and c.SFYP00 = 'N'
  and d.SFXMID = b.SFXMID
  and d.ZLXMID = a.ZLXMID       --and a.SFYXSQ='Y' 
  and a.MZZYBZ not in ('4','5')
group by a.ZLXMID,       --项目类别ID
         a.LBBH00,       --类别编号
         a.LBBHMC,       --类别名称
         a.ZLXMJC,       --项目简称
         a.ZLXMBH,       --诊疗项目编号
         a.PYSM00,       --拼音首码
         a.WBSM00,       --五笔首码
         a.DW0000,       --单位
         a.BBID00,       --标本ID
         a.BWBH00,       --部位编号
         a.BQZY00,       --项目开单目的（或称检查目的、病情摘要）
         a.MDSFBT,       --开单目的是否必填 0不必填 1必填，默认0             
         a.MDSFBT_MC,    --开单目的是否必填_名称  
         a.JCLBID,       --检查类别ID
         c.YBZXLB,       --医保中心类别
         c.FBBH00,       --费别编号
         c.YBBRLB,       --医保病人类别
         a.MZZYBZ,       --门诊住院标志
         b.SFBZDJ,       --
         a.FYID00,       --
         a.SFYXSQ        --
union all
select 
    a.ZLXMID,                                      --项目类别ID
    a.LBBH00,                                      --类别编号
    a.LBBHMC,                                      --类别名称    
    a.ZLXMJC,                                      --项目简称
    a.ZLXMBH,                                      --诊疗项目编号
    a.PYSM00,                                      --拼音首码
    a.WBSM00,                                      --五笔首码
    a.DW0000,                                      --单位
    a.BBID00,                                      --标本号    
    a.BWBH00,                                      --部位编号
    a.BQZY00,                                      --项目开单目的（或称检查目的、病情摘要）
    a.MDSFBT,                                      --开单目的是否必填 0不必填 1必填，默认0        
    a.MDSFBT_MC,                                   --开单目的是否必填_名称  
    a.JCLBID,                                      --检查类别ID
    null as YBZXLB,                                --医保中心类别
    null as YBLBMC,                                --医保中心类别名称
    null as YBBRLB,                                --医保病人类别
    0 as SFJE00,                                   --单价
    '100%' as ZFBL00,                              --自付比例
    1 as FBBH00,                                   --
    'N' as SFSFXM,                                 --是否收费项目
    '0' as COLOUR,                                 --
    '2' as MZZYBZ,                                 --
    b.SFBZDJ,                                      --
    nvl(a.FYID00,'0') as FYID00,                   --分院ID
    a.SFYXSQ                                       --
from VW_BM_ZLZD00 a,VW_XT_YYXX00_DJBZ b
where not exists (select ZLXMID from XT_ZLSFGX where ZLXMID=a.ZLXMID)--and a.SFYXSQ='Y' 
  and a.MZZYBZ not in ('4','5')
/


-- End of DDL Script for View SD_HOSPITAL.VW_YJ_YBFYYZ_DJBZ