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'