business logic query DebtorReminderOverByDInvoice

Description

DebtorOpenItemsByDInvoice

Parameters

iiCompanyId	integer	Company id
icBusRelCodeTo	character	BusRelCodeTo
itDInvoiceDateFrom	date	DInvoiceDateFrom
icDivisionCodeFrom	character	DivisionCodeFrom
icDivisionCodeTo	character	DivisionCodeTo
itDInvoiceDateTo	date	DInvoiceDateTo
itDInvoiceDueDate	date	DInvoiceDueDate
icDebtorCodeFrom	character	DebtorCodeFrom
icDebtorCodeTo	character	DebtorCodeTo
icBusRelCodeFrom	character	BusRelCodeFrom
itDInvoiceDate	date	DInvoiceDate
icDebtorCode	character	Customer Code
icCanDoBankStateAllocType	character

query condition

each DInvoice where

DInvoice.Company_Id = iiCompanyId AND
DInvoice.DInvoiceIsOpen = true AND
DInvoice.DInvoiceDueDate < itDInvoiceDueDate AND
DInvoice.DInvoiceDate >= itDInvoiceDateFrom AND
DInvoice.DInvoiceDate <= itDInvoiceDateTo AND
DInvoice.DInvoiceDate = itDInvoiceDate AND
(DInvoice.DInvoiceReminderCount <> 0 AND
DInvoice.DInvoiceType = 'INVOICE' OR
DInvoice.DInvoiceType = 'CREDITNOTE' OR
DInvoice.DInvoiceType = 'PREPAYMENT' )

each BankStateAlloc (outer-join) where

BankStateAlloc.Company_Id = iiCompanyId AND
BankStateAlloc.DInvoice_ID = DInvoice.DInvoice_ID AND
BankStateAlloc.BankStateAllocType can-do icCanDoBankStateAllocType

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

first Company (inner-join) where

Company.Company_Id = iiCompanyId AND
Company.Company_ID = DInvoice.Company_ID AND

first Currency (inner-join) where

Currency.Currency_ID = DInvoice.DInvoiceCurrency_ID AND

first Debtor (inner-join) where

Debtor.SharedSet_Id = vi_DEBTOR_sharedset(iiCompanyId) AND
Debtor.Debtor_ID = DInvoice.Debtor_ID AND
Debtor.DebtorCode >= icDebtorCodeFrom and
Debtor.DebtorCode <= icDebtorCodeTo and
Debtor.DebtorCode can-do icDebtorCode

first BusinessRelation (inner-join) where

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

first PaymentCondition (inner-join) where

PaymentCondition.PaymentCondition_ID = Debtor.NormalPaymentCondition_ID AND

first Division (conditional-join) where

Division.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division.Division_ID = DInvoice.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 = DInvoice.Journal_ID AND

first Period (inner-join) where

Period.Company_Id = iiCompanyId AND
Period.Period_ID = DInvoice.Period_ID AND

query resultset tqDebtorReminderOverByDInvoice

field name	data type	db field	description
tcBankStateNumber	character	BankState.BankStateNumber	Bank Statement No
tiBusinessRelation_ID	integer	BusinessRelation.BusinessRelation_ID	Record ID
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.
tcCompanyCode	character	Company.CompanyCode	Entity 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
tcDebtorCode	character	Debtor.DebtorCode	A 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.
tiDInvoice_ID	integer	DInvoice.DInvoice_ID	Record ID
tdDInvoiceBalanceCC	decimal	DInvoice.DInvoiceBalanceCC	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.
tdDInvoiceBalanceLC	decimal	DInvoice.DInvoiceBalanceLC	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.
tdDInvoiceBalanceTC	decimal	DInvoice.DInvoiceBalanceTC	Balance. This field displays the sum of the debit and credit amounts of all posting lines in transaction currency.
ttDInvoiceDate	date	DInvoice.DInvoiceDate	Invoice Date. This field indicates the invoice creation date.This field is normallly prior to the posting date and within the same accounting period as the posting date. The date must be within the upper and lower date limits of an open GL period. The system uses the invoice date with the credit terms to calculate due date and discount date.
	api annotation:		This field defaults to the DInvoicePostingDate.
tcDInvoiceDescription	character	DInvoice.DInvoiceDescription	Description. A brief description of the invoice. This field is mandatory.
ttDInvoiceDiscountDueDate	date	DInvoice.DInvoiceDiscountDueDate	Discount Due Date. This field displays the last date a discount applies, calculated by the system based on the credit terms and the invoice date. You can modify the due date without affecting the credit terms. If the credit terms have a base date specified, this is used in the due date calculations rather than the invoice creation date.
tcDInvoiceDIText	character	DInvoice.DInvoiceDIText	CIDescription. A brief description for the SI posting.
	api annotation:		This field defaults from the DInvoiceDescription.
ttDInvoiceDueDate	date	DInvoice.DInvoiceDueDate	Due Date. This field displays the date when payment is due, calculated by the system based on the credit terms and the invoice date. User can modify the due date without affecting the credit terms. If the credit terms have a base date specified, this is used in the due date calculations rather than the invoice creation date.
ttDinvoiceLastReminderDate	date	DInvoice.DinvoiceLastReminderDate	Last Reminder Date.Date of last Customer Reminder Letter.
tdDInvoiceOriginalCreditCC	decimal	DInvoice.DInvoiceOriginalCreditCC	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.
tdDInvoiceOriginalCreditLC	decimal	DInvoice.DInvoiceOriginalCreditLC	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.
tdDInvoiceOriginalCreditTC	decimal	DInvoice.DInvoiceOriginalCreditTC	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.
tdDInvoiceOriginalDebitCC	decimal	DInvoice.DInvoiceOriginalDebitCC	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.
tdDInvoiceOriginalDebitLC	decimal	DInvoice.DInvoiceOriginalDebitLC	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.
tdDInvoiceOriginalDebitTC	decimal	DInvoice.DInvoiceOriginalDebitTC	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.
ttDInvoicePostingDate	date	DInvoice.DInvoicePostingDate	Posting Date. This field displays the date the sales-related invoice was generated by Invoice Post and Print. This field is updated when the invoice is validated and saved.
	api annotation:		This field defaults from the invoice creation date.
tiDInvoiceReminderCount	integer	DInvoice.DInvoiceReminderCount	Reminder Counter. This field displays the reminder level that applies to this invoice.
tcDInvoiceType	character	DInvoice.DInvoiceType	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.
	api annotation:		PartialUpdate = yes - defaults to Invoice
tiDInvoiceVoucher	integer	DInvoice.DInvoiceVoucher	Voucher.This field displays the numeric identifier assigned to the posting. When the daybook of the journal entry is charged(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.
tcJournalCode	character	Journal.JournalCode	A daybook code (maximum eight characters).
tcPaymentConditionCode	character	PaymentCondition.PaymentConditionCode	Credit Terms Code
tiPeriodPeriod	integer	Period.PeriodPeriod	GL Period
tiPeriodYear	integer	Period.PeriodYear	GL Calendar Year
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(DInvoice),rowid(BankStateAlloc),rowid(BankStateLine),rowid(BankState),rowid(Company),rowid(Currency),rowid(Debtor),rowid(BusinessRelation),rowid(PaymentCondition),rowid(Division),rowid(Journal),rowid(Period)

Internal usage

QadFinancials

method BDebtorReport.DebtorRemindersOverview