---配药汇总条码视图
select a.*, ' ' as QRURL0 from VW_PY_FYBQ00 a where a.CFLSH0 = '13903680';

---配药明细条码视图
select * from VW_PY_YPBQXX WHERE CFLSH0 = '13903680';



create or replace view vw_py_fybq00 as
select
  A.DLXH00, --队列序号
  to_char(A.FYDLXH)FYDLXH, --发药队列序号
  A.CFLSH0, --处方号
  A.BRID00, --病人ID
  a.ghh000,
  A.BRXM00, --姓名
  a.xb0000,
  SUBSTRB(sf_bq_csrqtonl(A.CSRQ00),1,10) AS BRNL00,
  (select ysxm00 from yf_mzcf00 where cflsh0=a.cflsh0) as ysxm00,
  substrb(sf_yf_getzd0(a.ghh000),1,100) as brzdxx,
  (select zwxm00 from bm_ygbm00 where ygbh00=a.pyqrr0) pyr000,
  a.ypje00,
  a.dyrq00||' '||a.dysj00  as dyrqsj,
  A.FYCKBH, --发药窗口号
  A.YFBMBH, --药房部门号
  '共'||(select count(*) from YF_CFDL00 where BRID00 = A.BRID00 and FYDLXH = A.FYDLXH
      and FYCKBH = A.FYCKBH and YFBMBH = A.YFBMBH and PHRQ00=A.PHRQ00)||'张' as CFZS00, --处方张数
  (select lpad(CKMC00,2,'0') from bm_ckbm00 where ckbh00=a.FYCKBH and rownum=1)||nvl(A.PHRQ00,to_char(sysdate,'YYYYMMDD'))||lpad(to_char(A.FYDLXH),4,'0') TXMSJ0,
  '00'||nvl(A.PHRQ00,to_char(sysdate,'YYYYMMDD'))||lpad(to_char(A.FYDLXH),4,'0') TXMSJ1
from YF_CFDL00 A
where DLXH00 = (select min(DLXH00) from YF_CFDL00 where BRID00 = A.BRID00
                   and FYDLXH = A.FYDLXH and FYCKBH = A.FYCKBH and YFBMBH = A.YFBMBH and PHRQ00=A.PHRQ00)
union all
select
  null, --队列序号
  null, --发药队列序号
  A.CFLSH0, --处方号
  A.BRID00, --病人ID
  a.ghh000,
  A.BRXM00, --姓名
  a.xb0000,
  SUBSTRB(sf_bq_csrqtonl(A.CSRQ00),1,10) AS BRNL00,
  a.ysxm00,
  substrb(sf_yf_getzd0(a.ghh000),1,100) as brzdxx,
  null,
  a.cfzje0,
  null,
  null, --发药窗口号
  A.YFBMBH, --药房部门号
  '共'||(select count(*) from YF_MZCF00 where BRID00 = A.BRID00
                   and KFPC00 = A.KFPC00 and YFBMBH = A.YFBMBH)||'张' CFZS00, --处方张数
  null,
  null
from YF_MZCF00 A
where not exists (select 1 from YF_CFDL00 where CFLSH0=a.CFLSH0) and
      CFLSH0 = (select min(CFLSH0) from YF_MZCF00 where BRID00 = A.BRID00
                   and KFPC00 = A.KFPC00 and YFBMBH = A.YFBMBH);

/

create or replace view vw_py_ypbqxx as
select
  C.CFLSH0, --处方号
  substrb(C.FYRQ00,1,4)||'-'||substrb(C.FYRQ00,5,2)||'-'||substrb(C.FYRQ00,7,2) FYRQ00, --发药日期
  I.CFID00, --
  C.BRXM00, --姓名
  C.XB0000, --性别
  substrb(sf_bq_csrqtonl(c.csrq00),1,10) brnl00,
  --I.YPMC00, --药品名称
  d.YPMC00,  --药品名称
  I.YPYFMC, --药品用法名称
  '【'||I.YPGG00||'】' YPGG00,
  substr(SF_YF_GETYPKCZLXX(i.YPZSL0*i.ZHL000,2,d.K2J000,d.ZJJL2J,d.KCDW00,d.ZJJLDW,d.JLDW00),1,10) SLDW00,   --药品总量
--  decode(substr(trim(I.YPZSL0),1,1),'.','0','')||trim(I.YPZSL0)||trim(I.CFDW00) SLDW00,
    nvl((select trim(SM0000)||' ' from BM_PC0000 M where M.PCMC00=I.PCMC00 and rownum=1),' ')||
    decode(I.CSL000,null,null,'每次'||rtrim(replace(to_char(i.CSL000*i.F2J000/d.ZJJL2J,'FM9999990.99')||' ','. ',''))|| trim(d.ZJJLDW)||
    ' ('||rtrim(replace(to_char(I.CSL000,'FM9999990.99')||' ','. ',''))|| trim(I.JLDW00)||')') XXYF00, --详细用法

     '每次'||decode(trim(d.zyfydw),'支',i.csl000||i.jldw00,'瓶',i.csl000||i.jldw00,'片',decode(substr((i.csl000/d.z2j000),1,1),'.','0'||to_char(i.csl000/d.z2j000,'fm9999.00'),i.csl000/d.z2j000)||d.zyfydw,'盒',i.csl000||i.jldw00,'粒',
     decode(substr((i.csl000/d.z2j000),1,1),'.','0'||to_char(i.csl000/d.z2j000,'fm999.00'),i.csl000/d.z2j000)||d.zyfydw,'贴',i.csl000/d.z2j000||d.zyfydw,'袋',decode(substr((i.csl000/d.z2j000),1,1),'.','0'||i.csl000/d.z2j000,i.csl000/d.z2j000)||
     d.zyfydw,'包',i.csl000/d.z2j000||d.zyfydw,'板',i.csl000/d.z2j000||d.zyfydw,i.csl000) xxyf01 ,

  nvl(D.BZ0000,E.MC0000) BZ0000, --用法备注
  D.YFID00,  --用法ID
  (select trim(SM0000)||' ' from BM_PC0000 M where M.PCMC00=I.PCMC00 and rownum=1) as pcmc00,

  I.HJJE00,  --合计金额
  C.YSXM00,  --医生姓名
  (select BMMC00 from BM_BMBM00 where BMBH00=c.GHKS00) GHKSMC   --就诊科室名称
  ,(select pdxh00 from yf_ypkcxx kc where kc.ypnm00 = i.YPNM00 and kc.yfbmbh = c.yfbmbh)pdxh00 -- 盘点序号
from YF_MZCFMX I, BM_YD0000 D, YF_MZCF00 C,VW_YK_YPCCFS E
where I.YPNM00=D.YPNM00 and I.SFZBY0='N' and I.CFLSH0=C.CFLSH0 and D.CCFS00=E.NBBH00(+)  and i.hjje00>=0
  --and (LBBH00 in ('0','1') and (I.YPYFMC in (select YPYFMC from BM_YPYF00 where YFLBID = 0) or D.BZ0000 is not null
  --     or exists (select 1 from VW_YK_YPCCFS E where D.CCFS00=E.NBBH00)) or YFBMBH=2273);