API query SelectPostingSaf (optimised)

Description

Returs Posting lines and header data

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i
tCostCentre.CostCentreCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency.CurrencyCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDivision.DivisionCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tGL.GLCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tJournal.JournalCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tJournal.JournalDescription	c	=,>=,>,<>,<=,<,begins,can-do,matches
tJournal.JournalTypeCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tLayer.LayerCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tPeriod.PeriodPeriod	i	=,>=,>,<>,<=,<
tPeriod.PeriodYear	i	=,>=,>,<>,<=,<
tPosting.PostingBusinessRelationTxt	c	=,>=,>,<>,<=,<,begins,can-do,matches
tPosting.PostingDate	t	=,>=,>,<>,<=,<
tPosting.PostingInvoiceReferenceTxt	c	=,>=,>,<>,<=,<,begins,can-do,matches
tPostingLine.PostingLineCreditCC	d	=,>=,>,<>,<=,<
tPostingLine.PostingLineCreditLC	d	=,>=,>,<>,<=,<
tPostingLine.PostingLineCreditTC	d	=,>=,>,<>,<=,<
tPostingLine.PostingLineDebitCC	d	=,>=,>,<>,<=,<
tPostingLine.PostingLineDebitLC	d	=,>=,>,<>,<=,<
tPostingLine.PostingLineDebitTC	d	=,>=,>,<>,<=,<
tPostingLine.PostingLineOriginLineNbr	i	=,>=,>,<>,<=,<
tPostingLine.PostingLineText	c	=,>=,>,<>,<=,<,begins,can-do,matches
tPosting.PostingOriginDaybookCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tPosting.PostingOriginDaybookNumber	c	=,>=,>,<>,<=,<,begins,can-do,matches
tPosting.PostingOriginDocument	c	=,>=,>,<>,<=,<,begins,can-do,matches
tPosting.PostingOriginDocumentType	c	=,>=,>,<>,<=,<,begins,can-do,matches
tPosting.PostingOriginIsExternal	l	=,<>
tPosting.PostingOriginReference	c	=,>=,>,<>,<=,<,begins,can-do,matches
tPosting.PostingOriginTransType	c	=,>=,>,<>,<=,<,begins,can-do,matches
tPostingSaf.PostingSafInputSequence	i	=,>=,>,<>,<=,<
tPostingSaf.PostingSafParentType	c	=,>=,>,<>,<=,<,begins,can-do,matches
tPosting.PostingSystemDate	t	=,>=,>,<>,<=,<
tPosting.PostingText	c	=,>=,>,<>,<=,<,begins,can-do,matches
tPosting.PostingVoucher	i	=,>=,>,<>,<=,<
tProject.ProjectCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tSaf.SafCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tSafConcept.SafConceptCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tSafStructure.SafStructureCode	c	=,>=,>,<>,<=,<,begins,can-do,matches

Parameters (internal)

iiCompanyId

integer

Company id

query condition

each PostingLine where

PostingLine.Company_Id = iiCompanyId AND

first CostCentre (conditional-join) where

CostCentre.SharedSet_Id = vi_COSTCENTRE_sharedset(iiCompanyId) AND
CostCentre.CostCentre_ID = PostingLine.CostCentre_ID AND

first Currency (inner-join) where

Currency.Currency_ID = PostingLine.Currency_ID AND

first Division (conditional-join) where

Division.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division.Division_ID = PostingLine.Division_ID AND

first GL (inner-join) where

GL.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL.GL_ID = PostingLine.GL_ID AND

first Posting (inner-join) where

Posting.Company_Id = iiCompanyId AND
Posting.Posting_ID = PostingLine.Posting_ID AND

first Journal (inner-join) where

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

first Layer (inner-join) where

Layer.Layer_ID = Journal.Layer_ID AND

first Period (inner-join) where

Period.Company_Id = iiCompanyId AND
Period.Period_ID = Posting.Period_ID AND

each PostingSaf (inner-join) where

PostingSaf.PostingLine_ID = PostingLine.PostingLine_ID AND

first Saf (inner-join) where

Saf.Saf_ID = PostingSaf.Saf_ID AND

first SafConcept (inner-join) where

SafConcept.SafConcept_ID = Saf.SafConcept_ID AND

first SafStructure (inner-join) where

SafStructure.SafStructure_ID = PostingSaf.SafStructure_ID AND

first Project (conditional-join) where

Project.SharedSet_Id = vi_PROJECT_sharedset(iiCompanyId) AND
Project.Project_ID = PostingLine.Project_ID AND

query sort order

Period.PeriodYear (ascending)
Period.PeriodPeriod (ascending)
Journal.JournalCode (ascending)
Posting.PostingVoucher (ascending)
GL.GLCode (ascending)

query resultset tqSelectPostingSaf

field name	data type	db field	description
tiJournal_ID	integer	Journal.Journal_ID	Record ID
tcPostingSafParentType	character	PostingSaf.PostingSafParentType	Parent Type. Indicates the parent type of this SAF record.
tiPostingSafInputSequence	integer	PostingSaf.PostingSafInputSequence	Sequence number of this SAF within all the SAF lines for the same posting line.
tiPostingSaf_ID	integer	PostingSaf.PostingSaf_ID	Record ID
tcSafConceptCode	character	SafConcept.SafConceptCode	SAF Concept Code
tiSafConcept_ID	integer	Saf.SafConcept_ID	SAF Concept Code
tcSafCode	character	Saf.SafCode	SAF Code
tcSafStructureCode	character	SafStructure.SafStructureCode	Structure Code
tiSaf_ID	integer	PostingSaf.Saf_ID	SAF Code
tiSafStructure_ID	integer	PostingSaf.SafStructure_ID	Structure Code
tiPeriodYear	integer	Period.PeriodYear	GL Calendar Year
tiPeriodPeriod	integer	Period.PeriodPeriod	GL Period
tcJournalCode	character	Journal.JournalCode	A daybook code (maximum eight characters).
tiPostingVoucher	integer	Posting.PostingVoucher	Voucher is a sequential number that uniquely identifies the journal entry (within the combination of an entity and a daybook code).
	api annotation:		In create mode,enter 0 in this field - the voucher number is generated by the system. In Modify/Delete, the correct voucher number must be entered because this identifies the record.
tcGLCode	character	GL.GLCode	A code identifying the GL account.
tcDivisionCode	character	Division.DivisionCode	Sub-Account
tdPostingLineCreditLC	decimal	PostingLine.PostingLineCreditLC	BC Credit
	api annotation:		Partial Update = yes - it will get a default value depends on the value of PostingLineCreditTC, PostingLineExchangeRate & PostingLineRateScale. BC Amount = TC Amount * Exchange rate (TC/BC) * Scale Factor (TC/BC). If Partial Update is false, enter a value for this field.
tdPostingLineDebitLC	decimal	PostingLine.PostingLineDebitLC	BC Debit
	api annotation:		Partial Update = yes - it will get a default value depending on the value of PostingLineDebitTC, PostingLineExchangeRate and PostingLineRateScale. BC Amount = TC Amount * Exchange rate (TC/BC) * Scale Factor (TC/BC). If Partial Update = false, enter the correct value into this field.
tcPostingLineText	character	PostingLine.PostingLineText	Description
	api annotation:		Value for this field defaults to blank. Note: if a new record is created through the user interface, the default value is as same as the value of "PostingText" of tPosting.
ttPostingDate	date	Posting.PostingDate	The accounting date on which the journal entry is posted. The posting date must be a valid calendar date and must be within the GL calendar year (PostingYear) and period (PostingPeriod).
ttPostingSystemDate	date	Posting.PostingSystemDate	The system date of the posting corresponding to "PostingDate".
	api annotation:		In Create mode, when Partial Update = yes - this field gets a default value which is the current date of the system.
tcPostingInvoiceReferenceTxt	character	Posting.PostingInvoiceReferenceTxt	When the posting is part of an invoice posting, then this field contains the invoice number (Year/Daybook/Voucher extended with supplier invoice Reference in case of a supplier invoice). Used for information purpose on reports and browsers.
	api annotation:		Value for this field defaults to blank
tcPostingText	character	Posting.PostingText	Free text describing the posting.
	api annotation:		Value for this field defaults to blank
tcProjectCode	character	Project.ProjectCode	Project
tcCostCentreCode	character	CostCentre.CostCentreCode	Cost Center: A code that identifies the cost center.
tcCurrencyCode	character	Currency.CurrencyCode	Currency Code
tdPostingLineCreditTC	decimal	PostingLine.PostingLineCreditTC	TC Credit
tdPostingLineDebitTC	decimal	PostingLine.PostingLineDebitTC	TC Debit
tdPostingLineCreditCC	decimal	PostingLine.PostingLineCreditCC	SC Credit
	api annotation:		Partial Update = yes - it will get a default value depending on the value of PostingLinCreditTC, PostingLineCCRate & PostingLineCCScale. SC Amount = TC Amount * Exchange rate (TC/SC) * Scale Factor (TC/SC). If Partial Update is false, you should enter the correct value for this field
tdPostingLineDebitCC	decimal	PostingLine.PostingLineDebitCC	SC Debit
	api annotation:		Partial Update = yes - it will get a default value depending on the value of PostingLineDebitTC, PostingLineCCRate & PostingLineCCScale. SC Amount = TC Amount * Exchange rate (TC/SC) * Scale Factor (TC/SC). If Partial Update is false, enter the correct value for this field.
tcLayerCode	character	Layer.LayerCode	Layer Code
tcJournalTypeCode	character	Journal.JournalTypeCode	A daybook type code.
	api annotation:		Value for this field defaults to "Journal Entries".
tcJournalDescription	character	Journal.JournalDescription	A brief description of the daybook.
	api annotation:		Value for this field defaults to null.
tiCurrency_ID	integer	PostingLine.Currency_ID	Currency Code
tiDivision_ID	integer	PostingLine.Division_ID	Sub-Account Code
tiGL_ID	integer	PostingLine.GL_ID	GL Description
tiCompany_ID	integer	Posting.Company_ID	Link to Company
tiPeriod_ID	integer	Posting.Period_ID	Link to Period
tiPostingLine_ID	integer	PostingLine.PostingLine_ID	Record ID
tiPosting_ID	integer	PostingLine.Posting_ID	Link to Posting
tiClosing_ID	integer	Posting.ObsoleteClosing_ID	Link to Closing
tiProject_ID	integer	PostingLine.Project_ID	Description
tiCostCentre_ID	integer	PostingLine.CostCentre_ID	Cost Center Code
tcPostingBusinessRelationTxt	character	Posting.PostingBusinessRelationTxt	When the posting is a transaction linked to a business relation (for example, an invoice posting), this field contains the Business Relation Name. Used for information purposes on reports and browses.
	api annotation:		Value for this field defaults to blank.
tlPostingOriginIsExternal	logical	Posting.PostingOriginIsExternal	Indicate whether the posting is from an External Origin. When creating cross company postings, set this to true.
	api annotation:		Value for this field defaults to false.
tiPostingLineOriginLineNbr	integer	PostingLine.PostingLineOriginLineNbr	Equivalent to PostingLineSequence, however there is a difference in format and the value may differ in a number of specific cases. MfgPro Equivalent: glt_line
	api annotation:		Value for this field defaults to 0.
tcPostingOriginReference	character	Posting.PostingOriginReference	GL Reference Number. Format is ZZYYMMDD999999 where ZZ = module (IC, SO, WO) YY = Year, MM = Month, DD = Day, 999999 = Running Journal Number. MfgPro equivalent: glt_ref
tcPostingOriginDocument	character	Posting.PostingOriginDocument	Transaction History Number (Inventory or Operations) or Invoice Number (Sales Order) MfgPro equivalent: glt_doc
tcPostingOriginDocumentType	character	Posting.PostingOriginDocumentType	Document Type (I for Invoice, OP for Work Orders) MfgPro equivalent: glt_doc_type
tcPostingOriginTransType	character	Posting.PostingOriginTransType	System generated code used to identify a transaction (IC for Inventory, WO for Work Orders, SO for Sales Orders, FA for Fixed Assets) MfgPro equivalent: glt_tr_type
tcPostingOriginDaybookCode	character	Posting.PostingOriginDaybookCode	Daybook Code (normally this corresponds with our JournalCode) MfgPro equivalent: glt_dy_code
tcPostingOriginDaybookNumber	character	Posting.PostingOriginDaybookNumber	Daybook Number (normally this corresponds with our PostingVoucher) MfgPro equivalent: glt_dy_num
PostingLineCustom	character	calculated	custom fields
CostCentreCustom	character	calculated	custom fields
CurrencyCustom	character	calculated	custom fields
DivisionCustom	character	calculated	custom fields
GLCustom	character	calculated	custom fields
PostingCustom	character	calculated	custom fields
JournalCustom	character	calculated	custom fields
LayerCustom	character	calculated	custom fields
PeriodCustom	character	calculated	custom fields
PostingSafCustom	character	calculated	custom fields
SafCustom	character	calculated	custom fields
SafConceptCustom	character	calculated	custom fields
SafStructureCustom	character	calculated	custom fields
ProjectCustom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(PostingLine),rowid(CostCentre),rowid(Currency),rowid(Division),rowid(GL),rowid(Posting),rowid(Journal),rowid(Layer),rowid(Period),rowid(PostingSaf),rowid(Saf),rowid(SafConcept),rowid(SafStructure),rowid(Project)

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 = "SelectPostingSaf".
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.selectpostingsaf.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.