P_QuantityContractWithPOCount

@AbapCatalog.sqlViewName: 'PQTYCONTPOC'
@ClientHandling.algorithm: #SESSION_VARIABLE //Inserted by VDM CDS Suite Plugin

@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality:  #D
@ObjectModel.usageType.sizeCategory: #L
@AbapCatalog.compiler.compareFilter: true
@AccessControl.personalData.blocking: #BLOCKED_DATA_EXCLUDED
@VDM.viewType: #CONSUMPTION
@VDM.private: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Quantity Contract With PO Count'
define view P_QuantityContractWithPOCount
  with parameters
    P_DisplayCurrency : displaycurrency,
    P_StartDate       : vdm_validitystart,
    P_EndDate         : vdm_validitystart

  as select from            I_PurchaseContractItem                                                                            as PurchaseContractItem

    inner join              I_PurchaseContract                                                                                as PurchaseContract       on PurchaseContractItem.PurchaseContract = PurchaseContract.ActivePurchasingDocument

    inner join              I_PurContractClassification                                                                       as ContractClassification on PurchaseContract.PurchaseContractType = ContractClassification.PurchasingDocumentType

    left outer to many join P_ReleaseDocumentationValues(P_DisplayCurrency : $parameters.P_DisplayCurrency)                   as ReleaseDocumentation   on  PurchaseContractItem.PurchaseContract     = ReleaseDocumentation.PurchaseContract
                                                                                                                                                        and PurchaseContractItem.PurchaseContractItem = ReleaseDocumentation.PurchaseContractItem

    left outer to one join  P_POCountInQtyContract(P_StartDate : $parameters.P_StartDate, P_EndDate : $parameters.P_EndDate ) as POCount                on  PurchaseContractItem.PurchaseContract     = POCount.PurchaseContract
                                                                                                                                                        and PurchaseContractItem.PurchaseContractItem = POCount.PurchaseContractItem

{
  key PurchaseContractItem.PurchaseContract,
  key PurchaseContractItem.PurchaseContractItem,
  key ReleaseOrder,
  key ReleaseOrderItem,

      cast( concat(   PurchaseContractItem.PurchaseContract ,
                      concat( '/' , PurchaseContractItem.PurchaseContractItem))
      as formattedcontractitem )                               as FormattedPurchaseContractItem,

      PurchaseContract.Supplier,
      PurchaseContract.PurchasingGroup,
      PurchaseContract.PurchasingOrganization,
      PurchaseContract.CompanyCode,

      PurchaseContract.PurchasingDocumentCategory,
      PurchaseContract.PurchaseContractType,
      PurchasingDocumentItemCategory,

      PurchaseContractItem.Material,
      PurchaseContractItem.MaterialGroup,
      PurchaseContractItem.Plant,
      PurchaseContractItem.DocumentCurrency,

      PurchaseContract.CreatedByUser,
      ValidityEndDate,
      ReleaseOrderDate,

      MultipleAcctAssgmtDistribution,
      ReleaseOrderItemOrderQuantity,
      ReleaseOrderItemNetAmount, //3229617


      @Semantics.currencyCode: true
      cast( $parameters.P_DisplayCurrency as displaycurrency ) as DisplayCurrency,

      @Semantics.amount.currencyCode: 'DisplayCurrency'
      case when NetPriceQuantity > 0 then
      cast(division
      (currency_conversion( amount => PurchaseContractItem.ContractNetPriceAmount,
                                source_currency => PurchaseContractItem.DocumentCurrency,
                                target_currency => $parameters.P_DisplayCurrency,
                                exchange_rate_date => PurchaseContract.CreationDate ) , NetPriceQuantity,5)
       as abap.curr( 21, 5 ) ) //3360997

       else 0 end                                              as ContractNetPriceAmount,

      @Semantics.unitOfMeasure: true
      PurchaseContractItem.OrderQuantityUnit,

      @Semantics.quantity.unitOfMeasure: 'OrderQuantityUnit'
      TargetQuantity,

      @Semantics.quantity.unitOfMeasure: 'OrderQuantityUnit'
      cast(case when POCount.NumberOfPurchaseOrderItems > 1
        then division(TargetQuantity , NumberOfPurchaseOrderItems , 3)
      else TargetQuantity end as ktmng)                        as AverageTargetQuantity


}
where
        ContractClassification.PurchaseContractClassification = '1'
  and   PurchaseContractItem.PurchasingContractDeletionCode   = ''
  and   PurchaseContract.ValidityStartDate                    <= $parameters.P_EndDate
  and   PurchaseContract.ValidityEndDate                      >= $parameters.P_StartDate
  and   PurchaseContract.IsEndOfPurposeBlocked                = ''
  and   PurchasingDocumentItemCategory != '9'
  and   PurchasingDocumentItemCategory != '1'
  and   PurchasingDocumentItemCategory != 'A'
  and   not(
      PurchaseContractItem.DocumentCurrency                   = ''
      or  PurchaseContractItem.DocumentCurrency               is null
    )
  and   not(
      Material                                                = ''
      and IsStatisticalItem                                   = 'X'
    )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_PURCHASECONTRACT",
"I_PURCHASECONTRACTITEM",
"I_PURCONTRACTCLASSIFICATION",
"P_POCOUNTINQTYCONTRACT",
"P_RELEASEDOCUMENTATIONVALUES"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/