CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YF_BQSMYP
 (
 QRRQ00, YFBMBH, BQBMBH, YPNM00, YPMC00, YPGG00, CKDW00, PZBSM0, SMSL00, 
YSMSL0, SFCYDY

 )
 AS 
select
  C.QRRQ00,
  C.YFBMBH,
  A.BQBMBH,
  A.YPNM00,
  A.YPMC00,
  A.YPGG00,
  A.QLDW00 CKDW00,
  substrb((select to_char(wm_concat(PZBSM0)) from  YK_YPZSM0 where YPNM00=A.YPNM00),1,200) PZBSM0,      --药品标识码
  sum(A.YPZSL0-A.ZCXSL0) SMSL00,
  --(select nvl(sum(M.YPZSL0),0)/A.ZHL000 from YF_CFZSMX M where  M.MZZYBZ = '1' and M.FYTYBZ = '1'  and M.YZLB00 = '1' and M.YPNM00 = A.YPNM00
  -- and  exists (  select  1 from YF_YPCKD0 N,YF_YZYPSQ X where N.CKDH00 = X.CKDH00 AND X.CXBZ00 = 'Z' and X.YZXZ00 <> '3' and N.CKLXBH = '03' and N.QRRQ00 = C.QRRQ00   and N.CKDH00 = M.CFLSH0
  -- and  X.YPQLPC=M.YPQLPC  AND  X.BQBMBH = A.BQBMBH and N.YFBMBH = C.YFBMBH ) ) YSMSL0,
   (select nvl(sum(M.YPZSL0),0)/A.ZHL000 from YF_CFZSMX M,YF_YZYPSQ F where  M.MZZYBZ = '1' and M.YZLB00 = '1' and M.YPNM00 = A.YPNM00   and M.YPQLPC=F.YPQLPC and F.YFBMBH=C.YFBMBH and F.FYRQ00=C.QRRQ00 and F.BQBMBH=A.BQBMBH
    and F.YZXZ00 <> '3' and   (F.CXBZ00 = 'Z' or (F.CXBZ00='+' and F.YPZSL0>F.ZCXSL0) ) ) YSMSL0, 
   'N' SFCYDY
from YF_YZYPSQ A,YF_YPCKD0 C
where 1 = 1
and   A.CKDH00 = C.CKDH00
and   C.CKLXBH = '03'
and   (A.CXBZ00 = 'Z' or (A.CXBZ00='+' AND A.YPZSL0>A.ZCXSL0))
and   A.YZXZ00 <> '3'
and   exists ( select 1 from YK_YPZSM0 where YPNM00 = A.YPNM00 )
and   exists ( select 1 from BM_YD0000 where YPNM00 = A.YPNM00 and nvl(XSWZSM,0)<> '1')
group by C.QRRQ00,C.YFBMBH,A.BQBMBH,A.YPNM00,A.YPMC00,A.YPGG00,A.ZHL000,A.QLDW00
union all
select
  C.QRRQ00,
  C.YFBMBH,
  A.BQBMBH,
  A.YPNM00,
  A.YPMC00||'(出院带药)',
  A.YPGG00,
  A.QLDW00 CKDW00,
  substrb((select to_char(wm_concat(PZBSM0)) from  YK_YPZSM0 where YPNM00=A.YPNM00),1,200) PZBSM0,      --药品标识码
  sum(A.YPZSL0-A.ZCXSL0) SMSL00,
  --(select nvl(sum(M.YPZSL0),0)/A.ZHL000 from YF_CFZSMX M where  M.MZZYBZ = '1' and M.FYTYBZ = '1'  and M.YZLB00 = '1' and M.YPNM00 = A.YPNM00
  -- and  exists (  select  1 from YF_YPCKD0 N,YF_YZYPSQ X where N.CKDH00 = X.CKDH00 AND X.CXBZ00 = 'Z' and X.YZXZ00 = '3' and N.CKLXBH = '03' and N.QRRQ00 = C.QRRQ00   and N.CKDH00 = M.CFLSH0
  --  and  X.YPQLPC=M.YPQLPC  AND  X.BQBMBH = A.BQBMBH and N.YFBMBH = C.YFBMBH ) ) YSMSL0,
  (select nvl(sum(M.YPZSL0),0)/A.ZHL000 from YF_CFZSMX M,YF_YZYPSQ F where  M.MZZYBZ = '1' and M.YZLB00 = '1' and M.YPNM00 = A.YPNM00   and M.YPQLPC=F.YPQLPC and F.YFBMBH=C.YFBMBH and F.FYRQ00=C.QRRQ00 and F.BQBMBH=A.BQBMBH
   and F.YZXZ00 = '3' and   (F.CXBZ00 = 'Z' or (F.CXBZ00='+' AND F.YPZSL0>F.ZCXSL0) ) ) YSMSL0, 
   'Y'  SFCYDY
from YF_YZYPSQ A,YF_YPCKD0 C
where 1 = 1
and   A.CKDH00 = C.CKDH00
and   C.CKLXBH = '03'
and   (A.CXBZ00 = 'Z' or (A.CXBZ00='+' AND A.YPZSL0>A.ZCXSL0))
and   A.YZXZ00 = '3'
and   exists ( select 1 from YK_YPZSM0 where YPNM00 = A.YPNM00 )
and   exists ( select 1 from BM_YD0000 where YPNM00 = A.YPNM00 and nvl(XSWZSM,0)<> '1')
group by C.QRRQ00,C.YFBMBH,A.BQBMBH,A.YPNM00,A.YPMC00,A.YPGG00,A.ZHL000,A.QLDW00
/