I_ChgRecdRefPlngRoutingTP

@AbapCatalog.sqlViewName: 'ICRREFPLNGRTGTP'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.preserveKey:true
@VDM.viewType: #TRANSACTIONAL
@ObjectModel: { semanticKey: ['BillOfOperationsType','BillOfOperationsGroup','BillOfOperationsVariant'],
                writeDraftPersistence: 'PLMCRREFPLR_D',
                createEnabled,
                deleteEnabled,
                updateEnabled
              }
@ObjectModel.usageType: {serviceQuality: #C,
                         dataClass: #MIXED,
                         sizeCategory: #L}
@EndUserText.label: 'Chg Record Ref Obj Planning Routing TP'

define view I_ChgRecdRefPlngRoutingTP
  as select distinct from  I_ChgRecdRefPlngRoutingBsc as reference_planning_routing
    left outer to one join I_BillOfOperations         as PlanningRouting on  reference_planning_routing.ChgRecRefInternalKey1 = PlanningRouting.BillOfOperationsType
                                                                         and reference_planning_routing.ChgRecRefInternalKey2 = PlanningRouting.BillOfOperationsGroup
                                                                         and reference_planning_routing.ChgRecRefInternalKey3 = PlanningRouting.BillOfOperationsVariant

  association [1..1] to I_ChangeRecordDraftTP      as _ChangeRecord         on $projection.ChangeRecordUUID = _ChangeRecord.ChangeRecordUUID
  association [0..1] to E_ChgRecdRefPlngRoutingBsc as _PlngRoutingExtension on $projection.ChangeRecordReferenceUUID = _PlngRoutingExtension.ChangeRecordReferenceUUID
{
  key   reference_planning_routing.ChangeRecordReferenceUUID,
        @ObjectModel.readOnly: true
        case
          when PlanningRouting.BillOfOperationsType is null then cast(SUBSTRING( reference_planning_routing.ChangeRecordReference, 1, 1) as plnty)
          else cast(PlanningRouting.BillOfOperationsType as plnty)
        end  as BillOfOperationsType,

        case
          when PlanningRouting.BillOfOperationsGroup is null then  cast(SUBSTRING( reference_planning_routing.ChangeRecordReference, 2, 9) as plnnr)
          else cast(PlanningRouting.BillOfOperationsGroup as plnnr)
        end  as BillOfOperationsGroup,

        case
          when PlanningRouting.BillOfOperationsVariant is null then cast (SUBSTRING( reference_planning_routing.ChangeRecordReference, 10, 2) as plnal)
          else cast(PlanningRouting.BillOfOperationsVariant as plnal)
         end as BillOfOperationsVariant,

        reference_planning_routing.ChangeRecordUUID,
        reference_planning_routing.ChangeRecordReferenceType,
        reference_planning_routing.ChangeRecordReferenceSubtype,
        reference_planning_routing.ChangeRecordRefObjectUUID,
        reference_planning_routing.ChangeRecordReference,
        reference_planning_routing.ParentChangeRecordRefUUID,
        reference_planning_routing.IsMainReference,
        @Semantics.user.createdBy: true
        @ObjectModel.readOnly: true
        reference_planning_routing.CreatedByUser,
        reference_planning_routing.CreationDateTime,
        @Semantics.user.lastChangedBy: true
        @ObjectModel.readOnly: true
        reference_planning_routing.LastChangedByUser,
        reference_planning_routing.LastChangeDateTime,
        reference_planning_routing.ChangeRecordItemRelevance,
        reference_planning_routing.ChangeRecordVirtualRefInd,
        reference_planning_routing.ChangeRecordItemProcgStatus,
        reference_planning_routing.ChgRecdItmTgtSts,
        reference_planning_routing.ChgRecdItmDSgntr,
        reference_planning_routing. ChgRecdCurrentItemProcgStatus,
        reference_planning_routing.ChgRecdReasonForChange,
        reference_planning_routing.ExpdCompltnDte,
        reference_planning_routing.ChgRecdAuthznGrpText,
        reference_planning_routing.ChgRecdNmbrChgSts,
        reference_planning_routing.ChangeNumber,
        @ObjectModel.association.type:  [ #TO_COMPOSITION_PARENT, #TO_COMPOSITION_ROOT ]
        _ChangeRecord
}
where
     PlanningRouting.BillOfOperationsType = 'P'
  or PlanningRouting.BillOfOperationsType is null