-- Start of DDL Script for View SD_HOSPITAL.VW_ZY_RQDMX5
-- Generated 25-十一月-2021 12:53:23 from SD_HOSPITAL@zpxzyy

CREATE OR REPLACE VIEW vw_zy_rqdmx5 (
   qdh000,
   czsj00,
   zyid00,
   zyh000,
   xmbh00,
   xmmc00,
   xmgg00,
   xmdw00,
   xmdj00,
   xmsl00,
   hjje00,
   bh0000,
   zfbl00,
   zfje00,
   jzje00,
   fpxmmc,
   gjbm00,
   jzdh00,
   gjbm01,
   sfyp00,
   gfje00,
   sfxmid,
   yblb00,
   yexm00,
   xxgg01 )
AS
select
      A.QDRQ00	,    --清单号
      --'00:00:00',    --操作时间
      A.CZRQ00  ,    --操作日期
      A.ZYID00  ,    --住院ID
      A.ZYH000  ,    --住院号
      max(B.XMBH00)	,    --医疗收费编码
      SUBSTRB(B.HBXMMC,1,20) XMMC00,    --项目名称
      ' ',    --药品规格
      max(C.DW0000),    --单位
      Sum(B.HJJE00),  --单价
      1,    --数量
      sum(B.HJJE00) ,    --合计金额
      max(D.BH0000)  ,    --发票项目编号
      max(F.ZFBL00*100)  ,    --自付比率
      sum(B.ZFJE00) ,        --自付金额
      sum(B.JZJE00) ,
      max(D.XMMC00)  ,
      max(C.GJBM00)  ,
      A.JZDH00 ,    --结算单号
      max(trim(B.GJBM00)),
      'N',
      sum(B.GFJE00),
      max(C.SFXMID),
      max(decode(F.YBLB00,'4','甲类','5','乙类','6','民族','7','非医保','8','医保','') ),
      A.YEXM00,
      ' '     --药品规格
 from ZY_BRFY00 A,ZY_FYMX00 B,BM_YYSFXM C,BM_FPXM00 D,BM_BRXXB0 E,VW_BM_YBSFDY F
where A.DJH000 = B.DJH000
  and B.XMBH00 = C.SFXMID
  and C.ZYFPID = D.FPXMID
  and A.BRID00 = E.BRID00
  and B.XMBH00 = F.SFXMID
  and F.FBBH00 = 3
  AND F.YBBRLB='0'
  AND F.YBZXLB='1'
  and F.SFYP00 ='N'
  and (B.XMBH00 < 9999999990  or  JMBZ00 <> 0)  --非减免,非优惠的
  and B.HBXMMC is not null
group by A.QDRQ00,A.CZRQ00,A.ZYID00,A.ZYH000,B.HBXMMC,A.JZDH00,A.YEXM00
union all
select
      A.QDRQ00	,    --清单号
      --'00:00:00',    --操作时间
      A.CZRQ00  ,    --操作日期
      A.ZYID00  ,    --住院ID
      A.ZYH000  ,    --住院号
      B.XMBH00	,    --医疗收费编码
      substrb(B.XMMC00,1,20) XMMC00,    --项目名称
      substrb(decode(C.JSXM00,'2',C.GG0000,C.BZ0000),1,20) BZ0000,    --药品规格
      substrb(C.DW0000,1,10) DW0000,    --单位
      B.XMDJ00	,    --单价
      sum(B.XMSL00)	,    --数量
      sum(B.HJJE00) ,    --合计金额
      D.BH0000  ,    --发票项目编号
      F.ZFBL00*100  ,    --自付比率
      sum(B.ZFJE00) ,        --自付金额
      sum(B.JZJE00) ,
      D.XMMC00  ,
      C.GJBM00  ,
      A.JZDH00 ,    --结算单号
      trim(B.GJBM00),
      'N',
      sum(B.GFJE00),C.SFXMID,
      decode(F.YBLB00,'4','甲类','5','乙类','6','民族','7','非医保','8','医保','') ,
      A.YEXM00,
      substrb(decode(C.JSXM00,'2',C.GG0000,C.XMNH00),1,20) XMNH00     --药品规格/项目内涵
from  ZY_BRFY00 A,ZY_FYMX00 B,BM_YYSFXM C,BM_FPXM00 D,BM_BRXXB0 E,VW_BM_YBSFDY F
where A.DJH000 = B.DJH000
  and B.XMBH00 = C.SFXMID
  and C.ZYFPID = D.FPXMID
  and A.BRID00 = E.BRID00
  and B.XMBH00 = F.SFXMID
  and F.FBBH00 = 3
  --AND E.ybzxlb = '1'
  --and F.YBLB00='1'
  AND F.YBBRLB='0'
  AND F.YBZXLB='1'
  and F.SFYP00 ='N'
  and (B.XMBH00 < 9999999990  or  JMBZ00 <> 0)  --非减免,非优惠的
  and B.HBXMMC is null
group by D.XMMC00,A.QDRQ00,A.CZRQ00,A.ZYID00,A.ZYH000,B.XMBH00,B.XMMC00,B.GJBM00,D.BH0000,F.ZFBL00,
         decode(C.JSXM00,'2',C.GG0000,C.BZ0000),C.DW0000,B.XMDJ00,C.GJBM00,C.SFXMID,A.JZDH00,
         decode(F.YBLB00,'4','甲类','5','乙类','6','民族','7','非医保','8','医保',''),A.YEXM00,
         decode(C.JSXM00,'2',C.GG0000,C.XMNH00)
union all
select
      A.QDRQ00	,    --清单号
      --'00:00:00',
      A.CZRQ00  ,    --操作日期
      A.ZYID00  ,    --住院ID
      A.ZYH000  ,    --住院号
      C.YPNM00  ,    --药品内码
      substrb(C.YPMC00,1,20) YPMC00,    --药品名称
      substrb(C.YPGG00,1,20) YPGG00,    --药品规格
      substrb(C.CFDW00,1,10) CFDW00,    --单位
      C.LSDJ00  ,    --单价
      sum(C.YPZSL0)  ,        --数量
      sum(round(C.LSDJ00*C.YPZSL0,2)), --金额
      F.BH0000  ,    --发票项目编号
      H.ZFBL00*100,    --自付比率
      sum(round(C.ZFJE00,2)),       --自付金额
      sum(round(C.LSDJ00*C.YPZSL0-C.ZFJE00-C.GFJE00,2)) ,
      F.XMMC00,
      '',
      A.JZDH00,       --结算单号
      '',
      'Y',
      round(sum(decode(C.SFZBY0,'Y',0,C.GFJE00)),2) --减免金额
      ,C.YPNM00,
      decode(H.YBLB00,'4','甲类','5','乙类','6','民族','7','非医保','8','医保','') ,
      A.YEXM00,
      substrb(C.YPGG00,1,20) YPGG01     --药品规格
 from ZY_BRFY00 A,yf_zycf00 B,yf_zycfmx C ,BM_YPLB00 D,BM_YYSFXM E,BM_FPXM00 F,BM_BRXXB0 G,VW_BM_YBSFDY H,IC_YBBRLB I,BM_YBBRLB J  --yf_zycfmx没有含有中药帖数
where A.DJH000 = B.DJH000
  and B.cflsh0 = C.cflsh0
  and B.YPDLBH = D.LBBH00
  and D.SFDM00 = E.SFXMID
  and E.ZYFPID = F.FPXMID
  and A.BRID00 = G.BRID00
  AND C.YPNM00 = H.SFXMID
  and H.FBBH00 = I.FBBH00
  and H.YBZXLB = J.YBZXLB
  and J.YBZXLB = I.YBZXLB
  and H.YBBRLB = J.YBBRLB
  and I.YBLB00='1'
  AND J.YBBRLB='0'
 /* and  H.FBBH00 = 3
  AND  H.YBBRLB='0'
  AND  H.YBZXLB='1'*/
  and H.SFYP00 ='Y'
group by F.XMMC00 ,A.QDRQ00,A.CZRQ00,A.ZYID00,A.ZYH000,C.YPNM00,C.YPMC00,C.YPGG00,C.CFDW00,C.LSDJ00,F.BH0000,H.ZFBL00,C.YPNM00,A.JZDH00,
         decode(H.YBLB00,'4','甲类','5','乙类','6','民族','7','非医保','8','医保',''),A.YEXM00
union all
select
      A.QDRQ00	,    --清单号
      --'00:00:00',
      A.CZRQ00  ,    --操作日期
      A.ZYID00  ,    --住院ID
      A.ZYH000  ,    --住院号
      B.YPNM00  ,    --药品内码
      substrb(B.YPMC00,1,20) YPMC00,    --药品名称
      substrb(B.YPGG00,1,20) YPGG00,    --药品规格
      substrb(B.QLDW00,1,10) QLDW00,    --单位
      B.LSDJ00  ,    --单价
      sum(B.YPZSL0)  ,        --数量
      sum(round(B.LSDJ00*B.YPZSL0,2)), --金额
      F.BH0000  ,    --发票项目编号
      H.ZFBL00*100  ,    --自付比率
      sum(round(B.ZFJE00,2)),       --自付金额
      sum(round(B.LSDJ00*B.YPZSL0-B.ZFJE00-B.GFJE00,2)) ,
      F.XMMC00,
      '',
      A.JZDH00,       --结算单号
      '',
      'Y',
      sum(B.GFJE00) --减免金额
      ,B.YPNM00,
      decode(H.YBLB00,'4','甲类','5','乙类','6','民族','7','非医保','8','医保','') ,
      A.YEXM00,
      substrb(B.YPGG00,1,20) YPGG01    --药品规格
 from ZY_BRFY00 A,yf_yzypsq B,BM_YPLB00 D,BM_YYSFXM E,BM_FPXM00 F,BM_BRXXB0 G,VW_BM_YBSFDY H,IC_YBBRLB I,BM_YBBRLB J   --yf_yzypsq的总数量有含有中药的帖数
where A.DJH000 = B.DJH000
  and B.YPDLBH = D.LBBH00
  and D.SFDM00 = E.SFXMID
  and E.ZYFPID = F.FPXMID
  and A.BRID00 = G.BRID00
  and B.YPNM00 = H.SFXMID
  and H.FBBH00 = I.FBBH00
  and H.YBZXLB = J.YBZXLB
  and J.YBZXLB = I.YBZXLB
  and H.YBBRLB = J.YBBRLB
  and I.YBLB00='1'
  AND J.YBBRLB='0'
/*  and H.FBBH00 = 3
   AND H.YBBRLB = '0'
  AND  H.YBZXLB='1'*/
  and H.SFYP00 ='Y'
group by F.XMMC00 ,A.QDRQ00,A.CZRQ00,A.ZYID00,A.ZYH000,B.YPNM00,B.YPMC00,B.YPGG00,B.QLDW00,B.LSDJ00,F.BH0000,H.ZFBL00,B.YPNM00,A.JZDH00,
         decode(H.YBLB00,'4','甲类','5','乙类','6','民族','7','非医保','8','医保',''),A.YEXM00
/


-- End of DDL Script for View SD_HOSPITAL.VW_ZY_RQDMX5
