Historical Aged Trial Balance script

Earlier I wrote about using the native script for the RM Historical Aged Trial Balance report. This is  an example of using that script in a report.

 Because we're using a stored procedure from Dynamics GP, we have to use a 'Select Into' approach to get the stored procedure fields into a table. I created all the fields in the temp table as VARCHAR(100) except for the fields that I needed, those I typed correctly. There are 87 fields and time was a concern. The job of correctly typing those fields is left to you <smiles>

The resulting stored procedure produces this output. It is summed by customer:

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

 

IF exists (select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'dd_RMHistoricalAgingSummary') begin
    DROP proc dd_RMHistoricalAgingSummary
end
GO
   
       
CREATE proc dd_RMHistoricalAgingSummary
-- dd_RMHistoricalAgingSummary '6/17/2014',1
 
       
@ASOFDATE AS DATETIME,
@Consolidate_National_Accounts bit
 
AS
 
declare  @out table  (
    [APPLY_AMOUNT] [numeric](19, 5) NOT NULL,
    [AGING_AMOUNT] [numeric](19, 5) NOT NULL,
    [CUSTNMBR] [char](15) NOT NULL,
    [CUSTNAME] [char](65) NOT NULL,
    [BALNCTYP] [smallint] NOT NULL,
    [USERDEF1] [char](21) NOT NULL,
    [CNTCPRSN] [char](61) NOT NULL,
    [PHONE1] [char](21) NOT NULL,
    [SLPRSNID] [char](15) NOT NULL,
    [SALSTERR] [char](15) NOT NULL,
    [PYMTRMID] [char](21) NOT NULL,
    [CRLMTAMT] [numeric](19, 5) NOT NULL,
    [CRLMTPER] [smallint] NOT NULL,
    [CRLMTPAM] [numeric](19, 5) NOT NULL,
    [CRLMTTYP] [smallint] NOT NULL,
    [CUSTCLAS] [char](15) NOT NULL,
    [SHRTNAME] [char](15) NOT NULL,
    [ZIP] [char](11) NOT NULL,
    [STATE] [char](29) NOT NULL,
    [CUDSCRIPTN] [char](31) NOT NULL,
    [AGNGDATE] [datetime] NOT NULL,
    [CHCUMNUM] [char](15) NOT NULL,
    [DOCNUMBR] [char](21) NOT NULL,
    [RMDTYPAL] [smallint] NOT NULL,
    [DSCRIPTN] [char](31) NOT NULL,
    [DCURNCYID] [char](15) NOT NULL,
    [ORTRXAMT] [numeric](19, 5) NOT NULL,
    [CURTRXAM] [numeric](19, 5) NOT NULL,
    [AGNGBUKT] [smallint] NOT NULL,
    [CASHAMNT] [numeric](19, 5) NOT NULL,
    [COMDLRAM] [numeric](19, 5) NOT NULL,
    [SLSAMNT] [numeric](19, 5) NOT NULL,
    [COSTAMNT] [numeric](19, 5) NOT NULL,
    [FRTAMNT] [numeric](19, 5) NOT NULL,
    [MISCAMNT] [numeric](19, 5) NOT NULL,
    [TAXAMNT] [numeric](19, 5) NOT NULL,
    [DISAVAMT] [numeric](19, 5) NOT NULL,
    [DDISTKNAM] [numeric](19, 5) NOT NULL,
    [DWROFAMNT] [numeric](19, 5) NOT NULL,
    [TRXDSCRN] [char](31) NOT NULL,
    [DOCABREV] [char](3) NOT NULL,
    [CHEKNMBR] [char](21) NOT NULL,
    [DOCDATE] [datetime] NOT NULL,
    [DUEDATE] [datetime] NOT NULL,
    [GLPOSTDT] [datetime] NOT NULL,
    [DISCDATE] [datetime] NOT NULL,
    [POSTDATE] [datetime] NOT NULL,
    [DINVPDOF] [datetime] NOT NULL,
    [DCURRNIDX] [smallint] NOT NULL,
    [DXCHGRATE] [numeric](19, 7) NOT NULL,
    [ORCASAMT] [numeric](19, 5) NOT NULL,
    [ORSLSAMT] [numeric](19, 5) NOT NULL,
    [ORCSTAMT] [numeric](19, 5) NOT NULL,
    [ORDAVAMT] [numeric](19, 5) NOT NULL,
    [ORFRTAMT] [numeric](19, 5) NOT NULL,
    [ORMISCAMT] [numeric](19, 5) NOT NULL,
    [ORTAXAMT] [numeric](19, 5) NOT NULL,
    [ORCTRXAM] [numeric](19, 5) NOT NULL,
    [ORORGTRX] [numeric](19, 5) NOT NULL,
    [DORDISTKN] [numeric](19, 5) NOT NULL,
    [DORWROFAM] [numeric](19, 5) NOT NULL,
    [DDENXRATE] [numeric](19, 7) NOT NULL,
    [DMCTRXSTT] [smallint] NOT NULL,
    [Aging_Period_Amount] [numeric](19, 5) NOT NULL,
    [APFRDCNM] [char](21) NOT NULL,
    [APFRDCTY] [smallint] NOT NULL,
    [FROMCURR] [char](15) NOT NULL,
    [APTODCNM] [char](21) NOT NULL,
    [APTODCTY] [smallint] NOT NULL,
    [APPTOAMT] [numeric](19, 5) NOT NULL,
    [ACURNCYID] [char](15) NOT NULL,
    [DATE1] [datetime] NOT NULL,
    [POSTED] [tinyint] NOT NULL,
    [ADISTKNAM] [numeric](19, 5) NOT NULL,
    [AWROFAMNT] [numeric](19, 5) NOT NULL,
    [PPSAMDED] [numeric](19, 5) NOT NULL,
    [GSTDSAMT] [numeric](19, 5) NOT NULL,
    [ACURRNIDX] [smallint] NOT NULL,
    [AXCHGRATE] [numeric](19, 7) NOT NULL,
    [RLGANLOS] [numeric](19, 5) NOT NULL,
    [ORAPTOAM] [numeric](19, 5) NOT NULL,
    [AORDISTKN] [numeric](19, 5) NOT NULL,
    [AORWROFAM] [numeric](19, 5) NOT NULL,
    [ADENXRATE] [numeric](19, 7) NOT NULL,
    [AMCTRXSTT] [smallint] NOT NULL 
 )
 
insert into @Out
exec seermHATBSRSWrapper
 
@I_dAgingDate=@ASOFDATE,
@I_cStartCustomerNumber=N'0',@I_cEndCustomerNumber=N'zzz',
@I_cStartCustomerName=N'',@I_cEndCustomerName=N'',
@I_cStartClassID=N'',@I_cEndClassID=N'',
@I_cStartSalesPersonID=N'',@I_cEndSalesPersonID=N'',
@I_cStartSalesTerritory=N'',@I_cEndSalesTerritory=N'',
@I_cStartShortName=N'',@I_cEndShortName=N'',
@I_cStartState=N'',@I_cEndState=N'',
@I_cStartZipCode=N'',@I_cEndZipCode=N'',
@I_cStartPhoneNumber=N'',@I_cEndPhoneNumber=N'',
@I_cStartUserDefined=N'',@I_cEndUserDefined=N'',
@I_tUsingDocumentDate=0,
@I_dStartDate='1900-01-01 00:00:00',@I_dEndDate=@ASOFDATE,
@I_sIncludeBalanceTypes=0,        -- 0 = yes
@I_tExcludeNoActivity=1,          -- 0 = yes
@I_tExcludeMultiCurrency=1,       -- ?
@I_tExcludeZeroBalanceCustomer=1, -- 1 = yes
@I_tExcludeFullyPaidTrxs=1,       -- 1=yes
@I_tExcludeCreditBalance=0,       -- 0 = no
@I_tExcludeUnpostedAppldCrDocs=1, -- 1 = yes
@I_tConsolidateNAActivity = @Consolidate_National_Accounts     -- ?
 
 
--SELECT AGNGBUKT,apply_amount,aging_Amount,CUSTNMBR,CUSTNAME FROM @out
select CUSTNMBR,
        CUSTNAME,
        SUM(aging_Amount+apply_amount) AS BALANCE,
        ISNULL(SUM(CASE WHEN AGNGBUKT = 1 THEN aging_Amount+apply_amount END),0) AS CURR,
        ISNULL(SUM(CASE WHEN AGNGBUKT = 2 THEN aging_Amount+apply_amount END),0) AS ID31to60,
        ISNULL(SUM(CASE WHEN AGNGBUKT = 3 THEN aging_Amount+apply_amount END),0) AS ID61to90,
        ISNULL(SUM(CASE WHEN AGNGBUKT = 4 THEN aging_Amount+apply_amount END),0) AS [OVER]
    From @out
    GROUP BY CUSTNMBR, CUSTNAME
    order by custnmbr
     
-- dd_RMHistoricalAgingSummary '5/31/2014'
 
    
GO
  
GRANT EXEC ON dd_RMHistoricalAgingSummary TO PUBLIC

 

 

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences