In this blog post i wanted to show some ideas how to work with aggregation expressions in CDS-View-Clusters. CDS-View-Clusters are my term for a bundle of Basic-, Composite- and Consumption-Views, which are chained together as a virtual data model. As example i will use some requirements from a recent project.
The CDS-View should provide the quantities of certified raw material consumed in process orders.
Goods movement types
The first fact view splits goods movements for certified and non certified materials. The quick and dirty way would be hardcoding the movement types in the where-clause.
But instead of hardcoding i want to show you a more customizable way. The table
The second fact view aggregates the goods movements. In this view we need to filter materials by its type. Here i want to show you the approach with customizing tables, too. The material type is stored as parameter in table
This view just provides the order number as a key field. So the group by-clause clearly indicates how the key figures are aggregated.
The consumption view joins finally joins the process order properties with the consumed quantities.
Context
The CDS-View should provide the quantities of certified raw material consumed in process orders.
The database scheme
ZCERTIFIED_MAT
ZGM_TYPES
Goods movement types
Implementation
The first fact view splits goods movements for certified and non certified materials. The quick and dirty way would be hardcoding the movement types in the where-clause.
@AbapCatalog.sqlViewName: 'zcm_cons1'
@VDM.viewType: #BASIC
define view zcm_consumption1 as select from aufm as gm
left outer join zcertified_mat as cm on cm.matnr = gm.matnr
inner join mara as m on m.matnr = gm.matnr {
key gm.aufnr,
gm.matnr,
m.mtart,
case when cm.matnr is not null then case gm.shkzg when 'H' then gm.menge else -1 * gm.menge end else 0 end as quantity_certified_material,
case gm.shkzg when 'H' then gm.menge else -1 * gm.menge end as quantity
} where bwart = '261' or bwart = '262'
But instead of hardcoding i want to show you a more customizable way. The table
ZGM_TYPES contains the movement types. So we can integrate this table in the CDS-View.
@AbapCatalog.sqlViewName: 'zcm_cons1'
@VDM.viewType: #BASIC
define view zcm_consumption1 as select from aufm as gm
left outer join zcertified_mat as cm on cm.matnr = gm.matnr
inner join mara as m on m.matnr = gm.matnr
// workaround to prevent syntax error
inner join zgm_types as t on t.mandt = gm.mandt {
key gm.aufnr,
gm.matnr,
m.mtart,
case when cm.matnr is not null then case gm.shkzg when 'H' then gm.menge else -1 * gm.menge end else 0 end as quantity_certified_material,
case gm.shkzg when 'H' then gm.menge else -1 * gm.menge end as quantity
} where gm.bwart = t.consumption or gm.bwart = t.cancel_consumption
The second fact view aggregates the goods movements. In this view we need to filter materials by its type. Here i want to show you the approach with customizing tables, too. The material type is stored as parameter in table
TVARVC. This table is joined with the view ZCM_CONSUMPTION2. The join replaces the hardcoded value in the where-clause.
@AbapCatalog.sqlViewName: 'zcm_cons2'
@VDM.viewType: #COMPOSITE
define view zcm_consumption2 as select from zcm_consumption1 as mc
inner join tvarvc as v on v.name = 'ZRAW_MATERIAL_TYPE' and v.type = 'P' and v.low = mc.mtart {
key mc.aufnr,
sum( quantity_certified_material ) as quantity_certified_material,
sum( quantity ) as quantity
} group by mc.aufnr
This view just provides the order number as a key field. So the group by-clause clearly indicates how the key figures are aggregated.
The consumption view joins finally joins the process order properties with the consumed quantities.
@AbapCatalog.sqlViewName: 'zcm_proc_ord'
@VDM.viewType: #CONSUMPTION
define view zcm_process_order as select from caufv as po
inner join zcm_consumption2 as c on c.aufnr = po.aufnr {
key po.aufnr,
po.werks,
po.auart,
po.autyp,
c.quantity_certified_material,
c.quantity as consumed_quantity
}
- SAP Managed Tags:
3 Comments
