--药品：
-- Person      Date        Comments
-- cc       2015.05.20    增加yblb00,yblbmc,zfbl00
-- cc       2015.05.26    增加zyfydw，mzfydw，k2j000,z2j000,m2j000
-- cc       2015.06.05    增加c.fbbh00,c.ybzxlb,c.ybbrlb,
-- cc       2015.08.11    增加BZ0000以取得更详细的药物说明
-- --------- ------------- --------------------------------------------------
select  b.ypbm00 DrugChargeID,b.ypnm00 DrugCode,b.ypmc00 GenericName,
        b.yptym0 TradeName,b.ypgg00 Specifications,b.kcdw00 Unit ,
        b.jxbh00 FormCode,
        (select JXMC00 from BM_YPJX00 c where c.jxbh00 = b.jxbh00 and rownum =1 ) FormName,
       (select SCCJZW from BM_YPSCCJ d where d.SCCJBH = b.SCCJBH and rownum =1) Factory,
       b.lbbh00 kindcode,
       decode(b.lbbh00,'0','西药','1','成药','2','草药','未知') KindName,    --药品大类编号 '0'西 药,'2'草药'1'成药
       a.fydw00 PackagingLevel,
       a.yfbmbh PharmacyCode,
       (select bmmc00 from bm_bmbm00 where bmbh00 =  a.yfbmbh) PharmacyName,
       B.WBSM00,
       B.PYSM00,
       b.lsj000 Price,
       1 PriceMeaning,
       round(a.SJKCSL/b.K2J000,3) Stock,
       case when b.tzrq00 is null then 1
            else 0
            end Status,
        ' ' ApprovalNumber,
        b.YLBH00 PharmacologyCode1,
        (select YLMC00 from BM_YPYL00 e where e.YLBH00 = b.YLBH00 and rownum =1) PharmacologyName1,
        YLBH00 PharmacologyCode2,
        (select YLMC00 from BM_YPYL00 f where f.YLBH00 = b.YLBH00 and rownum =1) PharmacologyName2,
        0 IsAntibiotic,
        b.kssjb0 AntibioticClass,
        1 AntibioticDegrade,
        ' ' InsertDateTime,
        B.CFJB00,
        B.GZDJ00,
        B.MZDJ00,
        B.DPDJ00,
        B.SJDJ00,
        b.ypsysm,
        substr(c.bz0000||decode(nvl(b.KSSJB0,0),0,'',' 抗级别:'||b.KSSJB0),1,120) BZ0000,
        (select ypyfmc from bm_ypyf00 c where c.yfid00 = b.yfid00 and rownum = 1) ypyfmc,
        b.cyl000 syl000,
        b.jldw00 zxdw00,
        (select pcmc00 from bm_pc0000 where pcid00 = b.pcid00 and rownum = 1) pcmc00,
        b.sfpsyy,
        c.yblb00,decode(c.YBLB00,'4','甲类','5','乙类','6','民族','7','自付','8','医保') YBLBMC,
        c.zfbl00,
        b.zyfydw,b.mzfydw,b.k2j000,b.z2j000,b.m2j000,
        c.fbbh00,c.ybzxlb,c.ybbrlb
 --       from YF_YPKCXX a,BM_YD0000 b
 -- where a.YPNM00=b.YPNM00
from YF_YPKCXX a,BM_YD0000 b,bm_ybsfdy c
  where a.YPNM00=b.YPNM00 and b.ypnm00=c.sfxmid and a.ypnm00=c.sfxmid(+) and c.sfyp00='Y'