I_SubstanceLstdSubst

@EndUserText.label: 'Company Substance and related listed sub'
@VDM.viewType: #COMPOSITE
@ObjectModel.representativeKey: 'SubstanceUUID'
@AccessControl:
{
  authorizationCheck: #CHECK
}
@AbapCatalog:
{
  sqlViewName: 'ILISULSTDSUB',
  compiler.compareFilter: true,
  preserveKey: true
}

// 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: #MIXED }

// Performance-optimized view for listed substance root and name data for a company substance

// The stub for the company substance is needed as as otherwise the filter pushdown to the listed substance views would not work


define view I_SubstanceLstdSubst
  as select from I_Substance as Substance
  association [0..1] to I_ListedSubstanceCust        as _LisuCustRoot on  Substance.ListedSubstanceUUID = _LisuCustRoot.ListedSubstanceUUID
  association [0..1] to I_ListedSubstanceSAP         as _LisuContRoot on  Substance.ListedSubstanceUUID = _LisuContRoot.ListedSubstanceUUID
  association [0..1] to I_ListedSubstanceDefNameCust as _LisuCustName on  Substance.ListedSubstanceUUID = _LisuCustName.ListedSubstanceUUID
                                                                      and _LisuCustName.Language        = $session.system_language
  association [0..1] to I_ListedSubstanceDefNameSAP  as _LisuContName on  Substance.ListedSubstanceUUID = _LisuContName.ListedSubstanceUUID
                                                                      and _LisuContName.Language        = $session.system_language
{
      ----UUID of active or draft substance
  key Substance.SubstanceUUID                                     as SubstanceUUID,
      Substance.ListedSubstanceUUID                               as ActiveSubstanceUUID, // due to compatibility reasons this attribute must be kept

      Substance.ListedSubstanceUUID                               as ListedSubstanceUUID,
      Substance.ResponsibleUnit                                   as ResponsibleUnit, //needed for DCL


      _LisuCustName.ListedSubstanceName                           as CustListedSubstanceDfltName,
      _LisuContName.ListedSubstanceName                           as CntntListedSubstanceDfltName,
      coalesce(
                _LisuCustName.ListedSubstanceName,
                _LisuContName.ListedSubstanceName
              )                                                   as ListedSubstanceDefaultName,

      _LisuCustRoot.ListedSubstance                               as CustListedSubstance,
      _LisuContRoot.ListedSubstance                               as CntntListedSubstance,
      cast( coalesce(
                      _LisuCustRoot.ListedSubstance,
                      _LisuContRoot.ListedSubstance
                    ) as ehfnd_regbaslst_sub preserving type )    as ListedSubstance,

      _LisuCustRoot.CASNumber                                     as CustCASNumber,
      _LisuContRoot.CASNumber                                     as CntntCASNumber,
      cast( coalesce(
                      _LisuCustRoot.CASNumber,
                      _LisuContRoot.CASNumber
                    ) as ehfnd_chm_cas_no preserving type )       as CASNumber,

      _LisuCustRoot.ECNumber                                      as CustECNumber,
      _LisuContRoot.ECNumber                                      as CntntECNumber,
      cast( coalesce(
                      _LisuCustRoot.ECNumber,
                      _LisuContRoot.ECNumber
                    )  as ehfnd_chm_lisu_ec_number preserving type )  as ECNumber,

      _LisuCustRoot.MolecularFormula                              as CustMolecularFormula,
      _LisuContRoot.MolecularFormula                              as CntntMolecularFormula,
      cast( coalesce(
                      _LisuCustRoot.MolecularFormula,
                      _LisuContRoot.MolecularFormula
                    ) as ehfnd_lisu_mol_formula preserving type ) as MolecularFormula

//      cast( coalesce(

//                      _LisuCustRoot.CreationDateTime,

//                      _LisuContRoot.CreationDateTime

//                    ) as timestamp preserving type )              as CreationDateTime


}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_LISTEDSUBSTANCECUST",
"I_LISTEDSUBSTANCEDEFNAMECUST",
"I_LISTEDSUBSTANCEDEFNAMESAP",
"I_LISTEDSUBSTANCESAP",
"I_SUBSTANCE"
],
"ASSOCIATED":
[
"I_LISTEDSUBSTANCECUST",
"I_LISTEDSUBSTANCEDEFNAMECUST",
"I_LISTEDSUBSTANCEDEFNAMESAP",
"I_LISTEDSUBSTANCESAP"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/