UFN_REVENUESPLITBUSINESSUNIT_GETAMOUNTINCURRENCY_BULK

Returns the business unit amount of a revenue split record in the given currency.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@CURRENCYID	uniqueidentifier	IN
@ORGANIZATIONCURRENCYID	uniqueidentifier	IN
@DECIMALDIGITS	tinyint	IN
@ROUNDINGTYPECODE	tinyint	IN
Definition

Copy


CREATE function dbo.UFN_REVENUESPLITBUSINESSUNIT_GETAMOUNTINCURRENCY_BULK
(
  @CURRENCYID uniqueidentifier,
  @ORGANIZATIONCURRENCYID uniqueidentifier,
  @DECIMALDIGITS tinyint,
  @ROUNDINGTYPECODE tinyint
)
returns table
as
return
(
  select
     REVENUESPLITBUSINESSUNIT.ID,
     REVENUESPLITBUSINESSUNIT.REVENUESPLITID,
    case
      when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID) then REVENUESPLITBUSINESSUNIT.ORGANIZATIONAMOUNT
      else REVENUESPLITBUSINESSUNIT.AMOUNT
    end as AMOUNTINCURRENCY,
    REVENUESPLITBUSINESSUNIT.BUSINESSUNITCODEID
  from dbo.REVENUESPLITBUSINESSUNIT with (nolock)
  where 
    (@CURRENCYID is null) or
    (@CURRENCYID = @ORGANIZATIONCURRENCYID) or
    (@CURRENCYID = REVENUESPLITBUSINESSUNIT.BASECURRENCYID)

  union all

  select
    REVENUESPLITBUSINESSUNIT.ID,
     REVENUESPLITBUSINESSUNIT.REVENUESPLITID,
    case
      when LATESTORGANIZATIONEXCHANGERATE.RATE is not null
        then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUESPLITBUSINESSUNIT.ORGANIZATIONAMOUNT, LATESTORGANIZATIONEXCHANGERATE.RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)

      when LATESTINVERSEORGANIZATIONEXCHANGERATE.RATE is not null
        then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUESPLITBUSINESSUNIT.ORGANIZATIONAMOUNT, cast((1 / LATESTINVERSEORGANIZATIONEXCHANGERATE.RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
      else
        0
    end AMOUNTINCURRENCY,
     REVENUESPLITBUSINESSUNIT.BUSINESSUNITCODEID
  from dbo.REVENUESPLITBUSINESSUNIT with (nolock)
    outer apply
    (
      select
        RATE
      from dbo.CURRENCYEXCHANGERATE
      where
        @ORGANIZATIONCURRENCYID = CURRENCYEXCHANGERATE.FROMCURRENCYID and
        @CURRENCYID = CURRENCYEXCHANGERATE.TOCURRENCYID and
        CURRENCYEXCHANGERATE.TYPECODE in (0,1) and
        CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1 and
        REVENUESPLITBUSINESSUNIT.DATEADDED >= CURRENCYEXCHANGERATE.ASOFDATESDTZ and
        REVENUESPLITBUSINESSUNIT.DATEADDED <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
    ) LATESTORGANIZATIONEXCHANGERATE
    outer apply
    (
      select
        RATE
      from dbo.CURRENCYEXCHANGERATE
      where
        @CURRENCYID = CURRENCYEXCHANGERATE.FROMCURRENCYID and
        @ORGANIZATIONCURRENCYID= CURRENCYEXCHANGERATE.TOCURRENCYID and
        CURRENCYEXCHANGERATE.TYPECODE in (0,1) and
        CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1 and
        REVENUESPLITBUSINESSUNIT.DATEADDED >= CURRENCYEXCHANGERATE.ASOFDATESDTZ and
        REVENUESPLITBUSINESSUNIT.DATEADDED <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
    ) LATESTINVERSEORGANIZATIONEXCHANGERATE
    where 
      (@CURRENCYID is not null) and
      (@CURRENCYID <> @ORGANIZATIONCURRENCYID) and
      (@CURRENCYID <> REVENUESPLITBUSINESSUNIT.BASECURRENCYID)
)