Dynamics GP Inventory SQL Views
- Customer Pricing Customer specific pricing for each item.
- Inventory Price Levels All price levels for all items.
- Inventory with Dates Current quantities and the last sales and purchase dates along with the vendor for each item.
- Inventory with Dates by Site Current quantities and the last sales and purchase dates along with the vendor for each item – by site ID.
- Quantities on Hand Current quantities on hand with total and average costs.
- Sales Quantities by Customer by Item by Year Total sales quantities (invoices minus returns) by customer by item by year, with an overall total column.
- Sales Quantities by Item by Year Total sales quantities (invoices minus returns) by item by year, with an overall total column.
Victoria Yudin 
Victoria,
I am trying to reconcile my perpetual inventory to the GL. Everything that I have searched references using the “Reconcile Tool” in the financials or SEE30300 in SQL… BUT we have not implemented that feature (inventory portion) here, and cannot without significant disruption.
I have been trying to SQL from IV30200 thru IV30300 thru to GL20000 looking at the posted activity for a IV30200 GLPOSTDT date range (current month in the current year).
Any SQL suggestions on how to manually create a “Inventory Recon tool”?
Thanks,
JR
LikeLike
Hi John,
I would not recommend doing this yourself. It’s extremely difficult. Instead, I would recommend looking at The Closer: https://reporting-central.com/the-closer/.
-Victoria
LikeLike
HI Victoria,
Do you have any report which displays line item purchased on particular month by particular account number
LikeLike
Hi Shekar,
No, I don’t have anything like this. Have not seen this anywhere else, either. Sorry.
-Victoria
LikeLiked by 1 person
something like line item purchased on particular month ?
LikeLike
You would have to create this as a customer report.
-Victoria
LikeLiked by 1 person
Victoria, of the Views shown, is there one that best shows the Stock Status Report? We have a customer that can never tie the Stock Status Report in GP to the GL, and I thought maybe something would pop out from a SQL view.
LikeLike
Hi John,
Probably the best would be this one, showing the current inventory.
-Victoria
LikeLike
John,
you should never try to tie the Stock Status to the GL….This report uses the Current Cost from the Item Master. To tie to the GP use the Purchase Receitps report – or Smartlist from that Table….it holds actual cost and should tie to the GL.
LikeLike
Victoria, I am looking for a view that shows Inventory Items that have been paid for. We have an inventory evaluation that wants to see our TOP COGS Vendors with Items listed. I have got as far as looking in the PurchaseLineItems view but it does not show me the items that have been released are paid and when they got paid. Do you have any view that would show this?
Thanks for your help, I always find your site helpful.
Daryle
LikeLike
Hi Daryle,
There might be some payables views that could help: What Check Paid a Purchase Order and Payables invoices originating from POP.
-Victoria
LikeLike
Thank you. I like both of these. I think the Payables Invoices originating from POP will work, but is there a way to have the Paid date added to that view? Since I am looking for what we paid vendors.
LikeLike
Hi Daryle,
I have updated the code to add the date the invoice was paid.
-Victoria
LikeLike
Thanks. That was easy enough. I guess I missed that field earlier. Thanks for your help. Can’t tell you how many times I keep using your site.
LikeLike
I have more detail. They are really looking for a Stock Status Report exactly like you have shown with the following parameters:
Being able to select a cut-off date (i.e. 12/31/2016)
Include Negative Inventory Numbers
Exclude Service Items (Inventory Type = Service)
Is there some way to get to this “number” – Inventory Balance at YE – and exclude the Service Items?
LikeLike
Hi John,
At the heart of what you’re asking for is a historical inventory trial balance report. I do not have that in SQL. Sorry not to be able to help.
-Victoria
LikeLike
Hi Victoria
I was wondering if there was a report to view the default vendor assigned to each item? If not what table has that information in it?
Thanks
Vic
LikeLike
Vic,
I believe you can add a Primary Vendor column to the Item Quantities Smartlist to see this information.
-Victoria
LikeLike
Hi Victoria,
As always thank you for providing this wonderful resource.
Any chance you can help me with the following view I am trying to create? I am really struggling with the getting the data for the two unit of measures to the correct type in line two and would appreciate any advice.
Create view [dbo].[WebItemTracking3] as select T2.QTYONHND -T2.ATYALLOC as ‘Quantity Available’,
(T2.QTYONHND -T2.ATYALLOC)*(T5.[BASEUOFM] / T4.[UOFM]) as ‘Saleable Units’,
T1.[ITEMNMBR] as ‘Item Number’,
T1.[ITEMDESC] as ‘Item Description’,
case T1.[ITEMTYPE] when 1 then ‘Sales Inventory’ when 2 then ‘Discontinued’ when 3 then ‘Kit’ when 4 then ‘Misc Charges’ when 5 then ‘Services’ when 6 then ‘Flat Fee’ else ” end as ‘Item Type’,
T2.[QTYONORD] as ‘QTY On Order’,
T2.[ATYALLOC] as ‘QTY Allocated’,
isnull(T3.[Company1], ”) as ‘Company1’,
isnull(T3.[Company2], ”) as ‘Company2’,
isnull(T3.[Company3], ”) as ‘Company3’,
isnull(T3.[Exclude – Company1], ”) as ‘Exclude – Company1’,
isnull(T3.[Exclude – Company2], ”) as ‘Exclude – Company2’,
isnull(T3.[Exclude – Company3], ”) as ‘Exclude – Company3’,
isnull(T3.[Exlude from report], ”) as ‘Exlude from report’,
isnull(T3.[Out of Stock], ”) as ‘Out of Stock’,
isnull(T4.[UOFM], ”) as ‘U Of M’,
T4.[QTYBSUOM] as ‘QTY In Base U Of M’,
isnull(T4.[PRCLEVEL], ”) as ‘PriceLevel’,
T5.[BASEUOFM] as ‘Base U Of M’ from ~DBNAME..IV00101 T1 with (nolock)
LEFT JOIN ~DBNAME..IV00102 T2 with (nolock)
on
T2.[ITEMNMBR] = T1.[ITEMNMBR]
LEFT JOIN [~DBNAME].dbo.[JR_EXT_INTERNET] T3
on
T3.[Item Number] = T1.[ITEMNMBR]
LEFT JOIN ~DBNAME..IV00108 T4 with (nolock)
on
T4.[ITEMNMBR] = T1.[ITEMNMBR]
INNER JOIN ~DBNAME..IV40201 T5 with (nolock)
on
T5.[UOMSCHDL] = T1.[UOMSCHDL] where T2.[RCRDTYPE] = ‘1’
and T1.[ITEMNMBR] > ‘1000’
and isnull(T4.[PRCLEVEL], ”) IN (‘Company1PriceLevel’, ‘Company2PriceLevel’, ‘Company3PriceLevel’, ‘Company4PriceLevel’)
Best regards,
Kirk
LikeLike
Hi Kirk,
Your code looks like it’s trying to divide two strings…IV40201.BASEUOFM and IV00108.UOFM. That will not work, you can only use numbers for math operations. I guess I am not clear why you’re trying to divide those? The quantities in the IV00102 table are always stored in the Base UofM…are you trying to transform these into something else? If you can help me understand exactly what you’re trying to accomplish, maybe I can help more.
-Victoria
LikeLike
Victoria,
Do you have any Purchasing SQLs for inventory cost analysis purposes? Like a “Purchasing Quantities with Costs by Item by Vendor by Year” or anything Vendor related besides the “Last Vendor” info?
LikeLike
Hi Ryan,
Thanks for your inquiry. I don’t have anything ready to publish like what you are asking for. If you are interested in having something like this created as a consulting project, please let me know.
Thanks,
-Victoria
LikeLike
Hi Victoria,
I’m trying to create a report / notification to a salesperson at the following occurrence:
Document Type: Order, QTY on backorder linked to a P/O, a receiving occurs and the QTYBO moves to QTYFULFILLED.
Right now, a B/O report prints at posting but sometimes that person forgets to circulate the report.
My goal is for the salesperson who entered the order to receive an SQL alert that the item has been received in and can be shipped to the customer.
If this isn’t possible, how about an SSRS report that shows “today” these SOP lines linked to a POP have been received in (which would be sent to all salespeople/order entry personnel) ?
Thanks for your help !
Theo
LikeLike
Hi Theo,
I am not sure about automating this, it might have to be done with a SQL trigger. But you can certainly create an SSRS report for this. If the receipts are always posted right away (they should be, to update inventory), you can use the POP30300 and POP30310 tables for the receipts, the SOP60100 table for the SOP – POP links and the SOP tables needed to show the detail you want from the sales orders.
-Victoria
LikeLike
Hi Victoria,
I am looking to create a view which shows the date we are receiving an item along with the quantity. We could have 20 purchase orders for a given item but I would like to see the PO #, the expected arrival date and the quantity of the one we will get first. Is that possible?
Thanks,
David
LikeLike
Hi David,
Assuming the information about when inventory will be received is stored consistently on each PO, you should be able to get all of this information from the POP10100 and POP10110 tables. If you need more help, let me know exactly where your users are entering the ‘expected arrival date’ and I will put something together for you.
-Victoria
LikeLike
clicking on the first two links (Customer Pricing & Inventory Price Levels ) both go to the same page with the same view. Is this correct?
LikeLike
Jimmy,
Thanks for the heads up, I have fixed the links. The first one should have been going somewhere else.
-Victoria
LikeLike
I need to create a custom report to show the inventory transacations (sales, adjustments, variences, transfers, etc.) for all items for a certain time period. The catch is it has to include item lot information. By default the views and smartlists that come with GP do not provide that information. Can you provide any guidance creating a view to capture the information I need?
LikeLike
Jennifer,
I have not done a report like this and it may depend on all the different modules you are using…but you could try starting with the IV30300 table to get all the inventory transactions, then link to all the individual modules needed to get the lot numbers from there. So you would basically have a separate section of code for the lot information for each module (as needed).
I am not aware of any easier way to do this, but maybe if you post your question on the GP Community Forum someone else will have some other ideas for you.
-Victoria
LikeLike
Hi Victoria,
I am trying to do something fairly similar, but I am a novice with GP (I do have plenty of SQL and various data experience though).
Could you help me understand what you mean by “individual modules”? How does that translate into SQL tables, etc.?
Thanks so much!
Ryan
LikeLike
Hi Ryan,
Inventory transactions can come from many different sources – sales, purchasing, assembly. etc. So if you need to link back to the original transaction detail there may be multiple places to link back to. Each of those sources will have its own set of tables and they will not always follow the same pattern/numbering. A lot of reporting on GP data really comes down to first understanding exactly how/where data is being entered.
-Victoria
LikeLike
Hello Victoria
I have a query, I need to get a report that will show me inventory balances, ie the quantity on hand and the actual cost. Is there MICROSOFT DYNAMICS GP that information or otherwise calculated according to the movements that are given in Table IV30300? If there is another table where it can not extract that information would be great.
Greetings Poless
LikeLike
Hi Poless,
My inventory quantities view (https://victoriayudin.com/2009/06/18/sql-view-for-inventory-quantities-on-hand/) may help if you’re looking for current quantities and costs.
-Victoria
LikeLike
Victoria,
I tired using this SQL view. Why when items are using the ‘% Margin – Standard Cost’ price method, does the price not reflect on the view? For example, I have an item with standard cost of $15.41 and a Percent of List (column PRCLEVEL) of 23% yet price = $0.00.
Also, I am desperately trying to create a view that will allow me to look at items as a price list – item numbers down the first column with price levels as headers. I have been running as a Smartlist exporting to excel then creating a pivot table – too much work. Any help is appreciated.
Mark
LikeLike
Mark,
I guess you are referring to the SQL view for Inventory Price Levels in Dynamics GP? If so, I coded this to either give you the price if it’s an amount or the percentage if it’s a %, but not both. If you need something more, you could take my code and add to it…at least you won’t have to start from scratch. 😉
For your other question, I think if I was to code this in SQL (and thus SmartList) I would do something similar to how I show coding totals for each year in a separate column in my SQL view for sales by item by year view. The negative to this approach is if you have a very large number of price levels or if they change often, it’s a lot of work to upkeep. However…if you wanted to use a reporting tool like Crystal Reports or SSRS for this, this kind of coding in SQL could be avoided. For example, you can create a cross-tab in Crystal to do this very easily, all you would need is 3 fields: item number, price level and price.
-Victoria
LikeLike
Victoria
We are running under FIFO Perpetual for costs, but I am not finding the costs in the IV00118 table for some of our inventory. I have inventory that should be triggering cost changes and I can’t find the actual cost. Where else should I be looking or am I missing something.
LikeLike
Hi Patrick,
I do not believe IV00118 is what you should be using. Maybe IV10200 would be better?
-Victoria
LikeLike
I take it there is not one table that hold the costs of both manufactured and purchased items that I can tap into?
Patrick
LikeLike
Hi Victoria,
Do you know of anyone who has replicated the Inventory historical Stock status report, the native GP report takes a while to run, any input on this very highly appreciated
Thanks,
Farooq
LikeLike
Farooq,
I am not aware of anyone who has done this.
-Victoria
LikeLike
How can we make it run faster, any suggestions
Farooq
LikeLike
Victoria,
Just a quick question. Can a view be retrieved in Crystal Reports. I never realised that but today when I am trying to see a view in CR it is giving me a error.
What am I doing wrong ?
LikeLike
Hi Zack,
Yes, a SQL view can be used in Crystal Reports just like a SQL table. What is the exact error that you are getting and how are you trying to see the view in Crystal?
-Victoria
LikeLike
Thanks Victoria for your reply , i want to inform you that SOP line items contains three line items but in the inventory the same transaction contain just two line items .this case is frequently that is means the SOP transactions dose not fully updated the inventory quantities .I checked the SOP and the inventory inquiries , the smart list and the SOP and Inventory tables.what can i do to resolve this mistake .
LikeLike
Mahmoud,
I can think of 4 possibilities for what you’re describing:
1) You’re using a non-inventory item in SOP. Even if an item was subsequently created in inventory, if the system did not recognize it as an inventory item at the time of the SOP transaction entry, this will not get updated. This can be easily seen by bringing in a column called Non IV on the Sales Line Item SmartList.
2) Inventory history was not being tracked for an item at the time the SOP transaction was posted. This can be very difficult to see if the history tracking was turned on later, as there is no tracking for this kind of change out-of-the-box.
3) Something went wrong during posting. Unless you can duplicate the issue and see where it is happening, this also could be extremely difficult to track. Typically, I would expect to see other issues or errors during posting if this was happening.
4) You have customizations or 3rd party add-ons that are causing the system not to behave as designed.
To dig deeper into this and look for a resolution you would most likely need to work with your GP Partner and/or GP Support.
-Victoria
LikeLike
hi Victoria
thanks for your excellent website
My problem is there is a difference between the quantities of line items in SOP transactions and Quantity in the inventory So i made i reconcile for the inventory and the sales modules but no changes . so is there a SQL script to show the items quantities in SOP and Inventory ?
LikeLike
Hi Mahmoud,
What are the quantities you are trying to match from Inventory to SOP? I think you should be able to see all of these using default SmartLists, but if you’re not comparing corresponding columns, the issue may be there.
Also, if the problem is on the SOP side, you may need to run a Sales Reconciliation (Tools > Utilities > Sales > Reconcile – Remove Sales Documents). There is an option there to simply print a report, without performing the actual reconciliation, so that you can see what changes would be made if you were to reconcile.
-Victoria
LikeLike