Here’s a very simple query that lists all items having a distribution history in Dynamics GP 2013:

SELECT DISTINCT ITEMNMBR FROM IV30500 ORDER BY ITEMNMBR;

Which looks like this:

Sample showing 1 column

You can get more sophisticated and show which type(s) of distributions each item has like this:

select
  itemnmbr,
  case ivdoctyp
    when 1 then 'Inventory Adjustment'
    when 2 then 'Variance'
    when 3 then 'Inventory Transfer'
    when 4 then 'Purchase Receipt'
    when 5 then 'Sales Return'
    when 6 then 'Sales Invoice'
    when 7 then 'Assembly'
    when 8 then 'Inventory Cost Adjustment from POP Shipment (GLTRX)'
    when 11 then 'Inventory Cost Adjustment from POP Invoice (POIVC)'
    else 'Document Type ' + cast(ivdoctyp as varchar(12))
  end as InventoryDocumentType,
  count(*) as HowManyEntries
from iv30500
group by itemnmbr,ivdoctyp
order by itemnmbr,ivdoctyp
;

Which looks like this:

Sample showing 3 columns

The document types are taken from one of Victoria Yudin’s fantastic web pages, http://victoriayudin.com/gp-tables/inventory-tables/

Maybe this will save someone a few minutes of searching.

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available