C_QuantityVariance

@ClientHandling.algorithm: #SESSION_VARIABLE //Inserted by VDM CDS Suite Plugin

@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@AbapCatalog.sqlViewName: 'CMMQUANTVAR'
//Commented by VDM CDS Suite Plugin:@ClientDependent: true

@AbapCatalog.compiler.compareFilter: true
@VDM.viewType: #CONSUMPTION
@EndUserText.label: 'Quantity Variance'
//Commented as analytical manager no longer supports AVG, COUNT, COUNT_DISTINCT

//@Analytics.dataCategory: #CUBE

@OData.publish: true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #NOT_REQUIRED

define view C_QuantityVariance

  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_SupplierEvalByQuantity( P_DisplayCurrency : $parameters.P_DisplayCurrency,
                                              P_StartDate : $parameters.P_StartDate,
                                              P_EndDate : $parameters.P_EndDate)    as QuantityVariance

//   inner join P_SuplrEvalScoring as CalculatedScore on (   QuantityVariance.PurchasingOrganization = CalculatedScore.PurchasingOrganization

//                                                      and CalculatedScore.SuplrEvalCriterion                = '2'

//                                                      and QuantityVariance.QuantityVariancePctWithSign     = CalculatedScore.AbsolutePercentage

//                                                      and CalculatedScore.IsSupplierEvalSwitchActive = 'X'

//                                                      )

//                                                   or (    (  ( QuantityVariance.PurchasingCategory    = CalculatedScore.PurchasingCategory )

//                                                          or ( QuantityVariance.PurchasingCategory is null and CalculatedScore.PurchasingCategory is null ) )

//

//                                                      and CalculatedScore.SuplrEvalCriterion                = '02'

//                                                      and QuantityVariance.QuantityVariancePctWithSign     = CalculatedScore.AbsolutePercentage

//                                                      and CalculatedScore.IsSupplierEvalSwitchActive = 'X'

//                                                      

//                                                   )

    
    left outer join P_QuantityVarianceUpdatedScore  as UpdatedScore     on  QuantityVariance.PurchaseOrder     = UpdatedScore.PurchaseOrder
                                                                                 and QuantityVariance.PurchaseOrderItem = UpdatedScore.PurchaseOrderItem
    left outer  join  I_CalendarDate as Calendar    on PurchaseOrderDate = Calendar.CalendarDate
    
  association [1..1] to C_MM_SupplierValueHelp   as _Supplier                   on $projection.Supplier = _Supplier.Supplier 
                                                                               and $projection.CompanyCode = _Supplier.CompanyCode

  association [1..1] to I_PurchasingOrganization as _PurchasingOrganization     on $projection.PurchasingOrganization = _PurchasingOrganization.PurchasingOrganization

  association [1..1] to I_PurchasingGroup        as _PurchasingGroup            on $projection.PurchasingGroup = _PurchasingGroup.PurchasingGroup
  
  association [1..1] to I_MaterialGroup          as _MaterialGroup              on $projection.MaterialGroup = _MaterialGroup.MaterialGroup

  association [1..1] to I_Material               as _Material                   on $projection.Material = _Material.Material

  association [1..1] to I_Plant                  as _Plant                      on $projection.Plant = _Plant.Plant

{
      /* Keys */
  key cast( QuantityVariance.PurchaseOrder as vdm_purchaseorder )                       as  PurchaseOrder,
  key cast( QuantityVariance.PurchaseOrderItem as vdm_purchaseorderitem)                as  PurchaseOrderItem,


  @Consumption: {
                      filter: {                    -- Usage of derviation.lookupEntity requires a filter definition too!
                                mandatory: false,
                                hidden: true,
                                selectionType: #SINGLE,
                                multipleSelections: false
                              },
  //                     hidden: true,

                      derivation: {
                                    lookupEntity: 'F_SuplrEvalCriteriaGenConfign',
                                    resultElement: 'SuplrEvalCritraDelivCompleted'
                                  }
                    }
  @Environment.sql.passValue: true 
  //@Semantics.booleanIndicator:true

  SuplrEvalCritraDelivCompleted , 
  
      /* Order Date */
      PurOrdItmFirstGRPostingDate,
      PurOrdItmFinalGRPostingDate,
      @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' } }]
      QuantityVariance.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 Data */
      @Consumption.labelElement: 'SupplierName'
      @Consumption.valueHelpDefinition: [{ entity: { name : 'C_MM_SmplSupplierValueHelp', element : 'Supplier' } }]
      Supplier,
      @Semantics.text: true
      _Supplier.SupplierName,
      @Consumption.labelElement: 'CountryName'
      _Supplier.Country                                                                 as   SupplierCountry,
      @Semantics.text: true
      _Supplier._CountryText[1: Language = $session.system_language].CountryName,

      /* Item Data */
      @Consumption.labelElement: 'MaterialName'
      @Consumption.valueHelpDefinition: [{ entity: { name : 'C_MM_MaterialValueHelp', element : 'Material' } }]
      Material,
      @Semantics.text: true
      _Material._Text[1: Language = $session.system_language].MaterialName,

      @Consumption.labelElement: 'MaterialGroupName'
      QuantityVariance.MaterialGroup,
      @Semantics.text: true
      _MaterialGroup._Text[1: Language = $session.system_language].MaterialGroupName,

      @Consumption.labelElement: 'PlantName'
      Plant,
      @Semantics.text: true
      _Plant.PlantName                                                                  as  PlantName,

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

      @Semantics.unitOfMeasure: true
      PurchaseOrderQuantityUnit,

      @Semantics.currencyCode: true
      cast( DisplayCurrency as displaycurrency )                                        as  DisplayCurrency,

      /* Measures */
      @DefaultAggregation: #SUM
      @Semantics.quantity.unitOfMeasure: 'PurchaseOrderQuantityUnit'
      cast( OrderedQuantity as mm_a_ordered_quantity )                                  as  OrderedQuantity,
      @EndUserText.label: 'Goods Receipt Quantity'
      @DefaultAggregation: #SUM
      @Semantics.quantity.unitOfMeasure: 'PurchaseOrderQuantityUnit'
      DeliveredQuantity                                                                 as  GoodsReceiptQty,

      @DefaultAggregation: #SUM
      @Semantics.quantity.unitOfMeasure: 'PurchaseOrderQuantityUnit'
      cast ( QuantityVarianceInOrdUnit as mm_a_quantity_variance )                      as  QuantityVarianceInOrdUnit,

      @DefaultAggregation: #SUM
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast( QuantityVarianceInDspCrcy as mm_a_quantity_var_amount )                     as  QuantityVarianceInDspCrcy,

      @DefaultAggregation: #AVG
//      0 as QuantityVarianceScore,

      cast( case when UpdatedScore.IsScoreChanged = 'X' 
        then UpdatedScore.QuantityVarianceScore
       else SupplierEvaluationScore
      end as mm_a_variance_score )                                                      as  QuantityVarianceScore,


      @DefaultAggregation: #AVG
      cast ( QuantityVarianceInPct as mmpur_ana_de_quantityvar_pct )                               as  QuantityVarianceInPct , // changed the DE to avoid numeric overflow error , as this filed holds the variance and not percentage mm_a_timevar_statdeliv_pct 

      
       
      
      // @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  
      
}