SQL stored procedure for all open AR transactions by customer name


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

open AR

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.

5 Responses to “SQL stored procedure for all open AR transactions by customer name”

  1. 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!

    Like

Trackbacks/Pingbacks

  1. SQL stored procedure for all open AR transactions by customer name – 11/24, Victoria Yudin | - December 2, 2014

    […] Continue reading on Source Blog […]

    Like

  2. Create a refreshable Excel report based on a SQL Server stored procedure | Victoria Yudin - November 24, 2014

    […] Create a SQL stored procedure. Even though you might already have a stored procedure in place, you will typically need to modify it, at least a little, for use with Excel reports. I recommend making sure that your results are in the exact order you want to see them in Excel and that your field names are all in the format you will want to see in Excel (ie, with spaces). To have something to use as an example, this morning I posted code for a SQL stored procedure showing all unapplied AR transactions with a parameter that will use ‘…. […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: