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-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>
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-14.45.06.000000</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>