SQL CTE - Common Table Expression
SELECT usando WITH (SQL CTE - Common Table Expression) :
METHOD select_data.
get_dates( IMPORTING e_first_day = DATA(first_day) e_last_day = DATA(last_day) ).
get_period( IMPORTING fiscal_year = DATA(year_begin)
fiscal_period = DATA(period_begin) ).
get_period( IMPORTING fiscal_year = DATA(year_end) "#EC NEEDED
fiscal_period = DATA(period_end) ). "#EC NEEDED
period_begin = r_poper[ 1 ]-low.
period_end = COND #( WHEN r_poper[ 1 ]-high IS NOT INITIAL THEN r_poper[ 1 ]-high ELSE period_begin ).
DATA(period_prev) = CONV poper( period_begin - 1 ).
DATA(year_prev) = year_begin.
DATA(versn) = `000`.
IF period_prev IS INITIAL.
period_prev = `012`.
year_prev = year_begin - 1.
ENDIF.
DATA(period_start) = CONV poper( `01` ).
DATA(timer) = cl_abap_runtime=>create_hr_timer( ).
DATA(t1) = timer->get_runtime( ).
SELECT valsign AS sign,
valoption AS option,
valfrom AS low,
valto AS high
FROM setleaf AS s
WHERE setname = 'RA_DESP_INCLUDED'
INTO TABLE @DATA(rl_racct).
WITH
"Faturamento realizado
+fatur AS (
SELECT ps_psp_pnr, rtcur, SUM( tsl ) AS fatur_brt
FROM acdoca
WHERE rldnr = '0L'
AND blart = 'RV'
GROUP BY ps_psp_pnr, rtcur ),
"Desp Incluídas = Soma do faturamento realizado no período
"nas contas específicas RA_DESP_INCLUDED
+desp_incl AS (
SELECT ps_psp_pnr, rtcur, SUM( tsl ) AS desp_incl
FROM acdoca
WHERE rldnr = '0L'
AND poper BETWEEN @period_begin AND @period_end
AND racct IN @rl_racct
GROUP BY ps_psp_pnr, rtcur ),
* "Despesas Acumuldas = Soma do faturamento total
* "nas contas específicas RA_DESP_INCLUDED
* +desp_acum AS (
* SELECT ps_psp_pnr, rtcur, SUM( tsl ) AS desp_acum
* FROM acdoca
* WHERE rldnr = '0L'
* AND racct IN @rl_racct
* GROUP BY ps_psp_pnr, rtcur ),
"Honorário bruto = Soma de custo total do período para conta específica
+honor AS (
SELECT objnr, twaer,
SUM( wtg001 ) AS acca_brt,
CAST( CASE WHEN '01' BETWEEN @period_begin AND @period_end THEN SUM( wtg001 ) ELSE 0 END +
CASE WHEN '02' BETWEEN @period_begin AND @period_end THEN SUM( wtg002 ) ELSE 0 END +
CASE WHEN '03' BETWEEN @period_begin AND @period_end THEN SUM( wtg003 ) ELSE 0 END +
CASE WHEN '04' BETWEEN @period_begin AND @period_end THEN SUM( wtg004 ) ELSE 0 END +
CASE WHEN '05' BETWEEN @period_begin AND @period_end THEN SUM( wtg005 ) ELSE 0 END +
CASE WHEN '06' BETWEEN @period_begin AND @period_end THEN SUM( wtg006 ) ELSE 0 END +
CASE WHEN '07' BETWEEN @period_begin AND @period_end THEN SUM( wtg007 ) ELSE 0 END +
CASE WHEN '08' BETWEEN @period_begin AND @period_end THEN SUM( wtg008 ) ELSE 0 END +
CASE WHEN '09' BETWEEN @period_begin AND @period_end THEN SUM( wtg009 ) ELSE 0 END +
CASE WHEN '10' BETWEEN @period_begin AND @period_end THEN SUM( wtg010 ) ELSE 0 END +
CASE WHEN '11' BETWEEN @period_begin AND @period_end THEN SUM( wtg011 ) ELSE 0 END +
CASE WHEN '12' BETWEEN @period_begin AND @period_end THEN SUM( wtg012 ) ELSE 0 END
AS DEC( 23, 2 ) ) AS honor_brt
FROM v_cosp_view
WHERE kstar = '3101010150'
AND versn = @versn
GROUP BY objnr, twaer ),
"Desp Incl. realizadas acum. = Soma de custo total do período anterior
+desp_acum AS (
SELECT objnr, twaer,
CAST( CASE WHEN '01' = @period_prev THEN SUM( wtg001 ) ELSE 0 END +
CASE WHEN '02' = @period_prev THEN SUM( wtg002 ) ELSE 0 END +
CASE WHEN '03' = @period_prev THEN SUM( wtg003 ) ELSE 0 END +
CASE WHEN '04' = @period_prev THEN SUM( wtg004 ) ELSE 0 END +
CASE WHEN '05' = @period_prev THEN SUM( wtg005 ) ELSE 0 END +
CASE WHEN '06' = @period_prev THEN SUM( wtg006 ) ELSE 0 END +
CASE WHEN '07' = @period_prev THEN SUM( wtg007 ) ELSE 0 END +
CASE WHEN '08' = @period_prev THEN SUM( wtg008 ) ELSE 0 END +
CASE WHEN '09' = @period_prev THEN SUM( wtg009 ) ELSE 0 END +
CASE WHEN '10' = @period_prev THEN SUM( wtg010 ) ELSE 0 END +
CASE WHEN '11' = @period_prev THEN SUM( wtg011 ) ELSE 0 END +
CASE WHEN '12' = @period_prev THEN SUM( wtg012 ) ELSE 0 END
AS DEC( 23, 2 ) ) AS desp_acum
FROM v_cosp_view
WHERE gjahr = @year_prev
AND kstar = '3101010150'
AND versn = @versn
GROUP BY objnr, twaer ),
"Horas incorridas acumuladas = Soma das horas lançadas e transferidas CO do período anterior
+hreal AS (
SELECT objnr, gjahr, meinh,
CAST( CASE WHEN '01' = @period_prev THEN SUM( meg001 ) ELSE 0 END +
CASE WHEN '02' = @period_prev THEN SUM( meg002 ) ELSE 0 END +
CASE WHEN '03' = @period_prev THEN SUM( meg003 ) ELSE 0 END +
CASE WHEN '04' = @period_prev THEN SUM( meg004 ) ELSE 0 END +
CASE WHEN '05' = @period_prev THEN SUM( meg005 ) ELSE 0 END +
CASE WHEN '06' = @period_prev THEN SUM( meg006 ) ELSE 0 END +
CASE WHEN '07' = @period_prev THEN SUM( meg007 ) ELSE 0 END +
CASE WHEN '08' = @period_prev THEN SUM( meg008 ) ELSE 0 END +
CASE WHEN '09' = @period_prev THEN SUM( meg009 ) ELSE 0 END +
CASE WHEN '10' = @period_prev THEN SUM( meg010 ) ELSE 0 END +
CASE WHEN '11' = @period_prev THEN SUM( meg011 ) ELSE 0 END +
CASE WHEN '12' = @period_prev THEN SUM( meg012 ) ELSE 0 END
AS DEC( 23, 2 ) ) AS horas_brt2
FROM v_coss_view
WHERE kstar = '9040101000'
AND versn = @versn
AND wrttp = '04'
GROUP BY objnr, gjahr, meinh ),
"Taxa Budget
+budget AS (
SELECT objnr, twaer,
CAST( CASE WHEN '01' BETWEEN @period_begin AND @period_end THEN SUM( wtg001 ) ELSE 0 END +
CASE WHEN '02' BETWEEN @period_begin AND @period_end THEN SUM( wtg002 ) ELSE 0 END +
CASE WHEN '03' BETWEEN @period_begin AND @period_end THEN SUM( wtg003 ) ELSE 0 END +
CASE WHEN '04' BETWEEN @period_begin AND @period_end THEN SUM( wtg004 ) ELSE 0 END +
CASE WHEN '05' BETWEEN @period_begin AND @period_end THEN SUM( wtg005 ) ELSE 0 END +
CASE WHEN '06' BETWEEN @period_begin AND @period_end THEN SUM( wtg006 ) ELSE 0 END +
CASE WHEN '07' BETWEEN @period_begin AND @period_end THEN SUM( wtg007 ) ELSE 0 END +
CASE WHEN '08' BETWEEN @period_begin AND @period_end THEN SUM( wtg008 ) ELSE 0 END +
CASE WHEN '09' BETWEEN @period_begin AND @period_end THEN SUM( wtg009 ) ELSE 0 END +
CASE WHEN '10' BETWEEN @period_begin AND @period_end THEN SUM( wtg010 ) ELSE 0 END +
CASE WHEN '11' BETWEEN @period_begin AND @period_end THEN SUM( wtg011 ) ELSE 0 END +
CASE WHEN '12' BETWEEN @period_begin AND @period_end THEN SUM( wtg012 ) ELSE 0 END
AS DEC( 23, 2 ) ) AS taxa_budget
FROM v_cosp_view
WHERE kstar = '3101010150'
AND versn = @versn
GROUP BY objnr, twaer ),
+receit AS (
SELECT objnr, twaer,
CAST( CASE WHEN '01' BETWEEN @period_start AND @period_end THEN SUM( wtg001 ) ELSE 0 END +
CASE WHEN '02' BETWEEN @period_start AND @period_end THEN SUM( wtg002 ) ELSE 0 END +
CASE WHEN '03' BETWEEN @period_start AND @period_end THEN SUM( wtg003 ) ELSE 0 END +
CASE WHEN '04' BETWEEN @period_start AND @period_end THEN SUM( wtg004 ) ELSE 0 END +
CASE WHEN '05' BETWEEN @period_start AND @period_end THEN SUM( wtg005 ) ELSE 0 END +
CASE WHEN '06' BETWEEN @period_start AND @period_end THEN SUM( wtg006 ) ELSE 0 END +
CASE WHEN '07' BETWEEN @period_start AND @period_end THEN SUM( wtg007 ) ELSE 0 END +
CASE WHEN '08' BETWEEN @period_start AND @period_end THEN SUM( wtg008 ) ELSE 0 END +
CASE WHEN '09' BETWEEN @period_start AND @period_end THEN SUM( wtg009 ) ELSE 0 END +
CASE WHEN '10' BETWEEN @period_start AND @period_end THEN SUM( wtg010 ) ELSE 0 END +
CASE WHEN '11' BETWEEN @period_start AND @period_end THEN SUM( wtg011 ) ELSE 0 END +
CASE WHEN '12' BETWEEN @period_start AND @period_end THEN SUM( wtg012 ) ELSE 0 END
AS DEC( 23, 2 ) ) AS receit_plan
FROM v_cosp_view
WHERE kstar = '3101010150'
AND versn = @versn
GROUP BY objnr, twaer ),
+progr AS (
SELECT objnr, twaer,
CAST( CASE WHEN '01' BETWEEN @period_begin AND @period_end THEN SUM( wtg001 ) ELSE 0 END +
CASE WHEN '02' BETWEEN @period_begin AND @period_end THEN SUM( wtg002 ) ELSE 0 END +
CASE WHEN '03' BETWEEN @period_begin AND @period_end THEN SUM( wtg003 ) ELSE 0 END +
CASE WHEN '04' BETWEEN @period_begin AND @period_end THEN SUM( wtg004 ) ELSE 0 END +
CASE WHEN '05' BETWEEN @period_begin AND @period_end THEN SUM( wtg005 ) ELSE 0 END +
CASE WHEN '06' BETWEEN @period_begin AND @period_end THEN SUM( wtg006 ) ELSE 0 END +
CASE WHEN '07' BETWEEN @period_begin AND @period_end THEN SUM( wtg007 ) ELSE 0 END +
CASE WHEN '08' BETWEEN @period_begin AND @period_end THEN SUM( wtg008 ) ELSE 0 END +
CASE WHEN '09' BETWEEN @period_begin AND @period_end THEN SUM( wtg009 ) ELSE 0 END +
CASE WHEN '10' BETWEEN @period_begin AND @period_end THEN SUM( wtg010 ) ELSE 0 END +
CASE WHEN '11' BETWEEN @period_begin AND @period_end THEN SUM( wtg011 ) ELSE 0 END +
CASE WHEN '12' BETWEEN @period_begin AND @period_end THEN SUM( wtg012 ) ELSE 0 END
AS DEC( 23, 2 ) ) AS progr_brt
FROM v_coss_view
WHERE kstar = '9040101000'
AND versn = @versn
GROUP BY objnr, twaer ),
+hplan AS (
SELECT objnr, gjahr, meinh,
CAST( CASE WHEN '01' BETWEEN @period_begin AND @period_end THEN SUM( meg001 ) ELSE 0 END +
CASE WHEN '02' BETWEEN @period_begin AND @period_end THEN SUM( meg002 ) ELSE 0 END +
CASE WHEN '03' BETWEEN @period_begin AND @period_end THEN SUM( meg003 ) ELSE 0 END +
CASE WHEN '04' BETWEEN @period_begin AND @period_end THEN SUM( meg004 ) ELSE 0 END +
CASE WHEN '05' BETWEEN @period_begin AND @period_end THEN SUM( meg005 ) ELSE 0 END +
CASE WHEN '06' BETWEEN @period_begin AND @period_end THEN SUM( meg006 ) ELSE 0 END +
CASE WHEN '07' BETWEEN @period_begin AND @period_end THEN SUM( meg007 ) ELSE 0 END +
CASE WHEN '08' BETWEEN @period_begin AND @period_end THEN SUM( meg008 ) ELSE 0 END +
CASE WHEN '09' BETWEEN @period_begin AND @period_end THEN SUM( meg009 ) ELSE 0 END +
CASE WHEN '10' BETWEEN @period_begin AND @period_end THEN SUM( meg010 ) ELSE 0 END +
CASE WHEN '11' BETWEEN @period_begin AND @period_end THEN SUM( meg011 ) ELSE 0 END +
CASE WHEN '12' BETWEEN @period_begin AND @period_end THEN SUM( meg012 ) ELSE 0 END
AS DEC( 23, 2 ) ) AS horas_brt
FROM v_coss_view
WHERE kstar = '9040101000'
AND wrttp = '01'
GROUP BY objnr, gjahr, meinh ),
* +hreal AS (
* SELECT rproj, meinh, SUM( catshours ) AS horas_brt2
* FROM catsdb
* GROUP BY rproj, meinh ),
+quant AS (
SELECT rproj, meinh, SUM( catshours ) AS wip_mes
FROM catsdb
WHERE workdate BETWEEN @first_day AND @last_day
GROUP BY rproj, meinh ),
+result( pspnr, posid, pedit, objnr, prart, pratx, verna, astna, moeda,
partner1, partner2,
gjahr,
meinh1, horas_brt,
meinh2, horas_brt2,
etc,
meinh3, wip_mes,
fatur_brt,
progr_brt,
desp_incl,
desp_acum,
taxa_budget,
receit_plan,
acca_brt, honor_brt,
acca_bruto ) AS (
SELECT
p~pspnr, p~posid, p~posid_edit, p~objnr, p~prart, x~pratx, p~verna, p~astna, p~pwpos AS moeda,
b~name_org1 AS partner1, u~name_org1 AS partner2,
h~gjahr,
h~meinh AS meinh1, h~horas_brt,
d~meinh AS meinh2, d~horas_brt2,
( h~horas_brt - d~horas_brt2 ) AS etc,
q~meinh AS meinh3, q~wip_mes,
a~fatur_brt,
s~progr_brt,
e~desp_incl,
i~desp_acum,
f~taxa_budget,
g~receit_plan,
c~acca_brt, c~honor_brt,
( c~acca_brt - c~honor_brt ) AS acca_bruto
FROM proj AS j
JOIN prps AS p ON p~psphi = j~pspnr
LEFT JOIN tcj1t AS x ON x~prart = p~prart
AND x~langu = @sy-langu
LEFT JOIN but000 AS b ON b~partner = substring( j~usr00, 1, 10 )
LEFT JOIN but000 AS u ON u~partner = right( j~usr01, 10 )
LEFT JOIN +hplan AS h ON h~objnr = p~objnr
LEFT JOIN +hreal AS d ON d~objnr = p~objnr
LEFT JOIN +quant AS q ON q~rproj = p~pspnr
LEFT JOIN +fatur AS a ON a~ps_psp_pnr = p~pspnr
AND a~rtcur = p~pwpos
LEFT JOIN +desp_incl AS e ON e~ps_psp_pnr = p~pspnr
AND e~rtcur = p~pwpos
LEFT JOIN +desp_acum AS i ON i~objnr = p~objnr
AND i~twaer = p~pwpos
LEFT JOIN +budget AS f ON f~objnr = p~objnr
AND f~twaer = p~pwpos
LEFT JOIN +receit AS g ON g~objnr = p~objnr
AND g~twaer = p~pwpos
LEFT JOIN +progr AS s ON s~objnr = p~objnr
AND s~twaer = p~pwpos
LEFT JOIN +honor AS c ON c~objnr = p~objnr
AND c~twaer = p~pwpos
WHERE p~verna IN @r_verna[]
AND p~astnr IN @r_astnr[]
AND p~posid IN @r_posid[]
AND p~prart IN @r_prart[]
AND p~pkokr IN @r_kokrs[] )
SELECT * FROM +result INTO TABLE @DATA(results).
_time = ( timer->get_runtime( ) - t1 ) / 1000.
mt_psxra = CORRESPONDING #( results MAPPING unidade = meinh1 ).
ENDMETHOD.