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

 )
 AS 
select
  C.QRRQ00,
  C.YFBMBH,
  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,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.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.YPNM00,A.YPMC00,A.YPGG00,A.ZHL000,A.QLDW00
union all
select
  C.QRRQ00,
  C.YFBMBH,
  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,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.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.YPNM00,A.YPMC00,A.YPGG00,A.ZHL000,A.QLDW00
/