Inventory Transaction list

(0) Share

Report

Posted on by Community Member

Hi, I am looking to generate a list of transactions for the inventory items where it would reflect the item, quantity, doc type (adj or transfer or receipts), from / to location, site and bin number for all the transaction for a date range. Which tables should I use, do I need to include the history tables (what's the rule for history in Inventory), Please help

*This post is locked for comments

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Redbeard 12,931 on at

Like (1)

Report
Copy link

Link copied!

John,

Here is a query to get you started. Short answer, you're going to want to take history tables into account. Unposted inventory transactions are located in the IV10001 table, and when posted are written to the IV30300 table, so the bulk of your inventory transaction lines are going to be in the IV30300 table. The IV10003 table contains bin transaction information, so you're going to want to join this table with your transaction tables using left joins.

select * from IV10001 ITA

Left Join IV10003 ITB on ITA.IVDOCNBR = ITB.IVDOCNBR

and ITA.LNSEQNBR = ITB.LNSEQNBR

union all

select * from IV30300 ITH

Left Join IV10003 ITB on ITH.DOCNUMBR = ITB.IVDOCNBR

and ITH.LNSEQNBR = ITB.LNSEQNBR

Here is a link to Victoria Yudin's site, which provides very good information on table structures, possible values for various cryptic fields in GP and lists of what those possible values mean (i.e. Inventory Transaction Document types).

victoriayudin.com/.../inventory-tables

I also have a page dedicated to SQL queries and some case examples on how to convert numeric values to text when returning queries.

redbeardblogging.blogspot.com/.../sql.html

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thank you very much Harry. I really appreciate it. John

Was this reply helpful? Yes No
Thomas Newcomb 1,810 on at

Like (0)

Report
Copy link

Link copied!

Hi John!

I am just following up to see if this still an issue or if the information that Harry had posted has helped resolve your issue.

If the issue was resolved using Harry's information can you mark his answer as verified that way if anyone else has this type of question they can use the answer as well.

Please let us know!

~Thomas Newcomb

------------------------------------------

Microsoft Dynamics GP Support

------------------------------------------

This posting is provided "AS IS" with no warranties, and confers no rights

Was this reply helpful? Yes No