UFN_CHECKDETAIL_SPLITSCHANGED_EXCLUDEMEMBERSHIP

create function dbo.UFN_CHECKDETAIL_SPLITSCHANGED_EXCLUDEMEMBERSHIP(@REVENUEID as uniqueidentifier, @SPLITS as xml) 
returns bit
with execute as caller
as
begin
  declare @SPLITSTABLE table
  (
    ID uniqueidentifier,
    AMOUNT money,
    DESIGNATIONID uniqueidentifier,
    BASEEXCHANGERATEID uniqueidentifier
  );

  declare @NEWSPLITSTABLE table
  (
    ID uniqueidentifier,
    AMOUNT money,
    DESIGNATIONID uniqueidentifier,
    BASEEXCHANGERATEID uniqueidentifier
  );

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

  if @CHANGED = 0
  begin
    insert into @NEWSPLITSTABLE(ID, AMOUNT, DESIGNATIONID, BASEEXCHANGERATEID) 
      select ID, AMOUNT, DESIGNATIONID, BASEEXCHANGERATEID from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS);

    declare @ISMEMBERSHIPTRANSACTION bit;

    select @ISMEMBERSHIPTRANSACTION = case REVENUE.TYPECODE when 2 then dbo.UFN_RECURRINGGIFT_ISMEMBERSHIP(@REVENUEID) when 15 then 1 else 0 end
      from dbo.FINANCIALTRANSACTION REVENUE where ID = @REVENUEID

    --if this is a membership transaction exclude the null designation splits

    if @ISMEMBERSHIPTRANSACTION = 1 
      begin
        insert into @SPLITSTABLE(ID, AMOUNT, DESIGNATIONID, BASEEXCHANGERATEID)
          select ID, TRANSACTIONAMOUNT, DESIGNATIONID, BASEEXCHANGERATEID from dbo.UFN_REVENUE_GETSPLITS_2(@REVENUEID)
          where DESIGNATIONID is not null;
      end
    else
      begin
        insert into @SPLITSTABLE(ID, AMOUNT, DESIGNATIONID, BASEEXCHANGERATEID)
          select ID, TRANSACTIONAMOUNT, DESIGNATIONID, BASEEXCHANGERATEID from dbo.UFN_REVENUE_GETSPLITS_2(@REVENUEID);
      end

    select @SPLITSCOUNT = count(ID) from @SPLITSTABLE;

    if @SPLITSCOUNT <> (select count(*) from @NEWSPLITSTABLE) 
      set @CHANGED = 1;

    if @CHANGED = 0
    begin
      select @SPLITSCOUNT = count([NEW].ID)
      from @NEWSPLITSTABLE as [NEW] 
      inner join @SPLITSTABLE as [OLD] 
        on ([NEW].ID = [OLD].ID and
            [NEW].AMOUNT = [OLD].AMOUNT and
            ([NEW].BASEEXCHANGERATEID = [OLD].BASEEXCHANGERATEID or ([NEW].BASEEXCHANGERATEID is null and [OLD].BASEEXCHANGERATEID is null)) and
            ([NEW].DESIGNATIONID = [OLD].DESIGNATIONID or ([NEW].DESIGNATIONID is null and [OLD].DESIGNATIONID is null)));

      if @SPLITSCOUNT <> (select count(*) from @NEWSPLITSTABLE)
        set @CHANGED = 1;
    end

  end

  return @CHANGED
end