CREATE OR REPLACE VIEW VW_YF_YPMXZ0
(mxzlsh, yfbmbh, yprkpc, ypckpc, ypnm00, ypmc00, ypgg00, jzrq00, jzsj00, crbmbh, zy0000, jzdw00, zhl000, gjdj00, sldj00, slsl00, slje00, fcdj00, fcsl00, fcje00, jcdj00, jcsl00, jcje00, rclxbh, rclxmc, jzxz00, djh000, kcdw00, mzfydw, zyfydw, k2j000, m2j000, z2j000, jldw00, gjslje, gjfcje, jcgjje, lbbh00, ylbh00, jxbh00, bzgg00, ypbm00, ykgjje, yklsje, lsdj00, lsje00, gjje00, jcslxx, crslxx, crrqsj, crbz00, zkje00, pfdj00, scph00, ypsxsj, rkgjdj, ckgjdj, shf000, fhf000, fph000, sccjzw, pc0000, czflmc, dlxz00, cdmc00, zjjldw, zjjl2j, kssjb0, slsljl, fcsljl)
AS

--chenzh   纠正SLDJ00,FCDJ00的显示
SELECT
  a.MXZLSH,
  a.YFBMBH,
  a.YPRKPC,
  a.YPCKPC,
  a.YPNM00,
  b.YPMC00,
  a.YPGG00,
  a.JZRQ00,
  a.JZSJ00,
  a.CRBMBH,
  case when a.JZXZ00='C' and a.RCLXBH='09' then '门诊'||substr(SF_YF_CFLSH_GETBRXM(A.DJH000,A.RCLXBH,A.YPNM00,nvl(A.YPCKPC,0)),1,20)
       when a.JZXZ00='C' and a.RCLXBH='10' then '住院'||substr(SF_YF_CFLSH_GETBRXM(A.DJH000,A.RCLXBH,A.YPNM00,nvl(A.YPCKPC,0)),1,20)
        else a.ZY0000 end ZY0000,
  a.JZDW00,
  a.ZHL000,
  a.GJDJ00,
  ROUND(a.SLDJ00,4) SLDJ00,
  ROUND(a.SLSL00*a.ZHL000/b.K2J000,3) SLSL00,
  a.SLJE00,
  ROUND(a.FCDJ00,4) FCDJ00,
  ROUND(a.FCSL00*a.ZHL000/b.K2J000,3) FCSL00,
  a.FCJE00,
  a.JCDJ00,
  a.JCSL00,
  a.JCJE00,
  a.RCLXBH,
  a.RCLXMC,
  a.JZXZ00,
  a.DJH000,
  b.KCDW00,
  b.MZFYDW,
  b.ZYFYDW,
  b.K2J000,
  b.M2J000,
  b.Z2J000,
  b.JLDW00,
  nvl(a.GJSLJE,round(a.SLSL00*a.ZHL000/b.K2J000*a.GJDJ00,2)),
  nvl(a.GJFCJE,round(a.FCSL00*a.ZHL000/b.K2J000*a.GJDJ00,2)),
  nvl(a.JCGJJE,round(a.JCSL00*a.ZHL000/b.K2J000*a.GJDJ00,2)),
  b.LBBH00,
  b.YLBH00,
  b.JXBH00,
  b.K2ZJJL BZGG00,  --包装规格
  b.YPBM00,         --药品内码
  0 YKGJJE,
  0 YKLSJE,
  decode(a.JZXZ00,'C',a.FCDJ00,a.SLDJ00) LSDJ00,--零售单价
  decode(a.JZXZ00,'C',a.FCJE00,a.SLJE00) LSJE00,--零售总额
  decode(a.JZXZ00,'C',a.GJFCJE,a.GJSLJE) GJJE00,--购进总额
  substr(SF_YF_YPSLZH(a.JCSL00,a.YPNM00,3),1,50) JCSLXX, --结存数量信息
  --decode(a.JZXZ00,'C',a.FCSL00,a.SLSL00)||a.JZDW00 CRSLXX, --出入数量信息
  decode(a.JZXZ00,'C',substr(SF_YF_YPSLZH(a.FCSL00*a.ZHL000,a.YPNM00,3),1,50),substr(SF_YF_YPSLZH(a.SLSL00*a.ZHL000,a.YPNM00,3),1,50)) CRSLXX, --出入数量信息
  to_char(to_date(a.JZRQ00,'YYYYMMDD'),'YYYY-MM-DD')||' '||a.JZSJ00 CRRQSJ, --出入库日期时间
  a.JZXZ00 CRBZ00,
  c.ZKJE00 zkje00,
  decode(nvl(c.pfdj00,''),'',b.pfj000,c.pfdj00) pfdj00,
  C.SCPH00,
  C.YPSXRQ as ypsxsj,
  decode(a.JZXZ00,'R',a.GJDJ00,NULL) RKGJDJ,  --入库购进单价
  decode(a.JZXZ00,'C',a.GJDJ00,NULL) CKGJDJ,  --出库购进单价
  case when a.JZXZ00='R' then d.BMMC00
       when a.JZXZ00='C' and a.RCLXBH='09' then '门诊'
       when a.JZXZ00='C' and a.RCLXBH='10' then '住院'
        else a.ZY0000 end SHF000,
  decode(a.JZXZ00,'R',a.zy0000,d.bmmc00) FHF000,   --发货方
  decode(a.JZXZ00,'C','',c.FPH000) FPH000,
  c.sccjzw,
  a.YPRKPC PC0000,
  decode(b.YPCZFL,'2','全成分颗粒','1','中草药颗粒',(decode(b.LBBH00,'0','西药','1','中成药','2','普通中草药'))) CZFLMC,      --'3','包药机药品',
  decode(sign(to_number(sjdj00)+to_number(dpdj00)+to_number(mzdj00)),1,'麻精','普通') DLXZ00,
  C.CDMC00,    --产地名称
  b.ZJJLDW,    --中间计量单位
  b.ZJJL2J,    --中间计量/剂量 转换率
  b.KSSJB0,    --抗生素级别
  a.SLSL00*a.ZHL000 SLSLJL,   --收入数量(剂量单位)
  a.FCSL00*a.ZHL000 FCSLJL    --发出数量(剂量单位)
FROM YF_YPMXZ0 a,BM_YD0000 b,YK_YPRKMX c,bm_bmbm00 d
WHERE a.YPNM00=b.YPNM00  and a.ykrkpc=c.yprkpc(+) and a.yfbmbh=d.bmbh00;