Thursday, March 25, 2010

General Ledger Transactions for a Range of Accounts

The following stored procedure will imitate a general ledger transaction report from within Elite, with easy use in Excel. The concept is faily simple with the first SELECT statement pulling Accounts Payable entries and the second SELECT statement pulling non-AP general journal entries. This also uses the GJ entry description user defined function. There are two variables passed to the stored procedure: beginning and ending dates. The GL accounts are hard coded, but could easily be changed to variables.

IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'usp_GL_Expenses' 
)
   DROP PROCEDURE dbo.usp_GL_Expenses
GO

CREATE PROCEDURE dbo.usp_GL_Expenses
 @startdate DATETIME = NULL, 
 @enddate DATETIME = NULL
AS

SELECT
CAST(csddt.cknum AS VARCHAR(12)) AS 'Check #/GJ ID'
,csddt.ckline AS 'GJ Line'
,csddt.ckdate AS 'Date'
,MONTH(csddt.ckdate) AS 'Month'
,csddt.apnum AS 'Vendor #'
,csddt.apaddid AS 'Vendor Sub'
,ap.apname AS 'Vendor Name'
,apaddr.appynm AS 'Payee'
,csddt.vo_id AS 'Voucher'
,csddt.glnum AS 'GL Acct'
,csddt.glrc1 AS 'GL Dept'
,gl.gldes AS 'GL Description'
,gl.glnum + ' ' + gl.gldes AS 'GL Acct Description'
,csddt.amt AS 'Amount'
,ap.apct1 AS 'Vendor Type'
,voiddt AS 'Void Date'
,dbo.udfCsdDes(csddt.baid,csddt.cknum,csddt.ckline) AS 'Transaction Description'
FROM
csddt
INNER JOIN ap ON csddt.apnum = ap.apnum
INNER JOIN csd ON csddt.cknum = csd.cknum AND csddt.baid = csd.baid
INNER JOIN apaddr ON csddt.apnum = apaddr.apnum AND csddt.apaddid=apaddr.apaddid
INNER JOIN gl ON csddt.glnum = gl.glnum AND csddt.glrc1=gl.glrc1
WHERE
(csddt.glnum BETWEEN '62500' AND '63320'
OR csddt.glnum = '53100'
OR gl.glnum BETWEEN '84100' AND '84199'
OR gl.glnum BETWEEN '84400' AND '84499'
OR csddt.glnum = '93145')
AND csddt.ckdate BETWEEN @startdate AND @enddate

UNION

SELECT
gjid
,gjline
,gjtrdt
,MONTH(gjtrdt)
,NULL
,NULL
,NULL
,NULL
,NULL
,gj.glnum
,gj.glrc1
,gl.gldes
,gl.glnum + ' ' + gl.gldes AS 'glnumdes'
,ISNULL(dbamt,0)-ISNULL(cramt,0)
,NULL
,NULL
,des + ' ' + dbo.udfGJDes(gjid,gjline)
FROM gj
INNER JOIN gl ON gj.glnum=gl.glnum AND gj.glrc1=gl.glrc1
WHERE gjtrdt BETWEEN @startdate AND @enddate
AND (gl.glnum BETWEEN '62500' AND '63320'
OR gl.glnum = '53100'
OR gl.glnum BETWEEN '84100' AND '84199'
OR gl.glnum BETWEEN '84400' AND '84499'
OR gl.glnum = '93145')

GO
To execute the stored procedure, use this statement
EXECUTE dbo.usp_GL_Expenses '1/1/2010','12/31/2010'
GO
Make sure to grant execute to any other users that need access to this stored procedure.
GRANT EXECUTE ON dbo.usp_GL_Expenses TO elitereadonly;
GO

No comments:

Post a Comment