UFN_OPPORTUNITY_DESIGNATIONSCHANGED_2

create function dbo.UFN_OPPORTUNITY_DESIGNATIONSCHANGED_2
(
  @OPPORTUNITYID as uniqueidentifier,
  @DESIGNATIONS as xml
) 
returns bit
with execute as caller
as
begin

  declare @DESIGNATIONSTABLE table
  (
    ID uniqueidentifier,
    DESIGNATIONID uniqueidentifier,
    AMOUNT money,
    SEQUENCE int,
    CONSTITUENTID uniqueidentifier,
    TRANSACTIONCURRENCYID uniqueidentifier,
    FUNDINGMETHODCODEID uniqueidentifier,
    CATEGORYCODEID uniqueidentifier,
    TYPECODEID uniqueidentifier,
    USECODEID uniqueidentifier
  );

  declare @NEWDESIGNATIONSTABLE table
  (
    ID uniqueidentifier,
    DESIGNATIONID uniqueidentifier,
    AMOUNT money,
    SEQUENCE int,
    CONSTITUENTID uniqueidentifier,
    TRANSACTIONCURRENCYID uniqueidentifier,
    FUNDINGMETHODCODEID uniqueidentifier,
    CATEGORYCODEID uniqueidentifier,
    TYPECODEID uniqueidentifier,
    USECODEID uniqueidentifier
  );

  declare @DESIGNATIONSCOUNT int;
  declare @CHANGED as bit = 0;

  insert into
    @NEWDESIGNATIONSTABLE
  (
    ID,
    DESIGNATIONID,
    AMOUNT,
    SEQUENCE,
    CONSTITUENTID,
    TRANSACTIONCURRENCYID,
    FUNDINGMETHODCODEID,
    CATEGORYCODEID,
    TYPECODEID,
    USECODEID
  )
  select
    ID,
    DESIGNATIONID,
    AMOUNT,
    SEQUENCE,
    CONSTITUENTID,
    TRANSACTIONCURRENCYID,
    FUNDINGMETHODCODEID,
    CATEGORYCODEID,
    TYPECODEID,
    USECODEID
  from
    dbo.UFN_OPPORTUNITY_DESIGNATION_2_FROMITEMLISTXML(@DESIGNATIONS);

  insert into
    @DESIGNATIONSTABLE
  (
    ID,
    DESIGNATIONID,
    AMOUNT,
    SEQUENCE,
    CONSTITUENTID,
    TRANSACTIONCURRENCYID,
    FUNDINGMETHODCODEID,
    CATEGORYCODEID,
    TYPECODEID,
    USECODEID
  )
  select
    ID,
    DESIGNATIONID,
    AMOUNT,
    SEQUENCE,
    CONSTITUENTID,
    TRANSACTIONCURRENCYID,
    FUNDINGMETHODCODEID,
    CATEGORYCODEID,
    TYPECODEID,
    USECODEID
  from
    dbo.UFN_OPPORTUNITY_DESIGNATION_2(@OPPORTUNITYID);

  select @DESIGNATIONSCOUNT = count(ID) from @DESIGNATIONSTABLE;

  if @DESIGNATIONSCOUNT <> (select count(*) from @NEWDESIGNATIONSTABLE) 
    set @CHANGED = 1;

  if @CHANGED = 0
  begin
    select
      @DESIGNATIONSCOUNT = count(NEW.ID)
    from
      @NEWDESIGNATIONSTABLE as NEW
    inner join
      @DESIGNATIONSTABLE as OLD on
        NEW.AMOUNT = OLD.AMOUNT and
        NEW.SEQUENCE = OLD.SEQUENCE and
        (NEW.DESIGNATIONID = OLD.DESIGNATIONID or (NEW.DESIGNATIONID is null and OLD.DESIGNATIONID is null)) and
        (NEW.CONSTITUENTID = OLD.CONSTITUENTID or (NEW.CONSTITUENTID is null and OLD.CONSTITUENTID is null)) and
        (NEW.TRANSACTIONCURRENCYID = OLD.TRANSACTIONCURRENCYID or (NEW.TRANSACTIONCURRENCYID is null and OLD.TRANSACTIONCURRENCYID is null)) and
        (NEW.FUNDINGMETHODCODEID = OLD.FUNDINGMETHODCODEID or (NEW.FUNDINGMETHODCODEID is null and OLD.FUNDINGMETHODCODEID is null)) and
        (NEW.CATEGORYCODEID = OLD.CATEGORYCODEID or (NEW.CATEGORYCODEID is null and OLD.CATEGORYCODEID is null)) and
        (NEW.TYPECODEID = OLD.TYPECODEID or (NEW.TYPECODEID is null and OLD.TYPECODEID is null)) and
        (NEW.USECODEID = OLD.USECODEID or (NEW.USECODEID is null and OLD.USECODEID is null))

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

  return @CHANGED;
end