Performance Issue With Norwegian Payroll Archiver After Patch 18187702:R12.PAY.B (Doc ID 1940043.1)

Last updated on JANUARY 10, 2017

Applies to:

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

Symptoms

On : 12.1.3 version, Norway Payroll

ACTUAL BEHAVIOR
---------------
<Patch 18187702>:R12.PAY.B has been applied, but the Norwegian Payroll Archiver process still takes 3 hours.
Most expensive SQL:
SELECT
fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0)) FROM 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 BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
      and FEED.balance_type_id + 0 = :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
          /* Calendar Year */
and PACT.effective_date >= trunc(BACT.effective_date, 'Y')


call count cpu elapsed disk query current rows
------ ---------- ---------- Parse 1 0.00 0.00 0 0 0 0
Execute 265708 13.39 13.37 0 0 0 0
Fetch 265708 14483.16 14707.45 2694 631009954 0 265707
------ ---------- ---------- total 531417 14496.56 14720.83 2694 631009954 0
265707

Validated all invalid balances, but still the above SQL is the most
expensive.


EXPECTED BEHAVIOR
-----------------------
The process to run in a few minutes

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Apply <Patch 18187702>:R12.PAY.B
2. Run Norwegian Payroll Archiver 

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Unable to do reporting

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