UFN_OPPORTUNITYDESIGNATION_AMOUNTINCURRENCY

Returns the amount of an opportunity designation in a specific currency.
Return

Return Type
money
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN
@CURRENCYID	uniqueidentifier	IN
Definition

Copy


CREATE function dbo.UFN_OPPORTUNITYDESIGNATION_AMOUNTINCURRENCY(
  @ID uniqueidentifier,
  @CURRENCYID uniqueidentifier
)
returns money
with execute as caller
as begin

  declare @AMOUNT money;

  if (@CURRENCYID is null) or (@CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY())
  begin
    select @AMOUNT = coalesce(
                                (
                                    select
                                        sum(ORGANIZATIONAMOUNT)
                                    from
                                        dbo.OPPORTUNITYDESIGNATION
                                    where
                                        ID=@ID
                                ),
                                0
                            )
  end
  else
  begin
    declare @RECORDBASECURRENCYID uniqueidentifier;
    declare @RECORDDATE datetime;

    select
      @RECORDBASECURRENCYID = OPPORTUNITYDESIGNATION.BASECURRENCYID,
      @RECORDDATE = OPPORTUNITYDESIGNATION.DATEADDED
    from
      dbo.OPPORTUNITYDESIGNATION
    where
      OPPORTUNITYDESIGNATION.ID = @ID;

    if @CURRENCYID = @RECORDBASECURRENCYID
    begin
      select @AMOUNT = coalesce(
                              (
                                  select
                                      sum(AMOUNT)
                                  from
                                      dbo.OPPORTUNITYDESIGNATION
                                  where
                                      ID=@ID
                              ),
                              0
                          )
    end
    else
    begin
      declare @CURRENCYEXCHANGERATEID uniqueidentifier;

      set @CURRENCYEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), @CURRENCYID, @RECORDDATE, 1, null);
      if (@CURRENCYEXCHANGERATEID is not null)
      begin
        select @AMOUNT = coalesce(
                                (
                                    select
                                        sum(dbo.UFN_CURRENCY_CONVERT(ORGANIZATIONAMOUNT, @CURRENCYEXCHANGERATEID))
                                    from
                                        dbo.OPPORTUNITYDESIGNATION
                                    where
                                        ID=@ID
                                ),
                                0
                            )
      end
      else
      begin
        set @CURRENCYEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(@CURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), @RECORDDATE, 1, null);

        select @AMOUNT = coalesce(
                                (
                                    select
                                        sum(dbo.UFN_CURRENCY_CONVERTINVERSE(ORGANIZATIONAMOUNT, @CURRENCYEXCHANGERATEID))
                                    from
                                        dbo.OPPORTUNITYDESIGNATION
                                    where
                                        ID=@ID
                                ),
                                0
                            )
      end      
    end
  end

  return @AMOUNT;
end