P_SubstanceMigration

@AbapCatalog.sqlViewName: 'PSUBMIG'
@AbapCatalog.compiler.compareFilter: true

@ClientHandling.algorithm: #SESSION_VARIABLE

@AccessControl.authorizationCheck: #NOT_ALLOWED

@ObjectModel:
{
  usageType:
  {
    dataClass: #MASTER,
    sizeCategory: #M,
    serviceQuality: #B
  }
}  

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

-- View for the silent data migration for properties of Substance objects
-- It selects all Substance objects that do not have the necessary property root objects
define view P_SubstanceMigration

  -- Part One: Missing physical-chemical properties
  as select from I_Substance

    left outer to one join I_PhysicalChemicalProperty as PhysicalChemicalProperty on I_Substance.SubstanceUUID = PhysicalChemicalProperty.SubstanceUUID
{
  I_Substance.mandt,
  I_Substance.SubstanceUUID,
  I_Substance.SubstanceInternalName,
  '00'     as StatusField
}
where
       PhysicalChemicalProperty.PhysChmlPrptyUUID      is null

-- Part Two: Missing properties for ecotoxicological information
union select from I_Substance

    left outer to one join I_PCEctxProperty as PCEctxProperty on I_Substance.SubstanceUUID = PCEctxProperty.SubstanceUUID
{
  I_Substance.mandt,
  I_Substance.SubstanceUUID,
  I_Substance.SubstanceInternalName,
  '00'     as StatusField
}
where
       PCEctxProperty.PCEctxPrptyUUID     is null