business logic query BasePayListPrepay

Parameters

iiCompanyId	integer	Company id
iiAddressType_ID	integer
icCreditorCodes	character
itFromExecDate	date
itTillExecDate	date
icFromPaySel	character
icToPaySel	character
icStaus	character
icPaySel	character
itExecDate	date
iiCountryID	integer
icBusinessRelationName	character
icFromBusinessRelationName	character
icToBusinessRelationName	character

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 (conditional-join) where

BankNumber2.BankNumber_ID = PaySelLine.BankNumber_ID AND

first PaySelPrepay (inner-join) where

PaySelPrepay.PaySelPrepay_ID = PaySelLine.PaySelLineParentObject_ID AND

first Creditor (inner-join) where

Creditor.SharedSet_Id = vi_CREDITOR_sharedset(iiCompanyId) AND
Creditor.Creditor_ID = PaySelPrepay.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

each VatNumber (outer-join) where

VatNumber.BusinessRelation_ID = BusinessRelation.BusinessRelation_ID AND
VatNumber.IdentityCountry_ID = iiCountryID

first Currency (inner-join) where

Currency.Currency_ID = PaySelPrepay.Currency_ID AND

query resultset tqBasePayListPrepay

field name	data type	db field	description
tcAddressCity	character	Address.AddressCity	City. The city for this address. This field can be used to decide the tax zone of the address.
tcAddressStreet1	character	Address.AddressStreet1	Address: Line one of address details. This field can be blank.
tcAddressStreet2	character	Address.AddressStreet2	Address.Line two of address details. This field can be blank.
tcAddressZip	character	Address.AddressZip	Zip. 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.
tcBankNumber	character	BankNumber2.BankNumber	Bank Number
tiBankNumber_ID	integer	BankNumber2.BankNumber_ID	Record ID
tcBankNumberExtension	character	BankNumber2.BankNumberExtension	Bank Extension
tcBankNumberFormatted	character	BankNumber2.BankNumberFormatted	Formatted Bank Account No
tcBankNumberSwiftCode	character	BankNumber2.BankNumberSwiftCode	SWIFT Code
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.
tiCompany_ID	integer	PaySel.Company_ID	Link to Company
tcCompanyBankNumber	character	BankNumber1.BankNumber	Bank Number
tcCompanyBankNumberExtension	character	BankNumber1.BankNumberExtension	Bank Extension
tcCompanyBankNumberFormatted	character	BankNumber1.BankNumberFormatted	Formatted Bank Account No
tcCompanyBankNumberSwiftCode	character	BankNumber1.BankNumberSwiftCode	SWIFT Code
tiCountry_ID	integer	Country.Country_ID	Record ID
tcCountryCode	character	Country.CountryCode	Country Code
tcCountryDescription	character	Country.CountryDescription	Description
tcCreditorCode	character	Creditor.CreditorCode	Supplier Code
tcCurrencyCode	character	Currency.CurrencyCode	Currency Code
tiCurrencyNumberOfDecimals	integer	Currency.CurrencyNumberOfDecimals	Number of Decimals
tcGLCode	character	GL.GLCode	A code identifying the GL account.
tiIdentityCountry_ID	integer	VatNumber.IdentityCountry_ID	Country Tax Identification
tcPayFormatTypeCode	character	BankPayFormat.PayFormatTypeCode	Payment Format
tcPayFormatTypeCode2	character	PaySelLine.PayFormatTypeCode	Payment format of the payment selection
tiPaySel_ID	integer	PaySel.PaySel_ID	Record ID
tcPaySelCode	character	PaySel.PaySelCode	Code
ttPaySelDate	date	PaySel.PaySelDate	Execution Date
tiPaySelLine_ID	integer	PaySelLine.PaySelLine_ID	Record ID
tdPaySelLineAmountTC	decimal	PaySelLine.PaySelLineAmountTC	Amount
tdPaySelLineDiscountTC	decimal	PaySelLine.PaySelLineDiscountTC	Discount
tlPaySelLineIsRefInFile	logical	PaySelLine.PaySelLineIsRefInFile	Ref in File
tcPaySelLineObjectType	character	PaySelLine.PaySelLineObjectType	Type
tdPaySelLineWHTAmtCC	decimal	PaySelLine.PaySelLineWHTAmtCC	SC WHT Amount
tdPaySelLineWHTAmtLC	decimal	PaySelLine.PaySelLineWHTAmtLC	BC WHT Amount
tdPaySelLineWHTAmtTC	decimal	PaySelLine.PaySelLineWHTAmtTC	TC WHT Amount
tdPaySelPrepayAmountToPayTC	decimal	PaySelPrepay.PaySelPrepayAmountToPayTC	Amount to Pay
tcPaySelPrepayReferenceText	character	PaySelPrepay.PaySelPrepayReferenceText	Reference
tcPaySelStatus	character	PaySel.PaySelStatus	Status
tcVatNumberIdentity	character	VatNumber.VatNumberIdentity	Obsolete field
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(PaySel),rowid(BankPayFormat),rowid(BankNumber1),rowid(GL),rowid(PaySelLine),rowid(BankNumber2),rowid(PaySelPrepay),rowid(Creditor),rowid(BusinessRelation),rowid(Address),rowid(Country),rowid(VatNumber),rowid(Currency)

Internal usage

QadFinancials

method BPaymentSelectionReport.Paylist