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

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Smartlist Inventory Tr...
Microsoft Dynamics GP (Archived)

Smartlist Inventory Transactions

(0) ShareShare
ReportReport
Posted on by MSL 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

I have the same question (0)
  • Verified answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at

    Yes, it definitely should be the same. This view primarily retrieves data from IV30300, you may consider that for further assurance.

  • MSL Profile Picture
    MSL 202 on at

    What should I do, any check link or functionality to fix a problem like that ?

  • Verified answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at

    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:

    • IV100001
    • IV10000
    • IV30300 (which I previously mentioned)

    data retrieved include document number, document type and line sequence number (the example below shows the records for item 100XLG in Fabrikam)

    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,

  • MSL Profile Picture
    MSL 202 on at

    Hi,

    It works fine and I also add links to the 'Inventory lot number Work/History tables' in an SQL view and access it from Excel.

    But for this customer, it seems that he has some data corruption or empty values !

    Thanks,

    MSL

  • Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at

    Have you matched the smart list with the IV30300 table as it was initially suggested ?

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans