The question came up on the Public GP Newsgroup about automating customer aging in GP. I recalled doing this for some customers in the past and found this method works well. GP calls a stored procedure called rmAgeCustomer to execute the Receivables Aging Process. You can do the same and schedule this to run periodically without user intervention. Run a DEXSQL.log when running that routine to trap this yourself.
Below is the SQL that will age all customers, all statement cycles, and all balance types as of the current date. I recommend you test this before deploying in a production environment.
DECLARE @O_iErrorState int, @I_dAgingDate datetime
select @I_dAgingDate = convert(varchar(10), GetDate(), 102)
EXEC dbo.rmAgeCustomer 0, '', 'þþþþþþþþþþþþþþþ', @I_dAgingDate, 127, 0, 0, '', @O_iErrorState OUT
SELECT @O_iErrorState
12 comments:
I am interested in understanding how I can put this in place. Would you add some more instructions on how to put this in place and help out a novice.
Many thanks
I just tested this on a test copy of my database and it appears to have worked perfectly. One question, is there any reason this can not be run multiple times throughout the day? If a user is in Dynamics when this runs could it cause problems?
Thanks for the post.
I don't expect it will cause any problems to run this throughout the day with users in the system. You can run RM Aging with users in the system. This isn't any different. I wouldn't run it any more often than necessary and can't think of a scenario where more than once daily would be necessary.
can you advise the parameters in the execute part? When i ran the dexlog i had a 1 instead of a 0. It works nicely and i want to ensure i understand it before deploying into production.
mritter,
The parameters are, in this order:
BalanceType int
BeginCustNumber char(15)
EndCustNumber char(15)
AgingDate datetime
StatementCycle int
AgeFinanceCharges tinyint
MarkedTable tinyint
ErrorState int (output)
I'm guessing you had Age Finance Charges marked when you ran it. If so, the execute command would look like this:
EXEC dbo.rmAgeCustomer 0, '', 'þþþþþþþþþþþþþþþ', @I_dAgingDate, 127, 1, 0, '', @O_iErrorState OUT
The 1 following the 127 (Statement Cycle) is the Age Finance Charges flag.
Let me know if you were referring to another parm begin set to 1 and I'll help you interpret that.
thank you this worked, i have a few more questions.
When i age all customers it takes about 16 hours when i age only 9400 customers it takes 4 hours. I know this is general but is this a normal runtime? It took the same time to run aging manually. Additionally do you have any similar routines to run inventory reconcilation or MRP regeneration through SQL?
i set this up in GP9 and it worked perfectly, last weekend i upgraded to GP10 and now it does not work, i get the following failure in SQL:
Msg 8162, Level 16, State 2, Procedure rmAgeCustomer, Line 0
The formal parameter "@UsingCustomRange" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.
Any idea what changed in GP10?
mritter,
This is the same logic that is executed when you run Aging manually in GP so you can expect the performance to be the same.
Thanks,
MJ
mritter,
This will work in GP 10 as is. Can you e-mail exactly what you are executing to produce this error? I'll take a look and try to help you resolve this issue.
Thanks,
MJ
really amazing blog and I was thankful to you for sharing such a useful information.
- Microsoft Dynamics CRM
Hi,
Ive found this a very useful but I have a slight problem. Im trying to set it so when it runs it runs as at the last day of the current month. Ive tried a few ways and it still just ages as at current day. Can you please advise if / how this is possible to achieve please?
Kind Regards
Tim.
It is possible.
You can set this to age as of the last day of the current month by setting the "select @I_dAgingDate = SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))".
Post a Comment