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_ and FieldName for the SEARCH_CRITERION, QAS will retrieve records that contain fields such as NODE_TYPE and not NODECOUNT.

SEARCH_CRITERION

Required element

Valid search criteria are:

  • RecordName

  • FieldName

  • Description

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:

  • RecordName

  • FieldName

  • Description

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 or No.

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:

  • Key = 1

  • Search item = 2048

  • List item = 32

  • Required = 256

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 or No.

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:

  • Key = 1

  • Search item = 2048

  • List item = 32

  • Required = 256

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>&lt;=</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>&lt;=</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>&lt;=</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>