project QadFinancials > class BDebtorReport > 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)


tcBusinessFieldNametcDataTypetcOperator
iiCompanyIdi
iiAccYearPeriodi>=
icCandoBRCodeccan-do
icCandoDebtorCodeccan-do
icCanDoGLCodeccan-do
icFromBRCodec>=
icFromDebtorCodec>=
icFromGlCodec>=
icToBRCodec<=
icToDebtorCodec<=
icToGLCodec<=
tBusinessRelation.BusinessRelationCodec=,>=,>,<>,<=,<,begins,can-do,matches
tBusinessRelation.BusinessRelationName1c=,>=,>,<>,<=,<,begins,can-do,matches
tBusinessRelation.BusinessRelationName2c=,>=,>,<>,<=,<,begins,can-do,matches
tBusinessRelation.BusinessRelationSearchNamec=,>=,>,<>,<=,<,begins,can-do,matches
tCostCentre.CostCentreCodec=,>=,>,<>,<=,<,begins,can-do,matches
tCostCentre.CostCentreDescriptionc=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency.CurrencyCodec=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency.CurrencyDescriptionc=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency.CurrencyNumberOfDecimalsi=,>=,>,<>,<=,<
tDebtor.DebtorCodec=,>=,>,<>,<=,<,begins,can-do,matches
tDHist.DHistBalanceCCd=,>=,>,<>,<=,<
tDHist.DHistBalanceLCd=,>=,>,<>,<=,<
tDHist.DHistBalanceTCd=,>=,>,<>,<=,<
tDHist.DHistGrossCredNoteCCd=,>=,>,<>,<=,<
tDHist.DHistGrossCredNoteLCd=,>=,>,<>,<=,<
tDHist.DHistGrossCredNoteTCd=,>=,>,<>,<=,<
tDHist.DHistGrossInvoiceCCd=,>=,>,<>,<=,<
tDHist.DHistGrossInvoiceLCd=,>=,>,<>,<=,<
tDHist.DHistGrossInvoiceTCd=,>=,>,<>,<=,<
tDHist.DHistMovementCreditCCd=,>=,>,<>,<=,<
tDHist.DHistMovementCreditLCd=,>=,>,<>,<=,<
tDHist.DHistMovementCreditTCd=,>=,>,<>,<=,<
tDHist.DHistMovementDebitCCd=,>=,>,<>,<=,<
tDHist.DHistMovementDebitLCd=,>=,>,<>,<=,<
tDHist.DHistMovementDebitTCd=,>=,>,<>,<=,<
tDHist.DHistNettoCredNoteCCd=,>=,>,<>,<=,<
tDHist.DHistNettoCredNoteLCd=,>=,>,<>,<=,<
tDHist.DHistNettoCredNoteTCd=,>=,>,<>,<=,<
tDHist.DHistNettoInvoiceCCd=,>=,>,<>,<=,<
tDHist.DHistNettoInvoiceLCd=,>=,>,<>,<=,<
tDHist.DHistNettoInvoiceTCd=,>=,>,<>,<=,<
tDHist.DHistYearPeriodFromi=,>=,>,<>,<=,<
tDHist.DHistYearPeriodTilli=,>=,>,<>,<=,<
tDivision.DivisionCodec=,>=,>,<>,<=,<,begins,can-do,matches
tDivision.DivisionDescriptionc=,>=,>,<>,<=,<,begins,can-do,matches
tGL.GLCodec=,>=,>,<>,<=,<,begins,can-do,matches
tGL.GLDescriptionc=,>=,>,<>,<=,<,begins,can-do,matches
tJournal.JournalCodec=,>=,>,<>,<=,<,begins,can-do,matches
tJournal.JournalDescriptionc=,>=,>,<>,<=,<,begins,can-do,matches
tProject.ProjectCodec=,>=,>,<>,<=,<,begins,can-do,matches
tProject.ProjectDescriptionc=,>=,>,<>,<=,<,begins,can-do,matches


Parameters (internal)


iiCompanyIdintegerCompany id
iiAccYearPeriodinteger
icCandoBRCodecharacter
icCandoDebtorCodecharacter
icCanDoGLCodecharacter
icFromBRCodecharacter
icFromDebtorCodecharacter
icFromGlCodecharacter
icToBRCodecharacter
icToDebtorCodecharacter
icToGLCodecharacter


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 namedata typedb fielddescription
tiCostCentre_IDintegerCostCentre.CostCentre_IDRecord ID
tiDivision_IDintegerDivision.Division_IDRecord ID
tiGL_IDintegerGL.GL_IDRecord ID
tiJournal_IDintegerJournal.Journal_IDRecord ID
tiProject_IDintegerProject.Project_IDRecord ID
tcCostCentreCodecharacterCostCentre.CostCentreCodeCost Center: A code that identifies the cost center.
tcCostCentreDescriptioncharacterCostCentre.CostCentreDescriptionDescription: a brief description of the cost center.
tiCurrency_IDintegerCurrency.Currency_IDRecord ID
tcCurrencyCodecharacterCurrency.CurrencyCodeCurrency Code
tcCurrencyDescriptioncharacterCurrency.CurrencyDescriptionCurrency Description
tiCurrencyNumberOfDecimalsintegerCurrency.CurrencyNumberOfDecimalsNumber of Decimals
tcDivisionCodecharacterDivision.DivisionCodeSub-Account
tcDivisionDescriptioncharacterDivision.DivisionDescriptionDescription
tcGLCodecharacterGL.GLCodeA code identifying the GL account.
tcGLDescriptioncharacterGL.GLDescriptionA description of the GL account.
tcJournalCodecharacterJournal.JournalCodeA daybook code (maximum eight characters).
tcJournalDescriptioncharacterJournal.JournalDescriptionA brief description of the daybook.
api annotation:Value for this field defaults to null.
tcProjectCodecharacterProject.ProjectCodeProject
tcProjectDescriptioncharacterProject.ProjectDescriptionDescription
tiDebtor_IDintegerDebtor.Debtor_IDRecord ID
tcDebtorCodecharacterDebtor.DebtorCodeA 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_IDintegerBusinessRelation.BusinessRelation_IDRecord ID
tcBusinessRelationCodecharacterBusinessRelation.BusinessRelationCodeBusiness 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.
tcBusinessRelationName1characterBusinessRelation.BusinessRelationName1Name: 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.
tcBusinessRelationName2characterBusinessRelation.BusinessRelationName2Second Name. An extended name when the Name field is not large enough to contain all information.
This fielsd can be blank.
tcBusinessRelationSearchNamecharacterBusinessRelation.BusinessRelationSearchNameSearch 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.
tdDHistBalanceCCdecimalDHist.DHistBalanceCCSC Balance
tdDHistBalanceLCdecimalDHist.DHistBalanceLCBC Balance
tdDHistBalanceTCdecimalDHist.DHistBalanceTCTC Balance
tdDHistGrossCredNoteCCdecimalDHist.DHistGrossCredNoteCCSC Gross Credit Note
tdDHistGrossCredNoteLCdecimalDHist.DHistGrossCredNoteLCBC Gross Credit Note
tdDHistGrossCredNoteTCdecimalDHist.DHistGrossCredNoteTCTC Gross Credit Note
tdDHistGrossInvoiceCCdecimalDHist.DHistGrossInvoiceCCSC Gross Invoice
tdDHistGrossInvoiceLCdecimalDHist.DHistGrossInvoiceLCBC Gross Invoice
tdDHistGrossInvoiceTCdecimalDHist.DHistGrossInvoiceTCTC Gross Invoice
tdDHistMovementCreditCCdecimalDHist.DHistMovementCreditCCSC Activity Credit
tdDHistMovementCreditLCdecimalDHist.DHistMovementCreditLCBC Activity Credit
tdDHistMovementCreditTCdecimalDHist.DHistMovementCreditTCTC Activity Credit
tdDHistMovementDebitCCdecimalDHist.DHistMovementDebitCCSC Activity Debit
tdDHistMovementDebitLCdecimalDHist.DHistMovementDebitLCBC Activity Debit
tdDHistMovementDebitTCdecimalDHist.DHistMovementDebitTCTC Activity Debit
tdDHistNettoCredNoteCCdecimalDHist.DHistNettoCredNoteCCSC Net Credit Note
tdDHistNettoCredNoteLCdecimalDHist.DHistNettoCredNoteLCBC Net to Credit Note
tdDHistNettoCredNoteTCdecimalDHist.DHistNettoCredNoteTCTC Net Credit Note
tdDHistNettoInvoiceCCdecimalDHist.DHistNettoInvoiceCCSC Net Invoice
tdDHistNettoInvoiceLCdecimalDHist.DHistNettoInvoiceLCBC Net to Invoice
tdDHistNettoInvoiceTCdecimalDHist.DHistNettoInvoiceTCTC Net Invoice
tiDHistYearPeriodFromintegerDHist.DHistYearPeriodFromFrom Year GL Period
tiDHistYearPeriodTillintegerDHist.DHistYearPeriodTillTo Year GL Period
DebtorCustomcharactercalculatedcustom fields
BusinessRelationCustomcharactercalculatedcustom fields
DHistCustomcharactercalculatedcustom fields
CostCentreCustomcharactercalculatedcustom fields
CurrencyCustomcharactercalculatedcustom fields
DivisionCustomcharactercalculatedcustom fields
GLCustomcharactercalculatedcustom fields
JournalCustomcharactercalculatedcustom fields
ProjectCustomcharactercalculatedcustom fields
ti_sequenceintegercalculatedprimary index
tc_rowidcharactercalculated = rowid(Debtor),rowid(BusinessRelation),rowid(DHist),rowid(CostCentre),rowid(Currency),rowid(Division),rowid(GL),rowid(Journal),rowid(Project)


Internal usage


QadFinancials
method BDebtorReport.DebtorAccountsSummary