P_ProcurementProductProposal

@AbapCatalog.sqlViewName: 'PPROCPRODPROPOSL'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@EndUserText.label: 'Procurement Product proposal'

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

@ObjectModel:
  { usageType: { serviceQuality: #A,
                 sizeCategory : #L,
                 dataClass: #MASTER
               },
    semanticKey:['Product', 'AlternativeUnit']
  }

@ClientHandling.algorithm: #SESSION_VARIABLE
define view P_ProcurementProductProposal
  as select from    I_ProductUnitsOfMeasure   as uom1
    left outer join I_ProductUnitsOfMeasure   as uom2      on  uom1.Product         = uom2.Product
                                                                                                  and  uom1.AlternativeUnit = uom2.LowerLevelPackagingUnit

    inner join      I_ActiveLogisticalProduct as logprdct  on uom1.Product = logprdct.Product

  //  This left outer join is used to filter out already "existing UOMs", means there exist a Procurment Product

  //  which has this UOM as order unit. Note that if for that Porecurement Product the Base Unit is equal to

  //  the Order Unit, the Order Unit is left blank (MARA field BSTME == ''). That’s why this case needs to be handled

  //  explictly in the second part of the join condition.

    left outer join I_ActiveLogisticalProduct as procprdct on (         uom1.Product               = procprdct.SalesProduct
                                                                                                            and uom1.AlternativeUnit = procprdct.PurchaseOrderUnit  )
                                                                                                    or (         uom1.Product              = procprdct.SalesProduct
                                                                                                            and procprdct.PurchaseOrderUnit = ''
                                                                                                            and uom1.AlternativeUnit        = procprdct.BaseUnit   )

  association [0..1] to I_UnitOfMeasureText       as _AlternativeUnitText     on  $projection.AlternativeUnit   = _AlternativeUnitText.UnitOfMeasure
                                                                                                                            and _AlternativeUnitText.Language = $session.system_language

  association [1..1] to I_ProductTypeNumberRanges as _ProductTypeNumberRanges on  logprdct.ProductType = _ProductTypeNumberRanges.ProductType

{
  key uom1.Product                                                                                  as  Product,
  key uom1.AlternativeUnit                                                                      as  AlternativeUnit,
      _AlternativeUnitText.UnitOfMeasureName                                      as  AlternativeUnitName,
      uom1.QuantityNumerator                                                                 as  QuantityNumerator,
      uom1.QuantityDenominator                                                             as  QuantityDenominator,

      // No external material number assignment defined for the current product (more precisely for its product type)

      //  => Set indicator, that only internal product number assignment is allowed

      case _ProductTypeNumberRanges.ExternalProductNumberRange
        when '' then 'true'
        else         'false'
      end                                                                                                        as  OnlyIntProdNmbrAssgmtIsAllwd
}
where
  // Select only leaves (i.e. nodes which have no child).

      uom2.AlternativeUnit               is null
  // Because the MARM-Hierarchy is special in the sense that the children of the root node

  // (which represents the basic UoM) do NOT point to him. Therefore, the root would also

  // be selected and is filtered out with the help of the numerator/denominator (conversion

  // factor=1 => basic UoM).

  // Furthermore, we do not want to return leaves, which have a conversion factor < 1.

  and not uom1.QuantityNumerator         <= uom1.QuantityDenominator
  // Filter out entries belonging to a Logistical Procurement-Product.

  and logprdct.LogisticalProductCategory <> 'P'
  // Filter out the PurchaseOrderUnits for which a Procurement Product already exists

  and procprdct.PurchaseOrderUnit        is null
  // Each Sales Product acts also as its own Procuremtn Product. That's why we filter out

  // the PurchaseOrderUnit of the Sales Product.

  and uom1.AlternativeUnit               <> logprdct.PurchaseOrderUnit
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ACTIVELOGISTICALPRODUCT",
"I_PRODUCTTYPENUMBERRANGES",
"I_PRODUCTUNITSOFMEASURE",
"I_UNITOFMEASURETEXT"
],
"ASSOCIATED":
[
"I_PRODUCTTYPENUMBERRANGES",
"I_UNITOFMEASURETEXT"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/