Oracle Retropay Fails With: APP-PAY-06881: Error ORA-01403: No Data Found On Table pay_element_types_f (Doc ID 1075583.1)

SYMPTOMS

Problem Statement: 

When executing a Retro Process (RetroPay Enhanced or RetroPay by Element) the following error occurs:

SQL_NO 507
TABLE_NAME pay_element_types_f
APP-PAY-06881: Error ORA-01403: no data found
has occurred in table pay_element_types_f at location 507

Cause: an oracle error has occurred. The failure was reported on table pay_element_types_f at location 507 with the error text ORA-01403: no data found

CAUSE

The issue can be caused by one of the following setup issues :

1. RetroPay is not supported for the associated Legislation.
2. An element is included in the RetroPay set, but has no RetroPay element defined on the element setup. 
3. Effective Start Date for the one of the elements in Element Set is after the Start Date parameter passed for the 'Retropay by Element' process.

SOLUTION

This issue can be encountered if RetroPay (Enhanced) or RetroPay by Element is being executed on a Legislation for which it is not Supported.  Before any further debugging is done, confirm that your Legislation supports the functionality.  Review the following note:

• Document: 1118267.1 What localizations support Retropay By Element and Retropay Enhanced?

If the Legislation supports the functionality continue with the steps to Diagnose and Resolve this issue:

Debugging:

To assist in determining the issue the process should be rerun with LOGGING turned on.  The Logging vales should be 'GMFZT'

• For assistance with logging setup and obtaining the log file it is recommended that the following note be utilized: 

NOTE:1076458.6 - Logging For Use With Payroll / PYUGEN Process: How to Use and Settings For The Pay_Action_Parameters Table

Reminder:  Make sure to turn LOGGING off when the process has completed.

Review the Log file

Log File Output Example:

Retro Element: 192
Out } pybkecrec
Out } pybkegre
RETRO ELEMENT: 192
In { pycload
In { pycfind
object not found
Out } pycfind

object NOT already present in cache: creating
In { pyccret
object type: [ET] object id: [192]
In { pycfind
object not found
Out } pycfind
Out } pyccret
In { pyclet
Oracle error occurred
Table=pay_element_types_f
sqlno=507
ORA-01403: no data found

Exiting without success

object type: [ET] object id: [192] - this equates to the Retro Element Type ID value of 192

Problem Analysis: 

Cause 2: Confirm that Retro Element is defined

Run the following SQL using the element Type ID value for the input parameter &ET_ID:

select *
from pay_element_entries_f
where element_type_id = &ET_ID;

If a record is returned, then this is not the problem, skip to Cause 2 Problem Analysis.

If no record is returned then the run the following SQL to determine which 'Regular' Element has this Retro Element associated with it, using the element Type ID value for the input parameter &ET_ID:

select prcu.creator_id,
       prcu.RETRO_COMPONENT_USAGE_ID,
       pesu.RETRO_ELEMENT_TYPE_ID
from   pay_retro_component_usages prcu
      ,pay_element_span_usages pesu
where  pesu.RETRO_COMPONENT_USAGE_ID = prcu.RETRO_COMPONENT_USAGE_ID
and    pesu.RETRO_ELEMENT_TYPE_ID = &ET_ID;

Note:  The Creator_id is the element_type_id for the 'Regular' Element

Solution: 

First check that no element_entries have been created for the Retro Element, using the element Type ID value for the input parameter &ET_ID:

Select *
from   pay_element_entries_f
where  element_type_id = &ET_ID;

If no rows are selected, proceed with replacing the Retro Element that doesn't exist in Pay_Element_Types_F with one that does.

Using the following:

1. Total Compensation
2. Basic
3. Element Description (query up Element Name)
4. Click on Recalculation tab
5. Retro Components

Cause 3: Element Effective Start Date after the Start Date parameter passed for the Retropay

Run the following SQL using the element Type ID value for the input parameter &ET_ID:

select element_type_id,
       effective_start_date,
       effective_end_date,
       element_name
from   pay_element_types_f
where element_type_id = &ET_ID;

Review the output of the SQL to see if the element(s) have effective dates that are prior to the Start Date of the Retropay.  The RetroElement MUST BE active within the range of the Start and End dates used when executing the Retro Process.

Solution: 

1. Rollback the RetroPay Process
2. Correct either the
• Start Date of the RetroPay Process
• Effectives Date associated with the Retro Element
3. Retry the RetroPay Process

If neither of these solutions address the issue then please log a Service request with Support Services.  At the time that the SR is created, please provide the following:

NOTE:1075583.1 - Oracle Retropay Fails With: APP-PAY-06881: Error ORA-01403: No Data Found On Table pay_element_types_f


*****************************************************************************

Retro-Notifications Report Enhanced (PDF) Error: ORA-01422: exact fetch returns more than requested number of rows (Doc ID 2212566.1)

When attempting to Retro-Notifications Report Enhanced (PDF), the following error occurs:


HR_6881_HRPROC_ORA_ERR
SQLERRMC ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.HR_NONRUN_ASACT", line 5289
ORA-06512: at line 2

SQL_NO 435
TABLE_NAME hr_nonrun_asact.asact
APP-PAY-06881: Error ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.HR_NONRUN_ASACT", line 5289
ORA-06512: at line 2
has occurred in table hr_nonrun_asact.asact at location 435

Cause: an oracle error has occurred. The failure was reported on table hr_nonrun_asact.asact at location 435 with the error text ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.HR_NONRUN_ASACT", line 5289
ORA-06512: at line 2

CAUSE

There are duplicate unprocessed rows in pay_retro_assignments table for one or more employees.  Only one unprocessed row can exist at any given time for a single assignment.

To confirm that you have duplicated rows, run this query:

select assignment_id, count(*)
from pay_retro_assignments
where retro_assignment_action_id is null
group by assignment_id
having count(*) > 1

For each assignment_id identified, find the related assignment number (or employee name) and delete one of the duplicate rows from the Retropay Status form.

STEPS:

1. Navigate: View > Retropay Status
2. Query for unprocessed retro assignments for the assignment identified in the query
3. Change the status of one of the lines to Deferred (it doesn't matter which one you choose)
4. Requery for the deferred line and then delete it.

Repeat the steps for all assignments identified by the query.