P_MaterialPriceVariance

@AbapCatalog.sqlViewName: 'PMMMATLPRCVAR'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType : #CONSUMPTION
@VDM.private: true
@AccessControl.personalData.blocking: #NOT_REQUIRED
@EndUserText.label: 'Material Price Variance'
@AbapCatalog.viewEnhancementCategory: [#PROJECTION_LIST,#UNION] // Added for extensibility


define view P_MaterialPriceVariance
  with parameters
    P_DisplayCurrency : displaycurrency,
    P_Material        : matnr,
    P_StartDate       : vdm_validitystart,
    P_EndDate         : vdm_validityend

  as select from P_PurContractPriceVariance2 (  P_DisplayCurrency : $parameters.P_DisplayCurrency,
                                                P_Material : $parameters.P_Material , P_StartDate: $parameters.P_StartDate,
                                                         P_EndDate: $parameters.P_EndDate )
{

  key cast( PurchaseContract as mm_a_purchasing_document )                   as PurchasingDocument,
  key cast( PurchaseContractItem as ebelp )                                  as PurchasingDocumentItem,
  key ChangeDocument,

  key cast('' as gjahr )                                                     as MaterialDocumentYear,
  key cast('' as mm_a_material_doc_item )                                    as MaterialDocumentItem,
  key cast('' as esokz )                                                     as PurchasingInfoRecordCategory,
  key PurchasingGroup,
      concat( PurchaseContract, concat( '/' , PurchaseContractItem) )        as FormattedDocumentKey,
      cast(PurchaseContract as vdm_purchasecontract )                        as PurchaseContract,
      cast(PurchaseContractItem as vdm_purchasecontractitem)                 as PurchaseContractItem,

      cast('' as vdm_purchaseorder)                                          as PurchaseOrder,
      cast('' as vdm_purchaseorderitem )                                     as PurchaseOrderItem,

      cast('' as fikz_reginv_invoice )                                       as Invoice,

      cast( cast( '' as abap.char(10)) as mm_a_info_record preserving type ) as PurchasingInfoRecord,

      cast('' as budat )                                                     as InvoiceDate,
      CreationDate,
      DocumentChangeDate,

      PurchasingOrganization,

      Supplier,

      Material,

      MaterialGroup,

      CompanyCode,

      Plant,

      PurchaseContract                                                       as DocNumber,
      //    @Semantics.text: true

      cast( '01' as mm_pur_ana_de_doctype )                                  as PurchasingDocumentType,

      Currency                                                               as DocumentCurrency,
      @Semantics.currencyCode: true
      :P_DisplayCurrency                                                     as DisplayCurrency,

      cast('' as bstme)                                                      as PurchaseOrderQuantityUnit,
      cast(0 as bstmg)                                                       as OrderQuantity,

      OrderQuantityUnit,
      TargetQuantity,
      cast( case when TotalNumberOfChanges > 0
                  then division( ReleaseOrderItemNetAmount , TotalNumberOfChanges, 3 )
                 else ReleaseOrderItemNetAmount
      end as mm_a_release_order_net_amount )                                 as ReleaseOrderItemNetAmount,

      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast( PurchaseContractTargetAmount as mm_a_target_amount )             as TargetAmount,

      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast(0 as mm_pur_ana_de_invoice_amount)                                as InvoiceAmtInDisplayCurrency,

      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast(0 as purchase_order_net_amount)                                   as PurOrdNetAmountInDisplayCrcy,

      @DefaultAggregation:#AVG
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      fltp_to_dec( ContractNetPriceAmount as mm_pur_ana_mat_avg_price )      as NetPriceAmount,

      @DefaultAggregation:#AVG
      @Semantics.amount.currencyCode: 'DisplayCurrency'

      cast(
           case
           when NetPriceQuantity = 0
           then 0
           else
                division( fltp_to_dec( ContractNetPriceAmount as mm_pur_ana_mat_net_unit_price ) , NetPriceQuantity , 5 )
           end
                as mm_pur_ana_mat_net_unit_price )                           as NetUnitPriceAmount,

      cast( NetPriceQuantity as abap.quan( 13, 3 ) )                         as NetPriceQuantity,
      OrderPriceUnit,
      cast(0 as abap.quan(13,3) )                                            as QuantityInBaseUnit,
      cast('' as abap.char(1)) as BOMHasHeaderInfoRecord,
      NumberOfChanges                                                        as NumberOfContractChanges,
      cast( 0 as abap.int4)                                                  as NumberOfPriceChanges,

      @DefaultAggregation: #SUM
      cast(0 as mm_a_numberofpurchaseorders )                                as NumberOfPurchaseOrders,
      cast(0 as menge_d)                                                     as InvoiceQuantity
      //cast (0 as verpr) as MaterialMovingAvgPrice

}
union all select from P_InvoicePriceVariance ( P_DisplayCurrency : $parameters.P_DisplayCurrency,
                                               P_Material : $parameters.P_Material, P_StartDate: $parameters.P_StartDate,
                                                         P_EndDate: $parameters.P_EndDate )

{
  key cast( Invoice as mm_a_purchasing_document )                           as PurchasingDocument,
  key cast( '' as ebelp)                                                    as PurchasingDocumentItem,
  key ''                                                                    as ChangeDocument,

  key MaterialDocumentYear,
  key cast( MaterialDocumentItem as mm_a_material_doc_item )                as MaterialDocumentItem,

  key cast('' as esokz )                                                    as PurchasingInfoRecordCategory,
  key PurchasingGroup,

      Invoice                                                               as FormattedDocumentKey,

      cast('' as vdm_purchasecontract)                                      as PurchaseContract,
      cast('' as vdm_purchasecontractitem )                                 as PurchaseContractItem,

      cast('' as vdm_purchaseorder)                                         as PurchaseOrder,
      cast('' as vdm_purchaseorderitem )                                    as PurchaseOrderItem,

      cast(Invoice as fikz_reginv_invoice)                                  as Invoice,

      cast(cast( '' as abap.char(10)) as mm_a_info_record preserving type ) as PurchasingInfoRecord,

      InvoiceDate,
      cast( InvoiceDate as budat )                                          as CreationDate,
      cast( InvoiceDate as budat )                                          as DocumentChangeDate,

      PurchasingOrganization,

      Supplier,

      Material,

      MaterialGroup,

      CompanyCode,

      Plant,

      DocNumber,
      //@Semantics.text: true

      cast( '03' as mm_pur_ana_de_doctype )                                 as PurchasingDocumentType,

      Currency                                                              as DocumentCurrency,
      @Semantics.currencyCode: true
      DisplayCurrency,

      PurchaseOrderQuantityUnit,
      cast(0 as bstmg)                                                      as OrderQuantity,

      cast( '' as bstme )                                                   as OrderQuantityUnit,
      cast( 0 as ktmng )                                                    as TargetQuantity,
      cast( 0 as mm_a_release_order_net_amount )                            as ReleaseOrderItemNetAmount,
      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast(0 as mm_a_target_amount)                                         as TargetAmount,

      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast( InvoiceAmtInDisplayCurrency as mm_pur_ana_de_invoice_amount)    as InvoiceAmtInDisplayCurrency,

      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast(0 as purchase_order_net_amount)                                  as PurOrdNetAmountInDisplayCrcy,

      @Semantics.amount.currencyCode: 'DisplayCurrency'
      @DefaultAggregation:#AVG
      cast( NetPriceAmount as mm_pur_ana_mat_avg_price    )                 as NetPriceAmount,

      @DefaultAggregation:#AVG
      @Semantics.amount.currencyCode: 'DisplayCurrency'

      cast( division( InvoiceAmtInDisplayCurrency,
                 QtyInPurchaseOrderPriceUnit,
                 3
       ) as mm_pur_ana_mat_net_unit_price    )

                                                                            as NetUnitPriceAmount,

      cast( QtyInPurchaseOrderPriceUnit as abap.quan( 13, 3 ) )             as NetPriceQuantity,
      OrderPriceUnit,
      cast(0 as abap.quan(13,3) )                                           as QuantityInBaseUnit,
      cast('' as abap.char(1)) as BOMHasHeaderInfoRecord,
      cast(0 as abap.int4)                                                  as NumberOfContractChanges,
      cast( 0 as abap.int4)                                                 as NumberOfPriceChanges,
      @DefaultAggregation: #SUM
      cast(0 as mm_a_numberofpurchaseorders )                               as NumberOfPurchaseOrders,
      Quantity                                                              as InvoiceQuantity
      //cast (0 as verpr) as MaterialMovingAvgPrice


}

union all select from P_PurchaseOrderPriceVariance (  P_DisplayCurrency : $parameters.P_DisplayCurrency,
                                                      P_Material : $parameters.P_Material, P_StartDate: $parameters.P_StartDate,
                                                         P_EndDate: $parameters.P_EndDate           )

{

  key cast( PurchaseOrder as mm_a_purchasing_document )                     as PurchasingDocument,
  key cast( PurchaseOrderItem as ebelp )                                    as PurchasingDocumentItem,
  key ''                                                                    as ChangeDocument,

  key cast('' as gjahr )                                                    as MaterialDocumentYear,
  key cast('' as mm_a_material_doc_item )                                   as MaterialDocumentItem,
  key cast('' as esokz )                                                    as PurchasingInfoRecordCategory,

  key PurchasingGroup,

      concat( PurchaseOrder, concat( '/' , PurchaseOrderItem) )             as FormattedDocumentKey,

      cast('' as vdm_purchasecontract)                                      as PurchaseContract,
      cast('' as vdm_purchasecontractitem )                                 as PurchaseContractItem,

      cast(PurchaseOrder as vdm_purchaseorder)                              as PurchaseOrder,
      PurchaseOrderItem,


      cast('' as fikz_reginv_invoice )                                      as Invoice,


      cast(cast( '' as abap.char(10)) as mm_a_info_record preserving type ) as PurchasingInfoRecord,

      cast('' as budat )                                                    as InvoiceDate,
      PurchaseOrderDate                                                     as CreationDate,
      cast(PurchaseOrderDate as budat )                                     as DocumentChangeDate,

      PurchasingOrganization,

      Supplier,

      Material,

      MaterialGroup,

      CompanyCode,

      Plant,

      DocNumber,
      //@Semantics.text: true

      cast( '04' as mm_pur_ana_de_doctype )                                 as PurchasingDocumentType,

      DocumentCurrency,
      @Semantics.currencyCode: true
      DisplayCurrency,

      PurchaseOrderQuantityUnit,
      OrderQuantity,

      cast( '' as bstme )                                                   as OrderQuantityUnit,
      cast( 0 as ktmng )                                                    as TargetQuantity,

      cast( case when PurchaseContract = '' and PurchasingInfoRecord = ''
                  then PurOrdNetAmountInDisplayCrcy
                 else 0 end
       as mm_a_release_order_net_amount )                                   as ReleaseOrderItemNetAmount,
      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast(0 as mm_a_target_amount)                                         as TargetAmount,

      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast(0 as mm_pur_ana_de_invoice_amount)                               as InvoiceAmtInDisplayCurrency,

      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      PurOrdNetAmountInDisplayCrcy,

      @Semantics.amount.currencyCode: 'DisplayCurrency'
      @DefaultAggregation:#AVG
      cast( NetPriceAmount as mm_pur_ana_mat_avg_price )                    as NetPriceAmount,

      @DefaultAggregation:#AVG
      @Semantics.amount.currencyCode: 'DisplayCurrency'

      cast( case
            when NetPriceQuantity = 0
                 then 0
                 else
                   division( NetPriceAmount,
                 NetPriceQuantity, 5 )
                 end as mm_pur_ana_mat_net_unit_price )                     as NetUnitPriceAmount,

      cast( NetPriceQuantity as abap.quan( 13, 3 ) )                        as NetPriceQuantity,
      OrderPriceUnit,
      cast(0 as abap.quan(13,3) )                                           as QuantityInBaseUnit,
       cast('' as abap.char(1)) as BOMHasHeaderInfoRecord,
      cast(0 as abap.int4)                                                  as NumberOfContractChanges,
      cast( 0 as abap.int4)                                                 as NumberOfPriceChanges,
      @DefaultAggregation: #SUM
      cast(1 as mm_a_numberofpurchaseorders )                               as NumberOfPurchaseOrders,
      cast(0 as menge_d)                                                    as InvoiceQuantity
      //cast (0 as verpr) as MaterialMovingAvgPrice


}

union all select from P_InfoRecordPriceVariance1 (  P_DisplayCurrency : $parameters.P_DisplayCurrency,
                                                    P_Material : $parameters.P_Material, P_StartDate: $parameters.P_StartDate,
                                                         P_EndDate: $parameters.P_EndDate )

{
  key cast( PurchasingInfoRecord as mm_a_purchasing_document )         as PurchasingDocument,
  key cast( '' as ebelp )                                              as PurchasingDocumentItem,
  key ChangeDocument,

  key cast('' as gjahr )                                               as MaterialDocumentYear,
  key cast('' as mm_a_material_doc_item )                              as MaterialDocumentItem,
  key PurchasingInfoRecordCategory,

  key PurchasingGroup,

      PurchasingInfoRecord                                             as FormattedDocumentKey,

      cast('' as vdm_purchasecontract)                                 as PurchaseContract,
      cast('' as vdm_purchasecontractitem )                            as PurchaseContractItem,

      cast('' as vdm_purchaseorder)                                    as PurchaseOrder,
      cast('' as vdm_purchaseorderitem )                               as PurchaseOrderItem,

      cast('' as fikz_reginv_invoice )                                 as Invoice,

      cast( PurchasingInfoRecord as mm_a_info_record preserving type ) as PurchasingInfoRecord,

      cast('' as budat )                                               as InvoiceDate,
      CreationDate,
      DocumentChangeDate,

      PurchasingOrganization,

      Supplier,

      Material,

      MaterialGroup,

      CompanyCode,

      Plant,

      PurchasingInfoRecord                                             as DocNumber,
      cast( '02' as mm_pur_ana_de_doctype )                            as PurchasingDocumentType,

      DocumentCurrency,
      @Semantics.currencyCode: true
      :P_DisplayCurrency                                               as DisplayCurrency,

      cast('' as bstme)                                                as PurchaseOrderQuantityUnit,
      cast(0 as bstmg)                                                 as OrderQuantity,

      cast( '' as bstme )                                              as OrderQuantityUnit,
      cast( 0 as ktmng )                                               as TargetQuantity,
      //      cast( case when TotalNumberOfChanges > 0

      //                  then  division( ReleaseOrderItemNetAmount, TotalNumberOfChanges, 3 )

      //                else ReleaseOrderItemNetAmount end  as mm_a_release_order_net_amount ) as ReleaseOrderItemNetAmount,

      cast( 0 as mm_a_release_order_net_amount )                       as ReleaseOrderItemNetAmount,
      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast(0 as mm_a_target_amount)                                    as TargetAmount,

      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast(0 as mm_pur_ana_de_invoice_amount)                          as InvoiceAmtInDisplayCurrency,

      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast(0 as purchase_order_net_amount)                             as PurOrdNetAmountInDisplayCrcy,

      @Semantics.amount.currencyCode: 'DisplayCurrency'
      @DefaultAggregation:#AVG
      cast(NetPrice as mm_pur_ana_mat_avg_price )                      as NetPriceAmount,

      @DefaultAggregation:#AVG
      @Semantics.amount.currencyCode: 'DisplayCurrency'

      cast(
            case
            when MaterialPriceUnitQty = 0
            then 0
            else
                division( NetPrice, MaterialPriceUnitQty, 5 )
            end   as mm_pur_ana_mat_net_unit_price )                   as NetUnitPriceAmount,

      cast( MaterialPriceUnitQty as abap.quan( 13, 3 ) )               as NetPriceQuantity,
      PurchaseOrderPriceUnit                                           as OrderPriceUnit,
      QuantityInBaseUnit,
      
      BOMHasHeaderInfoRecord,
      cast(0 as abap.int4)                                             as NumberOfContractChanges,
      NumberOfPriceChanges,
      @DefaultAggregation: #SUM
      cast(0 as mm_a_numberofpurchaseorders )                          as NumberOfPurchaseOrders,
      cast(0 as menge_d)                                               as InvoiceQuantity
      // cast (0 as verpr) as MaterialMovingAvgPrice


}

union all select from P_MatlMovingAvgPrice ( P_Material : $parameters.P_Material , P_StartDate: $parameters.P_StartDate,
                                                         P_EndDate: $parameters.P_EndDate ) as MovingAvg
  inner join          I_ProductPlant                                                        as ProductPlant  on  MovingAvg.ValuationArea = ProductPlant.Plant
                                                                                                             and ProductPlant.Product    = MovingAvg.Material
  inner join          I_MaterialPlant                                                       as MaterialPlant on  MovingAvg.Material      = MaterialPlant.Material
                                                                                                             and MovingAvg.ValuationArea = MaterialPlant.Plant
  inner join          I_Material                                                            as MatMaster     on MatMaster.Material = MaterialPlant.Material


{
  key cast( '' as mm_a_purchasing_document )                                as PurchasingDocument,
  key cast( '' as ebelp )                                                   as PurchasingDocumentItem,
  key ''                                                                    as ChangeDocument,

  key cast('' as gjahr )                                                    as MaterialDocumentYear,
  key cast('' as mm_a_material_doc_item )                                   as MaterialDocumentItem,
  key cast('' as esokz )                                                    as PurchasingInfoRecordCategory,

      ProductPlant.PurchasingGroup                                          as PurchasingGroup,

      MovingAvg.Material                                                    as FormattedDocumentKey,

      cast('' as vdm_purchasecontract)                                      as PurchaseContract,
      cast('' as vdm_purchasecontractitem )                                 as PurchaseContractItem,

      cast('' as vdm_purchaseorder)                                         as PurchaseOrder,
      cast('' as vdm_purchaseorderitem )                                    as PurchaseOrderItem,

      cast('' as fikz_reginv_invoice )                                      as Invoice,
      cast(cast( '' as abap.char(10)) as mm_a_info_record preserving type ) as PurchasingInfoRecord,


      cast('' as budat )                                                    as InvoiceDate,
      CreationDate,
      CreationDate                                                          as DocumentChangeDate,

      cast ('' as ekorg)                                                    as PurchasingOrganization,

      cast ('' as elifn)                                                    as Supplier,

      MovingAvg.Material,

      cast ('' as matkl)                                                    as MaterialGroup,

      CompanyCode,

      //      ValuationArea as Plant,

      MaterialPlant.Plant                                                   as Plant,

      cast('00' as mm_a_doctype )                                           as DocNumber,
      cast( '00' as mm_pur_ana_de_doctype )                                 as PurchasingDocumentType,

      cast( :P_DisplayCurrency as displaycurrency )                         as DocumentCurrency,
      @Semantics.currencyCode: true
      :P_DisplayCurrency                                                    as DisplayCurrency,

      cast('' as bstme)                                                     as PurchaseOrderQuantityUnit,
      cast(0 as bstmg)                                                      as OrderQuantity,

      cast( '' as bstme )                                                   as OrderQuantityUnit,
      cast( 0 as ktmng )                                                    as TargetQuantity,
      cast( 0 as mm_a_release_order_net_amount )                            as ReleaseOrderItemNetAmount,

      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast(0 as mm_a_target_amount)                                         as TargetAmount,

      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast(0 as mm_pur_ana_de_invoice_amount)                               as InvoiceAmtInDisplayCurrency,

      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast(0 as purchase_order_net_amount)                                  as PurOrdNetAmountInDisplayCrcy,

      @Semantics.amount.currencyCode: 'DisplayCurrency'
      @DefaultAggregation:#AVG
      currency_conversion( amount => MovingPrice,
                source_currency => Currency,
                target_currency => $parameters.P_DisplayCurrency,
                exchange_rate_date => CreationDate,
                exchange_rate_type => 'M',
                error_handling => 'SET_TO_NULL' )                           as NetPriceAmount,

      @DefaultAggregation:#AVG
      @Semantics.amount.currencyCode: 'DisplayCurrency'
      cast( 0 as mm_pur_ana_mat_net_unit_price )                            as NetUnitPriceAmount,


      cast( MaterialPriceUnitQty as abap.quan( 13, 3 ) )                    as NetPriceQuantity,
      //      cast('' as bbprm)

      MatMaster.MaterialBaseUnit                                            as OrderPriceUnit,
      cast(0 as abap.quan(13,3) )                                           as QuantityInBaseUnit,
       cast('' as abap.char(1)) as BOMHasHeaderInfoRecord,
      cast(0 as abap.int4)                                                  as NumberOfContractChanges,
      cast( 0 as abap.int4)                                                 as NumberOfPriceChanges,
      @DefaultAggregation: #SUM
      cast(0 as mm_a_numberofpurchaseorders )                               as NumberOfPurchaseOrders,
      cast(0 as menge_d)                                                    as InvoiceQuantity


}