QAS_EXECUTECPQSYNC_OPER
This service operation validates and verifies XML-format 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_EXECPQ_SYNC_REQ_MSG.VERSION_1"
xmlns:qas1="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_EXECPQ_SYNC_REQ.VERSION_1">
<soapenv:Header/>
<soapenv:Body>
<qas:QAS_EXECPQ_SYNC_REQ_MSG>
<!--Optional:-->
<qas1:QAS_EXECPQ_SYNC_REQ>
<SelectFields>
<FIELD>
<FieldName>D173.A.TNODE1_DESC</FieldName>
<AggregateType>None</AggregateType>
</FIELD>
<FIELD>
<FieldName>D101.A.POSTED_TOTAL_AMT</FieldName>
<AggregateType>None</AggregateType>
</FIELD>
<FIELD>
<FieldName>D173.A.TNODE2</FieldName>
<AggregateType>None</AggregateType>
</FIELD>
<FIELD>
<FieldName>D173.A.TNODE3</FieldName>
<AggregateType>None</AggregateType>
</FIELD>
<FIELD>
<FieldName>D173.A.TNODE4</FieldName>
<AggregateType>None</AggregateType>
</FIELD>
<FIELD>
<FieldName>D173.A.TNODE5</FieldName>
<AggregateType>None</AggregateType>
</FIELD>
<FIELD>
<FieldName>D173.A.TNODE6</FieldName>
<AggregateType>None</AggregateType>
</FIELD>
<FIELD>
<FieldName>D173.A.ACCOUNT</FieldName>
<AggregateType>None</AggregateType>
</FIELD>
<FIELD>
<FieldName>D173.A.TNODE7</FieldName>
<AggregateType>None</AggregateType>
</FIELD>
<FIELD>
<FieldName>D173.A.TNODE1</FieldName>
<AggregateType>None</AggregateType>
</FIELD>
</SelectFields>
<FROM>
<QUERY>
<QueryName>PS_OTBI_LEDGER_VW</QueryName>
<OwnerType>Public</OwnerType>
<QueryAlias>D101</QueryAlias>
<ParentQueryAlias/>
<JoinType>Inner</JoinType>
</QUERY>
<QUERY>
<QueryName>PS_OI_TFLT_EFF_ACT</QueryName>
<OwnerType>Public</OwnerType>
<QueryAlias>D173</QueryAlias>
<ParentQueryAlias>D101</ParentQueryAlias>
<JoinType>Inner</JoinType>
<JoinCriteria>
<FieldName>A.ACCOUNT</FieldName>
<JoinCriteriaOperator>=</JoinCriteriaOperator>
<ParentFieldName>A.ACCOUNT</ParentFieldName>
</JoinCriteria>
<JoinCriteria>
<FieldName>A.SETID</FieldName>
<JoinCriteriaOperator>=</JoinCriteriaOperator>
<ParentFieldName>B.ACCOUNT_SETID</ParentFieldName>
</JoinCriteria>
<JoinCriteria>
<FieldName>A.SETID_TREE</FieldName>
<JoinCriteriaOperator>=</JoinCriteriaOperator>
<ParentFieldName>C.SETID</ParentFieldName>
</JoinCriteria>
</QUERY>
</FROM>
<WHERE>
<FilterCriteria>
<LogicalOperator/>
<LeftParenthesisLevel>0</LeftParenthesisLevel>
<RightParenthesisLevel>0</RightParenthesisLevel>
<Operand1FieldName>D173.A.TREE_NAME</Operand1FieldName>
<Operand2Type>Constant</Operand2Type>
<Operand2FieldName/>
<Operand2Text>ACCTROLLUPFIN</Operand2Text>
<Operator>=</Operator>
</FilterCriteria>
<FilterCriteria>
<LogicalOperator>and</LogicalOperator>
<LeftParenthesisLevel>0</LeftParenthesisLevel>
<RightParenthesisLevel>0</RightParenthesisLevel>
<Operand1FieldName>D173.A.EFF_END_DT</Operand1FieldName>
<Operand2Type>Constant</Operand2Type>
<Operand2FieldName/>
<Operand2Text>1900-01-01</Operand2Text>
<Operator>>=</Operator>
</FilterCriteria>
<FilterCriteria>
<LogicalOperator>and</LogicalOperator>
<LeftParenthesisLevel>0</LeftParenthesisLevel>
<RightParenthesisLevel>0</RightParenthesisLevel>
<Operand1FieldName>D173.A.EFF_ST_DT</Operand1FieldName>
<Operand2Type>Constant</Operand2Type>
<Operand2FieldName/>
<Operand2Text>1900-01-01</Operand2Text>
<Operator>>=</Operator>
</FilterCriteria>
<FilterCriteria>
<LogicalOperator>and</LogicalOperator>
<LeftParenthesisLevel>0</LeftParenthesisLevel>
<RightParenthesisLevel>0</RightParenthesisLevel>
<Operand1FieldName/>
<Operand2Type>Expression</Operand2Type>
<Operand2FieldName/>
<Operand2Text>
case when D173."A.TNODE1" = '' then NULL
else D173."A.TNODE1" end = 'ALLACCOUNTS'
</Operand2Text>
<Operator/>
</FilterCriteria>
</WHERE>
<BlockSizeKB>0</BlockSizeKB>
<MaxRow>1000</MaxRow>
<OutResultType>WEBROWSET</OutResultType>
<OutResultFormat>NONFILE</OutResultFormat>
<CompositeQueryName/>
<PruningType>NONE</PruningType>
</qas1:QAS_EXECPQ_SYNC_REQ>
</qas:QAS_EXECPQ_SYNC_REQ_MSG>
</soapenv:Body>
</soapenv:Envelope>
Response Message: QAS_QUERYRESULTS_WRS_RESP_MSG
| Element Name | Description |
|---|---|
|
QueryResults |
Query Result in webrowset format. |
Example Request:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soapenv:Body>
<QAS_GETQUERYRESULTS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/
Tools/schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
<webRowSet xmlns="http://java.sun.com/xml/ns/jdbc">
<properties>
<escape-processing>true</escape-processing>
<fetch-direction>1000</fetch-direction>
<fetch-size>0</fetch-size>
<isolation-level>1</isolation-level>
<key-columns/>
<map/>
<max-field-size>0</max-field-size>
<max-rows>0</max-rows>
<query-timeout>0</query-timeout>
<read-only>true</read-only>
<show-deleted>false</show-deleted>
<table-name/>
</properties>
<metadata>
<column-count>5</column-count>
<column-definition>
<column-index>1</column-index>
<auto-increment>false</auto-increment>
<case-sensitive>false</case-sensitive>
<currency>false</currency>
<nullable>0</nullable>
<signed>false</signed>
<searchable>false</searchable>
<column-display-size>0</column-display-size>
<column-label>Set</column-label>
<column-name>A.MESSAGE_SET_NBR</column-name>
<schema-name/>
<column-precision>0</column-precision>
<column-scale>0</column-scale>
<table-name/>
<catalog-name/>
<column-type>4</column-type>
<column-type-name>INTEGER</column-type-name>
</column-definition>
<column-definition>
<column-index>2</column-index>
<auto-increment>false</auto-increment>
<case-sensitive>false</case-sensitive>
<currency>false</currency>
<nullable>0</nullable>
<signed>false</signed>
<searchable>false</searchable>
<column-display-size>0</column-display-size>
<column-label>Descr</column-label>
<column-name>A.DESCR</column-name>
<schema-name/>
<column-precision>0</column-precision>
<column-scale>0</column-scale>
<table-name/>
<catalog-name/>
<column-type>12</column-type>
<column-type-name>VARCHAR</column-type-name>
</column-definition>
<column-definition>
<column-index>3</column-index>
<auto-increment>false</auto-increment>
<case-sensitive>false</case-sensitive>
<currency>false</currency>
<nullable>0</nullable>
<signed>false</signed>
<searchable>false</searchable>
<column-display-size>0</column-display-size>
<column-label>Msg</column-label>
<column-name>B.MESSAGE_NBR</column-name>
<schema-name/>
<column-precision>0</column-precision>
<column-scale>0</column-scale>
<table-name/>
<catalog-name/>
<column-type>4</column-type>
<column-type-name>INTEGER</column-type-name>
</column-definition>
<column-definition>
<column-index>4</column-index>
<auto-increment>false</auto-increment>
<case-sensitive>false</case-sensitive>
<currency>false</currency>
<nullable>0</nullable>
<signed>false</signed>
<searchable>false</searchable>
<column-display-size>0</column-display-size>
<column-label>Message</column-label>
<column-name>B.MESSAGE_TEXT</column-name>
<schema-name/>
<column-precision>0</column-precision>
<column-scale>0</column-scale>
<table-name/>
<catalog-name/>
<column-type>12</column-type>
<column-type-name>VARCHAR</column-type-name>
</column-definition>
<column-definition>
<column-index>5</column-index>
<auto-increment>false</auto-increment>
<case-sensitive>false</case-sensitive>
<currency>false</currency>
<nullable>0</nullable>
<signed>false</signed>
<searchable>false</searchable>
<column-display-size>0</column-display-size>
<column-label>Severity</column-label>
<column-name>B.MSG_SEVERITY</column-name>
<schema-name/>
<column-precision>0</column-precision>
<column-scale>0</column-scale>
<table-name/>
<catalog-name/>
<column-type>12</column-type>
<column-type-name>VARCHAR</column-type-name>
</column-definition>
</metadata>
<data>
<currentRow>
<columnValue>2</columnValue>
<columnValue>PeopleCode</columnValue>
<columnValue>1</columnValue>
<columnValue>Invalid use of !.</columnValue>
<columnValue>E</columnValue>
</currentRow>
<currentRow>
<columnValue>2</columnValue>
<columnValue>PeopleCode</columnValue>
<columnValue>2</columnValue>
<columnValue>Literal is too long.</columnValue>
<columnValue>E</columnValue>
</currentRow>
</data>
</webRowSet>
</QAS_GETQUERYRESULTS_RESP_MSG>
</soapenv:Body>
</soapenv:Envelope>