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.