P_PURCHASEORDERPRICEVARIANCE

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

  with parameters
    P_DisplayCurrency           : displaycurrency,
    P_Material                  : matnr,
    P_StartDate       : vdm_validitystart,
    P_EndDate         : vdm_validityend
    

    
  as select from I_PurchaseOrderItem         as PurchaseOrderItem
  
  inner join      I_PurchaseOrder            as PurchaseOrder     on PurchaseOrder.PurchaseOrder = PurchaseOrderItem.PurchaseOrder
  
  left outer join I_CalendarDate             as CalendarDate on PurchaseOrderDate = CalendarDate.CalendarDate
  
  association [0..1] to I_BR_Domain  as _DocText   on $projection.DocNumber               = _DocText.DomainValue
                                                           and _DocText.Name              = 'MM_A_DOCTYPE'
                                                           and _DocText.Language          = $session.system_language
  
    
{
   key PurchaseOrder.PurchaseOrder,
   key cast( PurchaseOrderItem.PurchaseOrderItem as vdm_purchaseorderitem )         as  PurchaseOrderItem,
 
      PurchaseOrder.PurchaseOrderType,
      
      /* Supplier */
      PurchaseOrder.Supplier,
      
      /* Org Data */
      PurchaseOrder.PurchasingOrganization,
      PurchaseOrder.PurchasingGroup,
      PurchaseOrder.CompanyCode,

      PurchaseOrder.PurchaseOrderDate,
      
      /* Item Data */
      PurchaseOrderItem.MaterialGroup,
      PurchaseOrderItem.Material,
      PurchaseOrderItem.Plant,
      PurchaseOrderItem.PurchaseContract,
      PurchaseOrderItem.PurchasingInfoRecord,
      
      cast('3' as mm_a_doctype ) as DocNumber,
      
      PurchaseOrderItem.PurchaseOrderQuantityUnit,
      PurchaseOrderItem.OrderQuantity,

      PurchaseOrderItem.DocumentCurrency,
      cast( :P_DisplayCurrency as displaycurrency )                                as  DisplayCurrency,
  //    NetPriceAmount,

      
      cast( case when PurchaseOrderItem.DocumentCurrency != '' or PurchaseOrderDate != ''
                  then  cast( currency_conversion(
                                         amount             => NetPriceAmount,
                                         source_currency    => PurchaseOrderItem.DocumentCurrency,
                                         target_currency    => :P_DisplayCurrency,
                                         exchange_rate_date => PurchaseOrderDate,
                                         exchange_rate_type => 'M',
                                         error_handling => 'FAIL_ON_ERROR'
                                      ) as purchase_order_net_amount ) 
        
                  else        0
               end                                    as mm_pur_ana_mat_avg_price ) as  NetPriceAmount,
      NetAmount,  
//      cast( case when PurchaseOrderItem.DocumentCurrency != '' or PurchaseOrder.PurchaseOrderDate != ''

//                  then  cast( currency_conversion(

//                                         amount             => NetPriceAmount,

//                                         source_currency    => PurchaseOrderItem.DocumentCurrency,

//                                         target_currency    => :P_DisplayCurrency,

//                                         exchange_rate_date => PurchaseOrder.PurchaseOrderDate,

//                                         exchange_rate_type => 'M',

//                                         error_handling => 'FAIL_ON_ERROR'

//                                      ) as purchase_order_net_amount ) 

//        

//                  else        0

//               end as purchase_order_net_amount )                                    as  NetPriceAmount,  

//               

      cast(currency_conversion(
                         amount             => NetAmount,
                         source_currency    => PurchaseOrderItem.DocumentCurrency,
                         target_currency    => :P_DisplayCurrency,
                         exchange_rate_date => PurchaseOrder.PurchaseOrderDate,
                         exchange_rate_type => 'M',
                         error_handling => 'FAIL_ON_ERROR'
                       )
      as purchase_order_net_amount )                                           as  PurOrdNetAmountInDisplayCrcy,
      
      case
           when OrdPriceUnitToOrderUnitDnmntr = 0
                then 0 
                else DIVISION(OrderPriceUnitToOrderUnitNmrtr, OrdPriceUnitToOrderUnitDnmntr, 1 ) * PurchaseOrderItem.OrderQuantity
                end
        as QuantityInBaseUnit,
        
       PurchaseOrderItem.NetPriceQuantity,
       OrderPriceUnit,
      
      _Plant,
      _PurchasingGroup,
      _PurchasingOrganization,
      _Supplier,
      PurchaseOrder._CompanyCode,
      _Material,
      _MaterialGroup,

      /* Dates */
      CalendarYear,
      CalendarQuarter,
      CalendarMonth,
      CalendarWeek,
      YearQuarter,
      YearMonth,
      YearWeek,
      _DocText


}
where PurchaseOrderItem._PurchaseOrder.PurchasingDocumentDeletionCode = ''
  and PurchaseOrderItem._PurchaseOrder.ReleaseIsNotCompleted          = ''
  and PurchaseOrderItem._PurchaseOrder.PurchasingCompletenessStatus   = ''
  and PurchaseOrderItem.PurchasingDocumentDeletionCode                = ''
  and Material = $parameters.P_Material
  and PurchaseOrderDate between $parameters.P_StartDate and $parameters.P_EndDate
  and not ( PurchaseOrderItem.IsStatisticalItem = 'X' and PurchaseOrderItem.Material= '' )