C_PriceVarianceQuery

@AbapCatalog.sqlViewName: 'CMMSEBYPRICEQRY'
@EndUserText.label: 'Price Variance'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@AccessControl.personalData.blocking: #NOT_REQUIRED
@VDM.viewType: #CONSUMPTION
@OData.publish: true
@Analytics.settings.maxProcessingEffort:#MEDIUM
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory:  #L
@Analytics.query: true
@AbapCatalog.preserveKey:true
@ObjectModel.supportedCapabilities: [ #ANALYTICAL_QUERY]
@ObjectModel.modelingPattern: #ANALYTICAL_QUERY
@Metadata.ignorePropagatedAnnotations:true
define view C_PriceVarianceQuery
  with parameters
    @Consumption.defaultValue: 'EUR'
    P_DisplayCurrency : displaycurrency,

     @Consumption.derivation: { lookupEntity: 'I_SglGregorianCalDateFunction', 
    resultElement: 'DateFunctionStartDate', binding: [ {
      targetParameter: 'P_DateFunction', type: #PARAMETER, value : 'P_DateFunction' } ] 
    } 
    @Consumption.hidden: true
    P_StartDate: vdm_validitystart,
    
    @Consumption.derivation: { lookupEntity: 'I_SglGregorianCalDateFunction', 
    resultElement: 'DateFunctionEndDate', binding: [ {
      targetParameter: 'P_DateFunction', type: #PARAMETER, value : 'P_DateFunction' } ] 
    } 
    @Consumption.hidden: true    
    P_EndDate         : vdm_validityend,

    @Consumption.defaultValue: 'PREVIOUSYEARTODATE'
    @Consumption.valueHelpDefinition: [{
      entity: {
        name:'C_GregorianCalDateFuncVH',
        element:'DateFunction'
      }
    }]
    P_DateFunction    : datefunctionid
  as select from C_PriceVarianceCube( P_DisplayCurrency : $parameters.P_DisplayCurrency,
                                    P_StartDate : $parameters.P_StartDate,
                                    P_EndDate : $parameters.P_EndDate )
{

  @EndUserText.label: 'Purchasing Document'
  @EndUserText.quickInfo: 'Purchasing Document'
  key PurchaseOrder,
  
  @EndUserText.label: 'Purchasing Document Item'
  @EndUserText.quickInfo: 'Purchasing Document Item'
  key PurchaseOrderItem,
  
     @Consumption: {
                      filter: {                    
                                mandatory: false,
                                hidden: true,
                                selectionType: #SINGLE,
                                multipleSelections: false
                              },                  
                      derivation: {
                                    lookupEntity: 'F_ProcmtAnalyticsDataSelMeth',
                                    resultElement: 'PurgDocMigrtnIsCmpltdForAnlyts'
                                  }
                    }
     
      
      @UI.hidden: true
      PurgDocMigrtnIsCmpltdForAnlyts,
      
            @Consumption: {
                 filter: {                   
                           mandatory: false,
                           hidden: true,
                           selectionType: #SINGLE,
                           multipleSelections: false
                         },
                 derivation: {
                               lookupEntity: 'F_SuplrEvalDocCatConfign',
                               resultElement: 'SuplrEvalRelevantDocCategory'
                             }
               }
      @Environment.sql.passValue: true
      @UI.hidden: true
      SuplrEvalRelevantDocCategory,
      
         
  
      @UI.hidden: true
      PurchaseOrderDate,
      CalendarYear,
      CalendarQuarter,
      CalendarMonth,
      CalendarWeek,
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      PurchasingOrganization,
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      PurchasingGroup,
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      CompanyCode,
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      Supplier,
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      SupplierCountry,
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      Region,
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      Plant,
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      Material,
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      MaterialGroup,
     @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      PurchasingCategory,
      PurgCatName,
      
      @EndUserText.label: 'Purchasing Document Category'
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      PurchasingDocumentCategory,
      @Semantics.text: true
      _PurchasingDocumentCatName._Text[1: Language = $session.system_language].PurchasingDocumentCategoryName as PurchasingDocumentCategoryName ,
       
      Currency,
      PurchaseOrderQuantityUnit,
      OrderPriceUnit,
      @UI.hidden: true
      DisplayCurrency,
      @DefaultAggregation: #SUM
      @Consumption.hidden: true
      PriceVarianceInPct,
      @DefaultAggregation: #SUM
      @Consumption.hidden: true
      PriceVarianceAmount,
      @DefaultAggregation: #SUM
      @Consumption.hidden: true
      PurchaseOrderNetPriceAmount,
      @DefaultAggregation: #SUM
      @Consumption.hidden: true
      InvoicePrice,
      @DefaultAggregation: #SUM
      @Consumption.hidden: true
      PriceVarianceScore,
       @DefaultAggregation: #SUM
      NumberOfPurchaseOrders,
      @DefaultAggregation: #SUM
      NumberOfPurchaseOrderItems,
      @DefaultAggregation: #FORMULA
      @EndUserText.label: ' Variance %'
      @AnalyticsDetails.query.formula :' $projection.PriceVarianceInPct /  $projection.NumberOfPurchaseOrderItems'
      cast( 1 as mm_ana_price_var_pct)       as SuplrEvalPriceVarcInPercent,
      @DefaultAggregation: #FORMULA
      @EndUserText.label: ' Price Variance Amount'
      @AnalyticsDetails.query.formula :' $projection.PriceVarianceAmount /  $projection.NumberOfPurchaseOrderItems'
      cast( 1 as abap.curr(13,3) )     as SuplrEvalPriceVarianceAmt,
      @DefaultAggregation: #FORMULA
      @EndUserText.label: 'PO Price Per OPU'
      @AnalyticsDetails.query.formula :' $projection.PurchaseOrderNetPriceAmount /  $projection.NumberOfPurchaseOrderItems'
      cast( 1 as abap.curr(13,3) )     as AmountPerPurchaseOrderPrcUnit,
      @DefaultAggregation: #FORMULA
      @EndUserText.label: 'Invc Price Per OPU'
      @EndUserText.quickInfo: 'Invoice Price Per OPU'
      @AnalyticsDetails.query.formula :' $projection.InvoicePrice /  $projection.NumberOfPurchaseOrderItems'
      cast( 1 as abap.curr(13,3) )     as PerInvoicePrcUnitAmount,
      @DefaultAggregation: #SUM
      @EndUserText.label: 'Delivered Quantity'
      DeliveredQuantity,
      @DefaultAggregation: #SUM
      OrderedQuantity,
      @DefaultAggregation: #FORMULA
      @EndUserText.label: 'Variance Score'
      @AnalyticsDetails.query.formula :' $projection.PriceVarianceScore /  $projection.NumberOfPurchaseOrderItems'
      cast( 1 as mm_a_variance_score ) as PriceVarianceScoreValue
      

}