project QadFinancials > class BDDocument > business logic query DDocumentForBankEntry

Description

DDocumentCollByAllocationInfo
Use inner join with dcollection because the documents i needed (incasso or disconto) are always in a dcollection

Parameters


iiCompanyIdintegerCompany id
iiDDocumentYearintegerDDocument Year
icDDocumentTypecharacterDDocument Type
iiDDocumentNumberintegerDDocument Number
itFromRequestedDatedatefrom requested date
idTotalCollectionAmountdecimalTotal Collection Amount
icGLCodecharacterGL code
icDDocumentStatuscharacterDDocument Status
icDocumentReferencecharacter
icIncSelCodecharacter

query condition


  each DDocument where
DDocument.Company_Id = iiCompanyId AND
DDocument.DDocumentYear = iiDDocumentYear AND
DDocument.DDocumentType = icDDocumentType AND
DDocument.DDocumentNumber = iiDDocumentNumber AND
DDocument.DDocumentStatus can-do icDDocumentStatus AND
DDocument.DDocumentReference matches icDocumentReference

      first Currency1 (inner-join) where
Currency1.Currency_ID = DDocument.Currency_ID AND

      first DCollection (inner-join) where
DCollection.Company_Id = iiCompanyId AND
DCollection.DCollection_ID = DDocument.DCollection_ID AND
DCollection.DCollectionRequestedDate >= itFromRequestedDate AND
(DCollection.DCollectionBalanceDebitTC = idTotalCollectionAmount OR
DCollection.DCollectionBalanceCreditTC = idTotalCollectionAmount ) AND
DCollection.DCollectionIncSelCodeList matches icIncSelCode

      first Debtor (inner-join) where
Debtor.SharedSet_Id = vi_DEBTOR_sharedset(iiCompanyId) AND
Debtor.Debtor_ID = DDocument.Debtor_ID AND

          first BusinessRelation (inner-join) where
BusinessRelation.BusinessRelation_ID = Debtor.BusinessRelation_ID AND

      first GL (inner-join) where
GL.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL.GL_ID = DDocument.GL_ID AND
GL.GLCode = icGLCode

          first Currency2 (conditional-join) where
Currency2.Currency_ID = GL.Currency_ID AND

query resultset tqDDocumentForBankEntry


field namedata typedb fielddescription
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.
tiCompany_IDintegerDDocument.Company_IDLink to Company
tiCurrency_IDintegerDDocument.Currency_IDCurrency Code
tiDCollection_IDintegerDCollection.DCollection_IDRecord ID
tdDCollectionBalanceCreditTCdecimalDCollection.DCollectionBalanceCreditTCTC Credit Balance
tdDCollectionBalanceDebitTCdecimalDCollection.DCollectionBalanceDebitTCTC Debit Balance
tcDCollectionIncSelCodeListcharacterDCollection.DCollectionIncSelCodeListCustomer Payment Selection Code
tlDCollectionIsAutomaticlogicalDCollection.DCollectionIsAutomaticAutomatic
tlDCollectionIsExecutedlogicalDCollection.DCollectionIsExecutedExecuted
tiDCollectionNumberintegerDCollection.DCollectionNumberNumber
tcDCollectionReferencecharacterDCollection.DCollectionReference1099 Box Reference
ttDCollectionRequestedDatedateDCollection.DCollectionRequestedDateRequested Date
tiDCollectionYearintegerDCollection.DCollectionYearYear/Customer Payment Selection Code
tiDDocument_IDintegerDDocument.DDocument_IDRecord ID
tdDDocumentCCRatedecimalDDocument.DDocumentCCRateSC Rate. The statutory scaling factor applying to the transaction currency.
tdDDocumentCCScaledecimalDDocument.DDocumentCCScaleSC Rate Scale. The statutory exchange rate used by the invoice.
tdDDocumentExchangeRatedecimalDDocument.DDocumentExchangeRateExchange Rate. This field holds the exchange rate applying to the transaction currency.
tiDDocumentNumberintegerDDocument.DDocumentNumberDocument Number. This field hold a unified number for report use.
tdDDocumentOriginalDebitBCdecimalDDocument.DDocumentOriginalDebitBCBC Original Debit
tdDDocumentOriginalDebitLCdecimalDDocument.DDocumentOriginalDebitLCTC Original Debit. This field holds the debit value of the payment in base currency.
tdDDocumentOriginalDebitTCdecimalDDocument.DDocumentOriginalDebitTCAmount.This field hold the value of the payment in the transaction currency. This field must be positive and can be entered manually or automatically by linking the payment to an open item.
tdDDocumentRateScaledecimalDDocument.DDocumentRateScaleScale Factor. This field indicates the scaling factor applying to the transaction currency.
tcDDocumentReferencecharacterDDocument.DDocumentReferenceReference. Reference text for the payment.
tcDDocumentStatuscharacterDDocument.DDocumentStatusStatus. The payment status. The default value for a new record is Initial.
tcDDocumentSubTypecharacterDDocument.DDocumentSubTypeSubtype. This read-only field indicates that the payment is manual or automatic. You create manual customer payments through the Customer Payment activities, and automatic payments through the Customer Payment Selections.
tcDDocumentTypecharacterDDocument.DDocumentTypePayment Instrument. The system displays the payment instrument associated with the payment format linked to the customer bank.
tiDDocumentYearintegerDDocument.DDocumentYearYear/Number. This field displays the accounting year and payment sequence number, which is automatically generated by the accounting year.
tcDocumentCurrencyCodecharacterCurrency1.CurrencyCodeCurrency Code
tiGL_IDintegerDCollection.GL_IDGL Account
tcGLCodecharacterGL.GLCodeA code identifying the GL account.
tcGLCurrencyCodecharacterCurrency2.CurrencyCodeCurrency Code
ti_sequenceintegercalculatedprimary index
tc_rowidcharactercalculated = rowid(DDocument),rowid(Currency1),rowid(DCollection),rowid(Debtor),rowid(BusinessRelation),rowid(GL),rowid(Currency2)

Internal usage


QadFinancials
method BBankEntry.GetPossibleCollAllocationsDebtor