P_PurContractPriceVariance2

@AbapCatalog.sqlViewName: 'PMMPCPRCVAR2'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType : #CONSUMPTION
@VDM.private: true
@AccessControl.personalData.blocking: #NOT_REQUIRED
@AbapCatalog.viewEnhancementCategory: [#PROJECTION_LIST,#GROUP_BY] // Added for extensibility


define view P_PurContractPriceVariance2
  with parameters
    P_DisplayCurrency : displaycurrency,
    P_Material        : matnr,
    P_StartDate       : vdm_validitystart,
    P_EndDate         : vdm_validityend
    
    
  as select from    P_PurContractPriceVariance1( P_Material : $parameters.P_Material, P_StartDate: $parameters.P_StartDate,
                                                         P_EndDate: $parameters.P_EndDate )     as PriceChange

    left outer join P_PurchaseContractTargetAmount ( P_DisplayCurrency : $parameters.P_DisplayCurrency,
                                                     P_Material : $parameters.P_Material , P_StartDate: $parameters.P_StartDate,
                                                         P_EndDate: $parameters.P_EndDate  ) as PurchaseContract on PriceChange.PurchaseContract = PurchaseContract.PurchaseContract
    left outer join I_PurchaseContractHistory                                               as ReleaseHistory   on  PriceChange.PurchaseContract     = ReleaseHistory.PurchaseContract
                                                                                                                and PriceChange.PurchaseContractItem = ReleaseHistory.PurchaseContractItem

    left outer join I_CalendarDate                                                          as CalendarDate     on PriceChange.DocumentChangeDate = CalendarDate.CalendarDate


{
  key PriceChange.PurchaseContract,
  key PriceChange.PurchaseContractItem,
  key ChangeDocument,

      PriceChange.PurchasingOrganization,
      PriceChange.PurchasingGroup,
      PriceChange.Supplier,
      PriceChange.CompanyCode,

      Material,
      MaterialGroup,
      PriceChange.Plant,
      PurchasingCategory,

      //cast(cast(PurchaseContractTargetAmount as abap.curr( 15, 2 )) as mm_a_target_amount) as PurchaseContractTargetAmount,

      PurchaseContractTargetAmount,
      case when  IsDocumentChanged = 'X'and NumberOfChanges = 0
              then PriceChange.DocumentCurrency
           else Currency end as Currency,

      PriceChange.CreationDate,
      NumberOfChanges,
      NumberOfPriceChanges,
      TotalNumberOfChanges,
      OrderQuantityUnit,
      TargetQuantity,

      DocumentChangeDate,

      avg( case when DocumentCurrency is not null then
                currency_conversion(
                  amount => ContractNetPriceAmount,
                  source_currency => DocumentCurrency,
                  target_currency => $parameters.P_DisplayCurrency,
                  exchange_rate_date => DocumentChangeDate )
               else 0
          end )              as ContractNetPriceAmount,
          
      sum( case when ReleaseOrderCurrency is not null then
                currency_conversion( amount => ReleaseOrderItemNetAmount,
                  source_currency => ReleaseOrderCurrency,
                  target_currency => $parameters.P_DisplayCurrency,
                  exchange_rate_date => ReleaseOrderDate )
                else 0
      end ) as ReleaseOrderItemNetAmount,
      
      QuantityInBaseUnit,
      NetPriceQuantity,
      OrderPriceUnit

}
group by

  PriceChange.PurchaseContract,
  PriceChange.PurchaseContractItem,
  ChangeDocument,

  PriceChange.PurchasingOrganization,
  PriceChange.PurchasingGroup,
  PriceChange.Supplier,
  PriceChange.CompanyCode,

  Material,
  MaterialGroup,
  PriceChange.Plant,
  PurchasingCategory,
  PurchaseContractTargetAmount,
  IsDocumentChanged,
  PriceChange.CreationDate,
  NumberOfChanges,
  NumberOfPriceChanges,
  TotalNumberOfChanges,
  PriceChange.DocumentCurrency,
  Currency,

  OrderQuantityUnit,
  TargetQuantity,

  DocumentChangeDate,
  QuantityInBaseUnit,
  NetPriceQuantity,
  OrderPriceUnit
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CALENDARDATE",
"I_PURCHASECONTRACTHISTORY",
"P_PURCHASECONTRACTTARGETAMOUNT",
"P_PURCONTRACTPRICEVARIANCE1"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/