prompt 48.2、门诊日清单明细视图(结账后按国家编码)    VW_SF_RQDMX3
create or replace view VW_SF_RQDMX3 (
   GJBM00,
   JZDH00,
   XM0000,
   MZID00,
   MZH000,
   XMBH00,
   XMMC00,
   XMGG00,
   XMDW00,
   XMDJ00,
   XMSL00,
   HJJE00,
   BH0000,
   FPXMMC,
   SFYBXM,
   ZFBL00,
   QDH000 )
AS
-- MODIFICATION HISTORY
-- Person      Date        Comments
--  XZW        2007.07.12  增加清单日期字段
--  xzw        2009.03.26  条件后面加上g.fbbh00=k.fbbh00 保持记录唯一性
-- zhangyc     2013.02.22  按SF_FYMX00.HBXMMC为空或不为空分开显示 by MZSF-20130220-002
-- jinfl       2013.03.26  中药处方剂量是否不按浓缩比例转化 by MZSF-20130322-001
--zhangyc       2013.11.01 药品国家编码取bm_yd0000.YPBM00 by MZSF-20131025-001
  select
      max(A.GJBM00)  ,    --国家编码
      B.JZDH00	,    --清单号
      K.BRXM00  ,    --姓名
      B.BRID00  ,    --门诊ID
      K.BRBLH0  ,    --门诊号
      max(A.XMBH00)	,    --医疗收费编码
      A.HBXMMC,    --项目名称
      substr(max(trim(C.BZ0000)),1,30),    --备注
      max(C.DW0000)  ,    --单位
      sum(A.HJJE00) XMDJ00	,    --单价
      1	XMSL00,    --数量
      sum(A.HJJE00) ,    --合计金额
      max(D.BH0000)  ,    --发票项目编号
      max(D.XMMC00)  ,    --发票项目名称
      max(decode(k.FBBH00,3,'Y','N'))SFYBXM,--max(E.SFYBXM)  ,    --是否医保项目
      max(A.ZFBL00)*100,
      B.CZRQ00
from  SF_FYMX00 A,SF_BRFY00 B,BM_YYSFXM C,BM_FPXM00 D,BM_BRXXB0 K
where A.DJH000 = B.DJH000
  and B.BRID00 = K.BRID00
  and A.XMBH00 = C.SFXMID
  and C.MZFPID = D.FPXMID
  and A.HBXMMC is not null
  and (A.XMBH00 < 9999999990  or  JMBZ00 <>'0')  --非减免,非优惠的
group  by  B.JZDH00,K.BRXM00,A.HBXMMC,B.BRID00,K.BRBLH0,B.CZRQ00
union all
select
      A.GJBM00  ,    --国家编码
      B.JZDH00	,    --清单号
      K.BRXM00  ,    --姓名
      B.BRID00  ,    --门诊ID
      K.BRBLH0  ,    --门诊号
      A.XMBH00,    --医疗收费编码
      Trim(A.XMMC00),    --项目名称
      substr(trim(C.BZ0000),1,30),    --备注
      C.DW0000  ,    --单位
      A.XMDJ00	,    --单价
      SUM(A.XMSL00)	,    --数量
      SUM(A.HJJE00) ,    --合计金额
      D.BH0000  ,    --发票项目编号
      D.XMMC00  ,    --发票项目名称
      max(decode(k.FBBH00,3,'Y','N'))SFYBXM, --E.SFYBXM  ,    --是否医保项目
      A.ZFBL00*100,
      B.CZRQ00
from  SF_FYMX00 A,SF_BRFY00 B,BM_YYSFXM C,BM_FPXM00 D,BM_BRXXB0 K
where A.DJH000 = B.DJH000
  and B.BRID00 = K.BRID00
  and A.XMBH00 = C.SFXMID
  and C.MZFPID = D.FPXMID
  and a.hbxmmc is  null
  and (A.XMBH00 <  9999999990  or  JMBZ00 <> 0)  --非减免,非优惠的
group  by  B.JZDH00,K.BRXM00,a.GJBM00,A.XMBH00,A.XMMC00,
C.DW0000,C.BZ0000,A.XMDJ00,
      D.BH0000,B.BRID00,K.BRBLH0,D.XMMC00,A.ZFBL00*100,B.CZRQ00,A.HBXMMC
union all
select
      --'',            --国家编码
      (select aa.YPBM00 from BM_YD0000 aa where aa.YPNM00=c.YPNM00 and rownum=1)GJBM00,
      A.JZDH00	,    --清单号
      K.BRXM00  ,    --姓名
      A.BRID00  ,    --门诊ID
      K.BRBLH0  ,    --门诊号
      C.YPNM00  ,    --药品内码
      Trim(C.YPMC00),    --药品名称
      C.YPGG00  ,    --药品规格
      C.CFDW00  ,    --单位
      max(decode((select count(1) from xt_xtcs00 where name00='SF_MZQDSFANSBL' and value0='Y'),'1',nvl((select 1/decode(NSBL00,0,1,NSBL00) from BM_YD0000 where YPNM00=C.YPNM00),1),1))*C.LSDJ00  ,    --单价
      max(decode((select count(1) from xt_xtcs00 where name00='SF_MZQDSFANSBL' and value0='Y'),'1',nvl((select decode(NSBL00,0,1,NSBL00) from BM_YD0000 where YPNM00=C.YPNM00),1),1))*SUM(DECODE(C.SFZBY0,'Y',0,C.YPZSL0)),  --数量,若是自备药,则将其数量改为0
      SUM(round(C.LSDJ00*DECODE(C.SFZBY0,'Y',0,C.YPZSL0)*100)/100), --金额
      F.BH0000  ,    --发票项目编号
      F.XMMC00  ,    --发票项目名称
      max(decode(k.FBBH00,3,'Y','N'))SFYBXM,--G.SFYBXM  ,    --是否医保项目
      C.ZFBL00*100,      --自付比例
      A.CZRQ00
from  SF_BRFY00 A,yf_mzcf00 B,yf_mzcfmx C,BM_YPLB00 D,BM_YYSFXM E,BM_FPXM00 F ,BM_BRXXB0 K
where  A.DJH000 = B.DJH000
  and  A.BRID00 = K.BRID00
  and  B.cflsh0 = C.cflsh0
  and  B.YPDLBH = D.LBBH00
  and  D.SFDM00 = E.SFXMID
  and  E.MZFPID = F.FPXMID
group by  A.JZDH00,K.BRXM00,C.YPNM00,C.YPMC00,C.YPGG00,C.CFDW00,C.LSDJ00,F.BH0000,A.BRID00,K.BRBLH0,F.XMMC00,C.ZFBL00*100,A.CZRQ00
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%