CREATE OR REPLACE VIEW VW_YK_RKJZCX
(mxzlsh, zy0000, lbbh00, ylbh00, jxbh00, khyy00, yhzh00, ykbmbh, rclxbh, rclxmc, jzrq00, jzsj00, shrq00, shsj00, sfzbyp, sfzfdj, crbmbh, ypnm00, ypmc00, tymbh0, yptym0, jxmc00, ypgg00, fph000, jzdw00, zhl000, gjdj00, pfdj00, sldj00, slsl00, gjje00, slje00, zkje00, djh000, sfsh00, sfjz00, sccjzw, ghdwmc, bz0000, zbxh00, pzwh00, zbq000, ypbm00, pysm00, ypsxrq, lsdjh0, jzyf00, scph00, lbmc00, ylmc00, mzdj00, sjdj00, ydkcsl, sfjcyy, ypmlbz, gjslje, gjfcje, ygjdj0, ygjje0, k2j000, ypczfl, sjrclx, fpdm00, fprq00)
AS
select
  Z.MXZLSH,       --流水号
  Z.ZY0000,       --摘要
  D.LBBH00,       --类别编号（西、中、成）
  D.YLBH00,       --药理编号
  D.JXBH00,       --剂型编号
  W.KHYY00,       --开户行
  W.YHZH00,       --账号
  Z.YKBMBH,       --药库部门编号
  Z.RCLXBH,       --入出类型编号
  decode(Z.RCLXMC,'退货出库','退货出库',(select RKLXMC from BM_YKRKLX where RKLXBH=Z.RCLXBH)) RCLXMC,       --入库类型名称
  Z.JZRQ00,       --记账日期
  Z.JZSJ00,       --记账时间
  Z.SHRQ00,       --审核日期
  Z.SHSJ00,       --审核时间
  Z.SFZBYP,
  Z.SFZFDJ,
  Z.CRBMBH,       --入库部门编号
  Z.YPNM00,       --药品内码
  Z.YPMC00,       --药品名称
  D.TYMBH0,       --通用名编号
  D.YPTYM0,       --药品通用名
  (SELECT J.JXMC00 FROM BM_YPJX00 J WHERE J.LBBH00=D.LBBH00 AND J.JXBH00=D.JXBH00),       --剂型名称
  Z.YPGG00,       --规格
  b.FPH000,       --发票号
  D.KCDW00,       --记账单位
  D.K2J000,       --转换率
  Z.GJDJ00,       --购进单价
  Z.PFDJ00,       --批发单价
  Z.SLDJ00,       --收入单价
  ROUND(Z.SLSL00*Z.ZHL000/D.K2J000,3),     --收入数量
  NVL(B.GJJE00,Z.GJSLJE),                  --购进金额
  Z.SLJE00,       --收入金额
  Z.ZKJE00,       --折扣金额
  Z.DJH000,       --单据号
  Z.SFSH00,       --是否审核
  Z.SFJZ00,       --是否记账
  B.SCCJZW,
  (select G.GHDWMC from VW_YK_KSGHDW G where Z.CRBMBH=G.GHDWNM),
  D.BZ0000,       --备注
  B.ZBXH00,
  nvl(b.pzwh00,D.PZWH00) pzwh00,
  (select M.ZBQMC0 from BM_ZBQZD0 M where M.ZBQNM0=Z.ZBQNM0) ZBQMC0,
  D.YPBM00,D.PYSM00,B.YPSXRQ,
  (select BZ0000 FROM YK_YPRKD0 WHERE RKDH00=b.RKDH00),
  substr(Z.YJZRQ0,1,6),
  Z.SCPH00,
  (SELECT LBMC00 FROM BM_YPLB00 WHERE LBBH00=D.LBBH00 AND ROWNUM=1) LBMC00,
  (SELECT YLMC00 FROM BM_YPYL00 WHERE LBBH00=D.LBBH00 AND YLBH00=D.YLBH00 AND ROWNUM=1) YLMC00,
  D.MZDJ00,
  D.SJDJ00,
  ROUND(D.KCSL00/D.K2J000,3) YDKCSL,
  D.SFJCYY,          --是否基层用药
  D.YPMLBZ,
  Z.GJSLJE,          --购进收入金额
  Z.GJFCJE,          --购进付出金额
  B.YGJDJ0,          --原购进单价
  ROUND(Z.SLSL00*Z.ZHL000/D.K2J000*B.YGJDJ0,2), --原购进金额
  D.K2J000,
  D.YPCZFL,
  Z.RCLXBH,
  b.FPDM00,
  b.FPRQ00
from BM_YD0000 D,BM_YPGHDW W,YK_YPRKMX b,YK_YPMXZ0 Z
where Z.JZXZ00='R' and RCLXBH<>'06'  --入库除去退库
  and Z.YPNM00=D.YPNM00 and Z.CRBMBH=W.GHDWNM(+)
  and Z.YPRKPC=b.YPRKPC(+)
UNION ALL
select
  Z.MXZLSH,       --流水号
  Z.ZY0000,
  D.LBBH00,
  D.YLBH00,       --药理编号
  D.JXBH00,       --剂型编号
  W.KHYY00,
  W.YHZH00,
  Z.YKBMBH,
  decode((select BMMC00 from BM_BMBM00 where BMBH00=Z.YKBMBH),'制剂库','03','01' ),
  decode((select BMMC00 from BM_BMBM00 where BMBH00=Z.YKBMBH),'制剂库',(select RKLXMC from BM_YKRKLX where RKLXBH='03'),(select RKLXMC from BM_YKRKLX where RKLXBH='01') ),
  Z.JZRQ00,
  Z.JZSJ00,
  Z.SHRQ00,
  Z.SHSJ00,
  Z.SFZBYP,
  Z.SFZFDJ,
  Z.CRBMBH,
  Z.YPNM00,
  Z.YPMC00,
  D.TYMBH0,       --通用名编号
  D.YPTYM0,       --药品通用名
  (SELECT J.JXMC00 FROM BM_YPJX00 J WHERE J.LBBH00=D.LBBH00 AND J.JXBH00=D.JXBH00),       --剂型名称
  Z.YPGG00,
  (SELECT Q.FPH000 FROM YK_YPQLMX Q WHERE Q.YPQLDH=Z.DJH000 AND Q.YPNM00=Z.YPNM00 and ROWNUM=1),
  D.KCDW00,
  D.K2J000,
  Z.GJDJ00,
  Z.PFDJ00,
  Z.FCDJ00,
  -ROUND(Z.FCSL00*Z.ZHL000/D.K2J000,3),
  ---NVL(ROUND(Z.GJDJ00*ROUND(Z.FCSL00*Z.ZHL000/D.K2J000,3),2),Z.GJFCJE),
  -NVL(ROUND(Z.GJDJ00*trunc(Z.FCSL00*Z.ZHL000/D.K2J000),2) + ROUND(Z.GJDJ00*mod(Z.FCSL00*Z.ZHL000/D.K2J000,1),2),Z.GJFCJE),
  -Z.FCJE00,
  -Z.ZKJE00,
  Z.DJH000,
  Z.SFSH00,
  Z.SFJZ00,
  (SELECT R.SCCJZW FROM YK_YPRKMX R,YK_YPCKMX C WHERE R.YPRKPC=C.YPRKPC AND C.YPCKPC=Z.YPCKPC) SCCJZW,
  (select G.GHDWMC from VW_YK_KSGHDW G where Z.CRBMBH=G.GHDWNM),
  D.BZ0000,       --备注
  (SELECT R.ZBXH00 FROM YK_YPRKMX R,YK_YPCKMX C WHERE R.YPRKPC=C.YPRKPC AND C.YPCKPC=Z.YPCKPC) ZBXH00,
  nvl(b.pzwh00,D.PZWH00) pzwh00,
  (select M.ZBQMC0 from BM_ZBQZD0 M where M.ZBQNM0=Z.ZBQNM0) ZBQMC0,
  D.YPBM00,D.PYSM00,Z.YPSXRQ,
  (Select BZ0000 FROM YK_YPQLD0 WHERE YPQLDH=Z.DJH000),
  substr(Z.YJZRQ0,1,6),
  Z.SCPH00,
  (SELECT LBMC00 FROM BM_YPLB00 WHERE LBBH00=D.LBBH00 AND ROWNUM=1) LBMC00,
  (SELECT YLMC00 FROM BM_YPYL00 WHERE LBBH00=D.LBBH00 AND YLBH00=D.YLBH00 AND ROWNUM=1) YLMC00,
  D.MZDJ00,
  D.SJDJ00,
  ROUND(D.KCSL00/D.K2J000,3) YDKCSL,
  D.SFJCYY,              --是否基层用药
  D.YPMLBZ,
  -Z.GJFCJE,           --购进付出金额
  -Z.GJSLJE,             --购进收入金额
  B.YGJDJ0,              --原购进价
  ---NVL(ROUND(Z.GJDJ00*ROUND(Z.FCSL00*Z.ZHL000/D.K2J000,3),2),Z.GJFCJE), --原购进金额
  -NVL(ROUND(Z.GJDJ00*trunc(Z.FCSL00*Z.ZHL000/D.K2J000),2) + ROUND(Z.GJDJ00*mod(Z.FCSL00*Z.ZHL000/D.K2J000,1),2),Z.GJFCJE), --原购进金额
  D.K2J000,
  D.YPCZFL,
  Z.RCLXBH,
  b.FPDM00,
  b.FPRQ00
 from BM_YD0000 D,BM_YPGHDW W,YK_YPMXZ0 Z,YK_YPRKMX b
where JZXZ00='C' and RCLXBH='09'  --加上负的退货
  and Z.YPNM00=D.YPNM00
  and Z.CRBMBH=W.GHDWNM(+)
  and Z.YPRKPC=b.YPRKPC(+);