CREATE OR REPLACE VIEW VW_YK_YPSRTS
(ypnm00, ykbmbh, ypbm00, pysm00, wbsm00, ypmc00, spm000, ypgg00, kcdw00, jldw00, zyfydw, mzfydw, k2j000, z2j000, m2j000, pfj000, lsj000, gjj000, ypjcl0, cyjl00, kc0000, kcsl00, kcsx00, kcxx00, sfkyyy, cfjb00, gzdj00, mzdj00, dpdj00, sjdj00, cfwz00, lbbh00, ylbh00, jxbh00, sccjbh, sccjzw, sfzbyp, ghdwnm, ghdwmc, zbxh00, pzwh00, zbqnm0, yptym0, zbqmc0, cdid00, cdmc00, mrcgsl, ypmlbz)
AS
SELECT
  D.YPNM00,
  X.YKBMBH,
  D.YPBM00,
  D.PYSM00,
  D.WBSM00,
  D.YPMC00,
  D.YPMC00,
  D.YPGG00,
  D.KCDW00,
  D.JLDW00,
  D.ZYFYDW,
  D.MZFYDW,
  D.K2J000,
  D.Z2J000,
  D.M2J000,
  D.PFJ000,
  D.LSJ000,
  D.GJJ000,
  D.YPJCL0,
  D.CYJL00,
  TO_CHAR(ROUND(DECODE((SELECT TRIM(VALUE0) FROM XT_XTCS00 WHERE NAME00='YK_SFQYLSYP'),'Y',
  X.KCSL00,D.KCSL00)/D.K2J000,3))||trim(D.KCDW00),
  NVL(DECODE((SELECT TRIM(VALUE0) FROM XT_XTCS00 WHERE NAME00='YK_SFQYLSYP'),'Y',
  X.KCSL00,D.KCSL00),0)/D.K2J000,
  X.KCSX00,
  X.KCXX00,
  D.SFKYYY,
  D.CFJB00,
  D.GZDJ00,
  D.MZDJ00,
  D.DPDJ00,
  D.SJDJ00,
  D.CFWZ00,
  D.LBBH00,
  D.YLBH00,
  D.JXBH00,
  C.SCCJBH,
  C.SCCJZW,
  D.SFZBYP,
  D.GHDWNM,
  (select W.GHDWMC from BM_YPGHDW W where D.GHDWNM=W.GHDWNM),
  D.ZBXH00,
  nvl((select k.PZWH00 from YK_YPRKMX k,YK_YPRKD0 h where k.YPNM00=D.YPNM00 and k.RKDH00=h.RKDH00 and h.YKBMBH=x.YKBMBH
  and h.JHRQ00||h.JHSJ00=(select max(b.JHRQ00||b.JHSJ00) from YK_YPRKMX a,YK_YPRKD0 b  where a.RKDH00=b.RKDH00 and b.YKBMBH=x.YKBMBH and a.YPNM00=k.YPNM00)
  and rownum=1),d.pzwh00)  PZWH00,
  (SELECT Q.ZBQNM0 FROM BM_ZBQZD0 Q WHERE Q.ZBQMC0=D.ZBQMC0),
  D.YPTYM0,
  D.ZBQMC0,
  D.CDID00,
  (select E.CDMC00 from VW_YK_YPCDZD E where E.CDID00=D.CDID00) CDMC00,
  X.MRCGSL, --默认采购数量
  D.YPMLBZ
  FROM BM_YPSCCJ C,BM_YD0000 D,YK_YPKCXX X
  WHERE D.TZRQ00 IS NULL AND X.PDXH00<>'0' AND D.YPNM00=X.YPNM00
    AND C.SCCJBH(+) = D.SCCJBH
  UNION ALL
  SELECT
  D.YPNM00,
  X.YKBMBH,
  D.YPBM00,
  B.PYSM00,
  B.WBSM00,
  B.BMMC00,
  D.YPMC00,
  D.YPGG00,
  D.KCDW00,
  D.JLDW00,
  D.ZYFYDW,
  D.MZFYDW,
  D.K2J000,
  D.Z2J000,
  D.M2J000,
  D.PFJ000,
  D.LSJ000,
  D.GJJ000,
  D.YPJCL0,
  D.CYJL00,
  TO_CHAR(ROUND(DECODE((SELECT TRIM(VALUE0) FROM XT_XTCS00 WHERE NAME00='YK_SFQYLSYP'),'Y',
  X.KCSL00,D.KCSL00)/D.K2J000,3))||trim(D.KCDW00),
  DECODE((SELECT TRIM(VALUE0) FROM XT_XTCS00 WHERE NAME00='YK_SFQYLSYP'),'Y',
  X.KCSL00,D.KCSL00)/D.K2J000,
  X.KCSX00,
  X.KCXX00,
  D.SFKYYY,
  D.CFJB00,
  D.GZDJ00,
  D.MZDJ00,
  D.DPDJ00,
  D.SJDJ00,
  D.CFWZ00,
  D.LBBH00,
  D.YLBH00,
  D.JXBH00,
  C.SCCJBH,
  C.SCCJZW,
  D.SFZBYP,
  D.GHDWNM,
  (select W.GHDWMC from BM_YPGHDW W where D.GHDWNM=W.GHDWNM),
  D.ZBXH00,
  nvl((select k.PZWH00 from YK_YPRKMX k,YK_YPRKD0 h where k.YPNM00=D.YPNM00 and k.RKDH00=h.RKDH00 and h.YKBMBH=x.YKBMBH
  and h.JHRQ00||h.JHSJ00=(select max(b.JHRQ00||b.JHSJ00) from YK_YPRKMX a,YK_YPRKD0 b  where a.RKDH00=b.RKDH00 and b.YKBMBH=x.YKBMBH and a.YPNM00=k.YPNM00)
  and rownum=1),d.pzwh00) PZWH00,
  (SELECT Q.ZBQNM0 FROM BM_ZBQZD0 Q WHERE Q.ZBQMC0=D.ZBQMC0),
  D.YPTYM0,
  D.ZBQMC0,
  D.CDID00,
  (select E.CDMC00 from VW_YK_YPCDZD E where E.CDID00=D.CDID00) CDMC00,
  X.MRCGSL, --默认采购数量
  D.YPMLBZ
  FROM  BM_YPSCCJ C,BM_YD0000 D,BM_YPBM00 B,YK_YPKCXX X
  WHERE D.YPNM00=B.YPNM00 AND D.TZRQ00 IS NULL AND D.YPNM00=X.YPNM00
    AND C.SCCJBH(+) = D.SCCJBH AND X.PDXH00<>'0';