UFN_EVENTEXPENSE_GETACTUALAMOUNTINCURRENCY_BULK

Returns the actual amount of an event expense in a 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_EVENTEXPENSE_GETACTUALAMOUNTINCURRENCY_BULK
            (
                @CURRENCYID uniqueidentifier,
        @ORGANIZATIONCURRENCYID uniqueidentifier,
                @DECIMALDIGITS tinyint,
                @ROUNDINGTYPECODE tinyint
            )
            returns table
            as 
      return(
        select 
          EVENTEXPENSE.ID,
          EVENTEXPENSE.EVENTID,
          case
                        when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
                            then EVENTEXPENSE.ORGANIZATIONACTUALAMOUNT
                        else
                            EVENTEXPENSE.ACTUALAMOUNT
                    end [AMOUNTINCURRENCY]
       from dbo.EVENTEXPENSE
       where (@CURRENCYID is null) 
                    or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
                    or @CURRENCYID = EVENTEXPENSE.BASECURRENCYID
       union all
       select
          EVENTEXPENSE.ID,
          EVENTEXPENSE.EVENTID,
          case
                        when [LATESTORGANIZATIONEXCHANGERATE].RATE is not null
                            then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(EVENTEXPENSE.ORGANIZATIONACTUALAMOUNT, [LATESTORGANIZATIONEXCHANGERATE].RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)

                        when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
                            then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(EVENTEXPENSE.ORGANIZATIONACTUALAMOUNT, cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)

                        else
                            0
                    end [AMOUNTINCURRENCY]
      from dbo.EVENTEXPENSE
      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 EVENTEXPENSE.DATEADDED >= CURRENCYEXCHANGERATE.ASOFDATE
                            and EVENTEXPENSE.DATEADDED <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAX
                    ) 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 EVENTEXPENSE.DATEADDED >= CURRENCYEXCHANGERATE.ASOFDATE
                      and EVENTEXPENSE.DATEADDED <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAX
              ) LATESTINVERSEORGANIZATIONEXCHANGERATE
              where (@CURRENCYID is not null) 
                  and (@CURRENCYID <> @ORGANIZATIONCURRENCYID)
                  and @CURRENCYID <> EVENTEXPENSE.BASECURRENCYID

      );