@AbapCatalog.sqlViewName: 'IPROCSTLITMCB'
@Analytics: { dataCategory: #CUBE, dataExtraction.enabled: true }
@VDM.viewType: #COMPOSITE
@EndUserText.label: 'Project Cost Line Items Cube'
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #REQUIRED
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.buffering.status: #NOT_ALLOWED
@Metadata.allowExtensions:true
@ObjectModel.semanticKey: ['ProjectObjectNode']
@AbapCatalog.preserveKey:true
@AccessControl.privilegedAssociations: [ '_ProjectHierarchy' ]
@VDM.lifecycle.contract.type: #SAP_INTERNAL_API
@AccessControl.personalData.blockingIndicator: ['true']
@Consumption.dbHints: [ 'USE_HEX_PLAN' ]
define view I_ProjectCostLineItemsCube
as select from I_ProjectCostLineItems as _ProjCostLineItems
association [0..1] to I_ProjectStructure as _ProjectHierarchy on $projection.ProjectObjectNode = _ProjectHierarchy.ProjectObjectNode
association [0..1] to I_PPM_AuthznByUsrH as _AuthUser on _AuthUser.ReferencedObjectUUID = $projection.ProjectSummaryTaskUUID
and _AuthUser.UserID = $session.user
and _AuthUser.Activity = 'Financials'
// 2508 - Performance Issue with Project Cost - Plan/Actual Application(for end user) - begin
// Note: Associations I_PPM_AuthznBySubstitH, I_PPM_AuthznByUserRoleH, I_PPM_AuthznByUsrGrpH do not return any data in public cloud systems.
// It is a legacy from the very old version of PCR app(but associations are still used in on premise systems).
// And cardinality [0..*] causes perforamnce issues for the end users(when DCL is applied).
// But we can not get rid of them completely according to guidlines, because it would be not compatible change.
// That's why we just provide constants for missing key fields in a join condition
//
// association [0..*] to I_PPM_AuthznBySubstitH as _AuthSubst on _AuthSubst.ReferencedObjectUUID = $projection.ProjectSummaryTaskUUID
// and _AuthSubst.UserID = $session.user
// and _AuthSubst.Activity = 'Financials'
// association [0..*] to I_PPM_AuthznByUserRoleH as _AuthRole on _AuthRole.ReferencedObjectUUID = $projection.ProjectSummaryTaskUUID
// and _AuthRole.UserID = $session.user
// and _AuthRole.Activity = 'Financials'
// association [0..*] to I_PPM_AuthznByUsrGrpH as _AuthGroup on _AuthGroup.ReferencedObjectUUID = $projection.ProjectSummaryTaskUUID
// and _AuthGroup.UserID = $session.user
// and _AuthGroup.Activity = 'Financials'
association [0..1] to I_PPM_AuthznBySubstitH as _AuthSubst on _AuthSubst.ReferencedObjectUUID = $projection.ProjectSummaryTaskUUID
and _AuthSubst.UserID = $session.user
and _AuthSubst.Activity = 'Financials'
and _AuthSubst.SubstitutedUser = 'X'
association [0..1] to I_PPM_AuthznByUserRoleH as _AuthRole on _AuthRole.ReferencedObjectUUID = $projection.ProjectSummaryTaskUUID
and _AuthRole.UserID = $session.user
and _AuthRole.Activity = 'Financials'
and _AuthRole.AuthorizationRoleID = 'X'
association [0..1] to I_PPM_AuthznByUsrGrpH as _AuthGroup on _AuthGroup.ReferencedObjectUUID = $projection.ProjectSummaryTaskUUID
and _AuthGroup.UserID = $session.user
and _AuthGroup.Activity = 'Financials'
and _AuthGroup.ProjectAuthznGroupID is null
// 2508 - Performance Issue with Project Cost - Plan/Actual Application(for end user) - end
association [0..1] to I_SingleResponsibleForProj as _Responsible on $projection.ProjectSummaryTaskUUID = _Responsible.ReferencedObjectUUID
association [1..1] to I_User as _UserName on $projection.AccountingDocCreatedByUser = _UserName.UserID
association [0..1] to I_PlanningCategory as _PlanningCategory on $projection.PlanningCategory = _PlanningCategory.PlanningCategory
{
@ObjectModel.foreignKey.association: '_ProjectHierarchy'
key _ProjCostLineItems.ProjectObjectNode,
key _ProjCostLineItems.Ledger,
key _ProjCostLineItems.SourceLedger,
key _ProjCostLineItems.CompanyCode,
key _ProjCostLineItems.FiscalYear,
key _ProjCostLineItems.AccountingDocument,
key _ProjCostLineItems.LedgerGLLineItem,
// plan posting keys
key _ProjCostLineItems.FinancialPlanningReqTransSqnc,
key _ProjCostLineItems.FinancialPlanningDataPacket,
key _ProjCostLineItems.ActualPlanJournalEntryItem,
_ProjectHierarchy.ProjectObjectRootNode,
_ProjCostLineItems.FinancialPlanningEntryItem,
_ProjCostLineItems.PlanningCategory,
_ProjCostLineItems.ControllingObjectCurrency,
@Semantics: { amount : {currencyCode: 'ControllingObjectCurrency'} }
_ProjCostLineItems.AmountInObjectCurrency,
_ProjCostLineItems.SemanticTag,
_ProjCostLineItems.PostingDate,
_ProjCostLineItems.AccountingDocCreatedByUser,
_ProjCostLineItems.ProjectSummaryTaskUUID,
_ProjCostLineItems.ProjectCategory,
_ProjCostLineItems.BusinessArea,
_ProjCostLineItems.GLAccount,
_ProjCostLineItems.ChartOfAccounts,
_ProjCostLineItems.ProfitCenter,
_ProjCostLineItems.WBSElementProfitCenter,
_ProjCostLineItems.ControllingArea,
_ProjCostLineItems.Project,
_ProjCostLineItems.ProjectUUID,
_ProjCostLineItems.ProjectDescription,
_ProjCostLineItems.ProjectProfileCode,
_ProjCostLineItems.ProjectInternalID,
_ProjCostLineItems.ProjectObject,
_ProjCostLineItems.WBSElement,
_ProjCostLineItems.WBSDescription,
_ProjCostLineItems.ResponsibleCostCenter,
// CE2402: Required Fields for AO /s4ppm/pr1 :- Start
_ProjCostLineItems.Plant,
_ProjCostLineItems.FunctionalArea,
// CE2402: Required Fields for AO /s4ppm/pr1 :- end
_ProjCostLineItems.ProjectType,
_ProjCostLineItems.OrderID,
MaintenanceOrderIsSelected,
_Responsible.BusinessPartnerFullName,
_Responsible.BusinessPartnerUUID,
_Responsible.IsBusinessPurposeCompleted,
IsCommitment,
@EndUserText.label: 'Created By'
_UserName.UserDescription,
IsSettled,
FiscalPeriod,
FiscalYearPeriod,
FiscalYearVariant,
PlanningCategory1,
PlanningCategory2,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
case
when PlanningCategory = PlanningCategory1
then
AmountInGlobalCurrency
else 0 end as OrderPlngCat1AmtInGlobCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'ControllingObjectCurrency'} }
case
when PlanningCategory = PlanningCategory1
then
AmountInObjectCurrency
else 0 end as OrderPlngCat1AmtInObjCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
case
when PlanningCategory = PlanningCategory1
then
AmountInTransactionCurrency
else 0 end as OrderPlngCat1AmtInTransCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case
when PlanningCategory = PlanningCategory1
then
AmountInCompanyCodeCurrency
else 0 end as OrderPlngCat1AmtInCoCodeCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency1'} }
case
when PlanningCategory = PlanningCategory1
then
AmountInFreeDefinedCurrency1
else 0 end as OrderPlngCat1AmtInFDCrcy1,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency2'} }
case
when PlanningCategory = PlanningCategory1
then
AmountInFreeDefinedCurrency2
else 0 end as OrderPlngCat1AmtInFDCrcy2,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency3'} }
case
when PlanningCategory = PlanningCategory1
then
AmountInFreeDefinedCurrency3
else 0 end as OrderPlngCat1AmtInFDCrcy3,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency4'} }
case
when PlanningCategory = PlanningCategory1
then
AmountInFreeDefinedCurrency4
else 0 end as OrderPlngCat1AmtInFDCrcy4,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency5'} }
case
when PlanningCategory = PlanningCategory1
then
AmountInFreeDefinedCurrency5
else 0 end as OrderPlngCat1AmtInFDCrcy5,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency6'} }
case
when PlanningCategory = PlanningCategory1
then
AmountInFreeDefinedCurrency6
else 0 end as OrderPlngCat1AmtInFDCrcy6,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency7'} }
case
when PlanningCategory = PlanningCategory1
then
AmountInFreeDefinedCurrency7
else 0 end as OrderPlngCat1AmtInFDCrcy7,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency8'} }
case
when PlanningCategory = PlanningCategory1
then
AmountInFreeDefinedCurrency8
else 0 end as OrderPlngCat1AmtInFDCrcy8,
//planning category 2
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
case
when PlanningCategory = PlanningCategory2
then
AmountInGlobalCurrency
else 0 end as OrderPlngCat2AmtInGlobCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'ControllingObjectCurrency'} }
case
when PlanningCategory = PlanningCategory2
then
AmountInObjectCurrency
else 0 end as OrderPlngCat2AmtInObjCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
case
when PlanningCategory = PlanningCategory2
then
AmountInTransactionCurrency
else 0 end as OrderPlngCat2AmtInTransCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case
when PlanningCategory = PlanningCategory2
then
AmountInCompanyCodeCurrency
else 0 end as OrderPlngCat2AmtInCoCodeCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency1'} }
case
when PlanningCategory = PlanningCategory2
then
AmountInFreeDefinedCurrency1
else 0 end as OrderPlngCat2AmtInFDCrcy1,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency2'} }
case
when PlanningCategory = PlanningCategory2
then
AmountInFreeDefinedCurrency2
else 0 end as OrderPlngCat2AmtInFDCrcy2,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency3'} }
case
when PlanningCategory = PlanningCategory2
then
AmountInFreeDefinedCurrency3
else 0 end as OrderPlngCat2AmtInFDCrcy3,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency4'} }
case
when PlanningCategory = PlanningCategory2
then
AmountInFreeDefinedCurrency4
else 0 end as OrderPlngCat2AmtInFDCrcy4,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency5'} }
case
when PlanningCategory = PlanningCategory2
then
AmountInFreeDefinedCurrency5
else 0 end as OrderPlngCat2AmtInFDCrcy5,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency6'} }
case
when PlanningCategory = PlanningCategory2
then
AmountInFreeDefinedCurrency6
else 0 end as OrderPlngCat2AmtInFDCrcy6,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency7'} }
case
when PlanningCategory = PlanningCategory2
then
AmountInFreeDefinedCurrency7
else 0 end as OrderPlngCat2AmtInFDCrcy7,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency8'} }
case
when PlanningCategory = PlanningCategory2
then
AmountInFreeDefinedCurrency8
else 0 end as OrderPlngCat2AmtInFDCrcy8,
@Semantics.currencyCode:true
TransactionCurrency,
@Semantics.currencyCode:true
CompanyCodeCurrency,
@Semantics.currencyCode:true
GlobalCurrency,
@Semantics.currencyCode:true
FreeDefinedCurrency1,
@Semantics.currencyCode:true
FreeDefinedCurrency2,
@Semantics.currencyCode:true
FreeDefinedCurrency3,
@Semantics.currencyCode:true
FreeDefinedCurrency4,
@Semantics.currencyCode:true
FreeDefinedCurrency5,
@Semantics.currencyCode:true
FreeDefinedCurrency6,
@Semantics.currencyCode:true
FreeDefinedCurrency7,
@Semantics.currencyCode:true
FreeDefinedCurrency8,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
AmountInTransactionCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
AmountInCompanyCodeCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
AmountInGlobalCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency1'} }
AmountInFreeDefinedCurrency1,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency2'} }
AmountInFreeDefinedCurrency2,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency3'} }
AmountInFreeDefinedCurrency3,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency4'} }
AmountInFreeDefinedCurrency4,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency5'} }
AmountInFreeDefinedCurrency5,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency6'} }
AmountInFreeDefinedCurrency6,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency7'} }
AmountInFreeDefinedCurrency7,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency8'} }
AmountInFreeDefinedCurrency8,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
ActualAmountInGlobalCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'ControllingObjectCurrency'} }
ActlCostInProjCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency1'} }
ActualAmountInFreeDfndCrcy1,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency2'} }
ActualAmountInFreeDfndCrcy2,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency3'} }
ActualAmountInFreeDfndCrcy3,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency4'} }
ActualAmountInFreeDfndCrcy4,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency5'} }
ActualAmountInFreeDfndCrcy5,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency6'} }
ActualAmountInFreeDfndCrcy6,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency7'} }
ActualAmountInFreeDfndCrcy7,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency8'} }
ActualAmountInFreeDfndCrcy8,
//2502 Include Project Stock costs in Project Cost Report - Begin
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
PrjStockAmountInGlobalCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'ControllingObjectCurrency'} }
ProjectStockAmtInProjCrcy,
// 2502 Include Project Stock costs in Project Cost Report - End
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
PlanAmountInGlobalCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'ControllingObjectCurrency'} }
PlndCostAmtInProjCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency1'} }
PlanAmountInFreeDefinedCrcy1,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency2'} }
PlanAmountInFreeDefinedCrcy2,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency3'} }
PlanAmountInFreeDefinedCrcy3,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency4'} }
PlanAmountInFreeDefinedCrcy4,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency5'} }
PlanAmountInFreeDefinedCrcy5,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency6'} }
PlanAmountInFreeDefinedCrcy6,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency7'} }
PlanAmountInFreeDefinedCrcy7,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency8'} }
PlanAmountInFreeDefinedCrcy8,
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
@DefaultAggregation: #SUM
CmtmtCostInGlobalCurrency,
@Semantics: { amount : {currencyCode: 'ControllingObjectCurrency'} }
@DefaultAggregation: #SUM
CmtmtCostInProjCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency1'} }
CmtmtCostInFreeDefinedCrcy1,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency2'} }
CmtmtCostInFreeDefinedCrcy2,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency3'} }
CmtmtCostInFreeDefinedCrcy3,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency4'} }
CmtmtCostInFreeDefinedCrcy4,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency5'} }
CmtmtCostInFreeDefinedCrcy5,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency6'} }
CmtmtCostInFreeDefinedCrcy6,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency7'} }
CmtmtCostInFreeDefinedCrcy7,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency8'} }
CmtmtCostInFreeDefinedCrcy8,
//2602 - Columns in Company Code Currency
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
ActualAmountInCompanyCodeCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
CmtmtCostInCCCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
PrjStockAmtInCompanyCodeCrcy,
//2602 - Columns in Company Code Currency
_ProjectHierarchy,
_UserName,
_AuthUser,
_AuthSubst,
_AuthRole,
_AuthGroup
}
where
(
_Responsible.BusinessPartnerUUID is null
or(
_Responsible.BusinessPartnerUUID is not null
and _Responsible.IsBusinessPurposeCompleted != 'X'
)
)
and _ProjCostLineItems.PartnerAccountAssignmentType != 'OR'