USP_DATAFORMTEMPLATE_ADD_APPEALMAILPREFERENCEBULKADD

The save procedure used by the add dataform template "Appeal Mail Preference Add Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@EMAILADDRESS	UDT_EMAILADDRESS	IN	Email Address
@PREFERENCES	xml	IN	Preferences

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_APPEALMAILPREFERENCEBULKADD
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
      @EMAILADDRESS UDT_EMAILADDRESS,
      @PREFERENCES XML
)
as

set nocount on;

if @ID is null
    set @ID = newid()

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try

  declare @PREFSTABLE table(APPEALCATEGORYCODEID uniqueidentifier, SITEID uniqueidentifier, BUSINESSUNITCODEID uniqueidentifier, SENDMAIL bit)
  insert into @PREFSTABLE
  select 
  P.I.value('APPEALCATEGORYCODEID[1]', 'uniqueidentifier'),
  P.I.value('SITEID[1]', 'uniqueidentifier'),
  P.I.value('BUSINESSUNITCODEID[1]', 'uniqueidentifier'),
  P.I.value('SENDMAIL[1]', 'bit')
  from @PREFERENCES.nodes('/PREFERENCES/ITEM') P(I)

    declare @EMAILCONSTIT table(EMAILID uniqueidentifier, CONSTITUENTID uniqueidentifier)
    insert into @EMAILCONSTIT
    select  ea.id, ea.CONSTITUENTID from dbo.EMAILADDRESS ea where ea.EMAILADDRESS = @EMAILADDRESS


    merge dbo.MAILPREFERENCE as target
    using
    (
        select EA.ID EMAILADDRESSID, EA.CONSTITUENTID, P.APPEALCATEGORYCODEID, P.SITEID, P.BUSINESSUNITCODEID, P.SENDMAIL 
        from @PREFSTABLE P cross join 
        (
          select  cast(min(cast(ec.EMAILID as nvarchar(100))) as uniqueidentifier) ID, ec.CONSTITUENTID
          from @EMAILCONSTIT ec
          group by ec.CONSTITUENTID
        ) EA 
    ) as source
    on ((target.CONSTITUENTID=source.CONSTITUENTID or target.CONSTITUENTID is null and source.CONSTITUENTID is null)
        and target.MAILTYPECODE=1
        and (target.BUSINESSUNITCODEID=source.BUSINESSUNITCODEID or target.BUSINESSUNITCODEID is null and source.BUSINESSUNITCODEID is null)
        and (target.CATEGORYCODEID=source.APPEALCATEGORYCODEID or target.CATEGORYCODEID is null and source.APPEALCATEGORYCODEID is null)
        and (target.SITEID=source.SITEID or target.SITEID is null and source.SITEID is null))
    when matched then
        update set CONSTITUENTID=source.CONSTITUENTID,
            MAILTYPECODE=1,
            BUSINESSUNITCODEID=source.BUSINESSUNITCODEID,
            CATEGORYCODEID=source.APPEALCATEGORYCODEID,
            SITEID=source.SITEID,
            SENDMAIL=source.sendmail,
            DELIVERYMETHODCODE=1,
            RECEIPTTYPECODE=0,
            EMAILADDRESSID=source.EMAILADDRESSID,
            CHANGEDBYID=@CHANGEAGENTID,
            DATECHANGED=@CURRENTDATE
    when not matched then
        insert (CONSTITUENTID,MAILTYPECODE,BUSINESSUNITCODEID,CATEGORYCODEID,SITEID,SENDMAIL,DELIVERYMETHODCODE,RECEIPTTYPECODE,EMAILADDRESSID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
            values (source.CONSTITUENTID, 1, source.BUSINESSUNITCODEID, source.APPEALCATEGORYCODEID, source.SITEID, source.SENDMAIL, 1, 0, source.EMAILADDRESSID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);;

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0