@AbapCatalog.sqlViewName: 'FCML_MAT_V'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass: #MIXED
@ClientHandling.type: #CLIENT_DEPENDENT
@ClientHandling.algorithm: #AUTOMATED
@EndUserText.label: 'CDS View with structure of table FCML_MAT'
define view FCML_MAT_DDL
as select from ckmlhd as hd
inner join mara as mara on hd.matnr = mara.matnr
inner join t001k as t001k on hd.bwkey = t001k.bwkey
inner join t001 as t001 on t001k.bukrs = t001.bukrs
inner join t001w as t001w on hd.bwkey = t001w.bwkey and
t001k.mlbwa = t001w.mgvupd
inner join tka02 as tka02 on t001k.bukrs = tka02.bukrs
inner join v_mbew_md as mbew on hd.kalnr = mbew.kaln1
inner join nsdm_e_marc as marc on hd.matnr = marc.matnr
and hd.bwkey = marc.werks
{
key hd.kalnr ,
key cast ('00000000' as vszhl preserving type) as vszhl,
cast ('MA' as j_obart preserving type) as obart,
hd.matnr,
hd.bwkey as bwkey,
t001w.werks as werks,
hd.sobkz,
hd.vbeln,
hd.posnr,
hd.lifnr,
hd.bwtar,
hd.pspnr,
mbew.bklas as bklas,
hd.mlast,
mbew.hrkft,
t001k.bukrs,
tka02.kokrs,
cast (' ' as kostl preserving type) as kostl,
cast (' ' as lstar preserving type) as lstar,
cast (' ' as co_prznr preserving type) as prznr,
marc.prctr as prctr,
mara.mtart,
mara.mbrsh,
mara.matkl,
mara.meins,
mara.spart,
mara.prdha,
mara.attyp,
mara.laeda,
t001.periv
}
where hd.sobkz = '' and
t001w.mgvupd = 'X'
union all select from ckmlhd as hd
inner join mara as mara on hd.matnr = mara.matnr
inner join t001k as t001k on hd.bwkey = t001k.bwkey
inner join t001 as t001 on t001k.bukrs = t001.bukrs
inner join t001w as t001w on hd.bwkey = t001w.bwkey and
t001k.mlbwa = t001w.mgvupd
inner join tka02 as tka02 on t001k.bukrs = tka02.bukrs
inner join v_ebew_md as ebew on hd.kalnr = ebew.kaln1
inner join nsdm_e_marc as marc on hd.matnr = marc.matnr
and hd.bwkey = marc.werks
{
key hd.kalnr ,
key cast ('00000000' as vszhl preserving type) as vszhl,
cast ('MA' as j_obart preserving type) as obart,
hd.matnr,
hd.bwkey as bwkey,
t001w.werks as werks,
hd.sobkz,
hd.vbeln,
hd.posnr,
hd.lifnr,
hd.bwtar,
hd.pspnr,
ebew.bklas as bklas,
hd.mlast,
ebew.hrkft,
t001k.bukrs,
tka02.kokrs,
cast (' ' as kostl preserving type) as kostl,
cast (' ' as lstar preserving type) as lstar,
cast (' ' as co_prznr preserving type) as prznr,
marc.prctr as prctr,
mara.mtart,
mara.mbrsh,
mara.matkl,
mara.meins,
mara.spart,
mara.prdha,
mara.attyp,
mara.laeda,
t001.periv
}
where ( hd.sobkz = 'E' or
hd.sobkz = 'T' ) and
t001w.mgvupd = 'X'
union all select from ckmlhd as hd
inner join mara as mara on hd.matnr = mara.matnr
inner join t001k as t001k on hd.bwkey = t001k.bwkey
inner join t001 as t001 on t001k.bukrs = t001.bukrs
inner join t001w as t001w on hd.bwkey = t001w.bwkey and
t001k.mlbwa = t001w.mgvupd
inner join tka02 as tka02 on t001k.bukrs = tka02.bukrs
inner join v_qbew_md as qbew on hd.kalnr = qbew.kaln1
inner join nsdm_e_marc as marc on hd.matnr = marc.matnr
and hd.bwkey = marc.werks
{
key hd.kalnr ,
key cast ('00000000' as vszhl preserving type) as vszhl,
cast ('MA' as j_obart preserving type) as obart,
hd.matnr,
hd.bwkey as bwkey,
t001w.werks as werks,
hd.sobkz,
hd.vbeln,
hd.posnr,
hd.lifnr,
hd.bwtar,
hd.pspnr,
qbew.bklas as bklas,
hd.mlast,
qbew.hrkft,
t001k.bukrs,
tka02.kokrs,
cast (' ' as kostl preserving type) as kostl,
cast (' ' as lstar preserving type) as lstar,
cast (' ' as co_prznr preserving type) as prznr,
marc.prctr as prctr,
mara.mtart,
mara.mbrsh,
mara.matkl,
mara.meins,
mara.spart,
mara.prdha,
mara.attyp,
mara.laeda,
t001.periv
}
where hd.sobkz = 'Q' and
t001w.mgvupd = 'X'
union all select from ckmlhd as hd
inner join mara as mara on hd.matnr = mara.matnr
inner join t001k as t001k on hd.bwkey = t001k.bwkey
inner join t001 as t001 on t001k.bukrs = t001.bukrs
inner join t001w as t001w on hd.bwkey = t001w.bwkey and
t001k.mlbwa = t001w.mgvupd
inner join tka02 as tka02 on t001k.bukrs = tka02.bukrs
inner join v_obew_md as obew on hd.kalnr = obew.kaln1
inner join nsdm_e_marc as marc on hd.matnr = marc.matnr
and hd.bwkey = marc.werks
{
key hd.kalnr ,
key cast ('00000000' as vszhl preserving type) as vszhl,
cast ('MA' as j_obart preserving type) as obart,
hd.matnr,
hd.bwkey as bwkey,
t001w.werks as werks,
hd.sobkz,
hd.vbeln,
hd.posnr,
hd.lifnr,
hd.bwtar,
hd.pspnr,
obew.bklas as bklas,
hd.mlast,
obew.hrkft,
t001k.bukrs,
tka02.kokrs,
cast (' ' as kostl preserving type) as kostl,
cast (' ' as lstar preserving type) as lstar,
cast (' ' as co_prznr preserving type) as prznr,
marc.prctr as prctr,
mara.mtart,
mara.mbrsh,
mara.matkl,
mara.meins,
mara.spart,
mara.prdha,
mara.attyp,
mara.laeda,
t001.periv
}
where hd.sobkz = 'O' and
t001w.mgvupd = 'X'
union all select from ckmllahd as lahd
inner join t001k as t001k on lahd.werks = t001k.bwkey
inner join t001 as t001 on t001k.bukrs = t001.bukrs
{
key lahd.kalnr,
key cast ('00000000' as vszhl preserving type) as vszhl,
cast ('KL' as j_obart preserving type) as obart ,
'' as matnr,
lahd.werks as bwkey,
lahd.werks as werks,
'' as sobkz,
'' as vbeln,
'000000' as posnr,
'' as lifnr,
'' as bwtar,
'00000000' as pspnr,
'' as bklas,
'' as mlast,
'' as hrkft,
t001k.bukrs as bukrs,
cast ( substring(lahd.objnr,3,4) as kokrs preserving type) as kokrs,
cast ( substring(lahd.objnr,7,10) as kostl preserving type) as kostl,
cast ( substring(lahd.objnr,17,6) as lstar preserving type) as lstar,
cast( ' ' as co_prznr preserving type) as prznr,
'' as prctr,
'' as mtart,
'' as mbrsh,
'' as matkl,
' ' as meins,
'' as spart,
'' as prdha,
'' as attyp,
'' as laeda,
t001.periv
}
where lahd.objnr like 'KL%'
union all select from ckmllahd as lahd
inner join t001k as t001k on lahd.werks = t001k.bwkey
inner join t001 as t001 on t001k.bukrs = t001.bukrs
{
key lahd.kalnr,
key cast ('00000000' as vszhl preserving type) as vszhl,
cast ('BP' as j_obart preserving type) as obart ,
'' as matnr,
lahd.werks as bwkey,
lahd.werks as werks,
'' as sobkz,
'' as vbeln,
'000000' as posnr,
'' as lifnr,
'' as bwtar,
'00000000' as pspnr,
'' as bklas,
'' as mlast,
'' as hrkft,
t001k.bukrs as bukrs,
cast ( substring(lahd.objnr,3,4) as kokrs preserving type) as kokrs,
cast ( ' ' as kostl preserving type) as kostl,
cast ( ' ' as lstar preserving type) as lstar,
cast ( substring(lahd.objnr,7,12) as co_prznr preserving type) as prznr,
'' as prctr,
'' as mtart,
'' as mbrsh,
'' as matkl,
' ' as meins,
'' as spart,
'' as prdha,
'' as attyp,
'' as laeda,
t001.periv
}
where lahd.objnr like 'BP%'