# Weekly Dynamic: AR HATB via SQL

- URL: https://dynamicaccounting.net/weekly-dynamic-ar-hatb-via-sql/
- Date: 2013-02-11
- Topics: Payables & Receivables
- Dynamics GP versions mentioned: GP 2010, GP 10
- Type: original article

So you want to run the AR Historical Trial Balance report via SQL. Well, in GP 2010 and higher for  (I don’t have GP 10 handy to test) the AR HATB report is available as an built in SSRS report. The foundation for that report is a SQL stored procedure that we can leverage to run the AR HATB in SQL.

The code to run the stored procedure is below so you can copy and paste it. The middle section is where you set the parameters. Have fun.

Mark

USE TWO

DECLARE @RC int

DECLARE @I\_dAgingDate datetime

DECLARE @I\_cStartCustomerNumber char(15)

DECLARE @I\_cEndCustomerNumber char(15)

DECLARE @I\_cStartCustomerName char(65)

DECLARE @I\_cEndCustomerName char(65)

DECLARE @I\_cStartClassID char(15)

DECLARE @I\_cEndClassID char(15)

DECLARE @I\_cStartSalesPersonID char(15)

DECLARE @I\_cEndSalesPersonID char(15)

DECLARE @I\_cStartSalesTerritory char(15)

DECLARE @I\_cEndSalesTerritory char(15)

DECLARE @I\_cStartShortName char(15)

DECLARE @I\_cEndShortName char(15)

DECLARE @I\_cStartState char(5)

DECLARE @I\_cEndState char(5)

DECLARE @I\_cStartZipCode char(11)

DECLARE @I\_cEndZipCode char(11)

DECLARE @I\_cStartPhoneNumber char(21)

DECLARE @I\_cEndPhoneNumber char(21)

DECLARE @I\_cStartUserDefined char(15)

DECLARE @I\_cEndUserDefined char(15)

DECLARE @I\_tUsingDocumentDate tinyint

DECLARE @I\_dStartDate datetime

DECLARE @I\_dEndDate datetime

DECLARE @I\_sIncludeBalanceTypes smallint

DECLARE @I\_tExcludeNoActivity tinyint

DECLARE @I\_tExcludeMultiCurrency tinyint

DECLARE @I\_tExcludeZeroBalanceCustomer tinyint

DECLARE @I\_tExcludeFullyPaidTrxs tinyint

DECLARE @I\_tExcludeCreditBalance tinyint

DECLARE @I\_tExcludeUnpostedAppldCrDocs tinyint

DECLARE @I\_tConsolidateNAActivity tinyint

— TODO: Set parameter values here.

Set @I\_dAgingDate=’9/30/12′

Set @I\_cStartCustomerNumber=”

Set @I\_cEndCustomerNumber=’ZZZZZZZZ’

Set @I\_cStartCustomerName=”

Set @I\_cEndCustomerName=’ZZZZZZZZ’

Set @I\_cStartClassID=”

Set @I\_cEndClassID=’ZZZZZZZZ’

Set @I\_cStartSalesPersonID=”

Set @I\_cEndSalesPersonID=’ZZZZZZZZ’

Set @I\_cStartSalesTerritory=”

Set @I\_cEndSalesTerritory=’ZZZZZZZZ’

Set @I\_cStartShortName=”

Set @I\_cEndShortName=’ZZZZZZZZ’

Set @I\_cStartState=”

Set @I\_cEndState=’ZZZZZZZZ’

Set @I\_cStartZipCode=”

Set @I\_cEndZipCode=’ZZZZZZZZ’

Set @I\_cStartPhoneNumber=”

Set @I\_cEndPhoneNumber=’ZZZZZZZZ’

Set @I\_cStartUserDefined=”

Set @I\_cEndUserDefined=’ZZZZZZZZ’

Set @I\_tUsingDocumentDate=0

Set @I\_dStartDate=’1/1/1900′

Set @I\_dEndDate=’9/30/12′

Set @I\_sIncludeBalanceTypes=0

Set @I\_tExcludeNoActivity=1

Set @I\_tExcludeMultiCurrency=1

Set @I\_tExcludeZeroBalanceCustomer=1

Set @I\_tExcludeFullyPaidTrxs=1

Set @I\_tExcludeCreditBalance=0

Set @I\_tExcludeUnpostedAppldCrDocs=1

Set @I\_tConsolidateNAActivity=0

EXECUTE @RC = [dbo].[seermHATBSRSWrapper]

@I\_dAgingDate

,@I\_cStartCustomerNumber

,@I\_cEndCustomerNumber

,@I\_cStartCustomerName

,@I\_cEndCustomerName

,@I\_cStartClassID

,@I\_cEndClassID

,@I\_cStartSalesPersonID

,@I\_cEndSalesPersonID

,@I\_cStartSalesTerritory

,@I\_cEndSalesTerritory

,@I\_cStartShortName

,@I\_cEndShortName

,@I\_cStartState

,@I\_cEndState

,@I\_cStartZipCode

,@I\_cEndZipCode

,@I\_cStartPhoneNumber

,@I\_cEndPhoneNumber

,@I\_cStartUserDefined

,@I\_cEndUserDefined

,@I\_tUsingDocumentDate

,@I\_dStartDate

,@I\_dEndDate

,@I\_sIncludeBalanceTypes

,@I\_tExcludeNoActivity

,@I\_tExcludeMultiCurrency

,@I\_tExcludeZeroBalanceCustomer

,@I\_tExcludeFullyPaidTrxs

,@I\_tExcludeCreditBalance

,@I\_tExcludeUnpostedAppldCrDocs

,@I\_tConsolidateNAActivity