I_EHSSubject

@AbapCatalog.sqlViewName: 'IEHSSUBJECT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Subject'

@VDM.viewType: #COMPOSITE

@ClientHandling.algorithm: #SESSION_VARIABLE

@ObjectModel : { dataCategory: #TEXT,
                 representativeKey: 'EHSSubjectUUID',
                 usageType: {serviceQuality: #D,
                             sizeCategory: #L,
                             dataClass: #META} }
//Chemical Physical property

define view I_EHSSubject
  as select distinct from I_EHSChemicalPhysicalProperty
  association [0..*] to I_EHSSubjectText as _Text on $projection.EHSSubjectUUID = _Text.EHSSubjectUUID
{
      @ObjectModel.text.association: '_Text'
  key EHSChemicalPhysicalPropUUID   as EHSSubjectUUID,
      EHSChemicalPhysicalPropID     as EHSSubjectID,
      cast('' as ehfnd_chm_cas_no ) as CASNumber,

      // association

      _Text
}
//Chemical

union select distinct from I_ChemicalNameText  as ChemicalNameText
  inner join               I_ChemicalRoot      as ChemicalRoot      on ChemicalRoot.ChemicalUUID = ChemicalNameText.ChemicalUUID
  inner join               I_ChemicalRevision  as ChemicalRevision  on ChemicalRoot.ChemicalUUID = ChemicalRevision.ChemicalUUID
  inner join               I_ChemicalBasicInfo as ChemicalBasicInfo on  ChemicalRevision.ChemicalUUID         = ChemicalBasicInfo.ChemicalUUID
                                                                    and ChemicalRevision.ChemicalRevisionUUID = ChemicalBasicInfo.ChemicalRevisionUUID
association [0..*] to I_EHSSubjectText as _Text on $projection.EHSSubjectUUID = _Text.EHSSubjectUUID
{
      @ObjectModel.text.association: '_Text'
  key ChemicalRoot.ChemicalUUID as EHSSubjectUUID,
      ChemicalRoot.ChemicalID   as EHSSubjectID,
      ChemicalBasicInfo.CASNumber,

      // association

      _Text
}
where
      ChemicalNameText.ChemicalUUID      = ChemicalBasicInfo.ChemicalUUID
  and ChemicalRevision.RevisionStartDate <= $session.system_date
  and ChemicalRevision.RevisionEndDate   >= $session.system_date


//Listed substance

union select distinct from I_ListedSubstance
association [0..*] to I_EHSSubjectText as _Text on $projection.EHSSubjectUUID = _Text.EHSSubjectUUID
{
      @ObjectModel.text.association: '_Text'
  key ListedSubstanceUUID as EHSSubjectUUID,
      ListedSubstance     as EHSSubjectID,
      CASNumber,

      // association

      _Text
}

// Chemical Compliance Info

union select distinct from I_ChmlCmplncInfo
association [0..*] to I_EHSSubjectText as _Text on $projection.EHSSubjectUUID = _Text.EHSSubjectUUID
{
      @ObjectModel.text.association: '_Text'
  key ChmlCmplncInfoUUID            as EHSSubjectUUID,
      Specification                 as EHSSubjectID,
      cast('' as ehfnd_chm_cas_no ) as CASNumber,

      // association

      _Text
}

// Company substance

union select distinct from I_Substance
association [0..*] to I_EHSSubjectText as _Text on $projection.EHSSubjectUUID = _Text.EHSSubjectUUID
{
      @ObjectModel.text.association: '_Text'
  key SubstanceUUID as EHSSubjectUUID,
      _ListedSubstance.ListedSubstance as EHSSubjectID,
      _ListedSubstance.CASNumber,

      // association

      _Text
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CHEMICALBASICINFO",
"I_CHEMICALNAMETEXT",
"I_CHEMICALREVISION",
"I_CHEMICALROOT",
"I_CHMLCMPLNCINFO",
"I_EHSCHEMICALPHYSICALPROPERTY",
"I_LISTEDSUBSTANCE",
"I_SUBSTACTIVEDRAFTLSTDSUBST",
"I_SUBSTANCE"
],
"ASSOCIATED":
[
"I_EHSSUBJECTTEXT"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/