API query SelectDeductionCat (optimised)

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i
iiDomainId	i	=
tCostCentre.CostCentreCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDeductionCat.DeductionCatCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDeductionCat.DeductionCatDescription	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDeductionCat.DeductionCatIsActive	l	=,<>
tDeductionCatLine.DeductionCatLineIsAutoWOff	l	=,<>
tDeductionCatLine.DeductionCatLineWOffLimit	d	=,>=,>,<>,<=,<
tDeductionCat.DeductionCatType	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDivision.DivisionCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL.GLCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tJournal2.JournalCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tJournal1.JournalCode	c	=,>=,>,<>,<=,<,begins,can-do,matches

Parameters (internal)

iiCompanyId	integer	Company id
iiDomainId	integer

query condition

each DeductionCat where

each DeductionCatLine (outer-join) where

DeductionCatLine.DeductionCat_ID = DeductionCat.DeductionCat_ID AND
DeductionCatLine.Domain_ID = iiDomainId

first CostCentre (conditional-join) where

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

first Division (conditional-join) where

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

first GL (conditional-join) where

GL.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL.GL_ID = DeductionCatLine.GL_ID AND

first Journal1 (conditional-join) where

Journal1.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal1.Journal_ID = DeductionCatLine.ExpenseJournal_ID AND

first Journal2 (conditional-join) where

Journal2.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal2.Journal_ID = DeductionCatLine.RechargeJournal_ID AND

query resultset tqSelectDeductionCat

field name	data type	db field	description
tiCostCentre_ID	integer	DeductionCatLine.CostCentre_ID	Link to CostCentre
tcCostCentreCode	character	CostCentre.CostCentreCode	Cost Center: A code that identifies the cost center.
tiDeductionCat_ID	integer	DeductionCat.DeductionCat_ID	Record ID
tcDeductionCatCode	character	DeductionCat.DeductionCatCode	Deduction Category Code
tcDeductionCatDescription	character	DeductionCat.DeductionCatDescription	Description
tlDeductionCatIsActive	logical	DeductionCat.DeductionCatIsActive	Active
tlDeductionCatIsAutoWriteOff	logical	DeductionCatLine.DeductionCatLineIsAutoWOff	Auto Write-off
tiDeductionCatLine_ID	integer	DeductionCatLine.DeductionCatLine_ID	Record ID
tcDeductionCatType	character	DeductionCat.DeductionCatType	Type
tcDeductionCatTypeCalc	character	calculated
tdDeductionCatWriteOffLimit	decimal	DeductionCatLine.DeductionCatLineWOffLimit	Write-off Limit
tiDivision_ID	integer	DeductionCatLine.Division_ID	Link to Division
tcDivisionCode	character	Division.DivisionCode	Sub-Account
tiDomain_ID	integer	DeductionCatLine.Domain_ID	Link to Doamin
tiExpenseJournal_ID	integer	DeductionCatLine.ExpenseJournal_ID	Link to ExpenseJournal
tiGL_ID	integer	DeductionCatLine.GL_ID	Link to GL
tcGLCode	character	GL.GLCode	A code identifying the GL account.
tcJournalCode	character	Journal1.JournalCode	A daybook code (maximum eight characters).
tcJournalCode1	character	Journal2.JournalCode	A daybook code (maximum eight characters).
tiRechargeJournal_ID	integer	DeductionCatLine.RechargeJournal_ID	Link to RechargeJournal
DeductionCatCustom	character	calculated	custom fields
DeductionCatLineCustom	character	calculated	custom fields
GLCustom	character	calculated	custom fields
DivisionCustom	character	calculated	custom fields
CostCentreCustom	character	calculated	custom fields
Journal1Custom	character	calculated	custom fields
Journal2Custom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(DeductionCat),rowid(DeductionCatLine),rowid(CostCentre),rowid(Division),rowid(GL),rowid(Journal1),rowid(Journal2)

Internal usage

QadFinancials

lookup query on tDeductionCat.DeductionCatCode

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 = "BDeductionCat".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "SelectDeductionCat".
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/bdeductioncat.selectdeductioncat.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.