Dynamics Community Forum Thread Details

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

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

Quick Links

Leaderboard