P_RAChangeDocumentItem

@AbapCatalog.sqlViewName: 'PRACHGDOCITEM'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AccessControl.privilegedAssociations: '_CreatedByUser'

@ClientHandling.algorithm: #SESSION_VARIABLE

@VDM.viewType: #COMPOSITE
@VDM.private:true

define view P_RAChangeDocumentItem as select from I_ChangeDocumentItem
 
    association [1..1] to P_RAContractForChangeItem             as _Contract 
                       on _Contract.ChangeDocObject             = $projection.ChangeDocObject                 
                       
    association [0..1] to I_RAPerformanceObligation             as _POB 
                       on _POB.PerformanceObligation            = $projection.PerformanceObligation                                                                                      
                       
    association [1..1] to I_RAChangeDocFieldName                as _FieldName 
                       on  _FieldName.DatabaseTable             = $projection.DatabaseTable
                       and _FieldName.DatabaseTableField        = $projection.ChangeDocDatabaseTableField
                       and _FieldName.Language                  = $session.system_language 
                                                        
    association [1..1] to I_RAChangeDocTableName                as _TableName 
                       on  _TableName.DatabaseTable             = $projection.DatabaseTable
                       and _TableName.Language                  = $session.system_language 
                                        
    association [1..1] to I_User                                as _CreatedByUser 
                       on _CreatedByUser.UserID                 = $projection.CreatedByUser
                       
    association [1..1] to I_RAChangeTransactionCode             as _RAChangeTransactionCode
                       on _RAChangeTransactionCode.ChangeTransactionCode = $projection.changetransactioncode                       
{   
    key ChangeDocObject,
    
    key ChangeDocObjectClass,
     
    key ChangeDocument,
    
        @ObjectModel.foreignKey.association: '_TableName'        
    key DatabaseTable,
    
    key ChangeDocTableKey,
    
        @ObjectModel.foreignKey.association: '_FieldName'
    key ChangeDocDatabaseTableField,
    
    key ChangeDocItemChangeType,
    
        _TableName.DatabaseTableText,  
        
        _FieldName.DatabaseTableFieldText,
                
        ChangeDocPreviousUnit,                
        ChangeDocNewUnit,
        ChangeDocPreviousCurrency,
        ChangeDocNewCurrency,
        
        // join new field value with its currency or unit

        case
          when ChangeDocNewCurrency   is not initial or
               ChangeDocNewUnit       is not initial
            then 
              cast( concat_with_space(ChangeDocNewFieldValue, concat(ChangeDocNewCurrency, ChangeDocNewUnit ), 1 ) as changedocnewfieldvalue )
          else
              cast( ChangeDocNewFieldValue as changedocnewfieldvalue )
        end as ChangeDocNewFieldValue,
        
        // join old field value with its currency or unit

        case
          when ChangeDocPreviousCurrency   is not initial or
               ChangeDocPreviousUnit       is not initial
            then 
              cast( concat_with_space(ChangeDocPreviousFieldValue, concat(ChangeDocPreviousCurrency, ChangeDocPreviousUnit ), 1 ) as changedocpreviousfieldvalue )
          else
              cast( ChangeDocPreviousFieldValue as changedocpreviousfieldvalue )
        end as ChangeDocPreviousFieldValue,      
        
        @Semantics.user.createdBy: true
        @ObjectModel.foreignKey.association: '_CreatedByUser'
        _ChangeDocument.CreatedByUser as CreatedByUser,        
        
        @Semantics.systemDate.createdAt: true
        _ChangeDocument.CreationDate,
        
        _ChangeDocument.CreationTime,
        
        @ObjectModel.foreignKey.association: '_RAChangeTransactionCode'
        _ChangeDocument.ChangeTransactionCode,        
        
        // Is Manually Changed

        case 
          when _ChangeDocument.ChangeTransactionCode like 'FARR_MANUAL_%'
            //or _ChangeDocument.ChangeTransactionCode = 'FARR_MANUAL_FULFILL'

            then cast('X' as farr_manual_changed_cds preserving type)
          else cast(' ' as farr_manual_changed_cds preserving type)
        end as RAContractIsManuallyChanged,        
        
        // select POB ID from ChangeDocTableKey

        @ObjectModel.foreignKey.association: '_POB'   
        case
            when DatabaseTable = 'FARR_D_POB' then cast(SUBSTRING(ChangeDocTableKey, 4, 16) as farr_cds_pob_id preserving type)            
        end as PerformanceObligation,
        
        _TableName,
        _FieldName,
        _CreatedByUser,
        _Contract,
        _POB,
        _RAChangeTransactionCode
        
} 
where   ChangeDocObjectClass = 'FARR_CONTRACT'        // Only select Revenue Accounting change 

  and (    DatabaseTable = 'FARR_D_CONTRACT'
        or DatabaseTable = 'FARR_D_POB'
        or DatabaseTable = 'FARR_D_FULFILLMT' )
   and  ChangeDocItemChangeType = 'U';                // history with updated flag