P_BasicDescForReldRglnScore

@AbapCatalog:{
  sqlViewName: 'PDGBDSCFRRS',
  compiler.compareFilter: true
}
@AccessControl.authorizationCheck: #NOT_REQUIRED

@ClientHandling.algorithm: #SESSION_VARIABLE

@ObjectModel: {
  usageType: {
    serviceQuality: #C,
    sizeCategory: #L,
    dataClass: #MIXED
  }
}

@VDM: {
  private: true,
  viewType: #COMPOSITE
}

define view P_BasicDescForReldRglnScore

  as select from I_CmplRqRslt
    inner join   I_CmplRqRsltDngrsGdsBscDescT on I_CmplRqRsltDngrsGdsBscDescT.CmplRqRsltUUID = I_CmplRqRslt.CmplRqRsltUUID

{

  key I_CmplRqRslt.ChmlCmplncInfoUUID,
  key I_CmplRqRslt.CmplRqRsltUUID,
  key I_CmplRqRsltDngrsGdsBscDescT.Language,


  key case
      // Look for CRID UNRTDG in the logon language

    when I_CmplRqRslt.ComplianceRequirement = 'UNRTDG' and I_CmplRqRsltDngrsGdsBscDescT.Language = $session.system_language
      then 51
      // Fallback same CRID but in English

    when I_CmplRqRslt.ComplianceRequirement = 'UNRTDG' and I_CmplRqRsltDngrsGdsBscDescT.Language = 'E'
      then 50
      // Fallback same CRID but in English

    when I_CmplRqRslt.ComplianceRequirement = 'IMDG-Code' and I_CmplRqRsltDngrsGdsBscDescT.Language = 'E'
      then 40
      // Fallback CRID ADR in the logon language

    when I_CmplRqRslt.ComplianceRequirement = 'ADR' and I_CmplRqRsltDngrsGdsBscDescT.Language = $session.system_language
      then 31
      // Fallback same CRID but in English

    when I_CmplRqRslt.ComplianceRequirement = 'ADR' and I_CmplRqRsltDngrsGdsBscDescT.Language = 'E'
      then 30
      // Fallback CRID 49 CFR in the logon language

    when I_CmplRqRslt.ComplianceRequirement = '49 CFR' and I_CmplRqRsltDngrsGdsBscDescT.Language = $session.system_language
      then 21
      // Give all other English descriptions the value 10

    when I_CmplRqRsltDngrsGdsBscDescT.Language = 'E'
      then 10
      // all others

    else 0
  end as FallbackScore,

      I_CmplRqRslt.CmplRqVersUUID,
      I_CmplRqRsltDngrsGdsBscDescT.DangerousGoodBasicDescription

}
where
      I_CmplRqRslt.CmplRqRsltProcessingStatus                    =  'RE'
  and I_CmplRqRsltDngrsGdsBscDescT.DangerousGoodBasicDescription <> ''

// This should not be necessary if we put a 10 to english descriptions per default: We wil filter out duplicate results in the I_UpaDaGdDescForReldRegulation either way


//// and Look for DG Basic Description on a released CRR for any (the first available) CRID in EN

//union select from I_CmplRqRsltDngrsGdsBscDescT

//  inner join      I_CmplRqRslt                 on I_CmplRqRsltDngrsGdsBscDescT.CmplRqRsltUUID = I_CmplRqRslt.CmplRqRsltUUID

//  inner join      P_BasicDescForReldRglnMaxCRR on  I_CmplRqRsltDngrsGdsBscDescT.CmplRqRsltUUID = P_BasicDescForReldRglnMaxCRR.CmplRqRsltUUID

//                                               and I_CmplRqRslt.ChmlCmplncInfoUUID             = P_BasicDescForReldRglnMaxCRR.ChmlCmplncInfoUUID

//

//{

//  key I_CmplRqRslt.ChmlCmplncInfoUUID,

//  key I_CmplRqRslt.CmplRqRsltUUID,

//

//  key 10 as FallbackScore,

//

//      I_CmplRqRslt.CmplRqVersUUID,

//      I_CmplRqRsltDngrsGdsBscDescT.Language,

//      I_CmplRqRsltDngrsGdsBscDescT.DangerousGoodBasicDescription

//

//}

//where

//  I_CmplRqRsltDngrsGdsBscDescT.Language = 'E'