C_TimeVariance

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

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

@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory:  #XL
define view C_TimeVariance

  with parameters
    @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_TimeVariance1( P_StartDate: $parameters.P_StartDate , P_EndDate: $parameters.P_EndDate )         as SupplierEvalByTime

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

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

  association [1..1] to C_MM_MaterialValueHelp      as _Material               on  $projection.Material = _Material.Material
                                                                               and $projection.Plant    = _Material.Plant
  association [0..1] to C_PurchasingGroupValueHelp  as _PurchasingGroup        on  $projection.PurchasingGroup = _PurchasingGroup.PurchasingGroup
  association [0..1] to C_PurchasingOrgValueHelp    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_PurchasingCategoryMatlGroup 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_PurchasingCategoryValueHelp as _PurchasingCategory on $projection.PurchasingCategory = _PurchasingCategory.PurchasingCategory

    
{
      /* Keys */
   cast( SupplierEvalByTime.PurchaseOrder as vdm_purchaseorder )                                        as  PurchaseOrder,      
       
   cast(SupplierEvalByTime.PurchaseOrderItem as vdm_purchaseorderitem)                                  as  PurchaseOrderItem,
   ScheduleLine,

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



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

                      derivation: {
                                    lookupEntity: 'F_ProcmtAnalyticsDataSelMeth',
                                    resultElement: 'PurgDocMigrtnIsCmpltdForAnlyts'
                                  }
                    }        
  @UI.hidden: true
  @Environment.sql.passValue: 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,
      PurOrdItmFinalGRPostingDate,
      PurOrdItmFirstGRPostingDate,
      //    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' } }]
      SupplierEvalByTime.PurchasingOrganization,
      @Semantics.text: true
      cast ( _PurchasingOrganization.PurchasingOrganizationName as mm_a_purg_org_name ) as  PurchasingOrganizationName,
      @Consumption.labelElement: 'PurchasingGroupName'
      @Consumption.valueHelpDefinition: [{ entity: { name : 'C_PurchasingGroupValueHelp', element : 'PurchasingGroup' } }]
      PurchasingGroup,
      @Semantics.text: true
      cast ( _PurchasingGroup.PurchasingGroupName as mm_a_purg_grp_name )               as  PurchasingGroupName,
      CompanyCode,

      /* Supplier */
      @Consumption.labelElement: 'SupplierName'
      @Consumption.valueHelpDefinition: [{ entity: { name : 'C_MM_SmplSupplierValueHelp', element : 'Supplier' } }]

      Supplier,
      @Semantics.text: true
      cast ( _Supplier.SupplierName as 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: 'MaterialName'
      @Consumption.valueHelpDefinition: [{ entity: { name : 'C_MM_MaterialValueHelp', element : 'Material' } }]
      Material,
      @Semantics.text: true
      _Material.MaterialName,
      @Consumption.labelElement: 'MaterialGroupName'
      SupplierEvalByTime.MaterialGroup,
      @Semantics.text: true
      _MaterialGroup.MaterialGroupName,
      @Consumption.labelElement: 'PlantName'
      SupplierEvalByTime.Plant,
      @Semantics.text: true
      _Plant.PlantName,

   //@ObjectModel.foreignKey.association: '_PurchasingCategory'

      @ObjectModel.text.element: ['PurgCatName']
      @Consumption.labelElement: 'PurgCatName'
      @Consumption.valueHelpDefinition: [{ entity: { name : 'I_PurchasingCategoryValueHelp', element : 'PurchasingCategory' } }]
      _PurchasingCategoryMatlGroup._PurchasingCategory.PurchasingCategory                                 as PurchasingCategory,
      @Semantics.text: true
      _PurchasingCategoryMatlGroup._PurchasingCategory.PurgCatName                                        as PurgCatName,
      
      @EndUserText.label: 'Purchasing Document Category'
     @ObjectModel.text.element: ['PurchasingDocumentCategoryName']  
     @Consumption.labelElement: 'PurchasingDocumentCategoryName'
     @Consumption.valueHelpDefinition: [{ entity: { name : 'C_PurchasingDocCategoryVH', element : 'PurchasingDocumentCategory' } }]  
     PurchasingDocumentCategory,
     @Semantics.text: true
     _PurchasingDocumentCategory._Text[1: Language = $session.system_language].PurchasingDocumentCategoryName as PurchasingDocumentCategoryName,
      

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

      @DefaultAggregation: #AVG
      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: #AVG
      cast( TimeVarianceInPctByDelivDate as mm_a_variance_delivery_pct ) as TimeVarianceInPctByDelivDate,

      @DefaultAggregation: #AVG
//       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: #AVG
//      0 as TimeVarianceScoreByDelivDate

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

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

//      @Aggregation.default: #COUNT_DISTINCT

//      cast(case when PurchasingDocumentCategory = 'F'

//              then 1

//            else 0

//       end as mm_pur_ana_numbrofpurords ) as NumberOfPurchaseOrders,

       
//      @EndUserText.label: 'Purchase Order Item Count'

      @Aggregation.referenceElement: ['PurchaseOrderItem']
      @DefaultAggregation: #SUM
//      @Aggregation.default: #COUNT_DISTINCT

       cast( 1 as mm_pur_ana_numbrofpurorditms ) as NumberOfPurchaseOrderItems 
    
//      @Aggregation.default: #COUNT_DISTINCT

//      cast(case when PurchasingDocumentCategory = 'F'

//              then 1

//            else 0

//       end as mm_pur_ana_numbrofpurorditms ) as NumberOfPurchaseOrderItems,

//       

//      @Aggregation.default: #COUNT_DISTINCT

//      cast(case when PurchasingDocumentCategory = 'L'

//              then 1

//            else 0

//       end as zm_pur_ana_numbrofschagmt ) as NumberOfSchedulingAgmt

//       

      //@EndUserText.label: 'Purchase Order Item Count'

//      @Aggregation.referenceElement: ['PurchaseOrderItemUniqueID']

//      @Aggregation.default: #COUNT_DISTINCT

//       cast( 1 as mm_pur_ana_numbrofpurorditms ) as NumberOfPurchaseOrderItems 

//      @DefaultAggregation: #SUM 

//      cast(case when PurchasingDocumentCategory = 'L'

//              then 1

//            else 0

//       end as mm_pur_ana_numbrofpurorditms ) as NumberOfSchedulingAgmtItems

            

}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"C_MM_MATERIALGROUPVALUEHELP",
"C_MM_MATERIALVALUEHELP",
"C_MM_SUPPLIERVALUEHELP",
"C_PURCHASINGGROUPVALUEHELP",
"C_PURCHASINGORGVALUEHELP",
"I_CALENDARDATE",
"I_COUNTRYTEXT",
"I_PLANT",
"I_PURCHASINGCATEGORY",
"I_PURCHASINGCATEGORYMATLGROUP",
"I_PURCHASINGDOCUMENTCATEGORY",
"I_PURGDOCUMENTCATEGORYTEXT",
"P_TIMEVARIANCE1",
"P_TIMEVARIANCEUPDATEDSCORE"
],
"ASSOCIATED":
[
"C_MM_MATERIALGROUPVALUEHELP",
"C_MM_MATERIALVALUEHELP",
"C_MM_SUPPLIERVALUEHELP",
"C_PURCHASINGGROUPVALUEHELP",
"C_PURCHASINGORGVALUEHELP",
"I_CALENDARDATE",
"I_PLANT",
"I_PURCHASINGCATEGORYMATLGROUP",
"I_PURCHASINGDOCUMENTCATEGORY"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/