EAP: Matching Process Performance Issue

(Doc ID 2374802.1)

Last updated on MARCH 16, 2018

Applies to:

PeopleSoft Enterprise FIN Payables - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

 

ISSUE:
 
PS 9.2 - PUM 23 Matching Process Performance Issue.


The replication steps on included in the document that we attached to the SR when we opened it.

In our analysis, we've identified two bad SQL's within the AP Match process. Here's our DEV team's notes on the steps and their timings:

-------------------------------------------------------------------------------------------------------------------------------------------------------
I ran AP Match in PRH for 1 voucher and it took 817.67 seconds. The longest running sequels are below:

1). AP_MATCH.VchRecv.Step06A2 – took 548.42 seconds. Notice that process instance is NOT a key in the PS_DISTRIB_LINE table and that was the only criteria. That table has 9,993,229 records in PRH.

%Select(BUSINESS_UNIT, VOUCHER_ID)
 SELECT DISTINCT BUSINESS_UNIT
 , VOUCHER_ID
  FROM PS_DISTRIB_LINE
 WHERE PROCESS_INSTANCE = %ProcessInstance

1). AP_MATCH.FlagVchr.Step05 – took 215.94 seconds

UPDATE %Table(VOUCHER)
  SET IN_PROCESS_FLG = 'N'
 WHERE IN_PROCESS_FLG = 'Y'
  AND PROCESS_INSTANCE <> %ProcessInstance
  AND EXISTS (
 SELECT 'X'
  FROM PS_PMN_PRCSLIST A
 WHERE A.PRCSINSTANCE = %Table(VOUCHER).PROCESS_INSTANCE
  AND A.RUNSTATUS = '8'
  AND A.PRCSNAME = 'AP_MATCH')

 

STEPS:
----------
1. Create PO/Receipt.
2. Create vouchers
3. Copy PO/receipt to the the voucher.
4. Run matching

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