Home » Listing Details
Top Websites
  1. Dynamics GP Help
    Over 6100 resources listed.
  2. Mark Polino's DynamicAccounting.net
    Over 5100 resources listed.
  3. Rose Business Solutions Blog New
    Over 2200 resources listed.
  4. Developing for Dynamics GP - By David Musgrave and the MS GP Dev Support Team
    Over 1100 resources listed.
  5. Mariano Gomez at The Dynamics GP Blogster
    Over 1000 resources listed.
  6. Microsoft Dynamics Partner Community Blog
    Over 900 resources listed.
  7. Christina Phillips, Steve Endow & Lorren Zemke at Dynamics GP Land
    Over 700 resources listed.
  8. Mohammad Daoud's Dynamics GP Blog
    Over 600 resources listed.
  9. Vaidy Mohan at Dynamics GP - Learn & Discuss
    Over 500 resources listed.
  10. Inside Microsoft Dynamics GP Official Blog
    Over 500 resources listed.
  11. eOne Business Solutions Blog
    Over 400 resources listed.
  12. About Dynamics, Development and Life
    Over 300 resources listed.
  13. Frank Hamelly at GP2theMax
    Over 300 resources listed.
  14. Dynamics CPM
    Over 300 resources listed.
  15. BKD Dynamics GP Insights Blog
    Over 200 resources listed.
  16. Leslie Vail at Dynamics Confessor Blogspot
    Over 200 resources listed.
  17. Victoria Yudin's Dynamics GP Website
    Over 200 resources listed.
    Victoria Yudin
  18. Janakiram M.P. at DynamicsBlogger
    Over 100 resources listed.
  19. VS Tools Forum
    Over 100 resources listed.
    Your Resource for Visual Studio Tools for Dynamics GP
  20. Inside Microsoft Dynamics GP Official Blog
    Over 100 resources listed.
  21. US Dynamics GP Field Team Blog
    Over 100 resources listed.
  22. Catherine Eibner MBS Developer Evangelist
    Over 100 resources listed.
  23. Sivakumar Venkataraman at Interesting Findings & Knowledge Sharing
    Over 100 resources listed.
  24. Dynamics Small Business
    Over 100 resources listed.
  25. Belinda, The GP CSI
    Over 100 resources listed.

ID:11451
Title:AR Historical Aging
URL:http://vstoolsforum.com/blogs/dynamicsgp/archive/2010/04/28/ar-historical-aging.aspx
Description:

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 0.00,
    WROFAMNT numeric(19,5) not null default 0.00,
    DISTKNAM numeric(19,5) not null default 0.00,
    DISAVAMT numeric(19,5) not null default 0.00,
    DISAVTKN numeric(19,5) not null default 0.00,
    DISCDATE datetime not null default '01/01/1900',
    TRXDSCRN char(31) not null default '',
    CSPORNBR char(21) not null default '',
    SLPRSNID char(15) not null default '',
    SHRTNAME char(30) not null default '',
    SLPRSNFN char(30) not null default '', 
    SPRSNLN char(30) not null default '', 
    SALSTERR char(15) not null default '',
    INET1 char(255) not null default '',
    VOIDSTTS tinyint not null default 0,
    CURNCYID char(15) not null default '',
    PYMTRMID char(21) not null default '',
    SHIPMTHD char(15) not null default '',
    VOIDDATE datetime not null default '01/01/1900',
    CUSTNAME char(65) not null default '',
    CUSTCLAS char(15) not null default '',
    BALNCTYP tinyint not null default 0,
    LASTAGED datetime not null default '01/01/1900',
    USERDEF1 char(50) not null default '',
    CNTCPRSN char(65) not null default '',
    PHONE1 char(31) not null default '',
    CRLMTAMT numeric(19,5) not null default 0.00,
    CRDTSTRG char(200) not null default '',
    CRLMTPER tinyint not null default 0,
    CRLMTPAM numeric(19,5) not null default 0.00,
    NUMOFDYS int not null default 0,
    AGINGBK1 numeric(19,5) not null default 0.00,
    AGINGBK2 numeric(19,5) not null default 0.00,
    AGINGBK3 numeric(19,5) not null default 0.00,
    AGINGBK4 numeric(19,5) not null default 0.00,
    AGINGBK5 numeric(19,5) not null default 0.00,
    AGINGBK6 numeric(19,5) not null default 0.00,
    AGINGBK7 numeric(19,5) not null default 0.00,
    MY_DEX_ROW int Identity (1,1) )
 
--Get records from the RM Open table and insert them into the temp table.
insert into #RMOPENTRX (CUSTNMBR ,DOCNUMBR ,RMDTYPAL ,DOCDATE ,GLPOSTDT ,DUEDATE ,ORTRXAMT ,CURTRXAM ,WROFAMNT ,DISTKNAM ,DISAVAMT ,DISAVTKN ,DISCDATE ,TRXDSCRN ,CSPORNBR ,VOIDSTTS ,CURNCYID ,PYMTRMID ,SHIPMTHD ,VOIDDATE , CUSTNAME ,CUSTCLAS ,BALNCTYP ,USERDEF1 ,CNTCPRSN ,CRLMTAMT ,CRLMTPER ,CRLMTPAM , LASTAGED , SLPRSNID , SHRTNAME , SALSTERR , SLPRSNFN , SPRSNLN , INET1 , DOCABREV ,PHONE1,CRDTSTRG )
    select          a.CUSTNMBR,a.DOCNUMBR,a.RMDTYPAL,a.DOCDATE,a.GLPOSTDT,a.DUEDATE,a.ORTRXAMT,a.CURTRXAM,a.WROFAMNT,a.DISTKNAM,a.DISAVAMT,a.DISAVTKN,a.DISCDATE,a.TRXDSCRN,a.CSPORNBR,a.VOIDSTTS,a.CURNCYID,a.PYMTRMID,a.SHIPMTHD,a.VOIDDATE, c.CUSTNAME,c.CUSTCLAS,c.BALNCTYP,c.USERDEF1,c.CNTCPRSN,c.CRLMTAMT,c.CRLMTPER,c.CRLMTPAM, cm.LASTAGED, c.SLPRSNID, c.SHRTNAME, sm.SALSTERR, sm.SLPRSNFN, sm.SPRSNSLN, inet.INET1, dt.DOCABREV,
            '(' + substring(c.PHONE1,1,3) + ')' + substring(c.PHONE1,4,3) + '-' + substring(c.PHONE1,7,4) + ' Ext. ' + substring(c.PHONE1,11,4),
            '$' + rtrim(cast(cast(c.CRLMTAMT as numeric(19,2)) as char(15))) + ' - Or if the Sum of Period ' + cast(c.CRLMTPER as char(1)) + ' and Beyond Exceeds ' + rtrim(cast(c.CRLMTPAM as char(15)))
        from RM20101 a (nolock)
            join RM00101 c (nolock) on a.CUSTNMBR = c.CUSTNMBR 
            join RM00103 cm (nolock) on cm.CUSTNMBR = c.custnmbr
            join RM40401 dt (nolock) on dt.RMDTYPAL = a.RMDTYPAL
            left join RM00301 sm (nolock) on sm.SLPRSNID = c.SLPRSNID
            left join SY01200 inet (nolock) on inet.Master_ID = c.SLPRSNID
        where a.CURTRXAM > 0
 
create index MY_DEX_ROW on #RMOPENTRX (MY_DEX_ROW)


--Cursor to set the transaction into the correct bucket based on the customers setup
DECLARE TRX_RANGE CURSOR FOR
    SELECT CUSTNMBR, RMDTYPAL, DOCDATE, DUEDATE, 
            case when rmdtypal >= 7 then -CURTRXAM else curtrxam end, 
            MY_DEX_ROW,LASTAGED
        FROM #RMOPENTRX 


OPEN TRX_RANGE
while 1=1 begin
    FETCH NEXT FROM TRX_RANGE INTO @C_CUSTNMBR, @C_RMDTYPAL, @C_DOCDATE, @C_DUEDATE, @C_CURTRXAM, @C_MY_DEX_ROW,@AGEDATE 
    if @@FETCH_STATUS <>0 begin
        break
    end


    --initialize
    set @C_DTDIFF = 0


    If @AGEBY = 1 Begin
        If (@C_DUEDATE > @AGEDATE) Begin 
            set @C_DTDIFF = 0
        End Else 
            If (@AGEUNAPPLDCR = 0 and @C_RMDTYPAL >= 7) Begin 
                set @C_DTDIFF = 0
            End Else 
                If (@AGEUNAPPLDCR = 1 and @C_DUEDATE = '01/01/1900' and @C_RMDTYPAL >= 7) Begin
                    set @C_DTDIFF = 0
                End Else Begin
                    SELECT @C_DTDIFF = DATEDIFF(day, @C_DOCDATE, @AGEDATE)
                End
    End Else Begin
        If (@C_DUEDATE > @AGEDATE) Begin 
            set @C_DTDIFF = 0
        End Else 
            If (@AGEUNAPPLDCR = 0 and @C_RMDTYPAL >= 7) Begin 
                set @C_DTDIFF = 0
            End Else 
                If (@AGEUNAPPLDCR = 1 and @C_DUEDATE = '01/01/1900' and @C_RMDTYPAL >= 7) Begin
                    set @C_DTDIFF = 0
                End Else Begin
                    SELECT @C_DTDIFF = DATEDIFF(day, @C_DUEDATE, @AGEDATE)
                End
    End


    --Fill the aging bucket for the transaction we are on.
    If (@C_DTDIFF = 0 ) Begin 
        update #RMOPENTRX set AGINGBK1 = @C_CURTRXAM, NUMOFDYS = @C_DTDIFF where MY_DEX_ROW = @C_MY_DEX_ROW
        Continue
    End
    If ((@C_DTDIFF <= @AGINGBKDY2) or (@C_DTDIFF > @MAXDAYS and @AGINGBKDY2 > @MAXDAYS)) Begin
        update #RMOPENTRX set AGINGBK2 = @C_CURTRXAM, NUMOFDYS = @C_DTDIFF where MY_DEX_ROW = @C_MY_DEX_ROW
        Continue
    End
    If ((@C_DTDIFF <= @AGINGBKDY3) or (@C_DTDIFF > @MAXDAYS and @AGINGBKDY3 > @MAXDAYS)) Begin
        update #RMOPENTRX set AGINGBK3 = @C_CURTRXAM, NUMOFDYS = @C_DTDIFF where MY_DEX_ROW = @C_MY_DEX_ROW
        Continue
    End
    If ((@C_DTDIFF <= @AGINGBKDY4) or (@C_DTDIFF > @MAXDAYS and @AGINGBKDY4 > @MAXDAYS)) Begin
        update #RMOPENTRX set AGINGBK4 = @C_CURTRXAM, NUMOFDYS = @C_DTDIFF where MY_DEX_ROW = @C_MY_DEX_ROW
        Continue
    End
    If ((@C_DTDIFF <= @AGINGBKDY5) or (@C_DTDIFF > @MAXDAYS and @AGINGBKDY5 > @MAXDAYS)) Begin
        update #RMOPENTRX set AGINGBK5 = @C_CURTRXAM, NUMOFDYS = @C_DTDIFF where MY_DEX_ROW = @C_MY_DEX_ROW
        Continue
    End
    If ((@C_DTDIFF <= @AGINGBKDY6) or (@C_DTDIFF > @MAXDAYS and @AGINGBKDY6 > @MAXDAYS)) Begin
        update #RMOPENTRX set AGINGBK6 = @C_CURTRXAM, NUMOFDYS = @C_DTDIFF where MY_DEX_ROW = @C_MY_DEX_ROW
        Continue
    End
    If ((@C_DTDIFF <= @AGINGBKDY7) or (@C_DTDIFF > @MAXDAYS and @AGINGBKDY7 > @MAXDAYS)) Begin
        update #RMOPENTRX set AGINGBK6 = @C_CURTRXAM, NUMOFDYS = @C_DTDIFF where MY_DEX_ROW = @C_MY_DEX_ROW
        Continue
    End 
End --End of the Item Range code block


CLOSE TRX_RANGE
DEALLOCATE TRX_RANGE


select NUMOFDYS,* from #RMOPENTRX order by CURTRXAM



GO


grant exec on FP_RMAgeTrialBalance to public

Category:SQL SCRIPTS: Receivables Management (SQL)
Link Owner:
Date Added:June 17, 2010 06:14:30 PM
Number Hits:110
RatingsAverage rating: (0 votes)
Reviews

No Reviews Yet.

 
GPWindow.com

Thank you for your support for GPWindow. It helps us cover part of the hosting costs for GPWindow.