I_ChgRecdRefBOMItmDataMigrtn

@AbapCatalog.sqlViewName: 'ICHGRECREFBITMDM'
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType: {serviceQuality: #C,
                         dataClass: #MIXED,
                         sizeCategory: #L}
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #NOT_REQUIRED
@AbapCatalog.preserveKey: true
@EndUserText.label: 'Change Record Reference BIT Data Mgrtn'

/*
  The three branches of the union take care of the following
   1) Records with existing reference which has not been migrated yet.
   2) Records with existing references which has been migrated (status = 01).
   3) Data which represents planned records. The planned records are stored as a string and the helper CDS view
      is responsible for splitting those into data fields.
 */
define view I_ChgRecdRefBOMItmDataMigrtn
  as select from P_ChgRecdRefBITBfrDataMigrtn as beforeDataMigrtn
    inner join   R_ChgRecdDataMigrationStatus as tf on(
      tf.ChgRecdDataMigrationPhase                                                 = 'X'
    )
                                                    or(
                                                      tf.ChgRecdDataMigrationPhase = 'R'
                                                    )
{
  key beforeDataMigrtn.ChangeRecordReferenceUUID,
      beforeDataMigrtn.Material,
      beforeDataMigrtn.Plant,
      beforeDataMigrtn.BillOfMaterialVariantUsage,
      beforeDataMigrtn.BillOfMaterialVariant,
      beforeDataMigrtn.BillOfMaterialItemUUID,
      beforeDataMigrtn.BillOfMaterialItemNumber,
      beforeDataMigrtn.BillOfMaterialComponent,
      beforeDataMigrtn.BillOfMaterial,
      beforeDataMigrtn.ChangeRecord,
      beforeDataMigrtn.ChangeRecordUUID,
      beforeDataMigrtn.ChangeRecordReferenceType,
      beforeDataMigrtn.ChangeRecordReferenceSubtype,
      beforeDataMigrtn.ChangeRecordReference,
      beforeDataMigrtn.ParentChangeRecordRefUUID,
      beforeDataMigrtn.IsMainReference,
      beforeDataMigrtn.ChgRecRefInternalKey1,
      beforeDataMigrtn.ChgRecRefInternalKey2,
      beforeDataMigrtn.ChgRecRefInternalKey3,
      beforeDataMigrtn.ChgRecRefInternalKey4,
      //beforeDataMigrtn.ChgRecRefInternalGUID,

      beforeDataMigrtn.CreatedByUser,
      beforeDataMigrtn.CreationDateTime,
      beforeDataMigrtn.LastChangedByUser,
      beforeDataMigrtn.LastChangeDateTime,
      beforeDataMigrtn.ChangeRecordVirtualRefInd,
      beforeDataMigrtn.ChangeRecordItemRelevance,
      beforeDataMigrtn.ChgRecdDataMigrationStatus,
      beforeDataMigrtn.ChangeRecordItemProcgStatus,
      beforeDataMigrtn.ChgRecdItmTgtSts,
      beforeDataMigrtn.ChgRecdItmDSgntr,
      beforeDataMigrtn.ChgRecdReasonForChange,
      beforeDataMigrtn.ExpdCompltnDte,
      beforeDataMigrtn.ChgRecdAuthznGrpText,
      beforeDataMigrtn.ChgRecdNmbrChgSts,
      beforeDataMigrtn.ChangeNumber,


      /* Associations */
      beforeDataMigrtn._ChangeRecordBsc
}
where
  (
    (
         tf.ChgRecdDataMigrationPhase                = 'X'
    )
    or(
         beforeDataMigrtn.ChgRecdDataMigrationStatus is initial
      or beforeDataMigrtn.ChgRecdDataMigrationStatus is null
    )
  )
  and    beforeDataMigrtn.ChangeRecordVirtualRefInd  = ''

union all select from P_ChgRecdRefBITAfterDataMigrtn as afterDataMigrtn
  inner join          R_ChgRecdDataMigrationStatus   as tf on(
    tf.ChgRecdDataMigrationPhase                                                          = 'F'
  )
                                                           or(
                                                             tf.ChgRecdDataMigrationPhase = 'R'
                                                           )
{
  key afterDataMigrtn.ChangeRecordReferenceUUID,
      afterDataMigrtn.Material,
      afterDataMigrtn.Plant,
      afterDataMigrtn.BillOfMaterialVariantUsage,
      afterDataMigrtn.BillOfMaterialVariant,
      afterDataMigrtn.BillOfMaterialItemUUID,
      afterDataMigrtn.BillOfMaterialItemNumber,
      afterDataMigrtn.BillOfMaterialComponent,
      afterDataMigrtn.BillOfMaterial,
      afterDataMigrtn.ChangeRecord,
      afterDataMigrtn.ChangeRecordUUID,
      afterDataMigrtn.ChangeRecordReferenceType,
      afterDataMigrtn.ChangeRecordReferenceSubtype,
      afterDataMigrtn.ChangeRecordReference,
      afterDataMigrtn.ParentChangeRecordRefUUID,
      afterDataMigrtn.IsMainReference,
      afterDataMigrtn.ChgRecRefInternalKey1,
      afterDataMigrtn.ChgRecRefInternalKey2,
      afterDataMigrtn.ChgRecRefInternalKey3,
      afterDataMigrtn.ChgRecRefInternalKey4,
      //afterDataMigrtn.ChgRecRefInternalGUID,

      afterDataMigrtn.CreatedByUser,
      afterDataMigrtn.CreationDateTime,
      afterDataMigrtn.LastChangedByUser,
      afterDataMigrtn.LastChangeDateTime,
      afterDataMigrtn.ChangeRecordVirtualRefInd,
      afterDataMigrtn.ChangeRecordItemRelevance,
      afterDataMigrtn.ChgRecdDataMigrationStatus,
      afterDataMigrtn.ChangeRecordItemProcgStatus,
      afterDataMigrtn.ChgRecdItmTgtSts,
      afterDataMigrtn.ChgRecdItmDSgntr,
      afterDataMigrtn.ChgRecdReasonForChange,
      afterDataMigrtn.ExpdCompltnDte,
      afterDataMigrtn.ChgRecdAuthznGrpText,
      afterDataMigrtn.ChgRecdNmbrChgSts,
      afterDataMigrtn.ChangeNumber,
      /* Associations */
      afterDataMigrtn._ChangeRecordBsc
}
where
  (
    (
      tf.ChgRecdDataMigrationPhase               = 'F'
    )
    or(
      afterDataMigrtn.ChgRecdDataMigrationStatus = '01'
    )
  )
  and afterDataMigrtn.ChangeRecordVirtualRefInd  = ''
union all select from P_ChgRecdRefBOMItmVirt as virtual
{
  key    virtual.ChangeRecordReferenceUUID,
  virtual.Material,
  virtual.Plant,
  virtual.BillOfMaterialVariantUsage,
  virtual.BillOfMaterialVariant,
  virtual.BillOfMaterialItemUUID,
  virtual.BillOfMaterialItemNumber,
  virtual.BillOfMaterialComponent,
  virtual.BillOfMaterial,
  virtual.ChangeRecord,
  virtual.ChangeRecordUUID,
  virtual.ChangeRecordReferenceType,
  virtual.ChangeRecordReferenceSubtype,
  virtual.ChangeRecordReference,
  virtual.ParentChangeRecordRefUUID,
  virtual.IsMainReference,
  virtual.ChgRecRefInternalKey1,
  virtual.ChgRecRefInternalKey2,
  virtual.ChgRecRefInternalKey3,
  virtual.ChgRecRefInternalKey4,
         // virtual.ChgRecRefInternalGUID,

  virtual.CreatedByUser,
  virtual.CreationDateTime,
  virtual.LastChangedByUser,
  virtual.LastChangeDateTime,
  virtual.ChangeRecordVirtualRefInd,
  virtual.ChangeRecordItemRelevance,
  virtual.ChgRecdDataMigrationStatus,
  virtual.ChangeRecordItemProcgStatus,
  virtual.ChgRecdItmTgtSts,
  virtual.ChgRecdItmDSgntr,
  virtual.ChgRecdReasonForChange,
  virtual.ExpdCompltnDte,
  virtual.ChgRecdAuthznGrpText,
  virtual.ChgRecdNmbrChgSts,
  virtual.ChangeNumber,

  virtual._ChangeRecordBsc
}
where
  ChangeRecordVirtualRefInd = 'X'