My Oracle Support Banner

Deductions Report (XML) Performance Issue (Doc ID 2924589.1)

Last updated on FEBRUARY 27, 2023

Applies to:

Oracle HRMS (US) - Version 12.1.1 and later
Information in this document applies to any platform.
This note provides a brief overview of Bug:34863612.

Symptoms

Problem Statement:

"Deductions Report (XML)" and "Deductions Report" are taking time in the following query (SQL ID:8dtsgp4mb0925).

select distinct
Consolidation_set_id ,
consolidation_set_name ,
payroll_id ,
payroll_name ,
payroll_effective_start_date ,
payroll_effective_end_date ,
payroll_action_id ,
payroll_action_effective_date ,
payroll_action_date_earned ,
time_period_id ,
period_name ,
period_num ,
period_type,
period_start_date,
period_end_date ,
assignment_action_id ,
assignment_id ,
tax_unit_id ,
GRE ,
person_id ,
employee_number ,
full_name ,
organization_id ,
organization_name ,
location_id ,
location_code ,
location_description ,
assignment_sequence ,
assignment_number ,
element_type_id ,
element_name ,
element_description ,
business_group_id ,
primary_balance_id ,
hours_balance_id ,
classification_id ,
classification_name ,
primary_balance ,
not_taken_balance ,
arrears_balance ,
accrued_balance ,
total_owed ,
decode(:P_SORT_OPTION1_L, 'GRE', 'GRE',
'Organization', 'Organization',
'Location', 'Location',
null) Sort_option1,
decode(:P_SORT_OPTION2, 'GRE', 'GRE',
'Organization', 'Organization',
'Location', 'Location',
null) Sort_option2,
decode(:P_SORT_OPTION3, 'GRE', 'GRE',
'Organization', 'Organization',
'Location', 'Location',
null) Sort_option3,
decode(:P_SORT_OPTION1_L, 'GRE', GRE,
'Organization', Organization_name,
'Location', Location_code,
null) sort_option1_value,
decode(:P_SORT_OPTION2, 'GRE', GRE,
'Organization', Organization_name,
'Location', Location_code,
null) sort_option2_value,
decode(:P_SORT_OPTION3, 'GRE', GRE,
'Organization', Organization_name,
'Location', Location_code,
null) sort_option3_value,
PAY_PAYUSDED_XMLP_PKG.cf_sort1formula(decode ( :P_SORT_OPTION1_L , 'GRE' , 'GRE' , 'Organization' , 'Organization' , 'Location' , 'Location' , null )) CF_Sort1,
PAY_PAYUSDED_XMLP_PKG.cf_sort2formula(decode ( :P_SORT_OPTION2 , 'GRE' , 'GRE' , 'Organization' , 'Organization' , 'Location' , 'Location' , null )) CF_Sort2,
PAY_PAYUSDED_XMLP_PKG.cf_sort3formula(decode ( :P_SORT_OPTION3 , 'GRE' , 'GRE' , 'Organization' , 'Organization' , 'Location' , 'Location' , null )) CF_Sort3,
PAY_PAYUSDED_XMLP_PKG.cf_sort1_valueformula(decode ( :P_SORT_OPTION1_L , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null )) CF_Sort1_value,
PAY_PAYUSDED_XMLP_PKG.cf_sort2_valueformula(decode ( :P_SORT_OPTION2 , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null )) CF_Sort2_value,
PAY_PAYUSDED_XMLP_PKG.cf_sort3_valueformula(decode ( :P_SORT_OPTION3 , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null )) CF_Sort3_value,
PAY_PAYUSDED_XMLP_PKG.s1_total_textformula(decode ( :P_SORT_OPTION1_L , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null )) s1_total_text,
PAY_PAYUSDED_XMLP_PKG.s2_total_textformula(decode ( :P_SORT_OPTION1_L , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null ), decode ( :P_SORT_OPTION2 , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null )) s2_total_text,
PAY_PAYUSDED_XMLP_PKG.s3_total_textformula(decode ( :P_SORT_OPTION1_L , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null ), decode ( :P_SORT_OPTION2 , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null ), decode ( :P_SORT_OPTION3 , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null )) s3_total_text,
PAY_PAYUSDED_XMLP_PKG.s3_textformula(decode ( :P_SORT_OPTION1_L , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null ), decode ( :P_SORT_OPTION1_L , 'GRE' , 'GRE' , 'Organization' , 'Organization' , 'Location' , 'Location' , null ), decode ( :P_SORT_OPTION2 , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null ), decode ( :P_SORT_OPTION2 , 'GRE' , 'GRE' , 'Organization' , 'Organization' , 'Location' , 'Location' , null ), decode ( :P_SORT_OPTION3 , 'GRE' , GRE , 'Organization' , Organization_name , 'Location' , Location_code , null ), decode ( :P_SORT_OPTION3 , 'GRE' , 'GRE' , 'Organization' , 'Organization' , 'Location' , 'Location' , null )) S3_Text,
PAY_PAYUSDED_XMLP_PKG.classification_total_textformu(classification_name) Classification_total_text,
PAY_PAYUSDED_XMLP_PKG.element_total_textformula(element_name) Element_total_text,
PAY_PAYUSDED_XMLP_PKG.person_total_textformula(full_name) Person_total_text,
PAY_PAYUSDED_XMLP_PKG.scheduled_dednformula(primary_balance, not_taken_balance, :arrears_taken) scheduled_dedn,
PAY_PAYUSDED_XMLP_PKG.current_arrearsformula(arrears_balance) current_arrears,
PAY_PAYUSDED_XMLP_PKG.arrears_takenformula(arrears_balance) arrears_taken,
PAY_PAYUSDED_XMLP_PKG.remainingformula(total_owed, PAY_PAYUSDED_XMLP_PKG.cf_accruedformula(accrued_balance, primary_balance, total_owed)) remaining,
PAY_PAYUSDED_XMLP_PKG.cf_accruedformula(accrued_balance, primary_balance, total_owed) CF_Accrued
from pay_us_deductions_report_v pudr
where consolidation_set_id = to_number(:P_CONSOLIDATION_SET_ID)
and payroll_action_effective_date between :P_START_DATE and :P_END_DATE
and business_group_id = to_number(:P_BUSINESS_GROUP_ID)
and primary_balance <> 0
and payroll_id = to_number(:p_payroll_id)
order by 2, 33, 1, 4, 5, 6, 46, 43, 47, 44, 48, 45,
36, 37, 31 , 30 , 32, 22, 29, 20, 17, 21, 28, 25, 27,
24, 19, 18, 26, 23, 8, 9, 11, 13, 10, 12, 15, 14, 7,
16, 34, 35, 38, 42, 41, 39, 40

/* order by Consolidation_set_id, element_description, payroll_id, payroll_name, payroll_effective_start_date, decode(:P_SORT_OPTION3, 'GRE', 'GRE',
'Organization', 'Organization',
'Location', 'Location',
null), total_owed, decode(:P_SORT_OPTION1_L, 'GRE', GRE,
'Organization', Organization_name,
'Location', Location_code,
null), decode(:P_SORT_OPTION1_L, 'GRE', 'GRE',
'Organization', 'Organization',
'Location', 'Location',
null), decode(:P_SORT_OPTION2, 'GRE', GRE,
'Organization', Organization_name,
'Location', Location_code,
null), decode(:P_SORT_OPTION2, 'GRE', 'GRE',
'Organization', 'Organization',
'Location', 'Location',
null),
hours_balance_id, classification_id, element_type_id, assignment_number , element_name, employee_number, assignment_sequence, GRE, assignment_action_id, person_id, location_description, organization_name, location_code,
organization_id, tax_unit_id, assignment_id, location_id, full_name, payroll_action_id, payroll_action_effective_date, time_period_id, period_num, payroll_action_date_earned, period_name, period_start_date, period_type, payroll_effective_end_date,
period_end_date, business_group_id, primary_balance_id, classification_name, accrued_balance, arrears_balance, primary_balance, not_taken_balance*/

 

 

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

  1.  Login as US HRMS Manager

  2.  Submit "Deductions Report (XML)".
     (N) Processes and Reports > Submit Processes and Reports > Single Request
     
  3.  Observe the performance issue.



Changes

 Upgrade to 12.2.11 or apply HR_PF.C RUP16.

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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.