Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
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 31
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
  • Suggested answer
    Devon Southall Profile Picture
    Devon Southall 89 on at
    Issue: create a relationship between the table (IV30400) and the view (InventoryTransactions).
    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 
  • Mohammed Rafe Profile Picture
    Mohammed Rafe 31 on at
    Issue: create a relationship between the table (IV30400) and the view (InventoryTransactions).

    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

  • Devon Southall Profile Picture
    Devon Southall 89 on at
    Issue: create a relationship between the table (IV30400) and the view (InventoryTransactions).
    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

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

News and Announcements

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,219 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans