My Oracle Support Banner

Fusion Applications OTBI: Analysis fails for one user and only on this POD : nQSError 43119 Query Failed Please have your Administrator look at the log for more details The nqquery.log shows this error: nQSError: 17010 SQL statement preparation failed (Doc ID 1678309.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Fusion Global Human Resources Cloud Service - Version 11.1.7.0.0 and later
Information in this document applies to any platform.

Symptoms

Fusion HCM Cloud 11.1.7 - Custom OTBI analysis with UNION, fails for one user


EXPECTED BEHAVIOR
-----------------------------
The custom analysis should execute and return data
 
ERROR
---------
The screen shows this error:
nQSError 43119  Query Failed Please have your Administrator look at the log for more details

The nqquery.log shows this error:
nQSError: 17010 SQL statement preparation failed.
 

STEPS
--------
1. Navigate to Fusion Apps OBIEE Home Page, connected as the user affected
2, Execute the custom Dashboard

BI Dashboard: Performance and Goals
Tab: Goal Count

3. See that all the included analyses work apart from one , which fails with an error

Note: As a second test, the end user filtered the analysis more tightly, but this did not help.

BUSINESS IMPACT
-------------------------
One user unable to execute the analysis in one POD


OTHER INFO
----------------
The user is a powerful user, he has a large number of data security roles, covering > 20 countries

Analysis definition:

SET VARIABLE QUERY_SRC_CD='Report',SAW_DASHBOARD='/shared/Custom/_portal/Performance And Goals',SAW_DASHBOARD_PG='Goals Count',SAW_SRC_PATH='/shared/Custom/Human Capital Management/FUSION REPORTS/PERFORMANCE/# of Emp with and without Goals',PREFERRED_CURRENCY='Local Currency';

SELECT saw_0, saw_1 FROM ((SELECT COUNT(DISTINCT "Worker"."Person Number") saw_0, 'Goals Created' saw_1 FROM "Workforce Goals - Goal Status Overview Real Time" WHERE ("Performance Goals"."Performance Goal Name" IS NOT NULL) AND ("Business Unit"."Business Unit Name" IN ('GSOL-Global Solutions', 'SMART-EUBG - Smart Infra. Division')) AND ("Worker Legislation"."Territory Short Name" = 'Brazil') AND ("Worker"."Assignment Status" = 'Active - Payroll Eligible') AND ("Business Unit"."Status" = 'Active') AND (YEAR("Performance Goals"."Start Date") = 2014) AND ("Worker"."Person Type" = 'Employee'))
UNION
(SELECT COUNT(DISTINCT "Worker"."Person Number") saw_0, 'Goals Not Created' saw_1 FROM "Workforce Goals - Goal Status Overview Real Time" WHERE ("Worker"."Person Number" IN (SELECT saw_0 FROM (SELECT saw_0 saw_0 FROM ((SELECT "Worker"."Person Number" saw_0 FROM "Workforce Management - Worker Assignment Real Time" WHERE ("Worker"."Assignment Status" = 'Active - Payroll Eligible') AND ("Business Unit"."Status" = 'Active') AND ("Business Unit"."Business Unit Name" IN ('GSOL-Global Solutions', 'SMART-EUBG - Smart Infra. Division')) AND ("Worker"."Person Start Date" < ANY (SELECT saw_0 FROM (SELECT "Performance Goals"."Start Date" saw_0 FROM "Workforce Goals - Goal Status Overview Real Time" WHERE YEAR("Performance Goals"."Start Date") = 2014) nqw_1 )) AND ("Worker"."Assignment Status" = 'Active - Payroll Eligible') AND ("Worker Legislation"."Territory Short Name" = 'Brazil')) EXCEPT (SELECT "Worker"."Person Number" saw_0 FROM "Workforce Goals - Goal Status Overview Real Time" WHERE ("Business Unit"."Business Unit Name" IN ('GSOL-Global Solutions', 'SMART-EUBG - Smart Infra. Division')) AND ("Business Unit"."Status" = 'Active') AND (YEAR("Performance Goals"."Start Date") = 2014) AND ("Worker"."Assignment Status" = 'Active - Payroll Eligible') AND ("Worker Legislation"."Territory Short Name" = 'Brazil') AND ("Worker"."Assignment Status" = 'Active - Payroll Eligible'))) t1) nqw_1 )) AND ("Worker"."Person Type" = 'Employee'))) t1 ORDER BY saw_1

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.