SOC 1 Type 11 Reporting

Blog

Need to know who entered a payables invoice in Dynamics GP?

Posted by Hope Ritson

Mar 4, 2019

If you have ever needed to supply documentation showing who entered a payables invoice in Dynamics GP, you know the frustration that the data in the open and history payables transactions tables updates this information to be the user who issued or applied a payment to the invoice.  You can join to the payables keys master table to obtain this information with the script below. 

  

select a.VCHRNMBR as VoucherNumber, a.VENDORID as VendorID, case a.DOCTYPE

 

     when 1 then 'Invoice'

 

     when 2 then 'Finance Charge'

 

     when 3 then 'Misc Charge'

 

     when 4 then 'Return'

 

     when 5 then 'Credit Memo'

 

     when 6 then 'Payment'

 

     end Document_Type,

 

a.DOCDATE as DocumentDate, a.DOCNUMBR as DocumentNumber, a.BACHNUMB as BatchNumber,

 

        case a.VOIDED

 

     when 0 then 'No'

 

     when 1 then 'Yes'

 

     end Voided,

 

a.POSTEDDT as DatePosted, a.PTDUSRID as PostedUser, a.MODIFDT as DateModified, a.MDFUSRID as ModifiedUser,

 

a.DOCAMNT as DocumentAmount,

 

a.PSTGDATE as GLPostingDate, z.USERID as OriginalUser

 

from PM20000 as a

 

join PM00400 as z

 

       on a.VCHRNMBR = z.CNTRLNUM

 

              and a.DOCTYPE = z.DOCTYPE

 

where a.PSTGDATE between '1/01/2018' and '12/30/2018'

 

and a.DOCTYPE <> 6

 

 

 

union

 

 

 

select a.VCHRNMBR as VoucherNumber, a.VENDORID as VendorID, case a.DOCTYPE

 

     when 1 then 'Invoice'

 

     when 2 then 'Finance Charge'

 

     when 3 then 'Misc Charge'

 

     when 4 then 'Return'

 

     when 5 then 'Credit Memo'

 

     when 6 then 'Payment'

 

     end Document_Type,

 

a.DOCDATE as DocumentDate, a.DOCNUMBR as DocumentNumber, a.BACHNUMB as BatchNumber,

 

        case a.VOIDED

 

     when 0 then 'No'

 

     when 1 then 'Yes'

 

     end Voided,

 

a.POSTEDDT as DatePosted, a.PTDUSRID as PostedUser, a.MODIFDT as DateModified, a.MDFUSRID as ModifiedUser,

 

a.DOCAMNT as DocumentAmount, a.PSTGDATE as GLPostingDate, z.USERID as OriginalUser

 

from PM30200 as a

 

join PM00400 as z

 

       on a.VCHRNMBR = z.CNTRLNUM

 

              and a.DOCTYPE = z.DOCTYPE

 

where a.PSTGDATE between '1/01/2018' and '12/30/2018'

 

and a.DOCTYPE <> 6


Topics: cloud ERP, financial accounting, Microsoft Dynamics GP, business software