create or replace view vw_yf_bqsmyp as
select
  C.QRRQ00,
  C.YFBMBH,
  A.YPNM00,
  A.YPMC00,
  A.YPGG00,
  A.CKDW00,
  substrb((select to_char(wm_concat(PZBSM0)) from  YK_YPZSM0 where YPNM00=A.YPNM00),1,200) PZBSM0       --Ò©Æ·±êʶÂë
  --(select count(1) from YF_CFZSMX M where M.FYTYBZ = '1' and M.YPNM00 = A.YPNM00 and M.CFLSH0 in ( select N.CKDH00 from YF_YPCKD0 N where N.YFBMBH = C.YFBMBH and N.QRRQ00 = C.QRRQ00 and N.CKLXBH = '03')) SMSL00
from YF_YPCKMX A,YF_YPCKD0 C,YF_YZYPSQ D
where 1 = 1
and   A.CKDH00 = C.CKDH00
and   C.CKLXBH = '03'
and   A.CKSL00 > 0
and   exists ( select 1 from YK_YPZSM0 X where X.YPNM00 = A.YPNM00 )
and   A.YPQLPC = D.YPQLPC
and   A.CKDH00 = D.CKDH00
--and   D.YZXZ00 <> '3'
--and   not exists ( select 1 from YF_YZYPSQ Y where Y.CKDH00 = A.CKDH00 and Y.YPQLPC = A.YPCKPC and Y.YZXZ00 = '3')
group by C.QRRQ00,C.YFBMBH,A.YPNM00,A.YPMC00,A.YPGG00,A.CKDW00
;