Enterprise PeopleTools 8.51 PeopleBook: Reporting Web Services

Executing a Query

This chapter provides overviews of the query execution process and output format and types. It also discusses the service operations used to:

Select a query
List fields
Enter prompts
Execute the query
Retrieve query results
Cancel a query
Retrieve query execution status

Understanding Query Execution

A third-party application will execute a PeopleSoft query using the same basic steps as a PeopleSoft online user. This table lists the steps to execute a query and retrieve query results.

Steps	Description
1. Select query.	QAS service operations are available to find and list existing queries.
2. Enter prompts.	QAS web services are available to retrieve query prompts and get translate values and prompt table values.
3. Filter fields.	A service operation is available to list all of the fields in a query.
4. Execute the query.	QAS service operations are available to execute a query synchronously or asynchronously.
5. Retrieve query results.	A service operation is available to retrieve the query results.
6. Cancel a query.	QAS provides a service operation to cancel query execution.
7. Retrieve query status.	QAS provides a service operation to retrieve the current query execution status.

Query Execution Models

Query Execution Services supports the following models:

Model	Description
Synchronous Request and Response	Request is sent and processed returning the response.
Synchronous Request and Synchronous Poll with Chunked Response	Request is sent synchronously and run in the Process Scheduler. Requestor will need to retrieve the results when the process has finished.
Asynchronous Request and Response	Request is sent asynchronously and queued in Integration Broker and processed. The response is returned to the http url specified in the ws addressing soap header. Requestor has the option of requesting a response when the process has finished.

Note. In order to use Integration Broker for asynchronous and synchronous poll message processing Pub/Sub must be active.

See Administering Messaging Servers for Asynchronous Messaging.

Performance Considerations

Which execution model to run depends on the number of rows and columns returned in the response. Note the following guidelines:

Synchronous execution is not recommended for large result sets.
Response data that consists of a large number of columns with fewer rows of data has a slower response than response data that consists of fewer columns and more rows, even though the response size may be smaller.
Executing synchronous poll query with block size = 0 or Max will result in all data in one block.
In synchronous poll execution, the larger the response block, the fewer blocks are returned and total response time for all blocks is shorter, but the user has to wait longer for the response.
In synchronous poll execution, if the heap size in the web server is not sufficient to retrieve the large response block, a JAVA out of memory exception will occur.
In synchronous poll execution, if a smaller block size (less than 100,000) is used, the result will be too many blocks containing very few rows. Optimal request block size in KB is in the range from 100,000 to 1,000,000.

Output Format and Output Type

When you execute a query using QAS service operations, the output type can be WEBROWSET, XMLP, EXCEL or HTML. The output format will be either FILE or NONFILE.

This table shows the valid combinations of output format and output type.

Type	FILE	NONFILE
WEBROWSET	Creates a file in webrowset format.	Creates a response in webrowset format.
XMLP	Creates an XML file. This file is suitable to use with XML Publisher reports.	Creates a response in XMLP format. Note. Connected Query is not supported for nonfile output.
EXCEL	Creates an XLS file.	Not valid
HTML	Creates an HTML file.	Not valid

Report Repository

Only HTTP and HTTPS are supported as the transport mechanism to publish QAS file output to the PeopleSoft Report Repository. This means that for QAS to work with file output in SYNC and ASYNC execution, a URI (universal resource identifier) scheme of HTTP or HTTPS must be defined for a server.

See Defining HTTP Distribution Nodes.

WebRowSet Format

WebRowSet represents a set of fetched rows that can be passed between tiers and components and for which the data (obtained in XML format) is managed either by storing it in a string or by writing it to a file in the local file system. PeopleSoft uses the standard WebRowSet format based on the WebRowSet schema. When you use WebRowSet, the rows (tabular data) referred to as rowsets can be read and written in XML format, thus enabling rowsets to be sent over the Internet using the HTTP/XML protocol.

WebRowSet Schema definition describes the internal data of a RowSet object in three distinct areas:

Properties	These properties describe the standard synchronization provider properties in addition to the more general rowset properties.
Metadata	The metadata describes the tabular structure governed by a WebRowSet object.
Data	The data is the current data values.

This example shows the four rows of data returned for a simple query MSGSET that has two columns. The SQL for this query is:

SELECT A.MESSAGE_SET_NBR, A.DESCR
  FROM PSMSGSETDEFN A

Example WebRowset File:

<?xml version='1.0'?>
 <webRowSet xmlns='http://java.sun.com/xml/ns/jdbc'>
  <properties>
    <command></command>
    <concurrency>1007</concurrency>
    <datasource/>
    <escape-processing>true</escape-processing>
    <fetch-direction>1000</fetch-direction>
    <fetch-size>0</fetch-size>
    <isolation-level>1</isolation-level>
    <key-columns/>
    <map/>
    <max-field-size>0</max-field-size>
    <max-rows>0</max-rows>
    <query-timeout>0</query-timeout>
    <read-only>true</read-only>
    <rowset-type>1003</rowset-type>
    <show-deleted>false</show-deleted>
    <table-name/>
    <url></url>
    <sync-provider>
      <sync-provider-name></sync-provider-name>
      <sync-provider-vendor></sync-provider-vendor>
      <sync-provider-version></sync-provider-version>
      <sync-provider-grade></sync-provider-grade>
      <data-source-lock></data-source-lock>
    </sync-provider>
  </properties>
  <metadata>
    <column-count>2</column-count>
    <column-definition>
      <column-index>1</column-index>
      <auto-increment>false</auto-increment>
      <case-sensitive>false</case-sensitive>
      <currency>false</currency>
      <nullable>false</nullable>
      <signed>false</signed>
      <searchable>false</searchable>
      <column-display-size>0</column-display-size>
      <column-label>Message Set</column-label>
      <column-name>A.MESSAGE_SET_N</column-name>
      <schema-name/>
      <column-precision>0</column-precision>
      <column-scale>0</column-scale>
      <table-name/>
      <catalog-name/>
      <column-type>2</column-type>
      <column-type-name>NUMBER</column-type-name>
   </column-definition>
   <column-definition>
      <column-index>2</column-index>
      <auto-increment>false</auto-increment>
      <case-sensitive>false</case-sensitive>
      <currency>false</currency>
      <nullable>false</nullable>
      <signed>false</signed>
      <searchable>false</searchable>
      <column-display-size>0</column-display-size>
      <column-label>Message Set Description</column-label>
      <column-name>A.DESCR</column-name>
      <schema-name/>
      <column-precision>0</column-precision>
      <column-scale>0</column-scale>
      <table-name/>
      <catalog-name/>
      <column-type>1</column-type>
      <column-type-name>CHAR</column-type-name>
   </column-definition>
  </metadata>
  <data>
    <currentRow>
      <columnValue>1</columnValue>
      <columnValue>
        <![CDATA[PeopleTools Message Bar Items]]>
      </columnValue>
    </currentRow>
    <currentRow>
      <columnValue>2</columnValue>
      <columnValue>
        <![CDATA[PeopleCode]]>
      </columnValue>
    </currentRow>
    <currentRow>  
      <columnValue>3</columnValue>
      <columnValue>
        <![CDATA[General Tools Messages]]>
      </columnValue>
    </currentRow>
    <currentRow>
      <columnValue>4</columnValue>
      <columnValue>
        <![CDATA[Help Processor]]>
      </columnValue>
    </currentRow>
  </data>
</webRowSet>

XMLP Format

XMLP format creates an XML file suitable for use with Oracle BI Publisher. Oracle BI Publisher expects the XML data file to consist of a root node with a set of related elements below.

This example shows the four rows of data returned for a simple query MSGSET that has two columns. The SQL for this query is:

SELECT A.MESSAGE_SET_NBR, A.DESCR
  FROM PSMSGSETDEFN A

Example XMLP File:

<?xml version='1.0'?>
<query numrows="4" queryname="MSGSET" xmlns:xsi="http://www.w3.org/2001/XMLSchema-⇒
instance" 

xsi:noNamespaceSchemaLocation="">
  <row rownumber="1">
    <MESSAGE_SET_NBR>1</MESSAGE_SET_NBR>
    <DESCR>PeopleTools Message Bar Items</DESCR>
  </row>
  <row rownumber="2">
    <MESSAGE_SET_NBR>2</MESSAGE_SET_NBR>
    <DESCR>PeopleCode</DESCR>
  </row>
  <row rownumber="3">
    <MESSAGE_SET_NBR>3</MESSAGE_SET_NBR>
    <DESCR>General Tools Messages</DESCR>
  </row>
  <row rownumber="4">
    <MESSAGE_SET_NBR>4</MESSAGE_SET_NBR>
    <DESCR>Help Processor</DESCR>
  </row>
</query>

Excel Format

Excel format creates an xls file that can be opened in Microsoft Excel.

This example shows the five rows of data returned for a simple query MSGSET that has two columns. The SQL for this query is:

SELECT A.MESSAGE_SET_NBR, A.DESCR
  FROM PSMSGSETDEFN A

Example XLS File:

HTML Format

HTML format creates an HTML file.

This example shows the four rows of data returned for a simple query MSGSET that has two columns. The SQL for this query is:

SELECT A.MESSAGE_SET_NBR, A.DESCR
  FROM PSMSGSETDEFN A

Example HTML File:

Date and Time Handling

When you create a query containing date fields, the default display in PeopleSoft Pure Internet Architecture is based on the regional setting. For example, if the personalization setting uses date format MMDDYY and date separator /, the date is displayed as MM/DD/YYYY.

See Defining User-Specific, Locale-Based Formatting.

When you execute the query using one of the QAS service operations, date and time are returned in the following way:

Date is returned to the client applications as YYYY-MM-DD. For example, April 6, 2009 is 2009-04-06.
Time is returned as hh:mm:ss.milliseconds. For example, 9:30 a.m. is returned as 9:30:00.000000.
Datetime is returned with the UTC (coordinated universal time) offset for the base time zone. For example, if the base time zone is PDT (Pacific Daylight Time), then April 6, 2009 9:30 a.m. is returned as 2009-04-06T09:30:00-0700.

If a date field is used as a prompt, the FieldValue must be entered as YYYY-MM-DD in the QAS execution request.

Execution Logging

HTTP transfer code is used to post file output to the PSFT Report Repository. For synchronous execution run to file , QAS HTTP transfer code logs messages to the application server log. Refer to the PeopleSoft Process Scheduler PeopleBook for information on setting logging levels and viewing log reports.

See Logging System for PeopleSoft Process Scheduler Server.

For all other debugging purposes, standard Integration Broker logs can be viewed.

See Monitoring Asynchronous Service Operations, Monitoring Synchronous Service Operations.

Selecting a Query

Once a query has been saved in the PeopleSoft application database, third-party applications can use service operations to find and list existing queries. This section describes the following service operations, which are available to select a query:

QAS_LISTQUERY_OPER
QAS_QUERY_DETAILS_OPER

QAS_LISTQUERY_OPER

Use this service operation to return a list of queries, along with the query descriptions, and owner type. If no queries match the search string, the response message will be empty.

Request Message: QAS_LISTQUERY_REQ_MSG

Element Name All elements are optional	Description
SearchString	Search string used for specifying the query name or the first few characters of the query name. If no value is entered, all queries for the user will be returned.
OwnerType	Optionally enter either public or private for the owner type. If no value is entered, all queries both public and private for the user will be returned.
MaxRows	Optionally enter the maximum number of rows to return.
isConnectedQry	Enter Y to return list of connected queries; enter N to return list of queries. If the no value is entered, a list of all queries, including connected queries, is returned.

Example Request:

This request will retrieve a list of all public queries that start with XRFW.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:⇒
qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_LISTQUERY_REQ_⇒
MSG.VERSION_1" xmlns:qas1="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_⇒
LISTQUERY_REQ.VERSION_1">
   <soapenv:Header/>
   <soapenv:Body>
      <qas:QAS_LISTQUERY_REQ_MSG>
         <!--Zero or more repetitions:-->
         <qas:QAS_LISTQUERY_REQ>
            <qas1:PTQASWRK class="R">
               <!--Optional:-->
               <qas1:SearchString>XRFW</qas1:SearchString>
               <!--Optional:-->
               <qas1:OwnerType>public</qas1:OwnerType>
               <!--Optional:-->
               <qas1:MaxRows></qas1:MaxRows>
               <!--Optional:-->
               <qas:isConnectedQry></qas:isConnectedQry>
            </qas1:PTQASWRK>
         </qas:QAS_LISTQUERY_REQ>
      </qas:QAS_LISTQUERY_REQ_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_LISTQUERY_RESP_MSG

Element Name	Description
QueryName	Query name.
Description	Query description.
OwnerType	Query owner type.
isConnectedQry	Connected query.

Example Response:

<?xml version="1.0"?>
<QAS_LISTQUERY_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas⇒
/QAS_LISTQUERY_RESP_MSG.VERSION_1">
    <QAS_LISTQUERY_RESP>
        <PTQASWRK class="R" xmlns="http://xmlns.oracle.com/Enterprise/Tools⇒
/schemas/QAS_LISTQUERY_RESP.VERSION_1">
            <QueryName>XRFWIN</QueryName>
            <OwnerType>public</OwnerType>
            <Description>XRFWIN</Description>
            <isConnectedQry>N</isConnectedQry>
        </PTQASWRK>
    </QAS_LISTQUERY_RESP>
    <QAS_LISTQUERY_RESP>
        <PTQASWRK class="R" xmlns="http://xmlns.oracle.com/Enterprise/Tools⇒
/schemas/QAS_LISTQUERY_RESP.VERSION_1">
            <QueryName>XRFWNFL</QueryName>
            <OwnerType>public</OwnerType>
            <Description>XRFWNFL</Description>
            <isConnectedQry>N</isConnectedQry>
        </PTQASWRK>
    </QAS_LISTQUERY_RESP>
</QAS_LISTQUERY_RESP_MSG>

QAS_QUERY_DETAILS_OPER

Use this service operation to return the complete details of an existing query in XML format.

Request Message: QAS_QUERY_DETAILS_REQ_MSG

Element Name	Description
QUERY_NAME Required element	Complete query name. Required.
IS_CONNECTED_QUERY Required element	Indicates that this is connected query. Valid values are Y and N. The default value is N.

Element Name

Description

QUERY_NAME

Required element

Complete query name. Required.

IS_CONNECTED_QUERY

Required element

Indicates that this is connected query. Valid values are Y and N.

The default value is N.

Example Request:

This request will return the query details for the query XRFWIN.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:⇒
qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_QUERY_DETAILS_REQ_⇒
MSG.VERSION_1">
   <soapenv:Header/>
   <soapenv:Body>
      <qas:QAS_QUERY_DETAILS_REQ_MSG>
         <QUERY_NAME>QUERY_WITH_EXPRESSION</QUERY_NAME>
      </qas:QAS_QUERY_DETAILS_REQ_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_QUERY_DETAILS_RESP_MSG

The response will include the details for the query definition. 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.

<Properties> Returns the properties of the query definition.

Properties Elements	Description
QUERY_NAME	Query name.
DESCRIPTION	Query description.
DESCRLONG	Long description
QUERY_OWNER	Query owner type.
CREATION_TIME	Time of query creation in PeopleSoft database.
LAST_UPDATE_TIME	Time of most recent update of the query in PeopleSoft database. Format is YYYY-MM-DD-HH.MM.SS.FF where FF is the fractional part of a second.

<Selects> Returns the select information of the query definition.

Select Elements	Description
SELECTNUM	SELECT number.
PARENTSELECTNUM	Number of parent SELECT.
SELECTTYPE	Type of selection, Main, Subquery, or Union.
QRYDISTINCT	Indicates whether this query is distinct.

<Records> Returns the record correlation and join information of the query definition.

Records Elements	Description
RCDNUM	Record number.
RCDNAME	Record name.
RCDSELECTNUM	Select number.
CORRNAME	Alias name such as A, B, C, and so on.
JOINTYPE	Join type.
JOINRCDALIAS	Join record alias.
JOINFIELD	Join field.

<Fields> Returns the field of the query definition.

Fields Elements	Description
FIELDNUM	Field number in the corresponding select.
FIELDNAME	Field name.
DESCR	Description.
FORMAT	Field format as a combination of field type and field length.
AGGREGRATE_TYPE	Aggregation type.
HEADING_TYPE	Heading type.
HEADING	Heading text.
FIELDSELECTNUM	Identifier of SELECT in which this field is included.
ORDERBYNUM	Order by number.
COLUMNNUM	Column number.
ORDERBYDIR	Direction of field ordering.
FIELDTYPE	Field type.
FIELDRCDALIAS	Alias of the record to which this field belongs.

<Criteria> Returns the criteria information of the query definition.

Criteria Elements	Description
CRTNUM	Criterion number in the corresponding select statement.
CRTTYPE	Specifies whether the HAVING clause is used.
CRTNAME	Criterion name.
CRTSELECTNUM	Select Number.
NEGATION	Returns True if NOT is specified in the operator; otherwise, returns False.
CONDITION_TYPE	Condition type in criterion.
LEFT_PARENTHESIS_LEVEL	Left parenthesis level specified from 0 onwards.
RIGHT_PARENTHESIS_LEVEL	Right parenthesis level specified from 0 onwards.
CRTEXP1TYPE	Specifies whether the criterion is Field or Expression.
CRTEXP1RCDALIAS	Alias for record used in expression 1 criterion, such as A, B, and so on.
CRTEXP1FIELD	Field used in expression 1 criterion.
CRTEXP1TEXT	Expression text for criterion 1.
CRTEXP1NUM	Expression number for the criterion.
CRTEXP2RCDALIAS	Alias for record used in expression 2 criterion.
CRTEXP2FIELD	Field used in expression 2 criterion.
CRTEXP2TYPE	Expression 2 type in criterion.
CRTEXP2TEXT	Expression 2 text.
CRTLOGICALOPER	Logical operator that links the criteria.

<Expression> Returns the expression information of the query definition.

Expression Elements	Description
EXPNUM	Expression number.
EXPSELECTNUM	Expression's SELECT number.
EXPNAME	Expression name.
EXPTYPE	Expression type.
EXPLENGTH	Expression length.
EXPDECIMALPOS	Number of decimal places.
EXPTEXT	Expression text.
EXPAGGREGATE	Specifies whether the expression is an aggregate function.

<Prompts> Returns the prompt information of the query definition.

Prompt Elements	Description
PROMPT_NUM	Prompt number.
PROMPT_NAME	Unique prompt name.
PROMPT_UNIQUE_NAME	Unique prompt name.
PROMPT_FLDNAME	Name of the field used as prompt.
PROMPT_TABLE	Prompt table name.
PROMPT_EDITTYPE	Edit type.
PROMPT_FLDFORMAT	Field format.
PROMPT_FLDLENGTH	Field length for prompt.
PROMPT_FLDDECIMALPOS	Number of decimal places in prompt.
PROMPT_FLDTYPE	Field type.
PROMPT_HEADING	Heading of prompt.
PROMPT_HEADINGTYPE	Type of prompt heading.

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_DETAILS_RESP_MSG xmlns:qcs="http://xmlns.oracle.com⇒
/Enterprise⇒
/Tools/schemas/QAS_QUERY_DETAILS_RESP_MSG.VERSION_1">
         <QUERY_NAME>QUERY_WITH_EXPRESSION</QUERY_NAME>
         <DESCRIPTION>Example query</DESCRIPTION>
         <DESCRLONG>Sample query with expression, criterion and prompt</DESCRLONG>
         <QUERY_OWNER>QEDMO, Public</QUERY_OWNER>
         <CREATION_TIME>2009-05-15-09.48.15.000000</CREATION_TIME>
         <LAST_UPDATE_TIME>2009-05-15-09.48.15.546000</LAST_UPDATE_TIME>
         <PROMPT>
            <PROMPT_NUM>1</PROMPT_NUM>
            <PROMPT_NAME>QE_ORDER_NBR</PROMPT_NAME>
            <PROMPT_FLDNAME>QE_ORDER_NBR</PROMPT_FLDNAME>
            <PROMPT_UNIQUE_NAME>BIND1</PROMPT_UNIQUE_NAME>
            <PROMPT_EDITTYPE>Prompt table</PROMPT_EDITTYPE>
            <PROMPT_TABLE>QEORDER_HDR</PROMPT_TABLE>
            <PROMPT_FLDFORMAT>Upper</PROMPT_FLDFORMAT>
            <PROMPT_FLDDECIMALPOS>0</PROMPT_FLDDECIMALPOS>
            <PROMPT_FLDLENGTH>8</PROMPT_FLDLENGTH>
            <PROMPT_FLDTYPE>Character</PROMPT_FLDTYPE>
            <PROMPT_HEADING>Order</PROMPT_HEADING>
            <PROMPT_HEADINGTYPE>RFT Short</PROMPT_HEADINGTYPE>
         </PROMPT>
         <SELECT>
            <SELECTNUM>1</SELECTNUM>
            <PARENTSELECTNUM>0</PARENTSELECTNUM>
            <SELECTTYPE>Main</SELECTTYPE>
            <QRYDISTINCT>False</QRYDISTINCT>
         </SELECT>
         <RECORD>
            <RCDNUM>1</RCDNUM>
            <RCDNAME>QEORDER_DTL</RCDNAME>
            <RCDSELECTNUM>1</RCDSELECTNUM>
            <CORRNAME>A</CORRNAME>
         </RECORD>
         <FIELD>
            <FIELDNUM>1</FIELDNUM>
            <FIELDNAME>QE_ORDER_NBR</FIELDNAME>
            <DESCR>Order Number</DESCR>
            <FORMAT>CHAR 8</FORMAT>
            <AGGREGATE_TYPE>None</AGGREGATE_TYPE>
            <HEADING_TYPE>RFT Short</HEADING_TYPE>
            <HEADING>Order</HEADING>
            <FIELDSELECTNUM>1</FIELDSELECTNUM>
            <ORDERBYNUM>0</ORDERBYNUM>
            <ORDERBYDIR>Ascending</ORDERBYDIR>
            <COLUMNNUM>1</COLUMNNUM>
            <FIELDTYPE>Character</FIELDTYPE>
            <FIELDRCDALIAS>A</FIELDRCDALIAS>
         </FIELD>
         <FIELD>
            <FIELDNUM>2</FIELDNUM>
            <FIELDNAME>QE_ORDER_LINE_NBR</FIELDNAME>
            <DESCR>Order Line Number</DESCR>
            <FORMAT>NUMBER 3</FORMAT>
            <AGGREGATE_TYPE>None</AGGREGATE_TYPE>
            <HEADING_TYPE>RFT Short</HEADING_TYPE>
            <HEADING>Order Line</HEADING>
            <FIELDSELECTNUM>1</FIELDSELECTNUM>
            <ORDERBYNUM>0</ORDERBYNUM>
            <ORDERBYDIR>Ascending</ORDERBYDIR>
            <COLUMNNUM>2</COLUMNNUM>
            <FIELDTYPE>Number</FIELDTYPE>
            <FIELDRCDALIAS>A</FIELDRCDALIAS>
         </FIELD>
         <FIELD>
            <FIELDNUM>3</FIELDNUM>
            <FIELDNAME>QE_QTY</FIELDNAME>
            <DESCR>Quantity</DESCR>
            <FORMAT>SIGNED 17</FORMAT>
            <AGGREGATE_TYPE>None</AGGREGATE_TYPE>
            <HEADING_TYPE>RFT Short</HEADING_TYPE>
            <HEADING>Qty</HEADING>
            <FIELDSELECTNUM>1</FIELDSELECTNUM>
            <ORDERBYNUM>0</ORDERBYNUM>
            <ORDERBYDIR>Ascending</ORDERBYDIR>
            <COLUMNNUM>3</COLUMNNUM>
            <FIELDTYPE>Signed Number</FIELDTYPE>
            <FIELDRCDALIAS>A</FIELDRCDALIAS>
         </FIELD>
         <FIELD>
            <FIELDNUM>4</FIELDNUM>
            <FIELDNAME>QE_PRICE</FIELDNAME>
            <DESCR>Price</DESCR>
            <FORMAT>NUMBER 9</FORMAT>
            <AGGREGATE_TYPE>None</AGGREGATE_TYPE>
            <HEADING_TYPE>RFT Short</HEADING_TYPE>
            <HEADING>Price</HEADING>
            <FIELDSELECTNUM>1</FIELDSELECTNUM>
            <ORDERBYNUM>0</ORDERBYNUM>
            <ORDERBYDIR>Ascending</ORDERBYDIR>
            <COLUMNNUM>4</COLUMNNUM>
            <FIELDTYPE>Number</FIELDTYPE>
            <FIELDRCDALIAS>A</FIELDRCDALIAS>
         </FIELD>
         <FIELD>
            <FIELDNUM>5</FIELDNUM>
            <FIELDNAME>EXPR5_5</FIELDNAME>
            <DESCR/>
            <FORMAT>NUMBER 11</FORMAT>
            <AGGREGATE_TYPE>None</AGGREGATE_TYPE>
            <HEADING_TYPE>Text</HEADING_TYPE>
            <HEADING>A.QE_QTY*A.QE_PRICE</HEADING>
            <FIELDSELECTNUM>1</FIELDSELECTNUM>
            <ORDERBYNUM>0</ORDERBYNUM>
            <ORDERBYDIR>Ascending</ORDERBYDIR>
            <COLUMNNUM>5</COLUMNNUM>
            <FIELDTYPE>Number</FIELDTYPE>
            <FIELDRCDALIAS/>
         </FIELD>
         <CRITERION>
            <CRTNUM>1</CRTNUM>
            <CRTSELECTNUM>1</CRTSELECTNUM>
            <NEGATION>False</NEGATION>
            <LEFT_PARENTHESIS_LEVEL>0</LEFT_PARENTHESIS_LEVEL>
            <RIGHT_PARENTHESIS_LEVEL>0</RIGHT_PARENTHESIS_LEVEL>
            <CONDITION_TYPE>equal to</CONDITION_TYPE>
            <CRTEXP1TYPE>Field</CRTEXP1TYPE>
            <CRTEXP1RCDALIAS>A</CRTEXP1RCDALIAS>
            <CRTEXP1FIELD>QE_ORDER_NBR</CRTEXP1FIELD>
            <CRTEXP2TYPE>Prompt</CRTEXP2TYPE>
            <CRTEXP2TEXT>:1</CRTEXP2TEXT>
            <CRTLOGICALOPER>not used</CRTLOGICALOPER>
         </CRITERION>
         <EXPRESSION>
            <EXPNUM>1</EXPNUM>
            <EXPSELECTNUM>1</EXPSELECTNUM>
            <EXPTYPE>Number</EXPTYPE>
            <EXPTEXT>A.QE_QTY*A.QE_PRICE</EXPTEXT>
            <EXPLENGTH>11</EXPLENGTH>
            <EXPDECIMALPOS>2</EXPDECIMALPOS>
            <EXPAGGREGATE>False</EXPAGGREGATE>
         </EXPRESSION>
      </qcs:QAS_QUERY_DETAILS_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Listing Fields

This section describes the service operation that is available for listing the fields in a query.

QAS_LISTQUERYFIELDS

Use this service operation to return a list of fields for a given query. This service operation will help the user discover the unique field names to use in the FilterFieldName when the query is executed.

Request Message: QAS_LISTQUERYFIELDS_REQ_MSG

Element Name	Description
QueryName Required element	Complete query name. Required.
OwnerType	Indicate whether it is a public or private query. If no value is specified, QAS will first look for a private query and if it is not found, will look for a public query.
IsconnectedQuery Required element	Indicate whether this is connected query. Required. Valid values are Y and N.

Example Request:

This request will return the fields for the query XRFWIN.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:⇒
qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_LISTQUERYFIELDS_REQ_⇒
MSG.VERSION_1" xmlns:qas1="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_⇒
LISTQUERYFIELDS_REQ.VERSION_1">
   <soapenv:Header/>
   <soapenv:Body>
      <qas:QAS_LISTQUERYFIELDS_REQ_MSG>
         <!--Zero or more repetitions:-->
         <qas:QAS_LISTQUERYFIELDS_REQ>
            <qas1:PTQASWRK class="R">
               <qas1:QueryName>XRFWIN</qas1:QueryName>
               <!--Optional:-->
               <qas1:OwnerType>Public</qas1:OwnerType>
               <qas1:isConnectedQuery>n</qas1:isConnectedQuery>
            </qas1:PTQASWRK>
         </qas:QAS_LISTQUERYFIELDS_REQ>
      </qas:QAS_LISTQUERYFIELDS_REQ_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_LISTQUERYFIELDS_RESP_MSG

Element Name	Description
ColumnNumber	Column number.
FieldName	Field name.
FieldType	Field type.
FieldLength	Field length.
FieldDecimal	Number of decimal positions.
HeadingText	Unique heading text.
UniqueFieldName	Unique field name. Unique field names are used to set up field filtering when the query is executed.

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>
      <QAS_LISTQUERYFIELDS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise⇒
/Tools/schemas/QAS_LISTQUERYFIELDS_RESP_MSG.VERSION_1">
         <QAS_LISTQUERYFIELDS_RESP>
            <PTQASWRK class="R" xmlns="http://xmlns.oracle.com/Enterprise/Tools⇒
/schemas/QAS_LISTQUERYFIELDS_RESP.VERSION_1">
               <QueryName>XRFWIN</QueryName>
               <PTQASFIELDWRK class="R">
                  <ColumnNumber>1</ColumnNumber>
                  <FieldName>MENUNAME</FieldName>
                  <FieldType>string</FieldType>
                  <FieldLength>30</FieldLength>
                  <FieldDecimal>0</FieldDecimal>
                  <HeadingText>Menu Name</HeadingText>
                  <UniqueFieldName>Menu Name</UniqueFieldName>
               </PTQASFIELDWRK>
            </PTQASWRK>
         </QAS_LISTQUERYFIELDS_RESP>
      </QAS_LISTQUERYFIELDS_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Entering Prompts

This section describes the service operations that are available for discovering prompt and valid prompt values for a query. These include:

QAS_LISTQUERYPROMPTS_OPER
QAS_GETPROMPTTABLEVALUES_OPER
QAS_GETXLAT_OPER

QAS_LISTQUERYPROMPTS_OPER

Use this service operation to retrieve a list of the prompts and associated prompt table for a specific query.

Request Message: QAS_LISTQUERYPROMPTS_REQ_MSG

Element Name	Description
QueryName Required element	Complete Query Name. Required.
OwnerType	Optionally, enter the query owner type. If no value is entered, both public and private are searched.
isConnectedQuery Required element	Indicate whether this is a connected query. Required. Valid values are Y and N.

Example Request:

This request will return the prompt field and prompt table for the query TWO_PROMPT_QUERY.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:⇒
qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_LISTQUERYPROMPTS_REQ_⇒
MSG.VERSION_1" xmlns:qas1="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_⇒
LISTQUERYPROMPTS_REQ.VERSION_1">
   <soapenv:Header/>
   <soapenv:Body>
      <qas:QAS_LISTQUERYPROMPTS_REQ_MSG>
         <qas:QAS_LISTQUERYPROMPTS_REQ>
            <qas1:PTQASWRK class="R">
               <qas1:QueryName>TWO_PROMPT_QUERY</qas1:QueryName>
               <!--Optional:-->
               <qas1:OwnerType>PUBLIC</qas1:OwnerType>
               <qas1:isConnectedQuery>n</qas1:isConnectedQuery>
            </qas1:PTQASWRK>
         </qas:QAS_LISTQUERYPROMPTS_REQ>
      </qas:QAS_LISTQUERYPROMPTS_REQ_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_LISTQUERYPROMPTS_RESP_MSG

Element Name	Description
QueryName	Query name.
FieldName	If this prompt uses a translate table, the field name for the prompt is returned.
HeadingText	Heading text for the prompt.
FieldType	Prompt field type.
FormatText	Format for the text.
FieldLength	Field length for the prompt.
FieldDecimal	Number of decimal places in the prompt field.
UniquePromptName	The unique name used for the prompt. This value is case-sensitive.
EditType	The type of edit: Translate table Yes/No table Prompt table No table edit
PromptTable	If this prompt uses a prompt table, the table name is returned.

Example Response:

This response has two prompts, one for a prompt table and one for a translate table edit.

<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>
      <QAS_LISTQUERYPROMPTS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/⇒
Tools/schemas/QAS_LISTQUERYPROMPTS_RESP_MSG.VERSION_1">
         <QAS_LISTQUERYPROMPTS_RESP>
            <PTQASWRK class="R" xmlns="http://xmlns.oracle.com/Enterprise/Tools/⇒
schemas/QAS_LISTQUERYPROMPTS_RESP.VERSION_1">
               <QueryName>TWO_PROMPT_QUERY</QueryName>
               <PTQASPRMPTWRK class="R">
                  <FieldName>NODE_TYPE</FieldName>
                  <HeadingText>Node Type</HeadingText>
                  <FieldType>string</FieldType>
                  <FormatText>upper</FormatText>
                  <FieldLength>30</FieldLength>
                  <FieldDecimal>0</FieldDecimal>
                  <UniquePromptName>BIND1</UniquePromptName>
                  <EditType>Translate table</EditType>
                  <PromptTable/>
               </PTQASPRMPTWRK>
               <PTQASPRMPTWRK class="R">
                  <FieldName>PORTAL_TYPE</FieldName>
                  <HeadingText>Portal</HeadingText>
                  <FieldType>string</FieldType>
                  <FormatText>upper</FormatText>
                  <FieldLength>30</FieldLength>
                  <FieldDecimal>0</FieldDecimal>
                  <UniquePromptName>BIND2</UniquePromptName>
                  <EditType>Prompt table</EditType>
                  <PromptTable>PSPRDMDEFN</PromptTable>
               </PTQASPRMPTWRK>
            </PTQASWRK>
         </QAS_LISTQUERYPROMPTS_RESP>
      </QAS_LISTQUERYPROMPTS_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>

QAS_GETPROMPTTABLEVALUES_OPER

Use this service operation to return a list of field values for a given prompt table.

Request Message: QAS_GETPRMPTTBLVAL_REQ_MSG

Element Name	Description
PromptTableName Required element	Complete prompt record name. Required.
FieldValue	Optional search string for the key field value. If no value is entered, a list of all key values will be returned. Note. Multiple keys are not supported in PS Query. Tables with no keys are not supported in PS Query.
MaxRows	Optionally, enter the maximum number of rows to be returned. MaxRows is a number of length 10 with a decimal position of 0.

Example Request:

This request will return the first three rows of prompt table values for the table PSMSGSETDEFN.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:⇒
qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_GETPRMPTTBLVAL_REQ_⇒
MSG.VERSION_1" xmlns:qas1="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_⇒
GETPRMPTTBLVAL_REQ.VERSION_1">
   <soapenv:Header/>
   <soapenv:Body>
      <qas:QAS_GETPRMPTTBLVAL_REQ_MSG>
         <!--Zero or more repetitions:-->
         <qas:QAS_GETPRMPTTBLVAL_REQ>
            <qas1:PTQASWRK class="R">
               <qas1:PromptTableName>psmsgsetdefn</qas1:PromptTableName>
               <!--Optional:-->
               <qas1:MaxRows>3</qas1:MaxRows>
               <!--Optional:-->
               <qas1:FieldName></qas1:FieldName>
            </qas1:PTQASWRK>
         </qas:QAS_GETPRMPTTBLVAL_REQ>
      </qas:QAS_GETPRMPTTBLVAL_REQ_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_GETPRMPTTBLVALUES_RESP_MSG

Element Name	Description
FieldValue	Field value.

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>
      <QAS_GETPRMPTTBLVAL_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/Tools⇒
/schemas/QAS_GETPRMPTTBLVAL_RESP_MSG.VERSION_1">
         <QAS_GETPRMPTTBLVAL_RESP>
            <PTQASFIELDWRK class="R" xmlns="http://xmlns.oracle.com/Enterprise⇒
/Tools/schemas/QAS_GETPRMPTTBLVAL_RESP.VERSION_1">
               <FieldValue>1</FieldValue>
            </PTQASFIELDWRK>
         </QAS_GETPRMPTTBLVAL_RESP>
         <QAS_GETPRMPTTBLVAL_RESP>
            <PTQASFIELDWRK class="R" xmlns="http://xmlns.oracle.com/Enterprise⇒
/Tools/schemas/QAS_GETPRMPTTBLVAL_RESP.VERSION_1">
               <FieldValue>2</FieldValue>
            </PTQASFIELDWRK>
         </QAS_GETPRMPTTBLVAL_RESP>
         <QAS_GETPRMPTTBLVAL_RESP>
            <PTQASFIELDWRK class="R" xmlns="http://xmlns.oracle.com/Enterprise⇒
/Tools/schemas/QAS_GETPRMPTTBLVAL_RESP.VERSION_1">
               <FieldValue>3</FieldValue>
            </PTQASFIELDWRK>
         </QAS_GETPRMPTTBLVAL_RESP>
      </QAS_GETPRMPTTBLVAL_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>

QAS_GETXLAT_OPER

Use this service operation to return the translate values for a given field.

Request Message: QAS_GETXLAT_REQ_MSG

Element Name	Description
FieldName	Complete field name. Required.

Example Request:

This request will return the translate value for EFF_STATUS.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:⇒
qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_GETXLAT_REQ_MSG.VERSION_⇒
1" xmlns:qas1="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_GETXLAT_⇒
REQ.VERSION_1"> 
   <soapenv:Header/>
   <soapenv:Body>
      <qas:QAS_GETXLAT_REQ_MSG>
         <!--Zero or more repetitions:-->
         <qas:QAS_GETXLAT_REQ>
            <qas1:PTQASWRK class="R">
               <qas1:FieldName>EFF_STATUS</qas1:FieldName>
            </qas1:PTQASWRK>
         </qas:QAS_GETXLAT_REQ>
      </qas:QAS_GETXLAT_REQ_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_GETXLAT_RESP_MSG

Element Name	Description
FieldValue	Translate value.
ShortName	Short name for the translate value.
LongName	Long name for the translate value.

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>
      <QAS_GETXLAT_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/Tools⇒
/schemas/QAS_GETXLAT_RESP_MSG.VERSION_1">
         <QAS_GETXLAT_RESP>
            <PTQASWRK class="R" xmlns="http://xmlns.oracle.com/Enterprise/Tools⇒
/schemas/QAS_GETXLAT_RESP.VERSION_1">
               <FieldValue>A</FieldValue>
               <ShortName>Active</ShortName>
               <LongName>Active</LongName>
            </PTQASWRK>
         </QAS_GETXLAT_RESP>
         <QAS_GETXLAT_RESP>
            <PTQASWRK class="R" xmlns="http://xmlns.oracle.com/Enterprise/Tools⇒
/schemas/QAS_GETXLAT_RESP.VERSION_1">
               <FieldValue>I</FieldValue>
               <ShortName>Inactive</ShortName>
               <LongName>Inactive</LongName>
            </PTQASWRK>
         </QAS_GETXLAT_RESP>
      </QAS_GETXLAT_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Executing the Query

This section provides an overview of the XML string and discusses the service operations to execute a query. These include:

QAS_EXECUTEQRYSYNC_OPER
QAS_EXECUTEQRYSYNCPOLL_OPER
QAS_EXECUTEQRYASYNC_OPER

XML String

When you request to execute a query, you can either select the query name if it exists in the database, or use the XML string to request an adhoc query. The same elements are used to define the XML string as defining the query to save. If you use XML string, it must contain at least one record, one field and one select.

<Records> Request must contain at least one record.

Records Elements	Description
RCDNUM	Record number.
RCDNAME	Record name.
RCDSELECTNUM	Select number.
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.

<Fields> Request must contain at least one field.

Fields Elements	Description
FIELDNUM	Field number.
FIELDNAME	Field name.
FIELDRCDNUM	Record number.
DESCR	Description.
AGGREGRATE_TYPE	Aggregation type. Note. For valid values, see QAS_SAVE_QUERY_OPER.
HEADING_TYPE	Heading type. Note. For valid values, see QAS_SAVE_QUERY_OPER.
HEADING	If the HEADING_TYPE is Heading, this element is used to enter the heading text.
COLUMNNUM	Column number.
ORDERBYNUM	Order by number.
ORDERBYDIR	Direction of field ordering. Note. For valid values, see QAS_SAVE_QUERY_OPER.
FIELDSELECTNUM	Identifier of SELECT in which this field is included.
EXPRESSION_AS_FIELD	A valid expression number when an expression is used as a field.

<Criteria> Enter criteria information.

Criteria Elements	Description
CRTNUM	Criterion number.
CRTNAME	Criterion name.
CRTHAVINGFLAG	True if specifying a HAVING clause. Note. For valid values, see QAS_SAVE_QUERY_OPER.
CRTSELECTNUM	Selectnumber.
CRTNEGATION	Negation in criterion. Note. For valid values, see QAS_SAVE_QUERY_OPER.
CONDITION_TYPE	Condition type in criterion. Note. For valid values, see QAS_SAVE_QUERY_OPER.
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.
CRTEXP1TEXT	Expression text for criterion 1.
CRTEXP1RCDALIAS	Record alias for record used in expression 1 in criterion.
CRTEXP1FIELD	Field used in expression 1 for criterion.
CRTEXP2RCDALIAS	Record alias for record used in expression 2 for criterion.
CRTEXP2FIELD	Field used in expression 2 for criterion.
CRTEXP2TYPE	Expression 2 type in criterion. Note. For valid values, see QAS_SAVE_QUERY_OPER.
CRTEXP2TEXT	Expression 2 text.
CRTLOGICALOPER	Logical operator that links the criteria. Note. For valid values, see QAS_SAVE_QUERY_OPER.

<Expressions> Enter expression information.

Expressions Elements	Description
EXPNUM	Expression Number.
EXPSELECTNUM	Expression's SELECT Number.
EXPNAME	Expression Name.
EXPTYPE	Expression Type. Note. For valid values, see QAS_SAVE_QUERY_OPER.
EXPLENGTH	Expression Length.
EXPDECIMALPOS	Number of Decimal Places.
EXPTEXT	Expression Text.
EXPAGGREGATE	Specifies if the expression is an aggregate function.

<Prompts> Enter prompt information.

Prompt Elements	Description
PROMPT_NUM	Prompt number.
PROMPT_NAME	Unique prompt name. This is the UniquePromptName value returned from the service operation QAS_LISTQUERYPROMPTS_OPER.
PROMPT_FLDNAME	Name of the field used as prompt.
PROMPT_UNIQUE_NAME	Unique prompt name.
PROMPT_TABLE	Prompt table
PROMPT_EDITTYPE	Edit type. Note. For valid values, see QAS_SAVE_QUERY_OPER.
PROMPT_HEADING	Heading of prompt.
PROMPT_HEADINGTYPE	Type of prompt heading. Note. For valid values, see QAS_SAVE_QUERY_OPER.
PROMPT_FLDLENGTH	Field length for prompt.
PROMPT_FLDDECIMALPOS	Number of decimal positions in prompt.

<Select> XML String must include at least one select.

Select Elements	Description
SELECTNUM	SELECT number.
PARENTSELECTNUM	Number of parent SELECT. For main SELECT, this must be set to 0.
SELECTTYPE	Type of selection. Note. For valid values, see QAS_SAVE_QUERY_OPER.
QRYDISTINCT	Indicate whether this query is distinct. Note. For valid values, see QAS_SAVE_QUERY_OPER.

QAS_EXECUTEQRYSYNC_OPER

Use this service operation to synchronously execute a query and receive the query results in the format selected in the service operation request. The response message depends on the request was for a file or a non file format.

Request Message: QAS_EXEQRY_SYNC_REQ_MSG

Element Name	Description
QueryName Required element unless you are using XML string	Query name. Required unless you are using an XML string.
isConnectedQuery Required element	Indicate whether this is a connected query. Valid values are Y or N in either upper or lower case. Note. The default value is N.
XMLString	XML string. Note. XML string is used only for ad hoc queries. See XML String for the elements.
OwnerType Required element	Query owner type, public or private in either uppercase or lowercase. Required.
BlockSizeKB Required element	Blocksize is used only for synchronous poll. For synchronous execution, the value should be set to 0 (zero). The default value is 0.
MaxRows Required element	The maximum number of rows to be fetched.
OutResultType Required element	Select the output type. Valid values are: WEBROWSET XMLP EXCEL HTML Note. For connected queries, the output type must be XMLP. See Output Format and Output Type.
OutResultFormat Required element	Select the output format. Valid values are: FILE NONFILE Note. For connected queries, the output format must be FILE.

Element Name

Description

QueryName

Required element unless you are using XML string

Query name. Required unless you are using an XML string.

isConnectedQuery

Required element

Indicate whether this is a connected query. Valid values are Y or N in either upper or lower case.

Note. The default value is N.

XMLString

XML string.

Note. XML string is used only for ad hoc queries. See XML String for the elements.

OwnerType

Required element

Query owner type, public or private in either uppercase or lowercase. Required.

BlockSizeKB

Required element

Blocksize is used only for synchronous poll. For synchronous execution, the value should be set to 0 (zero).

The default value is 0.

MaxRows

Required element

The maximum number of rows to be fetched.

OutResultType

Required element

Select the output type. Valid values are:

WEBROWSET
XMLP
EXCEL
HTML

Note. For connected queries, the output type must be XMLP.

See Output Format and Output Type.

OutResultFormat

Required element

Select the output format. Valid values are:

FILE
NONFILE

Note. For connected queries, the output format must be FILE.

<Prompts> These elements are used for Connected Query or queries containing prompts. .

Prompt Elements	Description
PSQueryName	This field is used only for Connected Query. Enter the name of the query containing the prompt.
UniquePromptName	Unique prompt name defined in the query. This value is case-sensitive. Use the service operation QAS_LISTQUERYPROMPTS_OPER to find the unique prompt name.
FieldValue	Field value for the prompt. This value is case-sensitive. Note. Date fields require the date format as YYYY-MM-DD.

<FieldFilter> If you want to return only specific fields in the query, indicate each field you want returned.

FilterFieldName Element	Description
FilterFieldName	List of field names to be returned. This value is case sensitive and must be the unique field name as returned by the service operation QAS_LISTQUERYFIELDS.

Example Request:

This request will execute the query MSGCAT_PROMPT, the unique name for the prompt is Set and the prompt value is 2.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:⇒
qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_EXEQRY_SYNC_REQ_MSG.⇒
VERSION_1" xmlns:qas1="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_EXEQRY⇒
_SYNC_REQ.⇒
VERSION_1">
   <soapenv:Header/>
   <soapenv:Body>
      <qas:QAS_EXEQRY_SYNC_REQ_MSG>
         <qas1:QAS_EXEQRY_SYNC_REQ>
            <QueryName>MSGCAT_PROMPT</QueryName>
            <isConnectedQuery>N</isConnectedQuery>
            <OwnerType>PUBLIC</OwnerType>
            <BlockSizeKB>0</BlockSizeKB>
            <MaxRow>2</MaxRow>
            <OutResultType>XMLP</OutResultType>
            <OutResultFormat>FILE</OutResultFormat>
            <Prompts>
               <!--Zero or more repetitions:-->
               <PROMPT>
                  <PSQueryName></PSQueryName>
                  <UniquePromptName>BIND1</UniquePromptName>
                  <FieldValue>2</FieldValue>
               </PROMPT>
            </Prompts>
         </qas1:QAS_EXEQRY_SYNC_REQ>
      </qas:QAS_EXEQRY_SYNC_REQ_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_QUERYRESULTS_FILE_RESP

This response is used when the output format requested is FILE.

Element Name	Description
QueryResults	File URL

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>
      <QAS_GETQUERYRESULTS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise⇒
/Tools/⇒
schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
         <QAS_QUERYRESULTS_FILE_RESP xmlns="http://xmlns.oracle.com/Enterprise⇒
/Tools/⇒
schemas/QAS_QUERYRESULTS_FILE_RESP.VERSION_1">
            <FILEURL>http://ple-infodev-08.peoplesoft.com:8010/psreports/QEDMO/⇒
9999957/MSGCAT_PROMPT.xml</FILEURL>
         </QAS_QUERYRESULTS_FILE_RESP>
      </QAS_GETQUERYRESULTS_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_QUERYRESULTS_WRS_RESP

This response is used when the requested output format is NONFILE and the output type is WEBROWSET.

Element Name	Description
QueryResults	Query Result in webrowset format.

Example Response for webrowset:

<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>
      <QAS_GETQUERYRESULTS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise⇒
/Tools⇒
/schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
         <webRowSet xmlns="http://java.sun.com/xml/ns/jdbc">
            <properties>
               <escape-processing>true</escape-processing>
               <fetch-direction>1000</fetch-direction>
               <fetch-size>0</fetch-size>
               <isolation-level>1</isolation-level>
               <key-columns/>
               <map/>
               <max-field-size>0</max-field-size>
               <max-rows>0</max-rows>
               <query-timeout>0</query-timeout>
               <read-only>true</read-only>
               <show-deleted>false</show-deleted>
               <table-name/>
            </properties>
            <metadata>
               <column-count>5</column-count>
               <column-definition>
                  <column-index>1</column-index>
                  <auto-increment>false</auto-increment>
                  <case-sensitive>false</case-sensitive>
                  <currency>false</currency>
                  <nullable>0</nullable>
                  <signed>false</signed>
                  <searchable>false</searchable>
                  <column-display-size>0</column-display-size>
                  <column-label>Set</column-label>
                  <column-name>A.MESSAGE_SET_NBR</column-name>
                  <schema-name/>
                  <column-precision>0</column-precision>
                  <column-scale>0</column-scale>
                  <table-name/>
                  <catalog-name/>
                  <column-type>4</column-type>
                  <column-type-name>INTEGER</column-type-name>
               </column-definition>
               <column-definition>
                  <column-index>2</column-index>
                  <auto-increment>false</auto-increment>
                  <case-sensitive>false</case-sensitive>
                  <currency>false</currency>
                  <nullable>0</nullable>
                  <signed>false</signed>
                  <searchable>false</searchable>
                  <column-display-size>0</column-display-size>
                  <column-label>Descr</column-label>
                  <column-name>A.DESCR</column-name>
                  <schema-name/>
                  <column-precision>0</column-precision>
                  <column-scale>0</column-scale>
                  <table-name/>
                  <catalog-name/>
                  <column-type>12</column-type>
                  <column-type-name>VARCHAR</column-type-name>
               </column-definition>
               <column-definition>
                  <column-index>3</column-index>
                  <auto-increment>false</auto-increment>
                  <case-sensitive>false</case-sensitive>
                  <currency>false</currency>
                  <nullable>0</nullable>
                  <signed>false</signed>
                  <searchable>false</searchable>
                  <column-display-size>0</column-display-size>
                  <column-label>Msg</column-label>
                  <column-name>B.MESSAGE_NBR</column-name>
                  <schema-name/>
                  <column-precision>0</column-precision>
                  <column-scale>0</column-scale>
                  <table-name/>
                  <catalog-name/>
                  <column-type>4</column-type>
                  <column-type-name>INTEGER</column-type-name>
               </column-definition>
               <column-definition>
                  <column-index>4</column-index>
                  <auto-increment>false</auto-increment>
                  <case-sensitive>false</case-sensitive>
                  <currency>false</currency>
                  <nullable>0</nullable>
                  <signed>false</signed>
                  <searchable>false</searchable>
                  <column-display-size>0</column-display-size>
                  <column-label>Message</column-label>
                  <column-name>B.MESSAGE_TEXT</column-name>
                  <schema-name/>
                  <column-precision>0</column-precision>
                  <column-scale>0</column-scale>
                  <table-name/>
                  <catalog-name/>
                  <column-type>12</column-type>
                  <column-type-name>VARCHAR</column-type-name>
               </column-definition>
               <column-definition>
                  <column-index>5</column-index>
                  <auto-increment>false</auto-increment>
                  <case-sensitive>false</case-sensitive>
                  <currency>false</currency>
                  <nullable>0</nullable>
                  <signed>false</signed>
                  <searchable>false</searchable>
                  <column-display-size>0</column-display-size>
                  <column-label>Severity</column-label>
                  <column-name>B.MSG_SEVERITY</column-name>
                  <schema-name/>
                  <column-precision>0</column-precision>
                  <column-scale>0</column-scale>
                  <table-name/>
                  <catalog-name/>
                  <column-type>12</column-type>
                  <column-type-name>VARCHAR</column-type-name>
               </column-definition>
            </metadata>
            <data>
               <currentRow>
                  <columnValue>2</columnValue>
                  <columnValue>PeopleCode</columnValue>
                  <columnValue>1</columnValue>
                  <columnValue>Invalid use of !.</columnValue>
                  <columnValue>E</columnValue>
               </currentRow>
               <currentRow>
                  <columnValue>2</columnValue>
                  <columnValue>PeopleCode</columnValue>
                  <columnValue>2</columnValue>
                  <columnValue>Literal is too long.</columnValue>
                  <columnValue>E</columnValue>
               </currentRow>
            </data>
         </webRowSet>
      </QAS_GETQUERYRESULTS_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_QUERYRESULTS_XMLP_RESP

This response is used when the requested output is NONFILE and the output type is XMLP.

<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>
      <QAS_GETQUERYRESULTS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise⇒
/Tools⇒
/schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
         <query numrows="2" queryname="MSGCAT_PROMPT" xsi:noNamespaceSchema⇒
Location="">
            <row rownumber="1">
               <MESSAGE_SET_NBR>2</MESSAGE_SET_NBR>
               <DESCR>PeopleCode</DESCR>
               <MESSAGE_NBR>1</MESSAGE_NBR>
               <MESSAGE_TEXT>Invalid use of !.</MESSAGE_TEXT>
               <MSG_SEVERITY>E</MSG_SEVERITY>
            </row>
            <row rownumber="2">
               <MESSAGE_SET_NBR>2</MESSAGE_SET_NBR>
               <DESCR>PeopleCode</DESCR>
               <MESSAGE_NBR>2</MESSAGE_NBR>
               <MESSAGE_TEXT>Literal is too long.</MESSAGE_TEXT>
               <MSG_SEVERITY>E</MSG_SEVERITY>
            </row>
         </query>
      </QAS_GETQUERYRESULTS_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>

QAS_EXECUTEQRYSYNCPOLL_OPER

This service operation runs the query request in the Process Scheduler. This service operation should be used for long running queries or queries with large result sets. The query is executed in batch mode through Integration Broker asynchronous servers (Pub/Sub) and the Process Schedulers.

When a client request for QAS_EXECUTEQRY_SYNCPOLL_OPER is received by the Integration Broker, the following occurs:

An asynchronous one way service operation which gets processed by Integration Broker.
The query instance ID (transaction ID) is returned to the requester.

The requester can use the query instance to poll for the status (QAS_QUERYSTATUS_OPER) and to retrieve the query results (QAS_GETQUERYRESULTS_OPER).
The service operation schedules the application engine program QASEXEQRY in the Process Scheduler.

The user ID in the request header must have permission to execute the QASEXEQRY application engine program, as well as permission for the service operation. The PeopleSoft delivered role QAS Admin grants the necessary permissions.

Note. Pub/Sub and Process Scheduler must be running on the application server for the QAS_EXECUTEQRYSYNCPOLL_OPER service operations to be queued and processed.

When the execute query sync poll request is processed, it will validate the query fields and query prompts. If the client application sending the request caches the query definition, the client application can assume the query definition has not changed and it is not necessary to make calls to ListQueryFields and ListQueryPrompts. If the client application receives an error, such as incorrect field name or incorrect prompt value, then the client application should assume that the query definition has been updated. If the query definition has been updated, then the client application will need to make calls to ListQueryFields and ListQueryPrompts.

Request Message: QAS_EXEQRYSYNCPOLL_REQ_MSG

Element Name	Description
QueryName Required element unless you are using XML string	Query name.
isConnectedQuery Required element	Indicate whether this is a connected query. Valid values are Y and N.
XMLString	XML string. Note. See XML String.
OwnerType Required element	Query owner type.
BlockSizeKB Required element	If the output format is nonfile, indicate the block size to use for chunking the response. BlockSizeKB is a number of length 10 with a decimal position of 0. Block size is in bytes. Note. The number of calls made to GetQueryResult reduces as the block size value increases. Thus, increasing performance. See Performance Considerations.
MaxRows Required element	The maximum number of rows to be fetched
OutResultType Required element	Select the output type. Valid values are: WEBROWSET XMLP EXCEL HTML Note. For connected queries, the output type must be XMLP. See Output Format and Output Type.
OutResultFormat Required element	Select the output format. Valid values are: FILE NONFILE Note. For connected queries, the output format must be FILE.

Element Name

Description

QueryName

Required element unless you are using XML string

Query name.

isConnectedQuery

Required element

Indicate whether this is a connected query. Valid values are Y and N.

XMLString

XML string.

Note. See XML String.

OwnerType

Required element

Query owner type.

BlockSizeKB

Required element

If the output format is nonfile, indicate the block size to use for chunking the response. BlockSizeKB is a number of length 10 with a decimal position of 0. Block size is in bytes.

Note. The number of calls made to GetQueryResult reduces as the block size value increases. Thus, increasing performance.

See Performance Considerations.

MaxRows

Required element

The maximum number of rows to be fetched

OutResultType

Required element

Select the output type. Valid values are:

WEBROWSET
XMLP
EXCEL
HTML

Note. For connected queries, the output type must be XMLP.

See Output Format and Output Type.

OutResultFormat

Required element

Select the output format. Valid values are:

FILE
NONFILE

Note. For connected queries, the output format must be FILE.

<Prompts> These elements are used for Connected Query or queries containing prompts. .

Prompt Elements	Description
PSQueryName	This field is used only for Connected Query. Enter the name of the query containing the prompt.
UniquePromptName	Unique prompt name defined in the query. This value is case-sensitive. Use the service operation QAS_LISTQUERYPROMPTS_OPER to find the unique prompt name.
FieldValue	Field value for the prompt. This value is case-sensitive. Note. Date fields require the date format as YYYY-MM-DD.

<FieldFilter> If you want to return only specific fields in the query, indicate each field you want returned.

FilterFieldName Element	Description
FilterFieldName	List of field names to be returned. This value is case sensitive and must be the unique field name as returned by the service operation QAS_LISTQUERYFIELDS.

Example Request:

This request will execute the query XRFWIN with an output type of XMLP and output format of FILE.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:⇒
qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_EXEQRY_SYNCPOLL_REQ_⇒
MSG.VERSION_1" xmlns:qas1="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_⇒
EXEQRY_SYNCPOLL_REQ.VERSION_1">
   <soapenv:Header/>
   <soapenv:Body>
      <qas:QAS_EXEQRY_SYNCPOLL_REQ_MSG>
         <!--Zero or more repetitions:-->
         <qas1:QAS_EXEQRY_SYNCPOLL_REQ>
            <QueryName>XRFWIN</QueryName>
            <isConnectedQuery>n</isConnectedQuery>
            <XMLString/> 
            <OwnerType>public</OwnerType>
            <BlockSizeKB></BlockSizeKB>
            <MaxRow>3</MaxRow>
            <OutResultType>XMLP</OutResultType>
            <OutResultFormat>FILE</OutResultFormat>
        </qas1:QAS_EXEQRY_SYNCPOLL_REQ>
      </qas:QAS_EXEQRY_SYNCPOLL_REQ_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_EXEQRYSYNCPOLL_RESP_MSG

Element Name	Description
QueryInstance	Query instance ID. This instance ID is used for GetQueryStatus, CancelQuery, and GetQueryResult.

Element Name

Description

QueryInstance

Query instance ID.

This instance ID is used for GetQueryStatus, CancelQuery, and GetQueryResult.

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>
      <QAS_EXEQRYSYNCPOLL_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/Tools⇒
/schemas/QAS_EXEQRYSYNCPOLL_RESP_MSG.VERSION_1">
         <QAS_EXEQRYSYNCPOLL_RESP>
            <PTQASWRK class="R" xmlns="http://xmlns.oracle.com/Enterprise/Tools⇒
/schemas/QAS_EXEQRYSYNCPOLL_RESP.VERSION_1">
               <QueryInstance>8995a01e-0a75-11dd-9c24-98a15db6aa18</QueryInstance>
            </PTQASWRK>
         </QAS_EXEQRYSYNCPOLL_RESP>
      </QAS_EXEQRYSYNCPOLL_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>

QAS_EXECUTEQRYASYNC_OPER

QAS supports running a query asynchronously. The request is received and executed. When the query has been completed, the results are asynchronously sent back to the requesting system.

Note. Pub/Sub must be running on the applications server for asynchronous service operations to be queued and processed.

For asynchronous requests, parameters are mandatory in the SOAP header in order to receive the response. This table lists the Reply To elements in the SOAP header:

These are standard parameters for any asynchronous request/response web service.

Element	Description
Address	Address where the http listener is running on the client application. The Address should be valid and should be accessible to Integration Broker in order to return the query result. If not, the results will never be returned.
ReferenceProperties	Any reference properties necessary for the client application.
PortType	Operations of the service.
Any additional elements	Any additional elements necessary for the client application.
MessageID	Unique message ID. Every request to execute query asynchronously should have a different value for MessageID.

This service operation is used to asynchronously request query execution and return the query results. The response message depends on the result type requested (OutResultType).

Request Message: QAS_EXEQRY_ASYNC_REQ_MSG

Element Name	Description
QueryName	Query name.
isConnectedQuery	Indicate whether this is a connected query. Valid values are Y and N.
XMLString	XML string. Note. See XML String for the elements.
OwnerType	Query owner type.
BlockSizeKB Required element	If the output format is nonfile, indicate the block size to use for chunking the response. BlockSizeKB is a number of length 10 with a decimal position of 0. Block size is in bytes. Note. If the BlockSize is 0 all query results are retrieved in 1 block.
MaxRows	The maximum number of rows to be fetched
OutResultType	Select the output type. Valid values are: WEBROWSET XMLP EXCEL HTML Note. For connected queries, the output type must be XMLP.
OutResultFormat	Select the output format. Valid values are: FILE NONFILE Note. For connected queries, the output format must be FILE.

<Prompts> These elements are used for Connected Query or queries containing prompts. .

Prompt Elements	Description
PSQueryName	This field is used only for Connected Query. Enter the name of the query containing the prompt.
UniquePromptName	Unique prompt name defined in the query. This value is case-sensitive. Use the service operation QAS_LISTQUERYPROMPTS_OPER to find the unique prompt name.
FieldValue	Field value for the prompt. This value is case-sensitive. Note. Date fields require the date format as YYYY-MM-DD.

<FieldFilter> If you want to return only specific fields in the query, indicate each field you want returned.

FilterFieldName Element	Description
FilterFieldName	List of field names to be returned. This value is case sensitive and must be the unique field name as returned by the service operation QAS_LISTQUERYFIELDS.

Example Request:

This is an example request to run the query MSGCAT_PROMPT and notify the client application when the request has finished.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:⇒
add="http://schemas.xmlsoap.org/ws/2003/03/addressing" xmlns:qas="http://xmlns.⇒
oracle.com/Enterprise/Tools/schemas/QAS_EXEQRY_ASYNC_REQ_MSG.VERSION_1" xmlns:qas1⇒
="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_EXEQRY_ASYNC_REQ.VERSION_1">
   <soapenv:Header>
      <add:ReplyTo>
         <add:Address>http://ple-indodev-08:8082/qasrefapp/callbackservice⇒
</add:Address>
      </add:ReplyTo>
      <add:MessageID>2ae456542b</add:MessageID>
   </soapenv:Header>
   <soapenv:Body>
      <qas:QAS_EXEQRY_ASYNC_REQ_MSG>
         <qas1:QAS_EXEQRY_ASYNC_REQ>
            <QueryName>xrfwin</QueryName>
            <isConnectedQuery>n</isConnectedQuery>
           <OwnerType>public</OwnerType>
            <BlockSizeKB>0</BlockSizeKB>
            <MaxRow></MaxRow>
            <OutResultType>xmlp</OutResultType>
            <OutResultFormat>NONFILE</OutResultFormat>
          </qas1:QAS_EXEQRY_ASYNC_REQ>
      </qas:QAS_EXEQRY_ASYNC_REQ_MSG>
   </soapenv:Body>
</soapenv:Envelope>

For Webrowset , the Response Message is QAS_EXECUTEQRYASYNC_RESP_MSG: QAS_QUERYRESULTS_WSR_RESP

Element Name	Description
QueryResults	Query Result in webrowset format

Example Response:

For File, the Response Message is QAS_QUERYRESULTS_FILE_RESP.

Element Name	Description
FILEURL	File URL

Retrieving Query Results

The service operation QAS_GETQUERYRESULTS_OPER is used only to retrieve the query results for the QAS_EXECUTEQRYSYNCPOLL_OPER service call.

Query results are stored either in message segments or in an XML file. Segments can contain rows of data (nonrowset-based messages). When the query result is stored in the XML file, QAS will return the URL of the file location.

QAS_GETQUERYRESULTS_OPER

This service operation is used to retrieve the query results.

Request Message: QAS_GETQUERYRESULTS_REQ_MSG

Element Name	Description
BlockNumber	The number of blocks to be retrieved. Query result is retrieved starting from block 1 in sequential order until the final block is retrieved. Note. The block that contains status finalBlockRetrieved is the final block.
QueryInstance	Query instance ID.

Element Name

Description

BlockNumber

The number of blocks to be retrieved.

Query result is retrieved starting from block 1 in sequential order until the final block is retrieved.

Note. The block that contains status finalBlockRetrieved is the final block.

QueryInstance

Query instance ID.

Example Request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:⇒
qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_GETQUERYRESULTS_REQ_⇒
MSG.VERSION_1" xmlns:qas1="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_⇒
GETQUERYRESULTS_REQ.VERSION_1">
   <soapenv:Header/>
   <soapenv:Body>
      <qas:QAS_GETQUERYRESULTS_REQ_MSG>
         <!--Zero or more repetitions:-->
         <qas:QAS_GETQUERYRESULTS_REQ>
            <qas1:PTQASWRK class="R">
               <qas1:BlockNumber>1</qas1:BlockNumber>
               <qas1:QueryInstance>8995a01e-0a75-11dd-9c24-98a15db6aa18</qas1:⇒
QueryInstance>
            </qas1:PTQASWRK>
         </qas:QAS_GETQUERYRESULTS_REQ>
      </qas:QAS_GETQUERYRESULTS_REQ_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_GETQUERYRESULTS_RESP_MSG

Depending on the output format requested, the response will be either a file URL or the query result in webrowset or XMLP format.

Element Name	Description
FILEURL	Returns FILE URL.
status	If the output format is file, the return status will be either success or failure. If the output format is non file the return status will be one of the following: running or queued indicates that clients have to continue polling for the same block until a status blockRetrieved or finalBlockRetrieved is returned. blockRetrieved indicates that the clients have received 1 block of query result and will have to poll for the next block. finalBlockRetrieved indicates that there are no more blocks to retrieve and clients can stop polling for results.

Element Name

Description

FILEURL

Returns FILE URL.

status

If the output format is file, the return status will be either success or failure.

If the output format is non file the return status will be one of the following:

running or queued indicates that clients have to continue polling for the same block until a status blockRetrieved or finalBlockRetrieved is returned.
blockRetrieved indicates that the clients have received 1 block of query result and will have to poll for the next block.
finalBlockRetrieved indicates that there are no more blocks to retrieve and clients can stop polling for results.

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>
      <QAS_GETQUERYRESULTS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise⇒
/Tools/schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
         <QAS_QUERYRESULTS_FILE_RESP xmlns="http://xmlns.oracle.com/Enterprise⇒
/Tools/schemas/QAS_QUERYRESULTS_FILE_RESP.VERSION_1">
            <FILEURL>http://ple-infodev-09.peoplesoft.com:8010/psreports/QEDMO⇒
/9999911/XRFWIN.xml</FILEURL>
            <status>success</status>
         </QAS_QUERYRESULTS_FILE_RESP>
      </QAS_GETQUERYRESULTS_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Canceling a Query

The service operation QAS_CANCELQUERY_OPER is used to cancel a query after execution. You need to cancel a query using the query instance ID to clean up the PSQASRUN table. The QAS Administration page can also be used to clear any orphaned rows in the PSQASRUN table.

See Using QAS Administration.

QAS_CANCELQUERY_OPER

This service operation is used to cancel an executed query.

Request Message: QAS_CANCELQUERY_REQ_MSG

Element Name	Description
QueryInstanceID	Query Instance ID

Example Request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:⇒
qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_CANCELQUERY_REQ_⇒
MSG.VERSION_1" xmlns:qas1="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_⇒
CANCELQUERY_REQ.VERSION_1">
   <soapenv:Header/>
   <soapenv:Body>
      <qas:QAS_CANCELQUERY_REQ_MSG>
         <qas:QAS_CANCELQUERY_REQ>
            <qas1:PTQASWRK class="R">
               <qas1:QueryInstanceID>ab621810-5437-11dd-83cd-a32b27c3af6a</qas1:⇒
QueryInstanceID>
            </qas1:PTQASWRK>
         </qas:QAS_CANCELQUERY_REQ>
      </qas:QAS_CANCELQUERY_REQ_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_CANCELQUERY_RESP_MSG

Element Name	Description
Status	Return status. Valid values are: idNotFound Used when the queryInstanceID isn't correct. fail success

Element Name

Description

Status

Return status. Valid values are:

idNotFound

Used when the queryInstanceID isn't correct.
fail
success

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>
      <QAS_CANCELQUERY_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/Tools⇒
/schemas/QAS_CANCELQUERY_RESP_MSG.VERSION_1">
         <QAS_CANCELQUERY_RESP>
            <PTQASSTATWRK class="R" xmlns="http://xmlns.oracle.com/Enterprise⇒
/Tools/schemas/QAS_CANCELQUERY_RESP.VERSION_1">
               <Status>success</Status>
            </PTQASSTATWRK>
         </QAS_CANCELQUERY_RESP>
      </QAS_CANCELQUERY_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Retrieving Query Status

The service operation QAS_QUERYSTATUS_OPER is used to return the status of query execution. This service operation returns the QAS status, not the Process Scheduler status.

See Process Request Run Status.

QAS_QUERYSTATUS_OPER

This service operation returns current query status.

Request Message: QAS_QUERYSTATUS_REQ_MSG

Element Name	Description
QueryInstanceID	Query instance ID.

Example Request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:⇒
qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_QUERYSTATUS_REQ_⇒
MSG.VERSION_1" xmlns:qas1="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_⇒
QUERYSTATUS_REQ.VERSION_1">
   <soapenv:Header/>
   <soapenv:Body>
      <qas:QAS_QUERYSTATUS_REQ_MSG>
         <qas:QAS_QUERYSTATUS_REQ>
            <qas1:PTQASWRK class="R">
               <qas1:QueryInstanceID>64ef3e36-5cf6-11dd-a1b6-cf9c0ed84831</qas1:⇒
QueryInstanceID>
            </qas1:PTQASWRK>
         </qas:QAS_QUERYSTATUS_REQ>
      </qas:QAS_QUERYSTATUS_REQ_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_QUERYSTATUS_RESP_MSG

Element Name	Description
Status	Returns query status. Values are: idNotFound running killed posting last queued success error
NumCols	Number of columns.
TotalRows	Total number of rows.
TotalBlocks	Always returns 0.
TotalBytes	Total number of bytes.

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>
      <QAS_QUERYSTATUS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/Tools⇒
/schemas/QAS_QUERYSTATUS_RESP_MSG.VERSION_1">
         <QAS_QUERYSTATUS_RESP>
            <PTQASSTATWRK class="R" xmlns="http://xmlns.oracle.com/Enterprise⇒
/Tools/schemas/QAS_QUERYSTATUS_RESP.VERSION_1">
               <Status>running</Status>
               <NumColumns>0</NumColumns>
               <TotalBlocks>0</TotalBlocks>
               <TotalBytes>0</TotalBytes>
               <TotalRows>0</TotalRows>
            </PTQASSTATWRK>
         </QAS_QUERYSTATUS_RESP>
      </QAS_QUERYSTATUS_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>