prompt 104  住院日清单明细视图(国家编码)_特殊(福清市医院)  VW_ZY_RQDMX1_TS_1
create or replace  view VW_ZY_RQDMX1_TS_1
(
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- lintj    2012.12.31     ZFBL00,ZFJE00-->ZYZFBL,ZYZFJE
-- jlg      2016.11.26     11g写法修正
    ZYID00,   --住院ID
    ZYH000,   --住院号
    QDRQ00,   --清单日期
    GJBM00,   --国家编码(BM_YYSFXM.GJBM00) 显示用
    GJBM01,   --国家编码(ZY_FYMX00.GJBM00) 排序用
    XMBH00,   --项目或药品内部号
    XMMC00,   --项目或药品名称
    XMGG00,   --项目或药品规格
    XMDW00,   --项目或药品单位
    XMDJ00,   --单价
    XMSL00,   --数量
    HJJE00,   --合计金额
    ZFJE00,   --自付金额
    ZFBL00,   --自付比例
    FBBH00,   --费别编号
    YBZXLB,   --医保中心类别
    YBBRLB    --医保病人类别
)
as
   select
          b.zyid00 ,
          b.zyh000 ,
          B.QDRQ00 ,
          G.GJBM00 , --显示用
          A.GJBM00 , --排序用
          A.XMBH00 ,
          A.XMMC00 ,
          A.XMGG00 ,
          A.XMDW00 ,
          DECODE(SIGN(A.XMDJ00-1),-1,'0','')||to_char(A.XMDJ00) XMDJ00,
          SUM(A.XMSL00) ,
          SUM(A.XMSL00*A.XMDJ00) HJJE00,
          SUM(A.XMSL00*A.XMDJ00*F.ZYZFBL) ZFJE00,
          F.ZYZFBL*100 ,
          F.FBBH00 ,
          F.YBZXLB ,
          F.YBBRLB
   from  ZY_FYMX00 A ,ZY_BRFY00 B ,VW_BM_YBSFDY F,BM_YYSFXM G
       where A.DJH000 = B.DJH000
         and F.SFYP00 = 'N'
         and A.XMBH00 = F.SFXMID
         AND A.XMBH00 = G.SFXMID
         and a.xmbh00<9999999990
   group by B.ZYID00,B.ZYH000,B.QDRQ00,G.GJBM00,A.GJBM00,A.XMBH00,A.XMMC00,A.XMGG00,A.XMDW00,
            DECODE(SIGN(A.XMDJ00-1),-1,'0','')||TO_CHAR(A.XMDJ00),F.ZYZFBL,F.FBBH00,F.YBZXLB,F.YBBRLB
union all
      select
          A.ZYID00,
          A.ZYH000,
          A.QDRQ00,
          '',
          '',
          C.YPNM00,
          C.YPMC00,
          C.YPGG00,
          C.CFDW00,
          DECODE(SIGN(C.LSDJ00-1),-1,'0','')||TO_CHAR(C.LSDJ00) XMDJ00,
          SUM(C.YPZSL0),
          SUM(round(C.LSDJ00*C.YPZSL0,2)) HJJE00,
          SUM(round(C.LSDJ00*C.YPZSL0,2)*J.ZYZFBL) ZFJE00,
          J.ZYZFBL*100,
          J.FBBH00,
          J.YBZXLB,
          J.YBBRLB
   from  ZY_BRFY00 A,yf_zycf00 B,yf_zycfmx C,VW_BM_YBSFDY J
       where A.DJH000 = B.DJH000
         and B.cflsh0 = C.cflsh0
         AND J.SFYP00 = 'Y'
         AND J.SFXMID = C.YPNM00
    group by A.ZYID00,A.ZYH000,A.QDRQ00,C.YPNM00,C.YPMC00,C.YPGG00,C.CFDW00,DECODE(SIGN(C.LSDJ00-1),-1,'0','')||TO_CHAR(C.LSDJ00),
             J.ZYZFBL,J.FBBH00,J.YBZXLB,J.YBBRLB
union all
     select
          A.ZYID00,
          A.ZYH000,
          A.QDRQ00,
          '',
          '',
          B.YPNM00,
          B.YPMC00,
          B.YPGG00,
          B.QLDW00,
          DECODE(SIGN(B.LSDJ00-1),-1,'0','')||TO_CHAR(B.LSDJ00) XMDJ00,
          SUM(B.YPZSL0),
          SUM(round(B.LSDJ00*B.YPZSL0,2)) HJJE00,
          SUM(round(B.LSDJ00*B.YPZSL0,2)*F.ZYZFBL) ZFJE00,
          F.ZYZFBL*100,
          F.FBBH00,
          F.YBZXLB,
          F.YBBRLB
 from  ZY_BRFY00 A,YF_YZYPSQ B,VW_BM_YBSFDY F
      where A.DJH000 = B.DJH000
        AND F.SFYP00 = 'Y'
        AND F.SFXMID = B.YPNM00
  group by A.ZYID00,A.ZYH000,A.QDRQ00,B.YPNM00,B.YPMC00,B.YPGG00,B.QLDW00,DECODE(SIGN(B.LSDJ00-1),-1,'0','')||TO_CHAR(B.LSDJ00),
           F.ZYZFBL,F.FBBH00,F.YBZXLB,F.YBBRLB
  ;