C_ChmlCmpstnSubstanceVH

@EndUserText.label: 'Value Help for Substance in Composition'
@AbapCatalog.sqlViewName: 'CMCMPSTNSUBVH'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #MANDATORY
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #CONSUMPTION
@ObjectModel.usageType: {
     serviceQuality:  #C,
     dataClass:       #MIXED,
     sizeCategory:    #L
   }
@ObjectModel.representativeKey: 'SubstanceUUID'
@ObjectModel.semanticKey:  [ 'SubstanceName' ]
@ObjectModel.dataCategory: #VALUE_HELP
@Metadata.ignorePropagatedAnnotations: true

@UI:
{
  presentationVariant:
  [{
    sortOrder:
    [{
        by: 'SubstanceName',
        direction: 'ASC'
    }],
   requestAtLeast: [ 'SubstanceUUID','SubstanceName' ]
  }]
}

@Search.searchable: true

@Consumption.dbHints: ['USE_HEX_PLAN']

/*----------------------------------------------------------------------------------------------------------------------
    Value Help view to find and assign substances that are not already used in the (draft) composition
------------------------------------------------------------------------------------------------------------------------*/

define view C_ChmlCmpstnSubstanceVH
  with parameters
    P_ChmlCompositionUUID : sysuuid_x
  -- select Substances
  as select from            I_SubstanceTP                  as _Substance

  --Join data with draft compositions
    inner join              I_ChmlCompositionDraft         as _ChmlCompositionDraft    on _ChmlCompositionDraft.ChmlCompositionUUID = $parameters.P_ChmlCompositionUUID

  --Join data of draft components of chemical composition that are already assigned
    left outer to many join I_SubstBsdCmpstnComponentDraft as _SubstBsdCmpstnCompDraft on  _Substance.SubstanceUUID                     = _SubstBsdCmpstnCompDraft.SubstanceUUID
                                                                                       and _SubstBsdCmpstnCompDraft.ChmlCompositionUUID = $parameters.P_ChmlCompositionUUID

  association [0..*] to I_SubstanceText as _SubstanceText on $projection.SubstanceUUID = _SubstanceText.SubstanceUUID
{

      @UI.hidden: true
  key $parameters.P_ChmlCompositionUUID as ChmlCompositionUUID,

      @UI.hidden: true
      @UI.selectionField: [{ exclude: true }]
      @Consumption.semanticObject: 'SubstanceUUID'
  key _Substance.SubstanceUUID,

      @UI.hidden: true
      @UI.selectionField: [{ exclude: true }]
      _Substance.ListedSubstanceUUID,

      @UI: {
        lineItem : [{
           position:   10,
           importance: #HIGH
        }],
        identification : [{
           position:   10,
           importance: #HIGH
        }]
      }
      _Substance.SubstanceName,

      @UI: {
        lineItem : [{
           position:   20,
           importance: #HIGH
        }],
        identification : [{
           position:   20,
           importance: #HIGH
        }]
      }
      @Search: {
        defaultSearchElement: true,
        fuzzinessThreshold:   0.8,
        ranking: #LOW
      }
      _Substance.SubstanceInternalName,

      // used to search within listed substance names

      // Remark: Do not search on ListedSubstanceDefaultName because it is a calculated field

      @UI.hidden:true
      @Search:
      {
        defaultSearchElement: true,
        fuzzinessThreshold:   0.8,
        ranking: #HIGH
      }
      _Substance._ListedSubstance.CustListedSubstanceDfltName,

      // used to search within listed substance names

      // Remark: Do not search on ListedSubstanceDefaultName because it is a calculated field

      @UI.hidden:true
      @Search:
      {
        defaultSearchElement: true,
        fuzzinessThreshold:   0.8,
        ranking: #HIGH
      }
      _Substance._ListedSubstance.CntntListedSubstanceDfltName,

      //used to search within company substance names

      @UI.hidden:true
      @Search:
      {
        defaultSearchElement: true,
        fuzzinessThreshold:   0.8,
        ranking: #MEDIUM
      }
      _Substance.CompanySubstanceName,


      // used to search within listed substance names

      // Remark: Do not search on ListedSubstance because it is a calculated field

      @UI.hidden:true
      @Search:
      {
        defaultSearchElement: true,
        fuzzinessThreshold:   0.8,
        ranking: #MEDIUM
      }
      _Substance._ListedSubstance.CustListedSubstance,

      // used to search within listed substance names

      // Remark: Do not search on ListedSubstance because it is a calculated field

      @UI.hidden:true
      @Search:
      {
        defaultSearchElement: true,
        fuzzinessThreshold:   0.8,
        ranking: #MEDIUM
      }
      _Substance._ListedSubstance.CntntListedSubstance,

      @UI: {
        lineItem: [{
          position:   30,
          importance: #MEDIUM
        }]
      }
      _Substance._ListedSubstance.ECNumber,

      // used to search for EC number

      // Remark: Do not search on EC Number because it is a calculated field

      @UI.hidden:true
      @Search: {
        defaultSearchElement: true,
        fuzzinessThreshold:   0.9,
        ranking: #MEDIUM
      }
      _Substance._ListedSubstance.CustECNumber,

      // used to search for EC number

      // Remark: Do not search on EC Number because it is a calculated field

      @UI.hidden:true
      @Search: {
        defaultSearchElement: true,
        fuzzinessThreshold:   0.9,
        ranking: #MEDIUM
      }
      _Substance._ListedSubstance.CntntECNumber,

      @UI: {
        lineItem: [{
          position:   40,
          importance: #MEDIUM
        }]
      }
      _Substance._ListedSubstance.CASNumber,

      // used to search for EC number

      // Remark: Do not search on CAS Number because it is a calculated field

      @UI.hidden:true
      @Search: {
        defaultSearchElement: true,
        fuzzinessThreshold:   0.9,
        ranking: #HIGH
      }
      _Substance._ListedSubstance.CustCASNumber,

      // used to search for EC number

      // Remark: Do not search on CAS Number because it is a calculated field

      @UI.hidden:true
      @Search: {
        defaultSearchElement: true,
        fuzzinessThreshold:   0.9,
        ranking: #HIGH
      }
      _Substance._ListedSubstance.CntntCASNumber,

      // used to search for EC number

      // Remark: Do not search on MolecularFormula because it is a calculated field

      @UI.hidden:true
      @Search: {
        defaultSearchElement: true,
        fuzzinessThreshold:   0.7,
        ranking: #LOW
      }
      _Substance._ListedSubstance.CustMolecularFormula,

      // used to search for EC number

      // Remark: Do not search on MolecularFormula because it is a calculated field

      @UI.hidden:true
      @Search: {
        defaultSearchElement: true,
        fuzzinessThreshold:   0.7,
        ranking: #LOW
      }
      _Substance._ListedSubstance.CntntMolecularFormula,

      @UI.hidden: true
      _Substance.ResponsibleUnit,

      // Associations

      _SubstanceText
}
where --Select components which are not assigned in before production in the current draft table
  _SubstBsdCmpstnCompDraft.ChmlCompositionUUID is null