USP_PROSPECTPLAN_SECONDARYFUNDRAISERS_ADDREQUESTSFROMXML

CREATE PROCEDURE [dbo].[USP_PROSPECTPLAN_SECONDARYFUNDRAISERS_ADDREQUESTSFROMXML] 
(
@PROSPECTPLANID uniqueidentifier,
@XML xml,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null,
@CURRENTAPPUSERID uniqueidentifier = null
)

as

    set nocount on;

    if @CHANGEAGENTID is null
        exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    if @CHANGEDATE is null 
        set @CHANGEDATE = getdate()

    -- build a temporary table containing the values from the XML

    declare @TempTbl table (
       [FUNDRAISERID] uniqueidentifier,
       [ID] uniqueidentifier,
       [SOLICITORROLECODEID] uniqueidentifier,
       [DATEFROM] datetime)

    insert into @TempTbl select 
        [FUNDRAISERID],
        [ID],
        [SOLICITORROLECODEID],
        [DATEFROM]
    from dbo.UFN_PROSPECTPLAN_SECONDARYFUNDRAISERS_FROMITEMLISTXML(@XML)


    declare @changes table (
       [ID] uniqueidentifier,
       [REQUESTID] uniqueidentifier,
       [NEWFUNDRAISERID] uniqueidentifier,
       [SOLICITORROLECODEID] uniqueidentifier,
       [DATEFROM] datetime)

    -- RobertDi 11/02/2009 :  Requests used to include "deletion" requests.  However, we aren't loading existing 

    --                        fundraisers into the UI anymore, so this functionality was removed.

    --                        There might not be much point in checking for changes, either, although it will

    --                        save the user from the confusion of seeing requests that won't change anything

    --                        if approved.


    -- log changes

    insert into @changes(ID,REQUESTID, NEWFUNDRAISERID, SOLICITORROLECODEID, DATEFROM)
    select SECONDARYFUNDRAISER.ID, newid(), temp.FUNDRAISERID, temp.[SOLICITORROLECODEID], temp.[DATEFROM]
        from dbo.[SECONDARYFUNDRAISER] inner join @TempTbl as [temp] on [SECONDARYFUNDRAISER].ID = [temp].ID
        where ([SECONDARYFUNDRAISER].[FUNDRAISERID]<>temp.[FUNDRAISERID]) or 
            ([SECONDARYFUNDRAISER].[FUNDRAISERID] is null and temp.[FUNDRAISERID] is not null) or 
            ([SECONDARYFUNDRAISER].[FUNDRAISERID] is not null and temp.[FUNDRAISERID] is null) or 
            ([SECONDARYFUNDRAISER].[ID]<>temp.[ID]) or 
            ([SECONDARYFUNDRAISER].[ID] is null and temp.[ID] is not null) or 
            ([SECONDARYFUNDRAISER].[ID] is not null and temp.[ID] is null) or 
            ([SECONDARYFUNDRAISER].[SOLICITORROLECODEID]<>temp.[SOLICITORROLECODEID]) or 
            ([SECONDARYFUNDRAISER].[SOLICITORROLECODEID] is null and temp.[SOLICITORROLECODEID] is not null) or 
            ([SECONDARYFUNDRAISER].[SOLICITORROLECODEID] is not null and temp.[SOLICITORROLECODEID] is null) or 
            ([SECONDARYFUNDRAISER].[DATEFROM]<>temp.[DATEFROM]) or 
            ([SECONDARYFUNDRAISER].[DATEFROM] is null and temp.[DATEFROM] is not null) or 
            ([SECONDARYFUNDRAISER].[DATEFROM] is not null and temp.[DATEFROM] is null)


    -- log items added to XML table

    insert into @changes(ID, REQUESTID, NEWFUNDRAISERID, SOLICITORROLECODEID, DATEFROM)
    select null, newid(), temp.FUNDRAISERID, temp.SOLICITORROLECODEID, temp.DATEFROM
    from @TempTbl as [temp]
    where not exists (select ID from dbo.[SECONDARYFUNDRAISER] as data where data.ID = [temp].ID)


    --Log changes to request tables

    insert into dbo.PROSPECTASSIGNMENTREQUEST (ID, TYPECODE, NEWFUNDRAISERID, SUBMITTEDBYID, STARTDATE, ADDEDBYID, CHANGEDBYID)
    select [REQUESTID], 4, NEWFUNDRAISERID, @CURRENTAPPUSERID, DATEFROM, @CHANGEAGENTID, @CHANGEAGENTID from @changes

    insert into dbo.PROSPECTASSIGNMENTREQUESTPROSPECTPLAN (ID, PROSPECTPLANID, SECONDARYFUNDRAISERID, SOLICITORROLECODEID, ADDEDBYID, CHANGEDBYID)
    select [REQUESTID], @PROSPECTPLANID, ID, SOLICITORROLECODEID ,@CHANGEAGENTID, @CHANGEAGENTID from @changes

    return 0;