UFN_CHECKDETAIL_STREAMSCHANGED

            CREATE function dbo.UFN_CHECKDETAIL_STREAMSCHANGED(@TRANSACTIONID as uniqueidentifier, @REVENUESTREAMS as xml) 
            returns bit
            with execute as caller
            as
            begin
            declare @OLDREVENUESTREAMS table 
            (
                ID uniqueidentifier, 
                APPLICATIONID uniqueidentifier, 
                APPLIED money,
                APPLICATIONCODE tinyint,
                GIFTFIELDS xml,
                OTHERFIELDS xml,
                CATEGORYCODEID uniqueidentifier
            );

            declare @UPDATEDREVENUESTREAMS table 
            (
                ID uniqueidentifier, 
                APPLICATIONID uniqueidentifier, 
                APPLIED money,
                APPLICATIONCODE tinyint,
                GIFTFIELDS xml,
                OTHERFIELDS xml,
                CATEGORYCODEID uniqueidentifier
            );

            declare @STREAMCOUNT int
            declare @CHANGED as bit
            set @CHANGED = 0;

            insert into @OLDREVENUESTREAMS
                select ID, APPLICATIONID, APPLIED, APPLICATIONCODE, GIFTFIELDS,OTHERFIELDS,CATEGORYCODEID
                from dbo.UFN_REVENUE_GETAPPLICATIONS(@TRANSACTIONID);

            insert into @UPDATEDREVENUESTREAMS
                select ID, APPLICATIONID, APPLIED, APPLICATIONCODE, GIFTFIELDS,OTHERFIELDS,CATEGORYCODEID
                from dbo.UFN_REVENUE_GETAPPLICATIONS_FROMXML(@REVENUESTREAMS);

            select @STREAMCOUNT = count(ID) from @OLDREVENUESTREAMS;

            if @STREAMCOUNT <> (select count(*) from @UPDATEDREVENUESTREAMS) 
                set @CHANGED = 1;

            if @CHANGED = 0
            begin
                select @STREAMCOUNT = count([NEW].ID)
                from @UPDATEDREVENUESTREAMS as [NEW] 
                inner join @OLDREVENUESTREAMS as [OLD] on [OLD].ID = [NEW].ID 
                where ([NEW].APPLICATIONID = [OLD].APPLICATIONID
                        or [NEW].APPLICATIONID is null and [OLD].APPLICATIONID is null)
                    and [NEW].APPLIED = [OLD].APPLIED
                    and [NEW].APPLICATIONCODE = [OLD].APPLICATIONCODE

                if @STREAMCOUNT <> (select count(ID) from @UPDATEDREVENUESTREAMS)
                    set @CHANGED = 1;
            end

            --Gift Changed

            if @CHANGED = 0
            begin
                select @STREAMCOUNT = count([NEW].ID)
                from @UPDATEDREVENUESTREAMS as [NEW] 
                inner join @OLDREVENUESTREAMS as [OLD] on [OLD].ID = [NEW].ID 
                outer apply [NEW].GIFTFIELDS.nodes('/GIFTFIELDS/ITEM') T1(giftfield)
                outer apply [OLD].GIFTFIELDS.nodes('/ITEM') T2(giftfield)
                where [NEW].APPLICATIONCODE = 0
                    and (T1.giftfield.value('(DESIGNATIONID)[1]','uniqueidentifier') = T2.giftfield.value('(DESIGNATIONID)[1]','uniqueidentifier')
                        or T1.giftfield.value('(DESIGNATIONID)[1]','uniqueidentifier') is null and T2.giftfield.value('(DESIGNATIONID)[1]','uniqueidentifier') is null)
                    and (T1.giftfield.value('(CATEGORYCODEID)[1]','uniqueidentifier') = T2.giftfield.value('(CATEGORYCODEID)[1]','uniqueidentifier')
                        or T1.giftfield.value('(CATEGORYCODEID)[1]','uniqueidentifier') is null and T2.giftfield.value('(CATEGORYCODEID)[1]','uniqueidentifier') is null)

                if @STREAMCOUNT <> (select count(ID) from @UPDATEDREVENUESTREAMS where APPLICATIONCODE = 0)
                    set @CHANGED = 1;
            end

            --Unapplied matching gift Changed

            if @CHANGED = 0
            begin
                select @STREAMCOUNT = count([NEW].ID)
                from @UPDATEDREVENUESTREAMS as [NEW] 
                inner join @OLDREVENUESTREAMS as [OLD] on [OLD].ID = [NEW].ID 
                outer apply [NEW].GIFTFIELDS.nodes('/GIFTFIELDS/ITEM') T1(giftfield)
                outer apply [OLD].GIFTFIELDS.nodes('/ITEM') T2(giftfield)
                where [NEW].APPLICATIONCODE = 100
                    and (T1.giftfield.value('(DESIGNATIONID)[1]','uniqueidentifier') = T2.giftfield.value('(DESIGNATIONID)[1]','uniqueidentifier')
                        or T1.giftfield.value('(DESIGNATIONID)[1]','uniqueidentifier') is null and T2.giftfield.value('(DESIGNATIONID)[1]','uniqueidentifier') is null)
                    and (T1.giftfield.value('(CATEGORYCODEID)[1]','uniqueidentifier') = T2.giftfield.value('(CATEGORYCODEID)[1]','uniqueidentifier')
                        or T1.giftfield.value('(CATEGORYCODEID)[1]','uniqueidentifier') is null and T2.giftfield.value('(CATEGORYCODEID)[1]','uniqueidentifier') is null)

                if @STREAMCOUNT <> (select count(ID) from @UPDATEDREVENUESTREAMS where APPLICATIONCODE = 100)
                    set @CHANGED = 1;
            end

            --Other Changed

            if @CHANGED = 0
            begin
                select @STREAMCOUNT = count([NEW].ID)
                from @UPDATEDREVENUESTREAMS as [NEW] 
                inner join @OLDREVENUESTREAMS as [OLD] on [OLD].ID = [NEW].ID 
                outer apply [NEW].OTHERFIELDS.nodes('/OTHERFIELDS/ITEM') T1(giftfield)
                outer apply [OLD].OTHERFIELDS.nodes('/ITEM') T2(giftfield)
                where [NEW].APPLICATIONCODE = 4
                    and (T1.giftfield.value('(DESIGNATIONID)[1]','uniqueidentifier') = T2.giftfield.value('(DESIGNATIONID)[1]','uniqueidentifier')
                        or T1.giftfield.value('(DESIGNATIONID)[1]','uniqueidentifier') is null and T2.giftfield.value('(DESIGNATIONID)[1]','uniqueidentifier') is null)
                    and (T1.giftfield.value('(CATEGORYCODEID)[1]','uniqueidentifier') = T2.giftfield.value('(CATEGORYCODEID)[1]','uniqueidentifier')
                        or T1.giftfield.value('(CATEGORYCODEID)[1]','uniqueidentifier') is null and T2.giftfield.value('(CATEGORYCODEID)[1]','uniqueidentifier') is null)

                if @STREAMCOUNT <> (select count(ID) from @UPDATEDREVENUESTREAMS where APPLICATIONCODE = 4)
                    set @CHANGED = 1;
            end

            return @CHANGED
        end