How price matrix gathers and prioritizes records
The basic approach is to do a single query that looks for a set of records that are current and where the ProductKeyPart = the current product, the current product's price code, or nothing and where the CustomerKeyPart = the current customer, the current customer's BillTo, the current customer's price code or nothing.
For example, if we have customer DALTON (id = 4C75B59E-3980-4DFF-B049-9F5800E9B326, price code = DEALER) and Product 1032FW (id = 8A9992A0-477D-4089-99A9-9F5800EA202C, price code = PLMBG) the system will gather all the records using a statement similar to:
SELECT * FROM PriceMatrix WHERE CustomerKeyPart IN('4C75B59E-3980-4DFF-B049-9F5800E9B326','DEALER','') AND ProductKeyPart IN('8A9992A0-477D-4089-99A9-9F5800EA202C','PLMBG','') AND ActivateOn <= GETDATE() AND (DeactivateOn IS NULL OR DeactivateOn > GETDATE())As a side note, if the customer passed in is a ShipTo, it will also include the BillTo's customer id and price code in the query.
Once the dataset is returned, the system will go through and remove any mismatches in warehouse, currency, or unit of measure
The system will then organize the records trying for the "best match" which means organizing the records by the record type defined above and inserting the BillTo customer after it tries to find the ShipTo customer's pricing information using the following order:
PriceMatrix.RecordTypeName.CustomerProductPriceMatrix.RecordTypeName.CustomerProduct (Parent Customer)PriceMatrix.RecordTypeName.CustomerProductPriceCodePriceMatrix.RecordTypeName.CustomerProductPriceCode (Parent Customer)PriceMatrix.RecordTypeName.CustomerPriceCodeProductPriceMatrix.RecordTypeName.CustomerPriceCodeProduct (Parent Customer)PriceMatrix.RecordTypeName.CustomerPriceCodeProductPriceCodePriceMatrix.RecordTypeName.CustomerPriceCodeProductPriceCode (Parent Customer)PriceMatrix.RecordTypeName.CustomerPriceMatrix.RecordTypeName.Customer (Parent Customer)PriceMatrix.RecordTypeName.CustomerPriceCodePriceMatrix.RecordTypeName.CustomerPriceCode (Parent Customer)PriceMatrix.RecordTypeName.ProductPriceMatrix.RecordTypeName.ProductPriceCodePriceMatrix.RecordTypeName.ProductSale Furthermore, the records will be placed in order where more detailed information is present - for example, a record with a warehouse specified would come before one where it is not.
Once the system finds a specific price matrix record to use, it will follow the calculation logic to determine the price.
Finally, if an active ProductSale record exists, it will calculate the price from that, compare it to the customer's calculated price and return the best price available.