P_PurFutureSpend

@AbapCatalog.sqlViewName: 'PMMPOFUTRSPND'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Purchasing Future Spend'
@VDM.private: true
@VDM.viewType: #CONSUMPTION
@AbapCatalog.preserveKey:true
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.personalData.blocking: #NOT_REQUIRED
define view P_PurFutureSpend

with parameters
  P_Date : sydatum

as select from I_PurchaseOrderItem as PurchaseOrderItem
inner join I_PurchaseOrder as PurchaseOrder on PurchaseOrderItem.PurchaseOrder = PurchaseOrder.PurchaseOrder
inner join I_PurchaseOrderScheduleLine as ScheduleLine on PurchaseOrderItem.PurchaseOrder = ScheduleLine.PurchaseOrder
                                                       and PurchaseOrderItem.PurchaseOrderItem = ScheduleLine.PurchaseOrderItem

 // left outer join I_CalendarDate as CalendarDate on ScheduleLine.SchedLineStscDeliveryDate = CalendarDate.CalendarDate

{
  key PurchaseOrderItem.PurchaseOrder,
  key PurchaseOrderItem.PurchaseOrderItem,
  key ScheduleLine.ScheduleLine,

      PurchaseOrderItem.PurchaseOrderCategory,
      PurchaseOrderItem.PurchaseOrderItemCategory,

      @ObjectModel.foreignKey.association: '_PurchaseRequisition'
      PurchaseOrderItem.PurchaseRequisition,
      @ObjectModel.foreignKey.association: '_PurchaseRequisitionItem'
      PurchaseOrderItem.PurchaseRequisitionItem,
      PurchaseOrderType,
      PurchaseOrder.PurchaseOrderDate,
//      CalendarDate.CalendarMonth,

//      CalendarDate.CalendarQuarter,

//      CalendarDate.CalendarWeek,

//      CalendarDate.CalendarYear,

//      CalendarDate.YearQuarter,


      PurchaseOrder.Supplier,
      PurchaseOrder.PurchasingGroup,
      PurchaseOrder.PurchasingOrganization,
      PurchaseOrder.CompanyCode,
      PurchaseOrderItem.CompanyCode as ReceivingCompanyCode,

      PurchaseOrderItem.MaterialGroup,
      PurchaseOrderItem.Material,

      PurchaseOrderItem.Plant,

      PurchaseOrderItem.ProductType,
      PurchaseOrderItem.ServicePerformer,

      ScheduleLineDeliveryDate,
      SchedLineStscDeliveryDate,
      PerformancePeriodStartDate,
      PerformancePeriodEndDate,
      ScheduleLineDeliveryTime,

      @Semantics.quantity.unitOfMeasure: 'PurchaseOrderQuantityUnit'
      @DefaultAggregation: #SUM
      ScheduleLineOrderQuantity,

      @Semantics.quantity.unitOfMeasure: 'PurchaseOrderQuantityUnit'
      @DefaultAggregation: #SUM
      RoughGoodsReceiptQty,

      PurchaseOrder.CreatedByUser,

      PurchaseOrderItem.PurchaseContract,
      PurchaseOrderItem.PurchaseContractItem,

      PurchaseOrderItem.MultipleAcctAssgmtDistribution,
      PurchaseOrderItem.AccountAssignmentCategory,

      @Semantics.unitOfMeasure: true
      PurchaseOrderItem.PurchaseOrderQuantityUnit       as PurchaseOrderQuantityUnit,

      @Semantics.currencyCode: true
      PurchaseOrderItem.DocumentCurrency                as Currency,

      case when NetPriceQuantity !=0 then
       cast ( div(PurchaseOrderItem.NetPriceAmount, PurchaseOrderItem.NetPriceQuantity) as abap.curr( 21, 5 ))  //3200874

       else
       cast(0 as abap.curr(21,5))
       end                                          as NetPriceAmount,

      OrderItemQtyToBaseQtyNmrtr,
      OrderItemQtyToBaseQtyDnmntr,
      OrderPriceUnitToOrderUnitNmrtr,
      OrdPriceUnitToOrderUnitDnmntr,
      PurchaseOrderItem._PurchaseRequisition,
      PurchaseOrderItem._PurchaseRequisitionItem


}where  PurchaseOrder.PurchasingDocumentDeletionCode = ''
    and PurchaseOrder.PurchasingCompletenessStatus   = ''
    and PurchaseOrder.ReleaseIsNotCompleted          = ''
    and PurchaseOrderItem.PurchasingDocumentDeletionCode <> 'L' //3393611

    and SchedLineStscDeliveryDate  <= dats_add_days( :P_Date, 180, 'FAIL' )
    and SchedLineStscDeliveryDate > $parameters.P_Date
    and SupplyingPlant                     =  ''            //^3373213

    and not(
        ProductType                  =  '2'
         or(
            PurchaseOrderItemCategory   =  '9'
         or PurchaseOrderItemCategory   =  '1'
         or PurchaseOrderItemCategory   =  'A'              //v3373213

       )
     )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_PURCHASEORDER",
"I_PURCHASEORDERITEM",
"I_PURCHASEORDERSCHEDULELINE"
],
"ASSOCIATED":
[
"I_PURCHASEREQUISITION",
"I_PURCHASEREQUISITIONITEM"
],
"BASE":
[
"I_PURCHASEORDERITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/