How price matrix gathers/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.CustomerProduct
            PriceMatrix.RecordTypeName.CustomerProduct (Parent Customer)
            PriceMatrix.RecordTypeName.CustomerProductPriceCode
            PriceMatrix.RecordTypeName.CustomerProductPriceCode (Parent Customer)
            PriceMatrix.RecordTypeName.CustomerPriceCodeProduct
            PriceMatrix.RecordTypeName.CustomerPriceCodeProduct (Parent Customer)
            PriceMatrix.RecordTypeName.CustomerPriceCodeProductPriceCode
            PriceMatrix.RecordTypeName.CustomerPriceCodeProductPriceCode (Parent Customer)
            PriceMatrix.RecordTypeName.Customer
            PriceMatrix.RecordTypeName.Customer (Parent Customer)
            PriceMatrix.RecordTypeName.CustomerPriceCode
            PriceMatrix.RecordTypeName.CustomerPriceCode (Parent Customer)
            PriceMatrix.RecordTypeName.Product
            PriceMatrix.RecordTypeName.ProductPriceCode
            PriceMatrix.RecordTypeName.ProductSale

Once the system finds a specific price matrix record to use, it will follow the calculation logic to determine the price.

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.

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.