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;	
												}
											}
										}
									}
								}
							}
						}
					}
				}
			}
		}
	}
}