P_RAChangeDocumentItem

@AccessControl.authorizationCheck: #NOT_REQUIRED
@AccessControl.privilegedAssociations: [ '_CreatedByUser' ]

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

define view entity 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 preserving type)
      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 preserving type)
      end                                                as ChangeDocPreviousFieldValue,

      @ObjectModel.foreignKey.association: '_CreatedByUser'
      @Semantics.user.createdBy: true
      I_ChangeDocumentItem._ChangeDocument.CreatedByUser as CreatedByUser,

      @Semantics.systemDate.createdAt: true
      I_ChangeDocumentItem._ChangeDocument.CreationDate,

      I_ChangeDocumentItem._ChangeDocument.CreationTime,

      @ObjectModel.foreignKey.association: '_RAChangeTransactionCode'
      I_ChangeDocumentItem._ChangeDocument.ChangeTransactionCode,

      // Is Manually Changed

      case
        when I_ChangeDocumentItem._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