business logic query APBalanceAsOfDate

Description

Query to retrieve the AP balance as-of-date

Parameters

iiCompanyId	integer	Company id
itEndOfDate	date

query condition

each CInvoice where

CInvoice.Company_Id = iiCompanyId AND
CInvoice.CInvoiceClosingDate > itEndOfDate AND
CInvoice.CInvoicePostingDate <= itEndOfDate AND
CInvoice.CInvoiceIsInitialStatus = no

each CInvoiceMovement (inner-join) where

CInvoiceMovement.Company_Id = iiCompanyId AND
CInvoiceMovement.CInvoice_ID = CInvoice.CInvoice_ID AND
CInvoiceMovement.CInvoiceMovementPostDate <= itEndOfDate

first PostingLine (inner-join) where

PostingLine.Company_Id = iiCompanyId AND
PostingLine.PostingLine_ID = CInvoiceMovement.PostingLine_ID AND

first GL (inner-join) where

GL.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL.GL_ID = PostingLine.GL_ID AND

query resultset tqAPBalanceAsOfDate

field name	data type	db field	description
tiCInvoice_ID	integer	CInvoice.CInvoice_ID	Record ID
ttCInvoiceClosingDate	date	CInvoice.CInvoiceClosingDate	Closing Date. This field displays the date on which the open balance became zero.
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.
tcGLCode	character	GL.GLCode	A code identifying the GL account.
tiPostingLine_ID	integer	CInvoiceMovement.PostingLine_ID	Link to PostingLine
tdPostingLineCreditCC	decimal	PostingLine.PostingLineCreditCC	SC Credit
	api annotation:		Partial Update = yes - it will get a default value depending on the value of PostingLinCreditTC, PostingLineCCRate & PostingLineCCScale. SC Amount = TC Amount * Exchange rate (TC/SC) * Scale Factor (TC/SC). If Partial Update is false, you should enter the correct value for this field
tdPostingLineCreditLC	decimal	PostingLine.PostingLineCreditLC	BC Credit
	api annotation:		Partial Update = yes - it will get a default value depends on the value of PostingLineCreditTC, PostingLineExchangeRate & PostingLineRateScale. BC Amount = TC Amount * Exchange rate (TC/BC) * Scale Factor (TC/BC). If Partial Update is false, enter a value for this field.
tdPostingLineDebitCC	decimal	PostingLine.PostingLineDebitCC	SC Debit
	api annotation:		Partial Update = yes - it will get a default value depending on the value of PostingLineDebitTC, PostingLineCCRate & PostingLineCCScale. SC Amount = TC Amount * Exchange rate (TC/SC) * Scale Factor (TC/SC). If Partial Update is false, enter the correct value for this field.
tdPostingLineDebitLC	decimal	PostingLine.PostingLineDebitLC	BC Debit
	api annotation:		Partial Update = yes - it will get a default value depending on the value of PostingLineDebitTC, PostingLineExchangeRate and PostingLineRateScale. BC Amount = TC Amount * Exchange rate (TC/BC) * Scale Factor (TC/BC). If Partial Update = false, enter the correct value into this field.

Internal usage

QadFinancials

method BClosingPostingsReport.CreditorControlGLBalance