CREATE or replace VIEW SD_HOSPITAL.VW_YK_YPCKCX
 (
 YPQLDH, YKBMBH, CKQXBH, CKQXMC, CKLXBH, CKLXMC, QLRQ00, QLSJ00, CZYBH0, 
CZYXM0, QRRBH0, QRRXM0, LYRBH0, LYRXM0, SHRBH0, SHRXM0, QLZT00, QRRQ00, 
QRSJ00, SHRQ00, GJZJE0, LSZJE0, YPSXRQ, SCCJZW, SCPH00, YPNM00, YPBM00, 
LBBH00, YLBH00, JXBH00, YPMC00, YPGG00, QLDW00, GJDJ00, PFDJ00, QLDJ00, 
ZHL000, QLSL00, SFSL00, BZ0000, SFSH00, SFZBYP, PYSM00, YPCKPC, RFPH00, 
YPXZ01, CFPH00, FPRQ00, YPMLBZ

 )
 AS 
SELECT distinct
  Q.YPQLDH,
  Q.YKBMBH,
  Q.CKQXBH,
  DECODE(Q.CKQXBH,99999,'盘亏出库',88888,'报损出库',(SELECT F.CRBMMC FROM VW_YK_YPCRKDW F WHERE Q.CKQXBH=F.CRBMNM)),  --出库去向部门名称
  Q.CKLXBH,
  L.CKLXMC,
  Q.QLRQ00,
  Q.QLSJ00,
  Q.CZYBH0,
  (SELECT Y1.ZWXM00 FROM BM_YGBM00 Y1 WHERE Q.CZYBH0=Y1.YGBH00),
  Q.QRRBH0,
  (SELECT Y2.ZWXM00 FROM BM_YGBM00 Y2 WHERE Q.QRRBH0=Y2.YGBH00),
  Q.LYRBH0,
  (SELECT Y3.ZWXM00 FROM BM_YGBM00 Y3 WHERE Q.LYRBH0=Y3.YGBH00),
  Q.SHRBH0,
  (SELECT Y4.ZWXM00 FROM BM_YGBM00 Y4 WHERE Q.SHRBH0=Y4.YGBH00),
  Q.QLZT00,
  Q.QRRQ00,
  Q.QRSJ00,
  Q.SHRQ00,
  0,
  0,
  R.YPSXRQ,
  R.SCCJZW,
  R.SCPH00,
  X.YPNM00,
  D.YPBM00,
  D.LBBH00,
  D.YLBH00,
  D.JXBH00,
  X.YPMC00,
  X.YPGG00,
  X.CKDW00,
  X.GJDJ00,
  X.PFDJ00,
  X.LSDJ00,
  X.ZHL000,
  X.CKSL00,
  X.CKSL00,
  X.BZ0000,
  Q.SFSH00,
  R.SFZBYP,
  D.PYSM00,
  X.YPCKPC,
  R.FPH000,
  DECODE(D.YPXZ00,1,'基本药物',2,'临时用处方药',3,'临时用非处方药',4,'非基本药物') YPXZ01,
  M.FPH000,
  M.FPRQ00,
  D.YPMLBZ
FROM YK_YPQLD0 Q,YK_YPCKMX X,BM_YKCKLX L,YK_YPRKMX R,BM_YD0000 D,YK_YPQLMX M
WHERE  Q.YPQLDH=X.YPQLDH
   AND Q.CKLXBH=L.CKLXBH(+)
   AND X.YPNM00=D.YPNM00
   AND X.YPRKPC=R.YPRKPC
   AND Q.ypqldh=M.ypqldh
   AND M.YPNM00=D.ypnm00
   and X.YPQLPC=M.YPQLPC
   --AND M.FPH000 IS NOT NULL
/