API query PostingLineByPostingIDLineID (optimised)

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i
iiPostingID	i	=
iiPostingLine_ID	i	=
tCInvoiceMovement.CInvoiceMovementType	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCInvoice.CInvoiceOriginalCreditTC	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceOriginalDebitTC	d	=,>=,>,<>,<=,<
tDInvoiceMovement.DInvoiceMovementType	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDInvoice.DInvoiceOriginalCreditTC	d	=,>=,>,<>,<=,<
tDInvoice.DInvoiceOriginalDebitTC	d	=,>=,>,<>,<=,<

Parameters (internal)

iiCompanyId	integer	Company id
iiPostingID	integer	PostingID
iiPostingLine_ID	integer	PostingLine_ID

query condition

each PostingLine where

PostingLine.Company_Id = iiCompanyId AND
PostingLine.Posting_ID = iiPostingID AND
PostingLine.PostingLine_ID = iiPostingLine_ID

each CInvoiceMovement (outer-join) where

CInvoiceMovement.Company_Id = iiCompanyId AND
CInvoiceMovement.PostingLine_ID = PostingLine.PostingLine_ID AND
CInvoiceMovement.CInvoiceMovementType = 'INITIAL'

first CInvoice (outer-join) where

CInvoice.Company_Id = iiCompanyId AND
CInvoice.CInvoice_ID = CInvoiceMovement.CInvoice_ID AND

each DInvoiceMovement (outer-join) where

DInvoiceMovement.Company_Id = iiCompanyId AND
DInvoiceMovement.PostingLine_ID = PostingLine.PostingLine_ID AND
DInvoiceMovement.DInvoiceMovementType = 'INITIAL'

first DInvoice (outer-join) where

DInvoice.Company_Id = iiCompanyId AND
DInvoice.DInvoice_ID = DInvoiceMovement.DInvoice_ID AND

query sort order

PostingLine.Posting_ID (ascending)
PostingLine.PostingLine_ID (ascending)

query resultset tqPostingLineByPostingIDLineID

field name	data type	db field	description
tcCInvoiceMovementType	character	CInvoiceMovement.CInvoiceMovementType	CInvoiceMovementType. "Initial" indicates that the movement represents the initial creation of the invoice, "movement" stands for all subsequent modifications of the invoice balance (payments, adjustments etc).
tdCInvoiceOriginalCreditTC	decimal	CInvoice.CInvoiceOriginalCreditTC	TC 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.
tdCInvoiceOriginalDebitTC	decimal	CInvoice.CInvoiceOriginalDebitTC	TC 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.
tcDInvoiceMovementType	character	DInvoiceMovement.DInvoiceMovementType	CInvoiceMovementType. "Initial" indicates that the movement represents the initial creation of the invoice, "movement" stands for all subsequent modifications of the invoice balance (payments, adjustments etc).
tdDInvoiceOriginalCreditTC	decimal	DInvoice.DInvoiceOriginalCreditTC	TC 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.
tdDInvoiceOriginalDebitTC	decimal	DInvoice.DInvoiceOriginalDebitTC	TC 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.
tiPosting_ID	integer	PostingLine.Posting_ID	Link to Posting
tiPostingLine_ID	integer	PostingLine.PostingLine_ID	Record ID
PostingLineCustom	character	calculated	custom fields
CInvoiceMovementCustom	character	calculated	custom fields
CInvoiceCustom	character	calculated	custom fields
DInvoiceMovementCustom	character	calculated	custom fields
DInvoiceCustom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(PostingLine),rowid(CInvoiceMovement),rowid(CInvoice),rowid(DInvoiceMovement),rowid(DInvoice)

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 = "BPosting".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "PostingLineByPostingIDLineID".
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/bposting.postinglinebypostingidlineid.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.