API query VatForWHT

Description

Query to return tax related details. Used for withholding tax. Only user on UI.

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
icDomainCode	c	=
tlbl_mstr.lbl_lang	c	=,>=,>,<>,<=,<,begins,can-do,matches
tlbl_mstr.lbl_long	c	=,>=,>,<>,<=,<,begins,can-do,matches
ttx2_mstr.tx2_ap_acct	c	=,>=,>,<>,<=,<,begins,can-do,matches
ttx2_mstr.tx2_ap_cn_acct	c	=,>=,>,<>,<=,<,begins,can-do,matches
ttx2_mstr.tx2_ap_cn_sub	c	=,>=,>,<>,<=,<,begins,can-do,matches
ttx2_mstr.tx2_ap_sub	c	=,>=,>,<>,<=,<,begins,can-do,matches
ttx2_mstr.tx2_desc	c	=,>=,>,<>,<=,<,begins,can-do,matches
ttx2_mstr.tx2_pct_recv	d	=,>=,>,<>,<=,<
ttx2_mstr.tx2_tax_pct	d	=,>=,>,<>,<=,<
ttx2_mstr.tx2_tax_type	c	=,>=,>,<>,<=,<,begins,can-do,matches
ttxty_mstr.txty_tax_desc	c	=,>=,>,<>,<=,<,begins,can-do,matches
ttxty_mstr.txty_wt_type	l	=,<>
tVat.VatCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tVat.VatInOut	c	=,>=,>,<>,<=,<,begins,can-do,matches

Parameters (internal)

icDomainCode

character

Domain code

query condition

each Vat where

first Domains (inner-join) where

Domains.Domain_ID = Vat.Domain_ID AND
Domains.DomainCode = icDomainCode

first tx2_mstr (inner-join) where

tx2_mstr.tx2_tax_code = Vat.VatCode AND
tx2_mstr.tx2_domain = icDomainCode

first txty_mstr (inner-join) where

txty_mstr.txty_tax_type = tx2_mstr.tx2_tax_type AND
txty_mstr.txty_wt_type = TRUE

first lbl_mstr (outer-join) where

lbl_mstr.lbl_term = txty_mstr.txty_tax_desc AND
lbl_mstr.lbl_lang = current-language

query resultset tqVatForWHT

field name	data type	db field	description
tiDomain_ID	integer	Vat.Domain_ID	Domain
tclbl_lang	character	lbl_mstr.lbl_lang	Language
tclbl_long	character	lbl_mstr.lbl_long	Long Label
tctx2_ap_acct	character	tx2_mstr.tx2_ap_acct	AP Tax Account
tctx2_ap_cn_acct	character	tx2_mstr.tx2_ap_cn_acct	AP Tax CN Account
tctx2_ap_cn_sub	character	tx2_mstr.tx2_ap_cn_sub	AP Tax CN Sub-Account
tctx2_ap_sub	character	tx2_mstr.tx2_ap_sub	AP Tax Sub-Account
tctx2_desc	character	tx2_mstr.tx2_desc	Description
tdtx2_pct_recv	decimal	tx2_mstr.tx2_pct_recv	Percent Recoverable
tdtx2_tax_pct	decimal	tx2_mstr.tx2_tax_pct	Tax Rate
tctx2_tax_type	character	tx2_mstr.tx2_tax_type	Tax Type
tctxty_tax_desc	character	txty_mstr.txty_tax_desc	Description
tltxty_wt_type	logical	txty_mstr.txty_wt_type	Withholding Tax Type
tcVatCode	character	Vat.VatCode	Tax Code
tcVatInOut	character	Vat.VatInOut	Tax In/Out
tcVatInOutCalc	character	calculated	Tax In/Out
VatCustom	character	calculated	custom fields
tx2_mstrCustom	character	calculated	custom fields
txty_mstrCustom	character	calculated	custom fields
lbl_mstrCustom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(Vat),rowid(Domains),rowid(tx2_mstr),rowid(txty_mstr),rowid(lbl_mstr)

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 = "BVAT".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "VatForWHT".
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/bvat.vatforwht.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.