@AbapCatalog.sqlViewName: 'PPROJFBUDGETSMRY'
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #CHECK
@VDM.private:true
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.buffering.status: #NOT_ALLOWED
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@AbapCatalog.compiler.compareFilter: true
@Consumption.dbHints: ['NO_JOIN_SIMPLIFICATION']
define view P_PROJECTFINBUDGETSMMRY
as select from P_EnterpriseProjectCosts as _ProjectCostLineItems //I_EnterpriseProject as _proj
// inner join P_EnterpriseProjectCosts as _ProjectCostLineItems on _ProjectCostLineItems.ProjectInternalID = _proj.ProjectInternalID
//I_EnterpriseProject as _proj
// inner join I_ProjectBasicData as _projBasic on _projBasic.ProjectInternalID = _proj.ProjectInternalID
// inner join P_ProjectCostLineItems as _ProjectCostLineItems on _ProjectCostLineItems.ProjectInternalID = _projBasic.ProjectInternalID
association [0..1] to P_ProjectLastPredictedOn as _ProjPredict on $projection.ProjectInternalID = _ProjPredict.ProjectInternalID
association [0..1] to R_EnterpriseProject as _ProjectSummary on _ProjectSummary.ProjectInternalID = $projection.ProjectInternalID
{
key _ProjectCostLineItems.ProjectUUID,
key _ProjectCostLineItems.ProjectInternalID as ProjectInternalID,
key _ProjectCostLineItems.SourceLedger, //key
key _ProjectCostLineItems.Ledger, //key
key _ProjectCostLineItems.CompanyCode, //key
key _ProjectCostLineItems.FiscalYear, //key
key _ProjectCostLineItems.AccountingDocument, //key
key _ProjectCostLineItems.FinancialPlanningReqTransSqnc, //key
key _ProjectCostLineItems.FinancialPlanningDataPacket, //key
key _ProjectCostLineItems.ActualPlanJournalEntryItem,
_ProjectCostLineItems.Project,
@Semantics.text: true
_ProjectCostLineItems.ProjectDescription,
_ProjectCostLineItems.ProcessingStatus,
_ProjectCostLineItems.ProjectProfileCode,
_ProjectCostLineItems.PlannedStartDate,
_ProjectCostLineItems.PlannedEndDate,
_ProjectCostLineItems.ProjectSummaryTaskUUID,
_ProjectCostLineItems.ProjectCategory,
_ProjectCostLineItems.ProfitCenter,
_ProjectCostLineItems.ProjectType,
//Availability Control Profile related fields
_ProjectCostLineItems.ProjectObject,
_ProjectCostLineItems.AvailabilityControlIsActive,
_ProjectCostLineItems.AvailabilityControlProfile,
_ProjectCostLineItems.AvailyCtrlTimeRangeType,
_ProjectCostLineItems.BudgetCurrencyRole,
// CE2402: Required Fields for AO /s4ppm/pr1:- start
_ProjectCostLineItems.Plant,
// CE2402: Required Fields for AO /s4ppm/pr1:- end
$session.system_date as CurrentDate,
_ProjectCostLineItems.ControllingArea as ControllingArea,
_ProjectCostLineItems.FunctionalArea as FunctionalArea,
_ProjectCostLineItems.ResponsibleCostCenter as ResponsibleCostCenter,
//Project Cost Fields
_ProjectCostLineItems.PlanningCategory,
_ProjectCostLineItems.SemanticTag,
_ProjectCostLineItems.PostingDate,
_ProjectCostLineItems.CreationDate,
// _ProjectCostLineItems.GLAccountHierarchy,
_ProjPredict.CreationDate as PredictedCostCreationDate, //Available since CE1908
'00000000' as PredictedCostPostingDate, // Added as Metadata extension true annotation doesnt allow removal of fields
_ProjectSummary.ProcessingStatus as ProjectProcessingStatus,
_ProjectCostLineItems.IsSettled as IsSettled,
//Global Currency Related fields
@Semantics.currencyCode:true
_ProjectCostLineItems.GlobalCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
_ProjectCostLineItems.AmountInGlobalCurrency,
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
@DefaultAggregation: #SUM
ActualAmountInGlobalCurrency,
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
@DefaultAggregation: #SUM
CmtmtCostInGlobalCurrency,
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
@DefaultAggregation: #SUM
case
when (_ProjectCostLineItems.PlanningCategory = 'CMTMT' or _ProjectCostLineItems.PlanningCategory = 'ACT01')
then _ProjectCostLineItems.AmountInGlobalCurrency
else 0
end as ActlCmtmtCumltvAmtInGlobCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
case
when _ProjectCostLineItems.PlanningCategory = 'PREDICT01'
then _ProjectCostLineItems.AmountInGlobalCurrency
else 0
end as PredictedAmtInGlobalCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
case
when _ProjectCostLineItems.PlanningCategory = 'PREDICT02'
then _ProjectCostLineItems.AmountInGlobalCurrency
else 0
end as LowerPredictionAmtInGlobCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
case
when _ProjectCostLineItems.PlanningCategory = 'PREDICT03'
then _ProjectCostLineItems.AmountInGlobalCurrency
else 0
end as UpperPredictionAmtInGlobCrcy,
@Semantics.currencyCode: true
ControllingObjectCurrency,
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'ControllingObjectCurrency'
AmountInObjectCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'ControllingObjectCurrency'} }
_ProjectCostLineItems.ActlCostInProjCrcy,
@Semantics: { amount : {currencyCode: 'ControllingObjectCurrency'} }
@DefaultAggregation: #SUM
CmtmtCostInProjCrcy,
@Semantics.amount.currencyCode: 'ControllingObjectCurrency'
@DefaultAggregation: #SUM
case
when ( (_ProjectCostLineItems.PlanningCategory = 'CMTMT' or _ProjectCostLineItems.PlanningCategory = 'ACT01') )
then _ProjectCostLineItems.AmountInObjectCurrency
else 0
end as AssgdValForBudgetInProjCrcy
}
where
//IsSettled = 'X' and ProjectCategory = '6'
Project != ''
and ProjectInternalID != '00000000'
union all select from //P_ProjectsWithProjCat6 as _proj
I_EnterpriseProject as _proj
inner join I_ProjectBasicData as _projBasic on _projBasic.ProjectInternalID = _proj.ProjectInternalID
association [0..1] to R_EnterpriseProject as _ProjectSummary on _ProjectSummary.ProjectInternalID = _proj.ProjectInternalID
association [0..1] to I_ControllingArea as _ControllingArea on _proj.ControllingArea = _ControllingArea.ControllingArea
association [0..1] to P_ProjectLastPredictedOn as _ProjPredict on $projection.ProjectInternalID = _ProjPredict.ProjectInternalID
{
key _proj.ProjectUUID,
key _proj.ProjectInternalID,
key '' as SourceLedger, //key
key '' as Ledger, //key
key _proj.CompanyCode, //key
key cast('' as fis_gjahr ) as FiscalYear,
key '' as AccountingDocument,
key cast('' as fis_fp_request_tsn) as FinancialPlanningReqTransSqnc,
key cast('' as fis_fp_data_packet) as FinancialPlanningDataPacket,
key '' as ActualPlanJournalEntryItem,
_projBasic.ProjectExternalID as Project, //To fix Masked project issues _proj.Project,
@Semantics.text: true
_projBasic.ProjectDescription, //_proj.ProjectDescription,
_proj.ProcessingStatus,
_projBasic.ProjectProfileCode, //_proj.ProjectProfileCode,
_projBasic.PlannedStartDate, //_proj.PlannedStartDate, Message fix: 2080309974
_projBasic.PlannedEndDate, //_proj.PlannedEndDate,
_proj.ProjectSummaryTaskUUID,
_ProjectSummary.ProjectCategory as ProjectCategory,
_projBasic.ProfitCenter, //_proj.ProfitCenter,
_proj.EnterpriseProjectType as ProjectType,
//Availability Control Profile related fields
_projBasic.ProjectObject,
_projBasic.AvailabilityControlIsActive, //_proj.AvailabilityControlIsActive,
_projBasic.AvailabilityControlProfile, //_proj.AvailabilityControlProfile,
_projBasic._AvailabilityCtrlProfile.AvailyCtrlTimeRangeType,
_projBasic._AvailabilityCtrlProfile.BudgetCurrencyRole,
// CE2402: Required Fields for AO /s4ppm/pr1:- start
_projBasic.Plant,
// CE2402: Required Fields for AO /s4ppm/pr1:- end
$session.system_date as CurrentDate,
_proj.ControllingArea as ControllingArea,
_proj.FunctionalArea as FunctionalArea,
_proj.ResponsibleCostCenter as ResponsibleCostCenter,
// Project Cost Fields
'' as PlanningCategory,
'' as SemanticTag, // _SemTagGL.SemanticTag,
'' as PostingDate,
'' as CreationDate,
// '' as GLAccountHierarchy, // _SemTagGL.GLAccountHierarchy,
_ProjPredict.CreationDate as PredictedCostCreationDate, //Available since CE1908
'00000000' as PredictedCostPostingDate, // Added as Metadata extension true annotation doesnt allow removal of fields
_ProjectSummary.ProcessingStatus as ProjectProcessingStatus,
'X' as IsSettled,
//Global Currency Related fields
@Semantics.currencyCode:true
_ControllingArea.ControllingAreaCurrency as GlobalCurrency, //_proj.Currency
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
0 as AmountInGlobalCurrency,
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
@DefaultAggregation: #SUM
0 as ActualAmountInGlobalCurrency,
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
@DefaultAggregation: #SUM
0 as CmtmtCostInGlobalCurrency,
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
@DefaultAggregation: #SUM
0 as ActlCmtmtCumltvAmtInGlobCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
0 as PredictedAmtInGlobalCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
0 as LowerPredictionAmtInGlobCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
0 as UpperPredictionAmtInGlobCrcy,
@Semantics.currencyCode: true
_projBasic.ProjectCurrency as ControllingObjectCurrency, //cast('' as fis_curr2)
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'ControllingObjectCurrency'
0 as AmountInObjectCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'ControllingObjectCurrency'} }
0 as ActlCostInProjCrcy,
@Semantics: { amount : {currencyCode: 'ControllingObjectCurrency'} }
@DefaultAggregation: #SUM
0 as CmtmtCostInProjCrcy,
@Semantics.amount.currencyCode: 'ControllingObjectCurrency'
@DefaultAggregation: #SUM
0 as AssgdValForBudgetInProjCrcy
}
where
_ProjectSummary.ProjectCategory = '6'
and _proj.Project != ''
and _proj.ProjectInternalID != '00000000'