API query SelectCashBox (optimised)

Description

SelectCashBox; Select query for the bank-states.
(conditiaon on transaction type is hardcoded, because otherwise you also see the banking entries in it)

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i
tBankState.BankStateClosingBalance	d	=,>=,>,<>,<=,<
tBankState.BankStateInfo	c	=,>=,>,<>,<=,<,begins,can-do,matches
tBankState.BankStateMovement	d	=,>=,>,<>,<=,<
tBankState.BankStateNumber	c	=,>=,>,<>,<=,<,begins,can-do,matches
tBankState.BankStateOpeningBalance	d	=,>=,>,<>,<=,<
tBankState.BankStatePostingDate	t	=,>=,>,<>,<=,<
tBankState.BankStateStatus	c	=,>=,>,<>,<=,<,begins,can-do,matches
tBankState.BankStateYear	i	=,>=,>,<>,<=,<
tCurrency.CurrencyCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL.GLCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tPeriod.PeriodPeriod	i	=,>=,>,<>,<=,<
tPeriod.PeriodYear	i	=,>=,>,<>,<=,<

Parameters (internal)

iiCompanyId

integer

Company id

query condition

each BankState where

BankState.Company_Id = iiCompanyId AND
BankState.BankStateTransactionType = 'CASH'

first Currency (inner-join) where

Currency.Currency_ID = BankState.Currency_ID AND

first GL (inner-join) where

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

first Period (inner-join) where

Period.Company_Id = iiCompanyId AND
Period.Period_ID = BankState.Period_ID AND

query sort order

BankState.Company_ID (ascending)
GL.GLCode (ascending)
BankState.BankStateNumber (ascending)

query resultset tqSelectCashBox

field name	data type	db field	description
tcBankStateInfo	character	BankState.BankStateInfo	Information
tiBankStateYear	integer	BankState.BankStateYear	Bank Statement Year
tcGLCode	character	GL.GLCode	A code identifying the GL account.
tcBankStateNumber	character	BankState.BankStateNumber	Bank Statement No
tcBankStateStatusSelect	character	calculated	BankStateStatusSelect
ttBankStatePostingDate	date	BankState.BankStatePostingDate	Posting Date
tiPeriodYear	integer	Period.PeriodYear	GL Calendar Year
tiPeriodPeriod	integer	Period.PeriodPeriod	GL Period
tcCurrencyCode	character	Currency.CurrencyCode	Currency Code
tdBankStateOpeningBalance	decimal	BankState.BankStateOpeningBalance	Opening Balance
tdBankStateMovement	decimal	BankState.BankStateMovement	Activity
tdBankStateClosingBalance	decimal	BankState.BankStateClosingBalance	Closing Balance
tiBankState_ID	integer	BankState.BankState_ID	Record ID
tiCompany_ID	integer	BankState.Company_ID	Link to Company
tiPeriod_ID	integer	BankState.Period_ID	GL Calendar Year
tiGL_ID	integer	BankState.GL_ID	GL Account
tiCurrency_ID	integer	BankState.Currency_ID	Currency Code
tcBankStateStatus	character	BankState.BankStateStatus	Status
BankStateCustom	character	calculated	custom fields
CurrencyCustom	character	calculated	custom fields
GLCustom	character	calculated	custom fields
PeriodCustom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(BankState),rowid(Currency),rowid(GL),rowid(Period)

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 = "BCashBox".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "SelectCashBox".
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/bcashbox.selectcashbox.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.