CREATE OR REPLACE VIEW VW_YF_ZYYFCFFYSL
(ygbh00, cffysl, cffyje, gjfyje)
AS
select FYR000, sum(CFFYSL), sum(CFFYJE),sum(GJFYJE) from (SELECT 
  FYR000,
  SUM(DECODE(YPDLBH,'2',CEIL(ZYTS00/TO_NUMBER(
  ZYTS00)
  ),ZYTS00)) CFFYSL,
  SUM(CFZJE0) CFFYJE,
  SUM((SELECT SUM(ROUND(GJDJ00*YPZSL0,2)) FROM YF_ZYCFMX WHERE CFLSH0=Z.CFLSH0 AND SFZBY0='N')) GJFYJE     
  FROM YF_ZYCF00 Z,XT_GYBBCS B
    WHERE  B.BBDH00='YF_ZYYFGZLTJ' 
    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'
    GROUP BY FYR000
union all
SELECT 
  FYR000,
/*  SUM(DECODE(YPDLBH,'2',CEIL(ZYTS00/TO_NUMBER(
  DECODE((SELECT TRIM(VALUE0) FROM XT_XTCS00 WHERE NAME00='YF_GZTJZYSFYTS'),'Y',B.BBCS60,ZYTS00)
  )),ZYTS00)) cffysl,*/
  count( distinct  z.ckdh00) cffysl,--2021.12.11 lingxianyun  
  SUM(round(Z.YPZSL0*Z.LSDJ00,2)) CFFYJE,
   SUM(round(Z.YPZSL0*Z.GJDJ00,2)) GJFYJE  
  FROM yf_yzypsq Z,XT_GYBBCS B
    WHERE  B.BBDH00='YF_ZYYFGZLTJ' 
    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.QLZT00<>'3'
     and cxbz00<>'-'
     AND exists (select 1 from YF_YPCKD0 where ckdh00=Z.ckdh00 ) 
    GROUP BY FYR000 ) GROUP BY FYR000;