I_TechnicalObjectChgHistory

@Metadata.ignorePropagatedAnnotations: true
@AccessControl.authorizationCheck: #CHECK
//@AccessControl.personalData.blocking: #('TRANSACTIONAL_DATA')

@EndUserText.label: 'Technical Object Change History'
@VDM.viewType: #COMPOSITE
@ObjectModel: {
   usageType.serviceQuality: #D,
   usageType.sizeCategory: #XL,
   usageType.dataClass: #MIXED
   }


define view entity I_TechnicalObjectChgHistory
  as select from I_ChangeDocumentItem as ChangeDocumentItem

{

  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 charcdescription  )                 as CharcDescription,
             cast ('' as klasse_d)                            as Class,
             cast('' as j_txt30 )                             as StatusName,
             cast( '' as j_stsma )                            as StatusProfile,
             @Semantics.booleanIndicator
             cast('' as j_inact)                              as StatusIsInactive


}
where
  (
       ChangeDocumentItem.ChangeDocObjectClass =  'EQUI'
    or ChangeDocumentItem.ChangeDocObjectClass =  'IFLO'
  )
  and  ChangeDocDatabaseTableField             <> 'KEY'

union all

select from              I_StatusObjectStatusChange_2 as StatusObjectStatusChange_2
  inner join             I_TechnicalObject            as TechnicalObject on StatusObjectStatusChange_2.StatusObject = TechnicalObject.MaintObjectInternalID

  left outer to one join I_StatusObject               as StatusObject    on StatusObjectStatusChange_2.StatusObject = StatusObject.StatusObject

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          TechnicalObject.TechnicalObject                                                as ChangeDocObject,
  key          concat(StatusObjectStatusChange_2.StatusCode , StatusObjectStatusChange_2.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          StatusObjectStatusChange_2.StatusChangeOperationCode                                                      as ChangeDocItemChangeType,
  key          StatusObjectStatusChange_2.LastChangeDate                                      as CreationDate,
  key          StatusObjectStatusChange_2.LastChangeTime                                      as CreationTime,

               cast(  case when StatusObjectStatusChange_2.StatusObject like 'IE%'  then  'EQUI'
                           when StatusObjectStatusChange_2.StatusObject like 'IF%'  then  'IFLO'
                           else ''
                      end as  cdobjectcl  )                                                   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  StatusObjectStatusChange_2.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  StatusObjectStatusChange_2.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 charcdescription  )                                               as CharcDescription,
               cast ('' as klasse_d)                                                          as Class,
               _StatusCode._StatusCodeText[1:Language = $session.system_language ].StatusName,
               StatusObject.StatusProfile                                                     as StatusProfile,
               StatusObjectStatusChange_2.StatusIsInactive                                    as StatusIsInactive


}
where
     StatusObjectStatusChange_2.StatusObject like 'IE%'
  or StatusObjectStatusChange_2.StatusObject like 'IF%'

union all select from    I_TechnicalObject             as TechnicalObject
  inner join             I_ClfnInternalIdObjectMapping as ClfnInternalIdObjectMapping on TechnicalObject.TechnicalObject               = ClfnInternalIdObjectMapping.ClfnObjectID
                                                                                      and(
                                                                                        ClfnInternalIdObjectMapping.ClfnObjectTable    = 'EQUI'
                                                                                        or ClfnInternalIdObjectMapping.ClfnObjectTable = 'IFLOT'
                                                                                      )
//and  ClfnInternalIdObjectMapping.ClassType = '002' //(  left outer to one join I_ClfnObjectClass             as _ObjectClass    on _ObjectClass.ClfnObjectInternalID = _ObjectToClfnId.ClfnObjectInternalID)


  inner join             I_ChangeDocumentItem          as ChangeDocumentItem          on  ChangeDocumentItem.ChangeDocObject      = concat(
    ClfnInternalIdObjectMapping.ClfnObjectInternalID, 'O'
  )
                                                                                      and ChangeDocumentItem.ChangeDocObjectClass = 'CLASSIFY'

  left outer to one join I_ClfnClass                   as ClfnClass                   on ClfnClass.ClassInternalID = substring(
    ChangeDocumentItem.ChangeDocTableKey, 20, 10
  )

  left outer to one join I_ClfnCharcDesc               as ClfnCharcDesc               on  ClfnCharcDesc.CharcInternalID   =  substring(
    ChangeDocumentItem.ChangeDocTableKey, 19, 10
  )
                                                                                      and ClfnCharcDesc.Language          = $session.system_language
  //   and ClfnCharcDesc.TimeIntervalNumber = substring(ChangeDocumentItem.ChangeDocTableKey, 29, 3)

                                                                                      and ClfnCharcDesc.IsDeleted         <> 'X'
                                                                                      and ClfnCharcDesc.ValidityStartDate <= $session.system_date
                                                                                      and ClfnCharcDesc.ValidityEndDate   >= $session.system_date

{
  key     cast ( TechnicalObject.TechnicalObject as cdobjectv ) as ChangeDocObject,
  key     ChangeDocumentItem.ChangeDocument                     as ChangeDocument,
  key     ChangeDocumentItem.DatabaseTable,

  key     case when ChangeDocumentItem.DatabaseTable = 'ABKSSK' and ChangeDocumentItem.ChangeDocDatabaseTableField = 'KEY'
          then 'CLAS'
          when ChangeDocumentItem.DatabaseTable = 'ABAUSP' and ChangeDocumentItem.ChangeDocDatabaseTableField = 'KEY'
          then 'ATINN'
          else ChangeDocumentItem.ChangeDocDatabaseTableField
          end                                                   as ChangeDocDatabaseTableField,

  key     ChangeDocumentItem.ChangeDocItemChangeType,
  key     ChangeDocumentItem._ChangeDocument.CreationDate       as CreationDate,
  key     ChangeDocumentItem._ChangeDocument.CreationTime       as CreationTime,
          ChangeDocumentItem.ChangeDocObjectClass               as ChangeDocObjectClass,

          case when ChangeDocumentItem.DatabaseTable = 'ABKSSK' and ChangeDocumentItem.ChangeDocItemChangeType = 'I' then ClfnClass.Class
          //   when ChangeDocumentItem.DatabaseTable = 'ABAUSP' and

              else ChangeDocumentItem.ChangeDocNewFieldValue
              end                                               as ChangeDocNewFieldValue,

          case when ChangeDocumentItem.DatabaseTable = 'ABKSSK' and ChangeDocumentItem.ChangeDocItemChangeType = 'D' then ClfnClass.Class
              else ChangeDocumentItem.ChangeDocPreviousFieldValue
              end                                               as ChangeDocPreviousFieldValue,
          ChangeDocumentItem.ChangeDocTableKey,

          '3'                                                   as MaintObjectChangeTypeCode, //Attribute Value Change


          ChangeDocumentItem._ChangeDocument.CreatedByUser      as CreatedByUser,


          ClfnCharcDesc.CharcDescription                        as CharcDescription,
          ClfnClass.Class,
          cast('' as j_txt30 )                                  as StatusName,
          cast( '' as j_stsma )                                 as StatusProfile,
          cast('' as j_inact)                                   as StatusIsInactive

}
where
  ChangeDocumentItem.ChangeDocDatabaseTableField <> 'ATCOD'