business logic query CreditorWTDirectBank

Description

This query will retireve all the Supplier Invoice with Withholding tax records that were Paid using option "Allocate to Invoice" in menu Banking Entry Create .

Parameters

iiCompanyId	integer	Company id
iiFromVoucher	integer
iiToVoucher	integer
icCertFormatCode	character
icFromCreditorCode	character
icFromJournalCode	character
iiFromPeriod	integer
iiFromWHTNumber	integer
ilIsPrinted	logical
icToCreditorCode	character
icToJournalCode	character
iiToWHTNumber	integer
iiYear	integer

query condition

each CInvoice where

CInvoice.Company_Id = iiCompanyId AND
CInvoice.CInvoiceIsWHT = TRUE AND
CInvoice.CInvoiceVoucher >= iiFromVoucher AND
CInvoice.CInvoiceVoucher <= iiToVoucher

first Creditor (inner-join) where

Creditor.SharedSet_Id = vi_CREDITOR_sharedset(iiCompanyId) AND
Creditor.Creditor_ID = CInvoice.Creditor_ID 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

first Journal (inner-join) where

Journal.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal.Journal_ID = CInvoice.Journal_ID AND
Journal.JournalCode >= icFromJournalCode AND
Journal.JournalCode <= icToJournalCode

each WHT (inner-join) where

WHT.Company_Id = iiCompanyId AND
WHT.CInvoice_ID = CInvoice.CInvoice_ID AND
WHT.CDocumentInvoiceXref_ID = 0 AND
WHT.BankStateAlloc_ID <> 0 AND
WHT.WHTIsPrinted = ilIsPrinted AND
YEAR(WHT.WHTPaymentDate) = iiYear AND
MONTH(WHT.WHTPaymentDate) = iiFromPeriod AND
WHT.WHTNumber >= iiFromWHTNumber AND
WHT.WHTNumber <= iiToWHTNumber AND
(WHT.WHTStatus = 'WHTINITIAL' OR
WHT.WHTStatus = 'WHTDUE' )

first Vat (inner-join) where

Vat.Vat_ID = WHT.Vat_ID AND

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

query resultset tqCreditorWTDirectBank

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
tcAddressState	character	Address.AddressState	Obsolete field
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.
tcAddressZip	character	Address.AddressZip	Zip. The postal code or US zip code associated with this address. This field is used to decide the tax zone for the address. This field can be blank.
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.
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.
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.
ttCInvoicePostingDate	date	CInvoice.CInvoicePostingDate	Posting Date. This filed indicates the date on which the invoice is to be posted.
	api annotation:		This field defaults from the invoice creation date.
tiCInvoicePostingPeriod	integer	CInvoice.CInvoicePostingPeriod	GL Period.This field indicates the period when the invoice to be posted. If this field is modified, the posting and tax dates are changed correspondingly.
	api annotation:		This field defaults to the period associated with the posting date.
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.
tiCInvoicePostingYearPeriod	integer	CInvoice.CInvoicePostingYearPeriod	Year/Period. This field indicates the accounting year and period 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 and period 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.
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.
tcCountryCode	character	Country.CountryCode	Country Code
tcCreditorBirthCity	character	Creditor.CreditorBirthCity	Birth City
ttCreditorBirthDate	date	Creditor.CreditorBirthDate	Birth Date
tcCreditorCode	character	Creditor.CreditorCode	Supplier Code
tcCreditorForeignFiscalCode	character	Creditor.CreditorForeignFiscalCode	Foreign Fiscal Code
tcCreditorTaxIDFiscalCode	character	Creditor.CreditorTaxIDFiscalCode	Tax ID / Fiscal Code
tiJournal_ID	integer	Journal.Journal_ID	Record ID
tcJournalCode	character	Journal.JournalCode	A daybook code (maximum eight characters).
tctx2_group	character	tx2_mstr.tx2_group	Tax Group
tctx2_tax_code	character	tx2_mstr.tx2_tax_code	Tax Code
tiVat_ID	integer	Vat.Vat_ID	Record ID
tcVatCode	character	Vat.VatCode	Tax Code
tcVatDescription	character	Vat.VatDescription	Description
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
tdWHTChargeAmtCreditTC	decimal	WHT.WHTChargeAmtCreditTC	Charge Amount Cr TC
tdWHTChargeAmtDebitTC	decimal	WHT.WHTChargeAmtDebitTC	Charge Amount Dr TC
tcWHTChargeType	character	WHT.WHTChargeType	Charge Type
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
ttWHTPaymentDate	date	WHT.WHTPaymentDate	Payment Date
tcWHTPaymentReference	character	WHT.WHTPaymentReference	Payment Reference
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
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

Internal usage

QadFinancials

method BCreditorReport.CreditorWHTCertificate