business logic query BaseCIREC

Description

Base

Parameters

iiCompanyId	integer	Company id
icCandoCreditorCode	character
icCandoBRCode	character
icFromBRCode	character
icFromCreditorCode	character
iiToAccPeriod	integer
icToBRCode	character
icToCreditorCode	character
iiFromAccPeriod	integer
icAllocationStatus	character
iiAllocPeriod	integer
itDatePeriod	date
icPostingType	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 CInvoice1 (inner-join) where

CInvoice1.Company_Id = iiCompanyId AND
CInvoice1.Creditor_ID = Creditor.Creditor_ID AND
CInvoice1.CInvoiceAllocationStatus <> icAllocationStatus AND
CInvoice1.CInvoicePostingDate <= itDatePeriod

first CInvoice2 (conditional-join) where

CInvoice2.Company_Id = iiCompanyId AND
CInvoice2.CInvoice_ID = CInvoice1.LinkedCInvoice_ID AND

first Journal1 (conditional-join) where

Journal1.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal1.Journal_ID = CInvoice2.Journal_ID AND

first Period1 (conditional-join) where

Period1.Company_Id = iiCompanyId AND
Period1.Period_ID = CInvoice2.Period_ID AND

each CInvoicePosting (conditional-join) where

CInvoicePosting.CInvoice_ID = CInvoice1.CInvoice_ID AND
CInvoicePosting.CInvoicePostingType = icPostingType

first Posting (conditional-join) where

Posting.Company_Id = iiCompanyId AND
Posting.Posting_ID = CInvoicePosting.Posting_ID AND

first Journal2 (conditional-join) where

Journal2.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal2.Journal_ID = Posting.Journal_ID AND

first Period2 (conditional-join) where

Period2.Company_Id = iiCompanyId AND
Period2.Period_ID = Posting.Period_ID AND
Period2.PeriodYearPeriod > iiAllocPeriod AND
Period2.PeriodYearPeriod >= iiFromAccPeriod AND
Period2.PeriodYearPeriod <= iiToAccPeriod

first PostingLine (conditional-join) where

PostingLine.Company_Id = iiCompanyId AND
PostingLine.PostingLine_ID = CInvoicePosting.CIRECPostingLine_ID AND

first Currency (inner-join) where

Currency.Currency_ID = CInvoice1.CInvoiceCurrency_ID AND

first Journal3 (inner-join) where

Journal3.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal3.Journal_ID = CInvoice1.Journal_ID AND

first Reason (inner-join) where

Reason.Reason_ID = CInvoice1.Reason_ID AND

query resultset tqBaseCIREC

field name	data type	db field	description
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.
tiCInvoice_ID	integer	CInvoice1.CInvoice_ID	Record ID
tcCInvoiceAllocationStatus	character	CInvoice1.CInvoiceAllocationStatus	Allocation Status.This field indicates whether postings occur and if they affect transient or official layers. User can not delete an invoice which has a non-initial invoice status code.
tcCInvoiceJournalCode	character	Journal3.JournalCode	A daybook code (maximum eight characters).
ttCInvoicePostingDate	date	CInvoice1.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.
tcCInvoicePostingType	character	CInvoicePosting.CInvoicePostingType	Invoice Type. This field displays the invoice type. Invoice Correction and Credit Note Correction display as choices only when the appropriate daybook types have already been defined.
tcCInvoiceReference	character	CInvoice1.CInvoiceReference	Reference. An alphanumeric reference to help identify the invoice in the system. This reference is typically the ID number of the invoice received from the supplier.
tiCInvoiceVoucher	integer	CInvoice1.CInvoiceVoucher	Voucher. This field displays the numeric identifier assigned to the posting. When the daybook of the journal entry is changed (after transfer), the voucher is cleared. Voucher must be unique in one GL period. When the user input is zero, the system automatically assign a value not used yet for this field.
tcCreditorCode	character	Creditor.CreditorCode	Supplier Code
tcCurrencyCode	character	Currency.CurrencyCode	Currency Code
tiCurrencyNumberOfDecimals	integer	Currency.CurrencyNumberOfDecimals	Number of Decimals
tiLinkedCInvoiceVoucher	integer	CInvoice2.CInvoiceVoucher	Voucher. This field displays the numeric identifier assigned to the posting. When the daybook of the journal entry is changed (after transfer), the voucher is cleared. Voucher must be unique in one GL period. When the user input is zero, the system automatically assign a value not used yet for this field.
tcLinkedJournalCode	character	Journal1.JournalCode	A daybook code (maximum eight characters).
tiLinkedPeriodYear	integer	Period1.PeriodYear	GL Calendar Year
tiLinkedPerioPeriod	integer	Period1.PeriodPeriod	GL Period
tiPeriodPeriod	integer	Period2.PeriodPeriod	GL Period
tiPeriodYear	integer	Period2.PeriodYear	GL Calendar Year
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).
tcPostingJournalCode	character	Journal2.JournalCode	A daybook code (maximum eight characters).
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.
tdPostingLineCreditTC	decimal	PostingLine.PostingLineCreditTC	TC Credit
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.
tdPostingLineDebitTC	decimal	PostingLine.PostingLineDebitTC	TC Debit
tiPostingVoucher	integer	Posting.PostingVoucher	Voucher is a sequential number that uniquely identifies the journal entry (within the combination of an entity and a daybook code).
	api annotation:		In create mode,enter 0 in this field - the voucher number is generated by the system. In Modify/Delete, the correct voucher number must be entered because this identifies the record.
tcReasonCode	character	Reason.ReasonCode	Invoice Status Code

Internal usage

QadFinancials

method BCreditorReport.CreditorCIREC
dataset BCreditorReport.tBaseCIRECCopy