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
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
Thank you very much Harry. I really appreciate it. John
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.
#1
André Arnaud de Cal...
292,492
Super User 2025 Season 1
#2
Martin Dráb
231,305
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
