project QadFinancials > class BCreditorReport > business logic query CreditorWHTByDocument

Description

Get WHT data by payment.


Parameters


iiCompanyIdintegerCompany id
icFromCreditorCodecharacter
icToCreditorCodecharacter
iiFromWHTNumberinteger
iiToWHTNumberinteger
iiYearinteger
icCertificateFormatcharacter
iiFromPaymentNumberinteger
iiToPaymentNumberinteger
icPaySelCodecharacter
itFromPaymentCreationDatedate
itToPaymentCreationDatedate
ilIsPrintedlogical


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 namedata typedb fielddescription
tcBankNumbercharacterBankNumber.BankNumberBank Number
tiCDocument_IDintegerCDocument.CDocument_IDRecord ID
ttCDocumentCreationDatedateCDocument.CDocumentCreationDateCreation Date.This field indicate the payment creation date.
This field is read-only.
tdCDocumentInvoiceXrefAlloTCdecimalCDocumentInvoiceXref.CDocumentInvoiceXrefAlloTCTC Allocated Amount. This field indicates the invocie amount already Allocated in transaction currency.
tdCDocumentInvoiceXrefDiscTCdecimalCDocumentInvoiceXref.CDocumentInvoiceXrefDiscTCTC Discount Amount. This field indicates the discount amount of the invoice already allocated by transaction currency.
tiCDocumentNumberintegerCDocument.CDocumentNumberDocument Number. This field hold a unified number for report use.
tdCDocumentOriginalCreditCCdecimalCDocument.CDocumentOriginalCreditCCSC Original Credit. This field hold the amount of the payment in management currency.
tdCDocumentOriginalCreditLCdecimalCDocument.CDocumentOriginalCreditLCBC Original Credit.This field holds the amount of the Payment in baisc currency.
tdCDocumentOriginalCreditTCdecimalCDocument.CDocumentOriginalCreditTCAmount.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.
tdCDocumentOriginalDebitCCdecimalCDocument.CDocumentOriginalDebitCCTC Original Debit. This field holds the debit value of the payment in management currency.
tdCDocumentOriginalDebitLCdecimalCDocument.CDocumentOriginalDebitLCTC Original Debit. This field holds the debit value of the payment in base currency.
tdCDocumentOriginalDebitTCdecimalCDocument.CDocumentOriginalDebitTCTC Original Debit. This field holds the debit value of the payment in transaction currency.
tiCDocumentPrePrintedNumberintegerCDocument.CDocumentPrePrintedNumberPreprinted Number.This field is used to define a number or range of numbers when you are reprinting the payment.
tcCDocumentReferencecharacterCDocument.CDocumentReferenceReference. Reference text for the payment.
tiCDocumentYearintegerCDocument.CDocumentYearYear/Number. This field displays the accounting year and payment sequence number, which is automatically generated by the accounting year.
tiCInvoiceCurrency_IDintegerCInvoice.CInvoiceCurrency_IDCurrency Code
tdCInvoiceOriginalCreditTCdecimalCInvoice.CInvoiceOriginalCreditTCTC 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.
tdCInvoiceOriginalDebitTCdecimalCInvoice.CInvoiceOriginalDebitTCTC 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_IDintegerCDocument.Company_IDLink to Company
tcCreditorCodecharacterCreditor.CreditorCodeSupplier Code
tcPayFormatTypeCodecharacterBankPayFormat.PayFormatTypeCodePayment Format
tcPaySelCodecharacterPaySel.PaySelCodeCode
tcVatCodecharacterVat.VatCodeTax Code
tdWHTAmtCreditCCdecimalWHT.WHTAmtCreditCCWHT Amount Cr SC
tdWHTAmtCreditLCdecimalWHT.WHTAmtCreditLCWHT Amount Cr BC
tdWHTAmtCreditTCdecimalWHT.WHTAmtCreditTCWHT Amount Cr TC
tdWHTAmtDebitCCdecimalWHT.WHTAmtDebitCCWHT Amount Dr SC
tdWHTAmtDebitLCdecimalWHT.WHTAmtDebitLCWHT Amount Dr LC
tdWHTAmtDebitTCdecimalWHT.WHTAmtDebitTCWHT Amount Dr TC
tcWHTCertFormatCodecharacterWHTCertFormat.WHTCertFormatCodeFormat Code
tdWHTChargeAmtCreditTCdecimalWHT.WHTChargeAmtCreditTCCharge Amount Cr TC
tdWHTChargeAmtDebitTCdecimalWHT.WHTChargeAmtDebitTCCharge Amount Dr TC
tiWHTNumberintegerWHT.WHTNumberWHT Number
tdWHTPaidAmtCreditTCdecimalWHT.WHTPaidAmtCreditTCPaid Amount Cr TC
tdWHTPaidAmtDebitTCdecimalWHT.WHTPaidAmtDebitTCPaid Amount Dr TC
ttWHTPaymentDatedateWHT.WHTPaymentDatePayment Date
tcWHTPaymentReferencecharacterWHT.WHTPaymentReferencePayment Reference
tdWHTPercentagedecimalWHT.WHTPercentageWHT Tax Rate Perc
tdWHTRetainedAmtCreditCCdecimalWHT.WHTRetainedAmtCreditCCRetained Amount Cr SC
tdWHTRetainedAmtCreditLCdecimalWHT.WHTRetainedAmtCreditLCRetained Amount Cr BC
tdWHTRetainedAmtCreditTCdecimalWHT.WHTRetainedAmtCreditTCRetained Amount Cr TC
tdWHTRetainedAmtDebitCCdecimalWHT.WHTRetainedAmtDebitCCRetained Amount Dr SC
tdWHTRetainedAmtDebitLCdecimalWHT.WHTRetainedAmtDebitLCRetained Amount Dr LC
tdWHTRetainedAmtDebitTCdecimalWHT.WHTRetainedAmtDebitTCRetained Amount Dr TC
tdWHTTaxableFeesCreditCCdecimalWHT.WHTTaxableFeesCreditCCTaxable Fee Amount Cr SC
tdWHTTaxableFeesCreditLCdecimalWHT.WHTTaxableFeesCreditLCTaxable Fee Amount Cr BC
tdWHTTaxableFeesCreditTCdecimalWHT.WHTTaxableFeesCreditTCTaxable Fee Amount Cr TC
tdWHTTaxableFeesDebitCCdecimalWHT.WHTTaxableFeesDebitCCTaxable Fee Amount Dr SC
tdWHTTaxableFeesDebitLCdecimalWHT.WHTTaxableFeesDebitLCTaxable Fee Amount Dr LC
tdWHTTaxableFeesDebitTCdecimalWHT.WHTTaxableFeesDebitTCTaxable Fee Amount Dr TC
tdWHTTotAmtCreditCCdecimalWHT.WHTTotAmtCreditCCTotal Amount Cr SC
tdWHTTotAmtCreditLCdecimalWHT.WHTTotAmtCreditLCTotal Amount Cr BC
tdWHTTotAmtCreditTCdecimalWHT.WHTTotAmtCreditTCTotal Amount Cr TC
tdWHTTotAmtDebitCCdecimalWHT.WHTTotAmtDebitCCTotal Amount Dr SC
tdWHTTotAmtDebitLCdecimalWHT.WHTTotAmtDebitLCTotal Amount Dr LC
tdWHTTotAmtDebitTCdecimalWHT.WHTTotAmtDebitTCTotal Amount Dr TC


Internal usage


QadFinancials
method BCreditorReport.CreditorWHTCertificate