I_UnpackagedDngrsGdsCube

@AbapCatalog.sqlViewName: 'IUPDGCUBE'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #MANDATORY
@AccessControl.personalData.blocking: #NOT_REQUIRED
@Analytics.dataCategory: #CUBE
@ClientHandling.algorithm: #SESSION_VARIABLE
@EndUserText.label: 'Unpackaged Dangerous Goods Cube'
@Metadata.ignorePropagatedAnnotations: true
@Metadata.allowExtensions: true
@ObjectModel.usageType:{ serviceQuality: #D,        // < 500 msec

                         sizeCategory:  #XL,        // < 100.000.000

                         dataClass: #MIXED }
@VDM.viewType: #COMPOSITE
define view I_UnpackagedDngrsGdsCube
  as select from I_CmplRqRslt
    join         I_ChmlCmplncMatlAssgmt      on I_ChmlCmplncMatlAssgmt.ChmlCmplncInfoUUID = I_CmplRqRslt.ChmlCmplncInfoUUID // Assignment of material to UP

    join         I_CmplRqVersEnhanced        on I_CmplRqRslt.CmplRqVersUUID = I_CmplRqVersEnhanced.CmplRqVersUUID // CRV Information

    join         I_CmplncPrpsRqmtAssgmt      on I_CmplncPrpsRqmtAssgmt.CmplRqUUID = I_CmplRqVersEnhanced.CmplRqUUID // Purposes the CRV appears in

    join         I_ChmlCmplncPrpsAssgmt      on  I_ChmlCmplncPrpsAssgmt.CmplncPrpsUUID     = I_CmplncPrpsRqmtAssgmt.CmplncPrpsUUID // Purpose is assigned to CCI

                                             and I_ChmlCmplncPrpsAssgmt.ChmlCmplncInfoUUID = I_ChmlCmplncMatlAssgmt.ChmlCmplncInfoUUID // Purpose is assigned to UP

    join         I_CmplncPrps                on I_CmplncPrps.CmplncPrpsUUID = I_CmplncPrpsRqmtAssgmt.CmplncPrpsUUID // Purpose information

    join         I_CmplRqRsltDngrsGdsBscDets on I_CmplRqRsltDngrsGdsBscDets.CmplRqRsltUUID = I_CmplRqRslt.CmplRqRsltUUID // Basic DG Details


  association [0..1] to I_CmplncPrps                 as _CmplncPrps                 on $projection.CmplncPrpsUUID = _CmplncPrps.CmplncPrpsUUID
  association [0..1] to I_ChmlCmplncInfo             as _ChmlCmplncInfo             on $projection.ChmlCmplncInfoUUID = _ChmlCmplncInfo.ChmlCmplncInfoUUID
  association [0..1] to I_Product                    as _Product                    on $projection.Product = _Product.Product
  association [1..1] to I_CmplRqRsltProcessingStatus as _CmplRqRsltProcessingStatus on $projection.CmplRqRsltProcessingStatus = _CmplRqRsltProcessingStatus.CmplRqRsltProcessingStatus
  association [1..1] to I_ClassifiedAsDangerousGood  as _ClassifiedAsDangerousGood  on $projection.ClassifiedAsDangerousGood = _ClassifiedAsDangerousGood.ClassifiedAsDangerousGood
  association [1..1] to I_TransportPermission        as _TransportPermission        on $projection.CmplRqRsltManualStatus = _TransportPermission.TransportPermission
  association [1..1] to I_DngrsGdsClassOrDivisionVH  as _DangerousGoodsClass        on $projection.DangerousGoodsClass = _DangerousGoodsClass.DangerousGoodsClass
  association [1..1] to I_DngrsGdsPackingGroupVH     as _DangerousGoodsPackingGroup on $projection.DangerousGoodsPackingGroup = _DangerousGoodsPackingGroup.DangerousGoodsPackingGroup
{

  key I_CmplRqRslt.CmplRqRsltUUID,

      // Compliance Purpose

      //@ObjectModel.foreignKey.association: '_CmplncPrps' <-- Commented as it prevents Dropdowns in ALP

  key I_CmplncPrps.CmplncPrpsUUID,

      // <!-- START OF TEMPORARY INSERTION -->

      // In 1908 we had Texts in the Cube like this and they were auto-associated to their keys

      // This is not the best approach but it allows us to define Value Helps with Dropdowns

      @Semantics.text: true
      COALESCE(_CmplncPrps._CmplncPrpsText[1: Language = $session.system_language ].CmplncPrpsName,
               _CmplncPrps._CmplncPrpsText[1: Language = 'E' ].CmplncPrpsName)                                                                                         as CmplncPrpsName,
      // <!-- END OF INSERTION -->


      // Compliance Requirement

      I_CmplRqRslt.ComplianceRequirement,
      I_CmplRqVersEnhanced.CmplRqVersName,
      I_CmplRqVersEnhanced.CmplRqPattern,

      I_CmplRqRslt.ChmlCmplncInfoUUID,
      // Product Internal Name

      I_CmplRqRslt._ChmlCmplncInfo.ChmlCmplncInternalName,
      // Material Number

      @ObjectModel.foreignKey.association: '_Product'
      I_ChmlCmplncMatlAssgmt.Material                                                                                                                                  as Product,
      // Product Name

      _Product._Text[1: Language = $session.system_language ].ProductName,

      // Processing Status

      //@ObjectModel.foreignKey.association: '_CmplRqRsltProcessingStatus' <-- Commented as it prevents Dropdowns

      I_CmplRqRslt.CmplRqRsltProcessingStatus,
      @Semantics.text: true
      cast( _CmplRqRsltProcessingStatus._Text[ 1: Language =  $session.system_language ].ProcessingStatusText as  ehfnd_crr_compl_stat_text preserving type )          as ProcessingStatusText,

      // Transport Permission

      //@ObjectModel.foreignKey.association: '_TransportPermission' <-- Commented as it prevents Dropdowns

      I_CmplRqRslt.CmplRqRsltManualStatus,
      @Semantics.text: true
      _TransportPermission._TransportPermissionText[ 1: Language =  $session.system_language ].TransportPermissionName,


      // Classified As

      //@ObjectModel.foreignKey.association: '_ClassifiedAsDangerousGood'  <-- Commented as it prevents Dropdowns

      I_CmplRqRsltDngrsGdsBscDets.ClassifiedAsDangerousGood                                                                                                            as ClassifiedAsDangerousGood,
      @Semantics.text: true
      cast( _ClassifiedAsDangerousGood._Text[ 1: Language =  $session.system_language ].ClassifiedAsDangerousGoodName as ehdgm_classified_as_dg_name preserving type ) as ClassifiedAsDangerousGoodName,

      // Prefix/Number

      I_CmplRqRsltDngrsGdsBscDets.DangerousGoodsIdnNumber,
      I_CmplRqRsltDngrsGdsBscDets.DangerousGoodsIdnNumberType                                                                                                          as DangerousGoodsIdnNumberType,
      concat_with_space ( I_CmplRqRsltDngrsGdsBscDets.DangerousGoodsIdnNumberType,
                          I_CmplRqRsltDngrsGdsBscDets.DangerousGoodsIdnNumber, 1)                                                                                      as DangerousGoodsIdnNumberCnctntd,

      // Class/Division

      //      @ObjectModel.foreignKey.association: '_DangerousGoodsClass'   <-- Commented as it prevents Dropdowns

      I_CmplRqRsltDngrsGdsBscDets.DangerousGoodsClass,

      // Packing Group

      //      @ObjectModel.foreignKey.association: '_DangerousGoodsPackingGroup'      <-- Commented as it prevents Dropdowns

      I_CmplRqRsltDngrsGdsBscDets.DangerousGoodsPackingGroup,

      /** MEASURES **/

      @DefaultAggregation: #SUM
      cast(1 as int4)                                                                                                                                                  as NmbrOfCmplRqRslts,

      _CmplncPrps,
      _ChmlCmplncInfo,
      _Product,
      _TransportPermission,
      _CmplRqRsltProcessingStatus,
      _ClassifiedAsDangerousGood,
      _DangerousGoodsClass,
      _DangerousGoodsPackingGroup
}
where
       I_CmplRqRslt._ChmlCmplncInfo.ChmlCmplncInfoType = 'BU'
  and  I_CmplRqVersEnhanced.CmplRqApplicationComponent = 'DG'
  and(
       CmplRqRsltProcessingStatus                      = 'IP'
    or CmplRqRsltProcessingStatus                      = 'RE'
    or CmplRqRsltProcessingStatus                      = 'OD'
  )