USP_CODETABLE_DATALIST

Lists all Code tables in the system.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@FILTERCATEGORY	uniqueidentifier	IN	Category

Definition

Copy



CREATE procedure dbo.USP_CODETABLE_DATALIST

@CURRENTAPPUSERID uniqueidentifier,
@FILTERCATEGORY uniqueidentifier = null

as

declare @ISSYSADMIN as bit
set @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

set nocount on;

select
    CT.ID,
    CT.ISBUILTIN,
    COALESCE(CAT.DESCRIPTION,'') AS [CATEGORY],
    CT.UINAME as CODETABLENAME,
    CT.DBTABLENAME,
    CASE dbo.[UFN_GETLISTSORTMETHOD](CT.DBTABLENAME)
    WHEN 1 THEN    'User defined' ELSE 'Alpha' END AS SORTMETHOD,
    case @ISSYSADMIN when 1 then 1 else dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRYADD_IN_SYSTEMROLE(@CURRENTAPPUSERID, CT.ID) end,
    case @ISSYSADMIN when 1 then 1 else dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRYUPDATE_IN_SYSTEMROLE(@CURRENTAPPUSERID, CT.ID) end,
    case @ISSYSADMIN when 1 then 1 else dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRYDELETE_IN_SYSTEMROLE(@CURRENTAPPUSERID, CT.ID) end
from dbo.V_INSTALLED_CODETABLECATALOG AS CT
left join dbo.CODETABLECATEGORY AS CAT on CT.CATEGORYID=CAT.ID

where 
(
    (@FILTERCATEGORY IS NULL) OR (CT.CATEGORYID=@FILTERCATEGORY)
)
order by CATEGORY, CT.UINAME;