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
| iiFromCurrencyId | input | integer | From Currency Id |
| icFromCurrencyCode | input | character | From Currency Code |
| iiToCurrencyId | input | integer | To Currency Id |
| icToCurrencyCode | input | character | To Curency Code |
| iiExchangeRateTypeId | input | integer | Exchange Rate Type Id |
| icExchangeRateTypeCode | input | character | Exchange Rate Type Code |
| itFromValidityDate | input | date | From Validity Date |
| itToValidityDate | input | date | To Validity Date |
| odAverageMultiplyExchangeRate | output | decimal | Average Multiply Exchange Rate |
| odAverageExchangeRateScaleFactor | output | decimal | Average Exchange Rate Scale Factor |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
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.