project QadFinancials > class BExchangeRate > API query SelectExchangeRate (optimised)

Description

Select query for exchange rate

Note:
because table Currency is used twice in the query, it's business fields cannot be used for filtering.
For this purpose, parameters icCurrFrom and icCurrTo were created, although this is against design rules.


query filter (table tFilter)


tcBusinessFieldNametcDataTypetcOperator
iiCompanyIdi
tCurrency2.CurrencyCodec=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency1.CurrencyCodec=,>=,>,<>,<=,<,begins,can-do,matches
tExchangeRate.ExchangeRated=,>=,>,<>,<=,<
tExchangeRate.ExchangeRateScaled=,>=,>,<>,<=,<
tExchangeRateType.ExchangeRateTypeCodec=,>=,>,<>,<=,<,begins,can-do,matches
tExchangeRate.ExchangeRateValidDateFromt=,>=,>,<>,<=,<
tExchangeRate.ExchangeRateValidDateTillt=,>=,>,<>,<=,<


Parameters (internal)


iiCompanyIdintegerCompany id


query condition


  each ExchangeRate where
ExchangeRate.SharedSet_Id = vi_EXCHANGERATE_sharedset(iiCompanyId) AND

      first Currency2 (inner-join) where
Currency2.Currency_ID = ExchangeRate.FromCurrency_ID AND

      first ExchangeRateType (inner-join) where
ExchangeRateType.ExchangeRateType_ID = ExchangeRate.ExchangeRateType_ID AND

      first Currency1 (inner-join) where
Currency1.Currency_ID = ExchangeRate.ToCurrency_ID AND


query sort order


Currency2.FromCurrencyCode (ascending)


query resultset tqSelectExchangeRate


field namedata typedb fielddescription
tdExchangeRatedecimalExchangeRate.ExchangeRateExchange Rate: This number is specified as the amount by which you multiply a single unit of a From Currency to reach the equivalent number of the To Currency units.
tdExchangeRateScaledecimalExchangeRate.ExchangeRateScaleScale Factor: A number used in the exchange rate calculation to adjust the amount of the From Currency.
Typically used in hyperinflationary environments when the differences between currency values is large.
api annotation:Value for this field defaults to 1.0000000 .
tcFromCurrencyCodecharacterCurrency2.CurrencyCodeCurrency Code
tcToCurrencyCodecharacterCurrency1.CurrencyCodeCurrency Code
tcExchangeRateTypeCodecharacterExchangeRateType.ExchangeRateTypeCodeA code identifying an exchange rate type. A number of types are predefined and required by the system.
ttExchangeRateValidDateFromdateExchangeRate.ExchangeRateValidDateFromValid from: The start date of the currency exchange relationship.
The effective period of an entry cannot overlap with another entry for the same relationship.
ttExchangeRateValidDateTilldateExchangeRate.ExchangeRateValidDateTillValid to: This field specify the date after which the exchange rate type becomes inactive.
api annotation:When creating a new exchange rate type, the system proposes a default validity end date based on the value you entered in the Default Validity field in Exchange Rate Type Create for the exchange rate type. However, you can overwrite the default value.
tiExchangeRate_IDintegerExchangeRate.ExchangeRate_IDid
tiFromCurrency_IDintegerExchangeRate.FromCurrency_IDFrom Currency Code
tiToCurrency_IDintegerExchangeRate.ToCurrency_IDTo Currency Code
tiExchangeRateType_IDintegerExchangeRate.ExchangeRateType_IDExchange Rate Type
tiSharedSet_IDintegerExchangeRate.SharedSet_IDLink to SharedSet
ExchangeRateCustomcharactercalculatedcustom fields
Currency1Customcharactercalculatedcustom fields
Currency2Customcharactercalculatedcustom fields
ExchangeRateTypeCustomcharactercalculatedcustom fields
ti_sequenceintegercalculatedprimary index
tc_rowidcharactercalculated = rowid(ExchangeRate),rowid(Currency2),rowid(ExchangeRateType),rowid(Currency1)


Internal usage


unused


Sample code: how to call this query 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 = "SelectExchangeRate".
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.selectexchangerate.i.xsd", ?).
vhParameter = vhInputDS:get-buffer-handle("tParameterI").
vhParameter:buffer-create().

assign vhParameter::icRange = "A"
       vhParameter::icRowid = ""
       vhParameter::iiRownum = 0
       vhParameter::iiNumber = 5 /* Number of records to read */
       vhParameter::icSortColumns = ""
       vhParameter::ilCountOnly = false
       vhParameter::ilForwardRead = true
       vhParameter::iiMaximumBrowseRecordsToCount = 0.

vhParameter = vhInputDS:get-buffer-handle("tFilter").
vhParameter:buffer-create().

assign vhParameter::<field-name-1> = <field-value-1>
       vhParameter::<field-name-2> = <field-value-2>
       ...

/* 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.