Retrieve Currency Specific Price List

[VVancalbergh]

I made a Reuse function to get the Base Price List. It does the initialization step:

//Initialization query to define Workcenter View
var qry_init = SalesPriceList.QueryByGroupCode;
var qry_init_params = qry_init.CreateSelectionParams();
qry_init_params.Add(qry_init.GroupCode.content, "I", "EQ", "PLPRICE1"); //Net Prices
var qry_init_result = qry_init.Execute(qry_init_params);

Then the query using the following filters:

TypeCode = "7PL0" //Price inside a list
ReleaseStatusCode = "3" //Released price list
StartTimePoint = Context.GetCurrentUserDate()
EndTimePoint = Context.GetCurrentUserDate()
PriceSpecificationPropertyValuationPriceSpecificationElementPropertyValuation1.PriceSpecificationElementPropertyReference.PriceSpecificationElementPropertyID.content = "CND_PRODUCT_ID"
PriceSpecificationPropertyValuationPriceSpecificationElementPropertyValuation2.PriceSpecificationElementPropertyReference.PriceSpecificationElementPropertyID.content = "CND_PRODUCT_ID_TYPE_CODE"
PriceSpecificationPropertyValuationPriceSpecificationElementPropertyValuation3.PriceSpecificationElementPropertyReference.PriceSpecificationElementPropertyID.content = "CND_PRODUCT_TYPE_CODE"
PriceSpecificationPropertyValuationPriceSpecificationElementPropertyValuation4.PriceSpecificationElementPropertyReference.PriceSpecificationElementPropertyID.content = "PRC_PRICE_LIST"<br>

(What do those 4 last filters even do?? There's no explanation! I assume they define what is in the result of the query. There are 10 possible filters. What more can I add? Can I already filter on a specific Product by filtering PriceSpecificationPropertyValuationPriceSpecificationElementPropertyValuation1.PriceSpecificationElementPropertyValue.ID.content == my product key?)

The query.Execute() then returns a collection of Price Lists. Each pricelist has a collection of PriceSpecification nodes. These each contain an Amount (the price) and a collection of PropertyValuation ("pv") nodes with information telling what the price relates to.

I loop through the PropertyValuation nodes and if pv.PriceSpecificationElementPropertyValuation.PriceSpecificationElementPropertyReference.PriceSpecificationElementPropertyID.content == "CND_PRODUCT_ID" then I can compare if pv.PriceSpecificationElementPropertyValuation.PriceSpecificationElementPropertyValue.ID.content == the product I'm looking for.

If it matches I take the PriceSpecification's price.

Now, the database also has Currency Specific Price Lists. In the Debugger I see this code only returns Base Price Lists. Why? How can I make it return Currency Specific Price Lists instead/as well?

[VVancalbergh · Accepted solution]

Ralph's suggestion that they should show up in my query (which they didn't) sort of led me to a working answer: Just read everything you can find and work it out yourself.

I pored it into a reuse function with parameters:

ProductKey: AP.Common.GSDT:ProductInternalID

CurrencyCode: AP.PDI.bo:String

Result: AP.Common.GDT:Amount

import ABSL;
import AP.Common.GDT;
import AP.FO.PriceAndTax.Global;
import AP.PDI.bo;

var baseCurrency = "EUR"; //TODO: Get from current company
var targetCurrency = Parameter::CurrencyCode;
if (targetCurrency.IsInitial())
{
	targetCurrency = baseCurrency;
}


var basePrice : Amount;
var currencyPrice : Amount;


var priceData : collectionof String;


if (!ProductKey.IsInitial())
{
	//Initialization query to define Workcenter View
	var qry_init = SalesPriceList.QueryByGroupCode;
	var qry_init_params = qry_init.CreateSelectionParams();
	qry_init_params.Add(qry_init.GroupCode.content, "I", "EQ", "PLPRICE1"); //Net Prices
	var qry_init_result = qry_init.Execute(qry_init_params);


	var query = SalesPriceList.QueryByTypeCodeAndPropertyIDAndPropertyValue;
	var selParams = query.CreateSelectionParams();
	var queryResult = query.Execute(selParams);


	foreach (var priceList in queryResult
		.Where(pl =>
			pl.Status.ReleaseStatusCode == "3" &&
			(pl.CurrencyCode == targetCurrency || pl.CurrencyCode == baseCurrency)))
	{
		var priceListID = priceList.ID.content;
		priceData.Add(priceListID);
		foreach (var price in priceList.PriceSpecification
			.Where(ps =>
				ps.ValidityPeriod.StartTimePoint.Date <= Context.GetCurrentUserDate() &&
				ps.ValidityPeriod.EndTimePoint.Date >= Context.GetCurrentUserDate() &&
				ps.Status.ReleaseStatusCode == "3"))
		{
			foreach (var propertyValuation in price.PropertyValuation.Where(pv =>
				pv.PriceSpecificationElementPropertyValuation.PriceSpecificationElementPropertyReference.PriceSpecificationElementPropertyID.content == "CND_PRODUCT_ID"))
			{
				var propID = propertyValuation.PriceSpecificationElementPropertyValuation.PriceSpecificationElementPropertyReference.PriceSpecificationElementPropertyID.content;
				var propValue = propertyValuation.PriceSpecificationElementPropertyValuation.PriceSpecificationElementPropertyValue.ID.content;
				priceData.Add(priceListID + " / " + propID + " / " + propValue + " = " + price.Amount.ToString());
				if (propValue == ProductKey.content.RemoveLeadingZeros())
				{
					if (priceList.CurrencyCode == targetCurrency)
					{
						currencyPrice = price.Amount;
					}
					if (priceList.CurrencyCode == baseCurrency)
					{
						basePrice = price.Amount;
					}
				}
			}
		}
	}
}


if (!currencyPrice.IsInitial())
{
	return currencyPrice;
}
else
{
	if (!basePrice.IsInitial())
	{
		return basePrice.ConvertCurrency(targetCurrency);
	}
	else
	{
		return basePrice;
	}
}

it may not be the best way, but it works with the horribly obtuse structure and the hodgepodge of information we can find on it. The priceData is something to log what prices you pass over. Once you're happy with it you can put a return in the "if (priceList.CurrencyCode == targetCurrency)" section.

[former_member197733]

Hi Vicent, your thread have many questions. Next time, please can create different topics. It makes it easier for people to respond.

What do those 4 last filters even do??

You can find a quick explanation here (Product = CND_PRODUCT_ID + CND_PRODUCT_ID_TYPE_CODE + CND_PRODUCT_TYPE_COD.

There are more details on SAP Note 1398753 about all these valuation properties and their supported values.

Can I already filter on a specific Product by filtering like ..,ElementPropertyValue.ID.content == my product key?

I am afraid it isn't that simple as you would need to compose the product using the structure above.

I see this code only returns Base Price Lists. Why? How can I make it return Currency Specific Price Lists instead/as well?

The Currency Dependent Price should be a result in the same query. Here is video of how to configure it (thanks murthy.v). In my tests using the ws query service, they showed up. Should be the same using ABSL.

PS: I would also recommend you to take a look in how to retrieve the price lists using webservices. IMHO a more simple (and elegant) approach. Here are samples using Odata and the specification using SOAP for the old school.

[VVancalbergh]

Hi Ralph,

Thanks for the effort of gathering up what is available on this monstrosity. I've tried a lot of different filtering and none returned my Currency Specific Price List. As you can see in my own answer I ended up having to do the filters manually.

As for your suggestion to call SAP's own web service, I would prefer to not go down that road because the overhead for that is too big.

Next implementation I plan on simply subscribing to the Event-BeforeSave of the SalesPriceList and store the pricing info in a more readable format.

I would appreciate it if the development team would add a couple of simple functions to make "getting a price" less of an ordeal in the future. Something like: GetPrice, GetDiscount, GetDiscountedPrice with the following parameters: BusinessPartnerID, ProductID, Quantity (to be expanded per your taste).