OBIA 11g: HR Load Fails Due To Error "Missing Right Parenthesis"

(Doc ID 2089991.1)

Last updated on AUGUST 10, 2017

Applies to:

Business Intelligence Applications Consumer - Version 11.1.1.8.1 and later
Information in this document applies to any platform.

Symptoms

On Oracle Business Intelligence Application 11.1.1.8.1


select
  '#BIAPPS.FLEX_TARGET_COLUMN' || ':' || '#BIAPPS.FLEX_TARGET_DOMAIN' C1_DOMAIN_CODE,
  SQ_GRP.ID C2_DOMAIN_MEMBER_CODE,
  SQ_GRP.MEANING C3_DOMAIN_MEMBER_NAME,
  SQ_GRP.DESCRIPTION C4_DOMAIN_MEMBER_DESCR,
  '#BIAPPS.FLEX_TARGET_COLUMN' || ':' || '#BIAPPS.FLEX_TARGET_DOMAIN' || '~' || SQ_GRP.ID C5_INTEGRATION_ID,
  SQ_GRP.CREATED_BY C6_CREATED_BY_ID,
  SQ_GRP.LAST_UPDATED_BY C7_CHANGED_BY_ID,
  SQ_GRP.CREATION_DATE C8_CREATED_ON_DT,
  SQ_GRP.LAST_UPDATE_DATE C9_CHANGED_ON_DT,
  SQ_GRP.LANGUAGE C10_LANGUAGE,
  SQ_GRP.SOURCE_LANG C11_SOURCE_LANG
from
( /* Subselect from SDE_ORA_DomainGeneral_FlexfieldValueSet.W_DOMAIN_MEMBER_GS_SQ
*/
select
  SQ_VSET.ID ID,
  MAX(SQ_VSET.MEANING) MEANING,
  MAX(SQ_VSET.DESCRIPTION) DESCRIPTION,
  SQ_VSET.LANGUAGE LANGUAGE,
  SQ_VSET.SOURCE_LANG SOURCE_LANG,
  MIN(SQ_VSET.CREATION_DATE) CREATION_DATE,
  MIN(SQ_VSET.CREATED_BY) CREATED_BY,
  MAX(SQ_VSET.LAST_UPDATE_DATE) LAST_UPDATE_DATE,
  MAX(SQ_VSET.LAST_UPDATED_BY) LAST_UPDATED_BY
from
( /* Subselect from SDE_ORA_DomainGeneral_FlexfieldValue_Set.W_DOMAIN_MEMBER_GS_SQ_VSET
*/
#BIAPPS.FLEX_VALUE_SET_SQL
) SQ_VSET
where (1=1)
And (
 1=1
)
And (SQ_VSET.LANGUAGE IN (#BIAPPS.LANGUAGE_LIST))
Group By SQ_VSET.ID,
 SQ_VSET.LANGUAGE,
 SQ_VSET.SOURCE_LANG
) SQ_GRP
where (1=1)

In the above mentioned SQL , there is an ODI variable #BIAPPS.FLEX_VALUE_SET_SQL used. As per the session log, the value of this variable is

SELECT
  DISTINCT TO_CHAR(LOOKUP_CODE) ID, TO_CHAR(MEANING) MEANING,
  NULL DESCRIPTION,'US' LANGUAGE,'US' SOURCE_LANG,
  TO_DATE(NULL) LAST_UPDATE_DATE,TO_NUMBER(NULL) LAST_UPDATED_BY,
  TO_NUMBER(NULL) CREATED_BY,TO_DATE(NULL) CREATION_DATE
FROM APPS.HR_LOOKUPS HRL
WHERE HRL.LOOKUP_TYPE = 'YES_NO'
  AND HRL.ENABLED_FLAG = 'Y'



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