Inventory SQL Views


Dynamics GP Inventory SQL Views

34 Responses to “Inventory SQL Views”

  1. 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

    Like

    • 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

      Like

  2. 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?

    Like

    • 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

      Like

  3. 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

    Like

    • 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

      Like

  4. 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

    Like

    • 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

      Like

  5. clicking on the first two links (Customer Pricing & Inventory Price Levels ) both go to the same page with the same view. Is this correct?

    Like

  6. 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?

    Like

    • 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

      Like

  7. 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

    Like

  8. 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

    Like

    • 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

      Like

  9. 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.

    Like

  10. 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

    Like

  11. 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 ?

    Like

  12. 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 .

    Like

    • 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

      Like

  13. 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 ?

    Like

    • 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

      Like

Trackbacks/Pingbacks

  1. SQL view for rolling 12 months of sales by item in Dynamics GP - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - August 27, 2012

    [...] Inventory SQL views [...]

    Like

  2. SQL view for rolling 12 months of sales by item in Dynamics GP | Victoria Yudin - August 27, 2012

    [...] Inventory SQL Views [...]

    Like

  3. SQL view for sales quantities by customer by item by year - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - March 9, 2012

    [...] can find more code like this on my SOP SQL Views and Inventory SQL Views pages. For additional GP reporting information and links, check out my GP Reports [...]

    Like

  4. SQL view for sales quantities by customer by item by year | Victoria Yudin - March 9, 2012

    [...] Inventory SQL Views [...]

    Like

  5. Dynamics GP inventory by location with dates - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - January 29, 2011

    [...] can see more Dynamics GP Inventory scripts here. Or visit my GP Reports page for links to additional GP scripts and other reporting information [...]

    Like

  6. Dynamics GP inventory by location with dates « Victoria Yudin - January 29, 2011

    [...] Inventory SQL Views [...]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,583 other followers

%d bloggers like this: