EAP: Performance Issue With Pay Cycle Process AP_APY2015 at Step AP_PYMNTID.IDMAIN.Step41 SQL (Doc ID 2066954.1)

Last updated on MARCH 18, 2016

Applies to:

PeopleSoft Enterprise FIN Payables - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

Form ID EFT2 (EFT w/Adv) is assigned to payment method EFT and all vouchers have option 'Pay Separate' selected.  When the pay cycle processes a large batch of payments there is a performance issue with the AP_PYMNTID. IDMAIN.Step41 Do Select loop.

The loop will process one payment at a time and for each payment will run the following SQL:

-- 18:45:27.007 .......(AP_PYMNTID.IDMAIN.Step41) (SQL)
UPDATE PS_PAY_ADV2_TMP SET PYMNT_OVERFLOW_ID = 'ADVICE' WHERE 'ACH00' =
PAY_CYCLE AND 2 = PAY_CYCLE_SEQ_NUM AND 'SHARE' = REMIT_SETID AND
'USA0000002' = REMIT_VENDOR AND 'SHARE' = BANK_SETID AND 'USBNK' = BANK_CD
AND 'CHCK' = BANK_ACCT_KEY AND 'ACH' = PYMNT_METHOD AND PYMNT_OVERFLOW_ID <>
'ADVICE' AND ' ' = PYMNT_ID_REF AND 'X' = 'X'
/
-- Row(s) affected: 0

The bind for PYMNT_ID_REF is always blank because the Do Select does not include it in the select list, therefore no rows are ever updated.

STEPS

1. Assign format id EFT2 (EFT w/Adv) to payment method EFT of bank account.
2. Create vouchers with separate payment selected.
3. Run pay cycle.

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