API query SelectReportVariant (optimised)

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i	=
tBusActivity.BusActivityCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tBusActivity.BusActivityLabel	c	=,>=,>,<>,<=,<,begins,can-do,matches
tBusComponent.BusComponentCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tBusComponent.BusComponentLabel	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCompany.CompanyCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tLng.LngCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tRole.RoleName	c	=,>=,>,<>,<=,<,begins,can-do,matches
tRptPrinter.RptPrinterName	c	=,>=,>,<>,<=,<,begins,can-do,matches
tRptRequest.RptRequestDescription	c	=,>=,>,<>,<=,<,begins,can-do,matches
tRptRequest.RptRequestName	c	=,>=,>,<>,<=,<,begins,can-do,matches
tUsr.UsrLogin	c	=,>=,>,<>,<=,<,begins,can-do,matches
tUsr.UsrName	c	=,>=,>,<>,<=,<,begins,can-do,matches

Parameters (internal)

iiCompanyId

integer

Company id

query condition

each RptRequestVariant where

RptRequestVariant.Company_Id = iiCompanyId AND
(RptRequestVariant.Company_ID = iiCompanyId OR
RptRequestVariant.Company_ID = 0 )

first Company (conditional-join) where

Company.Company_Id = iiCompanyId AND
Company.Company_ID = RptRequestVariant.Company_ID AND

first Role (conditional-join) where

Role.Role_ID = RptRequestVariant.Role_ID AND

first RptRequest (inner-join) where

RptRequest.RptRequest_ID = RptRequestVariant.RptRequest_ID AND

first BusActivity (conditional-join) where

BusActivity.BusActivity_ID = RptRequest.BusActivity_ID AND

first BusComponent (conditional-join) where

BusComponent.BusComponent_ID = BusActivity.BusComponent_ID AND

first Lng (conditional-join) where

Lng.Lng_ID = RptRequest.Lng_ID AND

first RptPrinter (conditional-join) where

RptPrinter.RptPrinter_ID = RptRequest.RptPrinter_ID AND

first Usr (conditional-join) where

Usr.Usr_ID = RptRequestVariant.Usr_ID AND

query sort order

RptRequest.RptRequestName (ascending)

query resultset tqSelectReportVariant

field name	data type	db field	description
tiBusActivity_ID	integer	BusActivity.BusActivity_ID	Record ID
tiBusComponent_ID	integer	BusComponent.BusComponent_ID	BusComponent_ID
tiCompany_ID	integer	RptRequestVariant.Company_ID	Entity
tiRole_ID	integer	RptRequestVariant.Role_ID	Role
tiUsr_ID	integer	RptRequestVariant.Usr_ID	User
tcRptRequestName	character	RptRequest.RptRequestName	Name
tcRptRequestDescription	character	RptRequest.RptRequestDescription	Description
tcCompanyCode	character	Company.CompanyCode	Entity Code
tcUsrLogin	character	Usr.UsrLogin	Login
tcUsrName	character	Usr.UsrName	User Name
tcRoleName	character	Role.RoleName	Role Name
tcBusComponentCode	character	BusComponent.BusComponentCode	Component
tcBusComponentLabel	character	BusComponent.BusComponentLabel	Label
	api annotation:		test annotation for BusComponentLabel
tcBusActivityCode	character	BusActivity.BusActivityCode	Code
tcBusActivityLabel	character	BusActivity.BusActivityLabel	Activity
tcLngCode	character	Lng.LngCode	Language Code
tcRptPrinterName	character	RptPrinter.RptPrinterName	Printer Name
tiRptRequest_ID	integer	RptRequest.RptRequest_ID	Record ID
RptRequestVariantCustom	character	calculated	custom fields
CompanyCustom	character	calculated	custom fields
RoleCustom	character	calculated	custom fields
RptRequestCustom	character	calculated	custom fields
BusActivityCustom	character	calculated	custom fields
BusComponentCustom	character	calculated	custom fields
LngCustom	character	calculated	custom fields
RptPrinterCustom	character	calculated	custom fields
UsrCustom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(RptRequestVariant),rowid(Company),rowid(Role),rowid(RptRequest),rowid(BusActivity),rowid(BusComponent),rowid(Lng),rowid(RptPrinter),rowid(Usr)

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 = "BReportVariant".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "SelectReportVariant".
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/breportvariant.selectreportvariant.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.