​How to Read Discount List from application studio

[4NDR]

Hello,
I'm in need of reading the Customer Discount Lists (Overall Customer 7PL1 and Cust. Products7PL2) out of the standard Business Object.

The documentation in the application studio is severely lacking and it only provides an example to read the valid price list.

I know I have to run the GroupCode Query first with "PLDISC1" as its condition to initialize the discount price lists, but the subsequent "QueryByTypeCodeAndPropertyIDAndPropertyValue" requires some parameters that are unexplained anywhere...

Does anyone know which values should be filled in as the various

"PriceSpecificationPropertyValuationPriceSpecificationElementPropertyValuation" entries to have the query produce something?

By the way, this is the working query used for the Price list (PLPRICE1).

// List type 7PL0 = Price inside a list
selParams.Add(query.TypeCode.content, "I", "EQ", "7PL0");
// Released price list
selParams.Add(query.ReleaseStatusCode, "I", "EQ", "3" );
// Valid today
selParams.Add(query.ValidityPeriod.StartTimePoint.Date, "I", "EQ", Context.GetCurrentUserDate());
selParams.Add(query.ValidityPeriod.EndTimePoint.Date, "I", "EQ", Context.GetCurrentUserDate());
// Base price list has no header fields (= PropertyValuation), but four item fields (= PriceSpecificationPropertyValuation1-4)
selParams.Add(query.PriceSpecificationPropertyValuationPriceSpecificationElementPropertyValuation1.PriceSpecificationElementPropertyReference.PriceSpecificationElementPropertyID.content, "I", "EQ", "CND_PRODUCT_ID");
selParams.Add(query.PriceSpecificationPropertyValuationPriceSpecificationElementPropertyValuation2.PriceSpecificationElementPropertyReference.PriceSpecificationElementPropertyID.content, "I", "EQ", "CND_PRODUCT_ID_TYPE_CODE");
selParams.Add(query.PriceSpecificationPropertyValuationPriceSpecificationElementPropertyValuation3.PriceSpecificationElementPropertyReference.PriceSpecificationElementPropertyID.content, "I", "EQ", "CND_PRODUCT_TYPE_CODE");
selParams.Add(query.PriceSpecificationPropertyValuationPriceSpecificationElementPropertyValuation4.PriceSpecificationElementPropertyReference.PriceSpecificationElementPropertyID.content, "I", "EQ", "PRC_PRICE_LIST");
// The query should return only one price list (due to defined query parameters)

[4NDR · Accepted solution]

Self answer!

While I still don't know the entire list of possible parameter commands to use, I finally found what I was looking for in terms of which commands are needed to get results. They follow the same rules of the Migration Template referenced in the KBA below:

https://userapps.support.sap.com/sap/support/knowledge/en/2487654

In practice to get product level discounts:

• General sheet: fill the Account ID field
• Items sheet: fill the Product ID field and leave the Account ID, Customer Group and Product Category ID fields empty

means that for the query it absolutely NEEDS the CND_BUYER_ID parameter in the "PropertyValuationPriceSpecificationElementPropertyValuation" block, and the three product parameters (CND_PRODUCT_ID, CND_PRODUCT_ID_TYPE_CODE and CND_PRODUCT_TYPE_CODE) in the "PriceSpecificationPropertyValuationPriceSpecificationElementPropertyValuation" fields.

[VVancalbergh]

For starters I agree that the way you have to look up a price is all kinds of "messed up" in ByDesign. It's a horribly obtuse system. That said, I've come to some understanding of how it works.

First: if you EVER want to query for prices and discounts then you should initialize for both:

//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
qry_init_params.Add(qry_init.GroupCode.content, "I", "EQ", "PLDISC1"); //Discounts
var qry_init_result = qry_init.Execute(qry_init_params);

Secondly, they're not all actual filters. Think of them more like parameters: The 'PriceSpecificationElementPropertyReference.PriceSpecificationElementPropertyID' query parameter is only half of the story. They always come in pairs. The first one tells you WHAT property of a price you want (CND_PRODUCT_ID). The second parameter then tells you which product. Example:

selParams.Add(query.PriceSpecificationPropertyValuationPriceSpecificationElementPropertyValuation1.PriceSpecificationElementPropertyReference.PriceSpecificationElementPropertyID.content,"I","EQ","CND_PRODUCT_ID");
selParams.Add(query.PriceSpecificationPropertyValuationPriceSpecificationElementPropertyValuation1.PriceSpecificationElementPropertyValue.ID.content,"I","EQ",RelevantProductID);

And finally: I hard recommend you build a custom BO to store a copy of the prices and discounts. Querying THAT will be much faster and better for your sanity. In fact: It's even what SAP recommends in their sample code!

Just set up an Internal Communication from SalesPriceList to custom BO A. Then in the BeforeSave of BO A, you Retrieve the changed SalesPriceList (no need to do the above funky query), read all values from it and dump them in a flat BO B and/or C (one for Prices, one for Discounts).

[4NDR]

Hello Vincent, thanks for your reply. I've seen on similar threads that using both PLPRICE1 and PLDISC1 at the same time might not work, and if that is needed "PLALL1" should be used instead to load both.

I did use a business object to load the list price in, but my problem lied in the correct set of "PriceSpecificationPropertyValuationPriceSpecificationElementPropertyValuation" to use so that a Discount could be picked up.

While I still don't know the entire list of possible parameter commands to use, I finally found what I was looking for in terms of which commands are needed to get results. They follow the same rules of the Migration Template referenced in the KBA below:

https://userapps.support.sap.com/sap/support/knowledge/en/2487654

In practice to get product level discounts:

• General sheet: fill the Account ID field
• Items sheet: fill the Product ID field and leave the Account ID, Customer Group and Product Category ID fields empty

means that for the query it absolutely NEEDS the CND_BUYER_ID parameter in the "PropertyValuationPriceSpecificationElementPropertyValuation" block, and the three product parameters (CND_PRODUCT_ID, CND_PRODUCT_ID_TYPE_CODE and CND_PRODUCT_TYPE_CODE) in the "PriceSpecificationPropertyValuationPriceSpecificationElementPropertyValuation" fields.

It would have been really nice if this was included in the documentation of the business object in the first place. It would have saved a lot of time and headaches. Hopefully this will speed up the job for others in the future.