API query SelectEmployee (optimised)

Description

Select on Employee

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i
tBusinessRelation.BusinessRelationCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tBusinessRelation.BusinessRelationName1	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCostCentre.CostCentreCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCreditor.CreditorCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDivision.DivisionCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tEmployee.EmployeeCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tEmployee.EmployeeDepartmentCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tEmployee.EmployeeIsActive	l	=,<>
tEmployee.EmployeeIsActiveExpNotes	l	=,<>
tEmployee.EmployeeIsActiveTimeReg	l	=,<>
tEmployee.EmployeeIsExternal	l	=,<>
tEmployee.EmployeeIsTravelOverSalary	l	=,<>
tEmployee.EmployeeIsUser	l	=,<>
tEmployee.EmployeeJobTitle	c	=,>=,>,<>,<=,<,begins,can-do,matches
tEmployee.EmployeeStartDate	t	=,>=,>,<>,<=,<
tUsr.UsrLogin	c	=,>=,>,<>,<=,<,begins,can-do,matches
tUsr.UsrName	c	=,>=,>,<>,<=,<,begins,can-do,matches

Parameters (internal)

iiCompanyId

integer

Company id

query condition

each Employee where

Employee.Company_Id = iiCompanyId AND

first Creditor (conditional-join) where

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

first Division (conditional-join) where

Division.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division.Division_ID = Employee.Division_ID AND

first Usr (conditional-join) where

Usr.Usr_ID = Employee.Usr_ID AND

first BusinessRelation (inner-join) where

BusinessRelation.BusinessRelation_ID = Employee.BusinessRelation_ID AND

first CostCentre (conditional-join) where

CostCentre.SharedSet_Id = vi_COSTCENTRE_sharedset(iiCompanyId) AND
CostCentre.CostCentre_ID = Employee.CostCentre_ID AND

query sort order

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

query resultset tqSelectEmployee

field name	data type	db field	description
tcBusinessRelationCode	character	BusinessRelation.BusinessRelationCode	Business Relation. A code to identify the business relation. If this field does not have a input from the user, the system automatically generates a number for the record based on the sequence defined in Business Relation Autonumber Create.
tcBusinessRelationName1	character	BusinessRelation.BusinessRelationName1	Name: The full name of the business relation. This field sets the default name for linked addresses such as customers and suppliers.
	api annotation:		PartialUpdate = yes - defaults to the BusinessRelationCode.
tiCompany_ID	integer	Employee.Company_ID	Link to Company
tcCostCentreCode	character	CostCentre.CostCentreCode	Cost Center: A code that identifies the cost center.
tcCreditorCode	character	Creditor.CreditorCode	Supplier Code
tcDivisionCode	character	Division.DivisionCode	Sub-Account
tcEmployeeDepartmentCode	character	Employee.EmployeeDepartmentCode	Department Code: The department in which this employee normally works.
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
tlEmployeeIsTravelOverSalary	logical	Employee.EmployeeIsTravelOverSalary	Include Travel Hours/Overtime
	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
tcEmployeeJobTitle	character	Employee.EmployeeJobTitle	Job Title: An optional job title for this employee. This field is for reference only and useful in sorting browses.
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
ttLastModifiedDate	date	Employee.LastModifiedDate	Last Modified Date
tiLastModifiedTime	integer	Employee.LastModifiedTime	Last Modified Time
tcLastModifiedUser	character	Employee.LastModifiedUser	Last Modified User
tiUsr_ID	integer	Usr.Usr_ID	Record ID
tcUsrLogin	character	Usr.UsrLogin	Login
tcUsrName	character	Usr.UsrName	User Name
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.
tlEmployeeIsActive	logical	Employee.EmployeeIsActive	Active: This field indicates if this is an active record.
	api annotation:		PartialUpdate = yes - defaults to true
tiEmployee_ID	integer	Employee.Employee_ID	Record ID
EmployeeCustom	character	calculated	custom fields
BusinessRelationCustom	character	calculated	custom fields
CostCentreCustom	character	calculated	custom fields
CreditorCustom	character	calculated	custom fields
DivisionCustom	character	calculated	custom fields
UsrCustom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(Employee),rowid(Creditor),rowid(Division),rowid(Usr),rowid(BusinessRelation),rowid(CostCentre)

Internal usage

QadFinancials

lookup query on tEmployee.EmployeeCode
lookup query on tBill.tcEmployeeCode
lookup query on tDebtor.tcEmployeeCode
lookup query on tExpNote.tcEmployeeCode

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 = "SelectEmployee".
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.selectemployee.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.