API query SelectReason (optimised)

Description

SelectReason; use this query in case you want to lookup a reason code; this query shoudl only be used by UI-components!!!!

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
tReason.ReasonAllocationStatus	c	=,>=,>,<>,<=,<,begins,can-do,matches
tReason2.ReasonCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tReason.ReasonCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tReason.ReasonDescription	c	=,>=,>,<>,<=,<,begins,can-do,matches
tReason.ReasonIsActive	l	=,<>
tReason.ReasonIsInitialStatus	l	=,<>
tReason.ReasonIsInvoiceApproved	l	=,<>
tReason.ReasonIsLockPayment	l	=,<>
tReason.ReasonIsLogisticMatching	l	=,<>

query condition

each Reason where

first Reason2 (conditional-join) where

Reason2.Reason_ID = Reason.MatchingReason_ID AND

query sort order

Reason.ReasonCode (ascending)

query resultset tqSelectReason

field name	data type	db field	description
tlReasonIsInitialStatus	logical	Reason.ReasonIsInitialStatus	Initial Status
tcReasonCode	character	Reason.ReasonCode	Invoice Status Code
tlReasonIsActive	logical	Reason.ReasonIsActive	Active
tlReasonIsLogisticMatching	logical	Reason.ReasonIsLogisticMatching	Receiver Matching
tlReasonIsLockPayment	logical	Reason.ReasonIsLockPayment	Lock Payment
tlReasonIsInvoiceApproved	logical	Reason.ReasonIsInvoiceApproved	Invoice Approved
tcReasonDescription	character	Reason.ReasonDescription	Description
tcReasonAllocationStatusCalc	character	calculated
tcReasonCodeAfterMatching	character	Reason2.ReasonCode	Invoice Status Code
tcReasonAllocationStatus	character	Reason.ReasonAllocationStatus	Allocation Status
tiMatchingReason_ID	integer	Reason.MatchingReason_ID	Status After Match
tiReason_ID	integer	Reason.Reason_ID	Record ID
ReasonCustom	character	calculated	custom fields
Reason2Custom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(Reason),rowid(Reason2)

Internal usage

QadFinancials

lookup query on tReason.ReasonCode
lookup query on tAPMatching.tcReasonCode
lookup query on tCInvoice.tcReasonCode
lookup query on tCreditor.tcReasonCode
lookup query on tDebtor.tcReasonCode
lookup query on tDInvoice.tcReasonCode
lookup query on tReason.tcMatchingReasonCode

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 = "BReason".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "SelectReason".
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/breason.selectreason.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.