-- Start of DDL Script for View SD_HOSPITAL.VW_YK_RKJZ00
-- Generated 17-四月-2012 11:56:45 from SD_HOSPITAL@ORCL

CREATE OR REPLACE VIEW vw_yk_rkjz00 (
   mxzlsh,
   zy0000,
   lbbh00,
   ylbh00,
   jxbh00,
   khyy00,
   yhzh00,
   ykbmbh,
   rclxbh,
   rclxmc,
   jzrq00,
   jzsj00,
   shrq00,
   shsj00,
   sfzbyp,
   sfzfdj,
   crbmbh,
   ypmc00,
   ypgg00,
   fph000,
   jzdw00,
   zhl000,
   gjdj00,
   pfdj00,
   sldj00,
   slsl00,
   gjje00,
   slje00,
   zkje00,
   djh000,
   sfsh00,
   sfjz00,
   ypbm00,
   pysm00,
   sccjzw,
   ghdwmc,
   ypsxrq,
   crbmxz,
   lsdjh0,
   scph00,
   ypnm00,
   kcdw00,
   lbmc00,
   ylmc00,
   jxmc00,
   sfjcyy,
   ypmlbz,
   pzwh00,
   gjslje,
   gjfcje )
AS
select
  Z.MXZLSH,       --流水号
  Z.ZY0000,       --摘要
  D.LBBH00,       --类别编号（西、中、成）
  D.YLBH00,       --药理编号
  D.JXBH00,       --剂型编号
  W.KHYY00,       --开户行
  W.YHZH00,       --账号
  Z.YKBMBH,       --药库部门编号
  Z.RCLXBH,       --入出类型编号
  Z.RCLXMC,       --入库类型名称
  Z.JZRQ00,       --记账日期
  Z.JZSJ00,       --记账时间
  Z.SHRQ00,       --审核日期
  Z.SHSJ00,       --审核时间
  Z.SFZBYP,       --是否中标药
  Z.SFZFDJ,       --是否政府定价
  Z.CRBMBH,       --入库部门编号
  Z.YPMC00,       --药品名称
  Z.YPGG00,       --规格
  b.FPH000,       --发票号
  Z.JZDW00,       --记账单位
  Z.ZHL000,       --转换率
  Z.GJDJ00,       --购进单价
  Z.PFDJ00,       --批发单价
  Z.SLDJ00,       --收入单价
  Z.SLSL00,       --收入金额
  B.GJJE00,
  Z.SLJE00,       --收入金额
  Z.ZKJE00,	  --折扣金额
  Z.DJH000,       --单据号
  Z.SFSH00,       --是否审核
  Z.SFJZ00,      --是否记账
  D.YPBM00,        --药品编码
  D.PYSM00,         --拼音首码   
  B.SCCJZW,       --生产厂家
  (select G.GHDWMC from VW_YK_GHDW00 G where Z.CRBMBH=G.GHDWNM),
  b.YPSXRQ,
  (select BMXZ00 FROM BM_BMBM00 WHERE BMBH00=Z.CRBMBH),   --入部门性质    
  (Select BZ0000 FROM YK_YPRKD0 WHERE RKDH00=b.RKDH00),
   Z.SCPH00,
   z.ypnm00,
   D.KCDW00,
   (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 AND ROWNUM=1) JXMC00,
   D.SFJCYY,
   D.YPMLBZ, 
   b.PZWH00,
     Z.GJSLJE,          --购进收入金额
  Z.GJFCJE           --购进付出金额 
from BM_YD0000 D,BM_YPGHDW W,YK_YPRKMX b,YK_YPMXZ0 Z
where Z.JZXZ00='R'
  and Z.YPNM00=D.YPNM00 and Z.CRBMBH=W.GHDWNM(+)
  and Z.YPRKPC=b.YPRKPC(+)
/


-- End of DDL Script for View SD_HOSPITAL.VW_YK_RKJZ00