API query SelectDomainsForFin (optimised)

Description

Select Query for Domains used in the financial module based on the selected company.

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i
tCompany.CompanyCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency1.CurrencyCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency2.CurrencyCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDomains.DomainCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDomains.DomainIsActive	l	=,<>
tDomains.DomainPropath	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDomainProperty.DomainPropertyCodePage	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDomainProperty.DomainPropertyExceptList	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDomainProperty.DomainPropertyIsChronolNr	l	=,<>
tDomainProperty.DomainPropertyIsConsecutNr	l	=,<>
tDomainProperty.DomainPropertyIsInvCertific	l	=,<>
tDomainProperty.DomainPropertyIsMaskCC	l	=,<>
tDomainProperty.DomainPropertyIsMaskCCAll	l	=,<>
tDomainProperty.DomainPropertyIsMaskDiv	l	=,<>
tDomainProperty.DomainPropertyIsMaskDivAll	l	=,<>
tDomainProperty.DomainPropertyIsMaskPrjAll	l	=,<>
tDomainProperty.DomainPropertyIsMaskProj	l	=,<>
tDomainProperty.DomainPropertyIsPopupInvNr	l	=,<>
tDomainProperty.DomainPropertyIsSetupCompl	l	=,<>
tDomainProperty.DomainPropertyIsStatutory	l	=,<>
tDomainProperty.DomainPropertyIsTaxValid	l	=,<>
tDomainProperty.DomainPropertyIsWHT	l	=,<>
tDomainProperty.DomainPropertyName	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDomainProperty.DomainPropertyNonChronErr	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDomainProperty.DomainPropertySearchName	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDomains.DomainType	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL5.GLCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL4.GLCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL3.GLCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL2.GLCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL1.GLCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tLng.LngCode	c	=,>=,>,<>,<=,<,begins,can-do,matches

Parameters (internal)

iiCompanyId

integer

Company id

query condition

each Domains where

first Company (conditional-join) where

Company.Company_Id = iiCompanyId AND
Company.Company_ID = Domains.PrimaryCompany_ID AND

each DomainProperty (inner-join) where

DomainProperty.Domain_ID = Domains.Domain_ID AND

first Currency1 (inner-join) where

Currency1.Currency_ID = DomainProperty.Currency_ID AND

first Currency2 (inner-join) where

Currency2.Currency_ID = DomainProperty.StatutoryCurrency_ID AND

first GL1 (conditional-join) where

GL1.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL1.GL_ID = DomainProperty.IntercoAPGL_ID AND

first GL2 (conditional-join) where

GL2.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL2.GL_ID = DomainProperty.IntercoARGL_ID AND

first GL3 (conditional-join) where

GL3.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL3.GL_ID = DomainProperty.IntercoFAGL_ID AND

first GL4 (conditional-join) where

GL4.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL4.GL_ID = DomainProperty.IntercoICGL_ID AND

first GL5 (conditional-join) where

GL5.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL5.GL_ID = DomainProperty.IntercoJEGL_ID AND

first Lng (inner-join) where

Lng.Lng_ID = Domains.Lng_ID AND

query sort order

Domains.DomainCode (ascending)

query resultset tqSelectDomainsForFin

field name	data type	db field	description
tcCompanyCode	character	Company.CompanyCode	Entity Code
tiCurrency_ID	integer	DomainProperty.Currency_ID	Base Currency
tcCurrencyCode	character	Currency1.CurrencyCode	Currency Code
tcDomainPropath	character	Domains.DomainPropath	Propath
tcDomainPropertyCodePage	character	DomainProperty.DomainPropertyCodePage	Domain Code Page
tcDomainPropertyExceptList	character	DomainProperty.DomainPropertyExceptList	Domain Exception List
tlDomainPropertyIsChronolNr	logical	DomainProperty.DomainPropertyIsChronolNr	Chronological Invoice Numbering
tlDomainPropertyIsConsecutNr	logical	DomainProperty.DomainPropertyIsConsecutNr	Consecutive Invoice Numbering
tlDomainPropertyIsInvCertific	logical	DomainProperty.DomainPropertyIsInvCertific	Invoice Certification: activated or not
tlDomainPropertyIsMaskCC	logical	DomainProperty.DomainPropertyIsMaskCC	Cost Center COA Mask
tlDomainPropertyIsMaskCCAll	logical	DomainProperty.DomainPropertyIsMaskCCAll	Cost Center All-Range Mask By Default
tlDomainPropertyIsMaskDiv	logical	DomainProperty.DomainPropertyIsMaskDiv	Sub-Account COA Mask
tlDomainPropertyIsMaskDivAll	logical	DomainProperty.DomainPropertyIsMaskDivAll	Sub-Account All-Range Mask By Default
tlDomainPropertyIsMaskPrjAll	logical	DomainProperty.DomainPropertyIsMaskPrjAll	Project All-Range Mask By Default
tlDomainPropertyIsMaskProj	logical	DomainProperty.DomainPropertyIsMaskProj	Project COA Mask
tlDomainPropertyIsPopupInvNr	logical	DomainProperty.DomainPropertyIsPopupInvNr	Show Supplier Invoice Number in Pop-up After Save
tlDomainPropertyIsStatutory	logical	DomainProperty.DomainPropertyIsStatutory	Statutory Currency Enabled
tlDomainPropertyIsTaxValid	logical	DomainProperty.DomainPropertyIsTaxValid	Tax Validation
tlDomainPropertyIsWHT	logical	DomainProperty.DomainPropertyIsWHT	Use Withholding Tax
tcDomainPropertyNonChronErr	character	DomainProperty.DomainPropertyNonChronErr	Action on Non-Chronological Number
tcIntercoAPGLCode	character	GL1.GLCode	A code identifying the GL account.
tcIntercoARGLCode	character	GL2.GLCode	A code identifying the GL account.
tcIntercoFAGLCode	character	GL3.GLCode	A code identifying the GL account.
tcIntercoICGLCode	character	GL4.GLCode	A code identifying the GL account.
tcIntercoJEGLCode	character	GL5.GLCode	A code identifying the GL account.
ttLastModifiedDate	date	Domains.LastModifiedDate	Last Modified Date
tiLastModifiedTime	integer	Domains.LastModifiedTime	Last Modified Time
tcLastModifiedUser	character	Domains.LastModifiedUser	Last Modified User
tcLngCode	character	Lng.LngCode	Language Code
tiStatutoryCurrency_ID	integer	DomainProperty.StatutoryCurrency_ID	Statutory Currency
tcStatutoryCurrencyCode	character	Currency2.CurrencyCode	Currency Code
tiDomain_ID	integer	Domains.Domain_ID	Record ID
tcDomainCode	character	Domains.DomainCode	Domain
tcDomainType	character	Domains.DomainType	Type
tlDomainIsActive	logical	Domains.DomainIsActive	Active
tlDomainPropertyIsSetupCompl	logical	DomainProperty.DomainPropertyIsSetupCompl	Setup Complete
tcDomainPropertyName	character	DomainProperty.DomainPropertyName	Name
tcDomainPropertySearchName	character	DomainProperty.DomainPropertySearchName	Search Name
DomainsCustom	character	calculated	custom fields
CompanyCustom	character	calculated	custom fields
DomainPropertyCustom	character	calculated	custom fields
Currency1Custom	character	calculated	custom fields
GL1Custom	character	calculated	custom fields
GL2Custom	character	calculated	custom fields
GL3Custom	character	calculated	custom fields
GL4Custom	character	calculated	custom fields
GL5Custom	character	calculated	custom fields
LngCustom	character	calculated	custom fields
Currency2Custom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(Domains),rowid(Company),rowid(DomainProperty),rowid(Currency1),rowid(Currency2),rowid(GL1),rowid(GL2),rowid(GL3),rowid(GL4),rowid(GL5),rowid(Lng)

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 = "BDomain".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "SelectDomainsForFin".
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/bdomain.selectdomainsforfin.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.