I_ProdMktCmplRqRsltText

@AbapCatalog.sqlViewName: 'IPRODMKTCRRTEXT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #NOT_REQUIRED
@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE
@EndUserText.label: 'Text associations for CmplRq for Products'
@ObjectModel.usageType:{ serviceQuality: #D,        // < 500 msec

                         sizeCategory:  #XL,        // < 100.000.000

                         dataClass: #MIXED }
define view I_ProdMktCmplRqRsltText
  as select from I_CmplRqRslt         as CmplRqRslt
  join         C_ChmlCmplncPrimMatl   as _ChmlCmplncPrimMatl            on CmplRqRslt.ChmlCmplncInfoUUID                = _ChmlCmplncPrimMatl.ChmlCmplncInfoUUID
  
  association [1..*] to I_ChmlCmplncMatlAssgmt as _ChmlCmplncMatlAssgmt on  $projection.ChmlCmplncInfoUUID = _ChmlCmplncMatlAssgmt.ChmlCmplncInfoUUID
  association [0..1] to I_BusinessUserBasic    as _ProcessorUser        on  $projection.Processor          = _ProcessorUser.BusinessPartner
  association [0..1] to I_BusinessUserBasic    as _ReleasedByUser       on  $projection.ReleasedByUser     = _ReleasedByUser.BusinessPartner
  association [0..*] to I_CmplRqRsltExemption  as _CmplRqRsltExemption  on  $projection.CmplRqRsltUUID     = _CmplRqRsltExemption.CmplRqRsltUUID
{
  key CmplRqRslt.ChmlCmplncInfoUUID,
  key CmplRqRslt.CmplRqRsltUUID,
  key CmplRqRslt.CmplRqVersUUID,
  key _ChmlCmplncMatlAssgmt.ChmlCmplncMatlAssgmtUUID,
  key _CmplRqRsltExemption.CmplRqRsltExmpAssgmtUUID,
       CmplRqRslt.ChmlSuplrMatlUUID,
       CmplRqRslt.SubstanceUUID,      
      _ChmlCmplncMatlAssgmt._Product.Product,
  
      _ChmlCmplncPrimMatl._ChmlCmplncInfo.ChmlCmplncInfoType,
      _ChmlCmplncPrimMatl._ChmlCmplncInfo.MaterialIsSold,
      _ChmlCmplncPrimMatl._ChmlCmplncInfo.MaterialIsTransported,
      _ChmlCmplncPrimMatl._ChmlCmplncInfo.MaterialIsSourced,
      _ChmlCmplncPrimMatl._ChmlCmplncInfo.MaterialIsProduced,
      _ChmlCmplncPrimMatl.Material,
      _ChmlCmplncPrimMatl._ChmlCmplncInfo.ProdStewardshipRespUnit,
      _ChmlCmplncPrimMatl.ChmlCmplncInfoCombinedName,
      cast(_ChmlCmplncPrimMatl._ChmlCmplncInfo.ChmlCmplncProdIsResearched as char1) as ChmlCmplncProdIsResearched,

      //Processing Status

      CmplRqRslt.CmplRqRsltProcessingStatus,

      cast(
          case
           when   ( CmplRqRslt.CmplRqRsltProcessingStatus = 'RE' or CmplRqRslt.CmplRqRsltProcessingStatus = 'OD' )
              then   CmplRqRslt.CmplRqRsltReldCmplncSts
           when   CmplRqRslt.CmplRqRsltProcessingStatus = 'IP' and CmplRqRslt.CmplRqRsltManualStatus <> '03'
              then   CmplRqRslt.CmplRqRsltManualStatus
           when CmplRqRslt.CmplRqRsltProcessingStatus = 'IP' and CmplRqRslt.CmplRqRsltCalculatedStatus is not null and CmplRqRslt.CmplRqRsltCalculatedStatus <> ''
              then   CmplRqRslt.CmplRqRsltCalculatedStatus
           else  '03' end as ehpma_crr_mkt_stat_nd preserving type
      )                                                                             as CmplRqRsltReldCmplncSts,

      CmplRqRslt.ComplianceRequirement,
      CmplRqRslt.ReleasedByUser,
      CmplRqRslt.Processor,

      _ProcessorUser,
      _ReleasedByUser,
      _ChmlCmplncMatlAssgmt._Product._ProductGroup,
      _ChmlCmplncMatlAssgmt._Product._ProductType,
      _ChmlCmplncMatlAssgmt._Product._Division,
      _ChmlCmplncPrimMatl._ChmlCmplncInfo._RespUnitForProductStewardship,

      ValidityStartDateTime,
      ValidityEndDateTime,

      _ChmlCmplncMatlAssgmt._Product.ProductGroup,
      _ChmlCmplncMatlAssgmt._Product.ProductType,
      _ChmlCmplncMatlAssgmt._Product.IndustrySector,
      _ChmlCmplncMatlAssgmt._Product.Division,
      _CmplRqRsltExemption.CmplRqVersExemption                                      as CmplRqVersExemption

}
where
  (
       CmplRqRslt.CmplRqRsltProcessingStatus = 'IP'
    or CmplRqRslt.CmplRqRsltProcessingStatus = 'RE'
    or CmplRqRslt.CmplRqRsltProcessingStatus = 'OD'
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"C_CHMLCMPLNCPRIMMATL",
"I_CHMLCMPLNCINFO",
"I_CHMLCMPLNCMATLASSGMT",
"I_CMPLRQRSLT",
"I_CMPLRQRSLTEXEMPTION",
"I_PRODUCT"
],
"ASSOCIATED":
[
"I_BUSINESSUSERBASIC",
"I_CHMLCMPLNCMATLASSGMT",
"I_CMPLRQRSLTEXEMPTION",
"I_DIVISION",
"I_PRODUCTGROUP",
"I_PRODUCTTYPE",
"I_RESPONSIBLEUNIT"
],
"BASE":
[
"I_CHMLCMPLNCINFO",
"I_PRODUCT"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/