P_InvoicePriceVariance1

@AbapCatalog.sqlViewName: 'PMMINVPRCVARC1'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType : #CONSUMPTION
@VDM.private: true
@AccessControl.personalData.blocking: #NOT_REQUIRED

define view P_InvoicePriceVariance1 
with parameters 
      P_Material        : matnr,
      P_StartDate       : vdm_validitystart,
      P_EndDate         : vdm_validityend  
      
as select from I_PurchaseOrderHistory 

 
{
        key PurchaseOrder,
        key PurchaseOrderItem,
        key AccountAssignmentNumber,
        key PurchaseOrderTransactionType,
        key MaterialDocumentYear,
        key MaterialDocument,
        key MaterialDocumentItem,
        
        PostingDate,
       
        DebitCreditCode,
        
        _PurchaseOrderItem.CompanyCode,
        Currency,
        
        _PurchaseOrderItem.IsReturnsItem,
 
        _PurchaseOrderItem._PurchaseOrder.PurchasingOrganization,
        _PurchaseOrderItem._PurchaseOrder.PurchasingGroup,
        _PurchaseOrderItem._PurchaseOrder.Supplier,
        _PurchaseOrderItem._PurchaseOrder.PurchaseOrderType,
        _PurchaseOrderItem.Material,
        _PurchaseOrderItem.MaterialGroup,
        _PurchaseOrderItem.Plant,
        
        Quantity,
        
        QtyInPurchaseOrderPriceUnit,
        _PurchaseOrderItem.OrderPriceUnit,
        QuantityInBaseUnit,
        _PurchaseOrderItem.BaseUnit,
                
        _PurchaseOrderItem.OrderItemQtyToBaseQtyDnmntr,
        _PurchaseOrderItem.OrderItemQtyToBaseQtyNmrtr,
        _PurchaseOrderItem.OrderPriceUnitToOrderUnitNmrtr,
        _PurchaseOrderItem.OrdPriceUnitToOrderUnitDnmntr,
        
        _PurchaseOrderItem.NetPriceAmount,
        _PurchaseOrderItem.NetPriceQuantity,
        
        // PurchaseOrderAmount amount in EKBE is wrbtr, is actual invoice amount

        
        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 QtyInPurchaseOrderPricUnit,
         
      @Semantics.unitOfMeasure: true
      PurchaseOrderQuantityUnit,
     
         /*Associations*/
      _PurchaseOrderItem._Material,
      _PurchaseOrderItem._MaterialGroup,
      _PurchaseOrderItem._Plant,
      _PurchaseOrderItem._PurchaseOrder._Supplier,
      _PurchaseOrderItem._PurchaseOrder._PurchasingOrganization,
      _PurchaseOrderItem._PurchaseOrder._PurchasingGroup,
      _PurchaseOrderItem._CompanyCode  
            
}where   
   _PurchaseOrderItem.PurchasingDocumentDeletionCode = '' 
        and PurchaseOrderTransactionType = '2' 
        and _PurchaseOrderItem._PurchaseOrder.PurchasingCompletenessStatus != 'X'
        and _PurchaseOrderItem._PurchaseOrder.ReleaseIsNotCompleted != 'X'
        and Material = $parameters.P_Material and PostingDate between $parameters.P_StartDate and $parameters.P_EndDate
        and not ( _PurchaseOrderItem.IsStatisticalItem = 'X' and _PurchaseOrderItem.Material= '' )
         
         
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_PURCHASEORDER",
"I_PURCHASEORDERHISTORY",
"I_PURCHASEORDERITEM"
],
"ASSOCIATED":
[
"I_COMPANYCODE",
"I_MATERIAL",
"I_MATERIALGROUP",
"I_PLANT",
"I_PURCHASINGGROUP",
"I_PURCHASINGORGANIZATION",
"I_SUPPLIER"
],
"BASE":
[
"I_PURCHASEORDER",
"I_PURCHASEORDERITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/