Here is a simple script that will process customer aging for all your companies as of the current date . You can run and schedule it with a SQL Server job.
[sourcecode language=’sql’]/* =============================================
Author: http://bolivier.com
Create date: 2009-09-02
Description: Process customers aging
Ex. :
Modifications (Detail, Author, Date) :
–
=============================================*/
SET NOCOUNT ON;
DECLARE @sql VARCHAR(MAX)
,@date SMALLDATETIME;
SELECT @date = DATEADD(dd ,DATEDIFF(dd ,0 ,CURRENT_TIMESTAMP) ,0)
,@sql = ”;
SELECT @sql = @sql + ‘USE ‘ + c.INTERID + ‘;
EXEC dbo.rmAgeCustomer 0
,””
,”þþþþþþþþþþþþþþþ”
,”’ + CAST(@date AS VARCHAR) + ”’
,127
,1
,0
,””;’
FROM DYNAMICS.dbo.SY01500 c;
EXEC(@sql);[/sourcecode]
Here are the options I used in my script :
If you prefer to change some options, here are the parameters accepted by the rmAgeCustomer stored procedure :
[sourcecode language=’sql’]EXECUTE @RC = [dbo].[rmAgeCustomer]
@I_iBalanceType = 0 –Account type “All”
,@I_sBeginCustNumber = ” –Customer id “From”
,@I_sEndCustNumber = ‘þþþþþþþþþþþþþþþ’ –Customer ID “To”
,@I_dAgingDate = ‘2009-09-02’ –Aging date
,@I_iStatementCycle = 127 –Statement cycles “All selected”
,@I_tAgeFinanceCharges = 1 –Age finances charges “True”
,@UsingCustomRange = 0
,@MarkedTable ”
,@O_iErrorState OUTPUT[/sourcecode]