Weekly Dynamic: AR HATB via SQL

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