UFN_CHECKDETAIL_BENEFITSCHANGEDFORGL

Determines if the benefits for a revenue detail record have changed so that the gl distributions should be regenerated.
Return

Return Type
bit
Parameters

Parameter	Parameter Type	Mode	Description
@REVENUEID	uniqueidentifier	IN
@BENEFITS	xml	IN
Definition

Copy


CREATE function dbo.UFN_CHECKDETAIL_BENEFITSCHANGEDFORGL
(
  @REVENUEID uniqueidentifier,
  @BENEFITS xml
)
returns bit
with execute as caller
as
begin

  declare @BENEFITSTABLE table
  (
    ID uniqueidentifier,
    BENEFITID uniqueidentifier,
    TOTALVALUE money,
    QUANTITY int,
    ITEMVALUE money,
    APPLICABLEAMOUNT money,
    VALUEPERCENT decimal
  );

  declare @NEWBENEFITSTABLE table
  (
    ID uniqueidentifier,
    BENEFITID uniqueidentifier,
    TOTALVALUE money,
    QUANTITY int,
    ITEMVALUE money,
    APPLICABLEAMOUNT money,
    VALUEPERCENT decimal
  );

  declare @BENEFITSCOUNT int;
  declare @CHANGED bit = 0;

  insert into @NEWBENEFITSTABLE(ID, BENEFITID, TOTALVALUE, QUANTITY, ITEMVALUE, APPLICABLEAMOUNT, VALUEPERCENT) 
  select ID, BENEFITID, TOTALVALUE, QUANTITY, UNITVALUE, PERCENTAPPLICABLEAMOUNT, VALUEPERCENT from dbo.UFN_REVENUE_GETBENEFITS2_FROMITEMLISTXML(@BENEFITS);

  set @BENEFITSCOUNT = @@ROWCOUNT;

  insert into @BENEFITSTABLE(ID, BENEFITID, TOTALVALUE, QUANTITY, ITEMVALUE, APPLICABLEAMOUNT, VALUEPERCENT) 
  select ID, BENEFITID, TOTALVALUE, QUANTITY, UNITVALUE, PERCENTAPPLICABLEAMOUNT, VALUEPERCENT from dbo.UFN_REVENUE_GETBENEFITS2(@REVENUEID);

  if @@ROWCOUNT <> @BENEFITSCOUNT
    set @CHANGED = 1;

  if @CHANGED = 0
  begin
    select
      @CHANGED = 1
    from
    (
      select
        count(1) BENEFITCOUNT,
        BENEFITID,
        TOTALVALUE,
        QUANTITY,
        ITEMVALUE,
        APPLICABLEAMOUNT,
        VALUEPERCENT
      from
        @NEWBENEFITSTABLE
      group by
        BENEFITID, TOTALVALUE, QUANTITY, ITEMVALUE, APPLICABLEAMOUNT, VALUEPERCENT
    ) NEWBENEFITS
    full outer join
    (
      select
        count(1) BENEFITCOUNT,
        BENEFITID,
        TOTALVALUE,
        QUANTITY,
        ITEMVALUE,
        APPLICABLEAMOUNT,
        VALUEPERCENT
      from
        @BENEFITSTABLE
      group by
        BENEFITID, TOTALVALUE, QUANTITY, ITEMVALUE, APPLICABLEAMOUNT, VALUEPERCENT
    ) OLDBENEFITS
      on NEWBENEFITS.BENEFITID = OLDBENEFITS.BENEFITID
      and NEWBENEFITS.TOTALVALUE = OLDBENEFITS.TOTALVALUE
      and NEWBENEFITS.BENEFITCOUNT = OLDBENEFITS.BENEFITCOUNT
      and NEWBENEFITS.QUANTITY = OLDBENEFITS.QUANTITY
      and NEWBENEFITS.ITEMVALUE = OLDBENEFITS.ITEMVALUE
      and NEWBENEFITS.APPLICABLEAMOUNT = OLDBENEFITS.APPLICABLEAMOUNT
      and NEWBENEFITS.VALUEPERCENT = OLDBENEFITS.VALUEPERCENT
    where
      NEWBENEFITS.BENEFITID is null or
      OLDBENEFITS.BENEFITID is null
  end

  return @CHANGED;
end