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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms