A little variation on my Unapplied AR Transactions view, this SQL stored procedure will return all open (unapplied) receivables transactions for customers with a name containing what is supplied as the parameter.
In case you’re wondering why I am all of a sudden posting a stored procedure, I am going to follow this up later today with a blog post on how to use this to create a refreshable Excel report and (optionally) set it up in GP Reports Viewer.
Sample output (please click on the image to see it bigger):
Additional resources:
create procedure sp_open_AR_per_customer_name @customer char(50) as -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ -- sp_open_AR_per_customer_name -- Created Nov 24, 2014 by Victoria Yudin, Flexible Solutions Inc -- For updates see https://victoriayudin.com/gp-reports/ -- Shows all unapplied Receivables transactions in Functional -- Currency only -- Will return any customer with a name containing the -- parameter supplied -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ set nocount on select cm.CUSTNMBR [Customer ID], cm.CUSTNAME [Customer Name], cm.PYMTRMID [Customer Terms], cm.CUSTCLAS [Customer Class], case rm.RMDTYPAL when 1 then 'Sale / Invoice' when 3 then 'Debit Memo' when 4 then 'Finance Charge' when 5 then 'Service Repair' when 6 then 'Warranty' when 7 then 'Credit Memo' when 8 then 'Return' when 9 then 'Payment' else 'Other' end [Document Type], rm.DOCNUMBR [Document Number], rm.DOCDATE [Document Date], rm.DUEDATE [Due Date], rm.ORTRXAMT [Document Amount], rm.CURTRXAM [Unapplied Amount], case when rm.DUEDATE >= getdate() then 0 when rm.RMDTYPAL in (7,8,9) then 0 else datediff(dd, rm.DUEDATE, getdate()) end [Days Past Due], cs.LASTPYDT [Last Payment Date], cs.LPYMTAMT [Last Payment Amount] from RM20101 rm inner join RM00101 cm on rm.CUSTNMBR = cm.CUSTNMBR inner join RM00103 cs on rm.CUSTNMBR = cs.CUSTNMBR where cm.CUSTNAME like '%'+rtrim(@customer)+'%' and rm.VOIDSTTS = 0 and rm.CURTRXAM <> 0 set nocount off go grant exec on sp_open_AR_per_customer_name to DYNGRP
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
i have this report can i add comments from the invoices i think the field is from SOP10106
SELECT CM.CUSTNMBR Customer_ID,
CM.CUSTNAME Customer_Name,
CM.SLPRSNID Salesmerson_ID,
CM.PYMTRMID Customer_Terms,
CM.CUSTCLAS Customer_Class,
CASE RM.RMDTYPAL
WHEN 1 THEN ‘Sale / Invoice’
WHEN 3 THEN ‘Debit Memo’
WHEN 4 THEN ‘Finance Charge’
WHEN 5 THEN ‘Service Repair’
WHEN 6 THEN ‘Warranty’
WHEN 7 THEN ‘Credit Memo’
WHEN 8 THEN ‘Return’
WHEN 9 THEN ‘Payment’
ELSE ‘Other’
END Document_Type,
RM.DOCNUMBR Document_Number,
RM.CSPORNBR Customer_PO_Number,
RM.DOCDATE Document_Date,
RM.DUEDATE Due_Date,
RM.ORTRXAMT Document_Amount,
RM.CURTRXAM Unapplied_Amount,
CASE
WHEN RM.DUEDATE >= GETDATE() THEN 0
WHEN RM.RMDTYPAL in (7,8,9) THEN 0
ELSE DATEDIFF(DD, RM.DUEDATE, GETDATE())
END Days_Past_Due,
CS.LASTPYDT Last_Payment_Date,
CS.LPYMTAMT Last_Payment_Amount
FROM RM20101 RM
INNER JOIN RM00101 CM
ON RM.CUSTNMBR = CM.CUSTNMBR
INNER JOIN RM00103 CS
ON RM.CUSTNMBR = CS.CUSTNMBR
WHERE RM.VOIDSTTS = 0 AND RM.CURTRXAM <> 0
LikeLike
Hi Shulim,
There are a lot of different comments – can you please let me know where they are exactly? (What window are they on, how do you get to them?)
-Victoria
LikeLike
Hi Victoria,
I need to know is who apply (username) A receipt in receivable module, as well as who the unapplied, i need table know that I can get that information.
Thank Very Much!
LikeLike
Unfortunately Dynamics GP does not store this kind of information. If you need this, you will have to set up something custom to track it or possibly buy a 3rd party product that does this.
-Victoria
LikeLiked by 1 person
Thanks very much!!
LikeLike