CREATE OR REPLACE VIEW SD_HOSPITAL.VW_BQ_YBFYYZ
 (
 ZLXMID, LBBH00, ZLXMJC, ZLXMBH, ZYZBID, PYSM00, WBSM00, DW0000, FBBH00, 
YBZXLB, YBLBMC, YBBRLB, SFJE00, ZFBL00, COLOUR, SFSFXM, XMZHSM, MRBBID, 
ZHXMBZ, XEJSXM

 )
 AS 
select a.ZLXMID,--项目类别ID
           a.LBBH00,--类别编号
           a.ZLXMJC,--项目简称
           a.ZLXMBH,--诊疗项目编号
           a.ZYZBID,--专业组别
           a.PYSM00,--拼音首码
           a.WBSM00,--五笔首码
           a.DW0000,--单位
           bb.FBBH00+0 FBBH00,--费别编号
           bb.YBZXLB,--医保中心类别
           decode(Round(sum(decode(NVL(cc.TCJE00,aa.SFJE00),null,1,0,1,NVL(cc.TCJE00,aa.SFJE00))*cc.SFCS00*bb.ZFBL00)/sum(decode(NVL(cc.TCJE00,aa.SFJE00),null,1,0,1,NVL(cc.TCJE00,aa.SFJE00))*cc.SFCS00),2),0,'医保',1,'非医保','部分医保') YBLBMC,
           bb.YBBRLB,--医保病人类别
           sum(NVL(cc.TCJE00,aa.SFJE00) * cc.SFCS00) SFJE00,--单价
           Round(sum(decode(NVL(cc.TCJE00,aa.SFJE00),null,1,0,1,NVL(cc.TCJE00,aa.SFJE00))*cc.SFCS00*bb.ZFBL00)/sum(decode(NVL(cc.TCJE00,aa.SFJE00),null,1,0,1,NVL(cc.TCJE00,aa.SFJE00))*cc.SFCS00),2)*100||'%' ZFBL00,--自付比例
           DECODE(bb.YBZXLB,'0','0',DECODE(Round(sum(decode(aa.SFJE00,null,1,0,1,aa.SFJE00)*cc.SFCS00*bb.ZFBL00)/sum(decode(aa.SFJE00,null,1,0,1,aa.SFJE00)*cc.SFCS00),2),1,'$0000FF',''))COLOUR,
           'Y' SFSFXM,--是否收费项目
           a.XMZHSM,   --项目综合说明
           a.MRBBID,   --默认标本ID
           a.ZHXMBZ,
           a.XEJSXM
    from VW_BQ_ZLZD00 a,
         VW_BM_YYSFXM aa,
         VW_BM_YBSFDY bb,
         VW_XT_ZLJGMX cc
    where bb.SFXMID = aa.SFXMID
      and bb.SFYP00 = 'N'
      and cc.SFXMID = aa.SFXMID
      and cc.ZLXMID = a.ZLXMID
      and cc.GDBZ00='Y'
      and a.ZLXMJC not like '检验%'
    group by a.ZLXMID, --项目类别ID
             a.LBBH00, --类别编号
             a.ZYZBID, --专业组别
             a.ZLXMJC, --项目简称
             a.ZLXMBH, --诊疗项目编号
             a.PYSM00, --拼音首码
             a.WBSM00, --五笔首码
             a.DW0000, --单位
             a.XMZHSM, --项目综合说明
             a.MRBBID,
             BB.FBBH00,
             bb.YBZXLB,--医保中心类别
             bb.YBBRLB, --医保病人类别
             a.ZHXMBZ,
             a.XEJSXM
    Union all
    select ZLXMID,--项目类别ID
           LBBH00,--类别编号
           ZLXMJC,--项目简称
           ZLXMBH,--诊疗项目编号
           ZYZBID,--专业组别
           PYSM00,--拼音首码
           WBSM00,--五笔首码
           DW0000,--单位
           1 FBBH00,
           null YBZXLB,--医保中心类别
           '非收费' YBLBMC,--
           null YBBRLB,--医保病人类别
           0 SFJE00,--单价
           null ZFBL00,--自付比例
           '0' COLOUR,--
           'N' SFSFXM,--是否收费项目
           ''  XMZHSM, --项目综合说明
           MRBBID,
           a.ZHXMBZ,
           a.XEJSXM
    from VW_BQ_ZLZD00 a
    where not exists (select ZLXMID from XT_ZLSFGX where ZLXMID = a.ZLXMID) and a.ZLXMJC not like '检验%'
/