business logic query BaseCreditorAgeingAnalBack

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
itAgeingDate	date
icFromGLCode	character
icToGLCode	character
icCanDoGLCode	character
icFromProjectCode	character
icToProjectCode	character
icCanDoProjectCode	character
icFromCCCode	character
icToCCCode	character
icCanDoCCCode	character
icFromPaymentGroup	character
icToPaymentGroup	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.CInvoiceClosingDate > itAgeingDate AND
CInvoice.CInvoiceIsInitialStatus <> true 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

each CInvoiceMovement (conditional-join) where

CInvoiceMovement.Company_Id = iiCompanyId AND
CInvoiceMovement.CInvoice_ID = CInvoice.CInvoice_ID AND

first PostingLine (conditional-join) where

PostingLine.Company_Id = iiCompanyId AND
PostingLine.PostingLine_ID = CInvoiceMovement.PostingLine_ID AND

first Posting (conditional-join) where

Posting.Company_Id = iiCompanyId AND
Posting.Posting_ID = PostingLine.Posting_ID AND
Posting.PostingDate <= itAgeingDate

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 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 tqBaseCreditorAgeingAnalBack

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
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
tlCInvoiceIsSelected	logical	CInvoice.CInvoiceIsSelected	Selected. This field indicates whether the invoice is included in a payment selection. This field is read-only.
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.
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.
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.
tcCostCentreCode	character	CostCentre.CostCentreCode	Cost Center: A code that identifies the cost center.
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
tiDivision_ID	integer	Division.Division_ID	Record ID
tcDivisionCode	character	Division.DivisionCode	Sub-Account
tcDivisionDescription	character	Division.DivisionDescription	Description
tiGL_ID	integer	GL.GL_ID	Record ID
tcGLCode	character	GL.GLCode	A code identifying the GL account.
tcGLDescription	character	GL.GLDescription	A description of the GL account.
tcJournalCode	character	Journal.JournalCode	A daybook code (maximum eight characters).
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.
tdPostingLineCreditCC	decimal	PostingLine.PostingLineCreditCC	SC Credit
	api annotation:		Partial Update = yes - it will get a default value depending on the value of PostingLinCreditTC, PostingLineCCRate & PostingLineCCScale. SC Amount = TC Amount * Exchange rate (TC/SC) * Scale Factor (TC/SC). If Partial Update is false, you should enter the correct value for this field
tdPostingLineCreditLC	decimal	PostingLine.PostingLineCreditLC	BC Credit
	api annotation:		Partial Update = yes - it will get a default value depends on the value of PostingLineCreditTC, PostingLineExchangeRate & PostingLineRateScale. BC Amount = TC Amount * Exchange rate (TC/BC) * Scale Factor (TC/BC). If Partial Update is false, enter a value for this field.
tdPostingLineCreditTC	decimal	PostingLine.PostingLineCreditTC	TC Credit
tdPostingLineDebitCC	decimal	PostingLine.PostingLineDebitCC	SC Debit
	api annotation:		Partial Update = yes - it will get a default value depending on the value of PostingLineDebitTC, PostingLineCCRate & PostingLineCCScale. SC Amount = TC Amount * Exchange rate (TC/SC) * Scale Factor (TC/SC). If Partial Update is false, enter the correct value for this field.
tdPostingLineDebitLC	decimal	PostingLine.PostingLineDebitLC	BC Debit
	api annotation:		Partial Update = yes - it will get a default value depending on the value of PostingLineDebitTC, PostingLineExchangeRate and PostingLineRateScale. BC Amount = TC Amount * Exchange rate (TC/BC) * Scale Factor (TC/BC). If Partial Update = false, enter the correct value into this field.
tdPostingLineDebitTC	decimal	PostingLine.PostingLineDebitTC	TC Debit
tcProjectCode	character	Project.ProjectCode	Project

Internal usage

QadFinancials

method BCreditorReport.CreditorAgeingABackwards