RTI - FPS - Performance Issue Take 6 Hours (Doc ID 1575387.1)

Last updated on JUNE 07, 2017

Applies to:

Oracle HRMS (UK) - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, UK Payroll

ACTUAL BEHAVIOR
---------------
When running the RTI FPS process, a performance issue is being encountered. Worst performing SQL is as detailed:

SELECT /*+ ORDERED */ fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0)) FROM pay_assignment_actions BAL_ASSACT
  ,pay_payroll_actions BACT
  ,per_time_periods BPTP
  ,per_all_assignments_f START_ASS
  ,per_all_assignments_f ASS
  ,pay_assignment_actions ASSACT
  ,pay_payroll_actions PACT
  ,per_time_periods PPTP
  ,pay_run_results RR
  ,pay_run_result_values TARGET
  ,pay_balance_feeds_f FEED
where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID
and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
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 BPTP.time_period_id = BACT.time_period_id
and PPTP.time_period_id = PACT.time_period_id
and START_ASS.assignment_id = BAL_ASSACT.assignment_id
and ASS.period_of_service_id = START_ASS.period_of_service_id
and ASSACT.assignment_id = ASS.assignment_id
and BACT.effective_date between
  START_ASS.effective_start_date and START_ASS.effective_end_date
and PACT.effective_date between
  ASS.effective_start_date and ASS.effective_end_date
and PACT.effective_date >=
  /* find the latest td payroll transfer date - compare each of the */
  /* assignment rows with its predecessor looking for the payroll */
  /* that had a different tax district at that date */
  ( select nvl(max(NASS.effective_start_date),
  to_date('01/01/0001','DD/MM/YYYY'))
from per_assignments_f NASS
,pay_payrolls_f ROLL
  ,hr_soft_coding_keyflex FLEX
,per_assignments_f PASS
  ,pay_payrolls_f PROLL
  ,hr_soft_coding_keyflex PFLEX
where NASS.assignment_id = ASS.assignment_id
and ROLL.payroll_id = NASS.payroll_id
and NASS.effective_start_date between
ROLL.effective_start_date and ROLL.effective_end_date
and ROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
and NASS.assignment_id = PASS.assignment_id
and PASS.effective_end_date = (NASS.effective_start_date - 1)
and NASS.effective_start_date and PROLL.payroll_id = PASS.payroll_id
and NASS.effective_start_date between
PROLL.effective_start_date and PROLL.effective_end_date
and PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
  and NASS.payroll_id != PASS.payroll_id
and FLEX.segment1 != PFLEX.segment1
)
and exists ( select null from
  /* check that the current assignment tax districts match */
  pay_payrolls_f BROLL
  ,hr_soft_coding_keyflex BFLEX
  ,pay_payrolls_f PROLL
  ,hr_soft_coding_keyflex PFLEX
  where BACT.payroll_id = BROLL.payroll_id
  and PACT.payroll_id = PROLL.payroll_id
  and BFLEX.soft_coding_keyflex_id = BROLL.soft_coding_keyflex_id
  and PFLEX.soft_coding_keyflex_id = PROLL.soft_coding_keyflex_id
  and BACT.effective_date between
  BROLL.effective_start_date and BROLL.effective_end_date
  and BACT.effective_date between
  PROLL.effective_start_date and PROLL.effective_end_date
  and BFLEX.segment1 = PFLEX.segment1
  )
and PPTP.regular_payment_date >= /* fin year start */
  ( to_date('06-04-' || to_char( to_number(
  to_char( BPTP.regular_payment_date,'YYYY'))
  + decode(sign( BPTP.regular_payment_date - to_date('06-04-'
  || to_char(BPTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
  -1,-1,0)),'DD-MM-YYYY'))
and ASSACT.action_sequence
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 999 0.55 0.56 0 0 0 0
Fetch 999 102.92 101.75 5 3037000 0 999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1999 103.47 102.32 5 3037000 0 999


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. From UK HRMS Manager responsibility
2. Submit the concurrent request: RTI - Full Payment Submission Process (FPS) 2013/14


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