API query SelectGrossIncAcc

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i
tCostCentre1.CostCentreCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCostCentre2.CostCentreCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDivision1.DivisionCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDivision3.DivisionCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDivision2.DivisionCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL2.GLCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL3.GLCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL1.GLCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGrossIncAcc.GrossIncAccIsAllCostCentre	l	=,<>
tGrossIncAcc.GrossIncAccIsAllDivision	l	=,<>
tGrossIncAcc.GrossIncAccIsAllProject	l	=,<>
tJournal.JournalCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tProject2.ProjectCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tProject1.ProjectCode	c	=,>=,>,<>,<=,<,begins,can-do,matches

Parameters (internal)

iiCompanyId

integer

Company id

query condition

each GrossIncAcc where

GrossIncAcc.Company_Id = iiCompanyId AND

first CostCentre1 (conditional-join) where

CostCentre1.SharedSet_Id = vi_COSTCENTRE_sharedset(iiCompanyId) AND
CostCentre1.CostCentre_ID = GrossIncAcc.SalesCostCentre_ID AND

first CostCentre2 (conditional-join) where

CostCentre2.SharedSet_Id = vi_COSTCENTRE_sharedset(iiCompanyId) AND
CostCentre2.CostCentre_ID = GrossIncAcc.TaxExpenseCostCentre_ID AND

first Division1 (conditional-join) where

Division1.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division1.Division_ID = GrossIncAcc.SalesDivision_ID AND

first Division2 (conditional-join) where

Division2.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division2.Division_ID = GrossIncAcc.TaxPayableDivision_ID AND

first Division3 (conditional-join) where

Division3.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division3.Division_ID = GrossIncAcc.TaxExpenseDivision_ID AND

first GL1 (inner-join) where

GL1.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL1.GL_ID = GrossIncAcc.SalesGL_ID AND

first GL2 (inner-join) where

GL2.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL2.GL_ID = GrossIncAcc.TaxPayableGL_ID AND

first GL3 (inner-join) where

GL3.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL3.GL_ID = GrossIncAcc.TaxExpenseGL_ID AND

first Journal (inner-join) where

Journal.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal.Journal_ID = GrossIncAcc.Journal_ID AND

first Project2 (conditional-join) where

Project2.SharedSet_Id = vi_PROJECT_sharedset(iiCompanyId) AND
Project2.Project_ID = GrossIncAcc.TaxExpenseProject_ID AND

first Project1 (conditional-join) where

Project1.SharedSet_Id = vi_PROJECT_sharedset(iiCompanyId) AND
Project1.Project_ID = GrossIncAcc.SalesProject_ID AND

query sort order

GL1.SalesGLCode (ascending)
Division1.SalesDivisionCode (ascending)
CostCentre1.SalesCostCentreCode (ascending)
Project1.SalesProjectCode (ascending)

query resultset tqSelectGrossIncAcc

field name	data type	db field	description
tiCompany_ID	integer	GrossIncAcc.Company_ID	Link to Company
tiGrossIncAcc_ID	integer	GrossIncAcc.GrossIncAcc_ID	Record ID
tlGrossIncAccIsAllCostCentre	logical	GrossIncAcc.GrossIncAccIsAllCostCentre	All Cost Centers
tlGrossIncAccIsAllDivision	logical	GrossIncAcc.GrossIncAccIsAllDivision	All Sub-Accounts
tlGrossIncAccIsAllProject	logical	GrossIncAcc.GrossIncAccIsAllProject	All Projects
tiJournal_ID	integer	GrossIncAcc.Journal_ID	Link To Journal
tcJournalCode	character	Journal.JournalCode	A daybook code (maximum eight characters).
tiSalesCostCentre_ID	integer	GrossIncAcc.SalesCostCentre_ID	Link To CostCentre (Sales)
tcSalesCostCentreCode	character	CostCentre1.CostCentreCode	Cost Center: A code that identifies the cost center.
tiSalesDivision_ID	integer	GrossIncAcc.SalesDivision_ID	Link To Division (Sales)
tcSalesDivisionCode	character	Division1.DivisionCode	Sub-Account
tiSalesGL_ID	integer	GrossIncAcc.SalesGL_ID	Link To GL (Sales)
tcSalesGLCode	character	GL1.GLCode	A code identifying the GL account.
tiSalesProject_ID	integer	GrossIncAcc.SalesProject_ID	Link To Project (Sales)
tcSalesProjectCode	character	Project1.ProjectCode	Project
tiTaxExpenseCostCentre_ID	integer	GrossIncAcc.TaxExpenseCostCentre_ID	Link To CostCentre (Tax Expense)
tcTaxExpenseCostCentreCode	character	CostCentre2.CostCentreCode	Cost Center: A code that identifies the cost center.
tiTaxExpenseDivision_ID	integer	GrossIncAcc.TaxExpenseDivision_ID	Link To Division (Tax Expense)
tcTaxExpenseDivisionCode	character	Division3.DivisionCode	Sub-Account
tiTaxExpenseGL_ID	integer	GrossIncAcc.TaxExpenseGL_ID	Link To GL (Tax Expense)
tcTaxExpenseGLCode	character	GL3.GLCode	A code identifying the GL account.
tiTaxExpenseProject_ID	integer	GrossIncAcc.TaxExpenseProject_ID	Link To Project (Tax Expense)
tcTaxExpenseProjectCode	character	Project2.ProjectCode	Project
tiTaxPayableDivision_ID	integer	GrossIncAcc.TaxPayableDivision_ID	Link To Division (Tax Payable)
tcTaxPayableDivisionCode	character	Division2.DivisionCode	Sub-Account
tiTaxPayableGL_ID	integer	GrossIncAcc.TaxPayableGL_ID	Link To GL (Tax Payable)
tcTaxPayableGLCode	character	GL2.GLCode	A code identifying the GL account.
GrossIncAccCustom	character	calculated	custom fields
GL1Custom	character	calculated	custom fields
GL2Custom	character	calculated	custom fields
GL3Custom	character	calculated	custom fields
Division1Custom	character	calculated	custom fields
Division2Custom	character	calculated	custom fields
Division3Custom	character	calculated	custom fields
CostCentre1Custom	character	calculated	custom fields
CostCentre2Custom	character	calculated	custom fields
Project1Custom	character	calculated	custom fields
Project2Custom	character	calculated	custom fields
JournalCustom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(GrossIncAcc),rowid(CostCentre1),rowid(CostCentre2),rowid(Division1),rowid(Division2),rowid(Division3),rowid(GL1),rowid(GL2),rowid(GL3),rowid(Journal),rowid(Project2),rowid(Project1)

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 = "BGrossIncomeAccounting".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "SelectGrossIncAcc".
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/bgrossincomeaccounting.selectgrossincacc.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.