My Oracle Support Banner

Absence Management: Overlapping Absences Not Working. (Doc ID 1970903.1)

Last updated on MARCH 27, 2018

Applies to:

Oracle Fusion Absence Management Cloud Service - Version and later
Information in this document applies to any platform.


Overlapping Absences not working.
While trying to add an open ended absence next error occurs:


JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT AVAILABILITY.RESOURCE_TYPE AVL_RESOURCE_TYPE, AVAILABILITY.RESOURCE_ID AVL_RESOURCE_ID, AVAILABILITY.OBJECT_TYPE, AVAILABILITY.OBJECT_ID, AVAILABILITY.OBJECT_CATEGORY, AVAILABILITY.OBJECT_NAME , AVAILABILITY.AVAILABILITY_CODE, AVAILABILITY.START_DATE_TIME START_TIME, AVAILABILITY.END_DATE_TIME STOP_TIME, AVAILABILITY.DURATION_MS_NUM/(1000 * 60 * 60) MEASURE , HOURS UNIT_OF_MEASURE, :person_id RESOURCE_ID, PERSON RESOURCE_TYPE, null COMMENT_TEXT, ASSIGNMENT.ASSIGNMENT_ID SUBRESOURCE_ID, null TIME_CONSUMER_CONFIG_SET_ID, null TIME_CONSUMER_SET_ID, null BUILDING_BLOCK_TYPE, null TIME_REPORTER_ID FROM PER_ALL_ASSIGNMENTS_F ASSIGNMENT, TABLE(PER_AVAILABILITY_DETAILS.GET_AVAILABILITY_DETAILS( P_RESOURCE_TYPE => :pAvlResourceType ,P_Resource_Id => Assignment.Assignment_Id ,P_Period_Start => TO_TIMESTAMP(:startTime,yyyy-mm-dd HH24:MI:SS.FF) ,P_PERIOD_END => TO_TIMESTAMP(:stopTime,yyyy-mm-dd HH24:MI:SS.FF) ,P_INCLUDE_CALEVENTS=> :pIncludeCalEvents ,P_INCLUDE_ABSENCE => :includeAbsence)) AVAILABILITY Where /* --Bug#19061376 - This join is restricting the schedule to the assignment. It is incorrect, hence removed. --Use filterByAssignmentId view criteria on this view if needed. Assignment.Assignment_Id = Availability.Resource_Id AND */ ASSIGNMENT.PERSON_ID in (select regexp_substr(:person_id,[^,]+, 1, level) from dual connect by regexp_substr(:person_id, [^,]+, 1, level) is not null) AND ASSIGNMENT.ASSIGNMENT_TYPE in (E,N,C) and assignment.effective_latest_change = Y and assignment.primary_flag = Y And Trunc(Sysdate) Between Assignment.Effective_Start_Date And Assignment.Effective_End_Date and Assignment.ASSIGNMENT_STATUS_TYPE = ACTIVE) QRSLT WHERE ( ( ( (UPPER(AVAILABILITY_CODE) = UPPER(:vc_temp_1) ) ) ) AND ( ( (AVL_RESOURCE_TYPE = :pAvlResourceType ) AND (AVL_RESOURCE_ID = :assignment_id ) ) ) ): ORA-10260: limit size (1048576) of the PGA heap set by event 10261 exceeded ORA-06512: at "FUSION.PER_AVAILABILITY_DETAILS", line 1299 ORA-06512: at "FUSION.PER_AVAILABILITY_DETAILS", line 2707 ORA-06512: at "FUSION.PER_AVAILABILITY_DETAILS", line 2777 ORA-06512: at line 1

The issue can be reproduced at will with the following steps:
1. enter an absence with type 'Earned leave' for one day 25.02.2014;
2. enter an open ended absence with type 'No Biometric Leave' starting with 25.02.2014; try to save it and the error occurs


To view full details, 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 a vibrant support community of peers and Oracle experts.