QAS_SAVE_QUERY_OPER

This service operation validates and verifies XML-format query and saves the query in the PeopleSoft database.

Request Message: QAS_QUERY_SAVE_REQ_MSG

This message has several groupings, and depending on the specific query, some groupings may appear multiple times, while other groupings do not apply. The elements are listed here in groups.

<Records> Every request must contain at least one record. For each record, indicate the elements.

Records Elements All element tags are required if, unless indicated otherwise Description All element values are required except where noted

RCDNUM

Record number.

RCDNAME

Record name.

CORRNAME

Alias name such as A,B,C, and so on.

JOINTYPE

Type of join. Valid values are:

  • None

  • Hierarchy

  • Related

  • Tree

  • LeftOuter

  • RelatedLeftOuter

Value is required only when the corresponding record is part of a JOIN in the query

JOINRCDALIAS

Record with which join is done.

Value is required only when the corresponding record is part of a JOIN in the query.

JOINFIELD

Field with which join is done.

Value is required only when the corresponding record is part of a JOIN in the query.

RCDSELECTNUM

Select number.

<Fields> The request must contain at least one field. For each field, indicate the elements:

Fields Elements All element tags are required if, unless indicated otherwise Description All element values are required except where noted

FIELDNUM

Field number.

FIELDNAME

Field name. When an expression is used as a field, the expression name is used for the field name and the value entered for this element is ignored.

FIELDRCDNUM

Record number. This element is ignored when an expression is used as a field.

DESCR

Not Required.

Description.

Value is not required.

AGGREGRATE_TYPE

Aggregation type. Valid values are:

  • Sum

  • Count

  • Min

  • Max

  • Average

  • None

HEADING_TYPE

Heading type. Valid values are:

  • None

  • Text

  • RFT Short

  • RFT Long

Note: If an expression is used as a field, the only valid value is Heading.

HEADING

If the HEADING_TYPE is Text, this element is used to enter the heading text.

Note: Value is required when the HEADING_TYPE value is Text. The default heading for an expression used as a field is the expression text.

COLUMNNUM

Column number.

ORDERBYNUM

Order by number.

ORDERBYDIR

Direction of field ordering. Valid values are:

  • Ascending

  • Descending

Note: Value is not required, the default is Ascending.

FIELDSELECTNUM

Identifier of SELECT in which this field is included.

EXPRESSION_AS_FIELD

Specify a valid expression number in order to use an expression as a field. For regular fields, leave this element blank.

<Criteria> If the query contains criteria, all criteria elements are required. For each criteria, indicate the elements.

Criteria Elements All element tags are required to define criteria Description All element values are required except where noted

CRTNUM

Criterion number.

CRTNAME

Criterion name.

CRTHAVINGFLAG

True if specifying a HAVING clause. Valid values are:

  • True

  • False

Note: Value is not required; the default is False.

CRTSELECTNUM

Identifier for the SELECT for this criterion.

CRTNEGATION

Negation in criterion. Valid values are:

  • True

  • False

Note: This is the equivalent of NOT in a SQL statement.

CONDITION_TYPE

Condition type in criterion. Valid values are:

  • none

  • between

  • not between

  • equal to

  • not equal to

  • exists

  • not exists

  • greater than

  • not greater than

  • in list

  • not in list

  • in tree

  • not in tree

  • is null

  • not is null

  • less than

  • not less than

  • like

  • not like

  • EffDate less than or equal to

  • EffDate greater than or equal to

  • EffDate less than

  • EffDate greater than

  • First EffDate

  • Last EffDate

  • in tree join

LEFT_PARENTHESIS_LEVEL

Left parenthesis level specified from 0 onwards. Used for GROUP BY.

RIGHT_PARENTHESIS_LEVEL

Right parenthesis level specified from 0 onwards. Used for GROUP BY.

CRTEXP1TYPE

Expression 1 type in criterion. Valid values are:

  • Field

  • Expression

CRTEXP1TEXT

Expression text for criterion 1.

CRTEXP1NUM

Expression number for the criteria.

CRTEXP1RCDALIAS

Alias for record used in expression 1 criterion. Used when CRTEXP1TYPE is Field. Alias name, such asA,B,C, and so on.

CRTEXP1FIELD

Field used in expression 1 criterion. Used when CRTEXP1TYPE is Field.

CRTEXP2RCDALIAS

Alias for record used in expression 2 criterion. Used when CRTEXP2TYPE is Field.

CRTEXP2FIELD

Field used in expression 2.

CRTEXP2TYPE

Expression 2 type in criterion. Valid values are:

  • Field

  • Expression

  • Constant

  • Prompt

  • Subquery

  • List

  • Const-Const

  • Const-Field

  • Const-Expr

  • Field-Const

  • Field-Field

  • Field-Expr

  • Expr-Const

  • Expr-Field

  • Expr-Expr

CRTEXP2TEXT

Expression 2 text is used to specify lists in A, B, C, D format, trees in string format, and prompt numbers.

CRTLOGICALOPER

Logical operator that links the criteria. Valid values are:

  • AND

  • OR

  • not used

CRT_BELONGSTO

If the criteria is for an outer join, you must indicate the correlation name (alias) that this criteria belongs to.

Note: This element is used for a query that contains an Outer Join. For others, value for this element is null.

<Expressions> If a query contains expressions, all expression elements are required. For each expression, indicate the elements.

Expressions Elements All element tags are required to define expression Description All element values are required except where noted

EXPNUM

Expression number.

EXPSELECTNUM

Identifier of SELECT number for this expression.

EXPNAME

Expression name.

EXPTYPE

Expression type. Valid values are:

  • Character

  • Date

  • Datetime

  • Drilling URL

  • Long Character

  • Number

  • Signed Number

  • Time

  • Image

EXPLENGTH

Expression length.

EXPDECIMALPOS

Number of decimal places in the expression.

EXPTEXT

Expression text.

EXPAGGREGATE

Specifies whether the expression is an aggregate function.

<Prompts> If the query contains prompts, all prompt elements are required.

Prompts Elements All element tags are required to define prompt Description All element values are required except where noted

PROMPT_NUM

Prompt number.

PROMPT_NAME

Prompt name.

PROMPT_UNIQUE_NAME

Unique prompt name.

Note: This value is not required; however, the client application is responsible for ensuring that all default prompt names are unique.

PROMPT_FLDNAME

Name of the field used as prompt.

PROMPT_TABLE

Name of the prompt table.

PROMPT_EDITTYPE

Edit type. Valid values are:

  • No table edit

  • Prompt table

  • Translate table

  • Yes/No table

PROMPT_HEADING

Heading used for the prompt.

PROMPT_HEADINGTYPE

Type of prompt heading. Valid values are:

  • RFT Long

  • RFT Short

  • Text

PROMPT_FLDLENGTH

Field length for prompt.

PROMPT_FLDDECIMALPOS

Number of decimal positions in prompt.

<Select> The request must contain at least one select. For each select, indicate the elements.

Select Elements All elements are required. Description All element values are required except where noted

SELECTNUM

SELECT number.

PARENTSELECTNUM

Number of parent SELECT. For main SELECT, this must be set to 0.

SELECTTYPE

Type of selection. Valid values are:

  • Main

  • Union

  • Subquery

QRYDISTINCT

Indicates whether this query is distinct. Valid values are:

  • True

  • False

Note: Value is not required; the default is False.

<Properties> The request must contain the properties that define the query. Indicate the property elements for the query.

Properties Elements All element tags are required Description All element values are required except where noted

QUERY_NAME

Query name.

DESCRIPTION

Short description. Optional.

DESCRLONG

Long description. Optional.

QUERY_OWNER

Indicate the query owner. Valid values are:

  • Public

  • Private

Note: If the query owner is not specified, the save will fail.

Depending on the complexity of the query, many different variations are available. Refer to the following sample queries.

Response Message: QAS_QUERY_SAVE_RESP_MSG

Element Name Description

STATUS

Status of the save operation, either Success or Failure.

INFO_MESSAGE

Returns information about the save operation.

Example Response:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Body>
      <qcs:QAS_QUERY_SAVE_RESP_MSG xmlns:qcs="http://xmlns.oracle.com/
      Enterprise/Tools/schemas/QAS_QUERY_SAVE_RESP_MSG.VERSION_1">
         <INFO_MESSAGE>Query TEST1 saved successfully.</INFO_MESSAGE>
      </qcs:QAS_QUERY_SAVE_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>