-- Start of DDL Script for View SD_HOSPITAL.VW_YF_MZYFCFFYSL
-- Generated 07-3ÔÂ-2025 17:16:16 from SD_HOSPITAL@his_ydfe_zsk

CREATE OR REPLACE VIEW vw_yf_mzyfcffysl (
   ygbh00,
   cffysl,
   cffyje,
   cfzyts,
   gjfyje )
AS
SELECT
  nvl(FYR000,0),
  SUM(DECODE(YPDLBH,'2',CEIL(ZYTS00/TO_NUMBER(
  DECODE((SELECT TRIM(VALUE0) FROM XT_XTCS00 WHERE NAME00='YF_GZTJZYSFYTS'),'Y',B.BBCS60,ZYTS00)
  )),ZYTS00)),
  SUM(CFZJE0),
  SUM(NVL(ZYTS00,0)),
  SUM((SELECT SUM(ROUND(GJDJ00*YPZSL0,2)) FROM YF_MZCFMX WHERE CFLSH0=Z.CFLSH0 AND SFZBY0='N')) GJFYJE
  --sum((select sum(ROUND(nvl(GJDJ00,(select GJDJ00 from YF_PCCKMX where LYDJH0=to_char(c.CFID00)  and DJLX00='0' and rownum=1))*YPZSL0,2)) from YF_MZCFMX c where CFLSH0=Z.CFLSH0 and SFZBY0='N')) GJFYJE
  FROM YF_MZCF00 Z,XT_GYBBCS B
    WHERE  B.BBDH00='YF_MZYFGZLTJ'
    AND Z.FYRQ00>=B.KSRQ00 AND Z.FYRQ00<=B.JSRQ00
    AND Z.FYRQ00||Z.FYSJ00>=B.KSRQ00||B.KSSJ00
    AND Z.FYRQ00||Z.FYSJ00<=B.JSRQ00||B.JSSJ00
    AND Z.YPDLBH>=Trim(B.BBCS20) AND Z.YPDLBH<=Trim(B.BBCS30)
    AND Z.YFBMBH+0=TO_NUMBER(B.BBCS10)
    AND Z.CFZT00<>'3' --and Z.JZRQ00 is not null
    GROUP BY FYR000
/

-- Grants for View
GRANT SELECT ON vw_yf_mzyfcffysl TO admin01
/
GRANT UPDATE ON vw_yf_mzyfcffysl TO admin01
/

-- End of DDL Script for View SD_HOSPITAL.VW_YF_MZYFCFFYSL