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

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

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


No Comments

Post a Comment

Comment
Name
Email
Website