I_MaintNotificationChgHistory

@AbapCatalog.sqlViewName: 'IMNOTIFCHNHIST'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #('TRANSACTIONAL_DATA')
@EndUserText.label: 'Maint. Notification Change History'
@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE

@ObjectModel: {
   usageType.serviceQuality: #D,
   usageType.sizeCategory: #XL,
   usageType.dataClass: #MIXED
   }

define view I_MaintNotificationChgHistory
  as select from I_ChangeDocumentItem as ChangeDocumentItem

  // association [0..1] to I_PMContactCardUser as _CreatedByUserCard on $projection.CreatedByUser = _CreatedByUserCard.UserID

{

  key       ChangeDocumentItem.ChangeDocObject               as ChangeDocObject,
  key       ChangeDocumentItem.ChangeDocument                as ChangeDocument,
  key       ChangeDocumentItem.DatabaseTable,
  key       ChangeDocumentItem.ChangeDocDatabaseTableField,
  key       ChangeDocumentItem.ChangeDocItemChangeType,
  key       ChangeDocumentItem._ChangeDocument.CreationDate  as CreationDate,
  key       ChangeDocumentItem._ChangeDocument.CreationTime  as CreationTime,
            ChangeDocumentItem.ChangeDocObjectClass          as ChangeDocObjectClass,
            ChangeDocumentItem.ChangeDocNewFieldValue,
            ChangeDocumentItem.ChangeDocPreviousFieldValue,
            ChangeDocumentItem.ChangeDocTableKey,
            '3'                                              as MaintObjectChangeTypeCode, //Attribute Value Change

            ChangeDocumentItem._ChangeDocument.CreatedByUser as CreatedByUser,
            cast ('' as klasse_d)                            as Class,
            cast('' as j_txt30 )                             as StatusName,
            cast( '' as j_stsma )                            as StatusProfile,
            cast('' as j_inact)                              as StatusIsInactive

            //      _CreatedByUserCard

}
where
  (
      ChangeDocumentItem.ChangeDocObjectClass =  'MELDUNG'
  )
  and ChangeDocDatabaseTableField             <> 'KEY'

union all select from    I_StatusObjectStatusChange_2 as StatusObjectStatusChange_2
  inner join             I_MaintNotificationTechObj   as MaintNotificationTechObj on StatusObjectStatusChange_2.StatusObject = MaintNotificationTechObj.MaintNotifInternalID
  left outer to one join I_StatusObject               as StatusObject             on StatusObjectStatusChange_2.StatusObject = StatusObject.StatusObject

//association [0..1] to I_PMContactCardUser as _CreatedByUserCard on $projection.CreatedByUser = _CreatedByUserCard.UserID


association [0..1] to I_StatusCode as _StatusCode on StatusObjectStatusChange_2.StatusCode = _StatusCode.StatusCode
                                                  and(
                                                    (
                                                      _StatusCode.StatusProfile            = StatusObject.StatusProfile
                                                      and _StatusCode.IsUserStatus         = 'X'
                                                    )
                                                    or(
                                                      _StatusCode.StatusProfile            = ''
                                                      and _StatusCode.IsUserStatus         = ''
                                                    )
                                                  )

{
  key          MaintNotificationTechObj.MaintenanceNotification                               as ChangeDocObject,
  key          concat(StatusObjectStatusChange_2.StatusCode , StatusObjectStatusChangeNumber) as ChangeDocument,


  key          cast( case when StatusObjectStatusChange_2.StatusCode like 'I%' then 'TJ02'
                  when StatusObjectStatusChange_2.StatusCode like 'E%' then 'TJ30'
                   else ''
             end as  tabname )                                                                as DatabaseTable,

  key          cast( case when StatusObjectStatusChange_2.StatusCode like 'I%' then 'ISTAT'
                 when StatusObjectStatusChange_2.StatusCode like 'E%' then 'ESTAT'
                  else ''
            end as  fieldname )                                                               as ChangeDocDatabaseTableField,


  key          StatusChangeOperationCode                                                      as ChangeDocItemChangeType,
  key          StatusObjectStatusChange_2.LastChangeDate                                      as CreationDate,
  key          StatusObjectStatusChange_2.LastChangeTime                                      as CreationTime,

               'MELDUNG'                                                                      as ChangeDocObjectClass,

               cast(  case when ( _StatusCode._StatusCodeText[1:Language = $session.system_language ].StatusShortName <> ''
                           or    _StatusCode._StatusCodeText[1:Language = $session.system_language ].StatusShortName is not null )
                           and  StatusIsInactive = ''
                          then   _StatusCode._StatusCodeText[1:Language = $session.system_language ].StatusShortName
                      else   ''   //StatusObjectStatusChange_2.StatusCode

                   end  as cdfldvaln )                                                        as ChangeDocNewFieldValue,


               cast(  case when ( _StatusCode._StatusCodeText[1:Language = $session.system_language ].StatusShortName <> ''
                       or    _StatusCode._StatusCodeText[1:Language = $session.system_language ].StatusShortName is not null )
                       and  StatusIsInactive = 'X'
                      then   _StatusCode._StatusCodeText[1:Language = $session.system_language ].StatusShortName
                  else   ''       //StatusObjectStatusChange_2.StatusCode

               end  as cdfldvalo )                                                            as ChangeDocPreviousFieldValue,


               cast( '' as cdtabkey )                                                         as ChangeDocTableKey,

               case when StatusObjectStatusChange_2.StatusCode like 'I%' then  '1' //System Status Change

                     when StatusObjectStatusChange_2.StatusCode like 'E%' then '2' //User Status Change

                      else '0'
                   end                                                                        as MaintObjectChangeTypeCode,

               StatusObjectStatusChange_2.LastChangedByUser                                   as CreatedByUser,

               cast ('' as klasse_d)                                                          as Class,
               _StatusCode._StatusCodeText[1:Language = $session.system_language ].StatusName,
               StatusObject.StatusProfile                                                     as StatusProfile,
               StatusObjectStatusChange_2.StatusIsInactive                                    as StatusIsInactive

               //      _CreatedByUserCard

}
where
  StatusObjectStatusChange_2.StatusObject like 'QM%'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CHANGEDOCUMENT",
"I_CHANGEDOCUMENTITEM",
"I_MAINTNOTIFICATIONTECHOBJ",
"I_STATUSCODE",
"I_STATUSCODETEXT",
"I_STATUSOBJECT",
"I_STATUSOBJECTSTATUSCHANGE_2"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/