I_ProdToExplicitAsstmtModule

@AbapCatalog.sqlViewName: 'IPRODTOEXPLASM'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@EndUserText.label: 'Product Assignment to Assortment Module'

@ClientHandling.algorithm: #SESSION_VARIABLE

@AccessControl: {
  authorizationCheck: #CHECK,
  personalData.blocking: #BLOCKED_DATA_EXCLUDED
}

@VDM: {
  viewType: #COMPOSITE,

  lifecycle: {
    contract: {
      type: #SAP_INTERNAL_API
    }
  }
}
@ObjectModel: {
    usageType: {
        serviceQuality: #C,
        sizeCategory: #L,
        dataClass: #MASTER
   }
}

define view I_ProdToExplicitAsstmtModule
  as select from    I_ExplicitAssortmentModule    as module
    inner join      I_ExplicitAsstmtModuleVersion as version             on module.AssortmentModuleUUID = version.AssortmentModuleUUID
    inner join      I_ProductToAssortmentModule   as product_assignments on version.AssortmentModuleVersionUUID = product_assignments.AssortmentModuleUUID
    
  
    association [0..1] to I_ExplctAsstmtMdlExclsnRatio  as _ExclusionRatio      on  $projection.AssortmentModuleUUID = _ExclusionRatio.AssortmentModuleGroupUUID
                                                                         and $projection.Product = _ExclusionRatio.Product
  association [1..*] to I_ProdAssgblToExplctMdlVersTP as _ModuleVersion on  $projection.AssortmentModuleName = _ModuleVersion.AssortmentModuleName
                                                                        and $projection.Product              = _ModuleVersion.Product
  association [1..*] to I_AssortmentModuleText        as _Text          on  _Text.AssortmentModuleUUID = $projection.AssortmentModuleUUID
{
  key module.AssortmentModuleUUID,
  key product_assignments.Product                                                                                                as Product,
  key module.AssortmentModuleName                                                                                                as AssortmentModuleName,
      product_assignments.ProductIsMandatoryInAssortment,
      cast ( min( product_assignments.ValidityStartDate ) as rfm_asm_valid_from preserving type )                                as ValidityStartDate,
      cast ( max( product_assignments.ValidityEndDate )   as rfm_asm_valid_to preserving type )                                  as ValidityEndDate,
      @Semantics.systemDateTime.lastChangedAt:true
      cast ( max( product_assignments.LastChangeDateTime )  as tzntstmps preserving type  )                                      as LastChangeDateTime,
      max( product_assignments.LastChangeDate )                                                                                  as LastChangeDate,
      _ModuleVersion,
      _Text,
      product_assignments._Product,
      _ExclusionRatio
}
group by
  module.AssortmentModuleUUID,
  product_assignments.Product,
  ProductIsMandatoryInAssortment,
  module.AssortmentModuleName