CF

最新推荐文章于 2024-02-23 16:17:43 发布
最新推荐文章于 2024-02-23 16:17:43 发布
阅读量393 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/RJ_Dream/article/details/73744493
版权

CF现金流量抽取，加载目标是DATI_SADI_LORDI表。


SAP:

SELECT SCENARIO,{IN-YEARMONTH} YEARMONTH,ZMONTH,ENTITY,ZZCASH,LCURR,WAERS,DMBTR,WRBTR,CATEG,MANDT FROM
(SELECT GJAHR,GJAHR||'ACT' SCENARIO, SUBSTR({IN-YEARMONTH},5,2) ZMONTH
,BUKRS ENTITY,ZZCASH,'CNY' LCURR,BKPF_WAERS WAERS
,SUM(CASE SHKZG WHEN 'H' THEN -1*DMBTR ELSE DMBTR END) DMBTR
,SUM(CASE SHKZG WHEN 'H' THEN -1*WRBTR ELSE WRBTR END) WRBTR
,'$AMOUNT' CATEG,'600'  MANDT
FROM
(SELECT * FROM ODS.ODSS600_BSEG_CF@FMSLK 
WHERE GJAHR = SUBSTR({IN-YEARMONTH},1,4) 
AND BUKRS IN ({IN-ENTITY})
AND HKONT LIKE '10%'
and BKPF_MONAT <=SUBSTR({IN-YEARMONTH},5,2)
AND SUBSTR({IN-YEARMONTH},5,2) <= '12'
)
GROUP BY GJAHR,BUKRS,ZZCASH,BKPF_WAERS,MANDT
)
WHERE DMBTR <> 0 OR WRBTR <> 0


UNION ALL

SELECT SCENARIO,{IN-YEARMONTH} YEARMONTH,ZMONTH,ENTITY,ZZCASH,LCURR,WAERS,DMBTR,WRBTR,CATEG,MANDT FROM
(SELECT GJAHR,GJAHR||'ACT' SCENARIO, '12'  ZMONTH
,BUKRS ENTITY,ZZCASH,'CNY' LCURR,BKPF_WAERS WAERS
,SUM(CASE SHKZG WHEN 'H' THEN -1*DMBTR ELSE DMBTR END) DMBTR
,SUM(CASE SHKZG WHEN 'H' THEN -1*WRBTR ELSE WRBTR END) WRBTR
,'1ADI' CATEG,'600' MANDT
FROM
(SELECT * FROM ODS.ODSS600_BSEG_CF@FMSLK 
WHERE GJAHR =  SUBSTR({IN-YEARMONTH},1,4)
AND BUKRS IN ({IN-ENTITY})
AND HKONT LIKE '10%'
and BKPF_MONAT > '12' AND BKPF_MONAT <= '16' 
AND SUBSTR({IN-YEARMONTH},5,2) = '16'
)
GROUP BY GJAHR,BUKRS,ZZCASH,BKPF_WAERS,MANDT 
)
WHERE DMBTR <> 0 OR WRBTR <> 0

K3:

with c1 as (
  SELECT *  FROM (
    SELECT FYEAR||fperiod YEARMONTH, fyear,fperiod,FYEAR||'ACT' SCENARIO,m.ELEDIM_OUTPUT1 companycode,
    companyname,itemcode,itemname,fcurrencyid,
           SUM(FAMOUNT)  FAMOUNT,
           SUM(famountfor) famountfor,client
      FROM(
        SELECT 
          fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
          SUM(FAMOUNT) FAMOUNT,SUM(famountfor) famountfor, client
        FROM(
          select 
           fyear, '01' fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
           FAMOUNT,famountfor,'K3' client
           from ODS.ODSTTKD_V_MR_CASHFLOW@FMSLK 
           where fperiod  <= 1
        )
      GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client
    ) S
    LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_ENTITY_K3' AND M.COD_MAPPATURA = 'HI_DATA_CF' AND M.ELEDIM_INPUT2 = S.companycode
    GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client,m.ELEDIM_OUTPUT1 ORDER BY COMPANYCODE ,ITEMNAME
  )
),
c2 as (
  SELECT *  FROM (
    SELECT FYEAR||fperiod YEARMONTH, fyear,fperiod,FYEAR||'ACT' SCENARIO,m.ELEDIM_OUTPUT1 companycode,
    companyname,itemcode,itemname,fcurrencyid,
           SUM(FAMOUNT)  FAMOUNT,
           SUM(famountfor) famountfor,client
      FROM(
        SELECT 
          fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
          SUM(FAMOUNT) FAMOUNT,SUM(famountfor) famountfor, client
        FROM(
          select 
           fyear, '02' fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
           FAMOUNT,famountfor,'K3' client
           from ODS.ODSTTKD_V_MR_CASHFLOW@FMSLK 
           where fperiod  <= 2
        )
      GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client
    ) S
    LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_ENTITY_K3' AND M.COD_MAPPATURA = 'HI_DATA_CF' AND M.ELEDIM_INPUT2 = S.companycode
    
     GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client,m.ELEDIM_OUTPUT1 ORDER BY COMPANYCODE ,ITEMNAME
  )
),
c3 as (
  SELECT *  FROM (
    SELECT FYEAR||fperiod YEARMONTH, fyear,fperiod,FYEAR||'ACT' SCENARIO,m.ELEDIM_OUTPUT1 companycode,
    companyname,itemcode,itemname,fcurrencyid,
           SUM(FAMOUNT)  FAMOUNT,
           SUM(famountfor) famountfor,client
      FROM(
        SELECT 
          fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
          SUM(FAMOUNT) FAMOUNT,SUM(famountfor) famountfor, client
        FROM(
          select 
           fyear, '03' fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
           FAMOUNT,famountfor,'K3' client
           from ODS.ODSTTKD_V_MR_CASHFLOW@FMSLK 
           where fperiod  <= 3
        )
      GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client
    ) S
    LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_ENTITY_K3' AND M.COD_MAPPATURA = 'HI_DATA_CF' AND M.ELEDIM_INPUT2 = S.companycode
    
     GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client,m.ELEDIM_OUTPUT1 ORDER BY COMPANYCODE ,ITEMNAME
  )
),
c4 as (
  SELECT *  FROM (
    SELECT FYEAR||fperiod YEARMONTH, fyear,fperiod,FYEAR||'ACT' SCENARIO,m.ELEDIM_OUTPUT1 companycode,
    companyname,itemcode,itemname,fcurrencyid,
           SUM(FAMOUNT)  FAMOUNT,
           SUM(famountfor) famountfor,client
      FROM(
        SELECT 
          fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
          SUM(FAMOUNT) FAMOUNT,SUM(famountfor) famountfor, client
        FROM(
          select 
           fyear, '04' fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
           FAMOUNT,famountfor,'K3' client
           from ODS.ODSTTKD_V_MR_CASHFLOW@FMSLK 
           where fperiod  <=4
        )
      GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client
    ) S
    LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_ENTITY_K3' AND M.COD_MAPPATURA = 'HI_DATA_CF' AND M.ELEDIM_INPUT2 = S.companycode
    
     GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client,m.ELEDIM_OUTPUT1 ORDER BY COMPANYCODE ,ITEMNAME
  )
),
c5 as (
  SELECT *  FROM (
    SELECT FYEAR||fperiod YEARMONTH, fyear,fperiod,FYEAR||'ACT' SCENARIO,m.ELEDIM_OUTPUT1 companycode,
    companyname,itemcode,itemname,fcurrencyid,
           SUM(FAMOUNT)  FAMOUNT,
           SUM(famountfor) famountfor,client
      FROM(
        SELECT 
          fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
          SUM(FAMOUNT) FAMOUNT,SUM(famountfor) famountfor, client
        FROM(
          select 
           fyear, '05' fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
           FAMOUNT,famountfor,'K3' client
           from ODS.ODSTTKD_V_MR_CASHFLOW@FMSLK 
           where fperiod  <= 5
        )
      GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client
    ) S
    LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_ENTITY_K3' AND M.COD_MAPPATURA = 'HI_DATA_CF' AND M.ELEDIM_INPUT2 = S.companycode
    
     GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client,m.ELEDIM_OUTPUT1 ORDER BY COMPANYCODE ,ITEMNAME
  )
),
c6 as (
  SELECT *  FROM (
    SELECT FYEAR||fperiod YEARMONTH, fyear,fperiod,FYEAR||'ACT' SCENARIO,m.ELEDIM_OUTPUT1 companycode,
    companyname,itemcode,itemname,fcurrencyid,
           SUM(FAMOUNT)  FAMOUNT,
           SUM(famountfor) famountfor,client
      FROM(
        SELECT 
          fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
          SUM(FAMOUNT) FAMOUNT,SUM(famountfor) famountfor, client
        FROM(
          select 
           fyear, '06' fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
           FAMOUNT,famountfor,'K3' client
           from ODS.ODSTTKD_V_MR_CASHFLOW@FMSLK 
           where fperiod  <= 6
        )
      GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client
    ) S
    LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_ENTITY_K3' AND M.COD_MAPPATURA = 'HI_DATA_CF' AND M.ELEDIM_INPUT2 = S.companycode
    
     GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client,m.ELEDIM_OUTPUT1 ORDER BY COMPANYCODE ,ITEMNAME
  )
),
c7 as (
  SELECT *  FROM (
    SELECT FYEAR||fperiod YEARMONTH, fyear,fperiod,FYEAR||'ACT' SCENARIO,m.ELEDIM_OUTPUT1 companycode,
    companyname,itemcode,itemname,fcurrencyid,
           SUM(FAMOUNT)  FAMOUNT,
           SUM(famountfor) famountfor,client
      FROM(
        SELECT 
          fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
          SUM(FAMOUNT) FAMOUNT,SUM(famountfor) famountfor, client
        FROM(
          select 
           fyear, '07' fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
           FAMOUNT,famountfor,'K3' client
           from ODS.ODSTTKD_V_MR_CASHFLOW@FMSLK 
           where fperiod  <= 7
        )
      GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client
    ) S
    LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_ENTITY_K3' AND M.COD_MAPPATURA = 'HI_DATA_CF' AND M.ELEDIM_INPUT2 = S.companycode
    
     GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client,m.ELEDIM_OUTPUT1 ORDER BY COMPANYCODE ,ITEMNAME
  )
),
c8 as (
  SELECT *  FROM (
    SELECT FYEAR||fperiod YEARMONTH, fyear,fperiod,FYEAR||'ACT' SCENARIO,m.ELEDIM_OUTPUT1 companycode,
    companyname,itemcode,itemname,fcurrencyid,
           SUM(FAMOUNT)  FAMOUNT,
           SUM(famountfor) famountfor,client
      FROM(
        SELECT 
          fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
          SUM(FAMOUNT) FAMOUNT,SUM(famountfor) famountfor, client
        FROM(
          select 
           fyear, '08' fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
           FAMOUNT,famountfor,'K3' client
           from ODS.ODSTTKD_V_MR_CASHFLOW@FMSLK 
           where fperiod  <= 8
        )
      GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client
    ) S
    LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_ENTITY_K3' AND M.COD_MAPPATURA = 'HI_DATA_CF' AND M.ELEDIM_INPUT2 = S.companycode
    
     GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client,m.ELEDIM_OUTPUT1 ORDER BY COMPANYCODE ,ITEMNAME
  )
),
c9 as (
  SELECT *  FROM (
    SELECT FYEAR||fperiod YEARMONTH, fyear,fperiod,FYEAR||'ACT' SCENARIO,m.ELEDIM_OUTPUT1 companycode,
    companyname,itemcode,itemname,fcurrencyid,
           SUM(FAMOUNT)  FAMOUNT,
           SUM(famountfor) famountfor,client
      FROM(
        SELECT 
          fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
          SUM(FAMOUNT) FAMOUNT,SUM(famountfor) famountfor, client
        FROM(
          select 
           fyear, '09' fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
           FAMOUNT,famountfor,'K3' client
           from ODS.ODSTTKD_V_MR_CASHFLOW@FMSLK 
           where fperiod  <= 9
        )
      GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client
    ) S
    LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_ENTITY_K3' AND M.COD_MAPPATURA = 'HI_DATA_CF' AND M.ELEDIM_INPUT2 = S.companycode
    
     GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client,m.ELEDIM_OUTPUT1 ORDER BY COMPANYCODE ,ITEMNAME
  )
),
c10 as (
  SELECT *  FROM (
    SELECT FYEAR||fperiod YEARMONTH, fyear,fperiod,FYEAR||'ACT' SCENARIO,m.ELEDIM_OUTPUT1 companycode,
    companyname,itemcode,itemname,fcurrencyid,
           SUM(FAMOUNT)  FAMOUNT,
           SUM(famountfor) famountfor,client
      FROM(
        SELECT 
          fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
          SUM(FAMOUNT) FAMOUNT,SUM(famountfor) famountfor, client
        FROM(
          select 
           fyear, '10' fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
           FAMOUNT,famountfor,'K3' client
           from ODS.ODSTTKD_V_MR_CASHFLOW@FMSLK 
           where fperiod  <= 10
        )
      GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client
    ) S
    LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_ENTITY_K3' AND M.COD_MAPPATURA = 'HI_DATA_CF' AND M.ELEDIM_INPUT2 = S.companycode
    
     GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client,m.ELEDIM_OUTPUT1 ORDER BY COMPANYCODE ,ITEMNAME
  )
),
c11 as (
  SELECT *  FROM (
    SELECT FYEAR||fperiod YEARMONTH, fyear,fperiod,FYEAR||'ACT' SCENARIO,m.ELEDIM_OUTPUT1 companycode,
    companyname,itemcode,itemname,fcurrencyid,
           SUM(FAMOUNT)  FAMOUNT,
           SUM(famountfor) famountfor,client
      FROM(
        SELECT 
          fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
          SUM(FAMOUNT) FAMOUNT,SUM(famountfor) famountfor, client
        FROM(
          select 
           fyear, '11' fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
           FAMOUNT,famountfor,'K3' client
           from ODS.ODSTTKD_V_MR_CASHFLOW@FMSLK 
           where fperiod <= 11
        )
      GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client
    ) S
    LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_ENTITY_K3' AND M.COD_MAPPATURA = 'HI_DATA_CF' AND M.ELEDIM_INPUT2 = S.companycode
    
     GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client,m.ELEDIM_OUTPUT1 ORDER BY COMPANYCODE ,ITEMNAME
  )
),
c12 as (
  SELECT *  FROM (
    SELECT FYEAR||fperiod YEARMONTH, fyear,fperiod,FYEAR||'ACT' SCENARIO,m.ELEDIM_OUTPUT1 companycode,
    companyname,itemcode,itemname,fcurrencyid,
           SUM(FAMOUNT)  FAMOUNT,
           SUM(famountfor) famountfor,client
      FROM(
        SELECT 
          fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
          SUM(FAMOUNT) FAMOUNT,SUM(famountfor) famountfor, client
        FROM(
          select 
           fyear, '12' fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,
           FAMOUNT,famountfor,'K3' client
           from ODS.ODSTTKD_V_MR_CASHFLOW@FMSLK 
           where fperiod  <= 12
        )
      GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client
    ) S
    LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_ENTITY_K3' AND M.COD_MAPPATURA = 'HI_DATA_CF' AND M.ELEDIM_INPUT2 = S.companycode
    
     GROUP BY fyear,fperiod,companycode,companyname,itemcode,itemname,fcurrencyid,client,m.ELEDIM_OUTPUT1 ORDER BY COMPANYCODE ,ITEMNAME
  )
)

SELECT * FROM (
select YEARMONTH, fyear,fperiod,SCENARIO,companycode,companyname,itemcode,itemname,fcurrencyid,client,
           DECODE(SUBSTR(ITEMCODE,6,2),02,FAMOUNT * -1,FAMOUNT) FAMOUNT,
           DECODE(SUBSTR(ITEMCODE,6,2),02,famountfor * -1,famountfor) famountfor
 from (
  select * from c1 
  union all
  select * from c2 
  union all
  select * from c3 
  union all
  select * from c4 
  union all
  select * from c5 
  union all
  select * from c6 
  union all
  select * from c7 
  union all
  select * from c8 
  union all
  select * from c9 
  union all
  select * from c10 
  union all
  select * from c11 
  union all
  select * from c12 
)
)
WHERE   YEARMONTH IN ({IN-YEARMONTH}) AND companycode IN ({IN-ENTITY})

NC

with c1 as(
  select  YEARMONTH, SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv, balance,CLIENT
  from(
  select YEARMONTH,YEARV||'ACT' SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv,
     CLIENT,SUM(BALANCE) balance
    from
     (
       select unitcode,unitname,subjcode,subjname,valuename,subjlev, YEAR YEARV,'01' periodv,YEAR||'01' YEARMONTH,
         SUM(localcreditamount*-1 + localdebitamount) balance,'YONYOU'CLIENT
      from ODS.Odsreyy_v_Cashflow@FMSLK v 
     WHERE  valuename not like '期初'and valuename not like '%存提现及转账%'
        and valuename not like '%存货的减少%'and valuename not like '%经营性应付项目的增加%'
        and period <= 01 
      group by unitcode,unitname,subjcode,subjname,subjlev,valuename,period,year
      order by unitcode,unitname,subjcode,year,period
     ) 
     group by YEARMONTH,unitcode,unitname,subjcode,subjname,subjlev,valuename,periodv,yearv,CLIENT
   )     
 ),
 c2 as(
 select  YEARMONTH, SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv, balance,CLIENT
  from(
select YEARMONTH,YEARV||'ACT' SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv,
        'YONYOU'CLIENT,SUM(BALANCE) balance
 
    from
     (
       select unitcode,unitname,subjcode,subjname,valuename,subjlev, YEAR YEARV,'02' periodv,YEAR||'02' YEARMONTH,
           SUM(localcreditamount*-1 + localdebitamount) balance,'YONYOU'CLIENT
      from ODS.Odsreyy_v_Cashflow@FMSLK v 
 WHERE  valuename not like '期初'and valuename not like '%存提现及转账%'
        and valuename not like '%存货的减少%'and valuename not like '%经营性应付项目的增加%'
          and period <= 02 
      group by unitcode,unitname,subjcode,subjname,subjlev,valuename,period,year
      order by unitcode,unitname,subjcode,year,period
     ) 
       group by YEARMONTH,unitcode,unitname,subjcode,subjname,subjlev,valuename,periodv,yearv
   )
 ),
 c3 as(
 select  YEARMONTH, SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv, balance,CLIENT
  from(
select YEARMONTH,YEARV||'ACT' SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv,
       'YONYOU'CLIENT,SUM(BALANCE) balance
  from(
   
       select unitcode,unitname,subjcode,subjname,valuename,subjlev, YEAR YEARV,'03' periodv,YEAR||'03' YEARMONTH,
           SUM(localcreditamount*-1 + localdebitamount) balance,'YONYOU'CLIENT
      from ODS.Odsreyy_v_Cashflow@FMSLK v 
  WHERE  valuename not like '期初'and valuename not like '%存提现及转账%'
        and valuename not like '%存货的减少%'and valuename not like '%经营性应付项目的增加%'
          and period <= 03 
      group by unitcode,unitname,subjcode,subjname,subjlev,valuename,period,year
      order by unitcode,unitname,subjcode,year,period
     ) 
       group by YEARMONTH,unitcode,unitname,subjcode,subjname,subjlev,valuename,periodv,yearv
   )
 ),
 c4 as(
 select  YEARMONTH, SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv, balance,CLIENT
  from(
select YEARMONTH,YEARV||'ACT' SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv,
        'YONYOU'CLIENT,SUM(BALANCE) balance

    from
     (
       select unitcode,unitname,subjcode,subjname,valuename,subjlev, YEAR YEARV,'04' periodv,YEAR||'04' YEARMONTH,
           SUM(localcreditamount*-1 + localdebitamount) balance,'YONYOU'CLIENT
      from ODS.Odsreyy_v_Cashflow@FMSLK v 
     WHERE  valuename not like '期初'and valuename not like '%存提现及转账%'
        and valuename not like '%存货的减少%'and valuename not like '%经营性应付项目的增加%'
          and period <= 04 
      group by unitcode,unitname,subjcode,subjname,subjlev,valuename,period,year
      order by unitcode,unitname,subjcode,year,period
     ) 
       group by YEARMONTH,unitcode,unitname,subjcode,subjname,subjlev,valuename,periodv,yearv
   )
 ),
 c5 as(
 select  YEARMONTH, SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv, balance,CLIENT
  from(
select YEARMONTH,YEARV||'ACT' SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv,
        'YONYOU'CLIENT,SUM(BALANCE) balance

    from
     (
       select unitcode,unitname,subjcode,subjname,valuename,subjlev, YEAR YEARV,'05' periodv,YEAR||'05' YEARMONTH,
           SUM(localcreditamount*-1 + localdebitamount) balance,'YONYOU'CLIENT
      from ODS.Odsreyy_v_Cashflow@FMSLK v 
     WHERE  valuename not like '期初'and valuename not like '%存提现及转账%'
        and valuename not like '%存货的减少%'and valuename not like '%经营性应付项目的增加%'
          and period <= 05 
      group by unitcode,unitname,subjcode,subjname,subjlev,valuename,period,year
      order by unitcode,unitname,subjcode,year,period
     ) 
       group by YEARMONTH,unitcode,unitname,subjcode,subjname,subjlev,valuename,periodv,yearv
   )
 ),
  c6 as(
  select  YEARMONTH, SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv, balance,CLIENT
  from(
select YEARMONTH,YEARV||'ACT' SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv,
       'YONYOU'CLIENT,SUM(BALANCE) balance

    from
     (
       select unitcode,unitname,subjcode,subjname,valuename,subjlev, YEAR YEARV,'06' periodv,YEAR||'06' YEARMONTH,
           SUM(localcreditamount*-1 + localdebitamount) balance,'YONYOU'CLIENT
      from ODS.Odsreyy_v_Cashflow@FMSLK v 
  WHERE  valuename not like '期初'and valuename not like '%存提现及转账%'
        and valuename not like '%存货的减少%'and valuename not like '%经营性应付项目的增加%'
          and period <= 06 
      group by unitcode,unitname,subjcode,subjname,subjlev,valuename,period,year
      order by unitcode,unitname,subjcode,year,period
     ) 
       group by YEARMONTH,unitcode,unitname,subjcode,subjname,subjlev,valuename,periodv,yearv
   )
 ),
  c7 as(
  select  YEARMONTH, SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv, balance,CLIENT
  from(
select YEARMONTH,YEARV||'ACT' SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv,
        'YONYOU'CLIENT,SUM(BALANCE) balance
  
    from
     (
       select unitcode,unitname,subjcode,subjname,valuename,subjlev, YEAR YEARV,'07' periodv,YEAR||'07' YEARMONTH,
           SUM(localcreditamount*-1 + localdebitamount) balance,'YONYOU'CLIENT
      from ODS.Odsreyy_v_Cashflow@FMSLK v 
     WHERE  valuename not like '期初'and valuename not like '%存提现及转账%'
        and valuename not like '%存货的减少%'and valuename not like '%经营性应付项目的增加%'
          and period <= 07 
      group by unitcode,unitname,subjcode,subjname,subjlev,valuename,period,year
      order by unitcode,unitname,subjcode,year,period
     ) 
       group by YEARMONTH,unitcode,unitname,subjcode,subjname,subjlev,valuename,periodv,yearv
   )
 ),
  c8 as(
  select  YEARMONTH, SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv, balance,CLIENT
  from(
select YEARMONTH,YEARV||'ACT' SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv,
      'YONYOU'CLIENT,SUM(BALANCE) balance

    from
     (
       select unitcode,unitname,subjcode,subjname,valuename,subjlev, YEAR YEARV,'08' periodv,YEAR||'08' YEARMONTH,
           SUM(localcreditamount*-1 + localdebitamount) balance,'YONYOU'CLIENT
      from ODS.Odsreyy_v_Cashflow@FMSLK v 
   WHERE  valuename not like '期初'and valuename not like '%存提现及转账%'
        and valuename not like '%存货的减少%'and valuename not like '%经营性应付项目的增加%'
          and period <= 08
      group by unitcode,unitname,subjcode,subjname,subjlev,valuename,period,year
      order by unitcode,unitname,subjcode,year,period
     ) 
       group by YEARMONTH,unitcode,unitname,subjcode,subjname,subjlev,valuename,periodv,yearv
   )
 ),
  c9 as(
  select  YEARMONTH, SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv, balance,CLIENT
  from(
select YEARMONTH,YEARV||'ACT' SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv,
       'YONYOU'CLIENT,SUM(BALANCE) balance
 
    from
     (
       select unitcode,unitname,subjcode,subjname,valuename,subjlev, YEAR YEARV,'09' periodv,YEAR||'09' YEARMONTH,
           SUM(localcreditamount*-1 + localdebitamount) balance,'YONYOU'CLIENT
      from ODS.Odsreyy_v_Cashflow@FMSLK v 
   WHERE  valuename not like '期初'and valuename not like '%存提现及转账%'
        and valuename not like '%存货的减少%'and valuename not like '%经营性应付项目的增加%'
          and period <= 09 
      group by unitcode,unitname,subjcode,subjname,subjlev,valuename,period,year
      order by unitcode,unitname,subjcode,year,period
     ) 
       group by YEARMONTH,unitcode,unitname,subjcode,subjname,subjlev,valuename,periodv,yearv
   )
 ),
  c10 as(
  select  YEARMONTH, SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv, balance,CLIENT
  from(
select YEARMONTH,YEARV||'ACT' SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv,
        'YONYOU'CLIENT,SUM(BALANCE) balance

    from
     (
       select unitcode,unitname,subjcode,subjname,valuename,subjlev, YEAR YEARV,'10' periodv,YEAR||'10' YEARMONTH,
           SUM(localcreditamount*-1 + localdebitamount) balance,'YONYOU'CLIENT
      from ODS.Odsreyy_v_Cashflow@FMSLK v 
 WHERE  valuename not like '期初'and valuename not like '%存提现及转账%'
        and valuename not like '%存货的减少%'and valuename not like '%经营性应付项目的增加%'
          and period <= 10 
      group by unitcode,unitname,subjcode,subjname,subjlev,valuename,period,year
      order by unitcode,unitname,subjcode,year,period
     ) 
       group by YEARMONTH,unitcode,unitname,subjcode,subjname,subjlev,valuename,periodv,yearv
   )
 ),
  c11 as(
  select  YEARMONTH, SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv, balance,CLIENT
  from(
select YEARMONTH,YEARV||'ACT' SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv,
      'YONYOU'CLIENT,SUM(BALANCE) balance

    from
     (
       select unitcode,unitname,subjcode,subjname,valuename,subjlev, YEAR YEARV,'11' periodv,YEAR||'11' YEARMONTH,
           SUM(localcreditamount*-1 + localdebitamount) balance,'YONYOU'CLIENT
      from ODS.Odsreyy_v_Cashflow@FMSLK v 
  WHERE  valuename not like '期初'and valuename not like '%存提现及转账%'
        and valuename not like '%存货的减少%'and valuename not like '%经营性应付项目的增加%'
          and period <= 11 
      group by unitcode,unitname,subjcode,subjname,subjlev,valuename,period,year
      order by unitcode,unitname,subjcode,year,period
     ) 
       group by YEARMONTH,unitcode,unitname,subjcode,subjname,subjlev,valuename,periodv,yearv
   )
 ),
  c12 as(
  select  YEARMONTH, SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv, balance,CLIENT
  from(
select YEARMONTH,YEARV||'ACT' SCENARIO,unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv,
       'YONYOU'CLIENT,SUM(BALANCE) balance

    from
     (
       select unitcode,unitname,subjcode,subjname,valuename,subjlev, YEAR YEARV,'12' periodv,YEAR||'12' YEARMONTH,
           SUM(localcreditamount*-1 + localdebitamount) balance,'YONYOU'CLIENT
      from ODS.Odsreyy_v_Cashflow@FMSLK v 
    WHERE  valuename not like '期初'and valuename not like '%存提现及转账%'
        and valuename not like '%存货的减少%'and valuename not like '%经营性应付项目的增加%'
          and period <= 12 
      group by unitcode,unitname,subjcode,subjname,subjlev,valuename,period,year
      order by unitcode,unitname,subjcode,year,period
     ) 
       group by YEARMONTH,unitcode,unitname,subjcode,subjname,subjlev,valuename,periodv,yearv
   )
 )

SELECT * FROM (
 select  YEARMONTH, SCENARIO, M.ELEDIM_OUTPUT1 unitcode,unitname,subjcode,subjname,valuename,subjlev,YEARV,periodv, balance,CLIENT  from (
 select * from c1 
 union all 
 select * from c2
 union all 
 select * from c3
 union all 
 select * from c4
 union all 
 select * from c5
 union all  
 select * from c6
 union all  
 select * from c7
 union all 
 select * from c8
 union all 
 select * from c9
 union all 
 select * from c10
 union all 
 select * from c11
 union all 
 select * from c12
)  S
  LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_BUKRS_YONYOU' AND M.COD_MAPPATURA = 'HI_DATA_GL' AND M.ELEDIM_INPUT2 = S.unitcode  
)
 WHERE YEARMONTH IN ({IN-YEARMONTH}) AND UNITCODE IN ({IN-ENTITY})
 order by VALUENAME,UNITCODE,SUBJCODE,YEARV,PERIODV



RJ_Dream
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
cf，，，，
jgvshb的博客
11-22 851
【代码】cf，，，，
CF 2B
我爱编程
07-02 83
#include <iostream> #include <cstdio> #include <string> using namespace std; int dp[1010][1010][2]; int mp[1010][1010]; int main() { int n, x; scanf("%d", &n); int pos = -1; for(int i = 1; i <= n; i ++) {
(python)cf火线瞄准红名自动开枪
calmdownn的博客
08-03 2万+
这个应该cf系列第四篇了，目前已经写了ai瞄准，罗技鼠标宏，这篇功能相比前俩个更简单一些,因为代码不多所以就不写类与功能函数了，直接直捣黄龙，我还写了一些辅助脚本，可以用来获取鼠标当前坐标和颜色的，改新的分辨率可以用得上
cf
jgvshb的博客
02-23 359
要想得到题目中想要的条件，要将各数组里面的最大和最小结合起来，才能得到相差的值最大。
关于codeforces比赛规则介绍（转载）
热门推荐
大神养成中.....
02-19 3万+
Codeforces 简称: cf(所以谈论cf的时候经常被误会成TX的那款游戏). 网址: codeforces.com 　　这是一个俄国的算法竞赛网站,由来自萨拉托夫州立大学、由Mike Mirzayanov领导的一个团队创立和维护,是一个举办比赛、做题和交流的平台.举办比赛和做题就不说了,“交流”指的是自带blog功能,可以求助/发布题解之类.官方语言是俄语和英语,因此可能有些偏僻的题
cf-tool.jar
08-11
Java是一门面向对象编程语言，不仅吸收了C++语言的各种优点，还摒弃了C++里难以理解的多继承、指针等概念，因此Java语言具有功能强大和简单易用两个特征。Java语言作为静态面向对象编程语言的代表，极好地实现了面向对象理论，允许程序员以优雅的思维方式进行复杂的编程
第二次cf实战总结
m0_63946635的博客
04-03 75
这次codeforces实战，先读了一遍题，开始有有点迷，多读了几遍，对题目有了大致的了解。题目意思很简单， vasya去商店买东西，他并不知道商店的商品的价格s，他不能在没有零钱的情况下支付的最低正整数金额，或者仅使用他的硬币支付。所以用他仅有的钱一遍一遍的测。即当a=0时，输出1；当a不为1时，输出（a+2*b）+1。就是有个地方卡了半天，忘了while（T--)。 一直error，没办法只能一遍一的测。最后用的for（），break；写完的。 第二题吃糖果，开始想的是既然不...
CF鼠标宏文件.xml
08-10
多功能的一个文件
easyImageWIN CF卡备份软件
09-08
《easyImageWIN CF卡备份软件全面解析》 在数字化时代，摄影师和摄像师们依赖于存储卡，尤其是CF（CompactFlash）卡，来保存珍贵的影像资料。然而，数据安全始终是不可忽视的问题，一旦卡片损坏或数据丢失，可能会...
CF源码
11-15
CF源码指的是穿越火线（CrossFire，简称CF）游戏的1.8.0版本的源代码。穿越火线是一款由韩国Smile Gate公司开发、腾讯在中国大陆代理运营的第一人称射击游戏。源码是软件开发的核心部分，包含了程序的设计逻辑、算法...
CF卡制作DOS系统.rar_CF卡_cf卡dos系统_dos系统_dos系统 cf卡_simplypit
09-24
标题中的“CF卡制作DOS系统.rar”表明这是一个关于如何将DOS系统安装到CF卡上的教程文件。CF卡，全称CompactFlash卡，是一种早期的数字存储设备，常用于老式硬件，如数码相机和某些便携式计算机。DOS（Disk ...
CF算法简介及实现
日日新
07-26 9078
cf算法全称“Collaborative Filtering”，即协同过滤算法。协同过滤算法是非常强大与成熟（古老）的一套算法。它广泛应用于电子商务系统等领域。  协同过滤算法的出现标志着推荐系统的产生。 协同过滤简单来说是利用某兴趣相投、拥有共同经验之群体的喜好来推荐用户感兴趣的信息，个人通过合作的机制给予信息相当程度的回应（如评分）并记录下来以达到过滤的目的进而帮助别人筛选信息。   关...
推荐算法之CB，CF算法
Jameslvt的博客
07-21 9385
初学推荐算法，以下是我的一些见解，如有不对请留言，后续还会更新，这个CSDN太坑了，写了快一个下午的文章，发表了，结果没保存，神坑。。。。。 首先我们来明确一下，推荐系统主要是干什么用的：毋庸置疑，在这么一个信息爆炸的时代，许多信息过载或是过剩，那么我们不可能把全部给看一遍，这里我们就用到推荐系统，那么推荐系统是给用户推荐自己想看的一些视频新闻或是购物的物品等等，或许可能理解的有点片面，但是基本...
人力资源+大数据+薪酬报告+涨薪调薪，在学习、工作生活中，越来越多的事务都会使用到报告，通常情况下，报告的内容含量大、篇幅较长
08-12
人力资源+大数据+薪酬报告+涨薪调薪，在学习、工作生活中，越来越多的事务都会使用到报告，通常情况下，报告的内容含量大、篇幅较长。那么什么样的薪酬报告才是有效的呢？以下是小编精心整理的调薪申请报告，欢迎大家分享。相信老板看到这样的报告，一定会考虑涨薪的哦。
SEQ投诉处理与VONR信令及特性介绍
08-12
SEQ平台5G分析模块介绍 投诉分析处理 VoNR信令及特性介绍
毕业设计php企业进销存管理系统-qkrp源码含文档工具包
最新发布
08-12
毕业设计php企业进销存管理系统-qkrp源码含文档工具包 php，数据库mysql，jdk1.8，开发工具用ecplise、myecplise、sts、idea都可以 类型管理 商品管理 库存查看 库存查看 入库查看 出库查看 用户管理 包含：源码、数据库脚本、论文、环境工具包、相同框架项目的安装教程（在说明文档中）
cf-predictor
07-27
对于问题"cf-predictor"，根据提供的引用内容，我无法找到与"cf-predictor"相关的信息。请提供更多的上下文或详细的问题描述，以便我能够更好地回答您的问题。 #### 引用[.reference_title] - *1* [cf]...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值