Retropay (Enhanced) Encountering a Performance Issue (Doc ID 1400604.1)

Last updated on JANUARY 05, 2017

Applies to:

Oracle Payroll - Version 11.5.10.2 to 12.1 HRMS RUP4 [Release 11.5 to 12.1]
Information in this document applies to any platform.


This note provides a brief overview of


Symptoms

Problem Statement:

When running the RetroPay (Enhanced) concurrent process a Performance issue is encountered with the following query:

SELECT
fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.r
result_value) * FEED.scale),0))
FROM /* Assignment within Government Reporting Entity Inception To Date */
    pay_run_result_values TARGET
   ,pay_balance_feeds_f FEED
   ,pay_run_results RR
   ,pay_assignment_actions ASSACT
   ,pay_assignment_actions BAL_ASSACT
   ,pay_payroll_actions PACT
   ,pay_payroll_actions BACT
where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID
  and BACT.payroll_action_id = BAL_ASSACT.payroll_action_id
  and FEED.balance_type_id = :U1 + decode(TARGET.INPUT_VALUE_ID,null,0,0)
  and FEED.input_value_id = TARGET.input_value_id
  and nvl(TARGET.result_value,'0') != '0'
  and TARGET.run_result_id = RR.run_result_id
  and RR.assignment_action_id = ASSACT.assignment_action_id
  and ASSACT.payroll_action_id = PACT.payroll_action_id
  and PACT.effective_date between FEED.effective_start_date
  and FEED.effective_end_date
  and RR.status in ('P','PA')
  and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
  and ASSACT.assignment_id = BAL_ASSACT.assignment_id
  and ASSACT.tax_unit_id = :TAX_UNIT_ID



Steps to Reproduce:

The issue can be reproduced at will with the following steps:

  1. Submit the Retro Notifications PDF report
  2. Submit the RetroPay Enhanced concurrent process
  3. Encounter performance issue

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