CREATE OR REPLACE VIEW VW_YF_PCKCXX
(yfbmbh, ypnm00, ypbm00, pysm00, wbsm00, ypmc00, ypgg00, czflmc, ypdw00, kcdw00, jldw00, zyfydw, mzfydw, k2j000, z2j000, m2j000, lsj000, gjj000, zhl000, kcsx00, kcxx00, ykkcsl, sjkcsl, kcje00, lbbh00, ylbh00, jxbh00, tymbh0, scph00, pdxh00, sfztpd, sccjzw, ypmlbz, zjjl2j, k2zjjl, kclsh0, ykrkpc, hgh000, sfyx00, ykkcxx, sjkcxx, gjje00, pxxh00, ypsxrq, gyry00, gyrqsj, scrq00, ykkcjl, sjkcjl, zjjldw, ghdwmc, ztbz00, sjkchj, ylmc00, gjbzbm, gjbzmc)
AS
select
      A.YFBMBH,--F.YFBMBH YFBMBH,
      A.YPNM00,--D.YPNM00 YPNM00,
      D.YPBM00 YPBM00,
      D.PYSM00 PYSM00,
      D.WBSM00 WBSM00,
      D.YPMC00 YPMC00,
      D.YPGG00 YPGG00,
      decode(D.YPCZFL,'2','全成分颗粒','1','中草药颗粒',(decode(D.LBBH00,'0','西药','1','中成药','2','普通中草药'))) CZFLMC, --'3','包药机药品',
      D.KCDW00 YPDW00,
      D.KCDW00 KCDW00,
      D.JLDW00 JLDW00,
      D.ZYFYDW ZYFYDW,
      D.MZFYDW MZFYDW,
      D.K2J000 K2J000,
      D.Z2J000 Z2J000,
      D.M2J000 M2J000,
      F.LSDJ00 LSDJ00,
      --D.GDJ000 GJJ000,
      F.GJDJ00 GJJ000,
      D.K2J000 ZHL000,
      D.KCSX00,
      D.KCXX00,
      round(F.YKKCSL/D.K2J000,3) YKKCSL,
      round(F.SJKCSL/D.K2J000,3) SJKCSL,
      round((F.SJKCSL/D.K2J000)*F.LSDJ00,2) ZJE000,
      D.LBBH00,
      D.YLBH00,
      D.JXBH00,
      D.TYMBH0,
      F.SCPH00,
      A.PDXH00,
      A.SFZTPD,
      (select trim(SCCJZW) from BM_YPSCCJ where SCCJBH=nvl(F.SCCJBH,D.SCCJBH)) SCCJZW,
      D.YPMLBZ,
      D.ZJJL2J,
      D.K2ZJJL,
      F.KCLSH0,
      F.YKRKPC,
      A.QWM000,
      F.SFYX00,
      substr(SF_YF_GETYPKCZLXX(F.YKKCSL,2,D.K2J000,D.ZJJL2J,D.KCDW00,D.ZJJLDW,D.JLDW00),1,100) YKKCXX,
      substr(SF_YF_GETYPKCZLXX(F.SJKCSL,2,D.K2J000,D.ZJJL2J,D.KCDW00,D.ZJJLDW,D.JLDW00),1,100) SJKCXX,
      round((F.SJKCSL/D.K2J000)*F.GJDJ00,2) GJJE00,
      0,
      F.YPSXRQ,
      F.GYRY00,
      F.GYRQSJ,
      (select SCRQ00 from YK_YPRKMX where YPRKPC=F.YKRKPC) SCRQ00,
      F.YKKCSL YKKCJL,
      F.SJKCSL SJKCJL,
      D.ZJJLDW,
      --(select GHDWMC from BM_YPGHDW where GHDWNM=D.GHDWNM) GHDWMC,
      (SELECT a.GHDWMC FROM BM_YPGHDW a,YK_YPRKD0 b,YK_YPRKMX c
WHERE a.GHDWNM=b.GHDWNM and b.RKDH00=c.RKDH00 and c.YPRKPC=F.YKRKPC and rownum=1) GHDWMC,
      A.ZTBZ00,
      substr(SF_YF_GETYPKCZLXX(F.SJKCSL,12,D.K2J000,D.ZJJL2J,D.KCDW00,D.ZJJLDW,D.JLDW00)|| D.ZJJLDW,1,100) SJKCHJ,  ---实际库存小单位（取到中间计量）  for  YF9-20200103-002
      (select YLMC00 from BM_YPYL00 where YLBH00=D.YLBH00) YLMC00,         --药品药理
      D.GJBZBM,
      D.GJBZMC
    from YF_PCKCMX F,BM_YD0000 D,YF_YPKCXX A
    where F.YPNM00=D.YPNM00 and F.YFBMBH=A.YFBMBH and F.YPNM00=A.YPNM00 and  D.TZRQ00 is  null and F.SJKCSL>0
    union all
    select
      M.YFBMBH,
      M.YPNM00,--N.YPNM00,
      N.YPBM00,
      N.PYSM00,
      N.WBSM00,
      N.YPMC00,
      N.YPGG00,
      decode(N.YPCZFL,'2','全成分颗粒','1','中草药颗粒',(decode(N.LBBH00,'0','西药','1','中成药','2','普通中草药'))) CZFLMC, --'3','包药机药品',
      N.KCDW00 YPDW00,
      N.KCDW00,
      N.JLDW00,
      N.ZYFYDW,
      N.MZFYDW,
      N.K2J000,
      N.Z2J000,
      N.M2J000,
      M.LSDJ00,
      --N.GJJ000,
      M.GJDJ00,
      N.K2J000 ZHL000,
      N.KCSX00,
      N.KCXX00,
      round(M.YKKCSL/N.K2J000,3) YKKCSL,
      round(M.SJKCSL/N.K2J000,3) SJKCSL,
      round((M.SJKCSL/N.K2J000)*M.LSDJ00,2) ZJE000,
      N.LBBH00,
      N.YLBH00,
      N.JXBH00,
      N.TYMBH0,
      '',
      M.PDXH00,
      M.SFZTPD,
      (select trim(sccjzw) from BM_YPSCCJ where sccjbh=N.sccjbh) SCCJZW,
      N.YPMLBZ,
      N.ZJJL2J,
      N.K2ZJJL,
      '0',
      0,
      M.QWM000,
      '',
      substr(SF_YF_GETYPKCZLXX(M.YKKCSL,2,N.K2J000,N.ZJJL2J,N.KCDW00,N.ZJJLDW,N.JLDW00),1,100) YKKCXX,
      substr(SF_YF_GETYPKCZLXX(M.SJKCSL,2,N.K2J000,N.ZJJL2J,N.KCDW00,N.ZJJLDW,N.JLDW00),1,100) SJKCXX,
      round((M.SJKCSL/N.K2J000)*M.GJDJ00,2) GJJE00,
      0,
      (select YPSXRQ from YF_PCKCMX where YPNM00=m.YPNM00 and rownum =1) YPSXRQ,
      null,
      null,
      null,
      M.YKKCSL YKKCJL,
      M.SJKCSL SJKCJL,
      N.ZJJLDW,
      (select GHDWMC from BM_YPGHDW where GHDWNM=N.GHDWNM) GHDWMC,
      M.ZTBZ00,
      substr(SF_YF_GETYPKCZLXX(M.SJKCSL,12,N.K2J000,N.ZJJL2J,N.KCDW00,N.ZJJLDW,N.JLDW00)|| N.ZJJLDW,1,100) SJKCHJ,         --实际库存小单位（取到中间计量）     forYF9-20200103-002
           (select YLMC00 from BM_YPYL00 where YLBH00=N.YLBH00) YLMC00,         --药品药理
      N.GJBZBM,
      N.GJBZMC
      from YF_YPKCXX M,BM_YD0000 N,BM_BMBM00 C
      where M.YPNM00=N.YPNM00 and M.SJKCSL=0 and  N.TZRQ00 is  null
      and M.YFBMBH=C.BMBH00
      and not exists(
      select 1 from YF_PCKCMX H where H.YFBMBH=M.YFBMBH and H.YPNM00=M.YPNM00
         and (H.SJKCSL>0 and C.BMXZ00 not in ('2','3') or C.BMXZ00 in ('2','3')));