business logic query CreditorOpenItemsByCreditor

Description

CreditorOpenItemsByCreditor

Parameters

iiCompanyId	integer	Company id
icBusRelCodeFrom	character	BusRelCodeFrom
icBusRelCodeTo	character	BusRelCodeTo
icCreditorCode	character	CreditorCode
icCreditorCodeFrom	character	CreditorCodeFrom
icCreditorCodeTo	character	CreditorCodeTo
itCInvoiceDate	date	CInvoiceDate
itCInvoiceDateFrom	date	CInvoiceDateFrom
itCInvoiceDateTo	date	CInvoiceDateTo
itCInvoiceDueDate	date	CInvoiceDueDate
icCInvoiceType	character	SInvoiceType
icDivisionCodeFrom	character	DivisionCodeFrom
icDivisionCodeTo	character	DivisionCodeTo
ilIncludeInitial	logical

query condition

each Creditor where

Creditor.SharedSet_Id = vi_CREDITOR_sharedset(iiCompanyId) AND
Creditor.CreditorCode = icCreditorCode AND
Creditor.CreditorCode >= icCreditorCodeFrom AND
Creditor.CreditorCode <= icCreditorCodeTo

first BusinessRelation (inner-join) where

BusinessRelation.BusinessRelation_ID = Creditor.BusinessRelation_ID AND
BusinessRelation.BusinessRelationCode >= icBusRelCodeFrom AND
BusinessRelation.BusinessRelationCode <= icBusRelCodeTo

each CInvoice (inner-join) where

CInvoice.Company_Id = iiCompanyId AND
CInvoice.Creditor_ID = Creditor.Creditor_ID AND
CInvoice.CInvoiceIsOpen = true AND
CInvoice.CInvoiceDueDate < itCInvoiceDueDate AND
CInvoice.CInvoiceType can-do icCInvoiceType AND
CInvoice.CInvoiceDate >= itCInvoiceDateFrom AND
CInvoice.CInvoiceDate = itCInvoiceDate AND
CInvoice.CInvoiceDate <= itCInvoiceDateTo AND
CInvoice.CInvoiceIsInitialStatus = ilIncludeInitial

each BankStateAlloc (outer-join) where

BankStateAlloc.Company_Id = iiCompanyId AND
BankStateAlloc.CInvoice_ID = CInvoice.CInvoice_ID AND

first BankStateLine (outer-join) where

BankStateLine.Company_Id = iiCompanyId AND
BankStateLine.BankStateLine_ID = BankStateAlloc.BankStateLine_ID AND

first BankState (outer-join) where

BankState.Company_Id = iiCompanyId AND
BankState.BankState_ID = BankStateLine.BankState_ID AND

each CInvoiceMovement (outer-join) where

CInvoiceMovement.Company_Id = iiCompanyId AND
CInvoiceMovement.CInvoice_ID = CInvoice.CInvoice_ID AND
CInvoiceMovement.CInvoiceMovementType = 'INITIAL'

first PostingLine (outer-join) where

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

first Currency (outer-join) where

Currency.Currency_ID = PostingLine.Currency_ID AND

first Posting (outer-join) where

Posting.Company_Id = iiCompanyId AND
Posting.Posting_ID = PostingLine.Posting_ID AND

first Division (conditional-join) where

Division.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division.Division_ID = CInvoice.Division_ID AND
Division.DivisionCode >= icDivisionCodeFrom AND
Division.DivisionCode <= icDivisionCodeTo

first Journal (inner-join) where

Journal.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal.Journal_ID = CInvoice.Journal_ID AND

first Period (inner-join) where

Period.Company_Id = iiCompanyId AND
Period.Period_ID = CInvoice.Period_ID AND

query resultset tqCreditorOpenItemsByCreditor

field name	data type	db field	description
tcBankStateNumber	character	BankState.BankStateNumber	Bank Statement No
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.
tdCInvoiceBalanceCC	decimal	CInvoice.CInvoiceBalanceCC	SC Balance. This field displays the sum of the debit and credit amounts of all posting lines in statutory currency. This field is calculated by the system using the TC Balance and the SC rate.
tdCInvoiceBalanceLC	decimal	CInvoice.CInvoiceBalanceLC	BC Balance. This field displays the sum of the debit and credit amounts of all posting lines in base currency. This field is calculated by the system using the TC Balance and the Exchange rate.
tdCInvoiceBalanceTC	decimal	CInvoice.CInvoiceBalanceTC	Balance. This field displays the sum of the debit and credit amounts of all posting lines in transaction currency.
ttCInvoiceDate	date	CInvoice.CInvoiceDate	Invoice Date. This field indicates the invoice creation date. The system uses this field with the credit terms to calculate due date and discount date.
	api annotation:		This field defaults from the system date.
tcCInvoiceDescription	character	CInvoice.CInvoiceDescription	Description. A brief description of the invoice. The system generates a default description based on the Reference and Supplier Code.
ttCInvoiceDiscountDueDate	date	CInvoice.CInvoiceDiscountDueDate	Discount Due Date.This field indicates the last date a discount applies, calculated by the system based on the credit terms and the invoice date. User can modify this field without affecting the credit terms.
ttCInvoiceDueDate	date	CInvoice.CInvoiceDueDate	Due Date.This field indicates the date when payment become due. This field can be modified without affecting the credit terms. This field is calculated by the system based on the credit terms and the invoice date.
tlCInvoiceIsInitialStatus	logical	CInvoice.CInvoiceIsInitialStatus	Initial Status. This field indicates if the invoice is in initial status.
	api annotation:		This field defaults from the Initial Status field of the Invoice status code.
tdCInvoiceOriginalCreditCC	decimal	CInvoice.CInvoiceOriginalCreditCC	SC Invoice Amount (CR). This field displays the credit total invoice amount in the statutory currency. This field is calculated by the system using the TC Invoice Amount(CR) and the SC Rate.
tdCInvoiceOriginalCreditLC	decimal	CInvoice.CInvoiceOriginalCreditLC	BC Invoice Amount(CR). This field displays the credit total invoice amount in the base currency. This field is calculated by the system using the TC Invoice Amount(CR) and the Exchange rate.
tdCInvoiceOriginalCreditTC	decimal	CInvoice.CInvoiceOriginalCreditTC	TC Invoice Amount CR. This field displays the credit total invoice amount in the transaction currency. If you modify this field, the system automatically recalculates the exchange rate to ensure that the BC Invoice Amount remains the same.
tdCInvoiceOriginalDebitCC	decimal	CInvoice.CInvoiceOriginalDebitCC	SC Invoice Amount (DR). This field displays the debit total invoice amount in the statutory currency. This field is calculated by the system using the TC Invoice Amount(CR) and the SC Rate.
tdCInvoiceOriginalDebitLC	decimal	CInvoice.CInvoiceOriginalDebitLC	BC Invoice Amount(DR). This field displays the debit total invoice amount in the base currency. This field is calculated by the system using the TC Invoice Amount(DR) and the Exchange rate.
tdCInvoiceOriginalDebitTC	decimal	CInvoice.CInvoiceOriginalDebitTC	TC AP Amount. This field displays the debit total invoice amount in the transaction currency. If you modify this field, the system automatically recalculates the exchange rate to ensure that the TC Invoice Amount remains the same.
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.
tcCInvoiceReference	character	CInvoice.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.
tcCInvoiceType	character	CInvoice.CInvoiceType	Invoice Type. This field identifies the invoice type. The value can be Invoice Correction and Credit Note Correction type only when the appropriate daybook types have already been defined.
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
tcJournalCode	character	Journal.JournalCode	A daybook code (maximum eight characters).
tiPeriodPeriod	integer	Period.PeriodPeriod	GL Period
tiPeriodYear	integer	Period.PeriodYear	GL Calendar Year
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.
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(Creditor),rowid(BusinessRelation),rowid(CInvoice),rowid(BankStateAlloc),rowid(BankStateLine),rowid(BankState),rowid(CInvoiceMovement),rowid(PostingLine),rowid(Currency),rowid(Posting),rowid(Division),rowid(Journal),rowid(Period)

Internal usage

QadFinancials

method BCreditorReport.CreditorOpenItemsDetail