C_PriceVariance

@AbapCatalog.sqlViewName: 'CMMPRICEVAR'
@EndUserText.label: 'Supplier Evaluation by Price Variance'
@VDM.viewType: #CONSUMPTION
//Commented as analytical manager no longer supports AVG, COUNT, COUNT_DISTINCT     //2809288

//@Analytics.dataCategory: #CUBE

@OData.publish: true
@AccessControl.authorizationCheck : #CHECK
@AccessControl.personalData.blocking: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE //Inserted by VDM CDS Suite Plugin

@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality:  #D
@ObjectModel.usageType.sizeCategory: #XL
@Metadata.allowExtensions:true

define view C_PriceVariance
  with parameters
  @Consumption.defaultValue: 'EUR'
  @Consumption.valueHelpDefinition: [{
      entity: {
        name:'I_Currency',
        element:'Currency'
      }
    }]
    P_DisplayCurrency           : displaycurrency,

    @Consumption.derivation: { lookupEntity: 'C_SglGregorianCalDateFunction', 
    resultElement: 'DateFunctionStartDate', binding: [ {
      targetParameter: 'P_DateFunction', type: #PARAMETER, value : 'P_DateFunction' },
       { targetParameter : 'P_Language' , type : #SYSTEM_FIELD, value : '#SYSTEM_LANGUAGE'  } ] 
    } 
    @Consumption.hidden: true
    P_StartDate: bedat,
    
    @Consumption.derivation: { lookupEntity: 'C_SglGregorianCalDateFunction', 
    resultElement: 'DateFunctionEndDate', binding: [ {
      targetParameter: 'P_DateFunction', type: #PARAMETER, value : 'P_DateFunction' },
       { targetParameter : 'P_Language' , type : #SYSTEM_FIELD, value : '#SYSTEM_LANGUAGE'  } ] 
    } 
    @Consumption.hidden: true
    P_EndDate: bedat,
   @Consumption.defaultValue: 'PREVIOUSYEARTODATE'
   @Consumption.valueHelpDefinition: [{
      entity: {
        name:'C_GregorianCalDateFuncVH',
        element:'DateFunction'
      }
    }]
    P_DateFunction              : datefunctionid 

  as select from    P_PriceVariance3( P_DisplayCurrency : $parameters.P_DisplayCurrency,
                                      P_StartDate : $parameters.P_StartDate,
                                      P_EndDate : $parameters.P_EndDate )               as PriceVariance
  
          
   left outer join P_PriceVarianceUpdatedScore  as UpdatedScore     on  PriceVariance.PurchaseOrder     = UpdatedScore.PurchaseOrder
                                                                                 and PriceVariance.PurchaseOrderItem = UpdatedScore.PurchaseOrderItem
  association [1..1] to C_MM_MaterialValueHelp      as _Material               on  $projection.Material = _Material.Material
                                                                               and $projection.Plant    = _Material.Plant

  association [1..1] to C_MM_MaterialGroupValueHelp as _MaterialGroup          on $projection.MaterialGroup = _MaterialGroup.MaterialGroup
                                                        
  association [1..1] to C_MM_SupplierValueHelp   as _Supplier               on $projection.Supplier = _Supplier.Supplier
                                                                           and $projection.CompanyCode = _Supplier.CompanyCode
  
  association[1..1] to I_PurchasingGroup as _PurchasingGroup on $projection.PurchasingGroup = _PurchasingGroup.PurchasingGroup  
  
  association [1..1] to I_PurchasingOrganization as _PurchasingOrganization on $projection.PurchasingOrganization = _PurchasingOrganization.PurchasingOrganization
  
  association [1..1] to I_Plant                  as _Plant                  on $projection.Plant = _Plant.Plant
  
  association [1..1] to I_CalendarDate           as _Calendar               on $projection.PurchaseOrderDate = _Calendar.CalendarDate
  
{
      /* Keys */
  key cast( PriceVariance.PurchaseOrder as vdm_purchaseorder )                      as PurchaseOrder,
  key cast(PriceVariance.PurchaseOrderItem as vdm_purchaseorderitem)                as PurchaseOrderItem,
       @Consumption.hidden: true  
      @Environment.sql.passValue: true
  key PurgDocMigrtnIsCmpltdForAnlyts,
      
      @Environment.sql.passValue: true
  key SuplrEvalRelevantDocCategory,
  
      @Consumption.hidden: true
      PurchaseOrderDate,
      @Semantics.calendar.year: true
      _Calendar.CalendarYear,
      _Calendar.CalendarQuarter,
      _Calendar.CalendarMonth,
      _Calendar.CalendarWeek,

      /* Org Data */
      @Consumption.labelElement: 'PurchasingOrganizationName'
      @Consumption.valueHelpDefinition: [{ entity: { name : 'C_PurchasingOrgValueHelp', element : 'PurchasingOrganization' } }]
      PriceVariance.PurchasingOrganization,
      @Semantics.text: true
      _PurchasingOrganization.PurchasingOrganizationName,
      @Consumption.labelElement: 'PurchasingGroupName'
      @Consumption.valueHelpDefinition: [{ entity: { name : 'C_PurchasingGroupValueHelp', element : 'PurchasingGroup' } }]
      PurchasingGroup,
      @Semantics.text: true
      _PurchasingGroup.PurchasingGroupName,
      CompanyCode,

      /* Supplier */
      //@Consumption.labelElement: 'SupplierName'

      @ObjectModel.text.element: ['SupplierName']
      @Consumption.valueHelpDefinition: [{ entity: { name : 'C_MM_SmplSupplierValueHelp', element : 'Supplier' } }]
      Supplier,
      @Semantics.text: true
      _Supplier.SupplierName                                                       as  SupplierName,
      @Consumption.labelElement: 'CountryName'
      @Consumption.valueHelpDefinition: [{ entity: { name : 'C_MM_CountryValueHelp', element : 'SupplierCountry' } }]
      cast( _Supplier.Country as mm_a_supplier_country )                           as SupplierCountry,
      @Semantics.text: true
      _Supplier._CountryText[1: Language = $session.system_language].CountryName,

      /* Item Data */
      @Consumption.labelElement: 'PlantName'
      PriceVariance.Plant,
      @Semantics.text: true
      _Plant.PlantName,

      @Consumption.labelElement: 'MaterialName'
      @Consumption.valueHelpDefinition: [{ entity: { name : 'C_MM_MaterialValueHelp', element : 'Material' } }]
      Material,
      @Semantics.text: true
      _Material.MaterialName,

      @Consumption.labelElement: 'MaterialGroupName'
      PriceVariance.MaterialGroup,
      @Semantics.text: true
      _MaterialGroup.MaterialGroupName,

      @ObjectModel.text.element: ['PurgCatName']
      @Consumption.labelElement: 'PurgCatName'
      @Consumption.valueHelpDefinition: [{ entity: { name : 'I_PurchasingCategoryValueHelp', element : 'PurchasingCategory' } }]
      PriceVariance.PurchasingCategory                                            as PurchasingCategory,
      @Semantics.text: true
      PurgCatName                                                                 as PurgCatName,

      @Semantics.currencyCode: true
      Currency,
      @Semantics.unitOfMeasure: true
      PurchaseOrderQuantityUnit,
      
      @Semantics.currencyCode: true      
      DisplayCurrency,

      /* Measures */
      @DefaultAggregation: #AVG
      //cast(PriceVarianceInPct as mm_a_variance_pct) as PriceVarianceInPct,                  //2809288

      @EndUserText.label: 'Variance %'
      cast(PriceVarianceInPct as mm_ana_price_var_pct) as PriceVarianceInPct,                  

      @DefaultAggregation: #AVG
      //@Semantics.amount.currencyCode: 'Currency'

      @Semantics.amount.currencyCode: 'DisplayCurrency'
      @EndUserText.label: 'Price Variance Amount'
      cast(PriceVarianceAmount as abap.curr(13,3)) as PriceVarianceAmount,                  //2809288


      @DefaultAggregation: #AVG
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      @EndUserText.label: 'PO price per unit'
      cast(PurchaseOrderNetPriceAmount as abap.curr(13,3)) as PurchaseOrderNetPriceAmount,  //2809288

      
      @DefaultAggregation: #AVG
      @EndUserText.label: 'Invc price per unit'
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast(InvoicePrice as abap.curr(13,3)) as InvoicePrice,                                //2809288


      @DefaultAggregation: #SUM
      @Semantics.quantity.unitOfMeasure: 'PurchaseOrderQuantityUnit'
      @EndUserText.label: 'Delivered Quantity'
      DeliveredQuantity,
      
      @DefaultAggregation: #SUM
      @Semantics.quantity.unitOfMeasure: 'PurchaseOrderQuantityUnit'
      @EndUserText.label: 'Ordered Quantity'
      OrderedQuantity,

      @DefaultAggregation: #AVG
      cast( case when UpdatedScore.IsScoreChanged = 'X'
            then UpdatedScore.PriceVarianceScore
           else SupplierEvaluationScore 
      end as mm_a_variance_score ) as PriceVarianceScore,
      
      // @EndUserText.label: 'Purchase Order Count'

       @Aggregation.referenceElement: ['PurchaseOrder']
      @Aggregation.default: #COUNT_DISTINCT
       cast( 1 as mm_pur_ana_numbrofpurords ) as NumberOfPurchaseOrders,    
       
      //@EndUserText.label: 'Purchase Order Item Count'

        @DefaultAggregation: #SUM
       cast( 1 as mm_pur_ana_numbrofpurorditms ) as NumberOfPurchaseOrderItems  
            
} 
     
  
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"C_MM_MATERIALGROUPVALUEHELP",
"C_MM_MATERIALVALUEHELP",
"C_MM_SUPPLIERVALUEHELP",
"I_CALENDARDATE",
"I_COUNTRYTEXT",
"I_PLANT",
"I_PURCHASINGGROUP",
"I_PURCHASINGORGANIZATION",
"P_PRICEVARIANCE3",
"P_PRICEVARIANCEUPDATEDSCORE"
],
"ASSOCIATED":
[
"C_MM_MATERIALGROUPVALUEHELP",
"C_MM_MATERIALVALUEHELP",
"C_MM_SUPPLIERVALUEHELP",
"I_CALENDARDATE",
"I_PLANT",
"I_PURCHASINGGROUP",
"I_PURCHASINGORGANIZATION"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/