P_MATERIALPRICEVARIANCE
Material Price Variance
P_MATERIALPRICEVARIANCE is a CDS View in S/4HANA. Material Price Variance. 1 CDS views read from this table.
CDS Views using this table (1)
| View | Type | Join | VDM | Description |
|---|---|---|---|---|
| C_MaterialPriceVariance | view | from | CONSUMPTION | Material Price Variance |
@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
}