Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

Wednesday, December 18, 2013

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

11

 

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
    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

4 comments:

  1. useful information , but if the post is to GL not through GL , how can i get the posted user ID . when i add this column in smart-list- inventory transaction , it just empty column

    ReplyDelete
    Replies
    1. Hello Mahmoud
      The script is precisely designed for inventory transactions that are being posted through GL. In case your posting setup is "Post To", you will need to apply other audit methods to keep track of such piece of information.

      Best Regards,
      Mahmoud M. AlSaadi

      Delete
  2. Thank you so much for your reply , do you mean by audit post tracing from setup ???

    ReplyDelete
    Replies
    1. When talking about Audit for Dynamics GP, there are definitely multiple valid options to be considered. If this is the only that that requires audit log, then I would consider SQL server solution.
      Here is my preferred option; SQL Server CDC
      http://mahmoudsaadi.blogspot.com/2014/10/audit-requirements-for-dynamics-gp.html

      Best Regards,

      Delete