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, zbj000, ypjcl0, cyjl00, kc0000, kcsl00, kcsx00, kcxx00, sfkyyy, cfjb00, gzdj00, mzdj00, dpdj00, sjdj00, cfwz00, cfwzmc, lbbh00, ylbh00, jxbh00, ypjxmc, sccjbh, sccjzw, sfzbyp, ghdwnm, ghdwmc, zbxh00, pzwh00, zbqnm0, yptym0, zbqmc0, cdid00, cdmc00, mrcgsl, ypmlbz, zjjldw, k2zjjl, zjjl2j, sfba00, ypxzmc)
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.ZBJ000,
  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,
  (select CFWZMC from BM_YPCFWZ where CFWZBH=D.CFWZ00 and rownum=1) as CFWZMC,
  D.LBBH00,
  D.YLBH00,
  D.JXBH00,
  (select JXMC00 from BM_YPJX00 where JXBH00=D.JXBH00 and LBBH00=d.LBBH00 and rownum=1 ) as YPJXMC,
  C.SCCJBH,
  C.SCCJZW,
  D.SFZBYP,
  D.GHDWNM,
  (select W.GHDWMC from BM_YPGHDW W where D.GHDWNM=W.GHDWNM),
  D.ZBXH00,
  D.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,
  D.ZJJLDW,
  D.K2ZJJL,
  D.ZJJL2J,
  D.SFBA00,
  (select MC0000 from BM_TYZD00 where ZDMC00=trim('药典维护-药品性质') and BH0000=D.YPXZ00 ) YPXZMC
  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
    and not exists (select 1 from BM_WZQTXX m where d.ypnm00=m.ypnm00 and m.wzsx00='2' and nvl(m.SFYXRK,'N')='N')
  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.ZBJ000,
  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,
  (select CFWZMC from BM_YPCFWZ where CFWZBH=D.CFWZ00 and LBBH00=d.LBBH00 and rownum=1) as CFWZMC,
  D.LBBH00,
  D.YLBH00,
  D.JXBH00,
  (select JXMC00 from BM_YPJX00 where JXBH00=D.JXBH00 and rownum=1) as YPJXMC,
  C.SCCJBH,
  C.SCCJZW,
  D.SFZBYP,
  D.GHDWNM,
  (select W.GHDWMC from BM_YPGHDW W where D.GHDWNM=W.GHDWNM),
  D.ZBXH00,
  D.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,
  D.ZJJLDW,
  D.K2ZJJL,
  D.ZJJL2J,
  D.SFBA00,
  (select MC0000 from BM_TYZD00 where ZDMC00=trim('药典维护-药品性质') and BH0000=D.YPXZ00 ) YPXZMC
  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'
    and not exists (select 1 from BM_WZQTXX m where d.ypnm00=m.ypnm00 and m.wzsx00='2' and nvl(m.SFYXRK,'N')='N');