I_SubstanceLstdSubstAnlyts1

@AbapCatalog.sqlViewName: 'ISUBLSTDSUBA1'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true

@EndUserText.label: 'Company Substance and Listed Substance'

@AccessControl.authorizationCheck: #CHECK

@ClientHandling:
{
  type: #INHERITED,
  algorithm: #SESSION_VARIABLE
}

@VDM.viewType: #COMPOSITE

@ObjectModel.usageType:{ serviceQuality: #C,
                         sizeCategory:  #L,
                         dataClass: #MIXED }
                         
                         
/*----------------------------------------------------------------------------------------------------------------------
    Company substance and its related listed substance from Customer (ehfndd_lisu_rt) and SAP (ehfndi_lisu_rt)
------------------------------------------------------------------------------------------------------------------------*/
define view I_SubstanceLstdSubstAnlyts1
  as select from I_Substance as Substance
  -- Association to Listed Substance from Customer
  association [0..1] to I_ListedSubstanceCust        as _LisuCustRoot on  Substance.ListedSubstanceUUID = _LisuCustRoot.ListedSubstanceUUID
  -- Association to Listed Substance from SAP
  association [0..1] to I_ListedSubstanceSAP         as _LisuContRoot on  Substance.ListedSubstanceUUID = _LisuContRoot.ListedSubstanceUUID
  -- Association to Listed Substance Default Name from Customer
  association [0..1] to I_ListedSubstanceDefNameCust as _LisuCustName on  Substance.ListedSubstanceUUID = _LisuCustName.ListedSubstanceUUID
                                                                      and _LisuCustName.Language        = $session.system_language
  -- Association to  Listed Substance Default Name from SAP
  association [0..1] to I_ListedSubstanceDefNameSAP  as _LisuContName on  Substance.ListedSubstanceUUID = _LisuContName.ListedSubstanceUUID
                                                                      and _LisuContName.Language        = $session.system_language

{
      -- UUID of Company Substance
  key Substance.SubstanceUUID                                                                        as       SubstanceUUID,

      -- UUID of Listed Substance
  key Substance.ListedSubstanceUUID                                                                  as       ListedSubstanceUUID,

      -- Responsible Unit (used for DCL)
      Substance.ResponsibleUnit                                                                      as       ResponsibleUnit,

      -- Internal Name of Company Substance respectifely Default Name of Listed Substance (Remark: Coalesce does not work on intitial value)
      case
        when SubstanceInternalName is not initial then SubstanceInternalName
        when _LisuCustName.ListedSubstanceName is not null then _LisuCustName.ListedSubstanceName // Remark: Do not use 'is not initial'

        else _LisuContName.ListedSubstanceName
      end                                                                                            as       CompanySubstanceName,
      -- Listed Substance
      cast( coalesce(
                      _LisuCustRoot.ListedSubstance,
                      _LisuContRoot.ListedSubstance
                    ) as ehfnd_regbaslst_sub preserving type )                                       as       ListedSubstance,
      -- CAS Number of Listed Substance
      cast( coalesce(
                      _LisuCustRoot.CASNumber,
                      _LisuContRoot.CASNumber
                    ) as ehfnd_chm_cas_no preserving type )                                          as       CASNumber,
      -- EC Number of Listed Substance
      cast( coalesce(
                      _LisuCustRoot.ECNumber,
                      _LisuContRoot.ECNumber
                    )  as ehfnd_lisu_ec_number preserving type )                                     as       ECNumber,
      -- Group Indicator of Listed Substance
      cast( coalesce(
                      _LisuCustRoot.ListedSubstanceIsGroup,
                      _LisuContRoot.ListedSubstanceIsGroup
                    )  as ehfnd_lisu_isgroup_ind preserving type )                                   as       ListedSubstanceIsGroup,

      // Substance Name

      _SubstanceText[1: Language = $session.system_language].SubstanceName                           as       SubstanceName
      
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_LISTEDSUBSTANCECUST",
"I_LISTEDSUBSTANCEDEFNAMECUST",
"I_LISTEDSUBSTANCEDEFNAMESAP",
"I_LISTEDSUBSTANCESAP",
"I_SUBSTANCE",
"I_SUBSTANCETEXT"
],
"ASSOCIATED":
[
"I_LISTEDSUBSTANCECUST",
"I_LISTEDSUBSTANCEDEFNAMECUST",
"I_LISTEDSUBSTANCEDEFNAMESAP",
"I_LISTEDSUBSTANCESAP"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/