R_ChgRecdRefOrdBOMTP

@AccessControl.authorizationCheck: #MANDATORY
@EndUserText.label: 'Change Record Reference Object OBM'
@VDM.lifecycle.contract.type:#SAP_INTERNAL_API
@AccessControl.personalData.blocking: #BLOCKED_DATA_EXCLUDED
@ObjectModel.usageType:{
 serviceQuality: #C,
 sizeCategory: #L,
 dataClass: #MIXED
}
@Search.searchable: true
@VDM.viewType:#TRANSACTIONAL
define view entity R_ChgRecdRefOrdBOMTP
  as select from           R_ChangeRecordItem       as items
    inner join             I_ChangeRecordOrderBOM_2 as OrdBOMItem       on items.ChangeRecordReferenceUUID = OrdBOMItem.ChangeRecordReferenceUUID
    left outer to one join I_SalesOrderBOMLink      as _ReferenceOrdBOM on(
      items.ChgRecRefInternalKey1                                                                              = _ReferenceOrdBOM.BillOfMaterialCategory
      and items.ChgRecRefInternalKey2                                                                          = _ReferenceOrdBOM.BillOfMaterial
      and items.ChgRecRefInternalKey3                                                                          = _ReferenceOrdBOM.BillOfMaterialVariant
    )
                                                                        or(
                                                                          OrdBOMItem.BillOfMaterialCategory    = _ReferenceOrdBOM.BillOfMaterialCategory
                                                                          and OrdBOMItem.BillOfMaterial        = _ReferenceOrdBOM.BillOfMaterial
                                                                          and OrdBOMItem.BillOfMaterialVariant = _ReferenceOrdBOM.BillOfMaterialVariant
                                                                        )
  association [0..1] to I_SalesDocumentBasic          as _SalesDocument              on  $projection.SalesOrder = _SalesDocument.SalesDocument
  association [0..1] to I_Material                    as _Material                   on  $projection.Material = _Material.Material

  association [0..1] to I_PlantStdVH                  as _Plant                      on  $projection.Plant = _Plant.Plant

  association [0..1] to I_BillOfMaterialUsage         as _BillOfMaterialVariantUsage on  $projection.BillOfMaterialVariantUsage = _BillOfMaterialVariantUsage.BillOfMaterialVariantUsage
                                                                                     and _BillOfMaterialVariantUsage.Language   = $session.system_language
  //  association [1..1] to I_ChgRecordItemRelevanceText_2 as _ChangeRecordItemRelText    on  $projection.ChangeRecordItemRelevance = _ChangeRecordItemRelText.ChangeRecordItemRelevance

  //                                                                                      and _ChangeRecordItemRelText.Language     = $session.system_language


  association [0..1] to I_ChangeRecordItemRelevance_3 as _ChangeRecordItemRelText    on  $projection.changerecordtype          = _ChangeRecordItemRelText.ChangeRecordType
                                                                                     and $projection.ChangeRecordItemType      = _ChangeRecordItemRelText.ChangeRecordItemType
                                                                                     and $projection.ChangeRecordItemRelevance = _ChangeRecordItemRelText.ChangeRecordItemRelevance
  association [1..1] to I_ChgRecdItemProcgStatusText  as _ChangeRecordItemProcgText  on  $projection.ChgRecdNextItemProcgStatus = _ChangeRecordItemProcgText.ChangeRecordItemProcgStatus
                                                                                     and _ChangeRecordItemProcgText.Language    = $session.system_language
  association        to parent R_ChangeRecordTP       as _ChangeRecordTP             on  $projection.ChangeRecordUUID = _ChangeRecordTP.ChangeRecordUUID
{
  key items.ChangeRecordReferenceUUID,
      items.ChangeRecordUUID,
      items.ChangeRecordReferenceType,
      items.ChangeRecordItemType,
      items.ChangeRecordReferenceSubtype,
      items.ChgRecRefInternalKey1,
      items.ChgRecRefInternalKey2,
      items.ChgRecRefInternalKey3,
      items.ChgRecRefInternalKey4,
      items.ParentChangeRecordRefUUID,
      items.IsMainReference,
      items.ChangeRecordReference,
      @Semantics.user.createdBy: true
      items.CreatedByUser,
      @Semantics.systemDateTime.createdAt: true
      items.CreationDateTime,
      @Semantics.user.localInstanceLastChangedBy: true
      items.LastChangedByUser,
      @Semantics.systemDateTime.localInstanceLastChangedAt: true
      items.LastChangeDateTime,
      items.ChangeRecordVirtualRefInd,
      items.ChangeRecordItemRelevance,
      items.ChangeRecordItemProcgStatus                                   as ChangeRecordItemProcgStatus,
      items.ChgRecdNextItemProcgStatus                                    as ChgRecdNextItemProcgStatus,
      items.ChgRecdItmTgtSts,
      items.ChgRecdItmDSgntr,
      items.ChgRecdReasonForChange,
      items.ExpdCompltnDte,
      items.ChgRecdAuthznGrpText,
      items.ChgRecdNmbrChgSts,
      items.ChangeNumber,
      cast( '' as cs_versn )                                              as BillOfMaterialVersion,
      @Search.defaultSearchElement: true
      case
       when _ReferenceOrdBOM.Material is null //then cast( substring( items.ChangeRecordReference, 1, 40 ) as matnr )

        then OrdBOMItem.Material
       else cast(_ReferenceOrdBOM.Material as matnr )
       end                                                                as Material,

      case
       when _ReferenceOrdBOM.Plant is null //then  cast( substring( items.ChangeRecordReference, 41, 4 ) as werks_d )

        then OrdBOMItem.Plant
       else cast(_ReferenceOrdBOM.Plant as werks_d )
       end                                                                as Plant,

      case
       when _ReferenceOrdBOM.BillOfMaterialVariantUsage is null //then cast ( substring( items.ChangeRecordReference, 45, 1 ) as stlan )

        then OrdBOMItem.BillOfMaterialVariantUsage
       else cast(_ReferenceOrdBOM.BillOfMaterialVariantUsage as stlan )
       end                                                                as BillOfMaterialVariantUsage,

      case
       when _ReferenceOrdBOM.BillOfMaterialVariant is null //then cast( substring( items.ChangeRecordReference, 46, 2 ) as stalt )

        then OrdBOMItem.BillOfMaterialVariant
       else cast(_ReferenceOrdBOM.BillOfMaterialVariant as stalt )

       end                                                                as BillOfMaterialVariant,

      case
       when _ReferenceOrdBOM.SalesOrder is null //then cast( substring( items.ChangeRecordReference, 48, 10 ) as cs_vbeln )

        then OrdBOMItem.SalesOrder
       else cast(_ReferenceOrdBOM.SalesOrder as cs_vbeln )

       end                                                                as SalesOrder,

      case
       when _ReferenceOrdBOM.SalesOrderItem is null //then cast( ltrim ( substring( items.ChangeRecordReference, 58, 6), '0' ) as cs_vbpos )

        then OrdBOMItem.SalesOrderItem
       else cast(_ReferenceOrdBOM.SalesOrderItem as cs_vbpos )

       end                                                                as SalesOrderItem,

      _ReferenceOrdBOM.BillOfMaterialCategory,
      cast( _ReferenceOrdBOM.BillOfMaterial as cs_stlnr preserving type ) as BillOfMaterial,
      _ChangeRecordTP.ChangeRecordType,
      _ChangeRecordTP.Partner,
      _ChangeRecordTP._ChangeRecordContactPerson,
      _ChangeRecordTP.ChangeRecordLifecycleStatus,
      /* Associations */
      _ChangeRecordTP,
      _ChangeRecordItemProcgText,
      _ChangeRecordItemRelText,
      _SalesDocument,
      _Material,
      _Plant,
      _BillOfMaterialVariantUsage
}

where
     items.ChangeRecordReferenceType = 'OBM'
  or items.ChangeRecordItemType      = 'SalesBillOfMaterial'