project QadFinancials > class BExchangeRate > method ApiGetAverageExchangeRate

Description

ApiGetAverageExchangeRate: Get the average exchange rate based on the number of days an exchange rate has been used (= Weighted Average).
If you run this method, you don't have to start the instance, only set the vhFcComponent = ?.


Parameters


iiFromCurrencyIdinputintegerFrom Currency Id
icFromCurrencyCodeinputcharacterFrom Currency Code
iiToCurrencyIdinputintegerTo Currency Id
icToCurrencyCodeinputcharacterTo Curency Code
iiExchangeRateTypeIdinputintegerExchange Rate Type Id
icExchangeRateTypeCodeinputcharacterExchange Rate Type Code
itFromValidityDateinputdateFrom Validity Date
itToValidityDateinputdateTo Validity Date
odAverageMultiplyExchangeRateoutputdecimalAverage Multiply Exchange Rate
odAverageExchangeRateScaleFactoroutputdecimalAverage Exchange Rate Scale Factor
oiReturnStatusoutputintegerReturn status of the method.


Internal usage


QadFinancials
method BConsolid.GetExchangeRatesWeightedAverage


program code (program9/bexchangerate.p)

/* This method will return the average exchange rate between the two dates you entered, */
/* based on the number of days an exchange rate has been used                           */

assign viLocalReturnStatus = oiReturnStatus
       oiReturnStatus      = -98.
       
/* ====================== */
/* replace unknown values */
/* ====================== */
if iiFromCurrencyId = ? then assign iiFromCurrencyId = 0.
if icFromCurrencyCode = ? then assign icFromCurrencyCode = "":U.
if iiToCurrencyId = ? then assign iiToCurrencyId = 0.
if icToCurrencyCode = ? then assign icToCurrencyCode = "":U.
if iiExchangeRateTypeId = ? then assign iiExchangeRateTypeId = 0.
if icExchangeRateTypeCode = ? then assign icExchangeRateTypeCode = "":U.
assign odAverageExchangeRateScaleFactor = 1.

/* ======================================== */
/* when id filled, code can be set to blank */
/* ======================================== */
if iiFromCurrencyId <> 0 then assign icFromCurrencyCode = "":U.
if iiToCurrencyId <> 0 then assign icToCurrencyCode = "":U.
if iiExchangeRateTypeId <> 0 then assign icExchangeRateTypeCode = "":U.

/* ====================== */
/* Test the ValidityDates */
/* ====================== */
assign vcMessage = "":U.
if itFromValidityDate = ? or
   itToValidityDate   = ?
then assign vcMessage = trim(#T-18'You must enter both Validity Dates.':200(61735)t-18#).
else if itToValidityDate < itFromValidityDate
     then assign vcMessage = trim(substitute(#T-19'The To Validity Date (&1) cannot be earlier than the From Validity Date (&2).':100(61736)t-19#, trim(string(itToValidityDate)), trim(string(itFromValidityDate)) )).
if vcMessage <> "":U
then do:
    <M-20 run SetMessage
       (input  vcMessage (icMessage), 
        input  '':U (icArguments), 
        input  '':U (icFieldName), 
        input  '':U (icFieldValue), 
        input  'E' (icType), 
        input  3 (iiSeverity), 
        input  '':U (icRowid), 
        input  'QadFin-6326':U (icFcMsgNumber), 
        input  '':U (icFcExplanation), 
        input  '':U (icFcIdentification), 
        input  '':U (icFcContext), 
        output viFcReturnSuper (oiReturnStatus)) in BExchangeRate>
    assign oiReturnStatus = -1.
    return.
end. /* if vcMessage <> "":U */

/* ========================== */
/* from currency is mandatory */
/* ========================== */
if iiFromCurrencyId = 0 and icFromCurrencyCode = "":U
then do:
    assign vcMessage      = trim(#T-14'You must enter a value in the From Currency field.':150(61731)t-14#)
           oiReturnStatus = -1.
    <M-1 run SetMessage
       (input  vcMessage (icMessage), 
        input  '':U (icArguments), 
        input  '':U (icFieldName), 
        input  '':U (icFieldValue), 
        input  'E':U (icType), 
        input  3 (iiSeverity), 
        input  '':U (icRowid), 
        input  'QadFin-6322':U (icFcMsgNumber), 
        input  '' (icFcExplanation), 
        input  '' (icFcIdentification), 
        input  '' (icFcContext), 
        output viFcReturnSuper (oiReturnStatus)) in BExchangeRate>
    return.
end.

/* ======================= */
/* default when not filled */
/* ======================= */
if iiToCurrencyId = 0 and icToCurrencyCode = "":U
then assign iiToCurrencyId = viCompanyLCId.
if iiExchangeRateTypeId = 0 and icExchangeRateTypeCode = "":U
then assign icExchangeRateTypeCode = {&EXCHANGERATETYPE-ACCOUNTING}.

/* ======================================== */
/* check if it is an existing from currency */
/* ======================================== */
<Q-2 run CurrencyPrim (all) (Read) (NoCache)
   (input icFromCurrencyCode, (CurrencyCode)
    input iiFromCurrencyId, (Currency_ID)
    output dataset tqCurrencyPrim) in BCurrency >
find first tqCurrencyPrim no-error.
if not available tqCurrencyPrim
then do:
    assign vcMessage      = trim(#T-15'The currency code specified in the From Currency field is not defined in the system.':150(61732)t-15#)
           oiReturnStatus = -1.
    <M-3 run SetMessage
       (input  vcMessage (icMessage), 
        input  '':U (icArguments), 
        input  '':U (icFieldName), 
        input  '':U (icFieldValue), 
        input  'E':U (icType), 
        input  3 (iiSeverity), 
        input  '':U (icRowid), 
        input  'QadFin-6323':U (icFcMsgNumber), 
        input  '' (icFcExplanation), 
        input  '' (icFcIdentification), 
        input  '' (icFcContext), 
        output viFcReturnSuper (oiReturnStatus)) in BExchangeRate>
    return.
end.
assign iiFromCurrencyId = tqCurrencyPrim.tiCurrency_ID.

/* ====================================== */
/* check if it is an existing to currency */
/* ====================================== */
if iiToCurrencyId <> 0 or icToCurrencyCode <> "":U
then do:
    <Q-4 run CurrencyPrim (all) (Read) (NoCache)
       (input icToCurrencyCode, (CurrencyCode)
        input iiToCurrencyId, (Currency_ID)
        output dataset tqCurrencyPrim) in BCurrency >
    find first tqCurrencyPrim no-error.
    if not available tqCurrencyPrim
    then do:
        assign vcMessage      = trim(#T-16'The currency code specified in the To Currency field is not defined in the system.':150(61733)t-16#)
               oiReturnStatus = -1.
        <M-5 run SetMessage
           (input  vcMessage (icMessage), 
            input  '':U (icArguments), 
            input  '':U (icFieldName), 
            input  '':U (icFieldValue), 
            input  'E':U (icType), 
            input  3 (iiSeverity), 
            input  '':U (icRowid), 
            input  'QadFin-6324':U (icFcMsgNumber), 
            input  '' (icFcExplanation), 
            input  '' (icFcIdentification), 
            input  '' (icFcContext), 
            output viFcReturnSuper (oiReturnStatus)) in BExchangeRate>
        return.
    end.
    assign iiToCurrencyId = tqCurrencyPrim.tiCurrency_ID.
end.

/* ============================================= */
/* check if it is an existing exchange rate type */
/* ============================================= */
if iiExchangeRateTypeId <> 0 or icExchangeRateTypeCode <> "":U
then do:
    <Q-6 run ExchangeRateTypePrim (all) (Read) (NoCache)
       (input iiExchangeRateTypeId, (ExchangeRateTypeId)
        input icexchangeRateTypeCode, (ExchangeRateTypeCode)
        output dataset tqExchangeRateTypePrim) in BExchangeRateType >
    find tqExchangeRateTypePrim no-error.
    if not available tqExchangeRateTypePrim
    then do:
        assign vcMessage      = trim(#T-17'The exchange rate type is not defined in the system.':150(61734)t-17#)
               oiReturnStatus = -1.
        <M-7 run SetMessage
           (input  vcMessage (icMessage), 
            input  '':U (icArguments), 
            input  '':U (icFieldName), 
            input  '':U (icFieldValue), 
            input  'E':U (icType), 
            input  3 (iiSeverity), 
            input  '':U (icRowid), 
            input  'QadFin-6325':U (icFcMsgNumber), 
            input  '' (icFcExplanation), 
            input  '' (icFcIdentification), 
            input  '' (icFcContext), 
            output viFcReturnSuper (oiReturnStatus)) in BExchangeRate>
        return.
    end.
end.

/* ======================================================= */
/* if from and to currency is the same, then set rate to 1 */
/* ======================================================= */
if iiFromCurrencyId = iiToCurrencyId
then do:
    assign odAverageMultiplyExchangeRate    = 1
           odAverageExchangeRateScaleFactor = 1
           oiReturnStatus                   = 0.
    return.
end. /* if iiFromCurrencyId = iiToCurrencyId */

/* Lookup the Multiply Rate */
/* ====================== */
/* Lookup multiply rates  */
/* ====================== */
assign vlDivide = false.
<Q-22 run ExchangeRateByCurrTypeFrToDate (all) (Read) (NoCache)
   (input iiFromCurrencyId, (FromCurrencyId)
    input iiToCurrencyId, (ToCurrencyId)
    input iiExchangeRateTypeId, (ExchangeRateTypeId)
    input itFromValidityDate, (ExchRateFromValidityDate)
    input icFromCurrencyCode, (FromCurrencyCode)
    input icToCurrencyCode, (ToCurrencyCode)
    input icExchangeRateTypeCode, (ExchangeRateType)
    input viCompanyId, (CompanyId)
    input itToValidityDate, (ExchRateToValidityDate)
    output dataset tqExchangeRateByCurrTypeFrToDate) in BExchangeRate >
if not can-find(first tqExchangeRateByCurrTypeFrToDate)
then do:
    <Q-23 run ExchangeRateByCurrTypeFrToDate (all) (Read) (NoCache)
       (input iiToCurrencyId, (FromCurrencyId)
        input iiFromCurrencyId, (ToCurrencyId)
        input iiExchangeRateTypeId, (ExchangeRateTypeId)
        input itFromValidityDate, (ExchRateFromValidityDate)
        input icToCurrencyCode, (FromCurrencyCode)
        input icFromCurrencyCode, (ToCurrencyCode)
        input icExchangeRateTypeCode, (ExchangeRateType)
        input viCompanyId, (CompanyId)
        input itToValidityDate, (ExchRateToValidityDate)
        output dataset tqExchangeRateByCurrTypeFrToDate) in BExchangeRate >
    if not can-find(first tqExchangeRateByCurrTypeFrToDate)
    then do:
        assign vcMessage      = trim(#T-24'No exchange rates are defined for the dates you selected.':100(61741)t-24#)
               oiReturnStatus = -1.
        <M-25 run SetMessage
           (input  vcMessage (icMessage), 
            input  '':U (icArguments), 
            input  '':U (icFieldName), 
            input  '':U (icFieldValue), 
            input  'E':U (icType), 
            input  3 (iiSeverity), 
            input  '':U (icRowid), 
            input  'QadFin-6328':U (icFcMsgNumber), 
            input  '':U (icFcExplanation), 
            input  '':U (icFcIdentification), 
            input  '':U (icFcContext), 
            output viFcReturnSuper (oiReturnStatus)) in BExchangeRate>
        return.
    end.
    else assign vlDivide = true.
end.

assign viTotalDays = 0.
do viDaysCnt = int(itToValidityDate) to int(itFromValidityDate) by -1: 
    find tqExchangeRateByCurrTypeFrToDate where
         tqExchangeRateByCurrTypeFrToDate.ttExchangeRateValidDateFrom <= date(viDaysCnt) and
         tqExchangeRateByCurrTypeFrToDate.ttExchangeRateValidDateTill >= date(viDaysCnt)
         no-error.
    if not available tqExchangeRateByCurrTypeFrToDate
    then do:
        assign vcMessage      = trim(substitute(#T-26'There is no exchange rate record for &1.':100(61747)t-26#, string(date(viDaysCnt)) ))
               oiReturnStatus = -1.
        <M-27 run SetMessage
           (input  vcMessage (icMessage), 
            input  '':U (icArguments), 
            input  '':U (icFieldName), 
            input  '':U (icFieldValue), 
            input  'E':U (icType), 
            input  3 (iiSeverity), 
            input  '':U (icRowid), 
            input  'QadFin-6329':U (icFcMsgNumber), 
            input  '':U (icFcExplanation), 
            input  '':U (icFcIdentification), 
            input  '':U (icFcContext), 
            output viFcReturnSuper (oiReturnStatus)) in BExchangeRate>
        return.
    end. /* if not available tqExchangeRateByCurrTypeFrToDate */
    assign viTotalDays = viTotalDays + 1
           vdTotalExchangeRate = vdTotalExchangeRate + (tqExchangeRateByCurrTypeFrToDate.tdExchangeRate * tqExchangeRateByCurrTypeFrToDate.tdExchangeRateScale).    
end. /* for viDaysCnt = itToValidityDate to itFromValidityDate by -1: */

assign odAverageMultiplyExchangeRate = vdTotalExchangeRate / viTotalDays
       odAverageExchangeRateScaleFactor = 1.
if vlDivide = true
then assign odAverageMultiplyExchangeRate = 1 / odAverageMultiplyExchangeRate no-error.
       
assign oiReturnStatus = viLocalReturnStatus.


Sample code: how to call this method through RPCRequestService (QXtend Inbound)

define temp-table ttContext no-undo
    field propertyQualifier as character
    field propertyName as character
    field propertyValue as character
    index entityContext is primary unique
        propertyQualifier
        propertyName
    index propertyQualifier
        propertyQualifier.

define dataset dsContext for ttContext.

define variable vhContextDS as handle no-undo.
define variable vhExceptionDS as handle no-undo.
define variable vhServer as handle no-undo.
define variable vhInputDS as handle no-undo.
define variable vhInputOutputDS as handle no-undo.
define variable vhOutputDS as handle no-undo.
define variable vhParameter as handle no-undo.

/* Create context */
create ttContext.
assign ttContext.propertyName = "programName"
       ttContext.propertyValue = "BExchangeRate".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "ApiGetAverageExchangeRate".
create ttContext.
assign ttContext.propertyName = "applicationId"
       ttContext.propertyValue = "fin".
create ttContext.
assign ttContext.propertyName = "entity"
       ttContext.propertyValue = "1000".
create ttContext.
assign ttContext.propertyName = "userName"
       ttContext.propertyValue = "mfg".
create ttContext.
assign ttContext.propertyName = "password"
       ttContext.propertyValue = "".

/* Create input dataset */
create dataset vhInputDS.
vhInputDS:read-xmlschema("file", "xml/bexchangerate.apigetaverageexchangerate.i.xsd", ?).
vhParameter = vhInputDS:get-buffer-handle("tParameterI").
vhParameter:buffer-create().
assign vhParameter::iiFromCurrencyId = <parameter value>
       vhParameter::icFromCurrencyCode = <parameter value>
       vhParameter::iiToCurrencyId = <parameter value>
       vhParameter::icToCurrencyCode = <parameter value>
       vhParameter::iiExchangeRateTypeId = <parameter value>
       vhParameter::icExchangeRateTypeCode = <parameter value>
       vhParameter::itFromValidityDate = <parameter value>
       vhParameter::itToValidityDate = <parameter value>.

/* Connect the AppServer */
create server vhServer.
vhServer:connect("-URL <appserver-url>").

if not vhServer:connected()
then do:
    message "Could not connect AppServer" view-as alert-box error title "Error".
    return.
end.

/* Run */
assign vhContextDS = dataset dsContext:handle.

run program/rpcrequestservice.p on vhServer
    (input-output dataset-handle vhContextDS by-reference,
           output dataset-handle vhExceptionDS,
     input        dataset-handle vhInputDS by-reference,
     input-output dataset-handle vhInputOutputDS by-reference,
           output dataset-handle vhOutputDS).

/* Handle output however you want, in this example, we dump it to xml */
if valid-handle(vhExceptionDS)
then vhExceptionDS:write-xml("file", "Exceptions.xml", true).

if valid-handle(vhOutputDS)
then vhOutputDS:write-xml("file", "Output.xml", true).

/* Cleanup */
vhServer:disconnect().
assign vhServer = ?.

if valid-handle(vhInputDS)
then delete object vhInputDS.

if valid-handle(vhOutputDS)
then delete object vhOutputDS.

if valid-handle(vhExceptionDS)
then delete object vhExceptionDS.