C_TimeVarianceQuery

@EndUserText.label: 'Time Variance'
@AbapCatalog.sqlViewName: 'CMMTIMEVARQUERY'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@VDM.viewType: #CONSUMPTION
@Analytics.query: true
@OData.publish: true
@ClientHandling.algorithm: #SESSION_VARIABLE //Inserted by VDM CDS Suite Plugin

@AccessControl.personalData.blocking: #NOT_REQUIRED
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory:  #XL
@AbapCatalog.preserveKey:true
@Metadata.ignorePropagatedAnnotations:true
@ObjectModel.supportedCapabilities: [ #ANALYTICAL_QUERY  ]
@ObjectModel.modelingPattern: #ANALYTICAL_QUERY
@Metadata.allowExtensions:true
define view C_TimeVarianceQuery
  with parameters

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

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

    @Consumption.defaultValue: 'PREVIOUSYEARTODATE'
    @Consumption.valueHelpDefinition: [{
     entity: {
       name:'C_GregorianCalDateFuncVH',
       element:'DateFunction'
     }
   }]
    P_DateFunction : datefunctionid

  as select from C_TimeVarianceCube( P_StartDate: $parameters.P_StartDate , P_EndDate:  $parameters.P_EndDate )
{

      /* Keys */
      @EndUserText.label: 'Purchasing Document'          -- Label added as key field cannot be deprecated in a released CDS
      @EndUserText.quickInfo: 'Purchasing Document'
  key PurchaseOrder,
      @EndUserText.label: 'Purchasing Document Item'     -- Label added as key field cannot be deprecated in a released CDS
      @EndUserText.quickInfo: 'Purchasing Document Item'
  key PurchaseOrderItem,
  key ScheduleLine,
      /*  @UI.hidden: true
        @Consumption.hidden: true
     PurchaseOrderItemUniqueID, */
      /* Order Date */
      @Consumption.filter : {selectionType:#INTERVAL,multipleSelections: false}             //3351863

      DeliveryDate,
     @Consumption.filter : {selectionType:#INTERVAL,multipleSelections: false}             //3351863

      SchedLineStscDeliveryDate,                                                           
      OriglPOSchdLnDelivDte,                                                               
      OriglPOSchedLineStstclDelivDte,                                                      
     @Consumption.filter : {selectionType:#INTERVAL,multipleSelections: false}             //3351863

      PurOrdItmFinalGRPostingDate,
      @Consumption.filter : {selectionType:#INTERVAL,multipleSelections: false}             //3351863

      PurOrdItmFirstGRPostingDate,
      OriglPurOrdItm1stGRPostgDte,
      OriglPurOrdItmFnlGRPostgDte,
      /*@Consumption.hidden: true
      @UI.hidden: true
       PurchaseOrderDate, */
      //      _Calendar.CalendarYear,

      //      _Calendar.CalendarQuarter,

      //      _Calendar.CalendarMonth,

      //      _Calendar.CalendarWeek,

      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      CalendarYear,
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      CalendarQuarter,
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      CalendarMonth,
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      CalendarWeek,

      /* Org Data*/
      @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,

      @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'
                                      }
                        }
      @Environment.sql.passValue: true
      @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
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      SuplrEvalRelevantDocCategory,

      /* Supplier */
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      @Environment.sql.passValue: true
      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,
      /* Item Data */
      @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
      Plant,
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      PurchasingCategory,
      @AnalyticsDetails.query.display: #KEY_TEXT
      @AnalyticsDetails.query.axis: #FREE
      @Analytics.internalName:#LOCAL
      PurchasingDocumentCategory,
       
      @VDM.lifecycle.status:#DEPRECATED                     //3351863

      @EndUserText.label: 'Purchasing Category Name (Deprecated)' 
      PurgCatName,

      @Consumption.hidden: true
      TimeVarianceInDays,
      @Consumption.hidden: true
      TimeVarianceInPct,
      @Consumption.hidden: true
      TimeVarianceInPctByDelivDate,
      @Consumption.hidden: true
      TimeVarianceScore,
      @Consumption.hidden: true
      TimeVarianceScoreByDelivDate,
      @Consumption.hidden: true
      NumberOfScheduleLines,




      /* Measures */
      @DefaultAggregation: #FORMULA
      @AnalyticsDetails.query.formula: 'CASE WHEN $projection.NumberOfScheduleLines > 0 THEN $projection.TimeVarianceInDays / $projection.NumberOfScheduleLines ELSE 0 END'
      cast( 1 as mm_a_overdue_days )              as SuplrEvalTimeVarianceInDays,

      @DefaultAggregation: #FORMULA
      @AnalyticsDetails.query.formula: 'CASE WHEN $projection.NumberOfScheduleLines > 0 THEN $projection.TimeVarianceInPct / $projection.NumberOfScheduleLines ELSE 0 END'
      cast( 1 as mmpur_ana_timevar_statdelivpct )     as SuplrEvalTimeVarianceInPct,  // changed the DE to avoid numeric overflow error , as this filed holds the variance and not percentage mm_a_timevar_statdeliv_pct 


      @DefaultAggregation: #FORMULA
      @AnalyticsDetails.query.formula: 'CASE WHEN $projection.NumberOfScheduleLines > 0 THEN $projection.TimeVarianceInPctByDelivDate / $projection.NumberOfScheduleLines ELSE 0 END'
      cast( 1 as mm_a_variance_delivery_pct )     as SuplrEvalTmeVarcByDelivDtePct,

      @DefaultAggregation: #FORMULA
      @AnalyticsDetails.query.formula: 'CASE WHEN $projection.NumberOfScheduleLines > 0 THEN $projection.TimeVarianceScore / $projection.NumberOfScheduleLines ELSE 0 END'
      cast( 1 as mm_a_timevar_score_statdeliv )   as SuplrEvalTimeVarianceScoreVal,

      @DefaultAggregation: #FORMULA
      @AnalyticsDetails.query.formula: 'CASE WHEN $projection.NumberOfScheduleLines > 0 THEN $projection.TimeVarianceScoreByDelivDate / $projection.NumberOfScheduleLines ELSE 0 END'
      cast( 1 as mm_a_timevarc_score_deliv_date ) as SuplrEvalTmeVarcDelivDteScrVal,

      NumberOfPurchaseOrders,
      NumberOfPurchaseOrderItems
      //





}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"C_TIMEVARIANCECUBE"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/