USP_GETCONSTITUENTNOTIFICATIONSFORDAILYSALES

CREATE procedure dbo.USP_GETCONSTITUENTNOTIFICATIONSFORDAILYSALES
(
    @CONSTITUENTID uniqueidentifier,
    @APPUSERID uniqueidentifier = null
)
as begin
    set nocount on;

    declare @date datetime = cast(cast(getdate() as date) as datetime);

    declare @DAILYSALESNOTIFICATION table (
        ID uniqueidentifier,
        NAME nvarchar(max),
        MESSAGE nvarchar(max),
        RECORDIDSETREGISTERID uniqueidentifier,
        APPUSERIDSETREGISTERID uniqueidentifier,
        APPLYTOCODE tinyint
    );

    insert into @DAILYSALESNOTIFICATION
    select
        NOTIFICATION.ID,
        NOTIFICATION.NAME,
        NOTIFICATION.MESSAGE,
        NOTIFICATION.RECORDIDSETREGISTERID,
        NOTIFICATION.APPUSERIDSETREGISTERID,
        NOTIFICATION.APPLYTOCODE
    from dbo.NOTIFICATION
    inner join dbo.ALTRUNOTIFICATION on NOTIFICATION.ID = ALTRUNOTIFICATION.ID
    inner join dbo.RECORDTYPE on NOTIFICATION.RECORDTYPEID = RECORDTYPE.ID
    where
        RECORDTYPE.NAME = 'Constituent'
        and ALTRUNOTIFICATION.DISPLAYINDAILYSALES = 1
        and (
            NOTIFICATION.VALIDUNTIL is null
            or @date <= NOTIFICATION.VALIDUNTIL
        );

    select
        NAME,
        MESSAGE
    from @DAILYSALESNOTIFICATION NOTIFICATION
    where
        (
            NOTIFICATION.RECORDIDSETREGISTERID is null
            or dbo.UFN_IDSETREADER_EXISTSIN_GUID(NOTIFICATION.RECORDIDSETREGISTERID, @CONSTITUENTID) = 1
        )
        and (
            NOTIFICATION.APPLYTOCODE = 0
            or dbo.UFN_IDSETREADER_EXISTSIN_GUID(NOTIFICATION.APPUSERIDSETREGISTERID, @APPUSERID) = 1
        );

    return 0;
end