C_ExistingProcurementProduct

@AbapCatalog:
  { sqlViewName: 'CEXISTPRCPROD',
    compiler.compareFilter: true,
    preserveKey: true
  }

@VDM.viewType: #CONSUMPTION
@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Existing procurement products'
@Metadata.allowExtensions: true

@ObjectModel:
  { usageType: { serviceQuality: #C,
                 sizeCategory : #L,
                 dataClass: #MASTER
               },
    semanticKey:['Product','SalesProduct', 'PurchaseOrderUnit']
  }

//@Search.searchable: true

define view C_ExistingProcurementProduct
  as select from    I_ActiveLogisticalProduct as activeLogisticalProduct
    left outer join I_ProductUnitsOfMeasure   as salesProdPurchaseOrderUom on  salesProdPurchaseOrderUom.Product         = activeLogisticalProduct.SalesProduct
                                                                           and salesProdPurchaseOrderUom.AlternativeUnit = activeLogisticalProduct.PurchaseOrderUnit
    left outer join I_ProductUnitsOfMeasure   as salesProdIssueUom         on  salesProdIssueUom.Product         = activeLogisticalProduct.SalesProduct
                                                                           and salesProdIssueUom.AlternativeUnit = activeLogisticalProduct.GoodsIssueUnit
    left outer join I_ProductUnitsOfMeasure   as salesProdBaseUom          on  salesProdBaseUom.Product         = activeLogisticalProduct.SalesProduct
                                                                           and salesProdBaseUom.AlternativeUnit = activeLogisticalProduct.BaseUnit
    left outer join I_Product                 as salesProduct              on salesProduct.Product = activeLogisticalProduct.SalesProduct


  association [1..1] to C_ExistingOrPotntlSalesProduct as _salesprod                     on  $projection.SalesProduct = _salesprod.Product
  association [1..1] to I_LogisticalProductCatText     as _LogisticalProductCategoryText on  $projection.LogisticalProductCategory   = _LogisticalProductCategoryText.LogisticalProductCategory
                                                                                         and _LogisticalProductCategoryText.Language = $session.system_language

{
      //      @Search: { defaultSearchElement: true,

      //                 fuzzinessThreshold: 1,

      //                 ranking: #HIGH }

      @ObjectModel.text.element:  [ 'ProductDescription' ]
  key activeLogisticalProduct.Product                                                                                                         as      Product,

      case activeLogisticalProduct.LogisticalProductCategory
        when '' then activeLogisticalProduct.Product
        else activeLogisticalProduct.SalesProduct
      end                                                                                                                                     as      SalesProduct,

      //      @Search.ranking: #MEDIUM

      ProductDescription                                                                                                                      as      ProductDescription,

      @Semantics.unitOfMeasure: true
      activeLogisticalProduct.BaseUnit,
      activeLogisticalProduct._BaseUnitOfMeasure,

      activeLogisticalProduct.ProductStandardID,

      @ObjectModel.text.association: '_LogisticalProductCategoryText'
      activeLogisticalProduct.LogisticalProductCategory,
      _LogisticalProductCategoryText,

      //   If the BaseUnit is also the PurchaseOrderUnit the related fields for the PurchaseOrderUnit are empty in P_LogisticalProduct and have to be replaced with the BaseUnit fields

      @Semantics.unitOfMeasure: true
      case PurchaseOrderUnit
        when '' then activeLogisticalProduct.BaseUnit
        else PurchaseOrderUnit
      end                                                                                                                                     as      PurchaseOrderUnit,

      case PurchaseOrderUnit
        when '' then _BaseUnitText.UnitOfMeasureName
        else PurchaseOrderUnitName
      end                                                                                                                                     as      PurchaseOrderUnitName,
      case PurchaseOrderUnit
        when '' then 1
        else OrderToBaseQuantityDnmntr
      end                                                                                                                                     as      OrderToBaseQuantityDnmntr,
      case PurchaseOrderUnit
        when '' then 1
        else OrderToBaseQuantityNmrtr
      end                                                                                                                                     as      OrderToBaseQuantityNmrtr,

      //   If the BaseUnit is also the GoodsIssueUnit the related fields for the GoodsIssueUnit are empty in P_LogisticalProduct and have to be replaced with the BaseUnit fields

      @Semantics.unitOfMeasure: true
      case GoodsIssueUnit
        when '' then activeLogisticalProduct.BaseUnit
        else GoodsIssueUnit
      end                                                                                                                                     as      GoodsIssueUnit,
      case GoodsIssueUnit
        when '' then _BaseUnitText.UnitOfMeasureName
        else GoodsIssueUnitName
      end                                                                                                                                     as      GoodsIssueUnitName,
      case GoodsIssueUnit
        when '' then 1
        else GoodsIssueToBaseQuantityDnmntr
      end                                                                                                                                     as      GoodsIssueToBaseQuantityDnmntr,
      case GoodsIssueUnit
        when '' then 1
        else GoodsIssueToBaseQuantityNmrtr
      end                                                                                                                                     as      GoodsIssueToBaseQuantityNmrtr,

      //  In case the product in question IS a Procurement Product, activeLogisticalProduct.SalesProduct is correctly filled, so that the join conditions for

      //  salesProdPurchaseOrderUom and salesProdIssueUom respectively work as intended. Note however, that in case the PurchaseOrder UoM or GoodsIssue UoM

      //  equals the Base UoM, the former are NOT filled, so that in that case we have to use the salesProdBaseUom Join.

      //  In case the product in question IS NOT a Procurement Product, we take over the data from the products own

      //  OrderToBaseQuantityNmrtr, OrderToBaseQuantityDnmntr, GoodsIssueToBaseQuantityNmrtr, GoodsIssueToBaseQuantityDnmntr. This means we also have to

      //  repeat the logic for first testing PurchaseOrderUnit and GoodsIssueUnit resp. as already done above.

      case activeLogisticalProduct.LogisticalProductCategory
        when 'P' then
          case activeLogisticalProduct.PurchaseOrderUnit
            when '' then salesProdBaseUom.QuantityNumerator
            else salesProdPurchaseOrderUom.QuantityNumerator
          end
        else case PurchaseOrderUnit
               when '' then 1
               else OrderToBaseQuantityNmrtr
             end
      end                                                                                                                                     as      OrderToSalesProdBaseUoMNmrtr,
      case activeLogisticalProduct.LogisticalProductCategory
        when 'P' then 
          case activeLogisticalProduct.PurchaseOrderUnit
            when '' then salesProdBaseUom.QuantityDenominator
            else salesProdPurchaseOrderUom.QuantityDenominator
          end
        else case PurchaseOrderUnit
               when '' then 1
               else OrderToBaseQuantityDnmntr
             end
      end                                                                                                                                     as      OrderToSalesProdBaseUoMDnmntr,
      case activeLogisticalProduct.LogisticalProductCategory
        when 'P' then 
          case activeLogisticalProduct.GoodsIssueUnit
            when '' then salesProdBaseUom.QuantityNumerator
            else salesProdIssueUom.QuantityNumerator
          end
        else case GoodsIssueUnit
                when '' then 1
                else GoodsIssueToBaseQuantityNmrtr
             end
      end                                                                                                                                     as      IssueToSalesProdBaseUoMNmrtr,
      case activeLogisticalProduct.LogisticalProductCategory
        when 'P' then 
          case activeLogisticalProduct.GoodsIssueUnit
            when '' then salesProdBaseUom.QuantityDenominator
            else salesProdIssueUom.QuantityDenominator
          end
        else case GoodsIssueUnit
               when '' then 1
               else GoodsIssueToBaseQuantityDnmntr
             end
      end                                                                                                                                     as      IssueToSalesProdBaseUoMDnmntr,

      //  In case the product in question is not a Logistical Product (activeLogisticalProduct.LogisticalProductCategory = ''), activeLogisticalProduct.SalesProduct

      //  is not filled and therefore the join condition for salesProduct does not work as intended. Therefore, in this case we take over the BaseUnit from

      //  the product directly.

      case activeLogisticalProduct.LogisticalProductCategory
        when '' then activeLogisticalProduct.BaseUnit
        else salesProduct.BaseUnit
      end                                                                                                                                     as      SalesProductBaseUoM,

      @Semantics.systemDate.createdAt: true
      activeLogisticalProduct.CreationDate                                                                                                    as      CreationDate,

      @Semantics.user.createdBy: true
      activeLogisticalProduct.CreatedByUser                                                                                                   as      CreatedByUser,

      @ObjectModel.readOnly: true
      @Semantics.systemDate.lastChangedAt: true
      activeLogisticalProduct.LastChangeDate                                                                                                  as      LastChangeDate,

      @Semantics.systemTime.lastChangedAt: true
      activeLogisticalProduct.LastChangeTime                                                                                                  as      LastChangeTime,

      @Semantics.systemDateTime.lastChangedAt: true
      activeLogisticalProduct.LastChangeDateTime                                                                                              as      LastChangeDateTime,

      @ObjectModel.association.type: [#TO_COMPOSITION_PARENT, #TO_COMPOSITION_ROOT]
      _salesprod

}