UFN_PLANNEDGIFTADDITION_DESIGNATIONSCHANGED

          CREATE function dbo.UFN_PLANNEDGIFTADDITION_DESIGNATIONSCHANGED
          (
              @PLANNEDGIFTADDITIONID as uniqueidentifier, 
              @DESIGNATIONS as xml
          ) 
          returns bit
          with execute as caller
          as
          begin
              declare @DESIGNATIONSTABLE table
              (
                  ID uniqueidentifier,
                  AMOUNT money,
                  DESIGNATIONID uniqueidentifier,
                  PLANNEDGIFTDESCATEGORYCODEID uniqueidentifier,
                  OPPORTUNITYDESIGNATIONTYPECODEID uniqueidentifier,
                  DESIGNATIONUSECODEID uniqueidentifier,
                  DATE datetime,
                  BASECURRENCYID uniqueidentifier,
                  ORGANIZATIONAMOUNT money,
                  ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                  TRANSACTIONAMOUNT money,
                  TRANSACTIONCURRENCYID uniqueidentifier,
                  BASEEXCHANGERATEID uniqueidentifier
              );

              declare @NEWDESIGNATIONSTABLE table
              (
                  ID uniqueidentifier,
                  AMOUNT money,
                  DESIGNATIONID uniqueidentifier,
                  PLANNEDGIFTDESCATEGORYCODEID uniqueidentifier,
                  OPPORTUNITYDESIGNATIONTYPECODEID uniqueidentifier,
                  DESIGNATIONUSECODEID uniqueidentifier,
                  DATE datetime,
                  BASECURRENCYID uniqueidentifier,
                  ORGANIZATIONAMOUNT money,
                  ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                  TRANSACTIONAMOUNT money,
                  TRANSACTIONCURRENCYID uniqueidentifier,
                  BASEEXCHANGERATEID uniqueidentifier
              );

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

              if @CHANGED = 0
              begin
                  insert into @NEWDESIGNATIONSTABLE(
                      ID,
                      AMOUNT,
                      DESIGNATIONID,
                      PLANNEDGIFTDESCATEGORYCODEID,
                      OPPORTUNITYDESIGNATIONTYPECODEID,
                      DESIGNATIONUSECODEID,
                      DATE,
                      BASECURRENCYID,
                      ORGANIZATIONAMOUNT,
                      ORGANIZATIONEXCHANGERATEID,
                      TRANSACTIONAMOUNT,
                      TRANSACTIONCURRENCYID,
                      BASEEXCHANGERATEID
                  ) 
                  select
                      ID,
                      AMOUNT,
                      DESIGNATIONID,
                      CATEGORYCODEID,
                      TYPECODEID,
                      USECODEID,
                      DATE,
                      BASECURRENCYID,
                      ORGANIZATIONAMOUNT,
                      ORGANIZATIONEXCHANGERATEID,
                      TRANSACTIONAMOUNT,
                      TRANSACTIONCURRENCYID,
                      BASEEXCHANGERATEID
                  from dbo.UFN_PLANNEDGIFTADDITION_GETDESIGNATIONS_FROMITEMLISTXML(@DESIGNATIONS);

                  insert into @DESIGNATIONSTABLE(
                      ID,
                      AMOUNT,
                      DESIGNATIONID,
                      PLANNEDGIFTDESCATEGORYCODEID,
                      OPPORTUNITYDESIGNATIONTYPECODEID,
                      DESIGNATIONUSECODEID,
                      DATE,
                      BASECURRENCYID,
                      ORGANIZATIONAMOUNT,
                      ORGANIZATIONEXCHANGERATEID,
                      TRANSACTIONAMOUNT,
                      TRANSACTIONCURRENCYID,
                      BASEEXCHANGERATEID
                  ) 
                  select
                      ID,
                      AMOUNT,
                      DESIGNATIONID,
                      CATEGORYCODEID,
                      TYPECODEID,
                      USECODEID,
                      DATE,
                      BASECURRENCYID,
                      ORGANIZATIONAMOUNT,
                      ORGANIZATIONEXCHANGERATEID,
                      TRANSACTIONAMOUNT,
                      TRANSACTIONCURRENCYID,
                      BASEEXCHANGERATEID
                  from dbo.UFN_PLANNEDGIFTADDITION_GETDESIGNATIONS(@PLANNEDGIFTADDITIONID);

                  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].PLANNEDGIFTDESCATEGORYCODEID = [OLD].PLANNEDGIFTDESCATEGORYCODEID or ([NEW].PLANNEDGIFTDESCATEGORYCODEID is null and [OLD].PLANNEDGIFTDESCATEGORYCODEID is null))
                          and ([NEW].OPPORTUNITYDESIGNATIONTYPECODEID = [OLD].OPPORTUNITYDESIGNATIONTYPECODEID or ([NEW].OPPORTUNITYDESIGNATIONTYPECODEID is null and [OLD].OPPORTUNITYDESIGNATIONTYPECODEID is null))
                          and ([NEW].DESIGNATIONUSECODEID = [OLD].DESIGNATIONUSECODEID or ([NEW].DESIGNATIONUSECODEID is null and [OLD].DESIGNATIONUSECODEID is null))
                          and ([NEW].DATE = [OLD].DATE or ([NEW].DATE is null and  [OLD].DATE is null))
                          and ([NEW].DESIGNATIONID = [OLD].DESIGNATIONID or ([NEW].DESIGNATIONID is null and [OLD].DESIGNATIONID is null))
                          and ([NEW].BASECURRENCYID = [OLD].BASECURRENCYID or ([NEW].BASECURRENCYID is null and [OLD].BASECURRENCYID is null))
                          and ([NEW].ORGANIZATIONAMOUNT = [OLD].ORGANIZATIONAMOUNT or ([NEW].ORGANIZATIONAMOUNT is null and [OLD].ORGANIZATIONAMOUNT is null))
                          and ([NEW].ORGANIZATIONEXCHANGERATEID = [OLD].ORGANIZATIONEXCHANGERATEID or ([NEW].ORGANIZATIONEXCHANGERATEID is null and [OLD].ORGANIZATIONEXCHANGERATEID is null))
                          and ([NEW].TRANSACTIONAMOUNT = [OLD].TRANSACTIONAMOUNT or ([NEW].TRANSACTIONAMOUNT is null and [OLD].TRANSACTIONAMOUNT is null))
                          and ([NEW].TRANSACTIONCURRENCYID = [OLD].TRANSACTIONCURRENCYID or ([NEW].TRANSACTIONCURRENCYID is null and [OLD].TRANSACTIONCURRENCYID is null))
                          and ([NEW].BASEEXCHANGERATEID = [OLD].BASEEXCHANGERATEID or ([NEW].BASEEXCHANGERATEID is null and [OLD].BASEEXCHANGERATEID is null));

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

              end

              return @CHANGED
          end