USP_REVENUEBATCH_ADDRECOGNITIONS

            CREATE procedure dbo.USP_REVENUEBATCH_ADDRECOGNITIONS
            (
                @REVENUEID uniqueidentifier,
                @RECOGNITIONS xml,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime
            )
            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 @REC table
                (
                    ID uniqueidentifier,
                    CONSTITUENTID uniqueidentifier,
                    AMOUNT money,
                    EFFECTIVEDATE datetime,
                    REVENUERECOGNITIONTYPECODEID uniqueidentifier
                );

                insert into @REC(ID, CONSTITUENTID, AMOUNT, EFFECTIVEDATE, REVENUERECOGNITIONTYPECODEID)
                    select
                        T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                        T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') AS 'CONSTITUENTID',
                        T.c.value('(AMOUNT)[1]','money') AS 'AMOUNT',
                        T.c.value('(EFFECTIVEDATE)[1]','datetime') AS 'EFFECTIVEDATE',
                        T.c.value('(REVENUERECOGNITIONTYPECODEID)[1]','uniqueidentifier') AS 'REVENUERECOGNITIONTYPECODEID'
                    from @RECOGNITIONS.nodes('/RECOGNITIONS/ITEM') T(c);

                declare REC_CURSOR cursor local dynamic for --The cursor must be dynamic because the data in @REC will be updated as constituents are created

                    select CONSTITUENTID, AMOUNT, EFFECTIVEDATE, REVENUERECOGNITIONTYPECODEID from @REC;

                open REC_CURSOR;

                declare @CONSTITUENTID uniqueidentifier;
                declare @AMOUNT money;
                declare @EFFECTIVEDATE datetime;
                declare @REVENUERECOGNITIONTYPECODEID uniqueidentifier;

                declare @BATCHCONSTITUENTID uniqueidentifier;

                fetch next from REC_CURSOR into @CONSTITUENTID, @AMOUNT, @EFFECTIVEDATE, @REVENUERECOGNITIONTYPECODEID;
                while @@FETCH_STATUS = 0
                begin
                    --Create the constituent if necessary

                    if not exists(select ID from dbo.CONSTITUENT where ID = @CONSTITUENTID)
                    begin
                        set @BATCHCONSTITUENTID = @CONSTITUENTID;
                        set @CONSTITUENTID = newid();
                        exec USP_REVENUEBATCH_CONSTITUENT_ADD @CONSTITUENTID OUTPUT, @CHANGEAGENTID, @BATCHCONSTITUENTID;    

                        update @REC 
                            set CONSTITUENTID = @CONSTITUENTID
                        where CONSTITUENTID = @BATCHCONSTITUENTID;

                        exec dbo.USP_REVENUEBATCH_CONSTITUENT_DELETE @BATCHREVENUECONSTITUENTID = @BATCHCONSTITUENTID, @CHANGEAGENTID = @CHANGEAGENTID
                    end

                    fetch next from REC_CURSOR into @CONSTITUENTID, @AMOUNT, @EFFECTIVEDATE, @REVENUERECOGNITIONTYPECODEID;    
                end

                close REC_CURSOR;
                deallocate REC_CURSOR;

                set @RECOGNITIONS = 
                (
                    select
                        ID, CONSTITUENTID, AMOUNT, EFFECTIVEDATE, REVENUERECOGNITIONTYPECODEID
                    from @REC 
                    order by EFFECTIVEDATE desc
                    for xml raw('ITEM'),type,elements,root('RECOGNITIONS'),binary base64
                );

                declare @REVENUESPLITID uniqueidentifier, @SPLITCOUNT int
                select @SPLITCOUNT = count(*) from dbo.REVENUESPLIT
                where 
                    REVENUEID = @REVENUEID

                -- Determine if there are multiple splits for this revenue

                if @SPLITCOUNT > 1
                begin
                    declare @FULLAMOUNT money, @SPLITAMOUNT money, @SPLITRECOGNITIONS xml

                    -- FULLAMOUNT should only include the splits that will have recognition credits created in this procedure

                    -- since it is being used to calculate prorated amounts

                    select @FULLAMOUNT = sum(AMOUNT) from dbo.REVENUESPLIT
                        where 
                            REVENUEID = @REVENUEID 

                    -- Loop through splits

                    declare SPLIT_CURSOR cursor local fast_forward for
                        select ID, AMOUNT from dbo.REVENUESPLIT
                        where 
                            REVENUEID = @REVENUEID 
                    open SPLIT_CURSOR

                    fetch next from SPLIT_CURSOR into @REVENUESPLITID, @SPLITAMOUNT
                    while @@FETCH_STATUS = 0
                    begin
                        -- Calculate the prorated amount per split

                        set @SPLITRECOGNITIONS = (select
                                ID, 
                                AMOUNT 
                            from dbo.UFN_REVENUE_GETRECOGNITIONS_FROMITEMLISTXML(@RECOGNITIONS)
                            for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64)

                        declare @BASEDECIMALDIGITS tinyint;
                        select @BASEDECIMALDIGITS = DECIMALDIGITS from dbo.CURRENCY where CURRENCY.ID = @BASECURRENCYID;

                        set @SPLITRECOGNITIONS = (select
                                newid() as ID,
                                R.REVENUERECOGNITIONTYPECODEID, 
                                R.CONSTITUENTID, 
                                R.EFFECTIVEDATE, 
                                PRS.AMOUNT
                            from dbo.UFN_SPLITS_PRORATEAMOUNTS(@FULLAMOUNT, @SPLITAMOUNT, @BASEDECIMALDIGITS, @SPLITRECOGNITIONS) PRS
                            cross apply dbo.UFN_REVENUE_GETRECOGNITIONS_FROMITEMLISTXML(@RECOGNITIONS) R
                            where PRS.ID = R.ID
                            for xml raw('ITEM'),type,elements,root('RECOGNITIONS'),binary base64)

                            -- Process the recognitions xml to calculate organization amounts.

                            set @SPLITRECOGNITIONS = dbo.UFN_REVENUERECOGNITION_CONVERTAMOUNTSINXML(@SPLITRECOGNITIONS,@BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID);

                            --deleting the previous recognition. 

                            delete REVENUERECOGNITION from dbo.REVENUERECOGNITION where REVENUERECOGNITION.REVENUESPLITID = @REVENUESPLITID

                            exec dbo.USP_REVENUE_GETRECOGNITIONS_2_ADDFROMXML @REVENUESPLITID, @SPLITRECOGNITIONS, @CHANGEAGENTID, @CHANGEDATE;

                        fetch next from SPLIT_CURSOR into @REVENUESPLITID, @SPLITAMOUNT
                    end

                    close SPLIT_CURSOR    
                    deallocate SPLIT_CURSOR    
                end
                else
                if @SPLITCOUNT = 1 
                    begin
                        select top 1 @REVENUESPLITID = ID from dbo.REVENUESPLIT
                        where 
                            REVENUEID = @REVENUEID 
                             -- Process the recognitions xml to calculate organization amounts.

                            set @RECOGNITIONS = dbo.UFN_REVENUERECOGNITION_CONVERTAMOUNTSINXML(@RECOGNITIONS,@BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID);

                            --deleting the previous recognition. 

                            delete REVENUERECOGNITION from dbo.REVENUERECOGNITION where REVENUERECOGNITION.REVENUESPLITID = @REVENUESPLITID

                            exec dbo.USP_REVENUE_GETRECOGNITIONS_2_ADDFROMXML @REVENUESPLITID, @RECOGNITIONS, @CHANGEAGENTID, @CHANGEDATE;

                    end
            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch

            return 0;