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