During a recent consulting engagement, a customer asked for an easy way to look up a customer’s current, effective price for a range of items. While there isn’t a pre-existing report available in Acumatica, it is possible to build generic inquiry that comes close.
Note: Before we begin, I need to give credit where credit is due. While my skills developing generic inquires (GI) have grown rather strong, this GI was created by my friend Tim Rodman while he was working as a business consultant with Acumatica and even he had to reach out to one or more internal ‘techno-dev geniuses’ like Gabriel Michaud.
For those not familiar with how sales prices are established in Acumatica, we have a white paper covering the details you can pull down from here.
The challenge with compiling all the possible prices for an item on a single line of a report is that except for the items default price, all the remaining prices are stored as individual rows in the same table.
There are potentially seven different types of sales prices for a single item assuming we aren’t taking advantage of multiple price breaks. These seven are applied in the following order to determine which is used by Acumatica on a Sales Order or Invoice:
Acumatica does not look for the lowest possible price but rather walks down the list in the order listed above. The first effective price found is used.
means that to get all possible price options on a single row of a generic inquiry, we will need to reference the table holding sales prices (ARSalesPrice) a maximum of six times. The number of times you reference the table will depend on how many of the above sales price types you use in your Acumatica deployment.
In the GI we built and documented below, we use six of the seven options listed above. The only one we did not use was a Base Promotional pricing option.
The following shows the tables involved and the aliases assigned.
Unlike any other GI I have ever built; this GI makes very unique use of table relationships to link specific rows from the ARSalesPrice table.
As the above shows, there is a lot of logic built into most of the links. In our example, the user may enter an effective date so valid prices can be determined at any time. The last link condition above limits the returned value to Base Prices only.
The customer involved is limited to the customer selected a parameter defined below.
Location is required when a customer has multiple ship-to addresses.
As discussed earlier, a date parameter is used to filter by effective dates. The fifth link limits us to Customer Price Class Sales Prices. The last link ensures we are excluding promotional prices.
This link is identical the previous link except the last condition looks for “=True” value rather than “=False”.
Once again, the linkages filter based using an effective date and limits to non-promotional prices. The fifth linkage establishes that only customer specific sales prices are used.
Once again, this link is identical to its non-promotional sibling except it looks for a “=True” value in isPromotionalPrice.
In our example, we also wanted to display the inventory item’s UPC code within the output.
Two parameters are required to limit our rows to a specific customer and a specific effective date.
Typically, parameters are used on the conditions tab to limit the rows returned. However, in this GI, the parameters were used to limit rows through link conditions.
The results grid is pretty straight forward. However, determining the effective date requires us to build a formula to follow the hierarchy outlined earlier. This formula is as follows:
=IsNull([PriceCustomerPromotional.SalesPrice],
IsNull([PriceCustomer.SalesPrice],
IsNull([PriceCustomerPriceClassPromotional.SalesPrice],
IsNull([PriceCustomerPriceClass.SalesPrice],
IsNull([PriceBase.SalesPrice],[InventoryItem.BasePrice])))))
Entry Point and Navigation
These tabs are not used in this inquiry
As stated at the start of the white paper, this is generic inquire uses the most complex table join logic I have ever encountered. The benefit of working through this GI is the limitless number of possibilities it reveals for those with creative minds.
