Performance Issue While Accessing Assignment Process Results Form

(Doc ID 2302945.1)

Last updated on SEPTEMBER 11, 2017

Applies to:

Oracle Payroll - Version 12.1 HRMS RUP9 and later
Information in this document applies to any platform.

Symptoms


The 'Assignment Process Results' form is taking more than 5 min on dev instance and close to 30 min on production instance.

Below is the most expensive query:

SELECT ROW_ID,EFFECTIVE_DATE,PERIOD_NAME,ACTION_TYPE,TYPE,
ASSIGNMENT_ACTION_ID,PRE_PAYMENT_ID,ACTION_STATUS,STATUS_CODE,
MESSAGES_EXIST,ASSIGNMENT_ID,PAYROLL_ACTION_ID
FROM
PAY_ASSIGNMENT_ACTIONS_V WHERE assignment_id = :1 and business_group_id +0 =
:2 and (action_type not in ('BEE','X','Z') and (nvl(date_earned,
effective_date) between greatest(:3,:4) and least( :5,:6)) or (action_type
in ('BEE','X','Z') and nvl(date_earned, effective_date) between :7 and :8))
and ( ( :9 = 'US' and action_type != 'I' ) or ( :10 != 'US' and ( (
action_type = 'I' and not exists ( select 1 from pay_assignment_actions aac,
pay_run_results rrs where aac.assignment_id =
pay_assignment_actions_v.assignment_id and aac.action_sequence >
pay_assignment_actions_v.action_sequence and aac.assignment_action_id =
rrs.assignment_action_id and rrs.element_type_id + 0 in ( select
rrs1.element_type_id from pay_run_results rrs1 where
rrs1.assignment_action_id = pay_assignment_actions_v.assignment_action_id )
)) or (action_type != 'I' )))) and ((:11 is not null and ( (exists (select
1 from pay_restriction_values prv1 where prv1.restriction_code =
'ACTION_TYPE' and prv1.customized_restriction_id = :12 and prv1.value =
pay_assignment_actions_v.action_type )) or(not exists (select 1 from
pay_restriction_values prv1 where prv1.restriction_code = 'ACTION_TYPE' and
prv1.customized_restriction_id = :13) ))) or :14 is null) order by
action_sequence desc 


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

1. Using the US Super HRMS
2. Navigate to View >> Assignment Process Results
3. Query for an employee with no date range set
4. Observe long wait time


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