CREATE OR REPLACE VIEW 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, zysfnj, zynjjl, zysfjj, cfdgs0, zymrcs, zjzfid, 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)
AS
select
  '1'cfbz00,
  a.cflsh0,
  a.ypdlbh,
  decode(d.ypdlbh,'0','西药','1','成药','2','中药')cflbmc,
  e.ypmc00 cfmc00,
  d.ypnm00,
  g.xmyfid,
  d.xmyfmc,
  a.ypczfl,
  d.cfid00 mxid00,
  d.zb0000,
  d.YPGG00,
  g.pcid00,
  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,
  g.CSL000,
  d.jldw00,
  d.yysj00,
  d.yysjdw,
  d.zxcs00,
  g.yyzf00,
  d.lsdj00,
  d.sfzby0,
  g.yfsmid,
  DECODE(d.cfdw00,e.kcdw00,e.k2j000,e.m2j000)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(+)=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 j.cflsh0=d.cflsh0 and h.bccfh0 = a.cflsh0   and h.ghid00 = a.ghid00) ZT0000,
  (select decode(nvl(max(h.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 h,yf_mzcfmx j where h.cflsh0=j.cflsh0 and   j.ypnm00=d.ypnm00  and j.cflsh0=d.cflsh0 and h.bccfh0 = a.cflsh0   and h.ghid00 = a.ghid00) 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,
  a.ZYSFNJ,
  a.ZYNJJL,
  a.ZYSFJJ,
  a.CFDGS0,
  a.ZYMRCS,
  a.ZJZFID,
  a.ZYMRJS ,
  a.JYFS00,
  a.csrq00 brcsrq,
   '' zdid00,
   0 yjdjh0,
  '' bwmc00,
   0 SFJE00,
  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), --超大总量原因
  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  --包装量
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*decode(sign(a.xmzk00),1,a.xmzk00,1),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,
  '-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,
  '' TSBZBH,
  ''ZYSFNJ,
  0 ZYNJJL,
  ''ZYSFJJ,
  ''CFDGS0,
  0 ZYMRCS,
  ''ZJZFID,
  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,--单价
  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*decode(sign(a.xmzk00),1,a.xmzk00,1) 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  --包装量
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,
  '' TSBZBH,
  ''ZYSFNJ,
  0 ZYNJJL,
  ''ZYSFJJ,
  ''CFDGS0,
  0 ZYMRCS,
  ''ZJZFID,
  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,--单价
  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  --包装量
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(+);