business logic query BaseRemittanceByCollCreditor

Parameters

iiCompanyId	integer	Company id
iiAddressType_ID	integer
icCreditorCodes	character
icFromPaySel	character
icToPaySel	character
icPaySel	character
ilIsRefInFile	logical
iiFromColYear	integer
iiTillColYear	integer
iiColYear	integer
iiFromColNumber	integer
iiToColNumber	integer
iiColNumber	integer
icFromColReference	character
icToColReference	character
icColReference	character
itFromPaySelDate	date
itToPaySelDate	date
ilSendRemittance	logical

query condition

each CCollection where

CCollection.Company_Id = iiCompanyId AND
CCollection.CCollectionNumber >= iiFromColNumber AND
CCollection.CCollectionNumber <= iiToColNumber AND
CCollection.CCollectionNumber = iiColNumber AND
CCollection.CCollectionYear >= iiFromColYear AND
CCollection.CCollectionYear <= iiTillColYear AND
CCollection.CCollectionYear = iiColYear AND
CCollection.CCollectionReference >= icFromColReference AND
CCollection.CCollectionReference <= icToColReference AND
CCollection.CCollectionReference = icColReference

each CDocument (inner-join) where

CDocument.Company_Id = iiCompanyId AND
CDocument.CCollection_ID = CCollection.CCollection_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
CInvoice.Company_ID <> 0 AND
CInvoice.Company_ID <> ?

first Creditor (inner-join) where

Creditor.SharedSet_Id = vi_CREDITOR_sharedset(iiCompanyId) AND
Creditor.Creditor_ID = CInvoice.Creditor_ID AND
Creditor.CreditorCode can-do icCreditorCodes AND
Creditor.CreditorIsSendRemittance = ilSendRemittance

first BusinessRelation (inner-join) where

BusinessRelation.BusinessRelation_ID = Creditor.BusinessRelation_ID AND

each Address (outer-join) where

Address.BusinessRelation_ID = BusinessRelation.BusinessRelation_ID AND
Address.AddressType_ID = iiAddressType_ID

first Country (outer-join) where

Country.Country_ID = Address.Country_ID AND

first State (outer-join) where

State.State_ID = Address.State_ID AND

first Currency (inner-join) where

Currency.Currency_ID = CInvoice.CInvoiceCurrency_ID AND

first Journal (inner-join) where

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

first Period (inner-join) where

Period.Company_Id = iiCompanyId AND
Period.Period_ID = CInvoice.Period_ID AND
Period.Company_ID <> 0 AND
Period.Company_ID <> ?

first PaySelLine (inner-join) where

PaySelLine.PaySelLine_ID = CDocumentInvoiceXref.PaySelLine_ID AND
PaySelLine.PaySelLineIsRefInFile = ilIsRefInFile

first BankNumber1 (outer-join) where

BankNumber1.BankNumber_ID = PaySelLine.BankNumber_ID AND

first PaySel (inner-join) where

PaySel.Company_Id = iiCompanyId AND
PaySel.PaySel_ID = PaySelLine.PaySel_ID AND
PaySel.PaySelCode >= icFromPaySel AND
PaySel.PaySelCode <= icToPaySel AND
PaySel.PaySelCode = icPaySel AND
PaySel.PaySelDate >= itFromPaySelDate AND
PaySel.PaySelDate <= itToPaySelDate

first GL (inner-join) where

GL.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL.GL_ID = PaySel.GL_ID AND

each BankNumber2 (outer-join) where

BankNumber2.ParentObject_ID = GL.GL_ID AND

query resultset tqBaseRemittanceByCollCreditor

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.
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.
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.
tcBankNumber	character	BankNumber1.BankNumber	Bank Number
tcBankNumberExtension	character	BankNumber1.BankNumberExtension	Bank Extension
tcBankNumberFormatted	character	BankNumber1.BankNumberFormatted	Formatted Bank Account No
tcBankNumberSwiftCode	character	BankNumber1.BankNumberSwiftCode	SWIFT Code
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.
tiCCollection_ID	integer	CCollection.CCollection_ID	Record ID
tcCCollectionName	character	CCollection.CCollectionName	Name
tiCCollectionNumber	integer	CCollection.CCollectionNumber	Number
tcCCollectionReference	character	CCollection.CCollectionReference	Reference
ttCCollectionRequestedDate	date	CCollection.CCollectionRequestedDate	Requested Date
tiCCollectionYear	integer	CCollection.CCollectionYear	Year
tiCInvoice_ID	integer	CInvoice.CInvoice_ID	Record ID
tcCInvoiceTSMNumber	character	CInvoice.CInvoiceTSMNumber	Payment Reference. This field displays a unique reference number to be included in the supplier payment file. This reference can consist of a Transfer with Structured Message (TSM) number. This field can be blank.
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)
tcCompanyBankNumber	character	BankNumber2.BankNumber	Bank Number
tcCompanyBankNumberExtension	character	BankNumber2.BankNumberExtension	Bank Extension
tcCompanyBankNumberFormatted	character	BankNumber2.BankNumberFormatted	Formatted Bank Account No
tcCompanyBankNumberSwiftCode	character	BankNumber2.BankNumberSwiftCode	SWIFT Code
tiCountry_ID	integer	Country.Country_ID	Record ID
tcCountryCode	character	Country.CountryCode	Country Code
tcCountryDescription	character	Country.CountryDescription	Description
tcCreditorCode	character	Creditor.CreditorCode	Supplier Code
tcCurrencyCode	character	Currency.CurrencyCode	Currency Code
tiCurrencyNumberOfDecimals	integer	Currency.CurrencyNumberOfDecimals	Number of Decimals
tcGLCode	character	GL.GLCode	A code identifying the GL account.
tdInvoiceBalanceCreditCC	decimal	CInvoice.CInvoiceBalanceCreditCC	BC Balance Amount CR. This field displays the sum of the credit amounts of all posting lines in transaction currency. This field is calculated by the system using the TC Base Amount CR and the SC rate.
tdInvoiceBalanceCreditLC	decimal	CInvoice.CInvoiceBalanceCreditLC	BC Balance Amount CR. This field displays the sum of the credit amounts of all posting lines in transaction currency. This field is calculated by the system using the TC Base Amount CR and the Exchange rate.
tdInvoiceBalanceCreditTC	decimal	CInvoice.CInvoiceBalanceCreditTC	TC Balance Amount CR. This field displays the sum of the credit amounts of all posting lines in transaction currency.
tdInvoiceBalanceDebitCC	decimal	CInvoice.CInvoiceBalanceDebitCC	SC Balance Amount DR. This field displays the sum of the debit amounts of all posting lines in statutory currency. This field is calculated by the system using the TC Base Amount DR and the SC rate.
tdInvoiceBalanceDebitLC	decimal	CInvoice.CInvoiceBalanceDebitLC	BC Balance Amount DR. This field displays the sum of the debit amounts of all posting lines in transaction currency. This field is calculated by the system using the TC Base Amount DR and the Exchange rate.
tdInvoiceBalanceDebitTC	decimal	CInvoice.CInvoiceBalanceDebitTC	TC Balance Amount DR. This field displays the sum of the debit amounts of all posting lines in transaction currency.
tcInvoiceContact	character	CInvoice.CInvoiceContact	Contact. This field indicates the name of the purchasing contact in the supplier company.
	api annotation:		This field defaults from the Contact field of the invoice's supplier.
ttInvoiceDate	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.
tcInvoiceDescription	character	CInvoice.CInvoiceDescription	Description. A brief description of the invoice. The system generates a default description based on the Reference and Supplier Code.
tdInvoiceOriginalCreditCC	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.
tdInvoiceOriginalCreditLC	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.
tdInvoiceOriginalCreditTC	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.
tdInvoiceOriginalDebitCC	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.
tdInvoiceOriginalDebitLC	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.
tdInvoiceOriginalDebitTC	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.
tcInvoiceReference	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.
tcInvoiceType	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.
tiInvoiceVoucher	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.
tcJournalCode	character	Journal.JournalCode	A daybook code (maximum eight characters).
tcPayFormatTypeCode	character	PaySelLine.PayFormatTypeCode	Payment format of the payment selection
tcPaySelCode	character	PaySel.PaySelCode	Code
ttPaySelDate	date	PaySel.PaySelDate	Execution Date
tiPaySelLine_ID	integer	PaySelLine.PaySelLine_ID	Record ID
tdPaySelLineAmountTC	decimal	PaySelLine.PaySelLineAmountTC	Amount
tdPaySelLineDiscountTC	decimal	PaySelLine.PaySelLineDiscountTC	Discount
tlPaySelLineIsRefInFile	logical	PaySelLine.PaySelLineIsRefInFile	Ref in File
tcPaySelLineObjectType	character	PaySelLine.PaySelLineObjectType	Type
tdPaySelLineWHTAmtCC	decimal	PaySelLine.PaySelLineWHTAmtCC	SC WHT Amount
tdPaySelLineWHTAmtLC	decimal	PaySelLine.PaySelLineWHTAmtLC	BC WHT Amount
tdPaySelLineWHTAmtTC	decimal	PaySelLine.PaySelLineWHTAmtTC	TC WHT Amount
tcPaySelStatus	character	PaySel.PaySelStatus	Status
tiPeriodPeriod	integer	Period.PeriodPeriod	GL Period
tiPeriodYear	integer	Period.PeriodYear	GL Calendar Year
tiState_ID	integer	State.State_ID	Record ID
tcStateCode	character	State.StateCode	State
tcStateDescription	character	State.StateDescription	Description
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(CCollection),rowid(CDocument),rowid(CDocumentInvoiceXref),rowid(CInvoice),rowid(Creditor),rowid(BusinessRelation),rowid(Address),rowid(Country),rowid(State),rowid(Currency),rowid(Journal),rowid(Period),rowid(PaySelLine),rowid(BankNumber1),rowid(PaySel),rowid(GL),rowid(BankNumber2)

Internal usage

QadFinancials

method BPaymentSelectionReport.Remittance