White Paper

Developing Customer Pricing Generic Inquiries

Published on

December 8, 2017

Shawn Slavin

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:

  • Customer specific promotional price
  • Price Class specific promotional price
  • Base promotional price
  • Customer specific price
  • Price Class specific price
  • Base price
  • Item Default price

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.

Tables

The following shows the tables involved and the aliases assigned.

Acumatica Generic Inquiry customer pricing lookup

Relations

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.

Base Price

Base price generic lookup in Acumatica ERP

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.

Customer

Customer pricing and inventory lookup in Acumatica's Generic Inquiry

The customer involved is limited to the customer selected a parameter defined below.

Location

Customer Location Lookup in Acumatica

Location is required when a customer has multiple ship-to addresses.

Customer Price Class

Price class look up in acumatica generic inquiry

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.

Customer Price Class Promotional

This link is identical the previous link except the last condition looks for “=True” value rather than “=False”.

Customer Specific Sales Price

data field links for active relation for customer specific sales price

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.

Customer Specific Promotional Sales Price

Once again, this link is identical to its non-promotional sibling except it looks for a “=True” value in isPromotionalPrice.

Cross Reference

cross reference upc code within inquiry output

In our example, we also wanted to display the inventory item’s UPC code within the output.

Parameters

acumatica generic inquiry lookup parameters

Two parameters are required to limit our rows to a specific customer and a specific effective date.

Conditions

customer lookup conditions in acumatica's generic inquiry

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.

Grouping

customer grouping in generic inquiry



Sort Order

sort order in generic inquiry


Results Grid

results grid in acumatica's generic inquiry

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

In Conclusion

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.

Enjoy and explore!



Written By