project QadFinancials > class BPaymentSelectionReport > business logic query BasePayListCreditor


Parameters


iiCompanyIdintegerCompany id
iiAddressType_IDinteger
icCreditorCodescharacter
itFromExecDatedate
itTillExecDatedate
icFromPaySelcharacter
icToPaySelcharacter
icStauscharacter
icPaySelcharacter
itExecDatedate
icBusinessRelationNamecharacter
icFromBusinessRelationNamecharacter
icToBusinessRelationNamecharacter


query condition


  each PaySel where
PaySel.Company_Id = iiCompanyId AND
PaySel.PaySelDate >= itFromExecDate AND
PaySel.PaySelDate <= itTillExecDate AND
PaySel.PaySelCode >= icFromPaySel AND
PaySel.PaySelCode <= icToPaySel AND
PaySel.PaySelStatus = icStaus AND
PaySel.PaySelDate = itExecDate AND
PaySel.PaySelCode = icPaySel

      first BankPayFormat (inner-join) where
BankPayFormat.BankPayFormat_ID = PaySel.BankPayFormat_ID AND

          first BankNumber1 (inner-join) where
BankNumber1.BankNumber_ID = BankPayFormat.BankNumber_ID AND

      first GL (inner-join) where
GL.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL.GL_ID = PaySel.GL_ID AND

      each PaySelLine (inner-join) where
PaySelLine.PaySel_ID = PaySel.PaySel_ID AND

          first BankNumber2 (inner-join) where
BankNumber2.BankNumber_ID = PaySelLine.BankNumber_ID AND

          first CInvoice (inner-join) where
CInvoice.Company_Id = iiCompanyId AND
CInvoice.CInvoice_ID = PaySelLine.PaySelLineParentObject_ID AND
CInvoice.Company_Id <> 0 AND
CInvoice.Company_Id <> ?

              first Creditor (inner-join) where
Creditor.SharedSet_Id = vi_CREDITOR_sharedset(iiCompanyId) AND
Creditor.Creditor_ID = CInvoice.Creditor_ID AND
Creditor.CreditorCode can-do icCreditorCodes

                  first BusinessRelation (inner-join) where
BusinessRelation.BusinessRelation_ID = Creditor.BusinessRelation_ID AND
BusinessRelation.BusinessRelationName1 can-do icBusinessRelationName AND
BusinessRelation.BusinessRelationName1 >= icFromBusinessRelationName AND
BusinessRelation.BusinessRelationName1 <= icToBusinessRelationName

                      each Address (outer-join) where
Address.BusinessRelation_ID = BusinessRelation.BusinessRelation_ID AND
Address.AddressType_ID = iiAddressType_ID

                          first Country (outer-join) where
Country.Country_ID = Address.Country_ID AND

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

              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
Period.Company_ID <> 0 AND
Period.Company_ID <> ?


query resultset tqBasePayListCreditor


field namedata typedb fielddescription
tiBankNumber_IDintegerBankNumber2.BankNumber_IDRecord ID
tdCInvoiceWHTTotAmtCreditCCdecimalCInvoice.CInvoiceWHTTotAmtCreditCCSC WHT Total Amount (CR)
tdCInvoiceWHTTotAmtCreditLCdecimalCInvoice.CInvoiceWHTTotAmtCreditLCBC WHT Total Amount (CR)
tdCInvoiceWHTTotAmtCreditTCdecimalCInvoice.CInvoiceWHTTotAmtCreditTCTC WHT Total Amount (CR)
tdCInvoiceWHTTotAmtDebitCCdecimalCInvoice.CInvoiceWHTTotAmtDebitCCSC WHT Total Amount (DR)
tdCInvoiceWHTTotAmtDebitLCdecimalCInvoice.CInvoiceWHTTotAmtDebitLCBC WHT Total Amount (DR)
tdCInvoiceWHTTotAmtDebitTCdecimalCInvoice.CInvoiceWHTTotAmtDebitTCTC WHT Total Amount (DR)
tcCompanyBankNumbercharacterBankNumber1.BankNumberBank Number
tcCompanyBankNumberExtensioncharacterBankNumber1.BankNumberExtensionBank Extension
tcCompanyBankNumberFormattedcharacterBankNumber1.BankNumberFormattedFormatted Bank Account No
tcCompanyBankNumberSwiftCodecharacterBankNumber1.BankNumberSwiftCodeSWIFT Code
tiCountry_IDintegerCountry.Country_IDRecord ID
ttInvoiceDueDatedateCInvoice.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.
tiJournal_IDintegerJournal.Journal_IDRecord ID
tdPaySelLineWHTAmtCCdecimalPaySelLine.PaySelLineWHTAmtCCSC WHT Amount
tdPaySelLineWHTAmtLCdecimalPaySelLine.PaySelLineWHTAmtLCBC WHT Amount
tdPaySelLineWHTAmtTCdecimalPaySelLine.PaySelLineWHTAmtTCTC WHT Amount
tcPayFormatTypeCodecharacterBankPayFormat.PayFormatTypeCodePayment Format
tdPaySelLineDiscountTCdecimalPaySelLine.PaySelLineDiscountTCDiscount
tlPaySelLineIsRefInFilelogicalPaySelLine.PaySelLineIsRefInFileRef in File
tcPaySelLineObjectTypecharacterPaySelLine.PaySelLineObjectTypeType
tiPaySelLine_IDintegerPaySelLine.PaySelLine_IDRecord ID
tcBankNumbercharacterBankNumber2.BankNumberBank Number
tcBankNumberExtensioncharacterBankNumber2.BankNumberExtensionBank Extension
tcBankNumberFormattedcharacterBankNumber2.BankNumberFormattedFormatted Bank Account No
tcBankNumberSwiftCodecharacterBankNumber2.BankNumberSwiftCodeSWIFT Code
tdInvoiceBalanceCreditCCdecimalCInvoice.CInvoiceBalanceCreditCCBC Balance Amount CR. This field displays the sum of the credit amounts of all posting lines in transaction currency.
This field is calculated by the system using the TC Base Amount CR and the SC rate.
tdInvoiceBalanceCreditLCdecimalCInvoice.CInvoiceBalanceCreditLCBC Balance Amount CR. This field displays the sum of the credit amounts of all posting lines in transaction currency.
This field is calculated by the system using the TC Base Amount CR and the Exchange rate.
tdInvoiceBalanceCreditTCdecimalCInvoice.CInvoiceBalanceCreditTCTC Balance Amount CR. This field displays the sum of the credit amounts of all posting lines in transaction currency.
tdInvoiceBalanceDebitCCdecimalCInvoice.CInvoiceBalanceDebitCCSC Balance Amount DR. This field displays the sum of the debit amounts of all posting lines in statutory currency.
This field is calculated by the system using the TC Base Amount DR and the SC rate.
tdInvoiceBalanceDebitLCdecimalCInvoice.CInvoiceBalanceDebitLCBC Balance Amount DR. This field displays the sum of the debit amounts of all posting lines in transaction currency.
This field is calculated by the system using the TC Base Amount DR and the Exchange rate.
tdInvoiceBalanceDebitTCdecimalCInvoice.CInvoiceBalanceDebitTCTC Balance Amount DR. This field displays the sum of the debit amounts of all posting lines in transaction currency.
tcInvoiceContactcharacterCInvoice.CInvoiceContactContact. This field indicates the name of the purchasing contact in the supplier company.
api annotation:This field defaults from the Contact field of the invoice's supplier.
ttInvoiceDatedateCInvoice.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.
tcInvoiceDescriptioncharacterCInvoice.CInvoiceDescriptionDescription. A brief description of the invoice. The system generates a default description based on the Reference and Supplier Code.
tdInvoiceOriginalCreditCCdecimalCInvoice.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.
tdInvoiceOriginalCreditLCdecimalCInvoice.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.
tdInvoiceOriginalCreditTCdecimalCInvoice.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.
tdInvoiceOriginalDebitCCdecimalCInvoice.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.
tdInvoiceOriginalDebitLCdecimalCInvoice.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.
tdInvoiceOriginalDebitTCdecimalCInvoice.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.
tcInvoiceReferencecharacterCInvoice.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.
tcInvoiceTypecharacterCInvoice.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.
tiInvoiceVoucherintegerCInvoice.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.
tiPaySel_IDintegerPaySel.PaySel_IDRecord ID
tcCurrencyCodecharacterCurrency.CurrencyCodeCurrency Code
tiCurrencyNumberOfDecimalsintegerCurrency.CurrencyNumberOfDecimalsNumber of Decimals
tcJournalCodecharacterJournal.JournalCodeA daybook code (maximum eight characters).
tcJournalDescriptioncharacterJournal.JournalDescriptionA brief description of the daybook.
api annotation:Value for this field defaults to null.
tiPeriodPeriodintegerPeriod.PeriodPeriodGL Period
tiPeriodYearintegerPeriod.PeriodYearGL Calendar Year
tcCreditorCodecharacterCreditor.CreditorCodeSupplier Code
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.
tcAddressCitycharacterAddress.AddressCityCity. The city for this address. This field can be used to decide the tax zone of the address.
tcAddressStreet1characterAddress.AddressStreet1Address: Line one of address details.
This field can be blank.
tcAddressStreet2characterAddress.AddressStreet2Address.Line two of address details.
This field can be blank.
tcAddressZipcharacterAddress.AddressZipZip. The postal code or US zip code associated with this address.
This field is used to decide the tax zone for the address.
This field can be blank.
tcCountryCodecharacterCountry.CountryCodeCountry Code
tcCountryDescriptioncharacterCountry.CountryDescriptionDescription
tiCInvoice_IDintegerCInvoice.CInvoice_IDRecord ID
tcPaySelCodecharacterPaySel.PaySelCodeCode
ttPaySelDatedatePaySel.PaySelDateExecution Date
tcPaySelStatuscharacterPaySel.PaySelStatusStatus
tiCompany_IDintegerPaySel.Company_IDLink to Company
tcGLCodecharacterGL.GLCodeA code identifying the GL account.
tcPayFormatTypeCode2characterPaySelLine.PayFormatTypeCodePayment format of the payment selection
tdPaySelLineAmountTCdecimalPaySelLine.PaySelLineAmountTCAmount
ti_sequenceintegercalculatedprimary index
tc_rowidcharactercalculated = rowid(PaySel),rowid(BankPayFormat),rowid(BankNumber1),rowid(GL),rowid(PaySelLine),rowid(BankNumber2),rowid(CInvoice),rowid(Creditor),rowid(BusinessRelation),rowid(Address),rowid(Country),rowid(Currency),rowid(Journal),rowid(Period)


Internal usage


QadFinancials
method BPaymentSelectionReport.Paylist