prompt 住院摆药FR3打印单据视图 VW_YF_ZYBYDYDJ create or replace view VW_YF_ZYBYDYDJ ( ZYDJS0, CKDH00, --出库单号 BRID00, --病人ID BRBLH0, --病历号 BRJTDZ, --地址 KSMC00, --科室 BQMC00, --病区 BRXM00, --病人姓名 CH0000, --床号 XB0000, --性别 NL0000, --年龄 ZDMC00, --诊断 ZYH000, --住院号 BRDH00, --电话 FYRQSJ, --发药时间 YSXM00, --医生 SHRXM0, --审核人 TJRXM0, --调剂人 HDRXM0, --核对人 FYRXM0, --发药人 FBMC00, --费别 YFMC00, --药房 BYTJ00, --摆药条件 CFYZID, --处方医嘱ID(门诊药房打印处方编号) YZTXM0, --医嘱ID条码 SFZDBY, --摆药途径 '0'手工'1'自动摆药'2'静配'3'干预为手工 RYZD00, --入院诊断 ZYZD00, --中药诊断 ZYID00, --住院ID FYDLXH, --发药队列序号 DYCZ00, --重制 KFKSMC, --急诊 ZYTS00, --中药剂数 TYJE00, --退药金额 ZJYFMC, --整剂用法名称 ZYDJF0, --中药代煎费 JYFS00, --煎药方式 ZYMRCS, --中药每日次数 ZYJS00, --中药剂数 DYMB00, --打印模板:0西药,1草药 ZYYFSM, --中药嘱咐 ZYDJCZ, --中药代煎材质分类 CFMXBZ, --处方明细说明 YZTXM1, DQKSMC, YZID00, CXBZ00, --冲销备注 RYZYZD, DMZD00, --毒麻诊断 SFBD00 --是否补打 ) as -- MODIFICATION HISTORY -- Person Date Comments -- gzj 2018.08.03 create for YF9-20180803-003 -- gzj 2018.08.06 允许打印冲销单据 for YF9-20180712-001 -- gzj 2018.09.19 增加中医诊断字段ZYZD00 for YF9-20180919-001 -- gzj 2018.01.03 整合省人民的视图内容 for YF9-20180919-001 -- gzj 2019.06.14 增加毒麻诊断字段DMZD00 for YF9-20190524-001 -- linshu 2019.11.21 增加了是否补打字段 for YF9-20191120-002 -- --------- ---------- ------------------------------------------- select a.ZYDJS0, a.CKDH00, c.BRID00, f.BRBLH0, f.BRJTDZ, (select BMMC00 from bm_bmbm00 where bmbh00=a.SSKS00) KSMC00, (select BMMC00 from bm_bmbm00 where bmbh00=a.BQBMBH) BQMC00, a.BRXM00, a.CH0000, c.XB0000, substr(trim(SF_XT_CSRQTONL(c.CSRQ00,to_char(sysdate,'YYYYMMDD'))),1,20) NL0000, substr(trim(SF_XT_GETZYZD(c.ZYID00)),1,40) ZDMC00, a.ZYH000, f.BRDH00, substr(h.QRRQ00,1,4)||'-'||substr(h.QRRQ00,5,2)||'-'||substr(h.QRRQ00,7,2)||' '||h.QRSJ00 FYRQSJ, (select ZWXM00 from BM_YGBM00 where ygbh00=a.ysgzh0) YSXM00, (select ZWXM00 from BM_YGBM00 where YGBH00=h.HDR000) SHRXM0, (select ZWXM00 from BM_YGBM00 where YGBH00=h.TJR000) TJRXM0, (select ZWXM00 from BM_YGBM00 where YGBH00=h.HDR000) HDRXM0, (select ZWXM00 from BM_YGBM00 where YGBH00=h.QRR000) FYRXM0, (select decode(M.YBJGJC,N.BRLBJC,M.YBJGJC,M.YBJGJC||'('||N.BRLBJC||')') from IC_YBBRLB M,BM_YBBRLB N where f.FBBH00=M.FBBH00 and f.YBLB00=M.YBLB00 and f.YBBRLB=N.YBBRLB and f.FBBH00=N.FBBH00 and M.YBZXLB=N.YBZXLB and ROWNUM=1) FBMC00, --费别名称 (select BMMC00 from bm_bmbm00 where bmbh00=a.YFBMBH) YFMC00, (select MC0000 from YF_BYTJ00 where id0000=h.BYTJID) BYTJ00, a.CFYZID, '1'||decode(mod(length(a.CFYZID),2),0,'0','')||to_char(a.CFYZID) as YZTXM0, a.BYTJ00 SFZDBY, (select Trim(ZDMC00) from BQ_BRZDXX WHERE ZYID00=c.ZYID00 and ZDLB00='2' and rownum=1) RYZD00, (SELECT Trim(to_char(wm_concat(ZDMC00))) from BQ_BRZDXX WHERE ZYID00=c.ZYID00 and ZDLB00 in ('h','i') ) ZYZD00,--中药诊断 a.ZYID00, h.FYDLXH, decode(sign(nvl(h.DYCS00,0)-1),1,'重制','') as DYCZ00, decode(upper(substrb(a.PCMC00,1,3)),'S.T','急','') as KFKSMC, A.ZYTS00*SF_PY_XDCFZYTS(a.YPQLPC,1) ZYTS00, --中药贴数 ' ' TYJE00, decode(d.LBBH00,2,a.YPYFMC,a.ZJYFMC) ZJYFMC, substrb(SF_YF_GetSFSDJF(2, A.YZID00),1,10) ZYDJF0, --中药代煎费 decode(a.YPDLBH,'2',(select JYFSMC from YS_JYFS00 AA,BQ_YPYZ00 BB where AA.BH0000=BB.JYFS00 and BB.YZID00=a.YZID00),'') JYFS00, --煎药方式 case when a.YPDLBH='2' and (d.YPCZFL='2' or a.ZYDJS0>0) then '早晚各1包' else '分两次' end as ZYMRCS,--中药每日次数 case when a.YPDLBH='2' and d.YPCZFL='2' then '每日1剂(2包)' else '每日1剂' end as ZYJS00, --中药剂数 decode(d.LBBH00,2,1,0) DYMB00, --打印模板 decode(a.YPDLBH,2,a.YFSMMC,' ') ZYYFSM, --中药嘱咐 decode(a.YPDLBH,2,decode(sign(a.ZYDJS0),1,'代煎',decode(d.YPCZFL,'2','全成分',' ')),' ') as ZYDJCZ, --中药代煎材质分类 decode(d.LBBH00,2,SF_YF_CFYYZFMXBZ(a.YZID00),'') as CFMXBZ, --处方明细说明 'Z'||decode(mod(length(a.CFYZID),2),0,'0','')||to_char(a.CFYZID) as YZTXM1, (select BMMC00 from bm_bmbm00 where bmbh00=a.DQKS00) DQKSMC, a.YZID00, a.CXBZ00, (SELECT Trim(ZDMC00) from BQ_BRZDXX WHERE ZYID00=c.ZYID00 and ZDLB00='g' and rownum=1) as RYZYZD, --入院中医诊断 decode((select max(ZDMC00) from BQ_YPYZ00 where YZID00=a.YZID00),'',nvl(a.zdmc00,(select Trim(ZDMC00) from BQ_BRZDXX WHERE ZYID00=c.ZYID00 and ZDLB00='2' and rownum=1)),(select max(ZDMC00) from BQ_YPYZ00 where YZID00=a.YZID00)) as DMZD00, decode(nvl(h.DYCS00,0),0,'','补打') SFBD00 from YF_YZYPSQ a,ZY_BRXXB0 c,BM_YD0000 d,BM_BRXXB0 f,YF_YPCKD0 h where a.ZYID00=c.ZYID00 and a.YPNM00=d.YPNM00 and c.BRID00=f.BRID00 and a.CKDH00=h.CKDH00 /