business logic query BaseDebtorAAByProject

Parameters

iiCompanyId	integer	Company id
icCanDoDebtorCode	character
icFromDebtorCode	character
icToDebtorCode	character
icFromBRCode	character
icToBRCode	character
icCanDoBRCode	character
iiAccToPeriodYear	integer
icFromDivisionCode	character
icToDivisionCode	character
icCanDoDivisionCode	character
icFromProjectCode	character
icToProjectCode	character
icCanDoProjectCode	character
icCanDoProjectManager	character
icFromProjectManager	character
icToProjectManager	character
icCanDoJournalCode	character
icToJournalCode	character
icFromJournalCode	character
icCurrencyCode	character

query condition

each Debtor where

Debtor.SharedSet_Id = vi_DEBTOR_sharedset(iiCompanyId) AND
Debtor.DebtorCode can-do icCanDoDebtorCode AND
Debtor.DebtorCode >= icFromDebtorCode AND
Debtor.DebtorCode <= icToDebtorCode

first BusinessRelation (inner-join) where

BusinessRelation.BusinessRelation_ID = Debtor.BusinessRelation_ID AND
BusinessRelation.BusinessRelationCode can-do icCanDoBRCode AND
BusinessRelation.BusinessRelationCode >= icFromBRCode AND
BusinessRelation.BusinessRelationCode <= icToBRCode

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 DInvoice (conditional-join) where

DInvoice.Company_Id = iiCompanyId AND
DInvoice.Debtor_ID = Debtor.Debtor_ID AND

first Currency (conditional-join) where

Currency.Currency_ID = DInvoice.DInvoiceCurrency_ID AND
Currency.CurrencyCode = icCurrencyCode

first Division (conditional-join) where

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

first Journal (conditional-join) where

Journal.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal.Journal_ID = DInvoice.Journal_ID AND
Journal.JournalCode can-do icCanDoJournalCode AND
Journal.JournalCode >= icFromJournalCode AND
Journal.JournalCode <= icToJournalCode

first Period (conditional-join) where

Period.Company_Id = iiCompanyId AND
Period.Period_ID = DInvoice.Period_ID AND
Period.PeriodYearPeriod <= iiAccToPeriodYear

first Project (conditional-join) where

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

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 = Debtor.InvControlGLProfile_ID AND

first Profile2 (inner-join) where

Profile2.Profile_ID = Debtor.CnControlGLProfile_ID AND

query resultset tqBaseDebtorAAByProject

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.
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.
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.
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.
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.
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
tiDebtor_ID	integer	Debtor.Debtor_ID	Record ID
tcDebtorCode	character	Debtor.DebtorCode	A code (maximum eight characters) that identifies a customer. If the code you specify matches an existing supplier code, a warning message displays. You can choose to ignore the warning, and create the record. However, when a supplier and customer share the same code, they must reference the same business relation.
	api annotation:		If you leave the Customer Code field blank, the system automatically generates a number for the record based on the sequence defined in Customer Autonumber Create.
tiDInvoice_ID	integer	DInvoice.DInvoice_ID	Record ID
tdDInvoiceBalanceCreditCC	decimal	DInvoice.DInvoiceBalanceCreditCC	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.
tdDInvoiceBalanceCreditLC	decimal	DInvoice.DInvoiceBalanceCreditLC	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.
tdDInvoiceBalanceCreditTC	decimal	DInvoice.DInvoiceBalanceCreditTC	TC Balance Amount CR. This field displays the sum of the credit amounts of all posting lines in transaction currency.
tdDInvoiceBalanceDebitCC	decimal	DInvoice.DInvoiceBalanceDebitCC	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.
tdDInvoiceBalanceDebitLC	decimal	DInvoice.DInvoiceBalanceDebitLC	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.
tdDInvoiceBalanceDebitTC	decimal	DInvoice.DInvoiceBalanceDebitTC	TC Balance Amount DR. This field displays the sum of the debit amounts of all posting lines in transaction currency.
ttDInvoiceDate	date	DInvoice.DInvoiceDate	Invoice Date. This field indicates the invoice creation date.This field is normallly prior to the posting date and within the same accounting period as the posting date. The date must be within the upper and lower date limits of an open GL period. The system uses the invoice date with the credit terms to calculate due date and discount date.
	api annotation:		This field defaults to the DInvoicePostingDate.
tcDInvoiceDescription	character	DInvoice.DInvoiceDescription	Description. A brief description of the invoice. This field is mandatory.
ttDInvoiceDiscountDueDate	date	DInvoice.DInvoiceDiscountDueDate	Discount Due Date. This field displays the last date a discount applies, calculated by the system based on the credit terms and the invoice date. You can modify the due date without affecting the credit terms. If the credit terms have a base date specified, this is used in the due date calculations rather than the invoice creation date.
ttDInvoiceDueDate	date	DInvoice.DInvoiceDueDate	Due Date. This field displays the date when payment is due, calculated by the system based on the credit terms and the invoice date. User can modify the due date without affecting the credit terms. If the credit terms have a base date specified, this is used in the due date calculations rather than the invoice creation date.
tdDInvoiceOriginalCreditCC	decimal	DInvoice.DInvoiceOriginalCreditCC	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.
tdDInvoiceOriginalCreditLC	decimal	DInvoice.DInvoiceOriginalCreditLC	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.
tdDInvoiceOriginalCreditTC	decimal	DInvoice.DInvoiceOriginalCreditTC	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.
tdDInvoiceOriginalDebitCC	decimal	DInvoice.DInvoiceOriginalDebitCC	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.
tdDInvoiceOriginalDebitLC	decimal	DInvoice.DInvoiceOriginalDebitLC	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.
tdDInvoiceOriginalDebitTC	decimal	DInvoice.DInvoiceOriginalDebitTC	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.
ttDInvoicePostingDate	date	DInvoice.DInvoicePostingDate	Posting Date. This field displays the date the sales-related invoice was generated by Invoice Post and Print. This field is updated when the invoice is validated and saved.
	api annotation:		This field defaults from the invoice creation date.
tiDInvoiceReminderCount	integer	DInvoice.DInvoiceReminderCount	Reminder Counter. This field displays the reminder level that applies to this invoice.
tcDInvoiceType	character	DInvoice.DInvoiceType	Invoice Type. This field displays the invoice type. Invoice Correction and Credit Note Correction display as choices only when the appropriate daybook types have already been defined.
	api annotation:		PartialUpdate = yes - defaults to Invoice
tiDInvoiceVoucher	integer	DInvoice.DInvoiceVoucher	Voucher.This field displays the numeric identifier assigned to the posting. When the daybook of the journal entry is charged(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.
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).
tiPeriodPeriod	integer	Period.PeriodPeriod	GL Period
tiPeriodYear	integer	Period.PeriodYear	GL Calendar Year
tiProfile_ID_CN	integer	Profile2.Profile_ID	Record ID
tiProfile_ID_INV	integer	Profile1.Profile_ID	Record ID
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(Debtor),rowid(BusinessRelation),rowid(Address),rowid(Contact),rowid(DInvoice),rowid(Currency),rowid(Division),rowid(Journal),rowid(Period),rowid(Project),rowid(Usr),rowid(Profile1),rowid(Profile2)

Internal usage

QadFinancials

method BProjectReport.DebtorAgeingAnalysisByProj