Description
Get Exchange Rate
Parameters
| iiFromCurrencyID | input | integer | |
| iiToCurrencyID | input | integer | |
| iiExchangeRateTypeID | input | integer | |
| itDate | input | date | |
| iiSharedSetID | input | integer | Exchange Rate Shared Set ID |
| odExchangeRate | output | decimal | |
| odExchangeRateScale | output | decimal | |
| ocErrorMessage | output | character | |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program1/tsetstatutorycurrency.p)
assign oiReturnStatus = -98
viLocalReturnStatus = 0
odExchangeRate = ?
odExchangeRateScale = ?.
RATEBLOCK: do:
/* Validate input */
if iiSharedSetID = 0 or
iiSharedSetID = ? or
iiFromCurrencyID = 0 or
iiFromCurrencyID = ? or
iiToCurrencyID = 0 or
iiToCurrencyID = ? or
iiExchangeRateTypeID = 0 or
iiExchangeRateTypeID = ? or
itDate = ?
then do:
assign ocErrorMessage = "Missing information to retrieve the exchange-rate" + chr(10) +
"Shared-Set-Currency-ID: " + (if iiSharedSetID = ? then "?":U else string(iiSharedSetID)) + chr(10) +
"From-Currency-ID: " + (if iiFromCurrencyID = ? then "?":U else string(iiFromCurrencyID)) + chr(10) +
"To-Currency-ID: " + (if iiToCurrencyID = ? then "?":U else string(iiToCurrencyID)) + chr(10) +
"ExchangeRateType-ID: " + (if iiExchangeRateTypeID = ? then "?":U else string(iiExchangeRateTypeID)) + chr(10) +
"Date: " + (if itDate = ? then "?":U else string(itDate))
viLocalReturnStatus = -801.
leave RATEBLOCK.
end. /* If iiFromCurrencyID = 0 or */
if iiExchangeRateTypeID <> viACExchangeRateTypeID and
iiExchangeRateTypeID <> viSCExchangeRateTypeID and
iiExchangeRateTypeID <> viTXExchangeRateTypeID
then do :
assign ocErrorMessage = "Incorrect ExchangeRateType passed as input to retrieve the exchange-rate" + chr(10) +
"Exchange-rate-type-ID: " + (if iiExchangeRateTypeID = ? then "?":U else string(iiExchangeRateTypeID))
viLocalReturnStatus = -802.
leave RATEBLOCK.
end. /* If iiExchRateTypeID <> bACExchangeRateType.ExchangeRateType_ID */
/* Same currencies: return 1 */
if iiFromCurrencyID = iiToCurrencyID
then do :
assign odExchangeRate = 1
odExchangeRateScale = 1.
leave RATEBLOCK.
end. /* if iiFromCurrencyID = iiToCurrencyID */
/* ======================== Cascading rules ============================ */
/* We can only have 3 rate-types here: Statutory, Tax and Accounting */
/* For Tax, Stat is added to the list and if Stat allows fallback then */
/* Accounting is added as well. */
/* For Stat, Accounting is added if Stat allows fallback */
/* For Accounting, nothing is added */
/* ========================================================================== */
assign vcExchangeRateTypeIDs = string(iiExchangeRateTypeID).
if iiExchangeRateTypeID = viTXExchangeRateTypeID
then if vlSCIsFallBack = true
then assign vcExchangeRateTypeIDs = vcExchangeRateTypeIDs + ",":U +
string(viSCExchangeRateTypeID) + ",":U +
string(viACExchangeRateTypeID).
else assign vcExchangeRateTypeIDs = vcExchangeRateTypeIDs + ",":U +
string(viSCExchangeRateTypeID).
else if iiExchangeRateTypeID <> viACExchangeRateTypeID and
vlSCIsFallBack = true
then assign vcExchangeRateTypeIDs = vcExchangeRateTypeIDs + ",":U +
string(viACExchangeRateTypeID).
/* Lookup the rate (for the list of types) */
do viCounter = 1 to num-entries(vcExchangeRateTypeIDs):
assign viExchangeRateTypeID = integer(entry(viCounter, vcExchangeRateTypeIDs)).
/* Lookup multiply rate */
for each ExchangeRate where
ExchangeRate.SharedSet_Id = iiSharedSetID and
ExchangeRate.FromCurrency_ID = iiFromCurrencyID and
ExchangeRate.ToCurrency_ID = iiToCurrencyID and
ExchangeRate.ExchangeRateType_ID = viExchangeRateTypeID and
ExchangeRate.ExchangeRateValidDateFrom <= itDate and
ExchangeRate.ExchangeRateValidDateTill >= itDate
no-lock
by ExchangeRate.SharedSet_ID
by ExchangeRate.ExchangeRateType_ID
by ExchangeRate.FromCurrency_ID
by ExchangeRate.ToCurrency_ID
by ExchangeRate.ExchangeRateValidDateFrom :
assign odExchangeRate = ExchangeRate.ExchangeRate
odExchangeRateScale = (if ExchangeRate.ExchangeRateScale = 0 or
ExchangeRate.ExchangeRateScale = ?
then 1
else ExchangeRate.ExchangeRateScale).
leave. /* as we only need the first one */
end. /* for each */
if odExchangeRate <> ?
then leave RATEBLOCK.
/* Lookup divide rate */
for each ExchangeRate where
ExchangeRate.SharedSet_Id = iiSharedSetID and
ExchangeRate.FromCurrency_ID = iiToCurrencyID and
ExchangeRate.ToCurrency_ID = iiFromCurrencyID and
ExchangeRate.ExchangeRateType_ID = viExchangeRateTypeID and
ExchangeRate.ExchangeRateValidDateFrom <= itDate and
ExchangeRate.ExchangeRateValidDateTill >= itDate
no-lock
by ExchangeRate.SharedSet_ID
by ExchangeRate.ExchangeRateType_ID
by ExchangeRate.FromCurrency_ID
by ExchangeRate.ToCurrency_ID
by ExchangeRate.ExchangeRateValidDateFrom:
assign odExchangeRate = 1 / ExchangeRate.ExchangeRate
odExchangeRateScale = if ExchangeRate.ExchangeRateScale = 0 or
ExchangeRate.ExchangeRateScale = ?
then 1
else 1 / ExchangeRate.ExchangeRateScale.
leave. /* as we only need the first one */
end. /* for each */
if odExchangeRate <> ?
then leave RATEBLOCK.
/* ================================================================================================== */
/* We stop here as these types do not allow triangular rates to be used: Statutory, Inventory and VAT */
/* Type Inventory is already blocked before so we do not need to check on that one here */
/* ================================================================================================== */
if iiExchangeRateTypeID <> viSCExchangeRateTypeID and
iiExchangeRateTypeID <> viTXExchangeRateTypeID
then next.
/* In case the rate is not found, we try to find rates versus Local currency */
/* CurrFrom --> LC and LC --> CurrTO */
/* and then you have to divide the from rate into the to rate */
if iiFromCurrencyID <> viLocalCurrencyID and
iiToCurrencyID <> viLocalCurrencyID
then do:
assign vdFromExchangeRate = 0
vdFromExchangeRateScale = 0
vdToExchangeRate = 0
vdToExchangeRateScale = 0.
/* Lookup multiply rate */
for each ExchangeRate where
ExchangeRate.SharedSet_Id = iiSharedSetID and
ExchangeRate.FromCurrency_ID = iiFromCurrencyID and
ExchangeRate.ToCurrency_ID = viLocalCurrencyID and
ExchangeRate.ExchangeRateType_ID = viExchangeRateTypeID and
ExchangeRate.ExchangeRateValidDateFrom <= itDate and
ExchangeRate.ExchangeRateValidDateTill >= itDate
no-lock
by ExchangeRate.SharedSet_ID
by ExchangeRate.ExchangeRateType_ID
by ExchangeRate.FromCurrency_ID
by ExchangeRate.ToCurrency_ID
by ExchangeRate.ExchangeRateValidDateFrom:
assign vdFromExchangeRate = ExchangeRate.ExchangeRate
vdFromExchangeRateScale = ExchangeRate.ExchangeRateScale.
leave. /* as we only need the first one */
end. /* for each */
/* Lookup divide rate */
if vdFromExchangeRate = 0
then do:
for each ExchangeRate where
ExchangeRate.SharedSet_Id = iiSharedSetID and
ExchangeRate.FromCurrency_ID = viLocalCurrencyID and
ExchangeRate.ToCurrency_ID = iiFromCurrencyID and
ExchangeRate.ExchangeRateType_ID = viExchangeRateTypeID and
ExchangeRate.ExchangeRateValidDateFrom <= itDate and
ExchangeRate.ExchangeRateValidDateTill >= itDate
no-lock
by ExchangeRate.SharedSet_ID
by ExchangeRate.ExchangeRateType_ID
by ExchangeRate.FromCurrency_ID
by ExchangeRate.ToCurrency_ID
by ExchangeRate.ExchangeRateValidDateFrom:
assign vdFromExchangeRate = 1 / ExchangeRate.ExchangeRate
vdFromExchangeRateScale = 1 / ExchangeRate.ExchangeRateScale.
leave. /* as we only need the first one */
end. /* for each */
end. /* if vdFromExchangeRate = 0 */
if vdFromExchangeRate <> 0
then do:
/* Lookup multiply rate */
for each ExchangeRate where
ExchangeRate.SharedSet_Id = iiSharedSetID and
ExchangeRate.FromCurrency_ID = viLocalCurrencyID and
ExchangeRate.ToCurrency_ID = iiToCurrencyID and
ExchangeRate.ExchangeRateType_ID = viExchangeRateTypeID and
ExchangeRate.ExchangeRateValidDateFrom <= itDate and
ExchangeRate.ExchangeRateValidDateTill >= itDate
no-lock
by ExchangeRate.SharedSet_ID
by ExchangeRate.ExchangeRateType_ID
by ExchangeRate.FromCurrency_ID
by ExchangeRate.ToCurrency_ID
by ExchangeRate.ExchangeRateValidDateFrom:
assign vdToExchangeRate = ExchangeRate.ExchangeRate
vdToExchangeRateScale = ExchangeRate.ExchangeRateScale.
leave. /* as we only need the first one */
end. /* for each */
/* Lookup divide rate */
if vdToExchangeRate = 0
then do:
for each ExchangeRate where
ExchangeRate.SharedSet_Id = iiSharedSetID and
ExchangeRate.FromCurrency_ID = iiToCurrencyID and
ExchangeRate.ToCurrency_ID = viLocalCurrencyID and
ExchangeRate.ExchangeRateType_ID = viExchangeRateTypeID and
ExchangeRate.ExchangeRateValidDateFrom <= itDate and
ExchangeRate.ExchangeRateValidDateTill >= itDate
no-lock
by ExchangeRate.SharedSet_ID
by ExchangeRate.ExchangeRateType_ID
by ExchangeRate.FromCurrency_ID
by ExchangeRate.ToCurrency_ID
by ExchangeRate.ExchangeRateValidDateFrom:
assign vdToExchangeRate = 1 / ExchangeRate.ExchangeRate
vdToExchangeRateScale = 1 / ExchangeRate.ExchangeRateScale.
leave. /* as we only need the first one */
end. /* for each */
end. /* if vdFromExchangeRate = 0 */
assign odExchangeRate = if vdFromExchangeRate = 0 or vdToExchangeRate = 0
then 0
else vdFromExchangeRate / vdToExchangeRate.
assign odExchangeRateScale = if vdFromExchangeRateScale = 0 or vdToExchangeRateScale = 0
then 0
else vdFromExchangeRateScale / vdToExchangeRateScale.
end. /* if vdFromExchangeRate <> 0 */
end. /* if iiFromCurrencyID <> viLocalCurrencyID and */
/* do not continue in cascading search if the odExchangeRate was found/calculated */
if odExchangeRate <> 0 and
odExchangeRate <> ?
then leave RATEBLOCK.
end. /* do viCounter = 1 to num-entries(vcExchangeRateTypeIDs): */
end. /* RATEBLOCK */
if odExchangeRate = ? and
viLocalReturnStatus >= 0
then do :
assign viLocalReturnStatus = -803
ocErrorMessage = "Unable to get the correct exchange-rate with following parameters: "+ chr(10) +
"Date: " + (if itDate = ? then "?":U else string(itDate)).
Find SharedSet where
SharedSet.SharedSet_ID = iiSharedSetID
no-lock no-error.
if available SharedSet
then assign ocErrorMessage = ocErrorMessage + chr(10) +
"Currency-Shared-Set: " + SharedSet.SharedSetCode.
Find Currency where
Currency.Currency_ID = iiFromCurrencyID
no-lock no-error.
if available Currency
then assign ocErrorMessage = ocErrorMessage + chr(10) +
"From Currency: " + Currency.CurrencyCode.
Find Currency where
Currency.Currency_ID = iiToCurrencyID
no-lock no-error.
if available Currency
then assign ocErrorMessage = ocErrorMessage + chr(10) +
"To Currency: " + Currency.CurrencyCode.
Find ExchangeRateType where
ExchangeRateType.ExchangeRateType_ID = iiExchangeRateTypeID
no-lock no-error.
if available ExchangeRateType
then assign ocErrorMessage = ocErrorMessage + chr(10) +
"ExchangeRateType: " + ExchangeRateType.ExchangeRateTypeCode.
assign ocErrorMessage = ocErrorMessage + chr(10) +
"Currency-Shared-Set-ID: " + (if iiSharedSetID = ? then "?":U else string(iiSharedSetID)) + chr(10) +
"From-Currency-ID: " + (if iiFromCurrencyID = ? then "?":U else string(iiFromCurrencyID)) + chr(10) +
"To-Currency-ID: " + (if iiToCurrencyID = ? then "?":U else string(iiToCurrencyID)) + chr(10) +
"ExchangeRateType-ID: " + (if iiExchangeRateTypeID = ? then "?":U else string(iiExchangeRateTypeID)).
end. /* if odExchangeRate = ? and */
assign oiReturnStatus = viLocalReturnStatus.