P_InfoRecordPriceVariance

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

define view P_InfoRecordPriceVariance
with parameters 
      P_Material        : matnr,
      P_StartDate       : vdm_validitystart,
      P_EndDate         : vdm_validityend

 as select from I_PurchasingInfoRecord   as InfoRecord
 inner join I_PurgInfoRecdOrgPlantData   as InfoRecord1 on InfoRecord.PurchasingInfoRecord    = InfoRecord1.PurchasingInfoRecord and InfoRecord.CreationDate between $parameters.P_StartDate and $parameters.P_EndDate
 
 left outer join P_ChangeDocumentItem2 ( P_StartDate: $parameters.P_StartDate,
                                                         P_EndDate: $parameters.P_EndDate )  as ChangeItem  on InfoRecord1.PurchasingInfoRecord   = ChangeItem.ChangeDocObject
 
                                                        and InfoRecord1.PurchasingOrganization         = ChangeItem.PurchasingOrganization
                                                        and InfoRecord1.PurchasingInfoRecordCategory   = ChangeItem.PurchasingInfoRecordCategory
                                                        and InfoRecord1.Plant                          = ChangeItem.Plant
                                                        
// left outer join P_InfoRecordSpendAmount1( P_DisplayCurrency: $parameters.P_DisplayCurrency ) as _SpendAmount 

//                                                         on _InfoRecord1.PurchasingInfoRecord           = _SpendAmount.PurchasingInfoRecord

//                                                        and _InfoRecord1.PurchasingOrganization         = _SpendAmount.PurchasingOrganization

//                                                        and _InfoRecord1.PurchasingInfoRecordCategory   = _SpendAmount.PurchasingInfoRecordCategory

//                                                        and _InfoRecord1.Plant                          = _SpendAmount.Plant

                                                        
                                                      
{
    key InfoRecord.PurchasingInfoRecord,
    key InfoRecord1.PurchasingInfoRecordCategory,
    key InfoRecord1.PurchasingOrganization,
    key InfoRecord1.Plant,
    key ChangeDocument,
    
      InfoRecord1.PurchasingGroup,
      InfoRecord.Supplier,

      InfoRecord1.NetPriceAmount as IRNetPriceAmount,

      InfoRecord.CreationDate,
      
      InfoRecord.Material,
      InfoRecord.MaterialGroup,
      case when    
      InfoRecord1.LastReferencingPurchaseOrder = '' and 
      (InfoRecord1.LastReferencingPurOrderItem = '' or InfoRecord1.LastReferencingPurOrderItem = '00000' )
      then ''
      else
      'X' end as BOMHasHeaderInfoRecord,
      ChangeItem.Currency,
       case when  ChangeDocument is not null and ChangeItem.Currency is not null 
              then ChangeItem.Currency
       else InfoRecord1.Currency end as DocumentCurrency,
           
      case when ChangeDocument = '' or ChangeDocument is null
              then InfoRecord.CreationDate
              else
              ChangeItem.CreationDate             
      end as DocumentChangeDate,

      
      case when ChangeDocument is not null
              then cast(ChangeItem.NetPriceAmount + division(cast(ValueAfterDecimal as abap.dec( 21, 2 )), 100,2) as abap.curr( 21, 2 )) 
       else InfoRecord1.NetPriceAmount  end  as NetPriceAmount,
       
       case
           when OrdPriceUnitToOrderUnitDnmntr = 0
                then 0 
                else division(OrderPriceUnitToOrderUnitNmrtr, OrdPriceUnitToOrderUnitDnmntr, 1 ) * StandardPurchaseOrderQuantity
                end
        as QuantityInBaseUnit,
      
      MaterialPriceUnitQty,
      PurchaseOrderPriceUnit,  
       
      IsDocumentChanged,
      NumberOfChanges,
      NumberOfPriceChanges,
      // Associations

      InfoRecord._Material,
      InfoRecord._MaterialGroup,
      InfoRecord1._Plant,
      InfoRecord._Supplier,
      InfoRecord1._PurchasingOrganization,
      InfoRecord1._PurchasingGroup
      
      
} where IsDeleted = ''
    and InfoRecord.Material = $parameters.P_Material