business logic query PaySelLineCInvoiceByPaySelId

Description

This query is created to improve the performance on the Payment Selection load (GetPublicTables). To get more information, check BTS 3934.

Parameters

icAddressTypeCode	character	The address type code
icBusinessRelationCode	character	The code of the BusinessRelation
icObjectTypeList	character	A comma-seperated list of the object types which should be filtered.
iiPaySel_Id	integer	Payment Selection ID
iiCompanyId	integer	Company id
iiAddressType_ID	integer

query condition

each PaySelLine where

PaySelLine.PaySel_ID = iiPaySel_Id AND
PaySelLine.PaySelLineParentObject_ID > 0 AND
PaySelLine.PaySelLineObjectType can-do icObjectTypeList

first CInvoice (inner-join) where

CInvoice.Company_Id = iiCompanyId AND
CInvoice.CInvoice_ID = PaySelLine.PaySelLineParentObject_ID AND

each CInvoiceWHT (conditional-join) where

CInvoiceWHT.CInvoice_ID = CInvoice.CInvoice_ID AND

first Creditor (inner-join) where

Creditor.SharedSet_Id = vi_CREDITOR_sharedset(iiCompanyId) AND
Creditor.Creditor_ID = CInvoice.Creditor_ID AND

first BusinessRelation (inner-join) where

BusinessRelation.BusinessRelation_ID = Creditor.BusinessRelation_ID AND
BusinessRelation.BusinessRelationCode matches icBusinessRelationCode

each Address (inner-join) where

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

first AddressType (inner-join) where

AddressType.AddressType_ID = Address.AddressType_ID AND
AddressType.AddressTypeCode = icAddressTypeCode

first Country1 (inner-join) where

Country1.Country_ID = Address.Country_ID AND

first PaymentGroup (conditional-join) where

PaymentGroup.PaymentGroup_ID = Creditor.PaymentGroup_ID AND

first Currency (inner-join) where

Currency.Currency_ID = CInvoice.CInvoiceCurrency_ID AND

first Division (conditional-join) where

Division.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division.Division_ID = CInvoice.Division_ID AND

first Journal (inner-join) where

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

first PaymentCondition (inner-join) where

PaymentCondition.PaymentCondition_ID = CInvoice.NormalPaymentCondition_ID AND

first Period (inner-join) where

Period.Company_Id = iiCompanyId AND
Period.Period_ID = CInvoice.Period_ID AND

first VatNumber (conditional-join) where

VatNumber.VatNumber_ID = CInvoice.OwnVatNumber_ID AND

first Country2 (outer-join) where

Country2.Country_ID = VatNumber.DeclarationCountry_ID AND

query resultset tqPaySelLineCInvoiceByPaySelId

field name	data type	db field	description
tcAddressTypeCode	character	AddressType.AddressTypeCode	Address Type
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.
tiCInvoice_ID	integer	CInvoice.CInvoice_ID	Record ID
tiCInvoice_ID1	integer	CInvoiceWHT.CInvoice_ID	Link to CInvoice
tdCInvoiceBalanceCreditTC	decimal	CInvoice.CInvoiceBalanceCreditTC	TC Balance Amount CR. This field displays the sum of the credit amounts of all posting lines in transaction currency.
tdCInvoiceBalanceDebitTC	decimal	CInvoice.CInvoiceBalanceDebitTC	TC Balance Amount DR. This field displays the sum of the debit amounts of all posting lines in transaction currency.
tiCInvoiceCompany_ID	integer	CInvoice.Company_ID	Link to Company
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.
ttCInvoiceDiscountDueDate	date	CInvoice.CInvoiceDiscountDueDate	Discount Due Date.This field indicates the last date a discount applies, calculated by the system based on the credit terms and the invoice date. User can modify this field without affecting the credit terms.
ttCInvoiceDueDate	date	CInvoice.CInvoiceDueDate	Due Date.This field indicates the date when payment become due. This field can be modified without affecting the credit terms. This field is calculated by the system based on the credit terms and the invoice date.
tdCInvoiceExchangeRate	decimal	CInvoice.CInvoiceExchangeRate	Exchange Rate. The exchange rate used by the invoice. The BC Invoice Amount is calculated based on the exchange rate. If user modify the TC Invoice Amount, the BC invoice amount is automatically adjusted.
tdCInvoiceHoldAmountTC	decimal	CInvoice.CInvoiceHoldAmountTC	TC Hold Amount. This field displays the amount of the invoice total that is not to be paid. If this field is not blank, the hold amount is taken into account during payment processing. Hold amounts must be: For invoices or correction invoices: Less than the invoice total and greater than zero. For credit notes or correction credit notes: Greater than the document total and less than zero. The hold amount for a negative payment must always be set to zero.
tlCInvoiceIsWHT	logical	CInvoice.CInvoiceIsWHT	Withholding Tax
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.
tdCInvoiceRateScale	decimal	CInvoice.CInvoiceRateScale	Scale Factor. This field indicates the scale factor applying to the exchange rate in use for foreign currency payments.
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.
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.
tcCInvoiceType	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.
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.
tiCInvoiceWHT_ID	integer	CInvoiceWHT.CInvoiceWHT_ID	Record ID
tdCInvoiceWHTAmtCreditCC	decimal	CInvoiceWHT.CInvoiceWHTAmtCreditCC	SC WHT Amount (CR)
tdCInvoiceWHTAmtCreditLC	decimal	CInvoiceWHT.CInvoiceWHTAmtCreditLC	BC WHT Amount (CR)
tdCInvoiceWHTAmtCreditTC	decimal	CInvoiceWHT.CInvoiceWHTAmtCreditTC	TC WHT Amount (CR)
tdCInvoiceWHTAmtDebitCC	decimal	CInvoiceWHT.CInvoiceWHTAmtDebitCC	SC WHT Amount (DR)
tdCInvoiceWHTAmtDebitLC	decimal	CInvoiceWHT.CInvoiceWHTAmtDebitLC	BC WHT Amount (DR)
tdCInvoiceWHTAmtDebitTC	decimal	CInvoiceWHT.CInvoiceWHTAmtDebitTC	TC WHT Amount (DR)
tdCInvoiceWHTBaseAmtCreditCC	decimal	CInvoice.CInvoiceWHTBaseAmtCreditCC	SC WHT Base Amount (CR)
tdCInvoiceWHTBaseAmtCreditLC	decimal	CInvoice.CInvoiceWHTBaseAmtCreditLC	BC WHT Base Amount (CR)
tdCInvoiceWHTBaseAmtCreditTC	decimal	CInvoice.CInvoiceWHTBaseAmtCreditTC	TC WHT Base Amount (CR)
tdCInvoiceWHTBaseAmtDebitCC	decimal	CInvoice.CInvoiceWHTBaseAmtDebitCC	SC WHT Base Amount (DR)
tdCInvoiceWHTBaseAmtDebitLC	decimal	CInvoice.CInvoiceWHTBaseAmtDebitLC	BC WHT Base Amount (DR)
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)
tdCInvoiceWHTTaxFeeCreditCC	decimal	CInvoiceWHT.CInvoiceWHTTaxFeeCreditCC	SC Taxable Fee (CR)
tdCInvoiceWHTTaxFeeCreditLC	decimal	CInvoiceWHT.CInvoiceWHTTaxFeeCreditLC	BC Taxable Fee (CR)
tdCInvoiceWHTTaxFeeCreditTC	decimal	CInvoiceWHT.CInvoiceWHTTaxFeeCreditTC	TC Taxable Fee (CR)
tdCInvoiceWHTTaxFeeDebitCC	decimal	CInvoiceWHT.CInvoiceWHTTaxFeeDebitCC	SC Taxable Fee (DR)
tdCInvoiceWHTTaxFeeDebitLC	decimal	CInvoiceWHT.CInvoiceWHTTaxFeeDebitLC	BC Taxable Fee (DR)
tdCInvoiceWHTTaxFeeDebitTC	decimal	CInvoiceWHT.CInvoiceWHTTaxFeeDebitTC	TC Taxable Fee (DR)
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)
tiCreditor_ID	integer	CInvoice.Creditor_ID	Link to Creditor
tcCreditorCode	character	Creditor.CreditorCode	Supplier Code
tlCreditorIsWHT	logical	Creditor.CreditorIsWHT	Withholding Tax
tcCurrencyCode	character	Currency.CurrencyCode	Currency Code
tcDeclarationCountryCode	character	Country2.CountryCode	Country Code
tiDivision_ID	integer	CInvoiceWHT.Division_ID	Link to Division
tcDivisionCode	character	Division.DivisionCode	Sub-Account
tiGL_ID	integer	CInvoiceWHT.GL_ID	Link to GL
tcJournalCode	character	Journal.JournalCode	A daybook code (maximum eight characters).
tdPaymentConditionPercentage	decimal	PaymentCondition.PaymentConditionPercentage	Discount Percentage
tiPaySel_ID	integer	PaySelLine.PaySel_ID	Link to PaySel
tiPaySelLine_ID	integer	PaySelLine.PaySelLine_ID	Record ID
tcPaySelLineObjectType	character	PaySelLine.PaySelLineObjectType	Type
tiPaySelLineParentObject_ID	integer	PaySelLine.PaySelLineParentObject_ID	Parent Object
tiPeriodYear	integer	Period.PeriodYear	GL Calendar Year
tiVat_ID	integer	CInvoiceWHT.Vat_ID	Link to Vat

Internal usage

QadFinancials

method BPaymentSelection.CalculatePaySelLine