project QadFinancials > class BCreditorReport > report query BaseCreditorOpenItems (optimised)

Description

Base query for CreditorOpenItems report


query filter (table tFilter)


tcBusinessFieldNametcDataTypetcOperator
iiCompanyIdi
icFromCreditorCodec>=
icToCreditorCodec<=
icCandoCreditorCodeccan-do
icFromBRCodec>=
icToBRCodec<=
icCandoBRCodeccan-do
icFromDivisionCodec>=
icToDivisionCodec<=
icCandoDivisionCodeccan-do
icFromJournalCodec>=
icToJournalCodec<=
icCandoJournalCodeccan-do
icCurrencyCodec=
ilIncludeInitiall=
tBusinessRelation.BusinessRelationCodec=,>=,>,<>,<=,<,begins,can-do,matches
tBusinessRelation.BusinessRelationName1c=,>=,>,<>,<=,<,begins,can-do,matches
tBusinessRelation.BusinessRelationName2c=,>=,>,<>,<=,<,begins,can-do,matches
tBusinessRelation.BusinessRelationSearchNamec=,>=,>,<>,<=,<,begins,can-do,matches
tCInvoice.CInvoiceBalanceCCd=,>=,>,<>,<=,<
tCInvoice.CInvoiceBalanceLCd=,>=,>,<>,<=,<
tCInvoice.CInvoiceBalanceTCd=,>=,>,<>,<=,<
tCInvoice.CInvoiceDatet=,>=,>,<>,<=,<
tCInvoice.CInvoiceDescriptionc=,>=,>,<>,<=,<,begins,can-do,matches
tCInvoice.CInvoiceDiscountDueDatet=,>=,>,<>,<=,<
tCInvoice.CInvoiceDueDatet=,>=,>,<>,<=,<
tCInvoice.CInvoiceIsInitialStatusl=,<>
tCInvoice.CInvoiceIsLockPaymentl=,<>
tCInvoice.CInvoiceIsSelectedl=,<>
tCInvoice.CInvoiceOriginalCreditCCd=,>=,>,<>,<=,<
tCInvoice.CInvoiceOriginalCreditLCd=,>=,>,<>,<=,<
tCInvoice.CInvoiceOriginalCreditTCd=,>=,>,<>,<=,<
tCInvoice.CInvoiceOriginalDebitCCd=,>=,>,<>,<=,<
tCInvoice.CInvoiceOriginalDebitLCd=,>=,>,<>,<=,<
tCInvoice.CInvoiceOriginalDebitTCd=,>=,>,<>,<=,<
tCInvoice.CInvoicePostingDatet=,>=,>,<>,<=,<
tCInvoice.CInvoiceReferencec=,>=,>,<>,<=,<,begins,can-do,matches
tCInvoice.CInvoiceTSMNumberc=,>=,>,<>,<=,<,begins,can-do,matches
tCInvoice.CInvoiceTypec=,>=,>,<>,<=,<,begins,can-do,matches
tCInvoice.CInvoiceVoucheri=,>=,>,<>,<=,<
tCreditor.CreditorCodec=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency.CurrencyCodec=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency.CurrencyDescriptionc=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency.CurrencyNumberOfDecimalsi=,>=,>,<>,<=,<
tDivision.DivisionCodec=,>=,>,<>,<=,<,begins,can-do,matches
tDivision.DivisionDescriptionc=,>=,>,<>,<=,<,begins,can-do,matches
tJournal.JournalCodec=,>=,>,<>,<=,<,begins,can-do,matches
tPeriod.PeriodPeriodi=,>=,>,<>,<=,<
tPeriod.PeriodYeari=,>=,>,<>,<=,<
tProfile2.ProfileCodec=,>=,>,<>,<=,<,begins,can-do,matches
tProfile1.ProfileCodec=,>=,>,<>,<=,<,begins,can-do,matches
tReason.ReasonCodec=,>=,>,<>,<=,<,begins,can-do,matches


Parameters (internal)


iiCompanyIdintegerCompany id
icFromCreditorCodecharacter
icToCreditorCodecharacter
icCandoCreditorCodecharacter
icFromBRCodecharacter
icToBRCodecharacter
icCandoBRCodecharacter
icFromDivisionCodecharacter
icToDivisionCodecharacter
icCandoDivisionCodecharacter
icFromJournalCodecharacter
icToJournalCodecharacter
icCandoJournalCodecharacter
icCurrencyCodecharacter
ilIncludeInitiallogical


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 CInvoice (inner-join) where
CInvoice.Company_Id = iiCompanyId AND
CInvoice.Creditor_ID = Creditor.Creditor_ID AND
CInvoice.CInvoiceIsOpen = true AND
CInvoice.CInvoiceIsInitialStatus = ilIncludeInitial

          first Currency (inner-join) where
Currency.Currency_ID = CInvoice.CInvoiceCurrency_ID AND
Currency.CurrencyCode = icCurrencyCode

          first Division (conditional-join) where
Division.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division.Division_ID = CInvoice.Division_ID AND
Division.DivisionCode >= icFromDivisionCode AND
Division.DivisionCode <= icToDivisionCode AND
Division.DivisionCode can-do icCandoDivisionCode

          first Journal (inner-join) where
Journal.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal.Journal_ID = CInvoice.Journal_ID AND
Journal.JournalCode >= icFromJournalCode AND
Journal.JournalCode <= icToJournalCode AND
Journal.JournalCode can-do icCandoJournalCode

          first Period (inner-join) where
Period.Company_Id = iiCompanyId AND
Period.Period_ID = CInvoice.Period_ID AND

          first Reason (inner-join) where
Reason.Reason_ID = CInvoice.Reason_ID AND

      first Profile1 (inner-join) where
Profile1.Profile_ID = Creditor.InvControlGLProfile_ID AND

      first Profile2 (inner-join) where
Profile2.Profile_ID = Creditor.CnControlGLProfile_ID AND


query resultset tqBaseCreditorOpenItems


field namedata typedb fielddescription
tlCInvoiceIsInitialStatuslogicalCInvoice.CInvoiceIsInitialStatusInitial 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.
tlCInvoiceIsLockPaymentlogicalCInvoice.CInvoiceIsLockPaymentLock Payment. This field indicates if the invoice is locked for payment. The invoice cannot be included in any automatic payment selections if this field is true.
api annotation:PartialUpdate = yes - defaults to false
tcCInvoiceReferencecharacterCInvoice.CInvoiceReferenceReference. 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.
tiCurrency_IDintegerCurrency.Currency_IDRecord ID
tcCurrencyDescriptioncharacterCurrency.CurrencyDescriptionCurrency Description
tiCurrencyNumberOfDecimalsintegerCurrency.CurrencyNumberOfDecimalsNumber of Decimals
tiDivision_IDintegerDivision.Division_IDRecord ID
tcDivisionDescriptioncharacterDivision.DivisionDescriptionDescription
tiPeriodPeriodintegerPeriod.PeriodPeriodGL Period
tiPeriodYearintegerPeriod.PeriodYearGL Calendar Year
ttCInvoiceDiscountDueDatedateCInvoice.CInvoiceDiscountDueDateDiscount 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.
ttCInvoiceDueDatedateCInvoice.CInvoiceDueDateDue 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.
tlCInvoiceIsSelectedlogicalCInvoice.CInvoiceIsSelectedSelected. This field indicates whether the invoice is included in a payment selection.
This field is read-only.
tdCInvoiceOriginalCreditCCdecimalCInvoice.CInvoiceOriginalCreditCCSC 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.
tdCInvoiceOriginalCreditLCdecimalCInvoice.CInvoiceOriginalCreditLCBC 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.
tdCInvoiceOriginalCreditTCdecimalCInvoice.CInvoiceOriginalCreditTCTC 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.
tdCInvoiceOriginalDebitCCdecimalCInvoice.CInvoiceOriginalDebitCCSC 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.
tdCInvoiceOriginalDebitLCdecimalCInvoice.CInvoiceOriginalDebitLCBC 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.
tcBusinessRelationName2characterBusinessRelation.BusinessRelationName2Second Name. An extended name when the Name field is not large enough to contain all information.
This fielsd can be blank.
tcBusinessRelationSearchNamecharacterBusinessRelation.BusinessRelationSearchNameSearch Name: An alternate name for finding the business relation. This field can be useful for sorting and filtering.
api annotation:PartialUpdate = yes - defaults to the BusinessRelationCode.
tcJournalCodecharacterJournal.JournalCodeA daybook code (maximum eight characters).
tdCInvoiceOriginalDebitTCdecimalCInvoice.CInvoiceOriginalDebitTCTC 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.
ttCInvoicePostingDatedateCInvoice.CInvoicePostingDatePosting Date. This filed indicates the date on which the invoice is to be posted.
api annotation:This field defaults from the invoice creation date.
tcCInvoiceTSMNumbercharacterCInvoice.CInvoiceTSMNumberPayment Reference. This field displays a unique reference number to be included in the supplier payment file.
This reference can consist of a Transfer with Structured Message (TSM) number.
This field can be blank.
tcCInvoiceTypecharacterCInvoice.CInvoiceTypeInvoice 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.
tiCInvoiceVoucherintegerCInvoice.CInvoiceVoucherVoucher. 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.
tcDivisionCodecharacterDivision.DivisionCodeSub-Account
tcBusinessRelationCodecharacterBusinessRelation.BusinessRelationCodeBusiness 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.
tcBusinessRelationName1characterBusinessRelation.BusinessRelationName1Name: 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.
tcReasonCodecharacterReason.ReasonCodeInvoice Status Code
tiBusinessRelation_IDintegerCreditor.BusinessRelation_IDName
tcCreditorCodecharacterCreditor.CreditorCodeSupplier Code
tcCurrencyCodecharacterCurrency.CurrencyCodeCurrency Code
ttCInvoiceDatedateCInvoice.CInvoiceDateInvoice 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.
tcCInvoiceDescriptioncharacterCInvoice.CInvoiceDescriptionDescription. A brief description of the invoice. The system generates a default description based on the Reference and Supplier Code.
tcInvControlGLProfileCodecharacterProfile1.ProfileCodeA code (maximum 20 characters) that identifies a profile.
tiInvControlGLProfileIDintegerProfile1.Profile_IDRecord ID
tcCnControlGLProfileCodecharacterProfile2.ProfileCodeA code (maximum 20 characters) that identifies a profile.
tiCnControlGLProfileIDintegerProfile2.Profile_IDRecord ID
tdCInvoiceBalanceCCdecimalCInvoice.CInvoiceBalanceCCSC 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.
tdCInvoiceBalanceLCdecimalCInvoice.CInvoiceBalanceLCBC 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.
tdCInvoiceBalanceTCdecimalCInvoice.CInvoiceBalanceTCBalance. This field displays the sum of the debit and credit amounts of all posting lines in transaction currency.
CreditorCustomcharactercalculatedcustom fields
BusinessRelationCustomcharactercalculatedcustom fields
CInvoiceCustomcharactercalculatedcustom fields
CurrencyCustomcharactercalculatedcustom fields
DivisionCustomcharactercalculatedcustom fields
JournalCustomcharactercalculatedcustom fields
PeriodCustomcharactercalculatedcustom fields
ReasonCustomcharactercalculatedcustom fields
Profile1Customcharactercalculatedcustom fields
Profile2Customcharactercalculatedcustom fields
ti_sequenceintegercalculatedprimary index
tc_rowidcharactercalculated = rowid(Creditor),rowid(BusinessRelation),rowid(CInvoice),rowid(Currency),rowid(Division),rowid(Journal),rowid(Period),rowid(Reason),rowid(Profile1),rowid(Profile2)


Internal usage


QadFinancials
method BCreditorReport.CreditorOpenItems