P_RequisitionTypeAnalysis2

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

@AbapCatalog.sqlViewName: 'PMMREQTYPEANA0'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@AbapCatalog.preserveKey:true
@VDM.viewType: #CONSUMPTION
//@Analytics.dataCategory: #CUBE

@VDM.private : true
@AccessControl.personalData.blocking: #NOT_REQUIRED
define view P_RequisitionTypeAnalysis2
  with parameters
    P_StartDate : budat,
    P_EndDate   : budat
  // as  select from    I_Purchaserequisitionitem    as RequisitionItem           //To avoid unnecessary associations

  as select from I_PurchaseRequisitionItemBasic as RequisitionItem
{

  key RequisitionItem.PurchaseRequisition,
  key RequisitionItem.PurchaseRequisitionItem,

      PurchasingGroup,

      PurchasingOrganization,
      FixedSupplier,
      RequisitionItem.MaterialGroup,   
      
      BaseUnit,
      RequestedQuantity,
      @Semantics.quantity.unitOfMeasure: 'BaseUnit'
      @DefaultAggregation: #NONE
      PurReqnPriceQuantity,    
      PurReqnItemCurrency,
      @Semantics.amount.currencyCode: 'PurReqnItemCurrency'
      @DefaultAggregation: #NONE
      PurchaseRequisitionPrice,

      Material,
      Plant,
      PurchaseRequisitionType,
      CreatedByUser,
      PurReqnOrigin,

      @Consumption.hidden: true
      CreationDate,

      cast ( '02'  as mm_a_pur_reqn_item_classfctn ) as PurReqnItemClassification,

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

      @DefaultAggregation: #SUM
      cast( 1 as nmbr_free_txt_via_fiori_appl )      as NmbrOfFreeTxtItmCrtedFrmSSP,

      @DefaultAggregation: #SUM
      cast( 0 as nmbr_material_via_fiori_appl )      as NmbrOfMaterialItmCrtedFrmSSP,

      @DefaultAggregation: #SUM
      cast( 0 as nmbr_catalog_via_fiori_appl )       as NmbrOfCatalogItmCrtedFrmSSP,

      @DefaultAggregation: #SUM
      cast( 0 as nmbr_material_via_me51n )           as NmbrOfMaterialItmCrtedManually,

      @DefaultAggregation: #SUM
      cast( 0 as nmbr_free_txt_via_me51n )           as NmbrOfFreeTxtItmCrtedManually,

      @DefaultAggregation: #SUM
      cast( 0 as nmbr_material_via_extnl_source )    as NmbrOfMatlItmCrtedFrmExtSrce
}
where
        PurReqnOrigin     = 'S'
  and   Material          = ''
  and   PurReqnSSPCatalog = ''
  and
  //ReleaseIsNotCompleted = '' and


        CreationDate      <= $parameters.P_EndDate
  and   CreationDate      >= $parameters.P_StartDate
  and   IsDeleted         = ''
  and   not(
      Material            = ''
      and IsOutline       = 'X'
    )
union all
//select from    I_Purchaserequisitionitem    as RequisitionItem     //To avoid unnecessary associations

select from I_PurchaseRequisitionItemBasic as RequisitionItem
{

  key RequisitionItem.PurchaseRequisition,
  key RequisitionItem.PurchaseRequisitionItem,
      PurchasingGroup,

      PurchasingOrganization,
      FixedSupplier,
      //      cast(FixedSupplier as flief )                      as  FixedSupplier,



      RequisitionItem.MaterialGroup,
      
      BaseUnit,
      RequestedQuantity,
      @Semantics.quantity.unitOfMeasure: 'BaseUnit'
      @DefaultAggregation: #NONE
      PurReqnPriceQuantity,
      PurReqnItemCurrency,
      @Semantics.amount.currencyCode: 'PurReqnItemCurrency'
      @DefaultAggregation: #NONE
      PurchaseRequisitionPrice,

      Material,

      Plant,
      PurchaseRequisitionType,
      CreatedByUser,
      PurReqnOrigin,
      //_Supplier,                                                 //Association maintained at Top level CDS

      @Consumption.hidden: true
      CreationDate,
      cast ( '03'  as mm_a_pur_reqn_item_classfctn ) as PurReqnItemClassification,

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

      @DefaultAggregation: #SUM
      cast( 0 as nmbr_free_txt_via_fiori_appl )      as NmbrOfFreeTxtItmCrtedFrmSSP,

      @DefaultAggregation: #SUM
      cast( 1 as nmbr_material_via_fiori_appl )      as NmbrOfMaterialItmCrtedFrmSSP,

      @DefaultAggregation: #SUM
      cast(  0 as nmbr_catalog_via_fiori_appl )      as NmbrOfCatalogItmCrtedFrmSSP,

      @DefaultAggregation: #SUM
      cast(0 as nmbr_material_via_me51n )            as NmbrOfMaterialItmCrtedManually,

      @DefaultAggregation: #SUM
      cast( 0 as nmbr_free_txt_via_me51n )           as NmbrOfFreeTxtItmCrtedManually,

      @DefaultAggregation: #SUM
      cast( 0 as nmbr_material_via_extnl_source )    as NmbrOfMatlItmCrtedFrmExtSrce
}
where
        PurReqnOrigin = 'S'
  and   Material != ''
  and
  //ReleaseIsNotCompleted = '' and


        CreationDate  <= $parameters.P_EndDate
  and   CreationDate  >= $parameters.P_StartDate
  and   IsDeleted     = ''
  and   not(
      Material        = ''
      and IsOutline   = 'X'
    )