USP_DATAFORMTEMPLATE_EDITLOAD_SIMPLEACKNOWLEDGEMENTLETTER

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_SIMPLEACKNOWLEDGEMENTLETTER(
    @SIMPLEACKNOWLEDGEMENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier
)
as
begin

    declare @DEFAULTEXPORTID uniqueidentifier = dbo.UFN_COMMUNICATIONLETTER_GETDEFAULTEXPORTDEFINITIONID(3, 0);
    declare @MKTSEGMENTATIONACTIVATEPROCESSID uniqueidentifier;

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

    select
        isnull(SIMPLEACKNOWLEDGEMENT.EXPORTDEFINITIONID, @DEFAULTEXPORTID) as MAILEXPORTDEFINITIONID,
        @DEFAULTEXPORTID as DEFAULTMAILEXPORTDEFINITIONID,
        isnull(SIMPLEACKNOWLEDGEMENT.EXPORTDEFINITIONID, @DEFAULTEXPORTID) as EMAILEXPORTDEFINITIONID,
        @DEFAULTEXPORTID as DEFAULTEMAILEXPORTDEFINITIONID,
        dbo.UFN_SIMPLEACKNOWLEDGEMENT_GETSOLICITCODES_TOITEMLISTXML(SIMPLEACKNOWLEDGEMENT.ID) as EXCLUDEDSOLICITCODESXML,
        (select ID from dbo.RECORDTYPE where NAME = 'REVENUE') as RECORDTYPEID,
        dbo.[UFN_COMMUNICATION_GETLETTERS_TOITEMLISTXML](MKTSEGMENTATION.ID, 3) 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.MKTSEGMENTATION 
        left join dbo.SIMPLEACKNOWLEDGEMENT on SIMPLEACKNOWLEDGEMENT.ID = MKTSEGMENTATION.ID 
    where
        MKTSEGMENTATION.ID = @SIMPLEACKNOWLEDGEMENTID;

    return 0;
end