API query SelectYearClosing (optimised)

Description

Select Year Closing

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i
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
tJournal2.JournalCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tJournal1.JournalCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tJournal3.JournalCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tLayer.LayerCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tLayer.LayerTypeCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tYearClosing.YearClosingAccountingYear	i	=,>=,>,<>,<=,<
tYearClosing.YearClosingIsInclDivision	l	=,<>
tYearClosing.YearClosingIsTransPLToBal	l	=,<>

Parameters (internal)

iiCompanyId

integer

Company id

query condition

each YearClosing where

YearClosing.Company_Id = iiCompanyId AND

first GL1 (conditional-join) where

GL1.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL1.GL_ID = YearClosing.ClosingPLGL_ID AND

first GL2 (conditional-join) where

GL2.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL2.GL_ID = YearClosing.ClosingPLDivGL_ID AND

first GL3 (conditional-join) where

GL3.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL3.GL_ID = YearClosing.ClosingBalanceGL_ID AND

first GL4 (conditional-join) where

GL4.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL4.GL_ID = YearClosing.ClosingBalanceDivGL_ID AND

first Journal1 (inner-join) where

Journal1.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal1.Journal_ID = YearClosing.ClosingPLJournal_ID AND

first Journal2 (inner-join) where

Journal2.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal2.Journal_ID = YearClosing.ClosingBalanceJournal_ID AND

first Journal3 (conditional-join) where

Journal3.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal3.Journal_ID = YearClosing.ClosingTransferJournal_ID AND

first Layer (conditional-join) where

Layer.Layer_ID = YearClosing.Layer_ID AND

query sort order

YearClosing.Company_ID (ascending)
YearClosing.YearClosingAccountingYear (ascending)

query resultset tqSelectYearClosing

field name	data type	db field	description
tiLayer_ID	integer	Layer.Layer_ID	Record ID
tcLayerCode	character	Layer.LayerCode	Layer Code
tcLayerTypeCode	character	Layer.LayerTypeCode	Layer Type
tcLayerTypeCodeTr	character	calculated
tiYearClosingAccountingYear	integer	YearClosing.YearClosingAccountingYear	GL Calendar Year
tlYearClosingIsTransPLToBal	logical	YearClosing.YearClosingIsTransPLToBal	Auto-Transfer Profit/Loss to BS
tlYearClosingIsInclDivision	logical	YearClosing.YearClosingIsInclDivision	Include Sub-Accounts
tcClosingPLGLCode	character	GL1.GLCode	A code identifying the GL account.
tcClosingPLDivGLCode	character	GL2.GLCode	A code identifying the GL account.
tcClosingBalanceGLCode	character	GL3.GLCode	A code identifying the GL account.
tcClosingBalanceDivGLCode	character	GL4.GLCode	A code identifying the GL account.
tcClosingTransferJournalCode	character	Journal3.JournalCode	A daybook code (maximum eight characters).
tcClosingPLJournalCode	character	Journal1.JournalCode	A daybook code (maximum eight characters).
tcClosingBalanceJournalCode	character	Journal2.JournalCode	A daybook code (maximum eight characters).
tiYearClosing_ID	integer	YearClosing.YearClosing_ID	Record ID
tiCompany_ID	integer	YearClosing.Company_ID	Link to Company
tiClosingPLGL_ID	integer	YearClosing.ClosingPLGL_ID	Profit/Loss Closing Account
tiClosingPLDivGL_ID	integer	YearClosing.ClosingPLDivGL_ID	Profit/Loss Closing Sub-Account
tiClosingBalanceGL_ID	integer	YearClosing.ClosingBalanceGL_ID	Balance Sheet Closing Account
tiClosingBalanceDivGL_ID	integer	YearClosing.ClosingBalanceDivGL_ID	Balance Sheet Closing Sub-Account
tiClosingTransferJournal_ID	integer	YearClosing.ClosingTransferJournal_ID	Automated Profit/Loss Transfer Daybook
tiClosingPLJournal_ID	integer	YearClosing.ClosingPLJournal_ID	Closing Profit/Loss Daybook
tiClosingBalanceJournal_ID	integer	YearClosing.ClosingBalanceJournal_ID	Closing Balance Daybook
YearClosingCustom	character	calculated	custom fields
GL1Custom	character	calculated	custom fields
GL2Custom	character	calculated	custom fields
GL3Custom	character	calculated	custom fields
GL4Custom	character	calculated	custom fields
Journal1Custom	character	calculated	custom fields
Journal2Custom	character	calculated	custom fields
Journal3Custom	character	calculated	custom fields
LayerCustom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(YearClosing),rowid(GL1),rowid(GL2),rowid(GL3),rowid(GL4),rowid(Journal1),rowid(Journal2),rowid(Journal3),rowid(Layer)

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 = "BYearClosing".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "SelectYearClosing".
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/byearclosing.selectyearclosing.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.