C_TIMEVARIANCECUBE

@AbapCatalog.sqlViewName: 'CMMTIMEVARCUBE'
//@ClientDependent: true

@AbapCatalog.compiler.compareFilter: true
@EndUserText.label: 'Time Variance'
@VDM.viewType: #CONSUMPTION
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #NOT_REQUIRED
@OData.publish: true
@Analytics.dataCategory: #CUBE
@ClientHandling.algorithm: #SESSION_VARIABLE //Inserted by VDM CDS Suite Plugin

@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory:  #XL
@Metadata.ignorePropagatedAnnotations:true
@Analytics.internalName:#LOCAL 
@Metadata.allowExtensions:true
@ObjectModel.supportedCapabilities: [ #ANALYTICAL_PROVIDER  ]
@ObjectModel.modelingPattern: #ANALYTICAL_CUBE
@AbapCatalog.preserveKey:true 
define view C_TimeVarianceCube

 with parameters 
    P_StartDate       : budat,  
    P_EndDate         : budat

  as select from  P_TimeVariance1( P_StartDate: $parameters.P_StartDate , P_EndDate: $parameters.P_EndDate )         as SupplierEvalByTime

    
    left outer  to one join P_TimeVarianceUpdatedScore as UpdatedScore     on  SupplierEvalByTime.PurchaseOrder     = UpdatedScore.PurchaseOrder
                                                                                  and SupplierEvalByTime.PurchaseOrderItem = UpdatedScore.PurchaseOrderItem
  association [1..1] to I_CalendarDate as _Calendar     on $projection.PurchaseOrderDate = _Calendar.CalendarDate
  
  association [1..1] to I_ProductGroup_2 as _MaterialGroup on $projection.MaterialGroup = _MaterialGroup.ProductGroup

  association [0..1] to I_Supplier      as _Supplier               on  $projection.Supplier     = _Supplier.Supplier
                                                                             //  and $projection.CompanyCode  = _Supplier.CompanyCode


  association [1..1] to I_Product      as _Material               on  $projection.Material = _Material.Product 
                                                                         //      and $projection.Plant    = _Material.Plant

  association [0..1] to I_PurchasingGroup  as _PurchasingGroup        on  $projection.PurchasingGroup = _PurchasingGroup.PurchasingGroup
  association [0..1] to I_PurchasingOrganization    as _PurchasingOrganization on  $projection.PurchasingOrganization = _PurchasingOrganization.PurchasingOrganization //and

  association [1..1] to I_Plant as _Plant on $projection.Plant = _Plant.Plant  
   association[1..1] to I_PurgCatMaterialGroupAPI01 as _PurchasingCategoryMatlGroup on $projection.MaterialGroup = _PurchasingCategoryMatlGroup.MaterialGroup
  association[1..1] to I_PurchasingDocumentCategory as _PurchasingDocumentCategory 
    on $projection.PurchasingDocumentCategory = _PurchasingDocumentCategory.PurchasingDocumentCategory
  association [1..1] to I_CompanyCode               as _CompanyCode            on  $projection.CompanyCode = _CompanyCode.CompanyCode
  association[1..1] to I_Country as _Country on   $projection.SupplierCountry = _Country.Country
   association[1..1] to I_PurchasingCategoryValueHelp as _PurchasingCategory on $projection.PurchasingCategory = _PurchasingCategory.PurchasingCategory
    association[1..1] to I_Region as _Region  on $projection.Region = _Region.Region and _Region.Country = $projection.SupplierCountry
{
      /* Keys */
   key cast( SupplierEvalByTime.PurchaseOrder as vdm_purchaseorder )                                        as  PurchaseOrder,      
       
   key cast(SupplierEvalByTime.PurchaseOrderItem as vdm_purchaseorderitem)                                  as  PurchaseOrderItem,
   key ScheduleLine,

      /* Order Date */
      cast( ScheduleLineDeliveryDate as fieu_deliverydate)                              as  DeliveryDate,
      cast( SchedLineStscDeliveryDate as slfdt)                                         as  SchedLineStscDeliveryDate,
      
      OriglPOSchdLnDelivDte,
      OriglPOSchedLineStstclDelivDte,
      
//      cast ( PostingDate as mm_a_goods_receipt_date )                                   as  PostingDate,

      PurOrdItmFinalGRPostingDate,
      PurOrdItmFirstGRPostingDate,
      
      OriglPurOrdItm1stGRPostgDte,
      OriglPurOrdItmFnlGRPostgDte,
      
       PurchaseOrderDate,
      @Analytics.internalName:#LOCAL
      @Semantics.calendar.year: true
      _Calendar.CalendarYear,
      @Analytics.internalName:#LOCAL
      @Semantics.calendar.quarter: true                        //3351863

      _Calendar.CalendarQuarter,
      @Analytics.internalName:#LOCAL
      @Semantics.calendar.month: true                        //3351863

      _Calendar.CalendarMonth,
      @Analytics.internalName:#LOCAL
      @Semantics.calendar.week: true                        //3351863

      _Calendar.CalendarWeek,
      @Semantics.booleanIndicator: true
PurgDocMigrtnIsCmpltdForAnlyts, 
      @Environment.sql.passValue: true 
      SuplrEvalRelevantDocCategory,
      /* Org Data*/
      @ObjectModel.foreignKey.association: '_PurchasingOrganization'
      @Analytics.internalName:#LOCAL
      SupplierEvalByTime.PurchasingOrganization,
      //@Semantics.text: true

      //cast ( _PurchasingOrganization.PurchasingOrganizationName as mm_a_purg_org_name ) as  PurchasingOrganizationName,

      @ObjectModel.foreignKey.association: '_PurchasingGroup'
      @Analytics.internalName:#LOCAL
      PurchasingGroup,
      //@Semantics.text: true

     // cast ( _PurchasingGroup.PurchasingGroupName as mm_a_purg_grp_name )               as  PurchasingGroupName,

       @ObjectModel.foreignKey.association: '_CompanyCode'
       @Analytics.internalName:#LOCAL
      CompanyCode,

      /* Supplier */
      @ObjectModel.foreignKey.association: '_Supplier'
      @Analytics.internalName:#LOCAL
      Supplier,

     @ObjectModel.foreignKey.association: '_Region'
     @Analytics.internalName:#LOCAL
     _Supplier.Region as Region,
     
      @ObjectModel.foreignKey.association: '_Country'
      @Analytics.internalName:#LOCAL
      cast( _Supplier.Country as mm_a_supplier_country ) as SupplierCountry,

      /* Item Data */
      @ObjectModel.foreignKey.association: '_Material'
      @Analytics.internalName:#LOCAL
      Material,

      
      @ObjectModel.foreignKey.association: '_MaterialGroup'
      @Analytics.internalName:#LOCAL
      SupplierEvalByTime.MaterialGroup,
  
            @ObjectModel.foreignKey.association: '_Plant'
      @Analytics.internalName:#LOCAL
      SupplierEvalByTime.Plant,

   
       @ObjectModel.foreignKey.association: '_PurchasingCategory'
       @Analytics.internalName:#LOCAL
      _PurchasingCategoryMatlGroup._PurchasingCategory.PurchasingCategory                                 as PurchasingCategory,
       @VDM.lifecycle.status:#DEPRECATED                     //3351863

       @Semantics.text: true
      _PurchasingCategoryMatlGroup._PurchasingCategory.PurgCatName ,
            
      @EndUserText.label: 'Purchasing Document Category'
            @ObjectModel.foreignKey.association: '_PurchasingDocumentCategory'
      @Analytics.internalName:#LOCAL
      PurchasingDocumentCategory,

     //@Semantics.text: true

     //_PurchasingDocumentCategory._Text[1: Language = $session.system_language].PurchasingDocumentCategoryName as PurchasingDocumentCategoryName,

      

      /* Measures */
      @DefaultAggregation: #SUM
      cast (  TimeVarianceInDays as mm_a_overdue_days )                                 as  TimeVarianceInDays,

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

      @DefaultAggregation: #SUM
      cast( TimeVarianceInPctByDelivDate as mm_a_variance_delivery_pct ) as TimeVarianceInPctByDelivDate,

      @DefaultAggregation: #SUM
//       0 as TimeVarianceScore,

      cast( case when UpdatedScore.IsScoreChanged = 'X' 
        then UpdatedScore.TimeVarianceScore
       else SupplierEvalByTime.TimeVarianceScore
      end as mm_a_timevar_score_statdeliv  )                                                      as  TimeVarianceScore,
//      

      @DefaultAggregation: #SUM
//      0 as TimeVarianceScoreByDelivDate

      cast (  TimeVarianceScoreByDelivDate as mm_a_timevarc_score_deliv_date) as TimeVarianceScoreByDelivDate,
      
     cast( PurchaseOrderItemUniqueID as purchasingdocumentitemuniqueid ) as PurchaseOrderItemUniqueID,
      
      // @EndUserText.label: 'Purchase Order Count'

       @Aggregation.referenceElement: ['PurchaseOrder']
      @Aggregation.default: #COUNT_DISTINCT
       cast( 1 as mm_pur_ana_numbrofpurords ) as NumberOfPurchaseOrders, 

      @Aggregation.referenceElement: ['PurchaseOrderItemUniqueID']
    //  @DefaultAggregation: #SUM

      @Aggregation.default: #COUNT_DISTINCT 
       cast( 1 as mm_pur_ana_numbrofpurorditms ) as NumberOfPurchaseOrderItems ,
       
      @DefaultAggregation: #SUM 
       cast( 1 as mm_pur_ana_numbrofpurorditms ) as       NumberOfScheduleLines,
       
       _Calendar,
_MaterialGroup,
_Supplier,
_Material,
_PurchasingGroup,
_PurchasingOrganization,
_Plant,
_PurchasingDocumentCategory,
_CompanyCode,
_Country,
_Region,
_PurchasingCategory
    

}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CALENDARDATE",
"I_PURGCATAPI01",
"I_PURGCATMATERIALGROUPAPI01",
"I_SUPPLIER",
"P_TIMEVARIANCE1",
"P_TIMEVARIANCEUPDATEDSCORE"
],
"ASSOCIATED":
[
"I_CALENDARDATE",
"I_COMPANYCODE",
"I_COUNTRY",
"I_PLANT",
"I_PRODUCT",
"I_PRODUCTGROUP_2",
"I_PURCHASINGCATEGORYVALUEHELP",
"I_PURCHASINGDOCUMENTCATEGORY",
"I_PURCHASINGGROUP",
"I_PURCHASINGORGANIZATION",
"I_PURGCATMATERIALGROUPAPI01",
"I_REGION",
"I_SUPPLIER"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/