Note:

 Like all scripts, this one is provided *as is*, without any warrantee for its suitability for your purpose. There is no substitute for due diligence

-- =============================================
-- Written by Steve Gray
-- 941-747-3669
-- =============================================


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'FP_TrialBalanceSummary' 
)
   DROP PROCEDURE FP_TrialBalanceSummary
GO
CREATE PROCEDURE FP_TrialBalanceSummary
-- FP_TrialBalanceSummary '1/1/2009','10/31/2009','4010-01','no','2009','yes','yes','yes','yes','Segment1'


@StartDate datetime,
@EndDate datetime,
@GLAccountNumber varchar(17), 
@HistoryYear varchar(3), /* yes/no*/
@Year int,
@IncludeInactiveAccounts varchar(3), /* yes/no */
@IncludePostingAccounts varchar(3), /* yes/no */
@IncludeUnitAccounts  varchar(3), /* yes/no */
@IncludeZeroBalanceAccounts   varchar(3), /* yes/no */
@SortBy varchar(10) /* the account segment to sort by*/


AS


-- validate
if YEAR(@startdate) <> @Year or YEAR(@EndDate) <> @Year begin
    select @StartDate = '1/1/1900', @EndDate = '1/1/1900'
end


--============================================================================
-- handle account type
--============================================================================
declare @accountTypes table (accountType int)
insert into @accounttypes (accountType) values (3)
insert into @accounttypes (accountType) values (4)
if @IncludeUnitAccounts = 'yes' begin
    insert into @accounttypes (accountType) values (2)
end
if @IncludePostingAccounts = 'yes' begin
    insert into @accounttypes (accountType) values (1)
end


--============================================================================
-- main query
--================================…

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…

If you need to update the system password in Dynamics, run this to reset it to 'sa':

Use…

All you 'best practices' people out there are going to have to turn your head for a minute. Of couse if you have a better idea, I'll listen...

Be aware that the techniques that I discuss here are version specific, and are not even guaranteed to work in different service packs.  

I…