P_PROJECTFINBUDGETSMMRY

@AbapCatalog.sqlViewName: 'PPROJFBUDGETSMRY'
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #CHECK
@VDM.private:true
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.buffering.status: #NOT_ALLOWED
@Metadata.ignorePropagatedAnnotations: true
@AbapCatalog.preserveKey: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 I_PPM_ProjectSummaryTask 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,


      $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.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         I_PPM_ProjectSummaryTask 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,


       $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.ProjectProcessingStatus                        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'