API query SelectEmployeeLinkedToCreditor (optimised)

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i
iiUsr_ID	i	=
tCreditor.CreditorCocNumber	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCreditor.CreditorCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCreditor.CreditorDBNumber	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCreditor.CreditorDebtorNumber	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCreditor.CreditorIsTaxable	l	=,<>
tCreditor.CreditorIsTaxInCity	l	=,<>
tCreditor.CreditorIsTaxIncluded	l	=,<>
tCreditor.CreditorIsTaxReport	l	=,<>
tCreditor.CreditorTaxDeclaration	i	=,>=,>,<>,<=,<
tCreditor.CreditorTaxIDMisc1	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCreditor.CreditorTaxIDMisc2	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCreditor.CreditorTaxIDMisc3	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCreditor.CreditorTIDNotice	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency.CurrencyCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tEmployee.EmployeeCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tEmployee.EmployeeEndDate	t	=,>=,>,<>,<=,<
tEmployee.EmployeeIsActive	l	=,<>
tEmployee.EmployeeIsActiveExpNotes	l	=,<>
tEmployee.EmployeeIsActiveTimeReg	l	=,<>
tEmployee.EmployeeIsExternal	l	=,<>
tEmployee.EmployeeIsUser	l	=,<>
tEmployee.EmployeeStartDate	t	=,>=,>,<>,<=,<
tCreditor.TxclTaxCls	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCreditor.TxuTaxUsage	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCreditor.TxzTaxZone	c	=,>=,>,<>,<=,<,begins,can-do,matches

Parameters (internal)

iiCompanyId	integer	Company id
iiUsr_ID	integer

query condition

each Employee where

Employee.Company_Id = iiCompanyId AND

first Creditor (inner-join) where

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

first Currency (inner-join) where

Currency.Currency_ID = Employee.Currency_ID AND

first Usr (conditional-join) where

Usr.Usr_ID = iiUsr_ID

query sort order

Employee.Company_ID (ascending)
Employee.EmployeeCode (ascending)

query resultset tqSelectEmployeeLinkedToCreditor

field name	data type	db field	description
tiActivityCode_ID	integer	Employee.ActivityCode_ID	Activity Code
tiBusinessRelation_ID	integer	Employee.BusinessRelation_ID	Business Relation
tiClaimPriceList_ID	integer	Employee.ObsoleteClaimPriceList_ID	Claim Price List
tiCompany_ID	integer	Employee.Company_ID	Link to Company
tiCostCentre_ID	integer	Employee.CostCentre_ID	Default Cost Center Code
tiCreditor_ID	integer	Employee.Creditor_ID	Supplier Code
tcCreditorCocNumber	character	Creditor.CreditorCocNumber	Chamber of Commerce Number
tcCreditorCode	character	Creditor.CreditorCode	Supplier Code
tcCreditorDBNumber	character	Creditor.CreditorDBNumber	Credit Agency Reference
tcCreditorDebtorNumber	character	Creditor.CreditorDebtorNumber	External Customer Number
tlCreditorIsTaxable	logical	Creditor.CreditorIsTaxable	Taxable Supplier
tlCreditorIsTaxInCity	logical	Creditor.CreditorIsTaxInCity	Tax in City
tlCreditorIsTaxIncluded	logical	Creditor.CreditorIsTaxIncluded	Tax Is Included
tlCreditorIsTaxReport	logical	Creditor.CreditorIsTaxReport	Tax Report
tiCreditorTaxDeclaration	integer	Creditor.CreditorTaxDeclaration	Tax Declaration
tcCreditorTaxIDMisc1	character	Creditor.CreditorTaxIDMisc1	Miscellaneous Tax 1
tcCreditorTaxIDMisc2	character	Creditor.CreditorTaxIDMisc2	Miscellaneous Tax 2
tcCreditorTaxIDMisc3	character	Creditor.CreditorTaxIDMisc3	Miscellaneous Tax 3
tcCreditorTIDNotice	character	Creditor.CreditorTIDNotice	TID Notice
tiCurrency_ID	integer	Employee.Currency_ID	Registration Currency
tcCurrencyCode	character	Currency.CurrencyCode	Currency Code
tcCustomCombo0	character	Employee.CustomCombo0	Custom Combo Field
tcCustomCombo1	character	Employee.CustomCombo1	Custom Combo Field
tcCustomCombo2	character	Employee.CustomCombo2	Custom Combo Field
tcCustomCombo3	character	Employee.CustomCombo3	Custom Combo Field
tcCustomCombo4	character	Employee.CustomCombo4	Custom Combo Field
tcCustomCombo5	character	Employee.CustomCombo5	Custom Combo Field
tcCustomCombo6	character	Employee.CustomCombo6	Custom Combo Field
tcCustomCombo7	character	Employee.CustomCombo7	Custom Combo Field
tcCustomCombo8	character	Employee.CustomCombo8	Custom Combo Field
tcCustomCombo9	character	Employee.CustomCombo9	Custom Combo Field
ttCustomDate0	date	Employee.CustomDate0	Custom Date Field
ttCustomDate1	date	Employee.CustomDate1	Custom Date Field
ttCustomDate2	date	Employee.CustomDate2	Custom Date Field
ttCustomDate3	date	Employee.CustomDate3	Custom Date Field
ttCustomDate4	date	Employee.CustomDate4	Custom Date Field
tdCustomDecimal0	decimal	Employee.CustomDecimal0	Custom Decimal Field
tdCustomDecimal1	decimal	Employee.CustomDecimal1	Custom Decimal Field
tdCustomDecimal2	decimal	Employee.CustomDecimal2	Custom Decimal Field
tdCustomDecimal3	decimal	Employee.CustomDecimal3	Custom Decimal Field
tdCustomDecimal4	decimal	Employee.CustomDecimal4	Custom Decimal Field
tiCustomInteger0	integer	Employee.CustomInteger0	Custom Integer Field
tiCustomInteger1	integer	Employee.CustomInteger1	Custom Integer Field
tiCustomInteger2	integer	Employee.CustomInteger2	Custom Integer Field
tiCustomInteger3	integer	Employee.CustomInteger3	Custom Integer Field
tiCustomInteger4	integer	Employee.CustomInteger4	Custom Integer Field
tcCustomLong0	character	Employee.CustomLong0	Custom Long Field
tcCustomLong1	character	Employee.CustomLong1	Custom Long Field
tcCustomNote	character	Employee.CustomNote	Custom Note Field
tcCustomShort0	character	Employee.CustomShort0	Custom Short Field
tcCustomShort1	character	Employee.CustomShort1	Custom Short Field
tcCustomShort2	character	Employee.CustomShort2	Custom Short Field
tcCustomShort3	character	Employee.CustomShort3	Custom Short Field
tcCustomShort4	character	Employee.CustomShort4	Custom Short Field
tcCustomShort5	character	Employee.CustomShort5	Custom Short Field
tcCustomShort6	character	Employee.CustomShort6	Custom Short Field
tcCustomShort7	character	Employee.CustomShort7	Custom Short Field
tcCustomShort8	character	Employee.CustomShort8	Custom Short Field
tcCustomShort9	character	Employee.CustomShort9	Custom Short Field
tiDivision_ID	integer	Employee.Division_ID	Default Sub-Account Code
tiEmployee_ID	integer	Employee.Employee_ID	Record ID
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.
ttEmployeeEndDate	date	Employee.EmployeeEndDate	End Date: The date when this employee terminated employment at your company. This field is for reference and filtering report data.
tlEmployeeIsActive	logical	Employee.EmployeeIsActive	Active: This field indicates if this is an active record.
	api annotation:		PartialUpdate = yes - defaults to true
tlEmployeeIsActiveExpNotes	logical	Employee.EmployeeIsActiveExpNotes	Obsolete field
tlEmployeeIsActiveTimeReg	logical	Employee.EmployeeIsActiveTimeReg	Obsolete field
tlEmployeeIsExternal	logical	Employee.EmployeeIsExternal	External Employee: Indicate if the employee is a contractor or not employed directly by your organization.
	api annotation:		PartialUpdate = yes - defaults to false
tlEmployeeIsUser	logical	Employee.EmployeeIsUser	User: Indicate if this employee is also defined as a valid user in User Maintenance.
	api annotation:		PartialUpdate = yes - defaults to false
ttEmployeeStartDate	date	Employee.EmployeeStartDate	Start Date: The date this employee was hired. This field is for reference and filtering report data.
	api annotation:		PartialUpdate = yes - defaults to the date the record was created
tiExpenseCode_ID	integer	Employee.ExpenseCode_ID	Expense Code
ttLastModifiedDate	date	Employee.LastModifiedDate	Last Modified Date
tiLastModifiedTime	integer	Employee.LastModifiedTime	Last Modified Time
tcLastModifiedUser	character	Employee.LastModifiedUser	Last Modified User
tcTxclTaxCls	character	Creditor.TxclTaxCls	Tax Class
tcTxuTaxUsage	character	Creditor.TxuTaxUsage	Tax Usage
tcTxzTaxZone	character	Creditor.TxzTaxZone	Tax Zone
tiUsr_ID	integer	Employee.Usr_ID	Login
EmployeeCustom	character	calculated	custom fields
CreditorCustom	character	calculated	custom fields
CurrencyCustom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(Employee),rowid(Creditor),rowid(Currency),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 = "BEmployee".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "SelectEmployeeLinkedToCreditor".
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/bemployee.selectemployeelinkedtocreditor.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.