USP_DATAFORMTEMPLATE_ADDLOAD_APPEALMAILINGSETUPLETTER

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADDLOAD_APPEALMAILINGSETUPLETTER
as
begin

    declare @CONSTITUENTRECORDTYPEID uniqueidentifier;
    declare @MAILEXPORTDEFINITIONID uniqueidentifier = '5BB7835A-2634-4B01-A4BC-832C87B99EA9';
    declare @EMAILEXPORTDEFINITIONID uniqueidentifier = '29A3B986-62EB-48B8-A93D-BEB43831EF68';
    declare @NETCOMMUNITYDATASOURCEID int = 0;
    declare @BBNCURL nvarchar(1024) = '';
    declare @EMAILMERGEFIELDBBNCINFO xml = '';

    select 
        @CONSTITUENTRECORDTYPEID = [RECORDTYPE].[ID]
    from dbo.[RECORDTYPE] 
    where upper([NAME]) = 'CONSTITUENT';

    -- Retrieve BBNC Info

    select
        @NETCOMMUNITYDATASOURCEID = MKTEXPORTDEFINITION.NETCOMMUNITYDATASOURCEID,
        @BBNCURL = dbo.UFN_BBNC_URL(),
        @EMAILMERGEFIELDBBNCINFO = (
            select
                ID as FIELDID,
        -- have to determine this in code now

                --(select QUERYFIELD from dbo.MKTEXPORTDEFINITIONOUTPUTFIELD where EXPORTDEFINITIONID = @EMAILEXPORTDEFINITIONID and NAME = BBDM_Fields.Name) as FIELDVALUE,

        '' as FIELDVALUE,
                Name as FIELDNAME
            from dbo.BBDM_Fields
            where BBDM_Fields.DataSourceID = MKTEXPORTDEFINITION.NETCOMMUNITYDATASOURCEID
            for xml raw('ITEM'),type,elements,root('EMAILMERGEFIELDBBNCINFO'),binary base64
        )
    from dbo.MKTEXPORTDEFINITION
    where EXPORTDEFINITIONID = @EMAILEXPORTDEFINITIONID;

    select
        @CONSTITUENTRECORDTYPEID as CONSTITUENTRECORDTYPEID,
        dbo.UFN_MKTNETCOMMUNITYINTEGRATION_LINKESTABLISHED() as NETCOMMUNITYLINKESTABLISHED,
        @MAILEXPORTDEFINITIONID as MAILEXPORTDEFINITIONID,
        @EMAILEXPORTDEFINITIONID EMAILEXPORTDEFINITIONID,
        @NETCOMMUNITYDATASOURCEID as NETCOMMUNITYDATASOURCEID,
        (
            select 
                [NAME] as GROUPNAME 
            from dbo.[UFN_SELECTION_GETGROUPNAMES](@CONSTITUENTRECORDTYPEID) 
            for xml raw('ITEM'),type,elements,root('SELECTIONLISTS'),binary base64
        ) as SELECTIONLISTS,
        @BBNCURL as BBNCURL,
        @EMAILMERGEFIELDBBNCINFO as EMAILMERGEFIELDBBNCINFO;

end