USP_REVENUEBATCH_ADDAPPLICATIONSOLICITORS

      CREATE procedure dbo.USP_REVENUEBATCH_ADDAPPLICATIONSOLICITORS
            (
                @REVENUEID uniqueidentifier,
                @APPLICATIONSOLICITORS xml,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime,
                @REVENUESPLITSPONSORSHIPID xml = null
            )
      as
            set nocount on;

            declare @BASECURRENCYID uniqueidentifier;
            declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

            select
                @BASECURRENCYID = BASECURRENCYID,
                @ORGANIZATIONEXCHANGERATEID = ORGANIZATIONEXCHANGERATEID
            from dbo.REVENUE where ID = @REVENUEID;

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

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

            begin try
                declare @REVENUESPLITID uniqueidentifier
                declare @SOLICITORS xml
                declare @SOLICITORSTABLE table
                (
                  APPLICATIONCODE tinyint,
                  DESIGNATIONID uniqueidentifier,
                  SOLICITORS xml,
                  DECLINESGIFTAID bit,
                  SPONSORSHIPID uniqueidentifier
                )
                declare @REVENUESPLITSPONSORSHIPTABLE table
                (
                    REVENUESPLITID uniqueidentifier,
                    SPONSORSHIPID uniqueidentifier
                )        

                insert into @SOLICITORSTABLE(APPLICATIONCODE, DESIGNATIONID, SOLICITORS, DECLINESGIFTAID, SPONSORSHIPID)
                select case T.c.value('(ADDITIONALAPPLICATIONTYPECODE)[1]','tinyint') when 0 then 0 when 1 then 4 when 2 then 7 when 3 then 0 end as APPLICATIONCODE,
                  T.c.value('(ADDITIONALAPPLICATIONDESIGNATIONID)[1]','uniqueidentifier') as DESIGNATIONID,
                  case when T.c.exist('./SOLICITORS/ITEM') = 1 then T.c.query('(SOLICITORS)[1]') else null end as SOLICITORS,
                T.c.value('(ADDITIONALAPPLICATIONDECLINESGIFTAID)[1]','bit') as DECLINESGIFTAID,
                T.c.value('(ADDITIONALAPPLICATIONSPONSORSHIPID)[1]','uniqueidentifier') as SPONSORSHIPID
                from @APPLICATIONSOLICITORS.nodes('/APPLICATIONSOLICITORS/ITEM') T(c)

                insert into @REVENUESPLITSPONSORSHIPTABLE(REVENUESPLITID,SPONSORSHIPID)
                select T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') as REVENUESPLITID,
                T.c.value('(SPONSORSHIPID)[1]','uniqueidentifier') as SPONSORSHIPID
                from @REVENUESPLITSPONSORSHIPID.nodes('REVENUESPLITSPONSORSHIPID/ITEM') T(c)  

                declare SOLICITORSCURSOR cursor local fast_forward for
                select REVENUESPLIT.ID, SOLICITORS
                from @SOLICITORSTABLE ST
                inner join dbo.REVENUESPLIT on 
                    ST.APPLICATIONCODE = REVENUESPLIT.APPLICATIONCODE and 
                    ST.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
                left join dbo.REVENUESPLITGIFTAID on 
                    REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID and 
                    ST.DECLINESGIFTAID = REVENUESPLITGIFTAID.DECLINESGIFTAID
                left join @REVENUESPLITSPONSORSHIPTABLE REVSPONSOR on
                    REVSPONSOR.REVENUESPLITID = REVENUESPLIT.ID
                where REVENUEID = @REVENUEID
            --WI 194341 when sponsorship additional donation, limit records to below criteria

                and coalesce(ST.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(REVSPONSOR.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000')
                and (REVSPONSOR.REVENUESPLITID is null or REVSPONSOR.REVENUESPLITID = REVENUESPLIT.ID)            

                open SOLICITORSCURSOR

                fetch next from SOLICITORSCURSOR into @REVENUESPLITID, @SOLICITORS
                while @@FETCH_STATUS = 0
                begin
                    set @SOLICITORS = dbo.UFN_REVENUESOLICITOR_CONVERTAMOUNTSINXML(@SOLICITORS, @BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID);
                    exec dbo.USP_REVENUE_GETSOLICITORS_2_ADDFROMXML @REVENUESPLITID, @SOLICITORS, @CHANGEAGENTID, @CHANGEDATE;

          fetch next from SOLICITORSCURSOR into @REVENUESPLITID, @SOLICITORS
        end

        close SOLICITORSCURSOR    
                deallocate SOLICITORSCURSOR
            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch

            return 0;