web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Issue: create a relati...
Finance | Project Operations, Human Resources, ...
Suggested Answer

Issue: create a relationship between the table (IV30400) and the view (InventoryTransactions).

(1) ShareShare
ReportReport
Posted on by Mohammed Rafe 56
Hi 
Please help me create a relationship between the table (IV30400) and the view (InventoryTransactions).
 
IV30400 : Serial and Lot Number History (original table Dynamic GP)
InventoryTransactions :  Inventory Transactions (original view Dynamic GP)
 
The Issue I have is that I cannot retrieve the opening balance accurately based on the lot number.
 
Is there a correct relationship between (IV30400) and (InventoryTransactions ) so that the results are correct?
 
For those with experience please help
-----------------------------------------------------------------
I've used these but have had issues with them :

 SELECT   it.[item number] ,
          it.[TRX Location] ,
         isnull( sum(lt.[SERLTQTY]) , 0 )AS Qty 
 FROM     InventoryTransactions it WITH (NOLOCK) 
                LEFT OUTER JOIN IV30400 lt WITH (NOLOCK) ON lt.DOCNUMBR = it.[Document Number] and lt.LNSEQNBR = it.[Line SEQ Number]
 WHERE    [Document Type] <> 'Transfer'
          AND  it.[item number] BETWEEN  @p_cSItemNumber AND @p_cEItemNumber
          AND  [Document Date]  < @p_dEDate
          AND (lt.SERLTNUM = @L_cLotNumber or rtrim(@L_cLotNumber) = '')   
 GROUP BY it.[item number] ,it.[TRX Location]
Categories:
Microsoft Dynamics GP
v1.PNG
I have the same question (0)
  • Devon Southall Profile Picture
    Devon Southall 160 on at
    umm... yeah.  I, personally, wouldn't do this.  couple reasons:
     
    1 - those MS views will include work and void transactions - not just posted transactions  (also these views are pretty bloated)
     
    2 - GP is a module based system and not all inventory changes happen in inventory transactions.  because the IV30400 table ONLY contains serial/lot numbers in the inventory transactions (adjustments, variance, and transfers and Manf transactions and other creating these) and NOT other transactions changing inventory like Purchase orders receipts/ returns, sales orders, etc .  those transactions have other serial/lot tables.
     
    FYI- table IV30300 does contain ALL these inventory changes if keep history is turned on and there aren't any posting issues EVER. but it doesn't contain the serial/lot numbers.  (AND you can't just add it up - you need to take site transfers in into account with some calculated fields...  oh and don't forget the QtyType field.  phew!)  
     
    What - exactly - are you trying to get??
     
    Best,
    Devon
  • Mohammed Rafe Profile Picture
    Mohammed Rafe 56 on at

    Hi Devon,
    First of all, thank you so much for your detailed response!

    What I am specifically trying to achieve is a query that returns the quantity of an item based on the Lot Number.

    Currently, I am using the following query to get the opening balance for a specific item, but based on the Lot Number:

    SELECT   it.[item number], it.[TRX Location], ISNULL(SUM(lt2.[SERLTQTY]), 0) AS Qty
    FROM     InventoryTransactions it WITH (NOLOCK)
             LEFT OUTER JOIN ReqIVLineLotDetailView lt2 WITH (NOLOCK)
             ON lt2.IVDOCNBR = it.[Document Number] AND lt2.LNSEQNBR = it.[Line SEQ Number]             AND it.[Item Number] = lt2.ITEMNMBR
    WHERE    [Document Type] <> 'Transfer'
             AND it.[item number] BETWEEN '03068' AND '03068'
             AND [Document Date] < '01-01-2022'
             AND lt2.SERLTNUM = @L_cLotNumber
    GROUP BY it.[item number], it.[TRX Location]

    Let me explain this query:

    • InventoryTransactions is an original view from Dynamics GP.
    • ReqIVLineLotDetailView is a view I created, which contains the following data:

    SELECT 1 AS "TrxState", IVDOCNBR, SERLTNUM, SERLTQTY, FROMBIN, TOBIN, LNSEQNBR, SLTSQNUM,         MFGDATE, EXPNDATE, IVDOCTYP, DATERECD, IV10002.ITEMNMBR
    FROM   IV10002 WITH (NOLOCK)
           JOIN IV00101 WITH (NOLOCK) ON IV00101.ITEMNMBR = IV10002.ITEMNMBR
           AND IV00101.ITMTRKOP = 3
    UNION ALL
    SELECT 3 AS TrxState, DOCNUMBR AS IVDOCNBR, SERLTNUM, SERLTQTY, TOBIN, LNSEQNBR, SLTSQNUM,
           MFGDATE, EXPNDATE, IVDOCTYP, '' AS DATERECD, IV30400.ITEMNMBR
    FROM   IV30400 WITH (NOLOCK)
    JOIN   IV00101 WITH (NOLOCK) ON IV00101.ITEMNMBR = IV30400.ITEMNMBR
           AND IV00101.ITMTRKOP = 3

    The issue I am facing is that the query doesn't return the correct QTY based on the Lot Number.

    What I need is a query that can accurately retrieve the quantity for the specific Lot Number. It's not a requirement to use the views mentioned above, so I am open to suggestions for alternative approaches.

    Your help and support are greatly appreciated.

    Best regards

  • Suggested answer
    Devon Southall Profile Picture
    Devon Southall 160 on at
    OK - so you are trying to get the qty in inventory right now for each lot number?   
     
    You want SQL table IV00300,  QTYTYPE=1 is on hand inventory.  Lot numbers leave this table when they are no longer in inventory.  QTYRECVD minus QTYSOLD is the qty in inventory.    That should give you what you have below in your query if you sum.
     
    This might have multiple records per lot number since it is tied to the receipt (layers) in table IV10200 using the fields ITEMNMBR, LOCNCODE, DATERECD, and RCTSEQNM.   if you want additional data table IV10200 can be helpful.
     
    If there are any discrepancies, that means there is a data problem.  Inventory reconcile was designed to fix those issues.
     
    Hope this helps!
    Devon 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 700 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 567 Super User 2025 Season 2

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 408 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans