business logic query BaseCreditorAAByProject

Parameters

iiCompanyId	integer	Company id
iiAccPeriod	integer
icCandoBR	character
icFromBR	character
icToBR	character
icCandoCreditor	character
icFromCreditor	character
icToCreditor	character
icCandoDivision	character
icFromDivision	character
icToDivision	character
icCandoProject	character
icFromProject	character
icToProject	character
icCandoJournal	character
icFromJournal	character
icToJournal	character
icCurrencyCode	character
icCandoProjectManager	character
icFromProjectManager	character
icToProjectManager	character

query condition

each Creditor where

Creditor.SharedSet_Id = vi_CREDITOR_sharedset(iiCompanyId) AND
Creditor.CreditorCode can-do icCandoCreditor AND
Creditor.CreditorCode >= icFromCreditor AND
Creditor.CreditorCode <= icToCreditor

first BusinessRelation (inner-join) where

BusinessRelation.BusinessRelation_ID = Creditor.BusinessRelation_ID AND
BusinessRelation.BusinessRelationCode can-do icCandoBR AND
BusinessRelation.BusinessRelationCode >= icFromBR AND
BusinessRelation.BusinessRelationCode <= icToBR

each Address (conditional-join) where

Address.BusinessRelation_ID = BusinessRelation.BusinessRelation_ID AND

each Contact (conditional-join) where

Contact.Address_ID = Address.Address_ID AND

each CInvoice (conditional-join) where

CInvoice.Company_Id = iiCompanyId AND
CInvoice.Creditor_ID = Creditor.Creditor_ID AND
CInvoice.CInvoiceIsOpen = true

first Currency (conditional-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 can-do icCandoDivision AND
Division.DivisionCode >= icFromDivision AND
Division.DivisionCode <= icToDivision

first Journal (conditional-join) where

Journal.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal.Journal_ID = CInvoice.Journal_ID AND
Journal.JournalCode can-do icCandoJournal AND
Journal.JournalCode >= icFromJournal AND
Journal.JournalCode <= icToJournal

first Period (conditional-join) where

Period.Company_Id = iiCompanyId AND
Period.Period_ID = CInvoice.Period_ID AND
Period.PeriodYearPeriod <= iiAccPeriod

first Project (conditional-join) where

Project.SharedSet_Id = vi_PROJECT_sharedset(iiCompanyId) AND
Project.Project_ID = CInvoice.Project_ID AND
Project.ProjectCode can-do icCandoProject AND
Project.ProjectCode >= icFromProject AND
Project.ProjectCode <= icToProject

first Usr (conditional-join) where

Usr.Usr_ID = Project.ProjectManagerUsr_ID AND
Usr.UsrName can-do icCandoProjectManager AND
Usr.UsrName >= icFromProjectManager AND
Usr.UsrName <= icToProjectManager

first Profile1 (inner-join) where

Profile1.Profile_ID = Creditor.InvControlGLProfile_ID AND

first Profile2 (inner-join) where

Profile2.Profile_ID = Creditor.CnControlGLProfile_ID AND

query resultset tqBaseCreditorAAByProject

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.
tcAddressCounty	character	Address.AddressCounty	Obsolete field
tcAddressEMail	character	Address.AddressEMail	E-Mail. This field indicates the e-mail address associated with this business relation. This field can be blank.
tcAddressFax	character	Address.AddressFax	Fax. This field indicates the fax or telex number to use when sending documents to this address. This field can be balnk.
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.
tcAddressTelephone	character	Address.AddressTelephone	Telephone. The telephone number for calling this business relation address. This field can be blank.
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
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.
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.
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.
tcContactMobilePhone	character	Contact.ContactMobilePhone	Mobile. The mobile phone number of the contact. This field can be blank.
tcContactName	character	Contact.ContactName	Name. The full name of the contact. This field is mandatory.
tcContactTelephone	character	Contact.ContactTelephone	Telephone. The telephone number of the contact. This field can be blank.
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).
tiProfile_ID_CN	integer	Profile2.Profile_ID	Record ID
tiPROFILE_ID_INV	integer	Profile1.Profile_ID	Record ID
tcProfileCode	character	Profile1.ProfileCode	A code (maximum 20 characters) that identifies a profile.
tcProfileCode2	character	Profile2.ProfileCode	A code (maximum 20 characters) that identifies a profile.
tiProject_ID	integer	Project.Project_ID	Record ID
tcProjectCode	character	Project.ProjectCode	Project
tcProjectDescription	character	Project.ProjectDescription	Description
tcUsrName	character	Usr.UsrName	User Name
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(Creditor),rowid(BusinessRelation),rowid(Address),rowid(Contact),rowid(CInvoice),rowid(Currency),rowid(Division),rowid(Journal),rowid(Period),rowid(Project),rowid(Usr),rowid(Profile1),rowid(Profile2)

Internal usage

QadFinancials

method BProjectReport.CreditorAgeingAnalysisByProj