P_PriceVariance4

@AbapCatalog.sqlViewName: 'PMMPRICEVAR4'
@VDM.viewType: #CONSUMPTION
@VDM.private : true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #NOT_REQUIRED
@EndUserText.label: 'Price Variance 4'
define view P_PriceVariance4
  with parameters
    P_StartDate : budat,
    P_EndDate   : budat
  as select from I_PurchaseOrderHistory as History
  association [1..1] to I_PurchaseOrder     as _PurchaseOrder     on  $projection.PurchaseOrder = _PurchaseOrder.PurchaseOrder
  association [1..1] to I_PurchaseOrderItem as _PurchaseOrderItem on  $projection.PurchaseOrder     = _PurchaseOrderItem.PurchaseOrder
                                                                  and $projection.PurchaseOrderItem = _PurchaseOrderItem.PurchaseOrderItem


{
  key History.PurchaseOrder,
  key History.PurchaseOrderItem,

      _PurchaseOrder.PurchaseOrderDate,
      _PurchaseOrder.PurchasingOrganization,
      _PurchaseOrder.PurchasingGroup,
      _PurchaseOrder.Supplier,
      _PurchaseOrderItem._PurchaseOrder.PurchaseOrderType,

      _PurchaseOrderItem.Material,
      _PurchaseOrderItem.MaterialGroup,
      _PurchaseOrderItem.Plant,
      _PurchaseOrderItem.CompanyCode,
      _PurchaseOrderItem.PurchaseOrderCategory as PurchasingDocumentCategory,

      _PurchaseOrderItem.NetPriceAmount,
      _PurchaseOrderItem.NetPriceQuantity,
      _PurchaseOrderItem.OrderQuantity,
      _PurchaseOrderItem.PurchaseOrderQuantityUnit,

      _PurchaseOrderItem.NetAmount,

      _PurchaseOrder.DocumentCurrency,
      History.Quantity,

      History.InvoiceAmtInCoCodeCrcy           as PurchaseOrderAmount,

        History.Currency as InvoiceCurrency,                //3463291


        History.PostingDate as InvoiceDate,                 //3463291


      case _PurchaseOrderItem.IsReturnsItem
          when 'X' then case History.DebitCreditCode
                          when 'S' then -1 * History.PurchaseOrderAmount
                          when 'H' then  History.PurchaseOrderAmount
                          else 0
                        end
          else  case History.DebitCreditCode
                  when 'S' then History.PurchaseOrderAmount
                  when 'H' then -1 * History.PurchaseOrderAmount
                  else 0
                end
      end                                      as InvoiceAmountInDocCurrency,

      case _PurchaseOrderItem.IsReturnsItem
          when 'X' then case DebitCreditCode
                          when 'S' then -1 * History.QtyInPurchaseOrderPriceUnit
                          when 'H' then  History.QtyInPurchaseOrderPriceUnit
                          else 0
                        end
          else  case History.DebitCreditCode
                  when 'S' then History.QtyInPurchaseOrderPriceUnit
                  when 'H' then -1 * History.QtyInPurchaseOrderPriceUnit
                  else 0
                end
       end                                     as QtyInPurchaseOrderPricUnit,
      OrderPriceUnit

}
where
         _PurchaseOrderItem.PurchasingDocumentDeletionCode =  ''
  and    _PurchaseOrder.PurchasingDocumentDeletionCode     =  ''
  and    History.PurchaseOrderTransactionType              =  '2'
  and    _PurchaseOrder.PurchasingCompletenessStatus != 'X'
  and    _PurchaseOrder.ReleaseIsNotCompleted != 'X'
  and(
         _PurchaseOrder.Supplier                           <> ''
    or   _PurchaseOrder.Supplier                           is not null
  ) //2809288

  and    _PurchaseOrder.PurchaseOrderDate                  <= $parameters.P_EndDate
  and    _PurchaseOrder.PurchaseOrderDate                  >= $parameters.P_StartDate
  and(
         _PurchaseOrderItem.IsStatisticalItem              =  ''
    or   _PurchaseOrderItem.Material                       <> ''
  )
  and    not(
       _PurchaseOrderItem.ProductType                      =  '2'
       or(
            _PurchaseOrderItem.PurchaseOrderItemCategory   =  '9'
         or _PurchaseOrderItem.PurchaseOrderItemCategory   =  '1'
         or _PurchaseOrderItem.PurchaseOrderItemCategory   =  'A'
       )
     )