business logic query GetAllInvoiceForWHT

Parameters

iiCompanyId	integer	Company id
icFromCreditor	character
icToCreditor	character
iiPostingYear	integer
icCDocumentStatusList	character	contains a collection of cdocument statusses
iiYear	integer	Year
iiPeriod	integer	Period
icFromJournal	character	Journal From
icToJournal	character	Journal till
iiFromVoucher	integer	From Vouchernumber
iiToVoucher	integer	Till vouchernumber

query condition

each CInvoice where

CInvoice.Company_Id = iiCompanyId AND
CInvoice.CInvoiceIsWHT = yes AND
CInvoice.CInvoicePostingYear = iiPostingYear AND
CInvoice.CInvoiceVoucher >= iiFromVoucher AND
CInvoice.CInvoiceVoucher <= iiToVoucher

each CInvoiceWHT (inner-join) where

CInvoiceWHT.CInvoice_ID = CInvoice.CInvoice_ID AND

first Vat (outer-join) where

Vat.Vat_ID = CInvoiceWHT.Vat_ID AND

first tx2_mstr (outer-join) where

tx2_mstr.tx2_tax_code = Vat.VatCode AND

each WHT (outer-join) where

WHT.Company_Id = iiCompanyId AND
WHT.CInvoiceWHT_ID = CInvoiceWHT.CInvoiceWHT_ID AND
WHT.WHTStatus can-do icCDocumentStatusList AND
WHT.WHTPeriodYear = iiYear AND
WHT.WHTPeriodPeriod = iiPeriod

first Creditor (inner-join) where

Creditor.SharedSet_Id = vi_CREDITOR_sharedset(iiCompanyId) AND
Creditor.Creditor_ID = CInvoice.Creditor_ID AND
Creditor.CreditorCode >= icFromCreditor AND
Creditor.CreditorCode <= icToCreditor

first Journal (inner-join) where

Journal.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal.Journal_ID = CInvoice.Journal_ID AND
Journal.JournalCode >= icFromJournal AND
Journal.JournalCode <= icToJournal

query resultset tqGetAllInvoiceForWHT

field name	data type	db field	description
tiCInvoice_ID	integer	CInvoice.CInvoice_ID	Record ID
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.
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.
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.
tcCInvoiceReference	character	CInvoice.CInvoiceReference	Reference. An alphanumeric reference to help identify the invoice in the system. This reference is typically the ID number of the invoice received from the supplier.
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.
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.
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.
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.
tdCInvoiceWHTAmtCreditTC	decimal	CInvoiceWHT.CInvoiceWHTAmtCreditTC	TC WHT Amount (CR)
tdCInvoiceWHTAmtDebitTC	decimal	CInvoiceWHT.CInvoiceWHTAmtDebitTC	TC WHT Amount (DR)
tdCInvoiceWHTBaseAmtCreditTC	decimal	CInvoice.CInvoiceWHTBaseAmtCreditTC	TC WHT Base Amount (CR)
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)
tdCInvoiceWHTTotAmtCreditTC	decimal	CInvoice.CInvoiceWHTTotAmtCreditTC	TC WHT Total Amount (CR)
tdCInvoiceWHTTotAmtDebitTC	decimal	CInvoice.CInvoiceWHTTotAmtDebitTC	TC WHT Total Amount (DR)
tiCreditor_ID	integer	Creditor.Creditor_ID	Record ID
tcCreditorCode	character	Creditor.CreditorCode	Supplier Code
tcJournalCode	character	Journal.JournalCode	A daybook code (maximum eight characters).
tctx2_group	character	tx2_mstr.tx2_group	Tax Group
tdtx2_pct_recv	decimal	tx2_mstr.tx2_pct_recv	Percent Recoverable
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

Internal usage

QadFinancials

method BCreditorReport.CreditorWithholdingTax