P_PriceAndQuantityVariance

@AbapCatalog.sqlViewName: 'PMMPRCQNTYVAR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #CONSUMPTION
@VDM.private: true
@EndUserText.label: 'Price And Quantity Variance'
define view P_PriceAndQuantityVariance
  with parameters
    P_StartDate : bedat,
    P_EndDate   : bedat

  as select from I_PurchaseOrderHistory
  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 PurchaseOrder,

  key PurchaseOrderItem,

      _PurchaseOrderItem.PurchaseOrderQuantityUnit,

      _PurchaseOrderItem.NetAmount,
      _PurchaseOrderItem.OrderQuantity,

      PurchaseOrderTransactionType,
      _PurchaseOrder.PurchasingCompletenessStatus,

      _PurchaseOrderItem.DocumentCurrency      as Currency,
      _PurchaseOrderItem.PurchaseOrderCategory as PurchasingDocumentCategory,

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

      _PurchaseOrderItem.Plant,
      _PurchaseOrderItem.Material,
      _PurchaseOrderItem.MaterialGroup,
      _PurchaseOrderItem.CompanyCode,

      _PurchaseOrderItem.NetPriceAmount,
      _PurchaseOrderItem.NetPriceQuantity,

      PostingDate,


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

      case _PurchaseOrderItem.IsReturnsItem
          when 'X' then case DebitCreditCode
                          when 'S' then -1 * QtyInPurchaseOrderPriceUnit
                          when 'H' then QtyInPurchaseOrderPriceUnit
                          else 0
                        end
          else  case DebitCreditCode
                  when 'S' then QtyInPurchaseOrderPriceUnit
                  when 'H' then -1 * QtyInPurchaseOrderPriceUnit
                  else 0
                end
       end                                     as DeliveredQuantity

}
where
         _PurchaseOrderItem.PurchasingDocumentDeletionCode =  ''
  and    _PurchaseOrder.PurchasingDocumentDeletionCode     =  ''
  and    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'
       )
     )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_PURCHASEORDER",
"I_PURCHASEORDERHISTORY",
"I_PURCHASEORDERITEM"
],
"ASSOCIATED":
[
"I_PURCHASEORDER",
"I_PURCHASEORDERITEM"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/