USP_DATALIST_GLACCOUNT2

Returns a list of accounts.

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@ACCOUNTCODE	nvarchar(100)	IN	Account Code
@DESCRIPTION	nvarchar(400)	IN	Description

Definition

Copy



            CREATE procedure dbo.USP_DATALIST_GLACCOUNT2
                (
                @ID uniqueidentifier,
                @ACCOUNTCODE nvarchar(100) = '',
                @DESCRIPTION nvarchar(400) = ''
                )
                as
                    set nocount on;

                    set @ACCOUNTCODE = replace(@ACCOUNTCODE, '*', '%');
                    set @ACCOUNTCODE = replace(@ACCOUNTCODE, '?', '_');

                    set @DESCRIPTION = replace(@DESCRIPTION, '*', '%');
                    set @DESCRIPTION = replace(@DESCRIPTION, '?', '_');

                    select
                        T1.ID,
                        T1.ACCOUNTNUMBER,
                        T1.ACCOUNTDESCRIPTION,
                        (SELECT CATEGORYNAME FROM DBO.PDCATEGORYDEFINITION WHERE PDCATEGORYDEFINITION.ID = PDCATEGORYDEFINITIONID) CATEGORY,
            T1.ACCOUNTALIAS
                        from GLACCOUNT T1
                    inner join PDACCOUNTSEGMENT T2 on T1.ID=T2.GLACCOUNTID 
                    inner join PDACCOUNTSEGMENTVALUE T3 on T2.PDACCOUNTSEGMENTVALUEID = T3.ID 
                    inner join PDACCOUNTSTRUCTURE T4 on T4.ID =T3.PDACCOUNTSTRUCTUREID 
                    where 
                        SEGMENTTYPE = 1
                    and 
                        T3.SHORTDESCRIPTION like @ACCOUNTCODE + '%'
                    and
                        T1.ACCOUNTDESCRIPTION like '%' + @DESCRIPTION + '%'
                    and 
                        T1.PDACCOUNTSYSTEMID = @ID
                    and 
                        T4.PDACCOUNTSYSTEMID = @ID    

                    order by T1.ACCOUNTNUMBER