Code: Alles auswählen.
SELECT b~vkorg, c~werks, a~matnr, l~lfimg,
v~omeng, ( l~lfimg + v~omeng ) AS posanz
FROM mara AS a
INNER JOIN mvke AS b
ON b~matnr = a~matnr
INNER JOIN marc AS c
ON c~matnr = a~matnr
INNER JOIN mbew AS d
ON d~matnr = a~matnr
AND d~bwkey = c~werks
LEFT OUTER JOIN zcds_zstori_lips( lv_lfdat_ab = @s_lfdat-low, lv_lfdat_bis = @s_lfdat-high ) as l
on l~matnr = a~matnr
left OUTER JOIN zcds_zstori_vbbe( lv_werks = @p_werks, lv_mbdat_ab = @s_mbdat-low, lv_mbdat_bis = @s_mbdat-high ) as v
on v~matnr = a~matnr
WHERE a~matnr IN @s_matnr
AND b~vkorg EQ @p_vkorg
AND b~lvorm NE 'X'
AND b~mtpos IN @lr_mtpos
AND c~mmsta IN @s_mstav
AND c~werks EQ @p_werks
AND c~zzstori_skip NE 'X'
AND d~bklas NOT BETWEEN '3000' AND '3999'
ORDER BY b~vkorg, c~werks, a~matnr
INTO TABLE @gt_itab_mat.
Code: Alles auswählen.
@AbapCatalog.sqlViewName: 'ZCDS_A_ZSTORI'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'CDS View für ZSTORI Job'
define view Zcds_Zstori_SEL with parameters lv_vkorg : vkorg,
lv_werks : werks_d,
lv_datab : erdat,
lv_datbis : erdat,
lv_vbbe_datab : mbdat,
lv_vbbe_datbis : mbdat
as select from mara as a
inner join mvke as b
on b.matnr = a.matnr
inner join marc as c
on c.matnr = a.matnr
inner join mbew as d
on d.matnr = a.matnr
and d.bwkey = c.werks
left outer join Zcds_Zstori_Lips(lv_lfdat_ab:$parameters.lv_datab, lv_lfdat_bis:$parameters.lv_datbis) as l
on l.matnr = a.matnr
left outer join Zcds_Zstori_Vbbe(lv_werks:$parameters.lv_werks,
lv_mbdat_ab:$parameters.lv_vbbe_datab,
lv_mbdat_bis:$parameters.lv_vbbe_datbis) as vbbe
on vbbe.matnr = a.matnr
{
b.vkorg,
c.werks,
a.matnr,
l.lfimg,
vbbe.omeng,
( l.lfimg + vbbe.omeng ) as posanz
}
where b.vkorg = :lv_vkorg
and b.lvorm <> 'X'
and ( b.mtpos = '0001' or b.mtpos = '0002' or b.mtpos = 'NORM' or b.mtpos = 'ZKDE')
and c.werks = :lv_werks
and c.mmsta = '30'
and c.zzstori_skip <> 'X'
and d.bklas not between '3000' and '3999'
Folgende Benutzer bedankten sich beim Autor black_adept für den Beitrag:
RaCDigger
Code: Alles auswählen.
...
SELECT b~vkorg, c~werks, a~matnr,
CASE
WHEN l~lfimg IS NULL THEN 0
ELSE l~lfimg
END,
CASE
WHEN v~omeng IS NULL THEN 0
ELSE v~omeng
END,
( l~lfimg + v~omeng ) AS posanz
FROM mara AS a
...
Code: Alles auswählen.
...
define view Zcds_Zstori_SEL with parameters lv_vkorg : vkorg,
lv_werks : werks_d,
lv_datab : erdat,
lv_datbis : erdat,
lv_vbbe_datab : mbdat,
lv_vbbe_datbis : mbdat
as select from mara as a
inner join mvke as b
on b.matnr = a.matnr
inner join marc as c
on c.matnr = a.matnr
inner join mbew as d
on d.matnr = a.matnr
and d.bwkey = c.werks
left outer join Zcds_Zstori_Lips(lv_lfdat_ab:$parameters.lv_datab, lv_lfdat_bis:$parameters.lv_datbis) as l
on l.matnr = a.matnr
left outer join Zcds_Zstori_Vbbe(lv_werks:$parameters.lv_werks,
lv_mbdat_ab:$parameters.lv_vbbe_datab,
lv_mbdat_bis:$parameters.lv_vbbe_datbis) as vbbe
on vbbe.matnr = a.matnr
{
b.vkorg,
c.werks,
a.matnr,
case
when l.lfimg is null then 0
else l.lfimg
end as lfimg,
case
when vbbe.omeng is null then 0
else vbbe.omeng
end as omeng
}
where b.vkorg = :lv_vkorg
and b.lvorm <> 'X'
and ( b.mtpos = '0001' or b.mtpos = '0002' or b.mtpos = 'NORM' or b.mtpos = 'ZKDE')
and c.werks = :lv_werks
and c.mmsta = '30'
and c.zzstori_skip <> 'X'
and d.bklas not between '3000' and '3999'
Code: Alles auswählen.
SELECT vkorg, werks, matnr, lfimg,
omeng, ( lfimg + omeng ) AS posanz
FROM zcds_a_zstori( lv_vkorg = @p_vkorg,
lv_werks = @p_werks,
lv_datab = @s_lfdat-low,
lv_datbis = @s_lfdat-high,
lv_vbbe_datab = @s_mbdat-low,
lv_vbbe_datbis = @s_mbdat-high )
INTO TABLE @gt_itab_mat