CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YF_ZSMMZCX
 (
 CFLSH0, BRXM00, YFBMBH, CFZT00, CFZTMC, FYRQ00, CFID00, YPMC00, YPGG00, 
YPZSL0, CFDW00, YPNM00, PZBSM0, YSMSL0, SFCLYP, MZZYBZ, YSCSL0, SRRQ00, 
SRRQSJ, FYRQSJ, GHH000, GHID00, FYR000, FYRXM0, SFKSLY

 )
 AS 
select
  A.CFLSH0,                                                      --处方流水号
  A.BRXM00,                                                      --患者姓名
  A.YFBMBH,                                                      --药房部门编号
  A.CFZT00,                                                      --处方状态
  decode(A.CFZT00,'0',decode(A.BCCFH0,null,'未记账','未退费'),'1','已记账','4','未审核','2','已发药','3','已作废','5','已退药','6','已退费') CFZTMC,  --处方状态名称
  A.FYRQ00,                                                      --发药日期
  X.CFID00,                                                      --明细处方ID
  X.YPMC00,                                                      --药品名称
  X.YPGG00,                                                      --药品规格
  abs(X.YPZSL0) YPZSL0,                                          --药品数量
  X.CFDW00,                                                      --处方单位
  X.YPNM00,                                                      --药品内码
  substrb((select to_char(wm_concat(PZBSM0)) from  YK_YPZSM0 where YPNM00=B.YPNM00),1,200) PZBSM0,       --药品标识码
  decode(X.CXBZ00,'-',(select nvl(sum(TYZSL0),0)/X.ZHL000 from YF_TYZSMX where  CFLSH0 = X.CFLSH0 and YPNM00 = X.YPNM00 and YPQLPC = X.CFID00 and YZLB00 = '0'),
  ( select nvl(sum(K.YPZSL0),0)/X.ZHL000 from YF_CFZSMX k where k.CFLSH0 = X.CFLSH0 and k.YPNM00 = X.YPNM00 and K.YPQLPC = X.CFID00 and YZLB00 = '0' )) YSMSL0,   --追溯码已扫数量
  decode(nvl(B.ZSCLBZ,'0'),'1','Y','3','Y','N') SFCLYP,          --是否拆零药品
  '0' MZZYBZ,                                                    --门诊住院备注 
  decode(X.CXBZ00,'-',( select nvl(sum(YPZSL0),0)/X.ZHL000 from YF_YZZSMX M,YF_YBZSRZ N where M.ZSID00 = N.ZSID00 and N.LB0000 = '1' and M.MZZYBZ = '0' and YZLB00 = '0' and N.CFLSH0 = A.BCCFH0 and YPQLPC = X.CXCFID ),
  ( select nvl(sum(YPZSL0),0)/X.ZHL000 from YF_YZZSMX M,YF_YBZSRZ N where M.ZSID00 = N.ZSID00 and N.LB0000 = '0' and M.MZZYBZ = '0' and YZLB00 = '0' and N.CFLSH0 = A.CFLSH0 and YPQLPC = X.CFID00 )) YSCSL0, --已上传数量
  A.SRRQ00,
  A.SRRQ00||' '||A.SRSJ00 SRRQSJ,
  A.FYRQ00||' '||A.FYSJ00 FYRQSJ,
  A.GHH000,
  A.GHID00,
  A.FYR000,    --发药人编号
  ( select ZWXM00 from BM_YGBM00 where YGBH00 = A.FYR000 ) FYRXM0,   --发药人姓名
  A.SFKSLY                                                           --是否科室领药
from YF_MZCFMX X,BM_YD0000 B,YF_MZCF00 A
where  X.YPNM00 = B.YPNM00
 and   X.CFLSH0 = A.CFLSH0
 and   X.SFZBY0 <> 'Y'  and   nvl(B.XSWZSM,0) <> '1'
 --and   A.CFZT00 in ('2','5','6')
 and   exists ( select 1 from YK_YPZSM0 where YPNM00 = X.YPNM00 )
/