P_AssociatedTrdSecret

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


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

define view entity P_AssociatedTrdSecret
  --Select data from basic view 'Composition'
  as select from           I_ChmlComposition         as Composition

  --Join data from composition with status In Process => Active or draft composition
    left outer to one join P_AssociatedTrdSecretProc as CompositionInProcess on  Composition.ChmlCmplncInfoUUID  = CompositionInProcess.ChmlCmplncInfoUUID
                                                                             and Composition.ProdCmplncLegalArea = CompositionInProcess.ProdCmplncLegalArea

  --Join data from composition with status Released => only active chemical composition, because released composition is not editable
    left outer to one join P_AssociatedTrdSecretReld as CompositionReleased  on  Composition.ChmlCmplncInfoUUID  = CompositionReleased.ChmlCmplncInfoUUID
                                                                             and Composition.ProdCmplncLegalArea = CompositionInProcess.ProdCmplncLegalArea

  association [1..1] to P_CChmlCmplConstants as _Constants on _Constants.Constant1 = _Constants.Constant1
{
      --UUID of chemical compliance information
  key Composition.ChmlCmplncInfoUUID,

      --Composition Type
  key Composition.ChmlCompositionType,
  
  key Composition.ProdCmplncLegalArea,

      --UUID of chemical composition in process
      CompositionInProcess.ChmlCompositionInProcessUUID,

      --Status In Process
      case
        when CompositionInProcess.ChmlCmpstnInProcStatus = 'IP'
          then CompositionInProcess.ChmlCmpstnInProcStatus
        when CompositionInProcess.ChmlCmpstnInProcStatus = ''
             or CompositionInProcess.ChmlCmpstnInProcStatus is null
      --Set NE if the In Process Version is not existing
          then _Constants[inner].ConstantNotExisting
      end as ChmlCmpstnInProcStatus,

      --Indicator: Is active entity of chemical composition with status In Process
      CompositionInProcess.ChmlCmpstnInProcIsActiveEntity,

      --UUID of chemical composition released
      CompositionReleased.ChmlCompositionReleasedUUID,

      --Status Released
      case
        when CompositionReleased.ChmlCmpstnReldStatus = 'RE'
          then CompositionReleased.ChmlCmpstnReldStatus
        when CompositionReleased.ChmlCmpstnReldStatus = ''
             or CompositionReleased.ChmlCmpstnReldStatus is null
      --Set NE if the Released Version is not existing
          then _Constants[inner].ConstantNotExisting
      end as ChmlCmpstnReldStatus,

      --Released by User
      CompositionReleased.ReleasedByUser,

      --Indicator: Is active entity of chemical composition with status In Process
      CompositionReleased.ChmlCmpstnReldIsActiveEntity
}
where
  --Only for Released and In Process Compositions
  // Check for ChmlCompositionCategory = 'A' leads to temporary materialization of this view

  // Condition: ChmlCompositionType <> 'ANALYTIC1' and ChmlCompositionType <> 'MATLBSD1' does not lead to materialization.

  //       Composition._CompositionType.ChmlCompositionCategory =  'A'

  //  and  Composition.ChmlCompositionType                      <> 'ANALYTIC1'

  (
       Composition.ChmlCompositionType   = 'TS'
  )
  and(
       Composition.ChmlCompositionStatus = 'IP'
    or Composition.ChmlCompositionStatus = 'RE'
  )

group by
  Composition.ChmlCmplncInfoUUID,
  Composition.ChmlCompositionType,
  Composition.ProdCmplncLegalArea,
  CompositionInProcess.ChmlCompositionInProcessUUID,
  CompositionInProcess.ChmlCmpstnInProcStatus,
  CompositionInProcess.ChmlCmpstnInProcIsActiveEntity,
  CompositionReleased.ChmlCompositionReleasedUUID,
  CompositionReleased.ChmlCmpstnReldStatus,
  CompositionReleased.ReleasedByUser,
  CompositionReleased.ChmlCmpstnReldIsActiveEntity,
  _Constants[inner].ConstantNotExisting
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CHMLCOMPOSITION",
"P_ASSOCIATEDTRDSECRETPROC",
"P_ASSOCIATEDTRDSECRETRELD",
"P_CCHMLCMPLCONSTANTS"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/