C_RequisitionChange

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

@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL  //Inserted by VDM CDS Suite Plugin

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

@AbapCatalog.compiler.compareFilter: true
@EndUserText.label: 'Change Purchase Requisition'
@VDM.viewType: #CONSUMPTION
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #NOT_REQUIRED
@OData.publish: true

define view C_RequisitionChange
  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 from    P_PurchRequisitionChgAggregate

    inner join P_PurchaseRequisitionItem2( P_StartDate: $parameters.P_StartDate, P_EndDate: $parameters.P_EndDate)      as RequisitionItem             on P_PurchRequisitionChgAggregate.ChangeDocObject = RequisitionItem.PurchaseRequisition

    left outer join I_PurchasingCategoryMatlGroup as PurchasingCategoryMatlGroup on RequisitionItem.MaterialGroup = PurchasingCategoryMatlGroup.MaterialGroup

  association [1..1] to I_Purreqvaluationarea    as _RequisitionArea        on $projection.Plant = _RequisitionArea.ValuationArea
  
  association [1..1] to I_Material               as _Material               on $projection.Material = _Material.Material

  //association [1..1] to I_MaterialGroup          as _MaterialGroup          on $projection.MaterialGroup = _MaterialGroup.MaterialGroup


  association [1..1] to C_MM_SupplierValueHelp   as _Supplier               on RequisitionItem.FixedSupplier = _Supplier.Supplier 
                                                                            and RequisitionItem.CompanyCode = _Supplier.CompanyCode

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

  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_CalendarDate                as _Calendar          on _Calendar.CalendarDate = RequisitionItem.CreationDate

 
{
  key RequisitionItem.PurchaseRequisition,
      //  concat(PurchaseRequisition,PurchaseRequisitionItem) as FormattedPurRequisitionItem,

  key RequisitionItem.PurchaseRequisitionItem,

     
      @Consumption.labelElement: 'SupplierName'
      cast(RequisitionItem.FixedSupplier as flief )                                                 as FixedSupplier,
      @Semantics.text: true
      //RequisitionItem._Supplier.SupplierName,

      _Supplier.SupplierName,     

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

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

      @Consumption.labelElement: 'PurchasingGroupName'
      @Consumption.valueHelpDefinition: [{ entity: { name : 'C_PurchasingGroupValueHelp', element : 'PurchasingGroup' } }]
      RequisitionItem.PurchasingGroup,
      @Semantics.text: true
      _PurchasingGroup.PurchasingGroupName,

      @Consumption.labelElement: 'PurchasingOrganizationName'
      @Consumption.valueHelpDefinition: [{ entity: { name : 'C_PurchasingOrgValueHelp', element : 'PurchasingOrganization' } }]
      RequisitionItem.PurchasingOrganization,
      @Semantics.text: true
      _PurchasingOrganization.PurchasingOrganizationName,

      @Consumption.labelElement: 'PlantName'
      RequisitionItem.Plant,
      @Semantics.text: true
      _Plant.PlantName,
 
      @Consumption.valueHelpDefinition: [{ entity: { name : 'C_MM_CompanyCodeValueHelp', element : 'CompanyCode' } }]
      _RequisitionArea.CompanyCode,

      @ObjectModel.text.element: ['PurgCatName']
      @Consumption.labelElement: 'PurgCatName'
    //  case when PurchasingCategoryMatlGroup._PurchasingCategory.PurchasingCategory is null

      @Consumption.valueHelpDefinition: [{ entity: { name : 'I_PurchasingCategoryValueHelp', element : 'PurchasingCategory' } }]
      cast(coalesce(PurchasingCategoryMatlGroup._PurchasingCategory.PurchasingCategory,'') as /srmsmc/purchasing_category_id preserving type ) as PurchasingCategory,
      @Semantics.text: true
      PurchasingCategoryMatlGroup._PurchasingCategory.PurgCatName                                   as PurgCatName,

      RequisitionItem.CreatedByUser,
      
      @UI.hidden: true  
      RequisitionItem.CreationDate,
      
      _Calendar.CalendarYear,
      _Calendar.CalendarQuarter,
      _Calendar.CalendarMonth,
      _Calendar.CalendarWeek,
      //RequisitionItem._Material,

      //RequisitionItem._MaterialGroup,


      //RequisitionItem._Plant,

      //RequisitionItem._PurchasingOrganization,

      //RequisitionItem._PurchasingGroup,


      @DefaultAggregation: #SUM
      cast(P_PurchRequisitionChgAggregate.NumberOfQuantityChanges as mm_a_numberofquantitychanges)     as NumberOfQuantityChanges,

      @DefaultAggregation: #SUM
      cast(P_PurchRequisitionChgAggregate.NumberOfPriceChanges as mm_a_numberofpricechanges )          as NumberOfPriceChanges,

      @DefaultAggregation: #SUM
      cast(P_PurchRequisitionChgAggregate.NmbrOfPurchasingGroupChanges as mm_a_numberofpurggrpchanges) as NmbrOfPurchasingGroupChanges,

      @DefaultAggregation: #SUM
      cast(P_PurchRequisitionChgAggregate.NumberOfSupplierChanges as mm_a_numberofsupplierchanges )    as NumberOfSupplierChanges,
    
     @DefaultAggregation: #SUM
      cast(P_PurchRequisitionChgAggregate.CountOfMaterialGroupChanges as mm_a_numberofmatgrpchanges )    as NumberOfMaterialGroupChanges,
         
       
      @DefaultAggregation: #SUM
      cast(P_PurchRequisitionChgAggregate.PurchaseRequisitionItemChanges as mm_a_purgreqnitmchanges)   as PurchaseRequisitionItemChanges

}