@VDM.private: true
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #NOT_REQUIRED
// only LF is used in current existing views
// logic for other period types won't be deleted for the sake of documentation
define view entity P_Matval_Manage_Bal_Split
as select from I_ProductValuationBasic as hd
left outer to many join ckmlhd as ch on ch.matnr = hd.Product
and ch.bwkey = hd.ValuationArea
inner join acdoca_m_extract as ex on ex.kalnr = ch.kalnr
and ex.matnr = ch.matnr
and ex.bwkey = ch.bwkey
{
key hd.ProdCostEstNumber as CostEstimate,
key cast(ex.rldnr as fis_rldnr preserving type) as Ledger,
key ex.rbukrs as CompanyCode,
key ex.bwkey as ValuationArea,
key ex.matnr as Material,
key 'LF' as period_type,
cast( 'KG' as meins ) as unit,
@Semantics.quantity.unitOfMeasure : 'unit'
sum(ex.vmsl) as TotalVltdStockQuantity,
cast( 'EUR' as waers ) as CurrencyCode,
@Semantics.amount.currencyCode : 'CurrencyCode'
sum(ex.hsl) as AmountInCompanyCodeCurrency,
@Semantics.amount.currencyCode : 'CurrencyCode'
sum(ex.ksl) as AmountInGroupCurrency,
@Semantics.amount.currencyCode : 'CurrencyCode'
sum(ex.osl) as AmountInFreeDefinedCurrency1,
@Semantics.amount.currencyCode : 'CurrencyCode'
sum(ex.vsl) as AmountInFreeDefinedCurrency2,
@Semantics.amount.currencyCode : 'CurrencyCode'
sum(ex.bsl) as AmountInFreeDefinedCurrency3,
@Semantics.amount.currencyCode : 'CurrencyCode'
sum(ex.csl) as AmountInFreeDefinedCurrency4,
@Semantics.amount.currencyCode : 'CurrencyCode'
sum(ex.dsl) as AmountInFreeDefinedCurrency5,
@Semantics.amount.currencyCode : 'CurrencyCode'
sum(ex.esl) as AmountInFreeDefinedCurrency6,
@Semantics.amount.currencyCode : 'CurrencyCode'
sum(ex.fsl) as AmountInFreeDefinedCurrency7,
@Semantics.amount.currencyCode : 'CurrencyCode'
sum(ex.gsl) as AmountInFreeDefinedCurrency8
}
where
hd.ValuationType = ''
and hd.ValuationCategory <> ''
and ex.fiscyearper = '9999999'
group by
ex.rbukrs,
ex.bwkey,
ex.rldnr,
hd.ProdCostEstNumber,
ex.matnr
//
//union all select from mbew as hd
// inner join t001k as tk on tk.bwkey = hd.bwkey
//// inner join marv as mv on mv.bukrs = tk.bukrs
// left outer join mbew as ch on ch.matnr = hd.matnr
// and ch.bwkey = hd.bwkey
// inner join acdoca_m_extract as ex on ex.kalnr = ch.kaln1
// and ex.matnr = ch.matnr
// and ex.bwkey = ch.bwkey
// inner join P_MatlCtrlRecdLdgrFiscalPeriod as marvld on marvld.CompanyCode = ex.rbukrs
// and marvld.Ledger = ex.rldnr
//
//{
// key hd.kaln1 as CostEstimate,
// key cast(ex.rldnr as fis_rldnr preserving type) as Ledger,
// key ex.rbukrs as CompanyCode,
// key ex.bwkey as ValuationArea,
// key ex.matnr as Material,
// key 'VM' as period_type,
//
// cast (sum(ex.vmsl) as vquan1_12) as TotalVltdStockQuantity,
// cast (sum(hsl) as fins_vhcur12) as AmountInCompanyCodeCurrency,
// cast (sum(ksl) as fins_vkcur12) as AmountInGroupCurrency,
// cast (sum(osl) as fins_vocur12) as AmountInFreeDefinedCurrency1,
// cast (sum(vsl) as fins_vvcur12) as AmountInFreeDefinedCurrency2,
// cast (sum(bsl) as fins_vbcur12) as AmountInFreeDefinedCurrency3,
// cast (sum(csl) as fins_vccur12) as AmountInFreeDefinedCurrency4,
// cast (sum(dsl) as fins_vdcur12) as AmountInFreeDefinedCurrency5,
// cast (sum(esl) as fins_vecur12) as AmountInFreeDefinedCurrency6,
// cast (sum(fsl) as fins_vfcur12) as AmountInFreeDefinedCurrency7,
// cast (sum(gsl) as fins_vgcur12) as AmountInFreeDefinedCurrency8
//}
//where
// hd.bwtar = ''
// and hd.bwtty <> ''
// and ex.fiscyearper > marvld.PreviousFiscalYearPeriod
//
//group by
// ex.rbukrs,
// ex.bwkey,
// ex.rldnr,
// hd.kaln1,
// ex.matnr
//
//union all select from mbew as hd
// inner join t001k as tk on tk.bwkey = hd.bwkey
//// inner join marv as mv on mv.bukrs = tk.bukrs
// left outer join mbew as ch on ch.matnr = hd.matnr
// and ch.bwkey = hd.bwkey
// inner join acdoca_m_extract as ex on ex.kalnr = ch.kaln1
// and ex.matnr = ch.matnr
// and ex.bwkey = ch.bwkey
// inner join P_MatlCtrlRecdLdgrFiscalPeriod as marvld on marvld.CompanyCode = ex.rbukrs
// and marvld.Ledger = ex.rldnr
//
//{
// key hd.kaln1 as CostEstimate,
// key cast(ex.rldnr as fis_rldnr preserving type) as Ledger,
// key ex.rbukrs as CompanyCode,
// key ex.bwkey as ValuationArea,
// key ex.matnr as Material,
// key 'VJ' as period_type,
//
// cast (sum(ex.vmsl) as vquan1_12) as TotalVltdStockQuantity,
// cast (sum(hsl) as fins_vhcur12) as AmountInCompanyCodeCurrency,
// cast (sum(ksl) as fins_vkcur12) as AmountInGroupCurrency,
// cast (sum(osl) as fins_vocur12) as AmountInFreeDefinedCurrency1,
// cast (sum(vsl) as fins_vvcur12) as AmountInFreeDefinedCurrency2,
// cast (sum(bsl) as fins_vbcur12) as AmountInFreeDefinedCurrency3,
// cast (sum(csl) as fins_vccur12) as AmountInFreeDefinedCurrency4,
// cast (sum(dsl) as fins_vdcur12) as AmountInFreeDefinedCurrency5,
// cast (sum(esl) as fins_vecur12) as AmountInFreeDefinedCurrency6,
// cast (sum(fsl) as fins_vfcur12) as AmountInFreeDefinedCurrency7,
// cast (sum(gsl) as fins_vgcur12) as AmountInFreeDefinedCurrency8
//}
//where
// hd.bwtar = ''
// and hd.bwtty <> ''
// and ex.fiscyearper > marvld.PrevFsclYearLastFsclYearPeriod
//
//group by
// ex.rbukrs,
// ex.bwkey,
// ex.rldnr,
// hd.kaln1,
// ex.matnr
//