You’re offline. This is a read only version of the page.
202
Hi,
If I run the smartlist 'Inventory Transactions' in Dynamics GP and I run the SQL view 'InventoryTransactions' under the db views, should I have the same result ?
In Fabrikam, it's the same result, for my customer it is not the same !
*This post is locked for comments
Have you matched the smart list with the IV30300 table as it was initially suggested ?
Apologies for the encountered delay as I have been testing few scenarios and came up with an interesting finding.
I have dropped the "Inventory Transaction" view from the SQL level and the data is still being retrieved by the Inventory Transaction Smartlist. Following up on the SQL profiler, I have found that the process goes as follows:
The script below is run to retrieve all the posted and unposted inventory transactions primarily from the following tables:
data retrieved include document number, document type and line sequence number (the example below shows the records for item 100XLG in Fabrikam)
Share
Report
All responses (
Answers (
SELECT 1 AS '1' ,
TWO.dbo.IV10001.IVDOCTYP AS '2' ,
TWO.dbo.IV10001.IVDOCNBR AS '3' ,
TWO.dbo.IV10001.LNSEQNBR AS '4'
FROM TWO.dbo.IV10001 (NOLOCK)
LEFT OUTER JOIN TWO.dbo.IV00101 (NOLOCK) ON TWO.dbo.IV10001.ITEMNMBR = TWO.dbo.IV00101.ITEMNMBR
LEFT OUTER JOIN TWO.dbo.IV10000 (NOLOCK) ON TWO.dbo.IV10001.IVDOCTYP = TWO.dbo.IV10000.IVDOCTYP
AND TWO.dbo.IV10001.IVDOCNBR = TWO.dbo.IV10000.IVDOCNBR
LEFT OUTER JOIN TWO.dbo.IV40201 (NOLOCK) ON TWO.dbo.IV00101.UOMSCHDL = TWO.dbo.IV40201.UOMSCHDL
WHERE ( (UPPER(ISNULL(TWO.dbo.IV10001.ITEMNMBR, '')) = '100XLG') )
UNION ALL
SELECT 3 AS '1' ,
TWO.dbo.IV30300.DOCTYPE AS '2' ,
TWO.dbo.IV30300.DOCNUMBR AS '3' ,
TWO.dbo.IV30300.LNSEQNBR AS '4'
FROM TWO.dbo.IV30300 (NOLOCK)
LEFT OUTER JOIN TWO.dbo.IV00101 (NOLOCK) ON TWO.dbo.IV30300.ITEMNMBR = TWO.dbo.IV00101.ITEMNMBR
LEFT OUTER JOIN TWO.dbo.IV30200 (NOLOCK) ON TWO.dbo.IV30300.DOCTYPE = TWO.dbo.IV30200.IVDOCTYP
AND TWO.dbo.IV30300.DOCNUMBR = TWO.dbo.IV30200.DOCNUMBR
LEFT OUTER JOIN TWO.dbo.IV40201 (NOLOCK) ON TWO.dbo.IV00101.UOMSCHDL = TWO.dbo.IV40201.UOMSCHDL
WHERE ( (UPPER(ISNULL(TWO.dbo.IV30300.ITEMNMBR, '')) = '100XLG') )
ORDER BY '1' ASC ,
'2' ASC ,
'3' ASC ,
'4' ASC
Then, for each of the records above, data is being retrieved through several stored procedures to retrieve document details from from IV30300 and IV10001.
Interesting finding , isn't it !
Now, most importantly, how to get this solved out.
You need to match the data being retrieved with the data stored in IV30300 and IV10001. Both of them should make up the Inventory Transaction Smartlist as a whole.
Furthermore, can you elaborate on the reported problem from your client. Would it be just a decimal places ? Or missing records ?
Your feedback is highly appreciated,
Yes, it definitely should be the same. This view primarily retrieves data from IV30300, you may consider that for further assurance.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156