USP_DATAFORMTEMPLATE_LOAD_REMINDERLETTER

CREATE procedure dbo.USP_DATAFORMTEMPLATE_LOAD_REMINDERLETTER(
    @REMINDEROPTIONID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier
)
as
begin
    declare @MKTSEGMENTATIONACTIVATEPROCESSID uniqueidentifier;

    select 
        @MKTSEGMENTATIONACTIVATEPROCESSID = ID 
    from dbo.MKTSEGMENTATIONACTIVATEPROCESS 
    where MKTSEGMENTATIONACTIVATEPROCESS.SEGMENTATIONID = @REMINDEROPTIONID;

    select
        REMINDEROPTION.EXPORTDEFINITIONID as MAILEXPORTDEFINITIONID,
        dbo.UFN_COMMUNICATIONLETTER_GETDEFAULTEXPORTDEFINITIONID(4, 0) as DEFAULTMAILEXPORTDEFINITIONID,
        REMINDEROPTION.EXPORTDEFINITIONID as EMAILEXPORTDEFINITIONID,
        dbo.UFN_COMMUNICATIONLETTER_GETDEFAULTEXPORTDEFINITIONID(4, 1) as DEFAULTEMAILEXPORTDEFINITIONID,
        dbo.UFN_REMINDEROPTION_GETSOLICITCODES_TOITEMLISTXML(@REMINDEROPTIONID) as EXCLUDEDSOLICITCODESXML,
        (select [ID] from dbo.[RECORDTYPE] where upper([NAME]) = 'REVENUE') as RECORDTYPEID,
        dbo.[UFN_COMMUNICATION_GETLETTERS_TOITEMLISTXML](@REMINDEROPTIONID, 4) as LETTERS,
        (
            select top 1 
                sysschedules.freq_type --weekly = 8 daily = 1

            from msdb.dbo.sysschedules
            inner join msdb.dbo.sysjobschedules 
                on sysschedules.schedule_id = sysjobschedules.schedule_id
            inner join msdb.dbo.sysjobsteps 
                on sysjobschedules.job_id = sysjobsteps.job_id
            inner join msdb.dbo.sysjobs 
                on sysjobschedules.job_id = sysjobs.job_id
            inner join msdb.dbo.sysproxies 
                on sysjobsteps.proxy_id = sysproxies.proxy_id
            where sysproxies.name like 'Blackbaud % Proxy - ' + db_name()
              and sysjobsteps.step_name = 'Business process invoke' 
              and (null is null or sysjobsteps.command like '%DatabaseName=' + null + '&%')  
              and sysjobsteps.command like '%' + cast(@MKTSEGMENTATIONACTIVATEPROCESSID as nvarchar(36)) + '%'
        ) as FREQUENCYCODE
    from
        dbo.REMINDEROPTION
    where
        REMINDEROPTION.ID = @REMINDEROPTIONID;

    return 0;
end