business logic query BaseCreditorAgeingAnalysisCurr

Parameters

iiCompanyId	integer	Company id
icCandoCreditorCode	character
icCandoBRCode	character
icCandoDivisionCode	character
icCandoJournalCode	character
icCurrencyCode	character
icFromBRCode	character
icFromCreditorCode	character
icFromDivisionCode	character
icFromJournalCode	character
iiToAccPeriod	integer
icToBRCode	character
icToCreditorCode	character
icToDivisionCode	character
icToJournalCode	character
icFromGLCode	character
icToGLCode	character
icCanDoGLCode	character
icFromProjectCode	character
icToProjectCode	character
icCanDoProjectCode	character
icFromCCCode	character
icToCCCode	character
icCanDoCCCode	character
icToPaymentGroup	character
icFromPaymentGroup	character
icCandoPaymentGroup	character

query condition

each Creditor where

Creditor.SharedSet_Id = vi_CREDITOR_sharedset(iiCompanyId) AND
Creditor.CreditorCode >= icFromCreditorCode AND
Creditor.CreditorCode <= icToCreditorCode AND
Creditor.CreditorCode can-do icCandoCreditorCode

first BusinessRelation (inner-join) where

BusinessRelation.BusinessRelation_ID = Creditor.BusinessRelation_ID AND
BusinessRelation.BusinessRelationCode >= icFromBRCode AND
BusinessRelation.BusinessRelationCode <= icToBRCode AND
BusinessRelation.BusinessRelationCode can-do icCandoBRCode

each CInvoice (inner-join) where

CInvoice.Company_Id = iiCompanyId AND
CInvoice.Creditor_ID = Creditor.Creditor_ID AND
CInvoice.CInvoiceIsOpen = true AND
CInvoice.CInvoiceIsInitialStatus = false AND
CInvoice.CInvoicePostingYearPeriod <= iiToAccPeriod

first CostCentre (conditional-join) where

CostCentre.SharedSet_Id = vi_COSTCENTRE_sharedset(iiCompanyId) AND
CostCentre.CostCentre_ID = CInvoice.CostCentre_ID AND
CostCentre.CostCentreCode >= icFromCCCode AND
CostCentre.CostCentreCode <= icToCCCode AND
CostCentre.CostCentreCode can-do icCanDoCCCode

first Currency (inner-join) where

Currency.Currency_ID = CInvoice.CInvoiceCurrency_ID AND
Currency.CurrencyCode = icCurrencyCode

first Division (conditional-join) where

Division.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division.Division_ID = CInvoice.Division_ID AND
Division.DivisionCode >= icFromDivisionCode AND
Division.DivisionCode <= icToDivisionCode AND
Division.DivisionCode can-do icCandoDivisionCode

first GL (inner-join) where

GL.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL.GL_ID = CInvoice.ControlGL_ID AND
GL.GLCode >= icFromGLCode AND
GL.GLCode <= icToGLCode AND
GL.GLCode can-do icCanDoGLCode

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 AND
Journal.JournalCode can-do icCandoJournalCode

first PaymentCondition (inner-join) where

PaymentCondition.PaymentCondition_ID = CInvoice.NormalPaymentCondition_ID AND

first Project (conditional-join) where

Project.SharedSet_Id = vi_PROJECT_sharedset(iiCompanyId) AND
Project.Project_ID = CInvoice.Project_ID AND
Project.ProjectCode >= icFromProjectCode AND
Project.ProjectCode <= icToProjectCode AND
Project.ProjectCode can-do icCanDoProjectCode

first PaymentGroup (conditional-join) where

PaymentGroup.PaymentGroup_ID = Creditor.PaymentGroup_ID AND
PaymentGroup.PaymentGroupCode >= icFromPaymentGroup AND
PaymentGroup.PaymentGroupCode <= icToPaymentGroup AND
PaymentGroup.PaymentGroupCode can-do icCandoPaymentGroup

query resultset tqBaseCreditorAgeingAnalysisCurr

field name	data type	db field	description
tiBusinessRelation_ID	integer	Creditor.BusinessRelation_ID	Name
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.
tcBusinessRelationName2	character	BusinessRelation.BusinessRelationName2	Second Name. An extended name when the Name field is not large enough to contain all information. This fielsd can be blank.
tcBusinessRelationSearchName	character	BusinessRelation.BusinessRelationSearchName	Search Name: An alternate name for finding the business relation. This field can be useful for sorting and filtering.
	api annotation:		PartialUpdate = yes - defaults to the BusinessRelationCode.
tiCInvoice_ID	integer	CInvoice.CInvoice_ID	Record ID
tdCInvoiceBalanceCreditCC	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.
tdCInvoiceBalanceCreditLC	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.
tdCInvoiceBalanceCreditTC	decimal	CInvoice.CInvoiceBalanceCreditTC	TC Balance Amount CR. This field displays the sum of the credit amounts of all posting lines in transaction currency.
tdCInvoiceBalanceDebitCC	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.
tdCInvoiceBalanceDebitLC	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.
tdCInvoiceBalanceDebitTC	decimal	CInvoice.CInvoiceBalanceDebitTC	TC Balance Amount DR. This field displays the sum of the debit amounts of all posting lines in transaction currency.
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.
tcCInvoiceDescription	character	CInvoice.CInvoiceDescription	Description. A brief description of the invoice. The system generates a default description based on the Reference and Supplier Code.
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.
tlCInvoiceIsLockPayment	logical	CInvoice.CInvoiceIsLockPayment	Lock Payment. This field indicates if the invoice is locked for payment. The invoice cannot be included in any automatic payment selections if this field is true.
	api annotation:		PartialUpdate = yes - defaults to false
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.
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.
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.
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.
tiControlGL_ID	integer	GL.GL_ID	Record ID
tcControlGLCode	character	GL.GLCode	A code identifying the GL account.
tcControlGLDescription	character	GL.GLDescription	A description of the GL account.
tiCreditor_ID	integer	Creditor.Creditor_ID	Record ID
tcCreditorCode	character	Creditor.CreditorCode	Supplier Code
tiCurrency_ID	integer	Currency.Currency_ID	Record ID
tcCurrencyCode	character	Currency.CurrencyCode	Currency Code
tcCurrencyDescription	character	Currency.CurrencyDescription	Currency Description
tiCurrencyNumberOfDecimals	integer	Currency.CurrencyNumberOfDecimals	Number of Decimals
tiDivision_ID	integer	Division.Division_ID	Record ID
tcDivisionCode	character	Division.DivisionCode	Sub-Account
tcDivisionDescription	character	Division.DivisionDescription	Description
tcJournalCode	character	Journal.JournalCode	A daybook code (maximum eight characters).
tiPaymentConditionDayMthDisc	integer	PaymentCondition.PaymentConditionDayMthDisc	No of Periods
tcPaymentConditionPdDiscType	character	PaymentCondition.PaymentConditionPdDiscType	Period Type
tdPaymentConditionPercentage	decimal	PaymentCondition.PaymentConditionPercentage	Discount Percentage
tiPaymentGroup_ID	integer	PaymentGroup.PaymentGroup_ID	Record ID
tcPaymentGroupCode	character	PaymentGroup.PaymentGroupCode	Payment Group
tcPaymentGroupDescription	character	PaymentGroup.PaymentGroupDescription	Description
tiPeriodPeriod	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.
tiPeriodYear	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.

Internal usage

QadFinancials

method BCreditorReport.CreditorAgeingAnalysisCurrent