USP_REVENUEBATCH_ADDAPPLICATIONRECOGNITIONS

            CREATE procedure dbo.USP_REVENUEBATCH_ADDAPPLICATIONRECOGNITIONS
            (
                @REVENUEID uniqueidentifier,
                @APPLICATIONRECOGNITIONS xml,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime,
                @REVENUESPLITSPONSORSHIPID xml = null,
                @NEWAPPLICATIONPLEDGEID uniqueidentifier = 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 @RECOGNITIONS xml
                declare @ID uniqueidentifier;
                declare @CONSTITUENTID uniqueidentifier;
                declare @AMOUNT money;
                declare @EFFECTIVEDATE datetime;
                declare @REVENUERECOGNITIONTYPECODEID uniqueidentifier;
                declare @BATCHCONSTITUENTID uniqueidentifier
                declare @RECOGNITIONSTABLE table
                (
                    APPLICATIONID uniqueidentifier,
                    AMOUNT money,
                    APPLICATIONCODE tinyint,
                    DESIGNATIONID uniqueidentifier,
                    RECOGNITIONS xml,
                DECLINESGIFTAID bit,
                    SPONSORSHIPID uniqueidentifier
                )
                declare @RECOGNITION table
                (
                    ID uniqueidentifier,
                    CONSTITUENTID uniqueidentifier,
                    AMOUNT money,
                    EFFECTIVEDATE datetime,
                    REVENUERECOGNITIONTYPECODEID uniqueidentifier
                )
                declare @REVENUESPLITSPONSORSHIPTABLE table
                (
                     REVENUESPLITID uniqueidentifier,
                     SPONSORSHIPID uniqueidentifier
                )

                --Table to keep track of constituents which are added.

                declare @CONSTITUENTMAPPINGTABLE table
                (
                     OLDCONSTITUENTID uniqueidentifier,
                     NEWCONSTITUENTID uniqueidentifier
                )

                 --Table to keep track of recognition IDs which are added.

                declare @REVENUERECOGNITIONMAPPINGTABLE table
                (
                     OLDRECOGNITIONID uniqueidentifier
                )

                insert into @RECOGNITIONSTABLE(APPLICATIONID, AMOUNT, APPLICATIONCODE, DESIGNATIONID, RECOGNITIONS, DECLINESGIFTAID, SPONSORSHIPID)
                select T.c.value('(APPLICATIONID)[1]','uniqueidentifier') as APPLICATIONID,
                T.c.value('(APPLICATIONAMOUNT)[1]','money') as AMOUNT,
                    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('./RECOGNITIONS/ITEM') = 1 then T.c.query('(RECOGNITIONS)[1]') else null end as RECOGNITIONS,
            T.c.value('(ADDITIONALAPPLICATIONDECLINESGIFTAID)[1]','bit') as DECLINESGIFTAID,
                T.c.value('(ADDITIONALAPPLICATIONSPONSORSHIPID)[1]','uniqueidentifier') as SPONSORSHIPID
                from @APPLICATIONRECOGNITIONS.nodes('/APPLICATIONRECOGNITIONS/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 RECOGNITIONSCURSOR cursor local fast_forward for
                select REVENUESPLIT.ID, RECOGNITIONS
                from @RECOGNITIONSTABLE RT
                inner join dbo.REVENUESPLIT on 
                RT.APPLICATIONCODE = REVENUESPLIT.APPLICATIONCODE and
                RT.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
                left join dbo.REVENUESPLITGIFTAID on 
                    REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID and 
                    RT.DECLINESGIFTAID = REVENUESPLITGIFTAID.DECLINESGIFTAID
                left join @REVENUESPLITSPONSORSHIPTABLE REVSPONSOR on
                        REVSPONSOR.REVENUESPLITID = REVENUESPLIT.ID
                left join (select distinct PLEDGEID,PAYMENTID from INSTALLMENTSPLITPAYMENT) ISP  on
                        REVENUESPLIT.ID = ISP.PAYMENTID
                where REVENUEID = @REVENUEID
                        --WI 194341 when sponsorship additional donation, limit records to below criteria

                and coalesce(RT.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(REVSPONSOR.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000')
                and (REVSPONSOR.REVENUESPLITID is null or REVSPONSOR.REVENUESPLITID = REVENUESPLIT.ID)
                and ISP.PAYMENTID is null -- Remove any splits that are for commitments since we only want to add additional application recognitions


                union all

                select REVENUESPLIT.ID, RT.RECOGNITIONS
                from @RECOGNITIONSTABLE RT
                inner join (select distinct PLEDGEID,PAYMENTID from INSTALLMENTSPLITPAYMENT) ISP
                        on RT.APPLICATIONID = ISP.PLEDGEID
                inner join REVENUESPLIT 
                        on REVENUESPLIT.ID = ISP.PAYMENTID
                where REVENUESPLIT.REVENUEID = @REVENUEID

                union all

                select REVENUESPLIT.ID, RT.RECOGNITIONS
                from @RECOGNITIONSTABLE RT 
                inner join dbo.RECURRINGGIFTACTIVITY RGA 
                        on RGA.SOURCEREVENUEID = RT.APPLICATIONID
                inner join dbo.REVENUESPLIT 
                        on REVENUESPLIT.ID = RGA.PAYMENTREVENUEID 
                where REVENUESPLIT.REVENUEID = @REVENUEID

                union all

                select REVENUESPLIT.ID, RT.RECOGNITIONS
                from @RECOGNITIONSTABLE RT
                inner join dbo.EVENTREGISTRANTPAYMENT ERP 
                        on ERP.REGISTRANTID = RT.APPLICATIONID
                inner join dbo.REVENUESPLIT 
                        on REVENUESPLIT.ID = ERP.PAYMENTID 
                where REVENUESPLIT.REVENUEID = @REVENUEID

                union all

                select
                    REVENUESPLIT.ID,
                    RT.RECOGNITIONS
                from
                    dbo.REVENUESPLIT
                inner join
                 (select distinct PLEDGEID,PAYMENTID from INSTALLMENTSPLITPAYMENT) ISP on ISP.PAYMENTID = REVENUESPLIT.ID
                cross apply
                    @RECOGNITIONSTABLE as RT
                where
                    RT.APPLICATIONID = '9B9C1DC8-7960-4D31-A0BC-8199AB7F94DA' and
                    REVENUESPLIT.REVENUEID = @REVENUEID and
                    ISP.PLEDGEID = @NEWAPPLICATIONPLEDGEID;

                open RECOGNITIONSCURSOR

                fetch next from RECOGNITIONSCURSOR into @REVENUESPLITID, @RECOGNITIONS
                while @@FETCH_STATUS = 0
                begin
                    insert into @RECOGNITION(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 RECCURSOR cursor local dynamic for
                    select ID,CONSTITUENTID, AMOUNT, EFFECTIVEDATE, REVENUERECOGNITIONTYPECODEID
                    from @RECOGNITION   

                    open RECCURSOR

                    fetch next from RECCURSOR into @ID,@CONSTITUENTID, @AMOUNT, @EFFECTIVEDATE, @REVENUERECOGNITIONTYPECODEID;
                    while @@FETCH_STATUS = 0
                    begin
                    --Update constituents

                        if not exists(select ID from dbo.CONSTITUENT where ID = @CONSTITUENTID)
                        begin
                        --Constituent is not added already.

                        if(not exists(select OLDCONSTITUENTID from @CONSTITUENTMAPPINGTABLE where OLDCONSTITUENTID = @CONSTITUENTID))
                         begin
                            set @BATCHCONSTITUENTID = @CONSTITUENTID;
                            set @CONSTITUENTID = newid();
                            exec USP_REVENUEBATCH_CONSTITUENT_ADD @CONSTITUENTID OUTPUT, @CHANGEAGENTID, @BATCHCONSTITUENTID;
                            update @RECOGNITION set CONSTITUENTID = @CONSTITUENTID where CONSTITUENTID = @BATCHCONSTITUENTID;
                            exec dbo.USP_REVENUEBATCH_CONSTITUENT_DELETE @BATCHREVENUECONSTITUENTID = @BATCHCONSTITUENTID, @CHANGEAGENTID = @CHANGEAGENTID

                            --Insert a record to suggest that constituent is added.

                            insert into @CONSTITUENTMAPPINGTABLE(OLDCONSTITUENTID,NEWCONSTITUENTID) values(@BATCHCONSTITUENTID,@CONSTITUENTID)
                         end
                        else
                         begin
                            declare @NEWCONSTITUENTID uniqueidentifier
                            select @NEWCONSTITUENTID = NEWCONSTITUENTID from @CONSTITUENTMAPPINGTABLE where OLDCONSTITUENTID = @CONSTITUENTID
                            update @RECOGNITION set CONSTITUENTID = @NEWCONSTITUENTID where CONSTITUENTID = @CONSTITUENTID;
                         end
                        end

                         --Update recognition IDs

                         if exists(select OLDRECOGNITIONID from @REVENUERECOGNITIONMAPPINGTABLE where OLDRECOGNITIONID = @ID)
                         begin
                                update @RECOGNITION set ID = NEWID() where ID = @ID;
                         end
                         else
                         begin
                            --Insert a record to suggest that recognition ID is added.

                                insert into @REVENUERECOGNITIONMAPPINGTABLE(OLDRECOGNITIONID) values(@ID)
                         end
                        fetch next from RECCURSOR into @ID,@CONSTITUENTID, @AMOUNT, @EFFECTIVEDATE, @REVENUERECOGNITIONTYPECODEID;
                    end

                    close RECCURSOR;
                    deallocate RECCURSOR;

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

                    declare @TOTALSPLITAMOUNT 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

                    declare @REVENUEAPPLICATIONID uniqueidentifier = (select FTLI.FINANCIALTRANSACTIONID from FINANCIALTRANSACTIONLINEITEM SPLIT 
                    inner join FINANCIALTRANSACTIONLINEITEM FTLI with (nolock) on SPLIT.SOURCELINEITEMID = FTLI.ID
                    where SPLIT.ID = @REVENUESPLITID);

                    set @TOTALSPLITAMOUNT = (select AMOUNT from @RECOGNITIONSTABLE where APPLICATIONID = @REVENUEAPPLICATIONID);

                    set @SPLITAMOUNT = (select AMOUNT from dbo.REVENUESPLIT where ID = @REVENUESPLITID);

                    --If there are no splits. We do not need to split amount and use default @RECOGNITION.

                    if @TOTALSPLITAMOUNT is null
                    begin
                        --deleting the previous recognition.

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

                        -- Process the recognitions xml to calculate organization amounts.

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

                        exec dbo.USP_REVENUE_GETRECOGNITIONS_2_ADDFROMXML @REVENUESPLITID, @RECOGNITIONS, @CHANGEAGENTID, @CHANGEDATE;
                    end
                    else
                    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(@TOTALSPLITAMOUNT, @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);

                        --deleting the previous recognition.

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

                        -- Process the recognitions xml to calculate organization amounts.

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

                        exec dbo.USP_REVENUE_GETRECOGNITIONS_2_ADDFROMXML @REVENUESPLITID, @SPLITRECOGNITIONS, @CHANGEAGENTID, @CHANGEDATE;
                    end;
                    delete from @RECOGNITION; 

                    fetch next from RECOGNITIONSCURSOR into @REVENUESPLITID, @RECOGNITIONS
                end

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

            return 0;