API query SelectExpNote (optimised)

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i
tCreditor.CreditorCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency.CurrencyCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tEmployee.EmployeeCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tExpNote.ExpNoteConfirmationDate	t	=,>=,>,<>,<=,<
tExpNote.ExpNoteDescription	c	=,>=,>,<>,<=,<,begins,can-do,matches
tExpNote.ExpNoteNumber	i	=,>=,>,<>,<=,<
tExpNote.ExpNoteRateLCXC	d	=,>=,>,<>,<=,<
tExpNote.ExpNoteRateScale	d	=,>=,>,<>,<=,<
tExpNote.ExpNoteRegistrationDate	t	=,>=,>,<>,<=,<
tExpNote.ExpNoteStatusCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tExpNote.ExpNoteYear	i	=,>=,>,<>,<=,<
tUsr1.UsrName	c	=,>=,>,<>,<=,<,begins,can-do,matches
tUsr2.UsrName	c	=,>=,>,<>,<=,<,begins,can-do,matches

Parameters (internal)

iiCompanyId

integer

Company id

query condition

each ExpNote where

ExpNote.Company_Id = iiCompanyId AND

first Currency (inner-join) where

Currency.Currency_ID = ExpNote.Currency_ID AND

first Employee (inner-join) where

Employee.Company_Id = iiCompanyId AND
Employee.Employee_ID = ExpNote.Employee_ID AND

first Creditor (inner-join) where

Creditor.SharedSet_Id = vi_CREDITOR_sharedset(iiCompanyId) AND
Creditor.Creditor_ID = Employee.Creditor_ID AND

first Usr1 (conditional-join) where

Usr1.Usr_ID = ExpNote.ConfirmationUsr_ID AND

first Usr2 (conditional-join) where

Usr2.Usr_ID = ExpNote.RegistrationUsr_ID AND

query sort order

ExpNote.Company_ID (ascending)
ExpNote.ExpNoteYear (ascending)
ExpNote.ExpNoteNumber (ascending)

query resultset tqSelectExpNote

field name	data type	db field	description
tiCompany_ID	integer	ExpNote.Company_ID	Link to Company
tiConfirmationUsr_ID	integer	ExpNote.ConfirmationUsr_ID	Link to Usr
tcCreditorCode	character	Creditor.CreditorCode	Supplier Code
tiCurrency_ID	integer	ExpNote.Currency_ID	Currency Code
tcCurrencyCode	character	Currency.CurrencyCode	Currency Code
tcCustomCombo0	character	ExpNote.CustomCombo0	Custom Combo Field
tcCustomCombo1	character	ExpNote.CustomCombo1	Custom Combo Field
tcCustomCombo2	character	ExpNote.CustomCombo2	Custom Combo Field
tcCustomCombo3	character	ExpNote.CustomCombo3	Custom Combo Field
tcCustomCombo4	character	ExpNote.CustomCombo4	Custom Combo Field
tcCustomCombo5	character	ExpNote.CustomCombo5	Custom Combo Field
tcCustomCombo6	character	ExpNote.CustomCombo6	Custom Combo Field
tcCustomCombo7	character	ExpNote.CustomCombo7	Custom Combo Field
tcCustomCombo8	character	ExpNote.CustomCombo8	Custom Combo Field
tcCustomCombo9	character	ExpNote.CustomCombo9	Custom Combo Field
ttCustomDate0	date	ExpNote.CustomDate0	Custom Date Field
ttCustomDate1	date	ExpNote.CustomDate1	Custom Date Field
ttCustomDate2	date	ExpNote.CustomDate2	Custom Date Field
ttCustomDate3	date	ExpNote.CustomDate3	Custom Date Field
ttCustomDate4	date	ExpNote.CustomDate4	Custom Date Field
tdCustomDecimal0	decimal	ExpNote.CustomDecimal0	Custom Decimal Field
tdCustomDecimal1	decimal	ExpNote.CustomDecimal1	Custom Decimal Field
tdCustomDecimal2	decimal	ExpNote.CustomDecimal2	Custom Decimal Field
tdCustomDecimal3	decimal	ExpNote.CustomDecimal3	Custom Decimal Field
tdCustomDecimal4	decimal	ExpNote.CustomDecimal4	Custom Decimal Field
tiCustomInteger0	integer	ExpNote.CustomInteger0	Custom Integer Field
tiCustomInteger1	integer	ExpNote.CustomInteger1	Custom Integer Field
tiCustomInteger2	integer	ExpNote.CustomInteger2	Custom Integer Field
tiCustomInteger3	integer	ExpNote.CustomInteger3	Custom Integer Field
tiCustomInteger4	integer	ExpNote.CustomInteger4	Custom Integer Field
tcCustomLong0	character	ExpNote.CustomLong0	Custom Long Field
tcCustomLong1	character	ExpNote.CustomLong1	Custom Long Field
tcCustomNote	character	ExpNote.CustomNote	Custom Note Field
tcCustomShort0	character	ExpNote.CustomShort0	Custom Short Field
tcCustomShort1	character	ExpNote.CustomShort1	Custom Short Field
tcCustomShort2	character	ExpNote.CustomShort2	Custom Short Field
tcCustomShort3	character	ExpNote.CustomShort3	Custom Short Field
tcCustomShort4	character	ExpNote.CustomShort4	Custom Short Field
tcCustomShort5	character	ExpNote.CustomShort5	Custom Short Field
tcCustomShort6	character	ExpNote.CustomShort6	Custom Short Field
tcCustomShort7	character	ExpNote.CustomShort7	Custom Short Field
tcCustomShort8	character	ExpNote.CustomShort8	Custom Short Field
tcCustomShort9	character	ExpNote.CustomShort9	Custom Short Field
tiEmployee_ID	integer	ExpNote.Employee_ID	Employee Code
tcEmployeeCode	character	Employee.EmployeeCode	Employee Code. A code that identifies an employee. The code cannot match any other employee in the current entity or any other entities in the current domain. If you leave the Employee Code field blank, the system automatically generates a number for the record based on the sequence defined in Employee Autonumber Create.
tiExpNote_ID	integer	ExpNote.ExpNote_ID	Record ID
ttExpNoteConfirmationDate	date	ExpNote.ExpNoteConfirmationDate	Confirmation Date
tcExpNoteDescription	character	ExpNote.ExpNoteDescription	Description
tiExpNoteNumber	integer	ExpNote.ExpNoteNumber	Number
tdExpNoteRateLCXC	decimal	ExpNote.ExpNoteRateLCXC	BC xc Exchange Rate
tdExpNoteRateScale	decimal	ExpNote.ExpNoteRateScale	Scale Factor
ttExpNoteRegistrationDate	date	ExpNote.ExpNoteRegistrationDate	Registration Date
tcExpNoteStatusCode	character	ExpNote.ExpNoteStatusCode	Status
tiExpNoteYear	integer	ExpNote.ExpNoteYear	Year
ttLastModifiedDate	date	ExpNote.LastModifiedDate	Last Modified Date
tiLastModifiedTime	integer	ExpNote.LastModifiedTime	Last Modified Time
tcLastModifiedUser	character	ExpNote.LastModifiedUser	Last Modified User
tiRegistrationUsr_ID	integer	ExpNote.RegistrationUsr_ID	Link to Usr
tcUsrName	character	Usr1.UsrName	User Name
tcUsrName2	character	Usr2.UsrName	User Name
ExpNoteCustom	character	calculated	custom fields
CurrencyCustom	character	calculated	custom fields
EmployeeCustom	character	calculated	custom fields
CreditorCustom	character	calculated	custom fields
Usr1Custom	character	calculated	custom fields
Usr2Custom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(ExpNote),rowid(Currency),rowid(Employee),rowid(Creditor),rowid(Usr1),rowid(Usr2)

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 = "BExpenseNote".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "SelectExpNote".
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/bexpensenote.selectexpnote.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.