I_PPM_MeAsSubstituteProjects

@EndUserText.label: 'My Projects - As a Substitute'
@VDM.viewType: #COMPOSITE
@AbapCatalog.sqlViewName: 'IPPMMEASSUBST'
@AbapCatalog.compiler.compareFilter: true
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.sizeCategory:  #L
@ObjectModel.usageType.dataClass:  #MIXED

@ClientHandling.algorithm: #SESSION_VARIABLE

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

@ObjectModel.semanticKey:  [ 'Project' ]
@ObjectModel.representativeKey: 'ProjectUUID'
@ObjectModel.alternativeKey: [{
    id: 'ProjectSummaryTaskUUID',
    element: ['ProjectSummaryTaskUUID'],
    uniqueness: #UNIQUE
}]

@Metadata.allowExtensions: true
@VDM.lifecycle.contract.type:  #SAP_INTERNAL_API

define view I_PPM_MeAsSubstituteProjects
  as select from    P_PPM_ProjectList             as Project
  // next relevant milestone

    left outer join I_PPM_ProjectMilestRelNext    as MilestRelNext               on(
                    Project.ProjectUUID = MilestRelNext.ProjectUUID
                  )
    left outer join I_PPM_Task                    as MilestRelNextTask           on(
                MilestRelNextTask.TaskUUID = MilestRelNext.TaskUUID
              )
    left outer join I_PPM_Text                    as MilestRelNextTextUserLang   on (
         MilestRelNextTextUserLang.LanguageCode                                                                                   = $session.system_language
       )
                                                                                 and(
                                                                                   MilestRelNextTextUserLang.ReferencedObjectUUID = MilestRelNext.TaskUUID
                                                                                 )
//    left outer join I_PPM_Text                    as MilestRelNextTextMasterLang on (

//       MilestRelNextTextMasterLang.LanguageCode                                                                                     = Project.MasterLanguage

//     )

//                                                                                 and(

//                                                                                   MilestRelNextTextMasterLang.ReferencedObjectUUID = MilestRelNext.TaskUUID

//                                                                                 )

  // essential milestone

    left outer join I_PPM_ProjectMilestEssent     as MilestEssent                on(
                     Project.ProjectUUID = MilestEssent.ProjectUUID
                   )
    left outer join I_PPM_Task                    as MilestEssentTask            on(
                 MilestEssentTask.TaskUUID = MilestEssent.TaskUUID
               )
  // current phase

    left outer join I_PPM_ProjectPhaseCurrent     as PhaseCurrent                on(
                     Project.ProjectUUID = PhaseCurrent.ProjectUUID
                   )
    left outer join I_PPM_Task                    as PhaseCurrentTask            on(
                 PhaseCurrentTask.TaskUUID = PhaseCurrent.TaskUUID
               )
    left outer join I_PPM_Text                    as PhaseCurrentTextUserLang    on (
          PhaseCurrentTextUserLang.LanguageCode                                                                                  = $session.system_language
        )
                                                                                 and(
                                                                                   PhaseCurrentTextUserLang.ReferencedObjectUUID = PhaseCurrent.TaskUUID
                                                                                 )
//    left outer join I_PPM_Text                    as PhaseCurrentTextMasterLang  on (

//        PhaseCurrentTextMasterLang.LanguageCode                                                                                    = Project.MasterLanguage

//      )

//                                                                                 and(

//                                                                                   PhaseCurrentTextMasterLang.ReferencedObjectUUID = PhaseCurrent.TaskUUID

//                                                                                 )

    left outer join I_PPM_SingleResponsibleForObj as ProjectResponsible          on ProjectResponsible.ReferencedObjectUUID = Project.ProjectSummaryTaskUUID

  // retrieve portfolio info

    left outer join I_PPM_ItemInfo                as Item                        on(
                             Project.ProjectUUID = Item.ProjectUUID
                           )

  // project image

    left outer join P_PPM_ProjectImage            as projectImage                on projectImage.ProjectUUID = Project.ProjectUUID

{
      @Consumption.semanticObject: 'EnterpriseProject'
  key Project.ProjectUUID,

      Project.ProjectSummaryTaskUUID,

      // fields only used by navigation

      Item.PortfolioItemUUID,
      Item.PortfolioUUID,

      cast (
              case
                  when ProjectNameUserLanguage > '' then
                  ProjectNameUserLanguage
                  else Project.ProjectName
           
      end as /s4ppm/tv_project_name preserving type  )              as ProjectName,

      ProjectNameUserLanguage,

      ProjectNameMasterLanguage,

      Project.Project,

      Project.ProjectType,
      Project.ProjectTypeName,
      
      ProjectResponsible.BusinessPartnerUUID,
      ProjectResponsible.BusinessPartnerName as ProjectManager,

      cast (
          case
              when PhaseCurrentTextUserLang.ObjectName > '' then
                  PhaseCurrentTextUserLang.ObjectName
              else PhaseCurrentTask.TaskName
            
      end as /s4ppm/tv_description  preserving type )                    as CurrentPhaseName,
      
      PhaseCurrentTextUserLang.ObjectName    as CurrentPhaseNameUserLanguage,
      
      PhaseCurrentTask.TaskName              as CurrentPhaseNameMasterLanguage,
      
      PhaseCurrentTask.ExternalId            as CurrentPhase,
     
case
      
       when PhaseCurrentTask.ProjectElementDuration > 0 then
       PhaseCurrentTask.LatestFinishDate
      else
          PhaseCurrentTask.LatestFinishDate
          
      end      as CurrentPhaseEndDate,

      //note 2485200 start change

      //    case when MilestEssentTask.ProjectElementDuration > 0 then

      //        dats_add_days( tstmp_to_dats( MilestEssentTask.LatestFinishDateTime, abap_system_timezone( $session.client,'NULL' ), $session.client, 'NULL' ), -1, 'NULL')

      //    else

      //        tstmp_to_dats( MilestEssentTask.LatestFinishDateTime, abap_system_timezone( $session.client,'NULL' ), $session.client, 'NULL' )

      //    end as LaunchDate,

      @Semantics.businessDate.at: true
      case
      
        when MilestEssentTask.ProjectElementDuration = 0 then
      MilestEssentTask.LatestFinishDate
        else //this should only happen if the duration is > 0

          MilestEssentTask.LatestFinishDate
      end                                    as LaunchDate,
      //note 2485200 end change


      cast (
          case
              when MilestRelNextTextUserLang.ObjectName > '' then 
                  MilestRelNextTextUserLang.ObjectName
              else (
             
                   MilestRelNextTask.TaskName 
             
             )
      end as /s4ppm/tv_description preserving type)   as NextMilestoneName,
      
      MilestRelNextTextUserLang.ObjectName  as NextMilestoneNameUserLanguage,
      
      MilestRelNextTask.TaskName            as NextMilestoneNameMasterLang,
      
      MilestRelNextTask.ExternalId          as NextMilestone,

      //note 2485200 start change

      //     case when MilestRelNextTask.ProjectElementDuration > 0 then

      //        dats_add_days( tstmp_to_dats( MilestRelNextTask.LatestFinishDateTime, abap_system_timezone( $session.client,'NULL' ), $session.client, 'NULL' ), -1, 'NULL')

      //    else

      //        tstmp_to_dats( MilestRelNextTask.LatestFinishDateTime, abap_system_timezone( $session.client,'NULL' ), $session.client, 'NULL' )

      //    end as NextMilestoneDate,

      @Semantics.businessDate.to: true
      case
          when MilestRelNextTask.ProjectElementDuration > 0 then
       MilestRelNextTask.LatestFinishDate
         
        else 
         MilestRelNextTask.TaskName
         
      end                                    as NextMilestoneDate,
      //note 2485200 end change

      projectImage.ProjectImageURL,

      Project._AuthUser,
      Project._AuthSubst,
      Project._AuthRole,
      Project._AuthGroup,
      
      ProjectResponsible._BusinessUser  
            
}
where
  (
  // All active Status: Partially Released, Released, Released (Processing has begun), Locked, Locked (Released First)

      Project.ProjectProcessingStatus between '10' and '21'
  )
  and Project.ProjectVersionNumber    = '' // active projects do not have a version!

  and _Substitute.SubstituteUser      = $session.user