project QadFinancials > class BCreditorReport > business logic query CreditorOpenItemsByCreditor

Description

CreditorOpenItemsByCreditor


Parameters


iiCompanyIdintegerCompany id
icBusRelCodeFromcharacterBusRelCodeFrom
icBusRelCodeTocharacterBusRelCodeTo
icCreditorCodecharacterCreditorCode
icCreditorCodeFromcharacterCreditorCodeFrom
icCreditorCodeTocharacterCreditorCodeTo
itCInvoiceDatedateCInvoiceDate
itCInvoiceDateFromdateCInvoiceDateFrom
itCInvoiceDateTodateCInvoiceDateTo
itCInvoiceDueDatedateCInvoiceDueDate
icCInvoiceTypecharacterSInvoiceType
icDivisionCodeFromcharacterDivisionCodeFrom
icDivisionCodeTocharacterDivisionCodeTo
ilIncludeInitiallogical


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 namedata typedb fielddescription
tcBankStateNumbercharacterBankState.BankStateNumberBank Statement No
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.
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.
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.
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.
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.
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.
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.
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.
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.
tcCreditorCodecharacterCreditor.CreditorCodeSupplier Code
tiCurrency_IDintegerCurrency.Currency_IDRecord ID
tcCurrencyCodecharacterCurrency.CurrencyCodeCurrency Code
tcCurrencyDescriptioncharacterCurrency.CurrencyDescriptionCurrency Description
tiCurrencyNumberOfDecimalsintegerCurrency.CurrencyNumberOfDecimalsNumber of Decimals
tcJournalCodecharacterJournal.JournalCodeA daybook code (maximum eight characters).
tiPeriodPeriodintegerPeriod.PeriodPeriodGL Period
tiPeriodYearintegerPeriod.PeriodYearGL Calendar Year
tiPostingVoucherintegerPosting.PostingVoucherVoucher 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_sequenceintegercalculatedprimary index
tc_rowidcharactercalculated = 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