UFN_OPPORTUNITY_AMOUNTINCURRENCY

CREATE function dbo.UFN_OPPORTUNITY_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
                    case when STATUSCODE = 3 then  -- Accepted

                        coalesce(
                            ORGANIZATIONACCEPTEDASKAMOUNT,
                            (
                                select
                                    sum(ORGANIZATIONAMOUNT)
                                from
                                    dbo.OPPORTUNITYDESIGNATION
                                where
                                    OPPORTUNITYID=@ID
                            ),
                            0
                        )
                    else
                        case when ORGANIZATIONASKAMOUNT > 0 then ORGANIZATIONASKAMOUNT
                        else ORGANIZATIONEXPECTEDASKAMOUNT
                        end
                    end
                from
                    dbo.OPPORTUNITY
                where
                    ID = @ID
            ),
            0
        )
  end
  else
  begin
    declare @RECORDBASECURRENCYID uniqueidentifier;
    declare @RECORDDATE datetime;

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

    if @CURRENCYID = @RECORDBASECURRENCYID
    begin
      select @AMOUNT = coalesce(
              (
                  select
                      case when STATUSCODE = 3 then  -- Accepted

                          coalesce(
                              ACCEPTEDASKAMOUNT,
                              (
                                  select
                                      sum(AMOUNT)
                                  from
                                      dbo.OPPORTUNITYDESIGNATION
                                  where
                                      OPPORTUNITYID=@ID
                              ),
                              0
                          )
                      else
                          case when ASKAMOUNT > 0 then ASKAMOUNT
                          else EXPECTEDASKAMOUNT
                          end
                      end
                  from
                      dbo.OPPORTUNITY
                  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
                        case when STATUSCODE = 3 then  -- Accepted

                            coalesce(
                                dbo.UFN_CURRENCY_CONVERT(ORGANIZATIONACCEPTEDASKAMOUNT, @CURRENCYEXCHANGERATEID),
                                (
                                    select
                                        sum(dbo.UFN_CURRENCY_CONVERT(ORGANIZATIONAMOUNT, @CURRENCYEXCHANGERATEID))
                                    from
                                        dbo.OPPORTUNITYDESIGNATION
                                    where
                                        OPPORTUNITYID=@ID
                                ),
                                0
                            )
                        else
                            case when ORGANIZATIONASKAMOUNT > 0 then dbo.UFN_CURRENCY_CONVERT(ORGANIZATIONASKAMOUNT, @CURRENCYEXCHANGERATEID)
                            else dbo.UFN_CURRENCY_CONVERT(ORGANIZATIONEXPECTEDASKAMOUNT, @CURRENCYEXCHANGERATEID)
                            end
                        end
                    from
                        dbo.OPPORTUNITY
                    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
                        case when STATUSCODE = 3 then  -- Accepted

                            coalesce(
                                dbo.UFN_CURRENCY_CONVERTINVERSE(ORGANIZATIONACCEPTEDASKAMOUNT, @CURRENCYEXCHANGERATEID),
                                (
                                    select
                                        sum(dbo.UFN_CURRENCY_CONVERTINVERSE(ORGANIZATIONAMOUNT, @CURRENCYEXCHANGERATEID))
                                    from
                                        dbo.OPPORTUNITYDESIGNATION
                                    where
                                        OPPORTUNITYID=@ID
                                ),
                                0
                            )
                        else
                            case when ORGANIZATIONASKAMOUNT > 0 then dbo.UFN_CURRENCY_CONVERTINVERSE(ORGANIZATIONASKAMOUNT, @CURRENCYEXCHANGERATEID)
                            else dbo.UFN_CURRENCY_CONVERTINVERSE(ORGANIZATIONEXPECTEDASKAMOUNT, @CURRENCYEXCHANGERATEID)
                            end
                        end
                   from
                        dbo.OPPORTUNITY
                    where
                        ID = @ID
                ),
                0
            )

      end      
    end
  end

  return @AMOUNT;
end