C_POItemChangeCount

@AbapCatalog.sqlViewName: 'CMMPOITMCHGCNT'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #NOT_REQUIRED
@VDM.viewType : #CONSUMPTION
//@Analytics.dataCategory: #CUBE

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

@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@OData.publish: true
@ObjectModel.usageType.sizeCategory:  #XL
@EndUserText.label: 'Purchase Order Item Changes'



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

    @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    : badat,

    @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      : badat


  as select distinct from P_PURCHASEORDERITEMCHGDOCSUM( P_StartDate: $parameters.P_StartDate, P_EndDate: $parameters.P_EndDate) as _PurchaseChange
    left outer join       I_PurchaseOrder                                                                                       as _PurchaseOrder     on _PurchaseChange.PurchaseOrder = _PurchaseOrder.PurchaseOrder
    left outer join       I_PurchaseOrderItem                                                                                   as _purchaseOrderItem on  _PurchaseChange.PurchaseOrder     = _purchaseOrderItem.PurchaseOrder
                                                                                                                                                      and _PurchaseChange.PurchaseOrderItem = _purchaseOrderItem.PurchaseOrderItem
  // left outer join       P_PurchaseOrderItemDateRange( P_StartDate:  $parameters.P_StartDate, P_EndDate: $parameters.P_EndDate) as _purchaseOrderItem on  _PurchaseChange.PurchaseOrder     = _purchaseOrderItem.PurchaseOrder

  //                                                                                                                                                  and _PurchaseChange.PurchaseOrderItem = _purchaseOrderItem.PurchaseOrderItem

  association [0..1] to I_Supplier                    as _Supplier                    on _PurchaseOrder.Supplier = _Supplier.Supplier
  association [0..1] to I_PurchasingGroup             as _PurchasingGroup             on _PurchaseOrder.PurchasingGroup = _PurchasingGroup.PurchasingGroup
  association [0..1] to I_PurchasingOrganization      as _PurchasingOrganization      on _PurchaseOrder.PurchasingOrganization = _PurchasingOrganization.PurchasingOrganization
  association [0..1] to I_Material                    as _Material                    on _purchaseOrderItem.Material = _Material.Material
  association [0..1] to I_MaterialGroup               as _MaterialGroup               on _purchaseOrderItem.MaterialGroup = _MaterialGroup.MaterialGroup
  association [0..1] to I_CompanyCode                 as _CompanyCode                 on _PurchaseOrder.CompanyCode = _CompanyCode.CompanyCode
  association [0..1] to I_CompanyCode                 as _ReceivingCompanyCode        on _purchaseOrderItem.CompanyCode = _ReceivingCompanyCode.CompanyCode
  association [0..1] to I_Plant                       as _Plant                       on _purchaseOrderItem.Plant = _Plant.Plant
  association [0..1] to I_CalendarDate                as _PurchaseOrderDate           on _PurchaseOrderDate.CalendarDate = _PurchaseOrder.PurchaseOrderDate
  association [1..1] to I_PurchasingCategoryMatlGroup as _PurchasingCategoryMatlGroup on _purchaseOrderItem.MaterialGroup = _PurchasingCategoryMatlGroup.MaterialGroup

{
  key _PurchaseChange.PurchaseOrder                                                     as PurchaseOrder,
  key _PurchaseChange.PurchaseOrderItem                                                 as PurchaseOrderItem,

      @Consumption.labelElement: 'SupplierName'
      _PurchaseOrder.Supplier                                                           as Supplier,
      @Semantics.text: true
      _Supplier.SupplierName,


      @Consumption.labelElement: 'MaterialName'
      Material                                                                          as Material,
      @Semantics.text: true
      _Material._Text[1: Language = $session.system_language].MaterialName,

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

      @Consumption.labelElement: 'PurchasingGroupName'
      _PurchaseOrder.PurchasingGroup                                                    as PurchasingGroup,
      @Semantics.text: true
      _PurchasingGroup.PurchasingGroupName,

      @Consumption.labelElement: 'PurchasingOrganizationName'
      _PurchaseOrder.PurchasingOrganization                                             as PurchasingOrganization,
      @Semantics.text: true
      _PurchasingOrganization.PurchasingOrganizationName,

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

      @Consumption.labelElement: 'CompanyCodeName'
      _PurchaseOrder.CompanyCode,
      @Semantics.text: true
      _CompanyCode.CompanyCodeName,

      @Consumption.labelElement: 'ReceivingCompanyCodeName'
      @EndUserText.label: 'Receiving Plant Company Code'
      _purchaseOrderItem.CompanyCode                                                    as ReceivingCompanyCode,

      @Semantics.text: true
      _ReceivingCompanyCode.CompanyCodeName                                             as ReceivingCompanyCodeName,

      // @ObjectModel.text.element: ['PurgCatName']

      @Consumption.labelElement: 'PurgCatName'
      @EndUserText.label: 'Purchasing Category'
      coalesce( _PurchasingCategoryMatlGroup._PurchasingCategory.PurchasingCategory,'') as PurchasingCategory,
      //_PurchasingCategoryMatlGroup._PurchasingCategory.PurchasingCategory                                                                                                                                                                                          as PurchasingCategory,

      @Semantics.text: true
      _PurchasingCategoryMatlGroup._PurchasingCategory.PurgCatName                      as PurgCatName,


      _PurchaseOrder.CreatedByUser,

      _PurchaseOrderDate.CalendarYear,
      _PurchaseOrderDate.CalendarQuarter,
      _PurchaseOrderDate.CalendarMonth,
      _PurchaseOrderDate.CalendarWeek,

      @DefaultAggregation: #SUM
      @EndUserText.label: 'Quantity Changes'
      cast(_PurchaseChange.NumberOfQuantityChanges as mm_a_numberofquantitychanges)     as NumberOfQuantityChanges,

      @DefaultAggregation: #SUM
      @EndUserText.label:  'Price Changes'
      cast(_PurchaseChange.NumberOfPriceChanges as mm_a_numberofpricechanges)           as NumberOfPriceChanges,

      @DefaultAggregation: #SUM
      @EndUserText.label: 'Purchasing Group Changes'
      cast(_PurchaseChange.NmbrOfPurchasingGroupChanges as mm_a_numberofpurggrpchanges) as NmbrOfPurchasingGroupChanges,

      @DefaultAggregation: #SUM
      @EndUserText.label: 'Storage Location Changes'
      cast(_PurchaseChange.NumberOfStorageLocChanges as mm_a_numberofstoragelocchanges) as TotalNoOfStorageLocChanges,

      @DefaultAggregation: #SUM
      @EndUserText.label: 'No. of Items Added'
      cast(_PurchaseChange.NumberOfItemsAdded as mm_a_numberofpoitemsadded)             as TotalNoOfPOItemsAdded,

      @DefaultAggregation: #SUM
      @EndUserText.label: 'Payment Term Changes'
      cast(_PurchaseChange.NumberOfPaymentTermChanges as mm_a_numberofpaymttermchanges) as TotalNoOfPOPayTermChanges,


      @DefaultAggregation: #SUM
      @EndUserText.label: 'Delivery Date Changes'
      cast(_PurchaseChange.NumberOfDeliveryDateChanges as mm_a_numberofdeldatechanges)  as TotalNoOfPODelDateChanges,


      @DefaultAggregation: #SUM
      @EndUserText.label: 'Total Purchase Order Item Changes'
      cast(_PurchaseChange.PurchaseOrderItemChanges as mm_a_totalnumberofpoitemchange)  as TotalNoOfPOItemChanges



}
where
        not(
          _purchaseOrderItem.Material              = ''
          and _purchaseOrderItem.IsStatisticalItem = 'X'
        )