I_ChangeRecdRefTmplRoutingTP_2

@AbapCatalog.sqlViewName: 'ICRREFTMPLRTGTP2'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AccessControl.personalData.blocking: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.preserveKey:true
@VDM.viewType: #TRANSACTIONAL
@ObjectModel.usageType: {serviceQuality: #C,
                         dataClass: #MIXED,
                         sizeCategory: #XXL}
@EndUserText.label: 'Change Record Reference Object Template Routing'
@VDM.lifecycle.contract.type:#SAP_INTERNAL_API
define view I_ChangeRecdRefTmplRoutingTP_2
  as select from           I_ChgRecdRefTmplRoutingBsc as reference_template_routing
    left outer to one join I_ProductionRoutingVersion as Routing on  reference_template_routing.ChgRecRefInternalKey1 = Routing.BillOfOperationsType
                                                                 and reference_template_routing.ChgRecRefInternalKey2 = Routing.BillOfOperationsGroup
                                                                 and reference_template_routing.ChgRecRefInternalKey3 = Routing.BillOfOperationsVariant
                                                                 and reference_template_routing.ChgRecRefInternalKey4 = Routing.BillOfOperationsVersion
  association        to parent I_ChangeRecordTP_2  as _ChangeRecordAPITP    on $projection.ChangeRecordUUID = _ChangeRecordAPITP.ChangeRecordUUID
  association [0..1] to E_ChgRecdRefTmplRoutingBsc as _TmplRoutingExtension on $projection.ChangeRecordReferenceUUID = _TmplRoutingExtension.ChangeRecordReferenceUUID

{
  key   reference_template_routing.ChangeRecordReferenceUUID,
        case
           when Routing.BillOfOperationsType is null then cast(SUBSTRING( reference_template_routing.ChangeRecordReference, 1, 1) as plnty)
           else cast(Routing.BillOfOperationsType as plnty)
        end   as BillOfOperationsType,

        case
            when Routing.BillOfOperationsGroup is null then  cast(SUBSTRING( reference_template_routing.ChangeRecordReference, 2, 9) as mpe_rtg_reft_group)
            else cast(Routing.BillOfOperationsGroup as mpe_rtg_reft_group)
        end   as ShopFloorRtgTemplateGroup,

        case
          when Routing.BillOfOperationsVariant is null then cast (SUBSTRING( reference_template_routing.ChangeRecordReference, 10, 2) as mpe_rtg_reft_variant)
          else cast(Routing.BillOfOperationsVariant as mpe_rtg_reft_variant)
         end  as ShopFloorRtgTemplateGrpVariant,

        case
          when Routing.BillOfOperationsVersion is null then cast (SUBSTRING( reference_template_routing.ChangeRecordReference, 12, 4) as mpe_rtg_reft_version)
          else cast(Routing.BillOfOperationsVersion as mpe_rtg_reft_version)
          end as ShopFloorRtgTemplateVersion,

        reference_template_routing.ChangeRecordUUID,
        reference_template_routing.ChangeRecord,
        reference_template_routing.ChangeRecordReferenceType,
        reference_template_routing.ChangeRecordReferenceSubtype,
        reference_template_routing.ChangeRecordRefObjectUUID,
        reference_template_routing.ChangeRecordReference,
        reference_template_routing.ParentChangeRecordRefUUID,
        reference_template_routing.IsMainReference,
        reference_template_routing.CreatedByUser,
        reference_template_routing.CreationDateTime,
        reference_template_routing.LastChangedByUser,
        reference_template_routing.LastChangeDateTime,
        reference_template_routing.ChangeRecordItemRelevance,
        reference_template_routing.ChangeRecordVirtualRefInd,
        Routing.Plant,
        _ChangeRecordAPITP
}
where
  (
   reference_template_routing.ChangeRecordReferenceType = 'TER'
//       Routing.BillOfOperationsType                           = 'F' //F: Task list type for Template Routing

//    or Routing.BillOfOperationsType                           is null

  )
  and (reference_template_routing.ChangeRecordReference !=''  or  Routing._BillOfOperations.ShopFloorRtgTemplateCategory = ''  )// Category = '' is category for Reference Templates

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_BILLOFOPERATIONS",
"I_CHGRECDREFTMPLROUTINGBSC",
"I_PRODUCTIONROUTINGVERSION"
],
"ASSOCIATED":
[
"E_CHGRECDREFTMPLROUTINGBSC",
"I_CHANGERECORDTP_2"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/