P_CMBNDCHMLASSOCDANLYTLCMPSTN

@AbapCatalog.sqlViewName: 'PCCCIMPAAC'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED

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

@VDM.viewType: #COMPOSITE
@VDM.private: true

define view P_CmbndChmlAssocdAnlytlCmpstn
  as select distinct from           I_ChmlCmplncInfo  as ChmlCmplncInfo
    left outer to one join I_ChmlComposition as _InProgressComposition on  ChmlCmplncInfo.ChmlCmplncInfoUUID            = _InProgressComposition.ChmlCmplncInfoUUID
                                                                       and _InProgressComposition.ChmlCompositionType   = 'ANALYTIC1'
                                                                       and _InProgressComposition.ChmlCompositionStatus = 'IP'
                                                                       and _InProgressComposition.ProdCmplncLegalArea   = ''


    left outer to one join I_ChmlComposition as _ReleasedComposition   on  ChmlCmplncInfo.ChmlCmplncInfoUUID          = _ReleasedComposition.ChmlCmplncInfoUUID
                                                                       and _ReleasedComposition.ChmlCompositionType   = 'ANALYTIC1'
                                                                       and _ReleasedComposition.ChmlCompositionStatus = 'RE'
                                                                       and _ReleasedComposition.ProdCmplncLegalArea   = ''

{
  key ChmlCmplncInfo.ChmlCmplncInfoUUID,

      case
      when _InProgressComposition.ChmlCompositionUUID is not null then _InProgressComposition.ChmlCompositionUUID
      when _ReleasedComposition.ChmlCompositionUUID is not null then _ReleasedComposition.ChmlCompositionUUID
      else hextobin('00000000000000000000000000000000')
      end as ChmlCompositionUUID,

      // Active Chemical Composition Status

      case
      when _InProgressComposition.ChmlCompositionUUID is not null then _InProgressComposition.ChmlCompositionStatus
      when _ReleasedComposition.ChmlCompositionUUID is not null then _ReleasedComposition.ChmlCompositionStatus
      else ''
      end as ChmlCompositionStatus,
      
      case
        when _InProgressComposition.ChmlCompositionUUID is not null
         then 
          case 
            when _InProgressComposition.ChmlCompositionStatus = 'IP'
              then 2
            when _InProgressComposition.ChmlCompositionStatus = 'RE'
              then 3
            else 0
          end
        when _ReleasedComposition.ChmlCompositionUUID is not null
          then
            case 
            when _ReleasedComposition.ChmlCompositionStatus = 'IP'
              then 2
            when _ReleasedComposition.ChmlCompositionStatus = 'RE'
              then 3
            else 0
          end
        else 0
      end as    ChmlCmpstnNavgnStatusCritlty,
      
      case
      when _InProgressComposition.ChmlCompositionUUID is not null then _InProgressComposition._CompositionStatus._Text[1: Language = $session.system_language ].ChmlCompositionStatusName
      when _ReleasedComposition.ChmlCompositionUUID is not null then _ReleasedComposition._CompositionStatus._Text[1: Language = $session.system_language ].ChmlCompositionStatusName
      else ''
      end as ChmlCompositionStatusName,
      
      case
      when _InProgressComposition.ChmlCompositionUUID is not null then _InProgressComposition.ChmlCmplncProdCatztnPhrsUUID
      when _ReleasedComposition.ChmlCompositionUUID is not null then _ReleasedComposition.ChmlCmplncProdCatztnPhrsUUID
      else hextobin('00000000000000000000000000000000')
      end as ChmlCmplncProdCatztnPhrsUUID
}