I_SWI_WIIinvUsers

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Work Items with Invalid Users'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
  serviceQuality: #X,
  sizeCategory: #S,
  dataClass: #MIXED
}
define view entity I_SWI_WIIinvUsers
  as select from    swwuserwi
    inner join      usr02  on swwuserwi.user_id = usr02.bname
    left outer join usracl on usr02.bname = usracl.bname
{
  key swwuserwi.wi_id                                             as WorkItem,
  key swwuserwi.user_id                                           as UserId,
      cast (case
             when usr02.gltgv > $session.system_date or usr02.gltgb < $session.system_date and usr02.gltgb > '00000000' then 1
             when usracl.pname is null and usr02.uflag = 128 then 2
             when usr02.uflag < 128 then 2
             else 5
            end  as sww_user_invalidation_reason preserving type) as InvalidationReason
}
where
  (
    (
      (
            usr02.uflag      > 0
        and usr02.uflag      < 128
      ) //user is locked

      or(
            usracl.pname     is null
        and usr02.uflag      = 128
      ) //user is locked but SNC logon is possible

    )
    or(
            usr02.gltgv      > $session.system_date
    ) //user is not valid yet

    or(
            usr02.gltgb      < $session.system_date
      and   usr02.gltgb      > '00000000'
    ) //user is outdated

  )
  and       swwuserwi.no_sel = ''

union select from swwuserwi
  left outer join usr02 on swwuserwi.user_id = usr02.bname
{
  key swwuserwi.wi_id                                         as WorkItem,
  key swwuserwi.user_id                                       as UserId,
      cast(3 as sww_user_invalidation_reason preserving type) as InvalidationReason
}
where
      swwuserwi.user_id is not initial
  and swwuserwi.no_sel  = ''
  and usr02.bname       is null //user is deleted

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"SWWUSERWI",
"USR02",
"USRACL"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/