Microsoft Dynamics GP (Archived)

Item Unit Price - Sql Query

(0) Share

Report

Posted on by Selva D

Hi All,

I need to get unit price of the item which is reflecting in SOP.

Need a sql query something like below

Select UNitPrice from Table Where ItemNumber='XXXXXX'

Thanks

Selva D

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (1)

Community Member on at

Like (0)

Report

This will get you started. If the item pricing method is CurrencyAmount:

SELECT UOMPRICE FROM IV00108 WHERE itemnmbr = 'xxx' AND uofm = 'each' AND curncyid = 'USD' AND PRCLEVEL= 'standard'

Was this reply helpful? Yes No
Praveen Kumar RR 1,552 on at

Like (0)

Report

If you are aware of GP Tables for SOP and Inventory then you can simply fetch the information by passing ITEMNMBR as parameter in IV00102 table.

If you still not clear i am ready to help you by having a Teamviewer session.

Was this reply helpful? Yes No
Suggested answer

Heather Roggeveen 9,146 on at

Like (1)

Report

As Jon noted below, the key table is IV00108. Please note however that if you are using a percentage of option as your pricing method, the UOMPRICE filed actually holds the percentage that is applied. Then you may need to refer to IV00101 if using percentage of standard cost or current cost or table IV00105 is using percentage of list price.

It is also worth noting that when looking for which tables to use, from within Dynamics GP, go to Microsoft Dynamics GP menu --> Tools --> Resource Descriptions. You can click on the ellipse (three dots) beside "Table:" and this will take you to a listing. Choose the series you want. I then "view by" Table Physical Name and then use the Display name to give me clues where to look. As a user and consultant of GP for more than 11 years - I still constantly refer to this listing.

Cheers

Heather

Was this reply helpful? Yes No
Verified answer

Selva D 65 on at

Like (0)

Report

Thanks Heather and All. If Items is setup with extended pricing then this is the query to find out Unit Price value.

"SELECT CAST(PSITMVAL AS FLOAT) FROM IV10402 WHERE ITEMNMBR =@ItemNumber AND PRCSHID = @Country";

Was this reply helpful? Yes No