report query DebtorBalanceSummary

Description

This query can be sued to get all the balance using dHist. You should specify the period you are interested in. System will return balance at end of period.

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i
iiAccYearPeriod	i	>=
icCandoBRCode	c	can-do
icCandoDebtorCode	c	can-do
icCanDoGLCode	c	can-do
icFromBRCode	c	>=
icFromDebtorCode	c	>=
icFromGlCode	c	>=
icToBRCode	c	<=
icToDebtorCode	c	<=
icToGLCode	c	<=
tBusinessRelation.BusinessRelationCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tBusinessRelation.BusinessRelationName1	c	=,>=,>,<>,<=,<,begins,can-do,matches
tBusinessRelation.BusinessRelationName2	c	=,>=,>,<>,<=,<,begins,can-do,matches
tBusinessRelation.BusinessRelationSearchName	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCostCentre.CostCentreCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCostCentre.CostCentreDescription	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency.CurrencyCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency.CurrencyDescription	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency.CurrencyNumberOfDecimals	i	=,>=,>,<>,<=,<
tDebtor.DebtorCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDHist.DHistBalanceCC	d	=,>=,>,<>,<=,<
tDHist.DHistBalanceLC	d	=,>=,>,<>,<=,<
tDHist.DHistBalanceTC	d	=,>=,>,<>,<=,<
tDHist.DHistGrossCredNoteCC	d	=,>=,>,<>,<=,<
tDHist.DHistGrossCredNoteLC	d	=,>=,>,<>,<=,<
tDHist.DHistGrossCredNoteTC	d	=,>=,>,<>,<=,<
tDHist.DHistGrossInvoiceCC	d	=,>=,>,<>,<=,<
tDHist.DHistGrossInvoiceLC	d	=,>=,>,<>,<=,<
tDHist.DHistGrossInvoiceTC	d	=,>=,>,<>,<=,<
tDHist.DHistMovementCreditCC	d	=,>=,>,<>,<=,<
tDHist.DHistMovementCreditLC	d	=,>=,>,<>,<=,<
tDHist.DHistMovementCreditTC	d	=,>=,>,<>,<=,<
tDHist.DHistMovementDebitCC	d	=,>=,>,<>,<=,<
tDHist.DHistMovementDebitLC	d	=,>=,>,<>,<=,<
tDHist.DHistMovementDebitTC	d	=,>=,>,<>,<=,<
tDHist.DHistNettoCredNoteCC	d	=,>=,>,<>,<=,<
tDHist.DHistNettoCredNoteLC	d	=,>=,>,<>,<=,<
tDHist.DHistNettoCredNoteTC	d	=,>=,>,<>,<=,<
tDHist.DHistNettoInvoiceCC	d	=,>=,>,<>,<=,<
tDHist.DHistNettoInvoiceLC	d	=,>=,>,<>,<=,<
tDHist.DHistNettoInvoiceTC	d	=,>=,>,<>,<=,<
tDHist.DHistYearPeriodFrom	i	=,>=,>,<>,<=,<
tDHist.DHistYearPeriodTill	i	=,>=,>,<>,<=,<
tDivision.DivisionCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDivision.DivisionDescription	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL.GLCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL.GLDescription	c	=,>=,>,<>,<=,<,begins,can-do,matches
tJournal.JournalCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tJournal.JournalDescription	c	=,>=,>,<>,<=,<,begins,can-do,matches
tProject.ProjectCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tProject.ProjectDescription	c	=,>=,>,<>,<=,<,begins,can-do,matches

Parameters (internal)

iiCompanyId	integer	Company id
iiAccYearPeriod	integer
icCandoBRCode	character
icCandoDebtorCode	character
icCanDoGLCode	character
icFromBRCode	character
icFromDebtorCode	character
icFromGlCode	character
icToBRCode	character
icToDebtorCode	character
icToGLCode	character

query condition

each Debtor where

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

first BusinessRelation (inner-join) where

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

each DHist (inner-join) where

DHist.Company_Id = iiCompanyId AND
DHist.Debtor_ID = Debtor.Debtor_ID AND
DHist.DHistYearPeriodTill >= iiAccYearPeriod AND
DHist.DHistYearPeriodFrom <= iiAccYearPeriod

first CostCentre (conditional-join) where

CostCentre.SharedSet_Id = vi_COSTCENTRE_sharedset(iiCompanyId) AND
CostCentre.CostCentre_ID = DHist.CostCentre_ID AND

first Currency (inner-join) where

Currency.Currency_ID = DHist.Currency_ID AND

first Division (conditional-join) where

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

first GL (inner-join) where

GL.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL.GL_ID = DHist.GL_ID AND
GL.GLCode can-do icCanDoGLCode AND
GL.GLCode >= icFromGlCode AND
GL.GLCode <= icToGLCode

first Journal (inner-join) where

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

first Project (conditional-join) where

Project.SharedSet_Id = vi_PROJECT_sharedset(iiCompanyId) AND
Project.Project_ID = DHist.Project_ID AND

query resultset tqDebtorBalanceSummary

field name	data type	db field	description
tiCostCentre_ID	integer	CostCentre.CostCentre_ID	Record ID
tiDivision_ID	integer	Division.Division_ID	Record ID
tiGL_ID	integer	GL.GL_ID	Record ID
tiJournal_ID	integer	Journal.Journal_ID	Record ID
tiProject_ID	integer	Project.Project_ID	Record ID
tcCostCentreCode	character	CostCentre.CostCentreCode	Cost Center: A code that identifies the cost center.
tcCostCentreDescription	character	CostCentre.CostCentreDescription	Description: a brief description of the cost center.
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
tcDivisionCode	character	Division.DivisionCode	Sub-Account
tcDivisionDescription	character	Division.DivisionDescription	Description
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).
tcJournalDescription	character	Journal.JournalDescription	A brief description of the daybook.
	api annotation:		Value for this field defaults to null.
tcProjectCode	character	Project.ProjectCode	Project
tcProjectDescription	character	Project.ProjectDescription	Description
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.
tiBusinessRelation_ID	integer	BusinessRelation.BusinessRelation_ID	Record ID
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.
tdDHistBalanceCC	decimal	DHist.DHistBalanceCC	SC Balance
tdDHistBalanceLC	decimal	DHist.DHistBalanceLC	BC Balance
tdDHistBalanceTC	decimal	DHist.DHistBalanceTC	TC Balance
tdDHistGrossCredNoteCC	decimal	DHist.DHistGrossCredNoteCC	SC Gross Credit Note
tdDHistGrossCredNoteLC	decimal	DHist.DHistGrossCredNoteLC	BC Gross Credit Note
tdDHistGrossCredNoteTC	decimal	DHist.DHistGrossCredNoteTC	TC Gross Credit Note
tdDHistGrossInvoiceCC	decimal	DHist.DHistGrossInvoiceCC	SC Gross Invoice
tdDHistGrossInvoiceLC	decimal	DHist.DHistGrossInvoiceLC	BC Gross Invoice
tdDHistGrossInvoiceTC	decimal	DHist.DHistGrossInvoiceTC	TC Gross Invoice
tdDHistMovementCreditCC	decimal	DHist.DHistMovementCreditCC	SC Activity Credit
tdDHistMovementCreditLC	decimal	DHist.DHistMovementCreditLC	BC Activity Credit
tdDHistMovementCreditTC	decimal	DHist.DHistMovementCreditTC	TC Activity Credit
tdDHistMovementDebitCC	decimal	DHist.DHistMovementDebitCC	SC Activity Debit
tdDHistMovementDebitLC	decimal	DHist.DHistMovementDebitLC	BC Activity Debit
tdDHistMovementDebitTC	decimal	DHist.DHistMovementDebitTC	TC Activity Debit
tdDHistNettoCredNoteCC	decimal	DHist.DHistNettoCredNoteCC	SC Net Credit Note
tdDHistNettoCredNoteLC	decimal	DHist.DHistNettoCredNoteLC	BC Net to Credit Note
tdDHistNettoCredNoteTC	decimal	DHist.DHistNettoCredNoteTC	TC Net Credit Note
tdDHistNettoInvoiceCC	decimal	DHist.DHistNettoInvoiceCC	SC Net Invoice
tdDHistNettoInvoiceLC	decimal	DHist.DHistNettoInvoiceLC	BC Net to Invoice
tdDHistNettoInvoiceTC	decimal	DHist.DHistNettoInvoiceTC	TC Net Invoice
tiDHistYearPeriodFrom	integer	DHist.DHistYearPeriodFrom	From Year GL Period
tiDHistYearPeriodTill	integer	DHist.DHistYearPeriodTill	To Year GL Period
DebtorCustom	character	calculated	custom fields
BusinessRelationCustom	character	calculated	custom fields
DHistCustom	character	calculated	custom fields
CostCentreCustom	character	calculated	custom fields
CurrencyCustom	character	calculated	custom fields
DivisionCustom	character	calculated	custom fields
GLCustom	character	calculated	custom fields
JournalCustom	character	calculated	custom fields
ProjectCustom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(Debtor),rowid(BusinessRelation),rowid(DHist),rowid(CostCentre),rowid(Currency),rowid(Division),rowid(GL),rowid(Journal),rowid(Project)

Internal usage

QadFinancials

method BDebtorReport.DebtorAccountsSummary