Payroll Run Taking More Than 5 Hours to Complete for Singapore Legislation (Doc ID 1960450.1)

Last updated on SEPTEMBER 28, 2016

Applies to:

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

Symptoms

12.1.3 version, Singapore Payroll

ACTUAL BEHAVIOR
After deployed the new CPF and IRAS patches, "Payroll Run" program took more than 5 hours to complete the program.

EXPECTED BEHAVIOR
"Payroll Run" program will be completed within 2-5mins (for 1000 plus assignments).

STEPS
The issue can be reproduced at will with the following steps:
1.Apply CPF and IRAS patches
2.Run "Payroll Run" program
3.It shows performance issue

WORST PERFORMANCE SQL
Maximum Time Taking query:

SELECT
fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.r
esult_value) * FEED.scale),0)) FROM /* SG Assignment SG_ASG_PTD */
       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
      ,per_time_periods        PTP
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  ASSACT.action_sequence          <= BAL_ASSACT.action_sequence
  and  ASSACT.assignment_id             = BAL_ASSACT.assignment_id +
decode(PTP.start_date, null, 0, 0)/* Period to Date */
  and  BACT.effective_date between PTP.start_date and PTP.end_date
  and  PTP.payroll_id                   = BACT.payroll_id
  and  PACT.effective_date between PTP.start_date and PTP.end_date and
FEED.balance_type_id = :U1 + decode(TARGET.input_value_id, 0, 0, 0)

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