Inventory Transaction - User Who Posted !

Mostly in all Dynamics GP Modules, the information of “Who Posted This Transaction” is always at the tips of our fingers. Although, this piece of details is not available when digging in inventory tables.  Does it mean that I can not know who posted an inventory transactions ? The answer is simply, You can.

When the posting setup is configured to Post Through GL, the transaction automatically generates associated Journal Entries. In this essence, we can still get this information from GL Table, because the user who posted the IV transaction is the same who generated the GL journal entry.

The SQL Script below retrieves the following details for every single inventory transactions;

• Source Document
• IV Document Source
• IV Document Number
• Journal Entry
• Transaction Year
• Open/Closed Year
• Last User (Last user who edited the transaction)
• Last Edit Date
• User Who Posted (The user who actually posted the IV transaction )
• Posting Date

In order to retrieve the following details for a certain document number/s, include them in the “Where” condition in the following SQL Statement.

--- Tables Included:

• GL20000: Year to Date Transaction Open
• GL30000: Account Transaction History
• SEE30303: Historical Inventory Trial Balance

SELECT * FROM
(
    SELECT  DISTINCT
            SOURCDOC AS SourceDocument,
            ORGNTSRC AS IVDocumentSource,
            ORCTRNUM AS IVDocumentNumber,
            JRNENTRY AS JournalEntry,
            OPENYEAR AS TransactionYear,
           'Opean Year' AS 'Open/Closed Year',
            LASTUSER AS LastUser,
            LSTDTEDT AS LastEditDate, 
            USWHPSTD AS UserWhoPosted,
            ORPSTDDT AS PostingDate
    FROM GL20000
    UNION ALL
    SELECT  DISTINCT
            SOURCDOC,
            ORGNTSRC,
            ORCTRNUM,
            JRNENTRY,
            HSTYEAR,
            'History Year',
            LASTUSER,
            LSTDTEDT, 
            USWHPSTD,
            ORPSTDDT           
    FROM GL30000
)   AS A 
    WHERE A.JournalEntry IN
    (
     SELECT DISTINCT JRNENTRY
    FROM SEE30303
    WHERE DOCNUMBR = '#IVDocumentNumber'
    )

Helping Note !

The script considers the SEE30303 table. In case the HITB is not populated, you need to change the “WHERE Condition” in the script above to retrieve the IV document number from GL tables as follows;

WHERE A.IVDocumentNumber = '#IVDocumentNumber'

Best Regards,
Mahmoud M. AlSaadi