Fusion Applications - BI Publisher - Error - ORA-01722 - Invalid Number When Compiling Data Model (Doc ID 2137013.1)

Last updated on AUGUST 31, 2016

Applies to:

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

Symptoms

Data model Error when compiling specific data sets.

SQL Query:
========
SELECT COUNT(paam.PERSON_ID) Terms
, 0 BegHead
, 0 EndHead
FROM PER_ASSIGNMENT_SECURED_LIST_V paam
INNER JOIN PER_ASSIGNMENT_STATUS_TYPES past
ON past.ASSIGNMENT_STATUS_TYPE_ID = paam.ASSIGNMENT_STATUS_TYPE_ID

INNER JOIN PER_PERIODS_OF_SERVICE ppos
ON ppos.PERIOD_OF_SERVICE_ID = paam.PERIOD_OF_SERVICE_ID

WHERE paam.PRIMARY_FLAG = 'Y'
AND paam.EFFECTIVE_LATEST_CHANGE = 'Y'
AND past.PER_SYSTEM_STATUS <> 'INACTIVE'
AND past.ASSIGNMENT_STATUS_CODE <> 'EXTIN_NO_PROCESS'
AND paam.SYSTEM_PERSON_TYPE ='EMP'
AND paam.EMPLOYMENT_CATEGORY IN('FR', 'PR')
AND ppos.ACTUAL_TERMINATION_DATE BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE
AND ppos.ACTUAL_TERMINATION_DATE BETWEEN TO_DATE('1001' || TO_CHAR(NVL(:P_StartDate, SYSDATE), 'yyyy'), 'MMDDYYYY') AND TO_DATE('1231' || TO_CHAR(NVL(:P_StartDate, SYSDATE), 'yyyy'), 'MMDDYYYY')

Error: ORA-01722: invalid number

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