P_PurReqnItemTypeAnalysis

@AbapCatalog.sqlViewName: 'PMMREQTYPEANA2'
@AbapCatalog.compiler.compareFilter: true
@VDM.viewType: #CONSUMPTION
@VDM.private: true
@EndUserText.label: 'Purchase Requistion No Touch for OVP'
define view P_PurReqnItemTypeAnalysis 

with parameters
  P_StartDate: badat,
  P_EndDate: badat

  as select from    I_Purchaserequisitionitem     as RequisitionItem

    left outer join I_PurReqnAccountAssignment    as AccountAssignment           on  RequisitionItem.PurchaseRequisition     = AccountAssignment.PurchaseRequisition
                                                                                 and RequisitionItem.PurchaseRequisitionItem = AccountAssignment.PurchaseRequisitionItem
 
    left outer join I_CalendarDate  as RelevantDates on  RequisitionItem.CreationDate = RelevantDates.CalendarDate
    left outer join I_PurchasingCategoryMatlGroup as PurchasingCategoryMatlGroup on RequisitionItem.MaterialGroup = PurchasingCategoryMatlGroup.MaterialGroup

  association [1..1] to I_Purreqvaluationarea as _PurReqValuationArea on $projection.Plant = _PurReqValuationArea.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 I_PurReqnItemClassfctn as _PurReqnItemClassfctn on $projection.PurReqnItemClassification = _PurReqnItemClassfctn.PurReqnItemClassification
{
key RequisitionItem.PurchaseRequisition,
  key RequisitionItem.PurchaseRequisitionItem,
  key AccountAssignment.CostCenter,

      @ObjectModel.text.element: ['PurchasingGroupName'] //Inserted by VDM CDS Suite Plugin

      @Consumption.labelElement: 'PurchasingGroupName'
      PurchasingGroup,
      @Semantics.text: true
      _PurchasingGroup.PurchasingGroupName,
      @ObjectModel.text.element: ['PurchasingOrganizationName'] //Inserted by VDM CDS Suite Plugin

      @Consumption.labelElement: 'PurchasingOrganizationName'
      PurchasingOrganization,
      @Semantics.text: true
      _PurchasingOrganization.PurchasingOrganizationName,
      @ObjectModel.text.element: ['SupplierName'] //Inserted by VDM CDS Suite Plugin

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

      @ObjectModel.text.element: ['MaterialGroupName'] //Inserted by VDM CDS Suite Plugin

      @Consumption.labelElement: 'MaterialGroupName'
      RequisitionItem.MaterialGroup,
      @Semantics.text: true
      _MaterialGroup._Text[1: Language = $session.system_language].MaterialGroupName,
      @ObjectModel.text.element: ['MaterialName'] //Inserted by VDM CDS Suite Plugin

      @Consumption.labelElement: 'MaterialName'
      Material,
      @Semantics.text: true
      _Material._Text[1: Language = $session.system_language].MaterialName,
      @ObjectModel.text.element: ['PlantName'] //Inserted by VDM CDS Suite Plugin

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

      PurchasingCategoryMatlGroup._PurchasingCategory.PurchasingCategory as  PurchasingCategory,
      @Semantics.text: true
      PurchasingCategoryMatlGroup._PurchasingCategory.PurgCatName        as  PurgCatName,

      _PurReqValuationArea.CompanyCode,

      CreatedByUser,

      @Consumption.hidden: true
      CreationDate,
      RelevantDates.CalendarYear,
      RelevantDates.CalendarQuarter,
      RelevantDates.CalendarMonth,
      RelevantDates.CalendarWeek,

      @ObjectModel.foreignKey.association: '_PurReqnItemClassfctn'
      cast (case
              when PurReqnOrigin = 'S' and PurReqnSSPCatalog = '' and Material = '' //Free-text Item

                  then '02'
              when PurReqnOrigin = 'S' and Material != '' //Material Item

                  then '03'
              when PurReqnSSPCatalog != '' //Catalog Item

                  then '01'
              when  PurReqnOrigin = 'R' and Material != '' //Material Item (GUI)

                  then '05'
              when PurReqnOrigin = 'R' and Material = '' //Free-text Item (GUI)

                  then '04'
              end as mm_a_pur_reqn_item_classfctn ) as PurReqnItemClassification ,

              
      @DefaultAggregation: #SUM
      cast(  1 as abap.int4 ) as PurReqnItemClassfctnCount ,

      _PurReqnItemClassfctn,
      _PurReqValuationArea,
      _MaterialGroup,
      _Material,
      _Plant

}
where (
     PurReqnOrigin = 'R'
  or PurReqnOrigin = 'S'  )
  
  and
  CreationDate >=    :P_StartDate and
  CreationDate <= :P_EndDate
         
  
  
  
  
  
  
  
  
  
 /*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CALENDARDATE",
"I_MATERIAL",
"I_MATERIALGROUP",
"I_MATERIALGROUPTEXT",
"I_MATERIALTEXT",
"I_PLANT",
"I_PURCHASEREQUISITIONITEM",
"I_PURCHASINGCATEGORY",
"I_PURCHASINGCATEGORYMATLGROUP",
"I_PURCHASINGGROUP",
"I_PURCHASINGORGANIZATION",
"I_PURREQNACCOUNTASSIGNMENT",
"I_PURREQVALUATIONAREA",
"I_SUPPLIER"
],
"ASSOCIATED":
[
"I_MATERIAL",
"I_MATERIALGROUP",
"I_PLANT",
"I_PURREQNITEMCLASSFCTN",
"I_PURREQVALUATIONAREA"
],
"BASE":
[
"I_PURCHASEREQUISITIONITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/