Performance Issue While Accessing Assignment Process Results Form
(Doc ID 2302945.1)
Last updated on AUGUST 02, 2023
Applies to:
Oracle Payroll - Version 12.2 to 12.2 HRMS RUP 11 [Release 12.2]Oracle Payroll - Version 12.1 to 12.1 HRMS RUP10 [Release 12.0 to 12.1]
Information in this document applies to any platform.
Symptoms
The 'Assignment Process Results' form is taking time to retrieve the results.
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
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 HRMS responsibility
2. Navigate to View > Assignment Process Results
3. Query for an employee with no date range set
4. Observe long wait time
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |