USP_DATALIST_APPUSERALERTSETTING

CREATE procedure dbo.USP_DATALIST_APPUSERALERTSETTING
(
    @ID uniqueidentifier
)
as
    set nocount on;

    select
        ALERTTYPE.ID,
        ALERTTYPE.NAME [ALERTNAME],
        case when APPUSERALERTTYPESETTING.APPUSERID = @ID and APPUSERALERTTYPESETTING.ENABLED = 1 then 1 else 0 end [ENABLED]
    from
        dbo.ALERTTYPE
    left outer join 
        dbo.APPUSERALERTTYPESETTING on APPUSERALERTTYPESETTING.ALERTTYPEID = ALERTTYPE.ID and APPUSERALERTTYPESETTING.APPUSERID = @ID
    where
        ALERTTYPE.TYPECODE = 1
    and
        (
            (
                ALERTTYPE.SECURITYCHECKFEATUREID is null
            ) 
            or
            (
                dbo.UFN_APPUSER_ISSYSADMIN(@ID) = 1 and (
                    case ALERTTYPE.SECURITYCHECKFEATURETYPECODE
                        when 1 then dbo.UFN_DATAFORMINSTANCE_EXISTS(ALERTTYPE.SECURITYCHECKFEATUREID)
                        when 2 then dbo.UFN_RECORDOPERATION_EXISTS(ALERTTYPE.SECURITYCHECKFEATUREID)
                        when 3 then dbo.UFN_DATALIST_EXISTS(ALERTTYPE.SECURITYCHECKFEATUREID)
                        when 4 then dbo.UFN_SYSTEMPRIVILEGE_EXISTS(ALERTTYPE.SECURITYCHECKFEATUREID)
                        when 5 then dbo.UFN_BUSINESSPROCESS_EXISTS(ALERTTYPE.SECURITYCHECKFEATUREID)
                        else 0
                    end) = 1
            ) 
            or
            (
                case ALERTTYPE.SECURITYCHECKFEATURETYPECODE
                    when 1 then dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@ID, ALERTTYPE.SECURITYCHECKFEATUREID)
                    when 2 then dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@ID, ALERTTYPE.SECURITYCHECKFEATUREID)
                    when 3 then dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@ID, ALERTTYPE.SECURITYCHECKFEATUREID)
                    when 4 then dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@ID, ALERTTYPE.SECURITYCHECKFEATUREID)
                    when 5 then dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_IN_SYSTEMROLE(@ID, ALERTTYPE.SECURITYCHECKFEATUREID)
                    else 0
                end
            ) = 1
        )
    and
        dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(ALERTTYPE.INSTALLEDPRODUCTLIST) = 1
    order by
        ALERTTYPE.NAME asc;

    return 0;