@AbapCatalog.preserveKey: true
@AbapCatalog.sqlViewName: 'PFIFINSSKFPER1'
@AbapCatalog.buffering.status: #NOT_ALLOWED
@AbapCatalog.compiler.compareFilter: true
// @AccessControl.authorizationCheck: #CHECK
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
//@EndUserText.label: 'Statistical Keyfigures per period - Union'
@VDM.viewType: #COMPOSITE
@VDM.private:true
define view P_FinStatisticalKeyFigureItem1
as select from I_FinStatisticalKeyFigItemBsc
inner join I_FiscalCalYearPeriodForLedger
on I_FiscalCalYearPeriodForLedger.CompanyCode = I_FinStatisticalKeyFigItemBsc.CompanyCode
and ( ( I_FinStatisticalKeyFigItemBsc.StatisticalKeyFigureCategory = '1' and
I_FinStatisticalKeyFigItemBsc.ValidityStartDate > I_FiscalCalYearPeriodForLedger.FiscalPeriodEndDate )
or ( I_FinStatisticalKeyFigItemBsc.StatisticalKeyFigureCategory = '2' and
I_FinStatisticalKeyFigItemBsc.ValidityStartDate >= I_FiscalCalYearPeriodForLedger.FiscalPeriodStartDate and
I_FinStatisticalKeyFigItemBsc.ValidityStartDate <= I_FiscalCalYearPeriodForLedger.FiscalPeriodEndDate )
)
////////////////////////////////////////////////////////////////////////////////
// the first union subtree is for the normal case with filled Company Code
// Here we derive the FiscalYearVariant from Ledger/CompanyCode
////////////////////////////////////////////////////////////////////////////////
association [0..1] to I_WBSElementBasicData as _WBSElementBasicData on $projection.ps_psp_pnr = _WBSElementBasicData.WBSElementInternalID
{
Ledger as rldnr,
I_FinStatisticalKeyFigItemBsc.CompanyCode as rbukrs,
FiscalYearPeriod as FISCYEARPER,
I_FinStatisticalKeyFigItemBsc.StatisticalKeyFigure as stagr,
I_FinStatisticalKeyFigItemBsc.StatisticalKeyFigureItem as recid,
FiscalYear as RYEAR,
FiscalPeriod as poper,
FiscalYearVariant as periv,
IsSpecialPeriod,
I_FinStatisticalKeyFigItemBsc.ValidityStartDate as date_from,
I_FinStatisticalKeyFigItemBsc.StatisticalKeyFigureCategory as grtyp,
@Semantics.unitOfMeasure:true
I_FinStatisticalKeyFigItemBsc.StatisticalKeyFigQtyUnit as runit,
@DefaultAggregation: #SUM
@Semantics: { quantity : {unitOfMeasure: 'I_FinStatisticalKeyFigItemBsc.StatisticalKeyFigQtyUnit'} } I_FinStatisticalKeyFigItemBsc.StatisticalKeyFigureQuantity as msl,
I_FinStatisticalKeyFigItemBsc.GLRecordType as rrcty,
I_FinStatisticalKeyFigItemBsc.PlanningCategory as category,
I_FinStatisticalKeyFigItemBsc.CostCenter as rcntr,
I_FinStatisticalKeyFigItemBsc.ProfitCenter as prctr,
I_FinStatisticalKeyFigItemBsc.FunctionalArea as rfarea,
I_FinStatisticalKeyFigItemBsc.BusinessArea as rbusa,
I_FinStatisticalKeyFigItemBsc.ControllingArea as kokrs,
I_FinStatisticalKeyFigItemBsc.Segment as segment,
I_FinStatisticalKeyFigItemBsc.ControllingBusTransacType as vorgn,
I_FinStatisticalKeyFigItemBsc.AccountAssignmentType as accasty,
I_FinStatisticalKeyFigItemBsc.LogicalSystem as logsys,
I_FinStatisticalKeyFigItemBsc.ControllingObject as objnr,
I_FinStatisticalKeyFigItemBsc.ControllingKeySubNumber as hrkft,
//I_FinStatisticalKeyFigItemBsc.paobjnr,
I_FinStatisticalKeyFigItemBsc.ControllingObjectClass as scope,
I_FinStatisticalKeyFigItemBsc.OrderID as aufnr,
I_FinStatisticalKeyFigItemBsc.OrderCategory as autyp,
// I_FinStatisticalKeyFigItemBsc.WBSElementInternalID as ps_psp_pnr,
cast( I_FinStatisticalKeyFigItemBsc.WBSElementInternalID as fis_wbsint_no_conv preserving type ) as ps_psp_pnr,
I_FinStatisticalKeyFigItemBsc.WBSElement as ps_posid,
cast( COALESCE( _WBSElementBasicData.WBSElementExternalID , '' ) as fis_wbsext_no_conv preserving type ) as WBSElementExternalID,
I_FinStatisticalKeyFigItemBsc.ProjectInternalID as ps_prj_pnr,
I_FinStatisticalKeyFigItemBsc.Project as ps_pspid,
I_FinStatisticalKeyFigItemBsc.ProjectNetwork as nplnr,
I_FinStatisticalKeyFigItemBsc.RelatedNetworkActivity as nplnr_vorgn,
I_FinStatisticalKeyFigItemBsc.BusinessProcess as prznr,
I_FinStatisticalKeyFigItemBsc.CostObject as kstrg,
I_FinStatisticalKeyFigItemBsc.Fund as rfund,
I_FinStatisticalKeyFigItemBsc.GrantID as rgrant_nbr,
I_FinStatisticalKeyFigItemBsc.BudgetPeriod as rbudget_pd,
//I_FinStatisticalKeyFigItemBsc.vbeln,
//I_FinStatisticalKeyFigItemBsc.posnr,
I_FinStatisticalKeyFigItemBsc.SalesOrder as kdauf,
I_FinStatisticalKeyFigItemBsc.SalesOrderItem as kdpos,
I_FinStatisticalKeyFigItemBsc.CostCtrActivityType as lstar,
I_FinStatisticalKeyFigItemBsc.ServiceDocumentType as service_doc_type,
I_FinStatisticalKeyFigItemBsc.ServiceDocument as service_doc_id,
I_FinStatisticalKeyFigItemBsc.ServiceDocumentItem as service_doc_item_id
}
////////////////////////////////////////////////////////////////////////////////
// the second union subtree is for the special case with initial Company Code
// Here we derive the FiscalYearVariant from ControllingArea
// (in the first release we only want to allow filled CompanyCode; but for
// later version with COPA planning it could be possible)
////////////////////////////////////////////////////////////////////////////////
union all
select from I_FinStatisticalKeyFigItemBsc
inner join ( I_FiscalYearPeriod inner join I_ControllingArea on I_FiscalYearPeriod.FiscalYearVariant = I_ControllingArea.FiscalYearVariant )
on I_FinStatisticalKeyFigItemBsc.CompanyCode = ' '
and I_FinStatisticalKeyFigItemBsc.ControllingArea = I_ControllingArea.ControllingArea
and ( ( I_FinStatisticalKeyFigItemBsc.StatisticalKeyFigureCategory = '1' and
I_FinStatisticalKeyFigItemBsc.ValidityStartDate > I_FiscalYearPeriod.FiscalPeriodEndDate )
or ( I_FinStatisticalKeyFigItemBsc.StatisticalKeyFigureCategory = '2' and
I_FinStatisticalKeyFigItemBsc.ValidityStartDate >= I_FiscalYearPeriod.FiscalPeriodStartDate and
I_FinStatisticalKeyFigItemBsc.ValidityStartDate <= I_FiscalYearPeriod.FiscalPeriodEndDate )
)
inner join I_Ledger on I_Ledger.IsLeadingLedger = 'X'
association [0..1] to I_WBSElementBasicData as _WBSElementBasicData on $projection.ps_psp_pnr = _WBSElementBasicData.WBSElementInternalID
{
Ledger as rldnr,
I_FinStatisticalKeyFigItemBsc.CompanyCode as rbukrs,
FiscalYearPeriod as FISCYEARPER,
I_FinStatisticalKeyFigItemBsc.StatisticalKeyFigure as stagr,
I_FinStatisticalKeyFigItemBsc.StatisticalKeyFigureItem as recid,
FiscalYear as RYEAR,
FiscalPeriod as poper,
I_FiscalYearPeriod.FiscalYearVariant as periv,
IsSpecialPeriod,
I_FinStatisticalKeyFigItemBsc.ValidityStartDate as date_from,
I_FinStatisticalKeyFigItemBsc.StatisticalKeyFigureCategory as grtyp,
@Semantics.unitOfMeasure:true
I_FinStatisticalKeyFigItemBsc.StatisticalKeyFigQtyUnit as runit,
@DefaultAggregation: #SUM
@Semantics: { quantity : {unitOfMeasure: 'I_FinStatisticalKeyFigItemBsc.StatisticalKeyFigQtyUnit'} } I_FinStatisticalKeyFigItemBsc.StatisticalKeyFigureQuantity as msl,
I_FinStatisticalKeyFigItemBsc.GLRecordType as rrcty,
I_FinStatisticalKeyFigItemBsc.PlanningCategory as category,
I_FinStatisticalKeyFigItemBsc.CostCenter as rcntr,
I_FinStatisticalKeyFigItemBsc.ProfitCenter as prctr,
I_FinStatisticalKeyFigItemBsc.FunctionalArea as rfarea,
I_FinStatisticalKeyFigItemBsc.BusinessArea as rbusa,
I_FinStatisticalKeyFigItemBsc.ControllingArea as kokrs,
I_FinStatisticalKeyFigItemBsc.Segment as segment,
I_FinStatisticalKeyFigItemBsc.ControllingBusTransacType as vorgn,
I_FinStatisticalKeyFigItemBsc.AccountAssignmentType as accasty,
I_FinStatisticalKeyFigItemBsc.LogicalSystem as logsys,
I_FinStatisticalKeyFigItemBsc.ControllingObject as objnr,
I_FinStatisticalKeyFigItemBsc.ControllingKeySubNumber as hrkft,
//I_FinStatisticalKeyFigItemBsc.paobjnr,
I_FinStatisticalKeyFigItemBsc.ControllingObjectClass as scope,
I_FinStatisticalKeyFigItemBsc.OrderID as aufnr,
I_FinStatisticalKeyFigItemBsc.OrderCategory as autyp,
// I_FinStatisticalKeyFigItemBsc.WBSElementInternalID as ps_psp_pnr,
cast( I_FinStatisticalKeyFigItemBsc.WBSElementInternalID as fis_wbsint_no_conv preserving type ) as ps_psp_pnr,
I_FinStatisticalKeyFigItemBsc.WBSElement as ps_posid,
cast( COALESCE( _WBSElementBasicData.WBSElementExternalID , '' ) as fis_wbsext_no_conv preserving type ) as WBSElementExternalID,
I_FinStatisticalKeyFigItemBsc.ProjectInternalID as ps_prj_pnr,
I_FinStatisticalKeyFigItemBsc.Project as ps_pspid,
I_FinStatisticalKeyFigItemBsc.ProjectNetwork as nplnr,
I_FinStatisticalKeyFigItemBsc.RelatedNetworkActivity as nplnr_vorgn,
I_FinStatisticalKeyFigItemBsc.BusinessProcess as prznr,
I_FinStatisticalKeyFigItemBsc.CostObject as kstrg,
I_FinStatisticalKeyFigItemBsc.Fund as rfund,
I_FinStatisticalKeyFigItemBsc.GrantID as rgrant_nbr,
I_FinStatisticalKeyFigItemBsc.BudgetPeriod as rbudget_pd,
//I_FinStatisticalKeyFigItemBsc.vbeln,
//I_FinStatisticalKeyFigItemBsc.posnr,
I_FinStatisticalKeyFigItemBsc.SalesOrder as kdauf,
I_FinStatisticalKeyFigItemBsc.SalesOrderItem as kdpos,
I_FinStatisticalKeyFigItemBsc.CostCtrActivityType as lstar,
I_FinStatisticalKeyFigItemBsc.ServiceDocumentType as service_doc_type,
I_FinStatisticalKeyFigItemBsc.ServiceDocument as service_doc_id,
I_FinStatisticalKeyFigItemBsc.ServiceDocumentItem as service_doc_item_id
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CONTROLLINGAREA",
"I_FINSTATISTICALKEYFIGITEMBSC",
"I_FISCALCALYEARPERIODFORLEDGER",
"I_FISCALYEARPERIOD",
"I_LEDGER",
"I_WBSELEMENTBASICDATA"
],
"ASSOCIATED":
[
"I_WBSELEMENTBASICDATA"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/