-- Start of DDL Script for View SD_HOSPITAL.VW_YF_YPTKPHTS
-- Generated 2017-10-26 14:41:13 from SD_HOSPITAL@TOSD_kfclYY

CREATE OR REPLACE VIEW vw_yf_yptkphts (
   ykbmbh,
   yfbmbh,
   ypqldh,
   yprkpc,
   ypnm00,
   ypmc00,
   lsdj00,
   pfdj00,
   gjdj00,
   cksl00,
   scph00,
   ypsxrq,
   sccjbh,
   sccjzw,
   zhl000,
   qrrq00,
   fph000,
   ghdwmc,
   xxlsdj,
   xxgjdj )
AS
SELECT
  D.YKBMBH,
  D.CKQXBH,
  X.YPQLDH,
  X.YPRKPC,
  X.YPNM00,
  X.YPMC00,
--  nvl(DECODE((select trim(VALUE0)  from XT_XTCS00 where name00='YF_SFDLSJGL'),'N',(select LSDJ00 from YF_YPKCXX where YFBMBH=D.CKQXBH and YPNM00=M.YPNM00),M.XXLSDJ),M.XXLSDJ),
  nvl(DECODE((select trim(VALUE0)  from XT_XTCS00 where name00='YF_SFDLSJGL'),'N',
--  nvl(M.LSDJ00,(select LSDJ00 from YF_YPKCXX where YFBMBH=D.CKQXBH and YPNM00=M.YPNM00)),
DECODE((select trim(VALUE0)  from XT_XTCS00 where name00='YF_SFASLJGTK'),'N',(select LSDJ00 from YF_YPKCXX where YFBMBH=D.CKQXBH and YPNM00=M.YPNM00),
DECODE((select trim(VALUE0)  from XT_XTCS00 where name00='YK_YF_LSJSFDLGL'),'N',(select LSDJ00 from YF_YPKCXX where YFBMBH=D.CKQXBH and YPNM00=M.YPNM00),M.LSDJ00)
),
  M.XXLSDJ),M.XXLSDJ),
  M.PFDJ00,
--    nvl(DECODE((select trim(VALUE0)  from XT_XTCS00 where name00='YF_SFDLSJGL'),'N',(select GJDJ00 from YF_YPKCXX where YFBMBH=D.CKQXBH and YPNM00=M.YPNM00),DECODE((SELECT TRIM(VALUE0)FROM XT_XTCS00 WHERE NAME00='YK_ZRJRCJ'),'Y',M.GJDJ00,M.FPGJDJ)),DECODE((SELECT TRIM(VALUE0)FROM XT_XTCS00 WHERE NAME00='YK_ZRJRCJ'),'Y',M.GJDJ00,M.FPGJDJ)),
  nvl(DECODE((select trim(VALUE0)  from XT_XTCS00 where name00='YF_SFDLSJGL'),'N',
 -- nvl(M.YGJDJ0,(select GJDJ00 from YF_YPKCXX where YFBMBH=D.CKQXBH and YPNM00=M.YPNM00)),
  DECODE((select trim(VALUE0)  from XT_XTCS00 where name00='YF_SFASLJGTK'),'N',(select GJDJ00 from YF_YPKCXX where YFBMBH=D.CKQXBH and YPNM00=M.YPNM00),
DECODE((select trim(VALUE0)  from XT_XTCS00 where name00='YK_YF_LSJSFDLGL'),'N',(select GJDJ00 from YF_YPKCXX where YFBMBH=D.CKQXBH and YPNM00=M.YPNM00),M.YGJDJ0)
),

  DECODE((SELECT TRIM(VALUE0)FROM XT_XTCS00 WHERE NAME00='YK_ZRJRCJ'),'Y',M.GJDJ00,M.FPGJDJ)),
  DECODE((SELECT TRIM(VALUE0)FROM XT_XTCS00 WHERE NAME00='YK_ZRJRCJ'),'Y',M.GJDJ00,M.FPGJDJ)),
  X.CKSL00,
  M.SCPH00,
  M.YPSXRQ,
  M.SCCJBH,
  M.SCCJZW,
  M.ZHL000,
  D.QRRQ00,
  M.FPH000,
   (select ghdwmc from VW_YK_GHDW00  where ghdwnm=C.ghdwnm),
  M.XXLSDJ,
  M.GJDJ00
FROM YK_YPRKMX M,YK_YPCKMX X,YK_YPQLD0 D,YK_YPRKD0 C
WHERE D.QLZT00='2'  --'2'领用部门入库
  AND D.QRRQ00>=TO_CHAR(SYSDATE-365,'YYYYMMDD')
  AND D.YPQLDH=X.YPQLDH
  AND M.YPRKPC=X.YPRKPC
  AND M.rkdh00=C.rkdh00
  --update by dsm 20171026
  and exists(select 1 from yf_pckcmx where yfbmbh=d.ckqxbh and ypnm00=m.ypnm00 and scph00=m.scph00 and sjkcsl>0)
ORDER BY SCPH00 DESC,X.YPQLDH
/


-- End of DDL Script for View SD_HOSPITAL.VW_YF_YPTKPHTS