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 SOAP based service operation:

    • QAS_LISTQUERY_OPER

    • QAS_LISTQUERY_REST_GET

    • QAS_QUERY_DETAILS_OPER

  • QAS RESTful based service operation:

    • QAS_LISTQUERY_REST_GET

    • QAS_QUERY_DETAILS_REST_GET

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 orprivate 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>

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_TEMPL

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 orprivate 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.

json_response

Enter true for JSON response type; enter false for standard XML response. If no value is provided, the default is standard XML response.

Example Request

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

https://<servername>:<port>/PSIGW/RESTListeningConnector/<defaultlocal node>/ListQuery.v1/public/listquery?search=QE&maxrows=1000&isconnectedquery=N

Example of an URI template

{OwnerType}/listquery?search={SearchString}&maxrows={MaxRows}&isconnectedquery={IsConnectedQry}&json_resp={json_response}

Response Message: QAS_LISTQUERY_RESP_MSG

Example Response

When the parameter json_response is set to false:

<?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>QECECHAR</QueryName>
            <OwnerType>public</OwnerType>
            <Description>Query for CE Prompt Testing</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>QECEDATE</QueryName>
            <OwnerType>public</OwnerType>
            <Description>Query for CE Prompt Testing</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>QECEDESC</QueryName>
            <OwnerType>public</OwnerType>
            <Description>Query for CE Prompt Testing</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>QECEDTTM</QueryName>
            <OwnerType>public</OwnerType>
            <Description>Query for CE Prompt Testing</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>QECENUM</QueryName>
            <OwnerType>public</OwnerType>
            <Description>Query for CE Prompt Testing</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>QECESNUM</QueryName>
            <OwnerType>public</OwnerType>
            <Description>Query for CE Prompt Testing</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>QENVSPROMPT1</QueryName>
            <OwnerType>public</OwnerType>
            <Description>nVision Single Prompt Testing</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>QENVSPROMPT2</QueryName>
            <OwnerType>public</OwnerType>
            <Description>nVision Multi Prompt Testing</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>QETOWORD</QueryName>
            <OwnerType>public</OwnerType>
            <Description>CRW GLOBAL STRING TEST</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>QE_ALL_CAT_Q</QueryName>
            <OwnerType>public</OwnerType>
            <Description/>
            <isConnectedQry>N</isConnectedQry>
        </PTQASWRK>
    </QAS_LISTQUERY_RESP>
</QAS_LISTQUERY_RESP_MSG>

Example Response

When the parameter json_response is set to true:

{
"status": "success","data": 
 {"Query": 
  [{"queryName": "QECECHAR","ownerType": "public","description": 
     "Query for CE Prompt Testing","isConnectedQry": false},

    {"queryName": "QECEDATE","ownerType": "public","description":
     "Query for CE Prompt Testing","isConnectedQry": false},

    {"queryName": "QECEDESC","ownerType": "public","description":
     "Query for CE Prompt Testing","isConnectedQry": false},

    {"queryName": "QECEDTTM","ownerType": "public","description":
     "Query for CE Prompt Testing","isConnectedQry": false},

    {"queryName": "QECENUM","ownerType": "public","description":
     "Query for CE Prompt Testing","isConnectedQry": false},

     {"queryName": "QECESNUM","ownerType": "public","description":
     "Query for CE Prompt Testing","isConnectedQry": false}     ]}
  }

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 andN.

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, orUnion.

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, returnsFalse.

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 orExpression.

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-192.0.2.000001</CREATION_TIME>
         <LAST_UPDATE_TIME>2009-05-15-192.0.2.000240</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>

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

Request Message: QAS_QUERY_DETAILS_TEMPL

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 andN.

The default value is N.

Example Request:

https://<servername>:<port>/PSIGW/RESTListeningConnector/<defaultlocal node>/QueryDetails.v1/XRFWIN?isconnectedquery=N

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, orUnion.

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, returnsFalse.

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 orExpression.

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:

<?xml version="1.0"?>
<qcs:QAS_QUERY_DETAILS_RESP_MSG xmlns:qcs="http://xmlns.oracle.com/
Enterprise/Tools/schemas/QAS_QUERY_DETAILS_RESP_MSG.VERSION_1">
    <QUERY_NAME>XRFWIN</QUERY_NAME>
    <DESCRIPTION>XRFWIN</DESCRIPTION>
    <DESCRLONG/>
    <QUERY_OWNER>, Public</QUERY_OWNER>
    <CREATION_TIME/>
    <LAST_UPDATE_TIME>2003-10-30-192.0.2.000120</LAST_UPDATE_TIME>
    <SELECT>
        <SELECTNUM>1</SELECTNUM>
        <PARENTSELECTNUM>0</PARENTSELECTNUM>
        <SELECTTYPE>Main</SELECTTYPE>
        <QRYDISTINCT>False</QRYDISTINCT>
    </SELECT>
    <RECORD>
        <RCDNUM>1</RCDNUM>
        <RCDNAME>PSMENUDEFN</RCDNAME>
        <RCDSELECTNUM>1</RCDSELECTNUM>
        <CORRNAME>A</CORRNAME>
    </RECORD>
    <FIELD>
        <FIELDNUM>1</FIELDNUM>
        <FIELDNAME>MENUNAME</FIELDNAME>
        <DESCR>Menu Name</DESCR>
        <FORMAT>CHAR 30</FORMAT>
        <AGGREGATE_TYPE>None</AGGREGATE_TYPE>
        <HEADING_TYPE>RFT Long</HEADING_TYPE>
        <HEADING>Menu Name</HEADING>
        <FIELDSELECTNUM>1</FIELDSELECTNUM>
        <ORDERBYNUM>1</ORDERBYNUM>
        <ORDERBYDIR>Ascending</ORDERBYDIR>
        <COLUMNNUM>1</COLUMNNUM>
        <FIELDTYPE>Character</FIELDTYPE>
        <FIELDRCDALIAS>A</FIELDRCDALIAS>
    </FIELD>
</qcs:QAS_QUERY_DETAILS_RESP_MSG>