CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YS_YJCFHISTORY_MZDZBL
 (
 CFBZ00, CFLSH0, CFLB00, CFLBMC, CFMC00, YPNM00, XMYFID, XMYFMC, YPCZFL, 
MXID00, ZB0000, YPGG00, PCID00, PCMC00, ZFJE00, GFJE00, YPDLBHMX, ZFBL00, 
YBLB00, YBLBMC, YPZSL0, CFDW00, CSL000, JLDW00, YYSJ00, YYSJDW, ZXCS00, 
YYZF00, LSDJ00, SFZBY0, YFSMID, ZHL000, ZQCS00, F2J000, YPYFMC, YFID00, 
PSLBID, PSMC00, YSXM00, SL0000, CFZJE0, KDYSXM, KDKSMC, ZXKSMC, CFZT00, 
CXCFH0, GHH000, GHID00, BRID00, KDRQSJ, KDRQ00, DJH000, BZ0000, KDYS00, 
SFLB00, ZT0000, ZT0001, YJFZH0, RLRY00, ICKH00, CFXZ00, CFXZMC, CXBZ00, 
THBZ00, THBZMC, THYY00, YFBMBH, BRXM00, XB0000, BRLB00, YSGZH0, SSKS00, 
GHKS00, ZYTS00, BZID00, BZMC00, FJFDJH, CZY000, SRRQ00, SRSJ00, CFMBMC, 
BRDZ00, ZYDJS0, TSBZBH, TSBZMC, ZYSFNJ, ZYNJJL, ZYSFJJ, CFDGS0, ZYMRCS, 
ZJZFID, ZJZFMC, ZYMRJS, JYFS00, BRCSRQ, ZDID00, YJDJH0, BWMC00, SFJE00, 
ZDMC00, YJKSMC, JZBZ00, DCBZ00, TSBSMC, YZCFID, CXDJH1, BBID00, KDSJ00, 
ZXRXM0, ZXRQ00, ZXSJ00, KFRXM0, KFRQ00, KFSJ00, SFYSLR, YPBM00, DJFSMC, 
M2J000, ZJMZCF, K2J000, QZFS00, ZDXL00, HJJE00, FJDJBZ, CFSM00, MAXTS0, 
MAXZL0, CDTSYY, CDZLYY, CSLDW0, MZDJ00, DPDJ00, SJDJ00, MZKFDW, MZFYDW, 
ZJJLDW, KCDW00, YDJLDW, YDF2J0, ZJJL2J, YYMBID, LRJMLB, SQID00, TSYS00, 
YWJJID, YWJJJE, SFXMID, LJID00, BRZXJD, JHMXID, LJYZMX, BYYY00, CXSL00, 
XMZK00, TCID00, JYFABM, BZL000, MZFPID, TZRQ00, DBRXM0, DBRXB0, DBRSFZ, 
ZFFSBZ, BZIDS0, BZMCS0, DJID00, DJMXID, SFPS00, ZXYSZ0, CJKSBH, BQZY00, 
SFKSLY, XDFBZ0, SFFR00, DCNYJ0, GJBZBM, SFZF00, WGBZMC, SM0000

 )
 AS 
select
  '1'cfbz00,
  a.cflsh0,
  a.ypdlbh,
  decode(f.cfzt00,'b','处方流转', decode(d.ypdlbh,'0','西药','1','成药','2','中药')) cflbmc,
  e.ypmc00 cfmc00,
  d.ypnm00,
  g.xmyfid,
  d.xmyfmc,
  e.ypczfl,
  d.cfid00 mxid00,
  d.zb0000,
  d.YPGG00,
  nvl(g.PCID00,0),
  d.pcmc00,
  round(d.hjje00*d.zfbl00,2),
  d.GFJE00,
  d.ypdlbh ypdlbhmx,
  d.zfbl00,
  d.yblb00,
  DECODE ( d.yblb00, '4','甲类','5','乙类','6','民族用药','7','非医保'),
  --decode(e.qzfs00,'3',round(g.ypzsl0*DECODE(g.cfdw00,e.zyfydw,e.z2j000,e.mzfydw,e.m2j000,e.zjjldw,e.zjjl2j,e.mzkfdw,e.f2j000,e.kcdw00,e.k2j000,e.jldw00,1,g.zhl000)/DECODE(g.cfdw00,e.kcdw00,e.k2j000,e.m2j000),4),CEIL(g.ypzsl0*DECODE(g.cfdw00,e.zyfydw,e.z2j000,e.mzfydw,e.m2j000,e.zjjldw,e.zjjl2j,e.mzkfdw,e.f2j000,e.kcdw00,e.k2j000,e.jldw00,1,g.zhl000)/DECODE(g.cfdw00,e.kcdw00,e.k2j000,e.m2j000))) ypzsl0,
  --DECODE(d.cfdw00,e.kcdw00,e.kcdw00,e.mzfydw)cfdw00,
  case
     when ((select VALUE0 from XT_XTCS00 s  where NAME00='YS_MJZYF0' and instr( s.VALUE0,a.yfbmbh)>0)is not null) and
    ((select VALUE0 from XT_XTCS00 s  where NAME00='YS_MJZYFYPBM' and instr( s.VALUE0,D.YPNM00)>0) is not null)  then
     d.ypzsl0
     else
      decode(e.qzfs00,'3',round(nvl(g.ypzsl0,d.ypzsl0)*decode(nvl(g.cfdw00,d.cfdw00),e.zyfydw,e.z2j000,e.mzfydw,e.m2j000,e.zjjldw,e.zjjl2j,e.mzkfdw,e.f2j000,e.kcdw00,e.k2j000,e.jldw00,1,nvl(g.zhl000,d.zhl000))/DECODE(nvl(g.cfdw00,d.cfdw00),e.kcdw00,e.k2j000,e.m2j000),4),CEIL(nvl(g.ypzsl0,d.ypzsl0)*DECODE(nvl(g.cfdw00,d.cfdw00),e.zyfydw,e.z2j000,e.mzfydw,e.m2j000,e.zjjldw,e.zjjl2j,e.mzkfdw,e.f2j000,e.kcdw00,e.k2j000,e.jldw00,1,nvl(g.zhl000,d.zhl000))/DECODE(nvl(g.cfdw00,d.cfdw00),e.kcdw00,e.k2j000,e.m2j000)))
     end  ypzsl0,
  case
     when ((select VALUE0 from XT_XTCS00 s  where NAME00='YS_MJZYF0' and instr( s.VALUE0,a.yfbmbh)>0)is not null) and
    ((select VALUE0 from XT_XTCS00 s  where NAME00='YS_MJZYFYPBM' and instr( s.VALUE0,D.YPNM00)>0) is not null)  then
     d.cfdw00
     else
     --e.mzfydw
     decode(d.cfdw00,e.kcdw00,e.kcdw00,e.mzfydw)
   end cfdw00,
  nvl(g.CSL000,D.CSL000),
  d.jldw00,
  d.yysj00,
  d.yysjdw,
  d.zxcs00,
  g.yyzf00,
  g.lsdj00,
  d.sfzby0,
  g.yfsmid,
case
     when ((select VALUE0 from XT_XTCS00 s  where NAME00='YS_MJZYF0' and instr( s.VALUE0,a.yfbmbh)>0)is not null) and
    ((select VALUE0 from XT_XTCS00 s  where NAME00='YS_MJZYFYPBM' and instr( s.VALUE0,D.YPNM00)>0) is not null)  then
     d.zhl000
     else
    decode(d.cfdw00,e.kcdw00,e.k2j000,e.m2j000)
  end zhl000,
  g.zqcs00,
  DECODE(d.jldw00,e.zyfydw,e.z2j000,e.mzfydw,e.m2j000,e.zjjldw,e.zjjl2j,e.mzkfdw,e.f2j000,e.kcdw00,e.k2j000,e.jldw00,1,d.f2j000)f2j000,
  d.ypyfmc,
  g.yfid00,
  d.PSLBID,
  (SELECT PSMC00 FROM BM_PS0000 h where h.psid00(+)=trim(d.PSLBID)) PSMC00,
  a.ysxm00,
  decode(a.ypdlbh,'2',a.zyts00||'剂',''),
  a.cfzje0,
  a.ysxm00,
  b.bmmc00 kdksmc,
  c.bmmc00,
 a.cfzt00,
  a.bccfh0,
  a.ghh000,
  a.ghid00,
  a.brid00,
  Substr(a.srrq00,1,4)||'-'||Substr(a.srrq00,5,2)||'-'||Substr(a.srrq00,7,2)||' '||a.srsj00 ,to_date(a.srrq00,'yyyymmdd') ,
  a.djh000,
  a.bz0000,
  a.ysgzh0,
  '-1',  --yj_ywjj00.sflb00
  --  decode(a.cfzt00,'0',decode(sign(a.cfzje0),-1,'申请退','未收费'),'1','已收费','2','已发药','3','作废','4','未审核','5','已退药',
  --   '6','已退费','未知') ,
  (select decode(nvl(max(h.cfzt00),'-1'),'-1',  decode(a.cfzt00,'0','未收费','1','已收费','2','已发药','3','已作废'),
           '0','退费申请','5','已退药','6','已退费')
           from yf_mzcf00 h,yf_mzcfmx j where h.cflsh0=j.cflsh0 and   j.ypnm00=d.ypnm00 and h.bccfh0 = a.cflsh0   and h.ghh000 = a.ghh000) ZT0000,
  (select decode(nvl(max(x.cfzt00),'-1'),'-1',  decode(a.cfzt00,'0','未收费','1',decode(nvl(h.jzdh00,0),0,'已开单','已结算'),'2',decode(nvl(h.jzdh00,0),0,'已发药','已结算'),'3','已作废'),
           '0','已退药','5','已退费','6','已退费')
           from yf_mzcf00 x,yf_mzcfmx j where x.cflsh0=j.cflsh0 and   j.ypnm00=d.ypnm00 and x.bccfh0 = a.cflsh0   and x.ghh000 = a.ghh000) ZT0001,
  0,
  (select f.zwxm00 from bm_ygbm00 f where f.ygbh00=a.czy000) rlry00,
  (select ICKH00 FROM IC_YHXX00 WHERE BRID00=a.brid00 and zt0000='1' and rownum=1) ickh00,
  a.cfxz00,
  decode(a.ypdlbh,'0','普通药品','1','毒','2','麻','3','神经')cfxzmc,
  d.cxbz00,
  d.thbz00,
  DECODE(d.THBZ00,'0','正常','1','退回') THBZMC,
  a.thyy00,
  a.YFBMBH,--
  a.BRXM00,
  a.XB0000,
  f.brlb00,
  a.YSGZH0,
  a.SSKS00,
  a.GHKS00,
  a.ZYTS00,
  a.BZID00,
  (SELECT m.bzmc00 FROM BM_DMZD00 m where m.bzid00=a.bzid00)BZMC00,
  a.FJFDJH,
  a.CZY000,
  a.SRRQ00,
  a.SRSJ00,
  f.CFMBMC,
  a.BRDZ00,
  a.ZYDJS0,
  a.TSBZBH,
 -- (SELECT yl.mc0000 FROM BM_TSBZB0 yl,sf_brxxb0 i where i.ghid00 = a.ghid00 and  yl.bh0000=a.tsbzbh and yl.ybzxlb = i.ybzxlb )TSBZMC,
 (SELECT yl.mc0000 FROM BM_TSBZB0 yl where   yl.bh0000=a.tsbzbh  and rownum=1 )TSBZMC,
  a.ZYSFNJ,
  a.ZYNJJL,
  a.ZYSFJJ,
  a.CFDGS0,
  a.ZYMRCS,
  a.ZJZFID,
  (SELECT cy.yfsmmc FROM BM_YPYFSM cy where cy.yfsmid=a.zjzfid)ZJZFMC,
  a.ZYMRJS ,
  a.JYFS00,
  a.csrq00 brcsrq,
   '' zdid00,
   0 yjdjh0,
  '' bwmc00,
   0 SFJE00,
  (select to_char(wm_concat(zdmc00)) zdmc00 from ys_brzdxx x  where x.ghh000=a.ghh000) zdmc00,--a.zdmc00 zdmc00,
  '' yjksmc,
  ''jzbz00,
  '0' dcbz00,
  '' Tsbsmc,
  '' YZCFID,
  0 CXDJH1,
  '-1' bbid00,  --add by yqs 标本id 20120825
  a.srsj00 kdsj00,--add by rbh 开单时间 20120828
  '' zxrxm0,--add by rbh 执行人姓名 20120828
  a.fyrq00 zxrq00,--add by rbh 执行日期 20120828
  a.fysj00 zxsj00,--add by rbh 执行时间 20120828
  '' kfrxm0,--add by rbh 扣费人姓名 20120828
  a.jzrq00 kfrq00,--add by rbh 扣费日期 20120828
  a.jzsj00 kfsj00,--add by rbh 扣费时间 20120828
  decode(f.cflsh0,null,'0','1') sfyslr,
  e.ypbm00,--add by rbh 药品编码 20120905
  f.djfsmc,--add by rbh 中药代煎方式名称  回家自煎，医院代煎，免煎 20120907
  e.m2j000,
  e.zjmzcf,
  e.k2j000,
  e.qzfs00,
  e.zdxl00, --最大限量
  d.hjje00,
  '0' fjdjbz,
  f.cfsm00,
  e.maxts0,--最大天数
  e.maxzl0,--最大总量
  (select nr0000 from YS_KSSSQ0 where cfid00=g.cfid00 and sqlb00=2),--超大天数原因
  (select nr0000 from YS_KSSSQ0 where cfid00=g.cfid00 and sqlb00=3), --超大总量原因
  decode(substr(g.csl000,1,1),'.','0'||g.csl000,g.csl000)||g.jldw00,--次数量和单位
  e.mzdj00,--麻醉等级
  e.dpdj00,--毒品登记
  e.sjdj00,--神经等级
  e.mzkfdw, e.mzfydw, e.zjjldw, e.kcdw00,e.jldw00,e.f2j000, e.zjjl2j,
  d.yymbid, --xt_ypmbmx.mbid
  nvl(a.lrjmlb,a.ypdlbh),
  0 sqid00, --申请id
  g.tsys00,--美康提示颜色
  0, --yj_ywjj00.id0000
  0, --ywjjje
  0 sfxmid,
  d.ljid00, --路径id
  d.brzxjd,
  d.jhmxid,
  d.ljyzmx,
  d.byyy00,
  d.zcxsl0,
  1 xmzk00,
  null tcid00,
  f.jyfabm, --煎药方案编码
  f.bzl000,  --包装量
  e.mzfpid,
  E.TZRQ00, --停止日期
  f.dbrxm0, --代办人姓名
  f.dbrxb0, --代办人性别
  f.dbrsfz,  --代办人身份证
  G.ZFFSBZ, --自费发送标志
  F.BZIDS0,
  F.BZMCS0,
  0 DJID00,
  0 DJMXID,
  F.SFPS00, --是否配送1是,其他否
  null ZXYSZ0, --执行医师组
  null CJKSBH,  --采血费备注
  null, --临床情况目的
  a.SFKSLY,--是否科室领药
  f.XDFBZ0,--协定方标志
  null SFFR00,--是否发热： 1是，0否
  null DCNYJ0,--是否多重耐药菌：1是，0否
  E.GJBZBM, --国家标准编码
  G.ZFFSBZ,
  decode(E.WGYPBZ,'1','双通道','2','统筹') WGBZMC,
  (select SM0000 from BM_PC0000 where  PCMC00=d.PCMC00) SM0000
from  yf_mzcf00 a,bm_bmbm00 b,bm_bmbm00 c,yf_mzcfmx d,bm_yd0000 e,ys_cfxxb0 f,ys_cfmx00 g, sf_brfy00 h
where a.yfbmbh=c.bmbh00 and a.ghks00=b.bmbh00  and d.ypnm00=e.ypnm00
and f.cflsh0(+)=g.cflsh0 and d.cflsh0=g.cflsh0(+) AND A.cflsh0= D.CFLSH0 AND D.cfid00=G.cfid00(+) and d.ypzsl0>0
and a.djh000=h.djh000(+)
union all
select
  '2'cfbz00,
  to_char(a.yjdjh0),
  to_char(b.lbbh00),
  c.lbmc00,
  SF_YS_GETYJXMJC(a.yjdjh0),
  a.zlxmid,
  '' xmyfid,
  '' xmyfmc,
  '',
  A.YJDJH0 mxid00,
  '' zb0000,
  '' ypgg00,
  0 pcid00,
  '' pcmc00,
  (SELECT round(F.ZFBL00*a.zje000,2) FROM YJ_YWJJ00 F WHERE F.yjdjh0 =A.yjdjh0 and rownum=1) zfje00,
  0,
  '' ypdlbhmx,
  (SELECT F.ZFBL00 FROM YJ_YWJJ00 F WHERE F.yjdjh0 =A.yjdjh0 and rownum=1) ZFBL00,
  (SELECT YBBZ00 FROM YJ_YWJJ00 G WHERE G.yjdjh0 =A.yjdjh0 and rownum=1) YBLB00,
   (SELECT DECODE(YBBZ00,'Y','医保','N','非医保')FROM YJ_YWJJ00 H WHERE H.yjdjh0 =A.yjdjh0 and H.SFLB00='0' and rownum=1) YBLBMC,
  a.sl0000 ypzsl0,
  a.dw0000 cfdw00,
  a.sl0000 CSL000,
  a.dw0000 jldw00,
  1 yysj00,--数量单位
  a.dw0000 yysjdw,--数量单位
  a.zxsl00 zxcs00,--执行数量
  '' yyzf00,
 0 lsdj00,
  '' sfzby0,
  '' yfsmid,
  0 zhl000,
  0 zqcs00,
  0 f2j000,
  '' YFID00,
  '' ypyfmc,
  '' PSLBID,
  ''PSMC00,
  a.KDYSXM ysxm00,
  a.sl0000||a.dw0000,
  zje000,
  a.kdysxm,
  d.bmmc00,
  e.bmmc00,
a.xmzt00,
  to_char(a.cxdjh0),a.zyhghh,a.zyghid,a.brid00, Substr(a.KDRQ00,1,4)||'-'||Substr(a.KDRQ00,5,2)||'-'||Substr(a.KDRQ00,7,2)||' '||kdsj00,to_date(kdrq00,'yyyymmdd'),
  a.sfdjh0,
  a.bz0000,
  a.kdys00,
  '-1' sflb00, --yj_ywjj00.sflb00
  (select decode(nvl(max(d.xmzt00),'-1'),'-1',
   decode(a.xmzt00,'0','未计价','1','未收费','2','已收费','3','已执行','4','已报告','6','已作废','9','待审核'),'1','退费申请','2','退费确认','3','退费确认')
   from yj_yw0000 d           where d.cxdjh0 = a.yjdjh0             and d.ZYHGHH = a.ZYHGHH) ZT0000,

   (select decode(nvl(max(d.xmzt00),'-1'),'-1',
   decode(a.xmzt00,'0','未计价','1','未收费','2',decode(nvl(f.jzdh00,0),0,'已开单','已结算'),'3',decode(nvl(f.jzdh00,0),0,'已执行','已结算'),'4','已报告','6','已作废','9','待审核'),'1','退执行','2','已退费','3','已退费')
   from yj_yw0000 d           where d.cxdjh0 = a.yjdjh0             and d.ZYHGHH = a.ZYHGHH) ZT0001,

  a.yjfzh0,
  a.LRXM00,(select ICKH00 FROM IC_YHXX00 WHERE BRID00=a.brid00 and zt0000='1' and rownum=1) ickh00,'','',
  'Z'cxbz00,
  '' thbz00,
  '' thbzmc,
  '' thyy00,
  0 YFBMBH,
  a.brxm00,
  a.XB0000,
  0 BRLB00,
  a.KDYS00 YSGZH0,--开单医生编号
  a.yjksbh SSKS00,--医技科室
  a.kdksbh GHKS00,--开单科室
  0 ZYTS00,--中药帧数
  0 BZID00,--病症ID号(主要用于毒麻处方)  对应BM_DMZD00表
  '' BZMC00,
  0 FJFDJH,--附加费单据号
  0 CZY000 ,--操作员
  a.kdrq00 SRRQ00,
  a.kdsj00 SRSJ00,
  ''CFMBMC,
  ''BRDZ00,
  0 ZYDJS0,
   a.bh0000,
 --(SELECT yl.mc0000 FROM BM_TSBZB0 yl,sf_brxxb0 i where i.ghid00 = a.ZYGHID and  yl.bh0000=a.bh0000 and yl.ybzxlb = i.ybzxlb and rownum=1)TSBZMC,
 (SELECT yl.mc0000 FROM BM_TSBZB0 yl where   yl.bh0000=a.bh0000 and rownum=1 )TSBZMC,
  ''ZYSFNJ,
  0 ZYNJJL,
  ''ZYSFJJ,
  ''CFDGS0,
  0 ZYMRCS,
  ''ZJZFID,
  ''ZJZFMC,
  0 ZYMRJS ,
  ''JYFS00,
  a.csrq00 brcsrq,
  a.zdid00,
  a.yjdjh0,
  a.bwmc00,
  (select sum(NVL(e.TCJE00,d.SFJE00)*e.SFCS00) SFJE00 from VW_BM_YYSFXM d, XT_ZLSFGX e where d.SFXMID=e.sfxmid and e.zlxmid=a.zlxmid) SFJE00,--单价
  (SELECT to_char(wm_concat(ZDMC00)) ZDMC00 FROM YS_BRZDXX X  WHERE X.GHH000=a.ZYHGHH) ZDMC00,--a.zdmc00,
  e.bmmc00 yjksmc,
  a.jzbz00,
  a.dcbz00,
  a.Tsbsmc,
  a.YZCFID,
  (select cxdjh0 from yj_yw0000 d2 where d2.cxdjh0 = a.yjdjh0 and d2.zyghid = a.zyghid and mzzybz=a.mzzybz and rownum=1) CXDJH1,
  a.bbid00, --add by yqs 标本id 20120825
  a.kdsj00,--add by rbh 开单时间 20120828
  a.zxrxm0,--add by rbh 执行人姓名 20120828
  a.zxrq00,--add by rbh 执行日期 20120828
  a.zxsj00,--add by rbh 执行时间 20120828
  (select czyxm0 from sf_brfy00 where djh000=a.sfdjh0) kfrxm0,--add by rbh 扣费人姓名 20120828
  (select czrq00 from sf_brfy00 where djh000=a.sfdjh0) kfrq00,--add by rbh 扣费日期 20120828
  (select czsj00 from sf_brfy00 where djh000=a.sfdjh0) kfsj00,--add by rbh 扣费时间 20120828
  '1' sfyslr,
  '',--add by rbh 药品编码 20120905
  '',--add by rbh 中药代煎方式名称  回家自煎，医院代煎，免煎 20120907
  0,
  '',
  0,
  '',
  0 zdxl00,
  a.zje000 hjje00,
  a.fjdjbz,
  '',
  0,--最大天数
  0, --最大总量
  '',--超大天数原因
  '', --超大总量原因
  a.sl0000||a.dw0000,--次数量和单位
  '',--麻醉等级
  '',--毒品登记
  '',--神经等级
  '', '', '', '','',0, 0,
  0,
  '',
  a.sqid00, --申请id
  -1, --美康提示颜色
  (select id0000 from yj_ywjj00 where yjdjh0=a.yjdjh0 and rownum=1) ywjjid, --yj_ywjj00.id0000
  0, --ywjjje
  (select sfxmid from yj_ywjj00 where yjdjh0=a.yjdjh0 and rownum=1) sfxmid,
  a.ljid00, --路径id
  a.brzxjd,
  a.jhmxid,
  a.ljyzmx,
  a.byyy00,
  a.cxsl00,
  decode(sign(a.xmzk00),1,a.xmzk00,1) xmzk00,
  a.tcid00 tcid00,
  null jyfabm, --煎药方案编码
  null bzl000,  --包装量
  (select mzfpid from BM_YYSFXM where sfxmid in (select sfxmid from  XT_ZLSFGX where zlxmid = b.zlxmid ) and rownum = 1),
  B.TZRQ00, --停止日期
  null dbrxm0, --代办人姓名
  null dbrxb0, --代办人性别
  null dbrsfz,  --代办人身份证
  A.ZFFSBZ, --自费发送标志
  '' BZIDS0,
  '' BZMCS0,
  A.DJID00,
  A.DJMXID,
  null SFPS00,
  A.ZXYSZ0, --执行医师组
  A.CJKSBH, --采血费备注
  A.BQZY00, --临床情况目的
  null SFKSLY,--是否科室领药
  null XDFBZ0,--协定方标志
  A.SFFR00,--是否发热： 1是，0否
  A.DCNYJ0,--是否多重耐药菌：1是，0否
  null GJBZBM, --国家标准编码
  A.SFZF00,
  null WGBZMC,
  null SM0000
from  yj_yw0000 a,bm_zlzd00 b,bm_zlxmlb c,bm_bmbm00 d,bm_bmbm00 e, sf_brfy00 f
where a.zlxmid=b.zlxmid and b.lbbh00=c.lbbh00 and  a.sl0000>0 and
      a.kdksbh=d.bmbh00 and a.yjksbh=e.bmbh00 and a.mzzybz='0' and b.lbbh00 not in(20,21)
      and a.sfdjh0=f.djh000(+)
union all
select
  '2'cfbz00,
  to_char(a.yjdjh0),
  to_char(b.lbbh00),
  c.lbmc00,
  '    '||g.xmmc00,
  a.zlxmid,
  '' xmyfid,
  '' xmyfmc,
  '',
  A.YJDJH0 mxid00,
  '' zb0000,
  '' ypgg00,
  0 pcid00,
  '' pcmc00,
  (SELECT round(F.ZFBL00*a.zje000,2) FROM YJ_YWJJ00 F WHERE F.yjdjh0 =A.yjdjh0 and rownum=1) zfje00,
  0,
  '' ypdlbhmx,
  (SELECT F.ZFBL00 FROM YJ_YWJJ00 F WHERE F.yjdjh0 =A.yjdjh0 and rownum=1) ZFBL00,
  (SELECT YBBZ00 FROM YJ_YWJJ00 G WHERE G.yjdjh0 =A.yjdjh0 and rownum=1) YBLB00,
   (SELECT DECODE(YBBZ00,'Y','医保','N','非医保')FROM YJ_YWJJ00 H WHERE H.yjdjh0 =A.yjdjh0 and rownum=1) YBLBMC,
  a.sl0000 ypzsl0,
  a.dw0000 cfdw00,
  a.sl0000 CSL000,
  a.dw0000 jldw00,
  1 yysj00,--数量单位
  a.dw0000 yysjdw,--数量单位
  a.zxsl00 zxcs00,--执行数量
  '' yyzf00,
 0 lsdj00,
  '' sfzby0,
  '' yfsmid,
  0 zhl000,
  0 zqcs00,
  0 f2j000,
  '' YFID00,
  '' ypyfmc,
  '' PSLBID,
  ''PSMC00,
  a.KDYSXM ysxm00,
  a.sl0000||a.dw0000,
  zje000,
  a.kdysxm,
  d.bmmc00,
  e.bmmc00,
a.xmzt00,
  to_char(a.cxdjh0),a.zyhghh,a.zyghid,a.brid00, Substr(a.KDRQ00,1,4)||'-'||Substr(a.KDRQ00,5,2)||'-'||Substr(a.KDRQ00,7,2)||' '||kdsj00,to_date(kdrq00,'yyyymmdd'),
  a.sfdjh0,
  a.bz0000,
  a.kdys00,
  f.sflb00, --yj_ywjj00.sflb00
  (select decode(nvl(max(d.xmzt00),'-1'),'-1',
   decode(a.xmzt00,'0','未计价','1','未收费','2','已收费','3','已执行','4','已报告','6','已作废','9','待审核'),'1','退费申请','2','退费确认','3','退费确认')
   from yj_yw0000 d           where d.cxdjh0 = a.yjdjh0             and d.ZYHGHH = a.ZYHGHH) ZT0000,

   (select decode(nvl(max(d.xmzt00),'-1'),'-1',
   decode(a.xmzt00,'0','未计价','1','未收费','2',decode(nvl(h.jzdh00,0),0,'已开单','已结算'),'3',decode(nvl(h.jzdh00,0),0,'已执行','已结算'),'4','已报告','6','已作废','9','待审核'),'1','退执行','2','已退费','3','已退费')
   from yj_yw0000 d           where d.cxdjh0 = a.yjdjh0             and d.ZYHGHH = a.ZYHGHH) ZT0001,

  a.yjfzh0,
  a.LRXM00,(select ICKH00 FROM IC_YHXX00 WHERE BRID00=a.brid00 and zt0000='1' and rownum=1) ickh00,'','',
  'Z'cxbz00,
  '' thbz00,
  '' thbzmc,
  '' thyy00,
  0 YFBMBH,
  a.brxm00,
  a.XB0000,
  0 BRLB00,
  a.KDYS00 YSGZH0,--开单医生编号
  a.yjksbh SSKS00,--医技科室
  a.kdksbh GHKS00,--开单科室
  0 ZYTS00,--中药帧数
  0 BZID00,--病症ID号(主要用于毒麻处方)  对应BM_DMZD00表
  '' BZMC00,
  0 FJFDJH,--附加费单据号
  0 CZY000 ,--操作员
  '' SRRQ00,
  '' SRSJ00,
  ''CFMBMC,
  ''BRDZ00,
  0 ZYDJS0,
     a.bh0000,
  (SELECT yl.mc0000 FROM BM_TSBZB0 yl,sf_brxxb0 i where i.ghid00 = a.ZYGHID and  yl.bh0000=a.bh0000 and yl.ybzxlb = i.ybzxlb )TSBZMC,
  ''ZYSFNJ,
  0 ZYNJJL,
  ''ZYSFJJ,
  ''CFDGS0,
  0 ZYMRCS,
  ''ZJZFID,
  ''ZJZFMC,
  0 ZYMRJS ,
  ''JYFS00,
  a.csrq00 brcsrq,
  a.zdid00,
  a.yjdjh0,
  a.bwmc00,
  (select sum(NVL(e.TCJE00,d.SFJE00)*e.SFCS00) SFJE00 from VW_BM_YYSFXM d, XT_ZLSFGX e where d.SFXMID=e.sfxmid and e.zlxmid=a.zlxmid) SFJE00,--单价
  (select to_char(wm_concat(zdmc00)) zdmc00 from ys_brzdxx x  where x.ghh000=A.ZYHGHH) ZDMC00,--a.zdmc00,
  e.bmmc00 yjksmc,
  a.jzbz00,
  a.dcbz00,
  a.Tsbsmc,
  a.YZCFID,
  (select cxdjh0 from yj_yw0000 d2 where d2.cxdjh0 = a.yjdjh0 and d2.zyghid = a.zyghid and mzzybz=a.mzzybz and rownum=1) CXDJH1,
  a.bbid00, --add by yqs 标本id 20120825
  a.kdsj00,--add by rbh 开单时间 20120828
  a.zxrxm0,--add by rbh 执行人姓名 20120828
  a.zxrq00,--add by rbh 执行日期 20120828
  a.zxsj00,--add by rbh 执行时间 20120828
  (select czyxm0 from sf_brfy00 where djh000=a.sfdjh0) kfrxm0,--add by rbh 扣费人姓名 20120828
  (select czrq00 from sf_brfy00 where djh000=a.sfdjh0) kfrq00,--add by rbh 扣费日期 20120828
  (select czsj00 from sf_brfy00 where djh000=a.sfdjh0) kfsj00,--add by rbh 扣费时间 20120828
  '1' sfyslr,
  '',--add by rbh 药品编码 20120905
  '',--add by rbh 中药代煎方式名称  回家自煎，医院代煎，免煎 20120907
  0,
  '',
  0,
  '',
  0 zdxl00,
  0 hjje00,
  a.fjdjbz,
  '',
  0,--最大天数
  0, --最大总量
  '',--超大天数原因
  '', --超大总量原因
  f.sfcs00||a.dw0000,--次数量和单位
  '',--麻醉等级
  '',--毒品登记
  '',--神经等级
  '', '', '', '','',0, 0,
  0,
  '',
  a.sqid00, --申请id
  -1, --美康提示颜色
  f.id0000, --yj_ywjj00.id0000
  g.sfje00*f.sl0000 ywjjje,
  (select sfxmid from yj_ywjj00 where yjdjh0=a.yjdjh0 and rownum=1) sfxmid,
  a.ljid00, --路径id
  a.brzxjd,
  a.jhmxid,
  a.ljyzmx,
  a.byyy00,
  a.cxsl00,
  decode(sign(a.xmzk00),1,a.xmzk00,1) xmzk00,
  a.tcid00 tcid00,
  null jyfabm, --煎药方案编码
  null bzl000,  --包装量
  (select mzfpid from BM_YYSFXM where sfxmid in (select sfxmid from  XT_ZLSFGX where zlxmid = b.zlxmid ) and rownum = 1),
  B.TZRQ00, --停止日期
  null dbrxm0, --代办人姓名
  null dbrxb0, --代办人性别
  null dbrsfz,  --代办人身份证
  nvl(A.ZFFSBZ,A.SFZF00)   ZFFSBZ, --自费发送标志
  '' BZIDS0,
  '' BZMCS0,
  A.DJID00,
  A.DJMXID,
  null SFPS00,
  A.ZXYSZ0, --执行医师组
  A.CJKSBH,  --采血费备注
  A.BQZY00, --临床情况目的
  null SFKSLY,--是否科室领药
  null XDFBZ0,--协定方标志
  A.SFFR00,--是否发热： 1是，0否
  A.DCNYJ0,--是否多重耐药菌：1是，0否
  null GJBZBM, --国家标准编码
  A.SFZF00,
  null WGBZMC,
  null SM0000
from  yj_yw0000 a,bm_zlzd00 b,bm_zlxmlb c,bm_bmbm00 d,bm_bmbm00 e,YJ_YWJJ00 f,bm_yysfxm g, sf_brfy00 h
where a.zlxmid=b.zlxmid and b.lbbh00=c.lbbh00 and  a.sl0000>0 and
      a.kdksbh=d.bmbh00 and a.yjksbh=e.bmbh00 and a.mzzybz='0' and b.lbbh00 not in(20,21)
      and a.yjdjh0=f.yjdjh0(+) and f.sfxmid=g.sfxmid(+) and (select count(1) from YJ_YWJJ00 h where h.yjdjh0=a.yjdjh0)>1
      and a.sfdjh0=h.djh000(+)
/