Technical Articles
Material Costs on Sales Orders
Dear ByDesign Users.
Problem Description
If you or your customer sells products on Sales Orders, you may notice the margin calculation is not entirely accurate.
This is because the estimated product cost on a sales order is a mean calculation of all the material in stock, across all business residences (if you have BR Level costing enabled). Therefores the cost, as indicated is not always a useful guide. Especially if you have large variances in costs between Business Residences.
It has to be this way, because at the time of the sales order, there is no fixed source of supply. The goods issue may happen at a different location to that originally determined on the sales order.
The Actual confirmation journal that happens when the goods are issued is taken from the material/company/business residence valuation, so financial postings are accurate.
Many organisations, however want to see their own valuation at the sales order level, and this can be achieved using the Cloud Application Studio.
Cloud Application Studio Solution.
Using the Repository Explorer in the CAS, you will find an object called
MaterialValuationData
The Material Valuation Data business object contains attributes and prices that are used in the valuation of materials and material groups.
The material-based master data of Material Valuation Data consists of financial data (such as the set of books) that supplements the master data in the foundation layer.
So with this information it is possible to create a query from the Sales Order that retrieves the current valuation Data and can display it on the Sales Order Item Level.
I did it using the “OnModify” event of the Sales Order Item.
First, determine the Selling Company:
SellingCo = item.ToRoot.SalesAndServiceBusinessArea.SalesOrganisation.CurrentSuperordinateCompany.UUID
and the Business Residence supplying the material
var itemBR = item.ShipFromItemLocation.LocationID.content;
With this data, you can create a querybyElements against the MaterialValuationData;
var qryValLevel = MaterialValuationData.ValuationLevel.QueryByElements;
There are 2 x parameters to this query
first, pass the material ID
var paramValLevel = qryValLevel.CreateSelectionParams();
paramValLevel.Add(qryValLevel.MaterialValuationDataMaterialKey.ProductID.content,”I”,”EQ”,matID.content);
Then, pass the business residence (source of supply/Ship From on the sales order) paramValLevel.Add(qryValLevel.MaterialValuationDataPermanentEstablishmentID,”I”,”EQ”,itemBR);
var resValLevel = qryValLevel.Execute(paramValLevel);
These 2 parameters will then give you a collection of valuations, including a valuation for all of the sets of books enabled in the ByDesign system.
I decided to use the valuation based on the default set of books.
So using foreach, to loop through each resValLevel record I get the setofbooksID, and using the BusinessObject
SetOfBooks
I create a query that gets me that SoB record. I then use another foreach, and look through each SoB record and each CompanyAssignment for each set of books. Then, when I find the default indicator, use that SetOfBooksID to determine the valuation.
This valuation is displayed via an extension field on the Sales Order>Item node.
This script is NOT production ready, there is no error trapping or tracing. This script has NO impact on the sales order pricing on the “Pricing & Invoicing” area, so is provided “As-Is” for your testing and experimenting.
To avoid confusion, if you implement this method, disable the option
“Do you calculate profit margins for service or sales orders and make them visible to your sales employees?”
In the Sales Price Specification section of the Project Scope.
John
/*
Add your SAP Business ByDesign scripting language implementation for:
Business Object: SalesOrder
Node: Item
Event: AfterModify
Note:
- To access the elements of the business object node,
use path expressions, for example, this.<element name>.
- To use code completion, press CTRL+J.
- This script file is mass-enabled, i.e. the keyword "this"
refers to a collection of node instances.
- The solution uses this script if:
- the value of any field in the node in which this script is contained is modified.
- the node itself is modified by another business object within the same solution.
*/
import ABSL;
import AP.FinancialAccounting.Global;
import AP.FO.ProductDataMaintenance.Global;
import AP.FO.MOM.Global;
foreach(var item in this){
if(item.IsSet()){
var matID = item.ItemProduct.ProductInternalID;
if(item.ShipFromItemLocation.IsSet()){
var itemBR = item.ShipFromItemLocation.LocationID.content;
var itemOrg = item.ToRoot.SalesAndServiceBusinessArea.SalesOrganisation;
var itemCO = itemOrg.CurrentSuperordinateCompany.UUID;
var qryValLevel = MaterialValuationData.ValuationLevel.QueryByElements;
var paramValLevel = qryValLevel.CreateSelectionParams();
paramValLevel.Add(qryValLevel.MaterialValuationDataMaterialKey.ProductID.content,"I","EQ",matID.content);
paramValLevel.Add(qryValLevel.MaterialValuationDataPermanentEstablishmentID,"I","EQ",itemBR);
var resValLevel = qryValLevel.Execute(paramValLevel);
if(resValLevel.Count()>0){
var matValPrice = resValLevel.ValuationPrice;
if(matValPrice.Count()>0){
foreach(var value in matValPrice){
var valEndDate = Date.ConvertToGlobalDateTime(value.ValidityDatePeriod.EndDate);
var valStartDate = Date.ConvertToGlobalDateTime(value.ValidityDatePeriod.StartDate);
var itemReqDate = item.FirstRequestedItemScheduleLine.DateTimePeriod.EndDateTime.ConvertToGlobalDateTime();
if(valStartDate<= itemReqDate){
if(valEndDate >= itemReqDate){
var sobID = value.SetOfBooksID;
var qrySOB = SetOfBooks.QueryByIDAndDescription;
var sobParam = qrySOB.CreateSelectionParams();
sobParam.Add(qrySOB.ID.content,"I","EQ",sobID.content);
var resSOB =qrySOB.Execute(sobParam);
if(resSOB.Count()>0){
foreach(var books in resSOB){
foreach(var company in books.CompanyAssignment){
if(company.CompanyUUID.content == itemCO.content){
if(company.SetOfBooksDefaultIndicator){
var BRValue = value.LocalCurrencyValuationPrice.Amount;
item.brValue = BRValue;
var netVal = item.PriceAndTaxCalculationItem.ItemMainTotal.CalculatedAmount;
var netCost = (item.FirstRequestedItemScheduleLine.Quantity.content)*BRValue.content;
var margin = netVal.content - netCost;
item.brMargin.content = margin;
item.brMargin.currencyCode =BRValue.currencyCode;
}
}
}
}
}
}
}
}
}
}
}
}
}
Just to clarify John Meadow's useful posting. John's initial point is that the source of supply cannot be predicted, so an average should be used.
That makes sense, but the current averaging algorithm in ByDesign is the average of the last-recorded material costs, across all business residences, regardless of whether there is any inventory at that BR.
Consider a product N that is valuated at national warehouse A (100 units), satellite warehouse B (no units), three shops C, D and E (total 6 units) and in a sales organization F that would never hold inventory, but needs to be available in case of error in intercompany accounting records.
Using the current algorithm, where a sales order uses the average cost ignoring the inventory count., we see the non-existent valuation at F, empty warehouse B and the minimal inventories at C, D and E all having the same weight as the value in the main warehouse A. So the sales order uses the average 146.90.
The margin calculation would be more helpful if the weighted average was used instead, in this example 152.46 not 146.90. This would remove the distortion from sites that have little/no stock.