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

General Journal Entry Description User Defined Function

Below is a user defined function to concatenate all lines of a GJ entry description. Unlike most other tables in Elite, the GJ description table only includes records for the second through x lines. The first line of the description is stored in the gj table itself. So, to fully utilize this udf, it will need to be concatenated to the first line from the gj table.

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfGJDes] (@gjid VARCHAR(12), @gjline INT)  
RETURNS VARCHAR(1000) AS  
BEGIN 
 DECLARE @description VARCHAR(1000)
 
 SELECT @description = ''
 
 SELECT @description = @description + ' ' + des FROM gjdes WHERE gjid = @gjid AND gjline = @gjline ORDER BY gjline 
 
 RETURN LTRIM(RTRIM(@description))
END

Here's an example of how to use it properly

SELECT
gjid
,gjline
,gjtrdt
,MONTH(gjtrdt)
,gj.glnum
,gj.glrc1
,gl.gldes
,gl.glnum + ' ' + gl.gldes AS 'glnumdes'
,ISNULL(dbamt,0)-ISNULL(cramt,0)
,des + ' ' + dbo.udfGJDes(gjid,gjline)
FROM gj
INNER JOIN gl ON gj.glnum=gl.glnum AND gj.glrc1=gl.glrc1
WHERE gjtrdt BETWEEN '3/1/10' AND '3/31/10'
AND gl.glnum = '20236'

Payments by Invoice Ledger Code

This is a simple little query to see how payments from a particular client have been applied by the original invoice ledger code. For example, you can determine how much of the payments were applied to fees or costs. You may need to tinker with the criteria to match your setups. This uses the matter description udf.

SELECT 
mmatter AS 'Matter #'
,dbo.udfMattDesc(mmatter) AS 'Matter Description'
,SUM(CASE WHEN inv.llcode = 'FEES' THEN pmt.lamount ELSE 0 END) as 'Fees Collected'
,SUM(CASE WHEN inv.llcode <> 'FEES' THEN pmt.lamount ELSE 0 END) AS 'Costs Recovered'
,SUM(pmt.lamount) AS TOTAL
FROM ledger AS pmt
INNER JOIN matter ON pmt.lmatter=mmatter
INNER JOIN ledger AS inv ON pmt.laptoin=inv.lindex
WHERE YEAR(pmt.ltradat)='2009'
AND mclient='014523'
AND pmt.llcode LIKE 'PAY%'
GROUP BY mmatter