CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YK_CKJZCX
 (
 MXZLSH, ZY0000, LBBH00, YLBH00, JXBH00, TYMBH0, YKBMBH, RCLXBH, RCLXMC, 
JZRQ00, JZSJ00, SHRQ00, SHSJ00, SFZBYP, SFZFDJ, CRBMBH, YPNM00, YPMC00, 
YPGG00, JZDW00, ZHL000, GJDJ00, PFDJ00, FCDJ00, FCSL00, FCJE00, ZKJE00, 
DJH000, SFSH00, SFJZ00, YPBM00, PYSM00, LSDJH0, CRBMMC, JZYF00, SCPH00, 
LBMC00, YLMC00, JXMC00, MZDJ00, SJDJ00, YPKCDW, YPCKPC, YDKCSL, SFJCYY, 
YPMLBZ, GJSLJE, GJFCJE, K2J000, YPRKPC, GHDWMC, ZBQMC0, SF4J70, YPSXRQ, 
JZXZ00, WZSX00

 )
 AS 
select
  Z.MXZLSH,
  Z.ZY0000,       --摘要
  D.LBBH00,       --类别编号（西、中、成）
  D.YLBH00,       --药理编号
  D.JXBH00,       --剂型编号
  D.TYMBH0,       --通用名编号
  Z.YKBMBH,       --药库部门编号
  decode(Z.RCLXBH,null,decode(sign(FCJE00),-1,'05'),Z.RCLXBH),       --出类型编号
--  (select decode(MAX(CKLXMC),null,decode(sign(FCJE00),-1,'科室申领'),MAX(CKLXMC)) from BM_YKCKLX where CKLXBH=Z.RCLXBH) RCLXMC,       --出库类型名称
  decode(Z.RCLXMC,null,decode(sign(FCJE00),-1,'科室申领'),Z.RCLXMC) RCLXMC, 
  Z.JZRQ00,       --记账日期
  Z.JZSJ00,       --记账时间
  Z.SHRQ00,       --审核日期
  Z.SHSJ00,       --审核时间
  Z.SFZBYP,       --是否中标药
  Z.SFZFDJ,       --是否政府定价
  Z.CRBMBH,       --出库部门编号
  Z.YPNM00,       --药品内码
  Z.YPMC00,       --药品名称
  Z.YPGG00,       --规格
  D.KCDW00 as JZDW00,       --记账单位
  D.K2J000,   --Z.ZHL000,       --转换率
  Z.GJDJ00,       --购进单价
  Z.PFDJ00,       --批发单价
  Z.FCDJ00,       
  ROUND(Z.FCSL00*Z.ZHL000/K2J000,4),       --付出数量
  Z.FCJE00,       --付出金额
  Z.ZKJE00,   --折扣金额
  Z.DJH000,       --单据号
  Z.SFSH00,       --是否审核
  Z.SFJZ00,　      --是否记账
  D.YPBM00,        --药品编码
  D.PYSM00,         --拼音首码 
  (Select BZ0000 FROM YK_YPQLD0  WHERE YPQLDH=Z.DJH000),
   DECODE(Z.CRBMBH,99999,'盘亏出库',88888,'报损出库',nvl(F.CRBMMC,(select BMMC00 from BM_BMBM00 where BMBH00=Z.CRBMBH))),  --出库去向部门名称
   substr(Z.YJZRQ0,1,6) JZYF00,
   trim(nvl(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,
   (SELECT JXMC00 FROM BM_YPJX00 WHERE LBBH00=D.LBBH00 AND JXBH00=D.JXBH00 ) JXMC00,
   D.MZDJ00,
   D.SJDJ00,
   D.KCDW00 YPKCDW,
   Z.YPCKPC,
   ROUND(D.KCSL00/D.K2J000,3) YDKCSL,
   D.SFJCYY,
   D.YPMLBZ,
   Z.GJSLJE,          --购进收入金额
   Z.GJFCJE,           --购进付出金额 
   D.K2J000,
   Z.YPRKPC,
   (select J.GHDWMC from YK_YPRKMX H,YK_YPRKD0 I,BM_YPGHDW J where H.YPRKPC=Z.YPRKPC and H.RKDH00=I.RKDH00 and I.GHDWNM=J.GHDWNM and rownum=1) as GHDWMC,
   D.ZBQMC0,       --中标期
   decode(D.SF4J70,'1','是','否') as SF4J70,       --4+7标识
   Z.YPSXRQ,       --失效日期
   Z.JZXZ00,
   NVL(z.wzsx00,(select max(WZSX00)from bm_wzqtxx a where a.ypnm00 = z.ypnm00))
from BM_YD0000 D,YK_YPMXZ0 Z,VW_YK_YPCRKDW F
where Z.JZXZ00='C' and RCLXBH<>'09'  --入库除去退库
  and Z.YPNM00=D.YPNM00
  AND Z.CRBMBH=F.CRBMNM(+)
UNION ALL
select
  Z.MXZLSH,
  Z.ZY0000,
  D.LBBH00,
  D.YLBH00,       --药理编号
  D.JXBH00,       --剂型编号
  D.TYMBH0,
  Z.YKBMBH,
  '05',
  (select CKLXMC from BM_YKCKLX where CKLXBH='05'),
  Z.JZRQ00,
  Z.JZSJ00,
  Z.SHRQ00,
  Z.SHSJ00,
  Z.SFZBYP,
  Z.SFZFDJ,       --是否政府定价
  Z.CRBMBH,
  Z.YPNM00,
  Z.YPMC00,
  Z.YPGG00,
  D.KCDW00 as JZDW00,       --记账单位
  D.K2J000,   --Z.ZHL000,       --转换率
  Z.GJDJ00,       --购进单价
  Z.PFDJ00,       --批发单价
  Z.FCDJ00, 
  -ROUND(Z.SLSL00*Z.ZHL000/K2J000,4),
  -Z.SLJE00,
  -Z.SLJE00,
  Z.DJH000,
  Z.SFSH00,
  Z.SFJZ00,
  D.YPBM00,        --药品编码
  D.PYSM00,         --拼音首码   　
  (Select BZ0000 FROM YK_YPRKD0 WHERE RKDH00=Z.DJH000),
  DECODE(Z.CRBMBH,99999,'盘亏出库',88888,'报损出库',nvl(F.CRBMMC,(select BMMC00 from BM_BMBM00 where BMBH00=Z.CRBMBH))),  --出库去向部门名称
  substr(Z.YJZRQ0,1,6) JZYF00,
  trim(nvl(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,
  (SELECT JXMC00 FROM BM_YPJX00 WHERE LBBH00=D.LBBH00 AND JXBH00=D.JXBH00 ) JXMC00,
  D.MZDJ00,
  D.SJDJ00,
  D.KCDW00 YPKCDW,
  Z.YPCKPC,
  ROUND(D.KCSL00/D.K2J000,3) YDKCSL,
  D.SFJCYY,
  D.YPMLBZ,
  -Z.GJFCJE,           --购进付出金额 
  -Z.GJSLJE,          --购进收入金额
  D.K2J000,
  Z.YPRKPC,
  (select J.GHDWMC from YK_YPRKMX H,YK_YPRKMX K,YK_YPRKD0 I,BM_YPGHDW J where H.YPRKPC=Z.YPRKPC and H.YPTHPC=K.YPRKPC and K.RKDH00=I.RKDH00 and I.GHDWNM=J.GHDWNM and rownum=1) as GHDWMC,
  D.ZBQMC0,       --中标期
  decode(D.SF4J70,'1','是','否') as SF4J70,       --4+7标识
  Z.YPSXRQ,       --失效日期
  Z.JZXZ00,
  NVL(z.wzsx00,(select max(WZSX00)from bm_wzqtxx a where a.ypnm00 = z.ypnm00))
 from BM_YD0000 D,YK_YPMXZ0 Z,VW_YK_YPCRKDW F
where JZXZ00='R' and RCLXBH='06'  --加上负的退库 
  and Z.YPNM00=D.YPNM00
  AND Z.CRBMBH=F.CRBMNM(+)
/