Here is a SQL view that will return all unapplied Receivables transactions in Dynamics GP. This will calculate how many days overdue unapplied transaction are. If something is not overdue, or if it is a credit transaction (payment, credit or return) the Days_Past_Due will be zero. For other SQL code, please visit my GP Reports page.

~~~~~

CREATE VIEW view_Unapplied_AR_Trx
AS

/****************************************************************
view_Unapplied_AR_Trx
Created Sep 05, 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
Shows all unapplied Receivables transactions
     in Functional Currency only
Tables used:
     CM - RM00101 - Customer Master
     CS - RM00103 – Customer Master…

This is a second version of the AR Aging script, it loops through the open RM transactions an updates the aging buckets

Note: This script (and every other script on this site) is provided 'as-is' and needs to be verified by a qualified accountant

 --Written by Steve Gray
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'FP_RMAgeTrialBalance' 
)
   DROP PROCEDURE dbo.FP_RMAgeTrialBalance
GO


CREATE PROCEDURE dbo.FP_RMAgeTrialBalance


create procedure FP_RMAgeTrialBalance 
-- FP_RMAgeTrialBalance '5/1/2010'


@ASOFDATE char(21) --Return that data as of this date


as
set nocount on


declare
    @AGINGBKLBL1 char(15),
    @AGINGBKLBL2 char(15),
    @AGINGBKLBL3 char(15),
    @AGINGBKLBL4 char(15),
    @AGINGBKLBL5 char(15),
    @AGINGBKLBL6 char(15),
    @AGINGBKLBL7 char(15),
    @AGINGBKDY1 int,
    @AGINGBKDY2 int,
    @AGINGBKDY3 int,
    @AGINGBKDY4 int,
    @AGINGBKDY5 int,
    @AGINGBKDY6 int,
    @AGINGBKDY7 int,
    @MAXDAYS int,
    @MAXINDX tinyint,
    @AGEBY tinyint,
    @AGEUNAPPLDCR tinyint,
    @AGEDATE char(12),
    @CDATE char(12),
    @C_CUSTNMBR char(15),
    @C_RMDTYPAL tinyint,
    @C_DTDIFF int,
    @C_DOCDATE datetime, 
    @C_DUEDATE datetime, 
    @C_CURTRXAM numeric(19,5), 
    @C_MY_DEX_ROW int


--set the params defaults
set @AGINGBKLBL1 = ''
set @AGINGBKLBL2 = ''
set @AGINGBKLBL3 = ''
set @AGINGBKLBL4 = ''
set @AGINGBKLBL5 = ''
set @AGINGBKLBL6 = ''
set @AGINGBKLBL7 = ''
set @AGINGBKDY1 = 0
set @AGINGBKDY2 = 0
set @AGINGBKDY3 = 0
set @AGINGBKDY4 = 0
set @AGINGBKDY5 = 0
set @AGINGBKDY6 = 0
set @AGINGBKDY7 = 0
set @CDATE = convert(char(12), getdate(), 101)


--set the aging bucket labels
select @AGINGBKLBL1 = RMPERDSC, @AGINGBKDY1 = RMPEREND from RM40201 where INDEX1 = 1
select @AGINGBKLBL2 = RMPERDSC, @AGINGBKDY2 = RMPEREND from RM40201 where INDEX1 = 2
select @AGINGBKLBL3 = RMPERDSC, @AGINGBKDY3 = RMPEREND from RM40201 where INDEX1 = 3
select @AGINGBKLBL4 = RMPERDSC, @AGINGBKDY4 = RMPEREND from RM40201 where INDEX1 = 4
select @AGINGBKLBL5 = RMPERDSC, @AGINGBKDY5 = RMPEREND from RM40201 where INDEX1 = 5
select @AGINGBKLBL6 = RMPERDSC, @AGINGBKDY6 = RMPEREND from RM40201 where INDEX1 = 6
select @AGINGBKLBL7 = RMPERDSC, @AGINGBKDY7 = RMPEREND from RM40201 where INDEX1 = 7
 
--Need to get the Max bucket so all transaction after last bucket falls into there.
select @MAXDAYS = max(RMPEREND) from RM40201
select @MAXINDX = INDEX1 from RM40201 where RMPEREND = @MAXDAYS
set @MAXINDX = @MAXINDX - 1
select @MAXDAYS = RMPEREND from RM40201 where INDEX1 = @MAXINDX
select @AGEBY = AGEBY, @AGEUNAPPLDCR = AGEUNAPPLDCR from RM40101


create table #RMOPENTRX (
    CUSTNMBR char(25) not null default '',
    DOCNUMBR char(21) not null default '',
    RMDTYPAL tinyint not null default 0,
    DOCABREV char(8) not null default '',
    DOCDATE datetime not null default '01/01/1900',
    GLPOSTDT datetime not null default '01/01/1900',
    DUEDATE datetime not null default '01/01/1900',
    ORTRXAMT numeric(19,5) not null default 0.00,
    CURTRXAM numeric(19,5) not null default…

Real Life Dynamics GP User has posted a unified view for Customer information including…