UFN_CHECKDETAIL_DESIGNATIONSCHANGED

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

  declare @NEWSPLITSTABLE table
  (
    ID uniqueidentifier,
    DESIGNATIONID uniqueidentifier
  );

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

  insert into @NEWSPLITSTABLE(ID, DESIGNATIONID) 
  select ID, DESIGNATIONID from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS);

  insert into @SPLITSTABLE(ID, DESIGNATIONID)
  select ID, DESIGNATIONID 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].DESIGNATIONID = [OLD].DESIGNATIONID or ([NEW].DESIGNATIONID is null and [OLD].DESIGNATIONID is null));

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

  return @CHANGED
end