Finance | Project Operations, Human Resources, ...

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

(1) Share

Report

Posted on by Mohammed Rafe

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

All responses (3)

Answers (0)

Suggested answer

Devon Southall 95 on at

Like (1)

Report

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 31 on at

Like (0)

Report
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 95 on at

Like (1)

Report

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