P_AssociatedProductComposition

@EndUserText.label: 'Associated Composition of Product'

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

@AccessControl:
{
  authorizationCheck: #NOT_REQUIRED
}

@ClientHandling.algorithm: #SESSION_VARIABLE

@VDM.viewType: #CONSUMPTION
@VDM: {
    private: true
}

define view P_AssociatedProductComposition
  --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 I_ChmlComposition as CompositionInProcess on  Composition.ChmlCmplncInfoUUID             = CompositionInProcess.ChmlCmplncInfoUUID
                                                                     and Composition.ChmlCompositionType            = CompositionInProcess.ChmlCompositionType
                                                                     and Composition.ProdCmplncLegalArea            = CompositionInProcess.ProdCmplncLegalArea
                                                                     and CompositionInProcess.ChmlCompositionStatus = 'IP'

  --Join data from composition with status Released => only active chemical composition, because released composition is not editable
    left outer to one join I_ChmlComposition as CompositionReleased  on  Composition.ChmlCmplncInfoUUID            = CompositionReleased.ChmlCmplncInfoUUID
                                                                     and Composition.ChmlCompositionType           = CompositionReleased.ChmlCompositionType
                                                                     and Composition.ProdCmplncLegalArea           = CompositionReleased.ProdCmplncLegalArea
                                                                     and CompositionReleased.ChmlCompositionStatus = 'RE'
{
      --UUID of chemical compliance information
  key Composition.ChmlCmplncInfoUUID             as ChmlCmplncInfoUUID,

      --Composition Type
  key Composition.ChmlCompositionType            as ChmlCompositionType,

  key Composition.ProdCmplncLegalArea            as ProdCmplncLegalArea,

      --Navigation Link UUID
      case
        when CompositionInProcess.ChmlCompositionStatus = 'IP' then
          CompositionInProcess.ChmlCompositionUUID
        when CompositionReleased.ChmlCompositionStatus = 'RE' then
          CompositionReleased.ChmlCompositionUUID
        else hextobin( '00000000000000000000000000000000' )
      end                                        as ChmlCmpstnNavgnLinkUUID,

      case
        when CompositionInProcess.ChmlCompositionStatus = 'IP' then
          CompositionInProcess.ChmlCmplncProdCatztnPhrsUUID
        when CompositionReleased.ChmlCompositionStatus = 'RE' then
          CompositionReleased.ChmlCmplncProdCatztnPhrsUUID
        else hextobin( '00000000000000000000000000000000' )
      end                                        as ChmlCmplncProdCatztnPhrsUUID,
      ------------------------------------------------------------------------------
      --- In Progress Version
      ------------------------------------------------------------------------------
      --UUID of chemical composition in process
      CompositionInProcess.ChmlCompositionUUID   as ChmlCompositionInProcessUUID,

      CompositionInProcess.ChmlCompositionStatus as ChmlCmpstnInProcStatus,

      ------------------------------------------------------------------------------
      --- Released Version
      ------------------------------------------------------------------------------
      --UUID of chemical composition released
      CompositionReleased.ChmlCompositionUUID    as ChmlCompositionReleasedUUID,

      CompositionReleased.ChmlCompositionStatus  as ChmlCmpstnReldStatus,

      --Released by User
      CompositionReleased.ReleasedByUser         as ReleasedByUser

}
where
       Composition.ChmlCmplncInfoUUID    <> hextobin( '00000000000000000000000000000000' )
  and  Composition.ChmlCompositionType   <> 'MATLBSD1'
  and(
       --Only for Released and In Process Compositions
       Composition.ChmlCompositionStatus =  'IP'
    or Composition.ChmlCompositionStatus =  'RE'
  )
group by
  Composition.ChmlCmplncInfoUUID,
  Composition.ChmlCompositionType,
  Composition.ProdCmplncLegalArea,
  CompositionInProcess.ChmlCmplncProdCatztnPhrsUUID,
  CompositionInProcess.ChmlCompositionUUID,
  CompositionInProcess.ChmlCompositionStatus,
  CompositionReleased.ChmlCmplncProdCatztnPhrsUUID,
  CompositionReleased.ChmlCompositionUUID,
  CompositionReleased.ChmlCompositionStatus,
  CompositionReleased.ReleasedByUser
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CHMLCOMPOSITION"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/