Subject:
The steps here will describe a starting point on how to obtain a Price List per Customer/Item using SQL API.
Resolution:
Here are the steps to follow:
- Make a complete restorable backup of the Microsoft Dynamics GP.
- Verify Omni Price is installed and registered. In Microsoft Dynamics GP, go to Help | Rockton Software Support.
- Download the SQL API Scripts for all Dynamics GP and Great Plains from our Products Downloads page: https://www.rocktonsoftware.com/gp/resources/product-downloads.
- Review the information in the Omni Price SQL API Read Me.txt file.
- In SQL Server Management Studio, open a New Query, and then run the OmniPriceSQLAIScripts*.sql script against the Company database that has Omni Price installed.
- In SQL Server Management Studio, open a New Query, and then run the following script against the Company database that has Omni Price installed.
SELECT TOP 1000 CUSTNMBR, ITEMNMBR, OP.*
FROM RM00101 CROSS JOIN IV00101
CROSS APPLY [dbo].[OPCalculateUnitPriceSimple3] (CURRCOST, CUSTNMBR, ITEMNMBR, ’04/12/2017′) AS OP
Notes pertaining to the script in Step 6:
- This is a starting point for returning a price list. There are many variables to how Omni Price is set up, and this is in no way guaranteed to be an official returned list of correct pricing. It is presented as a starting point for developers and administrators to return information from Omni Price.
- Remove the phrase “TOP 1000” in the script listed in step 6 in order to return the entire list. Keep in mind the number of records returned equals the number of Customer multiplied by the number of Items. Therefore, 400 customers with 600 items will return 240,000 rows. This may take some time.
- This StartingPrice column calculation uses CURRCOST (Current Cost) as a starting price taken from the Item Master. If you want the Standard Cost, you can change CURRCOST to STNDCOST (Standard Cost). To get the List Price, an additional join will be required to the inventory table containing the List Price. Keep in mind that depending on the pricing options selected on a contract, a “starting price” may or may not be required, and it may also vary from contract to contract or line to line.
- The date shown here is for April 12, 2017. You may want to change the date, which can affect results.
- If you would like to see values specific for a customer (such as AARONFIT0001) where the ContractID column is not blank, you can add the following where clause to the end of the script:
WHERE CUSTNMBR = ‘AARONFIT0001’ AND OP.ContractID <> ”