P_PPM_PrjActCstLineItemsSemTag

//@EndUserText.label: 'Planned Project Costs per month by SemTag'


@AbapCatalog: {
    sqlViewName: 'PPMPRJBYACTCSTS',
    compiler.compareFilter: true,
    dataMaintenance: #RESTRICTED
}

@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #NOT_REQUIRED

@ClientHandling.algorithm: #SESSION_VARIABLE

//@Consumption.semanticObject: 'EnterpriseProject'


@VDM: {
  private: true,
  viewType: #COMPOSITE
}


@ObjectModel: {
//   semanticKey:       [ 'Project' ],

   representativeKey: 'ProjectInternalID',
   supportedCapabilities: [ #SQL_DATA_SOURCE, #CDS_MODELING_DATA_SOURCE, #CDS_MODELING_ASSOCIATION_TARGET ],   
   usageType: {
     serviceQuality:  #D,
     dataClass:       #MIXED,
     sizeCategory:    #XL
   }
}
define view P_PPM_PrjActCstLineItemsSemTag
  as select from I_GLAccountLineItemRawData as ProjCstLineItm
// change based on cleanup of I_GLAccountLineItem 30.11.2022  

//    inner join   I_LedgerSourceLedger         as SourceLedger on   ProjCstLineItm.SourceLedger = SourceLedger.SourceLedger

    inner join   I_WBSElementBasicData        as Task         on   ProjCstLineItm.ProjectInternalID    = Task.ProjectInternalID
                                                               and ProjCstLineItm.WBSElementInternalID = Task.WBSElementInternalID
    inner join   P_PROJECTSEMTAGACC           as SemanticTag  on  SemanticTag.ControllingArea    = ProjCstLineItm.ControllingArea
                                                               and SemanticTag.HierarchyNodeVal   = ProjCstLineItm.GLAccount
                                                               and SemanticTag.HierarchyNodeClass = ProjCstLineItm.ChartOfAccounts                                                             

  association [0..1] to I_ControllingArea              as _ControllingArea               on  $projection.ControllingArea = _ControllingArea.ControllingArea
  association [0..1] to I_Ledger                       as _Ledger                        on  $projection.Ledger = _Ledger.Ledger
  association [1]    to I_CompanyCode                  as _CompanyCode                   on  $projection.CompanyCode = _CompanyCode.CompanyCode


{
  @ObjectModel.foreignKey.association: '_Ledger'  
  key  ProjCstLineItm.SourceLedger                                  as Ledger, // change based on cleanup of I_GLAccountLineItem 30.11.2022

  @ObjectModel.foreignKey.association: '_CompanyCode'
  key  ProjCstLineItm.CompanyCode,
  key  SemanticTag.SemanticTag, 
  key  ProjCstLineItm.ProjectInternalID,
  @ObjectModel.foreignKey.association: '_ControllingArea'
  key  ProjCstLineItm.ControllingArea,                
  key  ProjCstLineItm.BusinessArea,
  key  ProjCstLineItm.ProfitCenter,                   
  key  _CalendarDate.YearMonth                                     as YearMonth,
  key  ProjCstLineItm.FunctionalArea,
  key  ProjCstLineItm.Plant,
  key  Task.ResponsibleCostCenter,
  key  Task.ProjectType,
  
       @Semantics.currencyCode:true
       ProjCstLineItm.GlobalCurrency,
       @DefaultAggregation: #SUM
       @Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
       cast( sum( ProjCstLineItm.AmountInGlobalCurrency ) as /s4ppm/ovr_act_cos_in_glo_curr ) as AmountInGlobalCurrency,
       
  _CompanyCode,
  _ControllingArea,
  _Ledger,
  ProjCstLineItm._FunctionalArea

}
where ProjCstLineItm.ProjectInternalID = Task.ProjectInternalID
  and ProjCstLineItm.CompanyCode       = Task.CompanyCode
  and _Ledger.IsLeadingLedger = 'X'
  and ProjCstLineItm.IsCommitment != 'X'
/* Adjustment to have the same cost displayed by P_ProjectCostLineItems with filter IsSettled = 'X'      
   I.e. shows all costs "To Project" and exclude costs "From Project" like Settlement Cost.   
   This adjustment leads to the absence of Settlement Costs in the display. */ 
    and not (      ProjCstLineItm.ControllingDebitCreditCode = 'A' 
              or (       ProjCstLineItm.ControllingDebitCreditCode = 'S' 
                   and ( ProjCstLineItm.BusinessTransactionType = 'KOAO' or ProjCstLineItm.BusinessTransactionType = 'KOAE' ) 
                   and (     PartnerProject = ' '                    and PartnerOrder = ' '           and PartnerSalesDocument = ' ' and PartnerProjectNetwork = ' '   
                             and PartnerProjectNetworkActivity = ' ' and PartnerBusinessProcess = ' ' and PartnerCostObject = ' '
                          or PartnerProject = Project )
                 )
             )        
    and ProjCstLineItm.GlobalCurrency    <> '' 
//  and ProjCstLineItm.ProjectInternalID = Project.ProjectInternalID

group by ProjCstLineItm.SourceLedger, ProjCstLineItm.CompanyCode, SemanticTag.SemanticTag, ProjCstLineItm.ProjectInternalID, ProjCstLineItm.ControllingArea, ProjCstLineItm.BusinessArea, 
          ProjCstLineItm.ProfitCenter, ProjCstLineItm._CalendarDate.YearMonth, ProjCstLineItm.GlobalCurrency, ProjCstLineItm.FunctionalArea, ProjCstLineItm.Plant, Task.ResponsibleCostCenter, Task.ProjectType

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CALENDARDATE",
"I_GLACCOUNTLINEITEMRAWDATA",
"I_LEDGER",
"I_WBSELEMENTBASICDATA",
"P_PROJECTSEMTAGACC"
],
"ASSOCIATED":
[
"I_COMPANYCODE",
"I_CONTROLLINGAREA",
"I_FUNCTIONALAREA",
"I_LEDGER"
],
"BASE":
[
"I_GLACCOUNTLINEITEMRAWDATA"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/