UFN_CHECKDETAIL_SPLITSCHANGED

CREATE function dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@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);

    insert into @SPLITSTABLE(ID, AMOUNT, DESIGNATIONID, BASEEXCHANGERATEID)
      select ID, TRANSACTIONAMOUNT, DESIGNATIONID, BASEEXCHANGERATEID from dbo.UFN_REVENUE_GETSPLITS_2(@REVENUEID);

    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