API query SelectWHT (optimised)

Description

select all withholding tax records

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i
tCurrency.CurrencyCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tWHT.LastModifiedDate	t	=,>=,>,<>,<=,<
tWHT.LastModifiedTime	i	=,>=,>,<>,<=,<
tWHT.LastModifiedUser	c	=,>=,>,<>,<=,<,begins,can-do,matches
tVat.VatCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tWHT.WHTAdditionalNumber	i	=,>=,>,<>,<=,<
tWHT.WHTAmtCreditCC	d	=,>=,>,<>,<=,<
tWHT.WHTAmtCreditLC	d	=,>=,>,<>,<=,<
tWHT.WHTAmtCreditTC	d	=,>=,>,<>,<=,<
tWHT.WHTAmtDebitCC	d	=,>=,>,<>,<=,<
tWHT.WHTAmtDebitLC	d	=,>=,>,<>,<=,<
tWHT.WHTAmtDebitTC	d	=,>=,>,<>,<=,<
tWHT.WHTChargeAmtCreditCC	d	=,>=,>,<>,<=,<
tWHT.WHTChargeAmtCreditLC	d	=,>=,>,<>,<=,<
tWHT.WHTChargeAmtCreditTC	d	=,>=,>,<>,<=,<
tWHT.WHTChargeAmtDebitCC	d	=,>=,>,<>,<=,<
tWHT.WHTChargeAmtDebitLC	d	=,>=,>,<>,<=,<
tWHT.WHTChargeAmtDebitTC	d	=,>=,>,<>,<=,<
tWHT.WHTChargeType	c	=,>=,>,<>,<=,<,begins,can-do,matches
tWHT.WHTCInvoicePostingDate	t	=,>=,>,<>,<=,<
tWHT.WHTCInvoiceReference	c	=,>=,>,<>,<=,<,begins,can-do,matches
tWHT.WHTCompanyCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tWHT.WHTCompanyLCCurrencyCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tWHT.WHTCreditorCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tWHT.WHTCreditorSharedSetCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tWHT.WHTIsPrinted	l	=,<>
tWHT.WHTNumber	i	=,>=,>,<>,<=,<
tWHT.WHTPaidAmtCreditCC	d	=,>=,>,<>,<=,<
tWHT.WHTPaidAmtCreditLC	d	=,>=,>,<>,<=,<
tWHT.WHTPaidAmtCreditTC	d	=,>=,>,<>,<=,<
tWHT.WHTPaidAmtDebitCC	d	=,>=,>,<>,<=,<
tWHT.WHTPaidAmtDebitLC	d	=,>=,>,<>,<=,<
tWHT.WHTPaidAmtDebitTC	d	=,>=,>,<>,<=,<
tWHT.WHTPaymentDate	t	=,>=,>,<>,<=,<
tWHT.WHTPaymentReference	c	=,>=,>,<>,<=,<,begins,can-do,matches
tWHT.WHTPercentage	d	=,>=,>,<>,<=,<
tWHT.WHTPeriodPeriod	i	=,>=,>,<>,<=,<
tWHT.WHTPeriodYear	i	=,>=,>,<>,<=,<
tWHT.WHTRetainedAmtCreditCC	d	=,>=,>,<>,<=,<
tWHT.WHTRetainedAmtCreditLC	d	=,>=,>,<>,<=,<
tWHT.WHTRetainedAmtCreditTC	d	=,>=,>,<>,<=,<
tWHT.WHTRetainedAmtDebitCC	d	=,>=,>,<>,<=,<
tWHT.WHTRetainedAmtDebitLC	d	=,>=,>,<>,<=,<
tWHT.WHTRetainedAmtDebitTC	d	=,>=,>,<>,<=,<
tWHT.WHTSettlementDate	t	=,>=,>,<>,<=,<
tWHT.WHTStatus	c	=,>=,>,<>,<=,<,begins,can-do,matches
tWHT.WHTTaxableFeesCreditCC	d	=,>=,>,<>,<=,<
tWHT.WHTTaxableFeesCreditLC	d	=,>=,>,<>,<=,<
tWHT.WHTTaxableFeesCreditTC	d	=,>=,>,<>,<=,<
tWHT.WHTTaxableFeesDebitCC	d	=,>=,>,<>,<=,<
tWHT.WHTTaxableFeesDebitLC	d	=,>=,>,<>,<=,<
tWHT.WHTTaxableFeesDebitTC	d	=,>=,>,<>,<=,<
tWHT.WHTTaxType	c	=,>=,>,<>,<=,<,begins,can-do,matches
tWHT.WHTTotAmtCreditCC	d	=,>=,>,<>,<=,<
tWHT.WHTTotAmtCreditLC	d	=,>=,>,<>,<=,<
tWHT.WHTTotAmtCreditTC	d	=,>=,>,<>,<=,<
tWHT.WHTTotAmtDebitCC	d	=,>=,>,<>,<=,<
tWHT.WHTTotAmtDebitLC	d	=,>=,>,<>,<=,<
tWHT.WHTTotAmtDebitTC	d	=,>=,>,<>,<=,<
tWHT.WHTVATAmtCreditCC	d	=,>=,>,<>,<=,<
tWHT.WHTVATAmtCreditLC	d	=,>=,>,<>,<=,<
tWHT.WHTVATAmtCreditTC	d	=,>=,>,<>,<=,<
tWHT.WHTVATAmtDebitCC	d	=,>=,>,<>,<=,<
tWHT.WHTVATAmtDebitLC	d	=,>=,>,<>,<=,<
tWHT.WHTVATAmtDebitTC	d	=,>=,>,<>,<=,<

Parameters (internal)

iiCompanyId

integer

Company id

query condition

each WHT where

WHT.Company_Id = iiCompanyId AND

first Currency (inner-join) where

Currency.Currency_ID = WHT.Currency_ID AND

first Vat (conditional-join) where

Vat.Vat_ID = WHT.Vat_ID AND

query resultset tqSelectWHT

field name	data type	db field	description
tiCACInvoice_ID	integer	WHT.CACInvoice_ID	Link to CA-CInvoice
tiWHTAdditionalNumber	integer	WHT.WHTAdditionalNumber	Additional WHT Additional WHT Number
tcWHTCompanyCode	character	WHT.WHTCompanyCode	Entity Code
tcVatCode	character	Vat.VatCode	Tax Code
tcCurrencyCode	character	Currency.CurrencyCode	Currency Code
tcWHTCreditorCode	character	WHT.WHTCreditorCode	Supplier Code
tiWHTPeriodPeriod	integer	WHT.WHTPeriodPeriod	GL Period
tiWHTPeriodYear	integer	WHT.WHTPeriodYear	GL Calendar Year
tcWHTTaxType	character	WHT.WHTTaxType	Tax Type
ttWHTPaymentDate	date	WHT.WHTPaymentDate	Payment Date
tcWHTPaymentReference	character	WHT.WHTPaymentReference	Payment Reference
tcWHTStatus	character	WHT.WHTStatus	WHT Status
tiWHTNumber	integer	WHT.WHTNumber	WHT Number
tiBankStateAlloc_ID	integer	WHT.BankStateAlloc_ID	Link to BankStateAlloc
tiCDocumentInvoiceXref_ID	integer	WHT.CDocumentInvoiceXref_ID	Link to CDocumentInvoiceXref
tiCInvoice_ID	integer	WHT.CInvoice_ID	Link to CInvoice
tiCInvoiceWHT_ID	integer	WHT.CInvoiceWHT_ID	Link to CInvoiceWHT
tiCompany_ID	integer	WHT.Company_ID	Link to Company
tiCostCentre_ID	integer	WHT.CostCentre_ID	Link to CostCentre
tiCreditor_ID	integer	WHT.Creditor_ID	Link to Creditor
tiCurrency_ID	integer	WHT.Currency_ID	Link to Currency
tiDivision_ID	integer	WHT.Division_ID	Link to Division
tiGL_ID	integer	WHT.GL_ID	Link to GL
tiProject_ID	integer	WHT.Project_ID	Link to Project
tiVat_ID	integer	WHT.Vat_ID	Link to Vat
tiWHT_ID	integer	WHT.WHT_ID	Record ID
tdWHTAmtCreditCC	decimal	WHT.WHTAmtCreditCC	WHT Amount Cr SC
tdWHTAmtCreditLC	decimal	WHT.WHTAmtCreditLC	WHT Amount Cr BC
tdWHTAmtCreditTC	decimal	WHT.WHTAmtCreditTC	WHT Amount Cr TC
tdWHTAmtDebitCC	decimal	WHT.WHTAmtDebitCC	WHT Amount Dr SC
tdWHTAmtDebitLC	decimal	WHT.WHTAmtDebitLC	WHT Amount Dr LC
tdWHTAmtDebitTC	decimal	WHT.WHTAmtDebitTC	WHT Amount Dr TC
tiWHTCertFormat_ID	integer	WHT.WHTCertFormat_ID	Link to WHTCertFormat
tdWHTChargeAmtCreditCC	decimal	WHT.WHTChargeAmtCreditCC	Charge Amount Cr SC
tdWHTChargeAmtCreditLC	decimal	WHT.WHTChargeAmtCreditLC	Charge Amount Cr BC
tdWHTChargeAmtCreditTC	decimal	WHT.WHTChargeAmtCreditTC	Charge Amount Cr TC
tdWHTChargeAmtDebitCC	decimal	WHT.WHTChargeAmtDebitCC	Charge Amount Dr SC
tdWHTChargeAmtDebitLC	decimal	WHT.WHTChargeAmtDebitLC	Charge Amount Dr LC
tdWHTChargeAmtDebitTC	decimal	WHT.WHTChargeAmtDebitTC	Charge Amount Dr TC
tcWHTChargeType	character	WHT.WHTChargeType	Charge Type
ttWHTCInvoicePostingDate	date	WHT.WHTCInvoicePostingDate	Invoice Posting Date
tcWHTCInvoiceReference	character	WHT.WHTCInvoiceReference	Invoice Reference
tlWHTIsPrinted	logical	WHT.WHTIsPrinted	Printed
tdWHTPaidAmtCreditCC	decimal	WHT.WHTPaidAmtCreditCC	Paid Amount Cr SC
tdWHTPaidAmtCreditLC	decimal	WHT.WHTPaidAmtCreditLC	Paid Amount Cr BC
tdWHTPaidAmtCreditTC	decimal	WHT.WHTPaidAmtCreditTC	Paid Amount Cr TC
tdWHTPaidAmtDebitCC	decimal	WHT.WHTPaidAmtDebitCC	Paid Amount Dr SC
tdWHTPaidAmtDebitLC	decimal	WHT.WHTPaidAmtDebitLC	Paid Amount Dr LC
tdWHTPaidAmtDebitTC	decimal	WHT.WHTPaidAmtDebitTC	Paid Amount Dr TC
tdWHTPercentage	decimal	WHT.WHTPercentage	WHT Tax Rate Perc
tdWHTRetainedAmtCreditCC	decimal	WHT.WHTRetainedAmtCreditCC	Retained Amount Cr SC
tdWHTRetainedAmtCreditLC	decimal	WHT.WHTRetainedAmtCreditLC	Retained Amount Cr BC
tdWHTRetainedAmtCreditTC	decimal	WHT.WHTRetainedAmtCreditTC	Retained Amount Cr TC
tdWHTRetainedAmtDebitCC	decimal	WHT.WHTRetainedAmtDebitCC	Retained Amount Dr SC
tdWHTRetainedAmtDebitLC	decimal	WHT.WHTRetainedAmtDebitLC	Retained Amount Dr LC
tdWHTRetainedAmtDebitTC	decimal	WHT.WHTRetainedAmtDebitTC	Retained Amount Dr TC
ttWHTSettlementDate	date	WHT.WHTSettlementDate	Settlement Date
tdWHTTaxableFeesCreditCC	decimal	WHT.WHTTaxableFeesCreditCC	Taxable Fee Amount Cr SC
tdWHTTaxableFeesCreditLC	decimal	WHT.WHTTaxableFeesCreditLC	Taxable Fee Amount Cr BC
tdWHTTaxableFeesCreditTC	decimal	WHT.WHTTaxableFeesCreditTC	Taxable Fee Amount Cr TC
tdWHTTaxableFeesDebitCC	decimal	WHT.WHTTaxableFeesDebitCC	Taxable Fee Amount Dr SC
tdWHTTaxableFeesDebitLC	decimal	WHT.WHTTaxableFeesDebitLC	Taxable Fee Amount Dr LC
tdWHTTaxableFeesDebitTC	decimal	WHT.WHTTaxableFeesDebitTC	Taxable Fee Amount Dr TC
tdWHTTotAmtCreditCC	decimal	WHT.WHTTotAmtCreditCC	Total Amount Cr SC
tdWHTTotAmtCreditLC	decimal	WHT.WHTTotAmtCreditLC	Total Amount Cr BC
tdWHTTotAmtCreditTC	decimal	WHT.WHTTotAmtCreditTC	Total Amount Cr TC
tdWHTTotAmtDebitCC	decimal	WHT.WHTTotAmtDebitCC	Total Amount Dr SC
tdWHTTotAmtDebitLC	decimal	WHT.WHTTotAmtDebitLC	Total Amount Dr LC
tdWHTTotAmtDebitTC	decimal	WHT.WHTTotAmtDebitTC	Total Amount Dr TC
tdWHTVATAmtCreditCC	decimal	WHT.WHTVATAmtCreditCC	Tax Amount Cr SC
tdWHTVATAmtCreditLC	decimal	WHT.WHTVATAmtCreditLC	Tax Amount Cr BC
tdWHTVATAmtCreditTC	decimal	WHT.WHTVATAmtCreditTC	Tax Amount Cr TC
tdWHTVATAmtDebitCC	decimal	WHT.WHTVATAmtDebitCC	Tax Amount Dr SC
tdWHTVATAmtDebitLC	decimal	WHT.WHTVATAmtDebitLC	Tax Amount Dr LC
tdWHTVATAmtDebitTC	decimal	WHT.WHTVATAmtDebitTC	Tax Amount Dr TC
ttLastModifiedDate	date	WHT.LastModifiedDate	Last Modified Date
tiLastModifiedTime	integer	WHT.LastModifiedTime	Last Modified Time
tcLastModifiedUser	character	WHT.LastModifiedUser	Last Modified User
tcWHTCompanyLCCurrencyCode	character	WHT.WHTCompanyLCCurrencyCode	Base Currency
tcWHTCreditorSharedSetCode	character	WHT.WHTCreditorSharedSetCode	Supplier Shared Set Code
WHTCustom	character	calculated	custom fields
CurrencyCustom	character	calculated	custom fields
VatCustom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(WHT),rowid(Currency),rowid(Vat)

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 = "BWithholdingTax".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "SelectWHT".
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/bwithholdingtax.selectwht.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.