business logic query PostingLineByPostingDateGL

Parameters

iiCompanyId	integer	Company id
itFromPostingDate	date
iiGLID	integer
itToPostingDate	date
iiDivisionID	integer
icGLCodeStart	character
icGLCodeEnd	character
iiJournalID	integer
ilGLIsBalanceAccount	logical
icLayerCando	character
icJournalCando	character
icDivisionCando	character
icCostCentreCando	character
icProjectCando	character
icCurrency	character
icFromICBusinessRelation	character
icToICBusinessRelation	character
icGLCategory	character
icExcludeJournalTypeCode	character
iiCanCCID	integer

query condition

each Posting where

Posting.Company_Id = iiCompanyId AND
Posting.PostingDate >= itFromPostingDate AND
Posting.PostingDate <= itToPostingDate AND
Posting.Journal_ID = iiJournalID

first Journal (inner-join) where

Journal.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal.Journal_ID = Posting.Journal_ID AND
Journal.JournalCode can-do icJournalCando AND
Journal.JournalTypeCode <> icExcludeJournalTypeCode

first Layer (inner-join) where

Layer.Layer_ID = Journal.Layer_ID AND
Layer.LayerCode can-do icLayerCando

first Period (inner-join) where

Period.Company_Id = iiCompanyId AND
Period.Period_ID = Posting.Period_ID AND
Period.PeriodTypeCode <> 'YEARCLOSE'

each PostingLine (inner-join) where

PostingLine.Company_Id = iiCompanyId AND
PostingLine.Posting_ID = Posting.Posting_ID AND
PostingLine.GL_ID = iiGLID AND
PostingLine.Division_ID = iiDivisionID AND
PostingLine.CostCentre_ID = iiCanCCID

first BusinessRelation (conditional-join) where

BusinessRelation.BusinessRelation_ID = PostingLine.IntercoBusinessRelation_ID AND
BusinessRelation.BusinessRelationCode >= icFromICBusinessRelation AND
BusinessRelation.BusinessRelationCode <= icToICBusinessRelation

first CostCentre (conditional-join) where

CostCentre.SharedSet_Id = vi_COSTCENTRE_sharedset(iiCompanyId) AND
CostCentre.CostCentre_ID = PostingLine.CostCentre_ID AND
CostCentre.CostCentreCode can-do icCostCentreCando

first Currency (inner-join) where

Currency.Currency_ID = PostingLine.Currency_ID AND
Currency.CurrencyCode = icCurrency

first Division (conditional-join) where

Division.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division.Division_ID = PostingLine.Division_ID AND
Division.DivisionCode can-do icDivisionCando

first GL (inner-join) where

GL.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL.GL_ID = PostingLine.GL_ID AND
GL.GLCode >= icGLCodeStart AND
GL.GLCode <= icGLCodeEnd AND
GL.GLIsBalanceAccount = ilGLIsBalanceAccount AND
GL.GLCategory = icGLCategory

first Project (conditional-join) where

Project.SharedSet_Id = vi_PROJECT_sharedset(iiCompanyId) AND
Project.Project_ID = PostingLine.Project_ID AND
Project.ProjectCode can-do icProjectCando

query sort order

GL.GL_ID (ascending)
Posting.PostingDate (ascending)

query resultset tqPostingLineByPostingDateGL

field name	data type	db field	description
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.
tiCostCentre_ID	integer	PostingLine.CostCentre_ID	Cost Center Code
tcCostCentreCode	character	CostCentre.CostCentreCode	Cost Center: A code that identifies the cost center.
tiCurrency_ID	integer	PostingLine.Currency_ID	Currency Code
tcCurrencyCode	character	Currency.CurrencyCode	Currency Code
tiDivision_ID	integer	PostingLine.Division_ID	Sub-Account Code
tcDivisionCode	character	Division.DivisionCode	Sub-Account
tcGLCategory	character	GL.GLCategory	Category: A code for classifying the account based on its function on financial statements.
tcGLCode	character	GL.GLCode	A code identifying the GL account.
tcGLDescription	character	GL.GLDescription	A description of the GL account.
tlGLIsBalanceAccount	logical	GL.GLIsBalanceAccount	This field will be calculated by the system based on the value of tcBalance. If the account is a Balance Sheet account the value will be true, if it is a Profit and Loss account then it will be false.
	api annotation:		This field can be left blank as it will be calculated by the system.
tcGLTypeCode	character	GL.GLTypeCode	A code identifying the type of account.
tiJournal_ID	integer	Posting.Journal_ID	Daybook Code
tcJournalCode	character	Journal.JournalCode	A daybook code (maximum eight characters).
tcJournalTypeCode	character	Journal.JournalTypeCode	A daybook type code.
	api annotation:		Value for this field defaults to "Journal Entries".
tcLayerCode	character	Layer.LayerCode	Layer Code
tcPeriodTypeCode	character	Period.PeriodTypeCode	GL Period Type
tiPosting_ID	integer	Posting.Posting_ID	Record ID
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
tdPostingLineCreditTC	decimal	PostingLine.PostingLineCreditTC	TC Credit
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.
tdPostingLineDebitTC	decimal	PostingLine.PostingLineDebitTC	TC Debit
tcProjectCode	character	Project.ProjectCode	Project
tiCompany_ID	integer	Posting.Company_ID	Link to Company
ttPostingDate	date	Posting.PostingDate	The accounting date on which the journal entry is posted. The posting date must be a valid calendar date and must be within the GL calendar year (PostingYear) and period (PostingPeriod).
tiGL_ID	integer	GL.GL_ID	Record ID
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.
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.
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(Posting),rowid(Journal),rowid(Layer),rowid(Period),rowid(PostingLine),rowid(BusinessRelation),rowid(CostCentre),rowid(Currency),rowid(Division),rowid(GL),rowid(Project)

Internal usage

QadFinancials