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