USP_PAYMENT_APPLICATIONPAYMENTLOADCURRENCY

CREATE procedure dbo.USP_PAYMENT_APPLICATIONPAYMENTLOADCURRENCY
(
  @APPLICATIONID uniqueidentifier,
  @DATE datetime,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @PDACCOUNTSYSTEMID uniqueidentifier = null output,
  @SHOWACCOUNTSYSTEM bit = null output,
  @TRANSACTIONCURRENCYID uniqueidentifier = null output,
  @BASEEXCHANGERATEID uniqueidentifier = null output,
  @EXCHANGERATE decimal(20,8) = null output
)
as
  set nocount on;

  declare @APPLICATIONACCOUNTSYSTEMID uniqueidentifier;
  declare @APPLICATIONTRANSACTIONCURRENCYID uniqueidentifier;
  declare @APPLICATIONBASECURRENCYID uniqueidentifier;
  declare @ACCOUNTSYSTEMBASECURRENCYID uniqueidentifier;
  declare @BASECURRENCYID uniqueidentifier;

  select
    @APPLICATIONACCOUNTSYSTEMID = PDACCOUNTSYSTEMFORREVENUE.PDACCOUNTSYSTEMID,
    @APPLICATIONTRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
    @APPLICATIONBASECURRENCYID = REVENUE.BASECURRENCYID
  from
    dbo.REVENUE
    left join dbo.PDACCOUNTSYSTEMFORREVENUE on REVENUE.ID = PDACCOUNTSYSTEMFORREVENUE.ID
  where
    REVENUE.ID = @APPLICATIONID;


  if dbo.UFN_VALID_BASICGL_INSTALLED() != 0
    begin

      -- The user has rights to the account system saved on the application, so default this account system

      if exists(select 1 from dbo.UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CURRENTAPPUSERID) where ID = @APPLICATIONACCOUNTSYSTEMID)
      begin
        -- Only show the Account System if they have a choice.  Bug 104644

        --if dbo.UFN_PDACCOUNTSYSTEM_GETNUMBEROFSYSTEMSFORUSER(@CURRENTAPPUSERID) = 1

        --  set @SHOWACCOUNTSYSTEM = 0

        -- else

        --  set @SHOWACCOUNTSYSTEM = 1;


        --If the saved account system is found and defaulted, the user shouldn't have the ability to make a payment in a different account system than

        --the application

        set @SHOWACCOUNTSYSTEM = 0 

        set @PDACCOUNTSYSTEMID = @APPLICATIONACCOUNTSYSTEMID;
        set @TRANSACTIONCURRENCYID = @APPLICATIONTRANSACTIONCURRENCYID;
        set @BASECURRENCYID = @APPLICATIONBASECURRENCYID;
        select @BASEEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID,@BASECURRENCYID,@DATE,1,null);
      end

      -- Get the user's default account system.

      else
      begin
        declare    @NUMBEROFACCOUNTSYSTEMSFORUSER smallint;

        set @NUMBEROFACCOUNTSYSTEMSFORUSER = dbo.UFN_PDACCOUNTSYSTEM_GETNUMBEROFSYSTEMSFORUSER(@CURRENTAPPUSERID);
        if @NUMBEROFACCOUNTSYSTEMSFORUSER = 1
          begin
            set @SHOWACCOUNTSYSTEM = 0;
            select @PDACCOUNTSYSTEMID =  T1.ID from dbo.UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CURRENTAPPUSERID) as T1;
          end 
        else
          begin
            set @SHOWACCOUNTSYSTEM = 1;
            set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID);
          end 

        select 
          @ACCOUNTSYSTEMBASECURRENCYID = CURRENCYSET.BASECURRENCYID
        from 
          dbo.PDACCOUNTSYSTEM
          inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
        where
          PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID;

        set @BASECURRENCYID = @ACCOUNTSYSTEMBASECURRENCYID;

        -- If the transaction currency of the application is a valid transaction currency in the default currency set,

        -- use this transaction currency as the default on the payment.

        if exists 
        (
          select 1 
          from
            dbo.PDACCOUNTSYSTEM
            inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
            left join dbo.CURRENCYSETTRANSACTIONCURRENCY on CURRENCYSET.ID = CURRENCYSETTRANSACTIONCURRENCY.CURRENCYSETID
          where
            PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID
            and CURRENCYSETTRANSACTIONCURRENCY.CURRENCYID = @APPLICATIONTRANSACTIONCURRENCYID

        )
        begin
          set @TRANSACTIONCURRENCYID = @APPLICATIONTRANSACTIONCURRENCYID;
          select @BASEEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID,@BASECURRENCYID,@DATE,1,null);
        end

        else -- Use the base currency of the default currency set as the default transaction currency.

        begin
          set @TRANSACTIONCURRENCYID = @ACCOUNTSYSTEMBASECURRENCYID;
        end
      end
    end
  else
    begin
      set @SHOWACCOUNTSYSTEM = 0;
      set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B';
    end

  set @EXCHANGERATE =
    case
      when @BASEEXCHANGERATEID is not null
        then (select CURRENCYEXCHANGERATE.RATE from dbo.CURRENCYEXCHANGERATE where CURRENCYEXCHANGERATE.ID = @BASEEXCHANGERATEID)
      when @TRANSACTIONCURRENCYID = @BASECURRENCYID
        then 1
      else 0
    end;

  return 0;