Selecting Records
This section describes the service operations that are available for selecting records. These include:
QAS Soap based services:
QAS_RECORDS_OPER
QAS_RECORD_DEFN_OPER
QAS_HIERARCHY_RECORDS_OPER
QAS_RELATED_RECORDS_OPER
QAS_CREATE_CPQ_OPER
QAS REST based Services :
QAS_RECORDS_REST_GET
QAS_RECORD_DEFN_REST_GET
QAS_HIERARCHY_RECORDS_REST_GET
QAS_RELATED_RECORDS_REST_GET
Use this service operation to select a list of records, along with the descriptions that you can access.
Request Message: QAS_RECORDS_REQ_MSG
Element Name |
Description |
---|---|
SEARCH_STRING Required element |
Search string used for specifying the name or the first few characters of the search criterion definition name. If no value is entered, a list of all accessible records will be returned. Note: For field names, QAS does not interpret the underscore as a wildcard. For example, if you enter NODE_ andFieldName for the SEARCH_CRITERION, QAS will retrieve records that contain fields such asNODE_TYPE and notNODECOUNT. |
SEARCH_CRITERION Required element |
Valid search criteria are:
In the SEARCH_STRING element, enter the record name, field name, or description. Record name is the default criterion. |
Example Request:
This request will return a list of records that start with PSMSGC.
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_RECORDS_REQ_MSG.VERSION_1">
<soapenv:Header/>
<soapenv:Body>
<qas:QAS_RECORDS_REQ_MSG>
<SEARCH_STRING>PSMSGC</SEARCH_STRING>
<SEARCH_CRITERION>RecordName</SEARCH_CRITERION>
</qas:QAS_RECORDS_REQ_MSG>
</soapenv:Body>
</soapenv:Envelope>
Response Message: QAS_RECORDS_RESP_MSG
Element Name |
Description |
---|---|
RECORD_NAME |
Record name. |
RECORD_DESCRIPTION |
Record description. |
INFORMATION_MESSAGE |
Returns information about the request. For example if the criterion in the request is incorrect or if no records meet the criteria, a message indicating the error is returned. |
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_RECORDS_RESP_MSG xmlns:qcs="http://xmlns.oracle.com/
Enterprise/Tools/schemas/QAS_RECORDS_RESP_MSG.VERSION_1">
<RECORD>
<RECORD_NAME>PSMSGCATDEFN</RECORD_NAME>
<RECORD_DESCRIPTION>Message Catalog</RECORD_DESCRIPTION>
</RECORD>
</qcs:QAS_RECORDS_RESP_MSG>
</soapenv:Body>
</soapenv:Envelope>
Use this service operation to select a list of records, along with the descriptions that you can access.
Request Message: QAS_RECORDS_TEMPL
Element Name |
Description |
---|---|
SEARCH_STRING Required element |
Search string used for specifying the name or the first few characters of the search criterion definition name. If no value is entered, a list of all accessible records will be returned. |
SEARCH_CRITERION Required element |
Valid search criteria are:
In the SEARCH_STRING element, enter the record name, field name, or description. Record name is the default criterion. |
Instead of Request Messages, please use the format outlined in the doc "QAS Rest Services Test Case Document.doc" with slight modifications outlined below: REST Base URL(use placeholders for server name, port and node as below rather than format in doc): https://<servername>:<port>/PSIGW/RESTListeningConnector/<default local node>/Records.v1/ Available URI Templates (exactly as in doc)... Parameters (rather than "Variables" in doc):... Also, only put in the "Required element" if in my doc there is an asterisk there beside that parameter, "*") From "Response Message" on, it looks good..
Example Request:
https://<servername>:<port>/PSIGW/RESTListeningConnector/<default local node>/Records.v1/getrecords/RecordName?search=PSMSGC
Response Message: QAS_RECORDS_RESP_MSG
Element Name |
Description |
---|---|
RECORD_NAME |
Record name. |
RECORD_DESCRIPTION |
Record description. |
INFORMATION_MESSAGE |
Returns information about the request. For example if the criterion in the request is incorrect or if no records meet the criteria, a message indicating the error is returned. |
Example Response:
<?xml version="1.0"?>
<qcs:QAS_RECORDS_RESP_MSG xmlns:qcs="http://xmlns.oracle.com/Enterprise/
Tools/schemas/QAS_RECORDS_RESP_MSG.VERSION_1">
<RECORD>
<RECORD_NAME>PSMSGCATDEFN</RECORD_NAME>
<RECORD_DESCRIPTION>Message Catalog</RECORD_DESCRIPTION>
</RECORD>
</qcs:QAS_RECORDS_RESP_MSG>
Use this service operation to return the definition of a given record, including field descriptions and key information.
Request Message: QAS_RECORD_DEFN_REQ_MSG
Element Name |
Description |
---|---|
RECORD_NAME Required element |
Complete record name. Required. |
Example Request:
This request will return the record definition for PSMSGCATDEFN.
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_RECORD_DEFN_REQ_MSG.VERISON_1">
<soapenv:Header/>
<soapenv:Body>
<qas:QAS_RECORD_DEFN_REQ_MSG>
<RECORD_NAME>psmsgcatdefn</RECORD_NAME>
</qas:QAS_RECORD_DEFN_REQ_MSG>
</soapenv:Body>
</soapenv:Envelope>
Response Message: QAS_RECORD_DEFN_RESP_MSG
Element Name |
Description |
---|---|
RECORD_NAME |
Record name. |
FIELD_NAME |
Field name. |
FIELD_DESCRIPTION |
Field description. |
KEY |
Indicates whether this field is a key; value will be Yes orNo. |
FIELD_TYPE |
Field type. |
FIELD_LENGTH |
Field length. |
FIELD_LOOKUP_TABLE |
Prompt table name, if applicable. |
FIELD_EDIT_TYPE |
Field edit type is a 32-bit binary number. Counting from 1 from the right, a 1 in bit 10 indicates translate values, a 1 in bit 14 indicates a yes/no table, and a 1 in bit 15 indicates a prompt table. Client applications need to do bit-wise AND on this value. For example, the value 2337 represents:
See Flag. |
FIELD_SHORT_NAME |
Short name. |
FIELD_LONG_NAME |
Long name. |
INFORMATION_MESSAGE |
Returns information about the request. For example if the criterion in the request is incorrect or if no records meet the criteria, a message indicating the error is returned. |
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_RECORD_DEFN_RESP_MSG xmlns:qcs="http://xmlns.oracle.com/
Enterprise/Tools/schemas/QAS_RECORD_DEFN_RESP_MSG.VERSION_1">
<RECORD_NAME>PSMSGCATDEFN</RECORD_NAME>
<FIELD>
<FIELD_NAME>MESSAGE_SET_NBR</FIELD_NAME>
<FIELD_DESCRIPTION>Message Set Number</FIELD_DESCRIPTION>
<KEY>Yes</KEY>
<FIELD_TYPE>Number</FIELD_TYPE>
<FIELD_LENGTH>5</FIELD_LENGTH>
<FIELD_LOOKUP_TABLE/>
<FIELD_EDIT_TYPE>2337</FIELD_EDIT_TYPE>
<FIELD_SHORT_NAME>Set</FIELD_SHORT_NAME>
<FIELD_LONG_NAME>Message Set Number</FIELD_LONG_NAME>
</FIELD>
<FIELD>
<FIELD_NAME>MESSAGE_NBR</FIELD_NAME>
<FIELD_DESCRIPTION>Message Number</FIELD_DESCRIPTION>
<KEY>Yes</KEY>
<FIELD_TYPE>Number</FIELD_TYPE>
<FIELD_LENGTH>5</FIELD_LENGTH>
<FIELD_LOOKUP_TABLE/>
<FIELD_EDIT_TYPE>2337</FIELD_EDIT_TYPE>
<FIELD_SHORT_NAME>Msg</FIELD_SHORT_NAME>
<FIELD_LONG_NAME>Message Number</FIELD_LONG_NAME>
</FIELD>
<FIELD>
<FIELD_NAME>MESSAGE_TEXT</FIELD_NAME>
<FIELD_DESCRIPTION>Message Text</FIELD_DESCRIPTION>
<KEY>No</KEY>
<FIELD_TYPE>Character</FIELD_TYPE>
<FIELD_LENGTH>100</FIELD_LENGTH>
<FIELD_LOOKUP_TABLE/>
<FIELD_EDIT_TYPE>33554720</FIELD_EDIT_TYPE>
<FIELD_SHORT_NAME>Message</FIELD_SHORT_NAME>
<FIELD_LONG_NAME>Message Text</FIELD_LONG_NAME>
</FIELD>
.....
</qcs:QAS_RECORD_DEFN_RESP_MSG>
</soapenv:Body>
</soapenv:Envelope>
Use this service operation to return the definition of a given record, including field descriptions and key information.
Request Message: QAS_RECORD_DEFN_TEMPL
Element Name |
Description |
---|---|
RECORD_NAME Required element |
Complete record name. Required. |
Example Request:
This request will return the record definition for PSMSGCATDEFN.
https://<servername>:<port>/PSIGW/RESTListeningConnector/<default local node>/RecordDefn.v1/PSMSGCATDEFN
Response Message: QAS_RECORD_DEFN_RESP_MSG
Element Name |
Description |
---|---|
RECORD_NAME |
Record name. |
FIELD_NAME |
Field name. |
FIELD_DESCRIPTION |
Field description. |
KEY |
Indicates whether this field is a key; value will be Yes orNo. |
FIELD_TYPE |
Field type. |
FIELD_LENGTH |
Field length. |
FIELD_LOOKUP_TABLE |
Prompt table name, if applicable. |
FIELD_EDIT_TYPE |
Field edit type is a 32-bit binary number. Counting from 1 from the right, a 1 in bit 10 indicates translate values, a 1 in bit 14 indicates a yes/no table, and a 1 in bit 15 indicates a prompt table. Client applications need to do bit-wise AND on this value. For example, the value 2337 represents:
See Flag. |
FIELD_SHORT_NAME |
Short name. |
FIELD_LONG_NAME |
Long name. |
INFORMATION_MESSAGE |
Returns information about the request. For example if the criterion in the request is incorrect or if no records meet the criteria, a message indicating the error is returned. |
Example Response:
<?xml version="1.0"?>
<qcs:QAS_RECORD_DEFN_RESP_MSG xmlns:qcs="http://xmlns.oracle.com/
Enterprise/Tools/schemas/QAS_RECORD_DEFN_RESP_MSG.VERSION_1">
<RECORD_NAME>PSMSGCATDEFN</RECORD_NAME>
<FIELD>
<FIELD_NAME>MESSAGE_SET_NBR</FIELD_NAME>
<FIELD_DESCRIPTION>Message Set Number</FIELD_DESCRIPTION>
<KEY>Yes</KEY>
<FIELD_TYPE>Number</FIELD_TYPE>
<FIELD_LENGTH>5</FIELD_LENGTH>
<FIELD_LOOKUP_TABLE/>
<FIELD_EDIT_TYPE>2337</FIELD_EDIT_TYPE>
<FIELD_SHORT_NAME>Set</FIELD_SHORT_NAME>
<FIELD_LONG_NAME>Message Set Number</FIELD_LONG_NAME>
</FIELD>
<FIELD>
<FIELD_NAME>MESSAGE_NBR</FIELD_NAME>
<FIELD_DESCRIPTION>Message Number</FIELD_DESCRIPTION>
<KEY>Yes</KEY>
<FIELD_TYPE>Number</FIELD_TYPE>
<FIELD_LENGTH>5</FIELD_LENGTH>
<FIELD_LOOKUP_TABLE/>
<FIELD_EDIT_TYPE>2337</FIELD_EDIT_TYPE>
<FIELD_SHORT_NAME>Msg</FIELD_SHORT_NAME>
<FIELD_LONG_NAME>Message Number</FIELD_LONG_NAME>
</FIELD>
<FIELD>
<FIELD_NAME>MESSAGE_TEXT</FIELD_NAME>
<FIELD_DESCRIPTION>Message Text</FIELD_DESCRIPTION>
<KEY>No</KEY>
<FIELD_TYPE>Character</FIELD_TYPE>
<FIELD_LENGTH>100</FIELD_LENGTH>
<FIELD_LOOKUP_TABLE/>
<FIELD_EDIT_TYPE>33554720</FIELD_EDIT_TYPE>
<FIELD_SHORT_NAME>Message</FIELD_SHORT_NAME>
<FIELD_LONG_NAME>Message Text</FIELD_LONG_NAME>
</FIELD>
<FIELD>
<FIELD_NAME>MSG_SEVERITY</FIELD_NAME>
<FIELD_DESCRIPTION>Message Severity</FIELD_DESCRIPTION>
<KEY>No</KEY>
<FIELD_TYPE>Character</FIELD_TYPE>
<FIELD_LENGTH>1</FIELD_LENGTH>
<FIELD_LOOKUP_TABLE/>
<FIELD_EDIT_TYPE>768</FIELD_EDIT_TYPE>
<FIELD_SHORT_NAME>Severity</FIELD_SHORT_NAME>
<FIELD_LONG_NAME>Message Severity</FIELD_LONG_NAME>
</FIELD>
<FIELD>
<FIELD_NAME>LAST_UPDATE_DTTM</FIELD_NAME>
<FIELD_DESCRIPTION>Last Update Timestamp</FIELD_DESCRIPTION>
<KEY>No</KEY>
<FIELD_TYPE>Datetime</FIELD_TYPE>
<FIELD_LENGTH>26</FIELD_LENGTH>
<FIELD_LOOKUP_TABLE/>
<FIELD_EDIT_TYPE>33024</FIELD_EDIT_TYPE>
<FIELD_SHORT_NAME>Last Updt</FIELD_SHORT_NAME>
<FIELD_LONG_NAME>Last Update Timestamp</FIELD_LONG_NAME>
</FIELD>
<FIELD>
<FIELD_NAME>DESCRLONG</FIELD_NAME>
<FIELD_DESCRIPTION>Description</FIELD_DESCRIPTION>
<KEY>No</KEY>
<FIELD_TYPE>Long Character</FIELD_TYPE>
<FIELD_LENGTH>0</FIELD_LENGTH>
<FIELD_LOOKUP_TABLE/>
<FIELD_EDIT_TYPE>33554432</FIELD_EDIT_TYPE>
<FIELD_SHORT_NAME>Descr</FIELD_SHORT_NAME>
<FIELD_LONG_NAME>Description</FIELD_LONG_NAME>
</FIELD>
</qcs:QAS_RECORD_DEFN_RESP_MSG>
Use this service operation to return the list of hierarchy records, if any, along with their descriptions for the requested record.
Request Message: QAS_HIERARCHY_RECORDS_REQ_MSG
Element Name |
Description |
---|---|
RECORD_NAME Required element |
Complete record name. Required. |
Example Request:
This request will return a list of all hierarchy records for PSMSGCATDEFN.
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_HIERARCHY_RECORDS_REQ_MSG.VERSION_1">
<soapenv:Header/>
<soapenv:Body>
<qas:QAS_HIERARCHY_RECORDS_REQ_MSG>
<RECORD_NAME>PSMSGCATDEFN</RECORD_NAME>
</qas:QAS_HIERARCHY_RECORDS_REQ_MSG>
</soapenv:Body>
</soapenv:Envelope>
Response Message: QAS_HIERARCHY_RECORDS_RESP_MSG
Element Name |
Description |
---|---|
RECORD_NAME |
Record name. |
RECORD_DESCRIPTION |
Record description. |
INFORMATION_MESSAGE |
Returns information about the request. For example if the criterion in the request is incorrect or if no records meet the criteria, a message indicating the error is returned. |
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_HIERARCHY_RECORDS_RESP_MSG
xmlns:qcs="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_HIERARCHY_RECORDS_RESP_MSG.VERSION_1">
<RECORD>
<RECORD_NAME>PSMSGSETDEFN</RECORD_NAME>
<RECORD_DESCRIPTION>Message Sets</RECORD_DESCRIPTION>
</RECORD>
<RECORD>
<RECORD_NAME>PSMSGCATDEFN</RECORD_NAME>
<RECORD_DESCRIPTION>Message Catalog</RECORD_DESCRIPTION>
</RECORD>
<RECORD>
<RECORD_NAME>MSG_CAT_VW</RECORD_NAME>
<RECORD_DESCRIPTION>Message Catalog Trans. View</RECORD_DESCRIPTION>
</RECORD>
</qcs:QAS_HIERARCHY_RECORDS_RESP_MSG>
</soapenv:Body>
</soapenv:Envelope>
Use this service operation to return the list of hierarchy records, if any, along with their descriptions for the requested record.
Request Message: QAS_HIERARCHY_RECORDS_TEMPL
Element Name |
Description |
---|---|
RECORD_NAME Required element |
Complete record name. Required. |
Example Request:
This request will return a list of all hierarchy records for PSMSGCATDEFN.
https://<servername>:<port>/PSIGW/RESTListeningConnector/<default local node>/HierarchyRecords.v1/PSMSGCATDEFN
Response Message: QAS_HIERARCHY_RECORDS_RESP_MSG
Element Name |
Description |
---|---|
RECORD_NAME |
Record name. |
RECORD_DESCRIPTION |
Record description. |
INFORMATION_MESSAGE |
Returns information about the request. For example if the criterion in the request is incorrect or if no records meet the criteria, a message indicating the error is returned. |
Example Response:
<?xml version="1.0"?>
<qcs:QAS_HIERARCHY_RECORDS_RESP_MSG xmlns:qcs="http://xmlns.oracle.com/
Enterprise/Tools/schemas/QAS_HIERARCHY_RECORDS_RESP_MSG.VERSION_1">
<RECORD>
<RECORD_NAME>PSMSGSETDEFN</RECORD_NAME>
<RECORD_DESCRIPTION>Message Sets</RECORD_DESCRIPTION>
</RECORD>
<RECORD>
<RECORD_NAME>PSMSGCATDEFN</RECORD_NAME>
<RECORD_DESCRIPTION>Message Catalog</RECORD_DESCRIPTION>
</RECORD>
<RECORD>
<RECORD_NAME>MSG_CAT_VW</RECORD_NAME>
<RECORD_DESCRIPTION>Message Catalog Trans. View</RECORD_DESCRIPTION>
</RECORD>
</qcs:QAS_HIERARCHY_RECORDS_RESP_MSG>
Use this service operation to return a list of related records for all the fields in the requested record.
Request Message: QAS_RELATED_RECORDS_REQ_MSG
Element Name |
Description |
---|---|
RECORD_NAME Required element |
Complete record name. Required. |
Example Request :
This request will return a list of all the related records for QE_EMPLOYEE.
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_RELATED_RECORDS_REQ_MSG.VERSION_1">
<soapenv:Header/>
<soapenv:Body>
<qas:QAS_RELATED_RECORDS_REQ_MSG>
<RECORD_NAME>QE_EMPLOYEE</RECORD_NAME>
</qas:QAS_RELATED_RECORDS_REQ_MSG>
</soapenv:Body>
</soapenv:Envelope>
Response Message: QAS_RELATED_RECORDS_RESP_MSG
Element Name |
Description |
---|---|
FIELD_NAME |
Field for which a related record exists. |
RELATED_RECORD_NAME |
Record name. |
RELATED_RECORD_DESCRIPTION |
Record description. |
INFORMATION_MESSAGE |
Returns information about the request. For example if the criterion in the request is incorrect or if no records meet the criteria, a message indicating the error is returned. |
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_RELATED_RECORDS_RESP_MSG xmlns:qcs=
"http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_RELATED_RECORDS_RESP_MSG.VERSION_1">
<FIELD>
<FIELD_NAME>QE_JOBCODE</FIELD_NAME>
<RELATED_RECORD_NAME>QE_JOBCODE_TBL</RELATED_RECORD_NAME>
<RELATED_RECORD_DESCRIPTION>JobCode Table</RELATED_RECORD_DESCRIPTION>
</FIELD>
<FIELD>
<FIELD_NAME>DEPTID</FIELD_NAME>
<RELATED_RECORD_NAME>QE_DEPT_TBL</RELATED_RECORD_NAME>
<RELATED_RECORD_DESCRIPTION>
QE Data Department Table
</RELATED_RECORD_DESCRIPTION>
</FIELD>
</qcs:QAS_RELATED_RECORDS_RESP_MSG>
</soapenv:Body>
</soapenv:Envelope>
Use this service operation to return a list of related records for all the fields in the requested record.
Request Message: QAS_RELATED_RECORDS_TEMPL
Element Name |
Description |
---|---|
RECORD_NAME Required element |
Complete record name. Required. |
Example Request :
https://<servername>:<port>/PSIGW/RESTListeningConnector/<default local node>/RelatedRecords.v1/QE_EMPLOYEE
Response Message: QAS_RELATED_RECORDS_RESP_MSG
Element Name |
Description |
---|---|
FIELD_NAME |
Field for which a related record exists. |
RELATED_RECORD_NAME |
Record name. |
RELATED_RECORD_DESCRIPTION |
Record description. |
INFORMATION_MESSAGE |
Returns information about the request. For example if the criterion in the request is incorrect or if no records meet the criteria, a message indicating the error is returned. |
Example Response:
<?xml version="1.0"?>
<qcs:QAS_RELATED_RECORDS_RESP_MSG xmlns:qcs="http://xmlns.oracle.com/
Enterprise/Tools/schemas/QAS_RELATED_RECORDS_RESP_MSG.VERSION_1">
<FIELD>
<FIELD_NAME>QE_JOBCODE</FIELD_NAME>
<RELATED_RECORD_NAME>QE_JOBCODE_TBL</RELATED_RECORD_NAME>
<RELATED_RECORD_DESCRIPTION>JobCode Table</RELATED_RECORD_DESCRIPTION>
</FIELD>
<FIELD>
<FIELD_NAME>DEPTID</FIELD_NAME>
<RELATED_RECORD_NAME>QE_DEPT_TBL</RELATED_RECORD_NAME>
<RELATED_RECORD_DESCRIPTION>QE Data Department Table</RELATED_RECORD_DESCRIPTION>
</FIELD>
</qcs:QAS_RELATED_RECORDS_RESP_MSG>
Use this service operation to return a message that contains the SQL of the Composite Query.
Request Message: QAS_ CREATE_CPQ_REQ_MSG
Element Name |
Description |
---|---|
SelectFields |
Contains the select fields from Queries. Fields in this element will show in the SELECT clause of the generated SQL statement in response message. |
FROM |
Contains a list of Queries and the Join Criteria. The sub-elements contain all information used to construct FROM clause in SQL statement of the Composite Query. |
WHERE |
Contains filtering criteria that will be in WHERE clause in the SQL statement of the composite query. |
ORDERBy |
Contains one or more Field element. You can either use FieldName or ColumnNumber to specify columns in OrderBy. |
GROUPBy |
Use FieldName only. |
Example Request :
This request will return the SQL of the composite query.
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_CREATE_CPQ_REQ_MSG.VERSION_1">
<soapenv:Header/>
<soapenv:Body>
<qas:QAS_CREATE_CPQ_REQ_MSG>
<SelectFields>
<FIELD>
<FieldName>D883.A.BUDGET_YR_END_DT</FieldName>
<AggregateType>Min</AggregateType>
</FIELD>
<FIELD>
<FieldName>D962.A.LEDGER</FieldName>
<AggregateType>None</AggregateType>
</FIELD>
<FIELD>
<FieldName>D883.A.DEPT_TENURE_FLG</FieldName>
<AggregateType>None</AggregateType>
</FIELD>
<!--FIELD>
<FieldName>D883.A.BUDGET_YR_END_DT</FieldName>
<AggregateType>None</AggregateType>
</FIELD-->
<FIELD>
<FieldName>D1044.A.CF_ATTRIB_VALUE</FieldName>
<AggregateType>None</AggregateType>
</FIELD>
<FIELD>
<FieldName>D1004.A.TNODE11_DESC</FieldName>
<AggregateType>None</AggregateType>
</FIELD>
<FIELD>
<FieldName>D1004.A.TNODE11</FieldName>
<AggregateType>None</AggregateType>
</FIELD>
</SelectFields>
<FROM>
<QUERY>
<QueryName>OI_LEDGER_QRY</QueryName>
<OwnerType>Public</OwnerType>
<QueryAlias>D962</QueryAlias>
<ParentQueryAlias/>
<JoinType>Inner</JoinType>
</QUERY>
<QUERY>
<QueryName>OI_PROD_TFLT_QRY</QueryName>
<OwnerType>Public</OwnerType>
<QueryAlias>D1004</QueryAlias>
<ParentQueryAlias>D962</ParentQueryAlias>
<JoinType>Inner</JoinType>
<JoinCriteria>
<FieldName>A.PRODUCT</FieldName>
<JoinCriteriaOperator>equal to</JoinCriteriaOperator>
<ParentFieldName>A.PRODUCT</ParentFieldName>
</JoinCriteria>
<JoinCriteria>
<FieldName>A.SETID</FieldName>
<JoinCriteriaOperator>equal to</JoinCriteriaOperator>
<ParentFieldName>B.PRODUCT_SETID</ParentFieldName>
</JoinCriteria>
<JoinCriteria>
<FieldName>A.SETID_TREE</FieldName>
<JoinCriteriaOperator>equal to</JoinCriteriaOperator>
<ParentFieldName>C.SETID</ParentFieldName>
</JoinCriteria>
</QUERY>
<QUERY>
<QueryName>OI_PRODUCT_QRY</QueryName>
<OwnerType>Public</OwnerType>
<QueryAlias>D1044</QueryAlias>
<ParentQueryAlias>D962</ParentQueryAlias>
<JoinType>Inner</JoinType>
<JoinCriteria>
<FieldName>A.PRODUCT</FieldName>
<JoinCriteriaOperator>equal to</JoinCriteriaOperator>
<ParentFieldName>A.PRODUCT</ParentFieldName>
</JoinCriteria>
<JoinCriteria>
<FieldName>A.SETID</FieldName>
<JoinCriteriaOperator>equal to</JoinCriteriaOperator>
<ParentFieldName>B.PRODUCT_SETID</ParentFieldName>
</JoinCriteria>
</QUERY>
<QUERY>
<QueryName>OI_DEPT_QRY</QueryName>
<OwnerType>Public</OwnerType>
<QueryAlias>D883</QueryAlias>
<ParentQueryAlias>D962</ParentQueryAlias>
<JoinType>Inner</JoinType>
<JoinCriteria>
<FieldName>A.SETID</FieldName>
<JoinCriteriaOperator>equal to</JoinCriteriaOperator>
<ParentFieldName>B.DEPT_SETID</ParentFieldName>
</JoinCriteria>
<JoinCriteria>
<FieldName>A.DEPTID</FieldName>
<JoinCriteriaOperator>equal to</JoinCriteriaOperator>
<ParentFieldName>A.DEPTID</ParentFieldName>
</JoinCriteria>
</QUERY>
</FROM>
<WHERE>
<FilterCriteria>
<LogicalOperator/>
<LeftParenthesisLevel>0</LeftParenthesisLevel>
<RightParenthesisLevel>0</RightParenthesisLevel>
<Operand1FieldName>D883.A.EFF_END_DT</Operand1FieldName>
<Operand2Type>Constant</Operand2Type>
<Operand2FieldName/>
<Operand2Text>2014-01-01</Operand2Text>
<Operator>>=</Operator>
</FilterCriteria>
<FilterCriteria>
<LogicalOperator>and</LogicalOperator>
<LeftParenthesisLevel>0</LeftParenthesisLevel>
<RightParenthesisLevel>0</RightParenthesisLevel>
<Operand1FieldName>D1004.A.EFF_END_DT</Operand1FieldName>
<Operand2Type>Constant</Operand2Type>
<Operand2FieldName/>
<Operand2Text>2014-01-01</Operand2Text>
<Operator>>=</Operator>
</FilterCriteria>
<FilterCriteria>
<LogicalOperator>and</LogicalOperator>
<LeftParenthesisLevel>0</LeftParenthesisLevel>
<RightParenthesisLevel>0</RightParenthesisLevel>
<Operand1FieldName>D1044.A.EFF_END_DT</Operand1FieldName>
<Operand2Type>Constant</Operand2Type>
<Operand2FieldName/>
<Operand2Text>2014-01-01</Operand2Text>
<Operator>>=</Operator>
</FilterCriteria>
<FilterCriteria>
<LogicalOperator>and</LogicalOperator>
<LeftParenthesisLevel>0</LeftParenthesisLevel>
<RightParenthesisLevel>0</RightParenthesisLevel>
<Operand1FieldName>D883.A.EFF_ST_DT</Operand1FieldName>
<Operand2Type>Constant</Operand2Type>
<Operand2FieldName/>
<Operand2Text>2014-01-01</Operand2Text>
<Operator><=</Operator>
</FilterCriteria>
<FilterCriteria>
<LogicalOperator>and</LogicalOperator>
<LeftParenthesisLevel>0</LeftParenthesisLevel>
<RightParenthesisLevel>0</RightParenthesisLevel>
<Operand1FieldName>D1004.A.EFF_ST_DT</Operand1FieldName>
<Operand2Type>Constant</Operand2Type>
<Operand2FieldName/>
<Operand2Text>2014-01-01</Operand2Text>
<Operator><=</Operator>
</FilterCriteria>
<FilterCriteria>
<LogicalOperator>and</LogicalOperator>
<LeftParenthesisLevel>0</LeftParenthesisLevel>
<RightParenthesisLevel>0</RightParenthesisLevel>
<Operand1FieldName>D1044.A.EFF_ST_DT</Operand1FieldName>
<Operand2Type>Constant</Operand2Type>
<Operand2FieldName/>
<Operand2Text>2014-01-01</Operand2Text>
<Operator><=</Operator>
</FilterCriteria>
</WHERE>
<OrderBy>
<Field>
<FieldName>D962.A.LEDGER</FieldName>
<ColumnNumber>2</ColumnNumber>
<OrderByDirection>A</OrderByDirection>
</Field>
<Field>
<FieldName>D883.A.DEPT_TENURE_FLG</FieldName>
<ColumnNumber>3</ColumnNumber>
<OrderByDirection>A</OrderByDirection>
</Field>
<Field>
<FieldName>D1044.A.CF_ATTRIB_VALUE</FieldName>
<ColumnNumber>5</ColumnNumber>
<OrderByDirection>A</OrderByDirection>
</Field>
<Field>
<FieldName>D1004.A.TNODE11_DESC</FieldName>
<ColumnNumber>6</ColumnNumber>
<OrderByDirection>A</OrderByDirection>
</Field>
<Field>
<FieldName>D1004.A.TNODE11</FieldName>
<ColumnNumber>7</ColumnNumber>
<OrderByDirection>A</OrderByDirection>
</Field>
</OrderBy>
<GroupBy>
<Field>
<FieldName>D883.A.DEPT_TENURE_FLG</FieldName>
</Field>
<Field>
<FieldName>D962.A.LEDGER</FieldName>
</Field>
<Field>
<FieldName>D1004.A.TNODE11</FieldName>
</Field>
<Field>
<FieldName>D1004.A.TNODE11_DESC</FieldName>
</Field>
<Field>
<FieldName>D1044.A.CF_ATTRIB_VALUE</FieldName>
</Field>
</GroupBy>
</qas:QAS_CREATE_CPQ_REQ_MSG>
</soapenv:Body>
</soapenv:Envelope>
Response Message: QAS_CREATE_CPQ_RESP_MSG
Element Name |
Description |
---|---|
SQL |
SQL statement of Composite Query. |
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_CREATE_CPQ_RESP_MSG xmlns:qcs="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_CREATE_CPQ_RESP_MSG.VERSION_1">
<SQL>SELECT SUM(D101."A.POSTED_TOTAL_AMT"), D86."A.DESCR", D86."A.ACCOUNT", D94."A.DESCR", D94."A.DEPTID"
FROM (SELECT A.BUSINESS_UNIT AS "A.BUSINESS_UNIT", A.LEDGER AS "A.LEDGER", A.ACCOUNT AS "A.ACCOUNT", A.ALTACCT AS "A.ALTACCT", A.DEPTID AS "A.DEPTID", A.OPERATING_UNIT AS "A.OPERATING_UNIT", A.PRODUCT AS "A.PRODUCT", A.FUND_CODE AS "A.FUND_CODE", A.CLASS_FLD AS "A.CLASS_FLD", A.PROGRAM_CODE AS "A.PROGRAM_CODE", A.BUDGET_REF AS "A.BUDGET_REF", A.AFFILIATE AS "A.AFFILIATE", A.AFFILIATE_INTRA1 AS "A.AFFILIATE_INTRA1", A.AFFILIATE_INTRA2 AS "A.AFFILIATE_INTRA2", A.PROJECT_ID AS "A.PROJECT_ID", A.BOOK_CODE AS "A.BOOK_CODE", A.GL_ADJUST_TYPE AS "A.GL_ADJUST_TYPE", A.CURRENCY_CD AS "A.CURRENCY_CD", A.STATISTICS_CODE AS "A.STATISTICS_CODE", A.FISCAL_YEAR AS "A.FISCAL_YEAR", A.ACCOUNTING_PERIOD AS "A.ACCOUNTING_PERIOD", A.POSTED_TOTAL_AMT AS "A.POSTED_TOTAL_AMT", A.POSTED_BASE_AMT AS "A.POSTED_BASE_AMT", A.POSTED_TRAN_AMT AS "A.POSTED_TRAN_AMT", A.BASE_CURRENCY AS "A.BASE_CURRENCY", TO_CHAR(CAST((A.DTTM_STAMP_SEC) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF') AS "A.DTTM_STAMP_SEC", A.PROCESS_INSTANCE AS "A.PROCESS_INSTANCE", B.ACCOUNT_SETID AS "B.ACCOUNT_SETID", B.DEPTID_SETID AS "B.DEPTID_SETID", B.PRODUCT_SETID AS "B.PRODUCT_SETID", C.SETID AS "C.SETID"
FROM ((PS_LEDGER A LEFT OUTER JOIN PS_OTBI_BUSET_MAP B ON A.BUSINESS_UNIT = B.BUSINESS_UNIT ) LEFT OUTER JOIN PS_SET_CNTRL_TBL C ON A.BUSINESS_UNIT = C.SETCNTRLVALUE ) ) D101 JOIN ( SELECT A.SETID AS "A.SETID", A.DEPTID AS "A.DEPTID", TO_CHAR(A.EFFDT,'YYYY-MM-DD') AS "A.EFFDT", A.EFF_STATUS AS "A.EFF_STATUS", A.DESCR AS "A.DESCR", A.DESCRSHORT AS "A.DESCRSHORT"
FROM PS_OTBI_DEPT_VW A ) D94 ON D101."A.DEPTID" = D94."A.DEPTID" AND D101."B.DEPTID_SETID" = D94."A.SETID" JOIN ( SELECT A.SETID AS "A.SETID", A.ACCOUNT AS "A.ACCOUNT", TO_CHAR(A.EFF_ST_DT,'YYYY-MM-DD') AS "A.EFF_ST_DT", TO_CHAR(A.EFF_END_DT,'YYYY-MM-DD') AS "A.EFF_END_DT", A.EFF_STATUS AS "A.EFF_STATUS", A.DESCR AS "A.DESCR", A.DESCRSHORT AS "A.DESCRSHORT"
FROM PS_OTBI_ACCOUNT_VW A ) D86 ON D101."A.ACCOUNT" = D86."A.ACCOUNT" AND D101."B.ACCOUNT_SETID" = D86."A.SETID"
WHERE D86."A.ACCOUNT" = '401000' AND D94."A.DEPTID" = '21300' AND D101."A.ACCOUNT" = '401000' AND D101."A.DEPTID" = '21300'
GROUP BY D86."A.ACCOUNT", D86."A.DESCR", D94."A.DEPTID", D94."A.DESCR"
ORDER BY D86."A.DESCR" ASC, D86."A.ACCOUNT" ASC, D94."A.DESCR" ASC, D94."A.DEPTID" ASC</SQL>
</qcs:QAS_CREATE_CPQ_RESP_MSG>
</soapenv:Body>
</soapenv:Envelope>