I_ApplicationOverviewStstc

@AbapCatalog.sqlViewName: 'IAPPLOVWSTAT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@ClientHandling.type: #INHERITED
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.supportedCapabilities: #CDS_MODELING_DATA_SOURCE
@ObjectModel.usageType.sizeCategory: #XL
@EndUserText.label: 'Interface view for Application Document Overview Statistics'
define view I_ApplicationOverviewStstc
 as select distinct from   I_ApplicationDocLatestVersion as LatestAppVersion


// Application Doc Data for which contracts have been assigned

  inner join    I_ACMApplDocItem                as AppDocDetails on LatestAppVersion.ReferenceApplicationDocument   = AppDocDetails.ApplicationDocument
                                                                  and LatestAppVersion.ReferenceApplicationItem     = AppDocDetails.ReferenceApplicationItem
                                                                  and LatestAppVersion.ReferenceApplicationSide     = AppDocDetails.ReferenceApplicationSide
                                                                  and LatestAppVersion.ReferenceApplicationSubItem  = substring(AppDocDetails.ReferenceApplicationSubItem,1,6)

  inner join    I_ACMSystemStatus              as status        //Status table

                                                  on status.ApplicationStatusObject = AppDocDetails.ObjectInternalID
                                                 and status.ChangeDocumentStatusIsInactive = ''

{
  key LatestAppVersion.ReferenceApplicationDocument  as ApplicationDocument,
  key LatestAppVersion.ReferenceApplicationItem      as ReferenceApplicationItem,
  case
      when status.StatusProfileName = 'I7A04'
      then 1
      else 0
      end as ACMApplDocIsFinallyApplied,//ApplDocIsFinallyApplied,


      case
      when status.StatusProfileName = 'I7A02'
      then 1
      else 0
      end as ACMApplDocIsReadyToApply,//ApplDocIsReadyToApply,


      case
      when status.StatusProfileName = 'I7A03'
      then 1
      else 0
      end as ACMApplDocIsProvisionallyAppld //ApplDocIsProvisionallyApplied


}

where status.StatusProfileName <> 'I7A06' //To eliminate Cancelled App Docs

  and status.StatusProfileName <> 'I7A05' //To eliminate Reversed App Docs

  and status.StatusProfileName <> 'I7C01' //To eliminate Unassigned App Docs



  // Application Doc Data for which contracts have not been assigned


  union
 select distinct from  I_ACMApplDocItem          as AppDocDetails
           inner join  I_ACMSystemStatus         as status        //Status table

                                                 on status.ApplicationStatusObject = AppDocDetails.ObjectInternalID
                                                 and status.ChangeDocumentStatusIsInactive = ''
  {
  key ApplicationDocument,
  key ReferenceApplicationItem,
  case
      when status.StatusProfileName = 'I7A04'
      then 1
      else 0
      end as ACMApplDocIsFinallyApplied,//ApplDocIsFinallyApplied,


      case
      when status.StatusProfileName = 'I7A02'
      then 1
      else 0
      end as ACMApplDocIsReadyToApply,//ApplDocIsReadyToApply,


      case
      when status.StatusProfileName = 'I7A03'
      then 1
      else 0
      end as ACMApplDocIsProvisionallyAppld //ApplDocIsProvisionallyApplied


}

where status.StatusProfileName <> 'I7A04'  //To eliminate Finally Applied App Docs

  and status.StatusProfileName <> 'I7A03'  //To eliminate Provisionally Applied App Docs

  and status.StatusProfileName <> 'I7A15'  //To eliminate Return Completed App Docs