CREATE OR REPLACE VIEW SD_HOSPITAL.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, SF4J70, RKDH00, YPRKPC, MJPH00, CDMC00, MXBZ00, SFSB00

 )
 AS 
select
  Z.MXZLSH,       --流水号
  Z.ZY0000,       --摘要
  D.LBBH00,       --类别编号（西、中、成）
  D.YLBH00,       --药理编号
  D.JXBH00,       --剂型编号
  (select KHYY00 from BM_YPGHDW where GHDWNM=Z.CRBMBH),       --开户行
  (select YHZH00 from BM_YPGHDW where GHDWNM=Z.CRBMBH),       --账号
  Z.YKBMBH,       --药库部门编号
  Z.RCLXBH,       --入出类型编号
  --decode(Z.RCLXMC,'退货出库','退货出库',(select RKLXMC from BM_YKRKLX where RKLXBH=Z.RCLXBH)) RCLXMC,       --入库类型名称
  decode(Z.RCLXMC,(select CKLXMC from BM_YKCKLX where CKLXBH='09'),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,
  D.ZBQMC0,
  D.YPBM00,
  D.PYSM00,
  B.YPSXRQ,
  (Select BZ0000 from YK_YPRKD0 where RKDH00=b.RKDH00) LSDJH0,  
  --(select substr(nvl(substr(BZ0000,instr(BZ0000,'名',1)+2,instr(BZ0000,',',-1)-(instr(BZ0000,'名',1)+2)),BZ0000),1,100) 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,
  decode(z.rclxbh,'09',(select FPRQ00 FROM YK_YPCKMX WHERE yprkpc=Z.YPRKPC AND YPQLDH=Z.djh000 and rownum=1 ), b.FPRQ00) FPRQ00,
  decode(D.SF4J70,'1','是','否') SF4J70,
  b.RKDH00,       --入库单号
  b.YPRKPC,       --药品入库批次
  b.MJPH00,       --灭菌批号
  b.CDMC00,       --产地名称
  b.BZ0000,
  z.SFSB00        --是否上报 
from BM_YD0000 D,YK_YPRKMX b,YK_YPMXZ0 Z
where Z.JZXZ00='R' and Z.RCLXBH<>'06'  --入库除去退库
  and Z.YPNM00=D.YPNM00
  and Z.YPRKPC=b.YPRKPC(+)
union all
select
  Z.MXZLSH,       --流水号
  Z.ZY0000,
  D.LBBH00,
  D.YLBH00,       --药理编号
  D.JXBH00,       --剂型编号
  (select KHYY00 from BM_YPGHDW where GHDWNM=Z.CRBMBH),       --开户行
  (select YHZH00 from BM_YPGHDW where GHDWNM=Z.CRBMBH),       --账号
  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,
  D.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,
  decode(D.SF4J70,'1','是','否') SF4J70,
  b.RKDH00,       --入库单号
  b.YPRKPC,       --药品入库批次
  b.MJPH00,       --灭菌批号
  b.CDMC00,
  b.BZ0000,
  z.SFSB00        --是否上报 
 from BM_YD0000 D,YK_YPMXZ0 Z,YK_YPRKMX b
where Z.JZXZ00='C' and Z.RCLXBH='09'  --加上负的退货
  and Z.YPNM00=D.YPNM00
  and Z.YPRKPC=b.YPRKPC(+)
/