create or replace view vw_py_brcf00 as select a.CFLSH0, --处方流水号 a.YFBMBH, --药房部门编号 a.GHID00, --病人挂号流水号 a.GHH000, --挂号号 a.BRID00, --病人ID号 a.BRXM00, --病人姓名 a.XB0000, --性别 a.CSRQ00, --出生日期 substr(SF_XT_CSRQTONL(A.CSRQ00, ''),1,10) as NL0000, --年龄 a.DJH000, --收费单据号 a.YSGZH0, --医师工作号 (select XKH000 from BM_YGBM00 where YGBH00 = A.YSGZH0) as YSXKH0, --医生胸卡号 a.YSXM00, --医师姓名 a.SSKS00, --所属科室(医师) a.GHKS00, --开方科室(病人) a.ZYTS00*SF_PY_XDCFZYTS(a.CFLSH0) as ZYTS00, --中药贴数 a.YPDLBH, --药品大类编号 ''0''西药,''2''草药''1''成药 a.CFXZ00, --处方性质 :毒、麻、神经药品标志 ''1''毒''2''麻''3''神经 a.CFZT00, --处方状态:0未记账,1已记账,2已发药,3作废''4''需要审核''5''退药(负方) ''6''退费(负方) a.FJFDJH, --附加费收费单据号 a.CZY000, --操作员 a.FHR000, --审核人 a.PFR000, --配方人 a.FHR001, --复核人(待配->待发) a.FYR000, --发药人 a.SRRQ00, --输入日期 a.SRSJ00, --输入时间 a.JZRQ00, --记账日期 a.JZSJ00, --记账时间 a.FYRQ00, --发药日期 a.FYSJ00, --发药时间 decode(A.FYRQ00, '', null,'20991231',decode(a.CFZT00,'0',to_char(sysdate,'YYYY-MM-DD'),'1',to_char(sysdate,'YYYY-MM-DD'),null), substr(A.FYRQ00,1,4)||'-'||substr(A.FYRQ00,5,2)||'-'||substr(A.FYRQ00,7,2))||' '||substr(A.FYSJ00, 1, 5) as FYRQSJ, --发药日期时间 a.CFZJE0, --处方总金额 a.BZ0000, --备注 a.BCCFH0, --被冲销处方流水号 a.BZID00, --病症ID号(主要用于毒麻处方) a.BZMC00, --病症名称 a.BRDZ00, --病人地址,用于毒麻处方 a.SFKSLY, --是否科室领药 ''Y''科室领药，''N''病人领药 a.PSZT00, --皮试状态''0''非皮试,''1''需皮试''2''皮试未通过不能发药,''3'',皮试通过可发药 a.LYDH00, --领药单号 a.LYR000, --领药人(用于护士带领) a.CFSRBZ, --处方输入标志:0医生,1药房,2收费处,3医技,4病区 nvl(A.ZYDJS0,0) as ZYDJS0, --中药代煎数 a.THBZ00, --退回标志 ''1''退回''0''正常 a.LYKS00, --领药科室 a.CYJE00, --中药金额 a.YXJE00, --药械金额 a.SFXJJZ, --是否现金记帐 a.TSBZBH, --特殊病种编号 a.CFXLH0, --处方序列号 a.CFBDCS, --处方补打次数 a.CFDGS0, --处方单格式：0:麻醉和第一类精神药品 1:第二类精神药品 2:儿科 3:儿科+急诊 4:急诊 5: 普通 (select BMMC00 from BM_BMBM00 where BMBH00 = A.GHKS00) as KDKSMC, --开单科室名称 c.PYCKBH, --配药窗口编号 c.FYCKBH, --发药窗口编号 (select CKMC00 from BM_CKBM00 where CKBH00 = C.FYCKBH) as FYCKMC, --发药窗口名称 c.DLXH00, --队列序号 (select decode(M.YBJGJC,N.BRLBJC,M.YBJGJC,M.YBJGJC||'('||N.BRLBJC||')') from IC_YBBRLB M,BM_YBBRLB N where B.FBBH00=M.FBBH00 and B.YBLB00=M.YBLB00 and B.YBBRLB=N.YBBRLB and B.FBBH00=N.FBBH00 and M.YBZXLB=N.YBZXLB AND ROWNUM=1) as FBMC00, --费别名称 b.BRJTDZ, --家庭住址 nvl(B.BRDH00,b.BRLXDH) as BRDH00, --病人电话 /* decode(a.CFXZ00,'0',rpad(nvl(trim(decode(TRIM(SF_YF_GETXYZYZD(a.GHH000,decode(a.YPDLBH,'2','0','1'))),'','',decode(a.YPDLBH,'2','(中)','(西)')||trim(SF_YF_GETXYZYZD(a.GHH000,decode(a.YPDLBH,'2','0','1'))||chr(10))) ||decode(trim(SF_YF_GETXYZYZD(a.GHH000,decode(a.YPDLBH,'2','1','0'))),'','',decode(a.YPDLBH,'2','(西)','(中)')||trim(SF_YF_GETXYZYZD(a.GHH000,decode(a.YPDLBH,'2','1','0')))) ),'待查'),60,' '),a.BZMC00) as LCZD00, */ --临床诊断 rpad(nvl(trim(decode(TRIM(SF_YF_GETXYZYZD(a.GHH000,decode(a.YPDLBH,'2','0','1'))),'','',decode(a.YPDLBH,'2','(中)','(西)')||trim(SF_YF_GETXYZYZD(a.GHH000,decode(a.YPDLBH,'2','0','1'))||' '))--chr(10))) ||decode(trim(SF_YF_GETXYZYZD(a.GHH000,decode(a.YPDLBH,'2','1','0'))),'','',decode(a.YPDLBH,'2','(西)','(中)')||trim(SF_YF_GETXYZYZD(a.GHH000,decode(a.YPDLBH,'2','1','0')))) ),'待查'),60,' ') as LCZD00, --临床诊断 (select ZWXM00 from BM_YGBM00 where YGBH00=A.PFR000) as PFRXM0, --配方人 decode(A.YPDLBH, '2', substr(decode(SF_PY_PYLBMC(A.CFLSH0),'普通',' ',SF_PY_PYLBMC(A.CFLSH0)),1,10), decode(a.cfdgs0,'0','麻精','1','精二','2','儿科','3','儿急','4','急诊',' ')) as CFGSMC, --处方格式名称 (select JYFSMC from YS_JYFS00 where BH0000=A.JYFS00) as JYFS00, --煎药方式 case when a.YPDLBH='2' and (a.YPCZFL='2' or a.ZYDJS0>0) then '早晚各1包' else decode(A.ZYMRCS,1,'分一次',2,'分两次',3,'分三次',4,'分四次',5,'分五次',6,'分六次',7,'分七次',8,'分八次',9,'分九次') end as ZYMRCS,--中药每日次数 case when a.YPDLBH='2' and a.YPCZFL='2' then '每日1剂(2包)' else '每日1剂' end as ZYJS00 , --中药剂数 to_char(to_date(A.FYRQ00,'YYYYMMDD'),'Mondd')||'日' as FYKSRQ, --服药开始日期 to_char(to_date(A.FYRQ00,'YYYYMMDD') + (A.ZYTS00*SF_PY_XDCFZYTS(a.CFLSH0)) - 1,'Mondd')||'日' as FYJZRQ, --服药截止日期 (select nvl(ZWXM00, ' ') from BM_YGBM00 where YGBH00 = A.FHR000) as FHR0XM, --审核人姓名 (select nvl(ZWXM00, ' ') from BM_YGBM00 where YGBH00 = A.PFR000) as PFR0XM, --配方人姓名 (select nvl(ZWXM00, ' ') from BM_YGBM00 where YGBH00 = A.FYR000) as FYR0XM, --发药人姓名 (select nvl(ZWXM00, ' ') from BM_YGBM00 where YGBH00 = A.FHR001) as FHR1XM, --复核人姓名 (select nvl(XKH000, ' ') from BM_YGBM00 where YGBH00 = A.FHR000) as SHRXKH, --审核人胸卡号 (select nvl(XKH000, ' ') from BM_YGBM00 where YGBH00 = A.PFR000) as PFRXKH, --配方人胸卡号 (select nvl(XKH000, ' ') from BM_YGBM00 where YGBH00 = A.FYR000) as FYRXKH, --发药人胸卡号 (select nvl(XKH000, ' ') from BM_YGBM00 where YGBH00 = A.FHR001) as FHRXKH, --复核人胸卡号 substrb(SF_PY_GetSFSDJF(2, A.CFLSH0),1,10) as ZYDJF0, --中药代煎费 c.FYDLXH, --发药队列序号 decode(nvl(A.CFSM00,''),'','',A.CFSM00||'('||A.YSXM00||')') as CFSM00, (select decode(CKMC00,'','','[窗口'||CKMC00||']') from BM_CKBM00 where CKBH00 = C.FYCKBH) as FYCK00, decode(nvl(A.YSGZH0,''),'',' ',(select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00 = A.YSGZH0)) as YSXMXK,--医生姓名胸卡号 decode(nvl(A.PFR000,''),'',' ',(select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00 = A.PFR000)) as PYXMXK,--配方人 decode(nvl(A.FHR001,''),'',' ',(select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00 = A.FHR001)) as FHXMXK,--复核 decode(nvl(A.FHR000,''),'',' ',(select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00 = A.FHR000)) as SHXMXK,--审核 decode(nvl(A.FYR000,''),'',' ',(select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00 = A.FYR000)) as FYXMXK,--发药人 decode(nvl(C.FYDLXH,''),'',' ',C.FYDLXH)as FYDLX1, decode(A.ZYSFJJ,'Y','(急煎)','(门诊)') as KFKSMC, (select decode(nvl(sum(x.CFZJE0),0),0,null,'(退'||rtrim(replace(to_char(-1*sum(x.CFZJE0),'FM9999990.999')||' ','. ',''))||') ') from YF_MZCF00 X where X.GHID00=a.GHID00 and X.BCCFH0=a.CFLSH0 and X.CFZJE0<0) as TYJE00, substr(SF_PY_CFMXBZ(A.CFLSH0),1,50) as CFMXBZ, --处方明细备注 '0'||decode(mod(length(a.CFLSH0),2),0,'0','')||a.CFLSH0 as CFHTXM, --处方号条形码 SF_SF_GETKYJE(b.BRID00) as BRYE00, --病人余额 substr(SF_YF_GETCQLY00(a.CFLSH0),1,50) as CQLY00, (select nvl(E.LBMC00,' ') from SF_BRXXB0 D,BM_GHLBB0 E,XT_XTCS00 F where D.GHLB00=E.LBBH00 and D.GHID00=A.GHID00 and to_char(E.LBBH00)=F.VALUE0 and F.NAME00='YS_BMHGHLB' ) GHLBMC, --挂号类别名称 '0'||a.CFLSH0 as EWM001, --处方二维码 (select SF_PY_GETCFZZSXH(a.CFLSH0) from dual) as QYCFXX ,b.fbbh00 ,b.brblh0 ,substr(SF_PY_CFMXBZ1(A.CFLSH0),1,50) as CFMXBZ1 from YF_MZCF00 a, BM_BRXXB0 b, YF_CFDL00 c where a.BRID00+0 = b.BRID00 and a.CFLSH0 = c.CFLSH0(+);