business logic query CreditorWHTByDocument

Description

Get WHT data by payment.

Parameters

iiCompanyId	integer	Company id
icFromCreditorCode	character
icToCreditorCode	character
iiFromWHTNumber	integer
iiToWHTNumber	integer
iiYear	integer
icCertificateFormat	character
iiFromPaymentNumber	integer
iiToPaymentNumber	integer
icPaySelCode	character
itFromPaymentCreationDate	date
itToPaymentCreationDate	date
ilIsPrinted	logical

query condition

each CDocument where

CDocument.CDocumentNumber >= iiFromPaymentNumber AND
CDocument.CDocumentNumber <= iiToPaymentNumber AND
CDocument.CDocumentYear = iiYear AND
CDocument.CDocumentCreationDate >= itFromPaymentCreationDate AND
CDocument.CDocumentCreationDate <= itToPaymentCreationDate AND
(CDocument.CDocumentStatus <> 'VOID' AND
CDocument.CDocumentStatus <> 'INITIAL' AND
CDocument.CDocumentStatus <> 'BOUNCED' ) AND
CDocument.Company_ID > 0

first BankNumber (inner-join) where

BankNumber.BankNumber_ID = CDocument.BankNumber_ID AND

first BankPayFormat (inner-join) where

BankPayFormat.BankPayFormat_ID = BankNumber.BankPayFormat_ID AND

each CDocumentInvoiceXref (inner-join) where

CDocumentInvoiceXref.CDocument_ID = CDocument.CDocument_ID AND

first CInvoice (inner-join) where

CInvoice.Company_Id = iiCompanyId AND
CInvoice.CInvoice_ID = CDocumentInvoiceXref.CInvoice_ID AND

each WHT (inner-join) where

WHT.Company_Id = iiCompanyId AND
WHT.CDocumentInvoiceXref_ID = CDocumentInvoiceXref.CDocumentInvoiceXref_ID AND
WHT.WHTNumber >= iiFromWHTNumber AND
WHT.WHTNumber <= iiToWHTNumber AND
WHT.WHTIsPrinted = ilIsPrinted AND
(WHT.WHTStatus = 'WHTINITIAL' OR
WHT.WHTStatus = 'WHTDUE' ) AND
year(WHT.WHTPaymentDate) = iiYear

first Vat (inner-join) where

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

first WHTCertFormat (conditional-join) where

WHTCertFormat.WHTCertFormat_ID = WHT.WHTCertFormat_ID AND
WHTCertFormat.WHTCertFormatCode = icCertificateFormat

first Creditor (inner-join) where

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

first PaySel (conditional-join) where

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

query resultset tqCreditorWHTByDocument

field name	data type	db field	description
tcBankNumber	character	BankNumber.BankNumber	Bank Number
tiCDocument_ID	integer	CDocument.CDocument_ID	Record ID
ttCDocumentCreationDate	date	CDocument.CDocumentCreationDate	Creation Date.This field indicate the payment creation date. This field is read-only.
tdCDocumentInvoiceXrefAlloTC	decimal	CDocumentInvoiceXref.CDocumentInvoiceXrefAlloTC	TC Allocated Amount. This field indicates the invocie amount already Allocated in transaction currency.
tdCDocumentInvoiceXrefDiscTC	decimal	CDocumentInvoiceXref.CDocumentInvoiceXrefDiscTC	TC Discount Amount. This field indicates the discount amount of the invoice already allocated by transaction currency.
tiCDocumentNumber	integer	CDocument.CDocumentNumber	Document Number. This field hold a unified number for report use.
tdCDocumentOriginalCreditCC	decimal	CDocument.CDocumentOriginalCreditCC	SC Original Credit. This field hold the amount of the payment in management currency.
tdCDocumentOriginalCreditLC	decimal	CDocument.CDocumentOriginalCreditLC	BC Original Credit.This field holds the amount of the Payment in baisc currency.
tdCDocumentOriginalCreditTC	decimal	CDocument.CDocumentOriginalCreditTC	Amount.This field hold the value of the payment in the transaction currency. This field must be positive and can be entered manually or automatically by linking the payment to an open item.
tdCDocumentOriginalDebitCC	decimal	CDocument.CDocumentOriginalDebitCC	TC Original Debit. This field holds the debit value of the payment in management currency.
tdCDocumentOriginalDebitLC	decimal	CDocument.CDocumentOriginalDebitLC	TC Original Debit. This field holds the debit value of the payment in base currency.
tdCDocumentOriginalDebitTC	decimal	CDocument.CDocumentOriginalDebitTC	TC Original Debit. This field holds the debit value of the payment in transaction currency.
tiCDocumentPrePrintedNumber	integer	CDocument.CDocumentPrePrintedNumber	Preprinted Number.This field is used to define a number or range of numbers when you are reprinting the payment.
tcCDocumentReference	character	CDocument.CDocumentReference	Reference. Reference text for the payment.
tiCDocumentYear	integer	CDocument.CDocumentYear	Year/Number. This field displays the accounting year and payment sequence number, which is automatically generated by the accounting year.
tiCInvoiceCurrency_ID	integer	CInvoice.CInvoiceCurrency_ID	Currency Code
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.
tiCompany_ID	integer	CDocument.Company_ID	Link to Company
tcCreditorCode	character	Creditor.CreditorCode	Supplier Code
tcPayFormatTypeCode	character	BankPayFormat.PayFormatTypeCode	Payment Format
tcPaySelCode	character	PaySel.PaySelCode	Code
tcVatCode	character	Vat.VatCode	Tax Code
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
tcWHTCertFormatCode	character	WHTCertFormat.WHTCertFormatCode	Format Code
tdWHTChargeAmtCreditTC	decimal	WHT.WHTChargeAmtCreditTC	Charge Amount Cr TC
tdWHTChargeAmtDebitTC	decimal	WHT.WHTChargeAmtDebitTC	Charge Amount Dr TC
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
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

Internal usage

QadFinancials

method BCreditorReport.CreditorWHTCertificate