C_ProdCmplncServiceReqSubst

@EndUserText.label: 'PC Service Requests Private Substance'

@AbapCatalog:
{
  sqlViewName: 'CPCMPLSERVRQSUVH',
  compiler.compareFilter: true,
  preserveKey: true
}

@AccessControl.authorizationCheck: #MANDATORY

@VDM.viewType: #CONSUMPTION

@ClientHandling.algorithm: #SESSION_VARIABLE

@ObjectModel:
{
  usageType:
  {
    dataClass: #MIXED,
    sizeCategory: #L, // <= 10 000 000

    serviceQuality: #C // <= 20 msec

  }
}

@Metadata.ignorePropagatedAnnotations: true

/*+[hideWarning] { "IDS" : [ "DOUBLE_JOIN" ]  } */
define view C_ProdCmplncServiceReqSubst
  as select from           I_SubstanceTP               as _Substance

    left outer to one join I_PhysicalChemicalProperty  as _PhysChemProperty          on  _PhysChemProperty.SubstanceUUID   = _Substance.SubstanceUUID
                                                                                     and _PhysChemProperty.PCPrptyProcgSts = 'RE'

//    left outer to one join I_PhysicalChemicalProperty  as _PhysChemPropertyIp        on  _PhysChemPropertyIp.SubstanceUUID   = _Substance.SubstanceUUID

//                                                                                     and _PhysChemPropertyIp.PCPrptyProcgSts = 'IP'



    left outer to one join I_PCSftyProperty            as _SftyRelated               on  _SftyRelated.SubstanceUUID   = _Substance.SubstanceUUID
                                                                                     and _SftyRelated.PCPrptyProcgSts = 'RE'

//    left outer to one join I_PCSftyProperty            as _SftyRelatedIp             on  _SftyRelatedIp.SubstanceUUID   = _Substance.SubstanceUUID

//                                                                                     and _SftyRelatedIp.PCPrptyProcgSts = 'IP'


  // used to filter existing service requests on the create dialog

    left outer to one join P_LatestSrvcReqForSubstance as _LatestSrvcReqForSubstance on _LatestSrvcReqForSubstance.SubstanceUUID = _Substance.SubstanceUUID
  -- Data bucket information via a Custom Entity (ABAP Implementation - so only accessible via OData / SADL)
  association [0..*] to C_ProdCmplncPrvtSubstSendData as _SendData on $projection.SubstanceUUID = _SendData.SubstanceUUID
{
  key _Substance.SubstanceUUID, // used for the substance navigation


      _PhysChemProperty.PhysChmlPrptyUUID,

      _SftyRelated.PCSftyPrptyUUID,

      _Substance.SubstanceName,

      _Substance.SubstanceInternalName,

      _Substance._ListedSubstance.ECNumber,

      _Substance._ListedSubstance.CASNumber,

      // Dialog Confirm Request Creation

      // physical-chemical properties released date

//      _PhysChemProperty.ValidityStartDateTime                                                                                         as PhysChemPrptyReleaseDateTime,

      // safety-related properties released date

//      _SftyRelated.ValidityStartDateTime                                                                                              as PCSftyPrptyReleaseDateTime,


      cast ( case when _LatestSrvcReqForSubstance.LastChangeDateTime is null then ' ' else 'X' end as ehfnd_boolean preserving type ) as ProdCmplncServiceReqIsExisting,
//      cast ( case when _PhysChemPropertyIp.PhysChmlPrptyUUID  is null then ' ' else 'X' end as ehfnd_boolean preserving type )        as PhysChemPropertyIsInProgress,

//      cast ( case when _SftyRelatedIp.PCSftyPrptyUUID         is null then ' ' else 'X' end as ehfnd_boolean preserving type )        as PCSftyPrptyIsInProgress,


      @ObjectModel.filter.enabled: false
      _SendData

}
where
  _Substance.Specification <> ''