business logic query CreditorBalanceSummary

Description

This query can be sued to get all the balances using cHist. You should specify the from/till you are interested in.

Parameters

iiCompanyId	integer	Company id
icCandoCreditorCode	character
icCandoBRCode	character
icFromBRCode	character
icFromCreditorCode	character
iiAccYearPeriod	integer
icToBRCode	character
icToCreditorCode	character
icFromGLCode	character
icToGLCode	character
icCandoGLCode	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 CHist (inner-join) where

CHist.Company_Id = iiCompanyId AND
CHist.Creditor_ID = Creditor.Creditor_ID AND
CHist.CHistYearPeriodFrom <= iiAccYearPeriod AND
CHist.CHistYearPeriodTill >= iiAccYearPeriod

first CostCentre (conditional-join) where

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

first Currency (inner-join) where

Currency.Currency_ID = CHist.Currency_ID AND

first Division (conditional-join) where

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

first GL (inner-join) where

GL.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL.GL_ID = CHist.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 = CHist.Journal_ID AND

first Project (conditional-join) where

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

query resultset tqCreditorBalanceSummary

field name	data type	db field	description
tiBusinessRelation_ID	integer	Creditor.BusinessRelation_ID	Name
tiBusinessRelation_ID2	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.
tdCHistBalanceCC	decimal	CHist.CHistBalanceCC	SC Balance
tdCHistBalanceLC	decimal	CHist.CHistBalanceLC	BC Balance
tdCHistBalanceTC	decimal	CHist.CHistBalanceTC	TC Balance
tiCHistYearPeriodFrom	integer	CHist.CHistYearPeriodFrom	From Year GL Period
tiCHistYearPeriodTill	integer	CHist.CHistYearPeriodTill	To Year GL Period
tiCostCentre_ID	integer	CostCentre.CostCentre_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.
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
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.
tiJournal_ID	integer	Journal.Journal_ID	Record ID
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.
tiProject_ID	integer	Project.Project_ID	Record ID
tcProjectCode	character	Project.ProjectCode	Project
tcProjectDescription	character	Project.ProjectDescription	Description

Internal usage

QadFinancials

method BCreditorReport.CreditorAccountsSummary