I_MaterialMigrationVH

@AbapCatalog.sqlViewName: 'IMATERIALLMIGRVH'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey:true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Material Migration'

@VDM.viewType: #COMPOSITE

@ObjectModel.dataCategory: #VALUE_HELP
@ObjectModel.representativeKey: 'ChmlCmplncInfoUUID'

// Client handling by session

@ClientHandling.algorithm: #SESSION_VARIABLE

// used for BOBF Master Data Object

@ObjectModel.usageType:{ serviceQuality: #C,        // < 15 msec

                         sizeCategory:  #L,         // < 10.000.000

                         dataClass: #MASTER }

define view I_MaterialMigrationVH
  as select from           I_ChmlCmplncInfo             as ChemicalComplianceInfo //I_ChmlCmplncInfo (orig C_ChmlCmplncPrimMatl)


  --Is Researched DTEL Text
    left outer to one join I_ChmlCmplncIsResearchedText as ProdIsResearchedText on ProdIsResearchedText.Language = $session.system_language

  --Join data from primary material assignment
    left outer to one join I_ChmlCmplncMatlAssgmt       as PrimaryMaterial      on  ChemicalComplianceInfo.ChmlCmplncInfoUUID = PrimaryMaterial.ChmlCmplncInfoUUID
                                                                                and PrimaryMaterial.MatlAssgmtIsPrimary       = 'X'

  --Join data from product name
    left outer to one join I_ProductText                as MaterialData         on  PrimaryMaterial.Material = MaterialData.Product
                                                                                and MaterialData.Language    = $session.system_language

  association to I_ChmlCmplncInfo as _I_ChmlCmplncInfo on ChemicalComplianceInfo.ChmlCmplncInfoUUID = _I_ChmlCmplncInfo.ChmlCmplncInfoUUID
{

  key ChemicalComplianceInfo.ChmlCmplncInfoUUID,
      _I_ChmlCmplncInfo.Specification,

      case
        when ChemicalComplianceInfo.ChmlCmplncProdIsResearched = 'X' then
         cast( concat_with_space( concat_with_space( coalesce( MaterialData.ProductName, ChemicalComplianceInfo.ChmlCmplncInternalName ), '-', 2 ),
               ProdIsResearchedText.ChmlCmplncRnDSampleText, 2 )
               as ehfnd_cci_product_name )
        when ChemicalComplianceInfo.ChmlCmplncProdIsResearched = '' then
          cast( coalesce( MaterialData.ProductName, ChemicalComplianceInfo.ChmlCmplncInternalName ) as ehfnd_cci_product_name )
        when  PrimaryMaterial.Material is not initial or ChemicalComplianceInfo.Specification is not initial then
          coalesce( PrimaryMaterial.Material, ChemicalComplianceInfo.Specification )
        else BINTOHEX( ChemicalComplianceInfo.ChmlCmplncInfoUUID )
      end as MaterialName
}