CREATE OR REPLACE VIEW VW_PY_BRCF00
(cflsh0, yfbmbh, ghid00, ghh000, brid00, brxm00, xb0000, csrq00, nl0000, djh000, ysgzh0, ysxkh0, ysxm00, ssks00, ghks00, zyts00, ypdlbh, cfxz00, cfzt00, fjfdjh, czy000, fhr000, pfr000, fhr001, fyr000, srrq00, srsj00, jzrq00, jzsj00, fyrq00, fysj00, fyrqsj, cfzje0, bz0000, bccfh0, bzid00, bzmc00, brdz00, sfksly, pszt00, lydh00, lyr000, cfsrbz, zydjs0, thbz00, lyks00, cyje00, yxje00, sfxjjz, tsbzbh, cfxlh0, cfbdcs, cfdgs0, kdksmc, pyckbh, fyckbh, fyckmc, dlxh00, fbmc00, brjtdz, brdh00, lczd00, pfrxm0, cfgsmc, jyfs00, zymrcs, zyjs00, fyksrq, fyjzrq, fhr0xm, pfr0xm, fyr0xm, fhr1xm, shrxkh, pfrxkh, fyrxkh, fhrxkh, zydjf0, fydlxh, cfsm00, fyck00, ysxmxk, pyxmxk, fhxmxk, shxmxk, fyxmxk, fydlx1, kfksmc, tyje00, cfmxbz, cfhtxm, jey000, jej000, jef000, dxcfje, yymc00, djfsmc, cfsm01, ypzl00, ypzsl0, fyxmzj, shxmzj, ysxmzj)
AS
select
  A.CFLSH0, --处方流水号
  A.YFBMBH, --药房部门编号
  A.GHID00, --病人挂号流水号
  A.GHH000, --挂号号
  A.BRID00, --病人ID号
  A.BRXM00, --病人姓名
  A.XB0000, --性别
  A.CSRQ00, --出生日期
  SF_XT_CSRQTONL(A.CSRQ00, '') NL0000, --年龄
  A.DJH000, --收费单据号
  A.YSGZH0, --医师工作号
  (select XKH000 from BM_YGBM00 where YGBH00 = A.YSGZH0) YSXKH0, --医生胸卡号
  A.YSXM00, --医师姓名
  A.SSKS00, --所属科室(医师)
  A.GHKS00, --开方科室(病人)
  A.ZYTS00*SF_PY_XDCFZYTS(a.CFLSH0) 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) 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) 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) KDKSMC, --开单科室名称
  C.PYCKBH, --配药窗口编号
  C.FYCKBH, --发药窗口编号
  (select CKMC00 from BM_CKBM00 where CKBH00 = C.FYCKBH) FYCKMC, --发药窗口名称
  C.DLXH00, --队列序号
  --(select FBMC00 from BM_BRFBB0 G where FBBH00=B.FBBH00) FBMC00, --费别名称
  (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) FBMC00, --费别名称
  B.BRJTDZ, --家庭住址
  nvl(B.BRDH00,b.BRLXDH) BRDH00, --病人电话
  --(select ZDMC00 from YS_BRZDXX where GHH000 = A.GHH000) LCZD00 --临床诊断
  /*
  rpad(nvl(trim(decode(TRIM(SF_YF_GETXYZYZD(a.GHH000,'1')),'','','(西)'||trim(SF_YF_GETXYZYZD(a.GHH000,'1')))
              ||chr(10)||decode(trim(SF_YF_GETXYZYZD(a.GHH000,'0')),'','','(中)'||trim(SF_YF_GETXYZYZD(a.GHH000,'0')))
                ),'待查'
           ),60,' '
      ) 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,' '
      ) LCZD00, --临床诊断
  (select ZWXM00 from BM_YGBM00 where YGBH00=A.PFR000) 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','急诊',' ')) CFGSMC, --处方格式名称
  (select JYFSMC from YS_JYFS00 where BH0000=A.JYFS00) JYFS00, --煎药方式
--  decode(A.ZYMRCS,1,'一',2,'两',3,'三',4,'四',5,'五',6,'六',7,'七',8,'八',9,'九') as ZYMRCS,--中药每日次数
  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')||'日' FYKSRQ, --服药开始日期
  to_char(to_date(A.FYRQ00,'YYYYMMDD') + (A.ZYTS00*SF_PY_XDCFZYTS(a.CFLSH0)) - 1,'Mondd')||'日' FYJZRQ, --服药截止日期
  (select nvl(ZWXM00, ' ') from BM_YGBM00 where YGBH00 = A.FHR000) FHR0XM, --审核人姓名
  (select nvl(ZWXM00, ' ') from BM_YGBM00 where YGBH00 = A.PFR000) PFR0XM, --配方人姓名
  (select nvl(ZWXM00, ' ') from BM_YGBM00 where YGBH00 = A.FYR000) FYR0XM, --发药人姓名
  (select nvl(ZWXM00, ' ') from BM_YGBM00 where YGBH00 = A.FHR001) FHR1XM, --复核人姓名
  (select nvl(XKH000, ' ') from BM_YGBM00 where YGBH00 = A.FHR000) SHRXKH, --审核人胸卡号
  (select nvl(XKH000, ' ') from BM_YGBM00 where YGBH00 = A.PFR000) PFRXKH, --配方人胸卡号
  (select nvl(XKH000, ' ') from BM_YGBM00 where YGBH00 = A.FYR000) FYRXKH, --发药人胸卡号
  (select nvl(XKH000, ' ') from BM_YGBM00 where YGBH00 = A.FHR001) FHRXKH, --复核人胸卡号
  substrb(SF_PY_GetSFSDJF(2, A.CFLSH0),1,10) ZYDJF0, --中药代煎费
  C.FYDLXH, --发药队列序号
  decode(nvl(A.CFSM00,''),'','',A.CFSM00||'('||A.YSXM00||')') CFSM00,
 (select decode(CKMC00,'','','['||CKMC00||'窗口]') from BM_CKBM00 where CKBH00 = C.FYCKBH) FYCK00,
 decode(nvl(A.YSGZH0,''),'',' ',(select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00 = A.YSGZH0)) YSXMXK,--医生姓名胸卡号
 decode(nvl(A.PFR000,''),'',' ',(select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00 = A.PFR000)) PYXMXK,--配方人
 decode(nvl(A.FHR001,''),'',' ',(select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00 = A.FHR001)) FHXMXK,--复核
 decode(nvl(A.FHR000,''),'',' ',(select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00 = A.FHR000)) SHXMXK,--审核
 decode(nvl(A.FYR000,''),'',' ',(select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00 = A.FYR000)) FYXMXK,--发药人
 decode(nvl(C.FYDLXH,''),'',' ',C.FYDLXH||'号')as FYDLX1,
 --(select decode(instr(bmmc00,'急'),0,'(门诊)','(急诊)') from Bm_bmbm00 where bmbh00=A.GHKS00) KFKSMC,
 decode(A.ZYSFJJ,'Y','(急煎)','(门诊)') 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) TYJE00,
 substr(SF_PY_CFMXBZ(A.CFLSH0),1,50) CFMXBZ,  --处方明细备注
 '0'||decode(mod(length(a.CFLSH0),2),0,'0','')||a.CFLSH0 CFHTXM,  --处方号条形码
 --decode(substr(A.CFZJE0,0,instr(A.CFZJE0,'.')-1),'-','-0','','0',substr(A.CFZJE0,0,instr(A.CFZJE0,'.')-1)) as jey000,
 substr(a.cfzje0*100,1,length(a.cfzje0*100)-2) as jey000,
 decode(instr(A.CFZJE0,'.'),0,0,substr(A.CFZJE0,instr(A.CFZJE0,'.')+1,1)) as jej000,
 decode(instr(A.CFZJE0,'.'),0,0,substr(A.CFZJE0,instr(A.CFZJE0,'.')+2,1)) as jef000,
 SP_BM_RMB000(a.cfzje0),
 (select yyqc00 from xt_yyxx00 where yyid00 in (select yyid00  from bm_bmbm00 where bmbh00=a.yfbmbh and rownum=1)) yymc00,
 (select djfsmc from YS_CFXXB0  where cflsh0=a.cflsh0) djfsmc,
 (select cfsm00 from YS_CFXXB0  where cflsh0=a.cflsh0) cfsm01,
 d.ypzl00,
 d.ypzsl0,
 decode(nvl(A.FYR000,''),'',' ',(select ZWXM00||yszjhm from BM_YGBM00 where YGBH00 = A.FYR000)) FYXMZJ,--发药人+证件
   decode(nvl(A.FHR000,''),'',' ',(select ZWXM00||yszjhm from BM_YGBM00 where YGBH00 = A.FHR000)) shXMZJ,--审核人+证件
     (select ZWXM00||yszjhm from BM_YGBM00 where YGBH00 = A.YSGZH0) YSXMZJ
from YF_MZCF00 A, BM_BRXXB0 B, YF_CFDL00 C,(SELECT COUNT(DISTINCT YPNM00) YPZL00,SUM(CSL000) YPZSL0,CFLSH0 FROM YF_MZCFMX GROUP BY CFLSH0) D
where A.BRID00+0 = B.BRID00 and A.CFLSH0 = C.CFLSH0(+) AND A.CFLSH0=D.CFLSH0;