API query SelectMirroringGL

Description

This query returns the key fields for the company id and domain id.

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i
iiDomainId	i	=
tCompany.CompanyCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCostCentre1.CostCentreCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCostCentre2.CostCentreCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCostCentre3.CostCentreCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCostCentre4.CostCentreCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDivision2.DivisionCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDivision3.DivisionCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDivision4.DivisionCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDivision1.DivisionCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDomains.DomainCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL1.GLCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL2.GLCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL3.GLCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL4.GLCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL1.GLSystemTypeCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL2.GLSystemTypeCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL1.GLTypeCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL2.GLTypeCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tMirroringGL.MirroringGLSplitGLType	c	=,>=,>,<>,<=,<,begins,can-do,matches
tMirroringGL.MirroringGLTrans1Journal	c	=,>=,>,<>,<=,<,begins,can-do,matches
tMirroringGL.MirroringGLTrans2Journal	c	=,>=,>,<>,<=,<,begins,can-do,matches
tProject3.ProjectCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tProject2.ProjectCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tProject1.ProjectCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tProject4.ProjectCode	c	=,>=,>,<>,<=,<,begins,can-do,matches

Parameters (internal)

iiCompanyId	integer	Company id
iiDomainId	integer

query condition

each MirroringGL where

MirroringGL.Company_Id = iiCompanyId AND
MirroringGL.Domain_ID = iiDomainId

first Company (conditional-join) where

Company.Company_Id = iiCompanyId AND
Company.Company_ID = MirroringGL.Company_ID AND

first CostCentre1 (conditional-join) where

CostCentre1.SharedSet_Id = vi_COSTCENTRE_sharedset(iiCompanyId) AND
CostCentre1.CostCentre_ID = MirroringGL.Source2CostCentre_ID AND

first CostCentre2 (conditional-join) where

CostCentre2.SharedSet_Id = vi_COSTCENTRE_sharedset(iiCompanyId) AND
CostCentre2.CostCentre_ID = MirroringGL.Mirror2CostCentre_ID AND

first CostCentre3 (conditional-join) where

CostCentre3.SharedSet_Id = vi_COSTCENTRE_sharedset(iiCompanyId) AND
CostCentre3.CostCentre_ID = MirroringGL.Mirror1CostCentre_ID AND

first CostCentre4 (conditional-join) where

CostCentre4.SharedSet_Id = vi_COSTCENTRE_sharedset(iiCompanyId) AND
CostCentre4.CostCentre_ID = MirroringGL.Source1CostCentre_ID AND

first Division1 (conditional-join) where

Division1.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division1.Division_ID = MirroringGL.Source1Division_ID AND

first Division2 (conditional-join) where

Division2.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division2.Division_ID = MirroringGL.Source2Division_ID AND

first Division3 (conditional-join) where

Division3.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division3.Division_ID = MirroringGL.Mirror1Division_ID AND

first Division4 (conditional-join) where

Division4.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division4.Division_ID = MirroringGL.Mirror2Division_ID AND

first Domains (inner-join) where

Domains.Domain_ID = MirroringGL.Domain_ID AND

first GL1 (inner-join) where

GL1.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL1.GL_ID = MirroringGL.Source1GL_ID AND

first GL2 (inner-join) where

GL2.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL2.GL_ID = MirroringGL.Source2GL_ID AND

first GL3 (inner-join) where

GL3.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL3.GL_ID = MirroringGL.Mirror2GL_ID AND

first GL4 (inner-join) where

GL4.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL4.GL_ID = MirroringGL.Mirror1GL_ID AND

first Project1 (conditional-join) where

Project1.SharedSet_Id = vi_PROJECT_sharedset(iiCompanyId) AND
Project1.Project_ID = MirroringGL.Source1Project_ID AND

first Project2 (conditional-join) where

Project2.SharedSet_Id = vi_PROJECT_sharedset(iiCompanyId) AND
Project2.Project_ID = MirroringGL.Source2Project_ID AND

first Project3 (conditional-join) where

Project3.SharedSet_Id = vi_PROJECT_sharedset(iiCompanyId) AND
Project3.Project_ID = MirroringGL.Mirror1Project_ID AND

first Project4 (conditional-join) where

Project4.SharedSet_Id = vi_PROJECT_sharedset(iiCompanyId) AND
Project4.Project_ID = MirroringGL.Mirror2Project_ID AND

query sort order

Domains.DomainCode (ascending)
Company.CompanyCode (ascending)
GL1.Source1GLCode (ascending)

query resultset tqSelectMirroringGL

field name	data type	db field	description
tcDomainCode	character	Domains.DomainCode	Domain
tcCompanyCode	character	Company.CompanyCode	Entity Code
tcSource1GLCode	character	GL1.GLCode	A code identifying the GL account.
tcSource1GLTypeCode	character	GL1.GLTypeCode	A code identifying the type of account.
tcSource1GLSystemType	character	GL1.GLSystemTypeCode	System Type: A code identifying the type of system account.
tcSource1GLTypeCodeTr	character	calculated
tcSource1GLSystemTypeTr	character	calculated
tcSource2GLCode	character	GL2.GLCode	A code identifying the GL account.
tcSource2GLTypeCode	character	GL2.GLTypeCode	A code identifying the type of account.
tcSource2GLSystemType	character	GL2.GLSystemTypeCode	System Type: A code identifying the type of system account.
tcSource2GLTypeCodeTr	character	calculated
tcSource2GLSystemTypeTr	character	calculated
tcMirror1GLCode	character	GL4.GLCode	A code identifying the GL account.
tcMirror2GLcode	character	GL3.GLCode	A code identifying the GL account.
tcMirroringGLSplitGLType	character	MirroringGL.MirroringGLSplitGLType	Account Split Type
tcMirroringGLTrans1Journal	character	MirroringGL.MirroringGLTrans1Journal	Transaction 1 Daybook
tcMirroringGLTrans2Journal	character	MirroringGL.MirroringGLTrans2Journal	Transaction 2 Daybook
tcSource1CostCentreCode	character	CostCentre4.CostCentreCode	Cost Center: A code that identifies the cost center.
tcSource1ProjetCode	character	Project1.ProjectCode	Project
tcSource1DivisionCode	character	Division1.DivisionCode	Sub-Account
tcSource2CostCentreCode	character	CostCentre1.CostCentreCode	Cost Center: A code that identifies the cost center.
tcSource2ProjectCode	character	Project2.ProjectCode	Project
tcSource2DivisionCode	character	Division2.DivisionCode	Sub-Account
tcMirror1CostCentreCode	character	CostCentre3.CostCentreCode	Cost Center: A code that identifies the cost center.
tcMirror1ProjectCode	character	Project3.ProjectCode	Project
tcMirror1DivisionCode	character	Division3.DivisionCode	Sub-Account
tcMirror2CostCentreCode	character	CostCentre2.CostCentreCode	Cost Center: A code that identifies the cost center.
tcMirror2ProjectCode	character	Project4.ProjectCode	Project
tcMirror2DivisionCode	character	Division4.DivisionCode	Sub-Account
tcMirroringGLSplitGLTypeTr	character	calculated
tcMirroringGLTrans1JournalTr	character	calculated
tcMirroringGLTrans2JournalTr	character	calculated
ttLastModifiedDate	date	MirroringGL.LastModifiedDate	Last Modified Date
tiLastModifiedTime	integer	MirroringGL.LastModifiedTime	Last Modified Time
tcLastModifiedUser	character	MirroringGL.LastModifiedUser	Last Modified User
tiSource2GL_ID	integer	MirroringGL.Source2GL_ID	Source 2 Account Type
tiSource1GL_ID	integer	MirroringGL.Source1GL_ID	Source 1 Account Type
tiDomain_ID	integer	MirroringGL.Domain_ID	Domain
tiCompany_ID	integer	MirroringGL.Company_ID	Entity
tiMirror1CostCentre_ID	integer	MirroringGL.Mirror1CostCentre_ID	Mirror 1 Cost Center
tiMirror1Division_ID	integer	MirroringGL.Mirror1Division_ID	Mirror 1 Sub-Account
tiMirror1GL_ID	integer	MirroringGL.Mirror1GL_ID	Mirror 1 Account
tiMirror1Project_ID	integer	MirroringGL.Mirror1Project_ID	Mirror 1 Project
tiMirror2CostCentre_ID	integer	MirroringGL.Mirror2CostCentre_ID	Mirror 2 Cost Center
tiMirror2Division_ID	integer	MirroringGL.Mirror2Division_ID	Mirror 2 Sub-Account
tiMirror2GL_ID	integer	MirroringGL.Mirror2GL_ID	Mirror 2 Account
tiMirror2Project_ID	integer	MirroringGL.Mirror2Project_ID	Mirror 2 Project
tiMirroringGL_ID	integer	MirroringGL.MirroringGL_ID	Record ID
tiSource1CostCentre_ID	integer	MirroringGL.Source1CostCentre_ID	Source 1 Cost Center
tiSource1Division_ID	integer	MirroringGL.Source1Division_ID	Source 1 Sub-Account
tiSource1Project_ID	integer	MirroringGL.Source1Project_ID	Source 1 Project
tiSource2CostCentre_ID	integer	MirroringGL.Source2CostCentre_ID	Source 2 Cost Center
tiSource2Division_ID	integer	MirroringGL.Source2Division_ID	Source 2 Sub-Account
tiSource2Project_ID	integer	MirroringGL.Source2Project_ID	Source 2 Project
MirroringGLCustom	character	calculated	custom fields
CompanyCustom	character	calculated	custom fields
CostCentre1Custom	character	calculated	custom fields
CostCentre2Custom	character	calculated	custom fields
CostCentre3Custom	character	calculated	custom fields
CostCentre4Custom	character	calculated	custom fields
Division1Custom	character	calculated	custom fields
Division2Custom	character	calculated	custom fields
Division3Custom	character	calculated	custom fields
Division4Custom	character	calculated	custom fields
DomainsCustom	character	calculated	custom fields
GL1Custom	character	calculated	custom fields
GL2Custom	character	calculated	custom fields
GL3Custom	character	calculated	custom fields
GL4Custom	character	calculated	custom fields
Project1Custom	character	calculated	custom fields
Project2Custom	character	calculated	custom fields
Project3Custom	character	calculated	custom fields
Project4Custom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(MirroringGL),rowid(Company),rowid(CostCentre1),rowid(CostCentre2),rowid(CostCentre3),rowid(CostCentre4),rowid(Division1),rowid(Division2),rowid(Division3),rowid(Division4),rowid(Domains),rowid(GL1),rowid(GL2),rowid(GL3),rowid(GL4),rowid(Project1),rowid(Project2),rowid(Project3),rowid(Project4)

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 = "BMirroringGL".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "SelectMirroringGL".
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/bmirroringgl.selectmirroringgl.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.