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