API query DPaySelbyDInvoice

Description

Select a DPaySel record by invoice/10 digit code/bank reference and shipper (nota fiscal)

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i
tDCollection.DCollectionIsExecuted	l	=,<>
tDInvoice.DInvoiceDueDate	t	=,>=,>,<>,<=,<
tDInvoiceOpInfo.DInvoiceOpInfoCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDInvoice.DInvoicePostingPeriod	i	=,>=,>,<>,<=,<
tDInvoice.DInvoicePostingYear	i	=,>=,>,<>,<=,<
tDInvoiceStage.DInvoiceStageDueDate	t	=,>=,>,<>,<=,<
tDInvoice.DInvoiceVoucher	i	=,>=,>,<>,<=,<
tDPaySel.DPaySelCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDPaySel.DPaySelDate	t	=,>=,>,<>,<=,<
tDPaySelPayRef.DPaySelPayRefBankRef	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDPaySelPayRef.DPaySelPayRefCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDPaySel.DPaySelStatus	c	=,>=,>,<>,<=,<,begins,can-do,matches
tJournal.JournalCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tBankPayFormat.PayFormatTypeCode	c	=,>=,>,<>,<=,<,begins,can-do,matches

Parameters (internal)

iiCompanyId

integer

Company id

query condition

each DPaySel where

DPaySel.Company_Id = iiCompanyId AND

first BankPayFormat (inner-join) where

BankPayFormat.BankPayFormat_ID = DPaySel.BankPayFormat_ID AND

each DCollection (conditional-join) where

DCollection.Company_Id = iiCompanyId AND
DCollection.DPaySel_ID = DPaySel.DPaySel_ID AND

each DPaySelLine (conditional-join) where

DPaySelLine.DPaySel_ID = DPaySel.DPaySel_ID AND

first DInvoice (outer-join) where

DInvoice.Company_Id = iiCompanyId AND
DInvoice.DInvoice_ID = DPaySelLine.DPaySelLineParentObject_ID AND

each DInvoiceOpInfo (conditional-join) where

DInvoiceOpInfo.DInvoice_ID = DInvoice.DInvoice_ID AND
DInvoiceOpInfo.DInvoiceOpInfoType = 'SHIPPER'

first Journal (outer-join) where

Journal.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal.Journal_ID = DInvoice.Journal_ID AND

each DPaySelPayRef (conditional-join) where

DPaySelPayRef.DPaySelLine_ID = DPaySelLine.DPaySelLine_ID AND

first DPaySelLineStg (conditional-join) where

DPaySelLineStg.DPaySelLineStg_ID = DPaySelPayRef.DPaySelLineStg_ID AND

first DInvoiceStage (conditional-join) where

DInvoiceStage.DInvoiceStage_ID = DPaySelLineStg.DInvoiceStage_ID AND

query resultset tqDPaySelByDInvoice

field name	data type	db field	description
tiBankPayFormat_ID	integer	DPaySel.BankPayFormat_ID	Link to BankPayFormat
tlDCollectionIsExecuted	logical	DCollection.DCollectionIsExecuted	Executed
ttDInvoiceDueDate	date	DInvoice.DInvoiceDueDate	Due Date. This field displays the date when payment is due, calculated by the system based on the credit terms and the invoice date. User can modify the due date without affecting the credit terms. If the credit terms have a base date specified, this is used in the due date calculations rather than the invoice creation date.
tcDInvoiceOpInfoCode	character	DInvoiceOpInfo.DInvoiceOpInfoCode	Identifier. This field identifies an opinfo record.
tiDInvoicePostingPeriod	integer	DInvoice.DInvoicePostingPeriod	GL Period.This field indicates the period when the invoice to be posted. If this field is modified, the posting and tax dates are changed correspondingly.
	api annotation:		This field defaults to the period associated with the posting date.
tiDInvoicePostingYear	integer	DInvoice.DInvoicePostingYear	Year.This field indicates the accounting year for the invoice. If you modify these fields, the posting and tax dates are changed correspondingly.
	api annotation:		This field defaults to the accounting year associated with the posting date.
ttDInvoiceStageDueDate	date	DInvoiceStage.DInvoiceStageDueDate	Due Date. The date when this stage should end.
tiDInvoiceVoucher	integer	DInvoice.DInvoiceVoucher	Voucher.This field displays the numeric identifier assigned to the posting. When the daybook of the journal entry is charged(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.
tiDPaySel_ID	integer	DPaySel.DPaySel_ID	Record ID
tcDPaySelCode	character	DPaySel.DPaySelCode	Payment Selection
ttDPaySelDate	date	DPaySel.DPaySelDate	Date
tcDPaySelPayRefBankRef	character	DPaySelPayRef.DPaySelPayRefBankRef	Bank Reference
tcDPaySelPayRefCode	character	DPaySelPayRef.DPaySelPayRefCode	Payment Reference Code
tcDPaySelStatus	character	DPaySel.DPaySelStatus	Status
tcDPaySelStatusTR	character	calculated
tiGL_ID	integer	DPaySel.GL_ID	Link to GL
tiJournal_ID	integer	DInvoice.Journal_ID	Daybook Code
tcJournalCode	character	Journal.JournalCode	A daybook code (maximum eight characters).
tcPayFormatTypeCode	character	BankPayFormat.PayFormatTypeCode	Payment Format
DPaySelCustom	character	calculated	custom fields
DPaySelPayRefCustom	character	calculated	custom fields
DInvoiceCustom	character	calculated	custom fields
DInvoiceOpInfoCustom	character	calculated	custom fields
JournalCustom	character	calculated	custom fields
BankPayFormatCustom	character	calculated	custom fields
DCollectionCustom	character	calculated	custom fields
DInvoiceStageCustom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(DPaySel),rowid(BankPayFormat),rowid(DCollection),rowid(DPaySelLine),rowid(DInvoice),rowid(DInvoiceOpInfo),rowid(Journal),rowid(DPaySelPayRef),rowid(DPaySelLineStg),rowid(DInvoiceStage)

Internal usage

unused

Sample code: how to call this query through RPCRequestService (QXtend Inbound)

define temp-table ttContext no-undo
    field propertyQualifier as character
    field propertyName as character
    field propertyValue as character
    index entityContext is primary unique
        propertyQualifier
        propertyName
    index propertyQualifier
        propertyQualifier.

define dataset dsContext for ttContext.

define variable vhContextDS as handle no-undo.
define variable vhExceptionDS as handle no-undo.
define variable vhServer as handle no-undo.
define variable vhInputDS as handle no-undo.
define variable vhInputOutputDS as handle no-undo.
define variable vhOutputDS as handle no-undo.
define variable vhParameter as handle no-undo.

/* Create context */
create ttContext.
assign ttContext.propertyName = "programName"
       ttContext.propertyValue = "BDPaymentSelection".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "DPaySelByDInvoice".
create ttContext.
assign ttContext.propertyName = "applicationId"
       ttContext.propertyValue = "fin".
create ttContext.
assign ttContext.propertyName = "entity"
       ttContext.propertyValue = "1000".
create ttContext.
assign ttContext.propertyName = "userName"
       ttContext.propertyValue = "mfg".
create ttContext.
assign ttContext.propertyName = "password"
       ttContext.propertyValue = "".

/* Create input dataset */
create dataset vhInputDS.
vhInputDS:read-xmlschema("file", "xml/bdpaymentselection.dpayselbydinvoice.i.xsd", ?).
vhParameter = vhInputDS:get-buffer-handle("tParameterI").
vhParameter:buffer-create().

assign vhParameter::icRange = "A"
       vhParameter::icRowid = ""
       vhParameter::iiRownum = 0
       vhParameter::iiNumber = 5 /* Number of records to read */
       vhParameter::icSortColumns = ""
       vhParameter::ilCountOnly = false
       vhParameter::ilForwardRead = true
       vhParameter::iiMaximumBrowseRecordsToCount = 0.

vhParameter = vhInputDS:get-buffer-handle("tFilter").
vhParameter:buffer-create().

assign vhParameter::<field-name-1> = <field-value-1>
       vhParameter::<field-name-2> = <field-value-2>
       ...

/* Connect the AppServer */
create server vhServer.
vhServer:connect("-URL <appserver-url>").

if not vhServer:connected()
then do:
    message "Could not connect AppServer" view-as alert-box error title "Error".
    return.
end.

/* Run */
assign vhContextDS = dataset dsContext:handle.

run program/rpcrequestservice.p on vhServer
    (input-output dataset-handle vhContextDS by-reference,
           output dataset-handle vhExceptionDS,
     input        dataset-handle vhInputDS by-reference,
     input-output dataset-handle vhInputOutputDS by-reference,
           output dataset-handle vhOutputDS).

/* Handle output however you want, in this example, we dump it to xml */
if valid-handle(vhExceptionDS)
then vhExceptionDS:write-xml("file", "Exceptions.xml", true).

if valid-handle(vhOutputDS)
then vhOutputDS:write-xml("file", "Output.xml", true).

/* Cleanup */
vhServer:disconnect().
assign vhServer = ?.

if valid-handle(vhInputDS)
then delete object vhInputDS.

if valid-handle(vhOutputDS)
then delete object vhOutputDS.

if valid-handle(vhExceptionDS)
then delete object vhExceptionDS.