@AbapCatalog.sqlViewName: 'PMATLEDGERCURTP'
//@EndUserText.label: 'P_MATERIALLEDGER_CURTP'
@VDM.viewType: #COMPOSITE
@VDM.private:true
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #NOT_REQUIRED
//@ObjectModel.representativeKey: 'belnr'
define view P_Materialledger_CurTP
as select from P_Acdoca_Cube as it
//------------------------------------------------------------
// Part 1: Entry for currency column type "H"
//------------------------------------------------------------
inner join P_Materialledger_Relvt_Curtp as _curtp on (
it.rldnr = _curtp.rldnrh
or it.rldnr = _curtp.rldnrk
or it.rldnr = _curtp.rldnro
or it.rldnr = _curtp.rldnrv
)
and it.rbukrs = _curtp.bukrs
//inner join P_MaterialLedger_Curtp_Year as p
////on
//// P_MaterialLedger_Curtp_Year.rldnr = it.rldnr
////and
//on p.FiscalPeriodDate = it.fiscalyear
//inner join I_Ledger as ledger
// on it.rldnr = ledger.Ledger
//// and ledger.IsLeadingLedger = 'X'
association [0..1] to I_MaterialValuation as _MaterialValuation on it.kalnr = _MaterialValuation.CostEstimate
association [0..1] to I_BusinessTransactionType as _BusinessTransactionType on it.bttype = _BusinessTransactionType.BusinessTransactionType
//association[0..1] to I_Currency as _Currency on it.rhcur = _Currency.Currency
//association[0..1] to I_UnitOfMeasure as _UnitOfMeasure on it.rvunit = _UnitOfMeasure.UnitOfMeasure
//association[1..1] to P_MaterialLedger_Curtp_Year as p on p.FiscalPeriodDate = _FiscalYear.fiscalyear
{
key it.rbukrs,
key it.rldnr,
key it.racct,
key it.gjahr,
key it.belnr,
key it.docln,
key it.matnr,
it.ktopl,
it.blart,
it.bwkey,
it.bwtar,
it.poper,
it.ryear,
it.budat,
it.awref,
it.awtyp,
it.bldat,
it.bttype,
it.prctr,
it.rcntr,
it.segment,
it.usnam,
it.timestamp,
it.fiscyearper,
it.ebeln,
it.aufnr,
it.ktosl,
it.werks,
it.kokrs,
it.kalnr,
it.sobkz,
it.periv,
it.mlptyp,
it.mlcateg,
// it.hvkwrt,
it.bstat,
//----------------------------------------
//KEY FIGURES
//----------------------------------------
@Semantics.currencyCode:true
it.rhcur as rhcur,
@DefaultAggregation: #FORMULA
@Semantics: { amount : {currencyCode: 'rhcur'} }
it.hsl as hsl, //InventoryValue,
// @DefaultAggregation: #SUM
// @Semantics: { amount : {currencyCode: 'rkcur'} }
// it.hvkwrt as hvkwrt, //retail price,
//
@Semantics.currencyCode:true
it.rkcur as rkcur,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'rkcur'} }
// it.osl as ksl,
case Curtpk
when ' ' then cast(0 as fins_vkcur12)
else it.ksl
end as ksl,
@Semantics.currencyCode:true
it.rocur as rocur,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'rocur'} }
// it.osl as osl,
case Curtpo
when ' ' then cast(0 as fins_vocur12)
else it.osl
end as osl,
@Semantics.currencyCode:true
it.rvcur as rvcur,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'rvcur'} }
// it.vsl as vsl, FINS_VVCUR12
case Curtpv
when ' ' then cast(0 as fins_vocur12)
else it.vsl
end as vsl,
@Semantics.unitOfMeasure:true
it.rvunit as rvunit,
@DefaultAggregation: #SUM
@Semantics: { quantity : {unitOfMeasure: 'rvunit'} }
it.vmsl as vmsl,
_curtp.Curtph as CurtpH,
_curtp.rldnrh as rldnrh,
_curtp.ext_CurtpH as ext_CurtpH,
// _curtp.CurtpH_name as CurtpH_name,
_curtp.Curtpk as CurtpK,
_curtp.rldnrk as rldnrk,
_curtp.ext_CurtpK as ext_CurtpK,
// _curtp.CurtpK_name as CurtpK_name ,
_curtp.Curtpo as CurtpO,
_curtp.rldnro as rldnro,
_curtp.ext_CurtpO as ext_CurtpO,
// _curtp.CurtpO_name as CurtpO_name,
_curtp.Curtpv as CurtpV,
_curtp.rldnrv as rldnrv,
_curtp.ext_Curtpv as ext_CurtpV,
// _curtp.CurtpV_name as CurtpV_name,
it.xobew as xobew,
it.kzbws as kzbws,
it.mat_kdauf as mat_kdauf,
it.mat_kdpos as mat_kdpos,
it.mat_pspnr as mat_pspnr,
it.mat_lifnr as mat_lifnr,
//----------------------------------------
//Associations
//----------------------------------------
_GLAccountInChartOfAccounts,
_ChartOfAccounts,
_Segment,
_Material,
_CompanyCode,
_ProfitCenter,
_CostCenter,
_CompanyCodeCurrency,
_GlobalCurrency,
_FreeDefinedCurrency1,
_FreeDefinedCurrency2,
_CostSourceUnit,
_ControllingArea,
_Ledger,
_FiscalYear,
_FiscalYearVariant,
_JournalEntry,
// _ProfitCenterDetail,
// _CostCenterDetail,
_MaterialValuation,
_BusinessTransactionType
// _Currency,
// _UnitOfMeasure,
}
where
it.ktosl = 'BSX'
and it.rrcty = '0'
and it.poper > '000'
and it.kalnr > '000000000000'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"P_ACDOCA_CUBE",
"P_MATERIALLEDGER_RELVT_CURTP"
],
"ASSOCIATED":
[
"I_BUSINESSTRANSACTIONTYPE",
"I_CHARTOFACCOUNTS",
"I_COMPANYCODE",
"I_CONTROLLINGAREA",
"I_COSTCENTER",
"I_CURRENCY",
"I_FISCALYEARFORCOMPANYCODE",
"I_FISCALYEARVARIANT",
"I_GLACCOUNTINCHARTOFACCOUNTS",
"I_JOURNALENTRY",
"I_LEDGER",
"I_MATERIAL",
"I_MATERIALVALUATION",
"I_PROFITCENTER",
"I_SEGMENT",
"I_UNITOFMEASURE"
],
"BASE":
[
"P_ACDOCA_CUBE"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/