P_EngmntProjPlnAggrgnByPerd

@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog:{
    sqlViewName: 'PEPPLNDTABYPRD',
    compiler.compareFilter: true,
    preserveKey: true
}
@VDM: {
    viewType: #COMPOSITE,
    private: true,
    lifecycle.contract.type: #NONE
}
@AccessControl.authorizationCheck: #CHECK
@ObjectModel: {
    usageType: {
        sizeCategory: #XL,
        serviceQuality: #D,
        dataClass: #MIXED
    }
}
@Metadata: {
    ignorePropagatedAnnotations: true
}

define view P_EngmntProjPlnAggrgnByPerd
  as select from P_EngmntProjPlnDataByRsceType as A
    inner join   I_Ledger                      as Ledger on Ledger.IsLeadingLedger = 'X'
{
  key EngagementProject,
  key EngagementProjFinPlanUUID,
      Customer,
      EngagementProjectCategory,
      EngagementProjectReviewStatus,
      EngagementProjectStage,
      EngagementProjectType,
      EngmntProjReviewYearPeriod,
      WorkPackage,
      WorkItem,
      //@Semantics.currencyCode: true

      Currency,
      EngagementProjectResource,
      EngagementProjectResourceType,
      @Semantics.unitOfMeasure: true
      UnitOfMeasure,
      EngagementProjectServiceOrg,
      EngmntProjectDeliveryOrg,
      GLAccount, //This is added for revenue recognition to work

      '1'                                as EngmntProjPlanLineType,
      ResourceIsOfUnitWorkPackage,
      IsMyProject,
      IsUnitBased,
      ProjectManager,
      ProjectAccountant,
      ProjectController,
      Projectpartner,
      ProjectManagerName,
      ProjectAccountantName,
      ProjectControllerName,
      ProjectPartnerName,
      ProjectManagerExternalID,
      ProjectAccountantExternalID,
      ProjectControllerExternalID,
      ProjectPartnerExternalID,
      ProjectVisibility,
      ProjectProfileCode,
      EngmntProjectHeaderCompanyCode,
      EngagementProjectCostCenter,
      ProfitCenter,

      //Quantity Fields - Plan

      sum(PlanUnitQuantity)              as PlanUnitQuantity,
      sum(PlndEffortQty)                 as PlndEffortQty,
      sum(PlndEffortUpToCurPerd)         as PlndEffortUpToCurPerd,
      sum(BaselinePlndEffortQty)         as BaselinePlndEffortQty,
      sum(PlndEffortQtyUpToRevPerd)      as PlndEffortQtyUpToRevPerd,
      sum(AddlResourcePlannedQuantity)   as AddlResourcePlannedQuantity,
      sum(BaselinePlannedQuantity)       as BaselinePlannedQuantity,

      //Quantity Fields - Deviation

      sum(ActualPlanDeviationEffort)     as ActualPlanDeviationEffort,
      sum(AddlRsceActlPlanDeviationQty)  as AddlRsceActlPlanDeviationQty,
      sum(SupplyEffortQty)               as SupplyEffortQty,

      //Adjusted Quantity

      sum(AdjustedPlndEffort)            as AdjustedPlndEffort,
      sum(AdjustedOthRscePlndQty)        as AdjustedOthRscePlndQty,

      //Calculated ETC Qty for additional resource

      sum(AddlRsceCalcdEstToCmpltQty)    as AddlRsceCalcdEstToCmpltQty,

      //Planned Cost

      sum(PlannedEffortCostAmt)          as PlannedEffortCostAmt,
      sum(PlndCostAmt)                   as PlndCostAmt,
      sum(PlannedCostUpToCurPerd)        as PlannedCostUpToCurPerd,
      sum(PlndCostAmtUpToRevPerd)        as PlndCostAmtUpToRevPerd,
      sum(AddlResourcePlannedCostAmount) as AddlResourcePlannedCostAmount,

      //Baseline caost

      sum(BaselinePlndCostAmt)           as BaselinePlndCostAmt,
      sum(BaselinePlndRevnAmt)           as BaselinePlndRevnAmt,

      //PLanned Revenue

      sum(PlndRevnAmt)                   as PlndRevnAmt,
      sum(PlndRevnAmtUpToRevPerd)        as PlndRevnAmtUpToRevPerd,
      sum(PlannedRevnUpToCurPerd)        as PlannedRevnUpToCurPerd,

      //Expense Cost

      sum(PlannedExpenseCost)            as PlannedExpenseCost,
      sum(ActualPlanDeviationExpnCost)   as ActualPlanDeviationExpnCost,
      sum(PlndExpCostUpToCurPerd)        as PlndExpCostUpToCurPerd,
      sum(PlndExpenseCostUpToRevPerd)    as PlndExpenseCostUpToRevPerd,
      sum(AdjustedExpnCost)              as AdjustedExpnCost,
      sum(AdjustedPlndCost)              as AdjustedPlndCost,

      //Unit Completion Rate

      max(UnitCompltnRateUpToCurPerd)    as UnitCompltnRateUpToCurPerd,
      max(UnitCompltnRateUpToRevPerd)    as UnitCompltnRateUpToRevPerd,

      sum(ActualPlanDeviationCost)       as ActualPlanDeviationCost,

      Ledger.Ledger

      //max(EngmntProjectDeliveryOrg) as EngmntProjectDeliveryOrg


}
group by
  EngagementProject,
  EngagementProjFinPlanUUID,
  Customer,
  EngagementProjectCategory,
  EngagementProjectReviewStatus,
  EngagementProjectStage,
  EngagementProjectType,
  EngmntProjReviewYearPeriod,
  WorkPackage,
  WorkItem,
  Currency,
  EngagementProjectResource,
  EngagementProjectResourceType,
  UnitOfMeasure,
  EngagementProjectServiceOrg,
  EngmntProjectDeliveryOrg,
  GLAccount,
  ResourceIsOfUnitWorkPackage,
  IsMyProject,
  IsUnitBased,
  ProjectManager,
  ProjectAccountant,
  ProjectController,
  Projectpartner,
  ProjectManagerName,
  ProjectAccountantName,
  ProjectControllerName,
  ProjectPartnerName,
  ProjectManagerExternalID,
  ProjectAccountantExternalID,
  ProjectControllerExternalID,
  ProjectPartnerExternalID,
  ProjectVisibility,
  ProjectProfileCode,
  EngmntProjectHeaderCompanyCode,
  EngagementProjectCostCenter,
  ProfitCenter,
  Ledger.Ledger
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_LEDGER",
"P_ENGMNTPROJPLNDATABYRSCETYPE"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/