create or replace view vw_yf_pckcmx
(yfbmbh, ypmc00, ypnm00, lsdj00, scph00, ypsxrq, yksl00, sjsl00, k2j000, kcdw00, gjdj00, ypbm00, ypgg00, lbbh00, ylbh00, jxbh00, tymbh0, yprkpc, pdxh00, zhl000, pfdj00, byfl00, zjjldw, zjjl2j, sccjbh, sccjzw, sfyx00, colour, sjkcxx, hgh000, czflmc, ghdwmc, sfztpd,PYSM00)
as
select a.YFBMBH,b.YPMC00,a.YPNM00,a.LSDJ00,a.SCPH00,a.YPSXRQ,
 round(a.YKKCSL/b.K2J000,3) YKSL00,round(a.SJKCSL/b.K2J000,3) SJSL00 ,
 b.K2J000 ,b.KCDW00,a.GJDJ00,
  b.YPBM00, --药品编码
  b.YPGG00, --药品规格  
  b.LBBH00, --药品类别编号
  b.YLBH00, --药理编号
  b.JXBH00, --剂型编号
  b.TYMBH0, --通用名编号
  a.YKRKPC, --批次
  c.PDXH00, --盘点序号
  b.K2J000, --转换率
  B.PFJ000, --批发价  针对库存单位的价格
  b.BYFL00, --摆药分类
  b.ZJJLDW, --中间计量单位(J)  
  b.ZJJL2J, --中间计量/剂量 转换率     
  A.SCCJBH, --生产厂家编号
  (select SCCJZW from BM_YPSCCJ where SCCJBH = a.SCCJBH and rownum=1) SCCJZW, --生产厂家名称
  a.SFYX00,
  decode(a.SFYX00,'Y','0','$0000FF'),
  substr(SF_YF_GETYPKCZLXX(a.SJKCSL,2,b.k2j000,b.zjjl2j,b.kcdw00,b.zjjldw,b.jldw00),1,100),
  C.QWM000,
  decode(b.YPCZFL,'2','全成分颗粒','1','中草药颗粒',(decode(b.LBBH00,'0','西药','1','中成药','2','普通中草药'))) CZFLMC,  --'3','包药机药品',
  (select aa.GHDWMC from BM_YPGHDW aa,YK_YPRKMX bb,YK_YPRKD0 cc where bb.YPRKPC=a.YKRKPC and bb.RKDH00=cc.RKDH00 and cc.GHDWNM=aa.GHDWNM and rownum=1) as GHDWMC,
  C.SFZTPD,
  b.PYSM00
from YF_PCKCMX a,BM_YD0000 b,YF_YPKCXX c 
where c.YFBMBH=a.YFBMBH and c.YPNM00=a.YPNM00 and c.YPNM00=b.YPNM00
;