USP_DATAFORMTEMPLATE_VIEW_REMINDEROPTION

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

    declare @MKTSEGMENTATIONACTIVATEPROCESSID nvarchar(36);    

    select 
        @MKTSEGMENTATIONACTIVATEPROCESSID = cast(ID as nvarchar(36)) 
    from 
        dbo.MKTSEGMENTATIONACTIVATEPROCESS 
    where 
        MKTSEGMENTATIONACTIVATEPROCESS.SEGMENTATIONID = @ID;

    select
        NAMEFORMATPARAMETER.NAME as NAMEFORMATPARAMETER,
        ADDRESSPROCESSINGOPTION.NAME ADDRESSPROCESSINGOPTION,
        dbo.UFN_REMINDEROPTION_GETSOLICITCODES_TOITEMLISTXML(MKTSEGMENTATION.ID) as EXCLUDEDSOLICITCODESXML,
        isnull((select top 1 sysschedules.freq_type --weekly = 8 weekly = 16

                    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 '%' + @MKTSEGMENTATIONACTIVATEPROCESSID + '%'),0) as FREQUENCY,
        dbo.UFN_REMINDER_SCHEDULEMODIFIED(@MKTSEGMENTATIONACTIVATEPROCESSID) as SCHEDULEMODIFIED,
        dbo.UFN_COMMUNICATIONLETTER_GETSCHEDULEINFO_TOITEMLISTXML(MKTSEGMENTATION.ID) as LETTERS
    from
        dbo.REMINDEROPTION
    inner join dbo.MKTSEGMENTATION on MKTSEGMENTATION.ID = REMINDEROPTION.ID
    inner join dbo.MKTSEGMENTATIONACTIVATEPROCESS on MKTSEGMENTATIONACTIVATEPROCESS.SEGMENTATIONID = MKTSEGMENTATION.ID
    left join dbo.NAMEFORMATPARAMETER on NAMEFORMATPARAMETER.ID = MKTSEGMENTATION.NAMEFORMATPARAMETERID
    left join dbo.ADDRESSPROCESSINGOPTION on ADDRESSPROCESSINGOPTION.ID = MKTSEGMENTATION.ADDRESSPROCESSINGOPTIONID
    where
        REMINDEROPTION.ID = @ID;

    return 0;