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)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i
tCurrency2.CurrencyCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency1.CurrencyCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tExchangeRate.ExchangeRate	d	=,>=,>,<>,<=,<
tExchangeRate.ExchangeRateScale	d	=,>=,>,<>,<=,<
tExchangeRateType.ExchangeRateTypeCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tExchangeRate.ExchangeRateValidDateFrom	t	=,>=,>,<>,<=,<
tExchangeRate.ExchangeRateValidDateTill	t	=,>=,>,<>,<=,<

Parameters (internal)

iiCompanyId

integer

Company 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 name	data type	db field	description
tdExchangeRate	decimal	ExchangeRate.ExchangeRate	Exchange 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.
tdExchangeRateScale	decimal	ExchangeRate.ExchangeRateScale	Scale 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 .
tcFromCurrencyCode	character	Currency2.CurrencyCode	Currency Code
tcToCurrencyCode	character	Currency1.CurrencyCode	Currency Code
tcExchangeRateTypeCode	character	ExchangeRateType.ExchangeRateTypeCode	A code identifying an exchange rate type. A number of types are predefined and required by the system.
ttExchangeRateValidDateFrom	date	ExchangeRate.ExchangeRateValidDateFrom	Valid from: The start date of the currency exchange relationship. The effective period of an entry cannot overlap with another entry for the same relationship.
ttExchangeRateValidDateTill	date	ExchangeRate.ExchangeRateValidDateTill	Valid 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_ID	integer	ExchangeRate.ExchangeRate_ID	id
tiFromCurrency_ID	integer	ExchangeRate.FromCurrency_ID	From Currency Code
tiToCurrency_ID	integer	ExchangeRate.ToCurrency_ID	To Currency Code
tiExchangeRateType_ID	integer	ExchangeRate.ExchangeRateType_ID	Exchange Rate Type
tiSharedSet_ID	integer	ExchangeRate.SharedSet_ID	Link to SharedSet
ExchangeRateCustom	character	calculated	custom fields
Currency1Custom	character	calculated	custom fields
Currency2Custom	character	calculated	custom fields
ExchangeRateTypeCustom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= 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.