USP_REVENUEUPDATEBATCH_RECOGNITIONS_CREATECONSTITUENTS

            create procedure dbo.USP_REVENUEUPDATEBATCH_RECOGNITIONS_CREATECONSTITUENTS
            (
                @RECOGNITIONSXML xml output,
                @CHANGEAGENTID uniqueidentifier
            )
            as
            begin
                declare @RECOGNITION table
                (
                    ID uniqueidentifier,
                    CONSTITUENTID uniqueidentifier,
                    AMOUNT money,
                    EFFECTIVEDATE datetime,
                    REVENUERECOGNITIONTYPECODEID uniqueidentifier,
                    DESIGNATIONID uniqueidentifier,
                    RECOGNITIONCREDITFKID uniqueidentifier, 
                    DONORCHALLENGERECOGNITIONTYPECODE tinyint
                )

                insert into @RECOGNITION
                (
                    ID,
                    CONSTITUENTID,
                    AMOUNT,
                    EFFECTIVEDATE,
                    REVENUERECOGNITIONTYPECODEID,
                    DESIGNATIONID,
                    RECOGNITIONCREDITFKID,
                    DONORCHALLENGERECOGNITIONTYPECODE
                )
                select
                    ID,
                    CONSTITUENTID,
                    AMOUNT,
                    EFFECTIVEDATE,
                    REVENUERECOGNITIONTYPECODEID,
                    DESIGNATIONID,
                    RECOGNITIONCREDITFKID,
                    DONORCHALLENGERECOGNITIONTYPECODE
                from dbo.UFN_REVENUEUPDATEBATCH_GETRECOGNITIONSFOREDIT_FROMITEMLISTXML(@RECOGNITIONSXML)

                declare CONSTITUENTSTOCREATECURSOR cursor local fast_forward for
                select distinct CONSTITUENTID
                from @RECOGNITION
                where
                    CONSTITUENTID not in (select ID from dbo.CONSTITUENT)

                open CONSTITUENTSTOCREATECURSOR

                declare @BATCHREVENUECONSTITUENTID uniqueidentifier
                fetch next from CONSTITUENTSTOCREATECURSOR into @BATCHREVENUECONSTITUENTID

                while @@FETCH_STATUS = 0
                begin
                    declare @CONSTITUENTID uniqueidentifier = null
                    exec USP_REVENUEBATCH_CONSTITUENT_ADD @CONSTITUENTID output, @CHANGEAGENTID, @BATCHREVENUECONSTITUENTID

                    update @RECOGNITION set
                        CONSTITUENTID = @CONSTITUENTID
                    where CONSTITUENTID = @BATCHREVENUECONSTITUENTID

                    exec dbo.USP_REVENUEBATCH_CONSTITUENT_DELETE @BATCHREVENUECONSTITUENTID, @CHANGEAGENTID

                    fetch next from CONSTITUENTSTOCREATECURSOR into @BATCHREVENUECONSTITUENTID
                end

                close CONSTITUENTSTOCREATECURSOR
                deallocate CONSTITUENTSTOCREATECURSOR

                set @RECOGNITIONSXML = (
                    select 
                        ID,
                        CONSTITUENTID,
                        AMOUNT,
                        EFFECTIVEDATE,
                        REVENUERECOGNITIONTYPECODEID,
                        DESIGNATIONID,
                        RECOGNITIONCREDITFKID,
                        DONORCHALLENGERECOGNITIONTYPECODE
                    from @RECOGNITION
                    for xml raw('ITEM'),type,elements,root('RECOGNITIONS'),binary base64)
            end