create or replace view vw_ys_cfmxdy as
select
  decode(trim(a.BCCFH0),'',a.CFLSH0,a.BCCFH0) CFLSH0,a.GHID00,a.GHH000,
  SF_YS_GETYPMC(B.YPNM00)||DECODE(b.YPDLBH,'2',null,DECODE(b.YPGG00,NULL,NULL,'［'||trim(b.YPGG00)||'］')) YPMCGG,
  decode(to_char(abs(b.CSL000)),'0','','','',decode(substr(to_char(abs(b.CSL000)),1,1),'.','0','')||to_char(abs(b.CSL000))||trim(b.JLDW00)) CSL000,
  decode(pcmc00,'','',pcmc00) PCMC00,
  decode(to_char(b.YYSJ00),'0','','','',to_char(b.YYSJ00)||Trim(b.YYSJDW)) YYCXSJ,
  decode(to_char(b.YPZSL0),'0',null,'',null,to_char(b.YPZSL0)||b.CFDW00) YPZSL0,
  decode(to_char(abs(b.CSL000)),'0','','','',decode(substr(to_char(abs(b.CSL000)),1,1),'.','0','')||to_char(abs(b.CSL000))||trim(b.JLDW00))||
  decode(pcmc00,'','','/'||pcmc00)||decode(to_char(b.YYSJ00),'0','','','','/'||to_char(b.YYSJ00)||Trim(b.YYSJDW))||
  decode(ypyfmc,'','','/'||ypyfmc)||decode(pslbid,'','','/'||(select psmc00 from bm_ps0000 where psid00=pslbid)||'( )')
  ||decode(b.bz0000,'','','/'||b.bz0000)XYYF00,
  a.BCCFH0,
  b.zb0000,b.zjyfmc,b.xmyfmc||decode(sfzby0,'Y','［自备］','')xmyfmc,a.ZYTS00,B.BZ0000,a.ypdlbh,b.cfid00,a.ysxm00,a.bz0000 bz0001,
  b.CFDW00,b.YPZSL0 YPZSL1
from  yf_mzcf00 a,yf_mzcfmx b where a.cflsh0=b.cflsh0 and a.cfzt00<>'3';