business logic query GetWHTWithPayments

Parameters

iiCompanyId	integer	Company id
icCertFormatCode	character
icFromCreditorCode	character
iiMonth	integer
iiPaymentNumberFrom	integer
iiPaymentNumberTo	integer
icPaymentSelCode	character
icToCreditorCode	character
iiWHTNumberFrom	integer
iiWHTNumberTo	integer
iiYear	integer

query condition

each Creditor where

Creditor.SharedSet_Id = vi_CREDITOR_sharedset(iiCompanyId) AND
Creditor.CreditorCode >= icFromCreditorCode AND
Creditor.CreditorCode <= icToCreditorCode

first BusinessRelation (inner-join) where

BusinessRelation.BusinessRelation_ID = Creditor.BusinessRelation_ID AND

each Address (inner-join) where

Address.BusinessRelation_ID = BusinessRelation.BusinessRelation_ID AND

first AddressType (inner-join) where

AddressType.AddressType_ID = Address.AddressType_ID AND
AddressType.AddressTypeCode = 'HEADOFFICE'

first Country (inner-join) where

Country.Country_ID = Address.Country_ID AND

each CInvoice (inner-join) where

CInvoice.Company_Id = iiCompanyId AND
CInvoice.Creditor_ID = Creditor.Creditor_ID AND

each CDocumentInvoiceXref (inner-join) where

CDocumentInvoiceXref.CInvoice_ID = CInvoice.CInvoice_ID AND

first CDocument (inner-join) where

CDocument.CDocument_ID = CDocumentInvoiceXref.CDocument_ID AND
CDocument.CDocumentNumber >= iiPaymentNumberFrom AND
CDocument.CDocumentNumber <= iiPaymentNumberTo AND
CDocument.Company_ID > 0

first PaySel (conditional-join) where

PaySel.Company_Id = iiCompanyId AND
PaySel.PaySel_ID = CDocument.PaySel_ID AND
PaySel.PaySelCode = icPaymentSelCode

each WHT (inner-join) where

WHT.Company_Id = iiCompanyId AND
WHT.CDocumentInvoiceXref_ID = CDocumentInvoiceXref.CDocumentInvoiceXref_ID AND
year(WHT.WHTPaymentDate) = iiYear AND
month(WHT.WHTPaymentDate) = iiMonth AND
WHT.WHTNumber >= iiWHTNumberFrom AND
WHT.WHTNumber <= iiWHTNumberTo AND
WHT.WHTStatus <> 'WHTINITIAL' AND
WHT.WHTStatus <> 'WHTVOID'

first CInvoiceWHT (inner-join) where

CInvoiceWHT.CInvoiceWHT_ID = WHT.CInvoiceWHT_ID AND

first Vat (inner-join) where

(vat.vat_id = wht.vat_id OR
0 = wht.vat_id )

first tx2_mstr (inner-join) where

tx2_mstr.tx2_tax_code = Vat.VatCode AND

first WHTCertFormat (conditional-join) where

WHTCertFormat.WHTCertFormat_ID = WHT.WHTCertFormat_ID AND
WHTCertFormat.WHTCertFormatCode = icCertFormatCode

first Journal (inner-join) where

Journal.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal.Journal_ID = CInvoice.Journal_ID AND

query resultset tqGetWHTWithPayments

field name	data type	db field	description
tcAddressCity	character	Address.AddressCity	City. The city for this address. This field can be used to decide the tax zone of the address.
tcAddressCounty	character	Address.AddressCounty	Obsolete field
tcAddressName	character	Address.AddressName	Name. The full name of this particular address. The name of a headoffice address is the same as the name of the business relationship and can not be modified.
	api annotation:		PartialUpdate = yes - defaults to the name of the businessRelation.
tcAddressStreet1	character	Address.AddressStreet1	Address: Line one of address details. This field can be blank.
tcAddressStreet2	character	Address.AddressStreet2	Address.Line two of address details. This field can be blank.
tcAddressStreet3	character	Address.AddressStreet3	Address.Line three of address details. This field can be blank.
tiBusinessRelation_ID	integer	BusinessRelation.BusinessRelation_ID	Record ID
tiBusinessRelation_ID1	integer	Address.BusinessRelation_ID	Link to BusinessRelation
tcBusinessRelationCode	character	BusinessRelation.BusinessRelationCode	Business Relation. A code to identify the business relation. If this field does not have a input from the user, the system automatically generates a number for the record based on the sequence defined in Business Relation Autonumber Create.
tcBusinessRelationName1	character	BusinessRelation.BusinessRelationName1	Name: The full name of the business relation. This field sets the default name for linked addresses such as customers and suppliers.
	api annotation:		PartialUpdate = yes - defaults to the BusinessRelationCode.
tiCDocument_ID	integer	CDocumentInvoiceXref.CDocument_ID	Link to CDocument
ttCDocumentCreationDate	date	CDocument.CDocumentCreationDate	Creation Date.This field indicate the payment creation date. This field is read-only.
ttCDocumentDueDate	date	CDocument.CDocumentDueDate	Due Date. The due date for this payment.
tiCDocumentInvoiceXref_ID	integer	CDocumentInvoiceXref.CDocumentInvoiceXref_ID	Record ID
tdCDocumentInvoiceXrefDiscTC	decimal	CDocumentInvoiceXref.CDocumentInvoiceXrefDiscTC	TC Discount Amount. This field indicates the discount amount of the invoice already allocated by transaction currency.
tcCDocumentReference	character	CDocument.CDocumentReference	Reference. Reference text for the payment.
tcCDocumentStatus	character	CDocument.CDocumentStatus	Status. The payment status. The default value for a new record is Initial.
tcCDocumentSubType	character	CDocument.CDocumentSubType	Subtype.This field indicates whether the payment is manual or automatic. You create manual supplier payments through the Supplier Payment activities, and automatic payments through the Supplier Payment Selection activities. This field is read-only.
tiCInvoice_ID	integer	CDocumentInvoiceXref.CInvoice_ID	Link to CInvoice
tiCInvoiceCurrency_ID	integer	CInvoice.CInvoiceCurrency_ID	Currency Code
ttCInvoiceDate	date	CInvoice.CInvoiceDate	Invoice Date. This field indicates the invoice creation date. The system uses this field with the credit terms to calculate due date and discount date.
	api annotation:		This field defaults from the system date.
tdCInvoiceOriginalCreditCC	decimal	CInvoice.CInvoiceOriginalCreditCC	SC Invoice Amount (CR). This field displays the credit total invoice amount in the statutory currency. This field is calculated by the system using the TC Invoice Amount(CR) and the SC Rate.
tdCInvoiceOriginalCreditLC	decimal	CInvoice.CInvoiceOriginalCreditLC	BC Invoice Amount(CR). This field displays the credit total invoice amount in the base currency. This field is calculated by the system using the TC Invoice Amount(CR) and the Exchange rate.
tdCInvoiceOriginalCreditTC	decimal	CInvoice.CInvoiceOriginalCreditTC	TC Invoice Amount CR. This field displays the credit total invoice amount in the transaction currency. If you modify this field, the system automatically recalculates the exchange rate to ensure that the BC Invoice Amount remains the same.
tdCInvoiceOriginalDebitCC	decimal	CInvoice.CInvoiceOriginalDebitCC	SC Invoice Amount (DR). This field displays the debit total invoice amount in the statutory currency. This field is calculated by the system using the TC Invoice Amount(CR) and the SC Rate.
tdCInvoiceOriginalDebitLC	decimal	CInvoice.CInvoiceOriginalDebitLC	BC Invoice Amount(DR). This field displays the debit total invoice amount in the base currency. This field is calculated by the system using the TC Invoice Amount(DR) and the Exchange rate.
tdCInvoiceOriginalDebitTC	decimal	CInvoice.CInvoiceOriginalDebitTC	TC AP Amount. This field displays the debit total invoice amount in the transaction currency. If you modify this field, the system automatically recalculates the exchange rate to ensure that the TC Invoice Amount remains the same.
tiCInvoicePostingYear	integer	CInvoice.CInvoicePostingYear	Year.This field indicates the accounting year for the invoice. If you modify these fields, the posting and tax dates are changed correspondingly.
	api annotation:		This field defaults to the accounting year associated with the posting date.
tcCInvoiceType	character	CInvoice.CInvoiceType	Invoice Type. This field identifies the invoice type. The value can be Invoice Correction and Credit Note Correction type only when the appropriate daybook types have already been defined.
tdCInvoiceVatBaseCreditCC	decimal	CInvoice.CInvoiceVatBaseCreditCC	SC Base Amount CR. This field displays the credit base amount in the statutory currency. This field is calculated by the system using the TC Base Amount CR and the SC rate.
tdCInvoiceVatBaseCreditLC	decimal	CInvoice.CInvoiceVatBaseCreditLC	BC Base Amount CR. This field displays the credit base amount in the base currency. This field is calculated by the system using the TC Base Amount CR and the Exchange rate.
tdCInvoiceVatBaseCreditTC	decimal	CInvoice.CInvoiceVatBaseCreditTC	TC Base Amount CR. This field displays the credit base amount in the transaction currency. This field is calculated by the system using the total invoice amount (TC) and the applicable tax rate code.
tdCInvoiceVatBaseDebitCC	decimal	CInvoice.CInvoiceVatBaseDebitCC	SC Base Amount DR. This field displays the debit base amount in the statutory currency. This field is calculated by the system using the TC Base Amount DR and the SC rate.
tdCInvoiceVatBaseDebitLC	decimal	CInvoice.CInvoiceVatBaseDebitLC	BC Base Amount DR. This field displays the debit base amount in the base currency. This field is calculated by the system using the TC Base Amount DR and the Exchange rate.
tdCInvoiceVatBaseDebitTC	decimal	CInvoice.CInvoiceVatBaseDebitTC	TC Base Amount DR. This field displays the debit base amount in the transaction currency. This field is calculated by the system using the total invoice amount (TC) and the applicable tax rate code.
tdCInvoiceVatCreditCC	decimal	CInvoice.CInvoiceVatCreditCC	SC Tax Amount CR. This field displays the credit tax amount in the statutory currency. This field is calculated by the system using the TC Tax Amount CR and the SC rate.
tdCInvoiceVatCreditLC	decimal	CInvoice.CInvoiceVatCreditLC	BC Tax Amount CR. This field displays the debit tax amount in the base currency. This field is calculated by the system using the TC Tax Amount CR and the Exchange rate.
tdCInvoiceVatCreditTC	decimal	CInvoice.CInvoiceVatCreditTC	TC Tax Amount CR. This field displays the credit tax amount (TC) calculated by the system using the total invoice amount (TC) and the applicable tax rate code.
tdCInvoiceVatDebitCC	decimal	CInvoice.CInvoiceVatDebitCC	BC Tax Amount DR. This field displays the debit tax amount in the statutory currency. This field is calculated by the system using the TC Tax Amount DR and the SC rate.
tdCInvoiceVatDebitLC	decimal	CInvoice.CInvoiceVatDebitLC	BC Tax Amount DR. This field displays the debit tax amount in the base currency. This field is calculated by the system using the TC Tax Amount DR and the Exchange rate.
tdCInvoiceVatDebitTC	decimal	CInvoice.CInvoiceVatDebitTC	TC Tax Amount DR. This field displays the debit tax amount (TC) calculated by the system using the total invoice amount (TC) and the applicable tax rate code.
tiCInvoiceVoucher	integer	CInvoice.CInvoiceVoucher	Voucher. This field displays the numeric identifier assigned to the posting. When the daybook of the journal entry is changed (after transfer), the voucher is cleared. Voucher must be unique in one GL period. When the user input is zero, the system automatically assign a value not used yet for this field.
tdCInvoiceWHTBaseAmtCreditCC	decimal	CInvoice.CInvoiceWHTBaseAmtCreditCC	SC WHT Base Amount (CR)
tdCInvoiceWHTBaseAmtCreditLC	decimal	CInvoice.CInvoiceWHTBaseAmtCreditLC	BC WHT Base Amount (CR)
tdCInvoiceWHTBaseAmtCreditTC	decimal	CInvoice.CInvoiceWHTBaseAmtCreditTC	TC WHT Base Amount (CR)
tdCInvoiceWHTBaseAmtDebitCC	decimal	CInvoice.CInvoiceWHTBaseAmtDebitCC	SC WHT Base Amount (DR)
tdCInvoiceWHTBaseAmtDebitLC	decimal	CInvoice.CInvoiceWHTBaseAmtDebitLC	BC WHT Base Amount (DR)
tdCInvoiceWHTBaseAmtDebitTC	decimal	CInvoice.CInvoiceWHTBaseAmtDebitTC	TC WHT Base Amount (DR)
tdCInvoiceWHTExpenseCreditTC	decimal	CInvoiceWHT.CInvoiceWHTExpenseCreditTC	TC Expense (CR)
tdCInvoiceWHTExpenseDebitTC	decimal	CInvoiceWHT.CInvoiceWHTExpenseDebitTC	TC Expense (DR)
tdCInvoiceWHTFeeCreditTC	decimal	CInvoiceWHT.CInvoiceWHTFeeCreditTC	TC Fee (CR)
tdCInvoiceWHTFeeDebitTC	decimal	CInvoiceWHT.CInvoiceWHTFeeDebitTC	TC Fee (DR)
tdCInvoiceWHTTotAmtCreditCC	decimal	CInvoice.CInvoiceWHTTotAmtCreditCC	SC WHT Total Amount (CR)
tdCInvoiceWHTTotAmtCreditLC	decimal	CInvoice.CInvoiceWHTTotAmtCreditLC	BC WHT Total Amount (CR)
tdCInvoiceWHTTotAmtCreditTC	decimal	CInvoice.CInvoiceWHTTotAmtCreditTC	TC WHT Total Amount (CR)
tdCInvoiceWHTTotAmtDebitCC	decimal	CInvoice.CInvoiceWHTTotAmtDebitCC	SC WHT Total Amount (DR)
tdCInvoiceWHTTotAmtDebitLC	decimal	CInvoice.CInvoiceWHTTotAmtDebitLC	BC WHT Total Amount (DR)
tdCInvoiceWHTTotAmtDebitTC	decimal	CInvoice.CInvoiceWHTTotAmtDebitTC	TC WHT Total Amount (DR)
tcCountryCode	character	Country.CountryCode	Country Code
tiCreditor_ID	integer	Creditor.Creditor_ID	Record ID
tcCreditorCode	character	Creditor.CreditorCode	Supplier Code
tcCreditorTaxIDFiscalCode	character	Creditor.CreditorTaxIDFiscalCode	Tax ID / Fiscal Code
tiJournal_ID	integer	CInvoice.Journal_ID	Daybook Code
tcJournalCode	character	Journal.JournalCode	A daybook code (maximum eight characters).
tcPaymentStatusCalc	character	calculated
tcPaymentSubTypeCalc	character	calculated
tcPaySelCode	character	PaySel.PaySelCode	Code
tctx2_group	character	tx2_mstr.tx2_group	Tax Group
tdtx2_tax_pct	decimal	tx2_mstr.tx2_tax_pct	Tax Rate
tiVat_ID	integer	Vat.Vat_ID	Record ID
tcVatCode	character	Vat.VatCode	Tax Code
tcVatDescription	character	Vat.VatDescription	Description
tdWHTAmtCreditTC	decimal	WHT.WHTAmtCreditTC	WHT Amount Cr TC
tdWHTAmtDebitTC	decimal	WHT.WHTAmtDebitTC	WHT Amount Dr TC
tcWHTCertFormatCode	character	WHTCertFormat.WHTCertFormatCode	Format Code
tdWHTChargeAmtCreditTC	decimal	WHT.WHTChargeAmtCreditTC	Charge Amount Cr TC
tdWHTChargeAmtDebitTC	decimal	WHT.WHTChargeAmtDebitTC	Charge Amount Dr TC
tcWHTChargeType	character	WHT.WHTChargeType	Charge Type
tcWHTCInvoiceReference	character	WHT.WHTCInvoiceReference	Invoice Reference
tiWHTNumber	integer	WHT.WHTNumber	WHT Number
tdWHTPaidAmtCreditTC	decimal	WHT.WHTPaidAmtCreditTC	Paid Amount Cr TC
tdWHTPaidAmtDebitTC	decimal	WHT.WHTPaidAmtDebitTC	Paid Amount Dr TC
ttWHTPaymentDate	date	WHT.WHTPaymentDate	Payment Date
tcWHTPaymentReference	character	WHT.WHTPaymentReference	Payment Reference
tdWHTTaxableFeesCreditTC	decimal	WHT.WHTTaxableFeesCreditTC	Taxable Fee Amount Cr TC
tdWHTTaxableFeesDebitTC	decimal	WHT.WHTTaxableFeesDebitTC	Taxable Fee Amount Dr TC
tdWHTTotAmtCreditLC	decimal	WHT.WHTTotAmtCreditLC	Total Amount Cr BC
tdWHTTotAmtCreditTC	decimal	WHT.WHTTotAmtCreditTC	Total Amount Cr TC
tdWHTTotAmtDebitLC	decimal	WHT.WHTTotAmtDebitLC	Total Amount Dr LC
tdWHTTotAmtDebitTC	decimal	WHT.WHTTotAmtDebitTC	Total Amount Dr TC
tdWHTVATAmtCreditTC	decimal	WHT.WHTVATAmtCreditTC	Tax Amount Cr TC
tdWHTVATAmtDebitTC	decimal	WHT.WHTVATAmtDebitTC	Tax Amount Dr TC

Internal usage

QadFinancials

method BCreditorReport.CreditorWithholdingTaxDue