USP_RECONCILEMATCHINGGIFT_REVENUESTREAMS

            CREATE procedure dbo.USP_RECONCILEMATCHINGGIFT_REVENUESTREAMS
            (
                @REVENUESPLITID uniqueidentifier,
                @REVENUESPLITAMOUNT money,
                @REVENUESTREAMS xml,
                @RELATIONREVENUESTREAM xml,
                @CONSTITUENTID uniqueidentifier, 
                @DATE datetime, 
                @PAYMENTMETHODCODE tinyint, -- No longer used. Preserved for backwards compatibility.

                @BATCHNUMBER nvarchar(100), -- No longer used. Preserved for backwards compatibility. 

                @POSTDATE datetime, 
                @POSTSTATUSCODE tinyint, -- No longer used. Preserved for backwards compatibility. 

                @DONOTRECEIPT bit, -- No longer used. Preserved for backwards compatibility.

                @DONOTACKNOWLEDGE bit,  -- No longer used. Preserved for backwards compatibility.

                @FINDERNUMBER bigint = 0,  -- No longer used. Preserved for backwards compatibility.

                @SOURCECODE nvarchar(50) = null, -- No longer used. Preserved for backwards compatibility.

                @APPEALID uniqueidentifier = null, -- No longer used. Preserved for backwards compatibility.            

                @MAILINGID uniqueidentifier = null, -- No longer used. Preserved for backwards compatibility.

                @CHANNELCODEID uniqueidentifier = null, -- No longer used. Preserved for backwards compatibility.

                @CHECKDATE dbo.UDT_FUZZYDATE = '00000000', -- No longer used. Preserved for backwards compatibility.

                @CHECKNUMBER nvarchar(20) = '', -- No longer used. Preserved for backwards compatibility.

                @CONSTITUENTACCOUNTID uniqueidentifier = null, -- No longer used. Preserved for backwards compatibility.

                @REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000', -- No longer used. Preserved for backwards compatibility.

                @REFERENCENUMBER nvarchar(20) = '', -- No longer used. Preserved for backwards compatibility.

                @CARDHOLDERNAME nvarchar(255) = '', -- No longer used. Preserved for backwards compatibility.

                @CREDITCARDNUMBER nvarchar(4) = '', -- No longer used. Preserved for backwards compatibility.

                @CREDITTYPECODEID uniqueidentifier = null, -- No longer used. Preserved for backwards compatibility.

                @AUTHORIZATIONCODE nvarchar(20) = '', -- No longer used. Preserved for backwards compatibility.

                @EXPIRESON dbo.UDT_FUZZYDATE = '00000000', -- No longer used. Preserved for backwards compatibility.

                @ISSUER nvarchar(100) = '', -- No longer used. Preserved for backwards compatibility.

                @NUMBEROFUNITS decimal(20,3) = 0, -- No longer used. Preserved for backwards compatibility.

                @SYMBOL nvarchar(25) = '', -- No longer used. Preserved for backwards compatibility.

                @MEDIANPRICE decimal(19,4) = 0, -- No longer used. Preserved for backwards compatibility.

                @SALEDATE datetime = null, -- No longer used. Preserved for backwards compatibility.

                @SALEAMOUNT money = null, -- No longer used. Preserved for backwards compatibility.

                @BROKERFEE money = null, -- No longer used. Preserved for backwards compatibility.

                @SALEPOSTSTATUSCODE tinyint = null, -- No longer used. Preserved for backwards compatibility.

                @SALEPOSTDATE datetime = null, -- No longer used. Preserved for backwards compatibility.

                @PROPERTYSUBTYPECODEID uniqueidentifier = null, -- No longer used. Preserved for backwards compatibility.

                @GIFTINKINDSUBTYPECODEID uniqueidentifier = null, -- No longer used. Preserved for backwards compatibility.

                @CHANGEAGENTID uniqueidentifier,
                @CREATIONDATE datetime ,
                @TOTALAMOUNTAPPLIED money output,
                @KEYALREADYOPEN bit = 0 -- No longer used. Preserved for backwards compatibility.

            )
            as 
            set nocount on;

            begin try
                -- Holds the designations and amounts being paid

                declare @DESIGNATIONSPAID table
                (
                    INSTALLMENTSPLITPAYMENTID uniqueidentifier not null,
                    DESIGNATIONID uniqueidentifier not null,
                    REVENUESPLITID uniqueidentifier not null,
                    AMOUNT money not null,
                    APPLICATIONID uniqueidentifier not null,
                    MGCLAIMREVENUEID uniqueidentifier -- added for use when creating RC for MGC

                )

                declare @INSTALLMENTSPLITPAYMENTID uniqueidentifier;

                declare @AMOUNTCONVERTED money;
                declare @PAYMENTDESIGNATIONID uniqueidentifier, @REVENUEID uniqueidentifier, @PAYMENTDATE date;
                declare @PAYMENTTRANSACTIONCURRENCYID uniqueidentifier, @PAYMENTBASECURRENCYID uniqueidentifier, @PAYMENTBASEEXCHANGERATEID uniqueidentifier, @PAYMENTORGANIZATIONEXCHANGERATEID uniqueidentifier;
                declare @PAYMENTAPPLICATIONCODE tinyint, @PAYMENTTYPECODE tinyint;
                select
                    @PAYMENTDESIGNATIONID = REVENUESPLIT.DESIGNATIONID,
                    @REVENUEID = REVENUESPLIT.REVENUEID,
                    @PAYMENTTRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
                    @PAYMENTBASECURRENCYID = REVENUE.BASECURRENCYID,
                    @PAYMENTBASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,
                    @PAYMENTDATE = REVENUE.DATE,
                    @PAYMENTORGANIZATIONEXCHANGERATEID = REVENUESPLIT.ORGANIZATIONEXCHANGERATEID,
                    @PAYMENTAPPLICATIONCODE = REVENUESPLIT.APPLICATIONCODE,
                    @PAYMENTTYPECODE = REVENUESPLIT.TYPECODE
                from dbo.REVENUESPLIT
                inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
                where REVENUESPLIT.ID = @REVENUESPLITID;

                declare APPLICATIONSCURSOR cursor local fast_forward for
                    select
                        REVENUESPLITID,
                        APPLIED,
                        TYPECODE,
                        DESIGNATIONID
                    from dbo.UFN_RECONCILE_GETAPPLICATIONWITHCHILDREN_FROMITEMLISTXML(@REVENUESTREAMS)
                    where APPLIED > 0;

                declare @APPLICATIONID uniqueidentifier;
                declare @APPLICATIONTYPE tinyint;
                declare @APPLIEDAMOUNT money;
                declare @APPLICATIONDESIGNATIONID uniqueidentifier;
                declare @APPLICATIONEXCHANGERATEID uniqueidentifier;

                open APPLICATIONSCURSOR;
                fetch next from APPLICATIONSCURSOR into @APPLICATIONID, @APPLIEDAMOUNT, @APPLICATIONTYPE, @APPLICATIONDESIGNATIONID;

                while @@FETCH_STATUS = 0 
                begin
                    declare @MGCLAIMREVENUEID uniqueidentifier, @MGCLAIMDESIGNATIONID uniqueidentifier, @MGCLAIMAMOUNT money, @MGCLAIMTRANSACTIONCURRENCYID uniqueidentifier;
                    select
                        @MGCLAIMREVENUEID = REVENUEID,
                        @MGCLAIMDESIGNATIONID = DESIGNATIONID,
                        @MGCLAIMAMOUNT = TRANSACTIONAMOUNT,
                        @MGCLAIMTRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID
                    from dbo.REVENUESPLIT
                    where ID = @APPLICATIONID;

                    if @MGCLAIMTRANSACTIONCURRENCYID <> @PAYMENTTRANSACTIONCURRENCYID
                    begin
                        if @MGCLAIMTRANSACTIONCURRENCYID = @PAYMENTBASECURRENCYID
                            set @APPLICATIONEXCHANGERATEID = @PAYMENTBASEEXCHANGERATEID;
                        else
                            set @APPLICATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@PAYMENTTRANSACTIONCURRENCYID,@MGCLAIMTRANSACTIONCURRENCYID,@PAYMENTDATE,1,null);

                        if @APPLICATIONEXCHANGERATEID is null
                        begin
                            raiserror('BBERR_APPLICATIONEXCHANGERATEDOESNOTEXIST', 13, 1);
                            return 1;
                        end
                    end

                    if @MGCLAIMTRANSACTIONCURRENCYID = @PAYMENTTRANSACTIONCURRENCYID
                        set @AMOUNTCONVERTED = @APPLIEDAMOUNT;
                    else
                        set @AMOUNTCONVERTED = dbo.UFN_CURRENCY_CONVERT(@APPLIEDAMOUNT, @APPLICATIONEXCHANGERATEID);


                    -- Update the matching gift claim split and installments to point to the new designation if it's different

                    declare @MGCLAIMINSTALLMENTSPLITID uniqueidentifier;
                    declare @MGCLAIMREVENUESPLITID uniqueidentifier = @APPLICATIONID;
                    if @MGCLAIMDESIGNATIONID <> @APPLICATIONDESIGNATIONID
                    begin
                        --Bug 129338 - AdamBu - 12/20/10 - Get the revenue split ID output value from the SP because it may

                        --    have created a new split that should be the payment's application.


                        exec dbo.USP_RECONCILEMATCHINGGIFT_UPDATECLAIMSPLITS 
                            @MGCLAIMINSTALLMENTSPLITID = @MGCLAIMINSTALLMENTSPLITID output,
                            @APPLICATIONID = @APPLICATIONID,
                            @MGCLAIMREVENUEID = @MGCLAIMREVENUEID,
                            @MGCLAIMDESIGNATIONID = @MGCLAIMDESIGNATIONID,
                            @MGCLAIMAMOUNT = @MGCLAIMAMOUNT,
                            @APPLIEDAMOUNT = @AMOUNTCONVERTED,
                            @APPLICATIONDESIGNATIONID = @APPLICATIONDESIGNATIONID,
                            @CHANGEAGENTID = @CHANGEAGENTID,
                            @CREATIONDATE = @CREATIONDATE,
                            @MGCLAIMREVENUESPLITID = @MGCLAIMREVENUESPLITID output;
                    end
                    else
                    begin
                        select @MGCLAIMINSTALLMENTSPLITID = ID
                        from dbo.INSTALLMENTSPLIT
                        where
                            PLEDGEID = @MGCLAIMREVENUEID and
                            DESIGNATIONID = @MGCLAIMDESIGNATIONID
                    end

                    -- Determine amount left to pay for the claim.  If the amount applied is greater than that, set that amount to the amount left to pay.

                    declare @AMOUNTLEFTONCLAIM money
                    set @AMOUNTLEFTONCLAIM = dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(@MGCLAIMINSTALLMENTSPLITID)

                    if @AMOUNTCONVERTED > @AMOUNTLEFTONCLAIM
                        set @AMOUNTCONVERTED = @AMOUNTLEFTONCLAIM

                    if @AMOUNTCONVERTED > 0
                    begin
                        set @INSTALLMENTSPLITPAYMENTID = newid()
                        insert into dbo.INSTALLMENTSPLITPAYMENT (ID, PAYMENTID, PLEDGEID, INSTALLMENTSPLITID, AMOUNT, APPLICATIONCURRENCYID, APPLICATIONEXCHANGERATEID, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                            values (@INSTALLMENTSPLITPAYMENTID, @REVENUESPLITID, @MGCLAIMREVENUEID, @MGCLAIMINSTALLMENTSPLITID, @AMOUNTCONVERTED, @MGCLAIMTRANSACTIONCURRENCYID, @APPLICATIONEXCHANGERATEID, @CREATIONDATE, @CREATIONDATE, @CHANGEAGENTID, @CHANGEAGENTID)

                        insert into @DESIGNATIONSPAID (INSTALLMENTSPLITPAYMENTID, DESIGNATIONID, AMOUNT, REVENUESPLITID, APPLICATIONID, MGCLAIMREVENUEID)
                            values (@INSTALLMENTSPLITPAYMENTID, @APPLICATIONDESIGNATIONID, @APPLIEDAMOUNT, @REVENUESPLITID, @MGCLAIMREVENUESPLITID, @MGCLAIMREVENUEID)
                    end

                    fetch next from APPLICATIONSCURSOR into @APPLICATIONID, @APPLIEDAMOUNT, @APPLICATIONTYPE, @APPLICATIONDESIGNATIONID;
                end
                --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

                close APPLICATIONSCURSOR
                deallocate APPLICATIONSCURSOR

                declare    RELATIONAPPLICATIONSCURSOR cursor local fast_forward for
                    select
                        REVENUESPLITID,
                        APPLIED,
                        TYPECODE,
                        DESIGNATIONID
                    from dbo.UFN_RECONCILERELATION_GETAPPLICATIONWITHCHILDREN_FROMITEMLISTXML(@RELATIONREVENUESTREAM)
                    where APPLIED > 0;

                open RELATIONAPPLICATIONSCURSOR;

                fetch next from RELATIONAPPLICATIONSCURSOR into @APPLICATIONID, @APPLIEDAMOUNT, @APPLICATIONTYPE, @APPLICATIONDESIGNATIONID;

                while @@FETCH_STATUS = 0 
                begin
                    exec dbo.USP_RECONCILEMATCHINGGIFT_GENERATEANDPAYCLAIM @INSTALLMENTSPLITPAYMENTID = @INSTALLMENTSPLITPAYMENTID output,
                        @REVENUESPLITID = @REVENUESPLITID,
                        @APPLICATIONID = @APPLICATIONID,
                        @CONSTITUENTID = @CONSTITUENTID,
                        @APPLICATIONDESIGNATIONID = @APPLICATIONDESIGNATIONID,
                        @APPLIEDAMOUNT = @APPLIEDAMOUNT,
                        @MGCLAIMINSTALLMENTSPLITID = @MGCLAIMINSTALLMENTSPLITID,
                        @DATE = @DATE,
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @CREATIONDATE = @CREATIONDATE;

                    declare @MGCLAIMREVENUEID_REL uniqueidentifier;
                    select @MGCLAIMREVENUEID_REL = PLEDGEID
                      from dbo.INSTALLMENTSPLITPAYMENT
                     where ID = @INSTALLMENTSPLITPAYMENTID;

                    insert into @DESIGNATIONSPAID (INSTALLMENTSPLITPAYMENTID, DESIGNATIONID, AMOUNT, REVENUESPLITID, APPLICATIONID, MGCLAIMREVENUEID)
                        values (@INSTALLMENTSPLITPAYMENTID, @APPLICATIONDESIGNATIONID, @APPLIEDAMOUNT, @REVENUESPLITID, @APPLICATIONID, @MGCLAIMREVENUEID_REL);

                    fetch next from RELATIONAPPLICATIONSCURSOR into @APPLICATIONID, @APPLIEDAMOUNT, @APPLICATIONTYPE, @APPLICATIONDESIGNATIONID;
                end
                --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

                close RELATIONAPPLICATIONSCURSOR;
                deallocate RELATIONAPPLICATIONSCURSOR;

                -- Set the value of @TOTALAMOUNTAPPLIED since it's an output parameter

                select @TOTALAMOUNTAPPLIED = sum(AMOUNT)
                from @DESIGNATIONSPAID;

                -- Verify the total amount applied isn't greater than the unapplied split amount

                if @TOTALAMOUNTAPPLIED > @REVENUESPLITAMOUNT
                    raiserror('The total amount applied cannot be greater than the split amount.', 13, 1);

                declare @NEWDESIGNATIONEXISTS bit, @CLAIMSPAIDCOUNT int
                if exists (select 1 from @DESIGNATIONSPAID where DESIGNATIONID <> @PAYMENTDESIGNATIONID)
                    set @NEWDESIGNATIONEXISTS = 1
                else
                    set @NEWDESIGNATIONEXISTS = 0

                select @CLAIMSPAIDCOUNT = count(*) from @DESIGNATIONSPAID

                declare @REVENUESPLITDESIGNATIONCHANGED uniqueidentifier

                -- Check if the payment splits that apply to the claim need to be updated

                if @CLAIMSPAIDCOUNT > 0 and (@NEWDESIGNATIONEXISTS = 1 or @TOTALAMOUNTAPPLIED < @REVENUESPLITAMOUNT or @CLAIMSPAIDCOUNT > 1)
                begin    
                    declare @DESIGNATIONSPAIDXML xml;
                    set @DESIGNATIONSPAIDXML = (select  INSTALLMENTSPLITPAYMENTID,
                                                        DESIGNATIONID,
                                                        REVENUESPLITID,
                                                        AMOUNT, 
                                                        MGCLAIMREVENUEID as APPLICATIONID
                                                from @DESIGNATIONSPAID
                                                for xml raw('ITEM'),type,elements,root('DESIGNATIONSPAID'),binary base64);

                    exec dbo.USP_RECONCILEMATCHINGGIFT_UPDATEPAYMENTSPLITS @REVENUESPLITID = @REVENUESPLITID,
                        @TOTALAMOUNTAPPLIED = @TOTALAMOUNTAPPLIED,
                        @REVENUESPLITAMOUNT = @REVENUESPLITAMOUNT,
                        @REVENUEID = @REVENUEID,
                        @NEWDESIGNATIONEXISTS = @NEWDESIGNATIONEXISTS,
                        @CLAIMSPAIDCOUNT = @CLAIMSPAIDCOUNT,
                        @DESIGNATIONSPAIDXML = @DESIGNATIONSPAIDXML,
                        @PAYMENTDESIGNATIONID = @PAYMENTDESIGNATIONID,
                        @POSTDATE = @POSTDATE,
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @CREATIONDATE = @CREATIONDATE,
                        @REVENUESPLITDESIGNATIONCHANGED = @REVENUESPLITDESIGNATIONCHANGED output;
                end
                else
                begin
                    --If the payment splits are alright as is, handle auto-creating recognition credits on them.


                    declare @CREATEDSPLITS xml                
                    select @CREATEDSPLITS = 
                    (
                        select
                            PD.REVENUESPLITID as ID,
                            CURRENCYVALUES.BASEAMOUNT as AMOUNT,
              PD.DESIGNATIONID as DESIGNATIONID
                        from @DESIGNATIONSPAID PD
                        cross apply dbo.UFN_CURRENCY_GETCURRENCYVALUES_2(PD.AMOUNT,
                                                            null,
                                                            @PAYMENTBASECURRENCYID,
                                                            @PAYMENTBASEEXCHANGERATEID,
                                                            @PAYMENTTRANSACTIONCURRENCYID,
                                                            null,
                                                            null,
                                                            null,
                                                            @PAYMENTORGANIZATIONEXCHANGERATEID,
                                                            0) as CURRENCYVALUES
                        for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
                    );
                    -- added for when relation MGC is being reconciled.  

                    if @MGCLAIMREVENUEID is null
                    begin
                        select @MGCLAIMREVENUEID = MGCLAIMREVENUEID
                        from @DESIGNATIONSPAID;
                    end

                    declare @REVENUEGIVENANONYMOUSLY bit
                    select @REVENUEGIVENANONYMOUSLY =  REVENUE_EXT.GIVENANONYMOUSLY
                    from dbo.REVENUESPLIT
                    inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = REVENUESPLIT.REVENUEID
                    where REVENUESPLIT.ID = @REVENUESPLITID;

                    -- only create MGC recognitions based on Matching gift preferences for recognitions

                    exec dbo.USP_RECOGNITIONCREDITS_ADDBASEDONMGCPREFERENCES
                        @SPLITS = @CREATEDSPLITS,
                        @APPLICATIONID = @MGCLAIMREVENUEID,
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @CURRENTDATE = @CREATIONDATE,
                        @REVENUEGIVENANONYMOUSLY = @REVENUEGIVENANONYMOUSLY
                end

                -- If a split had its designation changed, remove its current campaigns.  Campaigns will be redefaulted in below.

                if @REVENUESPLITDESIGNATIONCHANGED is not null
                begin
                    declare @contextCache varbinary(128);

                    --cache current context information

                    set @contextCache = CONTEXT_INFO();

                    --set CONTEXT_INFO to @CHANGEAGENTID

                    set CONTEXT_INFO @CHANGEAGENTID;

                    delete from dbo.REVENUESPLITCAMPAIGN where REVENUESPLITID = @REVENUESPLITDESIGNATIONCHANGED

                    --reset CONTEXT_INFO to previous value

                    if not @contextCache is null
                        set CONTEXT_INFO @contextCache;
                end

                -- Copy campaigns from the claims to the payment.  This needs to be done last since the split used may change 

                -- after USP_RECONCILEMATCHINGGIFT_UPDATEPAYMENTSPLITS is called.

                insert into dbo.REVENUESPLITCAMPAIGN (REVENUESPLITID, CAMPAIGNID, CAMPAIGNSUBPRIORITYID, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                select INSTALLMENTSPLITPAYMENT.PAYMENTID, REVENUESPLITCAMPAIGN.CAMPAIGNID, REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID, @CREATIONDATE, @CREATIONDATE, @CHANGEAGENTID, @CHANGEAGENTID
                from @DESIGNATIONSPAID DESIGNATIONSPAID
                -- Join to INSTALLMENTSPLITPAYMENT to get the correct REVENUESPLITID.  The REVENUESPLITID in @DESIGNATIONSPAID

                -- may be incorrect because USP_RECONCILEMATCHINGGIFT_UPDATEPAYMENTSPLITS has run.

                inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.ID = DESIGNATIONSPAID.INSTALLMENTSPLITPAYMENTID
                inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = DESIGNATIONSPAID.APPLICATIONID
                inner join dbo.CAMPAIGN on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGN.ID
                where
                    CAMPAIGN.ISACTIVE = 1 and
                    -- Make sure the campaigns don't already exist on the payment

                    not exists (    select 1 
                                    from dbo.REVENUESPLITCAMPAIGN 
                                    where 
                                        REVENUESPLITID = INSTALLMENTSPLITPAYMENT.PAYMENTID and
                                        CAMPAIGNID = REVENUESPLITCAMPAIGN.CAMPAIGNID and
                                        (CAMPAIGNSUBPRIORITYID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID or
                                        (CAMPAIGNSUBPRIORITYID is null and REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID is null))
                                )
            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
            end catch