P_SubstanceAssgdPCEctx

@EndUserText.label: 'Assoc. Ecological Information Property'
--                  for Substance
@AbapCatalog:
{
  -- SQL view name (16 characters)
  sqlViewName: 'PSUBASGDECTX',

  --If it's true, the filter conditions are compared and, if they match, the associated join expression is evaluated only once
  compiler.compareFilter: true,
  preserveKey: true
}

--Access Control: Authorizations Checks
@AccessControl:
{
   authorizationCheck: #NOT_REQUIRED
}

--Client Handling of the view
@ClientHandling.algorithm: #SESSION_VARIABLE

--VDM view type
@VDM.viewType: #CONSUMPTION
@VDM.private: true

define view P_SubstanceAssgdPCEctx
  --Select data from basic view 'Substance'
  as select from           I_Substance      as Substance

  --Join data from physical chemical propertes with status In Process
    left outer to one join I_PCEctxProperty as EcotoxInProcess on  Substance.SubstanceUUID         = EcotoxInProcess.SubstanceUUID
                                                               and EcotoxInProcess.PCPrptyProcgSts = 'IP'

  --Join data from physical chemical propertes with status Released
    left outer to one join I_PCEctxProperty as EcotoxReleased  on  Substance.SubstanceUUID        = EcotoxReleased.SubstanceUUID
                                                               and EcotoxReleased.PCPrptyProcgSts = 'RE'

  --Dummy constant selection to solve issues with case-statements with constants (not null-preserving)
  association [1..1] to P_CChmlCmplConstants as _Constants on _Constants.Constant1 = _Constants.Constant1

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

      --Property Type
  key cast('EHFNDD_ECTX' as ehfnd_prop_property_root )                          as PCPrptyRootType,

      --UUID of in process version
      EcotoxInProcess.PCEctxPrptyUUID                                           as PCPrptyInProcessUUID,

      --Status In Process
      EcotoxInProcess.PCPrptyProcgSts                                           as PCPrptyInProcProcgSts,

      --Is Active entity
      cast(_Constants[inner].ConstantTrue as sdraft_is_active preserving type ) as PCPrptyInProcIsActive,

      --UUID of released version
      EcotoxReleased.PCEctxPrptyUUID                                            as PCPrptyReleasedUUID,

      --Status Released
      EcotoxReleased.PCPrptyProcgSts                                            as PCPrptyReldProcgSts,

      --Is Active entity
      cast(_Constants[inner].ConstantTrue as sdraft_is_active preserving type ) as PCPrptyReldIsActive,

      Substance.ResponsibleUnit                                                 as ResponsibleUnit,
      
      --Navigation Link UUID
      case
        when EcotoxInProcess.PCPrptyProcgSts = 'IP' then
          EcotoxInProcess.PCEctxPrptyUUID
        when EcotoxReleased.PCPrptyProcgSts = 'RE' then
          EcotoxReleased.PCEctxPrptyUUID
        else
          _Constants.ConstantEmptyRaw16
      end                                                                       as PCPrptyNavgnLinkUUID,

      --Active Indicator
      case
        when EcotoxInProcess.PCPrptyProcgSts = 'IP'
          then cast(_Constants[inner].ConstantTrue as sdraft_is_active preserving type )
        when EcotoxReleased.PCPrptyProcgSts = 'RE'
          then cast(_Constants[inner].ConstantTrue as sdraft_is_active preserving type )
        else
          _Constants.ConstantFalse
      end                                                                       as PCPrptyIsActive      
      
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_PCECTXPROPERTY",
"I_SUBSTANCE",
"P_CCHMLCMPLCONSTANTS"
],
"ASSOCIATED":
[
"P_CCHMLCMPLCONSTANTS"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/