Historical Aged Trial Balance script

	SOP Scripts
	eConnect
	taSopDeleteDocumentWrapper
	taSopLineDeleteWrapper
	taSOPLineIvcInsert Wrapper
	taSopLineIvcInsertComponent
	taSOPLotAuto - complete procedure
	Functions
	ddf_Address
	ddf_AllHoldsOnASOPDoc
	General
	Getting Email addresses from Dynamics GP
	Move a SOP transaction from open to history
	Posting Sequences
	SOP40200 'Allow' fields query
	sopGetIDNumber
	sopTrxDeleteWork
	Unvoid a SOP document
	Stored Procedures
	dd_SOP10102_UPD
	dd_SOPGetNextDocNumber/ sopGetIDNumber.
	dd_SOPPayment_INS
	dd_SOPRound - Round a SOP Multicurrency document to two decimal places
	dd_SOPTotal
	dd_SOPTotalTaxes
	dd_taSopVoidDocumentWrapper

You are accessing the Members Only area for DynDeveloper.com using guest access. The member experience is slightly different

Malachi
4Penny.net
Points: 7700
7/18/2014 1:20:57 PM

Historical Aged Trial Balance script

* This article, and all our great .NET Development documentation, Is available on the .NET Development menu

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:

Version: GP 2010,GP 2013

Section: SQL Scripts

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

Add a Comment

Not Subscribed. You will not receive emails on article changes or comment additions

Comments

body header
No records to display.