UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK

Returns the revenue split amounts of all appeal records 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
@CURRENCYCODE	tinyint	IN
Definition

Copy


      CREATE function [dbo].[UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK]
      (
        -- If you want to convert to...

        --    The base currency of each row:

        --        set @CURRENCYCODE = 0

        --        set @ORGANIZATIONCURRENCYID appropriately

        --    The organization currency:

        --        set @CURRENCYCODE = 1

        --        set @ORGANIZATIONCURRENCYID appropriately

        --    An arbitrary currency:

        --        set @CURRENCYCODE = anything but 0 or 1

        --        set @CURRENCYID = the currency's ID

        --        set @DECIMALDIGITS and @ROUNDINGTYPECODE according to the currency

        --        set @ORGANIZATIONCURRENCYID

        @CURRENCYID uniqueidentifier,
        @ORGANIZATIONCURRENCYID uniqueidentifier,
        @DECIMALDIGITS tinyint,
        @ROUNDINGTYPECODE tinyint,
        @CURRENCYCODE tinyint
      )
      returns table
      as
      return
      (
        with APPEALCTE
        as
        (
          select 
            APPEAL.ID APPEALID,
            case @CURRENCYCODE
              when 0 then APPEAL.BASECURRENCYID
              when 1 then @ORGANIZATIONCURRENCYID
              else @CURRENCYID
            end as CURRENCYID,
            DESIGNATION.ID DESIGNATIONID,
            DESIGNATION.NAME DESIGNATIONNAME,
            REVENUE.ID REVENUEID,
            cast(REVENUE.DATE as datetime) [DATE],
            REVENUE.TYPECODE TRANSACTIONTYPECODE,
            REVENUESPLIT.ID REVENUESPLITID,
            REVENUESPLIT.APPLICATIONCODE,
            REVENUESPLIT.TYPECODE,
            LI.TRANSACTIONAMOUNT REVENUESPLITTRANSACTIONAMOUNT,
            LI.BASEAMOUNT REVENUESPLITAMOUNT,
            LI.ORGAMOUNT REVENUESPLITORGANIZATIONAMOUNT,
            REVENUE.TRANSACTIONCURRENCYID REVENUETRANSACTIONCURRENCYID,
            isnull(REX.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) REVENUEBASECURRENCYID,
            WOLI.TRANSACTIONAMOUNT WRITEOFFSPLITTRANSACTIONAMOUNT,
            WOLI.BASEAMOUNT WRITEOFFSPLITAMOUNT,
            WOLI.ORGAMOUNT WRITEOFFSPLITORGANIZATIONAMOUNT,
            case
              --2 = 'Valid declaration', 3 = 'Covenant gift', 4 = 'Gift Aid sponsorship'

              when REVENUESPLITGIFTAIDAMOUNTS.ELIGIBILITYCODE in (2,3,4) then REVENUESPLITGIFTAIDAMOUNTS.TRANSACTIONTAXCLAIMAMOUNT
              else 0
            end as TRANSACTIONTAXCLAIMAMOUNT,
            case
              when REVENUESPLITGIFTAIDAMOUNTS.ELIGIBILITYCODE in (2,3,4) then REVENUESPLITGIFTAIDAMOUNTS.TAXCLAIMAMOUNT
              else 0
            end as TAXCLAIMAMOUNT,
            case
              when REVENUESPLITGIFTAIDAMOUNTS.ELIGIBILITYCODE in (2,3,4) then REVENUESPLITGIFTAIDAMOUNTS.ORGANIZATIONTAXCLAIMAMOUNT
              else 0
            end as ORGANIZATIONTAXCLAIMAMOUNT
          from
            dbo.FINANCIALTRANSACTION REVENUE
            inner join dbo.REVENUE_EXT REX on REX.ID = REVENUE.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on REVENUE.ID = LI.FINANCIALTRANSACTIONID
            inner join dbo.REVENUESPLIT_EXT REVENUESPLIT on REVENUESPLIT.ID = LI.ID
            inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on LI.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
            left join dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT.DESIGNATIONID
            left join dbo.FINANCIALTRANSACTION WRITEOFF on REVENUE.ID = WRITEOFF.PARENTID and WRITEOFF.TYPECODE = 20
            left join dbo.FINANCIALTRANSACTIONLINEITEM WOLI on WRITEOFF.ID = WOLI.FINANCIALTRANSACTIONID 
            left join dbo.REVENUESPLIT_EXT WRITEOFFSPLIT on WRITEOFFSPLIT.ID = WOLI.ID and REVENUESPLIT.DESIGNATIONID = WRITEOFFSPLIT.DESIGNATIONID
            left join dbo.APPEAL on APPEAL.ID = REX.APPEALID
            left join dbo.[REVENUESPLITGIFTAIDAMOUNTS] on [REVENUESPLIT].[ID] = [REVENUESPLITGIFTAIDAMOUNTS].[ID]
            where REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and LI.DELETEDON is null and LI.TYPECODE != 1
              and (WRITEOFF.ID is null or (WOLI.DELETEDON is null and WOLI.TYPECODE = 0))
        )
        select            -- Selected currency = base, org currency

          APPEALCTE.APPEALID,
          case
            when (APPEALCTE.CURRENCYID = APPEALCTE.REVENUEBASECURRENCYID)
              then APPEALCTE.REVENUESPLITAMOUNT
            when (APPEALCTE.CURRENCYID = @ORGANIZATIONCURRENCYID) or (APPEALCTE.CURRENCYID is null)
              then APPEALCTE.REVENUESPLITORGANIZATIONAMOUNT
          end [REVENUESPLITAMOUNTINCURRENCY],
          isnull(case
            when (APPEALCTE.CURRENCYID = APPEALCTE.REVENUEBASECURRENCYID)
              then APPEALCTE.WRITEOFFSPLITAMOUNT
            when (APPEALCTE.CURRENCYID = @ORGANIZATIONCURRENCYID) or (APPEALCTE.CURRENCYID is null)
              then APPEALCTE.WRITEOFFSPLITORGANIZATIONAMOUNT
          end, 0) [WRITEOFFSPLITAMOUNTINCURRENCY],
          isnull(case
            when (APPEALCTE.CURRENCYID = APPEALCTE.REVENUEBASECURRENCYID)
              then APPEALCTE.TAXCLAIMAMOUNT
            when (APPEALCTE.CURRENCYID = @ORGANIZATIONCURRENCYID) or (APPEALCTE.CURRENCYID is null)
              then APPEALCTE.ORGANIZATIONTAXCLAIMAMOUNT
          end, 0) [TAXCLAIMAMOUNTINCURRENCY],
          APPEALCTE.DESIGNATIONID,
          APPEALCTE.DESIGNATIONNAME,
          APPEALCTE.REVENUEID,
          APPEALCTE.DATE,
          APPEALCTE.TRANSACTIONTYPECODE,
          APPEALCTE.REVENUESPLITID,
          APPEALCTE.APPLICATIONCODE,
          APPEALCTE.TYPECODE
        from 
          APPEALCTE
        where 
          (APPEALCTE.CURRENCYID is null)
          or (APPEALCTE.CURRENCYID = APPEALCTE.REVENUEBASECURRENCYID)
          or (APPEALCTE.CURRENCYID = @ORGANIZATIONCURRENCYID)

        union all            -- Use designation base currency and currency <> base, org currency


        select
          APPEALCTE.APPEALID,
          case
            when LATESTEXCHANGERATE.RATE is not null
              then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(APPEALCTE.REVENUESPLITORGANIZATIONAMOUNT, LATESTEXCHANGERATE.RATE), CURRENCY.DECIMALDIGITS, CURRENCY.ROUNDINGTYPECODE)
            when LATESTINVERSEEXCHANGERATE.RATE is not null
              then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(APPEALCTE.REVENUESPLITORGANIZATIONAMOUNT, cast((1 / LATESTINVERSEEXCHANGERATE.RATE) as decimal(20,8))), CURRENCY.DECIMALDIGITS, CURRENCY.ROUNDINGTYPECODE)
            else 0
          end [REVENUESPLITAMOUNTINCURRENCY],
          isnull(case
            when LATESTEXCHANGERATE.RATE is not null
              then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(APPEALCTE.WRITEOFFSPLITORGANIZATIONAMOUNT, LATESTEXCHANGERATE.RATE), CURRENCY.DECIMALDIGITS, CURRENCY.ROUNDINGTYPECODE)
            when LATESTINVERSEEXCHANGERATE.RATE is not null
              then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(APPEALCTE.WRITEOFFSPLITORGANIZATIONAMOUNT, cast((1 / LATESTINVERSEEXCHANGERATE.RATE) as decimal(20,8))), CURRENCY.DECIMALDIGITS, CURRENCY.ROUNDINGTYPECODE)
            else 0
          end, 0) [WRITEOFFSPLITAMOUNTINCURRENCY],
          isnull(case
            when LATESTEXCHANGERATE.RATE is not null
              then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(APPEALCTE.ORGANIZATIONTAXCLAIMAMOUNT, LATESTEXCHANGERATE.RATE), CURRENCY.DECIMALDIGITS, CURRENCY.ROUNDINGTYPECODE)
            when LATESTINVERSEEXCHANGERATE.RATE is not null
              then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(APPEALCTE.ORGANIZATIONTAXCLAIMAMOUNT, cast((1 / LATESTINVERSEEXCHANGERATE.RATE) as decimal(20,8))), CURRENCY.DECIMALDIGITS, CURRENCY.ROUNDINGTYPECODE)
            else 0
          end, 0) [TAXCLAIMAMOUNTINCURRENCY],
          APPEALCTE.DESIGNATIONID,
          APPEALCTE.DESIGNATIONNAME,
          APPEALCTE.REVENUEID,
          APPEALCTE.DATE,
          APPEALCTE.TRANSACTIONTYPECODE,
          APPEALCTE.REVENUESPLITID,
          APPEALCTE.APPLICATIONCODE,
          APPEALCTE.TYPECODE
        from APPEALCTE
          left join dbo.CURRENCY on CURRENCY.ID = APPEALCTE.CURRENCYID
          outer apply
          (
            select
              RATE
            from
              dbo.CURRENCYEXCHANGERATE
            where
              CURRENCYEXCHANGERATE.FROMCURRENCYID = @ORGANIZATIONCURRENCYID
              and CURRENCYEXCHANGERATE.TOCURRENCYID = APPEALCTE.CURRENCYID
              and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
              and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
              and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= dateadd(ms, 86399996,APPEALCTE.DATE)
              and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= dateadd(ms, 86399996,APPEALCTE.DATE)
          ) LATESTEXCHANGERATE
          outer apply
          (
            select
              RATE
            from
              dbo.CURRENCYEXCHANGERATE
            where
              CURRENCYEXCHANGERATE.FROMCURRENCYID = APPEALCTE.CURRENCYID
              and CURRENCYEXCHANGERATE.TOCURRENCYID = @ORGANIZATIONCURRENCYID
              and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
              and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
              and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= dateadd(ms, 86399996,APPEALCTE.DATE)
              and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= dateadd(ms, 86399996,APPEALCTE.DATE)
          ) LATESTINVERSEEXCHANGERATE
        where (APPEALCTE.CURRENCYID is not null)
          and (APPEALCTE.CURRENCYID <> APPEALCTE.REVENUEBASECURRENCYID)
          and (APPEALCTE.CURRENCYID <> @ORGANIZATIONCURRENCYID)
          and @CURRENCYCODE = 0

        union all        -- Use arbitrary currency and currency <> base, org currency


        select 
          APPEALCTE.APPEALID,
          case
            when LATESTEXCHANGERATE.RATE is not null
              then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(APPEALCTE.REVENUESPLITORGANIZATIONAMOUNT, LATESTEXCHANGERATE.RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
            when LATESTINVERSEEXCHANGERATE.RATE is not null
              then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(APPEALCTE.REVENUESPLITORGANIZATIONAMOUNT, cast((1 / LATESTINVERSEEXCHANGERATE.RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
            else 0
          end [REVENUESPLITAMOUNTINCURRENCY],
          isnull(case
            when LATESTEXCHANGERATE.RATE is not null
              then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(APPEALCTE.WRITEOFFSPLITORGANIZATIONAMOUNT, LATESTEXCHANGERATE.RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
            when LATESTINVERSEEXCHANGERATE.RATE is not null
              then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(APPEALCTE.WRITEOFFSPLITORGANIZATIONAMOUNT, cast((1 / LATESTINVERSEEXCHANGERATE.RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
            else 0
          end, 0) [WRITEOFFSPLITAMOUNTINCURRENCY],
          isnull(case
            when LATESTEXCHANGERATE.RATE is not null
              then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(APPEALCTE.ORGANIZATIONTAXCLAIMAMOUNT, LATESTEXCHANGERATE.RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
            when LATESTINVERSEEXCHANGERATE.RATE is not null
              then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(APPEALCTE.ORGANIZATIONTAXCLAIMAMOUNT, cast((1 / LATESTINVERSEEXCHANGERATE.RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
            else 0
          end, 0) [TAXCLAIMAMOUNTINCURRENCY],
          APPEALCTE.DESIGNATIONID,
          APPEALCTE.DESIGNATIONNAME,
          APPEALCTE.REVENUEID,
          APPEALCTE.DATE,
          APPEALCTE.TRANSACTIONTYPECODE,
          APPEALCTE.REVENUESPLITID,
          APPEALCTE.APPLICATIONCODE,
          APPEALCTE.TYPECODE
        from APPEALCTE
          outer apply
          (
            select
              RATE
            from
              dbo.CURRENCYEXCHANGERATE
            where
              CURRENCYEXCHANGERATE.FROMCURRENCYID = @ORGANIZATIONCURRENCYID
              and CURRENCYEXCHANGERATE.TOCURRENCYID = APPEALCTE.CURRENCYID
              and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
              and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
              and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= dateadd(ms, 86399996,APPEALCTE.DATE)
              and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= dateadd(ms, 86399996,APPEALCTE.DATE)
          ) LATESTEXCHANGERATE
          outer apply
          (
            select
              RATE
            from
              dbo.CURRENCYEXCHANGERATE
            where
              CURRENCYEXCHANGERATE.FROMCURRENCYID = APPEALCTE.CURRENCYID
              and CURRENCYEXCHANGERATE.TOCURRENCYID = @ORGANIZATIONCURRENCYID
              and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
              and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
              and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= dateadd(ms, 86399996,APPEALCTE.DATE)
              and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= dateadd(ms, 86399996,APPEALCTE.DATE)
          ) LATESTINVERSEEXCHANGERATE
        where (APPEALCTE.CURRENCYID is not null)
          and (APPEALCTE.CURRENCYID <> APPEALCTE.REVENUEBASECURRENCYID)
          and (APPEALCTE.CURRENCYID <> @ORGANIZATIONCURRENCYID)
          and @CURRENCYCODE not in (0, 1)
      )