create or replace view vw_ys_mzzrzy_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, mzfpid, tzrq00, dbrxm0, dbrxb0, dbrsfz, yylb00, tsbzmc, zffsbz, sfyxsq, mzzybz, hbzyxm, yygxjg, czrq00, czrqsj, yyid00) as --chenzh2020.12.04 注释此条件 and b.LBBH00 not in(20,21) 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, a.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(a.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, ''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, --包装量 (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, --代办人身份证 null YYLB00, (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 i.BRID00 = a.BRID00 )TSBZMC, '', --自费发送标志 b.SFYXSQ,--是否有效申请 b.MZZYBZ,--门诊住院标志 a.HBZYXM, --合并住院项目 A.YYGXJG, --是否引用共享结果1是 其他否 (select CZRQ00 from SF_FYMX00 where YJDJH0=to_char(nvl(a.YJDJH0,0)) and rownum<=1) as CZRQ00, (select CZRQ00||' '||CZSJ00 from SF_FYMX00 where YJDJH0=to_char(nvl(a.YJDJH0,0)) and rownum<=1) as CZRQSJ, (select YYID00 from BM_BMBM00 where BMBH00=a.KDKSBH) as YYID00 --医院ID 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(+) and a.FJDJBZ<>'7' and a.FJDJBZ<>'8' and (a.CXDJH0 is null or a.CXDJH0=0) and exists(select 1 from SF_FYMX00 where CXBZ00='Z' and YJDJH0=to_char(nvl(a.YJDJH0,0)) and MZFPID not in (4,6)) 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, a.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(a.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, ''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, --包装量 (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, --代办人身份证 null YYLB00, (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 i.BRID00 = a.BRID00 )TSBZMC, '', --自费发送标志 b.SFYXSQ,--是否有效申请 b.MZZYBZ, --门诊住院标志 a.HBZYXM, --合并住院项目 A.YYGXJG, --是否引用共享结果1是 其他否 (select CZRQ00 from SF_FYMX00 where YJDJH0=to_char(nvl(a.YJDJH0,0)) and rownum<=1) as CZRQ00, (select CZRQ00||' '||CZSJ00 from SF_FYMX00 where YJDJH0=to_char(nvl(a.YJDJH0,0)) and rownum<=1) as CZRQSJ, (select YYID00 from BM_BMBM00 where BMBH00=a.KDKSBH) as YYID00 --医院ID 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(+) and a.FJDJBZ<>'7' and a.FJDJBZ<>'8' and (a.CXDJH0 is null or a.CXDJH0=0) and exists(select 1 from SF_FYMX00 where CXBZ00='Z' and YJDJH0=to_char(nvl(a.YJDJH0,0)) and MZFPID not in (4,6));