-- Start of DDL Script for View SD_HOSPITAL.VW_YK_YPPCKC
-- Generated 28-七月-2021 17:03:56 from SD_HOSPITAL@MHDEYY

CREATE OR REPLACE VIEW vw_yk_yppckc (
   yprkpc,
   ykbmbh,
   rkdh00,
   ghdwmc,
   ypnm00,
   ypbm00,
   ypmc00,
   ypgg00,
   kcdw00,
   k2j000,
   gjdj00,
   lsj000,
   xxlsdj,
   rksl00,
   pckc00,
   kcsx00,
   kcxx00,
   jhrq00,
   scph00,
   ypsxrq,
   cfwzmc,
   lbbh00,
   ylbh00,
   jxbh00,
   pdxh00,
   sccjzw,
   wbsm00,
   pysm00,
   bz0000,
   cjylbh,
   qrrq00,
   scrq00,
   ygjdj0 )
AS
select
  X.YPRKPC,
  K.YKBMBH,
  R.RKDH00,
  (select G.GHDWMC from BM_YPGHDW G where G.GHDWNM=R.GHDWNM),
  D.YPNM00,
  D.YPBM00,
  D.YPMC00,
  D.YPGG00,
  D.KCDW00,
  D.K2J000,
  decode((select trim(VALUE0) from XT_XTCS00 where NAME00='YK_ZRJRCJ'),'Y',
          round(X.GJDJ00/X.ZHL000*D.K2J000,4),
          round(X.FPGJDJ/X.ZHL000*D.K2J000,4)),
  round(X.LSDJ00/X.ZHL000*D.K2J000,4),
  round(X.XXLSDJ/X.ZHL000*D.K2J000,4),
  decode(sign(FPGJDJ),-1,0,round(X.RKSL00*X.ZHL000/D.K2J000,3)),
  --decode(k.KCSL00,0,0,round(X.PCKCSL/D.K2J000,3)) PCKC00,
  decode((select trim(VALUE0) from XT_XTCS00 where NAME00='YK_SFQYLSYP'),'N',round(X.PCKCSL/D.K2J000,3),null,round(X.PCKCSL/D.K2J000,3),
         decode((select trim(VALUE0) from XT_XTCS00 where NAME00='YK_CJJESFDLGL'),'Y',round(X.PCKCSL/D.K2J000,3),
                 decode(k.KCSL00,0,0,round(X.PCKCSL/D.K2J000,3)))) PCKC00,
  K.KCSX00,
  K.KCXX00,
 -- R.QRRQ00,
  R.JHRQ00,
  X.SCPH00,
  X.YPSXRQ,
  X.CFWZMC,
  D.LBBH00,
  D.YLBH00,
  D.JXBH00,
  K.PDXH00,
  X.SCCJZW,
  D.WBSM00,
  D.PYSM00,
  X.BZ0000,
  (select CJYLBH from BM_CJYLBM where LSH000=D.CYLLSH),
  R.QRRQ00,
  X.SCRQ00,
 -- decode((select trim(VALUE0) from XT_XTCS00 where NAME00='YK_ZRJRCJ'),'Y',
  --        round(decode((select trim(VALUE0) from XT_XTCS00 where NAME00='YK_YKTHFS'),'1',X.YGJDJ0,X.GJDJ00)/X.ZHL000*D.K2J000,4),
   --       round(X.FPGJDJ/X.ZHL000*D.K2J000,4))
   nvl(X.YGJDJ0,round(decode((select trim(VALUE0) from XT_XTCS00 where NAME00='YK_ZRJRCJ'),'Y',X.GJDJ00,X.FPGJDJ)/X.ZHL000*D.K2J000,4))
  from YK_YPRKD0 R,YK_YPRKMX X,BM_YD0000 D,YK_YPKCXX K
  where R.RKDH00=X.RKDH00 and X.YPNM00=D.YPNM00 and D.YPNM00=K.YPNM00 and R.YKBMBH=K.YKBMBH
    and X.RKSL00>0      and X.CXBZ00='Z'     and R.RKZT00 not in ('0','4')
    and D.TZRQ00 is null
/

-- Grants for View
GRANT SELECT ON vw_yk_yppckc TO sd_mhey
/

-- End of DDL Script for View SD_HOSPITAL.VW_YK_YPPCKC